Introduction:

In this guide, we’ll explain how to create AP invoice in oracle apps r12 using Open interface tables. and because there is no public API to create AP invoice in oracle APPS R12 is available only through an interface

Oracle AP open interface tables are used to create new AP invoices, whether from supplier invoices or imported from external systems.

AP Interface Tables In Oracle APPS R12 :

There are two interface tables in AP:

• AP_INVOICES_INTERFACE: Stores header information about invoices that you create or load for import.
• AP_INVOICE_LINES_INTERFACE: Stores lines & distribution information about invoices. Use it in conjunction with the AP_INVOICE_INTERFACE table.
  Note: during the payable import process, one row in this table may create more than one invoice distribution line. For example, enter a Tax line in this table and prorate it across multiple Item lines during Open Interface Import. The system may create multiple Tax invoice distributions based on the single Tax line in this table.

Payables Open Interface Error Tables In Oracle APPS R12 :

There are two error interface tables in AP:

• AP_INTERFACE_REJECTIONS: stores information about invoice data from the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables, which could not be processed by Payables Open Interface Import.
  Note: you can purge data in this table by using the Payables Open Interface Purge.
• AP_INTERFACE_CONTROLS: This temporary table holds control information about segregated data in the AP_INVOICES_INTERFACE table during the Payables Open Interface Import. The table ensures that each import must be unique concerning the combination of SOURCE and GROUP_ID. This allows the submission of multiple imports at the same time.
  Note: Oracle Payables application deletes the information from this table when you complete an import.

Delete / Purge Records From Payable Interface Tables :

Use the Payables Open Interface Purge Program to purge records from the Payables Open Interface tables (AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE). One parameter determines how Oracle deletes the data from Payable, which is called (Purge All), open interface tables.
Select Yes. If we want Oracle to delete all the records with the status ‘PROCESSED’
Select No. If we want Oracle to delete only records that have been successfully imported.

Goal:

Create new AP invoice using interface tables
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

Steps:

• Populate required information into the Payable invoice header (AP_INVOICES_INTERFACE).
• Populate required information into payable invoice lines, which is (AP_INVOICE_LINES_INTERFACE)
• Run the Payable Open Interface Import program to import invoices from the AP interface to AP base tables.

Note: for more information about all required and optional values to populate both tables, you can refer to this link from oracle by clicking here.

Forms Or Pages:

Example:

Required Columns:

1. AP_INVOICES_INTERFACE

INVOICE_ID (Required): Unique identifier for this invoice within this batch. The same value should be populated in the invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.

INVOICE_NUM (Required): Enter the invoice number that needs to be assigned to the invoice created in Payables from this record.

INVOICE_TYPE_LOOKUP_CODE (Optional): Type of invoice: Credit or Standard.

INVOICE DATE (Optional): Date of the invoice. If you do not enter a value, the system uses the date you submit Payables Open Interface Import as the invoice date.

PO_NUMBER (Optional): Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if the invoice is to be matched with a purchase order.

VENDOR_ID & VENDOR_SITE_ID (Required): VENDOR_ID is unique identifier for a supplier and VENDOR_SITE_ID is Internal supplier site identifier. Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.

VENDOR_NUM & VENDOR_NAME (Optional) : Supplier number and name. You must identify the supplier by entering a value for one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER.
INVOICE_AMOUNT (Required): Amount of the invoice.

INVOICE_CURRENCY_CODE (Optional): Currency code for the invoice. If you want to create foreign currency invoices, enter a currency code that is different from your functional currency.

EXCHANGE_RATE (Optional): This column is required if you enter a foreign currency code in the INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE.

TERMS_ID (Optional) : Internal identifier for the payment terms.

DESCRIPTION (Optional): Enter the description that you want to assign to the invoice created from this record.

SOURCE (Required): Source of the invoice data. If you import EDI invoices from the Oracle EDI Gateway, the source is EDI Gateway. For invoices, you import using SQL*Loader, use a QuickCode with the type Source that you have defined in the QuickCodes window in Payables.

2. AP_INVOICE_LINES_INTERFACE

INVOICE_ID (Required): Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table.

INVOICE_LINE_ID: This value is not required. You can enter a unique number for each invoice line of an invoice.

LINE_NUMBER (Optional): You can enter a unique number to identify the line.

LINE_TYPE_LOOKUP_CODE (Required): Enter the lookup code for the type of invoice distribution that you want Payables Open Interface Import to create from this record. The code you enter must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in the AP_LOOKUP_CODES table.

AMOUNT (Required): The invoice distribution amount. If you match a purchase order, the AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice distributions does not equal the amount of the invoice with the same INVOICE_ID, then Payables Open Interface Import will reject the invoice.

AP Open Interface populate example:

DECLARE
  l_po_number             po_headers_all.segment1%TYPE;
  l_payment_method_code   ap_invoices_all.payment_method_code%TYPE := :payment_method_code;
  l_org_id                NUMBER := :p_org_id;
  l_vendor_id             po_headers_all.vendor_id%TYPE;
  l_vendor_site_id        po_headers_all.vendor_site_id%TYPE;
  l_invoice_id            ap_invoices_all.invoice_id%TYPE;
  v_request_id            NUMBER;
  l_boolean               BOOLEAN;
  l_phase                 VARCHAR2 (200);
  l_status                VARCHAR2 (200);
  l_dev_phase             VARCHAR2 (200);
  l_dev_status            VARCHAR2 (200);
  l_message               VARCHAR2 (200);
  g_invalid_ex            EXCEPTION;
  l_error_seq             VARCHAR2 (200);
  l_error_msg             VARCHAR2 (4000);
BEGIN
  l_po_number := :p_po_number;
  l_payment_method_code := :payment_method_code;
  l_org_id := :p_org_id;
  l_invoice_id := ap_invoices_interface_s.nextval;

  BEGIN
    SELECT poh.vendor_id, poh.vendor_site_id
    INTO   l_vendor_id, l_vendor_site_id
    FROM   po_headers_all poh, po_lines_all pol
    WHERE  poh.segment1 = l_po_number
    AND    poh.po_header_id = pol.po_header_id;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_seq := '01';
      l_error_msg := sqlerrm;
      RAISE g_invalid_ex;
  END;

  BEGIN
    INSERT INTO ap_invoices_interface (invoice_id
                                      ,invoice_num
                                      ,vendor_id
                                      ,vendor_site_id
                                      ,invoice_amount
                                      ,invoice_currency_code
                                      ,invoice_date
                                      ,description
                                      ,source
                                      ,org_id
                                      ,payment_method_code)
    VALUES      (l_invoice_id
                ,'ORAASK-' || l_invoice_id
                ,l_vendor_id
                ,l_vendor_site_id
                ,25000
                ,'USD'
                ,to_date ('05-MAY-2012', 'dd-mon-yyyy')
                ,'This Invoice is created by ORAASK.COM'
                ,'MANUAL INVOICE ENTRY'
                ,l_org_id
                ,l_payment_method_code);
  END;

  BEGIN
    INSERT INTO ap_invoice_lines_interface (invoice_id
                                           ,line_number
                                           ,line_type_lookup_code
                                           ,amount)
    VALUES      (l_invoice_id
                ,1
                ,'ITEM'
                ,25000);
  END;

  COMMIT;

  BEGIN
    mo_global.init ('SQLAP');
    fnd_global.apps_initialize (user_id => 1318, resp_id => 50554, resp_appl_id => 200);
    --    fnd_request.set_org_id (204);
    --    mo_global.set_policy_context ('S', 204);

    v_request_id :=
      fnd_request.submit_request (application   => 'SQLAP'
                                 ,program       => 'APXIIMPT'
                                 ,description   => ''
                                 ,start_time    => NULL
                                 ,sub_request   => FALSE
                                 ,argument1     => 204
                                 ,argument2     => 'MANUAL INVOICE ENTRY'
                                 ,argument3     => NULL
                                 ,argument4     => NULL
                                 ,argument5     => NULL
                                 ,argument6     => NULL
                                 ,argument7     => NULL
                                 ,argument8     => 'N'
                                 ,argument9     => 'Y');
    COMMIT;

    IF v_request_id > 0 THEN
      l_boolean :=
        fnd_concurrent.wait_for_request (v_request_id
                                        ,20
                                        ,0
                                        ,l_phase
                                        ,l_status
                                        ,l_dev_phase
                                        ,l_dev_status
                                        ,l_message);
    END IF;

    dbms_output.put_line ('********************************');

    IF (l_status = 'Normal') THEN
      dbms_output.put_line ('Invoice Created Successfully, Please see the output of Payables OPEN Invoice Import program request id :' || v_request_id);
    ELSE
      l_error_seq := '02';
      l_error_msg := 'Payable Open Ivoice Pogram failed you can see the log from the application for the following reqiest id :' || v_request_id;
      RAISE g_invalid_ex;
    END IF;
  END;

  dbms_output.put_line ('********************************');
EXCEPTION
  WHEN g_invalid_ex THEN
    dbms_output.put_line ('l_error_seq = ' || l_error_seq);
    dbms_output.put_line ('l_error_msg = ' || l_error_msg);
  WHEN OTHERS THEN
    dbms_output.put_line ('Error :' || sqlerrm);
END;

Final Result:

Payables Open Interface Import :

Payable Invoice Screen :

Warning: Please Don’t use these APIs (AP_AI_TABLE_HANDLER_PKG.INSERT_ROW & AP_AIL_TABLE_HANDLER_PKG.INSERT_ROW) to create AP invoices because they are private APIs not public.

In this article, we have demonstrate how to Create AP Invoice using AP open interface .
Please don’t use this code directly on production environment, instead test it on test environment first to make sure that API working correctly as per your requirements.
We are only giving one example of how to allocate one line as constant value.

Hopefully, it was clear and concise.