Assignment View
- Devan Robertson
Name | Assignment_View |
|---|---|
Type | View |
Description | Includes records for all create and completed assignments, just like the Assignment table, as well as additional columns from various other tables that are relevant in the context of assignments. |
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 | ClaimStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
| |
Dispute Details | DisputeStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
| |
Claim Details | ClaimContactDate | DATE | CONDITIONAL | 2023-09-09 |
| |
Claim Details | ClaimAge | NUMBER(20,2) | Current Date - Create Date (DAYS) | ALWAYS | 48 |
|
Key | ClaimId | VARCHAR(32) | ALWAYS | 2306060014C |
| |
Key | DisputeId | VARCHAR(32) | ALWAYS | 2306060014D |
| |
Claim Details | ClaimAmount | NUMBER(20,2) | ALWAYS | 123.45 |
| |
Dispute Details | DisputeAmount | NUMBER(9,2) | The current amount in dispute, accounting for any recoveries or adjustments. | ALWAYS | 185.55 |
|
Assignment Details | Flow | VARCHAR(128) | The flow where a flow action is completed. | ALWAYS | Collect Customer Documentation |
|
Assignment Details | Assignment | VARCHAR(128) | Name of the assignment. | ALWAYS | Pending Documents |
|
Assignment Details | State | VARCHAR | The current state of the assignment | ALWAYS | Outstanding, Completed |
|
Assignment Details | CreatedDateTime | TIMESTAMP_NTZ(9) | Datetime that the assignment was created. | ALWAYS | 2023-09-09 21:00:00 |
|
Assignment Details | CompletedDateTime | TIMESTAMP_NTZ(9) | Datetime that the assignment was completed. | CONDITIONAL | 2023-09-09 21:00:00 | Only displayed when the assignment state is “Completed”. |
Assignment Details | Age | NUMBER(9,2) | For outstanding assignment, the number of days between the create date and today. For completed assignments, the number of days between the create date and the complete date. | ALWAYS | 12 |
|
Assignment Details | Queue | VARCHAR | Designates who is intended to complete the work. | ALWAYS | Pending, System, User |
|
Assignment Details | PerformedBy | VARCHAR | ALWAYS | D. Robertson |
| |
Assignment Details | PerformedBy | VARCHAR | ALWAYS | devan.robertson@quavo.com |
|
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
a.tenantid,
t.name,
a.clientid,
cl.commonname,
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.status,
d.status,
c.customercontactdate,
datediff(d, c.customercontactdate, current_date),
a.claimid,
a.disputeid,
c.totalclaimamount,
d.disputeamount,
CAMEL_CASE_TO_STRING(a.flowtype),
a.tasklabel,
a.assignmentid,
(CASE
WHEN ac.eventdatetime IS NULL THEN 'Outstanding'
ELSE 'Completed'
END) AS State,
TO_TIMESTAMP_TZ(a.createdatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(ac.eventdatetime::varchar || ' +0000'),
(CASE
WHEN ac.eventdatetime IS NULL THEN DATEDIFF(d, convert_timezone('GMT', t.timezone, a.eventdatetime), convert_timezone('GMT', t.timezone,GETDATE()))
ELSE DATEDIFF(d, convert_timezone('GMT', t.timezone, a.eventdatetime), convert_timezone('GMT', t.timezone,ac.eventdatetime))
END) AS Age,
(CASE
WHEN a.workgroup NOT IN ('System', 'Pending') THEN 'User'
ELSE a.workgroup
END),
(CASE
WHEN ac.performedbyid = 'System' or ac.performedbyid is NULL THEN 'System' -- QPS-9986
ELSE CONCAT(LEFT(u.firstname, 1), '. ', u.lastname)
END) AS PerformedBy,
ac.performedbyid,
t.golivedatetime
FROM assignment AS a
JOIN tenant AS t ON a.tenantid = t.tenantid
JOIN claim AS c ON a.tenantid = c.tenantid AND a.claimid = c.claimid
JOIN client AS cl ON a.tenantid = cl.tenantid AND a.clientid = cl.clientid
LEFT JOIN dispute AS d ON a.tenantid = d.tenantid AND a.disputeid = d.disputeid
LEFT JOIN assignment AS ac ON ac.tenantid = a.tenantid AND ac.assignmentid = a.assignmentid AND ac.assignmentstate = 'Completed'
LEFT JOIN userprofile as u ON u.tenantid = ac.tenantid AND u.userid = ac.performedbyid
WHERE
a.assignmentstate = 'Created'Change Log
Date | Change Summary |
|---|---|
12/11/2024 |
|
|
|