Only Four Left…

/*
   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
/

/*
   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
/

/*
  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
/