VIEW: APPS.AHL_SR_UE_ASSOCIATIONS_V

Source

View Text - Preformatted

SELECT UE.UNIT_EFFECTIVITY_ID, UE.OBJECT_VERSION_NUMBER, UE.CSI_ITEM_INSTANCE_ID, UE.STATUS_CODE, F1.meaning STATUS_meaning, F1.description status_description, UE.MR_HEADER_ID, MR.TITLE MR_TITLE, MR.VERSION_NUMBER MR_VERSION, MR.DESCRIPTION MR_DESCRIPTION, MR.PROGRAM_TYPE_CODE, F3.meaning PROGRAM_TYPE_meaning, F3.description program_type_description, SR_UE.cs_incident_id, UER.ue_relationship_id, UER.relationship_code, F2.meaning relationship_meaning, F2.description relationship_description From AHL_UNIT_EFFECTIVITIES_APP_V SR_UE, AHL_MR_HEADERS_VL MR, AHL_UE_RELATIONSHIPS UER, CS_INCIDENTS_ALL_B CS, ahl_unit_effectivities_app_v UE, (select MEANING,DESCRIPTION,LOOKUP_CODE from FND_LOOKUP_VALUES_VL where lookup_type = 'AHL_UNIT_EFFECTIVITY_STATUS') F1, (select MEANING,DESCRIPTION,LOOKUP_CODE from FND_LOOKUP_VALUES_VL where lookup_type = 'AHL_FMP_MR_RELATIONSHIP') F2, (select MEANING,DESCRIPTION,LOOKUP_CODE from FND_LOOKUP_VALUES_VL where lookup_type = 'AHL_FMP_MR_PROGRAM_TYPE') F3 WHERE sr_ue.unit_effectivity_id = uer.ue_id and uer.related_ue_id = ue.unit_effectivity_id and (sr_ue.status_code IS NULL OR sr_ue.status_code NOT IN ('DEFERRED', 'EXCEPTION') ) and UE.MR_HEADER_ID = MR.MR_HEADER_ID and SR_UE.cs_incident_id = CS.incident_id and f1.lookup_code(+) = UE.status_code and f2.lookup_code(+) = UER.relationship_code and UER.relationship_code = 'PARENT' and f3.lookup_code = MR.program_type_code and SR_UE.cs_incident_id = CS.incident_id

View Text - HTML Formatted

SELECT UE.UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER
, UE.CSI_ITEM_INSTANCE_ID
, UE.STATUS_CODE
, F1.MEANING STATUS_MEANING
, F1.DESCRIPTION STATUS_DESCRIPTION
, UE.MR_HEADER_ID
, MR.TITLE MR_TITLE
, MR.VERSION_NUMBER MR_VERSION
, MR.DESCRIPTION MR_DESCRIPTION
, MR.PROGRAM_TYPE_CODE
, F3.MEANING PROGRAM_TYPE_MEANING
, F3.DESCRIPTION PROGRAM_TYPE_DESCRIPTION
, SR_UE.CS_INCIDENT_ID
, UER.UE_RELATIONSHIP_ID
, UER.RELATIONSHIP_CODE
, F2.MEANING RELATIONSHIP_MEANING
, F2.DESCRIPTION RELATIONSHIP_DESCRIPTION
FROM AHL_UNIT_EFFECTIVITIES_APP_V SR_UE
, AHL_MR_HEADERS_VL MR
, AHL_UE_RELATIONSHIPS UER
, CS_INCIDENTS_ALL_B CS
, AHL_UNIT_EFFECTIVITIES_APP_V UE
, (SELECT MEANING
, DESCRIPTION
, LOOKUP_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_UNIT_EFFECTIVITY_STATUS') F1
, (SELECT MEANING
, DESCRIPTION
, LOOKUP_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_RELATIONSHIP') F2
, (SELECT MEANING
, DESCRIPTION
, LOOKUP_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE') F3
WHERE SR_UE.UNIT_EFFECTIVITY_ID = UER.UE_ID
AND UER.RELATED_UE_ID = UE.UNIT_EFFECTIVITY_ID
AND (SR_UE.STATUS_CODE IS NULL OR SR_UE.STATUS_CODE NOT IN ('DEFERRED'
, 'EXCEPTION') )
AND UE.MR_HEADER_ID = MR.MR_HEADER_ID
AND SR_UE.CS_INCIDENT_ID = CS.INCIDENT_ID
AND F1.LOOKUP_CODE(+) = UE.STATUS_CODE
AND F2.LOOKUP_CODE(+) = UER.RELATIONSHIP_CODE
AND UER.RELATIONSHIP_CODE = 'PARENT'
AND F3.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE
AND SR_UE.CS_INCIDENT_ID = CS.INCIDENT_ID