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