Schema

Core

The core schema represents the Referral schema derived from EMIS source system.

Column Name	Data Type	Description	Example
is_deleted	boolean	Indicates if this record should be considered soft deleted	FALSE
load_datetime	timestamp(6) with time zone	The datetime that the record was last upserted into the EXA data lake from EMIS source system with a relevant change from source system.	‘2023-05-12 14:30:15.000000 UTC’
_ingest_time	varchar	The load_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230512143015’
referral_observation_id	bigint	The unique internal identifier for the referral record within an organisation.	12345
referral_observation_guid	varchar	The GUID for the referral record within an organisation	‘a1b2c3d4-e5f6-7890-abcd-ef1234567890’
referral_observation_uuid	varchar	The UUID derived from referral_observation_id and organisation, providing a stable unique identifier.	‘f47ac10b-58cc-4372-a567-0e02b2c3d479’
patient_id	bigint	The unique internal identifier for the patient record within the organisation for whom the referral was recorded.	67890
patient_guid	varchar	The GUID for the patient record within an organisation	‘b2c3d4e5-f6a7-8901-bcde-f12345678901’
patient_uuid	varchar	The UUID derived from patient_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
patient_organisation_id	bigint	The unique internal identifier for the organisation where the patient is registered	12345
patient_organisation_guid	varchar	The GUID of the organisation where the patient is registered	‘c3d4e5f6-a7b8-9012-cdef-123456789012’
patient_organisation_uuid	varchar	The UUID derived from patient_organisation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
unique_booking_reference_number	varchar	Unique Booking Reference Number (UBRN) for the referral allowing for tracking and management of the referral process. Only available for inbound referrals.	‘000280834589’
consultation_id	bigint	The unique internal identifier for the consultation record within an organisation in which the referral was recorded.	12345
consultation_guid	varchar	The GUID for the consultation record within an organisation	‘d4e5f6a7-b8c9-0123-defa-234567890123’
consultation_uuid	varchar	The UUID derived from consultation_id and organisation, providing a stable unique identifier.	‘e5f6a7b8-c9d0-1234-efab-345678901234’
consultation_section_id	bigint	The unique internal identifier for the consultation section within an organisation.	12345
consultation_section_uuid	varchar	The UUID derived from consultation_section_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
consultation_source_code_id	bigint	The identifier of the code describing the consultation source (e.g. GP Surgery, Telephone)	1672851000006116
consultation_source_original_term	varchar	The EMIS term for the consultation source code	‘GP Surgery’
urgency	bigint	Numeric urgency code: 0=Routine, 1=Soon, 2=Urgent, 3=Dated, 4=2 Week Wait	2
urgency_description	varchar	Urgency description derived from urgency code	‘Urgent’
direction	varchar	Direction of the referral: ‘out’ (outgoing) or ‘in’ (incoming)	‘out’
service_type_id	bigint	EMIS code ID for the requested service type(e.g., 1775591000006112 - Referred to School Nursing Service,1775311000006113 - Referred to Cardiac Service)	1775591000006112
service_type_requested	bigint	The identifier that maps to a service type requested	9
service_type_display_text	varchar	Display text for the requested service type(e.g., 1 - Investigation, 2- Treatment, 7 - Self referral , 9 - Admission)	‘Treatment’
referral_mode_id	bigint	The identifier that maps to a referral mode	4
referral_mode_description	varchar	Referral mode description mapped to the referral mode id(e.g., 0 - NHS e-Referral, 1 - Telephone, 3 - Written, 4 - Managed)	‘Written’
problem_observation_id	bigint	The unique internal identifier for the first problem observation within an organisation linked to this problem across all episodes (Associated and Episode Follow On). The records are prioritised by effective_datetime, availability_datetime and observation_id.	12345
problem_observation_guid	varchar	The GUID for the first problem observation within an organisation linked to this referral	‘b8c9d0e1-f2a3-1b2c-6d7e-8f9a0b1c2d3e’
problem_observation_uuid	varchar	The UUID derived from problem_observation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
authorising_user_in_role_id	bigint	The unique identifier of the clinician who authorised or authored the referral record	11223
authorising_user_in_role_uuid	varchar	The UUID derived from authorising_user_in_role_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
entered_by_user_in_role_id	bigint	The unique identifier of the user who entered the referral record	33445
entered_by_user_in_role_uuid	varchar	The UUID derived from entered_by_user_in_role_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
observation_organisation_id	bigint	The unique internal identifier of the organisation where the referral was recorded	12345
observation_organisation_guid	varchar	The GUID of the organisation where the referral was recorded	‘a7b8c9d0-e1f2-0a1b-5c6d-7e8f9a0b1c2d’
observation_organisation_uuid	varchar	The UUID derived from observation_organisation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
target_organisation_id	bigint	The unique internal identifier for the target organisation the patient is referred to	12345
target_organisation_guid	varchar	The GUID of the target organisation the patient is referred to	‘f1e2d3c4-b5a6-7890-abcd-ef0123456789’
target_organisation_uuid	varchar	The UUID derived from from target_organisation_id and organisation, providing a stable unique identifier.organisation	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
code_id	bigint	The unique EMIS code identifier for the clinical code associated with the referral	8085301000006112
original_term	varchar	The EMIS term used to record the referral. Will be set to NULL for uncoded referrals.	‘Referral to cardiology’
qualifiers	varchar	Raw XML qualifier data associated with the referral. Qualifiers are extended data fields attached to a specific clinical or vaccination code that allow users to record highly detailed, granular information without needing to create separate, standalone clinical codes. Will be set to NULL for uncoded referrals.	‘Urgent’
effective_datetime	timestamp(6) with time zone	The date and time when the referral came into effect	‘2023-01-10 09:00:00.000000 UTC’
effective_datetime_precision	varchar	Precision of the effective datetime; set to ‘YMDT’.	‘YMDT’
availability_datetime	timestamp(6) with time zone	The date and time the referral was recorded in the source system	‘2023-01-10 09:00:00.000000 UTC’
referral_made_datetime	timestamp(6) with time zone	The datetime the inbound referral was originally made	‘2023-01-09 16:45:00.000000 UTC’
ended_datetime	timestamp(6) with time zone	The datetime the referral was resolved or ended	‘2023-06-30 00:00:00.000000 UTC’
is_sensitive	boolean	Indicates whether the referral record is flagged as sensitive. The sensitive reference set codes are 999004351000000109( gender related issues), 999004371000000100(assisted fertilisation), 999004361000000107(termination of pregnancy) and 999004381000000103 (sexually transmitted disease)	FALSE
is_confidential	boolean	Indicates whether the referral record is confidential. This is based on confidentiality set against the patient/consultation/parent observation/referral record.	FALSE
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	‘CDB-12345’
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model.	‘2023-05-12 14:30:15.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230512143015’

Vanilla

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	referral_observation_guid
exa_observation_guid	varchar	✓	✓	referral_observation_uuid
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
problem_observation_id	bigint	✗	✓
emis_problem_observation_guid	varchar	✓	✓	problem_observation_guid
problem_observation_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
target_organisation_id	bigint	✗	✓
emis_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
ubrn	varchar	✓	✓	unique_booking_reference_number
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
consultation_source_emis_original_term	varchar	✓	✓	consultation_source_original_term
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
referral_end_date	timestamp(6) with time zone	✓	✓	ended_datetime
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
received_date	date	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
referral_made_datetime	timestamp(6) with time zone	✗	✓
qualifiers	varchar	✓	✓
referral_mode_id	bigint	✗	✓
referral_mode_description	varchar	✓	✓
nhs_source	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_direction_description	varchar	✓	✓	direction
emis_urgency	bigint	✓	✓	urgency
emis_urgency_description	varchar	✓	✓	urgency_description
nhs_priority	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_priority_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
service_type_emis_code_id	bigint	✓	✓	service_type_id
service_type_requested	bigint	✗	✓
service_type_requested_description	varchar	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
action_date	timestamp(3)	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
service_type_snomed_concept_id	bigint	✓	✗
service_type_term	varchar	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
emis_referral_term	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗

Apollo

Not used in this schema

Artemis

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	referral_observation_guid
exa_observation_guid	varchar	✓	✓	referral_observation_uuid
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
target_organisation_id	bigint	✗	✓
emis_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
ubrn	varchar	✓	✓	unique_booking_reference_number
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
consultation_source_emis_original_term	varchar	✓	✓	consultation_source_original_term
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
received_date	date	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
referral_made_datetime	timestamp(6) with time zone	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
referral_end_date	timestamp(6) with time zone	✓	✓	ended_datetime
qualifiers	varchar	✓	✓
referral_mode_id	bigint	✗	✓
referral_mode_description	varchar	✓	✓
nhs_source	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_direction_description	varchar	✓	✓	direction
nhs_priority	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_priority_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_urgency	bigint	✓	✓	urgency
emis_urgency_description	varchar	✓	✓	urgency_description
service_type_emis_code_id	bigint	✓	✓	service_type_id
service_type_requested_description	varchar	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
action_date	timestamp(3)	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	bigint	✓	✗
dummy_patient_flag	boolean	✓	✗
emis_referral_term	varchar	✓	✗
regular_patient_flag	boolean	✓	✗
service_type_snomed_concept_id	bigint	✓	✗
service_type_term	varchar	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping
is_deleted	boolean
pseudo_referral_observation_uuid	varchar	referral_observation_uuid
pseudo_patient_uuid	varchar	patient_uuid
pseudo_patient_organisation_uuid	varchar	patient_organisation_uuid
pseudo_consultation_uuid	varchar	consultation_uuid
pseudo_consultation_section_uuid	varchar	consultation_section_uuid
pseudo_problem_observation_uuid	varchar	problem_observation_uuid
pseudo_observation_organisation_uuid	varchar	observation_organisation_uuid
pseudo_target_organisation_uuid	varchar	target_organisation_uuid
unique_booking_reference_number	varchar
effective_datetime	timestamp(6) with time zone
effective_datetime_precision	varchar
availability_datetime	timestamp(6) with time zone
referral_made_datetime	timestamp(6) with time zone
ended_datetime	timestamp(6) with time zone
code_id	bigint
consultation_source_code_id	bigint
consultation_source_original_term	varchar
original_term	varchar
urgency	bigint
urgency_description	varchar
direction	varchar
service_type_id	bigint
service_type_requested	bigint
service_type_display_text	varchar
referral_mode_id	bigint
referral_mode_description	varchar
is_confidential	boolean
is_sensitive	boolean
organisation	varchar
transform_datetime	timestamp(6) with time zone

Hestia

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	referral_observation_guid
exa_observation_guid	varchar	✓	✓	referral_observation_uuid
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
target_organisation_id	bigint	✗	✓
emis_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
ubrn	varchar	✓	✓	unique_booking_reference_number
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
consultation_source_emis_original_term	varchar	✓	✓	consultation_source_original_term
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
received_date	date	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
referral_made_datetime	timestamp(6) with time zone	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
referral_end_date	timestamp(6) with time zone	✓	✓	ended_datetime
qualifiers	varchar	✓	✓
referral_mode_id	bigint	✗	✓
referral_mode_description	varchar	✓	✓
nhs_source	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_direction_description	varchar	✓	✓	direction
nhs_priority	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_priority_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_urgency	bigint	✓	✓	urgency
emis_urgency_description	varchar	✓	✓	urgency_description
service_type_emis_code_id	bigint	✓	✓	service_type_id
service_type_requested_description	varchar	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
service_type_snomed_concept_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
emis_referral_term	varchar	✓	✗
service_type_term	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Olympus

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deletedin V1
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	referral_observation_guid
exa_observation_guid	varchar	✓	✓	referral_observation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_consultation_guid	varchar	✓	✓	consultation_guid
exa_consultation_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
emis_problem_observation_guid	varchar	✓	✓	problem_observation_guid
problem_observation_uuid	varchar	✗	✓
emis_code_id	bigint	✓	✓	code_id
pseudo_registration_guid	varchar	✓	✓	patient_guid
pseudo_patient_uuid	varchar	✗	✓	patient_uuid
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
observation_organisation_guid	varchar	✗	✓
observation_organisation_uuid	varchar	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
effective_date	date	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
entered_date	date	✓	✓	availability_datetime
entered_time	varchar	✓	✓	availability_datetime
referral_mode_id	bigint	✗	✓
referral_mode_description	varchar	✓	✓
emis_urgency_description	varchar	✓	✓	urgency_description
urgency	bigint	✗	✓
service_type_requested_description	varchar	✓	✓
target_organisation_id	bigint	✗	✓
emis_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
ubrn_flag	boolean	✓	✓	unique_booking_reference_number
observation_type	varchar	✓	✓	Set as ‘Referral’
confidential_flag	boolean	✓	✓	is_confidential
processing_id	integer	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Prometheus

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deletedin V1
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	referral_observation_guid
exa_observation_guid	varchar	✓	✓	referral_observation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_consultation_guid	varchar	✓	✓	consultation_guid
exa_consultation_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
emis_problem_observation_guid	varchar	✓	✓	problem_observation_guid
problem_observation_uuid	varchar	✗	✓
emis_code_id	bigint	✓	✓	code_id
pseudo_registration_guid	varchar	✓	✓	patient_guid
pseudo_patient_uuid	varchar	✗	✓	patient_uuid
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
observation_organisation_guid	varchar	✗	✓
observation_organisation_uuid	varchar	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_uuid	varchar	✗	✓
effective_date	date	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
entered_date	date	✓	✓	availability_datetime
entered_time	varchar	✓	✓	availability_datetime
referral_mode_id	bigint	✗	✓
referral_mode_description	varchar	✓	✓
emis_urgency_description	varchar	✓	✓	urgency_description
urgency	bigint	✗	✓
service_type_requested_description	varchar	✓	✓
target_organisation_id	bigint	✗	✓
emis_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
ubrn_flag	boolean	✓	✓	unique_booking_reference_number
confidential_flag	boolean	✓	✓	is_confidential
processing_id	integer	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓

Pseudo_Anon

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deletedin V1
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
observation_guid	varchar	✓	✓	referral_observation_guid
id_type5	varchar	✓	✓	referral_observation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
consultation_guid	varchar	✓	✓
consultation_uuid	varchar	✗	✓
problem_observation_id	bigint	✗	✓
problem_guid	varchar	✓	✓	problem_observation_guid
problem_observation_uuid	varchar	✗	✓
patient_guid	varchar	✓	✓
patient_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
patient_organisation_guid	varchar	✗	✓
patient_organisation_uuid	varchar	✗	✓
organisation_guid	varchar	✓	✗
observation_organisation_id	bigint	✗	✓
observation_organisation_guid	varchar	✗	✓
observation_organisation_uuid	varchar	✗	✓
code_id	bigint	✓	✓
referral_source_organisation_guid	varchar	✓	✗
clinician_user_in_role_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
document_guid	varchar	✓	✗
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
effective_date	date	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
entered_date	date	✓	✓	availability_datetime
entered_time	varchar	✓	✓	availability_datetime
referral_mode	varchar	✓	✓	referral_mode_description
referral_urgency	bigint	✓	✓	urgency
referral_service_type	varchar	✓	✓	service_type_requested_description
target_organisation_id	bigint	✗	✓
referral_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
observation_type	varchar	✓	✓	Set as ‘Referral’
value	varchar	✓	✗
numeric_unit	varchar	✓	✗
numeric_range_low	double	✓	✗
numeric_range_high	double	✓	✗
is_confidential	boolean	✓	✓
processing_id	integer	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓

Themis

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	referral_observation_guid
exa_observation_guid	varchar	✓	✓	referral_observation_uuid
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
problem_observation_id	bigint	✗	✓
emis_problem_observation_guid	varchar	✓	✓	problem_observation_guid
problem_observation_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✓	patient_id
registration_guid	varchar	✓	✓	patient_guid
patient_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
target_organisation_id	bigint	✗	✓
emis_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
ubrn	varchar	✓	✓	unique_booking_reference_number
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
consultation_source_emis_original_term	varchar	✓	✓	consultation_source_original_term
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_enteredby_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
entered_by_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_uuid	varchar	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
authorising_user_in_role_id	bigint	✗	✓
authorising_user_in_role_uuid	varchar	✗	✓
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
received_date	date	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
referral_made_datetime	timestamp(6) with time zone	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
referral_end_date	timestamp(6) with time zone	✓	✓	ended_datetime
qualifiers	varchar	✓	✓
referral_mode_id	bigint	✗	✓
referral_mode_description	varchar	✓	✓
nhs_source	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_direction_description	varchar	✓	✓	direction
nhs_priority	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_priority_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_urgency	bigint	✓	✓	urgency
emis_urgency_description	varchar	✓	✓	urgency_description
service_type_emis_code_id	bigint	✓	✓	service_type_id
service_type_requested_description	varchar	✓	✓
confidential_flag	boolean	✓	✓	is_confidential
sensitive_flag	boolean	✓	✓	is_sensitive
action_date	timestamp(3)	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
service_type_snomed_concept_id	bigint	✓	✗
service_type_term	varchar	✓	✗
dummy_patient_flag	boolean	✓	✗
emis_referral_term	varchar	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
_record_version	varchar	✓	✗
_update_date	varchar	✓	✗
_update_hour	varchar	✓	✗

Zeus

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
referral_observation_id	bigint	✗	✓
emis_observation_guid	varchar	✓	✓	referral_observation_guid
exa_observation_guid	varchar	✓	✓	referral_observation_uuid
consultation_section_id	bigint	✗	✓
consultation_section_uuid	varchar	✗	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓	consultation_guid
exa_encounter_guid	varchar	✓	✓	consultation_uuid
problem_observation_id	bigint	✗	✓
emis_problem_observation_guid	varchar	✓	✓	problem_observation_guid
problem_observation_uuid	varchar	✗	✓
observation_organisation_id	bigint	✗	✓
emis_observation_organisation_guid	varchar	✓	✓	observation_organisation_guid
observation_organisation_uuid	varchar	✗	✓
emis_patient_id	bigint	✓	✗	patient_id
pseudo_patient_uuid	varchar	✗	✓	patient_uuid
pseudo_registration_guid_ourea	varchar	✓	✓	patient_guid
patient_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓	patient_organisation_guid
patient_organisation_uuid	varchar	✗	✓
registration_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_code_id	bigint	✓	✓	code_id
emis_original_term	varchar	✓	✓	original_term
target_organisation_id	bigint	✗	✓
emis_target_organisation_guid	varchar	✓	✓	target_organisation_guid
target_organisation_uuid	varchar	✗	✓
ubrn	varchar	✓	✓	unique_booking_reference_number
consultation_source_emis_code_id	bigint	✓	✓	consultation_source_code_id
consultation_source_emis_original_term	varchar	✓	✓	consultation_source_original_term
emis_enteredby_userinrole_guid	varchar	✓	✗
emis_referral_term	varchar	✓	✗		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_authorising_userinrole_guid	varchar	✓	✗		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
effective_date	timestamp(6) with time zone	✓	✓	effective_datetime
effective_date_precision	varchar	✓	✓	effective_datetime_precision
received_date	date	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
referral_made_datetime	timestamp(6) with time zone	✗	✓
recorded_date	timestamp(6) with time zone	✓	✓	availability_datetime
referral_end_date	timestamp(6) with time zone	✓	✓	ended_datetime
qualifiers	varchar	✓	✓
referral_mode_id	bigint	✗	✓
referral_mode_description	varchar	✓	✓
nhs_source	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_direction_description	varchar	✓	✓	direction
nhs_priority	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_priority_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_urgency	bigint	✓	✓	urgency
emis_urgency_description	varchar	✓	✓	urgency_description
service_type_emis_code_id	bigint	✓	✓	service_type_id
service_type_requested_description	varchar	✓	✓
sensitive_flag	boolean	✓	✓	is_sensitive
action_date	timestamp(3)	✓	✗
confidential_flag	boolean	✓	✓	is_confidential
data_filter	bigint	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
service_type_snomed_concept_id	bigint	✓	✗
service_type_term	varchar	✓	✗
dummy_patient_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
readv2_code	varchar	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓