[Home] [Help]
PACKAGE BODY: APPS.HR_LEI_BUS
Source
1 Package Body hr_lei_bus as
2 /* $Header: hrleirhi.pkb 120.1.12010000.2 2009/01/28 09:08:21 ghshanka ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_lei_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< Chk_location_id >----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- Verify that the value in LOCATION_ID is in the HR_LOCATIONS table.
17 --
18 -- Pre Conditions:
19 --
20 --
21 -- In Parameters:
22 -- p_location_id
23 --
24 -- Post Success:
25 -- Processing continues.
26 --
27 -- Post Failure:
28 -- An application error will be raised and processing is terminated.
29 --
30 -- Developer Implementation Notes:
31 -- For insert, your business rules should be executed from this procedure and
32 -- should ideally (unless really necessary) just be straight procedure or
33 -- function calls. Try and avoid using conditional branching logic.
34 --
35 -- Access Status:
36 -- Internal Table Handler Use Only.
37 --
38 -- {End Of Comments}
39 -- ----------------------------------------------------------------------------
40 Procedure chk_location_id
41 (
42 p_location_id in hr_location_extra_info.location_id%type
43 ) is
44 --
45 l_proc varchar2(72) := g_package||'chk_location_id';
46 l_dummy varchar2(1);
47 --
48 cursor c_valid_loc is
49 select 'x'
50 from hr_locations
51 where location_id = p_location_id and location_use = 'HR';
52 --
53 Begin
54 hr_utility.set_location('Entering:'||l_proc, 1);
55 --
56 -- Check mandatory parameters have been set
57 --
58 hr_api.mandatory_arg_error
59 (
60 p_api_name => l_proc,
61 p_argument => 'location_id',
62 p_argument_value => p_location_id
63 );
64 hr_utility.set_location(l_proc, 2);
65 --
66 -- Check that the location_id is in the hr_locations table.
67 --
68 open c_valid_loc;
69 fetch c_valid_loc into l_dummy;
70 if c_valid_loc%notfound then
71 close c_valid_loc;
72 hr_utility.set_message(800, 'HR_INV_LOC_ID');
73 hr_utility.raise_error;
74 end if;
75 close c_valid_loc;
76 --
77 hr_utility.set_location(' Leaving:'||l_proc, 3);
78 --
79 End chk_location_id;
80 --
81 --
82 -- ----------------------------------------------------------------------------
83 -- |-----------------------< chk_non_updateable_args >------------------------|
84 -- ----------------------------------------------------------------------------
85 -- {Start Of Comments}
86 --
87 -- Description:
88 -- Verify that the non updateable arguments not changed.
89 -- For the LOCATION_EXTRA_INFO table neither of the FK's can be updated
90 -- i.e. LOCATION_ID and INFORMATION_TYPE
91 --
92 -- Pre Conditions:
93 -- None
94 --
95 -- In Parameters:
96 -- p_rec
97 --
98 -- Post Success:
99 -- Processing continues.
100 --
101 -- Post Failure:
102 -- An application error will be raised and processing is terminated.
103 --
104 -- Developer Implementation Notes:
105 -- None
106 --
107 -- Access Status:
108 -- Internal Row Handler Use Only.
109 --
110 -- {End Of Comments}
111 -- ----------------------------------------------------------------------------
112 --
113 Procedure chk_non_updateable_args(p_rec in hr_lei_shd.g_rec_type) is
114 --
115 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
116 l_error exception;
117 l_argument varchar2(30);
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'|| l_proc,10);
122 --
123 -- Only proceed with validation if a row exists for
124 -- the current record in the HR Schema.
125 if not hr_lei_shd.api_updating
126 (p_location_extra_info_id => p_rec.location_extra_info_id
127 ,p_object_version_number => p_rec.object_version_number
128 ) then
129 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
130 hr_utility.set_message_token('PROCEDURE', l_proc);
131 hr_utility.set_message_token('STEP', '20');
132 end if;
133 --
134 hr_utility.set_location (l_proc, 30);
135 --
136 if nvl(p_rec.location_id,hr_api.g_number)
137 <> nvl(hr_lei_shd.g_old_rec.location_id,hr_api.g_number) then
138 l_argument := 'location_id';
139 raise l_error;
140 end if;
141 --
142 if nvl(p_rec.information_type,hr_api.g_varchar2)
143 <> nvl(hr_lei_shd.g_old_rec.information_type,hr_api.g_varchar2) then
144 l_argument := 'information_type';
145 raise l_error;
146 end if;
147 --
148 hr_utility.set_location(' Leaving : '|| l_proc, 40);
149 --
150 exception
151 when l_error then
152 hr_api.argument_changed_error
153 (p_api_name => l_proc
154 ,p_argument => l_argument);
155 when others then
156 raise;
157 end chk_non_updateable_args;
158 --
159 --
160 --
161 --
162 -- ----------------------------------------------------------------------------
163 -- |------------------------< chk_location_info_type >------------------------|
164 -- ----------------------------------------------------------------------------
165 Procedure chk_location_info_type(p_information_type varchar2,
166 p_multiple_occurences_flag out nocopy varchar2) is
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 -- This procedure check number of rows against an info type
172 -- Information_type must exist with active_inactive_flag='Y',
173 -- FK HR_LOCATION_EXTRA_INFO_FK1, ensures the existence of row in info type table
174 -- but it should exist with active_inactive_flag = 'Y'
175 --
176 --
177 -- Pre Conditions:
178 -- This private procedure is called from insert/update_validate procedure.
179 --
180 -- In Parameters:
181 -- A Pl/Sql record structure, and multiple occurence flag.
182 --
183 -- Post Success:
184 -- Processing continues.
185 --
186 -- Post Failure:
187 -- If a business rules fails the error will not be handled by this procedure
188 -- unless explicity coded.
189 --
190 -- Developer Implementation Notes:
191 -- None
192 --
193 -- Access Status:
194 -- Internal Table Handler Use Only.
195 --
196 -- {End Of Comments}
197 -- ----------------------------------------------------------------------------
198 l_proc varchar2(72) := g_package||'chk_location_info_type';
199 l_active_inactive_flag HR_LOCATION_INFO_TYPES.ACTIVE_INACTIVE_FLAG%TYPE;
200 l_inactive_type exception;
201 --
202 CURSOR c_info_type IS
203 SELECT lit.multiple_occurences_flag
204 ,lit.active_inactive_flag
205 FROM hr_location_info_types lit
206 WHERE lit.information_type = p_information_type
207 ;
208 --
209 Begin
210 hr_utility.set_location('Entering:'||l_proc, 5);
211 --
212 --
213 -- Check mandatory parameters have been set
214 --
215 hr_api.mandatory_arg_error
216 (
217 p_api_name => l_proc,
218 p_argument => 'information_type',
219 p_argument_value => p_information_type
220 );
221 open c_info_type;
222 fetch c_info_type into p_multiple_occurences_flag, l_active_inactive_flag;
223 --
224 -- Check if there is any matching row for given info type
225 --
226 if c_info_type%NOTFOUND then
227 raise no_data_found;
228 end if;
229 --
230 -- Check if info type is active or not.
231 --
232 if l_active_inactive_flag = 'N' then
233 raise l_inactive_type;
234 end if;
235 --
236 close c_info_type;
237 --
238 hr_utility.set_location(' Leaving:'||l_proc, 10);
239 --
240 Exception
241 --
242 when l_inactive_type then
243 close c_info_type;
244 hr_utility.set_message(800, 'HR_INACTIVE_INFO_TYPE');
245 hr_utility.raise_error;
246 --
247 when no_data_found then
248 close c_info_type;
249 hr_utility.set_message(800, 'HR_INV_INFO_TYPE');
250 hr_utility.raise_error;
251 --
252 End chk_location_info_type;
253 --
254 --
255 -- Ensures that number of rows should not exceed one,
256 -- if multiple_occurences_flag='N'
257 --
258 -- ----------------------------------------------------------------------------
259 -- |----------------------------< chk_count_rows >----------------------------|
260 -- ----------------------------------------------------------------------------
261 Procedure chk_count_rows(p_information_type in varchar2
262 , p_location_id in number
263 , p_multiple_occurences_flag in varchar2
264 ) is
265 -- ----------------------------------------------------------------------------
266 -- {Start Of Comments}
267 --
268 -- Description:
269 -- This procedure check number of rows against an info type
270 --
271 -- Pre Conditions:
272 -- This private procedure is called from insert/update_validate procedure.
273 --
274 -- In Parameters:
275 -- A Pl/Sql record structure
276 --
277 -- Out Parameters
278 -- multiple occurence flag
279 --
280 -- Post Success:
281 -- Processing continues.
282 --
283 -- Post Failure:
284 -- If a business rules fails the error will not be handled by this procedure
285 -- unless explicity coded.
286 --
287 -- Developer Implementation Notes:
288 -- None
289 --
290 -- Access Status:
291 -- Internal Table Handler Use Only.
292 --
293 -- {End Of Comments}
294 -- ----------------------------------------------------------------------------
295 --
296 l_proc varchar2(72) := g_package||'chk_count_rows';
297 l_dummy varchar2(1);
298 l_success exception;
299 l_failure exception;
300 --
301 CURSOR c_count_rows IS
302 SELECT 'x'
303 FROM hr_location_extra_info lei
304 WHERE lei.information_type = p_information_type
305 AND lei.location_id = p_location_id ;
306 --
307 Begin
308 hr_utility.set_location('Entering:'||l_proc, 5);
309 --
310 if p_multiple_occurences_flag = 'N' then
311 --
312 -- Check mandatory parameters have been set
313 --
314 hr_api.mandatory_arg_error
315 (
316 p_api_name => l_proc,
317 p_argument => 'information_type',
318 p_argument_value => p_information_type
319 );
320 --
321 --
322 hr_api.mandatory_arg_error
323 (
324 p_api_name => l_proc,
325 p_argument => 'location_id',
326 p_argument_value => p_location_id
327 );
328 open c_count_rows;
329 fetch c_count_rows into l_dummy;
330 if c_count_rows%FOUND then
331 close c_count_rows;
332 raise l_failure;
333 else
334 close c_count_rows;
335 raise l_success;
336 end if;
337 end if;
338 --
339 hr_utility.set_location(' Leaving:'||l_proc, 10);
340 Exception
341 when l_success then
342 hr_utility.set_location(' Leaving:'||l_proc, 10);
343 when l_failure then
344 hr_utility.set_message(800, 'HR_INFO_TYPE_ALLOWS_1_ROW');
345 hr_utility.raise_error;
346
347 End chk_count_rows;
348 --
349 -- -----------------------------------------------------------------------
350 -- |------------------------------< chk_df >-----------------------------|
351 -- -----------------------------------------------------------------------
352 -- {Start Of Comments}
353 --
354 -- Description:
355 -- Validates the all Descriptive Flexfield values.
356 --
357 -- Pre-conditions:
358 -- All other columns have been validated. Must be called as the
359 -- last step from insert_validate and update_validate.
360 --
361 -- In Arguments:
362 -- p_rec
363 --
364 -- Post Success:
365 -- If the Descriptive Flexfield structure column and data values are
366 -- all valid this procedure will end normally and processing will
367 -- continue.
368 --
369 -- Post Failure:
370 -- If the Descriptive Flexfield structure column value or any of
371 -- the data values are invalid then an application error is raised as
372 -- a PL/SQL exception.
373 --
374 -- Access Status:
375 -- Internal Row Handler Use Only.
376 --
377 -- {End Of Comments}
378 --
379 -- -----------------------------------------------------------------------
380 procedure chk_df
381 (p_rec in hr_lei_shd.g_rec_type) is
382 --
383 l_proc varchar2(72) := g_package||'chk_df';
384 --
385 begin
386 hr_utility.set_location('Entering:'||l_proc, 10);
387 --
388 if ((p_rec.location_extra_info_id is not null) and (
389 nvl(hr_lei_shd.g_old_rec.lei_attribute_category, hr_api.g_varchar2) <>
390 nvl(p_rec.lei_attribute_category, hr_api.g_varchar2) or
391 nvl(hr_lei_shd.g_old_rec.lei_attribute1, hr_api.g_varchar2) <>
392 nvl(p_rec.lei_attribute1, hr_api.g_varchar2) or
393 nvl(hr_lei_shd.g_old_rec.lei_attribute2, hr_api.g_varchar2) <>
394 nvl(p_rec.lei_attribute2, hr_api.g_varchar2) or
395 nvl(hr_lei_shd.g_old_rec.lei_attribute3, hr_api.g_varchar2) <>
396 nvl(p_rec.lei_attribute3, hr_api.g_varchar2) or
397 nvl(hr_lei_shd.g_old_rec.lei_attribute4, hr_api.g_varchar2) <>
398 nvl(p_rec.lei_attribute4, hr_api.g_varchar2) or
399 nvl(hr_lei_shd.g_old_rec.lei_attribute5, hr_api.g_varchar2) <>
400 nvl(p_rec.lei_attribute5, hr_api.g_varchar2) or
401 nvl(hr_lei_shd.g_old_rec.lei_attribute6, hr_api.g_varchar2) <>
402 nvl(p_rec.lei_attribute6, hr_api.g_varchar2) or
403 nvl(hr_lei_shd.g_old_rec.lei_attribute7, hr_api.g_varchar2) <>
404 nvl(p_rec.lei_attribute7, hr_api.g_varchar2) or
405 nvl(hr_lei_shd.g_old_rec.lei_attribute8, hr_api.g_varchar2) <>
406 nvl(p_rec.lei_attribute8, hr_api.g_varchar2) or
407 nvl(hr_lei_shd.g_old_rec.lei_attribute9, hr_api.g_varchar2) <>
408 nvl(p_rec.lei_attribute9, hr_api.g_varchar2) or
409 nvl(hr_lei_shd.g_old_rec.lei_attribute10, hr_api.g_varchar2) <>
410 nvl(p_rec.lei_attribute10, hr_api.g_varchar2) or
411 nvl(hr_lei_shd.g_old_rec.lei_attribute11, hr_api.g_varchar2) <>
412 nvl(p_rec.lei_attribute11, hr_api.g_varchar2) or
413 nvl(hr_lei_shd.g_old_rec.lei_attribute12, hr_api.g_varchar2) <>
414 nvl(p_rec.lei_attribute12, hr_api.g_varchar2) or
415 nvl(hr_lei_shd.g_old_rec.lei_attribute13, hr_api.g_varchar2) <>
416 nvl(p_rec.lei_attribute13, hr_api.g_varchar2) or
417 nvl(hr_lei_shd.g_old_rec.lei_attribute14, hr_api.g_varchar2) <>
418 nvl(p_rec.lei_attribute14, hr_api.g_varchar2) or
419 nvl(hr_lei_shd.g_old_rec.lei_attribute15, hr_api.g_varchar2) <>
420 nvl(p_rec.lei_attribute15, hr_api.g_varchar2) or
421 nvl(hr_lei_shd.g_old_rec.lei_attribute16, hr_api.g_varchar2) <>
422 nvl(p_rec.lei_attribute16, hr_api.g_varchar2) or
423 nvl(hr_lei_shd.g_old_rec.lei_attribute17, hr_api.g_varchar2) <>
424 nvl(p_rec.lei_attribute17, hr_api.g_varchar2) or
425 nvl(hr_lei_shd.g_old_rec.lei_attribute18, hr_api.g_varchar2) <>
426 nvl(p_rec.lei_attribute18, hr_api.g_varchar2) or
427 nvl(hr_lei_shd.g_old_rec.lei_attribute19, hr_api.g_varchar2) <>
428 nvl(p_rec.lei_attribute19, hr_api.g_varchar2) or
429 nvl(hr_lei_shd.g_old_rec.lei_attribute20, hr_api.g_varchar2) <>
430 nvl(p_rec.lei_attribute20, hr_api.g_varchar2)))
431 or
432 (p_rec.location_extra_info_id is null) then
433 --
434 -- Only execute the validation if absolutely necessary:
435 -- a) During update, the structure column value or any
436 -- of the attribute values have actually changed.
437 -- b) During insert.
438 --
439 hr_dflex_utility.ins_or_upd_descflex_attribs
440 (p_appl_short_name => 'PER'
441 ,p_descflex_name => 'HR_LOCATION_EXTRA_INFO'
442 ,p_attribute_category => p_rec.lei_attribute_category
443 ,p_attribute1_name => 'LEI_ATTRIBUTE1'
444 ,p_attribute1_value => p_rec.lei_attribute1
445 ,p_attribute2_name => 'LEI_ATTRIBUTE2'
446 ,p_attribute2_value => p_rec.lei_attribute2
447 ,p_attribute3_name => 'LEI_ATTRIBUTE3'
448 ,p_attribute3_value => p_rec.lei_attribute3
449 ,p_attribute4_name => 'LEI_ATTRIBUTE4'
450 ,p_attribute4_value => p_rec.lei_attribute4
451 ,p_attribute5_name => 'LEI_ATTRIBUTE5'
452 ,p_attribute5_value => p_rec.lei_attribute5
453 ,p_attribute6_name => 'LEI_ATTRIBUTE6'
454 ,p_attribute6_value => p_rec.lei_attribute6
455 ,p_attribute7_name => 'LEI_ATTRIBUTE7'
456 ,p_attribute7_value => p_rec.lei_attribute7
457 ,p_attribute8_name => 'LEI_ATTRIBUTE8'
458 ,p_attribute8_value => p_rec.lei_attribute8
459 ,p_attribute9_name => 'LEI_ATTRIBUTE9'
460 ,p_attribute9_value => p_rec.lei_attribute9
461 ,p_attribute10_name => 'LEI_ATTRIBUTE10'
462 ,p_attribute10_value => p_rec.lei_attribute10
463 ,p_attribute11_name => 'LEI_ATTRIBUTE11'
464 ,p_attribute11_value => p_rec.lei_attribute11
465 ,p_attribute12_name => 'LEI_ATTRIBUTE12'
466 ,p_attribute12_value => p_rec.lei_attribute12
467 ,p_attribute13_name => 'LEI_ATTRIBUTE13'
468 ,p_attribute13_value => p_rec.lei_attribute13
469 ,p_attribute14_name => 'LEI_ATTRIBUTE14'
470 ,p_attribute14_value => p_rec.lei_attribute14
471 ,p_attribute15_name => 'LEI_ATTRIBUTE15'
472 ,p_attribute15_value => p_rec.lei_attribute15
473 ,p_attribute16_name => 'LEI_ATTRIBUTE16'
474 ,p_attribute16_value => p_rec.lei_attribute16
475 ,p_attribute17_name => 'LEI_ATTRIBUTE17'
476 ,p_attribute17_value => p_rec.lei_attribute17
477 ,p_attribute18_name => 'LEI_ATTRIBUTE18'
478 ,p_attribute18_value => p_rec.lei_attribute18
479 ,p_attribute19_name => 'LEI_ATTRIBUTE19'
480 ,p_attribute19_value => p_rec.lei_attribute19
481 ,p_attribute20_name => 'LEI_ATTRIBUTE20'
482 ,p_attribute20_value => p_rec.lei_attribute20);
483 end if;
484 --
485 hr_utility.set_location(' Leaving:'||l_proc, 20);
486 end chk_df;
487 --
488 -- -----------------------------------------------------------------------
489 -- |------------------------------< chk_ddf >----------------------------|
490 -- -----------------------------------------------------------------------
491 --
492 -- {Start Of Comments}
493 --
494 -- Description:
495 -- Validates the all Developer Descriptive Flexfield values.
496 --
497 -- Pre-conditions:
498 -- All other columns have been validated. Must be called as the
499 -- second last step from insert_validate and update_validate.
500 -- Before any Descriptive Flexfield (chk_df) calls.
501 --
502 -- In Arguments:
503 -- p_rec
504 --
505 -- Post Success:
506 -- If the Developer Descriptive Flexfield structure column and data
507 -- values are all valid this procedure will end normally and
508 -- processing will continue.
509 --
510 -- Post Failure:
511 -- If the DDF structure column value or any of the data values
512 -- are invalid then an application error is raised as
513 -- a PL/SQL exception.
514 --
515 -- Access Status:
516 -- Internal Row Handler Use Only.
517 --
518 -- {End Of Comments}
519 --
520 -- ----------------------------------------------------------------------------
521 procedure chk_ddf
522 (p_rec in hr_lei_shd.g_rec_type) is
523 --
524 l_proc varchar2(72) := g_package||'chk_ddf';
525 l_error exception;
526 --
527 Begin
528 hr_utility.set_location('Entering:'||l_proc, 5);
529 --
530 -- Check if the row is being inserted or updated and a
531 -- value has changed
532 --
533 if (p_rec.location_extra_info_id is null)
534 or ((p_rec.location_extra_info_id is not null)
535 and
536 nvl(hr_lei_shd.g_old_rec.lei_attribute_category, hr_api.g_varchar2) <>
537 nvl(p_rec.lei_information_category, hr_api.g_varchar2) or
538 nvl(hr_lei_shd.g_old_rec.lei_information1, hr_api.g_varchar2) <>
539 nvl(p_rec.lei_information1, hr_api.g_varchar2) or
540 nvl(hr_lei_shd.g_old_rec.lei_information2, hr_api.g_varchar2) <>
541 nvl(p_rec.lei_information2, hr_api.g_varchar2) or
542 nvl(hr_lei_shd.g_old_rec.lei_information3, hr_api.g_varchar2) <>
543 nvl(p_rec.lei_information3, hr_api.g_varchar2) or
544 nvl(hr_lei_shd.g_old_rec.lei_information4, hr_api.g_varchar2) <>
545 nvl(p_rec.lei_information4, hr_api.g_varchar2) or
546 nvl(hr_lei_shd.g_old_rec.lei_information5, hr_api.g_varchar2) <>
547 nvl(p_rec.lei_information5, hr_api.g_varchar2) or
548 nvl(hr_lei_shd.g_old_rec.lei_information6, hr_api.g_varchar2) <>
549 nvl(p_rec.lei_information6, hr_api.g_varchar2) or
550 nvl(hr_lei_shd.g_old_rec.lei_information7, hr_api.g_varchar2) <>
551 nvl(p_rec.lei_information7, hr_api.g_varchar2) or
552 nvl(hr_lei_shd.g_old_rec.lei_information8, hr_api.g_varchar2) <>
553 nvl(p_rec.lei_information8, hr_api.g_varchar2) or
554 nvl(hr_lei_shd.g_old_rec.lei_information9, hr_api.g_varchar2) <>
555 nvl(p_rec.lei_information9, hr_api.g_varchar2) or
556 nvl(hr_lei_shd.g_old_rec.lei_information10, hr_api.g_varchar2) <>
557 nvl(p_rec.lei_information10, hr_api.g_varchar2) or
558 nvl(hr_lei_shd.g_old_rec.lei_information11, hr_api.g_varchar2) <>
559 nvl(p_rec.lei_information11, hr_api.g_varchar2) or
560 nvl(hr_lei_shd.g_old_rec.lei_information12, hr_api.g_varchar2) <>
561 nvl(p_rec.lei_information12, hr_api.g_varchar2) or
562 nvl(hr_lei_shd.g_old_rec.lei_information13, hr_api.g_varchar2) <>
563 nvl(p_rec.lei_information13, hr_api.g_varchar2) or
564 nvl(hr_lei_shd.g_old_rec.lei_information14, hr_api.g_varchar2) <>
565 nvl(p_rec.lei_information14, hr_api.g_varchar2) or
566 nvl(hr_lei_shd.g_old_rec.lei_information15, hr_api.g_varchar2) <>
567 nvl(p_rec.lei_information15, hr_api.g_varchar2) or
568 nvl(hr_lei_shd.g_old_rec.lei_information16, hr_api.g_varchar2) <>
569 nvl(p_rec.lei_information16, hr_api.g_varchar2) or
570 nvl(hr_lei_shd.g_old_rec.lei_information17, hr_api.g_varchar2) <>
571 nvl(p_rec.lei_information17, hr_api.g_varchar2) or
572 nvl(hr_lei_shd.g_old_rec.lei_information18, hr_api.g_varchar2) <>
573 nvl(p_rec.lei_information18, hr_api.g_varchar2) or
574 nvl(hr_lei_shd.g_old_rec.lei_information19, hr_api.g_varchar2) <>
575 nvl(p_rec.lei_information19, hr_api.g_varchar2) or
576 nvl(hr_lei_shd.g_old_rec.lei_information20, hr_api.g_varchar2) <>
577 nvl(p_rec.lei_information20, hr_api.g_varchar2) or
578 nvl(hr_lei_shd.g_old_rec.lei_information21, hr_api.g_varchar2) <>
579 nvl(p_rec.lei_information21, hr_api.g_varchar2) or
580 nvl(hr_lei_shd.g_old_rec.lei_information22, hr_api.g_varchar2) <>
581 nvl(p_rec.lei_information22, hr_api.g_varchar2) or
582 nvl(hr_lei_shd.g_old_rec.lei_information23, hr_api.g_varchar2) <>
583 nvl(p_rec.lei_information23, hr_api.g_varchar2) or
584 nvl(hr_lei_shd.g_old_rec.lei_information24, hr_api.g_varchar2) <>
585 nvl(p_rec.lei_information24, hr_api.g_varchar2) or
586 nvl(hr_lei_shd.g_old_rec.lei_information25, hr_api.g_varchar2) <>
587 nvl(p_rec.lei_information25, hr_api.g_varchar2) or
588 nvl(hr_lei_shd.g_old_rec.lei_information26, hr_api.g_varchar2) <>
589 nvl(p_rec.lei_information26, hr_api.g_varchar2) or
590 nvl(hr_lei_shd.g_old_rec.lei_information27, hr_api.g_varchar2) <>
591 nvl(p_rec.lei_information27, hr_api.g_varchar2) or
592 nvl(hr_lei_shd.g_old_rec.lei_information28, hr_api.g_varchar2) <>
593 nvl(p_rec.lei_information28, hr_api.g_varchar2) or
594 nvl(hr_lei_shd.g_old_rec.lei_information29, hr_api.g_varchar2) <>
595 nvl(p_rec.lei_information29, hr_api.g_varchar2) or
596 nvl(hr_lei_shd.g_old_rec.lei_information30, hr_api.g_varchar2) <>
597 nvl(p_rec.lei_information30, hr_api.g_varchar2))
598 then
599 --
600 hr_dflex_utility.ins_or_upd_descflex_attribs
601 (p_appl_short_name => 'PER'
602 ,p_descflex_name => 'Extra Location Info DDF'
603 ,p_attribute_category => p_rec.lei_information_category
604 ,p_attribute1_name => 'LEI_INFORMATION1'
605 ,p_attribute1_value => p_rec.lei_information1
606 ,p_attribute2_name => 'LEI_INFORMATION2'
607 ,p_attribute2_value => p_rec.lei_information2
608 ,p_attribute3_name => 'LEI_INFORMATION3'
609 ,p_attribute3_value => p_rec.lei_information3
610 ,p_attribute4_name => 'LEI_INFORMATION4'
611 ,p_attribute4_value => p_rec.lei_information4
612 ,p_attribute5_name => 'LEI_INFORMATION5'
613 ,p_attribute5_value => p_rec.lei_information5
614 ,p_attribute6_name => 'LEI_INFORMATION6'
615 ,p_attribute6_value => p_rec.lei_information6
616 ,p_attribute7_name => 'LEI_INFORMATION7'
617 ,p_attribute7_value => p_rec.lei_information7
618 ,p_attribute8_name => 'LEI_INFORMATION8'
619 ,p_attribute8_value => p_rec.lei_information8
620 ,p_attribute9_name => 'LEI_INFORMATION9'
621 ,p_attribute9_value => p_rec.lei_information9
622 ,p_attribute10_name => 'LEI_INFORMATION10'
623 ,p_attribute10_value => p_rec.lei_information10
624 ,p_attribute11_name => 'LEI_INFORMATION11'
625 ,p_attribute11_value => p_rec.lei_information11
626 ,p_attribute12_name => 'LEI_INFORMATION12'
627 ,p_attribute12_value => p_rec.lei_information12
628 ,p_attribute13_name => 'LEI_INFORMATION13'
629 ,p_attribute13_value => p_rec.lei_information13
630 ,p_attribute14_name => 'LEI_INFORMATION14'
631 ,p_attribute14_value => p_rec.lei_information14
632 ,p_attribute15_name => 'LEI_INFORMATION15'
633 ,p_attribute15_value => p_rec.lei_information15
634 ,p_attribute16_name => 'LEI_INFORMATION16'
635 ,p_attribute16_value => p_rec.lei_information16
636 ,p_attribute17_name => 'LEI_INFORMATION17'
637 ,p_attribute17_value => p_rec.lei_information17
638 ,p_attribute18_name => 'LEI_INFORMATION18'
639 ,p_attribute18_value => p_rec.lei_information18
640 ,p_attribute19_name => 'LEI_INFORMATION19'
641 ,p_attribute19_value => p_rec.lei_information19
642 ,p_attribute20_name => 'LEI_INFORMATION20'
643 ,p_attribute20_value => p_rec.lei_information20
644 ,p_attribute21_name => 'LEI_INFORMATION21'
645 ,p_attribute21_value => p_rec.lei_information21
646 ,p_attribute22_name => 'LEI_INFORMATION22'
647 ,p_attribute22_value => p_rec.lei_information22
648 ,p_attribute23_name => 'LEI_INFORMATION23'
649 ,p_attribute23_value => p_rec.lei_information23
650 ,p_attribute24_name => 'LEI_INFORMATION24'
651 ,p_attribute24_value => p_rec.lei_information24
652 ,p_attribute25_name => 'LEI_INFORMATION25'
653 ,p_attribute25_value => p_rec.lei_information25
654 ,p_attribute26_name => 'LEI_INFORMATION26'
655 ,p_attribute26_value => p_rec.lei_information26
656 ,p_attribute27_name => 'LEI_INFORMATION27'
657 ,p_attribute27_value => p_rec.lei_information27
658 ,p_attribute28_name => 'LEI_INFORMATION28'
659 ,p_attribute28_value => p_rec.lei_information28
660 ,p_attribute29_name => 'LEI_INFORMATION29'
661 ,p_attribute29_value => p_rec.lei_information29
662 ,p_attribute30_name => 'LEI_INFORMATION30'
663 ,p_attribute30_value => p_rec.lei_information30
664 );
665 --
666 end if;
667 --
668 hr_utility.set_location(' Leaving:'||l_proc, 10);
669 end chk_ddf;
670 --
671 -- ----------------------------------------------------------------------------
672 -- |---------------------------< insert_validate >----------------------------|
673 -- ----------------------------------------------------------------------------
674 Procedure insert_validate(p_rec in hr_lei_shd.g_rec_type) is
675 --
676 l_proc varchar2(72) := g_package||'insert_validate';
677 l_multiple_occurences_flag hr_location_info_types.multiple_occurences_flag%type;
678 --
679 -- bug 6329528
680 --
681 /*
682 cursor csr_sec_grp is
683 select hoi.org_information14, hoi.org_information9
684 from hr_organization_information hoi
685 , hr_locations_all loc
686 where loc.location_id = p_rec.location_id
687 and hoi.organization_id = nvl(loc.business_group_id,0)
688 and hoi.org_information_context||'' = 'Business Group Information';
689 */
690
691 -- fix for the bug 7653370 modified the above cursor..as follows
692 cursor csr_sec_grp is
693 select hoi.org_information14, hoi.org_information9
694 from hr_organization_information hoi
695 , hr_locations_all loc
696 where loc.location_id = p_rec.location_id
697 and hoi.organization_id = nvl(loc.business_group_id,nvl(hr_general.get_business_group_id, -99))
698 and hoi.org_information_context||'' = 'Business Group Information';
699
700 l_security_group_id number;
701 l_legislation_code varchar2(150);
702
703 -- 6329528
704
705 Begin
706 hr_utility.set_location('Entering:'||l_proc, 5);
707 --
708 -- As this data is not within the context of a business group
709 -- the set_security_group_id procedure has zero passed
710 -- to it as the default security_group_id.
711 --
712 -- hr_api.set_security_group_id(p_security_group_id => 0); commented bug 6329528
713 -- bug 6329528
714
715 open csr_sec_grp;
716 fetch csr_sec_grp into l_security_group_id, l_legislation_code;
717 close csr_sec_grp;
718 hr_api.set_security_group_id(p_security_group_id => l_security_group_id);
719 --
720 --
721 hr_utility.set_location('Entering:'||l_proc, 6);
722 --
723 -- Call all supporting business operations
724 --
725 -- 1) Call chk_location_id to validate location_id
726 chk_location_id( p_location_id => p_rec.location_id );
727 --
728 -- 2) Call info_type procedure to validate info_type
729 --
730 chk_location_info_type(p_information_type => p_rec.information_type
731 ,p_multiple_occurences_flag => l_multiple_occurences_flag);
732 --
733 --
734 -- 3) Call count_rows procedure to allow/disallow inserts in extra_info
735 chk_count_rows(p_information_type => p_rec.information_type
736 ,p_location_id => p_rec.location_id
737 ,p_multiple_occurences_flag => l_multiple_occurences_flag
738 );
739 --
740 -- Call ddf procedure to validate Developer Descritive Flex Fields
741 --
742 hr_lei_bus.chk_ddf(p_rec => p_rec);
743 --
744 hr_utility.set_location(l_proc, 10);
745 --
746 -- Call df procedure to validate Descritive Flex Fields
747 --
748 hr_lei_bus.chk_df(p_rec => p_rec);
749 --
750 hr_utility.set_location(' Leaving:'||l_proc, 20);
751 --
752 --
753 End insert_validate;
754 --
755 -- ----------------------------------------------------------------------------
756 -- |---------------------------< update_validate >----------------------------|
757 -- ----------------------------------------------------------------------------
758 Procedure update_validate(p_rec in hr_lei_shd.g_rec_type) is
759 --
760 l_proc varchar2(72) := g_package||'update_validate';
761 --
762 -- bug 6329528
763 /*
764 cursor csr_sec_grp is
765 select hoi.org_information14, hoi.org_information9
766 from hr_organization_information hoi
767 , hr_locations_all loc
768 where loc.location_id = p_rec.location_id
769 and hoi.organization_id = nvl(loc.business_group_id,0)
770 and hoi.org_information_context||'' = 'Business Group Information';
771 */
772
773 -- fix for the bug 7653370 modified the above cursor..as follows
774 cursor csr_sec_grp is
775 select hoi.org_information14, hoi.org_information9
776 from hr_organization_information hoi
777 , hr_locations_all loc
778 where loc.location_id = p_rec.location_id
779 and hoi.organization_id = nvl(loc.business_group_id,nvl(hr_general.get_business_group_id, -99))
780 and hoi.org_information_context||'' = 'Business Group Information';
781
782 l_security_group_id number;
783 l_legislation_code varchar2(150);
784 -- bug 6329528
785 Begin
786 hr_utility.set_location('Entering:'||l_proc, 5);
787 --
788 -- As this data is not within the context of a business group
789 -- the set_security_group_id procedure has zero passed
790 -- to it as the default security_group_id.
791 --
792 -- bug 6329528
793 -- hr_api.set_security_group_id(p_security_group_id => 0); commented -- bug 6329528
794 --
795
796 open csr_sec_grp;
797 fetch csr_sec_grp into l_security_group_id, l_legislation_code;
798 close csr_sec_grp;
799 hr_api.set_security_group_id(p_security_group_id => l_security_group_id);
800 --
801
802 --
803 hr_utility.set_location('Entering:'||l_proc, 6);
804 --
805 -- Call all supporting business operations
806 --
807 -- 1) Check those columns which cannot be updated have not changed.
808 --
809 hr_utility.set_location(l_proc, 10);
810 --
811 chk_non_updateable_args (p_rec => p_rec);
812 --
813 -- Call ddf procedure to validate Developer Descritive Flex Fields
814 --
815 hr_lei_bus.chk_ddf(p_rec => p_rec);
816 --
817 hr_utility.set_location(l_proc, 10);
818 --
819 -- Call df procedure to validate Descritive Flex Fields
820 --
821 hr_lei_bus.chk_df(p_rec => p_rec);
822 --
823 hr_utility.set_location('Leaving: '||l_proc, 25);
824 --
825 --
826 End update_validate;
827 --
828 -- ----------------------------------------------------------------------------
829 -- |---------------------------< delete_validate >----------------------------|
830 -- ----------------------------------------------------------------------------
831 Procedure delete_validate(p_rec in hr_lei_shd.g_rec_type) is
832 --
833 l_proc varchar2(72) := g_package||'delete_validate';
834 --
835 Begin
836 hr_utility.set_location('Entering:'||l_proc, 5);
837 --
838 -- Call all supporting business operations
839 --
840 hr_utility.set_location(' Leaving:'||l_proc, 10);
841 End delete_validate;
842 --
843 end hr_lei_bus;