[Home] [Help]
PACKAGE BODY: APPS.HXC_ARRAY_TIME_DEPOSIT
Source
1 PACKAGE BODY hxc_array_time_deposit AS
2 /* $Header: hxctcardp.pkb 120.2 2005/07/14 17:40:53 arundell noship $ */
3
4 TYPE block_list is TABLE of varchar2(30) index by binary_integer;
5
6 g_resource_id NUMBER;
7 g_start_time VARCHAR2(100);
8 g_stop_time VARCHAR2(100);
9 g_debug_count NUMBER :=0;
10
11 e_array_time_deposit EXCEPTION;
12
13 /*
14 PROCEDURE debug
15 (p_procedure IN VARCHAR2
16 ,p_reference IN NUMBER
17 ,p_text IN VARCHAR2
18 ) IS
19
20 l_loc VARCHAR2(70) := 'HXC_SELF_SERVICE_TIME_DEPOSIT.'|| p_procedure;
21
22 BEGIN
23
24 g_debug_count := g_debug_count + 1;
25
26 if (g_resource_id = 12185) then
27
28 INSERT INTO hxc_timecard_debug
29 (LINE
30 ,LOCATION
31 ,REFERENCE
32 ,TEXT
33 )
34 VALUES
35 (g_debug_count
36 ,l_loc
37 ,p_reference
38 ,p_text
39 );
40
41 COMMIT;
42
43 end if;
44
45 END debug;
46 */
47 function globalBlockDeposit
48 (p_blocks in HXC_BLOCK_TABLE_TYPE
49 ,p_deleted in BOOLEAN default true) return block_list is
50
51 l_block_count NUMBER;
52 l_deposit_block BOOLEAN;
53 l_blocks_deposited block_list;
54 l_deposit_index number := 1;
55
56 begin
57
58 l_block_count := p_blocks.first;
59
60 LOOP
61 EXIT WHEN NOT p_blocks.exists(l_block_count);
62 -- Call the deposit block procedure
63 l_deposit_block := false;
64
65 if(p_blocks(l_block_count) is not null) then
66
67 if(p_deleted) then
68 l_deposit_block := true;
69 else
70
71 if(fnd_date.canonical_to_date(p_blocks(l_block_count).date_to) = hr_general.end_of_time) then
72
73 l_deposit_block := true;
74
75 end if;
76
77 end if;
78
79 end if;
80
81 if(l_deposit_block) then
82
83 /*
84 None of this is required anymore.
85
86 if(p_blocks(l_block_count).scope = 'TIMECARD') then
87 if(fnd_date.canonical_to_date(p_blocks(l_block_count).date_to) = hr_general.end_of_time) then
88
89 --
90 -- check to see if there is a timecard id already for this
91 -- period
92 --
93
94 if (hxc_deposit_checks.chk_timecard_deposit
95 (p_blocks
96 ,l_block_count
97 )
98 ) then
99 --
100 -- There is an existing timecard, stop processing.
101 --
102 FND_MESSAGE.SET_NAME('HXC','HXC_366333_TIMECARD_EXISTS');
103 raise e_array_time_deposit;
104
105 end if;
106
107 end if;
108 end if;
109 */
110
111 if(p_blocks(l_block_count).scope = 'TIMECARD') then
112
113 g_resource_id := p_blocks(l_block_count).resource_id;
114 g_start_time := p_blocks(l_block_count).start_time;
115 g_stop_time := p_blocks(l_block_count).stop_time;
116
117 end if;
118
119 l_blocks_deposited(p_blocks(l_block_count).time_building_block_id) := p_blocks(l_block_count).date_to;
120
121 HXC_SELF_SERVICE_TIME_DEPOSIT.CALL_BLOCK_DEPOSIT
122 (P_TIME_BUILDING_BLOCK_ID =>p_blocks(l_block_count).TIME_BUILDING_BLOCK_ID
123 ,P_TYPE =>p_blocks(l_block_count).TYPE
124 ,P_MEASURE =>p_blocks(l_block_count).MEASURE
125 ,P_UNIT_OF_MEASURE =>p_blocks(l_block_count).UNIT_OF_MEASURE
126 ,P_START_TIME =>p_blocks(l_block_count).START_TIME
127 ,P_STOP_TIME =>p_blocks(l_block_count).STOP_TIME
128 ,P_PARENT_BUILDING_BLOCK_ID =>p_blocks(l_block_count).PARENT_BUILDING_BLOCK_ID
129 ,P_SCOPE =>p_blocks(l_block_count).SCOPE
130 ,P_OBJECT_VERSION_NUMBER =>p_blocks(l_block_count).OBJECT_VERSION_NUMBER
131 ,P_APPROVAL_STATUS => p_blocks(l_block_count).APPROVAL_STATUS
132 ,P_RESOURCE_ID => p_blocks(l_block_count).RESOURCE_ID
133 ,P_RESOURCE_TYPE => p_blocks(l_block_count).RESOURCE_TYPE
134 ,P_APPROVAL_STYLE_ID => p_blocks(l_block_count).APPROVAL_STYLE_ID
135 ,P_DATE_FROM => p_blocks(l_block_count).DATE_FROM
136 ,P_DATE_TO => p_blocks(l_block_count).DATE_TO
137 ,P_COMMENT_TEXT => p_blocks(l_block_count).COMMENT_TEXT
138 ,P_PARENT_BUILDING_BLOCK_OVN => p_blocks(l_block_count).PARENT_BUILDING_BLOCK_OVN
139 ,P_NEW => p_blocks(l_block_count).NEW
140 ,P_PARENT_IS_NEW => p_blocks(l_block_count).PARENT_IS_NEW
141 ,P_CHANGED => p_blocks(l_block_count).CHANGED
142 );
143
144 end if;
145
146 l_block_count := p_blocks.next(l_block_count);
147
148 END LOOP;
149
150 return l_blocks_deposited;
151
152 end globalBlockDeposit;
153
154
155 function checkBlockDeposited
156 (p_building_block_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
157 ,p_blocks_deposited in block_list)
158 return boolean is
159
160 i number;
161 l_deposited BOOLEAN := false;
162 l_found_block BOOLEAN := false;
163
164 begin
165
166 i := p_blocks_deposited.first;
167
168 LOOP
169
170 EXIT WHEN ((NOT p_blocks_deposited.exists(i)) OR (l_found_block));
171
172 if(i = p_building_block_id) then
173
174 if(instr(p_blocks_deposited(i),'4712')>0 ) then
175
176 l_found_block := true;
177 l_deposited := true;
178
179 else
180
181 l_found_block := true;
182 l_deposited := false;
183
184 end if;
185
186 end if;
187
188 i := p_blocks_deposited.next(i);
189
190 END LOOP;
191
192 return l_deposited;
193
194 end checkBlockDeposited;
195
196 function checkBlockDeposited
197 (p_building_block_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE)
198 return boolean is
199
200 l_blocks HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO;
201 i number;
202 l_deposited BOOLEAN := false;
203 l_found_block BOOLEAN := false;
204
205 begin
206
207 l_blocks := hxc_self_service_time_deposit.get_building_blocks;
208
209 i := l_blocks.first;
210
211 LOOP
212
213 EXIT WHEN ((NOT l_blocks.exists(i)) OR (l_found_block));
214
215 if(l_blocks(i).time_building_block_id = p_building_block_id) then
216
217 if(l_blocks(i).date_to = hr_general.end_of_time) then
218
219 l_found_block := true;
220 l_deposited := true;
221
222 else
223
224 l_found_block := true;
225 l_deposited := false;
226
227 end if;
228
229 end if;
230
231 i := l_blocks.next(i);
232
233 END LOOP;
234
235 return l_deposited;
236
237 end checkBlockDeposited;
238
239 function checkAttributeValues
240 (p_attributes in HXC_ATTRIBUTE_TABLE_TYPE
241 ,p_attribute_number in NUMBER
242 ) return BOOLEAN is
243
244 l_deposit BOOLEAN := false;
245
246 BEGIN
247
248 if ((p_attributes(p_attribute_number).attribute1 is not null)
249 OR
250 (p_attributes(p_attribute_number).attribute1 <> '')) then
251
252 l_deposit := true;
253
254 end if;
255
256 return l_deposit;
257
258 END checkAttributeValues;
259
260 function checkAliasAttributeValue
261 (p_attributes in HXC_ATTRIBUTE_TABLE_TYPE
262 ,p_attribute_number in NUMBER
263 ) return BOOLEAN is
264
265 l_deposit BOOLEAN := false;
266
267 BEGIN
268 /*
269 if(instr(p_attributes(p_attribute_number).attribute_category,'OTL_ALIAS') = 0) then
270
271 l_deposit := true;
272
273 else
274
275 l_deposit := checkAttributeValues(p_attributes,p_attribute_number);
276
277 end if;
278
279 return l_deposit;
280 */
281 return true;
282
283 END checkAliasAttributeValue;
284
285 procedure globalAttributeDeposit
286 (p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE
287 ,p_deleted in BOOLEAN default TRUE
288 ,p_block_list in block_list) is
289
290 l_attribute_count NUMBER;
291 l_deposit_attribute BOOLEAN := FALSE;
292 l_deposit_alias_attribute BOOLEAN := FALSE;
293
294 begin
295
296 l_attribute_count := p_attributes.first;
297
298 LOOP
299 EXIT WHEN NOT p_attributes.exists(l_attribute_count);
300 l_deposit_attribute := false;
301
302
303 if(p_attributes(l_attribute_count).time_attribute_id is not null) then
304
305 if(p_deleted) then
306 l_deposit_attribute := true;
307 else
308 l_deposit_attribute := checkBlockDeposited(p_attributes(l_attribute_count).BUILDING_BLOCK_ID, p_block_list);
309 end if;
310
311 l_deposit_alias_attribute := checkAliasAttributeValue(p_attributes,l_attribute_count);
312
313 end if;
314
315 if((l_deposit_attribute) AND (l_deposit_alias_attribute))then
316
317 HXC_SELF_SERVICE_TIME_DEPOSIT.CALL_ATTRIBUTE_DEPOSIT
318 (P_TIME_ATTRIBUTE_ID => p_attributes(l_attribute_count).TIME_ATTRIBUTE_ID
319 ,P_BUILDING_BLOCK_ID => p_attributes(l_attribute_count).BUILDING_BLOCK_ID
320 ,P_ATTRIBUTE_CATEGORY=> p_attributes(l_attribute_count).ATTRIBUTE_CATEGORY
321 ,P_ATTRIBUTE1 => p_attributes(l_attribute_count).ATTRIBUTE1
322 ,P_ATTRIBUTE2 => p_attributes(l_attribute_count).ATTRIBUTE2
323 ,P_ATTRIBUTE3 => p_attributes(l_attribute_count).ATTRIBUTE3
324 ,P_ATTRIBUTE4 => p_attributes(l_attribute_count).ATTRIBUTE4
325 ,P_ATTRIBUTE5 => p_attributes(l_attribute_count).ATTRIBUTE5
326 ,P_ATTRIBUTE6 => p_attributes(l_attribute_count).ATTRIBUTE6
327 ,P_ATTRIBUTE7 => p_attributes(l_attribute_count).ATTRIBUTE7
328 ,P_ATTRIBUTE8 => p_attributes(l_attribute_count).ATTRIBUTE8
329 ,P_ATTRIBUTE9 => p_attributes(l_attribute_count).ATTRIBUTE9
330 ,P_ATTRIBUTE10 => p_attributes(l_attribute_count).ATTRIBUTE10
331 ,P_ATTRIBUTE11 => p_attributes(l_attribute_count).ATTRIBUTE11
332 ,P_ATTRIBUTE12 => p_attributes(l_attribute_count).ATTRIBUTE12
333 ,P_ATTRIBUTE13 => p_attributes(l_attribute_count).ATTRIBUTE13
334 ,P_ATTRIBUTE14 => p_attributes(l_attribute_count).ATTRIBUTE14
335 ,P_ATTRIBUTE15 => p_attributes(l_attribute_count).ATTRIBUTE15
336 ,P_ATTRIBUTE16 => p_attributes(l_attribute_count).ATTRIBUTE16
337 ,P_ATTRIBUTE17 => p_attributes(l_attribute_count).ATTRIBUTE17
338 ,P_ATTRIBUTE18 => p_attributes(l_attribute_count).ATTRIBUTE18
339 ,P_ATTRIBUTE19 => p_attributes(l_attribute_count).ATTRIBUTE19
340 ,P_ATTRIBUTE20 => p_attributes(l_attribute_count).ATTRIBUTE20
341 ,P_ATTRIBUTE21 => p_attributes(l_attribute_count).ATTRIBUTE21
342 ,P_ATTRIBUTE22 => p_attributes(l_attribute_count).ATTRIBUTE22
343 ,P_ATTRIBUTE23 => p_attributes(l_attribute_count).ATTRIBUTE23
344 ,P_ATTRIBUTE24 => p_attributes(l_attribute_count).ATTRIBUTE24
345 ,P_ATTRIBUTE25 => p_attributes(l_attribute_count).ATTRIBUTE25
346 ,P_ATTRIBUTE26 => p_attributes(l_attribute_count).ATTRIBUTE26
347 ,P_ATTRIBUTE27 => p_attributes(l_attribute_count).ATTRIBUTE27
348 ,P_ATTRIBUTE28 => p_attributes(l_attribute_count).ATTRIBUTE28
349 ,P_ATTRIBUTE29 => p_attributes(l_attribute_count).ATTRIBUTE29
350 ,P_ATTRIBUTE30 => p_attributes(l_attribute_count).ATTRIBUTE30
351 ,P_BLD_BLK_INFO_TYPE_ID => p_attributes(l_attribute_count).BLD_BLK_INFO_TYPE_ID
352 ,P_OBJECT_VERSION_NUMBER => p_attributes(l_attribute_count).OBJECT_VERSION_NUMBER
353 ,P_NEW => p_attributes(l_attribute_count).NEW
354 ,P_CHANGED => p_attributes(l_attribute_count).CHANGED
355 ,P_BLD_BLK_INFO_TYPE => p_attributes(l_attribute_count).BLD_BLK_INFO_TYPE
356 );
357
358 end if;
359
360 l_attribute_count := p_attributes.next(l_attribute_count);
361
362 END LOOP;
363 /*
364 exception
365 when others then
366 FND_MESSAGE.SET_NAME('HXC','HXC_GLOBAL_ATTRIBUTE_DEPOSIT');
367 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
368 FND_MESSAGE.RAISE_ERROR;
369 */
370 end globalAttributeDeposit;
371
372 procedure add_layout_attribute
373 (p_old_attributes in HXC_SELF_SERVICE_TIME_DEPOSIT.BUILDING_BLOCK_ATTRIBUTE_INFO
374 ,p_exploded_attributes in out nocopy HXC_SELF_SERVICE_TIME_DEPOSIT.BUILDING_BLOCK_ATTRIBUTE_INFO
375 ) is
376
377 l_found_attribute boolean := false;
378 l_next_attribute_index NUMBER;
379 l_attribute_index NUMBER;
380
381 begin
382
383 l_next_attribute_index := p_exploded_attributes.last;
384 l_next_attribute_index := l_next_attribute_index +1;
385
386 l_attribute_index := p_old_attributes.first;
387
388 LOOP
389
390 EXIT WHEN ( (not p_old_attributes.exists(l_attribute_index)) OR (l_found_attribute));
391
392 if(p_old_attributes(l_attribute_index).attribute_category = 'LAYOUT') then
393
394 l_found_attribute := true;
395
396 p_exploded_attributes(l_next_attribute_index).TIME_ATTRIBUTE_ID := p_old_attributes(l_attribute_index).TIME_ATTRIBUTE_ID ;
397 p_exploded_attributes(l_next_attribute_index).BUILDING_BLOCK_ID := p_old_attributes(l_attribute_index).BUILDING_BLOCK_ID ;
398 p_exploded_attributes(l_next_attribute_index).BLD_BLK_INFO_TYPE := p_old_attributes(l_attribute_index).BLD_BLK_INFO_TYPE ;
399 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE_CATEGORY := p_old_attributes(l_attribute_index).ATTRIBUTE_CATEGORY ;
400 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE1 := p_old_attributes(l_attribute_index).ATTRIBUTE1 ;
401 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE2 := p_old_attributes(l_attribute_index).ATTRIBUTE2 ;
402 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE3 := p_old_attributes(l_attribute_index).ATTRIBUTE3 ;
403 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE4 := p_old_attributes(l_attribute_index).ATTRIBUTE4 ;
404 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE5 := p_old_attributes(l_attribute_index).ATTRIBUTE5 ;
405 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE6 := p_old_attributes(l_attribute_index).ATTRIBUTE6 ;
406 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE7 := p_old_attributes(l_attribute_index).ATTRIBUTE7 ;
407 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE8 := p_old_attributes(l_attribute_index).ATTRIBUTE8 ;
408 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE9 := p_old_attributes(l_attribute_index).ATTRIBUTE9 ;
409 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE10 := p_old_attributes(l_attribute_index).ATTRIBUTE10 ;
410 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE11 := p_old_attributes(l_attribute_index).ATTRIBUTE11 ;
411 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE12 := p_old_attributes(l_attribute_index).ATTRIBUTE12 ;
412 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE13 := p_old_attributes(l_attribute_index).ATTRIBUTE13 ;
413 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE14 := p_old_attributes(l_attribute_index).ATTRIBUTE14 ;
414 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE15 := p_old_attributes(l_attribute_index).ATTRIBUTE15 ;
415 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE16 := p_old_attributes(l_attribute_index).ATTRIBUTE16 ;
416 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE17 := p_old_attributes(l_attribute_index).ATTRIBUTE17 ;
417 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE18 := p_old_attributes(l_attribute_index).ATTRIBUTE18 ;
418 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE19 := p_old_attributes(l_attribute_index).ATTRIBUTE19 ;
419 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE20 := p_old_attributes(l_attribute_index).ATTRIBUTE20 ;
420 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE21 := p_old_attributes(l_attribute_index).ATTRIBUTE21 ;
421 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE22 := p_old_attributes(l_attribute_index).ATTRIBUTE22 ;
422 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE23 := p_old_attributes(l_attribute_index).ATTRIBUTE23 ;
423 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE24 := p_old_attributes(l_attribute_index).ATTRIBUTE24 ;
424 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE25 := p_old_attributes(l_attribute_index).ATTRIBUTE25 ;
425 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE26 := p_old_attributes(l_attribute_index).ATTRIBUTE26 ;
426 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE27 := p_old_attributes(l_attribute_index).ATTRIBUTE27 ;
427 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE28 := p_old_attributes(l_attribute_index).ATTRIBUTE28 ;
428 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE29 := p_old_attributes(l_attribute_index).ATTRIBUTE29 ;
429 p_exploded_attributes(l_next_attribute_index).ATTRIBUTE30 := p_old_attributes(l_attribute_index).ATTRIBUTE30 ;
430 p_exploded_attributes(l_next_attribute_index).BLD_BLK_INFO_TYPE_ID := p_old_attributes(l_attribute_index).BLD_BLK_INFO_TYPE_ID ;
431 p_exploded_attributes(l_next_attribute_index).OBJECT_VERSION_NUMBER := p_old_attributes(l_attribute_index).OBJECT_VERSION_NUMBER ;
432 p_exploded_attributes(l_next_attribute_index).NEW := p_old_attributes(l_attribute_index).NEW ;
433 p_exploded_attributes(l_next_attribute_index).CHANGED := p_old_attributes(l_attribute_index).CHANGED ;
434
435 end if;
436
437 l_attribute_index := p_old_attributes.next(l_attribute_index);
438
439 END LOOP;
440
441 end add_layout_attribute;
442
443 function getValidExplosionBlocks
444 (p_explosion_blocks in HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO
445 ) return HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO is
446
447 l_valid_blocks HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO;
448 l_block_counter binary_integer;
449 l_block_index binary_integer;
450
451 begin
452
453 l_block_counter := p_explosion_blocks.first;
454 l_block_index := 0;
455 Loop
456 Exit When not p_explosion_blocks.exists(l_block_counter);
457 if((p_explosion_blocks(l_block_counter).TYPE = 'MEASURE')
458 OR
459 (p_explosion_blocks(l_block_counter).TYPE = 'RANGE')) then
460
461 l_block_index := l_block_index + 1;
462
463 l_valid_blocks(l_block_index).TIME_BUILDING_BLOCK_ID := p_explosion_blocks(l_block_counter).TIME_BUILDING_BLOCK_ID;
464 l_valid_blocks(l_block_index).TYPE := p_explosion_blocks(l_block_counter).TYPE;
465 l_valid_blocks(l_block_index).MEASURE := p_explosion_blocks(l_block_counter).MEASURE;
466 l_valid_blocks(l_block_index).UNIT_OF_MEASURE := p_explosion_blocks(l_block_counter).UNIT_OF_MEASURE;
467 l_valid_blocks(l_block_index).START_TIME := p_explosion_blocks(l_block_counter).START_TIME;
468 l_valid_blocks(l_block_index).STOP_TIME := p_explosion_blocks(l_block_counter).STOP_TIME;
469 l_valid_blocks(l_block_index).PARENT_BUILDING_BLOCK_ID := p_explosion_blocks(l_block_counter).PARENT_BUILDING_BLOCK_ID;
470 l_valid_blocks(l_block_index).PARENT_IS_NEW := p_explosion_blocks(l_block_counter).PARENT_IS_NEW;
471 l_valid_blocks(l_block_index).SCOPE := p_explosion_blocks(l_block_counter).SCOPE;
472 l_valid_blocks(l_block_index).OBJECT_VERSION_NUMBER := p_explosion_blocks(l_block_counter).OBJECT_VERSION_NUMBER;
473 l_valid_blocks(l_block_index).APPROVAL_STATUS := p_explosion_blocks(l_block_counter).APPROVAL_STATUS;
474 l_valid_blocks(l_block_index).RESOURCE_ID := p_explosion_blocks(l_block_counter).RESOURCE_ID;
475 l_valid_blocks(l_block_index).RESOURCE_TYPE := p_explosion_blocks(l_block_counter).RESOURCE_TYPE;
476 l_valid_blocks(l_block_index).APPROVAL_STYLE_ID := p_explosion_blocks(l_block_counter).APPROVAL_STYLE_ID;
477 l_valid_blocks(l_block_index).DATE_FROM := p_explosion_blocks(l_block_counter).DATE_FROM;
478 l_valid_blocks(l_block_index).DATE_TO := p_explosion_blocks(l_block_counter).DATE_TO;
479 l_valid_blocks(l_block_index).COMMENT_TEXT := p_explosion_blocks(l_block_counter).COMMENT_TEXT;
480 l_valid_blocks(l_block_index).PARENT_BUILDING_BLOCK_OVN := p_explosion_blocks(l_block_counter).PARENT_BUILDING_BLOCK_OVN;
481 l_valid_blocks(l_block_index).NEW := p_explosion_blocks(l_block_counter).NEW;
482 l_valid_blocks(l_block_index).CHANGED := p_explosion_blocks(l_block_counter).CHANGED;
483
484 end if;
485
486 l_block_counter := p_explosion_blocks.next(l_block_counter);
487
488 end loop;
489
490 return l_valid_blocks;
491
492 end getValidExplosionBlocks;
493
494 function get_translation_blocks
495 (p_blocks in HXC_BLOCK_TABLE_TYPE
496 ,p_details_blocks in HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO
497 ) return HXC_BLOCK_TABLE_TYPE IS -- HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO is
498
499 l_block number;
500 l_index number := 0;
501 l_trans_blocks HXC_BLOCK_TABLE_TYPE := HXC_BLOCK_TABLE_TYPE(); --hxc_self_service_time_deposit.timecard_info;
502
503 begin
504
505 l_block := p_blocks.first;
506
507 Loop
508 Exit when not p_blocks.exists(l_block);
509
510 if((p_blocks(l_block).scope = 'TIMECARD') OR (p_blocks(l_block).scope = 'DAY')) then
511
512 l_trans_blocks.extend;
513 l_index := l_trans_blocks.last;
514 l_trans_blocks(l_index) :=
515 HXC_BLOCK_TYPE(
516 p_blocks(l_block).TIME_BUILDING_BLOCK_ID
517 ,p_blocks(l_block).TYPE
518 ,p_blocks(l_block).MEASURE
519 ,p_blocks(l_block).UNIT_OF_MEASURE
520 ,p_blocks(l_block).START_TIME--fnd_date.date_to_canonical(p_blocks(l_block).START_TIME)
521 ,p_blocks(l_block).STOP_TIME--fnd_date.date_to_canonical(p_blocks(l_block).STOP_TIME)
522 ,p_blocks(l_block).PARENT_BUILDING_BLOCK_ID
523 ,p_blocks(l_block).PARENT_IS_NEW
524 ,p_blocks(l_block).SCOPE
525 ,p_blocks(l_block).OBJECT_VERSION_NUMBER
526 ,p_blocks(l_block).APPROVAL_STATUS
527 ,p_blocks(l_block).RESOURCE_ID
528 ,p_blocks(l_block).RESOURCE_TYPE
529 ,p_blocks(l_block).APPROVAL_STYLE_ID
530 ,p_blocks(l_block).DATE_FROM--fnd_date.date_to_canonical(p_blocks(l_block).DATE_FROM)
531 ,p_blocks(l_block).DATE_TO--fnd_date.date_to_canonical(p_blocks(l_block).DATE_TO)
532 ,p_blocks(l_block).COMMENT_TEXT
533 ,p_blocks(l_block).PARENT_BUILDING_BLOCK_OVN
534 ,p_blocks(l_block).NEW
535 ,p_blocks(l_block).CHANGED
536 ,null
537 ,null
538 ,p_blocks(l_block).TRANSLATION_DISPLAY_KEY
539 );
540
541 end if;
542
543 l_block := p_blocks.next(l_block);
544 End Loop;
545 --
546 -- Now append the exploded details
547 --
548 l_block := p_details_blocks.first;
549
550 Loop
551 Exit when not p_details_blocks.exists(l_block);
552
553 l_trans_blocks.extend;
554 l_index := l_trans_blocks.last;
555 l_trans_blocks(l_index) :=
556 HXC_BLOCK_TYPE(
557 p_details_blocks(l_block).TIME_BUILDING_BLOCK_ID
558 ,p_details_blocks(l_block).TYPE
559 ,p_details_blocks(l_block).MEASURE
560 ,p_details_blocks(l_block).UNIT_OF_MEASURE
561 ,fnd_date.date_to_canonical(p_details_blocks(l_block).START_TIME)
562 ,fnd_date.date_to_canonical(p_details_blocks(l_block).STOP_TIME)
563 ,p_details_blocks(l_block).PARENT_BUILDING_BLOCK_ID
564 ,p_details_blocks(l_block).PARENT_IS_NEW
565 ,p_details_blocks(l_block).SCOPE
566 ,p_details_blocks(l_block).OBJECT_VERSION_NUMBER
567 ,p_details_blocks(l_block).APPROVAL_STATUS
568 ,p_details_blocks(l_block).RESOURCE_ID
569 ,p_details_blocks(l_block).RESOURCE_TYPE
570 ,p_details_blocks(l_block).APPROVAL_STYLE_ID
571 ,fnd_date.date_to_canonical(p_details_blocks(l_block).DATE_FROM)
572 ,fnd_date.date_to_canonical(p_details_blocks(l_block).DATE_TO)
573 ,p_details_blocks(l_block).COMMENT_TEXT
574 ,p_details_blocks(l_block).PARENT_BUILDING_BLOCK_OVN
575 ,p_details_blocks(l_block).NEW
576 ,p_details_blocks(l_block).CHANGED
577 ,null
578 ,null
579 ,null -- can't set the display key at the moment.
580 );
581
582 l_block:=p_details_blocks.next(l_block);
583 End Loop;
584
585 return l_trans_blocks;
586
587 End get_translation_blocks;
588
589 procedure getExplodedHours
590 (p_blocks in out nocopy HXC_BLOCK_TABLE_TYPE
591 ,p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
592 ,p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
593 ) is
594
595 l_blocks HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO;
596 l_valid_blocks HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO;
597 l_blocks_for_translation HXC_BLOCK_TABLE_TYPE;
598 l_attributes HXC_SELF_SERVICE_TIME_DEPOSIT.BUILDING_BLOCK_ATTRIBUTE_INFO;
599 l_old_attributes HXC_SELF_SERVICE_TIME_DEPOSIT.BUILDING_BLOCK_ATTRIBUTE_INFO;
600 l_app_attributes HXC_SELF_SERVICE_TIME_DEPOSIT.app_attributes_info;
601 l_deposit_process_id HXC_DEPOSIT_PROCESSES.DEPOSIT_PROCESS_ID%TYPE;
602 l_messages HXC_SELF_SERVICE_TIME_DEPOSIT.MESSAGE_TABLE;
603 l_messages_table HXC_MESSAGE_TABLE_TYPE;
604 l_att_count NUMBER;
605 l_blocks_deposited block_list;
606 l_exploded_details HXC_BLOCK_TABLE_TYPE := HXC_BLOCK_TABLE_TYPE();
607 l_exploded_attributes HXC_ATTRIBUTE_TABLE_TYPE:= HXC_ATTRIBUTE_TABLE_TYPE();
608
609 cursor c_dep_prc_id is
610 select deposit_process_id
611 from hxc_deposit_processes
612 where name = 'OTL Deposit Process';
613
614 begin
615
616 --
617 -- Bug 3411488: Use new methods for doing the translation.
618 --
619
620 hxc_timecard_attribute_utils.set_bld_blk_info_type_id(p_attributes);
621 hxc_alias_translator.do_deposit_translation(p_attributes,p_messages);
622
623 --
624 -- Call to initialize the global variables!
625 --
626 p_messages := hxc_message_table_type();
627 hxc_self_service_time_deposit.initialize_globals;
628
629 -- set up the blocks and attributes
630 -- we don't just pass these because the hours explosion api
631 -- picks up the global variables!
632
633 --Bug 2770487 Sonarasi 04-Apr-2003
634 --Description : The p_deleted parameter of the function globalBlockDeposit
635 --and procedure globalAttributeDeposit determines whether deleted blocks can be
636 --passed to global tables or not. Deleted blocks will be considered for explosion
637 --if p_deleted parameter is true.
638
639 --Modifying the following call so that p_deleted is true.
640
641 --l_blocks_deposited := globalBlockDeposit(p_blocks, false);
642 --globalAttributeDeposit(p_attributes, false, l_blocks_deposited);
643
644 --Modified call
645 l_blocks_deposited := globalBlockDeposit(p_blocks, true);
646 globalAttributeDeposit(p_attributes, true, l_blocks_deposited);
647
648 --Bug 2770487 Sonarasi Over
649
650 l_old_attributes := HXC_SELF_SERVICE_TIME_DEPOSIT.get_block_attributes;
651
652 --
653 -- Obtain the application attributes expected by
654 -- the hours explosion API
655 --
656
657 open c_dep_prc_id;
658 fetch c_dep_prc_id into l_deposit_process_id;
659 close c_dep_prc_id;
660 --
661 -- Bug 3411488: Use new methods for creating the application
662 -- attribtes.
663 --
664 l_app_attributes := hxc_app_attribute_utils.create_app_attributes
665 (p_attributes => p_attributes
666 ,p_retrieval_process_id => null
667 ,p_deposit_process_id => l_deposit_process_id
668 );
669
670 hxc_timecard_message_helper.initializeErrors;
671 --
672 -- Call the hours explosion API
673 --
674
675 hxt_hxc_retrieval_process.otlr_review_details
676 (p_time_building_blocks => hxc_self_service_time_deposit.get_building_blocks
677 ,p_time_attributes => l_app_attributes
678 ,p_messages => l_messages
679 ,p_detail_build_blocks => l_blocks
680 ,p_detail_attributes => l_attributes
681 );
682
683 if(l_messages.count >0) then
684
685 hxc_timecard_message_utils.append_old_messages
686 (p_messages => p_messages
687 ,p_old_messages => l_messages
688 ,p_retrieval_process_id => null
689 );
690
691 hxc_timecard_message_helper.processerrors
692 (p_messages => p_messages);
693
694 p_messages := hxc_timecard_message_helper.prepareMessages;
695
696 elsif(l_blocks.count > 0) then
697 l_valid_blocks := getValidExplosionBlocks(l_blocks);
698 l_blocks_for_translation := get_translation_blocks(p_blocks,l_valid_blocks);
699 --
700 -- Translate back into alias understandable attributes, but first
701 -- add the layout attribute back in so that the translation code
702 -- knows which fields are translated on the layout!
703 --
704
705 add_layout_attribute(l_old_attributes,l_attributes);
706 l_exploded_attributes := hxc_deposit_wrapper_utilities.attributes_to_array
707 (p_attributes => l_attributes);
708
709 hxc_alias_translator.do_retrieval_translation
710 (p_attributes => l_exploded_attributes
711 ,p_blocks => l_blocks_for_translation
712 ,p_start_time => FND_DATE.CANONICAL_TO_DATE(g_start_time)
713 ,p_stop_time => FND_DATE.CANONICAL_TO_DATE(g_stop_time)
714 ,p_resource_id => g_resource_id
715 ,p_messages => l_messages_table
716 );
717
718 -- Now we have to populate the block and attribute
719 -- tables to pass back to the middle tier
720 --
721 l_exploded_details := hxc_deposit_wrapper_utilities.blocks_to_array
722 (p_blocks => l_valid_blocks);
723
724 elsif(l_blocks.count = 0) then
725
726 hxc_timecard_message_helper.adderrortocollection
727 (p_messages => p_messages,
728 p_message_name => 'HXC_NO_EXPLODED_BLOCKS',
729 p_message_level => 'WARNING',
730 p_message_field => null,
731 p_message_tokens => null,
732 p_application_short_name => 'HXC',
733 p_time_building_block_id => null,
734 p_time_building_block_ovn => null,
735 p_time_attribute_id => null,
736 p_time_attribute_ovn => null,
737 p_message_extent => null
738 );
739
740 hxc_timecard_message_helper.processerrors
741 (p_messages => p_messages);
742
743 p_messages := hxc_timecard_message_helper.prepareMessages;
744
745 end if;
746
747 p_blocks := l_exploded_details;
748 p_attributes := l_exploded_attributes;
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 FND_MESSAGE.SET_NAME('HXC','HXC_GET_EXPLODED_HOURS');
753 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE',SQLERRM);
754 FND_MSG_PUB.add;
755
756 end getExplodedHours;
757
758 procedure deposit_blocks
759 (p_timecard_id out nocopy HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
760 ,p_timecard_ovn out nocopy HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
761 ,p_blocks IN HXC_BLOCK_TABLE_TYPE
762 ,p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE
763 ,p_item_type in WF_ITEMS.ITEM_TYPE%TYPE
764 ,p_process_name in WF_ACTIVITIES.NAME%TYPE
765 ,p_mode in varchar2
766 ,p_deposit_process in varchar2
767 ,p_retrieval_process in varchar2
768 ,p_sql_error out nocopy varchar2
769 ,p_validate_session in boolean default TRUE
770 ,p_add_security in boolean default TRUE
771 ) is
772
773 l_blocks HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO;
774 l_attributes HXC_SELF_SERVICE_TIME_DEPOSIT.BUILDING_BLOCK_ATTRIBUTE_INFO;
775
776 l_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE;
777 l_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE;
778
779 l_block_count NUMBER;
780 l_attribute_count NUMBER;
781
782 l_blocks_deposited block_list;
783
784 BEGIN
785
786 --
787 -- Initialize the global variables
788 --
789
790 hxc_self_Service_time_deposit.initialize_globals;
791
792
793 --
794 -- Initialize the workflow globals
795 --
796
797 hxc_self_service_time_deposit.set_workflow_info
798 (p_item_type => p_item_type
799 ,p_process_name => p_process_name
800 );
801
802 -- Create the block and attribute records, and pass to the
803 -- timecard deposit process.
804
805 l_blocks_deposited := globalBlockDeposit(p_blocks, true);
806
807 --
808 -- OK, now do the same with the attributes
809 --
810 globalAttributeDeposit(p_attributes, true, l_blocks_deposited);
811
812 --
813 -- Call the main deposit blocks procedure to actually
814 -- deposit the timecard information now that the globals
815 -- have been initialized
816 --
817
818 hxc_self_service_time_deposit.deposit_blocks
819 (p_timecard_id => l_timecard_id
820 ,p_timecard_ovn => l_timecard_ovn
821 ,p_mode => p_mode
822 ,p_deposit_process => p_deposit_process
823 ,p_retrieval_process => p_retrieval_process
824 ,p_validate_session => p_validate_session
825 ,p_add_security => p_add_security
826 );
827
828 --
829 -- Set the timecard id and ovn, and exit.
830 --
831
832 p_timecard_id := l_timecard_id;
833 p_timecard_ovn := l_timecard_ovn;
834
835 EXCEPTION
836 WHEN e_array_time_deposit THEN
837 FND_MSG_PUB.ADD;
838 FND_MESSAGE.clear;
839
840 WHEN OTHERS THEN
841 p_sql_error := SQLERRM;
842 raise;
843
844 END deposit_blocks;
845
846 end hxc_array_time_deposit;