Sunday, 11 January 2015

AR Adjustments Query: AR 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.gl_code_combinations glc,
               apps.fnd_flex_values fv,
               apps.fnd_flex_values_tl fvt
         WHERE glc.code_combination_id = gjl.code_combination_id
           AND glc.segment4 = fv.flex_value
           AND fv.flex_value_set_id = 1002653
           AND fv.flex_value_id = fvt.flex_value_id) code_combo_desc,
       gjh.posted_date posted_on_dt, gjh.je_source, gjh.je_category,
       gjb.NAME je_batch_name, gjh.NAME document_name, '' je_seq_name,
       '' je_seq_num, gjl.je_line_num, gjl.description je_line_desc,
       xal.entered_cr global_cr, xal.entered_dr global_dr,
       xal.currency_code global_cur, ac.customer_name vendor_customer,
       rcta.trx_number transaction_number, rcta.trx_date transaction_date,
       xal.accounting_class_code transaction_type, xal.accounted_cr local_cr,
       xal.accounted_dr local_dr, gl.currency_code local_cur,
       (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)
       ) transaction_amount,
       gl.currency_code transaction_curr_code, 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.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       apps.gl_code_combinations glcc,
       xla.xla_transaction_entities xte,
       apps.ar_adjustments_all ada,
       apps.ra_customer_trx_all rcta,
       apps.gl_ledgers gl,
       apps.gl_balances gb,
       apps.ar_customers ac,
       apps.gl_je_batches gjb
 WHERE 1 = 1
   AND gjh.je_header_id = gjl.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.code_combination_id = glcc.code_combination_id
   AND xte.entity_id = xah.entity_id
   AND xte.entity_code = 'ADJUSTMENTS'
   AND xte.ledger_id = gl.ledger_id
   AND xte.application_id = 222
   AND NVL (xte.source_id_int_1, -99) = ada.adjustment_id
   AND ada.customer_trx_id = rcta.customer_trx_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 gb.ledger_id = gl.ledger_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND rcta.bill_to_customer_id = ac.customer_id(+)
   AND gjh.period_name IN ('NOV-14')
   AND glcc.segment4 = '20331'
   AND gjh.je_source = 'Receivables'

3 comments:

  1. I cannot thank lemeridian funding service enough and letting people know how grateful I am for all the assistance that you and your team staff have provided and I look forward to recommending friends and family should they need financial advice or assistance @ 1,9% Rate for Business Loan .Via Contact : . lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. Keep up the great work.
    Thanks, Busarakham.

    ReplyDelete
  2. Hi, Is there any other link to find the active record in ar adjustments table. every transaction reversals is storing in this ar adjustments table. I need active record among them.

    ReplyDelete