Sunday 11 January 2015

AP Distribution Query: AP XLA GL

SELECT glcc.segment1 company, glcc.segment2 LOCATION,
       glcc.segment3 cost_center, glcc.segment4 ACCOUNT,
       glcc.segment5 product_line, glcc.segment6 channel,
       glcc.segment7 project,
       (SELECT flex_value || ' ' || fvt.description
          FROM apps.ap_invoices_all ap1,
               apps.gl_code_combinations glc,
               apps.fnd_flex_values fv,
               apps.fnd_flex_values_tl fvt
         WHERE aia.accts_pay_code_combination_id = glc.code_combination_id
           AND glc.segment4 = fv.flex_value
           AND fv.flex_value_set_id = 1002653
           AND ap1.invoice_id = aia.invoice_id
           AND fv.flex_value_id = fvt.flex_value_id) code_combo_desc,
       gjh.posted_date posted_on_dt,
       CAST (TO_CHAR (TO_DATE (gjh.posted_date, 'DD-MM-YY'), 'YYYYMMDD') AS NUMBER
            ) AS month_dt_wid,
       gjh.je_category, gjh.je_header_id gl_journal_id,
       gjh.NAME document_name, gjh.external_reference acct_je_line_desc,
       xal.description je_line_desc, pv.vendor_name vendor_customer,
       pv.segment1 vendor_number,
       (SELECT pap.employee_number
          FROM apps.per_all_people_f pap
         WHERE pap.person_id = pv.employee_id AND ROWNUM = 1) employee_number,
       xal.accounting_class_code transaction_type,
       aia.invoice_num transaction_number, '' mod_transaction_number,
       CASE
          WHEN xal.entered_dr IS NULL
             THEN gjl.entered_dr
          ELSE xal.entered_dr
       END global_dr,
       CASE
          WHEN xal.entered_cr IS NULL
             THEN gjl.entered_cr
          ELSE xal.entered_cr
       END global_cr,
       xal.currency_code global_cur,
       CASE
          WHEN xal.accounted_cr IS NULL
             THEN gjl.accounted_cr
          ELSE xal.accounted_cr
       END local_cr,
       CASE
          WHEN xal.accounted_dr IS NULL
             THEN gjl.accounted_dr
          ELSE xal.accounted_dr
       END local_dr,
       aida.amount transaction_amount,
       aia.invoice_currency_code transaction_curr_code,
       gl.currency_code local_cur, gjh.period_name fiscal_period,
       (gb.begin_balance_dr - gb.begin_balance_cr) begin_balance,
       (  gb.period_net_dr
        - gb.period_net_cr
        + gb.project_to_date_dr
        - gb.project_to_date_cr
       ) end_balance,
       gl.NAME ledger_name
  FROM apps.ap_invoices_all aia,
       apps.ap_invoice_lines_all aila,
       apps.ap_invoice_distributions_all aida,
       apps.xla_events xe,
       apps.xla_distribution_links xdl,
       xla.xla_transaction_entities xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       apps.gl_code_combinations glcc,
       apps.gl_import_references gir,
       apps.gl_je_lines gjl,
       apps.gl_je_headers gjh,
       apps.ap_suppliers pv,
       apps.gl_ledgers gl,
       apps.gl_balances gb
 WHERE 1 = 1
   AND aia.vendor_id = pv.vendor_id
   AND xte.entity_id = xah.entity_id
   AND xte.ledger_id = gl.ledger_id
   AND aia.invoice_id = aila.invoice_id
   AND aila.invoice_id = aida.invoice_id
   AND aila.line_number = aida.invoice_line_number
   AND NVL (xte.source_id_int_1, -99) = aia.invoice_id
   AND xte.application_id = 200
   AND xte.entity_code = 'AP_INVOICES'
   AND xah.ae_header_id = xal.ae_header_id
   AND xah.application_id = xal.application_id
   AND xal.ledger_id = gjh.ledger_id
   AND xal.application_id = xte.application_id
   AND glcc.code_combination_id = gjl.code_combination_id
   AND xal.gl_sl_link_id = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjh.je_header_id = gjl.je_header_id
   AND gjh.ledger_id = xal.ledger_id
   AND gjh.ledger_id = gl.ledger_id
   AND gb.code_combination_id = glcc.code_combination_id
   AND gb.period_name = gjh.period_name
   AND gb.currency_code = gl.currency_code
   AND xal.ae_header_id = xdl.ae_header_id
   AND xal.ae_line_num = xdl.ae_line_num
   AND xdl.source_distribution_type = 'AP_INV_DIST'
   AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
   AND xdl.event_id = xe.event_id
   AND xe.application_id = xte.application_id
   AND gjh.period_name = 'OCT-14'
   AND glcc.segment4 = '20600'

1 comment: