Friday, December 26, 2014

AP Invoice Details Query (Till GL)


SELECT aida.period_name,
       aia.invoice_num ap_inv_numer,
       aia.invoice_date ap_inv_date,
       aia.invoice_amount ap_inv_amount,
       aia.amount_paid ap_inv_amt_paid,
       aia.accts_pay_code_combination_id ap_inv_accts_pay_ccid,
       aida.amount ap_inv_dist_amount,
       aida.dist_code_combination_id ap_inv_dist_ccid,
          gcc1.segment1
       || '.'
       || gcc1.segment2
       || '.'
       || gcc1.segment3
       || '.'
       || gcc1.segment4
       || '.'
       || gcc1.segment5
       || '.'
       || gcc1.segment6
       || '.'
       || gcc1.segment7
       || '.'
       || gcc1.segment8 ap_inv_dist_code_combination,
       gjh.NAME gl_je_header_name,
       gjh.description gl_je_header_desc,
       gjl.je_line_num,
       gjl.code_combination_id gl_je_line_ccid
  FROM ap.ap_invoices_all aia,
       ap.ap_invoice_distributions_all aida,
       ap.ap_invoice_payments_all aipa,
       gl.gl_code_combinations gcc1,
       gl.gl_ledgers gl,
       xla.xla_transaction_entities xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       xla.xla_distribution_links xdl,
       gl.gl_import_references gir,
       gl.gl_je_headers gjh,
       gl.gl_je_lines gjl
 WHERE 1 = 1
   AND aia.org_id = :P_ORG_ID
   AND aia.SOURCE = :P_INVOICE_SOURCE
   AND aida.period_name = :P_PERIOD_NAME
   AND aia.invoice_id = :P_INVOICE_ID
   AND aida.invoice_id = aia.invoice_id
   AND gcc1.code_combination_id = aida.dist_code_combination_id
   AND aipa.invoice_id = aia.invoice_id
   AND gl.short_name = :P_LEDGER_SHORT_NAME
   AND gl.chart_of_accounts_id = gcc1.chart_of_accounts_id
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'AP_INVOICES'
   AND xte.source_id_int_1 = aia.invoice_id
   AND xah.entity_id = xte.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
   AND xdl.applied_to_entity_code = 'AP_INVOICES'
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gjh.je_header_id = gir.je_header_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_line_num = gir.je_line_num;
 
Pass / Comment based on available values.

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete