Credit memo application not reducing ar custom report

Hi all,

please i need assistance with regards to my custom report, when you apply credit memo on a transaction , the balance will be changed on the form, but the report still holds the previous balance.

for example you have a transaction of #4,000 and you apply a credit memo of #1,000

new balance is #3,000.

but the report will still be holding #4,000 instead of the new balance #3,000.

the query for the custom report is copied below;

select distinct txa.trx_date,

txa.trx_number Invoice_no,

txa.ct_reference description,

txl.attribute12 Air_Way_Bill_No,

txl.quantity_invoiced,

txl.unit_selling_price,