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...

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

No comments:

Post a Comment