[Home] [Help]
PACKAGE BODY: APPS.GHR_PRH_BUS
Source
1 Package Body ghr_prh_bus as
2 /* $Header: ghprhrhi.pkb 120.3.12020000.1 2012/06/29 04:35:26 appldev ship $ */
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);
73 --
70 when others then
71 raise;
72 end chk_non_updateable_args;
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
228 if ((l_api_updating and nvl(ghr_prh_shd.g_old_rec.groupbox_id,hr_api.g_number)
225 (p_pa_routing_history_id => p_pa_routing_history_id
226 ,p_object_version_number => p_object_version_number);
227 --
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 /*Start Bug:6624155 No need to check if the user exists in the group box. If user does not exist in the group box then just route the action to the users personal inbox*/
331 -- for groupbox_user in c_groupbox_user loop
332 -- l_exists := true;
333 -- exit;
334 -- end loop;
335 l_exists := true;
336 /*End Bug:6624155*/
337 --bug# 4896738
338 if not l_exists and not ghr_proc_fut_mt.g_skip_grp_box then
339 hr_utility.set_message(8301,'GHR_38103_INV_GROUPBOX_USER');
340 hr_utility.raise_error;
341 end if;
342 end if;
343 l_exists := false;
344 for c_user_name_rec in c_user_name loop
345 l_exists := true;
346 exit;
347 end loop;
348 if not l_exists then
349 hr_utility.set_message(8301,'GHR_38102_INV_USER_NAME');
350 hr_utility.raise_error;
351 end if;
352 end if;
353 end if;
354 --
355 hr_utility.set_location(' Leaving:'|| l_proc, 3);
356 end chk_user_name;
357 --
358
359
360 -- ---------------------------------------------------------------------------
361 -- |-----------------------< chk_routing_list_id >--------------------------|
362 -- ---------------------------------------------------------------------------
363 --
364 -- Description:
365 -- Validates that the routing_list_id exists in the table
366 -- ghr_routing_lists
367 --
368 -- Pre-conditions:
369 -- None
370 --
371 -- In Arguments:
372 -- p_routing_list_id
373 -- p_pa_routing_history_id
374 -- p_object_version_number
375 --
376 -- Post Success:
377 -- Processing continues
378 --
379 -- Post Failure:
380 -- An application error is raised and processing is terminated.
381 --
382 -- Access Status:
386 (p_routing_list_id in ghr_pa_routing_history.routing_list_id%TYPE
383 -- Internal Table Handler Use Only.
384 --
385 Procedure chk_routing_list_id
387 ,p_pa_routing_history_id in ghr_pa_routing_history.pa_routing_history_id%TYPE
388 ,p_object_version_number in ghr_pa_routing_history.object_version_number%TYPE
389 ) is
390 --
391 l_exists boolean := false;
392 l_proc varchar2(72) := g_package||'chk_routing_list_id';
393 l_api_updating boolean;
394 --
395 Cursor c_rout_list_id is
396 select 1
397 from ghr_routing_lists prl
398 where prl.routing_list_id = p_routing_list_id;
399 --
400 begin
401 hr_utility.set_location('Entering:'|| l_proc, 10);
402 --
403 -- Check mandatory parameters have been set
404 --
405 hr_utility.set_location(l_proc, 20);
406 -- Only proceed with validation if:
407 -- a) The current g_old_rec is current and
408 -- b) The routing status value has changed
409 -- c) a record is being inserted
410 --
411 l_api_updating := ghr_prh_shd.api_updating
412 (p_pa_routing_history_id => p_pa_routing_history_id
413 ,p_object_version_number => p_object_version_number
414 );
415 hr_utility.set_location(l_proc, 30);
416 --
417 if ((l_api_updating
418 and nvl(ghr_prh_shd.g_old_rec.routing_list_id, hr_api.g_number)
419 <> nvl(p_routing_list_id,hr_api.g_number))
420 or
421 (NOT l_api_updating))
422 then
423 hr_utility.set_location(l_proc, 40);
424 --
425 -- Check if p_routing_list_id is valid
426 --
427 if p_routing_list_id is not null then
428 for rec in c_rout_list_id loop
429 l_exists := true;
430 end loop;
431 if not l_exists then
432 ghr_prh_shd.constraint_error(p_constraint_name => 'GHR_PA_ROUTING_HIST_FK2');
433 end if;
434 end if;
435 end if;
436 --
437 hr_utility.set_location(' Leaving:'|| l_proc, 50);
438 end chk_routing_list_id;
439 --
440
441
442 -- ----------------------------------------------------------------------------
443 -- |---------------------------<chk_rout_user_sequ_numb>----------------------------|
444 -- ----------------------------------------------------------------------------
445 -- Description:
446 -- Validates that the routing_seq_number exists in the table
447 -- 'GHR_ROUTING_LIST_MEMBERS for the specific routing_list
448 --
449 -- Pre-conditions:
450 --
451 --
452 -- In Arguments:
453 --
454 -- p_pa_routing_history_id
455 -- p_routing_list_id
456 -- p_routing_seq_number
457 -- p_object_version_number
458 --
459 -- Post Success:
460 -- If the routing_seq_number is valid
461 -- processing continues
462 --
463 -- Post Failure:
464 -- An application error is raised and processing is terminated
465 --
466 -- Access Status:
467 -- Internal Table Handler Use Only.
468 --
469 procedure chk_rout_user_sequ_numb
470 (p_pa_routing_history_id in ghr_pa_routing_history.pa_routing_history_id%TYPE
471 ,p_routing_list_id in ghr_pa_routing_history.routing_list_id%TYPE
472 ,p_routing_seq_number in ghr_pa_routing_history.routing_seq_number%TYPE
473 ,p_object_version_number in ghr_pa_routing_history.object_version_number%TYPE
474 )is
475
476 --
477 l_exists boolean := FALSE;
478 l_proc varchar2(72) := g_package||'chk_routing_seq_number';
479 l_api_updating boolean;
480 --
481 Cursor c_seq_num is
482 select 1
483 from ghr_routing_list_members rlm
484 where rlm.routing_list_id = p_routing_list_id
485 and rlm.seq_number = p_routing_seq_number;
486
487 begin
488 hr_utility.set_location('Entering:'|| l_proc, 1);
489 --
490 -- Only proceed with validation if:
491 -- a) The current g_old_rec is current and
492 -- b) routing_seq_number has changed
493 -- c) A record is being inserted
494 --
495 l_api_updating := ghr_prh_shd.api_updating
496 (p_pa_routing_history_id => p_pa_routing_history_id
497 ,p_object_version_number => p_object_version_number);
498 --
499 if ((l_api_updating and nvl(ghr_prh_shd.g_old_rec.routing_seq_number,hr_api.g_number)
500 <> nvl(p_routing_seq_number,hr_api.g_number))
501 or (NOT l_api_updating)) then
502 --
503 hr_utility.set_location(l_proc, 2);
504 --
505 -- check if the routing_seq_number exists for the
506 -- routing_list_id
507 if p_routing_seq_number is not null then
508 for rec in c_seq_num loop
509 l_exists := TRUE;
510 exit;
511 end loop;
512 if not l_exists then
513 hr_utility.set_message(8301,'GHR_38104_INV_ROUT_SEQ_NUM');
514 hr_utility.raise_error;
515 end if;
516 end if;
517 end if;
518 --
519 hr_utility.set_location(' Leaving:'|| l_proc, 3);
520 end chk_rout_user_sequ_numb;
521 --
522 --
523
524 -- ----------------------------------------------------------------------------
525 -- |---------------------------< insert_validate >----------------------------|
526 -- ----------------------------------------------------------------------------
527 Procedure insert_validate
528 (p_rec in ghr_prh_shd.g_rec_type
529 )is
530 --
531 l_proc varchar2(72) := g_package||'insert_validate';
532 --
536 --
533
534 Begin
535 hr_utility.set_location('Entering:'||l_proc, 5);
537 -- Call all supporting business operations
538 --
539 -- hr_utility.set_location(l_proc, 10);
540 --
541 -- to check valid request_id
542 ghr_prh_bus.chk_pa_request_id(p_pa_request_id =>p_rec.pa_request_id
543 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
544 ,p_object_Version_number =>p_rec.object_version_number
545 );
546
547
548 -- to check valid user_name
549 ghr_prh_bus.chk_user_name(p_user_name =>p_rec.user_name
550 ,p_groupbox_id =>p_rec.groupbox_id
551 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
552 ,p_object_Version_number =>p_rec.object_version_number
553 );
554
555 -- to check valid group box id
556 ghr_prh_bus.chk_groupbox_id(p_pa_routing_history_id =>p_rec.pa_routing_history_id
557 ,p_pa_request_id =>p_rec.pa_request_id
558 ,p_groupbox_id =>p_rec.groupbox_id
559 ,p_object_version_number => p_rec.object_version_number
560 );
561
562 -- to check valid routing_list_id
563 ghr_prh_bus.chk_routing_list_id(p_routing_list_id =>p_rec.routing_list_id
564 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
565 ,p_object_Version_number =>p_rec.object_version_number
566 );
567
568 --
569 -- to check valid routing user sequence number
570 ghr_prh_bus.chk_rout_user_sequ_numb(p_pa_routing_history_id =>p_rec.pa_routing_history_id
571 ,p_routing_list_id =>p_rec.routing_list_id
572 ,p_routing_seq_number =>p_rec.routing_seq_number
573 ,p_object_version_number => p_rec.object_version_number
574 );
575 --
576
577 hr_utility.set_location(' Leaving:'||l_proc, 20);
578 End insert_validate;
579 --
580 -- ----------------------------------------------------------------------------
581 -- |---------------------------< update_validate >----------------------------|
582 -- ----------------------------------------------------------------------------
583 --Note : identify all non_updateable args and remove code where necessary
584
585 Procedure update_validate
586 (p_rec in ghr_prh_shd.g_rec_type
587 )is
588 --
589 l_proc varchar2(72) := g_package||'update_validate';
590 --
591
592 Begin
593 hr_utility.set_location('Entering:'||l_proc, 5);
594 --
595 -- call chk_non_updateable_args
596 chk_non_updateable_args (p_rec => p_rec);
597
598 -- Call all supporting business operations
599 --
600 -- hr_utility.set_location(l_proc, 10);
601
602 -- to check valid user_name
603 ghr_prh_bus.chk_user_name(p_user_name =>p_rec.user_name
604 ,p_groupbox_id =>p_rec.groupbox_id
605 ,p_pa_routing_history_id =>p_rec.pa_routing_history_id
606 ,p_object_Version_number =>p_rec.object_version_number
607 );
608
609 -- to check valid group box id
610 /* ghr_prh_bus.chk_groupbox_id(p_pa_routing_history_id =>p_rec.pa_routing_history_id
611 ,p_pa_request_id =>p_rec.pa_request_id
612 ,p_groupbox_id =>p_rec.groupbox_id
613 ,p_object_version_number => p_rec.object_version_number
614 );
615 */
616
617 hr_utility.set_location(' Leaving:'||l_proc, 60);
618 End update_validate;
619
620
621 -- ----------------------------------------------------------------------------
622 -- |---------------------------< delete_validate >----------------------------|
623 -- ----------------------------------------------------------------------------
624 Procedure delete_validate(p_rec in ghr_prh_shd.g_rec_type) is
625 --
626 l_proc varchar2(72) := g_package||'delete_validate';
627 --
628 Begin
629 hr_utility.set_location('Entering:'||l_proc, 5);
630 --
631 -- Call all supporting business operations
632 --
633 hr_utility.set_location(' Leaving:'||l_proc, 10);
634 End delete_validate;
635 --
636
637 end ghr_prh_bus;