1 Reply Latest reply: Nov 29, 2012 1:04 AM by HrishikeshJ RSS

    Inventory Aging


      I need to develop "Inventory Aging with Lot Numbers info"

      | 0-30-days | 31-60-days | 61-90-days | 91-120-days | 121-150-days | 151-180-days | 181-plus-days | total-quantity

      Subinv Code , itemcode Item Desc, Lot Expire Dt Lot Number,

      i have developed this query.

      issue is i am getting more than one lot number for one transaction and one subinv code.

      please help me

      select * from (select distinct msib.segment1
      ,SUM (mmt.primary_quantity) quantity
      ,f.lot_number lot_number
      ,mln.expiration_date lot_expire_dt
      ,ROUND (cic.item_cost, 5) item_cost
      ,round(to_date(:p_date) - to_date(f.attribute3,'YYYY/MM/DD HH24:MI:SS'),0) days
      mtl_material_transactions mmt,
      mtl_system_items_b msib,
      mtl_lot_numbers mln,
      mtl_parameters mp,
      cst_item_costs cic,
      ,apps.mtl_transaction_types e
      ,apps.mtl_transaction_lot_numbers f
      Where 1=1
      AND mmt.inventory_item_id = msib.inventory_item_id
      AND mmt.organization_id = msib.organization_id
      AND mln.inventory_item_id = msib.inventory_item_id
      AND mln.organization_id = msib.organization_id
      AND mp.organization_id = msib.organization_id
      AND cic.inventory_item_id = msib.inventory_item_id
      AND cic.organization_id = msib.organization_id
      AND micv.inventory_item_id = msib.inventory_item_id
      AND micv.organization_id = msib.organization_id
      AND msib.INVENTORY_ITEM_STATUS_CODE not in ('Inactive')
      AND f.transaction_id = mmt.transaction_id
      AND mmt.transaction_type_id = e.transaction_type_id
      AND mmt.inventory_item_id = f.inventory_item_id
      AND mmt.organization_id = f.organization_id
      AND f.lot_number = mln.lot_number
      AND mmt.organization_id = :P_ORGANIZATION_ID
      AND micv.CATEGORY_CONCAT_SEGS between nvl(:p_cat_lo,micv.CATEGORY_CONCAT_SEGS) and nvl(:P_cat_hi,micv.CATEGORY_CONCAT_SEGS)
      AND micv.category_set_id = :p_category_set_id
      AND msib.segment1 between nvl(:P_ITEM_LO,msib.segment1) and nvl(:P_ITEM_HI,msib.segment1)
      AND mmt.subinventory_code BETWEEN nvl(:P_SUB_FROM,mmt.subinventory_code) AND nvl(:P_SUB_TO,mmt.subinventory_code)
      AND trunc (mmt.TRANSACTION_DATE) <= :p_date
      Group by
      Order by subinventory_code,f.lot_number
      ) a
      where 1=1
      AND a.quantity <>0;
        • 1. Re: Inventory Aging

          First of all, never use distinct (unless it is really required)
          If you are using Group By, then there is no need to use disctinct.

          Same lot can be present in multiple subinveentories; also one transaction can have multiple lots.
          Also you are taking SUM (mmt.primary_quantity) quantity; replace it by SUM (f.primary_quantity) quantity

          you should be able to get desired results.

          if not, then please provide sample output (pinpointing specific rows )


          Please mark the answer as Helpful/Correct if you find so.