--基础事件关系图
Select * From xla_entity_types_vl; --事件实体
Select * From xla_entity_id_mappings;--实体ID对应表
Select * From xla_event_classes_vl;--事件分类
Select * From xla_event_types_vl; --事件类型
===================================
--子分类帐关系图
Select * From xla_transaction_entities;--会计事物处理实体
Select * From xla_events ; --会计事件
Select * From xla_ae_headers;--子帐头
Select * From xla_ae_lines;--子帐行
Select * From xla_distribution_links;--关联事物处理信息
===================================
--子模块和ＧＬ关系图
Select * From gl_import_references;---（总帐参考）
总帐关联(gl_sl_link_id,gl_sl_link_table)
子分类账的xla_ae_lines（子帐行）
说明：GL和子模块之间的关联是通过gl_import_reference实现的，关键字段是gl_sl_link_id,gl_sl_link_table。
GL->子模块追溯
------------------------------------------------
Select Xal.Ae_Header_Id, Xal.Ae_Line_Num, Jh.Je_Source
From Gl_Je_Lines Jl,
Gl_Je_Headers Jh,
Gl_Import_References Gir,
Xla_Ae_Lines Xal
Where Jl.Je_Header_Id = Gir.Je_Header_Id
And Jh.Je_Header_Id = Jl.Je_Header_Id
And Jl.Je_Line_Num = Gir.Je_Line_Num
And Gir.Gl_Sl_Link_Id = Xal.Gl_Sl_Link_Id
And Gir.Gl_Sl_Link_Table = Xal.Gl_Sl_Link_Table
And Jl.Je_Header_Id = :1
And Jl.Je_Line_Num = :2
--根据je_header_id找到相应的会计实体，主要是需要实体代码和几个source_id
--通过source_id…和entity_code的组合判断，可以准确的追溯到具体的事物处理
Select Xte.Entity_Code,
Xte.Source_Id_Int_1,
Xte.Source_Id_Int_2,
Xte.Source_Id_Int_3,
Xte.Source_Id_Int_4,
Xte.Source_Id_Char_1,
Xte.Source_Id_Char_2,
Xte.Source_Id_Char_3,
Xte.Source_Id_Char_4,
Xte.Security_Id_Int_1,
Xte.Security_Id_Int_2,
Xte.Security_Id_Int_3
From Xla.Xla_Transaction_Entities Xte, Xla_Ae_Headers Xah
Where 1 = 1
And Xah.Ae_Header_Id = :1
And Xte.Entity_Id = Xah.Entity_Id
And Xte.Application_Id = Xah.Application_Id
===================================
--根据日记帐来源查询xla_subledgers表获得drilldown的程序
--由于这部分是写死的，因此，对程序员来说，只能做参考
--至于怎么写的灵活和通用，还需要参考琢磨写成一个通用的动态SQL
Select Xs.Drilldown_Procedure_Name, Xs.Application_Id
From Xla.Xla_Subledgers Xs
Where Xs.Je_Source_Name = :Je_Source_Name
And Xs.Application_Id = :Application_Id
--上面的信息查询出来后，组合成一个动态SQL，返回一个准确的结果集
--当然，通常情况下，我们都没有考虑写成通用程序，因此可以写死是
--哪些会计事件，会计实体代码
---==========================================
Select Cr.Cash_Receipt_Id Cash_Receipt_Id,
Cr.Document_Number Gather_Num,
Jh.Doc_Sequence_Value Doc_Sequence_Value,
Cr.Customer_Name Customer_Name,
Cr.Remit_Bank_Branch Bank_Name,
Cr.Remit_Bank_Account Bank_Account,
Cr.Receipt_Number Receipt_Number,
Cr.Amount Amount,
Cr.State_Dsp State_Dsp,
Trunc(h.Accounting_Date) ,
h.Event_Type_Code Event_Type_Code
From Xla_Ae_Lines l,
Xla_Ae_Headers h,
Xla.Xla_Transaction_Entities Te,
Gl_Import_References Ir,
Gl_Je_Headers Jh,
Ar_Cash_Receipts_v Cr
Where Cr.Cash_Receipt_Id = Te.Source_Id_Int_1(+)
And Cr.Currency_Code = &p_Currency
And Te.Entity_Code(+) = 'RECEIPTS'
And Te.Entity_Id = h.Entity_Id(+)
And Te.Application_Id = h.Application_Id(+)
And h.Ae_Header_Id = l.Ae_Header_Id(+)
And h.Application_Id = l.Application_Id(+)
And l.Gl_Sl_Link_Table = Ir.Gl_Sl_Link_Table(+)
And l.Gl_Sl_Link_Id = Ir.Gl_Sl_Link_Id(+)
And Ir.Je_Header_Id = Jh.Je_Header_Id(+)
And l.Ae_Line_Num(+) = 1
And h.Accounting_Entry_Status_Code(+) = 'F'
And h.Accounting_Date Between Trunc(&p_Start_Date) And
(trunc(&p_End_Date) + 86399 / 8640);
Select * From Ar_Cash_Receipts_v;
Begin
mo_global.init('M');
End;
Select * From xla_ae_headers xal Where xal.ae_header_id=32263;
Select * From xla_ae_lines xa Where xa.ae_header_id=32263;
Select * From ap_invoices_all ai Where ai.invoice_id=36293 And ai.org_id=114