Claims Basic View
- Devan Robertson
Name | ClaimsBasic |
|---|---|
Type | View |
Description | A lighter weight version of the Claims View for basic calculations and aggregations. |
Â
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 | ClaimContactDate | 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 | ResolvedDate | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 | Â | |
Dispute Details | DisputeCount | NUMBER(3,0) |
| CONDITIONAL | 2 | Â |
Dispute Details | DisputeAmount | NUMBER(20,2) |
| CONDITIONAL | 123.59 | Â |
Dispute Details | Age | NUMBER(20,2) | Current Date - Create Date (DAYS) | ALWAYS | 48 | Â |
SQL
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.cardtype,
c.cardbin,
c.channelorigin,
c.claimid,
c.claimamount,
cs.state,
CAMEL_CASE_TO_STRING(c.status),
TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
c.customercontactdate,
TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
(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,
DATEDIFF(d, createdatetime, CURRENT_DATE)
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
ORDER BY
c.createdatetime DESCChange Log
Date | Change Summary |
|---|---|
12/11/24 |
|
 |  |
 |  |