Sunday, 11 January 2015

AR Receipt Distribution Query: AR XLA GL

SELECT glcc.segment1 co, glcc.segment2 loc, glcc.segment3 cc,
       glcc.segment4 acct, glcc.segment5 prod, glcc.segment6 chan,
       glcc.segment7 proj, '' "CODECOMBO desc", xal.accounting_date,
       gjh.je_source, gjh.je_category CATEGORY, gjb.NAME batch,
       gjh.NAME "JOURNAL name", '' "JE SEQ name", '' "JE SEQ NUM",
       gjl.je_line_num "JE LINE", gjl.description "JE LINE DESCR",
       gjl.accounted_cr gl_cr, gjl.accounted_dr gl_dr, '' "VENDOR/CUSTOMER",
       acra.receipt_number "TRANSACTION NUM",
       xal.accounting_class_code "TRANSACTION TYPE", xal.accounted_cr xla_cr,
       xal.accounted_dr xla_dr, gjh.period_name 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_batches gjb,
       apps.gl_balances gb,
       apps.gl_ledgers gl,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       apps.gl_code_combinations glcc,
       apps.xla_ae_lines xal,
       apps.xla_ae_headers xah,
       apps.xla_distribution_links xdl,
       apps.ar_distributions_all ada,
       apps.ar_cash_receipt_history_all acrha,
       apps.ar_cash_receipts_all acra
 WHERE 1 = 1
   AND gjh.ledger_id = gl.ledger_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gjl.code_combination_id = glcc.code_combination_id
   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.ae_header_id = xdl.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.ae_line_num = xdl.ae_line_num
   AND xal.application_id = xdl.application_id
   AND xdl.source_distribution_type(+) = 'AR_DISTRIBUTIONS_ALL'
   AND xdl.source_distribution_id_num_1(+) = ada.line_id
   AND ada.source_id = acrha.cash_receipt_history_id
   AND acrha.cash_receipt_id = acra.cash_receipt_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 glcc.segment4 = '13010'
   AND gjh.period_name = 'OCT-14'
   AND gl.NAME = 'Amazon.com, Inc.'
   AND gjh.je_source = 'Receivables'
--and gjh.je_category = 'Receipts'
--and GJH.name = 'OCT-14 Receipts USD'

1 comment:

  1. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Expense Reports

    ReplyDelete