[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.GROUP_RESOURCE_TYPE_ID,a.UNCATEGORIZED_FLAG,b.current_working_flag
INTO l_group_resource_type_id,l_uncategorized_flag,l_current_working_flag
FROM pa_resource_lists_all_bg a, pa_budget_versions b
WHERE b.budget_version_id = p_budget_version_id
AND a.RESOURCE_LIST_ID = b.resource_list_id;
SELECT FIN_PLAN_PREFERENCE_CODE
,fin_plan_type_id
,project_id
,cost_time_phased_code
,revenue_time_phased_code
,all_time_phased_code
,cost_period_mask_id
,rev_period_mask_id
,all_period_mask_id
INTO l_plan_pref_code
,l_fin_plan_type_id
,l_project_id
,l_cost_time_phased_code
,l_revenue_time_phased_code
,l_all_time_phased_code
,l_cost_period_mask_id
,l_revenue_period_mask_id
,l_all_period_mask_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_budget_version_id
AND FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION';
SELECT pfo.cost_layout_code
,pfo.revenue_layout_code
,pfo.all_layout_code
,ptb.plan_class_code
INTO l_cost_layout_code
,l_revenue_layout_code
,l_all_layout_code
,l_plan_class_code
FROM pa_proj_fp_options pfo
,pa_fin_plan_types_b ptb
WHERE pfo.fin_plan_type_id = l_fin_plan_type_id
AND pfo.FIN_PLAN_version_id IS NULL
AND pfo.fin_plan_type_id = ptb.fin_plan_type_id
AND pfo.project_id = l_project_id;
SELECT integrator_code
INTO l_integrator_code
FROM bne_layouts_b
WHERE layout_code = x_layout_code
and application_id = (SELECT application_id
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PA');
SELECT user_name
INTO l_layout_meaning
FROM bne_layouts_tl
WHERE layout_code = x_layout_code
AND language = userenv('lang')
AND application_id = (SELECT application_id
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PA');
SELECT meaning
INTO l_plan_class_name
FROM pa_lookups
WHERE lookup_type = 'FIN_PLAN_CLASS'
AND Lookup_code = l_plan_class_code;
SELECT COUNT(*)
INTO l_no_of_periods
FROM pa_period_mask_details
WHERE period_mask_id = l_cost_period_mask_id
AND from_anchor_position not in (99999,-99999);
SELECT COUNT(*)
INTO l_no_of_periods
FROM pa_period_mask_details
WHERE period_mask_id = l_revenue_period_mask_id
AND from_anchor_position not in (99999,-99999);
SELECT COUNT(*)
INTO l_no_of_periods
FROM pa_period_mask_details
WHERE period_mask_id = l_all_period_mask_id
AND from_anchor_position not in (99999,-99999);
SELECT pt.start_date,pt.completion_date
FROM pa_resource_assignments pra,
pa_budget_versions pbv,
pa_tasks pt
WHERE pra.budget_version_id = pbv.budget_version_id
AND pbv.budget_version_id = l_budget_version_id
AND pt.task_id = pra.task_id
AND pra.project_id = pbv.project_id
AND pbv.project_id = pt.task_id;
SELECT period_profile_id
,project_id
INTO l_period_profile_id
,l_project_id
FROM pa_budget_versions
WHERE budget_version_id = l_budget_version_id;
SELECT start_date
,completion_date
INTO l_project_start_date
,l_project_end_date
FROM pa_projects_all p
WHERE p.project_id = l_project_id;
select task_id
into x_task_id
from pa_tasks
where project_id = p_project_id
and task_number = p_task_num;
select fp.PLAN_IN_MULTI_CURR_FLAG,
fp.proj_fp_options_id
into l_multi_curr_flag,
l_proj_fp_options_id
from pa_proj_fp_options fp, pa_budget_versions bv
where bv.budget_version_id = p_budget_version_id
and fp.fin_plan_version_id = p_budget_version_id
and fp.fin_plan_type_id = bv.fin_plan_type_id
and fp.fin_plan_option_level_code = 'PLAN_VERSION'
and fp.project_id = bv.project_id;
select fp_txn_currency_id
into l_txn_currency_id
from pa_fp_txn_currencies
where proj_fp_options_id = l_proj_fp_options_id --Sql Performance to avoid FTS fix sql id 16509328
and txn_currency_code = p_currency_code;
Select resource_list_id, project_id
into l_resource_list_id,l_project_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
SELECT GROUP_RESOURCE_TYPE_ID,UNCATEGORIZED_FLAG
INTO l_group_resource_type_id,l_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE RESOURCE_LIST_ID = l_resource_list_id;
Select resource_list_member_id
into l_rlm_id_gp
from pa_resource_list_members
where resource_list_id = l_resource_list_id
and parent_member_id is NULL
and alias = p_resource_group_name;
SELECT count(*)
INTO l_dummy_id
FROM pa_resource_list_members
WHERE resource_list_id = l_resource_list_id
AND parent_member_id = l_rlm_id_gp
AND rownum=1;
Select resource_list_member_id
into l_rlm_id_alias
from pa_resource_list_members
where resource_list_id = l_resource_list_id
and parent_member_id = l_rlm_id_gp
and alias = p_resource_alias;
Select resource_list_member_id
into l_rlm_id_alias
from pa_resource_list_members
where resource_list_id = l_resource_list_id
and alias = p_resource_alias;
SELECT pt.task_number
,pt.task_id
,decode(prlm.parent_member_id,null,decode(prl.group_resource_type_id,0,rtrim(prlm.alias),null)
,rtrim(prlm.alias)) resource_alias -- Added rtrim for #2839138
,decode(prlm.parent_member_id,null,rtrim(prlm.alias),rtrim(prlm_parent.alias)) resource_group_alias
-- Added rtrim for #2839138
,pra.unit_of_measure
,pra.parent_assignment_id
,prlm.resource_list_member_id
,prlm.resource_id
FROM pa_tasks pt
,pa_resource_assignments pra
,pa_resource_list_members prlm
,pa_resource_list_members prlm_parent
,pa_resource_lists_all_bg prl
WHERE pra.resource_assignment_id = c_resource_assignment_id
AND pra.project_id = pt.project_id
AND pra.task_id = pt.task_id
AND prlm.resource_list_member_id = pra.resource_list_member_id
AND prlm.parent_member_id = prlm_parent.resource_list_member_id(+)
AND prl.resource_list_id = prlm.resource_list_id;
SELECT decode(prlm.parent_member_id,null,decode(prl.group_resource_type_id,0,rtrim(prlm.alias),null)
,rtrim(prlm.alias)) resource_alias -- Added rtrim for #2839138
,decode(prlm.parent_member_id,null,rtrim(prlm.alias),rtrim(prlm_parent.alias)) resource_group_alias
-- Added rtrim for #2839138
,pra.unit_of_measure
,pra.parent_assignment_id
,prlm.resource_list_member_id
,prlm.resource_id
FROM pa_resource_assignments pra
,pa_resource_list_members prlm
,pa_resource_list_members prlm_parent
,pa_resource_lists_all_bg prl
WHERE pra.resource_assignment_id = c_resource_assignment_id
AND prlm.resource_list_member_id = pra.resource_list_member_id
AND prl.resource_list_id = prlm.resource_list_id
AND prlm.parent_member_id = prlm_parent.resource_list_member_id(+);
SELECT LOOKUP_CODE
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'BUDGET CHANGE REASON'
AND LOOKUP_CODE = p_change_reason_code ;
SELECT txn_currency_code
FROM pa_fp_txn_currencies
WHERE fin_plan_version_id = p_budget_version_id
AND proj_fp_options_id = p_proj_fp_options_id ;
SELECT
null -- For SD
, null -- For PD
, ppp.period_name1
, ppp.period_name2
, ppp.period_name3
, ppp.period_name4
, ppp.period_name5
, ppp.period_name6
, ppp.period_name7
, ppp.period_name8
, ppp.period_name9
, ppp.period_name10
, ppp.period_name11
, ppp.period_name12
, ppp.period_name13
, ppp.period_name14
, ppp.period_name15
, ppp.period_name16
, ppp.period_name17
, ppp.period_name18
, ppp.period_name19
, ppp.period_name20
, ppp.period_name21
, ppp.period_name22
, ppp.period_name23
, ppp.period_name24
, ppp.period_name25
, ppp.period_name26
, ppp.period_name27
, ppp.period_name28
, ppp.period_name29
, ppp.period_name30
, ppp.period_name31
, ppp.period_name32
, ppp.period_name33
, ppp.period_name34
, ppp.period_name35
, ppp.period_name36
, ppp.period_name37
, ppp.period_name38
, ppp.period_name39
, ppp.period_name40
, ppp.period_name41
, ppp.period_name42
, ppp.period_name43
, ppp.period_name44
, ppp.period_name45
, ppp.period_name46
, ppp.period_name47
, ppp.period_name48
, ppp.period_name49
, ppp.period_name50
, ppp.period_name51
, ppp.period_name52
, null -- For SD
, null -- For PD
, ppp.period1_start_date
, ppp.period2_start_date
, ppp.period3_start_date
, ppp.period4_start_date
, ppp.period5_start_date
, ppp.period6_start_date
, ppp.period7_start_date
, ppp.period8_start_date
, ppp.period9_start_date
, ppp.period10_start_date
, ppp.period11_start_date
, ppp.period12_start_date
, ppp.period13_start_date
, ppp.period14_start_date
, ppp.period15_start_date
, ppp.period16_start_date
, ppp.period17_start_date
, ppp.period18_start_date
, ppp.period19_start_date
, ppp.period20_start_date
, ppp.period21_start_date
, ppp.period22_start_date
, ppp.period23_start_date
, ppp.period24_start_date
, ppp.period25_start_date
, ppp.period26_start_date
, ppp.period27_start_date
, ppp.period28_start_date
, ppp.period29_start_date
, ppp.period30_start_date
, ppp.period31_start_date
, ppp.period32_start_date
, ppp.period33_start_date
, ppp.period34_start_date
, ppp.period35_start_date
, ppp.period36_start_date
, ppp.period37_start_date
, ppp.period38_start_date
, ppp.period39_start_date
, ppp.period40_start_date
, ppp.period41_start_date
, ppp.period42_start_date
, ppp.period43_start_date
, ppp.period44_start_date
, ppp.period45_start_date
, ppp.period46_start_date
, ppp.period47_start_date
, ppp.period48_start_date
, ppp.period49_start_date
, ppp.period50_start_date
, ppp.period51_start_date
, ppp.period52_start_date
, null -- For SD
, null -- For PD
, ppp.period1_end_date
, ppp.period2_end_date
, ppp.period3_end_date
, ppp.period4_end_date
, ppp.period5_end_date
, ppp.period6_end_date
, ppp.period7_end_date
, ppp.period8_end_date
, ppp.period9_end_date
, ppp.period10_end_date
, ppp.period11_end_date
, ppp.period12_end_date
, ppp.period13_end_date
, ppp.period14_end_date
, ppp.period15_end_date
, ppp.period16_end_date
, ppp.period17_end_date
, ppp.period18_end_date
, ppp.period19_end_date
, ppp.period20_end_date
, ppp.period21_end_date
, ppp.period22_end_date
, ppp.period23_end_date
, ppp.period24_end_date
, ppp.period25_end_date
, ppp.period26_end_date
, ppp.period27_end_date
, ppp.period28_end_date
, ppp.period29_end_date
, ppp.period30_end_date
, ppp.period31_end_date
, ppp.period32_end_date
, ppp.period33_end_date
, ppp.period34_end_date
, ppp.period35_end_date
, ppp.period36_end_date
, ppp.period37_end_date
, ppp.period38_end_date
, ppp.period39_end_date
, ppp.period40_end_date
, ppp.period41_end_date
, ppp.period42_end_date
, ppp.period43_end_date
, ppp.period44_end_date
, ppp.period45_end_date
, ppp.period46_end_date
, ppp.period47_end_date
, ppp.period48_end_date
, ppp.period49_end_date
, ppp.period50_end_date
, ppp.period51_end_date
, ppp.period52_end_date
, ppp.number_of_periods
, pbv.period_profile_id
FROM
pa_proj_period_profiles ppp
, pa_budget_versions pbv
WHERE pbv.budget_version_id = p_budget_version_id
AND ppp.period_profile_id = pbv.period_profile_id ;
pa_debug.write('DELETE_XFACE' || g_module_name,SQLERRM,4);
pa_debug.write('DELETE_XFACE' || g_module_name,pa_debug.G_Err_Stack,4);
SELECT a.rowid
FROM PA_FP_WEBADI_XFACE_TMP a
WHERE a.budget_version_id = p_budget_version_id
AND ( EXISTS (SELECT 'Y'
FROM PA_FP_WEBADI_XFACE_TMP b
WHERE a.rowid <> b.rowid
AND b.budget_version_id = p_budget_version_id
AND b.resource_assignment_id = a.resource_assignment_id
AND b.txn_currency_code = a.txn_currency_code
AND a.start_date <= b.end_date
AND a.end_date >= b.start_date )
OR EXISTS (SELECT 'Y'
FROM PA_BUDGET_LINES bl
WHERE bl.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = a.resource_assignment_id
AND bl.txn_currency_code = a.txn_currency_code
AND bl.start_date <> a.start_date
AND a.start_date <= bl.end_date
AND a.end_date >= bl.start_date )) ;
UPDATE PA_FP_WEBADI_XFACE_TMP tmp
SET val_error_code = 'PA_FP_WEBADI_OVERLAPPING_DATE'
,val_error_flag = 'Y'
WHERE rowid = l_rowid ;
select conversion_type, user_conversion_type
from pa_conversion_types_v
where user_conversion_type IN (p_pc_cost_rate_type_name
,p_pfc_cost_rate_type_name
,p_pc_rev_rate_type_name
,p_pfc_rev_rate_type_name);
select lookup_code, lookup_type, meaning
from pa_lookups
where lookup_type = 'PA_FP_RATE_DATE_TYPE'
and meaning IN (p_pc_cost_rate_date_type_name
,p_pfc_cost_rate_date_type_name
,p_pc_rev_rate_date_type_name
,p_pfc_rev_rate_date_type_name);
SELECT amount_type_name
INTO l_amount_type_name
FROM pa_amount_types_vl
WHERE amount_type_code = p_amount_type_code;
SELECT integrator_code
INTO l_integrator_code
FROM bne_layouts_b
WHERE layout_code= p_layout_code
AND application_id = (SELECT application_id
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PA');
PROCEDURE delete_interface_tbl_data
(p_request_id IN pa_budget_versions.request_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_debug_mode VARCHAR2(30);
l_module_name VARCHAR2(100) := 'PAFPWAUB.delete_interface_tbl_data';
pa_debug.g_err_stage:='Entering delete_inter_face_data';
SELECT run_id,
budget_version_id
INTO l_run_id,
l_budget_version_id
FROM pa_fp_webadi_upload_inf
WHERE request_id = p_request_id
AND ROWNUM = 1;
pa_debug.g_err_stage:='Calling PA_FP_WEBADI_PKG.delete_xface';
PA_FP_WEBADI_PKG.delete_xface
( p_run_id => l_run_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := 'Call to PA_FP_WEBADI_PKG.delete_xface returned with error';
pa_debug.g_err_stage:='PA_FP_WEBADI_PKG.delete_xface Called';
UPDATE pa_budget_versions
SET plan_processing_code = null,
request_id = null,
record_version_number = record_version_number + 1
WHERE budget_version_id = l_budget_version_id;
pa_debug.g_err_stage:='Leaving delete_interface_tbl_data';
,p_procedure_name => 'delete_interface_tbl_data');
END delete_interface_tbl_data;
SELECT run_id,
budget_version_id
INTO l_run_id,
l_budget_version_id
FROM pa_fp_webadi_upload_inf
WHERE request_id = p_old_request_id
AND ROWNUM = 1;
SELECT ppa.org_id
INTO l_org_id
FROM pa_projects_all ppa,
pa_budget_versions pbv
WHERE pbv.project_id = ppa.project_id
AND pbv.budget_version_id = l_budget_version_id;
UPDATE pa_budget_versions
SET plan_processing_code = 'XLUE'
WHERE budget_version_id = l_budget_version_id;
UPDATE pa_budget_versions
SET plan_processing_code = 'XLUP',
request_id = l_new_request_id
WHERE budget_version_id = l_budget_version_id;
UPDATE pa_fp_webadi_upload_inf
SET request_id = l_new_request_id
WHERE budget_version_id = l_budget_version_id
AND run_id = l_run_id;
select 1
from pa_budget_lines pbl, pa_resource_assignments pra
where pra.project_id = p_project_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id
and pbl.budget_version_id = l_curr_bv_id;
select pbv.budget_version_id
into l_curr_bv_id
from pa_budget_versions pbv,
pa_proj_fp_options pfo
where pfo.fin_plan_type_id = p_fin_plan_type_id
and pfo.project_id = p_project_id
and pfo.fin_plan_option_level_code = 'PLAN_VERSION'
and pfo.fin_plan_preference_code = p_fin_plan_preference_code
and pfo.fin_plan_version_id = pbv.budget_version_id
and pbv.current_flag = 'Y';
select bv.budget_version_id
into l_curr_bv_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_cost_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id
into l_curr_bv_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_rev_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
/* Bug 5144013 : Changed the following select queries to refer
to new entity pa_resource_asgn_curr instead of pa_budget_lines.
This is done as part of merging the MRUP3 changes done in 11i into R12.
if p_amount = 'QUANTITY' THEN
select total_display_quantity into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_curr_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_raw_cost into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_curr_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_burdened_cost into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_curr_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_revenue into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_curr_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_display_quantity
,total_txn_raw_cost
,total_txn_burdened_cost
,total_txn_revenue
into l_quantity
,l_txn_raw_cost
,l_txn_burdened_cost
,l_txn_revenue
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_curr_bv_id
and pra.project_id = p_project_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select 1
from pa_budget_lines pbl, pa_resource_assignments pra
where pra.project_id = p_project_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id
and pbl.budget_version_id = l_orig_bv_id;
select pbv.budget_version_id
into l_orig_bv_id
from pa_budget_versions pbv,
pa_proj_fp_options pfo
where pfo.fin_plan_type_id = p_fin_plan_type_id
and pfo.project_id = p_project_id
and pfo.fin_plan_option_level_code = 'PLAN_VERSION'
and pfo.fin_plan_preference_code = p_fin_plan_preference_code
and pfo.fin_plan_version_id = pbv.budget_version_id
and pbv.current_original_flag = 'Y';
select bv.budget_version_id
into l_orig_bv_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_cost_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_original_flag = 'Y';
select bv.budget_version_id
into l_orig_bv_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_rev_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_original_flag = 'Y';
select bv.budget_version_id
into l_orig_bv_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_cost_plan_type_flag = 'Y' and
bv.approved_rev_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_original_flag = 'Y';
/* Bug 5144013 : Changed the following select queries to refer
to new entity pa_resource_asgn_curr instead of pa_budget_lines.
This is done as part of merging the MRUP3 changes done in 11i into R12.
*/
if p_amount = 'QUANTITY' THEN
select total_display_quantity into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_orig_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_raw_cost into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_orig_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_burdened_cost into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_orig_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_revenue into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_orig_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select 1
from pa_budget_lines pbl, pa_resource_assignments pra
where pra.project_id = p_project_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id
and pbl.budget_version_id = l_pf_bv_id;
select pbv.budget_version_id
into l_pf_bv_id
from pa_budget_versions pbv,
pa_proj_fp_options pfo
where pfo.fin_plan_type_id = p_fin_plan_type_id
and pfo.project_id = p_project_id
and pfo.fin_plan_option_level_code = 'PLAN_VERSION'
and pfo.fin_plan_preference_code = p_fin_plan_preference_code
and pfo.fin_plan_version_id = pbv.budget_version_id
and pbv.current_flag = 'Y';
/* Bug 5144013 : Changed the following select queries to refer
to new entity pa_resource_asgn_curr instead of pa_budget_lines.
This is done as part of merging the MRUP3 changes done in 11i into R12.
*/
if p_amount = 'QUANTITY' THEN
select total_display_quantity into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_pf_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_raw_cost into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_pf_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_burdened_cost into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_pf_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select total_txn_revenue into l_quantity
from pa_resource_asgn_curr rac,
pa_resource_assignments pra
where rac.budget_version_id = l_pf_bv_id
and pra.budget_version_id = rac.budget_version_id
and pra.task_id = p_task_id
and pra.resource_list_member_id = p_resource_list_member_id
and pra.unit_of_measure = p_uom
and pra.resource_assignment_id = rac.resource_assignment_id
and rac.txn_currency_code = p_txn_curr_code;
select decode(p_amount_code,'RAW_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))))))
/decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
'ETC_RAW_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))))))
/decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
'BURDENED_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))))))
/decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
'ETC_BURDENED_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))))))
/decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
'BILL_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))))))
/decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
'ETC_BILL_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))))))
/decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
(nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
'TOTAL_QTY',sum(bl.display_quantity),
'FCST_QTY',sum(bl.display_quantity),
'TOTAL_RAW_COST' ,sum(bl.txn_raw_cost),
'FCST_RAW_COST' ,sum(bl.txn_raw_cost),
'TOTAL_REV' ,sum(bl.txn_revenue),
'FCST_REVENUE' ,sum(bl.txn_revenue),
'TOTAL_BURDENED_COST' ,sum(bl.txn_burdened_cost),
'FCST_BURDENED_COST' ,sum(bl.txn_burdened_cost),
'ACTUAL_QTY',sum(bl.init_quantity),
'ACTUAL_RAW_COST',sum(bl.txn_init_raw_cost),
'ACTUAL_BURD_COST',sum(bl.txn_init_burdened_cost),
'ACTUAL_REVENUE',sum(bl.txn_init_revenue),
'ETC_QTY',DECODE(sum(bl.display_quantity),null,null,sum(bl.quantity-nvl(bl.init_quantity,0))),
'ETC_RAW_COST',sum(bl.txn_raw_cost-nvl(bl.txn_init_raw_cost,0)),
'ETC_BURDENED_COST',sum(bl.txn_burdened_cost-nvl(bl.txn_init_burdened_cost,0)),
'ETC_REVENUE', sum(bl.txn_revenue-nvl(bl.txn_init_revenue,0)))
into l_return
from pa_budget_lines bl,
pa_resource_assignments pra
where bl.budget_version_id = p_budget_version_id
and bl.resource_assignment_id = p_resource_assignment_id
and bl.txn_currency_code = p_txn_currency_code
and pra.resource_assignment_id = bl.resource_assignment_id
and ((p_prd_start_date is not null and p_prd_end_date is not null and (decode(bl.start_date,p_prd_start_date,1,
decode(bl.end_date,p_prd_end_date,1,
decode((((p_prd_end_date-bl.end_date)/(abs(p_prd_end_date-bl.end_date)))*((bl.start_date-p_prd_start_date)/(abs(bl.start_date-p_prd_start_date)))),-1,0,1)))=1))
or
(p_prd_start_date is null and p_prd_end_date is null and decode(preceding_date,null,decode(((bl.start_date-succedeing_date)/abs(bl.start_date-succedeing_date)),1,1,0),
decode(((bl.end_date-preceding_date)/abs(bl.end_date-preceding_date)),-1,1,0))=1))
GROUP BY pra.rate_based_flag;