[Home] [Help]
PACKAGE BODY: APPS.IRC_IAV_BUS
Source
1 Package Body irc_iav_bus as
2 /* $Header: iriavrhi.pkb 120.1 2005/12/22 21:07:08 gganesan noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iav_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_vacancy_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_vacancy_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , per_all_vacancies pav
32 where pav.vacancy_id = p_vacancy_id
33 and pbg.business_group_id = pav.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'vacancy_id'
50 ,p_argument_value => p_vacancy_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'VACANCY_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_vacancy_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , per_all_vacancies pav
102 where pav.vacancy_id = p_vacancy_id
103 and pbg.business_group_id = pav.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'vacancy_id'
119 ,p_argument_value => p_vacancy_id
120 );
121 --
122 if ( nvl(irc_iav_bus.g_vacancy_id, hr_api.g_number)
123 = p_vacancy_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := irc_iav_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 irc_iav_bus.g_vacancy_id := p_vacancy_id;
154 irc_iav_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in irc_iav_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.agency_vacancy_id is not null) and (
198 nvl(irc_iav_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(irc_iav_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(irc_iav_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(irc_iav_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(irc_iav_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(irc_iav_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(irc_iav_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(irc_iav_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(irc_iav_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(irc_iav_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(irc_iav_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(irc_iav_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(irc_iav_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(irc_iav_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(irc_iav_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(irc_iav_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(irc_iav_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(irc_iav_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(irc_iav_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(irc_iav_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(irc_iav_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) or
240 nvl(irc_iav_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
241 nvl(p_rec.attribute21, hr_api.g_varchar2) or
242 nvl(irc_iav_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
243 nvl(p_rec.attribute22, hr_api.g_varchar2) or
244 nvl(irc_iav_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
245 nvl(p_rec.attribute23, hr_api.g_varchar2) or
246 nvl(irc_iav_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
247 nvl(p_rec.attribute24, hr_api.g_varchar2) or
248 nvl(irc_iav_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
249 nvl(p_rec.attribute25, hr_api.g_varchar2) or
250 nvl(irc_iav_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
251 nvl(p_rec.attribute26, hr_api.g_varchar2) or
252 nvl(irc_iav_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
253 nvl(p_rec.attribute27, hr_api.g_varchar2) or
254 nvl(irc_iav_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
255 nvl(p_rec.attribute28, hr_api.g_varchar2) or
256 nvl(irc_iav_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
257 nvl(p_rec.attribute29, hr_api.g_varchar2) or
258 nvl(irc_iav_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
259 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
260 or (p_rec.agency_vacancy_id is null) then
261 --
262 -- Only execute the validation if absolutely necessary:
263 -- a) During update, the structure column value or any
264 -- of the attribute values have actually changed.
265 -- b) During insert.
266 --
267 /* hr_dflex_utility.ins_or_upd_descflex_attribs
268 (p_appl_short_name => 'IRC'
269 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
270 ,p_attribute_category => 'ATTRIBUTE_CATEGORY'
271 ,p_attribute1_name => 'ATTRIBUTE1'
272 ,p_attribute1_value => p_rec.attribute1
273 ,p_attribute2_name => 'ATTRIBUTE2'
274 ,p_attribute2_value => p_rec.attribute2
275 ,p_attribute3_name => 'ATTRIBUTE3'
276 ,p_attribute3_value => p_rec.attribute3
277 ,p_attribute4_name => 'ATTRIBUTE4'
278 ,p_attribute4_value => p_rec.attribute4
279 ,p_attribute5_name => 'ATTRIBUTE5'
280 ,p_attribute5_value => p_rec.attribute5
281 ,p_attribute6_name => 'ATTRIBUTE6'
282 ,p_attribute6_value => p_rec.attribute6
283 ,p_attribute7_name => 'ATTRIBUTE7'
284 ,p_attribute7_value => p_rec.attribute7
285 ,p_attribute8_name => 'ATTRIBUTE8'
286 ,p_attribute8_value => p_rec.attribute8
287 ,p_attribute9_name => 'ATTRIBUTE9'
288 ,p_attribute9_value => p_rec.attribute9
289 ,p_attribute10_name => 'ATTRIBUTE10'
290 ,p_attribute10_value => p_rec.attribute10
291 ,p_attribute11_name => 'ATTRIBUTE11'
292 ,p_attribute11_value => p_rec.attribute11
293 ,p_attribute12_name => 'ATTRIBUTE12'
294 ,p_attribute12_value => p_rec.attribute12
295 ,p_attribute13_name => 'ATTRIBUTE13'
296 ,p_attribute13_value => p_rec.attribute13
297 ,p_attribute14_name => 'ATTRIBUTE14'
298 ,p_attribute14_value => p_rec.attribute14
299 ,p_attribute15_name => 'ATTRIBUTE15'
300 ,p_attribute15_value => p_rec.attribute15
301 ,p_attribute16_name => 'ATTRIBUTE16'
302 ,p_attribute16_value => p_rec.attribute16
303 ,p_attribute17_name => 'ATTRIBUTE17'
304 ,p_attribute17_value => p_rec.attribute17
305 ,p_attribute18_name => 'ATTRIBUTE18'
306 ,p_attribute18_value => p_rec.attribute18
307 ,p_attribute19_name => 'ATTRIBUTE19'
308 ,p_attribute19_value => p_rec.attribute19
309 ,p_attribute20_name => 'ATTRIBUTE20'
310 ,p_attribute20_value => p_rec.attribute20
311 ,p_attribute21_name => 'ATTRIBUTE21'
312 ,p_attribute21_value => p_rec.attribute21
313 ,p_attribute22_name => 'ATTRIBUTE22'
314 ,p_attribute22_value => p_rec.attribute22
315 ,p_attribute23_name => 'ATTRIBUTE23'
316 ,p_attribute23_value => p_rec.attribute23
317 ,p_attribute24_name => 'ATTRIBUTE24'
318 ,p_attribute24_value => p_rec.attribute24
319 ,p_attribute25_name => 'ATTRIBUTE25'
320 ,p_attribute25_value => p_rec.attribute25
321 ,p_attribute26_name => 'ATTRIBUTE26'
322 ,p_attribute26_value => p_rec.attribute26
323 ,p_attribute27_name => 'ATTRIBUTE27'
324 ,p_attribute27_value => p_rec.attribute27
325 ,p_attribute28_name => 'ATTRIBUTE28'
326 ,p_attribute28_value => p_rec.attribute28
327 ,p_attribute29_name => 'ATTRIBUTE29'
328 ,p_attribute29_value => p_rec.attribute29
329 ,p_attribute30_name => 'ATTRIBUTE30'
330 ,p_attribute30_value => p_rec.attribute30
331 ); */
332 null;
333 end if;
334 --
335 hr_utility.set_location(' Leaving:'||l_proc,20);
336 end chk_df;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |-----------------------< chk_non_updateable_args >------------------------|
340 -- ----------------------------------------------------------------------------
341 -- {Start Of Comments}
342 --
343 -- Description:
344 -- This procedure is used to ensure that non updateable attributes have
345 -- not been updated. If an attribute has been updated an error is generated.
346 --
347 -- Pre Conditions:
348 -- g_old_rec has been populated with details of the values currently in
349 -- the database.
350 --
351 -- In Arguments:
352 -- p_rec has been populated with the updated values the user would like the
353 -- record set to.
354 --
355 -- Post Success:
356 -- Processing continues if all the non updateable attributes have not
357 -- changed.
358 --
359 -- Post Failure:
360 -- An application error is raised if any of the non updatable attributes
361 -- have been altered.
362 --
363 -- {End Of Comments}
364 -- ----------------------------------------------------------------------------
365 Procedure chk_non_updateable_args
366 (p_rec in irc_iav_shd.g_rec_type
367 ) IS
368 --
369 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
370 --
371 Begin
372 --
373 -- Only proceed with the validation if a row exists for the current
374 -- record in the HR Schema.
375 --
376 IF NOT irc_iav_shd.api_updating
377 (p_agency_vacancy_id => p_rec.agency_vacancy_id
378 ,p_object_version_number => p_rec.object_version_number
379 ) THEN
380 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
381 fnd_message.set_token('PROCEDURE ', l_proc);
382 fnd_message.set_token('STEP ', '5');
383 fnd_message.raise_error;
384 END IF;
385 --
386 -- EDIT_HERE: Add checks to ensure non-updateable args have
387 -- not been updated.
388 --
389 End chk_non_updateable_args;
390 --
391 --
392 -- ----------------------------------------------------------------------------
393 -- |---------------------------< chk_agency_id >-------------------------------|
394 -- ----------------------------------------------------------------------------
395 --
396 -- Description:
397 -- Validates if the agency id exists in the PO_VENDORS table
398 --
399 -- Prerequisites:
400 -- Must be called as the first step in insert_validate.
401 --
402 -- In Arguments:
403 -- p_agency_id
404 --
405 -- Post Success:
406 -- If the agency_id is existing in PO_VENDORS (vendor_id)
407 -- then continue.
408 --
409 -- Post Failure:
410 -- If the agency_id is not present in PO_VENDORS (vendor_id)
411 -- then throw an error indicating the same.
412 --
413 -- Access Status:
414 -- Internal Row Handler Use Only.
415 --
416 -- ----------------------------------------------------------------------------
417 Procedure chk_agency_id
418 (p_agency_id
419 in irc_agency_vacancies.agency_id%type
420 ) is
421 --
422 l_proc varchar2(72) := g_package||'chk_agency_id';
423 l_agency_id irc_agency_vacancies.agency_id%type;
424 cursor csr_agency_id is
425 select 1
426 from po_vendors
427 where vendor_id = p_agency_id
428 and vendor_type_lookup_code = 'IRC_JOB_AGENCY';
432 hr_utility.set_location(' Entering:'||l_proc,10);
429 --
430 Begin
431 --
433 --
434 open csr_agency_id;
435 fetch csr_agency_id into l_agency_id;
436 --
437 hr_utility.set_location(l_proc,20);
438 --
439 if csr_agency_id%notfound then
440 close csr_agency_id;
441 fnd_message.set_name('PER','IRC_BAD_AGENCY_ID');
442 fnd_message.raise_error;
443 end if;
444 --
445 hr_utility.set_location(l_proc,30);
446 --
447 close csr_agency_id;
448 --
449 hr_utility.set_location(' Leaving:'||l_proc,50);
450 --
451 exception
452 when app_exception.application_exception then
453 if hr_multi_message.exception_add
454 (p_associated_column1 => 'IRC_AGENCY_VACANCIES.AGENCY_ID'
455 ) then
456 --
457 hr_utility.set_location(' Leaving:'||l_proc,60);
458 --
459 raise;
460 end if;
461 --
462 hr_utility.set_location(' Leaving:'||l_proc,70);
463 --
464 End chk_agency_id;
465
466 --
467 -- ----------------------------------------------------------------------------
468 -- |-----------------------< chk_vacancy_id >---------------------------------|
469 -- ----------------------------------------------------------------------------
470 -- {Start Of Comments}
471 --
472 -- Description:
473 -- This procedure checks if the vacancy_id exists in per_all_vacancies
474 --
475 --
476 -- Pre Conditions:
477 -- g_old_rec has been populated with details of the values currently in
478 -- the database.
479 --
480 -- In Arguments:
481 -- p_vacancy_id
482 -- p_start_date.
483 --
484 -- Post Success:
485 -- Processing continues if the mandatory parameters have been set and the
486 -- specified vacancy id exists.
487 --
488 -- Post Failure:
489 -- An application error is raised if the vacancy id does not exist.
490 --
491 -- {End Of Comments}
492 -- ----------------------------------------------------------------------------
493 Procedure chk_vacancy_id
494 (p_vacancy_id in irc_agency_vacancies.vacancy_id%type
495 ,p_start_date in irc_agency_vacancies.start_date%type
496 ) IS
497 --
498 l_proc varchar2(72) := g_package || 'chk_vacancy_id';
499 l_vacancy_id irc_agency_vacancies.vacancy_id%type;
500 --
501 --
502 -- Cursor to check that the vacancy_id exists in PER_ALL_VACANCIES
503 -- and is current at the p_start_date.
504 --
505 cursor csr_vacancy_id is
506 select vacancy_id
507 from per_all_vacancies
508 where vacancy_id = p_vacancy_id
509 and p_start_date between date_from and nvl(date_to,hr_api.g_eot);
510 --
511 Begin
512 --
513 hr_utility.set_location(' Entering:'||l_proc,10);
514 --
515 hr_api.mandatory_arg_error
516 (p_api_name => l_proc
517 ,p_argument => 'vacancy_id'
518 ,p_argument_value => p_vacancy_id
519 );
520 --
521 -- Check if the vacancy_id exists in PER_ALL_VACANCIES
522 -- and is current at the p_start_date.
523 --
524 open csr_vacancy_id;
525 fetch csr_vacancy_id into l_vacancy_id;
526 hr_utility.set_location(l_proc, 30);
527 if csr_vacancy_id%notfound then
528 close csr_vacancy_id;
529 fnd_message.set_name('PER','IRC_IAV_INV_VACANCY_ID');
530 fnd_message.raise_error;
531 end if;
532 close csr_vacancy_id;
533 hr_utility.set_location(' Leaving:'||l_proc,35);
534 --
535 exception
536 when app_exception.application_exception then
537 if hr_multi_message.exception_add
538 (p_associated_column1 => 'IRC_AGENCY_VACANCIES.VACANCY_ID'
539 ) then
540 hr_utility.set_location(' Leaving:'||l_proc, 40);
541 raise;
542 end if;
543 hr_utility.set_location(' Leaving:'||l_proc,50);
544 --
545 End chk_vacancy_id;
546
547
548 -- ----------------------------------------------------------------------------
549 -- |-----------------------< chk_end_date >---------------------------------|
550 -- ----------------------------------------------------------------------------
551 -- {Start Of Comments}
552 --
553 -- Description:
554 -- This procedure is used to ensure that valid dates have been entered.
555 --
556 -- Pre Conditions:
557 -- g_old_rec has been populated with details of the values currently in
558 -- the database.
559 --
560 -- In Arguments:
561 -- p_start_date
562 -- p_end_date
563 -- p_object_version_number.
564 --
565 -- Post Success:
566 -- Processing continues if valid dates are entered.
567 --
568 -- Post Failure:
569 -- An application error is raised if valid dates are not entered.
570 --
571 -- {End Of Comments}
572 -- ----------------------------------------------------------------------------
573 Procedure chk_end_date
574 (p_start_date in irc_agency_vacancies.start_date%type
575 ,p_end_date in irc_agency_vacancies.end_date%type
576 ,p_agency_vacancy_id in irc_agency_vacancies.agency_vacancy_id%type
577 ,p_object_version_number in irc_agency_vacancies.object_version_number%type
578 ) IS
579 --
580 l_proc varchar2(72) := g_package || 'chk_end_date';
581 l_api_updating boolean;
582 --
583 Begin
584 --
585 hr_utility.set_location(' Entering:'||l_proc,10);
586 if (p_start_date is not NULL or p_end_date is not NULL) then
587 --
588 l_api_updating := irc_iav_shd.api_updating(p_agency_vacancy_id
589 ,p_object_version_number);
590 --
591 -- Check to see if start_date or end_date values have changed.
592 --
593 hr_utility.set_location(l_proc, 30);
597 or (nvl(irc_iav_shd.g_old_rec.end_date,hr_api.g_eot) <> p_end_date)))
594 if ((l_api_updating
595 and ((nvl(irc_iav_shd.g_old_rec.start_date,hr_api.g_sot)
596 <> p_start_date)
598 or (NOT l_api_updating)) then
599 --
600 -- Check that the end date is not before the start date.
601 --
602 hr_utility.set_location(l_proc, 40);
603 if(nvl(p_start_date,hr_api.g_sot) > nvl(p_end_date,hr_api.g_eot)) then
604 fnd_message.set_name('PER','IRC_IAV_INV_ST_END_DATE');
605 fnd_message.raise_error;
606 end if;
607 --
608 end if;
609 end if;
610 hr_utility.set_location(' Leaving:'||l_proc,45);
611 exception
612 when app_exception.application_exception then
613 if hr_multi_message.exception_add
614 (p_associated_column1 => 'IRC_AGENCY_VACANCIES.START_DATE'
615 ,p_associated_column2 => 'IRC_AGENCY_VACANCIES.END_DATE'
616 ) then
617 hr_utility.set_location(' Leaving:'||l_proc, 50);
618 raise;
619 end if;
620 hr_utility.set_location(' Leaving:'||l_proc,60);
621 --
622 End chk_end_date;
623
624
625 --
626 -- ----------------------------------------------------------------------------
627 -- |-----------------------< chk_agency_vac_comb_id >--------------|
628 -- ----------------------------------------------------------------------------
629 -- {Start Of Comments}
630 --
631 -- Description:
632 -- This procedure checks if the agency_id and vacancy_id combination is
633 -- unique .
634 --
635 -- Pre Conditions:
636 -- g_old_rec has been populated with details of the values currently in
637 -- the database.
638 --
639 -- In Arguments:
640 -- p_vacancy_id
641 -- p_start_date.
642 --
643 -- Post Success:
644 -- Processing continues if the mandatory parameters have been set and the
645 -- specified vacancy id and vacancy id is unique.
646 --
647 -- Post Failure:
648 -- An application error is raised if the vacancy id does not exist.
649 --
650 -- {End Of Comments}
651 -- ----------------------------------------------------------------------------
652 Procedure chk_agency_vac_comb_id
653 (p_agency_id in irc_agency_vacancies.agency_id%type
654 ,p_vacancy_id in irc_agency_vacancies.vacancy_id%type
655 ,p_agency_vacancy_id in irc_agency_vacancies.agency_vacancy_id%type
656 ,p_object_version_number in irc_agency_vacancies.object_version_number%type
657 ) IS
658 --
659 l_proc varchar2(72) := g_package || 'chk_agency_vacancy_combination_id';
660 l_agency_vacancy_id irc_agency_vacancies.agency_vacancy_id%type;
661 --
662 --
663 --
664 --
665 --
666 cursor csr_agency_vacancy_id is
667 select agency_vacancy_id
668 from irc_agency_vacancies
669 where agency_id = p_agency_id
670 and vacancy_id = p_vacancy_id;
671 --
672 cursor csr_agency_vacancy_upd is
673 select agency_vacancy_id
674 from irc_agency_vacancies
675 where agency_id = p_agency_id
676 and vacancy_id = p_vacancy_id
677 and agency_vacancy_id <> p_agency_vacancy_id;
678 --
679 l_api_updating boolean;
680
681 Begin
682 --
683 hr_utility.set_location(' Entering:'||l_proc,10);
684 --
685 hr_api.mandatory_arg_error
686 (p_api_name => l_proc
687 ,p_argument => 'agency_id'
688 ,p_argument_value => p_agency_id
689 );
690 --
691 hr_api.mandatory_arg_error
692 (p_api_name => l_proc
693 ,p_argument => 'vacancy_id'
694 ,p_argument_value => p_vacancy_id
695 );
696 --
697 --
698 --
699 --
700 --
701 l_api_updating := irc_iav_shd.api_updating(p_agency_vacancy_id
702 ,p_object_version_number);
703 --
704 if l_api_updating then
705 open csr_agency_vacancy_upd ;
706 fetch csr_agency_vacancy_upd into l_agency_vacancy_id;
707 hr_utility.set_location(l_proc, 30);
708 if csr_agency_vacancy_upd%found then
709 close csr_agency_vacancy_upd ;
710 fnd_message.set_name('PER','IRC_IAV_INV_AGENCY_VAC_COMB');
711 fnd_message.raise_error;
712 end if;
713 close csr_agency_vacancy_upd ;
714 else
715 open csr_agency_vacancy_id;
716 fetch csr_agency_vacancy_id into l_agency_vacancy_id;
717 hr_utility.set_location(l_proc, 40);
718 if csr_agency_vacancy_id%found then
719 close csr_agency_vacancy_id;
720 fnd_message.set_name('PER','IRC_IAV_INV_AGENCY_VAC_COMB');
721 fnd_message.raise_error;
722 end if;
723 close csr_agency_vacancy_id;
724 --
725 end if;
726 --
727 hr_utility.set_location(' Leaving:'||l_proc,50);
728 --
729 exception
730 when app_exception.application_exception then
731 if hr_multi_message.exception_add
732 (p_associated_column1 => 'IRC_AGENCY_VACANCIES.AGENCY_ID'
733 ,p_associated_column2 => 'IRC_AGENCY_VACANCIES.VACANCY_ID'
734 ) then
735 hr_utility.set_location(' Leaving:'||l_proc, 60);
736 raise;
737 end if;
738 hr_utility.set_location(' Leaving:'||l_proc,70);
739 --
740 End chk_agency_vac_comb_id;
741
742
743 --
744 -- ----------------------------------------------------------------------------
745 -- |-----------------------< chk_manage_appl_allowed >---------------|
746 -- ----------------------------------------------------------------------------
747 -- {Start Of Comments}
748 --
749 -- Description:
750 -- This procedure checks if 'manage_applicants_allowed' value is valid .
751 --
755 --
752 -- Pre Conditions:
753 -- g_old_rec has been populated with details of the values currently in
754 -- the database.
756 -- In Arguments:
757 -- p_update_allowed
758 --
759 -- Post Success:
760 -- Processing continues if a valid 'manage_applicants_allowed' value is
761 -- entered.
762 --
763 -- Post Failure:
764 -- An application error is raised if a valid 'manage_applicants_allowed' value
765 -- is not entered.
766 --
767 -- {End Of Comments}
768 -- ----------------------------------------------------------------------------
769 Procedure chk_manage_appl_allowed
770 (p_manage_applicants_allowed in
771 irc_agency_vacancies.manage_applicants_allowed%type
772 ,p_start_date in irc_agency_vacancies.start_date%type
773 ,p_agency_vacancy_id in irc_agency_vacancies.agency_vacancy_id%type
774 ,p_object_version_number in irc_agency_vacancies.object_version_number%type
775 ) IS
776 --
777 l_proc varchar2(72) := g_package || 'chk_manage_appl_allowed';
778 l_var boolean;
779 l_api_updating boolean;
780 --
781 Begin
782 --
783 hr_utility.set_location(' Entering:'||l_proc,10);
784 if (p_manage_applicants_allowed is not NULL) then
785 --
786 l_api_updating := irc_iav_shd.api_updating(p_agency_vacancy_id
787 ,p_object_version_number);
788 --
789 -- Check to see if the update_allowed value has changed.
790 --
791 hr_utility.set_location(l_proc, 30);
792 if ((l_api_updating
793 and (irc_iav_shd.g_old_rec.manage_applicants_allowed <> p_manage_applicants_allowed))
794 or (NOT l_api_updating)) then
795 --
796 -- Check that a valid 'Update Allowed' value is entered.
797 --
798 l_var := hr_api.not_exists_in_hr_lookups
799 (p_start_date
800 ,'YES_NO'
801 ,p_manage_applicants_allowed
802 );
803 hr_utility.set_location(l_proc, 40);
804 if (l_var = true) then
805 fnd_message.set_name('PER','IRC_IAV_INV_MNG_APL_ALLOWED');
806 fnd_message.raise_error;
807 end if;
808 end if;
809 --
810 end if;
811 hr_utility.set_location(' Leaving:'||l_proc,45);
812 exception
813 when app_exception.application_exception then
814 if hr_multi_message.exception_add
815 (p_associated_column1 => 'IRC_AGENCY_VACANCIES.MANAGE_APPLICANTS_ALLOWED'
816 ) then
817 hr_utility.set_location(' Leaving:'||l_proc, 50);
818 raise;
819 end if;
820 hr_utility.set_location(' Leaving:'||l_proc,60);
821 --
822 End chk_manage_appl_allowed;
823 --
824 -- ----------------------------------------------------------------------------
825 -- |---------------------------< insert_validate >----------------------------|
826 -- ----------------------------------------------------------------------------
827 Procedure insert_validate
828 (p_rec in irc_iav_shd.g_rec_type
829 ) is
830 --
831 l_proc varchar2(72) := g_package||'insert_validate';
832 --
833 Begin
834 hr_utility.set_location('Entering:'||l_proc, 5);
835 --
836 -- Call all supporting business operations
837 --
838 hr_utility.set_location(l_proc, 10);
839 irc_iav_bus.set_security_group_id
840 (p_vacancy_id => p_rec.vacancy_id
841 ,p_associated_column1 => irc_iav_shd.g_tab_nam||'.VACANCY_ID'
842 );
843 --
844 -- Validate Dependent Attributes
845 --
846 --
847 irc_iav_bus.chk_df(p_rec);
848 --
849 chk_agency_id
850 (p_agency_id => p_rec.agency_id
851 );
852 --
853 chk_vacancy_id
854 (p_vacancy_id => p_rec.vacancy_id
855 ,p_start_date => p_rec.start_date
856 );
857 --
858 chk_agency_vac_comb_id
859 (p_agency_id => p_rec.agency_id
860 ,p_vacancy_id => p_rec.vacancy_id
861 ,p_agency_vacancy_id => p_rec.agency_vacancy_id
862 ,p_object_version_number => p_rec.object_version_number
863 );
864 --
865 chk_end_date
866 (p_start_date => p_rec.start_date
867 ,p_end_date => p_rec.end_date
868 ,p_agency_vacancy_id => p_rec.agency_vacancy_id
869 ,p_object_version_number => p_rec.object_version_number
870 );
871 --
872 chk_manage_appl_allowed
873 (p_manage_applicants_allowed => p_rec.manage_applicants_allowed
874 ,p_start_date => p_rec.start_date
875 ,p_agency_vacancy_id => p_rec.agency_vacancy_id
876 ,p_object_version_number => p_rec.object_version_number
877 );
878 --
879 hr_utility.set_location(' Leaving:'||l_proc, 20);
880 End insert_validate;
881 --
882 -- ----------------------------------------------------------------------------
883 -- |---------------------------< update_validate >----------------------------|
884 -- ----------------------------------------------------------------------------
885 Procedure update_validate
886 (p_rec in irc_iav_shd.g_rec_type
887 ) is
888 --
889 l_proc varchar2(72) := g_package||'update_validate';
890 --
891 Begin
892 hr_utility.set_location('Entering:'||l_proc, 5);
893 --
894 -- Call all supporting business operations
895 --
896 hr_utility.set_location(l_proc, 10);
897 irc_iav_bus.set_security_group_id
898 (p_vacancy_id => p_rec.vacancy_id
899 ,p_associated_column1 => irc_iav_shd.g_tab_nam||'.VACANCY_ID'
900 );
901 --
902 -- Validate Dependent Attributes
903 --
904 chk_non_updateable_args
905 (p_rec => p_rec
906 );
907 --
908 --
912 (p_agency_id => p_rec.agency_id
909 irc_iav_bus.chk_df(p_rec);
910 --
911 chk_agency_id
913 );
914 --
915 chk_vacancy_id
916 (p_vacancy_id => p_rec.vacancy_id
917 ,p_start_date => p_rec.start_date
918 );
919 --
920 chk_agency_vac_comb_id
921 (p_agency_id => p_rec.agency_id
922 ,p_vacancy_id => p_rec.vacancy_id
923 ,p_agency_vacancy_id => p_rec.agency_vacancy_id
924 ,p_object_version_number => p_rec.object_version_number
925 );
926 --
927 chk_end_date
928 (p_start_date => p_rec.start_date
929 ,p_end_date => p_rec.end_date
930 ,p_agency_vacancy_id => p_rec.agency_vacancy_id
931 ,p_object_version_number => p_rec.object_version_number
932 );
933 --
934 chk_manage_appl_allowed
935 (p_manage_applicants_allowed => p_rec.manage_applicants_allowed
936 ,p_start_date => p_rec.start_date
937 ,p_agency_vacancy_id => p_rec.agency_vacancy_id
938 ,p_object_version_number => p_rec.object_version_number
939 );
940 --
941 hr_utility.set_location(' Leaving:'||l_proc, 20);
942 End update_validate;
943 --
944 -- ----------------------------------------------------------------------------
945 -- |---------------------------< delete_validate >----------------------------|
946 -- ----------------------------------------------------------------------------
947 Procedure delete_validate
948 (p_rec in irc_iav_shd.g_rec_type
949 ) is
950 --
951 l_proc varchar2(72) := g_package||'delete_validate';
952 --
953 Begin
954 hr_utility.set_location('Entering:'||l_proc, 5);
955 --
956 -- Call all supporting business operations
957 --
958 hr_utility.set_location(' Leaving:'||l_proc, 10);
959 End delete_validate;
960 --
961 end irc_iav_bus;