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