[Home] [Help]
PACKAGE BODY: APPS.IRC_IOF_BUS
Source
1 Package Body irc_iof_bus as
2 /* $Header: iriofrhi.pkb 120.13 2006/04/17 05:06 narvenka noship $ */
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(
657 --
654 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
655 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
656 ) then
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 1 = (select count(*)
744 from irc_offer_status_history ios2
745 where ios2.offer_id = p_offer_id
746 and ios2.offer_status_history_id > ios1.offer_status_history_id);
747 --
748 cursor csr_prev_offer_status is
749 select offer_status
750 from irc_offers
751 where offer_id = p_offer_id;
752 --
753 cursor csr_offer_count is
754 select count(*)
755 from irc_offers iof
756 ,per_all_vacancies pav
757 ,irc_offer_status_history iosh
758 where pav.vacancy_id = p_vacancy_id
759 and iof.vacancy_id = pav.vacancy_id
760 and iosh.offer_status_history_id = (select max(offer_status_history_id) from irc_offer_status_history where offer_id = iof.offer_id)
761 and iof.latest_offer = 'Y'
762 and ( iof.offer_status = 'EXTENDED' or ( iof.offer_status = 'CLOSED' and iosh.change_reason = 'APL_ACCEPTED'));
763 --
764 l_vacancy_opening_count csr_vacancy_opening_count%ROWTYPE;
765 --
766 Begin
767 hr_utility.set_location('Entering:'||l_proc,10);
768 --
769 if p_offer_status = 'CLOSED'
770 then
771 --
772 open csr_prev_offer_status;
773 fetch csr_prev_offer_status into l_prev_offer_status;
774 if csr_prev_offer_status%notfound
775 then
776 --
777 close csr_prev_offer_status;
778 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
779 fnd_message.raise_error;
780 --
781 end if;
782 close csr_prev_offer_status;
783 --
784 if l_prev_offer_status = 'HOLD'
785 then
786 --
787 -- We now know that a closed offer has been taken off hold.
788 -- Check if the offer was closed because it was accepted.
789 --
790 open csr_prev_to_prev_offer_chg_rsn;
791 fetch csr_prev_to_prev_offer_chg_rsn into l_prev_to_prev_offer_status
792 ,l_prev_to_prev_change_reason;
793 if csr_prev_to_prev_offer_chg_rsn%notfound
794 then
795 --
796 close csr_prev_to_prev_offer_chg_rsn;
797 fnd_message.set_name('PER','IRC_412305_INV_PREVTOPREV_OFR');
798 fnd_message.raise_error;
799 --
800 end if;
804 AND l_prev_to_prev_change_reason = 'APL_ACCEPTED'
801 close csr_prev_to_prev_offer_chg_rsn;
802 --
803 if ( l_prev_to_prev_offer_status = 'CLOSED' -- just a double check
805 )
806 then
807 --
808 -- We now know that an Accepted offer, on hold has been taken off hold.
809 -- Hence, check for vacancy count
810 --
811 l_chk_vacancy_count := true;
812 --
813 end if;
814 --
815 end if;
816 --
817 elsif p_offer_status = 'EXTENDED'
818 then
819 --
820 l_chk_vacancy_count := true;
821 --
822 end if;
823 --
824 if l_chk_vacancy_count = true
825 then
826 --
827 if hr_multi_message.no_exclusive_error(
828 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
829 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
830 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
831 )
832 then
833 --
834 hr_api.mandatory_arg_error
835 (p_api_name => l_proc
836 ,p_argument => 'VACANCY_ID'
837 ,p_argument_value => p_vacancy_id
838 );
839 --
840 open csr_vacancy_opening_count;
841 fetch csr_vacancy_opening_count into l_vacancy_opening_count;
842 close csr_vacancy_opening_count;
843 --
844 hr_utility.set_location(l_proc,20);
845 --
846 if (l_vacancy_opening_count.budget_measurement_type <> 'FTE')
847 then
848 --
849 open csr_offer_count;
850 fetch csr_offer_count into l_offer_count;
851 close csr_offer_count;
852 --
853 if (l_offer_count >= l_vacancy_opening_count.budget_measurement_value)
854 then
855 fnd_message.set_name('PER','IRC_412331_OFR_EXCDD_VAC_COUNT');
856 fnd_message.raise_error;
857 end if;
858 --
859 end if;
860 --
861 end if; -- no_exclusive_error
862 --
863 end if; -- if l_chk_vacancy_count check
864 hr_utility.set_location(' Leaving:'||l_proc,30);
865 exception
866 when app_exception.application_exception then
867 if hr_multi_message.exception_add
868 (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
869 ) then
870 hr_utility.set_location(' Leaving:'|| l_proc, 40);
871 raise;
872 end if;
873 hr_utility.set_location(' Leaving:'|| l_proc, 50);
874 End chk_offers_exceeds_openings;
875 --
876 -- ----------------------------------------------------------------------------
877 -- |--------------------------< chk_respondent_id >---------------------------|
878 -- ----------------------------------------------------------------------------
879 -- {Start Of Comments}
880 --
881 -- Description:
882 -- This procedure ensures that the respondent is an existing user.
883 --
884 -- Pre Conditions:
885 -- None
886 --
887 -- In Arguments:
888 -- p_respondent_id
889 -- p_offer_id
890 -- p_object_version_number
891 --
892 -- Post Success:
893 -- Processing continues if the respondent exists
894 --
895 -- Post Failure:
896 -- An application error is raised if Respondent is not an existing user
897 --
898 -- {End Of Comments}
899 -- ----------------------------------------------------------------------------
900 Procedure chk_respondent_id
901 (p_respondent_id in irc_offers.respondent_id%TYPE
902 ,p_offer_id in irc_offers.offer_id%TYPE
903 ,p_object_version_number in irc_offers.object_version_number%TYPE
904 ) IS
905 --
906 l_proc varchar2(72) := g_package || 'chk_respondent_id';
907 l_api_updating boolean;
908 l_respondent_id number;
909 --
910 cursor csr_respondent_id is
911 select 1
912 from fnd_user
913 where user_id = p_respondent_id;
914 --
915 Begin
916 hr_utility.set_location('Entering:'||l_proc,10);
917 --
918 if hr_multi_message.no_exclusive_error(
919 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
920 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
921 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
922 ) then
923 --
924 -- Only proceed with validation if :
925 -- a) The current g_old_rec is current and
926 -- b) The value for respondant_id has changed
927 --
928 l_api_updating := irc_iof_shd.api_updating
929 (p_offer_id => p_offer_id
930 ,p_object_version_number => p_object_version_number);
931 --
932 hr_utility.set_location(l_proc,20);
933
934 if ((l_api_updating
935 and nvl(irc_iof_shd.g_old_rec.respondent_id, hr_api.g_number) <>
936 nvl(p_respondent_id, hr_api.g_number))
937 or
938 (NOT l_api_updating)) then
939 --
940 -- Check if respondent id is not null.
941 --
942 if p_respondent_id is not null then
943
944 hr_utility.set_location(l_proc, 30);
945
946 open csr_respondent_id;
947 fetch csr_respondent_id into l_respondent_id;
948 --
949 hr_utility.set_location(l_proc,40);
950 if (csr_respondent_id%notfound)
951 then
952 close csr_respondent_id;
953 fnd_message.set_name('FND','FND_GRANTS_GNT_USER_INVALID');
954 fnd_message.raise_error;
955 end if;
956 close csr_respondent_id;
957 --
958 hr_utility.set_location(' Leaving:'||l_proc,50);
959 --
960 end if;
961 end if;
962 end if; -- no_exclusive_error
963 --
967 (p_associated_column1 => 'IRC_OFFERS.RESPONDENT_ID'
964 exception
965 when app_exception.application_exception then
966 if hr_multi_message.exception_add
968 ) then
969 hr_utility.set_location(' Leaving:'|| l_proc, 60);
970 raise;
971 end if;
972 hr_utility.set_location(' Leaving:'|| l_proc, 70);
973 End chk_respondent_id;
974 --
975 -- ----------------------------------------------------------------------------
976 -- |---------------------------< chk_expiry_date >----------------------------|
977 -- ----------------------------------------------------------------------------
978 -- {Start Of Comments}
979 --
980 -- Description:
981 -- This procedure ensures that while EXTENDING the offer, if the expirty date
982 -- is NULL, it is set to the calculated value from the profiles
983 -- IRC_OFFER_DURATION_MEASUREMENT and IRC_OFFER_DURATION_VALUE.
984 --
985 -- Pre Conditions:
986 -- None
987 --
988 -- In Arguments:
989 -- p_expiry_date
990 -- p_offer_status
991 -- p_effective_date
992 --
993 -- Out Arguments:
994 -- p_expiry_date
995 --
996 -- Post Success:
997 -- The expiry date is set to a calculated value if null.
998 --
999 --
1000 -- {End Of Comments}
1001 -- ----------------------------------------------------------------------------
1002 Procedure chk_expiry_date
1003 (p_expiry_date in out nocopy irc_offers.expiry_date%TYPE
1004 ,p_offer_status in irc_offers.offer_status%TYPE
1005 ,p_offer_id in irc_offers.offer_id%TYPE
1006 ,p_offer_postal_service in irc_offers.offer_postal_service%TYPE
1007 ,p_offer_letter_tracking_code in irc_offers.offer_letter_tracking_code%TYPE
1008 ,p_offer_shipping_date in irc_offers.offer_shipping_date%TYPE
1009 ,p_effective_date date
1010 ) IS
1011 --
1012 l_proc varchar2(72) := g_package || 'chk_expiry_date';
1013 l_offer_duration_value varchar2(30);
1014 l_offer_duration_measurement varchar2(30);
1015 l_expiry_date irc_offers.expiry_date%TYPE := p_expiry_date;
1016 l_prev_expiry_date irc_offers.expiry_date%TYPE := irc_iof_shd.g_old_rec.expiry_date;
1017 l_prev_offer_postal_service irc_offers.offer_postal_service%TYPE := irc_iof_shd.g_old_rec.offer_postal_service;
1018 l_prev_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE := irc_iof_shd.g_old_rec.offer_letter_tracking_code;
1019 l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE := irc_iof_shd.g_old_rec.offer_shipping_date;
1020 l_effective_date date;
1021 --
1022 Begin
1023 hr_utility.set_location('Entering:'||l_proc,10);
1024 --
1025 if hr_multi_message.no_exclusive_error(
1026 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1027 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1028 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1029 ) then
1030 --
1031 if p_offer_status = 'EXTENDED'
1032 then
1033 --
1034 hr_utility.set_location(l_proc, 20);
1035 --
1036 if p_expiry_date is not null
1037 then
1038 --
1039 -- Set the effective date
1040 --
1041 if p_expiry_date < p_effective_date
1042 then
1043 --
1044 fnd_message.set_name('PER','IRC_412352_INV_EXP_DATE');
1045 fnd_message.raise_error;
1046 --
1047 end if;
1048 --
1049 else -- p_expiry_date is null or is the previous expiry date
1050 --
1051 -- Check if the intend of this update is to indeed update the expiry date.
1052 --
1053 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
1054 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
1055 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
1056 )
1057 then
1058 --
1059 -- No value has been entered for the expiry date.
1060 -- Set the expiry date to the calculated value.
1061 --
1062 l_offer_duration_value := to_number(fnd_profile.value('IRC_OFFER_DURATION_VALUE'));
1063 l_offer_duration_measurement := fnd_profile.value('IRC_OFFER_DURATION_MEASUREMENT');
1064 --
1065 if l_offer_duration_value is not null
1066 then
1067 --
1068 hr_utility.set_location(l_proc, 30);
1069 --
1070 -- Set the effective date
1071 --
1072 if l_prev_expiry_date > p_effective_date
1073 then
1074 --
1075 l_effective_date := l_prev_expiry_date;
1076 --
1077 else
1078 --
1079 l_effective_date := p_effective_date;
1080 --
1081 end if;
1082 --
1083 if l_offer_duration_measurement = 'MONTH'
1084 then
1085 --
1086 hr_utility.set_location(l_proc, 60);
1087 --
1088 l_expiry_date := add_months(l_effective_date,l_offer_duration_value);
1089 --
1090 elsif l_offer_duration_measurement = 'WEEK'
1091 then
1092 --
1093 hr_utility.set_location(l_proc, 50);
1094 --
1095 l_expiry_date := l_effective_date + (l_offer_duration_value * 7);
1096 --
1097 else -- By default l_offer_duration_measurement = 'DAY'
1098 --
1099 hr_utility.set_location(l_proc, 40);
1100 --
1104 --
1101 l_expiry_date := l_effective_date + l_offer_duration_value;
1102 --
1103 end if;
1105 else -- l_offer_duration_value is null
1106 --
1107 -- Both, the entered value and the profile value are null. Throw an error
1108 --
1109 fnd_message.set_name('PER','IRC_412353_NULL_EXPIRY_DATE');
1110 fnd_message.raise_error;
1111 --
1112 end if;
1113 --
1114 end if;
1115 --
1116 end if;
1117 --
1118 end if; -- p_offer_status = 'EXTENDED'
1119 --
1120 end if; -- no_exclusive_error
1121 --
1122 -- Set the in out variable
1123 --
1124 p_expiry_date := l_expiry_date;
1125 --
1126 hr_utility.set_location(' Leaving:'||l_proc,70);
1127 --
1128 exception
1129 when app_exception.application_exception then
1130 if hr_multi_message.exception_add
1131 (p_associated_column1 => 'IRC_OFFERS.EXPIRY_DATE'
1132 ) then
1133 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1134 raise;
1135 end if;
1136 hr_utility.set_location(' Leaving:'|| l_proc, 90);
1137 End chk_expiry_date;
1138 --
1139 -- ----------------------------------------------------------------------------
1140 -- |----------------------------< set_address_id >----------------------------|
1141 -- ----------------------------------------------------------------------------
1142 -- {Start Of Comments}
1143 --
1144 -- Description:
1145 -- This procedure defaults the address to the Recruiting address Id if it is
1146 -- available for the primary person and if not address_id is passed in
1147 --
1148 -- Pre Conditions:
1149 -- None
1150 --
1151 -- In Arguments:
1152 -- p_address_id
1153 -- p_applicant_assignment_id
1154 -- p_effective_date
1155 --
1156 -- Out Arguments:
1157 -- p_address_id
1158 --
1159 -- Post Success:
1160 -- If a Recruiting address exists for the person, that address is set in the offer
1161 -- record
1162 --
1163 -- Post Failure:
1164 -- If the person does not have a recruiting address, the value remains null
1165 --
1166 -- {End Of Comments}
1167 -- ----------------------------------------------------------------------------
1168 Procedure set_address_id
1169 (p_address_id in out nocopy irc_offers.address_id%TYPE
1170 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1171 ,p_effective_date date
1172 ) IS
1173 --
1174 l_proc varchar2(72) := g_package || 'set_address_id';
1175 l_rec_address_id irc_offers.address_id%TYPE;
1176 --
1177 cursor csr_rec_address_id is
1178 select adr.address_id
1179 from per_addresses adr
1180 ,per_all_assignments_f asg
1181 where asg.assignment_id = p_applicant_assignment_id
1182 and adr.person_id = irc_utilities_pkg.get_recruitment_person_id(asg.person_id,trunc(sysdate))
1183 and adr.address_type = 'REC'
1184 and p_effective_date
1185 between adr.date_from
1186 and nvl(adr.date_to, trunc(sysdate));
1187 --
1188 Begin
1189 hr_utility.set_location('Entering:'||l_proc,10);
1190 --
1191 if hr_multi_message.no_exclusive_error(
1192 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1193 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1194 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1195 ) then
1196 --
1197 hr_utility.set_location(l_proc,20);
1198 --
1199 -- Default the address_id only if it is null
1200 --
1201 if p_address_id is null then
1202 --
1203 open csr_rec_address_id;
1204 fetch csr_rec_address_id into l_rec_address_id;
1205 --
1206 hr_utility.set_location(l_proc,30);
1207 if (csr_rec_address_id%found)
1208 then
1209 --
1210 -- The person has a recruiting address. Hence, default the address_id
1211 -- in offer record to this value.
1212 --
1213 p_address_id := l_rec_address_id;
1214 --
1215 end if;
1216 close csr_rec_address_id;
1217 --
1218 hr_utility.set_location(' Leaving:'||l_proc,40);
1219 end if;
1220 end if; -- no_exclusive_error
1221 --
1222 exception
1223 when app_exception.application_exception then
1224 if hr_multi_message.exception_add
1225 (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1226 ) then
1227 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1228 raise;
1229 end if;
1230 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1231 End set_address_id;
1232 --
1233 -- ----------------------------------------------------------------------------
1234 -- |----------------------------< chk_address_id >----------------------------|
1235 -- ----------------------------------------------------------------------------
1236 -- {Start Of Comments}
1237 --
1238 -- Description:
1239 -- This procedure ensures that the address is a valid address
1240 --
1241 -- Pre Conditions:
1242 -- None
1243 --
1244 -- In Arguments:
1245 -- p_address_id
1246 -- p_offer_id
1247 -- p_object_version_number
1248 --
1249 -- Post Success:
1250 -- Processing continues if the address exists
1251 --
1252 -- Post Failure:
1253 -- An application error is raised if address is not valid
1254 --
1255 -- {End Of Comments}
1256 -- ----------------------------------------------------------------------------
1257 Procedure chk_address_id
1258 (p_address_id in irc_offers.address_id%TYPE
1259 ,p_offer_id in irc_offers.offer_id%TYPE
1260 ,p_object_version_number in irc_offers.object_version_number%TYPE
1261 ) IS
1265 l_api_updating boolean;
1262 --
1263 l_proc varchar2(72) := g_package || 'chk_address_id';
1264 l_address_id number;
1266 --
1267 cursor csr_address_id is
1268 select 1
1269 from per_addresses
1270 where address_id = p_address_id;
1271 --
1272 Begin
1273 hr_utility.set_location('Entering:'||l_proc,10);
1274 --
1275 if hr_multi_message.no_exclusive_error(
1276 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1277 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1278 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1279 ) then
1280 --
1281 -- Only proceed with validation if :
1282 -- a) The current g_old_rec is current and
1283 -- b) The value for address_id has changed
1284 --
1285 l_api_updating := irc_iof_shd.api_updating
1286 (p_offer_id => p_offer_id
1287 ,p_object_version_number => p_object_version_number);
1288 --
1289 hr_utility.set_location(l_proc,20);
1290
1291 if ((l_api_updating
1292 and nvl(irc_iof_shd.g_old_rec.address_id, hr_api.g_number) <>
1293 nvl(p_address_id, hr_api.g_number))
1294 or
1295 (NOT l_api_updating)) then
1296 --
1297 -- Check if address id is not null.
1298 --
1299 if p_address_id is not null then
1300 --
1301 open csr_address_id;
1302 fetch csr_address_id into l_address_id;
1303 --
1304 hr_utility.set_location(l_proc,30);
1305 if (csr_address_id%notfound)
1306 then
1307 close csr_address_id;
1308 fnd_message.set_name('PER','IRC_412001_BAD_ADDRESS_ID');
1309 fnd_message.raise_error;
1310 end if;
1311 close csr_address_id;
1312 --
1313 hr_utility.set_location(' Leaving:'||l_proc,40);
1314 end if;
1315 end if;
1316 end if; -- no_exclusive_error
1317 --
1318 exception
1319 when app_exception.application_exception then
1320 if hr_multi_message.exception_add
1321 (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1322 ) then
1323 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1324 raise;
1325 end if;
1326 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1327 End chk_address_id;
1328 --
1329 -- ----------------------------------------------------------------------------
1330 -- |----------------------------< chk_template_id >---------------------------|
1331 -- ----------------------------------------------------------------------------
1332 -- {Start Of Comments}
1333 --
1334 -- Description:
1335 -- This procedure ensures that the offer template is valid
1336 --
1337 -- Pre Conditions:
1338 -- None
1339 --
1340 -- In Arguments:
1341 -- p_template_id
1342 -- p_offer_id
1343 -- p_object_version_number
1344 -- p_effective_date
1345 --
1346 -- Post Success:
1347 -- Processing continues if the template exists
1348 --
1349 -- Out Arguments:
1350 -- p_template_id
1351 --
1352 -- Post Failure:
1353 -- An application error is raised if template is not valid
1354 --
1355 -- {End Of Comments}
1356 -- ----------------------------------------------------------------------------
1357 Procedure chk_template_id
1358 (p_template_id in out nocopy irc_offers.template_id%TYPE
1359 ,p_offer_id in irc_offers.offer_id%TYPE
1360 ,p_object_version_number in irc_offers.object_version_number%TYPE
1361 ,p_effective_date date
1362 ) IS
1363 --
1364 l_proc varchar2(72) := g_package || 'chk_template_id';
1365 l_template_id irc_template_associations.template_id%TYPE;
1366 l_api_updating boolean;
1367 --
1368 cursor csr_template_id is
1369 select 1
1370 from xdo_templates_b
1371 where template_id = p_template_id
1372 and p_effective_date
1373 between start_date
1374 and nvl(end_date,p_effective_date);
1375 --
1376 cursor csr_default_template_job is
1377 select ita.template_id
1378 from irc_template_associations ita
1379 ,per_all_assignments_f ppaf
1380 ,irc_offers iof
1381 where ita.default_association = 'Y'
1382 and iof.offer_id = p_offer_id
1383 and iof.offer_assignment_id = ppaf.assignment_id
1384 and ita.job_id = ppaf.job_id;
1385 --
1386 cursor csr_default_template_pos is
1387 select ita.template_id
1388 from irc_template_associations ita
1389 ,per_all_assignments_f ppaf
1390 ,irc_offers iof
1391 where ita.default_association = 'Y'
1392 and iof.offer_id = p_offer_id
1393 and iof.offer_assignment_id = ppaf.assignment_id
1394 and ita.position_id = ppaf.position_id;
1395 --
1396 cursor csr_default_template_org is
1397 select ita.template_id
1398 from irc_template_associations ita
1399 ,per_all_assignments_f ppaf
1400 ,irc_offers iof
1401 where ita.default_association = 'Y'
1402 and iof.offer_id = p_offer_id
1403 and iof.offer_assignment_id = ppaf.assignment_id
1404 and ita.organization_id = ppaf.organization_id;
1405 --
1406 Begin
1407 hr_utility.set_location('Entering:'||l_proc,10);
1408 --
1409 if hr_multi_message.no_exclusive_error(
1410 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1411 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1412 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1413 ) then
1414 --
1415 -- Only proceed with validation if :
1416 -- a) The current g_old_rec is current and
1420 (p_offer_id => p_offer_id
1417 -- b) The value for template_id has changed
1418 --
1419 l_api_updating := irc_iof_shd.api_updating
1421 ,p_object_version_number => p_object_version_number);
1422 --
1423 hr_utility.set_location(l_proc,20);
1424
1425 if ((l_api_updating
1426 and nvl(irc_iof_shd.g_old_rec.template_id, hr_api.g_number) <>
1427 nvl(p_template_id, hr_api.g_number)) or
1428 (NOT l_api_updating)) then
1429 --
1430 -- Check if template id is not null and if it is active
1431 --
1432 if p_template_id is not null then
1433 --
1434 hr_utility.set_location(l_proc,30);
1435 --
1436 open csr_template_id;
1437 fetch csr_template_id into l_template_id;
1438 --
1439 if (csr_template_id%notfound)
1440 then
1441 --
1442 hr_utility.set_location(l_proc,40);
1443 --
1444 close csr_template_id;
1445 fnd_message.set_name('PER','IRC_412326_OFFER_INV_TEMPLT_ID');
1446 fnd_message.raise_error;
1447 end if;
1448 close csr_template_id;
1449 --
1450 else
1451 --
1452 hr_utility.set_location(l_proc,50);
1453 --
1454 open csr_default_template_job;
1455 fetch csr_default_template_job into l_template_id;
1456 --
1457 if (csr_default_template_job%notfound)
1458 then
1459 --
1460 hr_utility.set_location(l_proc,60);
1461 --
1462 close csr_default_template_job;
1463 --
1464 open csr_default_template_pos;
1465 fetch csr_default_template_pos into l_template_id;
1466 --
1467 if (csr_default_template_pos%notfound)
1468 then
1469 --
1470 hr_utility.set_location(l_proc,70);
1471 --
1472 close csr_default_template_pos;
1473 --
1474 open csr_default_template_org;
1475 fetch csr_default_template_org into l_template_id;
1476 --
1477 if (csr_default_template_org%notfound)
1478 then
1479 --
1480 hr_utility.set_location(l_proc,80);
1481 --
1482 close csr_default_template_org;
1483 l_template_id := p_template_id;
1484 --
1485 end if; --org
1486 end if; --pos
1487 end if; --job
1488 --
1489 end if; -- if - else - endif;
1490 end if; -- l_api_updating
1491 end if; -- no_exclusive_error
1492 hr_utility.set_location(' Leaving:'||l_proc,90);
1493 --
1494 exception
1495 when app_exception.application_exception then
1496 if hr_multi_message.exception_add
1497 (p_associated_column1 => 'IRC_OFFERS.TEMPLATE_ID'
1498 ) then
1499 hr_utility.set_location(' Leaving:'|| l_proc, 100);
1500 raise;
1501 end if;
1502 hr_utility.set_location(' Leaving:'|| l_proc, 110);
1503 End chk_template_id;
1504 --
1505 -- ----------------------------------------------------------------------------
1506 -- |--------------------------< gen_offer_version >---------------------------|
1507 -- ----------------------------------------------------------------------------
1508 -- {Start Of Comments}
1509 --
1510 -- Description:
1511 -- This procedure generates then offer_version number
1512 --
1513 -- Pre Conditions:
1514 -- None
1515 --
1516 -- In Arguments:
1517 -- p_offer_version
1518 -- p_applicant_assignment_id
1519 --
1520 -- Post Success:
1521 -- A new offer version number is generated.
1522 --
1523 -- {End Of Comments}
1524 -- ----------------------------------------------------------------------------
1525 Procedure gen_offer_version
1526 (p_offer_version out nocopy irc_offers.offer_version%TYPE
1527 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1528 ) IS
1529 --
1530 l_proc varchar2(72) := g_package || 'gen_offer_version';
1531 l_offer_version irc_offers.offer_version%TYPE;
1532 --
1533 cursor csr_get_offer_version is
1534 select nvl(max(offer_version),0) + 1
1535 from irc_offers
1536 where applicant_assignment_id = p_applicant_assignment_id;
1537 --
1538 Begin
1539 hr_utility.set_location('Entering:'||l_proc,10);
1540 --
1541 hr_api.mandatory_arg_error
1542 (p_api_name => l_proc
1543 ,p_argument => 'APPLICANT_ASSIGNMENT_ID'
1544 ,p_argument_value => p_applicant_assignment_id
1545 );
1546 --
1547 --
1548 -- Generate next offer version number
1549 --
1550 open csr_get_offer_version;
1551 fetch csr_get_offer_version into l_offer_version;
1552 close csr_get_offer_version;
1553 p_offer_version := l_offer_version;
1554 --
1555 hr_utility.set_location(' Leaving:'||l_proc,20);
1556 --
1557 exception
1558 when app_exception.application_exception then
1559 if hr_multi_message.exception_add
1560 (p_associated_column1 => 'IRC_OFFERS.OFFER_VERSION'
1561 ) then
1562 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1563 raise;
1564 end if;
1565 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1566 End gen_offer_version;
1567 --
1568 -- ----------------------------------------------------------------------------
1569 -- |--------------------------< chk_latest_offer >---------------------------|
1570 -- ----------------------------------------------------------------------------
1571 -- {Start Of Comments}
1572 --
1573 -- Description:
1574 -- The following checks ensure that only one offer is the latest offer for a particular
1578 -- If the offer is in SFL status:
1575 -- applicant assignment. This check works in the following manner:
1576 --
1577 -- 1) During Insert:
1579 -- The latest offer for this record will be 'N', hence not required to perform this
1580 -- validation.
1581 -- Else
1582 -- The offer being insterted should be the latest offer and there should be no
1583 -- other latest offers for this applicant assignment.
1584 --
1585 -- 2) During Update:
1586 -- If the offer is not in SFL status:
1587 -- The offer being updated should be the latest offer and there should be no
1588 -- other latest offers for this applicant assignment.
1589 --
1590 -- 3) The value entered should be validated against HR_LOOKUPS.LOOKUP_CODE
1591 -- where the LOOKUP_TYPE is 'YES_NO'. (I, U)
1592 -- Process: hr_api.not_exists_in_hr_lookups
1593 --
1594 -- Pre Conditions:
1595 -- None
1596 --
1597 -- In Arguments:
1598 -- p_latest_offer
1599 -- p_offer_id
1600 -- p_offer_status
1601 -- p_applicant_assignment_id
1602 -- p_effective_date
1603 -- p_object_version_number
1604 --
1605 -- Post Success:
1606 -- During insert:
1607 -- Processing continues if no other record for this application assignment id
1608 -- is the latest offer.
1609 -- During update:
1610 -- Processing continues if latest_offer exists
1611 --
1612 -- Post Failure:
1613 -- An application error is raised.
1614 --
1615 -- {End Of Comments}
1616 -- ----------------------------------------------------------------------------
1617 Procedure chk_latest_offer
1618 (p_latest_offer in irc_offers.latest_offer%TYPE
1619 ,p_offer_id in irc_offers.offer_id%TYPE
1620 ,p_offer_status in irc_offers.offer_status%TYPE
1621 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1622 ,p_effective_date in date
1623 ,p_object_version_number in irc_offers.object_version_number%TYPE
1624 ) IS
1625 --
1626 l_proc varchar2(72) := g_package || 'chk_latest_offer';
1627 l_latest_offer number;
1628 l_api_updating boolean;
1629 --
1630 cursor csr_latest_offer_upd is
1631 select 1
1632 from irc_offers
1633 where latest_offer = 'Y'
1634 and applicant_assignment_id = p_applicant_assignment_id
1635 and offer_id <> p_offer_id;
1636 --
1637 cursor csr_latest_offer_ins is
1638 select 1
1639 from irc_offers
1640 where latest_offer = 'Y'
1641 and applicant_assignment_id = p_applicant_assignment_id;
1642 --
1643 Begin
1644 hr_utility.set_location('Entering:'||l_proc,10);
1645 --
1646 if hr_multi_message.no_exclusive_error(
1647 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1648 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1649 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1650 ) then
1651 --
1652 -- Only proceed with validation if :
1653 -- a) The current g_old_rec is current and
1654 -- b) The value for latest_offer has changed
1655 --
1656 l_api_updating := irc_iof_shd.api_updating
1657 (p_offer_id => p_offer_id
1658 ,p_object_version_number => p_object_version_number);
1659 --
1660 hr_utility.set_location(l_proc,20);
1661 --
1662 -- If a newly created offer is in SFL status, there
1663 -- should not be a validation as the offer_status for this
1664 -- offer will always be 'N'.
1665 --
1666 if (NOT l_api_updating)
1667 then
1668 if ( p_offer_status <> 'SAVED')
1669 then
1670 --
1671 open csr_latest_offer_ins;
1672 fetch csr_latest_offer_ins into l_latest_offer;
1673 --
1674 hr_utility.set_location(l_proc,30);
1675 if (csr_latest_offer_ins%found)
1676 then
1677 close csr_latest_offer_ins;
1678 fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1679 fnd_message.raise_error;
1680 end if;
1681 close csr_latest_offer_ins;
1682 --
1683 end if;
1684 --
1685 elsif (l_api_updating
1686 and nvl(irc_iof_shd.g_old_rec.latest_offer, hr_api.g_varchar2) <>
1687 nvl(p_latest_offer, hr_api.g_varchar2))
1688 then
1689 if ( p_offer_status <> 'SAVED')
1690 then
1691 --
1692 open csr_latest_offer_upd;
1693 fetch csr_latest_offer_upd into l_latest_offer;
1694 --
1695 hr_utility.set_location(l_proc,30);
1696 if (csr_latest_offer_upd%found)
1697 then
1698 close csr_latest_offer_upd;
1699 fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1700 fnd_message.raise_error;
1701 end if;
1702 close csr_latest_offer_upd;
1703 --
1704 end if;
1705 --
1706 end if;
1707 --
1708 if ((l_api_updating
1709 and nvl(irc_iof_shd.g_old_rec.latest_offer, hr_api.g_varchar2) <>
1710 nvl(p_latest_offer, hr_api.g_varchar2))
1711 or (NOT l_api_updating)) then
1712 --
1713 hr_utility.set_location(l_proc,40);
1714 --
1715 -- Checks that the value for latest_offer is
1716 -- valid and exists on YES_NO lookup
1717 --
1718 if hr_api.not_exists_in_hr_lookups
1719 (p_effective_date => p_effective_date
1720 ,p_lookup_type => 'YES_NO'
1721 ,p_lookup_code => p_latest_offer
1722 ) then
1723 --
1724 -- Error: Invalid latest offer value.
1725 --
1726 hr_utility.set_location(l_proc,50);
1727 --
1728 fnd_message.set_name(800, 'IRC_412307_INV_LATEST_OFR_VAL');
1729 fnd_message.raise_error;
1733 end if;
1730 end if;
1731 --
1732 hr_utility.set_location(' Leaving:'||l_proc,60);
1734 end if; -- no_exclusive_error
1735 --
1736 exception
1737 when app_exception.application_exception then
1738 if hr_multi_message.exception_add
1739 (p_associated_column1 => 'IRC_OFFERS.LATEST_OFFER'
1740 ) then
1741 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1742 raise;
1743 end if;
1744 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1745 End chk_latest_offer;
1746 --
1747 -- ----------------------------------------------------------------------------
1748 -- |--------------------< chk_offer_version_combination >---------------------|
1749 -- ----------------------------------------------------------------------------
1750 -- {Start Of Comments}
1751 --
1752 -- Description:
1753 -- This procedure is used to ensure that the offer version and applicant
1754 -- assignment comination is unique.
1755 --
1756 -- Pre Conditions:
1757 -- g_old_rec has been populated with details of the values currently in
1758 -- the database.
1759 --
1760 -- In Arguments:
1761 -- p_offer_id
1762 -- p_offer_version
1763 -- p_applicant_assignment_id
1764 -- p_object_version_number
1765 --
1766 -- Post Success:
1767 -- Processing continues if the combination is unique.
1768 --
1769 -- Post Failure:
1770 -- An application error is raised if the combination already exists.
1771 --
1772 -- {End Of Comments}
1773 -- ----------------------------------------------------------------------------
1774 Procedure chk_offer_version_combination
1775 (p_offer_id in irc_offers.offer_id%TYPE
1776 ,p_offer_version in irc_offers.offer_version%TYPE
1777 ,p_applicant_assignment_id in irc_offers.applicant_assignment_id%TYPE
1778 ,p_object_version_number in irc_offers.object_version_number%TYPE
1779 ) IS
1780 --
1781 l_proc varchar2(72) := g_package || 'chk_offer_version_combination';
1782 l_version number;
1783 l_api_updating boolean;
1784 --
1785 cursor csr_version is
1786 select 1
1787 from irc_offers
1788 where offer_version = p_offer_version
1789 and applicant_assignment_id = p_applicant_assignment_id;
1790 --
1791 Begin
1792 --
1793 hr_utility.set_location(' Entering:'||l_proc,10);
1794 --
1795 if hr_multi_message.no_exclusive_error(
1796 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1797 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1798 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1799 ) then
1800 --
1801 -- Only proceed with validation if :
1802 -- a) The current g_old_rec is current and
1803 -- b) The value for offer version has changed or applicant_assignment_id has changed
1804 --
1805 l_api_updating := irc_iof_shd.api_updating
1806 (p_offer_id => p_offer_id
1807 ,p_object_version_number => p_object_version_number);
1808 --
1809 if ((l_api_updating and
1810 ((nvl(irc_iof_shd.g_old_rec.offer_version, hr_api.g_number) <>
1811 nvl(p_offer_version, hr_api.g_number))
1812 or
1813 (nvl(irc_iof_shd.g_old_rec.applicant_assignment_id, hr_api.g_number) <>
1814 nvl(p_applicant_assignment_id, hr_api.g_number))
1815 ))
1816 or
1817 (NOT l_api_updating)) then
1818 --
1819 open csr_version;
1820 fetch csr_version into l_version;
1821 hr_utility.set_location(l_proc,20);
1822 if csr_version%found then
1823 close csr_version;
1824 fnd_message.set_name(800,'IRC_412308_INV_OFFER_VER_COMB');
1825 fnd_message.raise_error;
1826 end if;
1827 close csr_version;
1828 end if;
1829 end if; -- no_exclusive_error
1830 --
1831 exception
1832 when app_exception.application_exception then
1833 if hr_multi_message.exception_add
1834 (p_associated_column1 => 'IRC_OFFER.OFFER_VERSION'
1835 ,p_associated_column2 => 'IRC_OFFER.APPLICANT_ASSIGNMENT_ID'
1836 ) then
1837 hr_utility.set_location(' Leaving:'||l_proc,30);
1838 raise;
1839 end if;
1840 --
1841 hr_utility.set_location(' Leaving:'||l_proc,40);
1842 End chk_offer_version_combination;
1843 --
1844 -- ---------------------------------------------------------------------------
1845 -- |--------------------------< chk_offer_status >---------------------------|
1846 -- ---------------------------------------------------------------------------
1847 --
1848 -- Description:
1849 -- This procedure is used to ensure that Offer Status is a valid value
1850 -- from IRC_OFFER_STATUSES lookup
1851 --
1852 -- Pre-conditions:
1853 -- Effective_date must be valid.
1854 --
1855 -- In Arguments:
1856 -- p_offer_id
1857 -- p_offer_status
1858 -- p_effective_date
1859 -- p_object_version_number
1860 --
1861 -- Post Success:
1862 -- If the given offer status exists in IRC_OFFER_STATUSES Lookup,
1863 -- processing continues.
1864 --
1865 -- Post Failure:
1866 -- If the given offer status does not exist in IRC_OFFER_STATUSES Lookup,
1867 -- an application error will be raised and processing will be terminated.
1868 --
1869 -- {End Of Comments}
1870 -- ----------------------------------------------------------------------------
1871 procedure chk_offer_status
1872 (p_offer_id in irc_offers.offer_id%TYPE
1873 ,p_offer_status in irc_offers.offer_status%TYPE
1874 ,p_effective_date in date
1875 ,p_object_version_number in irc_offers.object_version_number%TYPE
1876 )IS
1877 --
1878 l_proc varchar2(72) := g_package||'chk_offer_status';
1879 l_api_updating boolean;
1880 --
1881 begin
1885 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1882 hr_utility.set_location('Entering:'|| l_proc, 10);
1883 --
1884 if hr_multi_message.no_exclusive_error(
1886 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1887 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1888 ) then
1889 --
1890 --
1891 -- Check mandatory parameters have been set
1892 --
1893 hr_api.mandatory_arg_error
1894 (p_api_name => l_proc
1895 ,p_argument => 'effective_date'
1896 ,p_argument_value => p_effective_date
1897 );
1898 --
1899 -- Only proceed with validation if :
1900 -- a) The current g_old_rec is current and
1901 -- b) The value of offer status has changed
1902 --
1903 l_api_updating := irc_iof_shd.api_updating
1904 (p_offer_id => p_offer_id
1905 ,p_object_version_number => p_object_version_number);
1906 --
1907 if ((l_api_updating
1908 and nvl(irc_iof_shd.g_old_rec.offer_status, hr_api.g_varchar2) <>
1909 nvl(p_offer_status, hr_api.g_varchar2))
1910 or
1911 (NOT l_api_updating)) then
1912 --
1913 hr_utility.set_location(l_proc, 20);
1914 --
1915 -- Checks that the value for offer_status is
1916 -- valid and exists on irc_offer_statuses within
1917 -- the specified date range
1918 --
1919 if hr_api.not_exists_in_hr_lookups
1920 (p_effective_date => p_effective_date
1921 ,p_lookup_type => 'IRC_OFFER_STATUSES'
1922 ,p_lookup_code => p_offer_status
1923 ) then
1924 --
1925 hr_utility.set_location(l_proc, 30);
1926 -- Error: Invalid offer status type.
1927 fnd_message.set_name('PER', 'IRC_412323_INV_OFFER_STATUS');
1928 fnd_message.raise_error;
1929 end if;
1930 end if;
1931 --
1932 -- While creation the offer cannot of any of the following statuses:
1933 -- 'CLOSED', 'EXTENDED', 'APPROVED', 'HOLD' or 'PENDING_EXTENDED'
1934 --
1935 if NOT l_api_updating
1936 then
1937 --
1938 hr_utility.set_location(l_proc, 40);
1939 if p_offer_status in ('CLOSED', 'EXTENDED', 'HOLD', 'CORRECTION', 'PENDING_EXTENDED')
1940 then
1941 --
1942 hr_utility.set_location(l_proc, 50);
1943 fnd_message.set_name('PER', 'IRC_412309_INV_CRT_OFR_STATUS');
1944 fnd_message.raise_error;
1945 --
1946 end if;
1947 end if;
1948 --
1949 --
1950 end if; -- no_exclusive_error
1951 --
1952 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1953 exception
1954 when app_exception.application_exception then
1955 if hr_multi_message.exception_add
1956 (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
1957 ) then
1958 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1959 raise;
1960 end if;
1961 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1962 end chk_offer_status;
1963 --
1964 --
1965 -- ---------------------------------------------------------------------------
1966 -- |-----------------------< chk_offer_status_update >-----------------------|
1967 -- ---------------------------------------------------------------------------
1968 --
1969 -- Description:
1970 -- This procedure ensures the following:
1971 -- 1) If the offer_status is 'CLOSED' do nothing - This is because we have already
1972 -- checked for offer_status 'CLOSED' in update_offer procedure of irc_offers_api.
1973 --
1974 -- 2) If the offer is in 'HOLD' state now, and the offer_status was previously 'HOLD'
1975 -- too, throw an error saying that an offer in HOLD status cannot be updated.
1976 --
1977 -- 3) If the offer was previously in 'HOLD' status, the current offer_status should
1978 -- be the status in which the offer was before it was Held.
1979 --
1980 -- Pre-conditions:
1981 -- none
1982 --
1983 -- In Arguments:
1984 -- p_current_offer_record
1985 --
1986 -- Post Success:
1987 -- If the above mentioned checks succeed, processing continues.
1988 --
1989 -- Post Failure:
1990 -- Incase any of the cases fail, appropriate errors will be thrown.
1991 --
1992 -- {End Of Comments}
1993 -- ----------------------------------------------------------------------------
1994 procedure chk_offer_status_update
1995 ( p_current_offer_record in irc_iof_shd.g_rec_type
1996 )IS
1997 --
1998 l_proc varchar2(72) := g_package||'chk_offer_status_update';
1999 l_prev_offer_status irc_offers.offer_status%TYPE := irc_iof_shd.g_old_rec.offer_status;
2000 l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
2001 l_mutiple_fields_updated boolean;
2002 --
2003 cursor csr_prev_to_prev_offer_status is
2004 select ios1.offer_status
2005 from irc_offer_status_history ios1
2006 where ios1.offer_id = p_current_offer_record.offer_id
2007 and 1 = (select count(*)
2008 from irc_offer_status_history ios2
2009 where ios2.offer_id = p_current_offer_record.offer_id
2010 and ios2.offer_status_history_id > ios1.offer_status_history_id);
2011 --
2012 begin
2013 hr_utility.set_location('Entering:'|| l_proc, 10);
2014 --
2015 if p_current_offer_record.offer_status <> 'CLOSED'
2016 and p_current_offer_record.offer_status <> 'APPROVED'
2017 and p_current_offer_record.offer_status <> 'CORRECTION'
2018 and p_current_offer_record.offer_status <> 'EXTENDED'
2019 then
2020 --
2021 hr_utility.set_location(l_proc,20);
2022 --
2023 -- Check to see if the offer status is 'Hold'.
2024 --
2025 if ( p_current_offer_record.offer_status = 'HOLD'
2026 ) then
2030 -- Check if the offer was previously in 'Hold'
2027 --
2028 hr_utility.set_location(l_proc,30);
2029 --
2031 -- state too. If so, the offer has been updated. Hence,
2032 -- throw an error saying that the offer cannot be updated.
2033 --
2034 if ( l_prev_offer_status = 'HOLD'
2035 ) then
2036 --
2037 hr_utility.set_location(l_proc,40);
2038 --
2039 fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2040 fnd_message.raise_error;
2041 end if;
2042 --
2043 -- Also Check that when in HOLD, no other data can be
2044 -- changed in the offer record.
2045 --
2046 IRC_IOF_BUS.chk_multiple_fields_updated
2047 ( p_offer_id => p_current_offer_record.offer_id
2048 ,p_offer_status => p_current_offer_record.offer_status
2049 ,p_discretionary_job_title => p_current_offer_record.discretionary_job_title
2050 ,p_offer_extended_method => p_current_offer_record.offer_extended_method
2051 ,p_expiry_date => p_current_offer_record.expiry_date
2052 ,p_proposed_start_date => p_current_offer_record.proposed_start_date
2053 ,p_offer_letter_tracking_code => p_current_offer_record.offer_letter_tracking_code
2054 ,p_offer_postal_service => p_current_offer_record.offer_postal_service
2055 ,p_offer_shipping_date => p_current_offer_record.offer_shipping_date
2056 ,p_applicant_assignment_id => p_current_offer_record.applicant_assignment_id
2057 ,p_offer_assignment_id => p_current_offer_record.offer_assignment_id
2058 ,p_address_id => p_current_offer_record.address_id
2059 ,p_template_id => p_current_offer_record.template_id
2060 ,p_offer_letter_file_type => p_current_offer_record.offer_letter_file_type
2061 ,p_offer_letter_file_name => p_current_offer_record.offer_letter_file_name
2062 ,p_attribute_category => p_current_offer_record.attribute_category
2063 ,p_attribute1 => p_current_offer_record.attribute1
2064 ,p_attribute2 => p_current_offer_record.attribute2
2065 ,p_attribute3 => p_current_offer_record.attribute3
2066 ,p_attribute4 => p_current_offer_record.attribute4
2067 ,p_attribute5 => p_current_offer_record.attribute5
2068 ,p_attribute6 => p_current_offer_record.attribute6
2069 ,p_attribute7 => p_current_offer_record.attribute7
2070 ,p_attribute8 => p_current_offer_record.attribute8
2071 ,p_attribute9 => p_current_offer_record.attribute9
2072 ,p_attribute10 => p_current_offer_record.attribute10
2073 ,p_attribute11 => p_current_offer_record.attribute11
2074 ,p_attribute12 => p_current_offer_record.attribute12
2075 ,p_attribute13 => p_current_offer_record.attribute13
2076 ,p_attribute14 => p_current_offer_record.attribute14
2077 ,p_attribute15 => p_current_offer_record.attribute15
2078 ,p_attribute16 => p_current_offer_record.attribute16
2079 ,p_attribute17 => p_current_offer_record.attribute17
2080 ,p_attribute18 => p_current_offer_record.attribute18
2081 ,p_attribute19 => p_current_offer_record.attribute19
2082 ,p_attribute20 => p_current_offer_record.attribute20
2083 ,p_attribute21 => p_current_offer_record.attribute21
2084 ,p_attribute22 => p_current_offer_record.attribute22
2085 ,p_attribute23 => p_current_offer_record.attribute23
2086 ,p_attribute24 => p_current_offer_record.attribute24
2087 ,p_attribute25 => p_current_offer_record.attribute25
2088 ,p_attribute26 => p_current_offer_record.attribute26
2089 ,p_attribute27 => p_current_offer_record.attribute27
2090 ,p_attribute28 => p_current_offer_record.attribute28
2091 ,p_attribute29 => p_current_offer_record.attribute29
2092 ,p_attribute30 => p_current_offer_record.attribute30
2093 ,p_mutiple_fields_updated => l_mutiple_fields_updated
2094 );
2095 if ( l_mutiple_fields_updated = true )
2096 then
2097 --
2098 hr_utility.set_location(l_proc,45);
2099 --
2100 fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2101 fnd_message.raise_error;
2102 end if;
2103 --
2104 else
2105 --
2106 -- If the offer status is anything else.
2107 --
2108 hr_utility.set_location(l_proc,50);
2109 --
2110 -- Check if the offer was previously in 'Hold' State.
2111 -- If so, the current state should be the state which existed
2112 -- before the offer was Held.
2113 --
2114 if ( l_prev_offer_status = 'HOLD'
2115 ) then
2116 --
2117 hr_utility.set_location(l_proc,60);
2118 --
2119 open csr_prev_to_prev_offer_status;
2120 fetch csr_prev_to_prev_offer_status into l_prev_to_prev_offer_status;
2121 close csr_prev_to_prev_offer_status;
2122 --
2123 if ( p_current_offer_record.offer_status <> l_prev_to_prev_offer_status
2124 ) then
2125 --
2126 hr_utility.set_location(l_proc,70);
2127 --
2128 fnd_message.set_name('PER','IRC_412305_INV_PREVTOPREV_OFR');
2129 fnd_message.raise_error;
2130 --
2131 end if;
2132 end if;
2133 end if; -- if-else-end if
2134 end if; -- Offer_status = 'CLOSED'
2135 --
2136 hr_utility.set_location(' Leaving:'|| l_proc, 80);
2137 exception
2138 when app_exception.application_exception then
2139 if hr_multi_message.exception_add
2140 (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
2141 ) then
2142 hr_utility.set_location(' Leaving:'|| l_proc, 90);
2143 raise;
2144 end if;
2145 hr_utility.set_location(' Leaving:'|| l_proc, 100);
2146 end chk_offer_status_update;
2147 --
2148 -- ----------------------------------------------------------------------------
2149 -- |---------------------< chk_multiple_fields_updated >----------------------|
2150 -- ----------------------------------------------------------------------------
2151 -- {Start Of Comments}
2152 --
2153 -- Description:
2154 -- This procedure ensures that not more than one field has been updated in the
2155 -- offer record.
2156 --
2157 -- Pre Conditions:
2158 -- None
2159 --
2160 -- In Arguments:
2161 -- All the IRC_OFFERS table fields except object_version_number and respondent_id.
2162 --
2163 -- Post Success:
2164 -- If only one field has been updated, p_mutiple_fields_updated will be set to
2165 -- 'false'. If multiple fields have been updated, p_mutiple_fields_updated will be
2166 -- set to 'true'.
2167 --
2168 -- Post Failure:
2169 -- None
2170 --
2171 -- {End Of Comments}
2172 -- ----------------------------------------------------------------------------
2173 Procedure chk_multiple_fields_updated
2174 ( p_offer_id in number
2175 ,p_offer_status in varchar2 default null
2176 ,p_discretionary_job_title in varchar2 default null
2177 ,p_offer_extended_method in varchar2 default null
2178 ,p_expiry_date in date default null
2179 ,p_proposed_start_date in date default null
2180 ,p_offer_letter_tracking_code in varchar2 default null
2181 ,p_offer_postal_service in varchar2 default null
2182 ,p_offer_shipping_date in date default null
2183 ,p_applicant_assignment_id in number default null
2184 ,p_offer_assignment_id in number default null
2185 ,p_address_id in number default null
2186 ,p_template_id in number default null
2187 ,p_offer_letter_file_type in varchar2 default null
2188 ,p_offer_letter_file_name in varchar2 default null
2189 ,p_attribute_category in varchar2 default null
2190 ,p_attribute1 in varchar2 default null
2191 ,p_attribute2 in varchar2 default null
2192 ,p_attribute3 in varchar2 default null
2193 ,p_attribute4 in varchar2 default null
2194 ,p_attribute5 in varchar2 default null
2195 ,p_attribute6 in varchar2 default null
2196 ,p_attribute7 in varchar2 default null
2197 ,p_attribute8 in varchar2 default null
2198 ,p_attribute9 in varchar2 default null
2199 ,p_attribute10 in varchar2 default null
2200 ,p_attribute11 in varchar2 default null
2201 ,p_attribute12 in varchar2 default null
2202 ,p_attribute13 in varchar2 default null
2203 ,p_attribute14 in varchar2 default null
2204 ,p_attribute15 in varchar2 default null
2205 ,p_attribute16 in varchar2 default null
2206 ,p_attribute17 in varchar2 default null
2207 ,p_attribute18 in varchar2 default null
2208 ,p_attribute19 in varchar2 default null
2209 ,p_attribute20 in varchar2 default null
2210 ,p_attribute21 in varchar2 default null
2211 ,p_attribute22 in varchar2 default null
2212 ,p_attribute23 in varchar2 default null
2213 ,p_attribute24 in varchar2 default null
2214 ,p_attribute25 in varchar2 default null
2215 ,p_attribute26 in varchar2 default null
2216 ,p_attribute27 in varchar2 default null
2217 ,p_attribute28 in varchar2 default null
2218 ,p_attribute29 in varchar2 default null
2219 ,p_attribute30 in varchar2 default null
2220 ,p_mutiple_fields_updated out nocopy boolean
2221 ) IS
2222 --
2223 l_proc varchar2(72) := g_package || 'chk_multiple_fields_updated';
2224 l_update_count number(2) := 0;
2225 l_api_updating boolean;
2226 --
2227 Cursor C_Sel1 is
2228 select
2229 offer_id
2230 ,offer_version
2231 ,latest_offer
2232 ,offer_status
2233 ,discretionary_job_title
2234 ,offer_extended_method
2235 ,respondent_id
2236 ,expiry_date
2237 ,proposed_start_date
2238 ,offer_letter_tracking_code
2239 ,offer_postal_service
2240 ,offer_shipping_date
2241 ,applicant_assignment_id
2242 ,offer_assignment_id
2243 ,address_id
2244 ,template_id
2245 ,offer_letter_file_type
2246 ,offer_letter_file_name
2247 ,attribute_category
2248 ,attribute1
2249 ,attribute2
2250 ,attribute3
2251 ,attribute4
2252 ,attribute5
2253 ,attribute6
2254 ,attribute7
2255 ,attribute8
2256 ,attribute9
2260 ,attribute13
2257 ,attribute10
2258 ,attribute11
2259 ,attribute12
2261 ,attribute14
2262 ,attribute15
2263 ,attribute16
2264 ,attribute17
2265 ,attribute18
2266 ,attribute19
2267 ,attribute20
2268 ,attribute21
2269 ,attribute22
2270 ,attribute23
2271 ,attribute24
2272 ,attribute25
2273 ,attribute26
2274 ,attribute27
2275 ,attribute28
2276 ,attribute29
2277 ,attribute30
2278 ,object_version_number
2279 from irc_offers
2280 where offer_id = p_offer_id;
2281 --
2282 l_offer_old_rec C_Sel1%ROWTYPE;
2283 Begin
2284 --
2285 hr_utility.set_location('Entering:'||l_proc,10);
2286 --
2287 Open C_Sel1;
2288 Fetch C_Sel1 Into l_offer_old_rec;
2289 If C_Sel1%notfound Then
2290 Close C_Sel1;
2291 --
2292 -- The primary key is invalid therefore we must error
2293 --
2294 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2295 fnd_message.raise_error;
2296 End If;
2297 Close C_Sel1;
2298 --
2299 -- If any field has changed, increment l_update_count.
2300 --
2301 if( p_offer_id <> hr_api.g_number) then
2302 if
2303 nvl(l_offer_old_rec.offer_id, hr_api.g_number) <>
2304 nvl(p_offer_id, hr_api.g_number)
2305 then
2306 --
2307 hr_utility.set_location(l_proc,20);
2308 --
2309 l_update_count := l_update_count + 1;
2310 end if;
2311 end if;
2312
2313 if(p_offer_status <> hr_api.g_varchar2) then
2314 --
2315 hr_utility.set_location(l_proc,30);
2316 --
2317 if
2318 nvl(l_offer_old_rec.offer_status, hr_api.g_varchar2) <>
2319 nvl(p_offer_status, hr_api.g_varchar2)
2320 then
2321 --
2322 hr_utility.set_location(l_proc,50);
2323 --
2324 l_update_count := l_update_count + 1;
2325 end if;
2326 end if;
2327
2328 if(p_discretionary_job_title <> hr_api.g_varchar2)
2329 or p_discretionary_job_title is null
2330 then
2331 if
2332 nvl(l_offer_old_rec.discretionary_job_title, hr_api.g_varchar2) <>
2333 nvl(p_discretionary_job_title, hr_api.g_varchar2)
2334 then
2335 --
2336 hr_utility.set_location(l_proc,60);
2337 --
2338 l_update_count := l_update_count + 1;
2339 end if;
2340 end if;
2341
2342 if(p_offer_extended_method <> hr_api.g_varchar2)
2343 or p_offer_extended_method is null
2344 then
2345 if
2346 nvl(l_offer_old_rec.offer_extended_method, hr_api.g_varchar2) <>
2347 nvl(p_offer_extended_method, hr_api.g_varchar2)
2348 then
2349 --
2350 hr_utility.set_location(l_proc,70);
2351 --
2352 l_update_count := l_update_count + 1;
2353 end if;
2354 end if;
2355
2356 if(p_expiry_date <> hr_api.g_date)
2357 or p_expiry_date is null
2358 then
2359 if
2360 nvl(l_offer_old_rec.expiry_date, hr_api.g_date) <>
2361 nvl(p_expiry_date, hr_api.g_date)
2362 then
2363 --
2364 hr_utility.set_location(l_proc,90);
2365 --
2366 l_update_count := l_update_count + 1;
2367 end if;
2368 end if;
2369
2370 if(p_proposed_start_date <> hr_api.g_date)
2371 or p_proposed_start_date is null
2372 then
2373 if
2374 nvl(l_offer_old_rec.proposed_start_date, hr_api.g_date) <>
2375 nvl(p_proposed_start_date, hr_api.g_date)
2376 then
2377 --
2378 hr_utility.set_location(l_proc,100);
2379 --
2380 l_update_count := l_update_count + 1;
2381 end if;
2382 end if;
2383 --
2384 if(p_offer_letter_tracking_code <> hr_api.g_varchar2)
2385 or p_offer_letter_tracking_code is null
2386 then
2387 if
2388 nvl(l_offer_old_rec.offer_letter_tracking_code, hr_api.g_varchar2) <>
2389 nvl(p_offer_letter_tracking_code, hr_api.g_varchar2)
2390 then
2391 --
2392 hr_utility.set_location(l_proc,110);
2393 --
2394 l_update_count := l_update_count + 1;
2395 end if;
2396 end if;
2397
2398 if(p_offer_postal_service <> hr_api.g_varchar2)
2399 or p_offer_postal_service is null
2400 then
2401 if
2402 nvl(l_offer_old_rec.offer_postal_service, hr_api.g_varchar2) <>
2403 nvl(p_offer_postal_service, hr_api.g_varchar2)
2404 then
2405 --
2406 hr_utility.set_location(l_proc,120);
2407 --
2408 l_update_count := l_update_count + 1;
2409 end if;
2410 end if;
2411
2412 if(p_offer_shipping_date <> hr_api.g_date)
2413 or p_offer_shipping_date is null
2414 then
2415 if
2416 nvl(l_offer_old_rec.offer_shipping_date, hr_api.g_date) <>
2417 nvl(p_offer_shipping_date, hr_api.g_date)
2418 then
2419 --
2420 hr_utility.set_location(l_proc,130);
2421 --
2422 l_update_count := l_update_count + 1;
2423 end if;
2424 end if;
2425 --
2426
2427 if(p_applicant_assignment_id <> hr_api.g_number) then
2428 if
2429 nvl(l_offer_old_rec.applicant_assignment_id, hr_api.g_number) <>
2430 nvl(p_applicant_assignment_id, hr_api.g_number)
2431 then
2432 --
2433 hr_utility.set_location(l_proc,150);
2434 --
2435 l_update_count := l_update_count + 1;
2436 end if;
2437 end if;
2438
2439 if(p_offer_assignment_id <> hr_api.g_number) then
2440 if
2444 --
2441 nvl(l_offer_old_rec.offer_assignment_id, hr_api.g_number) <>
2442 nvl(p_offer_assignment_id, hr_api.g_number)
2443 then
2445 hr_utility.set_location(l_proc,160);
2446 --
2447 l_update_count := l_update_count + 1;
2448 end if;
2449 end if;
2450
2451 if(p_address_id <> hr_api.g_number)
2452 or p_address_id is null
2453 then
2454 if
2455 nvl(l_offer_old_rec.address_id, hr_api.g_number) <>
2456 nvl(p_address_id, hr_api.g_number)
2457 then
2458 --
2459 hr_utility.set_location(l_proc,170);
2460 --
2461 l_update_count := l_update_count + 1;
2462 end if;
2463 end if;
2464
2465 if(p_template_id <> hr_api.g_number)
2466 or p_template_id is null
2467 then
2468 if
2469 nvl(l_offer_old_rec.template_id, hr_api.g_number) <>
2470 nvl(p_template_id, hr_api.g_number)
2471 then
2472 --
2473 hr_utility.set_location(l_proc,180);
2474 --
2475 l_update_count := l_update_count + 1;
2476 end if;
2477 end if;
2478
2479 if(p_offer_letter_file_type <> hr_api.g_varchar2)
2480 or p_offer_letter_file_type is null
2481 then
2482 if
2483 nvl(l_offer_old_rec.offer_letter_file_type, hr_api.g_varchar2) <>
2484 nvl(p_offer_letter_file_type, hr_api.g_varchar2)
2485 then
2486 --
2487 hr_utility.set_location(l_proc,190);
2488 --
2489 l_update_count := l_update_count + 1;
2490 end if;
2491 end if;
2492
2493 if(p_offer_letter_file_name <> hr_api.g_varchar2)
2494 or p_offer_letter_file_name is null
2495 then
2496 if
2497 nvl(l_offer_old_rec.offer_letter_file_name, hr_api.g_varchar2) <>
2498 nvl(p_offer_letter_file_name, hr_api.g_varchar2)
2499 then
2500 --
2501 hr_utility.set_location(l_proc,200);
2502 --
2503 l_update_count := l_update_count + 1;
2504 end if;
2505 end if;
2506
2507 if(p_attribute_category <> hr_api.g_varchar2)
2508 or p_attribute_category is null
2509 then
2510 if
2511 nvl(l_offer_old_rec.attribute_category, hr_api.g_varchar2) <>
2512 nvl(p_attribute_category, hr_api.g_varchar2)
2513 then
2514 --
2515 hr_utility.set_location(l_proc,210);
2516 --
2517 l_update_count := l_update_count + 1;
2518 end if;
2519 end if;
2520
2521 if(p_attribute1 <> hr_api.g_varchar2)
2522 or p_attribute1 is null
2523 then
2524 if
2525 nvl(l_offer_old_rec.attribute1, hr_api.g_varchar2) <>
2526 nvl(p_attribute1, hr_api.g_varchar2)
2527 then
2528 --
2529 hr_utility.set_location(l_proc,220);
2530 --
2531 l_update_count := l_update_count + 1;
2532 end if;
2533 end if;
2534
2535 if(p_attribute2 <> hr_api.g_varchar2)
2536 or p_attribute2 is null
2537 then
2538 if
2539 nvl(l_offer_old_rec.attribute2, hr_api.g_varchar2) <>
2540 nvl(p_attribute2, hr_api.g_varchar2)
2541 then
2542 --
2543 hr_utility.set_location(l_proc,230);
2544 --
2545 l_update_count := l_update_count + 1;
2546 end if;
2547 end if;
2548
2549 if(p_attribute3 <> hr_api.g_varchar2)
2550 or p_attribute3 is null
2551 then
2552 if
2553 nvl(l_offer_old_rec.attribute3, hr_api.g_varchar2) <>
2554 nvl(p_attribute3, hr_api.g_varchar2)
2555 then
2556 --
2557 hr_utility.set_location(l_proc,240);
2558 --
2559 l_update_count := l_update_count + 1;
2560 end if;
2561 end if;
2562
2563 if(p_attribute4 <> hr_api.g_varchar2)
2564 or p_attribute4 is null
2565 then
2566 if
2567 nvl(l_offer_old_rec.attribute4, hr_api.g_varchar2) <>
2568 nvl(p_attribute4, hr_api.g_varchar2)
2569 then
2570 --
2571 hr_utility.set_location(l_proc,250);
2572 --
2573 l_update_count := l_update_count + 1;
2574 end if;
2575 end if;
2576
2577 if(p_attribute5 <> hr_api.g_varchar2)
2578 or p_attribute5 is null
2579 then
2580 if
2581 nvl(l_offer_old_rec.attribute5, hr_api.g_varchar2) <>
2582 nvl(p_attribute5, hr_api.g_varchar2)
2583 then
2584 --
2585 hr_utility.set_location(l_proc,260);
2586 --
2587 l_update_count := l_update_count + 1;
2588 end if;
2589 end if;
2590
2591 if(p_attribute6 <> hr_api.g_varchar2)
2592 or p_attribute6 is null
2593 then
2594 if
2595 nvl(l_offer_old_rec.attribute6, hr_api.g_varchar2) <>
2596 nvl(p_attribute6, hr_api.g_varchar2)
2597 then
2598 --
2599 hr_utility.set_location(l_proc,270);
2600 --
2601 l_update_count := l_update_count + 1;
2602 end if;
2603 end if;
2604
2605 if(p_attribute7 <> hr_api.g_varchar2)
2606 or p_attribute7 is null
2607 then
2608 if
2609 nvl(l_offer_old_rec.attribute7, hr_api.g_varchar2) <>
2610 nvl(p_attribute7, hr_api.g_varchar2)
2611 then
2612 --
2613 hr_utility.set_location(l_proc,280);
2614 --
2615 l_update_count := l_update_count + 1;
2616 end if;
2617 end if;
2618
2619 if(p_attribute8 <> hr_api.g_varchar2)
2620 or p_attribute8 is null
2621 then
2622 if
2623 nvl(l_offer_old_rec.attribute8, hr_api.g_varchar2) <>
2624 nvl(p_attribute8, hr_api.g_varchar2)
2625 then
2626 --
2627 hr_utility.set_location(l_proc,290);
2628 --
2632
2629 l_update_count := l_update_count + 1;
2630 end if;
2631 end if;
2633 if(p_attribute9 <> hr_api.g_varchar2)
2634 or p_attribute9 is null
2635 then
2636 if
2637 nvl(l_offer_old_rec.attribute9, hr_api.g_varchar2) <>
2638 nvl(p_attribute9, hr_api.g_varchar2)
2639 then
2640 --
2641 hr_utility.set_location(l_proc,300);
2642 --
2643 l_update_count := l_update_count + 1;
2644 end if;
2645 end if;
2646
2647 if(p_attribute10 <> hr_api.g_varchar2)
2648 or p_attribute10 is null
2649 then
2650 if
2651 nvl(l_offer_old_rec.attribute10, hr_api.g_varchar2) <>
2652 nvl(p_attribute10, hr_api.g_varchar2)
2653 then
2654 --
2655 hr_utility.set_location(l_proc,310);
2656 --
2657 l_update_count := l_update_count + 1;
2658 end if;
2659 end if;
2660
2661 if(p_attribute11 <> hr_api.g_varchar2)
2662 or p_attribute11 is null
2663 then
2664 if
2665 nvl(l_offer_old_rec.attribute11, hr_api.g_varchar2) <>
2666 nvl(p_attribute11, hr_api.g_varchar2)
2667 then
2668 --
2669 hr_utility.set_location(l_proc,320);
2670 --
2671 l_update_count := l_update_count + 1;
2672 end if;
2673 end if;
2674
2675 if(p_attribute12 <> hr_api.g_varchar2)
2676 or p_attribute12 is null
2677 then
2678 if
2679 nvl(l_offer_old_rec.attribute12, hr_api.g_varchar2) <>
2680 nvl(p_attribute12, hr_api.g_varchar2)
2681 then
2682 --
2683 hr_utility.set_location(l_proc,330);
2684 --
2685 l_update_count := l_update_count + 1;
2686 end if;
2687 end if;
2688
2689 if(p_attribute13 <> hr_api.g_varchar2)
2690 or p_attribute13 is null
2691 then
2692 if
2693 nvl(l_offer_old_rec.attribute13, hr_api.g_varchar2) <>
2694 nvl(p_attribute13, hr_api.g_varchar2)
2695 then
2696 --
2697 hr_utility.set_location(l_proc,340);
2698 --
2699 l_update_count := l_update_count + 1;
2700 end if;
2701 end if;
2702
2703 if(p_attribute14 <> hr_api.g_varchar2)
2704 or p_attribute14 is null
2705 then
2706 if
2707 nvl(l_offer_old_rec.attribute14, hr_api.g_varchar2) <>
2708 nvl(p_attribute14, hr_api.g_varchar2)
2709 then
2710 --
2711 hr_utility.set_location(l_proc,350);
2712 --
2713 l_update_count := l_update_count + 1;
2714 end if;
2715 end if;
2716
2717 if(p_attribute15 <> hr_api.g_varchar2)
2718 or p_attribute15 is null
2719 then
2720 if
2721 nvl(l_offer_old_rec.attribute15, hr_api.g_varchar2) <>
2722 nvl(p_attribute15, hr_api.g_varchar2)
2723 then
2724 --
2725 hr_utility.set_location(l_proc,360);
2726 --
2727 l_update_count := l_update_count + 1;
2728 end if;
2729 end if;
2730
2731 if(p_attribute16 <> hr_api.g_varchar2)
2732 or p_attribute16 is null
2733 then
2734 if
2735 nvl(l_offer_old_rec.attribute16, hr_api.g_varchar2) <>
2736 nvl(p_attribute16, hr_api.g_varchar2)
2737 then
2738 --
2739 hr_utility.set_location(l_proc,370);
2740 --
2741 l_update_count := l_update_count + 1;
2742 end if;
2743 end if;
2744
2745 if(p_attribute17 <> hr_api.g_varchar2)
2746 or p_attribute17 is null
2747 then
2748 if
2749 nvl(l_offer_old_rec.attribute17, hr_api.g_varchar2) <>
2750 nvl(p_attribute17, hr_api.g_varchar2)
2751 then
2752 --
2753 hr_utility.set_location(l_proc,380);
2754 --
2755 l_update_count := l_update_count + 1;
2756 end if;
2757 end if;
2758
2759 if(p_attribute18 <> hr_api.g_varchar2)
2760 or p_attribute18 is null
2761 then
2762 if
2763 nvl(l_offer_old_rec.attribute18, hr_api.g_varchar2) <>
2764 nvl(p_attribute18, hr_api.g_varchar2)
2765 then
2766 --
2767 hr_utility.set_location(l_proc,390);
2768 --
2769 l_update_count := l_update_count + 1;
2770 end if;
2771 end if;
2772
2773 if(p_attribute19 <> hr_api.g_varchar2)
2774 or p_attribute19 is null
2775 then
2776 if
2777 nvl(l_offer_old_rec.attribute19, hr_api.g_varchar2) <>
2778 nvl(p_attribute19, hr_api.g_varchar2)
2779 then
2780 --
2781 hr_utility.set_location(l_proc,400);
2782 --
2783 l_update_count := l_update_count + 1;
2784 end if;
2785 end if;
2786
2787 if(p_attribute20 <> hr_api.g_varchar2)
2788 or p_attribute20 is null
2789 then
2790 if
2791 nvl(l_offer_old_rec.attribute20, hr_api.g_varchar2) <>
2792 nvl(p_attribute20, hr_api.g_varchar2)
2793 then
2794 --
2795 hr_utility.set_location(l_proc,410);
2796 --
2797 l_update_count := l_update_count + 1;
2798 end if;
2799 end if;
2800
2801 if(p_attribute21 <> hr_api.g_varchar2)
2802 or p_attribute21 is null
2803 then
2804 if
2805 nvl(l_offer_old_rec.attribute21, hr_api.g_varchar2) <>
2806 nvl(p_attribute21, hr_api.g_varchar2)
2807 then
2808 --
2809 hr_utility.set_location(l_proc,420);
2810 --
2811 l_update_count := l_update_count + 1;
2812 end if;
2813 end if;
2814
2815 if(p_attribute22 <> hr_api.g_varchar2)
2816 or p_attribute22 is null
2817 then
2818 if
2819 nvl(l_offer_old_rec.attribute22, hr_api.g_varchar2) <>
2823 hr_utility.set_location(l_proc,430);
2820 nvl(p_attribute22, hr_api.g_varchar2)
2821 then
2822 --
2824 --
2825 l_update_count := l_update_count + 1;
2826 end if;
2827 end if;
2828
2829 if(p_attribute23 <> hr_api.g_varchar2)
2830 or p_attribute23 is null
2831 then
2832 if
2833 nvl(l_offer_old_rec.attribute23, hr_api.g_varchar2) <>
2834 nvl(p_attribute23, hr_api.g_varchar2)
2835 then
2836 --
2837 hr_utility.set_location(l_proc,440);
2838 --
2839 l_update_count := l_update_count + 1;
2840 end if;
2841 end if;
2842
2843 if(p_attribute24 <> hr_api.g_varchar2)
2844 or p_attribute24 is null
2845 then
2846 if
2847 nvl(l_offer_old_rec.attribute24, hr_api.g_varchar2) <>
2848 nvl(p_attribute24, hr_api.g_varchar2)
2849 then
2850 --
2851 hr_utility.set_location(l_proc,450);
2852 --
2853 l_update_count := l_update_count + 1;
2854 end if;
2855 end if;
2856
2857 if(p_attribute25 <> hr_api.g_varchar2)
2858 or p_attribute25 is null
2859 then
2860 if
2861 nvl(l_offer_old_rec.attribute25, hr_api.g_varchar2) <>
2862 nvl(p_attribute25, hr_api.g_varchar2)
2863 then
2864 --
2865 hr_utility.set_location(l_proc,460);
2866 --
2867 l_update_count := l_update_count + 1;
2868 end if;
2869 end if;
2870
2871 if(p_attribute26 <> hr_api.g_varchar2)
2872 or p_attribute26 is null
2873 then
2874 if
2875 nvl(l_offer_old_rec.attribute26, hr_api.g_varchar2) <>
2876 nvl(p_attribute26, hr_api.g_varchar2)
2877 then
2878 --
2879 hr_utility.set_location(l_proc,470);
2880 --
2881 l_update_count := l_update_count + 1;
2882 end if;
2883 end if;
2884
2885 if(p_attribute27 <> hr_api.g_varchar2)
2886 or p_attribute27 is null
2887 then
2888 if
2889 nvl(l_offer_old_rec.attribute27, hr_api.g_varchar2) <>
2890 nvl(p_attribute27, hr_api.g_varchar2)
2891 then
2892 --
2893 hr_utility.set_location(l_proc,480);
2894 --
2895 l_update_count := l_update_count + 1;
2896 end if;
2897 end if;
2898
2899 if(p_attribute28 <> hr_api.g_varchar2)
2900 or p_attribute28 is null
2901 then
2902 if
2903 nvl(l_offer_old_rec.attribute28, hr_api.g_varchar2) <>
2904 nvl(p_attribute28, hr_api.g_varchar2)
2905 then
2906 --
2907 hr_utility.set_location(l_proc,490);
2908 --
2909 l_update_count := l_update_count + 1;
2910 end if;
2911 end if;
2912
2913 if(p_attribute29 <> hr_api.g_varchar2)
2914 or p_attribute29 is null
2915 then
2916 if
2917 nvl(l_offer_old_rec.attribute29, hr_api.g_varchar2) <>
2918 nvl(p_attribute29, hr_api.g_varchar2)
2919 then
2920 --
2921 hr_utility.set_location(l_proc,500);
2922 --
2923 l_update_count := l_update_count + 1;
2924 end if;
2925 end if;
2926
2927 if(p_attribute30 <> hr_api.g_varchar2)
2928 or p_attribute30 is null
2929 then
2930 if
2931 nvl(l_offer_old_rec.attribute30, hr_api.g_varchar2) <>
2932 nvl(p_attribute30, hr_api.g_varchar2)
2933 then
2934 --
2935 hr_utility.set_location(l_proc,510);
2936 --
2937 l_update_count := l_update_count + 1;
2938 end if;
2939 end if;
2940 --
2941 -- Check if l_update_count > 1, if Yes, set p_mutiple_fields_updated
2942 -- to True.
2943 --
2944 if l_update_count > 1
2945 then
2946 --
2947 hr_utility.set_location(l_proc,520);
2948 --
2949 p_mutiple_fields_updated := true;
2950 else
2951 --
2952 hr_utility.set_location(l_proc,530);
2953 --
2954 p_mutiple_fields_updated := false;
2955 end if;
2956 hr_utility.set_location(' Leaving:'||l_proc,540);
2957 --
2958 exception
2959 when others then
2960 hr_utility.set_location(' Leaving:'||l_proc,550);
2961 raise;
2962 End chk_multiple_fields_updated;
2963 --
2964 --
2965 -- ---------------------------------------------------------------------------
2966 -- |---------------------< chk_offer_extended_method >-----------------------|
2967 -- ---------------------------------------------------------------------------
2968 --
2969 -- Description:
2970 -- This procedure is used to ensure that offer extended method is a valid
2971 -- value from IRC_OFFER_EXTENDED_METHOD lookup
2972 --
2973 -- Pre-conditions:
2974 -- Effective_date must be valid.
2975 --
2976 -- In Arguments:
2977 -- p_offer_id
2978 -- p_offer_extended_method
2979 -- p_effective_date
2980 -- p_object_version_number
2981 --
2982 -- Post Success:
2983 -- If the given offer extended method exists in IRC_OFFER_EXTENDED_METHOD
2984 -- Lookup, processing continues.
2985 --
2986 -- Post Failure:
2987 -- If the offer extended method does not exist in IRC_OFFER_EXTENDED_METHOD
2988 -- Lookup, an application error will be raised and processing will be terminated.
2989 --
2990 -- {End Of Comments}
2991 -- ----------------------------------------------------------------------------
2992 procedure chk_offer_extended_method
2993 (p_offer_id in irc_offers.offer_id%TYPE
2994 ,p_offer_extended_method in irc_offers.offer_extended_method%TYPE
2995 ,p_effective_date in date
2996 ,p_object_version_number in irc_offers.object_version_number%TYPE
2997 )IS
2998 --
2999 l_proc varchar2(72) := g_package||'chk_offer_extended_method';
3000 l_api_updating boolean;
3001 --
3002 begin
3003 hr_utility.set_location('Entering:'|| l_proc, 10);
3004 --
3005 if hr_multi_message.no_exclusive_error(
3006 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3007 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3008 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3009 ) then
3010 --
3011 -- Check mandatory parameters have been set
3012 --
3013 hr_api.mandatory_arg_error
3014 (p_api_name => l_proc
3015 ,p_argument => 'effective_date'
3016 ,p_argument_value => p_effective_date
3017 );
3018 --
3019 -- Only proceed with validation if :
3020 -- a) The current g_old_rec is current and
3021 -- b) The value for offer extended method has changed
3022 --
3023 l_api_updating := irc_iof_shd.api_updating
3024 (p_offer_id => p_offer_id
3025 ,p_object_version_number => p_object_version_number);
3026 --
3027 if ((l_api_updating
3028 and nvl(irc_iof_shd.g_old_rec.offer_extended_method, hr_api.g_varchar2) <>
3029 nvl(p_offer_extended_method, hr_api.g_varchar2))
3030 or
3031 (NOT l_api_updating)) then
3032 --
3033 -- Check if offer_extended_method is not null.
3034 --
3035 if p_offer_extended_method is not null then
3036 --
3037 hr_utility.set_location(l_proc, 20);
3038 --
3039 -- Checks that the value for offer_extended_method is
3040 -- valid and exists on irc_offer_extended_method lookup
3041 -- within the specified date range
3042 --
3043 if hr_api.not_exists_in_hr_lookups
3044 (p_effective_date => p_effective_date
3045 ,p_lookup_type => 'IRC_OFFER_EXTENDED_METHOD'
3046 ,p_lookup_code => p_offer_extended_method
3047 ) then
3048 --
3049 -- Error: Invalid offer extended method.
3050 fnd_message.set_name(800, 'IRC_412310_INV_OFR_EXTNDD_MTHD');
3051 fnd_message.raise_error;
3052 end if;
3053 end if;
3054 end if;
3055 end if; -- no_exclusive_error
3056 --
3057 hr_utility.set_location(' Leaving:'|| l_proc, 30);
3058 exception
3059 when app_exception.application_exception then
3060 if hr_multi_message.exception_add
3061 (p_associated_column1 => 'IRC_OFFERS.OFFER_EXTENDED_METHOD'
3062 ) then
3063 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3064 raise;
3065 end if;
3066 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3067 end chk_offer_extended_method;
3068 --
3069 -- ---------------------------------------------------------------------------
3070 -- |---------------------< chk_offer_postal_service >------------------------|
3071 -- ---------------------------------------------------------------------------
3072 --
3073 -- Description:
3074 -- This procedure is used to ensure that offer postal service is a valid
3075 -- value from IRC_OFFER_POSTAL_SERVICE lookup
3076 --
3077 -- Pre-conditions:
3078 -- Effective_date must be valid.
3079 --
3080 -- In Arguments:
3081 -- p_offer_id
3082 -- p_offer_postal_service
3083 -- p_effective_date
3084 -- p_object_version_number
3085 --
3086 -- Post Success:
3087 -- If the given offer postal service exists in IRC_OFFER_POSTAL_SERVICE
3088 -- Lookup, processing continues.
3089 --
3090 -- Post Failure:
3091 -- If the offer extended method does not exist in IRC_OFFER_POSTAL_SERVICE
3092 -- Lookup, an application error will be raised and processing will be terminated.
3093 --
3094 -- {End Of Comments}
3095 -- ----------------------------------------------------------------------------
3096 procedure chk_offer_postal_service
3097 (p_offer_id in irc_offers.offer_id%TYPE
3098 ,p_offer_postal_service in irc_offers.offer_postal_service%TYPE
3099 ,p_effective_date in date
3100 ,p_object_version_number in irc_offers.object_version_number%TYPE
3101 )IS
3102 --
3103 l_proc varchar2(72) := g_package||'chk_offer_postal_service';
3104 l_api_updating boolean;
3105 --
3106 begin
3107 hr_utility.set_location('Entering:'|| l_proc, 10);
3108 --
3109 if hr_multi_message.no_exclusive_error(
3110 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3111 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3112 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3113 ) then
3114 --
3115 -- Check mandatory parameters have been set
3116 --
3117 hr_api.mandatory_arg_error
3118 (p_api_name => l_proc
3119 ,p_argument => 'effective_date'
3120 ,p_argument_value => p_effective_date
3121 );
3122 --
3123 -- Only proceed with validation if :
3124 -- a) The current g_old_rec is current and
3125 -- b) The value for offer postal service has changed
3126 --
3127 l_api_updating := irc_iof_shd.api_updating
3128 (p_offer_id => p_offer_id
3129 ,p_object_version_number => p_object_version_number);
3130 --
3131 if ((l_api_updating
3132 and nvl(irc_iof_shd.g_old_rec.offer_postal_service, hr_api.g_varchar2) <>
3133 nvl(p_offer_postal_service, hr_api.g_varchar2))
3134 or
3135 (NOT l_api_updating)) then
3136 --
3137 -- Check if offer_postal_service is not null.
3138 --
3139 if p_offer_postal_service is not null then
3140 --
3141 hr_utility.set_location(l_proc, 20);
3142 --
3143 -- Checks that the value for offer_postal_service is
3147 if hr_api.not_exists_in_hr_lookups
3144 -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3145 -- within the specified date range
3146 --
3148 (p_effective_date => p_effective_date
3149 ,p_lookup_type => 'IRC_OFFER_POSTAL_SERVICE'
3150 ,p_lookup_code => p_offer_postal_service
3151 ) then
3152 --
3153 -- Error: Invalid offer extended method.
3154 fnd_message.set_name(800, 'IRC_412311_INV_OFR_POSTAL_SERV');
3155 fnd_message.raise_error;
3156 end if;
3157 end if;
3158 end if;
3159 end if; -- no_exclusive_error
3160 --
3161 hr_utility.set_location(' Leaving:'|| l_proc, 30);
3162 exception
3163 when app_exception.application_exception then
3164 if hr_multi_message.exception_add
3165 (p_associated_column1 => 'IRC_OFFERS.OFFER_POSTAL_SERVICE'
3166 ) then
3167 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3168 raise;
3169 end if;
3170 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3171 end chk_offer_postal_service;
3172 --
3173 -- ---------------------------------------------------------------------------
3174 -- |--------------------------< chk_offer_letter >---------------------------|
3175 -- ---------------------------------------------------------------------------
3176 --
3177 -- Description:
3178 -- This procedure checks if, when the offer is moved to APPROVED status,
3179 -- an offer letter has been uploaded.
3180 --
3181 -- Pre-conditions:
3182 -- The offer status should be changed to APPROVED.
3183 --
3184 -- In Arguments:
3185 -- p_offer_id
3186 -- p_offer_status
3187 --
3188 -- Post Success:
3189 -- If the given offer letter file type exists in XDO_OUTPUT_TYPE Lookup,
3190 -- processing continues.
3191 --
3192 -- Post Failure:
3193 -- If the offer letter file type does not exist in XDO_OUTPUT_TYPE Lookup,
3194 -- an application error will be raised and processing will be terminated.
3195 --
3196 -- {End Of Comments}
3197 -- ----------------------------------------------------------------------------
3198 procedure chk_offer_letter
3199 (p_offer_id in irc_offers.offer_id%TYPE
3200 ,p_offer_status in irc_offers.offer_status%TYPE
3201 )IS
3202 --
3203 l_proc varchar2(72) := g_package||'chk_offer_letter';
3204 l_offer_letter irc_offers.offer_letter%TYPE;
3205 --
3206 cursor csr_offer_letter is
3207 select offer_letter
3208 from irc_offers
3209 where offer_id = p_offer_id;
3210 --
3211 begin
3212 hr_utility.set_location('Entering:'|| l_proc, 10);
3213 --
3214 if hr_multi_message.no_exclusive_error(
3215 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3216 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3217 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3218 ) then
3219 --
3220 if p_offer_status = 'APPROVED'
3221 and nvl(irc_iof_shd.g_old_rec.offer_status, hr_api.g_varchar2) <>
3222 nvl(p_offer_status, hr_api.g_varchar2)
3223 then
3224 --
3225 hr_utility.set_location(l_proc, 20);
3226 --
3227 open csr_offer_letter;
3228 fetch csr_offer_letter into l_offer_letter;
3229 if csr_offer_letter%notfound
3230 then
3231 --
3232 hr_utility.set_location(l_proc, 30);
3233 --
3234 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
3235 fnd_message.raise_error;
3236 --
3237 end if;
3238 --
3239 -- Check if the offer letter is present. If blob length is 0, it means that
3240 -- the blob is not uploaded.
3241 --
3242 if dbms_lob.getlength(l_offer_letter) = 0
3243 then
3244 --
3245 hr_utility.set_location(l_proc, 40);
3246 --
3247 fnd_message.set_name('PER','IRC_412312_UPLOAD_OFFER_LETTER');
3248 hr_multi_message.add
3249 (p_message_type => hr_multi_message.g_warning_msg
3250 );
3251 --
3252 end if;
3253 --
3254 end if;
3255 end if; -- no_exclusive_error
3256 --
3257 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3258 exception
3259 when app_exception.application_exception then
3260 if hr_multi_message.exception_add
3261 (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER'
3262 ) then
3263 hr_utility.set_location(' Leaving:'|| l_proc, 60);
3264 raise;
3265 end if;
3266 hr_utility.set_location(' Leaving:'|| l_proc, 70);
3267 end chk_offer_letter;
3268 --
3269 --
3270 -- ---------------------------------------------------------------------------
3271 -- |--------------------< chk_offer_letter_file_type >-----------------------|
3272 -- ---------------------------------------------------------------------------
3273 --
3274 -- Description:
3275 -- This procedure is used to ensure that offer letter file type is a valid
3276 -- value from XDO_OUTPUT_TYPE lookup
3277 --
3278 -- Pre-conditions:
3279 -- Effective_date must be valid.
3280 --
3281 -- In Arguments:
3282 -- p_offer_id
3283 -- p_offer_letter_file_type
3284 -- p_effective_date
3285 -- p_object_version_number
3286 --
3287 -- Post Success:
3288 -- If the given offer letter file type exists in XDO_OUTPUT_TYPE Lookup,
3289 -- processing continues.
3290 --
3291 -- Post Failure:
3292 -- If the offer letter file type does not exist in XDO_OUTPUT_TYPE Lookup,
3293 -- an application error will be raised and processing will be terminated.
3294 --
3295 -- {End Of Comments}
3296 -- ----------------------------------------------------------------------------
3297 procedure chk_offer_letter_file_type
3298 (p_offer_id in irc_offers.offer_id%TYPE
3299 ,p_offer_letter_file_type in irc_offers.offer_letter_file_type%TYPE
3300 ,p_effective_date in date
3301 ,p_object_version_number in irc_offers.object_version_number%TYPE
3302 )IS
3303 --
3304 l_proc varchar2(72) := g_package||'chk_offer_letter_file_type';
3305 l_api_updating boolean;
3306 --
3307 begin
3308 hr_utility.set_location('Entering:'|| l_proc, 10);
3309 --
3310 if hr_multi_message.no_exclusive_error(
3311 p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3312 ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3313 ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
3314 ) then
3315 --
3316 -- Check mandatory parameters have been set
3317 --
3318 hr_api.mandatory_arg_error
3319 (p_api_name => l_proc
3320 ,p_argument => 'effective_date'
3321 ,p_argument_value => p_effective_date
3322 );
3323 --
3324 -- Only proceed with validation if :
3325 -- a) The current g_old_rec is current and
3326 -- b) The value for offer postal service has changed
3327 --
3328 l_api_updating := irc_iof_shd.api_updating
3329 (p_offer_id => p_offer_id
3330 ,p_object_version_number => p_object_version_number);
3331 --
3332 if ((l_api_updating
3333 and nvl(irc_iof_shd.g_old_rec.offer_letter_file_type, hr_api.g_varchar2) <>
3334 nvl(p_offer_letter_file_type, hr_api.g_varchar2))
3335 or
3336 (NOT l_api_updating)) then
3337 --
3338 -- Check if offer_postal_service is not null.
3339 --
3340 if p_offer_letter_file_type is not null then
3341 --
3342 hr_utility.set_location(l_proc, 20);
3343 --
3344 -- Checks that the value for offer_postal_service is
3345 -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3346 -- within the specified date range
3347 --
3348 if hr_api.not_exists_in_fnd_lookups
3349 (p_effective_date => p_effective_date
3350 ,p_lookup_type => 'XDO_OUTPUT_TYPE'
3351 ,p_lookup_code => p_offer_letter_file_type
3352 ) then
3353
3354 fnd_message.set_name(800, 'IRC_412312_UPLOAD_OFFER_LETTER');
3355 fnd_message.raise_error;
3356 end if;
3357 end if;
3358 end if;
3359 end if; -- no_exclusive_error
3360 --
3361 hr_utility.set_location(' Leaving:'|| l_proc, 30);
3362 exception
3363 when app_exception.application_exception then
3364 if hr_multi_message.exception_add
3365 (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER_FILE_TYPE'
3366 ) then
3367 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3368 raise;
3369 end if;
3370 hr_utility.set_location(' Leaving:'|| l_proc, 50);
3371 end chk_offer_letter_file_type;
3372 --
3373 -- ----------------------------------------------------------------------------
3374 -- |---------------------------< insert_validate >----------------------------|
3375 -- ----------------------------------------------------------------------------
3376 --
3377 Procedure insert_validate
3378 (p_effective_date in date
3379 ,p_rec in out nocopy irc_iof_shd.g_rec_type
3380 ) is
3381 --
3382 l_proc varchar2(72) := g_package||'insert_validate';
3383 --
3384 Begin
3385 hr_utility.set_location('Entering:'||l_proc, 10);
3386 --
3387 -- Call all supporting business operations
3388 --
3389 hr_utility.set_location(l_proc, 20);
3390 --
3391 chk_applicant_assignment_id
3392 (p_effective_date => p_effective_date
3393 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3394 );
3395 --
3396 hr_utility.set_location(l_proc, 30);
3397 --
3398 chk_offer_assignment_id
3399 (p_offer_assignment_id => p_rec.offer_assignment_id
3400 );
3401 --
3402 hr_utility.set_location(l_proc, 40);
3403 --
3404 set_vacancy_id
3405 (p_vacancy_id => p_rec.vacancy_id
3406 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3407 ,p_effective_date => p_effective_date
3408 );
3409 --
3410 hr_utility.set_location(l_proc, 50);
3411 --
3412 chk_offers_exceeds_openings
3413 (p_vacancy_id => p_rec.vacancy_id
3414 ,p_offer_status => p_rec.offer_status
3415 ,p_offer_id => p_rec.offer_id
3416 );
3417 --
3418 hr_utility.set_location(l_proc, 60);
3419 --
3420 chk_respondent_id
3421 (p_respondent_id => p_rec.respondent_id
3422 ,p_offer_id => p_rec.offer_id
3423 ,p_object_version_number => p_rec.object_version_number
3424 );
3425 --
3426 hr_utility.set_location(l_proc, 70);
3427 --
3428 set_address_id
3429 (p_address_id => p_rec.address_id
3430 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3431 ,p_effective_date => p_effective_date
3432 );
3433 --
3434 hr_utility.set_location(l_proc, 75);
3435 --
3436 chk_address_id
3437 (p_address_id => p_rec.address_id
3438 ,p_offer_id => p_rec.offer_id
3439 ,p_object_version_number => p_rec.object_version_number
3440 );
3441 --
3442 hr_utility.set_location(l_proc, 80);
3443 --
3444 chk_template_id
3445 (p_template_id => p_rec.template_id
3446 ,p_offer_id => p_rec.offer_id
3447 ,p_object_version_number => p_rec.object_version_number
3451 hr_utility.set_location(l_proc, 90);
3448 ,p_effective_date => p_effective_date
3449 );
3450 --
3452 --
3453 gen_offer_version
3454 (p_offer_version => p_rec.offer_version
3455 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3456 );
3457 --
3458 hr_utility.set_location(l_proc, 100);
3459 --
3460 chk_latest_offer
3461 (p_latest_offer => p_rec.latest_offer
3462 ,p_offer_id => p_rec.offer_id
3463 ,p_offer_status => p_rec.offer_status
3464 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3465 ,p_effective_date => p_effective_date
3466 ,p_object_version_number => p_rec.object_version_number
3467 );
3468 --
3469 hr_utility.set_location(l_proc, 110);
3470 --
3471 chk_offer_version_combination
3472 (p_offer_id => p_rec.offer_id
3473 ,p_offer_version => p_rec.offer_version
3474 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3475 ,p_object_version_number => p_rec.object_version_number
3476 );
3477 --
3478 hr_utility.set_location(l_proc, 120);
3479 --
3480 chk_offer_status
3481 (p_offer_id => p_rec.offer_id
3482 ,p_offer_status => p_rec.offer_status
3483 ,p_effective_date => p_effective_date
3484 ,p_object_version_number => p_rec.object_version_number
3485 );
3486 --
3487 hr_utility.set_location(l_proc, 130);
3488 --
3489 chk_offer_extended_method
3490 (p_offer_id => p_rec.offer_id
3491 ,p_offer_extended_method => p_rec.offer_extended_method
3492 ,p_effective_date => p_effective_date
3493 ,p_object_version_number => p_rec.object_version_number
3494 );
3495 --
3496 hr_utility.set_location(l_proc, 140);
3497 --
3498 chk_offer_postal_service
3499 (p_offer_id => p_rec.offer_id
3500 ,p_offer_postal_service => p_rec.offer_postal_service
3501 ,p_effective_date => p_effective_date
3502 ,p_object_version_number => p_rec.object_version_number
3503 );
3504 --
3505 hr_utility.set_location(l_proc, 150);
3506 --
3507 chk_offer_letter_file_type
3508 (p_offer_id => p_rec.offer_id
3509 ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3510 ,p_effective_date => p_effective_date
3511 ,p_object_version_number => p_rec.object_version_number
3512 );
3513 --
3514 hr_utility.set_location(l_proc, 170);
3515 --
3516 irc_iof_bus.chk_df(p_rec);
3517 --
3518 hr_utility.set_location(l_proc, 180);
3519 --
3520 hr_utility.set_location(' Leaving:'||l_proc, 180);
3521 End insert_validate;
3522 --
3523 -- ----------------------------------------------------------------------------
3524 -- |---------------------------< update_validate >----------------------------|
3525 -- ----------------------------------------------------------------------------
3526 Procedure update_validate
3527 (p_effective_date in date
3528 ,p_rec in out nocopy irc_iof_shd.g_rec_type
3529 ) is
3530 --
3531 l_proc varchar2(72) := g_package||'update_validate';
3532 --
3533 Begin
3534 hr_utility.set_location('Entering:'||l_proc, 10);
3535 --
3536 -- Call all supporting business operations
3537 --
3538 hr_utility.set_location(l_proc, 20);
3539 --
3540 chk_non_updateable_args
3541 (p_effective_date => p_effective_date
3542 ,p_rec => p_rec
3543 );
3544 --
3545 hr_utility.set_location(l_proc, 30);
3546 --
3547 chk_offers_exceeds_openings
3548 (p_vacancy_id => p_rec.vacancy_id
3549 ,p_offer_status => p_rec.offer_status
3550 ,p_offer_id => p_rec.offer_id
3551 );
3552 --
3553 hr_utility.set_location(l_proc, 35);
3554 --
3555 chk_respondent_id
3556 (p_respondent_id => p_rec.respondent_id
3557 ,p_offer_id => p_rec.offer_id
3558 ,p_object_version_number => p_rec.object_version_number
3559 );
3560 --
3561 hr_utility.set_location(l_proc, 40);
3562 --
3563 chk_expiry_date
3564 (p_expiry_date => p_rec.expiry_date
3565 ,p_offer_status => p_rec.offer_status
3566 ,p_offer_id => p_rec.offer_id
3567 ,p_offer_postal_service => p_rec.offer_postal_service
3568 ,p_offer_letter_tracking_code => p_rec.offer_letter_tracking_code
3569 ,p_offer_shipping_date => p_rec.offer_shipping_date
3570 ,p_effective_date => p_effective_date
3571 );
3572 --
3573 hr_utility.set_location(l_proc, 45);
3574 --
3575 chk_address_id
3576 (p_address_id => p_rec.address_id
3577 ,p_offer_id => p_rec.offer_id
3578 ,p_object_version_number => p_rec.object_version_number
3579 );
3580 --
3581 hr_utility.set_location(l_proc, 50);
3582 --
3583 chk_template_id
3584 (p_template_id => p_rec.template_id
3585 ,p_offer_id => p_rec.offer_id
3586 ,p_object_version_number => p_rec.object_version_number
3587 ,p_effective_date => p_effective_date
3588 );
3589 --
3590 hr_utility.set_location(l_proc, 60);
3591 --
3592 chk_latest_offer
3593 (p_latest_offer => p_rec.latest_offer
3594 ,p_offer_id => p_rec.offer_id
3595 ,p_offer_status => p_rec.offer_status
3596 ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3597 ,p_effective_date => p_effective_date
3598 ,p_object_version_number => p_rec.object_version_number
3599 );
3600 --
3601 hr_utility.set_location(l_proc, 70);
3602 --
3603 chk_offer_status
3604 (p_offer_id => p_rec.offer_id
3605 ,p_offer_status => p_rec.offer_status
3606 ,p_effective_date => p_effective_date
3607 ,p_object_version_number => p_rec.object_version_number
3608 );
3609 --
3610 hr_utility.set_location(l_proc, 80);
3611 --
3612 chk_offer_status_update
3613 (p_current_offer_record => p_rec
3614 );
3615 --
3616 hr_utility.set_location(l_proc, 90);
3617 --
3618 chk_offer_extended_method
3619 (p_offer_id => p_rec.offer_id
3620 ,p_offer_extended_method => p_rec.offer_extended_method
3621 ,p_effective_date => p_effective_date
3622 ,p_object_version_number => p_rec.object_version_number
3623 );
3624 --
3625 hr_utility.set_location(l_proc, 100);
3626 --
3627 chk_offer_postal_service
3628 (p_offer_id => p_rec.offer_id
3629 ,p_offer_postal_service => p_rec.offer_postal_service
3630 ,p_effective_date => p_effective_date
3631 ,p_object_version_number => p_rec.object_version_number
3632 );
3633 --
3634 hr_utility.set_location(l_proc, 110);
3635 --
3636 chk_offer_letter
3637 (p_offer_id => p_rec.offer_id
3638 ,p_offer_status => p_rec.offer_status
3639 );
3640 --
3641 hr_utility.set_location(l_proc, 120);
3642 --
3643 chk_offer_letter_file_type
3644 (p_offer_id => p_rec.offer_id
3645 ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3646 ,p_effective_date => p_effective_date
3647 ,p_object_version_number => p_rec.object_version_number
3648 );
3649 --
3650 hr_utility.set_location(l_proc, 130);
3651 --
3652 irc_iof_bus.chk_df(p_rec);
3653 --
3654 hr_utility.set_location(' Leaving:'||l_proc, 140);
3655 End update_validate;
3656 --
3657 -- ----------------------------------------------------------------------------
3658 -- |---------------------------< delete_validate >----------------------------|
3659 -- ----------------------------------------------------------------------------
3660 Procedure delete_validate
3661 (p_rec in irc_iof_shd.g_rec_type
3662 ) is
3663 --
3664 l_proc varchar2(72) := g_package||'delete_validate';
3665 --
3666 Begin
3667 hr_utility.set_location('Entering:'||l_proc, 5);
3668 --
3669 -- Call all supporting business operations
3670 --
3671 hr_utility.set_location(' Leaving:'||l_proc, 10);
3672 End delete_validate;
3673 --
3674 end irc_iof_bus;