Search Results ahl_approval_api_u1

Overview

The AHL.AHL_APPROVAL_API table is a repository for metadata describing the programmatic interfaces (APIs) that Oracle E-Business Suite invokes when evaluating approval rules. It belongs to the AHL schema, which supports Oracle's product development and quality management functionality, and it resides in the APPS_TS_SEED tablespace — a placement consistent with seed or configuration data rather than high-volume transactional data. The table's stated purpose is to store information about APIs used by approval rules; each row associates an approval type, object type, and activity type with a specific PL/SQL package and procedure entry point. This makes AHL_APPROVAL_API a configuration-driven dispatch table: rather than hard-coding approval logic, the application can look up which API to call based on the approval context. The table is documented as VALID and carries 15 columns in the ETRM 12.2.2 schema.

From a Data Vault modeling perspective, the heuristic classification for this object is standalone, with no foreign-key relationships to other hubs or links other than a reference to FND_SECURITY_GROUPS via SECURITY_GROUP_ID. Accordingly, it is best modeled as a standalone hub-like reference or a small satellite of approval-API metadata rather than as a link table integrating multiple business entities.

Key Information Stored

The table is anchored by its surrogate primary key and business-key index, with a set of descriptive columns identifying the approval context and the API to be invoked.

• APPROVAL_API_ID (NUMBER) — Surrogate primary key; unique identifier for each approval API entry. It is also the leading column of the unique index AHL_APPROVAL_API_U1 (APPROVAL_API_ID, ZD_EDITION_NAME), which serves as the business-key candidate in the 12.2.2 schema.
• APPROVAL_TYPE (VARCHAR2, 30) — Identifier for the approval type the API supports.
• API_USED_BY (VARCHAR2, 80) — Identifier describing the consumer or context that uses the API.
• APPROVAL_OBJECT_TYPE (VARCHAR2, 30) — Identifier for the object type being approved.
• ACTIVITY_TYPE (VARCHAR2, 80) — Identifier for the activity type associated with the approval.
• PACKAGE_NAME (VARCHAR2, 80) — Name of the PL/SQL package implementing the approval API; together with PROCEDURE_NAME this forms the runtime dispatch target.
• PROCEDURE_NAME (VARCHAR2, 80) — Name of the procedure within PACKAGE_NAME that the approval engine invokes.
• SECURITY_GROUP_ID (NUMBER) — Multi-tenant/hosting attribution column; documented as a foreign key to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER (NUMBER) — Optimistic locking sequence number.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns recording audit trail for the row.

Common Use Cases and Queries

Because the table drives approval-rule API resolution, typical uses include diagnostic queries to confirm which package/procedure handles a given approval scenario, and configuration reporting to inventory registered approval APIs.

• Identify the dispatch target for a specific approval context:

SELECT approval_type, approval_object_type, activity_type, package_name, procedure_name
FROM ahl.ahl_approval_api
WHERE approval_type = :p_type;

• List all APIs used by a particular consumer:

SELECT approval_api_id, approval_type, package_name, procedure_name
FROM ahl.ahl_approval_api
WHERE api_used_by = :p_consumer;

• Join to FND_SECURITY_GROUPS to resolve the hosting security group:

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

Reporting scenarios include auditing API registrations, verifying that referenced packages and procedures exist, and tracking changes to approval API mappings over time via the Who columns.

Related Objects

The documented dependency graph is deliberately narrow. AHL_APPROVAL_API does not reference any database object directly other than the security-group attribution.

• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID; the only documented foreign-key join.
• APPS.AHL_APPROVAL_API — The APPS-synonymed access point through which the table is typically queried.
• AHL_APPROVAL_API_U1 — Unique index on (APPROVAL_API_ID, ZD_EDITION_NAME) enforcing the business-key candidate.
• AHL_APPROVAL_APU_PK — Primary key constraint on APPROVAL_API_ID.

Other AHL approval-rule tables (for example, approval-rule definition and approval-history stores) are functionally related even though they are not recorded as explicit FK dependencies in the provided metadata, since they consume the API mappings held here by resolving package/procedure targets at approval time.