Definition

The issue record data model contains information about medication issues prescribed to patients within the EMIS Web system. It captures details such as dosage, quantity, prescription type, costs, and the clinical codes associated with each issued medication.

Information

The issue record data model provides a comprehensive view of prescription issues linked to patients and their associated consultations. Each record represents a single medication issue event tied to a specific patient and, where applicable, a consultation.

Each record in this model can be uniquely identified using the issue_record_id and organisation.

In the issue record model, several indicators are available to distinguish the current status of a record:

• is_deleted - Indicates whether an issue record has been marked as deleted in the source system.
• is_cancelled - Indicates whether the prescription issue has been cancelled.
• effective_datetime - The date the issue became effective, indicating when the prescription was active.
• cancellation_datetime - The date the issue was cancelled, populated only when is_cancelled is TRUE.
• transform_datetime - Shows when the record was last updated in the data model, which can help determine the currency of the status information.

Examples

Active (non-cancelled) issues for a specific patient

SELECT
  issue_record_id,
  patient_id,
  consultation_id,
  effective_datetime,
  dosage,
  quantity,
  quantity_unit,
  prescription_type_description,
  issue_method_description,
  estimated_nhs_cost,
  organisation
FROM explorer_open_safely.medication_issue_record
WHERE
  patient_id = <patient_id>
  AND organisation = <organisation>
  AND NOT is_cancelled
  AND NOT is_deleted

Issues in the last 30 days

SELECT
  issue_record_id,
  patient_id,
  effective_datetime,
  dosage,
  quantity,
  quantity_unit,
  prescription_type_description,
  estimated_nhs_cost,
  organisation
FROM explorer_open_safely.medication_issue_record
WHERE
  effective_datetime >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND NOT is_cancelled
  AND NOT is_deleted