-- ESR CodeCR7766: veteran indicator does not update in HEC - sends inconsistent data to ESR

-- Following are ad-hoc queries used to retrieve records in ESR that matches 
-- the criteria given.  HEC takes the outputs and creates input file for the
-- HEC-ESR data sync job.


-- Query 1: all vets with eligibility of "NSC", veteran indicator of "1", and there is an Ineligible date.

select p.PERSON_ID, p.vpid_value, p.HEC_IEN
from enrollment_determination ed, eligibility_determination el, person p
where EL.ELIGIBILITY_TYPE_ID = 1467765
and EL.PRIMARY_SECONDARY_OR_OTHER like 'P'
and P.VETERAN_IND = 1
and P.PERSON_ID in (select distinct person_id from ineligible_ruling where ineligible_date is not null)
and p.person_id = ed.person_id
and ED.ENROLLMENT_DETERMINATION_ID = EL.ENROLLMENT_DETERMINATION_ID;

-- Query 2: veteran indicator was "0"; eligibility code was "Humanitarian Emergency"; there was no Ineligible Date; and the record was last updated after 8/17/2008 (AP1).

select p.PERSON_ID, p.vpid_value, p.HEC_IEN, rpt.SSN, std_edstatus.NAME as ENROLLMENT_STATUS, ie.INELIGIBLE_REASON_TEXT
from enrollment_determination ed, eligibility_determination el, person p, rpt_psim_traits rpt, STD_ENROLLMENTSTATUS
 std_edstatus, ineligible_ruling ie
where EL.ELIGIBILITY_TYPE_ID = 1467753
and EL.PRIMARY_SECONDARY_OR_OTHER like 'P'
and P.VETERAN_IND = 0
and P.PERSON_ID not in (select distinct person_id from ineligible_ruling where ineligible_date is not null)
and to_date (ED.RECORD_MODIFIED_DATE) > to_date ('08/17/2008','MM/DD/YYYY')
and p.person_id = ed.person_id
and ED.ENROLLMENT_DETERMINATION_ID = EL.ENROLLMENT_DETERMINATION_ID
and p.vpid_id  = rpt.VPID_ID (+)
and ed.STD_ENROLLMENTSTATUS_ID = std_edstatus.ID 
and p.PERSON_ID = ie.PERSON_ID (+);

