The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'Y'
From dual
Where exists(SELECT 'XYZ' from pa_proj_elements
WHERE proj_element_id = P_lifecycle_id
AND object_type='PA_STRUCTURES'
AND project_id=c_project_id);
SELECT project_status_code
from pa_project_statuses
WHERE project_status_name = P_phase_status_name
AND status_type = 'PHASE';
PROCEDURE delete_lifecycle (
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 := G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
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) := 'delete_lifecycle';
Select 'Y'
From dual
Where exists(SELECT 'XYZ' from pa_proj_elements
WHERE proj_element_id = P_lifecycle_id
AND object_type= c_object_type
AND project_id=c_project_id
);
Select elem.proj_element_id, elem.record_version_number
From pa_proj_element_versions ever
, pa_proj_elements elem
Where ever.parent_structure_version_id = l_life_elem_ver_id
AND ever.object_type= 'PA_TASKS'
AND ever.project_id=c_project_id
AND ever.proj_element_id = elem.proj_element_id
AND elem.project_id = c_project_id
AND elem.object_type = 'PA_TASKS';
Select ELEMENT_VERSION_ID
From pa_proj_element_versions
Where PROJ_ELEMENT_ID = p_lifecycle_id;
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Entered delete_lifecycle...');
SAVEPOINT LCYL_DELETE_LIFECYCLE_PUB;
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Initialising message stack...');
pa_debug.init_err_stack('PA_LIFECYCLES_PUB.DELETE_LIFECYCLE');
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle :After initialising the stack...');
select 'Y'
into l_project_usage_exists
from SYS.DUAL
where exists( select 'XYZ' from pa_lifecycle_usages
where lifecycle_id=P_lifecycle_id AND
usage_type=c_project_type);
select 'Y'
into l_product_usage_exists
from SYS.DUAL
where exists( select 'XYZ' from pa_lifecycle_usages
where lifecycle_id=P_lifecycle_id AND
usage_type=c_product_type);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Before call to private API check_delete_phase_ok');
pa_lifecycles_pvt.check_delete_lifecycle_ok(
P_api_version => P_api_version ,
p_calling_module => p_calling_module ,
p_debug_mode => p_debug_mode ,
p_max_msg_count => p_max_msg_count ,
P_lifecycle_id => p_lifecycle_id ,
P_lifecycle_version_id => l_life_elem_ver_id ,
X_return_status => l_return_status ,
X_msg_count => l_msg_count ,
X_msg_data => l_msg_data ,
x_del_lifecycle_ok => l_del_lifecycle_ok
);
PA_EGO_WRAPPER_PUB.check_delete_lifecycle_ok(
p_api_version => P_api_version ,
p_lifecycle_id => p_lifecycle_id ,
p_init_msg_list => p_init_msg_list ,
x_delete_ok => l_del_lifecycle_ok ,
x_return_status => l_return_status ,
x_errorcode => l_errorcode ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : calling pa_lifecycles_pvt.delete_lifecycle_phase for each phase');
pa_lifecycles_pub.delete_lifecycle_phase(
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 => p_calling_module ,
p_debug_mode => p_debug_mode ,
p_max_msg_count => p_max_msg_count ,
P_lifecycle_id => p_lifecycle_id ,
p_phase_id => r.proj_element_id ,
p_record_version_number => r.record_version_number ,
X_return_status => l_return_status ,
X_msg_count => l_msg_count ,
X_msg_data => l_msg_data
);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
PA_EGO_WRAPPER_PUB.delete_stale_data_for_lc(
p_api_version => P_api_version ,
p_lifecycle_id => p_lifecycle_id ,
p_init_msg_list => p_init_msg_list ,
p_commit => p_commit ,
x_errorcode => l_errorcode ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Before call to private API delete_lifecycle');
pa_lifecycles_pvt.delete_lifecycle(
P_api_version => P_api_version,
p_commit => p_commit,
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_lifecycle_id => p_lifecycle_id,
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('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
ROLLBACK TO LCYL_DELETE_LIFECYCLE_PUB;
p_procedure_name => 'DELETE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO LCYL_DELETE_LIFECYCLE_PUB;
ROLLBACK TO LCYL_DELETE_LIFECYCLE_PUB;
p_procedure_name => 'DELETE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_lifecycle;
PROCEDURE delete_lifecycle_phase (
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 := G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_phase_id IN NUMBER ,
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) := 'delete_lifecycle_phase';
Select 'Y'
From dual
Where exists(
Select 'XYZ'
from pa_proj_element_versions child
, pa_proj_element_versions parent
where child.proj_element_id = P_phase_id
and child.parent_structure_version_id = parent.element_version_id
and parent.proj_element_id = P_lifecycle_id
AND child.object_type= c_object_type
AND child.project_id=c_project_id
and parent.object_type = 'PA_STRUCTURES'
and parent.project_id = c_project_id);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase : Entered delete_lifecycle_phase...');
SAVEPOINT LCYL_DELETE_LCYL_PHASE_PUB;
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase : Initialising message stack...');
pa_debug.init_err_stack('PA_LIFECYCLES_PUB.DELETE_LIFECYCLE_PHASE');
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase :After initialising the stack...');
Select 'Y' into l_phase_exists
From dual
Where exists(
Select 'XYZ' from pa_proj_element_versions child
, pa_proj_element_versions parent
where child.proj_element_id = P_phase_id
and child.parent_structure_version_id = parent.element_version_id
and parent.proj_element_id = P_lifecycle_id
AND child.object_type= c_object_type
AND child.project_id=c_project_id
and parent.object_type = 'PA_STRUCTURES'
and parent.project_id = c_project_id);
select 'Y'
into l_project_usage_exists
from SYS.DUAL
where exists( select 'XYZ' from pa_lifecycle_usages
where lifecycle_id=P_lifecycle_id AND
usage_type=c_project_type);
select 'Y'
into l_product_usage_exists
from SYS.DUAL
where exists(select 'XYZ' from pa_lifecycle_usages
where lifecycle_id=P_lifecycle_id AND
usage_type=c_product_type);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase: checking message count');
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Before call to private API check_delete_lcyl_phase_ok');
pa_lifecycles_pvt.check_delete_lcyl_phase_ok(
P_api_version => P_api_version ,
p_calling_module => p_calling_module ,
p_debug_mode => p_debug_mode ,
p_max_msg_count => p_max_msg_count ,
P_lifecycle_id => P_lifecycle_id ,
P_lifecycle_phase_id => p_phase_id ,
X_return_status => l_return_status ,
X_msg_count => l_msg_count ,
X_msg_data => l_msg_data ,
x_delete_ok => l_del_phase_ok
);
pa_debug.debug('PA_EGO_WRAPPER_PUB.check_delete_phase_ok : Before call to private API check_delete_lcyl_phase_ok');
PA_EGO_WRAPPER_PUB.check_delete_phase_ok (
p_api_version => P_api_version ,
p_phase_id => p_phase_id ,
p_init_msg_list => p_init_msg_list ,
x_delete_ok => l_del_phase_ok ,
x_return_status => l_return_status ,
x_errorcode => l_errorcode ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
PA_EGO_WRAPPER_PUB.process_phase_delete(
p_api_version => P_api_version ,
p_phase_id => p_phase_id ,
p_init_msg_list => p_init_msg_list ,
p_commit => p_commit ,
x_errorcode => l_errorcode ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data);
pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase : calling pa_lifecycles_pvt.delete_lifecycle_phase for each phase');
pa_lifecycles_pvt.delete_lifecycle_phase(
P_api_version => P_api_version ,
p_commit => p_commit ,
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_phase_id => p_phase_id ,
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('PA_LIFECYCLES_PUB.delete_lifecycle_phase : checking message count');
ROLLBACK TO LCYL_DELETE_LCYL_PHASE_PUB;
p_procedure_name => 'DELETE_LIFECYCLE_PHASE',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO LCYL_DELETE_LCYL_PHASE_PUB;
ROLLBACK TO LCYL_DELETE_LCYL_PHASE_PUB;
p_procedure_name => 'DELETE_LIFECYCLE_PHASE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_lifecycle_phase;
PROCEDURE update_lifecycle (
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 :=G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_lifecycle_short_name IN VARCHAR2 :=G_MISS_CHAR ,
P_lifecycle_name IN VARCHAR2 :=G_MISS_CHAR ,
P_lifecycle_description IN VARCHAR2 :=G_MISS_CHAR ,
P_lifecycle_project_usage_type IN VARCHAR2 :=G_MISS_CHAR ,
P_lifecycle_product_usage_type IN VARCHAR2 :=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_lifecycle';
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : Entered update_lifecycle...');
SAVEPOINT LCYL_UPDATE_LIFECYCLE_PUB;
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: Checking api compatibility...');
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : Initialising message stack...');
pa_debug.init_err_stack('PA_LIFECYCLES_PUB.UPDATE_LIFECYCLE');
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle:After initialising the stack..getting the data from database.');
select name,element_number,description
into l_lcyl_name,l_short_name,l_lifecycle_description
from pa_proj_elements
where proj_element_id=l_lifecycle_id
AND object_type = c_object_type
AND project_id = c_project_id;
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle:Checking for null values assigning the updated values');
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Checking for existing parameters for uniqueness..');
Select 'N'
Into l_shname_uniq
from SYS.DUAL
Where exists(Select 'XYZ' from pa_proj_elements
where element_number = P_lifecycle_short_name
AND object_type=c_object_type
AND project_id = c_project_id);
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : checking message count'||FND_MSG_PUB.count_msg);
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : Before call to private API');
select 'Y'
into l_lifecycle_project_usage_type
from SYS.DUAL
where exists ( select '1' from pa_lifecycle_usages
where lifecycle_id=l_lifecycle_id
AND usage_type='PROJECTS');
select 'Y'
into l_lifecycle_product_usage_type
from SYS.DUAL
where exists ( select '1' from pa_lifecycle_usages
where lifecycle_id=l_lifecycle_id
AND usage_type='PRODUCTS');
p_msg_name => 'PA_NO_CHANGES_TO_UPDATE');
x_msg_data := 'PA_NO_CHANGES_TO_UPDATE';
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle:After checking for null values assigning the updated values');
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: checking message count');
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: Before call to private update API');
pa_lifecycles_pvt.update_lifecycle (
P_api_version => P_api_version ,
p_commit => p_commit ,
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_lifecycle_id => l_lifecycle_id ,
P_lifecycle_short_name => l_short_name ,
P_lifecycle_name => l_lcyl_name ,
P_lifecycle_description => l_lifecycle_description ,
P_lifecycle_project_usage_type => l_lifecycle_project_usage_type ,
P_lifecycle_product_usage_type => l_lifecycle_product_usage_type ,
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('PA_LIFECYCLES_PUB.update_lifecycle : checking message count After call to private update API');
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: checking message count');
ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PUB;
p_procedure_name => 'UPDATE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PUB;
ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PUB;
p_procedure_name => 'UPDATE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_lifecycle;
PROCEDURE update_lifecycle_phase (
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 :=G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_lifecycle_phase_id IN NUMBER ,
P_phase_status_name IN VARCHAR2 :=G_MISS_CHAR ,
P_phase_short_name IN VARCHAR2 :=G_MISS_CHAR ,
P_phase_name IN VARCHAR2 :=G_MISS_CHAR ,
P_phase_display_sequence IN NUMBER :=G_MISS_NUM ,
P_phase_description IN VARCHAR2 :=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_lifecycle_phase';
l_update_ok VARCHAR2(1) := FND_API.G_FALSE;
SELECT project_status_code
from pa_project_statuses
WHERE project_status_name = P_phase_status_name
AND status_type = 'PHASE';
/* Note that check for duplicate shortname and sequence can not be done here. as user may want to update
two records at the same time, swapping the values */
IF(p_debug_mode = 'Y') then
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : Entered update_lifecycle_phase...');
pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle_phases: Checking api compatibility...');
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : Initialising message stack...');
pa_debug.init_err_stack('PA_LIFECYCLES_PUB.UPDATE_LIFECYCLE_PHASE');
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase :After initialising the stack..and getting the old data from database.');
select prj.name,prj.element_number,prj.description,prj.phase_code,elem.display_sequence
into l_phase_name,l_phase_short_name,l_phase_description,l_phase_code,l_seqn
from pa_proj_elements prj
, pa_proj_element_versions elem
where prj.proj_element_id=P_lifecycle_phase_id
and prj.project_id=c_project_id
and prj.object_type=c_object_type
and prj.proj_element_id=elem.proj_element_id;
pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase:Checking for null values assigning the updated values');
pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase:After checking for null values assigning the updated values');
p_msg_name => 'PA_NO_CHANGES_TO_UPDATE');
x_msg_data := 'PA_NO_CHANGES_TO_UPDATE';
pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase: checking message count');
pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase: Calling private api check_delete_lcyl_phase_ok');
pa_lifecycles_pvt.check_delete_lcyl_phase_ok(
P_api_version =>l_api_version ,
p_calling_module =>p_calling_module ,
p_debug_mode =>p_debug_mode ,
P_max_msg_count =>P_max_msg_count ,
P_lifecycle_id =>P_lifecycle_id ,
P_lifecycle_phase_id =>P_lifecycle_phase_id ,
x_delete_ok =>l_update_ok ,
x_return_status =>l_return_status ,
x_msg_count =>l_msg_count ,
X_msg_data =>l_data
);
pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase: checking message count');
IF (l_update_ok <>FND_API.G_TRUE) THEN
RAISE FND_API.G_EXC_ERROR;
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : Before call to private API update_lifecycle_phase');
pa_lifecycles_pvt.update_lifecycle_phase (
P_api_version =>1.0 ,
p_commit =>p_commit ,
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_lifecycle_id => P_lifecycle_id ,
P_lifecycle_phase_id =>P_lifecycle_phase_id ,
P_phase_display_sequence =>l_seqn ,
P_phase_code =>l_phase_code ,
P_phase_short_name =>l_phase_short_name ,
P_phase_name =>l_phase_name ,
P_phase_description =>l_phase_description ,
P_record_version_number =>P_record_version_number ,
x_return_status =>l_return_status ,
x_msg_count =>l_msg_count ,
X_msg_data =>l_data
);
pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : checking message count after call to private API update_lifecycle_phase');
p_procedure_name => 'UPDATE_LIFECYCLE_PHASE',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'UPDATE_LIFECYCLE_PHASE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_lifecycle_phase;