Post date: Feb 03, 2016 2:57:46 AM
SELECT gjl.je_header_id,
gjl.je_line_num,
gjb.NAME batch_name,
gjh.NAME journal_name,
gjl.entered_dr,
gjl.entered_cr,
gjl.accounted_dr,
gjl.accounted_cr,
nvl(gjl.accounted_dr,0)- nvl(gjl.accounted_cr,0) mutasi,
gjh.je_source SOURCE,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8 ACCOUNT,
gcc.SEGMENT2,
gjl.period_name
FROM gl_je_lines gjl,
gl_code_combinations gcc,
gl_je_headers gjh,
gl_je_batches gjb
WHERE 1 = 1
-- and desc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
and gjb.JE_BATCH_ID=gjh.JE_BATCH_ID --join table untuk data batch dan journal name
and gjh.JE_HEADER_ID=gjl.JE_HEADER_ID --join table antara header dengan line
and gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID --join table untuk line dan library dari ccid
and gjh.DEFAULT_EFFECTIVE_DATE <='31-DEC-2015' --data sampai dengan
and gjh.JE_SOURCE<>'Consolidation' --belum include konsolidasi
AND gjl.je_header_id IN (SELECT DISTINCT je_header_id --cek header id yg mengandung akun tertentu
FROM gl_je_lines gjl,
gl_code_combinations gcc
WHERE 1 = 1
AND gcc.code_combination_id = gjl.code_combination_id
AND gcc.segment1 = '303' --untuk company SMP saja
AND gcc.segment2 LIKE '101060%') --akun berawalan 101060
order by gjl.je_header_id, gjl.je_line_num --diurutkan dari header id lalu line numbernya