Definition

The Referral data model represents clinical referral observations recorded within EMIS Web for registered patients. A referral captures the act of directing a patient to another healthcare service or clinician, including details such as urgency, direction (incoming or outgoing), service type, target organisation, and the consultation in which the referral was made.

Information

The Referral data model provides a view of all referrals associated with patients across healthcare organisations. Referrals are derived from observation data and include clinical coding (SNOMED CT, Read v2), dates, urgency classifications, and the linked consultation.

Each record in this model can be uniquely identified using the combination of referral_observation_id (or observation_guid) and organisation.

Key Data Fields

Some of the key data fields are as follows:

Referral Identifiers

• referral_observation_id: The internal EMIS observation identifier for this referral record.
• organisation: The unique identifier for the healthcare organisation (in CDB format) where the referral was recorded.
• observation_guid: The EMIS GUID for the referral observation.
• referral_observation_uuid: EXA-generated UUID5 derived from referral_observation_id and organisation, providing a stable unique identifier.

Clinical Coding

• code_id: The EMIS internal clinical code identifier for the referral type.
• original_term: The clinical term as recorded in EMIS Web. This field is masked when the referral is deleted or when the code is null.

Dates

• effective_datetime: The clinical effective date of the referral.
• effective_datetime_precision: The precision of the effective date; hardcoded to 'YMDT' in iPCV v2.
• availability_datetime: The datetime the referral record became available.
• referral_made_datetime: The datetime the referral was originally made.
• ended_datetime: The date the referral ended or was resolved, if applicable.

Referral Classification

• urgency: Numeric urgency code (0=Routine, 1=Soon, 2=Urgent, 3=Dated, 4=2 Week Wait).
• urgency_description: Human-readable urgency label derived from the urgency code.
• direction: Direction of the referral ('in' for incoming, 'out' for outgoing).
• referral_mode_description: The referral mode (e.g., written, telephone).
• service_type_id: EMIS code ID for the requested service type.
• service_type_display_text: Display text for the requested service type.

Linked Records

• consultation_guid: EMIS GUID of the consultation linked to this referral.
• consultation_uuid: EXA-generated UUID5 for the linked consultation.
• problem_observation_guid: EMIS GUID of the related problem observation, if the referral is linked to a problem.
• target_organisation_guid: EMIS GUID of the organisation to which the patient is referred.

Patient Identifiers

• patient_id: Internal EMIS patient identifier.
• patient_guid: EMIS patient GUID (used as registration GUID in flavour views).
• patient_organisation_guid: EMIS GUID of the patient’s registered organisation.

Access and Sensitivity Flags

• is_deleted: Indicates whether the referral record has been soft deleted.
• is_confidential: Indicates whether the record is currently marked as confidential.
• was_confidential: Indicates whether the record was previously marked as confidential.
• is_sensitive: Indicates whether the record is currently flagged as sensitive.
• was_sensitive: Indicates whether the record was previously flagged as sensitive.

Overview

flowchart TD
  org1["Organisation 1"] --> patA["Patient A"]
  org1 --> patB["Patient B"]
  org2["Organisation 2"] --> patC["Patient C"]

  patA --> ref1["Referral: Cardiology (Urgent)"]
  patA --> ref2["Referral: Physiotherapy (Routine)"]
  patB --> ref3["Referral: Ophthalmology (Soon)"]
  patC --> ref4["Referral: Respiratory (Urgent)"]

  ref1 --> gather["Gather Referral Data"]
  ref2 --> gather
  ref3 --> gather
  ref4 --> gather
  gather -->|Unique IDs: referral_observation_id + organisation| etl["ETL"]
  etl --> final["Final Consolidated Data"]

  classDef nodeStyle stroke:#9961a4;
  class org1,patA,patB,org2,patC,ref1,ref2,ref3,ref4,gather,etl,final nodeStyle;

  linkStyle default stroke:#117abf,fill:none;

Changes in v2

Removal of Redundant or Deprecated Fields

In iPCV v1, several attributes were directly included in the referral model. In iPCV v2, the below fields have been removed from the referral data model, and relevant IDs are provided to retrieve this information from dedicated extended data models.

Why?

This change simplifies the core referral model, making it lighter and easier to manage. By moving specific attributes to their own models, data redundancy is reduced, and maintainability is improved. This allows for more detailed and focused information within the extended models without cluttering the primary referral record, reducing the complexity of multiple joins.

Note: These decommissioned columns will be removed in a future iPCV release; plan migrations accordingly

Decommissioned Column	Relevant ID/ Column	Reason / Extended Data Model to be Referred
other_code	code_id	codeable_concept_v2
other_code_system	code_id	codeable_concept_v2
other_display	code_id	codeable_concept_v2
readv2_code	code_id	codeable_concept_v2
snomed_concept_id	code_id	codeable_concept_v2
snomed_description_id	code_id	codeable_concept_v2
service_type_snomed_concept_id	code_id	codeable_concept_v2
service_type_term	code_id	codeable_concept_v2
emis_referral_term	code_id	codeable_concept_v2
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
clinician_user_in_role_guid	authorising_user_in_role_id	user_in_role_v2
sensitive_patient_flag	patient_id	patient_v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
non_regular_and_current_active_flag	patient_id	patient_v2
opt_out_93c1_flag	patient_id	patient_v2
opt_out_9nd19nu09nu4_flag	patient_id	patient_v2
opt_out_9nd19nu0_flag	patient_id	patient_v2
opt_out_9nu0_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	Removed. Instead, use regular_patient_flag
regular_patient_flag	patient_id	patient_v2
action_date		Removed as it is deprecated in iPCV v1
received_date	referral_made_datetime	Always Null in source and it is replaced with referral_made_datetime column
referral_source_organisation_guid		Removed as it is deprecated in iPCV v1
parent_observation_guid		Removed as it is deprecated in iPCV v1
value		Removed as it is deprecated in iPCV v1
numeric_range_low		Removed as it is deprecated in iPCV v1
numeric_range_high		Removed as it is deprecated in iPCV v1
document_guid		Removed as it is deprecated in iPCV v1
processing_id		Always NULL as it is deprecated in v2
data_filter		Removed as it is deprecated in v2
_record_version		No longer available in v2
_update_date		Changes tracked differently in v2
_update_hour		Changes tracked differently in v2

Customer benefit

• Improved Performance: A leaner problem model can lead to faster query performance.
• Enhanced Scalability: Decoupled models are easier to maintain and extend independently.
• Data Consistency: Centralizing specific attributes in their own models ensures a single source of truth.

Customer action

• Identify any queries or reports that use the removed fields.

• Update these processes to join with the appropriate extended data models to retrieve the required information.

• For any reports or queries that rely on the removed fields, update them to join with the appropriate extended data models using the new IDs.

Addition of New Columns

The v2 Referral model introduces additional columns to enhance auditability, relationships, and incremental processing. These include:

• referral_observation_id
• entered_by_user_in_role_id
• authorising_user_in_role_id
• referral_made_datetime
• referral_observation_uuid
• consultation_uuid
• patient_id
• emis_urgency

Why?

These columns were added to modernise the Referral model with stable keys, clear relationship fields, and robust incremental-processing support.

Customer benefit

• More reliable joins through integer and UUID5 keys
• Richer referral context for urgency, service type, direction, and consultation linkage

Customer action

• Join entered_by_user_in_role_id and authorising_user_in_role_id to user_in_role_v2 for user attribution
• Update queries and joins to use referral_observation_uuid and consultation_uuid for stable cross-system identifiers
• Migrate patient_id and organisation combinations to leverage referral_observation_uuid where applicable
• Use urgency, direction, service_type_id, and target_organisation_guid for enhanced filtering and relationship queries

Examples

Retrieve all active referrals for a patient

SELECT
  referral_observation_id,
  emis_patient_id,
  emis_original_term,
  effective_date,
  emis_urgency_description,
  emis_direction_description,
  service_type_requested_description,
  is_deleted
FROM
  hive.explorer_ipcv_vanilla.referral_v2
WHERE
  emis_patient_id = 12345
  AND is_deleted = FALSE
ORDER BY
  effective_date DESC

Find referrals linked to a specific consultation

SELECT
  referral_observation_id,
  emis_original_term,
  effective_date,
  emis_urgency_description,
  emis_direction_description,
  service_type_requested_description
FROM
  hive.explorer_ipcv_vanilla.referral_v2
WHERE
  emis_encounter_guid = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
  AND is_deleted = FALSE