AR Balance of Advance Received in Selected Currency

SELECT   class
        ,customer_trx_id
        ,adv_num
        ,adv_date
        ,cust_num
        ,cust_name
        ,agent
        ,curr
        ,advance_amount
        ,advance_due
        ,advance_applied
		,(advance_amount - advance_applied) * exchange_rate adv_bal_func
		,seg_all
        ,seg_acc
        ,ctrl_seg
FROM (SELECT   RCTT.type                    class
        ,TRX.customer_trx_id          customer_trx_id
        ,APS.trx_number               adv_num
        ,TRX.trx_date                 adv_date
        ,HCA.account_number           cust_num
        ,HZ.party_name                cust_name
        ,REP.name                     agent
        ,TRX.invoice_currency_code    curr
		,NVL(trx.exchange_rate,1)            exchange_rate
        ,NVL(APS.amount_due_original,0)      advance_amount
        ,NVL(aps.amount_due_remaining,0)     advance_due
        ,(SELECT NVL(SUM(NVL(ara.amount,0)),0)
          FROM   ar_adjustments_all ARA
                ,ra_customer_trx_all TRX1
          WHERE  TRX1.initial_customer_trx_id = TRX.customer_trx_id
          AND    ARA.customer_trx_id          = TRX1.customer_trx_id
          AND    TRX1.complete_flag           = 'Y'
          AND    ARA.adjustment_type          = 'C'
          )*-1   advance_applied
		, fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')                   seg_all
        , fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_acc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')                   seg_acc
        , &FLEX_SELECT_CTRL_SEG              ctrl_seg
FROM      ra_customer_trx              trx 
        , ra_cust_trx_line_gl_dist_all lgd 
        , ar_payment_schedules_all     aps 
        , ra_cust_trx_types_all        rctt 
        , gl_code_combinations         gcc 
        , ra_salesreps_all             rep 
        , hz_parties                   hz 
        , hz_cust_accounts             hca 
where     trx.customer_trx_id = lgd.customer_trx_id 
and       trx.customer_trx_id = aps.customer_trx_id
and       trx.cust_trx_type_id = rctt.cust_trx_type_id
and       trx.org_id           = rctt.org_id
and       lgd.code_combination_id = gcc.code_combination_id
and       trx.primary_salesrep_id = rep.salesrep_id(+) 
and       trx.bill_to_customer_id = hca.cust_account_id 
and       hz.party_id             = hca.party_id 
and       lgd.account_class   = 'REC' 
and       lgd.latest_rec_flag = 'Y' 
and       rctt.type           = 'DEP'
and       trx.complete_flag   = 'Y'
and       &gc_currency_code_where
and       &FLEX_WHERE_GL_ACCOUNT
and       &FLEX_WHERE_CTRL_SEG
and       APS.trx_number >=NVL(:P_COMMITMENT_LOW,APS.trx_number) 
and       APS.trx_number <=NVL(:P_COMMITMENT_HIGH,APS.trx_number)
and       hca.cust_account_id >=NVL(:P_CUSTOMER_NUMBER_LOW,hca.cust_account_id)
and       hca.cust_account_id <=NVL(:P_CUSTOMER_NUMBER_HIGH,hca.cust_account_id)
AND       DECODE (:P_ASSGN_ACC_TO,'General Ledger Date',LGD.gl_date,TRX.trx_date) <= :P_AS_OF_DATE
AND       DECODE (:P_ASSGN_ACC_TO,'General Ledger Date',LGD.gl_date,TRX.trx_date) >= NVL(:P_COMMITMENT_DATE,SYSDATE)
)
ORDER BY  curr
         ,ctrl_seg
         ,DECODE (UPPER(:P_SORT_BY),'CUSTOMER NUMBER',cust_num,adv_num)