[Home] [Help]
PACKAGE BODY: APPS.IRC_INP_BUS
Source
1 Package Body irc_inp_bus as
2 /* $Header: irinprhi.pkb 120.2.12020000.3 2013/02/14 06:09:16 nitnaras ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_inp_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_party_id number default null;
15 --
16 --
17 -- ----------------------------------------------------------------------------
18 -- |------------------------------< chk_df >----------------------------------|
19 -- ----------------------------------------------------------------------------
20 --
21 -- Description:
22 -- Validates all the Descriptive Flexfield values.
23 --
24 -- Prerequisites:
25 -- All other columns have been validated. Must be called as the
26 -- last step from insert_validate and update_validate.
27 --
28 -- In Arguments:
29 -- p_rec
30 --
31 -- Post Success:
32 -- If the Descriptive Flexfield structure column and data values are
33 -- all valid this procedure will end normally and processing will
34 -- continue.
35 --
36 -- Post Failure:
37 -- If the Descriptive Flexfield structure column value or any of
38 -- the data values are invalid then an application error is raised as
39 -- a PL/SQL exception.
40 --
41 -- Access Status:
42 -- Internal Row Handler Use Only.
43 --
44 -- ----------------------------------------------------------------------------
45 procedure chk_df
46 (p_rec in irc_inp_shd.g_rec_type
47 ) is
48 --
49 l_proc varchar2(72) := g_package || 'chk_df';
50 --
51 begin
52 hr_utility.set_location('Entering:'||l_proc,10);
53 --
54 if ((p_rec.notification_preference_id is not null) and (
55 nvl(irc_inp_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
56 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
57 nvl(irc_inp_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
58 nvl(p_rec.attribute1, hr_api.g_varchar2) or
62 nvl(p_rec.attribute3, hr_api.g_varchar2) or
59 nvl(irc_inp_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
60 nvl(p_rec.attribute2, hr_api.g_varchar2) or
61 nvl(irc_inp_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
63 nvl(irc_inp_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
64 nvl(p_rec.attribute4, hr_api.g_varchar2) or
65 nvl(irc_inp_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
66 nvl(p_rec.attribute5, hr_api.g_varchar2) or
67 nvl(irc_inp_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
68 nvl(p_rec.attribute6, hr_api.g_varchar2) or
69 nvl(irc_inp_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
70 nvl(p_rec.attribute7, hr_api.g_varchar2) or
71 nvl(irc_inp_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
72 nvl(p_rec.attribute8, hr_api.g_varchar2) or
73 nvl(irc_inp_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
74 nvl(p_rec.attribute9, hr_api.g_varchar2) or
75 nvl(irc_inp_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
76 nvl(p_rec.attribute10, hr_api.g_varchar2) or
77 nvl(irc_inp_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
78 nvl(p_rec.attribute11, hr_api.g_varchar2) or
79 nvl(irc_inp_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
80 nvl(p_rec.attribute12, hr_api.g_varchar2) or
81 nvl(irc_inp_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
82 nvl(p_rec.attribute13, hr_api.g_varchar2) or
83 nvl(irc_inp_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
84 nvl(p_rec.attribute14, hr_api.g_varchar2) or
85 nvl(irc_inp_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
86 nvl(p_rec.attribute15, hr_api.g_varchar2) or
87 nvl(irc_inp_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
88 nvl(p_rec.attribute16, hr_api.g_varchar2) or
89 nvl(irc_inp_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
90 nvl(p_rec.attribute17, hr_api.g_varchar2) or
91 nvl(irc_inp_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
92 nvl(p_rec.attribute18, hr_api.g_varchar2) or
93 nvl(irc_inp_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
94 nvl(p_rec.attribute19, hr_api.g_varchar2) or
95 nvl(irc_inp_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
96 nvl(p_rec.attribute20, hr_api.g_varchar2) or
97 nvl(irc_inp_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
98 nvl(p_rec.attribute21, hr_api.g_varchar2) or
99 nvl(irc_inp_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
100 nvl(p_rec.attribute22, hr_api.g_varchar2) or
101 nvl(irc_inp_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
102 nvl(p_rec.attribute23, hr_api.g_varchar2) or
103 nvl(irc_inp_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
104 nvl(p_rec.attribute24, hr_api.g_varchar2) or
105 nvl(irc_inp_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
106 nvl(p_rec.attribute25, hr_api.g_varchar2) or
107 nvl(irc_inp_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
108 nvl(p_rec.attribute26, hr_api.g_varchar2) or
109 nvl(irc_inp_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
110 nvl(p_rec.attribute27, hr_api.g_varchar2) or
111 nvl(irc_inp_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
112 nvl(p_rec.attribute28, hr_api.g_varchar2) or
113 nvl(irc_inp_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
114 nvl(p_rec.attribute29, hr_api.g_varchar2) or
115 nvl(irc_inp_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
116 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
117 or (p_rec.notification_preference_id is null) then
118 --
119 -- Only execute the validation if absolutely necessary:
120 -- a) During update, the structure column value or any
121 -- of the attribute values have actually changed.
122 -- b) During insert.
123 --
124 hr_dflex_utility.ins_or_upd_descflex_attribs
125 (p_appl_short_name => 'PER'
126 ,p_descflex_name => 'IRC_NOTIFICATION_PREFERENCES'
127 ,p_attribute_category => p_rec.attribute_category
128 ,p_attribute1_name => 'ATTRIBUTE1'
129 ,p_attribute1_value => p_rec.attribute1
130 ,p_attribute2_name => 'ATTRIBUTE2'
131 ,p_attribute2_value => p_rec.attribute2
132 ,p_attribute3_name => 'ATTRIBUTE3'
133 ,p_attribute3_value => p_rec.attribute3
134 ,p_attribute4_name => 'ATTRIBUTE4'
135 ,p_attribute4_value => p_rec.attribute4
136 ,p_attribute5_name => 'ATTRIBUTE5'
137 ,p_attribute5_value => p_rec.attribute5
138 ,p_attribute6_name => 'ATTRIBUTE6'
139 ,p_attribute6_value => p_rec.attribute6
140 ,p_attribute7_name => 'ATTRIBUTE7'
141 ,p_attribute7_value => p_rec.attribute7
142 ,p_attribute8_name => 'ATTRIBUTE8'
143 ,p_attribute8_value => p_rec.attribute8
144 ,p_attribute9_name => 'ATTRIBUTE9'
145 ,p_attribute9_value => p_rec.attribute9
146 ,p_attribute10_name => 'ATTRIBUTE10'
147 ,p_attribute10_value => p_rec.attribute10
148 ,p_attribute11_name => 'ATTRIBUTE11'
149 ,p_attribute11_value => p_rec.attribute11
150 ,p_attribute12_name => 'ATTRIBUTE12'
151 ,p_attribute12_value => p_rec.attribute12
152 ,p_attribute13_name => 'ATTRIBUTE13'
153 ,p_attribute13_value => p_rec.attribute13
154 ,p_attribute14_name => 'ATTRIBUTE14'
155 ,p_attribute14_value => p_rec.attribute14
156 ,p_attribute15_name => 'ATTRIBUTE15'
157 ,p_attribute15_value => p_rec.attribute15
158 ,p_attribute16_name => 'ATTRIBUTE16'
159 ,p_attribute16_value => p_rec.attribute16
160 ,p_attribute17_name => 'ATTRIBUTE17'
164 ,p_attribute19_name => 'ATTRIBUTE19'
161 ,p_attribute17_value => p_rec.attribute17
162 ,p_attribute18_name => 'ATTRIBUTE18'
163 ,p_attribute18_value => p_rec.attribute18
165 ,p_attribute19_value => p_rec.attribute19
166 ,p_attribute20_name => 'ATTRIBUTE20'
167 ,p_attribute20_value => p_rec.attribute20
168 ,p_attribute21_name => 'ATTRIBUTE21'
169 ,p_attribute21_value => p_rec.attribute21
170 ,p_attribute22_name => 'ATTRIBUTE22'
171 ,p_attribute22_value => p_rec.attribute22
172 ,p_attribute23_name => 'ATTRIBUTE23'
173 ,p_attribute23_value => p_rec.attribute23
174 ,p_attribute24_name => 'ATTRIBUTE24'
175 ,p_attribute24_value => p_rec.attribute24
176 ,p_attribute25_name => 'ATTRIBUTE25'
177 ,p_attribute25_value => p_rec.attribute25
178 ,p_attribute26_name => 'ATTRIBUTE26'
179 ,p_attribute26_value => p_rec.attribute26
180 ,p_attribute27_name => 'ATTRIBUTE27'
181 ,p_attribute27_value => p_rec.attribute27
182 ,p_attribute28_name => 'ATTRIBUTE28'
183 ,p_attribute28_value => p_rec.attribute28
184 ,p_attribute29_name => 'ATTRIBUTE29'
185 ,p_attribute29_value => p_rec.attribute29
186 ,p_attribute30_name => 'ATTRIBUTE30'
187 ,p_attribute30_value => p_rec.attribute30
188 );
189 end if;
190 --
191 hr_utility.set_location(' Leaving:'||l_proc,20);
192 end chk_df;
193 --
194 -- ----------------------------------------------------------------------------
195 -- |-----------------------< chk_non_updateable_args >------------------------|
196 -- ----------------------------------------------------------------------------
197 -- {Start Of Comments}
198 --
199 -- Description:
200 -- This procedure is used to ensure that non updateable attributes have
201 -- not been updated. If an attribute has been updated an error is generated.
202 --
203 -- Pre Conditions:
204 -- g_old_rec has been populated with details of the values currently in
205 -- the database.
206 --
207 -- In Arguments:
208 -- p_rec has been populated with the updated values the user would like the
209 -- record set to.
210 --
211 -- Post Success:
212 -- Processing continues if all the non updateable attributes have not
213 -- changed.
214 --
215 -- Post Failure:
216 -- An application error is raised if any of the non updatable attributes
217 -- have been altered.
218 --
219 -- {End Of Comments}
220 -- ----------------------------------------------------------------------------
221 Procedure chk_non_updateable_args
222 (p_effective_date in date
223 ,p_rec in irc_inp_shd.g_rec_type
224 ) IS
225 --
226 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
227 --
228 Begin
229 --
230 -- Only proceed with the validation if a row exists for the current
231 -- record in the HR Schema.
232 --
233 IF NOT irc_inp_shd.api_updating
234 (p_notification_preference_id => p_rec.notification_preference_id
235 ,p_object_version_number => p_rec.object_version_number
236 ) THEN
237 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
238 fnd_message.set_token('PROCEDURE ', l_proc);
239 fnd_message.set_token('STEP ', '5');
240 fnd_message.raise_error;
241 END IF;
242 --
243 if nvl(p_rec.notification_preference_id, hr_api.g_number) <>
244 nvl(irc_inp_shd.g_old_rec.notification_preference_id, hr_api.g_number)
245 then
246 hr_api.argument_changed_error
247 (p_api_name => l_proc
248 ,p_argument => 'NOTIFICATION_PREFERENCE_ID'
249 ,p_base_table => irc_inp_shd.g_tab_nam
250 );
251 end if;
252 End chk_non_updateable_args;
253 --
254 -- ----------------------------------------------------------------------------
255 -- |---------------------------< chk_person_id >-------------------------------|
256 -- ----------------------------------------------------------------------------
257 --
258 -- Description:
259 -- Validates if the person id exists in the PER_ALL_PEOPLE_F table
260 --
261 -- Prerequisites:
262 -- Must be called as the first step in insert_validate.
263 --
264 -- In Arguments:
265 -- p_person_id
266 --
267 -- Post Success:
268 -- If the person_id is existing in PER_ALL_PEOPLE_F
269 -- then continue.
270 --
271 -- Post Failure:
272 -- If the person_id is not present in PER_ALL_PEOPLE_F
273 -- then throw an error indicating the same.
274 --
275 -- Access Status:
276 -- Internal Row Handler Use Only.
277 --
278 -- ----------------------------------------------------------------------------
279 Procedure chk_person_id
280 (p_person_id in irc_notification_preferences.person_id%type
281 ,p_party_id in out nocopy irc_notification_preferences.party_id%type
282 ,p_effective_date in date
283 ) is
284 --
285 l_proc varchar2(72) := g_package||'chk_person_id';
286 l_party_id irc_notification_preferences.party_id%type;
287 cursor l_person is
288 select party_id
289 from per_all_people_f
290 where person_id = p_person_id
291 and p_effective_date between
295 --
292 effective_start_date and effective_end_date;
293 --
294 --
296 Begin
297 --
298 hr_utility.set_location(' Entering:'||l_proc,10);
299 --
300 open l_person;
301 fetch l_person into l_party_id;
302 if l_person%notfound
303 then
304 close l_person;
305 fnd_message.set_name('PER','IRC_412157_PARTY_PERS_MISMTCH');
306 fnd_message.raise_error;
307 end if;
308 close l_person;
309 if p_party_id is not null then
310 if p_party_id<>l_party_id then
311 fnd_message.set_name('PER','IRC_412033_RTM_INV_PARTY_ID');
312 fnd_message.raise_error;
313 end if;
314 else
315 p_party_id:=l_party_id;
316 end if;
317 --
318 hr_utility.set_location(l_proc,30);
319 --
320 --
321 hr_utility.set_location(' Leaving:'||l_proc,50);
322 --
323 exception
324 when app_exception.application_exception then
325 if hr_multi_message.exception_add
326 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.PERSON_ID'
327 ) then
328 --
329 hr_utility.set_location(' Leaving:'||l_proc,60);
330 --
331 raise;
332 end if;
333 --
334 hr_utility.set_location(' Leaving:'||l_proc,70);
335 --
336 End chk_person_id;
337 --
338 --
339 -- ----------------------------------------------------------------------------
340 -- |---------------------------< chk_party_id >-------------------------------|
341 -- ----------------------------------------------------------------------------
342 --
343 -- Description:
344 -- Validates if the party id exists in the HZ_PARTIES with party_type = 'PERSON'
345 --
346 -- Prerequisites:
347 -- Must be called as the first step in insert_validate.
348 --
349 -- In Arguments:
350 -- p_party_id
351 --
352 -- Post Success:
353 -- If the party_id is existing in HZ_PARTIES with party_type = 'PERSON'
354 -- then continue.
355 --
356 -- Post Failure:
357 -- If the party_id does not existing in HZ_PARTIES with party_type = 'PERSON'
358 -- then throw an error indicating the same.
359 --
360 -- Access Status:
361 -- Internal Row Handler Use Only.
362 --
363 -- ----------------------------------------------------------------------------
364 Procedure chk_party_id
365 (p_party_id in irc_notification_preferences.party_id%type
366 ) is
367 --
368 l_proc varchar2(72) := g_package||'chk_party_id';
369 --
370 l_party_id irc_notification_preferences.party_id%type;
371 --
372 l_inp_pk varchar2(1);
373 cursor csr_party_id is
374 select 1
375 from hz_parties
376 where party_id = p_party_id
377 and party_type = 'PERSON';
378 --
379 cursor csr_chk_inp_pk is
380 select null
381 from irc_notification_preferences
382 where party_id = p_party_id;
383 --
384 Begin
385 --
386 hr_utility.set_location(' Entering:'||l_proc,10);
387 --
388 hr_api.mandatory_arg_error
389 (p_api_name => l_proc
390 ,p_argument => 'PARTY_ID'
391 ,p_argument_value => p_party_id
392 );
393 --
394 open csr_party_id;
395 fetch csr_party_id into l_party_id;
396 --
397 hr_utility.set_location(l_proc,20);
398 --
399 if csr_party_id%notfound then
400 close csr_party_id;
401 fnd_message.set_name('PER','IRC_412000_BAD_PARTY_PERSON_ID');
402 fnd_message.raise_error;
403 end if;
404 --
405 hr_utility.set_location(l_proc,30);
406 --
407 close csr_party_id;
408 --
409 open csr_chk_inp_pk;
410 fetch csr_chk_inp_pk into l_inp_pk;
411 --
412 hr_utility.set_location(l_proc,40);
413 --
414 if csr_chk_inp_pk%found then
415 close csr_chk_inp_pk;
416 fnd_message.set_name('PER','HR_6123_ALL_UNIQUE_NAME');
417 fnd_message.set_token('INFORMATION_TYPE','PARTY ID');
418 fnd_message.raise_error;
419 end if;
420 --
421 close csr_chk_inp_pk;
422 --
423 hr_utility.set_location(' Leaving:'||l_proc,50);
424 --
425 exception
426 when app_exception.application_exception then
427 if hr_multi_message.exception_add
428 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.PARTY_ID'
429 ) then
430 --
431 hr_utility.set_location(' Leaving:'||l_proc,60);
432 --
433 raise;
434 end if;
435 --
436 hr_utility.set_location(' Leaving:'||l_proc,70);
437 --
438 End chk_party_id;
439 --
440 -- ----------------------------------------------------------------------------
441 -- |---------------------------< chk_address_id >-----------------------------|
442 -- ----------------------------------------------------------------------------
443 --
444 -- Description:
445 -- Validates if the address id exists in PER_ADDRESSES table, if its not null
446 --
447 -- Prerequisites:
448 -- Must be called after the chk_party_id in insert_validate.
449 --
450 -- In Arguments:
451 -- p_party_id
452 -- p_address_id
453 -- p_object_version_number
454 --
455 -- Post Success:
456 -- If the address_id is existing in PER_ADDRESSES then continue.
457 --
458 -- Post Failure:
459 -- If the party_id does not exist in PER_ADDRESSES
460 -- then throw an error indicating the same.
461 --
462 -- Access Status:
466 Procedure chk_address_id
463 -- Internal Row Handler Use Only.
464 --
465 -- ----------------------------------------------------------------------------
467 (p_notification_preference_id
468 in irc_notification_preferences.notification_preference_id%type
469 ,p_address_id
470 in irc_notification_preferences.address_id%type
471 ,p_person_id
472 in irc_notification_preferences.person_id%type
473 ,p_object_version_number
474 in irc_notification_preferences.object_version_number%type
475 ) is
476 --
477 l_proc varchar2(72) := g_package||'chk_address_id';
478 --
479 l_address_id irc_notification_preferences.address_id%type;
480 --
481 l_api_updating boolean;
482 --
483 cursor csr_address_id is
484 select 1
485 from per_addresses
486 where person_id = p_person_id
487 and address_id = p_address_id;
488 --
489 Begin
490 --
491 hr_utility.set_location(' Entering:'||l_proc,10);
492 --
493 -- Only proceed with person_id, address_id validation when
494 -- multi message list does not already contain an error with
495 -- person_id
496 --
497 if hr_multi_message.no_exclusive_error
498 (p_check_column1 => 'IRC_NOTIFICATION_PREFERENCES.PERSON_ID'
499 ,p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.PERSON_ID'
500 ) then
501 if p_address_id is not null then
502 --
503 hr_utility.set_location(l_proc,20);
504 --
505 l_api_updating := irc_inp_shd.api_updating
506 (p_notification_preference_id => p_notification_preference_id
507 ,p_object_version_number => p_object_version_number);
508 --
509 hr_utility.set_location(l_proc,30);
510 --
511 if(l_api_updating and nvl(p_address_id, hr_api.g_number)
512 <> nvl(irc_inp_shd.g_old_rec.address_id, hr_api.g_number))
513 or (not l_api_updating) then
514 --
515 hr_utility.set_location(l_proc,40);
516 --
517 open csr_address_id;
518 fetch csr_address_id into l_address_id;
519 --
520 hr_utility.set_location(l_proc,50);
521 --
522 if csr_address_id%notfound then
523 close csr_address_id;
524 fnd_message.set_name('PER','IRC_412001_BAD_ADDRESS_ID');
525 fnd_message.raise_error;
526 end if;
527 --
528 hr_utility.set_location(l_proc,60);
529 --
530 close csr_address_id;
531 --
532 end if;
533 --
534 end if;
535 --
536 end if;
537 --
538 hr_utility.set_location(' Leaving:'||l_proc,70);
539 --
540 exception
541 when app_exception.application_exception then
542 if hr_multi_message.exception_add
543 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.ADDRESS_ID'
544 ) then
545 --
546 hr_utility.set_location(' Leaving:'||l_proc,80);
547 --
548 raise;
549 end if;
550 --
551 hr_utility.set_location(' Leaving:'||l_proc,90);
552 --
553 End chk_address_id;
554 --
555 -- ----------------------------------------------------------------------------
556 -- |-------------------------< chk_matching_jobs >----------------------------|
557 -- ----------------------------------------------------------------------------
558 --
559 -- Description:
560 -- Validates if the matching_jobs has a value of 'Y' or 'N'
561 --
562 -- Prerequisites:
563 -- Must be called in insert_validate.
564 --
565 -- In Arguments:
566 -- p_matching_jobs
567 -- p_party_id
568 -- p_object_version_number
569 --
570 -- Post Success:
571 -- If p_matching_jobs has a value of 'Y' or 'N' then continue.
572 --
573 -- Post Failure:
574 -- If p_mathcing_jobs has any other value other than 'Y' or 'N'
575 -- then throw an error indicating the same.
576 --
577 -- Access Status:
578 -- Internal Row Handler Use Only.
579 --
580 -- ----------------------------------------------------------------------------
581 Procedure chk_matching_jobs
582 (p_matching_jobs
583 in irc_notification_preferences.matching_jobs%type
584 ,p_notification_preference_id
585 in irc_notification_preferences.notification_preference_id%type
586 ,p_object_version_number
587 in irc_notification_preferences.object_version_number%type
588 ) is
589 --
590 l_proc varchar2(72) := g_package||'chk_matching_jobs';
591 --
592 l_api_updating boolean;
593 --
594 Begin
595 --
596 hr_utility.set_location(' Entering:'||l_proc,10);
597 --
598 hr_api.mandatory_arg_error
599 (p_api_name => l_proc
600 ,p_argument => 'MATCHING_JOBS'
601 ,p_argument_value => p_matching_jobs
602 );
603 --
604 l_api_updating := irc_inp_shd.api_updating
605 (p_notification_preference_id => p_notification_preference_id
606 ,p_object_version_number => p_object_version_number);
607 --
608 hr_utility.set_location(l_proc,20);
609 --
610 if (l_api_updating
611 and nvl(p_matching_jobs, hr_api.g_varchar2)
612 <> nvl(irc_inp_shd.g_old_rec.matching_jobs, hr_api.g_varchar2))
613 or (not l_api_updating) then
614 --
615 hr_utility.set_location(l_proc,30);
616 --
617 if (p_matching_jobs not in ('Y','N')) then
618 fnd_message.set_name('PER','IRC_412002_BAD_MATCHING_JOBS');
619 fnd_message.raise_error;
620 end if;
621 end if;
622 --
623 hr_utility.set_location(' Leaving:'||l_proc,40);
624 --
625 exception
629 ) then
626 when app_exception.application_exception then
627 if hr_multi_message.exception_add
628 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.MATCHING_JOBS'
630 --
631 hr_utility.set_location(' Leaving:'||l_proc,50);
632 --
633 raise;
634 end if;
635 --
636 hr_utility.set_location(' Leaving:'||l_proc,60);
637 --
638 End chk_matching_jobs;
639 --
640 -- ----------------------------------------------------------------------------
641 -- |-----------------------< chk_matching_job_freq >-------------------------|
642 -- ----------------------------------------------------------------------------
643 --
644 -- Description:
645 -- Validates the matching_job_freq against LOOKUP_TYPE = 'IRC_MESSAGE_FREQ'
646 --
647 -- Prerequisites:
648 -- Must be called in insert_validate.
649 --
650 -- In Arguments:
651 -- p_matching_job_freq
652 -- p_effective_date
653 -- p_party_id
654 -- p_object_version_number
655 --
656 -- Post Success:
657 -- If p_matching_job_freq exists for the LOOKUP_TYPE = 'IRC_MESSAGE_FREQ'
658 -- then continue.
659 --
660 -- Post Failure:
661 -- If p_mathcing_job_freq doesnt exist for the LOOKUP_TYPE =
662 -- 'IRC_MESSAGE_FREQ' then throw an error indicating the same.
663 --
664 -- Access Status:
665 -- Internal Row Handler Use Only.
666 --
667 -- ----------------------------------------------------------------------------
668 Procedure chk_matching_job_freq
669 (p_matching_job_freq
670 in irc_notification_preferences.matching_job_freq%type
671 ,p_notification_preference_id
672 in irc_notification_preferences.notification_preference_id%type
673 ,p_effective_date
674 in date
675 ,p_object_version_number
676 in irc_notification_preferences.object_version_number%type
677 ) is
678 --
679 l_proc varchar2(72) := g_package||'chk_matching_job_freq';
680 --
681 l_api_updating boolean;
682 --
683 Begin
684 --
685 hr_utility.set_location(' Entering:'||l_proc,10);
686 --
687 hr_api.mandatory_arg_error
688 (p_api_name => l_proc
689 ,p_argument => 'MATCHING_JOB_FREQ'
690 ,p_argument_value => p_matching_job_freq
691 );
692 --
693 l_api_updating := irc_inp_shd.api_updating
694 (p_notification_preference_id => p_notification_preference_id
695 ,p_object_version_number => p_object_version_number);
696 --
697 hr_utility.set_location(l_proc,20);
698 --
699 if (l_api_updating
700 and nvl(p_matching_job_freq, hr_api.g_varchar2)
701 <> nvl(irc_inp_shd.g_old_rec.matching_job_freq, hr_api.g_varchar2))
702 or (not l_api_updating) then
703 --
704 hr_utility.set_location(l_proc,30);
705 --
706 if hr_api.not_exists_in_hr_lookups
707 ( p_effective_date => p_effective_date
708 , p_lookup_type => 'IRC_MESSAGE_FREQ'
709 , p_lookup_code => p_matching_job_freq) then
710 fnd_message.set_name('PER','IRC_412003_BAD_MATCH_JOB_FREQ');
711 fnd_message.raise_error;
712 end if;
713 end if;
714 --
715 hr_utility.set_location(' Leaving:'||l_proc,40);
716 --
717 exception
718 when app_exception.application_exception then
719 if hr_multi_message.exception_add
720 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.MATCHING_JOB_FREQ'
721 ) then
722 --
723 hr_utility.set_location(' Leaving:'||l_proc,50);
724 --
725 raise;
726 end if;
727 --
728 hr_utility.set_location(' Leaving:'||l_proc,60);
729 --
730 End chk_matching_job_freq;
731 --
732 -- ----------------------------------------------------------------------------
733 -- |-----------------------< chk_receive_info_mail >--------------------------|
734 -- ----------------------------------------------------------------------------
735 --
736 -- Description:
737 -- Validates that the p_receive_info_mail is either 'Y' or 'N'
738 --
739 -- Prerequisites:
740 -- Must be called in insert_validate.
741 --
742 -- In Arguments:
743 -- p_receive_info_mail
744 -- p_party_id
745 -- p_object_version_number
746 --
747 -- Post Success:
748 -- If p_receive_info_mail is either 'Y' or 'N' then continue.
749 --
750 -- Post Failure:
751 -- If p_receive_info_mail is not 'Y' or 'N'
752 -- then throw an error indicating the same.
753 --
754 -- Access Status:
755 -- Internal Row Handler Use Only.
756 --
757 -- ----------------------------------------------------------------------------
758 Procedure chk_receive_info_mail
759 (p_receive_info_mail
760 in irc_notification_preferences.receive_info_mail%type
761 ,p_notification_preference_id
762 in irc_notification_preferences.notification_preference_id%type
763 ,p_object_version_number
764 in irc_notification_preferences.object_version_number%type
765 ) is
766 --
767 l_proc varchar2(72) := g_package||'chk_receive_info_mail';
768 --
769 l_api_updating boolean;
770 --
771 Begin
772 --
773 hr_utility.set_location(' Entering:'||l_proc,10);
774 --
775 hr_api.mandatory_arg_error
776 (p_api_name => l_proc
777 ,p_argument => 'RECEIVE_INFO_MAIL'
778 ,p_argument_value => p_receive_info_mail
779 );
780 --
781 l_api_updating := irc_inp_shd.api_updating
782 (p_notification_preference_id => p_notification_preference_id
783 ,p_object_version_number => p_object_version_number);
784 --
785 hr_utility.set_location(l_proc,20);
786 --
790 or (not l_api_updating) then
787 if (l_api_updating
788 and nvl(p_receive_info_mail, hr_api.g_varchar2)
789 <> nvl(irc_inp_shd.g_old_rec.receive_info_mail, hr_api.g_varchar2))
791 --
792 hr_utility.set_location(l_proc,30);
793 --
794 if p_receive_info_mail not in ('Y','N') then
795 fnd_message.set_name('PER','IRC_412004_BAD_REC_INFO_MAIL');
796 fnd_message.raise_error;
797 end if;
798 end if;
799 --
800 hr_utility.set_location(' Leaving:'||l_proc,40);
801 --
802 exception
803 when app_exception.application_exception then
804 if hr_multi_message.exception_add
805 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.RECEIVE_INFO_MAIL'
806 ) then
807 --
808 hr_utility.set_location(' Leaving:'||l_proc,50);
809 --
810 raise;
811 end if;
812 --
813 hr_utility.set_location(' Leaving:'||l_proc,60);
814 --
815 End chk_receive_info_mail;
816 --
817 -- ----------------------------------------------------------------------------
818 -- |----------------------------< chk_allow_access >--------------------------|
819 -- ----------------------------------------------------------------------------
820 --
821 -- Description:
822 -- Validates that the p_allow_access is either 'Y' or 'N'
823 --
824 -- Prerequisites:
825 -- Must be called in insert_validate.
826 --
827 -- In Arguments:
828 -- p_allow_access
829 -- p_party_id
830 -- p_object_version_number
831 --
832 -- Post Success:
833 -- If p_allow_access is either 'Y' or 'N' then continue.
834 --
835 -- Post Failure:
836 -- If p_allow_access is not 'Y' or 'N'
837 -- then throw an error indicating the same.
838 --
839 -- Access Status:
840 -- Internal Row Handler Use Only.
841 --
842 -- ----------------------------------------------------------------------------
843 Procedure chk_allow_access
844 (p_allow_access
845 in irc_notification_preferences.allow_access%type
846 ,p_notification_preference_id
847 in irc_notification_preferences.notification_preference_id%type
848 ,p_object_version_number
849 in irc_notification_preferences.object_version_number%type
850 ) is
851 --
852 l_proc varchar2(72) := g_package||'chk_allow_access';
853 --
854 l_api_updating boolean;
855 --
856 Begin
857 --
858 hr_utility.set_location(' Entering:'||l_proc,10);
859 --
860 hr_api.mandatory_arg_error
861 (p_api_name => l_proc
862 ,p_argument => 'ALLOW_ACCESS'
863 ,p_argument_value => p_allow_access
864 );
865 --
866 l_api_updating := irc_inp_shd.api_updating
867 (p_notification_preference_id => p_notification_preference_id
868 ,p_object_version_number => p_object_version_number);
869 --
870 hr_utility.set_location(l_proc,20);
871 --
872 if (l_api_updating
873 and nvl(p_allow_access, hr_api.g_varchar2)
874 <> nvl(irc_inp_shd.g_old_rec.allow_access, hr_api.g_varchar2))
875 or (not l_api_updating) then
876 if p_allow_access not in ('Y','N') then
877 fnd_message.set_name('PER','IRC_412005_BAD_ALLOW_ACCESS');
878 fnd_message.raise_error;
879 end if;
880 end if;
881 --
882 hr_utility.set_location(' Leaving:'||l_proc,30);
883 --
884 exception
885 when app_exception.application_exception then
886 if hr_multi_message.exception_add
887 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.ALLOW_ACCESS'
888 ) then
889 --
890 hr_utility.set_location(' Leaving:'||l_proc,40);
891 --
892 raise;
893 end if;
894 --
895 hr_utility.set_location(' Leaving:'||l_proc,50);
896 --
897 End chk_allow_access;
898 --
899 -- ----------------------------------------------------------------------------
900 -- |---------------------------< chk_agency_id >-------------------------------|
901 -- ----------------------------------------------------------------------------
902 --
903 -- Description:
904 -- Validates if the agency id exists in the PO_VENDORS with
905 -- vendor_type_lookup_code = 'IRC_JOB_AGENCY'
906 --
907 -- Prerequisites:
908 -- Must be called as the first step in insert_validate.
909 --
910 -- In Arguments:
911 -- p_agency_id
912 --
913 -- Post Success:
914 -- If the agency_id is existing in PO_VENDORS with
915 -- vendor_type_lookup_code = 'IRC_JOB_AGENCY', then continue.
916 --
917 -- Post Failure:
918 -- If the agency_id does not existing in PO_VENDORS with vendor_type_lookup_code =
919 -- 'IRC_JOB_AGENCY' then throw an error indicating the same.
920 --
921 -- Access Status:
922 -- Internal Row Handler Use Only.
923 --
924 -- ----------------------------------------------------------------------------
925 Procedure chk_agency_id
926 (p_agency_id
927 in irc_notification_preferences.agency_id%type
928 ) is
929 --
930 l_proc varchar2(72) := g_package||'chk_agency_id';
931 l_agency_id irc_notification_preferences.agency_id%type;
932 cursor csr_agency_id is
933 select 1
934 from po_vendors
935 where vendor_id = p_agency_id
936 and vendor_type_lookup_code = 'IRC_JOB_AGENCY';
937 --
938 Begin
939 --
940 hr_utility.set_location(' Entering:'||l_proc,10);
941 --
942 open csr_agency_id;
943 fetch csr_agency_id into l_agency_id;
944 --
945 hr_utility.set_location(l_proc,20);
946
947 if csr_agency_id%notfound then
948 close csr_agency_id;
949 fnd_message.set_name('PER','IRC_BAD_AGENCY_ID');
953 hr_utility.set_location(l_proc,30);
950 fnd_message.raise_error;
951 end if;
952 --
954 --
955 close csr_agency_id;
956 --
957 hr_utility.set_location(' Leaving:'||l_proc,50);
958 --
959 exception
960 when app_exception.application_exception then
961 if hr_multi_message.exception_add
962 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.AGENCY_ID'
963 ) then
964 --
965 hr_utility.set_location(' Leaving:'||l_proc,60);
966 --
967 raise;
968 end if;
969 --
970 hr_utility.set_location(' Leaving:'||l_proc,70);
971 --
972 End chk_agency_id;
973 --
974 -- ----------------------------------------------------------------------------
975 -- |---------------------------< chk_attempt_id>------------------------------|
976 -- ----------------------------------------------------------------------------
977 --
978 -- Description:
979 -- Validates if the attempt id exists in OTA_ATTEMPTS
980 --
981 -- Prerequisites:
982 -- Must be called as the first step in insert_validate.
983 --
984 -- In Arguments:
985 -- p_attempt_id
986 --
987 -- Post Success:
988 -- If attempt_id exists in OTA_ATTEMPTS, then continue.
989 --
990 -- Post Failure:
991 -- If the attempt_id does not exists in OTA_ATTEMPTS, then
992 -- throw an error indicating the same.
993 --
994 -- Access Status:
995 -- Internal Row Handler Use Only.
996 --
997 -- ----------------------------------------------------------------------------
998 Procedure chk_attempt_id
999 (p_attempt_id in
1000 irc_notification_preferences.attempt_id%type
1001 ,p_notification_preference_id in
1002 irc_notification_preferences.notification_preference_id%type
1003 ,p_object_version_number in
1004 irc_notification_preferences.object_version_number%type
1005 ) is
1006 --
1007 l_proc varchar2(72) := g_package||'chk_attempt_id';
1008 l_attempt_exists number;
1009 l_api_updating boolean;
1010 --
1011 cursor csr_attempt_exists is
1012 select 1
1013 from ota_attempts
1014 where attempt_id = p_attempt_id;
1015 --
1016 Begin
1017 --
1018 hr_utility.set_location(' Entering:'||l_proc,10);
1019 --
1020 l_api_updating := irc_inp_shd.api_updating
1021 (p_notification_preference_id => p_notification_preference_id
1022 ,p_object_version_number => p_object_version_number
1023 );
1024 --
1025 hr_utility.set_location(l_proc, 20);
1026 if ((l_api_updating and
1027 nvl(irc_inp_shd.g_old_rec.attempt_id, hr_api.g_number) <>
1028 nvl(p_attempt_id, hr_api.g_number))
1029 or
1030 (NOT l_api_updating)) then
1031 --
1032 hr_utility.set_location(l_proc, 30);
1033 --
1034 -- Check if attempt_id is not null
1035 --
1036 if p_attempt_id IS NOT NULL then
1037 --
1038 -- attempt_id must exist in ota_attempts
1039 --
1040 open csr_attempt_exists;
1041 fetch csr_attempt_exists into l_attempt_exists;
1042 --
1043 hr_utility.set_location(l_proc,40);
1044 --
1045 if csr_attempt_exists%notfound then
1046 close csr_attempt_exists;
1047 hr_utility.set_location(l_proc,50);
1048 fnd_message.set_name('PER','IRC_412233_INV_OTA_ATTEMPT');
1049 fnd_message.raise_error;
1050 else
1051 close csr_attempt_exists;
1052 end if;
1053 --
1054 end if;
1055 end if;
1056 --
1057 hr_utility.set_location(' Leaving:'||l_proc,60);
1058 --
1059 exception
1060 when app_exception.application_exception then
1061 if hr_multi_message.exception_add
1062 (p_associated_column1 => 'IRC_NOTIFICATION_PREFERENCES.ATTEMPT_ID'
1063 ) then
1064 --
1065 hr_utility.set_location(' Leaving:'||l_proc,70);
1066 --
1067 raise;
1068 end if;
1069 --
1070 hr_utility.set_location(' Leaving:'||l_proc,80);
1071 --
1072 End chk_attempt_id;
1073 --
1074 -- ----------------------------------------------------------------------------
1075 -- |---------------------------< insert_validate >----------------------------|
1076 -- ----------------------------------------------------------------------------
1077 Procedure insert_validate
1078 (p_effective_date in date
1079 ,p_rec in out nocopy irc_inp_shd.g_rec_type
1080 ) is
1081 --
1082 l_proc varchar2(72) := g_package||'insert_validate';
1083 --
1084 Begin
1085 hr_utility.set_location('Entering:'||l_proc, 5);
1086 --
1087 -- Call all supporting business operations
1088
1089 --
1090 if(p_rec.party_id is not null) then
1091 chk_party_id(p_rec.party_id);
1092 end if;
1093 --
1094 chk_person_id
1095 (p_person_id =>p_rec.person_id
1096 ,p_party_id => p_rec.party_id
1097 ,p_effective_date=>p_effective_date
1098 );
1099 --
1100 hr_utility.set_location(l_proc, 10);
1101 --
1102 chk_address_id
1103 (p_address_id => p_rec.address_id
1104 ,p_notification_preference_id => p_rec.notification_preference_id
1105 ,p_person_id => p_rec.person_id
1106 ,p_object_version_number => p_rec.object_version_number);
1107 --
1108 hr_utility.set_location(l_proc, 20);
1109 --
1110 chk_matching_jobs
1111 (p_matching_jobs => p_rec.matching_jobs
1112 ,p_notification_preference_id => p_rec.notification_preference_id
1113 ,p_object_version_number => p_rec.object_version_number);
1114 --
1118 (p_matching_job_freq => p_rec.matching_job_freq
1115 hr_utility.set_location(l_proc, 30);
1116 --
1117 chk_matching_job_freq
1119 ,p_effective_date => p_effective_date
1120 ,p_notification_preference_id => p_rec.notification_preference_id
1121 ,p_object_version_number => p_rec.object_version_number);
1122 --
1123 hr_utility.set_location(l_proc, 40);
1124 --
1125 chk_receive_info_mail
1126 (p_receive_info_mail => p_rec.receive_info_mail
1127 ,p_notification_preference_id => p_rec.notification_preference_id
1128 ,p_object_version_number => p_rec.object_version_number);
1129 --
1130 hr_utility.set_location(l_proc, 50);
1131 --
1132 chk_allow_access
1133 (p_allow_access => p_rec.allow_access
1134 ,p_notification_preference_id => p_rec.notification_preference_id
1135 ,p_object_version_number => p_rec.object_version_number);
1136 --
1137 hr_utility.set_location(l_proc, 60);
1138 --
1139 if p_rec.agency_id is not null then
1140 chk_agency_id
1141 (p_agency_id => p_rec.agency_id);
1142 end if;
1143 --
1144 hr_utility.set_location(l_proc, 65);
1145 chk_attempt_id
1146 (p_attempt_id => p_rec.attempt_id
1147 ,p_notification_preference_id => p_rec.notification_preference_id
1148 ,p_object_version_number => p_rec.object_version_number);
1149 --
1150 hr_utility.set_location(l_proc, 70);
1151 --
1152 irc_inp_bus.chk_df(p_rec);
1153 --
1154 hr_utility.set_location(' Leaving:'||l_proc, 80);
1155 End insert_validate;
1156 --
1157 -- ----------------------------------------------------------------------------
1158 -- |---------------------------< update_validate >----------------------------|
1159 -- ----------------------------------------------------------------------------
1160 Procedure update_validate
1161 (p_effective_date in date
1162 ,p_rec in out nocopy irc_inp_shd.g_rec_type
1163 ) is
1164 --
1165 l_proc varchar2(72) := g_package||'update_validate';
1166 --
1167 Begin
1168 hr_utility.set_location('Entering:'||l_proc, 5);
1169 --
1170 -- Call all supporting business operations
1171 --
1172 chk_person_id
1173 (p_person_id =>p_rec.person_id
1174 ,p_party_id => p_rec.party_id
1175 ,p_effective_date=>p_effective_date
1176 );
1177 --
1178 --
1179 hr_utility.set_location('Entering:'||l_proc, 6);
1180 --
1181 chk_address_id
1182 (p_address_id => p_rec.address_id
1183 ,p_notification_preference_id => p_rec.notification_preference_id
1184 ,p_person_id => p_rec.person_id
1185 ,p_object_version_number => p_rec.object_version_number);
1186 --
1187 hr_utility.set_location(l_proc, 10);
1188 --
1189 chk_matching_jobs
1190 (p_matching_jobs => p_rec.matching_jobs
1191 ,p_notification_preference_id => p_rec.notification_preference_id
1192 ,p_object_version_number => p_rec.object_version_number);
1193 --
1194 hr_utility.set_location(l_proc, 20);
1195 --
1196 chk_matching_job_freq
1197 (p_matching_job_freq => p_rec.matching_job_freq
1198 ,p_effective_date => p_effective_date
1199 ,p_notification_preference_id => p_rec.notification_preference_id
1200 ,p_object_version_number => p_rec.object_version_number);
1201 --
1202 hr_utility.set_location(l_proc, 30);
1203 --
1204 chk_receive_info_mail
1205 (p_receive_info_mail => p_rec.receive_info_mail
1206 ,p_notification_preference_id => p_rec.notification_preference_id
1207 ,p_object_version_number => p_rec.object_version_number);
1208 --
1209 hr_utility.set_location(l_proc, 40);
1210 --
1211 chk_allow_access
1212 (p_allow_access => p_rec.allow_access
1213 ,p_notification_preference_id => p_rec.notification_preference_id
1214 ,p_object_version_number => p_rec.object_version_number);
1215 --
1216 hr_utility.set_location(l_proc, 50);
1217 --
1218 if p_rec.agency_id is not null then
1219 chk_agency_id
1220 (p_agency_id => p_rec.agency_id);
1221 end if;
1222 --
1223 hr_utility.set_location(l_proc, 55);
1224 chk_attempt_id
1225 (p_attempt_id => p_rec.attempt_id
1226 ,p_notification_preference_id => p_rec.notification_preference_id
1227 ,p_object_version_number => p_rec.object_version_number);
1228 --
1229 hr_utility.set_location(l_proc, 60);
1230 --
1231 chk_non_updateable_args
1232 (p_effective_date => p_effective_date
1233 ,p_rec => p_rec
1234 );
1235 --
1236 irc_inp_bus.chk_df(p_rec);
1237 --
1238 hr_utility.set_location(' Leaving:'||l_proc, 60);
1239 End update_validate;
1240 --
1241 -- ----------------------------------------------------------------------------
1242 -- |---------------------------< delete_validate >----------------------------|
1243 -- ----------------------------------------------------------------------------
1244 Procedure delete_validate
1245 (p_rec in irc_inp_shd.g_rec_type
1246 ) is
1247 --
1248 l_proc varchar2(72) := g_package||'delete_validate';
1249 --
1250 Begin
1251 hr_utility.set_location('Entering:'||l_proc, 5);
1252 --
1253 -- Call all supporting business operations
1254 --
1255 hr_utility.set_location(' Leaving:'||l_proc, 10);
1256 End delete_validate;
1257 --
1258 end irc_inp_bus;