Thursday 3 October 2013

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

No comments:

Post a Comment