--restrict_proi_usage.sql
--Step 1.  Create table restrict_proi. Doesn't matter if it exists.
--Step 2. Insert default unrestricted status into restrict_proi
--Step 3. Check status value in restrict_proi. If 'restricted', return.
--Step 4. Create copies of pdm_user, pdm_role, pdm_roleaction, pdm_userfolauth, pdm_userfolrlauth, pdm_ddauthdef, pdm_userpdmauth tables.
--Step 5. Create temporary role 'ProI_To_WC_Migration' for existing super-users in Pro/I
--Step 6. Add entries to pdm_roleaction for 'ProI_To_WC_Migration' role (restrictive previlages)
--Step 7. Get all administrators from pdm_users and assign them to 'ProI_To_WC_Migration' role for 'Root Folder'
--Step 8. Disable all super-users in Pro/I in pdm_user table.
--Step 9. Delete all restricting role actions from pdm_roleaction table (Pro/I limited usage)
--Step 10. Modify table pdm_ddauthdef - change LCOSetAttribute, LCOUnSetAttribute's action from "Modify Object Attribute" To
--     	  "Modify Non-Versioned Attribute"
--Step 11. Set status in restrict_proi as 'restricted'.
--Step 12. Commit Transaction.


SET serveroutput on;
--Step 1.  Create table restrict_proi. Suppress error if it exists.

DECLARE
ERR_MSG VARCHAR2(100);
ERR_CDE NUMBER;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE restrict_proi  ( ID NUMBER NOT NULL,
                                           status NUMBER DEFAULT 0, --0 = un-restrict, 1 = restrict
                                 	   status_string VARCHAR2(2000) NOT NULL,
                                           CONSTRAINT pk_restrict_proi PRIMARY KEY (ID))';
EXCEPTION
   WHEN OTHERS
   THEN
   -- If table exists, suppress SQL Error
   -- ORA-00955: name is already used by an existing object
   ERR_MSG := SUBSTR(SQLERRM,1,100);
   ERR_CDE := SQLCODE;
   IF ERR_CDE != -00955
   THEN
     DBMS_OUTPUT.put_line ('SQL error occured while creating table restrict_proi. Error message is -');
     DBMS_OUTPUT.put_line (ERR_MSG);
     DBMS_OUTPUT.put_line ('');
   END IF;

END;
/

DECLARE
   ERR_MSG VARCHAR2(100);
   v_status        restrict_proi.status%TYPE;

   CURSOR status_cursor
   IS
      SELECT status
        FROM restrict_proi
       WHERE ID = 1;

   CURSOR roleaction_copy_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_ROLEACTION_COPY';

   CURSOR role_copy_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_ROLE_COPY';

   CURSOR user_copy_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_USER_COPY';

   CURSOR userfolauth_copy_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_USERFOLAUTH_COPY';

   CURSOR userfolrlauth_copy_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_USERFOLRLAUTH_COPY';

   CURSOR ddauthdef_copy_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_DDAUTHDEF_COPY';

   CURSOR userpdmauth_copy_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_USERPDMAUTH_COPY';

   v_table_name    user_tables.table_name%TYPE;
   v_roleid        pdm_role.roleid%TYPE;
   v_actnid        pdm_action.actnid%TYPE;
   v_actionident   pdm_action.actnident%TYPE;
   v_roleid1       pdm_roleaction.roleid%TYPE;
   v_userid        pdm_user.userid%TYPE;
   v_folderid      pdm_folder.folid%TYPE;
   v_ufaid         pdm_userfolauth.ufaid%TYPE;
   v_raid          pdm_roleaction.raid%TYPE;
   v_actnid1       pdm_roleaction.actnid%TYPE;
   v_ufraid        pdm_userfolrlauth.ufraid%TYPE;
   v_rlid          pdm_releaselevel.rlid%TYPE;

   CURSOR role_cursor
   IS
      SELECT roleid
        FROM pdm_role
       WHERE rolename = 'ProI_To_WC_Migration';

   CURSOR actionid_cursor
   IS
      SELECT DISTINCT actnid, actnident
                 FROM pdm_action, pdm_action_dat
                WHERE pdm_action.actnident = pdm_action_dat.ident;

   CURSOR roleaction_cursor
   IS
      SELECT roleid
        FROM pdm_roleaction
       WHERE roleid = (SELECT roleid
                         FROM pdm_role
                        WHERE rolename = 'ProI_To_WC_Migration');

   CURSOR userid_cursor
   IS
      SELECT userid
        FROM pdm_user
       WHERE usertype = 1;

   CURSOR userfolauth_cursor
   IS
      SELECT ufaid
        FROM pdm_userfolauth
       WHERE roleid = (SELECT roleid
                         FROM pdm_role
                        WHERE rolename = 'ProI_To_WC_Migration');

   CURSOR userfolrlauth_cursor
   IS
      SELECT ufraid
        FROM pdm_userfolrlauth
       WHERE roleid = (SELECT roleid
                         FROM pdm_role
                        WHERE rolename = 'ProI_To_WC_Migration');

   CURSOR rl_cursor
   IS
      SELECT rlid
        FROM pdm_releaselevel;

   CURSOR roleaction_cursor1
   IS
      SELECT tab1.raid, tab1.roleid, tab1.actnid
        FROM pdm_roleaction tab1, pdm_action tab2
       WHERE tab1.actnid = tab2.actnid
         AND tab2.actnident IN
                 (1100, 1200, 1500, 1700, 1900, 2000, 2100, 2200, 2300, 3100, 3500);

   CURSOR folder_cursor
   IS
   		SELECT folid from pdm_folder;

BEGIN
--Step 2. Insert default unrestricted status into restrict_proi
--Step 3. Check status value in restrict_proi. If 'restricted', return.
   OPEN status_cursor;

   FETCH status_cursor
    INTO v_status;

   IF status_cursor%NOTFOUND
   THEN                  --Table was just created, Insert unrestricted status
      INSERT INTO restrict_proi
                  (ID, status, status_string
                  )
           VALUES (1, 0, 'unrestricted'
                  );
   ELSE
      IF status_cursor%FOUND
      THEN
         IF v_status = 1                                 --already restricted
         THEN
            CLOSE status_cursor;

            DBMS_OUTPUT.put_line ('Current Pro/I usage is restricted');
            RETURN;
         END IF;
      END IF;
   END IF;

   CLOSE status_cursor;

   --Step 4. Create copies of pdm_user, pdm_role, pdm_roleaction, pdm_userfolauth, pdm_userfolrlauth, pdm_ddauthdef and pdm_userpdmauth tables.

   OPEN user_copy_cur;

   FETCH user_copy_cur
    INTO v_table_name;

   IF user_copy_cur%FOUND
   THEN
      EXECUTE IMMEDIATE 'DROP TABLE pdm_user_copy';
   END IF;

   CLOSE user_copy_cur;

   EXECUTE IMMEDIATE 'CREATE TABLE pdm_user_copy AS SELECT * FROM pdm_user';

   OPEN roleaction_copy_cur;

   FETCH roleaction_copy_cur
    INTO v_table_name;

   IF roleaction_copy_cur%FOUND
   THEN
      EXECUTE IMMEDIATE 'DROP TABLE pdm_roleaction_copy';
   END IF;

   CLOSE roleaction_copy_cur;

   EXECUTE IMMEDIATE 'CREATE TABLE pdm_roleaction_copy AS SELECT * FROM pdm_roleaction';

   OPEN role_copy_cur;

   FETCH role_copy_cur
    INTO v_table_name;

   IF role_copy_cur%FOUND
   THEN
      EXECUTE IMMEDIATE 'DROP TABLE pdm_role_copy';
   END IF;

   CLOSE role_copy_cur;

   EXECUTE IMMEDIATE 'CREATE TABLE pdm_role_copy AS SELECT * FROM pdm_role';

   OPEN userfolauth_copy_cur;

   FETCH userfolauth_copy_cur
    INTO v_table_name;

   IF userfolauth_copy_cur%FOUND
   THEN
      EXECUTE IMMEDIATE 'DROP TABLE pdm_userfolauth_copy';
   END IF;

   CLOSE userfolauth_copy_cur;

   EXECUTE IMMEDIATE 'CREATE TABLE pdm_userfolauth_copy AS SELECT * FROM pdm_userfolauth';

   OPEN userfolrlauth_copy_cur;

   FETCH userfolrlauth_copy_cur
    INTO v_table_name;

   IF userfolrlauth_copy_cur%FOUND
   THEN
      EXECUTE IMMEDIATE 'DROP TABLE pdm_userfolrlauth_copy';
   END IF;

   CLOSE userfolrlauth_copy_cur;

   EXECUTE IMMEDIATE 'CREATE TABLE pdm_userfolrlauth_copy AS SELECT * FROM pdm_userfolrlauth';

   OPEN ddauthdef_copy_cur;

   FETCH ddauthdef_copy_cur
    INTO v_table_name;

   IF ddauthdef_copy_cur%FOUND
   THEN
      EXECUTE IMMEDIATE 'DROP TABLE pdm_ddauthdef_copy';
   END IF;

   CLOSE ddauthdef_copy_cur;

   EXECUTE IMMEDIATE 'CREATE TABLE pdm_ddauthdef_copy AS SELECT * FROM pdm_ddauthdef';

   OPEN userpdmauth_copy_cur;

   FETCH userpdmauth_copy_cur
    INTO v_table_name;

   IF userpdmauth_copy_cur%FOUND
    THEN
      EXECUTE IMMEDIATE 'DROP TABLE pdm_userpdmauth_copy';
    END IF;

   CLOSE userpdmauth_copy_cur;

   EXECUTE IMMEDIATE 'CREATE TABLE pdm_userpdmauth_copy AS SELECT * FROM pdm_userpdmauth';

   DBMS_OUTPUT.put_line ('Current Pro/I usage is unrestricted');
   DBMS_OUTPUT.put_line ('Restricting Pro/I ...');

--Step 5. Create temporary role 'ProI_To_WC_Migration' for existing super-users in Pro/I
   OPEN role_cursor;

   FETCH role_cursor
    INTO v_roleid;

   IF role_cursor%NOTFOUND
   THEN
      INSERT INTO pdm_role
                  (roleid, rolename, authoritytype, createdby,
                   createdon, modifiedby, modifiedon
                  )
           VALUES (pdm_role_seq.NEXTVAL, 'ProI_To_WC_Migration', 1,
                                                              --role is hidden
                                                                   'PDM',
                   SYSDATE, 'PDM', SYSDATE
                  );
   END IF;

   CLOSE role_cursor;

--Step 6. Add entries to pdm_roleaction for 'ProI_To_WC_Migration' role (restrictive previlages)
   SELECT roleid
     INTO v_roleid
     FROM pdm_role
    WHERE rolename = 'ProI_To_WC_Migration';

   OPEN roleaction_cursor;

   FETCH roleaction_cursor
    INTO v_roleid1;

   IF roleaction_cursor%NOTFOUND
   THEN
      OPEN actionid_cursor;

      LOOP
         FETCH actionid_cursor
          INTO v_actnid, v_actionident;

         EXIT WHEN actionid_cursor%NOTFOUND;

         IF (   v_actionident = 100
             OR v_actionident = 200
             OR v_actionident = 300
             OR v_actionident = 400
             OR v_actionident = 500
             OR v_actionident = 600
             OR v_actionident = 700
             OR v_actionident = 800
             OR v_actionident = 900
             OR v_actionident = 1000
             OR v_actionident = 1400
             OR v_actionident = 1600
             OR v_actionident = 1800
             OR v_actionident = 2500
             OR v_actionident = 2600
             OR v_actionident = 2700
             OR v_actionident = 2800
             OR v_actionident = 3000
             OR v_actionident = 3200
             OR v_actionident = 3300
             OR v_actionident = 3400
             OR v_actionident = 3600
             OR v_actionident = 3800
             OR v_actionident = 4000
             OR v_actionident = 4100
             OR v_actionident = 4200
            )
         THEN
            INSERT INTO pdm_roleaction
                        (raid, roleid, actnid,
                         rastatus, createdby, createdon, modifiedby,
                         modifiedon
                        )
                 VALUES (pdm_roleaction_seq.NEXTVAL, v_roleid, v_actnid,
                         '1', 'PDM', SYSDATE, 'PDM',
                         SYSDATE
                        );
         END IF;
      END LOOP;

      CLOSE actionid_cursor;
   END IF;

   CLOSE roleaction_cursor;

--Step 7. Get all administrators from pdm_users and assign them to 'ProI_To_WC_Migration' role for 'Root Folder'
-- Add administrators to pdm_userpdmauth and assign them to 'ProI_To_WC_Migration'

   SELECT roleid
     INTO v_roleid
     FROM pdm_role
    WHERE rolename = 'ProI_To_WC_Migration';

   --Add entries to pdm_userfolauth table
   OPEN userfolauth_cursor;

   FETCH userfolauth_cursor
    INTO v_ufaid;

   IF (userfolauth_cursor%NOTFOUND)
   THEN
      OPEN userid_cursor;

      LOOP
         FETCH userid_cursor
          INTO v_userid;

         EXIT WHEN userid_cursor%NOTFOUND;

		 --delete the entry if it exists based on userid + folderid combination.
     	OPEN folder_cursor;
    	LOOP
     		FETCH folder_cursor
     			INTO v_folderid;

            EXIT WHEN folder_cursor%NOTFOUND;
       		DELETE FROM pdm_userfolauth WHERE userid = v_userid AND folid = v_folderid;

        	INSERT INTO pdm_userfolauth
    	                 (ufaid, folid, roleid,
        	              userid, createdby, createdon, modifiedby, modifiedon
            	         )
           			VALUES (pdm_userfolauth_seq.NEXTVAL, v_folderid, v_roleid,
                	      v_userid, 'PDM', SYSDATE, 'PDM', SYSDATE
                    	 );
        END LOOP;
        CLOSE folder_cursor;
      END LOOP;

      CLOSE userid_cursor;
   END IF;

   CLOSE userfolauth_cursor;

   --Add entries to pdm_userfolrlauth table
   OPEN userfolrlauth_cursor;

   FETCH userfolrlauth_cursor
    INTO v_ufraid;

   IF (userfolrlauth_cursor%NOTFOUND)
   THEN
      OPEN userid_cursor;

      LOOP
         FETCH userid_cursor
          INTO v_userid;
--         dbms_output.put_line('v_userid =  '|| v_userid);
         EXIT WHEN userid_cursor%NOTFOUND;

         OPEN rl_cursor;

         LOOP
            FETCH rl_cursor
             INTO v_rlid;
--			dbms_output.put_line( 'v_rlid =  '|| v_rlid);
            EXIT WHEN rl_cursor%NOTFOUND;

            OPEN folder_cursor;
            LOOP
   	     		FETCH folder_cursor
    	 			INTO v_folderid;
--				dbms_output.put_line( 'v_folderid =  '|| v_folderid);
	            EXIT WHEN folder_cursor%NOTFOUND;

		    	--delete the entry if it exists based on userid + folderid + rlid combination.
		    	DELETE FROM pdm_userfolrlauth WHERE userid = v_userid AND folid = v_folderid AND rlid = v_rlid;

            	INSERT INTO pdm_userfolrlauth
                        (ufraid, folid, rlid,
                         userid, roleid, createdby, createdon, modifiedby,
                         modifiedon
                        )
               VALUES (pdm_userfolrlauth_seq.NEXTVAL, v_folderid, v_rlid,
                         v_userid, v_roleid, 'PDM', SYSDATE, 'PDM',
                         SYSDATE
                        );
            END LOOP;
            CLOSE folder_cursor;

         END LOOP;

         CLOSE rl_cursor;
      END LOOP;

      CLOSE userid_cursor;
   END IF;

   CLOSE userfolrlauth_cursor;

   --Add administrators to pdm_userpdmauth
   OPEN userid_cursor;

	 LOOP
	    FETCH userid_cursor
	     INTO v_userid;

	    EXIT WHEN userid_cursor%NOTFOUND;

	    --delete the entry if userid exists in the table.
	    DELETE FROM pdm_userpdmauth WHERE userid = v_userid;

	       INSERT INTO pdm_userpdmauth
			   (upaid, userid, roleid, createdby, createdon, modifiedby, modifiedon
			   )
		    VALUES (pdm_userpdmauth_seq.NEXTVAL, v_userid, v_roleid, 'PDM', SYSDATE, 'PDM',
			    SYSDATE
			   );

	 END LOOP;
   CLOSE userid_cursor;


--Step 8. Disable all super-users in Pro/I in pdm_user table.
   UPDATE pdm_user
      SET usertype = 0;

--Step 9. Delete all restricting role actions from pdm_roleaction table (Pro/I limited usage)
   OPEN roleaction_cursor1;

   LOOP
      FETCH roleaction_cursor1
       INTO v_raid, v_roleid1, v_actnid;

      EXIT WHEN roleaction_cursor1%NOTFOUND;

      DELETE FROM pdm_roleaction
            WHERE raid = v_raid;
   END LOOP;

   CLOSE roleaction_cursor1;

--Step 10.  Modify table pdm_ddauthdef - change BaselineSetAttribute, BaselineSetAttributeNVO, BaselineUnSetAttribute, BaselineUnSetAttributeNVO, PIVBaselineCreateSRL, LCOSetAttribute, LCOUnSetAttribute's action from "Modify Object Attribute" To
--         "Modify Non-Versioned Attribute"

   SELECT actnid
     INTO v_actnid
     FROM pdm_action
    WHERE actnident = 2000;

   UPDATE pdm_ddauthdef
      SET actnid = v_actnid
    WHERE ddident IN (1200, 1205, 1300, 1305, 1405, 3350, 3360);

--Step 11.  Modify table pdm_ddauthdef - change FilespaceCreate, FilespaceAddVault, TriggerCreate,
--          TriggerDelete, TriggerSetDLLInfo, TriggerSetRequired, TriggerSetEnabled, TriggerSetLogged,
--          FolderAddFileSpaceReplica, FolderReplicateFiles,
--	    FolderReplicateFilesRecursive, FolderReplicateSelectedFiles,FolderReplicateSelectedPIVFiles
--	    FolderReplicateSelectedLOVFiles, FolderPurgeFilesRecursive, FolderPurgeDeletedFilesRecursive,
--          FileSpaceReplicaDelete, FileSpaceReplicaPurgeFiles, FolderPurgeDeletedFiles's action from "System Admin" To
--          "Purge Admin"
   SELECT actnid
     INTO v_actnid
     FROM pdm_action
    WHERE actnident = 4200;

   UPDATE pdm_ddauthdef
      SET actnid = v_actnid
      WHERE ddident IN (2400, 2500, 8600, 8700, 8800, 8900, 9000, 9100, 9600, 9700, 9710, 9720, 9730, 9740, 9800, 9820, 9840, 9900, 10000);

--Step 11. Set status in restrict_proi as 'restricted'.
   UPDATE restrict_proi
      SET status = 1,
          status_string = 'restricted'
    WHERE ID = 1;

--Step 12. Commit Transaction.
   COMMIT;
   DBMS_OUTPUT.put_line ('Pro/I  usage is successfully restricted.');
--Handle Exceptions ... Close Cursors
EXCEPTION
   WHEN OTHERS
   THEN
      ERR_MSG := SUBSTR(SQLERRM,1,100);

      DBMS_OUTPUT.put_line ('SQL error occured while restricting Pro/I usage. Error message is -');
      DBMS_OUTPUT.put_line (ERR_MSG);
      DBMS_OUTPUT.put_line ('');

      IF status_cursor%ISOPEN
      THEN
         CLOSE status_cursor;
      END IF;

      IF role_cursor%ISOPEN
      THEN
         CLOSE role_cursor;
      END IF;

      IF actionid_cursor%ISOPEN
      THEN
         CLOSE actionid_cursor;
      END IF;

      IF roleaction_cursor%ISOPEN
      THEN
         CLOSE roleaction_cursor;
      END IF;

      IF userid_cursor%ISOPEN
      THEN
         CLOSE userid_cursor;
      END IF;

      IF folder_cursor%ISOPEN
      THEN
         CLOSE folder_cursor;
      END IF;

      IF userfolauth_cursor%ISOPEN
      THEN
         CLOSE userfolauth_cursor;
      END IF;

      IF userfolrlauth_cursor%ISOPEN
      THEN
         CLOSE userfolrlauth_cursor;
      END IF;

      IF rl_cursor%ISOPEN
      THEN
         CLOSE rl_cursor;
      END IF;

      IF roleaction_cursor1%ISOPEN
      THEN
         CLOSE roleaction_cursor1;
      END IF;

      IF roleaction_copy_cur%ISOPEN
      THEN
         CLOSE roleaction_copy_cur;
      END IF;

      IF role_copy_cur%ISOPEN
      THEN
         CLOSE role_copy_cur;
      END IF;

      IF userfolauth_copy_cur%ISOPEN
      THEN
         CLOSE userfolauth_copy_cur;
      END IF;

      IF userfolrlauth_copy_cur%ISOPEN
      THEN
         CLOSE userfolrlauth_copy_cur;
      END IF;

      IF ddauthdef_copy_cur%ISOPEN
      THEN
         CLOSE ddauthdef_copy_cur;
      END IF;

      IF user_copy_cur%ISOPEN
      THEN
         CLOSE user_copy_cur;
      END IF;

--Rollback on any exception
      ROLLBACK;
END;
/

SET serveroutput off;
