DECLARE @sumtrans Numeric(19,5)
DECLARE @qtyonhnd Numeric(19,5)
DECLARE @StartingDate DATE = '11/17/2021'
DECLARE @itemnmbr VARCHAR(31) = 'GENSH~HIGHPOINTARTISANBLEND~QS'
DECLARE @locncode VARCHAR(11)
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
select locncode
from iv00102 iq
WHERE iq.LOCNCODE > ''
AND iq.ITEMNMBR = @itemnmbr
ORDER BY 1
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @locncode
if @@fetch_status <> 0 begin
break
end
--get the transactions
SELECT
CASE WHEN it.TRXSORCE LIKE 'rec%' THEN 'Receipt'
WHEN it.TRXSORCE LIKE 'ivadj%' THEN 'Adjustment'
WHEN it.TRXSORCE LIKE 'sls%' THEN 'SOP Document'
WHEN it.TRXSORCE LIKE 'IVTFR%' THEN 'Transfer'
ELSE it.TRXSORCE END AS TrasnactionSource,
it.DOCNUMBR, it.ITEMNMBR,
IIF(it.TRXSORCE LIKE 'IVTFR%',-1,1) * CONVERT(INT,(it.TRXQTY * it.QTYBSUOM) / 460) AS cubes,
it.DEX_ROW_ID, FORMAT(DATEADD(HOUR,-5,ih.DEX_ROW_TS),'MM/dd hh:mm') AS TrxDateTime
INTO #tmp
FROM iv30300 it
LEFT JOIN iv30200 ih ON ih.DOCNUMBR = it.DOCNUMBR
WHERE ITEMNMBR = @itemnmbr
AND TRXLOCTN = @locncode
AND it.docdate >= @StartingDate
UNION ALL
SELECT 'Transfer' AS TrasnactionSource,
it.DOCNUMBR,
it.ITEMNMBR,
CONVERT(INT,(it.TRXQTY * it.QTYBSUOM) / 460) AS cubes,
it.DEX_ROW_ID,
FORMAT(DATEADD(HOUR,-5,ih.DEX_ROW_TS),'MM/dd hh:mm') AS TrxDateTime
FROM iv30300 it
LEFT JOIN iv30200 ih ON ih.DOCNUMBR = it.DOCNUMBR
WHERE ITEMNMBR = @itemnmbr
AND it.TRNSTLOC = @locncode
AND it.docdate >= @StartingDate
AND it.TRXSORCE LIKE 'IVTFR%'
--get the sum of transactions
SELECT @sumtrans = ISNULL(SUM(cubes),0)
FROM #tmp
--get the quantity on hand for this yard
SELECT @qtyonhnd = iq.QTYONHND /460
FROM iv00102 iq
WHERE ITEMNMBR = @itemnmbr
AND iq.LOCNCODE = @locncode
SELECT *, SUM(cubes) OVER (ORDER BY t.DEX_ROW_ID) AS runningtot
FROM (
SELECT * FROM #tmp
UNION all
SELECT @locncode,'','Starting Value',@qtyonhnd - @sumtrans,0,null
) t
ORDER BY t.DEX_ROW_ID
DROP TABLE #tmp
END
CLOSE curName
DEALLOCATE curName