<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<!--
	=========================================
	Copyright © 2006 VHA. All rights reserved
	=========================================

	@description: This file contains all queries for standard reports

	@project: Common
	@author: Muddaiah Ranga
	@version: 1.0

-->
<hibernate-mapping
    default-lazy="false"
    auto-import="true"
    default-access="property"
    default-cascade="all">

	<!--
		Query statement for a summary view of a "New Enrollment Processing Time" report.
	-->
	<sql-query name="QM3.Summary">
		<![CDATA[
			select  count(z07_received_date) as APPLICATIONS_COUNT,
					round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
					round(avg(VBA_RESPONSE_DAYS)) as AVG_VBA_RESPONSE_DAYS,
					round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
					round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
					round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
					round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
			from
			(
				  select
				   z07_received_date,
				   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
				   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
				   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
				   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
				   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
				   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
					from enrollment_determination en, person p , rpt_psim_traits pt, std_institution facility,
				        (select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
								from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
								where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
								group by person_id) elg_verify,
				   		 (select person_id, min(log.record_created_date) as z07_received_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
						 		 	   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		 group by person_id)  z07_log,
						 (select person_id, min(log.record_created_date) as vba_query_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
						 		 group by person_id)  qryz11m_log,
						 (select person_id, min(log.record_created_date) as vba_resp_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
						 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		 group by person_id)  orfz11e_log,
						 (select person_id, min(log.ack_date) as vista_ack_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
						 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		           and log.ack_date >=
						 		           	(select min(elg_vr_h.record_modified_date)
										from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
										where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
											  and elg_vr_h.person_id=log.person_id)
						 		 group by person_id)  oruz11s_log
				   where en.person_id = p.person_id(+) and
				   		 p.vpid_value = pt.vpid_value and
				   		 en.person_id = elg_verify.person_id(+) and
						 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
				   		 en.person_id = qryz11m_log.person_id(+) and
						 en.person_id = orfz11e_log.person_id(+) and
						 en.person_id = oruz11s_log.person_id(+) and
						 p.preferred_site_id = facility.id and
						 (
						 ('Y' = :incompleteFlg and vista_ack_date is null)
						 OR vista_ack_date is not null
						 )and
						 en.change_effective_date >= :startDate and
						 en.change_effective_date < :endDate + 1
			)
		]]>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>

	<!--
		Query statement for a summary view of a "New Enrollment Processing Time" report.
	-->
	<sql-query name="QM3.Summary.visn">
		<![CDATA[
			select  grouping_id(VISN_NAME) as GROUP_ID,
					VISN_NAME,
					count(z07_received_date) as APPLICATIONS_COUNT,
					round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
					round(avg(VBA_RESPONSE_DAYS),1) as AVG_VBA_RESPONSE_DAYS,
					round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
					round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
					round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
					round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
			from
			(
				  select
				   nvl(visn.name,'NO VISN') as VISN_NAME,
				   z07_received_date,
				   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
				   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
				   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
				   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
				   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
				   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
				   from enrollment_determination en, person p , rpt_psim_traits pt, std_institution facility, std_institution visn,
				        (select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
								from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
								where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
								group by person_id) elg_verify,
				   		 (select person_id, min(log.record_created_date) as z07_received_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
						 		 	   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		 group by person_id)  z07_log,
						 (select person_id, min(log.record_created_date) as vba_query_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
						 		 group by person_id)  qryz11m_log,
						 (select person_id, min(log.record_created_date) as vba_resp_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
						 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		 group by person_id)  orfz11e_log,
						 (select person_id, min(log.ack_date) as vista_ack_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
						 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		           and log.ack_date >=
						 		           	(select min(elg_vr_h.record_modified_date)
										from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
										where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
											  and elg_vr_h.person_id=log.person_id)
						 		 group by person_id)  oruz11s_log
				   where en.person_id = p.person_id(+) and
				   		 p.vpid_value = pt.vpid_value and
				   		 en.person_id = elg_verify.person_id(+) and
						 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
				   		 en.person_id = qryz11m_log.person_id(+) and
						 en.person_id = orfz11e_log.person_id(+) and
						 en.person_id = oruz11s_log.person_id(+) and
						 p.preferred_site_id = facility.id and
						 facility.visn_id = visn.id(+) and
						 (visn.id in (:visns) or ('noVISN' = :noVISN and visn.id is null )) and
						 (('Y' = :incompleteFlg and vista_ack_date is null)	OR vista_ack_date is not null)and
						 en.change_effective_date >= :startDate and
						 en.change_effective_date < :endDate + 1
			)
			group by grouping sets((VISN_NAME), ())
			order by lpad(trim(substr(VISN_NAME, 5)), 2, '0'), GROUP_ID
		]]>
		<return-scalar column="GROUP_ID" type="big_decimal"/>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>

	<sql-query name="QM3.Summary.visn.site">
		<![CDATA[
			select  grouping_id(VISN_NAME, FACILITY_NAME) as GROUP_ID,
					VISN_NAME,
					FACILITY_NAME,
					count(z07_received_date) as APPLICATIONS_COUNT,
					round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
					round(avg(VBA_RESPONSE_DAYS)) as AVG_VBA_RESPONSE_DAYS,
					round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
					round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
					round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
					round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
				from
				(
				  select
				   nvl(visn.name,'NO VISN') as VISN_NAME,
				   facility.name as FACILITY_NAME,
				   z07_received_date,
				   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
				   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
				   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
				   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
				   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
				   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
				   from enrollment_determination en, person p , rpt_psim_traits pt, std_institution facility, std_institution visn,
					(select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
								from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
								where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
								group by person_id) elg_verify,
						 (select person_id, min(log.record_created_date) as z07_received_date
								 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
								 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
									   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
								 group by person_id)  z07_log,
						 (select person_id, min(log.record_created_date) as vba_query_date
								 from hl7_transaction_log log, STD_MESSAGETYPE mtype
								 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
								 group by person_id)  qryz11m_log,
						 (select person_id, min(log.record_created_date) as vba_resp_date
								 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
								 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
									   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
								 group by person_id)  orfz11e_log,
						 (select person_id, min(log.ack_date) as vista_ack_date
								 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
								 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
									   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
									   and log.ack_date >=
										(select min(elg_vr_h.record_modified_date)
										from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
										where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
											  and elg_vr_h.person_id=log.person_id)
								 group by person_id)  oruz11s_log
				   where en.person_id = p.person_id(+) and
				   		 p.vpid_value = pt.vpid_value and
						 en.person_id = elg_verify.person_id(+) and
						 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
						 en.person_id = qryz11m_log.person_id(+) and
						 en.person_id = orfz11e_log.person_id(+) and
						 en.person_id = oruz11s_log.person_id(+) and
						 p.preferred_site_id = facility.id and
						 facility.visn_id = visn.id(+) and
						 ('all' = :is_all or facility.id in (:facilities)) and
						 (
						 ('Y' = :incompleteFlg and vista_ack_date is null)
						 OR vista_ack_date is not null
						 )and
						 en.change_effective_date >= :startDate and
						 en.change_effective_date < :endDate + 1
				)
				group by grouping sets((VISN_NAME, FACILITY_NAME), (VISN_NAME), ())
				order by lpad(trim(substr(VISN_NAME, 5)), 2, '0'), FACILITY_NAME, GROUP_ID
		]]>
		<return-scalar column="GROUP_ID" type="big_decimal"/>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="FACILITY_NAME" type="string"/>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>

	<!--
		Query statement for a summary view of a "New Enrollment Processing Time" report.
	-->
	<sql-query name="QM3.Summary.site">
		<![CDATA[
			select  grouping_id(FACILITY_NAME) as GROUP_ID,
					FACILITY_NAME,
					count(z07_received_date) as APPLICATIONS_COUNT,
					round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
					round(avg(VBA_RESPONSE_DAYS)) as AVG_VBA_RESPONSE_DAYS,
					round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
					round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
					round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
					round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
			from
			(
				  select
				   facility.name as FACILITY_NAME,
				   z07_received_date,
				   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
				   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
				   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
				   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
				   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
				   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
				   from enrollment_determination en, person p , rpt_psim_traits pt, std_institution facility,
				        (select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
								from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
								where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
								group by person_id) elg_verify,
				   		 (select person_id, min(log.record_created_date) as z07_received_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
						 		 	   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		 group by person_id)  z07_log,
						 (select person_id, min(log.record_created_date) as vba_query_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
						 		 group by person_id)  qryz11m_log,
						 (select person_id, min(log.record_created_date) as vba_resp_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
						 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		 group by person_id)  orfz11e_log,
						 (select person_id, min(log.ack_date) as vista_ack_date
						 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
						 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
						 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
						 		           and log.ack_date >=
						 		           	(select min(elg_vr_h.record_modified_date)
										from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
										where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
											  and elg_vr_h.person_id=log.person_id)
						 		 group by person_id)  oruz11s_log
				   where en.person_id = p.person_id(+) and
				   		 p.vpid_value = pt.vpid_value and
				   		 en.person_id = elg_verify.person_id(+) and
						 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
				   		 en.person_id = qryz11m_log.person_id(+) and
						 en.person_id = orfz11e_log.person_id(+) and
						 en.person_id = oruz11s_log.person_id(+) and
						 p.preferred_site_id = facility.id and
						 ('all' = :is_all or facility.id in (:facilities)) and
						 (
						 ('Y' = :incompleteFlg and vista_ack_date is null)
						 OR vista_ack_date is not null
						 )and
						 en.change_effective_date >= :startDate and
		 				 en.change_effective_date < :endDate + 1
			)
			group by grouping sets((FACILITY_NAME), ())
			order by FACILITY_NAME, GROUP_ID
		]]>
		<return-scalar column="GROUP_ID" type="big_decimal"/>
		<return-scalar column="FACILITY_NAME" type="string"/>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>

	<!--
		Query statement for a summary view of a "New Enrollment Processing Time" report.
	-->
	<sql-query name="QM3.Detail">
		<![CDATA[
			select 	decode(grouping_id(NAME,SSN),0,'DETAIL',3,'GRAND_TOTAL') as GROUP_ID,
				NAME, SSN,
				APPLICATION_DATE,
				Z07_RECEIVED_DATE,
				VBA_QUERY_DATE,
				VBA_RESP_DATE,
				ELG_VERIFIED_DATE,
				ENROLLMENT_DATE,
				VISTA_ACK_DATE,
				TOTAL_PROCESSING_TIME,
				count(z07_received_date) as APPLICATIONS_COUNT,
				round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
				round(avg(VBA_RESPONSE_DAYS)) as AVG_VBA_RESPONSE_DAYS,
				round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
				round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
				round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
				round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
				from
				(
				select pt.last_name || ', ' || pt.first_name || ' ' || substr(pt.middle_name, 1, 1) as NAME,
					   nvl2(pt.ssn ,substr(pt.ssn ,1,3)||'-'||substr(pt.ssn ,4,2)||'-'||substr(pt.ssn ,6),null) as SSN,
					   APPLICATION_DATE,
					   Z07_RECEIVED_DATE,
					   VBA_QUERY_DATE,
					   VBA_RESP_DATE,
					   ELG_VERIFIED_DATE,
					   en.record_created_date as ENROLLMENT_DATE,
					   VISTA_ACK_DATE,
					   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
					   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
					   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
					   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
					   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
					   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
					   from enrollment_determination en, person p , rpt_psim_traits pt,
							(select person_id, min(application_date) as application_date
									from health_benefits_application_h
									group by person_id) appl,
						(select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
									from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
									where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
									group by person_id) elg_verify,
							 (select person_id, min(log.record_created_date) as z07_received_date
									 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
									 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
										   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
									 group by person_id)  z07_log,
							 (select person_id, min(log.record_created_date) as vba_query_date
									 from hl7_transaction_log log, STD_MESSAGETYPE mtype
									 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
									 group by person_id)  qryz11m_log,
							 (select person_id, min(log.record_created_date) as vba_resp_date
									 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
									 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
										   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
									 group by person_id)  orfz11e_log,
							 (select person_id, min(log.ack_date) as vista_ack_date
									 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
									 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
										   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
										   and log.ack_date >=
											(select min(elg_vr_h.record_modified_date)
											from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
											where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
												  and elg_vr_h.person_id=log.person_id)
									 group by person_id)  oruz11s_log
					   where en.person_id = p.person_id(+) and
							 p.vpid_value = pt.vpid_value and
							 en.person_id = appl.person_id(+) and
							 en.person_id = elg_verify.person_id(+) and
							 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
							 en.person_id = qryz11m_log.person_id(+) and
							 en.person_id = orfz11e_log.person_id(+) and
							 en.person_id = oruz11s_log.person_id(+) and
							 (('Y' = :incompleteFlg and vista_ack_date is null) OR vista_ack_date is not null) and
							 en.change_effective_date >= :startDate and
						     	 en.change_effective_date < :endDate + 1
				)
				where   ('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS >= :days)
					OR
					('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS >= :days)
					OR
					('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS >= :days)
					OR
					('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  >= :days)
					OR
					('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  >= :days)
					OR
					('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS is null)
					OR
					('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS is null)
					OR
					('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS is null)
					OR
					('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  is null)
					OR
					('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  is null)
				group by grouping sets((NAME, SSN,APPLICATION_DATE,Z07_RECEIVED_DATE,VBA_QUERY_DATE,VBA_RESP_DATE,ELG_VERIFIED_DATE,ENROLLMENT_DATE,VISTA_ACK_DATE,TOTAL_PROCESSING_TIME),())
				order by NAME, SSN
		]]>
		<return-scalar column="GROUP_ID" type="string"/>
		<return-scalar column="NAME" type="string"/>
		<return-scalar column="SSN" type="string"/>
		<return-scalar column="APPLICATION_DATE" type="timestamp"/>
		<return-scalar column="Z07_RECEIVED_DATE" type="timestamp"/>
		<return-scalar column="VBA_QUERY_DATE" type="timestamp"/>
		<return-scalar column="VBA_RESP_DATE" type="timestamp"/>
		<return-scalar column="ELG_VERIFIED_DATE" type="timestamp"/>
		<return-scalar column="ENROLLMENT_DATE" type="timestamp"/>
		<return-scalar column="VISTA_ACK_DATE" type="timestamp"/>
		<return-scalar column="TOTAL_PROCESSING_TIME" type="big_decimal"/>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>
	<!--
		Query statement for a summary view of a "New Enrollment Processing Time" report.
	-->
	<sql-query name="QM3.Detail.visn">
		<![CDATA[
			select	decode(grouping_id(VISN_NAME,SSN),0,'DETAIL',1,'VISN_TOTAL',3,'GRAND_TOTAL') as GROUP_ID,
					VISN_NAME, NAME, SSN,
					APPLICATION_DATE,
					Z07_RECEIVED_DATE,
					VBA_QUERY_DATE,
					VBA_RESP_DATE,
					ELG_VERIFIED_DATE,
					ENROLLMENT_DATE,
					VISTA_ACK_DATE,
					TOTAL_PROCESSING_TIME,
					count(z07_received_date) as APPLICATIONS_COUNT,
					round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
					round(avg(VBA_RESPONSE_DAYS)) as AVG_VBA_RESPONSE_DAYS,
					round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
					round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
					round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
					round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
					from
					(
					select nvl(visn.name,'NO VISN') VISN_NAME,
						   pt.last_name || ', ' || pt.first_name || ' ' || substr(pt.middle_name, 1, 1) as NAME,
						   nvl2(pt.ssn ,substr(pt.ssn ,1,3)||'-'||substr(pt.ssn ,4,2)||'-'||substr(pt.ssn ,6),null) as SSN,
						   APPLICATION_DATE,
						   Z07_RECEIVED_DATE,
						   VBA_QUERY_DATE,
						   VBA_RESP_DATE,
						   ELG_VERIFIED_DATE,
						   en.record_created_date as ENROLLMENT_DATE,
						   VISTA_ACK_DATE,
						   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
						   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
						   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
						   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
						   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
						   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
						   from enrollment_determination en, person p , rpt_psim_traits pt, std_institution facility, std_institution visn,
						   		(select person_id, min(application_date) as application_date
										from health_benefits_application_h
										group by person_id) appl,
						        (select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
										from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
										where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
										group by person_id) elg_verify,
						   		 (select person_id, min(log.record_created_date) as z07_received_date
								 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
								 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
								 		 	   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
								 		 group by person_id)  z07_log,
								 (select person_id, min(log.record_created_date) as vba_query_date
								 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype
								 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
								 		 group by person_id)  qryz11m_log,
								 (select person_id, min(log.record_created_date) as vba_resp_date
								 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
								 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
								 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
								 		 group by person_id)  orfz11e_log,
								 (select person_id, min(log.ack_date) as vista_ack_date
								 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
								 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
								 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
								 		           and log.ack_date >=
								 		           	(select min(elg_vr_h.record_modified_date)
												from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
												where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
													  and elg_vr_h.person_id=log.person_id)
								 		 group by person_id)  oruz11s_log
						   where en.person_id = p.person_id(+) and
						   		 p.vpid_value = pt.vpid_value and
						   		 en.person_id = appl.person_id(+) and
						   		 en.person_id = elg_verify.person_id(+) and
								 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
						   		 en.person_id = qryz11m_log.person_id(+) and
								 en.person_id = orfz11e_log.person_id(+) and
								 en.person_id = oruz11s_log.person_id(+) and
								 p.preferred_site_id = facility.id and
								 facility.visn_id = visn.id(+) and
                                 (visn.id in (:visns) or ('noVISN' = :noVISN and visn.id is null )) and
								 ( ('Y' = :incompleteFlg and vista_ack_date is null) OR vista_ack_date is not null) and
								 en.change_effective_date >= :startDate and
							     en.change_effective_date < :endDate + 1
					)
					where
						('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS >= :days)
						OR
						('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS >= :days)
						OR
						('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS >= :days)
						OR
						('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  >= :days)
						OR
						('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  >= :days)
						OR
						('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS is null)
						OR
						('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS is null)
						OR
						('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS is null)
						OR
						('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  is null)
						OR
						('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  is null)
					group by grouping sets(	(VISN_NAME, NAME, SSN,
						  	 	 APPLICATION_DATE, Z07_RECEIVED_DATE, VBA_QUERY_DATE,
								 VBA_RESP_DATE, ELG_VERIFIED_DATE, ENROLLMENT_DATE,
								 VISTA_ACK_DATE, TOTAL_PROCESSING_TIME), (VISN_NAME), ())
					order by lpad(trim(substr(VISN_NAME, 5)), 2, '0'), NAME, SSN

		]]>
		<return-scalar column="GROUP_ID" type="string"/>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="NAME" type="string"/>
		<return-scalar column="SSN" type="string"/>
		<return-scalar column="APPLICATION_DATE" type="timestamp"/>
		<return-scalar column="Z07_RECEIVED_DATE" type="timestamp"/>
		<return-scalar column="VBA_QUERY_DATE" type="timestamp"/>
		<return-scalar column="VBA_RESP_DATE" type="timestamp"/>
		<return-scalar column="ELG_VERIFIED_DATE" type="timestamp"/>
		<return-scalar column="ENROLLMENT_DATE" type="timestamp"/>
		<return-scalar column="VISTA_ACK_DATE" type="timestamp"/>
		<return-scalar column="TOTAL_PROCESSING_TIME" type="big_decimal"/>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>
	<!--
		Query statement for a summary view of a "New Enrollment Processing Time" report.
	-->
	<sql-query name="QM3.Detail.visn.site">
		<![CDATA[
		select 	decode(grouping_id(VISN_NAME,FACILITY_NAME,SSN),0,'DETAIL',1,'SITE_TOTAL',3,'VISN_TOTAL',7,'GRAND_TOTAL') as GROUP_ID,
				VISN_NAME, FACILITY_NAME, NAME, SSN,
				APPLICATION_DATE,
				Z07_RECEIVED_DATE,
				VBA_QUERY_DATE,
				VBA_RESP_DATE,
				ELG_VERIFIED_DATE,
				ENROLLMENT_DATE,
				VISTA_ACK_DATE,
				TOTAL_PROCESSING_TIME,
				count(z07_received_date) as APPLICATIONS_COUNT,
				round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
				round(avg(VBA_RESPONSE_DAYS)) as AVG_VBA_RESPONSE_DAYS,
				round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
				round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
				round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
				round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
				from
				(
				select nvl(visn.name,'NO VISN') VISN_NAME, facility.name as FACILITY_NAME,
					   pt.last_name || ', ' || pt.first_name || ' ' || substr(pt.middle_name, 1, 1) as NAME,
					   nvl2(pt.ssn ,substr(pt.ssn ,1,3)||'-'||substr(pt.ssn ,4,2)||'-'||substr(pt.ssn ,6),null) as SSN,
					   APPLICATION_DATE,
					   Z07_RECEIVED_DATE,
					   VBA_QUERY_DATE,
					   VBA_RESP_DATE,
					   ELG_VERIFIED_DATE,
					   en.record_created_date as ENROLLMENT_DATE,
					   VISTA_ACK_DATE,
					   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
					   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
					   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
					   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
					   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
					   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
					   from enrollment_determination en, person p , rpt_psim_traits pt, std_institution facility, std_institution visn,
					   		(select person_id, min(application_date) as application_date
									from health_benefits_application_h
									group by person_id) appl,
					        (select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
									from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
									where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
									group by person_id) elg_verify,
					   		 (select person_id, min(log.record_created_date) as z07_received_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
							 		 	   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
							 		 group by person_id)  z07_log,
							 (select person_id, min(log.record_created_date) as vba_query_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
							 		 group by person_id)  qryz11m_log,
							 (select person_id, min(log.record_created_date) as vba_resp_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
							 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
							 		 group by person_id)  orfz11e_log,
							 (select person_id, min(log.ack_date) as vista_ack_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
							 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
							 		           and log.ack_date >=
							 		           	(select min(elg_vr_h.record_modified_date)
											from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
											where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
												  and elg_vr_h.person_id=log.person_id)
							 		 group by person_id)  oruz11s_log
					   where en.person_id = p.person_id(+) and
					   		 p.vpid_value = pt.vpid_value and
					   		 en.person_id = appl.person_id(+) and
					   		 en.person_id = elg_verify.person_id(+) and
							 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
					   		 en.person_id = qryz11m_log.person_id(+) and
							 en.person_id = orfz11e_log.person_id(+) and
							 en.person_id = oruz11s_log.person_id(+) and
							 p.preferred_site_id = facility.id and
							 facility.visn_id = visn.id(+) and
							 ('all' = :is_all or facility.id in (:facilities)) and
							 (('Y' = :incompleteFlg and vista_ack_date is null) OR vista_ack_date is not null) and
							 en.change_effective_date >= :startDate and
						     	 en.change_effective_date < :endDate + 1
				)
				where
					('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS >= :days)
					OR
					('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS >= :days)
					OR
					('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS >= :days)
					OR
					('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  >= :days)
					OR
					('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  >= :days)
					OR
					('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS is null)
					OR
					('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS is null)
					OR
					('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS is null)
					OR
					('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  is null)
					OR
					('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  is null)
				group by grouping sets(	(VISN_NAME, FACILITY_NAME, NAME, SSN,APPLICATION_DATE,Z07_RECEIVED_DATE,VBA_QUERY_DATE,VBA_RESP_DATE,ELG_VERIFIED_DATE,ENROLLMENT_DATE,VISTA_ACK_DATE,TOTAL_PROCESSING_TIME),
					  	 	(VISN_NAME, FACILITY_NAME),
							(VISN_NAME), ())
				order by lpad(trim(substr(VISN_NAME, 5)), 2, '0'), FACILITY_NAME, NAME, SSN
		]]>
		<return-scalar column="GROUP_ID" type="string"/>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="FACILITY_NAME" type="string"/>
		<return-scalar column="NAME" type="string"/>
		<return-scalar column="SSN" type="string"/>
		<return-scalar column="APPLICATION_DATE" type="timestamp"/>
		<return-scalar column="Z07_RECEIVED_DATE" type="timestamp"/>
		<return-scalar column="VBA_QUERY_DATE" type="timestamp"/>
		<return-scalar column="VBA_RESP_DATE" type="timestamp"/>
		<return-scalar column="ELG_VERIFIED_DATE" type="timestamp"/>
		<return-scalar column="ENROLLMENT_DATE" type="timestamp"/>
		<return-scalar column="VISTA_ACK_DATE" type="timestamp"/>
		<return-scalar column="TOTAL_PROCESSING_TIME" type="big_decimal"/>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>

	<!--
		Query statement for a summary view of a "New Enrollment Processing Time" report.
	-->
	<sql-query name="QM3.Detail.site">
		<![CDATA[
			select
				decode(grouping_id(FACILITY_NAME,SSN),0,'DETAIL',1,'SITE_TOTAL',3,'GRAND_TOTAL') as GROUP_ID,
				FACILITY_NAME, NAME, SSN,
				APPLICATION_DATE,
				Z07_RECEIVED_DATE,
				VBA_QUERY_DATE,
				VBA_RESP_DATE,
				ELG_VERIFIED_DATE,
				ENROLLMENT_DATE,
				VISTA_ACK_DATE,
				TOTAL_PROCESSING_TIME,
				count(z07_received_date) as APPLICATIONS_COUNT,
				round(avg(VBA_QUERY_SENT_DAYS)) as AVG_VBA_QUERY_SENT_DAYS,
				round(avg(VBA_RESPONSE_DAYS)) as AVG_VBA_RESPONSE_DAYS,
				round(avg(ELG_VERIFIED_DAYS)) as AVG_ELG_VERIFIED_DAYS,
				round(avg(ENROLLMENT_DAYS)) as AVG_ENROLLMENT_DAYS,
				round(avg(ACK_RECEIVED_DAYS)) as AVG_ACK_RECEIVED_DAYS,
				round(avg(TOTAL_PROCESSING_TIME)) as AVG_TOTAL_PROCESSING_TIME
				from
				(
				select facility.name FACILITY_NAME,
					   pt.last_name || ', ' || pt.first_name || ' ' || substr(pt.middle_name, 1, 1) as NAME,
					   nvl2(pt.ssn ,substr(pt.ssn ,1,3)||'-'||substr(pt.ssn ,4,2)||'-'||substr(pt.ssn ,6),null) as SSN,
					   APPLICATION_DATE,
					   Z07_RECEIVED_DATE,
					   VBA_QUERY_DATE,
					   VBA_RESP_DATE,
					   ELG_VERIFIED_DATE,
					   en.record_created_date as ENROLLMENT_DATE,
					   VISTA_ACK_DATE,
					   case when (VBA_QUERY_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VBA_QUERY_DATE-Z07_RECEIVED_DATE) end as VBA_QUERY_SENT_DAYS,
					   case when (VBA_RESP_DATE-VBA_QUERY_DATE) <= 0 then 1 else ceil(VBA_RESP_DATE-VBA_QUERY_DATE) end as VBA_RESPONSE_DAYS,
					   case when (ELG_VERIFIED_DATE-VBA_RESP_DATE) <= 0  then 1 else ceil(ELG_VERIFIED_DATE-VBA_RESP_DATE) end as ELG_VERIFIED_DAYS,
					   case when (en.record_created_date-ELG_VERIFIED_DATE) <= 0   then 1 else ceil(en.record_created_date-ELG_VERIFIED_DATE) end as ENROLLMENT_DAYS,
					   case when (VISTA_ACK_DATE-en.record_created_date) <= 0 then 1 else ceil(VISTA_ACK_DATE-en.record_created_date) end as ACK_RECEIVED_DAYS,
					   case when (VISTA_ACK_DATE-Z07_RECEIVED_DATE) <= 0 then 1 else ceil(VISTA_ACK_DATE-Z07_RECEIVED_DATE) end as TOTAL_PROCESSING_TIME
					   from enrollment_determination en, person p , rpt_psim_traits pt, std_institution facility,
					   		(select person_id, min(application_date) as application_date
									from health_benefits_application_h
									group by person_id) appl,
					        (select person_id, min(elg_vr_h.record_modified_date) as elg_verified_date
									from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
									where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
									group by person_id) elg_verify,
					   		 (select person_id, min(log.record_created_date) as z07_received_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ07-E'
							 		 	   and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
							 		 group by person_id)  z07_log,
							 (select person_id, min(log.record_created_date) as vba_query_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
							 		 group by person_id)  qryz11m_log,
							 (select person_id, min(log.record_created_date) as vba_resp_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype, STD_HL7ACKNOWLEDGEMENTCODE ack_type
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
							 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
							 		 group by person_id)  orfz11e_log,
							 (select person_id, min(log.ack_date) as vista_ack_date
							 		 from hl7_transaction_log log, STD_MESSAGETYPE mtype,STD_HL7ACKNOWLEDGEMENTCODE ack_type
							 		 where log.message_transmission_type_id = mtype.id and mtype.code = 'ORUZ11-S'
							 		           and log.ack_type_id = ack_type.id and ack_type.code = 'AA'
							 		           and log.ack_date >=
							 		           	(select min(elg_vr_h.record_modified_date)
											from eligibility_verification_h elg_vr_h, std_eligibilitystatus elg_status
											where elg_vr_h.elgbty_status_type_id = elg_status.id and elg_status.code = 'V'
												  and elg_vr_h.person_id=log.person_id)
							 		 group by person_id)  oruz11s_log
					   where en.person_id = p.person_id(+) and
					   		 p.vpid_value = pt.vpid_value and
					   		 en.person_id = appl.person_id(+) and
					   		 en.person_id = elg_verify.person_id(+) and
							 en.person_id = z07_log.person_id(+) and z07_received_date is not null and
					   		 en.person_id = qryz11m_log.person_id(+) and
							 en.person_id = orfz11e_log.person_id(+) and
							 en.person_id = oruz11s_log.person_id(+) and
							 p.preferred_site_id = facility.id and
							 ('all' = :is_all or facility.id in (:facilities)) and
							 (('Y' = :incompleteFlg and vista_ack_date is null)  OR vista_ack_date is not null) and
							 en.change_effective_date >= :startDate and
							 en.change_effective_date < :endDate + 1
				)
				where
					  	('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS >= :days)
						OR
						('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS >= :days)
						OR
						('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS >= :days)
						OR
						('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  >= :days)
						OR
						('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  >= :days)
						OR
						('1' in (:enrollmentStatusComponent) and VBA_QUERY_SENT_DAYS is null)
						OR
						('2' in (:enrollmentStatusComponent) and VBA_RESPONSE_DAYS is null)
						OR
						('3' in (:enrollmentStatusComponent) and ELG_VERIFIED_DAYS is null)
						OR
						('4' in (:enrollmentStatusComponent) and ENROLLMENT_DAYS  is null)
						OR
						('5' in (:enrollmentStatusComponent) and ACK_RECEIVED_DAYS  is null)
				group by grouping sets(	(FACILITY_NAME, NAME, SSN,
					  	 		  		APPLICATION_DATE, Z07_RECEIVED_DATE, VBA_QUERY_DATE,
										VBA_RESP_DATE, ELG_VERIFIED_DATE, ENROLLMENT_DATE,
										VISTA_ACK_DATE, TOTAL_PROCESSING_TIME), (FACILITY_NAME), ())
				order by lpad(trim(substr(FACILITY_NAME, 5)), 2, '0'), NAME, SSN
		]]>
		<return-scalar column="GROUP_ID" type="string"/>
		<return-scalar column="FACILITY_NAME" type="string"/>
		<return-scalar column="NAME" type="string"/>
		<return-scalar column="SSN" type="string"/>
		<return-scalar column="APPLICATION_DATE" type="timestamp"/>
		<return-scalar column="Z07_RECEIVED_DATE" type="timestamp"/>
		<return-scalar column="VBA_QUERY_DATE" type="timestamp"/>
		<return-scalar column="VBA_RESP_DATE" type="timestamp"/>
		<return-scalar column="ELG_VERIFIED_DATE" type="timestamp"/>
		<return-scalar column="ENROLLMENT_DATE" type="timestamp"/>
		<return-scalar column="VISTA_ACK_DATE" type="timestamp"/>
		<return-scalar column="TOTAL_PROCESSING_TIME" type="big_decimal"/>
		<return-scalar column="APPLICATIONS_COUNT" type="big_decimal"/>
		<return-scalar column="AVG_VBA_QUERY_SENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_VBA_RESPONSE_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ELG_VERIFIED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ENROLLMENT_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_ACK_RECEIVED_DAYS" type="big_decimal"/>
		<return-scalar column="AVG_TOTAL_PROCESSING_TIME" type="big_decimal"/>
	</sql-query>

	<!--
		Query statement for a summary view of a "Manual Eligibility Update Report" report.
	-->
	<sql-query name="QM14.Summary">
		<![CDATA[

			select count(*) as MANUAL_ENROLLMENT_COUNT,
				   0 as DUMMY_COUNT
			from
			(
			select distinct p.person_id, modified_date, modified_by
			from person p, rpt_psim_traits pt,
				(
					select
						distinct
						person_id,
						field_name1,
						field_name2,
						field_name3,
						field_name4,
						field_name5,
						decode(split_id, 0, modified_date, prev_modified_date) as modified_date,
						decode(split_id, 0, current_modified_by, prev_modified_by) as modified_by,
						decode(split_id, 0, field_value1, prev_field_value1) as field_value1,
						decode(split_id, 0, field_value2, prev_field_value2) as field_value2,
						decode(split_id, 0, field_value3, prev_field_value3) as field_value3,
						decode(split_id, 0, field_value4, prev_field_value4) as field_value4,
						decode(split_id, 0, field_value5, prev_field_value5) as field_value5
					from
					(
						select
							split_id,
							person_id,
							modified_date,
							prev_modified_date,
							current_modified_by,
							prev_modified_by,
							field_name1, field_value1,
							field_name2, field_value2,
							field_name3, field_value3,
							field_name4, field_value4,
							field_name5, field_value5,
							decode(field_value1, null, null, prev_field_value1) as prev_field_value1,
							decode(field_value2, null, null, prev_field_value2) as prev_field_value2,
							decode(field_value3, null, null, prev_field_value3) as prev_field_value3,
							decode(field_value4, null, null, prev_field_value4) as prev_field_value4,
							decode(field_value5, null, null, prev_field_value5) as prev_field_value5
							from
							(
										select distinct person_id, record_modified_date as modified_date,
												current_modified_by,
												prev_modified_by,
											   'Prisoner Of War Indicator' as field_name1, pow_ind as field_value1,
											   null as field_name2, null as field_value2,
											   null as field_name3, null as field_value3,
											   null as field_name4, null as field_value4,
											   null as field_name5, null as field_value5,
											   prev_modified_date,
											   prev_pow_ind as prev_field_value1,
											   null as prev_field_value2,
											   null as prev_field_value3,
											   null as prev_field_value4,
											   null as prev_field_value5
											from (select pow.person_id as person_id,
														 pow.record_modified_date as record_modified_date,
														 pow_ind,
														 pow.record_modified_by as current_modified_by,
														 lag(pow.record_modified_by) over (partition by pow.person_id order by pow.record_modified_date) as prev_modified_by,
														 lag(pow.record_modified_date) over (partition by pow.person_id order by pow.record_modified_date) as prev_modified_date,
														 lag(pow_ind) over (partition by pow.person_id order by pow.record_modified_date) as prev_pow_ind
													from
														(select
														 person_id,
														 decode(transaction_type_code, 'D',  nvl2(indicator.name, '  ', ' '), nvl2( indicator.name,  indicator.name, ' ')) as pow_ind,
														 pow.record_modified_by,
														 pow.record_modified_date
														 from prisoner_of_war_registry_h pow, std_yesnocode indicator
														where pow.pow_stdyesno_id = indicator.id(+)
															    and record_modified_date >= :startDate
															    and record_modified_date < :endDate + 1
															    and prisoner_of_war_registry_h_id in (select max(prisoner_of_war_registry_h_id) from prisoner_of_war_registry_h group by person_id, record_modified_date)
														) pow
												)
											where pow_ind != prev_pow_ind and
												  current_modified_by in (select username from users) and prev_modified_by in (select username from users) and
												  trunc(record_modified_date) - trunc(prev_modified_date) <= :daysBetweenUpdates
							) data_tbl,
							(select 0 as split_id from dual
							union all
							select 1 as split_id from dual
							) split_tbl
					)

					UNION ALL

					select
						distinct
						person_id,
						field_name1,
						field_name2,
						field_name3,
						field_name4,
						field_name5,
						decode(split_id, 0, modified_date, prev_modified_date) as modified_date,
						decode(split_id, 0, current_modified_by, prev_modified_by) as modified_by,
						decode(split_id, 0, field_value1, prev_field_value1) as field_value1,
						decode(split_id, 0, field_value2, prev_field_value2) as field_value2,
						decode(split_id, 0, field_value3, prev_field_value3) as field_value3,
						decode(split_id, 0, field_value4, prev_field_value4) as field_value4,
						decode(split_id, 0, field_value5, prev_field_value5) as field_value5
					from
					(
						select
							split_id,
							person_id,
							modified_date,
							prev_modified_date,
							current_modified_by,
							prev_modified_by,
							field_name1, field_value1,
							field_name2, field_value2,
							field_name3, field_value3,
							field_name4, field_value4,
							field_name5, field_value5,
							decode(field_value1, null, null, prev_field_value1) as prev_field_value1,
							decode(field_value2, null, null, prev_field_value2) as prev_field_value2,
							decode(field_value3, null, null, prev_field_value3) as prev_field_value3,
							decode(field_value4, null, null, prev_field_value4) as prev_field_value4,
							decode(field_value5, null, null, prev_field_value5) as prev_field_value5
							from
							(
									select distinct person_id, record_modified_date as modified_date,
												current_modified_by,
												prev_modified_by,
											   'Purple Heart Indicator' as field_name1, decode(purple_heart_ind,'1','Yes','0','No',purple_heart_ind)  as field_value1,
											   'Purple Heart Status' as field_name2, ph_status as field_value2,
											   null as field_name3, null as field_value3,
											   null as field_name4, null as field_value4,
											   null as field_name5, null as field_value5,
											   prev_modified_date,
											   decode(prev_purple_heart_ind,'1','Yes','0','No',prev_purple_heart_ind)  as prev_field_value1,
											   prev_ph_status as prev_field_value2,
											   null as prev_field_value3,
											   null as prev_field_value4,
											   null as prev_field_value5
											   from (select ph.person_id as person_id,
														 record_modified_date,
														 purple_heart_ind,
														 ph_status,
														 record_modified_by as current_modified_by,
														 lag(ph.record_modified_by) over (partition by ph.person_id order by ph.record_modified_date) as prev_modified_by,
														 lag(ph.record_modified_date) over (partition by ph.person_id order by ph.record_modified_date) as prev_modified_date,
														 lag(ph.purple_heart_ind) over (partition by ph.person_id order by ph.record_modified_date) as prev_purple_heart_ind,
														 lag(ph_status) over (partition by ph.person_id order by ph.record_modified_date) as prev_ph_status
													from
														(select
														 ph.person_id as person_id,
														 ph.record_modified_date as record_modified_date,
														 decode(transaction_type_code, 'D', nvl2(purple_heart_ind, '  ', ' '), nvl2(purple_heart_ind, purple_heart_ind, ' ')) as purple_heart_ind,
														 ph.record_modified_by as record_modified_by,
														 decode(transaction_type_code, 'D',  nvl2(phstatus.description, '  ', ' '), nvl2(phstatus.description, phstatus.description, ' ')) as ph_status
														 from purple_heart_registry_h ph, ph_status_type phstatus
														where ph.record_modified_date >= :startDate
															    and ph.record_modified_date < :endDate + 1
															    and  ph.ph_status_type_id = phstatus.id(+)
															    and purple_heart_registry_h_id in (select max(purple_heart_registry_h_id) from purple_heart_registry_h group by person_id, record_modified_date)
														 ) ph
													)
										where (purple_heart_ind != prev_purple_heart_ind or ph_status != prev_ph_status) and
											   current_modified_by in (select username from users) and prev_modified_by in (select username from users) and
											   trunc(record_modified_date) - trunc(prev_modified_date) <= :daysBetweenUpdates
							) data_tbl,
							(select 0 as split_id from dual
							union all
							select 1 as split_id from dual
							) split_tbl
					)

					UNION ALL

					select
						distinct
						person_id,
						field_name1,
						field_name2,
						field_name3,
						field_name4,
						field_name5,
						decode(split_id, 0, modified_date, prev_modified_date) as modified_date,
						decode(split_id, 0, current_modified_by, prev_modified_by) as modified_by,
						decode(split_id, 0, field_value1, prev_field_value1) as field_value1,
						decode(split_id, 0, field_value2, prev_field_value2) as field_value2,
						decode(split_id, 0, field_value3, prev_field_value3) as field_value3,
						decode(split_id, 0, field_value4, prev_field_value4) as field_value4,
						decode(split_id, 0, field_value5, prev_field_value5) as field_value5
					from
					(
						select
							split_id,
							person_id,
							modified_date,
							prev_modified_date,
							current_modified_by,
							prev_modified_by,
							field_name1, field_value1,
							field_name2, field_value2,
							field_name3, field_value3,
							field_name4, field_value4,
							field_name5, field_value5,
							decode(field_value1, null, null, prev_field_value1) as prev_field_value1,
							decode(field_value2, null, null, prev_field_value2) as prev_field_value2,
							decode(field_value3, null, null, prev_field_value3) as prev_field_value3,
							decode(field_value4, null, null, prev_field_value4) as prev_field_value4,
							decode(field_value5, null, null, prev_field_value5) as prev_field_value5
							from
							(
										select 	    person_id,
												    record_modified_date as modified_date,
												    current_modified_by,
												    prev_modified_by,
												   'SC Indicator' as field_name1,  decode(current_sc_ind,'1','Yes','0','No',current_sc_ind)  as field_value1,
												   'SC Percentage' as field_name2,  current_sc_combined_percent  as field_value2,
												   'P & T Indicator' as field_name3, decode(current_sc_p_and_t_ind,'1','Yes','0','No',current_sc_p_and_t_ind) as field_value3,
												   'Unemployable Indicator' as field_name4, decode(current_unemployable_ind,'1','Yes','0','No',current_unemployable_ind) as field_value4,
												   null as field_name5, null as field_value5,
												   prev_modified_date,
												   decode(prev_sc_ind,'1','Yes','0','No',prev_sc_ind)  as prev_field_value1,
												   prev_sc_combined_percent as prev_field_value2,
												   decode(prev_sc_p_and_t_ind,'1','Yes','0','No',prev_sc_p_and_t_ind)  as prev_field_value3,
												   decode(prev_unemployable_ind,'1','Yes','0','No',prev_unemployable_ind) as prev_field_value4,
												   null as prev_field_value5
												   from (select sc.person_id as person_id,
																sc.record_modified_date as record_modified_date,
																sc.service_connected_ind as current_sc_ind,
																sc.sc_combined_percent as current_sc_combined_percent,
																sc.sc_permanent_and_total_ind as current_sc_p_and_t_ind,
																sc.unemployable_indicator as current_unemployable_ind,
																sc.record_modified_by as current_modified_by,
																lag(sc.record_modified_by) over (partition by sc.person_id order by sc.record_modified_date) as prev_modified_by,
																lag(sc.record_modified_date) over (partition by sc.person_id order by sc.record_modified_date) as prev_modified_date,
																lag(sc.service_connected_ind) over (partition by sc.person_id order by sc.record_modified_date) as prev_sc_ind,
																lag(sc.sc_combined_percent) over (partition by sc.person_id order by sc.record_modified_date) as prev_sc_combined_percent,
																lag(sc.sc_permanent_and_total_ind) over (partition by sc.person_id order by sc.record_modified_date) as prev_sc_p_and_t_ind,
																lag(sc.unemployable_indicator) over (partition by sc.person_id order by sc.record_modified_date) as prev_unemployable_ind
																from
																	(select
																			person_id,
																			decode(transaction_type_code, 'D', nvl2(service_connected_ind, '  ', ' '), nvl2(service_connected_ind, to_char(service_connected_ind), ' ')) as service_connected_ind,
																			decode(transaction_type_code, 'D', nvl2(sc_combined_percent, '  ', ' '), nvl2(sc_combined_percent, to_char(sc_combined_percent), ' ')) as sc_combined_percent,
																			decode(transaction_type_code, 'D', nvl2(sc_permanent_and_total_ind, '  ', ' '), nvl2(sc_permanent_and_total_ind, to_char(sc_permanent_and_total_ind), ' ')) as sc_permanent_and_total_ind,
																			decode(transaction_type_code, 'D', nvl2(unemployable_indicator, '  ', ' '), nvl2(unemployable_indicator, to_char(unemployable_indicator), ' ')) as unemployable_indicator,
																			record_modified_date,
																			record_modified_by
																			from service_connected_h
																			where record_modified_date >= :startDate
																				    and record_modified_date < :endDate + 1
																				    and service_connected_h_id in (select max(service_connected_h_id) from service_connected_h group by person_id, record_modified_date)
																	) sc
														)
											where (current_sc_combined_percent != prev_sc_combined_percent or current_sc_ind != prev_sc_ind or current_sc_p_and_t_ind != prev_sc_p_and_t_ind or current_unemployable_ind != prev_unemployable_ind) and
												  current_modified_by in (select username from users) and prev_modified_by in (select username from users) and
												  trunc(record_modified_date) - trunc(prev_modified_date) <= :daysBetweenUpdates
							) data_tbl,
							(select 0 as split_id from dual
							union all
							select 1 as split_id from dual
							) split_tbl
					)
				) data
				where p.person_id = data.person_id and
					p.vpid_value = pt.vpid_value
				)

		]]>
		<return-scalar column="MANUAL_ENROLLMENT_COUNT" type="integer"/>
		<return-scalar column="DUMMY_COUNT" type="integer"/>
	</sql-query>

	<!--
		Query statement for a summary view of a "Manual Eligibility Update Report" report.
	-->
	<sql-query name="QM14.Detailed">
		<![CDATA[

		select pt.last_name || ', ' || pt.first_name || ' ' || substr(pt.middle_name, 1, 1) as NAME,
				  nvl2(pt.ssn ,substr(pt.ssn ,1,3)||'-'||substr(pt.ssn ,4,2)||'-'||substr(pt.ssn ,6),null) as SSN,
				  data.modified_date as UPDATED_DATE,
				  (select usr.full_name from users usr where usr.username=modified_by) as UPDATED_BY,
				   FIELD_NAME1, FIELD_VALUE1,
				   FIELD_NAME2, FIELD_VALUE2,
				   FIELD_NAME3, FIELD_VALUE3,
				   FIELD_NAME4, FIELD_VALUE4,
				   FIELD_NAME5, FIELD_VALUE5
			 from person p, rpt_psim_traits pt,
			(
				select
					distinct
					'tbl1' as source_table,
					person_id,
					field_name1,
					field_name2,
					field_name3,
					field_name4,
					field_name5,
					decode(split_id, 0, modified_date, prev_modified_date) as modified_date,
					decode(split_id, 0, current_modified_by, prev_modified_by) as modified_by,
					decode(split_id, 0, field_value1, prev_field_value1) as field_value1,
					decode(split_id, 0, field_value2, prev_field_value2) as field_value2,
					decode(split_id, 0, field_value3, prev_field_value3) as field_value3,
					decode(split_id, 0, field_value4, prev_field_value4) as field_value4,
					decode(split_id, 0, field_value5, prev_field_value5) as field_value5
				from
				(
					select
						split_id,
						person_id,
						modified_date,
						prev_modified_date,
						current_modified_by,
						prev_modified_by,
						field_name1, field_value1,
						field_name2, field_value2,
						field_name3, field_value3,
						field_name4, field_value4,
						field_name5, field_value5,
						decode(field_value1, null, null, prev_field_value1) as prev_field_value1,
						decode(field_value2, null, null, prev_field_value2) as prev_field_value2,
						decode(field_value3, null, null, prev_field_value3) as prev_field_value3,
						decode(field_value4, null, null, prev_field_value4) as prev_field_value4,
						decode(field_value5, null, null, prev_field_value5) as prev_field_value5
						from
						(
									select distinct person_id, record_modified_date as modified_date,
											current_modified_by,
											prev_modified_by,
										   'Prisoner Of War Indicator' as field_name1, pow_ind as field_value1,
										   null as field_name2, null as field_value2,
										   null as field_name3, null as field_value3,
										   null as field_name4, null as field_value4,
										   null as field_name5, null as field_value5,
										   prev_modified_date,
										   prev_pow_ind as prev_field_value1,
										   null as prev_field_value2,
										   null as prev_field_value3,
										   null as prev_field_value4,
										   null as prev_field_value5
										from (select pow.person_id as person_id,
													 pow.record_modified_date as record_modified_date,
													 pow_ind,
													 pow.record_modified_by as current_modified_by,
													 lag(pow.record_modified_by) over (partition by pow.person_id order by pow.record_modified_date) as prev_modified_by,
													 lag(pow.record_modified_date) over (partition by pow.person_id order by pow.record_modified_date) as prev_modified_date,
													 lag(pow_ind) over (partition by pow.person_id order by pow.record_modified_date) as prev_pow_ind
												from
													(select
													 person_id,
													 decode(transaction_type_code, 'D',  nvl2(indicator.name, '  ', ' '), nvl2( indicator.name,  indicator.name, ' ')) as pow_ind,
													 pow.record_modified_by,
													 pow.record_modified_date
													 from prisoner_of_war_registry_h pow, std_yesnocode indicator
													where pow.pow_stdyesno_id = indicator.id(+)
														    and record_modified_date >= :startDate
														    and record_modified_date < :endDate + 1
														    and prisoner_of_war_registry_h_id in (select max(prisoner_of_war_registry_h_id) from prisoner_of_war_registry_h group by person_id, record_modified_date)
													) pow
											)
										where pow_ind != prev_pow_ind and
											  current_modified_by in (select username from users) and prev_modified_by in (select username from users) and
											  trunc(record_modified_date) - trunc(prev_modified_date) <= :daysBetweenUpdates
						) data_tbl,
						(select 0 as split_id from dual
						union all
						select 1 as split_id from dual
						) split_tbl
				)

				UNION ALL

				select
					distinct
					'tbl2' as source_table,
					person_id,
					field_name1,
					field_name2,
					field_name3,
					field_name4,
					field_name5,
					decode(split_id, 0, modified_date, prev_modified_date) as modified_date,
					decode(split_id, 0, current_modified_by, prev_modified_by) as modified_by,
					decode(split_id, 0, field_value1, prev_field_value1) as field_value1,
					decode(split_id, 0, field_value2, prev_field_value2) as field_value2,
					decode(split_id, 0, field_value3, prev_field_value3) as field_value3,
					decode(split_id, 0, field_value4, prev_field_value4) as field_value4,
					decode(split_id, 0, field_value5, prev_field_value5) as field_value5
				from
				(
					select
						split_id,
						person_id,
						modified_date,
						prev_modified_date,
						current_modified_by,
						prev_modified_by,
						field_name1, field_value1,
						field_name2, field_value2,
						field_name3, field_value3,
						field_name4, field_value4,
						field_name5, field_value5,
						decode(field_value1, null, null, prev_field_value1) as prev_field_value1,
						decode(field_value2, null, null, prev_field_value2) as prev_field_value2,
						decode(field_value3, null, null, prev_field_value3) as prev_field_value3,
						decode(field_value4, null, null, prev_field_value4) as prev_field_value4,
						decode(field_value5, null, null, prev_field_value5) as prev_field_value5
						from
						(
								select distinct person_id, record_modified_date as modified_date,
											current_modified_by,
											prev_modified_by,
										   'Purple Heart Indicator' as field_name1, decode(purple_heart_ind,'1','Yes','0','No',purple_heart_ind)  as field_value1,
										   'Purple Heart Status' as field_name2, ph_status as field_value2,
										   null as field_name3, null as field_value3,
										   null as field_name4, null as field_value4,
										   null as field_name5, null as field_value5,
										   prev_modified_date,
										   decode(prev_purple_heart_ind,'1','Yes','0','No',prev_purple_heart_ind)  as prev_field_value1,
										   prev_ph_status as prev_field_value2,
										   null as prev_field_value3,
										   null as prev_field_value4,
										   null as prev_field_value5
										   from (select ph.person_id as person_id,
													 record_modified_date,
													 purple_heart_ind,
													 ph_status,
													 record_modified_by as current_modified_by,
													 lag(ph.record_modified_by) over (partition by ph.person_id order by ph.record_modified_date) as prev_modified_by,
													 lag(ph.record_modified_date) over (partition by ph.person_id order by ph.record_modified_date) as prev_modified_date,
													 lag(ph.purple_heart_ind) over (partition by ph.person_id order by ph.record_modified_date) as prev_purple_heart_ind,
													 lag(ph_status) over (partition by ph.person_id order by ph.record_modified_date) as prev_ph_status
												from
													(select
													 ph.person_id as person_id,
													 ph.record_modified_date as record_modified_date,
													 decode(transaction_type_code, 'D', nvl2(purple_heart_ind, '  ', ' '), nvl2(purple_heart_ind, purple_heart_ind, ' ')) as purple_heart_ind,
													 ph.record_modified_by as record_modified_by,
													 decode(transaction_type_code, 'D',  nvl2(phstatus.description, '  ', ' '), nvl2(phstatus.description, phstatus.description, ' ')) as ph_status
													 from purple_heart_registry_h ph, ph_status_type phstatus
													where ph.record_modified_date >= :startDate
														    and ph.record_modified_date < :endDate + 1
														    and  ph.ph_status_type_id = phstatus.id(+)
														    and purple_heart_registry_h_id in (select max(purple_heart_registry_h_id) from purple_heart_registry_h group by person_id, record_modified_date)
													 ) ph
												)
									where (purple_heart_ind != prev_purple_heart_ind or ph_status != prev_ph_status) and
										   current_modified_by in (select username from users) and prev_modified_by in (select username from users) and
										   trunc(record_modified_date) - trunc(prev_modified_date) <= :daysBetweenUpdates
						) data_tbl,
						(select 0 as split_id from dual
						union all
						select 1 as split_id from dual
						) split_tbl
				)

				UNION ALL

				select
					distinct
			        'tbl3' as source_table,
					person_id,
					field_name1,
					field_name2,
					field_name3,
					field_name4,
					field_name5,
					decode(split_id, 0, modified_date, prev_modified_date) as modified_date,
					decode(split_id, 0, current_modified_by, prev_modified_by) as modified_by,
					decode(split_id, 0, field_value1, prev_field_value1) as field_value1,
					decode(split_id, 0, field_value2, prev_field_value2) as field_value2,
					decode(split_id, 0, field_value3, prev_field_value3) as field_value3,
					decode(split_id, 0, field_value4, prev_field_value4) as field_value4,
					decode(split_id, 0, field_value5, prev_field_value5) as field_value5
				from
				(
					select
						split_id,
						person_id,
						modified_date,
						prev_modified_date,
						current_modified_by,
						prev_modified_by,
						field_name1, field_value1,
						field_name2, field_value2,
						field_name3, field_value3,
						field_name4, field_value4,
						field_name5, field_value5,
						decode(field_value1, null, null, prev_field_value1) as prev_field_value1,
						decode(field_value2, null, null, prev_field_value2) as prev_field_value2,
						decode(field_value3, null, null, prev_field_value3) as prev_field_value3,
						decode(field_value4, null, null, prev_field_value4) as prev_field_value4,
						decode(field_value5, null, null, prev_field_value5) as prev_field_value5
						from
						(
									select 	    person_id,
											    record_modified_date as modified_date,
											    current_modified_by,
											    prev_modified_by,
											   'SC Indicator' as field_name1,  decode(current_sc_ind,'1','Yes','0','No',current_sc_ind)  as field_value1,
											   'SC Percentage' as field_name2,  current_sc_combined_percent  as field_value2,
											   'P & T Indicator' as field_name3, decode(current_sc_p_and_t_ind,'1','Yes','0','No',current_sc_p_and_t_ind) as field_value3,
											   'Unemployable Indicator' as field_name4, decode(current_unemployable_ind,'1','Yes','0','No',current_unemployable_ind) as field_value4,
											   null as field_name5, null as field_value5,
											   prev_modified_date,
											   decode(prev_sc_ind,'1','Yes','0','No',prev_sc_ind)  as prev_field_value1,
											   prev_sc_combined_percent as prev_field_value2,
											   decode(prev_sc_p_and_t_ind,'1','Yes','0','No',prev_sc_p_and_t_ind)  as prev_field_value3,
											   decode(prev_unemployable_ind,'1','Yes','0','No',prev_unemployable_ind) as prev_field_value4,
											   null as prev_field_value5
											   from (select sc.person_id as person_id,
															sc.record_modified_date as record_modified_date,
															sc.service_connected_ind as current_sc_ind,
															sc.sc_combined_percent as current_sc_combined_percent,
															sc.sc_permanent_and_total_ind as current_sc_p_and_t_ind,
															sc.unemployable_indicator as current_unemployable_ind,
															sc.record_modified_by as current_modified_by,
															lag(sc.record_modified_by) over (partition by sc.person_id order by sc.record_modified_date) as prev_modified_by,
															lag(sc.record_modified_date) over (partition by sc.person_id order by sc.record_modified_date) as prev_modified_date,
															lag(sc.service_connected_ind) over (partition by sc.person_id order by sc.record_modified_date) as prev_sc_ind,
															lag(sc.sc_combined_percent) over (partition by sc.person_id order by sc.record_modified_date) as prev_sc_combined_percent,
															lag(sc.sc_permanent_and_total_ind) over (partition by sc.person_id order by sc.record_modified_date) as prev_sc_p_and_t_ind,
															lag(sc.unemployable_indicator) over (partition by sc.person_id order by sc.record_modified_date) as prev_unemployable_ind
															from
																(select
																		person_id,
																		decode(transaction_type_code, 'D', nvl2(service_connected_ind, '  ', ' '), nvl2(service_connected_ind, to_char(service_connected_ind), ' ')) as service_connected_ind,
																		decode(transaction_type_code, 'D', nvl2(sc_combined_percent, '  ', ' '), nvl2(sc_combined_percent, to_char(sc_combined_percent), ' ')) as sc_combined_percent,
																		decode(transaction_type_code, 'D', nvl2(sc_permanent_and_total_ind, '  ', ' '), nvl2(sc_permanent_and_total_ind, to_char(sc_permanent_and_total_ind), ' ')) as sc_permanent_and_total_ind,
																		decode(transaction_type_code, 'D', nvl2(unemployable_indicator, '  ', ' '), nvl2(unemployable_indicator, to_char(unemployable_indicator), ' ')) as unemployable_indicator,
																		record_modified_date,
																		record_modified_by
																		from service_connected_h
																		where record_modified_date >= :startDate
																			    and record_modified_date < :endDate + 1
																			    and service_connected_h_id in (select max(service_connected_h_id) from service_connected_h group by person_id, record_modified_date)
																) sc
													)
										where (current_sc_combined_percent != prev_sc_combined_percent or current_sc_ind != prev_sc_ind or current_sc_p_and_t_ind != prev_sc_p_and_t_ind or current_unemployable_ind != prev_unemployable_ind) and
											  current_modified_by in (select username from users) and prev_modified_by in (select username from users) and
											  trunc(record_modified_date) - trunc(prev_modified_date) <= :daysBetweenUpdates
						) data_tbl,
						(select 0 as split_id from dual
						union all
						select 1 as split_id from dual
						) split_tbl
				)
			) data
			where p.person_id = data.person_id and
				p.vpid_value = pt.vpid_value
			order by NAME,SSN,UPDATED_DATE desc, UPDATED_BY, source_table

		]]>
		<return-scalar column="NAME" type="string"/>
		<return-scalar column="SSN" type="string"/>
		<return-scalar column="UPDATED_DATE" type="timestamp"/>
		<return-scalar column="UPDATED_BY" type="string"/>
		<return-scalar column="FIELD_NAME1" type="string"/>
		<return-scalar column="FIELD_VALUE1" type="string"/>
		<return-scalar column="FIELD_NAME2" type="string"/>
		<return-scalar column="FIELD_VALUE2" type="string"/>
		<return-scalar column="FIELD_NAME3" type="string"/>
		<return-scalar column="FIELD_VALUE3" type="string"/>
		<return-scalar column="FIELD_NAME4" type="string"/>
		<return-scalar column="FIELD_VALUE4" type="string"/>
		<return-scalar column="FIELD_NAME5" type="string"/>
		<return-scalar column="FIELD_VALUE5" type="string"/>
	</sql-query>

	<!--
		Query statement for address updates summary report - QM21.
	-->
    <sql-query name="addressUpdatesReport.Summary">
		<![CDATA[

		select
			count(*) as total_records,
			round(avg(case when(days <= :addressUpdateDays) then days else null end)) as avg_days
			--new_id, old_id, new_modified_date, old_modified_date
		from
		(
			select
				distinct new_id, old_id, new_modified_date, old_modified_date,
				ceil(new_modified_date - old_modified_date) as days
			from
				person,
				RPT_PSIM_TRAITS,
				(
				select
					*
				from
				(
					select
						person_id, std_addresstype_id,
						update_days,
						f_mod_date,
						t_mod_date,
						addr_update_count,
						address_h_id as new_id,
						lag(address_h_id)   over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as old_id,
						RECORD_MODIFIED_DATE as NEW_MODIFIED_DATE,
						lag(RECORD_MODIFIED_DATE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_MODIFIED_DATE,

						transaction_type_code as new_hflag,
						ADDRESS_LINE1 as NEW_LINE1,
						ADDRESS_LINE2 as NEW_LINE2,
						ADDRESS_LINE3 as NEW_LINE3,
						CITY as NEW_CITY,
						PROVINCE_CODE as NEW_PROVINCE_CODE,
						STATE_CODE as NEW_STATE_CODE,
						POSTAL_CODE as NEW_POSTAL_CODE,
						ZIP_CODE as NEW_ZIP_CODE,
						ZIP_PLUS_4  as NEW_ZIP_PLUS_4,
						COUNTRY_CODE as NEW_COUNTRY_CODE,
						std_institution_id as NEW_ADDR_CHG_SRC_TYPE_ID,

						lag(transaction_type_code) over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE) as old_hflag,
						lag(ADDRESS_LINE1)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE) as OLD_LINE1,
						lag(ADDRESS_LINE2)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_LINE2,
						lag(ADDRESS_LINE3)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_LINE3,
						lag(CITY)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_CITY,
						lag(PROVINCE_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_PROVINCE_CODE,
						lag(STATE_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_STATE_CODE,
						lag(POSTAL_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_POSTAL_CODE,
						lag(ZIP_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_ZIP_CODE,
						lag(ZIP_PLUS_4)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)   as OLD_ZIP_PLUS_4,
						lag(COUNTRY_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_COUNTRY_CODE,
						lag(std_institution_id)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_ADDR_CHG_SRC_TYPE_ID

					from
					(
						select
							f_mod_date,
							t_mod_date,
							update_days,
							decode(t_mod_date,
								   to_date('01/01/1900', 'MM/DD/YYYY'),
								  count(*) over (partition by address_h.person_id, address_h.std_addresstype_id, f_mod_date, t_mod_date, update_days),
								  count(*) over (partition by address_h.person_id, address_h.std_addresstype_id, f_mod_date, t_mod_date, update_days) - 1
								  ) as addr_update_count,
							address_h.*
						from address_h,
							 (select max(address_h_id) as address_h_id from address_h  group by person_id, std_addresstype_id, record_modified_date) max_address_h,
							(
									select
										person_id, std_addresstype_id,
										f_mod_date,
										nvl
										(
											(
											select max(i_addr_h.record_modified_date) as prev_modified_date
											from address_h i_addr_h
											where
												i_addr_h.person_id = addr_h_1.person_id and i_addr_h.std_addresstype_id = addr_h_1.std_addresstype_id
												and i_addr_h.record_modified_date < t_mod_date
											),
											to_date('01/01/1900', 'MM/DD/YYYY')
										) as t_mod_date,
										update_days
										from
											(
												select
													person_id, std_addresstype_id,
													max(f_mod_date) as f_mod_date,
													t_mod_date,
													update_days
													from
													(
														select
															person_id, std_addresstype_id,
															f_mod_date,
															min(t_mod_date) as t_mod_date,
															 update_days
														from
														(
															select
																	f_addr_h.person_id, f_addr_h.std_addresstype_id,
																	--f_addr_h.address_h_id as f_addr_h_id,
																	 --t_addr_h.address_h_id as t_addr_h_id,
																	 f_addr_h.record_modified_date as f_mod_date,
																	 t_addr_h.record_modified_date as t_mod_date,
																	 ceil(f_addr_h.record_modified_date -  t_addr_h.record_modified_date) as update_days
															from
															(select max(address_h_id) as address_h_id, person_id, std_addresstype_id, record_modified_date from address_h group by person_id, std_addresstype_id, record_modified_date) f_addr_h,
															(select max(address_h_id) as address_h_id, person_id, std_addresstype_id, record_modified_date from address_h group by person_id, std_addresstype_id, record_modified_date) t_addr_h
															where
																f_addr_h.RECORD_MODIFIED_DATE < :endDate + 1
																and f_addr_h.RECORD_MODIFIED_DATE >= :startDate
																and t_addr_h.RECORD_MODIFIED_DATE < :endDate + 1
																and t_addr_h.RECORD_MODIFIED_DATE >= :startDate
																and f_addr_h.person_id = t_addr_h.person_id
																and f_addr_h.std_addresstype_id = t_addr_h.std_addresstype_id
																and f_addr_h.record_modified_date >  t_addr_h.record_modified_date
																and (f_addr_h.record_modified_date -  t_addr_h.record_modified_date) <= :addressUpdateDays
														)
														group by person_id, std_addresstype_id, update_days, f_mod_date
													)
													group by person_id, std_addresstype_id, update_days, t_mod_date
											) addr_h_1
							) band_addr_h
						where
							address_h.address_h_id = max_address_h.address_h_id
							and address_h.person_id = band_addr_h.person_id
							and address_h.std_addresstype_id = band_addr_h.std_addresstype_id
							and address_h.record_modified_date <= band_addr_h.f_mod_date
							and address_h.record_modified_date >= band_addr_h.t_mod_date
					)
				)
				where  old_id is not null or (old_id is null and t_mod_date = to_date('01/01/1900', 'MM/DD/YYYY'))
				) addr_updates_tbl
				where
					person.person_id = addr_updates_tbl.PERSON_ID
					and person.vpid_value = RPT_PSIM_TRAITS.vpid_value
		)

		]]>
		<return-scalar column="total_records" type="integer"/>
		<return-scalar column="avg_days" type="integer"/>
    </sql-query>

	<!--
		Query statement for address updates detail report - QM21.
	-->
    <sql-query name="addressUpdatesReport.Detail">
		<![CDATA[

		select
			/*
			person_id, std_addresstype_id,
			new_id, old_id,
			f_mod_date,
			t_mod_date,
			NEW_ADDR_MOD_DATE,
			OLD_ADDR_MOD_DATE,
			new_hflag,
			old_hflag,
			addr_update_count,
			*/
			   days_between_updates,
			   name,
					 nvl2(ssn ,substr(ssn ,1,3)||'-'||substr(ssn ,4,2)||'-'||substr(ssn ,6),null) ssn,
			   decode(new_hflag, 'D', 'DELETED', NEW_LINE1) as NEW_LINE1,
			   decode(new_hflag, 'D', null, NEW_LINE2) as NEW_LINE2,
			   decode(new_hflag, 'D', null, NEW_LINE3) as NEW_LINE3,
			   decode(new_hflag, 'D', null, NEW_LINE4) as NEW_LINE4,
			   decode(new_hflag, 'D', null, NEW_LINE5) as NEW_LINE5,
			   TO_CHAR(NEW_ADDR_MOD_DATE, 'MM/DD/YYYY HH:MI:SS') as NEW_ADDR_MOD_DATE,
			   (select vistaname from std_institution where id=NEW_ADDR_CHG_SRC_TYPE_ID) as NEW_ADDR_CHG_SRC_TYPE_ID,
			   decode(OLD_hflag, 'D', 'DELETED', OLD_LINE1) as OLD_LINE1,
			   decode(OLD_hflag, 'D', null, OLD_LINE2) as OLD_LINE2,
			   decode(OLD_hflag, 'D', null, OLD_LINE3) as OLD_LINE3,
			   decode(OLD_hflag, 'D', null, OLD_LINE4) as OLD_LINE4,
			   decode(OLD_hflag, 'D', null, OLD_LINE5) as OLD_LINE5,
			   TO_CHAR(OLD_ADDR_MOD_DATE, 'MM/DD/YYYY HH:MI:SS') as OLD_ADDR_MOD_DATE,
			   (select vistaname from std_institution where id=OLD_ADDR_CHG_SRC_TYPE_ID) as OLD_ADDR_CHG_SRC_TYPE_ID
		from
		(
			select
				person.person_id, std_addresstype_id,
				new_id, old_id,
				f_mod_date,
				t_mod_date,
				new_hflag,
				old_hflag,
				addr_update_count,

				update_days as days_between_updates,
				decode(RPT_PSIM_TRAITS.LAST_NAME,
					   null, 'Unknown name',
					   RPT_PSIM_TRAITS.LAST_NAME || ', ' || RPT_PSIM_TRAITS.FIRST_NAME || ' ' || substr(RPT_PSIM_TRAITS.MIDDLE_NAME, 1, 1)
					  ) as name,
				RPT_PSIM_TRAITS.SSN as SSN,
				NEW_LINE1,
				NEW_LINE2,
				NEW_LINE3,
				decode(nvl(NEW_COUNTRY_CODE, decode(NEW_PROVINCE_CODE, null, 'USA', 'NON-USA')),
						  'USA',
						  (NEW_CITY
						  || ', ' || NEW_STATE_CODE
						  || ' ' || NEW_ZIP_CODE
						  || decode(NEW_ZIP_PLUS_4, null, '', '-' || NEW_ZIP_PLUS_4)),
						  (NEW_CITY
						  || ', ' || NEW_PROVINCE_CODE
						  || ' ' || NEW_POSTAL_CODE)
						  ) as new_line4,
				  NEW_COUNTRY_CODE as new_line5,
				  NEW_MODIFIED_DATE as NEW_ADDR_MOD_DATE,
				  NEW_ADDR_CHG_SRC_TYPE_ID,

				OLD_LINE1,
				OLD_LINE2,
				OLD_LINE3,
				decode(nvl(OLD_COUNTRY_CODE, decode(OLD_PROVINCE_CODE, null, 'USA', 'NON-USA')),
						  'USA',
						  (OLD_CITY
						  || ', ' || OLD_STATE_CODE
						  || ' ' || OLD_ZIP_CODE
						  || decode(OLD_ZIP_PLUS_4, null, '', '-' || OLD_ZIP_PLUS_4)),
						  (OLD_CITY
						  || ', ' || OLD_PROVINCE_CODE
						  || ' ' || OLD_POSTAL_CODE)
						  ) as OLD_line4,
				  OLD_COUNTRY_CODE as OLD_line5,
				  OLD_MODIFIED_DATE as OLD_ADDR_MOD_DATE,
				  OLD_ADDR_CHG_SRC_TYPE_ID
			from
				person,
				RPT_PSIM_TRAITS,
				(
				select
					*
				from
				(
					select
						person_id, std_addresstype_id,
						update_days,
						f_mod_date,
						t_mod_date,
						addr_update_count,
						address_h_id as new_id,
						lag(address_h_id)   over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as old_id,
						RECORD_MODIFIED_DATE as NEW_MODIFIED_DATE,
						lag(RECORD_MODIFIED_DATE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_MODIFIED_DATE,

						transaction_type_code as new_hflag,
						ADDRESS_LINE1 as NEW_LINE1,
						ADDRESS_LINE2 as NEW_LINE2,
						ADDRESS_LINE3 as NEW_LINE3,
						CITY as NEW_CITY,
						PROVINCE_CODE as NEW_PROVINCE_CODE,
						STATE_CODE as NEW_STATE_CODE,
						POSTAL_CODE as NEW_POSTAL_CODE,
						ZIP_CODE as NEW_ZIP_CODE,
						ZIP_PLUS_4  as NEW_ZIP_PLUS_4,
						COUNTRY_CODE as NEW_COUNTRY_CODE,
						std_institution_id as NEW_ADDR_CHG_SRC_TYPE_ID,

						lag(transaction_type_code) over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE) as old_hflag,
						lag(ADDRESS_LINE1)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE) as OLD_LINE1,
						lag(ADDRESS_LINE2)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_LINE2,
						lag(ADDRESS_LINE3)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_LINE3,
						lag(CITY)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_CITY,
						lag(PROVINCE_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_PROVINCE_CODE,
						lag(STATE_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_STATE_CODE,
						lag(POSTAL_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_POSTAL_CODE,
						lag(ZIP_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_ZIP_CODE,
						lag(ZIP_PLUS_4)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)   as OLD_ZIP_PLUS_4,
						lag(COUNTRY_CODE)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_COUNTRY_CODE,
						lag(std_institution_id)  over (partition by person_id, std_addresstype_id, f_mod_date, t_mod_date, update_days order by RECORD_MODIFIED_DATE)  as OLD_ADDR_CHG_SRC_TYPE_ID

					from
					(
						select
							f_mod_date,
							t_mod_date,
							update_days,
							decode(t_mod_date,
								   to_date('01/01/1900', 'MM/DD/YYYY'),
								  count(*) over (partition by address_h.person_id, address_h.std_addresstype_id, f_mod_date, t_mod_date, update_days),
								  count(*) over (partition by address_h.person_id, address_h.std_addresstype_id, f_mod_date, t_mod_date, update_days) - 1
								  ) as addr_update_count,
							address_h.*
						from address_h,
							 (select max(address_h_id) as address_h_id from address_h  group by person_id, std_addresstype_id, record_modified_date) max_address_h,
							(
									select
										person_id, std_addresstype_id,
										f_mod_date,
										nvl
										(
											(
											select max(i_addr_h.record_modified_date) as prev_modified_date
											from address_h i_addr_h
											where
												i_addr_h.person_id = addr_h_1.person_id and i_addr_h.std_addresstype_id = addr_h_1.std_addresstype_id
												and i_addr_h.record_modified_date < t_mod_date
											),
											to_date('01/01/1900', 'MM/DD/YYYY')
										) as t_mod_date,
										update_days
										from
											(
												select
													person_id, std_addresstype_id,
													max(f_mod_date) as f_mod_date,
													t_mod_date,
													update_days
													from
													(
														select
															person_id, std_addresstype_id,
															f_mod_date,
															min(t_mod_date) as t_mod_date,
															 update_days
														from
														(
															select
																	f_addr_h.person_id, f_addr_h.std_addresstype_id,
																	--f_addr_h.address_h_id as f_addr_h_id,
																	 --t_addr_h.address_h_id as t_addr_h_id,
																	 f_addr_h.record_modified_date as f_mod_date,
																	 t_addr_h.record_modified_date as t_mod_date,
																	 ceil(f_addr_h.record_modified_date -  t_addr_h.record_modified_date) as update_days
															from
															(select max(address_h_id) as address_h_id, person_id, std_addresstype_id, record_modified_date from address_h group by person_id, std_addresstype_id, record_modified_date) f_addr_h,
															(select max(address_h_id) as address_h_id, person_id, std_addresstype_id, record_modified_date from address_h group by person_id, std_addresstype_id, record_modified_date) t_addr_h
															where
																f_addr_h.RECORD_MODIFIED_DATE < :endDate + 1
																and f_addr_h.RECORD_MODIFIED_DATE >= :startDate
																and t_addr_h.RECORD_MODIFIED_DATE < :endDate + 1
																and t_addr_h.RECORD_MODIFIED_DATE >= :startDate
																and f_addr_h.person_id = t_addr_h.person_id
																and f_addr_h.std_addresstype_id = t_addr_h.std_addresstype_id
																and f_addr_h.record_modified_date >  t_addr_h.record_modified_date
																and (f_addr_h.record_modified_date -  t_addr_h.record_modified_date) <= :addressUpdateDays
														)
														group by person_id, std_addresstype_id, update_days, f_mod_date
													)
													group by person_id, std_addresstype_id, update_days, t_mod_date
											) addr_h_1
							) band_addr_h
						where
							address_h.address_h_id = max_address_h.address_h_id
							and address_h.person_id = band_addr_h.person_id
							and address_h.std_addresstype_id = band_addr_h.std_addresstype_id
							and address_h.record_modified_date <= band_addr_h.f_mod_date
							and address_h.record_modified_date >= band_addr_h.t_mod_date
					)
				)
				where  old_id is not null or (old_id is null and t_mod_date = to_date('01/01/1900', 'MM/DD/YYYY'))
				) addr_updates_tbl
				where
					person.person_id = addr_updates_tbl.PERSON_ID
					and person.vpid_value = RPT_PSIM_TRAITS.vpid_value
		) data_tbl
		order by days_between_updates, name, person_id, std_addresstype_id, f_mod_date desc, t_mod_date desc, data_tbl.NEW_ADDR_MOD_DATE

		]]>
		<return-scalar column="days_between_updates" type="integer"/>
		<return-scalar column="name" type="string"/>
		<return-scalar column="ssn" type="string"/>
		<return-scalar column="NEW_LINE1" type="string"/>
		<return-scalar column="NEW_LINE2" type="string"/>
		<return-scalar column="NEW_LINE3" type="string"/>
		<return-scalar column="NEW_LINE4" type="string"/>
		<return-scalar column="NEW_LINE5" type="string"/>
		<return-scalar column="NEW_ADDR_CHG_SRC_TYPE_ID" type="string"/>
		<return-scalar column="NEW_ADDR_MOD_DATE" type="string"/>
		<return-scalar column="OLD_LINE1" type="string"/>
		<return-scalar column="OLD_LINE2" type="string"/>
		<return-scalar column="OLD_LINE3" type="string"/>
		<return-scalar column="OLD_LINE4" type="string"/>
		<return-scalar column="OLD_LINE5" type="string"/>
		<return-scalar column="OLD_ADDR_MOD_DATE" type="string"/>
		<return-scalar column="OLD_ADDR_CHG_SRC_TYPE_ID" type="string"/>
    </sql-query>

	<!--
		Query statement for QM23 report.
	-->
	<sql-query name="phRejectReport.Summary">
		<![CDATA[
			select STD_REJECTEDREMARKS.name as reject_reason,
			       nvl(veteran_count, 0) as veteran_count,
			       round(nvl(10000 * ratio_to_report(veteran_count) over(), 0)) as percentage
			from
			(
				select
					STD_REJECTEDREMARKS_ID,
					count(person_id) veteran_count
				from
					PURPLE_HEART_REGISTRY
				where
			        person_id is not null
					and PH_STATUS_LAST_EDIT_DATE < :endDate + 1
					and PH_STATUS_LAST_EDIT_DATE  >= :startDate
					and PURPLE_HEART_REGISTRY.PH_STATUS_TYPE_ID = (select id from PH_STATUS_TYPE where code=5)
					and STD_REJECTEDREMARKS_ID in (select STD_REJECTEDREMARKS.id from STD_REJECTEDREMARKS where STD_REJECTEDREMARKS.code  in (:internalPHRejectionRemarks))
				group by STD_REJECTEDREMARKS_ID
			) rejected_ph,
			STD_REJECTEDREMARKS
			where STD_REJECTEDREMARKS.id = STD_REJECTEDREMARKS_ID(+)
			and STD_REJECTEDREMARKS.code  in (:internalPHRejectionRemarks)
			order by nvl(veteran_count, 0) desc
		]]>
		<return-scalar column="reject_reason" type="string"/>
		<return-scalar column="veteran_count" type="integer"/>
		<return-scalar column="percentage" type="integer"/>
	</sql-query>

	<!--
		Query statement for QM23 report.
	-->
	<sql-query name="phRejectIncUserReport.Summary">
		<![CDATA[
			select nvl((select full_name from users where username=USR_STD_REJECTEDREMARKS.ASSIGNED_CONTACT_REP_USERNAME), USR_STD_REJECTEDREMARKS.ASSIGNED_CONTACT_REP_USERNAME) as ASSIGNED_USER,
			       USR_STD_REJECTEDREMARKS.name as reject_reason,
			       nvl(veteran_count, 0) as veteran_count,
			       round(nvl(10000 * ratio_to_report(veteran_count) over(partition by USR_STD_REJECTEDREMARKS.ASSIGNED_CONTACT_REP_USERNAME), 0)) as percentage
			from
			(
				select
					nvl(ASSIGNED_CONTACT_REP_USERNAME, 'User unknown') as ASSIGNED_CONTACT_REP_USERNAME,
					STD_REJECTEDREMARKS_ID,
					count(person_id) veteran_count
				from
					PURPLE_HEART_REGISTRY
				where
			        person_id is not null
					and PH_STATUS_LAST_EDIT_DATE < :endDate + 1
					and PH_STATUS_LAST_EDIT_DATE >= :startDate
					and PURPLE_HEART_REGISTRY.PH_STATUS_TYPE_ID = (select id from PH_STATUS_TYPE where code=5)
					and STD_REJECTEDREMARKS_ID in (select STD_REJECTEDREMARKS.id from STD_REJECTEDREMARKS where STD_REJECTEDREMARKS.code  in (:internalPHRejectionRemarks))
				group by ASSIGNED_CONTACT_REP_USERNAME, STD_REJECTEDREMARKS_ID
			) rejected_ph,
			(
				select
					distinct nvl(ASSIGNED_CONTACT_REP_USERNAME, 'User unknown') as ASSIGNED_CONTACT_REP_USERNAME,
					STD_REJECTEDREMARKS.ID, STD_REJECTEDREMARKS.NAME
				from
					PURPLE_HEART_REGISTRY, STD_REJECTEDREMARKS
				where
				    person_id is not null
					and PH_STATUS_LAST_EDIT_DATE < :endDate + 1
					and PH_STATUS_LAST_EDIT_DATE >= :startDate
					and PURPLE_HEART_REGISTRY.PH_STATUS_TYPE_ID = (select id from PH_STATUS_TYPE where code=5)
					and STD_REJECTEDREMARKS.code  in (:internalPHRejectionRemarks)
			) USR_STD_REJECTEDREMARKS
			where USR_STD_REJECTEDREMARKS.id = STD_REJECTEDREMARKS_ID(+)
			      and USR_STD_REJECTEDREMARKS.ASSIGNED_CONTACT_REP_USERNAME = rejected_ph.ASSIGNED_CONTACT_REP_USERNAME(+)
			order by USR_STD_REJECTEDREMARKS.ASSIGNED_CONTACT_REP_USERNAME, nvl(veteran_count, 0) desc
		]]>
		<return-scalar column="assigned_user" type="string"/>
		<return-scalar column="reject_reason" type="string"/>
		<return-scalar column="veteran_count" type="integer"/>
		<return-scalar column="percentage" type="integer"/>
	</sql-query>

	<!--
		Query statement for QM23 report.
	-->
	<sql-query name="phRejectReport.Detail">
		<![CDATA[
			select reject_reason, name,
			       nvl2(ssn,substr(ssn,1,3)||'-'||substr(ssn,4,2)||'-'||substr(ssn,6),null) ssn,
			       date_last_modified, reject_days,
				   group_count, group_id,
				   round(nvl(10000 * ratio_to_report(group_count) over(partition by group_id), 0)) as percentage
			from
			(
					select reject_reason, name, ssn, date_last_modified, reject_days,
						   count(*) as group_count,
						   grouping_id(name) as group_id
					from
					(
								select
									  STD_REJECTEDREMARKS.name as reject_reason,
									  decode(RPT_PSIM_TRAITS.LAST_NAME,
											 null, 'Unknown name',
											 RPT_PSIM_TRAITS.LAST_NAME || ', ' || RPT_PSIM_TRAITS.FIRST_NAME || ' ' || substr(RPT_PSIM_TRAITS.MIDDLE_NAME, 1, 1)
											 ) as name,
									  RPT_PSIM_TRAITS.SSN as SSN,
									  phr.PH_STATUS_LAST_EDIT_DATE as date_last_modified,
									  round(SYSDATE-phr.PH_STATUS_LAST_EDIT_DATE) as reject_days
								from
									 PURPLE_HEART_REGISTRY phr,
									 person,
									 RPT_PSIM_TRAITS,
									 STD_REJECTEDREMARKS
								where
									phr.PERSON_ID is not null
									and PH_STATUS_LAST_EDIT_DATE < :endDate + 1
									and PH_STATUS_LAST_EDIT_DATE >= :startDate
									and phr.PH_STATUS_TYPE_ID = (select id from PH_STATUS_TYPE where code=5)
									and STD_REJECTEDREMARKS.code  in (:internalPHRejectionRemarks)
									and STD_REJECTEDREMARKS.id = phr.STD_REJECTEDREMARKS_ID
									and person.person_id = phr.PERSON_ID
									and person.vpid_value = RPT_PSIM_TRAITS.vpid_value(+)
					)
					group by grouping sets((reject_reason, name, ssn, date_last_modified, reject_days), (reject_reason))
			)
			order by group_id, reject_reason, name



		]]>
		<return-scalar column="reject_reason" type="string"/>
		<return-scalar column="name" type="string"/>
		<return-scalar column="ssn" type="string"/>
		<return-scalar column="date_last_modified" type="date"/>
		<return-scalar column="reject_days" type="integer"/>
		<return-scalar column="group_count" type="integer"/>
		<return-scalar column="group_id" type="integer"/>
		<return-scalar column="percentage" type="integer"/>
	</sql-query>

	<!--
		Query statement for QM23 report.
	-->
	<sql-query name="phRejectIncUserReport.Detail">
		<![CDATA[

			select assigned_user, reject_reason, name,
			       nvl2(ssn,substr(ssn,1,3)||'-'||substr(ssn,4,2)||'-'||substr(ssn,6),null) ssn,
			       date_last_modified, reject_days,
				   group_count, group_id,
				   round(nvl(10000 * ratio_to_report(group_count) over(partition by group_id, assigned_user), 0)) as percentage
			from
			(
				select assigned_user, reject_reason, name, ssn, date_last_modified, reject_days,
					    count(*) as group_count,
					    grouping_id(assigned_user, name) as group_id
				from
				(
					select nvl((select full_name from users where username=phr.ASSIGNED_CONTACT_REP_USERNAME), 'Unknown user') as assigned_user,
						  STD_REJECTEDREMARKS.name as reject_reason,
						  decode(RPT_PSIM_TRAITS.LAST_NAME,
								null, 'Unknown name',
								RPT_PSIM_TRAITS.LAST_NAME || ', ' || RPT_PSIM_TRAITS.FIRST_NAME || ' ' || substr(RPT_PSIM_TRAITS.MIDDLE_NAME, 1, 1)
								 ) as name,
						  RPT_PSIM_TRAITS.SSN as SSN,
						  phr.PH_STATUS_LAST_EDIT_DATE as date_last_modified,
						  round(SYSDATE-phr.PH_STATUS_LAST_EDIT_DATE) as reject_days
					from
						 PURPLE_HEART_REGISTRY phr,
						 person,
						 RPT_PSIM_TRAITS,
						 STD_REJECTEDREMARKS
					where
						phr.PERSON_ID is not null
						and PH_STATUS_LAST_EDIT_DATE < :endDate + 1
						and PH_STATUS_LAST_EDIT_DATE >= :startDate
						and phr.PH_STATUS_TYPE_ID = (select id from PH_STATUS_TYPE where code=5)
						and STD_REJECTEDREMARKS.code  in (:internalPHRejectionRemarks)
						and STD_REJECTEDREMARKS.id = phr.STD_REJECTEDREMARKS_ID
						and person.person_id = phr.PERSON_ID
						and person.vpid_value = RPT_PSIM_TRAITS.vpid_value(+)
				)
				group by grouping sets((assigned_user, reject_reason, name, ssn, date_last_modified, reject_days),
									  (assigned_user, reject_reason), (reject_reason))
			)
			order by assigned_user, group_id, reject_reason, name

		]]>
		<return-scalar column="assigned_user" type="string"/>
		<return-scalar column="reject_reason" type="string"/>
		<return-scalar column="name" type="string"/>
		<return-scalar column="ssn" type="string"/>
		<return-scalar column="date_last_modified" type="date"/>
		<return-scalar column="reject_days" type="integer"/>
		<return-scalar column="group_count" type="integer"/>
		<return-scalar column="group_id" type="integer"/>
		<return-scalar column="percentage" type="integer"/>
	</sql-query>

	<!--
		Query statement for a summary view of a Bulk Registry Load Statistics (QM26) report.
	-->
	<sql-query name="QM26.registry.stats">
		<![CDATA[select stats.registry_load_statistics_id as BATCH_NUMBER,
						stats.record_created_date as LOAD_DATE,
   						decode(reg_type.code,1,'POW',2,'PH',3,'SHAD') as REGISTRY_TYPE,
   						nvl(stats.processed_count,0) as RECS_READ,
   						nvl(stats.pass_count,0) as RECS_PASS_CHECK,
        				nvl(reg_details.exc_count,0) as RECS_SAME_EPISODE,
   						nvl(stats.ambiguous_match_count,0) as RECS_SSN_ONLY_MATCH,
   						nvl(stats.insert_count,0) as RECS_ADDED,
   						nvl((stats.pass_count - stats.no_match_count),0) as PERSON_MATCHES
						from registry_load_statistics stats, std_registrytype reg_type,
    					(select REGISTRY_LOAD_STATISTICS_ID, count(1)  exc_count
           					from registry_load_exception rle, rpt_exception_type ret
							where rle.RPT_EXCEPTION_TYPE_ID = ret.id and ret.code = '2'
         					group by REGISTRY_LOAD_STATISTICS_ID) reg_details
					where stats.REGISTRY_TYPE_ID = reg_type.id and
  		  			reg_type.code in (:registryType) and
		  			stats.record_modified_date >= :startDate and
					stats.record_modified_date < :endDate + 1 and
          			stats.REGISTRY_LOAD_STATISTICS_ID = reg_details.REGISTRY_LOAD_STATISTICS_ID(+)
					order by LOAD_DATE
		]]>
		<return-scalar column="BATCH_NUMBER" type="string"/>
		<return-scalar column="LOAD_DATE" type="timestamp"/>
		<return-scalar column="REGISTRY_TYPE" type="string"/>
		<return-scalar column="RECS_READ" type="integer"/>
		<return-scalar column="RECS_PASS_CHECK" type="integer"/>
		<return-scalar column="RECS_SAME_EPISODE" type="integer"/>
		<return-scalar column="RECS_SSN_ONLY_MATCH" type="integer"/>
		<return-scalar column="RECS_ADDED" type="integer"/>
		<return-scalar column="PERSON_MATCHES" type="integer"/>
	</sql-query>

	<!--
		Query statement for a summary view of a Bulk Registry Load Exception (QM27) report.
	-->
	<sql-query name="QM27.registry.exceptions">
		<![CDATA[select rle.registry_load_statistics_id as BATCH_NUMBER,
	   			   decode(retype.name,null,'SYSTEM ERROR',substr(retype.name,1,2)) as EXP_TYPE,
				   to_char(rle.exception_txt) as EXCEPTION_RECORD
				   from registry_load_exception rle, std_rptexceptiontype retype
				   where rle.rpt_exception_type_id = retype.id(+) and
				   		 rle.registry_load_statistics_id = :batchNumber and
				   		 (('is_all' = :is_all) or
				   		  ('is_null' = :is_null and rle.rpt_exception_type_id is null) or
						  ('is_not_null' = :is_not_null and retype.code = :exceptionType))
				   order by EXP_TYPE
		]]>
		<return-scalar column="BATCH_NUMBER" type="big_decimal"/>
		<return-scalar column="EXP_TYPE" type="string"/>
		<return-scalar column="EXCEPTION_RECORD" type="string"/>
	</sql-query>

	<!--
		Query statement for a national summary view of QM4
	-->
	<sql-query name="QM4.Summary">
		<![CDATA[select cd_reason, nvl(veterans,0) veterans,
			round(nvl(100 * ratio_to_report(veterans) over(), 0)) as percentage from (
			select upper(cdr.description) cd_reason, veterans, code from
			CANCEL_DECLINE_REASON_TYPE cdr, (
				select cdr.id cdr_id, count(1) veterans
				from enrollment_determination e, STD_ENROLLMENTSTATUS es, CANCEL_DECLINE cd,
			    CANCEL_DECLINE_REASON_TYPE cdr
				where e.person_id = cd.person_id
				and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
				and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
			    and cd.CANCEL_DECLINE_REASON_TYPE_ID in (:cdReasonType)
				and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id
				group by cdr.id) cdr_list
			where cdr.id = cdr_list.cdr_id(+) and cdr.id in (:cdReasonType))
			order by cd_reason
		]]>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERANS" type="integer"/>
		<return-scalar column="PERCENTAGE" type="integer"/>
	</sql-query>
	<!--
		Query statement for a national detail view of QM4
	-->
	<sql-query name="QM4.Detailed">
		<![CDATA[
		select cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,group_id, group_code,
            sum(veterans) veterans, sum(percentage) percentage
 		from (
		select cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,group_id,
					decode(group_id,'0','Detail','1','Reason_Summary') as group_code,
					group_count as veterans,
					nvl( ratio_to_report(group_count) over(partition by VETERAN_NAME), 0) as percentage
			from(
				select cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,
				 count(*) as group_count,
				grouping_id(VETERAN_NAME ) as group_id
				from(
					select upper(cdr.description) cd_reason,
							   psim.last_name||','||psim.first_name||' '||psim.middle_name VETERAN_NAME,
							   nvl2(psim.ssn,substr(psim.ssn,1,3)||'-'||substr(psim.ssn,4,2)||'-'||substr(psim.ssn,6),null) VETERAN_SSN,
							   cd.REMARKS REMARKS
					from person p, rpt_psim_traits psim, enrollment_determination e,
					 	 STD_ENROLLMENTSTATUS es, CANCEL_DECLINE cd, CANCEL_DECLINE_REASON_TYPE cdr
					where p.vpid_value = psim.vpid_value and p.person_id = e.person_id
						and e.person_id = cd.person_id
						and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
						and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
						and cd.CANCEL_DECLINE_REASON_TYPE_ID in (:cdReasonType)
						and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id
					)
				group by grouping sets(( cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS), ( cd_reason)))
				union
                select upper(cdr.description) cd_reason,
                       null VETERAN_NAME, null VETERAN_SSN, null REMARKS,
                       1 group_id, 'Reason_Summary' group_code, 0 veterans, 0 percentage
                       from CANCEL_DECLINE_REASON_TYPE cdr	where cdr.id in (:cdReasonType)
			)
			group by cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,group_id, group_code
			order by  group_id, cd_reason, VETERAN_NAME
		]]>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERAN_NAME" type="string"/>
		<return-scalar column="VETERAN_SSN" type="string"/>
		<return-scalar column="REMARKS" type="string"/>
		<return-scalar column="group_code" type="string"/>
		<return-scalar column="veterans" type="integer"/>
		<return-scalar column="percentage" type="double"/>
	</sql-query>
	<!--
		Query statement for a site detail view of QM4
	-->
	<sql-query name="QM4.Detailed.site">
		<![CDATA[
         select site_name, cd_reason, veteran_name, veteran_ssn, remarks, group_id, group_code, veterans, percentage, site_total
         from (
               select 	site_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,group_id,
					decode(group_id,'0','Detail','2','Site_Summary','3','Reason_Summary') as group_code,
					group_count as veterans,
					nvl( ratio_to_report(group_count) over(partition by VETERAN_NAME,site_name), 0) as percentage,
                    sum(site_count) over(partition by site_name) site_total
			from(
				select site_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,
				 sum(count_n) as group_count, sum(count_s) site_count,
				grouping_id(VETERAN_NAME,site_name ) as group_id
				from(
					select site.name||'('||site.STATIONNUMBER||')' site_name, upper(cdr.description) cd_reason,
					psim.last_name||','||psim.first_name||' '||psim.middle_name VETERAN_NAME,
		                        nvl2(psim.ssn,substr(psim.ssn,1,3)||'-'||substr(psim.ssn,4,2)||'-'||substr(psim.ssn,6),null) VETERAN_SSN,
					cd.REMARKS REMARKS, 1 count_n, 1 count_s
					from person p, rpt_psim_traits psim, enrollment_determination e,std_institution site,
					STD_ENROLLMENTSTATUS es, CANCEL_DECLINE cd, CANCEL_DECLINE_REASON_TYPE cdr
					where p.vpid_value = psim.vpid_value and p.person_id = e.person_id
					and e.person_id = cd.person_id
					and p.PREFERRED_SITE_ID = site.id
					and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
					and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
					and cd.CANCEL_DECLINE_REASON_TYPE_ID in (:cdReasonType)
					and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id
					and ('ALL' = :ALL or site.id in (:facilityList))
                    union
                    select site.name||'('||site.STATIONNUMBER||')' site_name,
                    upper(cdr.description) cd_reason, null veteran_name, null veteran_ssn,
                    null remarks, 0 count_n, 0 count_s
                    from std_institution site, CANCEL_DECLINE_REASON_TYPE cdr
                    where cdr.ID in (:cdReasonType)
					)
			group by grouping sets(( site_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS), (site_name, cd_reason),
				   ( cd_reason))
		)) where (group_id = 0 and veterans > 0) or group_id = 3 or (group_id = 2 and site_total > 0)
                order by  site_name, group_id, cd_reason, VETERAN_NAME
		]]>
		<return-scalar column="SITE_NAME" type="string"/>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERAN_NAME" type="string"/>
		<return-scalar column="VETERAN_SSN" type="string"/>
		<return-scalar column="REMARKS" type="string"/>
		<return-scalar column="group_code" type="string"/>
		<return-scalar column="veterans" type="integer"/>
		<return-scalar column="percentage" type="double"/>
	</sql-query>
	<!--
		Query statement for a visn detail view of QM4
	-->
	<sql-query name="QM4.Detailed.visn">
		<![CDATA[
         select visn_name, cd_reason, veteran_name, veteran_ssn, remarks, group_id, group_code, veterans, percentage, visn_total
         from (
		    select 	visn_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,group_id,
					decode(group_id,'0','Detail','2','VISN_Summary','3','Reason_Summary') as group_code,
					group_count as veterans,
					nvl( ratio_to_report(group_count) over(partition by VETERAN_NAME,visn_name), 0) as percentage,
					sum(visn_count) over(partition by visn_name) visn_total
			from(
				select visn_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,
				sum(count_n) as group_count, sum(count_s) visn_count,
				grouping_id(VETERAN_NAME,visn_name ) as group_id
				from(
					select nvl(visn.name,'NO VISN') visn_name, upper(cdr.description) cd_reason,
					psim.last_name||','||psim.first_name||' '||psim.middle_name VETERAN_NAME,
		            nvl2(psim.ssn,substr(psim.ssn,1,3)||'-'||substr(psim.ssn,4,2)||'-'||substr(psim.ssn,6),null) VETERAN_SSN,
					cd.REMARKS REMARKS, 1 count_n, 1 count_s
					from person p, rpt_psim_traits psim, enrollment_determination e,
					std_institution site, std_institution visn,
					STD_ENROLLMENTSTATUS es, CANCEL_DECLINE cd, CANCEL_DECLINE_REASON_TYPE cdr
					where p.vpid_value = psim.vpid_value and p.person_id = e.person_id
					and e.person_id = cd.person_id
					and p.PREFERRED_SITE_ID = site.id and site.visn_id = visn.id(+)
					and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
					and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
					and cd.CANCEL_DECLINE_REASON_TYPE_ID in (:cdReasonType)
					and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id
					and (site.visn_id in (:facilityList) or ('noVISN' = :noVISN and site.visn_id is null))
					union
                    select visn_name visn_name, upper(cdr.description) cd_reason,
                    null veteran_name, null veteran_ssn, null remarks, 0 count_n, 0 count_s   from
                    CANCEL_DECLINE_REASON_TYPE cdr,
                    (select 'NO VISN' visn_name, null id from dual union all
                        select name visn_name, id  from std_institution where id in (:facilityList)
                    )visn
                    where  cdr.id in (:cdReasonType) and
                        visn.id is not null or ('noVISN' = ':noVISN' and visn.id is null)
					)
					group by grouping sets((  visn_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS), (visn_name, cd_reason),
						   ( cd_reason))
			)) where (group_id = 0 and veterans > 0) or group_id = 3 or (group_id = 2 and visn_total > 0)
			order by  lpad(trim(substr(visn_name, 5)), 2, '0'), group_id, cd_reason, VETERAN_NAME
		]]>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERAN_NAME" type="string"/>
		<return-scalar column="VETERAN_SSN" type="string"/>
		<return-scalar column="REMARKS" type="string"/>
		<return-scalar column="group_code" type="string"/>
		<return-scalar column="veterans" type="integer"/>
		<return-scalar column="percentage" type="double"/>
	</sql-query>
	<!--
		Query statement for a visn site detail view of QM4
	-->
	<sql-query name="QM4.Detailed.visn.site">
		<![CDATA[
         select visn_name, site_name, cd_reason, veteran_name, veteran_ssn, remarks,
                group_code, veterans, percentage, group_id, visn_total, site_total
         from (
		    select 	visn_name,  site_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,group_id,
					decode(group_id,'0','Detail','4','Site_Summary','6','VISN_Summary','7','Reason_Summary') as group_code,
					group_count as veterans,
					nvl( ratio_to_report(group_count) over(partition by VETERAN_NAME,site_name,visn_name), 0) as percentage,
					sum(visn_count) over(partition by visn_name) visn_total,
					sum(site_count) over(partition by site_name) site_total
			from(
				select visn_name,  site_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS,
				sum(count_n) as group_count, sum(count_s) site_count, sum(count_v) visn_count,
				grouping_id(VETERAN_NAME,site_name,visn_name ) as group_id
				from(
				select nvl(visn.name,'NO VISN') visn_name,
					site.name||'('||site.STATIONNUMBER||')' site_name,
					upper(cdr.description) cd_reason, 1 count_n, 1 count_s, 1 count_v,
					psim.last_name||','||psim.first_name||' '||psim.middle_name VETERAN_NAME,
		            nvl2(psim.ssn,substr(psim.ssn,1,3)||'-'||substr(psim.ssn,4,2)||'-'||substr(psim.ssn,6),null) VETERAN_SSN,
					cd.REMARKS REMARKS
					from person p, rpt_psim_traits psim, enrollment_determination e,
					std_institution site, std_institution visn,
					STD_ENROLLMENTSTATUS es, CANCEL_DECLINE cd, CANCEL_DECLINE_REASON_TYPE cdr
					where p.vpid_value = psim.vpid_value and p.person_id = e.person_id
					and e.person_id = cd.person_id
					and p.PREFERRED_SITE_ID = site.id and site.visn_id = visn.id(+)
					and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
					and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
					and cd.CANCEL_DECLINE_REASON_TYPE_ID in (:cdReasonType)
					and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id
					and ('ALL' = :ALL or site.id in (:facilityList))
					union
					select nvl(visn.name,'NO VISN') visn_name, site.name||'('||site.STATIONNUMBER||')' site_name,
                           upper(cdr.description) cd_reason, 0 count_n, 0 count_s, 0 count_v,
                           null veteran_name, null veteran_ssn, null remarks
			        from std_institution site, std_institution visn, CANCEL_DECLINE_REASON_TYPE cdr
			        where site.visn_id = visn.id(+) and cdr.id in (:cdReasonType) and
			              ('ALL' = :ALL or site.id in (:facilityList))
					)
					group by grouping sets((visn_name,  site_name, cd_reason,  VETERAN_NAME, VETERAN_SSN, REMARKS), (visn_name,  site_name, cd_reason),
						  (visn_name, cd_reason), ( cd_reason))
			))where (group_id = 0 and veterans > 0) or group_id = 7 or
				(group_id = 6 and visn_total > 0) or (group_id = 4 and site_total > 0)
			order by  lpad(trim(substr(visn_name, 5)), 2, '0'),  site_name,group_id, cd_reason, VETERAN_NAME
		]]>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="SITE_NAME" type="string"/>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERAN_NAME" type="string"/>
		<return-scalar column="VETERAN_SSN" type="string"/>
		<return-scalar column="REMARKS" type="string"/>
		<return-scalar column="group_code" type="string"/>
		<return-scalar column="veterans" type="integer"/>
		<return-scalar column="percentage" type="double"/>
	</sql-query>
	<!--
		Query statement for a visn summary view of QM4
	-->
	<sql-query name="QM4.Summary.visn">
		<![CDATA[
		select * from
		(
			 select visn_name,   cd_reason, veterans,  visn_total, 100*veterans/visn_total percentage from (
				 select visn_name,   cd_reason,  sum(veterans) veterans,  sum(visn_total) visn_total  from (
					select all_visn.visn_name, all_visn.cd_reason, nvl(veterans,0) veterans,
					   sum(veterans) over (partition by all_visn.visn_name) visn_total from
                       (select visn_name visn_name, upper(cdr.description) cd_reason  from
                          CANCEL_DECLINE_REASON_TYPE cdr,
                          (select 'NO VISN' visn_name, null id from dual union all
                           select name visn_name, id  from std_institution where id in (:facilityList)
                          )visn
                          where  cdr.id in (:cdReasonType) and
                                 visn.id is not null or ('noVISN' = ':noVISN' and visn.id is null)
                       ) all_visn,
					   (select nvl( visn.name,'NO VISN')  visn_name, upper(cdr.description) cd_reason, count(1) veterans
						from
						person p, std_institution site, std_institution visn,
						enrollment_determination e, STD_ENROLLMENTSTATUS es,
						CANCEL_DECLINE cd, CANCEL_DECLINE_REASON_TYPE cdr
						where
						p.person_id = e.person_id and p.PREFERRED_SITE_ID = site.id and site.visn_id = visn.id(+)
						and e.person_id = cd.person_id
						and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
						and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
						and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id and cdr.id in (:cdReasonType)
						and (site.visn_id in (:facilityList) or ('noVISN' = :noVISN and site.visn_id is null ))
						group by  visn.name,  cdr.description
					) select_visn where all_visn.visn_name = select_visn.visn_name(+) and
					                    all_visn.cd_reason = select_visn.cd_reason(+)
				) where visn_total > 0 group by cube(visn_name,   cd_reason)
			) where cd_reason is not null order by visn_name,   cd_reason
		) order by lpad(trim(substr(visn_name, 5)), 2, '0'),cd_reason
		]]>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERANS" type="integer"/>
		<return-scalar column="VISN_TOTAL" type="integer"/>
		<return-scalar column="PERCENTAGE" type="integer"/>
	</sql-query>

	<!--
		Query statement for a visn/site summary view of QM4
	-->
	<sql-query name="QM4.Summary.visn.site">
		<![CDATA[
        select visn_name, site_name,   cd_reason, veterans,  site_total, 100*veterans/site_total percentage  from (
          select visn_name, site_name,   cd_reason,  sum(veterans) veterans,  sum(site_total) site_total  from (
            select visn_name, site_name, cd_reason, nvl(veterans,0) veterans,
			   sum(veterans) over (partition by visn_name, site_name) site_total from
               (select site.id site_id, site.name||'('||site.STATIONNUMBER||')' site_name,
                                nvl(visn.name,'NO VISN') visn_name,
                                cdr.id cdr_id, upper(cdr.description) cd_reason
			        from std_institution site, std_institution visn, CANCEL_DECLINE_REASON_TYPE cdr
			        where site.visn_id = visn.id(+) and cdr.id in (:cdReasonType) and
			              ('ALL' = :ALL or site.id in (:facilityList))) all_site,
               ( select site.id site_id, cdr.id cdr_id, count(1) veterans
					from
					person p, std_institution site,
					enrollment_determination e, STD_ENROLLMENTSTATUS es,
					CANCEL_DECLINE cd, CANCEL_DECLINE_REASON_TYPE cdr
					where
					p.person_id = e.person_id and p.PREFERRED_SITE_ID = site.id
					and e.person_id = cd.person_id
					and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
					and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
					and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id and cdr.id in (:cdReasonType)
					and ('ALL' = :ALL or site.id in (:facilityList))
					group by site.id , cdr.id) select_site
					where all_site.site_id = select_site.site_id (+) and all_site.cdr_id = select_site.cdr_id(+)
            ) where site_total > 0 group by cube(visn_name, site_name,   cd_reason)
        ) where cd_reason is not null and (visn_name is not null or site_name is null)
		  order by lpad(trim(substr(visn_name, 5)), 2, '0'), site_name, cd_reason
		]]>
		<return-scalar column="VISN_NAME" type="string"/>
		<return-scalar column="SITE_NAME" type="string"/>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERANS" type="integer"/>
		<return-scalar column="SITE_TOTAL" type="integer"/>
		<return-scalar column="PERCENTAGE" type="integer"/>
	</sql-query>

	<!--
		Query statement for a site summary view of QM4
	-->
	<sql-query name="QM4.Summary.site">
		<![CDATA[
	    select site_name,   cd_reason, veterans,  site_total, 100*veterans/site_total percentage from (
          select site_name,   cd_reason,  sum(veterans) veterans,  sum(site_total) site_total  from (
            select site_name, cd_reason, nvl(veterans,0) veterans,
			   sum(veterans) over (partition by site_name) site_total from
               (select site.id site_id, site.name||'('||site.STATIONNUMBER||')' site_name, cdr.id cdr_id, upper(cdr.description) cd_reason
			        from std_institution site, CANCEL_DECLINE_REASON_TYPE cdr
					where cdr.id in (:cdReasonType) and
			              ('ALL' = :ALL or site.id in (:facilityList))) all_site,
               ( select site.id site_id, cdr.id cdr_id, count(1) veterans
					from
					person p, std_institution site,
					enrollment_determination e, STD_ENROLLMENTSTATUS es,
					CANCEL_DECLINE cd, CANCEL_DECLINE_REASON_TYPE cdr
					where
					p.person_id = e.person_id and p.PREFERRED_SITE_ID = site.id
					and e.person_id = cd.person_id
					and e.CHANGE_EFFECTIVE_DATE >= :startDate and e.CHANGE_EFFECTIVE_DATE < :endDate + 1
					and e.STD_ENROLLMENTSTATUS_ID = es.ID and es.code = 7
					and cd.CANCEL_DECLINE_REASON_TYPE_ID = cdr.id and cdr.id in (:cdReasonType)
					and ('ALL' = :ALL or site.id in (:facilityList))
					group by site.id , cdr.id) select_site
					where all_site.site_id = select_site.site_id (+) and all_site.cdr_id = select_site.cdr_id(+)
            ) where site_total > 0 group by cube(site_name,   cd_reason)
		) where cd_reason is not null order by site_name,   cd_reason
		]]>
		<return-scalar column="SITE_NAME" type="string"/>
		<return-scalar column="CD_REASON" type="string"/>
		<return-scalar column="VETERANS" type="integer"/>
		<return-scalar column="SITE_TOTAL" type="integer"/>
		<return-scalar column="PERCENTAGE" type="integer"/>
	</sql-query>
	<!-- QM 19 & 25
	-->

    <sql-query name="hl7ErrorReport.Detailed">
		<![CDATA[
			select  ack.code as ackTypeCode,
					hl.transmission_date as transmissionDate,
			        decode(pt.LAST_NAME,
							   null, 'Unknown name',
							   pt.LAST_NAME || ', ' || pt.FIRST_NAME || ' ' || substr(pt.MIDDLE_NAME, 1, 1)
						   ) as name,
					nvl2(pt.ssn ,substr(pt.ssn ,1,3)||'-'||substr(pt.ssn ,4,2)||'-'||substr(pt.ssn ,6),null) as ssn,
					hl.ERROR_TEXT as errorText,
					site.NAME as site
			from
					hl7_transaction_log hl,
					ack_type ack,
					message_transmission_type mtt,
					person p,
					rpt_psim_traits pt,
					std_institution site
			where   mtt.DIRECTION = :direction
			and     mtt.id = hl.MESSAGE_TRANSMISSION_TYPE_ID
			and     hl.ACK_TYPE_ID = ack.id
			and     ack.code in (:messageError)
			and     ack.code in ('AE', 'AR')
			and     mtt.code in (:messageType)
			and     hl.person_id = p.person_id
			and     p.vpid_value = pt.vpid_value(+)
			and     site.ID = hl.STD_INSTITUTION_ID
			and     hl.TRANSMISSION_DATE >= :startDate
			and     hl.TRANSMISSION_DATE < :endDate + 1
			order by ackTypeCode, transmissionDate desc,  last_name, first_name, middle_name, ssn
		]]>
		<return-scalar column="ackTypeCode" type="string"/>
		<return-scalar column="transmissionDate" type="java.util.Date"/>
		<return-scalar column="name" type="string"/>
		<return-scalar column="ssn" type="string"/>
		<return-scalar column="errorText" type="string"/>
		<return-scalar column="site" type="string"/>
    </sql-query>


    <!-- Adding 3 new queries to fix CR_7424 -->
    <sql-query name="hl7AEErrorReport.Summary">
    <![CDATA[
      select
           'AE' as ackTypeCode,
           :messageType as msgTypeCode,
           (select count(*) as msgCount
      from
         hl7_transaction_log hl,
         ack_type ack,
         message_transmission_type mtt
      where mtt.DIRECTION = :direction
      and mtt.id = hl.MESSAGE_TRANSMISSION_TYPE_ID
      and hl.ACK_TYPE_ID = ack.id
      and ack.code in (:messageError)
      and ack.code in ('AE')
      and person_id is not null
      and mtt.code in (:messageType)
      and hl.TRANSMISSION_DATE >= :startDate
      and hl.TRANSMISSION_DATE < :endDate + 1 )
      as msgCount from dual
    ]]>
    <return-scalar column="ackTypeCode" type="string"/>
    <return-scalar column="msgTypeCode" type="string"/>
    <return-scalar column="msgCount" type="integer"/>
    </sql-query>

    <sql-query name="hl7ARErrorReport.Summary">
    <![CDATA[
      select
           'AR' as ackTypeCode,
           :messageType as msgTypeCode,
           (select count(*) as msgCount
      from
         hl7_transaction_log hl,
         ack_type ack,
         message_transmission_type mtt
      where mtt.DIRECTION = :direction
      and mtt.id = hl.MESSAGE_TRANSMISSION_TYPE_ID
      and hl.ACK_TYPE_ID = ack.id
      and ack.code in (:messageError)
      and ack.code in ('AR')
      and person_id is not null
      and mtt.code in (:messageType)
      and hl.TRANSMISSION_DATE >= :startDate
      and hl.TRANSMISSION_DATE < :endDate + 1 )
      as msgCount from dual
    ]]>
    <return-scalar column="ackTypeCode" type="string"/>
    <return-scalar column="msgTypeCode" type="string"/>
    <return-scalar column="msgCount" type="integer"/>
    </sql-query>

    <sql-query name="hl7AEandARErrorReport.Summary">
    <![CDATA[
      select
           'AE' as ackTypeCode,
           :messageType as msgTypeCode,
           (select count(*) as msgCount
      from
         hl7_transaction_log hl,
         ack_type ack,
         message_transmission_type mtt
      where mtt.DIRECTION = :direction
      and mtt.id = hl.MESSAGE_TRANSMISSION_TYPE_ID
      and hl.ACK_TYPE_ID = ack.id
      and 'AE' in (:messageError)
      and ack.code in ('AE')
      and person_id is not null
      and mtt.code in (:messageType)
      and hl.TRANSMISSION_DATE >= :startDate
      and hl.TRANSMISSION_DATE < :endDate + 1 )
      as msgCount from dual
      UNION
      select
           'AR' as ackTypeCode,
           :messageType as msgTypeCode,
           (select count(*) as msgCount
      from
         hl7_transaction_log hl,
         ack_type ack,
         message_transmission_type mtt
      where mtt.DIRECTION = :direction
      and mtt.id = hl.MESSAGE_TRANSMISSION_TYPE_ID
      and hl.ACK_TYPE_ID = ack.id
      and 'AR' in (:messageError)
      and ack.code in ('AR')
      and person_id is not null
      and mtt.code in (:messageType)
      and hl.TRANSMISSION_DATE >= :startDate
      and hl.TRANSMISSION_DATE < :endDate + 1 )
      as msgCount from dual
    ]]>
    <return-scalar column="ackTypeCode" type="string"/>
    <return-scalar column="msgTypeCode" type="string"/>
    <return-scalar column="msgCount" type="integer"/>
    </sql-query>

  <!--
		Query statement for a national summary view of QM4
	-->
	<sql-query name="QM20.Summary">
		<![CDATA[
			select qst.description vba_query_status, count(1) vba_status_count  from
			eligibility_verification ev, VBA_QUERY_STATUS_TYPE qst,
			(select person_id, min(log.record_created_date) as vba_query_date
				 from hl7_transaction_log log, STD_MESSAGETYPE mtype
				 where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
				 group by person_id
			 )  qryz11m_log
			where ev.VBA_QUERY_STATUS_TYPE_ID = qst.id and ev.person_id =  qryz11m_log.person_id
            and vba_query_date >= :startDate and  vba_query_date < :endDate +1
			and ev.VBA_QUERY_STATUS_TYPE_ID in (:vbaQueryStatusList)
            group by (qst.description) order by qst.description
		]]>
		<return-scalar column="VBA_QUERY_STATUS" type="string"/>
		<return-scalar column="VBA_STATUS_COUNT" type="integer"/>
	</sql-query>
	<sql-query name="QM20.Detailed">
		<![CDATA[
		select  nvl(vba_resp_date,vba_query_date) vba_receive_sent_date,
			    qst.description vba_query_status,
			    nvl2(psim.vpid_value,psim.last_name||','||psim.first_name||' '||psim.middle_name,'UNKNOWN') VETERAN_NAME,
                nvl2(psim.ssn,substr(psim.ssn,1,3)||'-'||substr(psim.ssn,4,2)||'-'||substr(psim.ssn,6),'') VETERAN_SSN from
        eligibility_verification ev,
        person p,
        rpt_psim_traits psim,
        VBA_QUERY_STATUS_TYPE qst,
        (select person_id, trunc(min(log.record_created_date)) as vba_query_date
             from hl7_transaction_log log, STD_MESSAGETYPE mtype
             where log.message_transmission_type_id = mtype.id and mtype.code = 'QRYZ11-M'
             group by person_id
         )  qryz11m_log,
		(select person_id, trunc(min(log.record_created_date)) as vba_resp_date
         from hl7_transaction_log log, STD_MESSAGETYPE mtype
         where log.message_transmission_type_id = mtype.id and mtype.code = 'ORFZ11-E'
         group by person_id)  orfz11e_log
		where   ev.VBA_QUERY_STATUS_TYPE_ID = qst.id
               and ev.person_id = p.person_id and p.vpid_value = psim.vpid_value(+)
               and ev.person_id =  qryz11m_log.person_id
               and ev.person_id = orfz11e_log.person_id(+)
               and vba_query_date >= :startDate and  vba_query_date < :endDate +1
			   and ev.VBA_QUERY_STATUS_TYPE_ID in (:vbaQueryStatusList)
		order by nvl(vba_resp_date,vba_query_date), qst.description
		]]>
		<return-scalar column="VBA_RECEIVE_SENT_DATE" type="date"/>
		<return-scalar column="VBA_QUERY_STATUS" type="string"/>
		<return-scalar column="VETERAN_NAME" type="string"/>
		<return-scalar column="VETERAN_SSN" type="string"/>
	</sql-query>
	<!--
		Query statement for a national summary view of QM15
	-->
	<sql-query name="QM15.Summary">
		<![CDATA[
			select fg.description functional_group, wst.description wkf_status,
                    wkftype.description case_type, count(1) total_cases
			from
			wkf_case w,
			wkf_case_status_detail  wkfs,
			(select sh.wkf_case_id, sh.WKF_CASE_STATUS_TYPE_ID, min(sh.record_modified_date) status_date from wkf_case_status_detail_h sh
            group by wkf_case_id, WKF_CASE_STATUS_TYPE_ID)  wkfsh,
			user_group_type fg,
			wkf_case_type  wkftype,
			wkf_case_status_type wst
		 where  w.wkf_case_id = wkfs.wkf_case_id
            and wkfs.wkf_case_id = wkfsh.wkf_case_id
			and wkfs.WKF_CASE_STATUS_TYPE_ID = wkfsh.WKF_CASE_STATUS_TYPE_ID
			and w.STD_USERGROUPTYPE_ID = fg.id
			and w.wkf_case_type_id = wkftype.id
			and wkfs.WKF_CASE_STATUS_TYPE_ID = wst.id
			and w.record_created_date >= :startDate and  w.record_created_date  < :endDate +1
            and w.wkf_case_type_id in (:wkfCaseTypeList)
            and w.STD_USERGROUPTYPE_ID in (:functionalGroupList)
            and wkfs.WKF_CASE_STATUS_TYPE_ID in (:wkfCaseStatusList)
            and  sysdate-status_date >= :days
		group by   fg.description, wst.description ,  wkftype.description
		order by fg.description,wst.description, wkftype.description
		]]>
		<return-scalar column="FUNCTIONAL_GROUP" type="string"/>
		<return-scalar column="WKF_STATUS" type="string"/>
		<return-scalar column="CASE_TYPE" type="string"/>
		<return-scalar column="TOTAL_CASES" type="big_decimal"/>
	</sql-query>

	<sql-query name="QM15.Detailed">
		<![CDATA[
		 select fg.description functional_group, wst.description wkf_status,
				  w.record_created_date open_date,
				  round(sysdate-w.record_created_date,0) open_days,
              psim.last_name||','||psim.first_name||' '||psim.middle_name veteran_name,
              nvl2(psim.ssn,substr(psim.ssn,1,3)||'-'||substr(psim.ssn,4,2)||'-'||substr(psim.ssn,6),null) veteran_ssn,
              wkftype.description case_type, round(sysdate-status_date,0) status_days,
              decode(wst.code,'04',null,'23',null,u.full_name) assigned_to
		 from
			 wkf_case w, wkf_case_status_detail  wkfs,
			 (select sh.wkf_case_id, sh.WKF_CASE_STATUS_TYPE_ID, min(sh.record_modified_date) status_date
				from wkf_case_status_detail_h sh group by wkf_case_id, WKF_CASE_STATUS_TYPE_ID)  wkfsh,
        	 person p,
			 rpt_psim_traits psim,
			 user_group_type fg,
			 wkf_case_type  wkftype,
			 wkf_case_status_type wst,
			 wkf_case_assignment wa,
			 users u
		 where
			w.wkf_case_id = wkfs.wkf_case_id
            and wkfs.wkf_case_id = wkfsh.wkf_case_id
            and wkfs.WKF_CASE_STATUS_TYPE_ID = wkfsh.WKF_CASE_STATUS_TYPE_ID
            and w.STD_USERGROUPTYPE_ID = fg.id
            and w.wkf_case_type_id = wkftype.id
            and wkfs.WKF_CASE_STATUS_TYPE_ID = wst.id
            and w.person_id = p.person_id and p.vpid_value = psim.vpid_value
            and w.wkf_case_id = wa.wkf_case_id and wa.WKF_CASE_ASSIGNED_TO  = u.username(+)
            and w.record_created_date >= :startDate and  w.record_created_date  < :endDate +1
            and w.wkf_case_type_id in (:wkfCaseTypeList)
            and w.STD_USERGROUPTYPE_ID in (:functionalGroupList)
            and wkfs.WKF_CASE_STATUS_TYPE_ID in (:wkfCaseStatusList)
            and  sysdate-status_date >= :days
			order by fg.description, wst.description, wkftype.description
		]]>
		<return-scalar column="functional_group" type="string"/>
		<return-scalar column="wkf_status" type="string"/>
		<return-scalar column="open_date" type="date"/>
		<return-scalar column="open_days" type="big_decimal"/>
		<return-scalar column="veteran_name" type="string"/>
		<return-scalar column="veteran_ssn" type="string"/>
		<return-scalar column="case_type" type="string"/>
		<return-scalar column="status_days" type="big_decimal"/>
		<return-scalar column="assigned_to" type="string"/>
	</sql-query>

	<!--
		Query statement for a national summary view of QM11
	-->
	<sql-query name="QM11.Summary">
		<![CDATA[
	        select fg.description functional_group,
				wkft.description case_type , count(1) case_type_total,
				round(avg(wkfs.record_modified_date-wkf.record_created_date),1) case_type_avg_days
			from wkf_case wkf, USER_GROUP_TYPE fg, wkf_case_type wkft, wkf_case_status_detail wkfs
			where wkf.STD_USERGROUPTYPE_ID = fg.id
				and wkf.WKF_CASE_TYPE_ID = wkft.id
				and wkf.wkf_case_id = wkfs.wkf_case_id
				and wkfs.WKF_CASE_STATUS_TYPE_ID in
				(select id from wkf_case_status_type where code = '04')
			    and wkf.record_created_date >= :startDate and wkf.record_created_date < :endDate +1
			    and wkf.wkf_case_type_id in (:wkfCaseTypeList)
                and wkf.STD_USERGROUPTYPE_ID in (:functionalGroupList)
			group by fg.description, wkft.description
			order by fg.description desc, wkft.description
		]]>
		<return-scalar column="FUNCTIONAL_GROUP" type="string"/>
		<return-scalar column="CASE_TYPE" type="string"/>
		<return-scalar column="CASE_TYPE_TOTAL" type="big_decimal"/>
		<return-scalar column="CASE_TYPE_AVG_DAYS" type="big_decimal"/>
	</sql-query>

	<sql-query name="QM11.Summary.User">
		<![CDATA[
	        select fg.description functional_group,
			    u.FULL_NAME full_name,
				wkft.description case_type , count(1) case_type_total,
				round(avg(wkfs.record_modified_date-wkfa.wkf_case_assignment_date),1) case_type_avg_days
			from wkf_case wkf, USER_GROUP_TYPE fg, wkf_case_type wkft, wkf_case_status_detail wkfs,
				wkf_case_assignment  wkfa, users u
			where wkf.STD_USERGROUPTYPE_ID = fg.id
				and wkf.WKF_CASE_TYPE_ID = wkft.id
				and wkf.wkf_case_id = wkfs.wkf_case_id
			    and wkf.wkf_case_id = wkfa.wkf_case_id
			    and wkfa.wkf_case_assigned_to = u.username
				and wkfs.WKF_CASE_STATUS_TYPE_ID in
				(select id from wkf_case_status_type where code = '04')
			    and wkfa.WKF_CASE_ASSIGNMENT_DATE is not null
				and wkfa.WKF_CASE_ASSIGNMENT_DATE >= :startDate
				and wkfa.WKF_CASE_ASSIGNMENT_DATE < :endDate +1
			    and wkf.wkf_case_type_id in (:wkfCaseTypeList)
                and wkf.STD_USERGROUPTYPE_ID in (:functionalGroupList)
			group by fg.description, wkft.description, u.FULL_NAME
			order by fg.description desc, u.FULL_NAME, wkft.description
		]]>
		<return-scalar column="FUNCTIONAL_GROUP" type="string"/>
		<return-scalar column="FULL_NAME" type="string"/>
		<return-scalar column="CASE_TYPE" type="string"/>
		<return-scalar column="CASE_TYPE_TOTAL" type="big_decimal"/>
		<return-scalar column="CASE_TYPE_AVG_DAYS" type="big_decimal"/>
	</sql-query>
	<sql-query name="QM11.Detailed">
		<![CDATA[
		select *
		from
		(
		  select functional_group, case_type, case_name,
          nvl2(case_ssn,substr(case_ssn,1,3)||'-'||substr(case_ssn,4,2)||'-'||substr(case_ssn,6),null) case_ssn,
			 case_detail_type, case_logged_date,case_closed_date,
             round((case_closed_date2 - case_logged_date) + 0.5 ,0) days_to_process
		  from
			(select fg.description functional_group,  wkft.description case_type ,
				 psim.last_name||','||psim.first_name||' '||psim.middle_name case_name,
				 psim.ssn case_ssn, wkf.error_message_text case_detail_type,
				 wkf.record_created_date case_logged_date,
				 decode(wkfs.WKF_CASE_STATUS_TYPE_ID,(select id from wkf_case_status_type where code = '04'),
				 wkfs.record_modified_date,sysdate) case_closed_date2,
			     decode(wkfs.WKF_CASE_STATUS_TYPE_ID,(select id from wkf_case_status_type where code = '04'),
				 wkfs.record_modified_date,null) case_closed_date
			from
				wkf_case wkf, person p, rpt_psim_traits psim,
				USER_GROUP_TYPE fg,
				wkf_case_type wkft ,
				wkf_case_status_detail wkfs
				where wkf.person_id = p.person_id and p.vpid_value = psim.vpid_value
				and wkf.STD_USERGROUPTYPE_ID = fg.id
				and wkf.WKF_CASE_TYPE_ID = wkft.id
				and wkf.wkf_case_id = wkfs.wkf_case_id
				and wkf.record_created_date >= :startDate and wkf.record_created_date < :endDate +1
				and wkf.wkf_case_type_id in (:wkfCaseTypeList)
				and wkf.STD_USERGROUPTYPE_ID in (:functionalGroupList)
		    )where round((case_closed_date - case_logged_date) + 0.5 ,0) >= :days
			order by dbms_random.random
		) where ('ALL' = :ALL or rownum < :randomNumberOfRecords + 1)
		  order by functional_group desc, case_type
		]]>
		<return-scalar column="FUNCTIONAL_GROUP" type="string"/>
		<return-scalar column="CASE_TYPE" type="string"/>
		<return-scalar column="CASE_NAME" type="string"/>
		<return-scalar column="CASE_SSN" type="string"/>
		<return-scalar column="CASE_DETAIL_TYPE" type="string"/>
		<return-scalar column="CASE_LOGGED_DATE" type="date"/>
		<return-scalar column="CASE_CLOSED_DATE" type="date"/>
		<return-scalar column="DAYS_TO_PROCESS" type="big_decimal"/>
	</sql-query>

	<sql-query name="QM11.Detailed.User">
		<![CDATA[
		select *
		from
		(
		select functional_group, full_name, case_type, case_name,
             nvl2(case_ssn,substr(case_ssn,1,3)||'-'||substr(case_ssn,4,2)||'-'||substr(case_ssn,6),null) case_ssn,
			 case_detail_type, case_logged_date,case_closed_date,
             round((case_closed_date2 - case_logged_date) + 0.5 ,0) days_to_process
		from
			(select fg.description functional_group,  u.FULL_NAME, wkft.description case_type ,
             psim.last_name||','||psim.first_name||' '||psim.middle_name case_name,
             psim.ssn case_ssn, wkf.error_message_text case_detail_type,
             wkfa.wkf_case_assignment_date case_logged_date,
             decode(wkfs.WKF_CASE_STATUS_TYPE_ID,(select id from wkf_case_status_type where code = '04'),
			 wkfs.record_modified_date,sysdate) case_closed_date2,
			 decode(wkfs.WKF_CASE_STATUS_TYPE_ID,(select id from wkf_case_status_type where code = '04'),
			 wkfs.record_modified_date,null) case_closed_date
			from
			wkf_case wkf, person p, rpt_psim_traits psim,
			USER_GROUP_TYPE fg,
			wkf_case_type wkft ,
			wkf_case_status_detail wkfs,
            wkf_case_assignment  wkfa,
            users u
			where wkf.person_id = p.person_id and p.vpid_value = psim.vpid_value
			and wkf.STD_USERGROUPTYPE_ID = fg.id
			and wkf.WKF_CASE_TYPE_ID = wkft.id
			and wkf.wkf_case_id = wkfs.wkf_case_id
            and wkf.wkf_case_id = wkfa.wkf_case_id
            and wkfa.wkf_case_assigned_to = u.username
            and wkfa.WKF_CASE_ASSIGNMENT_DATE is not null
            and wkfa.WKF_CASE_ASSIGNMENT_DATE >= :startDate
            and wkfa.WKF_CASE_ASSIGNMENT_DATE < :endDate +1
			and wkf.wkf_case_type_id in (:wkfCaseTypeList)
            and wkf.STD_USERGROUPTYPE_ID in (:functionalGroupList)
			)where round((case_closed_date - case_logged_date) + 0.5 ,0) >= :days
			order by dbms_random.random
			) where ('ALL' = :ALL or rownum < :randomNumberOfRecords + 1)
		  order by functional_group desc, full_name, case_type
		]]>
		<return-scalar column="FUNCTIONAL_GROUP" type="string"/>
		<return-scalar column="FULL_NAME" type="string"/>
		<return-scalar column="CASE_TYPE" type="string"/>
		<return-scalar column="CASE_NAME" type="string"/>
		<return-scalar column="CASE_SSN" type="string"/>
		<return-scalar column="CASE_DETAIL_TYPE" type="string"/>
		<return-scalar column="CASE_LOGGED_DATE" type="date"/>
		<return-scalar column="CASE_CLOSED_DATE" type="date"/>
		<return-scalar column="DAYS_TO_PROCESS" type="big_decimal"/>
	</sql-query>

	<!--
		Query statement for a HL7 message traffic QM 28, QM 29 and QM 30.
	-->
	<sql-query name="HL7_Message_Traffic">
		<![CDATA[
				   select
				   to_char(hl.transmission_date , 'HH24') as hour,
				   mtt.code as message_type,
				   site.stationnumber as station_number,
				   site.vistaname as site_name,
				   count(hl.transmission_date) as message_count
				from
					 hl7_transaction_log hl,
					 message_transmission_type mtt,
					 std_institution site
				where mtt.id = hl.message_transmission_type_id
					and hl.std_institution_id=site.id
					and mtt.code in (:messageTypes)
					and hl.transmission_date >= :startDate
					and hl.transmission_date < :startDate + 1
				group by
						to_char(hl.transmission_date , 'HH24'),
						mtt.code,
						site.stationnumber,
						site.vistaname
				order by
						hour,
						message_type,
						station_number,
						site_name
		]]>
		<return-scalar column="hour" type="string"/>
		<return-scalar column="message_type" type="string"/>
		<return-scalar column="station_number" type="string"/>
		<return-scalar column="site_name" type="string"/>
		<return-scalar column="message_count" type="integer"/>
	</sql-query>

	<!--
		Query statement for all views of a "MST National Report"
	-->
	<sql-query name="mstStatusReport" fetch-size="100">
		<![CDATA[
			select p.vpid_value as vpid,
				   sm.name as status,
				   mst.mst_evaluation_date as statusDate,
				   rpt.gender_code as genderCode
			from beneficiary_mst mst, person p, rpt_psim_traits rpt, std_mststatus sm
			where mst.person_id=p.person_id
				  and p.vpid_value=rpt.vpid_value
				  and sm.id=mst.mst_status_type_id
				  and mst.mst_evaluation_date >=:startDate
				  and mst.mst_evaluation_date < :endDate + 1
			order by mst.mst_evaluation_date
		]]>
		<return-scalar column="vpid" type="string"/>
		<return-scalar column="status" type="string"/>
		<return-scalar column="statusDate" type="date"/>
		<return-scalar column="genderCode" type="string"/>
	</sql-query>

</hibernate-mapping>