ECC Discrete Manufacturing, Work Order Delays, SQL1 (1)

Description
Categories: Enterprise Command Center
Columns: 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 ...
Imported from ECC
System: EBS
Dataset Key: wip-wodelaydetails
Description: This data set holds the delay information for the work order details
select * 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
                                         ,COMPLETION_DELAY_DURATION
                                         ,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
                                         ,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.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 ,
                                              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
                                              ) completion_locator ,
                                              NVL(NVL(ppa.NAME,psn.project_name) ,nonelk.meaning) project_name ,
                                              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_number task_number ,
                                              wdj.demand_class wo_demand_class ,
                                              wdj.lot_number lot_number ,
                                              mso1.concatenated_segments sales_order ,
                                              NVL(hz.pa