Running Total Error BAQ: Part Tran History

Kinetic ERP
1

Continuing the discussion from Error on Running Total: Part Tran History:

Hi all, I wanted to recreate the part transaction history report into a BAQ and have taken all of your input into account and used your suggestions. However it is calculating the incorrect running total compared to the Part Transaction History Tracker.

I have the calculated signed total as:

(case
when PartTran.TranType IN (‘STK-ASM’, ‘STK-CUS’,‘STK-STK’, ‘STK-INS’, ‘STK-KIT’, ‘STK-FAM’, ‘STK-MTL’, ‘STK-PLT’, ‘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

and my sum of running total as:

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

In the tracker I have this amount:

image
image938×1037 18.9 KB

But in my BAQ I have this amount:

image
image593×559 8.65 KB

What can I be doing to begin to check why this may be wrong? The number of records returned by both are the same so I assume it may be the TranTypes that may be messing it up. Any thoughts?

2

are you on Kinetic or 10.2.200.12 ?
Latest kinetic versions have server trace flag to dump SQL statements executed for BAQ.

1 Like
3

I am currently on Epicor 10.2.500.40

4

The Tran History display is fairly tricky to reproduce.

First off, It’ shard to get the part trans sorted such that they match the order shown in Part Tran Hist. I think that its would be SORT BY trandate DESC, trannum DESC. You can’t use tran num, because an entry created on 2/23, but back dated to 2/20, will have a tran number greater than those trans created on 2/20.

You might never get the rows to completely match up. But you should have the balances match for the last transaction (closest to top of list) for a given day.

Edit:

And don’t ever sort the Tran History grid. The running balance doesn’t recalculate, so you won’t see meaningful info in the balance column.

1 Like
5

I sorted it by your suggestion but the balances are still off by quite a large amount. 2.1m vs 6m

6

For example, take the following rows from your sorted Part Tran History screen

image

Neither the line above or below the highlighted line is different by 52.02 (the highlighted row’s tran qty). But the partially highlighted row is.

That shows that the order of the rows for 1/23 are out of wack, as well as those for 1/24.

The partially highlgted row should be the last row for 1/23, and the fully highlighted row should be the first for 1/24

7

Your screen shot of the Part Tran History window doesn’t show a zero balance, but your BAQ assumes it.

Was 1/23/2023 the first ever Part Trans action for this part?

8

They seem to match now after being sorted:

image
image2178×881 85.6 KB

And no the first ever transaction was 5/19/2020 which matches for both.

9

Compare your BAQ results to to Tran Hist results for the first few transactions (back at 5/19/2020)

10

Took a look and I am not entirely sure but it does seem like ADJ-CST messed up the beginning calculations for this as it sometimes substracts but also adds at points. Is this normal? I am not entirely familiar with the TranTypes as this dashboard was requested by another department internally.

image
image1877×997 108 KB

11

ADJ-CST and ADJ-PUR should have no bearing on the balance.

None of the transactions in the red rectangle affected the balance.

From the Tran History screenshot
image

The balance on 5/19 was the opening balance.
The balance on 10/2 (after the QTY-ADJ), was increased by the QTY-ADJ tran qty
The balance on 10/2 (after the STK-MTL), was increased by the STK-MTL tran qty

1 Like
12

I excluded those two types and the numbers are now matching! Awesome, ADJ-CST and ADJ-PUR why should they have no bearing on the balance? That is interesting…

13

Why would changing the price of something affect the qty of it?

Don’t get me wrong, changing the cost will change the inventory VALUE (as an accounting term), but it won’t change the physical quantity on hand.

1 Like
14

No prob - I assumed on my end that only inventory moving transactions would appear here. Thank you for the help Calvin.