Payables interface tables in oracle apps r12

Payables interface tables in oracle apps r12

In Oracle Payables , We do create Supplier Invoices , Payments for the Supplier Invoices. Oracle apps has given the Payables Interface to create the AP invoices from the External System. With the help of the Payables Interface we can upload the bulk Supplier invoices from the External system to the Oracle apps Payables. In this post , I will share the Detail Information about Payables Interface Table in Oracle apps r12.

 

 

2 Important Payables interface tables in oracle apps

1.AP_INVOICES_INTERFACE

2.AP_INVOICE_LINES_INTERFACE

 

 

4 Important Steps to Import Data in the Payables interface

1. First we need to load the Data in the Payables Interface Table. Prepare the Data in the Format of the Payables Interface.

2.Once the Data Load in to the Payables Interface Table then run the Payables Open interface Import program to import invoices from AP interface to AP base tables.

 

 

Example of Payables interface tables in Oracle Apps

 

 Here below is the sample Invoice Test Data which we got from the External System and how we will insert this Data in these Two Interface tables As Below.

 

 

 

DECLARE
    v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;

v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;

V_vendor_id ap_invoices_interface.vendor_id%TYPE;

v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;

v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;

v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;

v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;

v_payment_cross_rate ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;

v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;

v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;

v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;

v_description ap_invoices_interface.DESCRIPTION%TYPE;

v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;

v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;

v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;

v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;

v_terms ap_invoices_interface.TERMS_ID%TYPE;

v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;

v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;

v_invoice_recieved_date ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;

v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;

v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;

v_inv_count number;

V_CODE_COMBINATION_ID NUMBER;

v_invoice_id NUMBER;

v_invoice_line_id NUMBER;

v_line_no NUMBER;

v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;

v_accounting_date DATE;

v_line_type VARCHAR2(400);

V_LOOKUP_CODE VARCHAR2(400);

V_CURRENCY_CODE VARCHAR2(400);

V_INVOICE_NUMBER VARCHAR2(400);

V_concatenated_segment VARCHAR2(4000);

V_ERROR_MESSAGE VARCHAR2(4000);

V_ERROR_FLAG VARCHAR2(40);

cursor c_invoice IS

SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY

FROM XX_INVOICES_STG

WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')

GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY;

cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS

SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY,LINE_NUMBER,LINE_AMOUNT,GL_ACCOUNT,ATTRIBUTE1,ATTRIBUTE2

FROM XX_INVOICES_STG

where INVOICE_NUMBER=c_inv_number

and SUPPLIER_NUMBER=c_supplier_number;

 

 

  begin
   
  for f_invoice in c_invoice loop

V_ERROR_MESSAGE:=NULL;

V_ERROR_FLAG:=NULL;

v_inv_type :=NULL;

v_invoice_date:=NULL;

v_invoice_num:=NULL;

v_invoice_curr:=NULL;

v_invoice_amount:=NULL;

v_description:=NULL;

 

 BEGIN
   SELECT INVOICE_NUMBER into V_INVOICE_NUMBER

FROM XX_INVOICES_STG

WHERE INVOICE_NUMBER=f_invoice.invoice_number

GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY;

 

 EXCEPTION
   WHEN OTHERS THEN
null;

V_Error_flag:='Y';

 

 END;
  

　
 BEGIN
   select LOOKUP_CODE INTO V_LOOKUP_CODE from fnd_lookup_values

where LOOKUP_CODE='STANDARD'

AND LOOKUP_TYPE='INVOICE TYPE';

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

null;

V_Error_flag:='Y';

 

 END;
  

　

　
 begin
   select VENDOR_ID into v_vendor_id from ap_suppliers

where segment1=f_invoice.SUPPLIER_NUMBER

and VENDOR_NAME=f_invoice.SUPPLIER_NUMBER;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Selection';

V_Error_flag:='Y';

 

 END;
   

 

 begin
   select VENDOR_ID into v_vendor_site_id from ap_supplier_SITES_ALL

where VENDOR_ID=v_vendor_id

and VENDOR_SITE_CODE=f_invoice.SUPPLIER_SITE;

EXCEPTION WHEN NO_DATA_FOUND THEN

null;

V_Error_flag:='Y';

WHEN OTHERS THEN

null;

V_Error_flag:='Y';

 END;
   

 begin
   select CURRENCY_CODE into V_CURRENCY_CODE from fnd_currencies

where CURRENCY_CODE=f_invoice.INVOICE_CURRENCY;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Invoice Currency is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Currency Selection';

V_Error_flag:='Y';

 

 END;
  

　
 for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop

 

 begin
   select COUNT(*) into V_concatenated_segment from gl_code_combinations_kfv

where trim(concatenated_segments)=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));

EXCEPTION WHEN NO_DATA_FOUND THEN

null;

V_Error_flag:='Y';

WHEN OTHERS THEN
NULL;

V_Error_flag:='Y';

 

 END;
   end loop;

　
 --v_status:= cur_invoice.status;
    

 -- SELECT COUNT(*)

 -- INTO v_inv_count

 -- FROM AP_INVOICES_ALL

 -- WHERE  INVOICE_NUM = V_INVOICE_NUM

 -- AND VENDOR_ID     = v_vendor_id;

 --

 --IF  v_count > 0  THEN

 --   FND_FILE.PUT_LINE(FND_FILE.LOG,' Invocie already Exists - '||v_invoice_num);

 --end if;
    

 

 --inserting validated values to interface table(headers)='N'
    
if NVL(V_Error_flag,'N')='N' THEN

 

SELECT ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;

v_inv_type := f_invoice.INVOICE_TYPE;

v_invoice_date:=to_DATE(f_invoice.INVOICE_DATE,'MM/DD/YYYY');

v_invoice_num:= f_invoice.invoice_number;

v_invoice_curr:= f_invoice.INVOICE_CURRENCY;

v_invoice_amount:= f_invoice.invoice_amount;

v_description:= f_invoice.description;

 

INSERT INTO ap_invoices_interface (

invoice_id,

invoice_num,

invoice_type_lookup_code,

invoice_date,

vendor_id,

vendor_site_id,

invoice_amount,

invoice_currency_code,

description,

status,

source,

creation_date,

created_by,

last_update_date,

 

last_updated_by
  )

 VALUES

 (
    v_invoice_id,

v_invoice_num,

v_inv_type,

v_invoice_date,

v_vendor_id,

v_vendor_site_id,

nvl(v_invoice_amount,0),

v_invoice_curr,

v_description,

'NEW',

'TEST SOURCE',

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id

 );
   
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop

select ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;

v_line_no:=f_inv_detls.line_NUMBER;

v_amount:=f_inv_detls.line_amount;

v_accounting_date:=sysdate;

v_line_type:='Item';--(Item, Fright,Tax,Miscellaneous)

select CODE_COMBINATION_ID into V_CODE_COMBINATION_ID from gl_code_combinations_kfv

where concatenated_segments=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));

　
 -- BEGIN

 --        SELECT 'Y'

 --        INTO   v_found

 --        FROM   gl_period_statuses

 --        WHERE  application_id  = 200--ASSUMTION

 --        AND    set_of_books_id = 3--ASSUMTION

 --        AND    SYSDATE between start_date AND end_date

 --        AND    closing_status IN ('O', 'F')

 -- AND    NVL(adjustment_period_flag, 'N') = 'N'

 --        AND    ROWNUM = 1;

 -- EXCEPTION

 --  WHEN NO_DATA_FOUND THEN

 --             FND_FILE.PUT_LINE(FND_FILE.log,'Accounting Date Is Not In Open');

 --    END;

INSERT INTO ap_invoice_lines_interface (

invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

accounting_date,

dist_code_combination_id,

creation_date,

created_by,

last_update_date,

 

last_updated_by
   )

VALUES(v_invoice_id,

v_invoice_line_id,

v_line_no,

v_line_type,

v_amount,

v_accounting_date,

V_CODE_COMBINATION_ID,

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id

);

end loop;

UPDATE APPS.XX_INVOICES_STG

SET STATUS='PROCESSED',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

ELSE

UPDATE APPS.XX_INVOICES_STG

SET STATUS='ERROR',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

END IF;

COMMIT;

END LOOP;

 

 END;

 

 

Payables interface tables in oracle apps r12