Reclassification View
- Devan Robertson
Name | Reclassification |
|---|---|
Type | View |
Description | A normalized version of Reclassification table. |
Â
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 | Â | |
Reclassification Details | PreviousClaimCategory | VARCHAR | Claim Category prior to reclassification. | ALWAYS | Dispute | Â |
Reclassification Details | PreviousClaimReason | VARCHAR | Claim Reason prior to reclassification. | ALWAYS | Not Received | Â |
Key | ClaimId | VARCHAR(32) | ALWAYS | 2306060014C | Â | |
Claim Details | ClaimCreateDate | DATE | ALWAYS | 2023-09-09 | Â | |
Claim Details | ClaimCreatedBy | VARCHAR | Name of user that originally created the claim. | ALWAYS | D. Robertson | Â |
Reclassification Details | ReclassificationDateTime | DATE | Datetime that the claim was reclassified. | ALWAYS | 2023-09-09 | Â |
SQL
select
r.tenantid,
t.name,
r.clientid,
cl.commonname,
c.claimtype,
r.reasontype,
CAMEL_CASE_TO_STRING(r.reason),
(case
when r.previousreasontype = 'NonFraud' THEN 'Dispute'
when r.previousreasontype = 'NonFraudATM' THEN 'ATM'
when r.previousreasontype = 'AuthChargeback' THEN 'Authorization'
when r.previousreasontype = 'FraudAccountIssue' THEN 'Identity Theft'
else r.previousreasontype
end),
CAMEL_CASE_TO_STRING(r.previousreason),
r.claimid,
(CASE
WHEN c.createdbyuserid = 'System' THEN 'System'
ELSE CONCAT(LEFT(u.firstname, 1), '. ', u.lastname)
END),
TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(r.eventdatetime::varchar || ' +0000')
from reclassify as r
join tenant as t on r.tenantid = t.tenantid
join client as cl on r.tenantid = cl.tenantid and r.clientid = cl.clientid
join claim as c on r.tenantid = c.tenantid and r.claimid = c.claimid
left join userprofile as u on r.tenantid = u.tenantid and c.createdbyuserid = u.userid
Change Log
Date | Change Summary |
|---|---|
12/11/24 |
|
 |  |
 |  |