Monday 22 April 2013

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:

  5. SELECT * FROM xla_accounting_errors
    WHERE  event_id in (
             SELECT acct_event_id FROM pa_cost_distribution_lines_all
             WHERE  expenditure_item_id = 123456);




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


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

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


  9. 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)


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

1 comment: