WIP Repetitive Schedule Shortage
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Repetitive Schedule Shortage Report
Application: Work in Process
Source: Repetitive Schedule Shortage Report (XML)
Short Name: WIPREORQ_XML
DB package: WIP_WIPREORQ_XMLP_PKG
Description: Repetitive Schedule Shortage Report
Application: Work in Process
Source: Repetitive Schedule Shortage Report (XML)
Short Name: WIPREORQ_XML
DB package: WIP_WIPREORQ_XMLP_PKG
Run
WIP Repetitive Schedule Shortage and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT WL.LINE_CODE Line , WL.DESCRIPTION Line_Desc , WE.WIP_ENTITY_NAME C_ASSY , WE.DESCRIPTION Assembly_Desc , null Component , MSI2.DESCRIPTION Comp_Desc , MSI2.INVENTORY_ITEM_ID Comp_Id , BD.DEPARTMENT_CODE Department , NVL(WRO.SUPPLY_SUBINVENTORY,' ') Supply_Subinv , null Supply_Locator , NVL(MIL.INVENTORY_LOCATION_ID,-1) Locator_Id , SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,:C_END_NUM) - GREATEST(BCD.NEXT_SEQ_NUM,:C_START_NUM)) * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE ) Quantity_Required , MSI2.PRIMARY_UOM_CODE UOM , DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,'-1'), DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0) Supply_Flag , NVL(MOQ2.QUANTITY,0) Quantity_On_Hand , SUM(WRO.QUANTITY_ISSUED - GREATEST((:C_START_NUM-BCD.NEXT_SEQ_NUM),0) *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE) Ahead_Behind , SUM(WRO.QUANTITY_ISSUED) Quantity_Issued , DECODE(:P_AHEAD_BEHIND,1,(SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,:C_END_NUM ) - GREATEST(BCD.NEXT_SEQ_NUM, :C_START_NUM )) * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE ) + SUM(GREATEST((:C_START_NUM-BCD.NEXT_SEQ_NUM),0) *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE) - SUM(WRO.QUANTITY_ISSUED)) ,2,(SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS, :C_END_NUM) - GREATEST(BCD.NEXT_SEQ_NUM,:C_START_NUM)) * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE ) - SUM(WRO.QUANTITY_ISSUED)) ) Qty_Open , DECODE(MSS.AVAILABILITY_TYPE,1,1,0) Availability_Type, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_disp_sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_Component_Disp_Sort, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_disp', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_Locator_Disp, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_disp', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_Component_Disp, WIP_WIPREORQ_XMLP_PKG.avail_qtyformula(:Net_Qty_Comp, :C_Item_Total_Qty) Avail_Qty, WIP_WIPREORQ_XMLP_PKG.c_qty_short_qohformula(:Net_Qty_Comp, :C_Item_Total_Qty, :C_Qty_Short_Formula, :C_Open_Requirements, :Avail_Qty) C_Qty_Short_QOH, WIP_WIPREORQ_XMLP_PKG.open_shortformula() Open_Short, WIP_WIPREORQ_XMLP_PKG.c_qty_short_sub_locformula(:C_Qty_Short_Formula, DECODE(:P_AHEAD_BEHIND,1,(SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,:C_END_NUM ) - GREATEST(BCD.NEXT_SEQ_NUM, :C_START_NUM )) * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE ) + SUM(GREATEST((:C_START_NUM-BCD.NEXT_SEQ_NUM),0) *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE) - SUM(WRO.QUANTITY_ISSUED)) ,2,(SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS, :C_END_NUM) - GREATEST(BCD.NEXT_SEQ_NUM,:C_START_NUM)) * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE ) - SUM(WRO.QUANTITY_ISSUED)) )) C_Qty_Short_Sub_Loc, WIP_WIPREORQ_XMLP_PKG.c_total_qty_formulaformula(:C_Total_Qty, DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,'-1'), DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0)) C_Total_Qty_Formula, WIP_WIPREORQ_XMLP_PKG.net_qty_detailformula(DECODE(MSS.AVAILABILITY_TYPE,1,1,0), NVL(MOQ2.QUANTITY,0)) Net_Qty_Detail FROM MTL_SECONDARY_INVENTORIES MSS , MTL_ITEM_LOCATIONS MIL , BOM_DEPARTMENTS BD , BOM_CALENDAR_DATES BCD , MTL_ITEM_QUANTITIES_VIEW MOQ2 , WIP_LINES WL , MTL_SYSTEM_ITEMS MSI2 , WIP_ENTITIES WE , WIP_REQUIREMENT_OPERATIONS WRO , WIP_REPETITIVE_SCHEDULES WRS WHERE WL.ORGANIZATION_ID = :P_ORGANIZATION_ID &C_Limit_Lines AND WRS.ORGANIZATION_ID = :P_ORGANIZATION_ID AND WRS.LINE_ID = WL.LINE_ID AND WRS.STATUS_TYPE IN (1,3,4,6) AND WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID AND WE.ORGANIZATION_ID = :P_ORGANIZATION_ID &C_ITEM_WHERE AND WRO.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID AND WRO.ORGANIZATION_ID = :P_ORGANIZATION_ID AND WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID &C_Limit_Bulk &C_Limit_Vendor AND WRO.WIP_SUPPLY_TYPE <> 6 AND WRO.REQUIRED_QUANTITY > 0 AND BCD.CALENDAR_CODE = :C_CALENDAR_CODE AND BCD.EXCEPTION_SET_ID = :C_EXCEPTION_SET AND BCD.CALENDAR_DATE = WRO.DATE_REQUIRED AND (BCD.NEXT_SEQ_NUM < :C_END_NUM AND (BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS) > :C_START_NUM) AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID AND BD.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID AND MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID AND MIL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID AND MSI2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID AND MSI2.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND MOQ2.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID AND MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID AND MSS.SECONDARY_INVENTORY_NAME(+) = MOQ2.SUBINVENTORY_CODE AND MSS.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID &C_Limit_Components AND &P_FLEXWHERE &P_ITEM_ORG GROUP BY WL.LINE_CODE , WL.DESCRIPTION , WE.WIP_ENTITY_NAME , WE.DESCRIPTION , &P_COMP , MSI2.DESCRIPTION , MSI2.INVENTORY_ITEM_ID , BD.DEPARTMENT_CODE , NVL(WRO.SUPPLY_SUBINVENTORY,' ') , &P_SUPPLY_LOC , NVL(MIL.INVENTORY_LOCATION_ID,-1) , MSI2.PRIMARY_UOM_CODE , MOQ2.SUBINVENTORY_CODE , MOQ2.LOCATOR_ID , MOQ2.REVISION , DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,'-1'), DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0) , NVL(MOQ2.QUANTITY,0) , DECODE(MSS.AVAILABILITY_TYPE,1,1,0) ,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_disp_sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_disp', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_disp', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') &C_QTY_OPEN_SHORT ORDER BY 1 ASC, 2 ASC, 3 ASC, 20 ASC, 8 ASC, 9 ASC, 21 ASC, 14 ASC, 15 ASC, 19 ASC |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Report Type |
|
LOV Oracle | |
Include Nettable Only |
|
LOV Oracle | |
Include Qty Ahead/Behind |
|
LOV Oracle | |
Include Bulk |
|
LOV Oracle | |
Include Supplier |
|
LOV Oracle | |
Supply Days |
|
Number | |
Lines From |
|
LOV Oracle | |
To |
|
LOV Oracle | |
Assemblies From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Components From |
|
Char | |
To 3 |
|
Char |