-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
--
--	Name 		: ckaccrts.sql
--	Description	: This script creates access rights for the migrated users
--  Notes       : This script has been rewritten to so that it uses the 
--                Access Rights Service 
--                It also resolves subordinate users access Rights 
--                See US Bug 1261025
--
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

DECLARE

  l_orig_acc_string	     VARCHAR2(8);
  l_orig_acc_vers_string VARCHAR2(1);
  l_additional_wa_string VARCHAR2(2);
  rtnval 	             NUMBER;
  l_Migration_wa_irid    NUMBER;
  
BEGIN

  UPDATE ck_migration_objects SET ci_created = 'N' WHERE ci_stage = 'CKACCRTS';

  DBMS_OUTPUT.ENABLE(1000000);

  DBMS_OUTPUT.PUT_LINE('* Creating Access Rights *');

  -- GET THE CURRENT WA_IRID
  l_Migration_wa_irid := jr_context.workarea;
  
  -- if versioning is enabled 
  IF (jr_sys_privs.is_versioned_repos ) THEN 

	  -- Grant Access to the current WA to all subordinate users
	  FOR subordinate_users IN ( SELECT distinct ar.ar_usr_granted_to
				  				 FROM   upg_sdw_access_rights ar
				  				 WHERE  ar.ar_usr_granted_to != USER ) 
	  LOOP
	       -- DBMS_OUTPUT.PUT_LINE('* subordinate_user - '||subordinate_users.ar_usr_granted_to||' *');
	          rtnval := jr_acc_rights.ins( l_Migration_wa_irid, 
	  		                               'WA', 
	  		  			                   jr_acc_rights.STR_TO_AR('YNYYYYNN'), -- 1-DEL, 2-ADMIN, 3-INS, 4-SEL, 5-UPD, 6-VER, 7 COMPWA (N/A), 8 UPD SPEC (N/A)
	  					                   subordinate_users.ar_usr_granted_to 
										 );
	  END LOOP;
 
  END IF; -- if versioning is enabled 

  -- Get all the folders that have been migrated
  -- Make sure the Root folders are returned First
  FOR fol_rec IN (  SELECT a.irid, 
                           a.owning_app,
		  	   			   a.old_id,
			   			   f.root_flag,
			   			   f.owning_user
		     		FROM   jr_id_maps    a,
	  	          		   i$sdd_folders f
	            	WHERE  a.element_type = 'APP'
	              	AND    a.pop_tab      = 'SDD_ELEMENTS'
  		      		AND    f.irid         = a.irid
	         		ORDER BY decode(f.root_flag,'Y',1 , 2) )
  LOOP    
  
     -- There are no access rights at the moments so create the current user as the owner
     -- if current user has access in 2.1.2 then use those rights 
     -- otherwise grant SEL access
	 
	 l_additional_wa_string := NULL;
	 
	 BEGIN
		 SELECT ar.ar_del_allw            ||      -- DELETE
		        nvl(ar.ar_admin_allw,'N') ||      -- ADMIN (was not mandatory in 6.0 hence nvl used)
		        ar.ar_ins_allw            ||      -- INSERT
		        ar.ar_sel_allw            ||      -- SELECT
				ar.ar_upd_allw                	  -- UPDATE
				,ar.ar_upd_allw		  	  		  -- VERSIONING (on to be used if versioning is enabled)
		  INTO  l_orig_acc_string
		 		,l_orig_acc_vers_string
		  FROM 	upg_sdw_access_rights ar
		 WHERE 	get_irid(ar.ar_appid_for,0) = fol_rec.irid
		   AND 	ar.ar_usr_granted_to = USER; 

	 EXCEPTION
	     WHEN no_data_found THEN
		    l_orig_acc_string      := 'NNNYN';
		    l_orig_acc_vers_string := 'N';
	     WHEN OTHERS THEN 
		    DBMS_OUTPUT.PUT_LINE (' ** Exception Raised ** ');
            DBMS_OUTPUT.PUT_LINE ('...ERROR: '||substrb(sqlerrm, 1, 200));
		    RAISE;
	 END;

	 -- If versioning is not enabled then set the VERSIONING part to 'N'
     IF (jr_sys_privs.is_versioned_repos = FALSE ) THEN 
        l_orig_acc_vers_string := 'N';  -- VERSIONING
		l_additional_wa_string := 'YY';  -- COMPILE WA||UPDATE_SPEC
     END IF;

	 -- Finally concatenate the Access Rights String.
	 -- NOTE COMPILE WA and UPDATE SPEC are not required for folders so we leave then blank
	 l_orig_acc_string := l_orig_acc_string || l_orig_acc_vers_string;
	 
	 -- We cannot leave any access rights info blank as they will not be reflected in RON due to a RON Bug.
	 l_orig_acc_string := l_orig_acc_string || 'NN';
	 

     -- Grant the Access Rights for the current user
  	 -- DBMS_OUTPUT.PUT_LINE ('* Granting the Access Rights for the current user - '|| to_char(l_Migration_wa_irid)||' - '||l_orig_acc_string||' *');
     -- Bug 2375174 set mig_call flag to prevent hierarchical access rights creation
     rtnval := jr_acc_rights.ins(fol_rec.irid, 
	                             'CEL', 
								 jr_acc_rights.STR_TO_AR(l_orig_acc_string), 
								 USER, 'Y');
	 
		
	 -- Now Migrate access Rights for all other users that exist in both 2.1.2 and 6.5
	 -- Note we don't want to repeat the the ones for the current user
     -- DBMS_OUTPUT.PUT_LINE ('* Migrating access Rights for all other users that exist in both 2.1.2 and 6.5 - '||to_char(fol_rec.irid)||' *');
	 FOR Other_Users_Access IN (
		 SELECT ar.ar_usr_granted_to,
		        ar.ar_del_allw            ||      -- DELETE
		        nvl(ar.ar_admin_allw,'N') ||      -- ADMIN (was not mandatory in 6.0 hence nvl used)
		        ar.ar_ins_allw            ||      -- INSERT
		        ar.ar_sel_allw            ||      -- SELECT
			    ar.ar_upd_allw    l_orig_acc_string               -- UPDATE
				,ar.ar_upd_allw	  l_orig_acc_vers_string		  -- VERSIONING (on to be used if versioning is enabled)
		 FROM  upg_sdw_access_rights ar
		 WHERE get_irid(ar.ar_appid_for,0) = fol_rec.irid
		 AND   ar.ar_usr_granted_to != USER )

	 LOOP
	   
	    l_additional_wa_string := NULL;
	 
	    -- If versioning is not enabled then set the VERSIONING part to 'N'
        -- DBMS_OUTPUT.PUT_LINE ('* Is this a Versioned Repository *');
	    IF (jr_sys_privs.is_versioned_repos = FALSE ) THEN 
		   Other_Users_Access.l_orig_acc_vers_string := 'N';  -- VERSIONING
 	    END IF;

	    -- Finally concatenate the Access Rights String.
	    -- NOTE COMPILE WA and UPDATE SPEC are not required for folders so we leave then blank
	    Other_Users_Access.l_orig_acc_string := Other_Users_Access.l_orig_acc_string || Other_Users_Access.l_orig_acc_vers_string;

	 	-- We cannot leave any access rights info blank as they will not be reflected in RON due to a RON Bug.
	    Other_Users_Access.l_orig_acc_string := Other_Users_Access.l_orig_acc_string || 'NN';
		 
		 
	    -- Grant the Access Rights for the current user
        -- DBMS_OUTPUT.PUT_LINE ('* Granting the Access Rights for the current user - '||Other_Users_Access.ar_usr_granted_to||' *');
        -- Bug 2375174 set mig_call flag to prevent hierarchical access rights creation
        rtnval := jr_acc_rights.ins( fol_rec.irid, 
	                               	 'CEL', 
		  		         		  	 jr_acc_rights.STR_TO_AR(Other_Users_Access.l_orig_acc_string), 
					 				 Other_Users_Access.ar_usr_granted_to , 'Y'
								    );
						
	 END LOOP;
	 
	 -- Now we need to table care of the ownership issue
	 -- If the owning_user is the current user then do nothing
	 
	 -- if the owning user is not the current user...
     -- DBMS_OUTPUT.PUT_LINE ('* Owning user '||fol_rec.owning_user||' not current user *');
	 
	 IF (fol_rec.owning_user != USER) THEN
	    -- if the owning user exists in 6.5 
	
	    BEGIN
		
		  SELECT 1
		  INTO   rtnval
		  FROM   sdw_users 
		  WHERE  username = fol_rec.owning_user;
		  
          -- DBMS_OUTPUT.PUT_LINE ('* Before Change governor '||to_char(fol_rec.irid)||' - '|| fol_rec.owning_user||' *');
		  jr_acc_rights.change_governor(fol_rec.irid, fol_rec.owning_user);
          -- DBMS_OUTPUT.PUT_LINE ('* After Change governor '||to_char(fol_rec.irid)||' - '|| fol_rec.owning_user||' *');
		  
		EXCEPTION
		  WHEN no_data_found THEN
		     jr_context.disable(jr_context.access_rights);
		     UPDATE sdd_folders 
		     SET    owning_user = USER
		     WHERE  irid = fol_rec.irid;
		     jr_context.enable(jr_context.access_rights);
			 
			 -- 1300087 - MIGRATION SHOULD GRANT ADMINISTRATE RIGHTS ALSO IF MIGRATING USER BECOME OWNER			 
			 -- jr_acc_rights.grant_priv(fol_rec.irid, USER, 'ADM' );
			 
			 -- 1323102 - MIGRATION FAILS TO GRANT FULL CONTROL ON APPLICATION SYSTEMS
			 jr_acc_rights.upd(fol_rec.irid, USER, jr_acc_rights.full_access_rights ); 
			 
		  WHEN others THEN
		     DBMS_OUTPUT.PUT_LINE (' ** Exception Raised ** ');
		     DBMS_OUTPUT.PUT_LINE ('...ERROR: '||substrb(sqlerrm, 1, 200));
		     RAISE;
	    END;
	 END IF;
  END LOOP;

  UPDATE ck_migration_objects SET ci_created = 'Y' WHERE ci_stage = 'CKACCRTS';
  
  COMMIT;
  
  DBMS_OUTPUT.PUT_LINE('* Created Access Rights *');

EXCEPTION
   WHEN others THEN
      DBMS_OUTPUT.PUT_LINE (' ** Exception Raised ** ');
      DBMS_OUTPUT.PUT_LINE ('...ERROR: '||substrb(sqlerrm, 1, 200));
      RAISE;

END; 
/
