Name	ProcessingDisputes
Type	View
Description	Resolved dispute 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
Key	DisputeId	VARCHAR		ALWAYS	2306060014D
Claim Details	CreateDate	DATE		ALWAYS	2023-09-09
Claim Details	DisputeAmount	NUMBER		ALWAYS	123.45
Claim Details	DisputeStatus	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
    d.tenantid,
    t.name,
    d.clientid,
    cl.commonname,
    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,
    d.acquirernetwork,
    c.channelorigin,
    d.claimid,
    d.disputeid,
    d.disputeamount,
    d.status,
    (SELECT COUNT(*) FROM completedactions AS a WHERE a.tenantid = d.tenantid AND a.disputeid = d.disputeid AND a.eventdatetime >= d.createdatetime AND a.addedbyid = 'System') AS SystemActions,
    (SELECT COUNT(*) FROM completedactions AS a WHERE a.tenantid = d.tenantid AND a.disputeid = d.disputeid AND a.eventdatetime >= d.createdatetime AND a.addedbyid != 'System') AS UserActions,
    SystemActions + UserActions AS TotalActions,
    (CASE
        WHEN SystemActions = TotalActions then 'Processed Straight Through'
        ELSE 'Manual Processing Required'
    END) AS Processing,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    DATEDIFF(d, d.createdatetime, d.resolveddatetime) AS ProcessingTime
FROM dispute AS d
JOIN tenant AS t
    on d.tenantid = t.tenantid
JOIN claim AS c
    on d.tenantid = c.tenantid and d.claimid = c.claimid
JOIN client AS cl
    on d.tenantid = cl.tenantid and d.clientid = cl.clientid
WHERE
    d.status IN ('Resolved-Paid', 'Resolved-Denied')

Change Log

Date	Change Summary

Date	Change Summary
12/19/2024	New

QFD Knowledgebase

Dispute Processing View

Fields

SQL

Change Log