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

-- ESR CR 7263 Service Connected Percentage Cleanup script.
-- This script should be run after Data Migration and before Bulk E&E. 
-- It fixes records in ADR where Primary Eligibility was NSC, SC % was NOT NULL 
-- and there are no Rated Disabilities by setting Service connected % to 0 and
-- SC indicator to NULL.

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7263: SC % not null, but no Rated Disability.');
   DBMS_OUTPUT.put_line (' ');
END;
/

SET TERMOUT OFF

-- 1467765 = "NSC"

UPDATE SERVICE_CONNECTED
SET SERVICE_CONNECTED_IND='0',
    SC_COMBINED_PERCENT=NULL
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
);

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Changed SC indicator and percentage.');
END;
/
SET TERMOUT OFF

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

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Done.');
END;
/
SET TERMOUT OFF
-- Reset environment parameters
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
