ECC Discrete Manufacturing, Work Orders

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds the information for the work order details
Dataset Key: wip-wodetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
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_VIEW_QUALITY_NC','WIP_VIEW_ITEM','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 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)
                                         ) SELECT ecc_spec_id ,
                                      organization_id ,
                                      organization_code ,
                                      organization_name ,
                                      organization_currency ,
                                      loaded_date ,
                                      job ,
                                      job_name ,
                                      job_description ,
                                      assembly ,
                                      assembly_uom ,
                                      assembly_description ,
									  planner,
                                      job_status ,
                                      language ,
                                      job_status_type ,
                                      job_start_quantity ,
                                      scrap_quantity ,
                                      completed_quantity ,
                                      release_date ,
                                      actual_completion_date ,
									  actual_completion_date actual_completion_date_t ,
                                      job_creation_date ,
                                      open_quantity ,
									  Reject_quantity,
                                      job_type ,
                                      job_class ,
                                      net_quantity ,
                                      close_date ,
                                      build_sequence ,
                                      expedited_flag ,
                                      base_assembly ,
                                      assembly_fixed_lead_time ,
                                      assembly_var_lead_time ,
                                      assembly_total_lead_time ,
                                      repair_order_flag ,
									  repair_order,
                                      repair_line_id ,
                                      repair_number ,
                                      job_assembly_description ,
                                      schedule_group_name ,
                                      started_today_flag ,
                                      started_late_flag ,
                                      release_delay_flag ,
                                      completed_today_flag ,
                                      completed_yesterday_flag ,
                                      starting_tomorrow_flag ,
                                      completing_tomorrow_flag ,
                                      on_hold_flag ,
                                      started_today,
                                      started_late ,
                                      release_delay ,
                                      completed_today ,
                                      completed_yesterday ,
                                      starting_tomorrow ,
                                      completing_tomorrow ,
                                      on_hold ,
                                      job_start_delay ,
                                      job_finish_delay ,
                                      operation_delay_duration ,
                                      pending_material_issue,
                                      scheduled_start_date ,
                                      scheduled_completion_date ,
                                      scheduled_start_date scheduled_start_date_t ,
                                      scheduled_completion_date scheduled_completion_date_t ,
                                      bill_reference ,
                                      alternate_bill ,
                                      supply_type ,
                                      bill_revision ,
                                      bill_revision_date ,
									  bill_description,
									  routing_description,
                                      routing_reference ,
                                      routing_revision ,
                                      routing_revision_date ,
									  alternate_routing,
                                      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 ,
									  Kanban,
                                      scheduleing_priority ,
                                      sales_order ,
                                      customer ,
                                      scheduled_ship_date ,
                                      actual_ship_date ,
                                      sales_order_line ,
									  sales_order_id ,
									  ship_date_approach_risk_flag,
                                      ship_date_approach_risk_days,
                                      ship_date_passed_flag,
                                      ship_date_passed_days,
                                      job_note,
                                      demand_class ,
                                      sales_order_line_uom ,
                                      primary_quantity_reserved ,
                                      quantity_reserved ,
                                      sales_order_line_status ,
                                      delayed_sales_order ,
                                      sales_order_overdue ,
                                      assembly_category ,
                                      production_line ,
                                      operation_shop_floor_status ,
                                      start_delay_flag,
                                      finish_delay_flag ,
                                      Start_delay ,
                                      Completion_delay finish_delay,
                                      operation_delay_flag ,
                                      operation_delay,
                                      ontrack_flag,
                                      ontrack,
									  delayed,
									  delayed_flag,
									  inprogress,
									  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,
                                      release_delay_duation release_delay_duration,
                                      cal_rework_quantity rework_quantity,
									  osp_operation,
									  osp_operation_flag,
									  link,
									  item_link,
									  production_line_tbl,
									  schedule_group_name_tbl,
									  project_name_tbl,
									  task_name_tbl,
									  customer_tbl,
									  CASE WHEN pending_material_issue =1 THEN 'YES' ELSE 'NO' END unissued,
									  CASE WHEN delayed_flag =1 THEN 'YES' ELSE 'NO' END delayed_code,
									  CASE WHEN job_status_type = 1 THEN 'UNRELEASED'
                                           WHEN job_status_type = 3 THEN 'RELEASED'
	                                       WHEN job_status_type = 4 THEN 'COMPLETE'
	                                       WHEN job_status_type = 5 THEN 'COMPLETE - NO CHARGES ALLOWED'
	                                       WHEN job_status_type = 6 THEN 'HOLD'
	                                       WHEN job_status_type = 7 THEN 'CANCELED'
	                                       WHEN job_status_type = 12 THEN 'CLOSED'
	                                       WHEN job_status_type = 14 THEN 'PENDING CLOSE'
	                                       WHEN job_status_type = 15 THEN 'FAILED CLOSE'
									  END job_status_code,
									  CASE WHEN inprogress =1 THEN 'YES' ELSE 'NO' END open,
                                      CASE
                                        WHEN release_date IS NULL
                                        THEN NULL
                                        ELSE
                                          CASE
                                            WHEN (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity) > cal_completed_quantity OR cal_completed_quantity = 0
                                            THEN 0
                                            WHEN ((cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))/cal_completed_quantity) > 1
                                            THEN 100
                                            ELSE 100 * ((cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))/cal_completed_quantity)
                                          END
                                      END yield,
                                     cal_completed_quantity
                                     , dfv.*,
									 CASE WHEN release_date IS NULL
                                        THEN NULL
                                        ELSE
                                          CASE
                                            WHEN (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity) > cal_completed_quantity OR cal_completed_quantity = 0
                                            THEN 0
                                            ELSE  (cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))
                                          END
                                      END tot_scrap_qty
                                    FROM
                                      (SELECT src.*,
									    CASE WHEN osp_operation_cnt > 0 THEN yes_flag ELSE no_flag END OSP_operation,
									    CASE WHEN osp_operation_cnt > 0 THEN 1 ELSE 2 END OSP_operation_flag,
                                        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 Completion_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 1
                                          ELSE 0
                                        END delayed_flag,
									    CASE
                                          WHEN job_status_type IN (3,6)
                                          THEN 1
                                          ELSE 0
                                        END inprogress,
                                        CASE
                                          WHEN NVL(TO_NUMBER(SUBSTR(rework_quantity,1,INSTR(rework_quantity,'-A')-1)),0) > job_start_quantity
                                          THEN job_start_quantity
                                        ELSE  NVL(TO_NUMBER(SUBSTR(rework_quantity,1,INSTR(rework_quantity,'-A')-1)),0) END cal_rework_quantity,
                                        NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-A') +2,INSTR(rework_quantity,'-B',-1)-INSTR(rework_quantity,'-A')-2)),0)cal_scrap_quantity,
                                        NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-B') +2,INSTR(rework_quantity,'-C',-1)-INSTR(rework_quantity,'-B')-2)),0)cal_rejected_quantity,
                                       /* -- NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-C') +2,LENGTH(rework_quantity))),0)cal_completed_quantity */
									 completed_quantity cal_completed_quantity
                                      FROM
                                        (SELECT
 
                                          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.row_id,
                                          yeslk.meaning yes_flag,
                                          nolk.meaning no_flag,
                                          wdj.organization_code organization_code ,
                                          wdj.NAME organization_name ,
                                          WIP_ECC_UTIL_PVT.get_currency_code(wdj.organization_id) organization_currency ,
                                          SYSDATE loaded_date ,
                                          wdj.wip_entity_id job ,
									      (SELECT SUM(CASE
                                                          WHEN autocharge_type IN(3,4)
                                                          THEN 1
                                                          ELSE 0
                                                        END)
                                                     FROM wip_operation_resources wor
                                                     WHERE wdj.wip_entity_id   = wor.wip_entity_id
                                                     AND wdj.organization_id   = wor.organization_id
                                                   )  osp_operation_cnt,
                                          wdj.job_name job_name ,
                                          wdj.job_description job_description ,
                                          WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly ,
                                          WIP_ECC_UTIL_PVT.get_primary_uom_code(wdj.organization_id,wdj.primary_item_id) assembly_uom ,
                                          WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id)  assembly_description ,
									      WIP_ECC_UTIL_PVT.get_planner(wdj.organization_id,wdj.primary_item_id)  Planner,
                                          status_lkp.meaning job_status,
                                          status_lkp.language language,
									      view_quality_lkp.meaning link,
									 		view_item_lkp.meaning item_link,
                                          wdj.status_type job_status_type ,
                                          wdj.start_quantity job_start_quantity ,
                                          wdj.quantity_scrapped scrap_quantity ,
                                          wdj.quantity_completed completed_quantity ,
                                          wdj.date_released release_date ,
                                          wdj.date_completed actual_completion_date ,
                                          wdj.creation_date job_creation_date ,
                                          (wdj.start_quantity - NVL(wdj.quantity_completed,0) ) open_quantity ,
									      (SELECT SUM(quantity_rejected) FROM wip_operations  wo
                                                 WHERE wo.wip_entity_id = wdj.wip_entity_id) Reject_quantity,
                                          type_lkp.meaning job_type ,
                                          wdj.class_code job_class ,
                                          wdj.net_quantity net_quantity ,
                                          wdj.date_closed close_date ,
                                          wdj.build_sequence build_sequence ,
                                          DECODE(wdj.expedited,'Y',yeslk.meaning,nolk.meaning) expedited_flag,
                                          (SELECT msi6.concatenated_segments
                                          FROM mtl_system_items_vl msi6
                                          WHERE msi6.inventory_item_id = WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.primary_item_id)
                                          AND msi6.organization_id     = wdj.organization_id
                                          ) base_assembly,
                                          DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.primary_item_id) ,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.routing_reference_id)) assembly_fixed_lead_time,
                                          DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.routing_reference_id)) assembly_var_lead_time,
                                          NVL(DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.routing_reference_id)),0) + (NVL(DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.routing_reference_id)),0)*wdj.start_quantity) assembly_total_lead_time,
                                          DECODE(wdj.wip_entity_id,xref.wip_entity_id,1,2) repair_order_flag ,
									      DECODE(wdj.wip_entity_id,xref.wip_entity_id,yeslk.meaning,nolk.meaning) repair_order,
                                          xref.repair_line_id repair_line_id ,
                                          rep.repair_number repair_number,
                                          WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) job_assembly_description ,
                                          NVL(
                                          (SELECT schedule_group_name
                                          FROM wip_schedule_groups
                                          WHERE schedule_group_id= wdj.schedule_group_id
                                          ) ,nonelk.meaning ) schedule_group_name ,
 
                                          (SELECT schedule_group_name
                                          FROM wip_schedule_groups
                                          WHERE schedule_group_id= wdj.schedule_group_id
                                          )  schedule_group_name_tbl ,
                                          (SELECT MAX(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))
                                            ||'-A'
                                            ||SUM(MAX(wo.quantity_scrapped))
                                            ||'-B'
                                            || SUM(MAX(wo.quantity_rejected))
                                            ||'-C'
                                            || MAX(MAX(wo.quantity_completed))
                                          FROM wip_move_transactions WMT,
                                            Wip_Operations Wo
                                          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.to_operation_seq_num AND wmt.fm_operation_seq_num) )
                                          AND wo.wip_entity_id = wdj.wip_entity_id
                                          GROUP BY wo.wip_entity_id,
                                            wo.organization_id,
                                            wo.operation_seq_num
                                          ) rework_quantity,
                                          (SELECT SUM(wo.quantity_rejected)
                                          FROM wip_operations wo
                                          WHERE wo.wip_entity_id = wdj.wip_entity_id
                                          ) cumulative_reject,
                                          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