SLA Table Data Dump for Single Transaction

I was working on an issue once and Oracle asked for the following SQL to be run for an AR receipt we had a problem with.

The sub-selects can easily be modified for any other transaction, e.g. AP Invoice.

The SQL is useful as it allows you to get the full table export for each of the key SLA tables.

More information about how these tables link up with each other is provided in this post on oracleappshub.

SELECT *
  FROM xla.xla_transaction_entities xte
 WHERE xte.application_id = 222
   AND xte.entity_code = 'RECEIPTS'
   AND xte.source_id_int_1 = 11223344;

SELECT *
  FROM xla.xla_ae_headers xah
 WHERE xah.application_id = 222
   AND xah.entity_id IN
              (SELECT xte.entity_id
                 FROM xla.xla_transaction_entities xte
                WHERE xte.application_id = 222
                  AND xte.entity_code = 'RECEIPTS'
                  AND xte.source_id_int_1 = 11223344);
   
   SELECT *
  FROM xla.xla_events xe
 WHERE xe.application_id = 222
   AND xe.entity_id IN
              (SELECT xte.entity_id
                 FROM xla.xla_transaction_entities xte
                WHERE xte.application_id = 222
                  AND xte.entity_code = 'RECEIPTS'
                  AND xte.source_id_int_1 = 11223344);

SELECT xal.*
  FROM xla.xla_ae_lines xal
     , xla.xla_ae_headers xah
 WHERE xal.application_id = xah.application_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xah.application_id = 222
   AND xah.entity_id IN
              (SELECT xte.entity_id
                 FROM xla.xla_transaction_entities xte
                WHERE xte.application_id = 222
                  AND xte.entity_code = 'RECEIPTS'
                  AND xte.source_id_int_1 = 11223344);

SLA Tables Joined Together

When I was working on a helpdesk call where the accounting was faulty for some AP invoices, I used this SQL to run some checks.

I left-joined the tables to the ap_invoices_all table, but you could join to any other tables you need…

-- ##############################################################################
--      SLA TABLES JOINED TOGETHER
-- ##############################################################################

	   SELECT xte.source_id_int_1
			, xte.source_id_int_2
			, xte.source_id_int_3
			, xte.transaction_number
			, xte.entity_code
			, '######## ap_invoices #########' ap_invoices
			, aia.invoice_id
			, aia.invoice_num
			, aia.invoice_amount
			, '######### xla_events ######################' xla_events
			, xe.event_id
			, xe.event_number
			, xe.creation_date event_created
			, fu_xe.user_name event_created_by
			, flv2.meaning event_status
			, flv3.meaning event_process_status
			, xe.event_type_code
			, '######## xla_ae_headers #########' xla_ae_headers
			, xah.ae_header_id
			, decode(xah.balance_type_code,'e','encumbrance','a','actual') balance_type
			, xah.period_name
			, xah.completed_date
			, to_char(xah.accounting_date, 'dd-mon-yyyy') accounting_date
			, xah.gl_transfer_status_code
			, xah.je_category_name
			, xah.creation_date
			, xah.request_id
			, xah.group_id
			, xah.description
			, '######## xla_ae_lines #########' xla_ae_lines
			, gcc.concatenated_segments
			, xal.accounting_class_code
			, flv1.meaning accounting_class
			, gety.encumbrance_type
			, xal.displayed_line_number
			, xal.currency_code currency
			, xal.entered_dr
			, xal.entered_cr
			, xal.accounted_dr
			, xal.accounted_cr
			, xal.creation_date line_created
			, fu_xal.user_name line_created_by
		 FROM xla.xla_transaction_entities xte
		 JOIN xla_events xe                              ON xe.entity_id =                  xte.entity_id       and xte.application_id = xe.application_id
		 JOIN xla.xla_ae_headers xah                     ON xah.entity_id =                 xe.entity_id        and xah.event_id = xe.event_id and xah.application_id = xe.application_id
		 JOIN xla.xla_ae_lines xal                       ON xal.application_id =            xah.application_id  and xal.ae_header_id = xah.ae_header_id
		 JOIN applsys.fnd_user fu_xe                     ON xe.created_by =                 fu_xe.user_id
		 JOIN applsys.fnd_user fu_xal                    ON xal.created_by =                fu_xal.user_id
	LEFT JOIN fnd_lookup_values flv1                     ON xal.accounting_class_code =     flv1.lookup_code    AND flv1.lookup_type = 'XLA_ACCOUNTING_CLASS'
	LEFT JOIN fnd_lookup_values flv2                     ON xe.event_status_code =          flv2.lookup_code    AND flv2.lookup_type = 'XLA_EVENT_STATUS'
	LEFT JOIN fnd_lookup_values flv3                     ON xe.process_status_code =        flv3.lookup_code    AND flv3.lookup_type = 'XLA_EVENT_PROCESS_STATUS'
	LEFT JOIN gl.gl_encumbrance_types gety               ON gety.encumbrance_type_id =      xal.encumbrance_type_id
		 JOIN gl_code_combinations_kfv gcc               ON gcc.code_combination_id =       xal.code_combination_id
	LEFT JOIN ap_invoices_all aia                        ON aia.invoice_id =                xte.source_id_int_1
		WHERE 1 = 1
		  AND xal.creation_date > '03-AUG-2017'
		  AND xah.balance_type_code = 'E'
		  AND xte.entity_code = 'PURCHASE_ORDER'
		  AND xal.accounting_class_code = 'REQUISITION'
		  AND xte.entity_id IN (8629570,8808320)
		  AND xah.ae_header_id IN (17948134, 20025619)
		  AND xte.source_id_int_1 = 4066841
		  AND xte.transaction_number = '2212929'
		  AND xte.transaction_number IN ('1229071','2330343','2017-07-17-0001a')
		  AND gety.encumbrance_type = 'Obligation'
		  AND 1 = 1
	 ORDER BY xte.source_id_int_1
			, xte.transaction_number
			, xe.event_id
			, xe.event_number
			, xal.displayed_line_number;

SLA source_id_int_1 Mappings

If you spend any time hacking about dealing with issues with the flow of accounting data from sub-ledgers to GL (e.g. Payables, Purchasing, Receivables etc.) you might have had to join up GL journal tables with sub-ledger-accounting tables (as in this post about GL Journal Lines – joined to SLA tables).

There’s a field on the XLA_ENTITY_ID_MAPPINGS table called SOURCE_ID_INT_1 which contains the primary_id value for transactions from a lot of other sub-ledgers.

This provides a useful summary (from Anil Patil’s blog):

The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.

The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

If you want to find out how the source_id_int_1 field maps to the primary transactions, this this:

-- ##############################################################################
--      SOURCE_ID_INT_1 MAPPINGS
-- ##############################################################################

-- for a given application, find out what entity (e.g. AP_INVOICES) and column_name (e.g. INVOICE_ID)
-- found via Application Diagnostics > Subledger Accounting > Setup Diagnostics > Xla Setup Diagnostics Diagnostics Script

    SELECT fa.application_id
         , fa.application_short_name app
         , fat.application_name
         , DECODE(fpi.status,'I','Licensed','S','Shared','N','Not Licensed') status
         , fpi.product_version
         , fpi.patch_level
         , xeim.entity_code
         , xeim.source_id_col_name_1
         , xeim.transaction_id_col_name_1
         , xeim.source_id_col_name_2
         , xeim.transaction_id_col_name_2
         , xeim.source_id_col_name_3
         , xeim.transaction_id_col_name_3
         , xeim.source_id_col_name_4
         , xeim.transaction_id_col_name_4
      FROM xla.xla_entity_id_mappings xeim
      JOIN applsys.fnd_application fa ON xeim.application_id = fa.application_id
      JOIN applsys.fnd_application_tl fat ON fa.application_id = fat.application_id
 LEFT JOIN applsys.fnd_product_installations fpi ON fpi.application_id = fa.application_id
     WHERE 1 = 1
       AND xeim.source_id_col_name_1 IS NOT NULL
       AND fat.application_name LIKE 'Proj%'
--       AND fpi.status <> 'N'
  ORDER BY fat.application_name
         , xeim.entity_code;

Application Accounting Definitions

Sometimes when you apply a big patch (e.g. RPC patches), Application Accounting Definitions can become invalid.

After the patching you then have to run the “Validate Application Accounting Definitions” job to validate the definitions again.

I find it can be useful to run the Count SQL below to compare which definitions are invalid on the patched instance, with PROD, to see what has changed, and to make sure things line up again after you have run the “Validate Application Accounting Definitions” job.

-- ##################################################################
-- Application Accounting Definitions
-- ##################################################################

select xprf.application_name appl
--     , xprf.*
     , xprf.description
     , xprf.compile_status_dsp hdr_status
     , xprf.enabled_flag enbl
     , xprf.last_update_date hdr_upd_dt
     , xpahf.event_class_name ev_class
     , xpahf.event_type_name ev_type
     , xpahf.accounting_required_flag crt_acct
     , xpahf.locking_status_flag locked
     , xpahf.validation_status_dsp status
     , xpahf.last_update_date upd_dt
  from apps.xla_product_rules_fvl xprf
     , apps.xla_prod_acct_headers_fvl xpahf
 WHERE xprf.application_id = xpahf.application_id
   AND xprf.product_rule_code = xpahf.product_rule_code
--   AND xprf.application_name = 'Assets'
   AND xprf.compile_status_dsp <> 'Valid'
--   AND xprf.description IN ('Standard Accounting for Inflation','Standard Accounting for United Kingdom Local Authorities')
--   AND xpahf.event_class_name = 'Borrowed and Lent'
--   AND xprf.description = 'China Projects Standard Accounting'
ORDER BY xprf.application_name
       , xprf.description
       , xpahf.event_class_name;

-- count summary

    select count(*) ct
         , xprf.application_name
         , xprf.compile_status_dsp
      from apps.xla_product_rules_fvl xprf
         , apps.xla_prod_acct_headers_fvl xpahf
     WHERE xprf.application_id = xpahf.application_id
       AND xprf.product_rule_code = xpahf.product_rule_code
       AND xprf.compile_status_dsp = 'Invalid'
  GROUP BY xprf.compile_status_dsp
         , xprf.application_name
  ORDER BY xprf.compile_status_dsp
         , xprf.application_name;

select xprf.application_name appl
     , xprf.description
     , count(*) ct
  from apps.xla_product_rules_fvl xprf
     , apps.xla_prod_acct_headers_fvl xpahf
 WHERE xprf.application_id = xpahf.application_id
   AND xprf.product_rule_code = xpahf.product_rule_code
   AND xprf.compile_status_dsp = 'Valid'
GROUP BY xprf.application_name
     , xprf.description
ORDER BY 3 DESC
       , xprf.application_name
     , xprf.description;

-- XLA Application

select application_id
     , application_name
     , name
     , compile_status_dsp
     , enabled_flag
     , last_update_date 
  from apps.xla_product_rules_fvl xprf;

select xpahf.*
from apps.xla_prod_acct_headers_fvl xpahf;