Wednesday, 19 August 2015

AR Receipts to GL link in oracle R12

/* Formatted on 8/19/2015 6:17:53 PM (QP5 v5.240.12305.39446) */
SELECT glp.start_date,
       gjh.je_header_id,
       gjh.doc_sequence_value voucher_no,
       gjh.je_source,
       gjh.je_category,
       entity_code,
       gjh.period_name,
       gjh.status,
       gjh.actual_flag,
       gjh.default_effective_date,
       gjl.je_line_num,
       gjl.code_combination_id,
       gjl.description voucher_desc,
       xal.accounted_dr debit,
       xal.accounted_cr credit,
          gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
          account_code,
       TO_CHAR (acr.receipt_number) trx_num,
       acr.receipt_date trx_date
  FROM gl.gl_je_headers gjh,
       gl.gl_je_lines gjl,
       gl.gl_code_combinations gcc,
       gl.gl_periods glp,
       gl.gl_import_references imp,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       xla.xla_events xe,
       xla.xla_transaction_entities xte,
       ar_cash_receipts_all acr
 WHERE     1 = 1
       AND gjh.je_header_id = gjl.je_header_id
       AND gjl.status || '' = 'P'
       AND gjl.code_combination_id = gcc.code_combination_id
       AND gjh.period_name = glp.period_name
       AND glp.period_set_name = :p_period_set_name
       AND glp.adjustment_period_flag <> 'Y'
       AND gjh.je_source = 'Receivables'
       AND gjl.je_header_id = imp.je_header_id
       AND gjl.je_line_num = imp.je_line_num
       AND imp.gl_sl_link_id = xal.gl_sl_link_id
       AND imp.gl_sl_link_table = xal.gl_sl_link_table
       AND xal.application_id = xah.application_id
       AND xal.ae_header_id = xah.ae_header_id
       AND xah.application_id = xe.application_id
       AND xah.event_id = xe.event_id
       AND xe.application_id = xte.application_id
       AND xte.application_id = 222
       AND xe.entity_id = xte.entity_id
       AND xte.entity_code = 'RECEIPTS'
       AND xte.source_id_int_1 = acr.cash_receipt_id
       AND gjh.default_effective_date BETWEEN :p_period_from_start_date
                                          AND :p_period_to_end_date
       AND (gjh.actual_flag = :p_actual_flag OR :p_actual_flag IS NULL)

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete