Historic Sales Order Backlog

Kinetic ERP
1

I need to create a BAQ that will give me a sales order backlog for a historic date.

for Example: today 1/3/2025 what was the open order backlog as of 11/30/2024.

My initial thought is to criteria for Sales orders with Order dates <= 11/30/24 where the parttran (ship -CUS shipment transactions) have a date > 11/30/24 or no shipment as of 11/30/24.

Maybe it’s the holiday’s or just because it’s Friday, but i’m struggling with a clean way to do this and be as accurate as i can?

2

If you have bookings turned on in company configuration you can do it with the bookord, bookdtl and bookrel. If not I don’t know of a way since it is not just shipments but also cancellations and deleted sales orders that change your backlog.

2 Likes
3

I’ve got one in SQL … let me dig it up for you.

4

Try this out. It worked for us, but if you have different processes, this might not work 100% for you:

DECLARE @Date1 date = '2024-01-01';


WITH OrderInfo AS
(
    SELECT  d.Company
           ,d.OrderNum
           ,c.CustID
           ,CustName = c.Name
           ,d.OrderLine
           ,r.OrderRelNum
           ,RequestDate = r.ReqDate
           ,d.PartNum
           ,d.LineDesc
           ,OrderQty = r.SellingReqQty
           ,d.OrdBasedPrice
           ,d.OpenLine
           ,r.OpenRelease
           ,h.VoidOrder
           ,d.VoidLine
           ,r.VoidRelease
    FROM    Erp.OrderDtl d
            INNER JOIN Erp.OrderRel r ON d.Company = r.Company AND d.OrderNum = r.OrderNum AND d.OrderLine = r.OrderLine
            INNER JOIN Erp.Customer c ON d.Company = c.Company AND d.CustNum = c.CustNum
            INNER JOIN Erp.OrderHed h ON d.Company = h.Company AND d.OrderNum = h.OrderNum
    WHERE   r.VoidRelease <> 1 AND  d.VoidLine <> 1 AND h.VoidOrder <> 1 
)
    ,ShipInfo AS
(
    SELECT  d.Company, d.OrderNum, d.OrderLine, d.OrderRelNum, h.ShipDate, ShipQty = d.SellingInventoryShipQty + d.SellingJobShipQty
    FROM    Erp.ShipDtl d
            INNER JOIN Erp.ShipHead h ON d.Company = h.Company AND d.PackNum = h.PackNum
    WHERE   (
                h.Invoiced = 1 OR   h.ShipStatus = 'SHIPPED'
            )
    UNION ALL
    SELECT  d.Company, d.OrderNum, d.OrderLine, d.OrderRelNum, h.ReceiptDate, ShipQty = d.OurQty
    FROM    Erp.DropShipDtl d
            INNER JOIN Erp.DropShipHead h ON d.Company = h.Company
                                             AND d.PackSlip = h.PackSlip
                                             AND d.VendorNum = h.VendorNum
                                             AND   d.PurPoint = h.PurPoint
    WHERE   (
                h.ARInvoiced = 1 OR h.ReceivedShipped = 1
            )
)
SELECT  o.Company
       ,o.CustID
       ,o.CustName
       ,o.OrderNum
       ,o.OrderLine
       ,o.OrderRelNum
       ,AnalysisDate = @Date1
       ,o.RequestDate
       ,o.PartNum
       ,o.LineDesc
       ,o.OrderQty
       ,ShipQty = ISNULL(s.TotalShipQty, 0)
       ,FutureShipQty = ISNULL(f.TotalShipQty, 0)
       ,BackOrderQty = o.OrderQty - ISNULL(s.TotalShipQty, 0)
       ,OpenSales = o.OrdBasedPrice * (o.OrderQty - ISNULL(s.TotalShipQty, 0))
FROM    OrderInfo o
        LEFT JOIN (
                      SELECT    Company, OrderNum, OrderLine, OrderRelNum, TotalShipQty = SUM(ShipQty)
                      FROM      ShipInfo
                      WHERE     ShipDate <= @Date1
                      GROUP BY  Company, OrderNum, OrderLine, OrderRelNum
                  ) s ON o.Company = s.Company AND   o.OrderNum = s.OrderNum AND o.OrderLine = s.OrderLine AND   o.OrderRelNum = s.OrderRelNum
        LEFT JOIN (
                      SELECT    Company, OrderNum, OrderLine, OrderRelNum, TotalShipQty = SUM(ShipQty)
                      FROM      ShipInfo
                      WHERE     ShipDate > @Date1
                      GROUP BY  Company, OrderNum, OrderLine, OrderRelNum
                  ) f ON o.Company = f.Company AND   o.OrderNum = f.OrderNum AND o.OrderLine = f.OrderLine AND   o.OrderRelNum = f.OrderRelNum
WHERE   o.RequestDate <= @Date1
        AND ((o.OrderQty - ISNULL(s.TotalShipQty, 0)) > 0)
        AND  CASE
                 WHEN o.OpenRelease = 0 AND  f.Company IS NULL THEN
                     1
                 ELSE
                     0
             END = 0;
2 Likes
5

Thank you Doug;
i’ll take a look at this i can see the logic

6

thanks Greg;
we do not have bookings turned on unfortunately.

7

If you do dropship orders, you’d need to check PartTran for DRP-CUS too.

OrderDate<=11/30/2024
Max(TranDate)<=11/30/2024 or is null (not yet shipped)
VoidRelease/Line/Header all false

1 Like