Created
August 25, 2011 22:07
-
-
Save jpiwowar-zz/1172122 to your computer and use it in GitHub Desktop.
EBS FND_USER_PKG snippets, referenced in blog post http://only4left.jpiwowar.com/2011/08/fun-with-ebs-api-ii/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 | |
/ | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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 | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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