AR_XLA_GL

SELECT DISTINCT acr.receipt_number
,ard.code_combination_id,gcc.segment2,(NVL(SUM(gb.period_net_dr),0)-NVL(SUM(gb.period_net_cr),0))
FROM ar_cash_receipts_all acr
,ar_cash_receipt_history_all acrh
,ar_distributions_all ard
,xla_ae_headers xah
,xla_ae_lines xal
,xla_distribution_links xdl
,gl_import_references glimp
,gl_je_batches glb
,gl_je_headers glh
,gl_je_lines gll
,gl_code_combinations gcc
,gl_balances gb
WHERE acr.cash_receipt_id = acrh.cash_receipt_id
AND acrh.cash_receipt_history_id = ard.source_id
AND ard.source_table ='CRH'
AND xal.application_id = xah.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.application_id = xah.application_id
AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND xdl.source_distribution_id_num_1 = ard.line_id
AND glimp.je_header_id = glh.je_header_id
AND glimp.je_line_num = gll.je_line_num
AND glimp.je_batch_id = glb.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND glh.je_batch_id = glb.je_batch_id
AND glimp.gl_sl_link_table = xal.gl_sl_link_table
AND glimp.gl_sl_link_id = xal.gl_sl_link_id
AND glimp.reference_5 = xah.entity_id
AND glimp.reference_6 = xah.event_id
AND glimp.reference_7 = xah.ae_header_id
AND gcc.code_combination_id = xal.code_combination_id
AND gll.code_combination_id = gcc.code_combination_id
AND gb.code_combination_id = gcc.code_combination_id
AND gcc.enabled_flag = 'Y'
AND gcc.end_date_active is NULL
AND glh.je_source = 'Receivables'
AND gll.effective_date BETWEEN :begin_date AND :end_date
GROUP BY
acr.receipt_number,ard.line_id,ard.code_combination_id,gcc.segment2;