[Home] [Help]
PACKAGE BODY: APPS.IRC_IOF_BUS
Source
1 Package Body irc_iof_bus as
2 /* $Header: iriofrhi.pkb 120.20 2011/04/08 12:04:09 amikukum ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iof_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_offer_id number default null;
15 --
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_offer_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id
30 from per_business_groups pbg
31 , irc_offers iof
32 , per_all_vacancies vac
33 where iof.offer_id = p_offer_id
34 and vac.vacancy_id = iof.vacancy_id
35 and pbg.business_group_id = vac.business_group_id;
36 --
37 -- Declare local variables
38 --
39 l_security_group_id number;
40 l_proc varchar2(72) := g_package||'set_security_group_id';
41 --
42 begin
43 --
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Ensure that all the mandatory parameter are not null
47 --
48 hr_api.mandatory_arg_error
49 (p_api_name => l_proc
50 ,p_argument => 'offer_id'
51 ,p_argument_value => p_offer_id
52 );
53 --
54 open csr_sec_grp;
55 fetch csr_sec_grp into l_security_group_id;
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 => nvl(p_associated_column1,'OFFER_ID'));
66 --
67 else
68 close csr_sec_grp;
69 --
70 -- Set the security_group_id in CLIENT_INFO
71 --
72 hr_api.set_security_group_id
73 (p_security_group_id => l_security_group_id
74 );
75 --
76 end if;
77 hr_utility.set_location(' Leaving:'|| l_proc, 20);
78 --
79 end set_security_group_id;
80 --
81 -- ---------------------------------------------------------------------------
82 -- |---------------------< return_legislation_code >-------------------------|
83 -- ---------------------------------------------------------------------------
84 --
85 Function return_legislation_code
86 (p_offer_id in number
87 )
88 Return Varchar2 Is
89 --
90 -- Declare cursor
91 --
92 cursor csr_leg_code is
93 select pbg.legislation_code
94 from per_business_groups_perf pbg
95 , irc_offers iof
96 , per_all_vacancies vac
97 where iof.offer_id = p_offer_id
98 and iof.vacancy_id = vac.vacancy_id
99 and pbg.business_group_id = vac.business_group_id;
100 --
101 -- Declare local variables
102 --
103 l_legislation_code varchar2(150);
104 l_proc varchar2(72) := g_package||'return_legislation_code';
105 --
106 Begin
107 --
108 hr_utility.set_location('Entering:'|| l_proc, 10);
109 --
110 -- Ensure that all the mandatory parameter are not null
111 --
112 hr_api.mandatory_arg_error
113 (p_api_name => l_proc
114 ,p_argument => 'offer_id'
115 ,p_argument_value => p_offer_id
116 );
117 --
118 if ( nvl(irc_iof_bus.g_offer_id, hr_api.g_number)
119 = p_offer_id) then
120 --
121 -- The legislation code has already been found with a previous
122 -- call to this function. Just return the value in the global
123 -- variable.
124 --
125 l_legislation_code := irc_iof_bus.g_legislation_code;
126 hr_utility.set_location(l_proc, 20);
127 else
128 --
129 -- The ID is different to the last call to this function
130 -- or this is the first call to this function.
131 --
132 open csr_leg_code;
133 fetch csr_leg_code into l_legislation_code;
134 --
135 if csr_leg_code%notfound then
136 --
137 -- The primary key is invalid therefore we must error
138 --
139 close csr_leg_code;
140 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
141 fnd_message.raise_error;
142 end if;
143 hr_utility.set_location(l_proc,30);
144 --
145 -- Set the global variables so the values are
146 -- available for the next call to this function.
147 --
148 close csr_leg_code;
149 irc_iof_bus.g_offer_id := p_offer_id;
150 irc_iof_bus.g_legislation_code := l_legislation_code;
151 end if;
152 hr_utility.set_location(' Leaving:'|| l_proc, 40);
153 return l_legislation_code;
154 end return_legislation_code;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------------------------------< chk_df >----------------------------------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description:
161 -- Validates all the Descriptive Flexfield values.
162 --
163 -- Prerequisites:
164 -- All other columns have been validated. Must be called as the
165 -- last step from insert_validate and update_validate.
166 --
167 -- In Arguments:
168 -- p_rec
169 --
170 -- Post Success:
171 -- If the Descriptive Flexfield structure column and data values are
172 -- all valid this procedure will end normally and processing will
173 -- continue.
174 --
175 -- Post Failure:
176 -- If the Descriptive Flexfield structure column value or any of
177 -- the data values are invalid then an application error is raised as
178 -- a PL/SQL exception.
179 --
180 -- Access Status:
181 -- Internal Row Handler Use Only.
182 --
183 -- ----------------------------------------------------------------------------
184 procedure chk_df
185 (p_rec in irc_iof_shd.g_rec_type
186 ) is
187 --
188 l_proc varchar2(72) := g_package || 'chk_df';
189 --
190 begin
191 hr_utility.set_location('Entering:'||l_proc,10);
192 --
193 if ((p_rec.offer_id is not null) and (
194 nvl(irc_iof_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
195 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
196 nvl(irc_iof_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
197 nvl(p_rec.attribute1, hr_api.g_varchar2) or
198 nvl(irc_iof_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute2, hr_api.g_varchar2) or
200 nvl(irc_iof_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute3, hr_api.g_varchar2) or
202 nvl(irc_iof_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute4, hr_api.g_varchar2) or
204 nvl(irc_iof_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute5, hr_api.g_varchar2) or
206 nvl(irc_iof_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute6, hr_api.g_varchar2) or
208 nvl(irc_iof_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute7, hr_api.g_varchar2) or
210 nvl(irc_iof_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute8, hr_api.g_varchar2) or
212 nvl(irc_iof_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute9, hr_api.g_varchar2) or
214 nvl(irc_iof_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute10, hr_api.g_varchar2) or
216 nvl(irc_iof_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute11, hr_api.g_varchar2) or
218 nvl(irc_iof_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute12, hr_api.g_varchar2) or
220 nvl(irc_iof_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute13, hr_api.g_varchar2) or
222 nvl(irc_iof_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute14, hr_api.g_varchar2) or
224 nvl(irc_iof_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute15, hr_api.g_varchar2) or
226 nvl(irc_iof_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute16, hr_api.g_varchar2) or
228 nvl(irc_iof_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute17, hr_api.g_varchar2) or
230 nvl(irc_iof_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute18, hr_api.g_varchar2) or
232 nvl(irc_iof_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute19, hr_api.g_varchar2) or
234 nvl(irc_iof_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute20, hr_api.g_varchar2) or
236 nvl(irc_iof_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute21, hr_api.g_varchar2) or
238 nvl(irc_iof_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute22, hr_api.g_varchar2) or
240 nvl(irc_iof_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
241 nvl(p_rec.attribute23, hr_api.g_varchar2) or
242 nvl(irc_iof_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
243 nvl(p_rec.attribute24, hr_api.g_varchar2) or
244 nvl(irc_iof_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
245 nvl(p_rec.attribute25, hr_api.g_varchar2) or
246 nvl(irc_iof_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
247 nvl(p_rec.attribute26, hr_api.g_varchar2) or
248 nvl(irc_iof_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
249 nvl(p_rec.attribute27, hr_api.g_varchar2) or
250 nvl(irc_iof_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
251 nvl(p_rec.attribute28, hr_api.g_varchar2) or
252 nvl(irc_iof_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
253 nvl(p_rec.attribute29, hr_api.g_varchar2) or
254 nvl(irc_iof_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
255 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
256 or (p_rec.offer_id is null) then
257 --
258 -- Only execute the validation if absolutely necessary:
259 -- a) During update, the structure column value or any
260 -- of the attribute values have actually changed.
261 -- b) During insert.
262 --
263 hr_dflex_utility.ins_or_upd_descflex_attribs
264 (p_appl_short_name => 'PER'
265 ,p_descflex_name => 'IRC_OFFERS'
266 ,p_attribute_category => p_rec.attribute_category
267 ,p_attribute1_name => 'ATTRIBUTE1'
268 ,p_attribute1_value => p_rec.attribute1
269 ,p_attribute2_name => 'ATTRIBUTE2'
270 ,p_attribute2_value => p_rec.attribute2
271 ,p_attribute3_name => 'ATTRIBUTE3'
272 ,p_attribute3_value => p_rec.attribute3
273 ,p_attribute4_name => 'ATTRIBUTE4'
274 ,p_attribute4_value => p_rec.attribute4
275 ,p_attribute5_name => 'ATTRIBUTE5'
276 ,p_attribute5_value => p_rec.attribute5
277 ,p_attribute6_name => 'ATTRIBUTE6'
278 ,p_attribute6_value => p_rec.attribute6
279 ,p_attribute7_name => 'ATTRIBUTE7'
280 ,p_attribute7_value => p_rec.attribute7
281 ,p_attribute8_name => 'ATTRIBUTE8'
282 ,p_attribute8_value => p_rec.attribute8
283 ,p_attribute9_name => 'ATTRIBUTE9'
284 ,p_attribute9_value => p_rec.attribute9
285 ,p_attribute10_name => 'ATTRIBUTE10'
286 ,p_attribute10_value => p_rec.attribute10
287 ,p_attribute11_name => 'ATTRIBUTE11'
288 ,p_attribute11_value => p_rec.attribute11
289 ,p_attribute12_name => 'ATTRIBUTE12'
290 ,p_attribute12_value => p_rec.attribute12
291 ,p_attribute13_name => 'ATTRIBUTE13'
292 ,p_attribute13_value => p_rec.attribute13
293 ,p_attribute14_name => 'ATTRIBUTE14'
294 ,p_attribute14_value => p_rec.attribute14
295 ,p_attribute15_name => 'ATTRIBUTE15'
296 ,p_attribute15_value => p_rec.attribute15
297 ,p_attribute16_name => 'ATTRIBUTE16'
298 ,p_attribute16_value => p_rec.attribute16
299 ,p_attribute17_name => 'ATTRIBUTE17'
300 ,p_attribute17_value => p_rec.attribute17
301 ,p_attribute18_name => 'ATTRIBUTE18'
302 ,p_attribute18_value => p_rec.attribute18
303 ,p_attribute19_name => 'ATTRIBUTE19'
304 ,p_attribute19_value => p_rec.attribute19
305 ,p_attribute20_name => 'ATTRIBUTE20'
306 ,p_attribute20_value => p_rec.attribute20
307 ,p_attribute21_name => 'ATTRIBUTE21'
308 ,p_attribute21_value => p_rec.attribute21
309 ,p_attribute22_name => 'ATTRIBUTE22'
310 ,p_attribute22_value => p_rec.attribute22
311 ,p_attribute23_name => 'ATTRIBUTE23'
312 ,p_attribute23_value => p_rec.attribute23
313 ,p_attribute24_name => 'ATTRIBUTE24'
314 ,p_attribute24_value => p_rec.attribute24
315 ,p_attribute25_name => 'ATTRIBUTE25'
316 ,p_attribute25_value => p_rec.attribute25
317 ,p_attribute26_name => 'ATTRIBUTE26'
318 ,p_attribute26_value => p_rec.attribute26
319 ,p_attribute27_name => 'ATTRIBUTE27'
320 ,p_attribute27_value => p_rec.attribute27
321 ,p_attribute28_name => 'ATTRIBUTE28'
322 ,p_attribute28_value => p_rec.attribute28
323 ,p_attribute29_name => 'ATTRIBUTE29'
324 ,p_attribute29_value => p_rec.attribute29
325 ,p_attribute30_name => 'ATTRIBUTE30'
326 ,p_attribute30_value => p_rec.attribute30
327 );
328 null;
329 end if;
330 --
331 hr_utility.set_location(' Leaving:'||l_proc,20);
332 end chk_df;
333 --
334 -- ----------------------------------------------------------------------------
335 -- |-----------------------< chk_non_updateable_args >------------------------|
336 -- ----------------------------------------------------------------------------
337 -- {Start Of Comments}
338 --
339 -- Description:
340 -- This procedure is used to ensure that non updateable attributes have
341 -- not been updated. If an attribute has been updated an error is generated.
342 --
343 -- Pre Conditions:
344 -- g_old_rec has been populated with details of the values currently in
345 -- the database.
346 --
347 -- In Arguments:
348 -- p_rec has been populated with the updated values the user would like the
349 -- record set to.
350 --
351 -- Post Success:
352 -- Processing continues if all the non updateable attributes have not
353 -- changed.
354 --
355 -- Post Failure:
356 -- An application error is raised if any of the non updatable attributes
357 -- have been altered.
358 --
359 -- {End Of Comments}
360 -- ----------------------------------------------------------------------------
361 Procedure chk_non_updateable_args
362 (p_effective_date in date
363 ,p_rec in irc_iof_shd.g_rec_type
364 ) IS
365 --
366 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
367 --
368 Begin
369 --
370 -- Only proceed with the validation if a row exists for the current
371 -- record in the HR Schema.
372 --
373 IF NOT irc_iof_shd.api_updating
374 (p_offer_id => p_rec.offer_id
375 ,p_object_version_number => p_rec.object_version_number
376 ) THEN
377 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
378 fnd_message.set_token('PROCEDURE ', l_proc);
379 fnd_message.set_token('STEP ', '5');
380 fnd_message.raise_error;
381 END IF;
382 --
383 if irc_iof_shd.g_old_rec.offer_id <> p_rec.offer_id
384 then
385 hr_api.argument_changed_error
386 (p_api_name => l_proc
387 ,p_argument => 'offer_id'
388 ,p_base_table => irc_iof_shd.g_tab_name
389 );
390 end if;
391 --
392 if irc_iof_shd.g_old_rec.applicant_assignment_id <> p_rec.applicant_assignment_id
393 then
394 hr_api.argument_changed_error
395 (p_api_name => l_proc
396 ,p_argument => 'applicant_assignment_id'
397 ,p_base_table => irc_iof_shd.g_tab_name
398 );
399 end if;
400 --
401 if irc_iof_shd.g_old_rec.offer_assignment_id <> p_rec.offer_assignment_id
402 then
403 hr_api.argument_changed_error
404 (p_api_name => l_proc
405 ,p_argument => 'offer_assignment_id'
406 ,p_base_table => irc_iof_shd.g_tab_name
407 );
408 end if;
409 --
410 if irc_iof_shd.g_old_rec.vacancy_id <> p_rec.vacancy_id
411 then
412 hr_api.argument_changed_error
413 (p_api_name => l_proc
414 ,p_argument => 'vacancy_id'
415 ,p_base_table => irc_iof_shd.g_tab_name
416 );
417 end if;
418 --
419 if irc_iof_shd.g_old_rec.offer_version <> p_rec.offer_version
420 then
421 hr_api.argument_changed_error
422 (p_api_name => l_proc
423 ,p_argument => 'offer_version'
424 ,p_base_table => irc_iof_shd.g_tab_name
425 );
426 end if;
427 --
428 End chk_non_updateable_args;
429 --
430 -- ----------------------------------------------------------------------------
431 -- |-----------------------< chk_applicant_assignment_id >--------------------|
432 -- ----------------------------------------------------------------------------
433 -- {Start Of Comments}
434 --
435 -- Description:
436 -- This procedure ensures that an active assignment of type applicant('A')
437 -- is present.
438 --
439 -- Pre Conditions:
440 -- None
441 --
442 -- In Arguments:
443 -- p_applicant_assignment_id
444 -- p_effective_date
445 --
446 -- Post Success:
447 -- Processing continues if applicant assignment ID is not null and there is
448 -- an active assignment of type is Applicant.
449 --
450 -- Post Failure:
451 -- An application error is raised if offer ID is null or exists already
452 --
453 -- {End Of Comments}
454 -- ----------------------------------------------------------------------------
455 Procedure chk_applicant_assignment_id
456 (p_effective_date in date
457 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
458 ) IS
459 --
460 l_proc varchar2(72) := g_package || 'chk_applicant_assignment_id';
461 l_applicant_assignment_id number;
462 --
463 cursor csr_applicant_assignment_id is
464 select 1
465 from per_all_assignments_f
466 where assignment_id = p_applicant_assignment_id
467 and assignment_type = 'A'
468 and p_effective_date
469 between effective_start_date
470 and effective_end_date;
471 --
472 Begin
473 hr_utility.set_location('Entering:'||l_proc,10);
474 --
475 hr_api.mandatory_arg_error
476 (p_api_name => l_proc
477 ,p_argument => 'effective_date'
478 ,p_argument_value => p_effective_date
479 );
480
481 hr_api.mandatory_arg_error
482 (p_api_name => l_proc
483 ,p_argument => 'APPLICANT_ASSIGNMENT_ID'
484 ,p_argument_value => p_applicant_assignment_id
485 );
486 --
487 open csr_applicant_assignment_id;
488 fetch csr_applicant_assignment_id into l_applicant_assignment_id;
489 --
490 hr_utility.set_location(l_proc,20);
491 if (csr_applicant_assignment_id%notfound)
492 then
493 close csr_applicant_assignment_id;
494 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
495 fnd_message.raise_error;
496 end if;
497 close csr_applicant_assignment_id;
498 --
499 hr_utility.set_location(' Leaving:'||l_proc,30);
500 exception
501 when app_exception.application_exception then
502 if hr_multi_message.exception_add
503 (p_associated_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
504 ) then
505 hr_utility.set_location(' Leaving:'|| l_proc, 40);
506 raise;
507 end if;
508 hr_utility.set_location(' Leaving:'|| l_proc, 50);
509 End chk_applicant_assignment_id;
510 --
511 -- ----------------------------------------------------------------------------
512 -- |-----------------------< chk_offer_assignment_id >--------------------|
513 -- ----------------------------------------------------------------------------
514 -- {Start Of Comments}
515 --
516 -- Description:
517 -- This procedure ensures that assignment of type 'O'(Offers) is present
518 --
519 -- Pre Conditions:
520 -- None
521 --
522 -- In Arguments:
523 -- p_offer_assignment_id
524 --
525 -- Post Success:
526 -- Processing continues if applicant assignment ID is not null and
527 -- assignment_type is Offers
528 --
529 -- Post Failure:
530 -- An application error is raised if offer ID is null or exists already
531 --
532 -- {End Of Comments}
533 -- ----------------------------------------------------------------------------
534 Procedure chk_offer_assignment_id
535 (p_offer_assignment_id in irc_offers.offer_assignment_id%TYPE
536 ) IS
537 --
538 l_proc varchar2(72) := g_package || 'chk_offer_assignment_id';
539 l_offer_assignment_id number;
540 l_offer_assignment_exists number;
541 --
542 cursor csr_offer_assignment_id is
543 select 1
544 from per_all_assignments_f
545 where assignment_id = p_offer_assignment_id
546 and assignment_type = 'O';
547 --
548 cursor csr_offer_assigment_exists is
549 select 1
550 from irc_offers
551 where offer_assignment_id = p_offer_assignment_id;
552 --
553 Begin
554 hr_utility.set_location('Entering:'||l_proc,10);
555 --
556 if hr_multi_message.no_exclusive_error(
557 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
558 ) then
559 --
560 hr_api.mandatory_arg_error
561 (p_api_name => l_proc
562 ,p_argument => 'OFFER_ASSIGNMENT_ID'
563 ,p_argument_value => p_offer_assignment_id
564 );
565 --
566 open csr_offer_assignment_id;
567 fetch csr_offer_assignment_id into l_offer_assignment_id;
568 --
569 if (csr_offer_assignment_id%notfound)
570 then
571 --
572 hr_utility.set_location(l_proc,20);
573 --
574 close csr_offer_assignment_id;
575 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
576 fnd_message.raise_error;
577 end if;
578 close csr_offer_assignment_id;
579 --
580 -- Check to see if an offer already exists with this
581 -- offer assignment.
582 --
583 open csr_offer_assigment_exists;
584 fetch csr_offer_assigment_exists into l_offer_assignment_exists;
585
586 if (csr_offer_assigment_exists%found)
587 then
588 --
589 hr_utility.set_location(l_proc,25);
590 --
591 close csr_offer_assigment_exists;
592 fnd_message.set_name('PER','IRC_412348_OFR_ASNMT_EXISTS');
593 fnd_message.raise_error;
594 end if;
595 close csr_offer_assigment_exists;
596 end if; -- no_exclusive_error
597 --
598 hr_utility.set_location(' Leaving:'||l_proc,30);
599 exception
600 when app_exception.application_exception then
601 if hr_multi_message.exception_add
602 (p_associated_column1 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
603 ) then
604 hr_utility.set_location(' Leaving:'|| l_proc, 40);
605 raise;
606 end if;
607 hr_utility.set_location(' Leaving:'|| l_proc, 50);
608 End chk_offer_assignment_id;
609 --
610 -- ----------------------------------------------------------------------------
611 -- |---------------------------< set_vacancy_id >-----------------------------|
612 -- ----------------------------------------------------------------------------
613 -- {Start Of Comments}
614 --
615 -- Description:
616 -- This procedure sets the vacancy from the applicant assignment vacancy_id
617 --
618 -- Pre Conditions:
619 -- None
620 --
621 -- In Arguments:
622 -- p_effective_date
623 -- p_applicant_assignment_id
624 --
625 -- Post Success:
626 -- The vacancy_id is set from assignment record
627 --
628 -- Out Arguments:
629 -- p_vacancy_id
630 --
631 -- {End Of Comments}
632 -- ----------------------------------------------------------------------------
633 Procedure set_vacancy_id
634 (p_vacancy_id out nocopy irc_offers.vacancy_id%TYPE
635 ,p_effective_date in date
636 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
637 ) IS
638 --
639 l_proc varchar2(72) := g_package || 'set_vacancy_id';
640 l_vacancy_id irc_offers.vacancy_id%TYPE;
641 --
642 cursor csr_appl_vac_id is
643 select paaf.vacancy_id
644 from per_all_assignments_f paaf
645 where paaf.assignment_id = p_applicant_assignment_id
646 and p_effective_date
647 between effective_start_date
648 and effective_end_date;
649 --
650 Begin
651 hr_utility.set_location('Entering:'||l_proc,10);
652 --
653 if hr_multi_message.no_exclusive_error(
654 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
655 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
656 ) then
657 --
658 -- Select the vacancy_id from Applicant assignment record.
659 --
660 open csr_appl_vac_id;
661 fetch csr_appl_vac_id into l_vacancy_id;
662 --
663 hr_utility.set_location(l_proc,20);
664 if (csr_appl_vac_id%notfound)
665 then
666 --
667 hr_utility.set_location(l_proc,30);
668 --
669 close csr_appl_vac_id;
670 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
671 fnd_message.raise_error;
672 end if;
673 close csr_appl_vac_id;
674 --
675 p_vacancy_id := l_vacancy_id;
676 --
677 end if; -- no_exclusive_error
678 --
679 hr_utility.set_location(' Leaving:'||l_proc,40);
680 exception
681 when app_exception.application_exception then
682 if hr_multi_message.exception_add
683 (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
684 ) then
685 hr_utility.set_location(' Leaving:'|| l_proc, 50);
686 raise;
687 end if;
688 hr_utility.set_location(' Leaving:'|| l_proc, 60);
689 End set_vacancy_id;
690 --
691 -- ----------------------------------------------------------------------------
692 -- |--------------------< chk_offers_exceeds_openings >-----------------------|
693 -- ----------------------------------------------------------------------------
694 -- {Start Of Comments}
695 --
696 -- Description:
697 -- This procedure ensures that Number of Openings on a vacancy does not exceed
698 -- the number of Offers with the Status of Extended and Applicant Assignments
699 -- for the Vacancy with status of Accepted.
700 --
701 -- Pre Conditions:
702 -- None
703 --
704 -- In Arguments:
705 -- p_vacancy_id
706 -- p_offer_status
707 --
708 -- Post Success:
709 -- Processing continues if the number of openings for the vacancy have not been
710 -- exceeded.
711 --
712 -- Post Failure:
713 -- An application error is raised if number of offers for the vacancy have
714 -- exceeded the number of openings.
715 --
716 -- {End Of Comments}
717 -- ----------------------------------------------------------------------------
718 Procedure chk_offers_exceeds_openings
719 (p_vacancy_id in irc_offers.vacancy_id%TYPE
720 ,p_offer_status in irc_offers.offer_status%TYPE
721 ,p_offer_id in irc_offers.offer_id%TYPE
722 ) IS
723 --
724 l_proc varchar2(72) := g_package || 'chk_offers_exceeds_openings';
725 l_offer_count number(15);
726 l_prev_offer_status irc_offers.offer_status%TYPE;
727 l_prev_change_reason irc_offer_status_history.change_reason%TYPE;
728 l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
729 l_prev_to_prev_change_reason irc_offer_status_history.change_reason%TYPE;
730 l_chk_vacancy_count boolean := false;
731 --
732 cursor csr_vacancy_opening_count is
733 select budget_measurement_value
734 ,budget_measurement_type
735 from per_all_vacancies
736 where vacancy_id = p_vacancy_id;
737 --
738 cursor csr_prev_to_prev_offer_chg_rsn is
739 select ios1.offer_status
740 ,ios1.change_reason
741 from irc_offer_status_history ios1
742 where ios1.offer_id = p_offer_id
743 and EXISTS
744 (SELECT 1
745 FROM irc_offer_status_history iosh1
746 WHERE iosh1.offer_id = ios1.offer_id
747 AND iosh1.status_change_date > ios1.status_change_date
748 )
749 AND ios1.offer_status_history_id =
750 (SELECT MAX(iosh2.offer_status_history_id)
751 FROM irc_offer_status_history iosh2
752 WHERE iosh2.offer_id = ios1.offer_id
753 AND iosh2.status_change_date = ios1.status_change_date
754 )
755 AND 1 =
756 (SELECT COUNT(*)
757 FROM irc_offer_status_history ios3
758 WHERE ios3.offer_id = ios1.offer_id
759 AND ios3.status_change_date > ios1.status_change_date
760 );
761 --
762 cursor csr_prev_offer_status is
763 select offer_status
764 from irc_offers
765 where offer_id = p_offer_id;
766 --
767 cursor csr_offer_count is
768 select count(*)
769 from irc_offers iof
770 ,per_all_vacancies pav
771 ,irc_offer_status_history iosh
772 where pav.vacancy_id = p_vacancy_id
773 and iof.vacancy_id = pav.vacancy_id
774 and iosh.offer_id = iof.offer_id
775 and iof.offer_id <> p_offer_id
776 AND NOT EXISTS
777 (SELECT 1
778 FROM irc_offer_status_history iosh1
779 WHERE iosh1.offer_id = iosh.offer_id
780 AND iosh1.status_change_date > iosh.status_change_date
781 )
782 AND iosh.offer_status_history_id =
783 (SELECT MAX(iosh2.offer_status_history_id)
784 FROM irc_offer_status_history iosh2
785 WHERE iosh2.offer_id = iosh.offer_id
786 AND iosh2.status_change_date = iosh.status_change_date
787 )
788 and iof.latest_offer = 'Y'
789 and ( iof.offer_status = 'EXTENDED' or ( iof.offer_status = 'CLOSED' and iosh.change_reason = 'APL_ACCEPTED'));
790 --
791 l_vacancy_opening_count csr_vacancy_opening_count%ROWTYPE;
792 --
793 Begin
794 hr_utility.set_location('Entering:'||l_proc,10);
795 --
796 if p_offer_status = 'CLOSED'
797 then
798 --
799 open csr_prev_offer_status;
800 fetch csr_prev_offer_status into l_prev_offer_status;
801 if csr_prev_offer_status%notfound
802 then
803 --
804 close csr_prev_offer_status;
805 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
806 fnd_message.raise_error;
807 --
808 end if;
809 close csr_prev_offer_status;
810 --
811 if l_prev_offer_status = 'HOLD'
812 then
813 --
814 -- We now know that a closed offer has been taken off hold.
815 -- Check if the offer was closed because it was accepted.
816 --
817 open csr_prev_to_prev_offer_chg_rsn;
818 fetch csr_prev_to_prev_offer_chg_rsn into l_prev_to_prev_offer_status
819 ,l_prev_to_prev_change_reason;
820 if csr_prev_to_prev_offer_chg_rsn%notfound
821 then
822 --
823 close csr_prev_to_prev_offer_chg_rsn;
824 fnd_message.set_name('PER','IRC_412305_INV_PREVTOPREV_OFR');
825 fnd_message.raise_error;
826 --
827 end if;
828 close csr_prev_to_prev_offer_chg_rsn;
829 --
830 if ( l_prev_to_prev_offer_status = 'CLOSED' -- just a double check
831 AND l_prev_to_prev_change_reason = 'APL_ACCEPTED'
832 )
833 then
834 --
835 -- We now know that an Accepted offer, on hold has been taken off hold.
836 -- Hence, check for vacancy count
837 --
838 l_chk_vacancy_count := true;
839 --
840 end if;
841 --
842 end if;
843 --
844 elsif p_offer_status = 'EXTENDED'
845 then
846 --
847 l_chk_vacancy_count := true;
848 --
849 end if;
850 --
851 if l_chk_vacancy_count = true
852 then
853 --
854 if hr_multi_message.no_exclusive_error(
855 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
856 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
857 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
858 )
859 then
860 --
861 hr_api.mandatory_arg_error
862 (p_api_name => l_proc
863 ,p_argument => 'VACANCY_ID'
864 ,p_argument_value => p_vacancy_id
865 );
866 --
867 open csr_vacancy_opening_count;
868 fetch csr_vacancy_opening_count into l_vacancy_opening_count;
869 close csr_vacancy_opening_count;
870 --
871 hr_utility.set_location(l_proc,20);
872 --
873 if (l_vacancy_opening_count.budget_measurement_type <> 'FTE')
874 then
875 --
876 open csr_offer_count;
877 fetch csr_offer_count into l_offer_count;
878 close csr_offer_count;
879 --
880 if (l_offer_count >= l_vacancy_opening_count.budget_measurement_value)
881 then
882 fnd_message.set_name('PER','IRC_412331_OFR_EXCDD_VAC_COUNT');
883 fnd_message.raise_error;
884 end if;
885 --
886 end if;
887 --
888 end if; -- no_exclusive_error
889 --
890 end if; -- if l_chk_vacancy_count check
891 hr_utility.set_location(' Leaving:'||l_proc,30);
892 exception
893 when app_exception.application_exception then
894 if hr_multi_message.exception_add
895 (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
896 ) then
897 hr_utility.set_location(' Leaving:'|| l_proc, 40);
898 raise;
899 end if;
900 hr_utility.set_location(' Leaving:'|| l_proc, 50);
901 End chk_offers_exceeds_openings;
902 --
903 -- ----------------------------------------------------------------------------
904 -- |--------------------------< chk_respondent_id >---------------------------|
905 -- ----------------------------------------------------------------------------
906 -- {Start Of Comments}
907 --
908 -- Description:
909 -- This procedure ensures that the respondent is an existing user.
910 --
911 -- Pre Conditions:
912 -- None
913 --
914 -- In Arguments:
915 -- p_respondent_id
916 -- p_offer_id
917 -- p_object_version_number
918 --
919 -- Post Success:
920 -- Processing continues if the respondent exists
921 --
922 -- Post Failure:
923 -- An application error is raised if Respondent is not an existing user
924 --
925 -- {End Of Comments}
926 -- ----------------------------------------------------------------------------
927 Procedure chk_respondent_id
928 (p_respondent_id in irc_offers.respondent_id%TYPE
929 ,p_offer_id in irc_offers.offer_id%TYPE
930 ,p_object_version_number in irc_offers.object_version_number%TYPE
931 ) IS
932 --
933 l_proc varchar2(72) := g_package || 'chk_respondent_id';
934 l_api_updating boolean;
935 l_respondent_id number;
936 --
937 cursor csr_respondent_id is
938 select 1
939 from fnd_user
940 where user_id = p_respondent_id;
941 --
942 Begin
943 hr_utility.set_location('Entering:'||l_proc,10);
944 --
945 if hr_multi_message.no_exclusive_error(
946 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
947 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
948 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
949 ) then
950 --
951 -- Only proceed with validation if :
952 -- a) The current g_old_rec is current and
953 -- b) The value for respondant_id has changed
954 --
955 l_api_updating := irc_iof_shd.api_updating
956 (p_offer_id => p_offer_id
957 ,p_object_version_number => p_object_version_number);
958 --
959 hr_utility.set_location(l_proc,20);
960
961 if ((l_api_updating
962 and nvl(irc_iof_shd.g_old_rec.respondent_id, hr_api.g_number) <>
963 nvl(p_respondent_id, hr_api.g_number))
964 or
965 (NOT l_api_updating)) then
966 --
967 -- Check if respondent id is not null.
968 --
969 if p_respondent_id is not null then
970
971 hr_utility.set_location(l_proc, 30);
972
973 open csr_respondent_id;
974 fetch csr_respondent_id into l_respondent_id;
975 --
976 hr_utility.set_location(l_proc,40);
977 if (csr_respondent_id%notfound)
978 then
979 close csr_respondent_id;
980 fnd_message.set_name('FND','FND_GRANTS_GNT_USER_INVALID');
981 fnd_message.raise_error;
982 end if;
983 close csr_respondent_id;
984 --
985 hr_utility.set_location(' Leaving:'||l_proc,50);
986 --
987 end if;
988 end if;
989 end if; -- no_exclusive_error
990 --
991 exception
992 when app_exception.application_exception then
993 if hr_multi_message.exception_add
994 (p_associated_column1 => 'IRC_OFFERS.RESPONDENT_ID'
995 ) then
996 hr_utility.set_location(' Leaving:'|| l_proc, 60);
997 raise;
998 end if;
999 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1000 End chk_respondent_id;
1001 --
1002 -- ----------------------------------------------------------------------------
1003 -- |---------------------------< chk_expiry_date >----------------------------|
1004 -- ----------------------------------------------------------------------------
1005 -- {Start Of Comments}
1006 --
1007 -- Description:
1008 -- This procedure ensures that while EXTENDING the offer, if the expirty date
1009 -- is NULL, it is set to the calculated value from the profiles
1010 -- IRC_OFFER_DURATION_MEASUREMENT and IRC_OFFER_DURATION_VALUE.
1011 --
1012 -- Pre Conditions:
1013 -- None
1014 --
1015 -- In Arguments:
1016 -- p_expiry_date
1017 -- p_offer_status
1018 -- p_effective_date
1019 --
1020 -- Out Arguments:
1021 -- p_expiry_date
1022 --
1023 -- Post Success:
1024 -- The expiry date is set to a calculated value if null.
1025 --
1026 --
1027 -- {End Of Comments}
1028 -- ----------------------------------------------------------------------------
1029 Procedure chk_expiry_date
1030 (p_expiry_date in out nocopy irc_offers.expiry_date%TYPE
1031 ,p_offer_status in irc_offers.offer_status%TYPE
1032 ,p_offer_id in irc_offers.offer_id%TYPE
1033 ,p_offer_postal_service in irc_offers.offer_postal_service%TYPE
1034 ,p_offer_letter_tracking_code in irc_offers.offer_letter_tracking_code%TYPE
1035 ,p_offer_shipping_date in irc_offers.offer_shipping_date%TYPE
1036 ,p_effective_date date
1037 ) IS
1038 --
1039 l_proc varchar2(72) := g_package || 'chk_expiry_date';
1040 l_offer_duration_value varchar2(30);
1041 l_offer_duration_measurement varchar2(30);
1042 l_expiry_date irc_offers.expiry_date%TYPE := p_expiry_date;
1043 l_prev_expiry_date irc_offers.expiry_date%TYPE := irc_iof_shd.g_old_rec.expiry_date;
1044 l_prev_offer_postal_service irc_offers.offer_postal_service%TYPE := irc_iof_shd.g_old_rec.offer_postal_service;
1045 l_prev_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE := irc_iof_shd.g_old_rec.offer_letter_tracking_code;
1046 l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE := irc_iof_shd.g_old_rec.offer_shipping_date;
1047 l_effective_date date;
1048 --
1049 Begin
1050 hr_utility.set_location('Entering:'||l_proc,10);
1051 --
1052 if hr_multi_message.no_exclusive_error(
1053 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1054 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1055 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1056 ) then
1057 --
1058 if p_offer_status = 'EXTENDED'
1059 then
1060 --
1061 hr_utility.set_location(l_proc, 20);
1062 --
1063 if p_expiry_date is not null
1064 then
1065 --
1066 -- Set the effective date
1067 --
1068 if p_expiry_date < p_effective_date
1069 then
1070 --
1071 fnd_message.set_name('PER','IRC_412352_INV_EXP_DATE');
1072 fnd_message.raise_error;
1073 --
1074 end if;
1075 --
1076 else -- p_expiry_date is null or is the previous expiry date
1077 --
1078 -- Check if the intend of this update is to indeed update the expiry date.
1079 --
1080 if( nvl(p_offer_postal_service,hr_api.g_varchar2) = nvl(l_prev_offer_postal_service,hr_api.g_varchar2) -- Postal Service has not changed
1081 and nvl(p_offer_letter_tracking_code,hr_api.g_varchar2) = nvl(l_prev_letter_tracking_code,hr_api.g_varchar2) -- Tracking Code has not changed
1082 and nvl(p_offer_shipping_date,hr_api.g_date) = nvl(l_prev_offer_shipping_date,hr_api.g_date) -- Shipping Date has not changed
1083 )
1084 then
1085 --
1086 -- No value has been entered for the expiry date.
1087 -- Set the expiry date to the calculated value.
1088 --
1089 l_offer_duration_value := to_number(fnd_profile.value('IRC_OFFER_DURATION_VALUE'));
1090 l_offer_duration_measurement := fnd_profile.value('IRC_OFFER_DURATION_MEASUREMENT');
1091 --
1092 if l_offer_duration_value is not null
1093 then
1094 --
1095 hr_utility.set_location(l_proc, 30);
1096 --
1097 -- Set the effective date
1098 --
1099 if l_prev_expiry_date > p_effective_date
1100 then
1101 --
1102 l_effective_date := l_prev_expiry_date;
1103 --
1104 else
1105 --
1106 l_effective_date := p_effective_date;
1107 --
1108 end if;
1109 --
1110 if l_offer_duration_measurement = 'MONTH'
1111 then
1112 --
1113 hr_utility.set_location(l_proc, 60);
1114 --
1115 l_expiry_date := add_months(l_effective_date,l_offer_duration_value);
1116 --
1117 elsif l_offer_duration_measurement = 'WEEK'
1118 then
1119 --
1120 hr_utility.set_location(l_proc, 50);
1121 --
1122 l_expiry_date := l_effective_date + (l_offer_duration_value * 7);
1123 --
1124 else -- By default l_offer_duration_measurement = 'DAY'
1125 --
1126 hr_utility.set_location(l_proc, 40);
1127 --
1128 l_expiry_date := l_effective_date + l_offer_duration_value;
1129 --
1130 end if;
1131 --
1132 else -- l_offer_duration_value is null
1133 --
1134 -- Both, the entered value and the profile value are null. Throw an error
1135 --
1136 fnd_message.set_name('PER','IRC_412353_NULL_EXPIRY_DATE');
1137 fnd_message.raise_error;
1138 --
1139 end if;
1140 --
1141 end if;
1142 --
1143 end if;
1144 --
1145 end if; -- p_offer_status = 'EXTENDED'
1146 --
1147 end if; -- no_exclusive_error
1148 --
1149 -- Set the in out variable
1150 --
1151 p_expiry_date := l_expiry_date;
1152 --
1153 hr_utility.set_location(' Leaving:'||l_proc,70);
1154 --
1155 exception
1156 when app_exception.application_exception then
1157 if hr_multi_message.exception_add
1158 (p_associated_column1 => 'IRC_OFFERS.EXPIRY_DATE'
1159 ) then
1160 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1161 raise;
1162 end if;
1163 hr_utility.set_location(' Leaving:'|| l_proc, 90);
1164 End chk_expiry_date;
1165 --
1166 -- ----------------------------------------------------------------------------
1167 -- |----------------------------< set_address_id >----------------------------|
1168 -- ----------------------------------------------------------------------------
1169 -- {Start Of Comments}
1170 --
1171 -- Description:
1172 -- This procedure defaults the address to the Recruiting address Id if it is
1173 -- available for the primary person and if not address_id is passed in
1174 --
1175 -- Pre Conditions:
1176 -- None
1177 --
1178 -- In Arguments:
1179 -- p_address_id
1180 -- p_applicant_assignment_id
1181 -- p_effective_date
1182 --
1183 -- Out Arguments:
1184 -- p_address_id
1185 --
1186 -- Post Success:
1187 -- If a Recruiting address exists for the person, that address is set in the offer
1188 -- record
1189 --
1190 -- Post Failure:
1191 -- If the person does not have a recruiting address, the value remains null
1192 --
1193 -- {End Of Comments}
1194 -- ----------------------------------------------------------------------------
1195 Procedure set_address_id
1196 (p_address_id in out nocopy irc_offers.address_id%TYPE
1197 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1198 ,p_effective_date date
1199 ) IS
1200 --
1201 l_proc varchar2(72) := g_package || 'set_address_id';
1202 l_rec_address_id irc_offers.address_id%TYPE;
1203 --
1204 cursor csr_rec_address_id is
1205 select adr.address_id
1206 from per_addresses adr
1207 ,per_all_assignments_f asg
1208 where asg.assignment_id = p_applicant_assignment_id
1209 and adr.person_id = irc_utilities_pkg.get_recruitment_person_id(asg.person_id,trunc(sysdate))
1210 and adr.address_type = 'REC'
1211 and p_effective_date
1212 between adr.date_from
1213 and nvl(adr.date_to, trunc(sysdate));
1214 --
1215 Begin
1216 hr_utility.set_location('Entering:'||l_proc,10);
1217 --
1218 if hr_multi_message.no_exclusive_error(
1219 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1220 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1221 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1222 ) then
1223 --
1224 hr_utility.set_location(l_proc,20);
1225 --
1226 -- Default the address_id only if it is null
1227 --
1228 if p_address_id is null then
1229 --
1230 open csr_rec_address_id;
1231 fetch csr_rec_address_id into l_rec_address_id;
1232 --
1233 hr_utility.set_location(l_proc,30);
1234 if (csr_rec_address_id%found)
1235 then
1236 --
1237 -- The person has a recruiting address. Hence, default the address_id
1238 -- in offer record to this value.
1239 --
1240 p_address_id := l_rec_address_id;
1241 --
1242 end if;
1243 close csr_rec_address_id;
1244 --
1245 hr_utility.set_location(' Leaving:'||l_proc,40);
1246 end if;
1247 end if; -- no_exclusive_error
1248 --
1249 exception
1250 when app_exception.application_exception then
1251 if hr_multi_message.exception_add
1252 (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1253 ) then
1254 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1255 raise;
1256 end if;
1257 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1258 End set_address_id;
1259 --
1260 -- ----------------------------------------------------------------------------
1261 -- |----------------------------< chk_address_id >----------------------------|
1262 -- ----------------------------------------------------------------------------
1263 -- {Start Of Comments}
1264 --
1265 -- Description:
1266 -- This procedure ensures that the address is a valid address
1267 --
1268 -- Pre Conditions:
1269 -- None
1270 --
1271 -- In Arguments:
1272 -- p_address_id
1273 -- p_offer_id
1274 -- p_object_version_number
1275 --
1276 -- Post Success:
1277 -- Processing continues if the address exists
1278 --
1279 -- Post Failure:
1280 -- An application error is raised if address is not valid
1281 --
1282 -- {End Of Comments}
1283 -- ----------------------------------------------------------------------------
1284 Procedure chk_address_id
1285 (p_address_id in irc_offers.address_id%TYPE
1286 ,p_offer_id in irc_offers.offer_id%TYPE
1287 ,p_object_version_number in irc_offers.object_version_number%TYPE
1288 ) IS
1289 --
1290 l_proc varchar2(72) := g_package || 'chk_address_id';
1291 l_address_id number;
1292 l_api_updating boolean;
1293 --
1294 cursor csr_address_id is
1295 select 1
1296 from per_addresses
1297 where address_id = p_address_id;
1298 --
1299 Begin
1300 hr_utility.set_location('Entering:'||l_proc,10);
1301 --
1302 if hr_multi_message.no_exclusive_error(
1303 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1304 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1305 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1306 ) then
1307 --
1308 -- Only proceed with validation if :
1309 -- a) The current g_old_rec is current and
1310 -- b) The value for address_id has changed
1311 --
1312 l_api_updating := irc_iof_shd.api_updating
1313 (p_offer_id => p_offer_id
1314 ,p_object_version_number => p_object_version_number);
1315 --
1316 hr_utility.set_location(l_proc,20);
1317
1318 if ((l_api_updating
1319 and nvl(irc_iof_shd.g_old_rec.address_id, hr_api.g_number) <>
1320 nvl(p_address_id, hr_api.g_number))
1321 or
1322 (NOT l_api_updating)) then
1323 --
1324 -- Check if address id is not null.
1325 --
1326 if p_address_id is not null then
1327 --
1328 open csr_address_id;
1329 fetch csr_address_id into l_address_id;
1330 --
1331 hr_utility.set_location(l_proc,30);
1332 if (csr_address_id%notfound)
1333 then
1334 close csr_address_id;
1335 fnd_message.set_name('PER','IRC_412001_BAD_ADDRESS_ID');
1336 fnd_message.raise_error;
1337 end if;
1338 close csr_address_id;
1339 --
1340 hr_utility.set_location(' Leaving:'||l_proc,40);
1341 end if;
1342 end if;
1343 end if; -- no_exclusive_error
1344 --
1345 exception
1346 when app_exception.application_exception then
1347 if hr_multi_message.exception_add
1348 (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1349 ) then
1350 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1351 raise;
1352 end if;
1353 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1354 End chk_address_id;
1355 --
1356 -- ----------------------------------------------------------------------------
1357 -- |----------------------------< chk_template_id >---------------------------|
1358 -- ----------------------------------------------------------------------------
1359 -- {Start Of Comments}
1360 --
1361 -- Description:
1362 -- This procedure ensures that the offer template is valid
1363 --
1364 -- Pre Conditions:
1365 -- None
1366 --
1367 -- In Arguments:
1368 -- p_template_id
1369 -- p_offer_id
1370 -- p_object_version_number
1371 -- p_effective_date
1372 --
1373 -- Post Success:
1374 -- Processing continues if the template exists
1375 --
1376 -- Out Arguments:
1377 -- p_template_id
1378 --
1379 -- Post Failure:
1380 -- An application error is raised if template is not valid
1381 --
1382 -- {End Of Comments}
1383 -- ----------------------------------------------------------------------------
1384 Procedure chk_template_id
1385 (p_template_id in out nocopy irc_offers.template_id%TYPE
1386 ,p_offer_id in irc_offers.offer_id%TYPE
1387 ,p_object_version_number in irc_offers.object_version_number%TYPE
1388 ,p_effective_date date
1389 ) IS
1390 --
1391 l_proc varchar2(72) := g_package || 'chk_template_id';
1392 l_template_id irc_template_associations.template_id%TYPE;
1393 l_api_updating boolean;
1394 --
1395 cursor csr_template_id is
1396 select 1
1397 from xdo_templates_b
1398 where template_id = p_template_id
1399 and p_effective_date
1400 between start_date
1401 and nvl(end_date,p_effective_date);
1402 --
1403 cursor csr_default_template_job is
1404 select ita.template_id
1405 from irc_template_associations ita
1406 ,per_all_assignments_f ppaf
1407 ,irc_offers iof
1408 where ita.default_association = 'Y'
1409 and iof.offer_id = p_offer_id
1410 and iof.offer_assignment_id = ppaf.assignment_id
1411 and ita.job_id = ppaf.job_id;
1412 --
1413 cursor csr_default_template_pos is
1414 select ita.template_id
1415 from irc_template_associations ita
1416 ,per_all_assignments_f ppaf
1417 ,irc_offers iof
1418 where ita.default_association = 'Y'
1419 and iof.offer_id = p_offer_id
1420 and iof.offer_assignment_id = ppaf.assignment_id
1421 and ita.position_id = ppaf.position_id;
1422 --
1423 cursor csr_default_template_org is
1424 select ita.template_id
1425 from irc_template_associations ita
1426 ,per_all_assignments_f ppaf
1427 ,irc_offers iof
1428 where ita.default_association = 'Y'
1429 and iof.offer_id = p_offer_id
1430 and iof.offer_assignment_id = ppaf.assignment_id
1431 and ita.organization_id = ppaf.organization_id;
1432 --
1433 Begin
1434 hr_utility.set_location('Entering:'||l_proc,10);
1435 --
1436 if hr_multi_message.no_exclusive_error(
1437 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1438 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1439 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1440 ) then
1441 --
1442 -- Only proceed with validation if :
1443 -- a) The current g_old_rec is current and
1444 -- b) The value for template_id has changed
1445 --
1446 l_api_updating := irc_iof_shd.api_updating
1447 (p_offer_id => p_offer_id
1448 ,p_object_version_number => p_object_version_number);
1449 --
1450 hr_utility.set_location(l_proc,20);
1451
1452 if ((l_api_updating
1453 and nvl(irc_iof_shd.g_old_rec.template_id, hr_api.g_number) <>
1454 nvl(p_template_id, hr_api.g_number)) or
1455 (NOT l_api_updating)) then
1456 --
1457 -- Check if template id is not null and if it is active
1458 --
1459 if p_template_id is not null then
1460 --
1461 hr_utility.set_location(l_proc,30);
1462 --
1463 open csr_template_id;
1464 fetch csr_template_id into l_template_id;
1465 --
1466 if (csr_template_id%notfound)
1467 then
1468 --
1469 hr_utility.set_location(l_proc,40);
1470 --
1471 close csr_template_id;
1472 fnd_message.set_name('PER','IRC_412326_OFFER_INV_TEMPLT_ID');
1473 fnd_message.raise_error;
1474 end if;
1475 close csr_template_id;
1476 --
1477 else
1478 --
1479 hr_utility.set_location(l_proc,50);
1480 --
1481 open csr_default_template_job;
1482 fetch csr_default_template_job into l_template_id;
1483 --
1484 if (csr_default_template_job%notfound)
1485 then
1486 --
1487 hr_utility.set_location(l_proc,60);
1488 --
1489 close csr_default_template_job;
1490 --
1491 open csr_default_template_pos;
1492 fetch csr_default_template_pos into l_template_id;
1493 --
1494 if (csr_default_template_pos%notfound)
1495 then
1496 --
1497 hr_utility.set_location(l_proc,70);
1498 --
1499 close csr_default_template_pos;
1500 --
1501 open csr_default_template_org;
1502 fetch csr_default_template_org into l_template_id;
1503 --
1504 if (csr_default_template_org%notfound)
1505 then
1506 --
1507 hr_utility.set_location(l_proc,80);
1508 --
1509 close csr_default_template_org;
1510 l_template_id := p_template_id;
1511 --
1512 end if; --org
1513 end if; --pos
1514 end if; --job
1515 --
1516 end if; -- if - else - endif;
1517 end if; -- l_api_updating
1518 end if; -- no_exclusive_error
1519 hr_utility.set_location(' Leaving:'||l_proc,90);
1520 --
1521 exception
1522 when app_exception.application_exception then
1523 if hr_multi_message.exception_add
1524 (p_associated_column1 => 'IRC_OFFERS.TEMPLATE_ID'
1525 ) then
1526 hr_utility.set_location(' Leaving:'|| l_proc, 100);
1527 raise;
1528 end if;
1529 hr_utility.set_location(' Leaving:'|| l_proc, 110);
1530 End chk_template_id;
1531 --
1532 -- ----------------------------------------------------------------------------
1533 -- |--------------------------< gen_offer_version >---------------------------|
1534 -- ----------------------------------------------------------------------------
1535 -- {Start Of Comments}
1536 --
1537 -- Description:
1538 -- This procedure generates then offer_version number
1539 --
1540 -- Pre Conditions:
1541 -- None
1542 --
1543 -- In Arguments:
1544 -- p_offer_version
1545 -- p_applicant_assignment_id
1546 --
1547 -- Post Success:
1548 -- A new offer version number is generated.
1549 --
1550 -- {End Of Comments}
1551 -- ----------------------------------------------------------------------------
1552 Procedure gen_offer_version
1553 (p_offer_version out nocopy irc_offers.offer_version%TYPE
1554 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1555 ) IS
1556 --
1557 l_proc varchar2(72) := g_package || 'gen_offer_version';
1558 l_offer_version irc_offers.offer_version%TYPE;
1559 --
1560 cursor csr_get_offer_version is
1561 select nvl(max(offer_version),0) + 1
1562 from irc_offers
1563 where ( applicant_assignment_id = nvl(p_applicant_assignment_id,-1)
1564 OR
1565 applicant_assignment_id = nvl(irc_offers_api.g_src_apl_asg_id,-1)
1566 OR
1567 applicant_assignment_id in
1568 (
1569 select tgt_apl_asg_id from per_vac_linked_assignments
1570 where src_apl_asg_id = irc_offers_api.g_src_apl_asg_id
1571 )
1572 );
1573
1574 --
1575 Begin
1576 hr_utility.set_location('Entering:'||l_proc,10);
1577 --
1578 hr_api.mandatory_arg_error
1579 (p_api_name => l_proc
1580 ,p_argument => 'APPLICANT_ASSIGNMENT_ID'
1581 ,p_argument_value => p_applicant_assignment_id
1582 );
1583 --
1584 --
1585 -- Generate next offer version number
1586 --
1587 open csr_get_offer_version;
1588 fetch csr_get_offer_version into l_offer_version;
1589 close csr_get_offer_version;
1590 p_offer_version := l_offer_version;
1591 --
1592 hr_utility.set_location(' Leaving:'||l_proc,20);
1593 --
1594 exception
1595 when app_exception.application_exception then
1596 if hr_multi_message.exception_add
1597 (p_associated_column1 => 'IRC_OFFERS.OFFER_VERSION'
1598 ) then
1599 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1600 raise;
1601 end if;
1602 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1603 End gen_offer_version;
1604 --
1605 -- ----------------------------------------------------------------------------
1606 -- |--------------------------< chk_latest_offer >---------------------------|
1607 -- ----------------------------------------------------------------------------
1608 -- {Start Of Comments}
1609 --
1610 -- Description:
1611 -- The following checks ensure that only one offer is the latest offer for a particular
1612 -- applicant assignment. This check works in the following manner:
1613 --
1614 -- 1) During Insert:
1615 -- If the offer is in SFL status:
1616 -- The latest offer for this record will be 'N', hence not required to perform this
1617 -- validation.
1618 -- Else
1619 -- The offer being insterted should be the latest offer and there should be no
1620 -- other latest offers for this applicant assignment.
1621 --
1622 -- 2) During Update:
1623 -- If the offer is not in SFL status:
1624 -- The offer being updated should be the latest offer and there should be no
1625 -- other latest offers for this applicant assignment.
1626 --
1627 -- 3) The value entered should be validated against HR_LOOKUPS.LOOKUP_CODE
1628 -- where the LOOKUP_TYPE is 'YES_NO'. (I, U)
1629 -- Process: hr_api.not_exists_in_hr_lookups
1630 --
1631 -- Pre Conditions:
1632 -- None
1633 --
1634 -- In Arguments:
1635 -- p_latest_offer
1636 -- p_offer_id
1637 -- p_offer_status
1638 -- p_applicant_assignment_id
1639 -- p_effective_date
1640 -- p_object_version_number
1641 --
1642 -- Post Success:
1643 -- During insert:
1644 -- Processing continues if no other record for this application assignment id
1645 -- is the latest offer.
1646 -- During update:
1647 -- Processing continues if latest_offer exists
1648 --
1649 -- Post Failure:
1650 -- An application error is raised.
1651 --
1652 -- {End Of Comments}
1653 -- ----------------------------------------------------------------------------
1654 Procedure chk_latest_offer
1655 (p_latest_offer in irc_offers.latest_offer%TYPE
1656 ,p_offer_id in irc_offers.offer_id%TYPE
1657 ,p_offer_status in irc_offers.offer_status%TYPE
1658 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1659 ,p_effective_date in date
1660 ,p_object_version_number in irc_offers.object_version_number%TYPE
1661 ) IS
1662 --
1663 l_proc varchar2(72) := g_package || 'chk_latest_offer';
1664 l_latest_offer number;
1665 l_api_updating boolean;
1666 --
1667 cursor csr_latest_offer_upd is
1668 select 1
1669 from irc_offers
1670 where latest_offer = 'Y'
1671 and applicant_assignment_id = p_applicant_assignment_id
1672 and offer_id <> p_offer_id;
1673 --
1674 cursor csr_latest_offer_ins is
1675 select 1
1676 from irc_offers
1677 where latest_offer = 'Y'
1678 and applicant_assignment_id = p_applicant_assignment_id;
1679 --
1680 Begin
1681 hr_utility.set_location('Entering:'||l_proc,10);
1682 --
1683 if hr_multi_message.no_exclusive_error(
1684 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1685 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1686 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1687 ) then
1688 --
1689 -- Only proceed with validation if :
1690 -- a) The current g_old_rec is current and
1691 -- b) The value for latest_offer has changed
1692 --
1693 l_api_updating := irc_iof_shd.api_updating
1694 (p_offer_id => p_offer_id
1695 ,p_object_version_number => p_object_version_number);
1696 --
1697 hr_utility.set_location(l_proc,20);
1698 --
1699 -- If a newly created offer is in SFL status, there
1700 -- should not be a validation as the offer_status for this
1701 -- offer will always be 'N'.
1702 --
1703 if (NOT l_api_updating)
1704 then
1705 if ( p_offer_status <> 'SAVED')
1706 then
1707 --
1708 open csr_latest_offer_ins;
1709 fetch csr_latest_offer_ins into l_latest_offer;
1710 --
1711 hr_utility.set_location(l_proc,30);
1712 if (csr_latest_offer_ins%found)
1713 then
1714 close csr_latest_offer_ins;
1715 fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1716 fnd_message.raise_error;
1717 end if;
1718 close csr_latest_offer_ins;
1719 --
1720 end if;
1721 --
1722 elsif (l_api_updating
1723 and nvl(irc_iof_shd.g_old_rec.latest_offer, hr_api.g_varchar2) <>
1724 nvl(p_latest_offer, hr_api.g_varchar2))
1725 then
1726 if ( p_offer_status <> 'SAVED')
1727 then
1728 --
1729 open csr_latest_offer_upd;
1730 fetch csr_latest_offer_upd into l_latest_offer;
1731 --
1732 hr_utility.set_location(l_proc,30);
1733 if (csr_latest_offer_upd%found)
1734 then
1735 close csr_latest_offer_upd;
1736 fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1737 fnd_message.raise_error;
1738 end if;
1739 close csr_latest_offer_upd;
1740 --
1741 end if;
1742 --
1743 end if;
1744 --
1745 if ((l_api_updating
1746 and nvl(irc_iof_shd.g_old_rec.latest_offer, hr_api.g_varchar2) <>
1747 nvl(p_latest_offer, hr_api.g_varchar2))
1748 or (NOT l_api_updating)) then
1749 --
1750 hr_utility.set_location(l_proc,40);
1751 --
1752 -- Checks that the value for latest_offer is
1753 -- valid and exists on YES_NO lookup
1754 --
1755 if hr_api.not_exists_in_hr_lookups
1756 (p_effective_date => p_effective_date
1757 ,p_lookup_type => 'YES_NO'
1758 ,p_lookup_code => p_latest_offer
1759 ) then
1760 --
1761 -- Error: Invalid latest offer value.
1762 --
1763 hr_utility.set_location(l_proc,50);
1764 --
1765 fnd_message.set_name(800, 'IRC_412307_INV_LATEST_OFR_VAL');
1766 fnd_message.raise_error;
1767 end if;
1768 --
1769 hr_utility.set_location(' Leaving:'||l_proc,60);
1770 end if;
1771 end if; -- no_exclusive_error
1772 --
1773 exception
1774 when app_exception.application_exception then
1775 if hr_multi_message.exception_add
1776 (p_associated_column1 => 'IRC_OFFERS.LATEST_OFFER'
1777 ) then
1778 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1779 raise;
1780 end if;
1781 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1782 End chk_latest_offer;
1783 --
1784 -- ----------------------------------------------------------------------------
1785 -- |--------------------< chk_offer_version_combination >---------------------|
1786 -- ----------------------------------------------------------------------------
1787 -- {Start Of Comments}
1788 --
1789 -- Description:
1790 -- This procedure is used to ensure that the offer version and applicant
1791 -- assignment comination is unique.
1792 --
1793 -- Pre Conditions:
1794 -- g_old_rec has been populated with details of the values currently in
1795 -- the database.
1796 --
1797 -- In Arguments:
1798 -- p_offer_id
1799 -- p_offer_version
1800 -- p_applicant_assignment_id
1801 -- p_object_version_number
1802 --
1803 -- Post Success:
1804 -- Processing continues if the combination is unique.
1805 --
1806 -- Post Failure:
1807 -- An application error is raised if the combination already exists.
1808 --
1809 -- {End Of Comments}
1810 -- ----------------------------------------------------------------------------
1811 Procedure chk_offer_version_combination
1812 (p_offer_id in irc_offers.offer_id%TYPE
1813 ,p_offer_version in irc_offers.offer_version%TYPE
1814 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1815 ,p_object_version_number in irc_offers.object_version_number%TYPE
1816 ) IS
1817 --
1818 l_proc varchar2(72) := g_package || 'chk_offer_version_combination';
1819 l_version number;
1820 l_api_updating boolean;
1821 --
1822 cursor csr_version is
1823 select 1
1824 from irc_offers
1825 where offer_version = p_offer_version
1826 and applicant_assignment_id = p_applicant_assignment_id;
1827 --
1828 Begin
1829 --
1830 hr_utility.set_location(' Entering:'||l_proc,10);
1831 --
1832 if hr_multi_message.no_exclusive_error(
1833 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1834 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1835 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1836 ) then
1837 --
1838 -- Only proceed with validation if :
1839 -- a) The current g_old_rec is current and
1840 -- b) The value for offer version has changed or applicant_assignment_id has changed
1841 --
1842 l_api_updating := irc_iof_shd.api_updating
1843 (p_offer_id => p_offer_id
1844 ,p_object_version_number => p_object_version_number);
1845 --
1846 if ((l_api_updating and
1847 ((nvl(irc_iof_shd.g_old_rec.offer_version, hr_api.g_number) <>
1848 nvl(p_offer_version, hr_api.g_number))
1849 or
1850 (nvl(irc_iof_shd.g_old_rec.applicant_assignment_id, hr_api.g_number) <>
1851 nvl(p_applicant_assignment_id, hr_api.g_number))
1852 ))
1853 or
1854 (NOT l_api_updating)) then
1855 --
1856 open csr_version;
1857 fetch csr_version into l_version;
1858 hr_utility.set_location(l_proc,20);
1859 if csr_version%found then
1860 close csr_version;
1861 fnd_message.set_name(800,'IRC_412308_INV_OFFER_VER_COMB');
1862 fnd_message.raise_error;
1863 end if;
1864 close csr_version;
1865 end if;
1866 end if; -- no_exclusive_error
1867 --
1868 exception
1869 when app_exception.application_exception then
1870 if hr_multi_message.exception_add
1871 (p_associated_column1 => 'IRC_OFFER.OFFER_VERSION'
1872 ,p_associated_column2 => 'IRC_OFFER.APPLICANT_ASSIGNMENT_ID'
1873 ) then
1874 hr_utility.set_location(' Leaving:'||l_proc,30);
1875 raise;
1876 end if;
1877 --
1878 hr_utility.set_location(' Leaving:'||l_proc,40);
1879 End chk_offer_version_combination;
1880 --
1881 -- ---------------------------------------------------------------------------
1882 -- |--------------------------< chk_offer_status >---------------------------|
1883 -- ---------------------------------------------------------------------------
1884 --
1885 -- Description:
1886 -- This procedure is used to ensure that Offer Status is a valid value
1887 -- from IRC_OFFER_STATUSES lookup
1888 --
1889 -- Pre-conditions:
1890 -- Effective_date must be valid.
1891 --
1892 -- In Arguments:
1893 -- p_offer_id
1894 -- p_offer_status
1895 -- p_effective_date
1896 -- p_object_version_number
1897 --
1898 -- Post Success:
1899 -- If the given offer status exists in IRC_OFFER_STATUSES Lookup,
1900 -- processing continues.
1901 --
1902 -- Post Failure:
1903 -- If the given offer status does not exist in IRC_OFFER_STATUSES Lookup,
1904 -- an application error will be raised and processing will be terminated.
1905 --
1906 -- {End Of Comments}
1907 -- ----------------------------------------------------------------------------
1908 procedure chk_offer_status
1909 (p_offer_id in irc_offers.offer_id%TYPE
1910 ,p_offer_status in irc_offers.offer_status%TYPE
1911 ,p_effective_date in date
1912 ,p_object_version_number in irc_offers.object_version_number%TYPE
1913 )IS
1914 --
1915 l_proc varchar2(72) := g_package||'chk_offer_status';
1916 l_api_updating boolean;
1917 --
1918 begin
1919 hr_utility.set_location('Entering:'|| l_proc, 10);
1920 --
1921 if hr_multi_message.no_exclusive_error(
1922 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1923 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1924 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1925 ) then
1926 --
1927 --
1928 -- Check mandatory parameters have been set
1929 --
1930 hr_api.mandatory_arg_error
1931 (p_api_name => l_proc
1932 ,p_argument => 'effective_date'
1933 ,p_argument_value => p_effective_date
1934 );
1935 --
1936 -- Only proceed with validation if :
1937 -- a) The current g_old_rec is current and
1938 -- b) The value of offer status has changed
1939 --
1940 l_api_updating := irc_iof_shd.api_updating
1941 (p_offer_id => p_offer_id
1942 ,p_object_version_number => p_object_version_number);
1943 --
1944 if ((l_api_updating
1945 and nvl(irc_iof_shd.g_old_rec.offer_status, hr_api.g_varchar2) <>
1946 nvl(p_offer_status, hr_api.g_varchar2))
1947 or
1948 (NOT l_api_updating)) then
1949 --
1950 hr_utility.set_location(l_proc, 20);
1951 --
1952 -- Checks that the value for offer_status is
1953 -- valid and exists on irc_offer_statuses within
1954 -- the specified date range
1955 --
1956 if hr_api.not_exists_in_hr_lookups
1957 (p_effective_date => p_effective_date
1958 ,p_lookup_type => 'IRC_OFFER_STATUSES'
1959 ,p_lookup_code => p_offer_status
1960 ) then
1961 --
1962 hr_utility.set_location(l_proc, 30);
1963 -- Error: Invalid offer status type.
1964 fnd_message.set_name('PER', 'IRC_412323_INV_OFFER_STATUS');
1965 fnd_message.raise_error;
1966 end if;
1967 end if;
1968 --
1969 -- While creation the offer cannot of any of the following statuses:
1970 -- 'CLOSED', 'EXTENDED', 'APPROVED', 'HOLD' or 'PENDING_EXTENDED'
1971 --
1972 if NOT l_api_updating
1973 then
1974 --
1975 hr_utility.set_location(l_proc, 40);
1976 if p_offer_status in ('CLOSED', 'EXTENDED', 'HOLD', 'CORRECTION', 'PENDING_EXTENDED')
1977 then
1978 --
1979 hr_utility.set_location(l_proc, 50);
1980 fnd_message.set_name('PER', 'IRC_412309_INV_CRT_OFR_STATUS');
1981 fnd_message.raise_error;
1982 --
1983 end if;
1984 end if;
1985 --
1986 --
1987 end if; -- no_exclusive_error
1988 --
1989 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1990 exception
1991 when app_exception.application_exception then
1992 if hr_multi_message.exception_add
1993 (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
1994 ) then
1995 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1996 raise;
1997 end if;
1998 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1999 end chk_offer_status;
2000 --
2001 --
2002 -- ---------------------------------------------------------------------------
2003 -- |-----------------------< chk_offer_status_update >-----------------------|
2004 -- ---------------------------------------------------------------------------
2005 --
2006 -- Description:
2007 -- This procedure ensures the following:
2008 -- 1) If the offer_status is 'CLOSED' do nothing - This is because we have already
2009 -- checked for offer_status 'CLOSED' in update_offer procedure of irc_offers_api.
2010 --
2011 -- 2) If the offer is in 'HOLD' state now, and the offer_status was previously 'HOLD'
2012 -- too, throw an error saying that an offer in HOLD status cannot be updated.
2013 --
2014 -- 3) If the offer was previously in 'HOLD' status, the current offer_status should
2015 -- be the status in which the offer was before it was Held.
2016 --
2017 -- Pre-conditions:
2018 -- none
2019 --
2020 -- In Arguments:
2021 -- p_current_offer_record
2022 --
2023 -- Post Success:
2024 -- If the above mentioned checks succeed, processing continues.
2025 --
2026 -- Post Failure:
2027 -- Incase any of the cases fail, appropriate errors will be thrown.
2028 --
2029 -- {End Of Comments}
2030 -- ----------------------------------------------------------------------------
2031 procedure chk_offer_status_update
2032 ( p_current_offer_record in irc_iof_shd.g_rec_type
2033 )IS
2034 --
2035 l_proc varchar2(72) := g_package||'chk_offer_status_update';
2036 l_prev_offer_status irc_offers.offer_status%TYPE := irc_iof_shd.g_old_rec.offer_status;
2037 l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
2038 l_mutiple_fields_updated boolean;
2039 --
2040 cursor csr_prev_to_prev_offer_status is
2041 select ios1.offer_status
2042 from irc_offer_status_history ios1
2043 where ios1.offer_id = p_current_offer_record.offer_id
2044 and EXISTS
2045 (SELECT 1
2046 FROM irc_offer_status_history iosh1
2047 WHERE iosh1.offer_id = ios1.offer_id
2048 AND iosh1.status_change_date > ios1.status_change_date
2049 )
2050 AND ios1.offer_status_history_id =
2051 (SELECT MAX(iosh2.offer_status_history_id)
2052 FROM irc_offer_status_history iosh2
2053 WHERE iosh2.offer_id = ios1.offer_id
2054 AND iosh2.status_change_date = ios1.status_change_date
2055 )
2056 AND 1 =
2057 (SELECT COUNT(*)
2058 FROM irc_offer_status_history ios3
2059 WHERE ios3.offer_id = ios1.offer_id
2060 AND ios3.status_change_date > ios1.status_change_date
2061 );
2062 --
2063 begin
2064 hr_utility.set_location('Entering:'|| l_proc, 10);
2065 --
2066 if p_current_offer_record.offer_status <> 'CLOSED'
2067 and p_current_offer_record.offer_status <> 'APPROVED'
2068 and p_current_offer_record.offer_status <> 'CORRECTION'
2069 and p_current_offer_record.offer_status <> 'EXTENDED'
2070 then
2071 --
2072 hr_utility.set_location(l_proc,20);
2073 --
2074 -- Check to see if the offer status is 'Hold'.
2075 --
2076 if ( p_current_offer_record.offer_status = 'HOLD'
2077 ) then
2078 --
2079 hr_utility.set_location(l_proc,30);
2080 --
2081 -- Check if the offer was previously in 'Hold'
2082 -- state too. If so, the offer has been updated. Hence,
2083 -- throw an error saying that the offer cannot be updated.
2084 --
2085 if ( l_prev_offer_status = 'HOLD'
2086 ) then
2087 --
2088 hr_utility.set_location(l_proc,40);
2089 --
2090 fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2091 fnd_message.raise_error;
2092 end if;
2093 --
2094 -- Also Check that when in HOLD, no other data can be
2095 -- changed in the offer record.
2096 --
2097 IRC_IOF_BUS.chk_multiple_fields_updated
2098 ( p_offer_id => p_current_offer_record.offer_id
2099 ,p_offer_status => p_current_offer_record.offer_status
2100 ,p_discretionary_job_title => p_current_offer_record.discretionary_job_title
2101 ,p_offer_extended_method => p_current_offer_record.offer_extended_method
2102 ,p_expiry_date => p_current_offer_record.expiry_date
2103 ,p_proposed_start_date => p_current_offer_record.proposed_start_date
2104 ,p_offer_letter_tracking_code => p_current_offer_record.offer_letter_tracking_code
2105 ,p_offer_postal_service => p_current_offer_record.offer_postal_service
2106 ,p_offer_shipping_date => p_current_offer_record.offer_shipping_date
2107 ,p_applicant_assignment_id => p_current_offer_record.applicant_assignment_id
2108 ,p_offer_assignment_id => p_current_offer_record.offer_assignment_id
2109 ,p_address_id => p_current_offer_record.address_id
2110 ,p_template_id => p_current_offer_record.template_id
2111 ,p_offer_letter_file_type => p_current_offer_record.offer_letter_file_type
2112 ,p_offer_letter_file_name => p_current_offer_record.offer_letter_file_name
2113 ,p_attribute_category => p_current_offer_record.attribute_category
2114 ,p_attribute1 => p_current_offer_record.attribute1
2115 ,p_attribute2 => p_current_offer_record.attribute2
2116 ,p_attribute3 => p_current_offer_record.attribute3
2117 ,p_attribute4 => p_current_offer_record.attribute4
2118 ,p_attribute5 => p_current_offer_record.attribute5
2119 ,p_attribute6 => p_current_offer_record.attribute6
2120 ,p_attribute7 => p_current_offer_record.attribute7
2121 ,p_attribute8 => p_current_offer_record.attribute8
2122 ,p_attribute9 => p_current_offer_record.attribute9
2123 ,p_attribute10 => p_current_offer_record.attribute10
2124 ,p_attribute11 => p_current_offer_record.attribute11
2125 ,p_attribute12 => p_current_offer_record.attribute12
2126 ,p_attribute13 => p_current_offer_record.attribute13
2127 ,p_attribute14 => p_current_offer_record.attribute14
2128 ,p_attribute15 => p_current_offer_record.attribute15
2129 ,p_attribute16 => p_current_offer_record.attribute16
2130 ,p_attribute17 => p_current_offer_record.attribute17
2131 ,p_attribute18 => p_current_offer_record.attribute18
2132 ,p_attribute19 => p_current_offer_record.attribute19
2133 ,p_attribute20 => p_current_offer_record.attribute20
2134 ,p_attribute21 => p_current_offer_record.attribute21
2135 ,p_attribute22 => p_current_offer_record.attribute22
2136 ,p_attribute23 => p_current_offer_record.attribute23
2137 ,p_attribute24 => p_current_offer_record.attribute24
2138 ,p_attribute25 => p_current_offer_record.attribute25
2139 ,p_attribute26 => p_current_offer_record.attribute26
2140 ,p_attribute27 => p_current_offer_record.attribute27
2141 ,p_attribute28 => p_current_offer_record.attribute28
2142 ,p_attribute29 => p_current_offer_record.attribute29
2143 ,p_attribute30 => p_current_offer_record.attribute30
2144 ,p_mutiple_fields_updated => l_mutiple_fields_updated
2145 );
2146 if ( l_mutiple_fields_updated = true )
2147 then
2148 --
2149 hr_utility.set_location(l_proc,45);
2150 --
2151 fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2152 fnd_message.raise_error;
2153 end if;
2154 --
2155 else
2156 --
2157 -- If the offer status is anything else.
2158 --
2159 hr_utility.set_location(l_proc,50);
2160 --
2161 -- Check if the offer was previously in 'Hold' State.
2162 -- If so, the current state should be the state which existed
2163 -- before the offer was Held.
2164 --
2165 if ( l_prev_offer_status = 'HOLD'
2166 ) then
2167 --
2168 hr_utility.set_location(l_proc,60);
2169 --
2170 open csr_prev_to_prev_offer_status;
2171 fetch csr_prev_to_prev_offer_status into l_prev_to_prev_offer_status;
2172 close csr_prev_to_prev_offer_status;
2173 --
2174 if ( p_current_offer_record.offer_status <> l_prev_to_prev_offer_status
2175 ) then
2176 --
2177 hr_utility.set_location(l_proc,70);
2178 --
2179 fnd_message.set_name('PER','IRC_412305_INV_PREVTOPREV_OFR');
2180 fnd_message.raise_error;
2181 --
2182 end if;
2183 end if;
2184 end if; -- if-else-end if
2185 end if; -- Offer_status = 'CLOSED'
2186 --
2187 hr_utility.set_location(' Leaving:'|| l_proc, 80);
2188 exception
2189 when app_exception.application_exception then
2190 if hr_multi_message.exception_add
2191 (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
2192 ) then
2193 hr_utility.set_location(' Leaving:'|| l_proc, 90);
2194 raise;
2195 end if;
2196 hr_utility.set_location(' Leaving:'|| l_proc, 100);
2197 end chk_offer_status_update;
2198 --
2199 -- ----------------------------------------------------------------------------
2200 -- |---------------------< chk_multiple_fields_updated >----------------------|
2201 -- ----------------------------------------------------------------------------
2202 -- {Start Of Comments}
2203 --
2204 -- Description:
2205 -- This procedure ensures that not more than one field has been updated in the
2206 -- offer record.
2207 --
2208 -- Pre Conditions:
2209 -- None
2210 --
2211 -- In Arguments:
2212 -- All the IRC_OFFERS table fields except object_version_number and respondent_id.
2213 --
2214 -- Post Success:
2215 -- If only one field has been updated, p_mutiple_fields_updated will be set to
2216 -- 'false'. If multiple fields have been updated, p_mutiple_fields_updated will be
2217 -- set to 'true'.
2218 --
2219 -- Post Failure:
2220 -- None
2221 --
2222 -- {End Of Comments}
2223 -- ----------------------------------------------------------------------------
2224 Procedure chk_multiple_fields_updated
2225 ( p_offer_id in number
2226 ,p_offer_status in varchar2 default null
2227 ,p_discretionary_job_title in varchar2 default null
2228 ,p_offer_extended_method in varchar2 default null
2229 ,p_expiry_date in date default null
2230 ,p_proposed_start_date in date default null
2231 ,p_offer_letter_tracking_code in varchar2 default null
2232 ,p_offer_postal_service in varchar2 default null
2233 ,p_offer_shipping_date in date default null
2234 ,p_applicant_assignment_id in number default null
2235 ,p_offer_assignment_id in number default null
2236 ,p_address_id in number default null
2237 ,p_template_id in number default null
2238 ,p_offer_letter_file_type in varchar2 default null
2239 ,p_offer_letter_file_name in varchar2 default null
2240 ,p_attribute_category in varchar2 default null
2241 ,p_attribute1 in varchar2 default null
2242 ,p_attribute2 in varchar2 default null
2243 ,p_attribute3 in varchar2 default null
2244 ,p_attribute4 in varchar2 default null
2245 ,p_attribute5 in varchar2 default null
2246 ,p_attribute6 in varchar2 default null
2247 ,p_attribute7 in varchar2 default null
2248 ,p_attribute8 in varchar2 default null
2249 ,p_attribute9 in varchar2 default null
2250 ,p_attribute10 in varchar2 default null
2251 ,p_attribute11 in varchar2 default null
2252 ,p_attribute12 in varchar2 default null
2253 ,p_attribute13 in varchar2 default null
2254 ,p_attribute14 in varchar2 default null
2255 ,p_attribute15 in varchar2 default null
2256 ,p_attribute16 in varchar2 default null
2257 ,p_attribute17 in varchar2 default null
2258 ,p_attribute18 in varchar2 default null
2259 ,p_attribute19 in varchar2 default null
2260 ,p_attribute20 in varchar2 default null
2261 ,p_attribute21 in varchar2 default null
2262 ,p_attribute22 in varchar2 default null
2263 ,p_attribute23 in varchar2 default null
2264 ,p_attribute24 in varchar2 default null
2265 ,p_attribute25 in varchar2 default null
2266 ,p_attribute26 in varchar2 default null
2267 ,p_attribute27 in varchar2 default null
2268 ,p_attribute28 in varchar2 default null
2269 ,p_attribute29 in varchar2 default null
2270 ,p_attribute30 in varchar2 default null
2271 ,p_mutiple_fields_updated out nocopy boolean
2272 ) IS
2273 --
2274 l_proc varchar2(72) := g_package || 'chk_multiple_fields_updated';
2275 l_update_count number(2) := 0;
2276 l_api_updating boolean;
2277 --
2278 Cursor C_Sel1 is
2279 select
2280 offer_id
2281 ,offer_version
2282 ,latest_offer
2283 ,offer_status
2284 ,discretionary_job_title
2285 ,offer_extended_method
2286 ,respondent_id
2287 ,expiry_date
2288 ,proposed_start_date
2289 ,offer_letter_tracking_code
2290 ,offer_postal_service
2291 ,offer_shipping_date
2292 ,applicant_assignment_id
2293 ,offer_assignment_id
2294 ,address_id
2295 ,template_id
2296 ,offer_letter_file_type
2297 ,offer_letter_file_name
2298 ,attribute_category
2299 ,attribute1
2300 ,attribute2
2301 ,attribute3
2302 ,attribute4
2303 ,attribute5
2304 ,attribute6
2305 ,attribute7
2306 ,attribute8
2307 ,attribute9
2308 ,attribute10
2309 ,attribute11
2310 ,attribute12
2311 ,attribute13
2312 ,attribute14
2313 ,attribute15
2314 ,attribute16
2315 ,attribute17
2316 ,attribute18
2317 ,attribute19
2318 ,attribute20
2319 ,attribute21
2320 ,attribute22
2321 ,attribute23
2322 ,attribute24
2323 ,attribute25
2324 ,attribute26
2325 ,attribute27
2326 ,attribute28
2327 ,attribute29
2328 ,attribute30
2329 ,object_version_number
2330 from irc_offers
2331 where offer_id = p_offer_id;
2332 --
2333 l_offer_old_rec C_Sel1%ROWTYPE;
2334 Begin
2335 --
2336 hr_utility.set_location('Entering:'||l_proc,10);
2337 --
2338 Open C_Sel1;
2339 Fetch C_Sel1 Into l_offer_old_rec;
2340 If C_Sel1%notfound Then
2341 Close C_Sel1;
2342 --
2343 -- The primary key is invalid therefore we must error
2344 --
2345 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2346 fnd_message.raise_error;
2347 End If;
2348 Close C_Sel1;
2349 --
2350 -- If any field has changed, increment l_update_count.
2351 --
2352 if( p_offer_id <> hr_api.g_number) then
2353 if
2354 nvl(l_offer_old_rec.offer_id, hr_api.g_number) <>
2355 nvl(p_offer_id, hr_api.g_number)
2356 then
2357 --
2358 hr_utility.set_location(l_proc,20);
2359 --
2360 l_update_count := l_update_count + 1;
2361 end if;
2362 end if;
2363
2364 if(p_offer_status <> hr_api.g_varchar2) then
2365 --
2366 hr_utility.set_location(l_proc,30);
2367 --
2368 if
2369 nvl(l_offer_old_rec.offer_status, hr_api.g_varchar2) <>
2370 nvl(p_offer_status, hr_api.g_varchar2)
2371 then
2372 --
2373 hr_utility.set_location(l_proc,50);
2374 --
2375 l_update_count := l_update_count + 1;
2376 end if;
2377 end if;
2378
2379 if(p_discretionary_job_title <> hr_api.g_varchar2)
2380 or p_discretionary_job_title is null
2381 then
2382 if
2383 nvl(l_offer_old_rec.discretionary_job_title, hr_api.g_varchar2) <>
2384 nvl(p_discretionary_job_title, hr_api.g_varchar2)
2385 then
2386 --
2387 hr_utility.set_location(l_proc,60);
2388 --
2389 l_update_count := l_update_count + 1;
2390 end if;
2391 end if;
2392
2393 if(p_offer_extended_method <> hr_api.g_varchar2)
2394 or p_offer_extended_method is null
2395 then
2396 if
2397 nvl(l_offer_old_rec.offer_extended_method, hr_api.g_varchar2) <>
2398 nvl(p_offer_extended_method, hr_api.g_varchar2)
2399 then
2400 --
2401 hr_utility.set_location(l_proc,70);
2402 --
2403 l_update_count := l_update_count + 1;
2404 end if;
2405 end if;
2406
2407 if(p_expiry_date <> hr_api.g_date)
2408 or p_expiry_date is null
2409 then
2410 if
2411 nvl(l_offer_old_rec.expiry_date, hr_api.g_date) <>
2412 nvl(p_expiry_date, hr_api.g_date)
2413 then
2414 --
2415 hr_utility.set_location(l_proc,90);
2416 --
2417 l_update_count := l_update_count + 1;
2418 end if;
2419 end if;
2420
2421 if(p_proposed_start_date <> hr_api.g_date)
2422 or p_proposed_start_date is null
2423 then
2424 if
2425 nvl(l_offer_old_rec.proposed_start_date, hr_api.g_date) <>
2426 nvl(p_proposed_start_date, hr_api.g_date)
2427 then
2428 --
2429 hr_utility.set_location(l_proc,100);
2430 --
2431 l_update_count := l_update_count + 1;
2432 end if;
2433 end if;
2434 --
2435 if(p_offer_letter_tracking_code <> hr_api.g_varchar2)
2436 or p_offer_letter_tracking_code is null
2437 then
2438 if
2439 nvl(l_offer_old_rec.offer_letter_tracking_code, hr_api.g_varchar2) <>
2440 nvl(p_offer_letter_tracking_code, hr_api.g_varchar2)
2441 then
2442 --
2443 hr_utility.set_location(l_proc,110);
2444 --
2445 l_update_count := l_update_count + 1;
2446 end if;
2447 end if;
2448
2449 if(p_offer_postal_service <> hr_api.g_varchar2)
2450 or p_offer_postal_service is null
2451 then
2452 if
2453 nvl(l_offer_old_rec.offer_postal_service, hr_api.g_varchar2) <>
2454 nvl(p_offer_postal_service, hr_api.g_varchar2)
2455 then
2456 --
2457 hr_utility.set_location(l_proc,120);
2458 --
2459 l_update_count := l_update_count + 1;
2460 end if;
2461 end if;
2462
2463 if(p_offer_shipping_date <> hr_api.g_date)
2464 or p_offer_shipping_date is null
2465 then
2466 if
2467 nvl(l_offer_old_rec.offer_shipping_date, hr_api.g_date) <>
2468 nvl(p_offer_shipping_date, hr_api.g_date)
2469 then
2470 --
2471 hr_utility.set_location(l_proc,130);
2472 --
2473 l_update_count := l_update_count + 1;
2474 end if;
2475 end if;
2476 --
2477
2478 if(p_applicant_assignment_id <> hr_api.g_number) then
2479 if
2480 nvl(l_offer_old_rec.applicant_assignment_id, hr_api.g_number) <>
2481 nvl(p_applicant_assignment_id, hr_api.g_number)
2482 then
2483 --
2484 hr_utility.set_location(l_proc,150);
2485 --
2486 l_update_count := l_update_count + 1;
2487 end if;
2488 end if;
2489
2490 if(p_offer_assignment_id <> hr_api.g_number) then
2491 if
2492 nvl(l_offer_old_rec.offer_assignment_id, hr_api.g_number) <>
2493 nvl(p_offer_assignment_id, hr_api.g_number)
2494 then
2495 --
2496 hr_utility.set_location(l_proc,160);
2497 --
2498 l_update_count := l_update_count + 1;
2499 end if;
2500 end if;
2501
2502 if(p_address_id <> hr_api.g_number)
2503 or p_address_id is null
2504 then
2505 if
2506 nvl(l_offer_old_rec.address_id, hr_api.g_number) <>
2507 nvl(p_address_id, hr_api.g_number)
2508 then
2509 --
2510 hr_utility.set_location(l_proc,170);
2511 --
2512 l_update_count := l_update_count + 1;
2513 end if;
2514 end if;
2515
2516 if(p_template_id <> hr_api.g_number)
2517 or p_template_id is null
2518 then
2519 if
2520 nvl(l_offer_old_rec.template_id, hr_api.g_number) <>
2521 nvl(p_template_id, hr_api.g_number)
2522 then
2523 --
2524 hr_utility.set_location(l_proc,180);
2525 --
2526 l_update_count := l_update_count + 1;
2527 end if;
2528 end if;
2529
2530 if(p_offer_letter_file_type <> hr_api.g_varchar2)
2531 or p_offer_letter_file_type is null
2532 then
2533 if
2534 nvl(l_offer_old_rec.offer_letter_file_type, hr_api.g_varchar2) <>
2535 nvl(p_offer_letter_file_type, hr_api.g_varchar2)
2536 then
2537 --
2538 hr_utility.set_location(l_proc,190);
2539 --
2540 l_update_count := l_update_count + 1;
2541 end if;
2542 end if;
2543
2544 if(p_offer_letter_file_name <> hr_api.g_varchar2)
2545 or p_offer_letter_file_name is null
2546 then
2547 if
2548 nvl(l_offer_old_rec.offer_letter_file_name, hr_api.g_varchar2) <>
2549 nvl(p_offer_letter_file_name, hr_api.g_varchar2)
2550 then
2551 --
2552 hr_utility.set_location(l_proc,200);
2553 --
2554 l_update_count := l_update_count + 1;
2555 end if;
2556 end if;
2557
2558 if(p_attribute_category <> hr_api.g_varchar2)
2559 or p_attribute_category is null
2560 then
2561 if
2562 nvl(l_offer_old_rec.attribute_category, hr_api.g_varchar2) <>
2563 nvl(p_attribute_category, hr_api.g_varchar2)
2564 then
2565 --
2566 hr_utility.set_location(l_proc,210);
2567 --
2568 l_update_count := l_update_count + 1;
2569 end if;
2570 end if;
2571
2572 if(p_attribute1 <> hr_api.g_varchar2)
2573 or p_attribute1 is null
2574 then
2575 if
2576 nvl(l_offer_old_rec.attribute1, hr_api.g_varchar2) <>
2577 nvl(p_attribute1, hr_api.g_varchar2)
2578 then
2579 --
2580 hr_utility.set_location(l_proc,220);
2581 --
2582 l_update_count := l_update_count + 1;
2583 end if;
2584 end if;
2585
2586 if(p_attribute2 <> hr_api.g_varchar2)
2587 or p_attribute2 is null
2588 then
2589 if
2590 nvl(l_offer_old_rec.attribute2, hr_api.g_varchar2) <>
2591 nvl(p_attribute2, hr_api.g_varchar2)
2592 then
2593 --
2594 hr_utility.set_location(l_proc,230);
2595 --
2596 l_update_count := l_update_count + 1;
2597 end if;
2598 end if;
2599
2600 if(p_attribute3 <> hr_api.g_varchar2)
2601 or p_attribute3 is null
2602 then
2603 if
2604 nvl(l_offer_old_rec.attribute3, hr_api.g_varchar2) <>
2605 nvl(p_attribute3, hr_api.g_varchar2)
2606 then
2607 --
2608 hr_utility.set_location(l_proc,240);
2609 --
2610 l_update_count := l_update_count + 1;
2611 end if;
2612 end if;
2613
2614 if(p_attribute4 <> hr_api.g_varchar2)
2615 or p_attribute4 is null
2616 then
2617 if
2618 nvl(l_offer_old_rec.attribute4, hr_api.g_varchar2) <>
2619 nvl(p_attribute4, hr_api.g_varchar2)
2620 then
2621 --
2622 hr_utility.set_location(l_proc,250);
2623 --
2624 l_update_count := l_update_count + 1;
2625 end if;
2626 end if;
2627
2628 if(p_attribute5 <> hr_api.g_varchar2)
2629 or p_attribute5 is null
2630 then
2631 if
2632 nvl(l_offer_old_rec.attribute5, hr_api.g_varchar2) <>
2633 nvl(p_attribute5, hr_api.g_varchar2)
2634 then
2635 --
2636 hr_utility.set_location(l_proc,260);
2637 --
2638 l_update_count := l_update_count + 1;
2639 end if;
2640 end if;
2641
2642 if(p_attribute6 <> hr_api.g_varchar2)
2643 or p_attribute6 is null
2644 then
2645 if
2646 nvl(l_offer_old_rec.attribute6, hr_api.g_varchar2) <>
2647 nvl(p_attribute6, hr_api.g_varchar2)
2648 then
2649 --
2650 hr_utility.set_location(l_proc,270);
2651 --
2652 l_update_count := l_update_count + 1;
2653 end if;
2654 end if;
2655
2656 if(p_attribute7 <> hr_api.g_varchar2)
2657 or p_attribute7 is null
2658 then
2659 if
2660 nvl(l_offer_old_rec.attribute7, hr_api.g_varchar2) <>
2661 nvl(p_attribute7, hr_api.g_varchar2)
2662 then
2663 --
2664 hr_utility.set_location(l_proc,280);
2665 --
2666 l_update_count := l_update_count + 1;
2667 end if;
2668 end if;
2669
2670 if(p_attribute8 <> hr_api.g_varchar2)
2671 or p_attribute8 is null
2672 then
2673 if
2674 nvl(l_offer_old_rec.attribute8, hr_api.g_varchar2) <>
2675 nvl(p_attribute8, hr_api.g_varchar2)
2676 then
2677 --
2678 hr_utility.set_location(l_proc,290);
2679 --
2680 l_update_count := l_update_count + 1;
2681 end if;
2682 end if;
2683
2684 if(p_attribute9 <> hr_api.g_varchar2)
2685 or p_attribute9 is null
2686 then
2687 if
2688 nvl(l_offer_old_rec.attribute9, hr_api.g_varchar2) <>
2689 nvl(p_attribute9, hr_api.g_varchar2)
2690 then
2691 --
2692 hr_utility.set_location(l_proc,300);
2693 --
2694 l_update_count := l_update_count + 1;
2695 end if;
2696 end if;
2697
2698 if(p_attribute10 <> hr_api.g_varchar2)
2699 or p_attribute10 is null
2700 then
2701 if
2702 nvl(l_offer_old_rec.attribute10, hr_api.g_varchar2) <>
2703 nvl(p_attribute10, hr_api.g_varchar2)
2704 then
2705 --
2706 hr_utility.set_location(l_proc,310);
2707 --
2708 l_update_count := l_update_count + 1;
2709 end if;
2710 end if;
2711
2712 if(p_attribute11 <> hr_api.g_varchar2)
2713 or p_attribute11 is null
2714 then
2715 if
2716 nvl(l_offer_old_rec.attribute11, hr_api.g_varchar2) <>
2717 nvl(p_attribute11, hr_api.g_varchar2)
2718 then
2719 --
2720 hr_utility.set_location(l_proc,320);
2721 --
2722 l_update_count := l_update_count + 1;
2723 end if;
2724 end if;
2725
2726 if(p_attribute12 <> hr_api.g_varchar2)
2727 or p_attribute12 is null
2728 then
2729 if
2730 nvl(l_offer_old_rec.attribute12, hr_api.g_varchar2) <>
2731 nvl(p_attribute12, hr_api.g_varchar2)
2732 then
2733 --
2734 hr_utility.set_location(l_proc,330);
2735 --
2736 l_update_count := l_update_count + 1;
2737 end if;
2738 end if;
2739
2740 if(p_attribute13 <> hr_api.g_varchar2)
2741 or p_attribute13 is null
2742 then
2743 if
2744 nvl(l_offer_old_rec.attribute13, hr_api.g_varchar2) <>
2745 nvl(p_attribute13, hr_api.g_varchar2)
2746 then
2747 --
2748 hr_utility.set_location(l_proc,340);
2749 --
2750 l_update_count := l_update_count + 1;
2751 end if;
2752 end if;
2753
2754 if(p_attribute14 <> hr_api.g_varchar2)
2755 or p_attribute14 is null
2756 then
2757 if
2758 nvl(l_offer_old_rec.attribute14, hr_api.g_varchar2) <>
2759 nvl(p_attribute14, hr_api.g_varchar2)
2760 then
2761 --
2762 hr_utility.set_location(l_proc,350);
2763 --
2764 l_update_count := l_update_count + 1;
2765 end if;
2766 end if;
2767
2768 if(p_attribute15 <> hr_api.g_varchar2)
2769 or p_attribute15 is null
2770 then
2771 if
2772 nvl(l_offer_old_rec.attribute15, hr_api.g_varchar2) <>
2773 nvl(p_attribute15, hr_api.g_varchar2)
2774 then
2775 --
2776 hr_utility.set_location(l_proc,360);
2777 --
2778 l_update_count := l_update_count + 1;
2779 end if;
2780 end if;
2781
2782 if(p_attribute16 <> hr_api.g_varchar2)
2783 or p_attribute16 is null
2784 then
2785 if
2786 nvl(l_offer_old_rec.attribute16, hr_api.g_varchar2) <>
2787 nvl(p_attribute16, hr_api.g_varchar2)
2788 then
2789 --
2790 hr_utility.set_location(l_proc,370);
2791 --
2792 l_update_count := l_update_count + 1;
2793 end if;
2794 end if;
2795
2796 if(p_attribute17 <> hr_api.g_varchar2)
2797 or p_attribute17 is null
2798 then
2799 if
2800 nvl(l_offer_old_rec.attribute17, hr_api.g_varchar2) <>
2801 nvl(p_attribute17, hr_api.g_varchar2)
2802 then
2803 --
2804 hr_utility.set_location(l_proc,380);
2805 --
2806 l_update_count := l_update_count + 1;
2807 end if;
2808 end if;
2809
2810 if(p_attribute18 <> hr_api.g_varchar2)
2811 or p_attribute18 is null
2812 then
2813 if
2814 nvl(l_offer_old_rec.attribute18, hr_api.g_varchar2) <>
2815 nvl(p_attribute18, hr_api.g_varchar2)
2816 then
2817 --
2818 hr_utility.set_location(l_proc,390);
2819 --
2820 l_update_count := l_update_count + 1;
2821 end if;
2822 end if;
2823
2824 if(p_attribute19 <> hr_api.g_varchar2)
2825 or p_attribute19 is null
2826 then
2827 if
2828 nvl(l_offer_old_rec.attribute19, hr_api.g_varchar2) <>
2829 nvl(p_attribute19, hr_api.g_varchar2)
2830 then
2831 --
2832 hr_utility.set_location(l_proc,400);
2833 --
2834 l_update_count := l_update_count + 1;
2835 end if;
2836 end if;
2837
2838 if(p_attribute20 <> hr_api.g_varchar2)
2839 or p_attribute20 is null
2840 then
2841 if
2842 nvl(l_offer_old_rec.attribute20, hr_api.g_varchar2) <>
2843 nvl(p_attribute20, hr_api.g_varchar2)
2844 then
2845 --
2846 hr_utility.set_location(l_proc,410);
2847 --
2848 l_update_count := l_update_count + 1;
2849 end if;
2850 end if;
2851
2852 if(p_attribute21 <> hr_api.g_varchar2)
2853 or p_attribute21 is null
2854 then
2855 if
2856 nvl(l_offer_old_rec.attribute21, hr_api.g_varchar2) <>
2857 nvl(p_attribute21, hr_api.g_varchar2)
2858 then
2859 --
2860 hr_utility.set_location(l_proc,420);
2861 --
2862 l_update_count := l_update_count + 1;
2863 end if;
2864 end if;
2865
2866 if(p_attribute22 <> hr_api.g_varchar2)
2867 or p_attribute22 is null
2868 then
2869 if
2870 nvl(l_offer_old_rec.attribute22, hr_api.g_varchar2) <>
2871 nvl(p_attribute22, hr_api.g_varchar2)
2872 then
2873 --
2874 hr_utility.set_location(l_proc,430);
2875 --
2876 l_update_count := l_update_count + 1;
2877 end if;
2878 end if;
2879
2880 if(p_attribute23 <> hr_api.g_varchar2)
2881 or p_attribute23 is null
2882 then
2883 if
2884 nvl(l_offer_old_rec.attribute23, hr_api.g_varchar2) <>
2885 nvl(p_attribute23, hr_api.g_varchar2)
2886 then
2887 --
2888 hr_utility.set_location(l_proc,440);
2889 --
2890 l_update_count := l_update_count + 1;
2891 end if;
2892 end if;
2893
2894 if(p_attribute24 <> hr_api.g_varchar2)
2895 or p_attribute24 is null
2896 then
2897 if
2898 nvl(l_offer_old_rec.attribute24, hr_api.g_varchar2) <>
2899 nvl(p_attribute24, hr_api.g_varchar2)
2900 then
2901 --
2902 hr_utility.set_location(l_proc,450);
2903 --
2904 l_update_count := l_update_count + 1;
2905 end if;
2906 end if;
2907
2908 if(p_attribute25 <> hr_api.g_varchar2)
2909 or p_attribute25 is null
2910 then
2911 if
2912 nvl(l_offer_old_rec.attribute25, hr_api.g_varchar2) <>
2913 nvl(p_attribute25, hr_api.g_varchar2)
2914 then
2915 --
2916 hr_utility.set_location(l_proc,460);
2917 --
2918 l_update_count := l_update_count + 1;
2919 end if;
2920 end if;
2921
2922 if(p_attribute26 <> hr_api.g_varchar2)
2923 or p_attribute26 is null
2924 then
2925 if
2926 nvl(l_offer_old_rec.attribute26, hr_api.g_varchar2) <>
2927 nvl(p_attribute26, hr_api.g_varchar2)
2928 then
2929 --
2930 hr_utility.set_location(l_proc,470);
2931 --
2932 l_update_count := l_update_count + 1;
2933 end if;
2934 end if;
2935
2936 if(p_attribute27 <> hr_api.g_varchar2)
2937 or p_attribute27 is null
2938 then
2939 if
2940 nvl(l_offer_old_rec.attribute27, hr_api.g_varchar2) <>
2941 nvl(p_attribute27, hr_api.g_varchar2)
2942 then
2943 --
2944 hr_utility.set_location(l_proc,480);
2945 --
2946 l_update_count := l_update_count + 1;
2947 end if;
2948 end if;
2949
2950 if(p_attribute28 <> hr_api.g_varchar2)
2951 or p_attribute28 is null
2952 then
2953 if
2954 nvl(l_offer_old_rec.attribute28, hr_api.g_varchar2) <>
2955 nvl(p_attribute28, hr_api.g_varchar2)
2956 then
2957 --
2958 hr_utility.set_location(l_proc,490);
2959 --
2960 l_update_count := l_update_count + 1;
2961 end if;
2962 end if;
2963
2964 if(p_attribute29 <> hr_api.g_varchar2)
2965 or p_attribute29 is null
2966 then
2967 if
2968 nvl(l_offer_old_rec.attribute29, hr_api.g_varchar2) <>
2969 nvl(p_attribute29, hr_api.g_varchar2)
2970 then
2971 --
2972 hr_utility.set_location(l_proc,500);
2973 --
2974 l_update_count := l_update_count + 1;
2975 end if;
2976 end if;
2977
2978 if(p_attribute30 <> hr_api.g_varchar2)
2979 or p_attribute30 is null
2980 then
2981 if
2982 nvl(l_offer_old_rec.attribute30, hr_api.g_varchar2) <>
2983 nvl(p_attribute30, hr_api.g_varchar2)
2984 then
2985 --
2986 hr_utility.set_location(l_proc,510);
2987 --
2988 l_update_count := l_update_count + 1;
2989 end if;
2990 end if;
2991 --
2992 -- Check if l_update_count > 1, if Yes, set p_mutiple_fields_updated
2993 -- to True.
2994 --
2995 if l_update_count > 1
2996 then
2997 --
2998 hr_utility.set_location(l_proc,520);
2999 --
3000 p_mutiple_fields_updated := true;
3001 else
3002 --
3003 hr_utility.set_location(l_proc,530);
3004 --
3005 p_mutiple_fields_updated := false;
3006 end if;
3007 hr_utility.set_location(' Leaving:'||l_proc,540);
3008 --
3009 exception
3010 when others then
3011 hr_utility.set_location(' Leaving:'||l_proc,550);
3012 raise;
3013 End chk_multiple_fields_updated;
3014 --
3015 --
3016 -- ---------------------------------------------------------------------------
3017 -- |---------------------< chk_offer_extended_method >-----------------------|
3018 -- ---------------------------------------------------------------------------
3019 --
3020 -- Description:
3021 -- This procedure is used to ensure that offer extended method is a valid
3022 -- value from IRC_OFFER_EXTENDED_METHOD lookup
3023 --
3024 -- Pre-conditions:
3025 -- Effective_date must be valid.
3026 --
3027 -- In Arguments:
3028 -- p_offer_id
3029 -- p_offer_extended_method
3030 -- p_effective_date
3031 -- p_object_version_number
3032 --
3033 -- Post Success:
3034 -- If the given offer extended method exists in IRC_OFFER_EXTENDED_METHOD
3035 -- Lookup, processing continues.
3036 --
3037 -- Post Failure:
3038 -- If the offer extended method does not exist in IRC_OFFER_EXTENDED_METHOD
3039 -- Lookup, an application error will be raised and processing will be terminated.
3040 --
3041 -- {End Of Comments}
3042 -- ----------------------------------------------------------------------------
3043 procedure chk_offer_extended_method
3044 (p_offer_id in irc_offers.offer_id%TYPE
3045 ,p_offer_extended_method in irc_offers.offer_extended_method%TYPE
3046 ,p_effective_date in date
3047 ,p_object_version_number in irc_offers.object_version_number%TYPE
3048 )IS
3049 --
3050 l_proc varchar2(72) := g_package||'chk_offer_extended_method';
3051 l_api_updating boolean;
3052 --
3053 begin
3054 hr_utility.set_location('Entering:'|| l_proc, 10);
3055 --
3056 if hr_multi_message.no_exclusive_error(
3057 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3058 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3059 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3060 ) then
3061 --
3062 -- Check mandatory parameters have been set
3063 --
3064 hr_api.mandatory_arg_error
3065 (p_api_name => l_proc
3066 ,p_argument => 'effective_date'
3067 ,p_argument_value => p_effective_date
3068 );
3069 --
3070 -- Only proceed with validation if :
3071 -- a) The current g_old_rec is current and
3072 -- b) The value for offer extended method has changed
3073 --
3074 l_api_updating := irc_iof_shd.api_updating
3075 (p_offer_id => p_offer_id
3076 ,p_object_version_number => p_object_version_number);
3077 --
3078 if ((l_api_updating
3079 and nvl(irc_iof_shd.g_old_rec.offer_extended_method, hr_api.g_varchar2) <>
3080 nvl(p_offer_extended_method, hr_api.g_varchar2))
3081 or
3082 (NOT l_api_updating)) then
3083 --
3084 -- Check if offer_extended_method is not null.
3085 --
3086 if p_offer_extended_method is not null then
3087 --
3088 hr_utility.set_location(l_proc, 20);
3089 --
3090 -- Checks that the value for offer_extended_method is
3091 -- valid and exists on irc_offer_extended_method lookup
3092 -- within the specified date range
3093 --
3094 if hr_api.not_exists_in_hr_lookups
3095 (p_effective_date => p_effective_date
3096 ,p_lookup_type => 'IRC_OFFER_EXTENDED_METHOD'
3097 ,p_lookup_code => p_offer_extended_method
3098 ) then
3099 --
3100 -- Error: Invalid offer extended method.
3101 fnd_message.set_name(800, 'IRC_412310_INV_OFR_EXTNDD_MTHD');
3102 fnd_message.raise_error;
3103 end if;
3104 end if;
3105 end if;
3106 end if; -- no_exclusive_error
3107 --
3108 hr_utility.set_location(' Leaving:'|| l_proc, 30);
3109 exception
3110 when app_exception.application_exception then
3111 if hr_multi_message.exception_add
3112 (p_associated_column1 => 'IRC_OFFERS.OFFER_EXTENDED_METHOD'
3113 ) then
3114 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3115 raise;
3116 end if;
3117 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3118 end chk_offer_extended_method;
3119 --
3120 -- ---------------------------------------------------------------------------
3121 -- |---------------------< chk_offer_postal_service >------------------------|
3122 -- ---------------------------------------------------------------------------
3123 --
3124 -- Description:
3125 -- This procedure is used to ensure that offer postal service is a valid
3126 -- value from IRC_OFFER_POSTAL_SERVICE lookup
3127 --
3128 -- Pre-conditions:
3129 -- Effective_date must be valid.
3130 --
3131 -- In Arguments:
3132 -- p_offer_id
3133 -- p_offer_postal_service
3134 -- p_effective_date
3135 -- p_object_version_number
3136 --
3137 -- Post Success:
3138 -- If the given offer postal service exists in IRC_OFFER_POSTAL_SERVICE
3139 -- Lookup, processing continues.
3140 --
3141 -- Post Failure:
3142 -- If the offer extended method does not exist in IRC_OFFER_POSTAL_SERVICE
3143 -- Lookup, an application error will be raised and processing will be terminated.
3144 --
3145 -- {End Of Comments}
3146 -- ----------------------------------------------------------------------------
3147 procedure chk_offer_postal_service
3148 (p_offer_id in irc_offers.offer_id%TYPE
3149 ,p_offer_postal_service in irc_offers.offer_postal_service%TYPE
3150 ,p_effective_date in date
3151 ,p_object_version_number in irc_offers.object_version_number%TYPE
3152 )IS
3153 --
3154 l_proc varchar2(72) := g_package||'chk_offer_postal_service';
3155 l_api_updating boolean;
3156 --
3157 begin
3158 hr_utility.set_location('Entering:'|| l_proc, 10);
3159 --
3160 if hr_multi_message.no_exclusive_error(
3161 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3162 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3163 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3164 ) then
3165 --
3166 -- Check mandatory parameters have been set
3167 --
3168 hr_api.mandatory_arg_error
3169 (p_api_name => l_proc
3170 ,p_argument => 'effective_date'
3171 ,p_argument_value => p_effective_date
3172 );
3173 --
3174 -- Only proceed with validation if :
3175 -- a) The current g_old_rec is current and
3176 -- b) The value for offer postal service has changed
3177 --
3178 l_api_updating := irc_iof_shd.api_updating
3179 (p_offer_id => p_offer_id
3180 ,p_object_version_number => p_object_version_number);
3181 --
3182 if ((l_api_updating
3183 and nvl(irc_iof_shd.g_old_rec.offer_postal_service, hr_api.g_varchar2) <>
3184 nvl(p_offer_postal_service, hr_api.g_varchar2))
3185 or
3186 (NOT l_api_updating)) then
3187 --
3188 -- Check if offer_postal_service is not null.
3189 --
3190 if p_offer_postal_service is not null then
3191 --
3192 hr_utility.set_location(l_proc, 20);
3193 --
3194 -- Checks that the value for offer_postal_service is
3195 -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3196 -- within the specified date range
3197 --
3198 if hr_api.not_exists_in_hr_lookups
3199 (p_effective_date => p_effective_date
3200 ,p_lookup_type => 'IRC_OFFER_POSTAL_SERVICE'
3201 ,p_lookup_code => p_offer_postal_service
3202 ) then
3203 --
3204 -- Error: Invalid offer extended method.
3205 fnd_message.set_name(800, 'IRC_412311_INV_OFR_POSTAL_SERV');
3206 fnd_message.raise_error;
3207 end if;
3208 end if;
3209 end if;
3210 end if; -- no_exclusive_error
3211 --
3212 hr_utility.set_location(' Leaving:'|| l_proc, 30);
3213 exception
3214 when app_exception.application_exception then
3215 if hr_multi_message.exception_add
3216 (p_associated_column1 => 'IRC_OFFERS.OFFER_POSTAL_SERVICE'
3217 ) then
3218 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3219 raise;
3220 end if;
3221 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3222 end chk_offer_postal_service;
3223 --
3224 -- ---------------------------------------------------------------------------
3225 -- |--------------------------< chk_offer_letter >---------------------------|
3226 -- ---------------------------------------------------------------------------
3227 --
3228 -- Description:
3229 -- This procedure checks if, when the offer is moved to APPROVED status,
3230 -- an offer letter has been uploaded.
3231 --
3232 -- Pre-conditions:
3233 -- The offer status should be changed to APPROVED.
3234 --
3235 -- In Arguments:
3236 -- p_offer_id
3237 -- p_offer_status
3238 --
3239 -- Post Success:
3240 -- If the given offer letter file type exists in XDO_OUTPUT_TYPE Lookup,
3241 -- processing continues.
3242 --
3243 -- Post Failure:
3244 -- If the offer letter file type does not exist in XDO_OUTPUT_TYPE Lookup,
3245 -- an application error will be raised and processing will be terminated.
3246 --
3247 -- {End Of Comments}
3248 -- ----------------------------------------------------------------------------
3249 procedure chk_offer_letter
3250 (p_offer_id in irc_offers.offer_id%TYPE
3251 ,p_offer_status in irc_offers.offer_status%TYPE
3252 )IS
3253 --
3254 l_proc varchar2(72) := g_package||'chk_offer_letter';
3255 l_offer_letter irc_offers.offer_letter%TYPE;
3256 --
3257 cursor csr_offer_letter is
3258 select offer_letter
3259 from irc_offers
3260 where offer_id = p_offer_id;
3261 --
3262 begin
3263 hr_utility.set_location('Entering:'|| l_proc, 10);
3264 --
3265 if hr_multi_message.no_exclusive_error(
3266 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3267 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3268 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3269 ) then
3270 --
3271 if p_offer_status = 'APPROVED'
3272 and nvl(irc_iof_shd.g_old_rec.offer_status, hr_api.g_varchar2) <>
3273 nvl(p_offer_status, hr_api.g_varchar2)
3274 then
3275 --
3276 hr_utility.set_location(l_proc, 20);
3277 --
3278 open csr_offer_letter;
3279 fetch csr_offer_letter into l_offer_letter;
3280 if csr_offer_letter%notfound
3281 then
3282 --
3283 hr_utility.set_location(l_proc, 30);
3284 --
3285 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
3286 fnd_message.raise_error;
3287 --
3288 end if;
3289 --
3290 -- Check if the offer letter is present. If blob length is 0, it means that
3291 -- the blob is not uploaded.
3292 --
3293 if dbms_lob.getlength(l_offer_letter) = 0
3294 then
3295 --
3296 hr_utility.set_location(l_proc, 40);
3297 --
3298 fnd_message.set_name('PER','IRC_412312_UPLOAD_OFFER_LETTER');
3299 hr_multi_message.add
3300 (p_message_type => hr_multi_message.g_warning_msg
3301 );
3302 --
3303 end if;
3304 --
3305 end if;
3306 end if; -- no_exclusive_error
3307 --
3308 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3309 exception
3310 when app_exception.application_exception then
3311 if hr_multi_message.exception_add
3312 (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER'
3313 ) then
3314 hr_utility.set_location(' Leaving:'|| l_proc, 60);
3315 raise;
3316 end if;
3317 hr_utility.set_location(' Leaving:'|| l_proc, 70);
3318 end chk_offer_letter;
3319 --
3320 --
3321 -- ---------------------------------------------------------------------------
3322 -- |--------------------< chk_offer_letter_file_type >-----------------------|
3323 -- ---------------------------------------------------------------------------
3324 --
3325 -- Description:
3326 -- This procedure is used to ensure that offer letter file type is a valid
3327 -- value from XDO_OUTPUT_TYPE lookup
3328 --
3329 -- Pre-conditions:
3330 -- Effective_date must be valid.
3331 --
3332 -- In Arguments:
3333 -- p_offer_id
3334 -- p_offer_letter_file_type
3335 -- p_effective_date
3336 -- p_object_version_number
3337 --
3338 -- Post Success:
3339 -- If the given offer letter file type exists in XDO_OUTPUT_TYPE Lookup,
3340 -- processing continues.
3341 --
3342 -- Post Failure:
3343 -- If the offer letter file type does not exist in XDO_OUTPUT_TYPE Lookup,
3344 -- an application error will be raised and processing will be terminated.
3345 --
3346 -- {End Of Comments}
3347 -- ----------------------------------------------------------------------------
3348 procedure chk_offer_letter_file_type
3349 (p_offer_id in irc_offers.offer_id%TYPE
3350 ,p_offer_letter_file_type in irc_offers.offer_letter_file_type%TYPE
3351 ,p_effective_date in date
3352 ,p_object_version_number in irc_offers.object_version_number%TYPE
3353 )IS
3354 --
3355 l_proc varchar2(72) := g_package||'chk_offer_letter_file_type';
3356 l_api_updating boolean;
3357 --
3358 begin
3359 hr_utility.set_location('Entering:'|| l_proc, 10);
3360 --
3361 if hr_multi_message.no_exclusive_error(
3362 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3363 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3364 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3365 ) then
3366 --
3367 -- Check mandatory parameters have been set
3368 --
3369 hr_api.mandatory_arg_error
3370 (p_api_name => l_proc
3371 ,p_argument => 'effective_date'
3372 ,p_argument_value => p_effective_date
3373 );
3374 --
3375 -- Only proceed with validation if :
3376 -- a) The current g_old_rec is current and
3377 -- b) The value for offer postal service has changed
3378 --
3379 l_api_updating := irc_iof_shd.api_updating
3380 (p_offer_id => p_offer_id
3381 ,p_object_version_number => p_object_version_number);
3382 --
3383 if ((l_api_updating
3384 and nvl(irc_iof_shd.g_old_rec.offer_letter_file_type, hr_api.g_varchar2) <>
3385 nvl(p_offer_letter_file_type, hr_api.g_varchar2))
3386 or
3387 (NOT l_api_updating)) then
3388 --
3389 -- Check if offer_postal_service is not null.
3390 --
3391 if p_offer_letter_file_type is not null then
3392 --
3393 hr_utility.set_location(l_proc, 20);
3394 --
3395 -- Checks that the value for offer_postal_service is
3396 -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3397 -- within the specified date range
3398 --
3399 if hr_api.not_exists_in_fnd_lookups
3400 (p_effective_date => p_effective_date
3401 ,p_lookup_type => 'XDO_OUTPUT_TYPE'
3402 ,p_lookup_code => p_offer_letter_file_type
3403 ) then
3404
3405 fnd_message.set_name(800, 'IRC_412312_UPLOAD_OFFER_LETTER');
3406 fnd_message.raise_error;
3407 end if;
3408 end if;
3409 end if;
3410 end if; -- no_exclusive_error
3411 --
3412 hr_utility.set_location(' Leaving:'|| l_proc, 30);
3413 exception
3414 when app_exception.application_exception then
3415 if hr_multi_message.exception_add
3416 (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER_FILE_TYPE'
3417 ) then
3418 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3419 raise;
3420 end if;
3421 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3422 end chk_offer_letter_file_type;
3423 --
3424 -- ----------------------------------------------------------------------------
3425 -- |---------------------------< insert_validate >----------------------------|
3426 -- ----------------------------------------------------------------------------
3427 --
3428 Procedure insert_validate
3429 (p_effective_date in date
3430 ,p_rec in out nocopy irc_iof_shd.g_rec_type
3431 ) is
3432 --
3433 l_proc varchar2(72) := g_package||'insert_validate';
3434 --
3435 Begin
3436 hr_utility.set_location('Entering:'||l_proc, 10);
3437 --
3438 -- Call all supporting business operations
3439 --
3440 hr_utility.set_location(l_proc, 20);
3441 --
3442 chk_applicant_assignment_id
3443 (p_effective_date => p_effective_date
3444 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3445 );
3446 --
3447 hr_utility.set_location(l_proc, 30);
3448 --
3449 chk_offer_assignment_id
3450 (p_offer_assignment_id => p_rec.offer_assignment_id
3451 );
3452 --
3453 hr_utility.set_location(l_proc, 40);
3454 --
3455 if p_rec.vacancy_id is null then
3456 set_vacancy_id
3457 (p_vacancy_id => p_rec.vacancy_id
3458 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3459 ,p_effective_date => p_effective_date
3460 );
3461 end if;
3462
3463 --
3464 hr_utility.set_location(l_proc, 50);
3465 --
3466 chk_offers_exceeds_openings
3467 (p_vacancy_id => p_rec.vacancy_id
3468 ,p_offer_status => p_rec.offer_status
3469 ,p_offer_id => p_rec.offer_id
3470 );
3471 --
3472 hr_utility.set_location(l_proc, 60);
3473 --
3474 chk_respondent_id
3475 (p_respondent_id => p_rec.respondent_id
3476 ,p_offer_id => p_rec.offer_id
3477 ,p_object_version_number => p_rec.object_version_number
3478 );
3479 --
3480 hr_utility.set_location(l_proc, 70);
3481 --
3482 set_address_id
3483 (p_address_id => p_rec.address_id
3484 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3485 ,p_effective_date => p_effective_date
3486 );
3487 --
3488 hr_utility.set_location(l_proc, 75);
3489 --
3490 chk_address_id
3491 (p_address_id => p_rec.address_id
3492 ,p_offer_id => p_rec.offer_id
3493 ,p_object_version_number => p_rec.object_version_number
3494 );
3495 --
3496 hr_utility.set_location(l_proc, 80);
3497 --
3498 chk_template_id
3499 (p_template_id => p_rec.template_id
3500 ,p_offer_id => p_rec.offer_id
3501 ,p_object_version_number => p_rec.object_version_number
3502 ,p_effective_date => p_effective_date
3503 );
3504 --
3505 hr_utility.set_location(l_proc, 90);
3506 --
3507 gen_offer_version
3508 (p_offer_version => p_rec.offer_version
3509 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3510 );
3511 --
3512 hr_utility.set_location(l_proc, 100);
3513 --
3514 chk_latest_offer
3515 (p_latest_offer => p_rec.latest_offer
3516 ,p_offer_id => p_rec.offer_id
3517 ,p_offer_status => p_rec.offer_status
3518 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3519 ,p_effective_date => p_effective_date
3520 ,p_object_version_number => p_rec.object_version_number
3521 );
3522 --
3523 hr_utility.set_location(l_proc, 110);
3524 --
3525 chk_offer_version_combination
3526 (p_offer_id => p_rec.offer_id
3527 ,p_offer_version => p_rec.offer_version
3528 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3529 ,p_object_version_number => p_rec.object_version_number
3530 );
3531 --
3532 hr_utility.set_location(l_proc, 120);
3533 --
3534 chk_offer_status
3535 (p_offer_id => p_rec.offer_id
3536 ,p_offer_status => p_rec.offer_status
3537 ,p_effective_date => p_effective_date
3538 ,p_object_version_number => p_rec.object_version_number
3539 );
3540 --
3541 hr_utility.set_location(l_proc, 130);
3542 --
3543 chk_offer_extended_method
3544 (p_offer_id => p_rec.offer_id
3545 ,p_offer_extended_method => p_rec.offer_extended_method
3546 ,p_effective_date => p_effective_date
3547 ,p_object_version_number => p_rec.object_version_number
3548 );
3549 --
3550 hr_utility.set_location(l_proc, 140);
3551 --
3552 chk_offer_postal_service
3553 (p_offer_id => p_rec.offer_id
3554 ,p_offer_postal_service => p_rec.offer_postal_service
3555 ,p_effective_date => p_effective_date
3556 ,p_object_version_number => p_rec.object_version_number
3557 );
3558 --
3559 hr_utility.set_location(l_proc, 150);
3560 --
3561 chk_offer_letter_file_type
3562 (p_offer_id => p_rec.offer_id
3563 ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3564 ,p_effective_date => p_effective_date
3565 ,p_object_version_number => p_rec.object_version_number
3566 );
3567 --
3568 hr_utility.set_location(l_proc, 170);
3569 --
3570 irc_iof_bus.chk_df(p_rec);
3571 --
3572 hr_utility.set_location(l_proc, 180);
3573 --
3574 hr_utility.set_location(' Leaving:'||l_proc, 180);
3575 End insert_validate;
3576 --
3577 -- ----------------------------------------------------------------------------
3578 -- |---------------------------< update_validate >----------------------------|
3579 -- ----------------------------------------------------------------------------
3580 Procedure update_validate
3581 (p_effective_date in date
3582 ,p_rec in out nocopy irc_iof_shd.g_rec_type
3583 ) is
3584 --
3585 l_proc varchar2(72) := g_package||'update_validate';
3586 --
3587 Begin
3588 hr_utility.set_location('Entering:'||l_proc, 10);
3589 --
3590 -- Call all supporting business operations
3591 --
3592 hr_utility.set_location(l_proc, 20);
3593 --
3594 chk_non_updateable_args
3595 (p_effective_date => p_effective_date
3596 ,p_rec => p_rec
3597 );
3598 --
3599 hr_utility.set_location(l_proc, 30);
3600 --
3601 chk_offers_exceeds_openings
3602 (p_vacancy_id => p_rec.vacancy_id
3603 ,p_offer_status => p_rec.offer_status
3604 ,p_offer_id => p_rec.offer_id
3605 );
3606 --
3607 hr_utility.set_location(l_proc, 35);
3608 --
3609 chk_respondent_id
3610 (p_respondent_id => p_rec.respondent_id
3611 ,p_offer_id => p_rec.offer_id
3612 ,p_object_version_number => p_rec.object_version_number
3613 );
3614 --
3615 hr_utility.set_location(l_proc, 40);
3616 --
3617 chk_expiry_date
3618 (p_expiry_date => p_rec.expiry_date
3619 ,p_offer_status => p_rec.offer_status
3620 ,p_offer_id => p_rec.offer_id
3621 ,p_offer_postal_service => p_rec.offer_postal_service
3622 ,p_offer_letter_tracking_code => p_rec.offer_letter_tracking_code
3623 ,p_offer_shipping_date => p_rec.offer_shipping_date
3624 ,p_effective_date => p_effective_date
3625 );
3626 --
3627 hr_utility.set_location(l_proc, 45);
3628 --
3629 chk_address_id
3630 (p_address_id => p_rec.address_id
3631 ,p_offer_id => p_rec.offer_id
3632 ,p_object_version_number => p_rec.object_version_number
3633 );
3634 --
3635 hr_utility.set_location(l_proc, 50);
3636 --
3637 chk_template_id
3638 (p_template_id => p_rec.template_id
3639 ,p_offer_id => p_rec.offer_id
3640 ,p_object_version_number => p_rec.object_version_number
3641 ,p_effective_date => p_effective_date
3642 );
3643 --
3644 hr_utility.set_location(l_proc, 60);
3645 --
3646 chk_latest_offer
3647 (p_latest_offer => p_rec.latest_offer
3648 ,p_offer_id => p_rec.offer_id
3649 ,p_offer_status => p_rec.offer_status
3650 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3651 ,p_effective_date => p_effective_date
3652 ,p_object_version_number => p_rec.object_version_number
3653 );
3654 --
3655 hr_utility.set_location(l_proc, 70);
3656 --
3657 chk_offer_status
3658 (p_offer_id => p_rec.offer_id
3659 ,p_offer_status => p_rec.offer_status
3660 ,p_effective_date => p_effective_date
3661 ,p_object_version_number => p_rec.object_version_number
3662 );
3663 --
3664 hr_utility.set_location(l_proc, 80);
3665 --
3666 chk_offer_status_update
3667 (p_current_offer_record => p_rec
3668 );
3669 --
3670 hr_utility.set_location(l_proc, 90);
3671 --
3672 chk_offer_extended_method
3673 (p_offer_id => p_rec.offer_id
3674 ,p_offer_extended_method => p_rec.offer_extended_method
3675 ,p_effective_date => p_effective_date
3676 ,p_object_version_number => p_rec.object_version_number
3677 );
3678 --
3679 hr_utility.set_location(l_proc, 100);
3680 --
3681 chk_offer_postal_service
3682 (p_offer_id => p_rec.offer_id
3683 ,p_offer_postal_service => p_rec.offer_postal_service
3684 ,p_effective_date => p_effective_date
3685 ,p_object_version_number => p_rec.object_version_number
3686 );
3687 --
3688 hr_utility.set_location(l_proc, 110);
3689 --
3690 chk_offer_letter
3691 (p_offer_id => p_rec.offer_id
3692 ,p_offer_status => p_rec.offer_status
3693 );
3694 --
3695 hr_utility.set_location(l_proc, 120);
3696 --
3697 chk_offer_letter_file_type
3698 (p_offer_id => p_rec.offer_id
3699 ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3700 ,p_effective_date => p_effective_date
3701 ,p_object_version_number => p_rec.object_version_number
3702 );
3703 --
3704 hr_utility.set_location(l_proc, 130);
3705 --
3706 irc_iof_bus.chk_df(p_rec);
3707 --
3708 hr_utility.set_location(' Leaving:'||l_proc, 140);
3709 End update_validate;
3710 --
3711 -- ----------------------------------------------------------------------------
3712 -- |---------------------------< delete_validate >----------------------------|
3713 -- ----------------------------------------------------------------------------
3714 Procedure delete_validate
3715 (p_rec in irc_iof_shd.g_rec_type
3716 ) is
3717 --
3718 l_proc varchar2(72) := g_package||'delete_validate';
3719 --
3720 Begin
3721 hr_utility.set_location('Entering:'||l_proc, 5);
3722 --
3723 -- Call all supporting business operations
3724 --
3725 hr_utility.set_location(' Leaving:'||l_proc, 10);
3726 End delete_validate;
3727 --
3728 end irc_iof_bus;