Implementation guide for interoperable medicines

Detailed Logic for Step 2 - Calculate the required quantity of each VMP to fulfil the requested dose

Conversion between scaler units of measure, e.g. gram to milligram

A function is required to convert a VTM ingredient strength into the same units as the requested dose quantity.

For example, if a required dose quantity is 12.5 milligram, but a VMP for that drug is expressed with a strength in micrograms e.g. 500 microgram, then that strength needs to be expressed in milligrams before the mathematical function can be executed.

Thus 500 microgram would be converted into 0.5 milligram.

For example;

///
// Convert 500 micrograms into milligrams
// FNC_CONVERT_UNITS(vpi.strnt_nmrtr_val, vpi.strnt_nmrtr_uomcd, dose_uom_cd);
///

SELECT FNC_CONVERT_UNITS(500, 258685003, 258684004);

// returns `0.5`.

Within the dm+d, units of mass have the greatest range; kilogram, gram, milligram, microgram and nanogram.

Due to this range, the data type used within SQL must be a DECIMAL(30,12).

Conversion is required for the following units of measure.

Function for quantity

A suitable SQL function to calculate the quantity of a given VMP to fulfil the requested dose quantity would be as follows.

FUNCTION FNC_CALC_QTY( doseQty DECIMAL(9,3)
                     , numerator DECIMAL(30,12)
                     , denominator DECIMAL(9,3)
                     , unitDoseFormStrength DECIMAL(9,3)
                     )
RETURNS DECIMAL(30,12)
BEGIN

    IF denominator = 0  
        THEN SET denominator = 1; 
    END IF;
    
    IF unitDoseFormStrength = 0
        THEN RETURN doseQty / ( numerator / denominator );
    END IF;
	
    RETURN ( doseQty / ( numerator / denominator ) ) / unitDoseFormStrength;

END

A description of each variable used in FNC_CALC_QTY is contained below.