GL_会计科目子模组追溯至总账分析（案例）

2014-06-02 BaoXinjian

一、案例

---

需求： 从日记账源头追溯至分类账，再至子模组

1. 查询日记账，进行追溯

2. 常用的SQL

3. E-R图

4. SLA中查询

    (1). 会计事件 

    (2). 子分类账日记账分录 

 

二、案例实现

---

Step1. 查询日记账，进行追溯 

    

 

Step2. 常用的SQL

    2.1 xla_transaction_entities用以连接AP事物实体和XLA的连接表

    2.2 gl_import_references用以连接XLA和GL的连接表 

select invoice_id from ap_invoices_all

where invoice_num = 'BXJ20120904_001'

 

select * from ap_invoice_lines_all

where invoice_id = 2977123

 

select accounting_event_id from ap_invoice_distributions_all

where invoice_id = 2977123

 

select entity_id from xla. xla_events

where event_id = 21380679

 

select source_id_int_1, transaction_number from xla.xla_transaction_entities

where entity_id = 21028756

 

select * from xla. xla_ae_headers

where event_id = 21380679

 

select * from xla. xla_ae_lines

where ae_header_id in ( 121331026, 121331041)

 

select * from xla. xla_distribution_links

where ae_header_id in ( 121331026, 121331041)

 

select    je_batch_id,

            je_header_id ,

            gl_sl_link_id ,

            gl_sl_link_table ,

            reference_5 entity_id ,

            reference_6 accounting_event_id ,

            reference_7 ae_header_id

  from   gl_import_references

 where gl_sl_link_id = 246342532

 

select * from gl_je_lines

where je_header_id = 2720705

and gl_sl_link_id = 246342532

 

select * from gl_je_batches

where je_batch_id = 2694595

 

select * from gl_je_headers

whereje_batch_id = 2694595

 

Step3. E-R图

 

 

Step4. SLA中查询

    (1). 会计事件

        

    (2). 子分类账日记账分录 

            

 

参考 R12 Mapping Between Subledger Tables, SLA and GL Tables [ID 871622.1]

 

Thanks and Regards