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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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