For a nested or temporary interface, using this IKM gives additional functionality in the SQL generated for the nested SQL block. The main benefit is allowing hints to be included. In addition, Aggregate Lookup and Source SQL Override can also be useful for some applications.
Options for Functionality
Aggregate Lookup — Aggregates the SQL block by using the user flexfields to define a grouping of records to restrict the output to one row per group.
Prerequisites for using this option are:
The interface must set the following user flexfields:
- UD1: Group by columns. These columns are usually the columns being joined to.
- UD2: Aggregated columns. The fields being returned.
- UD3: Column for active lookup (LKP_ACTIVE). This is set to 1.
Source SQL Override — Allows the generated nested SQL block to be overridden. This is useful if the SQL needs to be more dynamic in nature, for example, using variables to reference dynamic tables.
Prerequisites for using this option are the following:
For each target interface column there must be an identically aliased column in the SELECT clause of the SQL override.
Use of this option is discouraged. Make sure the use case is reviewed by the Oracle BI Applications Standards Group before implementing.
Options for Performance Tuning
Hints — This IKM allows the passing of hints into the generated SQL. For more information, see the article titled Oracle Business Intelligence Applications Version 11.1.1.7.1 Performance Recommendations (Doc ID 1539322.1) on My Oracle Support.