Oracle 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 ap.ap_invoices_all aia,

      xla.xla_transaction_entities XTE,

      xla.xla_events xev,

      xla.xla_ae_headers XAH,

      xla.xla_ae_lines XAL,

      GL_IMPORT_REFERENCES gir,

      gl_je_headers gjh,

      gl_je_lines gjl,

      gl_code_combinations gcc,

      ap.ap_suppliers aps,

      (SELECT aid1.invoice_id,

              pa.project_id,

              nvl(pa.segment1, 'NO PROJECT') Project

         FROM ap_invoice_distributions_all aid1, PA_PROJECTS_ALL pa

        WHERE aid1.rowid IN (SELECT MAx(rowid)

                               FROM 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 ap_invoice_distributions_all aid1, PA_TASKS pt

        WHERE aid1.rowid IN (SELECT MAx(rowid)

                               FROM 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 = &Invoice_Id;