[Home] [Help]
PACKAGE BODY: APPS.HR_TIM_BUS
Source
1 Package Body hr_tim_bus as
2 /* $Header: hrtimrhi.pkb 115.10 2003/10/29 02:53:14 jpthomas noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_tim_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_template_item_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_template_item_id in number
22 ) is
23 --
24 l_proc varchar2(72) := g_package||'set_security_group_id';
25 --
26 begin
27 --
28 hr_utility.set_location('Entering:'|| l_proc, 10);
29 --
30 -- No business group context. Security group is not applicable.
31 --
32 null;
33 --
34 hr_utility.set_location(' Leaving:'|| l_proc, 20);
35 --
36 end set_security_group_id;
37 --
38 -- ---------------------------------------------------------------------------
39 -- |---------------------< return_legislation_code >-------------------------|
40 -- ---------------------------------------------------------------------------
41 --
42 Function return_legislation_code
43 (p_template_item_id in number
44 )
45 Return Varchar2 Is
46 --
47 -- Declare cursor
48 --
49 cursor csr_leg_code is
50 select tmp.legislation_code
51 from hr_form_templates_b tmp
52 ,hr_template_items_b tim
53 where tmp.form_template_id = tim.form_template_id
54 and tim.template_item_id = p_template_item_id;
55 --
56 -- Declare local variables
57 --
58 l_legislation_code varchar2(150);
59 l_proc varchar2(72) := g_package||'return_legislation_code';
60 --
61 Begin
62 --
63 hr_utility.set_location('Entering:'|| l_proc, 10);
64 --
65 -- Ensure that all the mandatory parameter are not null
66 --
67 hr_api.mandatory_arg_error
68 (p_api_name => l_proc
69 ,p_argument => 'template_item_id'
70 ,p_argument_value => p_template_item_id
71 );
72 --
73 if ( nvl(hr_tim_bus.g_template_item_id, hr_api.g_number)
74 = p_template_item_id) then
75 --
76 -- The legislation code has already been found with a previous
77 -- call to this function. Just return the value in the global
78 -- variable.
79 --
80 l_legislation_code := hr_tim_bus.g_legislation_code;
81 hr_utility.set_location(l_proc, 20);
82 else
83 --
84 -- The ID is different to the last call to this function
85 -- or this is the first call to this function.
86 --
87 open csr_leg_code;
88 fetch csr_leg_code into l_legislation_code;
89 --
90 if csr_leg_code%notfound then
91 --
92 -- The primary key is invalid therefore we must error
93 --
94 close csr_leg_code;
95 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
96 fnd_message.raise_error;
97 end if;
98 hr_utility.set_location(l_proc,30);
99 --
100 -- Set the global variables so the values are
101 -- available for the next call to this function.
102 --
103 close csr_leg_code;
104 hr_tim_bus.g_template_item_id := p_template_item_id;
105 hr_tim_bus.g_legislation_code := l_legislation_code;
106 end if;
107 hr_utility.set_location(' Leaving:'|| l_proc, 40);
108 return l_legislation_code;
109 end return_legislation_code;
110 --
111 -- ----------------------------------------------------------------------------
112 -- |-----------------------< chk_non_updateable_args >------------------------|
113 -- ----------------------------------------------------------------------------
114 -- {Start Of Comments}
115 --
116 -- Description:
117 -- This procedure is used to ensure that non updateable attributes have
118 -- not been updated. If an attribute has been updated an error is generated.
119 --
120 -- Pre Conditions:
121 -- g_old_rec has been populated with details of the values currently in
122 -- the database.
123 --
124 -- In Arguments:
125 -- p_rec has been populated with the updated values the user would like the
126 -- record set to.
127 --
128 -- Post Success:
129 -- Processing continues if all the non updateable attributes have not
130 -- changed.
131 --
132 -- Post Failure:
133 -- An application error is raised if any of the non updatable attributes
134 -- have been altered.
135 --
136 -- {End Of Comments}
137 -- ----------------------------------------------------------------------------
138 Procedure chk_non_updateable_args
139 (p_rec in hr_tim_shd.g_rec_type
140 ) IS
141 --
142 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
143 l_error EXCEPTION;
144 l_argument varchar2(30);
145 --
146 Begin
147 --
148 -- Only proceed with the validation if a row exists for the current
149 -- record in the HR Schema.
150 --
151 IF NOT hr_tim_shd.api_updating
152 (p_template_item_id => p_rec.template_item_id
153 ,p_object_version_number => p_rec.object_version_number
154 ) THEN
155 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
156 fnd_message.set_token('PROCEDURE ', l_proc);
157 fnd_message.set_token('STEP ', '5');
158 fnd_message.raise_error;
159 END IF;
160 --
161 IF (nvl(p_rec.form_item_id,hr_api.g_number) <>
162 nvl(hr_tim_shd.g_old_rec.form_item_id,hr_api.g_number)
163 ) THEN
164 l_argument := 'form_item_id';
165 RAISE l_error;
166 END IF;
167 --
168 IF (nvl(p_rec.form_template_id,hr_api.g_number) <>
169 nvl(hr_tim_shd.g_old_rec.form_template_id,hr_api.g_number)
170 ) THEN
171 l_argument := 'form_template_id';
172 RAISE l_error;
173 END IF;
174 --
175 EXCEPTION
176 WHEN l_error THEN
177 hr_api.argument_changed_error
178 (p_api_name => l_proc
179 ,p_argument => l_argument);
180 WHEN OTHERS THEN
181 RAISE;
182 End chk_non_updateable_args;
183 --
184 -- ----------------------------------------------------------------------------
185 -- |---------------------------< chk_form_item_id >---------------------------|
186 -- ----------------------------------------------------------------------------
187 Procedure chk_form_item_id
188 (p_template_item_id in number
189 ,p_object_version_number in number
190 ,p_form_item_id in number
191 ) is
192 --
193 l_proc varchar2(72) := g_package || 'chk_form_item_id';
194 l_api_updating boolean;
195 --
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 10);
198 --
199 -- Check value has been passed
200 --
201 hr_api.mandatory_arg_error
202 (p_api_name => l_proc
203 ,p_argument => 'form_item_id'
204 ,p_argument_value => p_form_item_id
205 );
206 --
207 hr_utility.set_location('Leaving:'||l_proc, 100);
208 End chk_form_item_id;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |-------------------------< chk_form_template_id >-------------------------|
212 -- ----------------------------------------------------------------------------
213 Procedure chk_form_template_id
214 (p_template_item_id in number
215 ,p_object_version_number in number
216 ,p_form_template_id in number
217 ) is
218 --
219 l_proc varchar2(72) := g_package || 'chk_form_template_id';
220 l_api_updating boolean;
221 --
222 Begin
223 hr_utility.set_location('Entering:'||l_proc, 10);
224 --
225 -- Check value has been passed
226 --
227 hr_api.mandatory_arg_error
228 (p_api_name => l_proc
229 ,p_argument => 'form_template_id'
230 ,p_argument_value => p_form_template_id
231 );
232 --
233 hr_utility.set_location('Leaving:'||l_proc, 100);
234 End chk_form_template_id;
235 --
236 -- ----------------------------------------------------------------------------
237 -- |------------------------< chk_item_and_template >-------------------------|
238 -- ----------------------------------------------------------------------------
239 Procedure chk_item_and_template
240 (p_template_item_id in number
241 ,p_object_version_number in number
242 ,p_form_item_id in number
243 ,p_form_template_id in number
244 ) is
245 --
246 cursor csr_form_item is
247 select fim.application_id
248 ,fim.form_id
249 from hr_form_items_b fim
250 where fim.form_item_id = p_form_item_id;
251 --
252 cursor csr_form_template is
253 select tmp.application_id
254 ,tmp.form_id
255 from hr_form_templates_b tmp
256 where tmp.form_template_id = p_form_template_id;
257 --
258 l_proc varchar2(72) := g_package || 'chk_item_and_template';
259 l_api_updating boolean;
260 l_item_application_id number;
261 l_item_form_id number;
262 l_template_application_id number;
263 l_template_form_id number;
264 --
265 Begin
266 hr_utility.set_location('Entering:'||l_proc, 10);
267 --
268 l_api_updating := hr_tim_shd.api_updating
269 (p_template_item_id => p_template_item_id
270 ,p_object_version_number => p_object_version_number
271 );
272 hr_utility.set_location(l_proc,20);
273 --
274 -- Only proceed with SQL validation if absolutely necessary
275 --
276 if ( ( l_api_updating
277 and ( nvl(hr_tim_shd.g_old_rec.form_item_id,hr_api.g_number) <>
278 nvl(p_form_item_id,hr_api.g_number)
279 or nvl(hr_tim_shd.g_old_rec.form_template_id,hr_api.g_number) <>
280 nvl(p_form_template_id,hr_api.g_number)))
281 or (NOT l_api_updating)) then
282 --
283 hr_utility.set_location(l_proc,30);
284 --
285 -- Check item and template reference the same form
286 --
287 open csr_form_item;
288 fetch csr_form_item into l_item_application_id, l_item_form_id;
289 if csr_form_item%notfound then
290 close csr_form_item;
291 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
292 fnd_message.set_token('PROCEDURE', l_proc);
293 fnd_message.set_token('STEP','10');
294 fnd_message.raise_error;
295 end if;
296 close csr_form_item;
297 --
298 open csr_form_template;
299 fetch csr_form_template into l_template_application_id, l_template_form_id;
300 if csr_form_template%notfound then
301 close csr_form_template;
302 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
303 fnd_message.set_token('PROCEDURE', l_proc);
304 fnd_message.set_token('STEP','20');
305 fnd_message.raise_error;
306 end if;
307 close csr_form_template;
308 --
309 hr_utility.set_location(l_proc,40);
310 --
311 if nvl(l_item_application_id,hr_api.g_number) <>
312 nvl(l_template_application_id,hr_api.g_number)
313 or nvl(l_item_form_id,hr_api.g_number) <>
314 nvl(l_template_form_id,hr_api.g_number) then
315 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
316 fnd_message.set_token('PROCEDURE', l_proc);
317 fnd_message.set_token('STEP','30');
318 fnd_message.raise_error;
319 end if;
320 --
321 end if;
322 --
323 hr_utility.set_location('Leaving:'||l_proc, 100);
324 End chk_item_and_template;
325 -- ----------------------------------------------------------------------------
326 -- |--------------------------< chk_no_flex_segment_comb >--------------------|
327 -- ----------------------------------------------------------------------------
328 Procedure chk_no_flex_segment_comb
329 (p_form_template_id in number
330 ,p_object_version_number in number
331 ,p_form_item_id in number
332 ) is
333 --
334 cursor csr_derive_item_name is
335 select fim.full_item_name
336 from hr_form_items_b fim
337 where fim.form_item_id = p_form_item_id;
338 --
339 cursor csr_flex_on_template(p_block varchar2, p_flex_name varchar2) is
340 select 1
341 from hr_template_items_b tim, hr_form_items_b fim
342 where tim.form_item_id = fim.form_item_id
343 and tim.form_template_id = p_form_template_id
344 and fim.full_item_name = p_block||'.'||p_flex_name;
345 --
346 cursor csr_segment_on_template(p_block varchar2, p_segment varchar2) is
347 select 1
348 from hr_template_items_b tim, hr_form_items_b fim
349 where tim.form_item_id = fim.form_item_id
350 and tim.form_template_id = p_form_template_id
351 and fim.full_item_name like p_block||'.'||p_segment||'%';
352 --
353 --
354 -- Bug 3163360 Start here
355 -- Description : Modified the cursor to exclude the no adrress informations such as
356 -- 'COUNTRY_OF_BIRTH','REGION_OF_BIRTH', 'COUNTRYn_MEANING'
357 --
358 cursor csr_address_segs_exist(p_block varchar2) is
359 select 1
360 from hr_template_items_b tim, hr_form_items_b fim
361 where tim.form_item_id = fim.form_item_id
362 and tim.form_template_id = p_form_template_id
363 and ((fim.full_item_name like p_block||'.ADDRESS_LINE%'
364 or fim.full_item_name like p_block||'.REGION_%'
365 or fim.full_item_name like p_block||'.POSTAL_CODE%'
366 or fim.full_item_name like p_block||'.TOWN_OR_CITY%'
367 or fim.full_item_name like p_block||'.COUNTRY%'
368 or fim.full_item_name like p_block||'.TELEPHONE_NUMBER_%'
369 or fim.full_item_name like p_block||'.ADD_INFORMATION%')
370 and fim.full_item_name not like p_block||'.%OF_BIRTH%'
371 and fim.full_item_name not like p_block||'.COUNTRY%_MEANING');
372
373 --
374 --Bug 3163360 End here
375 --
376 l_dummy1 varchar2(80);
377 l_dummy2 varchar2(80);
378 l_block varchar2(30);
379 l_segment varchar2(80);
380 l_flex_name varchar2(20);
381 l_form_item_name varchar2(80);
382 --
383 l_proc varchar2(72) := g_package || 'chk_no_flex_segment_comb';
387 hr_utility.set_location('Entering:'||l_proc, 10);
384 l_api_updating boolean;
385 --
386 Begin
388 --
389 -- Check value has been passed
390 --
391 hr_api.mandatory_arg_error
392 (p_api_name => l_proc
393 ,p_argument => 'form_item_id'
394 ,p_argument_value => p_form_item_id
395 );
396 --
397 -- Check to omit validation when not adding a flex popup or a segment
398 --
399 open csr_derive_item_name;
400 fetch csr_derive_item_name into l_form_item_name;
401 close csr_derive_item_name;
402 if not ( l_form_item_name like '%_SEGMENT%'
403 or l_form_item_name like '%_ATTRIBUTE%'
404 or l_form_item_name like '%_INFORMATION%'
405 or l_form_item_name like '%ADDRESS_LINE%'
406 or l_form_item_name like '%REGION_%'
407 or l_form_item_name like '%POSTAL_CODE%'
408 or l_form_item_name like '%TOWN_OR_CITY%'
409 or l_form_item_name like '%COUNTRY%'
410 or l_form_item_name like '%TELEPHONE_NUMBER_%'
411 or l_form_item_name like '%ADD_INFORMATION%'
412 or l_form_item_name like '%_DF'
413 or l_form_item_name like '%_KF'
414 ) then
415 null;
416 else
417 --
418 hr_utility.set_location(l_proc,20);
419 --
420 for j in 1..3 loop -- loop around the blocks
421 <<block>>
422 if j=1 then
423 l_block := 'MAINTAIN';
424 elsif j=2 then
425 l_block := 'SUMMARY';
426 elsif j=3 then
427 l_block := 'FIND_FOLDER';
428 end if;
429 --
430 --
431 -- Address Structure columns have differing names so require special treatment first
432 --
433 l_segment := 'ADD_INFORMATION';
434 l_flex_name := 'ADDR_DF';
435 open csr_flex_on_template(l_block, l_flex_name);
436 fetch csr_flex_on_template into l_dummy1;
437 if csr_flex_on_template%found then
438 close csr_flex_on_template;
439 open csr_address_segs_exist(l_block);
440 fetch csr_address_segs_exist into l_dummy2;
441 if csr_address_segs_exist%found then
442 close csr_address_segs_exist;
443 fnd_message.set_name('PER','PER_289203_INV_FLEX_SEG_COMB');
444 fnd_message.set_token('FLEX_SEG',l_block||'.'||l_segment);
445 fnd_message.raise_error;
446 else
447 close csr_address_segs_exist;
448 end if;
449 else
450 close csr_flex_on_template;
451 end if;
452 --
453 for i in 1..9 loop -- Now loop around the other flexfield items
454 <<prefix>>
455 if i = 1 then
456 l_segment := 'PER_INFORMATION';
457 l_flex_name := 'PER_DF';
458 elsif i = 2 then
459 l_segment := 'PER_ATTRIBUTE';
460 l_flex_name := 'PER_DETAILS_DF';
461 elsif i = 3 then
462 l_segment := 'ASS_ATTRIBUTE';
463 l_flex_name := 'ASS_DF';
464 elsif i = 4 then
465 l_segment := 'APPL_ATTRIBUTE';
466 l_flex_name := 'APPL_DF';
467 elsif i = 5 then
468 l_segment := 'ADDR_ATTRIBUTE';
469 l_flex_name := 'ADDR_DETAILS_DF';
470 elsif i = 6 then
471 l_segment := 'PYP_ATTRIBUTE';
472 l_flex_name := 'PYP_DF';
473 elsif i = 7 then
474 l_segment := 'DPF_ATTRIBUTE';
475 l_flex_name := 'DPF_DF';
476 elsif i = 8 then
477 l_segment := 'PGP_SEGMENT';
478 l_flex_name := 'PGP_KF';
479 elsif i = 9 then
480 l_segment := 'SCL_SEGMENT';
481 l_flex_name := 'SCL_KF';
482 end if;
483 --
484 open csr_flex_on_template(l_block, l_flex_name);
485 fetch csr_flex_on_template into l_dummy1;
486 if csr_flex_on_template%found then
487 close csr_flex_on_template;
488 open csr_segment_on_template(l_block, l_segment);
489 fetch csr_segment_on_template into l_dummy2;
490 if csr_segment_on_template%found then
491 close csr_segment_on_template;
492 fnd_message.set_name('PER','PER_289203_INV_FLEX_SEG_COMB');
493 fnd_message.set_token('FLEX_SEG',l_block||'.'||l_segment);
494 fnd_message.raise_error;
495 else
496 close csr_segment_on_template;
497 end if;
498 else
499 close csr_flex_on_template;
500 end if;
501 --
502 end loop prefix;
503 --
504 end loop block;
505 --
506 end if;
507 hr_utility.set_location('Leaving:'||l_proc, 100);
508 end chk_no_flex_segment_comb;
509 --
510 -- ----------------------------------------------------------------------------
511 -- |------------------------------< chk_delete >------------------------------|
512 -- ----------------------------------------------------------------------------
513 Procedure chk_delete
514 (p_rec in hr_tim_shd.g_rec_type
515 ) is
516 --
517 l_proc varchar2(72) := g_package||'chk_delete';
518 --
519 Begin
520 hr_utility.set_location('Entering:'||l_proc, 5);
521 --
525 --
522 -- No additional validation required
523 --
524 null;
526 hr_utility.set_location(' Leaving:'||l_proc, 10);
527 End chk_delete;
528 --
529 -- ----------------------------------------------------------------------------
530 -- |---------------------------< insert_validate >----------------------------|
531 -- ----------------------------------------------------------------------------
532 Procedure insert_validate
533 (p_rec in hr_tim_shd.g_rec_type
534 ) is
535 --
536 l_proc varchar2(72) := g_package||'insert_validate';
537 --
538 Begin
539 hr_utility.set_location('Entering:'||l_proc, 5);
540 --
541 -- Call all supporting business operations
542 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
543 --
544 chk_form_item_id
545 (p_template_item_id => p_rec.template_item_id
546 ,p_object_version_number => p_rec.object_version_number
547 ,p_form_item_id => p_rec.form_item_id
548 );
549 --
550 chk_form_template_id
551 (p_template_item_id => p_rec.template_item_id
552 ,p_object_version_number => p_rec.object_version_number
553 ,p_form_template_id => p_rec.form_template_id
554 );
555 --
556 chk_item_and_template
557 (p_template_item_id => p_rec.template_item_id
558 ,p_object_version_number => p_rec.object_version_number
559 ,p_form_item_id => p_rec.form_item_id
560 ,p_form_template_id => p_rec.form_template_id
561 );
562 --
563 hr_utility.set_location(' Leaving:'||l_proc, 10);
564 End insert_validate;
565 --
566 -- ----------------------------------------------------------------------------
567 -- |---------------------------< update_validate >----------------------------|
568 -- ----------------------------------------------------------------------------
569 Procedure update_validate
570 (p_rec in hr_tim_shd.g_rec_type
571 ) is
572 --
573 l_proc varchar2(72) := g_package||'update_validate';
574 --
575 Begin
576 hr_utility.set_location('Entering:'||l_proc, 5);
577 --
578 -- Call all supporting business operations
579 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
580 --
581 chk_non_updateable_args
582 (p_rec => p_rec
583 );
584 --
585 chk_form_item_id
586 (p_template_item_id => p_rec.template_item_id
587 ,p_object_version_number => p_rec.object_version_number
588 ,p_form_item_id => p_rec.form_item_id
589 );
590 --
591 chk_form_template_id
592 (p_template_item_id => p_rec.template_item_id
593 ,p_object_version_number => p_rec.object_version_number
594 ,p_form_template_id => p_rec.form_template_id
595 );
596 --
597 chk_item_and_template
598 (p_template_item_id => p_rec.template_item_id
599 ,p_object_version_number => p_rec.object_version_number
600 ,p_form_item_id => p_rec.form_item_id
601 ,p_form_template_id => p_rec.form_template_id
602 );
603 --
604 hr_utility.set_location(' Leaving:'||l_proc, 10);
605 End update_validate;
606 --
607 -- ----------------------------------------------------------------------------
608 -- |---------------------------< delete_validate >----------------------------|
609 -- ----------------------------------------------------------------------------
610 Procedure delete_validate
611 (p_rec in hr_tim_shd.g_rec_type
612 ) is
613 --
614 l_proc varchar2(72) := g_package||'delete_validate';
615 --
616 Begin
617 hr_utility.set_location('Entering:'||l_proc, 5);
618 --
619 -- Call all supporting business operations
620 --
621 chk_delete
622 (p_rec => p_rec
623 );
624 --
625 hr_utility.set_location(' Leaving:'||l_proc, 10);
626 End delete_validate;
627 --
628 end hr_tim_bus;