/* PR-2440 Increase ExtRef length in T_Document table */
ALTER TABLE T_Document MODIFY ( ExtRef NVARCHAR2(2000));
COMMIT;
/* End of PR-2440 */

/* Sub-Review */
INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (67, 'SYSTEM', 'ALLOWSUBREVIEWS', 'PERMIT', 'SYSTEM');
COMMIT;

INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (68, 'SYSTEM', 'AUTOUPDATECHANGESFROMMASTERREVIEW', 'PERMIT', 'SYSTEM');
COMMIT;

INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (69, 'SYSTEM', 'SUBREVIEWCOMMENTORIGINALLYBY', 'PERMIT', 'SYSTEM');
COMMIT;

INSERT INTO T_Subject(ObjectId,FullName,MailAddress,WorkgroupId,SystemRole,LicenseAgreed,CultureName,Active,LastLoginDate,DescriptionNote,UserType,MailAddressL)
VALUES (-1,'Master Review','masterreview@example.com',0,NULL,0,'',1,NULL,NULL,' ','masterreview@example.com');
COMMIT;

ALTER TABLE T_Review ADD SubReviewParentId INT Default 0 NOT NULL;
COMMIT;

ALTER TABLE T_ReviewDocument ADD IsClone INT Default 0 NOT NULL;
COMMIT;

ALTER TABLE T_Comment ADD PublishToMaster INT Default 0 NOT NULL;
COMMIT;

ALTER TABLE T_Comment ADD SubReviewCommentId INT Default 0 NOT NULL;
COMMIT;

ALTER TABLE T_Comment ADD MergedCommentId INT Default 0 NOT NULL;
COMMIT;
/* End of Sub-Review */

/* PR-3460 Participant can reply but cant make new comments */

UPDATE T_PermissionSet
   SET PermissionFlags = PermissionFlags || 'REPLY;'
 WHERE PermissionFlags like '%COMMENT;%';
COMMIT;

UPDATE T_PermissionSet
   SET PermissionFlags = PermissionFlags || 'CHANGE;'
 WHERE PermissionFlags like '%COMMENT;%';

COMMIT;

/* End of PR-3460 */

/* PR-3461 Archive via PDF */

INSERT INTO T_SystemPolicy(ObjectId,Principal,Permission,PolicyAction,PolicyObject)
VALUES (70, 'SYSTEM', 'ALLOWARCHIVING', 'PERMIT', 'SYSTEM');
COMMIT;

/* End of PR-3461 */

/* PR-3538 - change DescriptionNote fields from blob to varchar */

ALTER TABLE T_Subject ADD xxx NVARCHAR2(2000);
UPDATE T_Subject SET xxx = DescriptionNote;
ALTER TABLE T_Subject DROP COLUMN DescriptionNote;
ALTER TABLE T_Subject RENAME COLUMN xxx TO DescriptionNote;

ALTER TABLE T_Workgroup ADD xxx NVARCHAR2(2000);
UPDATE T_Workgroup SET xxx = DescriptionNote;
ALTER TABLE T_Workgroup DROP COLUMN DescriptionNote;
ALTER TABLE T_Workgroup RENAME COLUMN xxx TO DescriptionNote;

/* end of PR-3538 */

/* PR-PR-3507 Comment Category Improvement */
UPDATE T_Comment
   SET CategoryText = ''
 WHERE CategoryText = '<Blank>';
 COMMIT;
 
 UPDATE T_Comment
   SET ReviewerCategoryText = ''
 WHERE ReviewerCategoryText = '<Blank>';
COMMIT;
/* end of PR-3507 */

/* PR-3521  Configurability (Settings) - Database Changes & Migration Script */
ALTER TABLE T_Workgroup ADD ParentWorkgroupId INT NULL;
COMMIT;

DECLARE workgroup_exists NUMBER(1);
totalWorkgroups NUMBER;
originalObjectId NUMBER;
BEGIN
  SELECT CASE
    WHEN EXISTS (SELECT ObjectId FROM T_Workgroup WHERE ObjectId = 1)
    THEN 1
    ELSE 0
  END INTO workgroup_exists
  FROM DUAL;
  
  SELECT COUNT(ObjectId) INTO totalWorkgroups FROM T_Workgroup;
  
  IF (workgroup_exists = 0)
  THEN
    dbms_output.put_line('workgroup 1 not exists.');
    IF (totalWorkgroups = 1)
    THEN
      SELECT ObjectId INTO originalObjectId FROM T_Workgroup;
      
      UPDATE T_Workgroup SET ObjectId = 1 WHERE ObjectId = originalObjectId;
      
      UPDATE T_Subject SET WorkgroupId = 1 WHERE WorkgroupId = originalObjectId;
	
      UPDATE T_WorkgroupMember SET WorkgroupId = 1 WHERE WorkgroupId = originalObjectId;
					
      UPDATE T_Folder SET WorkgroupId = 1 WHERE WorkgroupId = originalObjectId;
					
      UPDATE T_Review SET WorkgroupId = 1 WHERE WorkgroupId = originalObjectId;

      UPDATE T_Document SET WorkgroupId = 1 WHERE WorkgroupId = originalObjectId;
					
      UPDATE T_DistList SET OwnerWorkgroupId = 1 WHERE OwnerWorkgroupId = originalObjectId;

      UPDATE T_GenDocAudit SET WorkgroupId = 1 WHERE WorkgroupId = originalObjectId;
      
      COMMIT; 
    ELSE
      INSERT INTO T_Workgroup(ObjectId, WorkgroupName, LicenseId, IsTaskgroup, IsPublic, IsAllMonitor, ParentWorkgroupId) VALUES (1,'Root',1,0,0,0, NULL);
      
      COMMIT; 
    END IF;
  END IF;
END; 
/

UPDATE T_Workgroup
	SET ParentWorkgroupId = 1
	WHERE ParentWorkgroupId IS NULL AND ObjectId != 1;
COMMIT;

CREATE TABLE T_ReviewType(
	ObjectId			INT				NOT NULL,
	ParentReviewTypeId	INT				NULL,
	WorkgroupId			INT				NOT NULL,
	ReviewTypeName		NVARCHAR2(128)	NOT NULL
);
CREATE UNIQUE INDEX IX_ReviewType ON T_ReviewType (ObjectId);
CREATE INDEX IX_ReviewType2 ON T_ReviewType (WorkgroupId);
CREATE INDEX IX_ReviewType3 ON T_ReviewType (ReviewTypeName);

ALTER TABLE T_Review ADD ReviewTypeId INT NULL;
COMMIT;

CREATE TABLE T_Config(
	ObjectId		INT NOT NULL,
	RefObjectType	NVARCHAR2(128) NOT NULL,
	RefObjectId		INT NOT NULL,
	EntryName		NVARCHAR2(128) NOT NULL,
	AllowOverride 	INT NOT NULL,
	StringParam1	NVARCHAR2(128),
	StringParam2	NVARCHAR2(128),
	IntParam1		INT,
	IntParam2		INT,
	DateParam1		DATE,
	DateParam2		DATE,
	TextParam1		NCLOB
);
CREATE UNIQUE INDEX IX_Config ON T_Config (ObjectId);
CREATE INDEX IX_Config2 ON T_Config (RefObjectType, RefObjectId);

/* PR-3700 */
ALTER TABLE T_Subject ADD HomeWorkgroupId INT Default 1 NOT NULL;
COMMIT;

CREATE OR REPLACE PACKAGE pr_storedproc AS
	PROCEDURE SP_GetParent
	(objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	contextId IN OUT NUMBER/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	parentObjectType OUT NVARCHAR2,
	parentId OUT NUMBER);
	PROCEDURE SP_GetConfig
	(objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	entryName IN NVARCHAR2 DEFAULT NULL ,
	ignoreAllowOverride IN NUMBER DEFAULT 0 ,
	hasPreviousValue IN NUMBER DEFAULT 0 ,
	previousConfigId IN NUMBER DEFAULT NULL ,
	previousAllowOverride IN NVARCHAR2 DEFAULT NULL ,
	previousObjectType IN NVARCHAR2 DEFAULT NULL ,
	previousId IN NUMBER DEFAULT NULL ,
	contextId IN OUT NVARCHAR2/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	lineage IN OUT NVARCHAR2/* DEFAULT NULL*/,
	cv_1 IN OUT SYS_REFCURSOR);
END pr_storedproc;
/

CREATE OR REPLACE PACKAGE BODY pr_storedproc AS
/*
 * Create Stored Procedure
 */
 
 /* SP_GetParent */

PROCEDURE SP_GetParent
(
	-- Add the parameters for the stored procedure here
	objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	contextId IN OUT NUMBER/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	parentObjectType OUT NVARCHAR2,
	parentId OUT NUMBER
)
AS
	currentId NUMBER(10,0);
	nextContextId NUMBER(10,0);
	nextContextType NVARCHAR2(128);

BEGIN

	currentId := id ;
	nextContextId := id ;
	nextContextType := objectType ;
	-- get parent id
	IF UPPER(objectType) = 'REVIEW' THEN

	BEGIN
		SELECT ParentReviewId 

			INTO parentId
			FROM T_Review 
			WHERE ObjectId = currentId AND ROWNUM <= 1;
		parentObjectType := 'REVIEW' ;
		IF parentId IS NULL
			OR parentId < 1 THEN

		BEGIN
			SELECT ReviewTypeId 

				INTO parentId
				FROM T_Review 
				WHERE ObjectId = currentId AND ROWNUM <= 1;
			parentObjectType := 'REVIEWTYPE' ;
		END;
		END IF;
		IF parentId IS NULL
			OR parentId < 1 THEN

		BEGIN
			SELECT WorkgroupId 

				INTO parentId
				FROM T_Review 
				WHERE ObjectId = currentId AND ROWNUM <= 1;
			parentObjectType := 'WORKGROUP' ;
		END;
		END IF;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.put_line('SP_GetParent REVIEW No data found ***');
	END;
	ELSE
		IF UPPER(objectType) = 'REVIEWTYPE' THEN

		BEGIN
			SELECT ParentReviewTypeId 

				INTO parentId
				FROM T_ReviewType 
				WHERE ObjectId = currentId AND ROWNUM <= 1;
			parentObjectType := 'REVIEWTYPE' ;
			--preserve context
			IF contextId IS NOT NULL THEN

			BEGIN
				nextContextId := contextId ;
				nextContextType := contextType ;
			END;
			END IF;
			IF parentId IS NULL
				OR parentId < 1 THEN

			BEGIN
				--use context
				IF UPPER(contextType) = 'REVIEW'
					AND contextId IS NOT NULL THEN

				BEGIN
					currentId := contextId ;
					SELECT WorkgroupId 

						INTO parentId
						FROM T_Review 
						WHERE ObjectId = currentId AND ROWNUM <= 1;
					parentObjectType := 'WORKGROUP' ;
				END;
				END IF;
				--preserve context
				IF contextId IS NOT NULL THEN

				BEGIN
					nextContextId := contextId ;
					nextContextType := contextType ;
				END;
				END IF;
			END;
			END IF;
			IF parentId IS NULL
				OR parentId < 1 THEN

			BEGIN
				SELECT WorkgroupId 

					INTO parentId
					FROM T_ReviewType 
					WHERE ObjectId = currentId AND ROWNUM <= 1;
				parentObjectType := 'WORKGROUP' ;
				--reset context
				IF contextId IS NOT NULL THEN

				BEGIN
					nextContextId := id ;
					nextContextType := objectType ;
				END;
				END IF;
			END;
			END IF;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					DBMS_OUTPUT.put_line('SP_GetParent REVIEWTYPE No data found ***');
		END;
		ELSE
			IF UPPER(objectType) = 'WORKGROUP' THEN

			BEGIN
				SELECT ParentWorkgroupId 

					INTO parentId
					FROM T_Workgroup 
					WHERE ObjectId = currentId AND ROWNUM <= 1;
				parentObjectType := 'WORKGROUP' ;
				EXCEPTION
					WHEN NO_DATA_FOUND THEN
						DBMS_OUTPUT.put_line('SP_GetParent WORKGROUP No data found ***');
			END;
			ELSE
				IF UPPER(objectType) = 'SUBJECT' THEN

				BEGIN
					SELECT U.ObjectId 

						INTO parentId
						FROM T_Subject S
							LEFT JOIN T_UserType U   ON UPPER(S.UserType) = UPPER(U.Title)
						WHERE S.ObjectId = currentId AND ROWNUM <= 1;
					parentObjectType := 'USERTYPE' ;
					IF parentId IS NULL
						OR parentId < 1 THEN

					BEGIN
						SELECT HomeWorkgroupId 

							INTO parentId
							FROM T_Subject 
							WHERE ObjectId = currentId AND ROWNUM <= 1;
						parentObjectType := 'WORKGROUP' ;
					END;
					END IF;
					EXCEPTION
						WHEN NO_DATA_FOUND THEN
							DBMS_OUTPUT.put_line('SP_GetParent SUBJECT No data found ***');
				END;
				ELSE
					IF UPPER(objectType) = 'USERTYPE' THEN

					BEGIN
					--use context
						IF UPPER(contextType) = 'SUBJECT'
							AND contextId IS NOT NULL THEN

						BEGIN
							currentId := contextId ;
							SELECT HomeWorkgroupId 

								INTO parentId
								FROM T_Subject 
								WHERE ObjectId = currentId AND ROWNUM <= 1;
							parentObjectType := 'WORKGROUP' ;
						END;
						END IF;
						--preserve context
						IF contextId IS NOT NULL THEN

						BEGIN
							nextContextId := contextId ;
							nextContextType := contextType ;
						END;
						END IF;
						EXCEPTION
							WHEN NO_DATA_FOUND THEN
								DBMS_OUTPUT.put_line('SP_GetParent USERTYPE No data found ***');
					END;
					END IF;
				END IF;
			END IF;
		END IF;
	END IF;
	contextId := nextContextId ;
	contextType := nextContextType ;
END;

 /* SP_GetConfig */

PROCEDURE SP_GetConfig
(
	-- Add the parameters for the stored procedure here
	objectType IN NVARCHAR2 DEFAULT NULL ,
	id IN NUMBER DEFAULT NULL ,
	entryName IN NVARCHAR2 DEFAULT NULL ,
	ignoreAllowOverride IN NUMBER DEFAULT 0 ,
	hasPreviousValue IN NUMBER DEFAULT 0 ,
	previousConfigId IN NUMBER DEFAULT NULL ,
	previousAllowOverride IN NVARCHAR2 DEFAULT NULL ,
	previousObjectType IN NVARCHAR2 DEFAULT NULL ,
	previousId IN NUMBER DEFAULT NULL ,
	contextId IN OUT NVARCHAR2/* DEFAULT NULL*/,
	contextType IN OUT NVARCHAR2/* DEFAULT NULL*/,
	lineage IN OUT NVARCHAR2/* DEFAULT NULL*/,
	cv_1 IN OUT SYS_REFCURSOR
)
AS
	currentConfigId NVARCHAR2(128);
	currentAllowOverride NVARCHAR2(128);
	currentObjectType NVARCHAR2(128);
	currentId NVARCHAR2(50);
	currentHasPreviousValue NUMBER(10,0);
	currentHasData NUMBER(10,0);
	parentObjectType NVARCHAR2(50);
	parentId NVARCHAR2(50);

BEGIN

	currentHasPreviousValue := 0 ;
	currentHasData := 0 ;
	IF lineage IS NULL THEN

	BEGIN
		lineage := ' ' ;
	END;
	END IF;
	lineage := lineage || objectType || '(Id:' || CAST(id AS NVARCHAR2) || ') ' ;
	BEGIN
		SELECT AllowOverride ,
			ObjectId ,
			RefObjectType ,
			RefObjectId 

			INTO currentAllowOverride,
				currentConfigId,
				currentObjectType,
				currentId
			FROM T_Config 
			WHERE UPPER(RefObjectType) = UPPER(objectType)
				AND RefObjectId = id
				AND UPPER(EntryName) = UPPER(SP_GetConfig.entryName) AND ROWNUM <= 1;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.put_line('SELECT T_CONFIG No data found ***');
	END;
	currentHasData := CASE 
		WHEN SQL%ROWCOUNT > 0 THEN 1
		ELSE 0
	END ;
	IF currentHasData = 1 THEN

	-- has current data, set hasPreviousValue to true for next recursion
	BEGIN
		currentHasPreviousValue := 1 ;
	END;
	END IF;
	IF hasPreviousValue = 1
		AND ( currentAllowOverride IS NULL
		OR currentAllowOverride = 1 ) THEN

	-- has previous and current is overridable, disregard current
	BEGIN
		currentConfigId := previousConfigId ;
		currentAllowOverride := previousAllowOverride ;
		currentObjectType := previousObjectType ;
		currentId := previousId ;
		currentHasPreviousValue := hasPreviousValue ;
	END;
	END IF;
	IF currentHasData = 0
		OR currentHasData = 1
		AND ignoreAllowOverride = 0 THEN

	BEGIN
		SP_GetParent(objectType,
			id,
			contextId,
			contextType,
			parentObjectType,
			parentId) ;
	END;
	END IF;
	IF parentId IS NOT NULL THEN

	-- parent exist, get config for parent
	BEGIN
		SP_GetConfig(parentObjectType,
			parentId,
			SP_GetConfig.entryName,
			ignoreAllowOverride,
			currentHasPreviousValue,
			currentConfigId,
			currentAllowOverride,
			currentObjectType,
			currentId,
			contextId,
			contextType,
			lineage,
			cv_1) ;
	END;
	ELSE

	-- no parent, set to previous/current value, exit recursion
	BEGIN
		OPEN  cv_1 FOR
			SELECT StringParam1 ,
				StringParam2 ,
				IntParam1 ,
				IntParam2 ,
				DateParam1 ,
				DateParam2 ,
				TextParam1 ,
				AllowOverride ,
				RefObjectType ,
				RefObjectId ,
				lineage Lineage  
			FROM T_Config 
			WHERE ObjectId = currentConfigId AND ROWNUM <= 1 ;
	END;
	END IF;
END;

END pr_storedproc;
/

 /* new Config entry */
INSERT INTO T_Config(ObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
VALUES(41, 'WORKGROUP', 1, 'SubReviewOwnerReadOnlyInMasterReview', 0, 0);
 

BEGIN
SAVEPOINT TRANt1;	

/* STEP 1: CREATING TEMPORARY TABLE */	
DECLARE
nCount NUMBER;
TConfigNextId NUMBER;
vSQL LONG;
	
BEGIN

SELECT COUNT(*) INTO nCount FROM tab WHERE tname = 'TMPT_SYSTEMPOLICY';

IF (nCount <= 0)
THEN	
		vSQL := 'CREATE TABLE tmpT_SystemPolicy (
			ObjectId	INT NOT NULL,
			Principal	NVARCHAR2(128),
			Permission NVARCHAR2(128),
			PolicyAction	NVARCHAR2(128),
			PolicyObject		NVARCHAR2(128),
			BackupDateTime DATE NOT NULL)';
		
		EXECUTE IMMEDIATE vSQL;
						
		vSQL := 'CREATE UNIQUE INDEX IX_tmpT_SystemPolicy ON tmpT_SystemPolicy (ObjectId)';
		EXECUTE IMMEDIATE vSQL;					
END IF;
	
SELECT COUNT(*) INTO nCount FROM tab WHERE tname = 'TMPT_SETTINGS';
IF (nCount <= 0)
THEN
		vSQL := 'CREATE TABLE tmpT_Settings (
			ObjectId	INT NOT NULL,
			RefObjectType	NVARCHAR2(128) NOT NULL,
			RefObjectId	INT NOT NULL,
			EntryName	NVARCHAR2(128) NOT NULL,
			StringParam1	NVARCHAR2(128) NULL,
			StringParam2	NVARCHAR2(128) NULL,
			IntParam1	INT NULL,
			IntParam2	INT NULL,
			DateParam1	DATE NULL,
			DateParam2	DATE NULL,
			TextParam1	NCLOB NULL,
			BackupDateTime	DATE NOT NULL)';
			
		EXECUTE IMMEDIATE vSQL;				
			
END IF;

SELECT COUNT(*) INTO nCount FROM tab WHERE tname = 'TMPT_CONFIG';
IF (nCount <= 0)
THEN
		vSQL := 'CREATE TABLE tmpT_Config (
			ObjectId	INT NOT NULL,
			ExistingObjectId	INT NOT NULL,
			RefObjectType	NVARCHAR2(128) NOT NULL,
			RefObjectId	INT NOT NULL,
			EntryName	NVARCHAR2(128) NOT NULL,
			AllowOverride	INT NOT NULL,
			StringParam1	NVARCHAR2(128) NULL,
			StringParam2	NVARCHAR2(128) NULL,
			IntParam1	INT NULL,
			IntParam2	INT NULL,
			DateParam1	DATE NULL,
			DateParam2	DATE NULL,
			TextParam1	NCLOB NULL,
			BackupDateTime	DATE NOT NULL,
			IsMigrated	NUMBER NOT NULL)';
			
		EXECUTE IMMEDIATE vSQL;
		
		vSQL := 'CREATE UNIQUE INDEX IX_tmpConfig1 ON tmpT_Config (ObjectId)';
		EXECUTE IMMEDIATE vSQL;
		
		vSQL := 'CREATE INDEX IX_tmpT_tmpConfig3 ON tmpT_Config (RefObjectType, RefObjectId)';
		EXECUTE IMMEDIATE vSQL;				
END IF;

/*		
		IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tmpT_Config]') AND name = N'IX_tmpConfig2')
		DROP INDEX [IX_tmpConfig2] ON [dbo].[tmpT_Config] WITH ( ONLINE = OFF )		
*/


--Inserting the t_sequencer record type next id for config if it is not exists
MERGE INTO T_Sequencer s1
USING (SELECT 'Config' RecordType, 10000 NextId from dual) s2
ON (s1.RecordType = s2.RecordType)
WHEN NOT MATCHED THEN INSERT (RecordType, NextId) VALUES (s2.RecordType, s2.NextId);
COMMIT;	

--Drop the transfer table if already exists, cater for second migration sql execution
 BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE TRANSFERT_CONFIG';
EXCEPTION
	WHEN OTHERS THEN
		IF SQLCODE != -942 THEN
			RAISE;
		END IF;
 END;
 
 
 --Must be encapsulated into try and catch, so any error will rollback the transaction
SELECT COUNT(*) INTO nCount FROM tab WHERE tname = 'TRANSFERT_CONFIG';
SELECT NextId + 1 INTO TConfigNextId FROM T_Sequencer WHERE RecordType = 'Config';
IF (nCount <= 0)
THEN
		vSQL := 'CREATE TABLE transferT_Config (
			ObjectId	INT PRIMARY KEY,
			ExistingObjectId	INT NOT NULL,
			RefObjectType	NVARCHAR2(128) NOT NULL,
			RefObjectId	INT NOT NULL,
			EntryName	NVARCHAR2(128) NOT NULL,
			AllowOverride	INT NOT NULL,
			StringParam1	NVARCHAR2(128) NULL,
			StringParam2	NVARCHAR2(128) NULL,
			IntParam1	INT NULL,
			IntParam2	INT NULL,
			DateParam1	DATE NULL,
			DateParam2	DATE NULL,
			TextParam1	NCLOB NULL)';
			
		EXECUTE IMMEDIATE vSQL;
		
		vSQL := 'CREATE INDEX IX_transfer_Config2 ON transferT_Config (RefObjectType, RefObjectId)';
		EXECUTE IMMEDIATE vSQL;

		vSQL := 'CREATE SEQUENCE transferT_Config_Seq START WITH ' || TConfigNextId || ' INCREMENT BY 1';
		EXECUTE IMMEDIATE vSQL;

		vSQL := 'CREATE TRIGGER transferT_Config_Trg
			BEFORE INSERT ON transferT_Config
			FOR EACH ROW
			BEGIN
				SELECT transferT_Config_Seq.nextval INTO :new.ObjectId FROM dual;
			END;
		';
		EXECUTE IMMEDIATE vSQL;
END IF;
/* END OF CREATING TEMPORARY TABLE */

EXCEPTION
	WHEN OTHERS THEN
		--ROLLBACK TO TRANt1;
		RAISE;
END;		
END;
/

VARIABLE BACKUPDATETIMEVALUE VARCHAR2(30);
EXEC :BACKUPDATETIMEVALUE := TO_DATE(SYSDATE, 'DD-MM-YYYY HH:Mi:SS');

/* STEP 2: BACKUP */
-- T_Settings BACKUP

INSERT INTO tmpT_Settings
SELECT OBJECTID, REFOBJECTTYPE, REFOBJECTID, ENTRYNAME, STRINGPARAM1, STRINGPARAM2, INTPARAM1, 
INTPARAM2, DATEPARAM1, DATEPARAM2, TEXTPARAM1, :BACKUPDATETIMEVALUE FROM T_Settings;
COMMIT;

-- T_SystemPolicy BACKUP
		INSERT INTO tmpT_SystemPolicy(ObjectId, Principal, Permission, PolicyAction, PolicyObject, BackupDateTime)
		SELECT ObjectId, Principal, Permission, PolicyAction, PolicyObject, :BACKUPDATETIMEVALUE
		FROM T_SystemPolicy
		WHERE Principal = 'SYSTEM' AND PolicyObject = 'SYSTEM' AND
			(Permission = 'ALLOWDOWNLOAD' OR
			 Permission = 'ALLOWINLINEEDITING' OR
			 Permission = 'ALLOWOFFLINE' OR
			 Permission = 'ALLOWSUBREVIEWS' OR
			 Permission = 'PDFIMAGEREVIEW' OR
			 Permission = 'DEFAULTPDFIMAGE' OR
			 Permission = 'AUTOUPDATECHANGESFROMMASTERREVIEW' OR
			 Permission = 'SUBREVIEWCOMMENTORIGINALLYBY' OR
			 Permission = 'REVIEWCOUNTDOWNTIMER' OR
			 Permission = 'ADDDOCUMENTAFTERSTART' OR
			 Permission = 'AUTODELETEDAYS' OR
			 Permission = 'DEFAULTREVIEWDURATION' OR
			 Permission = 'ALLOWBOOKMARK' OR
			 Permission = 'DEFAULTINDEPENDENT' OR
			 Permission = 'COMMENTATTACHMENT' OR
			 Permission = 'COMMENTHISTORY' OR
			 Permission = 'COMMENTNUMBER' OR
			 Permission = 'OWNERDEFAULTROLE' OR
			 Permission = 'DELETECOMMENTS' OR
			 Permission = 'HIDEREVIEWNAVIGATORFORPDF' OR
			 Permission = 'PARAGRAPHLEVELNOTIFICATION' OR
			 Permission = 'ALLOWPROPOSEDCHANGE' OR
			 Permission = 'AUTODELETEAUTHOROVERRIDE' OR
			 Permission = 'REMOVEEXTRAAUTONUMLGLTEXT' OR
			 Permission = 'QUICKACCEPT' OR
			 Permission = 'DEFAULTWATCH' OR
			 Permission = 'REOPENCOMMENT' OR
			 Permission = 'REOPENREVIEW' OR
			 Permission = 'AUTOREFRESH' OR
			 Permission = 'REVIEWFAILEDDOCUMENT' OR
			 Permission = 'REVIEWTIME' OR
			 Permission = 'TAKECONTROLOFREVIEW' OR
			 Permission = 'USEREMAILNOTIFY' OR
			 Permission = 'FORCEPARTICIPANTSTATUS' OR 
			 Permission = 'PARTICIPANTDEFAULTROLE' OR
			 Permission = 'SENDSYSTEMEMAILS' OR
			 Permission = 'RECYCLEPURGEDAYS' OR
			 Permission = 'RECYCLEPURGEDAYSAUTHOR' OR
			 Permission = 'USERPREFERENCES' OR
			 Permission = 'CANCELCHECKOUTONTRANSFER' OR
			 Permission = 'CHECKINAVAILABLE' OR
			 Permission = 'CHECKINWHENREVIEWOPEN' OR
			 Permission = 'AUTOCOMPLETEDAYS' OR
			 Permission = 'AUTODELETEREMINDER' OR
			 Permission = 'LOCALFILEUPLOAD' OR
			 Permission = 'UNLOCKEDWORKGROUPS' OR
			 Permission = 'PASSWORDPOLICY' OR
			 Permission = 'NEVERSENDWELCOMEEMAILFORSYSCON' OR
			 Permission = 'NOTRANSFERIFCHECKOUT' OR
			 Permission = 'SENDPASSWORDEMAILS' OR
			 Permission = 'CREATEREVIEWONLYINTASKGROUPS' OR
			 Permission = 'TASKGROUPSFORREVIEW' OR
			 Permission = 'PROTECTDOWNLOADEDDOCUMENT'
			);
COMMIT;
			
INSERT INTO tmpT_SystemPolicy(ObjectId, Principal, Permission, PolicyAction, PolicyObject, BackupDateTime)
SELECT ObjectId, Principal, Permission, PolicyAction, PolicyObject, :BACKUPDATETIMEVALUE
FROM T_SystemPolicy
WHERE Principal = 'Sysadmin' AND PolicyObject = 'SYSTEM' AND
	(Permission = 'ENTERANYREVIEW' OR
	 Permission = 'CHANGEPASSWORD'
	);
COMMIT;

INSERT INTO tmpT_SystemPolicy(ObjectId, Principal, Permission, PolicyAction, PolicyObject, BackupDateTime)
SELECT ObjectId, Principal, Permission, PolicyAction, PolicyObject, :BACKUPDATETIMEVALUE
FROM T_SystemPolicy
WHERE Principal = 'Admin' AND PolicyObject = 'SYSTEM' AND
	(Permission = 'ADDNEWUSER' OR
	 Permission = 'EDITALLUSERS' OR
	 Permission = 'CHANGEPASSWORD'
	);
COMMIT;
	
/* END OF BACKUP */	
		
/* STEP 3: MIGRATING SYSTEM POLICY (NON-USER DATA) */

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 1, 7, 'WORKGROUP', '1', 'AllowDownload', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ALLOWDOWNLOAD'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 1 AND c.IsMigrated = 1);	
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 2, 59, 'WORKGROUP', '1', 'InlineEditing', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, 'ToolbarOff', :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ALLOWINLINEEDITING'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 2 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 3, 6, 'WORKGROUP', '1', 'AllowOffline', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ALLOWOFFLINE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 3 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 4, 67, 'WORKGROUP', '1', 'AllowSubReviews', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ALLOWSUBREVIEWS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 4 AND c.IsMigrated = 1);
COMMIT;

/* AllowPdfImage and DefaultPdfImage system policy will be combined for objectype = workgroup
   4 possibilities:
   AllowPdfImage = PERMIT DefaultPdfImage = PERMIT  >>> AllowOverride = 1, StringParam1 = image
   AllowPdfImage = PERMIT DefaultPdfImage = DENY    >>> AllowOverride = 1, StringParam1 = plugin
   AllowPdfImage = DENY   DefaultPdfImage = PERMIT  >>> AllowOverride = 0, StringParam1 = image
   AllowPdfImage = DENY   DefaultPdfImage = DENY    >>> AllowOverride = 0, StringParam1 = plugin
*/
INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 5, 45, 'WORKGROUP', '1', 'AllowPdfReview', 
	CASE WHEN PolicyAction = 'PERMIT'
	     THEN 1
	     ELSE 0 END,
	CASE WHEN (SELECT PolicyAction
			   FROM T_SystemPolicy
			   WHERE Principal = 'SYSTEM'
			   AND PolicyObject = 'SYSTEM'
			   AND Permission = 'DEFAULTPDFIMAGE'
			   AND ROWNUM = 1
	           ) = 'PERMIT'
	     THEN 'image' ELSE 'plugin' END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'PDFIMAGEREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 5 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 6, 70, 'WORKGROUP', '1', 'AllowArchiving', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ALLOWARCHIVING'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 6 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 7, 68, 'WORKGROUP', '1', 'AutoUpdateChangesFromMasterReview', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'AUTOUPDATECHANGESFROMMASTERREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 7 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 8, 69, 'WORKGROUP', '1', 'ShowSubreviewCommentOriginallyBy', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'SUBREVIEWCOMMENTORIGINALLYBY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 8 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 9, 65, 'WORKGROUP', '1', 'AllowReviewCountDownTimer', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'REVIEWCOUNTDOWNTIMER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 9 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 10, 12, 'WORKGROUP', '1', 'AllowAddDocumentAfterStart', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ADDDOCUMENTAFTERSTART'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 10 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 11, 54, 'WORKGROUP', '1', 'AutoDeleteDays', 0, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'AUTODELETEDAYS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 11 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 12, 51, 'WORKGROUP', '1', 'DefaultReviewDuration', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'DEFAULTREVIEWDURATION'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 12 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 13, 40, 'WORKGROUP', '1', 'AllowBookmark', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ALLOWBOOKMARK'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 13 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 14, 17, 'WORKGROUP', '1', 'DefaultIndependent', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'DEFAULTINDEPENDENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 14 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 15, 56, 'WORKGROUP', '1', 'AllowProposedChange', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ALLOWPROPOSEDCHANGE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 15 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 16, 49, 'WORKGROUP', '1', 'CommentAttachment', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'COMMENTATTACHMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 16 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 17, 36, 'WORKGROUP', '1', 'CommentHistory', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'COMMENTHISTORY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 17 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 18, 13, 'WORKGROUP', '1', 'DefaultWatch', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'DEFAULTWATCH'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 18 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 19, 50, 'WORKGROUP', '1', 'RemoveExtraAutonumlglText', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'REMOVEEXTRAAUTONUMLGLTEXT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 19 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 20, 37, 'WORKGROUP', '1', 'CommentNumber', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'COMMENTNUMBER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 20 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 21, 41, 'WORKGROUP', '1', 'OwnerDefaultRole', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'OWNERDEFAULTROLE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 21 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 22, 33, 'WORKGROUP', '1', 'EnterAnyReview', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ENTERANYREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 22 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 23, 38, 'WORKGROUP', '1', 'DeleteComments', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'DELETECOMMENTS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 23 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 24, 39, 'WORKGROUP', '1', 'HideReviewNavigatorForPDF', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'HIDEREVIEWNAVIGATORFORPDF'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 24 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 25, 43, 'WORKGROUP', '1', 'ParagraphLevelNotification', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'PARAGRAPHLEVELNOTIFICATION'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 25 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 26, 63, 'WORKGROUP', '1', 'AutoDelete', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'AUTODELETEAUTHOROVERRIDE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 26 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 27, 11, 'WORKGROUP', '1', 'AutoRefresh', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'AUTOREFRESH'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 27 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 28, 27, 'WORKGROUP', '1', 'QuickAccept', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, 'Off', :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'QUICKACCEPT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 28 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 29, 26, 'WORKGROUP', '1', 'ReOpenComment', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'REOPENCOMMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 29 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 30, 25, 'WORKGROUP', '1', 'ReOpenReview', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'REOPENREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 30 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 31, 60, 'WORKGROUP', '1', 'ReviewFailedDocument', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'REVIEWFAILEDDOCUMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 31 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 32, 32, 'WORKGROUP', '1', 'ReviewTime', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'REVIEWTIME'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 32 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 38, 14, 'WORKGROUP', '1', 'TakeControlOfReview', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'TAKECONTROLOFREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 38 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 33, 15, 'WORKGROUP', '1', 'UserEmailNotify', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'USEREMAILNOTIFY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 33 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 34, 28, 'WORKGROUP', '1', 'ForceParticipantStatus', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'FORCEPARTICIPANTSTATUS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 34 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 35, 42, 'WORKGROUP', '1', 'ParticipantDefaultRole', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'PARTICIPANTDEFAULTROLE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 35 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 36, 29, 'WORKGROUP', '1', 'SendSystemEmails', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'SENDSYSTEMEMAILS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 36 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 37, 64, 'WORKGROUP', '1', 'ProtectDownloadedDocument', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'PROTECTDOWNLOADEDDOCUMENT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 37 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 39, 52, 'WORKGROUP', '1', 'RecyclePurgeDays', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'RECYCLEPURGEDAYS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 39 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 40, 62, 'WORKGROUP', '1', 'RecyclePurgeDaysPleaseCompose', 1, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'RECYCLEPURGEDAYSAUTHOR'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 40 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 42, 61, 'WORKGROUP', '1', 'AddNewUser', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'ADDNEWUSER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 42 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 43, 1, 'WORKGROUP', '1', 'EditAllUsers', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'EDITALLUSERS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 43 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 44, 30, 'WORKGROUP', '1', 'UserPreferences', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'USERPREFERENCES'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 44 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 45, 47, 'WORKGROUP', '1', 'CancelCheckOutOnTransfer', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'CANCELCHECKOUTONTRANSFER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 45 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 46, 24, 'WORKGROUP', '1', 'CheckInAvailable', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'CHECKINAVAILABLE'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 46 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 47, 23, 'WORKGROUP', '1', 'CheckInWhenReviewOpen', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'CHECKINWHENREVIEWOPEN'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 47 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 48, 53, 'WORKGROUP', '1', 'AutoCompleteDays', 0, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'AUTOCOMPLETEDAYS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 48 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 49, 55, 'WORKGROUP', '1', 'AutoDeleteReminder', 0, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'AUTODELETEREMINDER'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 49 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
VALUES (50, 8, 'WORKGROUP', '1', 'LocalFileUpload', 1, 1, :BACKUPDATETIMEVALUE, 0);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 51, 3, 'WORKGROUP', '1', 'SysAdminChangePassword', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'CHANGEPASSWORD' AND Principal = 'Sysadmin'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 51 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 52, 2, 'WORKGROUP', '1', 'AdminChangePassword', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'CHANGEPASSWORD'  AND Principal = 'Admin'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 52 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
VALUES (53, 9, 'WORKGROUP', '1', 'UnlockedWorkgroups', 0, 1, :BACKUPDATETIMEVALUE, 0);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 54, 5, 'WORKGROUP', '1', 'PasswordPolicy', 0, PolicyAction, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'PASSWORDPOLICY'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 54 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 55, 66, 'WORKGROUP', '1', 'NeverSendWelcomeEmailForSyscon', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'NEVERSENDWELCOMEEMAILFORSYSCON'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 55 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 56, 48, 'WORKGROUP', '1', 'NoTransferIfCheckout', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'NOTRANSFERIFCHECKOUT'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 56 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 57, 0, 'WORKGROUP', '1', 'CommentLocation', 1, 'popup', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 57 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 58, 0, 'WORKGROUP', '1', 'CommentWindowSize', 1, 555, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 58 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 59, 0, 'WORKGROUP', '1', 'ReviewDefaultNavigation', 0, 'contents', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 59 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 60, 0, 'WORKGROUP', '1', 'EmbedReview', 0, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 60 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 61, 0, 'WORKGROUP', '1', 'ReviewDefaultNavigationPleaseCompose', 0, 'contents', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 61 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 62, 0, 'WORKGROUP', '1', 'DefaultTrack', 1, 'open', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 62 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 63, 0, 'WORKGROUP', '1', 'HighlightParagraph', 1, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 63 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 64, 0, 'WORKGROUP', '1', 'ShowPageBorders', 1, 1, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 64 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 65, 0, 'WORKGROUP', '1', 'DefaultView', 1, 'normal', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 65 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 66, 0, 'WORKGROUP', '1', 'Digest', 1, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 66 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 67,0,'WORKGROUP',1,'EmailTemplateAdminError',1,'AdminError',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 67 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 75,0,'WORKGROUP',1,'EmailTemplateChangeEmail',1,'ChangeEmail',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 75 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 76,0,'WORKGROUP',1,'EmailTemplateDigest',1,'Digest',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 76 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 77,0,'WORKGROUP',1,'EmailTemplateEditAllocate',1,'EditAllocate',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 77 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 78,0,'WORKGROUP',1,'EmailTemplateEditPublish',1,'EditPublish',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 78 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 79,0,'WORKGROUP',1,'EmailTemplateEditRevise',1,'EditRevise',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 79 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 80,0,'WORKGROUP',1,'EmailTemplateFinished',1,'Finished',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 80 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 82,0,'WORKGROUP',1,'EmailTemplateInvitation',1,'Invitation',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 82 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 85,0,'WORKGROUP',1,'EmailTemplateMasterReviewDelete',1,'MasterReviewDelete',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 85 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 86,0,'WORKGROUP',1,'EmailTemplateNewUser',1,'NewUser',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 86 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 87,0,'WORKGROUP',1,'EmailTemplateParagraphNotify',1,'ParagraphNotify',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 87 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 88,0,'WORKGROUP',1,'EmailTemplatePasswordReset',1,'PasswordReset',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 88 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 89,0,'WORKGROUP',1,'EmailTemplateReconcile',1,'Reconcile',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 89 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 90,0,'WORKGROUP',1,'EmailTemplateReminder',1,'Reminder',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 90 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 91,0,'WORKGROUP',1,'EmailTemplateReviewAutoComplete',1,'ReviewAutoComplete',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 91 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 92,0,'WORKGROUP',1,'EmailTemplateReviewAutoDelete',1,'ReviewAutoDelete',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 92 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 94,0,'WORKGROUP',1,'EmailTemplateReviewError',1,'ReviewError',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 94 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 95,0,'WORKGROUP',1,'EmailTemplateReviewNotify',1,'ReviewNotify',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 95 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 96,0,'WORKGROUP',1,'EmailTemplateReviewOverdue',1,'ReviewOverdue',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 96 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 97,0,'WORKGROUP',1,'EmailTemplateSupport',1,'Support',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 97 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 98,0,'WORKGROUP',1,'EmailTemplateSystemError',1,'SystemError',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 98 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 99,0,'WORKGROUP',1,'EmailTemplateWatch',1,'Watch',:BACKUPDATETIMEVALUE,0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 99 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 100, 34, 'WORKGROUP', '1', 'DeleteParticipantWithComments', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'DELETEPARTICIPANTWITHCOMMENTS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 100 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 101, 31, 'WORKGROUP', '1', 'StartOnSysconFail', CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'STARTONSYSCONFAIL'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 101 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 102, 4, 'WORKGROUP', '1', 'SendPasswordEmails', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'SENDPASSWORDEMAILS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 102 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 103, 58, 'WORKGROUP', '1', 'CreateReviewOnlyInTaskgroups', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'CREATEREVIEWONLYINTASKGROUPS'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 103 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 104, 57, 'WORKGROUP', '1', 'TaskgroupsForReview', 0, CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END, :BACKUPDATETIMEVALUE, 0
FROM T_SystemPolicy
WHERE Permission = 'TASKGROUPSFORREVIEW'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 104 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 105, 0, 'WORKGROUP', '1', 'DefaultReminder', 1, '3', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 105 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 106, 0, 'WORKGROUP', '1', 'AutoProgressReviewersComplete', 1, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 106 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 107, 0, 'WORKGROUP', '1', 'AutoProgressDueDate', 1, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 107 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 108, 0, 'WORKGROUP', '1', 'MandatoryCategorization', 1, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 108 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 109, 0, 'WORKGROUP', '1', 'TransferComments', 1, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 109 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1, BackupDateTime, IsMigrated)
SELECT 110, 0, 'WORKGROUP', '1', 'HomeWorkgroupRoot', 0, 0, :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 110 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 111, 0, 'WORKGROUP', '1', 'DefaultReviewType', 1, '', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 111 AND c.IsMigrated = 1);
COMMIT;

INSERT INTO tmpT_Config (ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, BackupDateTime, IsMigrated)
SELECT 112, 0, 'WORKGROUP', '1', 'ControlSetGroup', 1, '', :BACKUPDATETIMEVALUE, 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM tmpT_Config c WHERE c.ObjectId = 112 AND c.IsMigrated = 1);
COMMIT;

--fix inline editing
UPDATE T_Config
   SET EntryName = 'InlineEditing'
      ,StringParam1 = 'ToolbarOff'
WHERE EntryName = 'AllowInlineEditing';
COMMIT;

--fix AutoDeleteDays
UPDATE T_Config
   SET IntParam1 = StringParam1
      ,StringParam1 = NULL
WHERE EntryName = 'AutoDeleteDays' AND StringParam1 IS NOT NULL;
COMMIT;

--fix RecyclePurgeDaysPleaseCompose
UPDATE T_Config
   SET EntryName = 'RecyclePurgeDaysPleaseCompose'
WHERE EntryName = 'RecyclePurgeDaysPleaseAuthor' ;
COMMIT;

--fix ReviewDefaultNavigationPleaseCompose
UPDATE T_Config
   SET EntryName = 'ReviewDefaultNavigationPleaseCompose'
WHERE EntryName = 'ReviewDefaultNavigationPleaseAuthor';
COMMIT;

/* END OF MIGRATING SYSTEM POLICY (NON-USER DATA) */


/* STEP 4 : MIGRATION FROM T_SETTINGS ---> TRANST_CONFIG */
INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'AllowPdfReview', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultPdfReview'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'AllowPdfReview');
COMMIT;

/* IntParam1 actually indicate allow delegation, by default, when user created a review, delegation is TRUE by default */
INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'DisallowDelegate', 0, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'DisallowDelegate'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'DisallowDelegate');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AllowProposedChange', 1, CASE WHEN s.IntParam1 = 1 THEN 0 ELSE 1 END
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AllowProposedChange'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AllowProposedChange');
COMMIT;

DECLARE allowDownload NUMBER := 1;
BEGIN
SELECT CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
INTO allowDownload
FROM T_SystemPolicy
WHERE Permission = 'ALLOWDOWNLOAD';

SELECT AllowOverride INTO allowDownload
FROM tmpT_Config
WHERE EntryName = 'AllowDownload' AND RefObjectType = 'WORKGROUP' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AllowDownload', allowDownload, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'OfflineOptions'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AllowDownload');
COMMIT;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		NULL;
END;
/

DECLARE allowOffline NUMBER := 1;
BEGIN
SELECT CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
INTO allowOffline
FROM T_SystemPolicy
WHERE Permission = 'ALLOWOFFLINE';

SELECT AllowOverride INTO allowOffline
FROM tmpT_Config
WHERE EntryName = 'AllowOffline' AND RefObjectType = 'WORKGROUP' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AllowOffline', allowOffline, s.IntParam2
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'OfflineOptions'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AllowOffline');
COMMIT;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		NULL;
END;
/

DECLARE inlineEditing NUMBER := 1;
BEGIN
SELECT CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
INTO inlineEditing
FROM T_SystemPolicy
WHERE Permission = 'ALLOWINLINEEDITING';

SELECT AllowOverride INTO inlineEditing
FROM tmpT_Config
WHERE EntryName = 'InlineEditing' AND RefObjectType = 'WORKGROUP' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'InlineEditing', inlineEditing, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'InlineEditing'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'InlineEditing');
COMMIT;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		NULL;
END;
/

DECLARE autoDelete NUMBER := 1;
BEGIN
SELECT CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
INTO autoDelete
FROM T_SystemPolicy
WHERE Permission = 'AUTODELETEAUTHOROVERRIDE';

SELECT AllowOverride INTO autoDelete
FROM tmpT_Config
WHERE EntryName = 'AutoDelete' AND RefObjectType = 'WORKGROUP' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AutoDelete', autoDelete, CASE WHEN s.IntParam1 = 0 THEN 1 ELSE 0 END 
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AutoDelete'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AutoDelete');
COMMIT;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		NULL;
END;
/

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'TransferComments', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'TransferComments'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'TransferComments');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'AutoRefresh', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'AutoRefresh'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'AutoRefresh');
COMMIT;

DECLARE quickAccept NUMBER := 1;
BEGIN
SELECT CASE WHEN PolicyAction = 'PERMIT' THEN 1 ELSE 0 END
INTO quickAccept
FROM T_SystemPolicy
WHERE Permission = 'QUICKACCEPT';

SELECT AllowOverride INTO quickAccept
FROM tmpT_Config
WHERE EntryName = 'QuickAccept' AND RefObjectType = 'WORKGROUP' AND RefObjectId = 1;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'QuickAccept', quickAccept, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'QuickAccept'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'QuickAccept');
COMMIT;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		NULL;
END;
/

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AutoProgressReviewersComplete', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AutoProgress'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AutoProgressReviewersComplete');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'AutoProgressDueDate', 1, s.IntParam2
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'AutoProgress'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'AutoProgressDueDate');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'REVIEW', s.RefObjectId, 'MandatoryCategorization', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Review'
AND s.EntryName = 'MandatoryCategorization'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'REVIEW' AND t.EntryName = 'MandatoryCategorization');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'CommentLocation', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'CommentLocation'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'CommentLocation');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'CommentWindowSize', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'CommentWindowSize'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'CommentWindowSize');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'ReviewDefaultNavigation', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultNavigation'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'ReviewDefaultNavigation');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'ReviewDefaultNavigationPleaseCompose', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'PA_DefaultNavigation'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'ReviewDefaultNavigationPleaseCompose');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'DefaultTrack', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultTrack'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'DefaultTrack');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'HighlightParagraph', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'HighlighParagraph'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'HighlightParagraph');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'ShowPageBorders', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'ShowPageBorders'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'ShowPageBorders');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'DefaultView', 1, s.StringParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'DefaultView'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'DefaultView');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'Digest', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'Digest'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'Digest');
COMMIT;

INSERT INTO transferT_Config (ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, IntParam1)
SELECT s.ObjectId, 'SUBJECT', s.RefObjectId, 'EmbedReview', 1, s.IntParam1
FROM T_Settings s
WHERE s.RefObjectType = 'Subject'
AND s.EntryName = 'EmbedReview'
AND NOT EXISTS (SELECT 1 FROM tmpT_Config t WHERE s.ObjectId = t.ExistingObjectId AND t.RefObjectType = 'SUBJECT' AND t.EntryName = 'EmbedReview');
COMMIT;

/* END OF MIGRATION FROM T_SETTINGS ---> TMPT_CONFIG */



/* STEP 5 : MIGRATION FROM TRANST_CONFIG ---> TMPT_CONFIG
		              AND  TMPT_CONFIG ---> T_CONFIG
*/
INSERT INTO tmpT_Config(ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, StringParam2, IntParam1, IntParam2, DateParam1, DateParam2, TextParam1, BackupDateTime, IsMigrated)
SELECT ObjectId, ExistingObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, StringParam2, IntParam1, IntParam2, DateParam1, DateParam2, TextParam1, :BACKUPDATETIMEVALUE, 0
FROM transferT_Config;
COMMIT;

INSERT INTO T_Config(ObjectId, RefObjectType, RefObjectId, EntryName, AllowOverride, StringParam1, StringParam2, IntParam1, IntParam2, DateParam1, DateParam2, TextParam1)
SELECT t.ObjectId, t.RefObjectType, t.RefObjectId, t.EntryName, t.AllowOverride, t.StringParam1, t.StringParam2, t.IntParam1, t.IntParam2, t.DateParam1, t.DateParam2, t.TextParam1
FROM tmpT_Config t LEFT JOIN T_Config c ON t.ObjectId = c.ObjectId
WHERE IsMigrated = 0;
COMMIT;
/* END OF MIGRATION FROM TMPT_CONFIG ---> T_CONFIG */


/* STEP 6 : UPDATING BACK MIGRATED for IsMigrated */
MERGE INTO tmpT_Config t 
USING (SELECT ObjectId FROM T_Config) trans
ON (t.ObjectId = trans.ObjectId)
WHEN MATCHED THEN UPDATE
SET t.IsMigrated = 1;
COMMIT;
/* END OF UPDATING BACK MIGRATED for IsMigrated */


/* STEP 7 : DELETING EXISTING DATA AT T_SETTINGS TABLE */
--DELETE s FROM T_Settings s INNER JOIN tmpT_Config trans ON s.ObjectId = trans.ExistingObjectId WHERE IsMigrated = 1

DELETE FROM 
(
	SELECT s.* FROM T_Settings s
	WHERE s.ObjectId IN (SELECT ExistingObjectId FROM tmpT_Config trans WHERE s.ObjectId = trans.ExistingObjectId AND trans.IsMigrated = 1)
);
COMMIT;
/* END OF DELETING EXISTING DATA AT T_SETTINGS TABLE */


/* STEP 8 : DELETING FROM EXISTING SYSTEM POLICY (Add according to ObjectId sequence please */
--No longer required. See PR-3822 below.
/* END OF DELETING FROM SYSTEM POLICY */

/* STEP 9 : UPDATING BACK T_SEQUENCER FOR T_Config TABLE */
DECLARE configCount int := 0;
sequencerCount int := 0;
BEGIN
	SELECT MAX(ObjectId) INTO configCount FROM T_Config;
	SELECT NEXTID INTO sequencerCount FROM T_Sequencer WHERE UPPER(RecordType) = 'CONFIG' AND rownum = 1;

	IF (configCount >= sequencerCount) THEN
		UPDATE T_Sequencer SET NEXTID = (SELECT MAX(ObjectId) FROM T_Config) + 1 WHERE UPPER(RecordType) = 'CONFIG';
		COMMIT;
	END IF;
END;
/
/* END OF UPDATING BACK T_SEQUENCER FOR T_Config TABLE */

/* End of PR-3521 */

CREATE TABLE T_LicenseFile(
	ObjectId INT NOT NULL,
	ParentLicenseFileId INT NULL,
	LicenseDetailsXml CLOB NULL,
	UpdateDate DATE NOT NULL
);
CREATE UNIQUE INDEX IX_LicenseFile ON T_LicenseFile (ObjectId);
COMMIT;

/* PR-3525 - Workgroup Management Enhancement */
/* TO SUPPORT MULTIPLE WORKGROUP WITH THE SAME NAME, THEREFORE THE UNIQUE CONTRAINTS MUST BE REMOVED. */
BEGIN
SAVEPOINT TWorkgroupTRANS;

DECLARE vSQL LONG;
BEGIN
/* STEP 1.0 - CREATE A NEW TABLE [T_WorkgroupNew] */
vSQL := 'CREATE TABLE T_WorkgroupNew (
	ObjectId	INT NOT NULL,
	WorkgroupName	NVARCHAR2(128)	NOT NULL,
	DescriptionNote NVARCHAR2(2000),
	LicenseId	INT NOT NULL,
	ExtSource	NVARCHAR2(128),
	ExtRef		NVARCHAR2(128),
	IsTaskgroup INT NOT NULL,
	IsPublic	INT NULL,
	IsAllMonitor	INT DEFAULT 0 NOT NULL,
	ParentWorkgroupId INT NULL
)';

EXECUTE IMMEDIATE vSQL;

/* STEP 2.0 - EXISTING UNIQUE INDEX ON [ObjectId] */
vSQL := 'CREATE UNIQUE INDEX IX_T_Workgroup ON T_WorkgroupNew (ObjectId)';
EXECUTE IMMEDIATE vSQL;	

/* ##### STEP 3.0 - THIS IS THE INDEX THAT CAUSED THE UNIQUE KEY CONSTRAINT ERROR, RECREATE WITH NON UNIQUE WITH [LicenseId], [WorkgroupName] */
vSQL := 'CREATE INDEX IX_T_Workgroup2 ON T_WorkgroupNew (LicenseId, WorkgroupName)';
EXECUTE IMMEDIATE vSQL;	

EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK TO TWorkgroupTRANS;
		RAISE;
END;
END;
/

/* STEP 4.0 - COPY DATA FROM [T_Workgroup] TO [T_WorkgroupNew] */
INSERT INTO T_WorkgroupNew (ObjectId, WorkgroupName, DescriptionNote, LicenseId, ExtSource, ExtRef, IsTaskgroup, IsPublic, IsAllMonitor, ParentWorkgroupId)
SELECT ObjectId, WorkgroupName, DescriptionNote, LicenseId, ExtSource, ExtRef, IsTaskgroup, IsPublic, IsAllMonitor, ParentWorkgroupId FROM T_Workgroup;
COMMIT;

/* STEP 5.0 - DROP TABLE [T_Workgroup] */
DROP TABLE T_Workgroup;
COMMIT;

/* STEP 6.0 - RENAME THE TABLE BACK TO [T_Workgroup] */
ALTER TABLE T_WorkgroupNew RENAME TO T_Workgroup;
COMMIT;

/* END OF PR-3525. */

DELETE FROM T_Config WHERE ObjectId in (68,69,70,71,72,73,74,81);
DELETE FROM T_StatusCode WHERE StatusType in ('A','V');
COMMIT;

/* PR-3803 - Slow Document Preparation. */
/* Column to track whether a document is uploaded with advanced setting. (e.g. Complex Document). */
ALTER TABLE T_Document
ADD AdvancedSetting NVARCHAR2(5) NULL;

/* PR-3822 - Remove T_SystemPolicy table and obsolete system policies */
DROP TABLE T_SystemPolicy;
COMMIT;

/* PR-3523 - Configurability (Settings) - Database Changes & Migration Script */
/* Drop All Temp Tables and Sequence. */
DROP TABLE tmpT_Config;
COMMIT;
DROP TABLE tmpT_Settings;
COMMIT;
DROP TABLE tmpT_SystemPolicy;
COMMIT;
DROP TABLE transferT_Config;
COMMIT;
DROP SEQUENCE transferT_Config_Seq;
COMMIT;

/* Schema Versioning */
UPDATE T_SchemaInfo SET SchemaVersion = '5.1.0';
COMMIT;
/* End of Schema Versioning */
