1 Package Body ben_ola_bus as
2 /* $Header: beolarhi.pkb 120.0 2005/05/28 09:51:12 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ola_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_csr_activities_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- csr_activities_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_csr_activities_id(p_csr_activities_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_csr_activities_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_ola_shd.api_updating
47 (p_csr_activities_id => p_csr_activities_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_csr_activities_id,hr_api.g_number)
52 <> ben_ola_shd.g_old_rec.csr_activities_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_ola_shd.constraint_error('BEN_CSR_ACTIVITIES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_csr_activities_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_ola_shd.constraint_error('BEN_CSR_ACTIVITIES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_csr_activities_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_function_name >------|
78 -- ----------------------------------------------------------------------------
79 Procedure chk_function_name(p_csr_activities_id in number,
80 p_function_name in varchar2,
81 -- p_effective_date in date,
82 p_object_version_number in number) is
83 --
84 l_proc varchar2(72) := g_package||'chk_function_name';
85 l_api_updating boolean;
86 l_dummy varchar2(1);
87 --
88 cursor c1 is
89 select null
90 from fnd_form_functions_vl ff
91 where ff.function_name = p_function_name;
92 /* ff.application_id = 810 */
93
94
95 -- Bug 2382144 - Changes to handle Person form localizations
96 -- The LOV for Function Name in the On Line Activity form will display
97 -- all values for the Lookup Type 'BEN_ON_LINE_ACT' except
98 -- the Lookup code 'PERWSHRG-403'.
99 -- It will also display all the form function names which have been
100 -- created for the Person form - PERWSHRG.
101
102 cursor c2 is
103 select null
104 from hr_lookups
105 where lookup_code = p_function_name
106 and lookup_type = 'BEN_ON_LINE_ACT'
107 and enabled_flag = 'Y'
108 and TRUNC(sysdate) between
109 nvl(start_date_active, TRUNC(sysdate))
110 and nvl(end_date_active, TRUNC(sysdate))
111 union
112 select null
113 from fnd_form frm,
114 fnd_form_functions fnc
115 where frm.form_name = 'PERWSHRG'
116 and frm.form_id = fnc.form_id
117 and frm.application_id = fnc.application_id
118 and fnc.function_name = p_function_name;
119 --
120 Begin
121 --
122 hr_utility.set_location('Entering:'||l_proc, 5);
123 --
124 l_api_updating := ben_ola_shd.api_updating
125 (p_csr_activities_id => p_csr_activities_id,
126 p_object_version_number => p_object_version_number);
127 --
128 if (l_api_updating
129 and nvl(p_function_name,hr_api.g_varchar2)
130 <> ben_ola_shd.g_old_rec.function_name
131 or not l_api_updating)
132 and p_function_name is not null then
133 --
134 -- check if value of function name is valid.
135 --
136 /* open c1;
137 --
138 -- fetch value from cursor if it returns a record then the
139 -- formula is valid otherwise its invalid
140 --
141 fetch c1 into l_dummy;
142 if c1%notfound then
143 --
144 close c1;
145 --
146 -- raise error
147 --
148 hr_utility.set_message(801,'FUNCTION_DOES_NOT_EXIST');
149 hr_utility.raise_error;
150 --
151 end if;
152 --
153 close c1;
154 */
155
156
157 -- Bug 2382144 start
158 /*
159 if hr_api.not_exists_in_hr_lookups
160 (p_lookup_type => 'BEN_ON_LINE_ACT',
161 p_lookup_code => p_function_name,
162 p_effective_date => sysdate) then
163 --
164 --
165 -- raise error as does not exist as lookup
166 --
167 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
168 hr_utility.raise_error;
169 --
170 end if;
171 --
172 --
173 */
174 --
175 -- check if value of function name is valid.
176 --
177 --
178 open c2;
179 --
180 -- fetch value from cursor if it returns a record then the
181 -- value is valid otherwise its invalid
182 --
183 fetch c2 into l_dummy;
184 if c2%notfound then
185 --
186 close c2;
187 --
188 -- raise error
189 --
190 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
191 hr_utility.raise_error;
192 --
193 end if;
194 --
195 close c2;
196 --
197
198 -- Bug 2382144 end
199
200 end if;
201 --
202 hr_utility.set_location('Leaving:'||l_proc,10);
203 --
204 end chk_function_name;
205 --
206 -- ----------------------------------------------------------------------------
207 -- |------< chk_function_type >------|
208 -- ----------------------------------------------------------------------------
209 --
210 -- Description
211 -- This procedure is used to check that the lookup value is valid.
212 --
213 Procedure chk_function_type(p_csr_activities_id in number,
214 p_function_type in varchar2,
215 -- p_effective_date in date,
216 p_object_version_number in number) is
217 --
218 l_proc varchar2(72) := g_package||'chk_function_type';
219 l_api_updating boolean;
220 --
221 Begin
222 --
223 hr_utility.set_location('Entering:'||l_proc, 5);
224 --
225 l_api_updating := ben_ola_shd.api_updating
226 (p_csr_activities_id => p_csr_activities_id,
227 p_object_version_number => p_object_version_number);
228 --
229 if (l_api_updating
230 and p_function_type
231 <> nvl(ben_ola_shd.g_old_rec.function_type,hr_api.g_varchar2)
232 or not l_api_updating)
233 and p_function_type is not null then
234 --
235 -- check if value of lookup falls within lookup type.
236 --
237 if hr_api.not_exists_in_hr_lookups
238 (p_lookup_type => 'BEN_FUNCTION_TYPE',
239 p_lookup_code => p_function_type,
240 p_effective_date => sysdate) then
241 --
242 -- raise error as does not exist as lookup
243 --
244 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
245 hr_utility.raise_error;
246 --
247 end if;
248 --
249 end if;
250 --
251 hr_utility.set_location('Leaving:'||l_proc,10);
252 --
253 end chk_function_type;
254 --
255
256 -- ----------------------------------------------------------------------------
257 -- |------< chk_duplicate_function_name >------|
258 -- ----------------------------------------------------------------------------
259 --
260 -- Description
261 -- This procedure is used to check the duplicate function name and end date is
262 -- greater than start date
263
264
265 --
266 Procedure chk_duplicate_function_name(p_csr_activities_id in number,
267 p_function_name in varchar2,
268 p_effective_start_date in date,
269 p_effective_end_date in date,
270 p_business_group_id in number) is
271 --
272 l_proc varchar2(72) := g_package||'chk_duplicate_function_name';
273 l_dummy varchar2(1) ;
274 Cursor c1 is select null
275 from ben_csr_activities
276 where (csr_activities_id <> p_csr_activities_id or p_csr_activities_id is null)and
277 function_name = p_function_name and
278 business_group_id = p_business_group_id;
279 --
280 Begin
281 --
282 hr_utility.set_location('Entering:'||l_proc, 11);
283 --
284 --
285 open c1;
286 fetch c1 into l_dummy;
287 if c1%found then
288 close c1;
289 -- raise error as duplicate function name is entered
290
291 --
292 fnd_message.set_name('BEN','BEN_92502_BENOLLAC_DUP_FUNC');
293 fnd_message.raise_error;
294 end if;
295 close c1;
296 -- check end date is less than start date
297 if p_effective_start_date is null and p_effective_end_date is not null then
298 fnd_message.set_name('BEN','BEN_92503_END_DT_GRTR_STRT_DT');
299 fnd_message.raise_error;
300 elsif p_effective_start_date is not null and p_effective_end_date is not null then
301 if p_effective_start_date > p_effective_end_date then
302 fnd_message.set_name('BEN','BEN_92503_END_DT_GRTR_STRT_DT');
303 fnd_message.raise_error;
304 end if;
305 end if;
306 --
307 --
308 --
309 hr_utility.set_location('Leaving:'||l_proc,11);
310 --
311 end chk_duplicate_function_name;
312 --
313
314 -- |------------------------< chk_seq_num_unique >--------------------------|
315 -- ----------------------------------------------------------------------------
316 --
317 -- Description
318 -- This procedure is used to check that the activity seqeuence number is
319 -- unique within a business group for a given date range.
320 --
321 -- Pre Conditions
322 -- chk_duplicate_function_name function already called so as to be sure
323 -- that start_date is not greater than end_date.
324 --
325 -- In Parameters
326 -- p_csr_activities_id PK of record being inserted or updated.
327 -- p_ordr_num Sequence no.
328 -- p_business_group_id Business group id of record being inserted.
329 -- p_object_version_number Object version number of record being
330 -- inserted or updated.
331 -- p_csr_start_date Start Date of the record
332 -- p_csr_end_date End Date of the record
333 --
334 -- Post Success
335 -- Processing continues
336 --
337 -- Post Failure
338 -- Errors handled by the procedure
339 --
340 -- Access Status
341 -- Internal table handler use only.
342 --
343 Procedure chk_seq_num_unique(p_csr_activities_id in number,
344 p_ordr_num in number,
345 p_business_group_id in number,
346 p_object_version_number in number,
347 p_csr_start_date in date,
348 p_csr_end_date in date) is
349 --
350 l_proc varchar2(72) := g_package||'chk_seq_num_unique';
351 l_dup_rec number;
352 l_api_updating boolean;
353 --
354 cursor c1 is
355 select 1
356 from BEN_CSR_ACTIVITIES bca
357 where bca.business_group_id +0 = p_business_group_id
358 and bca.csr_activities_id <> nvl(p_csr_activities_id,-1)
359 and bca.ordr_num = p_ordr_num
360 and nvl(bca.end_date, hr_api.g_eot) >= nvl(p_csr_start_date, hr_api.g_sot)
361 and nvl(bca.start_date, hr_api.g_sot) <= nvl(p_csr_end_date, hr_api.g_eot);
362 --
363 Begin
364 --
365 hr_utility.set_location('Entering:'||l_proc, 5);
366 --
367 l_api_updating := ben_ola_shd.api_updating
368 (p_csr_activities_id => p_csr_activities_id,
369 p_object_version_number => p_object_version_number);
370 --
371 --We need to check the duplicate seq no. when
372 --Inserting a new record or
373 --Updating ordr_num/start_date/end_date
374 --
375 if ((l_api_updating
376 and ((nvl(p_ordr_num,hr_api.g_number)
377 <> nvl(ben_ola_shd.g_old_rec.ordr_num,hr_api.g_number)) or
378 (nvl(p_csr_start_date,hr_api.g_date)
379 <> nvl(ben_ola_shd.g_old_rec.start_date,hr_api.g_date)) or
380 (nvl(p_csr_end_date,hr_api.g_date)
381 <> nvl(ben_ola_shd.g_old_rec.end_date,hr_api.g_date))
382 ))
383 or not l_api_updating) then
384 --
385 -- Check if order no. is unique
386 --
387 open c1;
388 --
389 fetch c1 into l_dup_rec;
390 if c1%found then
391 close c1;
392 --
393 -- raise an error for duplicate order no
394 --
395 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
396 fnd_message.raise_error;
397 --
398 end if;
399 close c1;
400 --
401 end if;
402 hr_utility.set_location('Leaving:'||l_proc, 10);
403 --
404 End chk_seq_num_unique;
405 --
406
407
408 -- ----------------------------------------------------------------------------
409 -- |---------------------------< insert_validate >----------------------------|
410 -- ----------------------------------------------------------------------------
411 Procedure insert_validate(p_rec in ben_ola_shd.g_rec_type) is
412 --
413 l_proc varchar2(72) := g_package||'insert_validate';
414 --
415 Begin
416 hr_utility.set_location('Entering:'||l_proc, 5);
417 --
418 -- Call all supporting business operations
419 --
420 --
421 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
422 --
423 chk_csr_activities_id
424 (p_csr_activities_id => p_rec.csr_activities_id,
425 p_object_version_number => p_rec.object_version_number);
426 --
427 chk_function_name
428 (p_csr_activities_id => p_rec.csr_activities_id ,
429 p_function_name => p_rec.function_name,
430 -- p_effective_date => p_effective_date,
431 p_object_version_number => p_rec.object_version_number);
432 --
433 chk_function_type
434 (p_csr_activities_id => p_rec.csr_activities_id,
435 p_function_type => p_rec.function_type,
436 -- p_effective_date => p_effective_date,
437 p_object_version_number => p_rec.object_version_number);
438 --
439 chk_duplicate_function_name
440 (p_csr_activities_id => p_rec.csr_activities_id,
441 p_function_name => p_rec.function_name,
442 p_effective_start_date => p_rec.start_date,
443 p_effective_end_date => p_rec.end_date,
444 p_business_group_id => p_rec.business_group_id);
445 --
446 chk_seq_num_unique
447 (p_csr_activities_id => p_rec.csr_activities_id,
448 p_ordr_num => p_rec.ordr_num,
449 p_business_group_id => p_rec.business_group_id,
450 p_object_version_number=> p_rec.object_version_number,
451 p_csr_start_date => p_rec.start_date,
452 p_csr_end_date => p_rec.end_date);
453
454 hr_utility.set_location(' Leaving:'||l_proc, 10);
455 End insert_validate;
456 --
457 -- ----------------------------------------------------------------------------
458 -- |---------------------------< update_validate >----------------------------|
459 -- ----------------------------------------------------------------------------
460 Procedure update_validate(p_rec in ben_ola_shd.g_rec_type) is
461 --
462 l_proc varchar2(72) := g_package||'update_validate';
463 --
464 Begin
465 hr_utility.set_location('Entering:'||l_proc, 5);
466 --
467 -- Call all supporting business operations
468 --
469 --
470 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
471 --
472 chk_csr_activities_id
473 (p_csr_activities_id => p_rec.csr_activities_id,
474 p_object_version_number => p_rec.object_version_number);
475 --
476 chk_function_name
477 (p_csr_activities_id => p_rec.csr_activities_id ,
478 p_function_name => p_rec.function_name,
479 -- p_effective_date => p_effective_date,
480 p_object_version_number => p_rec.object_version_number);
481 --
482 chk_function_type
483 (p_csr_activities_id => p_rec.csr_activities_id,
484 p_function_type => p_rec.function_type,
485 -- p_effective_date => p_effective_date,
486 p_object_version_number => p_rec.object_version_number);
487 --
488 chk_duplicate_function_name
489 (p_csr_activities_id => p_rec.csr_activities_id,
490 p_function_name => p_rec.function_name,
491 p_effective_start_date => p_rec.start_date,
492 p_effective_end_date => p_rec.end_date,
493 p_business_group_id => p_rec.business_group_id);
494 --
495 chk_seq_num_unique
496 (p_csr_activities_id => p_rec.csr_activities_id,
497 p_ordr_num => p_rec.ordr_num,
498 p_business_group_id => p_rec.business_group_id,
499 p_object_version_number=> p_rec.object_version_number,
500 p_csr_start_date => p_rec.start_date,
501 p_csr_end_date => p_rec.end_date);
502
503 hr_utility.set_location(' Leaving:'||l_proc, 10);
504 End update_validate;
505 --
506 -- ----------------------------------------------------------------------------
507 -- |---------------------------< delete_validate >----------------------------|
508 -- ----------------------------------------------------------------------------
509 Procedure delete_validate(p_rec in ben_ola_shd.g_rec_type) is
510 --
511 l_proc varchar2(72) := g_package||'delete_validate';
512 --
513 Begin
514 hr_utility.set_location('Entering:'||l_proc, 5);
515 --
516 -- Call all supporting business operations
517 --
518 hr_utility.set_location(' Leaving:'||l_proc, 10);
519 End delete_validate;
520 --
521 --
522 -- ---------------------------------------------------------------------------
523 -- |---------------------< return_legislation_code >-------------------------|
524 -- ---------------------------------------------------------------------------
525 --
526 function return_legislation_code
527 (p_csr_activities_id in number) return varchar2 is
528 --
529 -- Declare cursor
530 --
531 cursor csr_leg_code is
532 select a.legislation_code
533 from per_business_groups a,
534 ben_csr_activities b
535 where b.csr_activities_id = p_csr_activities_id
536 and a.business_group_id = b.business_group_id;
537 --
538 -- Declare local variables
539 --
540 l_legislation_code varchar2(150);
541 l_proc varchar2(72) := g_package||'return_legislation_code';
542 --
543 begin
544 --
545 hr_utility.set_location('Entering:'|| l_proc, 10);
546 --
547 -- Ensure that all the mandatory parameter are not null
548 --
549 hr_api.mandatory_arg_error(p_api_name => l_proc,
550 p_argument => 'csr_activities_id',
551 p_argument_value => p_csr_activities_id);
552 --
553 open csr_leg_code;
554 --
555 fetch csr_leg_code into l_legislation_code;
556 --
557 if csr_leg_code%notfound then
558 --
559 close csr_leg_code;
560 --
561 -- The primary key is invalid therefore we must error
562 --
563 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
564 hr_utility.raise_error;
565 --
566 end if;
567 --
568 close csr_leg_code;
569 --
570 hr_utility.set_location(' Leaving:'|| l_proc, 20);
571 --
572 return l_legislation_code;
573 --
574 end return_legislation_code;
575 --
576 end ben_ola_bus;
[Home] [Help]