Inventory Figures Tab with more options?

[Ernst]

My client is looking for a Tab in Inventory similar to figures tab,
See his comment below, any body had similar request?? or Dine anything like this, I saw Mike did an investigation into it at one stage, but it was two complicated due to Min SOH entry etc. The client just wants to see Units and $$$ values


"In Inventory

In Figures. I can only select financial years. (July1st to June 30th) in the drop down box.


Is it possible to select 12 month sliding scale?

Or at the least choose calendar years as well?

BTW It is only the figures (units and $$ value)

Don’t need any of the budget stuff, SOH, finished goods, usages, etc ."

[SBarnes]

The short answer is reworking what is there would be too complicated but given that Jiwa has Form Custom Tabs(see under system setting), you could build a series of tabs all you need do is write a stored procedure for each tab that returns two result sets the first being the schema of the data and the second is the results data an example of one that is defined as a custom tab is below, Jiwa will do all the user interface part for you. So you could have tabs for current calendar year last calendar year etc. and just use getdate in the procedure to work out your years.

Code: Select all

ALTER PROCEDURE [dbo].[usp_Jiwa_Inventory_BackOrderFulfillments] @RecID CHAR(36), @IN_LogicalID CHAR(36) = NULL, @HR_Staff_StaffID CHAR(36) = NULL AS
SET NOCOUNT ON

DECLARE @Schema TABLE
(
   id INT IDENTITY NOT NULL,
   Name VARCHAR(50) NOT NULL,   
   Caption VARCHAR(50) NOT NULL,
   IsVisible BIT NOT NULL,
   DefaultWidth INT NOT NULL,
   IsDrillDown BIT NOT NULL,
   DrillDownSourceIDColumnName VARCHAR(50) NULL,
   DrillDownClassNameColumnName VARCHAR(50) NULL,
   FormatString VARCHAR(255) NULL,
   DecimalPlacesColumnName VARCHAR(50) NULL
)

-- First Result defines the schema
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'Priority', 'Priority', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'OrdersOnBackID', 'OrdersOnBackID', 0, 0, 0, NULL, NULL, NULL, NULL
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'DebtorAccountNo', 'Debtor Account No.', 1, 6, 1, 'DebtorID', 'DebtorDrillDownClassName', NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'DebtorName', 'Debtor Name', 1, 10, 1, 'DebtorID', 'DebtorDrillDownClassName', NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'DebtorID', 'DebtorID', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'DebtorDrillDownClassName', 'DebtorDrillDownClassName', 0, 0, 0, NULL, NULL, NULL, NULL
         
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InvoiceNo', 'Invoice No.', 1, 6, 1, 'InvoiceID', 'InvoiceDrillDownClassName', NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InvoiceID', 'InvoiceID', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InvoiceDrillDownClassName', 'InvoiceDrillDownClassName', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'CustomerOrderNo', 'Customer Order No.', 1, 6, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'Date', 'Date', 1, 6, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'Quantity', 'Quantity', 1, 6, 0, NULL, NULL, NULL, 'QuantityDecimalPlaces'

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'QuantityDecimalPlaces', 'QuantityDecimalPlaces', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'Warehouse', 'Warehouse', 1, 6, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'OrderNo', 'Purchase Order No.', 1, 6, 1, 'OrderID', 'PurchaseOrderDrillDownClassName', NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'OrderID', 'OrderID', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'PurchaseOrderDrillDownClassName', 'PurchaseOrderDrillDownClassName', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'SupplierName', 'Supplier Name', 1, 10, 1, 'CreditorID', 'CreditorDrillDownClassName', NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'CreditorID', 'CreditorID', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'CreditorDrillDownClassName', 'CreditorDrillDownClassName', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'ExpectedDeliveryDate', 'Expected DeliveryDate', 1, 6, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'HistoryTextComment', 'History Text Comment', 1, 6, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'QuantityConsumed', 'Quantity Consumed', 1, 6, 0, NULL, NULL, NULL, 'QuantityDecimalPlaces'

   SELECT Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName
   FROM @Schema
   ORDER BY id

DECLARE @DecimalPlaces SMALLINT

DECLARE @BackOrders TABLE
(
id INT IDENTITY NOT NULL,
OrdersOnBackID Char(20) COLLATE Database_Default,
Priority char(34),
InvoiceID Char(20) COLLATE Database_Default,
InvoiceNo Varchar(50) COLLATE Database_Default,
CustomerOrderNo Varchar(50) COLLATE Database_Default,
DebtorID Char(20) COLLATE Database_Default,
DebtorAccountNo Varchar(50) COLLATE Database_Default,
DebtorName Varchar(255) COLLATE Database_Default,
[Date] DATETIME,
Quantity DECIMAL(19,6),
LogicalID CHAR(20) COLLATE Database_Default,
PhysicalWarehouse varchar(255) COLLATE Database_Default,
LogicalWarehouse varchar(255) COLLATE Database_Default,
OrderID Char(20) COLLATE Database_Default,
OrderNo Varchar(50) COLLATE Database_Default,
CreditorID Char(20) COLLATE Database_Default,
CreditorAccountNo Varchar(50) COLLATE Database_Default,
CreditorName Varchar(255) COLLATE Database_Default,
ExpectedDeliveryDate DATETIME,
HistoryTextComment VARCHAR(255),
QuantityConsumed DECIMAL(19,6),
InvoiceType SMALLINT,
Processed BIT
)

DECLARE @PurchaseOrders TABLE
(
id INT IDENTITY NOT NULL,
CreditorID Char(20) COLLATE Database_Default,
SupplierName VARCHAR(200) COLLATE Database_Default,
OrderLineID CHAR(36) COLLATE Database_Default,
OrderID Char(20) COLLATE Database_Default,
OrderNo varchar(50) COLLATE Database_Default,
OrderDate DATETIME,
Quantity DECIMAL(19,6),
QuantityUsed DECIMAL(19,6),
ExpectedDeliveryDate DATETIME,
HistoryTextComment VARCHAR(255),
LogicalDescription varchar(255) COLLATE Database_Default,
PhysicalDescription varchar(255) COLLATE Database_Default,
QuantityDelivered DECIMAL(19,6),
Processed BIT
)

SELECT @DecimalPlaces = DecimalPlaces FROM IN_Main WHERE InventoryID = @RecID

IF @IN_LogicalID IS NULL
    BEGIN
      -- Do B2B backorders first
      INSERT INTO @BackOrders (OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], Quantity, LogicalID, LogicalWarehouse, PhysicalWarehouse, OrderID, OrderNo, CreditorID, CreditorAccountNo, CreditorName, ExpectedDeliveryDate, HistoryTextComment, QuantityConsumed, InvoiceType, Processed)
      SELECT OrdersOnBackID, Priority, SO_Main.InvoiceID, SO_Main.InvoiceNo, SO_Main.OrderNo, DB_Main.DebtorID, DB_Main.AccountNo,
      DB_Main.Name, IN_OnBackOrder.TranDate, IN_OnBackOrder.Quantity,
      IN_Logical.IN_LogicalID, IN_Physical.Description, IN_Logical.Description,
      PO_Main.OrderID, PO_Main.OrderNo, CR_Main.CreditorID, CR_Main.AccountNo, CR_Main.Name, PO_Lines.DeliveryDate, PO_Lines.HistoryText_Comment, IN_OnBackorder.Quantity, 1, 1
      FROM IN_OnBackorder
      JOIN SO_Main ON SO_Main.InvoiceID = IN_OnBackorder.InvoiceID
      JOIN DB_Main ON DB_Main.DebtorID = SO_Main.DebtorID
      JOIN IN_Logical ON IN_Logical.IN_LogicalID = IN_OnBackorder.IN_LogicalID
      JOIN IN_Physical ON IN_Physical.IN_PhysicalID = IN_Logical.IN_PhysicalID
      LEFT JOIN PO_Lines ON PO_Lines.OrderLineID = IN_OnBackOrder.OrderLineID
      LEFT JOIN PO_Main ON PO_Main.OrderID = PO_Lines.OrderID
      LEFT JOIN CR_Main ON CR_Main.CreditorID = PO_Main.CreditorID
      WHERE IN_OnBackorder.InventoryID =  @RecID
      AND (@IN_LogicalID IS NULL OR (IN_OnBackorder.IN_LogicalID = @IN_LogicalID))
      AND IN_OnBackorder.InvoiceType = 1
      ORDER BY IN_OnBackorder.Priority

      INSERT INTO @BackOrders (OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], Quantity, LogicalID, LogicalWarehouse, PhysicalWarehouse, OrderID, OrderNo, CreditorID, CreditorAccountNo, CreditorName, ExpectedDeliveryDate, HistoryTextComment, QuantityConsumed, InvoiceType, Processed)
      SELECT OrdersOnBackID, Priority, SO_Main.InvoiceID, SO_Main.InvoiceNo, SO_Main.OrderNo, DB_Main.DebtorID, DB_Main.AccountNo,
      DB_Main.Name, IN_OnBackOrder.TranDate, IN_OnBackOrder.Quantity,
      IN_Logical.IN_LogicalID, IN_Physical.Description, IN_Logical.Description,
      '', '', '', '', '', '', '', 0, 0, 0
      FROM IN_OnBackorder
      JOIN SO_Main ON SO_Main.InvoiceID = IN_OnBackorder.InvoiceID
      JOIN DB_Main ON DB_Main.DebtorID = SO_Main.DebtorID
      JOIN IN_Logical ON IN_Logical.IN_LogicalID = IN_OnBackorder.IN_LogicalID
      JOIN IN_Physical ON IN_Physical.IN_PhysicalID = IN_Logical.IN_PhysicalID
      WHERE IN_OnBackorder.InventoryID =  @RecID
      AND (@IN_LogicalID IS NULL OR (IN_OnBackorder.IN_LogicalID = @IN_LogicalID))
      AND IN_OnBackorder.InvoiceType = 0      
      ORDER BY IN_OnBackorder.Priority
    END
ELSE
    BEGIN
      -- Do B2B backorders first
      INSERT INTO @BackOrders (OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], Quantity, LogicalID, LogicalWarehouse, PhysicalWarehouse, OrderID, OrderNo, CreditorID, CreditorAccountNo, CreditorName, ExpectedDeliveryDate, HistoryTextComment, QuantityConsumed, InvoiceType, Processed)
      SELECT OrdersOnBackID, Priority, SO_Main.InvoiceID, SO_Main.InvoiceNo, SO_Main.OrderNo, DB_Main.DebtorID, DB_Main.AccountNo,
      DB_Main.Name, IN_OnBackOrder.TranDate, IN_OnBackOrder.Quantity,
      IN_Logical.IN_LogicalID, IN_Physical.Description, IN_Logical.Description,
      PO_Main.OrderID, PO_Main.OrderNo, CR_Main.CreditorID, CR_Main.AccountNo, CR_Main.Name, PO_Lines.DeliveryDate, PO_Lines.HistoryText_Comment, IN_OnBackorder.Quantity, 1, 1   
      FROM IN_OnBackorder
      JOIN SO_Main ON SO_Main.InvoiceID = IN_OnBackorder.InvoiceID
      JOIN DB_Main ON DB_Main.DebtorID = SO_Main.DebtorID
      JOIN IN_Logical ON IN_Logical.IN_LogicalID = IN_OnBackorder.IN_LogicalID
      JOIN IN_Physical ON IN_Physical.IN_PhysicalID = IN_Logical.IN_PhysicalID
      LEFT JOIN PO_Lines ON PO_Lines.OrderLineID = IN_OnBackOrder.OrderLineID
      LEFT JOIN PO_Main ON PO_Main.OrderID = PO_Lines.OrderID
      LEFT JOIN CR_Main ON CR_Main.CreditorID = PO_Main.CreditorID
      WHERE IN_OnBackorder.InventoryID =  @RecID
        AND (@IN_LogicalID IS NULL OR (IN_OnBackorder.IN_LogicalID = @IN_LogicalID))
      AND IN_OnBackorder.InvoiceType = 1
      ORDER BY IN_OnBackorder.Priority

        INSERT INTO @BackOrders (OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], Quantity, LogicalID, LogicalWarehouse, PhysicalWarehouse, OrderID, OrderNo, CreditorID, CreditorAccountNo, CreditorName, ExpectedDeliveryDate, HistoryTextComment, QuantityConsumed, InvoiceType, Processed)
      SELECT OrdersOnBackID, Priority, SO_Main.InvoiceID, SO_Main.InvoiceNo, SO_Main.OrderNo, DB_Main.DebtorID, DB_Main.AccountNo,
      DB_Main.Name, IN_OnBackOrder.TranDate, IN_OnBackOrder.Quantity,
      IN_Logical.IN_LogicalID, IN_Physical.Description, IN_Logical.Description,
      '', '', '', '', '', '', '', 0, 0, 0
      FROM IN_OnBackorder
      JOIN SO_Main ON SO_Main.InvoiceID = IN_OnBackorder.InvoiceID
      JOIN DB_Main ON DB_Main.DebtorID = SO_Main.DebtorID
      JOIN IN_Logical ON IN_Logical.IN_LogicalID = IN_OnBackorder.IN_LogicalID
      JOIN IN_Physical ON IN_Physical.IN_PhysicalID = IN_Logical.IN_PhysicalID
      WHERE IN_OnBackorder.InventoryID =  @RecID
        AND (@IN_LogicalID IS NULL OR (IN_OnBackorder.IN_LogicalID = @IN_LogicalID))
      AND IN_OnBackorder.InvoiceType = 0
      ORDER BY IN_OnBackorder.Priority   
    END

-- Find the purchase orders the backorders will likely be fulfilled with
DECLARE @TotalSOH DECIMAL(19,6)
DECLARE @QuantityBackOrder DECIMAL(19,6)
DECLARE @OrdersOnBackID CHAR(20)
DECLARE @Priority CHAR(34)
DECLARE @OrderID CHAR(20)
DECLARE @OrderLineID CHAR(36)
DECLARE @CreditorID CHAR(20)
DECLARE @CreditorName VARCHAR(255)
DECLARE @OrderNo VARCHAR(50)
DECLARE @ExpectedDeliveryDate DATETIME
DECLARE @HistoryTexT_Comment VARCHAR(255)
DECLARE @QuantityConsumed DECIMAL(19,6)
DECLARE @QuantityUsed DECIMAL(19,6)
DECLARE @TotalQuantityUsed DECIMAL(19,6)
DECLARE @Quantity DECIMAL(19,6)
DECLARE @BOid INT
DECLARE @IN_LogicalWarehouseID CHAR(20)
DECLARE @POid INT

SELECT @TotalSOH = COALESCE(SUM(QuantityLeft), 0)
FROM IN_SOH
WHERE InventoryID = @RecID
AND (@IN_LogicalID IS NULL OR (IN_LogicalID = @IN_LogicalID))
AND LinkID NOT IN
      (
      (SELECT B2BSOH1.LinkID FROM IN_SOH AS B2BSOH1 JOIN RE_Lines ON (B2BSOH1.SourceID = RE_Lines.SourceID) JOIN PO_Lines ON (PO_Lines.OrderLineID = RE_Lines.OrderLineID) JOIN PO_Main ON (PO_Main.OrderID = PO_Lines.OrderID) WHERE B2BSOH1.InventoryID = @RecID AND (@IN_LogicalID IS NULL OR (B2BSOH1.IN_LogicalID = @IN_LogicalID)) AND B2BSOH1.QuantityLeft > 0 AND PO_Main.OrderType = 1)
      UNION
      (SELECT B2BSOH2.LinkID FROM IN_SOH AS B2BSOH2 JOIN SH_BookInDetailLines ON (B2BSOH2.SourceID = SH_BookInDetailLines.RecID) JOIN SH_BookInLines ON (SH_BookInDetailLines.Parent_RecID = SH_BookInLines.BookInLineID) JOIN SH_Lines ON (SH_Lines.ShipmentLineID = SH_BookInLines.ShipmentLineID) JOIN PO_Lines ON (SH_Lines.PurchaseOrderLineID = PO_Lines.OrderLineID) JOIN PO_Main ON (PO_Main.OrderID = PO_Lines.OrderID) WHERE B2BSOH2.InventoryID = @RecID AND (@IN_LogicalID IS NULL OR (B2BSOH2.IN_LogicalID = @IN_LogicalID)) AND B2BSOH2.QuantityLeft > 0 AND PO_Main.OrderType = 1)
      )

   INSERT INTO @PurchaseOrders (CreditorID, SupplierName, OrderLineID, OrderID, OrderNo, OrderDate, Quantity, QuantityUsed, ExpectedDeliveryDate, HistoryTextComment, LogicalDescription, PhysicalDescription, QuantityDelivered, Processed)
    SELECT
    CR_Main.CreditorID, CR_Main.Name, PO_Lines.OrderLineID, PO_Main.OrderID, PO_Main.OrderNo, PO_Main.OrderedDate, PO_Lines.Quantity - PO_Lines.Delivered, 0, PO_Lines.DeliveryDate,
   PO_Lines.HistoryText_Comment,
    IN_Logical.Description, IN_Physical.Description, PO_Lines.Delivered, 0
    FROM PO_Lines 
    JOIN PO_Main ON (PO_Lines.OrderID = PO_Main.OrderID) 
   JOIN CR_Main ON CR_Main.CreditorID = PO_Main.CreditorID
    JOIN IN_Logical ON (IN_Logical.IN_LogicalID = PO_Main.IN_LogicalID) 
    JOIN IN_Physical ON (IN_Physical.IN_PhysicalID = IN_Logical.IN_PhysicalID)
    WHERE PO_Lines.InventoryID = @RecID
   AND (@IN_LogicalID IS NULL OR (PO_Main.IN_LogicalID = @IN_LogicalID))
   AND PO_Main.Status <> 0 AND PO_Main.Status <> 10
   AND PO_Lines.Delivered < PO_Lines.Quantity
   AND PO_Main.OrderType = 0
   ORDER BY PO_Lines.DeliveryDate, PO_Main.OrderNo

WHILE EXISTS(SELECT TOP 1 OrdersOnBackID FROM @BackOrders WHERE Processed = 0)
BEGIN
   SELECT TOP 1 @BOid = id, @OrdersOnBackID = OrdersOnBackID, @QuantityBackOrder = Quantity, @Priority = Priority, @IN_LogicalWarehouseID = LogicalID
   FROM @BackOrders BOTable
   WHERE Processed = 0
   ORDER BY BOTable.id -- BOTable.Priority

   Print '--------------'

   SELECT @OrderLineID = NULL, @CreditorID = NULL, @CreditorName = NULL, @OrderID = NULL, @OrderNo = NULL, @ExpectedDeliveryDate = NULL,
      @HistoryTexT_Comment = NULL, @QuantityConsumed = 0

   IF @TotalSOH > 0
   BEGIN
      -- We're going to use some SOH to fulfill this
      IF @TotalSOH >= @QuantityBackOrder
      BEGIN
         --print 'using ' + CAST(@QuantityBackOrder AS VARCHAR(10)) + ' from SOH'
         -- Use all the stock
         SET @CreditorName = 'SOH'
         SET @OrderNo = 'SOH'      
         SET @QuantityConsumed = @QuantityBackOrder         
         
         SET @TotalSOH = @TotalSOH - @QuantityBackOrder
         SET @QuantityBackOrder = 0
      END
      ELSE
      BEGIN
         --print 'using ' + CAST((@QuantityBackOrder - @TotalSOH) AS VARCHAR(10)) + ' from SOH'
         -- Use some SOH, and add a new row for us to attemp a PO allocation later
         SET @CreditorName = 'SOH'
         SET @OrderNo = 'SOH'      
         SET @QuantityConsumed = @TotalSOH

         -- Add a new row for the remainder
         INSERT INTO @BackOrders (OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], Quantity, LogicalWarehouse, PhysicalWarehouse, OrderID, OrderNo, CreditorID, CreditorAccountNo, CreditorName, ExpectedDeliveryDate, HistoryTextComment, QuantityConsumed, InvoiceType, Processed)
         SELECT OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], @QuantityBackOrder - @TotalSOH, LogicalWarehouse, PhysicalWarehouse, OrderID, OrderNo, CreditorID, CreditorAccountNo, CreditorName, ExpectedDeliveryDate, HistoryTextComment, 0, 0, 0
         FROM @BackOrders      
         WHERE id = @BOid

         SET @QuantityBackOrder = @QuantityBackOrder - @TotalSOH
         SET @TotalSOH = 0         
      END   
   END
   ELSE
   BEGIN
      -- Draw from PO's
            
      WHILE @QuantityBackOrder > 0 AND EXISTS(SELECT TOP 1 OrderLineID FROM @PurchaseOrders WHERE QuantityUsed < Quantity)
      BEGIN         
         --print 'BO Qty: ' + CAST(@QuantityBackOrder AS VARCHAR(10))

         SELECT TOP 1 @POID = id, @OrderLineID = OrderLineID, @OrderNo = OrderNo, @OrderID = OrderID, @CreditorID = CreditorID,
         @Quantity = Quantity, @QuantityUsed = QuantityUsed, @CreditorName = SupplierName, @HistoryTexT_Comment = HistoryTextComment, @ExpectedDeliveryDate = ExpectedDeliveryDate
         FROM @PurchaseOrders POTable
         WHERE QuantityUsed < Quantity
         ORDER BY POTable.id --POTable.ExpectedDeliveryDate, POTable.OrderNo

         --Print '*PO ' + @OrderNo + ' Quantity: ' + CAST(@Quantity AS VARCHAR(10)) + ' Used: ' + CAST(@QuantityUsed AS VARCHAR(10))
                                          
         IF (@Quantity - @QuantityUsed) >= @QuantityBackOrder
         BEGIN      
            --print '1 using ' + CAST(@QuantityBackOrder AS VARCHAR(10)) + ' from ' +  @OrderNo
            UPDATE @PurchaseOrders
            SET QuantityUsed = QuantityUsed + @QuantityBackOrder
            WHERE id = @POID

            SET @QuantityConsumed = @QuantityConsumed + @QuantityBackOrder
                  
            SET @QuantityBackOrder = 0   
         END
         ELSE
         BEGIN
            --print '2 using ' + CAST((@Quantity - @QuantityUsed) AS VARCHAR(10)) + ' from ' +  @OrderNo
            
            --print 'udating PO ' + @OrderNo + ' to quantity used of ' + CAST(@Quantity AS VARCHAR(10))
            UPDATE @PurchaseOrders
            SET QuantityUsed = Quantity
            WHERE id = @POID

            SET @QuantityBackOrder = @QuantityBackOrder - (@Quantity - @QuantityUsed)
            SET @QuantityConsumed = @QuantityConsumed + (@Quantity - @QuantityUsed)      
            
            -- Add a new row for the remainder
            --Print 'Adding split BO row for quantity ' + CAST(@QuantityBackOrder AS VARCHAR(10))
            INSERT INTO @BackOrders (OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], Quantity, LogicalWarehouse, PhysicalWarehouse, OrderID, OrderNo, CreditorID, CreditorAccountNo, CreditorName, ExpectedDeliveryDate, HistoryTextComment, QuantityConsumed, Processed)
            SELECT OrdersOnBackID, Priority, InvoiceID, InvoiceNo, CustomerOrderNo, DebtorID, DebtorAccountNo, DebtorName, [Date], @QuantityBackOrder, LogicalWarehouse, PhysicalWarehouse, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0
            FROM @BackOrders      
            WHERE id = @BOid          

            SET @QuantityBackOrder = 0   
         END         
      END
   END   
      
   UPDATE @BackOrders 
   SET Processed = 1, CreditorID = @CreditorID, CreditorName = @CreditorName, OrderID = @OrderID, OrderNo = @OrderNo, ExpectedDeliveryDate = @ExpectedDeliveryDate,
   HistoryTextComment = @HistoryTexT_Comment, QuantityConsumed = @QuantityConsumed
   WHERE id = @BOid    

   --Print '--------------'
END

SELECT Priority, OrdersOnBackID,
   DebtorAccountNo, DebtorName, DebtorID, 'JiwaFinancials.Jiwa.JiwaDebtorsUI.frmDebtor' [DebtorDrillDownClassName],
   InvoiceNo, InvoiceID, CASE InvoiceType WHEN 1 THEN 'JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.BackToBackSalesOrderEntryForm' ELSE 'JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.SalesOrderEntryForm' END [InvoiceDrillDownClassName],
   CustomerOrderNo, [Date], Quantity, @DecimalPlaces [QuantityDecimalPlaces], PhysicalWarehouse + ' / ' + LogicalWarehouse [Warehouse],
   OrderNo, OrderID, CASE InvoiceType WHEN 1 THEN 'JiwaFinancials.Jiwa.JiwaPurchaseOrdersUI.BackToBackPurchaseOrders' ELSE 'JiwaFinancials.Jiwa.JiwaPurchaseOrdersUI.PurchaseOrders' END [PurchaseOrderDrillDownClassName],
   CreditorName [SupplierName], CreditorID, 'JiwaFinancials.Jiwa.JiwaCreditorsUI.frmCreditor' [CreditorDrillDownClassName],
   ExpectedDeliveryDate, HistoryTextComment, QuantityConsumed
FROM @BackOrders BOTable
ORDER BY BOTable.Priority
SET NOCOUNT OFF


[Ernst]

Thanks Stuart, Will give that a go..