Query To Get Receipt Accounting Entries

SELECT

  amount_dr,

  amount_cr,

  acctd_amount_dr,

  acctd_amount_cr,

  gcc.segment1

  || '.'

  || gcc.segment2

  || '.'

  || gcc.segment3

  || '.'

  || gcc.segment4

  || '.'

  || gcc.segment5 ACCOUNT

FROM

  ar.ar_distributions_all ad,

  gl.gl_code_combinations gcc

WHERE source_table = 'CRH'

  AND ad.code_combination_id = gcc.code_combination_id

AND EXISTS (

            SELECT 'T'

            FROM

            ar.ar_cash_receipt_history_all a,

            ar.ar_cash_receipts_all b

            WHERE a.cash_receipt_id = b.cash_receipt_id

            AND source_id = cash_receipt_history_id

            AND b.org_id = '&org_id'

            AND b.org_id = a.org_id

            AND b.receipt_number LIKE '%&receipt_number%'

           )

UNION ALL

SELECT

  amount_dr,

  amount_cr,

  acctd_amount_dr,

  acctd_amount_cr,

  gcc.segment1

  || '.'

  || gcc.segment2

  || '.'

  || gcc.segment3

  || '.'

  || gcc.segment4

  || '.'

  || gcc.segment5 ACCOUNT

FROM

  ar.ar_distributions_all ad,

  gl.gl_code_combinations gcc

WHERE source_table = 'RA'

  AND ad.code_combination_id = gcc.code_combination_id

AND EXISTS (

            SELECT 'T'

            FROM

            ar.ar_receivable_applications_all a,

            ar.ar_cash_receipts_all b

            WHERE a.cash_receipt_id = b.cash_receipt_id

            AND a.receivable_application_id = source_id

            AND b.org_id = '&org_id'

            AND b.org_id = a.org_id

            AND b.receipt_number LIKE '%&receipt_number%'

           )