ECC Discrete Manufacturing, Work Order Delays

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_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 ,
                                           we.description job_description,
                                           haout.language
                                         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)
                                         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 ) )
                                         ) SELECT STARTED_TODAY_FLAG
                                         ,STARTED_LATE_FLAG
                                         ,RELEASE_DELAY_FLAG
                                         ,COMPLETED_TODAY_FLAG
                                         ,COMPLETED_YESTERDAY_FLAG
                                         ,STARTING_TOMORROW_FLAG
                                         ,COMPLETING_TOMORROW_FLAG
                                         ,ON_HOLD_FLAG
                                         ,JOB_START_DELAY
                                         ,JOB_FINISH_DELAY
                                         ,RELEASE_DELAY_DUATION
                                         ,OPERATION_DELAY_DURATION
                                         ,PENDING_MATERIAL_ISSUE
                                         ,SCHEDULED_START_DATE
                                         ,SCHEDULED_COMPLETION_DATE
                                         ,ALTERNATE_BILL
                                         ,BILL_DESCRIPTION
                                         ,SUPPLY_TYPE
                                         ,BILL_REVISION
                                         ,BILL_REVISION_DATE
                                         ,ROUTING_REFERENCE
                                         ,ROUTING_REVISION
                                         ,ALTERNATE_ROUTING
                                         ,ROUTING_REVISION_DATE
                                         ,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
                                         ,SALES_ORDER
                                         ,CUSTOMER
                                         ,DEMAND_CLASS
                                         ,PRODUCTION_LINE
                                         ,STARTED_TODAY
                                         ,STARTED_LATE
                                         ,RELEASE_DELAY
                                         ,COMPLETED_TODAY
                                         ,COMPLETED_YESTERDAY
                                         ,STARTING_TOMORROW
                                         ,COMPLETING_TOMORROW
                                         ,ON_HOLD
                                         ,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
                                         ,START_DELAY_FLAG
                                         ,FINISH_DELAY_FLAG
                                         ,START_DELAY
                                         ,FINISH_DELAY
                                         ,OPERATION_DELAY_FLAG
                                         ,OPERATION_DELAY
                                         ,ONTRACK_FLAG
                                         ,ONTRACK
                                         ,DELAYED
                                         ,DELAYED_FILTER
                                         ,DELAYED_FLAG
                                         ,INPROGRESS
                                         ,ECC_SPEC_ID
                                         ,ORGANIZATION_ID
                                         ,ORGANIZATION_CODE
                                         ,YES_FLAG
                                         ,NO_FLAG
                                         ,ORGANIZATION_NAME
                                         ,LOADED_DATE
                                         ,JOB
                                         ,JOB_NAME
                                         ,ASSEMBLY
                                         ,JOB_STATUS
                                         ,STATUS_CODE
                                         ,LANGUAGE
                                         ,JOB_STATUS_TYPE
                                         ,RELEASE_DATE
                                         ,ACTUAL_COMPLETION_DATE
                                         ,JOB_CREATION_DATE
                                         ,JOB_TYPE
                                         ,JOB_CLASS
                                         ,CLOSE_DATE
                                         ,BUILD_SEQUENCE
                                         ,SCHEDULE_GROUP_NAME
                                         ,CASE WHEN pending_material_issue =1 THEN 'YES' ELSE 'NO' END UNISSUED
                                        FROM
                                          (SELECT src.*,
                                            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 finish_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 'YES'
                                              ELSE 'NO'
                                            END delayed_filter,
                                            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
                                          FROM
                                            (SELECT
                                              /*+ PUSH_PRED(MSI4) PUSH_PRED(MSI8) */
                                              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.organization_code organization_code ,
                                              yeslk.meaning yes_flag,
                                              nolk.meaning no_flag,
                                              wdj.NAME organization_name ,
                                              SYSDATE loaded_date ,
                                              wdj.wip_entity_id job ,
                                              wdj.wip_entity_name job_name ,
                                              WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly ,
                                              status_lkp.meaning job_status,
                                              status_lkp.lookup_code status_code,
                                              status_lkp.language language,
                                              wdj.status_type job_status_type ,
                                              wdj.date_released release_date ,
                                              wdj.date_completed actual_completion_date ,
                                              wdj.creation_date job_creation_date ,
                                              type_lkp.meaning job_type ,
                                              wdj.class_code job_class ,
                                              wdj.date_closed close_date ,
                                              wdj.build_sequence build_sequence ,
                                              NVL(
                                              (SELECT schedule_group_name
                                              FROM wip_schedule_groups
                                              WHERE schedule_group_id= wdj.schedule_group_id
                                              ) ,nonelk.meaning ) schedule_group_name ,
                                              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 ,
                                              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,
                                              wdj.routing_revision_date routing_revision_date ,
                                              wdj.due_date requested_due_date ,
                                              wdj.completion_subinventory completion_subinventory ,