[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_MAPPING
(
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_FALSE
, 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_record_version_number IN NUMBER := FND_API.G_MISS_NUM
, p_wp_from_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
, p_wp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
, p_fp_task_version_id 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_msg_count NUMBER := 0;
SELECT
obj.object_relationship_id
, obj.record_version_number
FROM
PA_OBJECT_RELATIONSHIPS obj
WHERE obj.object_id_from1 = l_wp_task_version_id
AND obj.relationship_type='M';
SELECT
object_relationship_id
, record_version_number
FROM
PA_OBJECT_RELATIONSHIPS
WHERE
object_id_to1 = l_fp_task_version_id
AND relationship_type='M';
SELECT
obj.object_relationship_id
, obj.record_version_number
FROM
PA_OBJECT_RELATIONSHIPS obj
WHERE obj.object_id_from1 = l_wp_task_version_id
AND obj.object_id_to1 = l_fp_task_version_id
AND obj.relationship_type='M';
PA_DEBUG.set_curr_function( p_function => 'DELETE_MAPPING',
p_debug_mode => l_debug_mode );
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Printing Input parameters';
savepoint DELETE_MAPPING_PUBLIC;
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Validating Input parameters';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Both p_wp_task_version_id and p_fp_task_version_id are null';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : No Mapping Exists';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Calling delete';
PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
(
p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => p_commit
, p_validate_only => p_validate_only
, p_calling_module => 'SELF_SERVICE'
, p_debug_mode => l_debug_mode
, p_object_relationship_id => l_object_relationship_id
, p_record_version_number => l_rec_version_num
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Calling delete';
PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
(
p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => p_commit
, p_validate_only => p_validate_only
, p_calling_module => 'SELF_SERVICE'
, p_debug_mode => l_debug_mode
, p_object_relationship_id => l_object_relationship_id
, p_record_version_number => l_rec_version_num
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Mapping does not exist for the passed WP task ID';
PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
(
p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => p_commit
, p_validate_only => p_validate_only
, p_calling_module => 'SELF_SERVICE'
, p_debug_mode => l_debug_mode
, p_object_relationship_id => map_rec.object_relationship_id
, p_record_version_number => map_rec.record_version_number
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO DELETE_MAPPING_PUBLIC;
x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
ROLLBACK TO DELETE_MAPPING_PUBLIC;
, p_procedure_name => 'DELETE_MAPPING'
, p_error_text => x_msg_data);
ROLLBACK TO DELETE_MAPPING_PUBLIC;
, p_procedure_name => 'DELETE_MAPPING'
, p_error_text => x_msg_data);
END DELETE_MAPPING ;
SELECT
ppv.element_version_id
FROM
pa_proj_element_versions ppv
, pa_proj_elements pae
WHERE pae.name = l_task_name
AND pae.project_id = l_project_id
AND ppv.project_id = l_project_id
AND ppv.proj_element_id = pae.proj_element_id
AND ppv.parent_structure_version_id = l_parent_str_version_id
AND pae.object_type = 'PA_TASKS'
AND pae.project_id = ppv.project_id ;
SELECT
ppv.element_version_id
FROM
pa_proj_element_versions ppv
, pa_proj_elements pae
, pa_proj_elem_ver_structure str_ver
, pa_proj_structure_types str_type
, pa_structure_types
WHERE pae.name = l_task_name
AND pae.project_id = l_project_id
AND ppv.project_id = l_project_id
AND ppv.proj_element_id = pae.proj_element_id
AND ppv.parent_structure_version_id = str_ver.element_version_id
AND pae.object_type = 'PA_TASKS'
AND ppv.object_type = 'PA_TASKS'
AND str_ver.project_id = l_project_id
AND str_ver.proj_element_id = str_type.proj_element_id
AND str_type.structure_type_id = pa_structure_types.structure_type_id
AND pa_structure_types.structure_type = 'FINANCIAL'
AND pae.project_id = ppv.project_id ;
PROCEDURE UPDATE_MAPPING
(
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_FALSE
, 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_record_version_number IN NUMBER := FND_API.G_MISS_NUM
, p_structure_type IN VARCHAR2 := 'WORKPLAN'
, p_project_id IN NUMBER
, p_wp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
, p_wp_prnt_str_ver_id IN NUMBER := FND_API.G_MISS_NUM
, p_wp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
, p_fp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
, p_fp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
, p_object_relationship_id 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_msg_count NUMBER := 0;
SELECT ppv.element_version_id
FROM pa_proj_element_versions ppv, pa_proj_elements pae
WHERE pae.name = l_wp_task_name
AND pae.project_id = l_projectid
AND ppv.proj_element_id = pae.proj_element_id
AND ppv.parent_structure_version_id = l_prnt_str_ver_id
AND pae.object_type = 'PA_TASKS'
AND pae.project_id = ppv.project_id;
SELECT object_relationship_id , record_version_number
FROM pa_object_relationships
WHERE object_id_from1 = l_wp_task_version_id
AND relationship_type = 'M';
SELECT
ppv.element_version_id
FROM
pa_proj_element_versions ppv
, pa_proj_elements pae
, pa_proj_elem_ver_structure str_ver
, pa_proj_structure_types str_type
, pa_structure_types
WHERE pae.name = l_task_name
AND pae.project_id = l_project_id
AND ppv.project_id = l_project_id
AND ppv.proj_element_id = pae.proj_element_id
AND ppv.parent_structure_version_id = str_ver.element_version_id
AND pae.object_type = 'PA_TASKS'
AND ppv.object_type = 'PA_TASKS'
AND str_ver.project_id = l_project_id
AND str_ver.proj_element_id = str_type.proj_element_id
AND str_type.structure_type_id = pa_structure_types.structure_type_id
AND pa_structure_types.structure_type = 'FINANCIAL'
AND ppv.project_id = pae.project_id;
PA_DEBUG.set_curr_function( p_function => 'UPDATE_MAPPING',
p_debug_mode => l_debug_mode );
savepoint UPDATE_MAPPING_PUBLIC;
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Printing Input parameters';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Validating Input parameters';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : project id can not be null';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Both of tasks id and tasks name are null';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : parent structue version id can not be null, if wp task name is not null';
PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
(
p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => p_commit
, p_validate_only => p_validate_only
, p_calling_module => 'SELF_SERVICE'
, p_debug_mode => l_debug_mode
, p_object_relationship_id => l_object_relationship_id
, p_record_version_number => l_rec_version_num
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO UPDATE_MAPPING_PUBLIC;
x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Some parameters are NULL';
ROLLBACK TO UPDATE_MAPPING_PUBLIC;
, p_procedure_name => 'UPDATE_MAPPING'
, p_error_text => x_msg_data);
ROLLBACK TO UPDATE_MAPPING_PUBLIC;
, p_procedure_name => 'UPDATE_MAPPING'
, p_error_text => x_msg_data);
END UPDATE_MAPPING ;
SELECT
pelever.element_version_id AS structure_version
, pelever.attribute15 AS src_str_version_id
, pstrType.structure_type AS structure_type
FROM
pa_proj_element_versions pelever,
pa_proj_structure_types prjstrType,
pa_structure_types pstrType
WHERE
pelever.object_type = 'PA_STRUCTURES'
AND
pelever.project_id = l_dest_project_id
AND
prjstrType.proj_element_id = pelever.proj_element_id
AND
pstrType.structure_type_id = prjstrType.structure_type_id
AND
pstrType.structure_type = 'WORKPLAN'
AND
pstrType.structure_type_class_code = 'WORKPLAN';
SELECT
paObrel.OBJECT_ID_FROM1 as OBJECT_ID_FROM1
, paObrel.OBJECT_ID_TO1 as OBJECT_ID_TO1
FROM
pa_proj_element_versions elever
, pa_object_relationships paObrel
WHERE elever.PARENT_STRUCTURE_VERSION_ID = l_parent_str_version_id
AND paObrel.OBJECT_ID_FROM1 = elever.element_version_id
AND elever.project_id = l_project_id
AND paObrel.RELATIONSHIP_TYPE = 'M'
AND elever.object_type = 'PA_TASKS';
SELECT ELEMENT_VERSION_ID
FROM PA_PROJ_ELEMENT_VERSIONS ELEVER
WHERE ELEVER.ATTRIBUTE15 = l_src_task_version_id
AND ELEVER.OBJECT_TYPE = 'PA_TASKS'
AND ELEVER.PROJECT_ID = l_project_id;
SELECT proj_element_id
FROM pa_proj_element_versions
WHERE OBJECT_TYPE = 'PA_TASKS'
AND element_version_id = l_task_ver_id
AND parent_structure_version_id = l_str_version_id
AND pa_proj_element_versions.project_id = l_project_id;
SELECT element_version_id
FROM pa_proj_element_versions
WHERE OBJECT_TYPE = 'PA_TASKS'
AND parent_structure_version_id = l_str_version_id
AND proj_element_id = l_task_id
AND pa_proj_element_versions.project_id = l_project_id;
l_src_to_tasks_id_tbl.DELETE;
l_src_from_tasks_id_tbl.DELETE;
l_dest_from_tasks_id_tbl.DELETE;
l_dest_to_tasks_id_tbl.DELETE;
INSERT INTO PA_OBJECT_RELATIONSHIPS
(
object_relationship_id,
object_type_from,
object_id_from1,
object_id_from2,
object_id_from3,
object_id_from4,
object_id_from5,
object_type_to,
object_id_to1,
object_id_to2,
object_id_to3,
object_id_to4,
object_id_to5,
relationship_type,
relationship_subtype,
lag_day,
imported_lag,
priority,
pm_product_code,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage
)
VALUES ( pa_object_relationships_s.nextval,
'PA_TASKS',
l_dest_from_tasks_id_tbl(iCounter),
NULL,
NULL,
NULL,
NULL,
'PA_TASKS',
l_dest_to_tasks_id_tbl(iCounter),
NULL,
NULL,
NULL,
NULL,
'M',
NULL,
NULL,
NULL,
NULL,
NULL,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
NULL
);
l_src_from_tasks_id_tbl.DELETE;
l_src_to_tasks_id_tbl.DELETE;
l_from_task_id_tbl.DELETE;
l_from_dest_task_ver_id.DELETE;
INSERT INTO PA_OBJECT_RELATIONSHIPS (
object_relationship_id,
object_type_from,
object_id_from1,
object_id_from2,
object_id_from3,
object_id_from4,
object_id_from5,
object_type_to,
object_id_to1,
object_id_to2,
object_id_to3,
object_id_to4,
object_id_to5,
relationship_type,
relationship_subtype,
lag_day,
imported_lag,
priority,
pm_product_code,
Record_Version_Number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
weighting_percentage)
VALUES ( pa_object_relationships_s.nextval,
'PA_TASKS',
l_from_dest_task_ver_id(iCounter),
NULL,
NULL,
NULL,
NULL,
'PA_TASKS',
l_src_to_tasks_id_tbl(iCounter),
NULL,
NULL,
NULL,
NULL,
'M',
NULL,
NULL,
NULL,
NULL,
NULL,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
NULL
);
Pa_Debug.WRITE(g_module_name,'After Completing insert',l_debug_level3);
PROCEDURE DELETE_ALL_MAPPING
(
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_FALSE
, 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_project_id 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 := 0;
SELECT
obRel.object_relationship_id
FROM
pa_proj_element_versions elever1
, pa_proj_element_versions elever2
-- , pa_proj_structure_types projStrType Bug 3693235 Performance Fix
-- , pa_structure_types strType Bug 3693235 Performance Fix
, pa_object_relationships obRel
WHERE
elever1.object_type = 'PA_TASKS'
AND elever1.parent_structure_version_id = elever2.element_version_id
AND elever2.object_type = 'PA_STRUCTURES'
-- Bug 3693235 Performance Fix
--AND elever2.proj_element_id = projStrType.proj_element_id
--AND projStrType.structure_type_id = strType.structure_type_id
--AND strType.structure_type = 'FINANCIAL'
AND exists (SELECT 'xyz' FROM pa_proj_structure_types WHERE proj_element_id = elever2.proj_element_id and structure_type_id = 6) -- Bug 3693235 Performance Fix
AND elever1.project_id = l_project_id
AND elever2.project_id = l_project_id
AND elever1.project_id = elever2.project_id
AND elever1.element_version_id = obRel.object_id_to1
AND obRel.relationship_type='M'
-- Bug 3693235 Performance Fix
AND obRel.object_type_from = 'PA_TASKS'
AND obRel.object_type_to = 'PA_TASKS'
;
PA_DEBUG.set_curr_function( p_function => 'DELETE_ALL_MAPPING',
p_debug_mode => l_debug_mode );
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : Printing Input parameters';
savepoint DELETE_ALL_MAPPING_PUBLIC;
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : Validating Input parameters';
Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : p_project_id can not be null';
l_mapped_obj_rel_id_tbl.DELETE;
DELETE FROM PA_OBJECT_RELATIONSHIPS
WHERE
OBJECT_RELATIONSHIP_ID = l_mapped_obj_rel_id_tbl(iCounter);
ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
, p_procedure_name => 'DELETE_ALL_MAPPING'
, p_error_text => x_msg_data);
ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
, p_procedure_name => 'DELETE_ALL_MAPPING'
, p_error_text => x_msg_data);
END DELETE_ALL_MAPPING ;