Search Results ahl_approval_api




Overview
Explore in AI Chat

Overview

The AHL_APPROVAL_API table is a configuration repository within the Oracle E-Business Suite Complex Maintenance Repair and Overhaul (AHL) product family. It stores the mapping between approval rule types and the PL/SQL application programming interfaces that the Oracle Approval Management Engine (AME) or the AHL workflow layer invokes when an approval rule of a given type must be evaluated. In effect, the table acts as a registry: it tells the application which package and procedure to call for a specific approval scenario, activity type, and approval object.

Because the row population is largely static reference configuration maintained by the implementation team rather than high-volume transactional data, the table is low in row count but high in functional importance. Every approval transaction that flows through AHL — work order approvals, maintenance activity sign-offs, and related change authorizations — depends on a correctly configured entry in this table. The metadata's heuristic Data Vault classification is standalone, suggesting that the table behaves as an independent reference or configuration entity rather than as a transactional hub, link, or satellite. In Data Vault modeling terms, it is best treated as a small reference table, not as a business-process fact.

Key Information Stored

The physical schema documented for ETRM 12.2.2 contains 15 columns. The most significant are:

  • APPROVAL_API_ID — the surrogate primary key, enforced by the AHL_APPROVAL_APU_PK constraint. This column uniquely identifies each approval API registration row.
  • APPROVAL_TYPE — categorizes the approval rule the API serves, allowing multiple API definitions to coexist for different rule families.
  • API_USED_BY — identifies the consumer or context (for example, a specific approval engine or module) that invokes the API.
  • APPROVAL_OBJECT_TYPE — the business object being approved, such as a work order or maintenance activity.
  • ACTIVITY_TYPE — further qualifies the approval activity associated with the API.
  • PACKAGE_NAME and PROCEDURE_NAME — the actual PL/SQL entry point invoked at runtime; together these columns constitute the executable definition of the approval hook.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, providing multi-org and security-group scoping.
  • ZD_EDITION_NAME — the editioning column used under EBR (Edition-Based Redefinition), which in 12.2.2 participates in the unique index AHL_APPROVAL_API_U1 alongside APPROVAL_API_ID. This composite unique index is the documented business-key candidate.
  • OBJECT_VERSION_NUMBER — used by the Oracle Application Framework (OAF) for optimistic locking during concurrent updates.
  • The standard WHO audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — capture insert and update provenance.

Note the distinct roles of APPROVAL_API_ID (surrogate PK) and the (APPROVAL_API_ID, ZD_EDITION_NAME) pair (unique business-key candidate enforcing edition uniqueness).

Common Use Cases and Queries

Typical scenarios involve validating and auditing approval configuration. A common query lists all active API registrations for a given approval object and activity:

SELECT approval_api_id, approval_type, api_used_by,
       approval_object_type, activity_type,
       package_name, procedure_name
FROM   ahl.ahl_approval_api
WHERE  approval_object_type = :object_type
AND    activity_type        = :activity_type;

Implementation teams use the table to confirm that the correct package/procedure is wired for each approval scenario before going live. DBAs query it to reconcile security-group scoping:

SELECT a.approval_api_id, a.package_name, a.procedure_name,
       g.security_group_name
FROM   ahl.ahl_approval_api a,
       fnd_security_groups  g
WHERE  a.security_group_id = g.security_group_id;

Reporting use cases include generating a configuration matrix of approval types versus API hooks, and change-control reporting that flags rows whose LAST_UPDATE_DATE shows recent modification of approval logic.

Related Objects

  • FND_SECURITY_GROUPS — referenced via AHL_APPROVAL_API.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; the only documented foreign-key relationship.
  • AHL_APPROVAL_RULES (or equivalent AHL approval-rule tables) — consume the API definitions registered here when rule evaluation requires a PL/SQL hook.
  • AME_* approval engine tables — where the AHL layer delegates advanced conditional approval routing, indirectly dependent on the package and procedure named in this table.
  • FND_USER — resolves CREATED_BY and LAST_UPDATED_BY to the configuring user.
  • AHL_WORKORDERS and related maintenance objects — the transactional entities whose approvals ultimately trigger the APIs registered here.
  • The registered PL/SQL packages themselves (values in PACKAGE_NAME/PROCEDURE_NAME) — the executable dependencies that must remain valid for approval processing to succeed.

Because the table carries a standalone Data Vault classification and only one documented FK, it is best understood as self-contained approval configuration whose integrity is primarily enforced by the AHL_APPROVAL_APU_PK and AHL_APPROVAL_API_U1 indexes rather than by a dense web of referential constraints.