Calculate Inventory running total

[mchinsky (Mark F Chinsky)]

So if you look at the Part Transaction History Tracker, it shows you a running total of inventory based on the quantities and transaction types.

I have the inventory transactions tech ref, but it doesn’t describe the sign changes as to how to use it to calculate the running total. For some analytics I need to simulate the calculation int he Running Total column in the Part Transaction history tracker, but I don’t know for sure which transactions should add, subtract or leave alone (i.e. move bin to bin).

I can make alot of educated guesses but does anybody know where I can find out for sure the logic behind that or has anyone figured this out already?

[markdamen]

I’m sure there has been a newer post on this topic, but here is one where @josecgomez provided a -1, 0 or 1 against each Tran Type indicating what effect it has to stock.

PartTran TranQty True Sign

Does anyone have a list of the “True Sign” of these Part Transactions? For example I just realized that STK-CUS show positive when you Ship out and negative when you reverse it… (which is damn stupid)… I need to know how each of these transactions affects inventory regardless of the sign they show in the Part Tran table IE STK-CUST PartTran Positive means NEGATIVE (REMOVE) Inventory Etc.. 'PUR-INS' 'PUR-MTL', 'PUR-STK', 'PUR-SUB' 'PUR-UKN' 'STK-ASM' 'STK-CUS' '…

[MikeGross]

There is a technical reference guide called “Hierarchy of Inventory Trans” that you can grab from EpicWeb and that will help a lot to understand all the Transaction Types.

[A.Baeisa (Al)]

@mchinsky
i am not sure if this what you looking for, but this is my BAQ to calculate inventory/stock values as per selected cut off date

Stock Status Report - Zero Value on Part

Bev_StockStatus.baq (41.8 KB) My Stock Status BAQ to calculate Running Total On hand at any given date, and this is the query phrase in case if you are on early than 10.1.400.20 Epicor version: select [Part].[Company] as [Part_Company], [Part].[PartNum] as [Part_PartNum], [Part].[UOMClassID] as [Part_UOMClassID], [Part].[ProdCode] as [Part_ProdCode], [Part].[InternalUnitPrice] as [Part_InternalUnitPrice], ((case when (TTLQtyToStock.Calculated_TTLQtyToStock) is null then 0 else TTLQtyToS…

[mchinsky (Mark F Chinsky)]

So looking at your query, are you saying basically to ignore all transactions unless they have STK on one side or the other of the transaction type with the exception of Adjustments?

[A.Baeisa (Al)]

if you only want to know the Stock level, then yes i tested it in my environment against the running total in Part Transaction History, for many parts and it gave the correct answers, you more than welcome to check/validate the principle in your environment and correct it if applicable, please let me know if you find that i missed any transaction type