INV Inactive items

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Inactive items report (XML)
Short Name: INVIRSLO_XML
DB package: INV_INVIRSLO_XMLP_PKG
select	/*+ opt_param('_gby_hash_aggregation_enabled' 'false') */ decode(:P_break_id, 1 , mmt.subinventory_code, 'X')  subinventory,
	&P_item_flex		C_item_flex,
	mmt.revision			rev,
	msi.description,
	&P_cat_flex		C_cat_flex,
	msi.inventory_item_status_code	status,
	max(mmt.transaction_date)	Last_txn_date,
	INV_MEANING_SEL.C_QTY_ON_HAND(msi.inventory_item_id, :P_org_id,mmt.subinventory_code,:P_break_id)	Quantity,
	msi.primary_uom_code		uom,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')  C_item_field
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_item_pad
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_cat_field
from	mtl_system_items 		msi,
	mtl_material_transactions	mmt,
	mtl_item_categories		mic,
	mtl_categories		mc
where	mmt.inventory_item_id = msi.inventory_item_id 
and 	mmt.organization_id = :P_org_id
and 	msi.organization_id = :P_org_id
and	msi.inventory_item_flag = 'Y'
and	mmt.inventory_item_id =  mic.inventory_item_id
and	mic.category_set_id = :P_cat_set_id
and	mic.organization_id = :P_org_id
and	mic.category_id = mc.category_id 
&C_subinv_where
group by  fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),decode(:P_break_id, 1 , mmt.subinventory_code, 'X'),fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),
	&P_item_flex,mmt.revision,msi.description,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
	&P_cat_flex,msi.inventory_item_status_code,msi.primary_uom_code,
	INV_MEANING_SEL.C_QTY_ON_HAND(msi.inventory_item_id, :P_org_id,mmt.subinventory_code,:P_break_id)
having max(mmt.transaction_date) <= :P_cutoff_date
order by  subinventory,C_item_pad,mmt.revision, msi.description,C_cat_flex,
msi.inventory_item_status_code,Last_txn_date,Quantity,uom
/*order by  mmt.revision, msi.description,
msi.inventory_item_status_code*/
Parameter Name SQL text Validation
Dynamic Quantity Precision
 
Number
Organization
 
Number
To
 
LOV Oracle
Subinventories From
 
LOV Oracle
Inactive Since
 
Date
Category Set
 
LOV Oracle
Display and Break on Subinventory
 
LOV Oracle