AP-SLA-GL LINK QUERY

AP-SLA-GL 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",
      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;