Name	ProcessingClaims
Type	View
Description	Resolved claim data including system and user action counts for evaluating actiona automation, straight-through processing, and processing time.

Fields

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value	Notes

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value
Key	TenantId	VARCHAR		ALWAYS	qvo-stg
Key	Tenant	VARCHAR		ALWAYS	Default
Key	ClientId	VARCHAR		ALWAYS	Default
Key	Client	VARCHAR		ALWAYS	Default
Claim Details	ClaimType	VARCHAR		ALWAYS	Card-Pinless
Claim Details	ClaimCategory	VARCHAR		ALWAYS	Fraud
Claim Details	ClaimReason	VARCHAR		ALWAYS	Stolen
Compliance Details	RegulatoryCoverage	VARCHAR		ALWAYS	Reg E
Claim Details	ClaimChannel	VARCHAR		ALWAYS	Online Banking
Key	ClaimId	VARCHAR		ALWAYS	2306060014C
Claim Details	CreateDate	DATE		ALWAYS	2023-09-09
Claim Details	ClaimAmount	NUMBER(		ALWAYS	123.45
Claim Details	ClaimStatus	VARCHAR		ALWAYS	RESOLVED-PAID
Claim Details	ResolvedDate	TIMESTAMP_NTZ(9)		ALWAYS	2023-09-09 21:00:00
Processing Details	SystemActions	NUMBER	Count of actions taken by the system on a claim.	ALWAYS	8
Processing Details	UserActions	NUMBER	Count of actions taken by a user on a claim.	ALWAYS	2
Processing Details	TotalActions	NUMBER	Count of all actions taken on a claim.	ALWAYS	10
Processing Details	ProcessingTime	NUMBER	Difference between Create Date and Resolved Date, in days.	ALWAYS	14

SQL

SELECT
    c.tenantid,
    t.name,
    c.clientid,
    cl.legalname,
    c.claimtype,
    c.reasontype,
    c.reason,
    (CASE
        WHEN c.rege45datetime IS NOT NULL THEN 'Reg E'
        WHEN c.rege90datetime IS NOT NULL THEN 'Reg E'
        WHEN c.regz90datetime IS NOT NULL THEN 'Reg Z'
        WHEN c.isfcra = TRUE THEN 'FCRA'
        ELSE 'No Coverage'
    END) AS RegulatoryCoverage,
    c.channelorigin,
    c.claimid,
    c.claimamount,
    c.status,
    (SELECT COUNT(*) FROM completedactions AS a WHERE a.tenantid = c.tenantid AND a.claimid = c.claimid AND a.addedbyid = 'System' AND a.flowtype NOT IN ('RequestReopenScreenFlow', 'RequestCaseWithdrawn', 'LaunchLetterHub', 'IntakeScreenFlow') AND a.flowaction != 'ResendPreviousCorrespondence') AS SystemActions,
    (SELECT COUNT(*) FROM completedactions AS a WHERE a.tenantid = c.tenantid AND a.claimid = c.claimid AND a.addedbyid != 'System' AND a.flowtype NOT IN ('RequestReopenScreenFlow', 'RequestCaseWithdrawn', 'LaunchLetterHub', 'IntakeScreenFlow') AND a.flowaction != 'ResendPreviousCorrespondence') AS UserActions,
    SystemActions + UserActions AS TotalActions,
    (CASE
        WHEN SystemActions = TotalActions then 'Processed Straight Through'
        ELSE 'Manual Processing Required'
    END) AS Processing,
    TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
    DATEDIFF(d, c.createdatetime, c.resolveddatetime) AS ProcessingTime
FROM claim AS c
JOIN tenant AS t ON c.tenantid = t.tenantid
JOIN client AS cl ON c.tenantid = cl.tenantid AND c.clientid = cl.clientid
WHERE
    c.status IN ('Resolved-Paid', 'Resolved-Denied')
ORDER BY
    c.resolveddatetime DESC

Change Log

Date	Change Summary

Date	Change Summary
12/19/2024	New

QFD Knowledgebase

Claim Processing View

Fields

SQL

Change Log