SET ECHO ON
SET AUTOCOMMIT OFF
SET FEEDBACK ON
SET VERIFY OFF
SET TERMOUT ON
SET TRIMSPOOL OFF
SET SHOW OFF
SET LINESIZE 150

SET serveroutput ON SIZE 100000 

DEFINE JobUOW = &1

-- ESR Deployment phase 2 script.

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

-- 1.  Update the HL7_Transaction_log table, setting the message status to the
-- value for retransmission_failed for all messages except QRYZ11, that are
-- awaiting acknowledgement.

-- 1506928 = 'Retransmission Failed'
-- 1506923 = 'Awaiting Acknowledgement'
-- 1510428 = 'QRYZ11-M'

UPDATE hl7_transaction_log
   SET hl7_message_status_type_id = 1506928 
 WHERE hl7_message_status_type_id = 1506923 
   AND message_transmission_type_id <> 1510428;

--SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Changed QRYZ11-M from "Awaiting Acknowledgement" to "Retransmission Failed" in hl7_transaction_log.' );
END;
/
--SET TERMOUT OFF

-- 2. Enable the batch retransmission job.
-- 4.  Unpause the triggers for the OPP report batch job, ESR to HEC batch job,
-- and the NCOA batch job.

UPDATE qrtz_triggers
   SET trigger_state = 'WAITING'
 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 WAITING.');
END;
/
--SET TERMOUT OFF

-- 3.  Set the bulletin mail groups to the right, valid group in
-- com_email_template and set the dynamic addresses to their required
-- values so site emails will be sent.

-- Updating EMAIL_RECIPIENT_LIST:

UPDATE com_email_template
   SET email_recipient_list = PII                        '
 WHERE email_template_name = 'ENROLLMENT_LETTER_PROCESSING';

UPDATE com_email_template
   SET email_recipient_list = PII                '
 WHERE email_template_name = 'HEC_NOTIFY_SSA_RECORD_NOT_MATCH_HEC_INTERNAL_ID';

UPDATE com_email_template
   SET email_recipient_list = PII                  '
 WHERE email_template_name = 'EGT_PROCESS_START_NOTIFY';

UPDATE com_email_template
   SET email_recipient_list = PII                  '
 WHERE email_template_name = 'EGT_PROCESS_END_NOTIFY';

UPDATE com_email_template
   SET email_recipient_list = PII                  '
 WHERE email_template_name = 'EGT_PROCESS_STOPPED_SYS_NOTIFY';

UPDATE com_email_template
   SET email_recipient_list = PII                  '
 WHERE email_template_name = 'EGT_NOT_TRANSMITTED_NOTIFY';

UPDATE com_email_template
   SET email_recipient_list = PII              '
 WHERE email_template_name = 'OPP_EXTRACT_COMPLETED';

UPDATE com_email_template
   SET email_recipient_list = PII                       '
 WHERE email_template_name = 'NCOA_PROCESS_ENDED';

UPDATE com_email_template
   SET email_recipient_list = PII             '
 WHERE email_template_name = 'SOLICITED_Z11_NO_MATCH';

UPDATE com_email_template
   SET email_recipient_list = PII                                 '
 WHERE email_template_name = 'VSSC_FILE_PROCESSIGN_RESULTS';

--SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Set EMAIL_RECIPIENT_LIST to designated values.');
END;
/
--SET TERMOUT OFF

-- Updating SITE_EMAIL_PREFIX:

UPDATE com_email_template
   SET site_email_prefix = 'G.EAS MEANS TEST SIGNATURE'
 WHERE email_template_name = 'HEC_NOTIFY_SITE_SIGNATURE_INFORMATION_ERROR';

UPDATE com_email_template
   SET site_email_prefix = 'G.EAS MEANS TEST SIGNATURE'
 WHERE email_template_name = 'HEC_NOTIFY_SITE_INVALID_SIGNATURE_INFORMATION';

UPDATE com_email_template
   SET site_email_prefix = 'G.IB Means Test'
 WHERE email_template_name = 'HEC_NOTIFY_SERVICE_CONNECTED_PERCENTAGE_CHANGE';

UPDATE com_email_template
   SET site_email_prefix = 'G.IB Means Test'
 WHERE email_template_name = 'HEC_NOTIFY_VA_PENSION_STATUS_CHANGE';

UPDATE com_email_template
   SET site_email_prefix = 'G.IB Means Test'
 WHERE email_template_name = 'HEC_NOTIFY_RATED_DISABILITY_ADDED';

UPDATE com_email_template
   SET site_email_prefix = 'G.IB Means Test,G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_RATED_DISABILITY_REMOVED';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_POW_DISCREPANCY_NOT_POW';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_POW_DISCREPANCY_NOT_MATCH';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_PURPLE_HEART_NO_DECISION';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_POW_NO';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'BAD_ADDRESS_NOTIFY';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'INCONS_CONFLICT_DATA_FROM_SITE';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_SITE_SEND_INELIGIBLE_INFO';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_SITE_VETERAN_VERIFICATION';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'HEC_NOTIFY_SITE_VERIFY_DOD_DELETION';

UPDATE com_email_template
   SET site_email_prefix = 'G.DGEN Eligibility Alert'
 WHERE email_template_name = 'ELIG_NOT_VERIFIED_CALL_HEC';

--SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Set SITE_EMAIL_PREFIX to designated values.');
END;
/
SET TERMOUT ON
SET ECHO ON

-- 5. Cancel pending clocks
-- delete triggers for all SSN clocks (but not PH clocks)

-- delete from QRTZ_TRIGGERS table
COLUMN oldCount old_value old_count
COLUMN rows_deleted new_value rows_deleted

SELECT COUNT(*) AS oldCount
  FROM qrtz_triggers;

DELETE FROM qrtz_triggers
 WHERE trigger_group ='SSN_CLOCK_GROUP';

SELECT &old_count - COUNT(*) AS rows_deleted
  FROM qrtz_triggers;

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('&rows_deleted qrtz_triggers SSN_CLOCK_GROUP trigger(s) deleted.');
END;
/
SET TERMOUT OFF
-- delete from QRTZ_SIMPLE_TRIGGERS table
SELECT COUNT(*) AS oldCount
  FROM qrtz_simple_triggers;

DELETE FROM qrtz_simple_triggers
 WHERE trigger_group ='SSN_CLOCK_GROUP';

SELECT &old_count - COUNT(*) AS rows_deleted
  FROM qrtz_simple_triggers;

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('&rows_deleted qrtz_simple_triggers SSN_CLOCK_GROUP trigger(s) deleted.');
END;
/
SET TERMOUT OFF
-- delete from QRTZ_JOB_DETAILS table
SELECT COUNT(*) AS oldCount
  FROM qrtz_job_details;

DELETE FROM qrtz_job_details
 WHERE JOB_GROUP = 'SSN_CLOCK_GROUP';

SELECT &old_count - COUNT(*) AS rows_deleted
  FROM qrtz_job_details;

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('&rows_deleted qrtz_job_details SSN_CLOCK_GROUP trigger(s) deleted.');
END;
/
SET TERMOUT OFF

-- 6.  Set all letters that are Sent to AAC to Rejected at HEC.
-- 1615179 = 'Reject at HEC'
-- 1615177 = 'Sent to AAC'
-- 1615176 = 'Send to AAC'

UPDATE COM_MAILING_STATUS_DETAIL m
SET COM_MAILING_STATUS_TYPE_ID = 1615179 
WHERE COM_MAILING_STATUS_TYPE_ID = 1615177 or COM_MAILING_STATUS_TYPE_ID = 1615176
AND com_mailing_status_detail_id IN (
          SELECT   MAX (com_mailing_status_detail_id)
              FROM com_mailing_status_detail md
             WHERE m.com_mailing_id = md.com_mailing_id
          GROUP BY md.com_mailing_id);

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Set all letters that are Sent to AAC to Rejected at HEC.');
END;
/
SET TERMOUT OFF

-- 7. Delete IVM DM records from Person Change Log.

DELETE FROM PERSON_CHANGE_LOG WHERE SUBMITTER_DESCRIPTION='IVM_DM';

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Deleted IVM DM records from Person Change Log.');
END;
/
SET TERMOUT OFF

SET TERMOUT ON
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

-- 8. Run ESR_CodeCR7506 Close Completed PH Work Items
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7506_CloseCompletedPHWorkItems.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7506_CloseCompletedPHWorkItems.sql NONE

-- 9. Run ESR_CodeCR7619 Close merge records
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7619_closeMergeRecords.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7619_closeMergeRecords.sql NONE

-- 10.  Run ESR_CodeCR7650 Set Veteran Indicator to No for Non-SC and Ineligible records.
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7650_NonSCSetVetIndicatorToNo.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7650_NonSCSetVetIndicatorToNo.sql NONE

-- 11. Run ESR CR 7660 Clean up Cancel/Decline Date Populated by Data Sync.
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7660_CleanupCancelDeclineDateByDataSync.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7660_CleanupCancelDeclineDateByDataSync.sql NONE

-- 12. Run ESR_CodeCR7667 Clean up zip+4 to remove the zip code.
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR7667_Zipplus4Cleanup.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR7667_Zipplus4Cleanup.sql NONE

-- 13. Run CodeCR8346 to populate the Permanent Indicator as 'Unknown' for Catastrophic Disability records where it is null
SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Calling "CodeCR8346_populatePermanentIndicator.sql" NONE');
END;
/
SET TERMOUT OFF
@@CodeCR8346_populatePermanentIndicator.sql NONE


@@uow.SQL &JobUOW

BEGIN
   DBMS_OUTPUT.PUT_LINE('Done.  Now verifying data.');
END;
/

-- Verifying 7506 Close Completed PH Work Items results
DECLARE cnt INTEGER := 0;
BEGIN
	SELECT COUNT(*) 
	INTO cnt
	FROM WKF_CASE_STATUS_DETAIL
    WHERE WKF_CASE_STATUS_DETAIL_ID IN 
        (SELECT WKF_CASE_STATUS_DETAIL_ID
         FROM 
             WKF_CASE,
             WKF_CASE_STATUS_DETAIL,
             PURPLE_HEART_REGISTRY
         WHERE
             WKF_CASE.PERSON_ID = PURPLE_HEART_REGISTRY.PERSON_ID AND
			 --PH Work Items
             WKF_CASE.WKF_CASE_TYPE_ID = 1507491 AND 
             --confirmed PH or null Rejected Remark
             (PURPLE_HEART_REGISTRY.STD_REJECTEDREMARKS_ID IS NOT NULL OR PURPLE_HEART_REGISTRY.PH_STATUS_TYPE_ID = 1504903) AND 
             WKF_CASE_STATUS_DETAIL.WKF_CASE_ID = WKF_CASE.WKF_CASE_ID AND
			 -- New and Assigned 
             WKF_CASE_STATUS_DETAIL.WKF_CASE_STATUS_TYPE_ID IN (1617828, 1507210)); 	
             
	 	IF (cnt > 0) THEN
			DBMS_OUTPUT.PUT_LINE('7506 ERROR - Open PH work items found where PH status is confirmed or rejected and remark is not null. :' || cnt);
	 	ELSE
			DBMS_OUTPUT.PUT_LINE('7506 Close completed PH Work Items cleanup script results verified, no records found. :' || cnt);	
		END IF;
END;
/

-- Verifying 7619 Close merge records results
DECLARE cnt INTEGER := 0;
BEGIN
	SELECT COUNT(*) 
	INTO cnt
	FROM person_merge 
	WHERE person_merge_id IN (SELECT   c.person_merge_id   
	         FROM    (SELECT   a.person_merge_id 
	                ,a.primary_person_id person_id 
	         FROM   person_merge a  
	         WHERE  a.merge_end_date IS NULL 
	         UNION ALL 
	         SELECT   b.person_merge_id 
	                ,b.deprecated_person_id  person_id 
	         FROM   person_merge b 
	         WHERE  b.merge_end_date IS NULL 
	        ) c 
	WHERE   NOT EXISTS 
	        (SELECT  NULL 
	         FROM    enrollment_determination d 
	         WHERE  c.person_id = d.person_id 
	        ) 
	GROUP BY c.person_merge_id 
	HAVING COUNT(c.person_id) = 2);

 	IF (cnt > 0) THEN
		DBMS_OUTPUT.PUT_LINE('7619 ERROR - Open merge records found where neither person has enrollemnt record. : ' || cnt);
 	ELSE
		DBMS_OUTPUT.PUT_LINE('7619 Close merge records cleanup script results verified, no records found. : '  || cnt);	
	END IF;
END;
/

-- Verifying 7650 Set Veteran Indicator to No for Non-SC and Ineligible records.
DECLARE cnt INTEGER := 0;
BEGIN
	SELECT COUNT(*) 
	INTO cnt
	FROM person 
	WHERE person_id IN
	(SELECT p.person_id
	  FROM person p, service_connected sc, ineligible_ruling ir
	 WHERE p.veteran_ind = '1'
	   AND sc.person_id = p.person_id
	   AND sc.service_connected_ind = '0'
	   AND ir.person_id = p.person_id
	   AND ir.ineligible_date IS NOT NULL);

 	IF (cnt > 0) THEN
		DBMS_OUTPUT.PUT_LINE('7650 ERROR - Records found where Veteran Indicator is Yes for Non-SC and Ineligible records. :' || cnt);
 	ELSE
		DBMS_OUTPUT.PUT_LINE('7650 Set Veteran Indicator to No cleanup script results verified, no records found. : '  || cnt);	
	END IF;
END;
/

-- Verifying 7660 Clean up Cancel/Decline Date Populated by Data Sync.
DECLARE cnt INTEGER := 0;
BEGIN
	SELECT COUNT(*) 
	INTO cnt
	FROM CANCEL_DECLINE
	    WHERE CANCEL_DECLINE_EFFECTIVE_DATE IS NOT NULL 
	      	  AND CANCEL_DECLINE_IND = '0'
	          AND RECORD_MODIFIED_BY = 'Job-HECLegacyDataSyncConsumer';

 	IF (cnt > 0) THEN
		DBMS_OUTPUT.PUT_LINE('7660 ERROR - Records found with a Cancel/Decline Date populated by Data Sync. :'  || cnt);
 	ELSE
		DBMS_OUTPUT.PUT_LINE('7660 Cancel/Decline Date cleanup results verified, no records found. :'  || cnt);	
	END IF;
END;
/

-- Verifying 7667 Clean up zip+4 to remove the zip code.
DECLARE cnt INTEGER := 0;
BEGIN
	SELECT COUNT(*) 
	INTO cnt
	FROM address
	WHERE zip_plus_4 LIKE '%-____';

 	IF (cnt > 0) THEN
		DBMS_OUTPUT.PUT_LINE('7667 ERROR - Records found where zip+4 field contains the zip code. :'  || cnt);
 	ELSE
		DBMS_OUTPUT.PUT_LINE('7667 Clean up zip+4 results verified, no records found. : '  || cnt);	
	END IF;
END;
/

-- Verifying 8346 populate Permanent Indicator script.
DECLARE cnt INTEGER := 0;
BEGIN
	SELECT COUNT(*) 
	INTO cnt
	FROM CAT_DSBLTY_CONDITION 
		WHERE PRMNT_DSBLTY_STDYESNO_ID IS NULL;

 	IF (cnt > 0) THEN
		DBMS_OUTPUT.PUT_LINE('8346 ERROR - Records found where Permanent Indicator for Catastrophic Disability is null. : '  || cnt);
 	ELSE
		DBMS_OUTPUT.PUT_LINE('8346 populate Permanent Indicator script results verified, no records found. :'  || cnt);	
	END IF;
END;
/

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