Search Results ap_apinv_approvers

Overview

AP_APINV_APPROVERS is an Oracle Payables (AP) transactional table that links approvers to individual invoice components during the Invoice Approval Workflow. Each row associates an approver—identified by role, display name, or originating system reference—with either an invoice header or a specific invoice line. Records are inserted by the Oracle Workflow engine as the approval process routes invoices through its approval hierarchy, chain-of-authority, or group-based routing logic. The table therefore functions as an audit and state-management record of who was asked to approve what, at which stage of the workflow, and with what outcome.

Because the object is a mutable, event-driven transaction log keyed to an invoice entity rather than a stable business entity, the heuristic Data Vault classification supplied in the metadata is standalone. In modeling terms, it is best treated as a link or transactional satellite rather than a hub: it carries foreign references to invoices and approvers plus descriptive workflow attributes, and its row count grows with each iteration of the approval process. This is a suggestion only; Oracle does not publish a formal Data Vault classification for this table.

Key Information Stored

The table contains 43 documented columns in the 12.2.2 physical schema. The most significant columns fall into three categories:

• Invoice identity: INVOICE_ID, INVOICE_ITERATION, INVOICE_KEY, and LINE_NUMBER tie the approver record to the invoice header or a specific line. LINE_NUMBER is the discriminator between header-level and line-level approval records.
• Approver identity: ROLE_NAME, DISPLAY_NAME, APPROVER_CATEGORY, EXTERNAL_ROLE_NAME, ORIG_SYSTEM, and ORIG_SYSTEM_ID identify the person or role responsible for the approval. ORIG_SYSTEM_ID carries the documented foreign key to HZ_ORIG_SYSTEMS_B.
• Workflow state and ordering: NOTIFICATION_ITERATION, NOTIFICATION_KEY, NOTIFICATION_STATUS, APPROVAL_STATUS, ACTION_TYPE_ID, ACTION_TYPE_ORDER_NUMBER, GROUP_OR_CHAIN_ID, GROUP_OR_CHAIN_ORDER_NUMBER, OCCURRENCE, AUTHORITY, ACCESS_CONTROL_FLAG, and APPROVER_ORDER_NUMBER capture where the approver sits in the routing sequence and the current disposition of the notification.
• Item and process context: ITEM_TYPE, ITEM_KEY, ITEM_CLASS, CHILD_PROCESS_ITEM_TYPE, and CHILD_PROCESS_ITEM_KEY describe the Workflow item being processed.
• Standard audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID.

No surrogate primary key is documented in the supplied metadata. The natural business key is composite, built from INVOICE_ID, INVOICE_ITERATION, LINE_NUMBER, NOTIFICATION_ITERATION, and APPROVER_ORDER_NUMBER, which together distinguish successive approver rows for the same invoice. ORIG_SYSTEM_ID is the only documented foreign key, referencing HZ_ORIG_SYSTEMS_B.

Common Use Cases and Queries

Typical uses include determining the current approver for an invoice, reconstructing the full approval history, and reporting on approval cycle times for a payables period.

SELECT a.invoice_id, a.invoice_iteration, a.line_number,
       a.display_name, a.role_name, a.approval_status,
       a.notification_status, a.approver_order_number
  FROM ap.ap_apinv_approvers a
 WHERE a.invoice_id = :invoice_id
 ORDER BY a.invoice_iteration, a.approver_order_number;

A second pattern joins the origin system reference to resolve external approver identities:

SELECT a.invoice_id, a.display_name, o.orig_system
  FROM ap.ap_apinv_approvers a,
       hz.hz_orig_systems_b o
 WHERE a.orig_system_id = o.orig_system_id
   AND a.approval_status = 'PENDING';

Reporting use cases include identifying invoices stuck in approval (NOTIFICATION_STATUS not terminal), measuring the number of iterations per invoice, and auditing access-control-restricted approvals via ACCESS_CONTROL_FLAG.

Related Objects

• HZ_ORIG_SYSTEMS_B — joined via ORIG_SYSTEM_ID; resolves the originating system of the approver.
• AP_INVOICES_ALL — joined via INVOICE_ID; provides invoice header context.
• AP_INVOICE_LINES_ALL — joined via INVOICE_ID and LINE_NUMBER for line-level approvals.
• WF_ITEMS / WF_NOTIFICATIONS — joined via ITEM_TYPE, ITEM_KEY, NOTIFICATION_KEY; holds the underlying Workflow state.
• AP_INVOICE_APPROVAL_HISTORY — companion audit table for approval actions.

Together these objects support end-to-end tracing of invoice approval from routing to final disposition.