SQL Query from Oracle EBS
Hi,
I need to write query, which will provide valuable informations to our FIN dept. It's basically ad-hoc query as bellow..
select distinct gjb.name,
gjh.name,
gjh.period_name,
gjh.je_source,
gjl_1.je_line_num,
gjl_1.accounted_dr,
gjl_1.accounted_cr,
gjl_1.description,
gcc.segment3
from gl_je_lines gjl_1,
gl_je_lines gjl_2,
gl_code_combinations gcc,
gl_je_headers gjh,
gl_je_batches gjb
where gjl_1.je_header_id = gjl_2.je_header_id
and gcc.code_combination_id = gjl_1.code_combination_id
and gjh.je_header_id = gjl_1.je_header_id
and gjb.je_batch_id = gjh.je_batch_id
and gjh.je_header_id = '302322'
and gcc.segment3 not in (3119820, 3119830, 3119992, 3959320, 3959321)
Description
The key columns are accounted_dr and accounted_cr, which represents CREDIT and DEBIT in accounting. Each returned row is financial transaction. In one row, you take money from one account and in other row you take same amount of money to another account to ensure balance ( i don't know how to explain it better in english ). So basically every transaction needs to have 2 rows >>> for example: - 1000 from account A ( one row ) and + 1000 to account B ( another row )
Question
Basically, for every row.. i need to display one more column, which will show opposite transaction for orgiinal one, so user can check for each account, the opposite account where money went..
Thats what i have ( simplified )
Column: accounted_dr accounted_cr segment3 ( account )
1000 0 5555
0 1000 3333
Thats what i need
column: accounted_dr accounted_cr segment3 ( account ) Oposite transaction
1000 0 5555 3333
0 1000 3333 5555
Thanks a lot !!
I don't even know if it's possible with SQL
-- Sorry for formatting
Thats what i have..
DEBIT CREDIT ACCOUNT
---------- ---------- ----------
4842446 2210200
1103429 2210200
19362 2490010
603473 2490010
1103429 2490010
4547 2210200
1013 2210200
19362 2210200
4842446 2490010
4679917 2210200
2596 3150221
4679917 3430000
603473 2210200
1013 3119800
2596 2210200
4547 6480100
Edited by: user13376140 on 2.6.2011 7:57