Search Results lov_sql

Overview

AS_SCORECARD_RULES_V is a seeded Oracle E-Business Suite view owned by the APPS schema in the AS – Sales Foundation product. It exposes scorecard rule definitions used by Oracle Sales and TeleSales scorecarding functionality, a component of the Sales Foundation module (formerly Oracle Sales Online / iStore foundations). The view presents the qualification criteria and scoring attributes that determine how sales leads, opportunities, and other sales entities are scored against a given scorecard.

Because the view joins header-level rule qualification records to their multilingual lookup descriptions and to the enabled sales lead qualifiers, it provides a denormalized, language-aware access path for scorecard rule metadata. In EBS reporting and integration scenarios, AS_SCORECARD_RULES_V typically serves as the source for building validation lists, configuration extracts, and custom reports that describe how each scorecard rule evaluates a source column—such as a lead qualification attribute—and what score it assigns when the value falls within a defined range. This makes the view particularly relevant in the context of a "lov_sql" search, since one of its exposed columns (LOV_SQL) carries the dynamically generated SQL used to populate a list of values in the scorecard rule setup user interface.

The view retains a status of VALID in both Oracle EBS 12.1.1 and 12.2.2; the documented metadata is identical across those releases, indicating no structural change between them.

Underlying Base Objects

The view is defined over three documented base objects, all referenced through APPS synonyms:

• AS_CARD_RULE_QUAL_VALUES — stores the per-rule qualification values, including the score, numeric and character high/low range boundaries, and the qualification value identifier.
• AS_SALES_LEAD_QUALS_B — the base table describing sales lead qualifiers, supplying the source table name, source column name, data type, range flag, and LOV_SQL used for list-of-values generation.
• AS_SALES_LEAD_QUALS_TL — the translated (language-specific) table providing the user-facing meaning of each qualifier.

The join logic requires AS_SALES_LEAD_QUALS_B.ENABLED_FLAG = 'Y' and restricts the translated row to the session language via USERENV('LANG'), so the view only returns active qualifiers rendered in the user's current language.

Key Columns

• SCORECARD_ID / CARD_RULE_ID — identify the scorecard and the specific rule within that scorecard.
• SCORE — the score awarded when the associated qualification value is satisfied.
• HIGH_VALUE_NUMBER / LOW_VALUE_NUMBER — numeric range boundaries for range-based scoring rules.
• HIGH_VALUE_CHAR / LOW_VALUE_CHAR — character range boundaries for text-based rules.
• QUAL_VALUE_ID / SEED_QUAL_ID — identifiers for the qualification value and its seed qualifier.
• SOURCE_TABLE_NAME / SOURCE_COLUMN_NAME — the database table and column evaluated by the rule.
• DATA_TYPE — a truncated representation of the qualifier's data type (SUBSTR length 8).
• RANGE_FLAG — indicates whether the rule uses range-based comparison.
• LOV_SQL — the SQL fragment used to populate the list of values for the qualifier; this is the column directly relevant to "lov_sql" searches.
• MEANING — the translated, user-facing description of the qualifier.
• LAST_UPDATE_DATE — the last modification timestamp for the rule value record.

Common Use Cases and Queries

Typical uses include auditing scorecard configuration, extracting rule definitions for migration, and inspecting the dynamically generated LOV SQL for troubleshooting list-of-values behavior in scorecard setup.

To retrieve all active rules for a specific scorecard:

• SELECT card_rule_id, meaning, score, low_value_number, high_value_number FROM as_scorecard_rules_v WHERE scorecard_id = :p_scorecard_id ORDER BY card_rule_id;

To inspect the LOV SQL for enabled qualifiers:

• SELECT seed_qual_id, meaning, source_table_name, source_column_name, lov_sql FROM as_scorecard_rules_v WHERE lov_sql IS NOT NULL;

To list scoring rules filtered by data type:

• SELECT scorecard_id, meaning, data_type, range_flag FROM as_scorecard_rules_v WHERE data_type = 'NUMBER';

Because DATA_TYPE, RANGE_FLAG, and the associated range columns are exposed together, the view is well suited to dynamically constructing validation or scoring scripts that mirror the runtime evaluation performed by the Sales Foundation scoring engine.