View accounting query in oracle apps R12
Tools view accounting query in oracle EBS
Oracle receivables subledger accounting SLA query
Oracle Payables subledger accounting SLA query
Tools>>view accounting option can be seen on multiple forms of Oracle R12, such as Oracle Receivables transactions, Oracle Payables invoice screen, Receivable adjustments and so on. The underlying query for the view accounting form is same and it needs only two parameters that are event_id and application_id.
You can find the event id using below query in step 1. Application ids are 222=Receivables, 200=Payables
Step1: Run the below query using customer_trx_id/invoice_id as parameter and get the event id.
SELECT xe.event_id,
xe.application_id,
xe.event_type_code,
xe.event_date,
xe.event_number
FROM xla.xla_transaction_entities xte, xla.xla_events xe
WHERE xte.source_id_int_1 = '<enter transaction_id/Invoice_id/>'
AND xte.application_id = 222 --222=Receivables,200=Payables
AND xte.entity_code = 'TRANSACTIONS'
AND xte.entity_id = xe.entity_id
AND xte.application_id = xe.application_id;
--For inventory transaction cost accounting
SELECT xe.event_id,
xe.application_id,
xe.event_type_code,
xe.event_date,
xe.event_number,xte.application_id,xte.entity_code
FROM xla.xla_transaction_entities xte, xla.xla_events xe
WHERE xte.source_id_int_1 = ‘11205330975’
AND xte.application_id = 707 –222=Receivables,200=Payables,707=CST
AND xte.entity_code = ‘MTL_ACCOUNTING_EVENTS’
AND xte.entity_id = xe.entity_id
AND xte.application_id = xe.application_id;
Step 2: Run the below query using the event_id and application_id and you will get all event details along with AE_HEADER_ID.
SELECT -- gl.name ledger_name,
xah.amb_context_code,
xl1.meaning journal_entry_status,
xah.accounting_entry_status_code,
xl2.meaning balance_type,
xl3.meaning journal_entry_type,
jc.user_je_category_name journal_category,
xah.completed_date completion_date,
xah.accounting_date gl_date,
gbv.budget_name budget,
xl4.meaning fund_status,
xah.description description,
xah.doc_sequence_value document_sequence_number,
seq.name document_sequence_name,
xah.completion_acct_seq_value accounting_sequence_number,
seq2.header_name accounting_sequence_name,
seqv2.version_name accounting_sequence_version,
xah.close_acct_seq_value reporting_sequence_number,
seq3.header_name reporting_sequence_name,
seqv3.version_name reporting_sequence_version,
prt.name product_accounting_def,
xl6.meaning product_accounting_def_owner,
prb.product_rule_version product_accounting_def_ver,
xah.period_name period,
xah.reference_date reference_date,
xah.gl_transfer_date gl_transfer_date,
xl5.meaning gl_transfer_status,
le.name legal_entity,
le.legal_entity_identifier legal_entity_tax,
xect.name event_class,
xe.creation_date event_creation_date,
xah.ae_header_id,
xe.event_id,
-- gl.currency_code ledger_currency,
xah.ledger_id,
'N' select_flag,
1 hide_show,
xah.balance_type_code,
TO_NUMBER (NULL) total_accounted_cr,
TO_NUMBER (NULL) total_accounted_dr,
xah.parent_ae_header_id,
xah.accrual_reversal_flag,
xah.accounting_entry_type_code,
xet.name event_type,
xe.event_date
FROM xla.xla_ae_headers xah,
xla.xla_gl_ledgers gl, --xla_gl_ledgers_v
xla_lookups xl1,
xla_lookups xl2,
xla_lookups xl3,
xla_lookups xl4,
xla_lookups xl5,
xla_lookups xl6,
xla.xla_product_rules_tl prt,
xla.xla_product_rules_b prb,
fnd_document_sequences seq,
fun_seq_headers seq2,
fun_seq_versions seqv2,
fun_seq_headers seq3,
fun_seq_versions seqv3,
xle_entity_profiles le,
xla.xla_transaction_entities xte,
xla.xla_events xe,
gl_budget_versions gbv,
xla.xla_event_classes_tl xect,
xla.xla_event_types_tl xet,
gl_je_categories jc
WHERE xah.ledger_id = gl.ledger_id
AND xah.je_category_name = jc.je_category_name
AND xl1.lookup_type = 'XLA_ACCOUNTING_ENTRY_STATUS'
AND xl1.lookup_code = xah.accounting_entry_status_code
AND xl2.lookup_type = 'XLA_BALANCE_TYPE'
AND xl2.lookup_code = xah.balance_type_code
AND xl3.lookup_type = 'XLA_ACCOUNTING_ENTRY_TYPE'
AND xl3.lookup_code = xah.accounting_entry_type_code
AND xl4.lookup_type = 'XLA_FUNDS_STATUS'
AND xl4.lookup_code = NVL (xah.funds_status_code, 'R')
AND xl5.lookup_type(+) = 'GL_TRANSFER_FLAG'
AND xl5.lookup_code(+) = xah.gl_transfer_status_code
AND xl6.lookup_code(+) = xah.product_rule_type_code
AND xl6.lookup_type(+) = 'XLA_OWNER_TYPE'
AND prb.product_rule_type_code(+) = xah.product_rule_type_code
AND prb.product_rule_code(+) = xah.product_rule_code
AND prb.amb_context_code(+) = xah.amb_context_code
AND prb.application_id(+) = xah.application_id
AND prt.language(+) = USERENV ('LANG')
AND prt.product_rule_type_code(+) = xah.product_rule_type_code
AND prt.product_rule_code(+) = xah.product_rule_code
AND prt.amb_context_code(+) = xah.amb_context_code
AND prt.application_id(+) = xah.application_id
AND seq.doc_sequence_id(+) = xah.doc_sequence_id
AND seq2.seq_header_id(+) = seqv2.seq_header_id
AND seqv2.seq_version_id(+) = xah.completion_acct_seq_version_id
AND seq3.seq_header_id(+) = seqv3.seq_header_id
AND seqv3.seq_version_id(+) = xah.close_acct_seq_version_id
AND xte.application_id = xe.application_id
AND xte.legal_entity_id = le.legal_entity_id(+)
AND xe.entity_id = xte.entity_id
AND xe.event_id = xah.event_id
AND gbv.budget_version_id(+) = xah.budget_version_id
AND xah.application_id = xe.application_id
AND xet.application_id = xe.application_id
AND xet.event_type_code = xe.event_type_code
AND xet.language = USERENV ('LANG')
AND xect.application_id = xe.application_id
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.language = USERENV ('LANG')
AND xe.event_id = :1
AND xe.application_id = :2
Step 3: Run the below query using the AE_HEADER_ID obtained in step 2 and you will get the subledger accounting details/SLA details/XLA Details/View accounting details.
SELECT *
FROM xla_ae_lines xlal, xla_ae_headers xlah
WHERE xlal.ae_header_id = xlah.ae_header_id
AND xlah.ae_header_id = :ae_header_id
Related posts:
- PTD and YTD balances query in Oracle R12
- Query to find all Expense report entry delegations
- Query to find vacation rules in Oracle R12
- View accounting query in oracle apps R12
- Query to get AR invoice SLA and GL details
- Accounting entries not transferred to the General Ledger
- Query to find all APIs of Oracle Apps modules
- R12 AR cash receipt SLA and GL details query
- Oracle apps AR Applications and SLA details
- Oracle EBS query to get AP Invoice SLA Details
- Oracle EBS query to get AR Invoice SLA Details
- Oracle AR Remit to address query
- Oracle Apps iExpense credit card details query
- Query to get customer tax registration details in R12
- Purchase Requisition Vertex debug xml query in Oracle EBS
- Purchase Order Vertex debug xml query in Oracle EBS
- AP Invoice Vertex debug xml query in Oracle EBS
- AR Invoice Vertex debug xml query in Oracle EBS
- Query to find AP invoice workflow item key in oracle apps
- Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
- Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
- Query to find Sales Orders that are shipped but not invoiced
- Query to get Customer Bank Account Details
- Query to get description of GL Code Combination
- Query to find Purchase Order and Invoice Details
- Query to find AP Invoices matched with receipts
- Query to find Supplier Bank Details
- Query to find Credit Memos on an AP invoice
- Query to find the AP invoices that are applied to multiple Purchase Orders
- AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
- Query to find shipset and fulfilment set