Skip to main content

PCC AR Data Model

This document provides a detailed explanation of the PCC Accounts Receivable data model, focusing on the tables and relationships that support the AR aging functionality.

Core Tables Overview​

The PCC AR system is built around several interconnected tables that track financial transactions, payers, and clients. Understanding these tables is essential for accurate data analysis and reporting.

Transaction Tables​

ar_transactions​

The primary table for storing individual financial transactions.

Key Fields:

  • transaction_id: Unique identifier for the transaction
  • fac_id: Facility ID
  • client_id: Client/resident ID
  • payer_id: Payer ID
  • transaction_type: Type of transaction (C = Cash/Payment, others for charges/adjustments)
  • amount: Dollar amount of the transaction
  • effective_date: Date when the service was provided or charge became effective
  • transaction_date: Date when the transaction was entered into the system
  • OpenClose_Flag: 'O' for open transactions, 'C' for closed/resolved transactions
  • deleted: 'Y' for deleted transactions, 'N' for active transactions

ar_transactions_rollup_client​

A summary table that aggregates transaction data by client, typically for performance optimization.

Key Fields:

  • Similar to ar_transactions, but with aggregated data
  • Often includes monthly rollups rather than individual transactions

ar_applied_payment_history​

Tracks how payments are applied to specific charges.

Key Fields:

  • applied_payment_id: Unique identifier for the payment application
  • transaction_id: ID of the payment transaction
  • amount: Amount applied to a specific charge
  • applied_to_date: Date to which the payment is applied (often matches the charge's effective date)
  • current_period_date: Date when the payment application was recorded
  • OpenClose_Flag: 'O' for open, 'C' for closed

Payer Tables​

ar_lib_payers​

Master list of all payers across all facilities.

Key Fields:

  • payer_id: Unique identifier for the payer
  • payer_code: Primary code for the payer
  • payer_code2: Secondary code for the payer
  • description: Payer name or description
  • payer_type: Type of payer (e.g., Medicare, Medicaid, Private)
  • deleted: 'Y' for deleted payers, 'N' for active payers

ar_payers​

Facility-specific payer information.

Key Fields:

  • payer_id: References ar_lib_payers.payer_id
  • fac_id: Facility ID
  • Address fields (address1, address2, city, state, zip)
  • Contact information

Client/Resident Tables​

clients​

Stores information about residents/clients.

Key Fields:

  • client_id: Unique identifier for the client
  • client_id_number: External/visible client identifier
  • mpi_id: Reference to the Master Person Index
  • fac_id: Facility ID
  • admission_date: When the client was admitted
  • discharge_date: When the client was discharged (if applicable)
  • deleted: 'Y' for deleted clients, 'N' for active clients

mpi​

Master Person Index - stores demographic information.

Key Fields:

  • mpi_id: Unique identifier
  • first_name: Client's first name
  • last_name: Client's last name
  • Other demographic information

Facility Tables​

facility​

Information about each facility.

Key Fields:

  • fac_id: Unique identifier for the facility
  • facility_code: Short code for the facility
  • name: Facility name
  • Address and contact information

Data Relationships​

The following diagram illustrates the key relationships between these tables:

facility
   ↑
   |
   +-----------------+
   |                 |
clients             ar_payers
   ↑                 ↑
   |                 |
   |                 |
   |                 |
   +----+      +-----+
        |      |
        |      |
        v      v
    ar_transactions
        ↑
        |
        |
        v
ar_applied_payment_history

Transaction Flow​

  1. Charge Creation:

    • A charge is created in ar_transactions
    • It has a specific client_id, payer_id, and amount
    • It has both an effective_date and transaction_date
    • OpenClose_Flag is set to 'O' (open)

  2. Payment Recording:

    • A payment is recorded in ar_transactions
    • transaction_type is set to 'C' (cash)
    • It has a client_id but might not initially have specific charges it applies to

  3. Payment Application:

    • The payment is applied to specific charges
    • Records are created in ar_applied_payment_history
    • These records link the payment transaction to the charges it pays
    • The applied amount might be less than the full payment if it's split across charges

  4. Transaction Closure:

    • When a charge is fully paid, its OpenClose_Flag may be set to 'C' (closed)
    • Similarly, when a payment is fully applied, its OpenClose_Flag may be set to 'C'

Aging Calculation Logic​

The aging calculation uses this data model to:

  1. Identify open transactions (OpenClose_Flag = 'O')
  2. Determine their age based on either effective_date or transaction_date
  3. Group them into aging buckets (current, 30 days, 60 days, etc.)
  4. Aggregate the results by client and payer

Special Considerations​

Deleted Records​

Most tables have a deleted flag. Records marked as deleted (deleted = 'Y') are typically excluded from reports and calculations.

Facility Scope​

Many queries include a facility filter (fac_id). This allows multi-facility organizations to report on specific facilities.

Transaction Types​

Different transaction types affect how records are processed:

  • 'C' (Cash/Payment): Reduces the outstanding balance
  • Other types (charges, adjustments): Typically increase or modify the outstanding balance

Payment Application​

The relationship between payments and charges through ar_applied_payment_history is crucial for accurate aging. A payment transaction itself doesn't reduce aging until it's applied to specific charges.

Performance Considerations​

The AR data model can become quite large in active facilities. Some performance optimizations include:

  1. Rollup Tables: Tables like ar_transactions_rollup_client aggregate data for faster reporting
  2. Indexing: Key fields used in joins and filters should be properly indexed
  3. Temporary Tables: The aging stored procedures use temporary tables (#Temp) to improve performance
  4. Date Filtering: Limiting queries to specific date ranges can significantly improve performance