ITPub博客

首页 > Linux操作系统 > Linux操作系统 > GL追溯子模块的视图

GL追溯子模块的视图

原创 Linux操作系统 作者:imzh 时间:2011-04-28 14:44:14 0 删除 编辑

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'

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/126211/viewspace-694002/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-30

  • 博文量
    33
  • 访问量
    326193