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
d.tenantid,
t.name,
d.clientid,
cl.commonname,
c.claimtype,
c.reasontype,
CAMEL_CASE_TO_STRING(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,
c.cardtype,
c.cardbin,
d.transactiontype,
d.claimid,
d.disputeid,
cs.state,
CAMEL_CASE_TO_STRING(d.status),
(CASE
WHEN d.status LIKE CONCAT('%','-','%') THEN SUBSTRING(d.status, 1, CHARINDEX('-', d.status)-1 )
ELSE d.status
END) as statustype,
d.decision,
CAMEL_CASE_TO_STRING(d.denyreason) AS DenyReason,
d.transactiondate,
c.customercontactdate,
d.createdatetime,
to_date(d.investigationcompletedatetime) AS InvestigationDate,
to_date(d.resolveddatetime) AS ResolvedDate,
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,
(CASE
WHEN d.status ILIKE '%resolved%' THEN d.merchanttotal * -1
ELSE 0
END) AS RecoveredAmount,
d.losstotal * -1 AS LossAmount,
(CASE
WHEN d.status ILIKE '%resolved%' AND d.originaldisputeamount IS NOT NULL THEN d.originaldisputeamount - d.accountholdertotal - MerchantCreditAmount
WHEN d.status ILIKE '%resolved%' THEN d.disputeamount - d.accountholdertotal - MerchantCreditAmount
ELSE 0
END) AS DeniedAmount,
d.isreopened
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
LEFT JOIN claim_state AS cs ON cs.status = c.status
Change Log
Date | Change Summary |
|---|---|
11/26/2024 |
|
12/11/2024 | Converted timestamps from NTZ to TZ format for compatibility |