FND Lookup Values

Description
Categories: Enginatics
Repository: Github
Lookup types and values, for example to find a lookup type for a particular lookup code value or meaning.
Run FND Lookup Values and other Oracle EBS reports with Blitz Report™ on our demo environment
select
fav.application_name view_application,
flv.lookup_type,
fltv.description type_description,
flv.lookup_code code,
flv.language,
flv.source_lang source_language,
flv.meaning,
flv.description,
flv.tag,
xxen_util.meaning(flv.enabled_flag,'YES_NO',0) enabled_flag,
flv.start_date_active,
flv.end_date_active,
xxen_util.user_name(flv.created_by) created_by,
xxen_util.client_time(flv.creation_date) creation_date,
xxen_util.user_name(flv.last_updated_by) last_updated_by,
xxen_util.client_time(flv.last_update_date) last_update_date,
'xxen_util.meaning(x.'||lower(flv.lookup_type)||','''||flv.lookup_type||''','||flv.view_application_id||') '||lower(flv.lookup_type)||',' sql_text,
'select
flv.lookup_code,
flv.meaning,
flv.description
from
fnd_lookup_values flv
where
flv.lookup_type(+)='''||flv.lookup_type||''' and
flv.view_application_id(+)='||flv.view_application_id||' and
flv.language(+)=userenv(''lang'') and
flv.security_group_id(+)=0
order by
flv.lookup_code' lookup_values
from
fnd_application_vl fav0,
fnd_application_vl fav,
fnd_lookup_types_vl fltv,
fnd_lookup_values flv,
fnd_languages fl
where
1=1 and
fav0.application_id=fltv.application_id and
fav.application_id=flv.view_application_id and
fltv.lookup_type=flv.lookup_type and
fltv.view_application_id=flv.view_application_id and
flv.security_group_id=0 and
flv.language=fl.language_code
order by
flv.lookup_type,
flv.lookup_code,
fl.installed_flag,
flv.language
Parameter Name SQL text Validation
Type
flv.lookup_type=:lookup_type
LOV
View Application
fav.application_name=:view_application
LOV
Code contains
upper(flv.lookup_code) like '%'||upper(:lookup_code)||'%'
LOV
Meaning contains
upper(flv.meaning) like '%'||upper(:meaning)||'%'
LOV
Description contains
upper(flv.description) like '%'||upper(:description)||'%'
Char
Creation Date From
flv.creation_date>=:creation_date_from
Date
Creation Date To
flv.creation_date<:creation_date_to+1
Date
Created By
flv.created_by=xxen_util.user_id(:created_by)
LOV
Language Code
flv.language=:language
LOV
Missing Translation to Lang Code
not exists (select null from
fnd_lookup_values flv2
where
flv.lookup_type=flv2.lookup_type and
flv.lookup_code=flv2.lookup_code and
flv.view_application_id=flv2.view_application_id and
flv.security_group_id=flv2.security_group_id and
flv2.language=:missing_language and
flv2.source_lang=:missing_language
)
LOV