[Home] [Help]
PACKAGE BODY: APPS.PAY_PTA_BUS
Source
1 Package Body pay_pta_bus as
2 /* $Header: pyptarhi.pkb 120.0 2005/05/29 07:56:20 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pta_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_dated_table_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_dated_table_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_dated_tables pta
30 where pta.dated_table_id = p_dated_table_id
31 and pbg.business_group_id = pta.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 => 'dated_table_id'
47 ,p_argument_value => p_dated_table_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_dated_table_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_dated_tables pta
90 where pta.dated_table_id = p_dated_table_id
91 and pbg.business_group_id (+) = pta.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 => 'dated_table_id'
107 ,p_argument_value => p_dated_table_id
108 );
109 --
110 if ( nvl(pay_pta_bus.g_dated_table_id, hr_api.g_number)
111 = p_dated_table_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_pta_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_pta_bus.g_dated_table_id := p_dated_table_id;
142 pay_pta_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_rec in pay_pta_shd.g_rec_type
177 ) IS
178 --
179 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
180 l_error EXCEPTION;
181 l_argument varchar2(30);
182 --
183 Begin
184 --
185 -- Only proceed with the validation if a row exists for the current
186 -- record in the HR Schema.
187 --
188 IF NOT pay_pta_shd.api_updating
189 (p_dated_table_id => p_rec.dated_table_id
190 ,p_object_version_number => p_rec.object_version_number
191 ) THEN
192 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
193 fnd_message.set_token('PROCEDURE ', l_proc);
194 fnd_message.set_token('STEP ', '5');
195 fnd_message.raise_error;
196 END IF;
197 --
198 if (nvl(p_rec.table_name, hr_api.g_varchar2) <>
199 nvl(pay_pta_shd.g_old_rec.table_name, hr_api.g_varchar2)
200 ) then
201 l_argument := 'table_name';
202 raise l_error;
203 END IF;
204 --
205 if (nvl(p_rec.application_id, hr_api.g_number) <>
206 nvl(pay_pta_shd.g_old_rec.application_id,hr_api.g_number)
207 ) then
208 l_argument := 'application_id';
209 raise l_error;
210 END IF;
211 --
212 if (nvl(p_rec.business_group_id, hr_api.g_number) <>
213 nvl(pay_pta_shd.g_old_rec.business_group_id,hr_api.g_number)
214 ) then
215 l_argument := 'business_group_id';
216 raise l_error;
217 END IF;
218 --
219 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
220 nvl(pay_pta_shd.g_old_rec.legislation_code, hr_api.g_varchar2)
221 then
222 l_argument := 'legislation_code';
223 raise l_error;
224 end if;
225 --
226 EXCEPTION
227 WHEN l_error THEN
228 hr_api.argument_changed_error
229 (p_api_name => l_proc
230 ,p_argument => l_argument);
231 WHEN OTHERS THEN
232 RAISE;
233 End chk_non_updateable_args;
234
235 --
236 -- ----------------------------------------------------------------------------
237 -- |---------------------------<chk_dyn_trigger >----------------------------|
238 -- ----------------------------------------------------------------------------
239 -- {Start of comments}
240 --
241 -- Description:
242 -- This procedure validates the dynamic trigger type passed actually exists
243 -- in the appropriate lookup.
244 --
245 -- In Parameters:
246 -- A Pl/Sql record structure.
247 --
248 -- Post Success:
249 -- Processing continues.
250 --
251 -- Post Failure:
252 -- Error if incorrect value is being attempted to insert.
253 --
254 -- Access Status:
255 -- Internal Row Handler Use Only.
256 --
257 -- {End of comments}
258 --
259 Procedure chk_dyn_trigger
260 (p_rec in pay_pta_shd.g_rec_type
261 ) is
262 --
263 l_proc varchar2(72) := g_package||'chk_dyn_trigger';
264 --
265 Begin
266 hr_utility.set_location('Entering:'||l_proc, 5);
267 --
268 -- Validate against hr_lookups.
269 --
270 if p_rec.dyn_trigger_type is not null then
271 if hr_api.not_exists_in_hr_lookups
272 (p_effective_date => sysdate
273 ,p_lookup_type => 'PAY_DYN_TRIGGER_TYPES'
274 ,p_lookup_code => p_rec.dyn_trigger_type
275 )
276 then
277 hr_utility.set_location(' Leaving:'||l_proc, 10);
278 fnd_message.set_name('PAY', 'INVALID_LOOKUP_CODE');
279 fnd_message.set_token('LOOKUP_TYPE', 'PAY_DYN_TRIGGER_TYPES');
280 fnd_message.set_token('VALUE', p_rec.dyn_trigger_type);
281 fnd_message.raise_error;
282 end if;
283 end if;
284 hr_utility.set_location(' Leaving:'||l_proc, 20);
285 End chk_dyn_trigger;
286
287 --
288 -- ----------------------------------------------------------------------------
289 -- |---------------------------<chk_table_name >----------------------------|
290 -- ----------------------------------------------------------------------------
291 -- {Start of comments}
292 --
293 -- Description:
294 -- This procedure validates the table name passed actually exists
295 -- (EJ:28/4/5) and is in a schema that has dynamic triggers enabled
296 --
297 -- In Parameters:
298 -- A Pl/Sql record structure.
299 --
300 -- Post Success:
301 -- Processing continues.
302 --
303 -- Post Failure:
304 -- Error if column not recognised.
305 --
306 -- Access Status:
307 -- Internal Row Handler Use Only.
308 --
309 -- {End of comments}
310 --
311 Procedure chk_table_name
312 (p_rec in pay_pta_shd.g_rec_type
313 ) is
314 l_proc varchar2(72) := g_package||'chk_table_name';
315 --
316 Begin
317 --
318 hr_utility.set_location('Entering:'||l_proc, 5);
319 --
320 hr_api.mandatory_arg_error
321 (p_api_name => l_proc
322 ,p_argument => 'table_name'
323 ,p_argument_value => p_rec.table_name
324 );
325 --
326 If paywsdyg_pkg.is_table_valid(p_rec.table_name) = 'N' Then
327 --
328 -- The table does not exist and therefore we must error
329 --
330 fnd_message.set_name('PAY', 'HR_xxxx_INVALID_TABLE_NAME');
331
332 fnd_message.raise_error;
333 End If;
334 --
335 hr_utility.set_location(' Leaving:'||l_proc, 10);
336 end chk_table_name;
337
338
339 --
340 -- ----------------------------------------------------------------------------
341 -- |------------------------< chk_dyn_trig_pkg_generated >--------------------|
342 -- ----------------------------------------------------------------------------
343 -- {Start of comments}
344 --
345 -- Description:
346 -- This procedure validates the flag indicator is yes/no
347 --
348 -- In Parameters:
349 -- A Pl/Sql record structure.
350 --
351 -- Post Success:
352 -- Processing continues.
353 --
354 -- Post Failure:
355 -- Error if incorrect value is being attempted to insert.
356 --
357 -- Access Status:
358 -- Internal Row Handler Use Only.
359 --
360 -- {End of comments}
361 --
362 Procedure chk_dyn_trig_pkg_generated
363 (p_rec in pay_pta_shd.g_rec_type
364 ) is
365 --
366 l_proc varchar2(72) := g_package||'chk_dyn_trig_pkg_generated';
367 --
368 Begin
369 hr_utility.set_location('Entering:'||l_proc, 5);
370 --
371 --
372 -- Validate dyn_trig_pkg_generated against hr_lookups.
373 --
374 if p_rec.dyn_trig_pkg_generated is not null then
375 if hr_api.not_exists_in_hr_lookups
376 (p_effective_date => sysdate
377 ,p_lookup_type => 'YES_NO'
378 ,p_lookup_code => p_rec.dyn_trig_pkg_generated
379 )
380 then
381 hr_utility.set_location(' Leaving:'||l_proc, 10);
382 fnd_message.set_name('PAY', 'INVALID_LOOKUP_CODE');
383 fnd_message.set_token('LOOKUP_TYPE', 'YES_NO');
384 fnd_message.set_token('VALUE', p_rec.dyn_trig_pkg_generated);
385 fnd_message.raise_error;
386 end if;
387 end if;
388 hr_utility.set_location(' Leaving:'||l_proc, 20);
389 End chk_dyn_trig_pkg_generated;
390
391
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------------<chk_columns >----------------------------|
395 -- -----------------------------------------------------------------
396 -- {Start of comments}
397 --
398 -- Description:
399 -- This procedure validates the surrogate_key_name, start_date_name
400 -- and end_date_name are columns which exist on the table referred to
401 -- (EJ:28/4/5) and that the table is in a schema for which triggers are allowed
402 --
403 -- Prerequisites:
404 -- The table identified by p_table_name already exists.
405 --
406 -- In Parameters:
407 -- A Pl/Sql record structure.
408 --
409 -- Post Success:
410 -- Processing continues.
411 --
412 -- Post Failure:
413 -- Error if column not recognised.
414 --
415 -- Access Status:
416 -- Internal Row Handler Use Only.
417 --
418 -- {End of comments}
419 --
420 Procedure chk_columns
421 (p_rec in pay_pta_shd.g_rec_type
422 ) is
423 --
424 l_proc varchar2(72) := g_package||'chk_columns';
425 --
426 Begin
427 --
428 hr_utility.set_location('Entering:'||l_proc, 5);
429 --
430 hr_api.mandatory_arg_error
431 (p_api_name => l_proc
432 ,p_argument => 'surrogate_key_name'
433 ,p_argument_value => p_rec.surrogate_key_name
434 );
435 --
436 --Bugfix 3114746
437 -- remove mandatory_arg_error fo start_date_name and end_date_name
438 --
439 If paywsdyg_pkg.is_table_column_valid(p_rec.table_name,p_rec.surrogate_key_name) = 'N' Then
440 --
441 -- The column does not belong to the table therefore we must error
442 --
443 fnd_message.set_name('PAY', 'HR_xxxx_SURROGATE_KEY_NAME');
444
445 fnd_message.raise_error;
446 End If;
447 --
448 --Bugfix 3114746
449 if (p_rec.start_date_name is not null
450 and p_rec.end_date_name is not null) then
451 If paywsdyg_pkg.is_table_column_valid(p_rec.table_name,p_rec.start_date_name) = 'N' Then
452 --
453 -- The column does not belong to the table therefore we must error
454 --
455 fnd_message.set_name('PAY', 'HR_xxxx_START_DATE_NAME');
456
457 fnd_message.raise_error;
458 End If;
459 --
460 If paywsdyg_pkg.is_table_column_valid(p_rec.table_name,p_rec.end_date_name) = 'N' Then
461 --
462 -- The column does not belong to the table therefore we must error
463 --
464 fnd_message.set_name('PAY', 'HR_xxxx_END_DATE_NAME');
465
466 fnd_message.raise_error;
467 End If;
468 --
469 end if;
470 hr_utility.set_location(' Leaving:'||l_proc, 10);
471 end chk_columns;
472 --
473 -- ----------------------------------------------------------------------------
474 -- |---------------------------< insert_validate >----------------------------|
475 -- ----------------------------------------------------------------------------
476 Procedure insert_validate
477 (p_rec in pay_pta_shd.g_rec_type
478 ) is
479 --
480 l_proc varchar2(72) := g_package||'insert_validate';
481 --
482 Begin
483 hr_utility.set_location('Entering:'||l_proc, 5);
484 --
485 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
486 --
487 chk_table_name (p_rec => p_rec);
488 --
489 chk_columns (p_rec => p_rec);
490 --
491 chk_dyn_trigger(p_rec => p_rec);
492 --
493 chk_dyn_trig_pkg_generated(p_rec => p_rec);
494 --
495 hr_utility.set_location(' Leaving:'||l_proc, 10);
496 End insert_validate;
497 --
498 -- ----------------------------------------------------------------------------
499 -- |---------------------------< update_validate >----------------------------|
500 -- ----------------------------------------------------------------------------
501 Procedure update_validate
502 (p_rec in pay_pta_shd.g_rec_type
503 ) is
504 --
505 l_proc varchar2(72) := g_package||'update_validate';
506 --
507 Begin
508 hr_utility.set_location('Entering:'||l_proc, 5);
509 --
510 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
511 --
512 chk_non_updateable_args
513 (p_rec => p_rec
514 );
515 --
516 chk_table_name (p_rec => p_rec);
517 --
518 chk_columns (p_rec => p_rec);
519 --
520 chk_dyn_trigger(p_rec => p_rec);
521 --
522 chk_dyn_trig_pkg_generated(p_rec => p_rec);
523 --
524 hr_utility.set_location(' Leaving:'||l_proc, 10);
525 End update_validate;
526 --
527 -- ----------------------------------------------------------------------------
528 -- |---------------------------< delete_validate >----------------------------|
529 -- ----------------------------------------------------------------------------
530 Procedure delete_validate
531 (p_rec in pay_pta_shd.g_rec_type
532 ) is
533 --
534 l_proc varchar2(72) := g_package||'delete_validate';
535 --
536 Begin
537 hr_utility.set_location('Entering:'||l_proc, 5);
538 --
539 -- Call all supporting business operations
540 --
541 hr_utility.set_location(' Leaving:'||l_proc, 10);
542 End delete_validate;
543 --
544 end pay_pta_bus;