[Home] [Help]
PACKAGE BODY: APPS.HXC_HTC_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body hxc_htc_bus as
2 /* $Header: hxchtcrhi.pkb 120.3 2007/10/19 11:54:08 anuthi noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7
8 g_debug boolean := hr_utility.debug_enabled;
9
10 CURSOR csr_chk_ref_integ ( p_time_category_id NUMBER ) IS
11 SELECT DISTINCT ter.time_entry_rule_id
12 , dfcu.application_column_name
13 FROM fnd_descr_flex_column_usages dfcu
14 , hxc_time_entry_rules ter
15 WHERE ter.formula_id IS NOT NULL
16 AND
17 dfcu.application_id = 809 AND
18 dfcu.descriptive_flex_context_code = ter.attribute_category AND
19 UPPER(dfcu.end_user_column_name) like 'TIME_CATEGORY%'
20 AND
21 DECODE ( dfcu.application_column_name,
22 'ATTRIBUTE1', ter.attribute1,
23 'ATTRIBUTE2', ter.attribute2,
24 'ATTRIBUTE3', ter.attribute3,
25 'ATTRIBUTE4', ter.attribute4,
26 'ATTRIBUTE5', ter.attribute5,
27 'ATTRIBUTE6', ter.attribute6,
28 'ATTRIBUTE7', ter.attribute7,
29 'ATTRIBUTE8', ter.attribute8,
30 'ATTRIBUTE9', ter.attribute9,
31 'ATTRIBUTE10', ter.attribute10,
32 'ATTRIBUTE11', ter.attribute11,
33 'ATTRIBUTE12', ter.attribute12,
34 'ATTRIBUTE13', ter.attribute13,
35 'ATTRIBUTE14', ter.attribute14,
36 'ATTRIBUTE15', ter.attribute15,
37 'ATTRIBUTE16', ter.attribute16,
38 'ATTRIBUTE17', ter.attribute17,
39 'ATTRIBUTE18', ter.attribute18,
40 'ATTRIBUTE19', ter.attribute19,
41 'ATTRIBUTE20', ter.attribute20,
42 'ATTRIBUTE21', ter.attribute21,
43 'ATTRIBUTE22', ter.attribute22,
44 'ATTRIBUTE23', ter.attribute23,
45 'ATTRIBUTE24', ter.attribute24,
46 'ATTRIBUTE25', ter.attribute25,
47 'ATTRIBUTE26', ter.attribute26,
48 'ATTRIBUTE27', ter.attribute27,
49 'ATTRIBUTE28', ter.attribute28,
50 'ATTRIBUTE29', ter.attribute29,
51 'ATTRIBUTE30', ter.attribute30, 'zZz' ) = TO_CHAR(p_time_category_id);
52
53 g_package varchar2(33) := ' hxc_htc_bus.'; -- Global package name
54
55 -- ----------------------------------------------------------------------------
56 -- |-----------------------< chk_non_updateable_args >------------------------|
57 -- ----------------------------------------------------------------------------
58 -- {Start Of Comments}
59 --
60 -- Description:
61 -- This procedure is used to ensure that non updateable attributes have
62 -- not been updated. If an attribute has been updated an error is generated.
63 --
64 -- Pre Conditions:
65 -- g_old_rec has been populated with details of the values currently in
66 -- the database.
67 --
68 -- In Arguments:
69 -- p_rec has been populated with the updated values the user would like the
70 -- record set to.
71 --
72 -- Post Success:
73 -- Processing continues if all the non updateable attributes have not
74 -- changed.
75 --
76 -- Post Failure:
77 -- An application error is raised if any of the non updatable attributes
78 -- have been altered.
79 --
80 -- {End Of Comments}
81 -- ----------------------------------------------------------------------------
82 Procedure chk_non_updateable_args
83 (p_rec in hxc_htc_shd.g_rec_type
84 ) IS
85 --
86 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
87 l_error EXCEPTION;
88 l_argument varchar2(30);
89 --
90 Begin
91 --
92 -- Only proceed with the validation if a row exists for the current
93 -- record in the HR Schema.
94 --
95 IF NOT hxc_htc_shd.api_updating
96 (p_time_category_id => p_rec.time_category_id
97 ,p_object_version_number => p_rec.object_version_number
98 ) THEN
99 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
100 fnd_message.set_token('PROCEDURE ', l_proc);
101 fnd_message.set_token('STEP ', '5');
102 fnd_message.raise_error;
103 END IF;
104 --
105 -- EDIT_HERE: Add checks to ensure non-updateable args have
106 -- not been updated.
107 --
108 EXCEPTION
109 WHEN l_error THEN
110 hr_api.argument_changed_error
111 (p_api_name => l_proc
112 ,p_argument => l_argument);
113 WHEN OTHERS THEN
114 RAISE;
115 End chk_non_updateable_args;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |-----------------------< chk_time_category>-------------------------------|
119 -- ----------------------------------------------------------------------------
120 -- {Start Of Comments}
121 --
122 -- Description:
123 --
124 -- SEE DESCRIPTION IN PACKAGE HEADER
125 --
126 -- Note:
127 -- This procedure is called from the client
128 --
129 -- {End Of Comments}
130 -- ----------------------------------------------------------------------------
131 Procedure chk_time_category
132 (
133 p_time_category_id number,
134 p_time_category_name varchar2
135 ) IS
136
137 l_proc varchar2(72);
138
139 -- cursor to check time category code is unique
140
141 CURSOR csr_chk_time_category IS
142 SELECT 'error'
143 FROM sys.dual
147 WHERE map.time_category_name = p_time_category_name AND
144 WHERE EXISTS (
145 SELECT 'x'
146 FROM hxc_time_categories map
148 ( map.time_category_id <> p_time_category_id OR
149 p_time_category_id IS NULL ) );
150
151 l_dup_time_category varchar2(5) := NULL;
152
153 BEGIN
154 g_debug := hr_utility.debug_enabled;
155
156 if g_debug then
157 l_proc := g_package||'chk_time_category';
158 hr_utility.set_location('Entering:'||l_proc, 5);
159 end if;
160
161 -- check that the time category code is unique
162
163 OPEN csr_chk_time_category;
164 FETCH csr_chk_time_category INTO l_dup_time_category;
165 CLOSE csr_chk_time_category;
166
167 IF l_dup_time_category IS NOT NULL
168 THEN
169
170 hr_utility.set_message(809, 'HXC_HTC_DUPLICATE_TC');
171 hr_utility.raise_error;
172
173 END IF;
174
175 --
176 if g_debug then
177 hr_utility.set_location('Leaving:'||l_proc, 20);
178 end if;
179 --
180 END chk_time_category;
181
182
183 -- ----------------------------------------------------------------------------
187 -- Description:
184 -- |------------------< chk_tc_ref_integrity >--------------------------------|
185 -- ----------------------------------------------------------------------------
186
188
189 -- SEE DESCRIPTION IN PACKAGE HEADER
190
191 -- ----------------------------------------------------------------------------
192
193 FUNCTION chk_tc_ref_integrity ( p_time_category_id NUMBER ) RETURN BOOLEAN IS
194
195 l_referenced BOOLEAN := TRUE;
196
197 l_exists r_ter_record;
198
199 BEGIN
200
201 OPEN csr_chk_ref_integ ( p_time_category_id );
202 FETCH csr_chk_ref_integ INTO l_exists;
203
204 IF ( csr_chk_ref_integ%FOUND )
205 THEN
206
207 l_referenced := FALSE;
208
209 END IF;
210
211 CLOSE csr_chk_ref_integ;
212
213 RETURN l_referenced;
214
215 END chk_tc_ref_integrity;
216
217
218 -- ----------------------------------------------------------------------------
219 -- |------------------< get_tc_ref_integrity_list >---------------------------|
220 -- ----------------------------------------------------------------------------
221
222 -- Description:
223
224 -- SEE DESCRIPTION IN PACKAGE HEADER
225
226 -- ----------------------------------------------------------------------------
227
228 FUNCTION get_tc_ref_integrity_list ( p_time_category_id NUMBER ) RETURN t_ter_table IS
229
230 l_ter_list t_ter_table;
231 l_index BINARY_INTEGER := 1;
232
233 BEGIN
234
235 OPEN csr_chk_ref_integ ( p_time_category_id );
236 FETCH csr_chk_ref_integ INTO l_ter_list(l_index);
237
238 IF ( csr_chk_ref_integ%FOUND )
239 THEN
240
241 l_index := l_index + 1;
242
243 FETCH csr_chk_ref_integ INTO l_ter_list(l_index);
244
245 END IF;
246
247 CLOSE csr_chk_ref_integ;
248
249 RETURN l_ter_list;
250
251 END get_tc_ref_integrity_list;
252
253
254
255 -- ----------------------------------------------------------------------------
256 -- |-----------------------< chk_delete >-------------------------------------|
257 -- ----------------------------------------------------------------------------
258
259 -- Description:
260 --
261 -- SEE DESCRIPTION IN PACKAGE HEADER
262
263 -- ----------------------------------------------------------------------------
264 Procedure chk_delete
265 (
266 p_time_category_id number
267 ) IS
268
269 l_proc varchar2(72);
270
271 CURSOR csr_chk_tcc IS
272 SELECT 'exists'
273 FROM sys.dual
274 WHERE EXISTS (
275 SELECT 'x'
276 FROM hxc_time_category_comps tcc
277 WHERE tcc.ref_time_category_id = p_time_category_id);
278
279 l_exists VARCHAR2(6) := NULL;
280
281 BEGIN
282 g_debug := hr_utility.debug_enabled;
283
284 if g_debug then
285 l_proc := g_package||'chk_delete';
286 hr_utility.set_location('Entering:'||l_proc, 5);
287 end if;
288
289 -- check that time_category is not being used by another time category
290
291 OPEN csr_chk_tcc;
292 FETCH csr_chk_tcc INTO l_exists;
293 CLOSE csr_chk_tcc;
294
295 if g_debug then
296 hr_utility.set_location('Processing: '||l_proc, 10);
297 end if;
298
299 IF l_exists IS NOT NULL
303 hr_utility.raise_error;
300 THEN
301
302 hr_utility.set_message(809, 'HXC_HTC_TC_IN_USE');
304
305 END IF;
306
307 if g_debug then
308 hr_utility.set_location('Processing: '||l_proc,20);
309 end if;
310
311 -- check to see that this time category is not being referenced in any
312 -- of the seeded formula or other formula which have been coded using
313 -- the TIME_CATEGORY segment naming convention standard.
314
315 IF ( NOT hxc_htc_bus.chk_tc_ref_integrity ( p_time_category_id ) )
316 THEN
317
318 hr_utility.set_message(809, 'HXC_HTC_TC_IN_USE');
319 hr_utility.raise_error;
320
321 END IF;
322
323 END chk_delete;
324 --
325 -- ----------------------------------------------------------------------------
326 -- |---------------------------< insert_validate >----------------------------|
327 -- ----------------------------------------------------------------------------
328 Procedure insert_validate
329 (p_rec in hxc_htc_shd.g_rec_type
330 ) is
331 --
332 l_proc varchar2(72);
333 --
334 Begin
335 g_debug := hr_utility.debug_enabled;
336
337 if g_debug then
338 l_proc := g_package||'insert_validate';
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 end if;
341 --
342 -- Call all supporting business operations
343 --
344 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
345 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
346 --
347 hr_api.mandatory_arg_error
348 (p_api_name => l_proc
349 ,p_argument => 'TIME_CATEGORY_NAME'
350 ,p_argument_value => p_rec.time_category_name
351 );
352
353 hr_api.mandatory_arg_error
354 (p_api_name => l_proc
355 ,p_argument => 'OPERATOR'
356 ,p_argument_value => p_rec.operator
357 );
358
359 hr_api.mandatory_arg_error
360 (p_api_name => l_proc
361 ,p_argument => 'DISPLAY'
362 ,p_argument_value => p_rec.display
363 );
364
365 chk_time_category (
366 p_time_category_name => p_rec.time_category_name
367 ,p_time_category_id => p_rec.time_category_id );
368 --
369 if g_debug then
370 hr_utility.set_location(' Leaving:'||l_proc, 10);
371 end if;
372 End insert_validate;
373 --
374 -- ----------------------------------------------------------------------------
375 -- |---------------------------< update_validate >----------------------------|
376 -- ----------------------------------------------------------------------------
377 Procedure update_validate
378 (p_rec in hxc_htc_shd.g_rec_type
379 ) is
380 --
381 l_proc varchar2(72);
382 --
383 Begin
384 g_debug := hr_utility.debug_enabled;
385
386 if g_debug then
387 l_proc := g_package||'update_validate';
388 hr_utility.set_location('Entering:'||l_proc, 5);
389 end if;
390 --
391 -- Call all supporting business operations
392 --
393 --
394 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
395 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
396 --
397 hr_api.mandatory_arg_error
398 (p_api_name => l_proc
399 ,p_argument => 'TIME_CATEGORY_NAME'
400 ,p_argument_value => p_rec.time_category_name
401 );
402
403 hr_api.mandatory_arg_error
404 (p_api_name => l_proc
405 ,p_argument => 'OPERATOR'
406 ,p_argument_value => p_rec.operator
407 );
408
409 hr_api.mandatory_arg_error
410 (p_api_name => l_proc
411 ,p_argument => 'DISPLAY'
412 ,p_argument_value => p_rec.display
413 );
414
415 chk_time_category (
416 p_time_category_name => p_rec.time_category_name
417 ,p_time_category_id => p_rec.time_category_id );
418 --
419 chk_non_updateable_args
420 (p_rec => p_rec
421 );
422 --
423 --
424 if g_debug then
425 hr_utility.set_location(' Leaving:'||l_proc, 10);
426 end if;
427 End update_validate;
428 --
429 -- ----------------------------------------------------------------------------
430 -- |---------------------------< delete_validate >----------------------------|
431 -- ----------------------------------------------------------------------------
432 Procedure delete_validate
433 (p_rec in hxc_htc_shd.g_rec_type
434 ) is
435 --
436 l_proc varchar2(72);
437 --
438 Begin
439 g_debug := hr_utility.debug_enabled;
440
441 if g_debug then
442 l_proc := g_package||'delete_validate';
443 hr_utility.set_location('Entering:'||l_proc, 5);
444 end if;
445 --
446 -- Call all supporting business operations
447 --
448 chk_delete ( p_time_category_id => p_rec.time_category_id );
449 --
450 if g_debug then
451 hr_utility.set_location(' Leaving:'||l_proc, 10);
452 end if;
453 End delete_validate;
454 --
455 end hxc_htc_bus;