I need a list and total of reversed unapplied credits for a period of time.
Well, once you understand that reversing an unapplied credit generates a REFUND charge entry, all you have to do is to search for creditsplits applied to REFUND charges:
Now, there are several dates available among the columns in rv_creditsplits, but if you want to get the dates when the reversal was done, then you would add a filter to just look at the dateapplied date range:
Select All Code:
1 2 3 4 5 6 7 | SELECT * FROM rv_creditsplits WHERE srvcode = 'REFUND' AND dateapplied BETWEEN '2012-01-01' AND '2012-01-31' |
This query will return all the columns in rv_creditsplits, so you should replace the * in the second line with the column names you want, separated by commas.
The form above also will return both the positive and negative credit splits. You might be refunding only part of the unapplied amount, so you want to concentrate on the negative amounts — the amounts actually reversed, so add: AND crsplamt < 0, like so:
Select All Code:
1 2 3 4 5 6 7 8 | SELECT * FROM rv_creditsplits WHERE srvcode = 'REFUND' AND crsplamt < 0 AND dateapplied BETWEEN '2012-01-01' AND '2012-01-31' |
For your summary, you probably just want the total of the reversals, but expressed as a positive amount, so we query the sum of the credit split amounts and multiply by minus 1:
Select All Code:
1 2 3 4 5 6 7 8 | SELECT SUM(crsplamt) * (-1) FROM rv_creditsplits WHERE srvcode = 'REFUND' AND crsplamt < 0 AND dateapplied BETWEEN '2012-01-01' AND '2012-01-31' |