VIEW: APPS.AHL_MR_HEADERS_V

Source

View Text - Preformatted

SELECT MR.MR_HEADER_ID, MR.OBJECT_VERSION_NUMBER, MR.LAST_UPDATE_DATE, MR.LAST_UPDATED_BY, MR.CREATION_DATE, MR.CREATED_BY, MR.LAST_UPDATE_LOGIN, MR.TITLE, MR.VERSION_NUMBER, MR.PRECEDING_MR_HEADER_ID, PMR.TITLE, PMR.REVISION, MR.CATEGORY_CODE, CATEGORY.MEANING, MR.SERVICE_TYPE_CODE, SERVICE.MEANING, MR.MR_STATUS_CODE, STATUS.MEANING, MR.IMPLEMENT_STATUS_CODE, IMPLEMENT.MEANING, MR.REPETITIVE_FLAG, REPETITIVE.MEANING, MR.SHOW_REPETITIVE_CODE, SHOW.MEANING, MR.WHICHEVER_FIRST_CODE, FIRST.MEANING, MR.COPY_ACCOMPLISHMENT_FLAG, COPY.MEANING, MR.PROGRAM_TYPE_CODE, PROGTYPE.MEANING, MR.PROGRAM_SUBTYPE_CODE, SUBTYPE.MEANING, MR.EFFECTIVE_FROM, MR.EFFECTIVE_TO, MR.REVISION, MR_TL.DESCRIPTION, MR_TL.COMMENTS, MR.SERVICE_REQUEST_TEMPLATE_ID, MR.TYPE_CODE, TYPE.MEANING, MR.DOWN_TIME, MR.UOM_CODE, UOM.MEANING, MR.SPACE_CATEGORY_CODE, SPACAT.MEANING, MR.BILLING_ITEM_ID, MR.BILLING_ORG_ID, (SELECT DISTINCT CONCATENATED_SEGMENTS FROM MTL_SYSTEM_ITEMS_KFV WHERE INVENTORY_ITEM_ID=MR.BILLING_ITEM_ID ) BILLING_ITEM, MR.QA_INSPECTION_TYPE, QA.DESCRIPTION, MR.ATTRIBUTE_CATEGORY, MR.ATTRIBUTE1, MR.ATTRIBUTE2, MR.ATTRIBUTE3, MR.ATTRIBUTE4, MR.ATTRIBUTE5, MR.ATTRIBUTE6, MR.ATTRIBUTE7, MR.ATTRIBUTE8, MR.ATTRIBUTE9, MR.ATTRIBUTE10, MR.ATTRIBUTE11, MR.ATTRIBUTE12, MR.ATTRIBUTE13, MR.ATTRIBUTE14, MR.ATTRIBUTE15, MR.APPLICATION_USG_CODE, MR.AUTO_SIGNOFF_FLAG, SIGNOFF.MEANING, MR.COPY_INIT_ACCOMPL_FLAG, (SELECT MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE' AND LOOKUP_CODE = MR.COPY_INIT_ACCOMPL_FLAG) COPY_INIT_ACCOMPLSMNTS, MR.COPY_DEFERRALS_FLAG, (SELECT MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE' AND LOOKUP_CODE = MR.COPY_DEFERRALS_FLAG) COPY_DEFERRALS 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_CATEGORY' AND LANGUAGE = USERENV('LANG')) CATEGORY, (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_MR_PROGRAM_SUBTYPE' AND LANGUAGE = USERENV('LANG')) SUBTYPE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_MR_SERVICE_TYPE' AND LANGUAGE = USERENV('LANG')) SERVICE, (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_IMPLEMENT_STATUS' AND LANGUAGE = USERENV('LANG')) IMPLEMENT, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE' AND LANGUAGE = USERENV('LANG')) REPETITIVE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE' AND LANGUAGE = USERENV('LANG')) SIGNOFF, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_MR_SHOW_REPETITIVE' AND LANGUAGE = USERENV('LANG')) SHOW, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE' AND LANGUAGE = USERENV('LANG')) COPY, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_THRESHOLD_FIRST' AND LANGUAGE = USERENV('LANG')) FIRST, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_PM_DOWNTIME_UOM' AND LANGUAGE = USERENV('LANG')) UOM, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_FMP_MR_TYPE' AND LANGUAGE = USERENV('LANG')) TYPE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_LTP_SPACE_CATEGORY' AND LANGUAGE = USERENV('LANG')) SPACAT, (SELECT SHORT_CODE, DESCRIPTION FROM QA_CHAR_VALUE_LOOKUPS QAL where NVL(QAL.CHAR_ID, 87) = 87) QA, AHL_MR_HEADERS_B PMR 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 CATEGORY.lookup_code = MR.category_code And PROGTYPE.lookup_code = MR.program_type_code And STATUS.lookup_code = MR.mr_status_code And IMPLEMENT.lookup_code = MR.implement_status_code And REPETITIVE.lookup_code = MR.repetitive_flag And SHOW.lookup_code = MR.show_repetitive_code And COPY.lookup_code = MR.COPY_ACCOMPLISHMENT_FLAG And FIRST.lookup_code = MR.WHICHEVER_FIRST_CODE And SERVICE.lookup_code(+) = MR.service_type_code And SUBTYPE.lookup_code(+) = MR.program_subtype_code And SIGNOFF.lookup_code(+) =MR.AUTO_SIGNOFF_FLAG And TYPE.lookup_code(+) = MR.type_code And UOM.lookup_code(+) = MR.UOM_CODE And SPACAT.lookup_code(+) = MR.SPACE_CATEGORY_CODE And PMR.MR_HEADER_ID(+) = MR.PRECEDING_MR_HEADER_ID AND QA.SHORT_CODE(+) = MR.QA_INSPECTION_TYPE

View Text - HTML Formatted

SELECT MR.MR_HEADER_ID
, MR.OBJECT_VERSION_NUMBER
, MR.LAST_UPDATE_DATE
, MR.LAST_UPDATED_BY
, MR.CREATION_DATE
, MR.CREATED_BY
, MR.LAST_UPDATE_LOGIN
, MR.TITLE
, MR.VERSION_NUMBER
, MR.PRECEDING_MR_HEADER_ID
, PMR.TITLE
, PMR.REVISION
, MR.CATEGORY_CODE
, CATEGORY.MEANING
, MR.SERVICE_TYPE_CODE
, SERVICE.MEANING
, MR.MR_STATUS_CODE
, STATUS.MEANING
, MR.IMPLEMENT_STATUS_CODE
, IMPLEMENT.MEANING
, MR.REPETITIVE_FLAG
, REPETITIVE.MEANING
, MR.SHOW_REPETITIVE_CODE
, SHOW.MEANING
, MR.WHICHEVER_FIRST_CODE
, FIRST.MEANING
, MR.COPY_ACCOMPLISHMENT_FLAG
, COPY.MEANING
, MR.PROGRAM_TYPE_CODE
, PROGTYPE.MEANING
, MR.PROGRAM_SUBTYPE_CODE
, SUBTYPE.MEANING
, MR.EFFECTIVE_FROM
, MR.EFFECTIVE_TO
, MR.REVISION
, MR_TL.DESCRIPTION
, MR_TL.COMMENTS
, MR.SERVICE_REQUEST_TEMPLATE_ID
, MR.TYPE_CODE
, TYPE.MEANING
, MR.DOWN_TIME
, MR.UOM_CODE
, UOM.MEANING
, MR.SPACE_CATEGORY_CODE
, SPACAT.MEANING
, MR.BILLING_ITEM_ID
, MR.BILLING_ORG_ID
, (SELECT DISTINCT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID=MR.BILLING_ITEM_ID ) BILLING_ITEM
, MR.QA_INSPECTION_TYPE
, QA.DESCRIPTION
, MR.ATTRIBUTE_CATEGORY
, MR.ATTRIBUTE1
, MR.ATTRIBUTE2
, MR.ATTRIBUTE3
, MR.ATTRIBUTE4
, MR.ATTRIBUTE5
, MR.ATTRIBUTE6
, MR.ATTRIBUTE7
, MR.ATTRIBUTE8
, MR.ATTRIBUTE9
, MR.ATTRIBUTE10
, MR.ATTRIBUTE11
, MR.ATTRIBUTE12
, MR.ATTRIBUTE13
, MR.ATTRIBUTE14
, MR.ATTRIBUTE15
, MR.APPLICATION_USG_CODE
, MR.AUTO_SIGNOFF_FLAG
, SIGNOFF.MEANING
, MR.COPY_INIT_ACCOMPL_FLAG
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LOOKUP_CODE = MR.COPY_INIT_ACCOMPL_FLAG) COPY_INIT_ACCOMPLSMNTS
, MR.COPY_DEFERRALS_FLAG
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LOOKUP_CODE = MR.COPY_DEFERRALS_FLAG) COPY_DEFERRALS
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_CATEGORY'
AND LANGUAGE = USERENV('LANG')) CATEGORY
, (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_MR_PROGRAM_SUBTYPE'
AND LANGUAGE = USERENV('LANG')) SUBTYPE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_SERVICE_TYPE'
AND LANGUAGE = USERENV('LANG')) SERVICE
, (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_IMPLEMENT_STATUS'
AND LANGUAGE = USERENV('LANG')) IMPLEMENT
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LANGUAGE = USERENV('LANG')) REPETITIVE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LANGUAGE = USERENV('LANG')) SIGNOFF
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_SHOW_REPETITIVE'
AND LANGUAGE = USERENV('LANG')) SHOW
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LANGUAGE = USERENV('LANG')) COPY
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_THRESHOLD_FIRST'
AND LANGUAGE = USERENV('LANG')) FIRST
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_PM_DOWNTIME_UOM'
AND LANGUAGE = USERENV('LANG')) UOM
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_TYPE'
AND LANGUAGE = USERENV('LANG')) TYPE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_LTP_SPACE_CATEGORY'
AND LANGUAGE = USERENV('LANG')) SPACAT
, (SELECT SHORT_CODE
, DESCRIPTION
FROM QA_CHAR_VALUE_LOOKUPS QAL
WHERE NVL(QAL.CHAR_ID
, 87) = 87) QA
, AHL_MR_HEADERS_B PMR
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 CATEGORY.LOOKUP_CODE = MR.CATEGORY_CODE
AND PROGTYPE.LOOKUP_CODE = MR.PROGRAM_TYPE_CODE
AND STATUS.LOOKUP_CODE = MR.MR_STATUS_CODE
AND IMPLEMENT.LOOKUP_CODE = MR.IMPLEMENT_STATUS_CODE
AND REPETITIVE.LOOKUP_CODE = MR.REPETITIVE_FLAG
AND SHOW.LOOKUP_CODE = MR.SHOW_REPETITIVE_CODE
AND COPY.LOOKUP_CODE = MR.COPY_ACCOMPLISHMENT_FLAG
AND FIRST.LOOKUP_CODE = MR.WHICHEVER_FIRST_CODE
AND SERVICE.LOOKUP_CODE(+) = MR.SERVICE_TYPE_CODE
AND SUBTYPE.LOOKUP_CODE(+) = MR.PROGRAM_SUBTYPE_CODE
AND SIGNOFF.LOOKUP_CODE(+) =MR.AUTO_SIGNOFF_FLAG
AND TYPE.LOOKUP_CODE(+) = MR.TYPE_CODE
AND UOM.LOOKUP_CODE(+) = MR.UOM_CODE
AND SPACAT.LOOKUP_CODE(+) = MR.SPACE_CATEGORY_CODE
AND PMR.MR_HEADER_ID(+) = MR.PRECEDING_MR_HEADER_ID
AND QA.SHORT_CODE(+) = MR.QA_INSPECTION_TYPE