ECC Discrete Manufacturing, Work Orders

select
x.*
from
(
 SELECT * FROM (  WITH lookup_details as (SELECT /*+ materialize */ meaning,
                                            lookup_code,
                                            language,
                                            lookup_type,
                                            security_group_id,
                                            view_application_id
                                          FROM fnd_lookup_values
                                          WHERE lookup_type      IN ('WIP_VIEW_QUALITY_NC','WIP_VIEW_ITEM','WIP_ECC_WEEK_BINS','WIP_JOB_STATUS','WIP_DISCRETE_JOB','WIP_SUPPLY','WIP_TOLERANCE_TYPE','SYS_YES_NO','WIP_PURGE_REPORT_TYPE')
                                          AND view_application_id = 700
                                          AND security_group_id   = 0
                                          ), wdj AS
                                         (SELECT /*+ materialize use_nl(mp haout) */ wdj.*,
                                           mp.organization_code ,
                                           haout.NAME ,
                                           we.wip_entity_name job_name,
                                           we.description job_description,
                                           haout.language,wdj.rowid row_id
                                         FROM wip_entities we,
                                           mtl_parameters mp,
                                           hr_all_organization_units_tl haout,
                                           wip_discrete_jobs wdj
                                         WHERE we.wip_entity_id                                      = wdj.wip_entity_id
                                         AND we.entity_type                                         IN (1,3)
                                         AND mp.organization_id                                      = we.organization_id
                                         AND haout.organization_id                                   = mp.organization_id
                                         AND wdj.status_type                                                  IN (1,3,4,5,6,7,12,14,15)
                                         ) SELECT ecc_spec_id ,
                                      organization_id ,
                                      organization_code ,
                                      organization_name ,
                                      organization_currency ,
                                      loaded_date ,
                                      job ,
                                      job_name ,
                                      job_description ,
                                      assembly ,
                                      assembly_uom ,
                                      assembly_description ,
                                      planner,
                                      job_status ,
                                      status_code,
                                      language ,
                                      job_status_type ,
                                      job_start_quantity ,
                                      scrap_quantity ,
                                      completed_quantity ,
                                      release_date ,
                                      actual_completion_date ,
                                      actual_completion_date actual_completion_date_t ,
                                      job_creation_date ,
                                      open_quantity ,
                                      Reject_quantity,
                                      job_type ,
                                      job_class ,
                                      net_quantity ,
                                      close_date ,
                                      build_sequence ,
                                      expedited_flag ,
                                      base_assembly ,
                                      assembly_fixed_lead_time ,
                                      assembly_var_lead_time ,
                                      assembly_total_lead_time ,
                                      repair_order_flag ,
                                      repair_order,
                                      repair_line_id ,
                                      repair_number ,
                                      job_assembly_description ,
                                      schedule_group_name ,
                                      started_today_flag ,
                                      started_late_flag ,
                                      release_delay_flag ,
                                      completed_today_flag ,
                                      completed_yesterday_flag ,
                                      starting_tomorrow_flag ,
                                      completing_tomorrow_flag ,
                                      on_hold_flag ,
                                      started_today,
                                      started_late ,
                                      release_delay ,
                                      completed_today ,
                                      completed_yesterday ,
                                      starting_tomorrow ,
                                      completing_tomorrow ,
                                      on_hold ,
                                      job_start_delay ,
                                      job_finish_delay ,
                                      operation_delay_duration ,
                                      pending_material_issue,
                                      scheduled_start_date ,
                                      scheduled_completion_date ,
                                      scheduled_start_date scheduled_start_date_t ,
                                      scheduled_completion_date scheduled_completion_date_t ,
                                      bill_reference ,
                                      alternate_bill ,
                                      supply_type ,
                                      bill_revision ,
                                      bill_revision_date ,
                                      bill_description,
                                      routing_description,
                                      routing_reference ,
                                      routing_revision ,
                                      routing_revision_date ,
                                      alternate_routing,
                                      requested_due_date ,
                                      completion_subinventory ,
                                      serialization_start_operation ,
                                      completion_locator ,
                                      project_name ,
                                      project_number ,
                                      unit_number ,
                                      task_name ,
                                      task_number ,
                                      wo_demand_class ,
                                      lot_number ,
                                      Kanban,
                                      scheduleing_priority ,
                                      sales_order ,
                                      customer ,
                                      scheduled_ship_date ,
                                      actual_ship_date ,
                                      sales_order_line ,
                                      sales_order_id ,
                                      ship_date_approach_risk_flag,
                                      ship_date_approach_risk_days,
                                      ship_date_passed_flag,
                                      ship_date_passed_days,
                                      job_note,
                                      demand_class ,
                                      sales_order_line_uom ,
                                      primary_quantity_reserved ,
                                      quantity_reserved ,
                                      sales_order_line_status ,
                                      delayed_sales_order ,
                                      sales_order_overdue ,
                                      assembly_category ,
                                      production_line ,
                                      operation_shop_floor_status ,
                                      start_delay_flag,
                                      finish_delay_flag ,
                                      Start_delay ,
                                      Completion_delay finish_delay,
                                      operation_delay_flag ,
                                      operation_delay,
                                      ontrack_flag,
                                      ontrack,
                                      delayed,
                                      delayed_flag,
                                      inprogress,
                                      start_delay_duration,
                                      case when start_delay_duration <> 0 then round(start_delay_duration/24,6)
                                      else 0
                                      end start_delay_duration_days,
                                      case when start_delay_duration <> 0 then round(start_delay_duration*60,6)
                                      else 0
                                      end start_delay_duration_min,
                                      Completion_delay_duration,
                                     case
                                       when completion_delay_duration <> 0 then round(completion_delay_duration/24,6)
                                      else 0
                                     end completion_delay_duration_days,
                                     CASE
                                      WHEN completion_delay_duration <> 0 then round(completion_delay_duration*60,6)
                                       ELSE 0
                                      END completion_delay_duration_min,
                                      release_delay_duation release_delay_duration,
                                      cal_rework_quantity rework_quantity,
                                      osp_operation,
                                      osp_operation_flag,
                                      link,
                                      item_link,
                                     'fwkhp_sswafunc.gif' as wo_qa_link,
                                     'fwkhp_sswafunc.gif' as assembly_qa_link,
                                     'fwkhp_sswafunc.gif' as project_link,
                                      production_line_tbl,
                                      schedule_group_name_tbl,
                                      project_name_tbl,
                                      task_name_tbl,
                                      customer_tbl,
                                      CASE WHEN pending_material_issue =1 THEN 'YES' ELSE 'NO' END unissued,
                                      CASE WHEN delayed_flag =1 THEN 'YES' ELSE 'NO' END delayed_code,
                                      CASE WHEN job_status_type = 1 THEN 'UNRELEASED'
                                           WHEN job_status_type = 3 THEN 'RELEASED'
                                           WHEN job_status_type = 4 THEN 'COMPLETE'
                                           WHEN job_status_type = 5 THEN 'COMPLETE - NO CHARGES ALLOWED'
                                           WHEN job_status_type = 6 THEN 'HOLD'
                                           WHEN job_status_type = 7 THEN 'CANCELED'
                                           WHEN job_status_type = 12 THEN 'CLOSED'
                                           WHEN job_status_type = 14 THEN 'PENDING CLOSE'
                                           WHEN job_status_type = 15 THEN 'FAILED CLOSE'
                                      END job_status_code,
                                      CASE WHEN inprogress =1 THEN 'YES' ELSE 'NO' END open,
                                      CASE
                                        WHEN release_date IS NULL
                                        THEN NULL
                                        ELSE
                                          CASE
                                            WHEN (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity) > cal_completed_quantity OR cal_completed_quantity = 0
                                            THEN 0
                                            WHEN ((cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))/cal_completed_quantity) > 1
                                            THEN 100
                                            ELSE 100 * ((cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))/cal_completed_quantity)
                                          END
                                      END yield,
                                     cal_completed_quantity ,   dfv.*,
                                     CASE WHEN release_date IS NULL
                                        THEN NULL
                                        ELSE
                                          CASE
                                            WHEN (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity) > cal_completed_quantity OR cal_completed_quantity = 0
                                            THEN 0
                                            ELSE  (cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))
                                          END
                                      END tot_scrap_qty
                                    FROM
                                      (SELECT src.*,
                                        CASE WHEN osp_operation_cnt > 0 THEN yes_flag ELSE no_flag END OSP_operation,
                                        CASE WHEN osp_operation_cnt > 0 THEN 1 ELSE 2 END OSP_operation_flag,
                                        CASE
                                          WHEN started_today_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END started_today,
                                        CASE
                                          WHEN started_late_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END started_late ,
                                        CASE
                                          WHEN release_delay_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END release_delay ,
                                        CASE
                                          WHEN completed_today_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END completed_today ,
                                        CASE
                                          WHEN completed_yesterday_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END completed_yesterday ,
                                        CASE
                                          WHEN starting_tomorrow_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END starting_tomorrow ,
                                        CASE
                                          WHEN completing_tomorrow_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END completing_tomorrow ,
                                        CASE
                                          WHEN on_hold_flag = 1
                                          THEN yes_flag
                                          ELSE no_flag
                                        END on_hold ,
                                      CASE WHEN job_status_type IN (1) THEN job_start_delay ELSE 0 END start_delay_duration,
                                      CASE WHEN job_status_type IN (3,6) THEN job_finish_delay ELSE 0 END Completion_delay_duration,
                                        CASE
                                          WHEN job_start_delay > 0 AND job_status_type IN (1)
                                          THEN 1
                                          ELSE 0
                                        END start_delay_flag,
                                        CASE
                                          WHEN job_finish_delay > 0 AND job_status_type IN (3,6)
                                          THEN 1
                                          ELSE 0
                                        END finish_delay_flag ,
                                        CASE
                                          WHEN job_start_delay > 0 AND job_status_type IN (1)
                                          THEN yes_flag
                                          ELSE no_flag
                                        END Start_delay ,
                                        CASE
                                          WHEN job_finish_delay > 0 AND job_status_type IN (3,6)
                                          THEN yes_flag
                                          ELSE no_flag
                                        END Completion_delay ,
                                        CASE
                                          WHEN operation_delay_duration > 0 AND job_status_type IN (3,6)
                                          THEN 1
                                          ELSE 0
                                        END operation_delay_flag ,
                                        CASE
                                          WHEN operation_delay_duration > 0 AND job_status_type IN (3,6)
                                          THEN yes_flag
                                          ELSE no_flag
                                        END operation_delay,
                                        CASE WHEN job_status_type IN (3,6) AND
                                          ( job_start_delay        > 0
                                          OR job_finish_delay         > 0
                                          OR operation_delay_duration > 0)
                                          THEN 0
                                        WHEN job_status_type NOT IN (3,6)
                                          THEN 0
                                          ELSE 1
                                        END ontrack_flag,
                                        CASE WHEN job_status_type IN (3,6) AND
                                          ( job_start_delay        > 0
                                          OR job_finish_delay         > 0
                                          OR operation_delay_duration > 0)
                                          THEN no_flag
                                        WHEN job_status_type NOT IN (3,6)
                                          THEN no_flag
                                          ELSE yes_flag
                                        END ontrack,
                                        CASE
                                          WHEN (job_start_delay        > 0 AND job_status_type IN (1))
                                           OR  (job_finish_delay         > 0 AND job_status_type IN (3,6))
                                           OR  (operation_delay_duration > 0 AND job_status_type IN (3,6))
                                          THEN yes_flag
                                          ELSE no_flag
                                        END delayed,
                                        CASE
                                          WHEN (job_start_delay        > 0 AND job_status_type IN (1))
                                           OR (job_finish_delay         > 0 AND job_status_type IN (3,6))
                                           OR (operation_delay_duration > 0 AND job_status_type IN (3,6))
                                          THEN 1
                                          ELSE 0
                                        END delayed_flag,
                                        CASE
                                          WHEN job_status_type IN (3,6)
                                          THEN 1
                                          ELSE 0
                                        END inprogress,
                                        CASE
                                          WHEN NVL(TO_NUMBER(SUBSTR(rework_quantity,1,INSTR(rework_quantity,'-A')-1)),0) > job_start_quantity
                                          THEN job_start_quantity
                                        ELSE  NVL(TO_NUMBER(SUBSTR(rework_quantity,1,INSTR(rework_quantity,'-A')-1)),0) END cal_rework_quantity,
                                        NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-A') +2,INSTR(rework_quantity,'-B',-1)-INSTR(rework_quantity,'-A')-2)),0)cal_scrap_quantity,
                                        NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-B') +2,INSTR(rework_quantity,'-C',-1)-INSTR(rework_quantity,'-B')-2)),0)cal_rejected_quantity,
                                       /* -- NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-C') +2,LENGTH(rework_quantity))),0)cal_completed_quantity */
                                     completed_quantity cal_completed_quantity
                                      FROM
                                        (SELECT
 
                                          TO_CHAR (wdj.wip_entity_id
                                          ||'-'
                                          ||wdj.organization_id
                                          ||'-'
                                          ||mr.supply_source_header_id
                                          ||'-'
                                          ||ool1.line_id
                                          ||'-JOB') ecc_spec_id,
                                          wdj.organization_id organization_id , wdj.row_id,
                                          yeslk.meaning yes_flag,
                                          nolk.meaning no_flag,
                                          wdj.organization_code organization_code ,
                                          wdj.NAME organization_name ,
                                          WIP_ECC_UTIL_PVT.get_currency_code(wdj.organization_id) organization_currency ,
                                          SYSDATE loaded_date ,
                                          wdj.wip_entity_id job ,
                                          (SELECT SUM(CASE
                                                          WHEN autocharge_type IN(3,4)
                                                          THEN 1
                                                          ELSE 0
                                                        END)
                                                     FROM wip_operation_resources wor
                                                     WHERE wdj.wip_entity_id   = wor.wip_entity_id
                                                     AND wdj.organization_id   = wor.organization_id
                                                   )  osp_operation_cnt,
                                          wdj.job_name job_name ,
                                          wdj.job_description job_description ,
                                          WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly ,
                                          WIP_ECC_UTIL_PVT.get_primary_uom_code(wdj.organization_id,wdj.primary_item_id) assembly_uom ,
                                          WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id)  assembly_description ,
                                          WIP_ECC_UTIL_PVT.get_planner(wdj.organization_id,wdj.primary_item_id)  Planner,
                                          status_lkp.meaning job_status,
                                          status_lkp.lookup_code status_code,
                                          status_lkp.language language,
                                         'fwkhp_sswafunc.gif' link , /* view_quality_lkp.meaning link,*/
                                          'fwkhp_sswafunc.gif'  item_link , /*view_item_lkp.meaning item_link,*/
                                          wdj.status_type job_status_type ,
                                          wdj.start_quantity job_start_quantity ,
                                          wdj.quantity_scrapped scrap_quantity ,
                                          wdj.quantity_completed completed_quantity ,
                                          wdj.date_released release_date ,
                                          wdj.date_completed actual_completion_date ,
                                          wdj.creation_date job_creation_date ,
                                          (wdj.start_quantity - NVL(wdj.quantity_completed,0) ) open_quantity ,
                                          (SELECT SUM(quantity_rejected) FROM wip_operations  wo
                                                 WHERE wo.wip_entity_id = wdj.wip_entity_id) Reject_quantity,
                                          type_lkp.meaning job_type ,
                                          wdj.class_code job_class ,
                                          wdj.net_quantity net_quantity ,
                                          wdj.date_closed close_date ,
                                          wdj.build_sequence build_sequence ,
                                          DECODE(wdj.expedited,'Y',yeslk.meaning,nolk.meaning) expedited_flag,
                                          (SELECT msi6.concatenated_segments
                                          FROM mtl_system_items_vl msi6
                                          WHERE msi6.inventory_item_id = WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.primary_item_id)
                                          AND msi6.organization_id     = wdj.organization_id
                                          ) base_assembly,
                                          DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.primary_item_id) ,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.routing_reference_id)) assembly_fixed_lead_time,
                                          DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.routing_reference_id)) assembly_var_lead_time,
                                          NVL(DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.routing_reference_id)),0) + (NVL(DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.routing_reference_id)),0)*wdj.start_quantity) assembly_total_lead_time,
                                          DECODE(wdj.wip_entity_id,xref.wip_entity_id,1,2) repair_order_flag ,
                                          DECODE(wdj.wip_entity_id,xref.wip_entity_id,yeslk.meaning,nolk.meaning) repair_order,
                                          xref.repair_line_id repair_line_id ,
                                          rep.repair_number repair_number,
                                          WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) job_assembly_description ,
                                          NVL(
                                          (SELECT schedule_group_name
                                          FROM wip_schedule_groups
                                          WHERE schedule_group_id= wdj.schedule_group_id
                                          ) ,nonelk.meaning ) schedule_group_name ,
 
                                          (SELECT schedule_group_name
                                          FROM wip_schedule_groups
                                          WHERE schedule_group_id= wdj.schedule_group_id
                                          )  schedule_group_name_tbl ,
                                          (SELECT MAX(SUM(
                                            CASE
                                              WHEN wmt.fm_operation_seq_num        = wmt.to_operation_seq_num
                                              AND wmt.to_intraoperation_step_type  < wmt.fm_intraoperation_step_type
                                              AND wmt.to_intraoperation_step_type IN (1,2)
                                              THEN wmt.primary_quantity
                                              WHEN wmt.fm_operation_seq_num <> wmt.to_operation_seq_num
                                              AND wmt.to_operation_seq_num   < wmt.fm_operation_seq_num
                                              THEN
                                                CASE
                                                  WHEN wo.operation_seq_num            = wmt.to_operation_seq_num
                                                  AND wmt.to_intraoperation_step_type IN (1,2)
                                                  THEN wmt.primary_quantity
                                                  WHEN wo.operation_seq_num            = wmt.fm_operation_seq_num
                                                  AND wmt.fm_intraoperation_step_type <> 1
                                                  THEN wmt.primary_quantity
                                                  WHEN wo.operation_seq_num <> wmt.fm_operation_seq_num
                                                  OR wo.operation_seq_num   <> wmt.to_operation_seq_num
                                                  THEN wmt.primary_quantity
                                                  ELSE 0
                                                END
                                              ELSE 0
                                            END))
                                            ||'-A'
                                            ||SUM(MAX(wo.quantity_scrapped))
                                            ||'-B'
                                            || SUM(MAX(wo.quantity_rejected))
                                            ||'-C'
                                            || MAX(MAX(wo.quantity_completed))
                                          FROM wip_move_transactions WMT,
                                            Wip_Operations Wo
                                          WHERE 1                =1
                                          AND wo.organization_id = wmt.organization_id
                                          AND wo.wip_entity_id   = wmt.wip_entity_id
                                          AND ((wo.operation_seq_num BETWEEN wmt.fm_operation_seq_num AND wmt.to_operation_seq_num)
                                          OR ( wo.operation_seq_num BETWEEN wmt.to_operation_seq_num AND wmt.fm_operation_seq_num) )
                                          AND wo.wip_entity_id = wdj.wip_entity_id
                                          GROUP BY wo.wip_entity_id,
                                            wo.organization_id,
                                            wo.operation_seq_num
                                          ) rework_quantity,
                                          (SELECT SUM(wo.quantity_rejected)
                                          FROM wip_operations wo
                                          WHERE wo.wip_entity_id = wdj.wip_entity_id
                                          ) cumulative_reject,
                                          CASE
                                            WHEN NVL(TRUNC(wdj.actual_start_date),TRUNC(wdj.date_released)) = TRUNC(SYSDATE)
                                            AND wdj.status_type                                            <> 1
                                            THEN 1
                                            ELSE 0
                                          END started_today_flag,
                                          CASE
                                            WHEN wdj.scheduled_start_date                     < SYSDATE
                                            AND (NVL(wdj.actual_start_date,wdj.date_released) > wdj.scheduled_start_date)
                                            THEN 1
                                            ELSE 0
                                          END started_late_flag,
                                          CASE
                                            WHEN wdj.status_type  = 3
                                            AND wdj.date_released > wdj.scheduled_start_date
                                            THEN 1
                                            ELSE 0
                                          END release_delay_flag,
                                          CASE
                                            WHEN TRUNC(wdj.date_completed) = TRUNC(SYSDATE)
                                            THEN 1
                                            ELSE 0
                                          END completed_today_flag,
                                          CASE
                                            WHEN TRUNC(wdj.date_completed) = TRUNC(SYSDATE-1)
                                            THEN 1
                                            ELSE 0
                                          END completed_yesterday_flag,
                                          CASE
                                            WHEN TRUNC(wdj.scheduled_start_date) = TRUNC(SYSDATE+1)
                                            THEN 1
                                            ELSE 0
                                          END starting_tomorrow_flag,
                                          CASE
                                            WHEN TRUNC(wdj.scheduled_completion_date) = TRUNC(SYSDATE+1)
                                            THEN 1
                                            ELSE 0
                                          END completing_tomorrow_flag,
                                          CASE
                                            WHEN wdj.status_type = 6
                                            THEN 1
                                            ELSE 0
                                          END on_hold_flag,       CASE
                                            WHEN wdj.status_type         IN( 1,3)
                                            AND wdj.scheduled_start_date  < SYSDATE
                                            THEN CASE WHEN (SYSDATE - (wdj.scheduled_start_date )) > 0
                                                      THEN (SYSDATE - (wdj.scheduled_start_date ))* 24  ELSE 0 END
                                          END   job_start_delay,
                                          CASE
                                            WHEN wdj.date_completed IS NOT NULL
                                            AND wdj.date_completed   > wdj.scheduled_completion_date
                                            THEN CASE WHEN (wdj.date_completed - (wdj.scheduled_completion_date ))  > 0
                                                      THEN (wdj.date_completed - (wdj.scheduled_completion_date ))* 24 ELSE 0 END
                                          END job_finish_delay,
                                          CASE
                                            WHEN wdj.status_type  = 3
                                            AND wdj.date_released > wdj.scheduled_start_date
                                            THEN CASE WHEN (wdj.date_released - (wdj.scheduled_start_date)) > 0
                                                      THEN (wdj.date_released - (wdj.scheduled_start_date))* 24 ELSE 0 END
                                          END release_delay_duation,
                                          NVL(
                                          (SELECT MAX(
                                            CASE
                                              WHEN wo.actual_completion_date IS NOT NULL
                                              AND wo.actual_completion_date   > (wo.last_unit_completion_date )
                                              THEN CASE WHEN (wo.actual_completion_date - (wo.last_unit_completion_date )) > 0
                                                        THEN (wo.actual_completion_date - (wo.last_unit_completion_date ))* 24 ELSE 0 END
                                              WHEN wo.actual_completion_date    IS NULL
                                              AND (wo.last_unit_completion_date ) < sysdate
                                              AND (wo.quantity_in_queue          > 0
                                              OR wo.quantity_running             > 0)
                                              THEN CASE WHEN (sysdate- (wo.last_unit_completion_date )) > 0
                                                        THEN (sysdate- (wo.last_unit_completion_date ))* 24 ELSE 0 END
                                              WHEN wo.actual_start_date IS NOT NULL
                                              AND wo.actual_start_date   > (wo.first_unit_start_date )
                                              THEN CASE WHEN (wo.actual_start_date-(wo.first_unit_start_date )) > 0
                                                        THEN (wo.actual_start_date-(wo.first_unit_start_date ))* 24 ELSE 0 END
                                              WHEN wo.actual_start_date       IS NULL
                                              AND (wo.first_unit_start_date )   < sysdate
                                              AND NVL(wo.quantity_in_queue,0)  = 0
                                              AND NVL(wo.quantity_running,0)   = 0
                                              AND NVL(wo.quantity_completed,0) = 0
                                              THEN CASE WHEN (sysdate-(wo.first_unit_start_date )) > 0
                                                        THEN (sysdate-(wo.first_unit_start_date ))* 24 ELSE 0 END
                                              ELSE 0
                                            END)
                                          FROM wip_operations wo
                                          WHERE wo.organization_id                                                        = wdj.organization_id
                                          AND wo.wip_entity_id                                                            = wdj.wip_entity_id
                                          AND (( wo.quantity_in_queue                                                     > 0
                                          OR wo.quantity_running                                                          > 0 )
                                          OR ((wdj.start_quantity - wo.quantity_completed - wo.cumulative_scrap_quantity) > 0 ))
                                          ), 0) operation_delay_duration,
 
                                          (SELECT 1
                                          FROM DUAL
                                          WHERE EXISTS
                                            (SELECT 1
                                            FROM wip_operations wo,
                                              wip_requirement_operations wro
                                            WHERE wo.organization_id         = wdj.organization_id
                                            AND wo.wip_entity_id             = wdj.wip_entity_id
                                            AND wdj.status_type IN (3,4,6)
                                            AND wro.wip_entity_id            = wdj.wip_entity_id
                                            AND wro.quantity_per_assembly >= 0
                                            AND wro.organization_id          = wdj.organization_id
                                            AND wro.operation_seq_num        = wo.operation_seq_num(+)
                                            AND (
                                            (wro.quantity_per_assembly * (DECODE(wro.basis_type,2,1,NVL((wo.quantity_completed ), wdj.quantity_completed))) > wro.quantity_issued  AND wro.wip_supply_type <> 1)
                                             OR
                                            (wro.quantity_per_assembly * (DECODE(wro.basis_type,2,1,NVL((wo.quantity_running + wo.quantity_in_queue ), wdj.quantity_completed))) > wro.quantity_issued
                                              AND wro.wip_supply_type = 1)
 
                                              )
                                            )
                                          )pending_material_issue,
                                          wdj.scheduled_start_date scheduled_start_date ,
                                          wdj.scheduled_completion_date scheduled_completion_date ,
                                          WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.bom_reference_id) bill_reference ,
                                          wdj.alternate_bom_designator alternate_bill ,
                                          bad.description Bill_description,
                                          supply_type_lkp.meaning supply_type ,
                                          wdj.bom_revision bill_revision ,
                                          wdj.bom_revision_date bill_revision_date ,
                                          WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.routing_reference_id) routing_reference ,
                                          wdj.routing_revision routing_revision ,
                                          wdj.alternate_routing_designator alternate_routing,
                                          bad2.description routing_description,
                                          wdj.routing_revision_date routing_revision_date ,
                                          wdj.due_date requested_due_date ,
                                          wdj.completion_subinventory completion_subinventory ,
                                          wdj.serialization_start_op serialization_start_operation,
                                          (SELECT milk.concatenated_segments
                                          FROM mtl_item_locations_kfv milk
                                          WHERE milk.inventory_location_id = wdj.completion_locator_id
                                            and milk.organization_id = wdj.organization_id
                                          ) completion_locator ,
                                          NVL(NVL(ppa.NAME,psn.project_name) ,nonelk.meaning) project_name ,
                                          NVL(ppa.NAME,psn.project_name)  project_name_tbl ,
                                          NVL(ppa.segment1,psn.project_number) project_number ,
                                          wdj.end_item_unit_number unit_number ,
                                          NVL(pat.task_name ,nonelk.meaning ) task_name ,
                                          pat.task_name task_name_tbl ,
                                          pat.task_number task_number ,
                                          wdj.demand_class wo_demand_class ,
                                          wdj.lot_number lot_number ,
                                          (SELECT k.kanban_card_number
                                          FROM mtl_kanban_cards k
                                          WHERE k.kanban_card_id = wdj.kanban_card_id) kanban,
                                          tolerence_lkp.meaning overcompletion_tolerance_type,
                                          wdj.overcompletion_tolerance_value overcompletion_tolerance_value,
                                          wdj.priority scheduleing_priority ,
                                          mso1.concatenated_segments sales_order ,
                                          NVL(hz.party_name,nonelk.meaning ) customer ,
                                          hz.party_name customer_tbl ,
                                          ool1.schedule_ship_date scheduled_ship_date ,
                                          ool1.actual_shipment_date actual_ship_date ,
                                                case when ool1.schedule_ship_date > sysdate then
                                                        case when (((ool1.schedule_ship_date - sysdate) > 0) and ((ool1.schedule_ship_date - sysdate) < 7))
                                                          then yeslk.lookup_code
                                                             else nolk.lookup_code
                                                     end
                                                  else null
                                                end ship_date_approach_risk_flag,
                                                case when ool1.schedule_ship_date > sysdate and ((ool1.schedule_ship_date - sysdate) > 0) and ((ool1.schedule_ship_date - sysdate) < 7) then
                                                case when (((ool1.schedule_ship_date - sysdate) >= 6) and ((ool1.schedule_ship_date - sysdate) < 7))
                                                then '6-7'
                                                when (((ool1.schedule_ship_date - sysdate) >= 5) and ((ool1.schedule_ship_date - sysdate) < 6))
                                                then '5-6'
                                                when (((ool1.schedule_ship_date - sysdate) >= 4) and ((ool1.schedule_ship_date - sysdate) < 5))
                                                then '4-5'
                                                when (((ool1.schedule_ship_date - sysdate) >= 3) and ((ool1.schedule_ship_date - sysdate) < 4))
                                                then '3-4'
                                                when (((ool1.schedule_ship_date - sysdate) >= 2) and ((ool1.schedule_ship_date - sysdate) < 3))
                                                then '2-3'
                                                when (((ool1.schedule_ship_date - sysdate) >= 1) and ((ool1.schedule_ship_date - sysdate) < 2))
                                                then '1-2'
                                                when (((ool1.schedule_ship_date - sysdate) > 0) and ((ool1.schedule_ship_date - sysdate) < 1))
                                                then '0-1'
                                                else
                                                null
                                             end
                                              else
                                               null
                                         end ship_date_approach_risk_days,
                                    case when ((ool1.actual_shipment_date is null) and (ool1.schedule_ship_date < sysdate))
                                       then yeslk.lookup_code
                                      else nolk.lookup_code
                                  end ship_date_passed_flag ,
                                 case when ((ool1.actual_shipment_date is null) and (ool1.schedule_ship_date < sysdate))
                                       then
                                          case when ((sysdate - ool1.schedule_ship_date) >= 7)
                                              then '>7'
                                              when ((sysdate - ool1.schedule_ship_date) >=6 and (sysdate - ool1.schedule_ship_date) < 7)
                                              then '6-7'
                                              when ((sysdate - ool1.schedule_ship_date) >=5 and (sysdate - ool1.schedule_ship_date) < 6)
                                              then '5-6'
                                              when ((sysdate - ool1.schedule_ship_date) >=4 and (sysdate - ool1.schedule_ship_date) < 5)
                                              then '4-5'
                                              when ((sysdate - ool1.schedule_ship_date) >=3 and (sysdate - ool1.schedule_ship_date) < 4)
                                              then '3-4'
                                              when ((sysdate - ool1.schedule_ship_date) >=2 and (sysdate - ool1.schedule_ship_date) < 3)
                                              then '2-3'
                                              when ((sysdate - ool1.schedule_ship_date) >=1 and (sysdate - ool1.schedule_ship_date) < 2)
                                              then '1-2'
                                              when ((sysdate - ool1.schedule_ship_date) > 0 and (sysdate - ool1.schedule_ship_date) < 1)
                                              then '0-1'
                                              else
                                              NULL
                                          end
                                       else
                                      NULL
                                  end ship_date_passed_days,
                                   replace(replace(replace(replace(to_char(substrb(dbms_lob.substr( wdj.job_note, 4000, 1 ),4000)),'<br>',''),'***',''),'<b>',''),'</b>','') job_note,
                                          ool1.line_number sales_order_line ,
                                          ool1.header_id sales_order_id ,
                                          ool1.demand_class_code demand_class ,
                                          mr.reservation_uom_code sales_order_line_uom ,
                                          mr.primary_reservation_quantity primary_quantity_reserved ,
                                          mr.reservation_quantity quantity_reserved ,
                                          DECODE(ool1.line_number,NULL,NULL,DECODE(NVL(ool1.open_flag,'N'),'N',olclosed.meaning, DECODE (ool1.cancelled_flag,'Y',olcanceled.meaning, DECODE (ool1.booked_flag,'Y',olbooked.meaning,olentered.meaning)))) sales_order_line_status ,
                                          DECODE( mr.demand_source_header_id, NULL,0, DECODE(SIGN(ool1.schedule_ship_date - ool1.actual_shipment_date), 1, 0, 1 )) delayed_sales_order,
                                          CASE
                                            WHEN ool1.schedule_ship_date   < SYSDATE AND ool1.flow_status_code <> 'SHIPPED'
                                            AND ool1.actual_shipment_date IS NULL
                                            THEN 1
                                            ELSE 0
                                          END sales_order_overdue,
                                          (SELECT acat.concatenated_segments
                                          FROM mtl_item_catalog_groups_b_kfv acat
                                          WHERE acat.item_catalog_group_id = WIP_ECC_UTIL_PVT.get_item_catalog_group_id(wdj.organization_id,wdj.primary_item_id)
                                          ) assembly_category,
                                          NVL(wl.description ,nonelk.meaning ) production_line ,
                                          wl.description  production_line_tbl ,
                                          (SELECT COUNT(wsfs.shop_floor_status_code)
                                          FROM wip_shop_floor_statuses wsfs,
                                            wip_operations wo
                                          WHERE wo.organization_id = wdj.organization_id
                                          AND wo.wip_entity_id     = wdj.wip_entity_id
                                          AND wo.organization_id   = wsfs.organization_id
                                          AND wo.wip_entity_id     = wsfs.wip_entity_id
                                          AND wo.operation_seq_num = wsfs.operation_seq_num
                                          ) operation_shop_floor_status
                                               FROM wdj wdj,
                                                  csd_repair_job_xref xref,
                                                  csd_repairs rep,
                                                  wip_lines wl,
                                                  mtl_reservations mr,
                                                  oe_order_lines_all ool1,
                                                  mtl_sales_orders_kfv mso1,
                                                  hz_parties hz,
                                                  hz_cust_accounts hzc,
                                                  (SELECT meaning
                                                  FROM oe_lookups
                                                  WHERE lookup_type = 'LINE_FLOW_STATUS'
                                                  AND lookup_code   = 'CLOSED'
                                                  ) olclosed,
                                                  (SELECT meaning
                                                  FROM oe_lookups
                                                  WHERE lookup_type = 'LINE_FLOW_STATUS'
                                                  AND lookup_code   = 'CANCELLED'
                                                  ) olcanceled,
                                                  (SELECT meaning
                                                  FROM oe_lookups
                                                  WHERE lookup_type = 'LINE_FLOW_STATUS'
                                                  AND lookup_code   = 'BOOKED'
                                                  ) olbooked,
                                                  (SELECT meaning
                                                  FROM oe_lookups
                                                  WHERE lookup_type = 'LINE_FLOW_STATUS'
                                                  AND lookup_code   = 'ENTERED'
                                                  ) olentered,
                                                    (SELECT
                                                  ml1.meaning,
                                                  ml1.lookup_code,
                                                  ml1.language
                                                  FROM lookup_details ml1
                                                  WHERE ml1.lookup_type       = 'WIP_ECC_WEEK_BINS'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  AND ml1.lookup_code         = 1
                                                  ) past_week_lkp,
                                                  (SELECT
                                                  ml1.meaning,
                                                  ml1.lookup_code,
                                                  ml1.language
                                                  FROM lookup_details ml1
                                                  WHERE ml1.lookup_type       = 'WIP_ECC_WEEK_BINS'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  AND ml1.lookup_code         = 2
                                                  ) curr_week_lkp,
                                                  (SELECT
                                                  ml1.meaning,
                                                  ml1.lookup_code,
                                                  ml1.language
                                                  FROM lookup_details ml1
                                                  WHERE ml1.lookup_type       = 'WIP_ECC_WEEK_BINS'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  AND ml1.lookup_code         = 3
                                                  ) future_week_lkp,
                                                  (
                                                  SELECT
                                                    ml1.meaning,
                                                    ml1.lookup_code,
                                                    ml1.language
                                                  FROM lookup_details ml1
                                                  WHERE ml1.lookup_type       = 'WIP_JOB_STATUS'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  ) status_lkp,
                                                /*  (
                                                  SELECT
                                                    ml1.meaning,
                                                    ml1.lookup_code,
                                                    ml1.language
                                                  FROM lookup_details ml1
                                                  WHERE ml1.lookup_type       = 'WIP_VIEW_QUALITY_NC'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  AND ml1.lookup_code         = 1
                                                  ) view_quality_lkp,   */
                                                /*  (
                                                  SELECT
                                                    ml1.meaning,
                                                    ml1.lookup_code,
                                                    ml1.language
                                                  FROM lookup_details ml1
                                                  WHERE ml1.lookup_type       = 'WIP_VIEW_ITEM'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  AND ml1.lookup_code         = 1
                                                  ) view_item_lkp, */
                                                  (SELECT
                                                    ml4.meaning,
                                                    ml4.lookup_code,
                                                    ml4.language
                                                  FROM lookup_details ml4
                                                  WHERE ml4.lookup_type       = 'WIP_DISCRETE_JOB'
                                                  AND ml4.view_application_id = 700
                                                  AND ml4.security_group_id   = 0
                                                  ) type_lkp,
                                                  (SELECT
                                                    ml5.meaning,
                                                    ml5.lookup_code,
                                                    ml5.language
                                                  FROM lookup_details ml5
                                                  WHERE ml5.lookup_type       = 'WIP_SUPPLY'
                                                  AND ml5.view_application_id = 700
                                                  AND ml5.security_group_id   = 0
                                                  ) supply_type_lkp,
                                                  (SELECT  octlk.meaning, octlk.language,octlk.lookup_code
                                                   FROM lookup_details octlk
                                                   WHERE octlk.lookup_type = 'WIP_TOLERANCE_TYPE'
                                                   AND octlk.view_application_id = 700
                                                   AND octlk.security_group_id = 0)  tolerence_lkp,
                                                  pa_projects_all ppa,
                                                  pjm_seiban_numbers psn,
                                                  pa_tasks pat,
                                                   (SELECT
                                                    yeslk.meaning,
                                                    yeslk.lookup_code,
                                                    yeslk.language
                                                  FROM lookup_details yeslk
                                                  WHERE  yeslk.lookup_type           = 'SYS_YES_NO'
                                                AND yeslk.view_application_id   = 700
                                                AND yeslk.lookup_code           = '1'
                                                AND yeslk.security_group_id     = 0
                                                  )   yeslk,
                                                  (SELECT
                                                    nolk.meaning,
                                                    nolk.lookup_code,
                                                    nolk.language
                                                  FROM lookup_details nolk
                                                  WHERE  nolk.lookup_type           = 'SYS_YES_NO'
                                                AND nolk.view_application_id   = 700
                                                AND nolk.lookup_code           = '2'
                                                AND nolk.security_group_id     = 0
                                                  )    nolk,
                                                 (SELECT
                                                    nolk.meaning,
                                                    nolk.lookup_code,
                                                    nolk.language
                                                  FROM lookup_details nolk
                                                  WHERE  nolk.lookup_type           = 'WIP_PURGE_REPORT_TYPE'
                                                AND nolk.view_application_id   = 700
                                                AND nolk.lookup_code           = '4'
                                                AND nolk.security_group_id     = 0
                                                  )  nonelk,
                                                  bom_alternate_designators_tl bad,
                                                  bom_alternate_designators_tl bad2
                                                WHERE  wdj.alternate_bom_designator=bad.alternate_designator_code(+)
                                                AND wdj.organization_id = bad.organization_id(+)
                                                AND wdj.alternate_routing_designator=bad2.alternate_designator_code(+)
                                                AND wdj.organization_id = bad2.organization_id(+)
                                                AND wdj.line_id                 = wl.line_id(+)
                                                AND mr.demand_source_type_id(+) = 2
                                                AND mr.supply_source_type_id(+) = 5
                                                AND mr.demand_source_line_id    = ool1.line_id(+)
                                                AND mso1.sales_order_id(+)      = mr.demand_source_header_id
                                                AND ool1.sold_to_org_id         = hzc.cust_account_id(+)
                                                AND hz.party_id(+)              = hzc.party_id
                                                AND wdj.organization_id                          = mr.organization_id(+)
                                                AND wdj.wip_entity_id                            = mr.supply_source_header_id(+)
                                                AND wdj.project_id                               = ppa.project_id(+)
                                                AND wdj.project_id                               = psn.project_id(+)
                                                AND wdj.task_id                                  = pat.task_id(+)
                                                AND wdj.status_type                              = status_lkp.lookup_code(+)
                                                AND wdj.job_type                                 = type_lkp.lookup_code(+)
                                                AND wdj.wip_entity_id                            = xref.wip_entity_id(+)
                                                AND rep.repair_line_id(+)                        = xref.repair_line_id
                                                AND wdj.wip_supply_type                          = supply_type_lkp.lookup_code(+)
                                                AND to_char(wdj.overcompletion_tolerance_type)   = tolerence_lkp.lookup_code(+)
                                                AND wdj.language                               = NVL(status_lkp.language,wdj.language)
                                                AND NVL(supply_type_lkp.language,wdj.language) = NVL(status_lkp.language,wdj.language)
                                                AND NVL(type_lkp.language,wdj.language)        = NVL(status_lkp.language,wdj.language)
                                                AND NVL(type_lkp.language,wdj.language)        = NVL(tolerence_lkp.language,wdj.language)
                                            /*  AND NVL(type_lkp.language,wdj.language)        = NVL(view_quality_lkp.language,wdj.language)
                                                AND NVL(type_lkp.language,wdj.language)        = NVL(view_item_lkp.language,wdj.language)   */
                                                AND wdj.language                               = NVL(yeslk.language,wdj.language)
                                                AND wdj.language                               = NVL(nolk.language,wdj.language)
                                                AND wdj.language                               = NVL(nonelk.language,wdj.language)
                                                AND wdj.language                               = NVL(bad.language,wdj.language)
                                                AND wdj.language                               = NVL(bad2.language,wdj.language)
                                                AND wdj.language                               = NVL(future_week_lkp.language,wdj.language)
                                                AND wdj.language                               = NVL(curr_week_lkp.language,wdj.language)
                                                AND wdj.language                               = NVL(past_week_lkp.language,wdj.language)
                                                 AND (NVL(wdj.date_completed,wdj.scheduled_completion_date) >= SYSDATE - 30
                                          OR wdj.status_type  IN (3,6)
                                          OR (wdj.status_type  = 1
 
                                          AND wdj.scheduled_start_date  <= SYSDATE + 7 ))  ) src ) src, (select "ROW_ID" "'WIP_WO_ROW_ID'","CONTEXT" "'WIP_WO_CONTEXT'","CONCATENATED_SEGMENTS" "'WIP_WO_CONCATENATED_SEGMENTS'" from WIP_DISCRETE_JOBS_DFV) dfv
                                            WHERE src.row_id  = dfv."'WIP_WO_ROW_ID'"(+)
                                             AND src.language IN ( 'US' ) ) PIVOT ( MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME,  MAX(JOB_STATUS) AS JOB_STATUS , 
        MAX(JOB_TYPE) AS JOB_TYPE ,MAX(SUPPLY_TYPE) AS SUPPLY_TYPE,MAX(OSP_OPERATION) AS OSP_OPERATION,  MAX(STARTED_TODAY ) AS STARTED_TODAY, 
        MAX(STARTED_LATE ) AS  STARTED_LATE ,MAX(RELEASE_DELAY ) AS   RELEASE_DELAY, MAX(DELAYED) AS DELAYED, MAX(COMPLETED_TODAY ) AS   COMPLETED_TODAY, MAX(BILL_DESCRIPTION) AS BILL_DESCRIPTION, MAX(ROUTING_DESCRIPTION) AS ROUTING_DESCRIPTION, 
        MAX(COMPLETED_YESTERDAY ) AS  COMPLETED_YESTERDAY, MAX(STARTING_TOMORROW ) AS  STARTING_TOMORROW , MAX(EXPEDITED_FLAG) AS EXPEDITED_FLAG, 
        MAX(COMPLETING_TOMORROW ) AS  COMPLETING_TOMORROW, MAX(ON_HOLD ) AS  ON_HOLD , MAX(FINISH_DELAY) AS FINISH_DELAY, 
        MAX(START_DELAY) AS START_DELAY, MAX(OPERATION_DELAY) AS OPERATION_DELAY ,MAX(ONTRACK) AS ONTRACK ,
        MAX(PRODUCTION_LINE) AS PRODUCTION_LINE ,  MAX(PROJECT_NAME) AS PROJECT_NAME , MAX(TASK_NAME) AS TASK_NAME ,
        MAX(CUSTOMER) AS CUSTOMER, MAX(SCHEDULE_GROUP_NAME) AS SCHEDULE_GROUP_NAME ,/*MAX(link) AS link ,MAX(item_link) AS item_link,*/ MAX(REPAIR_ORDER) AS REPAIR_ORDER FOR LANGUAGE IN ('US' "US"))
) x
where
2=2