This discussion is archived
1 Reply Latest reply: Nov 28, 2012 11:04 PM by HrishikeshJ RSS

Inventory Aging

870861 Newbie
Currently Being Moderated
Hi,

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
,mmt.subinventory_code
,mmt.transaction_uom
,mmt.inventory_item_id
,mmt.organization_id
,msib.description
,f.lot_number lot_number
,mln.expiration_date lot_expire_dt
,mp.organization_code
,ROUND (cic.item_cost, 5) item_cost
,round(to_date(:p_date) - to_date(f.attribute3,'YYYY/MM/DD HH24:MI:SS'),0) days
from
mtl_material_transactions mmt,
mtl_system_items_b msib,
mtl_lot_numbers mln,
mtl_parameters mp,
cst_item_costs cic,
MTL_ITEM_CATEGORIES_V micv
,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
--------------------------Parameters--------------
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
msib.segment1
,msib.description
,f.lot_number
,mln.expiration_date
,mp.organization_code
,cic.item_cost
,mmt.subinventory_code
,mmt.inventory_item_id
,mmt.transaction_uom
,mmt.organization_id
,f.attribute3
Order by subinventory_code,f.lot_number
) a
where 1=1
AND a.quantity <>0;
  • 1. Re: Inventory Aging
    HrishikeshJ Journeyer
    Currently Being Moderated
    Hi,

    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 )

    Thanks,
    Hrishikesh

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points