[转]Oracle EBS R12 GL追溯子模块
--资产
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
-- 刘轶鹤转自网络
772
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
