GL effective vs accounting date comparison
Description
Run
GL effective vs accounting date comparison and other Oracle EBS reports with Blitz Report™ on our demo environment
select count(*) count, --gp.start_date, --gp.end_date, --gjl.effective_date, --xal.accounting_date, case when gjl.effective_date>=gp.start_date and gjl.effective_date<gp.end_date+1 then 'within period' when gjl.effective_date<gp.start_date then 'before' else 'after' end gjl_effective_date_check, case when xal.accounting_date>=gp.start_date and xal.accounting_date<gp.end_date+1 then 'within period' when xal.accounting_date<gp.start_date then 'before' else 'after' end xal_accounting_date_check, case when trunc(gjl.effective_date)>trunc(xal.accounting_date) then 'gjl.effective_date after xal.accounting_date' when trunc(gjl.effective_date)<trunc(xal.accounting_date) then 'gjl.effective_date before xal.accounting_date' else 'same date' end date_comparison from gl_ledgers gl, gl_periods gp, gl_je_lines gjl, gl_import_references gir, xla_ae_lines xal where gl.period_set_name=gp.period_set_name and gl.ledger_id=gjl.ledger_id and gp.period_name=gjl.period_name 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 group by case when gjl.effective_date>=gp.start_date and gjl.effective_date<gp.end_date+1 then 'within period' when gjl.effective_date<gp.start_date then 'before' else 'after' end, case when xal.accounting_date>=gp.start_date and xal.accounting_date<gp.end_date+1 then 'within period' when xal.accounting_date<gp.start_date then 'before' else 'after' end, case when trunc(gjl.effective_date)>trunc(xal.accounting_date) then 'gjl.effective_date after xal.accounting_date' when trunc(gjl.effective_date)<trunc(xal.accounting_date) then 'gjl.effective_date before xal.accounting_date' else 'same date' end order by count(*) desc |