Name	Claims
Type	View
Description	The Claims view provides most of the same information as the Claim table, though more consolidated, as well as additional columns that are useful in the context of claims, such as dispute count, dispute amount, recovered amount, etc.

Fields

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value	Notes
Key	TenantId	VARCHAR(32)	Unique ID associated with each Quavo client.	ALWAYS	qvo-stg
Key	Tenant	VARCHAR(64)	A tenant is a Quavo customer that uses QFD. Each tenant is assigned a unique Tenant ID.	ALWAYS	Default
Key	ClientId	VARCHAR(64)	A unique ID representing a single client.	ALWAYS	Default
Key	Client	VARCHAR(64)	A client is a Quavo customer that uses QFD through a relationship with a Quavo tenant. Each client is assigned a unique Client ID.	ALWAYS	Default
Claim Details	ClaimType	VARCHAR(64)	See Transaction Type.	CONDITIONAL	Card-Pinless
Claim Details	ClaimCategory	VARCHAR(64)	General categorization of the claim. For a more precise description, see Claim Reason.	CONDITIONAL	Fraud
Claim Details	ClaimReason	VARCHAR(64)	Identifies the specific reason associated with the claim.	CONDITIONAL	Stolen
Compliance Details	RegulatoryCoverage	VARCHAR	Identifies the specific reason associated with the claim.	CONDITIONAL	Reg E
Claim Details	ClaimChannel	VARCHAR(32)	The Claim Channel defines the intake channel where the claim originated. This channel can include various avenues such as online banking, back office users, contact centers, or external 3rd party contact centers.	ALWAYS	Online Banking
Key	ClaimId	VARCHAR(32)	Unique ID associated with a claim. Format: YYMMDD<Ordinal Number>"C" Example: 2405230003C	ALWAYS	2306060014C
Claim Details	CreateDate	DATE	The datetime that a record was created.	CONDITIONAL	2023-09-09
Claim Details	CustomerContactDate	DATE	Represents the date that the claim was received from the accountholder.	CONDITIONAL	2023-09-09
Claim Details	ClaimAmount	NUMBER(20,2)	The sum dispute amount of all posted disputes on a claim.	CONDITIONAL	123.45
Claim Details	ClaimState	VARCHAR	A summarized version of Claim Status. Abandoned and withdrawn claims have a value of "Other".	ALWAYS	OPEN
Claim Details	ClaimStatus	VARCHAR(64)	Represents the current stage or step of a particular process or lifecycle, such as a claim or dispute.	ALWAYS	RESOLVED-PAID
Card Details	BIN	VARCHAR(8)	Bank Identification Number (BIN) is the first 4-8 digits of a payment card that identifies the issuing institution.	CONDITIONAL	412356, 41235678
Card Details	CardType	VARCHAR(16)	Bank Identification Number (BIN) is the first 4-8 digits of a payment card that identifies the issuing institution.	CONDITIONAL	Debit
Claim Details	Decision	VARCHAR(64)	"Paid" or "Denied". If one, or more, disputes on a claim are paid, the claim decision is "Paid".	CONDITIONAL	Paid, Denied
Claim Details	DiscoveryDate	DATE	The date provided during intake on a lost or stolen claim that represents the date the claimant discovered that the card had been lost or stolen.	CONDITIONAL	2023-09-09
Claim Details	InvestigationCompleteDateTime	TIMESTAMP_NTZ(9)	Date and/or datetime that a Pay or Deny decision is rendered for all disputes on a claim.	CONDITIONAL	2023-09-09 21:00:00
Compliance Details	InvestigationDate	TIMESTAMP_NTZ(9)	Date and/or datetime that a Pay or Deny decision is rendered for all disputes on a claim.	CONDITIONAL	2023-09-09 21:00:00
Compliance Details	ResolutionDeadline	TIMESTAMP_NTZ(9)	Date and/or datetime that a Pay or Deny decision is rendered for all disputes on a claim.	CONDITIONAL	2023-09-09 21:00:00
Claim Details	ResolvedDate	TIMESTAMP_NTZ(9)	Datetime that a claim or dispute is set to a "Resolved" status.. Also referred to as RESOLVEDDATETIME.	CONDITIONAL	2023-09-09 21:00:00
Risk Details	PotentialAbuse	BOOLEAN	A specified number of claims submitted by a single customer/member within an established timeframe. The output of this property is a boolean (TRUE/FALSE).	CONDITIONAL	FALSE
Risk Details	EmployeeClaim	BOOLEAN	A specified number of claims submitted by a single customer/member within an established timeframe. The output of this property is a boolean (TRUE/FALSE).	CONDITIONAL	false
Claim Details	PCDecision	VARCHAR(16)	The outcome of the initial interaction decision determined at claim submission. Possible values include AutoPay, Deny, Grant and Review.	CONDITIONAL	Grant
Claim Details	PCDate	TIMESTAMP_NTZ(9)	Datetime when provisional credit was granted on a claim.	CONDITIONAL	2023-09-09 21:00:00
Claim Details	Reclassifications	NUMBER(3,0)	Number of times the claims has been reclassified.	CONDITIONAL	1
Claim Details	RequiredDocuments	NUMBER(3,0)	of documents required from the customer/member for claim processing.	CONDITIONAL	1
Metadata	CreatedBy	VARCHAR(64)	The username of the user that created the claim.	CONDITIONAL	devan.robertson@quavo.com	Available in 24.01.03.
Dispute Details	DisputeCount	NUMBER(3,0)	Count of disputes associated with a specific claim. Includes pending and posted disputes. Disputed transactions that were pending when the claim was submitted and have since posted are only counted once.	CONDITIONAL	2
Dispute Details	DisputeAmount	NUMBER(20,2)	The current amount in dispute, accounting for any recoveries or adjustments.	CONDITIONAL	123.59
Dispute Details	MerchantCreditAmount	NUMBER(20,2)	Amount of all applied merchant credits.	CONDITIONAL	123.59
Dispute Details	RecoveredAmount	NUMBER(20,2)	Total amount of all successful partial and full recovery attempts. Does not include merchant credits posted directly to the account. Not considered final until the dispute is in a resolved status.	CONDITIONAL	123.59
Dispute Details	LossAmount	NUMBER(20,2)	The balance of the Loss accounting collection. Also referred to as LOSSTOTAL.	CONDITIONAL	123.59

SQL

For greater transparency, and to provide clients with a “jumping off” point for their own queries, the SQL used to generated this view is provided below.

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,
    TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
    c.customercontactdate,
    c.totalclaimamount,
    cs.state,
    c.status,
    c.cardbin,
    c.cardtype,
    c.decision,
    c.customerdiscoverydate,
    TO_TIMESTAMP_TZ(c.investigationcompletedatetime::varchar || ' +0000'),
    (CASE
        WHEN regethresholddatetime IS NOT NULL THEN regethresholddatetime
        ELSE regz90thresholddatetime
    END) AS ResolutionDeadline,
    TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
    c.ispotentialabuse,
    c.isemployeeclaim,
    c.pcdecision,
    TO_TIMESTAMP_TZ(c.provisionalcreditdatetime::varchar || ' +0000'),
    c.reclassificationcount,
    c.requireddocumentcount,
    (CASE
        WHEN c.createdbyuserid= 'System' or c.createdbyuserid is NULL THEN 'System'  -- QPS-9986
        ELSE CONCAT(LEFT(u.firstname, 1), '. ', u.lastname)
    END) AS CreatedBy,
    (SELECT COUNT(*) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeCount,
    (SELECT SUM(d.disputeamount) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeAmount,
    IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = c.tenantid AND mc.claimid = c.claimid), 0) AS MerchantCreditAmount,
    IFNULL((SELECT SUM(d.merchanttotal) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND (d.status = 'Resolved-Paid' OR d.status = 'Resolved-Denied')), 0) * -1 AS RecoveredAmount,
    IFNULL((SELECT SUM(d.losstotal) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid), 0) * -1 AS LossAmount,
    to_date(t.golivedatetime) as TenantProductionDate
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
LEFT JOIN claim_state AS cs ON cs.status = c.status
LEFT JOIN userprofile as u ON u.tenantid = c.tenantid AND u.userid = c.createdbyuserid
ORDER BY
    c.createdatetime DESC

Change Log

Date	Change Summary
11/26/2024	Added SQL
12/11/2024	Converted timestamps from NTZ to TZ format for compatibility

Claims View

Fields

SQL

Change Log