1 Package Body irc_ilc_bus as
2 /* $Header: irilcrhi.pkb 120.1 2010/05/26 09:57:32 chsharma noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ilc_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_link_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_link_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- IRC_LINKED_CANDIDATES and PER_BUSINESS_GROUPS_PERF
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id,
34 pbg.legislation_code
35 from per_business_groups_perf pbg
36 , IRC_LINKED_CANDIDATES ilc
37 -- , EDIT_HERE table_name(s) 333
38 where ilc.link_id = p_link_id;
39 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 l_proc varchar2(72) := g_package||'set_security_group_id';
45 l_legislation_code varchar2(150);
46 --
47 begin
48 --
49 hr_utility.set_location('Entering:'|| l_proc, 10);
50 --
51 -- Ensure that all the mandatory parameter are not null
52 --
53 hr_api.mandatory_arg_error
54 (p_api_name => l_proc
55 ,p_argument => 'LINK_ID'
56 ,p_argument_value => p_link_id
57 );
58 --
59 open csr_sec_grp;
60 fetch csr_sec_grp into l_security_group_id
61 , l_legislation_code;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 hr_multi_message.add
71 (p_associated_column1
72 => nvl(p_associated_column1,'LINK_ID')
73 );
74 --
75 else
76 close csr_sec_grp;
77 --
78 -- Set the security_group_id in CLIENT_INFO
79 --
80 hr_api.set_security_group_id
81 (p_security_group_id => l_security_group_id
82 );
83 --
84 -- Set the sessions legislation context in HR_SESSION_DATA
85 --
86 hr_api.set_legislation_context(l_legislation_code);
87 end if;
88 --
89 hr_utility.set_location(' Leaving:'|| l_proc, 20);
90 --
91 end set_security_group_id;
92 --
93 -- ---------------------------------------------------------------------------
94 -- |---------------------< return_legislation_code >-------------------------|
95 -- ---------------------------------------------------------------------------
96 --
97 Function return_legislation_code
98 (p_link_id in number
99 )
100 Return Varchar2 Is
101 --
102 -- Declare cursor
103 --
104 -- EDIT_HERE In the following cursor statement add join(s) between
105 -- IRC_LINKED_CANDIDATES and PER_BUSINESS_GROUPS_PERF
106 -- so that the legislation_code for
107 -- the current business group context can be derived.
108 -- Remove this comment when the edit has been completed.
109 cursor csr_leg_code is
110 select pbg.legislation_code
111 from per_business_groups_perf pbg
112 , IRC_LINKED_CANDIDATES ilc
113 -- , EDIT_HERE table_name(s) 333
114 where ilc.link_id = p_link_id;
115 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
116 --
117 -- Declare local variables
118 --
119 l_legislation_code varchar2(150);
120 l_proc varchar2(72) := g_package||'return_legislation_code';
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'|| l_proc, 10);
125 --
126 -- Ensure that all the mandatory parameter are not null
127 --
128 hr_api.mandatory_arg_error
129 (p_api_name => l_proc
130 ,p_argument => 'LINK_ID'
131 ,p_argument_value => p_link_id
132 );
133 --
134 if ( nvl(irc_ilc_bus.g_link_id, hr_api.g_number)
135 = p_link_id) then
136 --
137 -- The legislation code has already been found with a previous
138 -- call to this function. Just return the value in the global
139 -- variable.
140 --
141 l_legislation_code := irc_ilc_bus.g_legislation_code;
142 hr_utility.set_location(l_proc, 20);
143 else
144 --
145 -- The ID is different to the last call to this function
146 -- or this is the first call to this function.
147 --
148 open csr_leg_code;
149 fetch csr_leg_code into l_legislation_code;
150 --
151 if csr_leg_code%notfound then
152 --
153 -- The primary key is invalid therefore we must error
154 --
155 close csr_leg_code;
156 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
157 fnd_message.raise_error;
158 end if;
159 hr_utility.set_location(l_proc,30);
160 --
161 -- Set the global variables so the values are
162 -- available for the next call to this function.
163 --
164 close csr_leg_code;
165 irc_ilc_bus.g_link_id := p_link_id;
166 irc_ilc_bus.g_legislation_code := l_legislation_code;
167 end if;
168 hr_utility.set_location(' Leaving:'|| l_proc, 40);
169 return l_legislation_code;
170 end return_legislation_code;
171 --
172 -- ----------------------------------------------------------------------------
173 -- |-----------------------< chk_non_updateable_args >------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This procedure is used to ensure that non updateable attributes have
179 -- not been updated. If an attribute has been updated an error is generated.
180 --
181 -- Pre Conditions:
182 -- g_old_rec has been populated with details of the values currently in
183 -- the database.
184 --
185 -- In Arguments:
186 -- p_rec has been populated with the updated values the user would like the
187 -- record set to.
188 --
189 -- Post Success:
190 -- Processing continues if all the non updateable attributes have not
191 -- changed.
192 --
193 -- Post Failure:
194 -- An application error is raised if any of the non updatable attributes
195 -- have been altered.
196 --
197 -- {End Of Comments}
198 -- ----------------------------------------------------------------------------
199 Procedure chk_non_updateable_args
200 (p_effective_date in date
201 ,p_rec in irc_ilc_shd.g_rec_type
202 ) IS
203 --
204 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
205 --
206 Begin
207 --
208 -- Only proceed with the validation if a row exists for the current
209 -- record in the HR Schema.
210 --
211 IF NOT irc_ilc_shd.api_updating
212 (p_link_id => p_rec.link_id
213 ,p_object_version_number => p_rec.object_version_number
214 ) THEN
215 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
216 fnd_message.set_token('PROCEDURE ', l_proc);
217 fnd_message.set_token('STEP ', '5');
218 fnd_message.raise_error;
219 END IF;
220 --
221 -- EDIT_HERE: Add checks to ensure non-updateable args have
222 -- not been updated.
223 if p_rec.party_id <> irc_ilc_shd.g_old_rec.party_id
224 then
225 hr_api.argument_changed_error
226 (p_api_name => l_proc
227 ,p_argument => 'PARTY_ID'
228 ,p_base_table => irc_ilc_shd.g_tab_nam
229 );
230 end if;
231 --
232 if p_rec.duplicate_set_id <> irc_ilc_shd.g_old_rec.duplicate_set_id
233 then
234 hr_api.argument_changed_error
235 (p_api_name => l_proc
236 ,p_argument => 'DUPLICATE_SET_ID'
237 ,p_base_table => irc_ilc_shd.g_tab_nam
238 );
239 end if;
240 --
241 End chk_non_updateable_args;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |-----------------------------< chk_party_id >-----------------------------|
245 -- ----------------------------------------------------------------------------
246 -- {Start Of Comments}
247 --
248 -- Description:
249 -- This procedure is used to ensure that party_id exists in PER_ALL_PEOPLE_F
250 --
251 -- Pre Conditions:
252 --
253 -- In Arguments:
254 -- p_party_id
255 --
256 -- Post Success:
257 -- Processing continues if p_party_id is valid.
258 --
259 -- Post Failure:
260 -- An application error is raised if p_party_id is invalid.
261 --
262 -- {End Of Comments}
263 -- ----------------------------------------------------------------------------
264 Procedure chk_party_id
265 (p_party_id in irc_linked_candidates.party_id%TYPE
266 ,p_effective_date in Date
267 ) IS
268 --
269 l_proc varchar2(72) := g_package || 'chk_party_id';
270 l_party_id varchar2(1);
271 --
272 cursor csr_party_id is
273 select null
274 from per_all_people_f ppf
275 where ppf.party_id = p_party_id
276 and trunc(p_effective_date) between ppf.effective_start_date
277 and ppf.effective_end_date;
278 --
279 begin
280 hr_utility.set_location('Entering:'||l_proc,10);
281 -- Check that Party_ID(Object_id) exists in per_all_people_f
282 open csr_party_id;
283 fetch csr_party_id into l_party_id;
284 hr_utility.set_location(l_proc,20);
285 if csr_party_id%NOTFOUND then
286 close csr_party_id;
287 fnd_message.set_name('PER','IRC_412008_BAD_PARTY_PERSON_ID');
288 fnd_message.raise_error;
289 end if;
290 close csr_party_id;
291 hr_utility.set_location(' Leaving:'||l_proc,30);
292 exception
293 when app_exception.application_exception then
294 if hr_multi_message.exception_add
295 (p_associated_column1 =>
296 'IRC_LINKED_CANDIDATES.PARTY_ID'
297 ) then
298 hr_utility.set_location(' Leaving:'||l_proc,40);
299 raise;
300 end if;
301 hr_utility.set_location(' Leaving:'||l_proc,50);
302 end chk_party_id;
303 --
304 -- ----------------------------------------------------------------------------
305 -- |-----------------------------< chk_status >-------------------------------|
306 -- ----------------------------------------------------------------------------
307 Procedure chk_status
308 (p_status in irc_linked_candidates.status%TYPE
309 ,p_effective_date in date
310 ) is
311 --
312 l_proc varchar2(72) := g_package||'chk_status';
313 l_not_exists boolean;
314 Begin
315 hr_utility.set_location('Entering:'||l_proc, 5);
316 --
317 if p_status is not null then
318 l_not_exists := hr_api.not_exists_in_hr_lookups
319 (p_effective_date
320 ,'IRC_CAND_LINK_STATUS'
321 ,p_status
322 );
323 hr_utility.set_location(l_proc, 10);
324 if (l_not_exists = true) then
325 -- RAISE ERROR SAYING THAT THE STATUS IS INVALID
326 fnd_message.set_name('PER','IRC_412628_INV_LINK_STATUS');
327 fnd_message.raise_error;
328 end if;
329 end if;
330 hr_utility.set_location('leaving:'||l_proc, 15);
331 end chk_status;
332 --
333 -- ----------------------------------------------------------------------------
334 -- |-----------------------------< chk_cand_link >----------------------------|
335 -- ----------------------------------------------------------------------------
336 Procedure chk_cand_link
337 (p_party_id in irc_linked_candidates.party_id%TYPE
338 ,p_duplicate_set_id in irc_linked_candidates.duplicate_set_id%TYPE
339 ) is
340 --
341 l_proc varchar2(72) := g_package||'chk_cand_link';
342 l_cand_linked varchar2(1);
343 --
344 cursor csr_cand_link is
345 select null
346 from irc_linked_candidates ilc
347 where ilc.party_id = p_party_id
348 and ilc.duplicate_set_id <> p_duplicate_set_id
349 and ilc.status in ('CONFIRMED','POTENTIAL');
350 --
351 Begin
352 hr_utility.set_location('Entering:'||l_proc, 10);
353 --
354 open csr_cand_link;
355 fetch csr_cand_link into l_cand_linked;
356 --
357 hr_utility.set_location(l_proc,20);
358 if csr_cand_link%FOUND then
359 close csr_cand_link;
360 fnd_message.set_name('PER','IRC_412629_ALREADY_LINKED');
361 fnd_message.raise_error;
362 end if;
363 close csr_cand_link;
364 hr_utility.set_location(' Leaving:'||l_proc,30);
365 --
366 exception
367 when app_exception.application_exception then
368 if hr_multi_message.exception_add
369 (p_associated_column1 =>
370 'IRC_LINKED_CANDIDATES.PARTY_ID'
371 ) then
372 hr_utility.set_location(' Leaving:'||l_proc,40);
373 raise;
374 end if;
375 hr_utility.set_location(' Leaving:'||l_proc,50);
376 --
377 end chk_cand_link;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |-----------------------------< chk_duplcate_set >-------------------------|
381 -- ----------------------------------------------------------------------------
382 Procedure chk_duplcate_set
383 (p_party_id in irc_linked_candidates.party_id%TYPE
384 ,p_duplicate_set_id in irc_linked_candidates.duplicate_set_id%TYPE
385 ) is
386 --
387 l_proc varchar2(72) := g_package||'chk_duplcate_set';
388 l_dup_set varchar2(1);
389 --
390 cursor csr_duplcate_set is
391 select null
392 from irc_linked_candidates ilc
393 where ilc.party_id = p_party_id
394 and ilc.duplicate_set_id = p_duplicate_set_id;
395 --
396 Begin
397 hr_utility.set_location('Entering:'||l_proc, 10);
398 --
399 open csr_duplcate_set;
400 fetch csr_duplcate_set into l_dup_set;
401 --
402 hr_utility.set_location(l_proc,20);
403 if csr_duplcate_set%FOUND then
404 close csr_duplcate_set;
405 fnd_message.set_name('PER','IRC_412630_PTY_MARKED_AS_DUP');
406 fnd_message.raise_error;
407 end if;
408 close csr_duplcate_set;
409 hr_utility.set_location(' Leaving:'||l_proc,30);
410 --
411 exception
412 when app_exception.application_exception then
413 if hr_multi_message.exception_add
414 (p_associated_column1 =>
415 'IRC_LINKED_CANDIDATES.PARTY_ID'
416 ,p_associated_column2 =>
417 'IRC_LINKED_CANDIDATES.DUPLICATE_SET_ID'
418 ) then
419 hr_utility.set_location(' Leaving:'||l_proc,40);
420 raise;
421 end if;
422 hr_utility.set_location(' Leaving:'||l_proc,50);
423 --
424 end chk_duplcate_set;
425 --
426 -- ----------------------------------------------------------------------------
427 -- |---------------------------< insert_validate >----------------------------|
428 -- ----------------------------------------------------------------------------
429 Procedure insert_validate
430 (p_effective_date in date
431 ,p_rec in irc_ilc_shd.g_rec_type
432 ) is
433 --
434 l_proc varchar2(72) := g_package||'insert_validate';
435 --
436 Begin
437 hr_utility.set_location('Entering:'||l_proc, 10);
438 --
439 -- Call all supporting business operations
440 --
441 --
442 -- EDIT_HERE: As this table does not have a mandatory business_group_id
443 -- column, ensure client_info is populated by calling a suitable
444 -- ???_???_bus.set_security_group_id procedure, or add one of the following
445 -- comments:
446 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
447 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
448 --
449 -- Validate Dependent Attributes
450 --
451 chk_party_id
452 (p_party_id => p_rec.party_id
453 ,p_effective_date => p_effective_date);
454 --
455 hr_utility.set_location(' Leaving:'||l_proc, 20);
456 --
457 if p_rec.target_party_id is not null then
458 --
459 chk_party_id
460 (p_party_id => p_rec.target_party_id
461 ,p_effective_date => p_effective_date);
462 --
463 end if;
464 --
465 hr_utility.set_location(' Leaving:'||l_proc, 30);
466 --
467 chk_status(p_status => p_rec.status
468 ,p_effective_date => p_effective_date);
469 --
470 hr_utility.set_location(' Leaving:'||l_proc, 40);
471 --
472 if p_rec.status in ('CONFIRMED','POTENTIAL') then
473 --
474 chk_cand_link(p_party_id => p_rec.party_id
475 ,p_duplicate_set_id => p_rec.duplicate_set_id);
476 --
477 end if;
478 --
479 hr_utility.set_location(' Leaving:'||l_proc, 50);
480 --
481 chk_duplcate_set(p_party_id => p_rec.party_id
482 ,p_duplicate_set_id => p_rec.duplicate_set_id
483 );
484 --
485 hr_utility.set_location(' Leaving:'||l_proc, 60);
486 --
487 End insert_validate;
488 --
489 -- ----------------------------------------------------------------------------
490 -- |---------------------------< update_validate >----------------------------|
491 -- ----------------------------------------------------------------------------
492 Procedure update_validate
493 (p_effective_date in date
494 ,p_rec in irc_ilc_shd.g_rec_type
495 ) is
496 --
497 l_proc varchar2(72) := g_package||'update_validate';
498 --
499 Begin
500 hr_utility.set_location('Entering:'||l_proc, 10);
501 --
502 -- Call all supporting business operations
503 --
504 --
505 -- EDIT_HERE: As this table does not have a mandatory business_group_id
506 -- column, ensure client_info is populated by calling a suitable
507 -- ???_???_bus.set_security_group_id procedure, or add one of the following
508 -- comments:
509 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
510 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
511 --
512 -- Validate Dependent Attributes
513 --
514 chk_non_updateable_args
515 (p_effective_date => p_effective_date
516 ,p_rec => p_rec
517 );
518 --
519 hr_utility.set_location(' Leaving:'||l_proc, 20);
520 --
521 chk_party_id
522 (p_party_id => p_rec.party_id
523 ,p_effective_date => p_effective_date);
524 --
525 hr_utility.set_location(' Leaving:'||l_proc, 30);
526 --
527 if p_rec.target_party_id is not null then
528 --
529 chk_party_id
530 (p_party_id => p_rec.target_party_id
531 ,p_effective_date => p_effective_date);
532 --
533 end if;
534 --
535 hr_utility.set_location(' Leaving:'||l_proc, 40);
536 --
537 chk_status(p_status => p_rec.status
538 ,p_effective_date => p_effective_date);
539 --
540 hr_utility.set_location(' Leaving:'||l_proc, 50);
541 --
542 if p_rec.status in ('CONFIRMED','POTENTIAL') then
543 --
544 chk_cand_link(p_party_id => p_rec.party_id
545 ,p_duplicate_set_id => p_rec.duplicate_set_id);
546 --
547 end if;
548 --
549 hr_utility.set_location(' Leaving:'||l_proc, 60);
550 --
551 End update_validate;
552 --
553 -- ----------------------------------------------------------------------------
554 -- |---------------------------< delete_validate >----------------------------|
555 -- ----------------------------------------------------------------------------
556 Procedure delete_validate
557 (p_rec in irc_ilc_shd.g_rec_type
558 ) is
559 --
560 l_proc varchar2(72) := g_package||'delete_validate';
561 --
562 Begin
563 hr_utility.set_location('Entering:'||l_proc, 5);
564 --
565 -- Call all supporting business operations
566 --
567 hr_utility.set_location(' Leaving:'||l_proc, 10);
568 End delete_validate;
569 --
570 end irc_ilc_bus;
[Home] [Help]