[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Execute_Update_Task_Asgmts
( p_api_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pa_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_UPDATE_TASK_ASGMTS';
L_FuncProc := 'Execute_Update_Task_Asgmts';
SAVEPOINT EXECUTE_UPDATE_TASK_ASGMTS;
UPDATE_Task_Assignments
( p_api_version_number => 1.0
,p_commit => FND_API.G_FALSE
,p_init_msg_list => FND_API.G_FALSE
,p_pm_product_code => p_pm_product_code
,p_pm_project_reference => p_pm_project_reference
,p_pa_project_id => p_pa_project_id
,p_pa_structure_version_id => p_pa_structure_version_id
,p_task_assignments_in => g_task_asgmts_in_tbl
,p_task_assignments_out => g_task_asgmts_out_tbl
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
ROLLBACK TO EXECUTE_UPDATE_TASK_ASGMTS;
ROLLBACK TO EXECUTE_UPDATE_TASK_ASGMTS;
ROLLBACK TO EXECUTE_UPDATE_TASK_ASGMTS;
END EXECUTE_UPDATE_TASK_ASGMTS;
l_delete_task_res_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
l_delete_task_elem_ver_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
select budget_version_id
from pa_budget_versions
where project_structure_version_id = p_structure_version_id
and project_id = p_pa_project_id;
SELECT pe.element_version_id
from pa_proj_element_versions pe
where parent_structure_version_id = p_structure_version_id
and pe.proj_element_id = p_task_id
and pe.project_id = p_project_id;
SELECT pe.element_version_id, pe.proj_element_id
from pa_proj_element_versions pe
where pe.element_version_id = p_task_element_version_id;
Select a.alias, b.resource_assignment_id
from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
where a.resource_list_member_id = b.resource_list_member_id
and b.resource_list_member_id = p_resource_list_member_id
and b.ta_display_flag = 'Y'
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id;
Select a.alias, b.resource_assignment_id
from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
where a.resource_list_member_id = b.resource_list_member_id
and b.resource_list_member_id = p_resource_list_member_id
and b.ta_display_flag = 'Y'
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id
and b.cbs_element_id = p_cbs_element_id;
SELECT 'X', resource_class_code
from pa_resource_list_members
where resource_list_member_id = p_resource_list_member_id;
select 'X'
from pa_resource_assignments a --, pa_budget_versions b
where a.pm_res_assignment_reference = p_res_assignment_reference
and a.pm_product_code = p_pm_product_code
and a.project_id = p_pa_project_id
and a.ta_display_flag is not null
and a.budget_version_id = c_budget_version_id;
Select 'X'
from pa_resource_assignments b, pa_budget_versions bv
where b.resource_list_member_id = p_resource_list_member_id
and b.ta_display_flag is not null
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id;
Select 'X'
from pa_resource_assignments b, pa_budget_versions bv
where b.resource_list_member_id = p_resource_list_member_id
and b.ta_display_flag is not null
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id
and b.cbs_element_id = p_cbs_element_id;
Select b.budget_version_id
from pa_resource_assignments b, pa_budget_versions bv
where b.ta_display_flag = 'N'
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id
and b.resource_class_code='PEOPLE'
and b.pm_product_code is not NULL;
select TRACK_WORKPLAN_COSTS_FLAG enabled_flag from pa_proj_fp_options
where fin_plan_version_id = p_budget_version_id;
SELECT uncategorized_flag
FROM pa_resource_lists
WHERE resource_list_id = c_resource_list_id;
SELECT resource_assignment_id, wbs_element_version_id
FROM pa_resource_assignments ra, pa_copy_asgmts_temp cat
WHERE ra.wbs_element_version_id = cat.src_elem_ver_id
AND ra.project_id = p_project_id
AND ra.budget_version_id = p_budget_version_id
AND ra.ta_display_flag = 'N';
pa_security.allow_update(x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
--dbms_output.put_line('pa_security.allow_query or update not allowed..');
delete from pa_resource_assignments where
resource_class_code = 'PEOPLE'
and project_id = l_project_id
and budget_version_id = c_people_res_rec.budget_version_id
and wbs_element_version_id = lk_task_elem_version_id_tbl(i)
and ta_display_flag = 'N';
/* Updates from the bug
In both pa_task_assignments_pub create_task_assignments and
update_task_assignments AMG APIs, we are deleting the ta_display_flag='N'
assignment records whenever the first resource is assigned to the task
version. There are a few problems that need to be fixed:
1. We currently only delete if the resource being assigned to task is of
'PEOPLE' class. This IF should be removed, deletion should be done when
resource of ANY class is assigned to task.
2. We should NOT be deleting directly from pa_resource_assignments. Instead,
we should call pa_fp_planning_transaction_pub.delete_planning_transactions to
to properly delete the assignment record.
*/
--Changes for Bug 3910882 Begin
/* Updates from the Bug
The UPDATE does not perform very well in volume env. My suggestion to improve
this is to replace the FORALL UPDATE by 2 operations:
* 1. Insert the task version ids into an existing temp table, PA_COPY_ASGMTS_TEMP
* 2. Select resource assignments from pa_resource_assignments joining to the temp table
*/
-- dynamically computing the statistics for the Temporary table
l_num_of_tasks := l_task_elem_version_id_tbl.COUNT;
SELECT to_number(value)
INTO l_db_block_size
FROM v$parameter
WHERE name = 'db_block_size';
DELETE pa_copy_asgmts_temp;
INSERT INTO pa_copy_asgmts_temp VALUES
(l_task_elem_version_id_tbl(i), -1, null, null, null, null);
pa_debug.g_err_stage:='Successfully inserted task version ids into the temp table';
l_delete_task_res_asgmt_id_tbl, l_delete_task_elem_ver_id_tbl;
pa_debug.g_err_stage:='Selected resource_assignment_ids and wbs_element_version_ids into plsql tables';
UPDATE pa_resource_assignments
SET ta_display_flag = 'N'
WHERE ta_display_flag = 'N'
AND wbs_element_version_id = l_task_elem_version_id_tbl(k)
AND project_id = l_project_id
AND budget_version_id = l_budget_version_id
RETURNING resource_assignment_id, wbs_element_version_id BULK COLLECT INTO
l_delete_task_res_asgmt_id_tbl, l_delete_task_elem_ver_id_tbl;
IF l_delete_task_res_asgmt_id_tbl.COUNT > 0 THEN
--dbms_output.put_line('Before calling delete planning transactions:ret. status' || x_return_status);
PA_FP_PLANNING_TRANSACTION_PUB.DELETE_PLANNING_TRANSACTIONS(
P_CONTEXT => pa_fp_constants_pkg.g_calling_module_task,
P_TASK_OR_RES => 'ASSIGNMENT',
P_ELEMENT_VERSION_ID_TBL => l_delete_task_elem_ver_id_tbl,
P_TASK_NUMBER_TBL => NULL,
P_TASK_NAME_TBL => NULL,
P_RESOURCE_ASSIGNMENT_TBL => l_delete_task_res_asgmt_id_tbl,
P_VALIDATE_DELETE_FLAG => NULL,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
select budget_version_id
from pa_budget_versions
where project_structure_version_id = p_structure_version_id
and project_id = p_pa_project_id;
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (p_plan_version_id => l_budget_version_id, -- added for bug 5469303
x_msg_code => l_msg_code,
x_return_status => l_return_status);
PROCEDURE Update_Task_Assignment_Periods
( p_api_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pa_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_task_assignments_in IN ASSIGNMENT_IN_TBL_TYPE
,p_task_assignment_periods_in IN PA_TASK_ASSIGNMENTS_PUB.ASSIGNMENT_PERIODS_TBL_TYPE
,p_task_assignments_out OUT NOCOPY ASSIGNMENT_OUT_TBL_TYPE
,p_task_assignment_periods_out OUT NOCOPY PA_TASK_ASSIGNMENTS_PUB.ASSIGNMENT_OUT_TBL_TYPE
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Task_Asgmt_Periods';
select budget_version_id
from pa_budget_versions
where project_structure_version_id = p_structure_version_id
and project_id = p_pa_project_id;
L_FuncProc := 'Update_Task_Asgmt_Periods';
SAVEPOINT UPDATE_TASK_ASGMT_PERIODS;
Update_Task_Assignments
( p_api_version_number => 1.0
,p_commit => FND_API.G_FALSE
,p_init_msg_list => FND_API.G_FALSE
,p_pm_product_code => p_pm_product_code
,p_pm_project_reference => p_pm_project_reference
,p_pa_project_id => p_pa_project_id
,p_pa_structure_version_id => p_pa_structure_version_id
,p_task_assignments_in => p_task_assignments_in
,p_task_assignments_out => p_task_assignments_out
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (p_plan_version_id => l_budget_version_id, -- added for bug 5469303
x_msg_code => l_msg_code,
x_return_status => l_return_status);
ROLLBACK TO UPDATE_TASK_ASGMT_PERIODS;
ROLLBACK TO UPDATE_TASK_ASGMT_PERIODS;
ROLLBACK TO UPDATE_TASK_ASGMT_PERIODS;
END Update_Task_Assignment_Periods;
PROCEDURE Delete_Task_Assignments
( p_api_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
--Either project reference or project id is required
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
--Structure version id is required
,p_pa_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
--Pass in list of task assignment id's or references as information at a minimum
,p_task_assignments_in IN ASSIGNMENT_IN_TBL_TYPE
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_pm_product_code VARCHAR2(30) := 'INTERNAL';
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Task_Assignments';
L_FuncProc := 'Delete_Task_Assignments';
SAVEPOINT Delete_Task_Assignments;
Update_Task_Assignments
( p_api_version_number => 1.0
,p_commit => FND_API.G_FALSE
,p_init_msg_list => FND_API.G_FALSE
,p_pm_product_code => l_pm_product_code
,p_pm_project_reference => p_pm_project_reference
,p_pa_project_id => p_pa_project_id
,p_pa_structure_version_id => p_pa_structure_version_id
,p_task_assignments_in => l_task_assignments_in
,p_task_assignments_out => l_task_assignments_out
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
ROLLBACK TO Delete_Task_Assignments;
ROLLBACK TO Delete_Task_Assignments;
ROLLBACK TO Delete_Task_Assignments;
END Delete_Task_Assignments;
PROCEDURE Update_Task_Assignments
( p_api_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pa_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_task_assignments_in IN ASSIGNMENT_IN_TBL_TYPE
,p_task_assignments_out OUT NOCOPY ASSIGNMENT_OUT_TBL_TYPE
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_rlm_id NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'update_task_assignments';
l_delete_task_res_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
l_delete_task_elem_ver_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
select project_assignment_id
from pa_resource_assignments
where resource_assignment_id = p_resource_assignment_id;
SELECT uncategorized_flag
FROM pa_resource_lists
WHERE resource_list_id = c_resource_list_id;
select budget_version_id
from pa_budget_versions
where project_structure_version_id = p_structure_version_id
and project_id = p_pa_project_id;
SELECT pe.element_version_id
from pa_proj_element_versions pe
where parent_structure_version_id = p_structure_version_id
and pe.proj_element_id = p_task_id
and pe.project_id = p_project_id;
SELECT pe.element_version_id, pe.proj_element_id
from pa_proj_element_versions pe
where pe.element_version_id = p_task_element_version_id;
Select a.alias, b.resource_assignment_id
from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
where a.resource_list_member_id = b.resource_list_member_id
and b.resource_list_member_id = p_resource_list_member_id
and b.ta_display_flag = 'Y'
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id;
Select a.alias, b.resource_assignment_id
from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
where a.resource_list_member_id = b.resource_list_member_id
and b.resource_list_member_id = p_resource_list_member_id
and b.ta_display_flag = 'Y'
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id
and b.cbs_element_id=p_cbs_element_id;
SELECT par.ALIAS, par.resource_class_code
from pa_resource_list_members par
where resource_list_member_id = p_resource_list_member_id;
select cost_type_id
from cst_cost_types_V
where multi_org_flag = 1
and cost_type = p_cost_type;
select 'X'
from cst_cost_types_V
where multi_org_flag = 1
and cost_type_id = p_cost_type_id;
select lookup_code --meaning
from pa_lookups
where lookup_type = 'UNIT'
and lookup_code = p_uom_code;
select lookup_code --meaning
from pa_lookups
where lookup_type = 'UNIT'
and meaning = p_uom;
select distinct TXN_CURRENCY_CODE
from PA_FP_TXN_CURRENCIES
where project_id = p_project_id and txn_currency_code = p_currency_code;
select distinct TXN_CURRENCY_CODE
from PA_FP_TXN_CURRENCIES
where --PROJECT_CURRENCY_FLAG = 'Y'
-- and
PROJFUNC_CURRENCY_FLAG = 'Y';
select task_id, wbs_element_version_id, resource_class_code, resource_assignment_id,
project_role_id, organization_id,
fc_res_type_code, named_role,res_type_code, planning_start_date, planning_end_date,
use_task_schedule_flag, rate_based_flag, supplier_id
from pa_resource_assignments
where resource_assignment_id = p_resource_assignment_id;
select a.resource_assignment_id, a.wbs_element_version_id
from pa_resource_assignments a, pa_budget_versions b
where a.budget_version_id = b.budget_version_id
and a.project_id = b.project_id
and a.ta_display_flag = 'Y'
and b.project_id = p_project_id
and b.project_structure_version_id = p_structure_version_id;
select 'X'
from pa_resource_assignments a --, pa_budget_versions b
where a.pm_res_assignment_reference = p_res_assignment_reference
and a.pm_product_code = p_pm_product_code
and a.project_id = p_pa_project_id
and a.ta_display_flag is not null
and a.budget_version_id = c_budget_version_id;
Select b.budget_version_id
from pa_resource_assignments b, pa_budget_versions bv
where b.ta_display_flag = 'N'
and b.budget_version_id = bv.budget_version_id
and b.project_id = bv.project_id
and bv.project_structure_version_id = p_structure_version_id
and b.project_id = p_project_id
and b.wbs_element_version_id = p_wbs_version_id
and b.resource_class_code='PEOPLE'
and b.pm_product_code is not NULL;
select TRACK_WORKPLAN_COSTS_FLAG enabled_flag from pa_proj_fp_options
where fin_plan_version_id = p_budget_version_id;
SELECT resource_assignment_id, wbs_element_version_id
FROM pa_resource_assignments ra, pa_copy_asgmts_temp cat
WHERE ra.wbs_element_version_id = cat.src_elem_ver_id
AND ra.project_id = p_project_id
AND ra.budget_version_id = p_budget_version_id
AND ra.ta_display_flag = 'N';
select a.spread_curve_id spread_curve_id
from pa_spread_curves_b a,
pa_spread_curves_tl t
where t.name = p_spread_curve_name
and a.spread_curve_id = t.spread_curve_id
and t.language = userenv('LANG')
and sysdate BETWEEN a.effective_start_date AND nvl(a.effective_end_date,
sysdate);
select 'X'
from pa_spread_curves_b
where spread_curve_id = p_spread_curve_id
and sysdate BETWEEN effective_start_date AND nvl(effective_end_date,
sysdate);
select spread_curve_code
from pa_spread_curves_b
where spread_curve_id = p_spread_curve_id
and sysdate BETWEEN effective_start_date AND nvl(effective_end_date,
sysdate);
select pa_fin_plan_utils.Get_Time_Phased_code(p_budget_version_id)
time_phased_code
from dual;
select spread_curve_id
from pa_resource_assignments
where resource_assignment_id = p_task_assignment_id ;
select resource_assignment_id
from pa_resource_assignments
where project_id = p_project_id AND
pm_res_assignment_reference = p_task_asgmt_reference AND
wbs_element_version_id = p_task_element_version_id AND
resource_list_member_id = p_resource_list_member_id;
L_FuncProc := 'Update_Task_Asgmts';
SAVEPOINT UPDATE_task_asgmts_pub;
l_module_name := 'PA_PM_UPDATE_TASK_ASSIGNMENT';
p_function_name => 'PA_PM_UPDATE_TASK_ASSIGNMENT',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed);
pa_security.allow_update(x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
pa_debug.g_err_stage:='Update Task Asgmts.: Structure element version for locking.: '|| l_struct_elem_version_id ;
pa_debug.g_err_stage:='Start of Loading internal Update/Add/Delete Tables i index is:' || i;
IF p_task_assignments_in(i).p_context <> 'D' THEN --Validations of Not Delete Context..(Update or Add)
IF p_task_assignments_in(i).resource_list_member_id IS NOT NULL AND
p_task_assignments_in(i).resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
C_Res_List_Mem_Check_Rec := NULL;
END IF; -- IF p_task_assignments_in(i).p_context <> 'D' THEN --Validations of Not Delete Context..
IF lp_resource_assignment_id_tbl(i) IS NOT NULL AND --Delete Content
lp_resource_assignment_id_tbl(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
p_task_assignments_in(i).p_context = 'D' THEN
OPEN C_Res_Asgmt_Data(lp_resource_assignment_id_tbl(i));
pa_debug.g_err_stage:='Entering Delete loading internal tables in Update api. Process';
ELSIF lp_resource_assignment_id_tbl(i) IS NOT NULL AND --Update Content
lp_resource_assignment_id_tbl(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
p_task_assignments_in(i).p_context <> 'D' THEN
OPEN C_Res_Asgmt_Data(lp_resource_assignment_id_tbl(i));
pa_debug.g_err_stage:='Entering Update loading internal tables in Update api. Process';
pa_debug.g_err_stage:='Update Tables index is:' || u_index;
pa_debug.g_err_stage:='Setting update only parameters:';
pa_debug.g_err_stage:='End of Setting All Update Params: int. index:' || u_index || ' Overall Index:' || i;
pa_debug.g_err_stage:='Entering setting of internal tables for Add sequence in Update..';
/* Updates from the bug
In both pa_task_assignments_pub create_task_assignments and
update_task_assignments AMG APIs, we are deleting the ta_display_flag='N'
assignment records whenever the first resource is assigned to the task
version. There are a few problems that need to be fixed:
1. We currently only delete if the resource being assigned to task is of
'PEOPLE' class. This IF should be removed, deletion should be done when
resource of ANY class is assigned to task.
2. We should NOT be deleting directly from pa_resource_assignments. Instead,
we should call pa_fp_planning_transaction_pub.delete_planning_transactions to
to properly delete the assignment record.
*/
--Changes for Bug 3910882 Begin
/* Updates from the Bug
The UPDATE does not perform very well in volume env. My suggestion to improve
this is to replace the FORALL UPDATE by 2 operations:
* 1. Insert the task version ids into an existing temp table, PA_COPY_ASGMTS_TEMP
* 2. Select resource assignments from pa_resource_assignments joining to the temp table
*/
-- dynamically computing the statistics for the Temporary table
l_num_of_tasks := l_task_elem_version_id_tbl.COUNT;
SELECT to_number(value)
INTO l_db_block_size
FROM v$parameter
WHERE name = 'db_block_size';
DELETE pa_copy_asgmts_temp;
INSERT INTO pa_copy_asgmts_temp VALUES
(l_task_elem_version_id_tbl(i), -1, null, null, null, null);
pa_debug.g_err_stage:='Successfully inserted task version ids into the temp table';
l_delete_task_res_asgmt_id_tbl, l_delete_task_elem_ver_id_tbl;
pa_debug.g_err_stage:='Selected resource_assignment_ids and wbs_element_version_ids into plsql tables';
UPDATE pa_resource_assignments
SET ta_display_flag = 'N'
WHERE ta_display_flag = 'N'
AND wbs_element_version_id = l_task_elem_version_id_tbl(k)
AND project_id = l_project_id
AND budget_version_id = l_budget_version_id
RETURNING resource_assignment_id, wbs_element_version_id BULK COLLECT INTO
l_delete_task_res_asgmt_id_tbl, l_delete_task_elem_ver_id_tbl;
IF l_delete_task_res_asgmt_id_tbl.COUNT > 0 THEN
--dbms_output.put_line('Before calling delete planning transactions:ret. status' || x_return_status);
PA_FP_PLANNING_TRANSACTION_PUB.DELETE_PLANNING_TRANSACTIONS(
P_CONTEXT => pa_fp_constants_pkg.g_calling_module_task,
P_TASK_OR_RES => 'ASSIGNMENT',
P_ELEMENT_VERSION_ID_TBL => l_delete_task_elem_ver_id_tbl,
P_TASK_NUMBER_TBL => NULL,
P_TASK_NAME_TBL => NULL,
P_RESOURCE_ASSIGNMENT_TBL => l_delete_task_res_asgmt_id_tbl,
P_VALIDATE_DELETE_FLAG => NULL,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
pa_debug.g_err_stage:='Need to call Add Planning Transactions in Update Process count of Add is:' || c_index ;
pa_debug.g_err_stage:='Need to call Delete Planning Transactions in Update Process count of Delete is:' || del_index;
pa_debug.g_err_stage:='Before calling delete planning transactions:ret. status' || x_return_status;
PA_FP_PLANNING_TRANSACTION_PUB.DELETE_PLANNING_TRANSACTIONS(
P_CONTEXT => pa_fp_constants_pkg.g_calling_module_task,
P_TASK_OR_RES => 'ASSIGNMENT',
P_ELEMENT_VERSION_ID_TBL => ld_task_elem_version_id_tbl,
P_TASK_NUMBER_TBL => NULL,
P_TASK_NAME_TBL => NULL,
P_RESOURCE_ASSIGNMENT_TBL => ld_resource_assignment_id_tbl,
P_VALIDATE_DELETE_FLAG => NULL,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
pa_debug.g_err_stage:='After Calling delete planning transactions:ret. status' || x_return_status;
pa_debug.g_err_stage:='Before Calling Update planning transactions:ret. status' || x_return_status || 'internal upd index:' || u_index;
pa_fp_planning_transaction_pub.update_planning_transactions (
p_context => pa_fp_constants_pkg.g_calling_module_task,
p_struct_elem_version_id => l_struct_elem_version_id ,
p_budget_version_id => l_budget_version_id,
p_task_elem_version_id_tbl => lu_task_elem_version_id_tbl,
p_schedule_start_date_tbl => l_start_date_tbl,
p_schedule_end_date_tbl => l_end_date_tbl,
p_planning_start_date_tbl => l_planning_start_date_tbl,
p_planning_end_date_tbl => l_planning_end_date_tbl,
p_resource_assignment_id_tbl => l_resource_assignment_id_tbl,
p_resource_list_member_id_tbl => l_resource_list_member_id_tbl,
--p_cbs_element_id_tbl => l_cbs_element_id_tbl, -- Added For CBS 16598322
p_assignment_description_tbl => l_description_tbl,
p_project_assignment_id_tbl => l_project_assignment_id_tbl,
p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl,
p_use_task_schedule_flag_tbl => l_use_task_schedule_flag_tbl,
p_quantity_tbl => l_quantity_tbl,
p_txn_currency_override_tbl => l_currency_code_tbl,
p_raw_cost_tbl => l_raw_cost_tbl,
p_burdened_cost_tbl => l_burdened_cost_tbl,
p_cost_rate_override_tbl => l_raw_cost_rate_override_tbl,
p_burdened_rate_override_tbl => l_burd_cost_rate_override_tbl,
p_billable_percent_tbl => l_billable_work_percent_tbl,
p_task_name_tbl => l_task_name_tbl,
p_task_number_tbl => l_task_number_tbl,
p_resource_alias_tbl => l_resource_alias_tbl,
p_resource_class_code_tbl => l_resource_class_code_tbl,
p_res_type_code_tbl => l_res_type_code_tbl,
p_resource_code_tbl => l_resource_code_tbl,
p_resource_name => l_resource_name,
p_project_role_id_tbl => l_project_role_id_tbl,
p_project_role_name_tbl => l_project_role_name_tbl ,
p_supplier_id_tbl => l_supplier_id_tbl,
p_supplier_name_tbl => l_supplier_name_tbl,
p_organization_id_tbl => l_organization_id_tbl,
p_organization_name_tbl => l_organization_name_tbl,
p_fc_res_type_code_tbl => l_fc_res_type_code_tbl,
p_named_role_tbl => l_named_role_tbl,
p_financial_category_name_tbl => l_financial_category_name_tbl,
p_incur_by_resource_code_tbl => l_incur_by_resource_code_tbl,
p_attribute_category_tbl => l_attribute_category_tbl,
p_attribute1_tbl => l_attribute1_tbl,
p_attribute2_tbl => l_attribute2_tbl,
p_attribute3_tbl => l_attribute3_tbl,
p_attribute4_tbl => l_attribute4_tbl,
p_attribute5_tbl => l_attribute5_tbl,
p_attribute6_tbl => l_attribute6_tbl,
p_attribute7_tbl => l_attribute7_tbl,
p_attribute8_tbl => l_attribute8_tbl,
p_attribute9_tbl => l_attribute9_tbl,
p_attribute10_tbl => l_attribute10_tbl,
p_attribute11_tbl => l_attribute11_tbl,
p_attribute12_tbl => l_attribute12_tbl,
p_attribute13_tbl => l_attribute13_tbl,
p_attribute14_tbl => l_attribute14_tbl,
p_attribute15_tbl => l_attribute15_tbl,
p_attribute16_tbl => l_attribute16_tbl,
p_attribute17_tbl => l_attribute17_tbl,
p_attribute18_tbl => l_attribute18_tbl,
p_attribute19_tbl => l_attribute19_tbl,
p_attribute20_tbl => l_attribute20_tbl,
p_attribute21_tbl => l_attribute21_tbl,
p_attribute22_tbl => l_attribute22_tbl,
p_attribute23_tbl => l_attribute23_tbl,
p_attribute24_tbl => l_attribute24_tbl,
p_attribute25_tbl => l_attribute25_tbl,
p_attribute26_tbl => l_attribute26_tbl,
p_attribute27_tbl => l_attribute27_tbl,
p_attribute28_tbl => l_attribute28_tbl,
p_attribute29_tbl => l_attribute29_tbl,
p_attribute30_tbl => l_attribute30_tbl,
p_scheduled_delay => l_scheduled_delay_tbl, --Bug 3948128
p_upd_cost_amts_too_for_ta_flg => 'Y', -- Bug 4538286
p_spread_curve_id_tbl => l_spread_curve_id_tbl, --Bug#8646853
p_sp_fixed_date_tbl => l_fixed_date_tbl,--Bug#8646853
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:='Return status after update planning transactions.' ||x_return_status;
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (p_plan_version_id => l_budget_version_id, -- added for bug 5469303
x_msg_code => l_msg_code,
x_return_status => l_return_status);
pa_debug.g_err_stage:='COMMIT done in Update Task Assignments';
pa_debug.g_err_stage:='End of Update_Task_Assignment';
ROLLBACK TO UPDATE_task_asgmts_pub;
ROLLBACK TO UPDATE_task_asgmts_pub;
ROLLBACK TO UPDATE_task_asgmts_pub;
END UPDATE_TASK_ASSIGNMENTS;
SELECT 'X', template_flag, PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(project_id)
FROM pa_projects_all
where project_id = p_pa_project_id;
SELECT 'X'
FROM pa_resource_assignments a, pa_budget_versions b
WHERE a.resource_assignment_id = p_pa_task_assignment_id
AND a.project_id = p_pa_project_id
AND b.budget_version_id = a.budget_version_id
AND b.project_structure_version_id = l_struct_ver_id;
SELECT a.resource_assignment_id
FROM pa_resource_assignments a, pa_budget_versions b
WHERE a.project_id = p_pa_project_id
AND b.budget_version_id = a.budget_version_id
AND b.project_structure_version_id = p_struct_version_id
AND a.pm_res_assignment_reference = p_pm_task_asgmt_reference
AND a.pm_product_code = p_pm_product_code;
SELECT a.resource_assignment_id
FROM pa_resource_assignments a, pa_budget_versions b
WHERE a.project_id = p_pa_project_id
AND b.budget_version_id = a.budget_version_id
AND b.project_structure_version_id = p_struct_version_id
AND a.wbs_element_version_id = p_pa_task_elem_ver_id
AND a.resource_list_member_id = p_resource_list_member_id
AND NVL(a.cbs_element_id,-1) = NVL(p_cbs_element_id,-1); -- Added for CBS 16598322
SELECT segment1
FROM pa_projects_all p
WHERE p.project_id = p_pa_project_id;
SELECT published_version.resource_assignment_id
FROM pa_resource_assignments published_version,
(SELECT task_id, resource_list_member_id
FROM pa_resource_assignments ra,
pa_budget_versions bv,
pa_proj_elem_ver_structure pevs
WHERE ra.pm_product_code = p_pm_product_code
AND ra.pm_res_assignment_reference = p_pm_task_asgmt_reference
AND ra.project_id = p_pa_project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_id = p_pa_project_id
AND bv.project_structure_version_id = pevs.element_version_id
AND pevs.current_flag = 'N'
AND pevs.project_id = p_pa_project_id) working_version,
pa_budget_versions bv
WHERE working_version.task_id = published_version.task_id
AND working_version.resource_list_member_id = published_version.resource_list_member_id
AND bv.project_structure_version_id = l_structure_version_id
AND published_version.project_id = p_pa_project_id
AND bv.budget_version_id = published_version.budget_version_id
AND rownum = 1;
select c.element_version_id
from pa_proj_element_versions c,
pa_proj_elements b,
pa_proj_structure_types a,
pa_structure_types d
where d.structure_type_class_code = 'WORKPLAN'
and d.structure_type_id = a.structure_type_id
and a.proj_element_id = b.proj_element_id
and b.project_id = p_pa_project_id
and b.proj_element_id = c.proj_element_id
and b.project_id = c.project_id
and c.object_type = 'PA_STRUCTURES';