1 Package Body hr_itf_bus as
2 /* $Header: hritfrhi.pkb 120.0 2005/05/31 00:58 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_itf_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_form_const varchar2(5) := 'PUI';
14 g_ss_const varchar2(5) := 'SS';
15 g_portal_const varchar2(5) := 'P';
16 g_user_interface_id number default null;
17
18 --
19 -- ----------------------------------------------------------------------------
20 -- |-----------------------< chk_non_updateable_args >------------------------|
21 -- ----------------------------------------------------------------------------
22 -- {Start Of Comments}
23 --
24 -- Description:
25 -- This procedure is used to ensure that non updateable attributes have
26 -- not been updated. If an attribute has been updated an error is generated.
27 --
28 -- Pre Conditions:
29 -- g_old_rec has been populated with details of the values currently in
30 -- the database.
31 --
32 -- In Arguments:
33 -- p_rec has been populated with the updated values the user would like the
34 -- record set to.
35 --
36 -- Post Success:
37 -- Processing continues if all the non updateable attributes have not
38 -- changed.
39 --
40 -- Post Failure:
41 -- An application error is raised if any of the non updatable attributes
42 -- have been altered.
43 --
44 -- {End Of Comments}
45 -- ----------------------------------------------------------------------------
46 Procedure chk_non_updateable_args
47 (p_effective_date in date
48 ,p_rec in hr_itf_shd.g_rec_type
49 ) IS
50 --
51 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
52 --
53 Begin
54 --
55 -- Only proceed with the validation if a row exists for the current
56 -- record in the HR Schema.
57 --
58 IF NOT hr_itf_shd.api_updating
59 (p_user_interface_id => p_rec.user_interface_id
60 ,p_object_version_number => p_rec.object_version_number
61 ) THEN
62 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
63 fnd_message.set_token('PROCEDURE ', l_proc);
64 fnd_message.set_token('STEP ', '5');
65 fnd_message.raise_error;
66 END IF;
67 --
68 End chk_non_updateable_args;
69
70 -- ----------------------------------------------------------------------------
71 -- ------------------------------< CHK_TYPE>-----------------------------------
72 -- ----------------------------------------------------------------------------
73 -- {Start Of Comments}
74 --
75 -- Description:
76 -- This procedure ensures a valid lookup value is selected for the type
77 -- column. The valid values are :SS,PUI,P.
78
79 -- Pre Conditions:
80 -- g_rec has been populated with details of the values
81 -- from the ins or the upd procedures
82 --
83 -- In Arguments:
84 -- p_effective_date, p_type
85
86 -- Post Success:
87 -- Processing continues if the type value comes from the set of values in
88 -- the lookup HR_KPI_INTERFACE_TYPE
89 --
90 -- Post Failure:
91 -- An application error is raised if user interface key is null or exists
92 -- already
93 --
94 -- {End Of Comments}
95 -- ----------------------------------------------------------------------------
96
97 procedure chk_type
98 (
99 p_effective_date in date,
100 p_type in varchar2
101 )
102 is
103
104 -- Variables for API Boolean parameters
105 l_proc varchar2(72) := g_package ||'chk_type';
106 l_found varchar2(10);
107
108 Begin
109
110 hr_utility.set_location(' Entering:' || l_proc,10);
111
112 hr_api.mandatory_arg_error
113 (p_api_name => l_proc
114 ,p_argument => 'TYPE'
115 ,p_argument_value => p_type
116 );
117
118
119 hr_utility.set_location('validating:'||l_proc,20);
120
121 --Is it neccessary to validate against not_exists_in_fnd_lookups?
122
123 if hr_api.not_exists_in_hrstanlookups
124 (p_effective_date => p_effective_date
125 ,p_lookup_type => 'HR_KPI_UI_TYPE'
126 ,p_lookup_code => p_type
127 ) then
128 fnd_message.set_name('PER', 'PER_449936_ITF_INT_TYPE_INVAL');
129 fnd_message.raise_error;
130 end if;
131
132 hr_utility.set_location(' Leaving:' || l_proc,30);
133
134 Exception
135 when app_exception.application_exception then
136 IF hr_multi_message.exception_add
137 (p_associated_column1 => 'HR_KI_USER_INTERFACES.TYPE'
138 )
139 THEN
140 hr_utility.set_location(' Leaving:'|| l_proc,40);
141 raise;
142 END IF;
143
144 hr_utility.set_location(' Leaving:'|| l_proc,50);
145 --
146 End chk_type;
147
148 -- ----------------------------------------------------------------------------
149 -- ------------------------------< CHK_FORM_NAME>------------------------------
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure ensures that valid form name is entered when the
155 -- type is PUI
156
157 -- Pre Conditions:
158 -- g_rec has been populated with details of the values
159 -- from the ins or the upd procedures
160 --
161 -- In Arguments:
162 -- p_type, p_form_name
163
164 -- Post Success:
165 -- Processing continues if the form_name is valid
166 --
167 -- Post Failure:
168 -- An application error is raised if form_name is invalid
169 -- already
170 --
171 -- {End Of Comments}
172 -- ----------------------------------------------------------------------------
173
174 procedure chk_form_name
175 (
176 p_type in varchar2,
177 p_form_name in varchar2,
178 p_user_interface_id in number
179 )
180 is
181
182 CURSOR csr_form is
183 select
184 'found'
185 From
186 fnd_form frm
187 where
188 frm.form_name = p_form_name;
189
190 CURSOR csr_form_unique is
191 select
192 'found'
193 From
194 hr_ki_user_interfaces itf
195 Where
196 itf.type = p_type
197 and itf.form_name = p_form_name
198 and (p_user_interface_id is null or
199 itf.user_interface_id <> p_user_interface_id);
200
201 -- Variables for API Boolean parameters
202 l_proc varchar2(72) := g_package ||'chk_form_name';
203 l_found varchar2(10);
204
205 Begin
206
207 hr_utility.set_location(' Entering:' || l_proc,10);
208
209
210 -- the check for form_name is done independent of any previous errors
211 -- further checks which depend on type are done by checking if any errors
212 -- already occur in the multi message list.
213 if (p_type = g_form_const)
214 then
215 open csr_form;
216 fetch csr_form into l_found;
217
218 if csr_form%NOTFOUND then
219 close csr_form;
220 fnd_message.set_name('PER','PER_449937_ITF_FORM_INVAL');
221 fnd_message.raise_error;
222 end if;
223
224 close csr_form;
225
226 if hr_multi_message.no_exclusive_error
227 (p_check_column1 =>
228 'HR_KI_USER_INTERFACES.TYPE'
229 )
230 then
231
232 -- checking for a duplicate record
233 -- the case of updation is handled in the cursor itself with a null check
234 open csr_form_unique;
235 fetch csr_form_unique into l_found;
236
237 if csr_form_unique%FOUND then
238 close csr_form_unique;
239 fnd_message.set_name('PER','PER_449938_ITF_FORM_DUPLI');
240 fnd_message.raise_error;
241 end if;
242 close csr_form_unique;
243
244 end if;
245 end if;
246
247 hr_utility.set_location(' Leaving:' || l_proc,20);
248
249 Exception
250 when app_exception.application_exception then
251 IF hr_multi_message.exception_add
252 (p_associated_column1 => 'HR_KI_USER_INTERFACES.FORM_NAME'
253 )
254 THEN
255 hr_utility.set_location(' Leaving:'|| l_proc,30);
256 raise;
257 END IF;
258
259 hr_utility.set_location(' Leaving:'|| l_proc,40);
260 --
261 End chk_form_name;
262
263 -- ----------------------------------------------------------------------------
264 -- --------------------------<CHK_PAGE_REGION_CODE>----------------------------
265 -- ----------------------------------------------------------------------------
266 -- {Start Of Comments}
267 --
268 -- Description:
269 -- This procedure ensures a valid page_region_code is entered.
270 --
271 -- Pre Conditions:
272 -- g_rec has been populated with details of the values
273 -- from the ins or the upd procedures
274 --
275 -- In Arguments:
276 -- p_type, p_page_region_code,p_region_code
277
278 -- Post Success:
279 -- Processing continues if the valid page_region_code is entered.
280 --
281 -- Post Failure:
282 -- An application error is raised if invalid page_region_code is entered.
283 --
284 -- {End Of Comments}
285 -- ----------------------------------------------------------------------------
286
287 procedure chk_page_region_code
288 (
289 p_type in varchar2,
290 p_page_region_code in varchar2,
291 p_region_code in varchar2,
292 p_user_interface_id in number
293 )
294 is
295
296 CURSOR csr_form_functions is
297 select
298 'found'
299 From
300 fnd_form_functions frm
301 where
302 frm.function_name = p_page_region_code;
303
304 CURSOR csr_reg_unique_check( cur_type varchar2,
305 cur_page_region_code varchar2,
306 cur_region_code varchar2,
307 cur_user_interface_id number) is
308 select 'found'
309 from hr_ki_user_interfaces itf
310 where
311 itf.type = cur_type
312 and itf.page_region_code = cur_page_region_code
313 and (cur_region_code is null or itf.region_code = cur_region_code)
314 and (cur_user_interface_id is null or itf.user_interface_id <> cur_user_interface_id);
315
316 -- Variables for API Boolean parameters
317 l_proc varchar2(72) := g_package ||'chk_type';
318 l_found varchar2(10);
319
320 Begin
321
322 hr_utility.set_location(' Entering:' || l_proc,10);
323
324 IF (p_type = g_ss_const or p_type = g_portal_const )THEN
325
326 -- perform independent validation of page_region_code.
327 open csr_form_functions;
328 fetch csr_form_functions into l_found;
329
330 if csr_form_functions%NOTFOUND then
331 close csr_form_functions;
332 fnd_message.set_name('PER','PER_449939_ITF_PGRGNCD_INVLD');
333 fnd_message.raise_error;
334 end if;
335
336 close csr_form_functions;
337
338 if hr_multi_message.no_exclusive_error
339 (p_check_column1 =>
340 'HR_KI_USER_INTERFACES.TYPE'
341 )
342 then
343 open csr_reg_unique_check(p_type,
344 p_page_region_code,
345 p_region_code,
346 p_user_interface_id);
347 fetch csr_reg_unique_check into l_found;
348
349 if csr_reg_unique_check%FOUND
350 then
351 close csr_reg_unique_check;
352 fnd_message.set_name('PER','PER_449940_ITF_RGNCD_DUPLI');
353 fnd_message.raise_error;
354 end if;
355 close csr_reg_unique_check;
356 end if;
357 end if;
358 hr_utility.set_location(' Leaving:'|| l_proc,20);
359
360 Exception
361 when app_exception.application_exception then
362 IF hr_multi_message.exception_add
363 (p_associated_column1 => 'HR_KI_USER_INTERFACES.PAGE_REGION_CODE',
364 p_associated_column2 => 'HR_KI_USER_INTERFACES.REGION_CODE'
365 )
366 THEN
367 hr_utility.set_location(' Leaving:'|| l_proc,30);
368 raise;
369 END IF;
370
371 hr_utility.set_location(' Leaving:'|| l_proc,40);
372 --
373 End chk_page_region_code;
374
375 -- ----------------------------------------------------------------------------
376 -- --------------------------<CHK_VALID_COMBINATION>----------------------------
377 -- ----------------------------------------------------------------------------
378 -- {Start Of Comments}
379 --
380 -- Description:
381 -- This procedure ensures a valid combination of values for type,form name
382 -- ,page region code and region code.
383
384 -- Pre Conditions:
385 -- g_rec has been populated with details of the values
386 -- from the ins or the upd procedures
387 --
388 -- In Arguments:
389 -- p_type, p_form_name, p_page_region_code, p_region_code
390
391 -- Post Success:
392 -- Processing continues if the type value comes from the set of values in
393 -- the lookup HR_KPI_UI_TYPES
394 --
395 -- Post Failure:
396 -- An application error is raised if user interface key is null or exists
397 -- already
398 --
399 -- {End Of Comments}
400 -- ----------------------------------------------------------------------------
401 procedure chk_valid_combination
402 (
403 p_type in varchar2,
404 p_form_name in varchar2,
405 p_page_region_code in varchar2,
406 p_region_code in varchar2
407 )
408 is
409
410 -- Variables for API Boolean parameters
411 l_proc varchar2(72) := g_package ||'chk_region_code';
412 l_found varchar2(10);
413
414 Begin
415
416 hr_utility.set_location(' Entering:' || l_proc,10);
417
418 if hr_multi_message.no_exclusive_error
419 (p_check_column1 =>
420 'HR_KI_USER_INTERFACES.TYPE'
421 )
422 then
423
424 if(p_type = g_form_const and
425 (p_form_name is null or
426 p_page_region_code is not null or
427 p_region_code is not null)
428 )
429 then
430 fnd_message.set_name('PER','PER_449941_ITF_IVLDPUI_COMBN');
431 fnd_message.raise_error;
432 end if;
433
434 if((p_type = g_ss_const or p_type = g_portal_const) and
435 (p_page_region_code is null or
436 p_form_name is not null)
437 )
438 then
439 fnd_message.set_name('PER','PER_449942_ITF_IVLDPG_COMBN');
440 fnd_message.raise_error;
441 end if;
442
443 end if;
444
445 hr_utility.set_location(' Leaving:'|| l_proc,20);
446
447 Exception
448 when app_exception.application_exception then
449 IF hr_multi_message.exception_add
450 (p_associated_column1 => 'HR_KI_USER_INTERFACES.TYPE',
451 p_associated_column2 => 'HR_KI_USER_INTERFACES.FORM_NAME',
452 p_associated_column3 => 'HR_KI_USER_INTERFACES.PAGE_REGION_CODE'
453 )
454 THEN
455 hr_utility.set_location(' Leaving:'|| l_proc,30);
456 raise;
457 END IF;
458
459 hr_utility.set_location(' Leaving:'|| l_proc,40);
460 --
461 End chk_valid_combination;
462
463 -- ----------------------------------------------------------------------------
464 -- -----------------------------< CHK_DELETE>----------------------------------
465 -- ----------------------------------------------------------------------------
466 -- {Start Of Comments}
467 --
468 -- Description:
469 -- This procedure ensures that a delete occurs only if there are no child
470 -- rows for a record in hr_ki_user_interfaces. The tables that contain child
471 -- rows are hr_ki_hierarchy_node_maps.
472
473 -- Pre Conditions:
474 -- g_rec has been populated with details of the values
475 -- from the ins or the upd procedures
476 --
477 -- In Arguments:
478 -- p_user_interface_id
479
480 -- Post Success:
481 -- Processing continues if there are no child records.
482 --
483 -- Post Failure:
484 -- An application error is raised if there are any child rows from any of
485 -- the above mentioned tables.
486 --
487 -- {End Of Comments}
488 -- ----------------------------------------------------------------------------
489
490 procedure chk_delete(p_user_interface_id in varchar2)
491 is
492
493 CURSOR csr_hnm_id is
494 select
495 distinct 'found'
496 From
497 hr_ki_hierarchy_node_maps hnm
498 where
499 hnm.user_interface_id = p_user_interface_id;
500
501 l_found varchar2(30);
502 l_proc varchar2(72) := g_package ||'chk_delete';
503
504 Begin
505
506 hr_utility.set_location(' Entering:' || l_proc,10);
507
508 open csr_hnm_id;
509 fetch csr_hnm_id into l_found;
510
511 if csr_hnm_id%FOUND then
512 close csr_hnm_id;
513 fnd_message.set_name( 'PER','PER_449944_ITF_HNM_MAIN_EXIST');
514 fnd_message.raise_error;
515 end if;
516
517 close csr_hnm_id;
518
519 hr_utility.set_location(' Leaving:' || l_proc,20);
520
521 Exception
522 when app_exception.application_exception then
523 IF hr_multi_message.exception_add
524 (p_associated_column1 =>
525 'HR_KI_USER_INTERFACES.USER_INTERFACE_ID'
526 )THEN
527 hr_utility.set_location(' Leaving:'|| l_proc,30);
528 raise;
529 END IF;
530 hr_utility.set_location(' Leaving:'|| l_proc,40);
531
532 End chk_delete;
533 --
534 -- ----------------------------------------------------------------------------
535 -- |---------------------------< insert_validate >----------------------------|
536 -- ----------------------------------------------------------------------------
537 Procedure insert_validate
538 (p_effective_date in date
539 ,p_rec in out nocopy hr_itf_shd.g_rec_type
540 ) is
541 --
542 l_proc varchar2(72) := g_package||'insert_validate';
543 --
544 Begin
545 hr_utility.set_location('Entering:'||l_proc, 5);
546 --
547 -- Call all supporting business operations
548 --
549 --
550 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
551 --
552 chk_type(p_effective_date => p_effective_date,
553 p_type => p_rec.type);
554
555 chk_valid_combination(p_type => p_rec.type,
556 p_form_name => p_rec.form_name,
557 p_page_region_code => p_rec.page_region_code,
558 p_region_code => p_rec.region_code);
559
560 chk_form_name(p_type => p_rec.type,
561 p_form_name => p_rec.form_name,
562 p_user_interface_id => ''
563 );
564
565 chk_page_region_code(p_type => p_rec.type,
566 p_page_region_code => p_rec.page_region_code,
567 p_region_code => p_rec.region_code,
568 p_user_interface_id => ''
569 );
570
571 hr_utility.set_location(' Leaving:'||l_proc, 10);
572 End insert_validate;
573 --
574 -- ----------------------------------------------------------------------------
575 -- |---------------------------< update_validate >----------------------------|
576 -- ----------------------------------------------------------------------------
577 Procedure update_validate
578 (p_effective_date in date
579 ,p_rec in out nocopy hr_itf_shd.g_rec_type
580 ) is
581 --
582 l_proc varchar2(72) := g_package||'update_validate';
583 --
584 Begin
585 hr_utility.set_location('Entering:'||l_proc, 5);
586 --
587 -- Call all supporting business operations
588 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
589 --
590 -- Validate Dependent Attributes
591 --
592 chk_non_updateable_args
593 (p_effective_date => p_effective_date
594 ,p_rec => p_rec
595 );
596
597 chk_type(p_effective_date => p_effective_date,
598 p_type => p_rec.type);
599
600 chk_valid_combination(p_type => p_rec.type,
601 p_form_name => p_rec.form_name,
602 p_page_region_code => p_rec.page_region_code,
603 p_region_code => p_rec.region_code);
604
605 chk_form_name(p_type => p_rec.type,
606 p_form_name => p_rec.form_name,
607 p_user_interface_id => p_rec.user_interface_id
608 );
609
610 chk_page_region_code(p_type => p_rec.type,
611 p_page_region_code => p_rec.page_region_code,
612 p_region_code => p_rec.region_code,
613 p_user_interface_id => p_rec.user_interface_id
614 );
615 --
616 --
617 hr_utility.set_location(' Leaving:'||l_proc, 10);
618 End update_validate;
619 --
620 -- ----------------------------------------------------------------------------
621 -- |---------------------------< delete_validate >----------------------------|
622 -- ----------------------------------------------------------------------------
623 Procedure delete_validate
624 (p_rec in hr_itf_shd.g_rec_type
625 ) is
626 --
627 l_proc varchar2(72) := g_package||'delete_validate';
628 --
629 Begin
630 hr_utility.set_location('Entering:'||l_proc, 5);
631 --
632 -- Call all supporting business operations
633 --
634 chk_delete(p_rec.user_interface_id);
635
636 hr_utility.set_location(' Leaving:'||l_proc, 10);
637 End delete_validate;
638 --
639 end hr_itf_bus;