View: AHL_UNIT_EFFECTIVITIES_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve unit effectivities information based on the Maintainance Requirements defined in Fleet Maintainance Program and item instances available in Install Base for a position (node).
Implementation/DBA Data:	APPS.AHL_UNIT_EFFECTIVITIES_V

View Text

SELECT UE.UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER
, UE.LAST_UPDATE_DATE
, UE.LAST_UPDATED_BY
, UE.CREATION_DATE
, UE.CREATED_BY
, UE.LAST_UPDATE_LOGIN
, UE.MR_INTERVAL_ID
, UE.CSI_ITEM_INSTANCE_ID
, UE.MR_EFFECTIVITY_ID
, UE.MR_HEADER_ID
, AHL_UMP_UTIL_PKG.GET_UNITNAME(UE.CSI_ITEM_INSTANCE_ID) UNIT_NAME
, MTL.CONCATENATED_SEGMENTS PART_NUMBER
, CSI.SERIAL_NUMBER
, CSI.INSTANCE_NUMBER
, UE.EARLIEST_DUE_DATE
, UE.DUE_DATE
, UE.LATEST_DUE_DATE
, UE.STATUS_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_UNIT_EFFECTIVITY_STATUS'
AND LOOKUP_CODE = UE.STATUS_CODE) STATUS
, UE.MESSAGE_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_UMP_MESSAGE'
AND LOOKUP_CODE = UE.MESSAGE_CODE) MESSAGE
, UE.TOLERANCE_FLAG
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LOOKUP_CODE = UE.TOLERANCE_FLAG) TOLERANCE
, UE.DATE_RUN
, UE.DUE_COUNTER_VALUE
, MRI.COUNTER_NAME
, (SELECT NET_READING
FROM CSI_CP_COUNTERS_V
WHERE CUSTOMER_PRODUCT_ID = UE.CSI_ITEM_INSTANCE_ID
AND COUNTER_NAME = MRI.COUNTER_NAME ) NET_READING
, UE.REMARKS
, UE.PRECEDING_UE_ID
, UE.FORECAST_SEQUENCE
, UE.REPETITIVE_MR_FLAG
, UE.SET_DUE_DATE
, UE.ACCOMPLISHED_DATE
, MR.TITLE TITLE
, MR.VERSION_NUMBER
, MR.DESCRIPTION DESCRIPTION
, MR.PROGRAM_TYPE_CODE PROGRAM_TYPE_CODE
, F1.MEANING PROGRAM_TYPE
, MR.IMPLEMENT_STATUS_CODE
, F2.MEANING IMPLEMENT_STATUS
, MR.CATEGORY_CODE
, F3.MEANING CATEGORY
, MR.REVISION
, UE.SERVICE_LINE_ID
, OKCL.LINE_NUMBER
, UE.PROGRAM_MR_HEADER_ID
, UE.CANCEL_REASON_CODE
, PGM.TITLE PROGRAM_TITLE
, OKCH.CONTRACT_NUMBER
, OKCH.CONTRACT_NUMBER_MODIFIER
, (SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B ORIG
, AHL_UE_RELATIONSHIPS REL
WHERE REL.RELATED_UE_ID = UE.UNIT_EFFECTIVITY_ID
AND ORIG.UNIT_EFFECTIVITY_ID = REL.ORIGINATOR_UE_ID
AND ORIG.MR_HEADER_ID = MR.MR_HEADER_ID) ORIGINATOR_TITLE
, (SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B DEP
WHERE DEP.UNIT_EFFECTIVITY_ID = UE.PRECEDING_UE_ID
AND DEP.MR_HEADER_ID = MR.MR_HEADER_ID) DEPENDANT_TITLE
, UE.ATTRIBUTE_CATEGORY
, UE.ATTRIBUTE1
, UE.ATTRIBUTE2
, UE.ATTRIBUTE3
, UE.ATTRIBUTE4
, UE.ATTRIBUTE5
, UE.ATTRIBUTE6
, UE.ATTRIBUTE7
, UE.ATTRIBUTE8
, UE.ATTRIBUTE9
, UE.ATTRIBUTE10
, UE.ATTRIBUTE11
, UE.ATTRIBUTE12
, UE.ATTRIBUTE13
, UE.ATTRIBUTE14
, UE.ATTRIBUTE15
, UE.DEFER_FROM_UE_ID
, (SELECT MR.TITLE
FROM AHL_MR_HEADERS_VL MR
, AHL_UNIT_EFFECTIVITIES_B DEF
WHERE DEF.UNIT_EFFECTIVITY_ID = UE.DEFER_FROM_UE_ID
AND DEF.MR_HEADER_ID = MR.MR_HEADER_ID) DEFER_FROM_MR_TITLE
, UE.QA_COLLECTION_ID
, TO_NUMBER(NULL) CS_INCIDENT_ID
, TO_CHAR(NULL) CS_INCIDENT_NUMBER
, TO_CHAR(NULL) CS_INCIDENT_SUMMARY
, UE.APPLICATION_USG_CODE
, UE.OBJECT_TYPE
FROM FND_LOOKUP_VALUES_VL F1
, FND_LOOKUP_VALUES_VL F2
, FND_LOOKUP_VALUES_VL F3
, AHL_MR_INTERVALS_V MRI
, AHL_MR_HEADERS_B PGM
, OKC_K_HEADERS_V OKCH
, OKC_K_LINES_V OKCL
, AHL_MR_HEADERS_VL MR
, MTL_SYSTEM_ITEMS_KFV MTL
, CSI_ITEM_INSTANCES CSI
, AHL_UNIT_EFFECTIVITIES_VL UE
WHERE UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND UE.MR_HEADER_ID = MR.MR_HEADER_ID
AND UE.MR_INTERVAL_ID = MRI.MR_INTERVAL_ID(+)
AND UE.PROGRAM_MR_HEADER_ID = PGM.MR_HEADER_ID(+)
AND UE.SERVICE_LINE_ID = OKCL.ID(+)
AND OKCL.DNZ_CHR_ID = OKCH.ID(+)
AND UE.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND UE.OBJECT_TYPE = 'MR'
AND F1.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND F1.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE
AND F2.LOOKUP_TYPE = 'AHL_FMP_MR_IMPLEMENT_STATUS'
AND F2.LOOKUP_CODE = MR.IMPLEMENT_STATUS_CODE
AND F3.LOOKUP_TYPE = 'AHL_FMP_MR_CATEGORY'
AND F3.LOOKUP_CODE = MR.CATEGORY_CODE UNION ALL SELECT UE.UNIT_EFFECTIVITY_ID
, UE.OBJECT_VERSION_NUMBER
, UE.LAST_UPDATE_DATE
, UE.LAST_UPDATED_BY
, UE.CREATION_DATE
, UE.CREATED_BY
, UE.LAST_UPDATE_LOGIN
, UE.MR_INTERVAL_ID
, UE.CSI_ITEM_INSTANCE_ID
, UE.MR_EFFECTIVITY_ID
, UE.MR_HEADER_ID
, AHL_UMP_UTIL_PKG.GET_UNITNAME(UE.CSI_ITEM_INSTANCE_ID) UNIT_NAME
, MTL.CONCATENATED_SEGMENTS PART_NUMBER
, CSI.SERIAL_NUMBER
, CSI.INSTANCE_NUMBER
, UE.EARLIEST_DUE_DATE
, UE.DUE_DATE
, UE.LATEST_DUE_DATE
, UE.STATUS_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_UNIT_EFFECTIVITY_STATUS'
AND LOOKUP_CODE = UE.STATUS_CODE) STATUS
, UE.MESSAGE_CODE
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_UMP_MESSAGE'
AND LOOKUP_CODE = UE.MESSAGE_CODE) MESSAGE
, UE.TOLERANCE_FLAG
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LOOKUP_CODE = UE.TOLERANCE_FLAG) TOLERANCE
, UE.DATE_RUN
, UE.DUE_COUNTER_VALUE
, TO_CHAR(NULL) COUNTER_NAME
, TO_NUMBER(NULL) NET_READING
, UE.REMARKS
, UE.PRECEDING_UE_ID
, UE.FORECAST_SEQUENCE
, UE.REPETITIVE_MR_FLAG
, UE.SET_DUE_DATE
, UE.ACCOMPLISHED_DATE
, (CIT.NAME || '-' || CS.INCIDENT_NUMBER) TITLE
, TO_NUMBER(NULL) VERSION_NUMBER
, CS.SUMMARY DESCRIPTION
, 'NON-ROUTINE' PROGRAM_TYPE_CODE
, F1.MEANING PROGRAM_TYPE
, TO_CHAR(NULL) IMPLEMENT_STATUS_CODE
, TO_CHAR(NULL) IMPLEMENT_STATUS
, TO_CHAR(NULL) CATEGORY_CODE
, TO_CHAR(NULL) CATEGORY
, TO_CHAR(NULL) REVISION
, TO_NUMBER(NULL) SERVICE_LINE_ID
, TO_CHAR(NULL) LINE_NUMBER
, TO_NUMBER(NULL) PROGRAM_MR_HEADER_ID
, UE.CANCEL_REASON_CODE
, TO_CHAR(NULL) PROGRAM_TITLE
, TO_CHAR(NULL) CONTRACT_NUMBER
, TO_CHAR(NULL) CONTRACT_NUMBER_MODIFIER
, (SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B ORIG
, AHL_UE_RELATIONSHIPS REL
WHERE REL.RELATED_UE_ID = UE.UNIT_EFFECTIVITY_ID
AND ORIG.UNIT_EFFECTIVITY_ID = REL.ORIGINATOR_UE_ID
AND ORIG.MR_HEADER_ID = MR.MR_HEADER_ID) ORIGINATOR_TITLE
, (SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B DEP
WHERE DEP.UNIT_EFFECTIVITY_ID = UE.PRECEDING_UE_ID
AND DEP.MR_HEADER_ID = MR.MR_HEADER_ID) DEPENDANT_TITLE
, UE.ATTRIBUTE_CATEGORY
, UE.ATTRIBUTE1
, UE.ATTRIBUTE2
, UE.ATTRIBUTE3
, UE.ATTRIBUTE4
, UE.ATTRIBUTE5
, UE.ATTRIBUTE6
, UE.ATTRIBUTE7
, UE.ATTRIBUTE8
, UE.ATTRIBUTE9
, UE.ATTRIBUTE10
, UE.ATTRIBUTE11
, UE.ATTRIBUTE12
, UE.ATTRIBUTE13
, UE.ATTRIBUTE14
, UE.ATTRIBUTE15
, UE.DEFER_FROM_UE_ID
, (SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR
, AHL_UNIT_EFFECTIVITIES_B DEF
WHERE DEF.UNIT_EFFECTIVITY_ID = UE.DEFER_FROM_UE_ID
AND DEF.MR_HEADER_ID = MR.MR_HEADER_ID) DEFER_FROM_MR_TITLE
, UE.QA_COLLECTION_ID
, CS.INCIDENT_ID CS_INCIDENT_ID
, CS.INCIDENT_NUMBER CS_INCIDENT_NUMBER
, CS.SUMMARY CS_INCIDENT_SUMMARY
, UE.APPLICATION_USG_CODE
, UE.OBJECT_TYPE
FROM FND_LOOKUP_VALUES_VL F1
, CS_INCIDENT_TYPES_VL CIT
, CS_INCIDENTS_ALL_VL CS
, MTL_SYSTEM_ITEMS_KFV MTL
, CSI_ITEM_INSTANCES CSI
, AHL_UNIT_EFFECTIVITIES_VL UE
WHERE UE.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
AND CIT.INCIDENT_TYPE_ID = CS.INCIDENT_TYPE_ID
AND UE.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND UE.OBJECT_TYPE = 'SR'
AND F1.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND F1.LOOKUP_CODE = 'NON-ROUTINE'

Columns