Account Payables

Oracle tables involved with AP Payments:Oracle Apps Functional

By Jag - April 16, 2014
This article talks about the major tables involved in making single and batch payments.
Single Payment
Payment is created in AP_CHECKS_ALL
1
2
3
4
5
SELECT *
  FROM ap_checks_all
 WHERE org_id = 360
   AND check_number = 9765   -- Document Num field in payment from
   AND doc_sequence_value = 71200004   -- Voucher Num field in payment form
The AP_INV_SELECTION_CRITERIA_ALL table contains the invoices selected by the payment
1
2
-- Get the Checkrun_name value from ap_checks_all. This is the payment name
select * from ap_inv_selection_criteria_all where checkrun_name = 'Quick Payment: ID=452069'
The AP_INVOICE_PAYMENTS_ALL table contains the payment details made for each invoice. For instance if an invoice is adjusted before making the payment, the details go into this table.
1
2
-- Get the Check_id value from ap_checks_all. This is the invoice selected for payment
select * from ap_invoice_payments_all where check_id = 452069
The invoice headers are stored in the AP_INVOICES_ALL table.
1
2
-- Get the invoice_id from ap_invoice_payments_all. See the full details of the invoice
select * from ap_invoices_all where invoice_id = 800541
The invoice lines/distributions are stored in AP_INVOICE_DISTRIBUTIONS_ALL table.
1
2
-- Get the invoice lines
select * from ap_invoice_distributions_all where invoice_id = 800541
Payment statuses
1
SELECT status_lookup_code FROM ap_checks_all
StatusMeaning
RECONCILEDPayment has been reconciled in Cash management from the bank statement
CLEAREDIf Allow Recon. Accounting is checked in Option form, then when clearing the check from Cash Management, the status will change to CLEARED
OVERFLOW 
RECONCILED ACCOUNTEDPayment has been reconciled in Cash management from the bank statement and the payment details has been transferred to General Ledger
CLEARED BUT UNACCOUNTEDIf Allow Recon. Accounting is NOT checked in option form, then when clearing the check from cash management, the status will changed to CLEARED BUT UNACCOUNTED and the ap_recon_distributions will not be created, hence the posting will post records to GL.
RECONCILED BUT UNACCOUNTEDWhen received bank statement and matched the check, the status will be changed to RECONCILED BUT UNACCOUNTED.
ISSUED 
VOIDED 
SPOILED 
NEGOTIABLE 
STOP INITIATEDStop Payment has been initiated for the payment
ISSUED 

Batch Payment
Invoice selection criteria for the Payment Batch: The AP_CHECKS_ALL table is populated when the batch is created. The status of the payment batch is NEW in payment batch form and UNSTARTED in the database.
1
2
-- Get the Checkrun_name value from ap_checks_all. This is the payment name
select * from ap_inv_selection_criteria_all where checkrun_name = 'TEST-PAYMENT-BATCH4'
After Select Invoices and Build is run check all the invoices that have been selected. The status of the payment batch is BUILT. The field,OK_TO_PAY_FLAG is set to Yes for payment and No for non payment. Oracle also updates the field DONT_PAY_REASON_CODE if it decides not make the payment. This can be done manually as well.
1
2
-- Get the checkrun_name. View all the invoices that have been selected by the batch
select * from ap_selected_invoices_all where checkrun_name = 'TEST-PAYMENT-BATCH4'
When the payment batch is Confirmed, the status of the batch becomes CONFIRMED. Now the payments have been generated. Query for the payments.
1
2
3
4
5
-- Get the payment details
SELECT *
  FROM ap_checks_all
 WHERE org_id = 103
   AND checkrun_name = 'TEST-PAYMENT-BATCH4'   -- Document Num field in payment from
Also check the invoices for the selected payments,
1
2
3
4
5
6
7
-- Get the Check_id value from ap_checks_all. This is the invoice selected for payment
SELECT *
  FROM ap_invoice_payments_all
 WHERE check_id IN (SELECT check_id
                      FROM ap_checks_all
                     WHERE org_id = 103 AND checkrun_name = 'TEST-PAYMENT-BATCH4' -- Document Num field in payment from
       )
Payment batch statuses
1
select status from ap_inv_selection_criteria_all

StatusMeaning
UNSTARTEDFor a new payment batch created
SELECTINGWhen the Select Invoice program is running
SELECTEDAfter Select Invoice program has completed
BUILDINGWhen the Build program is running
BUILTOnce the payment batch has been built
REBUILDINGWhen the Build program is running after adjusting payments to invoices/suppliers
FORMATTINGWhen the Format program is running
FORMATTEDAfter Format program has completed
CONFIRMINGWhen batch is being Confirmed
CONFIRMEDOnce the batch has been confirmed
CANCELLINGWhen batch cancel is in process
CANCELEDOnce the batch has been cancelled
QUICKCHECKFor single payments only
We have covered the main payment tables. Some of the other important AP tables are,
  • AP_BANK_BRANCHES
  • AP_BANK_ACCOUNTS_ALL
  • AP_HOLDS_ALL
  • AP_PAYMENT_HISTORY_ALL
  • AP_PAYMENT_DISTRIBUTIONS_ALL
  • AP_PAYMENT_SCHEDULES_ALL
  • AP_ACCOUNTING_EVENTS_ALL
  • AP_AE_HEADERS_ALL
  • AP_AE_LINES_ALL
  • AP_ENCUMBRANCE_LINES_ALL

  • Share:

You Might Also Like

0 comments