SELECT 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",
To_date(rp.performedondatetime) AS "Representment Date",
rp.amount AS "Representment 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'
JOIN recoveryaction AS rp
ON d.tenantid = rp.tenantid
AND d.disputeid = rp.disputeid
AND rp.actionname IN ( 'Inbound Pre-Compliance Received',
'Dispute Declined',
'Pre-Arbitration Received',
'Dispute Accepted - Partial' )
WHERE d.tenantid = 'YOURTENANTIDHERE'
AND rp.performedondatetime = :daterange