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

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips