[Home] [Help]
PACKAGE BODY: APPS.PAY_TFU_BUS
Source
1 Package Body pay_tfu_bus as
2 /* $Header: pytfurhi.pkb 120.0 2005/05/29 09:04 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_tfu_bus.'; -- Global package name
9 --
10 -- The following global variables are set by the chk_template_id.
11 --
12 g_legislation_code varchar2(150) default null;
13 g_business_group_id number default null;
14 g_template_type varchar2(150) default null;
15 g_template_id number default null;
16 --
17 -- ----------------------------------------------------------------------------
18 -- |-----------------------< chk_non_updateable_args >------------------------|
19 -- ----------------------------------------------------------------------------
20 -- {Start Of Comments}
21 --
22 -- Description:
23 -- This procedure is used to ensure that non updateable attributes have
24 -- not been updated. If an attribute has been updated an error is generated.
25 --
26 -- Pre Conditions:
27 -- g_old_rec has been populated with details of the values currently in
28 -- the database.
29 --
30 -- In Arguments:
31 -- p_rec has been populated with the updated values the user would like the
32 -- record set to.
33 --
34 -- Post Success:
35 -- Processing continues if all the non updateable attributes have not
36 -- changed.
37 --
38 -- Post Failure:
39 -- An application error is raised if any of the non updatable attributes
40 -- have been altered.
41 --
42 -- {End Of Comments}
43 -- ----------------------------------------------------------------------------
44 Procedure chk_non_updateable_args
45 (p_effective_date in date
46 ,p_rec in pay_tfu_shd.g_rec_type
47 ) IS
48 --
49 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
50 l_argument varchar2(30);
51 l_error exception;
52 --
53 Begin
54 --
55 -- Only proceed with the validation if a row exists for the current
56 -- record in the HR Schema.
57 --
58 IF NOT pay_tfu_shd.api_updating
59 (p_template_ff_usage_id => p_rec.template_ff_usage_id
60 ,p_object_version_number => p_rec.object_version_number
61 ) THEN
62 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
63 fnd_message.set_token('PROCEDURE ', l_proc);
64 fnd_message.set_token('STEP ', '5');
65 fnd_message.raise_error;
66 END IF;
67 --
68 -- p_template_id
69 --
70 if nvl(p_rec.template_id, hr_api.g_number) <>
71 nvl(pay_tfu_shd.g_old_rec.template_id, hr_api.g_number)
72 then
73 l_argument := 'p_template_id';
74 raise l_error;
75 end if;
76 --
77 exception
78 when l_error then
79 hr_utility.set_location('Leaving:'||l_proc, 30);
80 hr_api.argument_changed_error
81 (p_api_name => l_proc
82 ,p_argument => l_argument);
83 when others then
84 raise;
85 End chk_non_updateable_args;
86 -- ----------------------------------------------------------------------------
87 -- |------------------------------< chk_unique >------------------------------|
88 -- ----------------------------------------------------------------------------
89 Procedure chk_unique
90 (p_template_ff_usage_id in number
91 ,p_template_id in number
92 ,p_object_version_number in number
93 ,p_formula_id in number
94 ,p_object_id in number
95 ) is
96 --
97 -- Cursor to check that object_id corresponds to element_type_id for an
98 -- element within the same template.
99 --
100 cursor csr_uniqueness_chk
101 (p_template_ff_usage_id in number
102 ,p_template_id in number
103 ,p_object_id in number
104 ,p_formula_id in number
105 ) is
106 select null
107 from pay_template_ff_usages tfu
108 where tfu.template_id = p_template_id
109 and tfu.template_ff_usage_id <> p_template_ff_usage_id
110 and tfu.object_id = p_object_id
111 and tfu.formula_id = p_formula_id;
112 --
113 l_api_updating boolean;
114 l_proc varchar2(72) := g_package||'chk_unique';
115 l_exists varchar2(1);
116 --
117 Begin
118 l_api_updating := pay_tfu_shd.api_updating
119 (p_template_ff_usage_id => p_template_ff_usage_id
120 ,p_object_version_number => p_object_version_number
121 );
122 if l_api_updating and
123 (
124 nvl(p_object_id, hr_api.g_number) <>
125 nvl(pay_tfu_shd.g_old_rec.object_id, hr_api.g_number) or
126 nvl(p_formula_id, hr_api.g_number) <>
127 nvl(pay_tfu_shd.g_old_rec.formula_id, hr_api.g_number)
128 ) or
129 not l_api_updating then
130 open csr_uniqueness_chk
131 (p_template_ff_usage_id => nvl(p_template_ff_usage_id, hr_api.g_number)
132 ,p_template_id => p_template_id
133 ,p_object_id => p_object_id
134 ,p_formula_id => p_formula_id
135 );
136 fetch csr_uniqueness_chk into l_exists;
137 if csr_uniqueness_chk%found then
138 close csr_uniqueness_chk;
139 fnd_message.set_name('PAY', 'PAY_50207_TFU_FF_USAGE_EXISTS');
140 fnd_message.raise_error;
141 end if;
142 close csr_uniqueness_chk;
143 end if;
144 End chk_unique;
145 -- ----------------------------------------------------------------------------
146 -- |---------------------------< chk_template_id >----------------------------|
147 -- ----------------------------------------------------------------------------
148 Procedure chk_template_id
149 (p_template_id in number
150 ) is
151 --
152 -- Cursor to check that template_id is valid.
153 --
154 cursor csr_template_info is
155 select pet.template_id
156 , pet.template_type
157 , pet.business_group_id
158 , nvl(pbg.legislation_code, pet.legislation_code)
159 from pay_element_templates pet
160 , per_business_groups_perf pbg
161 where pet.template_id = p_template_id
162 and pet.template_type = 'T'
163 and pbg.business_group_id (+)= pet.business_group_id;
164 --
165 l_proc varchar2(72) := g_package||'chk_template_id';
166 --
167 Begin
168 open csr_template_info;
169 fetch csr_template_info
170 into g_template_id
171 , g_template_type
172 , g_business_group_id
173 , g_legislation_code
174 ;
175 if csr_template_info%notfound then
176 close csr_template_info;
177 fnd_message.set_name('PAY', 'PAY_50114_ETM_INVALID_TEMPLATE');
178 fnd_message.raise_error;
179 end if;
180 close csr_template_info;
181 End chk_template_id;
182 -- ----------------------------------------------------------------------------
183 -- |----------------------------< chk_object_id >-----------------------------|
184 -- ----------------------------------------------------------------------------
185 Procedure chk_object_id
186 (p_template_id in number
187 ,p_object_id in number
188 ) is
189 --
190 -- Cursor to check that object_id corresponds to element_type_id for an
191 -- element within the same template.
192 --
193 cursor csr_object_id is
194 select null
195 from pay_shadow_element_types pset
196 where pset.template_id = p_template_id
197 and pset.element_type_id = p_object_id;
198 --
199 l_proc varchar2(72) := g_package||'chk_object_id';
200 l_exists varchar2(1);
201 --
202 Begin
203 --
204 -- Check that template_id is not null.
205 --
206 hr_api.mandatory_arg_error
207 (p_api_name => l_proc
208 ,p_argument => 'p_object_id'
209 ,p_argument_value => p_object_id
210 );
211 --
212 -- Check that object_id is valid.
213 --
214 open csr_object_id;
215 fetch csr_object_id into l_exists;
216 if csr_object_id%notfound then
217 close csr_object_id;
218 fnd_message.set_name('PAY', 'PAY_50208_TFU_INVALID_OBJECT');
219 fnd_message.raise_error;
220 end if;
221 close csr_object_id;
222 End chk_object_id;
223 -- ----------------------------------------------------------------------------
224 -- |-------------------------< chk_exclusion_rule_id >------------------------|
225 -- ----------------------------------------------------------------------------
226 Procedure chk_exclusion_rule_id
227 (p_exclusion_rule_id in number
228 ,p_template_id in number
229 ,p_template_ff_usage_id in number
230 ,p_object_version_number in number
231 ) is
232 --
233 -- Cursor to check that the exclusion_rule_id is valid.
234 --
235 cursor csr_exclusion_rule_id_valid is
236 select null
237 from pay_template_exclusion_rules ter
238 where ter.exclusion_rule_id = p_exclusion_rule_id
239 and ter.template_id = p_template_id;
240 --
241 l_proc varchar2(72) := g_package||'chk_exclusion_rule_id';
242 l_api_updating boolean;
243 l_valid varchar2(1);
244 --
245 Begin
246 --
247 -- Check that exclusion_id is not null.
248 --
249 hr_api.mandatory_arg_error
250 (p_api_name => l_proc
251 ,p_argument => 'exclusion_rule_id'
252 ,p_argument_value => p_exclusion_rule_id
253 );
254 --
255 l_api_updating := pay_tfu_shd.api_updating
256 (p_template_ff_usage_id => p_template_ff_usage_id
257 ,p_object_version_number => p_object_version_number
258 );
259 if (l_api_updating and nvl(p_exclusion_rule_id, hr_api.g_number) <>
260 nvl(pay_tfu_shd.g_old_rec.exclusion_rule_id, hr_api.g_number)) or
261 not l_api_updating
262 then
263 if p_exclusion_rule_id is not null then
264 open csr_exclusion_rule_id_valid;
265 fetch csr_exclusion_rule_id_valid into l_valid;
266 if csr_exclusion_rule_id_valid%notfound then
267 close csr_exclusion_rule_id_valid;
268 fnd_message.set_name('PAY', 'PAY_50100_ETM_INVALID_EXC_RULE');
269 fnd_message.raise_error;
270 end if;
271 close csr_exclusion_rule_id_valid;
272 end if;
273 end if;
274 End chk_exclusion_rule_id;
275 -- ----------------------------------------------------------------------------
276 -- | ------------------------< chk_formula_id >-------------------------------|
277 -- ----------------------------------------------------------------------------
278 Procedure chk_formula_id
279 (p_formula_id in number
280 ,p_template_id in number
281 ,p_template_type in varchar2
282 ,p_business_group_id in number
283 ,p_legislation_code in varchar2
284 ,p_template_ff_usage_id in number
285 ,p_object_version_number in number
286 ) is
287 --
288 --
289 -- Check that the formula is valid.
290 --
291 -- Note: only payroll formulas are supported at this time.
292 --
293 -- The ff usage can only belong to a source template (template type 'T'). It
294 -- may be shared with other templates, but the legislative domain of the
295 -- formula must encompass that of the template.
296 --
297 cursor csr_formula_valid is
298 select null
299 from pay_shadow_formulas sf
300 where sf.formula_id = p_formula_id
301 and sf.template_type = 'T'
302 and nvl(sf.formula_type_name, pay_sf_shd.g_payroll_formula_type) =
303 pay_sf_shd.g_payroll_formula_type
304 and ((sf.legislation_code is null and sf.business_group_id is null) or
305 sf.legislation_code = p_legislation_code or
306 sf.business_group_id = p_business_group_id);
307 --
308 l_proc varchar2(72) := g_package||'chk_formula_id';
309 l_api_updating boolean;
310 l_valid varchar2(1);
311 --
312 Begin
313 l_api_updating := pay_tfu_shd.api_updating
314 (p_template_ff_usage_id => p_template_ff_usage_id
315 ,p_object_version_number => p_object_version_number
316 );
317 if (l_api_updating and nvl(p_formula_id, hr_api.g_number) <>
318 nvl(pay_tfu_shd.g_old_rec.formula_id, hr_api.g_number)) or
319 not l_api_updating
320 then
321 if p_formula_id is not null then
322 open csr_formula_valid;
323 fetch csr_formula_valid into l_valid;
324 if csr_formula_valid%notfound then
325 close csr_formula_valid;
326 fnd_message.set_name('PAY', 'PAY_50209_TFU_INVALID_FORMULA');
327 fnd_message.raise_error;
328 end if;
329 close csr_formula_valid;
330 end if;
331 end if;
332 End chk_formula_id;
333 -- ----------------------------------------------------------------------------
334 -- |---------------------------< insert_validate >----------------------------|
335 -- ----------------------------------------------------------------------------
336 Procedure insert_validate
337 (p_effective_date in date
338 ,p_rec in pay_tfu_shd.g_rec_type
339 ) is
340 --
341 l_proc varchar2(72) := g_package||'insert_validate';
342 l_leg_code varchar2(30);
343 --
344 Begin
345 hr_utility.set_location('Entering:'||l_proc, 5);
346 --
347 -- Call all supporting business operations
348 --
349 -- Validate Dependent Attributes
350 --
351 chk_template_id(p_template_id => p_rec.template_id);
352 --
353 chk_unique
354 (p_template_ff_usage_id => p_rec.template_ff_usage_id
355 ,p_template_id => p_rec.template_id
356 ,p_object_version_number => p_rec.object_version_number
357 ,p_formula_id => p_rec.formula_id
358 ,p_object_id => p_rec.object_id
359 );
360 --
361 chk_object_id
362 (p_template_id => p_rec.template_id
363 ,p_object_id => p_rec.object_id
364 );
365 --
366 chk_formula_id
367 (p_formula_id => p_rec.formula_id
368 ,p_template_id => p_rec.template_id
369 ,p_template_type => pay_tfu_bus.g_template_type
370 ,p_business_group_id => pay_tfu_bus.g_business_group_id
371 ,p_legislation_code => pay_tfu_bus.g_legislation_code
372 ,p_template_ff_usage_id => p_rec.template_ff_usage_id
373 ,p_object_version_number => p_rec.object_version_number
374 );
375 --
376 chk_exclusion_rule_id
377 (p_exclusion_rule_id => p_rec.exclusion_rule_id
378 ,p_template_id => p_rec.template_id
379 ,p_template_ff_usage_id => p_rec.template_ff_usage_id
380 ,p_object_version_number => p_rec.object_version_number
381 );
382 hr_utility.set_location(' Leaving:'||l_proc, 10);
383 End insert_validate;
384 --
385 -- ----------------------------------------------------------------------------
386 -- |---------------------------< update_validate >----------------------------|
387 -- ----------------------------------------------------------------------------
388 Procedure update_validate
389 (p_effective_date in date
390 ,p_rec in pay_tfu_shd.g_rec_type
391 ) is
392 --
393 l_proc varchar2(72) := g_package||'update_validate';
394 --
395 Begin
396 hr_utility.set_location('Entering:'||l_proc, 5);
397 --
398 -- Call all supporting business operations
399 --
400 -- Validate Dependent Attributes
401 --
402 chk_non_updateable_args
403 (p_effective_date => p_effective_date
404 ,p_rec => p_rec
405 );
406 --
407 -- Do the template check again as it sets up the globals used in
408 -- the chk_formula_id call.
409 --
410 chk_template_id(p_template_id => p_rec.template_id);
411 --
412 chk_unique
413 (p_template_ff_usage_id => p_rec.template_ff_usage_id
414 ,p_template_id => p_rec.template_id
415 ,p_object_version_number => p_rec.object_version_number
416 ,p_formula_id => p_rec.formula_id
417 ,p_object_id => p_rec.object_id
418 );
419 --
420 chk_object_id
421 (p_template_id => p_rec.template_id
422 ,p_object_id => p_rec.object_id
423 );
424 --
425 chk_formula_id
426 (p_formula_id => p_rec.formula_id
427 ,p_template_id => p_rec.template_id
428 ,p_template_type => pay_tfu_bus.g_template_type
429 ,p_business_group_id => pay_tfu_bus.g_business_group_id
430 ,p_legislation_code => pay_tfu_bus.g_legislation_code
431 ,p_template_ff_usage_id => p_rec.template_ff_usage_id
432 ,p_object_version_number => p_rec.object_version_number
433 );
434 --
435 chk_exclusion_rule_id
436 (p_exclusion_rule_id => p_rec.exclusion_rule_id
437 ,p_template_id => p_rec.template_id
438 ,p_template_ff_usage_id => p_rec.template_ff_usage_id
439 ,p_object_version_number => p_rec.object_version_number
440 );
441 --
442 hr_utility.set_location(' Leaving:'||l_proc, 10);
443 End update_validate;
444 --
445 -- ----------------------------------------------------------------------------
446 -- |---------------------------< delete_validate >----------------------------|
447 -- ----------------------------------------------------------------------------
448 Procedure delete_validate
449 (p_rec in pay_tfu_shd.g_rec_type
450 ) is
451 --
452 l_proc varchar2(72) := g_package||'delete_validate';
453 --
454 Begin
455 hr_utility.set_location('Entering:'||l_proc, 5);
456 --
457 -- Call all supporting business operations
458 --
459 hr_utility.set_location(' Leaving:'||l_proc, 10);
460 End delete_validate;
461 --
462 end pay_tfu_bus;