Home » Developer & Programmer » Reports & Discoverer » calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr.....
calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr..... [message #231675] |
Wed, 18 April 2007 01:46 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
la-oracle
Messages: 3 Registered: February 2007
|
Junior Member |
|
|
i have to calculate opening balance and calulate the running total for the DR and CR.I have done calculatin running total but how and where to get the opening balance from GL module.is PERIOD nessary?.i have given the eg format and the query i have done ,please help........
Description DR CR Balance
------------- ---- ---- -------
Opening Balance 100.000 100.000
25.000 75.000
100.000 175.000
100.000 275.000
100.000 375.000
SELECT distinct gcc.concatenated_segments acc,
gjl.entered_dr DR,
gjl.entered_cr CR,
b.name BATCH,
gjl.set_of_books_id,
gjl.description DESCRIPTION,
gjh.je_category CATEGORY,
gjh.je_source SOURCE,
gjh.currency_code CURRENCY,
decode(gjh.status,'P','POSTED','U','UNPOSTED') STATUS,
sum(nvl(gjl.entered_dr,0)-nvl(gjl.entered_cr,0)) over
(ORDER BY gjl.entered_dr,gjl.entered_cr,gcc.concatenated_segments) AS BAL,
--decode(b.actual_flag,'A','Actual','B','Budjet') ACCOUNTING,
to_char(gjh.date_created,'DD-MON-RR') DATE_1
FROM gl_je_lines gjl,
gl_je_batches b,
gl_je_headers gjh,
gl_code_combinations_kfv gcc
WHERE gjh.je_header_id = gjl.je_header_id
and gjl.code_combination_id = gcc.code_combination_id
and gjh.je_batch_id = b.je_batch_id
and gjh.set_of_books_id=nvl(:P_SET_OF_BOOKS_ID,gjh.set_of_books_id)
and to_char(gjh.date_created,'DD-MON-RR') between nvl(:P_FROM_DATE,to_char(gjh.date_created,'DD-MON-RR')) and nvl(:P_TO_DATE,to_char(gjh.date_created,'DD-MON-RR'))
and gcc.concatenated_segments = nvl(:P_CONC_SEG,gcc.concatenated_segments)
and gjh.status =decode(:P_STATUS,'POSTED','P','UNPOSTED','U','ALL',NULL,gjh.STATUS)
--and b.name = nvl(:P_BATCH_NO,b.name)
GROUP BY gjl.set_of_books_id,gcc.concatenated_segments,gjl.entered_dr,gjl.entered_cr,b.name ,gjl.description,gjh.je_category,gjh.je_source , gjh.currency_code, gjh.date_created,gjh.status
|
|
|
Re: calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr..... [message #231980 is a reply to message #231675] |
Thu, 19 April 2007 00:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
I'll try to find some time later on to have a look at your query.
Some notes in advance:
- please use proper formatting next time (see example below)
- why don't you use gl_balances?
- what is the functional requirement that leads to the DISTINCT? I see no reason for it.
SELECT DISTINCT gcc.concatenated_segments acc
,gjl.entered_dr dr
...
FROM gl_je_lines gjl
,gl_je_batches b
,gl_je_headers gjh
,gl_code_combinations_kfv gcc
WHERE gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
etc...
[Updated on: Thu, 19 April 2007 00:20] Report message to a moderator
|
|
|
Re: calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr..... [message #232192 is a reply to message #231980] |
Thu, 19 April 2007 14:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
Got to take a look at it. You have to use gl_balances, because the period closing and balancing is a complex process you don't want to do by hand based on gl_je_lines...
Below see a start I made for you, you can add things as you need it. Please note that I didn't take multi organization, multi currency, multi language and/or authorization into account.
SELECT *
FROM (SELECT gcc.concatenated_segments acc
,1 srt
,'Opening Balance' descr
,bal.period_name
,to_date(NULL) edate
,bal.begin_balance_dr debet
,bal.begin_balance_cr credit
FROM gl_balances bal
,gl_code_combinations_kfv gcc
WHERE gcc.code_combination_id = bal.code_combination_id
AND bal.set_of_books_id = 123
AND bal.period_name = '01-00'
UNION ALL
SELECT gcc2.concatenated_segments acc
,2 srt
,'GL Bookings' descr
,jel.period_name
,jel.effective_date edate
,jel.entered_dr debet
,jel.entered_cr credit
FROM gl_code_combinations_kfv gcc2
,gl_je_lines jel
WHERE jel.code_combination_id = gcc2.code_combination_id
AND jel.set_of_books_id = 123
AND jel.period_name = '01-00')
ORDER BY period_name
,acc
,srt
,edate NULLS FIRST
PS please check the SQL reference about the use of distinct and group by, in your query they where just hiding a cartesian product because you forgot set_of_books_id.
[Updated on: Thu, 19 April 2007 14:45] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Fri Sep 06 15:55:53 CDT 2024
|