Search Results ap_invoice_distributions_all

Overview

CE_AP_FC_DUE_INVOICES_V is a Cash Management (CE) view owned by the APPS schema and defined with VALID status in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to serve as a forecasting source for Payables invoices, stratified by due date. Within the Cash Management cash forecast workflow, this view supplies the candidate invoice population that the forecasting engine evaluates when projecting future cash outflows.

The view is not a transactional object; it is a read-only projection designed for reporting and integration. It presents open, unpaid invoice payment schedules in a normalized shape so that the forecast engine, or any downstream report, can derive expected disbursement dates and amounts without re-implementing the complex filter logic that distinguishes valid forecast candidates from prepayments, payment requests, unmatched invoices, and already-paid schedules.

Underlying Base Objects

The documented base objects for this view are:

• AP_INVOICES_ALL — invoice header information, including currency, payment currency, exchange rate, invoice type, pay group, project, and vendor site.
• AP_INVOICE_DISTRIBUTIONS_ALL — invoice distribution lines, used only in the filter predicates that determine whether an invoice is a valid forecast candidate.
• AP_PAYMENT_SCHEDULES_ALL — the driving table, supplying payment number, amount remaining, due date, payment priority, hold flag, and payment status.
• AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL — supplier and supplier site attributes, joined for org consistency and vendor type classification.
• AP_SYSTEM_PARAMETERS_ALL — provides the base (functional) currency code per operating unit, used to convert the amount remaining into the ledger currency.
• AP_UTILITIES_PKG — the PL/pgSQL package supplying AP_ROUND_CURRENCY for currency rounding.
• HZ_PARTIES and HZ_PARTY_SITES — TCA party objects used in the second UNION ALL branch of the view text.

The definition is a UNION ALL of two branches. The first branch joins the supplier and supplier-site tables to derive VIP.VENDOR_TYPE_LOOKUP_CODE. The second branch substitutes the literal '-1' for the vendor type and instead joins through HZ_PARTIES and HZ_PARTY_SITES, accommodating parties that are not represented in the legacy AP supplier tables. Both branches apply the same core predicates: the payment schedule is not fully paid (PAYMENT_STATUS_FLAG != 'Y'), the invoice is not a prepayment or payment request, at least one distribution exists, and no distribution has a match status other than 'A' (matched).

Key Columns

• INVOICE_ID — the Payables invoice identifier.
• PAYMENT_NUM — the payment schedule line number, identified in the documentation as column 2.
• AMOUNT_REMAINING (NVL to 0) — the outstanding balance on the payment schedule.
• Base currency amount — AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AMOUNT_REMAINING * NVL(EXCHANGE_RATE, 1), BASE_CURRENCY_CODE), the amount remaining converted to the functional base currency.
• PAYMENT_CURRENCY_CODE — the invoice payment currency.
• PAYMENT_PRIORITY (NVL to 99) — schedule priority; 99 signals no priority defined.
• VENDOR_TYPE_LOOKUP_CODE (NVL to '-1') — supplier type classification.
• PAY_GROUP_LOOKUP_CODE (NVL to '-1') — the invoice pay group.
• DUE_DATE — the payment schedule due date, the primary forecast time dimension.
• ORG_ID — the operating unit, propagated from the invoice and used for multi-org security.
• PROJECT_ID — the project associated with the invoice, where applicable.
• HOLD_FLAG (NVL to 'N') — indicates whether the schedule is on hold.
• INVOICE_NUM — the invoice number.
• A derived key of INVOICE_ID || 'X' || PAYMENT_NUM, providing a unique schedule identifier for downstream processing.

Common Use Cases and Queries

The primary use case is cash forecasting: projecting expected AP disbursements by due date for a given operating unit. A second common use is reconciliation and reporting of open invoices eligible for payment, including hold analysis and pay-group aggregation.

A representative query lists forecast candidates for one operating unit over a date range:

• SELECT invoice_num, payment_num, due_date, amount_remaining, payment_currency_code, payment_priority, hold_flag FROM ce_ap_fc_due_invoices_v WHERE org_id = :p_org_id AND due_date BETWEEN :p_start AND :p_end ORDER BY due_date, payment_priority;

A second query aggregates forecasted outflow by currency and week, using the base-currency amount to normalize across currencies:

• SELECT payment_currency_code, TRUNC(due_date, 'IW') AS due_week, SUM(amount_remaining) AS scheduled_amount FROM ce_ap_fc_due_invoices_v WHERE org_id = :p_org_id GROUP BY payment_currency_code, TRUNC(due_date, 'IW') ORDER BY due_week;

A third query isolates schedules on hold, which the forecast engine generally excludes or flags:

• SELECT invoice_num, payment_num, due_date, amount_remaining FROM ce_ap_fc_due_invoices_v WHERE org_id = :p_org_id AND hold_flag = 'Y';

Because the view reconstructs its filters from AP_INVOICE_DISTRIBUTIONS_ALL, AP_INVOICES_ALL, and AP_PAYMENT_SCHEDULES_ALL, users searching for AP_INVOICE_DISTRIBUTIONS_ALL will find that this view is one of the standard surfaces through which distribution-level match-status logic influences Cash Management forecasting. Queries against the view should always be constrained by ORG_ID to respect operating unit security and to keep the underlying UNION ALL branches performant.