ECC Discrete Manufacturing, Operations

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Row Id, Job, Job Name, Job Scheduled Start Date, Job Scheduled Completion Date, Job Actual Completion Date, Organization Id, Organization Currency, Organization Code ...
Imported from Enterprise Command Center
Description: This data set holds information about work order operations
Dataset Key: wip-opdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
 SELECT * FROM (  SELECT ecc_spec_id ,row_id,
                                      job ,
                                      job_name ,
									  job_scheduled_start_date,
									  job_scheduled_completion_date,
									  job_actual_completion_date,
									  job_status,
                                      organization_id ,
									  organization_currency,
									  organization_code,
									  organization_name,
                                      language,
                                      operation_sequence_number,
                                      department ,
                                      department_description ,
                                      planned_start_date ,
                                      planned_completion_date ,
                                      Actual_start_date ,
                                      Actual_completion_date,
                                      quantity_in_queue ,
                                      quantity_in_run ,
                                      quantity_to_move ,
                                      scrap_quantity ,
                                      rejected_quantity ,
                                      completed_quantity ,
                                      operation_description ,
                                      progress_percentage ,
                                      CASE WHEN osp_operation_flag = 1 THEN yes_flag ELSE no_flag END osp_operation,
									  osp_operation_flag,
									  count_point,
									  autocharge,
									  backflush,
									  clock_in_employee,
									  serialized_link,
									  Job_Description,
                                      operation_progress actual_operation_progress,
                                      CASE WHEN TO_NUMBER(SUBSTR(operation_progress,1,INSTR(operation_progress,'-A') -1)) > 100 THEN 100
									       ELSE NVL(TO_NUMBER(SUBSTR(operation_progress,1,INSTR(operation_progress,'-A') -1)),0)
									  END operation_progress,
                                      TO_NUMBER(SUBSTR(operation_progress,INSTR(operation_progress,'-A') +2,INSTR(operation_progress,'-B',-1)-INSTR(operation_progress,'-A')-2))elapsed_time,
                                      TO_NUMBER(SUBSTR(operation_progress,INSTR(operation_progress,'-B') +2,LENGTH(operation_progress)))lead_time,
                                      operation_delay_duration ,
                                      CASE
                                        WHEN operation_delay_duration > 0 and job_status_type in (3,6)
                                        THEN yes_flag
                                        ELSE no_flag
                                      END Operation_delayed,
                                      CASE
                                        WHEN operation_delay_duration > 0 and job_status_type in (3,6)
                                        THEN 1
                                        ELSE 0
                                      END Operation_delayed_flag,
									  CASE WHEN job_status_type IN (3,6) AND 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 operation_delay_duration > 0
									  THEN no_flag
									  WHEN job_status_type NOT IN (3,6)
									    THEN no_flag
                                        ELSE yes_flag
                                      END ontrack,
                                      operation_status,
                                      op_status_flag,
									  CASE WHEN op_status_flag =1 AND job_status_type = 3
									  THEN 1 ELSE 0 END upcoming,
									  shop_floor_status_code,
                                      Shop_Floor_Status_Step,
                                      Move_status,
                                      Status_Move_Flag Move_status_flag,
                                      Rework_quantity,
                                      CASE WHEN op_status_flag =3 THEN
									   CASE
                                        WHEN completed_quantity = 0
                                        OR completed_quantity  IS NULL
                                        THEN NULL
                                        WHEN (scrap_quantity + rejected_quantity + Rework_quantity) > completed_quantity
                                        THEN 0
                                        WHEN (completed_quantity - (scrap_quantity + rejected_quantity + Rework_quantity))/ completed_quantity > 1
                                        THEN 100
                                        ELSE 100 * (completed_quantity - (scrap_quantity + rejected_quantity + Rework_quantity))/ completed_quantity
                                       END
									  END yield,
									  CASE
										WHEN completed_quantity = 0 OR completed_quantity  IS NULL
                                        THEN NULL
                                        WHEN (scrap_quantity + rejected_quantity + Rework_quantity) > completed_quantity
                                        THEN 0
										ELSE (completed_quantity - (scrap_quantity + rejected_quantity + Rework_quantity))
                                       END tot_scrap_qty,
                                      Standard_operation_code,
									  Standard_operation_code_tbl,
                                      Assembly ,
									  assembly_description,
									  pending_material_issue,
									  item_cost * scrap_quantity Scrap_value,
                                      CASE
                                        WHEN (NVL(scrap_quantity,0) + NVL(completed_quantity,0) + NVL(rejected_quantity,0)) =0
                                        THEN 1
                                        ELSE (NVL(scrap_quantity,0) + NVL(completed_quantity,0) + NVL(rejected_quantity,0))
                                      END total_quantity,
                                      scheduled_quantity
                                    FROM
                                      (WITH wdj AS
                                         (SELECT /*+ materialize */ 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)
                                         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 )
                                             )
                                         ) ,
										 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_INTRAOPERATION_STEP','WIP_OPERATION_STATUS','WIP_SHOPFLOOR_MOVE_STATUS','WIP_JOB_STATUS','SYS_YES_NO','WIP_PURGE_REPORT_TYPE')
                                          AND view_application_id = 700
                                          AND security_group_id   = 0
                                          )
 
									  SELECT TO_CHAR (wdj.wip_entity_id
                                        ||'-'
                                        ||wo.operation_seq_num
                                        ||'-'
                                        ||wdj.organization_id
                                        ||'-OP') Ecc_Spec_Id,
										wdj.row_id,
                                        yeslk.meaning yes_flag,
                                        nolk.meaning no_flag,
                                        nonelk.meaning none_flag,
                                        nonelk.language language,
                                        wo.wip_entity_id Job,
                                        wdj.Job_name ,
										status_lkp.meaning job_status,
										WIP_ECC_UTIL_PVT.get_item_cost(wdj.primary_item_id,wdj.organization_id) item_cost,
										wdj.scheduled_start_date job_scheduled_start_date ,
                                        wdj.scheduled_completion_date job_scheduled_completion_date ,
										wdj.date_completed job_actual_completion_date ,
                                        wdj.organization_id organization_id ,
										WIP_ECC_UTIL_PVT.get_currency_code(wdj.organization_id) organization_currency ,
										wdj.organization_code organization_code,
										wdj.NAME organization_name ,
                                        wdj.date_completed job_completion_date,
                                        wo.operation_seq_num operation_sequence_number ,
                                        wo.scheduled_quantity scheduled_quantity,
                                        bd.description department_description ,
                                        NVL((SELECT MAX(CASE
                                                          WHEN autocharge_type IN(3,4)
                                                          THEN yeslk.lookup_code
                                                          ELSE nolk.lookup_code
                                                        END)
                                            FROM wip_operation_resources wor
                                            WHERE wo.wip_entity_id   = wor.wip_entity_id
                                            AND wo.operation_seq_num = wor.operation_seq_num
                                            AND wo.organization_id   = wor.organization_id
                                        ),nolk.lookup_code)  osp_operation_flag,
                                        wo.first_unit_start_date planned_start_date ,
                                        wo.last_unit_completion_date Planned_completion_date ,
                                        wo.actual_start_date Actual_start_date ,
                                        wo.actual_completion_date Actual_completion_date ,
                                        NVL(wo.quantity_in_queue,0) quantity_in_queue ,
                                        NVL(wo.quantity_running,0) quantity_in_run ,
                                        NVL(wo.quantity_waiting_to_move,0) Quantity_to_move ,
                                        to_number(NVL(wo.quantity_scrapped,0)) Scrap_quantity ,
                                        to_number(NVL(wo.quantity_rejected,0)) Rejected_Quantity ,
                                        NVL(wo.quantity_completed,0) Completed_quantity ,
                                        wo.description Operation_Description ,
										wdj.status_type job_status_type,
										to_char(decode(wo.count_point_type,3,nolk.meaning,yeslk.meaning)) autocharge,
                                        to_char(decode(wo.count_point_type,1,yeslk.meaning,nolk.meaning)) count_point,
                                        to_char(decode(wo.backflush_flag,1,yeslk.meaning,nolk.meaning)) backflush