INV Shortages Summary

SELECT
	 DECODE(:P_GROUP_BY, 1, &P_CAT_FLEX, 3, MSSV.item_planner_code, 'X') C_GROUP_FLEX
	,MSSV.object_type
	,MSSV.object_type_meaning
	,DECODE(:P_GROUP_BY, 2, 'X', &P_ITEM_FLEX) C_ITEM_FLEX
	,DECODE(:P_GROUP_BY, 2, 'X', MSSV.item_description) C_ITEM_DESCRIPTION_POS
	,DECODE(:P_GROUP_BY, 2, MSSV.item_description, 'X') C_ITEM_DESCRIPTION_HDR
	,DECODE(:P_GROUP_BY, 1, MSSV.item_planner_code, 'X') C_PLANNER_CODE_POS
	,DECODE(:P_GROUP_BY, 1, 'X', MSSV.item_planner_code) C_PLANNER_CODE_HDR
	,MSSV.uom_code
	,DECODE(:P_GROUP_BY, 2, MSSV.uom_code, 'X') C_UOM_CODE_HDR
	,MSSV.quantity_open
	,MSSV.object_name
	,MSSV.object_detail_name
	,MP.ORGANIZATION_CODE||'   '||HOU.NAME  ORG_NAME
	,DECODE(:P_GROUP_BY, 2, 0, MSSV.inventory_item_id) C_INVENTORY_ITEM_ID_POS
	,DECODE(:P_GROUP_BY, 2, MSSV.inventory_item_id, 0) C_INVENTORY_ITEM_ID_HDR, 
    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, 
	INV_INVTRSHS_XMLP_PKG.c_net_qty_hdrformula(DECODE ( :P_GROUP_BY , 2 , MSSV.inventory_item_id , 0 )) C_NET_QTY_HDR, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_CAT_PAD, 
	null C_HDR_ITEM_PAD, 
	null C_HDR_ITEM_FIELD, 
	INV_INVTRSHS_XMLP_PKG.c_notificationformula(DECODE ( :P_GROUP_BY , 2 , MSSV.inventory_item_id , 0 ), DECODE ( :P_GROUP_BY , 2 , 0 , MSSV.inventory_item_id )) C_NOTIFICATION, 
	INV_INVTRSHS_XMLP_PKG.c_net_qty_posformula(DECODE ( :P_GROUP_BY , 2 , 0 , MSSV.inventory_item_id )) C_NET_QTY_POS, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_pos_item_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_POS_ITEM_PAD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_pos_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_POS_ITEM_FIELD
FROM
	 mtl_short_summary_v MSSV
	,hr_organization_units HOU
	,mtl_parameters MP
	,mtl_system_items MSI
	,mtl_item_categories MIC, mtl_categories MC
WHERE
	MSSV.organization_id = :P_ORG_ID
AND	MSSV.seq_num = :P_SEQ_NUM
AND	MSI.inventory_item_id = MSSV.inventory_item_id
AND	MSI.organization_id = :P_ORG_ID
AND	MSSV.organization_id = MP.organization_id
AND	MP.organization_id = HOU.organization_id
AND	&P_ITEM_WHERE
AND	&P_CAT_WHERE &C_CAT_WHERE &C_ITEMPLANNER_WHERE
	&C_ONHAND_WHERE
and :P_GROUP_BY in (1,3)
union
SELECT
	 DECODE(:P_GROUP_BY, 2, &P_ITEM_FLEX,'X') C_GROUP_FLEX
	,MSSV.object_type
	,MSSV.object_type_meaning
	,DECODE(:P_GROUP_BY, 2, 'X', &P_ITEM_FLEX) C_ITEM_FLEX
	,DECODE(:P_GROUP_BY, 2, 'X', MSSV.item_description) C_ITEM_DESCRIPTION_POS
	,DECODE(:P_GROUP_BY, 2, MSSV.item_description, 'X') C_ITEM_DESCRIPTION_HDR
	,DECODE(:P_GROUP_BY, 1, MSSV.item_planner_code, 'X') C_PLANNER_CODE_POS
	,DECODE(:P_GROUP_BY, 1, 'X', MSSV.item_planner_code) C_PLANNER_CODE_HDR
	,MSSV.uom_code
	,DECODE(:P_GROUP_BY, 2, MSSV.uom_code, 'X') C_UOM_CODE_HDR
	,MSSV.quantity_open
	,MSSV.object_name
	,MSSV.object_detail_name
	,MP.ORGANIZATION_CODE||'   '||HOU.NAME  ORG_NAME
	,DECODE(:P_GROUP_BY, 2, 0, MSSV.inventory_item_id) C_INVENTORY_ITEM_ID_POS
	,DECODE(:P_GROUP_BY, 2, MSSV.inventory_item_id, 0) C_INVENTORY_ITEM_ID_HDR, 
    null C_CAT_FIELD,
   --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, 
	INV_INVTRSHS_XMLP_PKG.c_net_qty_hdrformula(DECODE ( :P_GROUP_BY , 2 , MSSV.inventory_item_id , 0 )) C_NET_QTY_HDR, 
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_CAT_PAD, 
	null C_CAT_PAD,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_hdr_item_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_HDR_ITEM_PAD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_hdr_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_HDR_ITEM_FIELD, 
	INV_INVTRSHS_XMLP_PKG.c_notificationformula(DECODE ( :P_GROUP_BY , 2 , MSSV.inventory_item_id , 0 ), DECODE ( :P_GROUP_BY , 2 , 0 , MSSV.inventory_item_id )) C_NOTIFICATION, 
	INV_INVTRSHS_XMLP_PKG.c_net_qty_posformula(DECODE ( :P_GROUP_BY , 2 , 0 , MSSV.inventory_item_id )) C_NET_QTY_POS, 
null C_POS_ITEM_PAD,
null C_POS_ITEM_FIELD
--	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_pos_item_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_POS_ITEM_PAD, 
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_pos_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_POS_ITEM_FIELD
FROM
	 mtl_short_summary_v MSSV
	,hr_organization_units HOU
	,mtl_parameters MP
	,mtl_system_items MSI
	/* Do not select from category tables.*/
WHERE
	MSSV.organization_id = :P_ORG_ID
AND	MSSV.seq_num = :P_SEQ_NUM
AND	MSI.inventory_item_id = MSSV.inventory_item_id
AND	MSI.organization_id = :P_ORG_ID
AND	MSSV.organization_id = MP.organization_id
AND	MP.organization_id = HOU.organization_id
AND	&P_ITEM_WHERE
AND	&P_CAT_WHERE
/*&C_CAT_WHERE */
&C_ITEMPLANNER_WHERE
&C_ONHAND_WHERE
and :P_GROUP_BY=2
&C_ORDER_BY