1 Package Body irc_ivc_bus as
2 /* $Header: irivcrhi.pkb 120.0 2005/07/26 15:12:26 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ivc_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_party_id number default null;
15 g_vacancy_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_vacancy_consideration_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25
26 -- Declare local variables
27 --
28 l_security_group_id number;
29 l_proc varchar2(72) := g_package||'set_security_group_id';
30 --
31 begin
32 --
33 hr_utility.set_location('Entering:'|| l_proc, 10);
34 --
35 -- Ensure that all the mandatory parameter are not null
36 --
37 hr_api.mandatory_arg_error
38 (p_api_name => l_proc
39 ,p_argument => 'vacancy_consideration_id'
40 ,p_argument_value => p_vacancy_consideration_id
41 );
42 --
43 hr_utility.set_location(' Leaving:'|| l_proc, 20);
44 --
45 end set_security_group_id;
46 --
47 -- ---------------------------------------------------------------------------
48 -- |---------------------< return_legislation_code >-------------------------|
49 -- ---------------------------------------------------------------------------
50 --
51 Function return_legislation_code
52 (p_vacancy_consideration_id in number
53 )
54 Return Varchar2 Is
55 --
56 -- Declare cursor
57 --
58 --
59 -- Declare local variables
60 --
61 l_legislation_code varchar2(150);
62 l_proc varchar2(72) := g_package||'return_legislation_code';
63 --
64 Begin
65 --
66 hr_utility.set_location('Entering:'|| l_proc, 10);
67 --
68 -- Ensure that all the mandatory parameter are not null
69 --
70 hr_api.mandatory_arg_error
71 (p_api_name => l_proc
72 ,p_argument => 'vacancy_consideration_id'
73 ,p_argument_value => p_vacancy_consideration_id
74 );
75 --
76
77 hr_utility.set_location(' Leaving:'|| l_proc, 40);
78 return l_legislation_code;
79 end return_legislation_code;
80 --
81 -- ----------------------------------------------------------------------------
82 -- |-----------------------< chk_non_updateable_args >------------------------|
83 -- ----------------------------------------------------------------------------
84 -- {Start Of Comments}
85 --
86 -- Description:
87 -- This procedure is used to ensure that non updateable attributes have
88 -- not been updated. If an attribute has been updated an error is generated.
89 --
90 -- Pre Conditions:
91 -- g_old_rec has been populated with details of the values currently in
92 -- the database.
93 --
94 -- In Arguments:
95 -- p_rec has been populated with the updated values the user would like the
96 -- record set to.
97 --
98 -- Post Success:
99 -- Processing continues if all the non updateable attributes have not
100 -- changed.
101 --
102 -- Post Failure:
103 -- An application error is raised if any of the non updatable attributes
104 -- have been altered.
105 --
106 -- {End Of Comments}
107 -- ----------------------------------------------------------------------------
108 Procedure chk_non_updateable_args
109 (p_effective_date in date
110 ,p_rec in irc_ivc_shd.g_rec_type
111 ) IS
112 --
113 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
114 --
115 Begin
116 --
117 -- Only proceed with the validation if a row exists for the current
118 -- record in the HR Schema.
119 --
120 IF NOT irc_ivc_shd.api_updating
121 (p_vacancy_consideration_id => p_rec.vacancy_consideration_id
122 ,p_object_version_number => p_rec.object_version_number
123 ) THEN
124 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
125 fnd_message.set_token('PROCEDURE ', l_proc);
126 fnd_message.set_token('STEP ', '5');
127 fnd_message.raise_error;
128 END IF;
129 --
130 -- Checks to ensure non-updateable args have
131 -- not been updated.
132 if p_rec.vacancy_consideration_id <> irc_ivc_shd.g_old_rec.vacancy_consideration_id
133 then
134 hr_api.argument_changed_error
135 (p_api_name => l_proc
136 ,p_argument => 'VACANCY_CONSIDERATION_ID'
137 ,p_base_table => irc_ivc_shd.g_tab_nam
138 );
139 end if;
140 --
141 if p_rec.vacancy_id <> irc_ivc_shd.g_old_rec.vacancy_id
142 then
143 hr_api.argument_changed_error
144 (p_api_name => l_proc
145 ,p_argument => 'VACANCY_ID'
146 ,p_base_table => irc_ivc_shd.g_tab_nam
147 );
148 end if;
149 --
150 if p_rec.person_id <> irc_ivc_shd.g_old_rec.person_id
151 then
152 hr_api.argument_changed_error
153 (p_api_name => l_proc
154 ,p_argument => 'PERSON_ID'
155 ,p_base_table => irc_ivc_shd.g_tab_nam
156 );
157 end if;
158 --
159 End chk_non_updateable_args;
160 --
161 -- ----------------------------------------------------------------------------
162 -- |-----------------------< chk_vacancy_id >---------------------------------|
163 -- ----------------------------------------------------------------------------
164 -- {Start Of Comments}
165 --
166 -- Description:
167 -- This procedure is used to ensure that mandatory parameters have been set.
168 -- If the vacancy id is not found in per_all_vacancies an error is generated.
169 --
170 -- Pre Conditions:
171 -- g_old_rec has been populated with details of the values currently in
172 -- the database.
173 --
174 -- In Arguments:
175 -- p_vacancy_id
176 -- p_consideration_status
177 -- p_effective_date
178 --
179 -- Post Success:
180 -- Processing continues if the mandatory parameters have been set and the
181 -- specified vacancy id exists.
182 --
183 -- Post Failure:
184 -- An application error is raised if the vacancy id does not exist.
185 --
186 -- {End Of Comments}
187 -- ----------------------------------------------------------------------------
188 Procedure chk_vacancy_id
189 (p_vacancy_id in irc_vacancy_considerations.vacancy_id%type
190 ,p_consideration_status in irc_vacancy_considerations.consideration_status%type
191 ,p_effective_date date
192 ) IS
193 --
194 l_proc varchar2(72) := g_package || 'chk_vacancy_id';
195 l_date_from per_all_vacancies.date_from%type;
196 l_date_to per_all_vacancies.date_to%type;
197 --
198 --
199 -- Cursor to check if the vacancy is future-dated or past-dated
200 -- and display the appropriate error messages
201 --
202 cursor csr_vacancy_dates is
203 select date_from
204 ,date_to
205 from per_all_vacancies
206 where vacancy_id = p_vacancy_id;
207 --
208 --
209 Begin
210 --
211 hr_utility.set_location(' Entering:'||l_proc,10);
212 --
213 hr_api.mandatory_arg_error
214 (p_api_name => l_proc
215 ,p_argument => 'vacancy_id'
216 ,p_argument_value => p_vacancy_id
217 );
218 --
219 -- Check if the vacancy is future-dated or past-dated.
220 -- and display the appropriate error messages.
221 --
222 open csr_vacancy_dates;
223 fetch csr_vacancy_dates into l_date_from,l_date_to;
224 hr_utility.set_location(l_proc, 30);
225 if csr_vacancy_dates%notfound then
226 close csr_vacancy_dates;
227 fnd_message.set_name('PER','IRC_412032_RTM_INV_VACANCY_ID');
228 fnd_message.raise_error;
229 --
230 elsif (p_effective_date > l_date_to) then
231 hr_utility.set_location(l_proc, 35);
232 close csr_vacancy_dates;
233 fnd_message.set_name('PER','IRC_412132_CLOSED_VACANCY_ID');
234 fnd_message.raise_error;
235 --
236 elsif (l_date_from > p_effective_date and p_consideration_status = 'PURSUE')
237 then
238 hr_utility.set_location(l_proc, 37);
239 close csr_vacancy_dates;
240 fnd_message.set_name('PER','IRC_FUTURE_VACANCY_ID');
241 fnd_message.raise_error;
242 --
243 end if;
244 close csr_vacancy_dates;
245 --
246 hr_utility.set_location(' Leaving:'||l_proc,40);
247 exception
248 when app_exception.application_exception then
249 if hr_multi_message.exception_add
250 (p_associated_column1 => 'IRC_VACANCY_CONSIDERATIONS.VACANCY_ID'
251 ) then
252 hr_utility.set_location(' Leaving:'|| l_proc, 50);
253 raise;
254 end if;
255 hr_utility.set_location(' Leaving:'|| l_proc, 60);
256 --
257 End chk_vacancy_id;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |-----------------------< chk_person_id >-----------------------------------|
261 -- ----------------------------------------------------------------------------
262 -- {Start Of Comments}
263 --
264 -- Description:
265 -- This procedure is used to ensure that mandatory parameters have been set.
266 -- If the person id is not found in per_all_people_f an error is generated.
267 --
268 -- Pre Conditions:
269 -- g_old_rec has been populated with details of the values currently in
270 -- the database.
271 --
272 -- In Arguments:
273 -- p_party_id
274 --
275 -- Post Success:
276 -- Processing continues if the mandatory parameters have been set and the
277 -- specified party id exists.
278 --
279 -- Post Failure:
280 -- An application error is raised if the party id does not exist.
281 --
282 -- {End Of Comments}
283 -- ----------------------------------------------------------------------------
284 Procedure chk_person_id
285 (p_person_id in irc_vacancy_considerations.person_id%type
286 ,p_party_id in out nocopy irc_vacancy_considerations.party_id%type
287 ,p_effective_date date
288 ) IS
289 --
290 l_proc varchar2(72) := g_package || 'chk_person_id';
291 l_party_id irc_notification_preferences.party_id%type;
292 l_var varchar2(30);
293 --
294 --
295 -- Cursor to check that the person_id exists in PER_ALL_PEOPLE_F.
296 --
297 cursor csr_person_id is
298 select party_id
299 from per_all_people_f
300 where person_id = p_person_id;
301 --
302 -- Cursor to check that the Person can be contacted ie., to check
303 -- if the ALLOW_ACCESS = 'Y' in irc_notification_preferences
304 --
305 cursor csr_not_pref is
306 select null
307 from IRC_NOTIFICATION_PREFERENCES
308 where person_id = p_person_id
309 and allow_access = 'Y';
310 Begin
311 --
312 hr_utility.set_location(' Entering:'||l_proc,10);
313 --
314 -- Check if the person_id exists in PER_ALL_PEOPLE_F.
315 --
316 open csr_person_id;
317 fetch csr_person_id into l_party_id;
318 hr_utility.set_location(l_proc, 30);
319 if csr_person_id%notfound then
320 close csr_person_id;
321 fnd_message.set_name('PER','IRC_412157_PARTY_PERS_MISMTCH');
322 fnd_message.raise_error;
323 end if;
324 close csr_person_id;
325 if p_party_id is not null then
326 if p_party_id<>l_party_id then
327 fnd_message.set_name('PER','IRC_412033_RTM_INV_PARTY_ID');
328 fnd_message.raise_error;
329 end if;
330 else
331 p_party_id:=l_party_id;
332 end if;
333 --
334 -- Check if the person can be contacted
335 --
336 open csr_not_pref;
337 fetch csr_not_pref into l_var;
338 hr_utility.set_location(l_proc, 40);
339 if csr_not_pref%notfound then
340 close csr_not_pref;
341 fnd_message.set_name('PER','IRC_412047_IVC_NO_ALLOW_ACCESS');
342 fnd_message.raise_error;
343 end if;
344 close csr_not_pref;
345 --
346 hr_utility.set_location(' Leaving:'||l_proc,70);
347 exception
348 when app_exception.application_exception then
349 if hr_multi_message.exception_add
350 (p_associated_column1 => 'IRC_VACANCY_CONSIDERATIONS.PARTY_ID'
351 ) then
352 hr_utility.set_location(' Leaving:'|| l_proc, 80);
353 raise;
354 end if;
355 hr_utility.set_location(' Leaving:'|| l_proc, 90);
356 --
357 End chk_person_id;
358 --
359 -- ----------------------------------------------------------------------------
360 -- |-----------------------< chk_consideration_status >-----------------------|
361 -- ----------------------------------------------------------------------------
362 -- {Start Of Comments}
363 --
364 -- Description:
365 -- This procedure is used to ensure a valid 'Consideration Status' value.
366 --
367 -- Pre Conditions:
368 -- g_old_rec has been populated with details of the values currently in
369 -- the database.
370 --
371 -- In Arguments:
372 -- p_consideration_status
373 -- p_party_id
374 -- p_vacancy_id
375 -- p_object_version_number
376 -- p_effective_date
377 --
378 -- Post Success:
379 -- Processing continues if a valid 'Consideration Status' value is entered.
380 --
381 -- Post Failure:
382 -- An application error is raised if a valid 'Consideration Status' value
383 -- is not entered.
384 --
385 -- {End Of Comments}
386 -- ----------------------------------------------------------------------------
387 Procedure chk_consideration_status
388 (p_consideration_status in irc_vacancy_considerations.
389 consideration_status%type
390 ,p_vacancy_consideration_id in irc_vacancy_considerations.vacancy_consideration_id%type
391 ,p_object_version_number in irc_vacancy_considerations.
392 object_version_number%type
393 ,p_effective_date in date
394 ) IS
395 --
396 l_proc varchar2(72) := g_package || 'chk_consideration_status';
397 l_var boolean;
398 l_api_updating boolean;
399 --
400 Begin
401 --
402 hr_utility.set_location(' Entering:'||l_proc,10);
403 --
404 l_api_updating := irc_ivc_shd.api_updating(p_vacancy_consideration_id
405 ,p_object_version_number);
406 --
407 -- Check to see if the consideration_status value has changed
408 --
409 hr_utility.set_location(l_proc, 20);
410 if ((l_api_updating
411 and (irc_ivc_shd.g_old_rec.consideration_status <>
412 p_consideration_status))
413 or (NOT l_api_updating)) then
414 --
415 -- Check that a valid 'Consideration Status' value is entered.
416 --
417 l_var := hr_api.not_exists_in_hr_lookups
418 (p_effective_date
419 ,'IRC_CONSIDERATION'
420 ,p_consideration_status
421 );
422 hr_utility.set_location(l_proc, 30);
423 if (l_var = true) then
424 fnd_message.set_name('PER','IRC_412048_IVC_INV_CONS_STATUS');
425 fnd_message.raise_error;
426 end if;
427 --
428 -- Check that the updated consideration status value is 'Pursue' if the
429 -- old value was 'Pursue'
430 --
434 fnd_message.set_name('PER','IRC_412049_IVC_INV_UPD_CONS_ST');
431 hr_utility.set_location(l_proc, 40);
432 if(l_api_updating and irc_ivc_shd.g_old_rec.consideration_status = 'PURSUE'
433 and (p_consideration_status <> 'PURSUE')) then
435 fnd_message.raise_error;
436 end if;
437 end if;
438 hr_utility.set_location(' Leaving:'||l_proc,50);
439 --
440 exception
441 when app_exception.application_exception then
442 if hr_multi_message.exception_add
443 (p_associated_column1 =>
444 irc_ivc_shd.g_tab_nam||'.CONSIDERATION_STATUS'
445 ) then
446 hr_utility.set_location(' Leaving:'|| l_proc, 80);
447 raise;
448 end if;
449 hr_utility.set_location(' Leaving:'|| l_proc, 90);
450 --
451 End chk_consideration_status;
452 --
453 -- ----------------------------------------------------------------------------
454 -- |---------------------------< insert_validate >----------------------------|
455 -- ----------------------------------------------------------------------------
456 Procedure insert_validate
457 (p_effective_date in date
458 ,p_rec in out nocopy irc_ivc_shd.g_rec_type
459 ) is
460 --
461 l_proc varchar2(72) := g_package||'insert_validate';
462 --
463 Begin
464 hr_utility.set_location('Entering:'||l_proc, 5);
465 --
466 -- Call all supporting business operations
467 --
468 hr_utility.set_location(l_proc, 10);
469 chk_person_id
470 (p_person_id =>p_rec.person_id
471 ,p_party_id => p_rec.party_id
472 ,p_effective_date=>p_effective_date
473 );
474 --
475 hr_utility.set_location(l_proc, 20);
476 chk_vacancy_id
477 (p_vacancy_id => p_rec.vacancy_id
478 ,p_consideration_status => p_rec.consideration_status
479 ,p_effective_date => p_effective_date
480 );
481 --
482 hr_utility.set_location(l_proc, 40);
483 chk_consideration_status
484 (p_consideration_status => p_rec.consideration_status
485 ,p_effective_date => p_effective_date
486 ,p_vacancy_consideration_id => p_rec.vacancy_consideration_id
487 ,p_object_version_number => p_rec.object_version_number
488 );
489 --
490 hr_utility.set_location(' Leaving:'||l_proc, 60);
491 End insert_validate;
492 --
493 -- ----------------------------------------------------------------------------
494 -- |---------------------------< update_validate >----------------------------|
495 -- ----------------------------------------------------------------------------
496 Procedure update_validate
497 (p_effective_date in date
498 ,p_rec in irc_ivc_shd.g_rec_type
499 ) is
500 --
501 l_proc varchar2(72) := g_package||'update_validate';
502 --
503 Begin
504 hr_utility.set_location('Entering:'||l_proc, 5);
505 --
506 -- Call all supporting business operations
507 --
508 --
509 hr_utility.set_location(l_proc, 10);
510 --
511 chk_consideration_status
512 (p_consideration_status => p_rec.consideration_status
513 ,p_effective_date => p_effective_date
514 ,p_vacancy_consideration_id => p_rec.vacancy_consideration_id
515 ,p_object_version_number => p_rec.object_version_number
516 );
517 --
518 --
519 hr_utility.set_location(l_proc, 30);
520 chk_non_updateable_args
521 (p_effective_date => p_effective_date
522 ,p_rec => p_rec
523 );
524 --
525 hr_utility.set_location(' Leaving:'||l_proc, 40);
526 End update_validate;
527 --
528 -- ----------------------------------------------------------------------------
529 -- |---------------------------< delete_validate >----------------------------|
530 -- ----------------------------------------------------------------------------
531 Procedure delete_validate
532 (p_rec in irc_ivc_shd.g_rec_type
533 ) is
534 --
535 l_proc varchar2(72) := g_package||'delete_validate';
536 --
537 Begin
538 hr_utility.set_location('Entering:'||l_proc, 5);
539 --
540 -- Call all supporting business operations
541 --
542 hr_utility.set_location(' Leaving:'||l_proc, 10);
543 End delete_validate;
544 --
545 end irc_ivc_bus;