Error on Running Total: Part Tran History

Kinetic ERP
,
1

Continuing the discussion from PartTran BAQ: Calculating the "Running Total" of the History Tracker:

Create a BAQ to try to imitate the behavior of PartTranHistory. I follow the query in the post, but the running total doesn’t calculate correctly, but only in specific tran types.

This is the current return values (from right to left Running Total, Output, Input)

kardex1
kardex11561×516 33.7 KB

the query that I use to calculate this is:

	((
   CASE
     WHEN PartTran.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK')
       THEN 1 
     WHEN PartTran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR', 'PLT-STK')
       THEN -1
     ELSE 0
   END
 ) * PartTran.TranQty) as [Calculated_Movimiento],
	(CASE WHEN Movimiento > 0 THEN Movimiento ELSE 0 END) as [Calculated_Entrada],
	(CASE WHEN Movimiento < 0 THEN Movimiento ELSE 0 END) as [Calculated_Salida],
	(SUM(Movimiento) OVER(PARTITION BY PartTran.PartNum, PartTran.Plant ORDER BY PartTran.TranNum)) as [Calculated_Saldo]

This calculation error doesn’t always appear

2

Is your issue that the highlighted transaction (STK-UKN) appears to be for a qty of 1, yet the running balance seems to adjust by 3?

3

yes, but I still don’t know what the problem could be, I think it must be something related to the partition but I found this, I even changed the query by adding the type of transaction.

[Saldo] = SUM((case  
    when pt.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK') AND pt.TranClass IN ('A', 'R') then 1 
    when pt.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR', 'PLT-STK') AND pt.TranClass IN ('I') then -1 
    else 0
end) * pt.TranQty) OVER(PARTITION BY pt.PartNum, pt.Plant ORDER BY pt.TranNum ROWS UNBOUNDED PRECEDING)

it returns me the results in this way

kardex2
kardex21667×464 21.4 KB

4

Is that 3rd column the trannum? You shouldn’t be getting multiple copies of a transaction. Double check your joins to see why you’re getting some dup patrtran records

5

these are my only joins i have with the table

FROM
	Erp.PartTran pt (nolock)
LEFT JOIN
	Erp.Reason rs (nolock) ON
	pt.Company = rs.Company AND
	pt.InvAdjReason = rs.ReasonCode
INNER JOIN
	Erp.Warehse wh (nolock) ON
	pt.Company = wh.Company AND
	pt.WareHouseCode = wh.WarehouseCode
INNER JOIN
	Erp.WhseBin wb (nolock) ON
	pt.Company = wb.Company AND
	pt.WareHouseCode = wb.WarehouseCode AND
	pt.BinNum = wb.BinNum AND
	wh.WarehouseCode = wb.WarehouseCode
6

ok, I found that the transaction number is repeated because the reason type belongs to 3 types A, D, and M

kardex4

but I don’t know in what cases it is used

7

I think it’s because you created 3 reason codes with the same code, but for different types.

You might need to add a join condition where the reason type is used for the trantype.

Edit

"Indicates the Type of reason. Can be “S” - Scrap, “R” - Rework, “M” - Inventory Adjustments, “A” - RMA, “D” - DMR, “Q” - Quality Assurance “C” - Service Calls “W” Win CRM “L” - Loss CRM, “T” Task CRM, “H” - Part On Hold, “E” ECO Task, “H” HelpDesk, “C” Service Call, “U” Course Result, ““F” Field Service Contract.”

1 Like
8

Thanks, in the end that worked, what did work was to relate the TranType with the ReasonType