View: EDW_LOOKUP_CODES_LCV

View Text

SELECT LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, LOOKUP_ID
, LOOKUP_CODES_DP
, NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, TABLE_CODE
, INSTANCE
, MAX(LAST_UPDATE_DATE)
, MAX(CREATION_DATE)
, USER_ATTRIBUTE1
, USER_ATTRIBUTE2
, USER_ATTRIBUTE3
, USER_ATTRIBUTE4
, USER_ATTRIBUTE5
FROM (SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' || 'PO' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(MEANING
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(MEANING
, 1
, 80) NAME
, DESCRIPTION
, PLC.START_DATE_ACTIVE START_DATE_ACTIVE
, PLC.END_DATE_ACTIVE END_DATE_ACTIVE
, 'PO' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, PLC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PLC.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM FND_LOOKUP_VALUES PLC
, EDW_LOCAL_INSTANCE ELI
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 PLC.LANGUAGE = USERENV('LANG')
AND PLC.VIEW_APPLICATION_ID = 201
AND PLC.SECURITY_GROUP_ID = 0 /* MFG_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' || 'MFG' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(MEANING
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(MEANING
, 1
, 80) NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, 'MFG' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, MFL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MFL.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM FND_LOOKUP_VALUES MFL
, EDW_LOCAL_INSTANCE ELI
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 MFL.LANGUAGE = USERENV('LANG')
AND MFL.VIEW_APPLICATION_ID = 700
AND MFL.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
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(MEANING
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(MEANING
, 1
, 80) NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, 'MFG' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, MFL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MFL.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM FND_LOOKUP_VALUES MFL
, EDW_LOCAL_INSTANCE ELI
WHERE LOOKUP_TYPE IN ('QA_INSPECTION_POINT')
AND MFL.LANGUAGE = USERENV('LANG')
AND MFL.VIEW_APPLICATION_ID = 3
AND MFL.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
, TO_NUMBER(NULL) LOOKUP_ID
, COST_MTHD_CODE LOOKUP_CODES_DP
, COST_MTHD_CODE NAME
, COST_MTHD_DESC DESCRIPTION
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, 'OPM' TABLE_CODE
, INSTANCE_CODE INSTANCE
, MTHD.LAST_UPDATE_DATE
, MTHD.CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM CM_MTHD_MST MTHD
, EDW_LOCAL_INSTANCE ELI /* RCV_ROUTING EXTRACT */ UNION ALL SELECT DISTINCT(UPPER(ROUTING_NAME)) || '-' || 'RCV ROUTING' || '-' || 'ROU' LOOKUP_CODE_PK
, 'RCV ROUTING' LOOKUP_TYPE
, ROUTING_NAME LOOKUP_CODE
, ROUTING_HEADER_ID LOOKUP_ID
, SUBSTRB(ROUTING_NAME
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(ROUTING_NAME
, 1
, 80) NAME
, DESCRIPTION
, RRH.CREATION_DATE START_DATE_ACTIVE
, RRH.CREATION_DATE END_DATE_ACTIVE
, 'ROU' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, RRH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RRH.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM RCV_ROUTING_HEADERS RRH
, EDW_LOCAL_INSTANCE ELI /* SO_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' ||'SOL' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(MEANING
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(MEANING
, 1
, 80) NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, 'SOL' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, SO.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SYSDATE-1 CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM FND_LOOKUP_VALUES SO
, EDW_LOCAL_INSTANCE ELI
WHERE LOOKUP_TYPE IN ( 'SALES_CHANNEL'
, 'ORDER_CATEGORY'
, 'CANCEL_CODE')
AND SO.LANGUAGE = USERENV('LANG')
AND SO.VIEW_APPLICATION_ID = 660
AND SO.SECURITY_GROUP_ID = 0 /* QPL_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' ||'QPL' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(MEANING
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(MEANING
, 1
, 80) NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, 'QPL' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, SO.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SYSDATE-1 CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM FND_LOOKUP_VALUES SO
, EDW_LOCAL_INSTANCE ELI
WHERE LOOKUP_TYPE IN ( 'QP_AGREEMENT_TYPE')
AND SO.LANGUAGE = USERENV('LANG')
AND SO.VIEW_APPLICATION_ID = 661
AND SO.SECURITY_GROUP_ID = 0 /* AR_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) || '-' || UPPER(LOOKUP_TYPE) || '-' ||'AR' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(MEANING
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(MEANING
, 1
, 80) NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, 'AR' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, AR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AR.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM FND_LOOKUP_VALUES AR
, EDW_LOCAL_INSTANCE ELI
WHERE LOOKUP_TYPE IN ( 'FOB'
, 'CREDIT_MEMO_REASON')
AND AR.LANGUAGE = USERENV('LANG')
AND AR.VIEW_APPLICATION_ID = 222
AND AR.SECURITY_GROUP_ID = 0 /* PO_DOCUMENT TYPES EXTRACT */ UNION ALL SELECT DISTINCT(UPPER(PDT.DOCUMENT_SUBTYPE) || '-' || 'AGREEMENT TYPE' || '-' || 'POD') LOOKUP_CODE_PK
, 'AGREEMENT TYPE' LOOKUP_TYPE
, PDT.DOCUMENT_SUBTYPE LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(T.TYPE_NAME
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(T.TYPE_NAME
, 1
, 80) NAME
, T.TYPE_NAME DESCRIPTION
, SYSDATE-1 START_DATE_ACTIVE
, SYSDATE-1 END_DATE_ACTIVE
, 'POD' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, PDT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PDT.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM PO_DOCUMENT_TYPES_ALL_B PDT
, PO_DOCUMENT_TYPES_ALL_TL T
, EDW_LOCAL_INSTANCE ELI
WHERE PDT.DOCUMENT_TYPE_CODE IN ( 'PO'
, 'PA')
AND PDT.DOCUMENT_SUBTYPE IN ( 'CONTRACT'
, 'PLANNED'
, 'STANDARD'
, 'BLANKET')
AND PDT.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
AND PDT.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
AND NVL(PDT.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
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(DESCRIPTION
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(DESCRIPTION
, 1
, 80) NAME
, DESCRIPTION
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, 'ORG' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, OFR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OFR.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM ORG_FREIGHT_TL OFR
, EDW_LOCAL_INSTANCE ELI
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
, REASON_ID LOOKUP_ID
, SUBSTRB(REASON_NAME
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(REASON_NAME
, 1
, 80) NAME
, DESCRIPTION
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, 'MTR' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, MTR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MTR.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM MTL_TRANSACTION_REASONS MTR
, EDW_LOCAL_INSTANCE ELI /* FND_LOOKUPS EXTRACT */ UNION ALL SELECT UPPER(LOOKUP_CODE) ||'-'|| UPPER(LOOKUP_TYPE) ||'-'||'FND' LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, MEANING LOOKUP_CODES_DP
, MEANING NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, 'FND' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, FL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SYSDATE-1 CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM FND_LOOKUP_VALUES FL
, EDW_LOCAL_INSTANCE ELI
WHERE LOOKUP_TYPE = 'YES_NO'
AND FL.LANGUAGE = USERENV('LANG')
AND FL.VIEW_APPLICATION_ID = 0
AND FL.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
, TO_NUMBER(NULL) LOOKUP_ID
, CODE LOOKUP_CODES_DP
, CODE NAME
, DESCRIPTION
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, 'POQ' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, PQ.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TO_DATE(NULL) CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM PO_QUALITY_CODES PQ
, EDW_LOCAL_INSTANCE ELI UNION ALL SELECT DISTINCT(UPPER(NAME))|| '-' || 'ORDER_TYPE'|| '-' || 'SOO' LOOKUP_CODE_PK
, 'ORDER_TYPE' LOOKUP_TYPE
, UPPER(NAME) LOOKUP_CODE
, TRANSACTION_TYPE_ID LOOKUP_ID
, NAME LOOKUP_CODES_DP
, NAME NAME
, DESCRIPTION DESCRIPTION
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, 'SOO' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, SOO.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SYSDATE-1 CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM OE_TRANSACTION_TYPES_TL SOO
, EDW_LOCAL_INSTANCE ELI
WHERE LANGUAGE = USERENV('LANG') UNION ALL SELECT DISTINCT(UPPER(NAME))|| '-' || 'ORDER_SOURCE'|| '-' || 'SOS' LOOKUP_CODE_PK
, 'ORDER_SOURCE' LOOKUP_TYPE
, UPPER(NAME) LOOKUP_CODE
, ORDER_SOURCE_ID LOOKUP_ID
, NAME LOOKUP_CODES_DP
, NAME NAME
, NVL(DESCRIPTION
, NAME) DESCRIPTION
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, 'SOS' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, SOS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SOS.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM OE_ORDER_SOURCES SOS
, EDW_LOCAL_INSTANCE ELI UNION ALL SELECT UPPER(GEM.LOOKUP_CODE)||'-'||UPPER(GEM.LOOKUP_TYPE)||'-'|| 'OPM' LOOKUP_CODE_PK
, GEM.LOOKUP_TYPE
, GEM.LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, SUBSTRB(GEM.MEANING
, 1
, 80) LOOKUP_CODES_DP
, SUBSTRB(GEM.MEANING
, 1
, 80) NAME
, GEM.DESCRIPTION
, GEM.START_DATE_ACTIVE START_DATE_ACTIVE
, GEM.END_DATE_ACTIVE END_DATE_ACTIVE
, 'OPM' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, GEM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, GEM.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM GEM_LOOKUP_VALUES GEM
, EDW_LOCAL_INSTANCE ELI UNION ALL SELECT UPPER(D.DOC_TYPE) || '-' || 'DOC TYPE' || '-' || 'OPM' LOOKUP_CODE_PK
, 'DOC TYPE' || '-' || 'OPM' LOOKUP_TYPE
, D.DOC_TYPE LOOKUP_CODE
, TO_NUMBER(NULL) LOOKUP_ID
, D.DOC_DESC LOOKUP_CODES_DP
, D.DOC_DESC NAME
, D.DOC_DESC DESCRIPTION
, D.CREATION_DATE START_DATE_ACTIVE
, D.CREATION_DATE END_DATE_ACTIVE
, 'OPM' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, D.LAST_UPDATE_DATE LAST_UPDATE_DATE
, D.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_DOCS_MST D
, EDW_LOCAL_INSTANCE ELI 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
, TO_NUMBER(NULL) LOOKUP_ID
, R.REASON_DESC1 LOOKUP_CODES_DP
, R.REASON_DESC1 NAME
, R.REASON_DESC1 DESCRIPTION
, R.CREATION_DATE START_DATE_ACTIVE
, R.CREATION_DATE END_DATE_ACTIVE
, 'OPM' TABLE_CODE
, ELI.INSTANCE_CODE INSTANCE
, R.LAST_UPDATE_DATE LAST_UPDATE_DATE
, R.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_REAS_CDS R
, EDW_LOCAL_INSTANCE ELI ) GROUP BY LOOKUP_CODE_PK
, LOOKUP_TYPE
, LOOKUP_CODE
, LOOKUP_ID
, LOOKUP_CODES_DP
, NAME
, DESCRIPTION
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, TABLE_CODE
, INSTANCE
, USER_ATTRIBUTE1
, USER_ATTRIBUTE2
, USER_ATTRIBUTE3
, USER_ATTRIBUTE4
, USER_ATTRIBUTE5

Columns