I am ordering by sys date and sys time. We are accounting for ALL transaction types, not just inventory.
This is the boxed version that we use now on the same part number as my above baq. What is confusing to me, is that the begin qty is defined by Epicor as the on hand quantity before the transaction. So with our STK-CUS transactions, I am unsure why the begin qty would be 0, as we had parts on hand.
I’d add a column to the BAQ to show the “signed TranQty” (TranQty with the sign derieved from the TranType). Make sure your SignedTranQty for STK-CUS isn’t zero.
Thank you Calvin. I will try that.
Do I need to create a calculated field that assigns (1, -1 or 0) to each transaction type or is that value already available to me per Transaction Type in Epicor?
Also, The Beginning Qty doesn’t look right. Shouldn’t it be the RunningTotal from the prior row?
And AfterQty should equal BeginQty + Signed TranQty.
Try ordering by TranNum instead. That will ensure that “simultaneous transaaction” (like the MFG-STK and the associated ADJ-CST) are in the right order.
As a warning, I have found that there are sometimes when an inventory transaction occurs and there is no before or after qty listed. So be careful.
Yes. There are several post on here that give the full list of tran types with the multipler for each.
1 Like
If I were to get the “signed” TranQty programmed correctly, and used (BeginQty + Signed TranQty) it looks as if it would just try to add it to the begin qty, which reads 0 - for these STK-CUS transactions.
1 Like
This is what I am noticing. We had 2,364 on hand before 3/23 STK-CUS transaction and the begin qty reads 0. Hmmmm…
FYI - The Data Dictionary says that the BeginQty
On Hand Quantity before the part costing calculations were run.
SO I’d say that’s only used for part trans that do costing.
Edit
Even stranger…
Issuing material to a job creates a STK-MTL with a positive TranQty, and the Before and After fields are always zero. (Red rectangles in BAQ of PartTRan below)
While Returning material from a job creates a STK-MTL with a negative TranQty, and the Before and After fields have values.(Blue rectangle in BAQ of PartTRan below)
I’d say the Before and After Qty fields can’t be used for running sum.
Thats so funky. I found the baq that Epicor uses in its Part Transaction grid (zES-PartTran) but it does not contain the calculated “Running Total” field. I imagine the grid is a system dashboard but I can’t find it. Thought maybe I could look at the calculation on dashboard level. But alas.
1 Like
Is the end goal to just know the QOH after each PartTran? Or do you need to show changing cost as well?
If it’s just the first, then limit the BAQ to just Trans of the type that affect inventory.
I was thinking that as well. The powers that be are only concerned about inventory. But that leaves me with my issues with the STK-CUS, which doesnt adjust QOH because of emply values got begin and after qtys. Thats the clincher.
Maybe I need to back up a bit. I made a grid to replace the Transaction grid because boss wants to see customer PO number (from OrderHead) so I had to copy original query (Zes_PartTran) and added orderhead. I expected the running total to be calculated at the query level and that I would be able to use Epicor’s existing logic. If there is another way to add a non-related field to the grid collection, I would go that route at this point.
Ignore the Before and After Qty’s. You’ll have to use some moderately tricky techniques in the BAQ to make a running sum.
Something like a “Sum over Partiton” Search this site there’s some examples on running sum.
Make a CalcField SignedTranQty with expression
(case
when PartTran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR') then -1
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
else 0
end) * PartTran.TranQty
Then another Calc field RunningQOH with the expression
SUM(SignedTranQty) OVER (Order By PartTran.TranNum)
Edit
You don’t need to select the GroupBy box on the other fields like you would with a plain SUM() function.
7 Likes
I just had to write this for a report. This gives you a running total per PartTran.TranNum for transactions grouped by company, part and lot. Also, I am not considering all TranTypes or Plants. The big thing that you want is the ROWS UNBOUNDED PRECEDING at the end of your statement.
SUM(CASE WHEN PartTran.TranClass IN (‘A’,‘R’) THEN PartTran.TranQty1 WHEN PartTran.TranClass IN (‘I’) THEN -1PartTran.TranQty END) OVER (PARTITION BY PartTran.Company, PartTran.PartNum, PartTran.LotNum ORDER BY PartTran.TranNum ROWS UNBOUNDED PRECEDING)
I have this in a Subquery in the BAQ and then join to the top level query by TranNum. I then apply a Table Criteria for the Subquery to filter the records to the top level records because WHERE’s are a lot faster than joins in all of my tests.
3 Likes
Thank you, I will try this!
Hey Alice,
Looks like others got here before me, but yes, there are TranTypes that need to be set to negative to get the calculations right. I marked @ckrusen post as solution.
1 Like
Just a follow-up. I used
SUM(SignedTranQty) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum)
to make the subscribe function in grid work. Thank you all!
1 Like