How to create Adhoc role in Workflow
PROCEDURE create_send_adhoc_role_prc (
p_to_send_role IN OUT VARCHAR2,
p_to_send_user_name IN VARCHAR2,
p_user_name_tbl IN user_name_tbl,
p_rtn_status OUT VARCHAR2,
p_rtn_msg OUT VARCHAR2
)
IS
l_role_cnt NUMBER := 0;
l_user_name fnd_user.user_name%TYPE := NULL;
l_delete_user_name VARCHAR2 (2000) := NULL;
--store multiple user name with comma seperated
l_lst_delete_user VARCHAR2 (2000) := NULL;
--store multiple user name with comma seperated
l_user_name_tbl user_name_tbl := p_user_name_tbl;
l_rtn_msg VARCHAR2 (2000) := NULL;
l_user_del_sql VARCHAR2 (2000) := NULL;
l_role_orig_system VARCHAR2 (50) := 'WF_LOCAL_ROLES';
-------Get all the User exists in the role excepts the user pass---
CURSOR cur_role_user (v_user_exists VARCHAR2)
IS
SELECT DISTINCT user_name
FROM wf_user_role_assignments
WHERE assigning_role = p_to_send_role
AND NVL (end_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
AND role_orig_system = 'WF_LOCAL_ROLES'
AND user_name NOT IN (v_user_exists);
BEGIN
-------Check role Exists----------
SELECT COUNT (NAME)
INTO l_role_cnt
FROM wf_roles
WHERE NAME = p_to_send_role AND status = 'ACTIVE';
------role not Exists , create role-------
IF l_role_cnt = 0
THEN
l_rtn_msg := 'Start Calling createadhocrole Program.';
p_rtn_status := 'S';
wf_directory.createadhocrole (p_to_send_role,
p_to_send_role,
NULL,
NULL,
'Role for ' || p_to_send_role,
'MAILHTML',
p_to_send_user_name,
-- 'NAME1 NAME2', --USER NAME SHOULD BE IN UPPER CASE
NULL,
NULL,
'ACTIVE',
NULL
);
ELSE
---########################Add user to adhoc Role#############---------
BEGIN
l_rtn_msg := 'Start Adding User to Role';
FOR i IN 1 .. l_user_name_tbl.COUNT
LOOP
l_delete_user_name :=
l_delete_user_name || ',''' || l_user_name_tbl (i)
|| '''';
/*DBMS_OUTPUT.put_line ( 'l_delete_user_name:'
|| l_delete_user_name
);*/
-------------check User Assignment for the role from role assignments table---------------
BEGIN
l_rtn_msg :=
'check User Assignment for the role :'
|| p_to_send_role
|| ' from role assignments table for User:'
|| l_user_name_tbl (i);
p_rtn_status := 'S';
SELECT DISTINCT user_name
INTO l_user_name
FROM wf_user_role_assignments
WHERE assigning_role = p_to_send_role
AND NVL (end_date, TRUNC (SYSDATE)) >=
TRUNC (SYSDATE)
AND role_orig_system = 'WF_LOCAL_ROLES'
AND user_name = l_user_name_tbl (i);
DBMS_OUTPUT.put_line
( ' after check User Assignment for the role :'
|| p_to_send_role
|| ' from role assignments table for User:'
|| l_user_name_tbl (i)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_rtn_msg :=
'user:'
|| l_user_name_tbl (i)
|| ' not found in role then add user to role.calling adduserstoadhocrole ';
p_rtn_status := 'S';
DBMS_OUTPUT.put_line (l_rtn_msg);
----if user not found in role then add user to role--------
BEGIN
wf_directory.adduserstoadhocrole (p_to_send_role,
l_user_name_tbl (i)
);
l_rtn_msg :=
'user:' || l_user_name_tbl (i)
|| ' Added to role';
--dbms_output.put_line('User Added to Role');
EXCEPTION
WHEN OTHERS
THEN
l_rtn_msg :=
'Error Calling wf_directory.adduserstoadhocrole. user:'
|| l_user_name_tbl (i)
|| ','
|| SQLERRM;
DBMS_OUTPUT.put_line (l_rtn_msg);
p_rtn_status := 'E';
END;
END;
END LOOP;
--######Delete User from ADhoc Role if not present in current setup##---
BEGIN
l_rtn_msg :=
'Start Deleting User from Role. User :'
|| l_delete_user_name;
BEGIN
--------Query to Get List of lis of user which are not part of current Role setup------------
l_rtn_msg := 'Start preparing Query';
l_user_del_sql :=
( 'SELECT listagg(user_name,'' '') within group (order by user_name) FROM wf_user_role_assignments WHERE assigning_role = '''
|| p_to_send_role
|| '''
AND NVL(end_date,TRUNC (SYSDATE))>= TRUNC (SYSDATE) AND role_orig_system='''
|| l_role_orig_system
|| ''' AND user_name NOT IN ('
|| SUBSTR (l_delete_user_name, 2, 1000)
|| ')'
);
l_rtn_msg :=
'Start Executing Query to get user which are not in current role setup . Current User: '
|| SUBSTR (l_delete_user_name, 2, 1000);
EXECUTE IMMEDIATE l_user_del_sql
INTO l_lst_delete_user;
IF l_lst_delete_user IS NOT NULL
THEN
l_rtn_msg :=
'Start Calling removeusersfromadhocrole Program for User: '
|| l_lst_delete_user;
wf_directory.removeusersfromadhocrole (p_to_send_role,
l_lst_delete_user
);
END IF;
--dbms_output.put_line( l_user_del_sql ||'---'||l_lst_delete_user);
--dbms_output.put_line('User kkk'||substr(l_delete_user_name,2,1000));
l_rtn_msg :=
'
User:'
|| l_lst_delete_user
|| ' Deleted Successfully from Role:'
|| p_to_send_role
|| '. User Exists in Role: '
|| '('
|| SUBSTR (l_delete_user_name, 2, 1000)
|| ')';
p_rtn_status := 'S';
DBMS_OUTPUT.put_line (l_rtn_msg);
EXCEPTION
WHEN OTHERS
THEN
l_rtn_msg :=
'Error Calling wf_directory.adduserstoadhocrole. user:'
|| l_delete_user_name
|| ','
|| SQLERRM;
p_rtn_status := 'E';
END;
END;
--######end Delete User from ADhoc Role if not present in current setup##---
END; --###### End Add/Delete User block###---
END IF;
p_rtn_msg := l_rtn_msg;
EXCEPTION
WHEN OTHERS
THEN
p_rtn_status := 'E';
p_rtn_msg := p_rtn_msg || ',' || SQLERRM;
END create_send_adhoc_role_prc;
