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
Description: Shortages Summary report
Application: Inventory
Source: Shortages Summary report (XML)
Short Name: INVTRSHS_XML
DB package: INV_INVTRSHS_XMLP_PKG
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 | |
---|---|---|---|
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 |