A 'Lookup' query to find cross-references to Lookup TYPE, CODE and MEANING

Oracle Certification, Database Administration, SQL, Application, Programming Reference Books A 'Lookup' query to find cross-references to Lookup TYPE, CODE and MEANING

Sometimes you need to be able to look up what a quickcode means, or what quickcode will return a particular meaning. The query below has been 'crippled' for use on a system where user 'queryit' does not have access to APPLSYS tables. If you DO have access to these tables in your system, remove the '--' comment line designators accordingly
(just the doubled lines, the '----' comment tags should remain in place).

Note that this is from an Oracle Manufacturing system. Your table names may differ!
This is an example of a UNION query that you can use to add more or different tables to your LOOKUP.

The user receives 3 prompts, where he can enter answers to the LOOKUP_TYPE, LOOKUP_CODE or
LOOKUP_MEANING. (Partial values are accepted for TYPE and MEANING; CODE searches for an exact match, as stated in the PROMPT below.)

SET linesize 130

PROMPT * Look for Lookup 'TYPE', 'CODE', 'MEANING' combinations in the following tables:
PROMPT * (not) APPLSYS.ALR_LOOKUPS,
PROMPT * (not) APPLSYS.FND_COMMON_LOOKUPS,
PROMPT * (not) APPLSYS.FND_LOOKUP_VALUES,
PROMPT * (not) APPLSYS.WF_LOOKUPS_TL,
PROMPT * APPS.MH_FND_LOOKUP_VALUES,
PROMPT * AR.AR_LOOKUPS,
PROMPT * CN.CN_LOOKUPS_ALL,
PROMPT * FA.FA_LOOKUPS,
PROMPT * HR.FF_LOOKUPS,
PROMPT * HR.HR_S_COMMON_LOOKUPS,
PROMPT * INV.MFG_LOOKUPS,
PROMPT * OE.SO_LOOKUPS,
PROMPT * OSM.AS_LOOKUPS,
PROMPT * PA.PA_LOOKUPS,
PROMPT * RG.RG_LOOKUPS
PROMPT
PROMPT * Enter any combination of 'TYPE', 'CODE' or 'MEANING' substrings at the prompts.
PROMPT * NOTE: You must enter any 'code' EXACTLY, OR with '%' or '_' wildcards.
PROMPT

COLUMN "Table" FORMAT A30
COLUMN "Type" FORMAT A30
COLUMN "Code" FORMAT A10 WORD
COLUMN "Meaning" FORMAT A30 WORD

BREAK ON "Table" -
ON "Type"

ACCEPT v_type PROMPT "Enter a lookup 'TYPE' or substring (default is '%'): "
ACCEPT v_code PROMPT "Enter a lookup 'CODE' or substring (default is '%'): "
ACCEPT v_mean PROMPT "Enter a 'MEANING' or substring (default is '%'): "

----Look in INV.MFG_LOOKUPS
SELECT 'inv.mfg_lookups'   "Table"
, lookup_type    "Type"
, TO_CHAR( lookup_code)   "Code"
, RTRIM( meaning)   "Meaning"
FROM inv.mfg_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
--UNION
----Look in APPLSYS.ALR_LOOKUPS
--SELECT 'applsys.alr_lookups'   "Table"
-- , lookup_type    "Type"
-- , lookup_code    "Code"
-- , RTRIM( meaning)   "Meaning"
--FROM applsys.alr_lookups
--WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
--AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
--AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
--UNION
----Look in APPLSYS.FND_COMMON_LOOKUPS
--SELECT 'applsys.fnd_common_lookups' "Table"
-- , lookup_type    "Type"
-- , lookup_code    "Code"
-- , RTRIM( meaning)   "Meaning"
--FROM applsys.fnd_common_lookups
--WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
--AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
--AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
--UNION
----Look in APPLSYS.WF_LOOKUPS_TL
--SELECT 'applsys.wf_lookups_tl'   "Table"
-- , lookup_type    "Type"
-- , lookup_code    "Code"
-- , RTRIM( meaning)   "Meaning"
--FROM applsys.wf_lookups_tl
--WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
--AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
--AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
--UNION
----Look in APPLSYS.FND_LOOKUP_VALUES
--SELECT 'applsys.fnd_lookup_values' "Table"
-- , lookup_type    "Type"
-- , lookup_code    "Code"
-- , RTRIM( meaning)   "Meaning"
--FROM applsys.fnd_lookup_values
--WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
--AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
--AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in HR.HR_S_COMMON_LOOKUPS
SELECT 'hr.hr_s_common_lookups' "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM hr.hr_s_common_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in HR.FF_LOOKUPS
SELECT 'hr.ff_lookups'    "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM hr.ff_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in CN.CN_LOOKUPS_ALL
SELECT 'cn.cn_lookups_all'   "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM cn.cn_lookups_all
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in APPS.MH_FND_LOOKUP_VALUES
SELECT 'apps.mh_fnd_lookup_values' "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM apps.mh_fnd_lookup_values
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in AR.AR_LOOKUPS
SELECT 'ar.ar_lookups'    "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM ar.ar_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in PA.PA_LOOKUPS
SELECT 'pa.pa_lookups'    "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM pa.pa_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in OE.SO_LOOKUPS
SELECT 'oe.so_lookups'    "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM oe.so_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in OSM.AS_LOOKUPS
SELECT 'osm.as_lookups'   "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM osm.as_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in RG.RG_LOOKUPS
SELECT 'rg.rg_lookups'    "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM rg.rg_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
UNION
----Look in FA.FA_LOOKUPS
SELECT 'fa.fa_lookups'    "Table"
, lookup_type    "Type"
, lookup_code    "Code"
, RTRIM( meaning)   "Meaning"
FROM fa.fa_lookups
WHERE lookup_type LIKE UPPER( NVL( '%&&v_type%', '%'))
AND lookup_code LIKE UPPER( NVL( '&&v_code', '%'))
AND UPPER( meaning) LIKE UPPER( NVL( '%&&v_mean%', '%'))
ORDER BY "Type", "Table", "Code"
/

CLEAR COLUMN

Return to : Oracle Database, SQL, Application, Programming Tips