Definition

The Medication model in iPCV v2 is made up of two closely related tables Drug Record and Issue Record:

Drug Record – represents an individual prescribed, issued or recorded medication item for a patient (for example, a repeat prescription item, an acute item, or a historical drug record). It captures the high‑level context such as the drug, dose, route, instructions, dates, status and key participants.

Issue Record – represents individual issues / supplies of medication against a drug record (for example, each time a repeat prescription is issued, or a supply is dispensed). Each issue record is linked back to its parent drug record and, where applicable, to the underlying prescribing / dispensing events.

Together, these models provide a comprehensive view of a patient’s medications, from the top‑level drug record down to the individual issues / supplies.

Information

An issue record is a child of a drug record. It records each issue event and is used to understand how and when a medication was issued, including the issue method, cancellation status, and (where available) consultation linkage.

Overview

flowchart TD
    patient["Patient"]
    drug_record["Drug Record"]
    consultation["Consultation"]
    issue_record[["Issue Record Model"]]

    patient --> drug_record
    drug_record --> issue_record
    consultation --> issue_record

    classDef nodeStyle stroke:#9961a4;
    class patient,drug_record,consultation,issue_record nodeStyle;

    linkStyle default stroke:#117abf,fill:none

What’s changed in iPCV v2

1. NULL value columns for user GUIDs replaced with joins

In v1 some identifying context for organisations and users was embedded on the medication views, for example:

• emis_authorising_userinrole_guid
• emis_enteredby_userinrole_guid
• cancellation_userinrole_guid
• registration_ods_code (organisation lookups moved to organisation_v2)
• nhs_prescribing_agency
• nhs_prescription_type
• reimburse_type

Why?

• To ensure that NULL values are handled consistently and do not lead to unexpected behaviour in downstream processes.

In v2 these are normalised and the guid values can be rendered by joining with user_in_role_id:

• User-in-role: GUID/text fields are replaced with numeric IDs that join to user_in_role_v2:
  • entered_by_user_in_role_id (v2)
  • authorising_user_in_role_id (v2)
  • cancelled_by_user_in_role_id (v2)
• Organisation identifiers made Null (ODS codes now resolved via organisation models)
• reimburse_type is now NULL; use the boolean field is_privately_prescribed (v2) to determine whether a medication was privately prescribed.

Customer benefit

• Cleaner, more consistent joins to user_in_role_id
• Easier reuse of practitioner logic across models
• Improved query performance

Customer action

• Replace direct use of user GUID/text with joins on the following fields to user_in_role_v2:
  • authorising_user_in_role_id
  • entered_by_user_in_role_id
  • cancelled_by_user_in_role_id

2. FHIR-aligned columns removed

The following v1 columns (FHIR-aligned fields) have been removed in v2:

• type
• uom_dmd
• fhir_medication_status
• fhir_medication_intent

Why?

• Decision to remove FHIR mappings from the core analytical view and rely on dedicated reference tables or external FHIR services when required. See Universal improvements for more details.

Customer benefit

• A simpler schema with fewer ambiguous, duplicated or incorrectly populated fields.
• Encourages use of dedicated reference tables or external FHIR services where appropriate.

Customer action

• Remove dependencies on these fields in your SQL.

3. Removal of low-value, confidential or technical columns

Several v1 columns have been removed in v2 due to low value, duplication, reliability issues or confidentiality concerns.

Technical/metadata fields removed:

• _record_version
• _update_date
• _update_hour
• data_filter

Clinical, inconsistent or NULL fields removed:

• emis_mostrecent_issue_date, emis_mostrecent_issue_method, exa_mostrecent_issue_date — available as most_recent_** at drug-level
• other_code, other_code_system, other_display, snomed_concept_id, snomed_description_id — use dedicated code reference tables instead (see Universal improvements)
• authorisedissues_authorising_user_in_role, authorisedissues_entered_by_userinrole
• authorisedissues_authorised_date, authorisedissues_first_issue_date
• drug_status/ emis_medication_status, number_of_issues, number_authorised — available at drug-level
• exa_prescription_guid

Why?

• Reduce storage and remove rarely used columns from generic analytical views.

Customer benefit

• A cleaner, easier-to-understand issue record schema.
• Encourages consistent joins to dedicated reference tables instead of relying on embedded IDs.

Customer action

• Replace _update_* logic with _ingest_time or transform_datetime.
• Replace organisation lookups with joins to organisation_v2.
• Remove reliance on confidential text fields, or source equivalent information from appropriate clinical systems where explicitly permitted.

4. Addition of new metadata and lifecycle columns

Lifecycle and availability:

• is_deleted
• transform_datetime

Local mixture information:

• local_mixture_name

Why?

• To provide a richer, auditable lifecycle for each issue record.
• To support soft-deletion, history of confidentiality/sensitivity changes, and robust linking to drug records.
• To provide a local mixture name where applicable (for example, where a mixture is recorded as a single drug record with a local code rather than discrete ingredients).

Customer benefit

• Ability to distinguish current vs historical or deleted issue records.
• Consistent transform/ingestion metadata that can be used for incremental loads.
• Local mixture name provides additional context for analysis of mixture drugs.

Customer action

• For incremental pipelines, use transform_datetime and/or _ingest_time as your primary change-detection fields.
• Consider explicitly excluding is_deleted = TRUE rows unless required.
• For mixtures, consider using local_mixture_name for additional context where available, but be aware this is not a standardised field and may not be populated for all records.

5. Removal of Redundant or Deprecated Fields

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

Why?

This change simplifies the core issue record 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 focused information within the extended models, that increases the performance of iPCV v2.

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
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
cancellation_userinrole_guid	cancelled_by_user_in_role_id	user_in_role_v2
nhs_prescribing_agency		Always NULL as it is deprecated in v2
nhs_prescription_type		Always NULL as it is deprecated in v2
registration_ods_code		Always NULL as it is deprecated in v2
reimburse_type	is_privately_prescribed	medication_issue_record_v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
regular_patient_flag	patient_id	patient_v2
sensitive_patient_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
non_regular_and_current_active_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	patient_v2
emis_mostrecent_issue_method		Always NULL as it is deprecated in v2
clinician_user_in_role_guid	authorising_user_in_role_id	user_in_role_v2
entered_by_user_in_role_guid	entered_by_user_in_role_id	user_in_role_v2
problem_observation_guid		Always NULL as it is deprecated in v2
processing_id		Always NULL as it is deprecated in v2

Customer benefit

• Improved Performance: A leaner issue record 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

• Review all reports and queries that rely on the decommissioned fields listed above
• Update them to join with the appropriate extended data models using the new IDs provided in the table
• Test thoroughly to ensure data accuracy and query performance with the new model structure

Examples

Join issue records to their parent drug records

SELECT
  ir.issue_record_guid,
  ir.drug_record_guid,
  ir.quantity,
  dr.code_id,
  dr.dosage
FROM hive.explorer_ipcv_vanilla.medication_issue_record_v2 AS ir
JOIN hive.explorer_ipcv_vanilla.medication_drug_record_v2 AS dr
  ON ir.drug_record_guid = dr.drug_record_guid
  AND ir.organisation = dr.organisation
WHERE
  ir.organisation = 'CDB-12345'
  AND ir.is_deleted = FALSE
  AND dr.is_deleted = FALSE;

Find cancelled issues

SELECT
  issue_record_guid,
  cancellation_date,
  cancellation_reason
FROM hive.explorer_ipcv_vanilla.medication_issue_record_v2
WHERE
  organisation = 'CDB-12345'
  AND is_cancelled = TRUE
  AND is_deleted = FALSE;

Get all issue records for a patient

SELECT *
FROM hive.explorer_ipcv_vanilla.medication_issue_record_v2
WHERE emis_patient_id = 123456789
AND organisation = 'CDB-12345'

Find confidential issue records

SELECT *
FROM hive.explorer_ipcv_vanilla.medication_issue_record_v2
WHERE is_confidential = TRUE
AND organisation = 'CDB-12345'

Join issue records with their drug record

SELECT dr.*, ir.*
FROM hive.explorer_ipcv_vanilla.medication_drug_record_v2 dr
JOIN hive.explorer_ipcv_vanilla.medication_issue_record_v2 ir
  ON dr.drug_record_guid = ir.drug_record_guid
WHERE ir.emis_patient_id = 123456789

Get issue records marked as deleted

SELECT *
FROM hive.explorer_ipcv_vanilla.medication_issue_record_v2
WHERE is_deleted = TRUE

Find issue records for a specific organisation

SELECT *
FROM hive.explorer_ipcv_vanilla.medication_issue_record_v2
WHERE organisation = 'CDB-12345'