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