Correspondence View
- Devan Robertson
Name | Correspondence_View |
|---|---|
Type | View |
Description | Includes all correspondence instances just like Correspondence Table , as well as additional columns from various other tables that are relevant in the context of correspondence. |
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) |
The Claim Channel defines the intake channel where the claim originated. This channel can include various avenues such as online banking, back office users, contact centers, or external 3rd party contact centers.
| ALWAYS | Back Office, Contact Center, Online Banking |
|
Key | ClaimId | VARCHAR(32) | ALWAYS | 2306060014C |
| |
Claim Details | ClaimStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
| |
Key | DisputeId | VARCHAR(32) | ALWAYS | 2306060014D |
| |
Dispute Details | DisputeStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
| |
Correspondence Details | Direction | VARCHAR | ALWAYS | Inbound, Outbound |
| |
Correspondence Details | Channel | VARCHAR(64) | ALWAYS | Email, Mail, Verbal |
| |
Correspondence Details | Type | VARCHAR(32) | CONDITIONAL | Confirmation, Denied, Paid, Provisional Credit, Other |
| |
Correspondence Details | Name | VARCHAR(128) | ALWAYS | Provisional Credit |
| |
Correspondence Details | Vendor | VARCHAR(32) |
| CONDITIONAL | Lob |
|
Correspondence Details | PageCount | NUMBER(38,0) |
| CONDITIONAL | 2 |
|
Correspondence Details | IsDoubleSided | BOOLEAN |
| CONDITIONAL | TRUE, FALSE |
|
Correspondence Details | IsReturnMailRequested | BOOLEAN |
| CONDITIONAL | TRUE, FALSE |
|
Correspondence Details | IsColor | BOOLEAN |
| CONDITIONAL | TRUE, FALSE |
|
Correspondence Details | Status | VARCHAR(64) | ALWAYS | In Transit |
| |
Metadata | CreateDateTime | TIMESTAMP_NTZ(9) | ALWAYS | 2023-09-09 21:00:00 |
| |
Correspondence Details | MailedDateTime | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 |
| |
Metadata | DaysToSend | NUMBER | CONDITIONAL | 2 |
| |
Metadata | BusinessDaysToSend | NUMBER | CONDITIONAL | 2 |
| |
Correspondence Details | Method | VARCHAR(32) | CONDITIONAL | AUTO |
| |
Correspondence Details | Source | VARCHAR(32) | ALWAYS | Workflow |
| |
Correspondence Details | Resend | BOOLEAN | CONDITIONAL | TRUE, FALSE |
|
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,
tenant.name,
c.clientid,
client.commonname,
claim.claimtype,
claim.reasontype,
claim.reason,
(CASE
WHEN claim.rege45datetime IS NOT NULL THEN 'Reg E'
WHEN claim.rege90datetime IS NOT NULL THEN 'Reg E'
WHEN claim.regz90datetime IS NOT NULL THEN 'Reg Z'
WHEN claim.isfcra = TRUE THEN 'FCRA'
ELSE 'No Coverage'
END) AS RegulatoryCoverage,
claim.channelorigin,
claim.claimid,
claim.status,
c.disputeid,
dispute.status,
c.inboundoutbound,
CAMEL_CASE_TO_STRING(c.channel),
(CASE
WHEN c.lettertype IS NULL THEN 'Other'
ELSE c.lettertype
END) AS Type,
c.purpose,
c.runtimevendor,
c.pagecount,
c.isdoublesided,
c.isreturnmailrequested,
c.iscolor,
c.transmissionstatus,
TO_TIMESTAMP_TZ(c.transmissionstatusdatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(c.maileddatetime::varchar || ' +0000'),
(CASE
WHEN c.createdatetime IS NOT NULL AND c.maileddatetime IS NOT NULL THEN datediff(d, c.createdatetime, c.maileddatetime)
END) AS DaysToSend,
(CASE
WHEN c.createdatetime IS NOT NULL AND c.maileddatetime IS NOT NULL THEN calculate_business_days_udf(c.createdatetime, c.maileddatetime)
END) AS BusinessDaysToSend,
c.executionmethod,
c.lettersource,
c.isresend,
c.correspondenceid
FROM
correspondence AS c
JOIN tenant AS tenant
ON tenant.tenantid = c.tenantid
JOIN claim AS claim
ON claim.tenantid = c.tenantid AND claim.claimid = c.claimid
LEFT JOIN
dispute AS dispute ON dispute.tenantid = c.tenantid AND dispute.disputeid = c.disputeid
JOIN client AS client
ON client.tenantid = c.tenantid AND client.clientid = c.clientid
WHERE
c.channel IS NOT NULL AND c.channel != 'Verbal'Change Log
Date | Change Summary |
|---|---|
12/11/24 |
|