Step 1:
SELECT gjh.je_source, gjh.je_category, gjb.NAME AS je_batch_name, gjh.NAME AS je_name,
gjh.period_name, --TRUNC (gjl.effective_date) AS gl_date,
-- TO_CHAR (gjh.doc_sequence_value) AS doc_number,
-- gjh.currency_code AS gl_currency_code,
-- gcc.concatenated_segments, gjl.description AS naration,
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_code_combinations_kfv gcc,
gl_je_batches gjb
WHERE gjl.je_header_id = gjh.je_header_id
AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded
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= gcc.code_combination_id)
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.status = 'P'
AND TRUNC (gjl.effective_date) <= :p_end_date
Group By gjh.je_source, gjh.je_category,gjb.NAME, gjh.NAME,gjh.period_name
Order By 1
SELECT gjh.je_source, gjh.je_category,
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_code_combinations gcc
WHERE gjl.je_header_id = gjh.je_header_id
AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded
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= gcc.code_combination_id)
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
AND TRUNC (gjl.effective_date) <= :p_end_date
Group By gjh.je_source, gjh.je_category
Order By 1
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_code_combinations gcc
WHERE gjl.je_header_id = gjh.je_header_id
AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded
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= gcc.code_combination_id)
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
AND TRUNC (gjl.effective_date) <= :p_end_date
Group By gjh.je_source, gjh.je_category
Order By 1
Step 2:
SELECT gjh.je_source, gjh.je_category, gjb.NAME AS je_batch_name, gjh.NAME AS je_name,
gjh.period_name, --TRUNC (gjl.effective_date) AS gl_date,
-- TO_CHAR (gjh.doc_sequence_value) AS doc_number,
-- gjh.currency_code AS gl_currency_code,
-- gcc.concatenated_segments, gjl.description AS naration,
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_code_combinations_kfv gcc,
gl_je_batches gjb
WHERE gjl.je_header_id = gjh.je_header_id
AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded
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= gcc.code_combination_id)
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.status = 'P'
AND TRUNC (gjl.effective_date) <= :p_end_date
Group By gjh.je_source, gjh.je_category,gjb.NAME, gjh.NAME,gjh.period_name
Order By 1