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