[Home] [Help]
PACKAGE BODY: APPS.HXC_BLOCK_COLLECTION_UTILS
Source
1 PACKAGE BODY hxc_block_collection_utils AS
2 /* $Header: hxcbkcout.pkb 120.6 2006/06/13 23:38:00 arundell noship $ */
3
4 C_END_OF_DAY CONSTANT NUMBER := .999988426;
5
6 type block_id_list is table of hxc_time_building_blocks.time_building_block_id%type;
7 type block_ovn_list is table of hxc_time_building_blocks.object_version_number%type;
8
9 Procedure append_to_list
10 (p_list1 in block_id_list,
11 p_list2 in out nocopy block_id_list,
12 p_list3 in block_ovn_list,
13 p_list4 in out nocopy block_ovn_list) is
14
15 l_id_idx binary_integer;
16
17 begin
18 l_id_idx := p_list1.first;
19 Loop
20 Exit when not p_list1.exists(l_id_idx);
21 p_list2.extend;
22 p_list2(p_list2.last) := p_list1(l_id_idx);
23 p_list4.extend;
24 p_list4(p_list4.last) := p_list3(l_id_idx);
25 l_id_idx := p_list1.next(l_id_idx);
26 End Loop;
27
28 end append_to_list;
29
30 Procedure load_attributes
31 (p_block_id_list in block_id_list,
32 p_block_ovn_list in block_ovn_list,
33 p_load_template_attributes in varchar2,
34 p_attributes in out NOCOPY hxc_attribute_table_type
35 ) is
36
37 cursor c_attribute_type_info
38 (p_block_id in hxc_time_building_blocks.time_building_block_id%type,
39 p_block_ovn in hxc_time_building_blocks.object_version_number%type,
40 p_load_template_attributes in varchar2) is
41 select hxc_attribute_type
42 (ta.TIME_ATTRIBUTE_ID,
43 p_block_id,
44 ta.ATTRIBUTE_CATEGORY,
45 ta.ATTRIBUTE1,
46 ta.ATTRIBUTE2,
47 ta.ATTRIBUTE3,
48 ta.ATTRIBUTE4,
49 ta.ATTRIBUTE5,
50 ta.ATTRIBUTE6,
51 ta.ATTRIBUTE7,
52 ta.ATTRIBUTE8,
53 ta.ATTRIBUTE9,
54 ta.ATTRIBUTE10,
55 ta.ATTRIBUTE11,
56 ta.ATTRIBUTE12,
57 ta.ATTRIBUTE13,
58 ta.ATTRIBUTE14,
59 ta.ATTRIBUTE15,
60 ta.ATTRIBUTE16,
61 ta.ATTRIBUTE17,
62 ta.ATTRIBUTE18,
63 ta.ATTRIBUTE19,
64 ta.ATTRIBUTE20,
65 ta.ATTRIBUTE21,
66 ta.ATTRIBUTE22,
67 ta.ATTRIBUTE23,
68 ta.ATTRIBUTE24,
69 ta.ATTRIBUTE25,
70 ta.ATTRIBUTE26,
71 ta.ATTRIBUTE27,
72 ta.ATTRIBUTE28,
73 ta.ATTRIBUTE29,
74 ta.ATTRIBUTE30,
75 ta.BLD_BLK_INFO_TYPE_ID,
76 ta.OBJECT_VERSION_NUMBER,
77 'N',
78 'N',
79 bbit.BLD_BLK_INFO_TYPE,
80 'N',
81 p_block_ovn)
82 from hxc_time_attribute_usages tau,
83 hxc_bld_blk_info_types bbit,
84 hxc_time_attributes ta
85 where tau.time_building_block_id = p_block_Id
86 and tau.time_building_block_ovn = p_block_ovn
87 and tau.time_attribute_id = ta.time_attribute_id
88 and (ta.attribute_category <> nvl(decode(p_load_template_attributes,'Y','TEMPLATES'),'$Sys_deF$')
89 and
90 ta.attribute_category <> nvl(decode(p_load_template_attributes,'Y','REASON'),'$Sys_deF$')
91 and
92 ta.attribute_category <> nvl(decode(p_load_template_attributes,'Y','SECURITY'),'$Sys_deF$')
93 )
94 and ta.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
95
96 l_attributes hxc_attribute_table_type;
97 l_attribute_index binary_integer;
98 l_block_index binary_integer;
99
100 Begin
101 if(p_attributes is null) then
102 p_attributes := hxc_attribute_table_type();
103 end if;
104 l_attributes := hxc_attribute_table_type();
105 l_block_index := p_block_id_list.first;
106 Loop
107 Exit when not p_block_id_list.exists(l_block_index);
108 open c_attribute_type_info(p_block_id_list(l_block_index),p_block_ovn_list(l_block_index),p_load_template_attributes);
109 fetch c_attribute_type_info bulk collect into l_attributes;
110 close c_attribute_type_info;
111 l_attribute_index := l_attributes.first;
112 Loop
113 Exit when not l_attributes.exists(l_attribute_index);
114 p_attributes.extend;
115 p_attributes(p_attributes.last) := l_attributes(l_attribute_index);
116 l_attribute_index := l_attributes.next(l_attribute_index);
117 End Loop;
118 l_block_index := p_block_id_list.next(l_block_index);
119 End Loop;
120
121 End load_attributes;
122
123 Procedure load_top_level_block
124 (p_top_level_block_id in hxc_time_building_blocks.time_building_block_id%type,
125 p_blocks in out nocopy hxc_block_table_type,
126 p_top_level_start_date in out nocopy date,
127 p_top_level_stop_date in out nocopy date
128 ) is
129
130 CURSOR c_block_type_info
131 (p_block_id in hxc_time_building_blocks.time_building_block_id%type) is
132 select hxc_block_type
133 (time_building_block_id,
134 type,
135 measure,
136 unit_of_measure,
137 fnd_date.date_to_canonical(start_time),
138 fnd_date.date_to_canonical(stop_time),
139 parent_building_block_id,
140 'N',
141 scope,
142 object_version_number,
143 approval_status,
144 resource_id,
145 resource_type,
146 approval_style_id,
147 fnd_date.date_to_canonical(date_from),
148 fnd_date.date_to_canonical(date_to),
149 comment_text,
150 parent_building_block_ovn,
151 'N',
152 'N',
153 'N',
154 application_set_id,
155 translation_display_key
156 )
157 from hxc_time_building_blocks
158 where time_building_block_id = p_block_id
159 and date_to = hr_general.end_of_time;
160
161 Begin
162 if(p_blocks is null) then
163 p_blocks := hxc_block_table_type();
164 end if;
165 p_blocks.extend;
166 open c_block_type_info(p_top_level_block_id);
167 fetch c_block_type_info into p_blocks(1);
168 if(c_block_type_info%notfound) then
169 p_blocks := null;
170 else
171 p_top_level_start_date := fnd_date.canonical_to_date(p_blocks(1).start_time);
172 p_top_level_stop_date := fnd_date.canonical_to_date(p_blocks(1).stop_time);
173 end if;
174 close c_block_type_info;
175 End load_top_level_block;
176
177 Procedure load_app_period_days
178 (p_top_level_block_id in hxc_time_building_blocks.time_building_block_id%type,
179 p_top_level_block_ovn in hxc_time_building_blocks.object_version_number%type,
180 p_start_time in date,
181 p_stop_time in date,
182 p_day_id_list out nocopy block_id_list,
183 p_day_ovn_list out nocopy block_ovn_list,
184 p_blocks in out nocopy hxc_block_table_type,
185 p_attributes in out nocopy hxc_attribute_table_type
186 ) is
187
188 CURSOR c_block_type_info is
189 select hxc_block_type
190 (days.time_building_block_id,
191 days.type,
192 days.measure,
193 days.unit_of_measure,
194 fnd_date.date_to_canonical(days.start_time),
195 fnd_date.date_to_canonical(days.stop_time),
196 p_top_level_block_id,
197 'N',
198 days.scope,
199 days.object_version_number,
200 days.approval_status,
201 days.resource_id,
202 days.resource_type,
203 days.approval_style_id,
204 fnd_date.date_to_canonical(days.date_from),
205 fnd_date.date_to_canonical(days.date_to),
206 days.comment_text,
207 p_top_level_block_ovn,
208 'N',
209 'N',
210 'N',
211 days.application_set_id,
212 days.translation_display_key),
213 days.time_building_block_id,
214 days.object_version_number
215 from hxc_time_building_blocks days,
216 hxc_time_building_blocks top_level
217 where top_level.time_building_block_id = p_top_level_block_id
218 and top_level.object_version_number = p_top_level_block_ovn
219 and days.resource_id = top_level.resource_id
220 and trunc(days.start_time) between trunc(top_level.start_time) and trunc(top_level.stop_time)
221 and days.scope = 'DAY'
222 and days.date_to = hr_general.end_of_time
223 and days.start_time >= p_start_time
224 and days.stop_time <= p_stop_time
225 and exists
226 (select 'Y'
227 from hxc_time_building_blocks timecard_check
228 where timecard_check.scope = 'TIMECARD'
229 and timecard_check.resource_id = days.resource_id
230 and timecard_check.date_to = hr_general.end_of_time
231 and timecard_check.time_building_block_id = days.parent_building_block_id
232 and timecard_check.object_version_number = days.parent_building_block_ovn
233 )
234 order by days.start_time;
235
236 l_day_blocks hxc_block_table_type;
237 l_day_index pls_integer;
238
239 Begin
240
241 l_day_blocks := hxc_block_table_type();
242 open c_block_type_info;
243 fetch c_block_type_info bulk collect into l_day_blocks,p_day_id_list,p_day_ovn_list;
244 close c_block_type_info;
245 l_day_index := l_day_blocks.first;
246 Loop
247 Exit when not l_day_blocks.exists(l_day_index);
248 p_blocks.extend;
249 p_blocks(p_blocks.last) := l_day_blocks(l_day_index);
250 l_day_index := l_day_blocks.next(l_day_index);
251 End Loop;
252
253 End load_app_period_days;
254
255 Procedure load_days
256 (p_top_level_block_id in hxc_time_building_blocks.time_building_block_id%type,
257 p_top_level_block_ovn in hxc_time_building_blocks.object_version_number%type,
258 p_day_id_list out nocopy block_id_list,
259 p_day_ovn_list out nocopy block_ovn_list,
260 p_blocks in out nocopy hxc_block_table_type,
261 p_attributes in out nocopy hxc_attribute_table_type
262 ) is
263
264 CURSOR c_block_type_info is
265 select hxc_block_type
266 (days.time_building_block_id,
267 days.type,
268 days.measure,
269 days.unit_of_measure,
270 fnd_date.date_to_canonical(days.start_time),
271 fnd_date.date_to_canonical(days.stop_time),
272 days.parent_building_block_id,
273 'N',
274 days.scope,
275 days.object_version_number,
276 days.approval_status,
277 days.resource_id,
278 days.resource_type,
279 days.approval_style_id,
280 fnd_date.date_to_canonical(days.date_from),
281 fnd_date.date_to_canonical(days.date_to),
282 days.comment_text,
283 days.parent_building_block_ovn,
284 'N',
285 'N',
286 'N',
287 days.application_set_id,
288 days.translation_display_key),
289 days.time_building_block_id,
290 days.object_version_number
294 and top_level.object_version_number = p_top_level_block_ovn
291 from hxc_time_building_blocks days,
292 hxc_time_building_blocks top_level
293 where top_level.time_building_block_id = p_top_level_block_id
295 and days.parent_building_block_id = top_level.time_building_block_Id
296 and days.parent_building_block_ovn = top_level.object_version_number
297 and days.object_version_number = (select max(object_version_number)
298 from hxc_time_building_blocks days_ovn
299 where days_ovn.time_building_block_id = days.time_building_block_id
300 and days_ovn.parent_building_block_id = top_level.time_building_block_Id
301 and days_ovn.parent_building_block_ovn = top_level.object_version_number)
302 order by days.start_time;
303
304 l_day_blocks hxc_block_table_type;
305 l_day_index pls_integer;
306 Begin
307 l_day_blocks := hxc_block_table_type();
308 open c_block_type_info;
309 fetch c_block_type_info bulk collect into l_day_blocks,p_day_id_list,p_day_ovn_list;
310 close c_block_type_info;
311 l_day_index := l_day_blocks.first;
312 Loop
313 Exit when not l_day_blocks.exists(l_day_index);
314 p_blocks.extend;
315 p_blocks(p_blocks.last) := l_day_blocks(l_day_index);
316 l_day_index := l_day_blocks.next(l_day_index);
317 End Loop;
318
319 End load_days;
320
321 Procedure add_missing_days
322 (p_blocks in out nocopy hxc_block_table_type,
323 p_start_time in date,
324 p_stop_time in date
325 ) is
326
327 l_index pls_integer;
328 l_curr_day date;
329 l_day hxc_block_type;
330 l_temp_blocks hxc_block_table_type;
331 l_day_diff number;
332
333 Begin
334 l_temp_blocks := hxc_block_table_type();
335 -- Check for days missing at the start
336 l_day_diff := trunc(fnd_date.canonical_to_date(p_blocks(2).start_time)) - trunc(p_start_time);
337 l_day := p_blocks(2);
338 if(l_day_diff > 0) then
339 l_curr_day := p_start_time;
340 -- Missing days from the front of the period. Add them, preserving the order.
341 l_temp_blocks.extend();
342 l_temp_blocks(1) := p_blocks(1);
343 For l_index in 2..(1+l_day_diff) Loop
344 l_day.start_time := fnd_date.date_to_canonical(l_curr_day);
345 l_day.stop_time := fnd_date.date_to_canonical((l_curr_day + C_END_OF_DAY));
346 l_day.time_building_block_id := -2-l_index;
347 l_temp_blocks.extend();
348 l_temp_blocks(l_index) := l_day;
349 l_curr_day := l_curr_day + 1;
350 End Loop;
351 -- append the existing days
352 l_index := 2;
353 Loop
354 Exit when not p_blocks.exists(l_index);
355 l_temp_blocks.extend();
356 l_temp_blocks(l_temp_blocks.last) := p_blocks(l_index);
357 l_index := p_blocks.next(l_index);
358 End Loop;
359 end if;
360 if(l_temp_blocks.count>0) then
361 p_blocks := l_temp_blocks;
362 end if;
363 -- Check for days missing at the end
364 l_temp_blocks := hxc_block_table_type();
365 l_day_diff := trunc(p_stop_time) - trunc(fnd_date.canonical_to_date(p_blocks(p_blocks.last).stop_time));
366 l_day := p_blocks(2);
367 if(l_day_diff > 0) then
368 -- preppend the existing days
369 l_index := p_blocks.first;
370 Loop
371 Exit when not p_blocks.exists(l_index);
372 l_temp_blocks.extend();
373 l_temp_blocks(l_temp_blocks.last) := p_blocks(l_index);
374 l_index := p_blocks.next(l_index);
375 End Loop;
376 l_curr_day := (fnd_date.canonical_to_date(p_blocks(p_blocks.last).start_time)+1);
377 For l_index in (l_temp_blocks.last+1) .. (l_temp_blocks.last+l_day_diff) Loop
378 l_day.start_time := fnd_date.date_to_canonical(l_curr_day);
379 l_day.stop_time := fnd_date.date_to_canonical((l_curr_day + C_END_OF_DAY));
380 l_day.time_building_block_id := -2-l_index;
381 l_temp_blocks.extend();
382 l_temp_blocks(l_index) := l_day;
383 l_curr_day := l_curr_day + 1;
384 End Loop;
385 end if;
386 if(l_temp_blocks.count >0) then
387 p_blocks := l_temp_blocks;
388 end if;
389 End add_missing_days;
390
391 Procedure load_app_period_details
392 (p_app_period_id in hxc_time_building_blocks.time_building_block_id%type,
393 p_start_time in date,
394 p_stop_time in date,
395 p_detail_id_list in out nocopy block_id_list,
396 p_detail_ovn_list in out nocopy block_ovn_list,
397 p_blocks in out NOCOPY hxc_block_table_type,
398 p_attributes in out NOCOPY hxc_attribute_table_type,
399 p_row_data out NOCOPY hxc_trans_display_key_utils.translation_row_used,
400 p_missing_rows in out NOCOPY boolean) is
401
402 CURSOR c_block_type_info
403 (p_app_period_id in hxc_time_building_blocks.time_building_block_id%type)
404 is
405 select hxc_block_type
406 (details.time_building_block_id,
407 details.type,
408 details.measure,
409 details.unit_of_measure,
410 fnd_date.date_to_canonical(details.start_time),
411 fnd_date.date_to_canonical(details.stop_time),
412 details.parent_building_block_id,
413 'N',
414 details.scope,
415 details.object_version_number,
419 details.approval_style_id,
416 details.approval_status,
417 details.resource_id,
418 details.resource_type,
420 fnd_date.date_to_canonical(details.date_from),
421 fnd_date.date_to_canonical(details.date_to),
422 details.comment_text,
423 details.parent_building_block_ovn,
424 'N',
425 'N',
426 'N',
427 details.application_set_id,
428 details.translation_display_key),
429 details.time_building_block_id,
430 details.object_version_number
431 from hxc_time_building_blocks details,
432 hxc_time_building_blocks days,
433 hxc_ap_detail_links adl
434 where details.time_building_block_id = adl.time_building_block_id
435 and details.object_version_number = adl.time_building_block_ovn
436 and days.start_time >= p_start_time
437 and days.stop_time <= p_stop_time
438 and days.time_building_block_id = details.parent_building_block_id
439 and days.object_version_number = details.parent_building_block_ovn
440 and adl.application_period_id = p_app_period_id
441 and details.date_to = hr_general.end_of_time;
442
443 l_detail_blocks hxc_block_table_type;
444 l_detail_index pls_integer;
445
446 Begin
447
448 l_detail_blocks := hxc_block_table_type();
449 open c_block_type_info(p_app_period_id);
450 fetch c_block_type_info bulk collect into l_detail_blocks,p_detail_id_list, p_detail_ovn_list;
451 close c_block_type_info;
452
453 l_detail_index := l_detail_blocks.first;
454 Loop
455 Exit when not l_detail_blocks.exists(l_detail_index);
456 p_blocks.extend;
457 p_blocks(p_blocks.last) := l_detail_blocks(l_detail_index);
458 l_detail_index := l_detail_blocks.next(l_detail_index);
459 if(p_missing_rows) then
460 hxc_trans_display_key_utils.set_row_data
461 (p_blocks(p_blocks.last).translation_display_key,
462 p_row_data);
463 end if;
464 End Loop;
465
466 If (p_missing_rows) then
467 p_missing_rows := hxc_trans_display_key_utils.missing_rows(p_row_data);
468 end if;
469
470 End load_app_period_details;
471
472 Procedure load_details
473 (p_day_id_list in block_id_list,
474 p_day_ovn_list in block_ovn_list,
475 p_detail_id_list in out nocopy block_id_list,
476 p_detail_ovn_list in out nocopy block_ovn_list,
477 p_blocks in out NOCOPY hxc_block_table_type,
478 p_attributes in out NOCOPY hxc_attribute_table_type,
479 p_row_data out NOCOPY hxc_trans_display_key_utils.translation_row_used,
480 p_missing_rows in out NOCOPY boolean) is
481
482 CURSOR c_block_type_info
483 (p_day_id in hxc_time_building_blocks.time_building_block_id%type,
484 p_day_ovn in hxc_time_building_blocks.object_version_number%type)
485 is
486 select hxc_block_type
487 (details.time_building_block_id,
488 details.type,
489 details.measure,
490 details.unit_of_measure,
491 fnd_date.date_to_canonical(details.start_time),
492 fnd_date.date_to_canonical(details.stop_time),
493 details.parent_building_block_id,
494 'N',
495 details.scope,
496 details.object_version_number,
497 details.approval_status,
498 details.resource_id,
499 details.resource_type,
500 details.approval_style_id,
501 fnd_date.date_to_canonical(details.date_from),
502 fnd_date.date_to_canonical(details.date_to),
503 details.comment_text,
504 details.parent_building_block_ovn,
505 'N',
506 'N',
507 'N',
508 details.application_set_id,
509 details.translation_display_key)
510 from hxc_time_building_blocks details
511 where details.parent_building_block_id = p_day_id
512 and details.parent_building_block_ovn = p_day_ovn
513 and details.date_to = hr_general.end_of_time;
514
515 l_day_index binary_integer;
516 l_detail_blocks hxc_block_table_type;
517 l_detail_index pls_integer;
518
519 Begin
520 p_detail_id_list := block_id_list();
521 p_detail_ovn_list := block_ovn_list();
522 l_detail_blocks := hxc_block_table_type();
523 --
524 -- When can use forall and bulkcollect together
525 -- in SQL statements, change this to use forall!
526 --
527 l_day_index := p_day_id_list.first;
528 Loop
529 Exit when not p_day_id_list.exists(l_day_index);
530 open c_block_type_info(p_day_id_list(l_day_index),p_day_ovn_list(l_day_index));
531 fetch c_block_type_info bulk collect into l_detail_blocks;
532 close c_block_type_info;
533 l_detail_index := l_detail_blocks.first;
534 Loop
535 Exit when not l_detail_blocks.exists(l_detail_index);
536 p_blocks.extend;
537 p_blocks(p_blocks.last) := l_detail_blocks(l_detail_index);
538 p_detail_id_list.extend;
539 p_detail_id_list(p_detail_id_list.last) := p_blocks(p_blocks.last).time_building_block_id;
540 p_detail_ovn_list.extend;
541 p_detail_ovn_list(p_detail_ovn_list.last) := p_blocks(p_blocks.last).object_version_number;
542 l_detail_index := l_detail_blocks.next(l_detail_index);
543 if(p_missing_rows) then
544 hxc_trans_display_key_utils.set_row_data
545 (p_blocks(p_blocks.last).translation_display_key,
549 l_day_index := p_day_id_list.next(l_day_index);
546 p_row_data);
547 end if;
548 End Loop;
550 End Loop;
551 If (p_missing_rows) then
552 p_missing_rows := hxc_trans_display_key_utils.missing_rows(p_row_data);
553 end if;
554 End load_details;
555
556 PROCEDURE load_collection
557 (p_top_level_block_id in hxc_time_building_blocks.time_building_block_id%type,
558 p_load_template_attributes in varchar2,
559 p_blocks out NOCOPY hxc_block_table_type,
560 p_attributes out NOCOPY hxc_attribute_table_type,
561 p_top_level_start_date out NOCOPY date,
562 p_top_level_stop_date out NOCOPY date,
563 p_row_data out NOCOPY hxc_trans_display_key_utils.translation_row_used,
564 p_missing_rows in out NOCOPY boolean
565 ) is
566
567 l_day_id_list block_id_list;
568 l_day_ovn_list block_ovn_list;
569 l_detail_id_list block_id_list;
570 l_detail_ovn_list block_ovn_list;
571
572 Begin
573
574 load_top_level_block
575 (p_top_level_block_id,
576 p_blocks,
577 p_top_level_start_date,
578 p_top_level_stop_date);
579
580 if(p_blocks is not null) then
581 load_days
582 (p_top_level_block_id,
583 p_blocks(1).object_version_number,
584 l_day_id_list,
585 l_day_ovn_list,
586 p_blocks,
587 p_attributes);
588 load_details
589 (l_day_id_list,
590 l_day_ovn_list,
591 l_detail_id_list,
592 l_detail_ovn_list,
593 p_blocks,
594 p_attributes,
595 p_row_data,
596 p_missing_rows);
597 l_detail_id_list.extend;
598 l_detail_id_list(l_detail_id_list.last) := p_blocks(1).time_building_block_id;
599 l_detail_ovn_list.extend;
600 l_detail_ovn_list(l_detail_ovn_list.last) := p_blocks(1).object_version_number;
601
602 append_to_list(l_day_id_list,l_detail_id_list,l_day_ovn_list,l_detail_ovn_list);
603
604 load_attributes(l_detail_id_list,l_detail_ovn_list,p_load_template_attributes,p_attributes);
605
606 else
607 p_attributes := null;
608 end if;
609 End load_collection;
610
611 PROCEDURE load_collection
612 (p_top_level_block_id in hxc_time_building_blocks.time_building_block_id%type,
613 p_blocks out NOCOPY hxc_block_table_type,
614 p_attributes out NOCOPY hxc_attribute_table_type,
615 p_row_data out NOCOPY hxc_trans_display_key_utils.translation_row_used,
616 p_missing_rows in out NOCOPY boolean
617 ) is
618
619 l_discard_date1 date;
620 l_discard_date2 date;
621
622 Begin
623
624 load_collection
625 (p_top_level_block_id,
626 null,
627 p_blocks,
628 p_attributes,
629 l_discard_date1,
630 l_discard_date2,
631 p_row_data,
632 p_missing_rows
633 );
634
635 End load_collection;
636 --
637 -- Public Functions and Procedures, see the package header for documentation.
638 --
639 -- +--------------------------------------------------------------------------+
640 -- |----------------------< get_application_period >--------------------------|
641 -- +--------------------------------------------------------------------------+
642 --
643 PROCEDURE get_application_period
644 (p_app_period_id in hxc_time_building_blocks.time_building_block_id%type,
645 p_blocks out NOCOPY hxc_block_table_type,
646 p_attributes out NOCOPY hxc_attribute_table_type
647 ) is
648
649 cursor c_app_period_times
650 (p_application_period_id in hxc_app_period_summary.application_period_id%type) is
651 select start_time,
652 stop_time
653 from hxc_app_period_summary
654 where application_period_id = p_application_period_id;
655
656 l_start_time date;
657 l_stop_time date;
658
659 Begin
660 open c_app_period_times(p_app_period_id);
661 fetch c_app_period_times into l_start_time, l_stop_time;
662 if(c_app_period_times%found) then
663 close c_app_period_times;
664 get_application_period
665 (p_app_period_id,
666 l_start_time,
667 l_stop_time,
668 p_blocks,
669 p_attributes
670 );
671 else
672 close c_app_period_times;
673 end if;
674
675 End get_application_period;
676
677 PROCEDURE get_application_period
678 (p_app_period_id in hxc_time_building_blocks.time_building_block_id%type,
679 p_start_time in date,
680 p_stop_time in date,
681 p_blocks out NOCOPY hxc_block_table_type,
682 p_attributes out NOCOPY hxc_attribute_table_type
683 ) is
684
685 cursor c_app_attribute
686 (p_app_period_id hxc_time_building_blocks.time_building_block_id%type) is
687 select hxc_attribute_type
688 (-2,
689 p_blocks(1).time_building_block_id,
690 'APPROVAL',
691 favtl.application_name,
692 '',
693 p.full_name,
694 '',
695 '',
696 '',
697 hr_general.decode_lookup('HXC_APPROVAL_STATUS', apsum.approval_status),
698 '',
699 '',
700 '' ,
704 '' ,
701 '' ,
702 '' ,
703 '' ,
705 '' ,
706 '' ,
707 '' ,
708 '' ,
709 '' ,
710 '' ,
711 '' ,
712 '' ,
713 '' ,
714 '' ,
715 '' ,
716 '' ,
717 '' ,
718 '' ,
719 '' ,
720 '' ,
721 bbit.bld_blk_info_type_id,
722 1,
723 'N',
724 'N',
725 bbit.bld_blk_info_type,
726 'N',
727 p_blocks(1).object_version_number
728 )
729 from hxc_app_period_summary apsum,
730 fnd_application_tl favtl,
731 hxc_time_recipients htr,
732 per_all_people_f p,
733 hxc_bld_blk_info_types bbit
734 where apsum.application_period_id = p_app_period_id
735 and favtl.application_id = htr.application_id
736 and htr.time_recipient_id = apsum.time_recipient_id
737 and favtl.language = userenv('LANG')
738 and p.person_id (+) = apsum.approver_id
739 and p.effective_end_date (+) = hr_general.end_of_time
740 and bbit.bld_blk_info_type = 'APPROVAL';
741
742 l_row_data hxc_trans_display_key_utils.translation_row_used;
743 l_missing_rows boolean;
744 l_app_period_start_time date;
745 l_app_period_stop_time date;
746 l_attribute hxc_attribute_type;
747 l_block_id_list block_id_list := block_id_list();
748 l_block_ovn_list block_ovn_list := block_ovn_list();
749 l_day_id_list block_id_list := block_id_list();
750 l_day_ovn_list block_ovn_list := block_ovn_list();
751 daynum pls_integer;
752 daysselected pls_integer;
753
754 Begin
755 p_blocks := null;
756
757 load_top_level_block
758 (p_app_period_id,
759 p_blocks,
760 l_app_period_start_time,
761 l_app_period_stop_time
762 );
763
764 if(p_blocks is not null) then
765 l_block_id_list.extend;
766 l_block_id_list(l_block_id_list.last) := p_blocks(1).time_building_block_id;
767 l_block_ovn_list.extend;
768 l_block_ovn_list(l_block_ovn_list.last) := p_blocks(1).object_version_number;
769 --
770 -- Reset start and stop time on the top level block for the alias translator
771 -- if different periods - we're only interested in the period associated with
772 -- the timecard anyway.
773 --
774 if(trunc(l_app_period_start_time) <> trunc(p_start_time)) then
775 p_blocks(1).start_time := fnd_date.date_to_canonical(p_start_time);
776 end if;
777 if(trunc(l_app_period_stop_time) <> trunc(p_stop_time)) then
778 p_blocks(1).stop_time := fnd_date.date_to_canonical(p_stop_time);
779 end if;
780 --
781 -- Ok, now add the dummy application attribute, still used in the fragment view
782 -- Since the block structure does not include the columns from the summary tables
783 --
784 if(p_attributes is null) then
785 p_attributes := hxc_attribute_table_type();
786 end if;
787 open c_app_attribute(p_app_period_id);
788 fetch c_app_attribute into l_attribute;
789 if(c_app_attribute%found) then
790 close c_app_attribute;
791 p_attributes.extend();
792 p_attributes(p_attributes.last) := l_attribute;
793 else
794 close c_app_attribute;
795 end if;
796 --
797 -- Get the days associated with this Application Period
798 --
799 load_app_period_days
800 (p_blocks(1).time_building_block_id,
801 p_blocks(1).object_version_number,
802 p_start_time,
803 p_stop_time,
804 l_day_id_list,
805 l_day_ovn_list,
806 p_blocks,
807 p_attributes
808 );
809 append_to_list(l_day_id_list,l_block_id_list,l_day_ovn_list,l_block_ovn_list);
810 daynum := trunc(p_stop_time)-trunc(p_start_time)+1;
811 daysselected := l_day_id_list.count;
812 if(daynum <> daysselected) then
813 -- Add missing days
814 add_missing_days
815 (p_blocks,
816 p_start_time,
817 p_stop_time
818 );
819 end if;
820 --
821 -- Get the details associated with the application period
822 --
823 l_day_id_list := block_id_list();
824 l_day_ovn_list := block_ovn_list();
825 l_missing_rows := true;
826 load_app_period_details
827 (l_block_id_list(1),
828 p_start_time,
829 p_stop_time,
830 l_day_id_list,
831 l_day_ovn_list,
832 p_blocks,
833 p_attributes,
834 l_row_data,
835 l_missing_rows
836 );
837 append_to_list(l_day_id_list,l_block_id_list,l_day_ovn_list,l_block_ovn_list);
838 --
839 -- Get the appropriate attributes
840 --
841 load_attributes(l_block_id_list,l_block_ovn_list,hxc_timecard.c_yes,p_attributes);
842
846 --
843 end if; -- Did the top level block exist?
844
845 End get_application_period;
847 -- +--------------------------------------------------------------------------+
848 -- |------------------------< get_timecard >--------------------------|
849 -- +--------------------------------------------------------------------------+
850 --
851 PROCEDURE get_timecard
852 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type,
853 p_blocks out NOCOPY hxc_block_table_type,
854 p_attributes out NOCOPY hxc_attribute_table_type
855 ) is
856 l_row_data hxc_trans_display_key_utils.translation_row_used;
857 l_missing_rows boolean;
858 Begin
859 -- Tell load collection we do not care about
860 -- the row translation information.
861 l_missing_rows := false;
862 get_timecard
863 (p_timecard_id => p_timecard_id,
864 p_blocks => p_blocks,
865 p_attributes => p_attributes,
866 p_row_data => l_row_data,
867 p_missing_rows => l_missing_rows
868 );
869 End get_timecard;
870 --
871 -- +--------------------------------------------------------------------------+
872 -- |------------------------< get_timecard >--------------------------|
873 -- +--------------------------------------------------------------------------+
874 --
875 PROCEDURE get_timecard
876 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type,
877 p_blocks out NOCOPY hxc_block_table_type,
878 p_attributes out NOCOPY hxc_attribute_table_type,
879 p_row_data out NOCOPY hxc_trans_display_key_utils.translation_row_used,
880 p_missing_rows in out NOCOPY boolean
881 ) is
882 Begin
883
884 p_blocks := hxc_block_table_type();
885 p_attributes := hxc_attribute_table_type();
886 load_collection
887 (p_timecard_id,
888 p_blocks,
889 p_attributes,
890 p_row_data,
891 p_missing_rows
892 );
893 End;
894 --
895 -- +--------------------------------------------------------------------------+
896 -- |------------------------< get_template >--------------------------|
897 -- +--------------------------------------------------------------------------+
898 --
899 PROCEDURE get_template
900 (p_template_id in hxc_time_building_blocks.time_building_block_id%type,
901 p_blocks out NOCOPY hxc_block_table_type,
902 p_attributes out NOCOPY hxc_attribute_table_type,
903 p_template_start_time out NOCOPY date,
904 p_template_stop_time out NOCOPY date
905 )is
906
907 l_row_data hxc_trans_display_key_utils.translation_row_used;
908 l_missing_rows boolean;
909
910 Begin
911 p_blocks := hxc_block_table_type();
912 p_attributes := hxc_attribute_table_type();
913 l_missing_rows := false;
914
915 load_collection
916 (p_template_id,
917 'Y',
918 p_blocks,
919 p_attributes,
920 p_template_start_time,
921 p_template_stop_time,
922 l_row_data,
923 l_missing_rows
924 );
925
926 End get_template;
927
928
929 END hxc_block_collection_utils;