[Home] [Help]
PACKAGE BODY: APPS.HR_TEMPLATE_DATA_GROUPS_API
Source
1 Package Body hr_template_data_groups_api as
2 /* $Header: hrtdgapi.pkb 115.4 2004/06/21 09:20:50 njaladi noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_template_data_groups_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< copy_template_data_group >--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure copy_template_data_group
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_language_code in varchar2 default hr_api.userenv_lang
16 ,p_template_data_group_id_from in number
17 ,p_form_template_id in number
18 ,p_template_data_group_id_to out nocopy number
19 ,p_object_version_number out nocopy number
20 ) is
21 --
22 -- Declare cursors and local variables
23 --
24 CURSOR cur_data_group
25 IS
26 SELECT tdg.form_data_group_id
27 FROM hr_template_data_groups tdg
28 WHERE tdg.template_data_group_id = p_template_data_group_id_from;
29
30 -- Bug # 3648566. Modified the cursor text to use base table hr_template_items_b
31 -- instead of view hr_template_items.
32 CURSOR cur_tmplt_item
33 IS
34 SELECT tit1.template_item_id
35 FROM hr_template_items_b tit2
36 ,hr_template_items_b tit1
37 ,hr_template_data_groups tdg
38 WHERE tit2.template_item_id IS NULL
39 AND tit2.form_template_id (+) = p_form_template_id
40 AND tit2.form_item_id (+) = tit1.form_item_id
41 AND tit1.form_template_id = tdg.form_template_id
42 AND tdg.template_data_group_id = p_template_data_group_id_from;
43
44 l_temp number;
45
46 CURSOR cur_api_val
47 IS
48 SELECT source_form_template_id
49 FROM hr_source_form_templates hsf
50 WHERE hsf.form_template_id_to = p_form_template_id;
51
52 l_form_data_group_id number;
53 -- the next 2 local vars are used only for storing the unused values
54 l_template_item_id_to number;
55 l_ovn_item number;
56
57 l_proc varchar2(72) := g_package||'copy_template_data_group';
58 l_object_version_number number;
59 l_template_data_group_id_to number;
60 l_language_code fnd_languages.language_code%TYPE;
61
62 begin
63 hr_utility.set_location('Entering:'|| l_proc, 10);
64 --
65 -- Issue a savepoint
66 --
67 savepoint copy_template_data_group;
68 --
69 -- Truncate the time portion from all IN date parameters
70 --
71 -- p_effective_date := TRUNC(p_effective_date);
72 --
73 -- Validate the language parameter. l_language_code should be passed
74 -- instead of p_language_code from now on, to allow an IN OUT parameter to
75 -- be passed through.
76 --
77 l_language_code := p_language_code;
78 hr_api.validate_language_code(p_language_code => l_language_code);
79 --
80 hr_utility.set_location(l_proc, 20);
81 --
82 --
83 -- Call Before Process User Hook
84 --
85 begin
86 hr_template_data_groups_bk1.copy_template_data_group_b
87 (p_effective_date => TRUNC(p_effective_date)
88 ,p_language_code => l_language_code
89 ,p_template_data_group_id_from => p_template_data_group_id_from
90 ,p_form_template_id => p_form_template_id
91 );
92 exception
93 when hr_api.cannot_find_prog_unit then
94 hr_api.cannot_find_prog_unit_error
95 (p_module_name => 'copy_template_data_group'
96 ,p_hook_type => 'BP'
97 );
98 end;
99 --
100 -- Validation in addition to Row Handlers
101 --
102 hr_utility.set_location('At:'|| l_proc, 15);
103
104 OPEN cur_api_val;
105 FETCH cur_api_val INTO l_temp;
106 IF (cur_api_val%NOTFOUND AND
107 hr_form_templates_api.g_session_mode <> 'SEED_DATA') THEN
108 CLOSE cur_api_val;
109 -- error message
110 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
111 fnd_message.set_token('PROCEDURE', l_proc);
112 fnd_message.set_token('STEP','10');
113 fnd_message.raise_error;
114 END IF;
115 CLOSE cur_api_val;
116 --
117 --
118 -- Process Logic
119 --
120 hr_utility.set_location('At:'|| l_proc, 20);
121
122 OPEN cur_data_group;
123 FETCH cur_data_group INTO l_form_data_group_id;
124 CLOSE cur_data_group;
125
126 hr_utility.set_location('At:'|| l_proc, 25);
127
128 hr_tdg_ins.ins(p_form_template_id => p_form_template_id
129 ,p_form_data_group_id => l_form_data_group_id
130 ,p_template_data_group_id => l_template_data_group_id_to
131 ,p_object_version_number => l_object_version_number);
132
133 hr_utility.set_location('At:'|| l_proc, 30);
134
135 FOR cur_rec in cur_tmplt_item LOOP
136 hr_template_items_api.copy_template_item(
137 p_effective_date => TRUNC(p_effective_date)
138 ,p_language_code => l_language_code
139 ,p_template_item_id_from => cur_rec.template_item_id
140 ,p_form_template_id => p_form_template_id
141 ,p_template_item_id_to => l_template_item_id_to
142 ,p_object_version_number => l_ovn_item);
143
144 END LOOP;
145 --
146 -- Call After Process User Hook
147 --
148 hr_utility.set_location('At:'|| l_proc, 35);
149
150 begin
151 hr_template_data_groups_bk1.copy_template_data_group_a
152 (p_effective_date => TRUNC(p_effective_date)
153 ,p_language_code => l_language_code
154 ,p_template_data_group_id_from => p_template_data_group_id_from
155 ,p_form_template_id => p_form_template_id
156 ,p_template_data_group_id_to => l_template_data_group_id_to
157 ,p_object_version_number => l_object_version_number
158 );
159 exception
160 when hr_api.cannot_find_prog_unit then
161 hr_api.cannot_find_prog_unit_error
162 (p_module_name => 'copy_template_data_group'
163 ,p_hook_type => 'AP'
164 );
165 end;
166 --
167 -- When in validation only mode raise the Validate_Enabled exception
168 --
169 hr_utility.set_location('At:'|| l_proc, 40);
170
171 if p_validate then
172 raise hr_api.validate_enabled;
173 end if;
174 --
175 -- Set all output arguments
176 --
177 p_template_data_group_id_to := l_template_data_group_id_to;
178 p_object_version_number := l_object_version_number;
179 --
180 hr_utility.set_location(' Leaving:'||l_proc, 70);
181 exception
182 when hr_api.validate_enabled then
183 --
184 -- As the Validate_Enabled exception has been raised
185 -- we must rollback to the savepoint
186 --
187 rollback to copy_template_data_group;
188 --
189 -- Only set output warning arguments
190 -- (Any key or derived arguments must be set to null
191 -- when validation only mode is being used.)
192 --
193 p_template_data_group_id_to := null;
194 p_object_version_number := null;
195 hr_utility.set_location(' Leaving:'||l_proc, 80);
196 when others then
197 --
198 -- A validation or unexpected error has occured
199 --
200 p_template_data_group_id_to := null;
201 p_object_version_number := null;
202
203 rollback to copy_template_data_group;
204 hr_utility.set_location(' Leaving:'||l_proc, 90);
205 raise;
206 end copy_template_data_group;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |----------------------< create_template_data_group >----------------------|
210 -- ----------------------------------------------------------------------------
211 --
212 procedure create_template_data_group
213 (p_validate in boolean default false
214 ,p_effective_date in date
215 ,p_form_template_id in number
216 ,p_form_data_group_id in number
217 ,p_template_data_group_id out nocopy number
218 ,p_object_version_number out nocopy number
219 ) is
220 --
221 -- Declare cursors and local variables
222 --
223
224 CURSOR cur_form_item
225 IS
226 SELECT fit.form_item_id
227 FROM hr_template_items tit
228 ,hr_form_items fit
229 ,hr_form_data_group_items fgi
230 WHERE tit.template_item_id IS NULL
231 AND tit.form_template_id (+) = p_form_template_id
232 AND tit.form_item_id (+) = fit.form_item_id
233 AND fit.form_item_id = fgi.form_item_id
234 AND fgi.form_data_group_id = p_form_data_group_id;
235
236 l_temp number;
237
238 CURSOR cur_api_val
239 IS
240 SELECT source_form_template_id
241 FROM hr_source_form_templates hsf
242 WHERE hsf.form_template_id_to = p_form_template_id;
243
244 l_proc varchar2(72) := g_package||'create_template_data_group';
245 l_template_data_group_id number;
246 l_object_version_number number;
247 l_template_item_id number;
248 l_ovn number;
249 l_override_value_warning boolean;
250 begin
251 hr_utility.set_location('Entering:'|| l_proc, 10);
252 --
253 -- Issue a savepoint
254 --
255 savepoint create_template_data_group;
256 --
257 -- Truncate the time portion from all IN date parameters
258 --
259 -- p_effective_date := TRUNC(p_effective_date);
260 --
261 -- Call Before Process User Hook
262 --
263 begin
264 hr_template_data_groups_bk2.create_template_data_group_b
265 (p_effective_date => TRUNC(p_effective_date)
266 ,p_form_data_group_id => p_form_data_group_id
267 ,p_form_template_id => p_form_template_id
268 );
269 exception
270 when hr_api.cannot_find_prog_unit then
271 hr_api.cannot_find_prog_unit_error
272 (p_module_name => 'create_template_data_group'
273 ,p_hook_type => 'BP'
274 );
275 end;
276 --
277 -- Validation in addition to Row Handlers
278 --
279
280 hr_utility.set_location('At:'|| l_proc, 15);
281
282 OPEN cur_api_val;
283 FETCH cur_api_val INTO l_temp;
284 IF (cur_api_val%NOTFOUND AND
285 hr_form_templates_api.g_session_mode <> 'SEED_DATA') THEN
286 CLOSE cur_api_val;
287 -- error message
288 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
289 fnd_message.set_token('PROCEDURE', l_proc);
290 fnd_message.set_token('STEP','10');
291 fnd_message.raise_error;
292 END IF;
293 CLOSE cur_api_val;
294 --
295 --
296 -- Process Logic
297 --
298 hr_utility.set_location('At:'|| l_proc, 20);
299
300 hr_tdg_ins.ins(p_form_template_id => p_form_template_id
301 ,p_form_data_group_id => p_form_data_group_id
302 ,p_template_data_group_id => l_template_data_group_id
303 ,p_object_version_number => l_object_version_number);
304
305 hr_utility.set_location('At:'|| l_proc, 25);
306
307 FOR cur_rec in cur_form_item LOOP
308 hr_template_items_api.create_template_item(
309 p_effective_date => TRUNC(p_effective_date)
310 ,p_form_template_id => p_form_template_id
311 ,p_form_item_id => cur_rec.form_item_id
312 ,p_template_item_id => l_template_item_id
313 ,p_object_version_number => l_ovn
314 ,p_override_value_warning => l_override_value_warning);
315 END LOOP;
316
317 hr_utility.set_location('At:'|| l_proc, 30);
318
319 --
320 -- Call After Process User Hook
321 --
322 begin
323 hr_template_data_groups_bk2.create_template_data_group_a
324 (p_effective_date => TRUNC(p_effective_date)
325 ,p_form_data_group_id => p_form_data_group_id
326 ,p_form_template_id => p_form_template_id
327 ,p_template_data_group_id => l_template_data_group_id
328 ,p_object_version_number => l_object_version_number);
329
330 exception
331 when hr_api.cannot_find_prog_unit then
332 hr_api.cannot_find_prog_unit_error
333 (p_module_name => 'create_template_data_group'
334 ,p_hook_type => 'AP'
335 );
336 end;
337 --
338 -- When in validation only mode raise the Validate_Enabled exception
339 --
340 hr_utility.set_location('At:'|| l_proc, 35);
341
342 if p_validate then
343 raise hr_api.validate_enabled;
344 end if;
345 --
346 -- Set all output arguments
347 --
348 p_template_data_group_id := l_template_data_group_id;
349 p_object_version_number := l_object_version_number;
350 --
351 hr_utility.set_location(' Leaving:'||l_proc, 70);
352 exception
353 when hr_api.validate_enabled then
354 --
355 -- As the Validate_Enabled exception has been raised
356 -- we must rollback to the savepoint
357 --
358 rollback to create_template_data_group;
359 --
360 -- Only set output warning arguments
361 -- (Any key or derived arguments must be set to null
362 -- when validation only mode is being used.)
363 --
364 p_template_data_group_id := null;
365 p_object_version_number := null;
366 hr_utility.set_location(' Leaving:'||l_proc, 80);
367 when others then
368 --
369 -- A validation or unexpected error has occured
370 --
371 p_template_data_group_id := null;
372 p_object_version_number := null;
373
374 rollback to create_template_data_group;
375 hr_utility.set_location(' Leaving:'||l_proc, 90);
376 raise;
377 end create_template_data_group;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |-----------------------< delete_template_data_group >---------------------|
381 -- ----------------------------------------------------------------------------
382 --
383 procedure delete_template_data_group
384 (p_validate in boolean default false
385 ,p_template_data_group_id in number
386 ,p_object_version_number in number
387 ) is
388 --
389 -- Declare cursors and local variables
390 --
391
392 CURSOR cur_tmplt_item
393 IS
394 SELECT tit.template_item_id
395 ,tit.object_version_number
396 FROM hr_template_items tit
397 ,hr_form_data_group_items fgi
398 ,hr_template_data_groups tdg
399 WHERE tit.form_template_id = tdg.form_template_id
400 AND tit.form_item_id = fgi.form_item_id
401 AND fgi.form_data_group_id = tdg.form_data_group_id
402 AND tdg.template_data_group_id = p_template_data_group_id
403 MINUS
404 SELECT tit.template_item_id
405 ,tit.object_version_number
406 FROM hr_template_items tit
407 ,hr_form_data_group_items fgi
408 ,hr_template_data_groups tdg2
409 ,hr_template_data_groups tdg1
410 WHERE tit.form_template_id = tdg2.form_template_id
411 AND tit.form_item_id = fgi.form_item_id
412 AND fgi.form_data_group_id = tdg2.form_data_group_id
413 AND tdg2.template_data_group_id <> tdg1.template_data_group_id
414 AND tdg2.form_template_id = tdg1.form_template_id
415 AND tdg1.template_data_group_id = p_template_data_group_id;
416
417 l_temp number;
418
419 CURSOR cur_api_val
420 IS
421 SELECT source_form_template_id
422 FROM hr_source_form_templates hsf
423 ,hr_template_data_groups tdg
424 WHERE hsf.form_template_id_to = tdg.form_template_id
425 AND tdg.template_data_group_id = p_template_data_group_id;
426
427 l_proc varchar2(72) := g_package||'delete_template_data_group';
428 begin
429 hr_utility.set_location('Entering:'|| l_proc, 10);
430 --
431 -- Issue a savepoint
432 --
433 savepoint delete_template_data_group;
434 --
435 -- Call Before Process User Hook
436 --
437 begin
438 hr_template_data_groups_bk3.delete_template_data_group_b
439 (p_template_data_group_id => p_template_data_group_id
440 ,p_object_version_number => p_object_version_number);
441 exception
442 when hr_api.cannot_find_prog_unit then
443 hr_api.cannot_find_prog_unit_error
444 (p_module_name => 'delete_template_data_group'
445 ,p_hook_type => 'BP'
446 );
447 end;
448 --
449 -- Validation in addition to Row Handlers
450 --
451 hr_utility.set_location('At:'|| l_proc, 15);
452
453 OPEN cur_api_val;
454 FETCH cur_api_val INTO l_temp;
455 IF (cur_api_val%NOTFOUND AND
456 hr_form_templates_api.g_session_mode <> 'SEED_DATA') THEN
457 CLOSE cur_api_val;
458 -- error message
459 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
460 fnd_message.set_token('PROCEDURE', l_proc);
461 fnd_message.set_token('STEP','10');
462 fnd_message.raise_error;
463 END IF;
464 CLOSE cur_api_val;
465 --
466 --
467 -- Process Logic
468 --
469 hr_utility.set_location('At:'|| l_proc, 20);
470
471 hr_tdg_shd.lck( p_template_data_group_id => p_template_data_group_id
472 ,p_object_version_number => p_object_version_number);
473
474 hr_utility.set_location('At:'|| l_proc, 25);
475
476 FOR cur_rec in cur_tmplt_item LOOP
477 hr_template_items_api.delete_template_item(
478 p_template_item_id => cur_rec.template_item_id
479 ,p_object_version_number => cur_rec.object_version_number);
480 END LOOP;
481
482 hr_utility.set_location('At:'|| l_proc, 30);
483
484 hr_tdg_del.del( p_template_data_group_id => p_template_data_group_id
485 ,p_object_version_number => p_object_version_number);
486
487 hr_utility.set_location('At:'|| l_proc, 35);
488
489 --
490 -- Call After Process User Hook
491 --
492 begin
493 hr_template_data_groups_bk3.delete_template_data_group_a
494 (p_template_data_group_id => p_template_data_group_id
495 ,p_object_version_number => p_object_version_number);
496 exception
497 when hr_api.cannot_find_prog_unit then
498 hr_api.cannot_find_prog_unit_error
499 (p_module_name => 'delete_template_data_group'
500 ,p_hook_type => 'AP'
501 );
502 end;
503 --
504 -- When in validation only mode raise the Validate_Enabled exception
505 --
506 hr_utility.set_location('At:'|| l_proc, 40);
507
508 if p_validate then
509 raise hr_api.validate_enabled;
510 end if;
511 --
512 hr_utility.set_location(' Leaving:'||l_proc, 70);
513 exception
514 when hr_api.validate_enabled then
515 --
516 -- As the Validate_Enabled exception has been raised
517 -- we must rollback to the savepoint
518 --
519 rollback to delete_template_data_group;
520 --
521 -- Only set output warning arguments
522 -- (Any key or derived arguments must be set to null
523 -- when validation only mode is being used.)
524 --
525 hr_utility.set_location(' Leaving:'||l_proc, 80);
526 when others then
527 --
528 -- A validation or unexpected error has occured
529 --
530 rollback to delete_template_data_group;
531 hr_utility.set_location(' Leaving:'||l_proc, 90);
532 raise;
533 end delete_template_data_group;
534 --
535 end hr_template_data_groups_api;