----------------------------------------------------------------------------------------
--- Cursor to check whether a person SSN already exists in the oracle or not
----------------------------------------------------------------------------------------
CURSOR cur_per_ssn (
p_national_identifier per_all_people_f.national_identifier%TYPE,
p_biz_id NUMBER)
IS
SELECT national_identifier
FROM apps.per_all_people_f papf
WHERE papf.national_identifier = p_national_identifier
AND business_group_id = p_biz_id
AND pv_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
----------------------------------------------------------------------------------------
--- Cursor to get the final process and rehire flag of a person using the SSN
----------------------------------------------------------------------------------------
CURSOR cur_rehire_chk (
p_national_identifier1 per_all_people_f.national_identifier%TYPE)
IS
SELECT ppos.final_process_date,
NVL (ppos.attribute2, 'N'),
papf.person_id
FROM per_all_people_f papf, per_periods_of_service ppos
WHERE papf.national_identifier = p_national_identifier1
AND papf.person_id = ppos.person_id
AND ppos.date_start IN
(SELECT MAX (date_start)
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = ppos.person_id
AND ppos1.date_start <= TRUNC (SYSDATE))
AND pv_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND ROWNUM < 2;
------------------------------------------------------------------------------------------
--- Cursor to get the person type id of a person to pass to the create emp API
------------------------------------------------------------------------------------------
CURSOR cur_per_type (
p_person_type per_person_types.user_person_type%TYPE,
p_biz_id NUMBER)
IS
SELECT person_type_id
FROM apps.per_person_types
WHERE UPPER (user_person_type) = UPPER (p_person_type)
AND business_group_id = p_biz_id;
--- Cursor to check whether a person SSN already exists in the oracle or not
----------------------------------------------------------------------------------------
CURSOR cur_per_ssn (
p_national_identifier per_all_people_f.national_identifier%TYPE,
p_biz_id NUMBER)
IS
SELECT national_identifier
FROM apps.per_all_people_f papf
WHERE papf.national_identifier = p_national_identifier
AND business_group_id = p_biz_id
AND pv_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
----------------------------------------------------------------------------------------
--- Cursor to get the final process and rehire flag of a person using the SSN
----------------------------------------------------------------------------------------
CURSOR cur_rehire_chk (
p_national_identifier1 per_all_people_f.national_identifier%TYPE)
IS
SELECT ppos.final_process_date,
NVL (ppos.attribute2, 'N'),
papf.person_id
FROM per_all_people_f papf, per_periods_of_service ppos
WHERE papf.national_identifier = p_national_identifier1
AND papf.person_id = ppos.person_id
AND ppos.date_start IN
(SELECT MAX (date_start)
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = ppos.person_id
AND ppos1.date_start <= TRUNC (SYSDATE))
AND pv_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND ROWNUM < 2;
------------------------------------------------------------------------------------------
--- Cursor to get the person type id of a person to pass to the create emp API
------------------------------------------------------------------------------------------
CURSOR cur_per_type (
p_person_type per_person_types.user_person_type%TYPE,
p_biz_id NUMBER)
IS
SELECT person_type_id
FROM apps.per_person_types
WHERE UPPER (user_person_type) = UPPER (p_person_type)
AND business_group_id = p_biz_id;
