转：ap_invoice_distributions_all到xla_ae_lines

--ap_invoice_distributions_all到xla_ae_lines. 到了XLA_AE_LINES后,会by科目和描述汇总.所以,不能一对一
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

转载于:https://www.cnblogs.com/everkitty/archive/2012/01/13/2321823.html