Project Accounting SQL Query in Oracle Apps
Here we are going to discuss about Project Accounting SQL Query in Oracle Apps. This Query will help us to find the project financials information's in Oracle apps. With the help of this SQL query we will get the Project cost related information in Oracle Apps. Please find below the Detail SQL query of Project Accounting.
Important Tables Involved in Project Accounting SQL Query in Oracle apps
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
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
Example of Project Accounting SQL Query in Oracle apps
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 (+)
