Votre modèle de données pour la gestion du cycle de revenus

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;

WITH
service_rendered AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Service Rendered' AS ActivityName,
        CAST(s.[Service rendered timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(s.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(s.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(s.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(s.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your service source table] s
        ON s.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE s.[Service rendered timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND s.[Service rendered timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Your company code filter]
),
charges_captured AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Charges Captured' AS ActivityName,
        CAST(t.[Charge captured timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(t.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(t.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(t.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(t.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN AR_PB_TRANSACTIONS t
        ON t.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE t.[Charge captured timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND t.[Charge captured timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND t.[Charge transaction filter]
      AND [Your company code filter]
),
claim_generated AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Claim Generated' AS ActivityName,
        CAST(c.[Claim generated timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(c.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(c.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(c.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(c.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your claim source table] c
        ON c.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE c.[Claim generated timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND c.[Claim generated timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND c.[Claim generated status filter]
      AND [Your company code filter]
),
claim_submitted AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Claim Submitted to Payer' AS ActivityName,
        CAST(c.[Claim submitted timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(c.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(c.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(c.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(c.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your claim source table] c
        ON c.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE c.[Claim submitted timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND c.[Claim submitted timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND c.[Claim submitted status filter]
      AND [Your company code filter]
),
claim_denied AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Claim Denied by Payer' AS ActivityName,
        CAST(d.[Denial timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(d.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(d.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(d.[Denial reason code column] AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(d.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(d.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your denial source table] d
        ON d.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE d.[Denial timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND d.[Denial timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND d.[Denial status filter]
      AND [Your company code filter]
),
denial_follow_up AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Denial Follow-Up Initiated' AS ActivityName,
        CAST(f.[Follow-up timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(f.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(f.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(f.[Denial reason code column] AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(f.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(f.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your follow-up source table] f
        ON f.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE f.[Follow-up timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND f.[Follow-up timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND f.[Follow-up status filter]
      AND [Your company code filter]
),
claim_resubmitted AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Claim Resubmitted' AS ActivityName,
        CAST(c.[Claim resubmitted timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(c.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(c.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(c.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(c.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your claim source table] c
        ON c.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE c.[Claim resubmitted timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND c.[Claim resubmitted timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND c.[Claim resubmitted status filter]
      AND [Your company code filter]
),
payment_received AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Payment Received' AS ActivityName,
        CAST(p.[Payment received timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(p.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(p.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(p.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(p.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your payment source table] p
        ON p.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE p.[Payment received timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND p.[Payment received timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND p.[Payment received status filter]
      AND [Your company code filter]
),
payment_posted AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Payment Posted to Account' AS ActivityName,
        CAST(p.[Payment posted timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(p.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(p.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(p.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(p.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your payment source table] p
        ON p.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE p.[Payment posted timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND p.[Payment posted timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND p.[Payment posted status filter]
      AND [Your company code filter]
),
account_adjustment AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Account Adjustment Made' AS ActivityName,
        CAST(x.[Adjustment timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(x.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(x.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(x.[Adjustment reason column] AS VARCHAR(100)) AS AdjustmentReason,
        CAST(x.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(x.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your adjustment source table] x
        ON x.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE x.[Adjustment timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND x.[Adjustment timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND x.[Adjustment transaction filter]
      AND [Your company code filter]
),
balance_collections AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Balance Sent to Collections' AS ActivityName,
        CAST(k.[Collections timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(k.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(k.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(k.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(k.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your collections source table] k
        ON k.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE k.[Collections timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND k.[Collections timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND k.[Collections status filter]
      AND [Your company code filter]
),
account_closed AS (
    SELECT
        CAST(a.HSP_ACCOUNT_ID AS VARCHAR(100)) AS BillingEvent,
        'Account Closed' AS ActivityName,
        CAST(z.[Account closed timestamp column] AS TIMESTAMP) AS EventTimestamp,
        CAST(z.[Responsible user column] AS VARCHAR(100)) AS ResponsibleUser,
        CAST(z.[Billing department column] AS VARCHAR(100)) AS BillingDepartment,
        CAST(NULL AS VARCHAR(100)) AS DenialReasonCode,
        CAST(NULL AS VARCHAR(100)) AS AdjustmentReason,
        CAST(z.[Outstanding balance column] AS DECIMAL(18,2)) AS OutstandingBalance,
        CAST(z.[Service type column] AS VARCHAR(200)) AS ServiceType
    FROM HSP_ACCOUNT a
    INNER JOIN [Your account closure source table] z
        ON z.[Billing event join column] = a.HSP_ACCOUNT_ID
    WHERE z.[Account closed timestamp column] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND z.[Account closed timestamp column] < CAST('[End date parameter]' AS TIMESTAMP)
      AND z.[Account closed status filter]
      AND [Your company code filter]
)
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM service_rendered
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM charges_captured
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM claim_generated
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM claim_submitted
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM claim_denied
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM denial_follow_up
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM claim_resubmitted
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM payment_received
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM payment_posted
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM account_adjustment
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM balance_collections
UNION ALL
SELECT BillingEvent, ActivityName, EventTimestamp, ResponsibleUser, BillingDepartment, DenialReasonCode, AdjustmentReason, OutstandingBalance, ServiceType FROM account_closed
ORDER BY BillingEvent, EventTimestamp, ActivityName;