Oracle Apps: Interfaces

IF ln_vendor_site_num = 0

THEN

BEGIN

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

--COMENTED BY SIVA ON 26-DEC-2012

/*INSERT INTO JA_IN_PO_VENDOR_SITES (vendor_id,

vendor_site_id,

excise_duty_range,

excise_duty_division,

excise_duty_reg_no,

excise_duty_zone,

excise_duty_region,

excise_duty_circle,

ec_code,

cst_reg_no,

st_reg_no,

approved_invoice_flag,

vendor_type,

override_flag,

vat_reg_no,

service_tax_regno,

service_type_code,

tax_category_list,

creation_date,

created_by,

last_update_date,

last_updated_by)

VALUES (vendor_rec.vendor_id,

vendor_rec.vendor_site_id,

NULL, --vendor_rec.exc_duty_range,

NULL, --vendor_rec.exc_duty_division,

TRIM (vendor_rec.excise_registration_no),

NULL, --vendor_rec.exc_duty_zone,

NULL, --vendor_rec.exc_duty_region,

NULL, --vendor_rec.exc_duty_circle,

NULL, --vendor_rec.ec_code,

TRIM (vendor_rec.central_sales_tax_no),

NULL, --vendor_rec.lst_registration_no,

'Y',

NULL, --trim(vendor_rec.type_of_assessee),

'Y',

TRIM (vendor_rec.value_Added_tax_no),

TRIM (vendor_rec.service_tax_no),

TRIM (lc_service_code),

NULL, --vendor_rec.tax_category_list,

SYSDATE,

l_created_by,

SYSDATE,

l_created_by);*/

--ADDED BY XXXX

INSERT INTO JAI_CMN_VENDOR_SITES (vendor_id,

vendor_site_id,

excise_duty_range,

excise_duty_division,

excise_duty_reg_no,

excise_duty_zone,

excise_duty_region,

excise_duty_circle,

ec_code,

cst_reg_no,

st_reg_no,

approved_invoice_flag,

vendor_type,

override_flag,

vat_reg_no,

service_tax_regno,

service_type_code,

tax_category_list,

creation_date,

created_by,

last_update_date,

last_updated_by)

VALUES (vendor_rec.vendor_id,

vendor_rec.vendor_site_id,

NULL, --vendor_rec.exc_duty_range,

NULL, --vendor_rec.exc_duty_division,

TRIM (vendor_rec.excise_registration_no),

NULL, --vendor_rec.exc_duty_zone,

NULL, --vendor_rec.exc_duty_region,

NULL, --vendor_rec.exc_duty_circle,

NULL, --vendor_rec.ec_code,

TRIM (vendor_rec.central_sales_tax_no),

NULL, --vendor_rec.lst_registration_no,

'Y',

NULL, --trim(vendor_rec.type_of_assessee),

'Y',

TRIM (vendor_rec.value_Added_tax_no),

TRIM (vendor_rec.service_tax_no),

TRIM (lc_service_code),

NULL, --vendor_rec.tax_category_list,

SYSDATE,

l_created_by,

SYSDATE,

l_created_by);

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

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

ln_insert_flag := ln_insert_flag + 1;

UPDATE XXST_AP_SUPP_INT_STG

SET STATUS = 'P'

WHERE supplier_name = vendor_rec.supplier_name

AND Batch_No = vendor_rec.Batch_No

AND SITE_NAME = vendor_rec.SITE_NAME;

--WHERE RECORD_ID=vendor_rec.RECORD_ID;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag4 := 'Y';

lc_error_message :=

' Error Occured While inserting into JA_IN_PO_VENDOR_SITES tables ';

fnd_file.

put_line (fnd_file.LOG,

'ErrNo:44' || lc_error_message || SQLERRM);

XXST_INT_UTILITY_PKG.

GENERATE_ERROR_LOG_PRC (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'JA_IN_PO_VENDOR_SITES',

vendor_rec.supplier_name,

vendor_rec.vendor_id,

lc_error_message,

vendor_rec.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_error_message

WHERE supplier_name = vendor_rec.supplier_name

AND Batch_No = vendor_rec.Batch_No

AND site_name = vendor_rec.site_name;

fnd_file.

put_line (fnd_file.LOG, lc_error_message);

COMMIT;

END;

IF l_vendor_tds IS NOT NULL

THEN

IF vendor_rec.pan_no IS NOT NULL

AND LENGTH (TRIM (vendor_rec.pan_no)) = 10

THEN

BEGIN

--COMENTED BY SIVA ON 26-DEC-2012

/* INSERT

INTO ja_in_vendor_tds_info_hdr (

vendor_id,

vendor_site_id,

pan_no,

tan_no,

ward_no,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login,

confirm_pan_flag,

tds_vendor_type_lookup_code)

VALUES (vendor_rec.vendor_id,

vendor_rec.vendor_site_id,

vendor_rec.pan_no,

NULL, --vendor_rec.tan_no,

NULL, --vendor_rec.ward_no,

SYSDATE,

l_created_by,

SYSDATE,

l_created_by,

'Y',

l_vendor_tds);*/

--ADDED BY XXXXX

INSERT

INTO jai_ap_tds_vendor_hdrs (

vendor_id,

vendor_site_id,

pan_no,

tan_no,

ward_no,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login,

confirm_pan_flag,

tds_vendor_type_lookup_code)

VALUES (vendor_rec.vendor_id,

vendor_rec.vendor_site_id,

vendor_rec.pan_no,

NULL, --vendor_rec.tan_no,

NULL, --vendor_rec.ward_no,

SYSDATE,

l_created_by,

SYSDATE,

l_created_by,

'Y',

l_vendor_tds);

ln_insert_flag := ln_insert_flag + 1;

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'P'

WHERE supplier_name = vendor_rec.supplier_name

AND Batch_No = vendor_rec.Batch_No

AND site_name = vendor_rec.site_name;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_error_message

WHERE supplier_name = vendor_rec.supplier_name

AND Batch_No = vendor_rec.Batch_No

AND site_name = vendor_rec.site_name;

lc_error_flag4 := 'Y';

lc_error_message :=

'Error Occured While inserting into JA_IN_VENDOR_TDS_INFO_HDR tables ';

fnd_file.

put_line (fnd_file.LOG,

'ErrNo:45' || lc_error_message);

COMMIT;

END;

END IF; --IF vendor_rec.pan_no IS NOT NULL

END IF; --IF l_vendor_tds IS NOT NULL THEN

ELSE

BEGIN

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E',

error_message =

'Supplier Additional Info Assignment is Already exists'

WHERE supplier_name = vendor_rec.supplier_name

AND Batch_No = vendor_rec.Batch_No

AND site_name = vendor_rec.site_name;

-- lc_error_flag4 := 'Y';

-- lc_error_message := 'Error Occured While inserting into JA_IN_VENDOR_TDS_INFO_HDR tables ';

-- fnd_file.put_line(fnd_file.LOG,'ErrNo:45'||lc_error_message);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.

put_line (fnd_file.LOG,

'ErrNo:49' || lc_error_message);

END;

COMMIT;

END IF; --IF NVL(ln_vendor_site_num,0)=0 THEN

COMMIT;

ln_insert_flag := 0;

END LOOP;

COMMIT;

Ln_Total_Records_Processed := 0;

BEGIN

SELECT COUNT (*)

INTO Ln_Total_Records_Processed

FROM XXST_AP_SUPP_INT_STG tkapi,

po_vendors pv,

po_vendor_sites_all pvsa

WHERE tkapi.supplier_name = pv.vendor_name AND tkapi.status = 'P'

AND (pv.vendor_id = pvsa.vendor_id

AND pvsa.vendor_site_code = tkapi.site_name)

AND Batch_No = p_batch_no;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.

put_line (fnd_file.LOG, 'Error while Getting Total Count');

END;

fnd_file.

put_line (

fnd_file.OUTPUT,

'Total No of Records Processed ' || Ln_Total_Records_Processed);

FOR j IN lcur_ja_in1

LOOP

EXIT WHEN lcur_ja_in1%NOTFOUND;

Ln_vendor_id1 := NULL;

Ln_vendor_site_id1 := NULL;

--VENDOR ID VALIDATION

BEGIN

SELECT vendor_id

INTO Ln_vendor_id1

FROM ap_suppliers pv --po_vendors pv

WHERE 1 = 1 --pv.vendor_name = j.supplier_name

AND pv.vendor_name = j.supplier_name;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

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;

WHEN OTHERS

THEN

NULL;

END;

IF Ln_vendor_id1 IS NOT NULL

THEN

BEGIN

SELECT vendor_site_id

INTO Ln_vendor_site_id1

FROM po_vendor_sites_all pvsa

WHERE pvsa.vendor_id = Ln_vendor_id1

AND pvsa.vendor_site_code = j.site_name;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E',

error_message = 'Supplier Site Does Not Exists'

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

WHEN OTHERS

THEN

NULL;

END;

END IF;

COMMIT;

END LOOP;

Ln_Total_Records_Errored := 0;

BEGIN

SELECT COUNT (*)

INTO Ln_Total_Records_Errored

FROM XXST_AP_SUPP_INT_STG tkapi

WHERE tkapi.status = 'E' AND Batch_No = p_batch_no;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.

put_line (fnd_file.LOG, 'Error while Getting Total Count');

END;

fnd_file.

put_line (fnd_file.OUTPUT,

'Total No of Records Errored ' || Ln_Total_Records_Errored);

END ap_localization_data_creation;

FUNCTION get_field (p_line IN VARCHAR2,

p_delimiter IN VARCHAR2,

p_field_no IN NUMBER)

RETURN VARCHAR2

l_start NUMBER := 0;

l_end NUMBER := 0;

lc_delimiter VARCHAR2 (10);

BEGIN

lc_delimiter := p_delimiter;

/*****-- Determine start position--*****/

IF p_field_no = 1

THEN

l_start := 0;

ELSE

l_start :=

INSTR (p_line,

p_delimiter,

(p_field_no - 1));

IF l_start = 0

THEN

RETURN NULL;

END IF;

/*****-- Determine end position--*****/

l_end :=

INSTR (p_line,

p_delimiter,

p_field_no);

IF l_end = 0

THEN

l_end := LENGTH (p_line) + 1;

END IF;

/*****-- Extract the field data --*****/

IF (l_end - l_start) = 1

THEN

RETURN NULL;

ELSE

RETURN SUBSTR (p_line, (l_start + 1), ( (l_end - l_start) - 1));

END IF;

EXCEPTION

WHEN OTHERS

THEN

RETURN NULL;

END get_field;

PROCEDURE ap_supplier_upload (Errbuf OUT VARCHAR2,

Retcode OUT NUMBER,

p_file_name IN VARCHAR2,

P_LINE_NUM IN NUMBER)

reqfiletype UTL_FILE.FILE_TYPE;

req_line VARCHAR2 (3000);

req_delim VARCHAR2 (1) := ',';

rec_count NUMBER;

v_File_Name VARCHAR2 (200) := P_File_Name;

v_date_processed DATE; -- (Q)

ln_user_id NUMBER := FND_GLOBAL.USER_ID;

ln_resp_id NUMBER := FND_GLOBAL.RESP_ID;

ln_resp_app_id NUMBER := FND_GLOBAL.RESP_APPL_ID;

v_request_id NUMBER := 0;

ln_batch_id NUMBER;

Lc_Transaction_type VARCHAR2 (500);

ln_line_no NUMBER;

ln_record_id NUMBER;

lc_batch_no VARCHAR2 (50);

lc_operating_unit VARCHAR2 (25);

ln_supplier_id NUMBER;

lc_supplier_name VARCHAR2 (240);

lc_address1 VARCHAR2 (240);

lc_address2 VARCHAR2 (240);

lc_address3 VARCHAR2 (240);

lc_address4 VARCHAR2 (240);

lc_city VARCHAR2 (25);

lc_state VARCHAR2 (150);

lc_pin_code VARCHAR2 (20);

lc_country VARCHAR2 (150);

lc_site_name VARCHAR2 (15);

lc_alternate_supplier_site_any VARCHAR2 (320);

lc_alternate_supp_address_any VARCHAR2 (560);

lc_first_name VARCHAR2 (50);

lc_last_name VARCHAR2 (50);

lc_std_isd_tel_code VARCHAR2 (10);

lc_telephone_no VARCHAR2 (50);

lc_std_isd_fax_code VARCHAR2 (10);

lc_fax_no VARCHAR2 (15);

lc_mobile_no VARCHAR2 (15);

lc_email_id VARCHAR2 (200);

lc_url VARCHAR2 (2000);

lc_central_sales_tax_no VARCHAR2 (50);

lc_value_added_tax_no VARCHAR2 (50);

lc_service_tax_no VARCHAR2 (50);

lc_excise_registration_no VARCHAR2 (50);

lc_pan_no VARCHAR2 (30);

lc_edible_oil_licence_no VARCHAR2 (150);

lc_trade_licence_no VARCHAR2 (150);

lc_type_of_assessee VARCHAR2 (150);

lc_small_scale_industry_yn VARCHAR2 (10);

lc_type_of_supplier VARCHAR2 (30);

lc_isuplier_flag VARCHAR2 (5);

lc_payment_terms_no_of_days VARCHAR2 (50);

lc_one_time_supplier_yn VARCHAR2 (1);

lc_currency_code VARCHAR2 (10);

lc_supplier_bank_name VARCHAR2 (60);

lc_supplier_bank_branch_name VARCHAR2 (60);

lc_bank_branch_address1 VARCHAR2 (35);

lc_bank_branch_address2 VARCHAR2 (35);

lc_bank_branch_address3 VARCHAR2 (35);

lc_bank_branch_address4 VARCHAR2 (35);

lc_bank_city VARCHAR2 (50);

lc_bank_state VARCHAR2 (25);

lc_bank_country VARCHAR2 (25);

lc_bank_pin_code VARCHAR2 (20);

lc_ifsc_no VARCHAR2 (30);

lc_supplier_bank_acctount_no VARCHAR2 (80);

lc_liability_account VARCHAR2 (25);

lc_prepayment_account VARCHAR2 (25);

lc_invoice_initiator_email VARCHAR2 (150);

lc_payment_term_email VARCHAR2 (150);

ln_org_id NUMBER;

ln_set_of_book_id NUMBER;

ln_request_id NUMBER;

lc_status VARCHAR2 (50);

BEGIN

v_file_name := p_file_name;

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

Ln_batch_id := 0;

ln_line_no := 0;

BEGIN

SELECT xxst_s.NEXTVAL INTO ln_batch_id FROM DUAL;

EXCEPTION

WHEN OTHERS

THEN

FND_FILE.

PUT_LINE (FND_FILE.OUTPUT, ' Error While Getting Batch id');

END;

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

reqfiletype := UTL_FILE.FOPEN ('XXFTAL_AP_SUPP_DIR1', v_File_Name, 'r');

LOOP

ln_line_no := ln_line_no + 1;

BEGIN

UTL_FILE.GET_LINE (reqfiletype, req_line);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

EXIT;

END;

IF ln_line_no > p_line_num

THEN

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

--- Mapping with csv fields

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

--Sl Numner

BEGIN

ln_record_id := TRIM (Get_Field (req_line, req_delim, 1)); -- (A)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Sl Number' || SQLERRM);

END;

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

--Operating Unit

BEGIN

lc_operating_unit := TRIM (Get_Field (req_line, req_delim, 2)); -- (B)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Operating Unit' || SQLERRM);

END;

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

-- Supplier Name

BEGIN

lc_supplier_name := TRIM (Get_Field (req_line, req_delim, 3)); -- (C)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Supplier Name ' || SQLERRM);

END;

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

-- Address1

BEGIN

lc_address1 := TRIM (Get_Field (req_line, req_delim, 4)); -- (D)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Address1 ' || SQLERRM);

END;

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

---- Address2

BEGIN

lc_address2 := TRIM (Get_Field (req_line, req_delim, 5)); -- (E)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Address2 ' || SQLERRM);

END;

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

--Address3

BEGIN

lc_Address3 := TRIM (Get_Field (req_line, req_delim, 6)); -- (F)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Address3 ' || SQLERRM);

END;

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

--Address4

BEGIN

lc_Address4 := TRIM (Get_Field (req_line, req_delim, 7)); -- (G)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Address4 ' || SQLERRM);

END;

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

-- lc_city

BEGIN

lc_city := TRIM (Get_Field (req_line, req_delim, 8)); -- (H)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'City ' || SQLERRM);

END;

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

--State

BEGIN

lc_state := TRIM (Get_Field (req_line, req_delim, 9)); -- (I)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'State ' || SQLERRM);

END;

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

-- Pin Code

BEGIN

lc_pin_code := TRIM (Get_Field (req_line, req_delim, 10)); -- (J)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Pin Code ' || SQLERRM);

END;

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

--lc_country

BEGIN

lc_country := TRIM (Get_Field (req_line, req_delim, 11)); -- (K)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Country ' || SQLERRM);

END;

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

--Site Name

BEGIN

lc_site_name := TRIM (Get_Field (req_line, req_delim, 12)); -- (L)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Site Name ' || SQLERRM);

END;

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

--Alternate Supplier Site

BEGIN

lc_alternate_supplier_site_any :=

TRIM (Get_Field (req_line, req_delim, 13)); -- (M)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Alternate Supplier Site' || SQLERRM);

END;

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

--alternate supplier address

BEGIN

lc_alternate_supp_address_any :=

TRIM (Get_Field (req_line, req_delim, 14)); -- (N)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Alternate Supplier Address ' || SQLERRM);

END;

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

--First Name

BEGIN

lc_first_name := TRIM (Get_Field (req_line, req_delim, 15)); -- (O)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'First Name ' || SQLERRM);

END;

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

--Last Name

BEGIN

lc_last_name := TRIM (Get_Field (req_line, req_delim, 16)); -- (O)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Last Name ' || SQLERRM);

END;

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

--STD / ISD Tel Code

BEGIN

lc_std_isd_tel_code :=

TRIM (Get_Field (req_line, req_delim, 17)); -- (P)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'STD / ISD Tel Code ' || SQLERRM);

END;

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

----Telephone No

BEGIN

lc_telephone_no := TRIM (Get_Field (req_line, req_delim, 18)); -- (Q)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Telephone No ' || SQLERRM);

END;

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

--STD / ISD Fax Code

BEGIN

lc_std_isd_fax_code :=

TRIM (Get_Field (req_line, req_delim, 19)); -- (P)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'STD / ISD Fax Code ' || SQLERRM);

END;

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

---- Fax No

BEGIN

lc_fax_no := TRIM (Get_Field (req_line, req_delim, 20)); -- (R)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Fax No ' || SQLERRM);

END;

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

----Mobile No

BEGIN

lc_mobile_no := TRIM (Get_Field (req_line, req_delim, 21)); -- (S)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'Mobile No ' || SQLERRM);

END;

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

----E Mail Id

BEGIN

lc_email_id := TRIM (Get_Field (req_line, req_delim, 22)); -- (T)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'E Mail Id ' || SQLERRM);

END;

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

----URL

BEGIN

lc_url := TRIM (Get_Field (req_line, req_delim, 23)); -- (T)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'URL Web Address ' || SQLERRM);

END;

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

---- CST Reg No

BEGIN

lc_central_sales_tax_no :=

TRIM (Get_Field (req_line, req_delim, 24)); -- (U)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'CST Reg. No ' || SQLERRM);

END;

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

---- VAT Reg No

BEGIN

lc_value_added_tax_no :=

TRIM (Get_Field (req_line, req_delim, 25)); -- (V)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'VAT Reg. No ' || SQLERRM);

END;

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

----Service Tax No

BEGIN

lc_service_tax_no := TRIM (Get_Field (req_line, req_delim, 26)); -- (W)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Service Tax No ' || SQLERRM);

END;

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

----Excise Reg. No

BEGIN

lc_excise_registration_no :=

TRIM (Get_Field (req_line, req_delim, 27)); -- (X)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Excise Reg. No ' || SQLERRM);

END;

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

----PAN No

BEGIN

lc_pan_no := TRIM (Get_Field (req_line, req_delim, 28)); -- (Y)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'PAN No ' || SQLERRM);

END;

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

----EOL No

BEGIN

lc_edible_oil_licence_no :=

TRIM (Get_Field (req_line, req_delim, 29)); -- (Z)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'EOL No ' || SQLERRM);

END;

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

---- TL No

BEGIN

lc_trade_licence_no :=

TRIM (Get_Field (req_line, req_delim, 30)); -- (AA)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'TL No ' || SQLERRM);

END;

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

---- TDS Supplier Type

BEGIN

lc_type_of_assessee :=

TRIM (Get_Field (req_line, req_delim, 31)); -- (AB)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'TDS Supplier Type ' || SQLERRM);

END;

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

---- Small Scale Industry

BEGIN

lc_small_scale_industry_yn :=

TRIM (Get_Field (req_line, req_delim, 32)); -- (AC)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Small Scale Industry Y/N ' || SQLERRM);

END;

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

---- Supplier Type

BEGIN

lc_type_of_supplier :=

TRIM (Get_Field (req_line, req_delim, 33)); -- (AD)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Supplier Type ' || SQLERRM);

END;

-- Added by XXXXX

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

---- ISupplier Flag

BEGIN

lc_isuplier_flag := TRIM (Get_Field (req_line, req_delim, 34)); -- (AD)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Supplier Type ' || SQLERRM);

END;

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

-- End of Addition by Ramarao on 18-AUG-2011

---- Payment Terms No of Days

BEGIN

lc_payment_terms_no_of_days :=

TRIM (Get_Field (req_line, req_delim, 35)); -- (AE)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Payment Terms No of Days ' || SQLERRM);

END;

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

---- One Time Supplier Yes/ No

BEGIN

lc_one_time_supplier_yn :=

TRIM (Get_Field (req_line, req_delim, 36)); -- (AF)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'One Time Supplier Yes/No ' || SQLERRM);

END;

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

---- Currency Code

BEGIN

lc_currency_code := TRIM (Get_Field (req_line, req_delim, 37)); -- (AG)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Currency Code ' || SQLERRM);

END;

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

---- Supplier Bank Name

BEGIN

lc_supplier_bank_name :=

TRIM (Get_Field (req_line, req_delim, 38)); -- (AH)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Supplier Bank Name ' || SQLERRM);

END;

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

---- Supplier Bank Branch Name

BEGIN

lc_supplier_bank_branch_name :=

TRIM (Get_Field (req_line, req_delim, 39)); -- (AI)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Supplier Bank Branch Name ' || SQLERRM);

END;

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

---- Bank Branch Address1

BEGIN

lc_bank_branch_address1 :=

TRIM (Get_Field (req_line, req_delim, 40)); -- (AJ)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Bank Branch Address1 ' || SQLERRM);

END;

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

----Bank Branch Address2

BEGIN

lc_bank_branch_address2 :=

TRIM (Get_Field (req_line, req_delim, 41)); -- (AK)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Bank Branch Address2 ' || SQLERRM);

END;

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

----Bank Branch Address3

BEGIN

lc_bank_branch_address3 :=

TRIM (Get_Field (req_line, req_delim, 42)); -- (AL)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Bank Branch Address3 ' || SQLERRM);

END;

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

----Bank Branch Address4

BEGIN

lc_bank_branch_address4 :=

TRIM (Get_Field (req_line, req_delim, 43)); -- (AL)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Bank Branch Address4 ' || SQLERRM);

END;

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

---- Bank Branch City

BEGIN

lc_bank_city := TRIM (Get_Field (req_line, req_delim, 44)); -- (AM)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Bank Branch City ' || SQLERRM);

END;

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

---- Bank Branch State

BEGIN

lc_bank_state := TRIM (Get_Field (req_line, req_delim, 45)); -- (AN)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Bank Branch State ' || SQLERRM);

END;

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

---- Bank Branch Country

BEGIN

lc_bank_country := TRIM (Get_Field (req_line, req_delim, 46)); -- (AN)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Bank Branch Country ' || SQLERRM);

END;

---- Bank Branch Pin Code

BEGIN

lc_bank_pin_code := TRIM (Get_Field (req_line, req_delim, 47)); -- (AO)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Bank Branch Pin Code ' || SQLERRM);

END;

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

---- IFSC Code

BEGIN

lc_ifsc_no := TRIM (Get_Field (req_line, req_delim, 48)); -- (AP)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'IFSC Code ' || SQLERRM);

END;

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

---- Supplier Bank Account No

BEGIN

lc_supplier_bank_acctount_no :=

TRIM (Get_Field (req_line, req_delim, 49)); -- (AQ)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Supplier Bank Account No ' || SQLERRM);

END;

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

---- Liability Account

BEGIN

lc_liability_account :=

TRIM (Get_Field (req_line, req_delim, 50)); -- (AR)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Liability Account ' || SQLERRM);

END;

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

---- Prepayment Account

BEGIN

lc_prepayment_account :=

TRIM (Get_Field (req_line, req_delim, 51)); -- (AS)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Prepayment Account ' || SQLERRM);

END;

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

---- Invoice Initiator Email

BEGIN

lc_invoice_initiator_email :=

TRIM (Get_Field (req_line, req_delim, 52)); -- (AT)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Invoice Initiator Email ' || SQLERRM);

END;

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

---- Payment Team Member Email

BEGIN

lc_payment_term_email :=

TRIM (Get_Field (req_line, req_delim, 53)); -- (AU)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Payment Team Member Email ' || SQLERRM);

END;

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

---- Payment Team Member Email

BEGIN

lc_status := TRIM (Get_Field (req_line, req_delim, 54)); -- (AU)

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057, 'test ' || SQLERRM);

END;

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

IF lc_supplier_name IS NOT NULL

THEN

ln_org_id := NULL;

ln_set_of_book_id := NULL;

BEGIN

SELECT ORGANIZATION_ID, SET_OF_BOOKS_ID

INTO ln_org_id, ln_set_of_book_id

FROM HR_OPERATING_UNITS

WHERE NAME = lc_operating_unit;

EXCEPTION

WHEN OTHERS

THEN

ln_org_id := NULL;

ln_set_of_book_id := NULL;

END;

BEGIN

INSERT

INTO XXST_AP_SUPP_INT_STG (

record_id, -----1

batch_no, -----2

operating_unit, -----3

org_id, ------4

set_of_books_id, ------5

supplier_id, ------6

supplier_name, ------7

address1, ------8

address2, ------9

address3, ------10

address4, ------11

city,

state,

pin_code,

country,

site_name,

alternate_supplier_site_if_any,

alternate_supp_address_ifany,

first_name,

last_name,

std_isd_tel_code,

telephone_no,

std_isd_fax_code,

fax_no,

mobile_no,

email_id,

url,

central_sales_tax_no,

value_added_tax_no,

service_tax_no,

excise_registration_no,

pan_no,

edible_oil_licence_no,

trade_licence_no,

type_of_assessee,

small_scale_industry_yn,

type_of_supplier,

isupplier_flag, -- Added by XXXXX

payment_terms_no_of_days,

--one_time_supplier_yn,

currency_code,

supplier_bank_name,

supplier_bank_branch_name,

bank_branch_address1,

bank_branch_address2,

bank_branch_address3,

bank_city,

bank_state,

bank_pin_code,

ifsc_no,

supplier_bank_acctount_no,

liability_account,

prepayment_account,

invoice_initiator_email,

payment_team_email,

creation_date,

status,

input_file_name,

error_message)

VALUES (TRIM (ln_record_id),

TRIM (ln_batch_id),

TRIM (lc_operating_unit),

TRIM (ln_org_id),

TRIM (ln_set_of_book_id),

NULL, --supplier_id,

UPPER (TRIM (lc_supplier_name)),

TRIM (lc_address1),

TRIM (lc_address2),

TRIM (lc_address3),

TRIM (lc_address4),

TRIM (lc_city),

TRIM (lc_state),

TRIM (lc_pin_code),

TRIM (lc_country),

UPPER (TRIM (lc_site_name)),

TRIM (lc_alternate_supplier_site_any),

TRIM (lc_alternate_supp_address_any),

TRIM (lc_first_name),

TRIM (lc_last_name),

TRIM (lc_std_isd_tel_code),

TRIM (lc_telephone_no),

TRIM (lc_std_isd_fax_code),

TRIM (lc_fax_no),

TRIM (lc_mobile_no),

TRIM (lc_email_id),

TRIM (lc_url),

TRIM (lc_central_sales_tax_no),

TRIM (lc_value_added_tax_no),

TRIM (lc_service_tax_no),

TRIM (lc_excise_registration_no),

TRIM (lc_pan_no),

TRIM (lc_edible_oil_licence_no),

TRIM (lc_trade_licence_no),

TRIM (lc_type_of_assessee),

TRIM (lc_small_scale_industry_yn),

TRIM (lc_type_of_supplier),

TRIM (lc_isuplier_flag), -- Added by XXXXX

TRIM (lc_payment_terms_no_of_days),

--TRIM(lc_one_time_supplier_yn),

TRIM (lc_currency_code),

TRIM (lc_supplier_bank_name),

TRIM (lc_supplier_bank_branch_name),

TRIM (lc_bank_branch_address1),

TRIM (lc_bank_branch_address2),

TRIM (lc_bank_branch_address3),

TRIM (lc_bank_city),

TRIM (lc_bank_state),

TRIM (lc_bank_pin_code),

TRIM (lc_ifsc_no),

TRIM (lc_supplier_bank_acctount_no),

TRIM (lc_liability_account),

TRIM (lc_prepayment_account),

TRIM (lc_invoice_initiator_email),

TRIM (lc_payment_term_email),

TRIM (SYSDATE), --lc_creation_date,

NULL, --lc_status,

TRIM (p_file_name), --lc_input_file_name,

NULL --lc_error_message,

);

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20057,

'Error While Insert into Stg Table ' || SQLERRM);

END;

END IF;

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

END IF;

END LOOP;

COMMIT;

Fnd_File.

put_line (fnd_file.LOG,

'File Uploaded into interface table succussfully');

----Calling Supplier creation procedure

--XXFTAL_AP_SUPP_INT_PKG.ap_vendor_creation(ln_batch_id);

BEGIN

ln_request_id :=

fnd_request.submit_request ('XXFTAL',

'XXFTAL_SUPP_INT_CREATION',

'',

FALSE,

ln_batch_id);

COMMIT;

END;

UTL_FILE.FCLOSE_ALL;

EXCEPTION

WHEN UTL_FILE.INVALID_OPERATION

THEN

Fnd_File.

put_line (

Fnd_File.output,

'Invalid Operation: '

|| ' File Not Exit in AP Supplier Files Directory ');

UTL_FILE.FCLOSE_ALL;

RAISE_APPLICATION_ERROR (

-20051,

'File Not Exit in AP Supplier Files Directory');

WHEN UTL_FILE.INVALID_PATH

THEN

Fnd_File.put_line (Fnd_File.output, 'Invalid Path');

UTL_FILE.FCLOSE_ALL;

RAISE_APPLICATION_ERROR (-20052, 'Invalid Path');

WHEN UTL_FILE.INVALID_MODE

THEN

Fnd_File.put_line (Fnd_File.output, 'Invalid Mode');

UTL_FILE.FCLOSE_ALL;

RAISE_APPLICATION_ERROR (-20053, 'Invalid Mode');

WHEN UTL_FILE.INVALID_FILEHANDLE

THEN

Fnd_File.put_line (Fnd_File.output, 'Invalid File Handle ');

UTL_FILE.FCLOSE_ALL;

RAISE_APPLICATION_ERROR (-20054, 'Invalid File Handle');

WHEN UTL_FILE.WRITE_ERROR

THEN

Fnd_File.put_line (Fnd_File.output, 'Invalid Write Error ');

UTL_FILE.FCLOSE_ALL;

RAISE_APPLICATION_ERROR (-20055, 'Invalid Write Error');

WHEN UTL_FILE.READ_ERROR

THEN

Fnd_File.put_line (Fnd_File.output, 'Read Error ');

UTL_FILE.FCLOSE_ALL;

RAISE_APPLICATION_ERROR (-20056, 'Read Error');

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (-20057,

'Check the File Data Format ' || SQLERRM);

UTL_FILE.FCLOSE_ALL;

END ap_supplier_upload;

END XXST_AP_SUPP_INT_PKG;

This is the Supplier Interface in Oracle apps R12.

Sorry for inconvenience Due to data issues and performance loading i divided it into multiple parts don't keep in mind,

I hope this will help you..

Thanks all,
Rajashekara Reddy.

Oracle Apps

Pages

Search This Blog

Thursday, June 20, 2013

Supplier Interface Script in Oracle apps Part 4