Showing posts with label Account Receivables (AR). Show all posts
Showing posts with label Account Receivables (AR). Show all posts

Tuesday, 18 August 2015

Query to Link Between AR and GL with XLA tables in R12

SELECT b.NAME batch_name,
              b.description batch_description,
              h.je_category,
              h.je_source,
              h.period_name je_period_name,
              h.NAME journal_name,
              h.status journal_status,
              h.description je_description,
              l.je_line_num line_number
 FROM gl_je_batches b,
             gl_je_headers h,
             gl_je_lines l,
             gl_code_combinations_kfv glcc,
             gl_import_references gir,
             xla_ae_lines xlal,
             xla_ae_headers xlah,
             xla_events xlae,
             xla.xla_transaction_entities xlate,
             ra_customer_trx_all rct
WHERE   b.je_batch_id = h.je_batch_id
     AND   h.je_header_id = l.je_header_id
     AND   xlal.code_combination_id = glcc.code_combination_id
     AND   l.je_header_id = gir.je_header_id
     AND   l.je_line_num = gir.je_line_num
     AND   gir.gl_sl_link_table = xlal.gl_sl_link_table
     AND   gir.gl_sl_link_id = xlal.gl_sl_link_id
     AND   xlal.ae_header_id = xlah.ae_header_id
     AND   xlah.event_id = xlae.event_id
     AND   xlae.entity_id = xlate.entity_id
     AND   xlae.application_id = xlate.application_id
     AND   h.je_source = 'Receivables'
     AND   rct.trx_number = xlate.transaction_number
     AND   xlate.transaction_number ='TRX_NUMBER'