[Home] [Help]
PACKAGE BODY: APPS.HXC_TIME_CATEGORY_API
Source
1 Package Body hxc_time_category_api as
2 /* $Header: hxchtcapi.pkb 120.2.12010000.4 2009/01/07 15:00:50 asrajago ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hxc_time_category_api.';
7
8 g_debug boolean := hr_utility.debug_enabled;
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< create_time_category >---------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure create_time_category
15 (p_validate in boolean default false
16 ,p_time_category_id in out nocopy number
17 ,p_object_version_number in out nocopy number
18 ,p_time_category_name in varchar2
19 ,p_operator in varchar2
20 ,p_description in varchar2
21 ,p_display in varchar2
22 ) is
23 --
24 -- Declare cursors and local variables
25 --
26 l_proc varchar2(72) ;
27 l_object_version_number hxc_time_categories.object_version_number%TYPE;
28 l_time_category_id hxc_time_categories.time_category_id%TYPE;
29
30 begin
31 g_debug := hr_utility.debug_enabled;
32 --
33 if g_debug then
34 l_proc := g_package||' create_time_category';
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 end if;
37 --
38 -- Issue a savepoint if operating in validation only mode
39 --
40 savepoint create_time_category;
41 --
42 if g_debug then
43 hr_utility.set_location(l_proc, 20);
44 end if;
45 --
46 -- Call Before Process User Hook
47 --
48 begin
49 hxc_time_category_BK_1.create_time_category_b
50 (p_time_category_id => p_time_category_id
51 ,p_object_version_number => p_object_version_number
52 ,p_time_category_name => p_time_category_name
53 ,p_operator => p_operator
54 ,p_description => p_description
55 ,p_display => p_display
56 );
57 exception
58 when hr_api.cannot_find_prog_unit then
59 hr_api.cannot_find_prog_unit_error
60 (p_module_name => 'create_time_category'
61 ,p_hook_type => 'BP'
62 );
63 end;
64 --
65 if g_debug then
66 hr_utility.set_location(l_proc, 30);
67 end if;
68 --
69 -- Process Logic
70 --
71 --
72 if g_debug then
73 hr_utility.set_location(l_proc, 40);
74 end if;
75 --
76 -- call row handler
77 --
78 hxc_htc_ins.ins (
79 p_time_category_name => p_time_category_name
80 ,p_operator => p_operator
81 ,p_description => p_description
82 ,p_display => p_display
83 ,p_time_category_id => l_time_category_id
84 ,p_object_version_number => l_object_version_number );
85 --
86 if g_debug then
87 hr_utility.set_location(l_proc, 50);
88 end if;
89 --
90 -- Call After Process User Hook
91 --
92 begin
93 hxc_time_category_BK_1.create_time_category_a
94 (p_time_category_id => l_time_category_id
95 ,p_object_version_number => l_object_version_number
96 ,p_time_category_name => p_time_category_name
97 ,p_operator => p_operator
98 ,p_description => p_description
99 ,p_display => p_display
100 );
101 exception
102 when hr_api.cannot_find_prog_unit then
103 hr_api.cannot_find_prog_unit_error
104 (p_module_name => 'create_time_category'
105 ,p_hook_type => 'AP'
106 );
107 end;
108 --
109 if g_debug then
110 hr_utility.set_location(l_proc, 60);
111 end if;
112 --
113 -- When in validation only mode raise the Validate_Enabled exception
114 --
115 if p_validate then
116 raise hr_api.validate_enabled;
117 end if;
118 --
119 if g_debug then
120 hr_utility.set_location(' Leaving:'||l_proc, 70);
121 end if;
122 --
123 -- Set all output arguments
124 --
125 p_time_category_id := l_time_category_id;
126 p_object_version_number := l_object_version_number;
127 --
128 exception
129 --
130 when hr_api.validate_enabled then
131 --
132 -- As the Validate_Enabled exception has been raised
133 -- we must rollback to the savepoint
134 --
135 ROLLBACK TO create_time_category;
136 --
137 -- Only set output warning arguments
138 -- (Any key or derived arguments must be set to null
139 -- when validation only mode is being used.)
140 --
141 p_time_category_id := null;
142 p_object_version_number := null;
143 --
144 if g_debug then
145 hr_utility.set_location(' Leaving:'||l_proc, 80);
146 end if;
147 --
148 when others then
149 --
150 -- A validation or unexpected error has occured
151 --
152 if g_debug then
153 hr_utility.trace('In exeception');
154 end if;
155 ROLLBACK TO create_time_category;
156 raise;
157 --
158 END create_time_category;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------< update_time_category>-----------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 procedure update_time_category
165 (p_validate in boolean default false
166 ,p_time_category_id in number
167 ,p_object_version_number in out nocopy number
168 ,p_time_category_name in varchar2
169 ,p_operator in varchar2
170 ,p_description in varchar2
171 ,p_display in varchar2
172 ) is
173 --
174 -- Declare cursors and local variables
175 --
176
177 l_proc varchar2(72) ;
178 l_object_version_number hxc_time_categories.object_version_number%TYPE := p_object_version_number;
179
180 begin
181 g_debug := hr_utility.debug_enabled;
182 --
183 if g_debug then
184 l_proc := g_package||' update_time_category';
185 hr_utility.set_location('Entering:'|| l_proc, 10);
186 end if;
187 --
188 -- Issue a savepoint if operating in validation only mode
189 --
190 savepoint update_time_category;
191 --
192 if g_debug then
193 hr_utility.set_location(l_proc, 20);
194 end if;
195 --
196 -- Call Before Process User Hook
197 --
198 begin
199 hxc_time_category_BK_2.update_time_category_b
200 (p_time_category_id => p_time_category_id
201 ,p_object_version_number => p_object_version_number
202 ,p_time_category_name => p_time_category_name
203 ,p_description => p_description
204 ,p_display => p_display
205 ,p_operator => p_operator
206 );
207 exception
208 when hr_api.cannot_find_prog_unit then
209 hr_api.cannot_find_prog_unit_error
210 (p_module_name => 'update_time_category'
211 ,p_hook_type => 'BP'
212 );
213 end;
214 --
215 if g_debug then
216 hr_utility.set_location(l_proc, 30);
217 end if;
218 --
219 -- Process Logic
220 --
221 -- call row handler
222 --
223 hxc_htc_upd.upd (
224 p_time_category_name => p_time_category_name
225 ,p_description => p_description
226 ,p_display => p_display
227 ,p_operator => p_operator
228 ,p_time_category_id => p_time_category_id
229 ,p_object_version_number => l_object_version_number );
230 --
231 --
232 if g_debug then
233 hr_utility.set_location(l_proc, 40);
234 end if;
235 --
236 -- Call After Process User Hook
237 --
238 begin
239 hxc_time_category_BK_2.update_time_category_a
240 (p_time_category_id => p_time_category_id
241 ,p_object_version_number => l_object_version_number
242 ,p_time_category_name => p_time_category_name
243 ,p_description => p_description
244 ,p_display => p_display
245 ,p_operator => p_operator );
246 exception
247 when hr_api.cannot_find_prog_unit then
248 hr_api.cannot_find_prog_unit_error
249 (p_module_name => 'update_time_category'
250 ,p_hook_type => 'AP'
251 );
252 end;
253 --
254 if g_debug then
255 hr_utility.set_location(l_proc, 50);
256 end if;
257 --
258 -- When in validation only mode raise the Validate_Enabled exception
259 --
260 if p_validate then
261 raise hr_api.validate_enabled;
262 end if;
263 --
264 if g_debug then
265 hr_utility.set_location(' Leaving:'||l_proc, 60);
266 end if;
267 --
268 -- Set all output arguments
269 --
270 p_object_version_number := l_object_version_number;
271 --
272 exception
273 --
274 when hr_api.validate_enabled then
275 --
276 -- As the Validate_Enabled exception has been raised
277 -- we must rollback to the savepoint
278 --
279 ROLLBACK TO update_time_category;
280 --
281 -- Only set output warning arguments
282 -- (Any key or derived arguments must be set to null
283 -- when validation only mode is being used.)
284 --
285 p_object_version_number := null;
286 --
287 if g_debug then
288 hr_utility.set_location(' Leaving:'||l_proc, 60);
289 end if;
290 --
291 when others then
292 --
293 -- A validation or unexpected error has occured
294 --
295 if g_debug then
296 hr_utility.trace('In exeception');
297 end if;
298 ROLLBACK TO update_time_category;
299 raise;
300 --
301 END update_time_category;
302 --
303 -- ----------------------------------------------------------------------------
304 -- |------------------------< delete_time_category >----------------------------|
305 -- ----------------------------------------------------------------------------
306 --
307 procedure delete_time_category
308 (p_validate in boolean default false
309 ,p_time_category_id in number
310 ,p_time_category_name in varchar2
311 ,p_object_version_number in number
312 ) is
313 --
314 -- Declare cursors and local variables
315 --
316 l_proc varchar2(72) ;
317 --
318 begin
319 g_debug := hr_utility.debug_enabled;
320 --
321 if g_debug then
322 l_proc := g_package||'delete_time_category';
323 hr_utility.set_location('Entering:'|| l_proc, 10);
324 end if;
325 --
326 -- Issue a savepoint if operating in validation only mode
327 --
328 savepoint delete_time_category;
329 --
330 if g_debug then
331 hr_utility.set_location(l_proc, 20);
332 end if;
333 --
334 -- Call Before Process User Hook
335 --
336 begin
337 --
338 hxc_time_category_BK_3.delete_time_category_b
339 (p_time_category_id => p_time_category_id
340 ,p_time_Category_name => p_time_category_name
341 ,p_object_version_number => p_object_version_number
342 );
343 exception
344 when hr_api.cannot_find_prog_unit then
345 hr_api.cannot_find_prog_unit_error
346 (p_module_name => 'delete_time_category_b'
347 ,p_hook_type => 'BP'
348 );
349 end;
350 --
351 if g_debug then
352 hr_utility.set_location(l_proc, 30);
353 end if;
354 --
355 -- Process Logic
356 --
357 hxc_htc_del.del
358 (
359 p_time_category_id => p_time_category_id
360 ,p_time_category_name => p_time_category_name
361 ,p_object_version_number => p_object_version_number
362 );
363 --
364 if g_debug then
365 hr_utility.set_location(l_proc, 40);
366 end if;
367 --
368 -- Call After Process User Hook
369 --
370 begin
371 --
372 hxc_time_category_BK_3.delete_time_category_a
373 (p_time_category_id => p_time_category_id
374 ,p_time_Category_name => p_time_category_name
375 ,p_object_version_number => p_object_version_number
376 );
377 exception
378 when hr_api.cannot_find_prog_unit then
379 hr_api.cannot_find_prog_unit_error
380 (p_module_name => 'delete_time_category_a'
381 ,p_hook_type => 'AP'
382 );
383 end;
384 --
385 -- When in validation only mode raise the Validate_Enabled exception
386 --
387 if p_validate then
388 raise hr_api.validate_enabled;
389 end if;
390 --
391 if g_debug then
392 hr_utility.set_location(' Leaving:'||l_proc, 50);
393 end if;
394 --
395 exception
396 --
397 when hr_api.validate_enabled then
398 --
399 -- As the Validate_Enabled exception has been raised
400 -- we must rollback to the savepoint
401 --
402 ROLLBACK TO delete_time_category;
403 --
404 when others then
405 --
406 -- A validation or unexpected error has occured
407 --
408 ROLLBACK TO delete_time_category;
409 raise;
410 --
411 end delete_time_category;
412 --
413 procedure set_dynamic_sql_string ( p_time_category_id NUMBER ) IS
414
415 l_time_sql long;
416 l_time_category_id hxc_time_Categories.time_category_id%TYPE;
417 l_operator hxc_time_categories.operator%TYPE;
418
419 BEGIN
420
421 g_debug := hr_utility.debug_enabled;
422
423 hxc_time_category_utils_pkg.mapping_component_string (
424 p_time_category_id => p_time_category_id
425 , p_time_sql => l_time_sql );
426
427 if g_debug then
428 hr_utility.trace('set dyn sql string string is '||l_time_sql);
429 end if;
430
431 UPDATE hxc_time_categories
432 SET time_sql = l_time_sql
433 WHERE time_category_id = p_time_category_id;
434
435 exception when others then
436
437 if g_debug then
438 hr_utility.trace('exception is '||SQLERRM);
439 end if;
440
441 raise;
442
443 END set_dynamic_sql_string;
444
445
446 -- Bug No : 7680264
447 -- Created the following procs.
448 -- delete_old_comps is called from hxcdeltcc.sql
449 -- to delete all the old corrupt Comps.
450 -- This happens only for the three seeded time categories below.
451
452 PROCEDURE delete_old_comps
453 IS
454
455 l_cat VARCHAR2(50);
456
457 CURSOR get_corrupt_tcs
458 IS SELECT cat.time_category_id
459 FROM hxc_time_categories cat,
460 hxc_time_category_comps comp
461 WHERE cat.time_category_id = comp.time_category_id
462 AND cat.time_category_name IN ('Payroll Processing Fields',
463 'Projects Processing Fields',
464 'HR Processing Fields')
465 GROUP
466 BY cat.time_category_id,
467 component_type_id
468 HAVING COUNT(1) > 1;
469
470 TYPE NUMTABLE IS TABLE OF NUMBER;
471 l_tc_tab NUMTABLE;
472
473
474 BEGIN
475
476 OPEN get_corrupt_tcs;
477 FETCH get_corrupt_tcs BULK COLLECT INTO l_tc_tab;
478 IF l_tc_tab.COUNT = 0
479 THEN
480 CLOSE get_corrupt_tcs;
481 RETURN;
482 END IF;
483 CLOSE get_corrupt_tcs;
484
485 IF l_tc_tab.COUNT > 0
486 THEN
487 FORALL i IN l_tc_tab.FIRST..l_tc_tab.LAST
488 DELETE FROM hxc_time_category_comps
489 WHERE time_category_id = l_tc_tab(i);
490 END IF;
491
492 COMMIT;
493 RETURN;
494
495 END delete_old_comps;
496
497
498 -- Bug No : 7680264
499 -- Returns the Mapping component name of the component Id passed.
500 -- Used in the download section of hxctmcatimcatgry.lct to return
501 -- the component type's name.
502
503 FUNCTION get_component_type_name( p_component_type_id IN NUMBER)
504 RETURN VARCHAR2
505 IS
506
507 l_comp_name VARCHAR2(50);
508
509 BEGIN
510 SELECT name
511 INTO l_comp_name
512 FROM hxc_mapping_components
513 WHERE mapping_component_id = p_component_type_id;
514
515 RETURN l_comp_name;
516
517 EXCEPTION
518 WHEN NO_DATA_FOUND THEN
519 RETURN NULL;
520 END get_component_type_name;
521
522
523
524 END hxc_time_category_api;