[Home] [Help]
PACKAGE BODY: APPS.GHR_PRH_BUS
Source
1 Package Body ghr_prh_bus as
2 /* $Header: ghprhrhi.pkb 120.2.12000000.1 2007/03/27 09:52:58 managarw noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_prh_bus.'; -- Global package name
9
10
11 Procedure chk_non_updateable_args(p_rec in ghr_prh_shd.g_rec_type) is
12 --
13 l_proc varchar2(72) ;
14 l_error exception;
15 l_argument varchar2(30);
16 --
17 Begin
18 l_proc := g_package || 'chk_non_updateable_args';
19 hr_utility.set_location( ' Entering:' ||l_proc, 10);
20 --
21 -- Only proceed with validation of a row exists for
22 -- the current record in the HR schema
23 --
24 if not ghr_prh_shd.api_updating
25 (p_pa_routing_history_id => p_rec.pa_routing_history_id
26 ,p_object_version_number => p_rec.object_version_number
27 ) then
28 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
29 hr_utility.set_message('PROCEDURE',l_proc);
30 hr_utility.set_message('STEP', '20');
31 end if;
32 hr_utility.set_location(l_proc,30);
33 --
34 if nvl(p_rec.pa_request_id,hr_api.g_number)
35 <> nvl(ghr_prh_shd.g_old_rec.pa_request_id,hr_api.g_number) then
36 l_argument := 'pa_request_id';
37 raise l_error;
38 end if;
39 /*if nvl(p_rec.groupbox_id,hr_api.g_number)
40 <> nvl(ghr_prh_shd.g_old_rec.groupbox_id,hr_api.g_number) then
41 l_argument := 'groupbox_id';
42 raise l_error;
43 end if;
44 */
45 if nvl(p_rec.routing_list_id,hr_api.g_number)
46 <> nvl(ghr_prh_shd.g_old_rec.routing_list_id,hr_api.g_number) then
47 l_argument := 'routing_list_id';
48 raise l_error;
49 end if;
50
51 if nvl(p_rec.routing_seq_number,hr_api.g_number)
52 <> nvl(ghr_prh_shd.g_old_rec.routing_seq_number,hr_api.g_number) then
53 l_argument := 'routing_seq_number';
54 raise l_error;
55 end if;
56
57 /* if nvl(p_rec.nature_of_action_id,hr_api.g_number)
58 <> nvl(ghr_prh_shd.g_old_rec.nature_of_action_id,hr_api.g_number) then
59 l_argument := 'nature_of_action_id';
60 raise l_error;
61 end if;
62 */
63 hr_utility.set_location(l_proc,40);
64 --
65 exception
66 when l_error then
67 hr_api.argument_changed_error
68 (p_api_name => l_proc
69 ,p_argument => l_argument);
70 when others then
71 raise;
72 end chk_non_updateable_args;
73 --
74
75 -- ---------------------------------------------------------------------------
76 -- |-----------------------< chk_pa_request_id >--------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 -- Description:
80 -- Validates that the pa_request_id exists in the ghr_pa_requests_table
81 --
82 -- Pre-conditions:
83 -- None
84 --
85 -- In Arguments:
86 -- p_pa_request_id
87 -- p_pa_routing_history_id
88 -- p_object_version_number
89 --
90 -- Post Success:
91 -- Processing continues
92 --
93 -- Post Failure:
94 -- An application error is raised and processing is terminated.
95 --
96 -- Access Status:
97 -- Internal Table Handler Use Only.
98 --
99 Procedure chk_pa_request_id
100 (p_pa_request_id in ghr_pa_routing_history.pa_request_id%TYPE
101 ,p_pa_routing_history_id in ghr_pa_routing_history.pa_routing_history_id%TYPE
102 ,p_object_Version_number in ghr_pa_routing_history.object_version_number%TYPE
103 ) is
104 --
105 l_exists boolean := FALSE;
106 l_proc varchar2(72);
107 l_api_updating boolean;
108 --
109 Cursor c_pa_req_id is
110 select 1
111 from ghr_pa_requests par
112 where par.pa_request_id = p_pa_request_id;
113 --
114 begin
115 l_proc := g_package||'chk_pa_request_id';
116 hr_utility.set_location('Entering:'|| l_proc, 10);
117 --
118 -- Check mandatory parameters have been set
119 --
120 hr_api.mandatory_arg_error
121 (p_api_name => l_proc
122 ,p_argument => 'pa_request_id'
123 ,p_argument_value => p_pa_request_id
124 );
125 --
126 hr_utility.set_location(l_proc, 20);
127 --
128 -- Only proceed with validation if:
129 -- a) The current g_old_rec is current and
130 -- b) The routing status value has changed
131 -- c) a record is being inserted
132 --
133 l_api_updating := ghr_prh_shd.api_updating
134 (p_pa_routing_history_id => p_pa_routing_history_id
135 ,p_object_version_number => p_object_version_number
136 );
137 hr_utility.set_location(l_proc, 30);
138 --
139 if ((l_api_updating
140 and nvl(ghr_prh_shd.g_old_rec.pa_request_id, hr_api.g_number)
141 <> nvl(p_pa_request_id,hr_api.g_number))
142 or
143 (NOT l_api_updating))
144 then
145 hr_utility.set_location(l_proc, 40);
146 --
147 -- Check if pa_request_id is valid
148 --
149 for rec in c_pa_req_id loop
150 l_exists := TRUE;
151 exit;
152 end loop;
153 if not l_exists then
154 ghr_prh_shd.constraint_error(p_constraint_name => 'GHR_PA_ROUTING_HIST_FK1');
155 end if;
156 end if;
157 --
158 hr_utility.set_location(' Leaving:'|| l_proc, 3);
159 end chk_pa_request_id;
160
161
162
163 -- ----------------------------------------------------------------------------
164 -- |---------------------------<chk_groupbox_id>----------------------------|
165 -- ----------------------------------------------------------------------------
166 -- Description:
167 -- Validates that the group_box_id exists in the table GHR_GROUPBOXES
168 -- for a specific routing_group
169 --
170 -- Pre-conditions:
171 --
172 --
173 -- In Arguments:
174 -- p_pa_routing_history_id
175 -- p_pa_request_id
176 -- p_groupbox_id
177 -- p_object_version_number
178 --
179 -- Post Success:
180 -- If the group_box_id is valid
181 -- processing continues
182 --
183 -- Post Failure:
184 -- An application error is raised and processing is terminated
185 --
186 -- Access Status:
187 -- Internal Table Handler Use Only.
188 --
189 procedure chk_groupbox_id
190 (p_pa_routing_history_id in ghr_pa_routing_history.pa_routing_history_id%TYPE
191 ,p_pa_request_id in ghr_pa_requests.pa_request_id%TYPE
192 ,p_groupbox_id in ghr_pa_routing_history.groupbox_id%TYPE
193 ,p_object_version_number in ghr_pa_routing_history.object_version_number%TYPE
194 )is
195
196 --
197 l_exists boolean := false;
198 l_proc varchar2(72) ;
199 l_api_updating boolean;l_grp_box Number;
200 --
201 Cursor c_gpbox_id is
202 select 1
203 from ghr_groupboxes gbx,
204 ghr_pa_requests par
205 where par.pa_request_id = p_pa_request_id
206 and gbx.routing_group_id = par.routing_group_id
207 and gbx.groupbox_id = p_groupbox_id;
208 --
209 begin
210 l_proc := g_package||'chk_groupbox_id';
211 hr_utility.set_location('Entering:'|| l_proc, 1);
212 --
213 -- Check mandatory parameters have been set
214 hr_api.mandatory_arg_error
215 (p_api_name => l_proc
216 ,p_argument => 'pa_request_id'
217 ,p_argument_value => p_pa_request_id
218 );
219 -- Only proceed with validation if:
220 -- a) The current g_old_rec is current and
221 -- b) routing_seq_number has changed
222 -- c) A record is being inserted
223 --
224 l_api_updating := ghr_prh_shd.api_updating
225 (p_pa_routing_history_id => p_pa_routing_history_id
226 ,p_object_version_number => p_object_version_number);
227 --
228 if ((l_api_updating and nvl(ghr_prh_shd.g_old_rec.groupbox_id,hr_api.g_number)
229 <> nvl(p_groupbox_id,hr_api.g_number))
230 or (NOT l_api_updating)) then
231 --
232 hr_utility.set_location(l_proc, 2);
233 --
234 -- check if the groupbox_id exists for the
235 -- routing_group_id
236 if p_groupbox_id is not null then
237 for rec in c_gpbox_id loop
238 l_exists := true;
239 exit;
240 end loop;
241 --bug 4896738 skip checking for group box if it comes from process futures..
242
243 hr_utility.set_location('value before raising invalid group box error'||l_grp_box,2222);
244 if not l_exists then
245 hr_utility.set_message(8301,'GHR_38101_INV_GROUPBOX_ID');
246 hr_utility.raise_error;
247
248 end if;
249 end if;
250 --
251 end if;
252 --
253 hr_utility.set_location(' Leaving:'|| l_proc, 3);
254 end chk_groupbox_id;
255
256 -- ----------------------------------------------------------------------------
257 -- |---------------------------< chk_user_name>----------------------------|
258 -- ----------------------------------------------------------------------------
259
260 -- Description:
261 -- Validates that the user_name exists in the table fnd_user and
262 -- Pre-conditions:
263 --
264 --
265 -- In Arguments:
266 -- p_pa_routing_history_id
267 -- p_user_name
268 -- p_object_version_number
269 --
270 -- Post Success:
271 -- If the user_person_id is valid
272 -- processing continues
273 --
274 -- Post Failure:
275 -- An application error is raised and processing is terminated
276 --
277 -- Access Status:
278 -- Internal Table Handler Use Only.
279 --
280
281 Procedure chk_user_name
282 (p_pa_routing_history_id in ghr_pa_routing_history.pa_routing_history_id%TYPE
283 ,p_user_name in ghr_pa_routing_history.user_name%TYPE
284 ,p_groupbox_id in ghr_pa_routing_history.groupbox_id%TYPE
285 ,P_object_version_number in ghr_pa_routing_history.object_version_number%TYPE
286 ) is
287 --
288 l_proc varchar2(72) ;
289 l_exists boolean := false;
290 l_api_updating boolean;
291 l_gpbox_id number := p_groupbox_id;
292 -- cursor to check that the person_id exists.
293 --
294 cursor c_groupbox_user is
295 select 1
296 from ghr_groupbox_users gbu
297 where gbu.groupbox_id = p_groupbox_id
298 and gbu.user_name = p_user_name;
299
300 cursor c_user_name is
301 select 1
302 from fnd_user
303 where upper(user_name) = upper(p_user_name);
304
305 begin
306 l_proc := g_package ||'chk_user_name';
307 hr_utility.set_location('Entering:'||l_proc,10);
308 --
309 -- Only proceed with validation if:
310 -- a) The current g_old_rec is current and
311 -- b) routing_user_name has changed
312 -- c) A record is being inserted
313 --
314 l_api_updating := ghr_prh_shd.api_updating
315 (p_pa_routing_history_id => p_pa_routing_history_id
316 ,p_object_version_number => p_object_version_number
317 );
318
319 --
320 if ((l_api_updating and nvl(ghr_prh_shd.g_old_rec.user_name,hr_api.g_varchar2)
321 <> nvl(p_user_name,hr_api.g_varchar2))
322 or (NOT l_api_updating)) then
323
324 --
325 hr_utility.set_location(l_proc, 2);
326 --
327 -- check if the user_name is valid
328 if p_user_name is not null then
329 if p_groupbox_id is not null then
330 for groupbox_user in c_groupbox_user loop
331 l_exists := true;
332 exit;
333 end loop;
334 --bug# 4896738
335 if not l_exists and not ghr_proc_fut_mt.g_skip_grp_box then
336 hr_utility.set_message(8301,'GHR_38103_INV_GROUPBOX_USER');
337 hr_utility.raise_error;
338 end if;
339 end if;
340 l_exists := false;
341 for c_user_name_rec in c_user_name loop
342 l_exists := true;
343 exit;
344 end loop;
345 if not l_exists then
346 hr_utility.set_message(8301,'GHR_38102_INV_USER_NAME');
347 hr_utility.raise_error;
348 end if;
349 end if;
350 end if;
351 --
352 hr_utility.set_location(' Leaving:'|| l_proc, 3);
353 end chk_user_name;
354 --
355
356
357 -- ---------------------------------------------------------------------------
358 -- |-----------------------< chk_routing_list_id >--------------------------|
359 -- ---------------------------------------------------------------------------
360 --
361 -- Description:
362 -- Validates that the routing_list_id exists in the table
363 -- ghr_routing_lists
364 --
365 -- Pre-conditions:
366 -- None
367 --
368 -- In Arguments:
369 -- p_routing_list_id
370 -- p_pa_routing_history_id
371 -- p_object_version_number
372 --
373 -- Post Success:
374 -- Processing continues
375 --
376 -- Post Failure:
377 -- An application error is raised and processing is terminated.
378 --
379 -- Access Status:
380 -- Internal Table Handler Use Only.
381 --
382 Procedure chk_routing_list_id
383 (p_routing_list_id in ghr_pa_routing_history.routing_list_id%TYPE
384 ,p_pa_routing_history_id in ghr_pa_routing_history.pa_routing_history_id%TYPE
385 ,p_object_version_number in ghr_pa_routing_history.object_version_number%TYPE
386 ) is
387 --
388 l_exists boolean := false;
389 l_proc varchar2(72) := g_package||'chk_routing_list_id';
390 l_api_updating boolean;
391 --
395 where prl.routing_list_id = p_routing_list_id;
392 Cursor c_rout_list_id is
393 select 1
394 from ghr_routing_lists prl
396 --
397 begin
398 hr_utility.set_location('Entering:'|| l_proc, 10);
399 --
400 -- Check mandatory parameters have been set
401 --
402 hr_utility.set_location(l_proc, 20);
403 -- Only proceed with validation if:
404 -- a) The current g_old_rec is current and
405 -- b) The routing status value has changed
406 -- c) a record is being inserted
407 --
408 l_api_updating := ghr_prh_shd.api_updating
409 (p_pa_routing_history_id => p_pa_routing_history_id
410 ,p_object_version_number => p_object_version_number
411 );
412 hr_utility.set_location(l_proc, 30);
413 --
414 if ((l_api_updating
415 and nvl(ghr_prh_shd.g_old_rec.routing_list_id, hr_api.g_number)
416 <> nvl(p_routing_list_id,hr_api.g_number))
417 or
418 (NOT l_api_updating))
419 then
420 hr_utility.set_location(l_proc, 40);
421 --
422 -- Check if p_routing_list_id is valid
423 --
424 if p_routing_list_id is not null then
425 for rec in c_rout_list_id loop
426 l_exists := true;
427 end loop;
428 if not l_exists then
429 ghr_prh_shd.constraint_error(p_constraint_name => 'GHR_PA_ROUTING_HIST_FK2');
430 end if;
431 end if;
432 end if;
433 --
434 hr_utility.set_location(' Leaving:'|| l_proc, 50);
435 end chk_routing_list_id;
436 --
437
438
439 -- ----------------------------------------------------------------------------
440 -- |---------------------------<chk_rout_user_sequ_numb>----------------------------|
441 -- ----------------------------------------------------------------------------
442 -- Description:
443 -- Validates that the routing_seq_number exists in the table
444 -- 'GHR_ROUTING_LIST_MEMBERS for the specific routing_list
445 --
446 -- Pre-conditions:
447 --
448 --
449 -- In Arguments:
450 --
451 -- p_pa_routing_history_id
452 -- p_routing_list_id
453 -- p_routing_seq_number
454 -- p_object_version_number
455 --
456 -- Post Success:
457 -- If the routing_seq_number is valid
458 -- processing continues
459 --
460 -- Post Failure:
461 -- An application error is raised and processing is terminated
462 --
463 -- Access Status:
464 -- Internal Table Handler Use Only.
465 --
466 procedure chk_rout_user_sequ_numb
467 (p_pa_routing_history_id in ghr_pa_routing_history.pa_routing_history_id%TYPE
468 ,p_routing_list_id in ghr_pa_routing_history.routing_list_id%TYPE
469 ,p_routing_seq_number in ghr_pa_routing_history.routing_seq_number%TYPE
470 ,p_object_version_number in ghr_pa_routing_history.object_version_number%TYPE
471 )is
472
473 --
474 l_exists boolean := FALSE;
475 l_proc varchar2(72) := g_package||'chk_routing_seq_number';
476 l_api_updating boolean;
477 --
478 Cursor c_seq_num is
479 select 1
480 from ghr_routing_list_members rlm
481 where rlm.routing_list_id = p_routing_list_id
482 and rlm.seq_number = p_routing_seq_number;
483
484 begin
485 hr_utility.set_location('Entering:'|| l_proc, 1);
486 --
487 -- Only proceed with validation if:
488 -- a) The current g_old_rec is current and
489 -- b) routing_seq_number has changed
490 -- c) A record is being inserted
491 --
492 l_api_updating := ghr_prh_shd.api_updating
493 (p_pa_routing_history_id => p_pa_routing_history_id
494 ,p_object_version_number => p_object_version_number);
495 --
496 if ((l_api_updating and nvl(ghr_prh_shd.g_old_rec.routing_seq_number,hr_api.g_number)
497 <> nvl(p_routing_seq_number,hr_api.g_number))
498 or (NOT l_api_updating)) then
499 --
500 hr_utility.set_location(l_proc, 2);
501 --
502 -- check if the routing_seq_number exists for the
503 -- routing_list_id
504 if p_routing_seq_number is not null then
505 for rec in c_seq_num loop
506 l_exists := TRUE;
507 exit;
508 end loop;
509 if not l_exists then
510 hr_utility.set_message(8301,'GHR_38104_INV_ROUT_SEQ_NUM');
511 hr_utility.raise_error;
512 end if;
513 end if;
514 end if;
515 --
516 hr_utility.set_location(' Leaving:'|| l_proc, 3);
517 end chk_rout_user_sequ_numb;
518 --
519 --
520
521 -- ----------------------------------------------------------------------------
522 -- |---------------------------< insert_validate >----------------------------|
523 -- ----------------------------------------------------------------------------
524 Procedure insert_validate
525 (p_rec in ghr_prh_shd.g_rec_type
526 )is
527 --
528 l_proc varchar2(72) := g_package||'insert_validate';
529 --
530
531 Begin
532 hr_utility.set_location('Entering:'||l_proc, 5);
533 --
534 -- Call all supporting business operations
535 --
539 ghr_prh_bus.chk_pa_request_id(p_pa_request_id =>p_rec.pa_request_id
536 -- hr_utility.set_location(l_proc, 10);
537 --
538 -- to check valid request_id
540 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
541 ,p_object_Version_number =>p_rec.object_version_number
542 );
543
544
545 -- to check valid user_name
546 ghr_prh_bus.chk_user_name(p_user_name =>p_rec.user_name
547 ,p_groupbox_id =>p_rec.groupbox_id
548 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
549 ,p_object_Version_number =>p_rec.object_version_number
550 );
551
552 -- to check valid group box id
553 ghr_prh_bus.chk_groupbox_id(p_pa_routing_history_id =>p_rec.pa_routing_history_id
554 ,p_pa_request_id =>p_rec.pa_request_id
555 ,p_groupbox_id =>p_rec.groupbox_id
556 ,p_object_version_number => p_rec.object_version_number
557 );
558
559 -- to check valid routing_list_id
560 ghr_prh_bus.chk_routing_list_id(p_routing_list_id =>p_rec.routing_list_id
561 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
562 ,p_object_Version_number =>p_rec.object_version_number
563 );
564
565 --
566 -- to check valid routing user sequence number
567 ghr_prh_bus.chk_rout_user_sequ_numb(p_pa_routing_history_id =>p_rec.pa_routing_history_id
568 ,p_routing_list_id =>p_rec.routing_list_id
569 ,p_routing_seq_number =>p_rec.routing_seq_number
570 ,p_object_version_number => p_rec.object_version_number
571 );
572 --
573
574 hr_utility.set_location(' Leaving:'||l_proc, 20);
575 End insert_validate;
576 --
577 -- ----------------------------------------------------------------------------
578 -- |---------------------------< update_validate >----------------------------|
579 -- ----------------------------------------------------------------------------
580 --Note : identify all non_updateable args and remove code where necessary
581
582 Procedure update_validate
583 (p_rec in ghr_prh_shd.g_rec_type
584 )is
585 --
586 l_proc varchar2(72) := g_package||'update_validate';
587 --
588
589 Begin
590 hr_utility.set_location('Entering:'||l_proc, 5);
591 --
592 -- call chk_non_updateable_args
593 chk_non_updateable_args (p_rec => p_rec);
594
595 -- Call all supporting business operations
596 --
597 -- hr_utility.set_location(l_proc, 10);
598
599 -- to check valid user_name
600 ghr_prh_bus.chk_user_name(p_user_name =>p_rec.user_name
601 ,p_groupbox_id =>p_rec.groupbox_id
602 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
603 ,p_object_Version_number =>p_rec.object_version_number
604 );
605
606 -- to check valid group box id
607 /* ghr_prh_bus.chk_groupbox_id(p_pa_routing_history_id =>p_rec.pa_routing_history_id
608 ,p_pa_request_id =>p_rec.pa_request_id
609 ,p_groupbox_id =>p_rec.groupbox_id
610 ,p_object_version_number => p_rec.object_version_number
611 );
612 */
613
614 hr_utility.set_location(' Leaving:'||l_proc, 60);
615 End update_validate;
616
617
618 -- ----------------------------------------------------------------------------
619 -- |---------------------------< delete_validate >----------------------------|
620 -- ----------------------------------------------------------------------------
621 Procedure delete_validate(p_rec in ghr_prh_shd.g_rec_type) is
622 --
623 l_proc varchar2(72) := g_package||'delete_validate';
624 --
625 Begin
626 hr_utility.set_location('Entering:'||l_proc, 5);
627 --
628 -- Call all supporting business operations
629 --
630 hr_utility.set_location(' Leaving:'||l_proc, 10);
631 End delete_validate;
632 --
633
634 end ghr_prh_bus;