R12 Queries SLA tables

R12 Queries SLA tables

To get the sum from the SLA tables.

QR13 - Sum from the SLA Tables

select /*+ parallel(xal) parallel(xah) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15151 <>
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1 <>
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
and (xah.upg_batch_id is null or xah.upg_batch_id=-9999) -- will help ignore upgraded data
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008' <>
group by currency_code


R12.B) To get the sum from the GL tables

QR14 - Sum from the GL Tables

select l.code_combination_id ccid,k.concatenated_segments,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) diff,
currency_code
from gl.gl_je_headers h
, gl.gl_je_lines l
,gl_code_combinations_kfv k
where h.currency_code = 'USD'
and l.ledger_id = 1 <>
and l.code_combination_id = k.code_combination_id
and h.je_header_id = l.je_header_id
and h.actual_flag = 'A'
and h.je_from_sla_flag= 'Y' -- will help ingore upgraded data
and l.code_combination_id = 15151 <>
and h.je_source = 'Payables'
and h.period_name in ('MAR-08') <>
group by l.code_combination_id, k.concatenated_segments, currency_code

R12.A and R12.B should match per CCID, currency within the period. If it does, check per period, per CCID, per currency--This should be the difference between XAL and XTB.

QR15 - To get the difference from SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =15011 -- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by currency_code

Note: In the following SQL query, we have introduced another filter--event_class_code.
This is done in order to remove the UNDO ACCOUNTING datafix entries. Read the section, Understanding how UNDO Accounting can impact Reconciliation, for more details. Such entries will match in SLA-to-GJL lines comparison, but will not match between XAL-to-XTB

QR16 - Get the XTB Difference

select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code

The Difference of the sum of Credit-Debit for the CCID/Currency combination in XAL (lines) should match the difference of the Sum Credit-Debit for the CCID/Transaction Currency in XTB, assuming all LIABILITY CCIDs are defined in the Trial Balance Definition.

QR17 - Get the difference from the SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code, entity_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15011
-- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by entity_id, currency_code,entity_id



QR18 - Get the difference from the XTB table

select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code, source_entity_id
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code, source_entity_id


If R12.A and R12.B do not match per CCID and currency within a period, then investigate the batches within that period from SLA and compare the amounts in SLA and GL using the group_id. This may not always work as the group_id sometimes gets deleted from GL.

QR19 - SLA query considering the SLA Manual entries

select /*+ parallel(xal) parallel(xah) leading(xah) */
--xah.entity_id source_entity_id,
sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff , xah.group_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY' and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =52261
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 2
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
group by xah.group_id


QR20 - GL query for amounts per Group ID

select 'GL' Module, b.group_id,
SUM(NVL(accounted_cr,0)) - SUM(NVL(accounted_dr,0)) diff_acc
from apps.gl_je_headers a, apps.gl_je_batches b,apps.gl_je_lines c
where
a.je_header_id=c.je_header_id
and a.je_batch_id=b.je_batch_id
and c.ledger_id=2
and a.posted_date is NOT NULL
and a.je_source ='Payables'
and a.actual_flag='A' and c.code_combination_id=52261
group by b.group_id;