Project and Subledger Links

n 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;