How to deal with backdated transactions on running totals

Epicor ERP 10
, ,
1

I am trying to write a query to show part adjustment percentages based on the system on hand quantity at the time the inventory adjustment was made. This is in an effort to show inventory accuracy trends over time. I know cycle counting does this, and we are using cycle counting, but there is a backstory here that involves working around the cycle count system to more favorably report accuracy so the data from our cycle count tolerance reports is not accurate.

Within our system are backdated transactions and this is causing me to be unable to get an accurate quantity at a given transaction occurence.

I have made a custom field in a BAQ to invert the sign orf the PartTran.TranQty when the PartTran.TranType includes STK-MTL or STK-CUS, all others post as PartTran.TranQty.

case
when PartTran.TranType IN (‘STK-MTL’, ‘STK-CUS’) then -PartTran.TranQty
when PartTran.TranType = ‘ADJ-CST’ or PartTran.TranType = ‘RMA-INS’ then 0
else PartTran.TranQty
end as ActTransAmount

Then to total all of these inventory transactions I am using the SUM() OVER (PARTITION BY ORDERBY).

SUM(ActTransAmount) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum)

The problem with this method is that it does not take into account backdated transactions where the PartTran.TransNum occurs after other inventory transactions on this part number. Ordering by PartTran.SysDate or PartTran.TranDate or a combination thereof also does not give me an accurate number.

I am making the assumption that Epicor handles this by a posting process that is triggered by transactions that fixes records in the Part Transaction History Tracker to accurately give the correct number in the Running Total column of that form.

  1. Does Epicor store this information anywhere?
  2. If not then is there a reliable way to get this appropriately calculated?
2

For anyone interested, I was able to get this resolved. One issue that I was not immediately aware of when first looking at this is that the running total in the Part Transaction History Form does not include quantity stored within a bin flagged as nonnettable. Once I removed the transactions to the nonnettable bins and stored the transaction qty in a calculated field to invert the sign for transaction quantities related to STK-CUS, STK-MTL, etc. I used SUM(CalcField) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranDate, PartTran.TranNum, PartTran.SysDate, PartTran.SysTime) to get the transactions to line up exactly.