Name	RecoveryDispute
Type	View
Description	Provides detailed information about every recovery attempt and outcome by dispute, primarily using data from the Recovery Action Table. NOTE: This view utilizes a significant number of joins and calculations. Please allow up to a few minutes load time for large sets of data.

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	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
Transaction Details	Network	VARCHAR(64)		CONDITIONAL	Mastercard, Visa
Key	ClaimId	VARCHAR(32)		ALWAYS	2306060014C
Key	DisputeId	VARCHAR(32)		ALWAYS	2306060014D
Claim Details	CustomerContactDate	DATE		CONDITIONAL	2023-09-09
Dispute Details	DisputeStatus	VARCHAR		ALWAYS	Pending-Response
Dispute Details	DisputeAmount	NUMBER		ALWAYS	123.45
Dispute Details	Reopened	BOOLEAN		CONDITIONAL	True	Only applicable when a dispute has been reopened after being denied.
Recovery Details	Cycle1Status			ALWAYS	Completed, Pending Response, Not Attempted
Recovery Details	Cycle1ActionDate			CONDITIONAL	2023-09-09
Recovery Details	Cycle1Action			CONDITIONAL	Chargeback, Request Submitted, Review Initiated
Recovery Details	Cycle1ActionAmount		Amount of recovery attempt.	CONDITIONAL	123.59
Recovery Details	Cycle1ResponseDate			CONDITIONAL	2023-09-09	Only applicable when a response in this cycle has been received.
Recovery Details	Cycle1Response			CONDITIONAL	Full Amount Recovered, Partial Amount Recovered, No Funds Recovered, Acquirer Declined, Acquirer Accepted Full, Acquirer Accepted Partial, Rapid Dispute Resolution, Inbound Pre-Arbitration	Only applicable when a response in this cycle has been received.
Recovery Details	Cycle1ResponseAmount		Amount of recovery attempt.	CONDITIONAL	123.59
Recovery Details	Cycle1Outcome			ALWAYS	Recovered, Not Recovered, Not Attempted
Recovery Details	Cycle2Status			ALWAYS	Completed, Pending Response, Not Attempted
Recovery Details	Cycle2ActionDate			CONDITIONAL	2023-09-09
Recovery Details	Cycle2Action			CONDITIONAL	Pre-Arbitration, Request Submitted, Review Initiated
Recovery Details	Cycle2ActionAmount		Amount of recovery attempt.	CONDITIONAL	123.59
Recovery Details	Cycle2ResponseDate			CONDITIONAL	2023-09-09	Only applicable when a response in this cycle has been received.
Recovery Details	Cycle2Response			CONDITIONAL	Full Amount Recovered, Partial Amount Recovered, No Funds Recovered, Acquirer Declined, Acquirer Accepted Full, Acquirer Accepted Partial, Acquirer Accepted Full, Inbound Arbitration, Acquirer Declined	Only applicable when a response in this cycle has been received.
Recovery Details	Cycle2ResponseAmount		Amount specified in response.	CONDITIONAL	123.59
Recovery Details	Cycle2Outcome			ALWAYS	Recovered, Not Recovered, Not Attempted
Recovery Details	Cycle3Status			ALWAYS	Completed, Pending Response, Not Attempted
Recovery Details	Cycle3ActionDate			CONDITIONAL	2023-09-09
Recovery Details	Cycle3Action			CONDITIONAL	Pre-Arbitration, Request Submitted, Review Initiated
Recovery Details	Cycle3ActionAmount		Amount of recovery attempt.	CONDITIONAL	123.59
Recovery Details	Cycle3ResponseDate			CONDITIONAL	2023-09-09	Only applicable when a response in this cycle has been received.
Recovery Details	Cycle3Response			CONDITIONAL	Full Amount Recovered, Partial Amount Recovered, No Funds Recovered, Arbitration Won, Arbitration Lost, Arbitration Split, Arbitration Accepted	Only applicable when a response in this cycle has been received.
Recovery Details	Cycle3ResponseAmount		Amount specified in response.	CONDITIONAL	123.59
Recovery Details	Cycle3Outcome			ALWAYS	Recovered, Not Recovered, Not Attempted
Recovery Details	OverallOutcome		Result of all recovery attempts.	ALWAYS	Recovered, Not Recovered, Not Attempted

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,
    tenant.name,
    d.clientid,
    client.commonname,
    c.claimtype,
    c.reasontype,
    c.reason,
    d.acquirernetwork,
    d.claimid,
    d.disputeid,
    (CASE
        WHEN d.status LIKE '%Resolved%' THEN 'Resolved'
        ELSE 'Open'
    END) as statustype,
    d.status,
    c.customercontactdate,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    d.disputeamount,
    (case
        when r1.actionname is null then 'Pending Response'
        else 'Completed'
    end) as cycle1Status,
    a1.recoveryoptionlabel as cycle1recoveryoption,
    (case
        when a1.actionname = 'Dispute Submitted' then 'Chargeback'
        else a1.actionname
    end) as Action,
    TO_TIMESTAMP_TZ(a1.performedondatetime::varchar || ' +0000'),
    a1.amount,
    r1.actionname,
    TO_TIMESTAMP_TZ(r1.performedondatetime::varchar || ' +0000'),
    r1.amount,
    (case
        when r1.actionname IN ('No Funds Recovered', 'Response Received - No Recovery', 'Dispute Declined', 'Pre-Arbitration Received') then 'No Recovery'
        when r1.actionname IN ('Full Amount Recovered', 'Rapid Dispute Resolution', 'Response Received - Full Amount Recovered', 'Dispute Accepted - Full', 'No Response Received', 'Representment Not Received') then 'Recovered'
        when r1.actionname IN ('Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Dispute Accepted - Partial') then 'Recovered'
        else 'N/A'  -- recalls, rejects
    end) as cycle1Outcome,
    (case
        when a2.actionname is null then 'Not Attempted'
        when r2.actionname is null then 'Pending Response'
        else 'Completed'
    end) as Cycle2Status,
    a2.recoveryoptionlabel as cycle2recoveryoption,
    (case
        when a2.actionname IN ('Inbound Pre-Arbitration Declined', 'Pre-Arbitration Submitted') then 'Pre-Arbitration'
        when a2.actionname = 'Arbitration Chargeback Submitted' then 'Arbitration Chargeback'
        else a2.actionname
    end) as Action,
    TO_TIMESTAMP_TZ(a2.performedondatetime::varchar || ' +0000'),
    a2.amount,
    r2.actionname,
    TO_TIMESTAMP_TZ(r2.performedondatetime::varchar || ' +0000'),
    r2.amount,
    (case
        when r2.actionname IN ('Inbound Arbitration Received', 'No Funds Recovered', 'Pre-Arbitration Declined', 'Response Received - No Recovery') then 'No Recovery'
        when r2.actionname IN ('Full Amount Recovered', 'Inbound Pre-Arbitration Response Accepted', 'Pre-Arbitration Accepted - Full', 'Response Received - Full Amount Recovered', 'No Inbound Arbitration Received', 'No Pre-Arbitration Response Received') then 'Recovered'
        when r2.actionname IN ('Partial Amount Recovered', 'Pre-Arbitration Accepted - Partial', 'Response Received - Partial Amount Recovered') then 'Recovered'
        else 'N/A' 
    end) as Cycle2Outcome,
    (case
        when a3.actionname is null then 'Not Attempted'
        when r3.actionname is null then 'Pending Response'
        else 'Completed'
    end) as Cycle3Status,
    a3.recoveryoptionlabel as cycle3recoveryoption,
    (case
        when a3.actionname IN ('Pre-Arbitration Escalated', 'Arbitration Submitted') then 'Arbitration'
        else a3.actionname
    end) as Action,
    TO_TIMESTAMP_TZ(a3.performedondatetime::varchar || ' +0000'),
    a3.amount,
    r3.actionname,
    TO_TIMESTAMP_TZ(r3.performedondatetime::varchar || ' +0000'),
    r3.amount,
    (case
        when r3.actionname IN ('Arbitration Lost', 'No Funds Recovered', 'Response Received - No Recovery') then 'No Recovery'
        when r3.actionname IN ('Arbitration Accepted', 'Arbitration Won', 'Full Amount Recovered', 'Response Received - Full Amount Recovered') then 'Recovered'
        when r3.actionname IN ('Arbitration Split', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered') then 'Recovered'
        else 'N/A' 
    end) as Cycle3Outcome,
    (case
        when Cycle3Outcome = 'Recovered' or Cycle2Outcome = 'Recovered' or Cycle1Outcome = 'Recovered' then 'Recovered'
        when r1.actionname is null then 'Not Attempted'
        else 'No Recovery'
    end) as OverallOutcome
from
    dispute as d
join claim as c on d.tenantid = c.tenantid and d.claimid = c.claimid
join tenant as tenant on d.tenantid = tenant.tenantid
join client as client on d.tenantid = client.tenantid and d.clientid = client.clientid

left join recoveryaction as a1 on a1.tenantid = d.tenantid and a1.disputeid = d.disputeid and a1.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)') and (a1.actionname = 'Dispute Submitted' or a1.actionname in ('Request Submitted', 'Review Initiated') and a1.cycle = '1') and a1.performedondatetime = (select max(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and (b.actionname = 'Dispute Submitted' or b.actionname in ('Request Submitted', 'Review Initiated') and b.cycle = '1') and b.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)'))

left join recoveryaction as r1 on r1.tenantid = d.tenantid and r1.disputeid = d.disputeid and r1.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Arbitration Received', 'Rapid Dispute Resolution', 'Dispute Accepted - Partial', 'Dispute Accepted - Full', 'Dispute Declined', 'Pre-Arbitration Received', 'No Response Received', 'Representment Not Received', 'Chargeback Rejected', 'Dispute Recalled') and r1.performedondatetime = (select min(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and b.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Arbitration Received', 'Rapid Dispute Resolution', 'Dispute Accepted - Partial', 'Dispute Accepted - Full', 'Dispute Declined', 'Pre-Arbitration Received', 'No Response Received', 'Representment Not Received', 'Chargeback Rejected', 'Dispute Recalled') and b.performedondatetime >= a1.performedondatetime)

left join recoveryaction as a2 on a2.tenantid = d.tenantid and a2.disputeid = d.disputeid and a2.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)') and (a2.actionname IN ('Arbitration Chargeback Submitted', 'Pre-Arbitration Submitted', 'Inbound Pre-Arbitration Declined') or a2.actionname in ('Request Submitted', 'Review Initiated') and a2.cycle = '2') and a2.performedondatetime = (select max(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and (b.actionname IN ('Arbitration Chargeback Submitted', 'Pre-Arbitration Submitted', 'Inbound Pre-Arbitration Declined') or b.actionname in ('Request Submitted', 'Review Initiated') and b.cycle = '2') and b.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)'))

left join recoveryaction as r2 on r2.tenantid = d.tenantid and r2.disputeid = d.disputeid and r2.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Pre-Arbitration Response Accepted', 'Pre-Arbitration Accepted - Full', 'Pre-Arbitration Accepted - Partial', 'Inbound Arbitration Received', 'Pre-Arbitration Declined', 'No Inbound Arbitration Received', 'No Pre-Arbitration Response Received') and r2.performedondatetime = (select min(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and b.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Inbound Pre-Arbitration Response Accepted', 'Pre-Arbitration Accepted - Full', 'Pre-Arbitration Accepted - Partial', 'Inbound Arbitration Received', 'Pre-Arbitration Declined', 'No Inbound Arbitration Received', 'No Pre-Arbitration Response Received') and b.performedondatetime >= a2.performedondatetime)

left join recoveryaction as a3 on a3.tenantid = d.tenantid and a3.disputeid = d.disputeid and a3.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)') and (a3.actionname IN ('Pre-Arbitration Escalated', 'Arbitration Submitted') or a3.actionname in ('Request Submitted', 'Review Initiated') and a3.cycle = '3') and a3.performedondatetime = (select max(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and (b.actionname IN ('Pre-Arbitration Escalated', 'Arbitration Submitted') or b.actionname in ('Request Submitted', 'Review Initiated') and b.cycle = '3') and b.recoveryoptionlabel not in ('Process Debit', 'Process Return (Debit)'))

left join recoveryaction as r3 on r3.tenantid = d.tenantid and r3.disputeid = d.disputeid and r3.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Arbitration Won', 'Arbitration Lost', 'Arbitration Split', 'Arbitration Accepted') and r3.performedondatetime = (select min(performedondatetime) from recoveryaction as b where b.tenantid = d.tenantid and b.disputeid = d.disputeid and b.actionname in ('Full Amount Recovered', 'No Funds Recovered', 'Partial Amount Recovered', 'Response Received - Partial Amount Recovered', 'Response Received - No Recovery', 'Response Received - Full Amount Recovered', 'Arbitration Won', 'Arbitration Lost', 'Arbitration Split', 'Arbitration Accepted') and b.performedondatetime >= a3.performedondatetime)


--NOTES:  Excludes unduly enriched (debits to claimant)

Change Log

Date	Change Summary

Date	Change Summary
12/10/2024	StickRate view deprecated in favor of Recovery view for performance optimization.
12/11/2024	SQL referenced added.

QFD Knowledgebase

Recovery Dispute View

SQL

Change Log