Friday, February 4, 2011

To query Receipt accounting entries - I

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 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%')
AND ad.code_combination_id = gcc.code_combination_id
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 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%')
AND ad.code_combination_id = gcc.code_combination_id

1 comment:

  1. This post helps you to query receipt accounting entries. The syntax for the query is not simple and I don't get the logic behind it. It will be better if you can give an example for the same. I am waiting for the next post. Thanks.
    sap upgrade transactions

    ReplyDelete