1 Package Body per_qat_bus as
2 /* $Header: peqatrhi.pkb 120.0.12010000.2 2008/11/20 12:27:31 kgowripe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_qat_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_qualification_id number default null;
15 g_language varchar2(4) default null;
16 --
17 -- The following global vaiables are only to be used by the
18 -- validate_translation function.
19 --
20 g_qualification_type_id number default null;
21 g_person_id number default null;
22 g_attendance_id number default null;
23 g_business_group_id number default null;
24 g_object_version_number number default null;
25 g_start_date date default null;
26 g_end_date date default null;
27 g_party_id number default null;
28 --
29 -- ---------------------------------------------------------------------------
30 -- |----------------------< set_security_group_id >--------------------------|
31 -- ---------------------------------------------------------------------------
32 --
33 Procedure set_security_group_id
34 (p_qualification_id in number
38 --
35 ) is
36 --
37 l_proc varchar2(72) := g_package||'set_security_group_id';
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 per_qua_bus.set_security_group_id(p_qualification_id => p_qualification_id);
44 --
45 hr_utility.set_location(' Leaving:'|| l_proc, 20);
46 --
47 end set_security_group_id;
48 --
49 -- ---------------------------------------------------------------------------
50 -- |---------------------< return_legislation_code >-------------------------|
51 -- ---------------------------------------------------------------------------
52 --
53 Function return_legislation_code
54 (p_qualification_id in number
55 ,p_language in varchar2
56 )
57 Return Varchar2 Is
58 --
59 -- Declare cursor
60 --
61 cursor csr_leg_code is
62 select pbg.legislation_code
63 from per_business_groups pbg
64 , per_qualifications_tl qat
65 , per_qualifications qau
66 where qat.qualification_id = p_qualification_id
67 and pbg.business_group_id = qau.business_group_id
68 and qau.qualification_id = qat.qualification_id;
69 --
70 -- Declare local variables
71 --
72 l_legislation_code varchar2(150);
73 l_proc varchar2(72) := g_package||'return_legislation_code';
74 --
75 Begin
76 --
77 hr_utility.set_location('Entering:'|| l_proc, 10);
78 --
79 -- Ensure that all the mandatory parameter are not null
80 --
81 hr_api.mandatory_arg_error
82 (p_api_name => l_proc
83 ,p_argument => 'qualification_id'
84 ,p_argument_value => p_qualification_id
85 );
86 --
87 --
88 if (( nvl(per_qat_bus.g_qualification_id, hr_api.g_number)
89 = p_qualification_id)
90 and ( nvl(per_qat_bus.g_language, hr_api.g_varchar2)
91 = p_language)) then
92 --
93 -- The legislation code has already been found with a previous
94 -- call to this function. Just return the value in the global
95 -- variable.
96 --
97 l_legislation_code := per_qat_bus.g_legislation_code;
98 hr_utility.set_location(l_proc, 20);
99 else
100 --
101 -- The ID is different to the last call to this function
102 -- or this is the first call to this function.
103 --
104 open csr_leg_code;
105 fetch csr_leg_code into l_legislation_code;
106 --
107 if csr_leg_code%notfound then
108 --
109 -- The primary key is invalid therefore we must error
110 --
111 close csr_leg_code;
112 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
113 fnd_message.raise_error;
114 end if;
115 hr_utility.set_location(l_proc,30);
116 --
117 -- Set the global variables so the values are
118 -- available for the next call to this function.
119 --
120 close csr_leg_code;
121 per_qat_bus.g_qualification_id := p_qualification_id;
122 per_qat_bus.g_language := p_language;
123 per_qat_bus.g_legislation_code := l_legislation_code;
124 end if;
125 hr_utility.set_location(' Leaving:'|| l_proc, 40);
126 return l_legislation_code;
127 end return_legislation_code;
128 --
129 -- ----------------------------------------------------------------------------
130 -- |-----------------------< chk_non_updateable_args >------------------------|
131 -- ----------------------------------------------------------------------------
132 -- {Start Of Comments}
133 --
134 -- Description:
135 -- This procedure is used to ensure that non updateable attributes have
136 -- not been updated. If an attribute has been updated an error is generated.
137 --
138 -- Pre Conditions:
139 -- g_old_rec has been populated with details of the values currently in
140 -- the database.
141 --
142 -- In Arguments:
143 -- p_rec has been populated with the updated values the user would like the
144 -- record set to.
145 --
146 -- Post Success:
147 -- Processing continues if all the non updateable attributes have not
148 -- changed.
149 --
150 -- Post Failure:
151 -- An application error is raised if any of the non updatable attributes
152 -- have been altered.
153 --
154 -- {End Of Comments}
155 -- ----------------------------------------------------------------------------
156 Procedure chk_non_updateable_args
157 (p_rec in per_qat_shd.g_rec_type
158 ) IS
159 --
160 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
161 --
162 Begin
163 --
164 -- Only proceed with the validation if a row exists for the current
165 -- record in the HR Schema.
166 --
167 IF NOT per_qat_shd.api_updating
168 (p_qualification_id => p_rec.qualification_id
169 ,p_language => p_rec.language
170 ) THEN
171 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
172 fnd_message.set_token('PROCEDURE ', l_proc);
173 fnd_message.set_token('STEP ', '5');
174 fnd_message.raise_error;
175 END IF;
176 --
177 --
178 End chk_non_updateable_args;
179 --
180 -- ----------------------------------------------------------------------------
184 Procedure chk_qual_overlap (p_qualification_id in number,
181 -- |-----------------------< chk_qual_overlap >-------------------------------|
182 -- ----------------------------------------------------------------------------
183 --
185 p_qualification_type_id in number,
186 p_person_id in number,
187 p_attendance_id in number,
188 p_business_group_id in number,
189 p_start_date in date,
190 p_end_date in date,
191 p_title in varchar2,
192 p_object_version_number in number,
193 p_party_id in number default null,
194 p_language in varchar2
195 ) is
196 --
197 l_proc varchar2(72) := g_package||'chk_qual_overlap';
198 --
199 --
200 -- This cursor checks for any identical overlapping qualifications for a
201 -- person taking a particular qualification. If the start date or end date
202 -- is within the bounds of a previously taken qualification then the
203 -- qualification is invalid i.e. dates must be changed. The reason that
204 -- qualifications can be taken more than once is for resits or failure, etc.
205 --
206 cursor c1 is
207 select null
208 from per_qualifications per
209 ,per_qualifications_tl qat
210 where qat.language = p_language
211 and qat.title = p_title
212 and qat.qualification_id = per.qualification_id
213 and per.qualification_type_id = p_qualification_type_id
214 and nvl(per.person_id,-1) = nvl(p_person_id,-1)
215 and nvl(per.party_id,-1) = nvl(p_party_id,nvl(per.party_id,-1)) --HR/TCA merge
216 and nvl(per.attendance_id,-1) = nvl(p_attendance_id,-1)
217 and nvl(per.business_group_id,-1) = nvl(p_business_group_id,
218 nvl(per.business_group_id,-1))
219 and per.qualification_id <> NVL(p_qualification_id,-1)
220 and (nvl(per.start_date,hr_api.g_sot)
221 between nvl(p_start_date,hr_api.g_sot)
222 and nvl(p_end_date,hr_api.g_eot)
223 ---modified below condition for fixing bug#7571790
224 --or nvl(per.end_date,nvl(per.start_date,p_start_date))
225 or nvl(per.end_date,hr_api.g_eot)
226 between nvl(p_start_date,hr_api.g_sot)
227 and nvl(p_end_date,hr_api.g_eot));
228 --
229 l_dummy varchar2(1);
230 --
231 Begin
232 --
233 hr_utility.set_location('Entering:'||l_proc,5);
234 --
235 if hr_multi_message.no_all_inclusive_error
236 (p_check_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
237 ,p_check_column2 => 'PER_QUALIFICATIONS.PARTY_ID'
238 ,p_check_column3 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
239 ,p_check_column4 => 'PER_QUALIFICATIONS.QUALIFICATION_ID'
240 ,p_check_column5 => 'PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID'
241 ) then
242 --
243 if hr_multi_message.no_all_inclusive_error
244 (p_check_column1 => 'PER_QUALIFICATIONS.START_DATE'
245 ,p_check_column2 => 'PER_QUALIFICATIONS.END_DATE'
246 ,p_check_column3 => 'PER_QUALIFICATIONS.TITLE'
247 ) then
248 --
249 --
250 /* This is commented out becuase the per_qua_shd.api_updating is
251 returning the new record and not the old rec. This may need to
252 be moved to the api since it needs values off the base table.
253
254 With this comment out the check will also run indepdent of
255 if the unique key have changed.
256
257 joward 28-JAN-2003
258
259 l_b_api_updating := per_qua_shd.api_updating
260 (p_qualification_id => p_qualification_id,
261 p_object_version_number => p_object_version_number);
262
263 if l_b_api_updating then
264 --
265 l_tl_api_updating := per_qat_shd.api_updating
266 (p_qualification_id => null,
267 p_language => p_language);
268 end if;
269 --
270 --
271 if (l_b_api_updating
272 and (nvl(p_qualification_type_id,hr_api.g_number)
273 <> nvl(per_qua_shd.g_old_rec.qualification_type_id,hr_api.g_number)
274 or nvl(p_person_id,hr_api.g_number)
275 <> nvl(per_qua_shd.g_old_rec.person_id,hr_api.g_number)
276 or nvl(p_party_id,hr_api.g_number) -- HR/TCA merge
277 <> nvl(per_qua_shd.g_old_rec.party_id,hr_api.g_number) --
278 or nvl(p_attendance_id,hr_api.g_number)
279 <> nvl(per_qua_shd.g_old_rec.attendance_id,hr_api.g_number)
280 or nvl(p_business_group_id,hr_api.g_number)
281 <> nvl(per_qua_shd.g_old_rec.business_group_id,hr_api.g_number)
282 or nvl(p_title,hr_api.g_varchar2)
283 <> nvl(per_qat_shd.g_old_rec.title,hr_api.g_varchar2)
284 or nvl(p_start_date,hr_api.g_date)
285 <> nvl(per_qua_shd.g_old_rec.start_date,hr_api.g_date)
286 or nvl(p_end_date,hr_api.g_date)
287 <> nvl(per_qua_shd.g_old_rec.end_date,hr_api.g_date))
288 or not l_b_api_updating) then
289 */ --
290 --
291 -- check if record already exists in PER_QUALIFICATIONS table.
292 --
293 open c1;
294 --
295 fetch c1 into l_dummy;
296 if c1%found then
300 --
297 --
298 -- raise error as qualification record already exists within these
299 -- date boundaries.
301 hr_utility.set_message(801,'HR_51847_QUA_REC_EXISTS');
302 hr_utility.raise_error;
303 --
304 end if;
305 --
306 close c1;
307 --
308 /* end if; */
309 --
310 end if; -- no_all_inclusive_error 2
311 --
312 end if; -- no_all_inclusive_error 1
313 --
314 hr_utility.set_location('Leaving:'||l_proc,10);
315 --
316 End chk_qual_overlap;
317 --
318 --
319 -- ----------------------------------------------------------------------------
320 -- |-------------------------< chk_qual_overlap >---------------------------|
321 -- ----------------------------------------------------------------------------
322 Procedure chk_qual_overlap
323 (p_rec in per_qat_shd.g_rec_type
324 ,p_qualification_id in number default NULL
325 ) is
326 --
327 l_proc varchar2(72) := g_package||'validate_translation';
328 --
329 -- Declare cursor
330 --
331 cursor csr_qualification is
332 select qau.qualification_type_id
333 ,qau.person_id
334 ,qau.attendance_id
335 ,qau.business_group_id
336 ,qau.object_version_number
337 ,qau.start_date
338 ,qau.end_date
339 ,qau.party_id
340 from per_qualifications qau
341 where qau.qualification_id = NVL(p_rec.qualification_id, p_qualification_id);
342 --
343 l_qau_rec csr_qualification%ROWTYPE;
344 --
345 Begin
346 --
347 hr_utility.set_location('Entering:'||l_proc,5);
348 --
349 open csr_qualification;
350 --
351 fetch csr_qualification into l_qau_rec;
352 --
353 close csr_qualification;
354 --
355 chk_qual_overlap
356 (p_qualification_id => NVL(p_rec.qualification_id,
357 p_qualification_id)
358 ,p_qualification_type_id => l_qau_rec.qualification_type_id
359 ,p_person_id => l_qau_rec.person_id
360 ,p_attendance_id => l_qau_rec.attendance_id
361 ,p_business_group_id => l_qau_rec.business_group_id
362 ,p_start_date => l_qau_rec.start_date
363 ,p_end_date => l_qau_rec.end_date
364 ,p_title => p_rec.title
365 ,p_object_version_number => l_qau_rec.object_version_number
366 ,p_party_id => l_qau_rec.party_id
367 ,p_language => p_rec.language);
368 --
369 hr_utility.set_location('Leaving:'||l_proc,10);
370 --
371 End chk_qual_overlap;
372 --
373 -- ----------------------------------------------------------------------------
374 -- |---------------------------< insert_validate >----------------------------|
375 -- ----------------------------------------------------------------------------
376 Procedure insert_validate
377 (p_rec in per_qat_shd.g_rec_type
378 ,p_qualification_id in number
379 ) is
380 --
381 l_proc varchar2(72) := g_package||'insert_validate';
382 --
383 Begin
384 hr_utility.set_location('Entering:'||l_proc, 5);
385 --
386 -- Call all supporting business operations
387 --
388 chk_qual_overlap
389 (p_rec => p_rec
390 ,p_qualification_id => p_qualification_id
391 );
392 --
393 --
394 hr_utility.set_location(' Leaving:'||l_proc, 10);
395 End insert_validate;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |---------------------------< update_validate >----------------------------|
399 -- ----------------------------------------------------------------------------
400 Procedure update_validate
401 (p_rec in per_qat_shd.g_rec_type
402 ) is
403 --
404 l_proc varchar2(72) := g_package||'update_validate';
405 --
406 Begin
407 hr_utility.set_location('Entering:'||l_proc, 5);
408 --
409 -- Call all supporting business operations
410 --
411 --
412 -- set_security_group_id(p_qualification_id => p_rec.qualification_id);
413 --
414 -- Validate Dependent Attributes
415 --
416 chk_non_updateable_args
417 (p_rec => p_rec
418 );
419 --
420 chk_qual_overlap
421 (p_rec => p_rec
422 );
423 --
424 hr_utility.set_location(' Leaving:'||l_proc, 10);
425 End update_validate;
426 --
427 -- ----------------------------------------------------------------------------
428 -- |---------------------------< delete_validate >----------------------------|
429 -- ----------------------------------------------------------------------------
430 Procedure delete_validate
431 (p_rec in per_qat_shd.g_rec_type
432 ) is
433 --
434 l_proc varchar2(72) := g_package||'delete_validate';
435 --
436 Begin
437 hr_utility.set_location('Entering:'||l_proc, 5);
438 --
439 -- Call all supporting business operations
440 --
441 hr_utility.set_location(' Leaving:'||l_proc, 10);
442 End delete_validate;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |-----------------------< set_translation_globals >------------------------|
446 -- ----------------------------------------------------------------------------
447 PROCEDURE set_translation_globals
448 (p_qualification_type_id in number
449 ,p_person_id in number
450 ,p_attendance_id in number
451 ,p_business_group_id in number
452 ,p_object_version_number in number
453 ,p_start_date in date
454 ,p_end_date in date
455 ,p_party_id in number
456 ) IS
457 --
458 l_proc varchar2(72) := g_package||'set_translation_globals';
459 --
460 BEGIN
461 --
462 hr_utility.set_location('Entering:'||l_proc,5);
463 --
464 g_qualification_type_id := p_qualification_type_id;
465 g_person_id := p_person_id;
466 g_attendance_id := p_attendance_id;
467 g_business_group_id := p_business_group_id;
468 g_object_version_number := p_object_version_number;
469 g_start_date := p_start_date;
470 g_end_date := p_end_date;
471 g_party_id := p_party_id;
472 --
473 hr_utility.set_location('Leaving:'||l_proc,10);
474 --
475 END;
476 --
477 -- ----------------------------------------------------------------------------
478 -- |------------------------< validate_translation>--------------------------|
479 -- ----------------------------------------------------------------------------
480 Procedure validate_translation
481 (p_qualification_id in number
482 ,p_language in varchar2
483 ,p_title in varchar2
484 ,p_group_ranking in varchar2
485 ,p_license_restrictions in varchar2
486 ,p_awarding_body in varchar2
487 ,p_grade_attained in varchar2
488 ,p_reimbursement_arrangements in varchar2
489 ,p_training_completed_units in varchar2
490 ,p_membership_category in varchar2
491 ,p_qualification_type_id in number default null
492 ,p_person_id in number default null
493 ,p_attendance_id in number default null
494 ,p_business_group_id in number default null
495 ,p_object_version_number in number default null
496 ,p_start_date in date default null
497 ,p_end_date in date default null
498 ,p_party_id in number default null
499 ) is
500 --
501 l_proc varchar2(72) := g_package||'validate_translation';
502 --
503 Begin
504 --
505 hr_utility.set_location('Entering:'||l_proc,5);
506 --
507 chk_qual_overlap
508 (p_qualification_id => p_qualification_id
509 ,p_qualification_type_id => nvl(p_qualification_type_id,
510 g_qualification_type_id)
511 ,p_person_id => nvl(p_person_id, g_person_id)
512 ,p_attendance_id => nvl(p_attendance_id, g_attendance_id)
513 ,p_business_group_id => nvl(p_business_group_id, g_business_group_id)
514 ,p_start_date => nvl(p_start_date, g_start_date)
515 ,p_end_date => nvl(p_end_date, g_end_date)
516 ,p_title => p_title
517 ,p_object_version_number => nvl(p_object_version_number,
518 g_object_version_number)
519 ,p_party_id => nvl(p_party_id, g_party_id)
520 ,p_language => p_language);
521 --
522 hr_utility.set_location('Leaving:'||l_proc,10);
523 --
524 End validate_translation;
525 --
526 end per_qat_bus;
[Home] [Help]