AP (Account Payable) Invoice Interface or Conversion in Oracle Apps

AP (Account Payable) Invoice Interface or Conversion

We will see how to create AP Invoice using Interface in Oracle R12.

Account payable is a part of Procure to Pay flow. Based on the PO receipt user create the Invoices.

-> Interface Tables: There are two tables-

I)   AP_INVOICES_INTERFACE

II)  AP_INVOICE_LINES_INTERFACE

-> Interface Error Table: AP_INTERFACE_REJECTIONS

Below are the basic steps:

Step 1) Create staging tables

        A) Header Staging table:

  CREATE TABLE xx_ap_inv_hdr_stg
     (invoice_num                 VARCHAR2 (75),
              invoice_type_lookup_code    VARCHAR2 (75),
              invoice_date                DATE,
              po_number                   VARCHAR2 (25),
              vendor_num                  VARCHAR2 (25),
              vendor_name                 VARCHAR2 (100),
              vendor_site_code            VARCHAR2 (25),
              invoice_amount              NUMBER,
              invoice_currency_code       VARCHAR2 (25),
              exchange_rate               NUMBER,
              exchange_rate_type          VARCHAR2 (25),
              exchange_date               DATE,
              terms_name                  VARCHAR2 (50),
              description                 VARCHAR2 (240),
              source                      VARCHAR2 (80),
              voucher_num                 VARCHAR2 (50),
              payment_method_lookup_code  VARCHAR2 (25),
              goods_received_date         DATE,
              invoice_received_date       DATE,
              gl_date                     DATE,
              accts_pay_code_comb_code    VARCHAR2 (50),
              creation_date               DATE,
              created_by                  NUMBER,
              last_updated_by             NUMBER,
              last_update_date            DATE,
              error_text                  VARCHAR2 (2000),
              process_flag                CHAR (1)
);

B) Line Staging table:

  CREATE TABLE xx_ap_inv_line_stg
     (invoice_number              VARCHAR2 (20),
              vendor_num                  VARCHAR2 (40),
              line_number                 VARCHAR2 (20),
              line_type_lookup_code       VARCHAR2 (20),
              quantity_invoiced           VARCHAR2 (30),
              unit_price                  VARCHAR2 (10),
              amount                      VARCHAR2 (10),
              accounting_date             DATE,
              description                 VARCHAR2 (40),
              tax_code                    VARCHAR2 (20),
              po_number                   VARCHAR2 (20),
              po_shipment_num             VARCHAR2 (40),
              dist_code_comb_code         VARCHAR2 (10),
              expenditure_type            VARCHAR2 (20),
              income_tax_region           VARCHAR2 (20),
              process_flag                CHAR (1),
              creation_date               DATE,
              created_by                  NUMBER,
              last_updated_by             NUMBER,
              last_update_date            DATE,
              error_text                  VARCHAR2 (2000)
             );

Step 2) Create Control files (.ctl) to insert data from CSV or text (.txt) files to staging tables:

Note: Here we used two Control files and two CSV files- one for header data and another for line data.

A) Header Control file:

LOAD DATA
        INFILE "D:\oracle\visappl\ap\11.5.0\data\XX_AP_INVOICE_HEADRES_DATA.txt"
        APPEND INTO TABLE xx_ap_inv_hdr_stg
        FIELDS TERMINATED BY ','
        TRAILING NULLCOLS(invoice_num,
                    invoice_type_lookup_code,
                  invoice_date,
                  po_number,
                  vendor_num,
                  vendor_name,
                  vendor_site_code,
                  invoice_amount,
                  invoice_currency_code,
                  exchange_rate,
                  exchange_rate_type,  
                    exchange_date,  
                    terms_name,  
                    description,  
                    source,
                    voucher_num,  
                    payment_method_lookup_code,  
                    goods_received_date,  
                    invoice_received_date,  
                    gl_date,  
                    accts_pay_code_comb_code,
                    creation_date "SYSDATE",
                  created_by constant "-1",
                  last_updated_by constant "-1",
                    last_update_date "SYSDATE",
                    process_flag constant "N"
)

B) Line Control file:

LOAD DATA
        INFILE "D:\ORACLE\VISAPPL\AP\11.5.0\DATA\XX_AP_INVOICE_LINES_DATA.TXT"
        APPEND INTO TABLE xx_ap_inv_line_stg
        FIELDS TERMINATED BY ','
        TRAILING NULLCOLS(invoice_number,  
                    vendor_num,  
                    line_number,
                    line_type_lookup_code,
                    quantity_invoiced,
                    unit_price,
                    amount,
                    accounting_date,
                    description,
                    tax_code,
                    po_number,
                    po_shipment_num,
                  dist_code_comb_code,
                  expenditure_type,
                  income_tax_region,
                    process_flag constant "N",
                    creation_date "SYSDATE",
                  created_by constant "-1",
                  last_updated_by constant "-1",
                    last_update_date "SYSDATE"
);

Step 3) Put the created control files and CSV files in custom application top/BIN folder.

Step 4) Create a Concurrent Executable and Concurrent Program to load data from CSV files to staging table using SQL*Loader.

Step 5) Assign the created concurrent program to Responsibility Group. And execute a program so data will insert in staging tables.

Step 6) Once a data inserted in staging table - we need to validate that data and insert in Interface tables. So, create validation package to insert data from staging tables to Interface tables:

  A) Package Specification:

CREATE OR REPLACE PACKAGE xx_ap_invoice_create_pkg
IS
            PROCEDURE xx_ap_invoice_create_proc(errbuf out VARCHAR2,
              retcode out NUMBER);
        END;
        /

B) Package Body:

CREATE OR REPLACE PACKAGE BODY xx_ap_invoice_create_pkg IS PROCEDURE xx_ap_invoice_create_proc(errbuf out VARCHAR2, retcode out NUMBER) IS ln_vendor_id NUMBER; ln_error_count NUMBER; ln_record_count NUMBER; lc_invoice_number VARCHAR2(75); ln_vendor_site_id NUMBER; lc_term_name VARCHAR2(50); lc_error_text VARCHAR2(2000):=''; ln_hdr_count NUMBER := 0; ln_line_count NUMBER := 0; BEGIN FOR lreq_hdr IN (SELECT a.rowid row_id,a.* FROM xx_ap_inv_hdr_stg a WHERE a.process_flag = 'N' ) LOOP lc_invoice_number := NULL; ln_record_count := ln_record_count + 1; -- Get Vendor Id BEGIN SELECT vendor_id INTO ln_vendor_id FROM po_vendors WHERE vendor_name = lreq_hdr.vendor_name AND segment1 = lreq_hdr.vendor_num; EXCEPTION WHEN OTHERS THEN -- here we can add other exceptions like NO_DATA_FOUND, TOO_MANY_ROWS etc. ln_error_count:=1; lc_error_text :='Vendor name does Not Exist'; END; --Primary key validation BEGIN SELECT 1 INTO lc_invoice_number FROM ap_invoices_all WHERE vendor_id = ln_vendor_id AND invoice_num = lreq_hdr.invoice_num; EXCEPTION WHEN OTHERS THEN ln_error_count:=0; END; -- IF (lc_invoice_number IS NOT NULL) THEN ln_error_count := 1; lc_error_text := lc_error_text||','||'Invoice number: '||lreq_hdr.invoice_num||' already Exist'; END IF; --To Get Vendor Site Id BEGIN SELECT vendor_site_id INTO ln_vendor_site_id FROM po_vendor_sites_all WHERE vendor_site_code = lreq_hdr.vendor_site_code AND vendor_id = ln_vendor_id AND org_id = 204; --get from PO or enter manually EXCEPTION WHEN OTHERS THEN ln_error_count:=1; lc_error_text := lc_error_text||','||'Vendor Site code does Not Exists'; END; --Term Name Exists or Nor BEGIN SELECT name INTO lc_term_name FROM ap_terms WHERE name = lreq_hdr.terms_name; EXCEPTION WHEN OTHERS THEN ln_error_count:=1; lc_error_text :=lc_error_text||','||'Term Name Not Exists'; END; IF ln_error_count <> 0 THEN UPDATE xx_ap_inv_hdr_stg SET process_flag = 'E', error_text = lc_error_text WHERE ROWID = lreq_hdr.ROW_ID; ELSE ln_hdr_count := ln_hdr_count + 1; INSERT INTO ap_invoices_interface (invoice_id, invoice_num, invoice_type_lookup_code, invoice_date, po_number, vendor_id, vendor_site_code, invoice_amount, invoice_currency_code, exchange_rate, exchange_rate_type, exchange_date, terms_name, description, source, voucher_num, payment_method_lookup_code, goods_received_date, invoice_received_date, gl_date, accts_pay_code_combination_id, org_id, creation_date, created_by, last_update_date, last_updated_by ) VALUES(ap_invoices_interface_s.nextval, lc_invoice_number, lreq_hdr.invoice_type_lookup_code, lreq_hdr.invoice_date, lreq_hdr.po_number, ln_vendor_id, lreq_hdr.vendor_site_code, lreq_hdr.invoice_amount, lreq_hdr.invoice_currency_code, lreq_hdr.exchange_rate, lreq_hdr.exchange_rate_type, lreq_hdr.exchange_date, lreq_hdr.terms_name, lreq_hdr.description, lreq_hdr.source, lreq_hdr.voucher_num, lreq_hdr.payment_method_lookup_code, lreq_hdr.goods_received_date, lreq_hdr.invoice_received_date, lreq_hdr.gl_date, lreq_hdr.accts_pay_code_comb_code, 204, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id); -- Update staging table with process flag S (Successfully inserted record in stg table.) BEGIN UPDATE xx_ap_inv_hdr_stg SET process_flag = 'S' WHERE rowid = lreq_hdr.row_id; EXCEPTION WHEN others THEN lc_error_text :=lc_error_text||','||'Header Stg table updation failed -'||SQLERRM; END; -- FOR j IN (SELECT b.rowid row_id,b.* FROM xx_ap_inv_line_stg b WHERE process_flag = 'N' AND vendor_num = lreq_hdr.vendor_num AND invoice_number = lreq_hdr.invoice_num ) LOOP ln_line_count := ln_line_count + 1; INSERT INTO ap_invoice_lines_interface

(invoice_id, invoice_line_id, line_number, line_type_lookup_code, quantity_invoiced, unit_price, amount, accounting_date, description, tax_code, po_number, po_shipment_num, dist_code_combination_id, expenditure_type, income_tax_region, created_by, creation_date, last_update_date, last_updated_by) VALUES(ap_invoices_interface_s.currval, ap_invoice_lines_interface_S.nextval, J.line_number, J.line_type_lookup_code, J.quantity_invoiced, J.unit_price, J.amount, J.accounting_date, J.description, J.tax_code, J.po_number, J.po_shipment_num, J.dist_code_comb_code, J.expenditure_type,

J.income_tax_region, fnd_global.user_id, sysdate, sysdate, fnd_global.user_id); -- BEGIN UPDATE xx_ap_inv_line_stg SET process_flag = 'S' WHERE rowid = j.row_id; EXCEPTION WHEN others THEN lc_error_text :=lc_error_text||','||'Line Stg table updation failed -'||SQLERRM; END; -- END LOOP; END IF; END LOOP; -- IF ln_record_count = 0 THEN retcode := 1; END IF; fnd_file.put_line(fnd_file.log,'**********************PSNL AP Invoice Interface program started**********************'); fnd_file.put_line(fnd_file.log,'Total header records processed: '||ln_hdr_count); fnd_file.put_line(fnd_file.log,'Total line records processed: '||ln_line_count); fnd_file.put_line(fnd_file.log,'**********************PSNL AP Invoice Interface program ended**********************'); END xx_ap_invoice_create_proc; END xx_ap_invoice_create_pkg; /

Step 7) Once the data has inserted in Interface table then we need to run a standard Import program to process data from Interface tables to Base tables.

Import program is: Payables Invoice Import.

Now, we can check a processed records in Base tables.

SELECT * FROM ap_invoices_all;

SELECT * FROM ap_invoice_lines_all;

SELECT * FROM ap_invoice_distributions_all;