[Home] [Help]
PACKAGE BODY: APPS.HXC_TIME_ATTRIBUTES_API
Source
1 package body hxc_time_attributes_api as
2 /* $Header: hxctatapi.pkb 120.4 2005/10/23 02:14:30 gkrishna noship $ */
3
4 g_package varchar2(33) := ' hxc_time_attributes_api.';
5
6 g_debug boolean := hr_utility.debug_enabled;
7
8 -- private procedure
9 -- extract_info_types
10 --
11 -- description
12 -- returns an array of distinct information types from the timecard
13 -- parameter passed into the hxc_deposit_process_pkg.execute_deposit_process
14 -- procedure. used by parse_table to extract attributes from the timecard
15 -- structure prior to insertion into hxc_time_attributes.
16 --
17 -- parameters
18 -- p_timecard - pl/sql table of attributes
19 -- p_info_types (out) - array of distinct information types
20
21 procedure extract_info_types
22 (p_timecard in timecard
23 ,p_info_types in out nocopy info_type_table
24 ) is
25
26 l_tc_loop number:=1;
27 l_it_loop number:=1;
28 l_info_types info_type_table;
29 l_info_type_noted boolean;
30
31 l_proc varchar2(30);
32 l_gaz varchar2(3);
33
34 begin
35
36
37 --if g_debug then
38 --l_proc := 'extract_info_types ';
39 --hr_utility.set_location('gaz: '|| l_proc, 50);
40 --end if;
41 -- initialize array. the maximum number of distinct information types
42 -- is equal to the number of attributes in the timecard
43 --l_gaz := to_char(p_timecard.count);
44 --if g_debug then
45 --hr_utility.set_location('gaz count is '|| l_gaz, 60);
46 --end if;
47 for l_rownum in 1..p_timecard.count loop
48 --if g_debug then
49 --hr_utility.set_location('gaz: '|| l_proc, 60);
50 --end if;
51 l_info_types(l_rownum):=null;
52 end loop;
53
54 -- identify distinct information types and store them by iterative
55 -- comparison of timecard and information type array
56 --if g_debug then
57 --hr_utility.set_location('gaz: '|| l_proc, 70);
58 --end if;
59 l_info_types(l_it_loop) := p_timecard(l_tc_loop).information_type;
60
61 for l_tc_loop in 1..p_timecard.count loop
62 --if g_debug then
63 --hr_utility.set_location('gaz: '|| l_proc, 80);
64 --end if;
65 if p_timecard(l_tc_loop).information_type <> l_info_types(l_it_loop) then
66 loop
67 --if g_debug then
68 --hr_utility.set_location('gaz: '|| l_proc, 90);
69 --end if;
70 l_it_loop:=l_it_loop+1;
71 if p_timecard(l_tc_loop).information_type = l_info_types(l_it_loop) then
72 --if g_debug then
73 --hr_utility.set_location('gaz: '|| l_proc, 100);
74 --end if;
75 l_info_type_noted := true;
76 end if;
77 --if g_debug then
78 --hr_utility.set_location('gaz: '|| l_proc, 150);
79 --end if;
80 exit when l_info_types(l_it_loop) is null;
81 end loop;
82 if not l_info_type_noted then
83 --if g_debug then
84 --hr_utility.set_location('gaz: '|| l_proc, 160);
85 --end if;
86 l_info_types(l_it_loop) := p_timecard(l_tc_loop).information_type;
87 end if;
88 end if;
89 l_it_loop := 1;
90 --if g_debug then
91 --hr_utility.set_location('gaz: '|| l_proc, 170);
92 --end if;
93 l_info_type_noted := false;
94 end loop;
95 --if g_debug then
96 --hr_utility.set_location('gaz: '|| l_proc, 180);
97 --end if;
98
99 -- delete empty cells from information type array
100 for l_it_loop in 1..p_timecard.count loop
101 --if g_debug then
102 --hr_utility.set_location('gaz: '|| l_proc, 190);
103 --end if;
104 if l_info_types(l_it_loop) is null then
105 --if g_debug then
106 --hr_utility.set_location('gaz: '|| l_proc, 200);
107 --end if;
108 l_info_types.delete(l_it_loop);
109 end if;
110 end loop;
111
112 --if g_debug then
113 --hr_utility.set_location('gaz: '|| l_proc, 210);
114 --end if;
115 -- assign out parameter
116 p_info_types := l_info_types;
117
118 end extract_info_types;
119
120
121 -- private procedure
122 -- parse_table
123 --
124 -- description
125 -- breaks down the pl/sql table passed to the deposit api into
126 -- its component attributes, prior to calling the HXC_TIME_ATTRIBUTES
127 -- row handler
128 --
129 -- parameters
130 -- p_process_id - deposit process id
131 -- p_timecard - attribute table
132 -- p_information_type - information type of attributes to parse
133 -- p_attribute1..30 - out parameters to contain mapped attribute values
134
135 procedure parse_table
136 (p_process_id in number
137 ,p_timecard in timecard
138 ,p_information_type in varchar2
139 ,p_attribute1 in out nocopy varchar2
140 ,p_attribute2 in out nocopy varchar2
141 ,p_attribute3 in out nocopy varchar2
142 ,p_attribute4 in out nocopy varchar2
143 ,p_attribute5 in out nocopy varchar2
144 ,p_attribute6 in out nocopy varchar2
145 ,p_attribute7 in out nocopy varchar2
146 ,p_attribute8 in out nocopy varchar2
147 ,p_attribute9 in out nocopy varchar2
148 ,p_attribute10 in out nocopy varchar2
149 ,p_attribute11 in out nocopy varchar2
150 ,p_attribute12 in out nocopy varchar2
151 ,p_attribute13 in out nocopy varchar2
152 ,p_attribute14 in out nocopy varchar2
153 ,p_attribute15 in out nocopy varchar2
154 ,p_attribute16 in out nocopy varchar2
155 ,p_attribute17 in out nocopy varchar2
156 ,p_attribute18 in out nocopy varchar2
157 ,p_attribute19 in out nocopy varchar2
158 ,p_attribute20 in out nocopy varchar2
159 ,p_attribute21 in out nocopy varchar2
160 ,p_attribute22 in out nocopy varchar2
161 ,p_attribute23 in out nocopy varchar2
162 ,p_attribute24 in out nocopy varchar2
163 ,p_attribute25 in out nocopy varchar2
164 ,p_attribute26 in out nocopy varchar2
165 ,p_attribute27 in out nocopy varchar2
166 ,p_attribute28 in out nocopy varchar2
167 ,p_attribute29 in out nocopy varchar2
168 ,p_attribute30 in out nocopy varchar2
169 ,p_attribute_category in out nocopy varchar2
170 ) is
171
172 type attribute_cluster is table of varchar2(150) index by binary_integer;
173 l_att_rec attribute_cluster;
174
175 l_rownum number;
176 l_mapping_exists boolean;
177 l_column_name varchar2(30);
178 l_column_number number;
179 l_information_type hxc_bld_blk_info_types.bld_blk_info_type%type;
180 l_set_attribute_category boolean := false;
181
182 e_no_attribute_mapping exception;
183
184 l_proc varchar2(30);
185
186 begin
187
188
189 -- initialize local attribute array
190 --if g_debug then
191 --l_proc := ' parse_Table';
192 --hr_utility.set_location('gaz: '|| l_proc, 10);
193 --end if;
194 for l_rownum in 1..30 loop
195 l_att_rec(l_rownum) := null;
196 end loop;
197
198 --if g_debug then
199 --hr_utility.set_location('gaz: '|| l_proc, 20);
200 --end if;
201 -- load attribute values from table into mapped position in local array
202 for l_rownum in 1..p_timecard.count loop
203
204 --if g_debug then
205 --hr_utility.set_location('gaz: '|| l_proc, 30);
206 --end if;
207 -- check that a mapping exists for the named attribute in the current
208 -- deposit process
209 /*l_mapping_exists := hxc_mapping_utilities.attribute_column
210 (p_timecard(l_rownum).attribute_name
211 ,'D'
212 ,p_process_id
213 ,l_column_name
214 ,l_information_type
215 );
216 */
217 --if g_debug then
218 --hr_utility.set_location('gaz: '|| l_proc, 40);
219 --end if;
220 -- if (l_mapping_exists) then
221 -- copy attribute value from timecard to local array
222 --if g_debug then
223 -- hr_utility.set_location('gaz: '|| l_proc, 50);
224 --hr_utility.set_location('l info type is '|| l_information_type, 40);
225 --hr_utility.set_location('p info type is '|| p_information_type, 40);
226 -- hr_utility.set_location('l column name is '|| l_column_name, 40);
227 --end if;
228 l_column_name := p_timecard(l_rownum).column_name;
229 l_information_type := p_timecard(l_rownum).info_mapping_type;
230
231 IF (l_column_name is not null) and (l_information_type is not null) THEN
232 if l_information_type = p_information_type then
233 if l_column_name = 'ATTRIBUTE_CATEGORY' then
234 p_attribute_category := p_timecard(l_rownum).attribute_value;
235 l_set_attribute_category := true;
236 else
237 l_column_number := to_number(ltrim(l_column_name, 'ATTRIBUTE'));
238 l_att_rec(l_column_number) := p_timecard(l_rownum).attribute_value;
239 end if;
240 end if;
241 else
242 -- if no mapping exists then we have issues
243 raise e_no_attribute_mapping;
244 end if;
245
246 end loop;
247
248 -- MV: Added the OR part
249 -- We need this because the previous checks do not work very well and
250 -- therefore there is a chance that the attribute_category does not get
251 -- set. When you use the TimeStore Deposit API, and you do not supply
252 -- the attribute_category, it will get created anyway by the deposit
253 -- wrapper. However the attribute_value for this record will be NULL
254 -- so in the previous checks, p_attribute_category is set to NULL and
255 -- l_set_attribute_category set to true. This means the next check
256 -- would not evaluate to true and p_attribute_category stays NULL.
257 -- With the added OR, we make sure p_attribute_category gets set.
258 if (NOT l_set_attribute_category) OR (p_attribute_category IS NULL) then
259 -- make sure the attribute category is set to the information type
260 -- if the attribute category is not part of the mapping
261 p_attribute_category := p_information_type;
262 end if;
263
264 -- copy local array to output parameters
265 --if g_debug then
266 --hr_utility.set_location('gaz: '|| l_proc, 70);
267 --end if;
268 p_attribute1 := l_att_rec(1);
269 p_attribute2 := l_att_rec(2);
270 p_attribute3 := l_att_rec(3);
271 p_attribute4 := l_att_rec(4);
272 p_attribute5 := l_att_rec(5);
273 p_attribute6 := l_att_rec(6);
274 p_attribute7 := l_att_rec(7);
275 p_attribute8 := l_att_rec(8);
276 p_attribute9 := l_att_rec(9);
277 p_attribute10 := l_att_rec(10);
278 --if g_debug then
279 --hr_utility.set_location('gaz: '|| l_proc, 80);
280 --end if;
281 p_attribute11 := l_att_rec(11);
282 p_attribute12 := l_att_rec(12);
283 p_attribute13 := l_att_rec(13);
284 p_attribute14 := l_att_rec(14);
285 p_attribute15 := l_att_rec(15);
286 p_attribute16 := l_att_rec(16);
287 p_attribute17 := l_att_rec(17);
288 p_attribute18 := l_att_rec(18);
289 p_attribute19 := l_att_rec(19);
290 p_attribute20 := l_att_rec(20);
291 --if g_debug then
292 -- hr_utility.set_location('gaz: '|| l_proc, 90);
293 --end if;
294 p_attribute21 := l_att_rec(21);
295 p_attribute22 := l_att_rec(22);
296 p_attribute23 := l_att_rec(23);
297 p_attribute24 := l_att_rec(24);
298 p_attribute25 := l_att_rec(25);
299 p_attribute26 := l_att_rec(26);
300 p_attribute27 := l_att_rec(27);
301 p_attribute28 := l_att_rec(28);
302 p_attribute29 := l_att_rec(29);
303 p_attribute30 := l_att_rec(30);
304 --if g_debug then
305 --hr_utility.set_location('gaz: '|| l_proc, 100);
306 --end if;
307
308 exception
309 when e_no_attribute_mapping then
310 fnd_message.set_name('HXC', 'HXC_NO_PROCESS_MAPPING');
311 fnd_message.raise_error;
312 when others then
313 raise;
314
315 end parse_table;
316
317
318 -- ---------------------------------------------------------------------------
319 -- |---------------------< create_attributes >-------------------------------|
320 -- ---------------------------------------------------------------------------
321
322 procedure create_attributes
323 (p_validate in boolean default false
324 ,p_timecard in timecard
325 ,p_process_id in number
326 ,p_time_building_block_id in number
327 ,p_tbb_ovn in number
328 ,p_time_attribute_id in out nocopy number
329 ,p_object_version_number in out nocopy number
330 ) is
331
332 --cursor c_attribute_usage_sequence is
333 -- select hxc_time_attribute_usages_s.nextval from dual; -- refer Bug#3062133
334
335 cursor c_bld_blk_info_type_id(p_bld_blk_info_type varchar2) is
336 select bld_blk_info_type_id
337 from hxc_bld_blk_info_types
338 where bld_blk_info_type = p_bld_blk_info_type;
339
340 cursor c_get_data_set(p_tbb_id number,p_tbb_ovn number)
341 is
342 select data_set_id from hxc_time_building_blocks
343 where time_building_block_id = p_tbb_id
344 and object_version_number = p_tbb_ovn;
345
346
347 l_proc varchar2(72);
348
349 l_object_version_number hxc_time_attributes.object_version_number%type;
350 l_time_attribute_id hxc_time_attributes.time_attribute_id%type;
351 l_time_attribute_usage_id number;
352 l_rownum number;
353 l_info_type_table info_type_table;
354 l_information_type varchar2(80);
355 l_bld_blk_info_type_id hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
356
357 l_attribute1 hxc_time_attributes.attribute1%TYPE;
358 l_attribute2 hxc_time_attributes.attribute2%TYPE;
359 l_attribute3 hxc_time_attributes.attribute3%TYPE;
360 l_attribute4 hxc_time_attributes.attribute4%TYPE;
361 l_attribute5 hxc_time_attributes.attribute5%TYPE;
362 l_attribute6 hxc_time_attributes.attribute6%TYPE;
363 l_attribute7 hxc_time_attributes.attribute7%TYPE;
364 l_attribute8 hxc_time_attributes.attribute8%TYPE;
365 l_attribute9 hxc_time_attributes.attribute9%TYPE;
366 l_attribute10 hxc_time_attributes.attribute10%TYPE;
367 l_attribute11 hxc_time_attributes.attribute11%TYPE;
368 l_attribute12 hxc_time_attributes.attribute12%TYPE;
369 l_attribute13 hxc_time_attributes.attribute13%TYPE;
370 l_attribute14 hxc_time_attributes.attribute14%TYPE;
371 l_attribute15 hxc_time_attributes.attribute15%TYPE;
372 l_attribute16 hxc_time_attributes.attribute16%TYPE;
373 l_attribute17 hxc_time_attributes.attribute17%TYPE;
374 l_attribute18 hxc_time_attributes.attribute18%TYPE;
375 l_attribute19 hxc_time_attributes.attribute19%TYPE;
376 l_attribute20 hxc_time_attributes.attribute20%TYPE;
377 l_attribute21 hxc_time_attributes.attribute21%TYPE;
378 l_attribute22 hxc_time_attributes.attribute22%TYPE;
379 l_attribute23 hxc_time_attributes.attribute23%TYPE;
380 l_attribute24 hxc_time_attributes.attribute24%TYPE;
381 l_attribute25 hxc_time_attributes.attribute25%TYPE;
382 l_attribute26 hxc_time_attributes.attribute26%TYPE;
383 l_attribute27 hxc_time_attributes.attribute27%TYPE;
384 l_attribute28 hxc_time_attributes.attribute28%TYPE;
385 l_attribute29 hxc_time_attributes.attribute29%TYPE;
386 l_attribute30 hxc_time_attributes.attribute30%TYPE;
387 l_attribute_category hxc_time_attributes.attribute_category%TYPE := null;
388
389 l_data_set_id hxc_time_attributes.data_set_id%type;
390 e_usage_data_missing exception;
391 begin
392
393 g_debug := hr_utility.debug_enabled;
394
395 if g_debug then
396 l_proc := g_package||'create_attributes';
397 hr_utility.set_location('Entering:'|| l_proc, 10);
398 end if;
399
400 -- break down the timecard into its component attributes
401
402 extract_info_types
403 (p_timecard => p_timecard
404 ,p_info_types => l_info_type_table
405 );
406
407 if g_debug then
408 hr_utility.set_location('gaz: '|| l_proc, 50);
409 end if;
410
411 for l_rownum in 1..l_info_type_table.count loop
412
413 l_information_type := l_info_type_table(l_rownum);
414
415 if g_debug then
416 hr_utility.set_location('gaz: '|| l_proc, 60);
417 end if;
418
419 parse_table
420 (p_process_id => p_process_id
421 ,p_timecard => p_timecard
422 ,p_information_type => l_information_type
423 ,p_attribute1 => l_attribute1
424 ,p_attribute2 => l_attribute2
425 ,p_attribute3 => l_attribute3
426 ,p_attribute4 => l_attribute4
427 ,p_attribute5 => l_attribute5
428 ,p_attribute6 => l_attribute6
429 ,p_attribute7 => l_attribute7
430 ,p_attribute8 => l_attribute8
431 ,p_attribute9 => l_attribute9
432 ,p_attribute10 => l_attribute10
433 ,p_attribute11 => l_attribute11
434 ,p_attribute12 => l_attribute12
435 ,p_attribute13 => l_attribute13
436 ,p_attribute14 => l_attribute14
437 ,p_attribute15 => l_attribute15
438 ,p_attribute16 => l_attribute16
439 ,p_attribute17 => l_attribute17
440 ,p_attribute18 => l_attribute18
441 ,p_attribute19 => l_attribute19
442 ,p_attribute20 => l_attribute20
443 ,p_attribute21 => l_attribute21
444 ,p_attribute22 => l_attribute22
445 ,p_attribute23 => l_attribute23
446 ,p_attribute24 => l_attribute24
447 ,p_attribute25 => l_attribute25
448 ,p_attribute26 => l_attribute26
449 ,p_attribute27 => l_attribute27
450 ,p_attribute28 => l_attribute28
451 ,p_attribute29 => l_attribute29
452 ,p_attribute30 => l_attribute30
453 ,p_attribute_category => l_attribute_category
454 );
455 --
456 -- AR: 115.4 The attribute category must be the same as the
457 -- information type, they must not be different.
458 --
459 -- l_attribute_category := l_information_type;
460
461 -- issue a savepoint
462 savepoint create_attributes;
463
464 -- get the information type id (bld_blk_info_type_id)
465 -- MS added attribute category
466 if g_debug then
467 hr_utility.set_location('gaz: '|| l_proc, 70);
468 end if;
469 open c_bld_blk_info_type_id(l_info_type_table(l_rownum));
470 fetch c_bld_blk_info_type_id into l_bld_blk_info_type_id;
471 close c_bld_blk_info_type_id;
472 if g_debug then
473 hr_utility.set_location('gaz: '|| l_proc, 80);
474 end if;
475
476 /* open c_get_data_set(p_time_building_block_id,p_tbb_ovn);
477 fetch c_get_data_set into l_data_set_id;
478 close c_get_data_set;*/
479
480 -- call the row handler
481 hxc_tat_ins.ins
482 (p_effective_date => null
483 ,p_attribute_category => l_attribute_category
484 ,p_attribute1 => l_attribute1
485 ,p_attribute2 => l_attribute2
486 ,p_attribute3 => l_attribute3
487 ,p_attribute4 => l_attribute4
488 ,p_attribute5 => l_attribute5
489 ,p_attribute6 => l_attribute6
490 ,p_attribute7 => l_attribute7
491 ,p_attribute8 => l_attribute8
492 ,p_attribute9 => l_attribute9
493 ,p_attribute10 => l_attribute10
494 ,p_attribute11 => l_attribute11
495 ,p_attribute12 => l_attribute12
496 ,p_attribute13 => l_attribute13
497 ,p_attribute14 => l_attribute14
498 ,p_attribute15 => l_attribute15
499 ,p_attribute16 => l_attribute16
500 ,p_attribute17 => l_attribute17
501 ,p_attribute18 => l_attribute18
502 ,p_attribute19 => l_attribute19
503 ,p_attribute20 => l_attribute20
504 ,p_attribute21 => l_attribute21
505 ,p_attribute22 => l_attribute22
506 ,p_attribute23 => l_attribute23
507 ,p_attribute24 => l_attribute24
508 ,p_attribute25 => l_attribute25
509 ,p_attribute26 => l_attribute26
510 ,p_attribute27 => l_attribute27
511 ,p_attribute28 => l_attribute28
512 ,p_attribute29 => l_attribute29
513 ,p_attribute30 => l_attribute30
514 ,p_bld_blk_info_type_id => l_bld_blk_info_type_id
515 ,p_data_set_id => NULL--l_data_set_id
516 ,p_time_attribute_id => l_time_attribute_id
517 ,p_object_version_number => l_object_version_number
518 );
519
520 -- insert row into hxc_time_attribute_usages to associate attributes
521 -- with a building block
522 if g_debug then
523 hr_utility.set_location('gaz: '|| l_proc, 90);
524 end if;
525
526 -- open c_attribute_usage_sequence; -- refer Bug#3062133
527 -- fetch c_attribute_usage_sequence into l_time_attribute_usage_id;
528 -- close c_attribute_usage_sequence;
529
530 if g_debug then
531 hr_utility.set_location('gaz: '|| l_proc, 100);
532 end if;
533
534 if ((p_time_building_block_id IS NULL) OR (l_time_attribute_id is NULL)) then
535 raise e_usage_data_missing;
536 end if;
537
538 insert into hxc_time_attribute_usages
539 (time_attribute_usage_id
540 ,time_attribute_id
541 ,time_building_block_id
542 ,time_building_block_ovn
543 ,data_set_id
544 ,created_by
545 ,creation_date
546 ,last_updated_by
547 ,last_update_date
548 ,last_update_login
549 ) values
550 (hxc_time_attribute_usages_s.nextval -- refer Bug#3062133
551 ,l_time_attribute_id
552 ,p_time_building_block_id
553 ,p_tbb_ovn
554 ,l_data_set_id
555 ,null
556 ,sysdate
557 ,null
558 ,sysdate
559 ,null
560 )
561 returning time_attribute_usage_id into l_time_attribute_usage_id; -- refer Bug#3062133
562
563 end loop;
564
565 if p_validate then
566 raise hr_api.validate_enabled;
567 end if;
568
569 -- set out parameters
570 p_object_version_number := l_object_version_number;
571 p_time_attribute_id := l_time_attribute_id;
572
573 exception
574 when hr_api.validate_enabled then
575 rollback to create_attributes;
576 when e_usage_data_missing then
577 rollback to create_attributes;
578 fnd_message.set_name('HXC', 'HXC_USAGE_DATA_MISSING');
579 fnd_message.raise_error;
580 when others then
581 raise;
582 end create_attributes;
583
584 procedure create_attribute
585 (p_validate in boolean default false
586 ,p_bld_blk_info_type_id in number
587 ,p_attribute_category in varchar2
588 ,p_attribute1 in varchar2
589 ,p_attribute2 in varchar2
590 ,p_attribute3 in varchar2
591 ,p_attribute4 in varchar2
592 ,p_attribute5 in varchar2
593 ,p_attribute6 in varchar2
594 ,p_attribute7 in varchar2
595 ,p_attribute8 in varchar2
596 ,p_attribute9 in varchar2
597 ,p_attribute10 in varchar2
598 ,p_attribute11 in varchar2
599 ,p_attribute12 in varchar2
600 ,p_attribute13 in varchar2
601 ,p_attribute14 in varchar2
602 ,p_attribute15 in varchar2
603 ,p_attribute16 in varchar2
604 ,p_attribute17 in varchar2
605 ,p_attribute18 in varchar2
606 ,p_attribute19 in varchar2
607 ,p_attribute20 in varchar2
608 ,p_attribute21 in varchar2
609 ,p_attribute22 in varchar2
610 ,p_attribute23 in varchar2
611 ,p_attribute24 in varchar2
612 ,p_attribute25 in varchar2
613 ,p_attribute26 in varchar2
614 ,p_attribute27 in varchar2
615 ,p_attribute28 in varchar2
616 ,p_attribute29 in varchar2
617 ,p_attribute30 in varchar2
618 ,p_time_building_block_id in number
619 ,p_tbb_ovn in number
620 ,p_time_attribute_id in out nocopy number
621 ,p_object_version_number in out nocopy number
622 ) is
623
624 --cursor c_attribute_usage_sequence is -- refer Bug#3062133
625 --select hxc_time_attribute_usages_s.nextval from dual;
626
627 cursor c_get_data_set(p_tbb_id number,p_tbb_ovn number)
628 is
629 select data_set_id from hxc_time_building_blocks
630 where time_building_block_id = p_tbb_id
631 and object_version_number = p_tbb_ovn;
632
633 l_time_attribute_id hxc_time_attributes.time_attribute_id%type;
634 l_object_version_number hxc_time_attributes.object_version_number%type;
635 l_time_attribute_usage_id hxc_time_attribute_usages.time_attribute_usage_id%type;
636
637 l_data_set_id hxc_time_attributes.data_set_id%type;
638 e_usage_data_missing exception;
639 Begin
640
641 savepoint create_attribute;
642
643 open c_get_data_set(p_time_building_block_id,p_tbb_ovn);
644 fetch c_get_data_set into l_data_set_id;
645 close c_get_data_set;
646
647 -- call the row handler
648 hxc_tat_ins.ins
649 (p_effective_date => null
650 ,p_attribute_category => p_attribute_category
651 ,p_attribute1 => p_attribute1
652 ,p_attribute2 => p_attribute2
653 ,p_attribute3 => p_attribute3
654 ,p_attribute4 => p_attribute4
655 ,p_attribute5 => p_attribute5
656 ,p_attribute6 => p_attribute6
657 ,p_attribute7 => p_attribute7
658 ,p_attribute8 => p_attribute8
659 ,p_attribute9 => p_attribute9
660 ,p_attribute10 => p_attribute10
661 ,p_attribute11 => p_attribute11
662 ,p_attribute12 => p_attribute12
663 ,p_attribute13 => p_attribute13
664 ,p_attribute14 => p_attribute14
665 ,p_attribute15 => p_attribute15
666 ,p_attribute16 => p_attribute16
667 ,p_attribute17 => p_attribute17
668 ,p_attribute18 => p_attribute18
669 ,p_attribute19 => p_attribute19
670 ,p_attribute20 => p_attribute20
671 ,p_attribute21 => p_attribute21
672 ,p_attribute22 => p_attribute22
673 ,p_attribute23 => p_attribute23
674 ,p_attribute24 => p_attribute24
675 ,p_attribute25 => p_attribute25
676 ,p_attribute26 => p_attribute26
677 ,p_attribute27 => p_attribute27
678 ,p_attribute28 => p_attribute28
679 ,p_attribute29 => p_attribute29
680 ,p_attribute30 => p_attribute30
681 ,p_bld_blk_info_type_id => p_bld_blk_info_type_id
682 ,p_data_set_id => NULL--l_data_set_id
683 ,p_time_attribute_id => l_time_attribute_id
684 ,p_object_version_number => l_object_version_number
685 );
686
687 -- insert row into hxc_time_attribute_usages to associate attributes
688 -- with a building block
689
690 --open c_attribute_usage_sequence; -- refer Bug#3062133
691 --fetch c_attribute_usage_sequence into l_time_attribute_usage_id;
692 --close c_attribute_usage_sequence;
693
694 if ((p_time_building_block_id IS NULL) OR (l_time_attribute_id is NULL)) then
695 raise e_usage_data_missing;
696 end if;
697
698 insert into hxc_time_attribute_usages
699 (time_attribute_usage_id
700 ,time_attribute_id
701 ,time_building_block_id
702 ,time_building_block_ovn
703 ,data_set_id
704 ,created_by
705 ,creation_date
706 ,last_updated_by
707 ,last_update_date
708 ,last_update_login
709 ) values
710 (hxc_time_attribute_usages_s.nextval -- refer Bug#3062133
711 ,l_time_attribute_id
712 ,p_time_building_block_id
713 ,p_tbb_ovn
714 ,l_data_set_id
715 ,null
716 ,sysdate
717 ,null
718 ,sysdate
719 ,null
720 )
721 returning time_attribute_usage_id into l_time_attribute_usage_id; -- refer Bug#3062133
722
723 if p_validate then
724 raise hr_api.validate_enabled;
725 end if;
726
727 -- set out parameters
728 p_object_version_number := l_object_version_number;
729 p_time_attribute_id := l_time_attribute_id;
730
731 exception
732 when hr_api.validate_enabled then
733 rollback to create_attribute;
734 when e_usage_data_missing then
735 rollback to create_attribute;
736 fnd_message.set_name('HXC', 'HXC_USAGE_DATA_MISSING');
737 fnd_message.raise_error;
738 when others then
739 raise;
740
741 End create_attribute;
742
743 -- ---------------------------------------------------------------------------
744 -- |---------------------< update_attributes >-------------------------------|
745 -- ---------------------------------------------------------------------------
746
747 procedure update_attributes
748 (p_validate in boolean default false
749 ,p_timecard in timecard
750 ,p_process_id in number
751 ,p_time_building_block_id in number
752 ,p_time_attribute_id in number
753 ,p_object_version_number in out nocopy number
754 ) is
755
756 l_proc varchar2(72);
757 l_object_version_number hxc_time_attributes.object_version_number%type;
758
759 begin
760
761 g_debug := hr_utility.debug_enabled;
762
763 if g_debug then
764 l_proc := g_package||'update_attributes';
765 hr_utility.set_location('Entering:'|| l_proc, 10);
766 end if;
767
768 -- issue a savepoint
769 savepoint update_building_block;
770
771 -- call the row handler
772 hxc_tat_upd.upd
773 (p_effective_date => sysdate
774 ,p_time_attribute_id => p_time_attribute_id
775 ,p_object_version_number => l_object_version_number
776 -- ,p_attribute_category => null
777 );
778
779 if p_validate then
780 raise hr_api.validate_enabled;
781 end if;
782
783 -- set out parameters
784 p_object_version_number := l_object_version_number;
785
786 if g_debug then
787 hr_utility.set_location(' Leaving:'|| l_proc, 20);
788 end if;
789
790 exception
791 when hr_api.validate_enabled then
792 rollback to update_attributes;
793 when others then
794 raise;
795
796 end update_attributes;
797
798
799 -- ---------------------------------------------------------------------------
800 -- |---------------------< delete_attributes >-------------------------------|
801 -- ---------------------------------------------------------------------------
802
803 procedure delete_attributes
804 (p_validate in boolean default false
805 ,p_time_attribute_id in number
806 ,p_object_version_number in number
807 ) is
808
809 begin
810
811 null;
812
813 end delete_attributes;
814
815
816 end hxc_time_attributes_api;