Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

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

23 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
  7. Thanks Alot Mr. Mahmood. How can I add column LOCNCODE field of SEE030303 in the output?

    ReplyDelete
    Replies
    1. Hello Tom
      I have created another report (Historical Inventory Trial Balance - per Location). It is scheduled to be published on the blog

      Please never hesitate to contact me on my email address MahmoodSaadi@Live.com, in order to provide you with the report.


      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  8. Hi Mahmoud - are we able to add LOCNCODE to this? Report is almost perfect. thanks, Jay

    ReplyDelete
    Replies
    1. Yes, for sure.

      There is another report which is built including the location code.


      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Mahmoud,
    Thanks so much for this information. My organization is hoping to get a look at historical data over the past 30 days. Is it possible to alter this code so that it shows 30 days, one day at a time, instead of the current year/period format? Thanks! Joel

    ReplyDelete