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

HITB Per Period (Cumulative) – Inventory Balance on a Prior Date

 

A common question frequently asked by clients is “ What was my Inventory Balance of a specific item on a prior date of this year ! “ . In this post, inventory balance on a prior date report provides the following details;

1

 

In a previous post, Historical IV Trial Balance - Per Period , the report provides the inventory balances per period separately. I have received several requests to build up on this report to provide the; Inventory Balance on a prior date. To deploy this report, following the steps below;

1- Run the script provided on - Historical IV Trial Balance - Per Period. This will create a view V_HITB_Per_Period.

2- Run the script below, which builds up on the view above to retrieve the cumulative inventory balances

SELECT      X.Trx_Year AS 'Year',
                    X.Period ,
                    X.Item ,
                    X.QuantityBalance AS 'Quantity Balance',
                    X.CostBalance AS 'Cost Balance',
        (
            SELECT SUM(QuantityBalance)
            FROM   V_HITB_Per_Period AS Y
            WHERE  X.Item = Y.Item AND
                   DATEADD(MONTH, Y.Period - MONTH(0),       
                                DATEADD(YEAR,Y.Trx_Year - YEAR(0), 0 )
                            ) <=
                   DATEADD(MONTH,X.Period - MONTH(0),
                                DATEADD(YEAR,X.Trx_Year- YEAR(0), 0 )
                            )
        ) AS 'Running Quantity Balance',
        (
            SELECT SUM(CostBalance)
            FROM   V_HITB_Per_Period AS Y
            WHERE  X.Item = Y.Item AND
                   DATEADD(MONTH, Y.Period - MONTH(0),       
                                DATEADD(YEAR,Y.Trx_Year - YEAR(0), 0 )
                            ) <=
                   DATEADD(MONTH,X.Period - MONTH(0),
                                DATEADD(YEAR,X.Trx_Year- YEAR(0), 0 )
                            )
        ) AS 'Running Cost Balance'
        FROM  V_HITB_Per_Period  AS X
        ORDER BY Item,Trx_Year,Period

 

Helping Note!

Both reports users HITB table (SEE30303) primarily to retrieve inventory transactions. So If you have upgraded from prior GP versions without deploying the HITB Reset Tool, the report will not retrieve any data.

 

Best Regards,
Mahmoud M. AlSaadi

3 comments:

  1. Thank you for your code, I am going to try it.

    ReplyDelete
    Replies
    1. You are most welcome

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  2. Thank you for this. Is it possible to narrow this down to the last year or two?

    ReplyDelete