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
/* Formatted on 7/4/2014 2:33:42 PM (QP5 v5.115.810.9015) */
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

/* Formatted on 7/4/2014 2:34:06 PM (QP5 v5.115.810.9015) */
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
/* Formatted on 7/4/2014 2:34:22 PM (QP5 v5.115.810.9015) */
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.

/* Formatted on 7/4/2014 2:34:38 PM (QP5 v5.115.810.9015) */
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:
/* Formatted on 7/4/2014 2:34:51 PM (QP5 v5.115.810.9015) */
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')