Oracle Apps: Supplier Interface Script in Oracle apps Part 2

x_party_id);

COMMIT;

DBMS_OUTPUT.

put_line ('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.

put_line ('X_MSG_COUNT = ' || TO_CHAR (x_msg_count));

DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

DBMS_OUTPUT.

put_line ('X_VENDOR_ID = ' || TO_CHAR (x_vendor_id));

DBMS_OUTPUT.

put_line ('X_PARTY_ID = ' || TO_CHAR (x_party_id));

DBMS_OUTPUT.put_line ('');

fnd_file.

put_line (Fnd_File.LOG,

'X_RETURN_STATUS = ' || x_return_status);

fnd_file.

put_line (Fnd_File.LOG,

'X_MSG_COUNT = ' || TO_CHAR (x_msg_count));

fnd_file.

put_line (Fnd_File.LOG, 'X_MSG_DATA = ' || x_msg_data);

fnd_file.

put_line (Fnd_File.LOG,

'X_VENDOR_ID = ' || TO_CHAR (x_vendor_id));

fnd_file.

put_line (Fnd_File.LOG,

'X_PARTY_ID = ' || TO_CHAR (x_party_id));

COMMIT;

IF x_return_status = 'S'

THEN

COMMIT;

ELSE

ROLLBACK;

END IF;

IF x_msg_count > 0

THEN

FOR v_index IN 1 .. x_msg_count

LOOP

fnd_msg_pub.

get (p_msg_index => v_index,

p_encoded => 'F',

p_data => x_msg_data,

p_msg_index_out => v_msg_index_out);

x_msg_data := SUBSTR (x_msg_data, 1, 200);

DBMS_OUTPUT.put_line (x_msg_data);

END LOOP;

END IF;

--- Updates Process records to Interface Staging table

UPDATE xxst_ap_supp_int_stg

SET status = 'P' --, supplier_id=ln_supplier_id

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

count_ins_ven_records :=

NVL (count_ins_ven_records, 0) + 1;

gn_uploaded_rec := NVL (gn_uploaded_rec, 0) + 1;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:99 Data Insertion Error in Suppilers Interface Table');

lc_error_message_server :=

'Error at the time of Data insertion into Staging table'; -- dbms.sqlerrm;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Server Error',

i.supplier_name,

'',

lc_error_message_server,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E',

error_message = lc_error_message_server

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

END;

END IF;

lc_error_message := '';

lc_error_message_server := '';

lc_error_flag := '';

END;

END LOOP;

COMMIT;

--COMENTED BY SIVA ON 24-DEC-2012

/*BEGIN

---- Standard Supplier Creation concurrent program

ln_request_id1 :=

fnd_request.submit_request ('SQLAP',

'APXSUIMP',

'',

FALSE,

'ALL',

'1000',

'N',

'N');

COMMIT;

IF fnd_concurrent.wait_for_request (request_id => ln_request_id1,

INTERVAL => 2,

MAX_WAIT => 0,

phase => lc_phase,

status => lc_status,

dev_phase => lc_devpha,

dev_status => lc_devsta,

MESSAGE => lc_mesg)

THEN

COMMIT;

END IF;

END;*/

---- Supplier site interface Request submission starts

BEGIN

---- FTAL AP Supplier Site Creation Interface Program

ln_request_id2 :=

fnd_request.submit_request ('XXFTAL',

'XXFTAL_SUPP_SITE_INT',

'',

FALSE,

p_batch_no);

COMMIT;

IF fnd_concurrent.wait_for_request (request_id => ln_request_id2,

INTERVAL => 2,

MAX_WAIT => 0,

phase => lc_phase,

status => lc_status,

dev_phase => lc_devpha,

dev_status => lc_devsta,

MESSAGE => lc_mesg)

THEN

COMMIT;

END IF;

END;

---- Supplier Site Contact interface Request submission starts

BEGIN

---- FTAL AP Supplier Site Contact Creation Interface Program

ln_request_id3 :=

fnd_request.submit_request ('XXFTAL',

'XXFTAL_SUPP_SITE_CONT_INT',

'',

FALSE,

p_batch_no);

COMMIT;

IF fnd_concurrent.wait_for_request (request_id => ln_request_id3,

INTERVAL => 2,

MAX_WAIT => 0,

phase => lc_phase,

status => lc_status,

dev_phase => lc_devpha,

dev_status => lc_devsta,

MESSAGE => lc_mesg)

THEN

COMMIT;

END IF;

END;

BEGIN

ln_request_id4 :=

fnd_request.submit_request ('XXFTAL',

'XXFTAL_AP_SUPP_BANK_INT',

'',

FALSE,

p_batch_no);

COMMIT;

IF fnd_concurrent.wait_for_request (request_id => ln_request_id4,

INTERVAL => 2,

MAX_WAIT => 0,

phase => lc_phase,

status => lc_status,

dev_phase => lc_devpha,

dev_status => lc_devsta,

MESSAGE => lc_mesg)

THEN

COMMIT;

END IF;

END;

fnd_file.

put_line (

fnd_file.LOG,

' -------------------------------------------------------------------------------------');

fnd_file.

put_line (fnd_file.LOG, 'Supplier Interface Log Information ');

fnd_file.

put_line (

fnd_file.LOG,

' -------------------------------------------------------------------------------------');

fnd_file.

put_line (

fnd_file.LOG,

' -------------------------------------------------------------------------------------');

fnd_file.

put_line (

fnd_file.LOG,

'+---------------------------------------------------------------------------+');

fnd_file.put_line (fnd_file.LOG, ' ');

fnd_file.

put_line (fnd_file.LOG, ' Suppliers Interface Records');

fnd_file.

put_line (fnd_file.LOG, '+--------------------------------+');

fnd_file.

put_line (fnd_file.LOG,

' Total Records Processed :' || count_new_ven_records);

fnd_file.

put_line (fnd_file.LOG,

' Total Records Inserted :' || count_ins_ven_records);

fnd_file.

put_line (

fnd_file.LOG,

' Total Records Rejected :'

|| (count_new_ven_records - count_ins_ven_records));

fnd_file.put_line (fnd_file.LOG, ' ');

-- Update the Audit Data Table for the processed Records

/*UPDATE XXFTAL_INT_AUDIT_DATA

SET count_received = gn_processed_rec,

count_errored = gn_errored_rec,

count_uploaded = gn_uploaded_rec

WHERE batch_id = pn_batch_no AND conc_request_id = ln_conc_req_id;

COMMIT;*/

--Print The Audit and Error Information for the Processed Records

BEGIN

XXST_INT_UTILITY_PKG.

PRT_AUDIT_ERROR_LOG_DATA (pn_batch_no, ln_conc_req_id);

END;

COMMIT;

---- Calling error print procedure

ap_error_update;

COMMIT;

END ap_vendor_creation;

PROCEDURE ap_error_update

CURSOR lcur_error1

(SELECT segment1, status, reject_code

FROM ap_suppliers_int

WHERE status = 'REJECTED' OR status = 'R');

CURSOR lcur_error2

SELECT x.segment1,

y.vendor_id,

z.vendor_site_id,

z.vendor_site_code,

z.last_name,

z.reject_code

FROM (SELECT segment1, vendor_id FROM po_vendors) x,

(SELECT vendor_id, vendor_site_id FROM po_vendor_sites_all) y,

(SELECT vendor_site_id,

vendor_site_code,

last_name,

reject_code

FROM ap_sup_site_contact_int

WHERE status = 'REJECTED' OR status = 'R') z

WHERE 1 = 1

AND x.vendor_id = y.vendor_id

AND y.vendor_site_id = z.vendor_site_id;

BEGIN

FOR err1 IN lcur_error1

LOOP

EXIT WHEN lcur_error1%NOTFOUND;

BEGIN

UPDATE xxst_ap_supp_int_stg

SET status = 'R', error_message = err1.reject_code

WHERE supplier_name = err1.segment1;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END ap_error_update;

--------- Supplier Sites Procedure

PROCEDURE ap_supp_sites_creation (ERRBUF OUT VARCHAR2,

RETCODE OUT NUMBER,

p_batch_no IN VARCHAR2)

lc_payment_method VARCHAR2 (30);

ln_user_id NUMBER;

ln_conc_req_id NUMBER;

ln_login_id NUMBER;

ln_ship_to_location_id NUMBER;

ln_bill_to_location_id NUMBER;

ln_org_id NUMBER;

ln_org_name VARCHAR2 (100);

ln_org_id_c NUMBER;

lc_lookup_type VARCHAR2 (30);

lc_pay_on_receipt VARCHAR2 (50);

ln_distribution_set_id NUMBER;

ln_set_of_book_id NUMBER;

ln_prepay_combination_id gl_code_combinations_kfv.code_combination_id%TYPE;

--ln_prepay_combination_id NUMBER;

ln_chart_of_accounts_id NUMBER;

ln_liability_combination_id gl_code_combinations_kfv.code_combination_id%TYPE;

lc_prepay_acc_segments gl_code_combinations_kfv.concatenated_segments%TYPE;

lc_liability_acc_segments gl_code_combinations_kfv.concatenated_segments%TYPE;

--ln_liability_combination_id NUMBER;

ln_future_combination_id NUMBER;

lc_error_message_server VARCHAR2 (1000);

lc_error_message_record VARCHAR2 (5000);

lc_error_message VARCHAR2 (9000);

lc_error_flag2 VARCHAR2 (1) := 'N';

lc_text VARCHAR2 (1);

lr_vend_stg_row xxst_ap_supp_int_stg%ROWTYPE;

ln_supplier_id NUMBER; -- derived from sequence AP_SUPPLIERS_INT_S

ln_vend_category_id NUMBER; -- Look up value from fa_categories on given values

lc_vendor_type_lookuP_code VARCHAR2 (15);

ln_term_id NUMBER; -- term id derived from ap_terms for term name

lc_term_name VARCHAR2 (50);

lc_currency_code VARCHAR2 (15);

ln_location_id NUMBER; -- Loopk up value from fa_locations on given values

ln_last_updated_by NUMBER; -- derived using FND_GLOBAL package

ln_stg_vendor_count NUMBER := 0;

ln_base_vendor_count NUMBER := 0;

ln_stg_vendor_sites_count NUMBER := 0;

ln_base_vendor_sites_count NUMBER := 0;

lc_vendor_sites_flag VARCHAR2 (1) := 'N';

lc_vendor_creation_flag VARCHAR2 (1) := '';

lc_lookup_code1 VARCHAR2 (50);

lc_lookup_code2 VARCHAR2 (50);

l_country_code fnd_territories_vl.territory_code%TYPE;

ln_vendor_id NUMBER := 0;

ln_vendor_interface_id NUMBER := 0;

ln_ven_site_flag NUMBER := 0;

ln_sites_pay_site NUMBER := 0;

ln_pay_site_flag NUMBER := 0;

lc_pay_site VARCHAR2 (1) := NULL;

ln_supplier_name VARCHAR2 (30);

lc_errors_all VARCHAR2 (2000);

v_request_id2 NUMBER;

lc_phase VARCHAR2 (20);

lc_status VARCHAR2 (20);

lc_devpha VARCHAR2 (20);

lc_devsta VARCHAR2 (20);

lc_mesg VARCHAR2 (20);

ln_bef_total_Sites_count NUMBER;

ln_After_total_Sites_count NUMBER;

ln_vendor_site_id NUMBER;

ln_alt_site_id NUMBER;

lc_ship_via VARCHAR2 (100);

LC_terms_date_basis ap_lookup_codes.lookup_code%TYPE;

lc_state_code VARCHAR2 (10);

--ADDED BY XXXXX

p_api_version NUMBER;

p_init_msg_list VARCHAR2 (200);

p_commit VARCHAR2 (200);

p_validation_level NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

x_vendor_site_id NUMBER;

x_party_site_id NUMBER;

x_location_id NUMBER;

v_msg_index_out NUMBER;

--ADDED BY XXXXX

lv_party_id NUMBER;

lv_org_id NUMBER;

ext_payee_tab iby_disbursement_setup_pub.external_payee_tab_type;

ext_payee_id_tab iby_disbursement_setup_pub.ext_payee_id_tab_type;

ext_payee_create_tab iby_disbursement_setup_pub.ext_payee_create_tab_type;

l_ext_payee_rec iby_disbursement_setup_pub.external_payee_rec_type;

ext_payee_update_tab iby_disbursement_setup_pub.ext_payee_update_tab_type;

l_pay_return_status VARCHAR2 (50);

l_pay_msg_count NUMBER;

l_pay_msg_data VARCHAR2 (1000);

lv_error_reason VARCHAR2 (2000) := NULL;

lv_ext_payee_count NUMBER;

p_vendor_id NUMBER;

p_vendor_site_id NUMBER;

p_party_site_id NUMBER;

p_pay_method VARCHAR2 (20);

--This cursor fetches the sites details

CURSOR lcur_supp_site

SELECT ROWID, tkapvs.*

FROM xxst_ap_supp_int_stg tkapvs

WHERE Batch_No = p_batch_no

AND status IS NULL

AND ERROR_MESSAGE IS NULL

ORDER BY supplier_name;

CURSOR lcur_error

(SELECT asi.segment1,

assi.vendor_id,

assi.vendor_site_code,

assi.reject_code

FROM ap_supplier_sites_int assi, ap_suppliers_int asi

WHERE asi.vendor_interface_id = assi.vendor_interface_id

AND assi.status = 'REJECTED'

OR assi.status = 'R');

BEGIN

fnd_file.put_line (fnd_file.LOG, 'Enter into loop');

--g_pn_batch_no1:=p_batch_no;

--SELECT decode(trim(p_batch_no),'ALL',null,to_number(p_batch_no)) INTO pn_batch_no FROM DUAL;

UPDATE xxst_ap_supp_int_stg

SET STATUS = NULL, error_message = NULL

WHERE batch_no = p_batch_no;

COMMIT;

ln_user_id := FND_PROFILE.VALUE ('USER_ID');

IF ln_user_id IS NULL

THEN

ln_user_id := -1;

END IF;

ln_last_updated_by := FND_GLOBAL.USER_ID;

-- To get the Conc Request ID

ln_conc_req_id := FND_GLOBAL.CONC_REQUEST_ID;

IF ln_conc_req_id IS NULL

THEN

ln_conc_req_id := -1;

END IF;

-- To get the Login ID

ln_login_id := FND_GLOBAL.LOGIN_ID;

IF ln_login_id IS NULL

THEN

ln_login_id := -1;

END IF;

-- To get the Set of Book ID

ln_set_of_book_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

-- Insert Audit Information Record in the Audit Table

/* BEGIN

XXST_INT_UTILITY_PKG.

maintain_audit_data_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

NULL,

NULL);

END;

COMMIT;*/

BEGIN

ln_bef_total_Sites_count := 0;

SELECT COUNT (*)

INTO ln_bef_total_Sites_count

FROM PO_VENDOR_SITES_ALL;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

fnd_file.

put_line (fnd_file.LOG, 'Enter into loopbefore' || p_batch_no);

-- Start to Process the Cursor Records

FOR j IN lcur_supp_site

LOOP

EXIT WHEN lcur_supp_site%NOTFOUND;

ln_alt_site_id := NULL;

BEGIN

lc_vendor_sites_flag := 'N';

ln_ven_site_flag := 0;

ln_vendor_interface_id := 0;

ln_vendor_id := 0;

lc_error_flag2 := 'N';

lc_errors_all := ' ';

SELECT COUNT (*)

INTO ln_ven_site_flag

FROM po_vendors

WHERE vendor_name = TRIM (j.supplier_name);

EXCEPTION

WHEN OTHERS

THEN

ln_ven_site_flag := 0;

END;

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

BEGIN

IF NVL (ln_ven_site_flag, 0) = 0

THEN

lc_error_flag2 := 'Y';

lc_error_message := 'Supplier Does not exists';

gn_processed_rec := NVL (gn_processed_rec, 0) + 1;

gn_errored_rec := NVL (gn_errored_rec, 0) + 1;

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER NUMBER',

j.supplier_name,

lc_error_message,

j.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E',

error_message = 'Supplier Does Not Exists'

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

--- Getting vendor id from po_vendors for the respective site

BEGIN

IF NVL (ln_ven_site_flag, 0) = 1

THEN

SELECT vendor_id

INTO ln_vendor_id

FROM po_vendors

WHERE UPPER (vendor_name) = UPPER (TRIM (j.supplier_name));

/*SELECT vendor_interface_id

INTO ln_vendor_interface_id

FROM ap_suppliers_int

WHERE UPPER (vendor_name) = UPPER (TRIM (j.supplier_name))

AND (TRIM (status) = 'PROCESSED' OR TRIM (status) = 'P');*/

END IF;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

IF NVL (ln_ven_site_flag, 0) = 1

THEN

BEGIN

gn_processed_rec := NVL (gn_processed_rec, 0) + 1;

ln_stg_vendor_sites_count := 0;

ln_base_vendor_sites_count := 0;

lc_vendor_sites_flag := 'N';

BEGIN

SELECT COUNT (*)

INTO ln_stg_vendor_sites_count

FROM xxst_ap_supp_int_stg

WHERE supplier_name = j.supplier_name

AND site_name = j.Site_name

AND Batch_No = p_batch_no;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'CNo:8');

NULL;

END;

BEGIN

SELECT COUNT (*)

INTO ln_base_vendor_sites_count

FROM po_vendor_sites_all

WHERE vendor_site_code = j.site_name

AND vendor_id = (SELECT vendor_id

FROM po_vendors

WHERE segment1 = j.supplier_name);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'CNo:9');

NULL;

END;

--- Validation for Duplicate Suppiler Sites

IF NVL (ln_stg_vendor_sites_count, 0) > 1

THEN

lc_vendor_sites_flag := 'Y';

lc_error_message :=

' Duplicate Supplier Sites in Staging Table';

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SITE NAME',

'SITE_NAME',

j.supplier_name,

j.site_name,

lc_error_message,

j.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND SITE_NAME = j.SITE_NAME;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:10 Record ID=' || j.record_id || lc_error_message);

END IF;

IF NVL (ln_base_vendor_sites_count, 0) >= 1

THEN

gn_errored_rec := NVL (gn_errored_rec, 0) + 1;

lc_vendor_sites_flag := 'Y';

lc_error_message := ' Supplier Sites Already Exists in EBS';

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SITE NAME',

'SITE_NAME',

j.supplier_name,

j.site_name,

lc_error_message,

j.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:40 Record ID=' || j.record_id || lc_error_message);

END IF;

END;

IF lc_vendor_sites_flag = 'N'

AND (TRIM (j.status) != 'P' OR TRIM (j.STATUS) IS NULL)

THEN

count_new_sites_records := NVL (count_new_sites_records, 0) + 1;

-----Getting Chart of Account ID value from GL Set Of Books

BEGIN

SELECT chart_of_accounts_id

INTO ln_chart_of_accounts_id

FROM gl_sets_of_books

WHERE set_of_books_id = ln_set_of_book_id;

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag2 := 'Y';

lc_error_message := ' Invalid Chart of Account ID';

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'CHART OF ACCOUNTS ID',

j.supplier_name,

ln_set_of_book_id,

lc_error_message,

j.record_id);

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:19 Record ID='

|| j.RECORD_ID

|| lc_error_message);

retcode := 2;

END;

-- Validation for ACCTS_PAY_CODE_COMBINATION_ID in LIABILITY CONCANTENATED (liability combination id)

IF ln_chart_of_accounts_id IS NOT NULL

THEN

lc_prepay_acc_segments := NULL;

begin mo_global.set_policy_context(upper('s'),22);end;

fnd_file.

put_line (

fnd_file.LOG,'at line 2322' );

BEGIN

SELECT gcc.segment1

|| '.'

|| gcc.segment2

|| '.'

|| gcc.segment3

|| '.'

|| gcc.segment4

|| '.'

|| TRIM (j.prepayment_account)

|| '.'

|| gcc.segment6

|| '.'

|| gcc.segment7

|| '.'

|| gcc.segment8

Prepay_ccd

INTO lc_prepay_acc_segments

FROM financials_system_parameters fsp,

gl_code_combinations gcc

WHERE 1 = 1

AND fsp.prepay_code_combination_id =

gcc.code_combination_id(+);

EXCEPTION

WHEN OTHERS THEN

lc_error_flag2 := 'Y';

lc_error_message := ' Invalid Prepay Account ID';

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'PREPAY_ACCT_SEGMENT',

j.supplier_name,

lc_prepay_acc_segments,

lc_error_message,

j.record_id);

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:19 Record ID='

|| j.RECORD_ID

|| lc_error_message);

retcode := 2;

END;

lc_liability_acc_segments := NULL;

BEGIN

SELECT gcc.segment1

|| '.'

|| gcc.segment2

|| '.'

|| gcc.segment3

|| '.'

|| gcc.segment4

|| '.'

|| TRIM (j.liability_account)

|| '.'

|| gcc.segment6

|| '.'

|| gcc.segment7

|| '.'

|| gcc.segment8

Lib_ccd

INTO lc_liability_acc_segments

FROM financials_system_parameters fsp,

gl_code_combinations gcc

WHERE 1 = 1

AND fsp.accts_pay_code_combination_id =

gcc.code_combination_id(+);

EXCEPTION

WHEN OTHERS THEN

lc_error_flag2 := 'Y';

lc_error_message := ' Invalid Liability Account ID';

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'LIABILITY_ACCOUNT',

j.supplier_name,

lc_prepay_acc_segments,

lc_error_message,

j.record_id);

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:19 Record ID='

|| j.RECORD_ID

|| lc_error_message);

retcode := 2;

END;

ln_prepay_combination_id := NULL;

fnd_file.

put_line (

fnd_file.LOG,'at line 2428' );

BEGIN

ln_liability_combination_id := NULL;

ln_prepay_combination_id :=

fnd_flex_ext.

get_ccid (

application_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => ln_chart_of_accounts_id,

validation_date => TO_CHAR (SYSDATE,

'DD-MON-YYYY'),

concatenated_segments => lc_prepay_acc_segments);

END;

IF NVL (ln_prepay_combination_id, 0) = 0

THEN

lc_error_flag2 := 'Y';

lc_error_message :=

' Invalid Prepayment concatenated id';

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'PREPAYMENT_CONCATENATED',

j.supplier_name,

lc_prepay_acc_segments,

lc_error_message,

j.record_id);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND SITE_NAME = j.SITE_NAME;

-- Print the Error Message in the Log

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:20 Record ID='

|| j.RECORD_ID

|| lc_error_message);

retcode := 2;

END IF;

--to Get Expense account id

ln_liability_combination_id := NULL;

BEGIN

ln_liability_combination_id :=

fnd_flex_ext.

get_ccid (

application_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => ln_chart_of_accounts_id,

validation_date => TO_CHAR (SYSDATE,

'DD-MON-YYYY'),

concatenated_segments => lc_liability_acc_segments);

END;

IF NVL (ln_liability_combination_id, 0) = 0

THEN

lc_error_flag2 := 'Y';

lc_error_message := ' Invalid liability concatenated id';

lc_errors_all := lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'LIABILITY_CONCATENATED',

j.supplier_name,

lc_liability_acc_segments,

lc_error_message,

j.record_id);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

COMMIT;

-- Print the Error Message in the Log

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:20 Record ID='

|| j.RECORD_ID

|| lc_error_message);

--fnd_file.put_line(fnd_file.LOG,'Supplier Name : '||j.supplier_name||' : ' ||p_batch_no);

retcode := 2;

END IF;

--Validation for Prepay code combination field: PREPAYMENT_CONCATENATED

-- Validation for payment term

-- and retrieve the term_id from ap_terms

ln_term_id := NULL;

----- Validation for Operating Unit

ln_org_id := NULL;

ln_org_name := NULL;

IF j.operating_unit IS NULL

THEN

BEGIN

ln_org_id := fnd_profile.VALUE ('ORG_ID');

SELECT name

INTO ln_org_name

FROM hr_operating_units

WHERE organization_id = ln_org_id;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

ELSE

BEGIN

SELECT organization_id, NAME

INTO ln_org_id, ln_org_name

FROM hr_operating_units

WHERE NAME = j.operating_unit;

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag2 := 'Y';

lc_error_message := ' Operating Unit is invalid';

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'OPERATING UNIT',

'OPERATING_UNIT',

j.supplier_name,

j.OPERATING_UNIT,

lc_error_message,

j.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:27 Record ID='

|| j.RECORD_ID

|| lc_error_message);

retcode := 2;

END;

END IF;

----- Validation for Country code

l_country_code := NULL;

IF j.country IS NOT NULL

THEN

BEGIN

SELECT vl.territory_code

INTO l_country_code

FROM fnd_territories_vl vl

WHERE UPPER (territory_short_name) =

UPPER (TRIM (j.country));

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag2 := 'Y';

lc_error_message := 'Invalid Country Code';

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'COUNTRY',

j.supplier_name,

j.COUNTRY,

lc_error_message,

j.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:28 Record ID='

|| j.RECORD_ID

|| lc_error_message);

retcode := 2;

END;

END IF;

IF j.state IS NOT NULL

THEN

BEGIN

SELECT lookup_code

INTO lc_state_code

FROM fnd_lookup_values

WHERE UPPER (lookup_code) = UPPER (TRIM (j.state))

AND LOOKUP_TYPE IN

('IN_STATES', 'XXFTAL SUPPLIER STATES');

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag2 := 'Y';

lc_error_message := 'Invalid State Code';

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (p_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'STATE',

j.supplier_name,

j.state,

lc_error_message,

j.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:28 Record ID='

|| j.RECORD_ID

|| lc_error_message);

retcode := 2;

END;

END IF;

IF lc_error_flag2 = 'N'

THEN

BEGIN --XXST_AP_SUPP_INT_STG

/* INSERT

INTO ap_supplier_sites_int (

vendor_site_code,

vendor_site_code_alt,

vendor_interface_id,

vendor_id,

purchasing_site_flag,

rfq_only_site_flag,

pay_site_flag,

attention_ar_flag,

address_line1,

address_lines_alt,

address_line2,

address_line3,

address_line4,

city,

state,

zip,

province,

country,

area_code,

phone,

ship_to_location_id,

ship_to_location_code,

bill_to_location_id,

bill_to_location_code,

ship_via_lookup_code,

freight_terms_lookup_code,

fob_lookup_code,

inactive_date,

fax,

fax_area_code,

telex,

payment_method_lookup_code,

terms_date_basis,

distribution_set_id,

distribution_set_name,

accts_pay_code_combination_id,

prepay_code_combination_id,

pay_group_lookup_code,

payment_priority,

terms_id,

terms_name,

invoice_amount_limit,

pay_date_basis_lookup_code,

always_take_disc_flag,

invoice_currency_code,

payment_currency_code,

hold_all_payments_flag,

hold_future_payments_flag,

hold_reason,

hold_unmatched_invoices_flag,

org_id,

operating_unit_name,

edi_transaction_handling,

edi_id_number,

edi_payment_method,

edi_payment_format,

edi_remittance_method,

bank_charge_bearer,

edi_remittance_instruction,

default_pay_site_id,

pay_on_code,

pay_on_receipt_summary_code,

match_option,

country_of_origin_code,

future_dated_payment_ccid,

create_debit_memo_flag,

offset_tax_flag,

supplier_notif_method,

email_address,

remittance_email,

primary_pay_site_flag,

import_request_id,

status,

ATTRIBUTE_CATEGORY,

ATTRIBUTE1,

ATTRIBUTE2,

ATTRIBUTE3,

ATTRIBUTE4,

ATTRIBUTE5,

ATTRIBUTE6,

ATTRIBUTE7,

ATTRIBUTE8,

ATTRIBUTE9,

ATTRIBUTE10,

ATTRIBUTE11,

ATTRIBUTE12,

ATTRIBUTE13,

ATTRIBUTE14,

ATTRIBUTE15,

last_update_date,

last_updated_by,

last_update_login,

creation_date,

created_by)

VALUES (

SUBSTR (TRIM (j.site_name), 1, 15), --- 1

TRIM (j.alternate_supplier_site_if_any), --- 2

ln_vendor_interface_id, --- 3

ln_vendor_id, --- 4

NULL, --j.purchasing_site, --- 5

NULL, --(j.rfqs_only_site),--j.rfqs_only_site),'y'), --- 6

NULL, --j.pay_site, --- 7

NULL, --trim(j.attention_ar_flag), --- 8

j.address1, --- 9

j.alternate_supp_address_ifany, --- 10

j.address2, --- 11

j.address3, --- 12

j.address4, --- 13

j.city, --- 14

j.state, --- 15

j.pin_code, --- 16

NULL, --j.province, --- 17

l_country_code, --- 18

j.std_isd_tel_code, --- 19

j.telephone_no, --- 20

NULL, --ln_ship_to_location_id, --- 21

NULL, ----initcap(j.ship_to_location), --- 22

ln_bill_to_location_id, --- 23

NULL, ----j.bill_to_location, --- 24

DECODE (j.operating_unit,

'FTALOU', 'ROAD',

NULL), --trim(lc_ship_via), --- 25

NULL, --trim(j.freight_terms), --- 26

NULL, --trim(j.fob), --- 27

NULL, --j.inactive_date, --- 28

j.fax_no, --- 29

j.std_isd_fax_code, --- 30

NULL, --- 31

UPPER (lc_lookup_code2), --- 32

'Invoice', --nvl(initcap(lc_terms_date_basis),'INVOICE'), --- 33

ln_distribution_set_id, --- 34

NULL, ----trim(j.distribution_control_id), --- 35

ln_liability_combination_id, --j.liability_concatenated, --- 36

ln_prepay_combination_id, --j.prepayment_concatenated, --- 37

NULL, --upper(j.pay_group), --- 38

NULL, --nvl(j.payment_priority,99), --- 39

ln_term_id, --- 40

NULL, --trim(j.payment_terms), --- 41

NULL, --- 42

NULL, --nvl(upper(j.pay_date_basis),'DUE'), --- 43

NULL, --trim(j.always_take_disc), --- 44

NULL, --nvl(trim(j.invoice_currency),'INR'), --- 45

NULL, --nvl(trim(j.payment_currency),'INR'), --- 46

NULL, --trim(j.hold_all_payments), --- 47

NULL, --trim(j.future_payments_hold), --- 48

NULL, --trim(j.hold_reason), --- 49

NULL, --trim(j.hold_unmatched_invoices), --- 50

TRIM (j.org_id), --- 51

TRIM (j.operating_unit), --- 52

NULL, --trim(j.edi_transaction_handling), --- 53

NULL, --trim(j.edi_trading_partner_no), --- 54

NULL, --trim(j.edi_payment_method), --- 55

NULL, --trim(j.edi_payment_format), --- 56

NULL, --trim(j.edi_remittance_method), --- 57

NULL, --trim(j.bank_charge_bearer), --- 58

NULL, --trim(j.edi_remittance_instruction), --- 59

ln_alt_site_id, --- 60

'RECEIPT', --j.pay_on_code, --lc_pay_on_receipt, --- 61

'RECEIPT', --j.pay_on_receipt_code, -- --- 62

NULL, --decode(j.invoice_match_option),'Y','R','N','P',null),--- 63

l_country_code, -- Y- Receipt (R) , N for (P) Purchase Order.--- 64

ln_future_combination_id, --j.future_dt_payment_concatenated,--- 65

'Y', --trim(j.credit_debit_memo), --- 66

NULL, --- 67

NULL, --trim(j.supplier_notification_method), --- 68

TRIM (j.email_id), --- 69

NULL, --trim(j.remittance_mail), --- 70

NULL, --trim(j.primary_pay_site),--lc_pay_site --- 71

NULL, --- 72

'NEW', --j.status, --- 73

NULL, --j.ATTRIBUTE_CATEGORY, --- 74

1000, --j.ATTRIBUTE1, --- 75

NULL, --j.ATTRIBUTE2, --- 75

NULL, --j.ATTRIBUTE3, --- 76

NULL, --j.ATTRIBUTE4, --- 77

NULL, --j.ATTRIBUTE5, --- 78

NULL, --j.ATTRIBUTE6, --- 79

TRIM (j.invoice_initiator_email), --j.ATTRIBUTE7, --- 80

TRIM (j.payment_team_email), --j.ATTRIBUTE8, --- 81

TRIM (j.ifsc_no), --j.ATTRIBUTE9, --- 82

NULL, --j.ATTRIBUTE10, --- 83

TRIM (j.ifsc_no), --j.ATTRIBUTE11, --- 84

'Y', --j.ATTRIBUTE12, --- 85

NULL, --j.ATTRIBUTE13, --- 86

NULL, --j.ATTRIBUTE14, --- 87

NULL, --j.ATTRIBUTE15, --- 88

SYSDATE, --- 89

ln_last_updated_by, --- 90

ln_login_id, --- 91

SYSDATE, --- 92

ln_user_id --- 93

);

--ADDED BY XXXXX

p_api_version := 1.0;

p_init_msg_list := FND_API.G_TRUE;

p_commit := FND_API.G_TRUE;

p_validation_level := FND_API.G_VALID_LEVEL_FULL;

x_return_status := NULL;

x_msg_count := NULL;

x_msg_data := NULL;

P_VENDOR_SITE_REC.vendor_site_code :=

SUBSTR (TRIM (j.site_name), 1, 15);

P_VENDOR_SITE_REC.vendor_site_code_alt :=

TRIM (j.alternate_supplier_site_if_any);

P_VENDOR_SITE_REC.vendor_interface_id := NULL;

P_VENDOR_SITE_REC.vendor_id := ln_vendor_id;

P_VENDOR_SITE_REC.purchasing_site_flag := 'Y';

P_VENDOR_SITE_REC.rfq_only_site_flag := 'N';

P_VENDOR_SITE_REC.pay_site_flag := 'Y';

P_VENDOR_SITE_REC.attention_ar_flag := NULL;

P_VENDOR_SITE_REC.address_line1 := j.address1;

P_VENDOR_SITE_REC.address_lines_alt :=

j.alternate_supp_address_ifany;

P_VENDOR_SITE_REC.address_line2 := j.address2;

P_VENDOR_SITE_REC.address_line3 := j.address3;

P_VENDOR_SITE_REC.address_line4 := j.address4;

P_VENDOR_SITE_REC.city := j.city;

P_VENDOR_SITE_REC.state := j.state;

P_VENDOR_SITE_REC.zip := j.pin_code;

P_VENDOR_SITE_REC.province := NULL;

P_VENDOR_SITE_REC.country := 'US'; --l_country_code;

P_VENDOR_SITE_REC.area_code := j.std_isd_tel_code;

P_VENDOR_SITE_REC.phone := j.telephone_no;

P_VENDOR_SITE_REC.ship_to_location_id := NULL;

P_VENDOR_SITE_REC.ship_to_location_code := NULL;

P_VENDOR_SITE_REC.bill_to_location_id :=

ln_bill_to_location_id;

P_VENDOR_SITE_REC.bill_to_location_code := NULL;

--P_VENDOR_SITE_REC.ship_via_lookup_code:=DECODE (j.operating_unit, 'FTALOU', 'ROAD', NULL);

P_VENDOR_SITE_REC.freight_terms_lookup_code := NULL;

P_VENDOR_SITE_REC.fob_lookup_code := NULL;

P_VENDOR_SITE_REC.inactive_date := NULL;

P_VENDOR_SITE_REC.fax := j.fax_no;

P_VENDOR_SITE_REC.fax_area_code := j.std_isd_fax_code;

P_VENDOR_SITE_REC.telex := NULL;

--P_VENDOR_SITE_REC.payment_method_lookup_code:='CHECK';

--P_VENDOR_SITE_REC.PAYMENT_METHOD_CODE:='CHECK';-

P_VENDOR_SITE_REC.terms_date_basis := 'Invoice';

P_VENDOR_SITE_REC.distribution_set_id :=

ln_distribution_set_id;

P_VENDOR_SITE_REC.distribution_set_name := NULL;

P_VENDOR_SITE_REC.accts_pay_code_combination_id :=

ln_liability_combination_id;

P_VENDOR_SITE_REC.prepay_code_combination_id :=

ln_prepay_combination_id;

P_VENDOR_SITE_REC.pay_group_lookup_code := NULL;

P_VENDOR_SITE_REC.payment_priority := 99;

P_VENDOR_SITE_REC.terms_id := ln_term_id;

P_VENDOR_SITE_REC.terms_name := NULL;

P_VENDOR_SITE_REC.invoice_amount_limit := NULL;

P_VENDOR_SITE_REC.pay_date_basis_lookup_code := NULL;

P_VENDOR_SITE_REC.always_take_disc_flag := NULL;

P_VENDOR_SITE_REC.invoice_currency_code := NULL;

P_VENDOR_SITE_REC.payment_currency_code := NULL;

P_VENDOR_SITE_REC.hold_all_payments_flag := NULL;

P_VENDOR_SITE_REC.hold_future_payments_flag := NULL;

P_VENDOR_SITE_REC.hold_reason := NULL;

P_VENDOR_SITE_REC.hold_unmatched_invoices_flag := NULL;

P_VENDOR_SITE_REC.org_id := TRIM (j.org_id);

P_VENDOR_SITE_REC.org_name := TRIM (j.operating_unit);

--P_VENDOR_SITE_REC.operating_unit_name:='ABC Hyderabad OU';

P_VENDOR_SITE_REC.edi_transaction_handling := NULL;

P_VENDOR_SITE_REC.edi_id_number := NULL;

P_VENDOR_SITE_REC.edi_payment_method := NULL;

P_VENDOR_SITE_REC.edi_payment_format := NULL;

P_VENDOR_SITE_REC.edi_remittance_method := NULL;

P_VENDOR_SITE_REC.bank_charge_bearer := NULL;

P_VENDOR_SITE_REC.edi_remittance_instruction := NULL;

P_VENDOR_SITE_REC.default_pay_site_id := ln_alt_site_id;

P_VENDOR_SITE_REC.pay_on_code := 'RECEIPT';

P_VENDOR_SITE_REC.pay_on_receipt_summary_code :=

'RECEIPT';

P_VENDOR_SITE_REC.match_option := NULL;

P_VENDOR_SITE_REC.country_of_origin_code :=

l_country_code;

P_VENDOR_SITE_REC.future_dated_payment_ccid := NULL;

P_VENDOR_SITE_REC.create_debit_memo_flag := 'Y';

P_VENDOR_SITE_REC.offset_tax_flag := NULL;

P_VENDOR_SITE_REC.supplier_notif_method := NULL;

P_VENDOR_SITE_REC.email_address := TRIM (j.email_id);

P_VENDOR_SITE_REC.remittance_email := NULL;

P_VENDOR_SITE_REC.primary_pay_site_flag := NULL;

-- P_VENDOR_SITE_REC.import_request_id:=NULL;

--P_VENDOR_SITE_REC.status:=NULL;

P_VENDOR_SITE_REC.ATTRIBUTE_CATEGORY := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE1 := 1000;

P_VENDOR_SITE_REC.ATTRIBUTE2 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE3 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE4 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE5 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE6 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE7 :=

TRIM (j.invoice_initiator_email);

P_VENDOR_SITE_REC.ATTRIBUTE8 :=

TRIM (j.payment_team_email);

P_VENDOR_SITE_REC.ATTRIBUTE9 := TRIM (j.ifsc_no);

P_VENDOR_SITE_REC.ATTRIBUTE10 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE11 := TRIM (j.ifsc_no);

P_VENDOR_SITE_REC.ATTRIBUTE12 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE13 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE14 := NULL;

P_VENDOR_SITE_REC.ATTRIBUTE15 := NULL;

x_vendor_site_id := NULL;

x_party_site_id := NULL;

x_location_id := NULL;

ap_vendor_pub_pkg.

create_vendor_site (p_api_version,

p_init_msg_list,

p_commit,

p_validation_level,

x_return_status,

x_msg_count,

x_msg_data,

p_vendor_site_rec,

x_vendor_site_id,

x_party_site_id,

x_location_id);

IF x_return_status = 'S'

THEN

NULL; -----

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

p_vendor_id := ln_vendor_id; --37170;-- number;

p_vendor_site_id := x_vendor_site_id; --6972;-- number;

p_party_site_id := x_party_site_id; --224634;-- number;

p_pay_method := 'CHECK'; -- varchar2(20);

SELECT org_id

INTO lv_org_id

FROM ap_supplier_sites_all

WHERE vendor_site_id = p_vendor_site_id;

mo_global.set_policy_context ('S', lv_org_id);

SELECT party_id

INTO lv_party_id

FROM ap_suppliers

WHERE vendor_id = p_vendor_id;

SELECT COUNT (1)

INTO lv_ext_payee_count

FROM iby_external_payees_all payee

WHERE payee.payee_party_id = lv_party_id

AND payee.payment_function = 'PAYABLES_DISB'

AND payee.party_site_id = p_party_site_id

AND payee.supplier_site_id = p_vendor_site_id

AND payee.org_id = lv_org_id

AND payee.org_type = 'OPERATING_UNIT';

IF lv_ext_payee_count = 0

THEN

l_ext_payee_rec.payee_party_id := lv_party_id;

l_ext_payee_rec.payee_party_site_id :=

p_party_site_id;

l_ext_payee_rec.supplier_site_id :=

p_vendor_site_id;

l_ext_payee_rec.payment_function := 'PAYABLES_DISB';

l_ext_payee_rec.payer_org_id := lv_org_id;

l_ext_payee_rec.payer_org_type := 'OPERATING_UNIT';

l_ext_payee_rec.default_pmt_method := p_pay_method;

l_ext_payee_rec.inactive_date := NULL;

l_ext_payee_rec.Exclusive_Pay_Flag := 'N';

ext_payee_tab (1) := l_ext_payee_rec;

IBY_DISBURSEMENT_SETUP_PUB.

Create_External_Payee (

p_api_version => 1.0,

p_init_msg_list => FND_API.G_FALSE,

p_ext_payee_tab => ext_payee_tab,

x_return_status => l_pay_return_status,

x_msg_count => l_pay_msg_count,

x_msg_data => l_pay_msg_data,

x_ext_payee_id_tab => ext_payee_id_tab,

x_ext_payee_status_tab => ext_payee_create_tab);

IF l_pay_msg_count >= 1

THEN

FOR i IN 1 .. l_pay_msg_count

LOOP

IF lv_error_reason IS NULL

THEN

lv_error_reason :=

SUBSTR (

fnd_msg_pub.

get (p_encoded => fnd_api.g_false),

255);

ELSE

lv_error_reason :=

lv_error_reason || ' ,'

|| SUBSTR (

fnd_msg_pub.

get (

p_encoded => fnd_api.g_false),

255);

END IF;

END LOOP;

fnd_file.

put_line (fnd_file.LOG,

'API failed: ' || lv_error_reason);

DBMS_OUTPUT.

put_line ('API failed: ' || lv_error_reason);

END IF;

ELSE

SELECT exclusive_payment_flag,

default_payment_method_code,

ece_tp_location_code,

bank_charge_bearer,

bank_instruction1_code,

bank_instruction2_code,

bank_instruction_details,

payment_reason_code,

payment_reason_comments,

inactive_date,

payment_text_message1,

payment_text_message2,

payment_text_message3,

delivery_channel_code,

payment_format_code,

settlement_priority,

payee_party_id,

payment_function,

party_site_id,

supplier_site_id,

org_id,

org_type,

ext_payee_id

INTO ext_payee_tab (1).exclusive_pay_flag,

ext_payee_tab (1).Default_Pmt_method,

ext_payee_tab (1).ece_tp_loc_code,

ext_payee_tab (1).Bank_Charge_Bearer,

ext_payee_tab (1).Bank_Instr1_Code,

ext_payee_tab (1).Bank_Instr2_Code,

ext_payee_tab (1).Bank_Instr_Detail,

ext_payee_tab (1).Pay_Reason_Code,

ext_payee_tab (1).Pay_Reason_Com,

ext_payee_tab (1).Inactive_Date,

ext_payee_tab (1).Pay_Message1,

ext_payee_tab (1).Pay_Message2,

ext_payee_tab (1).Pay_Message3,

ext_payee_tab (1).Delivery_Channel,

ext_payee_tab (1).Pmt_Format,

ext_payee_tab (1).Settlement_Priority,

ext_payee_tab (1).payee_party_id,

ext_payee_tab (1).payment_function,

ext_payee_tab (1).Payee_Party_Site_Id,

ext_payee_tab (1).supplier_site_id,

ext_payee_tab (1).Payer_Org_Id,

ext_payee_tab (1).Payer_Org_Type,

ext_payee_id_tab (1).ext_payee_id

FROM iby_external_payees_all payee

WHERE payee.payee_party_id = lv_party_id

AND payee.payment_function =

'PAYABLES_DISB'

AND payee.party_site_id = p_party_site_id

AND payee.supplier_site_id =

p_vendor_site_id

AND payee.org_id = lv_org_id

AND payee.org_type = 'OPERATING_UNIT';

ext_payee_tab (1).default_pmt_method :=

p_pay_method;

--fnd_file.put_line (fnd_file.LOG, p_msg);

iby_disbursement_setup_pub.

update_external_payee (

p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_ext_payee_tab => ext_payee_tab,

p_ext_payee_id_tab => ext_payee_id_tab,

x_return_status => l_pay_return_status,

x_msg_count => l_pay_msg_count,

x_msg_data => l_pay_msg_data,

x_ext_payee_status_tab => ext_payee_update_tab);

--fnd_file.put_line (fnd_file.LOG,I1.VENDOR_NAME||'-'||'VENDOR'||I1.VENDOR_SITE_CODE||'-'||'VENDOR SITE'||l_pay_return_status||'-'||l_pay_msg_count||'-'||l_pay_msg_data);

IF l_pay_msg_count >= 1

THEN

FOR i IN 1 .. l_pay_msg_count

LOOP

IF lv_error_reason IS NULL

THEN

lv_error_reason :=

SUBSTR (

fnd_msg_pub.

get (p_encoded => fnd_api.g_false),

255);

ELSE

lv_error_reason :=

lv_error_reason || ' ,'

|| SUBSTR (

fnd_msg_pub.

get (

p_encoded => fnd_api.g_false),

255);

END IF;

END LOOP;

--fnd_file.put_line (fnd_file.LOG, p_msg);

DBMS_OUTPUT.

put_line ('API failed: ' || lv_error_reason);

END IF;

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

Oracle Apps

Pages

Search This Blog

Thursday, June 20, 2013

Supplier Interface Script in Oracle apps Part 2

For Supplier Interface Script in Oracle apps Part 3 Click here

For Supplier Interface Script in Oracle apps Part 4 Click here

No comments:

Post a Comment