--Create Table
create table papf_16082018 (person_id
number, employee_number varchar2(10));
--Insert Data
insert into papf_16082018 values
(53687,'215677');
commit;
update apps.fnd_user set
employee_id=null where employee_id in
(select
distinct p.person_id from apps.per_all_people_f p,papf_16082018 i where
p.employee_number=i.employee_number);
commit;
set serveroutput on;
DECLARE
-- Input Variables
l_validate BOOLEAN := FALSE;
l_effective_date DATE := sysdate;
l_person_id
NUMBER := 0;
l_perform_predel_validation BOOLEAN
:= FALSE;
-- Output Variables
l_person_org_manager_warning VARCHAR2(2000);
BEGIN
--Capture in Cursor
declare
Cursor res is
SELECT person_id,employee_number from papf_16082018;
--Start Loop
BEGIN
FOR rc in res
LOOP
BEGIN
--API Update
-- Calling API HR_PERSON_API.DELETE_PERSON
hr_person_api.delete_person(p_validate => l_validate ,
p_effective_date =>
l_effective_date ,
p_person_id => rc.person_id ,
p_perform_predel_validation =>
l_perform_predel_validation ,
p_person_org_manager_warning => l_person_org_manager_warning );
ddbms_output.put_line('Employee
deleted successfully. Person ID ' || rc.person_id || ' Employee Number ' ||
rc.employee_number);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Unable to delete Employee : '||SQLCODE||'
'||SUBSTR(SQLERRM, 1, 100));
END;
END LOOP;
DBMS_OUTPUT.put_line ('Process Completed');
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error : ' || sqlerrm);
END;
/
Excellent blog, thanks for Sharing this informative article.
ReplyDeleteOracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Magnificent blog I visit this blog it's extremely wonderful. Interestingly, in this blog content composed plainly and reasonable. The substance of data is useful.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad