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
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 , status_code, 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, 'fwkhp_sswafunc.gif' as wo_qa_link, 'fwkhp_sswafunc.gif' as assembly_qa_link, 'fwkhp_sswafunc.gif' as project_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.lookup_code status_code, status_lkp.language language, 'fwkhp_sswafunc.gif' link , /* view_quality_lkp.meaning link,*/ 'fwkhp_sswafunc.gif' item_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 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 , WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.bom_reference_id) bill_reference , 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, bad2.description routing_description, 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 and milk.organization_id = wdj.organization_id ) completion_locator , NVL(NVL(ppa.NAME,psn.project_name) ,nonelk.meaning) project_name , NVL(ppa.NAME,psn.project_name) project_name_tbl , 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_name task_name_tbl , pat.task_number task_number , wdj.demand_class wo_demand_class , wdj.lot_number lot_number , (SELECT k.kanban_card_number FROM mtl_kanban_cards k WHERE k.kanban_card_id = wdj.kanban_card_id) kanban, tolerence_lkp.meaning overcompletion_tolerance_type, wdj.overcompletion_tolerance_value overcompletion_tolerance_value, wdj.priority scheduleing_priority , mso1.concatenated_segments sales_order , NVL(hz.party_name,nonelk.meaning ) customer , hz.party_name customer_tbl , ool1.schedule_ship_date scheduled_ship_date , ool1.actual_shipment_date actual_ship_date , case when ool1.schedule_ship_date > sysdate then case when (((ool1.schedule_ship_date - sysdate) > 0) and ((ool1.schedule_ship_date - sysdate) < 7)) then yeslk.lookup_code else nolk.lookup_code end else null end ship_date_approach_risk_flag, case when ool1.schedule_ship_date > sysdate and ((ool1.schedule_ship_date - sysdate) > 0) and ((ool1.schedule_ship_date - sysdate) < 7) then case when (((ool1.schedule_ship_date - sysdate) >= 6) and ((ool1.schedule_ship_date - sysdate) < 7)) then '6-7' when (((ool1.schedule_ship_date - sysdate) >= 5) and ((ool1.schedule_ship_date - sysdate) < 6)) then '5-6' when (((ool1.schedule_ship_date - sysdate) >= 4) and ((ool1.schedule_ship_date - sysdate) < 5)) then '4-5' when (((ool1.schedule_ship_date - sysdate) >= 3) and ((ool1.schedule_ship_date - sysdate) < 4)) then '3-4' when (((ool1.schedule_ship_date - sysdate) >= 2) and ((ool1.schedule_ship_date - sysdate) < 3)) then '2-3' when (((ool1.schedule_ship_date - sysdate) >= 1) and ((ool1.schedule_ship_date - sysdate) < 2)) then '1-2' when (((ool1.schedule_ship_date - sysdate) > 0) and ((ool1.schedule_ship_date - sysdate) < 1)) then '0-1' else null end else null end ship_date_approach_risk_days, case when ((ool1.actual_shipment_date is null) and (ool1.schedule_ship_date < sysdate)) then yeslk.lookup_code else nolk.lookup_code end ship_date_passed_flag , case when ((ool1.actual_shipment_date is null) and (ool1.schedule_ship_date < sysdate)) then case when ((sysdate - ool1.schedule_ship_date) >= 7) then '>7' when ((sysdate - ool1.schedule_ship_date) >=6 and (sysdate - ool1.schedule_ship_date) < 7) then '6-7' when ((sysdate - ool1.schedule_ship_date) >=5 and (sysdate - ool1.schedule_ship_date) < 6) then '5-6' when ((sysdate - ool1.schedule_ship_date) >=4 and (sysdate - ool1.schedule_ship_date) < 5) then '4-5' when ((sysdate - ool1.schedule_ship_date) >=3 and (sysdate - ool1.schedule_ship_date) < 4) then '3-4' when ((sysdate - ool1.schedule_ship_date) >=2 and (sysdate - ool1.schedule_ship_date) < 3) then '2-3' when ((sysdate - ool1.schedule_ship_date) >=1 and (sysdate - ool1.schedule_ship_date) < 2) then '1-2' when ((sysdate - ool1.schedule_ship_date) > 0 and (sysdate - ool1.schedule_ship_date) < 1) then '0-1' else NULL end else NULL end ship_date_passed_days, replace(replace(replace(replace(to_char(substrb(dbms_lob.substr( wdj.job_note, 4000, 1 ),4000)),'<br>',''),'***',''),'<b>',''),'</b>','') job_note, ool1.line_number sales_order_line , ool1.header_id sales_order_id , ool1.demand_class_code demand_class , mr.reservation_uom_code sales_order_line_uom , mr.primary_reservation_quantity primary_quantity_reserved , mr.reservation_quantity quantity_reserved , DECODE(ool1.line_number,NULL,NULL,DECODE(NVL(ool1.open_flag,'N'),'N',olclosed.meaning, DECODE (ool1.cancelled_flag,'Y',olcanceled.meaning, DECODE (ool1.booked_flag,'Y',olbooked.meaning,olentered.meaning)))) sales_order_line_status , DECODE( mr.demand_source_header_id, NULL,0, DECODE(SIGN(ool1.schedule_ship_date - ool1.actual_shipment_date), 1, 0, 1 )) delayed_sales_order, CASE WHEN ool1.schedule_ship_date < SYSDATE AND ool1.flow_status_code <> 'SHIPPED' AND ool1.actual_shipment_date IS NULL THEN 1 ELSE 0 END sales_order_overdue, (SELECT acat.concatenated_segments FROM mtl_item_catalog_groups_b_kfv acat WHERE acat.item_catalog_group_id = WIP_ECC_UTIL_PVT.get_item_catalog_group_id(wdj.organization_id,wdj.primary_item_id) ) assembly_category, NVL(wl.description ,nonelk.meaning ) production_line , wl.description production_line_tbl , (SELECT COUNT(wsfs.shop_floor_status_code) FROM wip_shop_floor_statuses wsfs, wip_operations wo WHERE wo.organization_id = wdj.organization_id AND wo.wip_entity_id = wdj.wip_entity_id AND wo.organization_id = wsfs.organization_id AND wo.wip_entity_id = wsfs.wip_entity_id AND wo.operation_seq_num = wsfs.operation_seq_num ) operation_shop_floor_status FROM wdj wdj, csd_repair_job_xref xref, csd_repairs rep, wip_lines wl, mtl_reservations mr, oe_order_lines_all ool1, mtl_sales_orders_kfv mso1, hz_parties hz, hz_cust_accounts hzc, (SELECT meaning FROM oe_lookups WHERE lookup_type = 'LINE_FLOW_STATUS' AND lookup_code = 'CLOSED' ) olclosed, (SELECT meaning FROM oe_lookups WHERE lookup_type = 'LINE_FLOW_STATUS' AND lookup_code = 'CANCELLED' ) olcanceled, (SELECT meaning FROM oe_lookups WHERE lookup_type = 'LINE_FLOW_STATUS' AND lookup_code = 'BOOKED' ) olbooked, (SELECT meaning FROM oe_lookups WHERE lookup_type = 'LINE_FLOW_STATUS' AND lookup_code = 'ENTERED' ) olentered, (SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_ECC_WEEK_BINS' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.lookup_code = 1 ) past_week_lkp, (SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_ECC_WEEK_BINS' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.lookup_code = 2 ) curr_week_lkp, (SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_ECC_WEEK_BINS' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.lookup_code = 3 ) future_week_lkp, ( SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_JOB_STATUS' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 ) status_lkp, /* ( SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_VIEW_QUALITY_NC' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.lookup_code = 1 ) view_quality_lkp, */ /* ( SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_VIEW_ITEM' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.lookup_code = 1 ) view_item_lkp, */ (SELECT ml4.meaning, ml4.lookup_code, ml4.language FROM lookup_details ml4 WHERE ml4.lookup_type = 'WIP_DISCRETE_JOB' AND ml4.view_application_id = 700 AND ml4.security_group_id = 0 ) type_lkp, (SELECT ml5.meaning, ml5.lookup_code, ml5.language FROM lookup_details ml5 WHERE ml5.lookup_type = 'WIP_SUPPLY' AND ml5.view_application_id = 700 AND ml5.security_group_id = 0 ) supply_type_lkp, (SELECT octlk.meaning, octlk.language,octlk.lookup_code FROM lookup_details octlk WHERE octlk.lookup_type = 'WIP_TOLERANCE_TYPE' AND octlk.view_application_id = 700 AND octlk.security_group_id = 0) tolerence_lkp, pa_projects_all ppa, pjm_seiban_numbers psn, pa_tasks pat, (SELECT yeslk.meaning, yeslk.lookup_code, yeslk.language FROM lookup_details yeslk WHERE yeslk.lookup_type = 'SYS_YES_NO' AND yeslk.view_application_id = 700 AND yeslk.lookup_code = '1' AND yeslk.security_group_id = 0 ) yeslk, (SELECT nolk.meaning, nolk.lookup_code, nolk.language FROM lookup_details nolk WHERE nolk.lookup_type = 'SYS_YES_NO' AND nolk.view_application_id = 700 AND nolk.lookup_code = '2' AND nolk.security_group_id = 0 ) nolk, (SELECT nolk.meaning, nolk.lookup_code, nolk.language FROM lookup_details nolk WHERE nolk.lookup_type = 'WIP_PURGE_REPORT_TYPE' AND nolk.view_application_id = 700 AND nolk.lookup_code = '4' AND nolk.security_group_id = 0 ) nonelk, bom_alternate_designators_tl bad, bom_alternate_designators_tl bad2 WHERE wdj.alternate_bom_designator=bad.alternate_designator_code(+) AND wdj.organization_id = bad.organization_id(+) AND wdj.alternate_routing_designator=bad2.alternate_designator_code(+) AND wdj.organization_id = bad2.organization_id(+) AND wdj.line_id = wl.line_id(+) AND mr.demand_source_type_id(+) = 2 AND mr.supply_source_type_id(+) = 5 AND mr.demand_source_line_id = ool1.line_id(+) AND mso1.sales_order_id(+) = mr.demand_source_header_id AND ool1.sold_to_org_id = hzc.cust_account_id(+) AND hz.party_id(+) = hzc.party_id AND wdj.organization_id = mr.organization_id(+) AND wdj.wip_entity_id = mr.supply_source_header_id(+) AND wdj.project_id = ppa.project_id(+) AND wdj.project_id = psn.project_id(+) AND wdj.task_id = pat.task_id(+) AND wdj.status_type = status_lkp.lookup_code(+) AND wdj.job_type = type_lkp.lookup_code(+) AND wdj.wip_entity_id = xref.wip_entity_id(+) AND rep.repair_line_id(+) = xref.repair_line_id AND wdj.wip_supply_type = supply_type_lkp.lookup_code(+) AND to_char(wdj.overcompletion_tolerance_type) = tolerence_lkp.lookup_code(+) AND wdj.language = NVL(status_lkp.language,wdj.language) AND NVL(supply_type_lkp.language,wdj.language) = NVL(status_lkp.language,wdj.language) AND NVL(type_lkp.language,wdj.language) = NVL(status_lkp.language,wdj.language) AND NVL(type_lkp.language,wdj.language) = NVL(tolerence_lkp.language,wdj.language) /* AND NVL(type_lkp.language,wdj.language) = NVL(view_quality_lkp.language,wdj.language) AND NVL(type_lkp.language,wdj.language) = NVL(view_item_lkp.language,wdj.language) */ AND wdj.language = NVL(yeslk.language,wdj.language) AND wdj.language = NVL(nolk.language,wdj.language) AND wdj.language = NVL(nonelk.language,wdj.language) AND wdj.language = NVL(bad.language,wdj.language) AND wdj.language = NVL(bad2.language,wdj.language) AND wdj.language = NVL(future_week_lkp.language,wdj.language) AND wdj.language = NVL(curr_week_lkp.language,wdj.language) AND wdj.language = NVL(past_week_lkp.language,wdj.language) 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 )) ) src ) src, (select "ROW_ID" "'WIP_WO_ROW_ID'","CONTEXT" "'WIP_WO_CONTEXT'","CONCATENATED_SEGMENTS" "'WIP_WO_CONCATENATED_SEGMENTS'" from WIP_DISCRETE_JOBS_DFV) dfv WHERE src.row_id = dfv."'WIP_WO_ROW_ID'"(+) AND src.language IN ( 'US' ) ) PIVOT ( MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME, MAX(JOB_STATUS) AS JOB_STATUS , MAX(JOB_TYPE) AS JOB_TYPE ,MAX(SUPPLY_TYPE) AS SUPPLY_TYPE,MAX(OSP_OPERATION) AS OSP_OPERATION, MAX(STARTED_TODAY ) AS STARTED_TODAY, MAX(STARTED_LATE ) AS STARTED_LATE ,MAX(RELEASE_DELAY ) AS RELEASE_DELAY, MAX(DELAYED) AS DELAYED, MAX(COMPLETED_TODAY ) AS COMPLETED_TODAY, MAX(BILL_DESCRIPTION) AS BILL_DESCRIPTION, MAX(ROUTING_DESCRIPTION) AS ROUTING_DESCRIPTION, MAX(COMPLETED_YESTERDAY ) AS COMPLETED_YESTERDAY, MAX(STARTING_TOMORROW ) AS STARTING_TOMORROW , MAX(EXPEDITED_FLAG) AS EXPEDITED_FLAG, MAX(COMPLETING_TOMORROW ) AS COMPLETING_TOMORROW, MAX(ON_HOLD ) AS ON_HOLD , MAX(FINISH_DELAY) AS FINISH_DELAY, MAX(START_DELAY) AS START_DELAY, MAX(OPERATION_DELAY) AS OPERATION_DELAY ,MAX(ONTRACK) AS ONTRACK , MAX(PRODUCTION_LINE) AS PRODUCTION_LINE , MAX(PROJECT_NAME) AS PROJECT_NAME , MAX(TASK_NAME) AS TASK_NAME , MAX(CUSTOMER) AS CUSTOMER, MAX(SCHEDULE_GROUP_NAME) AS SCHEDULE_GROUP_NAME ,/*MAX(link) AS link ,MAX(item_link) AS item_link,*/ MAX(REPAIR_ORDER) AS REPAIR_ORDER FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |