AP INTERFACE

Interface Tables:

AP_SUPPLIERS_INT - Supplier Information
AP_SUPPLIER_SITES_INT - Supplier Sites Information
AP_SUP_SITE_CONTACT_INT - Supplier Contact details

This uses Vendor ID, Vendor Site Code to relate the contacts to specific vendor.

Run the following Interface Programs:
a) Supplier Open Interface Import (Inserts data into PO_VENDORS).
b) Supplier Sites Open Interface Import ( Inserts data into po_vendor_sites_all)
c) Supplier Site Contacts Open Interface Import(Inserts data into po_vendor_contacts)

Base Tables:

po_vendors

po_vendor_sites_all

po_vendor_contacts

1)Creating Staging tables and inserting data:

CREATE TABLE XXAP_SUPP_RY(

VENDOR_INTERFACE_ID NUMBER(15) PRIMARY KEY,

VENDOR_NAME VARCHAR2(240),

SEGMENT1 VARCHAR2(30),

ENABLED_FLAG VARCHAR2(1),

VENDOR_TYPE_LOOKUP_CODE VARCHAR2(30),

TERMS_ID NUMBER,

SET_OF_BOOKS_ID NUMBER,

ACCTS_PAY_CODE_COMBINATION_ID number,

PREPAY_CODE_COMBINATION_ID NUMBER,

INVOICE_CURRENCY_CODE VARCHAR2(15),

PAYMENT_CURRENCY_CODE VARCHAR2(15));

insert into XXAP_SUPP_RY

(VENDOR_INTERFACE_ID,

VENDOR_NAME,

SEGMENT1,

ENABLED_FLAG,

VENDOR_TYPE_LOOKUP_CODE,

TERMS_ID)

values

(123123123,'RY',

'987654321','y','supplier',10003)

create table XXAP_SUPP_SITES_RY

(VENDOR_INTERFACE_ID NUMBER(15) references XXAP_SUPP_RY(VENDOR_INTERFACE_ID),

VENDOR_SITE_CODE VARCHAR2(15) not null,

ADDRESS_LINE1 VARCHAR2(240),ADDRESS_LINE2 VARCHAR2(240),ADDRESS_LINE3 VARCHAR2(240),

CITY VARCHAR2(25),STATE VARCHAR2(150),

ZIP VARCHAR2(20),COUNTRY VARCHAR2(25))

insert into XXAP_SUPP_SITES_RY

(VENDOR_INTERFACE_ID,

VENDOR_SITE_CODE,

ADDRESS_LINE1,ADDRESS_LINE2,

ADDRESS_LINE3,

CITY,STATE,

ZIP,COUNTRY

) values

(123123123,'Melbourne',

'No 82',

'Dock Lands',

' ',

'Melbourne','Melbourne',

'60001','AUSTRALIA')

CREATE TABLE XXAP_SUPP_CONTACT_RY(

VENDOR_SITE_CODE varchar2(15),

FIRST_NAME varchar2(15),

MIDDLE_NAME varchar2(15),

LAST_NAME varchar2(20),

PREFIX varchar2(5),

PHONE varchar2(15),

FAX varchar2(15),

EMAIL_ADDRESS varchar2(250),

ORG_ID number);

insert into XXAP_SUPP_CONTACT_RY(

VENDOR_SITE_CODE,

FIRST_NAME,

MIDDLE_NAME,

LAST_NAME,

PREFIX,

PHONE,

FAX,

EMAIL_ADDRESS

)

values

('Melbourne',

'Andrew ',' ','Andrew Aggasy',

'Mr',

'602345678',

'68876',

' aggasy.a@ferrari.com')

2. Creating Package to insert data into Interface Tables

create or replace package xxap_supp_pkg

is

procedure xxap_sup(errbuf out varchar2,Retcode out number);

end xxap_supp_pkg;

/

create or replace package body xxap_supp_pkg

is

procedure xxap_sup(errbuf out varchar2,Retcode out number)

is

v_accts_pay_code_comb_id NUMBER;

v_prepay_code_combination_id NUMBER;

v_invoice_currency_code ap_system_parameters_all.invoice_currency_code%TYPE;

v_payment_currency_code ap_system_parameters_all.payment_currency_code%TYPE;

v_org_id number :=204;

cursor c1 is select a.VENDOR_Interface_ID ,

a.VENDOR_NAME,

a.SEGMENT1,

a.ENABLED_FLAG,

a.VENDOR_TYPE_LOOKUP_CODE,

a.TERMS_ID,

b.VENDOR_SITE_CODE,

b.ADDRESS_LINE1,

b.ADDRESS_LINE2,

b.ADDRESS_LINE3,

b.city,

b.state,

b.country,

b.zip,

c.FIRST_NAME ,

c.MIDDLE_NAME,

c.LAST_NAME ,

c.PREFIX ,

c.PHONE ,

c.FAX,

c.EMAIL_ADDRESS

from XXAP_SUPP_RY a, XXAP_SUPP_SITES_RY b ,XXAP_SUPP_CONTACT_RY c

where a.VENDOR_INTERFACE_ID=b.VENDOR_INTERFACE_ID

and b.VENDOR_SITE_code =c.VENDOR_SITE_code;

BEGIN

FOR i IN c1

LOOP

Begin

SELECT accts_pay_code_combination_id,

prepay_code_combination_id

INTO v_accts_pay_code_comb_id

, v_prepay_code_combination_id

FROM financials_system_params_all

WHERE org_id = v_org_id;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line

(fnd_file.LOG, 'Acct_Pay_ccid and Prepayments_ccid are not available for this Org Id');

END;

BEGIN

SELECT invoice_currency_code

, payment_currency_code

INTO v_invoice_currency_code

, v_payment_currency_code

FROM ap_system_parameters_all

WHERE org_id = v_org_id;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.output, SQLCODE || ' ' || SQLERRM);

fnd_file.put_line(fnd_file.LOG, 'Existing Invoice and Payment Currency_Codes are notexisting

with client currency codes');

END;

fnd_file.put_line (fnd_file.output, SQLCODE || ' ' || SQLERRM);

------Inserting data into Interface Tables-----------

INSERT INTO ap_suppliers_int

(vendor_interface_id, vendor_name

,segment1, enabled_flag, vendor_type_lookup_code , terms_id,terms_name,

set_of_books_id, accts_pay_code_combination_id, created_by, creation_date, last_update_date

, last_updated_by, prepay_code_combination_id

, invoice_currency_code, payment_currency_code

)

VALUES

(i.vendor_interface_id,i.vendor_name

, i.segment1, i.enabled_flag, i.vendor_type_lookup_code , i.terms_id,'Net 45',1

, v_accts_pay_code_comb_id, -1

,SYSDATE, SYSDATE

, -1, v_prepay_code_combination_id

, v_invoice_currency_code

,v_payment_currency_code

);

INSERT INTO ap_supplier_sites_int

(vendor_interface_id, vendor_site_code

, address_line1, address_line2

, address_line3, city, state, zip

, country, created_by, creation_date

, last_update_date, last_updated_by

)

VALUES (i.vendor_interface_id, i.vendor_site_code

, i.address_line1, i.address_line2

, i.address_line3, i.city, i.state, i.zip

, i.country, -1, SYSDATE

, SYSDATE, -1

);

INSERT INTO ap_sup_site_contact_int

(vendor_site_code,first_name, middle_name, last_name, prefix, phone,

fax, EMAIL_ADDRESS, created_by

, creation_date, last_update_date

, last_updated_by

)

VALUES (i.vendor_site_code,i.first_name, i.middle_name,i.last_name,

i.prefix, i.phone, i.fax, i.EMAIL_ADDRESS, -1, SYSDATE, SYSDATE, -1);

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line(fnd_file.LOG, 'Error in Inserting data into interface tables ');

fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);

end xxap_sup;

end xxap_supp_pkg;/

3. Create Executable and concurrent program in Application Developer.

4. Attach Concurrent Program- xxpv_supplier_int program to the Request Group.

5. Run Concurrent Program “xxpv_supplier_int program” in Payables Application.

6. Run the following Standard Concurrent programs in Account Payables Application.

Supplier Open Interface Import

Supplier Sites Open Interface Import

Supplier Site Contacts Open Interface Import

AP invoice interface

This interface helps us to import vendor invoices into Oracle applications

from external systems into Oracle Applications.

Pre-requisites:

Set of Books

Code combinations

Employees

Lookups

Interface tables:

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

Base tables:

AP_INVOICES_ALL – header information

AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program:

Payables Open Interface Import

Validations:

Check for valid vendor

Check for Source, Location, org_id, currency_code’s validity

Check for valid vendor site code.

Check if record already exists in payables interface table.

Some important columns that need to be populated in the interface tables:

SQL * LOADER

OPTIONS (SKIP=1)

LOAD DATA

INFILE *

APPEND

INTO TABLE apcx_il_ap_invoice_stg

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(Invoice_source,

ORG_ID,

OPERATING_UNIT ,

INVOICE_TYPE_LOOKUP_CODE ,

VENDOR_NAME ,

SUPPLIER_NUMBER ,

VENDOR_SITE_CODE ,

INVOICE_DATE ,

INVOICE_NUM ,

INVOICE_CURRENCY_CODE ,

INVOICE_AMOUNT,

EXCHANGE_RATE ,

TERM_NAME ,

PAYMENT_METHOD_LOOKUP_CODE ,

PAY_GROUP_LOOKUP_CODE ,

DISTRIBUTION_LINE_NUMBER ,

AMOUNT ,

LIABILITY_ACCOUNT ,

PREPAYMENT_ACCT ,

DESCRIPTION ,

EXCHANGE_RATE_TYPE,

EXCHANGE_DATE,

VENDOR_ID ,

HEADER_GL_DATE ,

SOURCE ,

LINE_GL_DATE ,

PROCESS_FLAG ,

ERROR_MESSAGE

)

STAGING TABLE

CREATE TABLE APCX_IL_AP_INVOICE_STG

(

ORG_ID NUMBER,

OPERATING_UNIT VARCHAR2(100 BYTE),

INVOICE_ID NUMBER,

INVOICE_TYPE_LOOKUP_CODE VARCHAR2(100 BYTE),

INVOICE_NUM VARCHAR2(100 BYTE),

VENDOR_ID NUMBER,

VENDOR_NAME VARCHAR2(100 BYTE),

SUPPLIER_NUMBER VARCHAR2(100 BYTE),

VENDOR_SITE_CODE VARCHAR2(100 BYTE),

NEW_VENDOR_SITE_CODE VARCHAR2(100 BYTE),

VENDOR_SITE_ID NUMBER,

INVOICE_DATE DATE,

INVOICE_CURRENCY_CODE VARCHAR2(100 BYTE),

INVOICE_AMOUNT NUMBER,

NET_INV_AMOUNT NUMBER,

WITHHELD_AMOUNT NUMBER,

AMOUNT_PAID NUMBER,

HEADER_GL_DATE DATE,

DESCRIPTION VARCHAR2(240 BYTE),

EXCHANGE_RATE_TYPE VARCHAR2(100 BYTE),

EXCHANGE_DATE DATE,

EXCHANGE_RATE NUMBER,

ACCTS_PAY_CODE_COMBINATION_ID NUMBER,

LIABILITY_ACCOUNT VARCHAR2(240 BYTE),

TERMS_DATE DATE,

TERM_NAME VARCHAR2(100 BYTE),

TERM_DESCRIPTION VARCHAR2(240 BYTE),

TERM_ID NUMBER,

PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(100 BYTE),

PAY_GROUP_LOOKUP_CODE VARCHAR2(100 BYTE),

PAYMENT_CURRENCY_CODE VARCHAR2(100 BYTE),

PAYMENT_CROSS_RATE_DATE DATE,

PAYMENT_CROSS_RATE_TYPE VARCHAR2(100 BYTE),

PAYMENT_CROSS_RATE NUMBER,

SOURCE VARCHAR2(100 BYTE),

LINES VARCHAR2(100 BYTE),

INVOICE_DISTRIBUTION_ID NUMBER,

DISTRIBUTION_LINE_NUMBER VARCHAR2(100 BYTE),

LINE_TYPE VARCHAR2(100 BYTE),

AMOUNT NUMBER,

VAT_CODE VARCHAR2(100 BYTE),

LINE_GL_DATE DATE,

DIST_CODE_COMBINATION_ID NUMBER,

PREPAYMENT_ACCT VARCHAR2(240 BYTE),

LINE_DESCRIPTION VARCHAR2(240 BYTE),

TAX_RECOVERY_RATE NUMBER,

TAX_RECOVERABLE_FLAG VARCHAR2(1 BYTE),

ASSETS_TRACKING_FLAG VARCHAR2(2 BYTE),

GROUP_ID NUMBER,

PROCESS_FLAG VARCHAR2(1 BYTE),

ERROR_MESSAGE VARCHAR2(2000 BYTE),

NEW_ACCTS_PAY_CCID_ID NUMBER,

NEW_DIST_CODE_COMBINATION_ID NUMBER,

INVOICE_SOURCE VARCHAR2(15 BYTE)

)

CREATE SYNONYM APCX_IL_AP_INVOICE_STG FOR APC_CUSTOM.APCX_IL_AP_INVOICE_STG;

CREATE OR REPLACE PACKAGE apcx_asean_ap_invoice_conv_pkg

IS

/*

********************************************************************

********************************

* Type : Package Specification.

* Name : apcx_asean_ap_invoice_conv_pkg

* Purpose : Package Specification for APC Open Invoice-AP migration.

* Author : xxxxx.

* Date : 05-Oct-10

* Version : 1.1.0

* Description : Package Specification for APC Open Invoice-AP migration.

* '

**************************************************************************

**************************

*/

--v_gl_date DATE := '31-SEP-2010';

PROCEDURE main (retcode OUT VARCHAR2, errbuf OUT VARCHAR2 ,p_val_load

in varchar2,P_SOURCE IN VARCHAR2);

PROCEDURE VALIDATE (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2);

PROCEDURE upload_invoices (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2);

FUNCTION get_ccid(v_segments IN varchar2) RETURN NUMBER;

END apcx_asean_ap_invoice_conv_pkg;

/

CREATE OR REPLACE PACKAGE BODY apcx_asean_ap_invoice_conv_pkg

AS

/*

************************************************************************

****************************

* Type : Package Body.

* Name : apcx_asean_ap_invoice_conv_pkg

* Purpose : Package Body for APC Open Invoice-AP migration.

* Author : xxxx.

* Date :

* Version : 1.1.0

* Description : Package Body for APC Open Invoice-AP migration.

* '

*/

g_err_msg VARCHAR2 (4000) := NULL;

g_request_id NUMBER := fnd_global.conc_request_id;

g_user_id NUMBER := fnd_profile.VALUE ('USER_ID');

g_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');

g_sob_id NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

g_hdr_rec NUMBER := 0;

g_func_curr VARCHAR2(10);

-----------------------------------------------------

-- Process_flag details

-- 'E' - ERROR

-- 'V' - VALIDATED

-- 'Y' - PROCESSED

------------------------------------------------------

function get_ccid(v_segments IN varchar2)

return number is

v_chart_of_accounts_id number;

p_new_ccid number;

BEGIN

SELECT chart_of_accounts_id

INTO v_chart_of_accounts_id

FROM gl_sets_of_books

WHERE set_of_books_id = g_sob_id ;

-- SELECT code_combination_id

--INTO p_new_ccid

-- FROM gl_code_combinations_kfv

-- WHERE concatenated_segments =v_segments ;

SELECT code_combination_id

INTO p_new_ccid

FROM gl_code_combinations

WHERE segment1 =substr(v_segments,1,4) -------- Added by sudhir

and segment2 =substr(v_segments,6,7)

and segment3 =substr(v_segments,14,4)

and segment4=substr(v_segments,19,4)

and segment5=substr(v_segments,24,3)

and segment6=substr(v_segments,28,4)

and segment7=substr(v_segments,33,4)

and segment8=substr(v_segments,38,4)

and segment9=substr(v_segments,43,5);

return(p_new_ccid);

EXCEPTION

WHEN NO_DATA_FOUND THEN

p_new_ccid :=

fnd_flex_ext.get_ccid

(application_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => v_chart_of_accounts_id,

validation_date => TO_CHAR (SYSDATE, apps.fnd_flex_ext.DATE_FORMAT ),

concatenated_segments => v_segments

);

return(p_new_ccid);

end;

PROCEDURE main (retcode OUT VARCHAR2, errbuf OUT VARCHAR2, p_val_load in varchar2,P_SOURCE IN VARCHAR2)

IS

BEGIN

if p_val_load = 'VAL' then

fnd_file.put_line (fnd_file.output, '**************************************************' );

fnd_file.put_line (fnd_file.output, ' Validating records in the Staging table

apcx_il_ap_invoice_stg and apcx_il_ap_invoice_stg' );

--fnd_file.put_line (fnd_file.output, ' Calling apcx_il_ap_invoice_conv_pkg.validate' );

validate (g_org_id, g_sob_id,P_SOURCE);

end if;

--IF g_hdr_rec >0

if p_val_load = 'LOAD' then

fnd_file.put_line (fnd_file.output, ' Inserting Valdated records into Payable open Interface table' );

--fnd_file.put_line (fnd_file.output, ' Call apcx_il_ap_invoice_conv_pkg.upload_invoices' );

upload_invoices (g_org_id, g_sob_id,P_SOURCE);

fnd_file.put_line (fnd_file.output, ' Uploaded Invoices into interface tables' );

END IF;

END;

PROCEDURE VALIDATE (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2)

IS

CURSOR c_inv (cp_source varchar2)

IS

SELECT DISTINCT inv_hdr.operating_unit,

inv_hdr.invoice_type_lookup_code,

inv_hdr.invoice_num,

inv_hdr.vendor_name,

inv_hdr.supplier_number,

inv_hdr.vendor_site_code,

inv_hdr.invoice_date,

inv_hdr.invoice_currency_code,

inv_hdr.invoice_amount,

inv_hdr.header_gl_date,

inv_hdr.description,

inv_hdr.liability_account,

inv_hdr.term_name,

inv_hdr.line_type,

inv_hdr.payment_method_lookup_code,

inv_hdr.pay_group_lookup_code,

inv_hdr.SOURCE,

inv_hdr.INVOICE_SOURCE -----added sudhir

FROM apcx_il_ap_invoice_stg inv_hdr

WHERE NVL(process_flag,'X') in ('X','E')--IS NULL

--AND invoice_id between 854558 and 1382310

--and rownum<482

and inv_hdr.invoice_source=p_source

AND inv_hdr.org_id=g_org_id --and inv_hdr.header_gl_date=v_gl_date

ORDER BY supplier_number,invoice_num;

c_inv_rec c_inv%ROWTYPE;

CURSOR c_inv_line (cp_invoice_num VARCHAR2,cp_supplier_number VARCHAR2,cp_source varchar2)

IS

SELECT inv_line.invoice_num,

inv_line.distribution_line_number,

inv_line.amount,

inv_line.line_gl_date,

-- inv_line.dist_code_combination_id,

inv_line.prepayment_acct,

inv_line.line_type,

inv_line.ROWID

FROM apcx_il_ap_invoice_stg inv_line

WHERE invoice_num = cp_invoice_num

and inv_line.INVOICE_SOURCE=P_SOURCE

AND supplier_number = cp_supplier_number --AND process_flag IS NULL

AND inv_line.org_id=g_org_id --and inv_line.line_gl_date=v_gl_date

ORDER BY distribution_line_number;

c_inv_line_rec c_inv_line%ROWTYPE;

v_source ap_lookup_codes.lookup_code%TYPE := NULL;

v_vendor_id po_vendors.vendor_id%TYPE := NULL;

v_currency_code fnd_currencies.currency_code%TYPE := NULL;

v_pay_currency_code fnd_currencies.currency_code%TYPE := NULL;

v_vendor_site_id po_vendor_sites_all.vendor_site_id%TYPE := NULL;

v_invoice_num_exist NUMBER := NULL;

v_term_id ap_terms.term_id%TYPE := NULL;

v_period_status VARCHAR2 (30) := NULL;

v_payment_method ap_lookup_codes.lookup_code%TYPE := NULL;

v_pay_group po_lookup_codes.lookup_code%TYPE := NULL;

v_invoice_type ap_lookup_codes.lookup_code%TYPE := NULL;

v_total_inv_amt NUMBER := NULL;

v_dist_code_ccid NUMBER := NULL;

v_accts_pay_code_ccid NUMBER := NULL;

lp_coaid NUMBER := NULL;

v_line_type VARCHAR2 (60);

v_line_number NUMBER;

v_ap_batch_id NUMBER := NULL;

v_hdr_invoice_id NUMBER := NULL;

v_ap_inv_line_id NUMBER := NULL;

v_error_flag BOOLEAN;

g_err_msg VARCHAR2 (2000) := NULL;

v_ln_rec NUMBER := 0;

v_precess_rec NUMBER := 0;

v_rate_type VARCHAR2 (20) := 'User';

v_conv_rate NUMBER;

v_exch_rate NUMBER;

v_exch_date DATE;

v_exch_type VARCHAR2 (20);

v_pymt_rate NUMBER;

v_pymt_date DATE;

v_pymt_type VARCHAR2 (20);

v_count number:=0;

v_dist_amt NUMBER:=0;

e_inv_hdr_excep EXCEPTION;

e_inv_line_excep EXCEPTION;

line_amt number(10,2);

BEGIN

v_error_flag := FALSE;

SELECT ap_batches_s.NEXTVAL

INTO v_ap_batch_id

FROM DUAL;

--dbms_output.put_line('Batch:'||v_ap_batch_id);

fnd_file.put_line(fnd_file.output,'Batch:'||v_ap_batch_id);

SELECT currency_code

INTO g_func_curr

FROM gl_sets_of_books

WHERE set_of_books_id = g_sob_id;

fnd_file.put_line(fnd_file.output,'Func Curr:'||g_func_curr );

FOR c_inv_rec IN c_inv(p_source)

LOOP

--dbms_output.put_line('HeaderLoop');

--fnd_file.put_line(fnd_file.output,'HeaderLoop');

--dbms_output.put_line('Invoice# '||c_inv_rec.invoice_num);

fnd_file.put_line(fnd_file.output,'Invoice# '||c_inv_rec.invoice_num);

v_line_number := 0;

v_precess_rec := v_precess_rec+1;

line_amt:=0;

--Update apcx_il_ap_invoice_stg table amount column with invoice amount

/*UPDATE apcx_il_ap_invoice_stg

set amount=c_inv_rec.invoice_amount

where invoice_num=c_inv_rec.invoice_num;*/

BEGIN

g_err_msg := NULL;

v_error_flag := FALSE;

--

-- Validation for Source

--

/*BEGIN

SELECT lookup_code

INTO v_source

FROM ap_lookup_codes

WHERE lookup_type = 'SOURCE'

AND UPPER (displayed_field) = UPPER (c_inv_rec.SOURCE)

AND enabled_flag = 'Y'

AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active,SYSDATE))

AND TRUNC (NVL (inactive_date,SYSDATE));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :='Invalid Invoice Source-'|| v_source|| ', Error='|| SUBSTR (SQLERRM, 1, 2000);

dbms_output.put_line(g_err_msg);

fnd_file.put_line (fnd_file.output, g_err_msg);

END;*/

v_source :='CONVERSION';

--

--Validation for Invoice Amount#

--

BEGIN

SELECT sum(amount)

INTO v_dist_amt

FROM apcx_il_ap_invoice_stg

WHERE invoice_num = c_inv_rec.invoice_num

and supplier_number = c_inv_rec.supplier_number;

--IF v_dist_amt <> c_inv_rec.net_inv_amount

--THEN

--v_error_flag := TRUE;

--g_err_msg :=' Invoice Header Amount:'||c_inv_rec.net_inv_amount|| ' NOT EQUAL

to net Distribution Amount:'||v_dist_amt;

fnd_file.put_line (fnd_file.output,g_err_msg);

--END IF;

END;

--

--Validation for Vendor

--

fnd_file.put_line(fnd_file.output,'Vendor Id Begin');

BEGIN

SELECT po.vendor_id

INTO v_vendor_id

FROM po_vendors po , po_vendor_sites_all pv

WHERE --vendor_name = c_inv_rec.vendor_name and

--SEGMENT1 = c_inv_rec.supplier_number

-- attribute9=c_inv_rec.SUPPLIER_NUMBER

po.vendor_id = pv.vendor_id

and pv.attribute9 = c_inv_rec.SUPPLIER_NUMBER

and pv.attribute11 = c_inv_rec.invoice_source

AND pv.org_id=g_org_id

-- and upper(vendor_name) = trim(upper(c_inv_rec.VENDOR_NAME)) -- Added BY Sudhir 04sep09

AND enabled_flag = 'Y'

AND TRUNC (NVL (end_date_active, TRUNC (SYSDATE))) >=TRUNC (SYSDATE);

fnd_file.put_line(fnd_file.output,'Vendor_id:'||v_vendor_id);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Vendor Name: '|| c_inv_rec.vendor_name;fnd_file.put_line

(fnd_file.output, 'INVOICE_NUM='|| c_inv_rec.invoice_num

|| g_err_msg

);

END;

--

--Validation for Invoice Number--

--

BEGIN

SELECT count(1)

INTO v_count

FROM ap_invoices_all

WHERE invoice_num = c_inv_rec.invoice_num

AND vendor_id = v_vendor_id

AND org_id = g_org_id;

IF v_count > 0

THEN

v_error_flag := TRUE;

g_err_msg :=' Duplicate Invoice Number:'||c_inv_rec.invoice_num;

fnd_file.put_line (fnd_file.output,g_err_msg);

END IF;

END;

-- Validation for Invoice Currency Code

--

BEGIN

SELECT currency_code

INTO v_currency_code

FROM fnd_currencies

WHERE currency_code = UPPER (c_inv_rec.invoice_currency_code);

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=

' Invalid Invoice Currency code Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num ||' '||

c_inv_rec.invoice_currency_code|| g_err_msg);

END;

/* IF v_currency_code <> g_func_curr

THEN

BEGIN

SELECT conversion_rate

INTO v_conv_rate

FROM gl_daily_rates

WHERE conversion_type = v_rate_type

and from_currency = v_currency_code

and to_currency = g_func_curr

and conversion_date = v_gl_date

;

v_exch_rate := NULL;

v_exch_date := v_gl_date;

v_exch_type := v_rate_type;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg := ' Invalid Conversion Rate for Invoice Currency Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||' '||

c_inv_rec.invoice_currency_code|| g_err_msg);

END;

ELSE

v_exch_rate := NULL;

v_exch_date := v_gl_date;

v_exch_type := 'User';

END IF;*/

--

-- Validation for Invoice Payment Currency Code

--

/*BEGIN

SELECT currency_code

INTO v_pay_currency_code

FROM fnd_currencies

WHERE currency_code = UPPER (c_inv_rec.payment_currency_code);

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=g_err_msg|| 'Invalid Payment Currency code Error='|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.payment_currency_code|| g_err_msg);

END;

IF v_pay_currency_code <> g_func_curr

THEN

BEGIN

SELECT conversion_rate

INTO v_conv_rate

FROM gl_daily_rates

WHERE conversion_type = v_rate_type

and from_currency = v_pay_currency_code

and to_currency = g_func_curr

and conversion_date = v_gl_date

;

v_pymt_rate := v_conv_rate;

v_pymt_date := v_gl_date;

v_pymt_type := v_rate_type;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=g_err_msg

|| 'Invalid Conversion Rate for Payment Currency Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.payment_currency_code|| g_err_msg);

END;

ELSE

v_pymt_rate := c_inv_rec.exchange_rate;

v_pymt_date := c_inv_rec.exchange_date;

v_pymt_type := c_inv_rec.exchange_rate_type;

END IF;*/

--

--validation for vendor Site and Pay Site Flag

--

BEGIN

SELECT vendor_site_id

INTO v_vendor_site_id

FROM po_vendor_sites_all

WHERE vendor_id = v_vendor_id

AND vendor_site_code = c_inv_rec.vendor_site_code

AND org_id = g_org_id

AND pay_site_flag = 'Y'

AND TRUNC (NVL (inactive_date, TRUNC (SYSDATE))) >=TRUNC (SYSDATE);

EXCEPTION

WHEN no_data_found

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Vendor Site code: '|| c_inv_rec.vendor_site_code

|| ':There is no such vendorsite code';

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num|| g_err_msg);

WHEN OTHERS THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Vendor Site code: '

|| c_inv_rec.vendor_site_code

|| ':There is no such vendorsite code';

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num|| g_err_msg);

END;

--

-- Validation for the Liability Account

--

v_accts_pay_code_ccid:=get_ccid(c_inv_rec.liability_account);

IF v_accts_pay_code_ccid =0

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Liability Account:'|| c_inv_rec.liability_account;

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num|| g_err_msg);

END IF;

--

--Validation for Payment Term

--

BEGIN

SELECT term_id

INTO v_term_id

FROM ap_terms

WHERE UPPER (NAME) =UPPER (NVL (c_inv_rec.term_name, 'Immediate'))

AND enabled_flag = 'Y'

AND TRUNC (NVL (end_date_active, TRUNC (SYSDATE))) >=

TRUNC (SYSDATE);

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Invoice Terms Name, Error='|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||' '||

c_inv_rec.term_name|| g_err_msg);

END;

--

--Varify the Open AP Period

--

/*BEGIN

SELECT a.closing_status

INTO v_period_status

FROM gl_period_statuses a, fnd_application b

WHERE a.application_id = b.application_id

AND b.application_short_name = 'SQLAP'

AND a.set_of_books_id = g_sob_id

AND (TRUNC (c_inv_rec.header_gl_date)BETWEEN TRUNC (start_date)AND TRUNC (end_date));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=g_err_msg

|| 'Invoice Header, AP Period,either not defined or, Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.invoice_date|| g_err_msg);

END;

--Verify for Open period

IF v_period_status <> 'O'

THEN

v_error_flag := TRUE;

g_err_msg := g_err_msg || 'Invoide Header, Perod Not Open';

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.invoice_date|| g_err_msg);

END IF;*/

--

-- Validate PAYMENT_METHOD_LOOKUP_CODE

--

BEGIN

SELECT lookup_code

INTO v_payment_method

FROM ap_lookup_codes

WHERE lookup_type = 'PAYMENT METHOD'

AND UPPER (lookup_code) =UPPER (c_inv_rec.payment_method_lookup_code)

AND enabled_flag = 'Y'

AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active,SYSDATE - 1))

AND TRUNC (NVL (inactive_date,SYSDATE + 1));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg := ' Invalid Payment Method Lookup Code: ' ||

c_inv_rec.payment_method_lookup_code || ', Error=' || SQLERRM;

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='||

c_inv_rec.invoice_num||' '|| g_err_msg);

END;

--

-- Validate PAY_GROUP_CODE

--

BEGIN

IF c_inv_rec.pay_group_lookup_code IS NOT NULL

THEN

SELECT lookup_code

INTO v_pay_group

FROM po_lookup_codes

WHERE lookup_type = 'PAY GROUP'

AND UPPER (lookup_code) =UPPER (c_inv_rec.pay_group_lookup_code)

AND enabled_flag = 'Y'

AND NVL (inactive_date, SYSDATE + 1) > TRUNC (SYSDATE);

END IF;

EXCEPTION

WHEN OTHERS

THEN

v_pay_group := NULL;

v_error_flag := FALSE;

END;

--

--Update the invoice header stagging table

--

IF v_error_flag = TRUE

THEN

RAISE e_inv_hdr_excep;

ELSE

--

--loop start for Invoice lines

--

--FOR c_inv_line_rec IN c_inv_line (c_inv_rec.Line_num)

FOR c_inv_line_rec IN c_inv_line (c_inv_rec.invoice_num, c_inv_rec.supplier_number,p_source)

LOOP

--Initialize Inv Line variables

v_error_flag := FALSE;

g_err_msg := NULL;

v_ap_inv_line_id := NULL;

v_dist_code_ccid := NULL;

-- Line Number Counter serial Number

v_line_number := v_line_number + 1;

BEGIN

--

-- Validate the Line Type

--

BEGIN

SELECT lookup_code

INTO v_line_type

FROM ap_lookup_codes

WHERE lookup_type = 'INVOICE DISTRIBUTION TYPE'

AND UPPER (displayed_field) = UPPER (NVL (c_inv_line_rec.line_type,'ITEM'));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg := ' Invalid Line Type';

fnd_file.put_line (fnd_file.output,'LINE TYPE:' || c_inv_line_rec.line_type);

END;

--

--Validation for CCID for distribution

--

v_dist_code_ccid:=get_ccid(c_inv_line_rec.prepayment_acct);

IF v_dist_code_ccid = 0

THEN

v_error_flag := TRUE;

g_err_msg := ' No Mapping for Invoice Distribution Code Combination:'||c_inv_line_rec.prepayment_acct;

fnd_file.put_line

(fnd_file.output,'INVOICE_NUM :'|| c_inv_rec.invoice_num|| ', LINE :'|| c_inv_line_rec.distribution_line_number

|| g_err_msg);

END IF;

--

--Update for Validated records.

--

IF v_error_flag = FALSE

THEN

v_invoice_type :=UPPER (c_inv_rec.invoice_type_lookup_code);

UPDATE apcx_il_ap_invoice_stg

SET GROUP_ID = v_ap_batch_id,

invoice_type_lookup_code = v_invoice_type,

payment_method_lookup_code = v_payment_method,

line_type='ITEM',

pay_group_lookup_code = v_pay_group,

invoice_currency_code = v_currency_code,

vendor_id = v_vendor_id,

vendor_site_id = v_vendor_site_id,

-- amount=line_amt ,

new_dist_code_combination_id = v_dist_code_ccid,

new_accts_pay_ccid_id = v_accts_pay_code_ccid,

--header_gl_date = v_gl_date,

--line_gl_date = v_gl_date,

source = v_source,

-- exchange_date = v_exch_date,

-- exchange_rate = v_exch_rate,

-- exchange_rate_type = v_exch_type,

process_flag = 'V',

error_message = 'VALIDATED'

WHERE invoice_num = c_inv_line_rec.invoice_num

AND distribution_line_number = c_inv_line_rec.distribution_line_number

AND SUPPLIER_NUMBER = c_inv_rec.SUPPLIER_NUMBER;

COMMIT;

ELSE

RAISE e_inv_hdr_excep;--Raise the Header exception instead of line exception

due to no more line should be marked as validated.

--

--If line errored out don't process header and lines

--

END IF;

EXCEPTION

WHEN e_inv_line_excep

THEN

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = c_inv_line_rec.invoice_num

AND distribution_line_number =c_inv_line_rec.distribution_line_number;

END;

END LOOP; -- Invoice Lines' Loop--

g_hdr_rec := g_hdr_rec +1;

END IF;

EXCEPTION

WHEN e_inv_hdr_excep

THEN

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = c_inv_rec.invoice_num

AND SUPPLIER_NUMBER = c_inv_rec.SUPPLIER_NUMBER;

COMMIT;

WHEN OTHERS

THEN

g_err_msg:=sqlerrm;

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = c_inv_rec.invoice_num

AND SUPPLIER_NUMBER = c_inv_rec.SUPPLIER_NUMBER;

END;

END LOOP; -- Invoice Header Loop --

fnd_file.put_line (fnd_file.output,'-----------------------------------------');

fnd_file.put_line (fnd_file.output,' Total Invoice records Processed:'||v_precess_rec);

fnd_file.put_line (fnd_file.output,' Total Invoice records Validated:'||g_hdr_rec);

fnd_file.put_line (fnd_file.output,'-----------------------------------------');

END VALIDATE;

PROCEDURE upload_invoices (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2)

IS

CURSOR c_inv(cp_source varchar2)

IS

SELECT DISTINCT inv_hdr.GROUP_ID,

inv_hdr.invoice_type_lookup_code,

inv_hdr.invoice_num,

inv_hdr.supplier_number,

inv_hdr.vendor_id,

inv_hdr.vendor_site_id,

inv_hdr.invoice_date,

inv_hdr.invoice_currency_code,

inv_hdr.invoice_amount,

inv_hdr.header_gl_date,

inv_hdr.description,

inv_hdr.exchange_rate,

inv_hdr.new_accts_pay_ccid_id,

inv_hdr.payment_method_lookup_code,

inv_hdr.pay_group_lookup_code,

inv_hdr.SOURCE

FROM apcx_il_ap_invoice_stg inv_hdr

WHERE process_flag = 'V'

AND inv_hdr.org_id=g_org_id

AND inv_hdr.INVOICE_SOURCE=P_SOURCE

ORDER BY supplier_number,invoice_num;

c_inv_rec c_inv%ROWTYPE;

CURSOR c_inv_line (cp_invoice_num VARCHAR2,cp_supplier_number VARCHAR2,cp_source varchar2)

IS

SELECT inv_line.invoice_num,

inv_line.distribution_line_number,

inv_line.line_type,

inv_line.amount,

inv_line.line_gl_date,

inv_line.new_dist_code_combination_id,

inv_line.prepayment_acct,

inv_line.ROWID

FROM apcx_il_ap_invoice_stg inv_line

WHERE invoice_num = cp_invoice_num --AND process_flag = 'V'

AND supplier_number = cp_supplier_number

AND inv_line.org_id=g_org_id

AND inv_line.INVOICE_SOURCE=P_SOURCE

ORDER BY distribution_line_number;

v_line_num NUMBER;

v_hdr_rec NUMBER :=0;

BEGIN

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'INSERTING RECORDS IN AP_INVOICES_INTERFACE TABLE');

FOR inv_hdr IN c_inv(p_source)

LOOP

v_line_num := 0;

--fnd_file.put_line (fnd_file.output,'Invoice#'|| inv_hdr.invoice_num);

BEGIN

INSERT INTO ap_invoices_interface

(GROUP_ID,

invoice_id,

invoice_num,

invoice_type_lookup_code,

invoice_date,

vendor_id,

vendor_site_id,

invoice_amount,

invoice_currency_code,

exchange_rate,

exchange_rate_type,

exchange_date,

description,

SOURCE,

payment_method_lookup_code,

pay_group_lookup_code,

gl_date,

accts_pay_code_combination_id,

org_id,

created_by,

creation_date,

last_updated_by,

last_update_date

)

VALUES (inv_hdr.GROUP_ID,

ap_invoices_interface_s.NEXTVAL,

inv_hdr.invoice_num,

inv_hdr.invoice_type_lookup_code,

inv_hdr.invoice_date,

inv_hdr.vendor_id,

inv_hdr.vendor_site_id,

inv_hdr.invoice_amount,

inv_hdr.invoice_currency_code,

inv_hdr.exchange_rate,

'User',

sysdate,

inv_hdr.description,

inv_hdr.SOURCE,

inv_hdr.payment_method_lookup_code,

inv_hdr.pay_group_lookup_code,

inv_hdr.header_gl_date,

inv_hdr.new_accts_pay_ccid_id,

g_org_id,

g_user_id,

SYSDATE,

g_user_id,

SYSDATE

);

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'INSERTING RECORDS IN AP_INVOICE_LINES_INTERFACE TABLE');

FOR inv_line IN c_inv_line (inv_hdr.invoice_num,inv_hdr.supplier_number,p_source)

LOOP

v_line_num := v_line_num + 1;

BEGIN

INSERT INTO ap_invoice_lines_interface

(invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

accounting_date,

dist_code_combination_id,

created_by,

creation_date,

last_updated_by,

last_update_date

)

VALUES (ap_invoices_interface_s.CURRVAL,

ap_invoice_lines_interface_s.NEXTVAL,

v_line_num,

inv_line.line_type,

inv_line.amount,

inv_line.line_gl_date,

inv_line.new_dist_code_combination_id,

g_user_id,

SYSDATE,

g_user_id,

SYSDATE

);

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'P',

error_message = 'INSERTED'

WHERE invoice_num = inv_hdr.invoice_num

AND distribution_line_number =inv_line.distribution_line_number

AND SUPPLIER_NUMBER = inv_hdr.SUPPLIER_NUMBER;

EXCEPTION

WHEN OTHERS

THEN

g_err_msg :='Invoice#'||inv_hdr.invoice_num||' DistLine#'||inv_line.distribution_line_number ||' '||SQLERRM;

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = inv_hdr.invoice_num

AND distribution_line_number = inv_line.distribution_line_number

AND SUPPLIER_NUMBER = inv_hdr.SUPPLIER_NUMBER;

END;

END LOOP;

v_hdr_rec := v_hdr_rec+1;

EXCEPTION

WHEN OTHERS

THEN

g_err_msg :='Invoice#'||inv_hdr.invoice_num||' '||SQLERRM;

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = inv_hdr.invoice_num

AND SUPPLIER_NUMBER = inv_hdr.SUPPLIER_NUMBER;

END;

END LOOP;

fnd_file.put_line (fnd_file.output,'----------------------------------');

fnd_file.put_line (fnd_file.output,' Total Invoice Inserted:'||v_hdr_rec);

fnd_file.put_line (fnd_file.output,'----------------------------------');

COMMIT;

END upload_invoices;

END apcx_asean_ap_invoice_conv_pkg;

/

===============================================================================

AP INTERFACE VALIDATION

Create Or REplace Procedure xx_invocie(errbuf out varchar2,
retcode out varchar2) as
cursor c1 is select * from xx_inv_headers;
cursor c2 is select * from xx_inv_dist;
i_num Varchar2(100);
l_flag Varchar2(1) default 'A'
begin



for x1 in c1 loop

Begin
-- Invoice Number Validation
SELECT invoice_num
INTO i_num
FROM ap_invoices_all
WHERE invoice_num = x1.invoice_num
and vendor_id = x1.vendor_id ;
Fnd_File.put_line(Fnd_File.output,'Invoice Number is Duplicate');
l_flag:='E';
Exception
When no_data_found then
Fnd_File.put_line(Fnd_File.output,'Invoice Number Valid');
l_flag:='A';
End;



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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SOURCE,
GL_DATE)
values(AP_INVOICES_INTERFACE_S.NEXTVAL,
X1.INVOICE_NUM,
X1.INVOICE_TYPE_LOOKUP_CODE,
X1.INVOICE_DATE,
X1.VENDOR_ID,
X1.VENDOR_SITE_ID,
X1.INVOICE_AMOUNT,
X1.INVOICE_CURRENCY_CODE,
X1.DESCRIPTION,
X1.CREATED_BY,
SYSDATE,
SYSDATE,
X1.LAST_UPDATED_BY,
SYSDATE,
X1.SOURCE,
X1.GL_DATE);
END LOOP;

FOR X2 IN C1 LOOP
begin
SELECT CODE_COMBINATION_ID
INTO l_code_id
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = x2.dist_code_combination_id;
exception
When others then
Fnd_File.put_line(Fnd_File.output,'Invalid Code Combination ID');
End;

Begin
SELECT lookup_code
into L_lookup
FROM ap_lookup_codes
WHERE LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
AND LOOKUP_CODE = X2.line_type_lookup_code;
exception
When others then
Fnd_File.put_line(Fnd_File.output,'Invalid Lookup');
End;


Insert into AP_INVOICE_LINES_INTERFACE
(
invoice_id,
invoice_line_id,
amount,
dist_code_combination_id,
description,
line_number,
line_type_lookup_code,
accounting_date,
creation_date,
created_by
)
values
(
APPS.AP_INVOICES_INTERFACE_S.CURRVAL,
APPS.AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
X2.AMOUNT,
X2.DIST_CODE_COMBINATION_ID,
X2.DESCRIPTION,
X2.LINE_NUMBER,
X2.LINE_TYPE_LOOKUP_CODE,
X2.ACCOUNTING_DATE,
X2.CREATION_DATE,
X2.CREATED_BY)
END LOOP;
END;