Search Results ap_invoice_relationships_u1

Overview

AP.AP_INVOICE_RELATIONSHIPS is a transactional table in the Oracle Payables (AP) schema that links invoice records to one another within AP_INVOICES_ALL. Its principal purpose is to associate automatically generated Interest invoices with the overdue invoices that produced them when the automatic interest calculation feature is enabled. Payables creates these Interest invoices to compensate suppliers for late payment, computed in accordance with the U.S. Prompt Payment Act. The table therefore captures the lineage between an interest-bearing payment obligation and the specific invoice and payment number that triggered it.

The object resides in the APPS_TS_TX_DATA tablespace with PCT Free 10. Its structure is narrow, comprising nine columns that describe the relationship between two invoices plus the payment batch context in which it arose. From a heuristic Data Vault modeling perspective, the mined foreign key structure suggests a link classification: the table resolves a many-to-many style association between invoice entities (both RELATED_INVOICE_ID and ORIGINAL_INVOICE_ID reference AP_INVOICES_ALL) while carrying a small set of descriptive attributes (ORIGINAL_PAYMENT_NUM, CHECKRUN_NAME). It holds no master data of its own; it is purely an associative record.

Key Information Stored

The table stores two invoice references and the payment context that binds them, alongside standard WHO audit columns.

• RELATED_INVOICE_ID (NUMBER, 15) — Identifier of the Interest invoice; participates in the primary key.
• ORIGINAL_INVOICE_ID (NUMBER, 15) — Identifier of the original overdue invoice; participates in the primary key.
• ORIGINAL_PAYMENT_NUM (NUMBER, 15) — Payment number of the original invoice, used to tie the interest to the correct payment event.
• CHECKRUN_NAME (VARCHAR2, 255) — Name of the payment batch (check run) in which the relationship was created.
• CREATED_BY / CREATION_DATE — WHO columns capturing the creating user (foreign key to FND_USER.USER_ID) and creation timestamp.
• LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN — WHO audit columns recording the last modifying user, time, and OS login (foreign key to FND_LOGINS.LOGIN_ID).

The surrogate primary key is AP_INVOICE_RELATIONSHIPS_PK, defined on (RELATED_INVOICE_ID, ORIGINAL_INVOICE_ID). The unique business-key candidate is AP_INVOICE_RELATIONSHIPS_U1, defined over (ORIGINAL_INVOICE_ID, ORIGINAL_PAYMENT_NUM, RELATED_INVOICE_ID, CHECKRUN_NAME). A non-unique index, AP_INVOICE_RELATIONSHIPS_N1, exists on RELATED_INVOICE_ID alone to support reverse lookups from the interest invoice back to its original.

Common Use Cases and Queries

Typical usage centers on interest invoice reconciliation, late-payment reporting, and audit of Prompt Payment Act compliance. A query to find all interest invoices tied to a given original invoice joins the relationship table to AP_INVOICES_ALL on both identifier columns:

• Reconcile interest invoices against originals: SELECT r.RELATED_INVOICE_ID, r.ORIGINAL_INVOICE_ID, r.ORIGINAL_PAYMENT_NUM, r.CHECKRUN_NAME FROM AP.AP_INVOICE_RELATIONSHIPS r WHERE r.ORIGINAL_INVOICE_ID = :invoice_id;
• Reverse lookup from an interest invoice: use the N1 index by filtering on RELATED_INVOICE_ID.
• Batch-level interest analysis: group by CHECKRUN_NAME to measure interest generated per payment run.
• Audit trail: join CREATED_BY to FND_USER and CREATION_DATE to establish who created the linkage and when.

Related Objects

The most significant related objects follow directly from the documented foreign keys and indexes.

• AP.AP_INVOICES_ALL — referenced twice, via RELATED_INVOICE_ID and ORIGINAL_INVOICE_ID, making it the primary join target for both the interest invoice and its source.
• AP.AP_INVOICE_PAYMENTS_ALL — supplies the payment context referenced by ORIGINAL_PAYMENT_NUM.
• AP.AP_CHECKS_ALL — represents the payment batch identified by CHECKRUN_NAME.
• FND_USER — target of the CREATED_BY and LAST_UPDATED_BY foreign keys.
• FND_LOGINS — target of the LAST_UPDATE_LOGIN foreign key.
• AP.AP_INVOICE_RELATIONSHIPS_U1 / _N1 — the supporting unique and non-unique indexes underpinning lookups.