WIP Discrete Job Shortage
Description
Categories: BI Publisher
Application: Work in Process
Source: Discrete Job Shortage Report (XML)
Short Name: WIPDJORQ_XML
DB package: WIP_WIPDJORQ_XMLP_PKG
Source: Discrete Job Shortage Report (XML)
Short Name: WIPDJORQ_XML
DB package: WIP_WIPDJORQ_XMLP_PKG
SELECT WE.WIP_ENTITY_NAME Job , DECODE(:P_SORT_BY, 11, WE.DESCRIPTION ,'X') Job_Desc , BOD.DEPARTMENT_CODE Department , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy1', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ASSY , NVL(WDJ.PRIMARY_ITEM_ID, -1) Assembly_ID , DECODE(:P_SORT_BY, 11,MSITL.DESCRIPTION, 'X') Assembly_Desc , DECODE(:P_SORT_BY, 11,WDJ.SCHEDULED_START_DATE,SYSDATE) Scheduled_Start , DECODE(:P_SORT_BY, 11,WDJ.START_QUANTITY,-1) Job_Start_Qty , DECODE(:P_SORT_BY, 11,WDJ.SCHEDULED_COMPLETION_DATE ,SYSDATE) Scheduled_Complete --, to_date(WRO.DATE_REQUIRED,'DD-MON-YY') Date_Required , WRO.DATE_REQUIRED Date_Required --To fix bug 10270399 removed explicit conversion of date to to_date , null Component , WRO.INVENTORY_ITEM_ID Comp_id , MSI2.DESCRIPTION Comp_Desc , DECODE(:P_SORT_BY,9,MSI2.PLANNER_CODE,13,MSI2.PLANNER_CODE,'X') planner_code , DECODE(:P_SORT_BY,9,ML.MEANING ,13,ML.MEANING ,'X') make_buy , DECODE(:P_SORT_BY,9,EMP.FULL_NAME,13,EMP.FULL_NAME,'X') buyer_name , WRO.SUPPLY_SUBINVENTORY Supply_Subinv , null Supply_Locator , NVL(MIL.INVENTORY_LOCATION_ID,-1) Locator_Id , SUM(WRO.QUANTITY_ISSUED) Quantity_Issued , SUM(WRO.REQUIRED_QUANTITY ) Quantity_Required , SUM(WRO.REQUIRED_QUANTITY) - SUM(WRO.QUANTITY_ISSUED ) Quantity_Open , 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 , DECODE(MSS.AVAILABILITY_TYPE,1,1,0) Availability_Type , SG.SCHEDULE_GROUP_NAME , WDJ.BUILD_SEQUENCE, WIP_WIPDJORQ_XMLP_PKG.master_break_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 ), :C_Component_Disp, :C_Component_Sort, WE.WIP_ENTITY_NAME, BOD.DEPARTMENT_CODE, DECODE ( :P_SORT_BY , 9 , ML.MEANING , 13 , ML.MEANING , 'X' ), DECODE ( :P_SORT_BY , 9 , EMP.FULL_NAME , 13 , EMP.FULL_NAME , 'X' ), DECODE ( :P_SORT_BY , 9 , MSI2.PLANNER_CODE , 13 , MSI2.PLANNER_CODE , 'X' ), WRO.DATE_REQUIRED) C_Master_Break, WIP_WIPDJORQ_XMLP_PKG.c_break_jobformula(WE.WIP_ENTITY_NAME) C_Break_Job, WIP_WIPDJORQ_XMLP_PKG.c_break_comp_descformula(MSI2.DESCRIPTION) C_Break_Comp_Desc, WIP_WIPDJORQ_XMLP_PKG.c_break_date_requiredformula(WRO.DATE_REQUIRED) C_Break_Date_Required, WIP_WIPDJORQ_XMLP_PKG.c_break_departmentformula(BOD.DEPARTMENT_CODE) C_Break_Department, WIP_WIPDJORQ_XMLP_PKG.c_break_comp_dispformula(( 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 ), :C_Component_Disp) C_Break_Comp_Disp, WIP_WIPDJORQ_XMLP_PKG.c_break_assy_dispformula(( MSI.SEGMENT1 || '\n' || MSI.SEGMENT2 || '\n' || MSI.SEGMENT3 || '\n' || MSI.SEGMENT4 || '\n' || MSI.SEGMENT5 || '\n' || MSI.SEGMENT6 || '\n' || MSI.SEGMENT7 || '\n' || MSI.SEGMENT8 || '\n' || MSI.SEGMENT9 || '\n' || MSI.SEGMENT10 || '\n' || MSI.SEGMENT11 || '\n' || MSI.SEGMENT12 || '\n' || MSI.SEGMENT13 || '\n' || MSI.SEGMENT14 || '\n' || MSI.SEGMENT15 || '\n' || MSI.SEGMENT16 || '\n' || MSI.SEGMENT17 || '\n' || MSI.SEGMENT18 || '\n' || MSI.SEGMENT19 || '\n' || MSI.SEGMENT20 ), :C_ASSY_DISP) C_Break_Assy_Disp, WIP_WIPDJORQ_XMLP_PKG.c_tot_qty_shortformula(:C_TOTAL_AVAIL_QTY, :C_TOTAL_AVAIL_NET_QTY, :C_TOT_QTY_OPEN) C_TOT_QTY_SHORT, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_Component_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_assy_disp', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ASSY_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_WIPDJORQ_XMLP_PKG.avail_qtyformula(:Net_Qty_Comp, :C_Item_Total_Qty) Avail_Qty, WIP_WIPDJORQ_XMLP_PKG.c_qty_short_qohformula(:C_Qty_Short_Formula, :C_Open_Requirements, :Avail_Qty) C_Qty_Short_QOH, WIP_WIPDJORQ_XMLP_PKG.c_display_formulaformula(:C_Qty_Short_QOH) C_Display_Formula, WIP_WIPDJORQ_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_WIPDJORQ_XMLP_PKG.net_qty_detailformula(DECODE ( MSS.AVAILABILITY_TYPE , 1 , 1 , 0 ), NVL ( MOQ2.QUANTITY , 0 )) Net_Qty_Detail FROM PER_ALL_PEOPLE_F EMP , MFG_LOOKUPS ML , MTL_SECONDARY_INVENTORIES MSS , MTL_ITEM_LOCATIONS MIL , MTL_ITEM_QUANTITIES_VIEW MOQ2 , MTL_SYSTEM_ITEMS_B MSI , MTL_SYSTEM_ITEMS_TL MSITL , MTL_SYSTEM_ITEMS_VL MSI2 , BOM_DEPARTMENTS BOD , WIP_SCHEDULE_GROUPS SG , WIP_ENTITIES WE , WIP_DISCRETE_JOBS WDJ , WIP_REQUIREMENT_OPERATIONS WRO WHERE WDJ.ORGANIZATION_ID = :P_ORGANIZATION_ID AND WDJ.STATUS_TYPE IN (1,3,4,6) AND SG.SCHEDULE_GROUP_ID (+) = WDJ.SCHEDULE_GROUP_ID AND SG.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID &C_SCHEDULE_GROUP AND BOD.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID AND BOD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID 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,5) AND WE.ORGANIZATION_ID = :P_ORGANIZATION_ID &C_Limit_Jobs &C_Limit_Dates &C_Limit_Department &c_limit_planner &c_limit_buyer AND NVL(MSI2.PLANNING_MAKE_BUY_CODE,-1) = NVL(:P_MAKE_BUY,NVL(MSI2.PLANNING_MAKE_BUY_CODE,-1)) AND WRO.DATE_REQUIRED < NVL(:P_REQ_END_DATE +1,WRO.DATE_REQUIRED+1) AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND WRO.ORGANIZATION_ID = :P_ORGANIZATION_ID &C_Limit_Bulk &C_Limit_Vendor AND WRO.WIP_SUPPLY_TYPE <> 6 AND WRO.REQUIRED_QUANTITY > 0 AND WRO.REQUIRED_QUANTITY > WRO.QUANTITY_ISSUED 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 ML.LOOKUP_CODE(+)= MSI2.PLANNING_MAKE_BUY_CODE AND ML.LOOKUP_TYPE(+) = 'MTL_PLANNING_MAKE_BUY' 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 AND NVL(MSS.DISABLE_DATE(+),SYSDATE + 1) >= SYSDATE AND EMP.PERSON_ID(+) = MSI2.BUYER_ID AND NVL(EMP.employee_number(+),EMP.npw_number (+)) IS NOT NULL AND trunc(sysdate) between EMP.effective_start_date (+) and EMP.effective_end_date (+) AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',EMP.PERSON_ID (+), EMP.PERSON_TYPE_ID (+), NVL(EMP.EMPLOYEE_NUMBER (+), EMP.NPW_NUMBER (+)),EMP.APPLICANT_NUMBER (+))) = 'TRUE' AND decode(hr_general.get_xbg_profile,'Y', EMP.business_group_id (+),hr_general.get_business_group_id) = EMP.business_group_id (+) /* &P_ITEM_ORG &C_Limit_Components &P_FLEXWHERE */ GROUP BY &C_First_Sort, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy1', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') , WRO.INVENTORY_ITEM_ID , WE.WIP_ENTITY_NAME , WRO.DATE_REQUIRED , BOD.DEPARTMENT_CODE , WRO.SUPPLY_SUBINVENTORY , &P_SUPPLY_LOC , DECODE(:P_SORT_BY, 11,WE.DESCRIPTION,'X') , NVL(WDJ.PRIMARY_ITEM_ID,-1) , MSI2.DESCRIPTION , DECODE(:P_SORT_BY,9,MSI2.PLANNER_CODE,13,MSI2.PLANNER_CODE,'X') , DECODE(:P_SORT_BY,9,ML.MEANING,13,ML.MEANING,'X') , DECODE(:P_SORT_BY,9,EMP.FULL_NAME ,13,EMP.FULL_NAME ,'X') , DECODE(:P_SORT_BY, 11,WDJ.SCHEDULED_START_DATE, SYSDATE) , DECODE(:P_SORT_BY, 11,WDJ.START_QUANTITY , -1) , DECODE(:P_SORT_BY, 11,WDJ.SCHEDULED_COMPLETION_DATE , SYSDATE) , DECODE(:P_SORT_BY,11,MSITL.DESCRIPTION ,'X') , NVL(MIL.INVENTORY_LOCATION_ID,-1) , MSI2.PRIMARY_UOM_CODE , MOQ2.SUBINVENTORY_CODE , MOQ2.LOCATOR_ID , MOQ2.REVISION , MOQ2.QUANTITY , DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,'-1'), DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0) , MSS.AVAILABILITY_TYPE , SG.SCHEDULE_GROUP_NAME , WDJ.BUILD_SEQUENCE , WIP_WIPDJORQ_XMLP_PKG.master_break_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 ), :C_Component_Disp, :C_Component_Sort, WE.WIP_ENTITY_NAME, BOD.DEPARTMENT_CODE, DECODE ( :P_SORT_BY , 9 , ML.MEANING , 13 , ML.MEANING , 'X' ), DECODE ( :P_SORT_BY , 9 , EMP.FULL_NAME , 13 , EMP.FULL_NAME , 'X' ), DECODE ( :P_SORT_BY , 9 , MSI2.PLANNER_CODE , 13 , MSI2.PLANNER_CODE , 'X' ), WRO.DATE_REQUIRED), WIP_WIPDJORQ_XMLP_PKG.c_break_jobformula(WE.WIP_ENTITY_NAME) , WIP_WIPDJORQ_XMLP_PKG.c_break_comp_descformula(MSI2.DESCRIPTION), WIP_WIPDJORQ_XMLP_PKG.c_break_date_requiredformula(WRO.DATE_REQUIRED), WIP_WIPDJORQ_XMLP_PKG.c_break_departmentformula(BOD.DEPARTMENT_CODE), WIP_WIPDJORQ_XMLP_PKG.c_break_comp_dispformula(( 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 ), :C_Component_Disp) , WIP_WIPDJORQ_XMLP_PKG.c_break_assy_dispformula(( MSI.SEGMENT1 || '\n' || MSI.SEGMENT2 || '\n' || MSI.SEGMENT3 || '\n' || MSI.SEGMENT4 || '\n' || MSI.SEGMENT5 || '\n' || MSI.SEGMENT6 || '\n' || MSI.SEGMENT7 || '\n' || MSI.SEGMENT8 || '\n' || MSI.SEGMENT9 || '\n' || MSI.SEGMENT10 || '\n' || MSI.SEGMENT11 || '\n' || MSI.SEGMENT12 || '\n' || MSI.SEGMENT13 || '\n' || MSI.SEGMENT14 || '\n' || MSI.SEGMENT15 || '\n' || MSI.SEGMENT16 || '\n' || MSI.SEGMENT17 || '\n' || MSI.SEGMENT18 || '\n' || MSI.SEGMENT19 || '\n' || MSI.SEGMENT20 ), :C_ASSY_DISP) , WIP_WIPDJORQ_XMLP_PKG.c_tot_qty_shortformula(:C_TOTAL_AVAIL_QTY, :C_TOTAL_AVAIL_NET_QTY, :C_TOT_QTY_OPEN) , fnd_flex_xml_publisher_apis.process_kff_combination_1('C_Component_Sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') , fnd_flex_xml_publisher_apis |