Query to Link PO, AP, GL, ZX, CE, RCV & XLA

   SELECT glimp.je_header_id je_header_id, glimp.je_line_num je_line_num,
          xal.accounted_cr accounted_cr, xal.accounted_dr accounted_dr,
          xal.entered_cr entered_cr, xal.entered_dr entered_dr,
          xah.accounting_date
accounting_date
,
          xal.accounting_class_code
acct_line_type
,
          xal.code_combination_id
code_combination_id
,
            
gcc
.segment1
          || ‘.’
          ||
gcc
.segment3
          || ‘.’
          ||
gcc
.segment2
          || ‘.’
          ||
gcc
.segment4
          || ‘.’
          ||
gcc
.segment5 account_combination,
          gcc.segment1 company_code, gcc.segment3 center_code,
          gcc.segment2 account_code, gcc.segment4 subaccount,       
          gcc.segment5 cost_center_type,
          xal.description comments,
          xah.gl_transfer_status_code
gl_transfer_status
,        
          xal.party_id vendor_id,                    
          v.segment1 vendor_number,
          v.vendor_name vendor_name, xal.party_site_id vendor_site_id,
          vs.vendor_site_code
vendor_site_name
,
          c.check_id check_id,
          c.check_number check_number,
          ae.event_number
accounting_event_number
,
          ae.event_type_code
accounting_event_type
,
          xal.ae_line_num accounting_line_number,
          xah.je_category_name je_category,
          jc.user_je_category_name
user_je_category_name
,
          c.currency_code
trx_hdr_currency
,
          xal.last_update_date
last_update_date
,
          xal.last_updated_by
last_updated_by
,
          xal.creation_date creation_date, xal.created_by created_by,
          xal.last_update_login
last_update_login
, xal.request_id request_id,
          c.bank_account_name
bank_account_name            
          i.invoice_num
invoice_number
,                                     
          i.invoice_currency_code
invoice_currency
,
          d.distribution_line_number
invoice_line_number
,
          d.line_type_lookup_code
invoice_line_type
,
          ph.segment1 po_order_number, pr.release_num
po_order_release_num
,
          rsh.receipt_num rcv_receipt_num
     FROM xla_ae_headers xah,             
         
xla_ae_lines xal
,                
         
gl_import_references glimp
,
         
gl_je_headers glh
,               
         
gl_je_lines gll
,                 
         
gl_code_combinations gcc
,
         
gl_daily_conversion_types glct
,
         
gl_je_categories jc
,
         
ap_checks_all c
,
         
ap_invoices_all i
,
         
ap_invoice_payments_all
p,
         
ap_invoice_distributions_all d
,
          ap_invoice_lines_all l,
         
ap_accounting_events_all ae
,
         
po_vendor_sites_all vs
,
         
po_vendors v
,
         
po_distributions_all pd
,
         
po_headers_all ph
,
         
po_releases_all pr
,
         
ce_statement_lines sl
,
          ce_statement_headers sh,
         
rcv_transactions rct
,
         
rcv_shipment_headers rsh
,
         
ce_statement_reconciliations csr
,
         
zx_lines_summary_v zls
,
         
zx_rates_vl zr
,
         
fnd_document_sequences fd
,
         
fnd_document_sequences fd2
,
         
fnd_flex_values_vl ffvv1
,
         
ap_lookup_codes l1
    WHERE xal.application_id = xah.application_id
      AND xah.ae_header_id = xal.ae_header_id
      AND glimp.je_header_id = glh.je_header_id
      AND glimp.je_line_num = gll.je_line_num
      AND glh.je_header_id = gll.je_header_id
      AND glimp.gl_sl_link_id = xal.gl_sl_link_id
      AND gcc.code_combination_id = xal.code_combination_id
      AND xal.currency_conversion_type = glct.conversion_type(+)
      AND jc.je_category_name = xah.je_category_name
      AND d.invoice_id = i.invoice_id
      AND i.invoice_id = p.invoice_id
      AND c.check_id = ae.source_id
      AND c.check_id = p.check_id
     AND ae.accounting_event_id = xah.event_id
      AND xal.party_id = v.vendor_id
      AND xal.party_site_id = vs.vendor_site_id
      AND ph.po_header_id(+) = pd.po_header_id
      AND pd.po_distribution_id(+) = d.po_distribution_id
      AND pr.po_release_id(+) = pd.po_release_id
      AND sl.statement_header_id = sh.statement_header_id(+)
      AND csr.statement_line_id = sl.statement_line_id(+)
      AND rsh.shipment_header_id(+) = rct.shipment_header_id
      AND d.rcv_transaction_id = rct.transaction_id(+)
      and l.invoice_id=i.invoice_id
      and l.line_number=1
      AND csr.reference_type(+) = ‘PAYMENT’
      AND csr.current_record_flag(+) = ‘Y’
      AND gll.je_header_id(+) = csr.je_header_id
      AND c.check_id = csr.reference_id(+)
      AND i.invoice_id = zls.trx_id(+)
      AND zls.tax_rate_id = zr.tax_rate_id(+)
      AND c.doc_sequence_id = fd.doc_sequence_id(+)
      AND sh.doc_sequence_id = fd2.doc_sequence_id(+)
      AND ae.source_table = ‘AP_CHECKS’   
      AND l1.lookup_code = c.payment_method_lookup_code
      AND l1.lookup_type = ‘PAYMENT METHOD’
      AND DECODE (xal.source_table,
                 
‘AP_INVOICE_PAYMENTS’, xal.source_id,
                 
NULL
                
) = p.invoice_payment_id
      and l.line_number=d.invoice_line_number  
      
      Dhiraj
Jha
 
   
  • October 14, 2015 | 31 views