Electronic Prescribing Integration

This document outlines the requirements for an interface between TMS Insights Prescription Tracking System (PTS) and an Electronic Prescribing (ePrescribing) module.

Overview

The reason for the interface between the two systems is to save pharmacy staff time re-entering information into PTS that has already been entered into the other system.

Whereas ePrescribing systems tend to focus on the ordering and dispensing of individual drugs and the management of stock, the focus for PTS is in managing the workflow of ‘prescriptions’ for individual patients through the whole dispensing process – from when an order is placed through to completion, collection and delivery back to the Ward.

A ‘prescription’ in PTS is defined as a group of medicines that will be processed as one unit of work for an individual patient.

There are several circumstances where PTS & ePrescribing systems need to interface:

1) When a prescription is manually entered in PTS and the user enters a patient identifier (Hospital number or NHS number). An interface is required here to return demographic information to save the user having to type these details into PTS. This interface is provided as part of the system, while it’s not required many Trusts implement this to assist booking in prescriptions.

2) When an ‘order’ is created in the ePrescribing system this information is sent to PTS so that it can be tracked. In addition to the patient demographics more information is required about the order so that PTS can group medicines into ‘prescriptions’ for each patient, assign prescription types and allocate each prescription to a Ward and Dispensary.

3) When Prescription tracking information needs to be sent back to the ePrescribing system &/or to an additional system (such as a whiteboard or bed management system) when the status of a Prescription changes &/or an activity is recorded against a Prescription.

Automatic ePrescribing links (option 2) are chargeable and you should obtain a quotation if required.

Demographics for Manual Data Entry

PTS has a ‘booking-in’ screen for entering prescription information in manually. Options can be defined (by Prescription Type) to allow the following demographics to be captured:

• Local ID (e.g. Hospital Number)
• National ID (e.g. NHS Number)
• Forename*
• Surname*
• Prescription Type
• Ward*
• No. of Items
• Drugs
  • Drug name
  • Dose units
  • Dose frequency
• Title*
• Gender*
• DOB*
• Mobile Phone no.*
• E-Mail Address*
• Address*
• Date/Time prescription requested
• Visit number / Episode ID*

A * next to an entry in the above lists indicates a field that we can potentially get from the patient demographics held in the ePrescribing system.

ODBC link

PTS can be configured (by amending a DLL file on the PTS application server) to retrieve demographic fields via an ODBC connection when a patients hospital or NHS number is entered. Typically a 64 bit system DSN would be configured to connect to the source system and PTS would use this to retrieve the demographic information for the prescription type being entered from the hospital / NHS number that was entered.

We have connected to numerous PAS systems using an ODBC connection, examples being: Careflow MMS (CACHE or IRIS), MS SQL, MySQL. Essentially - if there is a 64 bit ODBC driver available, we should be able to connect.

The queries being performed to retrieve the demographic data vary with the underlying PAS system. Many trusts set up a view or stored procedure on the PAS database that we can use to pull the required demographic data. With more common connections (Careflow for example) we have 'standard' queries that we are able to utilise, for example:

SELECT patient_hospital_no.lnkphn, patient_basic.forenames, patient_basic.surname, patient_basic.nhs_no, patient_demographics.sex, patient_demographics.telephone_no, TRIM(LEADING ', ' FROM ifnull(patient_demographics.address1,'',UPPER(patient_demographics.address1))||ifnull(patient_demographics.address2,'',','||UPPER(patient_demographics.address2))||ifnull(patient_demographics.address3,'',', '||UPPER(patient_demographics.address3))||ifnull(patient_demographics.address4,'',', '||UPPER(patient_demographics.address4))||ifnull(patient_demographics.postcode,'',','||UPPER(patient_demographics.postcode))) address, patient_demographics.birth_date FROM JAC.patient_basic patient_basic, JAC.patient_demographics patient_demographics, JAC.patient_hospital_no patient_hospital_no WHERE patient_demographics.lnkpid = patient_basic.lnkpid AND patient_hospital_no.lnkpid = patient_basic.lnkpid AND patient_hospital_no.lnkphn = ' " & PatientID.ToUpper & "'"''

HL7 Query link

We can send an HL7 as an alternative to an ODBC query. We would need details of the IP address of the system capable of responding to HL7 query and details of the format of the message that is expected and that will be returned.

An example HL7 request from PTS would look something like this:

MSH|^~\&|TMSInsight|TMSInsight|TIE|RHM|20220320090000||QRY^A19^QRY_A19|GUID|P|2.4
QRD|20220320090000|R|I|GUID|20220320090000|1^RD|^NHS|DEM

Webservice link

As an alternative to an ODBC query we are able to call SOAP or REST Web-services to obtain demographic data. We would need to know the destination URL, the type of request (GET or POST) and the parameters that need to be sent. If the web service requires additional security (e.g. a JWT token) then we would need the details to obtain this token (e.g. any client secrets required). These details will be hard-coded into PTS so will not be visible in any text files etc.

Automatic order tracking

PTS 5 has a comprehensive API that allows ePrescribing systems to send order information to PTS. This API can be called directly, or a service can be installed on the PTS application server to forward HL7 / FHIR / ODBC Queries to the API.

API documentation is available at https://prescriptiontracking.co.uk/swagger

Call PTS API directly

In order to create a Prescription via the API, PTS will require the following information as a minimum:

• The Destination Ward
• The Type of Prescription
• Patient Details

So, for example a POST could be made to http://prescriptiontracking.co.uk/api/Prescriptions/PostPrescriptionWithCreationOptions with the following BODY:

{"prescription":{"OriginatingWardId":1,"PrescriptionTypeID":1,"Requested":"2021-09-08 11:37:00","RequestedBy":"ADMIN", "Patients": [{"LocalID": "123ABC", "Forename":"Anne", "FamilyName":"Othertest"}]}, "start":true, "notes":[]}

In order for this to successfully create a Prescription valid OriginatingWardId, PrescriptionTypeId and RequestedBy codes would need to be passed in. These are codes stored in PTS and won't match the Id's used by the external system. For this reason we enable an 'ePrescribingId' to be stored against each user and ward in the system & also enable order types and sub types to be matched to prescription types within PTS. It is common for external systems calling the API to get the PTS ID's of the Ward/Type and User from helper API methods before calling PostPrescriptionWithCreationOptions:

GET http://prescriptiontracking.co.uk/api/Prescriptions/GetWardDetailsFromEPrescribingID?id=TEST (Use the value from the 'WardID' field returned in the response)
GET http://prescriptiontracking.co.uk/api/prescriptions/GetPrescriptionTypeDetailsFromOrderType?orderType=T&subType=T (Use the value from the 'PrescriptionTypeId' field returned in the response)
GET http://prescriptiontracking.co.uk/api/Users/GetUserDetailsFromEPrescribingID?id=ADMIN (Use the value in the 'Barcode' field returned in the response)

When utilising the API it can be advantageous to enter the details of the drugs being requested. In order to facilitate this PTS has a list of Drugs (at a VMP level) in it's database. We have utilities to import Drug data from Careflow MMS and from the DM+D TRUD Drug files. Once PTS has the necessary Drug information the details can be passed in the POST to PostPrescriptionWithCreationOptions, e.g:

{"prescription":{"OriginatingWardId":1,"PrescriptionTypeID":51,"Requested":"2021-07-27 09:50:00","RequestedBy":"ADMIN", "Patients": [{"LocalID": "123456", "Forename":"Jim", "FamilyName":"McTesterson","DOB":"1971-03-20","Mobile":"0759123456" , "Drugs": [{"DrugID":"10447211000001101", "DoseFrequency":"Two tablets at tea time","NoOfDosesDispensed":25}]}]}, "start":true, "notes":[]}

Notes

• If a Ward is served by multiple dispensaries (for example a main dispensary and a satellite dispensary) the dispensary can be specified in the message OR PTS can use logic (based on opening times, bank holidays and drug lists) to determine which dispensary the prescription should be booked to.
• Prescriptions booked-in electronically can be configured so that they are not immediately started if the dispensary that they are being booked-in to is closed.
• Barcode labels for the tracking of the Prescription through the dispensing process can be configured to be printed at different stages (e.g. you might want to have the labels print as soon as the order comes in OR wait until you manually start them).
• 'User views' can be created within PTS to show only Electronic Prescriptions if required.

HL7 listener

ePrescribing systems are routinely able to send HL7 messages when an order for medication is created. TMSi would install an HL7 listener (windows service) on the server hosting PTS. This service listens for HL7 messages and forwards them on to PTS 5’s API. A ’standard’ HL7 link would use the following segments:

PID - Patient information ORC - Order PV1 - Ward details RXO/RXD - Pharmacy Treatment Order/Dispense for Drug information

So, as an example a message might look like this:

MSH|^~\&|PCS|SCM|HL7PRX||20161123113853||ORM^O01|2002417409110001|D|2.3
PID||50000214^^^TrustNo|50000214^^^TrustNo||Still^Stan^^^MR||19530428|Male||British|3 Warren Close^Bexleyheath^^^DA6 7LX^UK^Home||0117 496 0227||NSP|Unknown|Church of England|W00000073^^^VisitID
PV1||W|B4DAY^^^STH||||NHNA^Arsanious^N H N|||URO|""|||||||Waiting List|W00000073^^^VisitID|||||||||||||||||||||||||20190911090000
ORC|NW|001HKTFWN^PCS|||IP||^every 24 hours^^20161123113700^^Routine||20161123113853|interfaces^DataGate^Interfaces||INTERFACES2^DataGate^Interfaces
RXO|Pharm412^CITALOPRAM TABLET^Local_Pharm|5||mg|||||||||0

Notes

• The Ward code sent in the PV1 segment must match an ‘e-prescribing id’ of a Ward in PTS. PTS has logic to determine which dispensary a prescription will be created in if a ward is server by multiple dispensaries.
• The ‘Visit id’ can be pulled from the PV1 segment as well as the main patient demographics in the PID segment. This can be useful if messages are sent out from PTS.
• The Drug Codes used in the RXO/RXD segment must match a drug code in PTS (we utilise Dm+D VMP codes as a default coding but can usually import any bespoke drug codes if required).
• We will generally need information in the ORC field to determine what Prescription Type will be generated in PTS. Most systems can provide an order type / sub-type that can be used to map to a Prescription type in PTS.

FHIR listener

ePrescribing systems are routinely able to send FHIR messages when an order for medication is created. These XML format messages can be somewhat verbose so it is difficult to show an example here.

An FHIR listening service can be installed on the PTS application server to listen for these messages and forward them to our API:

• Patient details for the Prescription can be obtained from the Patient Node.
• Ward details can be obtained from the MedicationRequest Location Node.
• Drug details can be obtained from the Medication Node.
• Prescription Type can be obtained from the Encounter Node.

ODBC 'pull' model

When an electronic prescribing system is unable to send HL7 or FHIR messages when an order is created, PTS can adopt a 'pull' model where we periodically query the ePrescribing system for new records.

A service would be configured on the PTS application server to accomplish this. The service would connect via an ODBC connection. We would need the schema of the database to which we are connecting to create the query. Some providers have generated views or stored procedures within the database for this purpose.

We have worked extensively with Careflow MMS with this sort of connection, so schema information is not required if you are utilising the RXSELECT functionality within Careflow MMS. For Trusts using Careflow, but not RXSELECT, we have successfully managed to query data in different tables so this isn't a problem.

Example CMM ODBC pull query

It's proven rare that a customer would use an uncustomised query and rarer still that an end user would need to refer to it, but we include this "vanilla" version of the CMM electronic prescribing query for completeness.

SELECT * FROM (SELECT DISTINCT   case when dlp.verified_by is null then   'ADMIN'   else   case when dlp.verified_date is null then  'ADMIN'  when dlp.verified_date < dlp.cdate then  'ADMIN'  else  (select u.Username from JAC_System."User" u where u.UserId = dlp.verified_by)   end  end UserID,  (SELECT TOP 1 code FROM JAC.Ward WHERE description = dlp.ward) WardID,  case when dlp.verified_by is null then   null   else   case when dlp.verified_date is null then  null  when dlp.verified_date < dlp.cdate then  null  else  case when dlp.days_supply > 0 then 1 else null end  end  end Start,  dlp.hospital_no LocalID,  dlp.nhs_no NationalID,  (SELECT TOP 1 forenames FROM JAC.patient_basic WHERE lnkpid = dlp.lnkpid) Forename,  (SELECT TOP 1 surname FROM JAC.patient_basic WHERE lnkpid = dlp.lnkpid) FamilyName,  CAST((RIGHT(dlp.date_of_birth,4) || '-' || SUBSTR(dlp.date_of_birth,4,2) || '-' || LEFT(dlp.date_of_birth,2)) AS DATE) DOB,  '' Mobile,  'T' OrderType,  'T' SubType,  dlp.prescribing_drug_id DrugID,  dlp.drug_name DrugDescription,   --dlp.dose || ' ' || dlp.route || ' ' || dlp.freq DoseFrequency,  '' DoseFrequency, --dlp.days_supply NoOfDoses, ifnull(dlp.days_supply,1,dlp.days_supply)  * (Select top 1 ifnull(f.fcount * x.secondary_dose,1,f.fcount * x.secondary_dose) from JAC_Super.x_med_order_doses x inner join JAC.frequency f on x.frequency_code = f.freq_code  where x.lnkordid = dlp.lnkordid  and x.lnkpid = dlp.lnkpid) NoOfDoses, (SELECT TOP 1 c_form FROM JAC.drug_basic WHERE JAC.drug_basic.lnkdid = dlp.prescribing_drug_id) DrugDoseUnits,  Cast(Cast(dlp.cdate as CHAR(10)) || ' ' || Cast(dlp.ctime as CHAR(8)) as DATETIME) RequestedDate  FROM (JAC.dlp dlp  INNER JOIN JAC.dlpc dlpc ON (dlp.rname=dlpc.rname) AND (dlp.uname=dlpc.uname))   INNER JOIN JAC_Super.x_med_orders x_med_orders ON (dlp.lnkpid=x_med_orders.lnkpid) AND (dlp.lnkordid=x_med_orders.lnkordid)  WHERE (DATEDIFF(MI,Cast(dlp.cdate as CHAR(10)) || ' ' || Cast(dlp.ctime as CHAR(8)),GETDATE()) <= 160)  AND dlp.prescribing_drug_id IS NOT NULL  AND (dlp.verified_date IS NULL or dlp.verified_date = dlp.cdate)  UNION  SELECT Case when X.SelectedDate is not null then  (select top 1 u.Username from JAC_System."User" u where u.UserId = X.SelectedByUserId)  else  ifnull(x.UserID,'ADMIN',X.UserID)  end UserID, X.WardID, Case when X.SelectedDate is not null then 1  else  X."Start"  end Start, X.LocalID, X.NationalID, X.Forename, X.FamilyName, X.DOB, X.Mobile,X.OrderType, X.SubType, X.DrugID, X.DrugDescription, X.DoseFrequency, X.NoOfDoses, X.DrugDoseUnits, ifnull(X.VerifiedDate, X.DispReqDate, X.VerifiedDate) RequestedDate FROM ( SELECT DISTINCT  (select top 1 case when u.GradeId in (121,101,120,104) then (select top 1 us.Username from JAC_System."User" us where us.UserId = u.UserId) else null end from JAC_System.PatientAccessAudit a inner join JAC_System.LocalityUser u on a.UserId = u.UserId  where patientid = DispensingRequests.PatientId  and AccessDate >= DATEADD(MINUTE,-1,DispensingRequests.RequestDate) order by a.AccessDate DESC) UserId, (select code from JAC.ward w where w.lnklrb = DispensingRequests.WardId) WardID, (select top 1 case when u.GradeId in (121,101,120,104) then (select top 1  case when rxv.VerificationStatus='V' then 1 else null end from JAC_System.RxVerification rxv  where rxv.OrderId = DispensingRequests.OrderId and rxv.Seq = DispensingRequests.Seq   and rxv.PatientId  = DispensingRequests.PatientId   order by rxv.VerificationDate DESC)  else null end from JAC_System.PatientAccessAudit a inner join JAC_System.LocalityUser u on a.UserId = u.UserId  where patientid = DispensingRequests.PatientId  and AccessDate >= DATEADD(MINUTE,-1,DispensingRequests.RequestDate) order by a.AccessDate DESC) Start, DispensingRequests.SelectedDate, DispensingRequests.SelectedByUserId,  DispensingRequests.OrderId, DispensingRequests.Seq,  DispensingRequests.PatientId,  patient_basic.hospital_no LocalID,  patient_basic.nhs_no NationalID,  patient_basic.forenames Forename,  patient_basic.surname FamilyName,  cast(patient_demographics.birth_date as Date) DOB,  ifnull(patient_demographics.telephone_no,'',patient_demographics.telephone_no) Mobile,  DispensingRequests.RequestType OrderType,  ifnull(DispensingRequests.OrderSubtype,'',DispensingRequests.OrderSubtype) SubType,  DispensingRequests.DrugId DrugID,  (SELECT TOP 1 c_drugfull FROM JAC.drug_basic WHERE JAC.drug_basic.lnkdid = DispensingRequests.DrugId) DrugDescription,  --(select cast(x.primary_dose as varchar(10)) from JAC_Super.x_med_order_doses x WHERE x.lnkordid =DispensingRequests.OrderId and x.lnkpid = DispensingRequests.PatientId  and x.lnkseq = DispensingRequests.Seq) --|| ' ' || (select x.primary_dose_description || ' ' || x.route from JAC_Super.x_med_orders x WHERE x.lnkordid =DispensingRequests.OrderId and x.lnkpid = DispensingRequests.PatientId) --|| ' ' || (select x.frequency_description from JAC_Super.x_med_order_doses x WHERE x.lnkordid =DispensingRequests.OrderId and x.lnkpid = DispensingRequests.PatientId  and x.lnkseq = DispensingRequests.Seq) DoseFrequency, '' DoseFrequency, --DispensingRequests.Quantity NoOfDoses, CASE WHEN DispensingRequests.Quantity = 0 THEN (SELECT x.primary_dose * x.stop_days_doses from JAC_Super.x_med_order_doses x WHERE x.lnkordid =DispensingRequests.OrderId and x.lnkpid = DispensingRequests.PatientId  and x.lnkseq = DispensingRequests.Seq) ELSE DispensingRequests.Quantity END NoOfDoses,   (SELECT TOP 1 c_form FROM JAC.drug_basic WHERE JAC.drug_basic.lnkdid = DispensingRequests.DrugId) DrugDoseUnits,  case when (select top 1 cast(rxv.VerificationDate as DATETIME) from JAC_System.RxVerification rxv  where rxv.OrderId = DispensingRequests.OrderId  and rxv.Seq = DispensingRequests.Seq  and rxv.VerificationStatus = 'V' and rxv.PatientId  = DispensingRequests.PatientId --and cast(rxv.VerificationDate as date) >= DATEADD(YEAR,-1,DispensingRequests.RequestDate)  ) < Cast(Cast(DispensingRequests.RequestDate as CHAR(10)) || ' ' || Cast(DispensingRequests.RequestTime as CHAR(8)) as DATETIME) THEN Cast(Cast(DispensingRequests.RequestDate as CHAR(10)) || ' ' || Cast(DispensingRequests.RequestTime as CHAR(8)) as DATETIME) ELSE null END  VerifiedDate, Cast(Cast(DispensingRequests.RequestDate as CHAR(10)) || ' ' || Cast(DispensingRequests.RequestTime as CHAR(8)) as DATETIME) DispReqDate FROM JAC_System.DispensingRequest DispensingRequests, JAC.patient_basic patient_basic,   JAC.patient_demographics patient_demographics  WHERE DispensingRequests.PatientId = patient_basic.lnkpid   AND patient_demographics.lnkpid = patient_basic.lnkpid  AND DispensingRequests.OrderID <> 0  AND (DispensingRequests.RequestType || ifnull(DispensingRequests.OrderSubtype,'',DispensingRequests.OrderSubtype) IN ('T','TS','MT','D','R')) ) X WHERE DATEDIFF(MI,ifnull(X.VerifiedDate, X.DispReqDate, X.VerifiedDate),GETDATE()) <=30) Y ORDER BY Y.RequestedDate, Y.LocalID, Y.NoOfDoses DESC

Outbound messages

PTS can be set-up to send an HL7 message when a Prescriptions status changes &/or an Activity is recorded against a Prescription. These options are configurable for each Prescription Type, so, for example, you could have TTO Prescriptions sending messages on each Activity Scan & Status Change whilst Outpatient Prescriptions don't send any HL7 messages at all.

PTS can be configured (in application settings) to send to multiple addresses / ports & each can be given a description of the receiving service: e.g.

• HOSTNAME1;PORT1;APPNAME1
• localhost;2100;BedView

The HL7 sent contains the following segments that are V2.7 OMG_O19 messages:

MSH|^~\&|Prescription Tracking System|Main Dispensary|BedView|Test Hospitals University NHS Trust|20210914103317||

OMG^O19|529743|P|2.7

PID||XXXXXX|||TEST^TEST

ORC|529743||||Activity Booking in||||14/09/2021 10:32:41||||^^^^^^^^Main Dispensary||||^Organisation Name

The ORC segment contains details of the Activity or Status change in the OrderStatus field. For activities, the description of the Activity will be prefixed with the word 'Activity' whilst for status changes the description of the Status will be prefixed with the Word 'Status'.

Activity descriptions are user-configurable in PTS. Status changes will always be one of the following:

• Pending
• Incomplete
• Suspended
• Completed
• Waiting for Collection
• Collected
• Waiting for Delivery
• Delivered
• Cancelled