[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pb.active_closed_flag
from pa_purge_batches pb
where pb.purge_batch_id = p_purge_batch_id
for update of pb.purge_batch_id nowait ;
select pp.project_id,
/* pt.project_type_class_code,
p.project_status_code, Commented for bug 2715317*/ /* project current status */
pp.last_project_status_code,
pp.purge_project_status_code, /* Bug#2416385 Added for Phase -III Archive and Purge */
pp.purge_summary_flag,
pp.purge_capital_flag,
pp.purge_actuals_flag,
pp.purge_budgets_flag,
pp.txn_to_date
/* from pa_project_types pt,
pa_projects p, Commented for bug 2715317 */
from pa_purge_projects pp
where pp.purge_batch_id = p_purge_batch_id
/* and p.project_type = pt.project_type
and nvl(pt.org_id, -99) = nvl(p.org_id, -99)
and pp.project_id = p.project_id Commented for bug 2715317 */
for update of pp.purge_project_status_code nowait ;
select pp.purge_summary_flag
from pa_purge_projects pp
where pp.purge_batch_id = p_purge_batch_id
and pp.project_id=p_project_id;
g_delete_errors := 'Y'; /* Bug#2416385 Added for Phase -III Archive and Purge */
Select pt.project_type_class_code,
p.project_status_code
into l_project_type_class_code,
l_project_status_code
from pa_project_types pt,
pa_projects p
where p.project_type = pt.project_type
and p.project_id = l_GetProjectsInBatch_csr.project_id;
pa_debug.debug(' This project status changed after selecting a batch '||to_char(l_GetProjectsInBatch_csr.project_id));
, x_Insert_Update_Mode => NULL
, x_Calling_Module => NULL
, x_project_id => l_GetProjectsInBatch_csr.project_id
, x_Task_id => NULL
, x_old_value => l_GetProjectsInBatch_csr.last_project_status_code
, x_new_value => 'PENDING_PURGE'
, x_Project_Type => NULL
, x_Project_Start_Date => NULL
, x_Project_End_Date => NULL
, x_Public_Sector_Flag => NULL
, 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 => NULL
, 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_PM_Product_Code => NULL
, x_PM_Project_Reference => NULL
, x_PM_Task_Reference => NULL
, x_Functional_Security_Flag => NULL
, 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 ) ;
pa_purge_validate.insert_errors(p_Purge_Batch_Id => p_purge_batch_id,
p_Project_Id => l_GetProjectsInBatch_csr.project_id,
p_Error_Type => 'E',
p_User => pa_purge_validate.g_user,
x_Err_Stack => l_err_stack,
x_Err_Stage => l_err_stage,
x_Err_Code => l_err_code
);
update pa_purge_batches pb
set pb.batch_status_code = 'W',
pb.request_id = pa_purge_validate.g_request_id,
pb.program_application_id = pa_purge_validate.g_program_application_id,
pb.program_id = pa_purge_validate.g_program_id ,
pb.program_update_date = sysdate,
pb.last_update_login = -1,
pb.last_updated_by = -1,
pb.last_update_date = sysdate
where pb.purge_batch_id = p_purge_batch_id ;
g_delete_errors := 'N';
pa_purge_validate_icip.g_insert_errors_no_duplicate := 'N'; /* Bug# 2431705 */
pa_purge_validate.insert_errors ( p_Purge_Batch_Id => p_purge_batch_id,
p_Project_Id => c_prj_in_batch.project_id,
p_Error_Type => 'E',
p_User => pa_purge_validate.g_user,
x_Err_Stack => l_err_stack,
x_Err_Stage => l_err_stage,
x_Err_Code => l_err_code );
select purge_project_status_code
into l_purge_project_status_code
from pa_purge_projects
where project_id = c_prj_in_batch.project_id
and purge_batch_id = p_purge_batch_id ;
update pa_projects_all p
set p.project_status_code = c_prj_in_batch.last_project_status_code,
p.last_update_date = sysdate,
p.last_updated_by = -1,
p.last_update_login = -1
where p.project_id = c_prj_in_batch.project_id;
procedure insert_errors ( p_purge_batch_id in NUMBER,
p_project_id in NUMBER,
p_error_type in VARCHAR2,
p_user in NUMBER,
X_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
X_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
X_err_code in OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
) is
l_Count NUMBER ;
l_err_stack := X_err_stack || '-> Inserting errors to the error table ' ;
if g_delete_errors = 'Y' then /* Bug#2416385 Added for Phase -III Archive and Purge */
pa_debug.debug('Deleting errors for the project '||to_char(p_project_id)||' in batch '||to_char(p_purge_batch_id));
delete from pa_purge_project_errors pe
where pe.purge_batch_id = p_purge_batch_id
and pe.project_id = p_project_id ;
if g_delete_errors = 'Y' then /* Bug#2416385 Added for Phase -III Archive and Purge */
-- This means there are no errors for this validation run. So delete
-- all the errors from the previous run if exists and update the
-- project to Valid. Also update the status of the project in
-- PA_PROJECTS to 'PENDING_PURGE'.
pa_debug.debug('Updating purge_project_status_code to valid for project '||to_char(p_project_id)) ;
update pa_purge_projects pp
set pp.purge_project_status_code = 'V',
pp.request_id = pa_purge_validate.g_request_id,
pp.program_application_id = pa_purge_validate.g_program_application_id,
pp.program_id = pa_purge_validate.g_program_id ,
pp.program_update_date = sysdate
where pp.project_id = p_project_id
and pp.purge_batch_id = p_purge_batch_id ;
update pa_projects_all p
set p.project_status_code = 'PENDING_PURGE',
p.last_update_date = sysdate,
p.last_updated_by = -1,
p.last_update_login = -1
where p.project_id = p_project_id ;
end if; /* if g_delete_errors = 'Y check */
pa_debug.debug('Inserting validation errors for project '||to_char(p_project_id));
X_err_stage := 'Inserting validation errors for project '||to_char(p_project_id) ;
FND_MSG_PUB.DELETE_MSG(p_msg_index => 1);
insert into pa_purge_project_errors
( purge_batch_id,
project_id,
error_code,
error_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
values (p_purge_batch_id,
p_project_id,
-- replace(replace(l_message_code,'PA'||l_chr), l_chr),
l_msg_data,
p_error_type,
-1,
sysdate,
-1,
sysdate,
-1) ;
update pa_purge_projects pp
set pp.purge_project_status_code = 'I',
pp.request_id = pa_purge_validate.g_request_id,
pp.program_application_id = pa_purge_validate.g_program_application_id,
pp.program_id = pa_purge_validate.g_program_id ,
pp.program_update_date = sysdate
where pp.project_id = p_project_id
and pp.purge_batch_id = p_purge_batch_id ;
pa_debug.debug('Error Procedure Name := PA_PURGE_VALIDATE.INSERT_ERRORS' );
END insert_errors ;