Your Revenue Cycle Management Data Template
Your Revenue Cycle Management Data Template
- Recommended attributes to collect
- Key activities to track
- Extraction guidance
Revenue Cycle Management Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The name of the specific event or task performed within the revenue cycle management process. | ||
| Description This attribute describes a single step in the revenue cycle, such as 'Charges Captured', 'Claim Submitted to Payer', or 'Payment Received'. Each activity represents a distinct milestone in the process of billing and collecting payment for a service. Analyzing activities is the foundation of process mining. It allows for the visualization of the process map, identification of common pathways, discovery of bottlenecks between steps, and measurement of conformance to standard operating procedures. Why it matters Defines the steps in the process map, making it possible to visualize, analyze, and optimize the flow of work in the revenue cycle. Where to get This is typically derived from event logs, audit trails, or status change records within Epic Resolute's billing and claims modules. Examples Charges CapturedClaim Submitted to PayerPayment ReceivedAccount Closed | |||
| Billing Event BillingEvent | The unique identifier for a single service or product delivery that generates a charge, serving as the primary case identifier. | ||
| Description The Billing Event is the core identifier that connects all activities within the revenue cycle for a specific billable item. It begins when a service is rendered and concludes when the account is fully settled or closed. In process mining analysis, this attribute is essential for reconstructing the end-to-end journey of each charge. It allows for the tracking of activities like charge capture, claim submission, payment posting, and denial management for individual billing events, providing a clear view of the process flow and its variations. Why it matters This is the fundamental Case ID, crucial for linking all related process steps together to analyze the complete lifecycle of revenue generation and collection for each service. Where to get This is often a unique identifier for a hospital account (HAR) or a specific charge session in Epic Resolute. Consult Epic Resolute documentation for specific tables like HAR or Charge Session records. Examples BE10098765BE20012345BE30054321 | |||
| Event Timestamp EventTimestamp | The precise date and time when a specific activity or event occurred. | ||
| Description The Event Timestamp records the moment an activity took place. This temporal data is critical for understanding the timing and sequencing of events in the revenue cycle. In analysis, timestamps are used to calculate durations between activities, such as charge capture lag or payment posting time. They enable the discovery of bottlenecks, measurement of cycle times, and analysis of process performance over different time periods. Accurate timestamps are essential for nearly all time-based KPIs and dashboards. Why it matters This attribute is essential for calculating all time-based metrics, including cycle times and durations, which are fundamental to identifying delays and inefficiencies. Where to get Found in transaction or event log tables within Epic Resolute, associated with each recorded activity. Fields are often named with suffixes like Dt, DTTM, or Time. Examples 2023-04-15T09:30:00Z2023-04-16T11:05:21Z2023-05-01T14:00:00Z | |||
| Adjustment Reason AdjustmentReason | The reason for a manual or automated adjustment made to a patient's account balance. | ||
| Description This attribute explains why an account balance was changed outside of a standard payment or charge. Reasons can include contractual allowances with payers, small balance write-offs, or corrections for posting errors. This is essential for the 'Account Adjustment Volume By Type' dashboard. By analyzing adjustment reasons, organizations can identify sources of revenue leakage, understand the impact of payer contracts, and spot potential inefficiencies or errors in the billing process. Why it matters Provides insight into revenue leakage and billing accuracy by explaining why account balances are being modified, helping to reduce unnecessary write-offs. Where to get Located in the transaction details for adjustment entries within Epic Resolute's patient accounting module. Examples Contractual AllowanceSmall Balance Write-OffDuplicate Charge Correction | |||
| Billing Department BillingDepartment | The department or functional team responsible for the billing event or activity. | ||
| Description This attribute indicates the organizational unit, such as 'Inpatient Billing', 'Outpatient Billing', or 'Denial Management Team', that is associated with the billing event or performed a specific activity. This dimension is crucial for the 'Billing Department Performance Metrics' dashboard, enabling side-by-side comparisons of key metrics like denial rates or charge capture times. It helps management identify high-performing departments, standardize best practices, and allocate resources effectively. Why it matters Enables performance benchmarking across different departments, helping to identify best practices and areas needing improvement or additional resources. Where to get This information might be linked to the user record, the patient account, or the service location within Epic Resolute. Examples Cardiology BillingRadiology RCMCentral Billing Office | |||
| Denial Reason Code DenialReasonCode | A standardized code indicating the reason a payer denied a submitted claim. | ||
| Description When a payer rejects a claim, they provide a reason code that explains the denial, such as 'Non-Covered Service', 'Duplicate Claim', or 'Requires Additional Information'. These codes are often standardized as Claim Adjustment Reason Codes (CARCs). This attribute is the cornerstone of the 'Claim Denial Rates And Reasons' dashboard. Analyzing the frequency of different denial codes helps identify the root causes of denials, such as credentialing issues, coding errors, or missing pre-authorizations, enabling targeted improvement initiatives. Why it matters Directly explains why claims are being rejected, providing actionable insights needed to reduce denial rates, prevent revenue loss, and accelerate payments. Where to get This data is found in the claim response transactions (like an ANSI 835 file) received from payers and stored within Epic Resolute's claims management module. Examples CO-16: Claim/service lacks informationOA-18: Duplicate claim/servicePR-96: Non-covered charge(s) | |||
| Outstanding Balance OutstandingBalance | The remaining amount of money due from the payer or patient for the billing event. | ||
| Description This attribute represents the current accounts receivable balance for a specific billing event at the time of the activity. It reflects the financial status of the case throughout its lifecycle. The outstanding balance is critical for financial reporting and for the 'Outstanding Balance Aging Report'. Analyzing this value over time and by different dimensions like payer or department helps prioritize collection efforts, manage cash flow, and assess financial risk. Why it matters Directly measures the financial impact of process delays and is essential for prioritizing collections, managing cash flow, and understanding accounts receivable. Where to get This is a core field on the patient account or hospital account (HAR) record in Epic Resolute. It's a running balance that is updated by financial transactions. Examples 1500.00250.750.00 | |||
| Responsible User ResponsibleUser | The identifier of the user or employee who performed the activity. | ||
| Description This attribute captures the user ID, name, or employee number of the person responsible for completing a specific task in the revenue cycle. This could be the clinician who entered charges, the biller who submitted a claim, or the collector who followed up on a denial. Analyzing by user helps identify top performers, pinpoint training needs, and understand workload distribution. It is key for performance management and for investigating process deviations associated with specific individuals or roles. Why it matters Allows for performance analysis by individual or role, helping to identify training opportunities, workload imbalances, and resource-related bottlenecks. Where to get Typically found in audit trail or transaction logs within Epic Resolute, often linked to a user master data table (e.g., EMP record). Examples j.doebsmith123User7890 | |||
| Service Type ServiceType | The category or type of medical service that was rendered. | ||
| Description This attribute classifies the billable service, for example, as 'Radiology', 'Surgery', 'Consultation', or 'Emergency Room Visit'. It provides clinical context to the financial data. Analyzing the revenue cycle by service type can uncover process variations specific to certain clinical areas. For instance, surgical procedures may have more complex charge capture and authorization requirements than a standard office visit, leading to different process behaviors and challenges. Why it matters Provides clinical context to financial data, enabling analysis of how different types of medical services impact the revenue cycle process and its efficiency. Where to get Derived from the charge description master (CDM), service line, or department associated with the charge transaction in Epic. Examples Inpatient SurgeryOutpatient RadiologyEmergency Services | |||
| Adjusted Amount AdjustedAmount | The monetary value of an adjustment transaction. | ||
| Description This field records the specific dollar amount of an account adjustment. It can be a positive or negative value, representing a credit or debit to the account balance. This amount is the primary metric for the 'Account Adjustment Volume By Type' dashboard. Summing this value by adjustment reason provides a clear picture of the financial impact of different types of adjustments, such as how much revenue is written off due to contractual obligations versus how much is lost to correctable errors. Why it matters Quantifies the financial impact of account adjustments, making it possible to measure revenue leakage and the cost of billing inaccuracies. Where to get Located in the financial transaction detail tables in Epic Resolute, associated with adjustment-type transactions. Examples -1250.45-50.0025.10 | |||
| Claim ID ClaimId | The unique identifier assigned to an insurance claim submitted to a payer. | ||
| Description This attribute is the specific ID for the claim form (e.g., a CMS-1500 or UB-04) sent to a payer. A single billing event might involve multiple claims if services are rebilled or appealed. Tracking by Claim ID is useful for detailed analysis of the claim submission and denial management sub-processes. It helps distinguish activities related to the initial claim from those related to a subsequent, resubmitted claim for the same service. Why it matters Provides a granular identifier for tracking the lifecycle of each specific claim submission, which is crucial for analyzing resubmissions and appeals. Where to get Generated by Epic Resolute's claims management module when a claim is created. It is stored in the claims data tables. Examples CLM-2023-98765CLAIM-0012345623189A4567 | |||
| Event End Time EventEndTime | The timestamp indicating when an activity was completed, useful for calculating activity duration. | ||
| Description This attribute records the completion time of an activity. While many activities are instantaneous events where StartTime equals EndTime, some tasks have a measurable duration, such as a denial follow-up call. When available, EndTime allows for the direct calculation of activity processing time ('EndTime' - 'StartTime'). This is more accurate than inferring duration from the start time of the next activity, as it accounts for idle time between steps. It is a key component for calculating the 'ProcessingTime' attribute. Why it matters Enables the precise calculation of how long each activity takes to complete, which is crucial for identifying inefficient tasks and measuring resource productivity. Where to get This may be available in some Epic Resolute modules that track task start and end, such as work queue or activity management logs. Often, it is not explicitly tracked. Examples 2023-04-15T09:45:00Z2023-04-16T11:15:30Z2023-05-01T14:02:00Z | |||
| Is Automated IsAutomated | A boolean flag indicating if the activity was performed by a system or an automated process. | ||
| Description This flag distinguishes between tasks executed automatically by the system, such as automated claim generation or eligibility checks, and those performed manually by a user. Analyzing this attribute helps in understanding the level of automation in the process. It can be used to compare the efficiency and error rates of automated versus manual activities, identify opportunities for further automation, and monitor the performance of existing bots or system rules. Why it matters Distinguishes between system-driven and human-driven activities, which is key for evaluating the impact of automation and identifying new automation opportunities. Where to get This is often derived by checking if the 'ResponsibleUser' for an activity is a system or service account, or by flagging specific activity names known to be automated. Examples truefalse | |||
| Last Data Update LastDataUpdate | The timestamp indicating when the data for this event was last refreshed or extracted from the source system. | ||
| Description This attribute shows the freshness of the data. It indicates the last time the record was pulled from Epic Resolute into the process mining dataset. This is important for understanding the timeliness of the analysis and for data validation purposes. It helps users know if they are looking at the most current information available and is critical for managing data refresh cycles. Why it matters Ensures users understand the timeliness of the data they are analyzing, which is critical for making accurate, up-to-date business decisions. Where to get This timestamp is added by the ETL (Extract, Transform, Load) process during data ingestion. Examples 2023-06-10T02:00:00Z2023-06-11T02:00:00Z | |||
| Patient ID PatientId | The unique identifier for the patient receiving the service. | ||
| Description This attribute is the Medical Record Number (MRN) or other unique identifier for the patient. It links the financial billing event to a specific individual. While not typically used as a primary analysis dimension to protect patient privacy, it is essential for data validation and can be used to aggregate all billing events for a single patient to understand their overall financial journey. It is also crucial for any potential integration with clinical process data. Why it matters Links financial data to a specific patient, enabling data validation and potential for broader analysis of a patient's entire journey, though it must be handled with care due to privacy concerns. Where to get A fundamental identifier found throughout Epic, linked to the patient's registration and account records. Examples MRN-1234567MRN-8765432MRN-5551234 | |||
| Payer Name PayerName | The name of the insurance company, government entity, or other party responsible for payment. | ||
| Description This attribute identifies the primary payer associated with the billing event, such as 'Blue Cross Blue Shield', 'Medicare', or 'Aetna'. In cases of self-pay, it may indicate the patient. Segmenting the process by payer is a powerful analysis technique. It can reveal that certain payers have higher denial rates, longer payment cycles, or more complex requirements. This insight allows for the tailoring of billing strategies to specific payers to improve efficiency and payment speed. Why it matters Allows for performance analysis by payer, revealing which payers have high denial rates or slow payment cycles, enabling targeted follow-up strategies. Where to get This information is part of the patient's coverage details, linked to the hospital account (HAR) in Epic Resolute. Examples Medicare Part BUnitedHealthcareAetna PPO | |||
| Payment Due Date PaymentDueDate | The date by which payment for the billed service is expected. | ||
| Description This attribute specifies the deadline for payment, as stated on the invoice or determined by payer contracts. It serves as a benchmark for measuring timely payments. The payment due date is essential for creating the 'Outstanding Balance Aging Report'. By comparing the current date to the due date for open balances, accounts receivable can be categorized into aging buckets (e.g., 0-30 days, 31-60 days past due), which helps prioritize collection efforts on the most overdue accounts. Why it matters Serves as the basis for accounts receivable aging analysis, which is critical for prioritizing collections and managing financial risk from unpaid bills. Where to get This date is often calculated based on the invoice date and payment terms stored in the payer contract or patient account information within Epic. Examples 2023-05-302023-06-152023-07-01 | |||
| Processing Time ProcessingTime | The calculated duration of time spent actively working on an activity. | ||
| Description Processing time, also known as handling time, measures the duration of an activity from its start to its end. It represents the time a resource was actively engaged with the task. This metric is calculated as the difference between 'EventEndTime' and 'EventTimestamp'. Analyzing processing time helps identify which specific tasks are most time-consuming, allowing for focused efforts on streamlining and improving efficiency. It is a fundamental measure of resource productivity. Why it matters Measures the actual work duration of activities, helping to identify time-consuming tasks and evaluate the efficiency of resources. Where to get This is not a field in the source system. It is calculated during data transformation using the formula: EventEndTime - EventTimestamp. Examples 900605120 | |||
| Source System SourceSystem | The information system from which the data originates. | ||
| Description This attribute identifies the source system of the record, which for this context is Epic Resolute. In environments with multiple integrated systems, this field helps differentiate data origins. While it may seem redundant in a single-system view, it is a best practice for data governance and scalability. It ensures clarity if data from other systems, like a separate collections agency platform, is integrated later. Why it matters Provides crucial data lineage and context, ensuring clarity on the origin of the data, which is vital for data governance and troubleshooting. Where to get This is typically a static value added during the data extraction and transformation process to label the dataset's origin. Examples Epic ResoluteEpicResolute_V2023 | |||
| Total Revenue Cycle Time TotalRevenueCycleTime | The total calculated duration from the first service event to the final payment or account closure. | ||
| Description This is a case-level KPI that measures the end-to-end duration of the revenue cycle for a single billing event. It is typically calculated as the time difference between the 'Service Rendered' activity and the final 'Payment Received' or 'Account Closed' activity. This high-level metric provides a holistic view of the overall efficiency of the RCM process. Tracking this KPI over time helps measure the impact of process improvement initiatives and provides a key indicator of cash conversion speed. Why it matters Provides a high-level, end-to-end view of process efficiency, directly measuring how long it takes to convert a service into cash. Where to get This is a metric calculated within the process mining tool by filtering for the first and last events of each case and finding the time difference. Examples 259200038880005184000 | |||
Revenue Cycle Management Activities
| Activity | Description | ||
|---|---|---|---|
| Account Closed | This is the final activity, signifying that the billing event's outstanding balance has reached zero and there are no more pending activities. This can be due to full payment, adjustments, or a write-off. | ||
| Why it matters This event marks the successful completion of the revenue cycle for a billing event. The end-to-end duration from service to closure is a critical KPI for overall process efficiency. Where to get This is typically an inferred event. It is determined by identifying the point in time when the account balance for the billing event becomes zero and remains zero. Capture Inferred by calculating a running total of the account balance and identifying the timestamp of the last transaction that made the balance zero. Event type inferred | |||
| Charges Captured | Represents the formal recording of billable charges for the services rendered. In Epic, this is typically an explicit transaction posted to the patient's account, often generated automatically from clinical actions or entered manually. | ||
| Why it matters This is a critical first milestone. Measuring the velocity and accuracy of charge capture helps in accelerating the billing process and ensuring all provided services are billed for. Where to get Explicitly recorded in Resolute's transaction logs. Each charge is a discrete entry with a post date, service date, and amount, often found in tables like ARPB_TRANSACTIONS. Capture Capture charge posting transactions from the system's financial transaction log. Event type explicit | |||
| Claim Denied by Payer | Represents the receipt of a notification from the payer that the claim has been denied. This is captured when Epic processes an electronic remittance advice (835 file) or when a user manually posts a denial. | ||
| Why it matters This activity initiates a critical rework loop. Analyzing denial reasons and volumes is essential for identifying root causes, improving first-pass payment rates, and reducing collection delays. Where to get Explicitly recorded as a transaction or status update on the claim. Denial information, including reason codes, is typically received electronically and posted to the account. Capture Filter for specific transaction types or claim status updates that indicate a denial. Event type explicit | |||
| Claim Submitted to Payer | This marks the event where the claim is officially sent to the insurance payer for adjudication. In Epic, this is a tracked event, logged when the electronic claim file is transmitted to the clearinghouse or payer. | ||
| Why it matters This milestone is crucial as it starts the clock on the payer's payment timeline. Analyzing this helps measure the efficiency of the claims transmission process and supports the Invoice to Payer Delivery Time KPI. Where to get This is an explicit event recorded in Resolute. The claim record will have a submission status and a timestamp indicating when it was sent. Capture Capture the timestamp associated with the claim status changing to 'Submitted' or 'Transmitted'. Event type explicit | |||
| Payment Posted to Account | This is the event where a received payment is applied or allocated to specific charges on the patient's account. This action reduces the outstanding balance of the billing event. | ||
| Why it matters Efficient payment posting is crucial for maintaining accurate account balances and closing out billing events. It allows for correct identification of remaining balances for secondary billing or collections. Where to get This is an explicit transaction in Resolute. Payment posting links a payment transaction to one or more charge transactions, which is recorded in the transaction detail tables. Capture Capture the transaction record that applies a payment to a charge, identifiable by specific transaction types. Event type explicit | |||
| Payment Received | Represents the receipt of payment from a payer or patient. This event is typically recorded when an electronic remittance advice (ERA) is loaded or a manual check is entered into the system. | ||
| Why it matters This activity is a major milestone indicating that revenue is incoming. The time between claim submission and payment receipt is a key measure of accounts receivable performance. Where to get Explicitly recorded as a payment transaction in Resolute. These transactions are logged with a date, source, and amount, often before they are fully posted to individual charges. Capture Capture payment transactions from the financial transaction log, often identified by specific transaction types. Event type explicit | |||
| Service Rendered | This activity marks the point when a clinical service is provided to the patient, which initiates the billing event. This is often captured from the Epic EHR (EpicCare) when a clinician signs off on a visit or procedure. | ||
| Why it matters This is the primary start event for the revenue cycle. Analyzing the time from this point to charge capture is critical for identifying delays in billing initiation and potential revenue leakage. Where to get This event is typically inferred from service or visit timestamps in the clinical modules that are linked to the billing account. The service date on the charge transaction is the key data point. Capture Inferred from the service date associated with the first charge transaction for the billing event. Event type inferred | |||
| Account Adjustment Made | This activity represents a non-payment transaction that alters the account balance, such as a contractual adjustment, a small balance write-off, or a goodwill discount. This is recorded as a specific transaction type. | ||
| Why it matters Analyzing adjustments is key to identifying revenue leakage. High volumes of certain adjustment types can indicate issues with fee schedules, contracting, or internal policies. Where to get Explicitly recorded as adjustment transactions in Resolute's financial logs. Each adjustment will have a specific type or reason code associated with it. Capture Filter for transaction types that correspond to financial adjustments or write-offs. Event type explicit | |||
| Balance Sent to Collections | This marks the point when an unpaid account balance is transferred to an internal or external collections process. This is often an explicit status change on the account or billing event. | ||
| Why it matters This activity initiates the final stage of recovering unpaid balances. Tracking the success rate and cycle time of the collections process is vital for minimizing bad debt. Where to get This is typically an explicit event. Epic has functions to transfer accounts to collections agencies, which creates a log entry or a status change on the account. Capture Identify the status change or transaction that indicates an account has been placed with a collection agency. Event type explicit | |||
| Claim Generated | This activity signifies the system's creation of a formal claim or invoice based on the captured charges. It is a preparatory step before the claim is sent to the payer or patient. | ||
| Why it matters Tracking claim generation helps isolate delays between capturing charges and preparing them for submission. It is a key internal step that can impact overall billing timeliness. Where to get This is typically logged when a billing or claims generation batch job runs. The system will record a timestamp when the claim file (like an 837 file) is created for a given account. Capture Identify log entries or status changes indicating the claim has been compiled and is ready for submission. Event type explicit | |||
| Claim Resubmitted | This event occurs after a denied claim has been corrected and is sent back to the payer. This is a distinct submission event that is linked to the original claim. | ||
| Why it matters This is a key part of the rework loop. Measuring the time to resubmit and the success rate of resubmitted claims is vital for understanding the effectiveness of the denial resolution process. Where to get This is an explicit event similar to the initial submission, but often flagged as a resubmission. The claim record will show a new submission timestamp and may include a resubmission code. Capture Capture the timestamp for a claim submission that is flagged as a correction or resubmission. Event type explicit | |||
| Denial Follow-Up Initiated | This activity marks the start of the internal process to review and resolve a denied claim. It is often captured when a user takes ownership of the denied claim in a workqueue or changes its status. | ||
| Why it matters Tracking this helps measure the responsiveness of the denials management team. Delays between a denial and the start of follow-up can extend the revenue cycle unnecessarily. Where to get This is typically inferred from changes in the claim's status or assignment history within Epic's workqueues. For example, the claim's status might change from 'Denied' to 'In Review'. Capture Infer from a claim status change or an audit log entry showing a user has started working the denial. Event type inferred | |||
Extraction Guides
Steps
- Establish Database Connection: Obtain read-only credentials for the Epic Clarity database. Use a standard SQL client, such as DBeaver or Microsoft SQL Server Management Studio, to connect to the database server.
- Identify Core Tables: The primary tables for this extraction include
HSP_ACCOUNTfor case information,HSP_TRANSACTIONSfor financial events,CLP_CLAIM_INFOfor claim status, andF_ARHB_TX_SET_POST_HXfor payment posting details. You will also join master files likeCLARITY_EMPfor user details. - Define the Scope: Before writing the query, determine the scope of your analysis. Define a specific date range, typically 3 to 6 months, and identify any specific hospital service areas (
SERV_AREA_ID) or account classes you wish to include or exclude. - Develop the SQL Query: Construct a SQL query using a Common Table Expression (CTE) to first select the set of
HSP_ACCOUNT_IDvalues that fall within your defined scope. This will serve as the base population of billing events. - Union Individual Activity Queries: For each of the 12 required activities, write a separate
SELECTstatement that retrieves data from the relevant tables. Join back to your initial CTE to ensure you only analyze the intended accounts. - Combine Queries with UNION ALL: Use the
UNION ALLoperator to combine the results from all individual activity queries into a single, cohesive event log. This stacks the rows from each query vertically. - Map to Standard Schema: In each
SELECTstatement, alias the columns to match the required ProcessMind schema:BillingEvent,ActivityName,EventTimestamp,ResponsibleUser, etc. UseNULLfor attributes that are not applicable to a specific activity. - Execute and Refine the Query: Run the complete query against the Clarity database. Due to the size of the tables, this may take a significant amount of time. If performance is an issue, further restrict the date range or add more specific filters in the initial CTE.
- Review the Output: Once the query completes, inspect the first few hundred rows of the output. Verify that all columns are present, timestamps are in a consistent format, and different
ActivityNamevalues are appearing as expected. - Export to CSV: Export the entire result set from your SQL client to a CSV file. Ensure the file uses UTF-8 encoding and includes a header row with the correct column names.
- Prepare for Upload: Before uploading to ProcessMind, open the CSV file to confirm there are no formatting errors. Check that the timestamp format is consistent, for example,
YYYY-MM-DD HH:MI:SS. The file is now ready for ingestion.
Configuration
- Database Connection: A read-only user account with access to the Epic Clarity database is required.
- Date Range Parameters: The provided query uses
@StartDateand@EndDatevariables. These must be set to define the analysis period. A range of 3 to 6 months is recommended to balance data volume with performance. - Table and Column Mapping: The query assumes standard Clarity table and column names. Your organization's specific Epic configuration or version may have variations. You may need to adjust table names, column names, or join conditions accordingly.
- Transaction and Status Codes: The query includes placeholders like
[Your Denial Tx Type]and[Your Collections Status Code]. You must consult your Epic system administrators or review the relevant master files, likeZC_TX_TYPEorZC_ACCOUNT_STATUS, to find the correct codes for your instance. - Filtering: For better performance and more focused analysis, add filters to the initial
BaseAccountsCTE. Common filters includeSERV_AREA_IDto limit by hospital service area orACCOUNT_CLASS_Cto focus on Inpatient or Outpatient billing.
a Sample Query sql
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-06-30';
WITH BaseAccounts AS (
SELECT DISTINCT
HA.HSP_ACCOUNT_ID
FROM
HSP_ACCOUNT HA
WHERE
HA.ADM_DATE_TIME >= @StartDate
AND HA.ADM_DATE_TIME <= @EndDate
-- Add additional filters here if needed, for example:
-- AND HA.SERV_AREA_ID = [Your Service Area ID]
)
-- 1. Service Rendered
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Service Rendered' AS ActivityName,
tx.SERVICE_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
AND tx.ORIG_REV_TX_ID IS NULL -- Not a reversal
UNION ALL
-- 2. Charges Captured
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Charges Captured' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
UNION ALL
-- 3. Claim Generated
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Generated' AS ActivityName,
claim.GENERATED_TIME AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.GENERATED_TIME IS NOT NULL
UNION ALL
-- 4. Claim Submitted to Payer
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Submitted to Payer' AS ActivityName,
claim.XMIT_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.XMIT_DATE IS NOT NULL
UNION ALL
-- 5. Claim Denied by Payer
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Denied by Payer' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
remit.REMIT_CODE_ID AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN F_ARHB_TX_SET_POST_HX remit ON tx.TX_ID = remit.TX_ID
WHERE tx.TX_TYPE_C IN ([Your Denial Tx Type]) -- Placeholder for denial transaction type codes
UNION ALL
-- 6. Denial Follow-Up Initiated (assumes status change on account)
SELECT
hist.HSP_ACCOUNT_ID AS BillingEvent,
'Denial Follow-Up Initiated' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
NULL AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCT_STATUS_HX hist
INNER JOIN BaseAccounts ba ON hist.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON hist.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE hist.ACCOUNT_STATUS_C = [Your Denial Followup Status Code] -- Placeholder for a status indicating follow-up
UNION ALL
-- 7. Claim Resubmitted
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Resubmitted' AS ActivityName,
claim.RESUBMIT_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON claim.RESUBMIT_USER_ID = emp.USER_ID
WHERE claim.RESUBMIT_DATE IS NOT NULL
UNION ALL
-- 8. Payment Received & 9. Payment Posted to Account (combined for this query)
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Payment Posted to Account' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE tx.TX_TYPE_C IN ([Your Payer Payment Tx Type], [Your Patient Payment Tx Type]) -- Placeholder for payment transaction types
UNION ALL
-- 10. Account Adjustment Made
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Account Adjustment Made' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
zcar.NAME AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN ZC_ADJ_REASON zcar ON tx.ADJ_REASON_C = zcar.ADJ_REASON_C
WHERE tx.TX_TYPE_C IN ([Your Adjustment Tx Type]) -- Placeholder for adjustment transaction types
UNION ALL
-- 11. Balance Sent to Collections
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Balance Sent to Collections' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
INNER JOIN HSP_ACCT_STATUS_HX hist ON acct.HSP_ACCOUNT_ID = hist.HSP_ACCOUNT_ID AND hist.ACCOUNT_STATUS_C = [Your Collections Status Code]
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE acct.ACCOUNT_STATUS_C = [Your Collections Status Code] -- Placeholder for collections status
UNION ALL
-- 12. Account Closed
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Account Closed' AS ActivityName,
acct.CLOSED_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- System or Final transaction user
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE acct.ACCT_FIN_BALANCE = 0
AND acct.CLOSED_DATE IS NOT NULL
AND acct.CLOSED_DATE BETWEEN @StartDate and @EndDate
ORDER BY
BillingEvent,
EventTimestamp;Steps
- Establish Database Connection: Obtain read-only credentials for the Epic Clarity database. Use a standard SQL client, such as DBeaver or Microsoft SQL Server Management Studio, to connect to the database server.
- Identify Core Tables: The primary tables for this extraction include
HSP_ACCOUNTfor case information,HSP_TRANSACTIONSfor financial events,CLP_CLAIM_INFOfor claim status, andF_ARHB_TX_SET_POST_HXfor payment posting details. You will also join master files likeCLARITY_EMPfor user details. - Define the Scope: Before writing the query, determine the scope of your analysis. Define a specific date range, typically 3 to 6 months, and identify any specific hospital service areas (
SERV_AREA_ID) or account classes you wish to include or exclude. - Develop the SQL Query: Construct a SQL query using a Common Table Expression (CTE) to first select the set of
HSP_ACCOUNT_IDvalues that fall within your defined scope. This will serve as the base population of billing events. - Union Individual Activity Queries: For each of the 12 required activities, write a separate
SELECTstatement that retrieves data from the relevant tables. Join back to your initial CTE to ensure you only analyze the intended accounts. - Combine Queries with UNION ALL: Use the
UNION ALLoperator to combine the results from all individual activity queries into a single, cohesive event log. This stacks the rows from each query vertically. - Map to Standard Schema: In each
SELECTstatement, alias the columns to match the required ProcessMind schema:BillingEvent,ActivityName,EventTimestamp,ResponsibleUser, etc. UseNULLfor attributes that are not applicable to a specific activity. - Execute and Refine the Query: Run the complete query against the Clarity database. Due to the size of the tables, this may take a significant amount of time. If performance is an issue, further restrict the date range or add more specific filters in the initial CTE.
- Review the Output: Once the query completes, inspect the first few hundred rows of the output. Verify that all columns are present, timestamps are in a consistent format, and different
ActivityNamevalues are appearing as expected. - Export to CSV: Export the entire result set from your SQL client to a CSV file. Ensure the file uses UTF-8 encoding and includes a header row with the correct column names.
- Prepare for Upload: Before uploading to ProcessMind, open the CSV file to confirm there are no formatting errors. Check that the timestamp format is consistent, for example,
YYYY-MM-DD HH:MI:SS. The file is now ready for ingestion.
Configuration
- Database Connection: A read-only user account with access to the Epic Clarity database is required.
- Date Range Parameters: The provided query uses
@StartDateand@EndDatevariables. These must be set to define the analysis period. A range of 3 to 6 months is recommended to balance data volume with performance. - Table and Column Mapping: The query assumes standard Clarity table and column names. Your organization's specific Epic configuration or version may have variations. You may need to adjust table names, column names, or join conditions accordingly.
- Transaction and Status Codes: The query includes placeholders like
[Your Denial Tx Type]and[Your Collections Status Code]. You must consult your Epic system administrators or review the relevant master files, likeZC_TX_TYPEorZC_ACCOUNT_STATUS, to find the correct codes for your instance. - Filtering: For better performance and more focused analysis, add filters to the initial
BaseAccountsCTE. Common filters includeSERV_AREA_IDto limit by hospital service area orACCOUNT_CLASS_Cto focus on Inpatient or Outpatient billing.
a Sample Query sql
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-06-30';
WITH BaseAccounts AS (
SELECT DISTINCT
HA.HSP_ACCOUNT_ID
FROM
HSP_ACCOUNT HA
WHERE
HA.ADM_DATE_TIME >= @StartDate
AND HA.ADM_DATE_TIME <= @EndDate
-- Add additional filters here if needed, for example:
-- AND HA.SERV_AREA_ID = [Your Service Area ID]
)
-- 1. Service Rendered
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Service Rendered' AS ActivityName,
tx.SERVICE_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
AND tx.ORIG_REV_TX_ID IS NULL -- Not a reversal
UNION ALL
-- 2. Charges Captured
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Charges Captured' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
UNION ALL
-- 3. Claim Generated
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Generated' AS ActivityName,
claim.GENERATED_TIME AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.GENERATED_TIME IS NOT NULL
UNION ALL
-- 4. Claim Submitted to Payer
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Submitted to Payer' AS ActivityName,
claim.XMIT_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.XMIT_DATE IS NOT NULL
UNION ALL
-- 5. Claim Denied by Payer
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Denied by Payer' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
remit.REMIT_CODE_ID AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN F_ARHB_TX_SET_POST_HX remit ON tx.TX_ID = remit.TX_ID
WHERE tx.TX_TYPE_C IN ([Your Denial Tx Type]) -- Placeholder for denial transaction type codes
UNION ALL
-- 6. Denial Follow-Up Initiated (assumes status change on account)
SELECT
hist.HSP_ACCOUNT_ID AS BillingEvent,
'Denial Follow-Up Initiated' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
NULL AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCT_STATUS_HX hist
INNER JOIN BaseAccounts ba ON hist.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON hist.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE hist.ACCOUNT_STATUS_C = [Your Denial Followup Status Code] -- Placeholder for a status indicating follow-up
UNION ALL
-- 7. Claim Resubmitted
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Resubmitted' AS ActivityName,
claim.RESUBMIT_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON claim.RESUBMIT_USER_ID = emp.USER_ID
WHERE claim.RESUBMIT_DATE IS NOT NULL
UNION ALL
-- 8. Payment Received & 9. Payment Posted to Account (combined for this query)
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Payment Posted to Account' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE tx.TX_TYPE_C IN ([Your Payer Payment Tx Type], [Your Patient Payment Tx Type]) -- Placeholder for payment transaction types
UNION ALL
-- 10. Account Adjustment Made
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Account Adjustment Made' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
zcar.NAME AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN ZC_ADJ_REASON zcar ON tx.ADJ_REASON_C = zcar.ADJ_REASON_C
WHERE tx.TX_TYPE_C IN ([Your Adjustment Tx Type]) -- Placeholder for adjustment transaction types
UNION ALL
-- 11. Balance Sent to Collections
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Balance Sent to Collections' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
INNER JOIN HSP_ACCT_STATUS_HX hist ON acct.HSP_ACCOUNT_ID = hist.HSP_ACCOUNT_ID AND hist.ACCOUNT_STATUS_C = [Your Collections Status Code]
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE acct.ACCOUNT_STATUS_C = [Your Collections Status Code] -- Placeholder for collections status
UNION ALL
-- 12. Account Closed
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Account Closed' AS ActivityName,
acct.CLOSED_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- System or Final transaction user
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE acct.ACCT_FIN_BALANCE = 0
AND acct.CLOSED_DATE IS NOT NULL
AND acct.CLOSED_DATE BETWEEN @StartDate and @EndDate
ORDER BY
BillingEvent,
EventTimestamp;