Dynamics GP– Process to invoice only fulfilled and shipped items for scheduled, drop-ship or call off sales orders

What are call off orders and scheduled orders?

If a customer wants to order 12km of cable supplying for a long running project, but only wants it supplied in 1km despatches over the next twelve weeks, perhaps to different sites. Then this is a call off or a scheduled order. Here we will talk about cable as an example, but it could be applied to most products.

If it is a call off order, then the customer will call/email/electronically transact to request (call off the order) another x km of cable for date x on an ad-hoc basis, often depending on the pace of the project they are working on.

If the customer project/requirement is more strongly project managed and the exact about of cable required at each delivery location is known ahead of time, then this is a scheduled order.

In both cases, usually the customer would want the whole project to be referenced by the one sales order and one sales order to match their own entire purchase order.

To make this work in GP I did the following:

Call off orders

For call off orders, the entire stock the customer is reserving is placed on one SOP sales line, assuming the contract stipulates the entire stock to be held (allocated to them) and available at all times. If not it becomes like scheduled order as described later, the stock purchased based on the forward ship dates of the sales lines.

To hold the line from being despatched immediately, the sales line is held with a forward ship date many years in the future. This is an exact date, set by convention so it can used to identify call off order lines in reporting. The call off order is then put in a pending despatch, hold batch, out the way. The physical stock may or may not be held in a physical location too to the order, if using multiple bins for GP, this becomes easy.

When the customer calls to get some more stock, a new line is added to the order for the quantity they require, with a forward ship date of when they now want it. Often this is for immediate despatch. That quantity is subtracted off the master line that is holding the stock (the first line we put on the order), thus the stock allocation moves from the first stock hold line to the new (to be despatched now) line.

A SQL job picks up that the order now has something to be despatched and the order is pushed into our custom despatch process pipeline. The order is picked and despatched. The outcome is an order with that new line fulfilled in the pending sales invoice batch. We will pick up on the invoicing again later in this post.

Scheduled orders

Scheduled orders are placed with one line per delivery address and shipping date. For our example there would be twelve lines on the sales order, each with a forward date of a different week and each with the appropriate site address as the ship to address. As with the call off orders, the order is placed into the pending despatch hold batch. The SQL job that monitors this batch sees when a forward despatch date is reached for a particular order line and then releases that line to the warehouse for picking and despatch.

The pick list we use (SQL server reporting services) will only print picks for the lines that at or past forward ship dates and not yet invoiced, the result being those picked lines are fulfilled and placed into a batch pending sales invoicing.

Partial sales order line Invoicing

I have a number of processes running against orders before they are invoiced, one of them is designed to help with partial invoicing like that caused by drop-ship orders, scheduled orders and call off orders. If the sales orders from our example were to be transferred to invoiced immediately, items not yet fulfilled would be invoiced. This is because the “quantity to invoice” on the sales order would be equal to whatever is allocated to the order and not what has actually been fulfilled. The SQL script below skims off any quantities to invoice above the fulfilled quantities, essentially setting the Quantity To Invoice = Quantity Fulfilled. This is only true for inventory items, service items do not have the concept of being fulfilled in the same sense.

What about drop-ship orders?

Drop shipping of orders from our suppliers to customer also occurs and for those orders the GP shipping window is used to flag the purchase orders as shipped from the supplier. This makes the sales order available for invoicing before the purchase invoice from the supplier has reached us. There is a flaw in GP that if a partially shipped line were to be transferred to invoice, the whole order quantity ends up going to invoice, even though only some of the sales order line has been shipped by the supplier to the customer. The same SQL script used for skimming the quantities on the scheduled orders off, also looks into the shipping notification window table and ensures that the “quantity to invoice” of the sales line is set to the quantity shipped of that table. Thus only the correct invoice quantities are transferred.

Logging what happens is a good idea

For logging purposes the SQL script makes a record of any “adjustments” it makes to quantities for later diagnosis if there should be any issues (never had to use these records yet). When originally designed, this table was also going to be used to restore the quantities back onto the sales order that it had removed before transfer to invoice. Happily it turns out that GP recalculates the quantity to invoice field value of the sales order, after transfer to invoice, thus restoring the order to its nominal state, so no further intervention is required.

Closing the cycle

The final step to close the process is that the orders are returned to the pending despatch batch by a post transfer SQL script. That is unless no further order lines have remaining un-invoiced quantities. In that case the order will, as with standard GP behaviour end up in history.

-- ==========================================================================================
-- Author:        Tim Wappat
-- Create date: 2012/08/11
-- Description:
--        Procedure ran by the invoicing application
--        before transferring sales orders to Invoices  
--        Ensures that only the qty fulfilled gets invoiced.
--        (or qty shipped for drop ship orders)
--      ToInvoice value = Qty Fulfilled so only despatched items are invoiced
-- 
--        Update the QtyToINV of SOP10200 (sales lines) = QtyFULFI (Qty Fulfilled) 
--        squirt the original values into a table as a log record of activity
--        Logs_SOP_OrderToInvQtyAdjustments
--
-- Updates:
--        2016/09/29 Added awareness of shipping notification table and drop ships
-- ==========================================================================================
ALTER PROCEDURE [canford].[Jobs_SopSalesOrderQtyToInvoiceAdjustment] (
    @SOPTYPE SMALLINT = 0
    ,@SOPNUMBE AS CHAR(21) = NULL
    ,@SOPBACHNUMB AS CHAR(15) = NULL
    )
AS
BEGIN
    DECLARE @AdjustmentID AS INT;

    -- Get the next adjustment ID
    -- Note: this is not locked, so don't run this proc twice at same time
    SELECT @AdjustmentID = ISNULL(MAX(AdjustmentID) + 1, 1)
    FROM Logs_SOP_OrderToInvQtyAdjustments;

    IF (
            @SOPBACHNUMB IS NULL
            OR LEN(RTRIM(@SOPBACHNUMB)) = 0
            )
    BEGIN
        -- Process by Document
        BEGIN TRANSACTION;

        -- Update the sales lines NON-DROPSHIP
        UPDATE SalesLines
        SET QTYTOINV = QTYFULFI
        OUTPUT DELETED.SOPTYPE
            ,DELETED.SOPNUMBE
            ,DELETED.LNITMSEQ
            ,DELETED.CMPNTSEQ
            ,DELETED.QUANTITY
            ,DELETED.QTYFULFI
            ,DELETED.QTYTOINV
            ,DELETED.QTYTOINV - INSERTED.QTYTOINV
            ,getdate()
            ,@AdjustmentID
            ,0
        INTO Logs_SOP_OrderToInvQtyAdjustments
        FROM SOP10200 AS SalesLines
        INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
        WHERE SOPTYPE = @SOPTYPE
            AND SOPNUMBE = @SOPNUMBE
            AND SalesLines.QTYTOINV > 0
            AND SalesLines.DropShip = 0
            AND SalesLines.NONINVEN = 0
            AND (
                InventoryItem.ITEMTYPE = 1
                OR InventoryItem.ITEMTYPE = 2
                )
            AND SalesLines.QTYTOINV != SalesLines.QTYFULFI;

        -- Update the sales lines for DROPSHIP lines
        -- Qty to go to invoice on drop ship lines should be the qty shipped from ship notification table
        UPDATE SalesLines
        SET QTYTOINV = ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
        OUTPUT DELETED.SOPTYPE
            ,DELETED.SOPNUMBE
            ,DELETED.LNITMSEQ
            ,DELETED.CMPNTSEQ
            ,DELETED.QUANTITY
            ,DELETED.QTYFULFI
            ,DELETED.QTYTOINV
            ,DELETED.QTYTOINV - INSERTED.QTYTOINV
            ,getdate()
            ,@AdjustmentID
            ,0
        INTO Logs_SOP_OrderToInvQtyAdjustments
        FROM SOP10200 AS SalesLines
        INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
        JOIN SOP60100 Link ON Link.SOPTYPE = SalesLines.SOPTYPE
            AND Link.SOPNUMBE = SalesLines.SOPNUMBE
            AND Link.LNITMSEQ = SalesLines.LNITMSEQ
            AND Link.CMPNTSEQ = SalesLines.CMPNTSEQ
        JOIN taShipmentNotification ShipmentNotification ON Link.PONUMBER COLLATE DATABASE_DEFAULT = ShipmentNotification.PONUMBER COLLATE DATABASE_DEFAULT
            AND Link.ORD = ShipmentNotification.ORD
        WHERE SalesLines.SOPTYPE = @SOPTYPE
            AND SalesLines.SOPNUMBE = @SOPNUMBE
            AND SalesLines.QTYTOINV > 0
            AND SalesLines.DropShip = 1
            AND SalesLines.NONINVEN = 0
            AND (
                InventoryItem.ITEMTYPE = 1
                OR InventoryItem.ITEMTYPE = 2
                )
            AND SalesLines.QTYTOINV != ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
            AND SalesLines.DROPSHIP = 1;

        COMMIT TRANSACTION;

        RETURN 0
    END
    ELSE
    BEGIN
        BEGIN TRANSACTION;

        -- Update the sales lines by batch for NON-DROP SHIP
        UPDATE SalesLines
        SET QTYTOINV = QTYFULFI
        OUTPUT DELETED.SOPTYPE
            ,DELETED.SOPNUMBE
            ,DELETED.LNITMSEQ
            ,DELETED.CMPNTSEQ
            ,DELETED.QUANTITY
            ,DELETED.QTYFULFI
            ,DELETED.QTYTOINV
            ,DELETED.QTYTOINV - INSERTED.QTYTOINV
            ,getdate()
            ,@AdjustmentID
            ,0
        INTO Logs_SOP_OrderToInvQtyAdjustments
        FROM SOP10200 AS SalesLines
        INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
        INNER JOIN SOP10100 AS SalesHeader ON SalesHeader.SOPTYPE = SalesLines.SOPTYPE
            AND SalesHeader.SOPNUMBE = SalesLines.SOPNUMBE
        WHERE SalesHeader.BACHNUMB = @SOPBACHNUMB
            AND SalesLines.QTYTOINV > 0
            AND SalesLines.DropShip = 0
            AND SalesLines.NONINVEN = 0
            AND (
                InventoryItem.ITEMTYPE = 1
                OR InventoryItem.ITEMTYPE = 2
                )
            AND SalesLines.QTYTOINV != SalesLines.QTYFULFI
            AND SalesLines.SOPTYPE = 2
            AND SalesLines.DROPSHIP = 0;

        -- For DROP-SHIP items
        UPDATE SalesLines
        SET QTYTOINV = ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
        OUTPUT DELETED.SOPTYPE
            ,DELETED.SOPNUMBE
            ,DELETED.LNITMSEQ
            ,DELETED.CMPNTSEQ
            ,DELETED.QUANTITY
            ,DELETED.QTYFULFI
            ,DELETED.QTYTOINV
            ,DELETED.QTYTOINV - INSERTED.QTYTOINV
            ,getdate()
            ,@AdjustmentID
            ,0
        INTO Logs_SOP_OrderToInvQtyAdjustments
        FROM SOP10200 AS SalesLines
        INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
        INNER JOIN SOP10100 AS SalesHeader ON SalesHeader.SOPTYPE = SalesLines.SOPTYPE
            AND SalesHeader.SOPNUMBE = SalesLines.SOPNUMBE
        JOIN SOP60100 Link ON Link.SOPTYPE = SalesLines.SOPTYPE
            AND Link.SOPNUMBE = SalesLines.SOPNUMBE
            AND Link.LNITMSEQ = SalesLines.LNITMSEQ
            AND Link.CMPNTSEQ = SalesLines.CMPNTSEQ
        JOIN taShipmentNotification ShipmentNotification ON Link.PONUMBER COLLATE DATABASE_DEFAULT = ShipmentNotification.PONUMBER COLLATE DATABASE_DEFAULT
            AND Link.ORD = ShipmentNotification.ORD
        WHERE SalesHeader.BACHNUMB = @SOPBACHNUMB
            AND SalesLines.QTYTOINV > 0
            AND SalesLines.DropShip = 1
            AND SalesLines.NONINVEN = 0
            AND (
                InventoryItem.ITEMTYPE = 1
                OR InventoryItem.ITEMTYPE = 2
                )
            AND SalesLines.QTYTOINV != ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
            AND SalesLines.SOPTYPE = 2;

        COMMIT TRANSACTION;

        RETURN 0
    END
END

The scripts work with our particular configuration of GP but I can not emphasize enough that full testing and validation of the scripts for your own application and circumstances is essential.