Search Results ams_iba_rulesets_v




Overview
Explore in AI Chat

Overview

The AMS_IBA_RULESETS_V view is a marketing-focused reporting object owned by the APPS schema in Oracle E-Business Suite. It resides within the AMS (Marketing) product family and exposes ruleset definitions used by Oracle Marketing's interactive and batch targeting engine. A ruleset is a stored configuration that defines how a set of records should be selected, sorted, and returned to a calling application or campaign process. The view is defined as a denormalized projection over two base tables, combining the operational ruleset attributes held in the transactional table with the language-specific descriptive text held in the translation table.

The view's principal purpose is to provide a simplified, user-friendly read interface. Internal sort-order codes are translated into readable values, and multilanguage descriptions are resolved through the session language environment. This makes the view suitable for concurrent program reports, business intelligence extracts, OAF-based marketing pages, and integration layers that need to consume ruleset metadata without joining translation tables manually.

Underlying Base Objects

Based on the documented view text and ETRM metadata, AMS_IBA_RULESETS_V is constructed from two referenced base objects, both exposed through APPS-owned synonyms:

  • JTF_R_RULESETS_B — the base (B) table containing the core ruleset definition, including RULESET_ID, OBJECT_VERSION_NUMBER, sort order, sort-by code, status, creation date, and the effective date range (START_DATE, END_DATE). The alias RSB is used in the view query.
  • JTF_R_RULESETS_TL — the translation (TL) table holding NAME and DESCRIPTION for each ruleset in each supported language. The alias RSTL is used, and the join is outer (+) on LANGUAGE = USERENV('LANG').

The two tables are joined on RULESET_ID. The outer join on the language column ensures a row is returned even when no translation exists for the current session language, and the results are ordered by RULESET_ID. A synthetic sequence value is generated using ROWNUM to produce ROW_NUM.

Key Columns

  • ROW_NUM — a synthetic row number produced by ROWNUM for display or sequencing purposes.
  • RULESET_ID — primary identifier of the ruleset.
  • CR_OBJECT_VERSION_NUMBER — version number from the base table, used for optimistic locking and change tracking.
  • NAME and DESCRIPTION — translated descriptive text from the TL table.
  • SORT_ORDER — a decoded, human-readable value derived from IBA_SORT_ORDER: 0 maps to 'DESCENDING', 1 to 'ASCENDING', and 2 to 'RANDOM'.
  • SORT_CODE — the raw IBA_SORT_BY_CODE value indicating the attribute or column used for sorting. This is the column directly relevant to the search term "sort_code."
  • STATUS — the ruleset's active/inactive state.
  • CREATION_DATE, START_DATE, END_DATE — audit and effective-dating columns governing when the ruleset is valid.

Common Use Cases and Queries

Typical usage involves listing active rulesets, inspecting sort configuration, or driving downstream targeting logic. A representative query filtering on the sort code column is:

  • List active rulesets with their sort configuration:
    SELECT RULESET_ID, NAME, SORT_ORDER, SORT_CODE FROM AMS_IBA_RULESETS_V WHERE STATUS = 'A';
  • Identify rulesets sorted by a specific code:
    SELECT RULESET_ID, NAME, SORT_ORDER, SORT_CODE FROM AMS_IBA_RULESETS_V WHERE SORT_CODE = :p_sort_code;
  • Audit effective-dated rulesets:
    SELECT RULESET_ID, NAME, START_DATE, END_DATE FROM AMS_IBA_RULESETS_V WHERE SYSDATE BETWEEN START_DATE AND END_DATE;
  • Generate a sequentially numbered extract:
    SELECT ROW_NUM, RULESET_ID, NAME, SORT_ORDER FROM AMS_IBA_RULESETS_V ORDER BY ROW_NUM;

Because the view resolves language context and sort order automatically, it is preferred over direct base-table joins in reporting and integration deliverables. Note that the view is read-only; all DML must target the underlying JTF_R_RULESETS_B and JTF_R_RULESETS_TL tables.