매출 주기 관리 데이터 템플릿

SELECT
    c.ClaimID AS BillingEvent,
    'Service Rendered' AS ActivityName,
    c.ServiceDate AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    c.ClinicianID AS AssignedUser,
    d.DepartmentName AS BillingDepartment,
    c.TotalChargeAmount AS InvoiceAmount,
    p.PayerName AS PayerName,
    'Rendered' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [ServiceAndChargeData] c
JOIN [Departments] d ON c.DepartmentID = d.DepartmentID
JOIN [Payers] p ON c.PayerID = p.PayerID
WHERE c.ServiceDate BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    c.ClaimID AS BillingEvent,
    'Charges Captured' AS ActivityName,
    c.ChargeEntryTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    c.ChargeEntryUserID AS AssignedUser,
    d.DepartmentName AS BillingDepartment,
    c.TotalChargeAmount AS InvoiceAmount,
    p.PayerName AS PayerName,
    'Open' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [ServiceAndChargeData] c
JOIN [Departments] d ON c.DepartmentID = d.DepartmentID
JOIN [Payers] p ON c.PayerID = p.PayerID
WHERE c.ChargeEntryTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    cl.ClaimID AS BillingEvent,
    'Claim Created' AS ActivityName,
    cl.CreationTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    cl.CreatedByUserID AS AssignedUser,
    cl.BillingDepartment AS BillingDepartment,
    cl.TotalAmount AS InvoiceAmount,
    cl.PayerName AS PayerName,
    'Created' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [ClaimsData] cl
WHERE cl.CreationTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    cl.ClaimID AS BillingEvent,
    'Claim Submitted' AS ActivityName,
    cl.SubmissionTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    cl.SubmittedByUserID AS AssignedUser,
    cl.BillingDepartment AS BillingDepartment,
    cl.TotalAmount AS InvoiceAmount,
    cl.PayerName AS PayerName,
    'Submitted' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [ClaimsData] cl
WHERE cl.SubmissionTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    era.ClaimID AS BillingEvent,
    'Payer Adjudication Received' AS ActivityName,
    era.ReceivedTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    'System' AS AssignedUser,
    pa.BillingDepartment AS BillingDepartment,
    pa.InvoiceAmount AS InvoiceAmount,
    era.PayerName AS PayerName,
    'Adjudicated' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [RemittanceAdvice] era
JOIN [PatientAccounts] pa ON era.ClaimID = pa.BillingEvent
WHERE era.ReceivedTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    d.ClaimID AS BillingEvent,
    'Claim Denied' AS ActivityName,
    d.DenialTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    'System' AS AssignedUser,
    cl.BillingDepartment AS BillingDepartment,
    cl.TotalAmount AS InvoiceAmount,
    cl.PayerName AS PayerName,
    'Denied' AS InvoiceStatus,
    d.ReasonCode AS DenialReasonCode
FROM [DenialsLog] d
JOIN [ClaimsData] cl ON d.ClaimID = cl.ClaimID
WHERE d.DenialTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    dr.ClaimID AS BillingEvent,
    'Denial Rework Started' AS ActivityName,
    dr.ReworkStartTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    dr.AssignedUserID AS AssignedUser,
    cl.BillingDepartment AS BillingDepartment,
    cl.TotalAmount AS InvoiceAmount,
    cl.PayerName AS PayerName,
    'In Rework' AS InvoiceStatus,
    dr.OriginalDenialCode AS DenialReasonCode
FROM [DenialRework] dr
JOIN [ClaimsData] cl ON dr.ClaimID = cl.ClaimID
WHERE dr.ReworkStartTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    ps.PatientAccountID AS BillingEvent,
    'Patient Statement Generated' AS ActivityName,
    ps.GenerationTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    ps.GeneratedByUserID AS AssignedUser,
    pa.BillingDepartment AS BillingDepartment,
    ps.StatementBalance AS InvoiceAmount,
    'Patient' AS PayerName,
    'Patient Billed' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [PatientStatements] ps
JOIN [PatientAccounts] pa ON ps.PatientAccountID = pa.BillingEvent
WHERE ps.GenerationTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    p.AssociatedClaimID AS BillingEvent,
    'Payment Received' AS ActivityName,
    p.ReceiptTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    p.ProcessedByUserID AS AssignedUser,
    pa.BillingDepartment AS BillingDepartment,
    p.PaymentAmount AS InvoiceAmount,
    p.PayerName AS PayerName,
    'Payment Pending' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [PaymentsLog] p
JOIN [PatientAccounts] pa ON p.AssociatedClaimID = pa.BillingEvent
WHERE p.ReceiptTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    pt.ClaimID AS BillingEvent,
    'Payment Posted' AS ActivityName,
    pt.PostingTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    pt.PostedByUserID AS AssignedUser,
    pa.BillingDepartment AS BillingDepartment,
    pt.PostedAmount AS InvoiceAmount,
    pt.PayerName AS PayerName,
    'Partially Paid' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [PaymentTransactions] pt
JOIN [PatientAccounts] pa ON pt.ClaimID = pa.BillingEvent
WHERE pt.PostingTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    a.ClaimID AS BillingEvent,
    'Account Adjustment Made' AS ActivityName,
    a.AdjustmentTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    a.AdjusterID AS AssignedUser,
    pa.BillingDepartment AS BillingDepartment,
    a.AdjustmentAmount AS InvoiceAmount,
    pa.PayerName AS PayerName,
    'Adjusted' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [Adjustments] a
JOIN [PatientAccounts] pa ON a.ClaimID = pa.BillingEvent
WHERE a.AdjustmentTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    ca.PatientAccountID AS BillingEvent,
    'Collection Activity Started' AS ActivityName,
    ca.ActivityTimestamp AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    ca.AssignedAgentID AS AssignedUser,
    'Collections' AS BillingDepartment,
    pa.CurrentBalance AS InvoiceAmount,
    'Patient' AS PayerName,
    'In Collections' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [CollectionsActivity] ca
JOIN [PatientAccounts] pa ON ca.PatientAccountID = pa.BillingEvent
WHERE ca.ActivityTimestamp BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    pa.BillingEvent AS BillingEvent,
    'Account Placed in Bad Debt' AS ActivityName,
    pa.BadDebtPlacementDate AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    pa.BadDebtUserID AS AssignedUser,
    'Finance' AS BillingDepartment,
    pa.CurrentBalance AS InvoiceAmount,
    'Patient' AS PayerName,
    'Bad Debt' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [PatientAccounts] pa
WHERE pa.BadDebtPlacementDate BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    pa.BillingEvent AS BillingEvent,
    'Account Closed' AS ActivityName,
    pa.ClosureDate AS EventTime,
    'R1 RCM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    'System' AS AssignedUser,
    pa.BillingDepartment AS BillingDepartment,
    0 AS InvoiceAmount,
    pa.PayerName AS PayerName,
    'Closed' AS InvoiceStatus,
    NULL AS DenialReasonCode
FROM [PatientAccounts] pa
WHERE pa.ClosureDate BETWEEN '{StartDate}' AND '{EndDate}' AND pa.CurrentBalance = 0;