1 package body pa_employee_cost_rate as
2 /* $Header: PAXSUECB.pls 120.2 2005/08/10 04:23:25 avajain noship $ */
3 procedure check_overlapping_date(v_person_id varchar2,
4 v_err_code in out NOCOPY number,
5 v_mesg in out NOCOPY varchar2) is
6 v_temp varchar2(1);
7 cursor c is
8 select 'X'
9 from pa_compensation_details a, pa_compensation_details b
10 where a.person_id = v_person_id
11 and b.person_id = v_person_id
12 and a.rowid <> b.rowid
13 and ((a.start_date_active
14 between b.start_date_active
15 and nvl(b.end_date_active,a.start_date_active +1))
16 or (a.end_date_active
17 between b.start_date_active
18 and nvl(b.end_date_active,b.end_date_active +1))
19 or (b.start_date_active
20 between a.start_date_active
21 and nvl(a.end_date_active,b.start_date_active +1))
22 );
23 BEGIN
24 open c;
25 fetch c into v_temp;
26 if c%found then
27 v_err_code :=1;
28 else
29 v_err_code :=0;
30 end if;
31 close c;
32 EXCEPTION
33 when others then
34 v_err_code :=2;
35 v_mesg := to_char(sqlcode);
36 END check_overlapping_date;
37
38 END;