[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT project_id
,fin_plan_type_id
,fin_plan_version_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = c_proj_fp_options_id;
pa_debug.g_err_stage:= 'project ids are same. inserting into excluded elements';
INSERT INTO pa_fp_excluded_elements
( proj_fp_options_id
,project_id
,fin_plan_type_id
,element_type
,fin_plan_version_id
,task_id
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
SELECT p_to_proj_fp_options_id proj_fp_options_id
,l_to_fp_option_info_rec.project_id project_id
,l_to_fp_option_info_rec.fin_plan_type_id fin_plan_type_id
,DECODE( p_to_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,ee.element_type,
p_to_element_type)
,l_to_fp_option_info_rec.fin_plan_version_id fin_plan_version_id
,ee.task_id task_id
,1 record_version_number
,sysdate last_update_date
,fnd_global.user_id last_updated_by
,sysdate creation_date
,fnd_global.user_id created_by
,fnd_global.login_id last_update_login
FROM
pa_fp_excluded_elements ee
WHERE ee.proj_fp_options_id = p_from_proj_fp_options_id
AND ee.element_type = DECODE( p_from_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,ee.element_type,
p_from_element_type);
pa_debug.g_err_stage:= 'project ids are DIFFERENT. inserting into excluded elements';
INSERT INTO pa_fp_excluded_elements
( proj_fp_options_id
,project_id
,fin_plan_type_id
,element_type
,fin_plan_version_id
,task_id
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
SELECT p_to_proj_fp_options_id proj_fp_options_id
,l_to_fp_option_info_rec.project_id project_id
,l_to_fp_option_info_rec.fin_plan_type_id fin_plan_type_id
,DECODE( p_to_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,ee.element_type,
p_to_element_type)
,l_to_fp_option_info_rec.fin_plan_version_id fin_plan_version_id
,target_pt.task_id task_id
,1 record_version_number
,sysdate last_update_date
,fnd_global.user_id last_updated_by
,sysdate creation_date
,fnd_global.user_id created_by
,fnd_global.login_id last_update_login
FROM pa_fp_excluded_elements ee,
pa_tasks source_pt,
pa_tasks target_pt
WHERE proj_fp_options_id = p_from_proj_fp_options_id
AND target_pt.project_id = l_to_fp_option_info_rec.project_id
AND ee.element_type = DECODE( p_from_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,ee.element_type,
p_from_element_type)
AND source_pt.task_id = ee.task_id
AND target_pt.task_number = source_pt.task_number;
This api is called to delete all the tasks that are made plannable
while copying actuals etc., from pa_fp_excluded_elements.
==================================================================*/
PROCEDURE Synchronize_Excluded_Elements
( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
,p_element_type IN pa_fp_elements.element_type%TYPE
,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
AS
l_msg_count NUMBER := 0;
* Delete all the tasks that are made plannable from
* pa_fp_excluded_elements
* Note: Please note that
*/
DELETE FROM pa_fp_excluded_elements fee
WHERE fee.proj_fp_options_id = p_proj_fp_options_id
AND fee.element_type = p_element_type
AND fee.task_id IN (SELECT pfe.task_id
FROM pa_fp_elements pfe
WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
AND pfe.element_type = p_element_type
AND pfe.plannable_flag = 'Y'
AND pfe.resource_list_member_id = 0);
/* Called from setup pages to delete from pa_fp_excluded_elements when a task element is
made plannable. If the task element is not present in pa_fp_excluded_elements, the
delete_Row table handler is not called */
PROCEDURE Delete_Excluded_Elements
( p_proj_fp_options_id IN pa_fp_excluded_elements.proj_fp_options_id%TYPE
,p_element_type IN pa_fp_excluded_elements.element_type%TYPE
,p_task_id IN pa_fp_excluded_elements.task_id%TYPE
,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
AS
l_msg_count NUMBER := 0;
L_PROCEDURE_NAME CONSTANT VARCHAR2(100) := 'Pa_Fp_Excluded_Elements_Pkg.Delete_Excluded_Elements';
SELECT rowid
FROM pa_fp_excluded_elements
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = p_element_type
AND task_id = p_task_id;
pa_debug.set_curr_function( p_function => 'Delete_Excluded_Elements',
p_debug_mode => l_debug_mode );
/* Record to be deleted doesnt exists and no need to call delete_row table handler */
IF cur_excl_elems%FOUND THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Calling PA_FP_EXCLUDED_ELEMENTS_PKG.DELETE_ROW...';
PA_FP_EXCLUDED_ELEMENTS_PKG.DELETE_ROW(
p_row_id => excl_elems_rec.rowid,
x_return_status => x_return_status);
pa_debug.g_err_stage:= 'Error returned by PA_FP_EXCLUDED_ELEMENTS_PKG.DELETE_ROW';
pa_debug.g_err_stage:= 'Exiting Delete_Excluded_Elements';
,p_procedure_name => 'Delete_Excluded_Elements'
,p_error_text => x_msg_data);
END Delete_Excluded_Elements;