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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Inventory On Hand History
Microsoft Dynamics GP (Archived)

Inventory On Hand History

(0) ShareShare
ReportReport
Posted on by Matthew Magrum 275

I have been working on this for awhile and have been unable to get a solution that appears to have correct data. Essentially, I am trying to create a report that can tell me how much stock I had on hand on any given day in the past. I have tried writing a SQL query using the IV30300 table but if I add the inventory numbers together from eternity past, grouped by month, or if I take current stock levels and subtract the inventory change info in that table, I quickly end up with having too little stock. Below is a copy of the SQL query that I wrote to calculate historical inventory adds, unsuccessfully (the one that added from the past data and groups by month).

select month(docdate) as TransMonth, year(docdate) as TransYear, a.itemnmbr, trxloctn,      max(uscatvls_1)  as mfr,sum(trxqty * unitcost) + isnull((select sum(trxqty * unitcost) from iv30300       where itemnmbr = a.itemnmbr and trxloctn = a.trxloctn and docdate <       cast((cast(month(a.docdate) as varchar(4)) + '/1/' + cast(year(a.docdate) as varchar(4))) as datetime)), 0)from iv30300 a      inner join iv00101 b on a.itemnmbr = b.itemnmbrwhere a.unitcost > 0group by month(docdate), year(docdate), a.itemnmbr, trxloctn

Any help is appreciated!

*This post is locked for comments

I have the same question (0)
  • Bron Profile Picture
    Bron 4 on at

    Matthew - the attempt to create a historical stock status SQL query is pretty commentable. I don't think I've seen using the IV30300 table for this before (not to say it can't be done).

    I suggest looking at IV10200 in addition to this table if you are adament about using an SQL query. The IV10200 has an adjusted cost column which gets updated under the scenario such as.......we receive at A$ but pay B$ when Vendor invoice arrives and is processed. I don't think the IV30300 gets updated after posting.

    Microsoft created the HITB (Historical Inventory Trial Balance) tool for the purpose of tying out inventory to GL at any given time. It creates some tables which also might be used. You should research it.

    Good Luck

     

  • Matthew Magrum Profile Picture
    Matthew Magrum 275 on at

    Bron - thank you for your reply. After review, it looks like the IV10200 table is more of an inventory aging tool rather than allowing me to go back in time for a given item. What I'm looking to do is find out how much of an item (and how much that item cost) I had at a point in the past. For that, I would need to basically un-age things but also include sales orders. The IV10200 seems to only hold the information of when I received an item and if it was sold but not when, keeping me from connecting the dots to re-form my past inventory quantities.

    I did find the IV30102 table and have been working through that but even there I find disparity between the numbers it gives vs. my current on hand quantities. Have you heard of any other tables that may be of use? To give you an example of my SQL for the IV30102, it is below - I would be curious if you can run this and see if your values equal.

    select a.locncode, sum(qtyonhnd) from iv00102 a inner join iv00101 b on a.itemnmbr = b.itemnmbr where a.locncode <> '' group by a.locncodehaving sum(qtyonhnd) > 0order by a.locncode  select a.locncode, sum(smryqtys) from iv30102 a inner join iv00101 b on a.itemnmbr = b.itemnmbr where  smrypmth = 8 and year1 = 2010 and smrytype = 2 and a.locncode <> ''group by a.locncodehaving sum(smryqtys) > 0

    order by a.locncode

     

  • Bron Profile Picture
    Bron 4 on at

    Matthew -

    I have a SQL stored Procedure (written by someone else) that was used by a client of mine for a Historical Stock Status Report. This was before MBS tried to fix the system generated historical stock status (I assume you tried running the GP version first?) I haven't tested it nor have I used it. It does not calculate by Location Code..........it 'lumps' all transactions together.......

    Logically - a SQL stored Procedure and not a view should be used. The stored procedure I have requires 'as of date' parameter to be executed.

    Send me your email address and I'll send it to you. I can't vouch for it's accuracy I have not tested it.......it uses the IV30300 table and a few others.

    Regards......

  • RAJKUMAR KANDASAMY Profile Picture
    RAJKUMAR KANDASAMY 170 on at

    CURRENT ON HAND QUANTITY BASED ON IV30300 TABLE BY LOCNCODE WISE
     
    1ST VIEW:
    CREATE VIEW QTYLOCWISE
    AS
    SELECT A.ITEMNMBR, SUM(case1) + SUM(case2) AS  RIYADH,SUM(RAIL1) + SUM(RAIL2) AS  RAIL,
    SUM(JEDDAH1) + SUM(JEDDAH2) AS  JEDDAH,
    SUM(DAMMAM1) + SUM(DAMMAM2) AS DAMMAM,
    SUM(TABERJEL1) + SUM(TABERJEL2) AS  TABERJEL,
    SUM(SAJER1) + SUM(SAJER2) AS SAJER,
    SUM(BURAIDAH1) + SUM(BURAIDAH2) AS  BURAIDAH,
    SUM(WADI1) + SUM(WADI2) AS  WADI,
    SUM(WSHOP1) + SUM(WSHOP2) AS  WSHOP,
    SUM(KHAMIS1) + SUM(KHAMIS2) AS  KHAMIS
     FROM(
    SELECT A.ITEMNMBR,
    case1 =CASE A.TRXLOCTN WHEN 10 THEN TRXQTY * -1 ELSE 0 END,
    case2 = CASE A.TRNSTLOC WHEN 10 THEN A.TRXQTY* 1 ELSE 0 END,
    RAIL1 =CASE A.TRXLOCTN WHEN 11 THEN TRXQTY * -1 ELSE 0 END,
    RAIL2 = CASE A.TRNSTLOC WHEN 11 THEN A.TRXQTY* 1 ELSE 0 END,
    JEDDAH1 =CASE A.TRXLOCTN WHEN 20 THEN TRXQTY * -1 ELSE 0 END,
    JEDDAH2 = CASE A.TRNSTLOC WHEN 20 THEN A.TRXQTY* 1 ELSE 0 END,
    DAMMAM1 =CASE A.TRXLOCTN WHEN 30 THEN TRXQTY * -1 ELSE 0 END,
    DAMMAM2 = CASE A.TRNSTLOC WHEN 30 THEN A.TRXQTY* 1 ELSE 0 END,
    TABERJEL1 =CASE A.TRXLOCTN WHEN 40 THEN TRXQTY * -1 ELSE 0 END,
    TABERJEL2 = CASE A.TRNSTLOC WHEN 40 THEN A.TRXQTY* 1 ELSE 0 END,
    SAJER1 =CASE A.TRXLOCTN WHEN 50 THEN TRXQTY * -1 ELSE 0 END,
    SAJER2 = CASE A.TRNSTLOC WHEN 50 THEN A.TRXQTY* 1 ELSE 0 END,
    BURAIDAH1 =CASE A.TRXLOCTN WHEN 60 THEN TRXQTY * -1 ELSE 0 END,
    BURAIDAH2= CASE A.TRNSTLOC WHEN 60 THEN A.TRXQTY* 1 ELSE 0 END,
    WADI1 =CASE A.TRXLOCTN WHEN 70THEN TRXQTY * -1 ELSE 0 END,
    WADI2 = CASE A.TRNSTLOC WHEN 70 THEN A.TRXQTY* 1 ELSE 0 END,
    WSHOP1 =CASE A.TRXLOCTN WHEN 80 THEN TRXQTY * -1 ELSE 0 END,
    WSHOP2 = CASE A.TRNSTLOC WHEN 80 THEN A.TRXQTY* 1 ELSE 0 END,
    KHAMIS1     =CASE A.TRXLOCTN WHEN 90 THEN TRXQTY * -1 ELSE 0 END,
    KHAMIS2= CASE A.TRNSTLOC WHEN 90 THEN A.TRXQTY* 1 ELSE 0 END
    FROM IV30300 AS A  WHERE A.DOCTYPE='3')  A
    GROUP BY A.ITEMNMBR
     
    2ND VIEW:
    CREATE VIEW QTYLOCWISE1
    AS
    SELECT A.ITEMNMBR ,
    sum(CASE WHEN 10 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY ELSE 0 END )AS RIYADH,
    sum(CASE WHEN 11 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY  ELSE 0 END )AS RAIL,
    sum(CASE WHEN 20 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY ELSE 0 END )AS JEDDAH,
    sum(CASE WHEN 30 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY  ELSE 0 END )AS DAMMAM,
    sum(CASE WHEN 40 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY ELSE 0 END )AS TABERJEL,
    sum(CASE WHEN 50 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY  ELSE 0 END )AS SAJER,
    sum(CASE WHEN 60 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY ELSE 0 END )AS BURAIDAH,
    sum(CASE WHEN 70 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY  ELSE 0 END )AS WADI,
    sum(CASE WHEN 80 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY ELSE 0 END )AS WSHOP,
    sum(CASE WHEN 90 IN (TRNSTLOC,TRXLOCTN) THEN  TRXQTY  ELSE 0 END )AS KHAMIS
    FROM IV30300 AS A
       WHERE DOCTYPE <>'3'
    GROUP BY A.ITEMNMBR
    3RD VIEW: FINAL VIEW :
    CREATE VIEW  HISTTRANSFER
    AS
    SELECT A.ITEMNMBR, A.RIYADH+B.RIYADH AS RIYADH,A.RAIL+B.RAIL AS RAIL,A.JEDDAH+B.JEDDAH AS JEDDAH,A.DAMMAM+B.DAMMAM AS DAMMAM,
    A.TABERJEL+B.TABERJEL AS TABERJEL,A.SAJER+B.SAJER AS SAJER,A.BURAIDAH+B.BURAIDAH AS BURAIDAH,A.WADI+B.WADI AS WADI,
    A.WSHOP+B.WSHOP AS WSHOP,A.KHAMIS+B.KHAMIS AS KHAMIS FROM QTYLOCWISE AS A LEFT OUTER JOIN QTYLOCWISE1 AS B
    ON A.ITEMNMBR=B.ITEMNMBR
    IF EXCUTE ALL THE VIEWS  YOU CAN GE THE CURRENT ONHAND QTY WITHOUT DEDUCT QTYALLOC.
    RAGARDS
    RAJKUMAR.K
  • Richard Mintz Profile Picture
    Richard Mintz 20 on at

    Hi,

    I am trying to accomplish the same thing as Matthew in order to get historical inventory information into a Data warehouse. Bron could you send me the Stored Procedure that you mentioned.

    Thanks is advance.

    Richard Mintz

    TGO Consulting

  • Bron Profile Picture
    Bron 4 on at

    Richard - you'll need to send me an email address. send it to - btamulis@hotmail.com

  • Community Member Profile Picture
    Community Member on at

    Could anybody send me the stored procedure? I'm trying to accomplish the same thing. I would apporeciate it.

    aborda90@hotmail.com

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    Look at the SEExxxxx tables.  If these have data, they would be helpful.  These tables are summaries of movements of inventory used to produce the HITB report.

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    Find the report on my blog, 

    GP Essentials - Historical IV Trial Balance - Per Period

  • Amber253 Profile Picture
    Amber253 5 on at

    If you know it takes up to two months to source a product, and you see you have two months of stock left, it may be time to order more frequently. Likewise, items that are selling fast and there is a low supply of may need to be ordered. In terms of inventory turnover, understock represents an excessively high inventory turnover ratio www.mybkexperience.com survey.

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…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans