-- 收款核销，贷项通知单核销也是通过ar_receivable_applications_all表
SELECT cr.receipt_number,ad.amount_dr,ad.amount_cr,ad.source_table,ad.source_type,xla_oa_functions_pkg.get_ccid_description(gcc.chart_of_accounts_id,ad.code_combination_id),ad.creation_date
  FROM ar_distributions_all           ad,ar_receivable_applications_all ra,ar_cash_receipts_all           cr,gl_code_combinations         gcc
 WHERE ad.source_table = ‘RA‘
   AND ad.source_id = ra.receivable_application_id
   AND ra.cash_receipt_id = cr.cash_receipt_id
   AND gcc.code_combination_id = ad.code_combination_id
-- 收款记录
UNION ALL
SELECT cr.receipt_number,ad.creation_date
  FROM ar_distributions_all        ad,ar_cash_receipt_history_all crh,ar_cash_receipts_all        cr,gl_code_combinations         gcc
 WHERE ad.source_table = ‘CRH‘
   AND ad.source_id = crh.cash_receipt_history_id
   AND crh.cash_receipt_id = cr.cash_receipt_id
   AND gcc.code_combination_id = ad.code_combination_id
 ORDER BY creation_date;
 

  --杂项收款
 SELECT cr.receipt_number,cr.cash_receipt_id,ad.creation_date
  FROM ar_distributions_all           ad,ar_misc_cash_distributions_all mcd,gl_code_combinations         gcc
 WHERE ad.source_table = ‘MCD‘
   AND ad.source_id = mcd.misc_cash_distribution_id
   AND mcd.cash_receipt_id = cr.cash_receipt_id
   AND gcc.code_combination_id = ad.code_combination_id;

   --应收总账日记账分录
   SELECT h.je_source,h.je_category,fnd_flex_ext.get_segs(‘sqlGL‘,‘GL#‘,gcc.chart_of_accounts_id,l.code_combination_id) acct,l.code_combination_id) acct_descr,l.entered_dr,l.entered_cr
  FROM gl_je_headers        h,gl_je_lines          l,gl_code_combinations gcc,gl_period_statuses   gps
 WHERE h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_source = ‘Receivables‘
   AND l.period_name = gps.period_name;