Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
EBS FND_USER_PKG snippets, referenced in blog post http://only4left.jpiwowar.com/2011/08/fun-with-ebs-api-ii/
/*
ebs_bulkresp.sql
Contact: John Piwowar
Purpose: Add 1+ responsibilities to 1+ users
Requires: desired action (validate or apply) as script argument
Table xxrespload, of the format:
Name Type
----------------------------- --------------------
USER_NAME VARCHAR2(100)
APP_SHORTNAME VARCHAR2(50)
RESP_NAME VARCHAR2(100)
END_DATE DATE
Notes: Actually, validation happens no matter what, but it never hurts to be explicit
*/
SET serveroutput on size 1000000 format word_wrapped
SET verify off feedback off
col user_name for a20
col app for a10
col end_date for a15
col resp_name for a35
PROMPT Working with following records in table xxrespload:
SELECT user_name
, app_shortname app
, resp_name
, nvl(to_char(end_date,'DD-MON-YYYY'),'NONE') end_date
FROM xxrespload
ORDER BY user_name
, resp_name
/
DECLARE
action VARCHAR2(8) := upper('&1');
TYPE RespPair IS RECORD ( app APPLSYS.FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE
, resp APPLSYS.FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME %TYPE
);
TYPE UserList IS TABLE OF APPLSYS.FND_USER.USER_NAME%TYPE;
TYPE RespList IS TABLE OF RespPair;
BadUsers UserList;
BadResps RespList;
--N.B. Annoying security_group_id kludge. NULL=STANDARD, woohoo!
CURSOR cGetBulkResps IS
SELECT x.user_name
, x.app_shortname
, x.resp_name
, r.responsibility_key resp_key
, s.security_group_key secgrp
, x.end_date
FROM xxrespload x
JOIN applsys.fnd_application a on (a.application_short_name=x.app_shortname)
JOIN applsys.fnd_responsibility_tl t on ( a.application_id=t.application_id
and x.resp_name=t.responsibility_name)
JOIN applsys.fnd_responsibility r on ( t.application_id=r.application_id
and t.responsibility_id=r.responsibility_id)
JOIN applsys.fnd_security_groups s on (nvl(r.security_group_id,0)=s.security_group_id)
WHERE x.user_name is not null;
BEGIN
dbms_output.put_line('Starting validation...');
--Validate supplied users
SELECT DISTINCT user_name
BULK COLLECT INTO BadUsers
FROM xxrespload x
WHERE NOT EXISTS (SELECT user_name
FROM fnd_user u
WHERE u.user_name = x.user_name
AND (u.end_date is null OR u.end_date > SYSDATE)
);
--Validate app-resp pairs
SELECT DISTINCT app_shortname
, resp_name
BULK COLLECT INTO BadResps
FROM xxrespload x
WHERE NOT EXISTS ( SELECT a.application_short_name
, r.responsibility_name
FROM applsys.fnd_responsibility_tl r
JOIN applsys.fnd_application a using (application_id)
WHERE a.application_short_name = x.app_shortname
AND r.responsibility_name = x.resp_name
);
IF BadUsers.COUNT > 0 THEN
dbms_output.put_line('Found ' || BadUsers.COUNT || ' invalid user names.');
FOR i IN BadUsers.FIRST .. BadUsers.LAST
LOOP
dbms_output.put_line('User ' || nvl(BadUsers(i),'<NULL>') ||
' does not exist or is end-dated.'
);
END LOOP; --BadUsers;
ELSE
dbms_output.put_line('All users valid.');
END IF; --BadUsers.COUNT
IF BadResps.COUNT > 0 THEN
dbms_output.put_line('Found ' || BadResps.COUNT || ' invalid app-responsibility pairs.');
FOR i IN BadResps.FIRST .. BadResps.LAST
LOOP
dbms_output.put_line('Responsibility ' || nvl(BadResps(i).resp,'<NULL>') ||
' invalid for app ' ||
nvl(BadResps(i).app,'<NULL>') ||
', or vice versa.'
);
END LOOP; --BadResps
ELSE
dbms_output.put_line('All app-responsibility pairs valid');
END IF; --BadResps.COUNT
--Okay, now let's actually do stuff
IF action = 'APPLY' THEN
dbms_output.put_line('Adding responsibilities...');
FOR new_resp IN cGetBulkResps
LOOP
dbms_output.put_line('User: ' || new_resp.user_name ||
' App: ' || new_resp.app_shortname ||
' Resp: ' || new_resp.resp_name
);
apps.fnd_user_pkg.AddResp( username => new_resp.user_name
, resp_app => new_resp.app_shortname
, resp_key => new_resp.resp_key
, security_group => new_resp.secgrp
, description => 'Added by ebs_bulkresps.sql'
, start_date => SYSDATE
, end_date => new_resp.end_date
);
END LOOP; --cGetBulkResps
ELSE
dbms_output.put_line('Invoke script with APPLY to add responsibilities');
END IF; --action='APPLY';
END; --ebs_bulkresp
/
/*
ebs_dupresp.sql
Contact: John Piwowar
Purpose: Grant one user's responsbilities to another, optionally
creating the target user
Notes: ...
*/
SET serveroutput on size 1000000 format word_wrapped
SET verify off feedback off
--Readability improvements. First time for everything.
DEFINE src=&1
DEFINE targ=&2
PROMPT Source user: &&src
PROMPT Target user: &&targ
PROMPT CTRL-C might be a good idea if these are incorrect/reversed.
ACCEPT create_user_pref prompt 'Create &&targ if non-existent? (y/N) '
DECLARE
src_user APPLSYS.FND_USER.USER_NAME%TYPE := upper('&&src');
targ_user APPLSYS.FND_USER.USER_NAME%TYPE := upper('&&targ');
create_flag BOOLEAN := FALSE;
targ_userid APPLSYS.FND_USER.USER_ID%TYPE := NULL;
--Cursor to pull source user's responsbilities
--Note: does not pull end-dated responsibilities, since they were
-- presumably end-dated for a reason. Also retains forward-looking
-- end dates
CURSOR cGetResps (user_in APPLSYS.FND_USER.USER_NAME%TYPE)
IS
SELECT a.application_short_name app
, r.responsibility_key key
, t.responsibility_name respname
, s.security_group_key secgrp
, g.end_date
FROM apps.fnd_user_resp_groups_direct g
, applsys.fnd_responsibility r
, applsys.fnd_user u
, applsys.fnd_application a
, applsys.fnd_security_groups s
, applsys.fnd_responsibility_tl t
WHERE g.responsibility_id = r.responsibility_id
AND g.security_group_id = s.security_group_id
AND g.responsibility_application_id = r.application_id
AND g.responsibility_application_id=t.application_id
AND g.responsibility_id=t.responsibility_id
AND r.application_id = a.application_id
AND u.user_name = user_in
AND g.user_id = u.user_id
AND (g.end_date > SYSDATE or g.end_date is null);
BEGIN
IF upper(substr('&&create_user_pref',1,1)) = 'Y' THEN
create_flag := TRUE;
END IF; --create_user_pref=Y
--Create user if needed
IF create_flag AND NOT (apps.fnd_user_pkg.userExists(targ_user))
THEN
targ_userid := apps.fnd_user_pkg.createuserid (
x_user_name => targ_user
, x_owner => 'CUST'
, x_unencrypted_password => 'chang3m3'
, x_description => 'Autocreated by ebs_dupresps.sql'
);
--Probably won't ever get to this point, but just to be safe...
IF targ_userid IS NULL THEN
dbms_output.put_line('Failed to create ' || targ_user ||
', cannot continue'
);
RETURN;
ELSE
dbms_output.put_line('User ' || targ_user || ' created with id ' ||
targ_userid);
END IF; --targ_userid
ELSIF create_flag AND (apps.fnd_user_pkg.userExists(targ_user))
THEN
dbms_output.put_line('User ' || targ_user || ' already exists.');
ELSIF NOT create_flag AND NOT (apps.fnd_user_pkg.userExists(targ_user))
THEN
dbms_output.put_line('User ' || targ_user ||
' does not exist and create flag not specified.');
RETURN;
END IF; --create_flag
--Add responsibilities
FOR new_resp IN cGetResps(src_user)
LOOP
dbms_output.put_line('Adding responsibility ' || new_resp.respname);
apps.fnd_user_pkg.AddResp( username => targ_user
, resp_app => new_resp.app
, resp_key => new_resp.key
, security_group => new_resp.secgrp
, description => 'Added by ebs_dupresps.sql'
, start_date => SYSDATE
, end_date => new_resp.end_date
);
END LOOP; --new_resp
END; --ebs_dupresps
/
--Create external table xxrespload to go with ebs_bulkresps.sql
--Adjust directory name and filenames as preferred
drop table xxrespload
/
create table xxrespload
( user_name varchar2(100) --fnd_user.user_name
, app_shortname varchar2(50) --fnd_applcation.application_short_name
, resp_name varchar2(100) --fnd_responsibility_tl.responsibility_name
, end_date date
)
organization external
( type oracle_loader
default directory scratch
access parameters
( records delimited by newline
badfile scratch:'xxrespload.bad'
logfile scratch:'xxrespload.log'
fields terminated by '|'
missing field values are null
( user_name
, app_shortname
, resp_name
, end_date char date_format date mask "DD-MON-YYYY"
)
)
location ('xxrespload.txt')
)
reject limit unlimited
/
/*
iresp.sql
Contact: John Piwowar
Purpose: List useful identifier for EBS responsibilities
Notes: Provide fragment of responsbility name
*/
col responsibility_name for a50
col app_name for a10
col app_id for 999999
col resp_id for 999999
set pagesize 9999 verify off
select application_id app_id
, a.application_short_name app_name
, r.responsibility_id resp_id
, r.responsibility_name
from applsys.fnd_responsibility_tl r
join applsys.fnd_application a using (application_id)
where upper(r.responsibility_name) like upper('%&1%')
order by responsibility_name
/
/*
lresp.sql
Contact: John Piwowar
Purpose: List EBS user's active responsibilities
Requires: Valid EBS user name as script argument
Notes: Only displays responsbilities with null or future-dated end-dates
*/
set verify off pagesize 9999
col app for a20
col respname for a40
col end_date for a15
Prompt Currently active responsibilities for user &&1
select a.application_short_name app
, t.responsibility_name respname
, NVL(to_char(r.end_date,'DD-MON-YYYY'),'NONE') end_date
from apps.fnd_user_resp_groups_all r
join applsys.fnd_user u using (user_id)
join applsys.fnd_responsibility_tl t
on ( r.responsibility_application_id=t.application_id
and r.responsibility_id=t.responsibility_id
)
join fnd_application a using (application_id)
where u.user_name = upper('&&1')
and (r.end_date > SYSDATE or r.end_date is null)
order by respname asc
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment