Skip to main content

PointClickCare (PCC) Documentation

This section provides documentation for analyzing data from the PointClickCare (PCC) Electronic Medical Record (EMR) system.

Overview​

PointClickCare is a cloud-based electronic health record (EHR) platform designed for long-term and post-acute care providers. The database contains a wealth of information about residents, clinical care, billing, and operations.

Analysis Resources​

Aging Report Analysis​

The PCC aging report is a critical financial tool that helps facilities track outstanding balances by age categories. Our analysis includes:

  • Comprehensive Aging Report Analysis - Detailed analysis of the aging report data, including:
    • Data quality assessment
    • Financial workflow analysis (invoicing, payment processing, and adjustments)
    • Business process mapping
    • Historical trend analysis

PCC AR Aging Documentation​

We've created detailed documentation about the PCC AR Aging system:

Payer and Billing Analysis​

The PCC system includes robust capabilities for managing payers and billing services. Our analysis includes:

SQL Scripts​

The following SQL scripts are available for analyzing PCC data:

Aging Report Scripts​

Built-in PCC Stored Procedures​

  • sproc_ar_aging - PCC's built-in stored procedure for aging by effective date

    EXEC dbo.sproc_ar_aging @fac_ids = '1', @reference_date = '20230501';

  • sproc_ar_aging_postdate - PCC's built-in stored procedure for aging by transaction date

    EXEC dbo.sproc_ar_aging_postdate @fac_ids = '1', @reference_date = '20230501';

Custom Analysis Scripts​

  • analyze_aging_report.sql - General analysis of the aging report data
  • analyze_aging_data_quality.sql - Analysis of data quality issues in the aging report
  • analyze_aging_business_processes.sql - Analysis of business processes related to the aging report
  • analyze_aging_historical_trends.sql - Analysis of historical trends in the aging report data

Payer and Billing Scripts​

  • analyze_payer_data.sql - Analysis of payer data and identification of reliable payer identifiers
  • analyze_billing_services.sql - Analysis of billing and invoicing services in PCC

Database Structure Scripts​

  • db_info.sql - Basic information about the database
  • schema_analysis.sql - Analysis of the database schema
  • table_list.sql - List of tables in the database
  • top_tables_columns.sql - List of columns in the most important tables

Data Structure​

The PCC database includes several key tables related to the aging report:

  • ar_transactions - The central table for financial transactions
  • ar_transactions_rollup_client - Aggregated transaction data for reporting
  • ar_applied_payment_history - Tracks how payments are applied to charges
  • ar_payers - Contains facility-specific payer information
  • ar_lib_payers - Master list of payers across all facilities
  • ar_invoice - Represents invoices in the system
  • ar_batch - Groups transactions into batches
  • clients - Contains client/patient information
  • mpi - Master Person Index with demographic information

For a detailed explanation of the AR data model, see the PCC AR Data Model page.

Payer Identification and Billing Services​

For a comprehensive analysis of payer identification and billing services in PCC, see the Payer Identification and Billing Services page.

This analysis includes:

  • Reliable payer identifiers
  • Payer data structure
  • Electronic claim submission
  • Billing workflow
  • Recommendations for payer identification and management

The PCC system uses several key identifiers for payers, including:

  • payer_id - Unique numeric identifier
  • payer_type - Categorizes payers (Medicare A, Medicaid, Private, etc.)
  • payer_code - Short code (MCA, MCD, PP, etc.)
  • description - Full name of the payer

The system supports electronic claim submission through various clearinghouses, including Waystar, Trizetto, and Ability.

Usage​

To use these scripts:

  1. Connect to your PCC database
  2. Run the appropriate SQL script
  3. Analyze the results to gain insights into your data

For more detailed information, see the Aging Report Analysis page.