Friday, 10 October 2014

Query to Link AP Invoices, GL and SLA Tables: R12 AP GL XLA

SELECT   SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount,
         invoice_num
    FROM ap_invoices_all aia,
         xla.xla_transaction_entities xte,
         xla_ae_headers xah,
         xla_ae_lines xal,
         gl.gl_import_references gir,
         gl_je_lines gjl,
         gl_je_headers gjh
   WHERE 1 = 1
     AND aia.invoice_id = NVL ("SOURCE_ID_INT_1", (-99))
     AND xte.entity_code = 'AP_INVOICES'
     AND xte.application_id = 200
     AND xte.entity_id = xah.entity_id
     AND xah.ae_header_id = xal.ae_header_id
     AND xal.gl_sl_link_id = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
     AND aia.invoice_num = 'INVNO4567'   --Invoice Num
GROUP BY aia.invoice_num, aia.invoice_amount


4 comments:

  1. you can also see the below link:
    http://www.oracleappshelper.co.in/index.php/2015/07/15/ap-to-gl-link-through-sla-in-r12/

    ReplyDelete
  2. The above link had suspended...

    ReplyDelete
  3. select distinct ai.INVOICE_NUM,gjb.NAME batch_name,gjh.NAME journal_name,gjh.*,ai.* from
    fusion.gl_je_batches gjb
    ,fusion.GL_JE_HEADERS gjh
    ,fusion.GL_IMPORT_REFERENCES GR
    ,fusion.XLA_AE_HEADERS xh , fusion.XLA_AE_LINES xl
    ,fusion.ap_invoice_distributions_all aid
    ,fusion.ap_invoices_all ai
    where
    gjb.JE_BATCH_ID=gr.je_batch_id
    and gjh.je_header_id=gr.je_header_id
    and gr.GL_SL_LINK_ID=xl.GL_SL_LINK_ID
    and xh.AE_HEADER_ID=xl.AE_HEADER_ID
    and xh.EVENT_ID =aid.ACCOUNTING_EVENT_ID
    and ai.invoice_id=aid.INVOICE_ID
    and INVOICE_NUM='009-0041073261';


    --- invoices in specific batch

    select distinct ai.INVOICE_NUM,gjb.NAME batch_name,gjh.NAME journal_name,gjh.*,ai.* from
    fusion.gl_je_batches gjb
    ,fusion.GL_JE_HEADERS gjh
    ,fusion.GL_IMPORT_REFERENCES GR
    ,fusion.XLA_AE_HEADERS xh , fusion.XLA_AE_LINES xl
    ,fusion.ap_invoice_distributions_all aid
    ,fusion.ap_invoices_all ai
    where
    gjb.JE_BATCH_ID=gr.je_batch_id
    and gjh.je_header_id=gr.je_header_id
    and gr.GL_SL_LINK_ID=xl.GL_SL_LINK_ID
    and xh.AE_HEADER_ID=xl.AE_HEADER_ID
    and xh.EVENT_ID =aid.ACCOUNTING_EVENT_ID
    and ai.invoice_id=aid.INVOICE_ID
    and gjb.NAME='Payables A 47789 1895164 2 N' ;

    ReplyDelete
  4. 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