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
                                        (