Queries for Reconciliation Issue

Queries for Reconciliation Issue

The following are some useful queries that may help you to further analyze why you have a Reconciliation Issue.

QR23 - Run this SQL script to determine the Segment from a CCID

select concatenated_segments from gl_code_combinations_kfv
where code_combination_id=52261


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')