Project and Subledger Links

project to gl


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:  


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 line data for an expenditure item with id 123456:  


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');