Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Monday, November 18, 2013

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;

Untitled

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

2 comments:

  1. Good morning Mr. Alsaadi,

    As always your tools are valuable to all of us, it is possible to include document number inside your query? Ive been trying to link SEE30303 RCPTNMBR as document number, but can not figured out how.

    It could be a valuable addition to a valuable tool.

    Thank you very much.

    ReplyDelete
    Replies
    1. Hello Julian
      Thanks for your comment,
      This is a summary report which provides summarized values for the item number and a specific location, grouped by the transaction type.

      What you are looking for is much more of a detailed report as it considers the "document number". It will be considered and published on the blog soon.

      stay tuned !

      Mahmoud,

      Delete