[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT forecast_item_id, forecast_item_type
FROM pa_forecast_items
WHERE delete_flag='Y';
sql_command := 'SELECT 1 from dual where exists(SELECT NAME FROM PJI_SYSTEM_PARAMETERS)' ;
l_fi_tbl.delete; -- 5201806
l_fi_type_tbl.delete; -- 5201806
DELETE FROM pa_forecast_item_details
WHERE forecast_item_id = l_fi_tbl(i)
AND l_fi_type_tbl(i)='R';
DELETE FROM pa_forecast_item_details
WHERE forecast_item_id = l_fi_tbl(i)
AND l_fi_type_tbl(i) <> 'R'
AND PJI_SUMMARIZED_FLAG in ('X','E');
DELETE FROM pa_forecast_item_details a
WHERE a.forecast_item_id = l_fi_tbl(i)
AND l_fi_type_tbl(i) <> 'R'
AND ( 'Y' = ALL(SELECT nvl(b.PJI_SUMMARIZED_FLAG,'Y') -- All records are NULL or Y
FROM pa_forecast_item_details b
WHERE b.forecast_item_id = a.forecast_item_id)
OR 'N' = ALL (SELECT nvl(c.PJI_SUMMARIZED_FLAG,'XYZ') -- Otherwise,All records should be N
FROM pa_forecast_item_details c
WHERE c.forecast_item_id = a.forecast_item_id)
) ;
-- delete all child records with UTIL_SUMMARIZED_CODE as 'X' and 'E'
FORALL i IN l_fi_tbl.FIRST..l_fi_tbl.LAST
DELETE FROM pa_forecast_item_details
WHERE forecast_item_id = l_fi_tbl(i)
AND l_fi_type_tbl(i) <> 'R'
AND UTIL_SUMMARIZED_CODE in ('X','E');
DELETE FROM pa_forecast_item_details a
where a.forecast_item_id = l_fi_tbl(i)
and l_fi_type_tbl(i) <> 'R'
and ( 'Y' = ALL(SELECT nvl(b.UTIL_SUMMARIZED_CODE,'Y')
FROM pa_forecast_item_details b
WHERE b.forecast_item_id = a.forecast_item_id)
OR 'N' = ALL (SELECT nvl(c.UTIL_SUMMARIZED_CODE,'XYZ')
FROM pa_forecast_item_details c
WHERE c.forecast_item_id = a.forecast_item_id)
) ;
-- commit; -- 5201806 : Now commit is happening after each delete
-- Dont raise ,Just increment the Number of Rows Deleted Counter for the number of rows
-- successfully deleted so far.
l_rows2 := l_rows2 + nvl(sql%rowcount,0);
DELETE FROM pa_fi_amount_details fi
WHERE fi.forecast_item_id = l_fi_tbl(i)
AND( (l_fi_type_tbl(i) = 'R')
OR (l_fi_type_tbl(i) <> 'R'
AND NOT EXISTS( SELECT 'Y' from pa_forecast_item_details dtl
WHERE dtl.forecast_item_id = fi.forecast_item_id)))
;
DELETE FROM pa_forecast_items fi
WHERE fi.forecast_item_id = l_fi_tbl(i)
AND( (forecast_item_type = 'R')
OR (forecast_item_type <> 'R'
AND NOT EXISTS( SELECT 'Y' from pa_forecast_item_details dtl
WHERE dtl.forecast_item_id = fi.forecast_item_id)))
;
pa_debug.g_err_stage:= 'No. of rows deleted from pa_forecast_items - ' || l_rows1 ;
pa_debug.g_err_stage:= 'No. of rows deleted from pa_forecast_item_details - ' || l_rows2;
pa_debug.g_err_stage:= 'No. of rows deleted from pa_fi_amount_details - ' ||l_rows3;
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PA_FORECAST_ITEMS' ,
p_rows_deleted => l_rows1 ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PA_FORECAST_ITEM_DETAILS',
p_rows_deleted => l_rows2,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PA_FI_AMOUNT_DETAILS',
p_rows_deleted => l_rows3,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PA_FI_AMOUNT_DETAILS';
SELECT a.item_type, a.item_key, a.wf_type_code
FROM pa_wf_processes a
WHERE
a.item_type in ('PACANDID','PACOPR','PARADVWF','PARAPTEM','PARFIGEN','PAROVCNT','PAWFGPF','PAYPRJNT','PARMATRX','PAXWFHRU')
AND a.item_key NOT IN
(SELECT wi.item_key
FROM wf_items wi
WHERE wi.item_type IN ('PACANDID','PACOPR','PARADVWF','PARAPTEM','PARFIGEN','PAROVCNT','PAWFGPF','PAYPRJNT','PARMATRX','PAXWFHRU')
)
--bug#10238573 start
UNION
SELECT item_type, item_key, wf_type_code
FROM pa_wf_processes pwp
WHERE pwp.wf_type_code = 'ASSIGNMENT_APPROVAL'
and pwp.item_type = 'PAWFAAP'
and not exists
(select 'Y'
from pa_wf_ntf_performers ntf
where ntf.wf_type_code = 'ASSIGNMENT_APPROVAL'
and ntf.item_type = pwp.item_type
and ntf.item_key = pwp.item_key)
AND pwp.item_key NOT IN
(SELECT wi.item_key
FROM wf_items wi
WHERE wi.item_type = 'PAWFAAP')
--bug#10238573 end
;
l_wf_item_type_tbl.delete; -- 5201806
l_wf_item_key_tbl.delete; -- 5201806
l_wf_type_code_tbl.delete; -- 5201806
DELETE FROM PA_WF_NTF_PERFORMERS
WHERE item_key = l_wf_item_key_tbl(i)
AND item_type = l_wf_item_type_tbl(i) ;
DELETE FROM pa_wf_process_details
WHERE item_key=l_wf_item_key_tbl(i)
AND item_type = l_wf_item_type_tbl(i)
AND wf_type_code = l_wf_type_code_tbl(i) ;
DELETE FROM pa_wf_processes
WHERE item_key=l_wf_item_key_tbl(i)
AND item_type = l_wf_item_type_tbl(i)
AND wf_type_code = l_wf_type_code_tbl(i);
pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_processes ' || l_rows1 ;
pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_process_details ' || l_rows2 ;
pa_debug.g_err_stage:= 'No. of rows deleted from pa_wf_ntf_performers ' || l_rows3 ;
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PA_WF_PROCESSES' ,
p_rows_deleted => l_rows1 ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PA_WF_PROCESS_DETAILS',
p_rows_deleted => l_rows2,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PA_WF_NTF_PERFORMERS',
p_rows_deleted => l_rows3,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PA_WF_NTF_PERFORMERS';
SELECT request_id
FROM pa_reporting_exceptions
WHERE request_id not in
(SELECT request_id
FROM fnd_concurrent_requests)
AND nvl(request_id,0) > 0;
l_request_id_tbl.delete; -- 5201806
DELETE FROM pa_reporting_exceptions
WHERE request_id = l_request_id_tbl (i);
pa_debug.g_err_stage:= 'No. of rows deleted from pa_reporting_exceptions ' || l_rows1 ;
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PA_REPORTING_EXCEPTIONS' ,
p_rows_deleted => l_rows1 ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to pa_reporting_exceptions';
l_grants_deleted NUMBER := 0;
SELECT DISTINCT per.person_id,
(to_number(fg.instance_pk1_value)) organization_id,
fm.menu_name
FROM fnd_grants fg,
fnd_objects fo,
fnd_menus fm,
per_all_people_f per,
wf_roles wfr
WHERE per.person_id IN ( SELECT person_id
FROM per_periods_of_service ppos
WHERE ppos.person_id = per.person_id
AND ppos.actual_termination_date is not null
AND NOT EXISTS (SELECT 1
FROM per_periods_of_service
WHERE person_id = ppos.person_id
AND actual_termination_date IS NULL )
GROUP BY person_id
HAVING MAX(actual_termination_date) < SYSDATE
UNION
SELECT person_id
FROM per_periods_of_placement ppop
WHERE ppop.person_id = per.person_id
AND ppop.actual_termination_date is not null
AND NOT EXISTS (SELECT 1
FROM per_periods_of_placement
WHERE person_id = ppop.person_id
AND actual_termination_date IS NULL )
GROUP BY person_id
HAVING MAX(actual_termination_date) < SYSDATE )
AND fg.object_id = fo.object_id
AND fo.obj_name = 'ORGANIZATION'
AND fg.instance_type = 'INSTANCE'
AND fg.instance_pk1_value is not null
AND fg.grantee_key = wfr.NAME
AND fg.grantee_type = 'USER'
AND fg.instance_set_id is null
AND wfr.orig_system = 'HZ_PARTY'
AND per.party_id = wfr.orig_system_id
AND fg.menu_id = fm.menu_id
AND (TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date)
AND fm.menu_name IN ('PA_PRM_RES_AUTH', 'PA_PRM_PROJ_AUTH', 'PA_PRM_RES_PRMRY_CONTACT', 'PA_PRM_UTL_AUTH');
-- Delete Resource authority
IF l_menu_name = 'PA_PRM_RES_AUTH' THEN
pa_resource_utils.delete_grant( p_person_id => l_person_id
,p_org_id => l_orginzation_id
,p_role_name => 'PA_PRM_RES_AUTH'
,x_return_status => l_return_status);
-- Delete project authority
ELSIF l_menu_name = 'PA_PRM_PROJ_AUTH' THEN
pa_resource_utils.delete_grant( p_person_id => l_person_id
,p_org_id => l_orginzation_id
,p_role_name => 'PA_PRM_PROJ_AUTH'
,x_return_status => l_return_status);
-- Delete primary contact
ELSIF l_menu_name = 'PA_PRM_RES_PRMRY_CONTACT' THEN
pa_resource_utils.delete_grant( p_person_id => l_person_id
,p_org_id => l_orginzation_id
,p_role_name => 'PA_PRM_RES_PRMRY_CONTACT'
,x_return_status => l_return_status);
-- Delete utilization authority
ELSIF l_menu_name = 'PA_PRM_UTL_AUTH' THEN
pa_resource_utils.delete_grant( p_person_id => l_person_id
,p_org_id => l_orginzation_id
,p_role_name => 'PA_PRM_UTL_AUTH'
,x_return_status => l_return_status);
l_grants_deleted := l_grants_deleted + 1;
pa_debug.g_err_stage:= 'No. of grants deleted ' || l_grants_deleted;
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'FND_GRANTS' ,
p_rows_deleted => l_grants_deleted ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PURGE_ORG_AUTHORITY';
DELETE FROM PJI_FM_EXTR_PLAN_LINES_DEBUG WHERE ROWNUM <= p_commit_size;
DELETE FROM PJI_FM_XBS_ACCUM_TMP1_DEBUG WHERE ROWNUM <= p_commit_size;
DELETE FROM PJI_SYSTEM_DEBUG_MSG WHERE ROWNUM <= p_commit_size;
pa_debug.g_err_stage:= 'No. of rows deleted from PJI_FM_EXTR_PLAN_LINES_DEBUG ' || l_rows1 ;
pa_debug.g_err_stage:= 'No. of rows deleted from PJI_FM_XBS_ACCUM_TMP1_DEBUG ' || l_rows2 ;
pa_debug.g_err_stage:= 'No. of rows deleted from PJI_SYSTEM_DEBUG_MSG ' || l_rows3 ;
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PJI_FM_EXTR_PLAN_LINES_DEBUG' ,
p_rows_deleted => l_rows1 ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PJI_FM_XBS_ACCUM_TMP1_DEBUG',
p_rows_deleted => l_rows2,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_PURGE_PUB.INSERT_PURGE_LOG
(
p_request_id => p_request_id ,
p_table_name => 'PJI_SYSTEM_DEBUG_MSG',
p_rows_deleted => l_rows3,
x_return_status => x_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:= 'Successfully inserted Log details pertaining to PURGE_PJI_DEBUG';
SELECT table_name, num_recs_purged
FROM PA_PURGE_PRJ_DETAILS
WHERE purge_batch_id = p_request_id
AND project_id = 0
ORDER BY table_name ;
l_rows_deleted_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
FETCH c_purge_details BULK COLLECT INTO l_table_name_tbl,l_rows_deleted_tbl;
pa_debug.g_err_stage:= 'Purged '|| l_rows_deleted_tbl(i) ||' entries from ' || l_table_name_tbl(i) ;
PROCEDURE INSERT_PURGE_LOG
(
p_request_id IN NUMBER ,
p_table_name IN VARCHAR2 ,
p_rows_deleted IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_debug_mode VARCHAR2(1);
pa_debug.set_curr_function( p_function => 'INSERT_PURGE_LOG', p_debug_mode => 'Y');
Pa_Debug.WRITE('PA_PURGE_PUB','Before inserting into PA_PURGE_PRJ_DETAILS',l_debug_level3);
INSERT INTO PA_PURGE_PRJ_DETAILS
(
PURGE_BATCH_ID
,PROJECT_ID
,TABLE_NAME
,NUM_RECS_PURGED
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
)
VALUES
(
p_request_id
,0
,p_table_name
,p_rows_deleted
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,sysdate
,fnd_global.prog_appl_id
,fnd_global.conc_program_id
,sysdate
);
Pa_Debug.g_err_stage:='Successfully Inserted Purge Log';
, p_procedure_name => 'INSERT_PURGE_LOG'
, p_error_text => x_msg_data);
END INSERT_PURGE_LOG;