2 Replies Latest reply on Mar 27, 2018 1:45 PM by Richard Duffy

    Looking to create a query that shows 30,60 and 90 day usage for inventory items plus more....

      Looking to create a query that shows 30,60 and 90 day usage for inventory items. Needs to include items sold, items purchase, items expensed etc.

       

      I am using currently tables OIQR and OITM because I do want the item name and description. Doing this query to help self teach but stuck.

        • 1. Re: Looking to create a query that shows 30,60 and 90 day usage for inventory items plus more....
          Dave Gutman

          Hi Jennifer,

           

          Some time back I found a query that ages B1 Inventory on the SCN support forum.  I didn't write the code but it appears to work on a test system.  Glad to send it to you via email (with appropriate disclaimers).

           

          Dave Gutman

          DGutman@AdvantageERPGroup.com

          • 2. Re: Looking to create a query that shows 30,60 and 90 day usage for inventory items plus more....
            Richard Duffy

            Try this for size while I see what I can find for the usage - this query covers aging of inventory  (another winner from Gordon Du)

             

            By the way, are you on SQL or HANA?

             

            select b.code, b.name, b.Wh, b.Bal, b.Val,

            isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

            isnull(case when b.days between 31 and 60 then b.bal end,0) '31-60 Days',

            isnull(case when b.days between 61 and 90 then b.bal end,0) '61-90 Days',

            isnull(case when b.days between 91 and 120 then b.bal end,0) '91-120 Days',

            isnull(case when b.days between 121 and 150 then b.bal end,0) '121-150 Days',

            isnull(case when b.days between 151 and 180 then b.bal end,0) '151-180 Days',

            isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

            from (

            select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

            from (

            select max(t0.itemcode)'Code',max(t0.Dscription)'Name',

            max(t0.Warehouse)'Wh',

            sum(t0.inqty-t0.outqty)'Bal',sum(t0.transvalue)'Val',max(t0.docdate)'dt'

            from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

            where t0.warehouse='[%1]'

            group by t0.itemcode

            )a

            )b order by code