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) |
