GL追溯各子模块

4. GL追溯子模块（各模块表与xla.xla_transaction_entities 表的关联可以参数此表中的关系 xla.xla_entity_id_mappings）

4.1 GL追溯AP发票(关联PO表可以用invoice_id,event_id)
当source是Payables,category 是Purchase Invoices 用这段SQL追溯

SELECT NVL (ael.accounted_dr, ael.entered_dr) account_dr,
                NVL (ael.accounted_cr, ael.entered_cr) account_cr,
                aia.invoice_num,
                aia.invoice_date,
                aia.org_id,
                aia.invoice_id,ael.ae_header_id, ael.ae_line_num,
                aeh.event_id
           FROM gl_je_lines jl,
                gl_je_headers jh,
                gl_import_references gir,
                xla_ae_lines ael,
                xla_ae_headers aeh,
                xla.xla_transaction_entities xte,
                ap_invoices_all aia
          WHERE jl.je_header_id = jh.je_header_id
            AND jl.ledger_id = jh.ledger_id
            AND jl.je_header_id = gir.je_header_id
            AND jl.je_line_num = gir.je_line_num
            AND jl.je_header_id = p_header_id
            AND jl.je_line_num = p_line_num
            AND jh.je_batch_id = gir.je_batch_id
            AND jh.je_category = 'Purchase Invoices'
            AND jh.je_source = 'Payables'
            AND gir.gl_sl_link_id = ael.gl_sl_link_id
            AND gir.gl_sl_link_table = ael.gl_sl_link_table
            AND ael.ae_header_id = aeh.ae_header_id
            AND ael.application_id = aeh.application_id
            AND aeh.entity_id = xte.entity_id
            AND aeh.application_id = xte.application_id
            and xte.ledger_Id = p_ledger_id
            AND xte.entity_code = 'AP_INVOICES'
            AND NVL (xte.source_id_int_1, (-99)) = aia.invoice_id
            AND xte.application_id = 200;

4.2 GL追溯AP付款
当source是Payables,category 是payments,则使用下面这个SQL来取出追朔的数据

SELECT DISTINCT aca.bank_account_name l_ref2,
                   pd.payment_document_name
                || '-'
                || aca.doc_sequence_value l_ref3,
                TO_CHAR (aca.check_date, 'yyyy/mm/dd') l_ref4,
                aca.check_id trx_hdr_id,
                ael.accounting_class_code acct_line_type_name,
                pd.payment_document_name NAME,
                NVL (ael.accounted_cr, ael.entered_cr) account_cr,
                NVL (ael.accounted_dr, ael.entered_dr) account_dr,
                ael.ae_header_id, ael.ae_line_num
           FROM gl_je_lines jl,
                gl_je_headers jh,
                gl_import_references gir,
                xla_ae_lines ael,
                xla_ae_headers aeh,
                xla.xla_transaction_entities xte,
                ap_checks_all aca,
                ce_payment_documents pd,
                po_vendor_sites_all pvs
          WHERE jl.je_header_id = jh.je_header_id
            AND jl.ledger_id = jh.ledger_id
            AND jl.je_header_id = gir.je_header_id
            AND jl.je_line_num = gir.je_line_num
            AND jl.je_header_id = p_header_id
            AND jl.je_line_num = p_line_num
            AND jh.je_batch_id = gir.je_batch_id
            AND jh.je_category = 'P