Order backlog as of date

Kinetic ERP
1

Hi !
Is there some way to get out data from Epicor on how much the order backlog has been at a specific date in time? I guess there is nothing ready-made (?), but do you know if it is difficult to make a BAQ on this one, or maybe you have already done one? Thanks for your help!
BRgds Christoffer

2

What do you mean by this? How many sales order releases are overdue to ship?

3

I mean how many sales orders we have had in the system, which have not been delivered or invoiced at a certain point in time: nothing to do with overdue. I would like to check the history: for example, how many orders did we have open in the end of August 2022 compared to how many orders we have open in the end of August 2023

4

You would have to look at the individual releases and when they shipped. Then query where orderdate >= x and (shipment date<=x or no shipment yet)

It gets really complicated with partial shipments, because then what you really need to calculate is outstanding qty/dollars as of x.

1 Like
5

OK, thanks for the hint! This is not scientific, so I would say that we could live with the possible flaws of partial shipments. Did you have already yourself this kind of BAQ, or did you just come up with this now?

6

Yes somewhere, I can go digging for it later.

7

Thanks a lot: I could work with it tomorrow then :slight_smile:

8

I have a scheduled ubaq that runs nightly and writes the backlog as of the first day of the fiscal period. We also have a daily backlog that is used for updating. You may also need to learn about the Book tables which hold all of the changes to orders that are not shipments. Date, quantity, price and deletions.

E10CurrentBacklog.baq (56.0 KB)
CurrentBacklogToUD.baq (65.9 KB)

1 Like
9

Thanks Greg: we will check those BAQs. A daily backlog we have already, but it is not possible to give in a date in history there to check what the backlog was at that specific point in time

10

Rather than try to unwind all of the changes from today, if you need that on a regular basis I would do something like my monthly write to UD28

11

Sorry I meant to come back to this. The BAQ I have is way too messy and way too much company-specific logic to post here. I agree that going forward its a lot cleaner to just store the backlog at a point in time for reference rather than trying to recreate it in the past.

But if you really want to recreate in the past, you basically have to start with the order release and the shipment, and calculate the unshipped dollars on each release as of a given date based on the date of the shipment. So open quantity on the release is OurReqQty - (OurJobShippedQty + OurStockShippedQty) WHERE ShipHead.ShipDate<= [my asof date] and OrderHed.OrderDate>= [my asof date]. This ignores drop shipments and releases that got added to the order after the order date.
Then once you have the open quantity, you have to go up to the order line and figure out the backlog dollars. The simplest calculation is just unshipped qty*unit price. But if you are shipping in different units than your IUM or different currency than your base currency, your price per code isn’t E, or you have order based or line discounts, then that logic gets significantly more complicated.

1 Like
12

Thanks! Sounds a bit complex, but there has to be challenges in life :slight_smile:

1 Like