copy (
-- calculate realtime stock levels
select
master.catalogno,
master.distributor,
master.stock_qty,
master.in_qty,
master.ship_qty,
master.reserved_qty,
master.backorder_qty,
master.final_stock_qty,
coalesce(master.reserved_qty, null) as shipment_amount,
master.final_stock_qty - total_reserved_qty as on_hand,
master.releaseartist as artist,
master.releasetitle as title,
master.format,
substring(master.releasedate::text from 1 for 10 ) as releasedate,
master.label,
master.barcode,
substring(master.max_order::text from 1 for 10) as max_order_date
from
(select
res_selected.catalogno,
res_selected.distributor,
totals.stock_qty,
totals.in_qty,
totals.ship_qty,
res_selected.max_order as max_order,
coalesce(res_selected.reserved_qty, 0) as reserved_qty,
coalesce(res_selected.backorder_qty, 0) as backorder_qty,
coalesce(res_back.reserved_qty, 0) as total_reserved_qty,
coalesce(res_back.backorder_qty, 0) as total_backorder_qty,
totals.stock_qty + totals.in_qty - totals.ship_qty as final_stock_qty,
catalognew.releaseartist,
catalognew.releasetitle,
catalognew.format,
catalognew.releasedate,
catalognew.label,
catalognew.barcode
from
--get daily stock, recent stocked in, recent shipped
(select
kdgstock.catalogno,
min(kdgstock.stock_qty) as stock_qty,
sum(coalesce(stock_in.in_qty, 0)) as in_qty,
sum(coalesce(not_shipped.ship_qty, 0)) as ship_qty
from kdgstocktest as kdgstock
-- get just the latest day from kdgstocktest
inner join
(select
max(cur_date) as today_date,
catalogno
from kdgstocktest
group by catalogno
) as cur_date
on
cur_date.today_date = kdgstock.cur_date and
cur_date.catalogno = kdgstock.catalogno
-- find stockin that before next day's stock update
left join
(select
date as entry_date,
catno as catalogno,
received_qty as in_qty
from kdgincoming
) as stock_in
on
kdgstock.catalogno = stock_in.catalogno and
kdgstock.cur_date < stock_in.entry_date
-- find shipped that have not been fulfilled by order id
left join
(select
shipped.ship_group_id,
fulfilled.order_id as fulfilled_id,
shipped.shipment_date,
shipped.catalogno as shipped_cat,
fulfilled.catno as fulfilled_cat,
shipped.ship_qty,
fulfilled.ship_qty as fulfilled_qty
from kdgshipments as shipped
left outer join
(select
group_id as order_id,
catalogno as catno,
max(qty) as ship_qty
from kdglogtest
where
entry_date < NOW()::date
group by group_id, catalogno
) as fulfilled
on
(shipped.ship_group_id = fulfilled.order_id or shipped.ship_group_id = fulfilled.order_id)
and shipped.catalogno ilike fulfilled.catno
--discard the old and stale results
where shipment_date > '20190806'
and fulfilled.ship_qty is null
and shipped.ship_qty is not null
and shipped.ship_qty != 0
order by shipment_date
) as not_shipped
on
kdgstock.catalogno = not_shipped.shipped_cat
group by
kdgstock.catalogno
) as totals
-- find reserved per product for the selected distributor
full outer join
(select
reserved.catalogno,
reserved.distributor,
max(entry_date) filter (where entry_type = 'ORDER') as max_order,
coalesce(sum(coalesce(reserved.reserved_qty, 0)) FILTER (where entry_type = 'RESERVE'), 0) as reserved_qty,
coalesce(sum(coalesce(reserved.reserved_qty, 0)) FILTER (where entry_type = 'BACKORDER'), 0) as backorder_qty
from
(select
kdglog.entry_type,
kdglog.catalogno,
kdglog.distributor,
kdglog.entry_date,
kdglog.qty as reserved_qty
from kdglog
inner join
(select
catalogno,
distributor,
entry_type,
max(entry_date) as max_date
from kdglog
group by
catalogno,
distributor,
entry_type
) as latest
on
kdglog.catalogno = latest.catalogno and
kdglog.distributor = latest.distributor and
kdglog.entry_date = latest.max_date and
kdglog.entry_type = latest.entry_type
) as reserved
group by reserved.catalogno, reserved.distributor
) as res_selected
on
res_selected.catalogno = totals.catalogno
-- find all reserved per product
full outer join
(select
reserved.catalogno,
max(entry_date) filter (where entry_type = 'ORDER') as max_order,
coalesce(sum(coalesce(reserved.reserved_qty, 0)) FILTER (where entry_type = 'RESERVE'), 0) as reserved_qty,
coalesce(sum(coalesce(reserved.reserved_qty, 0)) FILTER (where entry_type = 'BACKORDER'), 0) as backorder_qty
from
(select
kdglog.entry_type,
kdglog.catalogno,
kdglog.distributor,
kdglog.entry_date,
kdglog.qty as reserved_qty
from kdglog
inner join
(select
catalogno,
distributor,
entry_type,
max(entry_date) as max_date
from kdglog
group by
catalogno,
distributor,
entry_type
) as latest
on
kdglog.catalogno = latest.catalogno and
kdglog.distributor = latest.distributor and
kdglog.entry_date = latest.max_date and
kdglog.entry_type = latest.entry_type
) as reserved
group by reserved.catalogno
) as res_back
on
res_back.catalogno = totals.catalogno
left join
catalognew
on
catalognew.catalogno = res_selected.catalogno
) as master
where
reserved_qty > 0 or backorder_qty > 0
order by
distributor, catalogno
) to '/tmp/onhand_20191212.csv' csv header