Name	Work_All
Type	View
Description	A list of all outstanding user assignments along with relevant claim and dispute attributes used to determine priority order.

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
Key	TenantId	VARCHAR		ALWAYS	qvo-stg
Key	Tenant	VARCHAR		ALWAYS	Default
Key	ClientId	VARCHAR		ALWAYS	Default
Key	Client	VARCHAR		ALWAYS	Default
Claim Details	ClaimType	VARCHAR		CONDITIONAL	Card-Pinless
Claim Details	ClaimCategory	VARCHAR		CONDITIONAL	Fraud
Compliance Details	RegulatoryCoverage	VARCHAR		CONDITIONAL	Reg E
Dispute Details	Network	VARCHAR		CONDITIONAL	Visa
Key	ClaimId	VARCHAR		ALWAYS	2306060014C
Key	DisputeId	VARCHAR		ALWAYS	2306060014D
Claim Details	TotalClaimAmount	NUMBER		ALWAYS	123.45
Dispute Details	DisputeAmount	NUMBER	The current amount in dispute, accounting for any recoveries or adjustments.	ALWAYS	185.55
Assignment Details	Assignment	VARCHAR	Name of the assignment.	ALWAYS	Pending Documents
Assignment Details	Workgroup	VARCHAR	Assigned workgroup.	ALWAYS	Daily Critical
Assignment Details	CreatedDateTime	DATE	Datetime that the assignment was created.	ALWAYS	2023-09-09
Accounting Details	ProvisionalCreditDate	DATE		CONDITIONAL	2023-09-09
Compliance Details	PCActionDays	NUMBER	Days until provisional credit is due.	CONDITIONAL	3
Recovery Details	NextRecoveryDateTime	Date	Date that the next issuer recovery action must be completed.	CONDITIONAL	2023-09-09
Recovery Details	RecoveryActionDays	Number	Days until the next issuer recovery action must be completed.	CONDITIONAL	3
Compliance Details	FinalRegDateTime	Date	Date that claim must be resolved based on applicable regulatory coverage.	CONDITIONAL	2023-09-09
Compliance Details	ResolutionActionDays	Number	Days until claim must be resolved based on applicable regulatory coverage.	CONDITIONAL	3
Assignment Details	AssignmentGroup	VARCHAR	General assignment grouping. Does not account for high dollar.	CONDITIONAL	2023-09-09
Assignment Details	NextAction	VARCHAR	Next required compliance or recovery action.	CONDITIONAL	Provisional Credit, Recovery Action, Resolution Action
Assignment Details	NextActionDate	Date	Date of next required compliance or recovery action.	CONDITIONAL	2023-09-09
Assignment Details	ActionDue	Number	Days until next required compliance or recovery action.	CONDITIONAL	3
Assignment Details	Age	NUMBER	Days between the assignment create date and today.	ALWAYS	12

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
    d.tenantid,
    t.name,
    d.clientid,
    cl.commonname,
    c.claimtype,
    c.reasontype,
    CAMEL_CASE_TO_STRING(c.reason) AS "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 "Regulatory Coverage",
    (CASE
        WHEN d.acquirernetwork = 'MasterCard' THEN 'Mastercard'
        WHEN d.acquirernetwork = 'Debit Mastercard' THEN 'Mastercard Debit'
        WHEN d.acquirernetwork IS NULL THEN 'Unknown'
        ELSE d.acquirernetwork
    END) AS "Network",
    c.channelorigin,
    c.cardtype,
    c.cardbin,
    d.claimid,
    d.disputeid,
    c.status,
    d.status,
    d.decision,
    CAMEL_CASE_TO_STRING(d.denyreason) AS "DenyReason",
    c.customercontactdate,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.investigationcompletedatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    (CASE
        WHEN d.status = 'Resolved-Paid' THEN 'Paid'
        WHEN d.status = 'Resolved-Denied' THEN 'Denied'
        WHEN d.status = 'Resolved-NoPosting' THEN 'Never Posted'
    END) as "Resolution",
    DATEDIFF(d, d.createdatetime, d.resolveddatetime) AS "ResolutionTimeDays",
    DATEDIFF(d, c.customercontactdate, to_date(d.investigationcompletedatetime)) AS CustomerResolutionTimeDays,
    d.authorizationstatus,
    d.merchantcategorycode,
    d.merchantname,
    d.posentrymode,
    d.description,
    d.disputeamount,
    d.amount,
    d.accountholdertotal,
    IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid), 0) AS "MerchantCreditAmount",
    IFNULL((CASE
        WHEN d.transactiontype = 'ACH' THEN (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid AND r.actionname = 'Recovery Pursued') - (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid AND r.actionname = 'Recovery Cancelled')
        ELSE (SELECT max(r.cycle) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid)
    END), 0) AS "Recovery Attempts",
    d.merchanttotal * -1 AS "RecoveredAmount",
    d.losstotal * -1 AS "LossAmount",
    d.disputeamount - d.accountholdertotal - "MerchantCreditAmount" AS "DeniedAmount",
    "MerchantCreditAmount" + "RecoveredAmount" + "DeniedAmount" AS "RecapturedAmount",
    (CASE
        WHEN d.isreopened = TRUE THEN TRUE
        ELSE FALSE
    END) AS "Reopened"
FROM dispute AS d
JOIN tenant AS t
    ON d.tenantid = t.tenantid
JOIN claim AS c
    ON d.tenantid = c.tenantid AND d.claimid = c.claimid
JOIN client AS cl
    ON d.tenantid = cl.tenantid AND d.clientid = cl.clientid
WHERE
    d.status IN ('Resolved-Paid', 'Resolved-Denied', 'Resolved-NoPosting')

Change Log

Date	Change Summary

Date	Change Summary
12/11/2024	Added SQL Reference Converted timestamps from NTZ to TZ format for compatibility

QFD Knowledgebase

Work View

SQL

Change Log