WIP Repetitive Schedule Shortage

➜ Back to Library
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
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
Download
Related Blitz Reports
WIP Repetitive Pick List MRP Pegging WIP Discrete Job Shortage CRP Resource Plan WIP Discrete Job Pick List WIP Required Components OPM WIP Value OPM2 WIP Resource Performance
Blitz Report™

Blitz Report™ provides multiple benefits: