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
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