[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO PA_FP_CALC_AMT_TMP1
( RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,UNIT_OF_MEASURE
,TRACK_AS_LABOR_FLAG
,RESOURCE_ASSIGNMENT_TYPE
,PLANNING_START_DATE
,PLANNING_END_DATE
,RES_TYPE_CODE
,FC_RES_TYPE_CODE
,RESOURCE_CLASS_CODE
,ORGANIZATION_ID
,JOB_ID
,PERSON_ID
,EXPENDITURE_TYPE
,EXPENDITURE_CATEGORY
,REVENUE_CATEGORY_CODE
,EVENT_TYPE
,SUPPLIER_ID
,PROJECT_ROLE_ID
,PERSON_TYPE_CODE
,NON_LABOR_RESOURCE
,BOM_RESOURCE_ID
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,BILLABLE_PERCENT
,TRANSACTION_SOURCE_CODE
,MFC_COST_TYPE_ID
,PROCURE_RESOURCE_FLAG
,INCURRED_BY_RES_FLAG
,RATE_JOB_ID
,RATE_EXPENDITURE_TYPE
,TA_DISPLAY_FLAG
,RATE_BASED_FLAG
,USE_TASK_SCHEDULE_FLAG
,RATE_EXP_FUNC_CURR_CODE
,RATE_EXPENDITURE_ORG_ID
,INCUR_BY_RES_CLASS_CODE
,INCUR_BY_ROLE_ID
,RESOURCE_CLASS_FLAG
,NAMED_ROLE
,ETC_METHOD_CODE
,MAPPED_FIN_TASK_ID)
(SELECT ra.resource_assignment_id,
ra.budget_version_id,
ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.unit_of_measure,
ra.track_as_labor_flag,
ra.resource_assignment_type,
ra.planning_start_date,
ra.planning_end_date,
ra.res_type_code,
ra.fc_res_type_code,
ra.resource_class_code,
ra.organization_id,
ra.job_id,
ra.person_id,
ra.expenditure_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.event_type,
ra.supplier_id,
ra.project_role_id,
ra.person_type_code,
ra.non_labor_resource,
ra.bom_resource_id,
ra.inventory_item_id,
ra.item_category_id,
ra.billable_percent,
l_txn_src_code,
ra.mfc_cost_type_id,
ra.procure_resource_flag,
ra.incurred_by_res_flag,
ra.rate_job_id,
ra.rate_expenditure_type,
ra.ta_display_flag,
ra.rate_based_flag,
ra.use_task_schedule_flag,
ra.rate_exp_func_curr_code,
ra.rate_expenditure_org_id,
ra.incur_by_res_class_code,
ra.incur_by_role_id,
ra.resource_class_flag,
ra.named_role,
ra.etc_method_code,
ra.task_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = l_source_bv_id
AND NVL(ra.task_id,0) = p_task_id AND
EXISTS (SELECT 1 from pa_budget_lines bl WHERE
ra.resource_assignment_id =
bl.resource_assignment_id AND
rownum < 2));
/* hr_utility.trace('no fo recs inserted in tmp1:'||sql%rowcount);
INSERT INTO PA_FP_CALC_AMT_TMP2
(resource_assignment_id,
txn_currency_code,
total_plan_quantity,
total_txn_raw_cost,
total_txn_burdened_cost,
total_txn_revenue,
total_pc_raw_cost,
total_pc_burdened_cost,
total_pc_revenue,
total_pfc_raw_cost,
total_pfc_burdened_cost,
total_pfc_revenue,
transaction_source_code )
(SELECT ra.resource_assignment_id,
decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_fp_cols_rec.x_project_currency_code),
sum(bl.quantity),
sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_raw_cost,
'N',bl.project_raw_cost)),
sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_burdened_cost,
'N',bl.project_burdened_cost)),
sum(decode(p_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),
l_txn_src_code
FROM pa_budget_lines bl,
pa_resource_assignments ra
WHERE ra.resource_assignment_id = bl.resource_assignment_id
and ra.budget_version_id = l_source_bv_id
and NVL(ra.task_id,0) = p_task_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.resource_assignment_id,l_txn_src_code,
decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_fp_cols_rec.x_project_currency_code));
INSERT INTO PA_FP_CALC_AMT_TMP1
( RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,UNIT_OF_MEASURE
,TRACK_AS_LABOR_FLAG
,RESOURCE_ASSIGNMENT_TYPE
,PLANNING_START_DATE
,PLANNING_END_DATE
,RES_TYPE_CODE
,FC_RES_TYPE_CODE
,RESOURCE_CLASS_CODE
,ORGANIZATION_ID
,JOB_ID
,PERSON_ID
,EXPENDITURE_TYPE
,EXPENDITURE_CATEGORY
,REVENUE_CATEGORY_CODE
,EVENT_TYPE
,SUPPLIER_ID
,PROJECT_ROLE_ID
,PERSON_TYPE_CODE
,NON_LABOR_RESOURCE
,BOM_RESOURCE_ID
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,BILLABLE_PERCENT
,TRANSACTION_SOURCE_CODE
,MFC_COST_TYPE_ID
,PROCURE_RESOURCE_FLAG
,INCURRED_BY_RES_FLAG
,RATE_JOB_ID
,RATE_EXPENDITURE_TYPE
,TA_DISPLAY_FLAG
,RATE_BASED_FLAG
,USE_TASK_SCHEDULE_FLAG
,RATE_EXP_FUNC_CURR_CODE
,RATE_EXPENDITURE_ORG_ID
,INCUR_BY_RES_CLASS_CODE
,INCUR_BY_ROLE_ID
,RESOURCE_CLASS_FLAG
,NAMED_ROLE
,ETC_METHOD_CODE
,MAPPED_FIN_TASK_ID)
(SELECT ra.resource_assignment_id,
ra.budget_version_id,
ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.unit_of_measure,
ra.track_as_labor_flag,
ra.resource_assignment_type,
ra.planning_start_date,
ra.planning_end_date,
ra.res_type_code,
ra.fc_res_type_code,
ra.resource_class_code,
ra.organization_id,
ra.job_id,
ra.person_id,
ra.expenditure_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.event_type,
ra.supplier_id,
ra.project_role_id,
ra.person_type_code,
ra.non_labor_resource,
ra.bom_resource_id,
ra.inventory_item_id,
ra.item_category_id,
ra.billable_percent,
l_txn_src_code,
ra.mfc_cost_type_id,
ra.procure_resource_flag,
ra.incurred_by_res_flag,
ra.rate_job_id,
ra.rate_expenditure_type,
ra.ta_display_flag,
ra.rate_based_flag,
ra.use_task_schedule_flag,
ra.rate_exp_func_curr_code,
ra.rate_expenditure_org_id,
ra.incur_by_res_class_code,
ra.incur_by_role_id,
ra.resource_class_flag,
ra.named_role,
ra.etc_method_code,
0
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = l_source_bv_id);
INSERT INTO PA_FP_CALC_AMT_TMP2
(resource_assignment_id,
txn_currency_code,
total_plan_quantity,
total_txn_raw_cost,
total_txn_burdened_cost,
total_txn_revenue,
total_pc_raw_cost,
total_pc_burdened_cost,
total_pc_revenue,
total_pfc_raw_cost,
total_pfc_burdened_cost,
total_pfc_revenue,
transaction_source_code )
(SELECT ra.resource_assignment_id,
decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_fp_cols_rec.x_project_currency_code),
sum(bl.quantity),
sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_raw_cost,
'N',bl.project_raw_cost)),
sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_burdened_cost,
'N',bl.project_burdened_cost)),
sum(decode(p_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),
l_txn_src_code
FROM pa_budget_lines bl,
pa_resource_assignments ra
WHERE ra.budget_version_id = l_source_bv_id
AND ra.resource_assignment_id = bl.resource_assignment_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.resource_assignment_id,l_txn_src_code,
decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_fp_cols_rec.x_project_currency_code));
INSERT INTO PA_FP_CALC_AMT_TMP1
( RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,UNIT_OF_MEASURE
,TRACK_AS_LABOR_FLAG
,RESOURCE_ASSIGNMENT_TYPE
,PLANNING_START_DATE
,PLANNING_END_DATE
,RES_TYPE_CODE
,FC_RES_TYPE_CODE
,RESOURCE_CLASS_CODE
,ORGANIZATION_ID
,JOB_ID
,PERSON_ID
,EXPENDITURE_TYPE
,EXPENDITURE_CATEGORY
,REVENUE_CATEGORY_CODE
,EVENT_TYPE
,SUPPLIER_ID
,PROJECT_ROLE_ID
,PERSON_TYPE_CODE
,NON_LABOR_RESOURCE
,BOM_RESOURCE_ID
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,BILLABLE_PERCENT
,TRANSACTION_SOURCE_CODE
,MFC_COST_TYPE_ID
,PROCURE_RESOURCE_FLAG
,INCURRED_BY_RES_FLAG
,RATE_JOB_ID
,RATE_EXPENDITURE_TYPE
,TA_DISPLAY_FLAG
,RATE_BASED_FLAG
,USE_TASK_SCHEDULE_FLAG
,RATE_EXP_FUNC_CURR_CODE
,RATE_EXPENDITURE_ORG_ID
,INCUR_BY_RES_CLASS_CODE
,INCUR_BY_ROLE_ID
,RESOURCE_CLASS_FLAG
,NAMED_ROLE
,ETC_METHOD_CODE
,MAPPED_FIN_TASK_ID)
(SELECT ra.resource_assignment_id,
ra.budget_version_id,
ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.unit_of_measure,
ra.track_as_labor_flag,
ra.resource_assignment_type,
ra.planning_start_date,
ra.planning_end_date,
ra.res_type_code,
ra.fc_res_type_code,
ra.resource_class_code,
ra.organization_id,
ra.job_id,
ra.person_id,
ra.expenditure_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.event_type,
ra.supplier_id,
ra.project_role_id,
ra.person_type_code,
ra.non_labor_resource,
ra.bom_resource_id,
ra.inventory_item_id,
ra.item_category_id,
ra.billable_percent,
l_txn_src_code,
ra.mfc_cost_type_id,
ra.procure_resource_flag,
ra.incurred_by_res_flag,
ra.rate_job_id,
ra.rate_expenditure_type,
ra.ta_display_flag,
ra.rate_based_flag,
ra.use_task_schedule_flag,
ra.rate_exp_func_curr_code,
ra.rate_expenditure_org_id,
ra.incur_by_res_class_code,
ra.incur_by_role_id,
ra.resource_class_flag,
ra.named_role,
ra.etc_method_code,
v.mapped_fin_task_id
FROM pa_resource_assignments ra,
pa_map_wp_to_fin_tasks_v v
WHERE
ra.budget_version_id = l_source_bv_id
AND v.mapped_fin_task_id = p_task_id
AND v.parent_structure_version_id =
p_fp_cols_rec_etc_wp.x_project_structure_version_id
AND v.proj_element_id = ra.task_id);
INSERT INTO PA_FP_CALC_AMT_TMP2
(resource_assignment_id,
txn_currency_code,
total_plan_quantity,
total_txn_raw_cost,
total_txn_burdened_cost,
total_txn_revenue,
total_pc_raw_cost,
total_pc_burdened_cost,
total_pc_revenue,
total_pfc_raw_cost,
total_pfc_burdened_cost,
total_pfc_revenue,
transaction_source_code )
(SELECT ra.resource_assignment_id,
decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_fp_cols_rec.x_project_currency_code),
sum(bl.quantity),
sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_raw_cost,
'N',bl.project_raw_cost)),
sum(decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_burdened_cost,
'N',bl.project_burdened_cost)),
sum(decode(p_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),
l_txn_src_code
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_map_wp_to_fin_tasks_v v
WHERE ra.resource_assignment_id = bl.resource_assignment_id
and ra.budget_version_id = l_source_bv_id
and v.parent_structure_version_id = p_fp_cols_rec_etc_wp.x_project_structure_version_id
and v.mapped_fin_task_id = p_task_id
and NVL(ra.task_id,0) = v.proj_element_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.resource_assignment_id,l_txn_src_code,
decode(p_fp_cols_rec.x_plan_in_multi_curr_flag,
'Y',bl.txn_currency_code,
'N',p_fp_cols_rec.x_project_currency_code));
INSERT INTO PA_FP_CALC_AMT_TMP1
( RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,UNIT_OF_MEASURE
,TRACK_AS_LABOR_FLAG
,RESOURCE_ASSIGNMENT_TYPE
,PLANNING_START_DATE
,PLANNING_END_DATE
,RES_TYPE_CODE
,FC_RES_TYPE_CODE
,RESOURCE_CLASS_CODE
,ORGANIZATION_ID
,JOB_ID
,PERSON_ID
,EXPENDITURE_TYPE
,EXPENDITURE_CATEGORY
,REVENUE_CATEGORY_CODE
,EVENT_TYPE
,SUPPLIER_ID
,PROJECT_ROLE_ID
,PERSON_TYPE_CODE
,NON_LABOR_RESOURCE
,BOM_RESOURCE_ID
,INVENTORY_ITEM_ID
,ITEM_CATEGORY_ID
,BILLABLE_PERCENT
,TRANSACTION_SOURCE_CODE
,MFC_COST_TYPE_ID
,PROCURE_RESOURCE_FLAG
,INCURRED_BY_RES_FLAG
,RATE_JOB_ID
,RATE_EXPENDITURE_TYPE
,TA_DISPLAY_FLAG
,RATE_BASED_FLAG
,USE_TASK_SCHEDULE_FLAG
,RATE_EXP_FUNC_CURR_CODE
,RATE_EXPENDITURE_ORG_ID
,INCUR_BY_RES_CLASS_CODE
,INCUR_BY_ROLE_ID
,RESOURCE_CLASS_FLAG
,NAMED_ROLE
,ETC_METHOD_CODE
,MAPPED_FIN_TASK_ID)
(SELECT ra.resource_assignment_id,
ra.budget_version_id,
ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.unit_of_measure,
ra.track_as_labor_flag,
ra.resource_assignment_type,
ra.planning_start_date,
ra.planning_end_date,
ra.res_type_code,
ra.fc_res_type_code,
ra.resource_class_code,
ra.organization_id,
ra.job_id,
ra.person_id,
ra.expenditure_type,
ra.expenditure_category,
ra.revenue_category_code,
ra.event_type,
ra.supplier_id,
ra.project_role_id,
ra.person_type_code,
ra.non_labor_resource,
ra.bom_resource_id,
ra.inventory_item_id,
ra.item_category_id,
ra.billable_percent,
l_txn_src_code,
ra.mfc_cost_type_id,
ra.procure_resource_flag,
ra.incurred_by_res_flag,
ra.rate_job_id,
ra.rate_expenditure_type,
ra.ta_display_flag,
ra.rate_based_flag,
ra.use_task_schedule_flag,
ra.rate_exp_func_curr_code,
ra.rate_expenditure_org_id,
ra.incur_by_res_class_code,
ra.incur_by_role_id,
ra.resource_class_flag,
ra.named_role,
ra.etc_method_code,
v.mapped_fin_task_id
FROM pa_resource_assignments ra,
pa_map_wp_to_fin_tasks_v v
WHERE
ra.budget_version_id = l_source_bv_id
AND v.mapped_fin_task_id = p_task_id
AND v.parent_structure_version_id =
p_fp_cols_rec_etc_wp.x_project_structure_version_id
AND v.proj_element_id = ra.task_id);
SELECT resource_assignment_id,
planning_start_date,
planning_end_date,
resource_list_member_id
BULK COLLECT
INTO l_res_asg_id_tab,
l_start_date_tab,
l_end_date_tab,
l_res_list_member_id_tab /* Bug 4070849 */
FROM pa_fp_calc_amt_tmp1
WHERE NVL(mapped_fin_task_id,0) = p_task_id;
SELECT o.projfunc_cost_rate_type
,o.projfunc_cost_rate_date
,o.projfunc_rev_rate_type
,o.projfunc_rev_rate_date
,o.project_cost_rate_type
,o.project_cost_rate_date
,o.project_rev_rate_type
,o.project_rev_rate_date
BULK COLLECT
INTO l_projfunc_cost_rate_type_tab,
l_projfunc_cost_rate_date_tab,
l_projfunc_rev_rate_type_tab,
l_projfunc_rev_rate_date_tab,
l_proj_cost_rate_type_tab,
l_proj_cost_rate_date_tab,
l_proj_rev_rate_type_tab,
l_proj_rev_rate_date_tab
FROM pa_proj_fp_options o
WHERE o.fin_plan_version_id = p_budget_version_id;
SELECT project_currency_code
,projfunc_currency_code
BULK COLLECT
INTO l_proj_currency_code_tab,
l_projfunc_currency_code_tab
FROM pa_projects_all
WHERE project_id = p_project_id;
l_res_asn_id_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_txn_currency_code_tab.delete;
l_txn_rw_cost_tab.delete;
l_txn_burdend_cost_tab.delete;
l_txn_rev_tab.delete;
l_projfunc_currency_code_tab.delete;
l_projfunc_cost_rate_type_tab.delete;
l_projfunc_cost_rate_tab.delete;
l_projfunc_cost_rate_date_tab.delete;
l_projfunc_rev_rate_type_tab.delete;
l_projfunc_rev_rate_tab.delete;
l_projfunc_rev_rate_date_tab.delete;
l_projfunc_raw_cost_tab.delete;
l_projfunc_burdened_cost_tab.delete;
l_projfunc_revenue_tab.delete;
l_projfunc_rejection_tab.delete;
l_proj_raw_cost_tab.delete;
l_proj_burdened_cost_tab.delete;
l_proj_revenue_tab.delete;
l_proj_rejection_tab.delete;
l_proj_currency_code_tab.delete;
l_proj_cost_rate_type_tab.delete;
l_proj_cost_rate_tab.delete;
l_proj_cost_rate_date_tab.delete;
l_proj_rev_rate_type_tab.delete;
l_proj_rev_rate_tab.delete;
l_proj_rev_rate_date_tab.delete;
l_user_validate_flag_tab.delete;
/* bulk insert */
FORALL m IN 1..l_res_asn_id_tab.count
INSERT INTO pa_fp_calc_amt_tmp2
(resource_assignment_id,
total_pc_raw_cost,
total_pc_burdened_cost,
total_pc_revenue,
total_pfc_raw_cost,
total_pfc_burdened_cost,
total_pfc_revenue,
transaction_source_code )
VALUES
(l_res_asn_id_tab(m),
l_ins_pc_raw_cost_tab(m),
l_ins_pc_burdened_cost_tab(m),
l_ins_pc_revenue_tab(m),
l_ins_pfc_raw_cost_tab(m),
l_ins_pfc_burd_cost_tab(m),
l_ins_pfc_revenue_tab(m),
l_txn_src_code);
/* select not required as the amounts will be populated
in the tmp table.
SELECT total_plan_quantity,
total_txn_raw_cost,
total_txn_burdened_cost,
total_txn_revenue
INTO x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM pa_fp_calc_amt_tmp2;
PROCEDURE UPDATE_TOTAL_PLAN_AMTS
(P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_gen_fcst_amt_pub.update_total_plan_amts';
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
pa_debug.set_curr_function( p_function => 'UPDATE_TOTAL_PLAN_AMTS'
,p_debug_mode => p_pa_debug_mode);
UPDATE pa_budget_lines
SET raw_cost = nvl(raw_cost,0) + nvl(init_raw_cost,0),
burdened_cost = nvl(burdened_cost,0) + nvl(init_burdened_cost,0),
revenue = nvl(revenue,0) + nvl(init_revenue,0),
project_raw_cost = nvl(project_raw_cost,0) + nvl(project_init_raw_cost,0),
project_burdened_cost = nvl(project_burdened_cost,0) +
nvl(project_init_burdened_cost,0),
project_revenue = nvl(project_revenue,0) + nvl(project_init_revenue,0),
txn_raw_cost = nvl(txn_raw_cost,0) + nvl(txn_init_raw_cost,0),
txn_burdened_cost = nvl(txn_burdened_cost,0) +
nvl(txn_init_burdened_cost,0),
txn_revenue = nvl(txn_revenue,0) + nvl(txn_init_revenue,0),
quantity = nvl(quantity,0) + nvl(init_quantity,0),
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login
WHERE budget_version_id = p_budget_version_id
and (resource_assignment_id,txn_currency_code) in
(select target_res_asg_id,etc_currency_code
from PA_FP_CALC_AMT_TMP2
where transaction_source_code = 'ETC');
,p_procedure_name => 'UPDATE_TOTAL_PLAN_AMTS');
END UPDATE_TOTAL_PLAN_AMTS;
SELECT NVL(rate_based_flag,'N') INTO l_rate_based_flag
FROM pa_resource_assignments
WHERE resource_assignment_id = P_RES_ASG_ID;
SELECT NVL(rate_based_flag,'N') INTO l_rate_based_flag
FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND resource_list_member_id = p_res_list_member_id
AND NVL(task_id, 0) = 0;
SELECT count(*),
SUM (DECODE(l_currency_flag,
'TC', NVL(init_quantity,0),
'PC', DECODE(l_rate_based_flag,
'Y', NVL(init_quantity,0),
decode(P_FP_COLS_REC.x_version_type,
'REVENUE',nvl(project_init_revenue,0),
NVL(project_init_raw_cost,0)))
)),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_init_raw_cost,0),
'PC', NVL(project_init_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_init_burdened_cost,0),
'PC', NVL(project_init_burdened_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_init_revenue,0),
'PC', NVL(project_init_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM pa_budget_lines
WHERE resource_assignment_id = P_RES_ASG_ID
AND start_date >= p_actual_from_date
AND start_date <= p_actual_to_date;
SELECT COUNT(DISTINCT period_name) INTO x_txn_amt_rec.no_of_periods
FROM pa_budget_lines
WHERE resource_assignment_id = P_RES_ASG_ID
AND start_date >= p_actual_from_date
AND start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_raw_cost,0),
'PC', NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_periods_all pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.org_id = p_fp_cols_rec.x_org_id
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_raw_cost,0),
'PC', NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_periods_all pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.org_id = p_fp_cols_rec.x_org_id
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_revenue,0),
'TC', NVL(txn_revenue,0))
)),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_raw_cost,0),
'PC', NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_periods_all pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.org_id = p_fp_cols_rec.x_org_id
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_periods_all pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.org_id = p_fp_cols_rec.x_org_id
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_periods_all pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.org_id = p_fp_cols_rec.x_org_id
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
FROM PA_FP_FCST_GEN_TMP1 tmp,
pa_periods_all pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.org_id = p_fp_cols_rec.x_org_id
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_raw_cost,0),
'PC', NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1 tmp,
gl_period_statuses pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND pd.adjustment_period_flag = 'N'
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_raw_cost,0),
'PC', NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1 tmp,
gl_period_statuses pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND pd.adjustment_period_flag = 'N'
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_revenue,0),
'TC', NVL(txn_revenue,0))
)),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_raw_cost,0),
'PC', NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1 tmp,
gl_period_statuses pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND pd.adjustment_period_flag = 'N'
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
FROM PA_FP_FCST_GEN_TMP1 tmp,
gl_period_statuses pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND pd.adjustment_period_flag = 'N'
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
FROM PA_FP_FCST_GEN_TMP1 tmp,
gl_period_statuses pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_raw_cost,0) <> 0
OR NVL(tmp.txn_brdn_cost,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND pd.adjustment_period_flag = 'N'
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
SELECT /*+ INDEX(tmp,PA_FP_FCST_GEN_TMP1_N1)*/
COUNT(DISTINCT pd.period_name) INTO x_txn_amt_rec.no_of_periods
FROM PA_FP_FCST_GEN_TMP1 tmp,
gl_period_statuses pd
WHERE tmp.data_type_code = 'TARGET_FP'
AND tmp.project_element_id = P_TASK_ID
AND tmp.res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(tmp.quantity,0) <> 0
OR NVL(tmp.txn_revenue,0) <> 0)
AND pd.period_name = tmp.period_name
AND pd.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND pd.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND pd.adjustment_period_flag = 'N'
AND pd.start_date >= p_actual_from_date
AND pd.start_date <= p_actual_to_date;
use the following SELECT to get the period count. But, if the
currency flag is TC, we can avoid this SELECT as we can get the
count from the above sql. */
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_txn_amt_rec.quantity_sum := 0;
SELECT count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(init_quantity,0),
DECODE(l_currency_flag,
'PC', NVL(project_init_raw_cost,0),
'TC', NVL(txn_init_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC',NVL(txn_init_raw_cost,0),
'PC',NVL(project_init_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_init_burdened_cost,0),
'PC', NVL(project_init_burdened_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_init_revenue,0),
'PC', NVL(project_init_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM pa_budget_lines
WHERE resource_assignment_id = P_RES_ASG_ID;
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC',NVL(txn_raw_cost,0),
'PC',NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1
WHERE data_type_code = 'TARGET_FP'
AND project_element_id = P_TASK_ID
AND res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(quantity,0) <> 0
OR NVL(txn_raw_cost,0) <> 0
OR NVL(txn_brdn_cost,0) <> 0
OR NVL(txn_revenue,0) <> 0);
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC',NVL(txn_raw_cost,0),
'PC',NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1
WHERE data_type_code = 'TARGET_FP'
AND project_element_id = P_TASK_ID
AND res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(quantity,0) <> 0
OR NVL(txn_raw_cost,0) <> 0
OR NVL(txn_brdn_cost,0) <> 0);
SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
count(*),
SUM (DECODE(l_rate_based_flag, 'Y',
NVL(quantity,0),
DECODE(l_currency_flag,
'PC', NVL(prj_raw_cost,0),
'TC', NVL(txn_raw_cost,0))
)),
SUM (DECODE(l_currency_flag,
'TC',NVL(txn_raw_cost,0),
'PC',NVL(prj_raw_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_brdn_cost,0),
'PC', NVL(prj_brdn_cost,0))),
SUM (DECODE(l_currency_flag,
'TC', NVL(txn_revenue,0),
'PC', NVL(prj_revenue,0)))
INTO x_txn_amt_rec.no_of_periods,
x_txn_amt_rec.quantity_sum,
x_txn_amt_rec.txn_raw_cost_sum,
x_txn_amt_rec.txn_burdened_cost_sum,
x_txn_amt_rec.txn_revenue_sum
FROM PA_FP_FCST_GEN_TMP1
WHERE data_type_code = 'TARGET_FP'
AND project_element_id = P_TASK_ID
AND res_list_member_id = P_RES_LIST_MEMBER_ID
AND (NVL(quantity,0) <> 0
OR NVL(txn_revenue,0) <> 0);
SELECT start_date
FROM pa_periods_all
WHERE period_name = c_period
AND org_id = p_fp_cols_rec.x_org_id;
SELECT start_date
FROM gl_period_statuses
WHERE period_name = c_period
AND application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N';
SELECT ra.resource_assignment_id,
ra.rate_based_flag,
NVL(ta.billable_flag,'Y') /* Added for ER 4376722 */
INTO l_res_asg_id,
l_rate_based_flag,
l_billable_flag /* Added for ER 4376722 */
FROM pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE ra.budget_version_id = p_budget_version_id
AND NVL(ra.task_id,0) = p_task_id
AND ra.resource_list_member_id = p_res_list_member_id
AND NVL(ra.task_id,0) = ta.task_id (+); /* Added for ER 4376722 */
SELECT ra.rate_based_flag,
NVL(ta.billable_flag,'Y') /* Added for ER 4376722 */
INTO l_rate_based_flag,
l_billable_flag /* Added for ER 4376722 */
FROM pa_resource_assignments ra,
pa_tasks ta /* Added for ER 4376722 */
WHERE ra.resource_assignment_id = l_res_asg_id
AND NVL(ra.task_id,0) = ta.task_id (+); /* Added for ER 4376722 */
SELECT period_name, start_date, end_date
BULK COLLECT
INTO l_period_name_tab, l_start_date_tab, l_end_date_tab
FROM pa_periods_all
WHERE org_id = p_fp_cols_rec.x_org_id
AND start_date >= l_etc_from_date
AND start_date <= least(p_planning_end_date,l_etc_to_date);
SELECT period_name, start_date, end_date
BULK COLLECT
INTO l_period_name_tab, l_start_date_tab, l_end_date_tab
FROM gl_period_statuses
WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N'
AND start_date >= l_etc_from_date
AND start_date <= least(p_planning_end_date,l_etc_to_date);
* based flag of 'N', target res asg will be updated to
* non rate based. And for this target res asg, all existing
* budget lines will be updated accordingly. **/
PROCEDURE UPD_TGT_RATE_BASED_FLAG
(P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PVT.upd_tgt_rate_based_flag';
SELECT /*+ LEADING(tmp) */
DISTINCT target_res_asg_id
BULK COLLECT
INTO l_tgt_res_asg_tab
FROM pa_fp_calc_amt_tmp1 tmp, pa_resource_assignments ra
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND ra.rate_based_flag = 'Y'
AND tmp.rate_based_flag = 'N';
SELECT /*+ LEADING(tmp) */
DISTINCT target_res_asg_id
BULK COLLECT
INTO l_tgt_res_asg_tab
FROM pa_fp_calc_amt_tmp1 tmp, pa_resource_assignments ra
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND ra.rate_based_flag = 'Y'
AND tmp.rate_based_flag = 'N'
AND ( ra.transaction_source_code is not null
OR
(ra.transaction_source_code is null and NOT exists
(select 1
from pa_budget_lines pbl
where pbl.resource_assignment_id = ra.resource_assignment_id
and pbl.start_date >= l_etc_start_date
)
)
);
SELECT /*+ LEADING(tmp) */
DISTINCT target_res_asg_id
BULK COLLECT
INTO l_tgt_res_asg_tab
FROM pa_fp_calc_amt_tmp1 tmp, pa_resource_assignments ra
WHERE tmp.target_res_asg_id = ra.resource_assignment_id
AND ra.rate_based_flag = 'Y'
AND tmp.rate_based_flag = 'N'
AND ( ra.transaction_source_code is not null
OR
(ra.transaction_source_code is null and NOT exists
(select 1
from pa_budget_lines pbl
where pbl.resource_assignment_id = ra.resource_assignment_id
and NVL(pbl.quantity,0) <> NVL(pbl.init_quantity,0)
)
)
);
UPDATE pa_resource_assignments
SET rate_based_flag = 'N',
unit_of_measure = 'DOLLARS'
WHERE resource_assignment_id = l_tgt_res_asg_tab(i);
DELETE FROM pa_res_list_map_tmp1;
INSERT INTO pa_res_list_map_tmp1(txn_resource_assignment_id)
VALUES (l_tgt_res_asg_tab(i));
SELECT bl.budget_line_id,
bl.txn_init_raw_cost,
bl.txn_raw_cost,
bl.txn_init_revenue,
bl.txn_revenue
BULK COLLECT
INTO l_budget_line_id_tab,
l_init_raw_cost_tab,
l_raw_cost_tab,
l_init_rev_tab,
l_rev_tab
FROM pa_budget_lines bl, pa_res_list_map_tmp1 tmp
WHERE bl.budget_version_id = l_bv_id AND
tmp.txn_resource_assignment_id = bl.resource_assignment_id;
UPDATE pa_budget_lines
SET init_quantity = l_init_rev_tab(i),
quantity = l_rev_tab(i)
WHERE budget_line_id = l_budget_line_id_tab(i);
UPDATE pa_budget_lines
SET init_quantity = l_init_raw_cost_tab(i),
quantity = l_raw_cost_tab(i)
WHERE budget_line_id = l_budget_line_id_tab(i);