1 Package Body per_apt_bus as
2 /* $Header: peaptrhi.pkb 120.4.12010000.3 2008/08/06 08:57:55 ubhat ship $ */
3
4 -- ---------------------------------------------------------------------------+
5 -- | Private Global Definitions |
6 -- ---------------------------------------------------------------------------+
7
8 g_package varchar2(33) := ' per_apt_bus.'; -- Global package name
9
10 -- The following two global variables are only to be used by the
11 -- return_legislation_code function.
12
13 g_appraisal_template_id number default null;
14 g_legislation_code varchar2(150) default null;
15 -- -------------------------------------------------------------------------+
16 -- |----------------------< chk_non_updateable_args >-----------------------|
17 -- -------------------------------------------------------------------------+
18
19 Procedure chk_non_updateable_args(p_rec in per_apt_shd.g_rec_type) is
20
21 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
22 l_error exception;
23 l_argument varchar2(30);
24
25 Begin
26 hr_utility.set_location('Entering:'||l_proc, 5);
27
28 -- Only proceed with validation if a row exists for
29 -- the current record in the HR Schema
30
31 if not per_apt_shd.api_updating
32 (p_appraisal_template_id => p_rec.appraisal_template_id
33 ,p_object_version_number => p_rec.object_version_number
34 ) then
35 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36 hr_utility.set_message_token('PROCEDURE', l_proc);
37 hr_utility.set_message_token('STEP', '5');
38 end if;
39
40 hr_utility.set_location(l_proc, 6);
41
42 if p_rec.business_group_id <> per_apt_shd.g_old_rec.business_group_id then
43 l_argument := 'business_group_id';
44 raise l_error;
45 end if;
46 hr_utility.set_location(l_proc, 11);
47
48 exception
49 when l_error then
50 hr_api.argument_changed_error
51 (p_api_name => l_proc
52 ,p_argument => l_argument);
53 when others then
54 raise;
55 hr_utility.set_location(' Leaving:'||l_proc, 12);
56 end chk_non_updateable_args;
57
58 ----------------------------------------------------------------------------+
59 ------------------------------------<chk_name>------------------------------+
60 ----------------------------------------------------------------------------+
61
62 -- Description:
63 -- - Validates that the appraisal template name has been entered as it
64 -- is a mandatory column
65 -- - Validates that the name is unique within business group
66
67 -- Pre_conditions:
68 -- Valid business_group_id
69
70 -- In Arguments:
71 -- p_appraisal_template_id
72 -- p_name
73 -- p_object_vesrion_number
74 -- p_business_group_id
75
76 -- Post Success:
77 -- Process continues if :
78 -- All the in parameters are valid.
79
80 -- Post Failure:
81 -- An application error is raised and processing is terminated if any of
82 -- the following cases are found :
83 -- -- name is not entered
84 -- -- name is not unique within business group
85
86 -- Access Status
87 -- Internal Table Handler Use Only.
88
89
90 procedure chk_name
91 (p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
92 ,p_name in per_appraisal_templates.name%TYPE
93 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
94 ,p_business_group_id in per_appraisal_templates.business_group_id%TYPE
95 )
96 is
97
98 l_exists varchar2(1);
99 l_business_group_id per_appraisal_templates.business_group_id%TYPE;
100 l_api_updating boolean;
101 l_proc varchar2(72) := g_package||'chk_name';
102
103
104 -- Cursor to check if name is unique
105
106 Cursor csr_name_unique
107 is
108 select business_group_id
109 from per_appraisal_templates apt
110 where ( (p_appraisal_template_id is null)
111 or(p_appraisal_template_id <> apt.appraisal_template_id)
112 )
113 and apt.name = p_name and
114 ( business_group_id is null or p_business_group_id is null or business_group_id = p_business_group_id);
115 begin
116 hr_utility.set_location('Entering:'|| l_proc, 1);
117
118
119
120 -- Only proceed with validation if :
121 -- a) The current g_old_rec is current and
122 -- b) The value for name has changed
123
124 l_api_updating := per_apt_shd.api_updating
125 (p_appraisal_template_id => p_appraisal_template_id
126 ,p_object_version_number => p_object_version_number);
127
128 if ( (l_api_updating and (per_apt_shd.g_old_rec.name
129 <> nvl(p_name,hr_api.g_varchar2))
130 ) or
131 (NOT l_api_updating)
132 ) then
133
134 -- check if the user has entered a name
135 -- It is mandatory column.
136
137 hr_utility.set_location(l_proc, 3);
138 if p_name is null then
139 hr_utility.set_message(801,'HR_51907_APT_NAME_MANDATORY');
140 hr_utility.raise_error;
141 end if;
142
143 hr_utility.set_location(l_proc, 4);
144 open csr_name_unique;
145 fetch csr_name_unique into l_business_group_id;
146 if csr_name_unique%found then
147 close csr_name_unique;
148 hr_utility.set_message(801,'HR_51908_APT_NAME_NOT_UNIQUE');
149 hr_utility.raise_error;
150 end if;
151 close csr_name_unique;
152 end if;
153
154 hr_utility.set_location(l_proc, 5);
155
156 hr_utility.set_location('Leaving: '|| l_proc, 10);
157
158 end chk_name;
159
160
161 -- ------------------------------------------------------------------------+
162 -- |------------------------< chk_template_dates >-------------------------|
163 -- ------------------------------------------------------------------------+
164
165 -- Description :
166 -- Perform check to make sure that :
167 -- - Validates that the date_from and date_to are valid if either of
168 -- then is not null
169 -- - Validates that the date_from is less then or equal to date_to
170 -- - Validates that date_to is later or equal to date_from
171
172 -- - if updating then Validates that the template date_from and date_to do not
173 -- invalidate the appraisal ie. template date_from has to be less than or equal
174 -- to appraisal start date and template date_to has to be greater
175 -- than or equal to appraisal end date
176
177 -- Pre-requisites
178 -- Valid appraisal_template_id
179
180 -- In Prameters
181 -- p_date_from
182 -- p_date_to
183 -- p_appraisal_template_id
184 -- p_object_version_number
185
186 -- Post Success
187 -- Processing continues.
188
189 -- Post Failure
190 -- An application error is raised and processing is terminated if any of
191 -- the following cases are found :
192 -- - date_from and date_to are not set, if either of them is set
193 -- - date_from is less than or equal to date_to
194 -- - date_to is greater than or equal to date_from
195 -- - Template dates do not invalidate the Appraisal
196
197 -- Access Status
198 -- Internal Development Use Only
199
200 Procedure chk_template_dates
201 (p_date_from in per_appraisal_templates.date_from%TYPE
202 ,p_date_to in per_appraisal_templates.date_to%TYPE
203 ,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
204 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
205 ) is
206
207 l_exists varchar2(1);
208 l_api_updating boolean;
209 l_proc varchar2(72):=g_package||'chk_template_dates';
210
211 Cursor csr_check_dates_in_apr is
212 select 'Y'
213 from per_appraisals apr
214 where ( apr.appraisal_period_start_date < nvl(p_date_from,hr_api.g_sot)
215 or apr.appraisal_period_end_date > nvl(p_date_to,hr_api.g_eot)
216 )
217 and apr.appraisal_template_id = p_appraisal_template_id;
218
219
220 begin
221 hr_utility.set_location('Entering:'|| l_proc, 1);
222
223 -- Check if date from is set to null when date to exists
224 if ( (p_date_from is null) AND (p_date_to is not null) )
225 then
226 hr_utility.set_message(800, 'HR_52247_APT_DATE_FROM_NULL');
227 hr_utility.raise_error;
228 end if;
229 -- Date From has to be <= date to, else error.
230 if ( nvl(p_date_from,hr_api.g_sot) > nvl(p_date_to,hr_api.g_eot) )
231 then
232 hr_utility.set_message(801, 'HR_51909_APT_DATE_FROM_BEFORE');
233 hr_utility.raise_error;
234 end if;
235 -- Date To has to be >= date to, else error.
236 if ( nvl(p_date_to,hr_api.g_eot) < nvl(p_date_from,hr_api.g_sot) )
237 then
238 hr_utility.set_message(801, 'HR_51910_APT_DATE_TO_AFTER');
239 hr_utility.raise_error;
240 end if;
241
242 l_api_updating := per_apt_shd.api_updating
243 (p_appraisal_template_id => p_appraisal_template_id
244 ,p_object_version_number => p_object_version_number);
245
246 -- Only continue if:
247 -- a) The current g_old_rec is current and
248 -- b) The value for dates have changed
249
250 if ( ((l_api_updating) and (per_apt_shd.g_old_rec.date_from <> nvl(p_date_from,hr_api.g_date)) )
251 or ((l_api_updating) and (per_apt_shd.g_old_rec.date_to <> nvl(p_date_to,hr_api.g_date)) )
252 ) then
253
254 -- only continue if we are updating and the dates have changed from the
255 -- previous values
256
257 -- Apart from having the standard date validation check ie.date from <= date_to
258 -- and date_to >= date_from, need to make sure that the user cannot change a date
259 -- so as to invalidate the dates on the appraisals.
260 -- Check dates against appraisals
261
262 open csr_check_dates_in_apr;
263 fetch csr_check_dates_in_apr into l_exists;
264 if csr_check_dates_in_apr%found then
265 hr_utility.set_location(l_proc, 3);
266 -- dates out of range
267 close csr_check_dates_in_apr ;
268 hr_utility.set_message(801,'HR_51911_APT_INVALIDATE_APR');
269 hr_utility.raise_error;
270 end if;
271 close csr_check_dates_in_apr;
272
273 end if;
274
275 hr_utility.set_location('Leaving:'|| l_proc, 10);
276
277 end chk_template_dates;
278
279 ----------------------------------------------------------------------------+
280 ------------------------------<chk_assessment_type>-------------------------+
281 ----------------------------------------------------------------------------+
282
283 -- Description:
284 -- - Validates that the assessment type exists and is within the same business
285 -- group as that of appraisal template
286 -- - Validates that the assessment type exists as of the template dates
287
288 -- Pre_conditions:
289
290
291 -- In Arguments:
292 -- p_appraisal_template_id
293 -- p_object_version_number
294 -- p_business_group_id
295 -- p_assessment_type_id
296
297 -- Post Success:
298 -- Process continues if :
299 -- All the in parameters are valid.
300
301 -- Post Failure:
302 -- An application error is raised and processing is terminated if any of
303 -- the following cases are found :
304 -- -- appraisal_template_id is not set
305 -- -- assessment type does not exist
306 -- -- assessment type exists but not with the same business group
307 -- -- appraisal type exists but not as of the template dates
308
309 -- Access Status
310 -- Internal Table Handler Use Only.
311
312 procedure chk_assessment_type
313 (p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
314 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
315 ,p_assessment_type_id in per_appraisal_templates.assessment_type_id%TYPE
316 ,p_date_from in per_appraisal_templates.date_from%TYPE
317 ,p_date_to in per_appraisal_templates.date_to%TYPE
318 ,p_business_group_id in per_appraisal_templates.business_group_id%TYPE
319 )
320 is
321
322 l_exists varchar2(1);
323 l_api_updating boolean;
324 l_proc varchar2(72) := g_package||'chk_assessment_type';
325 l_business_group_id per_appraisal_templates.business_group_id%TYPE;
326
327
328 -- Cursor to check if appraisal exists
329
330 Cursor csr_appraisal_type_exists
331 is
332 select business_group_id
333 from per_assessment_types
334 where assessment_type_id = p_assessment_type_id
335 and type = 'COMPETENCE';
336
337 -- Cursor to check if the assessment type is
338 -- valid as of appraisal template dates
339 -- Bug 3947233 Starts Here
340 -- Desc: Modified the p_date_from nvl into g_eot from g_sot and
341 -- p_date_to nvl into g_sot from g_eot.
342 Cursor csr_assessment_type_valid
343 is
344 select 'Y'
345 from per_assessment_types
346 where assessment_type_id = p_assessment_type_id
347 and nvl(date_from,hr_api.g_sot) <= nvl(p_date_from,hr_api.g_eot)
348 and nvl(date_to,hr_api.g_eot) >= nvl(p_date_to,hr_api.g_sot) ;
349 -- Bug 3947233 Ends Here
350
351 begin
352 hr_utility.set_location('Entering:'|| l_proc, 1);
353
354 -- Check mandatory parameters have been set
355
356
357 hr_utility.set_location('Entering:'|| l_proc, 2);
358
359 l_api_updating := per_apt_shd.api_updating
360 (p_appraisal_template_id => p_appraisal_template_id
361 ,p_object_version_number => p_object_version_number);
362
363 -- Only continue if:
364 -- a) The current g_old_rec is current and
368 and (nvl(per_apt_shd.g_old_rec.assessment_type_id,hr_api.g_number)
365 -- b) The value for assessment type has changed
366
367 if ( (l_api_updating
369 <> nvl(p_assessment_type_id,hr_api.g_number))
370 ) or
371 (NOT l_api_updating)
372 ) then
373
374 if p_assessment_type_id is not null then
375 open csr_appraisal_type_exists;
376 fetch csr_appraisal_type_exists into l_business_group_id;
377 if csr_appraisal_type_exists%notfound then
378 close csr_appraisal_type_exists;
379 hr_utility.set_message(801,'HR_51912_APT_AST_NOT_EXIST');
380 hr_utility.raise_error;
381 end if;
382 close csr_appraisal_type_exists;
383
384 -- check if assessment is in the same business group
385
386 if nvl(l_business_group_id,-1) <> nvl(p_business_group_id,-1) then
387 hr_utility.set_message(800,'HR_51913_APT_AST_DIFF_BUS_GRP');
388 hr_utility.raise_error;
389 end if;
390
391 -- check if assessment type exists with the date range of the
392 -- appraisal template
393
394 open csr_assessment_type_valid;
395 fetch csr_assessment_type_valid into l_exists;
396 if csr_assessment_type_valid%notfound then
397 close csr_assessment_type_valid;
398 hr_utility.set_message(801,'HR_51914_APT_AST_NOT_DATE');
399 hr_utility.raise_error;
400 end if;
401 close csr_assessment_type_valid;
402 end if;
403
404 end if;
405
406 hr_utility.set_location(l_proc, 4);
407
408 hr_utility.set_location('Leaving: '|| l_proc, 10);
409
410 end chk_assessment_type;
411
412 ----------------------------------------------------------------------------+
413 ------------------------------<chk_objective_asmnt_type>-------------------------+
414 ----------------------------------------------------------------------------+
415
416 -- Description:
417 -- - Validates that the objective assessment type exists and is within the same business
418 -- group as that of appraisal template
419 -- - Validates that the objective assessment type exists as of the template dates
420
421 -- Pre_conditions:
422
423
424 -- In Arguments:
425 -- p_appraisal_template_id
426 -- p_object_version_number
427 -- p_business_group_id
428 -- p_objective_asmnt_type_id
429
430 -- Post Success:
431 -- Process continues if :
432 -- All the in parameters are valid.
433
434 -- Post Failure:
435 -- An application error is raised and processing is terminated if any of
436 -- the following cases are found :
437 -- -- appraisal_template_id is not set
438 -- -- objective assessment type does not exist
439 -- -- objective assessment type exists but not with the same business group
440 -- -- appraisal type exists but not as of the template dates
441
442 -- Access Status
443 -- Internal Table Handler Use Only.
444
445 procedure chk_objective_asmnt_type
446 (p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
447 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
448 ,p_objective_asmnt_type_id in per_appraisal_templates.objective_asmnt_type_id%TYPE
449 ,p_date_from in per_appraisal_templates.date_from%TYPE
450 ,p_date_to in per_appraisal_templates.date_to%TYPE
451 ,p_business_group_id in per_appraisal_templates.business_group_id%TYPE
452 )
453 is
454
455 l_exists varchar2(1);
456 l_api_updating boolean;
457 l_proc varchar2(72) := g_package||'chk_objective_asmnt_type';
458 l_business_group_id per_appraisal_templates.business_group_id%TYPE;
459
460
461 -- Cursor to check if appraisal exists
462
463 Cursor csr_appraisal_type_exists
464 is
465 select business_group_id
466 from per_assessment_types
467 where assessment_type_id = p_objective_asmnt_type_id
468 and type = 'OBJECTIVE';
469
470 -- Cursor to check if the assessment type is
471 -- valid as of appraisal template dates
472 -- Bug 3947233 Starts Here
473 -- Desc: Modified the p_date_from nvl into g_eot from g_sot and
474 -- p_date_to nvl into g_sot from g_eot.
475 Cursor csr_assessment_type_valid
476 is
477 select 'Y'
478 from per_assessment_types
479 where assessment_type_id = p_objective_asmnt_type_id
480 and nvl(date_from,hr_api.g_sot) <= nvl(p_date_from,hr_api.g_eot)
481 and nvl(date_to,hr_api.g_eot) >= nvl(p_date_to,hr_api.g_sot) ;
482 -- Bug 3947233 Ends Here
483
484 begin
485 hr_utility.set_location('Entering:'|| l_proc, 1);
486
487
488
489 l_api_updating := per_apt_shd.api_updating
490 (p_appraisal_template_id => p_appraisal_template_id
491 ,p_object_version_number => p_object_version_number);
492
493 -- Only continue if:
494 -- a) The current g_old_rec is current and
495 -- b) The value for assessment type has changed
496
497 if ( (l_api_updating
498 and (nvl(per_apt_shd.g_old_rec.objective_asmnt_type_id,hr_api.g_number)
499 <> nvl(p_objective_asmnt_type_id,hr_api.g_number))
500 ) or
501 (NOT l_api_updating)
502 ) then
503
507 if csr_appraisal_type_exists%notfound then
504 if p_objective_asmnt_type_id is not null then
505 open csr_appraisal_type_exists;
506 fetch csr_appraisal_type_exists into l_business_group_id;
508 close csr_appraisal_type_exists;
509 hr_utility.set_message(800,'HR_APT_OAST_NOT_EXIST');
510 hr_utility.raise_error;
511 end if;
512 close csr_appraisal_type_exists;
513
514 -- check if assessment is in the same business group
515
516 if nvl(l_business_group_id,-1) <> nvl(p_business_group_id,-1) then
517 hr_utility.set_message(800,'HR_APT_OAST_DIFF_BUS_GRP');
518 hr_utility.raise_error;
519 end if;
520
521 -- check if assessment type exists with the date range of the
522 -- appraisal template
523
524 open csr_assessment_type_valid;
525 fetch csr_assessment_type_valid into l_exists;
526 if csr_assessment_type_valid%notfound then
527 close csr_assessment_type_valid;
528 hr_utility.set_message(800,'HR_APT_OAST_NOT_DATE');
529 hr_utility.raise_error;
530 end if;
531 close csr_assessment_type_valid;
532 end if;
533
534 end if;
535
536 hr_utility.set_location(l_proc, 4);
537
538 hr_utility.set_location('Leaving: '|| l_proc, 10);
539
540 end chk_objective_asmnt_type;
541
542 ----------------------------------------------------------------------------+
543 -----------------------------<chk_question_template>------------------------+
544 ----------------------------------------------------------------------------+
545
546 -- Description:
547 -- - Validates that the questionnaire template exists
548 -- - Validates that if the questionnaire template, it is in the correct
549 -- business group
550 -- - Validates that the questionnaire template cannot be updated if the
551 -- questuionnaire answer set, or in an appraisal
552
553 -- Pre_conditions:
554
555 -- In Arguments:
556 -- p_object_version_number
557 -- p_questionnaire_template_id
558 -- p_business_group_id
559 -- p_appraisal_template_id
560
561 -- Post Success:
562 -- Process continues if :
563 -- All the in parameters are valid.
564
565 -- Post Failure:
566 -- An application error is raised and processing is terminated if any of
567 -- the following cases are found :
568 -- -- quationnaire_template_id does not exist
569 -- -- questionnaire template exists but is not of type 'APPRAISAL'
570 -- -- questionnaire template exists and is used in appraisals which is further
571 -- -- used in Question and Answers
572
573 -- Access Status
574 -- Internal Table Handler Use Only.
575
576 procedure chk_question_template
577 (p_object_version_number in per_appraisal_templates.object_version_number%TYPE
578 ,p_questionnaire_template_id in per_appraisal_templates.questionnaire_template_id%TYPE
579 ,p_business_group_id in per_appraisal_templates.business_group_id%TYPE
580 ,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
581 )
582 is
583
584 l_exists varchar2(1);
585 l_api_updating boolean;
586 l_proc varchar2(72) := g_package||'chk_question_template';
587 l_bus_grp_id hr_questionnaires.business_group_id%TYPE;
588 -- l_type per_proposal_templates.type%TYPE;
589
590 -- Cursor to check if appraisal exists
591
592 Cursor csr_question_template_exists
593 is
594 select business_group_id
595 from hr_questionnaires qsn
596 where qsn.questionnaire_template_id = p_questionnaire_template_id;
597
598 -- Cursor to check if the questionnnaire template
599 -- can be updated
600
601 -- Cursor csr_question_update
602 -- is
603 -- select 'Y'
604 -- from per_appraisals apr
605 -- ,per_assign_proposal_answers apa
606 -- where apr.appraisal_id = apa.answer_for_key
607 -- and apa.type = 'APPRAISAL'
608 -- and apr.appraisal_template_id = p_appraisal_template_id;
609
610 -- Cursor to check if questionnaire template can be updated,
611 -- according to hr_quest_answers.
612 cursor csr_question_update1 is
613 select 'Y'
614 from hr_quest_answers hqa
615 where hqa.type = 'APPRAISAL'
616 and hqa.type_object_id in (select appraisal_id
617 from per_appraisals apr
618 where apr.appraisal_template_id
619 = p_appraisal_template_id);
620 -- Cursor to check if questionnaire template can be updated,
621 -- according to per_participants.
622 cursor csr_question_update2 is
623 select 'Y'
624 from per_participants par
625 where par.participation_in_table = 'PER_APPRAISALS'
626 and par.participation_in_id in (select appraisal_id
627 from per_appraisals apr
628 where apr.appraisal_template_id
629 = p_appraisal_template_id);
630
631 begin
632 hr_utility.set_location('Entering:'|| l_proc, 1);
633
637
634 l_api_updating := per_apt_shd.api_updating
635 (p_appraisal_template_id => p_appraisal_template_id
636 ,p_object_version_number => p_object_version_number);
638 -- Perfom the following check when inserting only
639
640 if (NOT l_api_updating) then
641 -- check if template exists
642 if p_questionnaire_template_id is not null then
643 hr_utility.set_location(l_proc, 3);
644 open csr_question_template_exists;
645 fetch csr_question_template_exists into l_bus_grp_id;
646 if csr_question_template_exists%notfound then
647 close csr_question_template_exists;
648 hr_utility.set_message(800,'HR_51915_APT_QST_NOT_EXIST');
649 hr_utility.raise_error;
650 end if;
651 close csr_question_template_exists;
652
653 -- check if questionnaire template is of type 'APPRAISAL'
654
655 hr_utility.set_location(l_proc, 6);
656 -- if l_type <> 'APPRAISAL' then
657 -- hr_utility.set_message(801,'HR_51916_APT_QST_INV_TYPE');
658 -- hr_utility.raise_error;
659 -- end if;
660
661 -- Check if business_group_id is correct for the given questionnaire
662 -- template id.
663
664 if nvl(l_bus_grp_id,-1) <> nvl(p_business_group_id,-1) then
665 fnd_message.set_name('PER','PER_52470_APT_TEMP_NOT_IN_BG');
666 fnd_message.raise_error;
667 end if;
668 end if;
669
670 end if;
671
672 -- Perform the following check only if updating and the value of
673 -- questionnaire_template_id has changed
674
675 -- Added nvl around per_apt_shd.g_old_rec.questionnaire_template_id
676 if ( (l_api_updating and ( nvl(per_apt_shd.g_old_rec.questionnaire_template_id, hr_api.g_number)
677 <> nvl(p_questionnaire_template_id,hr_api.g_number)) )
678 )
679 then
680
681 -- Check if the questionnaire template can be updated first.
682
683 -- hr_utility.set_location(l_proc, 7);
684 -- open csr_question_update;
685 -- fetch csr_question_update into l_exists;
686 -- if csr_question_update%found then
687 -- close csr_question_update;
688 -- hr_utility.set_message(801,'HR_51917_APT_USED_IN_APR_ANS');
689 -- hr_utility.raise_error;
690 -- end if;
691 -- close csr_question_update;
692 open csr_question_update1;
693 fetch csr_question_update1 into l_exists;
694 if csr_question_update1%found then
695 close csr_question_update1;
696 fnd_message.set_name('PER','PER_52471_APT_TEMPLATE_IN_USE');
697 fnd_message.raise_error;
698 end if;
699 close csr_question_update1;
700
701 open csr_question_update2;
702 fetch csr_question_update2 into l_exists;
703 if csr_question_update2%found then
704 close csr_question_update2;
705 fnd_message.set_name('PER','PER_52471_APT_TEMPLATE_IN_USE');
706 fnd_message.raise_error;
707 end if;
708 close csr_question_update2;
709
710
711
712 -- check if questionnaire template is of type 'APPRAISAL'
713
714 -- hr_utility.set_location(l_proc, 9);
715 -- if l_type <> 'APPRAISAL' then
716 -- hr_utility.set_message(801,'HR_51916_APT_QST_INV_TYPE');
717 -- hr_utility.raise_error;
718 -- end if;
719
720 -- check if questionnaire template is of correct business_group
721
722 end if;
723
724 hr_utility.set_location(l_proc, 10);
725
726 hr_utility.set_location('Leaving: '|| l_proc, 11);
727
728 end chk_question_template;
729
730
731 ----------------------------------------------------------------------------+
732 -----------------------------<chk_ma_question_template>------------------------+
733 ----------------------------------------------------------------------------+
734
735 -- Description:
736 -- - Validates that the questionnaire template exists
737 -- - Validates that if the questionnaire template, it is in the correct
738 -- business group
739 -- - Validates that the questionnaire template cannot be updated if the
740 -- questuionnaire answer set, or in an appraisal
741
742 -- Pre_conditions:
743
744 -- In Arguments:
745 -- p_object_version_number
746 -- p_ma_quest_template_id
747 -- p_business_group_id
748 -- p_appraisal_template_id
749
750 -- Post Success:
751 -- Process continues if :
752 -- All the in parameters are valid.
753
754 -- Post Failure:
755 -- An application error is raised and processing is terminated if any of
756 -- the following cases are found :
757 -- -- quationnaire_template_id does not exist
758 -- -- questionnaire template exists but is not of type 'APPRAISAL'
759 -- -- questionnaire template exists and is used in appraisals which is further
760 -- -- used in Question and Answers
761
762 -- Access Status
763 -- Internal Table Handler Use Only.
764
765 procedure chk_ma_question_template
766 (p_object_version_number in per_appraisal_templates.object_version_number%TYPE
767 ,p_ma_quest_template_id in per_appraisal_templates.ma_quest_template_id%TYPE
771 is
768 ,p_business_group_id in per_appraisal_templates.business_group_id%TYPE
769 ,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
770 )
772
773 l_exists varchar2(1);
774 l_api_updating boolean;
775 l_proc varchar2(72) := g_package||'chk_ma_question_template';
776 l_bus_grp_id hr_questionnaires.business_group_id%TYPE;
777 -- l_type per_proposal_templates.type%TYPE;
778
779 -- Cursor to check if appraisal exists
780
781 Cursor csr_question_template_exists
782 is
783 select business_group_id
784 from hr_questionnaires qsn
785 where qsn.questionnaire_template_id = p_ma_quest_template_id;
786
787 -- Cursor to check if the questionnnaire template
788 -- can be updated
789
790 -- Cursor csr_question_update
791 -- is
792 -- select 'Y'
793 -- from per_appraisals apr
794 -- ,per_assign_proposal_answers apa
795 -- where apr.appraisal_id = apa.answer_for_key
796 -- and apa.type = 'APPRAISAL'
797 -- and apr.appraisal_template_id = p_appraisal_template_id;
798
799 -- Cursor to check if questionnaire template can be updated,
800 -- according to hr_quest_answers.
801 cursor csr_question_update1 is
802 select 'Y'
803 from hr_quest_answers hqa
804 where hqa.type = 'APPRAISAL'
805 and hqa.type_object_id in (select appraisal_id
806 from per_appraisals apr
807 where apr.appraisal_template_id
808 = p_appraisal_template_id);
809 -- Cursor to check if questionnaire template can be updated,
810 -- according to per_participants.
811 cursor csr_question_update2 is
812 select 'Y'
813 from per_participants par
814 where par.participation_in_table = 'PER_APPRAISALS'
815 and par.participation_in_id in (select appraisal_id
816 from per_appraisals apr
817 where apr.appraisal_template_id
818 = p_appraisal_template_id);
819
820 begin
821 hr_utility.set_location('Entering:'|| l_proc, 1);
822
823 l_api_updating := per_apt_shd.api_updating
824 (p_appraisal_template_id => p_appraisal_template_id
825 ,p_object_version_number => p_object_version_number);
826
827 -- Perfom the following check when inserting only
828
829 if (NOT l_api_updating) then
830 -- check if template exists
831 if p_ma_quest_template_id is not null then
832 hr_utility.set_location(l_proc, 3);
833 open csr_question_template_exists;
834 fetch csr_question_template_exists into l_bus_grp_id;
835 if csr_question_template_exists%notfound then
836 close csr_question_template_exists;
837 hr_utility.set_message(800,'HR_APT_MA_QST_NOT_EXIST');
838 hr_utility.raise_error;
839 end if;
840 close csr_question_template_exists;
841
842
843
844 -- Check if business_group_id is correct for the given questionnaire
845 -- template id.
846
847 if nvl(l_bus_grp_id,-1) <> nvl(p_business_group_id,-1) then
848 fnd_message.set_name('PER','PER_52470_APT_TEMP_NOT_IN_BG');
849 fnd_message.raise_error;
850 end if;
851 end if;
852
853 end if;
854
855
856 -- Perform the following check only if updating and the value of
857 -- ma_quest_template_id has changed
858
859 -- Added nvl around per_apt_shd.g_old_rec.ma_quest_template_id
860 if ( (l_api_updating and ( nvl(per_apt_shd.g_old_rec.ma_quest_template_id, hr_api.g_number)
861 <> nvl(p_ma_quest_template_id,hr_api.g_number)) )
862 )
863 then
864
865 -- Check if the questionnaire template can be updated first.
866
867 -- hr_utility.set_location(l_proc, 7);
868 -- open csr_question_update;
869 -- fetch csr_question_update into l_exists;
870 -- if csr_question_update%found then
871 -- close csr_question_update;
872 -- hr_utility.set_message(801,'HR_51917_APT_USED_IN_APR_ANS');
873 -- hr_utility.raise_error;
874 -- end if;
875 -- close csr_question_update;
876 open csr_question_update1;
877 fetch csr_question_update1 into l_exists;
878 if csr_question_update1%found then
879 close csr_question_update1;
880 fnd_message.set_name('PER','PER_52471_APT_TEMPLATE_IN_USE');
881 fnd_message.raise_error;
882 end if;
883 close csr_question_update1;
884
885 open csr_question_update2;
886 fetch csr_question_update2 into l_exists;
887 if csr_question_update2%found then
888 close csr_question_update2;
889 fnd_message.set_name('PER','PER_52471_APT_TEMPLATE_IN_USE');
893
890 fnd_message.raise_error;
891 end if;
892 close csr_question_update2;
894
895
896
897 -- check if questionnaire template is of correct business_group
898
899 if l_bus_grp_id <> p_business_group_id then
900 hr_utility.set_message(801,'PER_52470_TEMP_NOT_IN_BG');
901 hr_utility.raise_error;
902 end if;
903 end if;
904
905 hr_utility.set_location(l_proc, 10);
906
907 hr_utility.set_location('Leaving: '|| l_proc, 11);
908
909 end chk_ma_question_template;
910
911 ----------------------------------------------------------------------------+
912 -------------------------------<chk_rating_scale>---------------------------+
913 ----------------------------------------------------------------------------+
914
915 -- Description:
916 -- - Validates that the rating scale exists and is within the same
917 -- business group as that of appraisal template
918 -- - Validates that if the rating scale, it is of type 'PERFORMANCE'
919 -- - Validates that the rating scale cannot be updated if overall_rating_id
920 -- is set
921 -- - Validates that the rating scale cannot be updated if the
922 -- appraisal template is used in an Appraisal which in turn is used in
923 -- performace ratings table (per_performance_ratings).
924
925 -- Pre_conditions:
926
927 -- In Arguments:
928 -- p_appraisal_template_id
929 -- p_object_version_number
930 -- p_business_group_id
931 -- p_questionnaire_template_id
932
933 -- Post Success:
934 -- Process continues if :
935 -- All the in parameters are valid.
936
937 -- Post Failure:
938 -- An application error is raised and processing is terminated if any of
939 -- the following cases are found :
940 -- -- rating scale does not exist
941 -- -- rating scale exists but not with the same business group
942 -- -- rating scale exists but is not of type 'PERFORMANCE'
943 -- -- overall_rating of the appraisal is set and the user try's
944 -- to update the raring scale
945 -- -- rating scale existsand is used in appraisals which is further
946 -- -- used in performance ratings
947
948 -- Access Status
949 -- Internal Table Handler Use Only.
950
951 procedure chk_rating_scale
952 (p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
953 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
954 ,p_rating_scale_id in per_appraisal_templates.rating_scale_id%TYPE
955 ,p_business_group_id in per_appraisal_templates.business_group_id%TYPE default null
956 )
957 is
958
959 l_exists varchar2(1);
960 l_api_updating boolean;
961 l_proc varchar2(72) := g_package||'chk_rating_scale';
962 l_type per_rating_scales.type%TYPE;
963 l_business_group_id per_appraisal_templates.business_group_id%TYPE;
964 l_overall_performance_level_id per_appraisals.overall_performance_level_id%TYPE;
965
966
967 -- Cursor to check if rating scale exists and get
968 -- the type
969
970 Cursor csr_rating_scale_exists
971 is
972 select business_group_id,type
973 from per_rating_scales
974 where rating_scale_id = p_rating_scale_id;
975
976 -- Cursor to check if the appraisal template
977 -- is used in appraisal and the overall_performance_level_id
978 -- is set on the appraisal
979
980 Cursor csr_rating_scale_update1
981 is
982 select overall_performance_level_id
983 from per_appraisals
984 where appraisal_template_id = p_appraisal_template_id;
985
986 -- Cursor to check if the appraisal template
987 -- is used in appraisal which is further used in
988 -- performance ratings
989
990 Cursor csr_rating_scale_update2
991 is
992 select 'Y'
993 from per_appraisals apr
994 ,per_performance_ratings prt
995 where prt.appraisal_id = apr.appraisal_id
996 and apr.appraisal_template_id = p_appraisal_template_id;
997
998
999 begin
1000 hr_utility.set_location('Entering:'|| l_proc, 1);
1001
1002 -- Check mandatory parameters have been set
1003
1004 -- ngundura change done for pa requirements
1005 /* hr_api.mandatory_arg_error
1006 (p_api_name => l_proc
1007 ,p_argument => 'business_group_id'
1008 ,p_argument_value => p_business_group_id
1009 );
1010 */
1011 -- ngundura end of changes
1012
1013 hr_utility.set_location('Entering:'|| l_proc, 2);
1014
1015 l_api_updating := per_apt_shd.api_updating
1016 (p_appraisal_template_id => p_appraisal_template_id
1017 ,p_object_version_number => p_object_version_number);
1018
1019 -- Only continue if:
1020 -- a) The current g_old_rec is current and
1021 -- b) The value for rating scale has changed
1022
1023 if (NOT l_api_updating) then
1024 if p_rating_scale_id is not null then
1025 hr_utility.set_location(l_proc, 3);
1026 open csr_rating_scale_exists;
1027 fetch csr_rating_scale_exists into l_business_group_id,l_type;
1028 if csr_rating_scale_exists%notfound then
1029 close csr_rating_scale_exists;
1033 close csr_rating_scale_exists;
1030 hr_utility.set_message(801,'HR_51928_APT_RSC_NOT_EXIST');
1031 hr_utility.raise_error;
1032 end if;
1034
1035 -- check if rating scale is in the same business group
1036
1037 hr_utility.set_location(l_proc, 5);
1038 if nvl(l_business_group_id,-1) <> nvl(p_business_group_id,-1) then
1039 if(l_business_group_id is not null) then
1040 hr_utility.set_message(801,'HR_51929_APT_RSC_DIFF_BUS_GRP');
1041 hr_utility.raise_error;
1042 end if;
1043 end if;
1044
1045 -- check if rating scale template is of type 'PERFORMANCE'
1046
1047 hr_utility.set_location(l_proc, 6);
1048 if l_type <> 'PERFORMANCE' then
1049 hr_utility.set_message(801,'HR_51930_APT_RSC_INV_TYPE');
1050 hr_utility.raise_error;
1051 end if;
1052 end if;
1053 end if;
1054
1055 -- Perform the below checks only if updating and the value of the
1056 -- rating_scale_id has changed
1057
1058 if ( (l_api_updating and (per_apt_shd.g_old_rec.rating_scale_id
1059 <> nvl(p_rating_scale_id,hr_api.g_number)) )
1060 )
1061 then
1062
1063 -- Check if the rating scale can be updated
1064
1065 hr_utility.set_location(l_proc, 7);
1066 open csr_rating_scale_update1;
1067 fetch csr_rating_scale_update1 into l_overall_performance_level_id;
1068 close csr_rating_scale_update1;
1069 if l_overall_performance_level_id is not null then
1070 hr_utility.set_message(801,'HR_51931_APT_APR_LVL_NOTNULL');
1071 hr_utility.raise_error;
1072 end if;
1073
1074 hr_utility.set_location(l_proc, 8);
1075 open csr_rating_scale_update2;
1076 fetch csr_rating_scale_update2 into l_exists;
1077 if csr_rating_scale_update2%found then
1078 close csr_rating_scale_update2;
1079 hr_utility.set_message(801,'HR_51932_APT_EXIST_IN_PRF');
1080 hr_utility.raise_error;
1081 end if;
1082 close csr_rating_scale_update2;
1083
1084 -- Now check if the rating scale exists and is of the correct type
1085
1086 if p_rating_scale_id is not null then
1087 hr_utility.set_location(l_proc, 9);
1088 open csr_rating_scale_exists;
1089 fetch csr_rating_scale_exists into l_business_group_id,l_type;
1090 if csr_rating_scale_exists%notfound then
1091 close csr_rating_scale_exists;
1092 hr_utility.set_message(801,'HR_51928_APT_RSC_NOT_EXIST');
1093 hr_utility.raise_error;
1094 end if;
1095 close csr_rating_scale_exists;
1096
1097 -- check if rating scale is in the same business group
1098
1099 hr_utility.set_location(l_proc, 10);
1100 if l_business_group_id <> p_business_group_id then
1101 hr_utility.set_message(801,'HR_51929_APT_RSC_DIFF_BUS_GRP');
1102 hr_utility.raise_error;
1103 end if;
1104
1105 -- check if rating scale template is of type 'PERFORMANCE'
1106
1107 hr_utility.set_location(l_proc, 11);
1108 if l_type <> 'PERFORMANCE' then
1109 hr_utility.set_message(801,'HR_52930_APT_RSC_INV_TYPE');
1110 hr_utility.raise_error;
1111 end if;
1112 end if;
1113
1114 end if;
1115
1116 hr_utility.set_location(l_proc, 12);
1117
1118 hr_utility.set_location('Leaving: '|| l_proc, 10);
1119
1120 end chk_rating_scale;
1121
1122
1123 --------------------------------------------------------------------------+
1124 -------------------------------<chk_template_delete>----------------------+
1125 --------------------------------------------------------------------------+
1126
1127 -- Description:
1128 -- - Validates that the appraisal template cannot be deleted if it is used
1129 -- in an appraisal
1130
1131 -- Pre_conditions:
1132 -- - Valid p_appraisal_template_id
1133
1134 -- In Arguments:
1135 -- p_appraisal_template_id
1136 -- p_object_version_number
1137
1138 -- Post Success:
1139 -- Process continues if :
1140 -- All the in parameters are valid.
1141
1142 -- Post Failure:
1143 -- An application error is raised and processing is terminated if any of
1144 -- the following cases are found :
1145 -- - Template is used in an Appraisal
1146
1147 -- Access Status
1148 -- Internal Table Handler Use Only.
1149
1150 procedure chk_template_delete
1154 is
1151 (p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
1152 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
1153 )
1155
1156 l_exists varchar2(1);
1157 l_proc varchar2(72) := g_package||'chk_template_delete';
1158
1159
1160 -- Cursor to check if the appraisal template
1161 -- is used in appraisal
1162
1163 Cursor csr_template_delete
1164 is
1165 select 'Y'
1166 from per_appraisals
1167 where appraisal_template_id = p_appraisal_template_id;
1168
1169 begin
1170 hr_utility.set_location('Entering:'|| l_proc, 1);
1171
1172 -- Check mandatory parameters have been set
1173
1174 hr_api.mandatory_arg_error
1175 (p_api_name => l_proc
1176 ,p_argument => 'appraisal_template_id'
1177 ,p_argument_value => p_appraisal_template_id
1178 );
1179
1180
1181 hr_utility.set_location('Entering:'|| l_proc, 2);
1182
1183 open csr_template_delete;
1184 fetch csr_template_delete into l_exists;
1185 if csr_template_delete%found then
1186 close csr_template_delete;
1187 hr_utility.set_message(801,'HR_51933_APT_EXIST_IN_APR');
1188 hr_utility.raise_error;
1189 end if;
1190 close csr_template_delete;
1191
1192 hr_utility.set_location('Leaving: '|| l_proc, 10);
1193
1194 end chk_template_delete;
1195
1196 --------------------------------------------------------------------------+
1197 -------------------------------<chk_update_comp_profile>----------------------+
1198 --------------------------------------------------------------------------+
1199
1200 -- Description:
1201 -- - Validates that the update_comp_personal_profile cannot be set to Y if
1202 -- assessment_template_id is null . Also Validate update_comp_profile
1203 -- against the HR_LOOKUP where lookup_type = 'YES_NO'.
1204 --
1205 --
1206
1207 -- Pre_conditions:
1208 -- - Valid p_appraisal_template_id
1209
1210 -- In Arguments:
1211 -- p_update_personal_comp_profile
1212 -- p_assessment_type_id
1213 -- p_effective_date
1214 -- p_object_version_number
1215
1216 -- Post Success:
1217 -- Process continues if :
1218 -- All the in parameters are valid.
1219
1220 -- Post Failure:
1221 -- An application error is raised and processing is terminated if any of
1222 -- the following cases are found :
1223 -- Update_comp_Profile value is not null but assessment_type_id is null
1224 -- update_comp_profile value is not validated against lookup 'YES_NO'.
1225
1226 -- Access Status
1227 -- Internal Table Handler Use Only.
1228
1229
1230 -- Access Status:
1231 -- Internal Table Handler Use Only.
1232 --
1233 Procedure chk_update_comp_profile
1234 (
1235 p_update_personal_comp_profile in per_appraisal_templates.update_personal_comp_profile%TYPE
1236 ,p_assessment_type_id in per_appraisal_templates.assessment_type_id%TYPE
1237 ,p_effective_date in date
1238 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
1239 ,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
1240 )
1241 is
1242 --
1243 l_proc varchar2(72):=g_package||'chk_update_comp_profile';
1244 l_api_updating boolean;
1245 --
1246 begin
1247 -- Bug#885806
1248 -- dbms_output.put_line('Inside the chk_display_assessment_comments procedure');
1249 hr_utility.trace('Inside the chk_update_comp_profile procedure');
1250 hr_utility.set_location('Entering:'|| l_proc, 1);
1251
1252 if ( p_assessment_type_id is null and p_update_personal_comp_profile is not null ) then
1253 hr_utility.set_location(l_proc, 2);
1254 hr_utility.set_message(800,'HR_APT_AST_REQUIRED_MSG');
1255 hr_utility.raise_error;
1256
1257 end if;
1258 --
1259 -- Check mandatory parameters have been set
1260 --
1261 hr_api.mandatory_arg_error
1262 (p_api_name => l_proc
1263 ,p_argument => 'effective_date'
1264 ,p_argument_value => p_effective_date
1265 );
1266 --
1267 -- Only proceed with the validation if :
1268 -- a) The current g_old_rec is current
1269 -- b) The value has changed.
1270 -- c) A record is being inserted
1271 --
1272
1273 l_api_updating := per_apt_shd.api_updating
1274 (p_appraisal_template_id => p_appraisal_template_id
1275 ,p_object_version_number => p_object_version_number);
1276 --
1277 if ((l_api_updating and nvl(per_apt_shd.g_old_rec.update_personal_comp_profile,
1278 hr_api.g_varchar2)
1279 <> nvl(p_update_personal_comp_profile, hr_api.g_varchar2))
1280 or
1281 (NOT l_api_updating)) then
1282 --
1283 hr_utility.set_location(l_proc, 5);
1284 --
1285 -- Check the value in p_display_assessment_comments exists in hr_lookups
1286 --
1287 if hr_api.not_exists_in_hr_lookups
1288 (p_effective_date => p_effective_date
1289 ,p_lookup_type => 'YES_NO'
1290 ,p_lookup_code => p_update_personal_comp_profile
1291 ) then
1292 hr_utility.set_location(l_proc, 10);
1293 hr_utility.set_message(801,'HR_APT_UPD_COMP_PROF_INVAL');
1297 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1294 hr_utility.raise_error;
1295 end if;
1296 --
1298 end if;
1299 end chk_update_comp_profile;
1300
1301 --------------------------------------------------------------------------+
1302 -------------------------------<chk_comp_profile_source_type>----------------------+
1303 --------------------------------------------------------------------------+
1304 -- Description:
1305 -- - Validates that the comp_profile_source_type cannot be set if
1306 -- update_personal_comp_profile is null or 'N' . Also Validate update_comp_profile
1307 -- against the HR_LOOKUP where lookup_type = 'YES_NO'.
1308 --
1309 --
1310
1311 -- Pre_conditions:
1312 -- - Valid p_appraisal_template_id
1313
1314 -- In Arguments:
1315 -- p_comp_profile_source_type
1316 -- p_update_personal_comp_profile
1317 -- p_effective_date
1318 -- p_object_version_number
1319
1320 -- Post Success:
1321 -- Process continues if :
1322 -- All the in parameters are valid.
1323
1324 -- Post Failure:
1325 -- An application error is raised and processing is terminated if any of
1326 -- the following cases are found :
1327 -- comp_profile_source_type value is not null but update_personal_comp_profile is null or 'N'
1328 -- comp_profile_source_type value is not validated against lookup 'PROFICIENCY_SOURCE'.
1329
1330 -- Access Status
1331 -- Internal Table Handler Use Only.
1332
1333
1334 -- Access Status:
1335 -- Internal Table Handler Use Only.
1336 --
1337
1338 Procedure chk_comp_profile_source_type
1339 (
1340 p_comp_profile_source_type in per_appraisal_templates.comp_profile_source_type%TYPE
1341 ,p_update_personal_comp_profile in per_appraisal_templates.update_personal_comp_profile%TYPE
1342 ,p_effective_date in date
1343 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
1344 ,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
1345 )
1346 is
1347 --
1348 l_proc varchar2(72):=g_package||'chk_comp_profile_source_type';
1349 l_api_updating boolean;
1350 --
1351 begin
1352 -- Bug#885806
1353 -- dbms_output.put_line('Inside the chk_display_assessment_comments procedure');
1354 hr_utility.trace('Inside the chk_comp_profile_source_type procedure');
1355 hr_utility.set_location('Entering:'|| l_proc, 1);
1356
1357 if ( p_comp_profile_source_type is not null and ( p_update_personal_comp_profile is null or p_update_personal_comp_profile = 'N') ) then
1358 hr_utility.set_location(l_proc, 2);
1359 hr_utility.set_message(800,'HR_APT_UPD_COMP_PROF_REQUIRED');
1360 hr_utility.raise_error;
1361 end if;
1362 --
1363 -- Check mandatory parameters have been set
1364 --
1365 hr_api.mandatory_arg_error
1366 (p_api_name => l_proc
1367 ,p_argument => 'effective_date'
1368 ,p_argument_value => p_effective_date
1369 );
1370 --
1371 -- Only proceed with the validation if :
1372 -- a) The current g_old_rec is current
1373 -- b) The value has changed.
1374 -- c) A record is being inserted
1375 --
1376
1377 l_api_updating := per_apt_shd.api_updating
1378 (p_appraisal_template_id => p_appraisal_template_id
1379 ,p_object_version_number => p_object_version_number);
1380 --
1381 if ((l_api_updating and nvl(per_apt_shd.g_old_rec.comp_profile_source_type,
1382 hr_api.g_varchar2)
1383 <> nvl(p_comp_profile_source_type, hr_api.g_varchar2))
1384 or
1385 (NOT l_api_updating)) then
1386 --
1387 hr_utility.set_location(l_proc, 5);
1388 --
1389 -- Check the value in p_display_assessment_comments exists in hr_lookups
1390 --
1391 if hr_api.not_exists_in_hr_lookups
1392 (p_effective_date => p_effective_date
1393 ,p_lookup_type => 'PROFICIENCY_SOURCE'
1394 ,p_lookup_code => p_comp_profile_source_type
1395 ) then
1396 hr_utility.set_location(l_proc, 10);
1397 hr_utility.set_message(800,'HR_APT_COMP_PROF_SRC_INVAL');
1398 hr_utility.raise_error;
1399 end if;
1400 --
1401 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1402 end if;
1403 end chk_comp_profile_source_type;
1404
1405 --------------------------------------------------------------------------+
1406 -------------------------------<chk_link_appr_to_learning_path>----------------------+
1407 --------------------------------------------------------------------------+
1408 -- Description:
1409 -- Validate link_appr_to_learning_path
1410 -- against the HR_LOOKUP where lookup_type = 'YES_NO'.
1411 --
1412
1413 -- Pre_conditions:
1414 -- - Valid p_appraisal_template_id
1415
1416 -- In Arguments:
1417 -- p_link_appr_to_learning_path
1418 -- p_effective_date
1419 -- p_object_version_number
1420
1421 -- Post Success:
1422 -- Process continues if :
1423 -- All the in parameters are valid.
1424
1425 -- Post Failure:
1426 -- An application error is raised and processing is terminated if any of
1427 -- the following cases are found :
1428 -- link_appr_to_learning_path value is not validated against lookup 'PROFICIENCY_SOURCE'.
1429
1430 -- Access Status
1431 -- Internal Table Handler Use Only.
1432
1433
1437
1434 -- Access Status:
1435 -- Internal Table Handler Use Only.
1436 --
1438 Procedure chk_link_appr_to_learning_path
1439 (
1440 p_link_appr_to_learning_path in per_appraisal_templates.link_appr_to_learning_path%TYPE
1441 ,p_effective_date in date
1442 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
1443 ,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
1444 )
1445 is
1446 --
1447 l_proc varchar2(72):=g_package||'chk_link_appr_to_learning_path';
1448 l_api_updating boolean;
1449 --
1450 begin
1451 -- Bug#885806
1452 -- dbms_output.put_line('Inside the chk_display_assessment_comments procedure');
1453 hr_utility.trace('Inside the chk_link_appr_to_learning_path procedure');
1454 hr_utility.set_location('Entering:'|| l_proc, 1);
1455
1456
1457 --
1458 -- Check mandatory parameters have been set
1459 --
1460 hr_api.mandatory_arg_error
1461 (p_api_name => l_proc
1462 ,p_argument => 'effective_date'
1463 ,p_argument_value => p_effective_date
1464 );
1465 --
1466 -- Only proceed with the validation if :
1467 -- a) The current g_old_rec is current
1468 -- b) The value has changed.
1469 -- c) A record is being inserted
1470 --
1471
1472 l_api_updating := per_apt_shd.api_updating
1473 (p_appraisal_template_id => p_appraisal_template_id
1474 ,p_object_version_number => p_object_version_number);
1475 --
1476 if ((l_api_updating and nvl(per_apt_shd.g_old_rec.link_appr_to_learning_path,
1477 hr_api.g_varchar2)
1478 <> nvl(p_link_appr_to_learning_path, hr_api.g_varchar2))
1479 or
1480 (NOT l_api_updating)) then
1481 --
1482 hr_utility.set_location(l_proc, 5);
1483 --
1484 -- Check the value in p_link_appr_to_learning_path exists in hr_lookups
1485 --
1486 if hr_api.not_exists_in_hr_lookups
1487 (p_effective_date => p_effective_date
1488 ,p_lookup_type => 'YES_NO'
1489 ,p_lookup_code => p_link_appr_to_learning_path
1490 ) then
1491 hr_utility.set_location(l_proc, 10);
1492 hr_utility.set_message(800,'HR_APT_LINK_TO_LP_INVAL');
1493 hr_utility.raise_error;
1494 end if;
1495 --
1496 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1497 end if;
1498 end chk_link_appr_to_learning_path;
1499
1500 --------------------------------------------------------------------------+
1501 -------------------------------<chk_available_flag>----------------------+
1502 --------------------------------------------------------------------------+
1503 -- Description:
1504 -- Validate available_flag
1505 -- against the HR_LOOKUP where lookup_type = 'TEMPLATE_AVAILABILITY_FLAG'.
1506 --
1507
1508 -- Pre_conditions:
1509 -- - Valid p_appraisal_template_id
1510
1511 -- In Arguments:
1512 -- p_available_flag
1513 -- p_effective_date
1514 -- p_object_version_number
1515
1516 -- Post Success:
1517 -- Process continues if :
1518 -- All the in parameters are valid.
1519
1520 -- Post Failure:
1521 -- An application error is raised and processing is terminated if any of
1522 -- the following cases are found :
1523 -- available_flag value is not validated against lookup 'PROFICIENCY_SOURCE'.
1524
1525 -- Access Status
1526 -- Internal Table Handler Use Only.
1527
1528
1529 -- Access Status:
1530 -- Internal Table Handler Use Only.
1531 --
1532
1533 Procedure chk_available_flag
1534 (
1535 p_available_flag in per_appraisal_templates.available_flag%TYPE
1536 ,p_effective_date in date
1537 ,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
1538 ,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
1539 )
1540 is
1541 --
1542 l_proc varchar2(72):=g_package||'chk_available_flag';
1543 l_api_updating boolean;
1544 --
1545 begin
1546 -- Bug#885806
1547 -- dbms_output.put_line('Inside the chk_display_assessment_comments procedure');
1548 hr_utility.trace('Inside the chk_available_flag procedure');
1549 hr_utility.set_location('Entering:'|| l_proc, 1);
1550
1551
1552 --
1553 -- Check mandatory parameters have been set
1554 --
1555 hr_api.mandatory_arg_error
1556 (p_api_name => l_proc
1557 ,p_argument => 'effective_date'
1558 ,p_argument_value => p_effective_date
1559 );
1560 --
1561 -- Only proceed with the validation if :
1562 -- a) The current g_old_rec is current
1563 -- b) The value has changed.
1564 -- c) A record is being inserted
1565 --
1566
1567 l_api_updating := per_apt_shd.api_updating
1568 (p_appraisal_template_id => p_appraisal_template_id
1569 ,p_object_version_number => p_object_version_number);
1570 --
1571 if ((l_api_updating and nvl(per_apt_shd.g_old_rec.available_flag,
1572 hr_api.g_varchar2)
1573 <> nvl(p_available_flag, hr_api.g_varchar2))
1574 or
1575 (NOT l_api_updating)) then
1576 --
1577 hr_utility.set_location(l_proc, 5);
1578 --
1579 -- Check the value in p_available_flag exists in hr_lookups
1580 --
1581 if hr_api.not_exists_in_hr_lookups
1585 ) then
1582 (p_effective_date => p_effective_date
1583 ,p_lookup_type => 'TEMPLATE_AVAILABILITY_FLAG'
1584 ,p_lookup_code => p_available_flag
1586 hr_utility.set_location(l_proc, 10);
1587 hr_utility.set_message(800,'HR_AVAIL_FLAG_INVAL');
1588 hr_utility.raise_error;
1589 end if;
1590 --
1591 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1592 end if;
1593 end chk_available_flag;
1594 -- ----------------------------------------------------------------------+
1595 -- |------------------------------< chk_df >-----------------------------+
1596 -- ----------------------------------------------------------------------+
1597
1598 -- Description:
1599 -- Validates the all Descriptive Flexfield values.
1600
1601 -- Pre-conditions:
1602 -- All other columns have been validated. Must be called as the
1603 -- last step from insert_validate and update_validate.
1604
1605 -- In Arguments:
1606 -- p_rec
1607
1608 -- Post Success:
1609 -- If the Descriptive Flexfield structure column and data values are
1610 -- all valid this procedure will end normally and processing will
1611 -- continue.
1612
1613 -- Post Failure:
1614 -- If the Descriptive Flexfield structure column value or any of
1615 -- the data values are invalid then an application error is raised as
1616 -- a PL/SQL exception.
1617
1618 -- Access Status:
1619 -- Internal Row Handler Use Only.
1620
1621 procedure chk_df
1622 (p_rec in per_apt_shd.g_rec_type) is
1623
1624 l_proc varchar2(72) := g_package||'chk_df';
1625
1626 begin
1627 hr_utility.set_location('Entering:'||l_proc, 10);
1628
1629 if ((p_rec.appraisal_template_id is not null) and (
1630 nvl(per_apt_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1631 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1632 nvl(per_apt_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1633 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1634 nvl(per_apt_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1635 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1636 nvl(per_apt_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1637 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1638 nvl(per_apt_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1639 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1640 nvl(per_apt_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1641 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1642 nvl(per_apt_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1643 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1644 nvl(per_apt_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1645 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1646 nvl(per_apt_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1647 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1648 nvl(per_apt_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1649 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1650 nvl(per_apt_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1651 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1652 nvl(per_apt_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1653 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1654 nvl(per_apt_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1655 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1656 nvl(per_apt_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1657 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1658 nvl(per_apt_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1659 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1660 nvl(per_apt_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1661 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1662 nvl(per_apt_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1663 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1664 nvl(per_apt_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1665 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1666 nvl(per_apt_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1667 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1668 nvl(per_apt_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1669 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1670 nvl(per_apt_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1671 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1672 or
1673 (p_rec.appraisal_template_id is null) then
1674
1675 -- Only execute the validation if absolutely necessary:
1676 -- a) During update, the structure column value or any
1677 -- of the attribute values have actually changed.
1678 -- b) During insert.
1679
1680 hr_dflex_utility.ins_or_upd_descflex_attribs
1681 (p_appl_short_name => 'PER'
1682 ,p_descflex_name => 'PER_APPRAISAL_TEMPLATES'
1683 ,p_attribute_category => p_rec.attribute_category
1684 ,p_attribute1_name => 'ATTRIBUTE1'
1685 ,p_attribute1_value => p_rec.attribute1
1686 ,p_attribute2_name => 'ATTRIBUTE2'
1687 ,p_attribute2_value => p_rec.attribute2
1688 ,p_attribute3_name => 'ATTRIBUTE3'
1689 ,p_attribute3_value => p_rec.attribute3
1690 ,p_attribute4_name => 'ATTRIBUTE4'
1691 ,p_attribute4_value => p_rec.attribute4
1692 ,p_attribute5_name => 'ATTRIBUTE5'
1693 ,p_attribute5_value => p_rec.attribute5
1694 ,p_attribute6_name => 'ATTRIBUTE6'
1695 ,p_attribute6_value => p_rec.attribute6
1696 ,p_attribute7_name => 'ATTRIBUTE7'
1700 ,p_attribute9_name => 'ATTRIBUTE9'
1697 ,p_attribute7_value => p_rec.attribute7
1698 ,p_attribute8_name => 'ATTRIBUTE8'
1699 ,p_attribute8_value => p_rec.attribute8
1701 ,p_attribute9_value => p_rec.attribute9
1702 ,p_attribute10_name => 'ATTRIBUTE10'
1703 ,p_attribute10_value => p_rec.attribute10
1704 ,p_attribute11_name => 'ATTRIBUTE11'
1705 ,p_attribute11_value => p_rec.attribute11
1706 ,p_attribute12_name => 'ATTRIBUTE12'
1707 ,p_attribute12_value => p_rec.attribute12
1708 ,p_attribute13_name => 'ATTRIBUTE13'
1709 ,p_attribute13_value => p_rec.attribute13
1710 ,p_attribute14_name => 'ATTRIBUTE14'
1711 ,p_attribute14_value => p_rec.attribute14
1712 ,p_attribute15_name => 'ATTRIBUTE15'
1713 ,p_attribute15_value => p_rec.attribute15
1714 ,p_attribute16_name => 'ATTRIBUTE16'
1715 ,p_attribute16_value => p_rec.attribute16
1716 ,p_attribute17_name => 'ATTRIBUTE17'
1717 ,p_attribute17_value => p_rec.attribute17
1718 ,p_attribute18_name => 'ATTRIBUTE18'
1719 ,p_attribute18_value => p_rec.attribute18
1720 ,p_attribute19_name => 'ATTRIBUTE19'
1721 ,p_attribute19_value => p_rec.attribute19
1722 ,p_attribute20_name => 'ATTRIBUTE20'
1723 ,p_attribute20_value => p_rec.attribute20
1724 );
1725 end if;
1726
1727 hr_utility.set_location(' Leaving:'||l_proc, 20);
1728
1729 end chk_df;
1730
1731 -- ---------------------------------------------------------------------------+
1732 -- |---------------------------< insert_validate >----------------------------|
1733 -- ---------------------------------------------------------------------------+
1734 Procedure insert_validate(p_rec in per_apt_shd.g_rec_type
1735 ,p_effective_date in date) is
1736
1737 l_proc varchar2(72) := g_package||'insert_validate';
1738
1739 Begin
1740 hr_utility.set_location('Entering:'||l_proc, 5);
1741
1742 -- Call all supporting business operations
1743 if p_rec.business_group_id is not null then
1744 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1745 end if;
1746
1747 -- Check name is unique
1748
1749 per_apt_bus.chk_name
1750 (p_appraisal_template_id => p_rec.appraisal_template_id
1751 ,p_name => p_rec.name
1752 ,p_object_version_number => p_rec.object_version_number
1753 ,p_business_group_id => p_rec.business_group_id
1754 );
1755
1756 -- Check template dates
1757
1758 per_apt_bus.chk_template_dates
1759 (p_date_from => p_rec.date_from
1760 ,p_date_to => p_rec.date_to
1761 ,p_appraisal_template_id => p_rec.appraisal_template_id
1762 ,p_object_version_number => p_rec.object_version_number
1763 );
1764
1765 -- check competence assessment template
1766
1767 per_apt_bus.chk_assessment_type
1768 (p_appraisal_template_id => p_rec.appraisal_template_id
1769 ,p_object_version_number => p_rec.object_version_number
1770 ,p_assessment_type_id => p_rec.assessment_type_id
1771 ,p_date_from => p_rec.date_from
1772 ,p_date_to => p_rec.date_to
1773 ,p_business_group_id => p_rec.business_group_id
1774 );
1775
1776 -- Check Questionnaire template
1777
1778 per_apt_bus.chk_question_template
1779 (p_object_version_number => p_rec.object_version_number
1780 ,p_questionnaire_template_id => p_rec.questionnaire_template_id
1781 ,p_business_group_id => p_rec.business_group_id
1782 ,p_appraisal_template_id => p_rec.appraisal_template_id
1783 );
1784
1785 -- Check rating scale
1786
1787 per_apt_bus.chk_rating_scale
1788 (p_appraisal_template_id => p_rec.appraisal_template_id
1789 ,p_object_version_number => p_rec.object_version_number
1790 ,p_rating_scale_id => p_rec.rating_scale_id
1791 ,p_business_group_id => p_rec.business_group_id
1792 );
1793
1794 -- check objective assessment template
1795
1796 per_apt_bus.chk_objective_asmnt_type
1797 (p_appraisal_template_id => p_rec.appraisal_template_id
1798 ,p_object_version_number => p_rec.object_version_number
1799 ,p_objective_asmnt_type_id => p_rec.objective_asmnt_type_id
1800 ,p_date_from => p_rec.date_from
1801 ,p_date_to => p_rec.date_to
1802 ,p_business_group_id => p_rec.business_group_id
1803 );
1804
1805 -- Check MA Questionnaire template
1806
1807 per_apt_bus.chk_ma_question_template
1808 (p_object_version_number => p_rec.object_version_number
1809 ,p_ma_quest_template_id => p_rec.ma_quest_template_id
1810 ,p_business_group_id => p_rec.business_group_id
1811 ,p_appraisal_template_id => p_rec.appraisal_template_id
1812 );
1813
1814 -- Check update_comp_profile value
1815 if p_rec.update_personal_comp_profile is not null then
1816 per_apt_bus.chk_update_comp_profile
1817 (
1818 p_update_personal_comp_profile => p_rec.update_personal_comp_profile
1819 ,p_assessment_type_id => p_rec.assessment_type_id
1820 ,p_effective_date => p_effective_date
1821 ,p_object_version_number => p_rec.object_version_number
1822 ,p_appraisal_template_id => p_rec.appraisal_template_id
1823 );
1824 end if;
1825 if p_rec.comp_profile_source_type is not null then
1829 p_comp_profile_source_type => p_rec.comp_profile_source_type
1826 -- Check comp_profile_source_type value
1827 per_apt_bus.chk_comp_profile_source_type
1828 (
1830 ,p_update_personal_comp_profile => p_rec.update_personal_comp_profile
1831 ,p_effective_date => p_effective_date
1832 ,p_object_version_number => p_rec.object_version_number
1833 ,p_appraisal_template_id => p_rec.appraisal_template_id
1834 );
1835 end if;
1836 -- Check link_to_learning_path value
1837 if p_rec.link_appr_to_learning_path is not null then
1838 per_apt_bus.chk_link_appr_to_learning_path
1839 (
1840 p_link_appr_to_learning_path => p_rec.link_appr_to_learning_path
1841 ,p_effective_date => p_effective_date
1842 ,p_object_version_number => p_rec.object_version_number
1843 ,p_appraisal_template_id => p_rec.appraisal_template_id
1844 );
1845 end if;
1846
1847 -- Check available_flag value
1848 if p_rec.available_flag is not null then
1849 per_apt_bus.chk_available_flag
1850 (
1851 p_available_flag => p_rec.available_flag
1852 ,p_effective_date => p_effective_date
1853 ,p_object_version_number => p_rec.object_version_number
1854 ,p_appraisal_template_id => p_rec.appraisal_template_id
1855 );
1856 end if;
1857
1858 -- Call descriptive flexfield validation routines
1859
1860 per_apt_bus.chk_df(p_rec => p_rec);
1861
1862 hr_utility.set_location(' Leaving:'||l_proc, 45);
1863
1864 hr_utility.set_location(' Leaving:'||l_proc, 10);
1865 End insert_validate;
1866
1867 -- ---------------------------------------------------------------------------+
1868 -- |---------------------------< update_validate >----------------------------|
1869 -- ---------------------------------------------------------------------------+
1870 Procedure update_validate(p_rec in per_apt_shd.g_rec_type
1871 ,p_effective_date in date) is
1872
1873 l_proc varchar2(72) := g_package||'update_validate';
1874
1875 Begin
1876 hr_utility.set_location('Entering:'||l_proc, 5);
1877
1878 -- Call all supporting business operations
1879
1880 if p_rec.business_group_id is not null then
1881 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1882
1883 end if;
1884
1885 -- Rule Check non-updateable fields cannot be updated
1886
1887 chk_non_updateable_args(p_rec => p_rec);
1888
1889
1890 -- Check name is unique
1891
1892 per_apt_bus.chk_name
1893 (p_appraisal_template_id => p_rec.appraisal_template_id
1894 ,p_name => p_rec.name
1895 ,p_object_version_number => p_rec.object_version_number
1896 ,p_business_group_id => p_rec.business_group_id
1897 );
1898
1899 -- Check template dates
1900
1901 per_apt_bus.chk_template_dates
1902 (p_date_from => p_rec.date_from
1903 ,p_date_to => p_rec.date_to
1904 ,p_appraisal_template_id => p_rec.appraisal_template_id
1905 ,p_object_version_number => p_rec.object_version_number
1906 );
1907
1908 -- Check assessment type
1909
1910 per_apt_bus.chk_assessment_type
1911 (p_appraisal_template_id => p_rec.appraisal_template_id
1912 ,p_object_version_number => p_rec.object_version_number
1913 ,p_assessment_type_id => p_rec.assessment_type_id
1914 ,p_date_from => p_rec.date_from
1915 ,p_date_to => p_rec.date_to
1916 ,p_business_group_id => p_rec.business_group_id
1917 );
1918
1919 -- Check Questionnaire template
1920
1921 per_apt_bus.chk_question_template
1922 (p_object_version_number => p_rec.object_version_number
1923 ,p_questionnaire_template_id => p_rec.questionnaire_template_id
1924 ,p_business_group_id => p_rec.business_group_id
1925 ,p_appraisal_template_id => p_rec.appraisal_template_id
1926 );
1927
1928 -- Check rating scale update
1929
1930 per_apt_bus.chk_rating_scale
1931 (p_appraisal_template_id => p_rec.appraisal_template_id
1932 ,p_object_version_number => p_rec.object_version_number
1933 ,p_rating_scale_id => p_rec.rating_scale_id
1934 ,p_business_group_id => p_rec.business_group_id
1935 );
1936
1937 -- check objective assessment template
1938
1939 per_apt_bus.chk_objective_asmnt_type
1940 (p_appraisal_template_id => p_rec.appraisal_template_id
1941 ,p_object_version_number => p_rec.object_version_number
1942 ,p_objective_asmnt_type_id => p_rec.objective_asmnt_type_id
1943 ,p_date_from => p_rec.date_from
1944 ,p_date_to => p_rec.date_to
1945 ,p_business_group_id => p_rec.business_group_id
1946 );
1947
1948 -- Check MA Questionnaire template
1949
1950 per_apt_bus.chk_ma_question_template
1951 (p_object_version_number => p_rec.object_version_number
1952 ,p_ma_quest_template_id => p_rec.ma_quest_template_id
1953 ,p_business_group_id => p_rec.business_group_id
1954 ,p_appraisal_template_id => p_rec.appraisal_template_id
1955 );
1956
1957 -- Check update_comp_profile value
1958 if p_rec.update_personal_comp_profile is not null then
1959 per_apt_bus.chk_update_comp_profile
1960 (
1961 p_update_personal_comp_profile => p_rec.update_personal_comp_profile
1962 ,p_assessment_type_id => p_rec.assessment_type_id
1963 ,p_effective_date => p_effective_date
1964 ,p_object_version_number => p_rec.object_version_number
1965 ,p_appraisal_template_id => p_rec.appraisal_template_id
1966 );
1967 end if;
1968 if p_rec.comp_profile_source_type is not null then
1969 -- Check comp_profile_source_type value
1970 per_apt_bus.chk_comp_profile_source_type
1971 (
1972 p_comp_profile_source_type => p_rec.comp_profile_source_type
1973 ,p_update_personal_comp_profile => p_rec.update_personal_comp_profile
1974 ,p_effective_date => p_effective_date
1975 ,p_object_version_number => p_rec.object_version_number
1976 ,p_appraisal_template_id => p_rec.appraisal_template_id
1977 );
1978 end if;
1979 -- Check link_to_learning_path value
1980 if p_rec.link_appr_to_learning_path is not null then
1981 per_apt_bus.chk_link_appr_to_learning_path
1982 (
1983 p_link_appr_to_learning_path => p_rec.link_appr_to_learning_path
1984 ,p_effective_date => p_effective_date
1985 ,p_object_version_number => p_rec.object_version_number
1986 ,p_appraisal_template_id => p_rec.appraisal_template_id
1987 );
1988 end if;
1989
1990 -- Check available_flag value
1991 if p_rec.available_flag is not null then
1992 per_apt_bus.chk_available_flag
1993 (
1994 p_available_flag => p_rec.available_flag
1995 ,p_effective_date => p_effective_date
1996 ,p_object_version_number => p_rec.object_version_number
1997 ,p_appraisal_template_id => p_rec.appraisal_template_id
1998 );
1999 end if;
2000 hr_utility.set_location(' Leaving:'||l_proc, 10);
2001
2002 -- Call descriptive flexfield validation routines
2003
2004 per_apt_bus.chk_df(p_rec => p_rec);
2005
2006 hr_utility.set_location(' Leaving:'||l_proc, 15);
2007 End update_validate;
2008
2009 -- ---------------------------------------------------------------------------+
2010 -- |---------------------------< delete_validate >----------------------------|
2011 -- ---------------------------------------------------------------------------+
2012 Procedure delete_validate(p_rec in per_apt_shd.g_rec_type) is
2013
2014 l_proc varchar2(72) := g_package||'delete_validate';
2015
2016 Begin
2017 hr_utility.set_location('Entering:'||l_proc, 5);
2018
2019 -- Call all supporting business operations
2020
2021 per_apt_bus.chk_template_delete
2022 (p_appraisal_template_id => p_rec.appraisal_template_id
2023 ,p_object_version_number => p_rec.object_version_number
2024 );
2025
2026 hr_utility.set_location(' Leaving:'||l_proc, 10);
2027 End delete_validate;
2028
2029 -- ---------------------------------------------------------------------------+
2030 -- |-----------------------< return_legislation_code >------------------------|
2031 -- ---------------------------------------------------------------------------+
2032 Function return_legislation_code
2033 ( p_appraisal_template_id in number )
2034 return varchar2 is
2035
2036 -- Declare cursor
2037
2038 cursor csr_leg_code is
2039 select legislation_code
2040 from per_business_groups pbg,
2041 per_appraisal_templates pat
2042 where pat.appraisal_template_id = p_appraisal_template_id
2043 and pbg.business_group_id = pat.business_group_id;
2044
2045 l_proc varchar2(72) := g_package||'return_legislation_code';
2046 l_legislation_code varchar2(150);
2047
2048 Begin
2049 hr_utility.set_location('Entering:'||l_proc, 5);
2050
2051 -- Ensure that all the mandatory parameters are not null
2052
2053 hr_api.mandatory_arg_error (p_api_name => l_proc,
2054 p_argument => 'appraisal_template_id',
2055 p_argument_value => p_appraisal_template_id );
2056
2057 if nvl(g_appraisal_template_id, hr_api.g_number) = p_appraisal_template_id then
2058
2059 -- The legislation has already been found with a previous
2060 -- call to this function. Just return the value in the global
2061 -- variable.
2062
2063 l_legislation_code := g_legislation_code;
2064 hr_utility.set_location(l_proc, 20);
2065 else
2066
2067 -- The ID is different to the last call to this function
2068 -- or this is the first call to this function.
2069
2070
2071 open csr_leg_code;
2072 fetch csr_leg_code into l_legislation_code;
2073 if csr_leg_code%notfound then
2074 close csr_leg_code;
2075
2076 -- The primary key is invalid therefore we must error out
2077
2078 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2079 hr_utility.raise_error;
2080 end if;
2081
2082 close csr_leg_code;
2083 g_appraisal_template_id := p_appraisal_template_id;
2084 g_legislation_code := l_legislation_code;
2085 end if;
2086 hr_utility.set_location(' Leaving:'||l_proc, 10);
2087 return l_legislation_code;
2088
2089
2090 End return_legislation_code;
2091
2092
2093 end per_apt_bus;
[Home] [Help]