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',
'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,
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,
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
IS
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,
1,
(p_field_no - 1));
IF l_start = 0
THEN
RETURN NULL;
END IF;
END IF;
/*****-- Determine end position--*****/
l_end :=
INSTR (p_line,
p_delimiter,
1,
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)
IS
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.
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.
Hi , Can you share the input file and the interface program to itsramesha@gmail.com
ReplyDelete