Claims Resolved View
- Devan Robertson
Name | ClaimsResolved |
|---|---|
Type | View |
Description | The Claims view provides most of the same information as the Claim table, though more consolidated, as well as additional columns that are useful in the context of claims, such as dispute count, dispute amount, recovered amount, etc. Includes only claims with a resolved status. |
Fields
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) | ALWAYS | Card-Pinless |
|
| |
Claim Details | ClaimCategory | VARCHAR(64) | ALWAYS | Fraud |
|
| |
Claim Details | ClaimReason | VARCHAR(64) | ALWAYS | Stolen |
|
| |
Compliance Details | RegulatoryCoverage | VARCHAR |
| ALWAYS | Reg E |
|
|
Claim Details | ClaimChannel | VARCHAR(32) |
| ALWAYS | Online Banking |
|
|
Key | ClaimId | VARCHAR(32) | ALWAYS | 2306060014C |
|
| |
Claim Details | CreateDate | DATE | ALWAYS | 2023-09-09 |
|
| |
Claim Details | ClaimAmount | NUMBER(20,2) | ALWAYS | 123.45 |
|
| |
Claim Details | ClaimStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
|
| |
Card Details | BIN | VARCHAR(8) | CONDITIONAL | 412356, 41235678 |
|
| |
Card Details | CardType | VARCHAR(16) |
| CONDITIONAL | Debit |
|
|
Claim Details | Decision | VARCHAR(64) | ALWAYS | Paid, Denied |
|
| |
Claim Details | PCDecision | VARCHAR(16) |
| CONDITIONAL | Grant |
|
|
Claim Details | PCDate | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 |
|
| |
Claim Details | ClaimContactDate | DATE | ALWAYS | 2023-09-09 |
|
| |
Claim Details | DiscoveryDate | DATE | CONDITIONAL | 2023-09-09 |
|
| |
Claim Details | InvestigationDate | TIMESTAMP_NTZ(9) | ALWAYS | 2023-09-09 21:00:00 |
|
| |
Claim Details | ResolvedDate | TIMESTAMP_NTZ(9) | ALWAYS | 2023-09-09 21:00:00 |
|
| |
Claim Details | Resolution | VARCHAR | Overall decision. | ALWAYS | Paid, Denied |
|
|
Claim Details | ResolutionTimeDays | NUMBER | Number of days between create date and resolution date. | ALWAYS | 30 |
|
|
Claim Details | CustomerResolutionTimeDays | NUMBER | Number of days between contact date and investigation date. | ALWAYS | 30 |
|
|
Claim Details | Reclassifications | NUMBER(3,0) | CONDITIONAL | 1 |
|
| |
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.
SELECT
c.tenantid,
t.name,
c.clientid,
cl.commonname,
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,
c.claimamount,
c.status,
c.cardbin,
c.cardtype,
c.decision,
c.pcdecision,
TO_TIMESTAMP_TZ(c.provisionalcreditdatetime::varchar || ' +0000'),
c.customercontactdate,
c.customerdiscoverydate,
TO_TIMESTAMP_TZ(c.investigationcompletedatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
(CASE
WHEN c.status = 'Resolved-Paid' THEN 'Paid'
ELSE 'Denied'
END) as "Resolution",
DATEDIFF(d, to_date(c.createdatetime), to_date(c.resolveddatetime)) AS ResolutionTimeDays,
DATEDIFF(d, c.customercontactdate, to_date(c.investigationcompletedatetime)) AS CustomerResolutionTimeDays,
c.reclassificationcount,
(SELECT COUNT(*) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.authorizationstatus = 'Posted') AS DisputeCount,
(SELECT SUM(d.disputeamount) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.authorizationstatus = 'Posted') 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), 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
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
WHERE c.status IN ('Resolved-Paid', 'Resolved-Denied')Change Log
Date | Change Summary |
|---|---|
12/11/24 |
|
|
|
|
|