AP_XLA_GL

/* Formatted on 1/2/2012 1:38:15 PM (QP5 v5.115.810.9015) */
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",
         gjh.je_source,
         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 = &Invoice_Id;