how to link projects transactions to subledger accounting (sla)

How To Link Projects Transactions To Subledger Accounting (SLA) [ID 1274575.1]

Modified 07-APR-2011 Type HOWTO Status PUBLISHED

In this Document Goal Solution Examples:

Applies to:

Oracle Project Costing - Version: 12.1.1 and later [Release: 12.1 and later ] Information in this document applies to any platform.

Goal

How can you link the data in Subledger Accounting (SLA) back to the source expenditure item transactions in Oracle Projects?

Solution

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 sourcedata 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:

Source_Distribution_Type Source Table Distribution Type Description

Join Conditions

R PA_COST_DISTRIBUTION_LINES_ALL Raw Cost source_distribution_id_num_1 = expenditure_item_id source_distribution_id_num_2 = line_num

C PA_COST_DISTRIBUTION_LINES_ALL Total Burdened Credit source_distribution_id_num_1 = expenditure_item_id source_distribution_id_num_2 = line_num

D PA_COST_DISTRIBUTION_LINES_ALL Total Burdened Debit

source_distribution_id_num_1 = expenditure_item_id source_distribution_id_num_2 = line_num

BL PA_CC_DIST_LINES_ALL Borrowed and Lent


Revenue - Normal Revenue PA_CUST_REV_DIST_LINES_ALL Expenditure Revenue


Revenue - Event Revenue PA_CUST_EVENT_RDL_ALL Event Revenue source_distribution_id_num_1 = pa_events.event_id source_distribution_id_num_2 = pa_cust_event_rdl_all.line_num

Revenue - UBR PA_DRAFT_REVENUES_ALL Unbilled Receivables

source_distribution_id_num_1 = project_id source_distribution_id_num_2 = draft_revenue_num

Revenue - UER PA_DRAFT_REVENUES_ALL Unearned Revenue

source_distribution_id_num_1 = project_id source_distribution_id_num_2 = draft_revenue_num

From XLA_DISTRIBUTION_LINKS table you can in turn join to XLA_EVENTS, XLA_AE_HEADERS, or XLA_AE_LINES

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 are some additional queries which use the acct_event_id, that can be used for gathering data and troubleshooting problems with XLA:

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:

a.

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):

b.

SELECT * FROM xla_events

WHERE process_status_code = 'I'

AND application_id = 275;

Rate this document

Page 1 of 1

01-06-2012https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOW...

how to link projects transactions to subledger accounting (sla)

Documents