Project and Subledger Links ............

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:

1. 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;
   
   
2. 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;
   
   
3. 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);
   
   
4. 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);





5. 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);
   
   
   
6. 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));
   
   
7. 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));



8. 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)
   
   
   
9. 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');