View accounting query in oracle apps R12

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

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                                                 -- ledger_name,
       xl1.meaning                   journal_entry_status,
       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,                      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,                      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,                       legal_entity,
       le.legal_entity_identifier    legal_entity_tax,                     event_class,
       xe.creation_date              event_creation_date,
       -- gl.currency_code ledger_currency,
       'N'                           select_flag,
       1                             hide_show,
       TO_NUMBER (NULL)              total_accounted_cr,
       TO_NUMBER (NULL)              total_accounted_dr,
       xah.accounting_entry_type_code,                      event_type,
  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.

  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: Upload your own post and refer it anywhere anytime:

Leave a Reply

%d bloggers like this: