1 package body hxc_timecard_hours_pkg as
2 /* $Header: hxchours.pkb 115.11 2002/06/10 00:37:12 pkm ship $ */
3 --
4 --
5 -- procedure
6 -- get_hours
7 --
8 -- description
9 -- Gets the sum for a particular type of Hours as defined by
10 -- the lookup type
11 --
12 -- parameters
13 -- p_timecard_id - timecard id
14 -- p_timecard_ovn - timecard ovn
15 -- p_lookup_type - hours type lookup
16 --
17
18 TYPE t_element_ids IS TABLE OF
19 NUMBER
20 INDEX BY BINARY_INTEGER;
21
22
23 FUNCTION get_hours( p_timecard_id NUMBER,
24 p_timecard_ovn NUMBER,
25 p_lookup_type VARCHAR2) RETURN NUMBER
26 IS
27
28
29 -- Might want to run the following cursor off an effective date
30 -- which would require a date to be passed to this routine.
31
32 CURSOR csr_element_ids(p_lookup_type VARCHAR2) IS
33 select
34 petf.element_type_id element_type_id
35 from
36 hr_lookups hl,
37 pay_element_types_f petf
38 where
39 upper(petf.element_name) = hl.lookup_code
40 and hl.lookup_type = p_lookup_type
41 and petf.effective_end_date = hr_general.end_of_time;
42
43 CURSOR csr_element_details(p_timecard_id NUMBER,
44 p_timecard_ovn NUMBER,
45 p_element_segment VARCHAR2,
46 p_element_context VARCHAR2,
47 p_element_category VARCHAR2)
48 IS
49 SELECT
50 DECODE(p_element_segment,
51 'ATTRIBUTE1' , ta_det.attribute1,
52 'ATTRIBUTE2' , ta_det.attribute2,
53 'ATTRIBUTE3' , ta_det.attribute3,
54 'ATTRIBUTE4' , ta_det.attribute4,
55 'ATTRIBUTE5' , ta_det.attribute5,
56 'ATTRIBUTE6' , ta_det.attribute6,
57 'ATTRIBUTE7' , ta_det.attribute7,
58 'ATTRIBUTE8' , ta_det.attribute8,
59 'ATTRIBUTE9' , ta_det.attribute9,
60 'ATTRIBUTE10' , ta_det.attribute10,
61 'ATTRIBUTE11' , ta_det.attribute11,
62 'ATTRIBUTE12' , ta_det.attribute12,
63 'ATTRIBUTE13' , ta_det.attribute13,
64 'ATTRIBUTE14' , ta_det.attribute14,
65 'ATTRIBUTE15' , ta_det.attribute15,
66 'ATTRIBUTE16' , ta_det.attribute16,
67 'ATTRIBUTE17' , ta_det.attribute17,
68 'ATTRIBUTE18' , ta_det.attribute18,
69 'ATTRIBUTE19' , ta_det.attribute19,
70 'ATTRIBUTE20' , ta_det.attribute20,
71 'ATTRIBUTE21' , ta_det.attribute21,
72 'ATTRIBUTE22' , ta_det.attribute22,
73 'ATTRIBUTE23' , ta_det.attribute23,
74 'ATTRIBUTE24' , ta_det.attribute24,
75 'ATTRIBUTE25' , ta_det.attribute25,
76 'ATTRIBUTE26' , ta_det.attribute26,
77 'ATTRIBUTE27' , ta_det.attribute27,
78 'ATTRIBUTE28' , ta_det.attribute28,
79 'ATTRIBUTE29' , ta_det.attribute29,
80 'ATTRIBUTE30' , ta_det.attribute30,
81 'ATTRIBUTE_CATEGORY', ta_det.attribute_category) element_id,
82 tbb_det.measure,
83 tbb_det.unit_of_measure,
84 tbb_det.start_time,
85 tbb_det.stop_time,
86 tbb_det.type,
87 tbb_det.scope
88 FROM hxc_time_building_blocks tbb_day,
89 hxc_time_building_blocks tbb_det,
90 hxc_time_attribute_usages tau_det,
91 hxc_time_attributes ta_det
92 WHERE tbb_det.scope = 'DETAIL'
93 and tbb_day.scope = 'DAY'
94 and tbb_det.date_to = hr_general.end_of_time
95 and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
96 and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
97 and tbb_day.parent_building_block_id = p_timecard_id
98 and tbb_day.parent_building_block_ovn = p_timecard_ovn
99 and tau_det.time_building_block_id = tbb_det.time_building_block_id
100 and tau_det.time_building_block_ovn = tbb_det.object_version_number
101 and tau_det.time_attribute_id = ta_det.time_attribute_id
102 and ta_det.attribute_category = nvl(p_element_context,ta_det.attribute_category)
103 and ta_det.BLD_BLK_INFO_TYPE_ID in
104 (select BLD_BLK_INFO_TYPE_ID from HXC_BLD_BLK_INFO_TYPE_USAGES
105 where BUILDING_BLOCK_CATEGORY=p_element_category);
106
107 -- this is not elegant. View hxc_mapping_attributes_v should include building_block_category
108 -- so this can be tidied up.
109
110 cursor csr_element_location is
111 select ma.context context,
112 ma.segment segment,
113 bbitu.building_block_category category
114 from hxc_mapping_attributes_v ma,
115 hxc_deposit_processes dp,
116 hxc_mappings mp,
117 hxc_bld_blk_info_type_usages bbitu
118 where ma.map = mp.name
119 and mp.mapping_id = dp.mapping_id
120 and dp.name = 'OTL Deposit Process'
121 and ma.field_name = 'Dummy Element Context'
122 and bbitu.bld_blk_info_type_id=ma.bld_blk_info_type_id;
123
124 l_running_total number;
125 l_element_context hxc_time_attributes.attribute_category%TYPE;
126 l_element_segment VARCHAR2(30);
127 l_element_category hxc_bld_blk_info_type_usages.building_block_category%TYPE;
128
129 l_element_ids t_element_ids;
130 l_element_count number;
131
132 BEGIN
133
134 l_running_total := 0;
135
136 -- build a list of elements that represent the type of hours
137
138 l_element_count := 0;
139
140 FOR l_element_id in csr_element_ids(p_lookup_type) LOOP
141
142 l_element_ids(l_element_count):=l_element_id.element_type_id;
143 l_element_count:=l_element_count+1;
144
145 END LOOP;
146
147 -- find out where the elements are kept in this instance
148
149 open csr_element_location;
150 fetch csr_element_location into l_element_context,l_element_segment,l_element_category;
151 close csr_element_location;
152
153 -- There are two cases. One case is where element_ids are stored in a specific
154 -- context/segment combination. Another is where elements are stored in the
155 -- attribute_category of many contexts - one for each element.
156
157 IF(l_element_context='Dummy Element Context') THEN
158
159 l_element_context:=null;
160
161 END IF;
162
163 -- get all attributes of the timecard in question
164 -- could just get the elements but its not going to
165 -- slow things down significantly.
166
167 FOR l_element_detail in
168 csr_element_details(p_timecard_id,p_timecard_ovn,
169 l_element_segment,l_element_context,l_element_category) LOOP
170
171 -- its an element. Now simply check this against our list to
172 -- see if it needs to be included in the sum
173
174 FOR l_iterator in 0..l_element_count-1 LOOP
175
176 IF(substr(l_element_detail.element_id,11,20) = l_element_ids(l_iterator)) then
177
178 -- add to the total based on whether is a range or measure detail
179
180 if(l_element_detail.type = 'MEASURE' and
181 l_element_detail.unit_of_measure = 'HOURS' and
182 l_element_detail.measure is not null) then
183 l_running_total:=l_running_total + l_element_detail.measure;
184 end if;
185 if(l_element_detail.type = 'RANGE' and
186 l_element_detail.start_time is not null and
187 l_element_detail.stop_time is not null ) then
188 l_running_total:=l_running_total +
189 (l_element_detail.stop_time-l_element_detail.start_time)*24;
190 end if;
191
192 -- If the detail was not understood then just ignore it.
193 -- Might want to raise an error here in the future.
194
195 -- Note that details with more than one element attribute attached may
196 -- be double counted. This sort of data is not expected.
197
198 END IF;
199
200 END LOOP;
201
202 END LOOP; -- end attribute loop
203
204 RETURN l_running_total;
205
206 END get_hours;
207
208 ------------------------------------------------------------------------
209 -- overload the above one so that we won't possibly break existing code
210 ------------------------------------------------------------------------
211
212 -- aioannou - this should be merged asap.
213
214 FUNCTION get_hours(
215 p_period_start_time IN DATE,
216 p_period_stop_time IN DATE,
217 p_resource_id IN NUMBER,
218 p_lookup_type IN VARCHAR2
219 )
220 RETURN NUMBER
221 IS
222
223
224 -- Might want to run the following cursor off an effective date
225 -- which would require a date to be passed to this routine.
226
227 CURSOR csr_element_ids(p_lookup_type VARCHAR2) IS
228 select
229 petf.element_type_id element_type_id
230 from
231 hr_lookups hl,
232 pay_element_types_f petf
233 where
234 upper(petf.element_name) = hl.lookup_code
235 and hl.lookup_type = p_lookup_type
236 and petf.effective_end_date = hr_general.end_of_time;
237
238 CURSOR csr_element_details(p_period_start_time DATE,
239 p_period_stop_time DATE,
240 p_resource_id NUMBER,
241 p_element_segment VARCHAR2,
242 p_element_context VARCHAR2,
243 p_element_category VARCHAR2)
244
245 IS
246 SELECT
247 DECODE(p_element_segment,
248 'ATTRIBUTE1' , ta_det.attribute1,
249 'ATTRIBUTE2' , ta_det.attribute2,
250 'ATTRIBUTE3' , ta_det.attribute3,
251 'ATTRIBUTE4' , ta_det.attribute4,
252 'ATTRIBUTE5' , ta_det.attribute5,
253 'ATTRIBUTE6' , ta_det.attribute6,
254 'ATTRIBUTE7' , ta_det.attribute7,
255 'ATTRIBUTE8' , ta_det.attribute8,
256 'ATTRIBUTE9' , ta_det.attribute9,
257 'ATTRIBUTE10' , ta_det.attribute10,
258 'ATTRIBUTE11' , ta_det.attribute11,
259 'ATTRIBUTE12' , ta_det.attribute12,
260 'ATTRIBUTE13' , ta_det.attribute13,
261 'ATTRIBUTE14' , ta_det.attribute14,
262 'ATTRIBUTE15' , ta_det.attribute15,
263 'ATTRIBUTE16' , ta_det.attribute16,
264 'ATTRIBUTE17' , ta_det.attribute17,
265 'ATTRIBUTE18' , ta_det.attribute18,
266 'ATTRIBUTE19' , ta_det.attribute19,
267 'ATTRIBUTE20' , ta_det.attribute20,
268 'ATTRIBUTE21' , ta_det.attribute21,
269 'ATTRIBUTE22' , ta_det.attribute22,
270 'ATTRIBUTE23' , ta_det.attribute23,
271 'ATTRIBUTE24' , ta_det.attribute24,
272 'ATTRIBUTE25' , ta_det.attribute25,
273 'ATTRIBUTE26' , ta_det.attribute26,
274 'ATTRIBUTE27' , ta_det.attribute27,
275 'ATTRIBUTE28' , ta_det.attribute28,
276 'ATTRIBUTE29' , ta_det.attribute29,
277 'ATTRIBUTE30' , ta_det.attribute30,
278 'ATTRIBUTE_CATEGORY', ta_det.attribute_category) element_id,
279 tbb_det.measure,
280 tbb_det.unit_of_measure,
281 tbb_det.start_time,
282 tbb_det.stop_time,
283 tbb_det.type,
284 tbb_det.scope
285 FROM hxc_time_building_blocks tbb_day,
286 hxc_time_building_blocks tbb_det,
287 hxc_time_attribute_usages tau_det,
288 hxc_time_attributes ta_det
289 WHERE tbb_det.scope = 'DETAIL'
290 and tbb_day.scope = 'DAY'
291 and tbb_day.date_to = hr_general.end_of_time
292 and tbb_det.date_to = hr_general.end_of_time
293 and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
294 and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
295 and tbb_day.start_time >= p_period_start_time
296 and tbb_day.stop_time <= p_period_stop_time
297 and tbb_det.resource_id = p_resource_id
298 and tau_det.time_building_block_id = tbb_det.time_building_block_id
299 and tau_det.time_building_block_ovn = tbb_det.object_version_number
300 and tau_det.time_attribute_id = ta_det.time_attribute_id
301 and ta_det.attribute_category = nvl(p_element_context,ta_det.attribute_category)
302 and ta_det.BLD_BLK_INFO_TYPE_ID in
303 (select BLD_BLK_INFO_TYPE_ID from HXC_BLD_BLK_INFO_TYPE_USAGES
304 where BUILDING_BLOCK_CATEGORY=p_element_category);
305
306
307 cursor csr_element_location is
308 select ma.context context,
309 ma.segment segment,
310 bbitu.building_block_category category
311 from hxc_mapping_attributes_v ma,
312 hxc_deposit_processes dp,
313 hxc_mappings mp,
314 hxc_bld_blk_info_type_usages bbitu
315 where ma.map = mp.name
316 and mp.mapping_id = dp.mapping_id
317 and dp.name = 'OTL Deposit Process'
318 and ma.field_name = 'Dummy Element Context'
319 and bbitu.bld_blk_info_type_id=ma.bld_blk_info_type_id;
320
321 l_running_total number;
322 l_element_context hxc_time_attributes.attribute_category%TYPE;
323 l_element_segment VARCHAR2(30);
324 l_element_category hxc_bld_blk_info_type_usages.building_block_category%TYPE;
325
326 l_element_ids t_element_ids;
327 l_element_count number;
328
329 BEGIN
330
331 l_running_total:=0;
332
333 -- build a list of elements that represent the type of hours
334
335 l_element_count:=0;
336
337 FOR l_element_id in csr_element_ids(p_lookup_type) LOOP
338
339 l_element_ids(l_element_count):=l_element_id.element_type_id;
340 l_element_count:=l_element_count+1;
341 END LOOP;
342
343 -- find out where the elements are kept in this instance
344
345 open csr_element_location;
346 fetch csr_element_location into l_element_context,l_element_segment,l_element_category;
347 close csr_element_location;
348
349 IF(l_element_context='Dummy Element Context') THEN
350 l_element_context:=null;
351 END IF;
352
353 -- get all attributes of the timecard in question
354 -- could just get the elements but its not going to
355 -- slow things down significantly.
356
357 FOR l_element_detail in
358 csr_element_details(p_period_start_time,p_period_stop_time,
359 p_resource_id,
360 l_element_segment,l_element_context,l_element_category) LOOP
361
362 -- its an element. Now simply check this against our list to
363 -- see if it needs to be included in the sum
364
365 FOR l_iterator in 0..l_element_count-1 LOOP
366
367 IF(substr(l_element_detail.element_id,11,20) = l_element_ids(l_iterator)) then
368
369 -- add to the total based on whether is a range or measure detail
370
371 if(l_element_detail.type = 'MEASURE' and
372 l_element_detail.unit_of_measure = 'HOURS' and
373 l_element_detail.measure is not null) then
374 l_running_total:=l_running_total + l_element_detail.measure;
375 end if;
376 if(l_element_detail.type = 'RANGE' and
377 l_element_detail.start_time is not null and
378 l_element_detail.stop_time is not null ) then
379 l_running_total:=l_running_total +
380 (l_element_detail.stop_time-l_element_detail.start_time)*24;
381 end if;
382
383 -- If the detail was not understood then just ignore it.
384 -- Might want to raise an error here in the future.
385
386 -- Note that details with more than one element attribute attached may
387 -- be double counted. This sort of data is not expected.
388
389 END IF;
390
391 END LOOP;
392
393 END LOOP; -- end attribute loop
394
395 RETURN l_running_total;
396
397 END get_hours;
398
399
400 --------------------------------------------------------------------------
401 -- overload get_total_hours
402 -- mode parameter specifies how to deal with the start/stop times
403 -- passed in
407 -- p_mode = 'DATE_TIME' -- only count hours if the details lie within
404 -- p_mode = 'DAYS_INCLUSIVE' -- ignore time component of dates passed in
405 -- consider summation from the start of the
406 -- start day to the end of the end day.
408 -- the interval of time as defined by
409 -- period_start_time/period_stop_time
410 --------------------------------------------------------------------------
411 FUNCTION get_total_hours(
412 p_period_start_time IN DATE,
413 p_period_stop_time IN DATE,
414 p_resource_id IN NUMBER,
415 p_mode IN VARCHAR2 DEFAULT 'DAYS_INCLUSIVE'
416 )
417 RETURN NUMBER
418 IS
419 l_sum NUMBER := 0;
420 l_start_time DATE;
421 l_stop_time DATE;
422 l_one_sec_as_day_fraction number:=(1/24/60/60);
423
424 BEGIN
425
426 if(p_mode = 'DAYS_INCLUSIVE') then
427 l_start_time := trunc(p_period_start_time);
428 l_stop_time := trunc(p_period_stop_time+1)-l_one_sec_as_day_fraction;
429 elsif (p_mode = 'DATE_TIME') then
430 l_start_time := p_period_start_time;
431 l_stop_time := p_period_stop_time;
432 end if;
433
434 select
435 sum(decode(tbb_det.type,
436 'MEASURE',nvl(tbb_det.measure,0),
437 'RANGE', nvl(tbb_det.stop_time-tbb_det.start_time,0)*24)) into l_sum
438 FROM hxc_time_building_blocks tbb_day,
439 hxc_time_building_blocks tbb_det,
440 hxc_time_building_blocks tbb_tim
441 WHERE tbb_det.scope = 'DETAIL'
442 and tbb_day.scope = 'DAY'
443 and tbb_tim.scope = 'TIMECARD'
444 and tbb_det.date_to = hr_general.end_of_time
445 and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
446 and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
447 and tbb_day.parent_building_block_ovn = tbb_tim.object_version_number
448 and tbb_day.parent_building_block_id = tbb_tim.time_building_block_id
449 and tbb_day.start_time >= l_start_time
450 and tbb_day.stop_time <= l_stop_time
451 and tbb_det.resource_id = p_resource_id;
452
453 return l_sum;
454
455 END get_total_hours;
456
457 -- get_total_hours
458 --
459 -- procedure
460 -- Calculates the total hours for a timecard
461 --
462 -- description
463 --
464 -- parameters
465 -- p_timecard_id - timecard Id
466 -- p_timecard_ovn - timecard Ovn
467 --
468
469 FUNCTION get_total_hours
470 (
471 p_timecard_id number,
472 p_timecard_ovn number
473 ) RETURN NUMBER
474 is
475
476 l_sum number;
477
478 BEGIN
479
480 l_sum := 0;
481
482 select
483 sum(decode(tbb_det.type,
484 'MEASURE',nvl(tbb_det.measure,0),
485 'RANGE', nvl(tbb_det.stop_time-tbb_det.start_time,0)*24)) into l_sum
486 FROM hxc_time_building_blocks tbb_day,
487 hxc_time_building_blocks tbb_det,
488 hxc_time_building_blocks tbb_tim
489 WHERE tbb_det.scope = 'DETAIL'
490 and tbb_day.scope = 'DAY'
491 and tbb_tim.scope = 'TIMECARD'
492 and tbb_day.parent_building_block_ovn = tbb_tim.object_version_number
493 and tbb_day.parent_building_block_id = tbb_tim.time_building_block_id
494 and tbb_det.date_to = hr_general.end_of_time
495 and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
496 and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
497 and tbb_day.parent_building_block_id = p_timecard_id
498 and tbb_day.parent_building_block_ovn = p_timecard_ovn;
499
500
501 return l_sum;
502
503 END get_total_hours;
504
505 END hxc_timecard_hours_pkg;
[Home] [Help]