ECC Discrete Manufacturing, Work Order Delays

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 Enterprise Command Center
Description: This data set holds the delay information for the work order details
Dataset Key: wip-wodelaydetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
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
                                         ,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