Account Payables

Account Payables
Account Payable (AP) is a finance module to keep the records of invoices and payments which has to pay to supplier/ vendor with respect to PO for goods or for services.



Backend base tables related to invoice and payment

1. AP_INVOICES_ALL : This is header table which store invoice number, invoice amount, payment terms, supplier etc. Main columns are:

  • INVOICE_ID
  • INVOICE_NUM
  • INVOICE_DATE
  • INVOICE_AMOUNT
  • VENDOR_ID
  • VENDOR_SITE_ID

SELECT invoice_num, invoice_date, invoice_amount
FROM ap_invoices_all;

2. AP_INVOICE_LINES_ALL : This is line of invoice header which stores amount, tax, po links, trade operation link etc. INVOICE_ID column is use to join AP_INVOICES_ALL and AP_INVOICE_LINES_ALL Tables. Main columns are:

  • LINE_NUMBER
  • AMOUNT
  • LINE_TYPE_LOOKUP_CODE
  • DISCARDED_FLAG
  • INVOICE_ID

SELECT invoice_num, invoice_date, ail.line_type_lookup_code,line_number , ail.amount
FROM ap_invoices_all aia, ap_invoice_lines_all ail
WHERE aia.invoice_id = ail.invoice_id
AND aia.invoice_num = 'Test12345';

3. AP_INVOICE_DISTRIBUTIONS_ALL: This table is used to store accounting details like accounting date, posted flag, account code etc. INVOICE_ID and INVOICE_LINE_NUMBER are use to join with AP_INVOICE_LINES_ALL and AP_INVOICE_DISTRIBUTIONS_ALL tables. Main columns are:

  • ACCOUNTING_DATE
  • LINE_TYPE_LOOKUP_CODE
  • CODE_COMBINATION_ID
  • INVOICE_LINE_NUMBER
  • POSTED_FLAG

4. AP_TERMS_B : Store Payment information.

5. AP_TERMS_TL : Store payment information like term name. Data stores in multiple languages like US, AR etc. 

SELECT a.term_id, 
               b.name, 
               a.start_date_active,
               a.end_date_active, 
       a.enabled_flag
  FROM  ap_terms_b a, 
              ap_terms_tl b
 WHERE a.term_id = b.term_id
  AND b.language = 'US';

6. POZ_SUPPLIERS_V: Stores supplier details. Main columns are:

  • VENDOR_NAME
  • SEGMENT1: Stores vendor number
  • VENDOR_ID

7. POZ_SUPPLIER_SITES_V: Stores supplier site details. Main columns are:

  • VENDOR_ID
  • VENDOR_SITE_ID
  • VENDOR_SITE_CODE
  • PRC_BU_ID

8. AP_CHECKS_ALL: Stores payment details. Main columns are:

  • CHECK_ID
  • AMOUNT
  • VOID_DATE

9. AP_INVOICE_PAYMENTS_ALL: Stores details about the payment done against invoice. INVOICE_ID and CHECK_ID stores. Main columns are:

  • INVOICE_ID
  • CHECK_ID
  • ACCOUNTING_DATE
  • AMOUNT
  • POSTED_FLAG

10. AP_PAYMENT_SCHEDULES_ALL: This table stores the information about the schedule payment of an invoice. Main columns are:

  • INVOICE_ID
  • PAYMENT_NUM
  • AMOUNT_REMAINING
  • GROSS_AMOUNT

In this section, you will find various AP related SQL_QUERIES from below links:

  1. AP Invoice Query
  2. Supplier Aging/ AP Aging Query (Based on Base Tables)
  3. Supplier Aging Query based on Subledger Accounting
  4. Supplier Prepayment Aging based on Subledger
  5. Procure to Pay (P2P) Query
  6. Supplier Contact Query
  7. Customer Refund Query
  8. Customer Refund Applied Query
  9. Supplier Query
  10. Supplier Bank Tables and Query


Subscribe to: Comments (Atom)