ap_invoice_distributions_all到xla_ae_lines

来源：https://www.cnblogs.com/benio/archive/2011/01/01/1923697.html

 

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 /*ad.invoice_id = 10194
      
   and*/ 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