1 Package Body pay_spr_bus as
2 /* $Header: pysprrhi.pkb 120.0 2005/05/29 08:54:06 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_spr_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_security_profile_id number default null;
15 g_payroll_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_security_profile_id in number
23 ,p_payroll_id in number
24 ,p_associated_column1 in varchar2 default null
25 ,p_associated_column2 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30 cursor csr_sec_grp is
31 select pbg.security_group_id
32 from per_business_groups pbg
33 , pay_security_payrolls spr
34 where spr.security_profile_id = p_security_profile_id
35 and spr.payroll_id = p_payroll_id
36 and pbg.business_group_id = spr.business_group_id;
37 --
38 -- Declare local variables
39 --
40 l_security_group_id number;
41 l_proc varchar2(72) := g_package||'set_security_group_id';
42 --
43 begin
44 --
45 hr_utility.set_location('Entering:'|| l_proc, 10);
46 --
47 -- Ensure that all the mandatory parameter are not null
48 --
49 hr_api.mandatory_arg_error
50 (p_api_name => l_proc
51 ,p_argument => 'security_profile_id'
52 ,p_argument_value => p_security_profile_id
53 );
54 hr_api.mandatory_arg_error
55 (p_api_name => l_proc
56 ,p_argument => 'payroll_id'
57 ,p_argument_value => p_payroll_id
58 );
59 --
60 open csr_sec_grp;
61 fetch csr_sec_grp into l_security_group_id;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 hr_multi_message.add
71 (p_associated_column1
72 => nvl(p_associated_column1,'SECURITY_PROFILE_ID')
73 ,p_associated_column2
74 => nvl(p_associated_column2,'PAYROLL_ID')
75 );
76 --
77 else
78 close csr_sec_grp;
79 --
80 -- Set the security_group_id in CLIENT_INFO
81 --
82 hr_api.set_security_group_id
83 (p_security_group_id => l_security_group_id
84 );
85 end if;
86 --
87 hr_utility.set_location(' Leaving:'|| l_proc, 20);
88 --
89 end set_security_group_id;
90 --
91 -- ---------------------------------------------------------------------------
92 -- |---------------------< return_legislation_code >-------------------------|
93 -- ---------------------------------------------------------------------------
94 --
95 Function return_legislation_code
96 (p_security_profile_id in number
97 ,p_payroll_id in number
98 )
99 Return Varchar2 Is
100 --
101 -- Declare cursor
102 --
103 cursor csr_leg_code is
104 select pbg.legislation_code
105 from per_business_groups pbg
106 , pay_security_payrolls spr
107 where spr.security_profile_id = p_security_profile_id
108 and spr.payroll_id = p_payroll_id
109 and pbg.business_group_id = spr.business_group_id;
110 --
111 -- Declare local variables
112 --
113 l_legislation_code varchar2(150);
114 l_proc varchar2(72) := g_package||'return_legislation_code';
115 --
116 Begin
117 --
118 hr_utility.set_location('Entering:'|| l_proc, 10);
119 --
120 -- Ensure that all the mandatory parameter are not null
121 --
122 hr_api.mandatory_arg_error
123 (p_api_name => l_proc
124 ,p_argument => 'security_profile_id'
125 ,p_argument_value => p_security_profile_id
126 );
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'payroll_id'
130 ,p_argument_value => p_payroll_id
131 );
132 --
133 if (( nvl(pay_spr_bus.g_security_profile_id, hr_api.g_number)
134 = p_security_profile_id)
135 and ( nvl(pay_spr_bus.g_payroll_id, hr_api.g_number)
136 = p_payroll_id)) then
137 --
138 -- The legislation code has already been found with a previous
139 -- call to this function. Just return the value in the global
140 -- variable.
141 --
142 l_legislation_code := pay_spr_bus.g_legislation_code;
143 hr_utility.set_location(l_proc, 20);
144 else
145 --
146 -- The ID is different to the last call to this function
147 -- or this is the first call to this function.
148 --
149 open csr_leg_code;
150 fetch csr_leg_code into l_legislation_code;
151 --
152 if csr_leg_code%notfound then
153 --
154 -- The primary key is invalid therefore we must error
155 --
156 close csr_leg_code;
157 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
158 fnd_message.raise_error;
159 end if;
160 hr_utility.set_location(l_proc,30);
161 --
162 -- Set the global variables so the values are
163 -- available for the next call to this function.
164 --
165 close csr_leg_code;
166 pay_spr_bus.g_security_profile_id := p_security_profile_id;
167 pay_spr_bus.g_payroll_id := p_payroll_id;
168 pay_spr_bus.g_legislation_code := l_legislation_code;
169 end if;
170 hr_utility.set_location(' Leaving:'|| l_proc, 40);
171 return l_legislation_code;
172 end return_legislation_code;
173 --
174 -- --------------------------------------------------------------------------
175 -- |---------------------------< chk_payroll_id >---------------------------|
176 -- --------------------------------------------------------------------------
177 Procedure chk_payroll_id
178 (p_payroll_id in pay_all_payrolls_f.payroll_id%type
179 ,p_security_profile_id in pay_security_payrolls.security_profile_id%type
180 ,p_business_group_id out nocopy pay_all_payrolls_f.business_group_id%type)
181 --
182 is
183 --
184 l_proc varchar2(80) := g_package||'chk_payroll_id';
185 --
186 Begin
187 hr_utility.set_location('Entering:'||l_proc, 5);
188
189 hr_utility.set_location(to_char(p_payroll_id),10);
190 hr_utility.set_location(to_char(p_security_profile_id),15);
191
192 --Ensure that the payroll_id and security_profile_id are valid
193
194 Select distinct pay_all_payrolls_f.business_group_id
195 Into p_business_group_id
196 From per_security_profiles, pay_all_payrolls_f
197 Where pay_all_payrolls_f.business_group_id =
198 per_security_profiles.business_group_id
199 And pay_all_payrolls_f.payroll_id = p_payroll_id
200 And per_security_profiles.security_profile_id = p_security_profile_id;
201
202 Exception
203 When no_data_found then
204 fnd_message.set_name('PER', 'HR_289801_INVALID_PAYROLL_ID');
205 fnd_message.raise_error;
206
207 End chk_payroll_id;
208 --
209 -- --------------------------------------------------------------------------
210 -- |-------------------------<chk_security_profile>-------------------------|
211 -- --------------------------------------------------------------------------
212 Procedure chk_security_profile
213 (p_security_profile_id in per_security_profiles.security_profile_id%type
214 ,p_business_group_id out nocopy per_security_profiles.business_group_id%type)
215 --
216 is
217 --
218 e_null_business_group_id Exception;
219 l_proc varchar2(80) := g_package||'chk_security_profile';
220 --
221 Begin
222 hr_utility.set_location('Entering:'||l_proc, 5);
223
224 --Ensure that the security profile id is not global
225
226 Select business_group_id
227 Into p_business_group_id
228 From per_security_profiles
229 Where security_profile_id = p_security_profile_id;
230 hr_utility.set_location(to_char(p_business_group_id),15);
231 If p_business_group_id is null then
232 hr_utility.set_location(to_char(p_business_group_id),16);
233 Raise e_null_business_group_id;
234 End if;
235
236 Exception
237 When e_null_business_group_id then
238 fnd_message.set_name ('PER', 'HR_289800_GLOBAL_SEC_PROFILE');
239 fnd_message.raise_error;
240
241 When no_data_found then
242 fnd_message.set_name ('PER', 'HR_289799_INVALID_SEC_PROFILE');
243 fnd_message.raise_error;
244 hr_utility.set_location('Leaving:'||l_proc, 10);
245 End chk_security_profile;
246 --
247 -- --------------------------------------------------------------------------
248 -- |-------------------------<chk_for_duplicate>----------------------------|
249 -- --------------------------------------------------------------------------
250 --
251 PROCEDURE chk_for_duplicate
252 (p_security_profile_id IN NUMBER
253 ,p_payroll_id IN NUMBER)
254 IS
255
256 l_proc VARCHAR2(80) := g_package||'chk_for_duplicate';
257 l_dummy NUMBER;
258
259 --
260 -- Check that this payroll does not already exist in this security profile.
261 --
262 CURSOR csr_chk_for_dup IS
263 SELECT NULL
264 FROM pay_security_payrolls spr
265 WHERE spr.security_profile_id = p_security_profile_id
266 AND spr.payroll_id = p_payroll_id;
267
268 BEGIN
269
270 hr_utility.set_location('Entering:'||l_proc, 10);
271
272 --
273 -- Only proceed with validation when the Multiple Message List
274 -- does not already contain an error associated with the
275 -- below columns.
276 --
277 IF hr_multi_message.no_exclusive_error
278 (p_check_column1 => pay_spr_shd.g_tab_nam||'.SECURITY_PROFILE_ID'
279 ,p_associated_column1 => pay_spr_shd.g_tab_nam||'.SECURITY_PROFILE_ID'
280 ,p_check_column2 => pay_spr_shd.g_tab_nam||'.PAYROLL_ID'
281 ,p_associated_column2 => pay_spr_shd.g_tab_nam||'.PAYROLL_ID')
282 THEN
283
284 hr_utility.set_location(l_proc, 20);
285
286 OPEN csr_chk_for_dup;
287 FETCH csr_chk_for_dup INTO l_dummy;
288
289 IF csr_chk_for_dup%FOUND THEN
290 --
291 -- This security profile already has this payroll; raise an error.
292 --
293 hr_utility.set_location(l_proc, 30);
294 CLOSE csr_chk_for_dup;
295 fnd_message.set_name('PER','PER_7061_DEF_SECPROF_PAY_EXIST');
296 fnd_message.raise_error;
297
298 END IF;
299 CLOSE csr_chk_for_dup;
300
301 hr_utility.set_location('Leaving:'||l_proc, 40);
302
303 END IF;
304
305 EXCEPTION
306 --
307 -- Multiple Error Detection is enabled so handle the Application Errors
308 -- which have been raised by this procedure. Transfer the error to the
309 -- Multiple Message List and associate the error with the above columns.
310 --
311 WHEN app_exception.application_exception THEN
312
313 IF hr_multi_message.exception_add
314 (p_same_associated_columns => 'Y') THEN
315
316 hr_utility.set_location(' Leaving:'|| l_proc, 50);
317 RAISE;
318
319 END IF;
320 hr_utility.set_location(' Leaving:'|| l_proc, 60);
321
322 END chk_for_duplicate;
323 --
324 -- --------------------------------------------------------------------------
325 -- |-------------------------<chk_view_all_payrolls_flag>-------------------|
326 -- --------------------------------------------------------------------------
327 --
328 PROCEDURE chk_view_all_payrolls_flag
329 (p_security_profile_id IN NUMBER)
330 IS
331
332 l_proc VARCHAR2(80) := g_package||'chk_view_all_payrolls_flag';
333 l_view_all_payrolls_flag per_security_profiles.view_all_payrolls_flag%TYPE;
334
335 --
336 -- Fetches the view_all_payrolls_flag.
337 --
338 CURSOR csr_get_view_all_payrolls IS
339 SELECT psp.view_all_payrolls_flag
340 FROM per_security_profiles psp
341 WHERE psp.security_profile_id = p_security_profile_id;
342
343 BEGIN
344
345 hr_utility.set_location('Entering:'||l_proc, 10);
346
347 --
348 -- Only proceed with validation when the Multiple Message List
349 -- does not already contain an error associated with the
350 -- below columns.
351 --
352 IF hr_multi_message.no_exclusive_error
353 (p_check_column1 => pay_spr_shd.g_tab_nam||'.SECURITY_PROFILE_ID'
354 ,p_associated_column1 => pay_spr_shd.g_tab_nam||'.SECURITY_PROFILE_ID')
355 THEN
356
357 hr_utility.set_location(l_proc, 20);
358
359 OPEN csr_get_view_all_payrolls;
360 FETCH csr_get_view_all_payrolls INTO l_view_all_payrolls_flag;
361 CLOSE csr_get_view_all_payrolls;
362
363 IF l_view_all_payrolls_flag <> 'N' THEN
364 --
365 -- Payrolls cannot be added for this profile because it is set to
366 -- "View All Payrolls."
367 --
368 hr_utility.set_location(l_proc, 30);
369 fnd_message.set_name('PER','HR_289830_SPR_VIEW_ALL_PAY_SET');
370 fnd_message.raise_error;
371
372 END IF;
373
374 hr_utility.set_location('Leaving:'||l_proc, 40);
375
376 END IF;
377
378 EXCEPTION
379 --
380 -- Multiple Error Detection is enabled so handle the Application Errors
381 -- which have been raised by this procedure. Transfer the error to the
385
382 -- Multiple Message List and associate the error with the above columns.
383 --
384 WHEN app_exception.application_exception THEN
386 IF hr_multi_message.exception_add
387 (p_same_associated_columns => 'Y') THEN
388
389 hr_utility.set_location(' Leaving:'|| l_proc, 50);
390 RAISE;
391
392 END IF;
393 hr_utility.set_location(' Leaving:'|| l_proc, 60);
394
395 END chk_view_all_payrolls_flag;
396 --
397 -- --------------------------------------------------------------------------
398 -- |-------------------------<set_view_all_payrolls_flag>-------------------|
399 -- --------------------------------------------------------------------------
400 --
401 PROCEDURE set_view_all_payrolls_flag
402 (p_security_profile_id IN NUMBER)
403 IS
404
405 l_proc VARCHAR2(80) := g_package||'set_view_all_payrolls_flag';
406 l_dummy NUMBER;
407
408 --
409 -- Fetches the security payrolls.
410 --
411 CURSOR csr_get_security_payrolls IS
412 SELECT NULL
413 FROM pay_security_payrolls spr
414 WHERE spr.security_profile_id = p_security_profile_id;
415
416 BEGIN
417
418 hr_utility.set_location('Entering:'||l_proc, 10);
419
420 OPEN csr_get_security_payrolls;
421 FETCH csr_get_security_payrolls INTO l_dummy;
422
423 IF csr_get_security_payrolls%NOTFOUND THEN
424 --
425 -- Update the view all payrolls flag on the Security Profile.
426 --
427 hr_utility.set_location(l_proc, 30);
428
429 UPDATE per_security_profiles
430 SET view_all_payrolls_flag = 'Y'
431 WHERE security_profile_id = p_security_profile_id;
432
433 END IF;
434
435 CLOSE csr_get_security_payrolls;
436
437 hr_utility.set_location('Leaving:'||l_proc, 40);
438
439 END set_view_all_payrolls_flag;
440 --
441 -- ----------------------------------------------------------------------------
442 -- |-----------------------< chk_non_updateable_args >------------------------|
443 -- ----------------------------------------------------------------------------
444 -- {Start Of Comments}
445 --
446 -- Description:
447 -- This procedure is used to ensure that non updateable attributes have
448 -- not been updated. If an attribute has been updated an error is generated.
449 --
450 -- Pre Conditions:
451 -- g_old_rec has been populated with details of the values currently in
452 -- the database.
453 --
454 -- In Arguments:
455 -- p_rec has been populated with the updated values the user would like the
456 -- record set to.
457 --
458 -- Post Success:
459 -- Processing continues if all the non updateable attributes have not
460 -- changed.
461 --
462 -- Post Failure:
463 -- An application error is raised if any of the non updatable attributes
464 -- have been altered.
465 --
466 -- {End Of Comments}
467 -- ----------------------------------------------------------------------------
468 Procedure chk_non_updateable_args
469 (p_effective_date in date
470 ,p_rec in pay_spr_shd.g_rec_type
471 ) IS
472 --
473 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
474 --
475 Begin
476 --
477 -- Only proceed with the validation if a row exists for the current
478 -- record in the HR Schema.
479 --
480 IF NOT pay_spr_shd.api_updating
481 (p_security_profile_id => p_rec.security_profile_id
482 ,p_payroll_id => p_rec.payroll_id
483 ,p_object_version_number => p_rec.object_version_number
484 ) THEN
485 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
486 fnd_message.set_token('PROCEDURE ', l_proc);
487 fnd_message.set_token('STEP ', '5');
488 fnd_message.raise_error;
489 END IF;
490 --
491 End chk_non_updateable_args;
492 --
493 -- ----------------------------------------------------------------------------
494 -- |---------------------------< insert_validate >----------------------------|
495 -- ----------------------------------------------------------------------------
496 Procedure insert_validate
497 (p_effective_date in date
498 ,p_rec in pay_spr_shd.g_rec_type
499 ) is
500 --
501 l_proc varchar2(72) := g_package||'insert_validate';
502 --
503
504 Begin
505 hr_utility.set_location('Entering:'||l_proc, 5);
506 --
507 -- Call all supporting business operations
508 --
509 hr_api.validate_bus_grp_id
510 (p_business_group_id => p_rec.business_group_id
511 ,p_associated_column1 => pay_spr_shd.g_tab_nam
512 || '.BUSINESS_GROUP_ID');
513
514 -- After validating the set of important attributes,
515 -- if Multiple Message detection is enabled and at least
516 -- one error has been found then abort further validation.
517 --
518 hr_multi_message.end_validation_set;
519
520 --
521 -- Validate Dependent Attributes.
522 -- First check for a duplicate payroll in this security profile.
523 --
524 chk_for_duplicate
525 (p_security_profile_id => p_rec.security_profile_id
526 ,p_payroll_id => p_rec.payroll_id);
527
528 --
529 -- Check that the view all payrolls flag is correct.
530 --
531 chk_view_all_payrolls_flag
532 (p_security_profile_id => p_rec.security_profile_id);
533
534 hr_utility.set_location(' Leaving:'||l_proc, 10);
535
536 End insert_validate;
537 --
538 -- ----------------------------------------------------------------------------
539 -- |---------------------------< delete_validate >----------------------------|
540 -- ----------------------------------------------------------------------------
541 Procedure delete_validate
542 (p_rec in pay_spr_shd.g_rec_type
543 ) is
544 --
545 l_proc varchar2(72) := g_package||'delete_validate';
546 --
547 Begin
548 hr_utility.set_location('Entering:'||l_proc, 5);
549 --
550 -- Call all supporting business operations
551 --
552 hr_utility.set_location(' Leaving:'||l_proc, 10);
553 End delete_validate;
554 --
555 --
556 end pay_spr_bus;
[Home] [Help]