1Overview

Carrier payment data represents the financial transactions between healthcare insurance carriers (payers) and healthcare providers. This data captures how much the insurance company paid for a claim, when they paid it, and the status of that payment.

Understanding carrier payment data is essential for:

• Revenue cycle management and accounts receivable
• Claims reconciliation and payment posting
• Denial management and appeals
• Financial reporting and forecasting
• Contract performance analysis
• Cash flow management

2Key Payment Fields

carrier_pmt_amt (carrier payment amount)

Definition: The total amount the insurance carrier has agreed to pay for the claim, including all adjustments and contractual allowances.

Data Type: DECIMAL(10, 2)
Example: 1234.56
Null Allowed: No

carrier_paid_amt (carrier paid amount)

Definition: The actual amount paid by the carrier to the provider. This may differ from carrier_pmt_amt due to adjustments, withholdings, or recoupments.

Data Type: DECIMAL(10, 2)
Example: 1180.50
Null Allowed: No

carrier_pmt_status (carrier payment status)

Definition: Current status of the payment from the insurance carrier.

Data Type: VARCHAR(20)
Example: PAID, DENIED, PENDING
Null Allowed: No

carrier_pmt_dt (carrier payment date)

Definition: The date the carrier issued the payment or remittance advice.

Data Type: DATE
Example: 2026-03-15
Null Allowed: Yes (null if pending)

carrier_pmt_id (carrier payment identifier)

Definition: Unique identifier for the payment transaction assigned by the carrier. Often corresponds to the check number or EFT transaction ID.

Data Type: VARCHAR(50)
Example: CHK-2026-03-15-001234
Null Allowed: Yes

3Database Design

PostgreSQL Schema (with Standard Abbreviations)

CREATE TABLE carrier_pmts (
  pmt_id VARCHAR(50) PRIMARY KEY,
  claim_id VARCHAR(50) NOT NULL,
  claim_line_nbr INTEGER,
  
  -- Payment amounts
  carrier_pmt_amt DECIMAL(10, 2) NOT NULL,
  carrier_paid_amt DECIMAL(10, 2) NOT NULL,
  carrier_adj_amt DECIMAL(10, 2) DEFAULT 0.00,
  
  -- Payment details
  carrier_pmt_status VARCHAR(20) NOT NULL,
  carrier_pmt_dt DATE,
  carrier_pmt_method VARCHAR(20), -- CHECK, EFT, CREDIT_CARD
  carrier_check_nbr VARCHAR(50),
  carrier_eft_trace_nbr VARCHAR(50),
  
  -- Carrier information
  carrier_id VARCHAR(20) NOT NULL,
  carrier_nm VARCHAR(255),
  payer_id VARCHAR(50),
  
  -- Denial/adjustment reasons
  adj_reason_cd VARCHAR(10),
  adj_reason_desc TEXT,
  remark_cd VARCHAR(10),
  
  -- Metadata
  remittance_dt DATE,
  posting_dt DATE,
  created_dttm TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_dttm TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Foreign keys
  FOREIGN KEY (claim_id) REFERENCES claims(claim_id),
  FOREIGN KEY (carrier_id) REFERENCES carriers(carrier_id)
);

-- Indexes for performance
CREATE INDEX idx_carrier_pmts_claim ON carrier_pmts(claim_id);
CREATE INDEX idx_carrier_pmts_status ON carrier_pmts(carrier_pmt_status);
CREATE INDEX idx_carrier_pmts_dt ON carrier_pmts(carrier_pmt_dt);
CREATE INDEX idx_carrier_pmts_carrier ON carrier_pmts(carrier_id);

💡 Design Considerations

• claim_line_nbr: Track payments at line-item level for detailed reconciliation
• carrier_adj_amt: Store contractual adjustments separately for reporting
• carrier_pmt_method: Track how payment was received (check, EFT, credit card)
• adj_reason_cd: Store CARC/RARC codes for denial management
• posting_dt: Separate from payment date for accounting purposes

4Payment Status Codes

Status Code	Description	Action Required
PAID	Claim fully paid by carrier	Post payment to A/R
PARTIAL	Claim partially paid	Review adjustments, consider appeal
DENIED	Claim denied in full	Review denial reason, file appeal if warranted
PENDING	Payment processing in progress	Monitor for status update
ADJUSTED	Payment adjusted after initial processing	Review adjustment reason, update A/R
REVERSED	Payment reversed (recoupment)	Adjust A/R, investigate reason for reversal
SUSPENDED	Payment held pending review	Respond to carrier inquiry

5carrier_pmt_amt vs carrier_paid_amt

Understanding the difference between carrier_pmt_amt and carrier_paid_amt is crucial for accurate revenue cycle management.

carrier_pmt_amt

The amount the carrier determined they owe, including all adjustments.

Billed: $1,500.00
- Contractual Adj: -$265.44
= Pmt Amt: $1,234.56

carrier_paid_amt

The actual amount sent to the provider after withholdings/recoupments.

Pmt Amt: $1,234.56
- Withholding: -$54.06
= Paid Amt: $1,180.50

Common Reasons for Differences:

• Recoupments: Recovery of previous overpayments
• Withholdings: Quality metric penalties or risk pool withholdings
• Offsets: Application of credit balances to other claims
• Provider Advances: Deduction of previously advanced amounts
• Administrative Fees: Network participation fees or claim processing fees

6EDI 835 Integration

The EDI 835 (Healthcare Claim Payment/Advice) transaction set is the electronic format carriers use to send payment information to providers. Understanding the 835 structure is essential for automated payment posting.

EDI 835 Key Segments

BPR*I*1234.56*C*ACH*CCD*01*123456789*DA*987654321*...
  └─ Payment amount: $1,234.56 → carrier_paid_amt
  
TRN*1*CHK-2026-03-15-001234*1234567890
  └─ Payment ID → carrier_pmt_id
  
CLP*CLAIM123*1*1500.00*1234.56*265.44*12*...
  └─ Claim nbr, status, billed, paid, adj
  
SVC*HC:99213*1500.00*1234.56*HC:99213*1
  └─ Service line pmt details
  
CAS*CO*45*265.44
  └─ Adj reason cd and amt → adj_reason_cd, carrier_adj_amt

Mapping 835 to Database Fields:

BPR02→ carrier_paid_amt

TRN02→ carrier_pmt_id

CLP04→ carrier_pmt_amt

CLP05→ carrier_adj_amt

CAS01-03→ adj_reason_cd, carrier_adj_amt

7SQL Query Examples

Find All Denied Claims

SELECT 
  claim_id,
  carrier_pmt_amt,
  adj_reason_cd,
  adj_reason_desc
FROM carrier_pmts
WHERE carrier_pmt_status = 'DENIED'
  AND carrier_pmt_dt >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY carrier_pmt_dt DESC;

Calculate Total Adjustments by Carrier

SELECT 
  carrier_nm,
  COUNT(*) as total_claims,
  SUM(carrier_pmt_amt) as total_allowed,
  SUM(carrier_adj_amt) as total_adj,
  ROUND(
    SUM(carrier_adj_amt) / 
    NULLIF(SUM(carrier_pmt_amt + carrier_adj_amt), 0) * 100, 
    2
  ) as adj_rate_pct
FROM carrier_pmts
WHERE carrier_pmt_dt BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY carrier_nm
ORDER BY total_adj DESC;

Identify Pmt vs Paid Discrepancies

SELECT 
  claim_id,
  carrier_pmt_id,
  carrier_pmt_amt,
  carrier_paid_amt,
  (carrier_pmt_amt - carrier_paid_amt) as difference,
  adj_reason_desc
FROM carrier_pmts
WHERE carrier_pmt_amt != carrier_paid_amt
  AND carrier_pmt_status = 'PAID'
  AND ABS(carrier_pmt_amt - carrier_paid_amt) > 0.01
ORDER BY difference DESC
LIMIT 100;

Average Days to Payment by Carrier

SELECT 
  c.carrier_nm,
  COUNT(*) as paid_claims,
  ROUND(AVG(
    EXTRACT(DAY FROM (cp.carrier_pmt_dt - cl.service_dt))
  ), 1) as avg_days_to_pmt
FROM carrier_pmts cp
JOIN claims cl ON cp.claim_id = cl.claim_id
JOIN carriers c ON cp.carrier_id = c.carrier_id
WHERE cp.carrier_pmt_status = 'PAID'
  AND cp.carrier_pmt_dt >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY c.carrier_nm
HAVING COUNT(*) >= 10
ORDER BY avg_days_to_pmt ASC;

8Reporting & Analytics

Key Performance Indicators (KPIs)

Collection Rate

Percentage of billed amount successfully collected

(Paid Amt / Billed Amt) × 100

Denial Rate

Percentage of claims denied by carriers

(Denied Claims / Total Claims) × 100

Days in A/R

Average time from service to payment

AVG(Pmt Date - Service Date)

Adjustment Rate

Percentage of billed amount adjusted

(Adj Amt / Billed Amt) × 100

Monthly Revenue Cycle Dashboard Query

WITH monthly_metrics AS (
  SELECT 
    DATE_TRUNC('month', carrier_pmt_dt) as month,
    COUNT(*) as total_claims,
    COUNT(*) FILTER (WHERE carrier_pmt_status = 'PAID') as paid_claims,
    COUNT(*) FILTER (WHERE carrier_pmt_status = 'DENIED') as denied_claims,
    SUM(carrier_pmt_amt) as total_allowed,
    SUM(carrier_paid_amt) as total_collected,
    SUM(carrier_adj_amt) as total_adj
  FROM carrier_pmts
  WHERE carrier_pmt_dt >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY DATE_TRUNC('month', carrier_pmt_dt)
)
SELECT 
  month,
  total_claims,
  paid_claims,
  denied_claims,
  ROUND((denied_claims::NUMERIC / total_claims * 100), 2) as denial_rate_pct,
  total_allowed,
  total_collected,
  ROUND((total_collected / NULLIF(total_allowed, 0) * 100), 2) as collection_rate_pct,
  total_adj,
  ROUND((total_adj / NULLIF(total_allowed + total_adj, 0) * 100), 2) as adj_rate_pct
FROM monthly_metrics
ORDER BY month DESC;

mdatool.

Carrier Payment Data Standards

Abbreviations Used in This Guide

Table of Contents