Votre Modèle de Données de Gestion du cycle de revenus (RCM)

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;

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;