WIP Repetitive Pick List

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Repetitive Pick List Report
Application: Work in Process
Source: Repetitive Pick List Report (XML)
Short Name: WIPREPCK_XML
DB package: WIP_WIPREPCK_XMLP_PKG 
SELECT WL.LINE_CODE                       Line
,      WL.DESCRIPTION                     Line_Desc
,      DECODE(:P_SORT_BY,6,WE.WIP_ENTITY_NAME,'X')                 C_ASSY
,      DECODE(:P_SORT_BY,6,WE.DESCRIPTION,'X')         Assembly_Desc
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
                                          Supply_Code
,      ML.MEANING                         Wip_Supply_Type
--,      &P_COMP                            Component
,      MSI2.DESCRIPTION                   Comp_Desc
,      DECODE(:P_SORT_BY,6,WRO.OPERATION_SEQ_NUM,1)  Op_Seq
,      NVL(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
,      MOQ2.SUBINVENTORY_CODE             On_Hand_Subinv
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'))
                                          On_Hand_Locator
,      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,
	WIP_WIPREPCK_XMLP_PKG.order_func(DECODE ( :P_SORT_BY , 6 , WRO.OPERATION_SEQ_NUM , 1 ), NVL ( WRO.SUPPLY_SUBINVENTORY , ' ' ), 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'), NVL ( BD.DEPARTMENT_CODE , ' ' ), 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_comp_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_loc_sort', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE')) C_Order,
	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,
	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_comp_sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_Comp_Sort,
	WIP_WIPREPCK_XMLP_PKG.c_loc_sortformula(NVL ( MIL.INVENTORY_LOCATION_ID , - 1 ), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_sort', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE')) C_Loc_Sort
FROM   MFG_LOOKUPS ML
,      MTL_SYSTEM_ITEMS MSI2
,      MTL_ITEM_LOCATIONS MIL
,      MTL_ITEM_LOCATIONS MIL2
,      MTL_SECONDARY_INVENTORIES MSINV
,      BOM_DEPARTMENTS BD
,      BOM_CALENDAR_DATES BCD
,      MTL_ITEM_QUANTITIES_VIEW MOQ2
,      WIP_REQUIREMENT_OPERATIONS WRO
,      WIP_REPETITIVE_SCHEDULES WRS
,      WIP_LINES WL
,      WIP_ENTITIES WE
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 &C_Limit_Status
AND    WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND    WE.ORGANIZATION_ID = :P_ORGANIZATION_ID
&C_Limit_Assemblies
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
AND    WRO.WIP_SUPPLY_TYPE <> 6
AND    WRO.REQUIRED_QUANTITY != 0
&C_Limit_Supply_Type
&C_Limit_Subinv
AND    ML.LOOKUP_TYPE = 'WIP_SUPPLY_PICK'
AND    ML.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND    ML.ENABLED_FLAG = 'Y'
AND    BCD.CALENDAR_CODE = :C_CALENDAR_CODE
AND    BCD.EXCEPTION_SET_ID = :C_EXCEPTION_SET
AND    BCD.CALENDAR_DATE = TRUNC(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    MSINV.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MSINV.SECONDARY_INVENTORY_NAME(+) = MOQ2.SUBINVENTORY_CODE
AND    (MSINV.DISABLE_DATE IS NULL OR
             MSINV.DISABLE_DATE > TRUNC(SYSDATE))
AND    MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND    MIL2.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
GROUP BY WL.LINE_CODE
,      WL.DESCRIPTION
,      DECODE(:P_SORT_BY,6,WE.WIP_ENTITY_NAME,'X')
,      DECODE(:P_SORT_BY,6,WE.DESCRIPTION,'X')
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
,      ML.MEANING
--,      &P_COMP
,      MSI2.DESCRIPTION
,      DECODE(:P_SORT_BY,6,WRO.OPERATION_SEQ_NUM,1)
,      NVL(BD.DEPARTMENT_CODE,' ')
,      NVL(WRO.SUPPLY_SUBINVENTORY,' ')
--,      &P_SUPPLY_LOC
,      NVL(MIL.INVENTORY_LOCATION_ID,-1)
,      MSI2.PRIMARY_UOM_CODE
,      MOQ2.SUBINVENTORY_CODE
,     MOQ2.REVISION
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'))
,      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),
	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') ,
	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_comp_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_loc_sort', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE')
ORDER BY 1 ASC,2 ASC,3 ASC,5 ASC,19,14 ASC,15 ASC
Parameter Name SQL text Validation
Sort By 
 
 LOV Oracle
Transactable Only 
 
 LOV Oracle
Start Date 
 
 Date
Days of Supply 
 
 Number
Lines From 
 
 LOV Oracle
To 
 
 LOV Oracle
Assemblies From 
 
 LOV Oracle
To 2 
 
 LOV Oracle
Supply Type 
 
 LOV Oracle
Supply Subinventory 
 
 LOV Oracle
Download
Related Blitz Reports
WIP Repetitive Schedule Shortage MRP Pegging CRP Resource Plan WIP Discrete Job Shortage WIP Discrete Job Pick List CAC WIP Resource Efficiency WIP Required Components CAC Material Account Detail CAC ICP PII Material Account Detail
Blitz Report™

Blitz Report™ provides multiple benefits: