[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TYPE
FROM pa_override_fields_v pof
WHERE pof.pa_field_name = 'CUSTOMER_NAME'
AND EXISTS(SELECT 'x' from pa_projects_all PP
-- replaced c_project_id with p_orig_project_id in where clause for Bug 5478390
WHERE PP.project_id = p_orig_project_id
AND nvl(PP.created_from_project_id,p_orig_project_id) =
POF.pa_source_template_id);
SELECT start_date, completion_date, enable_top_task_customer_flag
FROM pa_projects_all
WHERE project_id = c_project_id;
select meaning
from pa_project_types pt
, pa_lookups lps
, pa_projects pp
where pt.project_type = pp.project_type
and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
and lps.lookup_code(+) = pt.project_type_class_code
and pp.project_id=p_orig_project_id;
Select project_currency_code, project_type_class_code
From pa_projects_all pa, pa_project_types_all pt
Where pa.project_id = c_project_id
And pa.project_type = pt.project_type
And nvl(pa.org_id,-99) = nvl(pt.org_id,-99);
SELECT TYPE
FROM pa_override_fields_v pof
WHERE pof.pa_field_name = 'CUSTOMER_NAME'
AND EXISTS(SELECT 'x' from pa_projects_all PP
WHERE PP.project_id = c_project_id
AND PP.created_from_project_id =
POF.pa_source_template_id);*/
SELECT default_rate_type
FROM pa_implementations;
SELECT PARTY_ID
FROM PA_CUSTOMERS_V
WHERE CUSTOMER_ID = p_customer_id
AND PARTY_TYPE = 'ORGANIZATION';
SELECT
nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
FROM
pa_projects_all
WHERE project_id = c_project_id ;
UPDATE PA_PROJECT_CUSTOMERS
SET project_party_id = l_project_party_id
WHERE project_id = p_project_id
AND customer_id = p_customer_id;
PROCEDURE UPDATE_PROJECT_BASIC_INFO
(
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 ,
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 ,
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
-- Dates 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_return_status VARCHAR2(1);
SELECT meaning
FROM pa_lookups
WHERE lookup_type = 'TRANSLATION'
AND lookup_code = 'MANUAL';
SELECT meaning
FROM pa_lookups
WHERE lookup_type = 'TRANSLATION'
AND lookup_code = 'MASS_UPDATE_BATCH_DESC';
SELECT *
FROM pa_projects p
WHERE p.project_id = p_project_id;
SELECT carrying_out_organization_id
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT ppt.project_type_id
FROM pa_projects_all ppa, pa_project_types ppt
WHERE ppa.project_id = p_project_id
AND ppa.project_type = ppt.project_type;
SELECT template_flag
FROM pa_projects_all
WHERE project_id = p_project_id;
SAVEPOINT update_project_basic_info;
pa_debug.debug('UPDATE_PROJECT_BASIC_INFO: Calling validate_project_details.');
pa_debug.debug('Update_project_basic_info PVT: Locking record');
SELECT 'x' INTO l_dummy
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number;
pa_debug.debug('Update_project_basic_info PVT: Calling validate project info API');
p_action => 'UPDATE',
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 => p_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_record_version_number => p_record_version_number,
-- anlee
-- Dates changes
p_target_start_date => p_target_start_date,
p_target_finish_date => p_target_finish_date,
-- End of changes
-- anlee
-- Project Long Name changes
p_long_name => p_long_name );
pa_debug.debug('Update_project_basic_info PVT: upating pa_projects table');
UPDATE PA_PROJECTS
SET name = l_project_name,
segment1 = l_project_number,
project_type = l_project_type,
description = l_description,
project_status_code = l_project_status_code,
public_sector_flag = l_public_sector_flag,
carrying_out_organization_id = l_carrying_out_organization_id,
start_date = l_start_date,
completion_date = l_completion_date,
-- location_id = p_location_id,
-- calendar_id = p_calendar_id,
priority_code = l_priority_code,
attribute_category = l_attribute_category,
attribute1 = l_attribute1,
attribute2 = l_attribute2,
attribute3 = l_attribute3,
attribute4 = l_attribute4,
attribute5 = l_attribute5,
attribute6 = l_attribute6,
attribute7 = l_attribute7,
attribute8 = l_attribute8,
attribute9 = l_attribute9,
attribute10 = l_attribute10,
record_version_number = record_version_number + 1,
-- anlee
-- Dates changes
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
target_start_date = l_target_start_date,
target_finish_date = l_target_finish_date,
-- End of changes
-- anlee Project Long Name changes
long_name = p_long_name,
-- End of changes
funding_approval_status_code = p_funding_approval_status, -- 4055319
security_level = p_security_level
WHERE project_id = p_project_id;
p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
p_error_text => SUBSTRB(SQLERRM,1,240));
PA_PROJECT_CTX_SEARCH_PVT.UPDATE_ROW (
p_project_id => p_project_id
,p_template_flag => 'N'
,p_project_name => l_project_name
,p_project_number => l_project_number
,p_project_long_name => p_long_name
,p_project_description => l_description
,x_return_status => l_return_status );
PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_project_id
,p_old_classification_id => l_old_project_type_id
,p_new_classification_id => l_new_project_type_id
,p_classification_type => 'PROJECT_TYPE'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_MU_BATCHES_V_PKG.INSERT_ROW(
x_rowid => l_row_id
,x_batch_id => l_batch_id
,x_creation_date => l_project_rec.last_update_date
,x_created_by => l_project_rec.last_updated_by
,x_last_updated_by => l_project_rec.last_updated_by
,x_last_update_date => l_project_rec.last_update_date
,x_last_update_login => l_project_rec.last_update_login
,x_batch_name => l_batch_name
,x_batch_status_code => 'C'
,x_description => l_batch_description
,x_project_attribute => 'ORGANIZATION'
,x_effective_date => trunc(sysdate)
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL );
UPDATE PA_MASS_UPDATE_BATCHES
SET batch_name = substr(l_batch_name,1,20)||'-'||to_char(l_batch_id)
WHERE rowid = l_row_id;
PA_MU_DETAILS_V_PKG.INSERT_ROW(
x_rowid => l_row_id
,x_line_id => l_line_id
,x_batch_id => l_batch_id
,x_creation_date => l_project_rec.last_update_date
,x_created_by => l_project_rec.last_updated_by
,x_last_updated_by => l_project_rec.last_updated_by
,x_last_update_date => l_project_rec.last_update_date
,x_last_update_login => l_project_rec.last_update_login
,x_project_id => p_project_id
,x_task_id => NULL
,x_old_attribute_value => to_char(l_old_organization_id)
,x_new_attribute_value => to_char(p_carrying_out_organization_id)
,x_update_flag => 'Y'
,x_recalculate_flag => 'Y' );
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_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_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_return_status VARCHAR2(250);
SELECT name, segment1, description, split_cost_from_workplan_flag, calendar_id
--,sys_program_flag,allow_multi_program_rollup
FROM PA_PROJECTS_ALL
WHERE project_id = p_project_id;
SAVEPOINT update_project_additional_info;
pa_debug.debug('Update_project_additional_info PVT: locking record');
SELECT 'x' INTO l_dummy
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number;
pa_debug.debug('Update_project_additional_info PVT: Calling validate project info API');
p_action => 'UPDATE',
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_project_id => p_project_id,
p_calendar_id => p_calendar_id,
p_work_type_id => p_work_type_id,
p_role_list_id => p_role_list_id,
p_cost_job_group_id => p_cost_job_group_id,
p_bill_job_group_id => p_bill_job_group_id,
p_record_version_number => p_record_version_number);
pa_debug.debug('Update_project_additional_info PVT: updating pa_projects table');
PA_PROJ_TASK_STRUC_PUB.create_delete_workplan_struc(
p_calling_module => p_calling_module
,p_project_id => p_project_id
,p_project_number => l_project_number
,p_project_name => l_project_name
,p_project_description => l_project_description
,p_split_workplan => l_split_cost_from_wokplan_flag
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
PA_PROJECT_STRUCTURE_PVT1.update_wp_calendar(
p_project_id => p_project_id
,p_calendar_id => l_calendar_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
UPDATE pa_projects
SET record_version_number = record_version_number +1,
attribute_category = l_attribute_category,
attribute1 = l_attribute1,
attribute2 = l_attribute2,
attribute3 = l_attribute3,
attribute4 = l_attribute4,
attribute5 = l_attribute5,
attribute6 = l_attribute6,
attribute7 = l_attribute7,
attribute8 = l_attribute8,
attribute9 = l_attribute9,
attribute10 = l_attribute10,
calendar_id = l_calendar_id ,
work_type_id = l_work_type_id ,
role_list_id = l_role_list_id ,
split_cost_from_workplan_flag = l_split_cost_from_wokplan_flag,
split_cost_from_bill_flag = l_split_cost_from_bill_flag,
cost_job_group_id = l_cost_job_group_id ,
bill_job_group_id = l_bill_job_group_id ,
-- Added WHO columns for Bug 7286976
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
--,
-- sys_program_flag = l_sys_program_flag,
-- allow_multi_program_rollup = l_allow_multi_program_rollup
WHERE project_id = p_project_id;
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;
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_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_project_value IN NUMBER := FND_API.G_MISS_NUM ,
p_expected_approval_date IN DATE ,
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_msg_count NUMBER;
SAVEPOINT update_project_pipeline_info;
pa_debug.debug('Update_project_pipeline_info PVT: locking record');
SELECT 'x' INTO l_dummy
FROM pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number;
pa_debug.debug('Update_project_pipeline_info PVT: Calling validate project API');
p_action => 'UPDATE',
p_debug_mode => p_debug_mode,
p_max_msg_count => p_max_msg_count,
p_project_id => p_project_id,
p_probability_member_id => p_probability_member_id,
p_project_value => p_project_value,
p_expected_approval_date => p_expected_approval_date,
p_record_version_number => p_record_version_number);
pa_debug.debug('Update_project_pipeline_info PVT: update pa_projects table');
UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
SET record_version_number = record_version_number +1 ,
probability_member_id = l_probability_member_id,
project_value = l_project_value,
expected_approval_date = l_expected_approval_date
WHERE project_id = p_project_id;
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;
p_action => 'INSERT',
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 => l_code_percentage);
pa_debug.debug('Create classification PVT: Calling Table handler to insert new classification record ');
pa_project_classes_pkg.insert_row
( l_row_id
,p_object_id
,p_object_type
,p_class_category
,p_class_code
,l_code_percentage
,l_attribute_category
,l_attribute1
,l_attribute2
,l_attribute3
,l_attribute4
,l_attribute5
,l_attribute6
,l_attribute7
,l_attribute8
,l_attribute9
,l_attribute10
,l_attribute11
,l_attribute12
,l_attribute13
,l_attribute14
,l_attribute15
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id );
PROCEDURE UPDATE_CLASSIFICATIONS
(
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_code_percentage NUMBER;
SELECT cat.class_category_id, code.class_code_id
FROM PA_PROJECT_CLASSES ppc, PA_CLASS_CATEGORIES cat, PA_CLASS_CODES code
WHERE ppc.rowid = p_rowid
AND ppc.class_category = cat.class_category
AND ppc.class_category = code.class_category
AND ppc.class_code = code.class_code;
pa_debug.debug('PA_PROJECTS_MAINT_PVT.Update_Classifications BEGIN');
savepoint update_classifications_pvt;
SELECT 'x' INTO l_dummy
FROM pa_project_classes
WHERE rowid = p_rowid
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_project_classes
WHERE rowid = p_rowid
AND record_version_number = p_record_version_number;
p_action => 'UPDATE',
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 => l_code_percentage,
p_rowid => p_rowid);
pa_project_classes_pkg.update_row
( X_rowid => p_rowid
,X_object_id => p_object_id
,X_object_type => p_object_type
,X_class_category => p_class_category
,X_class_code => p_class_code
,X_code_percentage => l_code_percentage
,X_attribute_category => l_attribute_category
,X_attribute1 => l_attribute1
,X_attribute2 => l_attribute2
,X_attribute3 => l_attribute3
,X_attribute4 => l_attribute4
,X_attribute5 => l_attribute5
,X_attribute6 => l_attribute6
,X_attribute7 => l_attribute7
,X_attribute8 => l_attribute8
,X_attribute9 => l_attribute9
,X_attribute10 => l_attribute10
,X_attribute11 => l_attribute11
,X_attribute12 => l_attribute12
,X_attribute13 => l_attribute13
,X_attribute14 => l_attribute14
,X_attribute15 => l_attribute15
,X_Last_Update_Date => sysdate
,X_Last_Updated_By => fnd_global.user_id
,X_Creation_Date => sysdate
,X_Created_By => fnd_global.user_id
,X_Last_Update_Login => fnd_global.login_id
,X_record_version_number => p_record_version_number);
PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_object_id
,p_old_classification_id => l_old_category_id
,p_new_classification_id => l_new_category_id
,p_classification_type => 'CLASS_CATEGORY'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_object_id
,p_old_classification_id => l_old_code_id
,p_new_classification_id => l_new_code_id
,p_classification_type => 'CLASS_CODE'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
pa_debug.debug('PA_PROJECTS_MAINT_PVT.Update_Classifications END');
rollback to update_classifications_pvt;
rollback to update_classifications_pvt;
p_procedure_name => 'Update_Classifications',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_CLASSIFICATIONS;
p_action IN VARCHAR2 := 'INSERT' ,
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 OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
p_rowid IN VARCHAR2 := FND_API.G_MISS_CHAR)
IS
l_return_status VARCHAR2(250);
SELECT rowid
FROM pa_project_classes
WHERE object_id = c_object_id
AND object_type = c_object_type
AND class_category = c_class_category
AND class_code = c_class_code;
SELECT class_category
FROM PA_PROJECT_CLASSES
WHERE rowid = c_rowid;
if p_action = 'INSERT' then
--dbms_output.put_line('PA_PROJECTS_MAINT_UTILS.CHECK_CLASS_CATG_CAN_OVERRIDE ');
SELECT ppta.project_type_id
INTO l_object_type_id
FROM PA_PROJECT_TYPES_ALL ppta,
PA_PROJECTS_ALL ppa
WHERE ppa.project_id = p_object_id
AND ppa.project_type = ppta.project_type
-- AND NVL(ppa.org_id, -99) = NVL(ppta.org_id, -99); MOAC Changes: Bug 4363092 - removed nvl
ELSIF p_action = 'UPDATE' then
if (p_debug_mode = 'Y') then
pa_debug.debug('Validate classification PVT: Calling API to check if category is valid');
SELECT ppta.project_type_id
INTO l_object_type_id
FROM PA_PROJECT_TYPES_ALL ppta,
PA_PROJECTS_ALL ppa
WHERE ppa.project_id = p_object_id
AND ppa.project_type = ppta.project_type
-- AND NVL(ppa.org_id, -99) = NVL(ppta.org_id, -99); MOAC Changes: Bug 4363092 - removed nvl
ELSIF p_action = 'DELETE' then
if (p_debug_mode = 'Y') then
pa_debug.debug('Validate classification PVT: Calling API to check if the catg code entered can be deleted');
PA_PROJECTS_MAINT_UTILS.CHECK_CLASS_CATG_CAN_DELETE
(p_object_id,
p_object_type,
p_class_category,
l_return_status,
l_error_msg_code);
PROCEDURE DELETE_CLASSIFICATIONS
(
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_dummy VARCHAR2(1);
l_delete_flag VARCHAR2(1) := 'N';
SELECT class_category_id
FROM PA_CLASS_CATEGORIES
WHERE class_category = p_class_category;
SELECT class_code_id
FROM PA_CLASS_CODES
WHERE class_category = p_class_category
AND class_code = p_class_code;
SAVEPOINT delete_classifications_pvt;
pa_debug.debug('Delete classification PVT: locking record for deletion');
SELECT rowid INTO l_row_id
FROM pa_project_classes
WHERE object_id = p_object_id
AND object_type = p_object_type
AND class_category = p_class_category
and class_code = p_class_code
and record_version_number = p_record_version_number
FOR UPDATE OF class_code NOWAIT;
SELECT rowid INTO l_row_id
FROM pa_project_classes
WHERE object_id = p_object_id
AND object_type = p_object_type
AND class_category = p_class_category
and class_code = p_class_code
and record_version_number = p_record_version_number;
pa_debug.debug('Delete classification PVT: Calling API to validate classfications');
p_action => 'DELETE',
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 => l_code_percentage,
p_rowid => l_row_id);
pa_debug.debug('Delete classification PVT: Calling table handler to delete classfications record');
pa_project_classes_pkg.delete_row
( l_row_id);
PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_object_id
,p_old_classification_id => l_category_id
,p_classification_type => 'CLASS_CATEGORY'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
p_validate_only => FND_API.G_FALSE
,p_project_id => p_object_id
,p_old_classification_id => l_code_id
,p_classification_type => 'CLASS_CODE'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
ROLLBACK TO delete_classifications_pvt;
p_procedure_name => 'DELETE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO delete_classifications_pvt;
p_procedure_name => 'DELETE_CLASSIFICATIONS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_CLASSIFICATIONS;
p_action IN VARCHAR2 := 'UPDATE' ,
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 := FND_API.G_MISS_CHAR ,
p_project_number IN VARCHAR2 := FND_API.G_MISS_CHAR ,
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 := FND_API.G_MISS_CHAR ,
p_public_sector_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
p_carrying_out_organization_id IN NUMBER := FND_API.G_MISS_NUM ,
p_start_date IN DATE := FND_API.G_MISS_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_calendar_id IN NUMBER := FND_API.G_MISS_NUM ,
p_work_type_id IN NUMBER := FND_API.G_MISS_NUM ,
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_probability_member_id IN NUMBER := FND_API.G_MISS_NUM ,
p_project_value IN NUMBER := FND_API.G_MISS_NUM ,
p_expected_approval_date IN DATE := FND_API.G_MISS_DATE ,
p_record_version_number IN NUMBER ,
-- anlee
-- Dates 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
-- anlee
-- Project Long Name changes
p_long_name IN VARCHAR2 DEFAULT NULL )
-- end of changes
IS
l_dummy VARCHAR2(1) := 'N';
l_update_start_date_flag VARCHAR2(1);
l_update_end_date_flag VARCHAR2(1);
SELECT 'x'
FROM pa_projects_all
WHERE name = p_project_name;
SELECT 'x'
FROM pa_projects_all
WHERE segment1 = p_project_number;
SELECT *
FROM pa_projects_all p -- Bug#3807805 : Modifed pa_projects to pa_projects_all
WHERE p.project_id = p_project_id
FOR UPDATE OF p.record_version_number;
Select location_id
from pa_locations
where country_code = x_country_code
and region = x_state_region
and city = x_city;
select territory_code
from fnd_territories_tl
where territory_short_name = x_country_name;
select probability_percentage
from pa_probability_members
where probability_member_id = c_probability_member_id;
select *
from pa_project_types_all -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
where project_type = c_project_type
and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
Select unassigned_time
from pa_project_types_all -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
where project_type = c_project_type
and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
Select project_type_class_code
from pa_project_types_all -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
where project_type = c_project_type
and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
select billable_capitalizable_flag
from pa_work_types_vl
where work_type_id = c_work_type_id;
select distribution_rule
from pa_project_type_distributions
where project_type = c_project_type
and default_flag = 'Y';
select min(start_date) task_start_date
from pa_tasks
where project_id = p_project_id
and start_date is not null;
select max(completion_date) task_completion_date
from pa_tasks
where project_id = p_project_id
and completion_date is not null;
l_delete_project_allowed VARCHAR2(1) := 'Y';
l_update_proj_num_allowed VARCHAR2(1) := 'Y';
l_update_proj_name_allowed VARCHAR2(1) := 'Y';
l_update_proj_desc_allowed VARCHAR2(1) := 'Y';
l_update_proj_dates_allowed VARCHAR2(1) := 'Y';
l_update_proj_status_allowed VARCHAR2(1) := 'Y';
l_update_proj_manager_allowed VARCHAR2(1) := 'Y';
l_update_proj_org_allowed VARCHAR2(1) := 'Y';
l_delete_task_allowed VARCHAR2(1) := 'Y';
l_update_task_num_allowed VARCHAR2(1) := 'Y';
l_update_task_name_allowed VARCHAR2(1) := 'Y';
l_update_task_dates_allowed VARCHAR2(1) := 'Y';
l_update_task_desc_allowed VARCHAR2(1) := 'Y';
l_update_parent_task_allowed VARCHAR2(1) := 'Y';
l_update_task_org_allowed VARCHAR2(1) := 'Y';
SELECT pm_product_code
FROM PA_PROJECTS_ALL
WHERE project_id = p_project_id;
select org_id into PA_PROJECT_REQUEST_PVT.G_ORG_ID from pa_projects where project_id = p_project_id;--Bug 8882288
,p_delete_project_allowed => l_delete_project_allowed
,p_update_proj_num_allowed => l_update_proj_num_allowed
,p_update_proj_name_allowed => l_update_proj_name_allowed
,p_update_proj_desc_allowed => l_update_proj_desc_allowed
,p_update_proj_dates_allowed => l_update_proj_dates_allowed
,p_update_proj_status_allowed => l_update_proj_status_allowed
,p_update_proj_manager_allowed => l_update_proj_manager_allowed
,p_update_proj_org_allowed => l_update_proj_org_allowed
,p_add_task_allowed => l_add_task_allowed
,p_delete_task_allowed => l_delete_task_allowed
,p_update_task_num_allowed => l_update_task_num_allowed
,p_update_task_name_allowed => l_update_task_name_allowed
,p_update_task_dates_allowed => l_update_task_dates_allowed
,p_update_task_desc_allowed => l_update_task_desc_allowed
,p_update_parent_task_allowed => l_update_parent_task_allowed
,p_update_task_org_allowed => l_update_task_org_allowed
,p_error_code => l_err_code
,p_error_stack => l_err_stack
,p_error_stage => l_err_stage );
IF l_update_proj_num_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_NUM_ERR');
END IF; -- l_update_proj_num_allowed
IF l_update_proj_name_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_NAME_ERR');
END IF; -- l_update_proj_name_allowed
if l_update_proj_desc_allowed = 'N' then
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_DESC_ERR');
if l_update_proj_desc_allowed = 'N' then
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_DESC_ERR');
CURSOR cur_job_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
WHERE bill_rate_sch_id = l_project_type_rec.job_bill_rate_schedule_id ;
CURSOR cur_emp_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
WHERE bill_rate_sch_id = l_project_type_rec.emp_bill_rate_schedule_id ;
CURSOR cur_nl_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
WHERE bill_rate_sch_id = l_project_type_rec.non_lab_std_bill_rt_sch_id ;
CURSOR cur_impl IS SELECT default_rate_type FROM pa_implementations;
UPDATE pa_project_customers
SET inv_rate_type = x_default_rate_type
WHERE project_id = p_project_id;
UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
SET role_list_id = l_project_type_rec.role_list_id,
work_type_id = l_project_type_rec.work_type_id,
cost_ind_rate_sch_id = l_project_type_rec.cost_ind_rate_sch_id,
labor_sch_type = l_project_type_rec.labor_sch_type,
labor_bill_rate_org_id = l_project_type_rec.labor_bill_rate_org_id,
labor_std_bill_rate_schdl = l_project_type_rec.labor_std_bill_rate_schdl,
non_labor_sch_type = l_project_type_rec.non_labor_sch_type,
non_labor_bill_rate_org_id = l_project_type_rec.non_labor_bill_rate_org_id,
non_labor_std_bill_rate_schdl = l_project_type_rec.non_labor_std_bill_rate_schdl,
rev_ind_rate_sch_id = l_project_type_rec.rev_ind_rate_sch_id,
inv_ind_rate_sch_id = l_project_type_rec.inv_ind_rate_sch_id,
labor_invoice_format_id = l_project_type_rec.labor_invoice_format_id,
non_labor_invoice_format_id = l_project_type_rec.non_labor_invoice_format_id,
billing_offset = l_project_type_rec.billing_offset,
billing_cycle_id = l_project_type_rec.billing_cycle_id,
bill_job_group_id = l_project_type_rec.bill_job_group_id,
cost_job_group_id = l_project_type_rec.cost_job_group_id,
emp_bill_rate_schedule_id = l_project_type_rec.emp_bill_rate_schedule_id,
job_bill_rate_schedule_id = l_project_type_rec.job_bill_rate_schedule_id,
project_status_code = l_project_type_rec.def_start_proj_status_code,
--bug 3068781
-- multi_currency_billing_flag = decode(l_project_type_rec.cc_prvdr_flag, 'Y', 'N', multi_currency_billing_flag),
multi_currency_billing_flag = NVL( l_multi_currency_billing_flag,multi_currency_billing_flag ),
PROJFUNC_BIL_RATE_TYPE = l_PROJFUNC_BIL_RATE_TYPE,
PROJECT_BIL_RATE_TYPE = l_PROJECT_BIL_RATE_TYPE,
FUNDING_RATE_TYPE = l_FUNDING_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE_CODE = l_PROJFUNC_BIL_RATE_DATE_CODE,
PROJECT_BIL_RATE_DATE_CODE = l_PROJECT_BIL_RATE_DATE_CODE,
FUNDING_RATE_DATE_CODE = l_FUNDING_RATE_DATE_CODE,
BTC_COST_BASE_REV_CODE = l_BTC_COST_BASE_REV_CODE,
--end bug 3068781
cc_process_labor_flag = 'N',
cc_process_nl_flag = 'N',
nl_tp_schedule_id = null,
labor_tp_schedule_id = null,
nl_tp_fixed_date = null,
labor_tp_fixed_date = null,
-- anlee
-- patchset K changes
revaluate_funding_flag = l_project_type_rec.revaluate_funding_flag,
include_gains_losses_flag = l_project_type_rec.include_gains_losses_flag,
-- End of changes
--PA L Changes 2872708
asset_allocation_method = l_project_type_rec.asset_allocation_method,
capital_event_processing = l_project_type_rec.capital_event_processing,
CINT_RATE_SCH_ID = l_project_type_rec.CINT_RATE_SCH_ID,
--federal changes by sunkalya. Bug#5511353.
date_eff_funds_consumption = nvl(l_project_type_rec.date_eff_funds_consumption,'N')
--federal changes by sunkalya. Bug#5511353.
--End PA L Changes 2872708
WHERE project_id = p_project_id;
UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
SET role_list_id = l_project_type_rec.role_list_id,
work_type_id = l_project_type_rec.work_type_id,
-- service_type_code = l_project_type_rec.service_type_code,
cost_ind_rate_sch_id = l_project_type_rec.cost_ind_rate_sch_id,
labor_sch_type = l_project_type_rec.labor_sch_type,
labor_bill_rate_org_id = l_project_type_rec.labor_bill_rate_org_id,
labor_std_bill_rate_schdl = l_project_type_rec.labor_std_bill_rate_schdl,
non_labor_sch_type = l_project_type_rec.non_labor_sch_type,
non_labor_bill_rate_org_id = l_project_type_rec.non_labor_bill_rate_org_id,
non_labor_std_bill_rate_schdl = l_project_type_rec.non_labor_std_bill_rate_schdl,
rev_ind_rate_sch_id = l_project_type_rec.rev_ind_rate_sch_id,
inv_ind_rate_sch_id = l_project_type_rec.inv_ind_rate_sch_id,
labor_invoice_format_id = l_project_type_rec.labor_invoice_format_id,
non_labor_invoice_format_id = l_project_type_rec.non_labor_invoice_format_id,
billing_offset = l_project_type_rec.billing_offset,
billing_cycle_id = l_project_type_rec.billing_cycle_id,
bill_job_group_id = l_project_type_rec.bill_job_group_id,
cost_job_group_id = l_project_type_rec.cost_job_group_id,
emp_bill_rate_schedule_id = l_project_type_rec.emp_bill_rate_schedule_id,
job_bill_rate_schedule_id = l_project_type_rec.job_bill_rate_schedule_id,
project_status_code = l_project_type_rec.def_start_proj_status_code,
--bug 3068781
-- multi_currency_billing_flag = decode(l_project_type_rec.cc_prvdr_flag, 'Y', 'N', multi_currency_billing_flag),
multi_currency_billing_flag = NVL( l_multi_currency_billing_flag,multi_currency_billing_flag ),
PROJFUNC_BIL_RATE_TYPE = l_PROJFUNC_BIL_RATE_TYPE,
PROJECT_BIL_RATE_TYPE = l_PROJECT_BIL_RATE_TYPE,
FUNDING_RATE_TYPE = l_FUNDING_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE_CODE = l_PROJFUNC_BIL_RATE_DATE_CODE,
PROJECT_BIL_RATE_DATE_CODE = l_PROJECT_BIL_RATE_DATE_CODE,
FUNDING_RATE_DATE_CODE = l_FUNDING_RATE_DATE_CODE,
BTC_COST_BASE_REV_CODE = l_BTC_COST_BASE_REV_CODE,
--bug end 3068781
-- anlee
-- patchset K changes
revaluate_funding_flag = l_project_type_rec.revaluate_funding_flag,
include_gains_losses_flag = l_project_type_rec.include_gains_losses_flag,
-- End of changes
--PA L Changes 2872708
asset_allocation_method = l_project_type_rec.asset_allocation_method,
capital_event_processing = l_project_type_rec.capital_event_processing,
CINT_RATE_SCH_ID = l_project_type_rec.CINT_RATE_SCH_ID,
--End PA L Changes 2872708
--federal changes by sunkalya. Bug#5511353.
date_eff_funds_consumption = nvl(l_project_type_rec.date_eff_funds_consumption,'N')
--federal changes by sunkalya. Bug#5511353.
WHERE project_id = p_project_id;
DELETE FROM PA_BUDGETARY_CONTROL_OPTIONS
WHERE PROJECT_ID = P_PROJECT_ID;
UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
SET cc_tax_task_id = NULL
WHERE project_id = p_project_id;
UPDATE pa_project_customers
SET
CUSTOMER_BILL_SPLIT = NULL
WHERE
PROJECT_ID = p_project_id;
UPDATE pa_project_customers SET customer_bill_split = 100
WHERE customer_id = hghst_ctr_cust_id AND project_id = p_project_id;
UPDATE pa_project_customers SET customer_bill_split = 0
WHERE customer_id <> hghst_ctr_cust_id AND project_id = p_project_id;
UPDATE pa_tasks
SET work_type_id = l_project_type_rec.work_type_id,
billable_flag = decode(NVL(PA_INSTALL.is_prm_licensed(),'N'), 'Y', l_billable_capitalizable_flag, 'N', billable_flag), /* added decode for bug#3481807 */
emp_bill_rate_schedule_id = l_project_type_rec.emp_bill_rate_schedule_id,
job_bill_rate_schedule_id = l_project_type_rec.job_bill_rate_schedule_id,
labor_sch_type = l_project_type_rec.labor_sch_type,
service_type_code = l_project_type_rec.service_type_code,
cost_ind_rate_sch_id = l_project_type_rec.cost_ind_rate_sch_id,
labor_bill_rate_org_id = l_project_type_rec.labor_bill_rate_org_id,
labor_std_bill_rate_schdl = l_project_type_rec.labor_std_bill_rate_schdl,
non_labor_sch_type = l_project_type_rec.non_labor_sch_type,
non_labor_bill_rate_org_id = l_project_type_rec.non_labor_bill_rate_org_id,
non_labor_std_bill_rate_schdl = l_project_type_rec.non_labor_std_bill_rate_schdl,
rev_ind_rate_sch_id = l_project_type_rec.rev_ind_rate_sch_id,
inv_ind_rate_sch_id = l_project_type_rec.inv_ind_rate_sch_id
WHERE project_id = p_project_id;
UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
SET distribution_rule = l_distribution_rule,
revenue_accrual_method = substr(l_distribution_rule,1,instr(l_distribution_rule,'/')-1), --Added for bug 8655611
invoice_method = substr(l_distribution_rule,instr(l_distribution_rule,'/')+1) --Added for bug 8655611
WHERE project_id = p_project_id;
IF l_update_proj_org_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_ORG_ERR');
if fnd_function.test('PA_PAXPREPR_UPDATE_ORG') then
function_flag := 'Y'; -- function returns TRUE
,X_insert_update_mode => NULL
,X_calling_module => 'UPDATE_PROJECT'
,X_project_id => p_project_id
,X_task_id => NULL
,X_old_value => To_char(l_project_old_rec.carrying_out_organization_id)
,X_new_value => To_char(p_carrying_out_organization_id)
,X_project_type => l_project_old_rec.project_type
,X_project_start_date => l_project_old_rec.start_date
,X_project_end_date => l_project_old_rec.completion_date
,X_public_sector_flag => l_project_old_rec.public_sector_flag
,X_task_manager_person_id => NULL
,X_Service_type => NULL
,X_task_start_date => NULL
,X_task_end_date => NULL
,X_entered_by_user_id => FND_GLOBAL.USER_ID
,X_attribute_category => l_project_old_rec.attribute_category
,X_attribute1 => l_project_old_rec.attribute1
,X_attribute2 => l_project_old_rec.attribute2
,X_attribute3 => l_project_old_rec.attribute3
,X_attribute4 => l_project_old_rec.attribute4
,X_attribute5 => l_project_old_rec.attribute5
,X_attribute6 => l_project_old_rec.attribute6
,X_attribute7 => l_project_old_rec.attribute7
,X_attribute8 => l_project_old_rec.attribute8
,X_attribute9 => l_project_old_rec.attribute9
,X_attribute10 => l_project_old_rec.attribute10
,X_pm_product_code => l_project_old_rec.pm_product_code
,X_pm_project_reference => l_project_old_rec.pm_project_reference
,X_pm_task_reference => NULL
,X_functional_security_flag => function_flag
,x_warnings_only_flag => l_warnings_only_flag --bug3134205
,X_err_code => l_err_code
,X_err_stage => l_err_stage
,X_err_stack => l_err_stack );
select meaning
into t_project_type_class_code
from pa_project_types_all pt -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
, pa_lookups lps
where pt.project_type = p_project_type
and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
and lps.lookup_code(+) = pt.project_type_class_code
and pt.org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
pa_locations_pkg.INSERT_ROW(
p_CITY => x_city_name,
p_REGION => x_region_name,
p_COUNTRY_CODE => x_country_code,
p_CREATION_DATE => sysdate,
p_CREATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_ROWID => x_rowid,
X_LOCATION_ID => x_location_id);
END IF; -- l_update_proj_org_allowed
,p_update_start_date_flag => l_update_start_date_flag
,p_update_end_date_flag => l_update_end_date_flag
,p_return_status => l_return_status );
IF l_update_proj_dates_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_DATES_ERR');
END IF; -- l_update_proj_dates_allowed
IF l_update_proj_dates_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_DATES_ERR');
END IF; -- l_update_proj_dates_allowed
IF l_update_proj_status_allowed = 'N' THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_PM_PROJ_STATUS_ERR');
UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
SET closed_date = trunc(sysdate)
WHERE project_id = p_project_id;
END IF; -- l_update_proj_status_allowed
(x_calling_module => 'UPDATE_PROJECT'
,X_project_id => l_project_old_rec.project_id
,X_old_proj_status_code => l_project_old_rec.project_status_code
,X_new_proj_status_code => p_project_status_code
,X_project_type => l_project_old_rec.project_type
,X_project_start_date => l_project_old_rec.start_date
,X_project_end_date => l_project_old_rec.completion_date
,X_public_sector_flag => l_project_old_rec.public_sector_flag
,X_attribute_category => l_project_old_rec.attribute_category
,X_attribute1 => l_project_old_rec.attribute1
,X_attribute2 => l_project_old_rec.attribute2
,X_attribute3 => l_project_old_rec.attribute3
,X_attribute4 => l_project_old_rec.attribute4
,X_attribute5 => l_project_old_rec.attribute5
,X_attribute6 => l_project_old_rec.attribute6
,X_attribute7 => l_project_old_rec.attribute7
,X_attribute8 => l_project_old_rec.attribute8
,X_attribute9 => l_project_old_rec.attribute9
,X_attribute10 => l_project_old_rec.attribute10
,X_pm_product_code => l_project_old_rec.pm_product_code
,x_init_msg => 'N'
,x_verify_ok_flag => l_verify_ok_flag
,x_wf_enabled_flag => l_wf_enabled_flag
,X_err_stage => l_err_stage
,X_err_stack => l_err_stack
,x_err_msg_count => l_err_msg_count
,x_warnings_only_flag => l_warnings_only_flag );
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 = 'IN_ROUTE',
project_status_code = p_project_status_code
WHERE project_id = p_project_id;
table handler to insert new record');
pa_locations_pkg.INSERT_ROW(
p_CITY => p_city,
p_REGION => p_state_region,
p_COUNTRY_CODE => x_country_code,
p_CREATION_DATE => sysdate,
p_CREATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_ROWID => x_rowid,
X_LOCATION_ID => x_location_id);
update pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
set location_id = x_location_id
where project_id = p_project_id;
PROCEDURE UPDATE_PROJECT_STAFFING_INFO
(
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_exp_org_struct_ver_id IN pa_projects_all.search_org_hier_id%TYPE := FND_API.G_MISS_NUM,
p_search_exp_start_org_id IN pa_projects_all.search_starting_org_id%TYPE := FND_API.G_MISS_NUM,
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_msg_count NUMBER;
SAVEPOINT update_project_staffing_info;
pa_debug.debug('Update_project_staffing_info PVT: locking record');
SELECT 'x' INTO l_dummy
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_projects
WHERE project_id = p_project_id
AND record_version_number = p_record_version_number;
pa_debug.debug('Update_project_staffing_info PVT: update pa_projects table');
UPDATE pa_projects_all
SET record_version_number = record_version_number +1 ,
COMPETENCE_MATCH_WT = p_comp_match_weighting,
AVAILABILITY_MATCH_WT = p_avail_match_weighting,
JOB_LEVEL_MATCH_WT = p_job_level_match_weighting,
ENABLE_AUTOMATED_SEARCH = p_enable_auto_cand_nom_flag,
MIN_CAND_SCORE_REQD_FOR_NOM = p_search_min_candidate_score,
SEARCH_MIN_AVAILABILITY = p_search_min_availability,
SEARCH_ORG_HIER_ID = p_search_exp_org_struct_ver_id,
SEARCH_STARTING_ORG_ID = p_search_exp_start_org_id,
SEARCH_COUNTRY_CODE = p_search_country_code
WHERE project_id = p_project_id;
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;