Topic: Payables

Oracle Payables Query 0

Query to identify the various components that a Payables Invoice goes through a P2P process. Below query provides the details like Invoice, Supplier, Supplier Sites, Payment Method codes, terms, Distribution details, Payment schedules, Check details, Internal Bank Payment and Internal Bank details.

SELECT aia.invoice_num                 "Invoice Number",
       aps.vendor_name                 "Vendor Name" -- get vendor name from ap_suppliers table
       assa.vendor_site_code           "Vendor Site Code",
       aia.invoice_id                  "Invoice ID",
       aia.invoice_currency_code       "Currency Code",
       aia.invoice_amount              "Invoice Amount",
       aia.amount_paid                 "Amount Paid",
       aia.invoice_type_lookup_code    "Invoice Type", -- values are derived from ap_lookup_codes and lookup_type = 'INVOICE TYPE'.
                                                       --STANDARD, CREDIT, DEBIT, EXPENSE REPORT, PREPAYMENT, MIXED, RETAINAGE RELEASE are other Invoice Types
       aia.payment_method_lookup_code, -- values are derived from ap_lookup_codes table and lookup_type = 'PAYMENT METHOD'
                                       -- Check, Clearing, Electronic, Wire
       aia.terms_id                    "Terms ID", -- get terms name from ap_terms table
       aia.pay_group_lookup_code,                  -- values are derived from the fnd_lookup_values_vl and lookup_type = 'PAY GROUP'
       aia.org_id                      "Operating Unit ID", -- values are derived from hr_operating_units table - organization_id column
       aia.gl_date                     "GL Date",
       ail.line_number                 "Line Number",
       ail.line_type_lookup_code       "Line Type", -- values are derived from ap_lookup_codes and lookup_type = 'INVOICE LINE TYPE'
                                                    -- Item, Freigh, Miscellaneous, Tax
       ail.amount                      "Line Amount",
       aid.dist_code_combination_id    "Distribution Code Comb ID", -- segment information can be derived from gl_code_combinations_kfv
       aid.accounting_event_id         "Invoice Accounting Event ID", -- will be used to link to SLA tables
       apsa.amount_remaining           "Remaining Invoice Amount",
       apsa.due_date                   "Due Date",
       aipa.accounting_event_id        "Payment Accounting Event ID",
       aca.amount                      "Check Amount",
       aca.check_number                "Check Number",
       aca.checkrun_name               "Payment Process Request",
       idpa.document_amount            "Payment Amount",
       idpa.pay_proc_trxn_type_code    "Payment Processing Document",
       idpa.calling_app_doc_ref_number "Invoice Number",
       ipa.paper_document_number       "Payment Number",
       ipa.payee_name                  "Paid to Name",
       ipa.payee_address1              "Paid to Address",
       ipa.payee_city                  "Paid to City",
       ipa.payee_postal_code           "Payee Postal Code",
       ipa.payee_state                 "Payee State",
       ipa.payee_country               "Payee Country",
       ipa.payment_profile_acct_name   "Payment Process Profile",
       ipa.int_bank_name               "Payee Bank Name",
       ipa.int_bank_number             "Payee Bank Number",
       ipa.int_bank_account_name       "Payee Bank Account Name",
       ipa.int_bank_account_number     "Payee Bank Account Number"
  FROM ap_invoices_all               aia,
       ap_invoice_lines_all          ail,
       ap_invoice_distributions_all  aid,
       ap_suppliers                  aps,
       ap_supplier_sites_all         assa,
       ap_payment_schedules_all      apsa,
       ap_invoice_payments_all       aipa,
       ap_checks_all                 aca,
       iby_docs_payable_all          idpa,
       iby_payments_all              ipa
 WHERE     1 = 1
       AND aia.invoice_id = ail.invoice_id
       AND aia.invoice_id = aid.invoice_id
       AND aia.vendor_id = aps.vendor_id
       AND aps.vendor_id = assa.vendor_id
       AND aia.invoice_id = apsa.invoice_id
       AND aia.invoice_id = aipa.invoice_id
       AND aipa.check_id = aca.check_id
       AND aia.invoice_id = idpa.calling_app_doc_unique_ref2
       AND idpa.calling_app_id = 200
       AND aps.party_id = idpa.payee_party_id
       AND ipa.payment_id = idpa.payment_id
       AND aps.segment1 = ipa.payee_supplier_number
       -- and assa.vendor_site_id = ipa.supplier_site_id
       AND assa.org_id = aia.org_id
       AND aca.vendor_site_id = assa.vendor_site_id
       AND invoice_num = p_invoice_num; -- Enter Invoice number here. 

Oracle Payables Invoice Interface – Validate Vendor Number 1

During the Payables Invoice Interface process, one of the validations involve validating the Vendor Number for which the Invoice is being created for. The Validation logic checks in the system to see if a vendor already exists for that vendor number. If Yes, then an Invoice can be created for that Supplier. Else, since that Supplier doesn’t exist, Invoices cannot be created against that supplier and the validation should result in error.

   p_vendor_num   ap_suppliers.segment1%TYPE;
   l_vendor_id    ap_suppliers.vendor_id%TYPE;

   CURSOR cur_vendor_num_chk (cp_vendor_num ap_suppliers.segment1%TYPE)
      SELECT vendor_id
        FROM ap_suppliers
       WHERE segment1 = cp_vendor_num;
   OPEN cur_vendor_num_chk (p_vendor_num);

   FETCH cur_vendor_num_chk INTO l_vendor_id;

   IF (cur_vendor_num_chk%FOUND)
      DBMS_OUTPUT.put_line (
            'Supplier exists in the system '
         || CHR (10)
         || ' Thus, Invoice Can be created for this vendor.');
      DBMS_OUTPUT.put_line (
         'Supplier does not exist and Invoice cannot be created using this Supplier');
   END IF;

   CLOSE cur_vendor_num_chk;
      DBMS_OUTPUT.put_line ('Error while validation: ' || SQLERRM);

Oracle Payables Invoice Interface – Validating Invoice Number 1

During the Payables Invoice Interface process, one of the validations involve validating the Invoice Number. If the Invoice Number already exists in the system for a given Vendor in a given Operating Unit, then another Invoice cannot be created with the same Invoice Number. The following logic can be used for validation:

   p_inv_number   ap_invoices_all.invoice_num%TYPE;
   p_vendor_id    ap_invoices_all.vendor_id%TYPE;
   p_org_id       ap_invoices_all.org_id%TYPE;
   l_inv_id       ap_invoices_all.invoice_id%TYPE;

   CURSOR cur_inv_num_chk (
      cp_inv_number    ap_invoices_all.invoice_num%TYPE,
      cp_vendor_id     ap_invoices_all.vendor_id%TYPE,
      cp_org_id        ap_invoices_all.org_id%TYPE)
      SELECT invoice_id
        FROM ap_invoices_all
       WHERE     invoice_number = cp_inv_number
             AND vendor_id = cp_vendor_id
             AND org_id = cp_org_id;
   OPEN cur_inv_num_chk (p_inv_number, p_vendor_id, p_org_id);

   FETCH cur_inv_num_chk INTO l_inv_id;

   IF (cur_inv_num_chk%FOUND)
      DBMS_OUTPUT.put_line (
            'Invoice already exists in the system'
         || CHR (10)
         || ' Thus, Invoice Cannot be created with this Invoice Number
                        for this Vendor '
         || p_vendor_id
         || ' in this organization: '
         || p_org_id);
      DBMS_OUTPUT.put_line (
         'Invoice does not exist and can be created using this Invoice Number');
   END IF;

   CLOSE cur_inv_num_chk;
      DBMS_OUTPUT.put_line ('Error while validation: ' || SQLERRM);

Understanding AP Invoice Interface 2

Objective: The primary objective of this post is to understand why and how to import data into Oracle Applications in order to create Payables Invoices.

As one can understand the difference between a conversion process and an Interface – A conversion process is conducted once during a cycle to import legacy data into the Oracle, whereas an Interface is used on a daily basis to import external data into Oracle.

Many a times, users are given the task of importing external data into Oracle and create AP Invoices. Invoice data comes from sources including:

  • EDI invoices from your suppliers that you load through Oracle e-Commerce Gateway.
  • Supplier invoices that you transfer through the Oracle XML Gateway.
  • Invoices that you load using Oracle SQL*Loader lease invoices from Oracle Property Manager.
  • Lease payments from Oracle Assets, credit card transaction data that you load using the Credit Card Invoice Interface Summary.
  • Invoices that you enter through the Invoice Gateway.

An Interface process goes through the following stages. A brief overview of the process is as below.


Step1: Loading into Staging table

Also, the inbound data can be loaded into the staging table in either of the following ways:

  1.  Interfaced into the staging table using middleware technologies like webmethods or SOA.
  2. The data can be created and placed in a csv file and can be loaded into the staging table using SQL Loader. (Please check my sql loader tutorial for understanding this process).

Usually the data and the staging tables are designed in such a way that one row in the staging table has all the header and Line Information associated with the Payables Invoice. Also, the staging table is designed in such a way that it mimics the columns in the AP Invoice Header and Line tables. (AP_INVOICES_ALL and AP_INVOICE_LINES_ALL) tables.

Step 2: Validating Staging table

Once, the data is placed in the staging table, the data here goes through validation. Some of the basic validations that are required for AP Invoice Interface are:

  1. Validate Invoice Number.
  2. Validate Vendor Name.
  3. Validate Vendor Number
  4. Validate Invoice Type
  5. Validate Invoice Amount
  6. Validate Payment Terms.
  7. Validate Payment Method.
  8. Validate GL Date.
  9. Validate Payment Reason Code.
  10. Validate Line Type.
  11. Validate Sum of Line Amount
  12. Validate POET (Project, Organization, Expenditure, Task).
  13. Validate Tax Code.
  14. Validate Operating Unit.
  15. Validate Exchange Rate.
  16. Validate Exchange Type.
  17. Validate Bank Account.
  18. Validate Tax Location and Tax Code.

And depending on the data / columns in the staging table – more validations may be required based on the current requirement.

During the validation process, each record in the staging table is marked either as a Processed record or a failed record, depending on whether the record passed validation or not.

If the record fails validation, then the corresponding process_flag (or whatever the name of the column that you give) column in the staging table is marked as E (error). Else, if the record passes validation, then the process_flag for that record is marked as V (Validated)

Step 3: Loading into Interface Table. 

All the records from the staging table that has the process_flag as ‘V‘ are then inserted into the Interface tables.

All the Header information from the staging table is passed to AP_Invoices_Interface table and all the Line Information is passed to AP_Invoice_lines_interface table.

Some of the important columns that have to be populated in the Interface tables are:


INVOICE_ID Invoice identifier. Must be unique.
INVOICE_NUM Supplier invoice number. Validation: must be unique for the supplier site.
INVOICE_TYPE_LOOKUP_CODE Type of invoice. Use the Manage Payables Lookups task, lookup type INVOICE TYPE to identify valid values.
INVOICE_DATE Invoice date used to calculate due date per payment terms. Date format: YYYY/MM/DD.
PO_NUMBER Document number used to request a seller supply a product or service for payment. Use the Manage Purchase Orders task to identify valid values.
VENDOR_ID Supplier identifier. If the identifier is not available, use the VENDOR_NAME column. Validated against POZ_SUPPLIERS.VENDOR_ID.
VENDOR_NUM Unique number to identify a supplier. Use the Manage Suppliers task to identify valid values.
VENDOR_NAME Supplier name. Use the Manage Suppliers task to identify valid values.
VENDOR_SITE_ID Supplier site identifier. If the identifier is not available, use the VENDOR_SITE_CODE column. Validated against POZ_SUPPLIER_SITES_ALL.VENDOR_SITE_ID.
VENDOR_SITE_CODE Physical location of the supplier. Use the Manage Suppliers task to identify valid values.
INVOICE_AMOUNT Invoice amount in transaction currency. Should be positive for standard and prepayment invoices. Should be negative for credit memos.
INVOICE_CURRENCY_CODE Currency of invoice. Use the Manage Currencies task to identify valid values. Use the three character ISO currency code. Example: US Dollars is USD.
EXCHANGE_RATE Exchange rate used for foreign currency invoices. User entered conversion rate. Not validated.
EXCHANGE_RATE_TYPE Exchange rate type for foreign currency invoices. Use the Manage Conversion Rate Types task to identify valid values. Validated against GL_DAILY_CONVERSION_TYPES.CONVERSION_TYPE.
EXCHANGE_DATE Date exchange rate is effective. Usually the accounting date of the transaction. Date format: YYYY/MM/DD.
TERMS_ID Payment terms identifier. If the identifier is not available, use the TERMS_NAME column. Validated against AP_TERMS_TL.TERM_ID.
TERMS_NAME Name of payment term for an invoice. Use the Manage Payment Terms task to identify valid values.
DESCRIPTION Invoice description. Free text field. Not validated.


INVOICE_ID Invoice identifier. Must be unique.
INVOICE_LINE_ID Unique number for each invoice line of an invoice. If NULL, populated by AP_INVOICE_LINES_INTERFACE_S sequence.
LINE_NUMBER Invoice line number. Must be a positive integer. Must be unique per invoice.
LINE_TYPE_LOOKUP_CODE Type of invoice line. Use the Manage Payables Lookups task, lookup type INVOICE LINE TYPE to identify valid values.
LINE_GROUP_NUMBER Line group number. Used to prorate charges across a group of lines. Must be a positive integer.
AMOUNT Amount of the invoice line. Validated against the invoice type. Example: standard invoices must have positive amounts. Amount precision is validated against the currency.
ACCOUNTING_DATE Date when the invoice line is to be accounted. Date format: YYYY/MM/DD.
DESCRIPTION Description of the invoice line. Free text field. Not validated.

Step 4: Submit Standard Program.

Once the data is inserted into the Interface table, submit the “Payables Open Interface Import” standard import program to import the Interface data and create the Payables Invoices.

l_request_id := FND_REQUEST.SUBMIT_REQUEST     ( application =>  application_short_name
                                               , program     => 'APXIIMPT' 
                                               , description => NULL
                                               , start_time  => SYSDATE
                                               , sub_request => FALSE
                                               , argument1   => org_id
                                               , argument2   => p_source
                                               , argument3   => NULL
                                               , argument4   => NULL
                                               , argument5   => NULL
                                               , argument6   => NULL
                                               , argument7   => NULL
                                               , argument8   => NULL
                                               , argument9   => NULL
                                               , argument10  => NULL -- Debug Flag
                                               , argument11  => NULL
                                               , argument12  => NULL
                                               , argument13  => NULL --g_user_id
                                               , argument14  => NULL --g_login_id

Step 5: Error Handling in Interface tables.

When data is being migrated from Interface tables to the base tables in the above step – some of the data in the Interface tables may get into error and will not be used to create the Invoices. These records can be found in the AP_INTERFACE_REJECTIONS table.

The join between the AP_Interface_Rejections and the two Interface tables is: 

select air.reject_lookup_code, air.* 
from ap_interface_rejections air 
    ,ap_invoices_interface ait
where air.parent_id = ait.invoice_id 
and air.parent_table = 'AP_INVOICES_INTERFACE'
select air.reject_lookup_code, air.* 
from ap_interface_rejections air 
    ,ap_invoice_lines_interface ail
where air.parent_id = ail.invoice_line_id 
and air.parent_table = 'AP_INVOICE_LINES_INTERFACE'

Step 6: Verify the created Invoices

Login into Oracle Applications and navigate to Payables Superuser to query the created Invoices from the front end application and in the following base tables:

select * from ap_invoices_all

select * from ap_invoice_lines_all