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

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
			   ,permit.permit_id PERMIT_ID
               ,permit.permit_name PERMIT_NAME
               ,permit.description PERMIT_DESCRIPTION
               ,permit.permit_type PERMIT_TYPE_ID
               ,ml1.meaning PERMIT_TYPE
               ,permit.user_defined_status_id PERMIT_USER_STATUS_ID
               ,permit.status_type PERMIT_SYSTEM_STATUS_ID
               ,decode (permit.pending_flag
                       ,'Y'
                       ,epsv.permit_status
                        || '-'
                        || fnd_message.get_string ('EAM'
                                                  ,'EAM_PENDING_TEXT')
                       ,epsv.permit_status) PERMIT_STATUS
               ,permit.valid_from PERMIT_VALID_FROM
               ,permit.valid_to PERMIT_VALID_TO
               ,permit.pending_flag PERMIT_PENDING_FLAG
               ,permit.completion_date PERMIT_COMPLETION_DATE
        FROM    eam_work_permits permit
               ,eam_safety_associations esa
               ,wip_discrete_jobs wdj
               ,mfg_lookups ml1
               ,eam_permit_statuses_vl epsv
               ,eam_ecc_wo_temp eewt
		WHERE esa.target_ref_id = wdj.wip_entity_id
        AND     esa.source_id = permit.permit_id
        AND     esa.association_type = 3
        AND     ml1.lookup_code (+) = permit.permit_type
        AND     ml1.lookup_type (+) = 'EAM_WORK_PERMIT_TYPE'
        AND     epsv.status_id = permit.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
               ,permit1.permit_id PERMIT_ID
               ,permit1.permit_name PERMIT_NAME
               ,permit1.description PERMIT_DESCRIPTION
               ,permit1.permit_type PERMIT_TYPE_ID
               ,ml2.meaning PERMIT_TYPE
               ,permit1.user_defined_status_id PERMIT_USER_STATUS_ID
               ,permit1.status_type PERMIT_SYSTEM_STATUS_ID
               ,decode (permit1.pending_flag
                       ,'Y'
                       ,epsv1.permit_status
                        || '-'
                        || fnd_message.get_string ('EAM'
                                                  ,'EAM_PENDING_TEXT')
                       ,epsv1.permit_status) PERMIT_STATUS
               ,permit1.valid_from PERMIT_VALID_FROM
               ,permit1.valid_to PERMIT_VALID_TO
               ,permit1.pending_flag PERMIT_PENDING_FLAG
               ,permit1.completion_date PERMIT_COMPLETION_DATE
        FROM    eam_work_permits permit1
               ,eam_safety_associations esa1
               ,eam_safety_associations esa2
               ,wip_discrete_jobs wdj1
               ,mfg_lookups ml2
               ,eam_permit_statuses_vl epsv1
               ,eam_ecc_wo_temp eewt1
		WHERE wdj1.wip_entity_id = esa2.target_ref_id
        AND     esa2.source_id = esa1.target_ref_id
        AND     esa2.association_type = 4
        AND     esa1.association_type = 2
        AND     esa1.source_id = permit1.permit_id
        AND     ml2.lookup_code (+) = permit1.permit_type
        AND     ml2.lookup_type (+) = 'EAM_WORK_PERMIT_TYPE'
        AND     epsv1.status_id = permit1.user_defined_status_id
        AND     wdj1.wip_entity_id = eewt1.wip_entity_id
        AND     wdj1.organization_id = eewt1.organization_id
        AND     permit1.permit_id NOT IN
                (
                SELECT  permit.permit_id
                FROM    eam_work_permits permit
                       ,eam_safety_associations esa
                       ,wip_discrete_jobs wdj
                       ,eam_permit_statuses_vl epsv
                       ,eam_ecc_wo_temp eewt
                WHERE   esa.target_ref_id = wdj.wip_entity_id
                AND     esa.source_id = permit.permit_id
                AND     esa.association_type = 3
                AND     epsv.status_id = permit.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
                )
) x
where
2=2