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

Data Category	Event Field/Column Name	Data Type	Description	Applicability	Sample Value	Notes
Key	Tenant ID	VARCHAR	Unique ID associated with each Quavo client.	ALWAYS	qvo-stg
Key	Tenant	VARCHAR	A tenant is a Quavo customer that uses QFD. Each tenant is assigned a unique Tenant ID.	ALWAYS	Default
Key	Client ID	VARCHAR	A unique ID representing a single client.	ALWAYS	Default
Key	Client	VARCHAR	A client is a Quavo customer that uses QFD through a relationship with a Quavo tenant. Each client is assigned a unique Client ID.	ALWAYS	Default
Claim Details	Claim Type	VARCHAR	See Transaction Type.	CONDITIONAL	Card-Pinless
Claim Details	Claim Category	VARCHAR	General categorization of the claim. For a more precise description, see Claim Reason.	CONDITIONAL	Fraud
Compliance Details	Regulatory Coverage	VARCHAR	Identifies the specific reason associated with the claim.	CONDITIONAL	Reg E
Dispute Details	Network	VARCHAR	Unique ID associated with a claim. Format: YYMMDD<Ordinal Number>"C" Example: 2405230003C	CONDITIONAL	VISA
Key	ClaimId	VARCHAR	Unique ID associated with a claim. Format: YYMMDD<Ordinal Number>"C" Example: 2405230003C	ALWAYS	2306060014C
Key	DisputeId	VARCHAR	Unique ID associated with a disputed transaction. Format: YYMMDD#####"D"	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	Defines the function and/or skillset require to complete a task and determines who will work the task. Work Groups have been deprecated in favor of Assignment Groups.	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	Name of the user that performed the action.	ALWAYS	D. Robertson
Assignment Details	Performed By User ID	VARCHAR	The unique ID of the operator that performed an action.	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
2/3/2025	New view

Completed Tasks View

SQL

Change Log