[转]Oracle EBS R12GL追溯子模块
--资产 FROM XLA_FA_AEL_GL_V XLA_AEL_GL_V WHERE application_id = 140 AND je_header_id = 276683 FROM XLA_FA_AEL_GL_V XLA_AEL_GL_V WHERE application_id = 140 AND je_header_id = 276684 --应付 FROM XLA_AP_INV_AEL_GL_V XLA_AEL_GL_V WHERE application_id = 200 AND je_header_id = 272724 FROM XLA_AP_PAY_AEL_GL_V XLA_AEL_GL_V WHERE application_id = 200 AND je_header_id = 272181 FROM SQ_GL_JE_BATCHES_HEADERS_V WHERE set_of_books_id = 1 and (JE_CATEGORY = 'Purchase Invoices') and (JE_SOURCE = 'Payables') --应收 发票 FROM XLA_AR_INV_AEL_GL_V XLA_AEL_GL_V WHERE application_id = 222 AND je_header_id = 272056 核销 FROM XLA_AR_REC_AEL_GL_V XLA_AEL_GL_V WHERE application_id = 222 AND je_header_id = 278535 AND je_line_num = 1 收款 FROM XLA_AR_REC_AEL_GL_V XLA_AEL_GL_V WHERE application_id = 222 AND je_header_id = 277989
The real tables (they're infact views) which store this information are : Oracle Payables : ----------------- XLA_AP_INV_AEL_GL_V - Payable invoice entries (Gen. Ledger) XLA_AP_INV_AEL_SL_V - Payable invoice entries (Subledger) XLA_AP_PAY_AEL_GL_V - Payment voucher entries (Gen.Ledger) XLA_AP_PAY_AEL_SL_V - Payment voucher entries (Subledger) Oracle Receivables : -------------------- XLA_AR_ADJ_AEL_SL_V - AR adjustment entries (Subledger) XLA_AR_INV_AEL_SL_V - AR invoices (Sugledger) XLA_AR_REC_AEL_SL_V - AR receipts (Subledger) XLA_AR_ADJ_AEL_GL_V - AR adjustment entries (Gen.Ledger) XLA_AR_INV_AEL_GL_V - AR invoices (Gen.Ledger) XLA_AR_REC_AEL_GL_V - AR receipts (Gen.Ledger) For the rest of the modules, you can check out the XLA% views or synonyms in APPS schema. The form displays information from these views/synonyms and in the Record History, shows it as XLA_AEL_GL_V.
How to drilldown to the following Source/Categories via SQL: 1. Payables/Payments 2. Purchasing/Receiving 3. Inventory/MTL Solution 1. Payables/Payments: SELECT * FROM apps.gl_je_lines gje, apps.gl_je_headers gjh, apps.ap_invoice_distributions_all apid, ap_invoices_all apa, ap_ae_lines_all apae WHERE apae.GL_SL_LINK_ID = gje.GL_SL_LINK_ID -- this provides drill back to AP and gje.je_header_id = gjh.je_header_id and gjh.NAME = 'Purchase Invoices USD DD-MMM-YY' -- <--Journal name AND apae.REFERENCE5 = 'XX 100506' -- invoice number and apae.REFERENCE2 = apid.INVOICE_ID AND apid.invoice_id = apa.invoice_id and apae.SOURCE_ID = apid.INVOICE_DISTRIBUTION_ID -- ties distrib table to GL AND gjh.actual_flag = 'A' ORDER BY gje.je_line_num 2. Purchasing/Receiving: select * from apps.gl_je_lines gje, apps.gl_je_headers gjh, po_headers_all poh, po_distributions_all pod, po_lines_all poll, apps.rcv_transactions rcvt where gje.reference_2 = to_char(poh.PO_HEADER_ID) and gje.REFERENCE_3 = to_char(pod.PO_DISTRIBUTION_ID) and pod.PO_LINE_ID = poll.PO_LINE_ID and gje.REFERENCE_5 = to_char(rcvt.TRANSACTION_ID) and gje.JE_HEADER_ID = gjh.JE_HEADER_ID and gjH.ACTUAL_FLAG = 'A' and gjh.PERIOD_NAME = 'MMM-YY' -- and gjh.NAME = 'Receiving USD DD-MMM-YY' -- header id is 120597 order by gje.JE_LINE_NUM 3. Inventory/MTL: select * from apps.gl_je_lines gje, apps.gl_je_headers gjh, apps.mtl_material_transactions mmt where gje.REFERENCE_3 = to_char(mmt.TRANSACTION_ID) and gje.JE_HEADER_ID = gjh.JE_HEADER_ID and gjH.ACTUAL_FLAG = 'A' and gjh.NAME = 'MTL USD DD-MMM-YY' -- je_header_id = 116944 ; ref 3 = 6092631 order by gje.JE_LINE_NUM
用表查吧，主要这些表就可以了，xla.xla_ae_headers，xla.xla_ae_lines，xla.xla_transaction_entities，xla_events （用的少） 参考追溯AP注意修改子模块的表和付款例子xte.entity_code 值得。(这里值为'AP_PAYMENTS'） select ac.check_id, ac.check_date, ac.check_number, gcc.segment1, gcc.segment3, ac.amount, xl.accounted_dr, xl.accounted_cr, xte.entity_code, gir.je_header_id, gjh.period_name, xh.event_type_code from xla.xla_ae_headers xh, xla.xla_ae_lines xl, xla.xla_transaction_entities xte, gl.gl_code_combinations gcc, ap.ap_checks_all ac, gl.gl_import_references gir, gl.gl_je_headers gjh, gl.gl_je_batches gjb, xla.xla_events xea where xh.ae_header_id = xl.ae_header_id and xte.entity_id = xh.entity_id and ac.check_id = xte.Source_Id_Int_1(+) and gir.gl_sl_link_id(+) = xl.gl_sl_link_id and gir.je_header_id = gjh.je_header_id(+) and xl.code_combination_id = gcc.code_combination_id and gjh.je_batch_id = gjb.je_batch_id and xea.event_id = xte.entity_id and xte.entity_code = 'AP_PAYMENTS'
Payables data in Sub-Ledger Accounting (XLA) - R12
Payment data in Sub-Ledger Accounting (XLA) - R12 ~ Oracle Apps Knowledge Sharing In this post, we will check the Payment Data related to the Payable INVOICE (Invoice_id = 166014) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 Instance.
XLA_EVENTS SELECT DISTINCTxe.* FROM ap_invoice_payments_all aip, xla_events xe, xla.xla_transaction_entities xte WHERExte.application_id=200 AND xte.application_id =xe.application_id AND aip.invoice_id ='166014' AND xte.entity_code ='AP_PAYMENTS' AND xte.source_id_int_1 =aip.check_id AND xte.entity_id =xe.entity_id ORDERBY xe.entity_id, xe.event_number;
XLA_AE_HEADERS SELECTDISTINCTxeh.* FROM xla_ae_headers xeh, ap_invoice_payments_all aip, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND xte.application_id =xeh.application_id AND aip.invoice_id ='166014' AND xte.entity_code ='AP_PAYMENTS' AND xte.source_id_int_1 =aip.check_id AND xte.entity_id =xeh.entity_id ORDERBY xeh.event_id, xeh.ae_header_idASC;
XLA_AE_LINES SELECTDISTINCTxel.*, fnd_flex_ext.get_segs('SQLGL','GL#','50577',xel.code_combination_id)"Account" FROM xla_ae_lines xel, xla_ae_headers xeh, ap_invoice_payments_all aip, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND xel.application_id =xeh.application_id AND xte.application_id =xeh.application_id AND aip.invoice_id ='166014' AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND xte.source_id_int_1 =aip.check_id AND xte.entity_id =xeh.entity_id ORDERBY xel.ae_header_id, xel.ae_line_numASC;
XLA_DISTRIBUTION_LINKS
SELECTDISTINCTxdl.* FROM xla_distribution_links xdl, xla_ae_headers xeh, ap_invoice_payments_all aip, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND xdl.application_id =xeh.application_id AND xte.application_id =xeh.application_id AND aip.invoice_id ='166014' AND xdl.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_PAYMENTS' AND xte.source_id_int_1 =aip.check_id AND xte.entity_id =xeh.entity_id In this post, we will check the Data related to the Payable INVOICE (Invoice_id =166014) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 Instance.
XLA_EVENTS
SELECTDISTINCTxe.* FROM ap_invoices_all ai, xla_events xe, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND xte.application_id =xe.application_id AND ai.invoice_id ='166014' AND xte.entity_code ='AP_INVOICES' AND xte.source_id_int_1 =ai.invoice_id AND xte.entity_id =xe.entity_id ORDERBY xe.entity_id, xe.event_number;
XLA_AE_HEADERS SELECTDISTINCTxeh.* FROM xla_ae_headers xeh, ap_invoices_all ai, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND xte.application_id =xeh.application_id AND ai.invoice_id ='166014' AND xte.entity_code ='AP_INVOICES' AND xte.source_id_int_1 =ai.invoice_id AND xte.entity_id =xeh.entity_id ORDERBY xeh.event_id, xeh.ae_header_idASC;
XLA_AE_LINES
SELECTDISTINCTxel.*, fnd_flex_ext.get_segs('SQLGL','GL#','50577',xel.code_combination_id)"Account" FROM xla_ae_lines xel, xla_ae_headers xeh, ap_invoices_all ai, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND xel.application_id =xeh.application_id AND xte.application_id =xeh.application_id AND ai.invoice_id ='166014' AND xel.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND xte.source_id_int_1 =ai.invoice_id AND xte.entity_id =xeh.entity_id ORDERBY xel.ae_header_id, xel.ae_line_numASC;
XLA_DISTRIBUTION_LINKS
SELECTDISTINCTxdl.* FROM xla_distribution_links xdl, xla_ae_headers xeh, ap_invoices_all ai, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND xdl.application_id =xeh.application_id AND xte.application_id =xeh.application_id AND ai.invoice_id ='166014' AND xdl.ae_header_id =xeh.ae_header_id AND xte.entity_code ='AP_INVOICES' AND xte.source_id_int_1 =ai.invoice_id AND xte.entity_id =xeh.entity_id ORDERBY xdl.event_id, xdl.a_header_id, xdl.ae_line_numASC;
XLA_TRANSACTION_ENTITIES
SELECTDISTINCTxte.* FROM ap_invoices_all ai, xla.xla_transaction_entities xte WHERE xte.application_id=200 AND ai.invoice_id ='166014' AND xte.entity_code ='AP_INVOICES' AND xte.source_id_int_1 =ai.invoice_id;
XLA_ACCOUNTING_ERRORS
SELECTDISTINCTxae.* FROM ap_invoices_all ai, xla_events xe, xla.xla_transaction_entities xte, xla_accounting_errors xae WHERE xte.application_id=200 AND xae.application_id =xte.application_id AND xte.application_id =xe.application_id AND ai.invoice_id ='166014' AND xe.event_id =xae.event_id AND xte.entity_code ='AP_INVOICES' AND xte.source_id_int_1 =ai.invoice_id AND xte.entity_id =xe.entity_id;
ap_invoice_distributions_all到xla_ae_lines --ap_invoice_distributions_all到xla_ae_lines. 到了XLA_AE_LINES后,会by科目和描述汇总.所以,不能一对一 select c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num, te.source_id_int_1, te.application_id, te.entity_id, h.je_source, h.je_category, i.gl_date, s.vendor_name, s.segment1 as supplier_no, l.event_class_code as event_class, i.invoice_id, ad.invoice_distribution_id, i.invoice_num as transaction_number, i.invoice_date, initcap(jl.description) description, jl.accounted_dr as debit, jl.accounted_cr as credit, nvl(jl.accounted_dr, 0) - nvl(jl.accounted_cr, 0) net_amount
from apps.gl_je_headers h, apps.gl_je_lines jl, apps.gl_code_combinations c, apps.gl_import_references r, apps.xla_ae_lines al, apps.xla_ae_headers ah, apps.xla_distribution_links l, apps.ap_invoices_all i, apps.ap_invoice_distributions_all ad, apps.ap_suppliers s, apps.xla_events e, apps.xla_transaction_entities te
where jl.je_header_id = h.je_header_id and jl.code_combination_id = c.code_combination_id and al.gl_sl_link_id = r.gl_sl_link_id and al.ae_header_id = ah.ae_header_id and al.application_id = ah.application_id and ah.application_id = e.application_id and ah.event_id = e.event_id and e.application_id = te.application_id(+) and e.entity_id = te.entity_id(+) and r.je_header_id = jl.je_header_id and r.je_line_num = jl.je_line_num and l.ae_header_id = al.ae_header_id and l.ae_line_num = al.ae_line_num and l.applied_to_source_id_num_1 = i.invoice_id and l.source_distribution_id_num_1 = ad.invoice_distribution_id and ad.invoice_id = i.invoice_id and i.vendor_id = s.vendor_id
order by i.gl_date desc
-- 刘轶鹤转自网络