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