[Home] [Help]
PACKAGE BODY: APPS.PER_PAR_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body per_par_bus as
2 /* $Header: peparrhi.pkb 120.1.12020000.2 2012/11/16 07:55:56 kgowripe ship $ */
3 --
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
38 -- the current record in the HR Schema
35 hr_utility.set_location('Entering:'||l_proc, 5);
36 --
37 -- Only proceed with validation if a row exists for
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
164 <> per_par_shd.g_old_rec.attribute3 then
161 fnd_message.set_name('PER','PER_52465_PAR_UPD_NOT_ALLOWED');
162 fnd_message.raise_error;
163 elsif p_rec.attribute3
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
211 elsif p_rec.attribute15
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;
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 --
388 -------------------------------------------------------------------------------
389 --------------------------<chk_participation_in_table>-------------------------
390 -------------------------------------------------------------------------------
391 --
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 /* changing as per NZDT DBCC Coding standards for bug#15882347
498 select 'Y'
499 from all_tab_columns
500 where table_name = upper(p_participation_in_table)
501 and column_name = upper(p_participation_in_column)
502 and owner = l_per_owner
503 */
504 select 'Y'
505 from user_synonyms syn, dba_tab_columns col
506 where syn.synonym_name = upper(p_participation_in_table)
507 and col.owner = syn.table_owner
508 and col.table_name = syn.table_name
509 and col.column_name = upper(p_participation_in_column);
510
511 begin
512 hr_utility.set_location('Entering:'|| l_proc, 1);
513 --
514 if (p_participation_in_column is NULL) then
515 hr_utility.set_message(801, 'HR_52066_PAR_COLUMN_NULL');
516 hr_utility.raise_error;
517 end if;
518 --
519 open chk_column_exists;
520 fetch chk_column_exists into l_exists;
521 if chk_column_exists%notfound then
522 close chk_column_exists;
523 hr_utility.set_message(801,'HR_52067_PAR_INVALID_COLUMN');
524 hr_utility.raise_error;
525 end if;
526 close chk_column_exists;
527 hr_utility.set_location(l_proc, 3);
528 --
529 hr_utility.set_location('Leaving: '|| l_proc, 10);
530
531 EXCEPTION
532 when app_exception.application_exception then
533 if hr_multi_message.exception_add
534 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPATION_IN_COLUMN'
535 ) then
536 raise;
537 end if;
538 end chk_participation_in_column;
539 --
540 --
541 -------------------------------------------------------------------------------
542 -----------------------------<chk_participation_in_id>-------------------------
543 -------------------------------------------------------------------------------
544 --
545 -- Description:
546 -- - Validates that a valid participation_in_id is inserted
547 --
548 -- Pre_conditions:
549 -- Valid participation_in_table
550 -- Valid participation_in_column
551 -- Valid business_group_id
552 --
553 -- In Arguments:
554 -- p_participation_in_table
555 -- p_participation_in_column
556 -- p_participation_in_id
557 --
558 -- Post Success:
559 -- Process continues if :
560 -- All the in parameters are valid.
561 --
562 -- Post Failure:
563 -- An application error is raised and processing is terminated if any of
564 -- the following cases are found :
565 -- - participation_in_id does not exist in the table
566 --
567 -- Access Status
568 -- Internal Table Handler Use Only.
569 --
570 procedure chk_participation_in_id
571 (p_participation_in_table in per_participants.participation_in_table%TYPE
572 ,p_participation_in_column in per_participants.participation_in_column%TYPE
573 ,p_participation_in_id in per_participants.participation_in_id%TYPE
574 ,p_business_group_id in per_participants.business_group_id%TYPE
575 )
576 is
577 --
578 l_business_group_id per_participants.business_group_id%TYPE;
579 l_exists varchar2(1);
580 l_proc varchar2(72) := g_package||'chk_participation_in_id';
581 l_sql_cursor integer; -- Dynamic sql cursor
582 l_dynamic_sql varchar2(2000); -- Dynamic sql text
583 l_rows integer; -- No of rows returned
584 lv_cross_business_group VARCHAR2(10); -- bug 1980440 fix
585
586 --
587 -- Check if participation_in_id exists and is
588 -- in the same business group as participant
589 --
590
591 --
592 begin
593 /*
594 l_dynamic_sql := 'select par.business_group_id ' ||
595 'from {p_participation_in_table} par ' ||
596 'where par.{participation_in_column} = :p_participation_in_id ' ;
597 */
598 --
599 hr_utility.set_location('Entering:'|| l_proc, 1);
600 -- check if the participant_in_id is set as it is a mandatory
601 -- column, if not then error
602 if (p_participation_in_id is NULL) then
603 hr_utility.set_message(801, 'HR_52068_PAR_COLUMN_ID_NULL');
604 hr_utility.raise_error;
605 end if;
606 --
607 -- Check mandatory parameters have been set
608 --
609 hr_api.mandatory_arg_error
610 (p_api_name => l_proc
611 ,p_argument => 'participation_in_table'
612 ,p_argument_value => p_participation_in_table
613 );
614 --
615 --
616 hr_api.mandatory_arg_error
617 (p_api_name => l_proc
618 ,p_argument => 'participation_in_column'
619 ,p_argument_value => p_participation_in_column
620 );
621 --
622 hr_utility.set_location(l_proc, 2);
623 --
624 --
625 -- Dynamic literal string replacement
626 --
627 l_dynamic_sql := 'select par.business_group_id ' ||
628 'from ' ||p_participation_in_table ||' par ' ||
629 'where par.'|| p_participation_in_column || '= :p_participation_in_id ' ;
630 /*
631 l_dynamic_sql := Replace(l_dynamic_sql, '{p_participation_in_table}',
632 p_participation_in_table);
633 l_dynamic_sql := Replace(l_dynamic_sql, '{p_participation_in_column}',
634 p_participation_in_column);
635 */
636 --
637 hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 1,50), 4);
638 hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 51), 4);
639
640 -- Dynamic sql steps:
641 -- ==================
642 -- 1. Open dynamic sql cursor
643 -- 2. Parse dynamic sql
644 -- 3. Bind dynamic sql variables
645 -- 4. Define dynamic sql columns
646 -- 5. Execute and fetch dynamic sql
647 --
648 hr_Utility.Set_Location(l_proc, 6);
649 l_sql_cursor := dbms_sql.open_cursor; -- Step 1
650 --
651 hr_Utility.Set_Location(l_proc, 10);
652 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
653 --
654 hr_Utility.Set_Location(l_proc, 15);
655 dbms_sql.bind_variable(l_sql_cursor, -- Step 3
656 ':p_participation_in_id', p_participation_in_id);
657 --
658 hr_Utility.Set_Location(l_proc, 20);
659 dbms_sql.define_column(l_sql_cursor, 1, p_business_group_id); -- Step 4
660 --
661 hr_Utility.Set_Location(l_proc, 30);
662 l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
663 If (l_rows = 0 ) then
664 -- if no rows are returned that means the id does not exist
665 -- hence error
666 dbms_sql.close_cursor(l_sql_cursor);
667 hr_Utility.set_Location(l_proc, 35);
668 hr_utility.set_message(801,'HR_52069_PAR_INVALID_COLUMN_ID');
669 hr_utility.raise_error;
670 Else
671 -- check if the business groups match
672 --
673 -- Get the column values and close the cursor
674 --
675 hr_Utility.set_Location(l_proc, 40);
676 dbms_sql.column_value(l_sql_cursor, 1, l_business_group_id);
677 dbms_sql.close_cursor(l_sql_cursor);
678
679 -- bug 1980440 fix starts
680 -- do the validation if Cross Business Group profile is not enabled
681 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
682
683 if lv_cross_business_group <> 'Y' THEN
684
685 if p_business_group_id <> l_business_group_id then
686 hr_utility.set_message(801,'HR_52070_PAR_COL_ID_DIFF_BG');
687 hr_utility.raise_error;
688 end if;
689
690 end if;
691
692 End if;
693 --
694 --
695 -- If any other Oracle Error is trapped (e.g. during parse, execute,
696 -- fetch etc), then we must check to see if the cursor is still open
697 -- and close down if necessary.
698 --
699 Exception
700
701 when app_exception.application_exception then
702 if hr_multi_message.exception_add
703 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPATION_IN_ID'
704 ) then
705 raise;
706 end if;
707
708 When Others Then
709 Hr_Utility.Set_Location(l_proc, 50);
710 If (dbms_sql.is_open(l_sql_cursor)) then
711 hr_utility.Set_Location(l_proc, 55);
712 dbms_sql.close_cursor(l_sql_cursor);
713 End If;
714 Raise;
715 --
716 hr_utility.set_location(l_proc, 55);
717 --
718 hr_utility.set_location('Leaving: '|| l_proc, 60);
719
720
721 end chk_participation_in_id;
722 --
723 --
724 -----------------------------------------------------------------------------
725 --------------------------------<chk_person_id>------------------------------
726 -----------------------------------------------------------------------------
727 --
728 -- Description:
729 -- - Validates that the person_id has been entered as it is a mandatory
730 -- column
731 -- - Validates that the person is in the same business group
732 -- as participant
733 -- - Validates that the person is valid as of effective date
734 --
735 -- Pre_conditions:
736 -- Valid business group id
737 -- Valid participation_in_table
738 -- Valid participation_in_column
739 -- Valid participation_in_id
740 --
741 -- In Arguments:
742 -- p_participant_id
743 -- p_object_version_number
744 -- p_person_id
745 -- p_effective_date
746 -- p_business_group_id
747 -- participation_in_table
748 -- participation_in_column
749 -- participation_in_id
750 --
751 -- Post Success:
752 -- Process continues if :
753 -- All the in parameters are valid.
754 --
755 -- Post Failure:
756 -- An application error is raised and processing is terminated if any of
757 -- the following cases are found :
758 -- -- effective_date is not set
759 -- -- person does not exist as of effective date
760 -- -- person is not in the same business group as participants
761 --
762 -- Access Status
763 -- Internal Table Handler Use Only.
764 --
765 --
766 procedure chk_person_id
767 (p_participant_id in per_participants.participant_id%TYPE
768 ,p_object_version_number in per_participants.object_version_number%TYPE
769 ,p_person_id in per_participants.person_id%TYPE
770 ,p_business_group_id in per_participants.business_group_id%TYPE
771 ,p_participation_in_table in per_participants.participation_in_table%TYPE
772 ,p_participation_in_column in per_participants.participation_in_column%TYPE
773 ,p_participation_in_id in per_participants.participation_in_id%TYPE
774 ,p_effective_date in date
775 )
776 is
777 --
778 l_exists varchar2(1);
779 l_api_updating boolean;
780 l_business_group_id per_participants.business_group_id%TYPE;
781 l_proc varchar2(72) := g_package||'chk_chk_person_id';
782 lv_cross_business_group VARCHAR2(10); -- bug 1980440 fix
783
784 --
785 --
786 -- Cursor to check if the person_exists
787 --
788 Cursor csr_person_bg
789 is
790 select business_group_id
791 from per_all_people_f
792 where person_id = p_person_id;
793 -- Bug 1980440 fix
794 -- Cursor to check if person is valid as of effective date
795 -- this cursor uses per_all_people_f to support Cross Business Group
796 --
797 Cursor csr_cbg_person_bg
798 is
799 select business_group_id
800 from per_all_people_f
801 where person_id = p_person_id;
802 --
803 --
804 -- Cursor to check if person is valid
805 -- as of effective date
806 --
807 Cursor csr_person_valid_date
808 is
809 select 'Y'
810 from per_all_people_f
811 where person_id = p_person_id
812 and p_effective_date between
813 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
814
815 -- Bug 1980440 fix
816 -- Cursor to check if person is valid as of effective date
817 -- this cursor uses per_all_people_f to support Cross Business Group
818 --
819 Cursor csr_cbg_person_valid_date
820 is
821 select 'Y'
822 from per_all_people_f
823 where person_id = p_person_id
824 and p_effective_date between
825 effective_start_date and nvl(effective_end_date,hr_api.g_eot);
826
827 --
828 -- Cursor to check if person id is unique for the
829 -- combination of participation_in_table, participation_in_column
830 -- and participation_in_id. Basically the same person cannot be the
831 -- appraisor and the appraisee etc...
832 --
833 Cursor csr_person_id_unique
834 is
835 select 'Y'
836 from per_participants par
837 where ( (p_participant_id is null)
838 or(p_participant_id <> par.participant_id)
839 )
840 and par.participation_in_table = p_participation_in_table
841 and par.participation_in_column = p_participation_in_column
842 and par.participation_in_id = p_participation_in_id
843 and par.person_id = p_person_id;
844 --
845 begin
846 hr_utility.set_location('Entering:'|| l_proc, 1);
847 --
848 if (p_person_id is NULL) then
849 hr_utility.set_message(801, 'HR_52075_PAR_PERSON_NULL');
850 hr_utility.raise_error;
851 end if;
852 --
853 -- Check mandatory parameters have been set
854 --
855 hr_api.mandatory_arg_error
856 (p_api_name => l_proc
857 ,p_argument => 'effective_date'
858 ,p_argument_value => p_effective_date
859 );
860 --
861 -- Check mandatory parameters have been set
862 --
863 hr_api.mandatory_arg_error
864 (p_api_name => l_proc
865 ,p_argument => 'participation_in_table'
866 ,p_argument_value => p_participation_in_table
867 );
868 --
869 --
870 -- Check mandatory parameters have been set
871 --
875 ,p_argument_value => p_participation_in_column
872 hr_api.mandatory_arg_error
873 (p_api_name => l_proc
874 ,p_argument => 'participation_in_column'
876 );
877 --
878 --
879 -- Check mandatory parameters have been set
880 --
881 hr_api.mandatory_arg_error
882 (p_api_name => l_proc
883 ,p_argument => 'participation_in_id'
884 ,p_argument_value => p_participation_in_id
885 );
886 --
887 --
888 hr_utility.set_location('Entering:'|| l_proc, 2);
889 --
890 --
891 -- bug 1980440 fix starts
892 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
893
894 -- Only proceed with validation if :
895 -- a) The current g_old_rec is current and
896 -- b) The value for participant role has changed
897 --
898 l_api_updating := per_par_shd.api_updating
899 (p_participant_id => p_participant_id
900 ,p_object_version_number => p_object_version_number);
901 --
902 if ( (l_api_updating and nvl(per_par_shd.g_old_rec.person_id,
903 hr_api.g_number)
904 <> nvl(p_person_id,hr_api.g_number)
905 ) or
906 (NOT l_api_updating)
907 ) then
908 --
909 hr_utility.set_location(l_proc, 2);
910 --
911 if p_person_id is not null then
912 -- Bug 1980440 fix
913 -- if Cross Business Group is not enabled use csr_person_bg cursor
914 -- to fetch the Business Group Id
915 if lv_cross_business_group <> 'Y' THEN
916 open csr_person_bg;
917 fetch csr_person_bg into l_business_group_id;
918 if csr_person_bg%notfound then
919 close csr_person_bg;
920 hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
921 hr_utility.raise_error;
922 end if;
923 close csr_person_bg;
924 else -- use the csr_cbg_person_bg cursor to get the Business Group Id
925 open csr_cbg_person_bg;
926 fetch csr_cbg_person_bg into l_business_group_id;
927 if csr_cbg_person_bg%notfound then
928 close csr_cbg_person_bg;
929 hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
930 hr_utility.raise_error;
931 end if;
932 close csr_cbg_person_bg;
933 end if;
934 hr_utility.set_location(l_proc, 3);
935
936 -- bug 1980440 fix starts
937 -- do the validation if Cross Business Group profile is not enabled
938 if lv_cross_business_group <> 'Y' THEN
939 -- check if business group match
940 if p_business_group_id <> l_business_group_id then
941 hr_utility.set_message(801,'HR_52072_PAR_PERSON_DIFF_BG');
942 hr_utility.raise_error;
943 end if;
944 end if;
945 -- bug 1980440 fix ends
946
947 hr_utility.set_location(l_proc, 4);
948 -- check if person is valid as of effective date
949 -- bug 1980440 fix starts
950 if lv_cross_business_group <> 'Y' THEN
951 open csr_person_valid_date;
952 fetch csr_person_valid_date into l_exists;
953 if csr_person_valid_date%notfound then
954 close csr_person_valid_date;
955 hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
956 hr_utility.raise_error;
957 end if;
958 close csr_person_valid_date;
959 else
960 open csr_cbg_person_valid_date;
961 fetch csr_cbg_person_valid_date into l_exists;
962 if csr_cbg_person_valid_date%notfound then
963 close csr_cbg_person_valid_date;
964 hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
965 hr_utility.raise_error;
966 end if;
967 close csr_cbg_person_valid_date;
968 end if;
969 -- bug 1980440 fix ends
970 -- check that the person id is unique
971 hr_utility.set_location(l_proc, 5);
972 open csr_person_id_unique;
973 fetch csr_person_id_unique into l_exists;
974 if csr_person_id_unique%found then
975 close csr_person_id_unique;
976 hr_utility.set_message(801,'HR_52074_PAR_PERSON_NOT_UNIQUE');
977 hr_utility.raise_error;
978 end if;
979 close csr_person_id_unique;
980 end if;
981 --
982 end if;
983 --
984 hr_utility.set_location(l_proc, 6);
985 --
986 hr_utility.set_location('Leaving: '|| l_proc, 10);
987 --
988 EXCEPTION
989 when app_exception.application_exception then
990 if hr_multi_message.exception_add
991 (p_associated_column1 => 'PER_PARTICIPANTS.PERSON_ID'
992 ) then
993 raise;
994 end if;
995 end chk_person_id;
996 --
997 -- -----------------------------------------------------------------------
998 -- |------------------------------< chk_df >-----------------------------|
999 -- -----------------------------------------------------------------------
1000 --
1001 -- Description:
1002 -- Validates the all Descriptive Flexfield values.
1003 --
1004 -- Pre-conditions:
1005 -- All other columns have been validated. Must be called as the
1006 -- last step from insert_validate and update_validate.
1007 --
1008 -- In Arguments:
1009 -- p_rec
1010 --
1011 -- Post Success:
1012 -- If the Descriptive Flexfield structure column and data values are
1013 -- all valid this procedure will end normally and processing will
1014 -- continue.
1015 --
1016 -- Post Failure:
1017 -- If the Descriptive Flexfield structure column value or any of
1018 -- the data values are invalid then an application error is raised as
1019 -- a PL/SQL exception.
1020 --
1021 -- Access Status:
1022 -- Internal Row Handler Use Only.
1023 --
1024 procedure chk_df
1025 (p_rec in per_par_shd.g_rec_type) is
1026 --
1027 l_proc varchar2(72) := g_package||'chk_df';
1028 --
1029 begin
1030 hr_utility.set_location('Entering:'||l_proc, 10);
1031 --
1032 if ((p_rec.participant_id is not null) and (
1033 nvl(per_par_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1034 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1035 nvl(per_par_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1036 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1037 nvl(per_par_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1038 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1039 nvl(per_par_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1040 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1041 nvl(per_par_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1042 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1043 nvl(per_par_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1044 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1045 nvl(per_par_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1046 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1047 nvl(per_par_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1048 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1049 nvl(per_par_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1050 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1051 nvl(per_par_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1052 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1053 nvl(per_par_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1054 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1055 nvl(per_par_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1056 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1057 nvl(per_par_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1058 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1059 nvl(per_par_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1060 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1061 nvl(per_par_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1062 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1063 nvl(per_par_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1064 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1065 nvl(per_par_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1066 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1067 nvl(per_par_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1068 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1069 nvl(per_par_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1070 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1071 nvl(per_par_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1072 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1073 nvl(per_par_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1074 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1075 or
1076 (p_rec.participant_id is null) then
1077 --
1078 -- Only execute the validation if absolutely necessary:
1079 -- a) During update, the structure column value or any
1080 -- of the attribute values have actually changed.
1081 -- b) During insert.
1082 --
1083 hr_dflex_utility.ins_or_upd_descflex_attribs
1084 (p_appl_short_name => 'PER'
1085 ,p_descflex_name => 'PER_PARTICIPANTS'
1086 ,p_attribute_category => p_rec.attribute_category
1087 ,p_attribute1_name => 'ATTRIBUTE1'
1088 ,p_attribute1_value => p_rec.attribute1
1089 ,p_attribute2_name => 'ATTRIBUTE2'
1090 ,p_attribute2_value => p_rec.attribute2
1091 ,p_attribute3_name => 'ATTRIBUTE3'
1092 ,p_attribute3_value => p_rec.attribute3
1093 ,p_attribute4_name => 'ATTRIBUTE4'
1094 ,p_attribute4_value => p_rec.attribute4
1095 ,p_attribute5_name => 'ATTRIBUTE5'
1096 ,p_attribute5_value => p_rec.attribute5
1097 ,p_attribute6_name => 'ATTRIBUTE6'
1098 ,p_attribute6_value => p_rec.attribute6
1099 ,p_attribute7_name => 'ATTRIBUTE7'
1100 ,p_attribute7_value => p_rec.attribute7
1101 ,p_attribute8_name => 'ATTRIBUTE8'
1102 ,p_attribute8_value => p_rec.attribute8
1103 ,p_attribute9_name => 'ATTRIBUTE9'
1104 ,p_attribute9_value => p_rec.attribute9
1105 ,p_attribute10_name => 'ATTRIBUTE10'
1106 ,p_attribute10_value => p_rec.attribute10
1107 ,p_attribute11_name => 'ATTRIBUTE11'
1108 ,p_attribute11_value => p_rec.attribute11
1109 ,p_attribute12_name => 'ATTRIBUTE12'
1110 ,p_attribute12_value => p_rec.attribute12
1111 ,p_attribute13_name => 'ATTRIBUTE13'
1112 ,p_attribute13_value => p_rec.attribute13
1113 ,p_attribute14_name => 'ATTRIBUTE14'
1114 ,p_attribute14_value => p_rec.attribute14
1115 ,p_attribute15_name => 'ATTRIBUTE15'
1116 ,p_attribute15_value => p_rec.attribute15
1117 ,p_attribute16_name => 'ATTRIBUTE16'
1118 ,p_attribute16_value => p_rec.attribute16
1119 ,p_attribute17_name => 'ATTRIBUTE17'
1120 ,p_attribute17_value => p_rec.attribute17
1121 ,p_attribute18_name => 'ATTRIBUTE18'
1122 ,p_attribute18_value => p_rec.attribute18
1123 ,p_attribute19_name => 'ATTRIBUTE19'
1124 ,p_attribute19_value => p_rec.attribute19
1125 ,p_attribute20_name => 'ATTRIBUTE20'
1126 ,p_attribute20_value => p_rec.attribute20
1127 ,p_attribute21_name => 'PARTICIPANT_USAGE_STATUS'
1128 ,p_attribute21_value => p_rec.participant_usage_status
1129 );
1130 end if;
1131 --
1132 hr_utility.set_location(' Leaving:'||l_proc, 20);
1133 --
1134 end chk_df;
1135 --
1136 -- ----------------------------------------------------------------------------
1137 -- |--------------------------< chk_participant_usage_status >-----------------|
1138 -- ----------------------------------------------------------------------------
1139 -- {Start Of Comments}
1140 --
1141 -- Description:
1142 -- This procedure is used to validate that the oparticipant_usage_status value
1143 --
1144 -- Pre Conditions:
1145 -- None
1146 --
1147 -- In Arguments:
1148 --
1149 --
1150 -- Post Success:
1151 -- Processing continues if the participant_usage_status value is valid.
1152 --
1153 -- Post Failure:
1154 -- An application error is raised if the participant_usage_status value is
1155 -- invalid.
1156 --
1157 -- {End Of Comments}
1158 -- ----------------------------------------------------------------------------
1159 Procedure chk_participant_usage_status
1160 (p_participant_id IN number
1161 ,p_object_version_number IN number
1162 ,p_participant_usage_status IN varchar2
1163 ,p_effective_date IN date
1164 ) IS
1165
1166
1167 --
1168 l_proc varchar2(72) := g_package || 'chk_participant_usage_status';
1169 l_api_updating boolean;
1170 l_participant_usage_status varchar2(30);
1171 --
1172 --
1173 BEGIN
1174
1175
1176 hr_utility.set_location('Entering:'|| l_proc, 10);
1177
1178
1179 --
1180 -- Only proceed with validation if :
1181 -- a) The current g_old_rec is current and
1182 -- b) The participant_usage_status value has changed
1183 --
1184 l_api_updating := per_par_shd.api_updating
1185 (p_participant_id => p_participant_id
1186 ,p_object_version_number => p_object_version_number);
1187 --
1188 IF (l_api_updating
1189 AND nvl(per_par_shd.g_old_rec.participant_usage_status, hr_api.g_varchar2)
1190 = nvl(p_participant_usage_status, hr_api.g_varchar2))
1191 THEN
1192 RETURN;
1193 END IF;
1194
1195 IF p_participant_usage_status IS NOT null THEN
1199 hr_utility.set_location(l_proc, 20);
1196 --
1197 -- Check that oparticipant_usage_status is valid.
1198 --
1200 IF hr_api.not_exists_in_hr_lookups
1201 (p_effective_date => p_effective_date
1202 ,p_lookup_type => 'APPRAISAL_OFFLINE_STATUS'
1203 ,p_lookup_code => upper(p_participant_usage_status)
1204 ) THEN
1205 fnd_message.set_name('PER', 'HR_34569_INV_PART_USAGE_STATUS');
1206 fnd_message.raise_error;
1207 END IF;
1208
1209 /* IF upper(p_participant_usage_status)
1210 not in ('EXPORTED','IMPORTED','IMPORT IGNORED') THEN
1211 fnd_message.set_name('PER', 'HR_50264_INV_PART_USAGE_STATUS');
1212 fnd_message.raise_error;
1213 END IF;*/
1214
1215 END IF;
1216
1217 hr_utility.set_location('Leaving:'|| l_proc, 970);
1218 EXCEPTION
1219
1220 WHEN app_exception.application_exception THEN
1221 IF hr_multi_message.exception_add
1222 (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPANT_USAGE_STATUS')
1223 THEN
1224 hr_utility.set_location(' Leaving:'|| l_proc, 980);
1225 RAISE;
1226 END IF;
1227 hr_utility.set_location(' Leaving:'|| l_proc, 990);
1228
1229 END chk_participant_usage_status;
1230 --
1231 -- ----------------------------------------------------------------------------
1232 -- |---------------------------< insert_validate >----------------------------|
1233 -- ----------------------------------------------------------------------------
1234 Procedure insert_validate(p_rec in per_par_shd.g_rec_type
1235 ,p_effective_date in date) is
1236 --
1237 l_proc varchar2(72) := g_package||'insert_validate';
1238 --
1239 Begin
1240 hr_utility.set_location('Entering:'||l_proc, 5);
1241 --
1242 -- Call all supporting business operations
1243 --
1244 hr_api.validate_bus_grp_id(p_rec.business_group_id
1245 ,p_associated_column1 => per_par_shd.g_tab_nam ||
1246 '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1247 hr_multi_message.end_validation_set;
1248 --
1249 hr_utility.set_location(l_proc, 1);
1250 --
1251 per_par_bus.chk_participation_in_table
1252 (p_participation_in_table => p_rec.participation_in_table
1253 );
1254 --
1255 hr_utility.set_location(l_proc, 2);
1256 --
1257 per_par_bus.chk_participation_in_column
1258 (p_participation_in_table => p_rec.participation_in_table
1259 ,p_participation_in_column => p_rec.participation_in_column
1260 );
1261 --
1262 hr_utility.set_location(l_proc, 3);
1263 --
1264 per_par_bus.chk_participation_in_id
1265 (p_participation_in_table => p_rec.participation_in_table
1266 ,p_participation_in_column => p_rec.participation_in_column
1267 ,p_participation_in_id => p_rec.participation_in_id
1268 ,p_business_group_id => p_rec.business_group_id
1269 );
1270 --
1271 hr_utility.set_location(l_proc, 4);
1272 --
1273 per_par_bus.chk_person_id
1274 (p_participant_id => p_rec.participant_id
1275 ,p_object_version_number => p_rec.object_version_number
1276 ,p_person_id => p_rec.person_id
1277 ,p_business_group_id => p_rec.business_group_id
1278 ,p_participation_in_table => p_rec.participation_in_table
1279 ,p_participation_in_column => p_rec.participation_in_column
1280 ,p_participation_in_id => p_rec.participation_in_id
1281 ,p_effective_date => p_effective_date
1282 );
1283 --
1284 hr_utility.set_location(l_proc, 5);
1285 --
1286 per_par_bus.chk_participation_status
1287 (p_participation_status => p_rec.participation_status
1288 ,p_effective_date => p_effective_date
1289 );
1290 --
1291 hr_utility.set_location(l_proc,6);
1292 --
1293 per_par_bus.chk_participation_type
1294 (p_participant_id => p_rec.participant_id
1295 ,p_object_version_number => p_rec.object_version_number
1296 ,p_participation_type => p_rec.participation_type
1297 ,p_effective_date => p_effective_date
1298 );
1299 --
1300 hr_utility.set_location(' Leaving:'||l_proc, 10);
1301 --
1302 per_par_bus.chk_participant_usage_status
1303 (p_participant_id => p_rec.participant_id
1304 ,p_object_version_number => p_rec.object_version_number
1305 ,p_participant_usage_status => p_rec.participant_usage_status
1306 ,p_effective_date => p_effective_date
1307 );
1308 --
1309 -- Call Descriptive Flexfield Validation routines
1310 --
1311 per_par_bus.chk_df(p_rec => p_rec);
1312 --
1313 End insert_validate;
1314 --
1315 -- ----------------------------------------------------------------------------
1316 -- |---------------------------< update_validate >----------------------------|
1317 -- ----------------------------------------------------------------------------
1318 Procedure update_validate(p_rec in per_par_shd.g_rec_type
1319 ,p_effective_date in date) is
1320 --
1321 l_proc varchar2(72) := g_package||'update_validate';
1322 --
1323 Begin
1324 hr_utility.set_location('Entering:'||l_proc, 1);
1325 --
1326 -- Call all supporting business operations
1327 --
1328 --
1329 hr_api.validate_bus_grp_id(p_rec.business_group_id
1330 ,p_associated_column1 => per_par_shd.g_tab_nam ||
1331 '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1332
1333 hr_multi_message.end_validation_set;
1334 --
1335 --
1336 -- Rule Check non-updateable fields cannot be updated
1337 --
1338 chk_non_updateable_args(p_rec => p_rec);
1339 --
1340 hr_utility.set_location(l_proc, 2);
1341 --
1342 per_par_bus.chk_participation_status
1343 (p_participation_status => p_rec.participation_status
1344 ,p_effective_date => p_effective_date
1345 );
1346 --
1347 per_par_bus.chk_update_allowed
1348 (p_rec => p_rec
1349 );
1350 --
1351 per_par_bus.chk_participation_type
1352 (p_participant_id => p_rec.participant_id
1353 ,p_object_version_number => p_rec.object_version_number
1354 ,p_participation_type => p_rec.participation_type
1355 ,p_effective_date => p_effective_date
1356 );
1357 --
1358 hr_utility.set_location(l_proc,3);
1359 --
1360 per_par_bus.chk_person_id
1361 (p_participant_id => p_rec.participant_id
1362 ,p_object_version_number => p_rec.object_version_number
1363 ,p_person_id => p_rec.person_id
1364 ,p_business_group_id => p_rec.business_group_id
1365 ,p_participation_in_table => p_rec.participation_in_table
1366 ,p_participation_in_column => p_rec.participation_in_column
1367 ,p_participation_in_id => p_rec.participation_in_id
1368 ,p_effective_date => p_effective_date
1369 );
1370 --
1371 hr_utility.set_location(l_proc, 5);
1372 --
1373 hr_api.validate_bus_grp_id(p_rec.business_group_id
1374 ,p_associated_column1 => per_par_shd.g_tab_nam ||
1375 '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1376
1377 hr_multi_message.end_validation_set;
1378 --
1379 hr_utility.set_location(' Leaving:'||l_proc, 10);
1380 --
1381 per_par_bus.chk_participant_usage_status
1382 (p_participant_id => p_rec.participant_id
1383 ,p_object_version_number => p_rec.object_version_number
1384 ,p_participant_usage_status => p_rec.participant_usage_status
1385 ,p_effective_date => p_effective_date
1386 );
1387 --
1388 -- Call Descriptive Flexfield Validation routines
1389 --
1390 per_par_bus.chk_df(p_rec => p_rec);
1391 --
1392 End update_validate;
1393 --
1394 -- ----------------------------------------------------------------------------
1395 -- |---------------------------< delete_validate >----------------------------|
1396 -- ----------------------------------------------------------------------------
1397 Procedure delete_validate(p_rec in per_par_shd.g_rec_type) is
1398 --
1399 l_proc varchar2(72) := g_package||'delete_validate';
1400 --
1401 Begin
1402 hr_utility.set_location('Entering:'||l_proc, 5);
1403 --
1404 -- Call all supporting business operations
1405 --
1406 hr_utility.set_location(' Leaving:'||l_proc, 10);
1407 End delete_validate;
1408 --
1409 -- ----------------------------------------------------------------------------
1410 -- |-----------------------< return_legislation_code >-------------------------|
1411 -- ----------------------------------------------------------------------------
1412 Function return_legislation_code
1413 ( p_participant_id in number
1414 ) return varchar2 is
1415 --
1416 -- Declare cursor
1417 --
1418 cursor csr_leg_code is
1419 select legislation_code
1420 from per_business_groups pbg,
1421 per_participants par
1422 where par.participant_id = p_participant_id
1423 and pbg.business_group_id = par.business_group_id;
1424
1425 l_proc varchar2(72) := g_package||'return_legislation_code';
1426 l_legislation_code varchar2(150);
1427 --
1428 Begin
1429 hr_utility.set_location('Entering:'||l_proc, 5);
1430 --
1431 -- Ensure that all the mandatory parameters are not null
1432 --
1433 hr_api.mandatory_arg_error (p_api_name => l_proc,
1434 p_argument => 'participant_id',
1435 p_argument_value => p_participant_id );
1436 --
1437 if nvl(g_participant_id, hr_api.g_number) = p_participant_id then
1438 --
1439 -- The legislation code has already been found with a previous
1440 -- call to this function. Just return the value in the global
1441 -- variable.
1442 --
1443 l_legislation_code := g_legislation_code;
1444 hr_utility.set_location(l_proc, 10);
1445 else
1446 --
1447 -- The ID is different to the last call to this function
1448 -- or this is the first call to this function.
1449 --
1450 open csr_leg_code;
1451 fetch csr_leg_code into l_legislation_code;
1452 if csr_leg_code%notfound then
1453 close csr_leg_code;
1454 --
1455 -- The primary key is invalid therefore we must error out
1456 --
1457 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1458 hr_utility.raise_error;
1459 end if;
1460 --
1461 close csr_leg_code;
1462 g_participant_id := p_participant_id;
1463 g_legislation_code := l_legislation_code;
1464 end if;
1465 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1466
1467 return l_legislation_code;
1468 --
1469 hr_utility.set_location(' Leaving:'||l_proc, 30);
1470 --
1471 End return_legislation_code;
1472 --
1473 end per_par_bus;