--unrestrict_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 'unretricted', return.
--Step 4.  Truncate table pdm_roleaction and copy all entries from pdm_roleaction_copy to pdm_roleaction
--Step 5.  Truncate table pdm_userfolauth and copy all entries from pdm_userfolauth_copy pdm_userfolauth 
--Step 6a).  Truncate table pdm_userfolrlauth and copy all entries from pdm_userfolrlauth_copy pdm_userfolauth
--Step 6b).  Truncate table pdm_userpdmauth and copy all entries from pdm_userpdmauth_copy pdm_userpdmauth
--Step 7.  Find all administrators (super users... ) from pdm_user_copy and update pdm_user to make their USERTYPE = 1
--Step 8.  Delete from pdm_role where rolename = 'ProI_To_WC_Migration''
--Step 9.  Truncate pdm_ddauthdef and copy all ntries from pdm_ddauthdef_copy to pdm_ddauthdef
--Step 10. Set status in restrict_proi as 'unrestricted'.
--Step 11. Commit Transaction.  


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

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_cur
   IS
      SELECT table_name
        FROM user_tables
       WHERE table_name = 'PDM_ROLEACTION_COPY';

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

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

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

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

   TYPE refcur IS REF CURSOR;

   v_table_name   user_tables.table_name%TYPE;
   v_userid       pdm_user.userid%TYPE;
   v_sqlstmt      VARCHAR2 (400);
   user_cur       refcur;
BEGIN
--Step 2.  Insert default unrestricted status into restrict_proi
--Step 3.  Check status value in restrict_proi. If 'unretricted', 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'
                  );
           CLOSE status_cursor;
           commit;
           return;
   ELSE 
   IF status_cursor%FOUND
   THEN
      IF v_status = 0                                  --already unrestricted
      THEN
         CLOSE status_cursor;
         DBMS_OUTPUT.put_line ('Current Pro/I usage is unrestricted');
         RETURN;
      END IF;
     END IF;   
   END IF;

   CLOSE status_cursor;

--Step 4.  Truncate table pdm_roleaction and copy all entries from pdm_roleaction_copy to pdm_roleaction
--Step 5.  Truncate table pdm_userfolauth and copy all entries from pdm_userfolauth_copy pdm_userfolauth
--Step 6a).  Truncate table pdm_userfolrlauth and copy all entries from pdm_userfolrlauth_copy pdm_userfolauth
--Step 6b).  Truncate table pdm_userpdmauth and copy all entries from pdm_userpdmauth_copy pdm_userpdmauth
--Step 9.  Truncate pdm_ddauthdef and copy all ntries from pdm_ddauthdef_copy to pdm_ddauthdef
   DBMS_OUTPUT.put_line ('Current Pro/I usage is restricted');
   DBMS_OUTPUT.put_line ('Unrestricting Pro/I ...');

   OPEN roleaction_cur;

   FETCH roleaction_cur
    INTO v_table_name;

   IF roleaction_cur%FOUND
   THEN
      DELETE FROM pdm_roleaction;
   END IF;

   CLOSE roleaction_cur;

   OPEN userfolauth_cur;

   FETCH userfolauth_cur
    INTO v_table_name;

   IF userfolauth_cur%FOUND
   THEN
      DELETE FROM pdm_userfolauth;
   END IF;

   CLOSE userfolauth_cur;

   OPEN userfolrlauth_cur;

   FETCH userfolrlauth_cur
    INTO v_table_name;

   IF userfolrlauth_cur%FOUND
   THEN
      DELETE FROM pdm_userfolrlauth;
   END IF;

   CLOSE userfolrlauth_cur;

   OPEN userpdmauth_cur;
   
   FETCH userpdmauth_cur
    INTO v_table_name;
  
   IF userpdmauth_cur%FOUND
    THEN
       DELETE FROM pdm_userpdmauth;
    END IF;
 
   CLOSE userpdmauth_cur;

   OPEN ddauthdef_cur;

   FETCH ddauthdef_cur
    INTO v_table_name;

   IF ddauthdef_cur%FOUND
   THEN
      DELETE FROM pdm_ddauthdef;
   END IF;

   CLOSE ddauthdef_cur;

   EXECUTE IMMEDIATE 'INSERT INTO pdm_roleaction SELECT * FROM pdm_roleaction_copy';

   EXECUTE IMMEDIATE 'INSERT INTO pdm_userfolauth SELECT * FROM pdm_userfolauth_copy';

   EXECUTE IMMEDIATE 'INSERT INTO pdm_userfolrlauth SELECT * FROM pdm_userfolrlauth_copy';
   
   EXECUTE IMMEDIATE 'INSERT INTO pdm_userpdmauth SELECT * FROM pdm_userpdmauth_copy';

   EXECUTE IMMEDIATE 'INSERT INTO pdm_ddauthdef SELECT * FROM pdm_ddauthdef_copy';

--Step 7.  Find all administrators (super users... ) from pdm_user_copy and update pdm_user to make their USERTYPE = 1 (super users)
   
   v_sqlstmt := 'select userid from pdm_user_copy where usertype = 1';

   OPEN user_cur FOR v_sqlstmt;
   
   LOOP
      FETCH user_cur
       INTO v_userid;

      EXIT WHEN user_cur%NOTFOUND;

      UPDATE pdm_user
         SET usertype = 1
       WHERE userid = v_userid;
   END LOOP;

   CLOSE user_cur;
   
--Step 8.  Delete from pdm_role where rolename = 'ProI_To_WC_Migration''
   DELETE FROM pdm_role
         WHERE rolename = 'ProI_To_WC_Migration';
		    

--Step 10. Set status in restrict_proi_during_wc_mig as 'unrestricted'.
   UPDATE restrict_proi
      SET status = 0,
          status_string = 'unrestricted'
    WHERE ID = 1;
   
	
--Step 11. Commit Transaction.
   COMMIT;
  DBMS_OUTPUT.put_line ('Pro/I  usage is successfully unrestricted.'); 
EXCEPTION
   WHEN OTHERS
   THEN
      ERR_MSG := SUBSTR(SQLERRM,1,100);       
      
      DBMS_OUTPUT.put_line ('SQL error occured while unrestricting 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 roleaction_cur%ISOPEN
      THEN
         CLOSE roleaction_cur;
      END IF;

      IF userfolauth_cur%ISOPEN
      THEN
         CLOSE userfolauth_cur;
      END IF;

      IF userfolrlauth_cur%ISOPEN
      THEN
         CLOSE userfolrlauth_cur;
      END IF;

      IF ddauthdef_cur%ISOPEN
      THEN
         CLOSE ddauthdef_cur;
      END IF;

      IF user_cur%ISOPEN
      THEN
         CLOSE user_cur;
      END IF;

      ROLLBACK;
      RETURN;
END;
/
SET serveroutput off;

