[Home] [Help]
PACKAGE BODY: APPS.PAY_PEU_BUS
Source
1 Package Body pay_peu_bus as
2 /* $Header: pypeurhi.pkb 120.0 2005/05/29 07:29:11 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_peu_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_event_update_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_event_update_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pay_event_updates peu
30 where peu.event_update_id = p_event_update_id
31 and pbg.business_group_id = peu.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'event_update_id'
47 ,p_argument_value => p_event_update_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_event_update_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , pay_event_updates peu
90 where peu.event_update_id = p_event_update_id
91 and pbg.business_group_id (+) = peu.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'event_update_id'
107 ,p_argument_value => p_event_update_id
108 );
109 --
110 if ( nvl(pay_peu_bus.g_event_update_id, hr_api.g_number)
111 = p_event_update_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := pay_peu_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 pay_peu_bus.g_event_update_id := p_event_update_id;
142 pay_peu_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_effective_date in date
177 ,p_rec in pay_peu_shd.g_rec_type
178 ) IS
179 --
180 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
181 l_error EXCEPTION;
182 l_argument varchar2(30);
183 --
184 Begin
185 --
186 -- Only proceed with the validation if a row exists for the current
187 -- record in the HR Schema.
188 --
189 IF NOT pay_peu_shd.api_updating
190 (p_event_update_id => p_rec.event_update_id
191 ,p_object_version_number => p_rec.object_version_number
192 ) THEN
193 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
194 fnd_message.set_token('PROCEDURE ', l_proc);
195 fnd_message.set_token('STEP ', '5');
196 fnd_message.raise_error;
197 END IF;
198 --
199 -- EDIT_HERE: Add checks to ensure non-updateable args have
200 -- not been updated.
201 --
202 if (nvl(p_rec.dated_table_id, hr_api.g_number) <>
203 nvl(pay_peu_shd.g_old_rec.dated_table_id, hr_api.g_number)
204 ) then
205 l_argument := 'dated_table_id';
206 raise l_error;
207 END IF;
208 --
209 if (nvl(p_rec.table_name, hr_api.g_varchar2) <>
210 nvl(pay_peu_shd.g_old_rec.table_name, hr_api.g_varchar2)
211 ) then
212 l_argument := 'table_name';
213 raise l_error;
214 END IF;
215 --
216 if (nvl(p_rec.business_group_id, hr_api.g_number) <>
217 nvl(pay_peu_shd.g_old_rec.business_group_id,hr_api.g_number)
218 ) then
219 l_argument := 'business_group_id';
220 raise l_error;
221 END IF;
222 --
223 EXCEPTION
224 WHEN l_error THEN
225 hr_api.argument_changed_error
226 (p_api_name => l_proc
227 ,p_argument => l_argument);
228 WHEN OTHERS THEN
229 RAISE;
230 End chk_non_updateable_args;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |---------------------------<chk_event_type>----------------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start of comments}
236 --
237 -- Description:
238 -- This procedure validates the event type passed actually exists
239 --
240 -- In Parameters:
241 -- A Pl/Sql record structure.
242 --
243 -- Post Success:
244 -- Processing continues.
245 --
246 -- Post Failure:
247 -- Error if column not recognised.
248 --
249 -- Access Status:
250 -- Internal Row Handler Use Only.
251 --
252 -- {End of comments}
253 --
254 Procedure chk_event_type
255 (p_effective_date in date
256 ,p_rec in pay_peu_shd.g_rec_type
257 ) is
258 l_proc varchar2(72) := g_package||'chk_event_type';
259 --
260 Begin
261 --
262 hr_utility.set_location('Entering:'||l_proc, 5);
263 --
264 hr_api.mandatory_arg_error
265 (p_api_name => l_proc
266 ,p_argument => 'event_type'
267 ,p_argument_value => p_rec.event_type
268 );
269 --
270 if hr_api.not_exists_in_hrstanlookups(p_effective_date => p_effective_date
271 ,p_lookup_type => 'EVENT_TYPE'
272 ,p_lookup_code => p_rec.event_type) then
273 --
274 -- The event_type for this record is not recognised
275 --
276 fnd_message.set_name('PAY','HR_xxxx_INVALID_EVENT_TYPE');
277 fnd_message.raise_error;
278 end if;
279 hr_utility.set_location(l_proc,30);
280 --
281 -- Set the global variables so the values are
282 -- available for the next call to this function.
283 --
284 end chk_event_type;
285 --
286 -- ----------------------------------------------------------------------------
287 -- |---------------------------< chk_change_type>----------------------------|
288 -- ----------------------------------------------------------------------------
289 Procedure chk_change_type
290 (p_effective_date in date
291 ,p_rec in pay_peu_shd.g_rec_type
292 ) IS
293 --
294 l_proc varchar2(72) := g_package || 'chk_change_type';
295 l_error EXCEPTION;
296 l_argument varchar2(30);
297 --
298 --
299 Begin
300 --
301 if hr_api.not_exists_in_hrstanlookups(p_effective_date => p_effective_date
302 ,p_lookup_type => 'PROCESS_EVENT_TYPE'
303 ,p_lookup_code => p_rec.change_type) then
304 --
305 -- The change_type for this record is not recognised
306 --
307 fnd_message.set_name('PAY','HR_xxxx_INVALID_CHANGE_TYPE');
308 fnd_message.raise_error;
309 end if;
310 hr_utility.set_location(l_proc,30);
311 --
312 -- Set the global variables so the values are
313 -- available for the next call to this function.
314 --
315 end chk_change_type;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |---------------------------< chk_column >----------------------------|
319 -- ----------------------------------------------------------------------------
320 -- {Start of comments}
321 --
322 -- Description:
323 -- This procedure validates the column_name is a column
324 -- which exist on the table referred to
325 --
326 -- Prerequisites:
327 --
328 -- In Parameters:
329 -- A Pl/Sql record structure.
330 --
331 -- Post Success:
332 -- Processing continues.
333 --
334 -- Post Failure:
335 -- Error if column not recognised.
336 --
337 -- Access Status:
338 -- Internal Row Handler Use Only.
339 --
340 -- {End of comments}
341 --
342 Procedure chk_column
343 (p_rec in pay_peu_shd.g_rec_type
344 ) is
345 --
346 l_proc varchar2(72) := g_package||'chk_column';
347 l_error EXCEPTION;
348 l_argument varchar2(30);
349 l_dummy number(1);
350 --
351 cursor csr_chk_column is
352 select 1
353 from dual
354 where exists (
355 select 1
356 from pay_dated_tables dt,
357 fnd_tables tab,
358 fnd_columns col
359 where dt.dated_table_id = p_rec.dated_table_id
360 and dt.table_name = tab.table_name
361 and tab.table_id = col.table_id
362 and col.column_name = p_rec.column_name
363 );
364 --
365 Begin
366 --
367 hr_utility.set_location('Entering:'||l_proc, 5);
368 --
369 If (p_rec.event_type = 'U')
370 Then
371 hr_api.mandatory_arg_error
372 (p_api_name => l_proc
373 ,p_argument => 'COLUMN_NAME'
374 ,p_argument_value => p_rec.column_name
375 );
376 --
377 Open csr_chk_column;
378 Fetch csr_chk_column Into l_dummy;
379 If csr_chk_column%notfound Then
380 Close csr_chk_column;
381 --
382 -- The column does not belong to the table therefore we must error
383 --
384 fnd_message.set_name('PAY', 'HR_xxxx_INVALID_COLUMN_NAME');
385
386 fnd_message.raise_error;
387 End If;
388 Close csr_chk_column;
389 End If;
390 --
391 --
392 hr_utility.set_location(' Leaving:'||l_proc, 10);
393 end chk_column;
394 -- Bug no. 3526519. Added check procedure for duplicate check.
395 --
396 -- ----------------------------------------------------------------------------
397 -- |-------------------------< chk_unique_rules >-----------------------------|
398 -- ----------------------------------------------------------------------------
399 --
400 -- Description:
401 -- This procedure is used to check whether the Row Level Event is unique or
402 -- not
403 -- ----------------------------------------------------------------------------
404 Procedure chk_unique_rules
405 (p_rec in pay_peu_shd.g_rec_type
406 ) is
407 --
408 l_proc varchar2(72) := g_package||'chk_unique_rules';
409 l_exists varchar2(1);
410 l_event_update_id number;
411 --
412 cursor c_duplicate_row
413 is
414 select '1'
415 from pay_event_updates
416 where nvl(table_name,'~') = nvl(p_rec.table_name,'~')
417 and nvl(event_type,'~') = nvl(p_rec.event_type,-1)
418 and nvl(column_name,'~') = nvl(p_rec.column_name,'~')
419 and change_type = p_rec.change_type
420 and ((legislation_code =
421 nvl(p_rec.legislation_code,hr_api.return_legislation_code(p_rec.business_group_id)))
422 or ( legislation_code is null and
423 business_group_id = p_rec.business_group_id)
424 or ( legislation_code is null and
425 business_group_id is null));
426 --
427 begin
428 --
429 hr_utility.set_location('Entering:'||l_proc, 1);
430 hr_utility.set_location('Business_group_id :'||p_rec.business_Group_id, 2);
431 hr_utility.set_location('legislation_code :'||p_rec.legislation_code, 3);
432 hr_utility.set_location('event_type :'||p_rec.event_type, 4);
433 hr_utility.set_location('column_name :'||p_rec.column_name, 5);
434 hr_utility.set_location('change_type :'||p_rec.change_type, 6);
435 hr_utility.set_location('event_type :'||p_rec.table_name, 7);
436 --
437 open c_duplicate_row;
438 fetch c_duplicate_row into l_exists;
439 if c_duplicate_row%found then
440 --
441 close c_duplicate_row;
442 fnd_message.set_name('PAY', 'PAY_33272_ROW_EVENT_NOT_UNIQUE');
443 fnd_message.raise_error;
444 --
445 End If;
446 --
447 close c_duplicate_row;
448 hr_utility.set_location('Leaving:'||l_proc, 2);
449 End chk_unique_rules;
450 --
451 -- ----------------------------------------------------------------------------
452 -- |---------------------------< insert_validate >----------------------------|
453 -- ----------------------------------------------------------------------------
454 Procedure insert_validate
455 (p_effective_date in date
456 ,p_rec in pay_peu_shd.g_rec_type
457 ) is
458 --
459 l_proc varchar2(72) := g_package||'insert_validate';
460 --
461 Begin
462 hr_utility.set_location('Entering:'||l_proc, 5);
463 --
464 -- Call all supporting business operations
465 -- Commenting out the validate bus grp operation as bus grp can
466 -- take a null value.
467 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
468 --
469 chk_change_type (p_effective_date => p_effective_date
470 ,p_rec => p_rec);
471 --
472 chk_event_type (p_effective_date => p_effective_date
473 ,p_rec => p_rec);
474 --
475 chk_column (p_rec => p_rec);
476 --
477 -- Bug no. 3526519. call of check procedure for duplicate check.
478 chk_unique_rules(p_rec => p_rec);
479 --
480 hr_utility.set_location(' Leaving:'||l_proc, 10);
481 End insert_validate;
482 --
483 -- ----------------------------------------------------------------------------
484 -- |---------------------------< update_validate >----------------------------|
485 -- ----------------------------------------------------------------------------
486 Procedure update_validate
487 (p_effective_date in date
488 ,p_rec in pay_peu_shd.g_rec_type
489 ) is
490 --
491 l_proc varchar2(72) := g_package||'update_validate';
492 --
493 Begin
494 hr_utility.set_location('Entering:'||l_proc, 5);
495 --
496 -- Call all supporting business operations
497 -- Commenting out the validate bus grp operation as bus grp can
498 -- take a null value.
499 --
500 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
501 --
502 chk_non_updateable_args
503 (p_effective_date => p_effective_date
504 ,p_rec => p_rec
505 );
506 --
507 chk_change_type (p_effective_date => p_effective_date
508 ,p_rec => p_rec);
509 --
510 chk_event_type (p_effective_date => p_effective_date
511 ,p_rec => p_rec);
512 --
513 chk_column (p_rec => p_rec);
514 --
515 -- Bug no. 3526519. call of check procedure for duplicate check.
516 chk_unique_rules(p_rec => p_rec);
517 --
518 hr_utility.set_location(' Leaving:'||l_proc, 10);
519 End update_validate;
520 --
521 -- ----------------------------------------------------------------------------
522 -- |---------------------------< delete_validate >----------------------------|
523 -- ----------------------------------------------------------------------------
524 Procedure delete_validate
525 (p_rec in pay_peu_shd.g_rec_type
526 ) is
527 --
528 l_proc varchar2(72) := g_package||'delete_validate';
529 --
530 Begin
531 hr_utility.set_location('Entering:'||l_proc, 5);
532 --
533 -- Call all supporting business operations
534 --
535 hr_utility.set_location(' Leaving:'||l_proc, 10);
536 End delete_validate;
537 --
538 end pay_peu_bus;