您的收入周期管理数据模板
您的收入周期管理数据模板
- 建议收集的属性
- 需要追踪的关键活动
- 提取指南
收入周期管理属性
| 名称 | 描述 | ||
|---|---|---|---|
| 事件timestamp EventTimestamp | 特定活动或事件发生的精确日期和时间。 | ||
| 描述 Event Timestamp 记录了活动发生的时刻。此类时间数据对于理解收入周期中事件的时机和顺序至关重要。 在分析中，时间戳用于计算活动之间的持续时间，例如费用捕获延迟或付款入账时间。它们支持发现瓶颈、衡量周期时间以及分析不同时期的流程绩效。准确的时间戳对于几乎所有基于时间的 KPI 和 Dashboard 都必不可少。 为何重要 此属性对于计算所有基于时间的指标（包括周期时间和持续时间）至关重要，是识别延迟和低效环节的基础。 获取方式 存在于 Epic Resolute 的交易或事件日志表中，与每项记录的活动相关联。字段名称通常带有 Dt、DTTM 或 Time 等后缀。 示例 2023-04-15T09:30:00Z2023-04-16T11:05:21Z2023-05-01T14:00:00Z | |||
| 活动名称 ActivityName | 在收入周期管理流程中执行的特定事件或任务的名称。 | ||
| 描述 此属性描述了收入周期中的单个步骤，例如“费用已捕获”、“索赔已提交至付款方”或“已收到付款”。每项活动都代表了服务计费和催收过程中的一个不同里程碑。 分析活动是流程挖掘的基础。它支持流程图的可视化、识别常见路径、发现步骤间的瓶颈以及衡量对标准操作程序 (SOP) 的遵循情况。 为何重要 定义流程图中的各个步骤，从而实现对收入周期工作流的可视化、分析和优化。 获取方式 这通常源自 Epic Resolute 计费和索赔模块中的事件日志、审计线索或状态更改记录。 示例 费用已捕获理赔已提交至付款方已收到付款账户已关闭 | |||
| 账单事件 BillingEvent | 单次服务或产品交付的唯一标识符，产生一项费用，作为主要的 case identifier。 | ||
| 描述 Billing Event 是连接特定计费项目在收入周期内所有活动的核心标识符。它始于提供服务，终于账户完全结清或关闭。 在流程挖掘分析中，该属性对于重构每笔费用的端到端旅程至关重要。它支持跟踪单个账单事件的活动（如费用捕获、索赔提交、付款入账和拒付管理），从而清晰地展示流程流转及其变体。 为何重要 这是基础的 Case ID，对于将所有相关的流程步骤链接在一起至关重要，以便分析每项服务从收入产生到催收的完整生命周期。 获取方式 这通常是 Epic Resolute 中医院账户 (HAR) 或特定计费会话的唯一标识符。请参考 Epic Resolute 文档中的特定表，如 HAR 或计费会话记录。 示例 BE10098765BE20012345BE30054321 | |||
| 拒付原因代码 DenialReasonCode | 一个标准化的代码，指示付款方拒绝已提交理赔的原因。 | ||
| 描述 当付款方拒绝索赔时，他们会提供说明拒付原因的代码，例如“非承保服务”、“重复索赔”或“需要补充信息”。这些代码通常标准化为索赔调整原因代码 (CARC)。 此属性是“索赔拒付率和原因”Dashboard 的基石。分析不同拒付代码的频率有助于识别拒付的根本原因（如资质问题、编码错误或缺少预授权），从而实现针对性的改进。 为何重要 直接解释理赔被拒的原因，提供降低拒付率、防止收入损失和加速支付所需的可操作见解。 获取方式 此数据可在从付款方收到的索赔响应交易（如 ANSI 835 文件）中找到，并存储在 Epic Resolute 的索赔管理模块中。 示例 CO-16: 理赔/服务信息缺失OA-18: 重复理赔/服务PR-96: 非承保费用 | |||
| 服务类型 ServiceType | 所提供的医疗服务的类别或类型。 | ||
| 描述 此属性对可计费服务进行分类，例如“放射科”、“外科”、“咨询”或“急诊室就诊”。它为财务数据提供临床背景。 按服务类型分析收入周期可以发现特定临床领域的流程变体。例如，手术流程可能比标准门诊具有更复杂的费用捕获和授权要求，从而导致不同的流程行为和挑战。 为何重要 为财务数据提供临床背景，以便分析不同类型的医疗服务如何影响收入周期流程及其效率。 获取方式 源自 Epic 中与费用交易相关的费用描述母表 (CDM)、服务线或部门。 示例 住院手术门诊放射科急诊服务 | |||
| 未付余额 OutstandingBalance | 付款方或患者就该账单事件尚欠的余额。 | ||
| 描述 此属性表示活动发生时特定账单事件的当前应收账款余额，反映了该 case 在其生命周期内的财务状态。 未清余额对于财务报告和“未清余额账龄报告”至关重要。按时间维度以及付款方或部门等不同维度分析此数值，有助于确定催收工作的优先级、管理现金流并评估财务风险。 为何重要 直接衡量流程延迟的财务影响，对于催收优先级排序、现金流管理和理解应收账款至关重要。 获取方式 这是 Epic Resolute 中患者账户或医院账户 (HAR) 记录的核心字段。它是随财务交易更新的动态余额。 示例 1500.00250.750.00 | |||
| 调整原因 AdjustmentReason | 对患者账户余额进行手动或自动调整的原因。 | ||
| 描述 此属性解释了账户余额在标准付款或计费之外发生变动的原因。原因可能包括付款方合同免除、小额余额冲销或入账错误更正。 这对于“按类型划分的账户调整量”Dashboard 至关重要。通过分析调整原因，企业可以识别收入流失的源头，了解付款方合同的影响，并发现计费流程中潜在的低效或错误。 为何重要 通过解释账户余额被修改的原因，深入洞察收入流失和计费准确性，帮助减少不必要的坏账冲销。 获取方式 位于 Epic Resolute 患者会计模块中调整分录的交易详情中。 示例 合约津贴小额余额冲销重复费用更正 | |||
| 负责人 ResponsibleUser | 执行该活动的用户或员工的标识符。 | ||
| 描述 此属性捕获负责完成收入周期中特定任务的人员的用户 ID、姓名或员工编号。这可以是输入费用的临床医生、提交索赔的计费员或跟进拒付的催收员。 按用户分析有助于识别表现优异者、确定培训需求并了解工作量分配。它是绩效管理以及调查与特定个人或角色相关的流程偏差的关键。 为何重要 支持按个人或角色进行绩效分析，有助于识别培训机会、工作量失衡以及与资源相关的瓶颈。 获取方式 通常存在于 Epic Resolute 的审计线索或交易日志中，通常链接到用户主数据表（例如 EMP 记录）。 示例 j.doebsmith123User7890 | |||
| 财务部 BillingDepartment | 负责账单事件或活动的部门或职能团队。 | ||
| 描述 此属性表示与账单事件相关或执行特定活动的组织单元，如“住院计费”、“门诊计费”或“拒付管理团队”。 该维度对于“计费部门绩效指标”Dashboard 至关重要，能够对拒付率或费用捕获时间等关键指标进行横向比较。它有助于管理层识别高绩效部门、标准化最佳实践并有效分配资源。 为何重要 支持跨部门绩效基准测试，有助于识别最佳实践以及需要改进或额外资源的领域。 获取方式 此信息可能关联到 Epic Resolute 中的用户记录、患者账户或服务地点。 示例 心血管科计费放射科 RCM中央计费办公室 | |||
| 事件结束时间 EventEndTime | 指示活动何时完成的时间戳，用于计算活动持续时间。 | ||
| 描述 此属性记录活动的完成时间。虽然许多活动是瞬时事件（StartTime 等于 EndTime），但某些任务具有可衡量的持续时间，例如拒付跟进电话。 如果可用，EndTime 支持直接计算活动处理时间（“EndTime” - “StartTime”）。这比从下一个活动的开始时间推断持续时间更准确，因为它考虑了步骤之间的空闲时间。它是计算“ProcessingTime”属性的关键组件。 为何重要 能够精确计算每项活动完成所需的时间，这对于识别低效任务和衡量资源生产力至关重要。 获取方式 这在某些跟踪任务开始和结束的 Epic Resolute 模块中可能可用，例如 work queue 或活动管理日志。通常情况下，它不会被显式跟踪。 示例 2023-04-15T09:45:00Z2023-04-16T11:15:30Z2023-05-01T14:02:00Z | |||
| 付款到期日 PaymentDueDate | 计费服务预计付款的日期。 | ||
| 描述 此属性指定付款截止日期（如发票所示或由付款方合同决定），作为衡量及时付款的基准。 付款截止日期对于创建“未清余额账龄报告”必不可少。通过将当前日期与未结余额的到期日进行比较，可以将应收账款分类到账龄区间（例如 0-30 天、31-60 天逾期），从而帮助优先催收逾期最久的账户。 为何重要 作为应收账款账龄分析的基础，这对于确定催收优先级和管理欠单带来的财务风险至关重要。 获取方式 此日期通常根据 Epic 中的付款方合同或患者账户信息中存储的发票日期和付款条件计算得出。 示例 2023-05-302023-06-152023-07-01 | |||
| 付款方名称 PayerName | 负责付款的保险公司、政府实体或其他方的名称。 | ||
| 描述 此属性识别与账单事件相关的主要付款方，如“Blue Cross Blue Shield”、“Medicare”或“Aetna”。在自费情况下，它可能指代患者。 按付款方对流程进行细分是一种强大的分析技术。它可以揭示某些付款方是否具有更高的拒付率、更长的付款周期或更复杂的要求。这种洞察支持针对特定付款方定制计费策略，从而提高效率和回款速度。 为何重要 支持按付款方进行绩效分析，揭示哪些付款方的拒付率高或付款周期慢，从而实现针对性的跟进策略。 获取方式 此信息是患者保险覆盖详情的一部分，关联到 Epic Resolute 中的医院账户 (HAR)。 示例 Medicare Part BUnitedHealthcareAetna PPO | |||
| 最后数据更新 LastDataUpdate | 指示该事件数据最近一次从源系统刷新或提取的时间戳。 | ||
| 描述 此属性显示数据的时效性，指示记录最后一次从 Epic Resolute 提取到流程挖掘数据集的时间。 这对于理解分析的及时性以及数据验证至关重要。它帮助用户了解所查看的是否是可用的最新信息，并且对于管理数据刷新周期非常关键。 为何重要 确保用户了解所分析数据的时效性，这对于做出准确、及时的业务决策至关重要。 获取方式 此时间戳是在数据接入过程中由 ETL（提取、转换、加载）流程添加的。 示例 2023-06-10T02:00:00Z2023-06-11T02:00:00Z | |||
| 处理时间 ProcessingTime | 活动实际花费的计算时长。 | ||
| 描述 处理时间（也称为操作时间）衡量一项活动从开始到结束的持续时长，代表了资源在任务上投入的实际工作时间。 该指标通过“EventEndTime”与“EventTimestamp”之差计算得出。通过分析处理时间，可以识别出哪些特定任务最耗时，从而针对性地简化流程并提升效率。它是衡量资源生产力的核心指标。 为何重要 衡量活动的实际工作持续时间，有助于识别耗时的任务并评估资源的效率。 获取方式 这不是源系统中的字段。它是在数据转换过程中使用公式“EventEndTime - EventTimestamp”计算得出的。 示例 900605120 | |||
| 总收入周期时间 TotalRevenueCycleTime | 从首次服务事件到最终付款或销户的总计算时长。 | ||
| 描述 这是一个 case 级别的 KPI，用于衡量单个账单事件收入周期的端到端时长。通常计算为“服务已提供”活动与最终“收到付款”或“销户”活动之间的时间差。 这一宏观指标提供了 RCM 流程整体效率的全景视图。长期跟踪此 KPI 有助于衡量流程改进举措的影响，并作为现金转化速度的关键指标。 为何重要 提供流程效率的全局、端到端视图，直接衡量从提供服务到回收现金所需的时间。 获取方式 这是在流程挖掘工具中计算出的指标，通过筛选每个 case 的第一个和最后一个事件并找出时间差得出。 示例 259200038880005184000 | |||
| 患者 ID PatientId | 接受服务的患者的唯一标识符。 | ||
| 描述 此属性是病案号 (MRN) 或患者的其他唯一标识符。它将财务账单事件连接到特定的个人。 虽然为了保护患者隐私通常不将其作为主要的分析维度，但它对于数据验证至关重要，并可用于汇总单个患者的所有账单事件，以了解其整体财务历程。这对于未来与临床流程数据的任何集成也至关重要。 为何重要 将财务数据连接到特定患者，从而支持数据验证以及对患者整个旅程进行更广泛分析的可能性，但由于隐私问题，必须谨慎处理。 获取方式 贯穿 Epic 系统的一个基本标识符，与患者的注册和账户记录相关联。 示例 MRN-1234567MRN-8765432MRN-5551234 | |||
| 是否已自动化 IsAutomated | 一个布尔标志，指示活动是由系统还是自动化程序执行的。 | ||
| 描述 此标识区分了系统自动执行的任务（如自动生成索赔或资格检查）与用户手动执行的任务。 分析此属性有助于了解流程的自动化水平。它可用于比较自动活动与手动活动的效率及错误率，识别进一步自动化的机会，并监控现有机器人或系统规则的性能。 为何重要 区分系统驱动和人工驱动的活动，这是评估自动化影响和识别新自动化机会的关键。 获取方式 这通常通过检查活动的“ResponsibleUser”是否为系统或服务账户，或通过标记已知为自动化的特定活动名称来衍生得出。 示例 truefalse | |||
| 源系统 SourceSystem | 数据源自的信息系统。 | ||
| 描述 此属性识别记录的源系统，在此情境下为 Epic Resolute。在具有多个集成系统的环境中，此字段有助于区分数据来源。 虽然在单一系统视图中看似多余，但它是数据治理和扩展性的最佳实践。它确保了如果以后集成来自其他系统（如独立的催收机构平台）的数据时，来源依然清晰。 为何重要 提供关键的数据血缘和上下文，确保数据来源清晰，这对于数据治理和故障排除至关重要。 获取方式 这通常是在 data 提取和 transformation 过程中添加的静态值，用于标记 dataset 的来源。 示例 Epic ResoluteEpicResolute_V2023 | |||
| 索赔ID ClaimId | 分配给提交给付款方的保险索赔的唯一标识符。 | ||
| 描述 此属性是发送给付款方的索赔表单（例如 CMS-1500 或 UB-04）的特定 ID。如果服务被重新计费或申诉，单个账单事件可能涉及多个索赔。 按 Claim ID 跟踪有助于对索赔提交和拒付管理子流程进行详细分析。它有助于区分与初始索赔相关的活动以及与同一服务的后续重新提交索赔相关的活动。 为何重要 为跟踪每项特定索赔提交的生命周期提供细颗粒度的标识符，这对于分析重新提交和申诉至关重要。 获取方式 理赔创建时由 Epic Resolute 的理赔管理模块生成。它存储在理赔数据表中。 示例 CLM-2023-98765CLAIM-0012345623189A4567 | |||
| 调整金额 AdjustedAmount | 调整交易的金额。 | ||
| 描述 此字段记录账户调整的具体金额。它可以是正值或负值，代表账户余额的贷记或借记。 该金额是“按类型划分的账户调整量”Dashboard 的核心指标。按调整原因汇总此数值，可以清晰展示不同类型调整的财务影响，例如：由于合同义务冲销了多少收入，以及由于可纠正的错误损失了多少收入。 为何重要 量化账户调整带来的财务影响，使衡量收入流失和计费不准确带来的成本成为可能。 获取方式 位于 Epic Resolute 的财务交易明细表中，与调整类交易相关联。 示例 -1250.45-50.0025.10 | |||
收入周期管理活动
| 活动 | 描述 | ||
|---|---|---|---|
| 付款方拒绝理赔 | 代表收到付款方发出的索赔拒付通知。当 Epic 处理电子汇款建议书（835 文件）或用户手动录入拒付时，会捕获此事件。 | ||
| 为何重要 此活动会触发一个关键的返工循环。分析拒付原因和数量对于识别根本原因、提高一次性付款成功率以及减少催收延迟至关重要。 获取方式 明确记录为理赔交易或状态更新。拒付信息（包括原因代码）通常以电子方式接收并过账到账户。 捕获 过滤指示拒付的特定交易类型或理赔状态更新。 事件类型 explicit | |||
| 已收到付款 | 代表收到付款方或患者的付款。此事件通常在加载电子汇款建议书 (ERA) 或在系统中输入手动支票时记录。 | ||
| 为何重要 此活动是一个重要的里程碑，预示着收入即将入账。从索赔提交到收到付款之间的时间是衡量应收账款绩效的关键指标。 获取方式 在 Resolute 中明确记录为付款交易。这些交易记录包含日期、来源和金额，通常在完全过账到单个费用之前记录。 捕获 从财务交易日志中捕获付款交易，通常通过特定的交易类型进行识别。 事件类型 explicit | |||
| 服务已提供 | 此活动标志着向患者提供临床服务的时间点，从而启动账单事件。这通常在临床医生签字确认就诊或手术时从 Epic EHR (EpicCare) 中捕获。 | ||
| 为何重要 这是收入周期的主要起始事件。分析从这一时点到费用捕获的时间，对于识别计费启动延迟和潜在的收入流失至关重要。 获取方式 此事件通常根据与账单账户关联的临床模块中的服务或就诊时间戳推断得出。费用交易上的服务日期是关键数据点。 捕获 从与账单事件的第一笔费用交易相关的服务日期推断。 事件类型 inferred | |||
| 款项已过账至账户 | 此事件是指将收到的付款应用或分配到患者账户中的特定费用。此操作会减少账单事件的未清余额。 | ||
| 为何重要 高效的款项过账对于维持准确的账户余额和关闭账单事件至关重要。它能够正确识别剩余余额，以便进行二次计费或催收。 获取方式 这是 Resolute 中的一项明确交易。付款入账将付款交易与一笔或多笔费用交易关联起来，记录在交易详情表中。 捕获 捕获将付款应用于费用的交易记录，可通过特定的交易类型识别。 事件类型 explicit | |||
| 理赔已提交至付款方 | 这标志着索赔单正式发送至保险付款方进行裁定的事件。在 Epic 中，这是一个被跟踪的事件，在电子索赔文件传输至结算中心或付款方时记录。 | ||
| 为何重要 这一里程碑至关重要，因为它启动了付款方的付款计时。分析这一点有助于衡量索赔传输流程的效率，并支持“发票至付款方交付时间”KPI。 获取方式 这是 Resolute 中记录的明确事件。索赔记录将包含提交状态和指示发送时间的时间戳。 捕获 捕获理赔状态变为“已提交”或“已传输”时的时间戳。 事件类型 explicit | |||
| 账户已关闭 | 这是最后一项活动，表示账单事件的未清余额已归零，且不再有待处理的活动。这可能是由于全额付款、调整或冲销造成的。 | ||
| 为何重要 此事件标志着账单事件收入周期的圆满完成。从服务到销户的端到端时长是衡量整体流程效率的关键 KPI。 获取方式 这通常是一个推断出的事件。通过识别账单事件的账户余额变为零并保持为零的时间点来确定。 捕获 通过计算账户余额的累计总额，并识别使余额归零的最后一笔交易的时间戳来推断。 事件类型 inferred | |||
| 费用已捕获 | 代表对已提供服务的应计费用进行正式记录。在 Epic 中，这通常是记录在患者账户中的明确交易，通常由临床操作自动生成或手动输入。 | ||
| 为何重要 这是一个关键的第一步里程碑。衡量费用捕获的速度和准确性有助于加速计费流程，并确保所有提供的服务均已计费。 获取方式 在 Resolute 的交易日志中明确记录。每项费用都是一条独立的条目，包含过账日期、服务日期和金额，通常可在 ARPB_TRANSACTIONS 等表中找到。 捕获 从系统的财务交易日志中捕获费用过账交易。 事件类型 explicit | |||
| 余额已送往催收 | 这标志着未付账户余额转入内部或外部催收流程的时间点。这通常是账户或账单事件上的明确状态更改。 | ||
| 为何重要 此活动开启了追回未付余额的最后阶段。跟踪催收流程的成功率和周期时间对于最大限度减少坏账至关重要。 获取方式 这通常是一个明确的事件。Epic 具有将账户转移给催收机构的功能，这会产生日志条目或账户状态更改。 捕获 识别指示账户已被转交给催收机构的状态更改或交易。 事件类型 explicit | |||
| 已执行账户调整 | 此活动代表改变账户余额的非付款交易，如合同调整、小额余额冲销或商誉折扣。这被记录为一种特定的交易类型。 | ||
| 为何重要 分析调整是识别收入流失的关键。某些调整类型的大量出现可能表明收费表、合同签订或内部政策存在问题。 获取方式 在 Resolute 的财务日志中明确记录为调整交易。每次调整都会关联特定的类型或原因代码。 捕获 过滤对应于财务调整或核销的交易类型。 事件类型 explicit | |||
| 拒付跟进已启动 | 此活动标志着审核和解决已拒付索赔的内部流程开始。通常在用户在 workqueue 中接手被拒索赔或更改其状态时捕获。 | ||
| 为何重要 跟踪此项有助于衡量拒付管理团队的响应能力。收到拒付通知与开始跟进之间的延迟会不必要地延长收入周期。 获取方式 这通常根据 Epic workqueue 中索赔状态或分配历史的变化推断得出。例如，索赔状态可能从“已拒付”更改为“审核中”。 捕获 从理赔状态更改或显示用户已开始处理拒付的审计日志条目中推断。 事件类型 inferred | |||
| 理赔已生成 | 此活动表示系统根据捕获的费用创建了正式的索赔单或发票。这是索赔单发送给付款方或患者之前的准备步骤。 | ||
| 为何重要 跟踪索赔生成有助于隔离费用捕获与准备提交之间的延迟。这是一个可能影响整体计费及时性的关键内部步骤。 获取方式 这通常在运行计费或索赔生成批处理作业时记录。当为给定账户创建索赔文件（如 837 文件）时，系统会记录时间戳。 捕获 识别指示理赔已编译并准备提交的日志条目或状态更改。 事件类型 explicit | |||
| 理赔已重新提交 | 此事件发生在拒付索赔得到纠正并重新发回给付款方之后。这是一个与原始索赔关联的独立提交事件。 | ||
| 为何重要 这是返工循环的关键部分。衡量重新提交的时间和重新提交索赔的成功率，对于了解拒付解决流程的有效性至关重要。 获取方式 这是一个类似于初始提交的明确事件，但通常被标记为重新提交。索赔记录将显示新的提交时间戳，并可能包含重新提交代码。 捕获 捕获标记为更正或重新提交的理赔提交的时间戳。 事件类型 explicit | |||
提取指南
步骤
- 建立数据库连接：获取 Epic Clarity 数据库的只读凭据。使用标准的 SQL 客户端（如 DBeaver 或 Microsoft SQL Server Management Studio）连接到数据库服务器。
- 识别核心表：提取所需的主要表包括用于 case 信息的
HSP_ACCOUNT、用于财务事件的HSP_TRANSACTIONS、用于理赔状态的CLP_CLAIM_INFO以及用于款项过账详情的F_ARHB_TX_SET_POST_HX。您还需要连接母表（如CLARITY_EMP）以获取用户详情。 - 确定范围：在编写查询之前，先确定分析范围。定义一个特定的日期范围（通常为 3 到 6 个月），并识别您希望包含或排除的特定医院服务区域 (
SERV_AREA_ID) 或账户类别。 - 开发 SQL 查询：构建一个 SQL 查询，使用公用表表达式 (CTE) 首先筛选出符合定义范围的
HSP_ACCOUNT_ID集合。这将作为账单事件的基础样本群体。 - 合并各活动查询：针对 12 个所需活动中的每一个，编写独立的
SELECT语句从相关表中检索数据。通过连接回初始 CTE，确保您仅分析目标账户。 - 使用 UNION ALL 组合查询：使用
UNION ALL运算符将所有单个活动查询的结果合并为一个连贯的事件日志。这会将每个查询的行垂直堆叠起来。 - 映射到标准架构：在每个
SELECT语句中，为列设置别名，以匹配所需的 ProcessMind 架构：BillingEvent、ActivityName、EventTimestamp、ResponsibleUser等。对于不适用于特定活动的属性，请使用NULL。 - 执行并优化查询：在 Clarity 数据库中运行完整的查询。由于表规模巨大，这可能需要较长时间。如果遇到性能问题，请进一步缩小日期范围或在初始 CTE 中添加更具体的过滤器。
- 检查输出结果：查询完成后，检查输出的前几百行。验证所有列是否齐全、时间戳格式是否统一，以及不同的
ActivityName值是否如预期显示。 - 导出为 CSV：将 SQL 客户端中的整个结果集导出为 CSV 文件。确保文件使用 UTF-8 编码，并包含带有正确列名的标题行。
- 准备上传：在上传到 ProcessMind 之前，打开 CSV 文件确认没有格式错误。检查时间戳格式是否一致，例如
YYYY-MM-DD HH:MI:SS。文件现在已准备好进行导入。
配置
- 数据库连接：需要一个具有 Epic Clarity 数据库访问权限的只读用户账号。
- 日期范围参数：提供的查询使用了
@StartDate和@EndDate变量。必须设置这些变量来定义分析周期。建议选择 3 到 6 个月的时间范围，以平衡数据量与性能。 - 表和列映射：该查询假设使用标准的 Clarity 表名和列名。您所在机构的特定 Epic 配置或版本可能会有所不同。您可能需要相应地调整表名、列名或连接条件。
- 交易和状态代码：查询中包含占位符，例如
[Your Denial Tx Type]和[Your Collections Status Code]。您必须咨询您的 Epic 系统管理员或查看相关的母表（如ZC_TX_TYPE或ZC_ACCOUNT_STATUS），以查找适用于您实例的正确代码。 - 过滤：为了获得更好的性能和更具针对性的分析，请在初始的
BaseAccountsCTE 中添加过滤器。常见的过滤器包括用于限制医院服务区域的SERV_AREA_ID，或用于专注住院或门诊账单的ACCOUNT_CLASS_C。
a 查询示例 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;步骤
- 建立数据库连接：获取 Epic Clarity 数据库的只读凭据。使用标准的 SQL 客户端（如 DBeaver 或 Microsoft SQL Server Management Studio）连接到数据库服务器。
- 识别核心表：提取所需的主要表包括用于 case 信息的
HSP_ACCOUNT、用于财务事件的HSP_TRANSACTIONS、用于理赔状态的CLP_CLAIM_INFO以及用于款项过账详情的F_ARHB_TX_SET_POST_HX。您还需要连接母表（如CLARITY_EMP）以获取用户详情。 - 确定范围：在编写查询之前，先确定分析范围。定义一个特定的日期范围（通常为 3 到 6 个月），并识别您希望包含或排除的特定医院服务区域 (
SERV_AREA_ID) 或账户类别。 - 开发 SQL 查询：构建一个 SQL 查询，使用公用表表达式 (CTE) 首先筛选出符合定义范围的
HSP_ACCOUNT_ID集合。这将作为账单事件的基础样本群体。 - 合并各活动查询：针对 12 个所需活动中的每一个，编写独立的
SELECT语句从相关表中检索数据。通过连接回初始 CTE，确保您仅分析目标账户。 - 使用 UNION ALL 组合查询：使用
UNION ALL运算符将所有单个活动查询的结果合并为一个连贯的事件日志。这会将每个查询的行垂直堆叠起来。 - 映射到标准架构：在每个
SELECT语句中，为列设置别名，以匹配所需的 ProcessMind 架构：BillingEvent、ActivityName、EventTimestamp、ResponsibleUser等。对于不适用于特定活动的属性，请使用NULL。 - 执行并优化查询：在 Clarity 数据库中运行完整的查询。由于表规模巨大，这可能需要较长时间。如果遇到性能问题，请进一步缩小日期范围或在初始 CTE 中添加更具体的过滤器。
- 检查输出结果：查询完成后，检查输出的前几百行。验证所有列是否齐全、时间戳格式是否统一，以及不同的
ActivityName值是否如预期显示。 - 导出为 CSV：将 SQL 客户端中的整个结果集导出为 CSV 文件。确保文件使用 UTF-8 编码，并包含带有正确列名的标题行。
- 准备上传：在上传到 ProcessMind 之前，打开 CSV 文件确认没有格式错误。检查时间戳格式是否一致，例如
YYYY-MM-DD HH:MI:SS。文件现在已准备好进行导入。
配置
- 数据库连接：需要一个具有 Epic Clarity 数据库访问权限的只读用户账号。
- 日期范围参数：提供的查询使用了
@StartDate和@EndDate变量。必须设置这些变量来定义分析周期。建议选择 3 到 6 个月的时间范围，以平衡数据量与性能。 - 表和列映射：该查询假设使用标准的 Clarity 表名和列名。您所在机构的特定 Epic 配置或版本可能会有所不同。您可能需要相应地调整表名、列名或连接条件。
- 交易和状态代码：查询中包含占位符，例如
[Your Denial Tx Type]和[Your Collections Status Code]。您必须咨询您的 Epic 系统管理员或查看相关的母表（如ZC_TX_TYPE或ZC_ACCOUNT_STATUS），以查找适用于您实例的正确代码。 - 过滤：为了获得更好的性能和更具针对性的分析，请在初始的
BaseAccountsCTE 中添加过滤器。常见的过滤器包括用于限制医院服务区域的SERV_AREA_ID，或用于专注住院或门诊账单的ACCOUNT_CLASS_C。
a 查询示例 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;