1 Reply Latest reply on Apr 6, 2010 6:18 PM by Stick

    Inventory

    721046
      I would like to know how I can get information on the age of the inventory item. eg., all items existing from 30 - 60 days both by item and value.

      Would really appreciate if someone can assist.

      Thanks!
        • 1. Re: Inventory
          Stick
          I got this from one of our developers:

          We've got a Discoverer report built on the sql below. We've createD a parameter 'Inactive Since' that allows a user to enter a transaction date. So if we want to see all items with no movement since the beginning of the year, it would be 'Inactive Since' = '01-JAN-2009'.

          select
          txn.inventory_item_id
          ,item.segment1||','||item.segment2 "item"
          ,item.description
          ,item.creation_date "item creation date"
          ,txn.organization_id
          ,item.attribute1 "planner"
          ,item.attribute2 "product code"
          ,min(txn.creation_date) "first date onhand"
          ,txn.transaction_uom
          ,cost.item_cost
          ,sum(txn.transaction_quantity) "quantity on hand"
          ,cost.item_cost*sum(txn.transaction_quantity)"value"
          ,max(txn.transaction_date)"last transaction"
          ,trunc(((sysdate - min(txn.creation_date))/365),4) "years in stock"
          ,trunc((max(txn.transaction_date)-min(txn.creation_date))) "days btw 1st and last txn"
          ,sysdate - max(txn.transaction_date) "Inactive Days"
          from CST_ITEM_COSTS cost, mtl_material_transactions txn, mtl_system_items_b item
          where txn.inventory_item_id = cost.inventory_item_id (+)
          and txn.inventory_item_id = item.inventory_item_id
          and txn.organization_id = <your org>
          and cost.organization_id (+) = <your org>
          and item.organization_id = <your org>
          and cost.cost_type_id (+) = 1
          and item.inventory_item_flag = 'Y'
          and txn.transaction_type_id not in ('4','24','36') -- no cc, cost update, rtv
          group by
          txn.inventory_item_id
          ,item.segment1,item.segment2
          ,item.description
          ,item.attribute1
          ,item.attribute2
          ,item.creation_date
          ,txn.organization_id
          ,txn.transaction_uom
          ,cost.item_cost
          order by txn.inventory_item_id