VIEW: APPS.AHL_MR_DETAILS_V

Source

View Text - Preformatted

SELECT MR.MR_HEADER_ID, MR.OBJECT_VERSION_NUMBER, MR.TITLE, MR.VERSION_NUMBER, MR.CATEGORY_CODE, MR.MR_STATUS_CODE, STATUS.MEANING, MR.PROGRAM_TYPE_CODE, PROGTYPE.MEANING, MR.EFFECTIVE_FROM, MR.EFFECTIVE_TO, MR.REVISION, MR_TL.DESCRIPTION, MRR.MR_HEADER_ID, PMR.TITLE, DV.DOCUMENT_ID, DV.DOCUMENT_TITLE, MR.TYPE_CODE, TYPE.MEANING FROM AHL_MR_HEADERS_B MR, AHL_MR_HEADERS_TL MR_TL, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE' AND LANGUAGE = USERENV('LANG')) PROGTYPE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS' AND LANGUAGE = USERENV('LANG')) STATUS, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_MR_TYPE' AND LANGUAGE = USERENV('LANG')) TYPE, AHL_MR_RELATIONSHIPS MRR, AHL_MR_HEADERS_B PMR, ( SELECT ADS.DOCUMENT_ID, ADS.DOCUMENT_TITLE, ADT.ASO_OBJECT_ID FROM AHL_DOCUMENTS_TL ADS, AHL_DOC_TITLE_ASSOS_B ADT, AHL_DOC_REVISIONS_B ADR WHERE ADT.aso_object_type_code = 'MR' AND ADT.DOC_REVISION_ID IS NOT NULL AND ADS.DOCUMENT_ID = ADR.DOCUMENT_ID (+) AND ADS.LANGUAGE = USERENV('LANG') AND ADR.DOC_REVISION_ID = ADT.DOC_REVISION_ID AND ADT.SOURCE_REF_CODE (+) = 'SOURCE' UNION SELECT ADS.DOCUMENT_ID, ADS.DOCUMENT_TITLE, ADT.ASO_OBJECT_ID FROM AHL_DOC_TITLE_ASSOS_B ADT, AHL_DOCUMENTS_TL ADS WHERE ADT.aso_object_type_code = 'MR' AND ADT.DOC_REVISION_ID IS NULL AND ADS.DOCUMENT_ID = ADT.DOCUMENT_ID AND ADS.LANGUAGE = USERENV('LANG') AND ADT.SOURCE_REF_CODE (+) = 'SOURCE' ) DV WHERE MR.MR_HEADER_ID = MR_TL.MR_HEADER_ID And MR_TL.LANGUAGE = USERENV('LANG') And MR.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE') And PROGTYPE.lookup_code = MR.program_type_code And STATUS.lookup_code = MR.mr_status_code And TYPE.lookup_code(+) = MR.type_code and MR.MR_HEADER_ID = MRR.RELATED_MR_HEADER_ID (+) AND MRR.relationship_code (+) = 'PARENT' AND PMR.MR_HEADER_ID (+) = MRR.MR_HEADER_ID AND DV.aso_object_id (+) = MR.MR_HEADER_ID

View Text - HTML Formatted

SELECT MR.MR_HEADER_ID
, MR.OBJECT_VERSION_NUMBER
, MR.TITLE
, MR.VERSION_NUMBER
, MR.CATEGORY_CODE
, MR.MR_STATUS_CODE
, STATUS.MEANING
, MR.PROGRAM_TYPE_CODE
, PROGTYPE.MEANING
, MR.EFFECTIVE_FROM
, MR.EFFECTIVE_TO
, MR.REVISION
, MR_TL.DESCRIPTION
, MRR.MR_HEADER_ID
, PMR.TITLE
, DV.DOCUMENT_ID
, DV.DOCUMENT_TITLE
, MR.TYPE_CODE
, TYPE.MEANING
FROM AHL_MR_HEADERS_B MR
, AHL_MR_HEADERS_TL MR_TL
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND LANGUAGE = USERENV('LANG')) PROGTYPE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
AND LANGUAGE = USERENV('LANG')) STATUS
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_TYPE'
AND LANGUAGE = USERENV('LANG')) TYPE
, AHL_MR_RELATIONSHIPS MRR
, AHL_MR_HEADERS_B PMR
, ( SELECT ADS.DOCUMENT_ID
, ADS.DOCUMENT_TITLE
, ADT.ASO_OBJECT_ID
FROM AHL_DOCUMENTS_TL ADS
, AHL_DOC_TITLE_ASSOS_B ADT
, AHL_DOC_REVISIONS_B ADR
WHERE ADT.ASO_OBJECT_TYPE_CODE = 'MR'
AND ADT.DOC_REVISION_ID IS NOT NULL
AND ADS.DOCUMENT_ID = ADR.DOCUMENT_ID (+)
AND ADS.LANGUAGE = USERENV('LANG')
AND ADR.DOC_REVISION_ID = ADT.DOC_REVISION_ID
AND ADT.SOURCE_REF_CODE (+) = 'SOURCE' UNION SELECT ADS.DOCUMENT_ID
, ADS.DOCUMENT_TITLE
, ADT.ASO_OBJECT_ID
FROM AHL_DOC_TITLE_ASSOS_B ADT
, AHL_DOCUMENTS_TL ADS
WHERE ADT.ASO_OBJECT_TYPE_CODE = 'MR'
AND ADT.DOC_REVISION_ID IS NULL
AND ADS.DOCUMENT_ID = ADT.DOCUMENT_ID
AND ADS.LANGUAGE = USERENV('LANG')
AND ADT.SOURCE_REF_CODE (+) = 'SOURCE' ) DV
WHERE MR.MR_HEADER_ID = MR_TL.MR_HEADER_ID
AND MR_TL.LANGUAGE = USERENV('LANG')
AND MR.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND PROGTYPE.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE
AND STATUS.LOOKUP_CODE = MR.MR_STATUS_CODE
AND TYPE.LOOKUP_CODE(+) = MR.TYPE_CODE
AND MR.MR_HEADER_ID = MRR.RELATED_MR_HEADER_ID (+)
AND MRR.RELATIONSHIP_CODE (+) = 'PARENT'
AND PMR.MR_HEADER_ID (+) = MRR.MR_HEADER_ID
AND DV.ASO_OBJECT_ID (+) = MR.MR_HEADER_ID