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'

No comments:

Post a Comment