RD INV Inventory Aging

Description
Categories: BI Publisher
Application: Inventory
Source: Inventory Aging Report(XML)
Short Name: INVAGERP_XML
DB package: INV_AGERPXML_PKG
Run RD INV Inventory Aging and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Title
 
Char
Category Set
 
LOV Oracle
Category From
 
Char
Category To
 
Char
Item From
 
Char
Item To
 
Char
Level
 
LOV Oracle
Cost Group From
 
LOV Oracle
Cost Group To
 
LOV Oracle
Order By
 
LOV Oracle
Buckets Days
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Include Expense Subinventories
 
LOV Oracle
Download
Blitz Report™

Blitz Report™ provides multiple benefits: