I'm about to perform a BAQ (Biochemical Quantity) and need to find out how much stock is on hand between the Start Date and the End Date

Kinetic ERP
1

I’m about to perform a BAQ (Biochemical Quantity) and need to find out how much stock is on hand between the Start Date and the End Date.

I don’t know where to start. Does anyone have a BAQ like this?

2

There is a runningtotal column on parttran, i cant remember if you can get away from pulling the max(parttranseq) for each partnum within date range 1989-01-01 to the end date, and taking the runningtotal from it. that might work?

1 Like
3

Is it possible to split the Lotnum?

4

you need parttran in a subquery, group by partnum and lotnum, with table criteria to date range 1989-01-01 to end date, put a calculated field on it to calculate max(parttranseq)

Then in your top level query, join the subquery to parttran based on the parttranseq, and pull out the runningtotal

5

Unfortunately the running total isn’t stored anywhere, it’s just calculated for display at runtime. There are prior threads on how to calculate it floating around though. It’s confusing to calculate because epicor stores transactions that do and do not affect imventory quantities all in one table.

Or you can just run the stock status report which will give the quantity (but not cost) as of a prior date).

3 Likes
6

This video will teach you how to get running sums, if that’s actually what you need.

this statement though:

Doesn’t really make sense. How much stock is on hand would indicate a singular amount, but between a start and end date is a range and the stock number could change while in that range, it’s not a single point in time. So you’re going to have to define that better in order to be able to do anything here.

2 Likes
8

The parttran database doesn’t have a runningtotal. Is there another way to calculate it?