Name	DisputesResolved
Type	View
Description	Provides most of the same information as the Dispute table, though more consolidated, as well as additional columns from various other tables that are relevant in the context of a disputed transaction. Only includes disputes with a resolved status.

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value
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
Transaction Details	Network	VARCHAR(64)	A network is a company that provides the communication system between a merchant and a card issuer in order to process transactions. For more details, see Acquirer Networks.	CONDITIONAL	Mastercard, Visa
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
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
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
Transaction Details	TransactionType	VARCHAR	The type of transaction, such as ACH, BillPay, Card, Check, RTP, Wire, Zelle, etc.	ALWAYS	ATM
Key	ClaimId	VARCHAR(32)	Unique ID associated with a claim. Format: YYMMDD<Ordinal Number>"C" Example: 2405230003C	ALWAYS	2306060014C
Key	DisputeId	VARCHAR(32)	Unique ID associated with a disputed transaction. Format: YYMMDD#####"D"	ALWAYS	2306060014D
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
Dispute Details	DisputeStatus	VARCHAR(64)	Represents the current stage or step of a particular process or lifecycle, such as a claim or dispute.	ALWAYS	RESOLVED-PAID
Dispute 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
Dispute Details	DenyReason	VARCHAR	The reason associated with a denial action. See Denied for a list of supported reasons.	CONDITIONAL	No Error
Transaction Details	TransactionDate	DATE	The date of the transaction, as provided by the system of record. This generally represents the Posting Date.	ALWAYS	2023-09-09
Claim Details	ContactDate	DATE	Represents the date that the claim was received from the accountholder.	ALWAYS	2023-09-09
Dispute Details	CreateDate	DATE	The datetime that a record was created.	ALWAYS	2023-09-09
Dispute 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
Dispute 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
Transaction Details	AuthorizationStatus	VARCHAR(32)	Represents the posting state of a transaction. Values include "Authorization" and "Posted".	ALWAYS	Pending, Posted
Transaction Details	MerchantCategoryCode	VARCHAR(6)	Also referred to as "MCC", this 4-digit code represents the type of goods or services a business offers.	CONDITIONAL	8999
Transaction Details	Merchant		Name of the business providing goods or services, as provided by the system of record.	ALWAYS	PAYPAL
Transaction Details	POSEntryMode		Code representing how the merchant entered the transaction at the point of sale (POS).	CONDITIONAL	81
Transaction Details	TransactionDescription		A description of the transaction as provided by the system of record. Typically, this will match what the customer/member sees on their account statement.	ALWAYS	PAYPAL *CASHAPP0777701 402-935-7733 CA
Dispute Details	DisputeAmount	NUMBER(20,2)	The current amount in dispute, accounting for any recoveries or adjustments.	CONDITIONAL	123.59
Transaction Details	TransactionAmount	NUMBER(20,2)	Posted amount of the transaction. May differ from /wiki/spaces/ARIA/pages/48269484.	ALWAYS	123.59
Dispute Details	AccoundholderAmount	NUMBER(20,2)	Amount of all accountholder 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
Dispute Details	DeniedAmount	NUMBER(20,2)	Total amount denied.	CONDITIONAL	123.59
Dispute Details	RecapturedAmount	NUMBER(20,2)	Represents the total dollar amount of successful loss avoidance, including recoveries, merchant credits, and denials.	CONDITIONAL	123.59
Dispute Details	Reopened	BOOLEAN	Indicates whether the dispute has been reopened.	ALWAYS	TRUE, FALSE

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
    d.tenantid,
    t.name,
    d.clientid,
    cl.commonname,
    c.claimtype,
    c.reasontype,
    CAMEL_CASE_TO_STRING(c.reason) AS "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 "Regulatory Coverage",
    (CASE
        WHEN d.acquirernetwork = 'MasterCard' THEN 'Mastercard'
        WHEN d.acquirernetwork = 'Debit Mastercard' THEN 'Mastercard Debit'
        WHEN d.acquirernetwork IS NULL THEN 'Unknown'
        ELSE d.acquirernetwork
    END) AS "Network",
    c.channelorigin,
    c.cardtype,
    c.cardbin,
    d.claimid,
    d.disputeid,
    c.status,
    d.status,
    d.decision,
    CAMEL_CASE_TO_STRING(d.denyreason) AS "DenyReason",
    c.customercontactdate,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.investigationcompletedatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    (CASE
        WHEN d.status = 'Resolved-Paid' THEN 'Paid'
        WHEN d.status = 'Resolved-Denied' THEN 'Denied'
        WHEN d.status = 'Resolved-NoPosting' THEN 'Never Posted'
    END) as "Resolution",
    DATEDIFF(d, d.createdatetime, d.resolveddatetime) AS "ResolutionTimeDays",
    DATEDIFF(d, c.customercontactdate, to_date(d.investigationcompletedatetime)) AS CustomerResolutionTimeDays,
    d.authorizationstatus,
    d.merchantcategorycode,
    d.merchantname,
    d.posentrymode,
    d.description,
    d.disputeamount,
    d.amount,
    d.accountholdertotal,
    IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid), 0) AS "MerchantCreditAmount",
    IFNULL((CASE
        WHEN d.transactiontype = 'ACH' THEN (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid AND r.actionname = 'Recovery Pursued') - (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid AND r.actionname = 'Recovery Cancelled')
        ELSE (SELECT max(r.cycle) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid)
    END), 0) AS "Recovery Attempts",
    d.merchanttotal * -1 AS "RecoveredAmount",
    d.losstotal * -1 AS "LossAmount",
    d.disputeamount - d.accountholdertotal - "MerchantCreditAmount" AS "DeniedAmount",
    "MerchantCreditAmount" + "RecoveredAmount" + "DeniedAmount" AS "RecapturedAmount",
    (CASE
        WHEN d.isreopened = TRUE THEN TRUE
        ELSE FALSE
    END) AS "Reopened"
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', 'Resolved-NoPosting')

Change Log

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

Disputes Resolved View

SQL

Change Log