Add PQH Roles and add users to it

Complete procedure to add PQH Roles and add users to it

PROCEDURE mpl_cse_create_roles(
errbuf OUT NOCOPY  VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2)
IS
/*************************************************************************************
**  Name       : MPL_CSE_CREATE_ROLES
**  DESCRIPTION: This procedure used to define roles for AME
**************************************************************************************/
l_num_role_id      pqh_roles.role_id%TYPE;
l_num_object_version_number   pqh_roles.object_version_number%type;
l_num_person_extra_info_id   per_people_extra_info.person_extra_info_id%type;
l_num_ovn_pie      per_people_extra_info.object_version_number%type;
l_num_person_id      per_people_extra_info.person_id%type;
l_num_loop_count        NUMBER;
l_var_user              VARCHAR2(1000);
l_var_err               VARCHAR2(1000);
l_num_addusrrole_id     NUMBER;
l_var_process           VARCHAR2(10);
CURSOR cur_role
IS
SELECT *
FROM mpl_cse_role_conv_stg
WHERE process_flag IS NULL;
BEGIN
l_num_object_version_number := NULL;
l_num_role_id               := NULL;
l_num_person_id             := NULL;
l_num_person_extra_info_id  := NULL;
l_num_ovn_pie               := NULL;
FOR cur_role_rec IN cur_role
LOOP
BEGIN
pqh_roles_api.create_role
( p_validate          => FALSE ,
p_role_id           => l_num_role_id ,
p_role_name         => cur_role_rec.role_name ,
p_business_group_id   => 0 ,
p_role_type_cd    => ‘EMP’ ,
p_enable_flag    => ‘Y’ ,
p_object_version_number  => l_num_object_version_number ,
p_effective_date    => SYSDATE ,
p_information_category  => cur_role_rec.location
);
COMMIT;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
l_var_err     := SQLERRM;
l_var_process :=’N’;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = ‘Role Could not be  created ‘
|| l_var_err
WHERE role_name = cur_role_rec.role_name;
FND_FILE.PUT_LINE(FND_FILE.LOG,’Role Could not be  created’);
END;
/*adding users to role*******/
/*checking number of user in a role*/
BEGIN
SELECT DECODE(cur_role_rec.member_1, NULL, 0, 1) + DECODE(cur_role_rec.member_2, NULL, 0, 1) + DECODE(cur_role_rec.member_3, NULL, 0, 1)
INTO l_num_loop_count
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_num_loop_count := 0;
l_var_process    :=’N’;
END;
IF cur_role_rec.member_1 IS NOT NULL OR cur_role_rec.member_2 IS NOT NULL OR cur_role_rec.member_3 IS NOT NULL THEN
FOR i IN 1..l_num_loop_count
LOOP
l_var_user          := NULL;
l_num_person_id     := NULL;
l_num_addusrrole_id := NULL;
l_var_process       := NULL;
IF i                 =1 THEN
l_var_user        := cur_role_rec.member_1_emp_num;
ELSIF i              =2 THEN
l_var_user        := cur_role_rec.member_2_emp_num;
ELSIF i              =3 THEN
l_var_user        := cur_role_rec.member_3_emp_num;
END IF;
BEGIN
SELECT person_id
INTO l_num_person_id
FROM per_all_people_f
WHERE employee_number =l_var_user
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_var_process :=’N’;
l_var_user    := NULL;
dbms_output.put_line(‘person_id:’||SQLERRM);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ User could not be added to Role. Employee Number: ‘
||cur_role_rec.member_1_emp_num
WHERE role_name = cur_role_rec.role_name;
END;
BEGIN
SELECT role_id
INTO l_num_addusrrole_id
FROM pqh_roles
WHERE role_name = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘role_id:’||SQLERRM);
l_var_process       :=’N’;
l_num_addusrrole_id := NULL;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ Role not valid’
WHERE role_name = cur_role_rec.role_name;
END;
IF NVL(l_var_process,’Y’) !=’N’ THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
( p_validate       => FALSE ,
p_person_id      => l_num_person_id ,
p_information_type     => ‘PQH_ROLE_USERS’ ,
p_pei_information_category   => ‘PQH_ROLE_USERS’ ,
p_pei_information3     => l_num_addusrrole_id ,
p_pei_information4     => ‘N’ ,
p_pei_information5     => ‘Y’ ,
p_pei_information9     => ‘N’ ,
p_person_extra_info_id    =>l_num_person_extra_info_id ,
p_object_version_number   =>l_num_ovn_pie
);
IF l_num_person_extra_info_id IS NOT NULL THEN
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
ELSE
DBMS_OUTPUT.PUT_LINE(‘USER NOT LOADED :’||l_num_person_id);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘Seeded api ERROR hr_person_extra_info_api.create_person_extra_info ‘
WHERE role_name = cur_role_rec.role_name;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,’user Could not be  be added to role’);
dbms_output.put_line(‘user Could not be  be added to role’||SQLERRM);
l_var_err := SUBSTR(SQLERRM,1000);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘user could not be adde for perosn_id’
||l_num_person_id
WHERE role_name = cur_role_rec.role_name;
END;
END IF;
END LOOP;
END IF ;
COMMIT;
END LOOP;
END mpl_cse_create_roles;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s