ECC Enterprise Asset Management, Work Orders (Maintenance), SQL4

➜ Back to Library
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
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
Download
Related Blitz Reports
ECC Enterprise Asset Management, Work Orders (Maintenance), SQL3 ECC Enterprise Asset Management, Work Orders (Maintenance), SQL1 AHL Supplier Warranty Claims ECC Enterprise Asset Management, Stores Work Order (Maintenance), SQL1 EAM Work Orders ECC Enterprise Asset Management, Work Order Assets (Maintenance) ECC Enterprise Asset Management, Work Order Operations (Maintenance) ECC Enterprise Asset Management, Work Requests (Maintenance) CST Activity Cause
Blitz Report™

Blitz Report™ provides multiple benefits: