Thursday, February 14, 2013

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;
  

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect