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

      //Date and Calendar Info
      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;

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

      //Step 1 Inventory Calc
      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
      Load Distinct
      [Item Number],
      Sum(Invoice+[Work Order]+Transfers) as [Inventory Change]

      Resident InventoryTemp
      Group By
      [Item Number];
      DROP Table InventoryTemp;
      //Current Inventory Info
      LIITM as [Item Number (Short)],
      LIMCU as [Business Unit],
      LILOCN as Location,
      LIGLPT as [G/L Category],
      LIPQOH/10000 As [Qty On Hand]
      where (LIPQOH <> 0);

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

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