[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_PROGRESS
( p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
, p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_as_of_date IN DATE
, p_percent_complete IN NUMBER
, p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_object_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_object_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_object_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_progress_status_code IN VARCHAR2 := 'PROGRESS_STAT_ON_TRACK'
, p_progress_comment IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_actual_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
, p_actual_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
, p_estimated_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
, p_estimated_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
, p_scheduled_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
, p_scheduled_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
, p_task_status IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_structure_type IN VARCHAR2 := 'FINANCIAL'
, p_est_remaining_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_actual_work_quantity IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_etc_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM /* FP M Task Progress 3420093*/
, p_pm_deliverable_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 3606627
, p_pm_task_assgn_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 3606627
, p_actual_cost_to_date IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM -- Bug 3606627
, p_actual_effort_to_date IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM -- Bug 3606627
, p_populate_pji_tables IN VARCHAR2 := 'Y' -- Bug 3606627
, p_rollup_entire_wbs IN VARCHAR2 := 'N' -- Bug 3606627
, p_txn_currency_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
-- Fix for Bug # 3988457.
)
IS
l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PROGRESS';
SELECT trunc(date_computed)
FROM pa_percent_completes pc
WHERE pc.project_id = l_project_id_out
AND pc.task_id = l_task_id_out
AND pc.current_flag = 'Y';*/
select ppevs.proj_element_id, ppevs.element_version_id
from pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
where ppevs.project_id = c_project_id
and ppevs.proj_element_id = ppst.proj_element_id
and ppevs.status_code = 'STRUCTURE_PUBLISHED'
and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = c_structure_type;
select elev.element_version_id, elev.parent_structure_version_id
from pa_proj_element_versions elev,
pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
where elev.project_id = c_project_id
and elev.object_type = c_object_type
and elev.proj_element_id = c_proj_element_id
and elev.parent_structure_version_id = ppevs.element_version_id
and ppevs.project_id = c_project_id
and ppevs.proj_element_id = ppst.proj_element_id
and ppevs.status_code = 'STRUCTURE_PUBLISHED'
and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = c_structure_type;
SELECT ppe.proj_element_id --, por.object_id_to1 (commented by rtarway for BUG 3746647)
FROM pa_proj_elements ppe,
pa_object_relationships por
WHERE
ppe.object_type = 'PA_TASKS'
and ppe.proj_element_id = por.object_id_from2
and por.object_id_to2 = c_del_proj_element_id
and por.object_type_to = c_object_type
and por.relationship_type = 'A'
and por.relationship_subtype = 'TASK_TO_DELIVERABLE'
and ppe.base_percent_comp_deriv_code='DELIVERABLE';
SELECT ppev.element_version_id
FROM pa_proj_element_versions ppev
WHERE ppev.proj_element_id = c_del_proj_element_id
AND ppev.project_id = c_project_id
AND ppev.object_type = 'PA_DELIVERABLES' ;
SELECT resource_list_member_id
FROM pa_task_assignments_v
WHERE project_id = c_project_id
AND resource_assignment_id = c_object_id
AND structure_version_id = c_structure_version_id
AND task_version_id = c_task_version_id;
SELECT 'Y', ta_display_flag
FROM pa_task_assignments_v
WHERE project_id = c_project_id
AND resource_list_member_id = c_object_id
AND structure_version_id = c_structure_version_id
AND task_version_id = c_task_version_id;
pa_debug.init_err_stack ('PA_PROGRESS_PUB.UPDATE_PROGRESS');
SAVEPOINT Update_Progress_Pub;
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'PA_STATUS_PUB.UPDATE_PROGRESS Start : Passed Parameters :', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_api_version_number='||p_api_version_number, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_init_msg_list='||p_init_msg_list, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_commit='||p_commit, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_project_id='||p_project_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_project_reference='||p_pm_project_reference, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_task_id='||p_task_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_task_reference='||p_pm_task_reference, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_as_of_date='||p_as_of_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_percent_complete='||p_percent_complete, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_product_code='||p_pm_product_code, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_description='||p_description, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_object_id='||p_object_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_object_version_id='||p_object_version_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_object_type='||p_object_type, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_progress_status_code='||p_progress_status_code, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_progress_comment='||p_progress_comment, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_start_date='||p_actual_start_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_finish_date='||p_actual_finish_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_estimated_start_date='||p_estimated_start_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_estimated_finish_date='||p_estimated_finish_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_scheduled_start_date='||p_scheduled_start_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_scheduled_finish_date='||p_scheduled_finish_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_task_status='||p_task_status, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_structure_type='||p_structure_type, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_est_remaining_effort='||p_est_remaining_effort, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_work_quantity='||p_actual_work_quantity, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_etc_cost='||p_etc_cost, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_deliverable_reference='||p_pm_deliverable_reference, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_task_assgn_reference='||p_pm_task_assgn_reference, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_cost_to_date='||p_actual_cost_to_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_effort_to_date='||p_actual_effort_to_date, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'G_bulk_load_flag is N, Calling Project_Level_Validations', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'After Project_Level_Validations l_return_status='||l_return_status, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Case 1 : task_id is passed as 0 and object_type and object_id is not passed', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Case 2 : task_id is passed or task_ref is passed and object_type and object_id is not passed', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Case 3 : object_type is passed', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_object_type='||l_object_type, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_object_id='||l_object_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_object_version_id='||l_object_version_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_structure_version_id='||l_structure_version_id, x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_task_id='||l_task_id, x_Log_Level=> 3);
to PA_PROGRESS_PUB.UPDATE_PROGRESS call */
-- Bug 3606627 : Using new signature of GET_LATEST_AS_OF_DATE
--BUG 4133128, rtarway
-- bug 4868792 added progress mode check, latest_as_of_date check
---- the reason we have to check valid as_of_date for each record is because this can only be done after finding out it is future or backdated record
----if (G_bulk_load_flag <> 'Y' and l_progress_mode = 'FUTURE') then
if p_structure_type <> 'FINANCIAL' then -- added for bug 5398704
if (l_progress_mode = 'FUTURE') then
if (l_object_type = 'PA_DELIVERABLES' OR l_object_type = 'PA_TASKS' OR l_object_type = 'PA_STRUCTURES')then
IF PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE(TRUNC(l_as_of_date), l_project_id_out, l_object_id, l_object_type ) = 'N'
AND trunc(nvl(l_latest_as_of_date,l_as_of_date + 1 )) <> TRUNC(l_as_of_date)
THEN
PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
,p_msg_name => 'PA_TP_INV_AOD2');
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Calling PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Log_Level=> 3);
PA_PROGRESS_PUB.UPDATE_PROGRESS(
p_api_version => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => FND_API.G_FALSE, --Bug 3754134 instead passing p_commit pass false
p_action => 'PUBLISH',
P_rollup_entire_wbs_flag => l_rollup_entire_wbs,
p_progress_mode => l_progress_mode,
p_calling_module => 'AMG',
p_project_id => l_project_id_out,
p_object_id => l_object_id,
p_object_version_id => l_object_version_id,
p_object_type => l_object_type,
p_as_of_date => trunc(l_as_of_date), -- 5294838
p_percent_complete => p_percent_complete,
p_progress_status_code => l_progress_status_code,
p_progress_comment => l_progress_comment,
p_brief_overview => l_description,
p_actual_start_date => l_actual_start_date,
p_actual_finish_date => l_actual_finish_date,
p_estimated_start_date => l_estimated_start_date,
p_estimated_finish_date => l_estimated_finish_date,
p_scheduled_start_date => l_scheduled_start_date,
p_scheduled_finish_date => l_scheduled_finish_date,
p_pm_product_code => l_pm_product_code,
p_record_version_number => 1,
p_task_status => l_task_status,
p_est_remaining_effort => l_est_remaining_effort,
p_actual_work_quantity => l_actual_work_quantity,
p_ETC_cost => l_etc_cost,
p_structure_type => p_structure_type,
p_actual_effort => l_ACTUAL_EFFORT_TO_DATE, -- Bug 3799841 : there was swap b/w cost and effort
p_actual_cost => l_ACTUAL_COST_TO_DATE,
p_task_id => l_task_id,
p_structure_version_id => l_structure_version_id,
p_prog_fom_wp_flag => 'N',
p_txn_currency_code => p_txn_currency_code, -- Fix for Bug # 3988457.
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data );
PA_PROGRESS_PUB.UPDATE_PROGRESS(
p_api_version => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => FND_API.G_FALSE,
p_action => 'PUBLISH',
P_rollup_entire_wbs_flag => l_rollup_entire_wbs,
p_progress_mode => l_progress_mode,
p_calling_module => 'AMG',
p_project_id => l_project_id_out,
p_object_id => l_object_id,
p_object_version_id => l_object_version_id,
p_object_type => l_object_type,
p_as_of_date => trunc(l_as_of_date), -- 5294838
p_percent_complete => p_percent_complete,
p_progress_status_code => l_progress_status_code,
p_progress_comment => l_progress_comment,
p_brief_overview => l_description,
p_scheduled_start_date => l_scheduled_start_date,
p_scheduled_finish_date => l_scheduled_finish_date,
p_pm_product_code => l_pm_product_code,
p_record_version_number => 1,
p_task_status => l_task_status,
p_structure_type => p_structure_type,
p_task_id => l_task_id,
p_structure_version_id => l_structure_version_id,
p_prog_fom_wp_flag => 'N',
p_txn_currency_code => p_txn_currency_code,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data );
pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'After call of PA_PROGRESS_PUB.UPDATE_PROGRESS p_return_status='||p_return_status, x_Log_Level=> 3);
ROLLBACK TO Update_Progress_Pub;
ROLLBACK TO Update_Progress_Pub;
ROLLBACK TO Update_Progress_Pub;
ROLLBACK TO Update_Progress_Pub;
ROLLBACK TO Update_Progress_Pub;
END Update_Progress;
PROCEDURE Update_Earned_Value
(p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
, p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
, p_as_of_date IN DATE
, p_bcws_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_acwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_bcwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_bac_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_bcws_itd IN NUMBER
, p_acwp_itd IN NUMBER
, p_bcwp_itd IN NUMBER
, p_bac_itd IN NUMBER
, p_bqws_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_aqwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_bqwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_baq_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
, p_bqws_itd IN NUMBER
, p_aqwp_itd IN NUMBER
, p_bqwp_itd IN NUMBER
, p_baq_itd IN NUMBER
)
IS
l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Earned_Value';
SELECT trunc(as_of_date)
FROM pa_earned_values ev
WHERE ev.project_id = l_project_id_out
AND ev.task_id = l_task_id_out
AND ev.resource_list_member_id = l_resource_list_member_id_out
AND ev.current_flag = 'Y'
FOR UPDATE NOWAIT;
SAVEPOINT Update_Earned_Value_Pub;
p_function_name => 'PA_PM_UPDATE_EARNED_VALUE',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed );
IF pa_security.allow_update (x_project_id => l_project_id_out ) = 'N'
THEN
-- The user does not have update privileges on this project
-- Hence , raise error
FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
UPDATE pa_earned_values ev
SET ev.current_flag = 'N'
WHERE ev.project_id = l_project_id_out
AND ev.task_id = l_task_id_out
AND ev.resource_list_member_id = l_resource_list_member_id_out
AND ev.current_flag = 'Y';
INSERT INTO pa_earned_values (
PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AS_OF_DATE
, CURRENT_FLAG
, BCWS
, ACWP
, BCWP
, BAC
, BCWS_ITD
, ACWP_ITD
, BCWP_ITD
, BAC_ITD
, BQWS
, AQWP
, BQWP
, BAQ
, BQWS_ITD
, AQWP_ITD
, BQWP_ITD
, BAQ_ITD
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN)
VALUES (
l_project_id_out
, l_task_id_out
, l_resource_list_member_id_out
, p_as_of_date
, l_current_flag
, pa_currency.round_currency_amt(l_bcws_current)
, pa_currency.round_currency_amt(l_acwp_current)
, pa_currency.round_currency_amt(l_bcwp_current)
, pa_currency.round_currency_amt(l_bac_current)
, pa_currency.round_currency_amt(p_bcws_itd)
, pa_currency.round_currency_amt(p_acwp_itd)
, pa_currency.round_currency_amt(p_bcwp_itd)
, pa_currency.round_currency_amt(p_bac_itd)
, l_bqws_current
, l_aqwp_current
, l_bqwp_current
, l_baq_current
, p_bqws_itd
, p_aqwp_itd
, p_bqwp_itd
, p_baq_itd
, g_last_update_date
, g_last_updated_by
, g_creation_date
, g_created_by
, g_last_update_login);
FND_MESSAGE.SET_NAME('PA','PA_EV_INSERT_ERROR');
ROLLBACK TO Update_Earned_Value_Pub;
ROLLBACK TO Update_Earned_Value_Pub;
ROLLBACK TO Update_Progress_Pub;
ROLLBACK TO Update_Earned_Value_Pub;
END Update_Earned_Value;
PROCEDURE Init_Update_Task_Progress
(p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
, p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Init_Update_Task_Progress';
G_TASK_PROGRESS_in_tbl.delete;
END Init_Update_Task_Progress;
PROCEDURE Execute_Update_Task_Progress
( p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
, p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
, p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_UPDATE_TASK_PROGRESS';
select ppevs.proj_element_id, ppevs.element_version_id
from pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
where ppevs.project_id = c_project_id
and ppevs.proj_element_id = ppst.proj_element_id
and ppevs.status_code = 'STRUCTURE_PUBLISHED'
and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = c_structure_type;
SELECT task_weight_basis_code
FROM pa_proj_progress_attr
WHERE project_id = c_project_id
AND structure_type = c_structure_type;
l_progress_updated_flag VARCHAR2(1) := 'N';
savepoint Execute_Update_Task_Progress;
FND_MESSAGE.SET_NAME('PA','PA_NO_TASK_PROGRESS_UPDATE');
g_task_version_id_tbl.delete; -- Bug 4218507
select 'Y' into l_rollup from dual
where exists (select 1 from pa_percent_completes
where task_id = l_TASK_ID
AND PROJECT_ID = l_project_id_out
and object_type = 'PA_TASKS'
and PROGRESS_STATUS_CODE is not null);
l_progress_updated_flag := 'Y'; --maansari6/28 bug 3673618
PA_STATUS_PUB.UPDATE_PROGRESS
(p_api_version_number => p_api_version_number
,p_msg_count => l_msg_count
,p_msg_data => l_msg_data
,p_commit => FND_API.G_FALSE -- Bug 3754134 Added this
,p_return_status => l_return_status
, p_project_id => l_project_id_out
, p_pm_project_reference => g_pm_project_reference
, p_task_id => l_task_id
, p_pm_task_reference => l_pm_task_reference
, p_as_of_date => g_as_of_date
, p_percent_complete => l_percent_complete
, p_pm_product_code => g_pm_product_code
, p_description => l_description
, p_object_id => l_object_id
, p_object_version_id => l_object_version_id
, p_object_type => l_object_type
, p_progress_status_code => l_progress_status_code
, p_progress_comment => l_progress_comment
, p_actual_start_date => l_actual_start_date
, p_actual_finish_date => l_actual_finish_date
, p_estimated_start_date => l_estimated_start_date
, p_estimated_finish_date => l_estimated_finish_date
, p_scheduled_start_date => l_scheduled_start_date
, p_scheduled_finish_date => l_scheduled_finish_date
, p_task_status => l_task_status
, p_structure_type => g_structure_type
, p_est_remaining_effort => l_est_remaining_effort
, p_actual_work_quantity => l_actual_work_quantity
, p_etc_cost => l_etc_cost -- bug 3606627
, p_pm_deliverable_reference => l_pm_deliverable_reference -- bug 3606627
, p_pm_task_assgn_reference => l_pm_task_assgn_reference -- bug 3606627
, p_actual_cost_to_date => l_actual_cost_to_date -- bug 3606627
, p_actual_effort_to_date => l_actual_effort_to_date -- bug 3606627
, p_populate_pji_tables => 'N' -- bug 3606627
, p_rollup_entire_wbs => 'Y' -- bug 3606627
);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_msg_count);
IF l_progress_updated_flag = 'N'
THEN
return;
delete from PA_PROG_ACT_BY_PERIOD_TEMP where project_id = l_project_id_out
AND structure_version_id = l_structure_version_id;
p_procedure_name => 'execute_update_progress',
p_error_text => SUBSTRB('PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_WRP:'||SQLERRM,1,120));
PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (x_msg_code => l_msg_code,
x_return_status => l_return_status);
p_procedure_name => 'EXECUTE_UPDATE_TASK_PROGRESS',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE Failed:'||SQLERRM,1,120));
p_procedure_name => 'EXECUTE_UPDATE_TASK_PROGRESS',
p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_UTILS.populate_workplan_data: failed'||SQLERRM,1,120));
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Msg => 'Calling program_rollup_pvt', x_Log_Level=> 3);
pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Msg => 'After Calling program_rollup_pvt l_return_status='||l_return_status, x_Log_Level=> 3);
select STRUCTURE_TYPE into G_STRUCTURE_TYPE
from PA_STRUCT_VERSIONS_LOV_AMG_V where STRUCTURE_VERSION_ID = l_structure_version_id;
PA_PROGRESS_PUB.INSERT_TASK_PROGRESSES(
p_api_version => p_api_version_number,
p_calling_module => 'AMG',
p_project_id => l_project_id_out,
p_pm_product_code => G_PM_PRODUCT_CODE,
p_structure_version_id => l_structure_version_id,
p_structure_type => G_STRUCTURE_TYPE,
p_as_of_date => G_AS_OF_DATE,
p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data
);
PA_PROGRESS_PUB.UPDATE_ROLLUP_PROGRESSES(
p_api_version => p_api_version_number,
p_calling_module => 'AMG',
p_progress_mode => l_progress_mode,
p_project_id => l_project_id_out,
p_structure_version_id => l_structure_version_id,
p_structure_type => G_STRUCTURE_TYPE,
p_as_of_date => G_AS_OF_DATE,
p_rollup_table => l_rollup_table,
p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data
);
pa_status_pub.update_task_progress_amg(
p_api_version => p_api_version_number,
p_calling_module => 'AMG',
p_progress_mode => l_progress_mode,
p_project_id => l_project_id_out,
p_structure_version_id => l_structure_version_id,
p_structure_type => G_STRUCTURE_TYPE,
p_as_of_date => G_AS_OF_DATE,
p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
x_return_status => l_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data
);
PA_TASK_PVT1.Update_Dates_To_All_Versions(
p_project_id => l_project_id_out
,p_element_version_id => l_object_version_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
Select ppev1.proj_element_id
into l_structure_id
from pa_proj_element_versions ppev1, pa_proj_element_versions ppev2
where ppev2.element_version_id = l_object_version_id
and ppev2.project_id = ppev1.project_id
and ppev2.parent_structure_version_id = ppev1.element_version_id;
select nvl(max(as_of_date),sysdate) into l_max_as_of_date
from pa_progress_rollup where project_id= l_project_id_out;
update pa_progress_rollup
set COMPLETED_PERCENTAGE = null
where project_id = l_project_id_out
AND OBJECT_TYPE ='PA_TASKS'
AND CURRENT_FLAG='Y'
AND as_of_date=l_max_as_of_date
and nvl(COMPLETED_PERCENTAGE,0) = nvl(eff_rollup_percent_comp, -1);
ROLLBACK TO Execute_Update_Task_Progress;
G_TASK_PROGRESS_in_tbl.delete;
ROLLBACK TO Execute_Update_Task_Progress;
G_TASK_PROGRESS_in_tbl.delete;
ROLLBACK TO Execute_Update_Task_Progress;
G_TASK_PROGRESS_in_tbl.delete;
ROLLBACK TO Execute_Update_Task_Progress;
G_TASK_PROGRESS_in_tbl.delete;
END Execute_Update_Task_Progress;
p_function_name => 'PA_PM_UPDATE_PROJ_PROGRESS',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed );
IF pa_security.allow_update (x_project_id => l_project_id_out ) = 'N'
THEN
-- The user does not have update privileges on this project
-- Hence , raise error
FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
PROCEDURE update_task_progress_amg
( p_api_version IN NUMBER :=1.0
,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
,p_commit IN VARCHAR2 :=FND_API.G_FALSE
,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
,p_debug_mode IN VARCHAR2 :='N'
,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_progress_mode IN VARCHAR2 := 'FUTURE'
,p_project_id IN NUMBER
,p_structure_version_id IN NUMBER
,p_structure_type IN VARCHAR2
,p_as_of_date IN DATE
,p_task_progress_list_table IN PA_PROGRESS_PUB.PA_TASK_PROGRESS_LIST_TBL_TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_task_progress_rec PA_PROGRESS_PUB.PA_TASK_PROGRESS_LIST_REC_TYPE;
l_api_name VARCHAR2(30) := 'update_task_progress_amg';
PA_PROGRESS_PUB.UPDATE_PROGRESS(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_validation_level => p_validation_level
,p_calling_module => 'AMG'
,p_debug_mode => p_debug_mode
,p_max_msg_count => p_max_msg_count
,P_rollup_entire_wbs_flag => 'N'
,p_progress_mode => p_progress_mode
--,p_percent_complete_id => l_task_progress_rec.percent_complete_id
,p_project_id => p_project_id
,p_object_id => l_task_progress_rec.object_id
,p_object_version_id => l_task_progress_rec.object_version_id
,p_object_type => l_task_progress_rec.object_type
,p_as_of_date => p_as_of_date
,p_percent_complete => l_task_progress_rec.percent_complete
,p_progress_status_code => l_task_progress_rec.progress_status_code
,p_progress_comment => l_task_progress_rec.progress_comment
--,p_brief_overview => l_task_progress_rec.brief_overview
,p_actual_start_date => l_task_progress_rec.actual_start_date
,p_actual_finish_date => l_task_progress_rec.actual_finish_date
,p_estimated_start_date => l_task_progress_rec.estimated_start_date
,p_estimated_finish_date => l_task_progress_rec.estimated_finish_date
,p_scheduled_start_date => l_task_progress_rec.scheduled_start_date
,p_scheduled_finish_date => l_task_progress_rec.scheduled_finish_date
--,p_record_version_number => l_task_progress_rec.record_version_number
,p_task_status => l_task_progress_rec.task_status
,p_est_remaining_effort => l_task_progress_rec.est_remaining_effort
,p_ETC_cost => l_task_progress_rec.ETC_cost
,p_actual_work_quantity => l_task_progress_rec.actual_work_quantity
--,p_pm_product_code => l_task_progress_rec.pm_product_code
,p_structure_type => p_structure_type
--,p_actual_effort => l_task_progress_rec.actual_effort
--,p_actual_cost => l_task_progress_rec.actual_cost
--,p_actual_effort_this_period => l_task_progress_rec.actual_effort_this_period
--,p_actual_cost_this_period => l_task_progress_rec.actual_cost_this_period
--,p_object_sub_type => l_task_progress_rec.object_sub_type
,p_task_id => l_task_progress_rec.task_id
,p_structure_version_id => p_structure_version_id
--,p_prog_fom_wp_flag => l_task_progress_rec.prog_fom_wp_flag
--,p_rollup_reporting_lines_flag=> l_task_progress_rec.rollup_reporting_lines_flag
--,p_planned_cost => l_task_progress_rec.planned_cost
--,p_planned_effort => l_task_progress_rec.planned_effort
--,p_rate_based_flag => l_task_progress_rec.rate_based_flag
--,p_resource_class_code => l_task_progress_rec.resource_class_code
--,p_transfer_wp_pc_flag => l_task_progress_rec.transfer_wp_pc_flag
--,p_rbs_element_id => l_task_progress_rec.rbs_element_id
--,p_resource_list_member_id => l_task_progress_rec.resource_list_member_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
end update_task_progress_amg;