[Home] [Help]
PACKAGE BODY: APPS.PER_PAR_BUS
Source
1 Package Body per_par_bus as
2 /* $Header: peparrhi.pkb 120.1 2007/06/20 07:48:26 rapandi ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 l_status varchar2(50);
9 l_industry varchar2(50);
10 l_per_owner varchar2(30);
11 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
12 l_industry, l_per_owner);
13 g_package varchar2(33) := ' per_par_bus.'; -- Global package name
14 --
15 --
16 -- The following two global variables are only to be
17 -- used by the return_legislation_code function.
18 --
19 g_legislation_code varchar2(150) default null;
20 g_participant_id number default null;
21 --
22
23 --
24 -- ----------------------------------------------------------------------------
25 -- |----------------------< chk_non_updateable_args >-----------------------|
26 -- ----------------------------------------------------------------------------
27 --
28 Procedure chk_non_updateable_args(p_rec in per_par_shd.g_rec_type) is
29 --
30 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
31 l_error exception;
32 l_argument varchar2(30);
33 --
34 Begin
35 hr_utility.set_location('Entering:'||l_proc, 5);
36 --
37 -- Only proceed with validation if a row exists for
38 -- the current record in the HR Schema
39 --
40 if not per_par_shd.api_updating
41 (p_participant_id => p_rec.participant_id
42 ,p_object_version_number => p_rec.object_version_number
43 ) then
44 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
45 hr_utility.set_message_token('PROCEDURE', l_proc);
46 hr_utility.set_message_token('STEP', '5');
47 end if;
48 --
49 hr_utility.set_location(l_proc, 6);
50 --
51 if p_rec.business_group_id <> per_par_shd.g_old_rec.business_group_id then
52 l_argument := 'business_group_id';
53 raise l_error;
54 -- elsif p_rec.questionnaire_template_id <> per_par_shd.g_old_rec.questionnaire_template_id then
55 -- l_argument := 'questionnaire_template_id';
56 -- raise l_error;
57 elsif p_rec.participation_in_table <> per_par_shd.g_old_rec.participation_in_table then
58 hr_utility.set_location(l_proc, 7);
59 l_argument := 'participation_in_table';
60 raise l_error;
61 elsif p_rec.participation_in_column <> per_par_shd.g_old_rec.participation_in_column then
62 hr_utility.set_location(l_proc, 8);
63 l_argument := 'participation_in_column';
64 raise l_error;
65 elsif p_rec.participation_in_id <> per_par_shd.g_old_rec.participation_in_id then
66 hr_utility.set_location(l_proc, 9);
67 l_argument := 'participation_in_id';
68 raise l_error;
69 end if;
70 hr_utility.set_location(l_proc, 11);
71 --
72 exception
73 when l_error then
74 hr_api.argument_changed_error
75 (p_api_name => l_proc
76 ,p_argument => l_argument
77 ,p_base_table => per_par_shd.g_tab_nam);
78 when others then
79 raise;
80 hr_utility.set_location(' Leaving:'||l_proc, 12);
81 end chk_non_updateable_args;
82 --
83 -- ----------------------------------------------------------------------------
84 -- |------------------------< chk_update_allowed >----------------------------|
85 -- ----------------------------------------------------------------------------
86 --
87 -- Description:
88 -- Validates that a row can be updated by checking the status of the
89 -- participation_status column. A value of OPEN means the record can be
90 -- updated. Other values: "Pending Approval", "Closed" or "Rejected" mean
91 -- the record cannot be updated.
92 -- Participation status can be updated at any time.
93 --
94 -- Pre-requisites:
95 -- Participation_status is valid.
96 --
97 -- IN Parameters:
98 -- p_rec
99 --
100 -- Post Success:
101 -- Processing continues if the update is allowed.
102 --
103 -- Post Failure:
104 -- An application error is raised and processing is terminated if the
105 -- update is not allowed.
106 --
107 -- Developer/Implementation Notes:
108 -- None.
109 --
110 -- Access Status:
111 -- Internal Row Handler Development Only.
112 -- ----------------------------------------------------------------------------
113 procedure chk_update_allowed
114 (p_rec in per_par_shd.g_rec_type
115 )
116 is
117 --
118 l_proc varchar2(72) := g_package || 'chk_update_allowed';
119 --
120 begin
121 --
122 hr_utility.set_location('Entering: '||l_proc,10);
123 --
124 -- Check that the participation status is not closed.
125 --
126 -- if (p_rec.participation_status = 'CLOSED' or
127 -- Changes for V4 appraisals built.
128 if ( p_rec.participation_status = 'REJECTED') then
129 -- Check that the columns arent being updated
130 --
131 if p_rec.date_completed
132 <> per_par_shd.g_old_rec.date_completed then
133 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
134 fnd_message.raise_error;
135 elsif p_rec.participation_type
136 <> per_par_shd.g_old_rec.participation_type then
137 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
138 fnd_message.raise_error;
139 elsif p_rec.last_notified_date
140 <> per_par_shd.g_old_rec.last_notified_date then
141 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
142 fnd_message.raise_error;
143 elsif p_rec.comments
144 <> per_par_shd.g_old_rec.comments then
145 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
146 fnd_message.raise_error;
147 elsif p_rec.person_id
148 <> per_par_shd.g_old_rec.person_id then
149 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
150 fnd_message.raise_error;
151 elsif p_rec.attribute_category
152 <> per_par_shd.g_old_rec.attribute_category then
153 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
154 fnd_message.raise_error;
155 elsif p_rec.attribute1
156 <> per_par_shd.g_old_rec.attribute1 then
157 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
158 fnd_message.raise_error;
159 elsif p_rec.attribute2
160 <> per_par_shd.g_old_rec.attribute2 then
161 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
162 fnd_message.raise_error;
163 elsif p_rec.attribute3
164 <> per_par_shd.g_old_rec.attribute3 then
165 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
166 fnd_message.raise_error;
167 elsif p_rec.attribute4
168 <> per_par_shd.g_old_rec.attribute4 then
169 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
170 fnd_message.raise_error;
171 elsif p_rec.attribute5
172 <> per_par_shd.g_old_rec.attribute5 then
173 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
174 fnd_message.raise_error;
175 elsif p_rec.attribute6
176 <> per_par_shd.g_old_rec.attribute6 then
177 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
178 fnd_message.raise_error;
179 elsif p_rec.attribute7
180 <> per_par_shd.g_old_rec.attribute7 then
181 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
182 fnd_message.raise_error;
183 elsif p_rec.attribute8
184 <> per_par_shd.g_old_rec.attribute8 then
185 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
186 fnd_message.raise_error;
187 elsif p_rec.attribute9
188 <> per_par_shd.g_old_rec.attribute9 then
189 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
190 fnd_message.raise_error;
191 elsif p_rec.attribute10
192 <> per_par_shd.g_old_rec.attribute10 then
193 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
194 fnd_message.raise_error;
195 elsif p_rec.attribute11
196 <> per_par_shd.g_old_rec.attribute11 then
197 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
198 fnd_message.raise_error;
199 elsif p_rec.attribute12
200 <> per_par_shd.g_old_rec.attribute12 then
201 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
202 fnd_message.raise_error;
203 elsif p_rec.attribute13
204 <> per_par_shd.g_old_rec.attribute13 then
205 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
206 fnd_message.raise_error;
207 elsif p_rec.attribute14
208 <> per_par_shd.g_old_rec.attribute14 then
209 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
210 fnd_message.raise_error;
211 elsif p_rec.attribute15
212 <> per_par_shd.g_old_rec.attribute15 then
213 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
214 fnd_message.raise_error;
215 elsif p_rec.attribute16
216 <> per_par_shd.g_old_rec.attribute16 then
217 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
218 fnd_message.raise_error;
219 elsif p_rec.attribute17
220 <> per_par_shd.g_old_rec.attribute17 then
221 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
222 fnd_message.raise_error;
223 elsif p_rec.attribute18
224 <> per_par_shd.g_old_rec.attribute18 then
225 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
226 fnd_message.raise_error;
227 elsif p_rec.attribute19
228 <> per_par_shd.g_old_rec.attribute19 then
229 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
230 fnd_message.raise_error;
231 elsif p_rec.attribute20
232 <> per_par_shd.g_old_rec.attribute20 then
233 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
234 fnd_message.raise_error;
235 end if;
236 end if;
237 --
238 hr_utility.set_location('Leaving: '||l_proc,20);
239 end chk_update_allowed;
240 --
241 -- ----------------------------------------------------------------------------
242 -- |---------------------< chk_participation_type >---------------------------|
243 -- ----------------------------------------------------------------------------
244 --
245 -- Description:
246 -- Validates the participation_type exists in HR_LOOKUPS, where the
247 -- LOOKUP_TYPE is 'PARTICIPATION_TYPE'.
248 -- Also validates that updates are only allowed to a the participation_type
249 -- when it was previously null.
250 --
251 -- Pre-requisites:
252 -- None.
253 --
254 -- IN Parameters:
255 -- p_participant_id
256 -- p_object_version_number
257 -- p_participation_type
258 -- p_effective_date
259 --
260 -- Post Success:
261 -- Processing continues if the participation type is valid.
262 --
263 -- Post Failure:
264 -- An application error is raised, and the processing continues if the
265 -- participation_type is invalid.
266 --
267 -- Developer/Implementation Notes:
268 -- None.
269 --
270 -- Access Status:
271 -- Internal Row Handler Use Only.
272 --
273 -- ----------------------------------------------------------------------------
274 procedure chk_participation_type
275 (p_participant_id in per_participants.participant_id%TYPE
276 ,p_object_version_number in per_participants.object_version_number%TYPE
277 ,p_participation_type in per_participants.participation_type%TYPE
278 ,p_effective_date in date
279 )
280 is
281 --
282 l_proc varchar2(72) := g_package || 'chk_participation_type';
283 l_api_updating boolean;
284 --
285 begin
286 --
287 hr_utility.set_location('Entering: '||l_proc,10);
288 --
289 l_api_updating := per_par_shd.api_updating
290 (p_participant_id => p_participant_id
291 ,p_object_version_number => p_object_version_number
292 );
293 --
294 hr_utility.set_location(l_proc,20);
295 --
296 -- Check that update is allowed
297 --
298 if l_api_updating and (per_par_shd.g_old_rec.participation_type <> null) then
299 hr_utility.set_message(801,'PER_52466_PAR_TYPE_VAL_EXISTS');
300 hr_utility.raise_error;
301 else
302 if (p_participation_type <> null) and
303 (hr_api.not_exists_in_hr_lookups
304 (p_effective_date => p_effective_date
305 ,p_lookup_type => 'PARTICIPATION_TYPE'
306 ,p_lookup_code => p_participation_type
307 )) then
308 -- p_participation_type does not exist in lookup, thus error.
309 hr_utility.set_message(800,'PER_52463_PAR_INVAL_PART_TYPE');
310 hr_utility.raise_error;
311 end if;
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_PARTICIPANTS.PARTICIPATION_TYPE'
320 ) then
321 raise;
322 end if;
323 end chk_participation_type;
324 --
325 -- ----------------------------------------------------------------------------
326 -- |---------------------< chk_participation_status >-------------------------|
327 -- ----------------------------------------------------------------------------
328 --
329 -- Description:
330 -- Validates that the participation_status is either NULL or exists within
331 -- HR_LOOKUPS where the lookup_type is 'PARTICIPANT_ACCESS'.
332 --
333 -- Pre-Requisites:
334 -- None.
335 --
336 -- IN Parameters:
337 -- p_participation_status
338 -- p_effective_date
339 --
340 -- Post Success:
341 -- Processing continues if the participation status is valid.
342 --
343 -- Post Failure:
344 -- An application error is raised, and processing is terminated if the
345 -- participation_status is invalid.
346 --
347 -- Developer/Implementation Notes:
348 -- None.
349 --
350 -- Access Status:
351 -- Internal Row Handler Use Only.
352 -- ----------------------------------------------------------------------------
353 procedure chk_participation_status
354 (p_participation_status in per_participants.participation_status%TYPE
355 ,p_effective_date in date
356 )
357 is
358 --
359 l_proc varchar2(72) := g_package || 'chk_participation_status';
360 --
361 begin
362 --
363 hr_utility.set_location('Entering: '||l_proc,10);
364 --
365 if (p_participation_status <> null) and
366 (hr_api.not_exists_in_hr_lookups
367 (p_effective_date => p_effective_date
368 ,p_lookup_type => 'PARTICIPANT_ACCESS'
369 ,p_lookup_code => p_participation_status
370 )) then
371 -- p_participation_status does not exist in lookup, thus error
372 hr_utility.set_message(800,'PER_52464_PAR_INVAL_PAR_STATUS');
373 hr_utility.raise_error;
374 end if;
375 --
376 hr_utility.set_location('Leaving: '||l_proc,20);
377 --
378 EXCEPTION
379 when app_exception.application_exception then
380 if hr_multi_message.exception_add
381 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPATION_STATUS'
382 ) then
383 raise;
384 end if;
385
386 end chk_participation_status;
387 --
391 --
388 -------------------------------------------------------------------------------
389 --------------------------<chk_participation_in_table>-------------------------
390 -------------------------------------------------------------------------------
392 -- Description:
393 -- - Validates that a valid participation_in_table is inserted
394 --
395 -- Pre_conditions:
396 --
397 -- In Arguments:
398 -- p_participation_in_table
399 --
400 -- Post Success:
401 -- Process continues if :
402 -- All the in parameters are valid.
403 --
404 -- Post Failure:
405 -- An application error is raised and processing is terminated if any of
406 -- the following cases are found :
407 -- - table name passed in is invalid
408 --
409 -- Access Status
410 -- Internal Table Handler Use Only.
411 --
412 procedure chk_participation_in_table
413 (p_participation_in_table in per_participants.participation_in_table%TYPE
414 )
415 is
416 --
417 l_api_updating boolean;
418 l_exists varchar2(1);
419 l_proc varchar2(72) := g_package||'chk_participation_in_table';
420 --
421 Cursor chk_table_exists
422 is
423 select 'Y'
424 from all_tables
425 where table_name = upper(p_participation_in_table)
426 and owner = l_per_owner;
427
428 begin
429 hr_utility.set_location('Entering:'|| l_proc, 1);
430 --
431 if (p_participation_in_table is NULL) then
432 hr_utility.set_message(801, 'HR_52064_PAR_TABLE_NULL');
433 hr_utility.raise_error;
434 end if;
435 --
436 hr_utility.set_location('Entering:'|| l_proc, 5);
437 --
438 open chk_table_exists;
439 fetch chk_table_exists into l_exists;
440 if chk_table_exists%notfound then
441 close chk_table_exists;
442 hr_utility.set_message(801,'HR_52065_PAR_INVALID_TABLE');
443 hr_utility.raise_error;
444 end if;
445 close chk_table_exists;
446 hr_utility.set_location(l_proc, 3);
447 --
448 hr_utility.set_location('Leaving: '|| l_proc, 10);
449 EXCEPTION
450 when app_exception.application_exception then
451 if hr_multi_message.exception_add
452 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPATION_IN_TABLE'
453 ) then
454 raise;
455 end if;
456 end chk_participation_in_table;
457 --
458 --
459 -------------------------------------------------------------------------------
460 --------------------------<chk_participation_in_column>------------------------
461 -------------------------------------------------------------------------------
462 --
463 -- Description:
464 -- - Validates that a valid participation_in_column is inserted
465 --
466 -- Pre_conditions:
467 -- Valid participation_in_table
468 --
469 -- In Arguments:
470 -- p_participation_in_table
471 -- p_participation_in_column
472 --
473 -- Post Success:
474 -- Process continues if :
475 -- All the in parameters are valid.
476 --
477 -- Post Failure:
478 -- An application error is raised and processing is terminated if any of
479 -- the following cases are found :
480 -- - column name passed in is invalid
481 --
482 -- Access Status
483 -- Internal Table Handler Use Only.
484 --
485 procedure chk_participation_in_column
486 (p_participation_in_table in per_participants.participation_in_table%TYPE
487 ,p_participation_in_column in per_participants.participation_in_column%TYPE
488 )
489 is
490 --
491 l_api_updating boolean;
492 l_exists varchar2(1);
493 l_proc varchar2(72) := g_package||'chk_participation_in_column';
494 --
495 Cursor chk_column_exists
496 is
497 select 'Y'
498 from all_tab_columns
499 where table_name = upper(p_participation_in_table)
500 and column_name = upper(p_participation_in_column)
501 and owner = l_per_owner;
502
503 begin
504 hr_utility.set_location('Entering:'|| l_proc, 1);
505 --
506 if (p_participation_in_column is NULL) then
507 hr_utility.set_message(801, 'HR_52066_PAR_COLUMN_NULL');
508 hr_utility.raise_error;
509 end if;
510 --
511 open chk_column_exists;
512 fetch chk_column_exists into l_exists;
513 if chk_column_exists%notfound then
514 close chk_column_exists;
515 hr_utility.set_message(801,'HR_52067_PAR_INVALID_COLUMN');
516 hr_utility.raise_error;
517 end if;
518 close chk_column_exists;
519 hr_utility.set_location(l_proc, 3);
520 --
521 hr_utility.set_location('Leaving: '|| l_proc, 10);
522
523 EXCEPTION
524 when app_exception.application_exception then
525 if hr_multi_message.exception_add
526 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPATION_IN_COLUMN'
527 ) then
528 raise;
529 end if;
530 end chk_participation_in_column;
531 --
532 --
533 -------------------------------------------------------------------------------
534 -----------------------------<chk_participation_in_id>-------------------------
538 -- - Validates that a valid participation_in_id is inserted
535 -------------------------------------------------------------------------------
536 --
537 -- Description:
539 --
540 -- Pre_conditions:
541 -- Valid participation_in_table
542 -- Valid participation_in_column
543 -- Valid business_group_id
544 --
545 -- In Arguments:
546 -- p_participation_in_table
547 -- p_participation_in_column
548 -- p_participation_in_id
549 --
550 -- Post Success:
551 -- Process continues if :
552 -- All the in parameters are valid.
553 --
554 -- Post Failure:
555 -- An application error is raised and processing is terminated if any of
556 -- the following cases are found :
557 -- - participation_in_id does not exist in the table
558 --
559 -- Access Status
560 -- Internal Table Handler Use Only.
561 --
562 procedure chk_participation_in_id
563 (p_participation_in_table in per_participants.participation_in_table%TYPE
564 ,p_participation_in_column in per_participants.participation_in_column%TYPE
565 ,p_participation_in_id in per_participants.participation_in_id%TYPE
566 ,p_business_group_id in per_participants.business_group_id%TYPE
567 )
568 is
569 --
570 l_business_group_id per_participants.business_group_id%TYPE;
571 l_exists varchar2(1);
572 l_proc varchar2(72) := g_package||'chk_participation_in_id';
573 l_sql_cursor integer; -- Dynamic sql cursor
574 l_dynamic_sql varchar2(2000); -- Dynamic sql text
575 l_rows integer; -- No of rows returned
576 lv_cross_business_group VARCHAR2(10); -- bug 1980440 fix
577
578 --
579 -- Check if participation_in_id exists and is
580 -- in the same business group as participant
581 --
582
583 --
584 begin
585 /*
586 l_dynamic_sql := 'select par.business_group_id ' ||
587 'from {p_participation_in_table} par ' ||
588 'where par.{participation_in_column} = :p_participation_in_id ' ;
589 */
590 --
591 hr_utility.set_location('Entering:'|| l_proc, 1);
592 -- check if the participant_in_id is set as it is a mandatory
593 -- column, if not then error
594 if (p_participation_in_id is NULL) then
595 hr_utility.set_message(801, 'HR_52068_PAR_COLUMN_ID_NULL');
596 hr_utility.raise_error;
597 end if;
598 --
599 -- Check mandatory parameters have been set
600 --
601 hr_api.mandatory_arg_error
602 (p_api_name => l_proc
603 ,p_argument => 'participation_in_table'
604 ,p_argument_value => p_participation_in_table
605 );
606 --
607 --
608 hr_api.mandatory_arg_error
609 (p_api_name => l_proc
610 ,p_argument => 'participation_in_column'
611 ,p_argument_value => p_participation_in_column
612 );
613 --
614 hr_utility.set_location(l_proc, 2);
615 --
616 --
617 -- Dynamic literal string replacement
618 --
619 l_dynamic_sql := 'select par.business_group_id ' ||
620 'from ' ||p_participation_in_table ||' par ' ||
621 'where par.'|| p_participation_in_column || '= :p_participation_in_id ' ;
622 /*
623 l_dynamic_sql := Replace(l_dynamic_sql, '{p_participation_in_table}',
624 p_participation_in_table);
625 l_dynamic_sql := Replace(l_dynamic_sql, '{p_participation_in_column}',
626 p_participation_in_column);
627 */
628 --
629 hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 1,50), 4);
630 hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 51), 4);
631
632 -- Dynamic sql steps:
633 -- ==================
634 -- 1. Open dynamic sql cursor
635 -- 2. Parse dynamic sql
636 -- 3. Bind dynamic sql variables
637 -- 4. Define dynamic sql columns
638 -- 5. Execute and fetch dynamic sql
639 --
640 hr_Utility.Set_Location(l_proc, 6);
641 l_sql_cursor := dbms_sql.open_cursor; -- Step 1
642 --
643 hr_Utility.Set_Location(l_proc, 10);
644 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
645 --
646 hr_Utility.Set_Location(l_proc, 15);
647 dbms_sql.bind_variable(l_sql_cursor, -- Step 3
648 ':p_participation_in_id', p_participation_in_id);
649 --
650 hr_Utility.Set_Location(l_proc, 20);
651 dbms_sql.define_column(l_sql_cursor, 1, p_business_group_id); -- Step 4
652 --
653 hr_Utility.Set_Location(l_proc, 30);
654 l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
655 If (l_rows = 0 ) then
656 -- if no rows are returned that means the id does not exist
657 -- hence error
658 dbms_sql.close_cursor(l_sql_cursor);
659 hr_Utility.set_Location(l_proc, 35);
660 hr_utility.set_message(801,'HR_52069_PAR_INVALID_COLUMN_ID');
661 hr_utility.raise_error;
662 Else
663 -- check if the business groups match
664 --
665 -- Get the column values and close the cursor
666 --
667 hr_Utility.set_Location(l_proc, 40);
671 -- bug 1980440 fix starts
668 dbms_sql.column_value(l_sql_cursor, 1, l_business_group_id);
669 dbms_sql.close_cursor(l_sql_cursor);
670
672 -- do the validation if Cross Business Group profile is not enabled
673 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
674
675 if lv_cross_business_group <> 'Y' THEN
676
677 if p_business_group_id <> l_business_group_id then
678 hr_utility.set_message(801,'HR_52070_PAR_COL_ID_DIFF_BG');
679 hr_utility.raise_error;
680 end if;
681
682 end if;
683
684 End if;
685 --
686 --
687 -- If any other Oracle Error is trapped (e.g. during parse, execute,
688 -- fetch etc), then we must check to see if the cursor is still open
689 -- and close down if necessary.
690 --
691 Exception
692
693 when app_exception.application_exception then
694 if hr_multi_message.exception_add
695 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPATION_IN_ID'
696 ) then
697 raise;
698 end if;
699
700 When Others Then
701 Hr_Utility.Set_Location(l_proc, 50);
702 If (dbms_sql.is_open(l_sql_cursor)) then
703 hr_utility.Set_Location(l_proc, 55);
704 dbms_sql.close_cursor(l_sql_cursor);
705 End If;
706 Raise;
707 --
708 hr_utility.set_location(l_proc, 55);
709 --
710 hr_utility.set_location('Leaving: '|| l_proc, 60);
711
712
713 end chk_participation_in_id;
714 --
715 --
716 -----------------------------------------------------------------------------
717 --------------------------------<chk_person_id>------------------------------
718 -----------------------------------------------------------------------------
719 --
720 -- Description:
721 -- - Validates that the person_id has been entered as it is a mandatory
722 -- column
723 -- - Validates that the person is in the same business group
724 -- as participant
725 -- - Validates that the person is valid as of effective date
726 --
727 -- Pre_conditions:
728 -- Valid business group id
729 -- Valid participation_in_table
730 -- Valid participation_in_column
731 -- Valid participation_in_id
732 --
733 -- In Arguments:
734 -- p_participant_id
735 -- p_object_version_number
736 -- p_person_id
737 -- p_effective_date
738 -- p_business_group_id
739 -- participation_in_table
740 -- participation_in_column
741 -- participation_in_id
742 --
743 -- Post Success:
744 -- Process continues if :
745 -- All the in parameters are valid.
746 --
747 -- Post Failure:
748 -- An application error is raised and processing is terminated if any of
749 -- the following cases are found :
750 -- -- effective_date is not set
751 -- -- person does not exist as of effective date
752 -- -- person is not in the same business group as participants
753 --
754 -- Access Status
755 -- Internal Table Handler Use Only.
756 --
757 --
758 procedure chk_person_id
759 (p_participant_id in per_participants.participant_id%TYPE
760 ,p_object_version_number in per_participants.object_version_number%TYPE
761 ,p_person_id in per_participants.person_id%TYPE
762 ,p_business_group_id in per_participants.business_group_id%TYPE
763 ,p_participation_in_table in per_participants.participation_in_table%TYPE
764 ,p_participation_in_column in per_participants.participation_in_column%TYPE
765 ,p_participation_in_id in per_participants.participation_in_id%TYPE
766 ,p_effective_date in date
767 )
768 is
769 --
770 l_exists varchar2(1);
771 l_api_updating boolean;
772 l_business_group_id per_participants.business_group_id%TYPE;
773 l_proc varchar2(72) := g_package||'chk_chk_person_id';
774 lv_cross_business_group VARCHAR2(10); -- bug 1980440 fix
775
776 --
777 --
778 -- Cursor to check if the person_exists
779 --
780 Cursor csr_person_bg
781 is
782 select business_group_id
783 from per_all_people_f
784 where person_id = p_person_id;
785 -- Bug 1980440 fix
786 -- Cursor to check if person is valid as of effective date
787 -- this cursor uses per_all_people_f to support Cross Business Group
788 --
789 Cursor csr_cbg_person_bg
790 is
791 select business_group_id
792 from per_all_people_f
793 where person_id = p_person_id;
794 --
795 --
796 -- Cursor to check if person is valid
797 -- as of effective date
798 --
799 Cursor csr_person_valid_date
800 is
801 select 'Y'
802 from per_all_people_f
803 where person_id = p_person_id
804 and p_effective_date between
805 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
806
807 -- Bug 1980440 fix
808 -- Cursor to check if person is valid as of effective date
809 -- this cursor uses per_all_people_f to support Cross Business Group
810 --
811 Cursor csr_cbg_person_valid_date
812 is
813 select 'Y'
814 from per_all_people_f
818
815 where person_id = p_person_id
816 and p_effective_date between
817 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
819 --
820 -- Cursor to check if person id is unique for the
821 -- combination of participation_in_table, participation_in_column
822 -- and participation_in_id. Basically the same person cannot be the
823 -- appraisor and the appraisee etc...
824 --
825 Cursor csr_person_id_unique
826 is
827 select 'Y'
828 from per_participants par
829 where ( (p_participant_id is null)
830 or(p_participant_id <> par.participant_id)
831 )
832 and par.participation_in_table = p_participation_in_table
833 and par.participation_in_column = p_participation_in_column
834 and par.participation_in_id = p_participation_in_id
835 and par.person_id = p_person_id;
836 --
837 begin
838 hr_utility.set_location('Entering:'|| l_proc, 1);
839 --
840 if (p_person_id is NULL) then
841 hr_utility.set_message(801, 'HR_52075_PAR_PERSON_NULL');
842 hr_utility.raise_error;
843 end if;
844 --
845 -- Check mandatory parameters have been set
846 --
847 hr_api.mandatory_arg_error
848 (p_api_name => l_proc
849 ,p_argument => 'effective_date'
850 ,p_argument_value => p_effective_date
851 );
852 --
853 -- Check mandatory parameters have been set
854 --
855 hr_api.mandatory_arg_error
856 (p_api_name => l_proc
857 ,p_argument => 'participation_in_table'
858 ,p_argument_value => p_participation_in_table
859 );
860 --
861 --
862 -- Check mandatory parameters have been set
863 --
864 hr_api.mandatory_arg_error
865 (p_api_name => l_proc
866 ,p_argument => 'participation_in_column'
867 ,p_argument_value => p_participation_in_column
868 );
869 --
870 --
871 -- Check mandatory parameters have been set
872 --
873 hr_api.mandatory_arg_error
874 (p_api_name => l_proc
875 ,p_argument => 'participation_in_id'
876 ,p_argument_value => p_participation_in_id
877 );
878 --
879 --
880 hr_utility.set_location('Entering:'|| l_proc, 2);
881 --
882 --
883 -- bug 1980440 fix starts
884 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
885
886 -- Only proceed with validation if :
887 -- a) The current g_old_rec is current and
888 -- b) The value for participant role has changed
889 --
890 l_api_updating := per_par_shd.api_updating
891 (p_participant_id => p_participant_id
892 ,p_object_version_number => p_object_version_number);
893 --
894 if ( (l_api_updating and nvl(per_par_shd.g_old_rec.person_id,
895 hr_api.g_number)
896 <> nvl(p_person_id,hr_api.g_number)
897 ) or
898 (NOT l_api_updating)
899 ) then
900 --
901 hr_utility.set_location(l_proc, 2);
902 --
903 if p_person_id is not null then
904 -- Bug 1980440 fix
905 -- if Cross Business Group is not enabled use csr_person_bg cursor
906 -- to fetch the Business Group Id
907 if lv_cross_business_group <> 'Y' THEN
908 open csr_person_bg;
909 fetch csr_person_bg into l_business_group_id;
910 if csr_person_bg%notfound then
911 close csr_person_bg;
912 hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
913 hr_utility.raise_error;
914 end if;
915 close csr_person_bg;
916 else -- use the csr_cbg_person_bg cursor to get the Business Group Id
917 open csr_cbg_person_bg;
918 fetch csr_cbg_person_bg into l_business_group_id;
919 if csr_cbg_person_bg%notfound then
920 close csr_cbg_person_bg;
921 hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
922 hr_utility.raise_error;
923 end if;
924 close csr_cbg_person_bg;
925 end if;
926 hr_utility.set_location(l_proc, 3);
927
928 -- bug 1980440 fix starts
929 -- do the validation if Cross Business Group profile is not enabled
930 if lv_cross_business_group <> 'Y' THEN
931 -- check if business group match
932 if p_business_group_id <> l_business_group_id then
933 hr_utility.set_message(801,'HR_52072_PAR_PERSON_DIFF_BG');
934 hr_utility.raise_error;
935 end if;
936 end if;
937 -- bug 1980440 fix ends
938
939 hr_utility.set_location(l_proc, 4);
940 -- check if person is valid as of effective date
941 -- bug 1980440 fix starts
942 if lv_cross_business_group <> 'Y' THEN
943 open csr_person_valid_date;
944 fetch csr_person_valid_date into l_exists;
945 if csr_person_valid_date%notfound then
946 close csr_person_valid_date;
947 hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
948 hr_utility.raise_error;
949 end if;
950 close csr_person_valid_date;
951 else
955 close csr_cbg_person_valid_date;
952 open csr_cbg_person_valid_date;
953 fetch csr_cbg_person_valid_date into l_exists;
954 if csr_cbg_person_valid_date%notfound then
956 hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
957 hr_utility.raise_error;
958 end if;
959 close csr_cbg_person_valid_date;
960 end if;
961 -- bug 1980440 fix ends
962 -- check that the person id is unique
963 hr_utility.set_location(l_proc, 5);
964 open csr_person_id_unique;
965 fetch csr_person_id_unique into l_exists;
966 if csr_person_id_unique%found then
967 close csr_person_id_unique;
968 hr_utility.set_message(801,'HR_52074_PAR_PERSON_NOT_UNIQUE');
969 hr_utility.raise_error;
970 end if;
971 close csr_person_id_unique;
972 end if;
973 --
974 end if;
975 --
976 hr_utility.set_location(l_proc, 6);
977 --
978 hr_utility.set_location('Leaving: '|| l_proc, 10);
979 --
980 EXCEPTION
981 when app_exception.application_exception then
982 if hr_multi_message.exception_add
983 (p_associated_column1 => 'PER_PARTICIPANTS.PERSON_ID'
984 ) then
985 raise;
986 end if;
987 end chk_person_id;
988 --
989 -- -----------------------------------------------------------------------
990 -- |------------------------------< chk_df >-----------------------------|
991 -- -----------------------------------------------------------------------
992 --
993 -- Description:
994 -- Validates the all Descriptive Flexfield values.
995 --
996 -- Pre-conditions:
997 -- All other columns have been validated. Must be called as the
998 -- last step from insert_validate and update_validate.
999 --
1000 -- In Arguments:
1001 -- p_rec
1002 --
1003 -- Post Success:
1004 -- If the Descriptive Flexfield structure column and data values are
1005 -- all valid this procedure will end normally and processing will
1006 -- continue.
1007 --
1008 -- Post Failure:
1009 -- If the Descriptive Flexfield structure column value or any of
1010 -- the data values are invalid then an application error is raised as
1011 -- a PL/SQL exception.
1012 --
1013 -- Access Status:
1014 -- Internal Row Handler Use Only.
1015 --
1016 procedure chk_df
1017 (p_rec in per_par_shd.g_rec_type) is
1018 --
1019 l_proc varchar2(72) := g_package||'chk_df';
1020 --
1021 begin
1022 hr_utility.set_location('Entering:'||l_proc, 10);
1023 --
1024 if ((p_rec.participant_id is not null) and (
1025 nvl(per_par_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1026 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1027 nvl(per_par_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1028 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1029 nvl(per_par_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1030 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1031 nvl(per_par_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1032 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1033 nvl(per_par_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1034 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1035 nvl(per_par_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1036 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1037 nvl(per_par_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1038 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1039 nvl(per_par_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1040 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1041 nvl(per_par_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1042 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1043 nvl(per_par_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1044 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1045 nvl(per_par_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1046 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1047 nvl(per_par_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1048 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1049 nvl(per_par_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1050 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1051 nvl(per_par_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1052 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1053 nvl(per_par_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1054 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1055 nvl(per_par_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1056 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1057 nvl(per_par_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1058 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1059 nvl(per_par_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1060 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1061 nvl(per_par_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1062 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1063 nvl(per_par_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1064 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1065 nvl(per_par_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1066 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1067 or
1068 (p_rec.participant_id is null) then
1069 --
1070 -- Only execute the validation if absolutely necessary:
1074 --
1071 -- a) During update, the structure column value or any
1072 -- of the attribute values have actually changed.
1073 -- b) During insert.
1075 hr_dflex_utility.ins_or_upd_descflex_attribs
1076 (p_appl_short_name => 'PER'
1077 ,p_descflex_name => 'PER_PARTICIPANTS'
1078 ,p_attribute_category => p_rec.attribute_category
1079 ,p_attribute1_name => 'ATTRIBUTE1'
1080 ,p_attribute1_value => p_rec.attribute1
1081 ,p_attribute2_name => 'ATTRIBUTE2'
1082 ,p_attribute2_value => p_rec.attribute2
1083 ,p_attribute3_name => 'ATTRIBUTE3'
1084 ,p_attribute3_value => p_rec.attribute3
1085 ,p_attribute4_name => 'ATTRIBUTE4'
1086 ,p_attribute4_value => p_rec.attribute4
1087 ,p_attribute5_name => 'ATTRIBUTE5'
1088 ,p_attribute5_value => p_rec.attribute5
1089 ,p_attribute6_name => 'ATTRIBUTE6'
1090 ,p_attribute6_value => p_rec.attribute6
1091 ,p_attribute7_name => 'ATTRIBUTE7'
1092 ,p_attribute7_value => p_rec.attribute7
1093 ,p_attribute8_name => 'ATTRIBUTE8'
1094 ,p_attribute8_value => p_rec.attribute8
1095 ,p_attribute9_name => 'ATTRIBUTE9'
1096 ,p_attribute9_value => p_rec.attribute9
1097 ,p_attribute10_name => 'ATTRIBUTE10'
1098 ,p_attribute10_value => p_rec.attribute10
1099 ,p_attribute11_name => 'ATTRIBUTE11'
1100 ,p_attribute11_value => p_rec.attribute11
1101 ,p_attribute12_name => 'ATTRIBUTE12'
1102 ,p_attribute12_value => p_rec.attribute12
1103 ,p_attribute13_name => 'ATTRIBUTE13'
1104 ,p_attribute13_value => p_rec.attribute13
1105 ,p_attribute14_name => 'ATTRIBUTE14'
1106 ,p_attribute14_value => p_rec.attribute14
1107 ,p_attribute15_name => 'ATTRIBUTE15'
1108 ,p_attribute15_value => p_rec.attribute15
1109 ,p_attribute16_name => 'ATTRIBUTE16'
1110 ,p_attribute16_value => p_rec.attribute16
1111 ,p_attribute17_name => 'ATTRIBUTE17'
1112 ,p_attribute17_value => p_rec.attribute17
1113 ,p_attribute18_name => 'ATTRIBUTE18'
1114 ,p_attribute18_value => p_rec.attribute18
1115 ,p_attribute19_name => 'ATTRIBUTE19'
1116 ,p_attribute19_value => p_rec.attribute19
1117 ,p_attribute20_name => 'ATTRIBUTE20'
1118 ,p_attribute20_value => p_rec.attribute20
1119 ,p_attribute21_name => 'PARTICIPANT_USAGE_STATUS'
1120 ,p_attribute21_value => p_rec.participant_usage_status
1121 );
1122 end if;
1123 --
1124 hr_utility.set_location(' Leaving:'||l_proc, 20);
1125 --
1126 end chk_df;
1127 --
1128 -- ----------------------------------------------------------------------------
1129 -- |--------------------------< chk_participant_usage_status >-----------------|
1130 -- ----------------------------------------------------------------------------
1131 -- {Start Of Comments}
1132 --
1133 -- Description:
1134 -- This procedure is used to validate that the oparticipant_usage_status value
1135 --
1136 -- Pre Conditions:
1137 -- None
1138 --
1139 -- In Arguments:
1140 --
1141 --
1142 -- Post Success:
1143 -- Processing continues if the participant_usage_status value is valid.
1144 --
1145 -- Post Failure:
1146 -- An application error is raised if the participant_usage_status value is
1147 -- invalid.
1148 --
1149 -- {End Of Comments}
1150 -- ----------------------------------------------------------------------------
1151 Procedure chk_participant_usage_status
1152 (p_participant_id IN number
1153 ,p_object_version_number IN number
1154 ,p_participant_usage_status IN varchar2
1155 ,p_effective_date IN date
1156 ) IS
1157
1158
1159 --
1160 l_proc varchar2(72) := g_package || 'chk_participant_usage_status';
1161 l_api_updating boolean;
1162 l_participant_usage_status varchar2(30);
1163 --
1164 --
1165 BEGIN
1166
1167
1168 hr_utility.set_location('Entering:'|| l_proc, 10);
1169
1170
1171 --
1172 -- Only proceed with validation if :
1173 -- a) The current g_old_rec is current and
1174 -- b) The participant_usage_status value has changed
1175 --
1176 l_api_updating := per_par_shd.api_updating
1177 (p_participant_id => p_participant_id
1178 ,p_object_version_number => p_object_version_number);
1179 --
1180 IF (l_api_updating
1181 AND nvl(per_par_shd.g_old_rec.participant_usage_status, hr_api.g_varchar2)
1182 = nvl(p_participant_usage_status, hr_api.g_varchar2))
1183 THEN
1184 RETURN;
1185 END IF;
1186
1187 IF p_participant_usage_status IS NOT null THEN
1188 --
1189 -- Check that oparticipant_usage_status is valid.
1190 --
1191 hr_utility.set_location(l_proc, 20);
1192 IF hr_api.not_exists_in_hr_lookups
1193 (p_effective_date => p_effective_date
1194 ,p_lookup_type => 'APPRAISAL_OFFLINE_STATUS'
1195 ,p_lookup_code => upper(p_participant_usage_status)
1196 ) THEN
1197 fnd_message.set_name('PER', 'HR_34569_INV_PART_USAGE_STATUS');
1198 fnd_message.raise_error;
1199 END IF;
1200
1201 /* IF upper(p_participant_usage_status)
1202 not in ('EXPORTED','IMPORTED','IMPORT IGNORED') THEN
1206
1203 fnd_message.set_name('PER', 'HR_50264_INV_PART_USAGE_STATUS');
1204 fnd_message.raise_error;
1205 END IF;*/
1207 END IF;
1208
1209 hr_utility.set_location('Leaving:'|| l_proc, 970);
1210 EXCEPTION
1211
1212 WHEN app_exception.application_exception THEN
1213 IF hr_multi_message.exception_add
1214 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPANT_USAGE_STATUS')
1215 THEN
1216 hr_utility.set_location(' Leaving:'|| l_proc, 980);
1217 RAISE;
1218 END IF;
1219 hr_utility.set_location(' Leaving:'|| l_proc, 990);
1220
1221 END chk_participant_usage_status;
1222 --
1223 -- ----------------------------------------------------------------------------
1224 -- |---------------------------< insert_validate >----------------------------|
1225 -- ----------------------------------------------------------------------------
1226 Procedure insert_validate(p_rec in per_par_shd.g_rec_type
1227 ,p_effective_date in date) is
1228 --
1229 l_proc varchar2(72) := g_package||'insert_validate';
1230 --
1231 Begin
1232 hr_utility.set_location('Entering:'||l_proc, 5);
1233 --
1234 -- Call all supporting business operations
1235 --
1236 hr_api.validate_bus_grp_id(p_rec.business_group_id
1237 ,p_associated_column1 => per_par_shd.g_tab_nam ||
1238 '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1239 hr_multi_message.end_validation_set;
1240 --
1241 hr_utility.set_location(l_proc, 1);
1242 --
1243 per_par_bus.chk_participation_in_table
1244 (p_participation_in_table => p_rec.participation_in_table
1245 );
1246 --
1247 hr_utility.set_location(l_proc, 2);
1248 --
1249 per_par_bus.chk_participation_in_column
1250 (p_participation_in_table => p_rec.participation_in_table
1251 ,p_participation_in_column => p_rec.participation_in_column
1252 );
1253 --
1254 hr_utility.set_location(l_proc, 3);
1255 --
1256 per_par_bus.chk_participation_in_id
1257 (p_participation_in_table => p_rec.participation_in_table
1258 ,p_participation_in_column => p_rec.participation_in_column
1259 ,p_participation_in_id => p_rec.participation_in_id
1260 ,p_business_group_id => p_rec.business_group_id
1261 );
1262 --
1263 hr_utility.set_location(l_proc, 4);
1264 --
1265 per_par_bus.chk_person_id
1266 (p_participant_id => p_rec.participant_id
1267 ,p_object_version_number => p_rec.object_version_number
1268 ,p_person_id => p_rec.person_id
1269 ,p_business_group_id => p_rec.business_group_id
1270 ,p_participation_in_table => p_rec.participation_in_table
1271 ,p_participation_in_column => p_rec.participation_in_column
1272 ,p_participation_in_id => p_rec.participation_in_id
1273 ,p_effective_date => p_effective_date
1274 );
1275 --
1276 hr_utility.set_location(l_proc, 5);
1277 --
1278 per_par_bus.chk_participation_status
1279 (p_participation_status => p_rec.participation_status
1280 ,p_effective_date => p_effective_date
1281 );
1282 --
1283 hr_utility.set_location(l_proc,6);
1284 --
1285 per_par_bus.chk_participation_type
1286 (p_participant_id => p_rec.participant_id
1287 ,p_object_version_number => p_rec.object_version_number
1288 ,p_participation_type => p_rec.participation_type
1289 ,p_effective_date => p_effective_date
1290 );
1291 --
1292 hr_utility.set_location(' Leaving:'||l_proc, 10);
1293 --
1294 per_par_bus.chk_participant_usage_status
1295 (p_participant_id => p_rec.participant_id
1296 ,p_object_version_number => p_rec.object_version_number
1297 ,p_participant_usage_status => p_rec.participant_usage_status
1298 ,p_effective_date => p_effective_date
1299 );
1300 --
1301 -- Call Descriptive Flexfield Validation routines
1302 --
1303 per_par_bus.chk_df(p_rec => p_rec);
1304 --
1305 End insert_validate;
1306 --
1307 -- ----------------------------------------------------------------------------
1308 -- |---------------------------< update_validate >----------------------------|
1309 -- ----------------------------------------------------------------------------
1310 Procedure update_validate(p_rec in per_par_shd.g_rec_type
1311 ,p_effective_date in date) is
1312 --
1313 l_proc varchar2(72) := g_package||'update_validate';
1314 --
1315 Begin
1316 hr_utility.set_location('Entering:'||l_proc, 1);
1317 --
1318 -- Call all supporting business operations
1319 --
1320 --
1321 hr_api.validate_bus_grp_id(p_rec.business_group_id
1322 ,p_associated_column1 => per_par_shd.g_tab_nam ||
1323 '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1324
1325 hr_multi_message.end_validation_set;
1326 --
1327 --
1328 -- Rule Check non-updateable fields cannot be updated
1329 --
1330 chk_non_updateable_args(p_rec => p_rec);
1331 --
1332 hr_utility.set_location(l_proc, 2);
1333 --
1334 per_par_bus.chk_participation_status
1335 (p_participation_status => p_rec.participation_status
1336 ,p_effective_date => p_effective_date
1337 );
1338 --
1339 per_par_bus.chk_update_allowed
1340 (p_rec => p_rec
1341 );
1342 --
1343 per_par_bus.chk_participation_type
1344 (p_participant_id => p_rec.participant_id
1345 ,p_object_version_number => p_rec.object_version_number
1346 ,p_participation_type => p_rec.participation_type
1347 ,p_effective_date => p_effective_date
1348 );
1349 --
1350 hr_utility.set_location(l_proc,3);
1351 --
1352 per_par_bus.chk_person_id
1353 (p_participant_id => p_rec.participant_id
1354 ,p_object_version_number => p_rec.object_version_number
1355 ,p_person_id => p_rec.person_id
1356 ,p_business_group_id => p_rec.business_group_id
1357 ,p_participation_in_table => p_rec.participation_in_table
1358 ,p_participation_in_column => p_rec.participation_in_column
1359 ,p_participation_in_id => p_rec.participation_in_id
1360 ,p_effective_date => p_effective_date
1361 );
1362 --
1363 hr_utility.set_location(l_proc, 5);
1364 --
1365 hr_api.validate_bus_grp_id(p_rec.business_group_id
1366 ,p_associated_column1 => per_par_shd.g_tab_nam ||
1367 '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1368
1369 hr_multi_message.end_validation_set;
1370 --
1371 hr_utility.set_location(' Leaving:'||l_proc, 10);
1372 --
1373 per_par_bus.chk_participant_usage_status
1374 (p_participant_id => p_rec.participant_id
1375 ,p_object_version_number => p_rec.object_version_number
1376 ,p_participant_usage_status => p_rec.participant_usage_status
1377 ,p_effective_date => p_effective_date
1378 );
1379 --
1380 -- Call Descriptive Flexfield Validation routines
1381 --
1382 per_par_bus.chk_df(p_rec => p_rec);
1383 --
1384 End update_validate;
1385 --
1386 -- ----------------------------------------------------------------------------
1387 -- |---------------------------< delete_validate >----------------------------|
1388 -- ----------------------------------------------------------------------------
1389 Procedure delete_validate(p_rec in per_par_shd.g_rec_type) is
1390 --
1391 l_proc varchar2(72) := g_package||'delete_validate';
1392 --
1393 Begin
1394 hr_utility.set_location('Entering:'||l_proc, 5);
1395 --
1396 -- Call all supporting business operations
1397 --
1398 hr_utility.set_location(' Leaving:'||l_proc, 10);
1399 End delete_validate;
1400 --
1401 -- ----------------------------------------------------------------------------
1402 -- |-----------------------< return_legislation_code >-------------------------|
1403 -- ----------------------------------------------------------------------------
1404 Function return_legislation_code
1405 ( p_participant_id in number
1406 ) return varchar2 is
1407 --
1408 -- Declare cursor
1409 --
1410 cursor csr_leg_code is
1411 select legislation_code
1412 from per_business_groups pbg,
1413 per_participants par
1414 where par.participant_id = p_participant_id
1415 and pbg.business_group_id = par.business_group_id;
1416
1417 l_proc varchar2(72) := g_package||'return_legislation_code';
1418 l_legislation_code varchar2(150);
1419 --
1420 Begin
1421 hr_utility.set_location('Entering:'||l_proc, 5);
1422 --
1423 -- Ensure that all the mandatory parameters are not null
1424 --
1425 hr_api.mandatory_arg_error (p_api_name => l_proc,
1426 p_argument => 'participant_id',
1427 p_argument_value => p_participant_id );
1428 --
1429 if nvl(g_participant_id, hr_api.g_number) = p_participant_id then
1430 --
1431 -- The legislation code has already been found with a previous
1432 -- call to this function. Just return the value in the global
1433 -- variable.
1434 --
1435 l_legislation_code := g_legislation_code;
1436 hr_utility.set_location(l_proc, 10);
1437 else
1438 --
1439 -- The ID is different to the last call to this function
1440 -- or this is the first call to this function.
1441 --
1442 open csr_leg_code;
1443 fetch csr_leg_code into l_legislation_code;
1444 if csr_leg_code%notfound then
1445 close csr_leg_code;
1446 --
1447 -- The primary key is invalid therefore we must error out
1448 --
1449 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1450 hr_utility.raise_error;
1451 end if;
1452 --
1453 close csr_leg_code;
1454 g_participant_id := p_participant_id;
1455 g_legislation_code := l_legislation_code;
1456 end if;
1457 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1458
1459 return l_legislation_code;
1460 --
1461 hr_utility.set_location(' Leaving:'||l_proc, 30);
1462 --
1463 End return_legislation_code;
1464 --
1465 end per_par_bus;