Name	Work_Completed
Type	View
Description	All tasks in a user queue that have been completed. Useful for performance and task completion timeliness reporting.
Version Availability	25.01

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value	Notes

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value	Notes
Key	Tenant ID	VARCHAR		ALWAYS	qvo-stg
Key	Tenant	VARCHAR		ALWAYS	Default
Key	Client ID	VARCHAR		ALWAYS	Default
Key	Client	VARCHAR		ALWAYS	Default
Claim Details	Claim Type	VARCHAR		CONDITIONAL	Card-Pinless
Claim Details	Claim Category	VARCHAR		CONDITIONAL	Fraud
Compliance Details	Regulatory Coverage	VARCHAR		CONDITIONAL	Reg E
Dispute Details	Network	VARCHAR		CONDITIONAL	VISA
Key	ClaimId	VARCHAR		ALWAYS	2306060014C
Key	DisputeId	VARCHAR		ALWAYS	2306060014D
Claim Details	PC Date	TIMESTAMP_TZ	Date that provisional credit must be granted by, based on applicable regulatory rules.	CONDITIONAL	2023-09-09 21:00:00
Claim Details	Final Reg Date	TIMESTAMP_TZ	Date the the claim must be resolved by, based on applicable regulatory rules.	CONDITIONAL	2023-09-09 21:00:00
Dispute Details	Next Recovery Date	TIMESTAMP_TZ	Date that the next recovery action must be completed by the issuer.	CONDITIONAL	2023-09-09 21:00:00
Assignment Details	Workgroup	VARCHAR		ALWAYS
Assignment Details	Assignment Group	VARCHAR	General grouping of assignment.	ALWAYS	Collect Customer Documentation
Assignment Details	Assignment	VARCHAR	Name of the assignment.	ALWAYS	Pending Documents
Assignment Details	Created Date	TIMESTAMP_TZ	Datetime that the assignment was created.	ALWAYS	2023-09-09 21:00:00
Assignment Details	Completed Date	TIMESTAMP_TZ	Datetime that the assignment was completed.	CONDITIONAL	2023-09-09 21:00:00	Only displayed when the assignment state is “Completed”.
Assignment Details	Performed By	VARCHAR		ALWAYS	D. Robertson
Assignment Details	Performed By User ID	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,
    (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 "Regulatory Coverage",
    d.acquirernetwork,
    a.claimid,
    a.disputeid,
    (CASE
        WHEN c.rege10datetime IS NOT NULL THEN TO_TIMESTAMP_TZ(c.rege10datetime::varchar || ' +0000')
        WHEN c.regzpcdatetime IS NOT NULL THEN TO_TIMESTAMP_TZ(c.regzpcdatetime::varchar || ' +0000')
    END) AS provisionalcreditdate,
    a.nextrecoverydatetime as nextrecoverydate,
    (CASE
        WHEN c.rege45datetime IS NOT NULL THEN TO_TIMESTAMP_TZ(c.rege45datetime::varchar || ' +0000')
        WHEN c.rege90datetime IS NOT NULL THEN TO_TIMESTAMP_TZ(c.rege90datetime::varchar || ' +0000')
        WHEN c.regz90datetime IS NOT NULL THEN TO_TIMESTAMP_TZ(c.regz90datetime::varchar || ' +0000')
    END) AS finalregdate,
    a.workgroup,
    a.tasklabel,
    TO_TIMESTAMP_TZ(a.createdatetime::varchar || ' +0000') as createddate,
    TO_TIMESTAMP_TZ(a.eventdatetime::varchar || ' +0000') as completeddate,
    (CASE
        WHEN g.assignmentgroup = 'Daily Critical Assignments' THEN 'Daily Critical'
        WHEN g.assignmentgroup = 'Daily Maintenance Assignments' THEN 'Daily Maintenance'
        WHEN g.assignmentgroup = 'Late Lifecycle Recovery Assignments' THEN 'Late Lifecycle Recovery'
        WHEN g.assignmentgroup IS NULL AND (completeddate <= nextrecoverydate OR completeddate <= finalregdate) THEN 'Approaching Recovery/Regulatory Deadline'
        WHEN g.assignmentgroup IS NULL AND (nextrecoverydate IS NOT NULL OR finalregdate IS NOT NULL) THEN 'Past Recovery/Regulatory Deadline'
        WHEN g.assignmentgroup IS NULL AND nextrecoverydate IS NULL AND finalregdate IS NULL THEN 'Non-Regulatory'
        ELSE g.assignmentgroup
    END) AS assignmentgroup,
    (CASE
        WHEN a.performedbyid = 'System' or a.performedbyid is NULL THEN 'System'  -- QPS-9986
        ELSE CONCAT(u.lastname, '. ', u.firstname)
    END) AS PerformedBy,
    a.performedbyid
FROM assignment as a
JOIN tenant as t on t.tenantid = a.tenantid
JOIN client as cl on cl.tenantid = a.tenantid AND cl.clientid = a.clientid
JOIN claim as c on c.tenantid = a.tenantid AND c.claimid = a.claimid
LEFT JOIN assignmentgrouping as g on g.assignment = a.tasklabel
LEFT JOIN dispute as d on d.tenantid = a.tenantid AND d.disputeid = a.disputeid
LEFT JOIN userprofile as u ON u.tenantid = a.tenantid AND u.userid = a.performedbyid
WHERE
    a.assignmentstate = 'Completed'
    AND a.workgroup NOT IN ('System','Pending','Unworkable','Default')

Change Log

Date	Change Summary

Date	Change Summary
2/3/2025	New view

QFD Knowledgebase

Completed Tasks View

Analytics

SQL

Change Log

Related content