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 | |
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 | DisputeStatusType | VARCHAR(64) | Represents the current stage or step of a particular process or lifecycle, such as a claim or dispute. | ALWAYS | ||
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 | 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 | |
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 | |
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 | |
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) | Datetime assigned by the system by which a claim must be resolved in order to be compliant with applicable regulations. | 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 | |
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 | 4829 | |
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) | CONDITIONAL | 1 | ||
Metadata | CreatedBy | VARCHAR(64) | The username of the user that created the claim. | CONDITIONAL | 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 --top 2500
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 |
|
12/11/2024 | Converted timestamps from NTZ to TZ format for compatibility |