[Home] [Help]
PACKAGE BODY: APPS.PER_PDM_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body per_pdm_bus as
2 /* $Header: pepdmrhi.pkb 115.8 2002/12/09 14:33:06 pkakar ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pdm_bus.'; -- Global package name
9 --
10 --
11 -- --------------------------------------------------------------------------
12 -- |---------------------< chk_non_updateable_args >------------------------|
13 -- --------------------------------------------------------------------------
14 Procedure chk_non_updateable_args
15 (p_rec in per_pdm_shd.g_rec_type
16 ,p_effective_date in date
17 ) is
18 --
19 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
20 l_error exception;
21 l_argument varchar2(30);
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 -- Only proceed with validation if a row exists for
27 -- the current record in the HR Schema
28 --
29 if not per_pdm_shd.api_updating
30 (p_delivery_method_id => p_rec.delivery_method_id
31 ,p_object_version_number => p_rec.object_version_number
32 ) then
33 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
34 hr_utility.set_message_token('PROCEDURE', l_proc);
35 hr_utility.set_message_token('STEP', '20');
36 end if;
37 hr_utility.set_location(l_proc, 30);
38 --
39 if p_rec.person_id <> per_pdm_shd.g_old_rec.person_id then
40 l_argument := 'person_id';
41 raise l_error;
42 end if;
43 hr_utility.set_location(l_proc, 50);
44 --
45 exception
46 when l_error then
47 hr_api.argument_changed_error
48 (p_api_name => l_proc
49 ,p_argument => l_argument
50 );
51 when others then
52 raise;
53 hr_utility.set_location(' Leaving:'||l_proc, 100);
54 end chk_non_updateable_args;
55 --
56 -- -----------------------------------------------------------------
57 -- |-----------------------< chk_person_id >-------------------------|
58 -- -----------------------------------------------------------------
59 --
60 -- Description:
61 -- Check that the person_id is not null and that it refers to a row on
62 -- the parent PER_ALL_PEOPLE_F table.
63 --
64 -- Pre-Requisites:
65 -- None
66 --
67 -- In Parameters:
68 -- p_person_id
69 --
70 -- Post Success:
71 -- Processing continues if the person_id is valid.
72 --
73 -- Post Failure:
74 -- An application error is raised and processing is terminated if
75 -- the person_id is invalid.
76 --
77 -- Access Status:
78 -- Internal Row Handler Use Only.
79 --
80 Procedure chk_person_id
81 (p_person_id in number,
82 p_effective_date in date
83 ) is
84 --
85 -- Local declarations
86 l_proc varchar2(72) := g_package||' chk_person_id';
87 l_person_id number;
88
89 -- Setup cursor for valid module type check
90 cursor csr_valid_person_id is
91 select person_id
92 from per_all_people_f
93 where person_id = p_person_id
94 and ((effective_start_date <= p_effective_date and
95 effective_end_date >= p_effective_date));
96 --
97 begin
98 hr_utility.set_location('Entering: '||l_proc,5);
99 --
100 --------------------------------
101 -- Check person id not null --
102 --------------------------------
103 if p_person_id is null then
104 hr_utility.set_message(801,'HR_52460_PDM_NULL_PERSON_ID');
105 hr_utility.raise_error;
106 end if;
107
108 --------------------------------
109 -- Check person id is valid --
110 --------------------------------
111 open csr_valid_person_id;
112 fetch csr_valid_person_id into l_person_id;
113 if csr_valid_person_id%notfound then
114 close csr_valid_person_id;
118
115 per_pdm_shd.constraint_error('PER_PERSON_DLVRY_METHODS_FK1');
116 end if;
117 close csr_valid_person_id;
119 hr_utility.set_location('Leaving: '||l_proc,10);
120 end chk_person_id;
121 --
122 -- ----------------------------------------------------------------------------
123 -- |--------------------------< chk_comm_dlvry_method >------------------------|
124 -- ----------------------------------------------------------------------------
125 --
126 -- Description
127 -- This procedure is used to check that the lookup value is valid.
128 --
129 -- Pre Conditions
130 -- None.
131 --
132 -- In Parameters
133 -- delivery_method_id PK of record being inserted or updated.
134 -- comm_dlvry_method Value of lookup code.
135 -- person_id Parent Row Id.
136 -- effective_date effective date
137 -- object_version_number Object version number of record being
138 -- inserted or updated.
139 --
140 -- Post Success
141 -- Processing continues
142 --
143 -- Post Failure
144 -- Errors handled by procedure. Errors raised if there exists another
145 -- row in the table with this delivery method for this person. Also,
146 -- the delivery method is validated against a lookup table.
147 --
148 -- Access Status
149 -- Internal table handler use only.
150 --
151 Procedure chk_comm_dlvry_method(p_delivery_method_id in number,
152 p_comm_dlvry_method in varchar2,
153 p_person_id in number,
154 p_effective_date in date,
155 p_object_version_number in number) is
156 --
157 l_proc varchar2(72) := g_package||'chk_comm_dlvry_method';
158 l_delivery_method_id number;
159 --
160 --
161 -- Setup cursor for combination check
162 --
163 cursor csr_check_uniqueness is
164 select delivery_method_id
165 from per_person_dlvry_methods
166 where comm_dlvry_method = p_comm_dlvry_method
167 and person_id = p_person_id;
168 --
169 Begin
170 --
171 hr_utility.set_location('Entering:'||l_proc, 5);
172 --
173 --------------------------------------
174 -- Check comm_dlvry_method not null --
175 --------------------------------------
176 --
180 end if;
177 if p_comm_dlvry_method is null then
178 hr_utility.set_message(801,'HR_52461_PDM_NULL_COMM_ID');
179 hr_utility.raise_error;
181 --
182 -----------------------------------
183 -- Check that person id is not null
184 -----------------------------------
185 --
186 hr_api.mandatory_arg_error
187 (p_api_name => l_proc
188 ,p_argument => 'person_id'
189 ,p_argument_value => p_person_id
190 );
191
192 --
193 if (p_comm_dlvry_method
194 <> nvl(per_pdm_shd.g_old_rec.comm_dlvry_method,hr_api.g_varchar2) )
195 and p_comm_dlvry_method is not null then
196 --
197 -- check if value of lookup falls within lookup type.
198 --
199 if hr_api.not_exists_in_hr_lookups
200 (p_lookup_type => 'PER_CM_MTHD',
201 p_lookup_code => p_comm_dlvry_method,
202 p_effective_date => p_effective_date) then
203 --
204 -- raise error as does not exist as lookup
205 --
206 hr_utility.set_message(801,'HR_52391_PDM_INV_DLVRY_METHOD');
207 hr_utility.raise_error;
208 --
209 end if;
210 --
211 ----------------------------------------
212 -- Check uniqueness of created record --
213 ----------------------------------------
214 --
215 open csr_check_uniqueness;
216 fetch csr_check_uniqueness into l_delivery_method_id;
217 if csr_check_uniqueness%found then
218 close csr_check_uniqueness;
219 per_pdm_shd.constraint_error('PER_PERSON_DLVRY_METHODS_UK1');
220 end if;
221 close csr_check_uniqueness;
222 --
223 --
224 end if;
225 --
226 hr_utility.set_location('Leaving:'||l_proc,10);
227 --
228 end chk_comm_dlvry_method;
229 --
233 --
230 -- ---------------------------------------------------------------------------
231 -- |----------------------------< chk_date_start_end >------------------------|
232 -- ---------------------------------------------------------------------------
234 -- Desciption :
235 --
236 -- DATE_START is mandatory
237 -- DATE_START must be less than DATE_END
238 --
239 -- Pre-conditions :
240 -- Format for date_from and date_to must be correct
241 --
242 -- In Arguments :
243 -- p_delivery_method_id
244 -- p_date_start
245 -- p_date_end
246 -- p_object_version_number
247 --
248 -- Post Success :
249 -- Processing continues
250 --
251 -- Post Failure :
252 -- An application error will be raised and processing is
253 -- terminated
254 --
255 -- Access Status :
256 -- Internal Table Handler Use only.
257 --
258 -- {End of Comments}
259 --
260 -- ---------------------------------------------------------------------------
261 procedure chk_date_start_end
262 (p_delivery_method_id in number
263 ,p_date_start in date
264 ,p_date_end in date
265 ,p_object_version_number in number
266 ) is
267 --
268 l_proc varchar2(72) := g_package||'chk_date_start';
269 --
270 begin
271 hr_utility.set_location('Entering:'||l_proc, 1);
272 --
273 -- Check mandatory parameters have been set
274 --
275 if p_date_start is null then
276 hr_utility.set_message(801,'HR_52462_PDM_NULL_DATE_START');
277 hr_utility.raise_error;
278 end if;
279 --
280 -- Only proceed with validation if :
281 -- a) The current g_old_rec is current and
282 -- b) The date_start or date_start value has changed
283 --
284 if ((nvl(per_pdm_shd.g_old_rec.date_start,hr_api.g_date)
285 <> nvl(p_date_start,hr_api.g_date)
286 or nvl(per_pdm_shd.g_old_rec.date_end,hr_api.g_date)
287 <> nvl(p_date_end,hr_api.g_date)))
288 then
289 hr_utility.set_location(l_proc, 2);
290 --
291 -- Check that the date_start value is less than or equal to the date_start
292 -- value for the current record
293 --
294 if p_date_start > nvl(p_date_end,hr_api.g_eot)then
295 hr_utility.set_message(801,'PER_7004_ALL_DATE_TO_FROM');
296 hr_utility.raise_error;
297 end if;
298 end if;
299 --
300 hr_utility.set_location(' Leaving:'||l_proc, 3);
301 --
302 end chk_date_start_end;
303 --
304 --
305 -- ----------------------------------------------------------------------------
306 -- |--------------------------< chk_preferred_flag >--------------------------|
307 -- ----------------------------------------------------------------------------
308 --
309 -- Description
310 -- This procedure is used to check that the lookup value is valid.
311 --
312 -- Pre Conditions
313 -- None.
314 --
315 -- In Parameters
316 -- delivery_method_id PK of record being inserted or updated.
317 -- preferred_flag Value of lookup code.
318 -- effective_date effective date
319 -- object_version_number Object version number of record being
320 -- inserted or updated.
321 --
322 -- Post Success
323 -- Processing continues
324 --
325 -- Post Failure
326 -- Error is raised.
327 --
328 -- Access Status
329 -- Internal table handler use only.
330 --
331 Procedure chk_preferred_flag(p_delivery_method_id in number,
332 p_preferred_flag in varchar2,
333 p_person_id in number,
334 p_effective_date in date,
335 p_object_version_number in number) is
336 --
337 l_proc varchar2(72) := g_package||'chk_preferred_flag';
338 l_delivery_method_id number;
339 --
340 --
341 -- Setup cursor for combination check
342 --
343 cursor csr_check_uniqueness is
344 select delivery_method_id
345 from per_person_dlvry_methods
346 where preferred_flag = 'Y'
347 and person_id = p_person_id;
348 --
349 Begin
350 --
351 hr_utility.set_location('Entering:'||l_proc, 5);
352 --
353 if (nvl(p_preferred_flag, hr_api.g_varchar2)
354 <> nvl(per_pdm_shd.g_old_rec.preferred_flag,hr_api.g_varchar2))
355 and p_preferred_flag is not null then
356 --
357 -- check if value of lookup falls within lookup type.
358 --
359 if hr_api.not_exists_in_hr_lookups
360 (p_lookup_type => 'YES_NO',
364 -- raise error as does not exist as lookup
361 p_lookup_code => p_preferred_flag,
362 p_effective_date => p_effective_date) then
363 --
365 --
366 hr_utility.set_message(801,'HR_52393_PDM_INV_PREF_FLAG');
367 hr_utility.raise_error;
368 --
369 end if;
370 --
371 -- If Value is being set to 'Y' then check that this is the only
372 -- row that has preferred flag set to Y.
373 --
374 if p_preferred_flag = 'Y' then
375 --
376 open csr_check_uniqueness;
377 fetch csr_check_uniqueness into l_delivery_method_id;
378 if csr_check_uniqueness%found then
379 close csr_check_uniqueness;
380 hr_utility.set_message(801, 'HR_52394_PDM_DUP_PREF_FLAG');
381 hr_utility.raise_error;
382 end if;
383 close csr_check_uniqueness;
384 --
385 end if;
386 --
387 end if;
388 --
389 hr_utility.set_location('Leaving:'||l_proc,10);
390 --
391 end chk_preferred_flag;
392 --
393 -- -----------------------------------------------------------------------
394 -- |------------------------------< chk_df >-----------------------------|
395 -- -----------------------------------------------------------------------
396 --
397 -- Description:
398 -- Validates the all Descriptive Flexfield values.
399 --
400 -- Pre-conditions:
401 -- All other columns have been validated. Must be called as the
402 -- last step from insert_validate and update_validate.
403 --
404 -- In Arguments:
405 -- p_rec
406 --
407 -- Post Success:
408 -- If the Descriptive Flexfield structure column and data values are
409 -- all valid this procedure will end normally and processing will
410 -- continue.
411 --
412 -- Post Failure:
413 -- If the Descriptive Flexfield structure column value or any of
414 -- the data values are invalid then an application error is raised as
415 -- a PL/SQL exception.
416 --
417 -- Access Status:
418 -- Internal Row Handler Use Only.
419 --
420 procedure chk_df
421 (p_rec in per_pdm_shd.g_rec_type) is
422 --
423 l_proc varchar2(72) := g_package||'chk_df';
424 --
425 begin
426 hr_utility.set_location('Entering:'||l_proc, 10);
427 --
428 if ((p_rec.delivery_method_id is not null) and (
429 nvl(per_pdm_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
430 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
431 nvl(per_pdm_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
432 nvl(p_rec.attribute1, hr_api.g_varchar2) or
433 nvl(per_pdm_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
434 nvl(p_rec.attribute2, hr_api.g_varchar2) or
435 nvl(per_pdm_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
436 nvl(p_rec.attribute3, hr_api.g_varchar2) or
437 nvl(per_pdm_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
438 nvl(p_rec.attribute4, hr_api.g_varchar2) or
439 nvl(per_pdm_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
440 nvl(p_rec.attribute5, hr_api.g_varchar2) or
441 nvl(per_pdm_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
442 nvl(p_rec.attribute6, hr_api.g_varchar2) or
443 nvl(per_pdm_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
444 nvl(p_rec.attribute7, hr_api.g_varchar2) or
445 nvl(per_pdm_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
446 nvl(p_rec.attribute8, hr_api.g_varchar2) or
447 nvl(per_pdm_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
448 nvl(p_rec.attribute9, hr_api.g_varchar2) or
449 nvl(per_pdm_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
450 nvl(p_rec.attribute10, hr_api.g_varchar2) or
451 nvl(per_pdm_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
452 nvl(p_rec.attribute11, hr_api.g_varchar2) or
453 nvl(per_pdm_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
454 nvl(p_rec.attribute12, hr_api.g_varchar2) or
455 nvl(per_pdm_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
456 nvl(p_rec.attribute13, hr_api.g_varchar2) or
457 nvl(per_pdm_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
458 nvl(p_rec.attribute14, hr_api.g_varchar2) or
459 nvl(per_pdm_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
460 nvl(p_rec.attribute15, hr_api.g_varchar2) or
461 nvl(per_pdm_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
462 nvl(p_rec.attribute16, hr_api.g_varchar2) or
463 nvl(per_pdm_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
464 nvl(p_rec.attribute17, hr_api.g_varchar2) or
465 nvl(per_pdm_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
466 nvl(p_rec.attribute18, hr_api.g_varchar2) or
467 nvl(per_pdm_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
468 nvl(p_rec.attribute19, hr_api.g_varchar2) or
469 nvl(per_pdm_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
470 nvl(p_rec.attribute20, hr_api.g_varchar2)))
471 or
472 (p_rec.delivery_method_id is null) then
473 --
474 -- Only execute the validation if absolutely necessary:
475 -- a) During update, the structure column value or any
476 -- of the attribute values have actually changed.
477 -- b) During insert.
478 --
479 hr_dflex_utility.ins_or_upd_descflex_attribs
480 (p_appl_short_name => 'PER'
481 ,p_descflex_name => 'PER_PERSON_DLVRY_METHODS'
482 ,p_attribute_category => p_rec.attribute_category
483 ,p_attribute1_name => 'ATTRIBUTE1'
484 ,p_attribute1_value => p_rec.attribute1
485 ,p_attribute2_name => 'ATTRIBUTE2'
486 ,p_attribute2_value => p_rec.attribute2
487 ,p_attribute3_name => 'ATTRIBUTE3'
488 ,p_attribute3_value => p_rec.attribute3
489 ,p_attribute4_name => 'ATTRIBUTE4'
490 ,p_attribute4_value => p_rec.attribute4
491 ,p_attribute5_name => 'ATTRIBUTE5'
492 ,p_attribute5_value => p_rec.attribute5
493 ,p_attribute6_name => 'ATTRIBUTE6'
494 ,p_attribute6_value => p_rec.attribute6
495 ,p_attribute7_name => 'ATTRIBUTE7'
496 ,p_attribute7_value => p_rec.attribute7
497 ,p_attribute8_name => 'ATTRIBUTE8'
498 ,p_attribute8_value => p_rec.attribute8
499 ,p_attribute9_name => 'ATTRIBUTE9'
500 ,p_attribute9_value => p_rec.attribute9
501 ,p_attribute10_name => 'ATTRIBUTE10'
502 ,p_attribute10_value => p_rec.attribute10
503 ,p_attribute11_name => 'ATTRIBUTE11'
504 ,p_attribute11_value => p_rec.attribute11
505 ,p_attribute12_name => 'ATTRIBUTE12'
506 ,p_attribute12_value => p_rec.attribute12
507 ,p_attribute13_name => 'ATTRIBUTE13'
511 ,p_attribute15_name => 'ATTRIBUTE15'
508 ,p_attribute13_value => p_rec.attribute13
509 ,p_attribute14_name => 'ATTRIBUTE14'
510 ,p_attribute14_value => p_rec.attribute14
512 ,p_attribute15_value => p_rec.attribute15
513 ,p_attribute16_name => 'ATTRIBUTE16'
514 ,p_attribute16_value => p_rec.attribute16
515 ,p_attribute17_name => 'ATTRIBUTE17'
516 ,p_attribute17_value => p_rec.attribute17
517 ,p_attribute18_name => 'ATTRIBUTE18'
518 ,p_attribute18_value => p_rec.attribute18
519 ,p_attribute19_name => 'ATTRIBUTE19'
520 ,p_attribute19_value => p_rec.attribute19
521 ,p_attribute20_name => 'ATTRIBUTE20'
522 ,p_attribute20_value => p_rec.attribute20
523 );
524 end if;
525 --
526 hr_utility.set_location(' Leaving:'||l_proc, 20);
527 end chk_df;
528 --
529 -- ----------------------------------------------------------------------------
530 -- |---------------------------< insert_validate >----------------------------|
531 -- ----------------------------------------------------------------------------
532 Procedure insert_validate(p_rec in per_pdm_shd.g_rec_type
533 ,p_effective_date in date) is
534 --
535 l_proc varchar2(72) := g_package||'insert_validate';
536 --
537 Begin
538 hr_utility.set_location('Entering:'||l_proc, 5);
539 --
540 -- Call all supporting business operations
541 --
542 chk_person_id
543 (p_person_id => p_rec.person_id,
544 p_effective_date => p_effective_date);
545 --
546 chk_comm_dlvry_method
547 (p_delivery_method_id => p_rec.delivery_method_id,
548 p_comm_dlvry_method => p_rec.comm_dlvry_method,
549 p_person_id => p_rec.person_id,
550 p_effective_date => p_effective_date,
551 p_object_version_number => p_rec.object_version_number);
552 --
553 if not per_pdm_bus.g_called_from_form then
554 chk_preferred_flag
555 (p_delivery_method_id => p_rec.delivery_method_id,
556 p_person_id => p_rec.person_id,
557 p_preferred_flag => p_rec.preferred_flag,
558 p_effective_date => p_effective_date,
559 p_object_version_number => p_rec.object_version_number);
560 end if;
561 --
562 chk_date_start_end
563 (p_delivery_method_id => p_rec.delivery_method_id
564 ,p_date_start => p_rec.date_start
565 ,p_date_end => p_rec.date_end
566 ,p_object_version_number => p_rec.object_version_number);
567 --
568 -- Call descriptive flexfield validation routines
569 --
570 per_pdm_bus.chk_df(p_rec => p_rec);
571 --
572 hr_utility.set_location(' Leaving:'||l_proc, 10);
573 End insert_validate;
574 --
575 -- ----------------------------------------------------------------------------
576 -- |---------------------------< update_validate >----------------------------|
577 -- ----------------------------------------------------------------------------
578 Procedure update_validate(p_rec in per_pdm_shd.g_rec_type
579 ,p_effective_date in date) is
580 --
581 l_proc varchar2(72) := g_package||'update_validate';
582 --
583 Begin
584 hr_utility.set_location('Entering:'||l_proc, 5);
585 --
586 -- Call all supporting business operations
587 --
588 chk_non_updateable_args
589 (p_rec => p_rec
590 ,p_effective_date => p_effective_date);
591 --
592 chk_comm_dlvry_method
593 (p_delivery_method_id => p_rec.delivery_method_id,
594 p_comm_dlvry_method => p_rec.comm_dlvry_method,
595 p_person_id => p_rec.person_id,
596 p_effective_date => p_effective_date,
597 p_object_version_number => p_rec.object_version_number);
598 --
599 -- No need to call this chk routine if calling from form as only
600 -- one check box can ever be checked.
601 --
602 if not per_pdm_bus.g_called_from_form then
603 chk_preferred_flag
604 (p_delivery_method_id => p_rec.delivery_method_id,
605 p_person_id => p_rec.person_id,
606 p_preferred_flag => p_rec.preferred_flag,
607 p_effective_date => p_effective_date,
608 p_object_version_number => p_rec.object_version_number);
609 end if;
610 --
611 chk_date_start_end
612 (p_delivery_method_id => p_rec.delivery_method_id
613 ,p_date_start => p_rec.date_start
614 ,p_date_end => p_rec.date_end
615 ,p_object_version_number => p_rec.object_version_number);
616 --
617 -- Call descriptive flexfield validation routines
618 --
619 per_pdm_bus.chk_df(p_rec => p_rec);
620 --
621 --
622 hr_utility.set_location(' Leaving:'||l_proc, 10);
623 End update_validate;
624 --
625 -- ----------------------------------------------------------------------------
626 -- |---------------------------< delete_validate >----------------------------|
627 -- ----------------------------------------------------------------------------
628 Procedure delete_validate(p_rec in per_pdm_shd.g_rec_type) is
629 --
630 l_proc varchar2(72) := g_package||'delete_validate';
631 --
632 Begin
633 hr_utility.set_location('Entering:'||l_proc, 5);
634 --
635 -- Call all supporting business operations
639 --
636 --
637 hr_utility.set_location(' Leaving:'||l_proc, 10);
638 End delete_validate;
640 --
641 -- ---------------------------------------------------------------------------
642 -- |---------------------< return_legislation_code >-------------------------|
643 -- ---------------------------------------------------------------------------
644 --
645 function return_legislation_code
646 (p_delivery_method_id in number
647 ) return varchar2 is
648 --
649 -- Cursor to find legislation code
650 --
651 cursor csr_leg_code is
652 select pbg.legislation_code
653 from per_business_groups pbg
654 , per_person_dlvry_methods pdm
655 , per_all_people_f per
656 where pdm.delivery_method_id = p_delivery_method_id
657 and pdm.person_id = per.person_id
658 and pbg.business_group_id = per.business_group_id;
659 --
660 -- Declare local variables
661 --
662 l_legislation_code varchar2(150);
663 l_proc varchar2(72) := 'return_legislation_code';
664 begin
665 hr_utility.set_location('Entering:'|| l_proc, 10);
666 --
667 -- Ensure that all the mandatory parameter are not null
668 --
669 hr_api.mandatory_arg_error(p_api_name => l_proc,
670 p_argument => 'delivery_method_id',
671 p_argument_value => p_delivery_method_id);
672 --
673 if nvl(g_delivery_method_id, hr_api.g_number) = p_delivery_method_id then
674 --
675 -- The legislation code has already been found with a previous
676 -- call to this function. Just return the value in the global
677 -- variable.
678 --
679 l_legislation_code := g_legislation_code;
680 hr_utility.set_location(l_proc, 20);
681 else
682 --
683 -- The ID is different to the last call to this function
684 -- or this is the first call to this function.
685 --
686 open csr_leg_code;
687 fetch csr_leg_code into l_legislation_code;
688 if csr_leg_code%notfound then
689 --
690 -- The primary key is invalid therefore we must error
691 --
692 close csr_leg_code;
693 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
694 hr_utility.raise_error;
695 end if;
696 hr_utility.set_location(l_proc, 30);
697 --
698 -- Set the global variables so the values are
699 -- available for the next call to this function
700 --
701 close csr_leg_code;
702 g_delivery_method_id := p_delivery_method_id;
703 g_legislation_code := l_legislation_code;
704 end if;
705 hr_utility.set_location(' Leaving:'|| l_proc, 40);
706 --
707 return l_legislation_code;
708 end return_legislation_code;
709 --
710 -- ----------------------------------------------------------------------------
711 -- |--------------------------< set_called_from_form >------------------------|
712 -- ----------------------------------------------------------------------------
713 procedure set_called_from_form
714 ( p_flag in boolean ) as
715 begin
716 g_called_from_form := p_flag;
717 end;
718 --
719 end per_pdm_bus;