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 (+)
3 comments:
Timetech Ltd can commercially exploit the new chip for the sport watches, and for that, it needs to
buy buildings for a new manufacturing base in the local industrial zone at a cost of £8,000,000 and
machines worth £2,000,000. The investment will be operational for 5 years and, at the end of that
period, the buildings will be sold for £8,000,000 but the machines will have no value. Timetech Ltd will
also have to lease a high street shop to sell directly to customers. The annual rental cost of this shop
will be £90,000 paid annually at the start of each year and subject to annual increases equal to the
inflation. The security cost for the production plant is estimated to be £60,000.
The new investment will have production capacity of producing 800 smart watches per week for 52
weeks per year, but there is a 50% probability to work at 80% capacity and 50% probability to operate
at 100% capacity. The production capacity will increase 6% per year throughout the five-year period
of the investment.
The market for sport watches is very competitive and its conditions are likely to affect the selling prices
of Timetech’s new sport watch. It is expected that there is 50% probability that the selling price will
be £120, 20% probability that the price will be £110 and 30% probability that the price will be £140.
The profit margin will be 40% of the selling price.
We assume that there will be a constant inflation rate of 3% per year and the cost of capital for the
new investment will be 9%. The staff cost is estimated to be £240,000 per year. We also assume that
staff and security cost will be paid at the end of each year and will increase annually by the rate of
inflation.
The tax rate is 20% of the profits. The first half of the tax is paid in the year that it arises, and the
remaining half in the following year.
All cash flows, except the initial investment and rent, are assumed to occur at the end of the year
Criterion 1: Estimate the Net Present Value of the project and the Internal Rate of Return of Option 1
and show your calculations clearly.
Criterion 2: Estimate the payback period, accounting rate of return, net present value and the internal
rate of return of the three investment opportunities in Option 2 and show your calculations clearly.
Criterion 3: Write a short report to the Board to explaining which of the three competing alternatives
(A, B or C) in Option 2 is best and why.
Criterion 4: Include in your report an explanation of the advantages and disadvantages of the
alternative investment appraisal methods that you have used in Option 2.
Option 2:
Timetech Ltd has three other alternative investment opportunities for their cash. The three competing
projects A, B and C have the following net cash flow profiles:
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
A - 10,000,000 2,000,000 3,200,000 3,300,000 5,000,000 6,000,000
B - 10,000,000 1,000,000 1,200,000 2,000,000 8,000,000 12,000,000
C - 10,000,000 7,000,000 3,000,000 4,000,000 1,200,000 1,000,000
The current Managing Director, who will be retiring in few months’ time, has argued that the company
should undertake the last project (C) because it has the highest internal rate of return. However, there
are concerns raised by other members of the Board, who have decided to hire you to estimate the
payback period; the accounting rate of return; the net present value and the internal rate of return of
each alternative and rank them. They have asked you to prepare a report to the Board to show the
results and the rankings and explain which of these three alternative investments (A, B or C) is best
and why. They also expect you to clearly explain the arguments for and against each method of
investment appraisal.
Post a Comment