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<font face="Courier New" size="2">
<font color = "blue">SELECT</font> <font color = "maroon">client</font><font color = "silver">.</font><font color = "maroon">commonname</font>               <font color = "blue">AS</font> <font color = "maroon">"Client"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">c</font><font color = "silver">.</font><font color = "maroon">claimtype</font>                     <font color = "blue">AS</font> <font color = "maroon">"Claim Type"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">c</font><font color = "silver">.</font><font color = "maroon">reasontype</font>                    <font color = "blue">AS</font> <font color = "maroon">"Claim Category"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">c</font><font color = "silver">.</font><font color = "maroon">reason</font>                        <font color = "blue">AS</font> <font color = "maroon">"Claim Reason"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">claimid</font>                       <font color = "blue">AS</font> <font color = "maroon">"Claim ID"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">disputeid</font>                     <font color = "blue">AS</font> <font color = "maroon">"Dispute ID"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">c</font><font color = "silver">.</font><font color = "maroon">customercontactdate</font>           <font color = "blue">AS</font> <font color = "maroon">"Contact Date"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">acquirernetwork</font>               <font color = "blue">AS</font> <font color = "maroon">"Network"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">status</font>                        <font color = "blue">AS</font> <font color = "maroon">"Dispute Status"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">disputeamount</font>                 <font color = "blue">AS</font> <font color = "maroon">"Dispute Amount"</font><font color = "silver">,</font>
<br/>       <font color = "#FF0080"><b>To_date</b></font><font color = "maroon">(</font><font color = "maroon">cb</font><font color = "silver">.</font><font color = "maroon">performedondatetime</font><font color = "maroon">)</font> <font color = "blue">AS</font> <font color = "maroon">"Chargeback Date"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">cb</font><font color = "silver">.</font><font color = "maroon">amount</font>                       <font color = "blue">AS</font> <font color = "maroon">"Chargeback Amount"</font><font color = "silver">,</font>
<br/>       <font color = "#FF0080"><b>To_date</b></font><font color = "maroon">(</font><font color = "maroon">rp</font><font color = "silver">.</font><font color = "maroon">performedondatetime</font><font color = "maroon">)</font> <font color = "blue">AS</font> <font color = "maroon">"Representment Date"</font><font color = "silver">,</font>
<br/>       <font color = "maroon">rp</font><font color = "silver">.</font><font color = "maroon">amount</font>                       <font color = "blue">AS</font> <font color = "maroon">"Representment Amount"</font>
<br/><font color = "blue">FROM</font>   <font color = "maroon">dispute</font> <font color = "blue">AS</font> <font color = "maroon">d</font>
<br/>       <font color = "blue">JOIN</font> <font color = "maroon">claim</font> <font color = "blue">AS</font> <font color = "maroon">c</font>
<br/>         <font color = "blue">ON</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">tenantid</font> <font color = "silver">=</font> <font color = "maroon">c</font><font color = "silver">.</font><font color = "maroon">tenantid</font>
<br/>            <font color = "blue">AND</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">claimid</font> <font color = "silver">=</font> <font color = "maroon">c</font><font color = "silver">.</font><font color = "maroon">claimid</font>
<br/>       <font color = "blue">JOIN</font> <font color = "maroon">client</font> <font color = "blue">AS</font> <font color = "maroon">client</font>
<br/>         <font color = "blue">ON</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">tenantid</font> <font color = "silver">=</font> <font color = "maroon">client</font><font color = "silver">.</font><font color = "maroon">tenantid</font>
<br/>            <font color = "blue">AND</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">clientid</font> <font color = "silver">=</font> <font color = "maroon">client</font><font color = "silver">.</font><font color = "maroon">clientid</font>
<br/>       <font color = "blue">JOIN</font> <font color = "maroon">recoveryaction</font> <font color = "blue">AS</font> <font color = "maroon">cb</font>
<br/>         <font color = "blue">ON</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">tenantid</font> <font color = "silver">=</font> <font color = "maroon">cb</font><font color = "silver">.</font><font color = "maroon">tenantid</font>
<br/>            <font color = "blue">AND</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">disputeid</font> <font color = "silver">=</font> <font color = "maroon">cb</font><font color = "silver">.</font><font color = "maroon">disputeid</font>
<br/>            <font color = "blue">AND</font> <font color = "maroon">cb</font><font color = "silver">.</font><font color = "maroon">actionname</font> <font color = "blue">IN</font> <font color = "maroon">(</font> <font color = "red">'Dispute Submitted'</font><font color = "silver">,</font>
<br/>                                   <font color = "red">'Rapid Dispute Resolution'</font> <font color = "maroon">)</font>
<br/>            <font color = "blue">AND</font> <font color = "maroon">cb</font><font color = "silver">.</font><font color = "maroon">cycle</font> <font color = "silver">=</font> <font color = "red">'1'</font>
<br/>       <font color = "blue">JOIN</font> <font color = "maroon">recoveryaction</font> <font color = "blue">AS</font> <font color = "maroon">rp</font>
<br/>         <font color = "blue">ON</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">tenantid</font> <font color = "silver">=</font> <font color = "maroon">rp</font><font color = "silver">.</font><font color = "maroon">tenantid</font>
<br/>            <font color = "blue">AND</font> <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">disputeid</font> <font color = "silver">=</font> <font color = "maroon">rp</font><font color = "silver">.</font><font color = "maroon">disputeid</font>
<br/>            <font color = "blue">AND</font> <font color = "maroon">rp</font><font color = "silver">.</font><font color = "maroon">actionname</font> <font color = "blue">IN</font> <font color = "maroon">(</font> <font color = "red">'Inbound Pre-Compliance Received'</font><font color = "silver">,</font>
<br/>                                   <font color = "red">'Dispute Declined'</font><font color = "silver">,</font>
<br/>                                   <font color = "red">'Pre-Arbitration Received'</font><font color = "silver">,</font>
<br/>                                       <font color = "red">'Dispute Accepted - Partial'</font> <font color = "maroon">)</font>
<br/><font color = "blue">WHERE</font>  <font color = "maroon">d</font><font color = "silver">.</font><font color = "maroon">tenantid</font> <font color = "silver">=</font> <font color = "red">'YOURTENANTIDHERE'</font>
<br/>       <font color = "blue">AND</font> <font color = "maroon">rp</font><font color = "silver">.</font><font color = "maroon">performedondatetime</font> <font color = "silver">=</font> <font color = "maroon">:daterange</font> 
</font>