How to check if the employee is rehired - Script

CREATE OR REPLACE PACKAGE rn_rehire_flag_pkg AS

FUNCTION get_rehire_flag_fnc

( p_business_group_id IN NUMBER

, p_assignment_id IN Number

)

RETURN VARCHAR2 ;

END rn_rehire_flag_pkg;

/

CREATE OR REPLACE PACKAGE Body rn_rehire_flag_pkg AS

FUNCTION get_rehire_flag_fnc

( p_business_group_id IN NUMBER

, p_assignment_id IN Number

)

RETURN VARCHAR2

IS

v_error_desc varchar2(250);

v_error_code varchar2(10);

v_flag varchar2(2) :=NULL ;

v_ohire_date date;

v_rhire_date date;

BEGIN

v_flag :=null;



begin

SELECT Original_Date_of_hire INTO v_ohire_date

FROM per_people_x

WHERE person_id = (SELECT DISTINCT person_id

FROM per_assignments_x

WHERE assignment_id = p_assignment_id)

AND business_group_id=p_business_group_id;



SELECT date_start INTO v_rhire_date

FROM per_periods_of_service

WHERE person_id = (SELECT DISTINCT person_id

FROM per_assignments_x

WHERE assignment_id = p_assignment_id)

AND business_group_id=p_business_group_id;

exception

when others then null;

end;

IF v_rhire_date =v_ohire_date THEN

v_flag:='N';

ELSIF v_rhire_date > v_ohire_date THEN

v_flag:='Y';

END IF;



RETURN (v_flag);

EXCEPTION

WHEN OTHERS THEN

-- v_error_code := SQLCODE;

-- v_error_desc := SQLERRM;

--dbms_output.put_line(v_error_code','v_error_desc);

RETURN 'N';

END get_rehire_flag_fnc;

END rn_rehire_flag_pkg;

/