Definition

The drug code model provides a standardized reference catalog of pharmaceutical products based on the Dictionary of Medicines and Devices (dm+d) along with essential exception codes for historical data compatibility.

Information

The drug code model provides a standardized reference catalog of pharmaceutical products used within the healthcare system. It focuses specifically on medications defined in the Dictionary of Medicines and Devices (dm+d), along with select exception codes needed for historical data compatibility. The model includes drug identifiers, terms, and reference codes to enable consistent medication analysis across all participating organizations.

What’s changed in iPCV v2

Schema updates for data maintenance

• The is_deleted column has been added to indicate whether a clinical code has been marked as deleted
• The data_filter and processing_id columns are been removed as it is no longer required

Benefit

• The is_deleted column enables more effective data maintenance by allowing customers to identify and manage deleted clinical codes
• The data model is streamlined by removing deprecated columns from iPCV v1 and v2, resulting in a cleaner and more efficient structure

Customer Action

• Update your ETL processes to use the is_deleted column to manage and clean up clinical codes
• Review your queries and remove any references to the deprecated processing_id and data_filter columns to align with the updated schema and avoid confusion

Examples

Find codes based on DMD product code

SELECT
  *
from hive.explorer_ipcv.srv_drug_code
where dmdproductcodeid = 1234567890123456

Find medications with a specific ingredient

SELECT
  code_id,
  term,
  dmd_product_code_id
FROM hive.explorer_ipcv.srv_drug_code
WHERE
  term LIKE '%Paracetamol%'
  AND is_deleted = FALSE
ORDER BY
  term

Find medications migrated or degraded during system transfers

SELECT
  code_id,
  term,
  old_code,
  CASE
    WHEN code_id = 1572871000006117 THEN 'Awaiting migration'
    WHEN code_id = 294711000000118 THEN 'Transfer-degraded'
  END AS exception_type
FROM hive.explorer_ipcv.srv_drug_code
WHERE
  code_id IN (1572871000006117, 294711000000118)
ORDER BY
  exception_type, term;