Queries for Reconciliation Issue

Queries for Reconciliation Issue

QR24 - Run this SQL script to determine the Batch ID for a Group

select je_batch_id from gl_je_batches where group_id=2068553 


QR25 - Run this SQL script to list the Amounts in the 
SLA table for all Manual (YTD) transactions

select /*+ parallel(l) parallel(h) parallel(gl) leading(h) */ 
gl.concatenated_segments,l.code_combination_id,sum(nvl(entered_cr,0)),
sum(nvl(entered_dr,0)),sum(nvl(entered_cr,0))-sum(nvl(entered_dr,0)), currency_code
from xla_ae_headers h, xla_ae_lines l, gl_code_combinations_kfv gl
where gl.code_combination_id = l.code_combination_id and
h.application_id = 200
and l.application_id = h.application_id
and l.ae_header_id = h.ae_header_id
and h.accounting_date between to_date('01-JUL-2007','DD-MON-YYYY') AND
to_date('31-MAR-2008','DD-MON-YYYY') -- <>
and h.ledger_id = 1 <>
AND h.gl_transfer_status_code='Y'
AND h.accounting_entry_status_code='F'
AND l.code_combination_id = 112771 <>
and h.event_type_code='MANUAL'
and h.application_id=200
and h.balance_type_code='A'
group by l.code_combination_id, gl.concatenated_segments, currency_code


QR26 - Run this SQL script to list all transactions that are marked as transferred from SLA, but is not present in the GL

SELECT l.ae_header_id, l.gl_sl_link_id, l.gl_sl_link_table 
FROM xla_ae_lines l, xla_ae_headers h WHERE 
l.application_id=h.application_id AND 
l.ae_header_id=h.ae_header_id AND 
h.application_id=:p_application_id AND 
h.ledger_id= :p_ledger_id AND 
h.upg_batch_id IS NULL AND 
h.gl_transfer_status_code='Y' AND 
h.accounting_entry_status_code='F' AND 
h.accounting_date BETWEEN :p_period_start_date AND :p_period_end_date AND 
h.event_type_code <> ' MANUAL' 
AND NOT EXISTS 
(SELECT 1 FROM gl_import_references ir , gl_je_headers gh 
WHERE ir.gl_sl_link_id=l.gl_sl_link_id AND 
ir.gl_sl_link_table=l.gl_sl_link_table AND 
ir.je_header_id=gh.je_header_id AND 
ir.je_batch_id=gh.je_batch_id AND 
gh.ledger_id>0); 



QR27 - Run this SQL script periodically to check for multiple postings

select distinct a.gl_sl_link_id, a.gl_sl_link_table 
from gl_import_references a 
where (a.gl_sl_link_id,a.gl_sl_link_table) in 
(select distinct gl_sl_link_id, gl_sl_link_table 
from xla_ae_headers xah ,xla_ae_lines xal 
where xah.application_id = xal.application_id 
and xah.ae_header_id = xal.ae_header_id 
and xah.ledger_id= :ledger_id 
and xah.application_id =:appl_id -- 200 for AP, 222 for AR etc. 
and xah.accounting_entry_status_code='F' 
and xah.accounting_date between :p_start and :p_end) 
and exists (select 1 from gl_je_headers gh 
where gh.je_batch_id = a.je_batch_id 
and gh.je_header_id = a.je_header_id 
and gh.ledger_id >0 
and nvl(gh.accrual_rev_je_header_id,0) =0 
) 
group by a.gl_sl_link_id, a.gl_sl_link_table 
having count(*) > 1 


QR28 - Run this SQL script to identify where the GL_SL_LINK_ID 
is in GL but not in SLA, for a batch or period.

select imp.gl_sl_link_id 
from gl_import_references imp, gl_je_lines gl
where gl.je_header_id=imp.je_header_id and
gl.je_line_num=imp.je_line_num 
and code_combination_id=52261 
and gl.je_header_id in
(select je_header_id from gl_je_headers where --je_batch_id=2586374
je_source='Payables' and ledger_id=2
and je_source ='Payables' and posted_date is NOT NULL
and actual_flag='A' 
and period_name in ('Jan-08','Feb-08','Mar-08','Apr-08') )
and not exists
(select 1 from xla_ae_lines where gl_sl_link_id=imp.gl_sl_link_id
and gl_sl_link_table='XLAJEL') and imp.gl_sl_link_id is not null


QR29 - Run this SQL script to list all the Batches with Null Group IDs: 

select je_batch_id from gl_je_batches where group_id is null
and je_batch_id in
(select distinct je_batch_id from gl_je_headers
where ledger_id=2 and je_source='Payables' and status='P'
and period_name='Jan-08')