Project Accounting tables in oracle apps r12

Project Accounting tables in oracle apps r12

In this post, we will be discussing about the Project Accounting Tables in Oracle Apps r12. These Project accounting tables helps to store the different types of Oracle Project related information’s. We can get all types of Project accounting information’s for a Project from these tables in oracle apps r12. Here below I will first divide the Project Accounting tables in two parts. In first Part, I will share the Basic Project related tables list and in the second part I will share the most important Project accounting Tables. Please find below the details list and complete explanation of project accounting tables in oracle apps r12.

Project accounting tables in oracle apps r12 Divided in Two Parts

1.       Basic Project Related Tables.

2.       Most important Project accounting Tables.

8 Most Important project accounting tables in oracle apps r12

1.       PA_COST_DISTRIBUTION_LINES_ALL

2.       PA_COST_DISTRIBUTION_LINES_ALL

3.       PA_COST_DISTRIBUTION_LINES_ALL

4.       PA_CC_DIST_LINES_ALL

5.       PA_CUST_REV_DIST_LINES_ALL

6.       PA_CUST_EVENT_RDL_ALL

7.       PA_DRAFT_REVENUES_ALL

8.       PA_DRAFT_REVENUES_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:

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;

10 Most Important project tables in oracle apps r12

1.pa_resource_assignments
2.pa_proj_elements
3.pa_resource_list_members
4.pa_resource_asgn_curr
5.pa_budget_versions
6.pa_fin_plan_types_b
7.pa_proj_fp_options
8.pa_proj_element_versions
9.pa_control_items
10.pa_projects_all

SQL Query Using Project Accounting tables  in Oracle apps r12

 SELECT p.name "Project Name",
pbv.ci_id,
  NVL(pe.name ,p.name) task_name,
    NVL(pe.element_number, p.segment1)
  || '('
  || NVL(pe.name ,p.name)
  || ')' task_name_num
  rlm.alias planning_resource_name,
  (NVL(rac.total_projfunc_burdened_cost,0)+NVL(NULL,0)) total_cost,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_txn_raw_cost)             AS raw_cost,
  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_raw_cost_rate_override, to_number(NULL)),2)    AS raw_cost_rate,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_projfunc_raw_cost)        AS raw_cost_proj_func_cur,

ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_burden_cost_rate_override, TO_NUMBER(NULL)),2) AS burd_cost_rate_override,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_projfunc_burdened_cost)   AS burd_cost_proj_func_cur,
  DECODE(pfpt.plan_class_code, 'FORECAST', to_number(NULL), rac.total_txn_burdened_cost) AS burd_cost_txn_cur
FROM pa_resource_assignments pra,
  pa_proj_elements pe,
  pa_resource_list_members rlm,
  pa_resource_asgn_curr rac,
  pa_budget_versions pbv,
  pa_fin_plan_types_b pfpt,
  pa_proj_fp_options po,
  pa_proj_element_versions ppe,
  pa_control_items ci,
  pa_projects_all p
WHERE pra.resource_assignment_id       = rac.resource_assignment_id
AND ( rac.total_quantity              IS NOT NULL
OR rac.txn_burden_cost_rate_override  IS NOT NULL
OR rac.total_txn_burdened_cost        IS NOT NULL
OR rac.txn_bill_rate_override         IS NOT NULL
OR rac.total_txn_revenue              IS NOT NULL
or rac.txn_raw_cost_rate_override     is not null
OR rac.total_txn_raw_cost             IS NOT NULL)
and pra.resource_list_member_id        = rlm.resource_list_member_id
and p.project_id                       = :p_project_id
and pra.project_id                     = p.project_id
AND pbv.budget_version_id              = rac.budget_version_id
and pbv.budget_version_id              = pra.budget_version_id
and pbv.project_id                     = pra.project_id
and pbv.ci_id                          = ci.ci_id
AND pbv.ci_id                          = :p_change_order_id
AND pbv.fin_plan_type_id               = pfpt.fin_plan_type_id
AND pbv.budget_version_id              = po.fin_plan_version_id
AND po.fin_plan_option_level_code      = 'PLAN_VERSION'
and po.fin_plan_version_id             = pbv.budget_version_id
AND pra.task_id                        = pe.proj_element_id (+)
and ppe.parent_structure_version_id (+)= pa_project_structure_utils.get_fin_struc_ver_id(pe.project_id)
AND pe.proj_element_id                 = ppe.proj_element_id (+)

Project Accounting tables in oracle apps r12