7 Replies Latest reply: Sep 13, 2012 5:07 AM by Michael Andreasson RSS

    How to find history of inventory from current inventory?

      I am working on an inventory project which requires me to find the historical inventory over the past several years. I have multiple scripts which I believe will help me calculate the histoy of inventory, however, I'm not sure how to combine them in the script to get the history. In general, I have calcualted the history of the change in inventory (by the day), by adding work order and transfers and subtracting invoices. The resulting daily inventory change is called[Inventory Change]. I also have a value for the current invenotry in the warehouses right now denoted as [Total Current Inventory]. What I must do is now work backwards (from today) adding or subtracting the Inventory Change from the previous day. My hypothesis is that by doing this, I will find a pretty accurate history of the inventory. I am at a complete loss, however, as to how to go about doing this. I also have a CurrentDate field which gives the today whenever I reload the script thru the ODBC connection. ANy feedback on  how to calcualte my invenotry or an a more direct approach to finding the history would be greatly appreciated!

       

      Here is my script:

       

      ////////////////////////////////////////////////
      // F4111 Loading Item Ledger Info
      ////////////////////////////////////////////////
      ItemLedger:
      LOAD
          
      ILDCT as [Document Type],
          
      ILMCU as [Business Unit],
          
      ILDOCO as [Order Number],
          
      If(IsNum(ILLITM), ILLITM) As [Item Number],
          
      Floor(ILTRQT/10000) as [Quantity],
          
      If(ILDCT = 'RI', ILTRQT/10000) as [Invoice Key],
          
      If(ILDCT = 'IC', ILTRQT/10000) as [Work Order Key],
          
      If(ILDCT= 'OV', ILTRQT/10000) as [Transfer Key],
          
      If(Len(text(ILTRDJ))>5,Date(MakeDate(1900 + (left(text(ILTRDJ),1) * 100) + mid(text(ILTRDJ),2,2)) + (right(text(ILTRDJ),3) - 1)),if(ILTRDJ > 0 ,Date(MakeDate(1000 + (left(text(ILTRDJ),1) * 100) + mid(text(ILTRDJ),1,2)) + (right(+text(ILTRDJ),3) - 1)), Null())) as [Order Date]
      ;SQL SELECT *
      FROM "JDE_PRODUCTION".PRODDTA.F4111

      ////////////////////////////////////////////////
      //Date and Calendar Info
      ////////////////////////////////////////////////
      DateInfo:
      Load DISTINCT
          
      [Order Date],
          
      Year([Order Date]) as Year,
          
      Month([Order Date]) as Month,
          
      WeekEnd([Order Date]) as [Week End],
           'Q' &
      Ceil(Month(num([Order Date]) + Right([Order Date], 3) - 1)/3) as QuarterResident ItemLedger;

      CurrentDateTemp:Load
          
      Date(Max([Order Date]), 'M/D/YYYY') as CurrentDateResident ItemLedger;
      Let vMaxVar = Peek('CurrentDate');

      ////////////////////////////////////////////////
      //Step 1 Inventory Calc
      ////////////////////////////////////////////////
      InventoryTemp:
      Load Distinct
          
      [Item Number],
          
      Sum([Invoice Key]) as Invoice,
          
      Sum([Work Order Key]) as [Work Order],
          
      Sum([Transfer Key]) as TransfersResident ItemLedger
          
      Group By
                
      [Item Number];
      //////////////////////////////////////////////////
      //// Inventory Info
      //////////////////////////////////////////////////
      InventoryInfo:
      Load Distinct
          
      [Item Number],
          
      Sum(Invoice+[Work Order]+Transfers) as [Inventory Change]

      Resident InventoryTemp
          
      Group By
          
      [Item Number];
      DROP Table InventoryTemp;
      ////////////////////////////////////////////////
      //Current Inventory Info
      ////////////////////////////////////////////////
      ItemLocationFile:
      LOAD
          
      LIITM as [Item Number (Short)],
          
      LIMCU as [Business Unit],
          
      LILOCN as Location,
          
      LIGLPT as [G/L Category],
          
      LIPQOH/10000 As [Qty On Hand]
      ;SQL SELECT
           LIITM, LILOCN, LIGLPT, LIPQOH, LIMCU
      FROM "JDE_PRODUCTION".PRODDTA.F41021
      where (LIPQOH <> 0);

      ////////////////////////////////////////////////
      //Current Inventory Final
      ////////////////////////////////////////////////
      CurrentInv:
      Load Distinct
          
      [Item Number (Short)],
          
      Sum([Qty On Hand]) as [Total Current Inventory]

      Resident ItemLocationFile
          
      Group By
                
      [Item Number (Short)];
                
      Drop Table ItemLocationFile;