INV Shortages Summary

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Shortages Summary report
Application: Inventory
Source: Shortages Summary report (XML)
Short Name: INVTRSHS_XML
DB package: INV_INVTRSHS_XMLP_PKG
Run INV Shortages Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Organization
 
Number
Item Category Structure
 
Number
Group Option
 
LOV Oracle
Item Category Set
 
LOV Oracle
Item Categories From
 
Char
To
 
Char
Items From
 
Char
To 2
 
Char
Item Planner
 
LOV Oracle
With on-hand quantity only
 
LOV Oracle
Send notifications
 
LOV Oracle