Home » RDBMS Server » Performance Tuning » Heavy query with GL
Heavy query with GL [message #268668] |
Wed, 19 September 2007 05:39 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
tarmenel
Messages: 63 Registered: February 2007 Location: Israel
|
Member |
|
|
Hi there,
I'm posting this here because this is more SQL related than E-Biz suite related. I'm trying to query the General Ledger tables but it is extremely heavy and not sure if there's something in my query that may be a problem. What I need to do is take two sets of books in the organization and compare them against each other on one line for each line, please could someone take a look and see if I could optimize it:
Select je_headers.Name,
je_headers.doc_sequence_value doc_num,
je_headers.period_name,
gcc.concatenated_segments accnt1001, -- jel1001.code_combination_id,
je_headers.currency_code,
Sum(nvl(jel1001.entered_dr, 0)) - Sum(nvl(jel1001.entered_cr, 0)) entered1001,
Sum(nvl(jel1001.accounted_dr, 0)) - Sum(nvl(jel1001.accounted_cr, 0)) accounted1001,
gcc2009.concatenated_segments accnt2009,
Sum(nvl(jel2009.entered_dr, 0)) - Sum(nvl(jel2009.entered_cr, 0)) entered2009,
Sum(nvl(jel2009.accounted_dr, 0)) - Sum(nvl(jel2009.accounted_cr, 0)) accounted2009,
decode(gcc.code_combination_id-gcc2009.code_combination_id,0,'TRUE','FALSE') SameAcc
From gl_je_lines jel1001,
gl_code_combinations_kfv gcc,
gl_je_lines jel2009,
gl_code_combinations_kfv gcc2009,
(Select jeh1001.je_header_id h1001,
jeh2009.je_header_id h2009,
jeh1001.Name,
jeh1001.doc_sequence_value,
jeh1001.currency_code,
jeh1001.period_name
From gl.gl_je_headers jeh1001, gl.gl_je_headers jeh2009
Where jeh2009.set_of_books_id = 2009
And jeh2009.parent_je_header_id = jeh1001.je_header_id
--*******************************
-- And jeh1001.Name = 'PLS Cellcom ILS'
--And jeh1001.doc_sequence_value = '710104'
--*******************************
) je_headers
Where jel1001.je_line_num = jel2009.je_line_num
--And jel1001.code_combination_id = jel2009.code_combination_id
And jel1001.period_name = jel2009.period_name
And jel1001.je_header_id = je_headers.h1001
And jel2009.je_header_id = je_headers.h2009
And jel1001.code_combination_id = gcc.code_combination_id
And jel2009.code_combination_id = gcc2009.code_combination_id
Group By je_headers.Name,
je_headers.doc_sequence_value,
jel1001.je_header_id,
gcc.concatenated_segments,
je_headers.currency_code,
je_headers.period_name,
gcc2009.concatenated_segments,
gcc.code_combination_id,gcc2009.code_combination_id
|
|
|
|
Goto Forum:
Current Time: Mon Sep 09 08:26:54 CDT 2024
|