FND Lookup Search – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Application Object Library · all Application Object Library reports
Overview
Finds the best matching lookup_type for a given set of lookup_codes in a custom application base table.
Example: Coding a sql for ap suppliers, the value of column vendor_type_lookup_code should get translated to the user visible meaning instead of the code. The lookup_type used for translation can be found by this report entering table name AP_SUPPLIERS and column name VENDOR_TYPE_LOOKUP_CODE. The output contains a column SQL_TEXT which can be used directly in the sql where clause:
=flv.lookup_code(+) and flv.lookup_type(+)=’VENDOR TYPE’ and flv.view_application_id(+)=201 and flv.language(+)=userenv(‘lang’) and flv.security_group_id(+)=0 and
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Table Name, Colum Name
Oracle EBS Tables Used
dba_tables, fnd_oracle_userid, fnd_product_installations, fnd_application, xo, fnd_lookup_values, fnd_application_vl
Report Categories
Related Reports
FND Audit Setup, DBA Users, FND Applications, FND Histogram Columns, CAC Accounting Period Status, CAC Interface Error Summary, XLA Subledger Accounting Rules Detail Listing, Blitz Report VPD Policy Setup
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | FND Lookup Search 27-Jul-2018 212528.xlsx |
| Blitz Report™ XML Import | FND_Lookup_Search.xml |
| SQL Source | FND Lookup Search.sql |
| Full Report on Enginatics | www.enginatics.com/reports/fnd-lookup-search/ |
Executive Summary
The FND Lookup Search report is a developer utility designed to reverse-engineer lookup codes. It helps find the correct LOOKUP_TYPE for a given column in a table.
Business Challenge
- Data Mapping: When writing SQL queries, you often see a code (e.g., ‘S’) in a column but don’t know which Lookup Type decodes it to ‘Standard’.
- Legacy Analysis: Understanding the data model of older or custom tables.
The Solution
This Blitz Report analyzes the data in a specific table and column:
- Pattern Matching: It compares the values in your target column against all active Lookup Types in the system.
- Recommendation: It suggests the most likely Lookup Type that matches your data.
- SQL Generation: It generates the SQL join syntax to link your table to
FND_LOOKUP_VALUES.
Technical Architecture
The report uses a heuristic approach to match distinct values from the target table with lookup codes.
Parameters & Filtering
- Table Name: The table you are investigating (e.g.,
AP_SUPPLIERS). - Column Name: The column containing the code (e.g.,
VENDOR_TYPE_LOOKUP_CODE).
Performance & Optimization
- Analysis Tool: This is an interactive tool, not a standard report. It runs a dynamic query to find matches.
FAQ
- Q: Does it work for custom lookups?
- A: Yes, it searches all lookups in
FND_LOOKUP_TYPES.
- A: Yes, it searches all lookups in
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH