[Home] [Help]
PACKAGE BODY: APPS.IRC_IDP_BUS
Source
1 Package Body irc_idp_bus as
2 /* $Header: iridprhi.pkb 120.0.12010000.2 2008/08/05 10:49:00 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_idp_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_default_posting_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_idp_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.default_posting_id is not null) and (
55 nvl(irc_idp_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
56 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
57 nvl(irc_idp_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
58 nvl(p_rec.attribute1, hr_api.g_varchar2) or
59 nvl(irc_idp_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
60 nvl(p_rec.attribute2, hr_api.g_varchar2) or
61 nvl(irc_idp_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
62 nvl(p_rec.attribute3, hr_api.g_varchar2) or
63 nvl(irc_idp_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
64 nvl(p_rec.attribute4, hr_api.g_varchar2) or
65 nvl(irc_idp_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
66 nvl(p_rec.attribute5, hr_api.g_varchar2) or
67 nvl(irc_idp_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
68 nvl(p_rec.attribute6, hr_api.g_varchar2) or
69 nvl(irc_idp_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
70 nvl(p_rec.attribute7, hr_api.g_varchar2) or
71 nvl(irc_idp_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
72 nvl(p_rec.attribute8, hr_api.g_varchar2) or
73 nvl(irc_idp_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
74 nvl(p_rec.attribute9, hr_api.g_varchar2) or
75 nvl(irc_idp_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
76 nvl(p_rec.attribute10, hr_api.g_varchar2) or
77 nvl(irc_idp_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
78 nvl(p_rec.attribute11, hr_api.g_varchar2) or
79 nvl(irc_idp_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
80 nvl(p_rec.attribute12, hr_api.g_varchar2) or
81 nvl(irc_idp_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
82 nvl(p_rec.attribute13, hr_api.g_varchar2) or
83 nvl(irc_idp_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
84 nvl(p_rec.attribute14, hr_api.g_varchar2) or
85 nvl(irc_idp_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
86 nvl(p_rec.attribute15, hr_api.g_varchar2) or
87 nvl(irc_idp_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
88 nvl(p_rec.attribute16, hr_api.g_varchar2) or
89 nvl(irc_idp_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
90 nvl(p_rec.attribute17, hr_api.g_varchar2) or
91 nvl(irc_idp_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
92 nvl(p_rec.attribute18, hr_api.g_varchar2) or
93 nvl(irc_idp_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
94 nvl(p_rec.attribute19, hr_api.g_varchar2) or
95 nvl(irc_idp_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
96 nvl(p_rec.attribute20, hr_api.g_varchar2) or
97 nvl(irc_idp_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
98 nvl(p_rec.attribute21, hr_api.g_varchar2) or
99 nvl(irc_idp_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
100 nvl(p_rec.attribute22, hr_api.g_varchar2) or
101 nvl(irc_idp_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
102 nvl(p_rec.attribute23, hr_api.g_varchar2) or
103 nvl(irc_idp_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
104 nvl(p_rec.attribute24, hr_api.g_varchar2) or
105 nvl(irc_idp_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
106 nvl(p_rec.attribute25, hr_api.g_varchar2) or
107 nvl(irc_idp_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
108 nvl(p_rec.attribute26, hr_api.g_varchar2) or
109 nvl(irc_idp_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
110 nvl(p_rec.attribute27, hr_api.g_varchar2) or
111 nvl(irc_idp_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
112 nvl(p_rec.attribute28, hr_api.g_varchar2) or
113 nvl(irc_idp_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
114 nvl(p_rec.attribute29, hr_api.g_varchar2) or
115 nvl(irc_idp_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
116 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
117 or (p_rec.default_posting_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_DEFAULT_POSTINGS'
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'
161 ,p_attribute17_value => p_rec.attribute17
162 ,p_attribute18_name => 'ATTRIBUTE18'
163 ,p_attribute18_value => p_rec.attribute18
164 ,p_attribute19_name => 'ATTRIBUTE19'
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_rec in irc_idp_shd.g_rec_type
223 ) IS
224 --
225 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
226 l_error EXCEPTION;
227 l_argument varchar2(30);
228 --
229 Begin
230 --
231 -- Only proceed with the validation if a row exists for the current
232 -- record in the HR Schema.
233 --
234 IF NOT irc_idp_shd.api_updating
235 (p_default_posting_id => p_rec.default_posting_id
236 ,p_object_version_number => p_rec.object_version_number
237 ) THEN
238 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
239 fnd_message.set_token('PROCEDURE ', l_proc);
240 fnd_message.set_token('STEP ', '5');
241 fnd_message.raise_error;
242 END IF;
243 --
244 EXCEPTION
245 WHEN l_error THEN
246 hr_api.argument_changed_error
247 (p_api_name => l_proc
248 ,p_argument => l_argument);
249 WHEN OTHERS THEN
250 RAISE;
251 End chk_non_updateable_args;
252
253 --
254 -- ---------------------------------------------------------------------------
255 -- |-----------------------------< chk_one_id >-----------------------------|
256 -- ---------------------------------------------------------------------------
257 --
258 -- Description:
259 -- - Validates that only one of organization id, job id and position id is entered.
260 --
261 -- Pre-conditions:
262 -- None.
263 --
264 -- In Arguments:
265 -- p_organization_id
266 -- p_job_id
267 -- p_position_id
268 --
269 -- Post Success:
270 -- If only one of the input parameters is not null then
271 -- processing continues.
272 --
273 -- Post Failure:
274 -- If two or more of the input parameters are not null
275 -- an application error will be raised and processing is terminated.
276 --
277 -- Access Status:
278 -- Internal Table Handler Use Only.
279 --
280 -- {End Of Comments}
281 -- ----------------------------------------------------------------------------
282 procedure chk_one_id
283 (p_job_id in irc_default_postings.job_id%TYPE
284 ,p_organization_id in irc_default_postings.organization_id%TYPE
285 ,p_position_id in irc_default_postings.position_id%TYPE
286 )
287 is
288 --
289 l_proc varchar2(72) := g_package||'chk_one_id';
290 l_flag number;
291 --
292 begin
293 hr_utility.set_location('Entering:'|| l_proc, 10);
294 --
295 l_flag := 0;
296 --
297 if (p_job_id is not null) then
298 l_flag := l_flag + 1;
299 end if;
300 --
301 if (p_position_id is not null) then
302 l_flag := l_flag + 1;
303 end if;
304 --
305 if (p_organization_id is not null) then
306 l_flag := l_flag + 1;
307 end if;
308 --
309 if (l_flag > 1) then
310 hr_utility.set_message(800, 'IRC_412090_TOO_MANY_ARGS');
311 hr_utility.raise_error;
312 end if;
313 --
314 hr_utility.set_location(' Leaving:'|| l_proc, 40);
315 end chk_one_id;
316
317 --
318 -- ---------------------------------------------------------------------------
319 -- |---------------------------< chk_organization_id >--------------------------|
320 -- ---------------------------------------------------------------------------
321 --
322 -- Description:
323 -- - Validates that a organization id exists in table hr_all_organization_units.
324 --
325 -- Pre-conditions:
326 -- None.
327 --
328 -- In Arguments:
329 -- p_organization_id
330 --
331 -- Post Success:
332 -- If a row does exist in hr_all_organization_units for the given organization id then
333 -- processing continues.
334 --
335 -- Post Failure:
336 -- If a row does not exist in hr_all_organization_units for the given organization id then
337 -- an application error will be raised and processing is terminated.
338 --
339 -- Access Status:
340 -- Internal Table Handler Use Only.
341 --
342 -- {End Of Comments}
343 -- ----------------------------------------------------------------------------
344 procedure chk_organization_id
345 (p_organization_id in irc_default_postings.organization_id%TYPE
346 )
347 is
348 --
349 l_proc varchar2(72) := g_package||'chk_organization_id';
350 l_org varchar2(1);
351 cursor csr_org is
352 select null
353 from hr_all_organization_units haou
354 where haou.organization_id = p_organization_id;
355 --
356 --
357 begin
361 open csr_org;
358 hr_utility.set_location('Entering:'|| l_proc, 10);
359 --
360 if p_organization_id is not null or p_organization_id <> hr_api.g_number then
362 fetch csr_org into l_org;
363 if (csr_org%notfound)
364 then
365 close csr_org;
366 hr_utility.set_message(800, 'IRC_412091_ORG_NOT_EXIST');
367 hr_utility.raise_error;
368 end if;
369 close csr_org;
370 end if;
371 --
372 hr_utility.set_location(' Leaving:'|| l_proc, 40);
373 end chk_organization_id;
374
375 --
376 -- ---------------------------------------------------------------------------
377 -- |-----------------------------< chk_job_id >-----------------------------|
378 -- ---------------------------------------------------------------------------
379 --
380 -- Description:
381 -- - Validates that a job id exists in table per_jobs.
382 --
383 -- Pre-conditions:
384 -- None.
385 --
386 -- In Arguments:
387 -- p_job_id
388 --
389 -- Post Success:
390 -- If a row does exist in per_jobs for the given job id then
391 -- processing continues.
392 --
393 -- Post Failure:
394 -- If a row does not exist in per_jobs for the given job id then
395 -- an application error will be raised and processing is terminated.
396 --
397 -- Access Status:
398 -- Internal Table Handler Use Only.
399 --
400 -- {End Of Comments}
401 -- ----------------------------------------------------------------------------
402 procedure chk_job_id
403 (p_job_id in irc_default_postings.job_id%TYPE
404 )
405 is
406 --
407 l_proc varchar2(72) := g_package||'chk_job_id';
408 l_job varchar2(1);
409 cursor csr_job is
410 select null
411 from per_jobs pj
412 where pj.job_id = p_job_id;
413 --
414 begin
415 hr_utility.set_location('Entering:'|| l_proc, 10);
416 --
417 if p_job_id is not null or p_job_id <> hr_api.g_number then
418 open csr_job;
419 fetch csr_job into l_job;
420 --
421 if (csr_job%notfound) then
422 --
423 close csr_job;
424 hr_utility.set_message(800, 'IRC_412037_RTM_INV_JOB_ID');
425 hr_utility.raise_error;
426 --
427 end if;
428 --
429 close csr_job;
430 end if;
431 hr_utility.set_location(' Leaving:'|| l_proc, 40);
432 end chk_job_id;
433
434 --
435 -- ---------------------------------------------------------------------------
436 -- |---------------------------< chk_position_id >--------------------------|
437 -- ---------------------------------------------------------------------------
438 --
439 -- Description:
440 -- - Validates that a position id exists in table hr_all_positions_f.
441 --
442 -- Pre-conditions:
443 -- None.
444 --
445 -- In Arguments:
446 -- p_position_id
447 --
448 -- Post Success:
449 -- If a row does exist in hr_all_positions_f for the given position id then
450 -- processing continues.
451 --
452 -- Post Failure:
453 -- If a row does not exist in hr_all_positions_f for the given position id then
454 -- an application error will be raised and processing is terminated.
455 --
456 -- Access Status:
457 -- Internal Table Handler Use Only.
458 --
459 -- {End Of Comments}
460 -- ----------------------------------------------------------------------------
461 procedure chk_position_id
462 (p_position_id in irc_default_postings.position_id%TYPE
463 )
464 is
465 --
466 l_proc varchar2(72) := g_package||'chk_position_id';
467 l_pos varchar2(1);
468 cursor csr_pos is
469 select null
470 from hr_all_positions_f hapf
471 where hapf.position_id = p_position_id
472 and trunc(sysdate) between hapf.effective_start_date
473 and hapf.effective_end_date;
474 --
475 begin
476 hr_utility.set_location('Entering:'|| l_proc, 10);
477 --
478 if p_position_id is not null and p_position_id <> hr_api.g_number then
479 open csr_pos;
480 fetch csr_pos into l_pos;
481 --
482 if (csr_pos%notfound) then
483 --
484 close csr_pos;
485 hr_utility.set_message(800, 'IRC_412092_POS_NOT_EXIST');
486 hr_utility.raise_error;
487 --
488 end if;
489 --
490 close csr_pos;
491 end if;
492 --
493 hr_utility.set_location(' Leaving:'|| l_proc, 40);
494 end chk_position_id;
495
496 --
497 -- ----------------------------------------------------------------------------
498 -- |---------------------------< insert_validate >----------------------------|
499 -- ----------------------------------------------------------------------------
500 Procedure insert_validate
501 (p_rec in irc_idp_shd.g_rec_type
502 ) is
503 --
504 l_proc varchar2(72) := g_package||'insert_validate';
505 --
506 Begin
507 hr_utility.set_location('Entering:'||l_proc, 5);
508 --
509 -- Call all supporting business operations
510 --
511 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
512 --
513
514 --
515 -- Validate CHK_ONE_ID
516 --
520 ,p_position_id => p_rec.position_id
517 irc_idp_bus.chk_one_id
518 (p_job_id => p_rec.job_id
519 ,p_organization_id => p_rec.organization_id
521 );
522
523 --
524 -- Validate CHK_POSITION_ID
525 --
526 irc_idp_bus.chk_position_id
527 (p_position_id => p_rec.position_id
528 );
529
530 --
531 -- Validate CHK_JOB_ID
532 --
533 irc_idp_bus.chk_job_id
534 (p_job_id => p_rec.job_id
535 );
536
537 --
538 -- Validate CHK_ORGANIZATION_ID
539 --
540 irc_idp_bus.chk_organization_id
541 (p_organization_id => p_rec.organization_id
542 );
543
544
545 --
546 irc_idp_bus.chk_df(p_rec);
547 --
548 hr_utility.set_location(' Leaving:'||l_proc, 10);
549 End insert_validate;
550 --
551 -- ----------------------------------------------------------------------------
552 -- |---------------------------< update_validate >----------------------------|
553 -- ----------------------------------------------------------------------------
554 Procedure update_validate
555 (p_rec in irc_idp_shd.g_rec_type
556 ) is
557 --
558 l_proc varchar2(72) := g_package||'update_validate';
559 --
560 Begin
561 hr_utility.set_location('Entering:'||l_proc, 5);
562 --
563 -- Call all supporting business operations
564 --
565
566 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
567
568 --
569 --
570 chk_non_updateable_args
571 (p_rec => p_rec
572 );
573 --
574 --
575
576 --
577 -- Validate CHK_ONE_ID
578 --
579 irc_idp_bus.chk_one_id
580 (p_job_id => p_rec.job_id
581 ,p_organization_id => p_rec.organization_id
582 ,p_position_id => p_rec.position_id
583 );
584
585 --
586 -- Validate CHK_POSITION_ID
587 --
588 irc_idp_bus.chk_position_id
589 (p_position_id => p_rec.position_id
590 );
591
592 --
593 -- Validate CHK_JOB_ID
594 --
595 irc_idp_bus.chk_job_id
596 (p_job_id => p_rec.job_id
597 );
598
599 --
600 -- Validate CHK_ORGANIZATION_ID
601 --
602 irc_idp_bus.chk_organization_id
603 (p_organization_id => p_rec.organization_id
604 );
605
606 --
607 irc_idp_bus.chk_df(p_rec);
608 --
609 hr_utility.set_location(' Leaving:'||l_proc, 10);
610 End update_validate;
611 --
612 -- ----------------------------------------------------------------------------
613 -- |---------------------------< delete_validate >----------------------------|
614 -- ----------------------------------------------------------------------------
615 Procedure delete_validate
616 (p_rec in irc_idp_shd.g_rec_type
617 ) is
618 --
619 l_proc varchar2(72) := g_package||'delete_validate';
620 --
621 Begin
622 hr_utility.set_location('Entering:'||l_proc, 5);
623 --
624 -- Call all supporting business operations
625 --
626 hr_utility.set_location(' Leaving:'||l_proc, 10);
627 End delete_validate;
628 --
629 end irc_idp_bus;