Oracle Apps: Supplier Interface Script in Oracle apps Part 3

END IF;

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_SITE_ID = ' || TO_CHAR (x_vendor_site_id));

DBMS_OUTPUT.

put_line (

'X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));

DBMS_OUTPUT.

put_line (

'X_LOCATION_ID = ' || TO_CHAR (x_location_id));

DBMS_OUTPUT.put_line ('');

fnd_file.

put_line (Fnd_File.LOG,

'X_RETURN_STATUS = ' || x_return_status);

--fnd_file. put_line ( '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_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));

fnd_file.

put_line (

Fnd_File.LOG,

'X_LOCATION_ID = ' || TO_CHAR (x_location_id));

--fnd_file. put_line ( Fnd_File.OUTPUT,'X_RETURN_STATUS = ' || x_return_status);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'P', VENDOR_SITE_ID = x_vendor_site_id

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

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);

fnd_file.put_line (Fnd_File.OUTPUT, x_msg_data);

END LOOP;

END IF;

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'P', supplier_id = ln_vendor_id

WHERE supplier_name = j.supplier_name

AND Batch_No = j.Batch_No

AND site_name = j.site_name;

COMMIT;

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

count_ins_sites_records :=

NVL (count_ins_sites_records, 0) + 1;

EXCEPTION

WHEN OTHERS

THEN

lc_error_message_server := ' Server Error'; -- DBMS.SQLERRM;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:29 Data Insertion error in Sites Table');

fnd_file.

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

fnd_file.

put_line (fnd_file.LOG, 'Message: ' || SQLERRM);

END;

lc_error_flag2 := '';

END IF; --IF lc_error_flag2 IS NOT NULL AND lc_error_flag2 <> 'Y' THEN

END IF; --IF lc_vendor_sites_flag='N' and (TRIM(j.status)!='P' OR TRIM(j.STATUS) IS NULL) then

END IF; --F NVL(ln_ven_site_flag,0)=1 then

lc_error_message := '';

lc_error_message_server := '';

lc_error_flag2 := '';

END LOOP;

COMMIT;

--CALLING SUPPLIERS SITES CONC PRG

/*v_request_id2 :=

fnd_request.submit_request ('SQLAP',

'APXSSIMP',

'',

FALSE,

'ALL',

'1000',

'N',

'N');

COMMIT;

IF fnd_concurrent.wait_for_request (request_id => v_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;*/

FOR err IN lcur_error

LOOP

BEGIN

UPDATE XXST_AP_SUPP_INT_STG

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

WHERE supplier_name = err.segment1

AND site_name = err.vendor_site_code;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

END LOOP;

-- Update the Audit Data Table for the processed Records

/* UPDATE XXST_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;*/

BEGIN

ln_after_total_Sites_count := 0;

SELECT COUNT (*)

INTO ln_after_total_Sites_count

FROM PO_VENDOR_SITES_ALL;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

--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;

fnd_file.

put_line (

fnd_file.OUTPUT,

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

COMMIT;

END ap_supp_sites_creation;

--------- Supplier Contacts Procedure

PROCEDURE ap_supp_contacts_creation (ERRBUF OUT VARCHAR2,

RETCODE OUT NUMBER,

p_batch_no IN VARCHAR2)

ln_org_id_c NUMBER;

lc_org_name VARCHAR2 (100);

lc_contacts_flag VARCHAR2 (2) := 'N';

ln_contacts_flag NUMBER := 0;

lc_error_flag1 VARCHAR2 (1) := 'N';

ln_conc_req_id NUMBER;

lc_error_message VARCHAR2 (200) := '';

ln_last_updated_by NUMBER;

ln_login_id NUMBER;

ln_user_id NUMBER;

ln_bef_total_Contact_count NUMBER;

ln_after_total_Contact_count NUMBER;

Ln_vendor_site_id po_vendor_sites_all.vendor_site_id%TYPE;

lc_error_message_server VARCHAR2 (200);

v_request_id3 NUMBER;

lc_phase VARCHAR2 (20);

lc_status VARCHAR2 (20);

lc_devpha VARCHAR2 (20);

lc_devsta VARCHAR2 (20);

lc_mesg VARCHAR2 (20);

v_request_id5 NUMBER;

ln_vendor_id NUMBER;

ln_party_site_id NUMBER;

--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_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;

x_vendor_contact_id NUMBER;

x_per_party_id NUMBER;

x_rel_party_id NUMBER;

x_rel_id NUMBER;

x_org_contact_id NUMBER;

x_party_site_id NUMBER;

v_msg_index_out NUMBER;

CURSOR CAPVCTAdd

SELECT *

FROM xxst_ap_supp_int_stg tkapcs

WHERE tkapcs.Batch_No = P_BATCH_NO

AND STATUS IS NULL

AND ERROR_MESSAGE IS NULL;

CURSOR lcur_error2

SELECT x.segment1,

y.vendor_id,

z.vendor_site_id,

--z.vendor_site_code,

z.last_name

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, first_name, last_name

FROM xxst_ap_supp_int_stg

WHERE vendor_site_id IS NOT NULL) z

WHERE 1 = 1

AND x.vendor_id = y.vendor_id

AND y.vendor_site_id = z.vendor_site_id;

CURSOR CAPVCTAdd IS

SELECT pvs.vendor_site_id,tkapcs.*

FROM xxst_ap_supp_int_stg tkapcs,

po_vendors pv,

po_vendor_sites_all pvs

WHERE pv.segment1=tkapcs.supplier_name

AND pv.vendor_id=pvs.vendor_id

AND tkapcs.SITE_NAME=PVS.VENDOR_SITE_CODE

AND tkapcs.Batch_No=P_BATCH_NO

ORDER BY tkapcs.supplier_name;*/

BEGIN

UPDATE xxst_ap_supp_int_stg

SET STATUS = NULL, error_message = NULL

WHERE batch_no = p_batch_no;

COMMIT;

--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;

gn_processed_rec := 0;

gn_errored_rec := 0;

gn_uploaded_rec := 0;

-- To get the User Id

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 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 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;

ln_bef_total_Contact_count := 0;

/*BEGIN

SELECT COUNT (*)

INTO ln_bef_total_Contact_count

FROM PO_VENDOR_CONTACTS;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;*/

FOR k IN CAPVCTAdd

LOOP

BEGIN

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

lc_contacts_flag := 'N';

lc_error_flag1 := 'N';

ln_contacts_flag := 0;

lc_error_message := '';

--getting vendor site id

Ln_vendor_site_id := NULL;

BEGIN

SELECT vendor_id, party_site_id, vendor_site_id

INTO ln_vendor_id, ln_party_site_id, Ln_vendor_site_id

FROM ap_supplier_sites_ALL

WHERE vendor_site_id = K.vendor_site_id;

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag1 := 'Y';

gn_errored_rec := gn_errored_rec + 1;

lc_error_message :=

'Supplier Site Does Not Exists'

|| ' Vendor Number '

|| k.supplier_name

|| 'Vendor Site Name '

|| k.SITE_NAME;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (p_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Supplier Site',

k.supplier_name,

k.SITE_NAME,

lc_error_message,

k.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_error_message

WHERE vendor_site_id = K.vendor_site_id;

COMMIT;

--WHERE record_id = k.record_id;

fnd_file.

put_line (fnd_file.LOG,

'ErrNo:32.0' || lc_error_message);

--END IF;

END;

fnd_file.

put_line (fnd_file.LOG,

'Vendor Site Id' || Ln_vendor_site_id);

/*IF Ln_vendor_site_id IS NOT NULL

THEN

BEGIN

SELECT COUNT (*)

INTO ln_contacts_flag

FROM PO_VENDOR_CONTACTS

WHERE vendor_site_id = Ln_vendor_site_id

AND UPPER (Last_name) = UPPER (TRIM (k.last_name));

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

END IF;*/

IF Ln_vendor_site_id IS NULL

THEN

lc_error_flag1 := 'Y';

gn_errored_rec := gn_errored_rec + 1;

lc_error_message :=

'Supplier Site Does Not Exists'

|| ' Vendor Number '

|| k.supplier_name

|| 'Vendor Site Name '

|| k.SITE_NAME;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (p_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Supplier Site',

k.supplier_name,

k.SITE_NAME,

lc_error_message,

k.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_error_message

WHERE supplier_name = k.supplier_name

AND Batch_No = k.Batch_No

AND SITE_NAME = k.SITE_NAME;

COMMIT;

--WHERE record_id = k.record_id;

fnd_file.

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

END IF;

fnd_file.

put_line (fnd_file.LOG,

'ln_contacts_flag' || ln_contacts_flag);

/*IF NVL(ln_contacts_flag,0)>=1 THEN

lc_contacts_flag:='Y';

lc_error_flag1 := 'Y';

gn_errored_rec:=gn_errored_rec+1;

lc_error_message := 'Supplier Same contact already Exists';

XXST_INT_UTILITY_PKG.generate_error_log_prc(p_batch_no,ln_conc_req_id,

'XXST_AP_SUPP_INT_STG','Supplier Contact','Supplier Contact',NULL,

k.SITE_NAME,lc_error_message,k.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E',

error_message = lc_error_message

WHERE supplier_name=k.supplier_name AND Batch_No=k.Batch_No AND SITE_NAME=k.SITE_NAME ;

--WHERE record_id = k.record_id;

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

END IF;*/

IF lc_contacts_flag = 'N'

THEN

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

-- Validation for Operating Unit

ln_org_id_c := NULL;

lc_org_name := NULL;

IF TRIM (k.operating_unit) IS NULL

THEN

BEGIN

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

SELECT name

INTO lc_org_name

FROM HR_OPERATING_UNITS

WHERE ORGANIZATION_ID = ln_org_id_c;

EXCEPTION

WHEN OTHERS

THEN

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

NULL;

END;

ELSE

BEGIN

SELECT organization_id, NAME

INTO ln_org_id_c, lc_org_name

FROM HR_OPERATING_UNITS

WHERE NAME = k.OPERATING_UNIT;

EXCEPTION

WHEN OTHERS

THEN

gn_errored_rec := gn_errored_rec + 1;

lc_error_flag1 := 'Y';

lc_error_message := ' Operating Unit is invalid';

XXST_INT_UTILITY_PKG.

generate_error_log_prc (p_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'OPERATING UNIT',

'OPERATING_UNIT',

k.supplier_name,

k.OPERATING_UNIT,

lc_error_message,

k.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_error_message

WHERE supplier_name = k.supplier_name

AND Batch_No = k.Batch_No

AND SITE_NAME = k.SITE_NAME

AND record_id = k.record_id;

--WHERE record_id = k.record_id;

fnd_file.

put_line (fnd_file.LOG,

'ErrNo:32' || lc_error_message);

END;

END IF;

------ Validation for Last Name

IF TRIM (k.last_name) IS NULL

THEN

BEGIN

lc_error_flag1 := 'Y';

lc_error_message := ' Last Name cannot be null';

XXST_INT_UTILITY_PKG.

generate_error_log_prc (p_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'LAST_NAME',

k.supplier_name,

k.last_name,

lc_error_message,

k.record_id);

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'E', error_message = lc_error_message

WHERE supplier_name = k.supplier_name

AND Batch_No = k.Batch_No

AND SITE_NAME = k.SITE_NAME

AND record_id = k.record_id;

fnd_file.

put_line (fnd_file.LOG,

'ErrNo:33' || lc_error_message);

EXCEPTION

WHEN OTHERS

THEN

gn_errored_rec := gn_errored_rec + 1;

lc_error_flag1 := 'Y';

lc_error_message := ' Last Name cannot be null';

XXST_INT_UTILITY_PKG.

generate_error_log_prc (p_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'LAST_NAME',

k.supplier_name,

k.last_name,

lc_error_message,

k.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET STATUS = 'E', ERROR_MESSAGE = lc_error_message

WHERE supplier_name = k.supplier_name

AND Batch_No = k.Batch_No

AND SITE_NAME = k.SITE_NAME

AND record_id = k.record_id;

--WHERE RECORD_ID = k.RECORD_ID;

fnd_file.

put_line (fnd_file.LOG,

'ErrNo:34' || lc_error_message);

END;

END IF;

fnd_file.

put_line (

fnd_file.LOG,

'vendor_site_code'

|| k.SITE_NAME

|| ':'

|| lc_error_flag1

|| ':'

|| Ln_vendor_site_id);

IF lc_error_flag1 = 'Y'

THEN

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

END IF;

fnd_file.

put_line (

fnd_file.LOG,

'New Address CNo:Before30'

|| lc_error_flag1

|| Ln_vendor_site_id);

IF lc_error_flag1 = 'N' AND Ln_vendor_site_id IS NOT NULL

THEN

fnd_file.

put_line (fnd_file.LOG, 'New Address CNo:After30');

BEGIN

/* INSERT

INTO ap_sup_site_contact_int (--vendor_site_code, -- To avoid (ORG INFO and VENDOR_SITE_CODE mismatch) error

vendor_site_id,

org_id,

operating_unit_name,

inactive_date,

first_name,

middle_name,

last_name,

last_name_alt,

prefix,

title,

area_code,

phone,

department,

status,

email_address,

url,

alt_area_code,

alt_phone,

fax_area_code,

fax,

last_update_date,

last_updated_by,

last_update_login,

creation_date,

created_by)

VALUES (--trim(k.site_name),

ln_vendor_site_id,

ln_org_id_c,

lc_org_name,

NULL, --k.inactive_date,

SUBSTR (INITCAP (TRIM (k.first_name)), 1, 15),

NULL, --trim(k.middle_name),

SUBSTR (INITCAP (TRIM (k.last_name)), 1, 20),

NULL, --initcap(trim(k.last_name)),

NULL, --trim(k.prefix),

NULL, --trim(k.title),

TRIM (k.std_isd_tel_code),

TRIM (k.telephone_no),

NULL, --trim(k.department),

'NEW', --trim(k.status),

TRIM (k.email_id),

TRIM (k.url),

NULL, --trim(k.area_code),

TRIM (k.mobile_no),

TRIM (k.std_isd_fax_code),

TRIM (k.fax_no),

SYSDATE,

ln_last_updated_by,

ln_login_id,

SYSDATE,

ln_user_id);*/

--ADDED BY XXXX

p_api_version := 1.0;

p_init_msg_list := 'T';

p_commit := 'T';

p_validation_level := FND_API.G_VALID_LEVEL_FULL;

x_return_status := NULL;

x_msg_count := NULL;

x_msg_data := NULL;

p_vendor_contact_rec.vendor_contact_id :=

po_vendor_contacts_s.NEXTVAL;

--DBMS_OUTPUT.put_line ('po_vendor_contacts_s.NEXTVAL = ' || po_vendor_contacts_s.NEXTVAL);

--fnd_file.put_line (Fnd_File.OUTPUT, 'po_vendor_contacts_s.NEXTVAL = ' || po_vendor_contacts_s.NEXTVAL);

P_VENDOR_CONTACT_REC.vendor_site_id := ln_vendor_site_id;

P_VENDOR_CONTACT_REC.org_id := ln_org_id_c;

P_VENDOR_CONTACT_REC.operating_unit_name := lc_org_name;

P_VENDOR_CONTACT_REC.inactive_date := NULL;

P_VENDOR_CONTACT_REC.PERSON_first_name :=

SUBSTR (INITCAP (TRIM (k.first_name)), 1, 15);

P_VENDOR_CONTACT_REC.PERSON_middle_name := NULL;

P_VENDOR_CONTACT_REC.PERSON_last_name :=

SUBSTR (INITCAP (TRIM (k.last_name)), 1, 20);

P_VENDOR_CONTACT_REC.PERSON_LAST_NAME_PHONETIC := NULL;

P_VENDOR_CONTACT_REC.prefix := NULL;

P_VENDOR_CONTACT_REC.PERSON_TITLE := NULL;

P_VENDOR_CONTACT_REC.area_code :=

TRIM (k.std_isd_tel_code);

P_VENDOR_CONTACT_REC.phone := TRIM (k.telephone_no);

P_VENDOR_CONTACT_REC.department := NULL;

---P_VENDOR_CONTACT_REC.status:=NULL;

P_VENDOR_CONTACT_REC.email_address := TRIM (k.email_id);

P_VENDOR_CONTACT_REC.url := TRIM (k.url);

P_VENDOR_CONTACT_REC.alt_area_code := NULL;

P_VENDOR_CONTACT_REC.alt_phone := TRIM (k.mobile_no);

P_VENDOR_CONTACT_REC.fax_area_code :=

TRIM (k.std_isd_fax_code);

P_VENDOR_CONTACT_REC.FAX_PHONE := TRIM (k.fax_no);

p_vendor_contact_rec.party_site_id := ln_party_site_id;

--p_vendor_contact_rec.org_party_site_id := 304651;

p_vendor_contact_rec.VENDOR_ID := ln_vendor_id;

x_vendor_contact_id := NULL;

x_per_party_id := NULL;

x_rel_party_id := NULL;

x_rel_id := NULL;

x_org_contact_id := NULL;

x_party_site_id := NULL;

ap_vendor_pub_pkg.

create_vendor_contact (p_api_version,

p_init_msg_list,

p_commit,

p_validation_level,

x_return_status,

x_msg_count,

x_msg_data,

p_vendor_contact_rec,

x_vendor_contact_id,

x_per_party_id,

x_rel_party_id,

x_rel_id,

x_org_contact_id,

x_party_site_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_CONTACT_ID = '

|| TO_CHAR (x_vendor_contact_id));

DBMS_OUTPUT.

put_line (

'X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));

DBMS_OUTPUT.

put_line (

'X_REL_PARTY_ID = ' || TO_CHAR (x_rel_party_id));

DBMS_OUTPUT.

put_line ('X_REL_ID = ' || TO_CHAR (x_rel_id));

DBMS_OUTPUT.

put_line (

'X_ORG_CONTACT_ID = ' || TO_CHAR (x_org_contact_id));

DBMS_OUTPUT.

put_line (

'X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_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_VENDOR_CONTACT_ID = '

|| TO_CHAR (x_vendor_contact_id));

fnd_file.

put_line (

Fnd_File.LOG,

'X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));

fnd_file.

put_line (

Fnd_File.LOG,

'X_REL_PARTY_ID = ' || TO_CHAR (x_rel_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;

UPDATE XXST_AP_SUPP_INT_STG

SET status = 'P'

WHERE supplier_name = k.supplier_name

AND Batch_No = k.Batch_No

AND SITE_NAME = k.SITE_NAME;

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

count_ins_cont_records :=

NVL (count_ins_cont_records, 0) + 1;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:35 Data Insertion Error for Contacts');

lc_error_message_server := 'Error'; -- DBMS.SQLERRM;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (p_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Server Error',

k.supplier_name,

'',

lc_error_message_server,

k.RECORD_ID);

UPDATE XXST_AP_SUPP_INT_STG

SET STATUS = 'E',

ERROR_MESSAGE = lc_error_message_server

WHERE supplier_name = k.supplier_name

AND Batch_No = k.Batch_No

AND SITE_NAME = k.SITE_NAME

AND record_id = k.record_id;

END;

lc_error_flag1 := 'N';

END IF;

lc_error_flag1 := 'N';

END IF;

END;

lc_error_flag1 := 'N';

END LOOP;

COMMIT;

/*v_request_id3 :=

fnd_request.submit_request ('SQLAP',

'APXSCIMP',

'',

FALSE,

'ALL',

'1000',

'N',

'N');

COMMIT;

IF fnd_concurrent.wait_for_request (request_id => v_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;*/

v_request_id5 :=

fnd_request.submit_request ('XXFTAL',

'XXFTAL_AP_SUPP_JA_IN_INT',

'',

FALSE,

p_batch_no);

COMMIT;

/* FOR err2 IN lcur_error2

LOOP

BEGIN

UPDATE XXST_AP_SUPP_INT_STG

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

WHERE supplier_name = err2.segment1

AND site_name = err2.vendor_site_code

AND last_name = err2.last_name

AND Batch_No = p_batch_no;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

END LOOP;*/

-- 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 = p_batch_no AND conc_request_id = ln_conc_req_id;

COMMIT;*/

ln_after_total_Contact_count := 0;

BEGIN

SELECT COUNT (*)

INTO ln_after_total_Contact_count

FROM PO_VENDOR_CONTACTS;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

BEGIN

SELECT COUNT (*)

INTO gn_errored_rec

FROM XXST_AP_SUPP_INT_STG

WHERE Batch_No = P_BATCH_NO AND ERROR_MESSAGE IS NOT NULL;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

BEGIN

SELECT COUNT (*)

INTO gn_processed_rec

FROM XXST_AP_SUPP_INT_STG

WHERE Batch_No = P_BATCH_NO;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

BEGIN

SELECT COUNT (*)

INTO gn_uploaded_rec

FROM XXST_AP_SUPP_INT_STG

WHERE Batch_No = P_BATCH_NO AND STATUS = 'P';

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

fnd_file.

put_line (

fnd_file.OUTPUT,

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

fnd_file.

put_line (

fnd_file.OUTPUT,

' Supplier Site Contact Conversion ');

fnd_file.

put_line (

fnd_file.OUTPUT,

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

fnd_file.

put_line (

fnd_file.OUTPUT,

'Total Suppliers Site Contacts Exists in EBS Before Submitting this Batch :'

|| ln_bef_total_Contact_count);

fnd_file.

put_line (

fnd_file.OUTPUT,

'Total Suppliers Site Contacts Exists in EBS After Submitting this Batch :'

|| ln_after_total_Contact_count);

fnd_file.

put_line (

fnd_file.OUTPUT,

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

fnd_file.

put_line (fnd_file.OUTPUT,

'Total Records Processed : ' || ' ' || gn_processed_rec);

fnd_file.

put_line (fnd_file.OUTPUT,

'Total Records Rejected : ' || ' ' || gn_errored_rec);

fnd_file.

put_line (fnd_file.OUTPUT,

'Total Records Inserted : ' || ' ' || gn_uploaded_rec);

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

BEGIN

XXST_INT_UTILITY_PKG.

PRT_AUDIT_ERROR_LOG_DATA (p_batch_no, ln_conc_req_id);

END;

COMMIT;

fnd_file.

put_line (

fnd_file.OUTPUT,

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

END ap_supp_contacts_creation;

------------ Localization Supplier information

PROCEDURE ap_localization_data_creation (

errBuff OUT NOCOPY VARCHAR2,

retCode OUT NOCOPY NUMBER,

p_batch_no IN VARCHAR2)

CURSOR lcur_ja_in

SELECT pv.vendor_id, --pvsa.vendor_site_id,

--pvsa.vendor_site_code,

a.*

FROM xxst_ap_supp_int_stg a,

ap_suppliers pv, --po_vendors pv,

ap_supplier_sites_all pvsa --po_vendor_sites_all pvsa

WHERE a.supplier_name = pv.vendor_name

AND (pv.vendor_id = pvsa.vendor_id

AND pvsa.vendor_site_code = a.site_name)

AND a.Batch_No = p_batch_no

ORDER BY a.supplier_name;

CURSOR lcur_ja_in1

SELECT *

FROM xxst_ap_supp_int_stg

WHERE status = 'P' AND batch_no = p_batch_no;

l_vendor_site_id NUMBER;

l_created_by fnd_user.user_id%TYPE;

l_vendor_id NUMBER;

ln_vendor_count NUMBER;

v_err_msg VARCHAR2 (9000);

record_identi VARCHAR2 (2000);

lc_service_code VARCHAR2 (30);

lc_tax_category_list VARCHAR2 (50);

l_vendor_tds fnd_lookup_values.lookup_code%TYPE;

lc_error_flag4 VARCHAR2 (1) := '';

lc_error_message VARCHAR2 (2000) := '';

ln_login_id NUMBER;

ln_conc_req_id NUMBER;

ln_user_id NUMBER;

ln_last_updated_by NUMBER; -- derived using FND_GLOBAL package

ln_set_of_book_id NUMBER;

ln_vendor_site_flag NUMBER := 0;

ln_vendor_site_Num NUMBER;

ln_insert_flag NUMBER := 0;

Ln_Total_Records_received NUMBER;

Ln_Total_Records_Processed NUMBER;

Ln_Total_Records_Errored NUMBER;

Ln_vendor_id1 NUMBER;

Ln_vendor_site_id1 NUMBER;

BEGIN

g_pn_batch_no1 := p_batch_no;

fnd_file.

put_line (

fnd_file.OUTPUT,

'=================================================================================');

fnd_file.

put_line (fnd_file.OUTPUT,

'Supplier Additional Info Conversion Details');

fnd_file.

put_line (

fnd_file.OUTPUT,

'=================================================================================');

fnd_file.

put_line (fnd_file.OUTPUT, 'Batch No :' || p_batch_no);

ln_Total_Records_received := 0;

BEGIN

SELECT COUNT (*)

INTO Ln_Total_Records_received

FROM xxst_ap_supp_int_stg tkapi,

ap_suppliers pv, --po_vendors pv,

ap_supplier_sites_all pvsa --po_vendor_sites_all pvsa

WHERE tkapi.supplier_name = pv.vendor_name

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 Received ' || Ln_Total_Records_received);

SELECT DECODE (TRIM (p_batch_no), 'ALL', NULL, TO_NUMBER (p_batch_no))

INTO pn_batch_no

FROM DUAL;

l_created_by := fnd_profile.VALUE ('USER_ID');

-- To get the User Id

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

FOR vendor_rec IN lcur_ja_in

LOOP

EXIT WHEN lcur_ja_in%NOTFOUND;

l_vendor_site_id := 0;

l_vendor_id := NULL;

ln_vendor_count := NULL;

lc_error_flag4 := '';

lc_error_message := '';

ln_insert_flag := 0;

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

BEGIN

SELECT vendor_id

INTO l_vendor_id

FROM ap_suppliers --po_vendors

WHERE UPPER (vendor_name) = UPPER (vendor_rec.supplier_name);

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag4 := 'Y';

lc_error_message :=

' Supplier not found ' || vendor_rec.supplier_name;

fnd_file.

put_line (fnd_file.LOG,

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

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;

--WHERE RECORD_ID=vendor_rec.RECORD_ID;

COMMIT;

END;

IF l_vendor_id IS NOT NULL

THEN

BEGIN

ln_vendor_count := 0;

SELECT COUNT (*)

INTO ln_vendor_count

FROM JAI_CMN_VENDOR_SITES --ja_in_po_vendor_sites

WHERE vendor_id = l_vendor_id AND vendor_site_id = 0; --ADDED BY XXXX

EXCEPTION

WHEN NO_DATA_FOUND

THEN

ln_vendor_count := 0;

WHEN OTHERS

THEN

NULL;

END;

END IF;

l_vendor_tds := NULL;

IF TRIM (vendor_rec.type_of_assessee) IS NOT NULL

THEN

BEGIN

SELECT lookup_code

INTO l_vendor_tds

FROM fnd_lookup_values

WHERE UPPER (meaning) =

UPPER (TRIM (vendor_rec.type_of_assessee))

AND lookup_type = 'JAI_TDS_VENDOR_TYPE';

EXCEPTION

WHEN OTHERS

THEN

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

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;

--WHERE record_id=vendor_rec.record_id;

COMMIT;

END;

END IF;

IF lc_error_flag4 = 'Y'

THEN

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

END IF;

--IF NVL(ln_vendor_count,0) = 0 THEN

IF ln_vendor_count = 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,

0, --ln_vendor_site_id=0,

NULL, --trim(vendor_rec.exc_duty_range),

NULL, --trim(vendor_rec.exc_duty_division),

TRIM (vendor_rec.EXCISE_REGISTRATION_NO),

NULL, --trim(vendor_rec.exc_duty_zone),

NULL, --trim(vendor_rec.exc_duty_region),

NULL, --trim(vendor_rec.exc_duty_circle),

NULL, --trim(vendor_rec.ec_code),

TRIM (vendor_rec.central_sales_tax_no),

'NA', --null,--trim(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, --trim(vendor_rec.tax_category_list),

SYSDATE,

l_created_by,

SYSDATE,

l_created_by);*/

--ADDED BY XXXXX

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,

0, --ln_vendor_site_id=0,

NULL, --trim(vendor_rec.exc_duty_range),

NULL, --trim(vendor_rec.exc_duty_division),

TRIM (vendor_rec.EXCISE_REGISTRATION_NO),

NULL, --trim(vendor_rec.exc_duty_zone),

NULL, --trim(vendor_rec.exc_duty_region),

NULL, --trim(vendor_rec.exc_duty_circle),

NULL, --trim(vendor_rec.ec_code),

TRIM (vendor_rec.central_sales_tax_no),

'NA', --null,--trim(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, --trim(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;

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:41' || 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);

fnd_file.

put_line (fnd_file.LOG, lc_error_message);

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

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;

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.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 XXXX

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.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

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:42' || lc_error_message || SQLERRM);

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;

--WHERE record_id=vendor_rec.record_id;

COMMIT;

END;

END IF; --vendor_rec.pan_no IS NOT NULL

END IF; --l_vendor_tds IS NOT NULL THEN

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

IF l_vendor_id IS NOT NULL

THEN

BEGIN

ln_vendor_site_num := 0;

SELECT vendor_site_id

INTO ln_vendor_site_num

FROM jai_cmn_vendor_sites --JA_IN_PO_VENDOR_SITES

WHERE vendor_id = l_vendor_id

AND vendor_site_id = vendor_rec.vendor_site_id;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

ln_vendor_site_num := 0;

WHEN OTHERS

THEN

NULL;

END;

NULL;

END IF;

Oracle Apps

Pages

Search This Blog

Thursday, June 20, 2013

Supplier Interface Script in Oracle apps Part 3

For Supplier Interface Script in Oracle apps Part 4 Click here

No comments:

Post a Comment