PCC AR Aging: Web Interface vs. Database Procedures

This document explains the relationship between the PCC web interface for AR Aging reports and the underlying database stored procedures. Understanding this relationship can help reconcile differences between reports generated through different methods.

Overview

The PCC AR Aging system consists of two main components:

1. Web Interface: The screens shown in the screenshots where users can select parameters and run reports
2. Database Procedures: The stored procedures (sproc_ar_aging and sproc_ar_aging_postdate) that perform the actual calculations

Web Interface Components

The PCC web interface for AR Aging reports includes various elements for filtering and running reports. The interface allows users to select parameters such as reference date, aging method, and various filters.

Key Elements:

1. Report Selection: "AR Aging 'New'" at the top of the screen
2. Date Selection: Reference date for aging calculations
3. Aging Bracket: Allows selection of aging method (effective date or transaction date)
4. Filtering Options:
   • Payer Type
   • Payer Reporting Group
   • Resident Status
   • Payer
   • Specific Residents
5. Output Options: Run Now, Excel, PDF/Print

Database Stored Procedures

The database contains two main stored procedures for AR aging:

1. sproc_ar_aging: Ages transactions based on effective date (service date)
2. sproc_ar_aging_postdate: Ages transactions based on transaction date (posting date)

How They Connect

When a user runs a report through the web interface:

1. The web application collects all the parameters selected by the user
2. Based on the aging method selected, it calls either sproc_ar_aging or sproc_ar_aging_postdate
3. It passes the selected parameters to the stored procedure
4. The stored procedure executes and returns results
5. The web application formats and displays these results

Key Differences

Parameter Handling

Web Interface	Database Procedure
Offers user-friendly dropdowns and selections	Accepts raw parameter values
May apply default filters	Only applies filters explicitly passed as parameters
May have additional validation	Minimal validation of inputs

Output Formatting

Web Interface	Database Procedure
Formatted for screen viewing	Returns raw data in result sets
Offers export to Excel/PDF	Returns tabular data only
May include charts or visualizations	Text/numeric data only
May apply additional sorting or grouping	Basic sorting only

Calculation Nuances

In some cases, the web interface might:

1. Apply additional business rules not in the stored procedures
2. Filter out certain results based on user permissions
3. Aggregate data differently for display purposes
4. Round numbers or format currencies differently

Common Discrepancies

When comparing reports from the web interface versus direct database queries, you might notice:

1. Different Totals: Due to filtering differences or parameter handling
2. Missing Records: The web interface might filter out certain payers or residents
3. Timing Differences: Reports run at different times might show different results
4. Formatting Variations: The same data might appear differently

Troubleshooting Differences

If you notice discrepancies between the web interface and database reports:

1. Check Parameters: Ensure you're using identical parameters in both methods
2. Verify Reference Date: Even small differences in reference date can change results
3. Examine Filters: The web interface might apply implicit filters
4. Check Timing: Ensure both reports are run at the same time (or with the same data snapshot)

Technical Implementation

For technical users, here's how the connection works:

1. The PCC web application is likely built using ASP.NET or a similar web framework
2. When a user submits the form to run a report, the application:
   • Validates user inputs
   • Constructs a SQL command to call the appropriate stored procedure
   • Executes the command against the database
   • Processes the returned data
   • Renders the results in HTML or exports to the requested format

Accessing Raw Data

If you need to access the raw data directly:

1. Database Query: You can execute the stored procedures directly:

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

2. Data Export: The web interface allows exporting to Excel, which can then be analyzed

Conclusion

The PCC web interface provides a user-friendly way to access the AR aging data calculated by the database stored procedures. While they work with the same underlying data, differences in parameter handling, filtering, and presentation can sometimes lead to apparent discrepancies between reports generated through different methods.

Understanding the relationship between these components can help reconcile differences and ensure consistent reporting across your organization.