Assign WorkList Access to EBS User

Script uses API to assign WF User Roles for Worklist access


set serveroutput on
 declare
 l_userTable WF_DIRECTORY.UserTable;
 l_roleName varchar2(240):='<EBS USERNAME>';
 l_roleDispName varchar2(240):='<EBS USERNAME>';
 l_userName varchar2(240):='<EBS USERNAME>';
 l_userDispName varchar2(240):='<EBS USERNAME>';
 begin
-- wf_directory.CreateAdHocUser(name=>l_userName,
-- display_name=>l_userDispName,language=>'AMERICAN', territory=>'UNITED KINGDOM');
--dbms_output.put_line('After User '|| l_userName);
 l_userTable(0) := l_userName;
 wf_directory.CreateAdHocRole2(role_name=>l_roleName,
 role_display_name=>l_roleDispName,
 role_users=>l_userTable,language=>'AMERICAN', territory=>'UNITED KINGDOM');
 dbms_output.put_line('After Role '|| l_roleName);
 commit;
 end;


Now run the following sqls to verify the wfds tables


select * from wf_local_roles where name='<EBS USERNAME>';

select * from wf_user_roles where role_name='<EBS USERNAME>';

select * from wf_local_user_roles where role_name='<EBS USERNAME>';


If you want to delete the inserted records,


 --delete from wf_local_roles where name='<EBS USERNAME>' and notification_preference = 'QUERY';

  --delete from wf_user_roles where role_name='<EBS USERNAME>';

-- delete from wf_local_user_roles where role_name='<EBS USERNAME>';


If want to remove the username from and Adhoc Role


exec wf_directory.RemoveUserRole('<EBS USERNAME>', '<EBS USERNAME>','WF_LOCAL_USERS',0, 'WF_LOCAL_ROLES', 0);


This will set an end date to the assignment but will not remove the record; it will be purged by the purge process.


exec wf_directory.DeleteUserRole('<EBS USERNAME>', '<EBS USERNAME>','WF_LOCAL_USERS',0, 'WF_LOCAL_ROLES', 0);