In this Article we will look at how to link the data between Oracle Projects with the SLA module.
After Generate Accounting Events is run the acct_event_id in the pa_cost_distribution_lines_all table will be populated with the accounting event id in the SLA. So using this acct_event_id column we can easily get to the XLA Tables.
For the Cost Distribution lines, we can link the acct_event_id directly to the Xla_events table.
Example:
Lets say my expenditure item id is 123456.
Query to Find the XLA Events for the above expenditure item:
|
|
SELECT * FROM xla_events
WHERE event_id in (
SELECT DISTINCT acct_event_id
FROM pa_cost_distribution_lines_all
WHERE expenditure_item_id = 123456);
|
To select cost distribution line data where the process status on the associated accounting event is ‘I’ (Invalid). Other process statuses that might be of interest: E=Error, U=Unprocessed, R=Related Event in Error:
To select cost distribution line data where the process status on the associated accounting event is ‘I’ (Invalid). Other process statuses that might be of interest: E=Error, U=Unprocessed, R=Related Event in Error:
|
|
SELECT * FROM pa_cost_distribution_lines_all pa
WHERE acct_event_id in (
SELECT event_id FROM xla_events
WHERE process_status_code = 'I'
AND application_id = 275)
ORDER BY expenditure_item_id,line_num;
|
To select all event data for Projects related events where the process status code is ‘I’ (Invalid)
|
|
SELECT * FROM xla_events
WHERE process_status_code = 'I'
AND application_id = 275;
|
To select all event data related to a particular expenditure item with id 123456:
|
|
SELECT * FROM xla_events
WHERE event_id in (
SELECT DISTINCT acct_event_id FROM pa_cost_distribution_lines_all
WHERE expenditure_item_id = 123456);
|
To view accounting errors associated with a particular expenditure item with id 123456:
|
|
SELECT * FROM xla_accounting_errors
WHERE event_id in (
SELECT acct_event_id FROM pa_cost_distribution_lines_all
WHERE expenditure_item_id = 123456);
|
To select all accounting event header data for an expenditure item with id 123456:
|
|
SELECT * FROM xla_ae_headers
WHERE application_id = 275
AND event_id IN (
SELECT acct_event_id FROM pa_cost_distribution_lines_all
WHERE expenditure_item_id = 123456);
|
To select all accounting event lines data
|
|
SELECT * FROM xla_ae_lines
WHERE application_id = 275
AND ae_header_id IN (
SELECT ae_header_id FROM xla_ae_headers
WHERE application_id = 275
AND event_id IN (
SELECT acct_event_id FROM pa_cost_distribution_lines_all
WHERE expenditure_item_id = 123456));
|
To select all distribution link data, which provides direct links between accounting event, event header, and event line details and the associated transactions in the source subledger, you can use:
|
|
SELECT * FROM xla_distribution_links
WHERE application_id = 275
AND ae_header_id in (
SELECT ae_header_id FROM xla_ae_headers
WHERE application_id = 275
AND event_id IN (
SELECT acct_event_id FROM pa_cost_distribution_lines_all
WHERE expenditure_item_id = 123456));
|
To show accounting event headers that indicate they are finally accounted, but cannot be found in General Ledger (GL):
|
|
SELECT DISTINCT hdr.ae_header_id , hdr.event_id, hdr.event_type_code, hdr.je_category_name
FROM xla_ae_lines lines, xla_ae_headers hdr
WHERE hdr.ae_header_id = lines.ae_header_id
AND hdr.accounting_entry_status_code = 'F'
AND lines.gl_sl_link_id IS NOT NULL
AND NOT EXISTS (
SELECT gl_sl_link_id FROM gl_import_references gir
WHERE lines.gl_sl_link_id = gir.gl_sl_link_id)
|
You can also use a query like the one below to query all events for a particular type of transaction in Projects:
|
|
SELECT * FROM xla_events
WHERE application_id = 275
AND event_type_code = upper('&event_type_code');
|
