OraFAQ Forum: Reports & Discoverer » calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr.....

Home » Developer & Programmer » Reports & Discoverer » calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr.....

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr..... [message #231675]

Wed, 18 April 2007 01:46

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

Report message to a moderator

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

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

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

Previous Topic:	How to schedule report export to excel
Next Topic:	Discoverer question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 08 05:47:48 CST 2024