In this article you will get all the AP tables of Oracle Apps R12, which can be used to retrieve all of the AP/Payables related data from the database. There are numerous critical tables in the AP module that store various types of information, such as Supplier Invoice Information and Payment Information. The most essential AP tables in Oracle Aps R12 are listed here.
AP_INVOICES_ALL
This table contains all the information related invoice headers and the workbench.
AP_INVOICE_LINES_ALL
SAME AS INVOICE WORKBENCH, THIS TABLE STORES ALL INVOICE LINES INFORMATION.
AP_INVOICE_DISTRIBUTIONS_ALL
STORES ALL INVOICE LINES INFORMATION, SIMILAR TO INVOICE WORKBENCH.
THIS TABLE CONTAINS ALL INFORMATION RELATED TO INVOICE DISTRIBUTIONS (ALL GL ACCOUNT INFORMATION).
AP_SUPPLIERS
WE NEED TO GO TO AP SUPPLIERS TO GET ALL OF THE SUPPLIER INFORMATION FOR THE INVOICE. This is the main table for suppliers.
AP_SUPPLIER_SITES_ALL
WE NEED TO GO TO AP SUPPLIER SITES ALL TO GET ALL OF THE SUPPLIER SITE INFORMATION FOR THE INVOICE. We’ll receive supplier sites and addresses in this table.
AP_PAYMENT_SCHEDULES_ALL
THIS TABLE, LIKE THE INVOICE WORKBENCH, STORES INFORMATION FROM THE INVOICE PAYMENT SCHEDULE TAB. This table contains all information concerning payment due dates for example, when do we have to make the payments, This database also keeps track of discount information.
AP_PAYMENT_HISTORY_ALL
It also saves the payment’s maturity history for future dates. Once a future dated payment matures, i.e. becomes negotiable, a row is added to the table for each future dated payment. When a payment is cleared or not cleared, a row for the payment is placed into this table.
AP_BATCHES_ALL
If you’re submitting payables invoices in batches, this table will show you the batch details.
AP_CHECKS_ALL
THIS TABLE CONTAINS ALL PAYMENT INFORMATION, FOR EXAMPLE, ALL PAYMENT INFORMATION, CHECK NUMBERS, BANKS DETAILS, AND VENDOR INFORMATION.
AP_INVOICE_PAYMENTS_ALL
IF YOU WANT TO SEE ONE CHECK DONE AGAINST WHICH PAYMENT, CLICK HERE.
Query to get Payment against Invoice period wise in Oracle Apps R12
SELECT gcc.concatenated_segments gl_account, ap.gl_date,
‘Payables’ payable_source, ap.invoice_id, ap.invoice_currency_code,
ap.invoice_amount, ap.exchange_rate, ap.base_amount, ap.vendor_id,
aps.segment1 vendor_number, aps.vendor_name, ap.invoice_num,
ac.check_id, ac.check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_payments_all aip,
ap_checks_all ac
WHERE ap.accts_pay_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
AND apss.vendor_site_id(+) = ap.vendor_site_id
AND aip.invoice_id(+) = ap.invoice_id
AND aip.check_id = ac.check_id(+)
AND fnd_date.string_to_date (ap.gl_date, ‘DD-MON-RR’)
BETWEEN fnd_date.string_to_date (’01-‘ || :p_period, ‘DD-MON-RR’)
AND LAST_DAY (fnd_date.string_to_date (’01-‘ || :p_period,
‘DD-MON-RR’
)
)
AND invoice_num = ‘5001234’