1 Package Body per_apr_bus as
2 /* $Header: peaprrhi.pkb 120.8.12010000.4 2008/08/06 08:57:14 ubhat ship $ */
3
4 -- ---------------------------------------------------------------------------+
5 -- | Private Global Definitions |
6 -- ---------------------------------------------------------------------------+
7
8 g_package varchar2(33) := ' per_apr_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_id number default null;
14 g_legislation_code varchar2(150) default null;
15
16 -- --------------------------------------------------------------------------+
17 -- |----------------------< set_security_group_id >------------------------|
18 -- --------------------------------------------------------------------------+
19
20 procedure set_security_group_id
21 (
22 p_appraisal_id in per_appraisals.appraisal_id%TYPE
23 ,p_associated_column1 in varchar2 default null
24 ) is
25
26 -- Declare cursor
27
28 cursor csr_sec_grp is
29 select inf.org_information14
30 from hr_organization_information inf
31 , per_appraisals apr
32 where apr.appraisal_id = p_appraisal_id
33 and inf.organization_id = apr.business_group_id
34 and inf.org_information_context || '' = 'Business Group Information';
35
36 -- Local variables
37
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40
41 begin
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43
44 -- Ensure that all the mandatory parameter are not null
45
46 hr_api.mandatory_arg_error(p_api_name => l_proc,
47 p_argument => 'appraisal_id',
48 p_argument_value => p_appraisal_id);
49
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 if csr_sec_grp%notfound then
53 close csr_sec_grp;
54
55 -- The primary key is invalid therefore we must error
56
57 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
58 hr_multi_message.add(p_associated_column1 =>
59 nvl(p_associated_column1,'PER_APPRAISALS.PERSON_ID'));
60 else
61 close csr_sec_grp;
62 --
63 -- Set the security_group_id in CLIENT_INFO
64 --
65 hr_api.set_security_group_id
66 (p_security_group_id => l_security_group_id
67 );
68 end if;
69
70 hr_utility.set_location(' Leaving:'|| l_proc, 20);
71
72 end set_security_group_id;
73
74 -- -------------------------------------------------------------------------+
75 -- |----------------------< chk_non_updateable_args >-----------------------|
76 -- -------------------------------------------------------------------------+
77
78 Procedure chk_non_updateable_args(p_rec in per_apr_shd.g_rec_type) is
79
80 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
81
82 Begin
83 hr_utility.set_location('Entering:'||l_proc, 5);
84
85 -- Only proceed with validation if a row exists for
86 -- the current record in the HR Schema
87
88 if not per_apr_shd.api_updating
89 (p_appraisal_id => p_rec.appraisal_id
90 ,p_object_version_number => p_rec.object_version_number
91 ) then
92 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
93 hr_utility.set_message_token('PROCEDURE', l_proc);
94 hr_utility.set_message_token('STEP', '5');
95 end if;
96
97 hr_utility.set_location(l_proc, 6);
98
99 if p_rec.business_group_id <> per_apr_shd.g_old_rec.business_group_id then
100 hr_api.argument_changed_error
101 (
102 p_api_name => l_proc
103 ,p_argument => 'BUSINESS_GROUP_ID'
104 ,p_base_table => per_per_shd.g_tab_nam
105 );
106 elsif p_rec.appraisal_template_id <> per_apr_shd.g_old_rec.appraisal_template_id then
107 hr_utility.set_location(l_proc, 7);
108 hr_api.argument_changed_error
109 (
110 p_api_name => l_proc
111 ,p_argument => 'APPRAISAL_TEMPLATE_ID'
112 ,p_base_table => per_per_shd.g_tab_nam
113 );
114 elsif p_rec.appraisee_person_id <> per_apr_shd.g_old_rec.appraisee_person_id then
115 hr_utility.set_location(l_proc, 8);
116 hr_api.argument_changed_error
117 (
118 p_api_name => l_proc
119 ,p_argument => 'APPRAISEE_ID'
120 ,p_base_table => per_per_shd.g_tab_nam
121 );
122 elsif p_rec.assignment_id <> per_apr_shd.g_old_rec.assignment_id then
123 hr_utility.set_location(l_proc, 9);
124 hr_api.argument_changed_error
125 (
126 p_api_name => l_proc
127 ,p_argument => 'ASSIGNMENT_ID'
128 ,p_base_table => per_per_shd.g_tab_nam
129 );
130 elsif p_rec.assignment_start_date <> per_apr_shd.g_old_rec.assignment_start_date then
131 hr_utility.set_location(l_proc, 10);
132 hr_api.argument_changed_error
133 (
134 p_api_name => l_proc
135 ,p_argument => 'ASSIGNMENT_START_DATE'
136 ,p_base_table => per_per_shd.g_tab_nam
137 );
138 elsif p_rec.assignment_business_group_id <> per_apr_shd.g_old_rec.assignment_business_group_id then
139 hr_utility.set_location(l_proc, 11);
140 hr_api.argument_changed_error
141 (
142 p_api_name => l_proc
143 ,p_argument => 'ASSIGNMENT_BUSINESS_GROUP_ID'
144 ,p_base_table => per_per_shd.g_tab_nam
145 );
146 elsif p_rec.assignment_organization_id <> per_apr_shd.g_old_rec.assignment_organization_id then
147 hr_utility.set_location(l_proc, 12);
148 hr_api.argument_changed_error
149 (
150 p_api_name => l_proc
151 ,p_argument => 'ASSIGNMENT_ORGANIZATION_ID'
152 ,p_base_table => per_per_shd.g_tab_nam
153 );
154 elsif p_rec.assignment_job_id <> per_apr_shd.g_old_rec.assignment_job_id then
155 hr_utility.set_location(l_proc, 13);
156 hr_api.argument_changed_error
157 (
158 p_api_name => l_proc
159 ,p_argument => 'ASSIGNMENT_JOB_ID'
160 ,p_base_table => per_per_shd.g_tab_nam
161 );
162 elsif p_rec.assignment_position_id <> per_apr_shd.g_old_rec.assignment_position_id then
163 hr_utility.set_location(l_proc, 14);
164 hr_api.argument_changed_error
165 (
166 p_api_name => l_proc
167 ,p_argument => 'ASSIGNMENT_POSITION_ID'
168 ,p_base_table => per_per_shd.g_tab_nam
169 );
170
171 end if;
172 hr_utility.set_location(' Leaving:'||l_proc, 15);
173
174 end chk_non_updateable_args;
175
176 -- --------------------------------------------------------------------------+
177 -- |--------------------------< chk_open >-----------------------------------|
178 -- --------------------------------------------------------------------------+
179
180 -- Description:
181 -- Validates that p_open exists in HR_LOOKUPS, where the lookup type is
182 -- 'YES_NO'. A null value is assumed to be valid.
183
184 -- Pre-requisites:
185 -- None.
186
187 -- IN Parameters:
188 -- p_open
189 -- p_effective_date
190
191 -- Post Success:
192 -- Processing continues if the open column is valid against the lookup
193
194 -- Post Failure:
195 -- An application error is raised, and processing is terminated if OPEN is
196 -- invalid.
197
198 -- Developer/Implementation Notes:
199 -- None.
200
201 -- Access Status:
202 -- Internal Row Handler Use Only.
203
204 -- ---------------------------------------------------------------------------+
205 procedure chk_open
206 (p_open in per_appraisals.open%TYPE
207 ,p_effective_date in date
208 )
209 is
210
211 l_proc varchar2(72) := g_package || 'chk_open';
212
213 begin
214
215 hr_utility.set_location('Entering: '||l_proc,10);
216
217 if (p_open <> null) and
218 (hr_api.not_exists_in_hr_lookups
219 (p_effective_date => p_effective_date
220 ,p_lookup_type => 'YES_NO'
221 ,p_lookup_code => p_open
222 )) then
223 -- p_open does not exist in lookup, thus error.
224 fnd_message.set_name('PER','PER_52459_APR_INVALID_OPEN_TYPE');
225 fnd_message.raise_error;
226 end if;
227
228 hr_utility.set_location('Leaving: '||l_proc,20);
229
230 EXCEPTION
231 when app_exception.application_exception then
232 if hr_multi_message.exception_add
233 (p_associated_column1 => 'PER_APPRAISALS.OPEN'
234 ) then
235 raise;
236 end if;
237 end chk_open;
238
239 -- ---------------------------------------------------------------------------+
240 -- |---------------------< chk_update_allowed >-------------------------------|
241 -- ---------------------------------------------------------------------------+
242
243 -- Description:
244 -- Validates that update is allowed, by checking that on APPRAISAL_DATE,
245 -- the system_person_type of appraisee_person_id is not 'EX_EMP' or
246 -- 'EX_EMP_APL'.
247 -- Also - that OPEN is 'Y', or is being changed to 'Y'.
248
249 -- Pre-requisites:
250 -- p_open is a valid parameter
251
252 -- IN Parameters:
253 -- p_appraisee_person_id
254 -- p_appraisal_date
255 -- p_open
256
257 -- Post Success:
258 -- Processing continues if the update is allowed.
259
260 -- Post Failure:
261 -- An application error is raised, and processing is terminated, if it is
262 -- found that the update is invalid.
263
264 -- Developer/Implementation Notes:
265 -- None.
266
267 -- Access Status:
268 -- Internal Row Handler Use Only
269
270 -- ---------------------------------------------------------------------------+
271 procedure chk_update_allowed
272 (p_rec in per_apr_shd.g_rec_type
273 )
274 is
275
276 l_proc varchar2(72) := g_package || 'chk_update_allowed';
277 l_type per_person_types.system_person_type%TYPE;
278
279 -- Cursor to fetch system_person_type for appraisee_person_id
280
281 cursor csr_fetch_type is
282 select ppt.system_person_type
283 from per_person_types ppt
284 , per_person_type_usages_f ptu
285 , per_all_people_f per
286 where per.person_id = p_rec.appraisee_person_id
287 and p_rec.appraisal_date BETWEEN per.effective_start_date AND per.effective_end_date
288 AND ptu.person_id = per.person_id
289 and p_rec.appraisal_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
290 and ptu.person_type_id = ppt.person_type_id;
291
292 l_allow_appraisal BOOLEAN := FALSE;
293 begin
294
295 hr_utility.set_location('Entering: '||l_proc,10);
296
297 -- Fetch person_type
298
299 for i in csr_fetch_type
300 LOOP
301 IF i.system_person_type IN ('CWK','EMP') THEN
302 l_allow_appraisal := TRUE;
303 END IF;
304 END LOOP;
305 -- Update not allowed
306 IF NOT l_allow_appraisal THEN
307 fnd_message.set_name('PER','PER_52456_APR_UPD_EX_EMP_APR');
308 fnd_message.raise_error;
309 END IF;
310
311 -- Check also that the appraisal is OPEN.
312
313 if (p_rec.open = 'N') then
314 -- Check that columns arent being updated (except OPEN column)
315
316 if p_rec.appraiser_person_id
317 <> per_apr_shd.g_old_rec.appraiser_person_id then
318 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
319 fnd_message.raise_error;
320 elsif p_rec.appraisal_date
321 <> per_apr_shd.g_old_rec.appraisal_date then
322 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
323 fnd_message.raise_error;
324 elsif p_rec.appraisal_period_end_date
325 <> per_apr_shd.g_old_rec.appraisal_period_end_date then
326 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
327 fnd_message.raise_error;
328 elsif p_rec.appraisal_period_start_date
329 <> per_apr_shd.g_old_rec.appraisal_period_start_date then
330 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
331 fnd_message.raise_error;
332 elsif p_rec.type
333 <> per_apr_shd.g_old_rec.type then
334 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
335 fnd_message.raise_error;
336 elsif p_rec.next_appraisal_date
337 <> per_apr_shd.g_old_rec.next_appraisal_date then
338 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
339 fnd_message.raise_error;
340 elsif p_rec.status
341 <> per_apr_shd.g_old_rec.status then
342 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
343 fnd_message.raise_error;
344 elsif p_rec.comments
345 <> per_apr_shd.g_old_rec.comments then
346 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
347 fnd_message.raise_error;
348 elsif p_rec.overall_performance_level_id
349 <> per_apr_shd.g_old_rec.overall_performance_level_id then
350 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
351 fnd_message.raise_error;
352 elsif p_rec.attribute_category
353 <> per_apr_shd.g_old_rec.attribute_category then
354 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
355 fnd_message.raise_error;
356 elsif p_rec.attribute1
357 <> per_apr_shd.g_old_rec.attribute1 then
358 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
359 fnd_message.raise_error;
360 elsif p_rec.attribute2
361 <> per_apr_shd.g_old_rec.attribute2 then
362 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
363 fnd_message.raise_error;
364 elsif p_rec.attribute3
365 <> per_apr_shd.g_old_rec.attribute3 then
366 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
367 fnd_message.raise_error;
368 elsif p_rec.attribute4
369 <> per_apr_shd.g_old_rec.attribute4 then
370 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
371 fnd_message.raise_error;
372 elsif p_rec.attribute5
373 <> per_apr_shd.g_old_rec.attribute5 then
374 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
375 fnd_message.raise_error;
376 elsif p_rec.attribute6
377 <> per_apr_shd.g_old_rec.attribute6 then
378 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
379 fnd_message.raise_error;
380 elsif p_rec.attribute7
381 <> per_apr_shd.g_old_rec.attribute7 then
382 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
383 fnd_message.raise_error;
384 elsif p_rec.attribute8
385 <> per_apr_shd.g_old_rec.attribute8 then
386 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
387 fnd_message.raise_error;
388 elsif p_rec.attribute9
389 <> per_apr_shd.g_old_rec.attribute9 then
390 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
391 fnd_message.raise_error;
392 elsif p_rec.attribute10
393 <> per_apr_shd.g_old_rec.attribute10 then
394 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
395 fnd_message.raise_error;
396 elsif p_rec.attribute11
397 <> per_apr_shd.g_old_rec.attribute11 then
398 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
399 fnd_message.raise_error;
400 elsif p_rec.attribute12
401 <> per_apr_shd.g_old_rec.attribute12 then
402 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
403 fnd_message.raise_error;
404 elsif p_rec.attribute13
405 <> per_apr_shd.g_old_rec.attribute13 then
406 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
407 fnd_message.raise_error;
408 elsif p_rec.attribute14
409 <> per_apr_shd.g_old_rec.attribute14 then
410 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
411 fnd_message.raise_error;
412 elsif p_rec.attribute15
413 <> per_apr_shd.g_old_rec.attribute15 then
414 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
415 fnd_message.raise_error;
416 elsif p_rec.attribute16
417 <> per_apr_shd.g_old_rec.attribute16 then
418 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
419 fnd_message.raise_error;
420 elsif p_rec.attribute17
421 <> per_apr_shd.g_old_rec.attribute17 then
422 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
423 fnd_message.raise_error;
424 elsif p_rec.attribute18
425 <> per_apr_shd.g_old_rec.attribute18 then
426 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
427 fnd_message.raise_error;
428 elsif p_rec.attribute19
429 <> per_apr_shd.g_old_rec.attribute19 then
430 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
431 fnd_message.raise_error;
432 elsif p_rec.attribute20
433 <> per_apr_shd.g_old_rec.attribute20 then
434 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
435 fnd_message.raise_error;
436 elsif p_rec.system_type
437 <> per_apr_shd.g_old_rec.system_type then
438 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
439 fnd_message.raise_error;
440 elsif p_rec.system_params
441 <> per_apr_shd.g_old_rec.system_params then
442 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
443 fnd_message.raise_error;
444 elsif p_rec.appraisee_access
445 <> per_apr_shd.g_old_rec.appraisee_access then
446 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
447 fnd_message.raise_error;
448 elsif p_rec.main_appraiser_id
449 <> per_apr_shd.g_old_rec.main_appraiser_id then
450 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
451 fnd_message.raise_error;
452 elsif p_rec.assignment_id
453 <> per_apr_shd.g_old_rec.assignment_id then
454 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
455 fnd_message.raise_error;
456 elsif p_rec.assignment_start_date
457 <> per_apr_shd.g_old_rec.assignment_start_date then
458 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
459 fnd_message.raise_error;
460 elsif p_rec.assignment_business_group_id
461 <> per_apr_shd.g_old_rec.assignment_business_group_id then
462 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
463 fnd_message.raise_error;
464 elsif p_rec.assignment_organization_id
465 <> per_apr_shd.g_old_rec.assignment_organization_id then
466 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
467 fnd_message.raise_error;
468 elsif p_rec.assignment_job_id
469 <> per_apr_shd.g_old_rec.assignment_job_id then
470 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
471 fnd_message.raise_error;
472 elsif p_rec.assignment_position_id
473 <> per_apr_shd.g_old_rec.assignment_position_id then
474 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
475 fnd_message.raise_error;
476 elsif p_rec.assignment_grade_id
477 <> per_apr_shd.g_old_rec.assignment_grade_id then
478 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
479 fnd_message.raise_error;
480 elsif p_rec.potential_readiness_level
481 <> per_apr_shd.g_old_rec.potential_readiness_level then
482 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
483 fnd_message.raise_error;
484 elsif p_rec.potential_short_term_workopp
485 <> per_apr_shd.g_old_rec.potential_short_term_workopp then
486 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
487 fnd_message.raise_error;
488 elsif p_rec.potential_long_term_workopp
489 <> per_apr_shd.g_old_rec.potential_long_term_workopp then
490 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
491 fnd_message.raise_error;
492 elsif p_rec.potential_details
493 <> per_apr_shd.g_old_rec.potential_details then
494 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
495 fnd_message.raise_error;
496 elsif p_rec.event_id
497 <> per_apr_shd.g_old_rec.event_id then
498 fnd_message.set_name('PER','PER_52458_APR_UPD_PUBLSHD_APR');
499 fnd_message.raise_error;
500
501 end if;
502
503
504 end if;
505
506 hr_utility.set_location('Leaving: '||l_proc,20);
507
508 end chk_update_allowed;
509
510
511 -- ---------------------------------------------------------------------------+
512 -- |--------------------< chk_appraiser_person_id >---------------------------|
513 -- ---------------------------------------------------------------------------+
514
515 -- Description:
516 -- Validates that, on update, the appraiser_person_id cannot be updated
517 -- if an answer set exists.
518 -- flemonni added and appraiser_person_id is being changed
519
520 -- Pre-requisites:
521 -- None.
522
523 -- IN Parameters:
524 -- p_appraisal_id
525
526 -- Post Success:
527 -- Processing continues, and the appraiser_person_id is updated if an answer
528 -- set does not already exist.
529
530 -- Post Failure:
531 -- An application error is raised, and processing is terminated if an answer
532 -- set already exists.
533
534 -- Developer/Implementation Notes:
535 -- None.
536
537 -- Access Status:
538 -- Internal Row Handler Use Only.
539
540 -- ---------------------------------------------------------------------------+
541 procedure chk_appraiser_person_id
542 (p_appraisal_id in per_appraisals.appraisal_id%TYPE
543 )
544 is
545
546 l_proc varchar2(72) := g_package || 'chk_appraiser_person_id';
547 l_exists varchar2(1);
548
549 -- Cursor to determine id an answer set exists for the current
550 -- appraiser_person_id.
551
552 cursor csr_answer_set_exists is
553 select null
554 from hr_quest_answers qsa
555 where p_appraisal_id = qsa.type_object_id
556 and qsa.type = 'APPRAISAL';
557
558 begin
559
560 hr_utility.set_location('Entering: '|| l_proc,10);
561
562 -- flemonni added
563
564 if p_appraisal_id <> per_apr_shd.g_old_rec.appraisal_id then
565 open csr_answer_set_exists;
566 fetch csr_answer_set_exists into l_exists;
567
568 if csr_answer_set_exists%found then
569 -- answer set does exists, thus cannot update => error!
570 close csr_answer_set_exists;
571 fnd_message.set_name('PER','PER_52457_APR_UPD_COMPLTD_APR');
572 fnd_message.raise_error;
573 end if;
574 close csr_answer_set_exists;
575 else
576 null;
577 end if;
578
579 hr_utility.set_location('Leaving: '||l_proc,20);
580
581 EXCEPTION
582 when app_exception.application_exception then
583 if hr_multi_message.exception_add
584 (p_associated_column1 => 'PER_APPRAISALS.APPRAISER_PERSON_ID'
585 ) then
586 raise;
587 end if;
588
589 end chk_appraiser_person_id;
590
591
592 ----------------------------------------------------------------------------+
593 ---------------------------<chk_appraisal_template>-------------------------+
594 ----------------------------------------------------------------------------+
595
596 -- Description:
597 -- - Validates that the appraisal template exists and is within the same business
598 -- group as that of appraisal
599 -- - Validates that the appraisal template exists as of the users effetcive date
600
601 -- Pre_conditions:
602
603
604 -- In Arguments:
605 -- p_appraisal_template_id
606 -- p_business_group_id
607 -- p_effective_date
608
609 -- Post Success:
610 -- Process continues if :
611 -- All the in parameters are valid.
612
613 -- Post Failure:
614 -- An application error is raised and processing is terminated if any of
615 -- the following cases are found :
616 -- -- appraisal template does not exist
617 -- -- appraisal template exists but not with the same business group
618 -- -- appraisal_template_id is not set
619 -- -- appraisal template exists but not as of the effective date
620
621 -- Access Status
622 -- Internal Table Handler Use Only.
623
624
625 procedure chk_appraisal_template
626 (p_appraisal_template_id in per_appraisals.appraisal_template_id%TYPE
627 ,p_business_group_id in per_appraisals.business_group_id%TYPE
628 ,p_effective_date in date
629 )
630 is
631
632 l_exists varchar2(1);
633 l_proc varchar2(72) := g_package||'chk_appraisal_template';
634 l_business_group_id per_appraisals.business_group_id%TYPE;
635
636
637 -- Cursor to check if appraisal exists
638
639 Cursor csr_appraisal_exists
640 is
641 select business_group_id
642 from per_appraisal_templates
643 where appraisal_template_id = p_appraisal_template_id;
644
645 -- Cursor to check if the appraisal template is
646 -- valid as of users effective date
647
648 Cursor csr_appraisal_template_valid
649 is
650 select 'Y'
651 from per_appraisal_templates
652 where appraisal_template_id = p_appraisal_template_id
653 and p_effective_date between
654 nvl(date_from,hr_api.g_sot) and nvl(date_to,hr_api.g_eot);
655
656
657 begin
658 hr_utility.set_location('Entering:'|| l_proc, 1);
659
660 -- Check mandatory parameters have been set
661
662 hr_api.mandatory_arg_error
663 (p_api_name => l_proc
664 ,p_argument => 'business_group_id'
665 ,p_argument_value => p_business_group_id
666 );
667
668
669 hr_api.mandatory_arg_error
670 (p_api_name => l_proc
671 ,p_argument => 'effective_date'
672 ,p_argument_value => p_effective_date
673 );
674
675 hr_utility.set_location('Entering:'|| l_proc, 2);
676
677 -- check if the user has entered a appraisal_template_id
678 -- It is mandatory column.
679
680 if p_appraisal_template_id is null then
681 hr_utility.set_message(800,'HR_52256_APR_TEMP_MANDATORY');
682 hr_utility.raise_error;
683 end if;
684
685 hr_utility.set_location(l_proc, 3);
686
687 if p_appraisal_template_id is not null then
688 open csr_appraisal_exists;
689 fetch csr_appraisal_exists into l_business_group_id;
690 if csr_appraisal_exists%notfound then
691 close csr_appraisal_exists;
692 hr_utility.set_message(801,'HR_52246_APR_TEMP_NOT_EXIST');
693 hr_utility.raise_error;
694 end if;
695 close csr_appraisal_exists;
696
697 -- check if appraisal template is in the same business group
698
699 if l_business_group_id <> p_business_group_id then
700 hr_utility.set_message(801,'HR_51883_TEMP_DIFF_BUS_GRP');
701 hr_utility.raise_error;
702 end if;
703
704 -- check if appraisal template exists as of users effective date
705
706 open csr_appraisal_template_valid;
707 fetch csr_appraisal_template_valid into l_exists;
708 if csr_appraisal_template_valid%notfound then
709 close csr_appraisal_template_valid;
710 hr_utility.set_message(801,'HR_51884_APR_TEMP_NOT_DATE');
711 hr_utility.raise_error;
712 end if;
713 close csr_appraisal_template_valid;
714 end if;
715
716 hr_utility.set_location(l_proc, 4);
717
718 hr_utility.set_location('Leaving: '|| l_proc, 10);
719 EXCEPTION
720 when app_exception.application_exception then
721 if hr_multi_message.exception_add
722 (p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_TEMPLATE_ID'
723 ) then
724 raise;
725 end if;
726
727 end chk_appraisal_template;
728
729 ----------------------------------------------------------------------------+
730 ----------------------------<chk_appraisee_appraiser>-----------------------+
731 ----------------------------------------------------------------------------+
732
733 -- Description:
734 -- - Validates that the person_id (appraisee_id or appraiser_id) have been entered
735 -- as these are mandatory fields
736 -- - Validates that the person is in the same business group as the appraisal
737 -- - Validates that the person is valid as of appraisal date
738
739 -- Pre_conditions:
740
741 -- In Arguments:
742 -- p_person_id
743 -- p_effective_date
744 -- p_business_group_id
745 -- p_person_type
746
747 -- Post Success:
748 -- Process continues if :
749 -- All the in parameters are valid.
750
751 -- Post Failure:
752 -- An application error is raised and processing is terminated if any of
753 -- the following cases are found :
754 -- -- effective_date is not set
755 -- -- person is not in the same business group as the appraisal
756 -- -- person does not exist as of effective date
757
758 -- Access Status
759 -- Internal Table Handler Use Only.
760
761
762 procedure chk_appraisee_appraiser
763 (p_person_id in per_people_f.person_id%TYPE
764 ,p_business_group_id in per_appraisals.business_group_id%TYPE
765 ,p_effective_date in date
766 ,p_person_type in varchar2
767 )
768 is
769
770 l_exists varchar2(1);
771 l_business_group_id per_appraisals.business_group_id%TYPE;
772 l_proc varchar2(72) := g_package||'chk_appraisee_appraiser';
773 lv_cross_business_group varchar2(10); -- bug 1980440 fix
774
775
776 -- Cursor to check if the person_exists
777
778 Cursor csr_person_bg
779 is
780 select business_group_id
781 from per_all_people_f
782 where person_id = p_person_id;
783
784 -- bug 1980440 fix
785 -- WE NEED to use different cursors as Appraiser can be changed to
786 -- a person from a different BG
787 -- Cursor to check if the person_exists
788
789 Cursor csr_cbg_person_bg
790 is
791 select business_group_id
792 from per_all_people_f
793 where person_id = p_person_id;
794
795 -- Cursor to check if person is valid
796 -- as of effective date
797
798 Cursor csr_person_valid_date
799 is
800 select 'Y'
801 from per_all_people_f
802 where person_id = p_person_id
803 and p_effective_date between
804 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
805
806 -- bug 1980440 fix
807 -- WE NEED to use different cursors as Appraiser can be changed to
808 -- a person from a different BG
809 -- Cursor to check if person is valid
810 -- as of effective date
811
812 Cursor csr_cbg_person_valid_date
813 is
814 select 'Y'
815 from per_all_people_f
816 where person_id = p_person_id
817 and p_effective_date between
818 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
819
820 begin
821 hr_utility.set_location('Entering:'|| l_proc, 1);
822
823 if (p_person_id is NULL) then
824 if p_person_type = 'APPRAISEE' then
825 hr_utility.set_message(801, 'HR_51887_APR_APPRAISEE_NULL');
826 hr_utility.raise_error;
827 elsif p_person_type = 'APPRAISER' then
828 hr_utility.set_message(801, 'HR_51888_APR_APPRAISER_NULL');
829 hr_utility.raise_error;
830 end if;
831 end if;
832
833 -- Check mandatory parameters have been set
834
835 hr_api.mandatory_arg_error
836 (p_api_name => l_proc
837 ,p_argument => 'effective_date'
838 ,p_argument_value => p_effective_date
839 );
840
841
842 hr_utility.set_location('Entering:'|| l_proc, 2);
843
844 -- bug 1980440 fix starts
845 -- if CROSS_BUSINESS_GROUP option is enabled we shouldn't do a comparison
846 -- between appraisers BG and appraisee BG as they may be different
847 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
848
849 if p_person_id is not null then
850 IF lv_cross_business_group <> 'Y' THEN
851 open csr_person_bg;
852 fetch csr_person_bg into l_business_group_id;
853 if csr_person_bg%notfound then
854 close csr_person_bg;
855 hr_utility.set_message(801,'HR_51889_APR_PERSON_NOT_EXIST');
856 hr_utility.raise_error;
857 end if;
858 close csr_person_bg;
859 ELSE
860 open csr_cbg_person_bg;
861 fetch csr_cbg_person_bg into l_business_group_id;
862 if csr_cbg_person_bg%notfound then
863 close csr_cbg_person_bg;
864 hr_utility.set_message(801,'HR_51889_APR_PERSON_NOT_EXIST');
865 hr_utility.raise_error;
866 end if;
867 close csr_cbg_person_bg;
868 END IF;
869
870 IF lv_cross_business_group <> 'Y' THEN
871 hr_utility.set_location(l_proc, 3);
872 -- check if business group match
873 if p_business_group_id <> l_business_group_id then
874 hr_utility.set_message(801,'HR_51890_APR_PERSON_DIFF_BG');
875 hr_utility.raise_error;
876 end if;
877 end if;
878
879 hr_utility.set_location(l_proc, 4);
880 -- check if person is valid as of effective date
881 IF lv_cross_business_group <> 'Y' THEN
882 open csr_person_valid_date;
883 fetch csr_person_valid_date into l_exists;
884 if csr_person_valid_date%notfound then
885 close csr_person_valid_date;
886 hr_utility.set_message(801,'HR_51891_APR_PERSON_DATE_RANGE');
887 hr_utility.raise_error;
888 end if;
889 close csr_person_valid_date;
890 ELSE
891 open csr_cbg_person_valid_date;
892 fetch csr_cbg_person_valid_date into l_exists;
893 if csr_cbg_person_valid_date%notfound then
894 close csr_cbg_person_valid_date;
895 hr_utility.set_message(801,'HR_51891_APR_PERSON_DATE_RANGE');
896 hr_utility.raise_error;
897 end if;
898 close csr_cbg_person_valid_date;
899 END IF;
900 -- bug 1980440 fix ends
901 end if;
902
903 hr_utility.set_location(l_proc, 5);
904
905 hr_utility.set_location('Leaving: '|| l_proc, 10);
906
907 end chk_appraisee_appraiser;
908
909 ------------------------------------------------------------------------------+
910 ------------------------------<chk_main_appraiser_id>-------------------------+
911 ------------------------------------------------------------------------------+
912 -- Description:
913 -- - Validates that the main appraiser is valid as of effective date.
914
915 -- Pre_conditions:
916
917 -- In Arguments:
918 -- p_main_appraiser_id
919
920 -- Post Success:
921 -- Process continues if :
922 -- All the in parameters are valid.
923
924 -- Post Failure:
925 -- An application error is raised and processing is terminated if any of
926 -- the following cases are found :
927 -- -- effective_date is not set
928 -- -- main appraiser does not exist as of effective date
929
930 -- Access Status
931 -- Internal Table Handler Use Only.
932
933 procedure chk_main_appraiser_id
934 (p_main_appraiser_id in per_appraisals.main_appraiser_id%TYPE
935 ,p_business_group_id in per_appraisals.business_group_id%TYPE
936 ,p_effective_date in date
937
938 )
939 is
940
941 --
942 l_proc varchar2(72) := g_package||'chk_main_appraiser_id';
943 lv_cross_business_group varchar2(10);
944 l_exists varchar2(1);
945 l_business_group_id per_appraisals.business_group_id%TYPE;
946
947 --
948
949 -- Cursor to check if person is valid
950 -- as of effective date
951
952 Cursor csr_person_valid_date
953 is
954 select 'Y'
955 from per_all_people_f
956 where person_id = p_main_appraiser_id
957 and business_group_id = p_business_group_id
958 and p_effective_date between
959 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
960
961 -- Cursor to check if person is valid
962 -- as of effective date
963
964 Cursor csr_cbg_person_valid_date
965 is
966 select 'Y'
967 from per_all_people_f
968 where person_id = p_main_appraiser_id
969 and p_effective_date between
970 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
971
972
973 begin
974
975 hr_utility.set_location('Entering:'|| l_proc, 5);
976
977 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
978
979 IF p_main_appraiser_id IS NULL OR p_main_appraiser_id = -1 THEN
980 return;
981 END IF;
982
983 -- Check mandatory parameters have been set
984
985 hr_api.mandatory_arg_error
986 (p_api_name => l_proc
987 ,p_argument => 'effective_date'
988 ,p_argument_value => p_effective_date
989 );
990
991 hr_utility.set_location( l_proc, 6);
992
993 -- check if person is valid as of effective date
994 IF lv_cross_business_group <> 'Y' THEN
995 hr_utility.set_location( l_proc, 7);
996 open csr_person_valid_date;
997 fetch csr_person_valid_date into l_exists;
998 if csr_person_valid_date%notfound then
999 hr_utility.set_location( l_proc, 8);
1000 close csr_person_valid_date;
1001 hr_utility.set_message(800,'HR_449030_MA_PERSON_DATE_RANGE');
1002 hr_utility.raise_error;
1003 end if;
1004 close csr_person_valid_date;
1005 ELSE
1006 open csr_cbg_person_valid_date;
1007 hr_utility.set_location( l_proc, 9);
1008 fetch csr_cbg_person_valid_date into l_exists;
1009 if csr_cbg_person_valid_date%notfound then
1010 hr_utility.set_location( l_proc, 10);
1011 close csr_cbg_person_valid_date;
1012 hr_utility.set_message(800,'HR_449030_MA_PERSON_DATE_RANGE');
1013 hr_utility.raise_error;
1014 end if;
1015 close csr_cbg_person_valid_date;
1016 END IF;
1017 -- bug 1980440 fix ends
1018 hr_utility.set_location('Leaving:'|| l_proc, 10);
1019
1020 EXCEPTION
1021 when app_exception.application_exception then
1022 if hr_multi_message.exception_add
1023 (p_associated_column1 => 'PER_APPRAISALS.MAIN_APPRAISER_ID'
1024 ) then
1025 raise;
1026 end if;
1027
1028 end chk_main_appraiser_id;
1029 ------------------------------------------------------------------------------+
1030 ------------------------------<chk_appraisal_type>----------------------------+
1031 ------------------------------------------------------------------------------+
1032
1033 -- Description:
1034 -- - Validates that a valid appraisal type is set
1035 -- - Validates that it is exists as lookup code for that type
1036
1037 -- Pre_conditions:
1038
1039 -- In Arguments:
1040 -- p_appraisal_id
1041 -- p_appraisal_type
1042 -- p_object_version_number
1043 -- p_effective_date
1044
1045 -- Post Success:
1046 -- Process continues if :
1047 -- All the in parameters are valid.
1048
1049 -- Post Failure:
1050 -- An application error is raised and processing is terminated if any of
1051 -- the following cases are found :
1052 -- - appraisal type is invalid
1053
1054 -- Access Status
1055 -- Internal Table Handler Use Only.
1056
1057 procedure chk_appraisal_type
1058 (p_appraisal_id in per_appraisals.appraisal_id%TYPE
1059 ,p_object_version_number in per_appraisals.object_version_number%TYPE
1060 ,p_type in per_appraisals.type%TYPE
1061 ,p_effective_date in date
1062 )
1063 is
1064
1065 l_api_updating boolean;
1066 l_proc varchar2(72) := g_package||'chk_appraisal_type';
1067
1068
1069 begin
1070 hr_utility.set_location('Entering:'|| l_proc, 1);
1071
1072 -- Check mandatory parameters have been set
1073
1074 hr_api.mandatory_arg_error
1075 (p_api_name => l_proc
1076 ,p_argument => 'effective_date'
1077 ,p_argument_value => p_effective_date
1078 );
1079
1080 -- Only proceed with validation if :
1081 -- a) The current g_old_rec is current and
1082 -- b) The value for appraisal type flag has changed
1083
1084 l_api_updating := per_apr_shd.api_updating
1085 (p_appraisal_id => p_appraisal_id
1086 ,p_object_version_number => p_object_version_number);
1087
1088 if ( (l_api_updating and nvl(per_apr_shd.g_old_rec.type,
1089 hr_api.g_varchar2)
1090 <> nvl(p_type,hr_api.g_varchar2)
1091 ) or
1092 (NOT l_api_updating)
1093 ) then
1094
1095 hr_utility.set_location(l_proc, 2);
1096
1097
1098 -- If appraisal type is not null then
1099 -- check if the value exists in hr_lookups
1100 -- where the lookup_type = 'APPRAISAL_TYPE'
1101
1102
1103 if p_type is not null then
1104 if hr_api.not_exists_in_hr_lookups
1105 (p_effective_date => p_effective_date
1106 ,p_lookup_type => 'APPRAISAL_TYPE'
1107 ,p_lookup_code => p_type
1108 ) then
1109 -- error invalid type
1110 hr_utility.set_message(801,'HR_51892_APR_INVALID_TYPE');
1111 hr_utility.raise_error;
1112 end if;
1113 hr_utility.set_location(l_proc, 3);
1114 end if;
1115 end if;
1116 hr_utility.set_location('Leaving: '|| l_proc, 10);
1117 EXCEPTION
1118 when app_exception.application_exception then
1119 if hr_multi_message.exception_add
1120 (p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_TYPE'
1121 ) then
1122 raise;
1123 end if;
1124
1125
1126 end chk_appraisal_type;
1127
1128 ------------------------------------------------------------------------------+
1129 ------------------------------<chk_appraisal_status>--------------------------+
1130 ------------------------------------------------------------------------------+
1131
1132 -- Description:
1133 -- - Validates that a valid appraisal status set
1134 -- - Validates that it is exists as lookup code for that type
1135
1136 -- Pre_conditions:
1137
1138 -- In Arguments:
1139 -- p_appraisal_id
1140 -- p_status
1141 -- p_object_version_number
1142 -- p_effective_date
1143
1144 -- Post Success:
1145 -- Process continues if :
1146 -- All the in parameters are valid.
1147
1148 -- Post Failure:
1149 -- An application error is raised and processing is terminated if any of
1150 -- the following cases are found :
1151 -- - appraisal status is invalid
1152
1153 -- Access Status
1154 -- Internal Table Handler Use Only.
1155
1156 procedure chk_appraisal_status
1157 (p_appraisal_id in per_appraisals.appraisal_id%TYPE
1158 ,p_object_version_number in per_appraisals.object_version_number%TYPE
1159 ,p_status in per_appraisals.status%TYPE
1160 ,p_effective_date in date
1161 )
1162 is
1163
1164 l_api_updating boolean;
1165 l_proc varchar2(72) := g_package||'chk_appraisal_status';
1166
1167
1168 begin
1169 hr_utility.set_location('Entering:'|| l_proc, 1);
1170
1171 -- Check mandatory parameters have been set
1172
1173 hr_api.mandatory_arg_error
1174 (p_api_name => l_proc
1175 ,p_argument => 'effective_date'
1176 ,p_argument_value => p_effective_date
1177 );
1178
1179 -- Only proceed with validation if :
1180 -- a) The current g_old_rec is current and
1181 -- b) The value for appraisal status flag has changed
1182
1183 l_api_updating := per_apr_shd.api_updating
1184 (p_appraisal_id => p_appraisal_id
1185 ,p_object_version_number => p_object_version_number);
1186
1187 if ( (l_api_updating and nvl(per_apr_shd.g_old_rec.status,
1188 hr_api.g_varchar2)
1189 <> nvl(p_status,hr_api.g_varchar2)
1190 ) or
1191 (NOT l_api_updating)
1192 ) then
1193
1194 hr_utility.set_location(l_proc, 2);
1195
1196
1197 -- If appraisal status is not null then
1198 -- check if the value exists in hr_lookups
1199 -- where the lookup_type = 'APPRAISAL_ASSESSMENT_STATUS'
1200
1201
1202 if p_status is not null then
1203 if hr_api.not_exists_in_hr_lookups
1204 (p_effective_date => p_effective_date
1205 ,p_lookup_type => 'APPRAISAL_ASSESSMENT_STATUS'
1206 ,p_lookup_code => p_status
1207 ) then
1208 -- error invalid type
1209 hr_utility.set_message(801,'HR_51893_APR_INVALID_STATUS');
1210 hr_utility.raise_error;
1211 end if;
1212 hr_utility.set_location(l_proc, 3);
1213 end if;
1214 end if;
1215 hr_utility.set_location('Leaving: '|| l_proc, 10);
1216
1217 EXCEPTION
1218 when app_exception.application_exception then
1219 if hr_multi_message.exception_add
1220 (p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_STATUS'
1221 ) then
1222 raise;
1223 end if;
1224
1225 end chk_appraisal_status;
1226
1227
1228 -- ---------------------------------------------------------------------------+
1229 -- |----------------------< chk_group_date_id >-------------------------------|
1230 -- ---------------------------------------------------------------------------+
1231
1232 -- DESCRIPTION
1233 -- If the GROUP_INITIATOR_ID is not null, the GROUP_DATE must also be not null
1234 -- and vica versa.
1235
1236 -- PRE-REQUISITES
1237
1238 -- IN PARAMETERS
1239 -- group_initiator_id
1240 -- group_date
1241
1242 -- POST SUCCESS
1243 -- Processing continues
1244
1245 -- POST FAILURE
1246 -- Processing terminates
1247
1248 -- ACCESS STATUS
1249 -- Internal Development Use Only
1250
1251 Procedure chk_group_date_id
1252 (p_group_initiator_id in per_assessments.group_initiator_id%TYPE
1253 ,p_group_date in per_assessments.group_date%TYPE
1254 )
1255 is
1256
1257 l_proc varchar2(72):=g_package||'chk_group_date_id';
1258
1259
1260 begin
1261 hr_utility.set_location('Entering:'|| l_proc, 1);
1262
1263 -- Tests are carried out on insert only.
1264
1265
1266 If ((p_group_initiator_id is not null And p_group_date is null) Or
1267 (p_group_initiator_id is null AND p_group_date is not null)) Then
1268 /*
1269 If (p_group_initiator_id is null and p_group_date is null) then
1270 null;
1271 elsif (p_group_initiator_id is not null and p_group_date is not null) then
1272 null;
1273 else
1274 */
1275
1276 -- raise an error as the either both should exist or neither should.
1277
1278 hr_utility.set_message(801, 'HR_52308_CM_GPR_DATE_ID_PROB');
1279 hr_utility.raise_error;
1280
1281 end if;
1282
1283 hr_utility.set_location('Leaving:'|| l_proc, 2);
1284 EXCEPTION
1285 when app_exception.application_exception then
1286 if hr_multi_message.exception_add
1287 (p_associated_column1 => 'PER_ASSESSMENTS.GROUP_DATE'
1288 ) then
1289 raise;
1290 end if;
1291
1292 end chk_group_date_id;
1293
1294 -- ---------------------------------------------------------------------------+
1295 -- |----------------------< chk_group_initiator_id >--------------------------|
1296 -- ---------------------------------------------------------------------------+
1297
1298 -- DESCRIPTION
1299 -- GROUP_INITIATOR_ID must be of the same business group and must exist on
1300 -- the group_date.
1301
1302 -- PRE-REQUISITES
1303
1304 -- IN PARAMETERS
1305 -- group_initiator_id
1306 -- business_group_id
1307 -- group_date
1308
1309 -- POST SUCCESS
1310 -- Processing continues
1311
1312 -- POST FAILURE
1313 -- Processing terminates
1314
1315 -- ACCESS STATUS
1316 -- Internal Development Use Only
1317
1318 Procedure chk_group_initiator_id
1319 (p_group_initiator_id in per_assessments.group_initiator_id%TYPE
1320 ,p_business_group_id in per_assessments.business_group_id%TYPE
1321 ,p_group_date in per_assessments.group_date%TYPE
1322 )
1323 is
1324
1325 l_proc varchar2(72):=g_package||'chk_group_initiator_id';
1326
1327 cursor csr_chk_grp_per_sta_date is
1328 select distinct(min(effective_start_date)), business_group_id
1329 from per_all_people_f per
1330 where per.person_id = p_group_initiator_id
1331 group by business_group_id;
1332
1333 l_asn_grp_pers_sta_date per_people_f.start_date%TYPE;
1334 l_asn_grp_pers_bg per_people_f.business_group_id%TYPE;
1335
1336 lv_cross_business_group VARCHAR2(10); -- bug 1980440 fix
1337 begin
1338 hr_utility.set_location('Entering:'|| l_proc, 1);
1339
1340 -- Tests are carried out on insert, and update (even if values haven't changed)
1341 -- as data in the referenced table may have.
1342
1343 -- chk_group_date_id contains check whether group_date and group_initiator_id
1344 -- values are valid. return if null;
1345 if p_group_initiator_id IS NOT NULL THEN
1346
1347 open csr_chk_grp_per_sta_date;
1348 fetch csr_chk_grp_per_sta_date into l_asn_grp_pers_sta_date,l_asn_grp_pers_bg;
1349
1350 if (csr_chk_grp_per_sta_date%notfound or l_asn_grp_pers_sta_date IS NULL) then
1351
1352 close csr_chk_grp_per_sta_date;
1353
1354 -- raise an error as the person_id doesn't exist
1355
1356 hr_utility.set_message(801, 'HR_52305_ASN_GRPPER_NOT_EXIST');
1357 hr_utility.raise_error;
1358
1359 end if;
1360 close csr_chk_grp_per_sta_date;
1361
1362 -- The person has to be in the correct business group
1363
1364 -- bug 1980440 fix starts
1365 -- if CROSS_BUSINESS_GROUP option is enabled we shouldn't do a comparison
1366 -- between GroupInitiator's BG and Appraisee BG as they may be different
1367 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
1368
1369 if lv_cross_business_group <> 'Y' THEN
1370
1371 if (l_asn_grp_pers_bg <> p_business_group_id) then
1372
1373 -- raise an error as the person is in the wrong business_group
1374
1375 hr_utility.set_message(801, 'HR_52306_ASN_GRPPER_NOT_BG');
1376 hr_utility.raise_error;
1377
1378 end if;
1379 end if;
1380 -- bug 1980440 fix ends
1381
1382 -- The group_date has to be on or after the group initiators start date
1383
1384 if (p_group_date < l_asn_grp_pers_sta_date) then
1385
1386 hr_utility.set_message(801, 'HR_52307_ASN_GRPPER_NO_XIST_DA');
1387 hr_utility.raise_error;
1388
1389 end if;
1390 ELSE
1391 NULL;
1392 END IF;
1393
1394 hr_utility.set_location('Leaving:'|| l_proc, 1);
1395
1396 EXCEPTION
1397 when app_exception.application_exception then
1398 if hr_multi_message.exception_add
1399 (p_associated_column1 => 'PER_ASSESSMENTS.GROUP_INITIATOR_ID'
1400 ) then
1401 raise;
1402 end if;
1403 end chk_group_initiator_id;
1404
1405
1406
1407 ------------------------------------------------------------------------------+
1408 ----------------------------<chk_appraisal_period_dates>----------------------+
1409 ------------------------------------------------------------------------------+
1410
1411 -- Description:
1412 -- - Validates that the appraisal_period_start_date is less than or equal to
1413 -- appraisal_period_end_date
1414 -- - Validates that the appraisal_period_end_date is greater than or equal to
1415 -- appraisal_period_start_date
1416
1417 -- Pre_conditions:
1418
1419 -- In Arguments:
1420 -- p_appraisal_id
1421 -- p_appraisal_period_start_date
1422 -- p_appraisal_period_end_date
1423 -- p_object_version_number
1424
1425 -- Post Success:
1426 -- Process continues if :
1427 -- All the in parameters are valid.
1428
1429 -- Post Failure:
1430 -- An application error is raised and processing is terminated if any of
1431 -- the following cases are found :
1432 -- - the appraisal_period_start_date is greater than appraisal_period_end_date
1433 -- - the appraisal_period_end_date is less than appraisal_period_start_date
1434
1435 -- Access Status
1436 -- Internal Table Handler Use Only.
1437
1438 procedure chk_appraisal_period_dates
1439 (p_appraisal_id in per_appraisals.appraisal_id%TYPE
1440 ,p_object_version_number in per_appraisals.object_version_number%TYPE
1441 ,p_appraisal_period_start_date in per_appraisals.appraisal_period_start_date%TYPE
1442 ,p_appraisal_period_end_date in per_appraisals.appraisal_period_end_date%TYPE
1443 )
1444 is
1445
1446 l_api_updating boolean;
1447 l_proc varchar2(72) := g_package||'chk_appraisal_period_dates';
1448
1449 begin
1450 hr_utility.set_location('Entering:'|| l_proc, 1);
1451
1452 if (p_appraisal_period_start_date is NULL) then
1453 hr_utility.set_message(801, 'HR_51894_APR_START_DATE_NULL');
1454 hr_utility.raise_error;
1455 elsif (p_appraisal_period_end_date is NULL) then
1456 hr_utility.set_message(801, 'HR_51895_APR_END_DATE_NULL');
1457 hr_utility.raise_error;
1458 end if;
1459
1460 -- Only proceed with validation if :
1461 -- a) The current g_old_rec is current and
1462
1463 l_api_updating := per_apr_shd.api_updating
1464 (p_appraisal_id => p_appraisal_id
1465 ,p_object_version_number => p_object_version_number);
1466
1467 -- Do the check
1468 hr_utility.set_location(l_proc, 2);
1469
1470 -- The start date has to be <= the end date and end date >= strat date, else error.
1471
1472 -- Fix 3062009
1473 -- Removed If condition for raising error when start date < end date.
1474
1475 if (p_appraisal_period_end_date < p_appraisal_period_start_date) then
1476 hr_utility.set_message(801, 'HR_51897_APR_END_DATE_LATER');
1477 hr_utility.raise_error;
1478 end if;
1479
1480 hr_utility.set_location('Leaving: '|| l_proc, 10);
1481 end chk_appraisal_period_dates;
1482
1483 -- ---------------------------------------------------------------------------+
1484 -- |---------------------< chk_appraisal_dates >------------------------------|
1485 -- ---------------------------------------------------------------------------+
1486
1487 -- Description:
1488 -- Validates that the appraisal_period_start_date,
1489 -- appraisal_period_end_date,
1490 -- appraisal_date
1491 -- cannot be set to a date where the appraisee_person_id has a system_
1492 -- person_type of 'EX_EMP' or 'EX_EMP_APL'.
1493 -- Also, that the appraisal_date cannot be set to a day when the
1494 -- appraiser_person_id has a system_person_type of 'EX_EMP' or 'EX_EMP_APL'
1495
1496 -- Pre-requisites:
1497 -- Dates are valid.
1498
1499 -- IN Parameters:
1500 -- p_appraisal_period_end_date
1501 -- p_appraisal_period_start_date
1502 -- p_appraisal_date
1503 -- p_appraisee_person_id
1504 -- p_appraiser_person_id
1505 -- p_main_appraiser_id
1506
1507 -- Post Success:
1508 -- Processing continues if the dates are all valid.
1509
1510 -- Post Failure:
1511 -- An application error is raised, and processing is terminated if the
1512 -- dates are invalid.
1513
1514 -- Developer/Implementation Notes:
1515 -- None.
1516
1517 -- Access Status:
1518 -- Internal Row Handler Use Only.
1519
1520 -- ---------------------------------------------------------------------------+
1521 procedure chk_appraisal_dates
1522 (p_appraisal_date in per_appraisals.appraisal_date%TYPE
1523 ,p_appraisal_period_start_date
1524 in per_appraisals.appraisal_period_start_date%TYPE
1525 ,p_appraisal_period_end_date
1526 in per_appraisals.appraisal_period_end_date%TYPE
1527 ,p_next_appraisal_date in per_appraisals.next_appraisal_date%TYPE
1528 ,p_appraisee_person_id
1529 in per_appraisals.appraisee_person_id%TYPE
1530 ,p_appraiser_person_id
1531 in per_appraisals.appraiser_person_id%TYPE
1532 ,p_main_appraiser_id in per_appraisals.main_appraiser_id%TYPE
1533 )
1534 is
1535
1536 -- Modified this procedure to add check on next appraisal date. Fix for bug 3061901
1537
1538 l_proc varchar2(72) := g_package || 'chk_appraisal_dates';
1539 l_valid boolean;
1540
1541 -- Function to return whether or not the date is valid
1542
1543 function validate_date
1544 (p_date in date
1545 ,p_person_id in per_all_people_f.person_id%TYPE
1546 ) RETURN boolean IS
1547
1548 l_exists varchar2(1);
1549 l_person_type per_person_types.system_person_type%TYPE;
1550 l_return boolean;
1551
1552 -- Cursor to fetch the person_type
1553
1554 -- Added person_type_id where clause
1555 -- Bug 820841
1556 -- changed the cursor for bug 7113142
1557 cursor csr_fetch_type is
1558 select pt.system_person_type
1559 from
1560 per_all_people_f per
1561 ,per_person_type_usages_f ptu
1562 ,per_person_types pt
1563 where per.person_id = p_person_id
1564 and p_date BETWEEN per.effective_start_date and per.effective_end_date
1565 AND per.person_id = ptu.person_id
1566 AND p_date between ptu.effective_start_date AND ptu.effective_End_date
1567 AND ptu.person_type_id = pt.person_type_id
1568 AND pt.system_person_type IN ('EMP','CWK','EMP_APL','CWK_APL');
1569
1570 begin
1571 -- fetch person_type
1572 open csr_fetch_type;
1573 fetch csr_fetch_type into l_person_type;
1574 -- Fix 3082788 Start
1575 IF csr_fetch_type%NOTFOUND then
1576 CLOSE csr_fetch_type;
1577 return FALSE;
1578 -- Fix 3082788 End
1579 ELSE
1580 close csr_fetch_type;
1581 return TRUE;
1582 END IF;
1583
1584 end validate_date;
1585
1586
1587 begin
1588
1589 hr_utility.set_location('Entering: '||l_proc,10);
1590
1591 -- Determine whether each of the dates are valid
1592 l_valid :=
1593 validate_date(p_appraisal_period_start_date, p_appraisee_person_id);
1594 if not l_valid then
1595 fnd_message.set_name('PER','PER_52452_APR_INVALID_START'); -- Fix 2516903
1596 hr_multi_message.add(p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_PERIOD_START_DATE');
1597 --fnd_message.raise_error;
1598 end if;
1599
1600 hr_utility.set_location(l_proc,20);
1601
1602 l_valid :=
1603 validate_date(p_appraisal_date,p_appraisee_person_id);
1604 if not l_valid then
1605 fnd_message.set_name('PER','PER_52453_APR_INVALID_APR_DATE');
1606 hr_multi_message.add(p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_DATE');
1607 --fnd_message.raise_error;
1608 end if;
1609
1610 hr_utility.set_location(l_proc,30);
1611
1612 l_valid :=
1613 validate_date(p_appraisal_period_end_date, p_appraisee_person_id);
1614 if not l_valid then
1615 fnd_message.set_name('PER','PER_52454_APR_INVALID_END'); -- Fix 2485178
1616 hr_multi_message.add(p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_PERIOD_END_DATE');
1617 --fnd_message.raise_error;
1618 end if;
1619
1620 hr_utility.set_location(l_proc,40);
1621
1622 -- Check that appraisal_date is not set to a date when appraiser_person_id
1623 -- in not person type of 'EX_EMP' or 'EX_EMP_APL'
1624 -- 6825820 Bug Fix
1625 -- Checking if appraiser is different from the appraisee and the main appraiser and if so by passing the validation
1626 if((p_appraisee_person_id <> p_appraiser_person_id) and (p_appraiser_person_id <> p_main_appraiser_id))
1627 then
1628 l_valid := true;
1629 else
1630 l_valid :=
1631 validate_date(p_appraisal_date, p_appraiser_person_id);
1632 if not l_valid then
1633 fnd_message.set_name('PER','PER_52455_APR_INVALID_APR_DATE');
1634 hr_multi_message.add(p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_DATE');
1635 -- fnd_message.raise_error;
1636 end if;
1637 end if;
1638
1639
1640 -- Check added for Bug 820841
1641
1642 hr_utility.set_location(l_proc,50);
1643 -- The appraisal date has to be >= appraisal start date, else error.
1644
1645 if (p_appraisal_period_start_date > p_appraisal_date) then
1646
1647 hr_utility.set_message(800, 'HR_52792_INVALID_APPR_DATE'); -- Fix 3061934.
1648 hr_multi_message.add(p_associated_column1 => 'PER_APPRAISALS.APPRAISAL_DATE'
1649 ,p_associated_column2 => 'PER_APPRAISALS.APPRAISAL_PERIOD_START_DATE'
1650 );
1651 --hr_utility.raise_error;
1652 end if;
1653
1654 hr_utility.set_location(l_proc,60);
1655
1656 -- Fix 3061901
1657
1658 if ( nvl(p_next_appraisal_date,hr_api.g_eot ) < p_appraisal_date) then
1659 hr_utility.set_message(800, 'HR_449014_INV_NEXT_APPR_DATE');
1660 hr_multi_message.add(p_associated_column1 => 'PER_APPRAISALS.NEXT_APPRAISAL_DATE'
1661 ,p_associated_column2 => 'PER_APPRAISALS.APPRAISAL_DATE'
1662 );
1663 --hr_utility.raise_error;
1664
1665 end if;
1666
1667 hr_utility.set_location('Leaving: '||l_proc,70);
1668
1669
1670 end chk_appraisal_dates;
1671
1672
1673 ------------------------------------------------------------------------------+
1674 -------------------------------<chk_overall_rating>---------------------------+
1675 ------------------------------------------------------------------------------+
1676
1677 -- Description:
1678 -- - Validates that the OVERALL_PERFORMANCE_LEVEL_ID is in the same business group as
1679 -- the appraisal and the same scale as appraisal template.
1680
1681 -- Pre_conditions:
1682 -- Valid appraisal_template_id
1683
1684 -- In Arguments:
1685 -- p_appraisal_id
1686 -- p_overall_performance_level_id
1687 -- p_appraisal_template_id
1688 -- p_object_version_number
1689 -- p_business_group_id
1690
1691 -- Post Success:
1692 -- Process continues if :
1693 -- All the in parameters are valid.
1694
1695 -- Post Failure:
1696 -- An application error is raised and processing is terminated if any of
1697 -- the following cases are found :
1698 -- - overall_performance_level_id is not in the same business group as
1699 -- the appraisal and the same scale as appraisal template.
1700
1701 -- Access Status
1702 -- Internal Table Handler Use Only.
1703
1704 procedure chk_overall_rating
1705 (p_appraisal_id in per_appraisals.appraisal_id%TYPE
1706 ,p_object_version_number in per_appraisals.object_version_number%TYPE
1707 ,p_appraisal_template_id in per_appraisals.appraisal_template_id%TYPE
1708 ,p_overall_performance_level_id in per_appraisals.overall_performance_level_id%TYPE
1709 ,p_business_group_id in per_appraisals.business_group_id%TYPE
1710 )
1711 is
1712
1713 l_exists varchar2(1);
1714 l_business_group_id per_appraisals.business_group_id%TYPE;
1715 l_rating_scale_id per_rating_levels.rating_scale_id%TYPE;
1716 l_api_updating boolean;
1717 l_proc varchar2(72) := g_package||'chk_overall_rating';
1718
1719
1720 -- Cursor to check if overall rating id exists and is
1721 -- in the same business group as appraisal
1722
1723 Cursor csr_overall_rating_bg
1724 is
1725 select business_group_id,rating_scale_id
1726 from per_rating_levels
1727 where rating_level_id = p_overall_performance_level_id;
1728
1729
1730 -- Cursor to check if overall rating id is for the ratibng scale
1731 -- defined in the appraisal template
1732
1733 Cursor csr_overall_rating_valid
1734 is
1735 select 'Y'
1736 from per_appraisal_templates
1737 where rating_scale_id = l_rating_scale_id
1738 and appraisal_template_id = p_appraisal_template_id;
1739
1740 begin
1741 hr_utility.set_location('Entering:'|| l_proc, 1);
1742
1743 -- Check mandatory parameters have been set
1744
1745 hr_api.mandatory_arg_error
1746 (p_api_name => l_proc
1747 ,p_argument => 'appraisal_template_id'
1748 ,p_argument_value => p_appraisal_template_id
1749 );
1750
1751 -- Only proceed with validation if :
1752 -- a) The current g_old_rec is current and
1753 -- b) The value for overall_performance_level_id has changed
1754
1755 l_api_updating := per_apr_shd.api_updating
1756 (p_appraisal_id => p_appraisal_id
1757 ,p_object_version_number => p_object_version_number);
1758
1759 if ( (l_api_updating and nvl(per_apr_shd.g_old_rec.overall_performance_level_id,
1760 hr_api.g_number)
1761 <> nvl(p_overall_performance_level_id,hr_api.g_number)
1762 ) or
1763 (NOT l_api_updating)
1764 ) then
1765 -- Do the check
1766 hr_utility.set_location(l_proc, 2);
1767 if p_overall_performance_level_id is not null then
1768 open csr_overall_rating_bg;
1769 fetch csr_overall_rating_bg into l_business_group_id , l_rating_scale_id;
1770 if csr_overall_rating_bg%notfound then
1771 close csr_overall_rating_bg;
1772 hr_utility.set_message(801,'HR_51898_APR_NO_SUCH_LEVEL');
1773 hr_utility.raise_error;
1774 end if;
1775 close csr_overall_rating_bg;
1776 -- check if the business groups match
1777 hr_utility.set_location(l_proc, 3);
1778 -- ngundura changes as per pa requirement
1779 -- added l_business_group_id is not null criteria to facilitate global rating scales
1780 if p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1781 hr_utility.set_message(801,'HR_51899_APR_LVL_DIFF_BG');
1782 hr_utility.raise_error;
1783 end if;
1784 -- check if the rating level is for the rating scale
1785 -- defined in the appraisal template
1786 hr_utility.set_location(l_proc, 4);
1787 open csr_overall_rating_valid;
1788 fetch csr_overall_rating_valid into l_exists;
1789 if csr_overall_rating_valid%notfound then
1790 close csr_overall_rating_valid;
1791 hr_utility.set_message(801,'HR_51900_APR_LVL_DIFF_SCALE');
1792 hr_utility.raise_error;
1793 end if;
1794 close csr_overall_rating_valid;
1795 end if;
1796 end if;
1797 hr_utility.set_location('Leaving: '|| l_proc, 10);
1798
1799 EXCEPTION
1800 when app_exception.application_exception then
1801 if hr_multi_message.exception_add
1802 (p_associated_column1 => 'PER_APPRAISALS.OVERALL_PERFORMANCE_LEVEL_ID'
1803 ) then
1804 raise;
1805 end if;
1806
1807 end chk_overall_rating;
1808
1809 ------------------------------------------------------------------------------+
1810 ------------------------------<chk_appraisal_delete>--------------------------+
1811 ------------------------------------------------------------------------------+
1812
1813 -- Description:
1814 -- - Validates that an appraisal cannot be deleted if:
1815 -- -Appraisal overall_performance_level_id is not null
1816 -- -Appraisal is referenced in:
1817 -- - per_assign_proposal_answers
1818 -- - per_objectives
1819 -- - per_assessments
1820 -- - per_performance_ratings
1821
1822 -- Pre_conditions:
1823 -- - A valid appraisal_id
1824
1825 -- In Arguments:
1826 -- p_appraisal_id
1827 -- p_object_version_number
1828
1829 -- Post Success:
1830 -- Process continues if :
1831 -- All the in parameters are valid.
1832
1833 -- Post Failure:
1834 -- An application error is raised and processing is terminated if any of
1835 -- the following cases are found :
1836 -- - appraisal overall_performance_level_id is not null
1837 -- - appraisal is refrenced in per_assign_proposal_answers, per_objectives
1838 -- per_assessments, per_performance_ratings
1839
1840 -- Access Status
1841 -- Internal Table Handler Use Only.
1842
1843 procedure chk_appraisal_delete
1844 (p_appraisal_id in per_appraisals.appraisal_id%TYPE
1845 ,p_object_version_number in per_appraisals.object_version_number%TYPE
1846 ,p_overall_performance_level_id in per_appraisals.overall_performance_level_id%TYPE
1847 )
1848 is
1849
1850 l_exists varchar2(1);
1851 l_proc varchar2(72) := g_package||'chk_appraisal_delete';
1852
1853
1854 -- Cursor to check if the appraisal is used in per_assign_proposal_answers
1855
1856 -- Change to use exists for WWBUG 1833930.
1857
1858 cursor csr_apr_exists_in_answers
1859 is
1860 select 'Y'
1861 from dual
1862 where exists (select null
1863 from per_assign_proposal_answers
1864 where answer_for_key = p_appraisal_id);
1865 -- 07/03/97 Changed
1866
1867 -- Cursor to check if the appraisal is used in per_objectives
1868
1869 cursor csr_apr_exits_in_objectives
1870 is
1871 select 'Y'
1872 from per_objectives
1873 where appraisal_id = p_appraisal_id;
1874
1875 -- Cursor to check if the appraisal is used in per_assessments
1876
1877 cursor csr_apr_exists_in_assessments
1878 is
1879 select 'Y'
1880 from per_assessments
1881 where appraisal_id = p_appraisal_id;
1882
1883 -- Cursor to check if the appraisal is used in per_performance_ratings
1884
1885 cursor csr_apr_exits_in_perf_rat
1886 is
1887 select 'Y'
1888 from per_performance_ratings
1889 where appraisal_id = p_appraisal_id;
1890
1891
1892 -- Cursor to check if the appraisal is used in PER_PARTICIPANTS
1893
1894 cursor csr_apr_exists_in_per_part is
1895 select 'Y'
1896 from per_participants par
1897 where par.participation_in_id = p_appraisal_id
1898 and par.participation_in_table = 'PER_APPRAISALS'
1899 and par.participation_in_column = 'APPRAISAL_ID';
1900
1901 -- Cursor to check if the appraisal is used in HR_QUEST_ANSWERS
1902
1903 cursor csr_apr_exists_in_hr_qsa is
1904 select 'Y'
1905 from hr_quest_answers qsa
1906 where qsa.type_object_id = p_appraisal_id
1907 and qsa.type = 'APPRAISAL';
1908
1909 begin
1910 hr_utility.set_location('Entering:'|| l_proc, 1);
1911
1912 -- Check mandatory parameters have been set
1913
1914 hr_api.mandatory_arg_error
1915 (p_api_name => l_proc
1916 ,p_argument => 'appraisal_id'
1917 ,p_argument_value => p_appraisal_id
1918 );
1919
1920 hr_utility.set_location(l_proc, 2);
1921
1922 -- check if overall_performance_level_id is null. If it is not null then
1923 -- do not allow the appraisal to be deleted.
1924
1925 if p_overall_performance_level_id is not null then
1926 hr_utility.set_message(801,'HR_51902_APR_LVL_NOT_NULL');
1927 hr_utility.raise_error;
1928 end if;
1929
1930 open csr_apr_exists_in_answers;
1931 fetch csr_apr_exists_in_answers into l_exists;
1932 if csr_apr_exists_in_answers%found then
1933 close csr_apr_exists_in_answers;
1934 hr_utility.set_message(801,'HR_51903_APR_IN_ANSWERS');
1935 hr_utility.raise_error;
1936 end if;
1937 close csr_apr_exists_in_answers;
1938
1939 hr_utility.set_location(l_proc, 3);
1940
1941 open csr_apr_exits_in_objectives;
1942 fetch csr_apr_exits_in_objectives into l_exists;
1943 if csr_apr_exits_in_objectives%found then
1944 close csr_apr_exits_in_objectives;
1945 hr_utility.set_message(801,'HR_51904_APR_IN_OBJECT');
1946 hr_utility.raise_error;
1947 end if;
1948 close csr_apr_exits_in_objectives;
1949
1950 hr_utility.set_location(l_proc, 4);
1951
1952 open csr_apr_exists_in_assessments;
1953 fetch csr_apr_exists_in_assessments into l_exists;
1954 if csr_apr_exists_in_assessments%found then
1955 close csr_apr_exists_in_assessments;
1956 hr_utility.set_message(801,'HR_51905_APR_IN_ASSMNT');
1957 hr_utility.raise_error;
1958 end if;
1959 close csr_apr_exists_in_assessments;
1960
1961 hr_utility.set_location(l_proc, 5);
1962
1963 open csr_apr_exits_in_perf_rat;
1964 fetch csr_apr_exits_in_perf_rat into l_exists;
1965 if csr_apr_exits_in_perf_rat%found then
1966 close csr_apr_exits_in_perf_rat;
1967 hr_utility.set_message(801,'HR_51906_APR_IN_PERF_RAT');
1968 hr_utility.raise_error;
1969 end if;
1970 close csr_apr_exits_in_perf_rat;
1971
1972 hr_utility.set_location(l_proc, 6);
1973
1974 open csr_apr_exists_in_per_part;
1975 fetch csr_apr_exists_in_per_part into l_exists;
1976 if csr_apr_exists_in_per_part%found then
1977 close csr_apr_exists_in_per_part;
1978 fnd_message.set_name('PER','PER_52450_APR_ANSWERS_EXIST');
1979 fnd_message.raise_error;
1980 end if;
1981 close csr_apr_exists_in_per_part;
1982
1983 hr_utility.set_location(l_proc,7);
1984
1985 open csr_apr_exists_in_hr_qsa;
1986 fetch csr_apr_exists_in_hr_qsa into l_exists;
1987 if csr_apr_exists_in_hr_qsa%found then
1988 close csr_apr_exists_in_hr_qsa;
1989 fnd_message.set_name('PER','PER_52451_APR_PARTICIP_EXIST');
1990 fnd_message.raise_error;
1991 end if;
1992 close csr_apr_exists_in_hr_qsa;
1993
1994 hr_utility.set_location('Leaving: '|| l_proc, 10);
1995 end chk_appraisal_delete;
1996
1997 --
1998 -- ----------------------------------------------------------------------------
1999 -- |-----------------------< chk_plan_id >------------------------------------|
2000 -- ----------------------------------------------------------------------------
2001 -- {Start Of Comments}
2002 --
2003 -- Description:
2004 -- This procedure is used to validate that the specified performance
2005 -- management plan exists.
2006 --
2007 -- Pre Conditions:
2008 -- The plan must already exist.
2009 --
2010 -- In Arguments:
2011 --
2012 --
2013 -- Post Success:
2014 -- Processing continues if the plan is valid.
2015 --
2016 -- Post Failure:
2017 -- An application error is raised if the plan does not exist.
2018 --
2019 -- {End Of Comments}
2020 -- ----------------------------------------------------------------------------
2021 Procedure chk_plan_id
2022 (p_appraisal_id IN number
2023 ,p_object_version_number IN number
2024 ,p_plan_id IN number
2025 ) IS
2026
2027 --
2028 l_proc varchar2(72) := g_package || 'chk_plan_id';
2029 l_api_updating boolean;
2030 l_plan_id number;
2031 --
2032
2033 CURSOR csr_chk_plan_id IS
2034 SELECT pmp.plan_id
2035 FROM per_perf_mgmt_plans pmp
2036 WHERE pmp.plan_id = p_plan_id;
2037 --
2038 BEGIN
2039
2040 hr_utility.set_location('Entering:'|| l_proc, 10);
2041
2042 --
2043 -- Only proceed with validation if :
2044 -- a) The current g_old_rec is current and
2045 -- b) The date values have changed
2046 --
2047 l_api_updating := per_apr_shd.api_updating
2048 (p_appraisal_id => p_appraisal_id
2049 ,p_object_version_number => p_object_version_number);
2050 --
2051 IF (l_api_updating
2052 AND nvl(per_apr_shd.g_old_rec.plan_id, hr_api.g_number)
2053 = nvl(p_plan_id, hr_api.g_number))
2054 THEN
2055 RETURN;
2056 END IF;
2057
2058 IF p_plan_id IS NOT null THEN
2059 --
2060 -- Check that plan exists.
2061 --
2062 hr_utility.set_location(l_proc, 20);
2063 OPEN csr_chk_plan_id;
2064 FETCH csr_chk_plan_id INTO l_plan_id;
2065 CLOSE csr_chk_plan_id;
2066
2067 IF l_plan_id IS null THEN
2068 fnd_message.set_name('PER', 'HR_50264_PMS_INVALID_PLAN');
2069 fnd_message.raise_error;
2070 END IF;
2071
2072 END IF;
2073
2074 hr_utility.set_location('Leaving:'|| l_proc, 970);
2075
2076 EXCEPTION
2077
2078 WHEN app_exception.application_exception THEN
2079 IF hr_multi_message.exception_add
2080 (p_associated_column1 => 'PER_APPRAISALS.PLAN_ID')
2081 THEN
2082 hr_utility.set_location(' Leaving:'|| l_proc, 980);
2083 RAISE;
2084 END IF;
2085 hr_utility.set_location(' Leaving:'|| l_proc, 990);
2086
2087 END chk_plan_id;
2088 --
2089 -- ----------------------------------------------------------------------------
2090 -- |--------------------------< chk_offline_status >--------------------------|
2091 -- ----------------------------------------------------------------------------
2092 -- {Start Of Comments}
2093 --
2094 -- Description:
2095 -- This procedure is used to validate that the offline status value
2096 --
2097 -- Pre Conditions:
2098 -- None
2099 --
2100 -- In Arguments:
2101 --
2102 --
2103 -- Post Success:
2104 -- Processing continues if the offline status value is valid.
2105 --
2106 -- Post Failure:
2107 -- An application error is raised if the offline status value is invalid.
2108 --
2109 -- {End Of Comments}
2110 -- ----------------------------------------------------------------------------
2111 Procedure chk_offline_status
2112 (p_appraisal_id IN number
2113 ,p_object_version_number IN number
2114 ,p_offline_status IN varchar2
2115 ,p_effective_date IN date
2116 ) IS
2117
2118
2119 --
2120 l_proc varchar2(72) := g_package || 'chk_offline_status';
2121 l_api_updating boolean;
2122 l_offline_status varchar2(30);
2123 --
2124 --
2125 BEGIN
2126
2127
2128 hr_utility.set_location('Entering:'|| l_proc, 10);
2129
2130
2131 --
2132 -- Only proceed with validation if :
2133 -- a) The current g_old_rec is current and
2134 -- b) The offline status value has changed
2135 --
2136 l_api_updating := per_apr_shd.api_updating
2137 (p_appraisal_id => p_appraisal_id
2138 ,p_object_version_number => p_object_version_number);
2139 --
2140 IF (l_api_updating
2141 AND nvl(per_apr_shd.g_old_rec.offline_status, hr_api.g_varchar2)
2142 = nvl(p_offline_status, hr_api.g_varchar2))
2143 THEN
2144 RETURN;
2145 END IF;
2146
2147 IF p_offline_status IS NOT null THEN
2148 --
2149 -- Check that offline status is valid.
2150 --
2151 hr_utility.set_location(l_proc, 20);
2152 IF hr_api.not_exists_in_hr_lookups(
2153 p_effective_date => p_effective_date
2154 ,p_lookup_type => 'APPRAISAL_OFFLINE_STATUS'
2155 ,p_lookup_code => upper(p_offline_status)
2156 ) THEN
2157 fnd_message.set_name('PER', 'HR_34568_INV_OFFLINE_STATUS');
2158 fnd_message.raise_error;
2159 END IF;
2160
2161 END IF;
2162
2163 hr_utility.set_location('Leaving:'|| l_proc, 970);
2164 EXCEPTION
2165
2166 WHEN app_exception.application_exception THEN
2167 IF hr_multi_message.exception_add
2168 (p_associated_column1 => 'PER_APPRAISALS.OFFLINE_STATUS')
2169 THEN
2170 hr_utility.set_location(' Leaving:'|| l_proc, 980);
2171 RAISE;
2172 END IF;
2173 hr_utility.set_location(' Leaving:'|| l_proc, 990);
2174
2175 END chk_offline_status;
2176 --
2177 -- ----------------------------------------------------------------------+
2178 -- |------------------------------< chk_df >-----------------------------|
2179 -- ----------------------------------------------------------------------+
2180
2181 -- Description:
2182 -- Validates the all Descriptive Flexfield values.
2183
2184 -- Pre-conditions:
2185 -- All other columns have been validated. Must be called as the
2186 -- last step from insert_validate and update_validate.
2187
2188 -- In Arguments:
2189 -- p_rec
2190
2191 -- Post Success:
2192 -- If the Descriptive Flexfield structure column and data values are
2193 -- all valid this procedure will end normally and processing will
2194 -- continue.
2195
2196 -- Post Failure:
2197 -- If the Descriptive Flexfield structure column value or any of
2198 -- the data values are invalid then an application error is raised as
2199 -- a PL/SQL exception.
2200
2201 -- Access Status:
2202 -- Internal Row Handler Use Only.
2203
2204 procedure chk_df
2205 (p_rec in per_apr_shd.g_rec_type) is
2206
2207 l_proc varchar2(72) := g_package||'chk_df';
2208
2209 begin
2210 hr_utility.set_location('Entering:'||l_proc, 10);
2211
2212 if ((p_rec.appraisal_id is not null) and (
2213 nvl(per_apr_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
2214 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
2215 nvl(per_apr_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
2216 nvl(p_rec.attribute1, hr_api.g_varchar2) or
2217 nvl(per_apr_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
2218 nvl(p_rec.attribute2, hr_api.g_varchar2) or
2219 nvl(per_apr_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
2220 nvl(p_rec.attribute3, hr_api.g_varchar2) or
2221 nvl(per_apr_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
2222 nvl(p_rec.attribute4, hr_api.g_varchar2) or
2223 nvl(per_apr_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
2224 nvl(p_rec.attribute5, hr_api.g_varchar2) or
2225 nvl(per_apr_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
2226 nvl(p_rec.attribute6, hr_api.g_varchar2) or
2227 nvl(per_apr_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
2228 nvl(p_rec.attribute7, hr_api.g_varchar2) or
2229 nvl(per_apr_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
2230 nvl(p_rec.attribute8, hr_api.g_varchar2) or
2231 nvl(per_apr_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
2232 nvl(p_rec.attribute9, hr_api.g_varchar2) or
2233 nvl(per_apr_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
2234 nvl(p_rec.attribute10, hr_api.g_varchar2) or
2235 nvl(per_apr_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
2236 nvl(p_rec.attribute11, hr_api.g_varchar2) or
2237 nvl(per_apr_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
2238 nvl(p_rec.attribute12, hr_api.g_varchar2) or
2239 nvl(per_apr_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
2240 nvl(p_rec.attribute13, hr_api.g_varchar2) or
2241 nvl(per_apr_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
2242 nvl(p_rec.attribute14, hr_api.g_varchar2) or
2243 nvl(per_apr_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
2244 nvl(p_rec.attribute15, hr_api.g_varchar2) or
2245 nvl(per_apr_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
2246 nvl(p_rec.attribute16, hr_api.g_varchar2) or
2247 nvl(per_apr_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
2248 nvl(p_rec.attribute17, hr_api.g_varchar2) or
2249 nvl(per_apr_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
2250 nvl(p_rec.attribute18, hr_api.g_varchar2) or
2251 nvl(per_apr_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
2252 nvl(p_rec.attribute19, hr_api.g_varchar2) or
2253 nvl(per_apr_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
2254 nvl(p_rec.attribute20, hr_api.g_varchar2)))
2255 or
2256 (p_rec.appraisal_id is null) then
2257
2258 -- Only execute the validation if absolutely necessary:
2259 -- a) During update, the structure column value or any
2260 -- of the attribute values have actually changed.
2261 -- b) During insert.
2262
2263 hr_dflex_utility.ins_or_upd_descflex_attribs
2264 (p_appl_short_name => 'PER'
2265 ,p_descflex_name => 'PER_APPRAISALS'
2266 ,p_attribute_category => p_rec.attribute_category
2267 ,p_attribute1_name => 'ATTRIBUTE1'
2268 ,p_attribute1_value => p_rec.attribute1
2269 ,p_attribute2_name => 'ATTRIBUTE2'
2270 ,p_attribute2_value => p_rec.attribute2
2271 ,p_attribute3_name => 'ATTRIBUTE3'
2272 ,p_attribute3_value => p_rec.attribute3
2273 ,p_attribute4_name => 'ATTRIBUTE4'
2274 ,p_attribute4_value => p_rec.attribute4
2275 ,p_attribute5_name => 'ATTRIBUTE5'
2276 ,p_attribute5_value => p_rec.attribute5
2277 ,p_attribute6_name => 'ATTRIBUTE6'
2278 ,p_attribute6_value => p_rec.attribute6
2279 ,p_attribute7_name => 'ATTRIBUTE7'
2280 ,p_attribute7_value => p_rec.attribute7
2281 ,p_attribute8_name => 'ATTRIBUTE8'
2282 ,p_attribute8_value => p_rec.attribute8
2283 ,p_attribute9_name => 'ATTRIBUTE9'
2284 ,p_attribute9_value => p_rec.attribute9
2285 ,p_attribute10_name => 'ATTRIBUTE10'
2286 ,p_attribute10_value => p_rec.attribute10
2287 ,p_attribute11_name => 'ATTRIBUTE11'
2288 ,p_attribute11_value => p_rec.attribute11
2289 ,p_attribute12_name => 'ATTRIBUTE12'
2290 ,p_attribute12_value => p_rec.attribute12
2291 ,p_attribute13_name => 'ATTRIBUTE13'
2292 ,p_attribute13_value => p_rec.attribute13
2293 ,p_attribute14_name => 'ATTRIBUTE14'
2294 ,p_attribute14_value => p_rec.attribute14
2295 ,p_attribute15_name => 'ATTRIBUTE15'
2296 ,p_attribute15_value => p_rec.attribute15
2297 ,p_attribute16_name => 'ATTRIBUTE16'
2298 ,p_attribute16_value => p_rec.attribute16
2299 ,p_attribute17_name => 'ATTRIBUTE17'
2300 ,p_attribute17_value => p_rec.attribute17
2301 ,p_attribute18_name => 'ATTRIBUTE18'
2302 ,p_attribute18_value => p_rec.attribute18
2303 ,p_attribute19_name => 'ATTRIBUTE19'
2304 ,p_attribute19_value => p_rec.attribute19
2305 ,p_attribute20_name => 'ATTRIBUTE20'
2306 ,p_attribute20_value => p_rec.attribute20
2307 );
2308 end if;
2309
2310 hr_utility.set_location(' Leaving:'||l_proc, 20);
2311
2312 end chk_df;
2313
2314 -- ---------------------------------------------------------------------------+
2315 -- |---------------------------< insert_validate >----------------------------|
2316 -- ---------------------------------------------------------------------------+
2317 Procedure insert_validate(p_rec in per_apr_shd.g_rec_type
2318 ,p_effective_date in date)
2319 is
2320
2321 l_proc varchar2(72) := g_package||'insert_validate';
2322
2323 Begin
2324 hr_utility.set_location('Entering:'||l_proc, 5);
2325
2326 -- Call all supporting business operations
2327
2328 hr_api.validate_bus_grp_id
2329 (p_business_group_id => p_rec.business_group_id
2330 ,p_associated_column1 => per_per_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
2331 ); -- VALIDATE BUSINESS GROUP
2332
2333
2334 hr_utility.set_location(l_proc, 6);
2335
2336 per_apr_bus.chk_appraisee_appraiser
2337 (p_person_id => p_rec.appraisee_person_id
2338 ,p_business_group_id => p_rec.business_group_id
2339 ,p_effective_date => p_effective_date
2340 ,p_person_type => 'APPRAISEE'
2341 );
2342
2343 hr_utility.set_location(l_proc, 7);
2344
2345 per_apr_bus.chk_appraisee_appraiser
2346 (p_person_id => p_rec.appraiser_person_id
2347 ,p_business_group_id => p_rec.business_group_id
2348 ,p_effective_date => p_effective_date
2349 ,p_person_type => 'APPRAISER'
2350 );
2351
2352 per_apr_bus.chk_main_appraiser_id
2353 (p_main_appraiser_id => p_rec.main_appraiser_id
2354 ,p_business_group_id => p_rec.business_group_id
2355 ,p_effective_date => p_effective_date
2356 );
2357
2358 hr_utility.set_location(l_proc, 8);
2359
2360 per_apr_bus.chk_appraisal_period_dates
2361 (p_appraisal_id => p_rec.appraisal_id
2362 ,p_object_version_number => p_rec.object_version_number
2363 ,p_appraisal_period_start_date => p_rec.appraisal_period_start_date
2364 ,p_appraisal_period_end_date => p_rec.appraisal_period_end_date
2365 );
2366
2367 hr_utility.set_location(l_proc, 9);
2368 per_apr_bus.chk_appraisal_dates
2369 (p_appraisal_date => p_rec.appraisal_date
2370 ,p_appraisal_period_start_date => p_rec.appraisal_period_start_date
2371 ,p_appraisal_period_end_date => p_rec.appraisal_period_end_date
2372 ,p_next_appraisal_date => p_rec.next_appraisal_date
2373 ,p_appraisee_person_id => p_rec.appraisee_person_id
2374 ,p_appraiser_person_id => p_rec.appraiser_person_id
2375 ,p_main_appraiser_id => p_rec.main_appraiser_id
2376 );
2377 hr_utility.set_location(l_proc, 10);
2378
2379 per_apr_bus.chk_appraisal_template
2380 (p_appraisal_template_id => p_rec.appraisal_template_id
2381 ,p_business_group_id => p_rec.business_group_id
2382 ,p_effective_date => p_rec.appraisal_period_start_date
2383 );
2384
2385 -- Fix 3061985.
2386 -- Template validation should be done on appraisal start date and appraisal end date.
2387
2388
2389 per_apr_bus.chk_appraisal_template
2390 (p_appraisal_template_id => p_rec.appraisal_template_id
2391 ,p_business_group_id => p_rec.business_group_id
2392 ,p_effective_date => p_rec.appraisal_period_end_date
2393 );
2394
2395
2396 hr_utility.set_location(l_proc, 11);
2397
2398 per_apr_bus.chk_appraisal_type
2399 (p_appraisal_id => p_rec.appraisal_id
2400 ,p_object_version_number => p_rec.object_version_number
2401 ,p_type => p_rec.type
2402 ,p_effective_date => p_effective_date
2403 );
2404
2405 hr_utility.set_location(l_proc, 12);
2406
2407 per_apr_bus.chk_overall_rating
2408 (p_appraisal_id => p_rec.appraisal_id
2409 ,p_object_version_number => p_rec.object_version_number
2410 ,p_appraisal_template_id => p_rec.appraisal_template_id
2411 ,p_overall_performance_level_id => p_rec.overall_performance_level_id
2412 ,p_business_group_id => p_rec.business_group_id
2413 );
2414
2415 hr_utility.set_location(l_proc, 13);
2416
2417 per_apr_bus.chk_appraisal_status
2418 (p_appraisal_id => p_rec.appraisal_id
2419 ,p_object_version_number => p_rec.object_version_number
2420 ,p_status => p_rec.status
2421 ,p_effective_date => p_effective_date
2422 );
2423
2424 hr_utility.set_location(l_proc, 14);
2425
2426
2427 per_apr_bus.chk_group_date_id
2428 (p_group_initiator_id => p_rec.group_initiator_id
2429 ,p_group_date => p_rec.group_date
2430 );
2431
2432 per_apr_bus.chk_group_initiator_id
2433 (p_group_initiator_id => p_rec.group_initiator_id
2434 ,p_business_group_id => p_rec.business_group_id
2435 ,p_group_date => p_rec.group_date
2436 );
2437
2438 per_apr_bus.chk_plan_id
2439 (p_appraisal_id => p_rec.appraisal_id
2440 ,p_object_version_number => p_rec.object_version_number
2441 ,p_plan_id => p_rec.plan_id
2442 );
2443 per_apr_bus.chk_offline_status
2444 (p_appraisal_id => p_rec.appraisal_id
2445 ,p_object_version_number => p_rec.object_version_number
2446 ,p_offline_status => p_rec.offline_status
2447 ,p_effective_date => p_effective_date
2448 );
2449
2450 -- Call descriptive flexfield validation routines
2451
2452 per_apr_bus.chk_df(p_rec => p_rec);
2453
2454 hr_utility.set_location(' Leaving:'||l_proc, 10);
2455 End insert_validate;
2456
2457 -- ---------------------------------------------------------------------------+
2458 -- |---------------------------< update_validate >----------------------------|
2459 -- ---------------------------------------------------------------------------+
2460 Procedure update_validate(p_rec in per_apr_shd.g_rec_type
2461 ,p_effective_date in date) is
2462
2463 l_proc varchar2(72) := g_package||'update_validate';
2464
2465 Begin
2466 hr_utility.set_location('Entering:'||l_proc, 5);
2467
2468 -- Call all supporting business operations
2469
2470 -- Rule Check non-updateable fields cannot be updated
2471
2472 hr_api.validate_bus_grp_id
2473 (p_business_group_id => p_rec.business_group_id
2474 ,p_associated_column1 => per_per_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
2475 ); -- VALIDATE BUSINESS GROUP
2476
2477 chk_non_updateable_args(p_rec => p_rec);
2478
2479 hr_utility.set_location(l_proc,6);
2480
2481 per_apr_bus.chk_open(p_open => p_rec.open
2482 ,p_effective_date => p_effective_date
2483 );
2484
2485 hr_utility.set_location(l_proc,7);
2486
2487 per_apr_bus.chk_update_allowed
2488 (p_rec => p_rec);
2489
2490
2491 hr_utility.set_location(l_proc, 8);
2492 -- Appraisee is non updatable argument. So, no need for that check during update.
2493
2494 per_apr_bus.chk_appraisee_appraiser
2495 (p_person_id => p_rec.appraiser_person_id
2496 ,p_business_group_id => p_rec.business_group_id
2497 ,p_effective_date => p_effective_date
2498 ,p_person_type => 'APPRAISER'
2499 );
2500
2501
2502 /*
2503 per_apr_bus.chk_appraiser_person_id
2504 (p_appraisal_id => p_rec.appraisal_id);
2505 -- Call to current behavior of chk_appraiser_person_id is not required..
2506 */
2507 hr_utility.set_location(l_proc,9);
2508
2509 per_apr_bus.chk_appraisal_period_dates
2510 (p_appraisal_id => p_rec.appraisal_id
2511 ,p_object_version_number => p_rec.object_version_number
2512 ,p_appraisal_period_start_date => p_rec.appraisal_period_start_date
2513 ,p_appraisal_period_end_date => p_rec.appraisal_period_end_date
2514 );
2515
2516 hr_utility.set_location(l_proc, 9);
2517
2518 if (p_rec.appraisal_system_status <> 'DELETED') then
2519 per_apr_bus.chk_appraisal_dates
2520 (p_appraisal_date => p_rec.appraisal_date
2521 ,p_appraisal_period_start_date => p_rec.appraisal_period_start_date
2522 ,p_appraisal_period_end_date => p_rec.appraisal_period_end_date
2523 ,p_next_appraisal_date => p_rec.next_appraisal_date
2524 ,p_appraisee_person_id => p_rec.appraisee_person_id
2525 ,p_appraiser_person_id => p_rec.appraiser_person_id
2526 ,p_main_appraiser_id => p_rec.main_appraiser_id
2527 );
2528 end if;
2529
2530 per_apr_bus.chk_appraisal_type
2531 (p_appraisal_id => p_rec.appraisal_id
2532 ,p_object_version_number => p_rec.object_version_number
2533 ,p_type => p_rec.type
2534 ,p_effective_date => p_effective_date
2535 );
2536
2537 hr_utility.set_location(l_proc, 10);
2538
2539 per_apr_bus.chk_appraisal_template
2540 (p_appraisal_template_id => p_rec.appraisal_template_id
2541 ,p_business_group_id => p_rec.business_group_id
2542 ,p_effective_date => p_rec.appraisal_period_start_date
2543 );
2544
2545 -- Fix 3061985.
2546 -- Template validation should be done on appraisal start date and appraisal end date.
2547
2548
2549 per_apr_bus.chk_appraisal_template
2550 (p_appraisal_template_id => p_rec.appraisal_template_id
2551 ,p_business_group_id => p_rec.business_group_id
2552 ,p_effective_date => p_rec.appraisal_period_end_date
2553 );
2554
2555 per_apr_bus.chk_overall_rating
2556 (p_appraisal_id => p_rec.appraisal_id
2557 ,p_object_version_number => p_rec.object_version_number
2558 ,p_appraisal_template_id => p_rec.appraisal_template_id
2559 ,p_overall_performance_level_id => p_rec.overall_performance_level_id
2560 ,p_business_group_id => p_rec.business_group_id
2561 );
2562
2563 hr_utility.set_location(l_proc, 13);
2564
2565 per_apr_bus.chk_appraisal_status
2566 (p_appraisal_id => p_rec.appraisal_id
2567 ,p_object_version_number => p_rec.object_version_number
2568 ,p_status => p_rec.status
2569 ,p_effective_date => p_effective_date
2570 );
2571
2572 hr_utility.set_location(l_proc, 14);
2573
2574 per_apr_bus.chk_main_appraiser_id
2575 (p_main_appraiser_id => p_rec.main_appraiser_id
2576 ,p_business_group_id => p_rec.business_group_id
2577 ,p_effective_date => p_effective_date
2578 );
2579
2580 hr_utility.set_location(l_proc, 15);
2581
2582 per_apr_bus.chk_plan_id
2583 (p_appraisal_id => p_rec.appraisal_id
2584 ,p_object_version_number => p_rec.object_version_number
2585 ,p_plan_id => p_rec.plan_id
2586 );
2587
2588 hr_utility.set_location(l_proc, 16);
2589 per_apr_bus.chk_offline_status
2590 (p_appraisal_id => p_rec.appraisal_id
2591 ,p_object_version_number => p_rec.object_version_number
2592 ,p_offline_status => p_rec.offline_status
2593 ,p_effective_date => p_effective_date
2594 );
2595
2596 -- Call descriptive flexfield validation routines
2597
2598 per_apr_bus.chk_df(p_rec => p_rec);
2599
2600 hr_utility.set_location(' Leaving:'||l_proc, 10);
2601 End update_validate;
2602
2603 -- ---------------------------------------------------------------------------+
2604 -- |---------------------------< delete_validate >----------------------------|
2605 -- ---------------------------------------------------------------------------+
2606 Procedure delete_validate(p_rec in per_apr_shd.g_rec_type) is
2607
2608 l_proc varchar2(72) := g_package||'delete_validate';
2609
2610 Begin
2611 hr_utility.set_location('Entering:'||l_proc, 5);
2612
2613 -- Call all supporting business operations
2614
2615 per_apr_bus.chk_appraisal_delete
2616 (p_appraisal_id => p_rec.appraisal_id
2617 ,p_object_version_number => p_rec.object_version_number
2618 ,p_overall_performance_level_id => p_rec.overall_performance_level_id
2619 ) ;
2620
2621 hr_utility.set_location(' Leaving:'||l_proc, 10);
2622
2623 End delete_validate;
2624
2625
2626 -- ---------------------------------------------------------------------------+
2627 -- |-----------------------< return_legislation_code >------------------------|
2628 -- ---------------------------------------------------------------------------+
2629 Function return_legislation_code
2630 ( p_appraisal_id in number
2631 ) return varchar2 is
2632
2633 -- Declare cursor
2634
2635 cursor csr_leg_code is
2636 select legislation_code
2637 from per_business_groups pbg,
2638 per_appraisals pap
2639 where pap.appraisal_id = p_appraisal_id
2640 and pbg.business_group_id = pap.business_group_id;
2641
2642 l_proc varchar2(72) := g_package||'return_legislation_code';
2643 l_legislation_code varchar2(150);
2644
2645 Begin
2646 hr_utility.set_location('Entering:'||l_proc, 5);
2647
2648 -- Ensure that all the mandatory parameters are not null
2649
2650 hr_api.mandatory_arg_error (p_api_name => l_proc,
2651 p_argument => 'appraisal_id',
2652 p_argument_value => p_appraisal_id );
2653
2654 if nvl(g_appraisal_id, hr_api.g_number) = p_appraisal_id then
2655
2656 -- The legislation has already been found with a previous
2657 -- call to this function. Just return the value in the global
2658 -- variable.
2659
2660 l_legislation_code := g_legislation_code;
2661 hr_utility.set_location(l_proc, 20);
2662 else
2663
2664 -- The ID is different to the last call to this function
2665 -- or this is the first call to this function.
2666
2667 open csr_leg_code;
2668 fetch csr_leg_code into l_legislation_code;
2669 if csr_leg_code%notfound then
2670 close csr_leg_code;
2671
2672 -- The primary key is invalid therefore we must error out
2673
2674 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2675 hr_utility.raise_error;
2676 end if;
2677
2678 close csr_leg_code;
2679 g_appraisal_id := p_appraisal_id;
2680 g_legislation_code := l_legislation_code;
2681 end if;
2682
2683 hr_utility.set_location(' Leaving:'||l_proc, 10);
2684
2685 return l_legislation_code;
2686 End return_legislation_code;
2687
2688
2689 end per_apr_bus;
[Home] [Help]