1 PACKAGE BODY per_gb_disability AS
2 /* $Header: pegbdisp.pkb 120.0.12000000.2 2007/02/20 09:41:22 npershad noship $ */
3
4 PROCEDURE VALIDATE_CREATE_DISABILITY(p_category in varchar2,
5 p_person_id in number,
6 p_effective_date date)
7 AS
8
9
10 cursor csr_get_securityid
11 is
12 select distinct security_group_id
13 from per_all_people_f ppf
14 ,per_business_groups pbg
15 where ppf.person_id = p_person_id
16 and ppf.business_group_id= pbg.business_group_id
17 and p_effective_date between
18 ppf.effective_start_date and ppf.effective_end_date;
19
20
21 cursor csr_get_owner(p_security_id number) is
22 select flv.last_updated_by upd
23 from fnd_lookup_values flv
24 where flv.lookup_type='DISABILITY_CATEGORY'
25 and flv.language = userenv('LANG')
26 and flv.security_group_id = p_security_id
27 and flv.lookup_code = p_category;
28
29 cursor csr_get_owner_std(p_security_id number) is
30 select flv.last_updated_by upd
31 from fnd_lookup_values flv
32 where flv.lookup_type='DISABILITY_CATEGORY'
33 and flv.language = userenv('LANG')
34 and flv.security_group_id =p_security_id
35 and flv.lookup_code = p_category;
36
37
38 v_csr csr_get_owner%rowtype;
39 l_security_id number := 0;
40 l_upd number;
41
42 BEGIN
43 IF p_category is not null THEN
44
45 open csr_get_securityid;
46 fetch csr_get_securityid into l_security_id;
47
48 open csr_get_owner(l_security_id);
49 fetch csr_get_owner into l_upd;
50 if csr_get_owner%notfound then
51 l_security_id := 0;
52 open csr_get_owner_std(l_security_id);
53 fetch csr_get_owner_std into l_upd;
54 close csr_get_owner_std;
55 end if;
56
57 IF p_category not in ('Y','N','ND') and l_upd in (0,1,2) THEN
58 hr_utility.set_message(800, 'HR_GB_78125_INVALID_CATEGORY');
59 hr_utility.raise_error;
60 END IF;
61 close csr_get_owner;
62 close csr_get_securityid;
63
64 END IF;
65
66 END VALIDATE_CREATE_DISABILITY;
67
68
69 PROCEDURE VALIDATE_UPDATE_DISABILITY(p_category in varchar2,
70 p_disability_id number,
71 p_effective_date date)
72 AS
73
74
75 cursor csr_get_securityid
76 is
77 select distinct security_group_id
78 from per_disabilities_f pdf
79 ,per_all_people_f ppf
80 ,per_business_groups pbg
81 where ppf.person_id = pdf.person_id
82 and ppf.business_group_id= pbg.business_group_id
83 and pdf.disability_id = p_disability_id
84 and p_effective_date between
85 ppf.effective_start_date and ppf.effective_end_date;
86 --and p_effective_date between pdf.effective_start_date and pdf.effective_end_date;
87
88 cursor csr_get_owner(p_security_id number) is
89 select flv.last_updated_by upd
90 from fnd_lookup_values flv
91 where flv.lookup_type='DISABILITY_CATEGORY'
92 and flv.language = userenv('LANG')
93 and flv.security_group_id =p_security_id
94 and flv.lookup_code = p_category;
95
96
97 cursor csr_get_owner_std(p_security_id number) is
98 select flv.last_updated_by upd
99 from fnd_lookup_values flv
100 where flv.lookup_type='DISABILITY_CATEGORY'
101 and flv.language = userenv('LANG')
102 and flv.security_group_id =p_security_id
103 and flv.lookup_code = p_category;
104
105 v_csr csr_get_owner%rowtype;
106 l_security_id number;
107 l_upd number;
108
109 BEGIN
110
111 IF p_category is not null THEN
112
113 open csr_get_securityid;
114 fetch csr_get_securityid into l_security_id;
115
116 open csr_get_owner(l_security_id);
117 fetch csr_get_owner into l_upd;
118 if csr_get_owner%notfound then
119 l_security_id := 0;
120 open csr_get_owner_std(l_security_id);
121 fetch csr_get_owner_std into l_upd;
122 close csr_get_owner_std;
123 end if;
124
125 IF p_category not in ('Y','N','ND') and l_upd in (0,1,2) THEN
126 hr_utility.set_message(800, 'HR_GB_78125_INVALID_CATEGORY');
127 hr_utility.raise_error;
128 END IF;
129 close csr_get_owner;
130 close csr_get_securityid;
131
132 END IF;
133
134 END VALIDATE_UPDATE_DISABILITY;
135
136 END per_gb_disability;
[Home] [Help]