---GL Opening Debit Credit Closing balance
Select bb.ACCOUNT_CODE,bb.ACCOUNT_DECS,bb.GL_DATE,aa.open_balance,bb.ACCOUNTED_DR, bb.ACCOUNTED_CR,(aa.open_balance-bb.net_balance)Closing_bal
FROM
(SELECT
gcc.segment2 account_code,
sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) open_balance
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc
WHERE 1=1
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
AND gjh.LEDGER_ID =2042
AND gjb.je_batch_id = gjh.je_batch_id
AND TRUNC (gjl.effective_date) < :p_start_date
--AND TRUNC (gjl.effective_date) <= :p_end_date
Group By gcc.segment2)aa
,(SELECT --gjb.name batch_name, gjh.je_source, gjh.je_category,
gcc.segment2 account_code,TRUNC (gjl.effective_date) GL_DATE,
(SELECT A4.DESCRIPTION DECS
from fnd_flex_values_vl A4
WHERE a4.flex_value=gcc.segment2
And a4.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID
from fnd_id_flex_segments
WHERE application_id = 101 AND id_flex_code = 'GL#' and enabled_flag = 'Y'
and application_column_name='SEGMENT2'))account_DECS,
sum(NVL(gjl.accounted_dr,0)) accounted_dr, sum(NVL(gjl.accounted_cr,0)) accounted_cr,
sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) net_balance
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc
WHERE 1=1
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
AND gjh.LEDGER_ID =2042
AND gjb.je_batch_id = gjh.je_batch_id
AND TRUNC (gjl.effective_date) >= :p_start_date
AND TRUNC (gjl.effective_date) <= :p_end_date
Group By gcc.segment2,TRUNC (gjl.effective_date)) bb
Where aa.ACCOUNT_CODE=bb.ACCOUNT_CODE
--AND bb.ACCOUNT_CODE='732183'
Order By 1;