WIP Discrete Job Pick List
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Discrete Job Pick List Report
Application: Work in Process
Source: Discrete Job Pick List Report (XML)
Short Name: WIPDJPCK_XML
DB package: WIP_WIPDJPCK_XMLP_PKG
Description: Discrete Job Pick List Report
Application: Work in Process
Source: Discrete Job Pick List Report (XML)
Short Name: WIPDJPCK_XML
DB package: WIP_WIPDJPCK_XMLP_PKG
Run
WIP Discrete Job Pick List and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
select rq.Job, rq.Job_Desc, rq.Status, rq.C_ASSY, rq.Assembly_ID, rq.Assembly_Desc, rq.Planner, rq.Scheduled_Start, rq.Class, rq.Class_Desc, rq.Job_Start_Qty, rq.Scheduled_Complete, rq.Supply_Code, rq.Wip_Supply_Type, rq.Date_Required, rq.Component, rq.Comp_Desc, rq.Op_Seq, rq.Supply_Subinv, rq.Supply_Locator, rq.Locator_Id, rq.Quantity_Open, rq.UOM, rq.On_Hand_Subinv, rq.On_Hand_Locator, rq.Supply_Flag, rq.Quantity_On_Hand, rq.Build_Sequence, rq.Schedule_Group_Name, rq.Item_Seq, rq.C_ASSY_DISP, rq.C_Order, rq.C_Comp_Sort, rq.C_Loc_Sort, rq.C_Component_Disp, rq.C_Locator_Disp, rq.C_Det_Loc_Disp, rq.Scheduled_Start1, rq.Scheduled_complete1, rq.DATE_REQUIRED1 FROM (SELECT DECODE(:P_SORT_BY, 9, 'X',WE.WIP_ENTITY_NAME) Job , DECODE(:P_SORT_BY, 9, 'X',WE.DESCRIPTION ) Job_Desc , DECODE(:P_SORT_BY, 9, 'X',ML1.MEANING ) Status , DECODE(:P_SORT_BY, 9, 'X',null ) C_ASSY , DECODE(:P_SORT_BY, 9, -1,NVL(WDJ.PRIMARY_ITEM_ID, -1)) Assembly_ID , DECODE(:P_SORT_BY, 9, 'X',MSITL.DESCRIPTION ) Assembly_Desc , DECODE(:P_SORT_BY, 9, 'X',MSI.PLANNER_CODE) Planner , DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_START_DATE) Scheduled_Start , DECODE(:P_SORT_BY, 9, 'X',WDJ.CLASS_CODE ) Class , DECODE(:P_SORT_BY, 9, 'X',WAC.DESCRIPTION) Class_Desc , DECODE(:P_SORT_BY, 9, 0,WDJ.START_QUANTITY) Job_Start_Qty , DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_COMPLETION_DATE) Scheduled_Complete , DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE) Supply_Code , ML2.MEANING Wip_Supply_Type , DECODE(:P_SORT_BY, 9, SYSDATE,WRO.DATE_REQUIRED ) Date_Required , null Component , MSI2.DESCRIPTION Comp_Desc , DECODE(:P_SORT_BY, 9, 0,WRO.OPERATION_SEQ_NUM ) Op_Seq , WRO.SUPPLY_SUBINVENTORY Supply_Subinv , null Supply_Locator , NVL(MIL.INVENTORY_LOCATION_ID,-1) Locator_Id , SUM(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED) Quantity_Open , MSI2.PRIMARY_UOM_CODE UOM , MOQ2.SUBINVENTORY_CODE On_Hand_Subinv , DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,&P_DETAIL_LOC) 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 , DECODE(:P_SORT_BY, 9,0, WDJ.BUILD_SEQUENCE) Build_Sequence , DECODE(:P_SORT_BY, 9, 'X',SG.SCHEDULE_GROUP_NAME) Schedule_Group_Name , DECODE(:P_SORT_BY, 9, 0, BIC.ITEM_NUM) Item_Seq, decode(WDJ.PRIMARY_ITEM_ID,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_ASSY_DISP, WIP_WIPDJPCK_XMLP_PKG.order_func(( MSI2.SEGMENT1 || '\n' || MSI2.SEGMENT2 || '\n' || MSI2.SEGMENT3 || '\n' || MSI2.SEGMENT4 || '\n' || MSI2.SEGMENT5 || '\n' || MSI2.SEGMENT6 || '\n' || MSI2.SEGMENT7 || '\n' || MSI2.SEGMENT8 || '\n' || MSI2.SEGMENT9 || '\n' || MSI2.SEGMENT10 || '\n' || MSI2.SEGMENT11 || '\n' || MSI2.SEGMENT12 || '\n' || MSI2.SEGMENT13 || '\n' || MSI2.SEGMENT14 || '\n' || MSI2.SEGMENT15 || '\n' || MSI2.SEGMENT16 || '\n' || MSI2.SEGMENT17 || '\n' || MSI2.SEGMENT18 || '\n' || MSI2.SEGMENT19 || '\n' || MSI2.SEGMENT20 ), ( MIL.SEGMENT1 || '\n' || MIL.SEGMENT2 || '\n' || MIL.SEGMENT3 || '\n' || MIL.SEGMENT4 || '\n' || MIL.SEGMENT5 || '\n' || MIL.SEGMENT6 || '\n' || MIL.SEGMENT7 || '\n' || MIL.SEGMENT8 || '\n' || MIL.SEGMENT9 || '\n' || MIL.SEGMENT10 || '\n' || MIL.SEGMENT11 || '\n' || MIL.SEGMENT12 || '\n' || MIL.SEGMENT13 || '\n' || MIL.SEGMENT14 || '\n' || MIL.SEGMENT15 || '\n' || MIL.SEGMENT16 || '\n' || MIL.SEGMENT17 || '\n' || MIL.SEGMENT18 || '\n' || MIL.SEGMENT19 || '\n' || MIL.SEGMENT20 ), :C_Component_Disp, :C_Locator_Disp, :C_Comp_Sort, :C_Loc_Sort, WRO.SUPPLY_SUBINVENTORY, DECODE ( :P_SORT_BY , 9 , 0 , WRO.OPERATION_SEQ_NUM ), DECODE ( :P_SORT_BY , 9 , SYSDATE , WRO.DATE_REQUIRED ), DECODE ( :P_SORT_BY , 9 , 0 , BIC.ITEM_NUM )) C_Order, 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_WIPDJPCK_XMLP_PKG.c_loc_sortformula(:C_Locator_Disp, NVL ( MIL.INVENTORY_LOCATION_ID , - 1 ), :C_Loc_Sort) C_Loc_Sort, 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_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_Det_Loc_Disp, DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_START_DATE,'DD-MON-YY')) Scheduled_Start1 ,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_COMPLETION_DATE,'DD-MON-YY')) Scheduled_complete1 , DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WRO.DATE_REQUIRED,'DD-MON-YY')) DATE_REQUIRED1 FROM MFG_LOOKUPS ML1 , MFG_LOOKUPS ML2 , MTL_ITEM_LOCATIONS MIL , MTL_ITEM_LOCATIONS MIL2 , MTL_SECONDARY_INVENTORIES MSINV , MTL_ITEM_QUANTITIES_VIEW MOQ2 , WIP_ENTITIES WE , MTL_SYSTEM_ITEMS_B MSI , MTL_SYSTEM_ITEMS_TL MSITL , WIP_ACCOUNTING_CLASSES WAC , MTL_SYSTEM_ITEMS_VL MSI2 , WIP_REQUIREMENT_OPERATIONS WRO , WIP_SCHEDULE_GROUPS SG , WIP_DISCRETE_JOBS WDJ , BOM_INVENTORY_COMPONENTS BIC WHERE WDJ.ORGANIZATION_ID = :P_ORGANIZATION_ID AND WDJ.STATUS_TYPE IN &C_Limit_Status AND SG.SCHEDULE_GROUP_ID &P_SG_OUTER = WDJ.SCHEDULE_GROUP_ID AND SG.ORGANIZATION_ID &P_SG_OUTER = WDJ.ORGANIZATION_ID AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS' AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE AND ML1.ENABLED_FLAG = 'Y' AND MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID AND MSI.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID AND MSITL.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID AND MSITL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID AND MSITL.LANGUAGE(+) = USERENV('LANG') AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND WE.ENTITY_TYPE IN (1,6) AND WE.ORGANIZATION_ID = :P_ORGANIZATION_ID &C_Limit_Jobs &C_Limit_Dates &C_Schedule_Group AND WAC.CLASS_CODE = WDJ.CLASS_CODE AND WAC.ORGANIZATION_ID = :P_ORGANIZATION_ID AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND WRO.ORGANIZATION_ID = :P_ORGANIZATION_ID AND WRO.WIP_SUPPLY_TYPE <> 6 AND ( ( WRO.REQUIRED_QUANTITY > 0 AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0 ) OR ( WRO.REQUIRED_QUANTITY < 0 AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED < 0 ) ) &C_Limit_Supply_Type &C_Limit_Subinv AND ML2.LOOKUP_TYPE = 'WIP_SUPPLY_PICK' AND ML2.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE) AND ML2.ENABLED_FLAG = 'Y' 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 AND MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID AND MIL2.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID AND BIC.COMPONENT_SEQUENCE_ID(+) = WRO.COMPONENT_SEQUENCE_ID AND NOT(NVL(MSI2.RECEIVING_ROUTING_ID,-1) = 3 AND MSI2.STOCK_ENABLED_FLAG = 'N' AND WE.ENTITY_TYPE <> 1 ) GROUP BY DECODE(:P_SORT_BY, 9, 'X',WE.WIP_ENTITY_NAME) , DECODE(:P_SORT_BY, 9, 'X',WE.DESCRIPTION ) , DECODE(:P_SORT_BY, 9, 'X',ML1.MEANING ) , DECODE(:P_SORT_BY, 9, 'X',null) , DECODE(:P_SORT_BY, 9, -1,NVL(WDJ.PRIMARY_ITEM_ID, -1)) , DECODE(:P_SORT_BY, 9, 'X',MSITL.DESCRIPTION ) , DECODE(:P_SORT_BY, 9, 'X',MSI.PLANNER_CODE) , DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_START_DATE) , DECODE(:P_SORT_BY, 9, 'X',WDJ.CLASS_CODE ) , DECODE(:P_SORT_BY, 9, 'X',WAC.DESCRIPTION) , DECODE(:P_SORT_BY, 9, 0,WDJ.START_QUANTITY) , DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_COMPLETION_DATE) , DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE) , ML2.MEANING , DECODE(:P_SORT_BY, 9, SYSDATE,WRO.DATE_REQUIRED ) , null , MSI2.DESCRIPTION , DECODE(:P_SORT_BY,9,0,WRO.OPERATION_SEQ_NUM ) , DECODE(:P_SORT_BY,9,0,BIC.ITEM_NUM) , WRO.SUPPLY_SUBINVENTORY , null , NVL(MIL.INVENTORY_LOCATION_ID,-1) , MSI2.PRIMARY_UOM_CODE , MOQ2.SUBINVENTORY_CODE , DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,&P_DETAIL_LOC) , 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(:P_SORT_BY, 9,0, WDJ.BUILD_SEQUENCE) , DECODE(:P_SORT_BY, 9, 'X',SG.SCHEDULE_GROUP_NAME), decode(WDJ.PRIMARY_ITEM_ID,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_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_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_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'), ( MSI2.SEGMENT1 || '\n' || MSI2.SEGMENT2 || '\n' || MSI2.SEGMENT3 || '\n' || MSI2.SEGMENT4 || '\n' || MSI2.SEGMENT5 || '\n' || MSI2.SEGMENT6 || '\n' || MSI2.SEGMENT7 || '\n' || MSI2.SEGMENT8 || '\n' || MSI2.SEGMENT9 || '\n' || MSI2.SEGMENT10 || '\n' || MSI2.SEGMENT11 || '\n' || MSI2.SEGMENT12 || '\n' || MSI2.SEGMENT13 || '\n' || MSI2.SEGMENT14 || '\n' || MSI2.SEGMENT15 || '\n' || MSI2.SEGMENT16 || '\n' || MSI2.SEGMENT17 || '\n' || MSI2.SEGMENT18 || '\n' || MSI2.SEGMENT19 || '\n' || MSI2.SEGMENT20 ), ( MIL.SEGMENT1 || '\n' || MIL.SEGMENT2 || '\n' || MIL.SEGMENT3 || '\n' || MIL.SEGMENT4 || '\n' || MIL.SEGMENT5 || '\n' || MIL.SEGMENT6 || '\n' || MIL.SEGMENT7 || '\n' || MIL.SEGMENT8 || '\n' || MIL.SEGMENT9 || '\n' || MIL.SEGMENT10 || '\n' || MIL.SEGMENT11 || '\n' || MIL.SEGMENT12 || '\n' || MIL.SEGMENT13 || '\n' || MIL.SEGMENT14 || '\n' || MIL.SEGMENT15 || '\n' || MIL.SEGMENT16 || '\n' || MIL.SEGMENT17 || '\n' || MIL.SEGMENT18 || '\n' || MIL.SEGMENT19 || '\n' || MIL.SEGMENT20 ), DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_START_DATE,'DD-MON-YY')) ,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_COMPLETION_DATE,'DD-MON-YY')) ,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WRO.DATE_REQUIRED,'DD-MON-YY')) UNION SELECT DECODE(:P_SORT_BY, 9, 'X',WE.WIP_ENTITY_NAME) Job , DECODE(:P_SORT_BY, 9, 'X',WE.DESCRIPTION ) Job_Desc , DECODE(:P_SORT_BY, 9, 'X',ML1.MEANING ) Status , DECODE(:P_SORT_BY, 9, 'X',null ) C_ASSY , DECODE(:P_SORT_BY, 9, -1,NVL(WDJ.PRIMARY_ITEM_ID, -1)) Assembly_ID , |