INV Inactive items

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*/

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*/