Disputes View
- Devan Robertson
Name | Disputes |
|---|---|
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. |
Version Availability | 24.02 |
Data Category | Event Field/Column Name | Data Type | Description | Applicability | Sample Value | Notes |
|---|---|---|---|---|---|---|
Key | TenantId | VARCHAR(32) | ALWAYS | qvo-stg |
| |
Key | Tenant | VARCHAR(64) |
| ALWAYS | Default |
|
Key | ClientId | VARCHAR(64) | ALWAYS | Default |
| |
Key | Client | VARCHAR(64) |
| ALWAYS | Default |
|
Claim Details | ClaimType | VARCHAR(64) | CONDITIONAL | Card-Pinless |
| |
Claim Details | ClaimCategory | VARCHAR(64) | CONDITIONAL | Fraud |
| |
Claim Details | ClaimReason | VARCHAR(64) | CONDITIONAL | Stolen |
| |
Compliance Details | RegulatoryCoverage | VARCHAR |
| CONDITIONAL | Reg E |
|
Transaction Details | Network | VARCHAR(64) | CONDITIONAL | Mastercard, Visa |
| |
Claim Details | ClaimChannel | VARCHAR(32) |
| ALWAYS | Online Banking |
|
Card Details | CardType | VARCHAR(16) |
| CONDITIONAL | Debit |
|
Card Details | BIN | VARCHAR(8) | CONDITIONAL | 412356, 41235678 |
| |
Transaction Details | TransactionType | VARCHAR | ALWAYS | ATM |
| |
Key | ClaimId | VARCHAR(32) | ALWAYS | 2306060014C |
| |
Key | DisputeId | VARCHAR(32) | ALWAYS | 2306060014D |
| |
Claim Details | ClaimStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
| |
Dispute Details | DisputeStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
| |
Dispute Details | DisputeStatusType | VARCHAR(64) | ALWAYS |
|
| |
Dispute Details | Decision | VARCHAR(64) | CONDITIONAL | Paid, Denied |
| |
Dispute Details | DenyReason | VARCHAR | CONDITIONAL | No Error |
| |
Transaction Details | TransactionDate | DATE | ALWAYS | 2023-09-09 |
| |
Claim Details | ContactDate | DATE | ALWAYS | 2023-09-09 |
| |
Dispute Details | CreateDate | DATE | ALWAYS | 2023-09-09 |
| |
Dispute Details | InvestigationDate | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 |
| |
Dispute Details | ResolvedDate | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 |
| |
Transaction Details | AuthorizationStatus | VARCHAR(32) | ALWAYS | Pending, Posted |
| |
Transaction Details | MerchantCategoryCode | VARCHAR(6) | CONDITIONAL | 8999 |
| |
Transaction Details | Merchant |
| ALWAYS | PAYPAL |
| |
Transaction Details | POSEntryMode |
|
| CONDITIONAL | 81 |
|
Transaction Details | TransactionDescription |
|
| ALWAYS | PAYPAL *CASHAPP0777701 402-935-7733 CA |
|
Dispute Details | DisputeAmount | NUMBER(20,2) |
| CONDITIONAL | 123.59 |
|
Transaction Details | TransactionAmount | NUMBER(20,2) | ALWAYS | 123.59 |
| |
Dispute Details | AccoundholderAmount | NUMBER(20,2) | Amount of all accountholder adjustments. | CONDITIONAL | 123.59 |
|
Dispute Details | MerchantCreditAmount | NUMBER(20,2) |
| CONDITIONAL | 123.59 |
|
Dispute Details | RecoveredAmount | NUMBER(20,2) |
| CONDITIONAL | 123.59 |
|
Dispute Details | LossAmount | NUMBER(20,2) |
| CONDITIONAL | 123.59 |
|
Dispute Details | DeniedAmount | NUMBER(20,2) | Total amount denied. | 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,
(CASE
when d.acquirernetwork = 'MasterCard' THEN 'Mastercard'
when d.acquirernetwork = 'VISA' THEN 'Visa'
else d.acquirernetwork
END),
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,
TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(d.investigationcompletedatetime::varchar || ' +0000') AS InvestigationDate,
TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000') AS ResolvedDate,
d.authorizationstatus,
d.merchantcategorycode,
d.merchantname,
d.posentrymode,
d.description,
d.disputeamount,
d.amount,
d.accountholdertotal,
d.appliedcreditstotal,
(CASE
WHEN d.status ILIKE '%resolved%' AND d.accountholdertotal < 0 THEN d.merchanttotal--"Thank you"/unduly enriched claim
WHEN d.status ILIKE '%resolved%' THEN d.merchanttotal * -1
END) RecoveredAmount,
d.losstotal * -1 AS LossAmount,
(CASE
WHEN d.status ILIKE '%resolved%' AND d.originaldisputeamount IS NOT NULL AND d.accountholdertotal < 0 THEN d.originaldisputeamount + d.accountholdertotal - d.appliedcreditstotal --"Thank you"/unduly enriched claim
WHEN d.status ILIKE '%resolved%' AND d.originaldisputeamount IS NOT NULL THEN d.originaldisputeamount - d.accountholdertotal - d.appliedcreditstotal
WHEN d.status ILIKE '%resolved%' THEN d.disputeamount - d.accountholdertotal - d.appliedcreditstotal
END) 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 |
2/14/2025 | Updated SQL to reference AppliedCreditsTotal instead of the MerchantCredit table. |
2/21/2025 |
|