Cacher is the code snippet organizer for pro developers

 
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