1 Package Body hr_int_bus as
2 /* $Header: hrintrhi.pkb 115.0 2004/01/09 01:40 vkarandi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_int_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_integration_id number default null;
15 -- ----------------------------------------------------------------------------
16 -- |-----------------------< chk_non_updateable_args >------------------------|
17 -- ----------------------------------------------------------------------------
18 -- {Start Of Comments}
19 --
20 -- Description:
21 -- This procedure is used to ensure that non updateable attributes have
22 -- not been updated. If an attribute has been updated an error is generated.
23 --
24 -- Pre Conditions:
25 -- g_old_rec has been populated with details of the values currently in
26 -- the database.
27 --
28 -- In Arguments:
29 -- p_rec has been populated with the updated values the user would like the
30 -- record set to.
31 --
32 -- Post Success:
33 -- Processing continues if all the non updateable attributes have not
34 -- changed.
35 --
36 -- Post Failure:
37 -- An application error is raised if any of the non updatable attributes
38 -- have been altered.
39 --
40 -- {End Of Comments}
41 -- ----------------------------------------------------------------------------
42 Procedure chk_non_updateable_args
43 (p_rec in hr_int_shd.g_rec_type
44 ) IS
45 --
46 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
47 --
48 Begin
49 --
50 -- Only proceed with the validation if a row exists for the current
51 -- record in the HR Schema.
52 --
53 IF NOT hr_int_shd.api_updating
54 (p_integration_id => p_rec.integration_id
55 ,p_object_version_number => p_rec.object_version_number
56 ) THEN
57 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
58 fnd_message.set_token('PROCEDURE ', l_proc);
59 fnd_message.set_token('STEP ', '5');
60 fnd_message.raise_error;
61 END IF;
62
63 if nvl(p_rec.integration_key, hr_api.g_varchar2) <>
64 nvl(hr_int_shd.g_old_rec.integration_key
65 ,hr_api.g_varchar2
66 ) then
67 hr_api.argument_changed_error
68 (p_api_name => l_proc
69 ,p_argument => 'INTEGRATION_KEY'
70 ,p_base_table => hr_int_shd.g_tab_nam
71 );
72 end if;
73
74
75 End chk_non_updateable_args;
76 -- ----------------------------------------------------------------------------
77 -- |-----------------------< CHK_INTEGRATION_KEY>------------------------|
78 -- ----------------------------------------------------------------------------
79 -- {Start Of Comments}
80 --
81 -- Description:
82 -- This procedure ensures integration key is not null and unique.
83 -- Pre Conditions:
84 -- g_old_rec has been populated with details of the values currently in
85 -- the database.
86 --
87 -- In Arguments:
88 -- p_integration_key
89 -- Post Success:
90 -- Processing continues if integration key is not null and unique
91 --
92 -- Post Failure:
93 -- An application error is raised if integration key is null or exists
94 -- already in table.
95 --
96 -- {End Of Comments}
97 -- ----------------------------------------------------------------------------
98 Procedure chk_integration_key
99 (p_integration_key in varchar2
100
101 ) IS
102 --
103 l_proc varchar2(72) := g_package || 'CHK_INTEGRATION_KEY';
104 l_key varchar2(1) ;
105 cursor csr_name is
106 select null
107 from hr_ki_integrations
108 where integration_key = p_integration_key;
109 --
110 Begin
111 hr_utility.set_location('Entering:'||l_proc,10);
112 --
113 -- Check value has been passed
114 --
115 hr_api.mandatory_arg_error
116 (p_api_name => l_proc
117 ,p_argument => 'INTEGRATION_KEY'
118 ,p_argument_value => p_integration_key
119 );
120
121 hr_utility.set_location('Validating:'||l_proc,20);
122 open csr_name;
123 fetch csr_name into l_key;
124 if (csr_name%found)
125 then
126 close csr_name;
127 fnd_message.set_name('PER','PER_449965_INT_IN_KEY_DUP');
128 fnd_message.raise_error;
129 end if;
130 close csr_name;
131
132 hr_utility.set_location(' Leaving:'||l_proc,30);
133 exception
134 when app_exception.application_exception then
135 if hr_multi_message.exception_add
136 (p_associated_column1 => 'HR_KI_INTEGRATIONS.INTEGRATION_KEY'
137 )then
138 hr_utility.set_location(' Leaving:'||l_proc, 40);
139 raise;
140 end if;
141 hr_utility.set_location(' Leaving:'||l_proc,50);
142 End chk_integration_key;
143
144 -- ----------------------------------------------------------------------------
145 -- |---------------------------< CHK_SYNCHED>----------------------------------|
146 -- ----------------------------------------------------------------------------
147 -- {Start Of Comments}
148 --
149 -- Description:
150 -- This procedure ensures synched is set to 'Y' for URL and 'N' for SSO and
151 -- ECX type of integrations.
152 -- Pre Conditions:
153 -- g_old_rec has been populated with details of the values currently in
154 -- the database.
155 --
156 -- In Arguments:
157 -- p_synched
158 -- p_url
159 -- Post Success:
160 -- Processing continues if synched is set to 'Y' for URL and 'N' for SSO and
161 -- ECX type of integrations.
162 --
163 -- Post Failure:
164 -- An application error is raised for invalid value of synched.
165 --
166 -- {End Of Comments}
167 -- ----------------------------------------------------------------------------
168 Procedure chk_synched
169 (p_synched in out nocopy varchar2
170 ,p_url in varchar2
171
172 ) IS
173 --
174 l_proc varchar2(72) := g_package || 'chk_synched';
175
176 --
177 Begin
178 hr_utility.set_location('Entering:'||l_proc,10);
179
180 if p_url is not null then
181 p_synched := 'Y';
182 else
183 p_synched := 'N';
184 end if;
185
186 hr_utility.set_location(' Leaving:'||l_proc,30);
187 exception
188 when app_exception.application_exception then
189 if hr_multi_message.exception_add
190 (p_associated_column1 => 'HR_KI_INTEGRATIONS.SYNCHED'
191 )then
192 hr_utility.set_location(' Leaving:'||l_proc, 40);
193 raise;
194 end if;
195 hr_utility.set_location(' Leaving:'||l_proc,50);
196 End chk_synched;
197
198 -- ----------------------------------------------------------------------------
199 -- |---------------------------< CHK_SYNCHED_UPD>-----------------------------|
200 -- ----------------------------------------------------------------------------
201 -- {Start Of Comments}
202 --
203 -- Description:
204 -- This procedure ensures synched is set to 'Y' for URL and 'Y' or 'N'
205 -- for SSO and ECX type of integrations.
206 -- Pre Conditions:
207 -- g_old_rec has been populated with details of the values currently in
208 -- the database.
209 --
210 -- In Arguments:
211 -- p_synched
212 -- p_url
213 -- Post Success:
214 -- Processing continues if synched is set to 'Y' for URL and 'Y' or 'N'
215 -- for SSO and ECX type of integrations.
216 --
217 -- Post Failure:
218 -- An application error is raised for invalid value of synched.
219 --
220 -- {End Of Comments}
221 -- ----------------------------------------------------------------------------
222 Procedure chk_synched_upd
223 (p_synched in varchar2
224 ,p_url in varchar2
225
226 ) IS
227 --
228 l_proc varchar2(72) := g_package || 'chk_synched_upd';
229
230 --
231 Begin
232 hr_utility.set_location('Entering:'||l_proc,10);
233 --
234 -- Check value has been passed
235 --
236 hr_api.mandatory_arg_error
237 (p_api_name => l_proc
238 ,p_argument => 'SYNCHED'
239 ,p_argument_value => p_synched
240 );
241
242 hr_utility.set_location('Validating:'||l_proc,20);
243
244 if upper(p_synched) ='Y' or upper(p_synched)='N' then
245
246 if (upper(p_synched) ='N' and p_url is not null ) then
247 --For URL type of integration synched should be Y
248 fnd_message.set_name('PER', 'PER_449973_INT_URL_SYND_INVAL');
249 fnd_message.raise_error;
250 end if;
251
252 else
253 fnd_message.set_name('PER', 'PER_449974_INT_SYNCHED_INVAL');
254 fnd_message.raise_error;
255
256 end if;
257
258 hr_utility.set_location(' Leaving:'||l_proc,30);
259 exception
260 when app_exception.application_exception then
261 if hr_multi_message.exception_add
262 (p_associated_column1 => 'HR_KI_INTEGRATIONS.SYNCHED'
263 )then
264 hr_utility.set_location(' Leaving:'||l_proc, 40);
265 raise;
266 end if;
267 hr_utility.set_location(' Leaving:'||l_proc,50);
268 End chk_synched_upd;
269
270 -- ----------------------------------------------------------------------------
271 -- |-------------------------------<CHK_SSO_DETAILS>--------------------------|
272 -- ----------------------------------------------------------------------------
273 -- {Start Of Comments}
274 --
275 -- Description:
276 -- This procedure ensures if the ecx details values are valid
277 -- are not duplicated in hr_ki_integration table
278 -- provided they are not null.
279 --
280 -- Pre Conditions:
281 -- g_old_rec has been populated with details of the values currently in
282 -- the database.
283 --
284 -- In Arguments:
285 -- p_insert
286 -- p_ext_application_id
287 -- p_application_name
288 -- p_application_type
289 -- p_application_url
290 -- p_logout_url
291 -- p_user_field
292 -- p_password_field
293 -- p_authentication_needed
294
295 --
296 -- Post Success:
297 -- Processing continues if ecx details are not null and are present in ecx
298 -- views and not duplicated in hr_ki_integration table.
299 --
300 -- Post Failure:
301 -- An application error is raised if ecx details are invalid.
302 --
303 -- {End Of Comments}
304 -- ----------------------------------------------------------------------------
305 Procedure CHK_SSO_DETAILS
306 (
307 p_insert in varchar2
308 ,p_ext_application_id in number
309 ,p_application_name in varchar2
310 ,p_application_type in varchar2
311 ,p_application_url in varchar2
312 ,p_logout_url in varchar2
313 ,p_user_field in varchar2
314 ,p_password_field in varchar2
315 ,p_authentication_needed in varchar2
316 ,p_integration_id in number
317 ) IS
318 --
319 l_proc varchar2(72) := g_package || 'CHK_SSO_DETAILS';
320
321 l_key_sso varchar2(1) ;
322 l_key_sso_upd varchar2(1) ;
323
324 cursor csr_sso is
325 select null
326 from hr_ki_integrations
327 where
328 application_name=p_application_name
329 and application_type=p_application_type
330 and application_url=p_application_url
331 and logout_url=p_logout_url
332 and user_field=p_user_field
333 and password_field=p_password_field
334 and authentication_needed=p_authentication_needed;
335
336 cursor csr_sso_upd is
337 select null
338 from hr_ki_integrations
339 where
340 application_name=p_application_name
341 and application_type=p_application_type
342 and application_url=p_application_url
343 and logout_url=p_logout_url
344 and user_field=p_user_field
345 and password_field=p_password_field
346 and authentication_needed=p_authentication_needed
347 and integration_id<>p_integration_id;
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc,10);
351
352 -- Only proceed with record validation when the
353 -- Multiple Message List does not already contain an errors
354 -- associated with the party_name
355 --
356 if hr_multi_message.no_exclusive_error
357 (p_check_column1 => 'HR_KI_INTEGRATIONS.EXT_APPLICATION_ID'
358 ,p_associated_column1 => 'HR_KI_INTEGRATIONS.EXT_APPLICATION_ID'
359 ) then
360
361
362 --For insert mode ext_application_id must be null
363 if(p_insert ='insert') then
364 if(p_ext_application_id is not null) then
365 fnd_message.set_name('PER','PER_449976_INT_SSO_ID_INVALID');
366 fnd_message.raise_error;
367 end if;
368 end if;
369
370 --If any ofthe SSO parameters are not null then validate SSO details
371
372 if ( p_application_name is not null
373 or p_application_type is not null
374 or p_application_url is not null
375 or p_logout_url is not null
376 or p_user_field is not null
377 or p_password_field is not null
378 or p_authentication_needed is not null
379 )then
380
381 if (p_application_name is null
382 or p_application_type is null
383 or p_application_url is null
384 or p_logout_url is null
385 or p_user_field is null
386 or p_password_field is null
387 or p_authentication_needed is null
388 ) then
389 fnd_message.set_name('PER','PER_449977_INT_SSO_COL_INVALID');
390 fnd_message.raise_error;
391 end if;
392 end if;
393
394 hr_utility.set_location('Validating:'||l_proc,20);
395
396 --for only URL case
397 --all SSO details will be null so this IF condition!
398
399 if ( p_application_name is not null
400 or p_application_type is not null
401 or p_application_url is not null
402 or p_logout_url is not null
403 or p_user_field is not null
404 or p_password_field is not null
405 or p_authentication_needed is not null
406 )then
407
408 hr_utility.set_location('Validating combination'||l_proc,30);
409 --
410 --Check if SSO combination already exists in the hr_ki_integrations table.
411 --
412 if(p_insert ='insert') then
413 open csr_sso;
414 fetch csr_sso into l_key_sso;
415 if (csr_sso%found)
416 then
417 close csr_sso;
418 fnd_message.set_name('PER','PER_449978_INT_SSO_DT_DUPLI');
419 fnd_message.raise_error;
420 end if;
421 close csr_sso;
422 else
423 open csr_sso_upd;
424 fetch csr_sso_upd into l_key_sso_upd;
425 if (csr_sso_upd%found)
426 then
427 close csr_sso_upd;
428 fnd_message.set_name('PER','PER_449978_INT_SSO_DT_DUPLI');
429 fnd_message.raise_error;
430 end if;
431 close csr_sso_upd;
432 end if;
433
434 end if;
435 end if;
436
437 exception
438 when app_exception.application_exception then
439 if hr_multi_message.exception_add
440 (p_associated_column1 => 'HR_KI_INTEGRATIONS.EXT_APPLICATION_ID'
441 )then
442 hr_utility.set_location(' Leaving:'||l_proc, 40);
443 raise;
444 end if;
445 hr_utility.set_location(' Leaving:'||l_proc,50);
446 End CHK_SSO_DETAILS;
447 -- ----------------------------------------------------------------------------
448 -- |---------------------< CHK_EXT_APPLICATION_ID_UPD>------------------------|
449 -- ----------------------------------------------------------------------------
450 -- {Start Of Comments}
451 --
452 -- Description:
453 -- This procedure ensures external application id is present in
454 -- hr_ki_ext_applications and it is not duplicated in hr_ki_integrations
455 -- table.
456 -- If earlier procedure is used for update validation then error will be
457 -- thrown even if id is unique and not null as earlier query
458 -- does not have addtional p_ext_application_id condition in the cursor.
459 -- We can not combine these 2 methods as p_integration_id is not
460 -- available at the time of insert_validation
461 -- Pre Conditions:
462 -- g_old_rec has been populated with details of the values currently in
463 -- the database.
464 --
465 -- In Arguments:
466 -- p_ext_application_id
467 -- p_integration_id
468 -- Post Success:
469 -- Processing continues if ext_application_id is valid.
470 --
471 -- Post Failure:
472 -- An application error is raised if if ext_application_id is invalid.
473 --
474 -- {End Of Comments}
475 -- ----------------------------------------------------------------------------
476 Procedure CHK_EXT_APPLICATION_ID_UPD
477 (p_ext_application_id in number
478 ,p_integration_id in number
479 ) IS
480 --
481 l_proc varchar2(72) := g_package || 'CHK_EXT_APPLICATION_ID_UPD';
482 l_key varchar2(1) ;
483 l_key_app varchar2(1) ;
484 cursor csr_name is
485 select null
486 from hr_ki_ext_applications
487 where ext_application_id = p_ext_application_id;
488 cursor csr_app is
489 select null
490 from hr_ki_integrations
491 where ext_application_id = p_ext_application_id
492 and integration_id<>p_integration_id;
493 --
494 Begin
495 hr_utility.set_location('Entering:'||l_proc,10);
496 --
497 -- Check value has been passed
498 --
499 if hr_multi_message.no_exclusive_error
500 (p_check_column1 => 'HR_KI_INTEGRATIONS.EXT_APPLICATION_ID'
501 ,p_associated_column1 => 'HR_KI_INTEGRATIONS.EXT_APPLICATION_ID'
502 ) then
503
504 if p_ext_application_id is not null then
505
506 hr_utility.set_location('Validating ID:'||l_proc,20);
507
508 open csr_name;
509 fetch csr_name into l_key;
510 if (csr_name%notfound)
511 then
512 close csr_name;
513 fnd_message.set_name('PER','PER_449970_INT_EXT_ID_ABSENT');
514 fnd_message.raise_error;
515 end if;
516 close csr_name;
517
518 --Now check if ext_application_id is already present in the
519 --hr_ki_integrations table
520
521 hr_utility.set_location('Validating ID in Integrations table:'||l_proc,30);
522 open csr_app;
523 fetch csr_app into l_key_app;
524 if (csr_app%found)
525 then
526 close csr_app;
527 fnd_message.set_name('PER','PER_449971_INT_EXT_ID_DUPLI');
528 fnd_message.raise_error;
529 end if;
530 close csr_app;
531
532 end if;
533
534 end if;
535 hr_utility.set_location(' Leaving:'||l_proc,30);
536 exception
537 when app_exception.application_exception then
538 if hr_multi_message.exception_add
539 (p_associated_column1 => 'HR_KI_INTEGRATIONS.EXT_APPLICATION_ID'
540 )then
541 hr_utility.set_location(' Leaving:'||l_proc, 40);
542 raise;
543 end if;
544 hr_utility.set_location(' Leaving:'||l_proc,50);
545 End CHK_EXT_APPLICATION_ID_UPD;
546
547 -- ----------------------------------------------------------------------------
548 -- |-------------------------------<CHK_ECX_DETAILS>--------------------------|
549 -- ----------------------------------------------------------------------------
550 -- {Start Of Comments}
551 --
552 -- Description:
553 -- This procedure ensures if the ecx details values are present in the ecx
554 -- views and are not duplicated in hr_ki_integration table
555 -- provided they are not null.
556 --
557 -- Pre Conditions:
558 -- g_old_rec has been populated with details of the values currently in
559 -- the database.
560 --
561 -- In Arguments:
562 -- ,p_party_type
563 -- ,p_party_name
564 -- ,p_party_site_name
565 -- ,p_transaction_type
566 -- ,p_transaction_subtype
567 -- ,p_standard_code
568 -- ,p_ext_trans_type
569 -- ,p_ext_trans_subtype
570 -- ,p_trans_direction
571 --
572 -- Post Success:
573 -- Processing continues if ecx details are not null and are present in ecx
574 -- views and not duplicated in hr_ki_integration table.
575 --
576 -- Post Failure:
577 -- An application error is raised if ecx details are invalid.
578 --
579 -- {End Of Comments}
580 -- ----------------------------------------------------------------------------
581 Procedure CHK_ECX_DETAILS
582 (
583 p_party_type in varchar2
584 ,p_party_name in varchar2
585 ,p_party_site_name in varchar2
586 ,p_transaction_type in varchar2
587 ,p_transaction_subtype in varchar2
588 ,p_standard_code in varchar2
589 ,p_ext_trans_type in varchar2
590 ,p_ext_trans_subtype in varchar2
591 ,p_trans_direction in varchar2
592 ,p_integration_id in number
593 ) IS
594 --
595 l_proc varchar2(72) := g_package || 'CHK_ECX_DETAILS';
596
597 l_key_ecx varchar2(1) ;
598 l_key_ecx_upd varchar2(1) ;
599
600 cursor csr_ecx is
601 select null
602 from hr_ki_integrations
603 where party_type=p_party_type
604 and party_name=p_party_name
605 and party_site_name=p_party_site_name
606 and transaction_type=p_transaction_type
607 and transaction_subtype=p_transaction_subtype
608 and standard_code=p_standard_code
609 and ext_trans_type=p_ext_trans_type
610 and ext_trans_subtype=p_ext_trans_subtype
611 and trans_direction=p_trans_direction;
612
613 cursor csr_ecx_upd is
614 select null
615 from hr_ki_integrations
616 where party_type=p_party_type
617 and party_name=p_party_name
618 and party_site_name=p_party_site_name
619 and transaction_type=p_transaction_type
620 and transaction_subtype=p_transaction_subtype
621 and standard_code=p_standard_code
622 and ext_trans_type=p_ext_trans_type
623 and ext_trans_subtype=p_ext_trans_subtype
624 and trans_direction=p_trans_direction
625 and integration_id<>p_integration_id;
626 --
627 Begin
628 hr_utility.set_location('Entering:'||l_proc,10);
629
630 -- Only proceed with record validation when the
631 -- Multiple Message List does not already contain an errors
632 -- associated with the party_name
633 --
634 if hr_multi_message.no_exclusive_error
635 (p_check_column1 => 'HR_KI_INTEGRATIONS.PARTY_NAME'
636 ,p_associated_column1 => 'HR_KI_INTEGRATIONS.PARTY_NAME'
637 ) then
638
639 --If any ofthe ECX parameters are not null then validate ECX details
640 if ( p_party_type is not null
641 or p_party_name is not null
642 or p_party_site_name is not null
643 or p_transaction_type is not null
644 or p_transaction_subtype is not null
645 or p_standard_code is not null
646 or p_ext_trans_type is not null
647 or p_ext_trans_subtype is not null
648 or p_trans_direction is not null )then
649
650 if (p_party_type is null
651 or p_party_name is null
652 or p_party_site_name is null
653 or p_transaction_type is null
654 or p_transaction_subtype is null
655 or p_standard_code is null
656 or p_ext_trans_type is null
657 or p_ext_trans_subtype is null
658 or p_trans_direction is null
659 ) then
660 fnd_message.set_name('PER','PER_449967_INT_ECX_COL_INVALID');
661 fnd_message.raise_error;
662 end if;
663 end if;
664
665 hr_utility.set_location('Validating:'||l_proc,20);
666
667 if ( p_party_type is not null
668 and p_party_name is not null
669 and p_party_site_name is not null
670 and p_transaction_type is not null
671 and p_transaction_subtype is not null
672 and p_standard_code is not null
673 and p_ext_trans_type is not null
674 and p_ext_trans_subtype is not null
675 and p_trans_direction is not null )then
676
677 hr_utility.set_location('Validating combination'||l_proc,30);
678 --
679 --Check if ECX combination already exists in the hr_ki_integrations table.
680 --
681 --For insert use csr_ecx cursor since integration_id will be null
682 --For update use csr_ecx_upd cursor
683 if p_integration_id is null then
684 open csr_ecx;
685 fetch csr_ecx into l_key_ecx;
686 if (csr_ecx%found)
687 then
688 close csr_ecx;
689 fnd_message.set_name('PER','PER_449969_INT_ECX_DT_DUPLI');
690 fnd_message.raise_error;
691 end if;
692 close csr_ecx;
693 else
694 open csr_ecx_upd;
695 fetch csr_ecx_upd into l_key_ecx_upd;
696 if (csr_ecx_upd%found)
697 then
698 close csr_ecx_upd;
699 fnd_message.set_name('PER','PER_449969_INT_ECX_DT_DUPLI');
700 fnd_message.raise_error;
701 end if;
702 close csr_ecx_upd;
703 end if;
704 end if;
705 end if;
706
707 exception
708 when app_exception.application_exception then
709 if hr_multi_message.exception_add
710 (p_associated_column1 => 'HR_KI_INTEGRATIONS.PARTY_NAME'
711 )then
712 hr_utility.set_location(' Leaving:'||l_proc, 40);
713 raise;
714 end if;
715 hr_utility.set_location(' Leaving:'||l_proc,50);
716 End CHK_ECX_DETAILS;
717
718
719 -- ----------------------------------------------------------------------------
720 -- |--------------------------< CHK_INTEGRATION_RECORD>------------------------|
721 -- ----------------------------------------------------------------------------
722 -- {Start Of Comments}
723 --
724 -- Description:
725 -- This procedure ensures that for simple URL integrations ECX details and
726 -- SSO columns are null and for ECX integration SSO and url columns
727 -- are null and for SSO integration ECX and url columns are null.
728 -- We will not consider field_name1.. and field_value1.. columns
729 -- in the validation.
730 --
731 -- Pre Conditions:
732 -- g_old_rec has been populated with details of the values currently in
733 -- the database.
734 --
735 -- In Arguments:
736 -- p_url
737 -- p_ext_application_id
738 -- p_party_type
739 -- p_party_name
740 -- p_party_site_name
741 -- p_transaction_type
742 -- p_transaction_subtype
743 -- p_standard_code
744 -- p_ext_trans_type
745 -- p_ext_trans_subtype
746 -- p_trans_direction
747 -- p_application_name
748 -- p_application_type
749 -- p_application_url
750 -- p_logout_url
751 -- p_user_field
752 -- p_password_field
753 -- p_authentication_needed
754 --
755 -- Post Success:
756 -- Processing continues if conditions specified as above are valid.
757 --
758 -- Post Failure:
759 -- An application error is raised if conditions specified as above are invalid
760 --
761 -- {End Of Comments}
762 -- ----------------------------------------------------------------------------
763 Procedure CHK_INTEGRATION_RECORD
764 (p_url in varchar2
765 ,p_ext_application_id in number
766 ,p_application_name in varchar2
767 ,p_application_type in varchar2
768 ,p_application_url in varchar2
769 ,p_logout_url in varchar2
770 ,p_user_field in varchar2
771 ,p_password_field in varchar2
772 ,p_authentication_needed in varchar2
773 ,p_party_type in varchar2
774 ,p_party_name in varchar2
775 ,p_party_site_name in varchar2
776 ,p_transaction_type in varchar2
777 ,p_transaction_subtype in varchar2
778 ,p_standard_code in varchar2
779 ,p_ext_trans_type in varchar2
780 ,p_ext_trans_subtype in varchar2
781 ,p_trans_direction in varchar2
782 ) IS
783 --
784 l_proc varchar2(72) := g_package || 'CHK_INTEGRATION_RECORD';
785 --
786 Begin
787 hr_utility.set_location('Entering:'||l_proc,10);
788
789
790 --At least one column out of url,ext_application_id and ecx column should
791 -- be not null
792
793 if (p_application_name is null
794 and p_application_name is null
795 and p_application_type is null
796 and p_application_url is null
797 and p_logout_url is null
798 and p_user_field is null
799 and p_password_field is null
800 and p_authentication_needed is null
801 and p_url is null
802 and p_party_type is null
803 and p_party_name is null
804 and p_party_site_name is null
805 and p_transaction_type is null
806 and p_transaction_subtype is null
807 and p_standard_code is null
808 and p_ext_trans_type is null
809 and p_ext_trans_subtype is null
810 and p_trans_direction is null
811 ) then
812 fnd_message.set_name('PER', 'PER_449966_INT_MAN_COL_NULL');
813 fnd_message.raise_error;
814 end if;
815
816 hr_utility.set_location('Validating extAppId:'||l_proc,20);
817
818 --for SSO type of integrations url and ecx details should be null
819
820 if (p_ext_application_id is not null
821 or p_application_name is not null
822 or p_application_type is not null
823 or p_application_url is not null
824 or p_logout_url is not null
825 or p_user_field is not null
826 or p_password_field is not null
827 or p_authentication_needed is not null
828
829 )then
830 if (p_url is not null
831 or p_party_type is not null
832 or p_party_name is not null
833 or p_party_site_name is not null
834 or p_transaction_type is not null
835 or p_transaction_subtype is not null
836 or p_standard_code is not null
837 or p_ext_trans_type is not null
838 or p_ext_trans_subtype is not null
839 or p_trans_direction is not null
840 ) then
841
842 fnd_message.set_name('PER', 'PER_449975_INT_U_E_S_INVALID');
843 fnd_message.raise_error;
844 end if;
845
846 end if;
847
848
849 hr_utility.set_location('Validating ECX:'||l_proc,30);
850 --
851 --for ECX type of integrations url and SSO details
852 --should be null
853
854
855 if (p_party_type is not null
856 and p_party_name is not null
857 and p_party_site_name is not null
858 and p_transaction_type is not null
859 and p_transaction_subtype is not null
860 and p_standard_code is not null
861 and p_ext_trans_type is not null
862 and p_ext_trans_subtype is not null
863 and p_trans_direction is not null) then
864
865 if (p_url is not null or p_ext_application_id is not null
866 or p_application_name is not null
867 or p_application_type is not null
868 or p_application_url is not null
869 or p_logout_url is not null
870 or p_user_field is not null
871 or p_password_field is not null
872 ) then
873 fnd_message.set_name('PER', 'PER_449975_INT_U_E_S_INVALID');
874 fnd_message.raise_error;
875 end if;
876
877 end if;
878
879 hr_utility.set_location('Validating URL:'||l_proc,40);
880 --
881 --for simple URL type of integrations ,SSO details
882 --and ecx details should be null
883
884
885 if p_url is not null then
886
887 if (p_ext_application_id is not null
888 or p_application_name is not null
889 or p_application_type is not null
890 or p_application_url is not null
891 or p_logout_url is not null
892 or p_user_field is not null
893 or p_password_field is not null
894 or p_party_type is not null
895 or p_party_name is not null
896 or p_party_site_name is not null
897 or p_transaction_type is not null
898 or p_transaction_subtype is not null
899 or p_standard_code is not null
900 or p_ext_trans_type is not null
901 or p_ext_trans_subtype is not null
902 or p_trans_direction is not null
903 ) then
904 fnd_message.set_name('PER', 'PER_449975_INT_U_E_S_INVALID');
905 fnd_message.raise_error;
906 end if;
907
908 end if;
909
910
911
912 exception
913 when app_exception.application_exception then
914 if hr_multi_message.exception_add
915 (p_associated_column1 => 'HR_KI_INTEGRATIONS.URL'
916 ,p_associated_column2 => 'HR_KI_INTEGRATIONS.EXT_APPLICATION_ID'
917 ,p_associated_column3 => 'HR_KI_INTEGRATIONS.PARTY_NAME'
918 )then
919 hr_utility.set_location(' Leaving:'||l_proc, 50);
920 raise;
921 end if;
922 hr_utility.set_location(' Leaving:'||l_proc,60);
923 End CHK_INTEGRATION_RECORD;
924
925 -- ----------------------------------------------------------------------------
926 -- -------------------------------< CHK_DELETE>--------------------------------
927 -- ----------------------------------------------------------------------------
928 -- {Start Of Comments}
929 --
930 -- Description:
931 -- This procedure ensures that a delete occurs only if there are no child
932 -- rows for a record in hr_ki_integrations. The tables that contain child
933 -- rows are hr_ki_integrations_tl,hr_ki_topic_integrations,hr_ki_options.
934 -- Pre Conditions:
935 -- g_rec has been populated with details of the values
936 -- from the ins or the upd procedures
937 --
938 -- In Arguments:
939 -- p_integration_id
940
941 -- Post Success:
942 -- Processing continues if there are no child records.
943 --
944 -- Post Failure:
945 -- An application error is raised if there are any child rows from any of the
946 -- above mentioned tables.
947 --
948 -- {End Of Comments}
949 -- ----------------------------------------------------------------------------
950 procedure chk_delete
951 (p_integration_id in number
952 )
953 is
954 --
955 -- Declare local variables
956 --
957 l_proc varchar2(72) := g_package||'chk_delete';
958 l_exists varchar2(1);
959 l_exists_ext varchar2(1);
960 l_exists_ti varchar2(1);
961 l_exists_tl varchar2(1);
962 --
963 -- Cursor to check that if maintenance rows exists.
964 --
965 cursor csr_maintenance_option is
966 select null
967 from hr_ki_options
968 where integration_id = p_integration_id;
969
970 cursor csr_maintenance_ti is
971 select null
972 from hr_ki_topic_integrations
973 where integration_id = p_integration_id;
974
975 cursor csr_maintenance_tl is
976 select null
977 from hr_ki_integrations_tl
978 where integration_id = p_integration_id;
979
980 cursor csr_maintenance_extapp is
981 select null from hr_ki_integrations inte,hr_ki_ext_applications ext
982 where inte.integration_id=p_integration_id
983 and inte.ext_application_id=ext.ext_application_id;
984
985 --
986 begin
987 hr_utility.set_location('Entering:'|| l_proc, 10);
988 --
989 -- Can always execute the cursor as chk_delete
990 -- will only be called for delete validation
991 -- from within the row handler.
992 --
993 open csr_maintenance_option;
994 fetch csr_maintenance_option into l_exists;
995 if csr_maintenance_option%found then
996 close csr_maintenance_option;
997 fnd_message.set_name('PER', 'PER_449979_INT_OPT_MAIN_EXIST');
998 fnd_message.raise_error;
999 end if;
1000 close csr_maintenance_option;
1001
1002 open csr_maintenance_extapp;
1003 fetch csr_maintenance_extapp into l_exists_ext;
1004 if csr_maintenance_extapp%found then
1005 close csr_maintenance_extapp;
1006 fnd_message.set_name('PER', 'PER_449982_INT_EXT_MAIN_EXIST');
1007 fnd_message.raise_error;
1008 end if;
1009 close csr_maintenance_extapp;
1010
1011 hr_utility.set_location('Checking for Topic Integrations:'|| l_proc, 20);
1012 open csr_maintenance_ti;
1013 fetch csr_maintenance_ti into l_exists_ti;
1014 if csr_maintenance_ti%found then
1015 close csr_maintenance_ti;
1016 fnd_message.set_name('PER', 'PER_449980_INT_TOIN_EXIST');
1017 fnd_message.raise_error;
1018 end if;
1019 close csr_maintenance_ti;
1020
1021 hr_utility.set_location('Checking for TL:'|| l_proc, 30);
1022 open csr_maintenance_tl;
1023 fetch csr_maintenance_tl into l_exists_tl;
1024 if csr_maintenance_tl%found then
1025 close csr_maintenance_tl;
1026 fnd_message.set_name('PER', 'PER_449981_INT_TL_EXIST');
1027 fnd_message.raise_error;
1028 end if;
1029 close csr_maintenance_tl;
1030
1031 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1032 exception
1033 when app_exception.application_exception then
1034 if hr_multi_message.exception_add
1035 (p_associated_column1 => 'HR_KI_INTEGRATIONS.integration_id'
1036 ) then
1037 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1038 raise;
1039 end if;
1040 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1041 end chk_delete;
1042
1043
1044 --
1045 -- ----------------------------------------------------------------------------
1046 -- |---------------------------< insert_validate >----------------------------|
1047 -- ----------------------------------------------------------------------------
1048 Procedure insert_validate
1049 (p_rec in out nocopy hr_int_shd.g_rec_type
1050 ) is
1051 --
1052 l_proc varchar2(72) := g_package||'insert_validate';
1053 --
1054 Begin
1055 hr_utility.set_location('Entering:'||l_proc, 5);
1056 --
1057 -- Call all supporting business operations
1058 --
1059 --
1060 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
1061 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
1062 --
1063 -- Validate Dependent Attributes
1064 --
1065 --
1066 CHK_INTEGRATION_KEY
1067 (
1068 p_integration_key => p_rec.integration_key
1069 );
1070
1071 CHK_INTEGRATION_RECORD
1072 (p_url =>p_rec.url
1073 ,p_ext_application_id=>p_rec.ext_application_id
1074 ,p_application_name => p_rec.application_name
1075 ,p_application_type => p_rec.application_type
1076 ,p_application_url => p_rec.application_url
1077 ,p_logout_url => p_rec.logout_url
1078 ,p_user_field => p_rec.user_field
1079 ,p_password_field => p_rec.password_field
1080 ,p_authentication_needed => p_rec.authentication_needed
1081 ,p_party_type =>p_rec.party_type
1082 ,p_party_name =>p_rec.party_name
1083 ,p_party_site_name =>p_rec.party_site_name
1084 ,p_transaction_type =>p_rec.transaction_type
1085 ,p_transaction_subtype =>p_rec.transaction_subtype
1086 ,p_standard_code =>p_rec.standard_code
1087 ,p_ext_trans_type =>p_rec.ext_trans_type
1088 ,p_ext_trans_subtype =>p_rec.ext_trans_subtype
1089 ,p_trans_direction =>p_rec.trans_direction
1090 );
1091
1092 CHK_SSO_DETAILS
1093 (
1094 p_insert => 'insert'
1095 ,p_ext_application_id => p_rec.ext_application_id
1096 ,p_application_name => p_rec.application_name
1097 ,p_application_type => p_rec.application_type
1098 ,p_application_url => p_rec.application_url
1099 ,p_logout_url => p_rec.logout_url
1100 ,p_user_field => p_rec.user_field
1101 ,p_password_field => p_rec.password_field
1102 ,p_authentication_needed => p_rec.authentication_needed
1103 ,p_integration_id => null
1104 );
1105
1106 CHK_ECX_DETAILS
1107 (
1108 p_party_type =>p_rec.party_type
1109 ,p_party_name =>p_rec.party_name
1110 ,p_party_site_name =>p_rec.party_site_name
1111 ,p_transaction_type =>p_rec.transaction_type
1112 ,p_transaction_subtype =>p_rec.transaction_subtype
1113 ,p_standard_code =>p_rec.standard_code
1114 ,p_ext_trans_type =>p_rec.ext_trans_type
1115 ,p_ext_trans_subtype =>p_rec.ext_trans_subtype
1116 ,p_trans_direction =>p_rec.trans_direction
1117 ,p_integration_id => null
1118 );
1119
1120 chk_synched
1121 (
1122 p_synched => p_rec.synched
1123 ,p_url =>p_rec.url
1124 );
1125
1126 hr_utility.set_location(' Leaving:'||l_proc, 10);
1127 End insert_validate;
1128 --
1129 -- ----------------------------------------------------------------------------
1130 -- |---------------------------< update_validate >----------------------------|
1131 -- ----------------------------------------------------------------------------
1132 Procedure update_validate
1133 (p_rec in hr_int_shd.g_rec_type
1134 ) is
1135 --
1136 l_proc varchar2(72) := g_package||'update_validate';
1137 --
1138 Begin
1139 hr_utility.set_location('Entering:'||l_proc, 5);
1140 --
1141 -- Call all supporting business operations
1142 --
1143 --
1144 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
1145 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
1146 --
1147 -- Validate Dependent Attributes
1148 --
1149 chk_non_updateable_args
1150 (p_rec => p_rec
1151 );
1152 CHK_INTEGRATION_RECORD
1153 (p_url =>p_rec.url
1154 ,p_ext_application_id=>p_rec.ext_application_id
1155 ,p_application_name => p_rec.application_name
1156 ,p_application_type => p_rec.application_type
1157 ,p_application_url => p_rec.application_url
1158 ,p_logout_url => p_rec.logout_url
1159 ,p_user_field => p_rec.user_field
1160 ,p_password_field => p_rec.password_field
1161 ,p_authentication_needed => p_rec.authentication_needed
1162 ,p_party_type =>p_rec.party_type
1163 ,p_party_name =>p_rec.party_name
1164 ,p_party_site_name =>p_rec.party_site_name
1165 ,p_transaction_type =>p_rec.transaction_type
1166 ,p_transaction_subtype =>p_rec.transaction_subtype
1167 ,p_standard_code =>p_rec.standard_code
1168 ,p_ext_trans_type =>p_rec.ext_trans_type
1169 ,p_ext_trans_subtype =>p_rec.ext_trans_subtype
1170 ,p_trans_direction =>p_rec.trans_direction
1171 );
1172
1173 CHK_EXT_APPLICATION_ID_UPD
1174 (
1175 p_ext_application_id=>p_rec.ext_application_id
1176 ,p_integration_id =>p_rec.integration_id
1177 );
1178
1179 CHK_SSO_DETAILS
1180 (
1181 p_insert => 'update'
1182 ,p_ext_application_id => p_rec.ext_application_id
1183 ,p_application_name => p_rec.application_name
1184 ,p_application_type => p_rec.application_type
1185 ,p_application_url => p_rec.application_url
1186 ,p_logout_url => p_rec.logout_url
1187 ,p_user_field => p_rec.user_field
1188 ,p_password_field => p_rec.password_field
1189 ,p_authentication_needed => p_rec.authentication_needed
1190 ,p_integration_id => p_rec.integration_id
1191 );
1192
1193 CHK_ECX_DETAILS
1194 (
1195 p_party_type =>p_rec.party_type
1196 ,p_party_name =>p_rec.party_name
1197 ,p_party_site_name =>p_rec.party_site_name
1198 ,p_transaction_type =>p_rec.transaction_type
1199 ,p_transaction_subtype =>p_rec.transaction_subtype
1200 ,p_standard_code =>p_rec.standard_code
1201 ,p_ext_trans_type =>p_rec.ext_trans_type
1202 ,p_ext_trans_subtype =>p_rec.ext_trans_subtype
1203 ,p_trans_direction =>p_rec.trans_direction
1204 ,p_integration_id => p_rec.integration_id
1205 );
1206
1207 chk_synched_upd
1208 (
1209 p_synched => p_rec.synched
1210 ,p_url =>p_rec.url
1211 );
1212
1213 --
1214 --
1215 hr_utility.set_location(' Leaving:'||l_proc, 10);
1216 End update_validate;
1217 --
1218 -- ----------------------------------------------------------------------------
1219 -- |---------------------------< delete_validate >----------------------------|
1220 -- ----------------------------------------------------------------------------
1221 Procedure delete_validate
1222 (p_rec in hr_int_shd.g_rec_type
1223 ) is
1224 --
1225 l_proc varchar2(72) := g_package||'delete_validate';
1226 --
1227 Begin
1228 hr_utility.set_location('Entering:'||l_proc, 5);
1229 --
1230 -- Call all supporting business operations
1231 --
1232 CHK_DELETE
1233 (
1234 p_integration_id =>p_rec.integration_id
1235 );
1236 hr_utility.set_location(' Leaving:'||l_proc, 10);
1237 End delete_validate;
1238 --
1239 end hr_int_bus;