RD INV Inventory Aging

select
bucket_seq,
organization_id,
decode(:p_age_level,1,null,subinventory_code) subinventory_code,
decode(:p_age_level,3,locator_code,null) locator_code,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
buckets_days_heading,
to_char(max(last_trx_date),'DD-MON-YYYY') last_trx_date,
trunc(sysdate)-min(first_trx_date) n_age,
sum(on_hand) on_hand,
max(sum_on_hand) t_onhand,
sum(round(n_Value,2)) n_value
from
(select organization_id,
subinventory_code,
locator_code,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
buckets_days_heading,
INV_AGERPXML_PKG.f_bucket_days_seq(:P_BUCKETS_ID,buckets_days_heading) bucket_seq,
trunc(max(last_trx_date)) last_trx_date,
trunc(min(first_trx_date)) first_trx_date,
sum(on_hand) on_hand,
max(sum_on_hand) sum_on_hand,
sum(n_Value) n_Value
from
(select
msib.concatenated_segments  item_code ,
--fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSIB.ORGANIZATION_ID, MSIB.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item_code,
moqd.organization_id,
moqd.subinventory_code,
inv_utilities.get_conc_segments(moqd.organization_id,moqd.locator_id) locator_code,
moqd.inventory_item_id,
--MIC.CATEGORY_CONCAT_SEGS item_category,
mic.category_concat_segs item_category,
--fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_cat_field', 'INV', 'MCAT', MIC.STRUCTURE_ID, NULL, MIC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') item_category,
msib.description item_desc,
decode(is_consigned, 1,
decode(moqd.owning_tp_type,
1, (select vendor_name||'-'||pvsa.vendor_site_code from po_vendors pv, po_vendor_sites_all pvsa where pvsa.vendor_id=pv.vendor_id and pvsa.vendor_site_id=moqd.owning_organization_id),
2, (select(substr(hout.name,1,60)||'-'||substr(mp.organization_code,1,3)) Party from hr_organization_information hoi, hr_all_organization_units_tl hout, mtl_parameters mp
where hoi.organization_id=hout.organization_id
and hout.organization_id=mp.organization_id
and hout.language=userenv ('LANG')
AND hoi.org_information1='OPERATING_UNIT'
AND hoi.org_information2='Y'
AND hoi.org_information_context='CLASS'
AND hoi.organization_id=moqd.owning_organization_id
AND moqd.organization_id<>moqd.owning_organization_id
)),null) owing_party,
case :p_age_level
when '1' then max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '2' then max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '3' then max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
end last_trx_date,
case :p_age_level
when '1' then min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '2' then min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '3' then min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
end first_trx_date,
moqd.primary_transaction_quantity on_hand,
case :p_age_level
when '1' then sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '2' then sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '3' then sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
end sum_on_hand,
moqd.primary_transaction_quantity * INV_AGERPXML_PKG.f_unit_cost(moqd.organization_id, moqd.inventory_item_id,moqd.locator_id,moqd.cost_group_id,nvl(moqd.orig_date_received,moqd.creation_date)) n_Value,
msib.primary_uom_code uom_code,
INV_AGERPXML_PKG.f_bucket_days_heading(:P_BUCKETS_ID,NVL(moqd.orig_date_received,moqd.creation_date)) buckets_days_heading --p_buckets_day
from mtl_onhand_quantities_detail moqd,
mtl_system_items_vl msib,
mtl_secondary_inventories_fk_v msiv,
mtl_item_categories_v mic,
mtl_item_locations_kfv mil,
cst_cost_groups ccg
where moqd.organization_id=msib.organization_id
and moqd.inventory_item_id=msib.inventory_item_id
and msib.organization_id=:p_org_id
and msib.inventory_asset_flag=decode(:p_exp_item,'2','Y',msib.inventory_asset_flag)
and msiv.organization_id=moqd.organization_id
and msiv.secondary_inventory_name=moqd.subinventory_code
and msiv.asset_inventory=decode(:p_exp_sub,'2',1,msiv.asset_inventory)
and moqd.cost_group_id=ccg.cost_group_id
and ccg.cost_group between nvl(:p_cg_from, ccg.cost_group) AND nvl(:p_cg_to, ccg.cost_group)
and msib.organization_id=mic.organization_id
and msib.inventory_item_id=mic.inventory_item_id
and moqd.organization_id=mil.organization_id (+)
and moqd.locator_id=mil.inventory_location_id(+)
and mic.category_set_id=:p_category_set
and 1 = 1
and 1 = 1
)
group by organization_id,
subinventory_code,
locator_code,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
INV_AGERPXML_PKG.f_bucket_days_seq(:p_buckets_id,buckets_days_heading),
buckets_days_heading
union all
--below is for heading all onhand and value is 0
select
organization_id,
subinventory_code,
locator_code,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
aablt.report_heading1||' '||aablt.report_heading2 buckets_days_heading,
aabl.bucket_sequence_num bucket_seq,
last_trx_date,
first_trx_date,
0 on_hand,
sum_on_hand,
0 n_Value
from
(select organization_id,
subinventory_code,
locator_code,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
buckets_days_heading,
INV_AGERPXML_PKG.f_bucket_days_seq(:P_BUCKETS_ID,buckets_days_heading) bucket_seq,
trunc(max(last_trx_date)) last_trx_date,
trunc(min(first_trx_date)) first_trx_date,
sum(on_hand) on_hand,
max(sum_on_hand) sum_on_hand,
sum(n_Value) n_Value
from
(select
msib.concatenated_segments   item_code ,
--fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSIB.ORGANIZATION_ID, MSIB.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item_code,
moqd.organization_id,
moqd.subinventory_code,
inv_utilities.get_conc_segments(moqd.organization_id,moqd.locator_id) locator_code,
moqd.inventory_item_id,
mic.category_concat_segs item_category,
--fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_cat_field', 'INV', 'MCAT', MIC.STRUCTURE_ID, NULL, MIC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') item_category,
msib.description item_desc,
decode(is_consigned, 1,
decode(moqd.owning_tp_type,
1, (select vendor_name||'-'||pvsa.vendor_site_code from po_vendors pv, po_vendor_sites_all pvsa where pvsa.vendor_id=pv.vendor_id and pvsa.vendor_site_id=moqd.owning_organization_id),
2, (select(substr(hout.name,1,60)||'-'||substr(mp.organization_code,1,3)) Party from hr_organization_information hoi, hr_all_organization_units_tl hout, mtl_parameters mp
where hoi.organization_id=hout.organization_id
and hout.organization_id=mp.organization_id
and hout.language=userenv ('LANG')
AND hoi.org_information1='OPERATING_UNIT'
AND hoi.org_information2='Y'
AND hoi.org_information_context='CLASS'
AND hoi.organization_id=moqd.owning_organization_id
AND moqd.organization_id<>moqd.owning_organization_id
)),null) owing_party,
case :p_age_level
when '1' then max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '2' then max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '3' then max(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
end last_trx_date,
case :p_age_level
when '1' then min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '2' then min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '3' then min(moqd.last_update_date) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
end first_trx_date,
moqd.primary_transaction_quantity on_hand,
case :p_age_level
when '1' then sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '2' then sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
when '3' then sum(moqd.primary_transaction_quantity) over(partition by moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.is_consigned,moqd.owning_tp_type,decode(moqd.is_consigned,1,moqd.owning_organization_id,null))
end sum_on_hand,
moqd.primary_transaction_quantity * INV_AGERPXML_PKG.f_unit_cost(moqd.organization_id, moqd.inventory_item_id,moqd.locator_id,moqd.cost_group_id,NVL(moqd.orig_date_received,moqd.creation_date)) n_Value,
msib.PRIMARY_UOM_CODE uom_code,
INV_AGERPXML_PKG.f_bucket_days_heading(:P_BUCKETS_ID,NVL(moqd.orig_date_received,moqd.creation_date)) buckets_days_heading --p_buckets_day
from mtl_onhand_quantities_detail moqd,
mtl_system_items_vl msib,
mtl_item_categories_v mic,
mtl_secondary_inventories_fk_v msiv,
mtl_item_locations_kfv mil,
cst_cost_groups ccg
where moqd.organization_id=msib.organization_id
and moqd.inventory_item_id=msib.inventory_item_id
and moqd.organization_id=:p_org_id
and msib.inventory_asset_flag=decode(:p_exp_item,'2','Y', msib.inventory_asset_flag)
and msiv.organization_id=moqd.organization_id
and msiv.secondary_inventory_name=moqd.subinventory_code
and msiv.asset_inventory=decode(:p_exp_sub,'2',1,msiv.asset_inventory)
and moqd.cost_group_id=ccg.cost_group_id
and ccg.cost_group between nvl(:p_cg_from, ccg.cost_group) and nvl(:p_cg_to, ccg.cost_group)
and msib.organization_id=mic.organization_id
and msib.inventory_item_id=mic.inventory_item_id
and moqd.organization_id=mil.organization_id (+)
and moqd.locator_id=mil.inventory_location_id(+)
and mic.category_set_id=:p_category_set
and 1 = 1
and 1 = 1
)
group by organization_id,
subinventory_code,
locator_code,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
INV_AGERPXML_PKG.f_bucket_days_seq(:P_BUCKETS_ID,buckets_days_heading),
buckets_days_heading
) detail,
ar_aging_buckets aab,
ar_aging_bucket_lines_b aabl, --AR_AGING_BUCKET_LINES_vl for language
ar_aging_bucket_lines_tl aablt
where aab.aging_type='INVENTORYAGE'
and aabl.aging_bucket_id=aab.aging_bucket_id
and aab.status='A'
and aablt.aging_bucket_line_id=aabl.aging_bucket_line_id
and aablt.language=userenv ('LANG')
and aab.aging_bucket_id=:p_buckets_id
and aabl.type='INVENTORYAGE'
  )
where bucket_seq not in (-99999,-99998)
group by organization_id,
decode(:p_age_level,1,null,subinventory_code) ,
decode(:p_age_level,3,locator_code,null) ,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
buckets_days_heading,
bucket_seq
order by
organization_id,
subinventory_code,
locator_code,
bucket_seq,
inventory_item_id,
item_code,
item_category,
item_desc,
uom_code,
owing_party,
buckets_days_heading