Definition

The mkb_drugpack model provides pharmaceutical packaging details, linking medications to their commercial forms. It combines preparation data with pack configurations including pricing, quantities, and supplier information.

Information

The MKB drugpack model provides packaging information for pharmaceutical products used within the healthcare system. It consolidates medication preparation data with their associated pack configurations, including pricing, unit quantities, and supplier details.

This model offers a standardized view of drug packaging across participating organizations.

The model can be used in conjunction with the drug_code model for common query patterns such as retrieving detailed packaging information for a specific drug via preparation_code_id which corresponds to code_id in the drug pack model. This model also uses dmd_product_code_id for mapping with the mapping dmd preparation model.

Model Changes in iPCV v2

Logic of ‘non_divisible_flag’ changed.

In iPCV v2, we’ve improved the medication divisibility indicator:

The column ‘non_divisible_flag’ has been renamed to ‘is_divisible’

The logic has been inverted for better clarity (TRUE now indicates a medication CAN be divided)

For example: If a medication previously had non_divisible_flag=FALSE (meaning it could be divided), it will now have is_divisible=TRUE

Why?

The double-negative logic of ‘non_divisible_flag’ was confusing and error-prone (FALSE meant divisible).

The new positive logic (‘is_divisible=TRUE’ means divisible) is more intuitive and matches common language.

Customer benefit

This change provides the following benefits:

• More intuitive naming with positive logic - “is_divisible” directly indicates whether a medication can be divided

• Improved query readability when filtering for divisible medications (no need for double negatives)

• Reduced likelihood of logical errors when writing conditions involving medication divisibility

Customer action

Customers should update their workflows and queries that referenced or used this column to now reference the renamed ‘is_divisible’ column and update to match new logic.

Schema updates for data maintenance

The is_deleted column has been added to indicate whether a clinical code has been marked as deleted

Benefit

The is_deleted column enables more effective data maintenance by allowing customers to identify and manage deleted clinical codes

Customer Action

Update your ETL processes to use the is_deleted column to manage and clean up clinical codes

Examples

Detailed packaging information for a specific drug

SELECT
  drug.term AS drug_name,
  drug.dmd_product_code_id,
  pack.pack_description,
  pack.units_per_pack,
  pack.unit_of_measure,
  pack.pack_price,
  pack.price_per_unit
FROM hive.explorer_ipcv.srv_drug_code AS drug
JOIN hive.explorer_ipcv.srv_mkb_drugpack AS pack
  ON drug.code_id = pack.preparation_code_id
WHERE
  drug.term LIKE '%metformin%'
ORDER BY
  drug.term, pack.units_per_pack

All packaging options for a specific medication

SELECT
  term AS drug_name,
  supplier_name,
  pack_description,
  units_per_pack,
  unit_of_measure,
  pack_price,
  price_per_unit
FROM hive.explorer_ipcv.srv_mkb_drugpack
WHERE
  term LIKE '%Amoxicillin%'
  AND is_deleted = FALSE
ORDER BY
  price_per_unit ASC

Medications with the most packaging options

SELECT
  term AS drug_name,
  COUNT(DISTINCT preparation_pack_id) AS pack_options_count,
  COUNT(DISTINCT supplier_name) AS supplier_count,
  MIN(price_per_unit) AS min_price_per_unit,
  MAX(price_per_unit) AS max_price_per_unit,
  AVG(price_per_unit) AS avg_price_per_unit
FROM hive.explorer_ipcv.srv_mkb_drugpack
WHERE
  is_deleted = FALSE
  AND price_per_unit > 0
GROUP BY
  term
HAVING
  COUNT(DISTINCT preparation_pack_id) > 3
ORDER BY
  pack_options_count DESC,
  supplier_count DESC
LIMIT 20