SET SERVEROUTPUT ON SIZE 1000000;
SET DEFINE OFF;
/*
Author :- Sandeep Sharma
Date :- 11-MAR-2026
Description :- Process to load sales rep email id and phone number from external table to QUOTING_SALES_REP lookup
*/
DECLARE
CURSOR cur_lookup_dtl IS
SELECT
lookup_type,
security_group_id,
view_application_id
FROM
fnd_lookup_types
WHERE
lookup_type = 'QUOTING_SALES_REP';
CURSOR cur_salesrep IS
SELECT
person_id,
resource_id,
employee_number,
full_name,
email_address,
phone_number
FROM
quoting_sales_rep_extbl
WHERE
employee_number in (111111) ;
l_rowid VARCHAR2(100) := 0;
l_userid NUMBER := -1;
l_count NUMBER := 0;
l_flag VARCHAR2(2) := NULL;
BEGIN
dbms_output.enable(NULL);
dbms_output.put_line('Script To insert sales rep to QUOTING_SALES_REP lookup');
dbms_output.put_line('--------------------------------------------------------------------------------');
dbms_output.put_line('PERSON_ID,'
|| 'RESOURCE_ID,'
|| 'EMPLOYEE_NUMBER,'
|| 'FULL_NAME,'
--|| 'EMAIL_ADDRESS,'
--|| 'PHONE_NUMBER,'
|| 'L_FLAG');
l_count := 0;
FOR rec_lookup_dtl IN cur_lookup_dtl LOOP
FOR rec_salesrep IN cur_salesrep LOOP
BEGIN
l_rowid := NULL;
l_flag := NULL;
fnd_lookup_values_pkg.insert_row(x_rowid => l_rowid,
x_lookup_type => rec_lookup_dtl.lookup_type,
x_security_group_id => rec_lookup_dtl.security_group_id,
x_view_application_id => rec_lookup_dtl.view_application_id,
x_lookup_code => rec_salesrep.resource_id,
x_tag => rec_salesrep.person_id,
x_attribute_category =>rec_lookup_dtl.lookup_type,--NULL,
x_attribute1 => rec_salesrep.email_address,
x_attribute2 => rec_salesrep.phone_number,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active => sysdate,
x_end_date_active => NULL,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => rec_salesrep.employee_number,
x_description => rec_salesrep.full_name,
x_creation_date => sysdate,
x_created_by => l_userid,
x_last_update_date => sysdate,
x_last_updated_by => l_userid,
x_last_update_login => l_userid);
--COMMIT;
--DBMS_OUTPUT.put_line (j.X_LOOKUP_CODE || ' loaded');
--dbms_output.put_line('sss1: ' || l_flag);
l_flag := 'S';
--dbms_output.put_line('insert completed: ' || l_flag);
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line('Inner Exception: ' || sqlerrm);
dbms_output.put_line('Error occurred while inserting records into the lookup for the employee number: '
|| rec_salesrep.employee_number
|| ': '
|| sqlerrm);
l_flag := 'E';
END;
dbms_output.put_line(rec_salesrep.person_id
|| ','
|| rec_salesrep.resource_id
|| ','
|| rec_salesrep.employee_number
|| ','
|| rec_salesrep.full_name
--|| ','
--|| rec_salesrep.email_address
--|| ','
--|| l_flag
--|| ','
--|| trim(rec_salesrep.phone_number)
|| ','
|| l_flag
);
l_count := l_count + 1;
END LOOP;
END LOOP;
dbms_output.put_line('--------------------------------------------------------------------------------');
dbms_output.put_line('Total Records Processed = ' || l_count);
dbms_output.put_line('--------------------------------------------------------------------------------');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error :' || sqlerrm);
END;
/
