Thursday, March 3, 2016

GL Entries with AP Payments Query in oracle apps AP to GL

GL Entries with AP Payments Query in oracle apps


ALso  OM to GL http://appserptechnical.blogspot.in/2015/04/o2c-query.html
/* Formatted on 8/19/2015 6:24:14 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 (aca.check_number) trx_num,
       aca.check_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,
       ap.ap_checks_all aca
 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 = 'Payables'
       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 = 200
       AND xe.entity_id = xte.entity_id
       AND xte.entity_code = 'AP_PAYMENTS'
       AND xte.source_id_int_1 = aca.check_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)

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...