[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_deleted_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
lx_deleted_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
'Before calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE',
p_module_name => l_module_name);
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_ETC_START_DATE => P_ACTUALS_THRU_DATE + 1,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
p_msg => 'After calling PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_BV_FOR_GEN_DATE,
ret status: '||x_return_status,
p_module_name => l_module_name);
DELETE FROM PA_FP_CALC_AMT_TMP1;
DELETE FROM PA_FP_CALC_AMT_TMP2;
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
SELECT plan_class_code
INTO l_src_plan_class_code
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = P_ETC_PLAN_TYPE_ID;
SELECT version_type
INTO l_src_version_type
FROM pa_budget_versions
WHERE budget_version_id = P_ETC_PLAN_VERSION_ID;
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
P_COMMIT_FLAG => 'N',
P_INIT_MSG_FLAG => 'N',
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA );
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
P_COMMIT_FLAG => 'N',
P_INIT_MSG_FLAG => 'N',
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PX_DELETED_RES_ASG_ID_TAB => lx_deleted_res_asg_id_tab,
PX_GEN_RES_ASG_ID_TAB => lx_gen_res_asg_id_tab,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA );
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PX_DELETED_RES_ASG_ID_TAB => l_deleted_res_asg_id_tab,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
SELECT count(*)
INTO l_task_count
FROM pa_tasks
WHERE project_id = p_project_id
AND gen_etc_source_code = 'FINANCIAL_PLAN'
AND gen_etc_source_code IS NOT NULL;
'update_total_plan_amts',
P_MODULE_NAME => l_module_name);
PA_FP_GEN_FCST_AMT_PVT.UPDATE_TOTAL_PLAN_AMTS
(P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
'UPDATE_TOTAL_PLAN_AMTS:'||x_return_status,
P_MODULE_NAME => l_module_name);
PA_FP_GEN_FCST_AMT_PUB1.call_clnt_extn_and_update_bl
(p_project_id => p_project_id
,p_budget_version_id => p_budget_version_id
,x_call_maintain_data_api => l_call_maintain_data_api
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
'call_clnt_extn_and_update_bl '||x_return_status,
P_MODULE_NAME => l_module_name);
SELECT task_id,
DECODE(c_gen_etc_src_code,
NULL,NVL(gen_etc_source_code,'NONE'),
c_gen_etc_src_code)
FROM pa_tasks t
WHERE project_id = P_PROJECT_ID;
SELECT task_id,
DECODE(c_gen_etc_src_code,
NULL,NVL(gen_etc_source_code,'NONE'),
c_gen_etc_src_code)
FROM pa_tasks t
WHERE project_id = P_PROJECT_ID and
parent_task_id is null;
SELECT task_id
FROM pa_tasks t
WHERE project_id = P_PROJECT_ID and
top_task_id = l_temp_top_task_id and
task_id <> top_task_id; -- don't want to retrieve the current node
SELECT tmp1.task_id,
NVL(c_gen_etc_source_code, NVL(tmp1.transaction_source_code,'NONE')),
tmp1.resource_assignment_id,
tmp1.target_res_asg_id,
tmp1.resource_list_member_id,
tmp1.etc_method_code
FROM PA_FP_CALC_AMT_TMP1 tmp1;
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
L_RES_ASG_UOM_UPDATE_TAB PA_PLSQL_DATATYPES.IdTabTyp;
* When the ETC generation source is Task Level Selection, the
* c_gen_etc_source_code cursor parameter should be NULL so that the
* cursor picks up each task's generation source. */
CURSOR etc_amts_cur_wp_fp_opt_same
(c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
tmp.RESOURCE_ASSIGNMENT_ID,
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
tmp_ra.etc_method_code,
SUM(tmp.ETC_PLAN_QUANTITY),
SUM(tmp.ETC_TXN_RAW_COST),
SUM(tmp.ETC_TXN_BURDENED_COST),
SUM(tmp.ETC_TXN_REVENUE),
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
nvl(tmp_ra.transaction_source_code, 'NONE'))
FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
PA_FP_CALC_AMT_TMP2 tmp,
PA_RESOURCE_ASSIGNMENTS ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Bug 4346172 */
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
GROUP BY tmp.RESOURCE_ASSIGNMENT_ID,
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
tmp_ra.etc_method_code,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
nvl(tmp_ra.transaction_source_code, 'NONE'));
* When the ETC generation source is Task Level Selection, the
* c_gen_etc_source_code cursor parameter should be NULL so that the
* cursor picks up each task's generation source. */
CURSOR etc_amts_cur_wp_fp_opt_diff
(c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
sum(1*null),
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
null, --ra.etc_method_code,
SUM(tmp.ETC_PLAN_QUANTITY),
SUM(tmp.ETC_TXN_RAW_COST),
SUM(tmp.ETC_TXN_BURDENED_COST),
SUM(tmp.ETC_TXN_REVENUE),
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
nvl(tmp_ra.transaction_source_code, 'NONE'))
FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
PA_FP_CALC_AMT_TMP2 tmp,
PA_RESOURCE_ASSIGNMENTS ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id /* Added for Bug 4369741 */
AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Added for Bug 4369741 */
AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
GROUP BY
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
null, --ra.etc_method_code,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
nvl(tmp_ra.transaction_source_code, 'NONE'));
* This cursor's SELECT statement uses the etc_amts_cur_wp_fp_opt_same
* cursor's SELECT statement for resources with Workplan source UNION
* ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
* resources with non-Workplan source. */
CURSOR etc_amts_cur_wp_opt_same IS
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
tmp.RESOURCE_ASSIGNMENT_ID,
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
tmp_ra.etc_method_code,
SUM(tmp.ETC_PLAN_QUANTITY),
SUM(tmp.ETC_TXN_RAW_COST),
SUM(tmp.ETC_TXN_BURDENED_COST),
SUM(tmp.ETC_TXN_REVENUE),
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code /* Added for Bug 4369741 */
FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
PA_FP_CALC_AMT_TMP2 tmp,
PA_RESOURCE_ASSIGNMENTS ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES' /* Added for Bug 4369741 */
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
GROUP BY tmp.RESOURCE_ASSIGNMENT_ID,
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
tmp_ra.etc_method_code,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code /* Added for Bug 4369741 */
UNION ALL
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
sum(1*null),
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
null, --ra.etc_method_code,
SUM(tmp.ETC_PLAN_QUANTITY),
SUM(tmp.ETC_TXN_RAW_COST),
SUM(tmp.ETC_TXN_BURDENED_COST),
SUM(tmp.ETC_TXN_REVENUE),
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code /* Added for Bug 4369741 */
FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
PA_FP_CALC_AMT_TMP2 tmp,
PA_RESOURCE_ASSIGNMENTS ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id /* Added for Bug 4369741 */
AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Added for Bug 4369741 */
AND tmp_ra.transaction_source_code <> 'WORKPLAN_RESOURCES' /* Added for Bug 4369741 */
AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
GROUP BY
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
null, --ra.etc_method_code,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code; /* Added for Bug 4369741 */
* This cursor's SELECT statement uses the etc_amts_cur_wp_fp_opt_same
* cursor's SELECT statement for resources with Financial Plan source
* UNION ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
* resources with non Financial Plan source. */
CURSOR etc_amts_cur_fp_opt_same IS
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
tmp.RESOURCE_ASSIGNMENT_ID,
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
tmp_ra.etc_method_code,
SUM(tmp.ETC_PLAN_QUANTITY),
SUM(tmp.ETC_TXN_RAW_COST),
SUM(tmp.ETC_TXN_BURDENED_COST),
SUM(tmp.ETC_TXN_REVENUE),
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code /* Added for Bug 4369741 */
FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
PA_FP_CALC_AMT_TMP2 tmp,
PA_RESOURCE_ASSIGNMENTS ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN' /* Added for Bug 4369741 */
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
GROUP BY tmp.RESOURCE_ASSIGNMENT_ID,
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
tmp_ra.etc_method_code,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code /* Added for Bug 4369741 */
UNION ALL
SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
sum(1*null),
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
null, --ra.etc_method_code,
SUM(tmp.ETC_PLAN_QUANTITY),
SUM(tmp.ETC_TXN_RAW_COST),
SUM(tmp.ETC_TXN_BURDENED_COST),
SUM(tmp.ETC_TXN_REVENUE),
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code /* Added for Bug 4369741 */
FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
PA_FP_CALC_AMT_TMP2 tmp,
PA_RESOURCE_ASSIGNMENTS ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
and NVL(ra.task_id,0) = ta.task_id (+) /* Added for ER 4376722 */
--and ta.project_id = P_PROJECT_ID /* Added for ER 4376722 */
AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id /* Added for Bug 4369741 */
AND tmp_ra.transaction_source_code <> 'OPEN_COMMITMENTS' /* Added for Bug 4369741 */
AND tmp_ra.transaction_source_code <> 'FINANCIAL_PLAN' /* Added for Bug 4369741 */
AND ra.budget_version_id = P_BUDGET_VERSION_ID /* Added for Bug 4369741 Perf */
AND ra.project_id = P_PROJECT_ID /* Added for Bug 4369741 Perf */
AND tmp_ra.resource_assignment_id = tmp.resource_assignment_id /* Added for Bug 4571025 */
GROUP BY
tmp.TARGET_RES_ASG_ID,
tmp.ETC_CURRENCY_CODE,
ra.rate_based_flag,
ra.resource_list_member_id,
ra.task_id,
ra.unit_of_measure,
null, --ra.etc_method_code,
NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
tmp_ra.transaction_source_code; /* Added for Bug 4369741 */
l_child_task_id_tab.DELETE;
UPDATE pa_fp_fcst_gen_tmp1 tmp
SET tmp.project_element_id = (SELECT pt.top_task_id
FROM pa_tasks pt
WHERE tmp.project_element_id = pt.task_id)
WHERE tmp.data_type_code = 'ETC_FP'
AND tmp.project_element_id
IN
(SELECT pt.task_id
FROM pa_tasks pt
WHERE pt.top_task_id IN (SELECT tmp1.task_id
FROM pa_fp_calc_amt_tmp1 tmp1
WHERE tmp1.budget_version_id =
p_etc_fp_plan_version_id)
AND pt.task_id NOT IN (SELECT tmp1.task_id
FROM pa_fp_calc_amt_tmp1 tmp1
WHERE tmp1.budget_version_id =
p_etc_fp_plan_version_id)
AND pt.project_id=p_project_id
AND pt.task_id<>pt.top_task_id
);
/* select count(*) into l_test from Pa_fp_CALC_AMT_TMP1;
select count(*) into l_test from Pa_fp_CALC_AMT_TMP2;
DELETE FROM PA_FP_CALC_AMT_TMP3;
SELECT DECODE( FIN_PLAN_PREFERENCE_CODE,'COST_ONLY', 'COST' ,
'COST_AND_REV_SEP', 'COST',
'COST_AND_REV_SAME', 'ALL') INTO l_version_type
FROM pa_proj_fp_options
WHERE fin_plan_type_id = l_fin_plan_type_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND project_id = P_PROJECT_ID;
/* select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
SELECT DISTINCT target_res_asg_id
BULK COLLECT INTO l_res_asg_uom_update_tab
FROM PA_FP_CALC_AMT_TMP2
WHERE transaction_source_code = 'ETC';
SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp1.target_res_asg_id
BULK COLLECT
INTO l_res_asg_uom_update_tab
FROM PA_FP_CALC_AMT_TMP1 tmp1,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp1.target_res_asg_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND bl.start_date >= l_etc_start_date
AND rownum = 1 )))
AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
FROM PA_FP_CALC_AMT_TMP2 tmp2
WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
AND tmp2.transaction_source_code = 'ETC'
AND rownum = 1 );
SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
DISTINCT tmp1.target_res_asg_id
BULK COLLECT
INTO l_res_asg_uom_update_tab
FROM PA_FP_CALC_AMT_TMP1 tmp1,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp1.target_res_asg_id
AND ( ra.transaction_source_code IS NOT NULL
OR ( ra.transaction_source_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id =
ra.resource_assignment_id
AND NVL(bl.quantity,0) <>
NVL(bl.init_quantity,0)
AND rownum = 1 )))
AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
FROM PA_FP_CALC_AMT_TMP2 tmp2
WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
AND tmp2.transaction_source_code = 'ETC'
AND rownum = 1 );
FORALL i IN 1..l_res_asg_uom_update_tab.count
UPDATE pa_resource_assignments
SET unit_of_measure = 'DOLLARS',
rate_based_flag = 'N'
WHERE resource_assignment_id = l_res_asg_uom_update_tab(i);
l_cal_ra_id_tab.delete;
l_cal_txn_currency_code_tab.delete;
l_cal_unit_of_measure_tab.delete;
l_cal_etc_qty_tab.delete;
l_cal_etc_raw_cost_tab.delete;
l_cal_etc_burdened_cost_tab.delete;
l_cal_rate_based_flag_tab.delete;
l_cal_rlm_id_tab.delete;
l_cal_task_id_tab.delete;
l_cal_etc_method_code_tab.delete;
SELECT transaction_source_code
INTO l_ra_txn_source_code
FROM pa_resource_assignments
WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i);
SELECT count(*)
INTO l_bl_count
FROM pa_budget_lines
WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i)
AND start_date > p_actuals_thru_date;
SELECT count(*)
INTO l_bl_count
FROM pa_budget_lines
WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i)
AND NVL(quantity,0) <> NVL(init_quantity,0);
plan lines should be deleted and amounts should be generated from the
generation source. */
IF ( l_ra_txn_source_code IS NULL AND l_bl_count > 0 ) THEN
/* Mannually entered lines do exist, so they will be honored,
source records will be dropped */
l_dummy := 1;
DELETE FROM pa_budget_lines
WHERE resource_assignment_id = l_cal_ra_id_tab_tmp(i)
AND start_date > p_actuals_thru_date;
/* End the logic to handle mannually updated lines*/
ELSE
l_cal_ra_id_tab := l_cal_ra_id_tab_tmp;
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp
( resource_assignment_id )
SELECT DISTINCT column_value
FROM TABLE( CAST( l_ra_id_tab_table(i) AS SYSTEM.pa_num_tbl_type ));
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp
( RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
TXN_RAW_COST_RATE_OVERRIDE,
TXN_BURDEN_COST_RATE_OVERRIDE )
SELECT rbc.resource_assignment_id,
rbc.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override
FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = p_budget_version_id
AND rbc.txn_bill_rate_override IS NOT NULL
AND EXISTS ( SELECT null
FROM TABLE(CAST( l_non_billable_fp_ra_id_tab AS SYSTEM.pa_num_tbl_type ))
WHERE rbc.resource_assignment_id = column_value );
P_ROLLUP_FLAG => 'N', -- 'N' indicates Insert
P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
l_cal_src_ra_id_tab_tmp.delete;
l_cal_ra_id_tab_tmp.delete;
l_cal_txn_curr_code_tab_tmp.delete;
l_cal_rate_based_flag_tab_tmp.delete;
l_cal_rlm_id_tab_tmp.delete;
l_cal_task_id_tab_tmp.delete;
l_cal_unit_of_measure_tab_tmp.delete;
l_cal_etc_method_code_tab_tmp.delete;
l_cal_etc_qty_tab_tmp.delete;
l_cal_etc_raw_cost_tab_tmp.delete;
l_cal_etc_brdn_cost_tab_tmp.delete;
l_cal_etc_revenue_tab_tmp.delete;
l_billable_flag_tab_tmp.delete;
SELECT /*+ INDEX(PA_FP_GEN_RATE_TMP,PA_FP_GEN_RATE_TMP_N1)*/
period_name,
raw_cost_rate,
burdened_cost_rate,
revenue_bill_rate
BULK COLLECT
INTO l_ext_period_name_tab,
l_ext_raw_cost_rate_tab,
l_ext_burdened_cost_rate_tab,
l_ext_revenue_bill_rate_tab
FROM pa_fp_gen_rate_tmp
WHERE target_res_asg_id = l_cal_ra_id_tab(i)
AND txn_currency_code = l_cal_txn_currency_code_tab(i);
l_input_period_rates_tbl.delete;
l_input_period_rates_tbl.delete;
l_input_period_rates_tbl.delete;
DELETE /*+ INDEX(PA_FP_GEN_RATE_TMP,PA_FP_GEN_RATE_TMP_N1)*/
FROM pa_fp_gen_rate_tmp
WHERE target_res_asg_id = l_cal_ra_id_tab(i)
AND txn_currency_code = l_cal_txn_currency_code_tab(i);
l_ext_period_name_tab.delete;
l_ext_raw_cost_rate_tab.delete;
l_ext_burdened_cost_rate_tab.delete;
l_ext_revenue_bill_rate_tab.delete;
INSERT INTO PA_FP_GEN_RATE_TMP
( SOURCE_RES_ASG_ID,
TXN_CURRENCY_CODE,
PERIOD_NAME,
RAW_COST_RATE,
BURDENED_COST_RATE,
REVENUE_BILL_RATE,
TARGET_RES_ASG_ID )
VALUES
( l_cal_src_ra_id_tab(i),
l_cal_txn_currency_code_tab(i),
l_ext_period_name_tab(j),
l_ext_raw_cost_rate_tab(j),
l_ext_burdened_cost_rate_tab(j),
l_ext_revenue_bill_rate_tab(j),
l_cal_ra_id_tab(i) );
to select the same data and subtract the amounts from the total amount.
Permanent Fix : The 'Calculate/Spread' API should spread only the amount
passed from the fcst generation process and should not manipulate the
data in any way. We have the p_calling_module parameter and this
parameter should be used to avoid any manipulation to the passed data.
If we go with the above strategy then the following code to select the
actual amount (and adding the actual amount to the ETC amounts) should be
removed and the changes should be made in the
Calculate API/Spread API.
If we are going to address this issue using a different strategy then
the code changes should be made in Fcst gen/Calculate/Spread API.
End bug 3826548
*/
/* bug fix start */
-- Bug 4211776, 4194849: Commented out logic for addition of actuals.
/*
SELECT sum(init_quantity),
sum(txn_init_raw_cost),
sum(txn_init_burdened_cost),
sum(txn_init_revenue)
INTO l_init_qty,
l_init_raw_cost,
l_init_burdened_cost,
l_init_revenue
FROM pa_budget_lines
WHERE resource_assignment_id = l_cal_ra_id_tab(i)
AND txn_currency_code = l_cal_txn_currency_code_tab(i);
l_cal_src_ra_id_tab_tmp.delete;
l_cal_ra_id_tab_tmp.delete;
l_cal_txn_curr_code_tab_tmp.delete;
l_cal_rate_based_flag_tab_tmp.delete;
l_cal_rlm_id_tab_tmp.delete;
l_cal_task_id_tab_tmp.delete;
l_cal_unit_of_measure_tab_tmp.delete;
l_cal_etc_method_code_tab_tmp.delete;
l_cal_etc_qty_tab_tmp.delete;
l_cal_etc_raw_cost_tab_tmp.delete;
l_cal_etc_brdn_cost_tab_tmp.delete;
l_cal_etc_revenue_tab_tmp.delete;
l_billable_flag_tab_tmp.delete;
l_cal_rcost_rate_ovrd_tab_tmp.delete;
l_cal_bcost_rate_ovrd_tab_tmp.delete;
l_cal_bill_rate_ovrd_tab_tmp.delete;
l_cal_ra_id_tab_tmp.delete;
l_cal_txn_curr_code_tab_tmp.delete;
l_cal_rate_based_flag_tab_tmp.delete;
l_cal_rlm_id_tab_tmp.delete;
l_cal_task_id_tab_tmp.delete;
l_cal_unit_of_measure_tab_tmp.delete;
l_cal_etc_method_code_tab_tmp.delete;
l_cal_etc_qty_tab_tmp.delete;
l_cal_etc_raw_cost_tab_tmp.delete;
l_cal_etc_brdn_cost_tab_tmp.delete;
l_cal_etc_revenue_tab_tmp.delete;
l_cal_rcost_rate_ovrd_tab_tmp.delete;
l_cal_bcost_rate_ovrd_tab_tmp.delete;
l_cal_bill_rate_ovrd_tab_tmp.delete;
SELECT nvl(sum(nvl(init_quantity,0)),0)
INTO l_init_qty
FROM pa_budget_lines
WHERE resource_assignment_id = l_cal_ra_id_tab(i)
AND txn_currency_code = l_cal_txn_currency_code_tab(i);
SELECT planning_start_date,
planning_end_date
INTO l_start_date,
l_end_date
FROM pa_resource_assignments
WHERE resource_assignment_id = l_cal_ra_id_tab(i);
l_amt_dtls_tbl.delete;
select count(*) into l_count from
pa_budget_lines where budget_version_id = P_BUDGET_VERSION_ID;
l_delete_budget_lines_tab.extend;
l_delete_budget_lines_tab(i) := Null;
DELETE pa_res_list_map_tmp1;
INSERT INTO pa_res_list_map_tmp1
( txn_resource_assignment_id )
VALUES ( l_cal_ra_id_tab(i) );
/* select etc_start_date into l_date from
pa_budget_versions where budget_version_id = P_BUDGET_VERSION_ID;
and update the planning end date in res asg table.
This logic is handled in the calculate API based on this
new parameter. */
PA_FP_CALC_PLAN_PKG.calculate(
p_calling_module => 'FORECAST_GENERATION',
P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_REFRESH_RATES_FLAG => l_refresh_rates_flag,
P_REFRESH_CONV_RATES_FLAG => l_refresh_conv_rates_flag,
P_SPREAD_REQUIRED_FLAG => l_spread_required_flag,
P_CONV_RATES_REQUIRED_FLAG => l_conv_rates_required_flag,
P_ROLLUP_REQUIRED_FLAG => 'N',
--P_MASS_ADJUST_FLAG
--P_QUANTITY_ADJ_PCT
--P_COST_RATE_ADJ_PCT
--P_BURDENED_RATE_ADJ_PCT
--P_BILL_RATE_ADJ_PCT
P_SOURCE_CONTEXT => l_source_context,
P_RESOURCE_ASSIGNMENT_TAB => l_cal_ra_id_tab,
P_DELETE_BUDGET_LINES_TAB => l_delete_budget_lines_tab,
P_SPREAD_AMTS_FLAG_TAB => l_spread_amts_flag_tab,
P_TXN_CURRENCY_CODE_TAB => l_cal_txn_currency_code_tab,
P_TXN_CURRENCY_OVERRIDE_TAB => l_txn_currency_override_tab,
P_TOTAL_QTY_TAB => l_cal_etc_qty_tab,
P_ADDL_QTY_TAB => l_addl_qty_tab,
P_TOTAL_RAW_COST_TAB => l_cal_etc_raw_cost_tab,
P_ADDL_RAW_COST_TAB => l_addl_raw_cost_tab,
P_TOTAL_BURDENED_COST_TAB => l_cal_etc_burdened_cost_tab,
P_ADDL_BURDENED_COST_TAB => l_addl_burdened_cost_tab,
P_TOTAL_REVENUE_TAB => l_cal_etc_revenue_tab,
P_ADDL_REVENUE_TAB => l_addl_revenue_tab,
P_RAW_COST_RATE_TAB => l_raw_cost_rate_tab,
P_RW_COST_RATE_OVERRIDE_TAB => l_cal_rcost_rate_override_tab,
P_B_COST_RATE_TAB => l_b_cost_rate_tab,
P_B_COST_RATE_OVERRIDE_TAB => l_cal_bcost_rate_override_tab,
P_BILL_RATE_TAB => l_bill_rate_tab,
P_BILL_RATE_OVERRIDE_TAB => l_cal_bill_rate_override_tab,
P_LINE_START_DATE_TAB => l_line_start_date_tab,
P_LINE_END_DATE_TAB => l_line_start_date_tab,
P_RATXN_ROLLUP_API_CALL_FLAG => l_raTxn_rollup_api_call_flag,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
DELETE FROM PA_FP_PLANNING_RES_TMP1;
source, all negative res asg values are inserted either for
tasks with etc source as WORK_QUANTITY or the etc is NONE or NULL. */
INSERT INTO PA_FP_PLANNING_RES_TMP1 (
TASK_ID,
RESOURCE_LIST_MEMBER_ID,
RESOURCE_ASSIGNMENT_ID,
planning_start_date,
planning_end_date )
(SELECT MAPPED_FIN_TASK_ID,
TARGET_RLM_ID,
to_number(NULL),
min(planning_start_date),
max(planning_end_date)
FROM PA_FP_CALC_AMT_TMP1
GROUP BY mapped_fin_task_id,TARGET_RLM_ID,to_number(NULL));
INSERT INTO PA_FP_PLANNING_RES_TMP1 (
TASK_ID,
RESOURCE_LIST_MEMBER_ID,
RESOURCE_ASSIGNMENT_ID,
planning_start_date,
planning_end_date )
(SELECT 0,
TARGET_RLM_ID,
to_number(NULL),
min(planning_start_date),
max(planning_end_date)
FROM PA_FP_CALC_AMT_TMP1
group by 0, TARGET_RLM_ID,
to_number(NULL) );
INSERT INTO PA_FP_PLANNING_RES_TMP1 (
TASK_ID,
RESOURCE_LIST_MEMBER_ID,
RESOURCE_ASSIGNMENT_ID,
planning_start_date,
planning_end_date )
(SELECT MAPPED_FIN_TASK_ID,
TARGET_RLM_ID,
to_number(NULL),
min(planning_start_date),
max(planning_end_date)
FROM PA_FP_CALC_AMT_TMP1 group by
mapped_fin_task_id,TARGET_RLM_ID,
to_number(NULL) );
P_MSG => 'Before calling pa_fp_copy_actuals_pub.update_res_asg',
P_MODULE_NAME => l_module_name,
p_log_level => 5);
PA_FP_COPY_ACTUALS_PUB.UPDATE_RES_ASG (
P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_FP_COLS_REC => P_FP_COLS_REC,
P_CALLING_PROCESS => 'FORECAST_GENERATION',
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
P_MSG => 'After calling update_res_asg,return status is: '||x_return_status,
P_MODULE_NAME => l_module_name,
p_log_level => 5);
* This procedure updates pa_budget_lines.other_rejection_code
* for the purpose of signalling ETC revenue amount calculation
* errors. See bug 5203622.
*
* Pre-Conditions:
* 1. At this point, other_rejection_code values should be stored
* in the txn_currency_code column of the pa_fp_calc_amt_tmp2
* table for planning txns with ETC revenue calculation errors.
*
* Note: The etc_currency_code column (not txn_currency_code)
* to store the currency for ETC records in pa_fp_calc_amt_tmp2.
*
* Also worth noting is that this procedure is package-private.
*/
PROCEDURE UPD_REV_CALCULATION_ERR
(P_PROJECT_ID IN PA_PROJECTS_ALL.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_ETC_START_DATE IN DATE,
P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
l_package_name VARCHAR2(30) := 'PA_FP_GEN_FCST_AMT_PUB';
UPDATE pa_budget_lines bl
SET bl.other_rejection_code =
( SELECT tmp2.txn_currency_code
FROM pa_fp_calc_amt_tmp2 tmp2
WHERE tmp2.transaction_source_code = 'ETC'
AND tmp2.txn_currency_code is not null
AND bl.resource_assignment_id = tmp2.target_res_asg_id
AND bl.txn_currency_code = tmp2.etc_currency_code )
WHERE bl.budget_version_id = p_budget_version_id
AND nvl(bl.quantity,0) <> nvl(bl.init_quantity,0) -- ETC lines only
AND EXISTS
( SELECT null
FROM pa_fp_calc_amt_tmp2 tmp2
WHERE tmp2.transaction_source_code = 'ETC'
AND tmp2.txn_currency_code is not null
AND bl.resource_assignment_id = tmp2.target_res_asg_id
AND bl.txn_currency_code = tmp2.etc_currency_code );