RD INV Inventory Aging

Description
Categories: BI Publisher
Columns: Bucket Seq, Organization Id, Subinventory Code, Locator Code, Inventory Item Id, Item Code, Item Category, Item Desc, UOM Code, Owing Party ...
Application: Inventory
Source: Inventory Aging Report(XML)
Short Name: INVAGERP_XML
DB package: INV_AGERPXML_PKG
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
 
Category Set
 
LOV Oracle
Category From
 
Category To
 
Item From
 
Item To
 
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
Category Structure