Thursday 17 November 2011

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;

1 comment:

  1. 25% OFF on Oracle Apps R12 Financials Self Paced Course along with 11 Additional Add On Courses (321 Session Videos of 120 Hours Recordings). Our Top Trending Course with 1700 Enrolled Udemy Students

    Please Check https://www.oracleappstechnical.com for details

    ReplyDelete