Search Results ams_object_rules_b




Overview
Explore in AI Chat

Overview

AMS_OBJECT_RULES_B is the base (B) table of the Oracle Marketing (AMS) module in Oracle E-Business Suite 12.1.1 and 12.2.2. As its description states, it "stores all the object rules" — the metadata-driven definitions that the Marketing application uses to control how marketing objects (campaigns, events, offers, and similar entities) behave at runtime. Each row describes a rule that binds a business action to a specific implementation artifact: an API, a PL/SQL package and procedure, a Java class, or a method. In this sense the table acts as a registry of executable behaviour rather than a store of transactional marketing data.

From a Data Vault modeling perspective, the mined relationship structure classifies this object as hub-leaning. The primary key OBJECT_RULE_ID is a single, stable surrogate identifier, which is characteristic of a hub; the foreign key to FND_SECURITY_GROUPS is best modelled as a link to an external reference hub. The presence of descriptive attributes such as APPROVAL_TYPE, RULE_TYPE, and API_TYPE indicates that satellite tables would accompany the hub in a full Data Vault design.

Key Information Stored

The table is documented with 37 columns but only a subset carries functional meaning. The most significant are:

  • OBJECT_RULE_ID — the surrogate primary key, enforced by AMS_OBJECT_RULES_B_PK.
  • RULE_USED_BY and RULE_USED_BY_TYPE — identify the consuming object and its type.
  • RULE_TYPE and API_TYPE — classify the rule and the invocation mechanism used to execute it.
  • APPROVAL_TYPE — controls approval processing behaviour for the rule.
  • PACKAGE_NAME, PROCEDURE_NAME, CLASS_NAME, METHOD_NAME — the concrete PL/SQL or Java entry points invoked at runtime.
  • QUERY — a stored query fragment used by the rule.
  • RELATED_OBJECT — links the rule to an associated marketing object.
  • SEEDED_FLAG — distinguishes Oracle-seeded rules from customer-defined ones.
  • PARAM1 through PARAM15 — a generic parameter block supplying rule-specific arguments.
  • APPLICATION_ID and SECURITY_GROUP_ID — application ownership and Multi-Org security context.

The business-key candidate documented is the unique index AMS_OBJECT_RULES_B_U1 over (OBJECT_RULE_ID, ZD_EDITION_NAME), which supports edition-based redefinition in 12.2.x. Standard audit columns (LAST_UPDATE_DATE, CREATED_BY, OBJECT_VERSION_NUMBER, and so on) are present for concurrent-program and row-versioning support.

Common Use Cases and Queries

Typical reporting and diagnostic tasks include enumerating seeded rules, finding which implementation artifact handles a given action, and verifying that rules are correctly registered before enabling a campaign workflow. A useful pattern joins the translatable table for the descriptive name:

  • Listing all seeded approval rules: SELECT OBJECT_RULE_ID, RULE_TYPE, PACKAGE_NAME, PROCEDURE_NAME FROM AMS_OBJECT_RULES_B WHERE SEEDED_FLAG = 'Y' AND RULE_TYPE = 'APPROVAL';
  • Finding customizations: SELECT b.OBJECT_RULE_ID, t.OBJECT_RULE_NAME FROM AMS_OBJECT_RULES_B b, AMS_OBJECT_RULES_TL t WHERE b.OBJECT_RULE_ID = t.OBJECT_RULE_ID AND t.LANGUAGE = USERENV('LANG') AND b.SEEDED_FLAG = 'N';
  • Auditing handler bindings for a class: SELECT CLASS_NAME, METHOD_NAME FROM AMS_OBJECT_RULES_B WHERE API_TYPE = 'JAVA';

These queries support migration validation, regression testing after patching, and documentation of marketing automation logic.

Related Objects

The following objects are most significant in relation to AMS_OBJECT_RULES_B:

  • AMS_OBJECT_RULES_TL — the translatable child table, joined on OBJECT_RULE_ID, supplying language-specific rule names and descriptions.
  • FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for Multi-Org access control.
  • FND_APPLICATION — resolved through APPLICATION_ID to identify the owning application.
  • AMS_OBJECT_RULES_B_PK — the primary key constraint on OBJECT_RULE_ID.
  • AMS_OBJECT_RULES_B_U1 — the unique index supporting edition-based lookups.

Together these objects form the metadata backbone through which Oracle Marketing resolves and executes object rules.