View accounting query in oracle apps R12

•By admin

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.

SQL Querysql
1SELECT xe.event_id,
2       xe.application_id,
3       xe.event_type_code,
4       xe.event_date,
5       xe.event_number
6  FROM xla.xla_transaction_entities xte, xla.xla_events xe
7 WHERE     xte.source_id_int_1 = '<enter transaction_id/Invoice_id/>'
8       AND xte.application_id = 222  --222=Receivables,200=Payables
9       AND xte.entity_code = 'TRANSACTIONS'
10       AND xte.entity_id = xe.entity_id
11       AND xte.application_id = xe.application_id;
12
13
14--For inventory transaction cost accounting
15SELECT xe.event_id,
16xe.application_id,
17xe.event_type_code,
18xe.event_date,
19xe.event_number,xte.application_id,xte.entity_code
20FROM xla.xla_transaction_entities xte, xla.xla_events xe
21WHERE xte.source_id_int_1 = ‘11205330975’
22AND xte.application_id = 707 –222=Receivables,200=Payables,707=CST
23AND xte.entity_code = ‘MTL_ACCOUNTING_EVENTS’
24AND xte.entity_id = xe.entity_id
25AND 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.

SQL Querysql
1SELECT                                                 -- gl.name ledger_name,
2      xah.amb_context_code,
3       xl1.meaning                   journal_entry_status,
4       xah.accounting_entry_status_code,
5       xl2.meaning                   balance_type,
6       xl3.meaning                   journal_entry_type,
7       jc.user_je_category_name      journal_category,
8       xah.completed_date            completion_date,
9       xah.accounting_date           gl_date,
10       gbv.budget_name               budget,
11       xl4.meaning                   fund_status,
12       xah.description               description,
13       xah.doc_sequence_value        document_sequence_number,
14       seq.name                      document_sequence_name,
15       xah.completion_acct_seq_value accounting_sequence_number,
16       seq2.header_name              accounting_sequence_name,
17       seqv2.version_name            accounting_sequence_version,
18       xah.close_acct_seq_value      reporting_sequence_number,
19       seq3.header_name              reporting_sequence_name,
20       seqv3.version_name            reporting_sequence_version,
21       prt.name                      product_accounting_def,
22       xl6.meaning                   product_accounting_def_owner,
23       prb.product_rule_version      product_accounting_def_ver,
24       xah.period_name               period,
25       xah.reference_date            reference_date,
26       xah.gl_transfer_date          gl_transfer_date,
27       xl5.meaning                   gl_transfer_status,
28       le.name                       legal_entity,
29       le.legal_entity_identifier    legal_entity_tax,
30       xect.name                     event_class,
31       xe.creation_date              event_creation_date,
32       xah.ae_header_id,
33       xe.event_id,
34       -- gl.currency_code ledger_currency,
35       xah.ledger_id,
36       'N'                           select_flag,
37       1                             hide_show,
38       xah.balance_type_code,
39       TO_NUMBER (NULL)              total_accounted_cr,
40       TO_NUMBER (NULL)              total_accounted_dr,
41       xah.parent_ae_header_id,
42       xah.accrual_reversal_flag,
43       xah.accounting_entry_type_code,
44       xet.name                      event_type,
45       xe.event_date
46  FROM xla.xla_ae_headers           xah,
47       xla.xla_gl_ledgers           gl,                     --xla_gl_ledgers_v
48       xla_lookups                  xl1,
49       xla_lookups                  xl2,
50       xla_lookups                  xl3,
51       xla_lookups                  xl4,
52       xla_lookups                  xl5,
53       xla_lookups                  xl6,
54       xla.xla_product_rules_tl     prt,
55       xla.xla_product_rules_b      prb,
56       fnd_document_sequences       seq,
57       fun_seq_headers              seq2,
58       fun_seq_versions             seqv2,
59       fun_seq_headers              seq3,
60       fun_seq_versions             seqv3,
61       xle_entity_profiles          le,
62       xla.xla_transaction_entities xte,
63       xla.xla_events               xe,
64       gl_budget_versions           gbv,
65       xla.xla_event_classes_tl     xect,
66       xla.xla_event_types_tl       xet,
67       gl_je_categories             jc
68 WHERE     xah.ledger_id = gl.ledger_id
69       AND xah.je_category_name = jc.je_category_name
70       AND xl1.lookup_type = 'XLA_ACCOUNTING_ENTRY_STATUS'
71       AND xl1.lookup_code = xah.accounting_entry_status_code
72       AND xl2.lookup_type = 'XLA_BALANCE_TYPE'
73       AND xl2.lookup_code = xah.balance_type_code
74       AND xl3.lookup_type = 'XLA_ACCOUNTING_ENTRY_TYPE'
75       AND xl3.lookup_code = xah.accounting_entry_type_code
76       AND xl4.lookup_type = 'XLA_FUNDS_STATUS'
77       AND xl4.lookup_code = NVL (xah.funds_status_code, 'R')
78       AND xl5.lookup_type(+) = 'GL_TRANSFER_FLAG'
79       AND xl5.lookup_code(+) = xah.gl_transfer_status_code
80       AND xl6.lookup_code(+) = xah.product_rule_type_code
81       AND xl6.lookup_type(+) = 'XLA_OWNER_TYPE'
82       AND prb.product_rule_type_code(+) = xah.product_rule_type_code
83       AND prb.product_rule_code(+) = xah.product_rule_code
84       AND prb.amb_context_code(+) = xah.amb_context_code
85       AND prb.application_id(+) = xah.application_id
86       AND prt.language(+) = USERENV ('LANG')
87       AND prt.product_rule_type_code(+) = xah.product_rule_type_code
88       AND prt.product_rule_code(+) = xah.product_rule_code
89       AND prt.amb_context_code(+) = xah.amb_context_code
90       AND prt.application_id(+) = xah.application_id
91       AND seq.doc_sequence_id(+) = xah.doc_sequence_id
92       AND seq2.seq_header_id(+) = seqv2.seq_header_id
93       AND seqv2.seq_version_id(+) = xah.completion_acct_seq_version_id
94       AND seq3.seq_header_id(+) = seqv3.seq_header_id
95       AND seqv3.seq_version_id(+) = xah.close_acct_seq_version_id
96       AND xte.application_id = xe.application_id
97       AND xte.legal_entity_id = le.legal_entity_id(+)
98       AND xe.entity_id = xte.entity_id
99       AND xe.event_id = xah.event_id
100       AND gbv.budget_version_id(+) = xah.budget_version_id
101       AND xah.application_id = xe.application_id
102       AND xet.application_id = xe.application_id
103       AND xet.event_type_code = xe.event_type_code
104       AND xet.language = USERENV ('LANG')
105       AND xect.application_id = xe.application_id
106       AND xect.entity_code = xet.entity_code
107       AND xect.event_class_code = xet.event_class_code
108       AND xect.language = USERENV ('LANG')
109       AND xe.event_id = :1
110       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.

SQL Querysql
1SELECT *
2  FROM xla_ae_lines xlal, xla_ae_headers xlah
3 WHERE     xlal.ae_header_id = xlah.ae_header_id
4       AND xlah.ae_header_id = :ae_header_id

Related posts: