Project and Subledger Links

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:

1 2 3 4 5

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:

1 2 3 4 5 6

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)

1 2 3

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:

1 2 3 4

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:

1 2 3 4

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:

1 2 3 4 5

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

1 2 3 4 5 6 7 8

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:

1 2 3 4 5 6 7 8

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):

1 2 3 4 5 6 7 8

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:

1 2 3

SELECT * FROM xla_events WHERE application_id = 275 AND event_type_code = upper('&event_type_code');