--
--  This procedure will list Groups and Roles required to be added to UserID 2 to match UserID 1.
--

SET DEFINE ON
SET SERVEROUTPUT ON

accept SourceUserID   prompt 'Enter UserID for the comparison user: '
accept TargetUserID   prompt 'Enter UserID for the user you want to compare: '

declare
  l_source_ein      varchar(20);
  l_destination_ein varchar(20);


  CURSOR c_group_membership IS
  select   mugm.group_id, mug.group_name, mugm.module_name
    from mv_user_profile up,
      mv_user_group_member mugm,
      mv_user_group mug
    where mugm.group_id = mug.group_id
    and up.user_id = mugm.USER_ID
    and nvl(up.enable_timestamp, sysdate-1) < sysdate
    and nvl(up.disable_timestamp, sysdate+1) > sysdate
    and up.user_id= l_source_ein
    and mug.group_id not in
	  (select ugm2.group_id 
          from mv_user_profile up2, mv_user_group_member ugm2
          where up2.user_id = l_destination_ein
          and nvl(up2.enable_timestamp, sysdate-1) < sysdate
	  and nvl(up2.disable_timestamp, sysdate+1) > sysdate
          and ugm2.user_id = up2.user_id);  


  CURSOR c_role_grant IS
    select  user_id, role_id, effective_as_of_timestamp, effective_until_timestamp, 
      enabled_yn, user_controllable_yn, module_name
    from sf_user_role_grant
    where user_id = l_source_ein
    and enabled_yn = 'Y'
    and role_id not in
	   (select rg.role_id
	   from sf_user_role_grant rg
           where rg.user_id = l_destination_ein and rg.enabled_yn = 'Y'
    	   and nvl(rg.effective_until_timestamp,sysdate+1) > sysdate
	   and nvl(rg.effective_as_of_timestamp,sysdate-1) < sysdate);


BEGIN
    l_source_ein := upper('&SourceUserID');
    l_destination_ein := upper('&TargetUserID');
    
	 dbms_output.put_line('	');
	 dbms_output.put_line('	');
	 dbms_output.put_line('****************************');
	 dbms_output.put_line('* Group additions required *');
	 dbms_output.put_line('****************************');

    FOR r_add_group_membership IN c_group_membership LOOP
    
	 dbms_output.put_line('Add UserID ' || l_destination_ein || ' to Group	' || r_add_group_membership.group_id || '	' || r_add_group_membership.group_name );
         
    END LOOP;

	 dbms_output.put_line('	');
	 dbms_output.put_line('	');
	 dbms_output.put_line('***************************');
	 dbms_output.put_line('* Role additions required *');
	 dbms_output.put_line('***************************');

    FOR r_add_role_grant IN c_role_grant LOOP

	 dbms_output.put_line('For UserID ' || l_destination_ein || ' add Role	' || r_add_role_grant.role_id);
         
    END LOOP;

END;
/