VIEW: APPS.AHL_ROUTES_V

Source

View Text - Preformatted

SELECT DISTINCT R.ROUTE_ID, R.OBJECT_VERSION_NUMBER, R.Last_update_date, R.Last_updated_by, R.Creation_date, R.Created_by, R.Last_update_login, R.ROUTE_NO, R.REVISION_NUMBER, R.REVISION_STATUS_CODE, REVST.MEANING, R.SUMMARY_FLAG, R.ENABLED_FLAG, R.START_DATE_ACTIVE, R.END_DATE_ACTIVE, R.OPERATOR_PARTY_ID, OPR.PARTY_NAME, R.SERVICE_ITEM_ID, R.SERVICE_ITEM_ORG_ID, MTL.CONCATENATED_SEGMENTS, R.TASK_TEMPLATE_GROUP_ID, TSK.TEMPLATE_GROUP_NAME, R.ACCOUNTING_CLASS_CODE, R.ACCOUNTING_CLASS_ORG_ID, ACC.DESCRIPTION, R.ROUTE_TYPE_CODE, RTTYPE.MEANING, R.PRODUCT_TYPE_CODE, PDTYPE.MEANING, R.ZONE_CODE, ZONE.MEANING, R.SUB_ZONE_CODE, SUBZONE.MEANING, R.PROCESS_CODE, PROCESS.MEANING, R.APPLICATION_USG_CODE, R.TIME_SPAN, R.QA_INSPECTION_TYPE, QAL.DESCRIPTION, R.SEGMENT1, R.SEGMENT2, R.SEGMENT3, R.SEGMENT4, R.SEGMENT5, R.SEGMENT6, R.SEGMENT7, R.SEGMENT8, R.SEGMENT9, R.SEGMENT10, R.SEGMENT11, R.SEGMENT12, R.SEGMENT13, R.SEGMENT14, R.SEGMENT15, R.CONCATENATED_SEGMENTS, T.TITLE, T.REMARKS, T.REVISION_NOTES, T.APPROVER_NOTE, R.ATTRIBUTE_CATEGORY, R.ATTRIBUTE1, R.ATTRIBUTE2, R.ATTRIBUTE3, R.ATTRIBUTE4, R.ATTRIBUTE5, R.ATTRIBUTE6, R.ATTRIBUTE7, R.ATTRIBUTE8, R.ATTRIBUTE9, R.ATTRIBUTE10, R.ATTRIBUTE11, R.ATTRIBUTE12, R.ATTRIBUTE13, R.ATTRIBUTE14, R.ATTRIBUTE15, NVL(R.UNIT_RECEIPT_UPDATE_FLAG,'N'), ST.MEANING, R.MODEL_CODE, MODELTYPE.MEANING, R.ENIGMA_DOC_ID, R.ENIGMA_ROUTE_ID, R.FILE_ID, R.ENIGMA_PUBLISH_DATE FROM AHL_ROUTES_B_KFV R, AHL_ROUTES_TL T, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_REVISION_STATUS' AND LANGUAGE = USERENV('LANG')) REVST, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_ROUTE_TYPE' AND LANGUAGE = USERENV('LANG')) RTTYPE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'ITEM_TYPE' AND LANGUAGE = USERENV('LANG')) PDTYPE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_ENIGMA_MODEL_CODE' AND LANGUAGE = USERENV('LANG') ) MODELTYPE , (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_ZONE' AND LANGUAGE = USERENV('LANG')) ZONE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_SUB_ZONE' AND LANGUAGE = USERENV('LANG')) SUBZONE, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_PROCESS_CODE' AND LANGUAGE = USERENV('LANG')) PROCESS, (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE' AND LANGUAGE = USERENV('LANG')) ST, HZ_PARTIES OPR, MTL_SYSTEM_ITEMS_KFV MTL, (SELECT B.TASK_TEMPLATE_GROUP_ID, T.TEMPLATE_GROUP_NAME FROM JTF_TASK_TEMP_GROUPS_TL T, JTF_TASK_TEMP_GROUPS_B B WHERE B.TASK_TEMPLATE_GROUP_ID = T.TASK_TEMPLATE_GROUP_ID and T.LANGUAGE = userenv('LANG')) TSK, WIP_ACCOUNTING_CLASSES ACC, (SELECT SHORT_CODE, DESCRIPTION FROM QA_CHAR_VALUE_LOOKUPS QAL where NVL(QAL.CHAR_ID, 87) = 87) QAL WHERE R.ROUTE_ID = T.ROUTE_ID AND T.LANGUAGE = USERENV('LANG') AND R.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE') AND REVST.LOOKUP_CODE = R.REVISION_STATUS_CODE AND RTTYPE.LOOKUP_CODE (+) = R.ROUTE_TYPE_CODE AND PDTYPE.LOOKUP_CODE (+) = R.PRODUCT_TYPE_CODE AND MODELTYPE.LOOKUP_CODE(+) = R.MODEL_CODE AND ZONE.LOOKUP_CODE (+) = R.ZONE_CODE AND SUBZONE.LOOKUP_CODE (+) = R.SUB_ZONE_CODE AND PROCESS.LOOKUP_CODE (+) = R.PROCESS_CODE AND OPR.PARTY_ID (+) = R.OPERATOR_PARTY_ID AND MTL.INVENTORY_ITEM_ID (+) = R.SERVICE_ITEM_ID AND MTL.ORGANIZATION_ID (+) = R.SERVICE_ITEM_ORG_ID AND TSK.TASK_TEMPLATE_GROUP_ID (+) = R.TASK_TEMPLATE_GROUP_ID AND ACC.CLASS_CODE (+) = R.ACCOUNTING_CLASS_CODE AND ACC.ORGANIZATION_ID (+) = R.ACCOUNTING_CLASS_ORG_ID AND QAL.SHORT_CODE (+) = R.QA_INSPECTION_TYPE AND ST.LOOKUP_CODE (+) = NVL(R.UNIT_RECEIPT_UPDATE_FLAG,'N')

View Text - HTML Formatted

SELECT DISTINCT R.ROUTE_ID
, R.OBJECT_VERSION_NUMBER
, R.LAST_UPDATE_DATE
, R.LAST_UPDATED_BY
, R.CREATION_DATE
, R.CREATED_BY
, R.LAST_UPDATE_LOGIN
, R.ROUTE_NO
, R.REVISION_NUMBER
, R.REVISION_STATUS_CODE
, REVST.MEANING
, R.SUMMARY_FLAG
, R.ENABLED_FLAG
, R.START_DATE_ACTIVE
, R.END_DATE_ACTIVE
, R.OPERATOR_PARTY_ID
, OPR.PARTY_NAME
, R.SERVICE_ITEM_ID
, R.SERVICE_ITEM_ORG_ID
, MTL.CONCATENATED_SEGMENTS
, R.TASK_TEMPLATE_GROUP_ID
, TSK.TEMPLATE_GROUP_NAME
, R.ACCOUNTING_CLASS_CODE
, R.ACCOUNTING_CLASS_ORG_ID
, ACC.DESCRIPTION
, R.ROUTE_TYPE_CODE
, RTTYPE.MEANING
, R.PRODUCT_TYPE_CODE
, PDTYPE.MEANING
, R.ZONE_CODE
, ZONE.MEANING
, R.SUB_ZONE_CODE
, SUBZONE.MEANING
, R.PROCESS_CODE
, PROCESS.MEANING
, R.APPLICATION_USG_CODE
, R.TIME_SPAN
, R.QA_INSPECTION_TYPE
, QAL.DESCRIPTION
, R.SEGMENT1
, R.SEGMENT2
, R.SEGMENT3
, R.SEGMENT4
, R.SEGMENT5
, R.SEGMENT6
, R.SEGMENT7
, R.SEGMENT8
, R.SEGMENT9
, R.SEGMENT10
, R.SEGMENT11
, R.SEGMENT12
, R.SEGMENT13
, R.SEGMENT14
, R.SEGMENT15
, R.CONCATENATED_SEGMENTS
, T.TITLE
, T.REMARKS
, T.REVISION_NOTES
, T.APPROVER_NOTE
, R.ATTRIBUTE_CATEGORY
, R.ATTRIBUTE1
, R.ATTRIBUTE2
, R.ATTRIBUTE3
, R.ATTRIBUTE4
, R.ATTRIBUTE5
, R.ATTRIBUTE6
, R.ATTRIBUTE7
, R.ATTRIBUTE8
, R.ATTRIBUTE9
, R.ATTRIBUTE10
, R.ATTRIBUTE11
, R.ATTRIBUTE12
, R.ATTRIBUTE13
, R.ATTRIBUTE14
, R.ATTRIBUTE15
, NVL(R.UNIT_RECEIPT_UPDATE_FLAG
, 'N')
, ST.MEANING
, R.MODEL_CODE
, MODELTYPE.MEANING
, R.ENIGMA_DOC_ID
, R.ENIGMA_ROUTE_ID
, R.FILE_ID
, R.ENIGMA_PUBLISH_DATE
FROM AHL_ROUTES_B_KFV R
, AHL_ROUTES_TL T
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_REVISION_STATUS'
AND LANGUAGE = USERENV('LANG')) REVST
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ROUTE_TYPE'
AND LANGUAGE = USERENV('LANG')) RTTYPE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ITEM_TYPE'
AND LANGUAGE = USERENV('LANG')) PDTYPE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ENIGMA_MODEL_CODE'
AND LANGUAGE = USERENV('LANG') ) MODELTYPE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_ZONE'
AND LANGUAGE = USERENV('LANG')) ZONE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_SUB_ZONE'
AND LANGUAGE = USERENV('LANG')) SUBZONE
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_PROCESS_CODE'
AND LANGUAGE = USERENV('LANG')) PROCESS
, (SELECT LOOKUP_CODE
, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND LANGUAGE = USERENV('LANG')) ST
, HZ_PARTIES OPR
, MTL_SYSTEM_ITEMS_KFV MTL
, (SELECT B.TASK_TEMPLATE_GROUP_ID
, T.TEMPLATE_GROUP_NAME
FROM JTF_TASK_TEMP_GROUPS_TL T
, JTF_TASK_TEMP_GROUPS_B B
WHERE B.TASK_TEMPLATE_GROUP_ID = T.TASK_TEMPLATE_GROUP_ID
AND T.LANGUAGE = USERENV('LANG')) TSK
, WIP_ACCOUNTING_CLASSES ACC
, (SELECT SHORT_CODE
, DESCRIPTION
FROM QA_CHAR_VALUE_LOOKUPS QAL
WHERE NVL(QAL.CHAR_ID
, 87) = 87) QAL
WHERE R.ROUTE_ID = T.ROUTE_ID
AND T.LANGUAGE = USERENV('LANG')
AND R.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND REVST.LOOKUP_CODE = R.REVISION_STATUS_CODE
AND RTTYPE.LOOKUP_CODE (+) = R.ROUTE_TYPE_CODE
AND PDTYPE.LOOKUP_CODE (+) = R.PRODUCT_TYPE_CODE
AND MODELTYPE.LOOKUP_CODE(+) = R.MODEL_CODE
AND ZONE.LOOKUP_CODE (+) = R.ZONE_CODE
AND SUBZONE.LOOKUP_CODE (+) = R.SUB_ZONE_CODE
AND PROCESS.LOOKUP_CODE (+) = R.PROCESS_CODE
AND OPR.PARTY_ID (+) = R.OPERATOR_PARTY_ID
AND MTL.INVENTORY_ITEM_ID (+) = R.SERVICE_ITEM_ID
AND MTL.ORGANIZATION_ID (+) = R.SERVICE_ITEM_ORG_ID
AND TSK.TASK_TEMPLATE_GROUP_ID (+) = R.TASK_TEMPLATE_GROUP_ID
AND ACC.CLASS_CODE (+) = R.ACCOUNTING_CLASS_CODE
AND ACC.ORGANIZATION_ID (+) = R.ACCOUNTING_CLASS_ORG_ID
AND QAL.SHORT_CODE (+) = R.QA_INSPECTION_TYPE
AND ST.LOOKUP_CODE (+) = NVL(R.UNIT_RECEIPT_UPDATE_FLAG
, 'N')