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
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