eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.EDW_LOOKUP_CODES_LCV

Source

View Text - Preformatted


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

View Text - HTML Formatted

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