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

DEFINE JobUOW = &1

-- ESR Final Deployment Phase 1 Script to be run post migration.

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE(' ');
   DBMS_OUTPUT.PUT_LINE('ESR Deployment Phase 1 Script - Final, to be run post data migration.');
   DBMS_OUTPUT.PUT_LINE(' ');
END;
/
SET TERMOUT OFF

-- Run CodeCR6786 cleanup script to correct CancelDecline indicator values.  

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

-- 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.  Now verifying data.');
END;
/

DECLARE cnt INTEGER := 0;
BEGIN
	SELECT count(*)
	INTO cnt
	FROM 	 cancel_decline  
	 WHERE person_id IN (
	          SELECT ed.person_id
	            FROM enrollment_determination ed JOIN cancel_decline cd ON ed.person_id =
	                                                                         cd.person_id
	                 JOIN std_enrollmentstatus se ON ed.std_enrollmentstatus_id =
	                                                                         se.ID
	           WHERE cd.cancel_decline_ind = 1
	                 AND se.enrollmentcat_id <> 1615233);
	
	 	IF (cnt > 0) THEN
			DBMS_OUTPUT.PUT_LINE('ERROR - Cancel_Decline records found where Cancel/Decline Indicator is Yes and the enrollment category is NOT "Not Enrolled"');
	 	ELSE
			DBMS_OUTPUT.PUT_LINE('Cancel_Declines cleanup script results verified, no records found.');	
		END IF;
END;
/

DECLARE cnt INTEGER := 0;
BEGIN
	cnt := 0;
	
	SELECT count(*)
	INTO cnt
	FROM  SPECIAL_FACTOR
  	WHERE SPCL_FACTOR_MAPPING_TYPE_CODE = 'AOE' and 
		SPECIAL_FACTOR_STDYESNO_ID = 1504002 and 
		AGENT_ORANGE_LOCATION_TYPE_ID is null;

	IF (cnt > 0) 
		THEN
			DBMS_OUTPUT.PUT_LINE('ERROR - Agent Orange records found where Indicator value is Yes and AgentOrangeLocation is NULL.');
		ELSE
			DBMS_OUTPUT.PUT_LINE('Agent Orange cleanup script results verified, no records found.');		
	END IF;
END;
/

DECLARE cnt INTEGER := 0;
BEGIN
	cnt := 0;
	
	SELECT count(*)
	INTO cnt
	FROM registry_trait 
	WHERE length(ssn) < 9;

	IF (cnt > 0) 
		THEN
			DBMS_OUTPUT.PUT_LINE('ERROR - Records found in the PH/POW/SHAD registry where the SSN had less than 9 digits.');
		ELSE
			DBMS_OUTPUT.PUT_LINE('Registry SSN cleanup script results verified, no records found.');			
	END IF;
END;
/

DECLARE cnt INTEGER := 0;
BEGIN
	cnt := 0;
	
	SELECT count(*)
	INTO cnt
	FROM SERVICE_CONNECTED
	WHERE SERVICE_CONNECTED_ID IN (
		SELECT sc.SERVICE_CONNECTED_ID
		FROM PERSON p, ENROLLMENT_DETERMINATION ed, SERVICE_CONNECTED sc, eligibility_determination el
		WHERE sc.SC_COMBINED_PERCENT is not null
			and sc.SERVICE_CONNECTED_ID not in (select distinct service_connected_id from rated_disability_detail)
			AND el.ELIGIBILITY_TYPE_ID = 1467765
			and el.PRIMARY_SECONDARY_OR_OTHER like 'P'
			AND p.person_id = ed.person_id
			AND p.person_id = sc.person_id
			and ed.ENROLLMENT_DETERMINATION_ID = el.ENROLLMENT_DETERMINATION_ID
		);

	IF (cnt > 0) 
		THEN
			DBMS_OUTPUT.PUT_LINE('ERROR - Records found in Service_connected where Primary Eligibility was NSC, SC % was NOT NULL 
-- and there are no Rated Disabilities.');
		ELSE
			DBMS_OUTPUT.PUT_LINE('Service Connected Percentage cleanup script results verified, no records found.');		
	END IF;
END;
/


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