VIEW: APPS.EDW_LOOKUP_CODE_FKV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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