select
z.*,
gcck1.concatenated_segments gjl_account,
gcck2.concatenated_segments xal_account
from
(
select
max(dr_mismatch) over (partition by je_header_id,je_line_num) max_dr_mismatch,
max(cr_mismatch) over (partition by je_header_id,je_line_num) max_cr_mismatch,
max(ccid_mismatch) over (partition by je_header_id,je_line_num) max_ccid_mismatch,
y.*
from
(
select
decode(nvl(gjl_accounted_dr,0),nvl(xal_accounted_dr_sum,0),null,'Y') dr_mismatch,
decode(nvl(gjl_accounted_cr,0),nvl(xal_accounted_cr_sum,0),null,'Y') cr_mismatch,
decode(gjl_code_combination_id,xal_code_combination_id,null,'Y') ccid_mismatch,
x.*
from
(
select
gjb.name batch_name,
gjh.je_category,
gjh.je_source,
gjh.je_header_id,
gjl.je_line_num,
count(*) over (partition by gjl.je_header_id, gjl.je_line_num) xal_count,
xal.ae_header_id,
xal.ae_line_num,
xal.application_id,
xah.event_id,
xal.gl_sl_link_id,
xal.gl_sl_link_table,
gjl.accounted_dr gjl_accounted_dr,
xal.accounted_dr xal_accounted_dr,
sum(xal.accounted_dr) over (partition by gjl.je_header_id, gjl.je_line_num) xal_accounted_dr_sum,
gjl.accounted_cr gjl_accounted_cr,
xal.accounted_cr xal_accounted_cr,
sum(xal.accounted_cr) over (partition by gjl.je_header_id, gjl.je_line_num) xal_accounted_cr_sum,
gjl.code_combination_id gjl_code_combination_id,
xal.code_combination_id xal_code_combination_id,
gl.name ledger,
gjh.period_name,
gjh.posted_date
from
gl_ledgers gl,
gl_periods gp,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah
where
1=1 and
gl.period_set_name=gp.period_set_name and
gp.adjustment_period_flag='N' and
gp.period_name=gjh.period_name and
gl.ledger_id=gjh.ledger_id and
gjh.status='P' and
gjh.actual_flag='A' and
gjb.je_batch_id=gjh.je_batch_id and
gjh.je_header_id=gjl.je_header_id and
gjl.je_header_id=gir.je_header_id(+) and
gjl.je_line_num=gir.je_line_num(+) and
gir.gl_sl_link_id=xal.gl_sl_link_id and
gir.gl_sl_link_table=xal.gl_sl_link_table and
xah.ae_header_id=xal.ae_header_id and
xah.application_id=xal.application_id
) x
) y
) z,
gl_code_combinations_kfv gcck1,
gl_code_combinations_kfv gcck2
where
(max_dr_mismatch is not null or max_cr_mismatch is not null or max_ccid_mismatch is not null) and
gjl_code_combination_id=gcck1.code_combination_id(+) and
xal_code_combination_id=gcck2.code_combination_id(+)
order by
je_header_id,
je_line_num,
ae_header_id,
ae_line_num,
application_id |
