1 PACKAGE BODY hr_it_extra_person_rules AS
2 /* $Header: peitexpr.pkb 120.0 2005/05/31 10:27:32 appldev noship $ */
3 --
4 --
5 -- Service functions to return TRUE if the value passed has been changed.
6 --
7 FUNCTION val_changed(p_value IN NUMBER) RETURN BOOLEAN IS
8 BEGIN
9 RETURN (p_value IS NULL OR p_value <> hr_api.g_number);
10 END val_changed;
11 --
12 FUNCTION val_changed(p_value IN VARCHAR2) RETURN BOOLEAN IS
13 BEGIN
14 RETURN (p_value IS NULL OR p_value <> hr_api.g_varchar2);
15 END val_changed;
16 --
17 FUNCTION val_changed(p_value IN DATE) RETURN BOOLEAN IS
18 BEGIN
19 RETURN (p_value IS NULL OR p_value <> hr_api.g_date);
20 END val_changed;
21 --
22 --
23 -- Uses Tobacco:
24 --
25 -- This cannot be entered.
26 --
27 -- Employee Reference No (per_information2):
28 --
29 -- Must be unique.
30 --
31 -- Note: ONLY supports real values.
32 --
33 procedure extra_create_person_checks
34 (p_per_information2 IN VARCHAR2
35 ,p_uses_tobacco_flag IN VARCHAR2
36 ,p_business_group_id IN NUMBER) IS
37 --
38 --
39 -- Local variables.
40 --
41 l_v number(2) :=0;
42
43 l_per_information2 VARCHAR2(50);
44 BEGIN
45 --
46 --
47 -- Uses tobacco cannot be entered.
48 --
49 If p_uses_tobacco_flag IS NOT NULL THEN
50 hr_utility.set_message(800, 'HR_IT_INVALID_USES_TOBACCO');
51 hr_utility.raise_error;
52 END IF;
53 --
54 --
55 -- Employee Reference No must be unique.
56 --
57 IF p_per_information2 IS NOT NULL THEN
58 BEGIN
59 select 1
60 into l_v
61 from dual where EXISTS
62 (SELECT NULL
63 FROM per_all_people_f
64 WHERE per_information2 = p_per_information2
65 AND business_group_id=p_business_group_id);
66 if l_v = 1 then
67 hr_utility.set_message(800, 'HR_IT_INVALID_EMP_REF_NO');
68 hr_utility.raise_error;
69 end if;
70
71 EXCEPTION
72 WHEN no_data_found THEN
73 NULL;
74 END;
75 END IF;
76 END extra_create_person_checks;
77
78
79 --
80 -- Uses Tobacco:
81 --
82 -- This cannot be entered.
83 --
84 -- Employee Reference No (per_information2):
85 --
86 -- Must be unique.
87 --
88 -- Note: Supports both real and API system values (these are passed when the value has not
89 -- been changed.
90 --
91 PROCEDURE extra_update_person_checks
92 (p_person_id IN NUMBER
93 ,p_per_information2 IN VARCHAR2
94 ,p_uses_tobacco_flag IN VARCHAR2) IS
95 --
96 --
97 -- Local variables.
98 --
99 l_v number(2) := 0;
100 l_per_information2 VARCHAR2(50);
101 l_business_group_id per_all_people_f.business_group_id%TYPE;
102 BEGIN
103 --
104 --
105 -- Uses tobacco cannot be entered.
106 --
107 If val_changed(p_uses_tobacco_flag) AND p_uses_tobacco_flag IS NOT NULL THEN
108 hr_utility.set_message(800, 'HR_IT_INVALID_USES_TOBACCO');
109 hr_utility.raise_error;
110 END IF;
111 --
112 --
113
114 select distinct business_group_id into l_business_group_id
115 from per_all_people_f
116 where person_id=p_person_id;
117
118 -- Employee Reference No must be unique.
119 --
120 IF val_changed(p_per_information2) AND p_per_information2 IS NOT NULL THEN
121 BEGIN
122 select 1
123 into l_v
124 from dual where EXISTS
125 (SELECT NULL
126 FROM per_all_people_f
127 WHERE per_information2 = p_per_information2
128 AND person_id <> p_person_id
129 AND business_group_id=l_business_group_id);
130 if l_v = 1 then
131 hr_utility.set_message(800, 'HR_IT_INVALID_EMP_REF_NO');
132 hr_utility.raise_error;
133 end if;
134
135 EXCEPTION
136 WHEN no_data_found THEN
137 NULL;
138 END;
139 END IF;
140 END extra_update_person_checks;
141 --
142 --
143 -- Service procedure to check that the collective agreement grade being entered is
144 -- within the structure of IT_CAGR.
145 --
146 -- If it is valid then the dynamic inserts flag is returned for future reference.
147 --
148 PROCEDURE cagr_structure_valid
149 (p_cagr_id_flex_num IN NUMBER
150 ,p_collective_agreement_id IN NUMBER
151 ,p_organization_id IN NUMBER
152 ,o_dynamic_inserts OUT NOCOPY VARCHAR2) IS
153 --
154 --
155 -- Local cursors.
156 --
157 CURSOR csr_it_cagr
158 (p_cagr_id_flex_num NUMBER
159 ,p_collective_agreement_id NUMBER
160 ,p_organization_id NUMBER) IS
161 SELECT pcagv.dynamic_insert_allowed
162 FROM per_coll_agree_grades_v pcagv
163 ,hr_organization_units org
164 WHERE org.organization_id = p_organization_id
165 AND pcagv.business_group_id = org.business_group_id
166 AND pcagv.collective_agreement_id = p_collective_agreement_id
167 AND pcagv.id_flex_num = p_cagr_id_flex_num
168 AND pcagv.d_grade_type_name = 'IT_CAGR';
169 --
170 --
171 -- Local variables.
172 --
173 l_dynamic_insert VARCHAR2(1) := 'N';
174 BEGIN
175 --
176 --
177 -- Get the name of the collective agreement grade structure that is being used.
178 --
179 OPEN csr_it_cagr(p_cagr_id_flex_num, p_collective_agreement_id, p_organization_id);
180 FETCH csr_it_cagr INTO l_dynamic_insert;
181 IF csr_it_cagr%NOTFOUND THEN
182 CLOSE csr_it_cagr;
183 hr_utility.set_message(800, 'HR_IT_INVALID_GRADE_TYPE_NAME');
184 hr_utility.raise_error;
185 END IF;
186 CLOSE csr_it_cagr;
187 --
188 --
189 -- Return dynamic insert flag.
190 --
191 o_dynamic_inserts := l_dynamic_insert;
192 END cagr_structure_valid;
193 --
194 --
195 -- Service procedure to check that the collective agreement grade being entered is
196 -- defined correctly i.e. built from first segment down with no intermediate null
197 -- values.
198 --
199 PROCEDURE cagr_format_valid
200 (p_cag_segment1 IN VARCHAR2
201 ,p_cag_segment2 IN VARCHAR2
202 ,p_cag_segment3 IN VARCHAR2) IS
203 BEGIN
204 --
205 --
206 -- Level or Description has been entered without a grade.
207 --
208 If p_cag_segment1 IS NULL AND (p_cag_segment2 IS NOT NULL OR p_cag_segment3 IS NOT NULL) THEN
209 hr_utility.set_message(800, 'HR_IT_IVALID_GRADE_LEVEL_DESC');
210 hr_utility.raise_error;
211 --
212 --
213 -- Description has been entered without a Level.
214 --
215 ELSIF p_cag_segment2 IS NULL AND p_cag_segment3 IS NOT NULL THEN
216 hr_utility.set_message(800, 'HR_IT_IVALID_LEVEL_DESC');
217 hr_utility.raise_error;
218 END IF;
219 END cagr_format_valid;
220 --
221 --
222 -- Collective Agreement Grades:
223 --
224 -- If the user is using a collective agreement grade then it must be within the
225 -- predefined structure of IT_CAGR: Grade - Level - Description. This requires that
226 -- the structure is associated with the collective agreement and the collective
227 -- agreement is defined within the business group to which the assignment belongs.
228 --
229 -- The grade structure must be built from the first segment down i.e. cannot have a null
230 -- value followed by an actual value e.g. cannot have a value for level if there is no
231 -- grade.
232 --
233 -- If dynamic inserts is not enabled for the IT_CAGR structure then the combination
234 -- must already exist.
235 --
236 -- Unemployment Insurance Code (p_segment2):
237 --
238 -- This is mandatory.
239 --
240 -- Note: ONLY supports real values.
241 --
242 PROCEDURE extra_create_assignment_checks
243 (p_collective_agreement_id IN NUMBER
244 ,p_cagr_id_flex_num IN NUMBER
245 ,p_organization_id IN NUMBER
246 ,p_cag_segment1 IN VARCHAR2
247 ,p_cag_segment2 IN VARCHAR2
248 ,p_cag_segment3 IN VARCHAR2
249 ,p_scl_segment2 IN VARCHAR2) IS
250 --
251 --
252 -- Local variables.
253 --
254 l_dynamic_inserts VARCHAR2(1) := 'N';
255 BEGIN
256 --
257 --
258 -- A collective agreement grade has been entered.
259 --
260 If p_collective_agreement_id IS NOT NULL AND p_cagr_id_flex_num IS NOT NULL THEN
261 --
262 --
263 -- Check that collective agreement grade structure is IT_CAGR.
264 --
265 cagr_structure_valid(p_cagr_id_flex_num, p_collective_agreement_id, p_organization_id, l_dynamic_inserts);
266 --
267 --
268 -- Check that collective agreement grade is formatted correctly i.e. built from top
269 -- down with no intermediate null values.
270 --
271 cagr_format_valid(p_cag_segment1, p_cag_segment2, p_cag_segment3);
272 --
273 --
274 -- If dynamic inserts are not supported then the collective agreeement grade must already
275 -- exist NB. this is supported by the API via hr_cgd_ins.ins_or_sel().
276 --
277 NULL;
278 END IF;
279 --
280 --
281 -- Unemployment insurance code must be entered.
282 --
283 /*IF p_scl_segment2 IS NULL THEN
284 hr_utility.set_message(800, 'HR_IT_NULL_INS_CODE');
285 hr_utility.raise_error;
286 END IF;*/
287 END extra_create_assignment_checks;
288 --
289 --
290 -- Collective Agreement Grades:
291 --
292 -- If the user is using a collective agreement grade then it must be within the
293 -- predefined structure of IT_CAGR: Grade - Level - Description. This requires that
294 -- the structure is associated with the collective agreement and the collective
295 -- agreement is defined within the business group to which the assignment belongs.
296 --
297 -- The grade structure must be built from the first segment down i.e. cannot have a null
298 -- value followed by an actual value e.g. cannot have a value for level if there is no
299 -- grade.
300 --
301 -- If dynamic inserts is not enabled for the IT_CAGR structure then the combination
302 -- must already exist.
303 --
304 -- Unemployment Insurance Code (p_segment2):
305 --
306 -- This is mandatory.
307 --
308 -- Note: Supports both real and API system values (these are passed when the value has not
309 -- been changed.
310 --
311 PROCEDURE extra_update_assignment_checks
312 (p_collective_agreement_id IN NUMBER
313 ,p_cagr_id_flex_num IN NUMBER
314 ,p_assignment_id IN NUMBER
315 ,p_object_version_number IN NUMBER
316 ,p_effective_date IN DATE
317 ,p_cag_segment1 IN VARCHAR2
318 ,p_cag_segment2 IN VARCHAR2
319 ,p_cag_segment3 IN VARCHAR2
320 ,p_segment2 IN VARCHAR2) IS
321 --
322 --
323 -- Local cursors.
324 --
325 CURSOR csr_cagr_details
326 (p_effective_date DATE
327 ,p_assignment_id NUMBER
328 ,p_object_version_number NUMBER) IS
329 SELECT asg.collective_agreement_id
330 ,asg.cagr_id_flex_num
331 ,asg.organization_id
332 ,cagr.segment1 cag_segment1
333 ,cagr.segment2 cag_segment2
334 ,cagr.segment3 cag_segment3
335 FROM per_all_assignments_f asg
336 ,per_cagr_grades_def cagr
337 WHERE asg.assignment_id = p_assignment_id
338 AND asg.object_version_number = p_object_version_number
339 AND p_effective_date BETWEEN asg.effective_start_date
340 AND asg.effective_end_date
341 AND cagr.cagr_grade_def_id (+) = asg.cagr_grade_def_id;
342 --
343 --
344 -- Local variables.
345 --
346 l_dynamic_inserts VARCHAR2(1) := 'N';
347 l_rec csr_cagr_details%ROWTYPE;
348 BEGIN
349 --
350 --
351 -- Check to see if any value affecting the collective agreement grade has changed.
352 --
353 IF val_changed(p_collective_agreement_id) OR
354 val_changed(p_cagr_id_flex_num) OR
355 val_changed(p_cag_segment1) OR
356 val_changed(p_cag_segment2) OR
357 val_changed(p_cag_segment3) THEN
358 --
359 --
360 -- Fill in any values which have not been changed this time i.e. the resulting record
361 -- represents the new combination of values for all the above values.
362 --
363 OPEN csr_cagr_details(p_effective_date, p_assignment_id, p_object_version_number);
364 FETCH csr_cagr_details INTO l_rec;
365 CLOSE csr_cagr_details;
366 --
367 IF val_changed(p_collective_agreement_id) THEN
368 l_rec.collective_agreement_id := p_collective_agreement_id;
369 END IF;
370 IF val_changed(p_cagr_id_flex_num) THEN
371 l_rec.cagr_id_flex_num := p_cagr_id_flex_num;
372 END IF;
373 IF val_changed(p_cag_segment1) THEN
374 l_rec.cag_segment1 := p_cag_segment1;
375 END IF;
376 IF val_changed(p_cag_segment2) THEN
377 l_rec.cag_segment2 := p_cag_segment2;
378 END IF;
379 IF val_changed(p_cag_segment3) THEN
380 l_rec.cag_segment3 := p_cag_segment3;
381 END IF;
382 --
383 --
384 -- A collective agreement grade has been entered.
385 --
386 If l_rec.collective_agreement_id IS NOT NULL AND l_rec.cagr_id_flex_num IS NOT NULL THEN
387 --
388 --
389 -- Check that collective agreement grade structure is IT_CAGR.
390 --
391 cagr_structure_valid(l_rec.cagr_id_flex_num, l_rec.collective_agreement_id, l_rec.organization_id, l_dynamic_inserts);
392 --
393 --
394 -- Check that collective agreement grade is formatted correctly i.e. built from top
395 -- down with no intermediate null values.
396 --
397 cagr_format_valid(l_rec.cag_segment1, l_rec.cag_segment2, l_rec.cag_segment3);
398 --
399 --
400 -- If dynamic inserts are not supported then the collective agreeement grade must already
401 -- exist NB. this is supported by the API via hr_cgd_ins.ins_or_sel().
402 --
403 NULL;
404 END IF;
405 END IF;
406 --
407 --
408 -- Unemployment insurance code must be entered.
409 --
410 /*IF val_changed(p_segment2) AND p_segment2 IS NULL THEN
411 hr_utility.set_message(800, 'HR_IT_NULL_INS_CODE');
412 hr_utility.raise_error;
413 END IF;*/
414 END extra_update_assignment_checks;
415 END hr_it_extra_person_rules;
[Home] [Help]