ECC Discrete Manufacturing, Operations, SQL1

Description
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, Org Code ...
Imported from ECC
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,
                                      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,
										bso.rowid 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,
                                        wo.progress_percentage progress_percentage ,
										wdj.job_description,
										(select pap.full_name
                                        from per_all_people_f pap
                                        where pap.person_id = wo.employee_id
                                        and rownum = 1) clock_in_employee,
										to_char(case when wdj.serialization_start_op is not null and
                                               wdj.serialization_start_op <= wo.operation_seq_num and
                                         (to_number(nvl(wo.quantity_in_queue,0)) + to_number(nvl(wo.quantity_running,0)) + to_number(nvl(wo.quantity_waiting_to_move,0)) +
                                          to_number(nvl(wo.quantity_rejected,0)) + to_number(nvl(wo.quantity_scrapped,0))) <> 0
                                          then yeslk.meaning
                                          else null
                                          end) serialized_link,
                                        TO_CHAR(
                                        (SELECT (SUM(SUM((
                                          CASE
                                            WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999)
                                            THEN 0
                                            ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*wor.applied_resource_units
                                          END))) )   
										  /(SUM(MAX((
										  CASE WHEN (CASE
                                            WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999)
                                            THEN 0
                                            ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*wor.usage_rate_or_amount
                                          END) = 0 THEN NULL ELSE 
                                          (CASE
                                            WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999)
                                            THEN 0
                                            ELSE   WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*  wor.usage_rate_or_amount
                                          END) END )*   CASE WHEN least(wor.assigned_units,bdr.capacity_units) =0 OR bdr.utilization = 0 OR  bdr.efficiency =0 OR wo.scheduled_quantity =0 THEN NULL ELSE   (
										  DECODE(wor.basis_type,2,1,wo.scheduled_quantity) /least(wor.assigned_units,bdr.capacity_units)/(NVL( bdr.utilization,1))/(NVL(bdr.efficiency,1))) END )))
                                          ||'-A'
                                          ||(SUM(SUM((
                                          CASE
                                            WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999)
                                            THEN 0
                                            ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*  wor.applied_resource_units
                                          END))) )
                                          ||'-B'
                                          ||(SUM(MAX((
                                          CASE
                                            WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999)
                                            THEN 0
                                            ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*wor.usage_rate_or_amount
                                          END)   * CASE WHEN least(wor.assigned_units,bdr.capacity_units) =0 OR bdr.utilization = 0 
										  OR bdr.efficiency =0 OR wo.scheduled_quantity =0  THEN NULL ELSE 
										  (DECODE(wor.basis_type,2,1,wo.scheduled_quantity) /least(wor.assigned_units,bdr.capacity_units)/(NVL( bdr.utilization,1))/(NVL(bdr.efficiency,1))) END )))
                                        FROM wip_operation_resources wor,
                                          bom_department_resources bdr
                                        WHERE wor.wip_entity_id  = wo.wip_entity_id
                                        AND wo.operation_seq_num = wor.operation_seq_num
                                        AND wo.department_id     = bdr.department_id
                                        AND wor.resource_id      = bdr.resource_id
                                        AND wor.scheduled_flag  <>2
                                        GROUP BY TO_CHAR(NVL(TO_CHAR(wor.schedule_seq_num),rowidtochar(wor.rowid)))
                                        )) operation_progress,
                                        bd.department_code Department,
                                        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 ))  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 )) 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 )) 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 )) ELSE 0 END
                                          ELSE 0
                                        END operation_delay_duration,
                                        CASE
                                        WHEN (wo.quantity_completed >= (wdj.start_quantity-wo.cumulative_scrap_quantity)) OR wdj.status_type in (4,5,7,12)
                                        THEN operation_com_lkp.meaning
                                        WHEN ((Wo.quantity_in_queue + wo.quantity_running > 0) OR (wo.date_last_moved is not null) or (wo.quantity_completed > 0)) and wdj.status_type in (3,6)
                                        THEN operation_run_lkp.meaning
                                        ELSE operation_ns_lkp.meaning
                                        END operation_status,
                                        CASE
                                        WHEN (wo.quantity_completed >= (wdj.start_quantity-wo.cumulative_scrap_quantity)) OR wdj.status_type in (4,5,7,12)
                                        THEN operation_com_lkp.lookup_code
                                        WHEN ((Wo.quantity_in_queue + wo.quantity_running > 0) OR (wo.date_last_moved is not null) or (wo.quantity_completed > 0)) and wdj.status_type in (3,6)
                                        THEN operation_run_lkp.lookup_code
                                        ELSE operation_ns_lkp.lookup_code
                                        END op_status_flag,
                                        shop_floor_status_step.meaning shop_floor_status_step,
                                        wsfs.shop_floor_status_code shop_floor_status_code,
                                        Wsfs.Status_Move_Flag,
                                        CASE WHEN wdj.status_type in (3,6)
                                        THEN
										CASE
                                        WHEN Wsfs.Status_Move_Flag =1
                                        THEN move_status_lkp.meaning
                                        WHEN Wsfs.Status_Move_Flag =2
                                        THEN nomove_status_lkp.meaning
                                        ELSE NULL
                                        END
										ELSE NULL
                                        END Move_status,
                                        (
                                        SELECT SUM(
                                          CASE
                                            WHEN wmt.fm_operation_seq_num        = wmt.to_operation_seq_num
                                            AND wmt.to_intraoperation_step_type  < wmt.fm_intraoperation_step_type
                                            AND wmt.to_intraoperation_step_type IN (1,2)
                                            THEN wmt.primary_quantity
                                            WHEN wmt.fm_operation_seq_num <> wmt.to_operation_seq_num
                                            AND wmt.to_operation_seq_num   < wmt.fm_operation_seq_num
                                            THEN
                                              CASE
                                                WHEN wo.operation_seq_num            = wmt.to_operation_seq_num
                                                AND wmt.to_intraoperation_step_type IN (1,2)
                                                THEN wmt.primary_quantity
                                                WHEN wo.operation_seq_num            = wmt.fm_operation_seq_num
                                                AND wmt.fm_intraoperation_step_type <> 1
                                                THEN wmt.primary_quantity
                                                WHEN wo.operation_seq_num <> wmt.fm_operation_seq_num
                                                OR wo.operation_seq_num   <> wmt.to_operation_seq_num
                                                THEN wmt.primary_quantity
                                                ELSE 0
                                              END
                                            ELSE 0
                                          END )
                                        FROM wip_move_transactions WMT
                                        WHERE 1                =1
                                        AND wo.organization_id = wmt.organization_id
                                        AND wo.wip_entity_id   = wmt.wip_entity_id
                                        AND ((wo.operation_seq_num BETWEEN wmt.fm_operation_seq_num AND wmt.to_operation_seq_num)
                                        OR ( wo.operation_seq_num BETWEEN wmt.t