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 |
|
|
|
|
|