Scheduling & Bed/OR Management KPIs & Reporting

KPI Summary

Note: Where additional helper tables are implied (e.g., cleaning timestamps, integration logs), they are assumed to exist in the broader HIS (e.g., cleaning_tasks, integration_message_log) and are referenced, not owned, by this module.

KPI Definitions

KPI-SCHED-001: Appointment No-Show Rate

Description
Percentage of scheduled outpatient appointments where the patient did not attend and did not cancel in advance. This measures scheduling effectiveness, patient engagement, and clinic efficiency.

Calculation Formula

SELECT
    CAST(
        COUNT(CASE WHEN a.status = 'no_show' THEN 1 END) * 100.0
        / NULLIF(COUNT(CASE WHEN a.status IN ('completed', 'no_show') THEN 1 END), 0)
        AS DECIMAL(5,2)
    ) AS appointment_no_show_rate_pct
FROM appointments a
WHERE a.appointment_datetime >= :start_date
  AND a.appointment_datetime < :end_date
  AND a.facility_id = COALESCE(:facility_id, a.facility_id)
  AND a.department_id = COALESCE(:department_id, a.department_id)
  AND a.provider_id = COALESCE(:provider_id, a.provider_id);

Target / Benchmark

Data Source

• appointments.status (values: scheduled, checked_in, completed, cancelled, no_show, etc.)
• appointments.appointment_datetime
• appointments.facility_id
• appointments.department_id
• appointments.provider_id

Dimensions / Filters

• Time: day, week, month, quarter, year
• Facility (multi-hospital groups)
• Department / specialty
• Provider
• Appointment type (appointments.appointment_type_id)
• Payer (via join to encounters/payers if needed)

Visualization

• Line chart: trend of no-show rate by month
• Bar chart: no-show rate by provider or department
• Table: top patients with repeated no-shows (for internal use, PDPL role-based access)

Alert Thresholds

• Warning: > 10% for 2 consecutive weeks (clinic-level)
• Critical: > 15% for any week or > 12% for 4 consecutive weeks
• Notifications:
• Department head
• Scheduling Administrator
• Quality & Patient Experience team (summary only, no patient identifiers in email to comply with UAE PDPL)

KPI-SCHED-002: Average Wait Time (Clinic)

Description
Average time from patient check-in to provider first seeing the patient. Measures outpatient flow efficiency and patient experience.

Assumption: encounters (or a related table) stores provider_seen_time. If not, a clinic_flow_events table should be used; adjust accordingly.

Calculation Formula

SELECT
    CAST(
        AVG(
            DATEDIFF(
                MINUTE,
                a.check_in_time,
                e.provider_seen_time
            )
        ) AS DECIMAL(6,2)
    ) AS avg_wait_time_minutes
FROM appointments a
JOIN encounters e
  ON e.encounter_id = a.encounter_id
WHERE a.check_in_time IS NOT NULL
  AND e.provider_seen_time IS NOT NULL
  AND a.appointment_datetime >= :start_date
  AND a.appointment_datetime < :end_date
  AND a.facility_id = COALESCE(:facility_id, a.facility_id)
  AND a.department_id = COALESCE(:department_id, a.department_id)
  AND a.provider_id = COALESCE(:provider_id, a.provider_id);

Target / Benchmark

Data Source

• appointments.check_in_time
• appointments.encounter_id
• appointments.facility_id, department_id, provider_id
• encounters.provider_seen_time (or equivalent field in encounter details/clinic flow table)

Dimensions / Filters

• Time: day, week, month
• Facility
• Department / clinic
• Provider
• Appointment type
• Time of day (session: morning/afternoon/evening)

Visualization

• Line chart: average wait time by day
• Box plot: distribution of wait times by department
• Heatmap: hour-of-day vs. day-of-week average wait time

Alert Thresholds

• Warning: average wait time > 25 minutes for 3 consecutive days
• Critical: average wait time > 35 minutes on any day
• Notifications:
• Clinic manager / Charge Nurse
• Scheduling Administrator
• Operations Director (critical only)

KPI-SCHED-003: Bed Occupancy Rate

Description
Average percentage of active beds occupied over a period. Measures inpatient capacity utilization and informs bed planning.

Assumption: daily occupancy snapshots are derived from bed_assignments and beds.

Calculation Formula

Daily occupancy (subquery):

-- Example for a single day
SELECT
    b.facility_id,
    b.department_id,
    CAST(
        COUNT(DISTINCT CASE WHEN ba.assignment_id IS NOT NULL THEN b.bed_id END) * 100.0
        / NULLIF(COUNT(DISTINCT b.bed_id), 0)
        AS DECIMAL(5,2)
    ) AS bed_occupancy_rate_pct
FROM beds b
LEFT JOIN bed_assignments ba
  ON ba.bed_id = b.bed_id
 AND ba.assigned_datetime <= :day_end
 AND (ba.released_datetime IS NULL OR ba.released_datetime > :day_start)
WHERE b.is_active = 1
  AND b.facility_id = COALESCE(:facility_id, b.facility_id)
  AND b.department_id = COALESCE(:department_id, b.department_id)
GROUP BY b.facility_id, b.department_id;

Period average (conceptual): average of daily occupancy rates over the selected period.

Target / Benchmark

Data Source

• beds.bed_id, beds.facility_id, beds.department_id, beds.is_active
• bed_assignments.bed_id, assigned_datetime, released_datetime

Dimensions / Filters

• Time: daily, weekly, monthly
• Facility
• Ward / department
• Bed type (beds.ward_type, beds.is_isolation, beds.gender_restriction)

Visualization

• Line chart: daily occupancy rate by ward
• Stacked bar: occupancy by ward type (ICU, medical, surgical)
• Gauge: current occupancy vs. target range

Alert Thresholds

• Warning: occupancy > 90% for 2 consecutive days
• Critical: occupancy > 95% on any day or ICU occupancy > 90%
• Notifications:
• Bed Management Coordinator
• Nursing Administration
• Executive on-call (critical)

KPI-SCHED-004: Average Length of Stay (ALOS)

Description
Average inpatient length of stay (LOS) in days, overall and by DRG/specialty. Used for efficiency, capacity planning, and benchmarking.

Calculation Formula

SELECT
    ed.drg_code,
    e.department_id,
    CAST(
        AVG(
            DATEDIFF(
                DAY,
                e.admission_datetime,
                e.discharge_datetime
            )
        ) AS DECIMAL(6,2)
    ) AS alos_days
FROM encounters e
LEFT JOIN encounter_details ed
  ON ed.encounter_id = e.encounter_id
WHERE e.encounter_class = 'inpatient'
  AND e.discharge_datetime IS NOT NULL
  AND e.admission_datetime >= :start_date
  AND e.admission_datetime < :end_date
  AND e.facility_id = COALESCE(:facility_id, e.facility_id)
GROUP BY ed.drg_code, e.department_id;

Target / Benchmark

Targets are set per DRG/specialty based on internal benchmarks and payer expectations (e.g., Daman, Thiqa).

Data Source

• encounters.encounter_class (inpatient)
• encounters.admission_datetime, discharge_datetime
• encounters.facility_id, department_id
• encounter_details.drg_code, case_weight

Dimensions / Filters

• Time: month, quarter, year
• Facility
• Department / specialty
• DRG
• Payer (via encounters.payer_id)

Visualization

• Line chart: ALOS trend by month
• Bar chart: ALOS by DRG vs. benchmark
• Scatter plot: case weight vs. LOS (outlier detection)

Alert Thresholds

• Warning: ALOS for a DRG > 15% above target for 2 consecutive months
• Critical: ALOS for a DRG > 25% above target or sudden spike (>1 day increase) month-on-month
• Notifications:
• Quality & Clinical Governance
• Department heads
• Case Management team

KPI-SCHED-005: Bed Turnaround Time

Description
Average time from bed being released (patient discharge/transfer) to bed being ready for next admission (cleaning completed). Measures housekeeping and bed management efficiency.

Assumption: cleaning tasks are tracked in cleaning_tasks (owned by Cleaning module) with timestamps.

Calculation Formula

SELECT
    b.facility_id,
    b.department_id,
    CAST(
        AVG(
            DATEDIFF(
                MINUTE,
                ba.released_datetime,
                ct.completed_datetime
            )
        ) AS DECIMAL(6,2)
    ) AS avg_bed_turnaround_minutes
FROM bed_assignments ba
JOIN beds b
  ON b.bed_id = ba.bed_id
JOIN cleaning_tasks ct
  ON ct.bed_id = ba.bed_id
 AND ct.trigger_source = 'bed_released'
 AND ct.trigger_reference_id = ba.assignment_id
WHERE ba.released_datetime >= :start_date
  AND ba.released_datetime < :end_date
  AND b.facility_id = COALESCE(:facility_id, b.facility_id)
  AND b.department_id = COALESCE(:department_id, b.department_id)
  AND ct.completed_datetime IS NOT NULL
GROUP BY b.facility_id, b.department_id;

Target / Benchmark

Data Source

• bed_assignments.released_datetime
• bed_assignments.assignment_id
• beds.bed_id, facility_id, department_id
• cleaning_tasks.bed_id, trigger_source, trigger_reference_id, completed_datetime

Dimensions / Filters

• Time: day, week, month
• Facility
• Ward / department
• Bed type (ICU vs. general)
• Cleaning shift (day/night) if available

Visualization

• Line chart: average turnaround time by day
• Bar chart: turnaround by ward
• Table: list of beds with longest turnaround times

Alert Thresholds

• Warning: average turnaround > 60 minutes for a ward on any day
• Critical: average turnaround > 90 minutes or > 3 delayed beds (>120 minutes) in a day
• Notifications:
• Bed Management Coordinator
• Housekeeping Supervisor
• Nursing Administration (critical)

KPI-SCHED-006: OR Utilization Rate

Description
Percentage of available OR time that is used for cases and turnover. Measures efficiency of operating room scheduling and capacity usage.

Assumption:

• or_schedules defines blocks per OR per day.
• or_rooms.max_daily_hours defines total available hours if blocks are not explicitly used.
• turnover_time is derived from gap between cases or stored in or_cases.turnover_minutes if available.

Calculation Formula

Example using actual case duration and scheduled block hours:

WITH case_times AS (
    SELECT
        oc.schedule_id,
        os.or_room_id,
        os.schedule_date,
        SUM(
            DATEDIFF(MINUTE, oc.actual_start, oc.actual_end)
        ) AS total_case_minutes
    FROM or_cases oc
    JOIN or_schedules os
      ON os.schedule_id = oc.schedule_id
    WHERE oc.actual_start IS NOT NULL
      AND oc.actual_end IS NOT NULL
      AND os.schedule_date >= :start_date
      AND os.schedule_date < :end_date
    GROUP BY oc.schedule_id, os.or_room_id, os.schedule_date
),
block_times AS (
    SELECT
        os.or_room_id,
        os.schedule_date,
        SUM(
            DATEDIFF(MINUTE, os.block_start, os.block_end)
        ) AS total_block_minutes
    FROM or_schedules os
    WHERE os.schedule_date >= :start_date
      AND os.schedule_date < :end_date
    GROUP BY os.or_room_id, os.schedule_date
)
SELECT
    bt.or_room_id,
    CAST(
        SUM(ct.total_case_minutes) * 100.0
        / NULLIF(SUM(bt.total_block_minutes), 0)
        AS DECIMAL(5,2)
    ) AS or_utilization_rate_pct
FROM block_times bt
LEFT JOIN case_times ct
  ON ct.or_room_id = bt.or_room_id
 AND ct.schedule_date = bt.schedule_date
GROUP BY bt.or_room_id;

(If turnover minutes are stored separately, add them into total_case_minutes.)

Target / Benchmark

Data Source

• or_schedules.or_room_id, schedule_date, block_start, block_end, block_type
• or_cases.schedule_id, actual_start, actual_end, case_status
• or_rooms.room_id, facility_id, max_daily_hours

Dimensions / Filters

• Time: day, week, month
• Facility
• OR room
• Surgeon
• Specialty (via procedure code or surgeon specialty)
• Block type (elective, emergency)

Visualization

• Line chart: utilization by OR over time
• Bar chart: utilization by surgeon or specialty
• Gantt-style view: scheduled vs. actual case times (operational dashboard)

Alert Thresholds

• Warning: utilization < 65% or > 90% for a room over a month
• Critical: utilization < 60% or > 95% for 2 consecutive months
• Notifications:
• OR Manager
• Surgical Services Director
• Finance/Operations (monthly summary)

KPI-SCHED-007: OR First-Case On-Time Start

Description
Percentage of first cases in each OR that start within 5 minutes of scheduled start time. Measures morning start discipline and downstream impact on the day’s schedule.

Calculation Formula

WITH first_cases AS (
    SELECT
        os.or_room_id,
        os.schedule_date,
        MIN(oc.actual_start) AS first_actual_start,
        MIN(oc.scheduled_start) AS first_scheduled_start,
        COUNT(*) AS total_cases
    FROM or_cases oc
    JOIN or_schedules os
      ON os.schedule_id = oc.schedule_id
    WHERE os.schedule_date >= :start_date
      AND os.schedule_date < :end_date
      AND oc.case_status IN ('completed', 'in_progress')
    GROUP BY os.or_room_id, os.schedule_date
)
SELECT
    CAST(
        COUNT(
            CASE
                WHEN DATEDIFF(
                        MINUTE,
                        fc.first_scheduled_start,
                        fc.first_actual_start
                     ) <= 5
                THEN 1
            END
        ) * 100.0
        / NULLIF(COUNT(*), 0)
        AS DECIMAL(5,2)
    ) AS first_case_on_time_start_pct
FROM first_cases fc;

Assumption: or_cases.scheduled_start exists; if not, use or_schedules.block_start for first case.

Target / Benchmark

Data Source

• or_cases.actual_start, scheduled_start, case_status
• or_cases.schedule_id
• or_schedules.or_room_id, schedule_date

Dimensions / Filters

• Time: day, week, month
• Facility
• OR room
• Surgeon
• Specialty
• Reason for delay (if captured in or_cases.delay_reason)

Visualization

• Line chart: on-time start rate by month
• Bar chart: on-time start by surgeon or OR room
• Table: list of delayed first cases with reasons

Alert Thresholds

• Warning: on-time rate < 85% for a month
• Critical: on-time rate < 75% for 2 consecutive months
• Notifications:
• OR Manager
• Surgical Committee
• Anesthesia Lead

KPI-SCHED-008: Scheduling Lead Time

Description
Average time between appointment creation and appointment date/time. Measures access to care and scheduling responsiveness, especially for routine vs. urgent appointments.

Calculation Formula

SELECT
    at.type_name_en AS appointment_type,
    CAST(
        AVG(
            DATEDIFF(
                DAY,
                a.created_at,
                a.appointment_datetime
            )
        ) AS DECIMAL(6,2)
    ) AS avg_lead_time_days
FROM appointments a
JOIN appointment_types at
  ON at.type_id = a.appointment_type_id
WHERE a.appointment_datetime >= :start_date
  AND a.appointment_datetime < :end_date
  AND a.status IN ('scheduled', 'completed')
  AND a.facility_id = COALESCE(:facility_id, a.facility_id)
GROUP BY at.type_name_en;

Target / Benchmark

(Implementation: map appointment types to urgency categories via master data.)

Data Source

• appointments.created_at, appointment_datetime, status
• appointments.appointment_type_id
• appointment_types.type_id, type_name_en, requires_referral, requires_pre_auth
• Optional: scheduling_rules to classify urgency

Dimensions / Filters

• Time: week, month, quarter
• Facility
• Department / specialty
• Provider
• Appointment type / urgency
• Payer (for access by insurance)

Visualization

• Line chart: average lead time by month
• Bar chart: lead time by department or provider
• Table: distribution of lead times (0–2 days, 3–7, 8–14, >14)

Alert Thresholds

• Warning: routine lead time > 10 days or urgent > 72 hours for any department
• Critical: routine > 14 days or urgent > 96 hours
• Notifications:
• Scheduling Administrator
• Department heads
• Operations Director

KPI-SCHED-009: Waitlist Conversion Rate

Description
Percentage of waitlist entries that result in a booked appointment or bed. Measures effectiveness of waitlist management and recovery of cancelled slots.

Calculation Formula

SELECT
    CAST(
        COUNT(CASE WHEN we.resolution_type = 'booked' THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0)
        AS DECIMAL(5,2)
    ) AS waitlist_conversion_rate_pct
FROM waitlist_entries we
WHERE we.added_datetime >= :start_date
  AND we.added_datetime < :end_date
  AND we.waitlist_type = COALESCE(:waitlist_type, we.waitlist_type)
  AND we.status IN ('resolved', 'cancelled', 'expired');

Target / Benchmark

Data Source

• waitlist_entries.entry_id
• waitlist_entries.waitlist_type (appointment, bed, OR)
• waitlist_entries.added_datetime
• waitlist_entries.status (active, resolved, expired)
• waitlist_entries.resolution_type (booked, cancelled, no_response, etc.)

Dimensions / Filters

• Time: month, quarter
• Facility
• Department / specialty
• Waitlist type (clinic, OR, bed)
• Priority (waitlist_entries.priority)

Visualization

• Line chart: conversion rate over time
• Bar chart: conversion by department or waitlist type
• Table: average days waiting by priority

Alert Thresholds

• Warning: conversion < 50% for any department over a quarter
• Critical: conversion < 40% or average waiting days > defined threshold (e.g., 30 days)
• Notifications:
• Scheduling Administrator
• Department heads
• Patient Experience (summary only)

KPI-SCHED-010: Appointment Cancellation Rate

Description
Percentage of appointments that are cancelled (by patient or provider) relative to all booked appointments. High rates indicate scheduling instability and potential access issues.

Calculation Formula

SELECT
    CAST(
        COUNT(CASE WHEN a.status = 'cancelled' THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0)
        AS DECIMAL(5,2)
    ) AS appointment_cancellation_rate_pct
FROM appointments a
WHERE a.appointment_datetime >= :start_date
  AND a.appointment_datetime < :end_date
  AND a.facility_id = COALESCE(:facility_id, a.facility_id)
  AND a.department_id = COALESCE(:department_id, a.department_id)
  AND a.provider_id = COALESCE(:provider_id, a.provider_id);

Optional: separate patient-initiated vs provider-initiated using cancellation_reason.

Target / Benchmark

Data Source

• appointments.status
• appointments.cancellation_reason
• appointments.appointment_datetime
• appointments.facility_id, department_id, provider_id

Dimensions / Filters

• Time: day, week, month
• Facility
• Department / provider
• Cancellation reason category (patient, provider, system)
• Appointment type

Visualization

• Line chart: cancellation rate trend
• Stacked bar: cancellations by reason category
• Table: high-cancellation providers or clinics

Alert Thresholds

• Warning: cancellation rate > 15% for a department in a month
• Critical: > 20% or sudden spike (>5 percentage points) month-on-month
• Notifications:
• Scheduling Administrator
• Department heads

KPI-SCHED-011: Discharge Before Noon Rate

Description
Percentage of inpatient discharges completed before 12:00 (noon). Earlier discharges improve bed availability and flow from ED and elective admissions.

Calculation Formula

SELECT
    CAST(
        COUNT(
            CASE
                WHEN CAST(e.discharge_datetime AS TIME) < '12:00:00'
                THEN 1
            END
        ) * 100.0
        / NULLIF(COUNT(*), 0)
        AS DECIMAL(5,2)
    ) AS discharge_before_noon_rate_pct
FROM encounters e
WHERE e.encounter_class = 'inpatient'
  AND e.discharge_datetime IS NOT NULL
  AND e.discharge_datetime >= :start_date
  AND e.discharge_datetime < :end_date
  AND e.facility_id = COALESCE(:facility_id, e.facility_id)
  AND e.department_id = COALESCE(:department_id, e.department_id);

Target / Benchmark

Data Source

• encounters.encounter_class
• encounters.discharge_datetime
• encounters.facility_id, department_id

Dimensions / Filters

• Time: day, week, month
• Facility
• Department / ward
• Discharge disposition (encounters.discharge_disposition)

Visualization

• Line chart: rate over time
• Bar chart: rate by ward
• Table: list of late discharges with reasons (if captured)

Alert Thresholds

• Warning: rate < 35% for a ward for a month
• Critical: rate < 25% for 2 consecutive months
• Notifications:
• Bed Management Coordinator
• Discharge Planning / Case Management
• Department heads

KPI-SCHED-012: NABIDH/Malaffi ADT Submission Rate

Description
Percentage of ADT messages (A01/A02/A03/A04) successfully acknowledged (ACK status AA) by NABIDH (Dubai) and Malaffi (Abu Dhabi). Measures HIE compliance and data sharing reliability.

Calculation Formula

SELECT
    im.target_system,
    CAST(
        COUNT(CASE WHEN im.ack_status = 'AA' THEN 1 END) * 100.0
        / NULLIF(COUNT(*), 0)
        AS DECIMAL(5,2)
    ) AS adt_submission_success_rate_pct
FROM integration_message_log im
WHERE im.message_type = 'ADT'
  AND im.event_type IN ('A01','A02','A03','A04')
  AND im.sent_datetime >= :start_date
  AND im.sent_datetime < :end_date
  AND im.target_system IN ('NABIDH', 'Malaffi')
GROUP BY im.target_system;

Target / Benchmark

Data Source

• integration_message_log.message_id
• integration_message_log.message_type (ADT)
• integration_message_log.event_type (A01/A02/A03/A04)
• integration_message_log.target_system (NABIDH, Malaffi)
• integration_message_log.sent_datetime
• integration_message_log.ack_status (AA, AE, AR)

Dimensions / Filters

• Time: hour, day, month
• Target system (NABIDH vs Malaffi)
• Facility
• Event type (A01/A02/A03/A04)

Visualization

• Line chart: success rate by day per HIE
• Bar chart: count of failures by error code
• Table: failed messages with error details (for integration team)

Alert Thresholds

• Warning: success rate < 99.5% in any 24-hour period
• Critical: success rate < 98% in any 24-hour period or >10 consecutive failures
• Notifications:
• Integration / IT Operations
• HIS Administrator
• Compliance Officer (daily summary, no PHI in email to comply with UAE PDPL)

Standard Reports

Dashboard Wireframe

Below is an HTML wireframe mockup for the Scheduling Analytics Dashboard (SCR-SCH-010), focusing on the KPIs defined above.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Scheduling Analytics Dashboard</title>
</head>
<body style="font-family: Arial, sans-serif; background-color:#f5f5f5; margin:0; padding:0;">
  <div style="padding:16px; background-color:#004b8d; color:#fff;">
    <h1 style="margin:0; font-size:20px;">Scheduling & Bed/OR Management Dashboard</h1>
    <div style="margin-top:8px;">
      <label>Date Range:
        <select style="margin-left:4px;">
          <option>Last 7 days</option>
          <option>Last 30 days</option>
          <option>This month</option>
          <option>Custom...</option>
        </select>
      </label>
      <label style="margin-left:16px;">Facility:
        <select><option>All</option></select>
      </label>
      <label style="margin-left:16px;">Department:
        <select><option>All</option></select>
      </label>
    </div>
  </div>

  <div style="padding:16px;">
    <!-- KPI Cards Row 1 -->
    <div style="display:flex; gap:16px; margin-bottom:16px;">
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">Appointment No-Show Rate</h2>
        <div style="font-size:24px; font-weight:bold; color:#c0392b;">8.5%</div>
        <div style="font-size:11px; color:#666;">Target ≤ 10%</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">Average Wait Time (Clinic)</h2>
        <div style="font-size:24px; font-weight:bold; color:#27ae60;">17 min</div>
        <div style="font-size:11px; color:#666;">Target ≤ 20 min</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">Bed Occupancy Rate</h2>
        <div style="font-size:24px; font-weight:bold; color:#f39c12;">88%</div>
        <div style="font-size:11px; color:#666;">Target 80–90%</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">ALOS (Inpatient)</h2>
        <div style="font-size:24px; font-weight:bold;">4.3 days</div>
        <div style="font-size:11px; color:#666;">Compare vs DRG benchmarks</div>
      </div>
    </div>

    <!-- KPI Cards Row 2 -->
    <div style="display:flex; gap:16px; margin-bottom:16px;">
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">Bed Turnaround Time</h2>
        <div style="font-size:24px; font-weight:bold; color:#e67e22;">55 min</div>
        <div style="font-size:11px; color:#666;">Target ≤ 60 min</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">OR Utilization Rate</h2>
        <div style="font-size:24px; font-weight:bold; color:#27ae60;">79%</div>
        <div style="font-size:11px; color:#666;">Target 75–85%</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">First-Case On-Time Start</h2>
        <div style="font-size:24px; font-weight:bold; color:#c0392b;">82%</div>
        <div style="font-size:11px; color:#666;">Target ≥ 90%</div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
        <h2 style="font-size:14px; margin:0 0 4px;">ADT Submission Rate (HIE)</h2>
        <div style="font-size:24px; font-weight:bold; color:#27ae60;">99.7%</div>
        <div style="font-size:11px; color:#666;">Target ≥ 99.5%</div>
      </div>
    </div>

    <!-- Charts Row -->
    <div style="display:flex; gap:16px; margin-bottom:16px;">
      <div style="flex:2; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd; height:260px;">
        <h2 style="font-size:14px; margin:0 0 8px;">Appointment No-Show & Cancellation Trends</h2>
        <div style="border:1px dashed #ccc; height:210px; text-align:center; padding-top:90px; color:#999; font-size:12px;">
          Line chart: monthly no-show and cancellation rates by department
        </div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd; height:260px;">
        <h2 style="font-size:14px; margin:0 0 8px;">Bed Occupancy by Ward</h2>
        <div style="border:1px dashed #ccc; height:210px; text-align:center; padding-top:90px; color:#999; font-size:12px;">
          Bar chart: current occupancy vs target by ward
        </div>
      </div>
    </div>

    <!-- Charts Row 2 -->
    <div style="display:flex; gap:16px; margin-bottom:16px;">
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd; height:260px;">
        <h2 style="font-size:14px; margin:0 0 8px;">OR Utilization & First-Case Starts</h2>
        <div style="border:1px dashed #ccc; height:210px; text-align:center; padding-top:90px; color:#999; font-size:12px;">
          Combined bar/line chart by OR room
        </div>
      </div>
      <div style="flex:1; background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd; height:260px;">
        <h2 style="font-size:14px; margin:0 0 8px;">ALOS vs DRG Benchmarks</h2>
        <div style="border:1px dashed #ccc; height:210px; text-align:center; padding-top:90px; color:#999; font-size:12px;">
          Bar chart: ALOS vs target by DRG group
        </div>
      </div>
    </div>

    <!-- Detail Table -->
    <div style="background:#fff; padding:12px; border-radius:4px; border:1px solid #ddd;">
      <h2 style="font-size:14px; margin:0 0 8px;">Clinic Performance Detail</h2>
      <div style="overflow-x:auto;">
        <table style="width:100%; border-collapse:collapse; font-size:12px;">
          <thead>
            <tr style="background:#eee;">
              <th style="border:1px solid #ccc; padding:4px;">Clinic</th>
              <th style="border:1px solid #ccc; padding:4px;">Provider</th>
              <th style="border:1px solid #ccc; padding:4px;">No-Show %</th>
              <th style="border:1px solid #ccc; padding:4px;">Cancel %</th>
              <th style="border:1px solid #ccc; padding:4px;">Avg Wait (min)</th>
              <th style="border:1px solid #ccc; padding:4px;">Lead Time (days)</th>
              <th style="border:1px solid #ccc; padding:4px;">Appointments</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td style="border:1px solid #ccc; padding:4px;">Internal Medicine</td>
              <td style="border:1px solid #ccc; padding:4px;">Dr. Ahmed Al-Maktoum</td>
              <td style="border:1px solid #ccc; padding:4px; color:#27ae60;">7.2%</td>
              <td style="border:1px solid #ccc; padding:4px;">11.0%</td>
              <td style="border:1px solid #ccc; padding:4px;">18</td>
              <td style="border:1px solid #ccc; padding:4px;">5.4</td>
              <td style="border:1px solid #ccc; padding:4px;">220</td>
            </tr>
            <tr>
              <td style="border:1px solid #ccc; padding:4px;">Orthopedics</td>
              <td style="border:1px solid #ccc; padding:4px;">Dr. Fatima Al-Nahyan</td>
              <td style="border:1px solid #ccc; padding:4px; color:#c0392b;">12.5%</td>
              <td style="border:1px solid #ccc; padding:4px;">16.3%</td>
              <td style="border:1px solid #ccc; padding:4px;">24</td>
              <td style="border:1px solid #ccc; padding:4px;">9.1</td>
              <td style="border:1px solid #ccc; padding:4px;">185</td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</body>
</html>

Regulatory Reports

All regulatory references are UAE-specific and must comply with UAE PDPL (Federal Decree-Law No. 45/2021), DOH, DHA, MOH, and TDRA/NESA cybersecurity expectations.

1. MOH / Federal-Level Reporting

Although MOH does not mandate a single national HIS format, typical required indicators that use this module’s data include:

• Inpatient Activity Summary
• Admissions, discharges, transfers by facility and specialty
• ALOS by specialty
• Bed occupancy rates
• Surgical Activity Summary
• Number of surgeries by type (CPT group), elective vs emergency
• OR utilization summary

System Requirements

• Provide exportable aggregated reports (no direct patient identifiers) in CSV/PDF for MOH submissions.
• Support de-identification/anonymization when exporting patient-level data for federal statistical requests.
• Ensure all exports are logged (report_audit_log) with:
• Report name, parameters, user, timestamp
• Whether PHI was included (for PDPL audit)

2. DOH (Abu Dhabi) / Malaffi

• Malaffi ADT Feed Compliance
• ADT A01/A02/A03/A04 messages must be sent in real-time for Abu Dhabi facilities.
• KPI-SCHED-012 directly supports monitoring compliance.
• DOH Statistical Submissions
• Bed occupancy, ALOS, admissions/discharges by specialty.
• ED boarding times (if ED module integrated with encounters).

System Requirements

• Ability to filter reports by emirate and facility to isolate DOH-regulated entities.
• Provide DOH-specific templates (e.g., Excel layouts) for inpatient activity and surgical statistics.
• Maintain an integration log for Malaffi with:
• Message ID, event type, facility, ack status, error code, retry count.

3. DHA (Dubai) / NABIDH

• NABIDH ADT Feed Compliance
• Real-time ADT messages for Dubai facilities.
• KPI-SCHED-012 used to monitor success rates and error patterns.
• DHA Statistical Reporting
• Outpatient volumes, no-show rates, and waiting times by specialty.
• Bed occupancy and LOS for Dubai hospitals.

System Requirements

• Emirate-based filtering to isolate DHA facilities.
• Pre-built “DHA Outpatient Access & No-Show” report:
• Appointment volumes, no-show and cancellation rates by specialty.
• Average lead time and wait time.

4. UAE PDPL Compliance & Audit Reporting

To comply with UAE PDPL:

• Access & Export Audit
• Every scheduling/bed/OR report run must be logged:
  • User, role, timestamp
  • Filters (date range, facility, department)
  • Whether patient identifiers were included
  • Export type (CSV, Excel, PDF, API)
• Minimum Necessary Principle
• Default regulatory reports should be aggregated; patient-level detail requires elevated permissions.
• Data Subject Rights Support
• Ability to retrieve all encounters, appointments, and bed/OR events for a given patient (Emirates ID or MRN) for PDPL access requests.
• Ability to show when and by whom the patient’s scheduling data was accessed or exported.

PDPL Audit Report Examples

• RPT-SCHED-PDPL-001: Scheduling Data Access Audit
• Lists all users who accessed appointment/encounter scheduling data for a given patient or date range.
• RPT-SCHED-PDPL-002: Report & Export Audit
• Lists all scheduling-related reports run, with export details.

5. Cybersecurity & TDRA/NESA

• All regulatory and HIE reports must be accessible only over secure channels (HTTPS, VPN).
• Role-based access control enforced via users, roles, permissions.
• Integration failure reports (for NABIDH/Malaffi) must not expose PHI in email or external notifications; detailed PHI only visible inside the secure HIS UI.

Ad-Hoc Reporting

Available Data Fields (Key Domains)

The ad-hoc reporting layer should expose a curated semantic model over the underlying tables. Key field groups:

1. Appointments - appointments.appointment_id - appointments.patient_id (masked or pseudonymized option) - appointments.encounter_id - appointments.provider_id - appointments.department_id, facility_id - appointments.appointment_type_id - appointments.appointment_datetime - appointments.duration_minutes - appointments.status - appointments.check_in_time - appointments.reason_for_visit - appointments.cancellation_reason - appointments.created_at, created_by

2. Encounters - encounters.encounter_id - encounters.patient_id - encounters.encounter_type, encounter_class - encounters.encounter_status - encounters.admission_datetime, discharge_datetime - encounters.facility_id, department_id - encounters.attending_provider_id, admitting_provider_id - encounters.admission_source, discharge_disposition - encounters.payer_id, insurance_plan_id - encounters.expected_los, actual_los

3. Bed Management - beds.bed_id, facility_id, department_id, ward_type - beds.bed_number, bed_status, is_isolation, gender_restriction - bed_assignments.assignment_id, bed_id, patient_id, encounter_id - bed_assignments.assigned_datetime, released_datetime, assignment_reason - bed_transfers.transfer_id, from_bed_id, to_bed_id, transfer_reason, transfer_datetime

4. OR Scheduling - or_rooms.room_id, facility_id, room_name, room_type - or_schedules.schedule_id, or_room_id, schedule_date, block_start, block_end, block_type - or_cases.case_id, schedule_id, patient_id, encounter_id - or_cases.surgeon_id, anesthesiologist_id - or_cases.procedure_code_cpt, procedure_description - or_cases.estimated_duration, actual_start, actual_end, case_status - or_cases.pre_op_checklist_complete, consent_verified

5. Waitlist - waitlist_entries.entry_id, patient_id - waitlist_entries.waitlist_type, requested_service - waitlist_entries.preferred_provider_id - waitlist_entries.priority - waitlist_entries.added_datetime - waitlist_entries.target_date_from, target_date_to - waitlist_entries.status - waitlist_entries.offered_count - waitlist_entries.resolved_datetime, resolution_type

6. Integration & HIE - integration_message_log.message_id - integration_message_log.message_type, event_type - integration_message_log.target_system - integration_message_log.sent_datetime - integration_message_log.ack_status, error_code, retry_count

All ad-hoc access must respect PDPL and role-based permissions; some fields (e.g., patient identifiers) may be hidden or masked depending on user role.

Export Formats

The reporting engine should support:

• CSV: For data analysis in external tools; UTF-8 encoding; no PHI in unsecured channels.
• Excel (XLSX): For operational and management reports; includes basic formatting and filters.
• PDF: For official reporting, sign-off, and archival.
• JSON / API (optional): For integration with external analytics platforms, with appropriate authentication and authorization.

Each export action must be logged in a report_audit_log table with:

• report_audit_id
• report_name
• parameters_json
• export_format
• requested_by_user_id
• requested_at
• contains_phi (boolean)
• row_count

Scheduled Report Delivery

The system should allow scheduling of recurring reports with the following capabilities:

• Scheduling Options
• Frequency: daily, weekly, monthly, quarterly, yearly
• Time of day (with facility time zone)

• Start and end dates

• Delivery Channels

• Secure email with link to download (no PHI in email body; require login to view)
• Internal notification within HIS (inbox/notification center)

• Secure SFTP drop for integration with corporate BI tools

• Access Control

• Only users with appropriate permissions can schedule or receive specific reports.

• Distribution lists must be role-based where possible (e.g., “OR Managers”, “Bed Management Team”).

• Compliance

• All scheduled runs logged in report_audit_log.
• Ability to revoke or modify schedules centrally (e.g., by HIS admin).
• For reports containing PHI, enforce:
  • Encrypted storage at rest
  • Time-limited download links
  • No automatic attachment of raw PHI data to emails.

This specification provides developer-ready definitions for KPIs, standard and regulatory reports, dashboard layout, and ad-hoc reporting capabilities for the Scheduling & Bed/OR Management module in the UAE regulatory context.