Search Results ap_line_aprvl_hist_all




Overview
Explore in AI Chat

Overview

AP_LINE_APRVL_HIST_ALL is a Payables (AP) transaction table in the AP schema that stores the approval and rejection history of individual invoice lines routed through the Invoice Approval Workflow (IAW). When an invoice enters the approval phase process, the workflow engine inserts one record per approver assigned to review the invoice, capturing both the assignment and the approver's response. Because the table name carries the _ALL suffix, it designates a multi-organization (multi-org) table, with the ORG_ID column partitioning rows by operating unit under the Oracle EBS partitioning model.

The ETRM metadata classifies this object heuristically as a standalone Data Vault entity — that is, it is not currently modeled as a dependent satellite hanging off an invoice hub, but rather as an independent historical record keyed by the surrogate LINE_APRVL_HISTORY_ID. From a Data Vault modeling perspective, this table is best treated as a satellite of the invoice line hub, since it records the state and outcome of an approval event over time. The standalone classification reflects the absence of documented foreign keys in the current schema, not necessarily the absence of a logical relationship to the invoice.

Key Information Stored

The surrogate primary key is LINE_APRVL_HISTORY_ID, enforced by the AP_LINE_APRVL_HIST_PK constraint. This column — the value users most often search for — is the unique identifier for each approval history row and should never be treated as a business key. The integration (business) key to the parent invoice is composed of INVOICE_ID together with LINE_NUMBER (and, in multi-org contexts, ORG_ID). The workflow engine additionally assigns NOTIFICATION_KEY, and the pair of ITERATION and NOTIFICATION_ORDER controls the ordering of notifications within the approval process, particularly when parallel or repeated approval cycles occur.

Response data is held in RESPONSE (the approver's action, such as approve or reject), APPROVER_ID (the approving party), and APPROVER_COMMENTS. Amounts approved at the line level are broken out by category: LINE_AMOUNT_APPROVED, TAX_AMOUNT_APPROVED, FREIGHT_AMOUNT_APPROVED, and MISC_AMOUNT_APPROVED.

The table also supports an audit trail of changes through HISTORY_TYPE, ACCESS_TYPE_CODE, ALTERED_FIELD_CODE, ALTERED_FIELD_DATA_TYPE, plus ORIGINAL_VALUE and NEW_VALUE. This allows the workflow to record what a value was before and after an approver modified it. Standard EBS audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — are present for troubleshooting and for incremental extraction. NEW_LINE_NUMBER supports line renumbering scenarios during approval.

Common Use Cases and Queries

Typical reporting retrieves the approval chain and outcome for a given invoice line. A representative query joins the history to the invoice header and lines:

SELECT h.line_aprvl_history_id,
       h.invoice_id,
       h.line_number,
       h.approver_id,
       h.response,
       h.line_amount_approved,
       h.approver_comments,
       h.creation_date
FROM   ap.ap_line_aprvl_hist_all h
WHERE  h.invoice_id = :p_invoice_id
ORDER  BY h.iteration, h.notification_order;

Common scenarios include: auditing who approved or rejected a specific invoice line; reconciling approved amounts against invoice distributions; building cycle-time reports using CREATION_DATE and the ordering columns; and tracing field-level changes via ALTERED_FIELD_CODE, ORIGINAL_VALUE, and NEW_VALUE. Because the table is multi-org, production queries should filter by ORG_ID (or rely on the MOAC/security profile) unless the report is intentionally cross-org. Incremental extracts should key off LAST_UPDATE_DATE for performance.

Related Objects

The strongest logical relationship is from INVOICE_ID and LINE_NUMBER to the Payables invoice tables:

Note the orphaned-approver risk: because APPROVER_ID is not visibly FK-constrained in the documented schema, joins to PER_ALL_PEOPLE_F may not return rows for terminated approvers; retention of the historical ID is therefore essential for audit completeness.