View: AHL_SR_UE_ASSOCIATIONS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	View to retrieve the unit effectivities that are associated to a Service Request.
Implementation/DBA Data:	APPS.AHL_SR_UE_ASSOCIATIONS_V

View Text

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_V 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

Columns