1 package body hxc_tat_bus as
2 /* $Header: hxtatrhi.pkb 120.2 2005/09/23 07:03:57 rchennur noship $ */
3 -- --------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- --------------------------------------------------------------------------
6 g_package varchar2(33) := ' hxc_tat_bus.'; -- Global package name
7 g_debug boolean := hr_utility.debug_enabled;
8 -- the following two global variables are only to be
9 -- used by the return_legislation_code function.
10
11 g_legislation_code varchar2(150) default null;
12 g_time_attribute_id number default null;
13
14 -- -------------------------------------------------------------------------
15 -- |----------------------< set_security_group_id >------------------------|
16 -- -------------------------------------------------------------------------
17 procedure set_security_group_id
18 (p_time_attribute_id in number
19 ) is
20 --
21 -- Declare cursor
22 --
23 -- EDIT_HERE In the following cursor statement add join(s) between
24 -- hxc_time_attributes and PER_BUSINESS_GROUPS
25 -- so that the security_group_id for
26 -- the current business group context can be derived.
27 -- Remove this comment when the edit has been completed.
28 cursor csr_sec_grp is
29 select pbg.security_group_id
30 from per_business_groups pbg
31 , hxc_time_attributes tat
32 -- , EDIT_HERE table_name(s) 333
33 where tat.time_attribute_id = p_time_attribute_id;
34 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) ;
40 --
41 begin
42 --
43 g_debug :=hr_utility.debug_enabled;
44 if g_debug then
45 l_proc := g_package||'set_security_group_id';
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 end if;
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 => 'time_attribute_id'
54 ,p_argument_value => p_time_attribute_id
55 );
56 --
57 open csr_sec_grp;
58 fetch csr_sec_grp into l_security_group_id;
59 --
60 if csr_sec_grp%notfound then
61 --
62 close csr_sec_grp;
63 --
64 -- The primary key is invalid therefore we must error
65 --
66 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
67 fnd_message.raise_error;
68 --
69 end if;
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 if g_debug then
79 hr_utility.set_location(' Leaving:'|| l_proc, 20);
80 end if;
81 --
82 end set_security_group_id;
83 --
84 -- -------------------------------------------------------------------------
85 -- |---------------------< return_legislation_code >-----------------------|
86 -- -------------------------------------------------------------------------
87 --
88 Function return_legislation_code
89 (p_time_attribute_id in number
90 )
91 return Varchar2 Is
92 --
93 -- Declare cursor
94 --
95 -- EDIT_HERE In the following cursor statement add join(s) between
96 -- hxc_time_attributes and PER_BUSINESS_GROUPS
97 -- so that the legislation_code for
98 -- the current business group context can be derived.
99 -- Remove this comment when the edit has been completed.
100 cursor csr_leg_code is
101 select pbg.legislation_code
102 from per_business_groups pbg
103 , hxc_time_attributes tat
104 -- , EDIT_HERE table_name(s) 333
105 where tat.time_attribute_id = p_time_attribute_id;
106 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
107 --
108 -- Declare local variables
109 --
110 l_legislation_code varchar2(150);
111 l_proc varchar2(72) ;
112 --
113 begin
114 --
115 g_debug :=hr_utility.debug_enabled;
116 if g_debug then
117 l_proc := g_package||'return_legislation_code';
118 hr_utility.set_location('Entering:'|| l_proc, 10);
119 end if;
120 --
121 -- Ensure that all the mandatory parameter are not null
122 --
123 hr_api.mandatory_arg_error
124 (p_api_name => l_proc
125 ,p_argument => 'time_attribute_id'
126 ,p_argument_value => p_time_attribute_id
127 );
128 --
129 if ( nvl(hxc_tat_bus.g_time_attribute_id, hr_api.g_number)
130 = p_time_attribute_id) then
131 --
132 -- The legislation code has already been found with a previous
133 -- call to this function. Just return the value in the global
134 -- variable.
135 --
136 l_legislation_code := hxc_tat_bus.g_legislation_code;
137 if g_debug then
138 hr_utility.set_location(l_proc, 20);
139 end if;
140 else
141 --
142 -- The ID is different to the last call to this function
143 -- or this is the first call to this function.
144 --
145 open csr_leg_code;
146 fetch csr_leg_code into l_legislation_code;
147 --
148 if csr_leg_code%notfound then
149 --
150 -- The primary key is invalid therefore we must error
151 --
152 close csr_leg_code;
153 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
154 fnd_message.raise_error;
155 end if;
156 if g_debug then
157 hr_utility.set_location(l_proc,30);
158 end if;
159 --
160 -- Set the global variables so the values are
161 -- available for the next call to this function.
162 --
163 close csr_leg_code;
164 hxc_tat_bus.g_time_attribute_id := p_time_attribute_id;
165 hxc_tat_bus.g_legislation_code := l_legislation_code;
166 end if;
167 if g_debug then
168 hr_utility.set_location(' Leaving:'|| l_proc, 40);
169 end if;
170 return l_legislation_code;
171 end return_legislation_code;
172
173 -- --------------------------------------------------------------------------
174 -- |------------------------------< chk_df >--------------------------------|
175 -- --------------------------------------------------------------------------
176 --
177 -- Description:
178 -- Validates all the Descriptive Flexfield values.
179 --
180 -- Prerequisites:
181 -- All other columns have been validated. Must be called as the
182 -- last step from insert_validate and update_validate.
183 --
184 -- In Arguments:
185 -- p_rec
186 --
187 -- Post Success:
188 -- if the Descriptive Flexfield structure column and data values are
189 -- all valid this procedure will end normally and processing will
190 -- continue.
191 --
192 -- Post Failure:
193 -- if the Descriptive Flexfield structure column value or any of
194 -- the data values are invalid then an application error is raised as
195 -- a PL/SQL exception.
196 --
197 -- Access Status:
198 -- Internal Row Handler Use Only.
199 --
200 -- --------------------------------------------------------------------------
201 procedure chk_df
202 (p_rec in hxc_tat_shd.g_rec_type
203 ) is
204
205 l_proc varchar2(72) ;
206
207 begin
208
209 if g_debug then
210 l_proc := g_package || 'chk_df';
211 hr_utility.set_location('Entering:'||l_proc,10);
212 end if;
213
214 if ((p_rec.time_attribute_id is not null) and (
215 nvl(hxc_tat_shd.g_old_rec.time_attribute_id, hr_api.g_varchar2) <>
216 nvl(p_rec.time_attribute_id, hr_api.g_varchar2) or
217 nvl(hxc_tat_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
218 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
219 nvl(hxc_tat_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
220 nvl(p_rec.attribute1, hr_api.g_varchar2) or
221 nvl(hxc_tat_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
222 nvl(p_rec.attribute2, hr_api.g_varchar2) or
223 nvl(hxc_tat_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
224 nvl(p_rec.attribute3, hr_api.g_varchar2) or
225 nvl(hxc_tat_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
226 nvl(p_rec.attribute4, hr_api.g_varchar2) or
227 nvl(hxc_tat_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute5, hr_api.g_varchar2) or
229 nvl(hxc_tat_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute6, hr_api.g_varchar2) or
231 nvl(hxc_tat_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
232 nvl(p_rec.attribute7, hr_api.g_varchar2) or
233 nvl(hxc_tat_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
234 nvl(p_rec.attribute8, hr_api.g_varchar2) or
235 nvl(hxc_tat_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
236 nvl(p_rec.attribute9, hr_api.g_varchar2) or
237 nvl(hxc_tat_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
238 nvl(p_rec.attribute10, hr_api.g_varchar2) or
239 nvl(hxc_tat_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
240 nvl(p_rec.attribute11, hr_api.g_varchar2) or
241 nvl(hxc_tat_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
242 nvl(p_rec.attribute12, hr_api.g_varchar2) or
243 nvl(hxc_tat_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
244 nvl(p_rec.attribute13, hr_api.g_varchar2) or
245 nvl(hxc_tat_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
246 nvl(p_rec.attribute14, hr_api.g_varchar2) or
247 nvl(hxc_tat_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
248 nvl(p_rec.attribute15, hr_api.g_varchar2) or
249 nvl(hxc_tat_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
250 nvl(p_rec.attribute16, hr_api.g_varchar2) or
251 nvl(hxc_tat_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
252 nvl(p_rec.attribute17, hr_api.g_varchar2) or
253 nvl(hxc_tat_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
254 nvl(p_rec.attribute18, hr_api.g_varchar2) or
255 nvl(hxc_tat_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
256 nvl(p_rec.attribute19, hr_api.g_varchar2) or
257 nvl(hxc_tat_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
258 nvl(p_rec.attribute20, hr_api.g_varchar2) or
259 nvl(hxc_tat_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
260 nvl(p_rec.attribute21, hr_api.g_varchar2) or
261 nvl(hxc_tat_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
262 nvl(p_rec.attribute22, hr_api.g_varchar2) or
263 nvl(hxc_tat_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
264 nvl(p_rec.attribute23, hr_api.g_varchar2) or
265 nvl(hxc_tat_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
266 nvl(p_rec.attribute24, hr_api.g_varchar2) or
267 nvl(hxc_tat_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
268 nvl(p_rec.attribute25, hr_api.g_varchar2) or
269 nvl(hxc_tat_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
270 nvl(p_rec.attribute26, hr_api.g_varchar2) or
271 nvl(hxc_tat_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
272 nvl(p_rec.attribute27, hr_api.g_varchar2) or
273 nvl(hxc_tat_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
274 nvl(p_rec.attribute28, hr_api.g_varchar2) or
275 nvl(hxc_tat_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
276 nvl(p_rec.attribute29, hr_api.g_varchar2) or
277 nvl(hxc_tat_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
278 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
279 or (p_rec.time_attribute_id is null) then
280
281 -- only execute the validation if absolutely necessary:
282 -- a) during update, the structure column value or any
283 -- of the attribute values have actually changed.
284 -- b) during insert.
285 /*
286 hr_dflex_utility.ins_or_upd_descflex_attribs
287 (p_appl_short_name => 'HXC'
288 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
289 ,p_attribute_category => 'ATTRIBUTE_CATEGORY'
290 ,p_attribute1_name => 'ATTRIBUTE1'
291 ,p_attribute1_value => p_rec.attribute1
292 ,p_attribute2_name => 'ATTRIBUTE2'
293 ,p_attribute2_value => p_rec.attribute2
294 ,p_attribute3_name => 'ATTRIBUTE3'
295 ,p_attribute3_value => p_rec.attribute3
296 ,p_attribute4_name => 'ATTRIBUTE4'
297 ,p_attribute4_value => p_rec.attribute4
298 ,p_attribute5_name => 'ATTRIBUTE5'
299 ,p_attribute5_value => p_rec.attribute5
300 ,p_attribute6_name => 'ATTRIBUTE6'
301 ,p_attribute6_value => p_rec.attribute6
302 ,p_attribute7_name => 'ATTRIBUTE7'
303 ,p_attribute7_value => p_rec.attribute7
304 ,p_attribute8_name => 'ATTRIBUTE8'
305 ,p_attribute8_value => p_rec.attribute8
306 ,p_attribute9_name => 'ATTRIBUTE9'
307 ,p_attribute9_value => p_rec.attribute9
308 ,p_attribute10_name => 'ATTRIBUTE10'
309 ,p_attribute10_value => p_rec.attribute10
310 ,p_attribute11_name => 'ATTRIBUTE11'
311 ,p_attribute11_value => p_rec.attribute11
312 ,p_attribute12_name => 'ATTRIBUTE12'
313 ,p_attribute12_value => p_rec.attribute12
314 ,p_attribute13_name => 'ATTRIBUTE13'
315 ,p_attribute13_value => p_rec.attribute13
316 ,p_attribute14_name => 'ATTRIBUTE14'
317 ,p_attribute14_value => p_rec.attribute14
318 ,p_attribute15_name => 'ATTRIBUTE15'
319 ,p_attribute15_value => p_rec.attribute15
320 ,p_attribute16_name => 'ATTRIBUTE16'
321 ,p_attribute16_value => p_rec.attribute16
322 ,p_attribute17_name => 'ATTRIBUTE17'
323 ,p_attribute17_value => p_rec.attribute17
324 ,p_attribute18_name => 'ATTRIBUTE18'
325 ,p_attribute18_value => p_rec.attribute18
326 ,p_attribute19_name => 'ATTRIBUTE19'
327 ,p_attribute19_value => p_rec.attribute19
328 ,p_attribute20_name => 'ATTRIBUTE20'
329 ,p_attribute20_value => p_rec.attribute20
330 ,p_attribute21_name => 'ATTRIBUTE21'
331 ,p_attribute21_value => p_rec.attribute21
332 ,p_attribute22_name => 'ATTRIBUTE22'
333 ,p_attribute22_value => p_rec.attribute22
334 ,p_attribute23_name => 'ATTRIBUTE23'
335 ,p_attribute23_value => p_rec.attribute23
336 ,p_attribute24_name => 'ATTRIBUTE24'
337 ,p_attribute24_value => p_rec.attribute24
338 ,p_attribute25_name => 'ATTRIBUTE25'
339 ,p_attribute25_value => p_rec.attribute25
340 ,p_attribute26_name => 'ATTRIBUTE26'
341 ,p_attribute26_value => p_rec.attribute26
342 ,p_attribute27_name => 'ATTRIBUTE27'
343 ,p_attribute27_value => p_rec.attribute27
344 ,p_attribute28_name => 'ATTRIBUTE28'
345 ,p_attribute28_value => p_rec.attribute28
346 ,p_attribute29_name => 'ATTRIBUTE29'
347 ,p_attribute29_value => p_rec.attribute29
348 ,p_attribute30_name => 'ATTRIBUTE30'
349 ,p_attribute30_value => p_rec.attribute30
350 );
351 */null;
352 end if;
353
354 if g_debug then
355 hr_utility.set_location(' Leaving:'||l_proc,20);
356 end if;
357
358 end chk_df;
359
360 -- --------------------------------------------------------------------------
361 -- |-----------------------< chk_non_updateable_args >----------------------|
365 -- Description:
362 -- --------------------------------------------------------------------------
363 -- {Start Of Comments}
364 --
366 -- This procedure is used to ensure that non updateable attributes have
367 -- not been updated. if an attribute has been updated an error is generated.
368 --
369 -- Pre Conditions:
370 -- g_old_rec has been populated with details of the values currently in
371 -- the database.
372 --
373 -- In Arguments:
374 -- p_rec has been populated with the updated values the user would like the
375 -- record set to.
376 --
377 -- Post Success:
378 -- processing continues if all the non updateable attributes have not
379 -- changed.
380 --
381 -- Post Failure:
382 -- An application error is raised if any of the non updatable attributes
383 -- have been altered.
384 --
385 -- {end Of Comments}
386 -- --------------------------------------------------------------------------
387 procedure chk_non_updateable_args
388 (p_effective_date in date
389 ,p_rec in hxc_tat_shd.g_rec_type
390 ) is
391
392 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
393 l_error exception;
394 l_argument varchar2(30);
395
396 begin
397
398 -- only proceed with the validation if a row exists for the current
399 -- record in the HR Schema.
400
401 if not hxc_tat_shd.api_updating
402 (p_time_attribute_id => p_rec.time_attribute_id
403 ,p_object_version_number => p_rec.object_version_number
404 ) then
405 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
406 fnd_message.set_token('PROCEDURE ', l_proc);
407 fnd_message.set_token('STEP ', '5');
408 fnd_message.raise_error;
409 end if;
410
411 -- EDIT_HERE: Add checks to ensure non-updateable args have
412 -- not been updated.
413
414 exception
415 when l_error then
416 hr_api.argument_changed_error
417 (p_api_name => l_proc
418 ,p_argument => l_argument);
419 when others then
420 raise;
421
422 end chk_non_updateable_args;
423
424 -- --------------------------------------------------------------------------
425 -- |---------------------------< insert_validate >--------------------------|
426 -- --------------------------------------------------------------------------
427 procedure insert_validate
428 (p_effective_date in date
429 ,p_rec in hxc_tat_shd.g_rec_type
430 ) is
431
432 l_proc varchar2(72) ;
433
434 begin
435 g_debug :=hr_utility.debug_enabled;
436 if g_debug then
437 l_proc := g_package||'insert_validate';
438 hr_utility.set_location('Entering:'||l_proc, 5);
439 end if;
440
441 -- call all supporting business operations
442
443 --
444 -- EDIT_HERE: As this table does not have a mandatory business_group_id
445 -- column, ensure client_info is populated by calling a suitable
446 -- ???_???_bus.set_security_group_id procedure, or add one of the following
447 -- comments:
448 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
449 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
450
451 hxc_tat_bus.chk_df(p_rec);
452
453 if g_debug then
454 hr_utility.set_location(' Leaving:'||l_proc, 10);
455 end if;
456
457 end insert_validate;
458
459 -- --------------------------------------------------------------------------
460 -- |---------------------------< update_validate >--------------------------|
461 -- --------------------------------------------------------------------------
462 procedure update_validate
463 (p_effective_date in date
464 ,p_rec in hxc_tat_shd.g_rec_type
465 ) is
466
467 l_proc varchar2(72) ;
468
469 begin
470 g_debug :=hr_utility.debug_enabled;
471 if g_debug then
472 l_proc := g_package||'update_validate';
473 hr_utility.set_location('Entering:'||l_proc, 5);
474 end if;
475
476 -- call all supporting business operations
477
478 -- EDIT_HERE: As this table does not have a mandatory business_group_id
479 -- column, ensure client_info is populated by calling a suitable
480 -- ???_???_bus.set_security_group_id procedure, or add one of the following
481 -- comments:
482 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
483 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
484
485 chk_non_updateable_args
489
486 (p_effective_date => p_effective_date
487 ,p_rec => p_rec
488 );
490 hxc_tat_bus.chk_df(p_rec);
491
492 if g_debug then
493 hr_utility.set_location(' Leaving:'||l_proc, 10);
494 end if;
495
496 end update_validate;
497
498 -- --------------------------------------------------------------------------
499 -- |---------------------------< delete_validate >--------------------------|
500 -- --------------------------------------------------------------------------
501 procedure delete_validate
502 (p_rec in hxc_tat_shd.g_rec_type
503 ) is
504
505 l_proc varchar2(72) ;
506
507 begin
508 g_debug :=hr_utility.debug_enabled;
509 if g_debug then
510 l_proc := g_package||'delete_validate';
511 hr_utility.set_location('Entering:'||l_proc, 5);
512 end if;
513
514 -- call all supporting business operations
515
516 if g_debug then
517 hr_utility.set_location(' Leaving:'||l_proc, 10);
518 end if;
519
520 end delete_validate;
521
522 end hxc_tat_bus;