Wednesday, 19 August 2015

GL Entries with the AR Transaction (Invoices) Query in oracle apps

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 (rct.trx_number) trx_num, rct.trx_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,
ra_customer_trx_all rct
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 = 'TRANSACTIONS'
AND xte.source_id_int_1 = rct.customer_trx_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