1 Package Body pay_rtt_bus as
2 /* $Header: pyrttrhi.pkb 115.4 2003/02/06 17:21:56 rthirlby noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_rtt_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_run_type_id number default null;
15 g_language varchar2(4) default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 /* There are no lookups on this table so this procedure is not required */
22 Procedure set_security_group_id
23 (p_run_type_id in number
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id
30 from per_business_groups pbg
31 , pay_run_types_f_tl rtt
32 where rtt.run_type_id = p_run_type_id;
33 -- and pbg.business_group_id = business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 --
40 begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 /*
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'run_type_id'
50 ,p_argument_value => p_run_type_id
51 );
52 --
53 --
54 open csr_sec_grp;
55 fetch csr_sec_grp into l_security_group_id;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 fnd_message.raise_error;
65 --
66 end if;
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 --
75 */
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_run_type_id in number
86 ,p_language in varchar2
87 )
88 Return Varchar2 Is
89 --
90 -- Declare cursor
91 --
92 -- EDIT_HERE In the following cursor statement add join(s) between
93 -- pay_run_types_f_tl and PER_BUSINESS_GROUPS
94 -- so that the legislation_code for
95 -- the current business group context can be derived.
96 -- Remove this comment when the edit has been completed.
97 cursor csr_leg_code is
98 select pbg.legislation_code
99 from per_business_groups pbg
100 , pay_run_types_f_tl rtt
101 -- , EDIT_HERE table_name(s) 333
102 where rtt.run_type_id = p_run_type_id
103 and rtt.language = p_language;
104 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
105 --
106 -- Declare local variables
107 --
108 l_legislation_code varchar2(150);
109 l_proc varchar2(72) := g_package||'return_legislation_code';
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'|| l_proc, 10);
114 --
115 -- Ensure that all the mandatory parameter are not null
116 --
117 hr_api.mandatory_arg_error
118 (p_api_name => l_proc
119 ,p_argument => 'run_type_id'
120 ,p_argument_value => p_run_type_id
121 );
122 --
123 --
124 if (( nvl(pay_rtt_bus.g_run_type_id, hr_api.g_number)
125 = p_run_type_id)
126 and ( nvl(pay_rtt_bus.g_language, hr_api.g_varchar2)
127 = p_language)) 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 := pay_rtt_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 pay_rtt_bus.g_run_type_id := p_run_type_id;
158 pay_rtt_bus.g_language := p_language;
159 pay_rtt_bus.g_legislation_code := l_legislation_code;
160 end if;
161 hr_utility.set_location(' Leaving:'|| l_proc, 40);
162 return l_legislation_code;
163 end return_legislation_code;
164 --
165 -- ----------------------------------------------------------------------------
166 -- |-----------------------< chk_non_updateable_args >------------------------|
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 -- This procedure is used to ensure that non updateable attributes have
172 -- not been updated. If an attribute has been updated an error is generated.
173 --
174 -- Pre Conditions:
175 -- g_old_rec has been populated with details of the values currently in
176 -- the database.
177 --
178 -- In Arguments:
179 -- p_rec has been populated with the updated values the user would like the
180 -- record set to.
181 --
182 -- Post Success:
183 -- Processing continues if all the non updateable attributes have not
184 -- changed.
185 --
186 -- Post Failure:
187 -- An application error is raised if any of the non updatable attributes
188 -- have been altered.
189 --
190 -- {End Of Comments}
191 -- ----------------------------------------------------------------------------
192 Procedure chk_non_updateable_args
193 (p_rec in pay_rtt_shd.g_rec_type
194 ) IS
195 --
196 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
197 l_error EXCEPTION;
198 l_argument varchar2(30);
199 --
200 Begin
201 --
202 -- Only proceed with the validation if a row exists for the current
203 -- record in the HR Schema.
204 --
205 IF NOT pay_rtt_shd.api_updating
206 (p_run_type_id => p_rec.run_type_id
207 ,p_language => p_rec.language
208 ) THEN
209 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
210 fnd_message.set_token('PROCEDURE ', l_proc);
211 fnd_message.set_token('STEP ', '5');
212 fnd_message.raise_error;
213 END IF;
214 --
215 -- RET 12-DEC-2001 making run_type_name non-updateable as is the key
216 -- for uploading ldts.
217 -- RET 05-FEB-2003 making run_type_name updateable, only the base table
218 -- should be non updateable.
219 --
220 EXCEPTION
221 WHEN l_error THEN
222 hr_api.argument_changed_error
223 (p_api_name => l_proc
224 ,p_argument => l_argument);
225 WHEN OTHERS THEN
226 RAISE;
227 End chk_non_updateable_args;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |--------------------------< chk_run_type_id >-----------------------------|
231 -- ----------------------------------------------------------------------------
232 -- {Start Of Comments}
233 --
234 -- Description:
235 -- The surrogate key, run_type_id must exist on the non-translated table
236 -- pay_run_types_f.
237 --
238 -- Pre Conditions:
239 -- g_old_rec has been populated with details of the values currently in
240 -- the database.
241 --
242 -- In Arguments:
243 -- p_rec has been populated with the updated values the user would like the
244 -- record set to.
245 --
246 -- Post Success:
247 -- Processing continues if the run_type_id exist on the non-translated table
248 -- pay_run_types_f.
249 --
250 -- Post Failure:
251 -- An application error is raised if run_type_id does not exist on
252 -- pay_run_types_f.
253 --
254 -- {End Of Comments}
255 -- ----------------------------------------------------------------------------
256 Procedure chk_run_type_id
257 (p_run_type_id in number) IS
258 --
259 CURSOR csr_chk_rt_id
263 WHERE prt.run_type_id = p_run_type_id;
260 IS
261 SELECT prt.run_type_id
262 FROM pay_run_types_f prt
264 --
265 l_proc varchar2(72) := g_package || 'chk_run_type_id';
266 l_error EXCEPTION;
267 l_argument varchar2(30);
268 l_rt_id number;
269 --
270 Begin
271 --
272 hr_utility.trace('l_rt_id is: '||to_char(p_run_type_id));
273 OPEN csr_chk_rt_id;
274 FETCH csr_chk_rt_id into l_rt_id;
275 IF csr_chk_rt_id%NOTFOUND THEN
276 --
277 CLOSE csr_chk_rt_id;
278 hr_utility.set_message(801, 'HR_33999_RTT_INV_ID');
279 hr_utility.raise_error;
280 END IF;
281 CLOSE csr_chk_rt_id;
282 --
283 END chk_run_type_id;
284 -- ----------------------------------------------------------------------------
285 -- |------------------------< chk_tl_run_type_name >--------------------------|
286 -- ----------------------------------------------------------------------------
287 -- {Start Of Comments}
288 --
289 -- Description:
290 -- The run_type_name must be unique within a language
291 --
292 -- Pre Conditions:
293 -- g_old_rec has been populated with details of the values currently in
294 -- the database.
295 --
296 -- In Arguments:
297 -- p_rec has been populated with the updated values the user would like the
298 -- record set to.
299 --
300 -- Post Success:
301 -- Processing continues if the run_type_id exist on the non-translated table
302 -- pay_run_types_f.
303 --
304 -- Post Failure:
305 -- An application error is raised if run_type_id does not exist on
306 -- pay_run_types_f.
307 --
308 -- {End Of Comments}
309 -- ----------------------------------------------------------------------------
310 Procedure chk_tl_run_type_name
311 (p_run_type_id in number
312 ,p_language in varchar2
313 ,p_run_type_name in varchar2) IS
314 --
315 l_proc varchar2(72) := g_package||'chk_tl_run_type_name';
316 --
317 -- cursor to get non-translated table bg and leg code
318 --
319 CURSOR get_startup_data
320 is
321 select business_group_id
322 , legislation_code
323 from pay_run_types_f
324 where run_type_id = p_run_type_id;
325 --
326 CURSOR csr_tl_name_exists(p_bus_grp_id number
327 ,p_leg_code varchar2)
328 is
329 select 'Y'
330 from pay_run_types_f prt
331 , pay_run_types_f_tl rtt
332 where prt.run_type_id = rtt.run_type_id
333 and rtt.language = p_language
334 and upper(p_run_type_name) = upper(rtt.run_type_name)
335 and (prt.run_type_id <> p_run_type_id
336 or p_run_type_id is null)
337 and (p_bus_grp_id = prt.business_group_id + 0
338 or (prt.business_group_id is null
339 and (p_leg_code = prt.legislation_code
340 or prt.legislation_code is null)));
341 --
342 l_bg_id number;
343 l_leg_code varchar2(10);
344 l_exists varchar2(10);
345 --
346 BEGIN
347 hr_utility.set_location('Entering: '||l_proc,5);
348 --
349 -- Only need to opent the cursor if run_type_name is not null
350 --
351 if p_run_type_name is not null then
352 hr_utility.set_location(l_proc, 10);
353 --
354 OPEN get_startup_data;
355 FETCH get_startup_data into l_bg_id, l_leg_code;
356 CLOSE get_startup_data;
357 --
358 OPEN csr_tl_name_exists(l_bg_id, l_leg_code);
359 FETCH csr_tl_name_exists into l_exists;
360 IF csr_tl_name_exists%FOUND THEN
361 --
362 hr_utility.set_message(801,'HR_33998_RTT_DUP_NAME');
363 hr_utility.raise_error;
364 END IF;
365 hr_utility.set_location(l_proc, 15);
366 CLOSE csr_tl_name_exists;
367 end if;
368 hr_utility.set_location('Leaving: '||l_proc, 20);
369 end chk_tl_run_type_name;
370 -- ----------------------------------------------------------------------------
371 -- |---------------------------< insert_validate >----------------------------|
372 -- ----------------------------------------------------------------------------
373 Procedure insert_validate
374 (p_rec in pay_rtt_shd.g_rec_type
375 ,p_run_type_id in number
376 ) is
377 --
378 l_proc varchar2(72) := g_package||'insert_validate';
379 --
380 Begin
381 hr_utility.set_location('Entering:'||l_proc, 5);
382 --
383 chk_run_type_id(p_run_type_id => p_run_type_id);
384 --
385 hr_utility.set_location(l_proc, 10);
386 --
387 chk_tl_run_type_name(p_run_type_id => p_rec.run_type_id
388 ,p_language => p_rec.language
389 ,p_run_type_name => p_rec.run_type_name);
390 --
391 hr_utility.set_location(l_proc, 15);
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 20);
394 End insert_validate;
395 --
396 -- ----------------------------------------------------------------------------
397 -- |---------------------------< update_validate >----------------------------|
398 -- ----------------------------------------------------------------------------
399 Procedure update_validate
400 (p_rec in pay_rtt_shd.g_rec_type
401 ) is
402 --
403 l_proc varchar2(72) := g_package||'update_validate';
404 --
405 Begin
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 -- Call all supporting business operations
409 --
410 chk_run_type_id(p_run_type_id => p_rec.run_type_id);
411 --
412 hr_utility.set_location(l_proc, 10);
413 --
414 -- RET 12-DEC-2001 removed as run_type_name is no longer updateable. Made this
415 -- change as run type name is the key for uploading run types via the ldt.
416 --
417 /*
418 chk_tl_run_type_name(p_run_type_id => p_rec.run_type_id
419 ,p_language => p_rec.language
420 ,p_run_type_name => p_rec.run_type_name);
421 */
422 --
423 hr_utility.set_location(l_proc, 15);
424 --
425 hr_utility.set_location(l_proc, 20);
426 chk_non_updateable_args
427 (p_rec => p_rec
428 );
429 --
430 hr_utility.set_location(' Leaving:'||l_proc, 25);
431 End update_validate;
432 --
433 -- ----------------------------------------------------------------------------
434 -- |---------------------------< delete_validate >----------------------------|
435 -- ----------------------------------------------------------------------------
436 Procedure delete_validate
437 (p_rec in pay_rtt_shd.g_rec_type
438 ) is
439 --
440 l_proc varchar2(72) := g_package||'delete_validate';
441 --
442 Begin
443 hr_utility.set_location('Entering:'||l_proc, 5);
444 --
445 -- Call all supporting business operations
446 --
447 chk_run_type_id(p_run_type_id => p_rec.run_type_id);
448 --
449 hr_utility.set_location(' Leaving:'||l_proc, 10);
450 End delete_validate;
451 --
452 end pay_rtt_bus;
[Home] [Help]