[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select gp.project_id,
gp.project_status_code
from pa_projects_all gp
where (gp.carrying_out_organization_id = x_organization_id
or x_organization_id is null )
and (gp.project_type = x_project_type
or x_project_type is null )
and (trunc(gp.closed_date) <= trunc(x_closed_thru_date)
or x_closed_thru_date is null )
and (gp.project_status_code = x_project_status_code
or ((x_active_closed_flag = 'A'
and gp.project_type in ( select project_type
from pa_project_types
where project_type_class_code = 'INDIRECT')
and gp.project_status_code not in ('PARTIALLY_PURGED',
'PURGED',
'CLOSED'))
or ( x_active_closed_flag = 'C'
and gp.project_status_code in ('PARTIALLY_PURGED',
'CLOSED')))) ;
l_select_clause VARCHAR2(2000);
l_select_clause := 'select p.project_id, p.project_status_code ' ||
' from pa_projects p ' ;
l_where_clause := ' where pa_security.allow_update(p.project_id) = ''Y'' '||
' and p.template_flag != ''Y'' ';
'(SELECT ps.project_status_code '||
' from pa_project_statuses ps'||
' where project_system_status_code in '||
' (''PARTIALLY_PURGED'' , ' ||
'''PURGED'' , ' ||
'''PENDING_PURGE'' , ' ||
'''CLOSED'' )' || ')' ;
' ( select pt.project_type ' ||
' from pa_project_types pt ' ||
' where pt.project_type_class_code = ''INDIRECT'') ' ;
'( SELECT ps.project_status_code '||
' from pa_project_statuses ps'||
' where ps.project_system_status_code in ' ||
'( ''CLOSED'' , ' ||
'''PARTIALLY_PURGED'' )' || ')';
dbms_sql.parse(v_cursor_gen_id, l_select_clause ||
l_where_clause,
dbms_sql.v7);
insert into pa_purge_projects
( Purge_batch_Id,
Project_Id,
Last_Project_Status_Code,
txn_to_date ,
Purge_Actuals_Flag,
Archive_Actuals_Flag,
Purge_Budgets_Flag,
Archive_Budgets_Flag,
Purge_Capital_Flag,
Archive_Capital_Flag,
Purge_Summary_Flag,
Archive_Summary_Flag,
Next_PP_Project_Status_Code,
Next_P_Project_Status_Code,
Purged_Date,
Purge_Project_Status_Code,
Created_By,
Last_Update_date,
Last_Updated_By,
Creation_Date )
select x_purge_batch_id,
l_project_id,
l_project_status_code,
x_txn_to_date ,
l_purge_actuals_flag,
decode(l_purge_actuals_flag, 'N','N',x_archive_actuals_flag),
l_purge_budgets_flag,
decode(l_purge_budgets_flag, 'N','N',x_archive_budgets_flag),
l_purge_capital_flag,
decode(l_purge_capital_flag, 'N','N',x_archive_capital_flag),
l_purge_summary_flag,
decode(l_purge_summary_flag, 'N','N',x_archive_summary_flag),
x_Next_PP_Project_Status_Code,
x_Next_P_Project_Status_Code,
NULL,
'N',
x_user_id,
sysdate,
x_user_id,
sysdate
from dual
where ( x_active_closed_flag = 'C'
and ( l_purge_actuals_flag = 'Y'
or l_purge_budgets_flag = 'Y'
or l_purge_capital_flag = 'Y'
or l_purge_summary_flag = 'Y'))
or ( l_txn_to_date is not null
and sign(x_txn_to_date - l_txn_to_date) = 1) ;
Select NVL(MAX(decode(pp.txn_to_date, NULL, pp.purge_actuals_flag, 'N')), 'N'),
MAX(pp.txn_to_date),
NVL(MAX(pp.purge_capital_flag), 'N'),
NVL(MAX(pp.purge_budgets_flag), 'N'),
NVL(MAX(pp.purge_summary_flag), 'N')
Into l_purge_actuals_flag,
l_txn_to_date,
l_purge_capital_flag,
l_purge_budgets_flag,
l_purge_summary_flag
From pa_purge_projects pp
Where pp.project_id = p_project_id ;