pa-sla-gl link query

pa-sla-gl link query

In this post , we will be discuss about the pa-sla-gl link query in oracle apps. I will share all the important tables which helps to join the Project with XLA and General ledger in oracle apps. I will share the pa-sla-gl link query using these tables which will help to extract the Project related information from Project to xla and general ledger. Here below i will share the Important SQL queries to create the pa-sla-gl link query. Please find below the complete Project XLA(pa-sla-gl link query) in oracle apps.

6 Most Important Tables Used By pa-sla-gl link query

1.pa_cust_rev_dist_lines_all

2.pa_cost_distribution_lines_all

3.xla_ae_headers

4. xla_ae_lines 

5.xla_distribution_links

6.PA_CUST_EVENT_RDL_ALL

The ACCT_EVENT_ID field in the PA_COST_DISTRIBUTION_LINES_ALL table is the simplest and most direct link between cost distribution lines in Projects (and thereby the source expenditure items) and the data in the SLA (XLA) tables. This column contains the identifier of the Accounting Event in SLA.

Distributions of various types, including cost distribution lines, can be linked to events, event headers, and event lines also via the table XLA_DISTRIBUTION_LINKS. In this table you have links between XLA data and the source distribution which depends on the distribution source. The table below illustrates how different sources in Projects connect to this table:

Examples:

Using the acct_event_id column, you could run a query like the following to gather the event data for a particular 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 = &exp_item_id);

Another example using XLA_DISTRIBUTION_LINKS.  This query retrieves all of the Accounting Event header and line data related to the revenue for a particular expenditure item:

SELECT dr.line_num, aeh.*, ael.*
FROM   xla_ae_headers aeh, xla_ae_lines ael,
       xla_distribution_links dl, pa_cust_rev_dist_lines_all dr
WHERE  dl.source_distribution_type = 'Revenue - Normal Revenue'
AND    dr.expenditure_item_id = &exp_item_id
AND    dr.expenditure_item_id = dl.source_distribution_id_num_1
AND    dl.source_distribution_id_num_2 = dr.line_num
AND    aeh.ae_header_id = ael.ae_header_id
AND    ael.ae_header_id = dl.ae_header_id
AND    ael.ae_line_num = dl.ae_line_num;

The following Projects tables have reference fields in the General Ledger table GL_JE_LINES:

1. Tables
   
   • PA_COST_DISTRIBUTION_LINES_ALL
   • PA_CUST_EVENT_RDL_ALL
   • PA_CUST_REV_DIST_LINES_ALL
   
   all have reference fields in GL_JE_LINES.  The join conditions with these columns are:
   
   BATCH_NAME             = GL_JE_LINES.REFERENCE_1
   DR_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID
   
   
2. The PA_DRAFT_REVENUES_ALL table has the following join conditions:
   
   UNEARNED_BATCH_NAME          = GL_JE_LINES.REFERENCE_1
   UNEARNED_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID
   
   or
   
   
   UNBILLED_BATCH_NAME          = GL_JE_LINES.REFERENCE_1
   UNBILLED_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID