Definition

The medication issue record model in IPCV V2 provides a comprehensive view of the patient’s issue record relating to a given prescription.

The model represents individual issues/supplies of medication recorded for a patient. Each issue record is linked back to its parent drug record and, where applicable, to the underlying prescribing / dispensing events.

Information

The issue record model should be treated as the source of medication issues where an issue record is always linked to a drug record.

The model contains one record per prescription issue and organisation and includes the following information:

• Record and patient identifiers
• Links to drug record and registration organisation
• Consultation links for re-issues through GP visits
• Clinical coding and terms
• Issuing effective and system dates
• Prescription type and issue methods
• Indicators for private prescriptions
• Cancellation information
• Prescription duration and frequency information
• Authorisation and GP (user) details
• Record confidentiality

Grain and Scope

Each row is a unique prescription record and each record is uniquely identified by either combining issue_record_id and organisation or the issue_record_uuid column.

This model includes the following key identifiers:

• issue_record_id: The unique internal identifier for the issue record within an organisation.
• issue_record_guid: The GUID for the issue record within an organisation.
• issue_record_uuid: The UUID derived from issue_record_id and organisation, providing a stable unique identifier.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether an issue record has been deleted at source.
• is_confidential: Indicates whether the record is confidential.
• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

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

Examples

Join issue records to their parent drug records

SELECT
  ir.emis_issue_guid,
  ir.emis_drug_guid,
  ir.quantity,
  dr.emis_code_id,
  dr.dose
FROM
  hive.explorer_ipcv_vanilla.medication_issue_record_v2 AS ir
  JOIN hive.explorer_ipcv_vanilla.medication_drug_record_v2 AS dr ON ir.emis_drug_guid = dr.emis_drug_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
  emis_issue_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
  confidential_flag = 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.emis_drug_guid = ir.emis_drug_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';