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;

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