Billing & Claims Management KPIs & Reporting

KPI Summary

Note: All KPIs must respect UAE PDPL (Federal Decree-Law No. 45/2021) — dashboards should use de‑identified or role-appropriate views where possible.

KPI Definitions

KPI-BIL-001: Clean Claim Rate

Description

Percentage of claims that pass all internal and payer-specific edits and are accepted on first submission (no rejection at eClaimLink/DOH gateway and no payer rejection on first adjudication). High clean claim rate reduces rework and accelerates cash flow.

Calculation Formula (SQL)

Assumptions:

• A “first submission” is the earliest claim_submissions.submission_datetime per claim_id.
• A “clean claim” is:
• First submission has gateway_status = 'accepted' and rejection_reason IS NULL, and
• The first corresponding claim_responses record is not a hard denial (no denial codes, or final status not denied).

-- Date filter placeholder: BETWEEN :start_date AND :end_date on submission_datetime

WITH first_submissions AS (
    SELECT
        cs.claim_id,
        MIN(cs.submission_datetime) AS first_submission_datetime
    FROM claim_submissions cs
    GROUP BY cs.claim_id
),
first_submission_records AS (
    SELECT
        cs.claim_id,
        cs.submission_id,
        cs.gateway_status,
        cs.rejection_reason,
        cs.submission_datetime
    FROM claim_submissions cs
    JOIN first_submissions fs
      ON cs.claim_id = fs.claim_id
     AND cs.submission_datetime = fs.first_submission_datetime
),
first_responses AS (
    SELECT
        cr.claim_id,
        MIN(cr.response_datetime) AS first_response_datetime
    FROM claim_responses cr
    GROUP BY cr.claim_id
),
first_response_records AS (
    SELECT
        cr.claim_id,
        cr.response_id,
        cr.denial_codes,
        cr.response_type,
        cr.response_datetime
    FROM claim_responses cr
    JOIN first_responses fr
      ON cr.claim_id = fr.claim_id
     AND cr.response_datetime = fr.first_response_datetime
)
SELECT
    COUNT(
        CASE
            WHEN fsr.gateway_status = 'accepted'
             AND fsr.rejection_reason IS NULL
             AND (frr.response_id IS NULL
                  OR (frr.denial_codes IS NULL OR frr.denial_codes = '')
                  OR frr.response_type NOT IN ('denied', 'rejected'))
            THEN 1
        END
    ) * 100.0
    / NULLIF(COUNT(*), 0) AS clean_claim_rate_pct
FROM first_submission_records fsr
LEFT JOIN first_response_records frr
  ON fsr.claim_id = frr.claim_id
WHERE fsr.submission_datetime BETWEEN :start_date AND :end_date;

Target / Benchmark

Data Sources

• claim_submissions
• claim_id, submission_id, submission_datetime, gateway_status, rejection_reason, submission_channel
• claim_responses
• claim_id, response_id, response_datetime, response_type, denial_codes
• Optional filter fields from claims: payer_id, plan_id, facility_id, claim_type

Dimensions / Filters

• Time: day, week, month, quarter, year
• Facility (claims.facility_id)
• Department (encounters.department_id)
• Payer / Plan (claims.payer_id, claims.plan_id)
• Claim type (inpatient, outpatient, ER)
• Submission channel (claim_submissions.submission_channel – eClaimLink, DOH eClaims, others)

Visualization

• Primary: Gauge (current month clean claim rate vs 95% target)
• Secondary: Line chart (monthly trend, last 12 months)
• Drill-down: Bar chart by payer and submission channel

Alert Thresholds

• Warning: Clean claim rate < 93% for current rolling 30 days
• Notify: Revenue Cycle Manager, Billing Supervisor
• Critical: Clean claim rate < 90% for 7 consecutive days
• Notify: CFO/Finance Director, IT Integration Lead (for potential interface issues)
• Optional: Payer-specific alerts when any payer’s clean claim rate < 85%

KPI-BIL-002: Days in AR (Total)

Description

Average number of days that accounts receivable remain outstanding, across all payers and patient balances. Indicates overall efficiency of the revenue cycle and collection processes.

Calculation Formula (SQL)

Using standard AR days formula over a 90-day lookback:

-- Assume :as_of_date is the reporting date (e.g., month-end)
-- Charges in last 90 days
WITH charges_90 AS (
    SELECT
        SUM(c.charge_amount) AS total_charges_90
    FROM charges c
    WHERE c.service_date BETWEEN (:as_of_date::date - INTERVAL '90 day') AND :as_of_date
),
current_ar AS (
    SELECT
        SUM(a.total_outstanding) AS total_outstanding_ar
    FROM ar_aging a
    WHERE a.snapshot_date = :as_of_date::date
)
SELECT
    CASE
        WHEN c90.total_charges_90 IS NULL OR c90.total_charges_90 = 0 THEN NULL
        ELSE ca.total_outstanding_ar
             / (c90.total_charges_90 / 90.0)
    END AS days_in_ar
FROM charges_90 c90
CROSS JOIN current_ar ca;

Target / Benchmark

Data Sources

• ar_aging
• snapshot_date, total_outstanding, payer_id, patient_id, claim_id
• charges
• service_date, charge_amount, facility_id, department_id

Dimensions / Filters

• Time: snapshot date (typically month-end)
• Facility
• Department / cost center (via charge_details.cost_center if joined)
• Payer / financial class (via claims.payer_id, financial_class if present)
• AR bucket (0–30, 31–60, etc.) for drill-down

Visualization

• Primary: Gauge (overall days in AR vs target)
• Secondary: Stacked bar chart of AR by aging bucket and payer
• Trend: Line chart of days in AR by month

Alert Thresholds

• Warning: Days in AR > 45 for 2 consecutive months
• Notify: Revenue Cycle Manager, AR Supervisor
• Critical: Days in AR > 55 for current month
• Notify: CFO, CEO (if configured), Board reporting flag

KPI-BIL-003: Net Collection Rate

Description

Measures how effectively the organization collects against the amounts contractually allowed by payers (after contractual adjustments). Focuses on payer + patient collections vs allowed amounts.

Calculation Formula (SQL)

Assumptions:

• Allowed amount per claim line is stored in claim_lines.allowed_amount.
• Payer payments and adjustments are in payment_allocations.
• Patient payments are in patient_payments.

-- Date filter on payment_date / response_datetime (configurable)
WITH allowed AS (
    SELECT
        cl.claim_id,
        SUM(cl.allowed_amount) AS total_allowed
    FROM claim_lines cl
    JOIN claims c ON c.claim_id = cl.claim_id
    WHERE c.submission_date BETWEEN :start_date AND :end_date
    GROUP BY cl.claim_id
),
payer_payments AS (
    SELECT
        pa.claim_id,
        SUM(pa.allocated_amount) AS total_payer_payments
    FROM payment_allocations pa
    JOIN payments p ON p.payment_id = pa.payment_id
    WHERE p.payment_date BETWEEN :start_date AND :end_date
    GROUP BY pa.claim_id
),
patient_payments AS (
    SELECT
        pp.encounter_id,
        SUM(pp.payment_amount) AS total_patient_payments
    FROM patient_payments pp
    WHERE pp.payment_datetime BETWEEN :start_date AND :end_date
    GROUP BY pp.encounter_id
),
claim_encounters AS (
    SELECT DISTINCT
        c.claim_id,
        c.encounter_id
    FROM claims c
)
SELECT
    (SUM(COALESCE(ppay.total_payer_payments, 0))
     + SUM(COALESCE(ptpay.total_patient_payments, 0))) * 100.0
    / NULLIF(SUM(COALESCE(a.total_allowed, 0)), 0) AS net_collection_rate_pct
FROM allowed a
LEFT JOIN payer_payments ppay
  ON a.claim_id = ppay.claim_id
LEFT JOIN claim_encounters ce
  ON a.claim_id = ce.claim_id
LEFT JOIN patient_payments ptpay
  ON ce.encounter_id = ptpay.encounter_id;

Target / Benchmark

Data Sources

• claim_lines.allowed_amount
• claims.claim_id, claims.encounter_id, claims.payer_id, claims.facility_id
• payments.payment_id, payments.payment_date, payments.payer_id
• payment_allocations.payment_id, payment_allocations.claim_id, payment_allocations.allocated_amount
• patient_payments.payment_amount, patient_payments.payment_datetime, patient_payments.encounter_id

Dimensions / Filters

• Time: month, quarter, year
• Facility
• Department (via encounter)
• Payer / plan / financial class
• Encounter type (inpatient, outpatient, ER)

Visualization

• Primary: Gauge (current net collection rate vs 95% target)
• Secondary: Bar chart by payer
• Trend: Line chart (12-month rolling)

Alert Thresholds

• Warning: Net collection rate < 94% for current quarter
• Notify: Revenue Cycle Manager, Finance Controller
• Critical: Net collection rate < 92% for any month
• Notify: CFO, CEO (optional)

KPI-BIL-004: Denial Rate

Description

Percentage of submitted claims that are denied (fully or partially) by payers on first adjudication. High denial rate indicates issues with eligibility, authorization, coding, or documentation.

Calculation Formula (SQL)

Assumptions:

• A claim is “denied” if its first response has response_type IN ('denied', 'rejected') or denial_codes populated.

WITH first_responses AS (
    SELECT
        cr.claim_id,
        MIN(cr.response_datetime) AS first_response_datetime
    FROM claim_responses cr
    GROUP BY cr.claim_id
),
first_response_records AS (
    SELECT
        cr.claim_id,
        cr.response_type,
        cr.denial_codes,
        cr.response_datetime
    FROM claim_responses cr
    JOIN first_responses fr
      ON cr.claim_id = fr.claim_id
     AND cr.response_datetime = fr.first_response_datetime
),
submitted_claims AS (
    SELECT DISTINCT
        cs.claim_id
    FROM claim_submissions cs
    WHERE cs.submission_datetime BETWEEN :start_date AND :end_date
)
SELECT
    COUNT(
        CASE
            WHEN (frr.response_type IN ('denied', 'rejected'))
              OR (frr.denial_codes IS NOT NULL AND frr.denial_codes <> '')
            THEN 1
        END
    ) * 100.0
    / NULLIF(COUNT(*), 0) AS denial_rate_pct
FROM submitted_claims sc
LEFT JOIN first_response_records frr
  ON sc.claim_id = frr.claim_id;

Target / Benchmark

Data Sources

• claim_submissions.submission_datetime, claim_id
• claim_responses.response_type, denial_codes, response_datetime
• claims.payer_id, claims.facility_id, claims.claim_type

Dimensions / Filters

• Time: month, quarter
• Facility, department
• Payer / plan
• Denial category (eligibility, authorization, coding, etc. – from denial_codes mapping)
• Claim type

Visualization

• Primary: Line chart (denial rate trend)
• Secondary: Pareto bar chart of denial reasons
• Drill-down: Table of top denied claims by amount

Alert Thresholds

• Warning: Denial rate > 6% in current month
• Notify: Denial Specialist Lead, Billing Supervisor
• Critical: Denial rate > 8% or any payer > 10%
• Notify: Revenue Cycle Manager, Medical Director (for clinical denials)

KPI-BIL-005: Charge Capture Lag

Description

Average time between service date/time and creation of the corresponding charge record. Measures timeliness of charge capture and integration with clinical systems.

Calculation Formula (SQL)

Assumptions:

• charges.service_date (or service_datetime if present) and charges.created_at exist.

SELECT
    AVG(EXTRACT(EPOCH FROM (c.created_at - c.service_date)) / 3600.0) AS charge_capture_lag_hours
FROM charges c
WHERE c.service_date BETWEEN :start_date AND :end_date;

Target / Benchmark

Data Sources

• charges.service_date, charges.created_at
• charges.facility_id, charges.department_id, charges.source_module

Dimensions / Filters

• Time: day, week, month
• Facility, department
• Source module (CPOE, RIS, LIS, PIS, manual)
• Provider

Visualization

• Primary: Line chart (average lag hours by day)
• Secondary: Box plot by department
• Drill-down: Table of charges with lag > 48 hours

Alert Thresholds

• Warning: Average lag > 24 hours for 3 consecutive days
• Notify: Charge Entry Supervisor, Department Managers
• Critical: Average lag > 36 hours or > 10% of charges > 72 hours
• Notify: Revenue Cycle Manager, COO

KPI-BIL-006: Claim Submission Lag

Description

Average time from encounter discharge/closure to first claim submission. Indicates how quickly claims are leaving the hospital and entering payer workflows.

Calculation Formula (SQL)

WITH first_submissions AS (
    SELECT
        cs.claim_id,
        MIN(cs.submission_datetime) AS first_submission_datetime
    FROM claim_submissions cs
    GROUP BY cs.claim_id
)
SELECT
    AVG(
        EXTRACT(EPOCH FROM (fs.first_submission_datetime - e.discharge_datetime)) / 86400.0
    ) AS claim_submission_lag_days
FROM claims c
JOIN first_submissions fs
  ON c.claim_id = fs.claim_id
JOIN encounters e
  ON c.encounter_id = e.encounter_id
WHERE e.discharge_datetime BETWEEN :start_date AND :end_date;

Target / Benchmark

Data Sources

• encounters.encounter_id, discharge_datetime, facility_id, department_id
• claims.claim_id, claims.encounter_id, claims.payer_id
• claim_submissions.submission_datetime

Dimensions / Filters

• Time: discharge date
• Facility, department
• Payer / plan
• Encounter type

Visualization

• Primary: Line chart (average days from discharge to submission)
• Secondary: Bar chart by department
• Drill-down: List of encounters with lag > 7 days

Alert Thresholds

• Warning: Average lag > 3 days for current week
• Notify: Billing Supervisor
• Critical: Average lag > 5 days or > 10% of encounters > 10 days
• Notify: Revenue Cycle Manager, CFO

KPI-BIL-007: Patient Collection Rate

Description

Percentage of patient responsibility (co-pays, deductibles, co-insurance, self-pay) that is actually collected from patients. Reflects effectiveness of patient billing and POS collection processes.

Calculation Formula (SQL)

Assumptions:

• Patient responsibility per claim is in claim_responses.patient_responsibility.
• Patient invoices may aggregate multiple claims; for this KPI we focus on claim-level responsibility.

WITH patient_resp AS (
    SELECT
        cr.claim_id,
        SUM(cr.patient_responsibility) AS total_patient_resp
    FROM claim_responses cr
    JOIN claims c ON c.claim_id = cr.claim_id
    WHERE cr.response_datetime BETWEEN :start_date AND :end_date
    GROUP BY cr.claim_id
),
patient_payments AS (
    SELECT
        ce.claim_id,
        SUM(pp.payment_amount) AS total_patient_payments
    FROM patient_payments pp
    JOIN encounters e ON e.encounter_id = pp.encounter_id
    JOIN claims ce ON ce.encounter_id = e.encounter_id
    WHERE pp.payment_datetime BETWEEN :start_date AND :end_date
    GROUP BY ce.claim_id
)
SELECT
    SUM(COALESCE(ppay.total_patient_payments, 0)) * 100.0
    / NULLIF(SUM(pr.total_patient_resp), 0) AS patient_collection_rate_pct
FROM patient_resp pr
LEFT JOIN patient_payments ppay
  ON pr.claim_id = ppay.claim_id;

Target / Benchmark

Data Sources

• claim_responses.patient_responsibility, claim_responses.response_datetime
• claims.claim_id, claims.encounter_id, claims.payer_id
• patient_payments.payment_amount, payment_datetime, encounter_id, payment_location

Dimensions / Filters

• Time: month, quarter
• Facility
• Payment location (POS, online, bank transfer)
• Financial class (insured, self-pay, VIP, etc.)
• Payer (for co-insurance patterns)

Visualization

• Primary: Gauge (current rate vs 70% target)
• Secondary: Bar chart by payment location
• Trend: Line chart by month

Alert Thresholds

• Warning: Patient collection rate < 65% for current quarter
• Notify: Patient Billing Supervisor, Cashier Lead
• Critical: Patient collection rate < 60%
• Notify: Revenue Cycle Manager, CFO

KPI-BIL-008: Appeal Success Rate

Description

Percentage of appeals that result in overturning the original denial (full or partial payment). Indicates effectiveness of denial management and appeal strategies.

Note: This assumes appeal tracking fields exist in claim_responses or a related table (e.g., appeal_status, appeal_outcome). If a separate claim_appeals table is implemented, adapt accordingly.

Calculation Formula (SQL)

Assuming:

• claim_responses.response_type = 'appeal' for appeal responses.
• claim_responses.appeal_outcome with values like overturned, upheld, partial.

SELECT
    COUNT(
        CASE
            WHEN cr.response_type = 'appeal'
             AND cr.appeal_outcome = 'overturned'
            THEN 1
        END
    ) * 100.0
    / NULLIF(
        COUNT(
            CASE
                WHEN cr.response_type = 'appeal'
                THEN 1
            END
        ),
        0
    ) AS appeal_success_rate_pct
FROM claim_responses cr
WHERE cr.response_type = 'appeal'
  AND cr.response_datetime BETWEEN :start_date AND :end_date;

Target / Benchmark

Data Sources

• claim_responses.response_type, appeal_outcome, response_datetime, denial_codes, claim_id
• claims.payer_id, claims.facility_id, claims.total_charge_amount

Dimensions / Filters

• Time: month, quarter
• Facility
• Payer
• Denial category
• Appeal type (clinical vs administrative)

Visualization

• Primary: Gauge (appeal success rate vs 50% target)
• Secondary: Bar chart by payer
• Drill-down: Table of high-value overturned appeals

Alert Thresholds

• Warning: Appeal success rate < 40% for current quarter
• Notify: Denial Management Lead
• Critical: Appeal success rate < 30% or any payer < 20%
• Notify: Revenue Cycle Manager, Medical Director (for clinical denials)

KPI-BIL-009: Point-of-Service Collection Rate

Description

Percentage of expected co-pays and other patient responsibility amounts that are collected at point-of-service (POS), such as registration or discharge. High POS collection reduces downstream billing effort and bad debt.

Calculation Formula (SQL)

Assumptions:

• POS payments are patient_payments where payment_location = 'pos'.
• Expected co-pay/deductible is derived from claim_responses.patient_responsibility for encounters where payment could have been collected at POS (typically outpatient).

WITH patient_resp AS (
    SELECT
        c.encounter_id,
        SUM(cr.patient_responsibility) AS total_patient_resp
    FROM claim_responses cr
    JOIN claims c ON c.claim_id = cr.claim_id
    WHERE cr.response_datetime BETWEEN :start_date AND :end_date
    GROUP BY c.encounter_id
),
pos_payments AS (
    SELECT
        pp.encounter_id,
        SUM(pp.payment_amount) AS total_pos_payments
    FROM patient_payments pp
    WHERE pp.payment_location = 'pos'
      AND pp.payment_datetime BETWEEN :start_date AND :end_date
    GROUP BY pp.encounter_id
)
SELECT
    SUM(COALESCE(ppay.total_pos_payments, 0)) * 100.0
    / NULLIF(SUM(pr.total_patient_resp), 0) AS pos_collection_rate_pct
FROM patient_resp pr
LEFT JOIN pos_payments ppay
  ON pr.encounter_id = ppay.encounter_id;

Target / Benchmark

Data Sources

• patient_payments.payment_amount, payment_location, payment_datetime, encounter_id
• claim_responses.patient_responsibility, response_datetime
• encounters.encounter_type, facility_id, department_id

Dimensions / Filters

• Time: month, quarter
• Facility, department
• Encounter type (outpatient, ER)
• Cashier / collector (patient_payments.collected_by)
• Payer / financial class

Visualization

• Primary: Gauge (POS collection rate vs 80% target)
• Secondary: Bar chart by cashier / location
• Trend: Line chart by month

Alert Thresholds

• Warning: POS collection rate < 75% for current month
• Notify: Cashier Supervisor, Patient Access Manager
• Critical: POS collection rate < 65%
• Notify: Revenue Cycle Manager, CFO

KPI-BIL-010: Credit Balance Amount

Description

Total value of patient accounts with negative balances (credit balances). High credit balances may indicate overpayments, posting errors, or delayed refunds, and can be a compliance risk.

Calculation Formula (SQL)

Assumptions:

• Patient account balance is maintained in patient_invoices.balance (per invoice).
• Credit balances are balance < 0.

SELECT
    SUM(pi.balance) AS total_credit_balance_amount
FROM patient_invoices pi
WHERE pi.balance < 0
  AND pi.invoice_date <= :as_of_date;

To express as % of total collections over a period:

WITH credit_balances AS (
    SELECT SUM(pi.balance) AS total_credit_balance
    FROM patient_invoices pi
    WHERE pi.balance < 0
      AND pi.invoice_date <= :as_of_date
),
collections AS (
    SELECT
        SUM(pp.payment_amount) AS total_patient_collections
    FROM patient_payments pp
    WHERE pp.payment_datetime BETWEEN :start_date AND :end_date
)
SELECT
    cb.total_credit_balance * -100.0
    / NULLIF(c.total_patient_collections, 0) AS credit_balance_pct_of_collections
FROM credit_balances cb
CROSS JOIN collections c;

Target / Benchmark

Data Sources

• patient_invoices.balance, invoice_date, patient_id, status
• patient_payments.payment_amount, payment_datetime
• refunds.refund_amount, refund_datetime, status

Dimensions / Filters

• Time: as-of date
• Facility
• Patient type (inpatient/outpatient)
• Invoice status (open, closed)
• Refund status (for reconciliation)

Visualization

• Primary: Gauge (credit balance % of collections vs 1% target)
• Secondary: Table of top 50 credit balance accounts
• Trend: Line chart of total credit balance over time

Alert Thresholds

• Warning: Credit balance > 1% of collections
• Notify: Finance Controller, Internal Audit
• Critical: Credit balance > 2% or any single account > defined threshold (e.g., AED 50,000)
• Notify: CFO, Compliance Officer

KPI-BIL-011: Average Reimbursement per Encounter

Description

Average total reimbursement (payer + patient) per billed encounter. Useful for monitoring revenue trends, payer mix changes, and impact of coding/contract changes.

Calculation Formula (SQL)

WITH payer_payments AS (
    SELECT
        pa.claim_id,
        SUM(pa.allocated_amount) AS total_payer_payments
    FROM payment_allocations pa
    JOIN payments p ON p.payment_id = pa.payment_id
    WHERE p.payment_date BETWEEN :start_date AND :end_date
    GROUP BY pa.claim_id
),
patient_payments AS (
    SELECT
        e.encounter_id,
        SUM(pp.payment_amount) AS total_patient_payments
    FROM patient_payments pp
    JOIN encounters e ON e.encounter_id = pp.encounter_id
    WHERE pp.payment_datetime BETWEEN :start_date AND :end_date
    GROUP BY e.encounter_id
),
claim_encounters AS (
    SELECT DISTINCT
        c.claim_id,
        c.encounter_id
    FROM claims c
)
SELECT
    (SUM(COALESCE(ppay.total_payer_payments, 0))
     + SUM(COALESCE(ptpay.total_patient_payments, 0)))
    / NULLIF(COUNT(DISTINCT ce.encounter_id), 0) AS avg_reimbursement_per_encounter
FROM claim_encounters ce
LEFT JOIN payer_payments ppay
  ON ce.claim_id = ppay.claim_id
LEFT JOIN patient_payments ptpay
  ON ce.encounter_id = ptpay.encounter_id
JOIN encounters e
  ON ce.encounter_id = e.encounter_id
WHERE e.discharge_datetime BETWEEN :start_date AND :end_date;

Target / Benchmark

• No fixed numeric target; monitored by encounter type and payer mix.
• Benchmarks are facility-specific (e.g., average reimbursement for DRG-based inpatient vs outpatient visit).

Data Sources

• claims.claim_id, claims.encounter_id, claims.payer_id, claims.total_charge_amount
• payments.payment_date
• payment_allocations.allocated_amount, claim_id
• patient_payments.payment_amount, payment_datetime, encounter_id
• encounters.encounter_type, discharge_datetime, facility_id, department_id

Dimensions / Filters

• Time: month, quarter
• Facility, department
• Encounter type (inpatient, day surgery, outpatient, ER)
• Payer / plan
• DRG or case type (via charge_details.drg_code)

Visualization

• Primary: Column chart (average reimbursement per encounter by encounter type)
• Secondary: Line chart trend by month
• Drill-down: Table by payer and DRG

Alert Thresholds

• Warning: >10% drop in average reimbursement per encounter for any encounter type vs previous quarter
• Notify: Revenue Cycle Manager, Contracting Manager
• Critical: >20% drop or sudden change for a major payer
• Notify: CFO, CEO, Contracting Team

Standard Reports

Dashboard Wireframe

Below is an HTML wireframe mockup for the Billing & Claims Management executive dashboard (e.g., SCR-BIL-008 Revenue Dashboard), showing KPI cards, charts, and filters.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Billing & Claims Management Dashboard</title>
</head>
<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background:#f5f5f5;">
  <!-- Top Bar -->
  <header style="background:#004c97; color:#fff; padding:12px 20px;">
    <div style="display:flex; justify-content:space-between; align-items:center;">
      <h1 style="margin:0; font-size:20px;">Revenue Dashboard – Billing & Claims</h1>
      <div>
        <label style="margin-right:8px;">Date Range:</label>
        <select style="padding:4px 6px;">
          <option>Last 30 days</option>
          <option>Last 90 days</option>
          <option>Month to Date</option>
          <option>Year to Date</option>
          <option>Custom…</option>
        </select>
        <label style="margin:0 8px;">Facility:</label>
        <select style="padding:4px 6px;">
          <option>All Facilities</option>
          <option>Dubai General Hospital</option>
          <option>Abu Dhabi Medical Center</option>
        </select>
        <label style="margin:0 8px;">Payer:</label>
        <select style="padding:4px 6px;">
          <option>All Payers</option>
          <option>Daman</option>
          <option>THIQA</option>
          <option>Oman Insurance</option>
        </select>
      </div>
    </div>
  </header>

  <main style="padding:16px 20px;">
    <!-- KPI Cards Row 1 -->
    <section style="display:flex; gap:12px; margin-bottom:12px;">
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px; border-top:4px solid #2e7d32;">
        <div style="font-size:12px; color:#666;">Clean Claim Rate</div>
        <div style="font-size:24px; font-weight:bold;">96.4%</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≥ 95% · ▲ 1.2% vs last month</div>
      </div>
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px; border-top:4px solid #c62828;">
        <div style="font-size:12px; color:#666;">Denial Rate</div>
        <div style="font-size:24px; font-weight:bold;">6.1%</div>
        <div style="font-size:11px; color:#c62828;">Target ≤ 5% · ▼ 0.4% vs last month</div>
      </div>
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px; border-top:4px solid #0277bd;">
        <div style="font-size:12px; color:#666;">Days in AR</div>
        <div style="font-size:24px; font-weight:bold;">42.7</div>
        <div style="font-size:11px; color:#0277bd;">Target ≤ 45 · ▲ 1.0 vs last month</div>
      </div>
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px; border-top:4px solid #6a1b9a;">
        <div style="font-size:12px; color:#666;">Net Collection Rate</div>
        <div style="font-size:24px; font-weight:bold;">95.8%</div>
        <div style="font-size:11px; color:#6a1b9a;">Target ≥ 95% · Stable</div>
      </div>
    </section>

    <!-- KPI Cards Row 2 -->
    <section style="display:flex; gap:12px; margin-bottom:16px;">
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px;">
        <div style="font-size:12px; color:#666;">Patient Collection Rate</div>
        <div style="font-size:20px; font-weight:bold;">72.3%</div>
        <div style="font-size:11px; color:#2e7d32;">Target ≥ 70%</div>
      </div>
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px;">
        <div style="font-size:12px; color:#666;">POS Collection Rate</div>
        <div style="font-size:20px; font-weight:bold;">78.9%</div>
        <div style="font-size:11px; color:#c62828;">Target ≥ 80%</div>
      </div>
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px;">
        <div style="font-size:12px; color:#666;">Credit Balance Amount</div>
        <div style="font-size:20px; font-weight:bold;">AED 420,000</div>
        <div style="font-size:11px; color:#c62828;">1.4% of collections</div>
      </div>
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px;">
        <div style="font-size:12px; color:#666;">Avg Reimbursement / Encounter</div>
        <div style="font-size:20px; font-weight:bold;">AED 1,250</div>
        <div style="font-size:11px; color:#0277bd;">Monitored by encounter type</div>
      </div>
    </section>

    <!-- Charts Row -->
    <section style="display:flex; gap:16px; margin-bottom:16px;">
      <!-- Left: Trend chart -->
      <div style="flex:2; background:#fff; padding:10px; border-radius:4px; min-height:220px;">
        <div style="display:flex; justify-content:space-between; align-items:center;">
          <h2 style="margin:0; font-size:14px;">Clean Claim & Denial Trend</h2>
          <div style="font-size:11px; color:#666;">Monthly · Last 12 months</div>
        </div>
        <div style="margin-top:10px; border:1px dashed #ccc; height:180px; text-align:center; font-size:11px; color:#999; line-height:180px;">
          Line chart placeholder (Clean Claim %, Denial %)
        </div>
      </div>

      <!-- Right: Payer performance -->
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px; min-height:220px;">
        <h2 style="margin:0; font-size:14px;">Top Payers – Net Collection & AR Days</h2>
        <table style="width:100%; border-collapse:collapse; margin-top:8px; font-size:11px;">
          <thead>
            <tr>
              <th style="border-bottom:1px solid #ddd; text-align:left; padding:4px;">Payer</th>
              <th style="border-bottom:1px solid #ddd; text-align:right; padding:4px;">Net Coll%</th>
              <th style="border-bottom:1px solid #ddd; text-align:right; padding:4px;">AR Days</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td style="border-bottom:1px solid #eee; padding:4px;">Daman</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">96.5%</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">38</td>
            </tr>
            <tr>
              <td style="border-bottom:1px solid #eee; padding:4px;">THIQA</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">94.8%</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">41</td>
            </tr>
            <tr>
              <td style="border-bottom:1px solid #eee; padding:4px;">Oman Insurance</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">93.2%</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">49</td>
            </tr>
          </tbody>
        </table>
      </div>
    </section>

    <!-- Bottom Row: AR Aging & Denial Worklist -->
    <section style="display:flex; gap:16px;">
      <!-- AR Aging -->
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px; min-height:200px;">
        <h2 style="margin:0; font-size:14px;">AR Aging by Bucket</h2>
        <div style="margin-top:8px; border:1px dashed #ccc; height:160px; text-align:center; font-size:11px; color:#999; line-height:160px;">
          Stacked bar chart placeholder (0–30, 31–60, 61–90, 91–120, >120)
        </div>
      </div>

      <!-- Denial Worklist Snapshot -->
      <div style="flex:1; background:#fff; padding:10px; border-radius:4px; min-height:200px;">
        <div style="display:flex; justify-content:space-between; align-items:center;">
          <h2 style="margin:0; font-size:14px;">High-Value Denials – Worklist</h2>
          <button style="padding:4px 8px; font-size:11px;">View Full Worklist</button>
        </div>
        <table style="width:100%; border-collapse:collapse; margin-top:8px; font-size:11px;">
          <thead>
            <tr>
              <th style="border-bottom:1px solid #ddd; padding:4px; text-align:left;">Claim</th>
              <th style="border-bottom:1px solid #ddd; padding:4px; text-align:left;">Payer</th>
              <th style="border-bottom:1px solid #ddd; padding:4px; text-align:right;">Amount (AED)</th>
              <th style="border-bottom:1px solid #ddd; padding:4px; text-align:left;">Reason</th>
              <th style="border-bottom:1px solid #ddd; padding:4px; text-align:left;">Days to File</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td style="border-bottom:1px solid #eee; padding:4px;">CLM-2026-00123</td>
              <td style="border-bottom:1px solid #eee; padding:4px;">Daman</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">18,500</td>
              <td style="border-bottom:1px solid #eee; padding:4px;">Authorization missing</td>
              <td style="border-bottom:1px solid #eee; padding:4px;">5</td>
            </tr>
            <tr>
              <td style="border-bottom:1px solid #eee; padding:4px;">CLM-2026-00456</td>
              <td style="border-bottom:1px solid #eee; padding:4px;">THIQA</td>
              <td style="border-bottom:1px solid #eee; padding:4px; text-align:right;">12,200</td>
              <td style="border-bottom:1px solid #eee; padding:4px;">Medical necessity</td>
              <td style="border-bottom:1px solid #eee; padding:4px;">12</td>
            </tr>
          </tbody>
        </table>
      </div>
    </section>
  </main>
</body>
</html>

Regulatory Reports

All regulatory reporting must align with UAE regulations and emirate-level health authority requirements. The Billing & Claims module should support:

1. MOH (Federal) Reporting

• MOH Claims & Revenue Summary
• Aggregated inpatient/outpatient claims volume, total charges, total payments, and AR by emirate and facility.
• Data fields: facility, emirate, encounter type, ICD-10-AM groups, CPT categories, total charges, total paid, total outstanding.
• Source tables: claims, claim_lines, payments, payment_allocations, ar_aging.
• Output: CSV/XML for MOH portals; PDF summary for internal sign-off.
• MOH Audit Support – Overpayments & Refunds
• Detailed listing of refunds and credit balances by patient and payer.
• Source tables: patient_invoices, patient_payments, refunds, claims.
• Used during MOH financial and clinical audits.

2. DOH (Abu Dhabi) – Shafafiya / eClaims

• DOH eClaims Statistical Report
• Monthly statistics required by DOH: number of claims submitted, accepted, rejected, denial reasons, average AR days, by payer and facility.
• Source tables: claim_submissions, claim_responses, claims, ar_aging.
• Must align with DOH eClaims coding and denial reason mappings.
• DOH Payer Performance & Underpayment Report
• Comparison of paid vs allowed vs contracted amounts by payer.
• Supports DOH audits and contract negotiations.
• Source tables: claims, claim_lines, payments, payment_allocations, fee_schedule_items (from Policy & Contract Management).

3. DHA (Dubai) – eClaimLink / NABIDH

• DHA eClaimLink Submission & Rejection Report
• Daily/Monthly report of claims submitted via eClaimLink, gateway rejections, and payer rejections.
• Source tables: claim_submissions, claim_responses, claims.
• Used to monitor compliance with DHA eClaimLink technical and business rules.
• DHA NABIDH Financial Data Consistency Report
• Cross-check between clinical encounters in NABIDH and billed encounters in HIS.
• Ensures that all NABIDH-reportable encounters have corresponding billing records where applicable.
• Source tables: encounters, charges, claims; NABIDH integration logs (external).

4. UAE PDPL (Data Protection) Audit Reports

• PDPL Access & Disclosure Report (Financial Data)
• Lists users who accessed patient financial records (billing, claims, payments) over a period.
• Source tables: audit logs from users, roles, permissions (from EHR & Patient Management), plus module-specific access logs.
• Fields: user ID, role, timestamp, patient_id, action (view/edit/export), source IP.
• PDPL Data Minimization & Retention Report
• Summary of records approaching or exceeding retention periods (e.g., financial records older than X years).
• Source tables: claims, patient_invoices, patient_payments, refunds.
• Supports compliance with PDPL data retention and deletion/anonymization policies.

5. NABIDH / Malaffi Compliance Metrics (Financial Perspective)

While NABIDH (Dubai) and Malaffi (Abu Dhabi) focus on clinical data, the Billing & Claims module must support:

• Encounter-Billing Concordance Report
• Percentage of clinical encounters shared with NABIDH/Malaffi that have corresponding billing records.
• Source tables: encounters, charges, claims; HIE integration logs.
• Data Quality Checks for Coded Data
• Consistency of ICD-10-AM and CPT codes between clinical documentation and billing.
• Source tables: charge_details.drg_code, charges.icd10_codes, claim_lines.cpt_code.

All regulatory reports must:

• Support export in CSV, Excel, and where required, XML conforming to DOH/DHA schemas.
• Allow de-identification or pseudonymization where patient-level data is not strictly required, in line with UAE PDPL.

Ad-Hoc Reporting

Available Data Fields for Custom Queries

The ad-hoc reporting layer should expose a semantic data model (views) built on top of core tables. Key field groups:

• Patient & Encounter (via shared entities)
• patients.patient_id, demographic attributes (from ehr-patient-mgmt)
• encounters.encounter_id, encounter_type, admission_datetime, discharge_datetime, facility_id, department_id, attending_provider_id
• Charges & Charge Details
• charges.charge_id, patient_id, encounter_id, service_date, cpt_code, hcpcs_code, icd10_codes, modifier_codes, quantity, charge_amount, contracted_rate, provider_id, facility_id, department_id, charge_status, source_module, created_at
• charge_details.revenue_code, cost_center, drg_code, case_weight, ndc_code, units, service_description
• Claims & Claim Lines
• claims.claim_id, patient_id, encounter_id, payer_id, plan_id, contract_id, claim_type, total_charge_amount, total_paid_amount, total_adjusted_amount, patient_responsibility, claim_status, submission_date, adjudication_date, facility_id
• claim_lines.line_id, claim_id, charge_id, line_number, cpt_code, modifier_codes, icd10_pointer, quantity, charge_amount, allowed_amount, paid_amount, adjustment_amount, denial_code, line_status
• Submissions & Responses
• claim_submissions.submission_id, claim_id, submission_channel, submission_datetime, batch_id, gateway_status, gateway_response, response_datetime, rejection_reason
• claim_responses.response_id, claim_id, submission_id, response_type, response_datetime, allowed_amount, paid_amount, adjustment_codes, denial_codes, patient_responsibility, remittance_id, appeal_outcome (if implemented)
• Payments & Allocations
• payments.payment_id, payer_id, payment_date, payment_amount, payment_method, check_number, eft_reference, bank_deposit_date, reconciled, batch_id
• payment_allocations.allocation_id, payment_id, claim_id, claim_line_id, allocated_amount, adjustment_amount, adjustment_code, patient_responsibility_amount
• Patient Invoices & Payments
• patient_invoices.invoice_id, patient_id, invoice_date, total_amount, paid_amount, balance, due_date, statement_number, delivery_method, sent_datetime, status
• patient_payments.payment_id, patient_id, invoice_id, encounter_id, payment_amount, payment_method, payment_datetime, receipt_number, collected_by, payment_location
• Refunds
• refunds.refund_id, patient_id, payer_id, refund_amount, refund_reason, original_payment_id, refund_method, approved_by, refund_datetime, status
• AR Aging
• ar_aging.aging_id, snapshot_date, payer_id, patient_id, claim_id, total_outstanding, current_0_30, days_31_60, days_61_90, days_91_120, days_over_120
• Rules & Edits (for analytics)
• charge_capture_rules.rule_id, source_module, cpt_code, modifier_logic, bundling_rules, effective_date, is_active
• claim_edits.edit_id, edit_type, edit_name, severity, action, payer_specific, is_active

Ad-hoc reporting should be exposed via:

• Predefined SQL views (e.g., vw_claim_summary, vw_ar_aging_summary, vw_patient_financials) to simplify user queries.
• A metadata catalog describing each field, data type, and meaning.

Export Formats

Users with appropriate permissions should be able to export:

• CSV: For data analysis and integration with external tools.
• Excel (XLSX): For finance and management reporting.
• PDF: For signed reports, audit trails, and regulatory submissions.
• XML: For DOH/DHA eClaims-related exports where needed.

All exports must:

• Respect UAE PDPL: enforce role-based access, mask or omit identifiers where not required, and log export events (user, time, dataset, purpose).
• Support bilingual headers (English/Arabic) for patient-facing or regulatory reports where required.

Scheduled Report Delivery

The system should support:

• Scheduling
• Users can schedule reports (standard or ad-hoc) to run daily, weekly, monthly, or on custom cron-like schedules.
• Time zone awareness (Gulf Standard Time).
• Delivery Channels
• Secure email with PDF/Excel attachments (for internal recipients only).
• Secure internal portal (reports stored and accessed via HIS portal).
• SFTP drop for finance/ERP systems, where configured.
• Access Control & Audit
• Only users with appropriate roles (e.g., Revenue Cycle Manager, Finance Controller, Compliance Officer) can schedule and receive sensitive financial reports.
• All scheduled runs and deliveries logged with timestamp, recipient, and report parameters for PDPL and internal audit.
• Failure Handling
• If a scheduled report fails (e.g., query error, connectivity issue), system sends an error notification to the report owner and IT support.

This completes the KPI and reporting specification for the Billing & Claims Management (billing-claims) module.