Post date: Dec 05, 2012 8:46:29 AM
/* Formatted on 2012/12/05 14:39 (Formatter Plus v4.8.8) */
SELECT je_header_id, segment2, segment4
FROM (SELECT je_header_id, segment2, segment4,
(SELECT COUNT (1)
FROM pgn_map_cf_category b
WHERE b.cc_segment2 = a.segment2
AND b.cc_segment4 = a.segment4) ctr
FROM (SELECT gjh.je_header_id je_header_id,
gjl.je_line_num je_line_num,
gjl.period_name period_name,
gjl.code_combination_id code_combination_id,
gcc.segment1 segment1, gcc.segment2 segment2,
gcc.segment4 segment4,
gjl.effective_date effective_date,
gjl.set_of_books_id set_of_books_id,
gjl.description description,
(NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0)
) amount,
gps.quarter_num quarter_num,
gps.period_year period_year, gps.period_num period_num
FROM gl_je_categories gjc,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_category = gjc.je_category_name
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.period_name = gps.period_name
AND gjl.set_of_books_id = gps.set_of_books_id
AND gjc.user_je_category_name IN ('10', '11')
AND ( gcc.segment2 NOT LIKE '001%'
AND gcc.segment2 NOT LIKE '002%'
)
AND NVL (gjl.attribute9, 'N') != 'Y'
AND gjh.set_of_books_id = '86'
AND gjh.status = 'P'
AND gps.application_id = 101
--Added by Tandy / 2 Jun 04 - to cover wrong category but impact to cash/bank
UNION ALL
SELECT gjh.je_header_id je_header_id,
gjl.je_line_num je_line_num,
gjl.period_name period_name,
gjl.code_combination_id code_combination_id,
gcc.segment1 segment1, gcc.segment2 segment2,
gcc.segment4 segment4,
gjl.effective_date effective_date,
gjl.set_of_books_id set_of_books_id,
gjl.description description,
(NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0)
) amount,
gps.quarter_num quarter_num,
gps.period_year period_year, gps.period_num period_num
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.period_name = gps.period_name
AND gjl.set_of_books_id = gps.set_of_books_id
AND ( gcc.segment2 NOT LIKE '001%'
AND gcc.segment2 NOT LIKE '002%'
)
AND NVL (gjl.attribute9, 'N') != 'Y'
AND gjh.set_of_books_id = '86'
AND gjh.status = 'P'
AND gps.application_id = 101
AND gjl.je_header_id IN (
SELECT DISTINCT a.je_header_id
FROM gl_je_lines a,
gl_code_combinations b,
gl_je_headers c,
gl_je_categories d
WHERE a.set_of_books_id = '86'
AND b.code_combination_id =
a.code_combination_id
AND ( b.segment2 LIKE '001%'
OR b.segment2 LIKE '002%'
)
AND d.user_je_category_name NOT IN
('00', '10', '11', 'Revaluation')
AND c.je_header_id = a.je_header_id
AND d.je_category_name = c.je_category)
--Added by Tandy / 11 Jun 08 - to cover Revaluations
UNION ALL
SELECT jl2.je_header_id je_header_id,
jl2.je_line_num je_line_num,
gjl.period_name period_name,
gjl.code_combination_id code_combination_id,
gcc.segment1 segment1, gcc.segment2 segment2,
gcc.segment4 segment4,
gjl.effective_date effective_date,
gjl.set_of_books_id set_of_books_id,
gjl.description description,
(NVL (jl2.accounted_dr, 0) - NVL (jl2.accounted_cr, 0)
) amount,
gps.quarter_num quarter_num,
gps.period_year period_year, gps.period_num period_num
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_je_categories gjc,
gl_code_combinations gcc,
gl_period_statuses gps,
gl_je_lines jl2,
gl_code_combinations cc2
WHERE gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.period_name = gps.period_name
AND gjl.set_of_books_id = gps.set_of_books_id
AND ( cc2.segment2 LIKE '001%'
OR cc2.segment2 LIKE '002%'
OR cc2.segment2 LIKE '005%'
)
-- Added by Tandy/13-11-08 to cover Deposito revaluation
AND (gcc.segment2 = '90902')
AND NVL (jl2.attribute9, 'N') != 'Y'
AND gjh.set_of_books_id = '86'
AND gjh.status = 'P'
AND gps.application_id = 101
AND gjc.user_je_category_name = 'Revaluation'
AND gjh.je_category = gjc.je_category_name
AND jl2.je_header_id = gjl.je_header_id
AND jl2.code_combination_id = cc2.code_combination_id) a)
WHERE ctr = 0