package gov.va.med.pharmacy.persistence.track.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import gov.va.med.pharmacy.persistence.track.NcpdpMessageListModel;
import gov.va.med.pharmacy.persistence.track.NcpdpMessageModel;
import gov.va.med.pharmacy.persistence.track.NcpdpMessagesDao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component
public class NcpdpMessagesDaoImpl implements NcpdpMessagesDao {
	
	private static final org.apache.logging.log4j.Logger LOG = org.apache.logging.log4j.LogManager.getLogger(NcpdpMessagesDaoImpl.class);
	
	@Autowired
	private DataSource dataSource;
	
	@Autowired
    NcpdpMessageModel ncpdpMsg;
	
	public NcpdpMessagesDaoImpl() {
	}
	
	 public NcpdpMessagesDaoImpl(DataSource dataSource) {
	        this.dataSource = dataSource;
	    }
	

		public class NcpdpMsgListRowMapper implements RowMapper<NcpdpMessageListModel>
		{
			public NcpdpMessageListModel mapRow(ResultSet rs, int rowNum) throws SQLException{
				NcpdpMessageListModel ncpdpMessageListModel = new NcpdpMessageListModel();

				ncpdpMessageListModel.setInboundNcpdpMsgId((rs.getLong("inbound_ncpdp_msg_id")));
				ncpdpMessageListModel.setRxMessageId(rs.getString("rx_messageId"));
				ncpdpMessageListModel.setRelToMessageid(rs.getString("rel_to_message_id"));
				ncpdpMessageListModel.setMessageType(rs.getString("message_type"));
				ncpdpMessageListModel.setReceivedDate(rs.getString("received_date"));
				ncpdpMessageListModel.setVisn((rs.getInt("visn")));
				ncpdpMessageListModel.setVaStationId(rs.getString("va_station_id"));
				ncpdpMessageListModel.setPharmacyName(rs.getString("pharmacy_name"));
				ncpdpMessageListModel.setPharmacyAddr1(rs.getString("pharmacy_addr_1"));
				ncpdpMessageListModel.setPrescriberName(rs.getString("prescriber_Name"));
				ncpdpMessageListModel.setPrescriberNpi(rs.getString("prescriber_npi"));
				ncpdpMessageListModel.setPatientName(rs.getString("patient_Name"));
				ncpdpMessageListModel.setRxDrugPrescribed(rs.getString("rx_Drug_Prescribed"));
				ncpdpMessageListModel.setPatient_chk_status(rs.getString("patient_chk_status"));
				ncpdpMessageListModel.setProvider_chk_status(rs.getString("provider_chk_status"));
				ncpdpMessageListModel.setDrug_chk_status(rs.getString("drug_chk_status"));
				ncpdpMessageListModel.setMessage_status(rs.getString("message_status"));
				ncpdpMessageListModel.setPatientDob(rs.getString("patient_dob"));
				ncpdpMessageListModel.setPatientSsn(rs.getString("patient_ssn"));

	    		return ncpdpMessageListModel;
			}
		}
	 
	public class NcpdpMsgRowMapper implements RowMapper<NcpdpMessageModel>
	{
		public NcpdpMessageModel mapRow(ResultSet rs, int rowNum) throws SQLException{
			NcpdpMessageModel ncpdpMessageModel = new NcpdpMessageModel();

			
			ncpdpMessageModel.setInboundNcpdpMsgId(Long.parseUnsignedLong((rs.getString("inbound_ncpdp_msg_id"))));
			ncpdpMessageModel.setRxMessageId(rs.getString("rx_messageId"));
			ncpdpMessageModel.setRelToMessageid(rs.getString("rel_to_message_id"));
			ncpdpMessageModel.setMessage_status(rs.getString("message_status"));
			ncpdpMessageModel.setMessageType(rs.getString("message_type"));
			ncpdpMessageModel.setReceivedDate(rs.getString("received_date"));
			ncpdpMessageModel.setPharmacyName(rs.getString("pharmacy_name"));
			ncpdpMessageModel.setPharmacyAddr1(rs.getString("pharmacy_addr_1"));
			ncpdpMessageModel.setPharmacyAddr2(rs.getString("pharmacy_addr_2"));
			ncpdpMessageModel.setPharmacyPhone(rs.getString("pharmacy_phone"));
			ncpdpMessageModel.setPharmacyNcpdpid(rs.getString("pharmacy_ncpdpid")); 
			ncpdpMessageModel.setPrescriberFirstName(rs.getString("prescriber_First_Name"));
			ncpdpMessageModel.setPrescriberMidName(rs.getString("prescriber_Mid_Name"));
			ncpdpMessageModel.setPrescriberLastName(rs.getString("prescriber_Last_Name"));
			ncpdpMessageModel.setPrescriberAddr1(rs.getString("prescriber_addr_1"));
			ncpdpMessageModel.setPrescriberAddr2(rs.getString("prescriber_addr_2"));
			ncpdpMessageModel.setPrescriberNPI(rs.getString("prescriber_NPI"));
			ncpdpMessageModel.setPrescriberDEA(rs.getString("prescriber_DEA"));
			ncpdpMessageModel.setPrescriberStateLic(rs.getString("prescriber_State_Lic"));
			ncpdpMessageModel.setPrescriberPhone(rs.getString("prescriber_phone"));
			ncpdpMessageModel.setPrescriberFax(rs.getString("prescriber_fax"));
			ncpdpMessageModel.setPrescriberAgent(rs.getString("prescriber_Agent"));
			ncpdpMessageModel.setPatientFirstName(rs.getString("patient_First_Name"));
			ncpdpMessageModel.setPatientMidName(rs.getString("patient_Mid_Name"));
			ncpdpMessageModel.setPatientLastName(rs.getString("patient_Last_Name"));
			ncpdpMessageModel.setPatientAddr1(rs.getString("patient_addr_1"));
			ncpdpMessageModel.setPatientAddr2(rs.getString("patient_addr_2"));
			ncpdpMessageModel.setPatientDob(rs.getString("patient_dob"));
			ncpdpMessageModel.setPatientGender(rs.getString("patient_gender"));
			ncpdpMessageModel.setRxDrugPrescribed(rs.getString("rx_Drug_Prescribed"));
			ncpdpMessageModel.setRxQuantity(rs.getString("rx_Quantity"));
			ncpdpMessageModel.setRxDaysSupply(rs.getString("rx_Days_Supply"));
			ncpdpMessageModel.setRxDateWritten(rs.getString("rx_Date_Written"));
			ncpdpMessageModel.setRxPotencyUnitCode(rs.getString("rx_Potency_Unit_Code"));
			ncpdpMessageModel.setRxDrugForm(rs.getString("rx_Drug_Form"));
			ncpdpMessageModel.setRxDrugStrength(rs.getString("rx_Drug_Strength"));
			ncpdpMessageModel.setRxRefills(rs.getString("rx_Refills"));
			ncpdpMessageModel.setRxSig(rs.getString("rx_Sig"));
			ncpdpMessageModel.setRxDispenseNotes(rs.getString("rx_Dispense_Notes"));
			ncpdpMessageModel.setRxComments(rs.getString("rx_Comments"));
			ncpdpMessageModel.setPlanId(rs.getString("plan_id"));
			ncpdpMessageModel.setRxGrp(rs.getString("rx_Grp"));
			ncpdpMessageModel.setRxBinNum(rs.getString("rx_Bin_Num"));
			ncpdpMessageModel.setRxPcn(rs.getString("rx_Pcn"));
			ncpdpMessageModel.setReqRefno(rs.getString("req_refno"));
			ncpdpMessageModel.setResType(rs.getString("res_type"));
			ncpdpMessageModel.setResAprvReasonCd(rs.getString("res_aprv_reason_cd")); 
			ncpdpMessageModel.setResAprvRefno(rs.getString("res_aprv_refno")); 
			ncpdpMessageModel.setResAprvNote(rs.getString("res_aprv_note")); 
			ncpdpMessageModel.setResAprvWthChngReasonCd(rs.getString("res_aprv_w_chng_reason_cd")); 
			ncpdpMessageModel.setResAprvWthChngRefno(rs.getString("res_aprv_w_chng_refno")); 
			ncpdpMessageModel.setResAprvWthChngNote(rs.getString("res_aprv_w_chng_note"));
			ncpdpMessageModel.setResDenialReasonCd(rs.getString("res_denial_reason_cd")); 
			ncpdpMessageModel.setResDenialRefno(rs.getString("res_denial_refno"));
			ncpdpMessageModel.setResDenialReason(rs.getString("res_denial_reason")); 
			ncpdpMessageModel.setResDenialNrxReasonCd(rs.getString("res_denial_nrx_reason_cd")); 
			ncpdpMessageModel.setResDenialNrxRefno(rs.getString("res_denial_nrx_refno")); 
			ncpdpMessageModel.setResDenialNrxReason(rs.getString("res_denial_nrx_reason")); 	
			ncpdpMessageModel.setEsvDescriptionCode(rs.getString("esv_description_code")); 
			ncpdpMessageModel.setEsvCode(rs.getString("esv_code")); 
			ncpdpMessageModel.setEsvDescription(rs.getString("esv_description")); 
			ncpdpMessageModel.setClinicId(rs.getString("clinic_id")); 

    		return ncpdpMessageModel;
		}
	}
	
	@Override
	public NcpdpMessageModel findById(String id, String inboundOutbound) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "";
		if (inboundOutbound.equalsIgnoreCase("Inbound")) {	
        sql = "select t.inbound_ncpdp_msg_id inbound_ncpdp_msg_id,  \r\n" + 
        		"        		  x.message_id rx_messageId,  \r\n" + 
        		"        		  x.rel_to_message_id,  \r\n" + 
        		"        		  x.message_type,  \r\n" + 
        		"				  (select code_description from erx_status where code = t.message_status) message_status, \r\n"	+	
        		"        		  to_char(t.received_date,'MM/DD/YYYY') received_date,  \r\n" + 
        		"        		  x.pharmacy_name,  \r\n" + 
        		"        		  x.pharmacy_addr_1||' '||x.pharmacy_addr_2 pharmacy_addr_1,  \r\n" + 
        		"        		  x.pharmacy_city||', '||x.pharmacy_state||'  '||x.pharmacy_zip pharmacy_addr_2,  \r\n" + 
        		"        		pharmacy_phone.pharmacy_number pharmacy_phone,  \r\n" + 
        		"        		nvl(x.pharmacy_ncpdpid1,x.pharmacy_ncpdpid2) pharmacy_ncpdpid,   \r\n" + 
        		"        		  x.prescriber_First_Name,  \r\n" + 
        		"        		  x.prescriber_Mid_Name,  \r\n" + 
        		"        		  x.prescriber_Last_Name,  \r\n" + 
        		"        		  x.prescriber_addr_1||' '||x.prescriber_addr_2 prescriber_addr_1,  \r\n" + 
        		"        		  x.prescriber_city||', '||x.prescriber_state||'  '||x.prescriber_zip prescriber_addr_2,  \r\n" + 
        		"        		  x.npi1 prescriber_NPI,  \r\n" + 
        		"        		  x.npi2 clinic_id,  \r\n" + 
        		"        		  x.prescriber_DEA,  \r\n" + 
        		"        		  x.prescriber_State_Lic,  \r\n" + 
        		"        		prescriber_fax.prescriber_faxnumber prescriber_fax, \r\n" + 
        		"        		prescriber_phone.prescriber_number prescriber_phone,  \r\n" + 
        		"        		  x.prescriber_agent_first_name||' '||x.prescriber_agent_last_name prescriber_Agent,  \r\n" + 
        		"        		  x.patient_First_Name,  \r\n" + 
        		"        		  x.patient_Mid_Name,  \r\n" + 
        		"        		  x.patient_Last_Name,  \r\n" + 
        		"        		  x.patient_addr_1||' '||x.patient_addr_2 patient_addr_1,  \r\n" + 
        		"        		  x.patient_city||', '||x.patient_state||'  '||x.patient_zip patient_addr_2,  \r\n" + 
        		"        		to_char(to_date(decode(length(x.patient_dob),11,substr(x.patient_dob, 2,10), x.patient_dob),'YYYY-MM-DD'),'MM/DD/YYYY') patient_dob,  \r\n" + 
        		"        		  x.patient_gender patient_gender,  \r\n" + 
        		"        		  benefits1.cardholder_id plan_id,  \r\n" + 
        		"        		  x.rx_Drug_Prescribed,  \r\n" + 
        		"        		  x.rx_Quantity,  \r\n" + 
        		"        		  x.rx_Days_Supply,  \r\n" + 
        		"        	  	to_char(to_date(decode(length(x.rx_Date_Written),11,substr(x.rx_Date_Written, 2,10), x.rx_Date_Written),'YYYY-MM-DD'),'MM/DD/YYYY') rx_Date_Written,  \r\n" + 
        		"        		  (select distinct ncit_pref_term from ncpdp_code where ncit_code = x.rx_Potency_Unit_Code) rx_Potency_Unit_Code,  \r\n" + 
        		"        		  (select distinct ncit_pref_term from ncpdp_code where ncit_code = x.rx_Drug_Form) rx_Drug_Form,  \r\n" + 
        		"        		  x.rx_Drug_Strength||' '||(select distinct ncit_pref_term from ncpdp_code where ncit_code = x.rx_Drug_Strength_Code) rx_Drug_Strength,  \r\n" + 
        		"        		  x.rx_Refills,  \r\n" + 
        		"        		  x.rx_Sig,\r\n" + 
        		"              		decode(x.rx_Dispense_Notes,'0','0 = No Product Selection Indicated','1','1 = Substitution Not Allowed by Prescriber','2','2 = Substitution Allowed-Patient Requested Product Dispensed','3','3 = Substitution Allowed-Pharmacist Selected Product Dispensed','4','4 = Substitution Allowed-Generic Drug Not in Stock','5','5 = Substitution Allowed-Brand Drug Dispensed as a Generic','6','6 = Override','7','7 = Substitution Not Allowed-Brand Mandated by Law','8','8 = Substitution Allowed-Generic Drug Not Available in Marketplace','9','9 = Substitution Allowed By Prescriber but Plan Requests Brand - Patient''s Plan Requested Brand Product To Be Dispensed') rx_Dispense_Notes,\r\n" + 
        		"					x.rx_Comments,  \r\n" + 
        		"        		  benefits1.groupID rx_Grp,\r\n" + 
        		"        		  benefits1.rx_Bin_Num rx_Bin_Num,  \r\n" + 
        		"        		  benefits1.rx_Pcn rx_Pcn, \r\n" + 
        		"                 x.res_type, \r\n" + 
        		"                 x.req_refno,\r\n" + 
        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_aprv_reason_cd) res_aprv_reason_cd, \r\n" + 
        		"                 x.res_aprv_refno, \r\n" + 
        		"                 x.res_aprv_note, \r\n" + 
        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_aprv_w_chng_reason_cd) res_aprv_w_chng_reason_cd, \r\n" + 
        		"                 x.res_aprv_w_chng_refno, \r\n" + 
        		"                 x.res_aprv_w_chng_note, \r\n" + 
        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_denial_reason_cd) res_denial_reason_cd, \r\n" + 
        		"                 x.res_denial_refno, \r\n" + 
        		"                 x.res_denial_reason, \r\n" + 
        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_denial_nrx_reason_cd) res_denial_nrx_reason_cd, \r\n" + 
        		"                 x.res_denial_nrx_refno, \r\n" + 
        		"                 x.res_denial_nrx_reason, " +
        		"                 x.esv_description_code, " +
        		"                 x.esv_code, " +
        		"                 x.esv_description " +
        		"              from inbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
        		"        		    passing t.message  \r\n" + 
        		"        		    columns   \r\n" + 
        		"        		    message_id varchar2(35) path '//ns:Header/ns:MessageID',  \r\n" + 
        		"        		    rel_to_message_id varchar2(35) path '//ns:Header/ns:RelatesToMessageID',  \r\n" + 
        		"        		    message_type varchar2(35) path 'name(/ns:Body/*[1])',  \r\n" + 
        		"        		    pharmacy_name varchar2(35) path '//ns:Pharmacy/ns:StoreName',  \r\n" + 
        		"        		    pharmacy_addr_1 varchar2(35) path '//ns:Pharmacy/ns:Address/ns:AddressLine1',  \r\n" + 
        		"        		    pharmacy_addr_2 varchar2(35) path '//ns:Pharmacy/ns:Address/ns:AddressLine2',  \r\n" + 
        		"        		    pharmacy_city varchar2(35) path '//ns:Pharmacy/ns:Address/ns:City',  \r\n" + 
        		"        		    pharmacy_state varchar2(2) path '//ns:Pharmacy/ns:Address/ns:State',  \r\n" + 
        		"        		    pharmacy_zip varchar2(14) path '//ns:Pharmacy/ns:Address/ns:ZipCode',  \r\n" + 
        		"        		    pharmacy_ncpdpid1 varchar2(35) path '//ns:Pharmacy/ns:Identification/ns:NCPDPID',  \r\n" + 
        		"        		    pharmacy_ncpdpid2 varchar2(35) path '//ns:Header/ns:To',  \r\n" + 
        		"        		    npi1 varchar2(35) path '//ns:Prescriber/ns:Identification/ns:NPI',  \r\n" + 
        		"        		    npi2 varchar2(35) path '//ns:Header/ns:From',  \r\n" + 
        		"        		    prescriber_First_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:FirstName',  \r\n" + 
        		"        		    prescriber_Mid_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:MiddleName',  \r\n" + 
        		"        		    prescriber_Last_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:LastName',  \r\n" + 
        		"        		    prescriber_addr_1 varchar2(35) path '//ns:Prescriber/ns:Address/ns:AddressLine1',  \r\n" + 
        		"        		    prescriber_addr_2 varchar2(35) path '//ns:Prescriber/ns:Address/ns:AddressLine2',  \r\n" + 
        		"        		    prescriber_city varchar2(35) path '//ns:Prescriber/ns:Address/ns:City',  \r\n" + 
        		"        		    prescriber_state varchar2(2) path '//ns:Prescriber/ns:Address/ns:State',  \r\n" + 
        		"        		    prescriber_zip varchar2(14) path '//ns:Prescriber/ns:Address/ns:ZipCode',  \r\n" + 
        		"        		    prescriber_agent_last_name varchar2(35) path '//ns:Prescriber/ns:PrescriberAgent/ns:LastName',  \r\n" + 
        		"        		    prescriber_agent_first_name varchar2(35) path '//ns:Prescriber/ns:PrescriberAgent/ns:FirstName',  \r\n" + 
        		"        		    prescriber_DEA varchar2(35) path '//ns:Prescriber/ns:Identification/ns:DEANumber',  \r\n" + 
        		"        		    prescriber_State_Lic varchar2(35) path '//ns:Prescriber/ns:Identification/ns:StateLicenseNumber',  \r\n" + 
        		"        		    patient_First_Name varchar2(35) path '//ns:Patient/ns:Name/ns:FirstName',  \r\n" + 
        		"        		    patient_Mid_Name varchar2(35) path '//ns:Patient/ns:Name/ns:MiddleName',  \r\n" + 
        		"        		    patient_Last_Name varchar2(35) path '//ns:Patient/ns:Name/ns:LastName',  \r\n" + 
        		"        		    patient_addr_1 varchar2(35) path '//ns:Patient/ns:Address/ns:AddressLine1',  \r\n" + 
        		"        		    patient_addr_2 varchar2(35) path '//ns:Patient/ns:Address/ns:AddressLine2',  \r\n" + 
        		"        		    patient_city varchar2(35) path '//ns:Patient/ns:Address/ns:City',  \r\n" + 
        		"        		    patient_state varchar2(2) path '//ns:Patient/ns:Address/ns:State',  \r\n" + 
        		"        		    patient_zip varchar2(14) path '//ns:Patient/ns:Address/ns:ZipCode',  \r\n" + 
        		"        		    patient_dob varchar2(30) path '//ns:Patient/ns:DateOfBirth/ns:Date',  \r\n" + 
        		"        		    patient_gender varchar2(1) path '//ns:Patient/ns:Gender',  \r\n" + 
        		"        		    rx_Drug_Prescribed varchar2(105) path '//ns:MedicationPrescribed/ns:DrugDescription',  \r\n" + 
        		"        		    rx_Quantity varchar2(30) path '//ns:MedicationPrescribed/ns:Quantity/ns:Value',  \r\n" + 
        		"        		    rx_Days_Supply varchar2(30) path '//ns:MedicationPrescribed/ns:DaysSupply',  \r\n" + 
        		"        		    rx_Date_Written varchar2(30) path '//ns:MedicationPrescribed/ns:WrittenDate',  \r\n" + 
        		"        		    rx_Potency_Unit_Code varchar2(30) path '//ns:MedicationPrescribed/ns:Quantity/ns:PotencyUnitCode',  \r\n" + 
        		"        		    rx_Drug_Form varchar2(30) path '//ns:MedicationPrescribed/ns:DrugCoded/ns:FormCode',  \r\n" + 
        		"        		    rx_Drug_Strength varchar2(70) path '//ns:MedicationPrescribed/ns:DrugCoded/ns:Strength',  \r\n" + 
        		"					rx_Drug_Strength_Code varchar2(30) path '//ns:MedicationPrescribed/ns:DrugCoded/ns:StrengthCode',  \r\n" +
        		"        		    rx_Refills varchar2(2) path '//ns:MedicationPrescribed/ns:Refills/ns:Value',  \r\n" + 
        		"        		    rx_Sig varchar2(140) path '//ns:MedicationPrescribed/ns:Directions',  \r\n" + 
        		"        		    rx_Dispense_Notes varchar2(35) path '//ns:MedicationPrescribed/ns:Substitutions',  \r\n" + 
        		"        		    rx_Comments varchar2(210) path '//ns:MedicationPrescribed/ns:Note',  \r\n" + 
        		"             		res_type varchar2(70) path 'name(/ns:Body/*/ns:Response/*[1])', \r\n" + 
        		"             		req_refno varchar2(70) path '//ns:Request/ns:Request/ns:ReferenceNumber',\r\n" + 
        		"             		res_aprv_reason_cd varchar2(70) path '//ns:Response/ns:Approved/ns:ApprovalReasonCode[1]',\r\n" + 
        		"             		res_aprv_refno varchar2(70) path '//ns:Response/ns:Approved/ns:ReferenceNumber',\r\n" + 
        		"             		res_aprv_note varchar2(70) path '//ns:Response/ns:Approved/ns:Note',\r\n" + 
        		"             		res_aprv_w_chng_reason_cd varchar2(70) path '//ns:Response/ns:ApprovedWithChanges/ns:DenialReasonCode[1]',\r\n" + 
        		"             		res_aprv_w_chng_refno varchar2(70) path '//ns:Response/ns:ApprovedWithChanges/ns:ReferenceNumber',\r\n" + 
        		"             		res_aprv_w_chng_note varchar2(70) path '//ns:Response/ns:ApprovedWithChanges/ns:Note',\r\n" + 
        		"             		res_denial_reason_cd varchar2(70) path '//ns:Response/ns:Denied/ns:DenialReasonCode[1]',\r\n" + 
        		"             		res_denial_refno varchar2(70) path '//ns:Response/ns:Denied/ns:ReferenceNumber',\r\n" + 
        		"              		res_denial_reason varchar2(70) path '//ns:Response/ns:Denied/ns:DenialReason',\r\n" + 
        		"              		res_denial_nrx_reason_cd varchar2(70) path '//ns:Response/ns:DeniedNewPrescriptionToFollow/ns:DenialReasonCode[1]',\r\n" + 
        		"              		res_denial_nrx_refno varchar2(70) path '//ns:Response/ns:DeniedNewPrescriptionToFollow/ns:ReferenceNumber',\r\n" + 
        		"              		res_denial_nrx_reason varchar2(70) path '//ns:Response/ns:DeniedNewPrescriptionToFollow/ns:DenialReason' ,\r\n" +
        		"              		esv_description_code varchar2(70) path '//ns:DescriptionCode[1]',\r\n" + 
        		"              		esv_code varchar2(70) path '//ns:Code',\r\n" + 
        		"              		esv_description varchar2(70) path '//ns:Description' \r\n" +
        		"        		    ) x,  \r\n" + 
        		"        		    (select pharmacy_com.pharmacy_number pharmacy_number,  \r\n" + 
        		"        		    pharmacy_com.pharmacy_qualifier pharmacy_qualifier,  \r\n" + 
        		"        		    t.inbound_ncpdp_msg_id inbound_ncpdp_msg_id  \r\n" + 
        		"        		    from erx.inbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
        		"        		    passing t.message  \r\n" + 
        		"        		    columns records XMLTYPE PATH '//ns:Pharmacy/ns:CommunicationNumbers') r,  \r\n" + 
        		"        		    xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"),'//ns:Communication'  \r\n" + 
        		"        		    passing r.records  \r\n" + 
        		"        		    columns  \r\n" + 
        		"        		    pharmacy_number varchar2(80) path '//ns:Number',  \r\n" + 
        		"        		    pharmacy_qualifier varchar2(2) path '//ns:Qualifier') pharmacy_com  \r\n" + 
        		"        		    where pharmacy_com.pharmacy_qualifier = 'TE') pharmacy_phone,  \r\n" + 
        		"        		    (select prescriber_com.prescriber_number prescriber_number,  \r\n" + 
        		"        		    prescriber_com.prescriber_qualifier prescriber_qualifier,  \r\n" + 
        		"        		    t.inbound_ncpdp_msg_id inbound_ncpdp_msg_id  \r\n" + 
        		"        		    from erx.inbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
        		"        		    passing t.message  \r\n" + 
        		"        		    columns records XMLTYPE PATH '//ns:Prescriber/ns:CommunicationNumbers') r,  \r\n" + 
        		"        		    xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"),'//ns:Communication'  \r\n" + 
        		"        		    passing r.records  \r\n" + 
        		"        		    columns  \r\n" + 
        		"        		    prescriber_number varchar2(80) path '//ns:Number',  \r\n" + 
        		"        		    prescriber_qualifier varchar2(2) path '//ns:Qualifier') prescriber_com  \r\n" + 
        		"        		    where prescriber_com.prescriber_qualifier = 'TE') prescriber_phone,  \r\n" + 
        		"        		    (select prescriber_com.prescriber_number prescriber_faxnumber,  \r\n" + 
        		"        		    prescriber_com.prescriber_qualifier prescriber_qualifier,  \r\n" + 
        		"        		    t.inbound_ncpdp_msg_id inbound_ncpdp_msg_id  \r\n" + 
        		"        		    from erx.inbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
        		"        		    passing t.message  \r\n" + 
        		"        		    columns records XMLTYPE PATH '//ns:Prescriber/ns:CommunicationNumbers') r,  \r\n" + 
        		"        		    xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"),'//ns:Communication'  \r\n" + 
        		"        		    passing r.records  \r\n" + 
        		"        		    columns  \r\n" + 
        		"        		    prescriber_number varchar2(80) path '//ns:Number',  \r\n" + 
        		"        		    prescriber_qualifier varchar2(2) path '//ns:Qualifier') prescriber_com  \r\n" + 
        		"        		    where prescriber_com.prescriber_qualifier = 'FX') prescriber_fax,  \r\n" + 
        		"					(select benefits.cardholder_id cardholder_id ,  \r\n" + 
        		"        		    benefits.groupID groupID, \r\n" + 
        		"                   benefits.rx_Pcn,\r\n" + 
        		"                   benefits.rx_Bin_Num,\r\n" + 
        		"        		    t.inbound_ncpdp_msg_id inbound_ncpdp_msg_id  \r\n" + 
        		"        		    from erx.inbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
        		"        		    passing t.message  \r\n" + 
        		"        		    columns \r\n" + 
        		"                   rx_Pcn varchar2(35) path '//ns:BenefitsCoordination[1]/ns:PayerIdentification/ns:ProcessorIdentificationNumber',  \r\n" + 
        		"        		    rx_Bin_Num varchar2(35) path '//ns:BenefitsCoordination[1]/ns:PayerIdentification/ns:BINLocationNumber',\r\n" + 
        		"        		    cardholder_id varchar2(35) path '//ns:BenefitsCoordination[1]/ns:CardholderID',  \r\n" + 
        		"        		    groupID varchar2(35) path '//ns:BenefitsCoordination[1]/ns:GroupID') benefits) benefits1 \r\n" +
        		"        		    where t.inbound_ncpdp_msg_id = pharmacy_phone.inbound_ncpdp_msg_id (+)  \r\n" + 
        		"        			and t.inbound_ncpdp_msg_id = prescriber_fax.inbound_ncpdp_msg_id (+)  \r\n" + 
        		"        		    and t.inbound_ncpdp_msg_id = prescriber_phone.inbound_ncpdp_msg_id (+)  \r\n" + 
        		"					and t.inbound_ncpdp_msg_id = benefits1.inbound_ncpdp_msg_id (+) \r\n " +
        		"        		    and t.inbound_ncpdp_msg_id = ? ";
		} else {
	        sql = "select t.outbound_ncpdp_msg_id inbound_ncpdp_msg_id,  \r\n" + 
	        		"        		  x.message_id rx_messageId,  \r\n" + 
	        		"        		  x.rel_to_message_id,  \r\n" + 
	        		"        		  x.message_type,  \r\n" + 
	        		"				  (select code_description from erx_status where code = t.message_status) message_status, \r\n"	+	
	        		"        		  to_char(t.received_date,'MM/DD/YYYY') received_date,  \r\n" + 
	        		"        		  x.pharmacy_name,  \r\n" + 
	        		"        		  x.pharmacy_addr_1||' '||x.pharmacy_addr_2 pharmacy_addr_1,  \r\n" + 
	        		"        		  x.pharmacy_city||', '||x.pharmacy_state||'  '||x.pharmacy_zip pharmacy_addr_2,  \r\n" + 
	        		"        		pharmacy_phone.pharmacy_number pharmacy_phone,  \r\n" + 
	        		"        		nvl(x.pharmacy_ncpdpid1,x.pharmacy_ncpdpid2) pharmacy_ncpdpid,   \r\n" + 
	        		"        		  x.prescriber_First_Name,  \r\n" + 
	        		"        		  x.prescriber_Mid_Name,  \r\n" + 
	        		"        		  x.prescriber_Last_Name,  \r\n" + 
	        		"        		  x.prescriber_addr_1||' '||x.prescriber_addr_2 prescriber_addr_1,  \r\n" + 
	        		"        		  x.prescriber_city||', '||x.prescriber_state||'  '||x.prescriber_zip prescriber_addr_2,  \r\n" + 
	        		"        		  x.npi1 prescriber_NPI,  \r\n" + 
	        		"        		  x.npi2 clinic_id,  \r\n" + 
	        		"        		  x.prescriber_DEA,  \r\n" + 
	        		"        		  x.prescriber_State_Lic,  \r\n" + 
	        		"        		prescriber_fax.prescriber_faxnumber prescriber_fax, \r\n" + 
	        		"        		prescriber_phone.prescriber_number prescriber_phone,  \r\n" + 
	        		"        		  x.prescriber_agent_first_name||' '||x.prescriber_agent_last_name prescriber_Agent,  \r\n" + 
	        		"        		  x.patient_First_Name,  \r\n" + 
	        		"        		  x.patient_Mid_Name,  \r\n" + 
	        		"        		  x.patient_Last_Name,  \r\n" + 
	        		"        		  x.patient_addr_1||' '||x.patient_addr_2 patient_addr_1,  \r\n" + 
	        		"        		  x.patient_city||', '||x.patient_state||'  '||x.patient_zip patient_addr_2,  \r\n" + 
	        		"        		to_char(to_date(decode(length(x.patient_dob),11,substr(x.patient_dob, 2,10), x.patient_dob),'YYYY-MM-DD'),'MM/DD/YYYY') patient_dob,  \r\n" + 
	        		"        		  x.patient_gender patient_gender,  \r\n" + 
	        		"        		  benefits1.cardholder_id plan_id,  \r\n" + 
	        		"        		  x.rx_Drug_Prescribed,  \r\n" + 
	        		"        		  x.rx_Quantity,  \r\n" + 
	        		"        		  x.rx_Days_Supply,  \r\n" + 
	        		"        	  	to_char(to_date(decode(length(x.rx_Date_Written),11,substr(x.rx_Date_Written, 2,10), x.rx_Date_Written),'YYYY-MM-DD'),'MM/DD/YYYY') rx_Date_Written,  \r\n" + 
	        		"        		  (select distinct ncit_pref_term from ncpdp_code where ncit_code = x.rx_Potency_Unit_Code) rx_Potency_Unit_Code,  \r\n" + 
	        		"        		  (select distinct ncit_pref_term from ncpdp_code where ncit_code = x.rx_Drug_Form) rx_Drug_Form,  \r\n" + 
	        		"        		  x.rx_Drug_Strength||' '||(select distinct ncit_pref_term from ncpdp_code where ncit_code = x.rx_Drug_Strength_Code) rx_Drug_Strength,  \r\n" + 
	        		"        		  x.rx_Refills,  \r\n" + 
	        		"        		  x.rx_Sig,\r\n" + 
	        		"              		decode(x.rx_Dispense_Notes,'0','0 = No Product Selection Indicated','1','1 = Substitution Not Allowed by Prescriber','2','2 = Substitution Allowed-Patient Requested Product Dispensed','3','3 = Substitution Allowed-Pharmacist Selected Product Dispensed','4','4 = Substitution Allowed-Generic Drug Not in Stock','5','5 = Substitution Allowed-Brand Drug Dispensed as a Generic','6','6 = Override','7','7 = Substitution Not Allowed-Brand Mandated by Law','8','8 = Substitution Allowed-Generic Drug Not Available in Marketplace','9','9 = Substitution Allowed By Prescriber but Plan Requests Brand - Patient''s Plan Requested Brand Product To Be Dispensed') rx_Dispense_Notes,\r\n" + 
	        		"					x.rx_Comments,  \r\n" + 
	        		"        		  benefits1.groupID rx_Grp,\r\n" + 
	        		"        		  benefits1.rx_Bin_Num rx_Bin_Num,  \r\n" + 
	        		"        		  benefits1.rx_Pcn rx_Pcn, \r\n" + 
	        		"                 x.res_type, \r\n" + 
	        		"                 x.req_refno,\r\n" + 
	        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_aprv_reason_cd) res_aprv_reason_cd, \r\n" + 
	        		"                 x.res_aprv_refno, \r\n" + 
	        		"                 x.res_aprv_note, \r\n" + 
	        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_aprv_w_chng_reason_cd) res_aprv_w_chng_reason_cd, \r\n" + 
	        		"                 x.res_aprv_w_chng_refno, \r\n" + 
	        		"                 x.res_aprv_w_chng_note, \r\n" + 
	        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_denial_reason_cd) res_denial_reason_cd, \r\n" + 
	        		"                 x.res_denial_refno, \r\n" + 
	        		"                 x.res_denial_reason, \r\n" + 
	        		"                 (select code||':  '||definition from ecl_code where code_type = '1131 – Code List Qualifier – Response Code - RES Segment' and code = x.res_denial_nrx_reason_cd) res_denial_nrx_reason_cd, \r\n" + 
	        		"                 x.res_denial_nrx_refno, \r\n" + 
	        		"                 x.res_denial_nrx_reason, " +
	        		"                 x.esv_description_code, " +
	        		"                 x.esv_code, " +
	        		"                 x.esv_description " +
	        		"              from outbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
	        		"        		    passing t.message  \r\n" + 
	        		"        		    columns   \r\n" + 
	        		"        		    message_id varchar2(35) path '//ns:Header/ns:MessageID',  \r\n" + 
	        		"        		    rel_to_message_id varchar2(35) path '//ns:Header/ns:RelatesToMessageID',  \r\n" + 
	        		"        		    message_type varchar2(35) path 'name(/ns:Body/*[1])',  \r\n" + 
	        		"        		    pharmacy_name varchar2(35) path '//ns:Pharmacy/ns:StoreName',  \r\n" + 
	        		"        		    pharmacy_addr_1 varchar2(35) path '//ns:Pharmacy/ns:Address/ns:AddressLine1',  \r\n" + 
	        		"        		    pharmacy_addr_2 varchar2(35) path '//ns:Pharmacy/ns:Address/ns:AddressLine2',  \r\n" + 
	        		"        		    pharmacy_city varchar2(35) path '//ns:Pharmacy/ns:Address/ns:City',  \r\n" + 
	        		"        		    pharmacy_state varchar2(2) path '//ns:Pharmacy/ns:Address/ns:State',  \r\n" + 
	        		"        		    pharmacy_zip varchar2(14) path '//ns:Pharmacy/ns:Address/ns:ZipCode',  \r\n" + 
	        		"        		    pharmacy_ncpdpid1 varchar2(35) path '//ns:Pharmacy/ns:Identification/ns:NCPDPID',  \r\n" + 
	        		"        		    pharmacy_ncpdpid2 varchar2(35) path '//ns:Header/ns:From',  \r\n" + 
	        		"        		    npi1 varchar2(35) path '//ns:Prescriber/ns:Identification/ns:NPI',  \r\n" + 
	        		"        		    npi2 varchar2(35) path '//ns:Header/ns:To',  \r\n" + 
	        		"        		    prescriber_First_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:FirstName',  \r\n" + 
	        		"        		    prescriber_Mid_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:MiddleName',  \r\n" + 
	        		"        		    prescriber_Last_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:LastName',  \r\n" + 
	        		"        		    prescriber_addr_1 varchar2(35) path '//ns:Prescriber/ns:Address/ns:AddressLine1',  \r\n" + 
	        		"        		    prescriber_addr_2 varchar2(35) path '//ns:Prescriber/ns:Address/ns:AddressLine2',  \r\n" + 
	        		"        		    prescriber_city varchar2(35) path '//ns:Prescriber/ns:Address/ns:City',  \r\n" + 
	        		"        		    prescriber_state varchar2(2) path '//ns:Prescriber/ns:Address/ns:State',  \r\n" + 
	        		"        		    prescriber_zip varchar2(14) path '//ns:Prescriber/ns:Address/ns:ZipCode',  \r\n" + 
	        		"        		    prescriber_agent_last_name varchar2(35) path '//ns:Prescriber/ns:PrescriberAgent/ns:LastName',  \r\n" + 
	        		"        		    prescriber_agent_first_name varchar2(35) path '//ns:Prescriber/ns:PrescriberAgent/ns:FirstName',  \r\n" + 
	        		"        		    prescriber_DEA varchar2(35) path '//ns:Prescriber/ns:Identification/ns:DEANumber',  \r\n" + 
	        		"        		    prescriber_State_Lic varchar2(35) path '//ns:Prescriber/ns:Identification/ns:StateLicenseNumber',  \r\n" + 
	        		"        		    patient_First_Name varchar2(35) path '//ns:Patient/ns:Name/ns:FirstName',  \r\n" + 
	        		"        		    patient_Mid_Name varchar2(35) path '//ns:Patient/ns:Name/ns:MiddleName',  \r\n" + 
	        		"        		    patient_Last_Name varchar2(35) path '//ns:Patient/ns:Name/ns:LastName',  \r\n" + 
	        		"        		    patient_addr_1 varchar2(35) path '//ns:Patient/ns:Address/ns:AddressLine1',  \r\n" + 
	        		"        		    patient_addr_2 varchar2(35) path '//ns:Patient/ns:Address/ns:AddressLine2',  \r\n" + 
	        		"        		    patient_city varchar2(35) path '//ns:Patient/ns:Address/ns:City',  \r\n" + 
	        		"        		    patient_state varchar2(2) path '//ns:Patient/ns:Address/ns:State',  \r\n" + 
	        		"        		    patient_zip varchar2(14) path '//ns:Patient/ns:Address/ns:ZipCode',  \r\n" + 
	        		"        		    patient_dob varchar2(30) path '//ns:Patient/ns:DateOfBirth/ns:Date',  \r\n" + 
	        		"        		    patient_gender varchar2(1) path '//ns:Patient/ns:Gender',  \r\n" + 
	        		"        		    rx_Drug_Prescribed varchar2(105) path '//ns:MedicationPrescribed/ns:DrugDescription',  \r\n" + 
	        		"        		    rx_Quantity varchar2(30) path '//ns:MedicationPrescribed/ns:Quantity/ns:Value',  \r\n" + 
	        		"        		    rx_Days_Supply varchar2(30) path '//ns:MedicationPrescribed/ns:DaysSupply',  \r\n" + 
	        		"        		    rx_Date_Written varchar2(30) path '//ns:MedicationPrescribed/ns:WrittenDate',  \r\n" + 
	        		"        		    rx_Potency_Unit_Code varchar2(30) path '//ns:MedicationPrescribed/ns:Quantity/ns:PotencyUnitCode',  \r\n" + 
	        		"        		    rx_Drug_Form varchar2(30) path '//ns:MedicationPrescribed/ns:DrugCoded/ns:FormCode',  \r\n" + 
	        		"        		    rx_Drug_Strength varchar2(70) path '//ns:MedicationPrescribed/ns:DrugCoded/ns:Strength',  \r\n" +
	        		"					rx_Drug_Strength_Code varchar2(30) path '//ns:MedicationPrescribed/ns:DrugCoded/ns:StrengthCode',  \r\n" +
	        		"        		    rx_Refills varchar2(2) path '//ns:MedicationPrescribed/ns:Refills/ns:Value',  \r\n" + 
	        		"        		    rx_Sig varchar2(140) path '//ns:MedicationPrescribed/ns:Directions',  \r\n" + 
	        		"        		    rx_Dispense_Notes varchar2(35) path '//ns:MedicationPrescribed/ns:Substitutions',  \r\n" + 
	        		"        		    rx_Comments varchar2(210) path '//ns:MedicationPrescribed/ns:Note',  \r\n" + 
	        		"             		res_type varchar2(70) path 'name(/ns:Body/*/ns:Response/*[1])', \r\n" + 
	        		"             		req_refno varchar2(70) path '//ns:Request/ns:Request/ns:ReferenceNumber',\r\n" + 
	        		"             		res_aprv_reason_cd varchar2(70) path '//ns:Response/ns:Approved/ns:ApprovalReasonCode[1]',\r\n" + 
	        		"             		res_aprv_refno varchar2(70) path '//ns:Response/ns:Approved/ns:ReferenceNumber',\r\n" + 
	        		"             		res_aprv_note varchar2(70) path '//ns:Response/ns:Approved/ns:Note',\r\n" + 
	        		"             		res_aprv_w_chng_reason_cd varchar2(70) path '//ns:Response/ns:ApprovedWithChanges/ns:DenialReasonCode[1]',\r\n" + 
	        		"             		res_aprv_w_chng_refno varchar2(70) path '//ns:Response/ns:ApprovedWithChanges/ns:ReferenceNumber',\r\n" + 
	        		"             		res_aprv_w_chng_note varchar2(70) path '//ns:Response/ns:ApprovedWithChanges/ns:Note',\r\n" + 
	        		"             		res_denial_reason_cd varchar2(70) path '//ns:Response/ns:Denied/ns:DenialReasonCode[1]',\r\n" + 
	        		"             		res_denial_refno varchar2(70) path '//ns:Response/ns:Denied/ns:ReferenceNumber',\r\n" + 
	        		"              		res_denial_reason varchar2(70) path '//ns:Response/ns:Denied/ns:DenialReason',\r\n" + 
	        		"              		res_denial_nrx_reason_cd varchar2(70) path '//ns:Response/ns:DeniedNewPrescriptionToFollow/ns:DenialReasonCode[1]',\r\n" + 
	        		"              		res_denial_nrx_refno varchar2(70) path '//ns:Response/ns:DeniedNewPrescriptionToFollow/ns:ReferenceNumber',\r\n" + 
	        		"              		res_denial_nrx_reason varchar2(70) path '//ns:Response/ns:DeniedNewPrescriptionToFollow/ns:DenialReason' ,\r\n" +
	        		"              		esv_description_code varchar2(70) path '//ns:DescriptionCode[1]',\r\n" + 
	        		"              		esv_code varchar2(70) path '//ns:Code',\r\n" + 
	        		"              		esv_description varchar2(70) path '//ns:Description' \r\n" +
	        		"        		    ) x,  \r\n" + 
	        		"        		    (select pharmacy_com.pharmacy_number pharmacy_number,  \r\n" + 
	        		"        		    pharmacy_com.pharmacy_qualifier pharmacy_qualifier,  \r\n" + 
	        		"        		    t.outbound_ncpdp_msg_id outbound_ncpdp_msg_id  \r\n" + 
	        		"        		    from outbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
	        		"        		    passing t.message  \r\n" + 
	        		"        		    columns records XMLTYPE PATH '//ns:Pharmacy/ns:CommunicationNumbers') r,  \r\n" + 
	        		"        		    xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"),'//ns:Communication'  \r\n" + 
	        		"        		    passing r.records  \r\n" + 
	        		"        		    columns  \r\n" + 
	        		"        		    pharmacy_number varchar2(80) path '//ns:Number',  \r\n" + 
	        		"        		    pharmacy_qualifier varchar2(2) path '//ns:Qualifier') pharmacy_com  \r\n" + 
	        		"        		    where pharmacy_com.pharmacy_qualifier = 'TE') pharmacy_phone,  \r\n" + 
	        		"        		    (select prescriber_com.prescriber_number prescriber_number,  \r\n" + 
	        		"        		    prescriber_com.prescriber_qualifier prescriber_qualifier,  \r\n" + 
	        		"        		    t.outbound_ncpdp_msg_id outbound_ncpdp_msg_id  \r\n" + 
	        		"        		    from outbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
	        		"        		    passing t.message  \r\n" + 
	        		"        		    columns records XMLTYPE PATH '//ns:Prescriber/ns:CommunicationNumbers') r,  \r\n" + 
	        		"        		    xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"),'//ns:Communication'  \r\n" + 
	        		"        		    passing r.records  \r\n" + 
	        		"        		    columns  \r\n" + 
	        		"        		    prescriber_number varchar2(80) path '//ns:Number',  \r\n" + 
	        		"        		    prescriber_qualifier varchar2(2) path '//ns:Qualifier') prescriber_com  \r\n" + 
	        		"        		    where prescriber_com.prescriber_qualifier = 'TE') prescriber_phone,  \r\n" + 
	        		"        		    (select prescriber_com.prescriber_number prescriber_faxnumber,  \r\n" + 
	        		"        		    prescriber_com.prescriber_qualifier prescriber_qualifier,  \r\n" + 
	        		"        		    t.outbound_ncpdp_msg_id outbound_ncpdp_msg_id  \r\n" + 
	        		"        		    from outbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
	        		"        		    passing t.message  \r\n" + 
	        		"        		    columns records XMLTYPE PATH '//ns:Prescriber/ns:CommunicationNumbers') r,  \r\n" + 
	        		"        		    xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"),'//ns:Communication'  \r\n" + 
	        		"        		    passing r.records  \r\n" + 
	        		"        		    columns  \r\n" + 
	        		"        		    prescriber_number varchar2(80) path '//ns:Number',  \r\n" + 
	        		"        		    prescriber_qualifier varchar2(2) path '//ns:Qualifier') prescriber_com  \r\n" + 
	        		"        		    where prescriber_com.prescriber_qualifier = 'FX') prescriber_fax,  \r\n" + 
	        		"					(select benefits.cardholder_id cardholder_id ,  \r\n" + 
	        		"        		    benefits.groupID groupID, \r\n" + 
	        		"                   benefits.rx_Pcn,\r\n" + 
	        		"                   benefits.rx_Bin_Num,\r\n" + 
	        		"        		    t.outbound_ncpdp_msg_id outbound_ncpdp_msg_id  \r\n" + 
	        		"        		    from outbound_ncpdp_msg t, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'  \r\n" + 
	        		"        		    passing t.message  \r\n" + 
	        		"        		    columns \r\n" + 
	        		"                   rx_Pcn varchar2(35) path '//ns:BenefitsCoordination[1]/ns:PayerIdentification/ns:ProcessorIdentificationNumber',  \r\n" + 
	        		"        		    rx_Bin_Num varchar2(35) path '//ns:BenefitsCoordination[1]/ns:PayerIdentification/ns:BINLocationNumber',\r\n" + 
	        		"        		    cardholder_id varchar2(35) path '//ns:BenefitsCoordination[1]/ns:CardholderID',  \r\n" + 
	        		"        		    groupID varchar2(35) path '//ns:BenefitsCoordination[1]/ns:GroupID') benefits) benefits1 \r\n" +
	        		"        		    where t.outbound_ncpdp_msg_id = pharmacy_phone.outbound_ncpdp_msg_id (+)  \r\n" + 
	        		"        			and t.outbound_ncpdp_msg_id = prescriber_fax.outbound_ncpdp_msg_id (+)  \r\n" + 
	        		"        		    and t.outbound_ncpdp_msg_id = prescriber_phone.outbound_ncpdp_msg_id (+)  \r\n" + 
	        		"					and t.outbound_ncpdp_msg_id = benefits1.outbound_ncpdp_msg_id (+) \r\n " +
	        		"        		    and t.outbound_ncpdp_msg_id = ? ";
		};
        		//System.out.println("sql is:" + sql);

		try {
			LOG.info("Retrieving NCPDP message details.");
			ncpdpMsg = (NcpdpMessageModel) jdbcTemplate.queryForObject(sql, new Object[] { id },new NcpdpMsgRowMapper());
		} catch (DataAccessException e) {
			ncpdpMsg.setDataError(e.getMessage());
			LOG.info("Exception retrieving NCPDP message details." + e.getMessage());
		}
        
		return ncpdpMsg;

	}
	
	@Override
	public List<NcpdpMessageListModel> searchMessages(String messageType, String messageId, String relatesToId, String visn, String vaStationId,
			String fromDate, String toDate, String patientSsn, String patientLastName, String patientFirstName, String patientDob, String prescriberNpi,
			String prescriberLastName, String prescriberFirstName, String prescribedDrug, String messageStatus, String inboundNcpdpMsgId, String inboundOutbound) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "";
		if (inboundOutbound.equalsIgnoreCase("Inbound")) {	
        sql = "select t.inbound_ncpdp_msg_id inbound_ncpdp_msg_id,\r\n" + 
                "p.visn visn,\r\n" +
                "p.va_station_id va_station_id,\r\n" +
                "x.npi1 prescriber_npi,\r\n" +
                "x.patient_dob patient_dob,\r\n" +
                "x.patient_ssn patient_ssn,\r\n" +
        		"x.pharmacy_name pharmacy_name,\r\n" + 
        		"x.pharmacy_addr_1||' '||x.pharmacy_addr_2||' '||x.pharmacy_city||nvl2(x.pharmacy_city,nvl2(x.pharmacy_state,', ',null),null)||x.pharmacy_state||' '||x.pharmacy_zip pharmacy_addr_1,\r\n" + 
        		"x.patient_Last_Name||nvl2(x.patient_Last_Name,nvl2(x.patient_First_Name,', ',null),null)||x.patient_First_Name||' '||x.patient_Mid_Name patient_Name,\r\n" + 
        		"x.prescriber_Last_Name||nvl2(x.prescriber_Last_Name,nvl2(x.prescriber_First_Name,', ',null),null)||x.prescriber_First_Name||' '||x.prescriber_Mid_Name prescriber_Name,\r\n" + 
        		"x.rx_Drug_Prescribed rx_Drug_Prescribed,\r\n" + 
        		"t.message_type message_type,\r\n" + 
        		"t.rel_to_message_id rel_to_message_id,\r\n" + 
        		"t.message_id rx_messageId,\r\n" + 
        		"t.received_date received_date,\r\n" + 
        		"(select code_description from erx_status where code = t.patient_chk_status) patient_chk_status,\r\n" + 
        		"(select code_description from erx_status where code = t.provider_chk_status) provider_chk_status,\r\n" + 
        		"(select code_description from erx_status where code = t.drug_chk_status) drug_chk_status,\r\n" + 
        		"(select code_description from erx_status where code = t.message_status) message_status\r\n" + 
        		"from inbound_ncpdp_msg t, pharmacy p, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'\r\n" + 
        		"    passing t.message\r\n" + 
        		"    columns \r\n" + 
        		"    message_id varchar2(35) path '//ns:Header/ns:MessageID',\r\n" + 
        		"    rel_to_message_id varchar2(35) path '//ns:Header/ns:RelatesToMessageID',\r\n" + 
        		"    message_type varchar2(35) path 'name(/ns:Body/*[1])',\r\n" + 
        		"    pharmacy_name varchar2(30) path '//ns:Pharmacy/ns:StoreName',\r\n" + 
        		"    pharmacy_addr_1 varchar2(30) path '//ns:Pharmacy/ns:Address/ns:AddressLine1',\r\n" + 
        		"    pharmacy_addr_2 varchar2(30) path '//ns:Pharmacy/ns:Address/ns:AddressLine2',\r\n" + 
        		"    pharmacy_city varchar2(30) path '//ns:Pharmacy/ns:Address/ns:City',\r\n" + 
        		"    pharmacy_state varchar2(30) path '//ns:Pharmacy/ns:Address/ns:State',\r\n" + 
        		"    pharmacy_zip varchar2(30) path '//ns:Pharmacy/ns:Address/ns:ZipCode',\r\n" + 
        		"	 npi1 varchar2(35) path '//ns:Prescriber/ns:Identification/ns:NPI',\r\n" + 
        		"    npi2 varchar2(35) path '//ns:Header/ns:From',\r\n" + 
        		"    prescriber_First_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:FirstName',\r\n" + 
        		"    prescriber_Mid_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:MiddleName',\r\n" + 
        		"    prescriber_Last_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:LastName',\r\n" + 
        		"    patient_First_Name varchar2(35) path '//ns:Patient/ns:Name/ns:FirstName',\r\n" + 
        		"    patient_Mid_Name varchar2(35) path '//ns:Patient/ns:Name/ns:MiddleName',\r\n" + 
        		"    patient_Last_Name varchar2(35) path '//ns:Patient/ns:Name/ns:LastName',\r\n" + 
        		"    patient_addr_1 varchar2(30) path '//ns:Patient/ns:Address/ns:AddressLine1',\r\n" + 
        		"    patient_addr_2 varchar2(30) path '//ns:Patient/ns:Address/ns:AddressLine2',\r\n" + 
        		"    patient_city varchar2(30) path '//ns:Patient/ns:Address/ns:City',\r\n" + 
        		"    patient_state varchar2(30) path '//ns:Patient/ns:Address/ns:State',\r\n" + 
        		"    patient_zip varchar2(30) path '//ns:Patient/ns:Address/ns:ZipCode',\r\n" + 
        		"    patient_dob varchar2(30) path '//ns:Patient/ns:DateOfBirth/ns:Date',\r\n" + 
        		"    patient_ssn varchar2(11) path '//ns:Patient/ns:Identification/ns:SocialSecurity',\r\n" + 
        		"    plan_id varchar2(30) path '//ns:Patient/ns:BenefitsCoordination/ns:CardholderID',\r\n" + 
        		"    rx_Drug_Prescribed varchar2(30) path '//ns:MedicationPrescribed/ns:DrugDescription'\r\n" + 
        		"    ) x\r\n";
		} else {
			sql = "select t.outbound_ncpdp_msg_id inbound_ncpdp_msg_id,\r\n" +
	                "p.visn visn,\r\n" +
	                "p.va_station_id va_station_id,\r\n" +
	                "x.npi1 prescriber_npi,\r\n" +
	                "x.patient_dob patient_dob,\r\n" +
	                "x.patient_ssn patient_ssn,\r\n" +
	        		"x.pharmacy_name pharmacy_name,\r\n" + 					
	        		"x.pharmacy_addr_1||' '||x.pharmacy_addr_2||' '||x.pharmacy_city||nvl2(x.pharmacy_city,nvl2(x.pharmacy_state,', ',null),null)||x.pharmacy_state||' '||x.pharmacy_zip pharmacy_addr_1,\r\n" + 
	        		"x.patient_Last_Name||nvl2(x.patient_Last_Name,nvl2(x.patient_First_Name,', ',null),null)||x.patient_First_Name||' '||x.patient_Mid_Name patient_Name,\r\n" + 
	        		"x.prescriber_Last_Name||nvl2(x.prescriber_Last_Name,nvl2(x.prescriber_First_Name,', ',null),null)||x.prescriber_First_Name||' '||x.prescriber_Mid_Name prescriber_Name,\r\n" + 
	        		"x.rx_Drug_Prescribed rx_Drug_Prescribed,\r\n" + 
	        		"t.message_type message_type,\r\n" + 
	        		"t.rel_to_message_id rel_to_message_id,\r\n" + 
	        		"t.message_id rx_messageId,\r\n" + 
	        		"t.received_date received_date,\r\n" + 
	        		"'N/A' patient_chk_status,\r\n" + 
	        		"'N/A' provider_chk_status,\r\n" + 
	        		"'N/A' drug_chk_status,\r\n" + 
	        		"(select code_description from erx_status where code = t.message_status) message_status\r\n" + 
	        		"from outbound_ncpdp_msg t, pharmacy p, xmltable(xmlnamespaces('http://www.ncpdp.org/schema/SCRIPT' as \"ns\"), '/ns:Message'\r\n" + 
	        		"    passing t.message\r\n" + 
	        		"    columns \r\n" + 
	        		"    message_id varchar2(35) path '//ns:Header/ns:MessageID',\r\n" + 
	        		"    rel_to_message_id varchar2(35) path '//ns:Header/ns:RelatesToMessageID',\r\n" + 
	        		"    message_type varchar2(35) path 'name(/ns:Body/*[1])',\r\n" + 
	        		"    pharmacy_name varchar2(30) path '//ns:Pharmacy/ns:StoreName',\r\n" + 
	        		"    pharmacy_addr_1 varchar2(30) path '//ns:Pharmacy/ns:Address/ns:AddressLine1',\r\n" + 
	        		"    pharmacy_addr_2 varchar2(30) path '//ns:Pharmacy/ns:Address/ns:AddressLine2',\r\n" + 
	        		"    pharmacy_city varchar2(30) path '//ns:Pharmacy/ns:Address/ns:City',\r\n" + 
	        		"    pharmacy_state varchar2(30) path '//ns:Pharmacy/ns:Address/ns:State',\r\n" + 
	        		"    pharmacy_zip varchar2(30) path '//ns:Pharmacy/ns:Address/ns:ZipCode',\r\n" + 
	        		"	 npi1 varchar2(35) path '//ns:Prescriber/ns:Identification/ns:NPI',\r\n" + 
	        		"    npi2 varchar2(35) path '//ns:Header/ns:From',\r\n" + 
	        		"    prescriber_First_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:FirstName',\r\n" + 
	        		"    prescriber_Mid_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:MiddleName',\r\n" + 
	        		"    prescriber_Last_Name varchar2(35) path '//ns:Prescriber/ns:Name/ns:LastName',\r\n" + 
	        		"    patient_First_Name varchar2(35) path '//ns:Patient/ns:Name/ns:FirstName',\r\n" + 
	        		"    patient_Mid_Name varchar2(35) path '//ns:Patient/ns:Name/ns:MiddleName',\r\n" + 
	        		"    patient_Last_Name varchar2(35) path '//ns:Patient/ns:Name/ns:LastName',\r\n" + 
	        		"    patient_addr_1 varchar2(30) path '//ns:Patient/ns:Address/ns:AddressLine1',\r\n" + 
	        		"    patient_addr_2 varchar2(30) path '//ns:Patient/ns:Address/ns:AddressLine2',\r\n" + 
	        		"    patient_city varchar2(30) path '//ns:Patient/ns:Address/ns:City',\r\n" + 
	        		"    patient_state varchar2(30) path '//ns:Patient/ns:Address/ns:State',\r\n" + 
	        		"    patient_zip varchar2(30) path '//ns:Patient/ns:Address/ns:ZipCode',\r\n" + 
	        		"    patient_dob varchar2(30) path '//ns:Patient/ns:DateOfBirth/ns:Date',\r\n" + 
	        		"    patient_ssn varchar2(11) path '//ns:Patient/ns:Identification/ns:SocialSecurity',\r\n" + 
	        		"    plan_id varchar2(30) path '//ns:Patient/ns:BenefitsCoordination/ns:CardholderID',\r\n" + 
	        		"    rx_Drug_Prescribed varchar2(30) path '//ns:MedicationPrescribed/ns:DrugDescription'\r\n" + 
	        		"    ) x\r\n";
		};
        		
        		sql = sql + "    where t.pharmacy_id = p.pharmacy_id and t.message_id like ? \r\n"  +
        		"    and nvl(t.rel_to_message_id,' ') like ? \r\n" +
        		"    and t.RECEIVED_DATE between to_date('" + fromDate + "','YYYY-MM-DD') and to_date('" + toDate + "','YYYY-MM-DD') + 1\r\n";
        			
        		/*	
        			System.out.println("message_id:" + messageId);
        			System.out.println("rel_to_message_id:" + relatesToId);
        			System.out.println("message_type:" + messageType);
        			System.out.println("message_status:" + messageStatus);
        			System.out.println("visn:" + visn);
        			System.out.println("patient_ssn:" + patientSsn);
        			System.out.println("patient_last_name:" + patientLastName);
        			System.out.println("patient_first_name:" + patientFirstName);
        			System.out.println("prescriber_last_name:" + prescriberLastName);
        			System.out.println("prescriber_first_name:" + prescriberFirstName);
        			System.out.println("patient_dob:" + patientDob);
        			System.out.println("npi1:" + prescriberNpi);
        			System.out.println("rx_Drug_Prescribed:" + prescribedDrug);
        			System.out.println("va_station_id:" + vaStationId);
                  */ 

        		if (messageType != ""){
        			sql = sql + "    and t.message_type = '" + messageType + "' \r\n";
        		};
        		if (messageStatus != ""){
        			sql = sql +	"    and t.message_status = '" + messageStatus + "' \r\n";
        		} else { //excluding the 3006 status messages because they are invalid XML
        			sql = sql +	"    and t.message_status not in ('3006') \r\n"; 
        		};
        		if (visn != "") {
        			sql = sql + "    and t.pharmacy_id in (select pharmacy_id from pharmacy where visn = '" + visn + "')\r\n";
        		};
        			sql = sql + "    and t.pharmacy_id in (select pharmacy_id from pharmacy where va_station_id like ? ) \r\n";
        		if (patientSsn != "") {
        			sql = sql + " and x.patient_ssn = '" + patientSsn + "'\r\n";
        		};
        			sql = sql +	
        				"    and nvl(upper(x.patient_last_name),' ') like ?\r\n" + 
        				"    and nvl(upper(x.patient_first_name),' ') like ?\r\n"; 
        			
        			sql = sql +	
            				"    and nvl(upper(x.prescriber_last_name),' ') like ?\r\n" + 
            				"    and nvl(upper(x.prescriber_first_name),' ') like ?\r\n"; 
 
        		if (patientDob != "") {
        			sql = sql +   " and to_date(decode(length(x.patient_dob),11,substr(x.patient_dob, 2,10), x.patient_dob),'YYYY-MM-DD') = to_date('" + patientDob + "','YYYY-MM-DD') \r\n"; 
        		};
        			sql = sql +
        				"    and nvl(x.npi1, ' ') like ? \r\n" + 
        				"    and nvl(upper(x.rx_Drug_Prescribed),' ') like ? \r\n";
        			
        			if (inboundOutbound.equalsIgnoreCase("Inbound")){
        			sql = sql +	"    and t.inbound_ncpdp_msg_id like ? \r\n";
        			} else {
            		sql = sql +	"    and t.outbound_ncpdp_msg_id like ? \r\n";
        			}
        			sql = sql +	"	 order by t.RECEIVED_DATE DESC";
        
        			//System.out.println("sql is:" + sql);
        			
        List<NcpdpMessageListModel> ncpdpMsgList = new ArrayList<NcpdpMessageListModel>();
        
		try {
			//LOG.info("Retrieving NCPDP message details.");
			//System.out.println("Retrieving NCPDP message details.");
			 ncpdpMsgList = jdbcTemplate.query(sql,new NcpdpMsgListRowMapper(),messageId, relatesToId, vaStationId, patientLastName, patientFirstName, prescriberLastName, prescriberFirstName, prescriberNpi, prescribedDrug, inboundNcpdpMsgId);
		} catch (DataAccessException e) {
			//ncpdpMsg.setDataError(e.getMessage());
			//System.out.println("data error is:" + e.getMessage());
			LOG.info("Exception retrieving NCPDP message details." + e.getMessage());
		}
        
		return ncpdpMsgList;

	}

}
