Pharmacy Information System KPIs & Reporting

KPI Summary

* encounters and integration_message_log are referenced from other modules; see shared entity rules.

KPI Definitions

KPI-PIS-001: Order Verification Turnaround Time

Definition

Average time from when a medication order is received by the pharmacy to when it is verified by a pharmacist. Measures pharmacy responsiveness and supports DOH/DHA expectations for timely medication therapy, especially for STAT/critical medications.

Calculation Formula

Assumptions:

• pharmacy_orders.order_received_datetime – timestamp when order arrives in PIS (from CPOE).
• pharmacy_orders.verified_datetime – timestamp when pharmacist verifies the order.
• pharmacy_orders.order_priority – e.g., 'STAT', 'ROUTINE'.
• pharmacy_orders.order_status – 'verified' when completed.

-- Overall average verification TAT (minutes)
SELECT
    AVG(
        EXTRACT(EPOCH FROM (po.verified_datetime - po.order_received_datetime)) / 60.0
    ) AS verification_tat_minutes
FROM pharmacy_orders po
WHERE po.order_status = 'verified'
  AND po.order_received_datetime >= :start_datetime
  AND po.order_received_datetime < :end_datetime;

-- By priority (STAT vs routine)
SELECT
    po.order_priority,
    AVG(
        EXTRACT(EPOCH FROM (po.verified_datetime - po.order_received_datetime)) / 60.0
    ) AS verification_tat_minutes
FROM pharmacy_orders po
WHERE po.order_status = 'verified'
  AND po.order_received_datetime >= :start_datetime
  AND po.order_received_datetime < :end_datetime
GROUP BY po.order_priority;

Target / Benchmark

Data Source

• pharmacy_orders
• order_id
• order_received_datetime
• verified_datetime
• order_status
• order_priority
• facility_id (FK → facilities)
• department_id (if present)
• ordering_provider_id (FK → providers)

Dimensions / Filters

• Time: hour, day, week, month, quarter, year
• Facility (facility_id)
• Department / clinical unit (department_id or encounter location)
• Order priority (STAT, urgent, routine)
• Inpatient vs outpatient (via encounters.encounter_type)
• Ordering provider

Visualization

• Line chart: average TAT by day with separate lines for STAT and routine.
• Box-and-whisker: distribution of TAT by priority (identify outliers).
• Gauge: current-day STAT TAT vs target.

Alert Thresholds

• Warning:
• STAT average TAT > 12 minutes for 2 consecutive hours.
• Routine average TAT > 90 minutes for daily average.
• Critical:
• STAT average TAT ≥ 15 minutes for any 1-hour window.

• 5% of STAT orders with TAT > 30 minutes in a day.

• Notification recipients:
• Pharmacy Supervisor
• On-duty Clinical Pharmacist lead
• Nursing unit manager (for unit-specific breaches)

KPI-PIS-002: Dispensing Error Rate

Definition

Percentage of dispensing events where an error was detected and corrected before or after reaching the patient (e.g., wrong drug, dose, patient, label). Uses documented interventions and error flags. Supports patient safety monitoring and internal quality programs.

Calculation Formula

Assumptions:

• pharmacy_dispensing.error_flag – boolean indicating a dispensing error associated with the event (caught at any stage).
• Alternatively, link to pharmacy_interventions where intervention_type relates to dispensing errors (e.g., 'dispensing_error').

-- Using an explicit error flag on dispensing events
SELECT
    COALESCE(
        COUNT(CASE WHEN pd.error_flag = TRUE THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS dispensing_error_rate_pct
FROM pharmacy_dispensing pd
WHERE pd.dispensing_datetime >= :start_datetime
  AND pd.dispensing_datetime < :end_datetime;

-- Alternative: derive from interventions
SELECT
    COALESCE(
        COUNT(DISTINCT pi.dispensing_id) * 100.0
        / NULLIF(COUNT(DISTINCT pd.dispensing_id), 0),
        0
    ) AS dispensing_error_rate_pct
FROM pharmacy_dispensing pd
LEFT JOIN pharmacy_interventions pi
  ON pi.order_id = pd.order_id
 AND pi.intervention_type IN ('dispensing_error', 'wrong_drug_dispensed',
                              'wrong_dose_dispensed', 'wrong_patient_dispensed')
WHERE pd.dispensing_datetime >= :start_datetime
  AND pd.dispensing_datetime < :end_datetime;

Target / Benchmark

• Target: ≤ 0.1% of dispensing events.
• Benchmark: High-performing hospital pharmacies internationally report 0.01–0.1% detectable dispensing error rates when barcode verification is used.

Data Source

• pharmacy_dispensing
• dispensing_id
• order_id
• patient_id
• dispensing_datetime
• dispensed_by
• dispensing_location
• error_flag (BOOLEAN, to be implemented)
• error_type (optional code)
• pharmacy_interventions
• intervention_id
• order_id
• intervention_type
• outcome
• intervention_datetime

Dimensions / Filters

• Time: day, week, month, quarter
• Facility / pharmacy location
• Dispensed_by (user / technician)
• Drug class (via formulary_items.therapeutic_class)
• Inpatient vs outpatient
• Controlled vs non-controlled substances

Visualization

• Bar chart: error rate by month.
• Stacked bar: error types (wrong drug, dose, patient).
• Table: list of error events with drill-down.

Alert Thresholds

• Warning:
• Monthly error rate > 0.1% or
• Any single day with ≥ 3 dispensing errors.
• Critical:
• Monthly error rate ≥ 0.2% or
• Any sentinel event (e.g., wrong patient reached and administered).
• Notification recipients:
• Pharmacy Supervisor
• Chief Pharmacist / Director
• Quality & Patient Safety Officer

KPI-PIS-003: Pharmacist Intervention Rate

Definition

Percentage of verified orders that required a clinical pharmacist intervention (dose adjustment, therapy change, clarification). Indicates the level of clinical review and CDS effectiveness.

Calculation Formula

Assumptions:

• pharmacy_orders.order_status = 'verified' indicates completed verification.
• pharmacy_interventions linked by order_id.

SELECT
    COALESCE(
        COUNT(DISTINCT pi.order_id) * 100.0
        / NULLIF(COUNT(DISTINCT po.order_id), 0),
        0
    ) AS pharmacist_intervention_rate_pct
FROM pharmacy_orders po
LEFT JOIN pharmacy_interventions pi
  ON pi.order_id = po.order_id
WHERE po.order_status = 'verified'
  AND po.verified_datetime >= :start_datetime
  AND po.verified_datetime < :end_datetime;

Target / Benchmark

• Target: 5–10% of verified orders.
• Rationale: Too low may indicate superficial review or under-documentation; too high may indicate poor order quality or overly sensitive CDS.

Data Source

• pharmacy_orders
• order_id
• verified_datetime
• order_status
• facility_id
• ordering_provider_id
• pharmacy_interventions
• intervention_id
• order_id
• intervention_type
• outcome
• intervention_datetime

Dimensions / Filters

• Time: month, quarter
• Facility, department
• Intervention type (dose adjustment, interaction, duplication, allergy, renal adjustment)
• Provider
• Drug class

Visualization

• Line chart: intervention rate trend by month.
• Bar chart: intervention rate by department or provider.
• Pie chart: distribution by intervention type.

Alert Thresholds

• Warning:
• Intervention rate < 3% or > 15% for 2 consecutive months.
• Critical:
• Intervention rate < 1% (under-documentation) or > 20% (systemic ordering issues).
• Notification recipients:
• Chief Pharmacist / Director
• Clinical Pharmacy Lead
• Clinical Informatics (for CDS tuning)

KPI-PIS-004: Formulary Compliance Rate

Definition

Percentage of medication orders that use formulary-approved drugs. Supports cost control, standardization, and compliance with Pharmacy & Therapeutics (P&T) committee policies.

Calculation Formula

Assumptions:

• pharmacy_orders.drug_code_rxnorm links to formulary_items.drug_code_rxnorm.
• formulary_items.formulary_status – 'on_formulary', 'non_formulary', 'restricted'.

SELECT
    COALESCE(
        COUNT(CASE WHEN fi.formulary_status = 'on_formulary' THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS formulary_compliance_rate_pct
FROM pharmacy_orders po
LEFT JOIN formulary_items fi
  ON fi.drug_code_rxnorm = po.drug_code_rxnorm
 AND fi.formulary_status IN ('on_formulary', 'restricted', 'non_formulary')
WHERE po.order_received_datetime >= :start_datetime
  AND po.order_received_datetime < :end_datetime;

Optionally, treat approved non-formulary orders (with documented override) as compliant.

Target / Benchmark

• Target: ≥ 95% formulary compliance.
• Benchmark: Common target in large UAE and GCC hospitals for formulary adherence.

Data Source

• pharmacy_orders
• order_id
• drug_code_rxnorm
• order_received_datetime
• facility_id
• ordering_provider_id
• formulary_items
• item_id
• drug_code_rxnorm
• formulary_status
• restriction_type
• is_controlled

Dimensions / Filters

• Time: month, quarter, year
• Facility
• Department
• Provider
• Drug class
• Payer (for cost impact)

Visualization

• Bar chart: compliance rate by department.
• Line chart: monthly trend.
• Table: top non-formulary drugs ordered.

Alert Thresholds

• Warning:
• Facility-level compliance < 95% for a month.
• Critical:
• Compliance < 90% or any provider with < 80% compliance for 2 consecutive months.
• Notification recipients:
• Chief Pharmacist / Director
• P&T Committee Chair
• Department Heads (for provider-level issues)

KPI-PIS-005: eMAR Barcode Scanning Rate

Definition

Percentage of medication administrations where barcode verification was successfully performed (patient + medication). Measures adherence to closed-loop medication administration and supports DOH/DHA safety expectations.

Calculation Formula

SELECT
    COALESCE(
        COUNT(CASE WHEN ma.barcode_verified = TRUE THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS emar_barcode_scanning_rate_pct
FROM medication_administration ma
WHERE ma.scheduled_datetime >= :start_datetime
  AND ma.scheduled_datetime < :end_datetime;

Target / Benchmark

• Target: ≥ 95% of administrations barcode-verified.
• Benchmark: International closed-loop medication administration programs aim for ≥ 95–98%.

Data Source

• medication_administration
• admin_id
• order_id
• patient_id
• scheduled_datetime
• actual_datetime
• barcode_verified (BOOLEAN)
• status (given, held, refused, omitted)
• administered_by
• facility_id
• encounter_id

Dimensions / Filters

• Time: shift, day, week, month
• Facility, ward/unit
• Nurse / administering user
• Medication type (high-alert, controlled, IV vs oral)
• Inpatient vs day-care

Visualization

• Line chart: scanning rate by day.
• Heatmap: unit vs shift (morning/evening/night).
• Table: units with lowest scanning rates.

Alert Thresholds

• Warning:
• Unit-level scanning rate < 90% for any day.
• Critical:
• Facility-wide scanning rate < 90% for 2 consecutive days or
• High-alert medications scanning rate < 95%.
• Notification recipients:
• Nursing Director
• Pharmacy Supervisor
• Quality & Patient Safety Officer

KPI-PIS-006: Controlled Substance Discrepancy Rate

Definition

Percentage of controlled substance transactions with documented discrepancies (missing doses, count mismatches, unexplained waste). Supports UAE MOH controlled substance compliance and internal diversion monitoring.

Calculation Formula

Assumptions:

• controlled_substance_log.transaction_type – 'dispense', 'return', 'waste', 'adjustment', 'discrepancy'.
• controlled_substance_log.discrepancy_flag – BOOLEAN.

SELECT
    COALESCE(
        COUNT(CASE WHEN csl.discrepancy_flag = TRUE
                   OR csl.transaction_type = 'discrepancy'
              THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS cs_discrepancy_rate_pct
FROM controlled_substance_log csl
WHERE csl.transaction_datetime >= :start_datetime
  AND csl.transaction_datetime < :end_datetime;

Target / Benchmark

• Target: ≤ 0.5% of controlled substance transactions.
• Regulatory context: UAE MOH expects robust controlled substance tracking and investigation of discrepancies (Federal Law No. 14 of 1995 and amendments).

Data Source

• controlled_substance_log
• log_id
• drug_code_rxnorm
• controlled_schedule
• transaction_type
• quantity
• balance_after
• from_user
• to_user
• witness_id
• waste_amount
• location
• transaction_datetime
• discrepancy_flag (BOOLEAN)
• discrepancy_reason (TEXT)

Dimensions / Filters

• Time: day, month
• Facility, vault/ADC location
• Drug / controlled schedule
• From_user / to_user
• Transaction_type

Visualization

• Line chart: discrepancy rate by month.
• Bar chart: discrepancies by location or drug.
• Table: list of discrepancy events with investigation status.

Alert Thresholds

• Warning:
• Monthly discrepancy rate > 0.5% at any location.
• Critical:
• Any single discrepancy with quantity > defined threshold (e.g., > 5 doses) or
• Repeated discrepancies involving same user or drug.
• Notification recipients:
• Pharmacy Supervisor
• Chief Pharmacist / Director
• Compliance Officer / MOH liaison

KPI-PIS-007: Antimicrobial Stewardship Acceptance Rate

Definition

Percentage of antimicrobial stewardship recommendations that are accepted by prescribers (e.g., de-escalation, IV-to-oral switch, stop therapy). Supports antimicrobial stewardship programs and DOH/DHA quality initiatives.

Calculation Formula

SELECT
    COALESCE(
        COUNT(CASE WHEN asr.accepted = TRUE THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS stewardship_acceptance_rate_pct
FROM antimicrobial_stewardship_reviews asr
WHERE asr.reviewed_datetime >= :start_datetime
  AND asr.reviewed_datetime < :end_datetime;

Target / Benchmark

• Target: ≥ 70% acceptance.
• Benchmark: Many stewardship programs report 60–80% acceptance rates for evidence-based recommendations.

Data Source

• antimicrobial_stewardship_reviews
• review_id
• order_id
• patient_id
• antibiotic_rxnorm
• review_type
• indication
• culture_result_id
• recommendation
• accepted (BOOLEAN)
• reviewed_by
• reviewed_datetime
• prescriber_response

Dimensions / Filters

• Time: month, quarter, year
• Facility
• Review_type (de-escalation, IV-to-oral, stop, dose optimization)
• Ward (ICU vs non-ICU)
• Prescriber specialty
• Organism / infection type

Visualization

• Bar chart: acceptance rate by review_type.
• Line chart: overall acceptance trend.
• Table: top prescribers with low acceptance (for targeted education).

Alert Thresholds

• Warning:
• Overall acceptance < 70% for a quarter.
• Critical:
• Acceptance < 50% or
• Persistent low acceptance for specific high-impact recommendations (e.g., de-escalation).
• Notification recipients:
• Antimicrobial Stewardship Pharmacist
• Infectious Disease Lead
• Pharmacy & Therapeutics Committee

KPI-PIS-008: Medication Reconciliation Completion Rate

Definition

Percentage of eligible encounters (admissions and discharges) where medication reconciliation was completed within defined timeframes. Supports patient safety and continuity of care, and aligns with DOH/DHA accreditation expectations.

Calculation Formula

Assumptions:

• Admission reconciliation: reconciliation_type = 'admission'.
• Discharge reconciliation: reconciliation_type = 'discharge'.
• Completion defined as at least one reconciliation record for the encounter within the time window.

-- Admission reconciliation within 24h
SELECT
    COALESCE(
        COUNT(DISTINCT CASE
            WHEN mr.reconciliation_type = 'admission'
             AND mr.reconciled_datetime <= e.admit_datetime + INTERVAL '24 hours'
            THEN e.encounter_id
        END) * 100.0
        / NULLIF(COUNT(DISTINCT e.encounter_id), 0),
        0
    ) AS med_recon_completion_rate_pct
FROM encounters e
LEFT JOIN medication_reconciliation mr
  ON mr.encounter_id = e.encounter_id
WHERE e.encounter_type = 'inpatient'
  AND e.admit_datetime >= :start_datetime
  AND e.admit_datetime < :end_datetime;

-- Discharge reconciliation before discharge
SELECT
    COALESCE(
        COUNT(DISTINCT CASE
            WHEN mr.reconciliation_type = 'discharge'
             AND mr.reconciled_datetime <= e.discharge_datetime
            THEN e.encounter_id
        END) * 100.0
        / NULLIF(COUNT(DISTINCT e.encounter_id), 0),
        0
    ) AS discharge_recon_completion_rate_pct
FROM encounters e
LEFT JOIN medication_reconciliation mr
  ON mr.encounter_id = e.encounter_id
WHERE e.encounter_type = 'inpatient'
  AND e.discharge_datetime >= :start_datetime
  AND e.discharge_datetime < :end_datetime;

Target / Benchmark

• Target: ≥ 95% completion:
• Within 24 hours of admission.
• Before discharge.
• Benchmark: Common accreditation and patient safety targets in UAE/JCI-accredited hospitals.

Data Source

• encounters (from scheduling module)
• encounter_id
• encounter_type
• admit_datetime
• discharge_datetime
• facility_id
• department_id
• medication_reconciliation
• recon_id
• patient_id
• encounter_id
• reconciliation_type
• reconciled_by
• reconciled_datetime

Dimensions / Filters

• Time: month, quarter
• Facility, department
• Reconciled_by (pharmacist vs physician vs nurse)
• Admission vs discharge
• Age group (paediatric vs adult vs geriatric)

Visualization

• Gauge: overall completion rate vs 95% target.
• Bar chart: completion by department.
• Line chart: monthly trend.

Alert Thresholds

• Warning:
• Department completion rate < 95% for a month.
• Critical:
• Facility-wide completion < 90% or
• Any high-risk unit (ICU, oncology) < 95%.
• Notification recipients:
• Chief Pharmacist / Director
• Nursing Director
• Quality & Patient Safety Officer

KPI-PIS-009: Inventory Stockout Rate

Definition

Percentage of active inventory items that experienced stockout (quantity_on_hand = 0) while there was demand (orders or administrations) during the period. Measures inventory management performance.

Calculation Formula

Assumptions:

• pharmacy_inventory.quantity_on_hand.
• pharmacy_inventory.demand – can be derived from recent usage; for KPI, we consider items with at least one dispensing event in the period.

-- Items with stockout and demand > 0
WITH demanded_items AS (
    SELECT DISTINCT pd.drug_code_rxnorm, pd.facility_id, pd.dispensing_location AS location_id
    FROM pharmacy_dispensing pd
    WHERE pd.dispensing_datetime >= :start_datetime
      AND pd.dispensing_datetime < :end_datetime
),
inventory_snapshot AS (
    SELECT
        pi.inventory_id,
        pi.drug_code_rxnorm,
        pi.facility_id,
        pi.location_id,
        pi.quantity_on_hand
    FROM pharmacy_inventory pi
    -- snapshot at end of period or daily snapshot table if available
)
SELECT
    COALESCE(
        COUNT(CASE
            WHEN inv.quantity_on_hand = 0
             AND di.drug_code_rxnorm IS NOT NULL
            THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS inventory_stockout_rate_pct
FROM inventory_snapshot inv
LEFT JOIN demanded_items di
  ON di.drug_code_rxnorm = inv.drug_code_rxnorm
 AND di.facility_id = inv.facility_id
 AND di.location_id = inv.location_id;

Target / Benchmark

• Target: ≤ 2% of active items.
• Benchmark: Typical target for hospital pharmacies balancing cost and availability.

Data Source

• pharmacy_inventory
• inventory_id
• drug_code_rxnorm
• facility_id
• location_id
• quantity_on_hand
• reorder_point
• par_level
• last_count_date
• pharmacy_dispensing
• dispensing_id
• drug_code_rxnorm
• dispensing_datetime
• dispensing_location

Dimensions / Filters

• Time: day, week, month
• Facility, location (central pharmacy, ward stock, ADC)
• Drug class
• Critical vs non-critical medications

Visualization

• Bar chart: stockout rate by location.
• Heatmap: drug class vs month.
• Table: list of items with repeated stockouts.

Alert Thresholds

• Warning:
• Location stockout rate > 2% for a month.
• Critical:
• Any critical medication (e.g., emergency drugs) stockout at any time.
• Notification recipients:
• Pharmacy Inventory Manager
• Pharmacy Supervisor
• Procurement / Supply Chain

KPI-PIS-010: Medication Waste Rate

Definition

Percentage of medication cost wasted (expired, wasted doses, returns not reusable) relative to total dispensed cost. Includes controlled and non-controlled medications.

Calculation Formula

Assumptions:

• controlled_substance_log.waste_amount for controlled waste.
• inventory_transactions.transaction_type = 'waste' for non-controlled.
• pharmacy_inventory.unit_cost or pharmacy_dispensing.unit_cost for cost.

-- Total wasted cost
WITH waste_events AS (
    SELECT
        csl.drug_code_rxnorm,
        csl.waste_amount AS quantity,
        csl.transaction_datetime
    FROM controlled_substance_log csl
    WHERE csl.waste_amount > 0
      AND csl.transaction_datetime >= :start_datetime
      AND csl.transaction_datetime < :end_datetime

    UNION ALL

    SELECT
        pi.drug_code_rxnorm,
        it.quantity,
        it.transaction_datetime
    FROM inventory_transactions it
    JOIN pharmacy_inventory pi
      ON pi.inventory_id = it.inventory_id
    WHERE it.transaction_type = 'waste'
      AND it.transaction_datetime >= :start_datetime
      AND it.transaction_datetime < :end_datetime
),
waste_cost AS (
    SELECT
        SUM(we.quantity * pi.unit_cost) AS total_waste_cost
    FROM waste_events we
    JOIN pharmacy_inventory pi
      ON pi.drug_code_rxnorm = we.drug_code_rxnorm
),
dispensed_cost AS (
    SELECT
        SUM(pd.quantity_dispensed * pi.unit_cost) AS total_dispensed_cost
    FROM pharmacy_dispensing pd
    JOIN pharmacy_inventory pi
      ON pi.drug_code_rxnorm = pd.drug_code_rxnorm
    WHERE pd.dispensing_datetime >= :start_datetime
      AND pd.dispensing_datetime < :end_datetime
)
SELECT
    COALESCE(
        wc.total_waste_cost * 100.0 / NULLIF(dc.total_dispensed_cost, 0),
        0
    ) AS medication_waste_rate_pct
FROM waste_cost wc, dispensed_cost dc;

Target / Benchmark

• Target: ≤ 3% of dispensed cost.
• Benchmark: Typical internal target for hospital pharmacies; may be tightened over time.

Data Source

• controlled_substance_log
• waste_amount
• transaction_datetime
• drug_code_rxnorm
• inventory_transactions
• transaction_type
• quantity
• inventory_id
• transaction_datetime
• pharmacy_inventory
• inventory_id
• drug_code_rxnorm
• unit_cost
• pharmacy_dispensing
• quantity_dispensed
• drug_code_rxnorm
• dispensing_datetime

Dimensions / Filters

• Time: month, quarter, year
• Facility, location
• Drug class
• Controlled vs non-controlled
• Reason (expiry, breakage, patient-specific waste)

Visualization

• Line chart: waste rate trend.
• Bar chart: waste cost by drug class.
• Pareto chart: top 20 drugs contributing to waste.

Alert Thresholds

• Warning:
• Monthly waste rate > 3%.
• Critical:
• Waste rate > 5% or
• Any single drug with waste cost > defined threshold (e.g., AED 50,000 per quarter).
• Notification recipients:
• Pharmacy Inventory Manager
• Chief Pharmacist / Director
• Finance / Cost Control

KPI-PIS-011: NABIDH/Malaffi Dispensing Submission Rate

Definition

Percentage of medication dispensing events successfully submitted and accepted by the Dubai (NABIDH) and Abu Dhabi (Malaffi) Health Information Exchanges. Supports regulatory interoperability requirements.

Calculation Formula

Assumptions:

• pharmacy_dispensing.hie_submission_status – 'accepted', 'rejected', 'pending'.
• If not stored directly, use integration_message_log with source_module = 'PIS' and message_type = 'dispense'.

-- Using a status field on dispensing
SELECT
    COALESCE(
        COUNT(CASE WHEN pd.hie_submission_status = 'accepted' THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS hie_submission_rate_pct
FROM pharmacy_dispensing pd
WHERE pd.dispensing_datetime >= :start_datetime
  AND pd.dispensing_datetime < :end_datetime
  AND pd.facility_id IN (:dubai_abu_dhabi_facilities);

-- Using integration_message_log
SELECT
    COALESCE(
        COUNT(CASE WHEN iml.ack_status = 'AA' THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0),
        0
    ) AS hie_submission_rate_pct
FROM pharmacy_dispensing pd
LEFT JOIN integration_message_log iml
  ON iml.source_module = 'PIS'
 AND iml.message_type = 'dispense'
 AND iml.source_record_id = pd.dispensing_id
WHERE pd.dispensing_datetime >= :start_datetime
  AND pd.dispensing_datetime < :end_datetime
  AND pd.facility_id IN (:dubai_abu_dhabi_facilities);

Target / Benchmark

• Target: ≥ 99.5% accepted submissions.
• Context: NABIDH and Malaffi require high completeness and timeliness for medication data.

Data Source

• pharmacy_dispensing
• dispensing_id
• dispensing_datetime
• facility_id
• hie_submission_status (to be implemented)
• integration_message_log (from integration module)
• message_id
• source_module
• message_type
• source_record_id
• ack_status (AA/AE/AR)
• target_system ('NABIDH', 'Malaffi')
• sent_datetime

Dimensions / Filters

• Time: day, week, month
• HIE (NABIDH vs Malaffi)
• Facility
• Error type (validation error, connectivity, mapping)

Visualization

• Gauge: overall submission rate vs 99.5% target.
• Line chart: daily submission rate.
• Table: list of failed submissions with error reasons.

Alert Thresholds

• Warning:
• Daily submission rate < 99.5%.
• Critical:
• Any 1-hour window with submission rate < 95% or
• Continuous failures (e.g., > 10 consecutive rejections).
• Notification recipients:
• Integration / IT Team
• Pharmacy Supervisor
• Compliance Officer (for prolonged issues)

Standard Reports

Dashboard Wireframe

Below is an HTML wireframe mockup for the Pharmacy Analytics Dashboard (SCR-PIS-010) showing KPI cards, charts, and filters.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Pharmacy Analytics Dashboard</title>
</head>
<body style="font-family: Arial, sans-serif; background:#f5f5f5; margin:0; padding:0;">
  <header style="background:#004b8d; color:#fff; padding:16px 24px;">
    <h1 style="margin:0; font-size:20px;">Pharmacy Analytics Dashboard</h1>
    <div style="margin-top:8px; font-size:12px;">
      Facility:
      <select style="margin-right:8px;">
        <option>All Facilities</option>
        <option>Dubai General Hospital</option>
        <option>Abu Dhabi City Hospital</option>
      </select>
      Period:
      <select style="margin-right:8px;">
        <option>Last 7 days</option>
        <option>Last 30 days</option>
        <option>Last Quarter</option>
      </select>
      Department:
      <select>
        <option>All Departments</option>
        <option>ICU</option>
        <option>Medical Ward</option>
        <option>Surgical Ward</option>
      </select>
    </div>
  </header>

  <main style="padding:16px 24px;">
    <!-- KPI Cards Row 1 -->
    <section style="display:flex; gap:16px; margin-bottom:16px;">
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #2e7d32;">
        <div style="font-size:12px; color:#666;">Order Verification TAT (STAT)</div>
        <div style="font-size:24px; font-weight:bold;">11.4 min</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≤ 15 min • ▼ 1.2 vs last week</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #1976d2;">
        <div style="font-size:12px; color:#666;">Dispensing Error Rate</div>
        <div style="font-size:24px; font-weight:bold;">0.06%</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≤ 0.1% • Stable</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #f9a825;">
        <div style="font-size:12px; color:#666;">Pharmacist Intervention Rate</div>
        <div style="font-size:24px; font-weight:bold;">8.3%</div>
        <div style="font-size:11px; color:#555;">Target 5–10% • ▲ 0.5 vs last month</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #c62828;">
        <div style="font-size:12px; color:#666;">Controlled Substance Discrepancy Rate</div>
        <div style="font-size:24px; font-weight:bold;">0.3%</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≤ 0.5% • No critical events</div>
      </div>
    </section>

    <!-- KPI Cards Row 2 -->
    <section style="display:flex; gap:16px; margin-bottom:16px;">
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #2e7d32;">
        <div style="font-size:12px; color:#666;">eMAR Barcode Scanning Rate</div>
        <div style="font-size:24px; font-weight:bold;">96.7%</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≥ 95%</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #1976d2;">
        <div style="font-size:12px; color:#666;">Formulary Compliance</div>
        <div style="font-size:24px; font-weight:bold;">94.2%</div>
        <div style="font-size:11px; color:#c62828;">Target ≥ 95% • Review non-formulary use</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #6a1b9a;">
        <div style="font-size:12px; color:#666;">Stewardship Acceptance Rate</div>
        <div style="font-size:24px; font-weight:bold;">72.5%</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≥ 70%</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; border-top:4px solid #00838f;">
        <div style="font-size:12px; color:#666;">NABIDH/Malaffi Submission Rate</div>
        <div style="font-size:24px; font-weight:bold;">99.7%</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≥ 99.5%</div>
      </div>
    </section>

    <!-- Charts Row -->
    <section style="display:flex; gap:16px; margin-bottom:16px;">
      <div style="flex:2; background:#fff; padding:12px 16px; border-radius:4px; min-height:220px;">
        <h2 style="margin:0 0 8px 0; font-size:14px;">Verification TAT & Intervention Trend</h2>
        <div style="font-size:11px; color:#666; margin-bottom:4px;">
          [Line chart placeholder: STAT/Routine TAT (min) & Intervention Rate (%) by day]
        </div>
        <div style="border:1px dashed #ccc; height:170px;"></div>
      </div>
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; min-height:220px;">
        <h2 style="margin:0 0 8px 0; font-size:14px;">Barcode Scanning by Unit</h2>
        <div style="font-size:11px; color:#666; margin-bottom:4px;">
          [Horizontal bar chart placeholder: units vs scanning rate]
        </div>
        <div style="border:1px dashed #ccc; height:170px;"></div>
      </div>
    </section>

    <!-- Bottom Row: Inventory & Table -->
    <section style="display:flex; gap:16px;">
      <div style="flex:1; background:#fff; padding:12px 16px; border-radius:4px; min-height:220px;">
        <h2 style="margin:0 0 8px 0; font-size:14px;">Inventory Stockout & Waste</h2>
        <div style="font-size:11px; color:#666; margin-bottom:4px;">
          [Stacked bar / line chart placeholder: stockout rate & waste rate by month]
        </div>
        <div style="border:1px dashed #ccc; height:170px;"></div>
      </div>
      <div style="flex:1.2; background:#fff; padding:12px 16px; border-radius:4px; min-height:220px;">
        <h2 style="margin:0 0 8px 0; font-size:14px;">Alerts & Outliers</h2>
        <table style="width:100%; border-collapse:collapse; font-size:11px;">
          <thead>
            <tr>
              <th style="text-align:left; border-bottom:1px solid #ddd; padding:4px;">Date</th>
              <th style="text-align:left; border-bottom:1px solid #ddd; padding:4px;">KPI</th>
              <th style="text-align:left; border-bottom:1px solid #ddd; padding:4px;">Unit/Location</th>
              <th style="text-align:left; border-bottom:1px solid #ddd; padding:4px;">Value</th>
              <th style="text-align:left; border-bottom:1px solid #ddd; padding:4px;">Status</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">07-Feb-2026</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">Formulary Compliance</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">Medical Ward 3</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">88.1%</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0; color:#c62828;">Critical</td>
            </tr>
            <tr>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">06-Feb-2026</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">CS Discrepancy Rate</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">ICU ADC</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0;">0.7%</td>
              <td style="padding:4px; border-bottom:1px solid #f0f0f0; color:#f9a825;">Warning</td>
            </tr>
          </tbody>
        </table>
        <div style="margin-top:8px; text-align:right;">
          <button style="font-size:11px; padding:4px 8px;">View Details</button>
          <button style="font-size:11px; padding:4px 8px;">Export CSV</button>
        </div>
      </div>
    </section>
  </main>
</body>
</html>

Regulatory Reports

This section outlines UAE-specific regulatory and compliance reporting supported by the PIS KPIs and reporting layer.

1. UAE MOH – Controlled Substances

Purpose: Comply with UAE MOH requirements for controlled substance tracking and reporting (Federal Law No. 14 of 1995 and subsequent amendments; MOH circulars).

Key Reports / Data Sets

1. Monthly Controlled Substance Utilization Report - Source: controlled_substance_log, pharmacy_dispensing, pharmacy_inventory. - Content:

   • Drug (RxNorm, local code), strength, form.
   • Opening balance, quantities received, dispensed, returned, wasted, closing balance.
   • Per facility and vault/ADC.
   • Frequency: Monthly (RPT-PIS-004).
   • Format: MOH portal template (CSV/Excel) + signed PDF summary.

2. Discrepancy & Incident Report - Source: controlled_substance_log (discrepancy_flag, discrepancy_reason). - Trigger: Any discrepancy event or suspected diversion. - Content:

   • Drug, quantity, location, involved users, date/time, investigation status.
   • Frequency: Event-driven; summary monthly.
   • Format: PDF + CSV export for MOH portal if required.

3. Prescriber & Pharmacist Authorization Audit - Cross-reference with providers and provider_credentials (from EHR module). - Ensures only authorized prescribers and pharmacists handle controlled substances.

2. DOH (Abu Dhabi) / DHA (Dubai) – Statistical & Quality Submissions

Purpose: Support emirate-level quality and utilization reporting.

Examples

1. Medication Safety Indicators - Dispensing error rate (KPI-PIS-002). - Barcode scanning rate (KPI-PIS-005). - Controlled substance discrepancy rate (KPI-PIS-006). - Format: Aggregated statistics by month and facility; CSV/Excel.

2. Antimicrobial Stewardship Metrics - Stewardship acceptance rate (KPI-PIS-007). - Antibiotic utilization (DDD per 1,000 patient-days – derived from pharmacy_dispensing and encounters). - Facility antibiogram (via LIS integration, outside this module but referenced). - Format: Excel/PDF as per DOH/DHA templates.

3. Formulary & High-Cost Drug Utilization - Formulary compliance (KPI-PIS-004). - High-cost biologics and oncology drug usage and waste (KPI-PIS-010).

3. UAE PDPL – Data Protection & Audit

Purpose: Demonstrate compliance with UAE Federal Decree-Law No. 45/2021 (PDPL) for personal data processing in pharmacy workflows.

Relevant Reports

1. Access Audit Log Report - Source: users, roles, permissions, and audit log tables (not owned by PIS but referenced). - Content:

   • Who accessed which patient’s medication data, when, and for what action (view, edit, dispense).
   • Use: Respond to data subject access requests and internal audits.

2. Data Minimization & Retention Report - Shows retention periods for pharmacy data (orders, dispensing, controlled logs) and anonymization/pseudonymization status for analytics.

3. Breach Investigation Support - Extracts from audit logs and PIS tables to identify scope of any unauthorized access to medication records.

4. NABIDH / Malaffi Compliance Metrics

Purpose: Ensure medication data shared with Dubai’s NABIDH and Abu Dhabi’s Malaffi meets completeness and quality requirements.

Key Metrics / Reports

1. Dispensing Submission Rate (KPI-PIS-011) - Submission and acceptance rates by HIE, facility, and day.

2. Data Completeness Report - Checks mandatory fields for HIE messages:

   • Patient identifiers (Emirates ID, MRN).
   • Medication code (RxNorm/local).
   • Dose, route, frequency, prescriber, dispenser.
   • Source: pharmacy_dispensing, pharmacy_orders, patients, providers.

3. Error & Rejection Analysis - Source: integration_message_log. - Content:

   • Error codes, descriptions, counts, and trends.
   • Use: Correct mapping issues and improve data quality.

Ad-Hoc Reporting

Available Data Fields for Custom Queries

The PIS reporting layer should expose a semantic data mart or views that combine key tables with shared entities. Examples of commonly used fields:

• Patient & Encounter (via shared entities)
• patients.patient_id, patients.mrn, patients.full_name, patients.emirates_id
• encounters.encounter_id, encounters.encounter_type, encounters.admit_datetime, encounters.discharge_datetime

• facilities.facility_name, departments.department_name

• Orders (pharmacy_orders)

• order_id, patient_id, encounter_id
• ordering_provider_id, order_priority, order_status
• drug_code_rxnorm, drug_name, dose, dose_unit, route, frequency, duration_days
• is_controlled, controlled_schedule

• order_received_datetime, verified_datetime

• Dispensing (pharmacy_dispensing)

• dispensing_id, order_id, patient_id
• drug_code_rxnorm, quantity_dispensed, lot_number, expiry_date
• dispensed_by, verified_by, dispensing_datetime
• dispensing_location, label_printed

• ndc_code (if used), hie_submission_status, error_flag

• Administration (medication_administration)

• admin_id, order_id, patient_id, encounter_id
• scheduled_datetime, actual_datetime
• dose_given, route, site
• administered_by, witness_id
• status (given/held/refused/omitted)
• hold_reason, prn_indication, prn_response

• barcode_verified

• Formulary (formulary, formulary_items)

• formulary_id, formulary_name, facility_id, status
• item_id, drug_code_rxnorm, drug_name, generic_name
• formulary_status, restriction_type, therapeutic_class

• therapeutic_alternative_rxnorm, requires_prior_auth, is_controlled

• Inventory (pharmacy_inventory, inventory_transactions)

• inventory_id, drug_code_rxnorm, facility_id, location_id
• quantity_on_hand, reorder_point, par_level, unit_cost, expiry_date
• transaction_id, transaction_type, quantity, from_location, to_location

• performed_by, transaction_datetime, reason

• Controlled Substances (controlled_substance_log)

• log_id, drug_code_rxnorm, controlled_schedule
• transaction_type, quantity, balance_after
• from_user, to_user, witness_id
• waste_amount, waste_witnessed_by

• location, transaction_datetime, discrepancy_flag, discrepancy_reason

• Clinical Interventions & Stewardship

• pharmacy_interventions: intervention_type, recommendation, outcome, time_spent_minutes

• antimicrobial_stewardship_reviews: review_type, indication, recommendation, accepted, prescriber_response

• Medication Reconciliation (medication_reconciliation)

• recon_id, patient_id, encounter_id
• reconciliation_type (admission/discharge/transfer)
• home_medication_rxnorm, home_dose, home_route
• action_taken, new_order_id, discrepancy_type
• reconciled_by, reconciled_datetime

Export Formats

The reporting module should support:

• CSV – for data analysis and import into external tools.
• Excel (XLSX) – formatted reports with filters and pivot-ready data.
• PDF – official, read-only reports for management and regulatory submission.
• JSON – for API-based consumption by other systems (optional).

All exports must:

• Respect UAE PDPL:
• Apply role-based access control.
• Support de-identification/anonymization options for research/analytics.
• Log export events in an audit log (who, when, which dataset, purpose).

Scheduled Report Delivery

Capabilities:

1. Scheduling - Users can schedule reports (daily, weekly, monthly, quarterly, custom cron). - Time zone awareness (Gulf Standard Time).

2. Delivery Channels - Secure email with link to download (no PHI in email body). - Secure internal portal / dashboard. - SFTP drop for integration with corporate BI tools.

3. Access Control & Audit - Only authorized roles (e.g., Chief Pharmacist, Pharmacy Supervisor, Quality Officer) can schedule or receive sensitive reports (e.g., controlled substances). - All scheduled runs logged:

   • report_id, parameters, generated_by, generated_at, delivery_targets.
   • Ability to revoke schedules and access when staff roles change.

4. Paperless Transformation

For each report and KPI, the system replaces:

• Manual tally sheets for controlled substances with automated logs and MOH-ready exports.
• Spreadsheet-based tracking of interventions, stewardship, and inventory with centralized dashboards.
• Paper-based medication reconciliation forms with electronic records and completion KPIs.
• Ad-hoc Excel reports manually compiled from multiple systems with unified, parameterized queries and scheduled delivery.

This ensures the Pharmacy Information System supports a fully paperless, auditable, and analytics-driven medication-use process across the UAE facilities of Gates Group.