ECC Project Manufacturing, Work Orders

select
x.*
from
(
select * from ( SELECT  /*+ use_nl(msitl1) use_nl(msitl2) */    'PROJ_WO' || '-' || wdj.project_id || '-' || NVL( TO_CHAR(wdj.task_id), 'NO_TASK' ) || '-' ||  we.wip_entity_id || '-' || wdj.organization_id || '-' || NVL( TO_CHAR(wo.operation_seq_num), 'NO_OP' ) || '-' ||  NVL( TO_CHAR(wro.inventory_item_id), 'NO_COMP' ) || '-' || NVL(to_char(bso.standard_operation_id), 'NO_STDOP') AS ECC_SPEC_ID,  'PROJ_WO' AS RECORD_TYPE,  hou3.language AS LANGUAGE,  GREATEST( NVL( pp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  	,NVL( PT.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  	,NVL( WDJ.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  	,NVL( we.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  	,NVL( wo.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  	,NVL( bso.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  	,NVL( wro.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  	,NVL( mp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) AS ECC_LAST_UPDATE_DATE,  PP.PROJECT_ID AS PROJECT_ID, PP.SEGMENT1 AS PROJECT_NUMBER, PP.ORG_ID AS ORG_ID, PT.TASK_ID AS TASK_ID, PT.TASK_NUMBER AS TASK_NUMBER,  we.wip_entity_id AS job_id, we.wip_entity_name AS job_name, wdj.description AS job_description, wdj.class_code AS job_class,  flv1.meaning AS job_status, hou3.name AS job_organization, msi1.concatenated_segments AS assembly, msitl1.description AS assembly_description,  wdj.end_item_unit_number AS end_item_unit_number, wdj.scheduled_start_date AS job_start_date, wdj.scheduled_completion_date AS job_completion_date,  wdj.start_quantity AS job_start_quantity, msi1.primary_uom_code AS assembly_primary_uom, NVL( wdj.quantity_completed, 0 ) AS job_quantity_completed,  NVL( wdj.start_quantity, 0 ) - NVL( wdj.quantity_completed, 0 ) - NVL( wdj.quantity_scrapped, 0 ) AS job_quantity_remaining,  NVL( wdj.quantity_scrapped, 0 ) AS job_quantity_scrapped, wdj.bom_revision AS JOB_BOM_REVISION, wo.operation_seq_num AS OPERATION_SEQ_NUM,  bso.operation_code AS OPERATION_CODE, bd1.department_code AS operation_department, wo.description AS operation_description,  wo.scheduled_quantity AS op_quantity_scheduled, wo.quantity_in_queue AS op_quantity_in_queue, wo.quantity_running AS op_quantity_running,  wo.quantity_waiting_to_move AS op_quantity_waiting_to_move, wo.quantity_rejected AS op_quantity_rejected, wo.quantity_scrapped AS op_quantity_scrapped,  wo.quantity_completed AS op_quantity_completed, wo.first_unit_start_date AS OP_FIRST_UNIT_START_DATE,  wo.first_unit_completion_date AS OP_FIRST_UNIT_COMPLETION_DATE, wo.last_unit_start_date AS OP_LAST_UNIT_START_DATE,  wo.last_unit_completion_date AS OP_LAST_UNIT_COMPLETION_DATE, wo.previous_operation_seq_num AS OP_PREVIOUS_OPERATION_SEQ_NUM,  wo.next_operation_seq_num AS OP_NEXT_OPERATION_SEQ_NUM, wo.count_point_type AS OP_COUNT_POINT_TYPE,  NVL2(wo.count_point_type, DECODE( wo.count_point_type, 1, 'Yes', 'No' ) , NULL) AS OP_count_point,  NVL2(wo.count_point_type, DECODE( wo.count_point_type, 3, 'No', 'Yes' ) , NULL) AS OP_autocharge,  DECODE( wo.backflush_flag, 1, 'Yes', 2, 'No' ) AS OP_backflush, wo.minimum_transfer_quantity AS OP_MINIMUM_TRANSFER_QUANTITY,  wo.date_last_moved AS OP_DATE_LAST_MOVED, wro.inventory_item_id AS comp_item_id,  msi2.concatenated_segments AS comp_item_number, msitl2.description AS comp_item_description,  msi2.primary_uom_code AS comp_primary_uom, bd2.department_code AS comp_department, flv2.meaning AS COMP_supply_type,  wro.required_quantity AS comp_quantity_required,  DECODE( wro.quantity_issued, 0, NULL , wro.quantity_issued ) AS comp_quantity_issued,  DECODE( (wro.required_quantity - wro.quantity_issued), 0, NULL ,   DECODE( SIGN( wro.required_quantity ) , -1 * SIGN( wro.quantity_issued ) , (wro.required_quantity - wro.quantity_issued),    DECODE( SIGN( ABS( wro.required_quantity ) - ABS( wro.quantity_issued ) ) , -1, NULL , (wro.required_quantity - wro.quantity_issued) ) ) ) AS comp_quantity_open,  wro.quantity_per_assembly AS COMP_QUANTITY_PER_ASSEMBLY, wro.comments AS COMP_COMMENTS, wro.supply_subinventory AS COMP_SUPPLY_SUBINVENTORY,  DECODE( mp.project_reference_enabled, NULL , mil.concatenated_segments, 2, mil.concatenated_segments, 1, inv_project.get_pjm_locsegs(mil.concatenated_segments) ) AS COMP_supply_locator,  pjm_inquiry.component_serial(wro.organization_id, wro.wip_entity_id, wro.operation_seq_num, wro.inventory_item_id) AS component_serial  FROM PA_PROJECTS_ALL PP, PA_TASKS PT, wip_discrete_jobs wdj, wip_entities we, fnd_lookup_values flv1, hr_all_organization_units_tl hou3,  mtl_system_items_b_KFV msi1, mtl_system_items_tl msitl1, wip_operations wo, bom_standard_operations bso, bom_departments bd1, wip_requirement_operations wro,  mtl_system_items_b_kfv msi2, mtl_system_items_tl msitl2, bom_departments bd2, fnd_lookup_values flv2, mtl_parameters mp, mtl_item_locations_kfv mil,  ( SELECT DISTINCT project_id FROM pjm_project_parameters ) p3     WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date) AND pp.project_type <> 'AWARD_PROJECT'  AND pp.template_flag = 'N'  AND pp.project_id = p3.project_id  AND WDJ.PROJECT_ID = PP.PROJECT_ID  AND PT.TASK_ID(+) = WDJ.TASK_ID  AND we.wip_entity_id = wdj.wip_entity_id  AND flv1.lookup_type = 'WIP_JOB_STATUS'  AND flv1.lookup_code = wdj.status_type  AND flv1.language = hou3.language  AND hou3.organization_id = wdj.organization_id  AND msi1.inventory_item_id(+) = wdj.primary_item_id  AND msi1.organization_id(+) = wdj.organization_id  AND msitl1.inventory_item_id(+) = msi1.inventory_item_id  AND msitl1.organization_id(+) = msi1.organization_id  AND nvl(msitl1.language,hou3.language) = hou3.language  AND wo.wip_entity_id(+) = wdj.wip_entity_id  AND wo.organization_id(+) = wdj.organization_id  AND bso.standard_operation_id(+) = wo.standard_operation_id  AND bso.organization_id(+) = wo.organization_id  AND bd1.department_id(+) = wo.department_id  AND wro.wip_entity_id(+) = wo.wip_entity_id  AND wro.operation_seq_num(+) = wo.operation_seq_num  AND wro.organization_id(+) = wo.organization_id  AND msi2.inventory_item_id(+) = wro.inventory_item_id  AND msi2.organization_id(+) = wro.organization_id  AND msitl2.inventory_item_id(+) = msi2.inventory_item_id  AND msitl2.organization_id(+) = msi2.organization_id  AND nvl(msitl2.language,hou3.language) = hou3.language   AND bd2.department_id(+) = wro.department_id  AND flv2.lookup_type(+) = 'WIP_SUPPLY'  AND flv2.lookup_code(+) = wro.wip_supply_type  AND nvl(flv2.language,flv1.language) = flv1.language   AND mp.organization_id(+) = wro.organization_id  AND mil.inventory_location_id(+) = wro.supply_locator_id  AND mil.organization_id(+) = wro.organization_id  AND hou3.LANGUAGE IN ('US')  AND flv1.language (+) IN ('US')  AND flv2.language  (+) IN ('US')  AND msitl1.language (+) IN ('US') AND msitl2.language (+) IN ('US') )  PIVOT( MAX(JOB_STATUS) AS JOB_STATUS,  MAX(job_ORGANIZATION) AS job_ORGANIZATION ,  MAX(comp_supply_type) as comp_supply_type,   MAX(assembly_description) as assembly_description,   MAX(comp_item_description) as comp_item_description  FOR LANGUAGE IN ('US' "US"))
) x
where
2=2