This post will show how to get GL Journal details of AP Invoice Payment in Oracle Fusion, i.e. how to get details of accounting details of AP Invoice payment, by navigating from AP_INVOICES_ALL all the way to GL_JE_HEADERS
We will start from AP_INVOICES_ALL and select one of the invoices:
SELECT *
FROM ap_invoices_all aia
WHERE aia.INVOICE_ID = 2896;
Use AP_INVOICE_LINES_ALL to get details of invoice lines by passing INVOICE_ID from above query:
SELECT *
FROM ap_invoice_lines_all aila
WHERE aila.INVOICE_ID = 2896;
AP_INVOICE_PAYMENTS_ALL contains records of invoice payments that you made to suppliers
Use AP_INVOICE_PAYMENTS_ALL by passing INVOICE_ID from above query:
SELECT *
FROM ap_invoice_payments_all aipa
WHERE aipa.INVOICE_ID = 2896;
Use AP_CHECKS_ALL by passing CHECK_ID from above query:
SELECT *
FROM ap_checks_all aca
WHERE aca.CHECK_ID = 3012;
XLA_TRANSACTION_ENTITIES contains a row for each transaction for which events have been raised in Subledger Accounting
Use XLA_TRANSACTION_ENTITIES by passing CHECK_ID from above query and Entity Code as AP_PAYMENTS:
SELECT *
FROM XLA_TRANSACTION_ENTITIES xeh
WHERE XEH.ENTITY_CODE = 'AP_PAYMENTS'
AND XEH.SOURCE_ID_INT_1 = 3012;
XLA_AE_HEADERS contains subledger accounting journal entries
Use XLA_AE_HEADERS by passing ENTITY_ID from above query: SELECT *
FROM XLA_AE_HEADERS XAH
WHERE XAH.ENTITY_ID = 4029791;
XLA_AE_LINES contains the journal entry lines for each subledger accounting journal entry.
Use XLA_AE_LINES by passing AE_HEADER_ID from above query:
SELECT *
FROM XLA_AE_LINES XAL
WHERE XAL.AE_HEADER_ID = 2123011;
You can specify the journal entry sources for which you want to maintain your transaction's origin by entering 'Yes' in the Import Journal References field of the Journal Sources definition.
For each source that has Import Journal References set to 'Yes', Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.
Use GL_IMPORT_REFERENCES by passing GL_SL_LINK_ID from above query:
SELECT *
FROM GL_IMPORT_REFERENCES GIR
WHERE gir.GL_SL_LINK_ID = 4246046;
So, final query will be:
select
aia.invoice_num
,aia.invoice_amount
,aia.invoice_currency_code
,aia.payment_currency_code
,aia.invoice_date
,aila.line_number
,aila.line_type_lookup_code
,aila.description
,aila.quantity_invoiced
,aila.amount
,aipa.payment_num
,aca.check_number
,aca.check_date
,aca.checkrun_name
,xeh.transaction_number
,xah.event_type_code
,xah.gl_transfer_status_code
,xah.gl_transfer_date
,xal.accounting_class_code
,gjb.name gl_batch_name
,gjb.DESCRIPTION gl_batch_desc
,gjb.status gl_batch_status
,gjh.name gl_header_name
,gjh.je_category
,gjh.je_source
,gjh.period_name
,gjl.je_line_num
,gjl.description gl_line_desc
,( SELECT fnd_flex_ext.get_segs(
'GL' --Application Short Name
,'GL#' --Key Flex Code
,gcc.chart_of_accounts_id --Structure Number
,gcc.code_combination_id
)
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = gjl.code_combination_id
) gl_code_combination
,gjl.currency_conversion_date
,gjl.currency_conversion_type
,gjl.currency_conversion_rate
,gjl.entered_dr
,gjl.entered_cr
,gjl.accounted_dr
,gjl.accounted_cr
from
ap_invoices_all aia
,ap_invoice_lines_all aila
,ap_invoice_payments_all aipa
,ap_checks_all aca
,xla_transaction_entities xeh
,xla_ae_headers xah
,xla_ae_lines xal
,gl_import_references gir
,gl_je_batches gjb
,gl_je_headers gjh
,gl_je_lines gjl
where 1=1
AND aia.invoice_id = aila.invoice_id
and aia.invoice_id = aipa.invoice_id
and aipa.check_id = aca.check_id
and aipa.check_id = aca.check_id
and aca.check_id = xeh.source_id_int_1
and xeh.entity_code = 'AP_PAYMENTS'
and xeh.entity_id = xah.entity_id
and xah.ae_header_id = xal.ae_header_id
and xal.gl_sl_link_id = gir.gl_sl_link_id
and gir.je_batch_id = gjb.je_batch_id
and gir.je_header_id = gjh.je_header_id
and gir.je_header_id = gjl.je_header_id
and gir.je_line_num = gjl.je_line_num
and aia.invoice_id = 2896
Comments
Post a Comment