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) | 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 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 |