Skip to main content

PCC AR Aging System: Technical Documentation

This document provides a technical overview of the PointClickCare (PCC) Accounts Receivable (AR) Aging system, including database components, stored procedures, and data flow.

Database Schema​

Core Tables​

The PCC AR Aging system relies on several key tables:

Transaction Tables​

Table NameDescriptionKey Fields
ar_transactionsStores individual AR transactionstransaction_id, client_id, payer_id, amount, transaction_type, effective_date, transaction_date
ar_transactions_rollup_clientAggregated transaction data by clienttransaction_id, client_id, payer_id, amount, effective_date, transaction_date
ar_applied_payment_historyTracks payment applications to invoicesapplied_payment_id, transaction_id, amount, applied_to_date, current_period_date

Payer Tables​

Table NameDescriptionKey Fields
ar_payersFacility-specific payer informationpayer_id, fac_id
ar_lib_payersMaster list of payerspayer_id, payer_code, payer_code2, description, payer_type

Client Tables​

Table NameDescriptionKey Fields
clientsClient/resident informationclient_id, mpi_id, client_id_number, admission_date, discharge_date
mpiMaster Person Index - demographic datampi_id, first_name, last_name

Table Relationships​

ar_lib_payers (master payer list)
       ↑
       |
ar_payers (facility-specific payers)
       ↑
       |
ar_transactions ←→ ar_applied_payment_history
       ↑
       |
clients ←→ mpi

Stored Procedures​

sproc_ar_aging​

Purpose: Generates aging reports based on effective date of transactions

Key Parameters:

  • @fac_ids: Comma-separated list of facility IDs
  • @reference_date: Date to use as the reference point for aging calculations

Aging Buckets:

  • Current: effective_date >= reference_date AND < reference_date + 1 month
  • 30 days: effective_date >= reference_date - 1 month AND < reference_date
  • 60 days: effective_date >= reference_date - 2 months AND < reference_date - 1 month
  • 90 days: effective_date >= reference_date - 3 months AND < reference_date - 2 months
  • 120 days: effective_date >= reference_date - 4 months AND < reference_date - 3 months
  • 150 days: effective_date >= reference_date - 5 months AND < reference_date - 4 months
  • 180 days: effective_date >= reference_date - 6 months AND < reference_date - 5 months
  • 210+ days: effective_date < reference_date - 6 months

sproc_ar_aging_postdate​

Purpose: Generates aging reports based on transaction date (posting date)

Key Parameters:

  • @fac_ids: Comma-separated list of facility IDs
  • @reference_date: Date to use as the reference point for aging calculations

Aging Buckets:

  • Current: transaction_date >= reference_date AND < reference_date + 1 month
  • 30 days: transaction_date >= reference_date - 1 month AND < reference_date
  • 60 days: transaction_date >= reference_date - 2 months AND < reference_date - 1 month
  • 90 days: transaction_date >= reference_date - 3 months AND < reference_date - 2 months
  • 120 days: transaction_date >= reference_date - 4 months AND < reference_date - 3 months
  • 150 days: transaction_date >= reference_date - 5 months AND < reference_date - 4 months
  • 180 days: transaction_date >= reference_date - 6 months AND < reference_date - 5 months
  • 210+ days: transaction_date < reference_date - 6 months

Data Flow​

  1. Transaction Creation:

    • Charges, payments, and adjustments are recorded in ar_transactions
    • Each transaction has an effective_date (service date) and transaction_date (posting date)

  2. Payment Application:

    • When payments are applied to charges, records are created in ar_applied_payment_history
    • This links payment transactions to the charges they pay

  3. Aging Calculation:

    • The stored procedures query transaction data
    • They join with client and payer information
    • Transactions are grouped into aging buckets based on dates
    • Results are aggregated by client and payer

  4. Report Generation:

    • Results are returned in two sections:
      1. Detail level: Client-by-client, payer-by-payer breakdown
      2. Summary level: Aggregated by payer

Query Execution​

The stored procedures use a similar approach:

  1. Create a temporary table (#Temp) to store intermediate results
  2. Query transaction data from multiple sources:
    • ar_transactions_rollup_client
    • ar_transactions
    • ar_applied_payment_history
  3. Join with client and payer information
  4. Calculate aging buckets using CASE statements
  5. Return results in two result sets (detail and summary)

Technical Considerations​

  1. Transaction Types:

    • 'C' represents cash/payment transactions
    • Other transaction types include charges and adjustments

  2. Date Handling:

    • Effective date typically represents service date
    • Transaction date represents when the entry was posted
    • The choice between these dates affects aging calculations

  3. Filtering:

    • Only open transactions (OpenClose_Flag = 'O') are included
    • Medicare D payers are excluded
    • Only non-zero amounts are considered

  4. Performance:

    • The procedures use temporary tables for intermediate results
    • Complex CASE statements handle the aging bucket calculations