How to validate a AR is correct?

--Purpose: To validate whether a transaction's REC is equal to its REV plus TAX or not
--Author : Richard.liu

 

SELECT c.trx_number, c.complete_flag, c.customer_trx_id,
       a.customer_trx_line_id,
       (SELECT payment_schedule_id
          FROM ar.ar_payment_schedules_all d
         WHERE a.customer_trx_id = d.customer_trx_id) payment_schedule_id,
       (SELECT receivable_application_id
          FROM ar.ar_receivable_applications_all e
         WHERE a.customer_trx_id =
                                  e.customer_trx_id)
                                                    receivable_application_id
  FROM ar.ra_customer_trx_lines_all a,
       ar.ra_customer_trx_lines_all b,
       ar.ra_customer_trx_all c
 WHERE c.set_of_books_id = '&sob'
   AND a.customer_trx_id = c.customer_trx_id
   AND a.set_of_books_id = '&sob'

   AND a.set_of_books_id = b.set_of_books_id
   AND a.customer_trx_line_id = b.link_to_cust_trx_line_id
   AND a.revenue_amount <> b.taxable_amount
   AND EXISTS (
          SELECT 'T'
            FROM ar.ra_cust_trx_types_all rctt
           WHERE rctt.post_to_gl = 'Y'
             AND rctt.set_of_books_id = c.set_of_books_id
             AND rctt.cust_trx_type_id = c.cust_trx_type_id
             AND rctt.end_date IS NULL)
   AND EXISTS (
          SELECT 'T'
            FROM ar.ra_cust_trx_line_gl_dist_all rctlg
           WHERE rctlg.set_of_books_id = c.set_of_books_id
             AND rctlg.customer_trx_id = c.customer_trx_id
             AND rctlg.gl_date >= SYSDATE - 31)