...
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 | DenyReason | VARCHAR |
| CONDITIONAL | No Error | |||||||||||||
Transaction Details | TransactionDate | DATE |
| ALWAYS | 2023-09-09 | |||||||||||||
Claim Details | CreateDate | DATE |
| CONDITIONAL | 2023-09-09 | |||||||||||||
Claim Details | CustomerContactDate | DATE |
| CONDITIONAL | 2023-09-09 | |||||||||||||
Claim Details | ClaimAmount | NUMBER(20,2) |
| CONDITIONAL | 123.45 | |||||||||||||
Claim Details | ClaimState | VARCHAR |
| ALWAYS | OPEN | |||||||||||||
Dispute Details | Decision | VARCHAR(64) |
| CONDITIONAL | Paid, Denied | |||||||||||||
Claim Details | DiscoveryDate | DATE |
| CONDITIONAL | 2023-09-09 | |||||||||||||
Compliance Details | InvestigationDate | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | |||||||||||||
Compliance Details | ResolutionDeadline | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | |||||||||||||
Claim 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 | 4829 | |||||||||||||
Risk Details | PotentialAbuse | BOOLEAN |
| CONDITIONAL | FALSE | |||||||||||||
Risk Details | EmployeeClaim | BOOLEAN |
| CONDITIONAL | false | |||||||||||||
Claim Details | PCDecision | VARCHAR(16) |
| CONDITIONAL | Grant | |||||||||||||
Claim Details | PCDate | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | |||||||||||||
Claim Details | Reclassifications | NUMBER(3,0) |
| 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) |
| CONDITIONAL | 2 | |||||||||||||
Dispute Details | DisputeAmount | NUMBER(20,2) |
| 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 |
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 | Added TransactionDate column |
11/26/2024 | Added SQL Reference |