SET ECHO OFF
SET AUTOCOMMIT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET TRIMSPOOL OFF
SET SHOW OFF

DEFINE JobUOW = &1

-- ESR Deployment phase 1 script.
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE(' ');
   DBMS_OUTPUT.PUT_LINE('ESR deployment phase 1 script.');
   DBMS_OUTPUT.PUT_LINE(' ');
END;
/
SET TERMOUT OFF

-- 1.  Disable the batch retransmission job.
-- 3.  Pause the triggers for the OPP report batch job, ESR to HEC batch job, and the NCOA batch job.

UPDATE qrtz_triggers
   SET trigger_state='PAUSED'
 WHERE trigger_name = 'scheduledJob.retransmission.trigger'
    OR trigger_name = 'scheduledJob.OPPExtractProcess.trigger'
    OR trigger_name = 'scheduledJob.dataSynchronizationHECLegacyProducer.trigger'
    OR trigger_name = 'scheduledJob.sendNCOAAddressVerificationFile.trigger';

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Changed qrtz_triggers to PAUSED.');
END;
/
SET TERMOUT OFF

-- 2. Set the bulletin mail groups to null in comm_email_template and set the dynamic addresses to null so site emails will not be sent.

UPDATE com_email_template
   SET site_email_prefix = null,
       email_recipient_list = null;
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Set EMAIL_RECIPIENT_LIST and SITE_EMAIL_PREFIX to null.');
END;
/
SET TERMOUT OFF

-- Run CodeCR6786 cleanup script to correct CancelDecline indicator values.  Pass NONE as the UOW parameter
-- so that the work is not committed or rolled back until the end.

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR6786_CancelDeclineScript.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR6786_CancelDeclineScript.sql NONE

-- Run CodeCR6870 cleanup script to pause RetransmissionEGT batch process
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR6870_PauseRetransmissionEGT.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR6870_PauseRetransmissionEGT.sql NONE

-- 4. Run CodeCR7191 Password expiration and roles for super user in new databases
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7191_AddRolesToUser.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7191_AddRolesToUser.sql NONE

-- 5. Create ReadOnly Role to be used during Phase 1 by HEC users along with its capabilities.
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Creating Observer Role and its Capabilities.'); 
END;
/
SET TERMOUT OFF
DECLARE 
		cnt NUMBER;
		roleId NUMBER(20);
		TYPE IdArray IS VARRAY(29) OF NUMBER(20);
		permissionIds IdArray;

BEGIN  
	
	SELECT COUNT(*) INTO cnt 
       FROM ROLES roles 
	   WHERE ROLE_NAME = 'Test Observer';
	
	IF ( cnt = 0 ) 
        THEN
			INSERT INTO ROLES ( ROLE_ID, ROLE_NAME, DESCRIPTION, SORT_ORDER, INACTIVE_FLAG, INACTIVE_DATE,
			RECORD_CREATED_BY, RECORD_CREATED_DATE, RECORD_MODIFIED_BY, RECORD_MODIFIED_DATE,
			RECORD_MODIFIED_COUNT, ACTIVE_DATE ) VALUES ( 
			TRANSACTION_S.NEXTVAL, 'Test Observer', 'Observer is intended to be the role used in Activation Phase 1', 0, 0, NULL, 'ESR_CLEANUP',  SYSDATE, 
			'ESR_CLEANUP',  SYSDATE, 0, TO_DATE('03/31/2006 12:04:09', 'MM/DD/YYYY HH24:MI:SS')); 
			
			SELECT ROLE_ID INTO roleId
				FROM ROLES WHERE ROLE_NAME = 'Test Observer';

			permissionIds := IdArray(1617590,1617602,1617873,1617620,1617631,1617623,1617598,1617640,1617578,1617633,1615372,1617636,1617637,1617600,1617632,1617583,1615368,1617601,1617625,1615365,1617622,1615389,1615353,1617621,1617635,1615357,1617574,1617624,1617594);
				
			FOR i IN permissionIds.FIRST..permissionIds.LAST 
			LOOP
				INSERT INTO ADR.ROLE_PERMISSIONS
   (ROLE_PERMISSION_ID, PERMISSION_TYPE_ID, ROLE_ID, RECORD_CREATED_BY, RECORD_CREATED_DATE, RECORD_MODIFIED_BY, RECORD_MODIFIED_DATE, RECORD_MODIFIED_COUNT)
 Values
   (lookup_s.NEXTVAL, permissionIds(i), roleId, 'ESR_CLEANUP', SYSDATE, 'ESR_CLEANUP', SYSDATE, 0);
			END LOOP;		
		END IF;
END;
/

-- Run ESR_CodeCR7228 Agent Orange Exposure Location missing when AO Indicator = Yes
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7228_AgentOrangeLocation.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7228_AgentOrangeLocation.sql NONE

-- Run ESR_CodeCR7265 Add Leading Zeros to Registry SSNs with Less Than 9 Digits
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7265_LeadingZeroRegistrySsn.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7265_LeadingZeroRegistrySsn.sql NONE

-- Run ESR_CodeCR7263 Service Connected Percentage set Indicator to NULL, % to 0.
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7263_ServiceConnectedPercentage.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7263_ServiceConnectedPercentage.sql NONE

SET TERMOUT ON
SET SHOW OFF
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling uow.sql &JobUOW');
END;
/
SET TERMOUT OFF
@@uow.sql &JobUOW



SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Done.');
END;
/
SET TERMOUT OFF

-- Reset environment parameters
UNDEFINE JobUOW
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
