Search Results ahl_approval_rules_b




Overview
Explore in AI Chat

Overview

AHL_APPROVAL_RULES_B is the base (non-translated) approval rule definition table in the Oracle E-Business Suite Complex Maintenance, Repair and Overhaul (AHL) module. It stores the master records that define which approval rules govern approval processing for maintenance and overhaul transactions, including the object being approved, the approval type and priority, the effective date window, and the rule's active status. In EBS 12.1.1 and 12.2.2, the table resides in the AHL schema and is populated with both seeded rules supplied by Oracle (SEEDED_FLAG) and customer-defined rules.

The _B suffix indicates this is the base table of a translated entity; the corresponding AHL_APPROVAL_RULES_TL table holds the language-specific descriptive columns. The table is uniquely identified by the primary key constraint AHL_APPROVAL_RULES_B_PK on APPROVAL_RULE_ID. From a Data Vault modeling perspective (heuristic classification, mined from the foreign key structure), AHL_APPROVAL_RULES_B is hub-leaning: APPROVAL_RULE_ID serves as a durable business key that other tables (such as approvers and translations) reference, making it a natural candidate for a hub entity in a dimensional or Data Vault-style reporting layer.

Key Information Stored

The table exposes 34 documented columns. The most significant for functional and reporting work are:

Two unique keys are documented: the primary key AHL_APPROVAL_RULES_B_PK on APPROVAL_RULE_ID, and the unique index AHL_APPROVAL_RULES_B_U1 on the composite (APPROVAL_RULE_ID, ZD_EDITION_NAME), which reflects the editioning design of 12.2. The business-key candidate for integration purposes remains APPROVAL_RULE_ID.

Common Use Cases and Queries

The most frequent access path is a direct lookup by APPROVAL_RULE_ID, typically when tracing why a given approval action was triggered. A typical pattern joins the base table to its translation table to retrieve the user-visible name and description:

  • Retrieve a rule and its translated description:
    SELECT b.approval_rule_id, b.approval_object_code, b.status_code, t.name, t.description
    FROM ahl.ahl_approval_rules_b b
    JOIN ahl.ahl_approval_rules_tl t ON t.approval_rule_id = b.approval_rule_id
    WHERE b.approval_rule_id = :p_rule_id;
  • List active, non-seeded rules currently in effect for a given object code, filtering on STATUS_CODE, SEEDED_FLAG, and the ACTIVE_START_DATE/ACTIVE_END_DATE window.
  • Audit configuration changes over time using LAST_UPDATE_DATE and LAST_UPDATED_BY, useful for security and compliance reporting.
  • Identify all approvers attached to a rule via AHL_APPROVERS (joined on APPROVAL_RULE_ID) to produce approval-hierarchy reports.
  • Validate multi-org/security partitioning by joining SECURITY_GROUP_ID to FND_SECURITY_GROUPS.

Because the composite unique index includes ZD_EDITION_NAME, queries on 12.2 should be aware that edition-specific rows may exist; reporting extracts should generally constrain or group by edition where appropriate.

Related Objects

  • AHL_APPROVAL_RULES_TL — the translation table; joined on APPROVAL_RULE_ID to obtain display names and descriptions. This is the primary child table referencing AHL_APPROVAL_RULES_B.
  • AHL_APPROVERS — stores the approvers associated with each rule; joined on APPROVAL_RULE_ID. This is the second FK-dependent table and is essential for approval routing analysis.
  • FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID to resolve the security group to which the rule belongs.
  • The AHL approval engine (approval rule resolution logic) — consumes rows in this table at runtime to determine applicable approval rules for a given object, type, and priority.
  • Approval transaction and history entities within AHL — reference the resolved rule to record which rule governed a given approval event.
  • Concurrent programs and DBA/ETRM data-model reports — surface this table for configuration review and seeded-versus-custom comparisons.

Together, these objects form the approval configuration cluster in AHL, with AHL_APPROVAL_RULES_B acting as the central hub referenced by translations, approvers, and downstream approval processing logic.