AP-SLA-GL Link Query

SELECT aia.invoice_id "Invoice Id",

      aia.invoice_num "Invoice Number",

       aia.invoice_date "Invoice Date",

      aia.invoice_amount "Amount",

       xal.entered_dr "Entered DR in SLA",

      xal.entered_cr "Entered CR in SLA",

       xal.accounted_dr "Accounted DR in SLA",

       xal.accounted_cr "Accounted CR in SLA",

       gjl.entered_dr "Entered DR in GL",

       gjl.accounted_dr "Accounted DR in GL",

       xal.accounting_class_code "Accounting Class",

      gcc.segment1

       || '.'

       || gcc.segment2

       || '.'

       || gcc.segment3

       || '.'

       || gcc.segment4

       || '.'

       || gcc.segment5

       || '.'

       || gcc.segment6

       || '.'

       || gcc.segment7 "Code Combination",

       aia.invoice_currency_code "Inv Curr Code",

       aia.payment_currency_code "Pay Curr Code", aia.gl_date "GL Date",

       xah.period_name "Period", aia.payment_method_code "Payment Method",

       aia.vendor_id "Vendor Id", aps.vendor_name "Vendor Name",

       xah.je_category_name "JE Category Name"

  FROM apps.ap_invoices_all aia,

       xla.xla_transaction_entities xte,

       apps.xla_events xev,

       apps.xla_ae_headers xah,

       apps.xla_ae_lines xal,

       apps.gl_import_references gir,

       apps.gl_je_headers gjh,

       apps.gl_je_lines gjl,

       apps.gl_code_combinations gcc,

       apps.ap_suppliers aps,

       (SELECT aid1.invoice_id, pa.project_id,

               NVL (pa.segment1, 'NO PROJECT') project

          FROM apps.ap_invoice_distributions_all aid1,

               apps.pa_projects_all pa

         WHERE aid1.ROWID IN (SELECT   MAX (ROWID)

                                  FROM apps.ap_invoice_distributions_all aid2

                                 WHERE aid1.invoice_id = aid2.invoice_id

                              GROUP BY aid1.invoice_id

                        )

           AND aid1.project_id = pa.project_id(+)

      ) sql1,

       (SELECT aid1.invoice_id, pt.task_id,

               NVL (pt.task_number, 'NO TASK') task

          FROM apps.ap_invoice_distributions_all aid1, apps.pa_tasks pt

         WHERE aid1.ROWID IN (SELECT   MAX (ROWID)

                                  FROM apps.ap_invoice_distributions_all aid2

                                 WHERE aid1.invoice_id = aid2.invoice_id

                              GROUP BY aid1.invoice_id

                       )

           AND aid1.task_id = pt.task_id(+)

      ) sql2

 WHERE aia.invoice_id = xte.source_id_int_1

   AND aia.invoice_id = sql1.invoice_id

   AND aia.invoice_id = sql2.invoice_id

   AND xev.entity_id = xte.entity_id

   AND xah.entity_id = xte.entity_id

   AND xah.event_id = xev.event_id

   AND xah.ae_header_id = xal.ae_header_id

   AND xah.je_category_name = 'Purchase Invoices'

   AND xah.gl_transfer_status_code = 'Y'

   AND xal.gl_sl_link_id = gir.gl_sl_link_id

   AND gir.gl_sl_link_table = xal.gl_sl_link_table

   AND gjl.je_header_id = gjh.je_header_id

   AND gjh.je_header_id = gir.je_header_id

   AND gjl.je_header_id = gir.je_header_id

   AND gir.je_line_num = gjl.je_line_num

   AND gcc.code_combination_id = xal.code_combination_id

   AND gcc.code_combination_id = gjl.code_combination_id

   AND aia.vendor_id = aps.vendor_id

   AND gjh.status = 'P'

   AND gjh.actual_flag = 'A'

   AND gjh.currency_code = 'USD'

   AND aia.invoice_id =:p_invoice_id;