AP Trial Balance Report query

 SELECT

aia.invoice_num,

aia.invoice_date, 

aia.invoice_currency_code inv_curr,

aia.invoice_amount inv_amount,

SUM(CASE WHEN aia.invoice_currency_code = aia.payment_currency_code THEN

             (NVL(tb.entered_rounded_cr,0)) - (NVL(tb.entered_rounded_dr,0))          

        ELSE  (NVL(tb.entered_rounded_cr,0) - ((NVL(tb.entered_rounded_dr,0)*NVL((select conversion_rate

                                                                            from gl_daily_rates

                                                                            where conversion_type = aia.exchange_rate_type

                                                                            and trunc(conversion_date) = trunc(nvl(aia.exchange_date, tb.gl_date))

                                                                            and from_currency = aia.invoice_currency_code

                                                                            and to_currency = aia.payment_currency_code),0))))

END) inv_rem_amt,

(select segment1 from ap_suppliers where vendor_id = aia.vendor_id) segment1,

hp.party_name vendor_name,

gcc.concatenated_segments,

SUM (nvl(tb.acctd_rounded_cr,0)) - SUM (nvl(tb.acctd_rounded_dr,0)) remaining_amount,

SUM(CASE WHEN tb.applied_to_entity_id IS NULL THEN

             (NVL(tb.acctd_rounded_cr,0)) - (NVL(tb.acctd_rounded_dr,0))          

        ELSE  0

END) invoice_amount,

NVL (tb.applied_to_entity_id, tb.source_entity_id) source_entity_id,

tb.ledger_id ,

tb.code_combination_id,

aia.invoice_id ,

aia.org_id

FROM xla_transaction_entities xte

         , xla_trial_balances tb 

         , ap_invoices aia

         , hz_parties hp

         , gl_code_combinations_kfv gcc

WHERE  1=1 

AND  aia.party_id = hp.party_id

AND  NVL (tb.applied_to_entity_id, tb.source_entity_id) = xte.entity_id  

AND  xte.ledger_id= tb.ledger_id 

AND  xte.application_id = 200

and tb.source_application_id = 200

AND  aia.set_of_books_id = xte.ledger_id           

AND  aia.invoice_id = xte.source_id_int_1

AND  tb.code_combination_id = gcc.code_combination_id

----lp_date

and trunc(tb.gl_date) <=trunc(sysdate)             

GROUP BY aia.invoice_num,

                    aia.invoice_date, 

                    aia.invoice_currency_code,

                    aia.invoice_amount,

                   (aia.invoice_amount-(nvl(aia.amount_paid,0)+nvl(aia.discount_amount_taken,0))),                    

                    aia.vendor_id,

                    hp.party_name,

                    gcc.concatenated_segments,

                    aia.invoice_amount,

                    NVL (tb.applied_to_entity_id, tb.source_entity_id),

                    tb.ledger_id,

                    tb.code_combination_id,

                    aia.invoice_id,

                    aia.org_id 

HAVING SUM (nvl(tb.acctd_rounded_cr,0)) <> SUM (nvl(tb.acctd_rounded_dr,0))

begin

mo_global.set_policy_context('S',29830);

end;