WIP Repetitive Pick List

Description
Categories: BI Publisher
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
 
LOV Oracle
Supply Type
 
LOV Oracle
Supply Subinventory
 
LOV Oracle
Org_Id
 
Number
Quantity Precision
 
Number