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
VERY nice! However I need to bring in lines and have everything except the proper joins to query by GL period. Any suggestions?
ReplyDeleteThis 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.
ReplyDeleteLet me Know if it not works.
Can you please help me to find the transactions which are not posted to GL?
ReplyDeletewhy cannot we copy query ? what is the point of giving it here otherwise ?
ReplyDelete