[Home] [Help]
PACKAGE BODY: APPS.PQH_TEMPLATE_ATTRIBUTES_API
Source
1 Package Body pqh_TEMPLATE_ATTRIBUTES_api as
2 /* $Header: pqtatapi.pkb 115.10 2002/12/06 23:48:33 rpasapul noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pqh_TEMPLATE_ATTRIBUTES_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 --|------------------------< create_TEMPLATE_ATTRIBUTE >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_TEMPLATE_ATTRIBUTE
13 (p_validate in boolean default false
14 ,p_required_flag in varchar2 default null
15 ,p_view_flag in varchar2 default null
16 ,p_edit_flag in varchar2 default null
17 ,p_template_attribute_id out nocopy number
18 ,p_attribute_id in number default null
19 ,p_template_id in number default null
20 ,p_object_version_number out nocopy number
21 ,p_effective_date in date
22 ) is
23 --
24 -- Declare cursors and local variables
25 --
26 l_template_attribute_id pqh_template_attributes.template_attribute_id%TYPE;
27 l_proc varchar2(72) := g_package||'create_TEMPLATE_ATTRIBUTE';
28 l_object_version_number pqh_template_attributes.object_version_number%TYPE;
29 --
30 begin
31 --
32 hr_utility.set_location('Entering:'|| l_proc, 10);
33 --
34 -- Issue a savepoint if operating in validation only mode
35 --
36 savepoint create_TEMPLATE_ATTRIBUTES;
37 --
38 hr_utility.set_location(l_proc, 20);
39 --
40 -- Process Logic
41 --
42 begin
43 --
44 -- Start of API User Hook for the before hook of create_TEMPLATE_ATTRIBUTE
45 --
46 pqh_TEMPLATE_ATTRIBUTES_bk1.create_TEMPLATE_ATTRIBUTE_b
47 (
48 p_required_flag => p_required_flag
49 ,p_view_flag => p_view_flag
50 ,p_edit_flag => p_edit_flag
51 ,p_attribute_id => p_attribute_id
52 ,p_template_id => p_template_id
53 ,p_effective_date => trunc(p_effective_date)
54 );
55 exception
56 when hr_api.cannot_find_prog_unit then
57 hr_api.cannot_find_prog_unit_error
58 (
59 p_module_name => 'CREATE_TEMPLATE_ATTRIBUTE'
60 ,p_hook_type => 'BP'
61 );
62 --
63 -- End of API User Hook for the before hook of create_TEMPLATE_ATTRIBUTE
64 --
65 end;
66 --
67 pqh_tat_ins.ins
68 (
69 p_template_attribute_id => l_template_attribute_id
70 ,p_required_flag => p_required_flag
71 ,p_view_flag => p_view_flag
72 ,p_edit_flag => p_edit_flag
73 ,p_attribute_id => p_attribute_id
74 ,p_template_id => p_template_id
75 ,p_object_version_number => l_object_version_number
76 ,p_effective_date => trunc(p_effective_date)
77 );
78 --
79 begin
80 --
81 -- Start of API User Hook for the after hook of create_TEMPLATE_ATTRIBUTE
82 --
83 pqh_TEMPLATE_ATTRIBUTES_bk1.create_TEMPLATE_ATTRIBUTE_a
84 (
85 p_required_flag => p_required_flag
86 ,p_view_flag => p_view_flag
87 ,p_edit_flag => p_edit_flag
88 ,p_template_attribute_id => l_template_attribute_id
89 ,p_attribute_id => p_attribute_id
90 ,p_template_id => p_template_id
91 ,p_object_version_number => l_object_version_number
92 ,p_effective_date => trunc(p_effective_date)
93 );
94 exception
95 when hr_api.cannot_find_prog_unit then
96 hr_api.cannot_find_prog_unit_error
97 (p_module_name => 'CREATE_TEMPLATE_ATTRIBUTE'
98 ,p_hook_type => 'AP'
99 );
100 --
101 -- End of API User Hook for the after hook of create_TEMPLATE_ATTRIBUTE
102 --
103 end;
104 --
105 hr_utility.set_location(l_proc, 60);
106 --
107 -- When in validation only mode raise the Validate_Enabled exception
108 --
109 if p_validate then
110 raise hr_api.validate_enabled;
111 end if;
112 --
113 -- Set all output arguments
114 --
115 p_template_attribute_id := l_template_attribute_id;
116 p_object_version_number := l_object_version_number;
117 --
118 hr_utility.set_location(' Leaving:'||l_proc, 70);
119 --
120 exception
121 --
122 when hr_api.validate_enabled then
123 --
124 -- As the Validate_Enabled exception has been raised
125 -- we must rollback to the savepoint
126 --
127 ROLLBACK TO create_TEMPLATE_ATTRIBUTES;
128 --
129 -- Only set output warning arguments
130 -- (Any key or derived arguments must be set to null
131 -- when validation only mode is being used.)
132 --
133 p_template_attribute_id := null;
134 p_object_version_number := null;
135 hr_utility.set_location(' Leaving:'||l_proc, 80);
136 --
137 when others then
138 --
139 -- A validation or unexpected error has occured
140 --
141 ROLLBACK TO create_TEMPLATE_ATTRIBUTES;
142 raise;
143 --
144 end create_TEMPLATE_ATTRIBUTE;
145 -- ----------------------------------------------------------------------------
146 -- |------------------------< update_TEMPLATE_ATTRIBUTE >-- ------------------|
147 -- ----------------------------------------------------------------------------
148 --
149 procedure update_TEMPLATE_ATTRIBUTE
150 (p_validate in boolean default false
151 ,p_required_flag in varchar2 default hr_api.g_varchar2
152 ,p_view_flag in varchar2 default hr_api.g_varchar2
153 ,p_edit_flag in varchar2 default hr_api.g_varchar2
154 ,p_template_attribute_id in number
155 ,p_attribute_id in number default hr_api.g_number
156 ,p_template_id in number default hr_api.g_number
157 ,p_object_version_number in out nocopy number
158 ,p_effective_date in date
159 ) is
160 --
161 -- Declare cursors and local variables
162 --
163 l_proc varchar2(72) := g_package||'update_TEMPLATE_ATTRIBUTE';
164 l_object_version_number pqh_template_attributes.object_version_number%TYPE;
165 --
166 begin
167 --
168 hr_utility.set_location('Entering:'|| l_proc, 10);
169 --
170 -- Issue a savepoint if operating in validation only mode
171 --
172 savepoint update_TEMPLATE_ATTRIBUTES;
173 --
174 hr_utility.set_location(l_proc, 20);
175 --
176 -- Process Logic
177 --
178 l_object_version_number := p_object_version_number;
179 --
180 begin
181 --
182 -- Start of API User Hook for the before hook of update_TEMPLATE_ATTRIBUTE
183 --
184 pqh_TEMPLATE_ATTRIBUTES_bk2.update_TEMPLATE_ATTRIBUTE_b
185 (
186 p_required_flag => p_required_flag
187 ,p_view_flag => p_view_flag
188 ,p_edit_flag => p_edit_flag
189 ,p_template_attribute_id => p_template_attribute_id
190 ,p_attribute_id => p_attribute_id
191 ,p_template_id => p_template_id
192 ,p_object_version_number => p_object_version_number
193 ,p_effective_date => trunc(p_effective_date)
194 );
195 exception
196 when hr_api.cannot_find_prog_unit then
197 hr_api.cannot_find_prog_unit_error
198 (p_module_name => 'UPDATE_TEMPLATE_ATTRIBUTE'
199 ,p_hook_type => 'BP'
200 );
201 --
202 -- End of API User Hook for the before hook of update_TEMPLATE_ATTRIBUTE
203 --
204 end;
205 --
206 pqh_tat_upd.upd
207 (
208 p_template_attribute_id => p_template_attribute_id
209 ,p_required_flag => p_required_flag
210 ,p_view_flag => p_view_flag
211 ,p_edit_flag => p_edit_flag
212 ,p_attribute_id => p_attribute_id
213 ,p_template_id => p_template_id
214 ,p_object_version_number => l_object_version_number
215 ,p_effective_date => trunc(p_effective_date)
216 );
217 --
218 begin
219 --
220 -- Start of API User Hook for the after hook of update_TEMPLATE_ATTRIBUTE
221 --
222 pqh_TEMPLATE_ATTRIBUTES_bk2.update_TEMPLATE_ATTRIBUTE_a
223 (
224 p_required_flag => p_required_flag
225 ,p_view_flag => p_view_flag
226 ,p_edit_flag => p_edit_flag
227 ,p_template_attribute_id => p_template_attribute_id
228 ,p_attribute_id => p_attribute_id
229 ,p_template_id => p_template_id
230 ,p_object_version_number => l_object_version_number
231 ,p_effective_date => trunc(p_effective_date)
232 );
233 exception
234 when hr_api.cannot_find_prog_unit then
235 hr_api.cannot_find_prog_unit_error
236 (p_module_name => 'UPDATE_TEMPLATE_ATTRIBUTE'
237 ,p_hook_type => 'AP'
238 );
239 --
240 -- End of API User Hook for the after hook of update_TEMPLATE_ATTRIBUTE
241 --
242 end;
243 --
244 hr_utility.set_location(l_proc, 60);
245 --
246 -- When in validation only mode raise the Validate_Enabled exception
247 --
248 if p_validate then
249 raise hr_api.validate_enabled;
250 end if;
251 --
252 -- Set all output arguments
253 --
254 p_object_version_number := l_object_version_number;
255 --
256 hr_utility.set_location(' Leaving:'||l_proc, 70);
257 --
258 exception
259 --
260 when hr_api.validate_enabled then
261 --
262 -- As the Validate_Enabled exception has been raised
263 -- we must rollback to the savepoint
264 --
265 ROLLBACK TO update_TEMPLATE_ATTRIBUTES;
266 --
267 -- Only set output warning arguments
268 -- (Any key or derived arguments must be set to null
269 -- when validation only mode is being used.)
270 --
271 hr_utility.set_location(' Leaving:'||l_proc, 80);
272 --
273 when others then
274 --
275 -- A validation or unexpected error has occured
276 --
277 ROLLBACK TO update_TEMPLATE_ATTRIBUTES;
278 raise;
279 --
280 end update_TEMPLATE_ATTRIBUTE;
281 -- ----------------------------------------------------------------------------
282 -- |------------------------< delete_TEMPLATE_ATTRIBUTE >----------------------|
283 -- ----------------------------------------------------------------------------
284 --
285 procedure delete_TEMPLATE_ATTRIBUTE
286 (p_validate in boolean default false
287 ,p_template_attribute_id in number
288 ,p_object_version_number in number
289 ,p_effective_date in date
290 ) is
291 --
292 -- Declare cursors and local variables
293 --
294 l_proc varchar2(72) := g_package||'delete_TEMPLATE_ATTRIBUTE';
295 l_object_version_number pqh_template_attributes.object_version_number%TYPE;
296 --
297 begin
298 --
299 hr_utility.set_location('Entering:'|| l_proc, 10);
300 --
301 -- Issue a savepoint if operating in validation only mode
302 --
303 savepoint delete_TEMPLATE_ATTRIBUTES;
304 --
305 hr_utility.set_location(l_proc, 20);
306 --
307 -- Process Logic
308 --
309 l_object_version_number := p_object_version_number;
310 --
311 --
312 begin
313 --
314 -- Start of API User Hook for the before hook of delete_TEMPLATE_ATTRIBUTE
315 --
316 pqh_TEMPLATE_ATTRIBUTES_bk3.delete_TEMPLATE_ATTRIBUTE_b
317 (
318 p_template_attribute_id => p_template_attribute_id
319 ,p_object_version_number => p_object_version_number
320 ,p_effective_date => trunc(p_effective_date)
321 );
322 exception
323 when hr_api.cannot_find_prog_unit then
324 hr_api.cannot_find_prog_unit_error
325 (p_module_name => 'DELETE_TEMPLATE_ATTRIBUTE'
326 ,p_hook_type => 'BP'
327 );
328 --
329 -- End of API User Hook for the before hook of delete_TEMPLATE_ATTRIBUTE
330 --
331 end;
332 --
333 pqh_tat_del.del
334 (
335 p_template_attribute_id => p_template_attribute_id
336 ,p_object_version_number => l_object_version_number
337 ,p_effective_date => p_effective_date
338 );
339 --
340 begin
341 --
342 -- Start of API User Hook for the after hook of delete_TEMPLATE_ATTRIBUTE
343 --
344 pqh_TEMPLATE_ATTRIBUTES_bk3.delete_TEMPLATE_ATTRIBUTE_a
345 (
346 p_template_attribute_id => p_template_attribute_id
347 ,p_object_version_number => l_object_version_number
348 ,p_effective_date => trunc(p_effective_date)
349 );
350 exception
351 when hr_api.cannot_find_prog_unit then
352 hr_api.cannot_find_prog_unit_error
353 (p_module_name => 'DELETE_TEMPLATE_ATTRIBUTE'
354 ,p_hook_type => 'AP'
355 );
356 --
357 -- End of API User Hook for the after hook of delete_TEMPLATE_ATTRIBUTE
358 --
359 end;
360 --
361 hr_utility.set_location(l_proc, 60);
362 --
363 -- When in validation only mode raise the Validate_Enabled exception
364 --
365 if p_validate then
366 raise hr_api.validate_enabled;
367 end if;
368 --
369 hr_utility.set_location(' Leaving:'||l_proc, 70);
370 --
371 exception
372 --
373 when hr_api.validate_enabled then
374 --
375 -- As the Validate_Enabled exception has been raised
376 -- we must rollback to the savepoint
377 --
378 ROLLBACK TO delete_TEMPLATE_ATTRIBUTES;
379 --
380 -- Only set output warning arguments
381 -- (Any key or derived arguments must be set to null
382 -- when validation only mode is being used.)
383 --
384 --
385 when others then
386 --
387 -- A validation or unexpected error has occured
388 --
389 ROLLBACK TO delete_TEMPLATE_ATTRIBUTES;
390 raise;
391 --
392 end delete_TEMPLATE_ATTRIBUTE;
393 --
394 -- ----------------------------------------------------------------------------
395 -- |-------------------------------< lck >------------------------------------|
396 -- ----------------------------------------------------------------------------
397 --
398 procedure lck
399 (
400 p_template_attribute_id in number
401 ,p_object_version_number in number
402 ) is
403 --
404 --
405 -- Declare cursors and local variables
406 --
407 l_proc varchar2(72) := g_package||'lck';
408 --
409 begin
410 --
411 hr_utility.set_location('Entering:'|| l_proc, 10);
412 --
413 pqh_tat_shd.lck
414 (
415 p_template_attribute_id => p_template_attribute_id
416 ,p_object_version_number => p_object_version_number
417 );
418 --
419 hr_utility.set_location(' Leaving:'||l_proc, 70);
420 --
421 end lck;
422 --
423 --
424 -------------------------------------------------------------------------------
425 -- |----------------< create_update_copied_attribute >------------------------
426 -------------------------------------------------------------------------------
427 --
428 procedure create_update_copied_attribute
429 (
430 p_copied_attributes in pqh_prvcalc.t_attid_priv,
431 p_template_id in number
432 ) is
433 --
434 Cursor csr_tem_attr(p_attribute_id in number) is
435 Select template_attribute_id,object_version_number
436 From pqh_template_attributes
437 Where template_id = p_template_id
438 and attribute_id = p_attribute_id
439 FOR UPDATE ;
440 --
441 cursor c_select_flag(p_attribute_id number, p_template_id number) is
442 select 'x'
443 from pqh_attributes att, pqh_txn_category_attributes tct, pqh_templates tem
444 where
445 att.attribute_id = p_attribute_id
446 and att.attribute_id = tct.attribute_id
447 and tem.template_id = p_template_id
448 and tct.transaction_category_id = tem.transaction_category_id
449 and nvl(tct.select_flag,'N')='Y';
450 --
451 l_template_attribute_id pqh_template_attributes.template_attribute_id%type;
452 l_ovn pqh_template_attributes.object_version_number%type;
453 l_view_flag pqh_template_attributes.view_flag%type;
454 l_edit_flag pqh_template_attributes.edit_flag%type;
455 --
456 l_dummy varchar2(30);
457 --
458 l_proc varchar2(72) := g_package||'create_update_copied_attribute';
459 --
460 begin
461 --
462 hr_utility.set_location('Entering:'|| l_proc, 10);
463 --
464 For cnt in p_copied_attributes.FIRST .. p_copied_attributes.LAST loop
465 --
466 open c_select_flag(p_copied_attributes(cnt).attribute_id, p_template_id);
467 fetch c_select_flag into l_dummy;
468 if c_select_flag%found then
469 --
470 If p_copied_attributes(cnt).mode_flag = 'E' then
471 l_view_flag := 'Y';
472 l_edit_flag := 'Y';
473 elsif p_copied_attributes(cnt).mode_flag ='V' then
474 l_view_flag := 'Y';
475 l_edit_flag := 'N';
476 Else
477 l_view_flag := 'N';
478 l_edit_flag := 'N';
479 End if;
480 --
481 Open csr_tem_attr(p_attribute_id =>p_copied_attributes(cnt).attribute_id);
482 --
483 Fetch csr_tem_attr into l_template_attribute_id,l_ovn;
484 --
485 If csr_tem_attr%found then
486 --
487 pqh_TEMPLATE_ATTRIBUTES_api.update_TEMPLATE_ATTRIBUTE
488 (p_validate => false
489 ,p_required_flag => p_copied_attributes(cnt).reqd_flag
490 ,p_view_flag => l_view_flag
491 ,p_edit_flag => l_edit_flag
492 ,p_template_attribute_id => l_template_attribute_id
493 ,p_attribute_id => p_copied_attributes(cnt).attribute_id
494 ,p_template_id => p_template_id
495 ,p_object_version_number => l_ovn
496 ,p_effective_date => sysdate);
497 --
498 Else
499 --
500 pqh_TEMPLATE_ATTRIBUTES_api.create_TEMPLATE_ATTRIBUTE
501 (p_validate => false
502 ,p_required_flag => p_copied_attributes(cnt).reqd_flag
503 ,p_view_flag => l_view_flag
504 ,p_edit_flag => l_edit_flag
505 ,p_template_attribute_id => l_template_attribute_id
506 ,p_attribute_id => p_copied_attributes(cnt).attribute_id
507 ,p_template_id => p_template_id
508 ,p_object_version_number => l_ovn
509 ,p_effective_date => sysdate);
510
511 --
512 End if;
513 --
514 Close csr_tem_attr;
515 end if;
516 close c_select_flag;
517 --
518
519 End loop;
520 --
521 hr_utility.set_location(' Leaving:'||l_proc, 70);
522 --
523 end create_update_copied_attribute;
524 --
525 --
526 end pqh_TEMPLATE_ATTRIBUTES_api;