Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Tuesday, January 21, 2014

Historical IV Trial Balance - Per Period

Several Customers are encountered with the inquiry of having a detailed report for “Historical” Inventory Balances per Item per Period as shown below;

HITB Per Period>> For running total , create the view below and proceed with the next step on the
HITB Per Period (Cumulative) –  Inventory Balance on a Prior Date

The script below retrieves the data from Historical Inventory Trial Balance Tables (SEE30303), to provide both quantity and cost balances per item.

---Tables Included:

  • SY40100| Period Setup
  • SEE30303|Historical Inventory Trial Balance

CREATE VIEW V_HITB_Per_Period
AS
   SELECT  Z.Year AS Trx_Year,
           Z.Period,
           Z.Item,
           Z.[Quantity] AS QuantityBalance,
           Z.[Cost] AS CostBalance
           FROM    ( SELECT X.[Year],
                            X.[Period],
                            X.[Date],
                            X.[Item],
                            X.[Quantity],
                            X.[Cost]
                            FROM  
( SELECT E.[ITEMNMBR] AS Item,
         E.[Year1] AS Year,
         E.[PeriodID] AS Period ,
         ISNULL(F.[Quantity], 0) AS Quantity,
         ISNULL(F.[Cost], 0) AS Cost ,
         ( E.[Year1] * 365 ) + ( E.[PeriodID] * 30 ) 
         AS Date FROM 
        
(SELECT  Z.[ITEMNMBR] ,
                  D.[Year1],
                  D.[PeriodID]
          FROM     
         
( SELECT 
          DISTINCT
          Year1 ,
          PeriodID ,
          DATEADD(mm, 1,
          CONVERT(DATETIME, '01/'
          + CAST(PeriodID AS VARCHAR(2))
          + '/'
          + CAST(YEar1 AS VARCHAR(4)), 103)) AS Date
         
FROM  SY40100 AS X
          WHERE PeriodID <> 0
          ) AS D
       INNER JOIN (SELECT
                   Y.[ITEMNMBR],
                   MIN(Y.[DOCDATE]) AS Date
                   FROM
                   [SEE30303] AS Y
                   GROUP BY Y.[ITEMNMBR]
                   ) AS Z ON D.Date > Z.Date
                   ) AS E
                   LEFT OUTER JOIN
                  
(
                  
SELECT 
                   YEAR([DOCDATE]) AS Year ,
                   MONTH([DOCDATE]) AS Period ,
                   [ITEMNMBR] AS Item,
                   SUM([TRXQtyInBase]) AS Quantity,
                   SUM([EXTDCost]) AS Cost
                   FROM    SEE30303
                   GROUP BY YEAR([DOCDATE]),
                   MONTH([DOCDATE]),
                   [ITEMNMBR]
                   ) AS F ON E.ITEMNMBR = F.Item
                   AND E.PeriodID = F.Period
                   AND E.Year1 = F.Year
                   ) AS X
                   GROUP BY  X.[Year],
                             X.[Period],
                             X.[Date],
                             X.[Item],
                             X.[Quantity],
                             X.[Cost]
                   ) AS Z

Helping Note !
The script could be further enhanced in order to include further "drill down" details such as Inventory Account, Location and other inventorial parameters.

Best Regards,
Mahmoud M. AlSaadi

17 comments:

  1. Great input on this AR TB do you know if anyone has done one for AP? if so could you point me to it? Please and thank you

    ReplyDelete
    Replies
    1. @RLMcVicar you are most welcome
      For the mean time, I have nothing done off-the-shelf for AP. Although, you can consider MVP Belinda method on creating a quick pivot table for AP Aging, it is quite efficient and straight to the point.
      Check it out on
      http://saci.com/blogs/belinda-the-gp-csi/bid/151057/AP-aging-in-Excel-thanks-to-Victoria-Yudin-in-Microsoft-Dynamics-GP

      Delete
  2. Sorry,dumb question.
    How do I get the data into Excel?

    Thanks

    ReplyDelete
    Replies
    1. It is actually a common question
      On your Excel sheet, go to the "Data" tab, you will find a section named (Get external data), click on "From other Sources" > "From SQL Server"
      You will be asked to enter the server name and associated authentication in order to retrieve the database objects.
      Select the "Database", and the view created, next then finish.

      Let me know if you have any further questions

      Delete
  3. I can get the tables in Excel. How do I create the view?
    Thanks!

    ReplyDelete
    Replies
    1. Just run the script provided above on your SQL Management Studio, it will create a view named "V_HITB_Per_Period".
      Once created, it can be reachable on the Excel sheet.

      After deploying the view above, check the script provided on http://mahmoudsaadi.blogspot.com/2014/01/hitb-per-period-cumulative-inventory.html
      This will give you a cumulative running total for the item balance

      Delete
    2. Can you please tell me how to add to the view?
      I need to show Account and Item description as well.

      Thank you!

      Delete
  4. Hello Mr Mahmood

    I'm using this SQL to consult historical inventory balance., but I need to know the account number. How I can include it? Thanks

    DECLARE @ASOFDATE DATETIME
    SET @ASOFDATE = '2014-02-01'

    SELECT
    ITEMNMBR AS [Item Number],
    TRXLOCTN AS [Location],
    SUM(Quantity) AS Quantity,
    SUM([Extended Cost]) AS [Total Cost]

    FROM

    (SELECT ITEMNMBR,

    (dbo.IV10200.QTYRECVD -
    ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
    WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
    AND ITEMNMBR = IV10200.ITEMNMBR
    AND DOCDATE <= @ASOFDATE), 0))
    AS [Quantity],
    UNITCOST *
    (dbo.IV10200.QTYRECVD -
    ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
    WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
    AND ITEMNMBR = IV10200.ITEMNMBR
    AND DOCDATE <= @ASOFDATE), 0))
    AS [Extended Cost],
    TRXLOCTN
    FROM dbo.IV10200 WHERE DATERECD <@ASOFDATE) AS SOURCE
    GROUP BY ITEMNMBR, TRXLOCTN

    ReplyDelete
  5. Hi ,
    In Historical aged trial balance I am not able to untick the items with zero quantity as on date. The field is not enabled. Can you please help.

    ReplyDelete
  6. Whenever I add LOCNCODE to the above view it doesn't generate the correct totals. Can you add LOCNCODE to your view?

    ReplyDelete
    Replies
    1. Hello there
      I have a different SQL script for HITB per location. Stay tuned as it will be published on my blog.

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
    2. Sounds fantastic. Do you have an estimated date that you may publish it?

      Delete
    3. By end of April, 2017.

      Best Regards,
      Mahmoud M. AlSaadi

      Delete