R12 Queries SLA tables query

To get the sum from the SLA tables.

QR13 - Sum from the SLA Tables

/* Formatted on 6/25/2013 5:34:11 PM (QP5 v5.114.809.3010) */
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)
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008' <>
group by currency_code




To get the sum from the GL tables

 Sum from the GL Tables

/* Formatted on 6/25/2013 5:34:51 PM (QP5 v5.114.809.3010) */
/* Formatted on 6/25/2013 5:35:22 PM (QP5 v5.114.809.3010) */
  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

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

/* Formatted on 6/25/2013 5:35:31 PM (QP5 v5.114.809.3010) */
  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                
           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
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

/* Formatted on 6/25/2013 5:35:49 PM (QP5 v5.114.809.3010) */
  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

/* Formatted on 6/25/2013 5:35:55 PM (QP5 v5.114.809.3010) */
  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

/* Formatted on 6/25/2013 5:35:59 PM (QP5 v5.114.809.3010) */
  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

/* Formatted on 6/25/2013 5:36:05 PM (QP5 v5.114.809.3010) */
  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

/* Formatted on 6/25/2013 5:36:14 PM (QP5 v5.114.809.3010) */
  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;