/*GL Query*/
SELECT gjb.name batch_name, gjh.je_source, gjh.je_category, gcc.segment4 account_code,
(SELECT A4.DESCRIPTION DECS
from fnd_flex_values_vl A4
WHERE
a4.flex_value=gcc.segment4
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='SEGMENT4'))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--gjh.je_category = 'Receipts'
AND gjl.je_header_id = gjh.je_header_id
AND gjl.PERIOD_NAME NOT LIKE 'ADJ%'
AND EXISTS (
SELECT 1
FROM gl_code_combinations gc
WHERE gc.segment1 = :P_COMPANY
AND gc.segment4 = :P_ACCT_CD
AND gc.code_combination_id= gjl.code_combination_id)
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
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 gjb.name, gjh.je_source, gjh.je_category,gcc.segment4
Order By 1