Saturday 8 September 2012

Query to find AR Invoices posted to GL in R12


In R12 all GL posted transactions are sits in   xla_ae_headers, xla_ae_lines, xla_transaction_entities.
The below Query will give All AR Invoices posted to GL in R12

SELECT RCTLG.AMOUNT,
       AC.CUSTOMER_NUMBER,
       AC.CUSTOMER_NAME,
       RCX.TRX_NUMBER      
  FROM ra_customer_trx_all rcx,
       ar_customers ac,
       ra_cust_trx_line_gl_dist_all rctlg,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       xla_distribution_links xdl,
       gl_code_combinations gcc,
       xla.xla_transaction_entities xte,
       gl_import_references gir,
       gl_je_headers gjh,
       gl_je_lines gjl
 WHERE rcx.bill_to_customer_id = ac.customer_id(+)
   AND rcx.customer_trx_id = rctlg.customer_trx_id
   AND xah.ae_header_id = xal.ae_header_id(+)
   AND gcc.code_combination_id(+) = xal.code_combination_id
   AND rctlg.code_combination_id = xal.code_combination_id
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.source_distribution_id_num_1 = rctlg.cust_trx_line_gl_dist_id
   AND xte.source_id_int_1 = rcx.customer_trx_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND rctlg.account_class = 'REV'
   AND xte.entity_id = xah.entity_id
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID
   AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

4 comments:

  1. VERY nice! However I need to bring in lines and have everything except the proper joins to query by GL period. Any suggestions?

    ReplyDelete
  2. This query will returns all AR Transactions posted to GL,You can pass period name as parameter from gl_je_lines.If you want to show Invoice Lines information,you can read it from ra_cust_trx_line_gl_dist_all.

    Let me Know if it not works.

    ReplyDelete
  3. Can you please help me to find the transactions which are not posted to GL?

    ReplyDelete
  4. why cannot we copy query ? what is the point of giving it here otherwise ?

    ReplyDelete