Declare
l_user_id NUMBER;
v_customer_id NUMBER;
l_email VARCHAR2 (240);
l_error_message VARCHAR2 (240);
l_record_status BOOLEAN;
CURSOR c1
IS
SELECT *
FROM XX_CONC_USERS
where status = ‘R’
and error_mesg is null
FOR UPDATE;
BEGIN
FOR i IN c1
LOOP
l_record_status := TRUE;
l_error_message := ”;
BEGIN
SELECT a.OBJECT_ID
into v_customer_id
FROM hz_relationships a, hz_parties b
WHERE a.subject_id =
(SELECT party_id
FROM hz_parties
WHERE upper(party_name) = upper(i.vendor_name))
AND LOWER (b.party_name) LIKE LOWER (i.conc_last_name|| ‘%’)
AND a.party_id = b.party_id
AND b.party_type = ‘PARTY_RELATIONSHIP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_record_status := FALSE;
l_error_message := ‘Employee not exist…’;
END;
IF l_record_status = TRUE
THEN
BEGIN
fnd_user_pkg.createuser (x_user_name => LTRIM
(RTRIM
(i.conc_email
)
),
x_owner => NULL,
x_unencrypted_password => ‘123456’,
x_description => NULL,
x_customer_id => v_customer_id, —- OBJECT_ID of the Person
x_email_address => i.conc_email —– HZ_CONTACT_POINTS Email
);
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = upper(i.conc_email);
EXCEPTION
WHEN OTHERS
THEN
l_user_id := NULL;
END;
IF l_user_id IS NOT NULL
THEN
fnd_user_resp_groups_api.insert_assignment
(user_id => l_user_id,
responsibility_id => 23415,
responsibility_application_id => 396,
security_group_id => 0,
start_date => TRUNC
(SYSDATE
),
end_date => NULL,
description => NULL
);
UPDATE XX_CONC_USERS
SET status = ‘S’,
error_mesg = NULL
WHERE CURRENT OF c1;
ELSE
UPDATE XX_CONC_USERS
SET status = ‘R’,
error_mesg = ‘User Not Exist’
WHERE CURRENT OF c1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := SUBSTR (SQLERRM, 1, 200);
UPDATE XX_CONC_USERS
SET status = ‘R’,
error_mesg = l_error_message
WHERE CURRENT OF c1;
END;
ELSE
UPDATE XX_CONC_USERS
SET status = ‘E’,
error_mesg = l_error_message
WHERE CURRENT OF c1;
END IF;
END LOOP;
COMMIT;
END xx_create_user;
Recent Comments