Oracle Apps: Supplier Interface Script in Oracle apps Part 1

create or replace PACKAGE BODY XXST_AP_SUPP_INT_PKG

/**********************************************************************************************************************

* Name : Rajashekar

* Purpose : The Package is Used for Data Interface for Supplier Creation

* Arguments : P_BATCH_NO

* Script Location : $XXST/sql

* Notes : This Package Specification defines the Procedure Defined in the Package

* Change History

* Date Name Ver Description

* ---------------------------------------------------------------------------------------------------------------------

* 27-Mar-2013 Rajashekar 1.0 Created for Interface for Supplier Creation from Stage table to R/12

************************************************************************************************************************/

PROCEDURE ap_vendor_creation (errBuff OUT NOCOPY VARCHAR2,

retCode OUT NOCOPY NUMBER,

p_batch_no IN VARCHAR2)

lc_supplier_name VARCHAR2 (50);

lc_supplier_site_code VARCHAR2 (50);

lc_site_name VARCHAR2 (50);

lc_phase VARCHAR2 (20);

lc_status VARCHAR2 (20);

lc_devpha VARCHAR2 (20);

lc_devsta VARCHAR2 (20);

lc_mesg VARCHAR2 (20);

lc_payment_method VARCHAR2 (30);

ln_ship_to_location_id NUMBER;

ln_bill_to_location_id NUMBER;

ln_org_id NUMBER;

ln_org_name VARCHAR2 (100);

ln_org_id_c NUMBER;

lc_lookup_type VARCHAR2 (30);

lc_pay_on_receipt VARCHAR2 (50);

ln_prepay_combination_id NUMBER;

ln_chart_of_accounts_id NUMBER;

ln_liability_combination_id NUMBER;

ln_future_combination_id NUMBER;

lc_error_message_server VARCHAR2 (1000);

lc_error_message_record VARCHAR2 (5000);

lc_error_message VARCHAR2 (9000); -- Error Message for Validations

lc_error_flag VARCHAR2 (1) := 'N'; -- Indicator for Error in Validations

lc_text VARCHAR2 (1);

ln_supplier_id NUMBER; -- derived from sequence AP_SUPPLIERS_INT_S

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

lc_vendor_type_lookuP_code VARCHAR2 (15);

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

lc_term_name VARCHAR2 (50);

lc_currency_code VARCHAR2 (15);

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

ln_user_id NUMBER; -- Derived the user Id from profile

ln_conc_req_id NUMBER; -- Derived the Concurrent Id from Profile

ln_login_id NUMBER; -- Derived using FND_GLOBAL package

ln_last_updated_by NUMBER; -- derived using FND_GLOBAL package

ln_set_of_book_id NUMBER; -- Derived using profiles.

ln_created_by NUMBER; -- derived using FND_GLOBAL package

ln_stg_vendor_count NUMBER := 0; -- Count the Staging Table Records for Duplicates

ln_base_vendor_count NUMBER := 0; -- Count the Base Table Records for Duplicates

ln_stg_vendor_sites_count NUMBER := 0;

ln_base_vendor_sites_count NUMBER := 0;

lc_vendor_sites_flag VARCHAR2 (1) := 'N'; -- Validation for New / Existing Vendor N for New.

lc_vendor_creation_flag VARCHAR2 (1) := '';

lc_lookup_code1 VARCHAR2 (50);

lc_lookup_code2 VARCHAR2 (50);

l_country_code fnd_territories_vl.territory_code%TYPE;

ln_sites_pay_site NUMBER := 0;

lc_errors_all VARCHAR2 (2000) := '';

ln_total_vendor_count NUMBER;

ln_total_vendor_count_after NUMBER;

ln_request_id1 NUMBER;

ln_request_id2 NUMBER;

ln_request_id3 NUMBER;

ln_request_id4 NUMBER;

lc_operating_unit VARCHAR2 (10);

lc_state_code VARCHAR2 (10);

lc_isupp_flag VARCHAR2 (5);

ln_operating_org_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_rec ap_vendor_pub_pkg.r_vendor_rec_type;

x_vendor_id NUMBER;

x_party_id NUMBER;

V_MSG_INDEX_OUT NUMBER;

CURSOR lcur_supp_dtl (

pv VARCHAR2)

(SELECT *

FROM xxst_ap_supp_int_stg

WHERE (TRIM (status) != 'P' OR TRIM (status) IS NULL)

AND (pv = 'ALL' OR Batch_No = TO_NUMBER (pv)));

--decode(pv,'ALL',1,TRIM(Batch_No))=decode(pv,'ALL',1,to_number(pv)));

BEGIN

-- To get the Batch No

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

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

-- To get the Operating Uni ID

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

-- Insert Audit Information Record in the Audit Table

/* BEGIN

XXST_INT_UTILITY_PKG.

MAINTAIN_AUDIT_DATA_PRC (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

NULL,

NULL);

END;

COMMIT;*/

fnd_file.

put_line (

Fnd_File.OUTPUT,

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

fnd_file.

put_line (

Fnd_File.OUTPUT,

' Supplier Master Interface ');

fnd_file.

put_line (

Fnd_File.OUTPUT,

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

-- Start to Process the Cursor Records

FOR i IN lcur_supp_dtl (p_batch_no)

LOOP

EXIT WHEN lcur_supp_dtl%NOTFOUND;

BEGIN

lc_error_flag := 'N';

lc_errors_all := ' ';

--Count the No of the Records Processed

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

---- Check for Duplicate Vendors in staging table

BEGIN

ln_stg_vendor_count := 0;

ln_base_vendor_count := 0;

BEGIN

SELECT COUNT (*)

INTO ln_stg_vendor_count

FROM xxst_ap_supp_int_stg

WHERE UPPER (TRIM (supplier_name)) =

UPPER (TRIM (i.supplier_name))

AND UPPER (TRIM (site_name)) =

UPPER (TRIM (i.site_name))

AND Batch_No = p_batch_no;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

--- Checking in Base table for supplier

BEGIN

SELECT COUNT (*)

INTO ln_base_vendor_count

FROM AP_SUPPLIERS --po_vendors

WHERE UPPER (TRIM (segment1)) =

UPPER (TRIM (i.supplier_name))

OR UPPER (TRIM (vendor_name)) =

UPPER (TRIM (i.supplier_name));

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

lc_operating_unit := NULL;

------------- Validation for Operating Unit

BEGIN

SELECT name

INTO lc_operating_unit

FROM HR_OPERATING_UNITS

WHERE NAME = i.OPERATING_UNIT

AND ORGANIZATION_ID = ln_operating_org_id;

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag := 'Y';

lc_error_message :=

'Invalid Operating Unit or You can not upload data for different operating unit';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'OPERATING_UNIT',

i.supplier_name,

i.operating_unit,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:1 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END;

------------- Validation for Operating Unit

BEGIN

SELECT NAME

INTO lc_operating_unit

FROM hr_operating_units

WHERE NAME = i.operating_unit;

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag := 'Y';

lc_error_message := 'Invalid Operating Unit';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'OPERATING_UNIT',

i.supplier_name,

i.operating_unit,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:2 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END;

--- Supplier Name should not be null

IF i.supplier_name IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'Supplier should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER_NAME',

i.supplier_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:3 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--- City Name should not be null

IF i.city IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'City should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER_NAME',

i.supplier_name,

i.city,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:3 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--- Pin Code should not be null

IF i.city IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'Postal Code should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER_NAME',

i.supplier_name,

i.pin_code,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:3 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--- State should not be null

IF (i.state IS NOT NULL OR i.state IS NULL)

THEN

BEGIN

SELECT lookup_code

INTO lc_state_code

FROM fnd_lookup_values

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

AND LOOKUP_TYPE IN

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

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag := 'Y';

lc_error_message := 'Invalid State Code';

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'STATE',

NULL,

i.supplier_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:4 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END;

END IF;

--- Site name should not be null

IF i.SITE_NAME IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'Site name should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SITE_NAME',

i.supplier_name,

i.site_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:5 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--- E mail id should not be null

IF i.email_id IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'E Mail id should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'EMAIL_ID',

i.supplier_name,

i.site_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:6 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- CST Tax No should not be null

IF i.central_sales_tax_no IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'CST Tax No should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'CENTRAL_SALES_TAX_NO',

i.supplier_name,

i.central_sales_tax_no,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:7 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- VAT No should not be null

IF i.Value_Added_Tax_No IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'VAT No should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'VALUE_ADDED_TAX_NO',

i.supplier_name,

i.value_added_tax_no,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

retcode := 2;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:8 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

END IF;

---- PAN No should not be null

IF i.PAN_NO IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'PAN No should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'PAN_NO',

i.supplier_name,

i.PAN_NO,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:9 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- PAN No should be 10 digits

IF LENGTH (TRIM (i.PAN_NO)) <> 10

THEN

lc_error_flag := 'Y';

lc_error_message := 'PAN No should be 10 digits';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'PAN_NO',

i.supplier_name,

i.PAN_NO,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:9 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--- PAN No should not be null

IF i.Type_Of_Assessee IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'Type of Assessee should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'TYPE_OF_ASSESSEE',

i.supplier_name,

i.TYPE_OF_ASSESSEE,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:10 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--- Supplier Bank Name should be not nul

IF i.Supplier_Bank_Name IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'Supplier Bank Name should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER_BANK_NAME',

i.supplier_name,

i.supplier_bank_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:11 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--- Supplier Bank Branch should not be null

IF i.Supplier_Bank_Branch_Name IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message :=

'Supplier Bank Branch Name should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER_BANK_BRANCH_NAME',

i.supplier_name,

i.supplier_bank_branch_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:12 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- IFSC Code should not be null

IF i.IFSC_No IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'IFSC No should not be is null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'IFSC_NO',

i.supplier_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:13 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- Supplier Bank Account No Code should not be null

IF i.Supplier_Bank_Acctount_No IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message :=

'Supplier Bank Account No should not be is null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER_BANK_ACCTOUNT_NO',

i.supplier_name,

i.SUPPLIER_BANK_ACCTOUNT_NO,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:14 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- Supplier Bank Account No Code should not be null

IF i.Supplier_Bank_Acctount_No IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message :=

'Supplier Bank Account No should not be is null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER_BANK_ACCTOUNT_NO',

i.supplier_name,

i.SUPPLIER_BANK_ACCTOUNT_NO,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:15 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- Invoice Initiator Email should not be null

IF i.Invoice_Initiator_Email IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message :=

'Invoice Initiator Email should not be is null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'INVOICE_INITIATOR_EMAIL',

i.supplier_name,

i.invoice_initiator_email,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:16 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

---- Payment Team Email should not be null

IF i.PAYMENT_TEAM_EMAIL IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message :=

'Payment Team Email should not be is null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'PAYMENT_TEAM_EMAIL',

i.supplier_name,

i.PAYMENT_TEAM_EMAIL,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:17 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--------Validation for Duplicate Suppliers

IF NVL (ln_stg_vendor_count, 0) > 1

THEN

lc_error_flag := 'Y';

lc_error_message :=

' Duplicate Supplier Name in Staging Table';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER NAME',

'SUPPLIR_NAME',

i.supplier_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:18 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

END IF;

lc_vendor_creation_flag := 'N';

ln_supplier_id := 0;

IF NVL (ln_base_vendor_count, 0) >= 1

THEN

lc_vendor_creation_flag := 'Y';

ln_supplier_id := i.supplier_id;

lc_error_message :=

'Supplier Already Exist in EBS: ' || i.supplier_name;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIER NAME',

'SUPPLIR_NAME',

i.supplier_name,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_error_message

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:19 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

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

END IF;

END;

lc_text := NULL;

IF TRIM (i.type_of_supplier) IS NOT NULL

THEN

BEGIN

SELECT DISTINCT 'X'

INTO lc_text

FROM po_lookup_codes

WHERE lookup_type = 'VENDOR TYPE'

AND UPPER (TRIM (lookup_code)) =

UPPER (TRIM (i.type_of_supplier));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

lc_error_flag := 'Y';

lc_error_message := ' Supplier Type is not valid';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Supplier Type',

'SUPPLIR_TYPE',

i.supplier_name,

i.type_of_supplier,

lc_error_message,

i.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:20 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

WHEN OTHERS

THEN

lc_error_flag := 'Y';

lc_error_message := ' Supplier Type is not valid';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Supplier Type',

'SUPPLIR_TYPE',

i.supplier_name,

i.type_of_supplier,

lc_error_message,

i.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:20 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END;

END IF;

--Added by XXXXX based approval from XXXXX

IF TRIM (i.type_of_supplier) IN

('Miscellaneous', 'Supply', 'VENDOR', 'Service')

THEN

lc_error_flag := 'Y';

lc_error_message := ' Supplier Type should not be used';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Supplier Type',

'SUPPLIR_TYPE',

i.supplier_name,

i.type_of_supplier,

lc_error_message,

i.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:20 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

--End of Addition by ramarao based approval from hemant on 26-DEC-2011

IF lc_text IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'Type of supplier should not be is null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'TYPE_OF_SUPPLIER',

i.supplier_name,

i.TYPE_OF_SUPPLIER,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:17 Record ID: = '

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

IF I.type_of_supplier IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := ' Supplier Type is not valid';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIR_TYPE',

i.supplier_name,

i.type_of_supplier,

lc_error_message,

i.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:20 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

-- Added by XXXXX

IF I.type_of_supplier = 'PACKING MATERIAL'

THEN

IF I.isupplier_flag IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message :=

' ISupplier Flag Cannot Be Null For Packing Material Supplier Type';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SUPPLIR_TYPE',

i.supplier_name,

i.type_of_supplier,

lc_error_message,

i.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:20 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

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

IF I.pin_code IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := ' Postal Code should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'PIN_CODE',

i.supplier_name,

i.PIN_CODE,

lc_error_message,

i.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:21 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

IF I.CITY IS NULL

THEN

lc_error_flag := 'Y';

lc_error_message := 'Address City should not be null';

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

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'CITY',

i.supplier_name,

i.CITY,

lc_error_message,

i.RECORD_ID);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:22 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END IF;

-- This validation for New vendor entry. Here lc_vendor_creation_flag='N' means new vendor.

IF lc_vendor_creation_flag = 'N'

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

THEN

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

----- Validation for Supplier Type

----------- Validation for Payment Term

ln_term_id := NULL;

lc_term_name := NULL;

IF TRIM (i.payment_terms_no_of_days) IS NOT NULL

THEN

lc_term_name := TRIM (i.payment_terms_no_of_days);

BEGIN

SELECT term_id

INTO ln_term_id

FROM AP_TERMS

WHERE UPPER (name) = UPPER (TRIM (lc_term_name))

AND ENABLED_FLAG = 'Y'

AND ( (SYSDATE BETWEEN START_DATE_ACTIVE

AND END_DATE_ACTIVE)

OR END_DATE_ACTIVE IS NULL);

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag := 'Y';

lc_error_message :=

' Supplier Payment Terms is invalid';

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Payment Term',

'PAYMENT_TERM',

i.supplier_name,

i.payment_terms_no_of_days,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:21 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END;

END IF;

---- Validation for Additional Info

---- select * from FND_DESCR_FLEX_CONTEXTS_VL where upper(DESCRIPTIVE_FLEX_CONTEXT_CODE)='ADDITIONAL INFO'

---- Validation for SOB

IF TRIM (i.set_of_books_id) IS NOT NULL

THEN

BEGIN

SELECT 'X'

INTO lc_text

FROM gl_sets_of_books

WHERE set_of_books_id = i.set_of_books_id;

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag := 'Y';

lc_error_message := ' Invalid Set of Books Id';

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'SET_OF_BOOKS_ID',

i.supplier_name,

i.set_of_books_id,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET status = 'E', error_message = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:22 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END;

END IF;

---------- Validation for Currency Code

IF TRIM (i.currency_code) IS NOT NULL

THEN

lc_currency_code := i.currency_code;

BEGIN

SELECT 'x'

INTO lc_text

FROM fnd_currencies

WHERE UPPER (currency_code) = UPPER (lc_currency_code)

AND enabled_flag = 'Y'

AND ( (SYSDATE BETWEEN start_date_active

AND end_date_active)

OR end_date_active IS NULL);

EXCEPTION

WHEN OTHERS

THEN

lc_error_flag := 'Y';

lc_error_message := ' Currency Code is invalid';

lc_errors_all :=

lc_errors_all || '/' || lc_error_message;

XXST_INT_UTILITY_PKG.

generate_error_log_prc (pn_batch_no,

ln_conc_req_id,

'XXST_AP_SUPP_INT_STG',

'Currency Code',

'CURRENCYCODE',

i.supplier_name,

i.currency_code,

lc_error_message,

i.record_id);

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

fnd_file.

put_line (

fnd_file.LOG,

'ErrNo:23 Record ID : ='

|| i.RECORD_ID

|| ' '

|| lc_error_message);

retcode := 2;

END;

END IF;

-- fnd_file.put_line(fnd_file.LOG,'Before Updating Flag Variable Value'||lc_error_flag||p_batch_no);

IF lc_error_flag = 'Y'

THEN

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

UPDATE xxst_ap_supp_int_stg

SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

COMMIT;

END IF;

-- No Errors in Above Validations then insert the data into Interface Table.

IF lc_error_flag = 'N'

THEN

--COMENTED BY SIVA ON24-DEC-2012 AS DISCUSSED WITH SRINU

BEGIN

/* BEGIN

SELECT ap_suppliers_int_s.NEXTVAL

INTO ln_supplier_id

FROM DUAL;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.

put_line (

fnd_file.LOG,

'AP_SUPPLIERS_INT_S Sequence Not Found'

|| lc_error_flag

|| ' '

|| p_batch_no);

END;*/

-- Added by XXXXXX

BEGIN

SELECT DECODE (ISUPPLIER_FLAG, 'YES', 'YES', 'NO')

INTO lc_isupp_flag

FROM XXST_AP_SUPP_INT_STG

WHERE supplier_name = i.supplier_name

AND site_name = i.site_name

AND Batch_No = p_batch_no;

EXCEPTION

WHEN OTHERS

THEN

lc_isupp_flag := 'NO';

END;

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

--COMENTED BY SIVA ON24-DEC-2012 AS DISCUSSED WITH SRINU

/* INSERT

INTO ap_suppliers_int (vendor_interface_id,

vendor_name,

vendor_name_alt,

segment1,

last_update_date,

last_updated_by,

last_update_login,

creation_date,

created_by,

vendor_type_lookup_code,

terms_id,

terms_name,

set_of_books_id,

pay_date_basis_lookup_code,

pay_group_lookup_code,

payment_priority,

invoice_currency_code,

payment_currency_code,

payment_method_lookup_code,

terms_date_basis,

one_time_flag,

small_business_flag,

attribute_category,

attribute1,

attribute2,

attribute3,

attribute14,

status,

start_date_active)

VALUES (ln_supplier_id, ---- 1

UPPER (TRIM (i.supplier_name)), ---- 2

NULL, --trim(i.alt_supplier_name), ---- 3

NULL, --trim(i.supplier_name), ---- 4

SYSDATE, ---- 5

ln_last_updated_by, ---- 6

ln_login_id, ---- 7

SYSDATE, ---- 8

ln_user_id, ---- 9

i.Type_Of_Supplier, ---- 10

ln_term_id, ---- 11

i.Payment_Terms_No_of_days, ---- 12

TRIM (i.set_of_books_id), --,ln_set_of_book_id), ---- 13

'DUE', --nvl(upper(trim(i.pay_date_basis)),'DUE'), ---- 14

NULL, --upper(trim(i.pay_group)), ---- 15

99, --NULL,--nvl(trim(i.payment_priority),99), ---- 16

TRIM (i.currency_code), ---- 17

TRIM (i.currency_code), ---- 18

'EFT', ---- 19

'Invoice', --(initcap(i.terms_date_basis)) ---- 20

i.one_time_supplier_yn, ---- 21

i.small_scale_industry_yn, ---- 22

NULL, --initcap(i.context_additional_info), ---- 23

i.edible_oil_licence_no, --trim(i.description), ---- 24

NULL, --i.additional_info, ---- 25

i.trade_licence_no, ---- 26

lc_isupp_flag, --'NO', ---- 27 -- Modified by Ramarao on 18-AUG-2011

'NEW', ---- 28

SYSDATE ---- 29

);*/

p_api_version := 1.0;

p_init_msg_list := FND_API.G_TRUE;

p_commit := FND_API.G_TRUE;

p_validation_level := FND_API.G_VALID_LEVEL_FULL;

x_return_status := NULL;

x_msg_count := NULL;

x_msg_data := NULL;

p_vendor_rec.vendor_name :=

UPPER (TRIM (i.supplier_name));

p_vendor_rec.vendor_name_alt := NULL;

p_vendor_rec.segment1 := NULL;

p_vendor_rec.vendor_type_lookup_code :=

i.Type_Of_Supplier;

p_vendor_rec.terms_id := ln_term_id;

p_vendor_rec.terms_name := i.Payment_Terms_No_of_days;

p_vendor_rec.set_of_books_id := TRIM (i.set_of_books_id);

p_vendor_rec.pay_date_basis_lookup_code := 'DUE';

p_vendor_rec.pay_group_lookup_code := NULL;

p_vendor_rec.payment_priority := 99;

p_vendor_rec.invoice_currency_code :=

TRIM (i.currency_code);

p_vendor_rec.payment_currency_code :=

TRIM (i.currency_code);

-- p_vendor_rec.payment_method_lookup_code:=null;

p_vendor_rec.terms_date_basis := 'Invoice';

p_vendor_rec.one_time_flag := i.one_time_supplier_yn;

p_vendor_rec.small_business_flag :=

i.small_scale_industry_yn;

p_vendor_rec.attribute_category := NULL;

p_vendor_rec.attribute1 := i.edible_oil_licence_no;

p_vendor_rec.attribute2 := NULL;

p_vendor_rec.attribute3 :=i.trade_licence_no;

p_vendor_rec.attribute14 :=lc_isupp_flag;

x_vendor_id := NULL;

x_party_id := NULL;

ap_vendor_pub_pkg.

create_vendor (p_api_version,

p_init_msg_list,

p_commit,

p_validation_level,

x_return_status,

x_msg_count,

x_msg_data,

p_vendor_rec,

x_vendor_id,

Oracle Apps

Pages

Search This Blog

Thursday, June 20, 2013

Supplier Interface Script in Oracle apps Part 1

For Supplier Interface Script in Oracle apps Part 2 Click here

For Supplier Interface Script in Oracle apps Part 3 Click here

For Supplier Interface Script in Oracle apps Part 4 Click here

No comments:

Post a Comment