select
d.clientid,
client.commonname as "Client",
c.claimtype as "Claim Type",
c.reasontype as "Claim Category",
c.reason as "Claim Reason",
d.claimid as "Claim ID",
d.disputeid as "Dispute ID",
c.customercontactdate as "Contact Date",
d.acquirernetwork as "Network",
d.status as "Dispute Status",
d.disputeamount as "Dispute Amount",
to_date(cb.performedondatetime) as "Chargeback Date",
cb.amount as "Chargeback Amount"
from
dispute as d
join claim as c
on d.tenantid = c.tenantid and d.claimid = c.claimid
join client as client
on d.tenantid = client.tenantid and d.clientid = client.clientid
join recoveryaction as cb
on d.tenantid = cb.tenantid and d.disputeid = cb.disputeid and cb.actionname in ('Dispute Submitted', 'Rapid Dispute Resolution') and cb.cycle = '1'
where
cb.performedondatetime is not null -- only disputes with chargebacks
where
d.tenantid = 'YOURTENANTIDHERE'
AND chargebackdate=:daterange
| 