[Home] [Help]
View: EDW_LOOKUP_CODE_FKV
View Text
SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' || 'PO' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, 'PO' TABLE_CODE
FROM FND_LOOKUP_VALUES LV
WHERE LOOKUP_TYPE IN ( 'RCV TRANSACTION TYPE'
, 'INSPECTION STATUS'
, 'PRICE TYPE'
, 'TRANSACTION REASON'
, 'DOCUMENT STATE'
, 'DESTINATION TYPE'
, 'RCV DESTINATION TYPE'
, 'AUTHORIZATION STATUS'
, 'PRICE BREAK TYPE'
, 'ORDER TYPE'
, 'SHIPMENT TYPE'
, 'PRICE TYPE LOOKUP CODE'
, 'FREIGHT TERMS'
, 'VENDOR RECEIPT OPTION'
, 'CUSTOM MEASURE'
, 'SATISFACTION CRITERIA'
, 'SHIPMENT SOURCE TYPE'
, 'FOB'
, 'PURCHASE CLASSIFICATION')
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 201
AND SECURITY_GROUP_ID = 0 /* MFG_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' || 'MFG' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, 'MFG' TABLE_CODE
FROM FND_LOOKUP_VALUES LV
WHERE LOOKUP_TYPE IN ('MRP_ORDER_TYPE'
, 'WIP_DISCRETE_JOB'
, 'WIP_JOB_STATUS'
, 'WIP_FLOW_SCHEDULE_STATUS'
, 'MTL_PLANNING_MAKE_BUY'
, 'MTL_ENG_QUANTITY'
, 'MTL_LOT_CONTROL'
, 'MTL_LOCATION_CONTROL'
, 'MTL_SERIAL_CONTROL'
, 'MTL_MATERIAL_PLANNING'
, 'MTL_SHELF_LIFE'
, 'MRP_WIP_SCHEDULE_TYPE'
, 'WIP_ENTITY'
, 'MRP_DEMAND_ORIGINATION')
AND LV.LANGUAGE = USERENV('LANG')
AND LV.VIEW_APPLICATION_ID = 700
AND LV.SECURITY_GROUP_ID = 0 /* MFG_LOOKUPS EXTRACT (FOR THE LOOKUP_TYPE 'QA_INSPECTION_POINT') */ /*
AND VIEW_APPLICATION_ID = 3 (FOR THE REQUEST
FROM MBI TEAM)*/ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' || 'MFG' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, 'MFG' TABLE_CODE
FROM FND_LOOKUP_VALUES LV
WHERE LOOKUP_TYPE IN ('QA_INSPECTION_POINT')
AND LV.LANGUAGE = USERENV('LANG')
AND LV.VIEW_APPLICATION_ID = 3
AND LV.SECURITY_GROUP_ID = 0 /* 'OPI_OPM_COST_METHOD' FOR OPI */ UNION ALL SELECT UPPER(COST_MTHD_CODE) || '-' || 'OPI_OPM_COST_METHOD' || '-' || 'OPM' LOOKUP_CODE_PK
, 'OPI_OPM_COST_METHOD' LOOKUP_TYPE
, COST_MTHD_CODE LOOKUP_CODE
, 'OPM' TABLE_CODE
FROM CM_MTHD_MST /* RCV_ROUTING EXTRACT */ UNION ALL SELECT DISTINCT(UPPER(ROUTING_NAME)) || '-' || 'RCV ROUTING' || '-' || 'ROU' LOOKUP_CODE_PK
, 'RCV ROUTING' LOOKUP_TYPE
, ROUTING_NAME LOOKUP_CODE
, 'ROU' TABLE_CODE
FROM RCV_ROUTING_HEADERS RRH /* SO_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' ||'SOL' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, 'SOL' TABLE_CODE
FROM FND_LOOKUP_VALUES LV
WHERE LOOKUP_TYPE IN ( 'SALES_CHANNEL'
, 'ORDER_CATEGORY'
, 'CANCEL_CODE'
, 'AGREEMENT_TYPE')
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID = 0 /* QPL_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' ||'QPL' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, 'QPL' TABLE_CODE
FROM FND_LOOKUP_VALUES LV
WHERE LOOKUP_TYPE IN ( 'QP_AGREEMENT_TYPE')
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 661
AND SECURITY_GROUP_ID = 0 /* AR_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' ||'AR' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, 'AR' TABLE_CODE
FROM FND_LOOKUP_VALUES LV
WHERE LOOKUP_TYPE IN ( 'FOB'
, 'CREDIT_MEMO_REASON')
AND LV.LANGUAGE = USERENV('LANG')
AND LV.VIEW_APPLICATION_ID = 222
AND LV.SECURITY_GROUP_ID = 0 /* PO_DOCUMENT TYPES EXTRACT */ UNION ALL SELECT DISTINCT(UPPER(B.DOCUMENT_SUBTYPE) || '-' || 'AGREEMENT TYPE' || '-' || 'POD') LOOKUP_CODE_PK
, 'AGREEMENT TYPE' LOOKUP_TYPE
, B.DOCUMENT_SUBTYPE LOOKUP_CODE
, 'POD' TABLE_CODE /*
FROM PO_DOCUMENT_TYPES_ALL PDT */
FROM PO_DOCUMENT_TYPES_ALL_B B
, PO_DOCUMENT_TYPES_ALL_TL T
WHERE B.DOCUMENT_TYPE_CODE IN ( 'PO'
, 'PA')
AND B.DOCUMENT_SUBTYPE IN ( 'CONTRACT'
, 'PLANNED'
, 'STANDARD'
, 'BLANKET')
AND B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
AND NVL(B.ORG_ID
, -99) = NVL(T.ORG_ID
, -99)
AND T.LANGUAGE = USERENV('LANG') /* ORG_FREIGHT EXTRACT */ UNION ALL SELECT DISTINCT(UPPER(FREIGHT_CODE)) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG' LOOKUP_CODE_PK
, 'SHIP VIA TYPE' LOOKUP_TYPE
, FREIGHT_CODE LOOKUP_CODE
, 'ORG' TABLE_CODE /*
FROM ORG_FREIGHT OFR */
FROM ORG_FREIGHT_TL OFR
WHERE ORGANIZATION_ID IN (SELECT MAX(ORGANIZATION_ID)
FROM ORG_FREIGHT_TL OF2
WHERE OF2.FREIGHT_CODE = OFR.FREIGHT_CODE)
AND LANGUAGE = USERENV('LANG') /* MTL TRANSACTIONS EXTRACT */ UNION ALL SELECT DISTINCT(UPPER(REASON_NAME))|| '-' || 'TXN REASON'|| '-' || 'MTR' LOOKUP_CODE_PK
, 'TXN REASON' LOOKUP_TYPE
, UPPER(REASON_NAME) LOOKUP_CODE
, 'MTR' TABLE_CODE
FROM MTL_TRANSACTION_REASONS MTR /* FND_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) ||'-'|| UPPER(LOOKUP_TYPE) ||'-'||'FND' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE LOOKUP_CODE
, 'FND' TABLE_CODE
FROM FND_LOOKUP_VALUES LV
WHERE LOOKUP_TYPE = 'YES_NO'
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 0
AND SECURITY_GROUP_ID = 0 /* MTL TRANSACTIONS EXTRACT */ UNION ALL SELECT DISTINCT(UPPER(CODE))|| '-' || 'PO QUALITY CODE'|| '-' || 'POQ' LOOKUP_CODE_PK
, 'PO QUALITY CODE' LOOKUP_TYPE
, UPPER(CODE) LOOKUP_CODE
, 'POQ' TABLE_CODE
FROM PO_QUALITY_CODES PQ UNION ALL SELECT DISTINCT(UPPER(NAME))|| '-' || 'ORDER_TYPE'|| '-' || 'SOO' LOOKUP_CODES_PK
, 'ORDER_TYPE' LOOKUP_TYPE
, UPPER(NAME) LOOKUP_CODE
, 'SOO' TABLE_CODE
FROM OE_TRANSACTION_TYPES_TL SOO
WHERE LANGUAGE = USERENV('LANG') UNION ALL SELECT DISTINCT(UPPER(NAME))|| '-' || 'ORDER_SOURCE'|| '-' || 'SOS' LOOKUP_CODES_PK
, 'ORDER_SOURCE' LOOKUP_TYPE
, UPPER(NAME) LOOKUP_CODE
, 'SOS' TABLE_CODE
FROM OE_ORDER_SOURCES SOS UNION ALL SELECT UPPER(GEM.LOOKUP_CODE)||'-'||UPPER(GEM.LOOKUP_TYPE)||'-'|| 'OPM' LOOKUP_CODE_PK
, GEM.LOOKUP_TYPE
, GEM.LOOKUP_CODE
, 'OPM' TABLE_CODE
FROM GEM_LOOKUP_VALUES GEM UNION ALL SELECT UPPER(D.DOC_TYPE) || '-' || 'DOC TYPE' || '-' ||'OPM' LOOKUP_CODE_PK
, 'DOC TYPE' || '-' || 'OPM' LOOKUP_TYPE
, D.DOC_TYPE LOOKUP_CODE
, 'OPM' TABLE_CODE
FROM SY_DOCS_MST D UNION ALL SELECT UPPER(R.REASON_CODE) || '-' || 'GMA REASON CD' || '-' || 'OPM' LOOKUP_CODE_PK
, 'GMA REASON CD' || '-' || 'OPM' LOOKUP_TYPE
, R.REASON_CODE LOOKUP_CODE
, 'OPM' TABLE_CODE
FROM SY_REAS_CDS R
Columns
| Name |
| LOOKUP_CODE_PK |
| LOOKUP_TYPE |
| LOOKUP_CODE |
| TABLE_CODE |
APPS.EDW_LOOKUP_CODE_FKV