I’d like to share the following SQL query I put together with information from a couple posts on this site. Maybe someone will find this helpful to find the inventory part transaction balance of a part on a certain date. Each transaction has a multiplication factor which determines if the part is adjusted in, adjusted out, or not changed at all.
This post in particular was helpful: PartTran TranQty True Sign - #3 by system
Here’s the query you can run in SSMS. Just fill in the four parameters and execute.
USE E10Live;
GO
DECLARE @Company AS VARCHAR(2) = '' -- fill this in
, @PartNum AS VARCHAR(50) = '' -- fill this in
, @Plant AS VARCHAR(10) = '' -- fill this in
, @CutoffDate AS DATE = '' -- fill this in (YYYY-MM-DD)
, @RunningTotal AS DECIMAL(12,2) = 0;
SELECT
@CutoffDate AS 'CutoffDate'
, SUM(
CASE
-- NEGATIVE ADJUSTMENTS
WHEN pt.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM',
'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR')
THEN @RunningTotal + (-1 * pt.TranQty)
-- NO ADJUSTMENTS
WHEN pt.TranType IN ('ADJ-DRP', 'ADJ-MTL', 'ADJ-PUR', 'ADJ-SUB', 'ASM-INS', 'DMR-MTL',
'DMR-REJ', 'DMR-SUB', 'DRP-CUS', 'INS-ASM', 'INS-DMR', 'INS-MTL',
'INS-REJ', 'INS-SUB', 'KIT-CUS', 'MFG-CUS', 'MFG-PLT', 'MFG-VAR',
'MFG-VEN', 'MFG-WIP', 'MTL-INS', 'MTL-DMR', 'PLT-MTL', 'PUR-CUS',
'PUR-DRP', 'PUR-INS', 'PUR-MTL', 'PUR-SUB', 'PUR-UKN', 'RMA-INS',
'SUB-INS', 'SUB-DMR', 'UKN-CUS', 'WIP-MFG')
THEN 0
-- POSITIVE ADJUSTMENTS
WHEN pt.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK',
'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK')
THEN @RunningTotal + (1 * pt.TranQty)
END ) AS 'BalanceAtCutoff'
FROM [dbo].PartTran AS pt WITH (NOLOCK)
WHERE pt.Company = @Company
AND pt.PartNum = @PartNum
AND pt.Plant = @Plant
AND pt.TranDate <= @CutoffDate
(I know the 0 case has no effect but I just want to document the TRAN types that don’t affect inventory level inside the query)