Search Results ap_invoice_relationships




Overview
Explore in AI Chat

Overview

AP_INVOICE_RELATIONSHIPS is an Oracle Payables (AP) schema table that stores the association between invoices and the interest invoices automatically generated for overdue invoices. When Payables processes an overdue invoice and calculates late-payment interest, it creates a distinct interest invoice and records the linkage between that interest invoice and the original invoice in this table. The object therefore functions as a pure association table: it carries no descriptive invoice attributes of its own, only the references that tie two AP_INVOICES_ALL records together along with the payment and checkrun context in which the relationship was established.

Under the heuristic Data Vault classification mined from the foreign key structure, this table is best modeled as a link table. It resolves a many-to-many-style relationship between original invoices and their generated interest invoices, with the payment run and payment number acting as additional link-level descriptors. No hub or satellite role is implied, since the table introduces no new business keys and stores no independently changing descriptive attributes beyond audit columns.

Key Information Stored

The documented physical schema contains nine columns in ETRM 12.2.2. The most significant are:

  • RELATED_INVOICE_ID — the invoice identifier for the interest invoice generated for the overdue original; participates in the composite primary key and in a foreign key to AP_INVOICES_ALL.
  • ORIGINAL_INVOICE_ID — the invoice identifier of the source (overdue) invoice; the other half of the primary key and the second foreign key to AP_INVOICES_ALL.
  • ORIGINAL_PAYMENT_NUM — the payment number of the original payment associated with the overdue invoice, providing run-level context for the relationship.
  • CHECKRUN_NAME — the name of the payment batch/checkrun under which the interest invoice was produced.
  • CREATED_BY, CREATION_DATE — standard WHO audit columns recording the creating user and timestamp.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns recording the last modifying user, timestamp, and login session.

The surrogate-level primary key is documented as AP_INVOICE_RELATIONSHIPS_PK on (RELATED_INVOICE_ID, ORIGINAL_INVOICE_ID). A unique index, AP_INVOICE_RELATIONSHIPS_U1, is defined on (ORIGINAL_INVOICE_ID, ORIGINAL_PAYMENT_NUM, RELATED_INVOICE_ID, CHECKRUN_NAME) and represents the strongest business-key candidate, since it also incorporates the payment number and checkrun context.

Common Use Cases and Queries

Typical scenarios center on tracing late-payment interest charges back to the originating invoice, reconciling interest invoice creation by payment run, and auditing which original payments generated interest.

  • Identifying the interest invoice produced for a given original invoice:
SELECT related_invoice_id, original_invoice_id,
       original_payment_num, checkrun_name
FROM   ap.ap_invoice_relationships
WHERE  original_invoice_id = :p_invoice_id;
  • Reconciling interest invoices by checkrun:
SELECT checkrun_name, COUNT(*) interest_invoice_count
FROM   ap.ap_invoice_relationships
GROUP  BY checkrun_name
ORDER  BY checkrun_name;
  • Joining to AP_INVOICES_ALL to retrieve invoice numbers for both sides of the relationship:
SELECT oi.invoice_num AS original_invoice,
       ri.invoice_num AS interest_invoice
FROM   ap.ap_invoice_relationships r,
       ap.ap_invoices_all oi,
       ap.ap_invoices_all ri
WHERE  oi.invoice_id = r.original_invoice_id
AND    ri.invoice_id = r.related_invoice_id;

Because the table is narrow and indexed, it is inexpensive to query and is commonly embedded in Payables interest audit reports and data-extract pipelines.

Related Objects

  • AP_INVOICES_ALL — the principal parent table. RELATED_INVOICE_ID and ORIGINAL_INVOICE_ID both carry foreign keys to AP_INVOICES_ALL, so every row here points to two invoice records.
  • AP_INVOICE_RELATIONSHIPS_PK — the documented primary key constraint over (RELATED_INVOICE_ID, ORIGINAL_INVOICE_ID).
  • AP_INVOICE_RELATIONSHIPS_U1 — the unique index over (ORIGINAL_INVOICE_ID, ORIGINAL_PAYMENT_NUM, RELATED_INVOICE_ID, CHECKRUN_NAME).
  • AP_PAYMENT_SCHEDULES_ALL — holds the payment schedules for both the original and interest invoices, useful when reconciling amounts.
  • AP_CHECKS_ALL — links payment and checkrun details referenced by ORIGINAL_PAYMENT_NUM and CHECKRUN_NAME.
  • AP_INVOICE_PAYMENTS_ALL — records the payment application for the interest invoice and its originating invoice.

These objects together support end-to-end tracing from an overdue invoice through interest generation, payment application, and final disbursement.