Search Results ap_payment_schedules_all

Overview

The AP_PAYMENT_SCHEDULES_ALL table is a core transactional table within the Oracle E-Business Suite Payables module (AP). It serves as the central repository for all scheduled payment information related to supplier invoices. For every invoice entered into the system, one or more payment schedule records are created to define the terms, amounts, and due dates for payment. This table is fundamental to the accounts payable process, driving payment processing, cash forecasting, discount management, and aging reports. Its multi-org structure, indicated by the "_ALL" suffix, allows it to store data for multiple operating units, a critical feature for global deployments in both releases 12.1.1 and 12.2.2.

Key Information Stored

The table's primary key is a composite of INVOICE_ID and PAYMENT_NUM, reflecting that a single invoice can have multiple scheduled payment lines (e.g., for an installment plan). Key columns include INVOICE_ID, which links to AP_INVOICES_ALL; PAYMENT_NUM, the sequence of the schedule line; DUE_DATE and GROSS_AMOUNT, which define the payment obligation; AMOUNT_REMAINING, which tracks the unpaid balance; and DISCOUNT_AMOUNT_AVAILABLE and DISCOUNT_DATE for managing early payment discounts. Other critical fields include PAYMENT_METHOD_CODE, which determines the payment format (e.g., CHECK, ELECTRONIC); HOLD_FLAG and RELEASE_OF_HOLD_DATE for payment holds; and EXTERNAL_BANK_ACCOUNT_ID, which links to the specific bank account for payment. The SCHEDULED_PAYMENT_STATUS column indicates the current state of the schedule line, such as 'UNAPPROVED', 'NEVER VALIDATED', or 'CONFIRMED'.

Common Use Cases and Queries

A primary use case is generating an accounts payable aging report. A typical query would join AP_PAYMENT_SCHEDULES_ALL with AP_INVOICES_ALL and POZ_SUPPLIERS to summarize AMOUNT_REMAINING grouped by aging buckets based on DUE_DATE. Another common scenario is identifying available discounts for early payment, querying for schedules where AMOUNT_REMAINING > 0, DISCOUNT_AMOUNT_AVAILABLE > 0, and the current date is on or before DISCOUNT_DATE. For payment batch creation, applications query for schedules meeting payment terms, bank account, and currency criteria where SCHEDULED_PAYMENT_STATUS is eligible for payment. A fundamental diagnostic query checks for payment schedules without a corresponding invoice: SELECT aps.invoice_id FROM ap_payment_schedules_all aps WHERE NOT EXISTS (SELECT 1 FROM ap_invoices_all ai WHERE ai.invoice_id = aps.invoice_id).

Related Objects

AP_PAYMENT_SCHEDULES_ALL has integral relationships with several key Payables tables, as documented in the ETRM metadata. Its primary parent is AP_INVOICES_ALL, linked via INVOICE_ID. It references AP_BATCHES_ALL via BATCH_ID and AP_BANK_ACCOUNTS_ALL via EXTERNAL_BANK_ACCOUNT_ID. Crucially, it is the parent table for AP_INVOICE_PAYMENTS_ALL, which stores actual payment transactions against each schedule line; the foreign key is on the composite columns (INVOICE_ID, PAYMENT_NUM). It also serves as a parent to the localization table JL_BR_AP_COLLECTION_DOCS_ALL using the same composite key. These relationships ensure data integrity from invoice entry through to final payment settlement.