Thursday, December 3, 2015

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


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect