ECC Enterprise Asset Management, Work Orders (Maintenance), SQL4
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: eam-wo
Query Procedure: EAM_ECC_WO_UTIL_PVT.GET_ECC_WO_DATA_LOAD_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: eam-wo
Query Procedure: EAM_ECC_WO_UTIL_PVT.GET_ECC_WO_DATA_LOAD_INFO
Security Procedure: eam_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Enterprise Asset Management, Work Orders (Maintenance), SQL4 and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT eewt.ecc_spec_id ,ewc.work_clearance_id CLEARANCE_ID ,ewc.work_clearance_name CLEARANCE_NAME ,ewc.pending_flag CLEARANCE_PENDING_FLAG ,ewc.description CLEARANCE_DESCRIPTION ,ewc.scheduled_estab_start_date CLEARANCE_SCH_ESTAB_START_DATE ,ewc.scheduled_estab_end_date CLEARANCE_SCH_ESTAB_END_DATE ,ewc.scheduled_reestab_start_date CLEARANCE_SCH_RESTAB_STRT_DATE ,ewc.scheduled_reestab_end_date CLEARANCE_SCH_RESTAB_END_DATE ,ewc.user_defined_status_id CLEARANCE_USER_STATUS_ID ,decode (ewc.pending_flag ,'Y' ,ewsv.work_clearance_status || '-' || fnd_message.get_string ('EAM' ,'EAM_PENDING_TEXT') ,ewsv.work_clearance_status) CLEARANCE_STATUS ,ewc.status_type CLEARANCE_STATUS_TYPE ,ml2.meaning ISOLATION_TYPE FROM eam_work_clearances ewc ,eam_safety_associations esa ,wip_discrete_jobs wdj ,mfg_lookups ml1 ,mfg_lookups ml2 ,eam_work_clearance_statuses_vl ewsv ,eam_isolations iso ,eam_ecc_wo_temp eewt WHERE esa.target_ref_id = wdj.wip_entity_id AND esa.source_id = ewc.work_clearance_id AND esa.association_type = 4 AND ml1.lookup_code (+) = ewc.status_type AND ml2.lookup_code (+) = iso.isolation_type AND ml1.lookup_type (+) = 'EAM_WORK_CLEARANCE_STATUS' AND ml2.lookup_type (+) = 'EAM_ISOLATION_TYPE' AND ewc.last_isolation_id = iso.isolation_id (+) AND ewsv.status_id = ewc.user_defined_status_id AND wdj.organization_id = esa.organization_id AND wdj.wip_entity_id = eewt.wip_entity_id AND wdj.organization_id = eewt.organization_id UNION SELECT DISTINCT eewt1.ecc_spec_id ,ewc1.work_clearance_id CLEARANCE_ID ,ewc1.work_clearance_name CLEARANCE_NAME ,ewc1.pending_flag CLEARANCE_PENDING_FLAG ,ewc1.description CLEARANCE_DESCRIPTION ,ewc1.scheduled_estab_start_date CLEARANCE_SCH_ESTAB_START_DATE ,ewc1.scheduled_estab_end_date CLEARANCE_SCH_ESTAB_END_DATE ,ewc1.scheduled_reestab_start_date CLEARANCE_SCH_RESTAB_STRT_DATE ,ewc1.scheduled_reestab_end_date CLEARANCE_SCH_RESTAB_END_DATE ,ewc1.user_defined_status_id CLEARANCE_USER_STATUS_ID ,decode (ewc1.pending_flag ,'Y' ,ewsv1.work_clearance_status || '-' || fnd_message.get_string ('EAM' ,'EAM_PENDING_TEXT') ,ewsv1.work_clearance_status) CLEARANCE_STATUS ,ewc1.status_type CLEARANCE_STATUS_TYPE ,ml4.meaning ISOLATION_TYPE FROM eam_work_clearances ewc1 ,eam_work_permits permit ,eam_safety_associations esa1 ,eam_safety_associations esa2 ,mfg_lookups ml3 ,mfg_lookups ml4 ,eam_work_clearance_statuses_vl ewsv1 ,eam_isolations iso1 ,wip_discrete_jobs wdj1 ,eam_ecc_wo_temp eewt1 WHERE esa2.source_id = permit.permit_id AND esa2.target_ref_id = ewc1.work_clearance_id AND esa2.association_type = 2 AND esa1.target_ref_id = wdj1.wip_entity_id AND esa1.source_id = permit.permit_id AND esa1.association_type = 3 AND ml3.lookup_code (+) = ewc1.status_type AND ml4.lookup_code (+) = iso1.isolation_type AND ml3.lookup_type (+) = 'EAM_WORK_CLEARANCE_STATUS' AND ml4.lookup_type (+) = 'EAM_ISOLATION_TYPE' AND ewc1.last_isolation_id = iso1.isolation_id (+) AND ewsv1.status_id = ewc1.user_defined_status_id AND wdj1.wip_entity_id = eewt1.wip_entity_id AND wdj1.organization_id = eewt1.organization_id AND ewc1.work_clearance_id NOT IN ( SELECT ewc.work_clearance_id FROM eam_work_clearances ewc ,eam_safety_associations esa ,wip_discrete_jobs wdj ,eam_ecc_wo_temp eewt WHERE esa.target_ref_id = wdj.wip_entity_id AND esa.source_id = ewc.work_clearance_id AND esa.association_type = 4 AND wdj.organization_id = esa.organization_id AND wdj.wip_entity_id = eewt.wip_entity_id AND wdj.organization_id = eewt.organization_id ) ) x where 2=2 |