Product: | AHL - Complex Maintenance Repair and Overhaul |
---|---|
Description: | View based on join of AHL_MR_DETAILS_V, AHL_MR_EFFECTIVITIES to include the PC_NODE_ID defined in the MR effectivities |
Implementation/DBA Data: | APPS.AHL_MR_PC_NODES_V |
SELECT MR.MR_HEADER_ID
, MR.OBJECT_VERSION_NUMBER
, MR.TITLE
, MR.VERSION_NUMBER
, MR.MR_STATUS_CODE
, STATUS.MEANING STATUS
, MR.PROGRAM_TYPE_CODE
, PROGTYPE.MEANING PROGRAM_TYPE
, MR.PROGRAM_SUBTYPE_CODE
, SUBTYPE.MEANING PROGRAM_SUBTYPE
, MR.EFFECTIVE_FROM
, MR.EFFECTIVE_TO
, MR.REVISION
, MR.DESCRIPTION
, EF.PC_NODE_ID
FROM AHL_MR_HEADERS_APP_V MR
, FND_LOOKUP_VALUES_VL PROGTYPE
, FND_LOOKUP_VALUES_VL SUBTYPE
, FND_LOOKUP_VALUES_VL STATUS
, AHL_MR_EFFECTIVITIES EF
WHERE PROGTYPE.LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND PROGTYPE.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE
AND SUBTYPE.LOOKUP_TYPE (+) = 'AHL_FMP_MR_PROGRAM_SUBTYPE'
AND SUBTYPE.LOOKUP_CODE (+) = MR.PROGRAM_SUBTYPE_CODE
AND STATUS.LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
AND STATUS.LOOKUP_CODE = MR.MR_STATUS_CODE
AND MR.MR_HEADER_ID = EF.MR_HEADER_ID
AND SYSDATE <= NVL(MR.EFFECTIVE_TO
, SYSDATE)