[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ INDEX(t3,PA_FP_CALC_AMT_TMP3_N2)*/
t3.res_list_member_id,
ra.task_id,
decode(c_multi_curr_flag,'Y',bl.txn_currency_code,
'N',c_project_currency_code),
sum(bl.quantity),
sum(decode(c_multi_curr_flag,'Y',bl.txn_raw_cost,
'N',bl.project_raw_cost)),
sum(decode(c_multi_curr_flag,'Y',bl.txn_burdened_cost,
'N',bl.project_burdened_cost)),
sum(decode(c_multi_curr_flag,'Y',bl.txn_revenue,
'N',bl.project_revenue)),
sum(bl.project_raw_cost),
sum(bl.project_burdened_cost),
sum(bl.project_revenue),
sum(bl.raw_cost),
sum(bl.burdened_cost),
sum(bl.revenue)
FROM pa_resource_assignments ra,
pa_budget_lines bl,
pa_fp_calc_amt_tmp3 t3
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.resource_assignment_id = t3.res_asg_id
AND ra.budget_version_id = p_source_bv_id
AND bl.end_date <= nvl(p_actual_thru_date,bl.end_date)
and bl.cost_rejection_code is null
and bl.revenue_rejection_code is null
and bl.burden_rejection_code is null
and bl.other_rejection_code is null
and bl.pc_cur_conv_rejection_code is null
and bl.pfc_cur_conv_rejection_code is null
GROUP BY
t3.res_list_member_id,
ra.task_id,
decode(c_multi_curr_flag,'Y',bl.txn_currency_code,
'N',c_project_currency_code);
INSERT INTO PA_FP_CALC_AMT_TMP3
(plan_version_id,
res_list_member_id,
res_asg_id)
VALUES
(p_etc_fp_cols_rec.x_budget_version_id,
l_res_list_member_id_tab(i),
l_txn_src_id_tab(i));
SELECT ra.task_id,
ra.resource_assignment_id
BULK COLLECT
INTO l_tsk_id_tab,
l_resrc_assgn_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_etc_fp_cols_rec.
x_budget_version_id;
UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N2)*/
PA_FP_CALC_AMT_TMP3
SET task_id = l_tsk_id_tab(m)
WHERE res_asg_id = l_resrc_assgn_id(m);
DELETE /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
FROM pa_fp_calc_amt_tmp3
WHERE plan_version_id =
P_ETC_FP_COLS_REC.x_budget_version_id;
INSERT INTO pa_fp_calc_amt_tmp3
(plan_version_id,
task_id,
res_list_member_id,
res_asg_id,
txn_currency_code,
quantity,
txn_raw_cost,
txn_burdened_cost,
txn_revenue,
pc_raw_cost,
pc_burdened_cost,
pc_revenue,
pfc_raw_cost,
pfc_burdened_cost,
pfc_revenue)
VALUES (P_ETC_FP_COLS_REC.x_budget_version_id,
l_task_id_tab(k),
l_rlm_id_tab(k),
l_txn_src_id_tab(k),
l_txn_currency_code_tab(k),
l_qty_tab(k),
l_txn_raw_cost_sum_tab(k),
l_txn_burdend_cost_sum_tab(k),
l_txn_revenue_sum_tab(k),
l_pc_raw_cost_sum_tab(k),
l_pc_burdend_cost_sum_tab(k),
l_pc_revenue_sum_tab(k),
l_pfc_raw_cost_sum_tab(k),
l_pfc_burdend_cost_sum_tab(k),
l_pfc_revenue_sum_tab(k));
INSERT INTO PA_FP_CALC_AMT_TMP3
(plan_version_id,
task_id,
res_list_member_id,
res_asg_id,
txn_currency_code,
quantity,
txn_raw_cost,
txn_burdened_cost,
txn_revenue,
pc_raw_cost,
pc_burdened_cost,
pc_revenue,
pfc_raw_cost,
pfc_burdened_cost,
pfc_revenue)
(SELECT ra.budget_version_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y', bl.txn_currency_code,
'N',p_target_fp_cols_rec.x_project_currency_code),
sum(bl.quantity),
sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_raw_cost,
'N',bl.project_raw_cost)),
sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_burdened_cost,
'N',bl.project_burdened_cost)),
sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_revenue,
'N',bl.project_revenue)),
sum(bl.project_raw_cost),
sum(bl.project_burdened_cost),
sum(bl.project_revenue),
sum(bl.raw_cost),
sum(bl.burdened_cost),
sum(bl.revenue)
FROM pa_resource_assignments ra,
pa_budget_lines bl
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = p_source_bv_id
and bl.cost_rejection_code is null
and bl.revenue_rejection_code is null
and bl.burden_rejection_code is null
and bl.other_rejection_code is null
and bl.pc_cur_conv_rejection_code is null
and bl.pfc_cur_conv_rejection_code is null
GROUP BY
ra.budget_version_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_target_fp_cols_rec.x_project_currency_code));
INSERT INTO PA_FP_CALC_AMT_TMP3
(plan_version_id,
task_id,
res_list_member_id,
res_asg_id,
txn_currency_code,
quantity,
txn_raw_cost,
txn_burdened_cost,
txn_revenue,
pc_raw_cost,
pc_burdened_cost,
pc_revenue,
pfc_raw_cost,
pfc_burdened_cost,
pfc_revenue)
(SELECT ra.budget_version_id,
ra.task_id,
l_uc_res_list_rlm_id,
ra.resource_assignment_id,
decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y', bl.txn_currency_code,
'N',p_target_fp_cols_rec.x_project_currency_code),
sum(bl.quantity),
sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_raw_cost,
'N',bl.project_raw_cost)),
sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_burdened_cost,
'N',bl.project_burdened_cost)),
sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_revenue,
'N',bl.project_revenue)),
sum(bl.project_raw_cost),
sum(bl.project_burdened_cost),
sum(bl.project_revenue),
sum(bl.raw_cost),
sum(bl.burdened_cost),
sum(bl.revenue)
FROM pa_resource_assignments ra,
pa_budget_lines bl
WHERE ra.resource_assignment_id = bl.resource_assignment_id
AND ra.budget_version_id = p_source_bv_id
and bl.cost_rejection_code is null
and bl.revenue_rejection_code is null
and bl.burden_rejection_code is null
and bl.other_rejection_code is null
and bl.pc_cur_conv_rejection_code is null
and bl.pfc_cur_conv_rejection_code is null
GROUP BY
ra.budget_version_id,
ra.task_id,
l_uc_res_list_rlm_id,
ra.resource_assignment_id,
decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_target_fp_cols_rec.x_project_currency_code));
UPDATE pa_resource_assignments
SET rbs_element_id = l_rbs_element_id_tab(i),
txn_accum_header_id = l_txn_accum_header_id_tab(i)
WHERE resource_assignment_id = l_txn_src_id_tab(i);