[Home] [Help]
PACKAGE BODY: APPS.PQP_GAP_BUS
Source
1 Package Body pqp_gap_bus as
2 /* $Header: pqgaprhi.pkb 120.1 2005/06/30 12:04:22 rpinjala noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_gap_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_gap_absence_plan_id number default null;
14 g_legislation_code varchar2(150) default null;
15 g_assignment_id number default null;
16
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_gap_absence_plan_id in number
24 ,p_associated_column1 in varchar2 default null
25 ) is
26 --
27 -- Declare cursor
28 --
29 cursor csr_sec_grp is
30 select pbg.security_group_id,
31 pbg.legislation_code
32 from pqp_gap_absence_plans gap
33 , per_all_assignments_f paa
34 , per_business_groups_perf pbg
35 where gap.gap_absence_plan_id = p_gap_absence_plan_id
36 and paa.assignment_id = gap.assignment_id
37 and pbg.business_group_id = paa.business_group_id;
38 --
39 -- Declare local variables
40 --
41 l_security_group_id number;
42 l_proc varchar2(72) := g_package||'set_security_group_id';
43 l_legislation_code varchar2(150);
44 --
45 begin
46 --
47 hr_utility.set_location('Entering:'|| l_proc, 10);
48 --
49 -- Ensure that all the mandatory parameter are not null
50 --
51 hr_api.mandatory_arg_error
52 (p_api_name => l_proc
53 ,p_argument => 'gap_absence_plan_id'
54 ,p_argument_value => p_gap_absence_plan_id
55 );
56 --
57 open csr_sec_grp;
58 fetch csr_sec_grp into l_security_group_id
59 , l_legislation_code;
60 --
61 if csr_sec_grp%notfound then
62 --
63 close csr_sec_grp;
64 --
65 -- The primary key is invalid therefore we must error
66 --
67 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
68 hr_multi_message.add
69 (p_associated_column1
70 => nvl(p_associated_column1,'GAP_ABSENCE_PLAN_ID')
71 );
72 --
73 else
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 -- Set the sessions legislation context in HR_SESSION_DATA
83 --
84 hr_api.set_legislation_context(l_legislation_code);
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_assignment_id in number
97 )
98 Return Varchar2 Is
99 --
100 -- Declare cursor
101 --
102 cursor csr_leg_code is
103 select pbg.legislation_code
104 from per_all_assignments_f paa
105 , per_business_groups_perf pbg
106 where paa.assignment_id = p_assignment_id
107 and pbg.business_group_id = paa.business_group_id;
108 --
109 -- Declare local variables
110 --
111 l_legislation_code varchar2(150);
112 l_proc varchar2(72) := g_package||'return_legislation_code';
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'|| l_proc, 10);
117 --
118 -- Ensure that all the mandatory parameter are not null
119 --
120 hr_api.mandatory_arg_error
121 (p_api_name => l_proc
122 ,p_argument => 'p_assignment_id'
123 ,p_argument_value => p_assignment_id
124 );
125 --
126 if ( nvl(pqp_gap_bus.g_assignment_id, hr_api.g_number)
127 = p_assignment_id) then
128 --
129 -- The legislation code has already been found with a previous
130 -- call to this function. Just return the value in the global
131 -- variable.
132 --
133 l_legislation_code := pqp_gap_bus.g_legislation_code;
134 hr_utility.set_location(l_proc, 20);
135 else
136 --
137 -- The ID is different to the last call to this function
138 -- or this is the first call to this function.
139 --
140 open csr_leg_code;
141 fetch csr_leg_code into l_legislation_code;
142 --
143 if csr_leg_code%notfound then
144 --
145 -- The primary key is invalid therefore we must error
146 --
147 close csr_leg_code;
148 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
149 fnd_message.raise_error;
150 end if;
151 hr_utility.set_location(l_proc,30);
152 --
153 -- Set the global variables so the values are
154 -- available for the next call to this function.
155 --
156 close csr_leg_code;
157 pqp_gap_bus.g_assignment_id := p_assignment_id;
158 pqp_gap_bus.g_legislation_code := l_legislation_code;
159 end if;
160 hr_utility.set_location(' Leaving:'|| l_proc, 40);
161 return l_legislation_code;
162 end return_legislation_code;
163 --
164 -- ---------------------------------------------------------------------------
165 -- |---------------------< return_gap_legislation_code >---------------------|
166 -- ---------------------------------------------------------------------------
167 --
168 Function return_gap_legislation_code
169 (p_gap_absence_plan_id in number
170 )
171 Return Varchar2 Is
172 --
173 -- Declare cursor
174 --
175 cursor csr_leg_code is
176 select pbg.legislation_code
177 from pqp_gap_absence_plans gap
178 , per_all_assignments_f paa
179 , per_business_groups_perf pbg
180 where gap.gap_absence_plan_id = p_gap_absence_plan_id
181 and paa.assignment_id = gap.assignment_id
182 and pbg.business_group_id = paa.business_group_id;
183 --
184 -- Declare local variables
185 --
186 l_legislation_code varchar2(150);
187 l_proc varchar2(72) := g_package||'return_legislation_code';
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'|| l_proc, 10);
192 --
193 -- Ensure that all the mandatory parameter are not null
194 --
195 hr_api.mandatory_arg_error
196 (p_api_name => l_proc
197 ,p_argument => 'gap_absence_plan_id'
198 ,p_argument_value => p_gap_absence_plan_id
199 );
200 --
201 if ( nvl(pqp_gap_bus.g_gap_absence_plan_id, hr_api.g_number)
202 = p_gap_absence_plan_id) then
203 --
204 -- The legislation code has already been found with a previous
205 -- call to this function. Just return the value in the global
206 -- variable.
207 --
208 l_legislation_code := pqp_gap_bus.g_legislation_code;
209 hr_utility.set_location(l_proc, 20);
210 else
211 --
212 -- The ID is different to the last call to this function
213 -- or this is the first call to this function.
214 --
215 open csr_leg_code;
216 fetch csr_leg_code into l_legislation_code;
217 --
218 if csr_leg_code%notfound then
219 --
220 -- The primary key is invalid therefore we must error
221 --
222 close csr_leg_code;
223 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
224 fnd_message.raise_error;
225 end if;
226 hr_utility.set_location(l_proc,30);
227 --
228 -- Set the global variables so the values are
229 -- available for the next call to this function.
230 --
231 close csr_leg_code;
232 pqp_gap_bus.g_gap_absence_plan_id := p_gap_absence_plan_id;
233 pqp_gap_bus.g_legislation_code := l_legislation_code;
234 end if;
235 hr_utility.set_location(' Leaving:'|| l_proc, 40);
236 return l_legislation_code;
237 end return_gap_legislation_code;
238
239 --
240 -- ----------------------------------------------------------------------------
241 -- |-----------------------< chk_non_updateable_args >------------------------|
242 -- ----------------------------------------------------------------------------
243 -- {Start Of Comments}
244 --
245 -- Description:
246 -- This procedure is used to ensure that non updateable attributes have
247 -- not been updated. If an attribute has been updated an error is generated.
248 --
249 -- Pre Conditions:
250 -- g_old_rec has been populated with details of the values currently in
251 -- the database.
252 --
253 -- In Arguments:
254 -- p_rec has been populated with the updated values the user would like the
255 -- record set to.
256 --
257 -- Post Success:
258 -- Processing continues if all the non updateable attributes have not
259 -- changed.
260 --
261 -- Post Failure:
262 -- An application error is raised if any of the non updatable attributes
263 -- have been altered.
264 --
265 -- {End Of Comments}
266 -- ----------------------------------------------------------------------------
267 Procedure chk_non_updateable_args
268 (p_effective_date in date
269 ,p_rec in pqp_gap_shd.g_rec_type
270 ) IS
271 --
272 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
273 --
274 Begin
275 --
276 -- Only proceed with the validation if a row exists for the current
277 -- record in the HR Schema.
278 --
279 IF NOT pqp_gap_shd.api_updating
280 (p_gap_absence_plan_id => p_rec.gap_absence_plan_id
281 ,p_object_version_number => p_rec.object_version_number
282 ) THEN
283 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
284 fnd_message.set_token('PROCEDURE ', l_proc);
285 fnd_message.set_token('STEP ', '5');
286 fnd_message.raise_error;
287 END IF;
288 --
289 IF nvl(p_rec.absence_attendance_id, hr_api.g_number) <>
290 nvl(pqp_gap_shd.g_old_rec.absence_attendance_id
291 ,hr_api.g_number
292 ) THEN
293
294 hr_api.argument_changed_error
295 (p_api_name => l_proc
296 ,p_argument => 'ABSENCE_ATTENDANCE_ID'
297 ,p_base_table => pqp_gap_shd.g_tab_nam
298 );
299
300 END IF;
301 --
302 End chk_non_updateable_args;
303
304 --
305 -- ---------------------------------------------------------------------------+
306 -- |-----------------------< chk_absence_attendance_id >----------------------|
307 -- ---------------------------------------------------------------------------+
308 -- {Start Of Comments}
309 --
310 -- Description:
311 -- This procedure is used to validate ABSENCE_ATTENDANCE_ID
312 --
313 -- Pre Conditions:
314 --
315 -- In Arguments:
316 -- p_absence_attendance_id
317 --
318 -- Post Success:
319 -- Processing continues
320 --
321 -- Post Failure:
322 -- An application error will be raised and processing is
323 -- terminated
324 --
325 -- {End Of Comments}
326 -- ---------------------------------------------------------------------------+
327 Procedure chk_absence_attendance_id
328 (p_absence_attendance_id in number
329 ) IS
330 --
331 Cursor ChkAbsAttId is
332 Select 'Y'
333 From per_absence_attendances paa
334 Where paa.absence_attendance_id = p_absence_attendance_id;
335 --
336 l_proc varchar2(72) := g_package || 'chk_absence_attendance_id';
337 l_AAValid char(1) := 'N';
338 --
339 Begin
340 --
341 hr_utility.set_location(' Entering: '|| l_proc, 10);
342 --
343 hr_api.mandatory_arg_error
344 (p_api_name => l_proc
345 ,p_argument => 'absence_attendance_id'
346 ,p_argument_value => p_absence_attendance_id
347 );
348 --
349 hr_utility.set_location(l_proc, 20);
350 --
351 open ChkAbsAttId;
352 fetch ChkAbsAttId into l_AAValid;
353 close ChkAbsAttId;
354 --
355 if l_AAValid = 'N' then
356 -- invalid absence attendance id
357 fnd_message.set_name('PQP', 'PQP_230947_INVALID_PAA_ID');
358 fnd_message.raise_error;
359 end if; -- l_AAValid = 'N'
360 --
361 hr_utility.set_location(' Leaving: '|| l_proc, 30);
362 --
363 end chk_absence_attendance_id;
364 --
365 -- ---------------------------------------------------------------------------+
366 -- |-----------------------< chk_pl_id >--------------------------------------|
367 -- ---------------------------------------------------------------------------+
368 -- {Start Of Comments}
369 --
370 -- Description:
371 -- This procedure is used to validate pl_id
372 --
373 -- Pre Conditions:
374 --
375 -- In Arguments:
376 -- p_pl_id
377 -- p_gap_absence_plan_id
378 --
379 -- Post Success:
380 -- Processing continues
381 --
382 -- Post Failure:
383 -- An application error will be raised and processing is
384 -- terminated
385 --
386 -- {End Of Comments}
387 -- ---------------------------------------------------------------------------+
388 Procedure chk_pl_id
389 (p_gap_absence_plan_id in number
390 ,p_pl_id in number
391 ) IS
392 --
393 Cursor ChkPlId is
394 Select 'Y'
395 From ben_pl_f bp
396 Where bp.pl_id = p_pl_id;
397 --
398 l_proc varchar2(72) := g_package || 'chk_pl_id';
399 l_PlValid char(1) := 'N';
400 --
401 Begin
402 --
403 hr_utility.set_location(' Entering: '|| l_proc, 10);
404 --
405 hr_api.mandatory_arg_error
406 (p_api_name => l_proc
407 ,p_argument => 'pl_id'
408 ,p_argument_value => p_pl_id
409 );
410 --
411 if (((p_gap_absence_plan_id is not null) and
412 nvl(pqp_gap_shd.g_old_rec.pl_id,hr_api.g_number)
413 <> nvl(p_pl_id,hr_api.g_number))
414 or
415 (p_gap_absence_plan_id is null)
416 ) then
417
418 hr_utility.set_location(l_proc, 20);
419 --
420 begin
421 open ChkPlId;
422 fetch ChkPlId into l_PlValid;
423 close ChkPlId;
424 exception
425 when others then
426 l_PlValid := 'N';
427 end;
428
429 if l_PlValid = 'N' then
430
431 -- invalid pl id
432 fnd_message.set_name('PQP', 'PQP_230948_INVALID_PL_ID');
433 fnd_message.raise_error;
434
435 end if; -- l_PlValid = 'N'
436 --
437 hr_utility.set_location(l_proc, 30);
438 --
439 end if; -- (((p_gap_absence_plan_id is not null) and...
440 --
441 hr_utility.set_location(' Leaving: '|| l_proc, 40);
442 --
443 end chk_pl_id;
444 --
445 -- ----------------------------------------------------------------------------
446 -- |---------------------------< insert_validate >----------------------------|
447 -- ----------------------------------------------------------------------------
448 Procedure insert_validate
449 (p_effective_date in date
450 ,p_rec in pqp_gap_shd.g_rec_type
451 ) is
452 --
453 l_proc varchar2(72) := g_package||'insert_validate';
454 --
455 Begin
456 hr_utility.set_location('Entering:'||l_proc, 5);
457 --
458 -- Call all supporting business operations
459 --
460
461 -- Calling set_securit_group_id from row handler of parent
462 -- table PER_ABSENCE_ATTENDANCES as FK is available
463 per_abs_bus.set_security_group_id
464 (p_absence_attendance_id => p_rec.absence_attendance_id
465 );
466
467 --
468 -- Validate Dependent Attributes
469 --
470 chk_absence_attendance_id
471 (p_absence_attendance_id => p_rec.absence_attendance_id
472 );
473 --
474 chk_pl_id
475 (p_gap_absence_plan_id => p_rec.absence_attendance_id
476 ,p_pl_id => p_rec.pl_id
477 );
478 --
479 hr_utility.set_location(' Leaving:'||l_proc, 10);
480 End insert_validate;
481 --
482 -- ----------------------------------------------------------------------------
483 -- |---------------------------< update_validate >----------------------------|
484 -- ----------------------------------------------------------------------------
485 Procedure update_validate
486 (p_effective_date in date
487 ,p_rec in pqp_gap_shd.g_rec_type
488 ) is
489 --
490 l_proc varchar2(72) := g_package||'update_validate';
491 --
492 Begin
493 hr_utility.set_location('Entering:'||l_proc, 5);
494 --
495 -- Call all supporting business operations
496 --
497 pqp_gap_bus.set_security_group_id
498 (p_gap_absence_plan_id => p_rec.gap_absence_plan_id
499 );
500 --
501 -- Validate Dependent Attributes
502 --
503 chk_non_updateable_args
504 (p_effective_date => p_effective_date
505 ,p_rec => p_rec
506 );
507 --
508 chk_absence_attendance_id
509 (p_absence_attendance_id => p_rec.absence_attendance_id
510 );
511 --
512 chk_pl_id
513 (p_gap_absence_plan_id => p_rec.absence_attendance_id
514 ,p_pl_id => p_rec.pl_id
515 );
516 --
517 hr_utility.set_location(' Leaving:'||l_proc, 10);
518 End update_validate;
519 --
520 -- ----------------------------------------------------------------------------
521 -- |---------------------------< delete_validate >----------------------------|
522 -- ----------------------------------------------------------------------------
523 Procedure delete_validate
524 (p_rec in pqp_gap_shd.g_rec_type
525 ) is
526 --
527 l_proc varchar2(72) := g_package||'delete_validate';
528 --
529 Begin
530 hr_utility.set_location('Entering:'||l_proc, 5);
531 --
532 -- Call all supporting business operations
533 --
534 hr_utility.set_location(' Leaving:'||l_proc, 10);
535 End delete_validate;
536 --
537 end pqp_gap_bus;