Claims View
- Devan Robertson
Name | Claims |
|---|---|
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. |
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) | CONDITIONAL | Card-Pinless |
| |
Claim Details | ClaimCategory | VARCHAR(64) | CONDITIONAL | Fraud |
| |
Claim Details | ClaimReason | VARCHAR(64) | CONDITIONAL | Stolen |
| |
Compliance Details | RegulatoryCoverage | VARCHAR |
| CONDITIONAL | Reg E |
|
Claim Details | ClaimChannel | VARCHAR(32) |
| ALWAYS | Online Banking |
|
Key | ClaimId | VARCHAR(32) | ALWAYS | 2306060014C |
| |
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 |
|
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) | CONDITIONAL | Paid, Denied |
| |
Claim Details | DiscoveryDate | DATE | CONDITIONAL | 2023-09-09 |
| |
Claim Details | InvestigationCompleteDateTime | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 |
| |
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 |
| |
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.
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 DESCChange Log
Date | Change Summary |
|---|---|
11/26/2024 | Added SQL |
12/11/2024 | Converted timestamps from NTZ to TZ format for compatibility |
|
|