Skip to end of banner
Go to start of banner

Resolved Disputes - Summary

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 6 Next »

Description:Summary count, total dispute amount, total recovery amount, and total loss amount for disputes resolved within a specified date range. 
Sources:Dispute, Claim

SQL

WITH disputes AS (
SELECT
    d.amount AS "Transaction Amount",
    d.disputeamount AS "Dispute Amount",
    d.losstotal * -1 AS "Loss Amount",
    (CASE
        WHEN (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid) > 0 THEN (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid)
        WHEN (d.denyreason = 'Merchant Issued Credit' AND (d.disputeamount + d.merchanttotal + d.losstotal) > 0) THEN d.disputeamount + d.merchanttotal + d.losstotal
        ELSE 0
    END) AS "Merchant Credit Amount",
    (CASE
        WHEN d.amount = d.disputeamount THEN d.disputeamount - d.accountholdertotal - "Merchant Credit Amount"
        WHEN d.disputeamount = "Merchant Credit Amount" THEN 0
        ELSE d.disputeamount - d.accountholdertotal
    END) AS "Denied Amount",
    (CASE
        WHEN d.merchanttotal * -1 < 0 THEN 0 --account for undue enrichment disputes
        ELSE d.merchanttotal * -1
    END) AS "Recovered Amount",
    IFNULL((SELECT SUM(amount) FROM recoveryaction AS win WHERE win.tenantid = d.tenantid AND win.disputeid = d.disputeid AND (win.actionname IN ('Dispute Accepted - Partial', 'Dispute Accepted - Full', 'No Response Received', 'Representment Not Received', 'Rapid Dispute Resolution') OR win.actionname IN ('Response Received - Full Amount Recovered', 'Response Received - Partial Amount Recovered') AND win.cycle = 1 OR win.actionname IN ('Recovery Pursued', 'Return Submitted') AND win.recoveryassociation = 'ACH') AND win.actioncode != 'UndueEnrichment'), 0) - IFNULL((SELECT SUM(amount) FROM accounting AS a WHERE a.tenantid = d.tenantid AND a.disputeid = d.disputeid AND a.reason = 'Merchant Funds Disbursement' AND a.debitcredit = 'Debit'), 0) AS "Cycle 1 Recovery",
    IFNULL((SELECT SUM(amount) FROM recoveryaction AS win WHERE win.tenantid = d.tenantid AND win.disputeid = d.disputeid AND (win.actionname IN ('Inbound Pre-Arbitration Response Accepted', 'No Inbound Arbitration Received', 'Pre-Arbitration Accepted - Full', 'Pre-Arbitration Accepted - Partial', 'No Pre-Arbitration Response Received') OR win.actionname IN ('Response Received - Full Amount Recovered', 'Response Received - Partial Amount Recovered') AND win.cycle = 2) AND win.actioncode != 'UndueEnrichment'), 0) AS "Cycle 2 Recovery",
    IFNULL((SELECT SUM(amount) FROM recoveryaction AS win WHERE win.tenantid = d.tenantid AND win.disputeid = d.disputeid AND (win.actionname IN ('Arbitration Won', 'Arbitration Split', 'Arbitration Accepted') OR win.actionname IN ('Response Received - Full Amount Recovered', 'Response Received - Partial Amount Recovered') AND win.cycle >= 3) AND win.actioncode != 'UndueEnrichment'), 0) AS "Cycle 3 Recovery",
    "Recovered Amount" + "Merchant Credit Amount" + "Denied Amount" as "Recaptured Amount"    
FROM
    dispute AS d
JOIN
    claim AS c ON c.tenantid = d.tenantid AND c.claimid = d.claimid
LEFT JOIN
    correspondence AS dj ON dj.tenantid = d.tenantid AND dj.disputeid = d.disputeid AND dj.purpose IN ('No Error Occurred', 'Partial Deny No Error', 'Accountholder Liable', 'Resolved Partial Deny No Error') AND dj.runtimememo IS NOT NULL
WHERE
    d.resolveddatetime =:daterange
    AND d.status in ('Resolved-Paid', 'Resolved-Denied') -- exclude open disputes and temporary authorizations that posted as these will have a new dispute record
)
SELECT
    COUNT(*) AS "Resolved Dispute Count",
    SUM("Dispute Amount") AS "Amount Disputed",
    COUNT_IF("Loss Amount" > 0) AS "Disputes With Losses",
    SUM("Loss Amount") AS "Total Loss Amount",
    COUNT_IF("Recovered Amount" > 0) AS "Disputes With Recovery",
    SUM("Cycle 1 Recovery") AS "Cycle 1 Recovered Amount",
    SUM("Cycle 2 Recovery") AS "Cycle 2 Recovered Amount",
    SUM("Cycle 3 Recovery") AS "Cycle 3 Recovered Amount",
    SUM("Recovered Amount") AS "Total Recovered Amount",
    COUNT_IF("Merchant Credit Amount" > 0) AS "Disputes With Merchant Credit",
    SUM("Merchant Credit Amount") AS "Total Merchant Credits",
    COUNT_IF("Denied Amount" > 0) AS "Disputes With Denial",
    SUM("Denied Amount") AS "Total Denied",
    SUM("Recaptured Amount") AS "Recaptured Amount" 
FROM
    disputes

Field Glossary

FieldDefinition
Resolved Dispute CountPosted amount of the transaction.  May differ from /wiki/spaces/ARIA/pages/48269484.
Total Disputed AmountThe current amount in dispute, accounting for any recoveries or adjustments.
Disputes with LossesCount of disputes with a loss (partial or full).
Total Loss AmountThe balance of the Loss accounting collection.  Also referred to as LOSSTOTAL.
Disputes with RecoveryCount of disputes with successful recoveries (partial or full).
Cycle 1 RecoveryThe total dollar amount of any successful recovery attempts during the first cycle.  For card transactions, this refers to the chargeback cycle.
Cycle 2 RecoveryThe total dollar amount of any successful recovery attempts during the second cycle.  For card transactions, this typically represents the pre-arbitration cycle.
Cycle 3 RecoveryThe total dollar amount of any successful recovery attempts during the second cycle.  For card transactions, this typically represents an arbitration or compliance cycle.
Total Recovered AmountTotal amount of all successful partial and full recovery attempts.  Does not include merchant credits posted directly to the account.  Not considered final until the dispute is in a resolved status.
Denied AmountTotal amount of all partial and full denials applied, not including merchant credits.
Recovered AmountTotal amount of all successful partial and full recovery attempts.  Does not include merchant credits posted directly to the account.  Not considered final until the dispute is in a resolved status.
Disputes With Merchant CreditCount of disputes with an applied merchant credit (partial or full).
Total Merchant CreditsAmount of all applied merchant credits.
Disputes with DenialCount of disputes with an applied denial (partial or full).
Total DeniedTotal amount of all partial and full denials applied, not including merchant credits.
Recaptured AmountRepresents the total dollar amount of successful loss avoidance, including recoveries, merchant credits, and denials.

Change Log

DateDescription
5/9/2024Updated Merchant Credit logic to use the Merchant Credit table in order to capture partial merchant credits on paid and denied disputes.  
  • No labels