Results for “ozf_approval_access”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The OZF_APPROVAL_ACCESS table resides in the OZF schema and belongs to the Oracle Trade Management (OZF) product family within Oracle E-Business Suite. It stores approval access information for Trade Management objects, principally promotion and offer approval workflows. Each row captures the identity of an approver, the level at which approval was granted, the action taken, and the workflow context in which the action occurred. In release 12.1.1 and 12.2.2 the table is documented as VALID, with a physical schema of 17 columns.

From a Data Vault modeling perspective, the FK topology suggests a satellite-leaning classification. The table’s foreign key to OZF_REQUEST_HEADERS_ALL_B indicates that it describes attributes and state changes associated with a parent business entity rather than acting as an independent hub or a many-to-many link. This classification is heuristic and derived from the mined foreign-key structure; it should be treated as a modeling suggestion rather than an authoritative statement of design intent.

Key Information Stored

The table is anchored by the surrogate primary key APPROVAL_ACCESS_ID, enforced through the OZF_APPROVAL_ACCESS_PK constraint. A separate unique index, OZF_APPROVAL_ACCESS_U1, is also defined on APPROVAL_ACCESS_ID, making it the sole business-key candidate documented for this object. The most significant columns are:

• OBJECT_ID — the foreign key referencing OZF_REQUEST_HEADERS_ALL_B, tying each approval access record to its parent request header.
• OBJECT_TYPE — identifies the type of business object being approved, distinguishing promotion, offer, and related Trade Management entities.
• APPROVAL_LEVEL — the approval tier or sequence at which the record applies.
• APPROVAL_ACCESS_FLAG — a flag controlling whether the identified approver has access to act at that level.
• APPROVER_TYPE and APPROVER_ID — the category and specific identifier of the approver, typically a user, role, or group.
• ACTION_CODE and ACTION_DATE — the approval action performed and its timestamp.
• ACTION_PERFORMED_BY — the user who executed the action.
• WORKFLOW_ITEMKEY — the Oracle Workflow item key linking the row to the running approval process.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — the standard WHO audit columns.

Common Use Cases and Queries

The table is primarily interrogated to reconstruct approval history and to determine who acted at which level for a given request. A typical query joins to the request header to resolve the object under approval:

• Approval history: SELECT a.APPROVAL_LEVEL, a.APPROVER_TYPE, a.APPROVER_ID, a.ACTION_CODE, a.ACTION_DATE FROM OZF_APPROVAL_ACCESS a WHERE a.OBJECT_ID = :object_id ORDER BY a.APPROVAL_LEVEL, a.ACTION_DATE;
• Pending versus completed actions: filter on ACTION_CODE being null or populated to distinguish outstanding approvers from those who have acted.
• Access auditing: select rows where APPROVAL_ACCESS_FLAG is set to verify which approvers are entitled at each APPROVAL_LEVEL.
• Workflow tracing: join on WORKFLOW_ITEMKEY to correlate actions with Oracle Workflow history for troubleshooting stalled approvals.
• Reporting: aggregate by APPROVER_ID or APPROVER_TYPE to measure approval throughput and cycle time.

Related Objects

The following objects are most significant for joining and extending this table:

• OZF_REQUEST_HEADERS_ALL_B — the parent request header table, joined via OZF_APPROVAL_ACCESS.OBJECT_ID = OZF_REQUEST_HEADERS_ALL_B.OBJECT_ID (per the documented FK); this is the primary relationship.
• OZF_REQUEST_HEADERS_ALL_TL — the translation table for request headers, used to obtain descriptive text.
• OZF_REQUEST_LINES_ALL_B — request line details that inherit header-level approval context.
• Oracle Workflow runtime tables (WF_ITEMS, WF_ITEM_ACTIVITY_STATUSES) — joined through WORKFLOW_ITEMKEY for process state analysis.
• Approval and role setup objects in FND and JTF schemas — used to resolve APPROVER_ID and APPROVER_TYPE to actual users and roles.