SytelineMaster

Part of the Syteline year end procedure is to run an utility to reset the YTD number in item warehouse form.  This needs to be done in timely manner.  If you forget to run it during your year-end, there is no rebalance utility to recalculate that these YTD number in Syteline.

The below small script is to help re-calculate and update that YTD numbers.

——- Checking ———-
select top 100
iw.item,
iw.whse,
(select SUM(qty) from matltran where item = iw.item and whse = iw.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘R’ and ref_type <> ‘K’ and ref_type <> ‘S’) — PO receiptor
or
(trans_type = ‘W’ and ref_type = ‘P’) — PO return.
) ) ‘YTD Purchase’,
(select SUM(qty) from matltran where item = iw.item and whse = iw.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘S’) — CO Shipment
or
(trans_type = ‘W’ and ref_type = ‘O’) — CO return.
) ) ‘YTD Sold’
from itemwhse iw
where item = ‘512653’

———– Actual Update ————-
update itemwhse
set qty_pur_ytd = (select SUM(qty) from matltran where item = itemwhse.item and whse = itemwhse.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘R’ and ref_type <> ‘K’ and ref_type <> ‘S’) — PO receiptor
or
(trans_type = ‘W’ and ref_type = ‘P’) — PO return.
) ),
qty_sold_ytd = (select SUM(qty) from matltran where item = itemwhse.item and whse = itemwhse.whse and trans_date > ‘1/1/2015’ and
((trans_type = ‘S’) — CO Shipment
or
(trans_type = ‘W’ and ref_type = ‘O’) — CO return.
) )