--To validate whether a transaction's REC is equal to its REV plus TAX or not

--验证应收是否等于收入加税

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)