보험금 청구 처리 데이터 템플릿

-- Common Table Expression (CTE) to fetch core claim attributes
-- This improves readability and performance by querying base tables once.
WITH ClaimBase AS (
    SELECT 
        DC.ClaimId,
        DC.ClaimNumber,
        DC.ClaimType,
        DC.Severity AS ClaimSeverity,
        DC.CurrentStatus AS ClaimStatus,
        FC.LossAmount,
        DA.AdjusterName AS AssignedAdjuster,
        DD.DepartmentName AS Department,
        -- Timestamps for various events
        FC.FNOLReportedDate AS ClaimSubmittedTime,
        FC.ClaimRegisteredDate AS ClaimRegisteredTime,
        FC.AdjusterAssignmentDate AS AdjusterAssignedTime,
        FC.PaymentIssuedDate AS PaymentIssuedTime,
        FC.ClaimClosedDate AS ClaimClosedTime
    FROM 
        [DataHubSchema].[DimClaim] AS DC
    LEFT JOIN 
        [DataHubSchema].[FactClaim] AS FC ON DC.ClaimKey = FC.ClaimKey
    LEFT JOIN 
        [DataHubSchema].[DimAdjuster] AS DA ON FC.AssignedAdjusterKey = DA.AdjusterKey
    LEFT JOIN
        [DataHubSchema].[DimDepartment] AS DD ON FC.DepartmentKey = DD.DepartmentKey
    WHERE 
        FC.FNOLReportedDate BETWEEN @StartDate AND @EndDate
        /* AND DC.LineOfBusiness IN ('[Your_LOB_Filter]') */ -- Optional: Uncomment to filter by Line of Business
)
-- 1. Claim Submitted
SELECT
    cb.ClaimId,
    'Claim Submitted' AS ActivityName,
    cb.ClaimSubmittedTime AS EventTime,
    cb.AssignedAdjuster,
    cb.Department,
    cb.ClaimType,
    cb.ClaimSeverity,
    'Submitted' AS ClaimStatus, -- Status at the time of this event
    cb.LossAmount
FROM
    ClaimBase cb
WHERE
    cb.ClaimSubmittedTime IS NOT NULL

UNION ALL

-- 2. Claim Registered
SELECT
    cb.ClaimId,
    'Claim Registered' AS ActivityName,
    cb.ClaimRegisteredTime AS EventTime,
    cb.AssignedAdjuster,
    cb.Department,
    cb.ClaimType,
    cb.ClaimSeverity,
    'Registered' AS ClaimStatus,
    cb.LossAmount
FROM
    ClaimBase cb
WHERE
    cb.ClaimRegisteredTime IS NOT NULL

UNION ALL

-- 3. Adjuster Assigned
SELECT
    cb.ClaimId,
    'Adjuster Assigned' AS ActivityName,
    cb.AdjusterAssignedTime AS EventTime,
    cb.AssignedAdjuster,
    cb.Department,
    cb.ClaimType,
    cb.ClaimSeverity,
    'Assigned' AS ClaimStatus,
    cb.LossAmount
FROM
    ClaimBase cb
WHERE
    cb.AdjusterAssignedTime IS NOT NULL

UNION ALL

-- 4. Initial Review Completed (Inferred from status change)
SELECT
    cb.ClaimId,
    'Initial Review Completed' AS ActivityName,
    sh.StatusSetDate AS EventTime,
    cb.AssignedAdjuster,
    cb.Department,
    cb.ClaimType,
    cb.ClaimSeverity,
    sh.NewStatus AS ClaimStatus,
    cb.LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[DimStatusHistory] sh ON cb.ClaimId = sh.ClaimId
WHERE 
    sh.PreviousStatus IN ('Assigned', 'Registered') AND sh.NewStatus IN ('Under Review', 'Investigation')
    AND sh.StatusSetDate = (SELECT MIN(s2.StatusSetDate) FROM [DataHubSchema].[DimStatusHistory] s2 WHERE s2.ClaimId = cb.ClaimId AND s2.NewStatus IN ('Under Review', 'Investigation'))

UNION ALL

-- 5. Additional Information Requested
SELECT 
    cb.ClaimId, 
    'Additional Information Requested' AS ActivityName, 
    fct.TransactionDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    cb.ClaimStatus,
    cb.LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[FactClaimTransaction] fct ON cb.ClaimId = fct.ClaimId
WHERE 
    fct.TransactionType = 'InformationRequestSent'

UNION ALL

-- 6. Additional Information Received
SELECT 
    cb.ClaimId, 
    'Additional Information Received' AS ActivityName, 
    fct.TransactionDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    cb.ClaimStatus,
    cb.LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[FactClaimTransaction] fct ON cb.ClaimId = fct.ClaimId
WHERE 
    fct.TransactionType = 'InformationResponseReceived'

UNION ALL

-- 7. Investigation Started (Inferred from status change)
SELECT 
    cb.ClaimId, 
    'Investigation Started' AS ActivityName, 
    sh.StatusSetDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    sh.NewStatus AS ClaimStatus,
    cb.LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[DimStatusHistory] sh ON cb.ClaimId = sh.ClaimId
WHERE 
    sh.NewStatus = 'Under Investigation'
    AND sh.StatusSetDate = (SELECT MIN(s2.StatusSetDate) FROM [DataHubSchema].[DimStatusHistory] s2 WHERE s2.ClaimId = cb.ClaimId AND s2.NewStatus = 'Under Investigation')

UNION ALL

-- 8. Investigation Completed (Inferred from status change)
SELECT 
    cb.ClaimId, 
    'Investigation Completed' AS ActivityName, 
    sh.StatusSetDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    sh.NewStatus AS ClaimStatus,
    cb.LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[DimStatusHistory] sh ON cb.ClaimId = sh.ClaimId
WHERE 
    sh.PreviousStatus = 'Under Investigation' AND sh.NewStatus = 'Pending Decision'
    AND sh.StatusSetDate = (SELECT MIN(s2.StatusSetDate) FROM [DataHubSchema].[DimStatusHistory] s2 WHERE s2.ClaimId = cb.ClaimId AND s2.PreviousStatus = 'Under Investigation' AND s2.NewStatus = 'Pending Decision')

UNION ALL

-- 9. Loss Assessed (Reserve Set/Updated)
SELECT 
    cb.ClaimId, 
    'Loss Assessed' AS ActivityName, 
    fct.TransactionDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    cb.ClaimStatus,
    fct.TransactionAmount AS LossAmount -- Use transaction amount for this event
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[FactClaimTransaction] fct ON cb.ClaimId = fct.ClaimId
WHERE 
    fct.TransactionType = 'ReserveSet'

UNION ALL

-- 10. Claim Decision Made (Inferred from status change)
SELECT 
    cb.ClaimId, 
    'Claim Decision Made' AS ActivityName, 
    sh.StatusSetDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    sh.NewStatus AS ClaimStatus,
    cb.LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[DimStatusHistory] sh ON cb.ClaimId = sh.ClaimId
WHERE 
    sh.NewStatus IN ('Approved', 'Partially Approved', 'Denied')
    AND sh.StatusSetDate = (SELECT MIN(s2.StatusSetDate) FROM [DataHubSchema].[DimStatusHistory] s2 WHERE s2.ClaimId = cb.ClaimId AND s2.NewStatus IN ('Approved', 'Partially Approved', 'Denied'))

UNION ALL

-- 11. Settlement Calculated
SELECT 
    cb.ClaimId, 
    'Settlement Calculated' AS ActivityName, 
    fct.TransactionDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    cb.ClaimStatus,
    fct.TransactionAmount AS LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[FactClaimTransaction] fct ON cb.ClaimId = fct.ClaimId
WHERE 
    fct.TransactionType = 'SettlementCalculated'

UNION ALL

-- 12. Payment Authorized
SELECT 
    cb.ClaimId, 
    'Payment Authorized' AS ActivityName, 
    fct.TransactionDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    cb.ClaimStatus,
    fct.TransactionAmount AS LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[FactClaimTransaction] fct ON cb.ClaimId = fct.ClaimId
WHERE 
    fct.TransactionType = 'PaymentAuthorized'

UNION ALL

-- 13. Payment Issued
SELECT
    cb.ClaimId,
    'Payment Issued' AS ActivityName,
    cb.PaymentIssuedTime AS EventTime,
    cb.AssignedAdjuster,
    cb.Department,
    cb.ClaimType,
    cb.ClaimSeverity,
    'PaymentIssued' AS ClaimStatus,
    cb.LossAmount
FROM
    ClaimBase cb
WHERE
    cb.PaymentIssuedTime IS NOT NULL

UNION ALL

-- 14. Claim Denied
SELECT 
    cb.ClaimId, 
    'Claim Denied' AS ActivityName, 
    sh.StatusSetDate AS EventTime,
    cb.AssignedAdjuster, 
    cb.Department, 
    cb.ClaimType, 
    cb.ClaimSeverity, 
    'Denied' AS ClaimStatus,
    cb.LossAmount
FROM 
    ClaimBase cb
JOIN [DataHubSchema].[DimStatusHistory] sh ON cb.ClaimId = sh.ClaimId
WHERE 
    sh.NewStatus = 'Denied'

UNION ALL

-- 15. Claim Closed
SELECT
    cb.ClaimId,
    'Claim Closed' AS ActivityName,
    cb.ClaimClosedTime AS EventTime,
    cb.AssignedAdjuster,
    cb.Department,
    cb.ClaimType,
    cb.ClaimSeverity,
    'Closed' AS ClaimStatus,
    cb.LossAmount
FROM
    ClaimBase cb
WHERE
    cb.ClaimClosedTime IS NOT NULL;