[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select project_type
From pa_projects_all
Where project_id = c_project_id;
PROCEDURE UPDATE_PROJECT_BASIC_INFO
(
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 := FND_API.G_MISS_NUM ,
p_project_id IN NUMBER ,
p_project_name IN VARCHAR2 ,
p_project_number IN VARCHAR2 ,
p_project_type IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_description IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_project_status_code IN VARCHAR2 ,
p_public_sector_flag IN VARCHAR2 ,
p_carrying_out_organization_id IN NUMBER := FND_API.G_MISS_NUM ,
p_organization_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_start_date IN DATE ,
p_completion_date IN DATE := FND_API.G_MISS_DATE ,
p_territory_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_country IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_location_id IN NUMBER := FND_API.G_MISS_NUM ,
p_state_region IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_city IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_record_version_number IN NUMBER ,
p_recalculate_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
-- anlee
-- Date changes
p_target_start_date IN DATE := FND_API.G_MISS_DATE ,
p_target_finish_date IN DATE := FND_API.G_MISS_DATE ,
-- End of changes
p_security_level IN NUMBER := FND_API.G_MISS_NUM ,
-- anlee
-- Project Long Name changes
p_long_name IN VARCHAR2 DEFAULT NULL ,
-- end of changes
p_funding_approval_status IN VARCHAR2 DEFAULT NULL , -- added for 4055319
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_api_name CONSTANT VARCHAR(30) := 'update_project_basic_info';
SAVEPOINT update_project_basic_info;
pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_basic_info');
pa_debug.debug('Update_project_basic_info PUB : Checking API compatibility');
pa_debug.debug('Update_project_basic_info PUB : Checking Carrying out org');
pa_debug.debug('Update_project_basic_info PUB : Checking message count');
pa_debug.debug('Update_project_basic_info PUB : Calling private API update_project_basic_info');
PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_BASIC_INFO
(
p_commit => FND_API.G_FALSE,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module, -- added for bug 7204572
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_project_id => p_project_id,
p_project_name => p_project_name,
p_project_number => p_project_number,
p_project_type => p_project_type,
p_description => p_description,
p_project_status_code => p_project_status_code,
p_public_sector_flag => p_public_sector_flag,
p_carrying_out_organization_id => l_carrying_out_organization_id,
p_start_date => p_start_date,
p_completion_date => p_completion_date,
p_territory_code => p_territory_code,
p_country => p_country,
p_location_id => p_location_id,
p_state_region => p_state_region,
p_city => p_city,
p_priority_code => p_priority_code,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_record_version_number => p_record_version_number,
p_recalculate_flag => p_recalculate_flag,
-- anlee
-- Dates changes
p_target_start_date => p_target_start_date,
p_target_finish_date => p_target_finish_date,
-- End of changes
p_security_level => p_security_level,
-- anlee
-- Project Long Name changes
p_long_name => p_long_name,
-- End of changes
p_funding_approval_status => p_funding_approval_status, -- 4055319
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('Update_project_basic_info PUB : Checking message count');
ROLLBACK TO update_project_basic_info;
p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO update_project_basic_info;
ROLLBACK TO update_project_basic_info;
p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_PROJECT_BASIC_INFO;
PROCEDURE UPDATE_PROJECT_ADDITIONAL_INFO
(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 := FND_API.G_MISS_NUM ,
p_project_id IN NUMBER ,
p_calendar_id IN NUMBER := FND_API.G_MISS_NUM ,
p_calendar_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_work_type_id IN NUMBER ,
p_role_list_id IN NUMBER := FND_API.G_MISS_NUM ,
p_cost_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
p_bill_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
p_split_cost_from_wokplan_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_split_cost_from_bill_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_record_version_number IN NUMBER ,
p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_api_name CONSTANT VARCHAR(30) := 'update_project_additional_info';
SAVEPOINT update_project_additional_info;
pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_additional_info');
pa_debug.debug('Update_project_Additional_info PUB : Checking API Compatibility');
pa_debug.debug('Update_Project_additional_info PUB : Checking calendar');
pa_debug.debug('Update_project_Additional_info PUB : Calling private API Update_project_additional_info ');
PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_ADDITIONAL_INFO
(
p_commit => FND_API.G_FALSE,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module,
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_project_id => p_project_id,
p_calendar_id => l_calendar_id,
p_work_type_id => p_work_type_id,
p_role_list_id => p_role_list_id,
p_cost_job_group_id => l_cost_job_group_id,
p_bill_job_group_id => p_bill_job_group_id,
p_split_cost_from_wokplan_flag => p_split_cost_from_wokplan_flag,
p_split_cost_from_bill_flag => p_split_cost_from_bill_flag,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_record_version_number => p_record_version_number,
p_sys_program_flag => p_sys_program_flag,
p_allow_multi_prog_rollup => p_allow_multi_prog_rollup,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('Update_project_Additional_info PUB : Checking message count');
ROLLBACK TO update_project_additional_info;
p_procedure_name => 'UPDATE_PROJECT_ADDITIONAL_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO update_project_additional_info;
ROLLBACK TO update_project_additional_info;
p_procedure_name => 'UPDATE_PROJECT_ADDITIONAL_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_PROJECT_ADDITIONAL_INFO;
PROCEDURE UPDATE_PROJECT_PIPELINE_INFO
(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 := FND_API.G_MISS_NUM ,
p_project_id IN NUMBER ,
p_probability_member_id IN NUMBER := FND_API.G_MISS_NUM ,
p_probability_percentage IN NUMBER := FND_API.G_MISS_NUM ,
p_project_value IN NUMBER := FND_API.G_MISS_NUM ,
p_opportunity_value IN NUMBER := FND_API.G_MISS_NUM ,
p_opp_value_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_expected_approval_date IN DATE := NULL ,
p_record_version_number IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_api_name CONSTANT VARCHAR(30) := 'update_project_pipeline_info';
Select pr.project_type, pr.probability_member_id, pr.expected_approval_date,
op.opportunity_value, op.opp_value_currency_code
From pa_projects_all pr, pa_project_opp_attrs op
Where pr.project_id = op.project_id
AND pr.project_id = p_project_id;
SAVEPOINT update_project_pipeline_info;
pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_pipeline_info');
PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
,x_msg => 'Beginning of update_project_pipeline_info'
,x_log_level => 3);
pa_debug.debug('Update_project_pipeline_info PUB : Checking API Compatibility');
PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
,x_msg => 'AFTER check probablity ... '||l_return_status
,x_log_level => 3);
pa_debug.debug('Update_project_pipeline_info PUB : Checking message count');
pa_debug.debug('Update_project_pipeline_info PUB : Calling private API update_project_pipeline_info');
PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
,x_msg => 'Before calling PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_PIPELINE_INFO'
,x_log_level => 3);
PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_PIPELINE_INFO (
p_commit => FND_API.G_FALSE,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module,
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_project_id => p_project_id,
p_probability_member_id => l_probability_member_id,
p_project_value => p_project_value,
p_expected_approval_date => l_expected_approval_date,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PA_DEBUG.write (x_module => 'pa.plsql.PA_PROJECTS_MAINT_PUB.update_project_pipeline_info'
,x_msg => 'After calling PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_PIPELINE_INFO'
,x_log_level => 3);
p_update_project => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('Update_project_pipeline_info PUB : Checking message count');
ROLLBACK TO update_project_pipeline_info;
p_procedure_name => 'UPDATE_PROJECT_PIPELINE_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO update_project_pipeline_info;
ROLLBACK TO update_project_pipeline_info;
p_procedure_name => 'UPDATE_PROJECT_PIPELINE_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_PROJECT_PIPELINE_INFO;
SELECT 'x' INTO l_dummy
FROM pa_class_categories
WHERE CLASS_CATEGORY = p_class_category;
SELECT 'x' INTO l_dummy
FROM pa_class_codes
WHERE CLASS_CODE = p_class_code and
CLASS_CATEGORY = p_class_category;
PROCEDURE UPDATE_CLASSIFICATIONS
(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 := FND_API.G_MISS_NUM ,
p_object_id IN NUMBER,
p_object_type IN VARCHAR2,
p_class_category IN VARCHAR2 ,
p_class_code IN VARCHAR2 ,
p_code_percentage IN NUMBER := FND_API.G_MISS_NUM ,
p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_rowid IN VARCHAR2 ,
p_record_version_number IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_dummy VARCHAR2(1);
SAVEPOINT update_classifications;
pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_classifications');
pa_debug.debug('Update_classifications PUB : Checking API compatibility');
pa_debug.debug('Update_classifications PUB : Calling Private API Update_classifications');
pa_debug.debug('Update_classifications PUB: Checking Class Cateogory ');
SELECT 'x' INTO l_dummy
FROM pa_class_categories
WHERE CLASS_CATEGORY = p_class_category;
p_procedure_name => 'UPDATE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
pa_debug.debug('Update_classifications PUB: Checking Class Category / Codes Pair');
SELECT 'x' INTO l_dummy
FROM pa_class_codes
WHERE CLASS_CODE = p_class_code and
CLASS_CATEGORY = p_class_category;
p_procedure_name => 'UPDATE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
PA_PROJECTS_MAINT_PVT.UPDATE_CLASSIFICATIONS
(
p_commit => FND_API.G_FALSE,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module,
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_object_id => p_object_id,
p_object_type => p_object_type,
p_class_category => p_class_category,
p_class_code => p_class_code,
p_code_percentage => p_code_percentage,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_rowid => p_rowid,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO update_classifications;
p_procedure_name => 'UPDATE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO update_classifications;
ROLLBACK TO UPDATE_CLASSIFICATIONS;
p_procedure_name => 'UPDATE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_CLASSIFICATIONS;
PROCEDURE DELETE_CLASSIFICATIONS
(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 := FND_API.G_MISS_NUM ,
p_object_id IN NUMBER ,
p_object_type IN VARCHAR2 ,
p_class_category IN VARCHAR2 ,
p_class_code IN VARCHAR2 ,
p_record_version_number IN NUMBER := FND_API.G_MISS_NUM ,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_api_name CONSTANT VARCHAR(30) := 'delete_classifications';
l_delete_flag VARCHAR2(1) := 'N';
SAVEPOINT delete_classifications;
pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.delete_classifications');
pa_debug.debug('delete_classifications PUB : Checking API Compatibility');
pa_debug.debug('delete_classifications PUB : Calling private API delete_classifcations');
PA_PROJECTS_MAINT_PVT.DELETE_CLASSIFICATIONS
(
p_commit => FND_API.G_FALSE,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module,
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_object_id => p_object_id,
p_object_type => p_object_type,
p_class_category => p_class_category,
p_class_code => p_class_code,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('delete_classifications PUB : Checking message count');
ROLLBACK TO delete_classifications;
p_procedure_name => 'DELETE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO delete_classifications;
ROLLBACK TO delete_classifications;
p_procedure_name => 'DELETE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_CLASSIFICATIONS;
PROCEDURE UPDATE_PROJECT_STAFFING_INFO
(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 := FND_API.G_MISS_NUM ,
p_project_id IN NUMBER ,
p_comp_match_weighting IN pa_projects_all.COMPETENCE_MATCH_WT%TYPE := FND_API.G_MISS_NUM,
p_avail_match_weighting IN pa_projects_all.availability_match_wt%TYPE := FND_API.G_MISS_NUM,
p_job_level_match_weighting IN pa_projects_all.job_level_match_wt%TYPE := FND_API.G_MISS_NUM,
p_search_min_availability IN pa_projects_all.search_min_availability%TYPE := FND_API.G_MISS_NUM,
p_search_country_code IN pa_projects_all.search_country_code%TYPE := FND_API.G_MISS_CHAR,
p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR,
p_search_exp_org_struct_ver_id IN pa_projects_all.search_org_hier_id%TYPE := FND_API.G_MISS_NUM,
p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR,
p_search_exp_start_org_id IN pa_projects_all.search_starting_org_id%TYPE := FND_API.G_MISS_NUM,
p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR,
p_search_min_candidate_score IN pa_projects_all.min_cand_score_reqd_for_nom%TYPE := FND_API.G_MISS_NUM,
p_enable_auto_cand_nom_flag IN pa_projects_all.enable_automated_search%TYPE := FND_API.G_MISS_CHAR,
p_record_version_number IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_api_name CONSTANT VARCHAR(30) := 'update_project_staffing_info';
SAVEPOINT update_project_staffing_info;
pa_debug.init_err_stack('PA_PROJECTS_MAINT_PUB.update_project_staffing_info');
pa_debug.debug('Update_project_staffing_info PUB : Checking API Compatibility');
pa_debug.debug('update_project_staffing_info PUB : Calling private API update_project_staffing_info');
PA_PROJECTS_MAINT_PVT.UPDATE_PROJECT_STAFFING_INFO
(
p_commit => FND_API.G_FALSE,
p_validate_only => p_validate_only,
p_validation_level => p_validation_level,
p_calling_module => p_calling_module,
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_project_id => p_project_id,
p_comp_match_weighting => p_comp_match_weighting,
p_avail_match_weighting => p_avail_match_weighting,
p_job_level_match_weighting => p_job_level_match_weighting,
p_search_min_availability => p_search_min_availability,
p_search_country_code => l_search_country_code,
p_search_exp_org_struct_ver_id => l_search_exp_org_struct_ver_id,
p_search_exp_start_org_id => l_search_exp_start_org_id,
p_search_min_candidate_score => p_search_min_candidate_score,
p_enable_auto_cand_nom_flag => p_enable_auto_cand_nom_flag,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pa_debug.debug('update_project_staffing_info PUB : Checking message count');
ROLLBACK TO update_project_staffing_info;
p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO update_project_staffing_info;
ROLLBACK TO update_project_staffing_info;
p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_PROJECT_STAFFING_INFO;
SELECT ppc.customer_id, ppp.record_version_number, ppc.project_party_id
FROM PA_PROJECT_CUSTOMERS ppc,
PA_PROJECT_PARTIES ppp
WHERE ppc.project_id = p_project_id
AND ppc.project_party_id = ppp.project_party_id
AND ppp.resource_source_id = c_party_id
AND ppp.resource_type_id = 112;
SELECT role_party_class
FROM pa_project_role_types_b /* Bug#2788814-Changed pa_project_role_types_vl to pa_project_role_types_b */
WHERE project_role_id = c_project_role_id;
UPDATE pa_project_customers
SET project_party_id = null
WHERE project_id = p_project_id
AND customer_id = l_customer_id;
PA_PROJECT_PARTIES_PUB.DELETE_PROJECT_PARTY
( p_init_msg_list => p_init_msg_list
,p_validate_only => p_validate_only
,p_validation_level => p_validation_level
,p_debug_mode => p_debug_mode
,p_calling_module => p_calling_module
,p_record_version_number => l_record_version_number
,p_project_id => p_project_id
,p_project_party_id => l_project_party_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
UPDATE pa_project_customers
SET project_party_id = l_new_project_party_id
WHERE project_id = p_project_id
AND customer_id = l_customer_id;
SELECT project_status_code
,project_type
,start_date
,closed_date
,public_sector_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,pm_product_code
,segment1 -- 3671408 added column to retrieve project number value
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT project_status_code
FROM pa_projects_all
WHERE project_id = c_project_id;
SELECT pps.project_system_status_code
FROM pa_project_statuses pps
WHERE pps.project_status_code = nvl(c_status_code,' ');
SELECT pa_obj_status_changes_s.NEXTVAL INTO l_obj_status_change_id
FROM dual;
pa_debug.debug('Calling PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW');
--For inserting status change comment into the status history table
PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW
( X_ROWID => l_rowid,
X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
X_OBJECT_TYPE => 'PA_PROJECTS',
X_OBJECT_ID => p_project_id,
X_STATUS_TYPE => 'PROJECT',
X_NEW_PROJECT_STATUS_CODE => p_new_status_code,
X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sys_status,
X_OLD_PROJECT_STATUS_CODE => l_old_project_status,
X_OLD_PROJECT_SYSTEM_STATUS_CO => l_old_sys_status,
X_CHANGE_COMMENT => p_comment,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_CREATED_BY => fnd_global.user_id,
X_CREATION_DATE => sysdate,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATE_LOGIN => fnd_global.user_id );
pa_debug.debug('Updated PA_PROJECTS_ALL for status history');
SELECT wf_status_code INTO l_wf_status_code
FROM PA_PROJECTS_ALL
WHERE project_id = p_project_id;
UPDATE pa_projects_all
SET wf_status_code = NULL
,project_status_code = p_new_status_code
,closed_date = sysdate
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE project_id = p_project_id;
UPDATE pa_projects_all
SET wf_status_code = NULL
,project_status_code = p_new_status_code --Added for bug 3818322
/*4364878 : Included update of who columns also*/
,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE project_id = p_project_id;
UPDATE pa_projects_all
SET wf_status_code = 'IN_ROUTE'
,project_status_code = p_new_status_code
,closed_date = sysdate
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE project_id = p_project_id;
UPDATE pa_projects_all
SET wf_status_code = 'IN_ROUTE'
,project_status_code = p_new_status_code --Added for bug 3818322
/*4364878 : Included update of who columns also*/
,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE project_id = p_project_id;
UPDATE pa_projects_all
SET project_status_code = p_new_status_code
,closed_date = sysdate
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE project_id = p_project_id ;
UPDATE pa_projects_all
SET project_status_code = p_new_status_code
/*4364878 : Included update of who columns also*/
,closed_date = to_date(null) --For any status other than Closed,this field should be NULL
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE project_id = p_project_id ;