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

SQL 95 lines

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

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

Description

SQL 95 lines

Find out more

Features

Download

FAQ