Delete SIT API

API for Deleting SIT details.

 

 

 

PROCEDURE XXHR_DELETE_SIT
/* =====================================================================
— NAME : XXHR_DELETE_SIT

— PROGRAM TYPE : Procedure

— DESCRIPTION : This is used to delete Special Information type for any employee
— INPUTS : None
— OUTPUTS : None
— ===================================================================*/
IS

cursor cur_del_sit
is
select *
FROM XXHR_EMP_MASTER_STG
where process_flag is null;
ln_person_id number;
ln_person_analysis_id number;
ln_ovn number;
lv_err varchar2(3000);
lv_status varchar2(100);

begin
for cur_del_sit_rec in cur_del_sit
loop
begin
select person_id into ln_person_id
from per_all_people_f papf
where employee_number = cur_del_sit_rec.employee_number
and sysdate between effective_start_date and effective_end_date;
exception when others then
ln_person_id := null;
end;
begin
select distinct ppa. person_analysis_id ,ppa.object_version_number
into ln_person_analysis_id, ln_ovn
from PER_ANALYSIS_CRITERIA pac,
FND_ID_FLEX_SEGMENTS_VL fifs,
PER_PERSON_ANALYSES ppa
where 1=1
–pac.ID_FLEX_NUM =50448
and pac.id_flex_num = fifs.id_flex_num
and fifs.segment_name like ‘Passport Number’
and ppa.person_id = ln_person_id
and ppa.id_flex_num = pac.id_flex_num;
exception when others then
lv_status := ‘N’;
null;
end;
if nvl(lv_status,’Y’) =’Y’
then
begin

hr_sit_api.delete_sit
(p_validate =>FALSE
,p_person_analysis_id => ln_person_analysis_id
,p_pea_object_version_number => ln_ovn
);

UPDATE XXHR_EMP_MASTER_STG
SET PROCESS_FLAG = ‘S’
— ERROR_MSG = ‘last name is null’
WHERE employee_number = cur_del_sit_rec.employee_number;

exception when others then
lv_err := sqlerrm;
UPDATE XXHR_EMP_MASTER_STG
SET PROCESS_FLAG = ‘E’,
ERROR_MSG = lv_err
WHERE employee_number = cur_del_sit_rec.employee_number;
end;

end if;
end loop;
end XXHR_DELETE_SIT;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s