web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Historical Inventory A...
Microsoft Dynamics GP (Archived)

Historical Inventory Aged Trial Balance

(0) ShareShare
ReportReport
Posted on by babubaskaran@outloo... 12

Dear All,

Is there any report within GP which I can use to get Historical Inventory Aged Trial Balance.  I got the following view from the community member Waseem but my problem is I wanted to run for a specific period(i.e. 30.06.15) and this one will give as of date.  Did any of you done this please help me.

CREATE VIEW vw_IVStockAgeing
AS SELECT IV00101.ITEMNMBR AS ITEMNUMBER,
IV00101.ITEMDESC AS ITEMNAME,
IV10200.DATERECD AS DATERECEIVED,
IV10200.QTYRECVD AS QTYRECEIVED,
IV10200.QTYSOLD AS QTYSOLD,
IV00101.ITMCLSCD AS ITEMCLASS,
IV10200.UNITCOST AS UNITCOST,
IV10200.PCHSRCTY AS RECEIPTTYPE,
IV10200.RCPTNMBR AS RECEIPTNO,
IV10200.TRXLOCTN AS LOCATION,
( IV10200.QTYRECVD – IV10200.QTYSOLD ) AS QTYAVAILABLE,
( ( IV10200.QTYRECVD – IV10200.QTYSOLD ) * IV10200.UNITCOST ) AS CURRENTVALUE,
DATEDIFF(day, IV10200.DATERECD, GETDATE()) AS AGEDDAYS,
CASE WHEN DATEDIFF(day, IV10200.DATERECD, GETDATE()) < 0
THEN 'Current'
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 0
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 30
) THEN '0-30 Days' -- 1 month
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 30
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 60
) THEN '31-60 Days' -- 2 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 60
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 90
) THEN '61-90 Days' -- 3 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 90
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 180
) THEN '91-180 Days' -- 6 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 180
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 360
) THEN '181-360 Days' -- 1 year
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 360
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 720
) THEN '> 1 year’ — 2 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 720
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 1440
) THEN '> 2 years’ — 3 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 1440
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 2880
) THEN '> 3 years’– 4 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 2880
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 5760
) THEN '> 4 years’– 5 years
ELSE ‘> 5 years’ — Above 5 years
END AS BUCKET
FROM ( IV00101 IV00101
INNER JOIN IV10200 IV10200 ON IV00101.ITEMNMBR = IV10200.ITEMNMBR
)
INNER JOIN IV00102 IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
WHERE IV00102.RCRDTYPE = 1
AND IV10200.PCHSRCTY <> 0

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Historical Inventory Aged Trial Balance

    Hello Babu

    Have you checked the most recent report published on GP Essentials for the Item Stock Movement ? If not, check it out on Item Stock Movement Report - SQL View

    Additionally, further more scripts related to the HITB can be found on the SQL Tab of my blog.

    Your feedback is highly appreciated,

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at
    RE: Historical Inventory Aged Trial Balance

    Hi Mahmoud,

    Yes I have seen this, sorry just to be clear that how do I get this report for a ending period.  It should have the following.

    Itemnbr Rcddate QtyRcd Qtysold unitcost qtyavailable ageddays

    So when I run the report for ending 30.6.15 i wanted to see only the qty on hand on that day with aged days.

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Historical Inventory Aged Trial Balance

    The report above already includes the following:

    Item Number, Document Date, Document Number, Quantity in base, Quantity Balance, Unit Cost and Cost Balance.  There is a parameter for the item number, site and as of

    Date.

    What is missing ? Only the aging bucket ?

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at
    RE: Historical Inventory Aged Trial Balance

    Hi Mahmoud,

    Thanks for clarifying it.  Yes I can use the as of date but my question is what is the condition for the as of date.  I need all the qty on hand as of 30.6.15 which is received on or before 30.6.15 as well as issued or sold within 30.6.15.  So please confirm whether the above report can be used for this purpose.

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Historical Inventory Aged Trial Balance

    Babu

    The details above provides the quantity balance as of this specific date, per transaction.

    Lets consider the following example, you want to run this report for the item 100XLG and site (Main), as of 31/6/2014.

    On the report panel, provide the "from date" and "to date" to specify the timeline of the calculation, and the report will calculate the beginning balance considering the "From Date" parameter, and list all transaction with cumulative quantity and cost balance till the "To date" parameter.

    Simple enough :) ?

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at
    RE: Historical Inventory Aged Trial Balance

    Hi  Mahmoud,

    which sql script you are referring to please confirm.

    Item Stock Movement Report - which is your latest script

    or

    Inventory Balance between Two Dates

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Historical Inventory Aged Trial Balance

    Item Stock Movement Report

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at
    RE: Historical Inventory Aged Trial Balance

    Thanks a lot Mahmoud for your kind reply.  How do I change the from date and to date on the report panel please help me.  

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Historical Inventory Aged Trial Balance

    It is a stored procedure, pass the parameters

    SET @QuantityBalance = 0

    SET @CostBalance = 0

    SET @ItemNumber = '    '

    SET @LocationCode = '     '

    SET @From = '     '

    SET @To = '    '

    You can create an excel file to publish the report, and link excel fields to these parameters as illustrated in the article.

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at
    RE: Historical Inventory Aged Trial Balance

    Thanks Mahmoud I will try this on tomorrow and let you know.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans