WIP Repetitive Schedule Shortage

Description
Categories: BI Publisher, Manufacturing
Application: Work in Process
Source: Repetitive Schedule Shortage Report (XML)
Short Name: WIPREORQ_XML
DB package: WIP_WIPREORQ_XMLP_PKG
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
Quantity Field Precision
 
Number
Org_Id
 
Number
To
 
Components From
 
To
 
LOV Oracle
Assemblies From
 
LOV Oracle
To
 
LOV Oracle
Lines From
 
LOV Oracle
Supply Days
 
Number
Include Supplier
 
LOV Oracle
Include Bulk
 
LOV Oracle
Include Qty Ahead/Behind
 
LOV Oracle
Include Nettable Only
 
LOV Oracle
Report Type
 
LOV Oracle