WIP Repetitive Schedule Shortage

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