Inventory Balance Between Two Dates

Commonly asked to determine the inventory balance and cost for a certain item on a certain location between two dates. In this post, I provide a stored procedure which retrieves the following report;

The balances are grouped by the document type. In addition, Master Quantity data are provided along with the summary to provide better insight for analysts.

• Quantity on Hand: Calculated from the Purchase Receipt Layer
• Quantity Allocated: retrieved “as is” from Item Quantity Master
• Quantity Damaged: retrieved “as is” from Item Quantity Master
• Quantity on Order: retrieved “as is” from Item Quantity Master

--- Tables Included:

• SEE30303: Historical Inventory Trial Balance
• IV10200: Purchase Receipt Layers
• IV00101: Item Master
• IV00102: Item Quantity Master

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[IV_ITM_LOCTN_Summary]  -- Item Summary Between Two Datese per Location
    (
      @ITEMNMBR CHAR(31) ,
      @TRXLOCTN CHAR(11) ,
      @FromDate DATETIME ,
      @ToDate DATETIME
    )
AS
SET nocount ON
SELECT  IVSUM.DocumentType,
        IVSUM.ITEMNMBR,
        IVSUM.ItemType,
        IVSUM.Item_Inactive,
        IVSUM.LOCNCODE,
        IVSUM.Quantity,
        IVSUM.Value,
        IVBLNC.QuantityOnHand AS 'Quantity On Hand',
        IVQMSTR.QTYONORD AS 'Quantity On ORDER',
        IVQMSTR.QTYDMGED AS 'Quantity Damaged',
        IVQMSTR.ATYALLOC AS 'Allocated Quantity'
        FROM
(
SELECT  DocumentType,
        ITEMNMBR,
        ItemType,
        LOCNCODE,
        Item_Inactive,
        SUM(TRXQTYInBase) AS Quantity,
        SUM(Extended_Cost) AS Value
        FROM
        (

         Select    CASE A.DOCTYPE
                        WHEN 1 THEN 'Adjustment'
                        WHEN 2 THEN 'Variance'
                        WHEN 3 THEN 'Transfer'
                        WHEN 4 THEN 'Receiving'
                        WHEN 5 THEN 'Sales Return'
                        WHEN 6 THEN 'Sales'
                        WHEN 7 THEN 'BOM-Assembly'
                        ELSE        'Cost ADJ'
                    END AS DocumentType,
                    A.ITEMNMBR,
                    CASE B.ITEMTYPE
                        WHEN 1 THEN 'Sales Inventory'
                        WHEN 2 THEN 'Discontinued'
                        WHEN 3 THEN 'Kit'
                        WHEN 4 THEN 'Miscellaneous Charges'
                        WHEN 5 THEN 'Services'
                        WHEN 6 THEN 'Flat Fee'
                        ELSE        'N.A'
                    END AS ItemType,
                    CASE B.INACTIVE
                        WHEN 0 THEN 'No'
                        WHEN 1 THEN 'Yes'
                        ELSE        'N.A'
                    END AS Item_Inactive,
                    A.DOCDATE,
                    A.LOCNCODE,
                    A.TRXQTYInBase,
                    A.UNITCOST,
                    A.TRXQTYInBase * A.UNITCOST

                                        AS  Extended_Cost
                    FROM dbo.SEE30303 AS A
                    LEFT OUTER JOIN dbo.IV00101 AS B
                    ON A.ITEMNMBR = B.ITEMNMBR
                    WHERE    A.ITEMNMBR =  @ITEMNMBR AND
                            A.LOCNCODE = @TRXLOCTN AND 
          (A.DOCDATE BETWEEN @FromDate AND @ToDate)       
        ) AS IVTRX
        GROUP BY DocumentType,
                 ITEMNMBR,
                 ItemType,
                 Item_Inactive,
                 LOCNCODE
                
) AS IVSUM
LEFT OUTER JOIN
(
    SELECT  ITEMNMBR,TRXLOCTN,
            SUM(QTYRECVD-QTYSOLD) AS QuantityOnHand
    FROM IV10200
   GROUP BY ITEMNMBR , TRXLOCTN
) AS IVBLNC
ON    IVSUM.ITEMNMBR = IVBLNC.ITEMNMBR AND
      IVSUM.LOCNCODE = IVBLNC.TRXLOCTN
LEFT OUTER JOIN dbo.IV00102 AS IVQMSTR
ON    IVQMSTR.ITEMNMBR = IVBLNC.ITEMNMBR AND
      IVQMSTR.LOCNCODE = IVBLNC.TRXLOCTN

SET nocount OFF   
GO

To run the procedure, run the following code including the required parameters;

EXECUTE [dbo].[IV_ITM_LOCTN_Summary]
               '#ItemNumber'
              ,'#Location Code'
              ,'#2013-01-01'
              ,'#2013-12-31'
GO

Best Regards,
Mahmoud M. AlSaadi