[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMEKEEPER_UTILITIES
Source
1 PACKAGE BODY hxc_timekeeper_utilities AS
2 /* $Header: hxctkutil.pkb 120.30 2011/12/23 12:45:26 jnerella ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 -------------------------------------------------------------------------------
5 -- this procedure add a block in the block_table
6 -------- -----------------------------------------------------------------------
7 PROCEDURE add_block (
8 p_timecard IN OUT NOCOPY hxc_block_table_type,
9 p_timecard_id IN NUMBER,
10 p_ovn IN NUMBER,
11 p_parent_id IN NUMBER,
12 p_parent_ovn IN NUMBER,
13 p_approval_style_id IN NUMBER,
14 p_measure IN NUMBER,
15 p_scope IN VARCHAR2,
16 p_date_to IN DATE,
17 p_date_from IN DATE,
18 p_start_period IN DATE,
19 p_end_period IN DATE,
20 p_resource_id IN NUMBER,
21 p_changed IN VARCHAR2,
22 p_comment_text IN VARCHAR2,
23 p_submit_flg IN BOOLEAN,
24 p_application_set_id IN hxc_time_building_blocks.application_set_id%type,
25 p_timecard_index_info IN OUT NOCOPY hxc_timekeeper_process.t_timecard_index_info
26 ) IS
27 l_type hxc_time_building_blocks.TYPE%TYPE;
28 l_parent_is_new VARCHAR2 (1);
29 l_approval_status hxc_time_building_blocks.approval_status%TYPE;
30 l_new VARCHAR2 (1);
31 l_block_index NUMBER
32 /*ADVICE(99): NUMBER has no precision [315] */
33 := 0;
34 l_date_from VARCHAR2 (50);
35 l_date_to VARCHAR2 (50);
36
37 l_changed VARCHAR2(1);
38 l_application_set_id HXC_TIME_BUILDING_BLOCKS.APPLICATION_SET_ID%TYPE;
39 cursor c_app_set_id(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type,
40 p_ovn in hxc_time_building_blocks.object_version_number%type)
41 is
42 select
43 application_set_id
44 from
45 hxc_time_building_blocks
46 where
47 time_building_block_id = p_timecard_id
48 and
49 object_version_number=p_ovn;
50
51 BEGIN
52
53 l_changed := p_changed;
54
55 IF p_measure IS NOT NULL THEN
56 l_type := 'MEASURE';
57 ELSE
58 l_type := 'RANGE';
59 END IF;
60
61 IF (p_parent_id < 0) THEN
62 l_parent_is_new := 'Y';
63 ELSE
64 l_parent_is_new := 'N';
65 END IF;
66
67 IF (p_submit_flg) THEN
68 l_approval_status := 'SUBMITTED';
69 ELSE
70 l_approval_status := 'WORKING';
71 END IF;
72
73 IF (p_timecard_id < 0) THEN
74 l_new := 'Y';
75 ELSE
76 l_new := 'N';
77 /*fix for 5099360 */
78 IF p_application_set_id IS NULL then
79 open c_app_set_id(p_timecard_id,p_ovn);
80 fetch c_app_set_id into l_application_set_id;
81 close c_app_set_id;
82 ELSE
83 l_application_set_id:=p_application_set_id;
84 END if;
85 /*end of fix for 5099360 */
86 END IF;
87
88 l_date_from := fnd_date.date_to_canonical (p_date_from);
89 l_date_to := fnd_date.date_to_canonical (p_date_to);
90
91
92 IF p_timecard_index_info.EXISTS (p_timecard_id) THEN
93 l_block_index := p_timecard_index_info (p_timecard_id).time_block_row_index;
94
95 if g_debug then
96 hr_utility.trace('l_block_index1 = '||l_block_index);
97 end if;
98 --it is an update and we don't have to change detae from or date to
99 IF p_date_from IS NULL AND p_date_to IS NULL THEN
100 l_date_from := p_timecard (l_block_index).date_from;
101 l_date_to := p_timecard (l_block_index).date_to;
102 END IF;
103 -- check the changed flag
104 IF l_changed <> 'Y' THEN
105
106 IF ((nvl(p_timecard (l_block_index).measure,-999) <> nvl(p_measure,-999))
107 OR (nvl(fnd_date.date_to_canonical
108 (to_date(p_timecard (l_block_index).start_time,'YYYY/MM/DD HH24:MI:SS')),-999)
109 <> nvl(fnd_date.date_to_canonical(
110 to_date(p_start_period,'YYYY/MM/DD HH24:MI:SS')),-999))
111 OR (nvl(fnd_date.date_to_canonical (
112 to_date(p_timecard (l_block_index).stop_time,'YYYY/MM/DD HH24:MI:SS')),-999)
113 <> nvl(fnd_date.date_to_canonical(to_date(p_end_period,'YYYY/MM/DD HH24:MI:SS')),-999))
114 OR (nvl(p_timecard (l_block_index).comment_text,-999)
115 <> nvl(p_comment_text,-999))) THEN
116 l_changed := 'Y';
117 END IF;
118 END IF;
119
120 ELSE
121 p_timecard.EXTEND;
122 l_block_index := p_timecard.LAST;
123 if g_debug then
124 hr_utility.trace('l_block_index2 = '||l_block_index);
125 end if;
126
127 END IF;
128
129 if g_debug then
130 hr_utility.trace('Going to create hxc_block_type');
131 end if;
132
133 p_timecard (l_block_index) := hxc_block_type (
134 p_timecard_id,
135 l_type,
136 p_measure,
137 'HOURS',
138 fnd_date.date_to_canonical (p_start_period),
139 fnd_date.date_to_canonical (p_end_period),
140 p_parent_id,
141 'N',
142 p_scope,
143 nvl(p_ovn,1),
144 l_approval_status,
145 p_resource_id,
146 'PERSON',
147 p_approval_style_id,
148 l_date_from,
149 l_date_to,
150 p_comment_text,
151 p_parent_ovn,
152 l_new,
153 l_changed,
154 'N',
155 l_APPLICATION_SET_ID,
156 NULL
157 );
158 if g_debug then
159 hr_utility.trace('after create hxc_block_type');
160 end if;
161
162 p_timecard_index_info (p_timecard_id).time_block_row_index := l_block_index;
163
164 if g_debug then
165 hr_utility.trace('after p_timecard_index_info');
166 end if;
167
168 END add_block;
169
170 -------------------------------------------------------------------------------
171 -- this procedure add a attribute in the attribute_table
172 -------------------------------------------------------------------------------
173 PROCEDURE add_attribute (
174 p_attribute IN OUT NOCOPY hxc_attribute_table_type,
175 p_attribute_id IN NUMBER,
176 p_tbb_id IN NUMBER,
177 p_tbb_ovn IN NUMBER,
178 p_blk_type IN VARCHAR2,
179 p_blk_id IN NUMBER,
180 p_att_category IN VARCHAR2,
181 p_att_1 IN VARCHAR2,
182 p_att_2 IN VARCHAR2,
183 p_att_3 IN VARCHAR2,
184 p_att_4 IN VARCHAR2,
185 p_att_5 IN VARCHAR2 DEFAULT NULL,
186 p_att_6 IN VARCHAR2 DEFAULT NULL,
187 p_att_7 IN VARCHAR2 DEFAULT NULL,
188 p_att_8 IN VARCHAR2 DEFAULT NULL,
189 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
190 ) IS
191 l_new VARCHAR2 (1);
192 l_attribute_index NUMBER
193 /*ADVICE(195): NUMBER has no precision [315] */
194 := 0;
195 BEGIN
196 IF p_attribute_index_info.EXISTS (p_attribute_id) THEN
197 l_attribute_index := p_attribute_index_info (p_attribute_id).attribute_block_row_index;
198 ELSE
199 p_attribute.EXTEND;
200 l_attribute_index := p_attribute.LAST;
201 END IF;
202
203 IF p_attribute_id > 0 THEN
204 l_new := 'N';
205 ELSE
206 l_new := 'Y';
207 END IF;
208
209 p_attribute (l_attribute_index) := hxc_attribute_type (
210 p_attribute_id,
211 p_tbb_id,
212 p_att_category,
213 p_att_1,
214 p_att_2,
215 p_att_3,
216 p_att_4,
217 p_att_5,
218 p_att_6,
219 p_att_7,
220 p_att_8,
221 NULL,
222 NULL,
223 NULL,
224 NULL,
225 NULL,
226 NULL,
227 NULL,
228 NULL,
229 NULL,
230 NULL,
231 NULL,
232 NULL,
233 NULL,
234 NULL,
235 NULL,
236 NULL,
237 NULL,
238 NULL,
239 NULL,
240 NULL,
241 NULL,
242 NULL,
243 p_blk_id,
244 NULL,
245 l_new,
246 'Y',
247 p_blk_type,
248 'N',
249 p_tbb_ovn
250 );
251 p_attribute_index_info (p_attribute_id).attribute_block_row_index := l_attribute_index;
252 END add_attribute;
253
254
255 -------------------------------------------------------------------------------
256 -- this procedure create the attribute structure for an timecard_id
257 -------------------------------------------------------------------------------
258 PROCEDURE create_attribute_structure (
259 p_timecard_id IN NUMBER,
260 p_timecard_ovn IN NUMBER,
261 p_resource_id IN NUMBER,
262 p_start_period
263 /*ADVICE(265): Unreferenced parameter [552] */
264 IN DATE,
265 p_end_period
266 /*ADVICE(268): Unreferenced parameter [552] */
267 IN DATE,
268 p_attributes OUT NOCOPY hxc_attribute_table_type,
269 p_add_hours_type_id
270 /*ADVICE(272): Unreferenced parameter [552] */
271 IN NUMBER,
272 p_attribute_index_info OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
273 ) IS
274 CURSOR c_detail_attribute (
275 timecard_id IN NUMBER,
276 timecard_ovn IN NUMBER,
277 l_resource_id IN NUMBER
278 ) IS
279 SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
280 a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
281 a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
282 a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
283 a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
284 a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
285 a.bld_blk_info_type_id, a.object_version_number
286 FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
287 WHERE a.time_attribute_id = au.time_attribute_id
288 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
289 AND (au.time_building_block_id, au.time_building_block_ovn) IN
290 (SELECT detail.time_building_block_id, detail.object_version_number
291 FROM hxc_time_building_blocks detail, hxc_time_building_blocks DAY
292 WHERE DAY.time_building_block_id = detail.parent_building_block_id
293 AND DAY.object_version_number = detail.parent_building_block_ovn
294 AND DAY.SCOPE = 'DAY'
295 AND detail.resource_id = l_resource_id
296 AND detail.SCOPE = 'DETAIL'
297 AND DAY.date_to = hr_general.end_of_time
298 AND detail.date_to = hr_general.end_of_time
299 AND DAY.parent_building_block_id = timecard_id
300 AND DAY.parent_building_block_ovn = timecard_ovn
301 AND DAY.resource_id = l_resource_id)
302 UNION
303 SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
304 a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
305 a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
306 a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
307 a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
308 a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
309 a.bld_blk_info_type_id, a.object_version_number
310 FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
311 WHERE a.time_attribute_id = au.time_attribute_id
312 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
313 AND (au.time_building_block_id, au.time_building_block_ovn) IN
314 (SELECT DAY.time_building_block_id, DAY.object_version_number
315 FROM hxc_time_building_blocks DAY
316 WHERE DAY.date_to = hr_general.end_of_time
317 AND DAY.SCOPE = 'DAY'
318 AND DAY.parent_building_block_id = timecard_id
319 AND DAY.parent_building_block_ovn = timecard_ovn
320 AND DAY.resource_id = l_resource_id)
321 UNION
322 SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
323 a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
324 a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
325 a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
326 a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
327 a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
328 a.bld_blk_info_type_id, a.object_version_number
329 FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
330 WHERE a.time_attribute_id = au.time_attribute_id
331 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
332 AND (au.time_building_block_id, au.time_building_block_ovn) IN
333 (SELECT time_building_block_id, object_version_number
334 FROM hxc_time_building_blocks htbb
335 WHERE htbb.date_to = hr_general.end_of_time
336 AND htbb.SCOPE = 'TIMECARD'
337 AND htbb.time_building_block_id = timecard_id
338 AND htbb.object_version_number = timecard_ovn
339 AND htbb.resource_id = l_resource_id)
340 ORDER BY time_building_block_id;
341
342 l_attribute_index NUMBER
343 /*ADVICE(345): NUMBER has no precision [315] */
344 := 0;
345 l_attribute8 varchar2(150);
346 l_attribute3 varchar2(150);
347 l_attribute4 varchar2(150);
348 BEGIN
349 p_attributes := hxc_attribute_table_type ();
350
351 FOR detail_attribute_info IN c_detail_attribute (p_timecard_id, p_timecard_ovn, p_resource_id) LOOP
352 -- index the attribute table with the attribute_id
353 p_attributes.EXTEND;
354 l_attribute_index := p_attributes.LAST;
355 /* start of changes made by senthil for 4295540 */
356 if hxc_timekeeper_process.g_submit and detail_attribute_info.attribute_category = 'REASON' then
357 l_attribute8:='N';
358 else
359 l_attribute8:=detail_attribute_info.attribute8;
360 end if;
361 /* end of changes made by senthil */
362
363 --Condition Added By Mithun for Persistent resp Enhancement
364 --condition was added so that Whenever TK makes modification to a timecard
365 --The resp_id and user_id stored in attribute3 and attribute4 gets refreshed.
366 if hxc_timekeeper_process.g_submit and detail_attribute_info.attribute_category = 'SECURITY' then
367 l_attribute3:=FND_GLOBAL.USER_ID;
368 l_attribute4:=FND_GLOBAL.RESP_ID;
369 else
370 l_attribute3:=detail_attribute_info.attribute3;
371 l_attribute4:=detail_attribute_info.attribute4;
372 end if;
373 --End of Condition Added By Mithun
374
375 p_attributes (l_attribute_index) := hxc_attribute_type (
376 detail_attribute_info.time_attribute_id,
377 detail_attribute_info.time_building_block_id,
378 detail_attribute_info.attribute_category,
379 detail_attribute_info.attribute1,
380 detail_attribute_info.attribute2,
381 l_attribute3,
382 l_attribute4,
383 detail_attribute_info.attribute5,
384 detail_attribute_info.attribute6,
385 detail_attribute_info.attribute7,
386 l_attribute8,
387 detail_attribute_info.attribute9,
388 detail_attribute_info.attribute10,
389 detail_attribute_info.attribute11,
390 detail_attribute_info.attribute12,
391 detail_attribute_info.attribute13,
392 detail_attribute_info.attribute14,
393 detail_attribute_info.attribute15,
394 detail_attribute_info.attribute16,
395 detail_attribute_info.attribute17,
396 detail_attribute_info.attribute18,
397 detail_attribute_info.attribute19,
398 detail_attribute_info.attribute20,
399 detail_attribute_info.attribute21,
400 detail_attribute_info.attribute22,
401 detail_attribute_info.attribute23,
402 detail_attribute_info.attribute24,
403 detail_attribute_info.attribute25,
404 detail_attribute_info.attribute26,
405 detail_attribute_info.attribute27,
406 detail_attribute_info.attribute28,
407 detail_attribute_info.attribute29,
408 detail_attribute_info.attribute30,
409 detail_attribute_info.bld_blk_info_type_id,
410 detail_attribute_info.object_version_number,
411 'N',
412 'N',
413 detail_attribute_info.bld_blk_info_type,
414 'N',
415 NULL
416 );
417 p_attribute_index_info (detail_attribute_info.time_attribute_id).attribute_block_row_index :=
418 l_attribute_index;
419 END LOOP;
420 END create_attribute_structure;
421
422
423 ----------------------------------------------------------------------------
424 -- This Function is used to get which attribute is used to calculate the
425 -- Attribute category of the details associated with
426 ----------------------------------------------------------------------------
427 FUNCTION get_tk_dff_attrname (
428 p_tkid
429 /*ADVICE(408): Unreferenced parameter [552] */
430 IN NUMBER,
431 p_insert_detail IN hxc_timekeeper_process.t_time_info,
432 p_base_dff IN VARCHAR2,
433 p_att_tab IN hxc_alias_utility.t_alias_att_info
434 )
435 RETURN VARCHAR2 IS
436 att_dep_item NUMBER
437 /*ADVICE(416): NUMBER has no precision [315] */
438 ;
439 new_att_catg VARCHAR2 (2000);
440 /*ADVICE(419): VARCHAR2 declaration with length greater than 500 characters [307] */
441
442 l_reference_field fnd_descriptive_flexs.default_context_field_name%TYPE;
443
444 CURSOR c_reference_field IS
445 SELECT d.default_context_field_name
446 FROM fnd_descriptive_flexs d, fnd_application a, fnd_product_installations z
447 WHERE d.application_id = a.application_id
448 AND z.application_id = a.application_id
449 AND a.application_short_name = 'PA'
450 AND z.status = 'I'
451 AND d.descriptive_flexfield_name = 'PA_EXPENDITURE_ITEMS_DESC_FLEX';
452
453 BEGIN
454 g_debug :=hr_utility.debug_enabled;
455 --get the number say 3 FROM ATTRIBUTE3 using substr function
456 att_dep_item := TO_NUMBER (SUBSTR (p_base_dff, 10));
457 if g_debug then
458 hr_utility.trace('att_dep_item is '||att_dep_item);
459 hr_utility.trace('in detail is '||p_insert_detail.attr_id_3);
460 end if;
461 ---depending upon the number select id value from the timecard block
462 -- for 3 it will be attr_id_3
463 IF att_dep_item = 1 THEN
464 new_att_catg := p_insert_detail.attr_id_1;
465 ELSIF att_dep_item = 2 THEN
466 new_att_catg := p_insert_detail.attr_id_2;
467 ELSIF att_dep_item = 3 THEN
468 new_att_catg := p_insert_detail.attr_id_3;
469 ELSIF att_dep_item = 4 THEN
470 new_att_catg := p_insert_detail.attr_id_4;
471 ELSIF att_dep_item = 5 THEN
472 new_att_catg := p_insert_detail.attr_id_5;
473 ELSIF att_dep_item = 6 THEN
474 new_att_catg := p_insert_detail.attr_id_6;
475 ELSIF att_dep_item = 7 THEN
476 new_att_catg := p_insert_detail.attr_id_7;
477 ELSIF att_dep_item = 8 THEN
478 new_att_catg := p_insert_detail.attr_id_8;
479 ELSIF att_dep_item = 9 THEN
480 new_att_catg := p_insert_detail.attr_id_9;
481 ELSIF att_dep_item = 10 THEN
482 new_att_catg := p_insert_detail.attr_id_10;
483 ELSIF att_dep_item = 11 THEN
484 new_att_catg := p_insert_detail.attr_id_11;
485 ELSIF att_dep_item = 11 THEN
486 new_att_catg := p_insert_detail.attr_id_11;
487 ELSIF att_dep_item = 12 THEN
488 new_att_catg := p_insert_detail.attr_id_12;
489 ELSIF att_dep_item = 13 THEN
490 new_att_catg := p_insert_detail.attr_id_13;
491 ELSIF att_dep_item = 14 THEN
492 new_att_catg := p_insert_detail.attr_id_14;
493 ELSIF att_dep_item = 15 THEN
494 new_att_catg := p_insert_detail.attr_id_15;
495 ELSIF att_dep_item = 16 THEN
496 new_att_catg := p_insert_detail.attr_id_16;
497 ELSIF att_dep_item = 17 THEN
498 new_att_catg := p_insert_detail.attr_id_17;
499 ELSIF att_dep_item = 18 THEN
500 new_att_catg := p_insert_detail.attr_id_18;
501 ELSIF att_dep_item = 19 THEN
502 new_att_catg := p_insert_detail.attr_id_19;
503 ELSIF att_dep_item = 20 THEN
504 new_att_catg := p_insert_detail.attr_id_20;
505 END IF;
506
507 if g_debug then
508 hr_utility.trace('new cat is '||new_att_catg);
509 end if;
510 IF new_att_catg IS NOT NULL THEN
511 l_reference_field := NULL;
512 if g_debug then
513 hr_utility.trace('new cat is '||new_att_catg);
514 end if;
515 OPEN c_reference_field;
516 FETCH c_reference_field INTO l_reference_field;
517 CLOSE c_reference_field;
518
519 IF l_reference_field = 'SYSTEM_LINKAGE_FUNCTION' THEN
520 IF p_att_tab (att_dep_item).alias_type LIKE 'VALUE%' THEN
521 SELECT DECODE (
522 SUBSTR (new_att_catg, INSTR (new_att_catg, 'ALIAS_SEPARATOR') + 15),
523 'OT', 'PAEXPITDFF - OT',
524 'PAEXPITDFF - ST'
525 )
526 INTO new_att_catg
527 FROM DUAL;
528 ELSE
529 SELECT DECODE (
530 hxc_alias_utility.get_sfl_from_alias_value (new_att_catg),
531 'OT', 'PAEXPITDFF - OT',
532 'PAEXPITDFF - ST'
533 )
534 INTO new_att_catg
535 FROM DUAL;
536 END IF;
537 ELSIF l_reference_field = 'EXPENDITURE_TYPE' THEN
538 IF p_att_tab (att_dep_item).alias_type LIKE 'VALUE%' THEN
539 SELECT hxc_deposit_wrapper_utilities.get_dupdff_code (
540 'PAEXPITDFF - '|| SUBSTR (new_att_catg, 1, INSTR (new_att_catg, 'ALIAS_SEPARATOR') - 1)
541 )
542 INTO new_att_catg
543 FROM DUAL;
544 ELSE
545 -- new_att_catg:=get_exp_type_from_alias( new_att_catg);
546 new_att_catg :=
547 hxc_deposit_wrapper_utilities.get_dupdff_code (
548 'PAEXPITDFF - '|| get_exp_type_from_alias (new_att_catg)
549 );
550 END IF;
551 -- :DETAIL_BLK.C_ATTRIBUTE_CATEGORY:=HXC_DEPOSIT_WRAPPER_UTILITIES.GET_DUPDFF_CODE(:DETAIL_BLK.C_ATTRIBUTE_CATEGORY) ;--3791698
552 ELSIF l_reference_field = ''
553 /*ADVICE(525): In Oracle 8, VARCHAR2 variables of zero length assigned to CHAR variables will blank-pad
554 these rather than making them NULL [111] */
555 OR l_reference_field IS NULL THEN
556 IF (check_global_context ('PAEXPITDFF')) THEN
557 new_att_catg := 'PAEXPITDFF - GLOBAL';
558 END IF;
559 END IF;
560 END IF;
561
562 /*if new_att_catg is not null then
563 --now look for the type of that attribute if it is like '%VALUE%' then
564 --it will be either PAEXPITDFF - OT OR PAEXPITDFF - ST
565 if p_att_tab(att_dep_item).alias_type like 'VALUE%' then
566 SELECT decode(SUBSTR(new_att_catg,INSTR(new_att_catg,'ALIAS_SEPARATOR')+15),'OT','PAEXPITDFF - OT','PAEXPITDFF - ST')
567 INTO new_att_catg
568 FROM DUAL;
569 else
570 select decode(hxc_alias_utility.get_sfl_from_alias_value(new_att_catg),'OT','PAEXPITDFF - OT','PAEXPITDFF - ST')
571 INTO new_att_catg
572 FROM DUAL;
573 end if;
574 end if;*/
575 if g_debug then
576 hr_utility.trace('return new cat is '||new_att_catg);
577 end if;
578 RETURN (new_att_catg);
579 END;
580
581
582 -------------------------------------------------------------------------------
583 -------------------------------------------------------------------------------
584 PROCEDURE add_dff_attribute (
585 p_attribute IN OUT NOCOPY hxc_attribute_table_type,
586 p_attribute_id IN NUMBER,
587 p_tbb_id IN NUMBER,
588 p_tbb_ovn
589 /*ADVICE(559): Unreferenced parameter [552] */
590 IN NUMBER,
591 p_blk_type IN VARCHAR2,
592 p_blk_id IN NUMBER,
593 p_att_category IN VARCHAR2,
594 p_att_1 IN VARCHAR2,
595 p_att_2 IN VARCHAR2,
596 p_att_3 IN VARCHAR2,
597 p_att_4 IN VARCHAR2,
598 p_att_5 IN VARCHAR2,
599 p_att_6 IN VARCHAR2,
600 p_att_7 IN VARCHAR2,
601 p_att_8 IN VARCHAR2,
602 p_att_9 IN VARCHAR2,
603 p_att_10 IN VARCHAR2,
604 p_att_11 IN VARCHAR2,
605 p_att_12 IN VARCHAR2,
606 p_att_13 IN VARCHAR2,
607 p_att_14 IN VARCHAR2,
608 p_att_15 IN VARCHAR2,
609 p_att_16 IN VARCHAR2,
610 p_att_17 IN VARCHAR2,
611 p_att_18 IN VARCHAR2,
612 p_att_19 IN VARCHAR2,
613 p_att_20 IN VARCHAR2,
614 p_att_21 IN VARCHAR2,
615 p_att_22 IN VARCHAR2,
616 p_att_23 IN VARCHAR2,
617 p_att_24 IN VARCHAR2,
618 p_att_25 IN VARCHAR2,
619 p_att_26 IN VARCHAR2,
620 p_att_27 IN VARCHAR2,
621 p_att_28 IN VARCHAR2,
622 p_att_29 IN VARCHAR2,
623 p_att_30 IN VARCHAR2,
624 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
625 ) IS
626 l_new VARCHAR2 (1);
627 l_attribute_index NUMBER
628 /*ADVICE(598): NUMBER has no precision [315] */
629 := 0;
630 BEGIN
631
632 --p_attribute :=HXC_ATTRIBUTE_TABLE_TYPE();
633 IF p_attribute_index_info.EXISTS (p_attribute_id) THEN
634 l_attribute_index := p_attribute_index_info (p_attribute_id).attribute_block_row_index;
635 ELSE
636 p_attribute.EXTEND;
637 l_attribute_index := p_attribute.LAST;
638 END IF;
639
640 IF p_attribute_id > 0 THEN
641 l_new := 'N';
642 ELSE
643 l_new := 'Y';
644 END IF;
645
646 p_attribute (l_attribute_index) := hxc_attribute_type (
647 p_attribute_id,
648 p_tbb_id,
649 p_att_category,
650 p_att_1,
651 p_att_2,
652 p_att_3,
653 p_att_4,
654 p_att_5,
655 p_att_6,
656 p_att_7,
657 p_att_8,
658 p_att_9,
659 p_att_10,
660 p_att_11,
661 p_att_12,
662 p_att_13,
663 p_att_14,
664 p_att_15,
665 p_att_16,
666 p_att_17,
667 p_att_18,
668 p_att_19,
669 p_att_20,
670 p_att_21,
671 p_att_22,
672 p_att_23,
673 p_att_24,
674 p_att_25,
675 p_att_26,
676 p_att_27,
677 p_att_28,
678 p_att_29,
679 p_att_30,
680 p_blk_id,
681 NULL,
682 l_new,
683 'Y',
684 p_blk_type,
685 'Y',
686 NULL
687 );
688 p_attribute_index_info (p_attribute_id).attribute_block_row_index := l_attribute_index;
689 END add_dff_attribute;
690
691
692 -------------------------------------------------------------------------------
693 --used to order the timecard create ...but not used in the timekeeper process
694 -------------------------------------------------------------------------------
695 PROCEDURE order_building_blocks (
696 p_timecard IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
697 p_ord_timecard IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info
698 ) IS
699 l_timecard_start NUMBER
700 /*ADVICE(670): NUMBER has no precision [315] */
701 := 1;
702 l_block_count NUMBER
703 /*ADVICE(673): NUMBER has no precision [315] */
704 := 0;
705 n
706 /*ADVICE(676): Unreferenced variable [553] */
707 NUMBER
708 /*ADVICE(678): NUMBER has no precision [315] */
709 := 0;
710 BEGIN
711 IF (p_ord_timecard.COUNT > 0) THEN
712 p_ord_timecard.DELETE;
713 END IF;
714
715 l_block_count := p_timecard.FIRST;
716
717 LOOP
718 EXIT WHEN NOT p_timecard.EXISTS (l_block_count);
719 p_ord_timecard (l_timecard_start) := p_timecard (l_block_count);
720 l_timecard_start := l_timecard_start + 1;
721 l_block_count := p_timecard.NEXT (l_block_count);
722 END LOOP;
723 END order_building_blocks;
724
725
726 -------------------------------------------------------------------------------
727 -- This procedure dump the timkeeper date table information
728 -------------------------------------------------------------------------------
729 PROCEDURE dump_timkeeper_data (
730 p_timekeeper_data IN hxc_timekeeper_process.t_timekeeper_table
731 ) IS
732 l_index NUMBER
733 /*ADVICE(703): NUMBER has no precision [315] */
734 ;
735 BEGIN
736 l_index := p_timekeeper_data.FIRST;
737
738 LOOP
739 EXIT WHEN (NOT p_timekeeper_data.EXISTS (l_index));
740 l_index := p_timekeeper_data.NEXT (l_index);
741 END LOOP;
742 END;
743
744
745 -------------------------------------------------------------------------------
746 -- This procedure dump the buffer table information
747 -------------------------------------------------------------------------------
748 PROCEDURE dump_buffer_table (
749 p_buffer_table hxc_timekeeper_process.t_buffer_table
750 /*ADVICE(720): Mode of parameter is not specified with IN parameter [521] */
751
752 ) IS
753 l_index NUMBER
754 /*ADVICE(724): NUMBER has no precision [315] */
755 ;
756 BEGIN
757 l_index := p_buffer_table.FIRST;
758
759 LOOP
760 EXIT WHEN (NOT p_buffer_table.EXISTS (l_index));
761 l_index := p_buffer_table.NEXT (l_index);
762 END LOOP;
763 END dump_buffer_table;
764
765
766 -------------------------------------------------------------------------------
767 -- this procedure dump the resource tc table information
768 -------------------------------------------------------------------------------
769 PROCEDURE dump_resource_tc_table (
770 l_resource_tc_table hxc_timekeeper_process.t_resource_tc_table
771 /*ADVICE(741): Mode of parameter is not specified with IN parameter [521] */
772
773 ) IS
774 l_index NUMBER
775 /*ADVICE(745): NUMBER has no precision [315] */
776 ;
777 BEGIN
778 l_index := l_resource_tc_table.FIRST;
779
780 LOOP
781 EXIT WHEN (NOT l_resource_tc_table.EXISTS (l_index));
782
783 IF (1 >= fnd_log.g_current_runtime_level) THEN
784 fnd_log.STRING (
785 1,
786 'hxc_timekeeper_process.l_resource_tc_table',
787 'index_string:' || l_resource_tc_table (l_index).index_string || ' index:' || l_index
788 );
789 END IF;
790
791 l_index := l_resource_tc_table.NEXT (l_index);
792 END LOOP;
793 END dump_resource_tc_table;
794
795
796 -------------------------------------------------------------------------------
797 -- this procedure dump the buffer table information
798 -------------------------------------------------------------------------------
799 PROCEDURE dump_timecard (
800 p_timecard IN hxc_self_service_time_deposit.timecard_info
801 ) IS
802 l_index NUMBER
803 /*ADVICE(773): NUMBER has no precision [315] */
804 ;
805 BEGIN
806 l_index := p_timecard.FIRST;
807
808 LOOP
809 EXIT WHEN (NOT p_timecard.EXISTS (l_index));
810
811 IF (1 >= fnd_log.g_current_runtime_level) THEN
812 fnd_log.STRING (
813 1,
814 'hxc_timekeeper_process.dump_buffer_table',
815 'time_building_block_id :' || p_timecard (l_index).time_building_block_id || ' ovn :'
816 || p_timecard (l_index).object_version_number || ' start_time :' || p_timecard (l_index).start_time
817 || ' stop_time :' || p_timecard (l_index).stop_time || ' parent_id :'
818 || p_timecard (l_index).parent_building_block_id || ' parent_ovn :'
819 || p_timecard (l_index).parent_building_block_ovn || ' resource_id :'
820 || p_timecard (l_index).resource_id || ' resource_type :' || p_timecard (l_index).resource_type
821 || ' type :' || p_timecard (l_index).TYPE
822 );
823 fnd_log.STRING (
824 1,
825 'hxc_timekeeper_process.dump_buffer_table',
826 ' measure :' || p_timecard (l_index).measure || ' OFM :' || p_timecard (l_index).unit_of_measure
827 || ' parent_is_new :' || p_timecard (l_index).parent_is_new || ' scope :' || p_timecard (l_index).SCOPE
828 || ' app_status :' || p_timecard (l_index).approval_status || ' app_style_id :'
829 || p_timecard (l_index).approval_style_id || ' date_from :' || p_timecard (l_index).date_from
830 || ' date_to :' || p_timecard (l_index).date_to || ' comment_text :'
831 || p_timecard (l_index).comment_text || ' new :' || p_timecard (l_index).NEW || ' changed :'
832 || p_timecard (l_index).changed || ' index:' || l_index
833 );
834 END IF;
835
836 l_index := p_timecard.NEXT (l_index);
837 END LOOP;
838 END dump_timecard;
839
840
841 -------------------------------------------------------------------------------
842 -- this procedure used to give all timecards including midperiod timecards
843 -- saved in that range
844 -------------------------------------------------------------------------------
845 PROCEDURE populate_tc_tab (
846 resource_id IN NUMBER,
847 tc_frdt IN DATE,
848 tc_todt IN DATE,
849 emp_tc_info OUT NOCOPY hxc_timekeeper_utilities.emptctab
850 ) IS
851 CURSOR get_tc_data (
852 p_resource_id NUMBER,
853 p_tc_frdt DATE,
854 p_tc_todt DATE
855 ) IS
856 SELECT time_building_block_id tbbid, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
857 FROM hxc_time_building_blocks
858 WHERE resource_id
859 /*ADVICE(829): Cursor references an external variable (use a parameter) [209] */
860 = p_resource_id
861 AND SCOPE = 'TIMECARD'
862 AND date_to = hr_general.end_of_time
863 AND TO_DATE (p_tc_frdt, 'dd-mm-rrrr') BETWEEN TO_DATE (start_time, 'dd-mm-rrrr')
864 AND TO_DATE (stop_time, 'dd-mm-rrrr')
865 UNION
866 SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
867 FROM hxc_time_building_blocks
868 WHERE resource_id
869 /*ADVICE(839): Cursor references an external variable (use a parameter) [209] */
870 = p_resource_id
871 AND SCOPE = 'TIMECARD'
872 AND date_to = hr_general.end_of_time
873 AND TO_DATE (p_tc_todt, 'dd-mm-rrrr') BETWEEN TO_DATE (start_time, 'dd-mm-rrrr')
874 AND TO_DATE (stop_time, 'dd-mm-rrrr')
875 UNION
876 SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
877 FROM hxc_time_building_blocks
878 WHERE resource_id
879 /*ADVICE(849): Cursor references an external variable (use a parameter) [209] */
880 = p_resource_id
881 AND SCOPE = 'TIMECARD'
882 AND date_to = hr_general.end_of_time
883 AND TO_DATE (start_time, 'dd-mm-rrrr') >= TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
884 AND TO_DATE (stop_time, 'dd-mm-rrrr') <= TO_DATE (p_tc_todt, 'dd-mm-rrrr')
885 MINUS
886 SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
887 FROM hxc_time_building_blocks
888 WHERE resource_id
889 /*ADVICE(859): Cursor references an external variable (use a parameter) [209] */
890 = p_resource_id
891 AND SCOPE = 'TIMECARD'
892 AND date_to = hr_general.end_of_time
893 AND TO_DATE (start_time, 'dd-mm-rrrr') = TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
894 AND TO_DATE (stop_time, 'dd-mm-rrrr') = TO_DATE (p_tc_todt, 'dd-mm-rrrr')
895 ORDER BY 4;
896
897 tc_tab_rec get_tc_data%ROWTYPE;
898 p_index NUMBER
899 /*ADVICE(869): NUMBER has no precision [315] */
900 := 0;
901 BEGIN
902 --This procedure is used to get all the timecrds saved
903 --in the time building blocks which are between from and to date.
904 --used in timekeeper query and disabling of fields.
905 emp_tc_info.DELETE;
906 OPEN get_tc_data (resource_id, tc_frdt, tc_todt);
907
908 LOOP
909 FETCH get_tc_data INTO tc_tab_rec;
910 EXIT WHEN get_tc_data%NOTFOUND;
911 --We index the emp_tc_info table by julian date
912 p_index := TO_NUMBER (TO_CHAR (tc_tab_rec.start_time, 'J'));
913 emp_tc_info (p_index).timecard_id := tc_tab_rec.tbbid;
914 emp_tc_info (p_index).resource_id := resource_id;
915 emp_tc_info (p_index).tc_frdt := tc_tab_rec.start_time;
916 emp_tc_info (p_index).tc_todt := tc_tab_rec.stop_time;
917 END LOOP;
918 END;
919
920
921 -------------------------------------------------------------------------------
922 -- this procedure used to query mid period timecards
923 -------------------------------------------------------------------------------
924 PROCEDURE populate_query_tc_tab (
925 resource_id IN NUMBER,
926 tc_frdt IN DATE,
927 tc_todt IN DATE,
928 emp_qry_tc_info OUT NOCOPY hxc_timekeeper_utilities.emptctab
929 ) IS
930 CURSOR get_tc_data (
931 p_resource_id NUMBER,
932 p_tc_frdt DATE,
933 p_tc_todt DATE
934 ) IS
935 /*
936 select start_time,stop_time,to_date(start_time)
937 from hxc_time_building_blocks
938 where resource_id = p_resource_id
939 and scope='TIMECARD'
940 and date_to=hr_general.end_of_time
941 and to_date(start_time,'dd-mm-rrrr') >=to_date(p_tc_frdt,'dd-mm-rrrr') and
942 to_date(stop_time,'dd-mm-rrrr') <=to_date(p_tc_todt,'dd-mm-rrrr')
943 union
944 select tc_frdt,tc_todt,to_date(tc_frdt)
945 from dual
946 order by 3;
947 */
948 SELECT TO_DATE (start_time, 'dd-mm-rrrr hh24:mi:ss') start_time,
949 TO_DATE (stop_time, 'dd-mm-rrrr hh24:mi:ss') stop_time, TO_DATE (start_time, 'dd-mm-rrrr') orddt
950 FROM hxc_time_building_blocks
951 WHERE resource_id
952 /*ADVICE(922): Cursor references an external variable (use a parameter) [209] */
953 = p_resource_id
954 AND SCOPE = 'TIMECARD'
955 AND date_to = hr_general.end_of_time
956 AND TO_DATE (start_time, 'dd-mm-rrrr') >= TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
957 AND TO_DATE (stop_time, 'dd-mm-rrrr') <= TO_DATE (p_tc_todt, 'dd-mm-rrrr')
958 UNION
959 SELECT TO_DATE (tc_frdt
960 /*ADVICE(930): Cursor references an external variable (use a parameter) [209] */
961 , 'dd-mm-rrrr hh24:mi:ss'), TO_DATE (tc_todt
962 /*ADVICE(932): Cursor references an external variable (use a parameter) [209] */
963 , 'dd-mm-rrrr hh24:mi:ss'),
964 TO_DATE (tc_frdt
965 /*ADVICE(935): Cursor references an external variable (use a parameter) [209] */
966 , 'dd-mm-rrrr')
967 FROM DUAL
968 ORDER BY 3;
969
970 tc_tab_rec get_tc_data%ROWTYPE;
971 p_index NUMBER
972 /*ADVICE(942): NUMBER has no precision [315] */
973 := 0;
974 BEGIN
975 ---This procedure is used to get the timecards which are exactly between the range selected
976 ---used in timekeeper query and disabling of fields.
977 -- if this table contains more than 1 row means in that period person is
978 -- mid period case.
979 emp_qry_tc_info.DELETE;
980 OPEN get_tc_data (resource_id, tc_frdt, tc_todt);
981
982 LOOP
983 FETCH get_tc_data INTO tc_tab_rec;
984 EXIT WHEN get_tc_data%NOTFOUND;
985 p_index := p_index + 1;
986 --p_index:=to_number(to_char(tc_tab_rec.start_time,'J'));
987 emp_qry_tc_info (p_index).resource_id := resource_id;
988 emp_qry_tc_info (p_index).tc_frdt := tc_tab_rec.start_time;
989 emp_qry_tc_info (p_index).tc_todt := tc_tab_rec.stop_time;
990 END LOOP;
991 END;
992
993
994 -------------------------------------------------------------------------------
995 -- this procedure gives split of timecards
996 -- Used in save procedure to break the timecard
997 --when monthly timecard is
998 -------------------------------------------------------------------------------
999 PROCEDURE split_timecard (
1000 p_resource_id IN NUMBER,
1001 p_start_date IN DATE,
1002 p_end_date IN DATE,
1003 p_spemp_tc_info IN hxc_timekeeper_utilities.emptctab,
1004 p_tc_list OUT NOCOPY hxc_timecard_utilities.periods
1005 ) IS
1006 m_periods
1007 /*ADVICE(977): Unreferenced variable [553] */
1008 VARCHAR2 (2000);
1009 /*ADVICE(979): VARCHAR2 declaration with length greater than 500 characters [307] */
1010
1011 newtab hxc_timecard_utilities.periods;
1012 emp_tab_index NUMBER
1013 /*ADVICE(983): NUMBER has no precision [315] */
1014 := 0;
1015 new_tab_index
1016 /*ADVICE(986): Unreferenced variable [553] */
1017 NUMBER
1018 /*ADVICE(988): NUMBER has no precision [315] */
1019 := 0;
1020 l_emp_negpref VARCHAR2 (150);
1021 l_emp_recpref NUMBER
1022 /*ADVICE(992): NUMBER has no precision [315] */
1023 ;
1024 l_emp_appstyle NUMBER
1025 /*ADVICE(995): NUMBER has no precision [315] */
1026 ;
1027 l_emp_layout1 NUMBER
1028 /*ADVICE(998): NUMBER has no precision [315] */
1029 ;
1030 l_emp_layout2 NUMBER
1031 /*ADVICE(1001): NUMBER has no precision [315] */
1032 ;
1033 l_emp_layout3 NUMBER
1034 /*ADVICE(1004): NUMBER has no precision [315] */
1035 ;
1036 l_emp_layout4 NUMBER
1037 /*ADVICE(1007): NUMBER has no precision [315] */
1038 ;
1039 l_emp_layout5 NUMBER
1040 /*ADVICE(1010): NUMBER has no precision [315] */
1041 ;
1042 l_emp_layout6 NUMBER
1043 /*ADVICE(1013): NUMBER has no precision [315] */
1044 ;
1045 l_emp_layout7 NUMBER
1046 /*ADVICE(1016): NUMBER has no precision [315] */
1047 ;
1048 l_emp_layout8 NUMBER
1049 /*ADVICE(1019): NUMBER has no precision [315] */
1050 ;
1051 l_emp_edits VARCHAR2 (150);
1052 l_pastdt VARCHAR2 (30);
1053 l_futuredt VARCHAR2 (30);
1054 l_emp_start_date DATE;
1055 l_emp_terminate_date DATE;
1056 l_audit_enabled VARCHAR2 (150);
1057 BEGIN
1058 g_debug :=hr_utility.debug_enabled;
1059 newtab.DELETE;
1060 if g_debug then
1061 hr_utility.trace('p_resource_id '||p_resource_id);
1062 hr_utility.trace('p_end_date'||to_date(p_end_date,'dd-mm-rrrr'));
1063 hr_utility.trace('start '||to_date(p_start_date,'dd-mm-rrrr'));
1064 end if;
1065 l_emp_negpref := NULL;
1066 l_emp_recpref := NULL;
1067 l_emp_appstyle := NULL;
1068 l_emp_layout1 := NULL;
1069 l_emp_layout2 := NULL;
1070 l_emp_layout3 := NULL;
1071 l_emp_layout4 := NULL;
1072 l_emp_layout5 := NULL;
1073 l_emp_layout6 := NULL;
1074 l_emp_layout7 := NULL;
1075 l_emp_layout8 := NULL;
1076 l_emp_edits := NULL;
1077 hxc_timekeeper_utilities.get_emp_pref (
1078 p_resource_id,
1079 l_emp_negpref,
1080 l_emp_recpref,
1081 l_emp_appstyle,
1082 l_emp_layout1,
1083 l_emp_layout2,
1084 l_emp_layout3,
1085 l_emp_layout4,
1086 l_emp_layout5,
1087 l_emp_layout6,
1088 l_emp_layout7,
1089 l_emp_layout8,
1090 l_emp_edits,
1091 l_pastdt,
1092 l_futuredt,
1093 l_emp_start_date,
1094 l_emp_terminate_date,
1095 l_audit_enabled
1096 );
1097
1098 if g_debug then
1099 hr_utility.trace(' just checking cout'||p_spemp_tc_info.count);
1100 end if;
1101 IF p_spemp_tc_info.COUNT > 0 THEN --this means the person is a mid period change
1102 --and we need to split the timecard.
1103 if g_debug then
1104 hr_utility.trace('get the periods');
1105 end if;
1106
1107 get_resource_time_periods (
1108 p_resource_id => p_resource_id,
1109 p_resource_type => 'PERSON',
1110 p_current_date => SYSDATE,
1111 p_max_date_in_futur => TO_DATE (p_end_date, 'dd-mm-rrrr') + 1,
1112 p_max_date_in_past => TO_DATE (p_start_date, 'dd-mm-rrrr') - 1,
1113 p_recurring_period_id => l_emp_recpref,
1114 p_check_assignment => TRUE,
1115 p_periodtab => newtab
1116 );
1117 --New tab contains the timecard periods in that range through which
1118 --we loop and save the timecard
1119 if g_debug then
1120 hr_utility.trace('newtab.count'||newtab.count);
1121 end if;
1122 emp_tab_index := p_spemp_tc_info.FIRST;
1123
1124 LOOP
1125 EXIT WHEN NOT p_spemp_tc_info.EXISTS (emp_tab_index);
1126
1127 if g_debug then
1128 hr_utility.trace(emp_tab_index);
1129 end if;
1130 IF TO_NUMBER (emp_tab_index) < TO_NUMBER (TO_CHAR (TO_DATE (p_start_date, 'dd-mm-rrrr'), 'J'))
1131 OR TO_NUMBER (emp_tab_index) > TO_NUMBER (TO_CHAR (TO_DATE (p_end_date, 'dd-mm-rrrr'), 'J')) THEN
1132 --remove the rows from the pl/sql table table which are out of range
1133 --incomplete timecard
1134 IF newtab.EXISTS (emp_tab_index) THEN
1135 newtab.DELETE (emp_tab_index);
1136 if g_debug then
1137 hr_utility.trace('in delete emptab ');
1138 end if;
1139 END IF;
1140 END IF;
1141
1142 emp_tab_index := p_spemp_tc_info.NEXT (emp_tab_index);
1143 END LOOP;
1144 ELSE
1145 IF TRUNC (p_start_date) < l_emp_start_date THEN -- Bug 13489321
1146 if g_debug then
1147 hr_utility.trace('mid hire employee1 ');
1148 hr_utility.trace('p_start_date'|| l_emp_start_date);
1149 end if;
1150 newtab (TO_NUMBER (TO_CHAR (l_emp_start_date, 'J'))).start_date := l_emp_start_date; --Bug 13489321
1151 /* changes done by senthil for emp terminate enhancement*/
1152 IF TRUNC (p_end_date) > nvl(l_emp_terminate_date,p_end_date) THEN --Bug 13489321
1153 if g_debug then
1154 hr_utility.trace('Terminated employee ');
1155 hr_utility.trace('p_end_date'|| nvl(l_emp_terminate_date,p_end_date));
1156 end if;
1157 newtab (TO_NUMBER (TO_CHAR (l_emp_start_date, 'J'))).end_date := nvl(l_emp_terminate_date,p_end_date); --Bug 13489321
1158 else
1159 if g_debug then
1160 hr_utility.trace(' Normal employee ');
1161 hr_utility.trace('p_end_date'|| p_end_date);
1162 end if;
1163 newtab (TO_NUMBER (TO_CHAR (l_emp_start_date, 'J'))).end_date := p_end_date; --Bug 13489321
1164 end if;
1165 /* end of changes made by senthil */
1166 ELSE
1167 if g_debug then
1168 hr_utility.trace(' Normal employee ');
1169 hr_utility.trace('p_start_date'|| p_start_date);
1170 end if;
1171 newtab (TO_NUMBER (TO_CHAR (p_start_date, 'J'))).start_date := p_start_date; --Bug 13489321
1172 /* changes done by senthil for emp terminate enhancement*/
1173 IF TRUNC (p_end_date) > nvl(l_emp_terminate_date,p_end_date) THEN --Bug 13489321
1174 if g_debug then
1175 hr_utility.trace('Terminated employee ');
1176 hr_utility.trace('p_end_date'|| nvl(l_emp_terminate_date,p_end_date));
1177 end if;
1178 newtab (TO_NUMBER (TO_CHAR (p_start_date, 'J'))).end_date := nvl(l_emp_terminate_date,p_end_date);
1179 else
1180 if g_debug then
1181 hr_utility.trace(' Normal employee ');
1182 hr_utility.trace('p_end_date'|| p_end_date);
1183 end if;
1184 newtab (TO_NUMBER (TO_CHAR (p_start_date, 'J'))).end_date := p_end_date;
1185 end if;
1186 /* end of changes made by senthil */
1187 end if;
1188
1189 END IF;
1190 if g_debug then
1191 hr_utility.trace('newtab.count'||newtab.count);
1192 end if;
1193 p_tc_list := newtab;
1194
1195 if g_debug then
1196 hr_utility.trace('p_tc_list.count'||p_tc_list.count);
1197 end if;
1198 END;
1199
1200
1201 ----------------------------------------------------------------------------
1202 -- add_resource_to_perftab is used to popluate the global pl/sql resource
1203 -- preference table
1204 ----------------------------------------------------------------------------
1205 PROCEDURE add_resource_to_perftab (
1206 p_resource_id IN NUMBER,
1207 p_pref_code IN VARCHAR2,
1208 p_attribute1 IN VARCHAR2,
1209 p_attribute2 IN VARCHAR2,
1210 p_attribute3 IN VARCHAR2,
1211 p_attribute4 IN VARCHAR2,
1212 p_attribute5 IN VARCHAR2,
1213 p_attribute6 IN VARCHAR2,
1214 p_attribute7 IN VARCHAR2,
1215 p_attribute8 IN VARCHAR2,
1216 p_attribute11 IN VARCHAR2
1217 ) IS
1218 BEGIN
1219 IF (p_pref_code = 'TC_W_ALW_NEG_TIME') THEN
1220 g_resource_perftab (p_resource_id).res_negentry := p_attribute1;
1221 ELSIF (p_pref_code = 'TC_W_TCRD_PERIOD') THEN
1222 g_resource_perftab (p_resource_id).res_recperiod := p_attribute1;
1223 ELSIF (p_pref_code = 'TC_W_TCRD_LAYOUT') THEN
1224 g_resource_perftab (p_resource_id).res_layout1 := p_attribute1;
1225 g_resource_perftab (p_resource_id).res_layout2 := p_attribute2;
1226 g_resource_perftab (p_resource_id).res_layout3 := p_attribute3;
1227 g_resource_perftab (p_resource_id).res_layout4 := p_attribute4;
1228 g_resource_perftab (p_resource_id).res_layout5 := p_attribute5; --CTK
1229 g_resource_perftab (p_resource_id).res_layout6 := p_attribute6; --CHECK OUT
1230 g_resource_perftab (p_resource_id).res_layout7 := p_attribute7;
1231 g_resource_perftab (p_resource_id).res_layout8 := p_attribute8;
1232 ELSIF (p_pref_code = 'TS_PER_APPROVAL_STYLE') THEN
1233 g_resource_perftab (p_resource_id).res_appstyle := p_attribute1;
1234 ELSIF (p_pref_code = 'TS_PER_AUDIT_REQUIREMENTS') THEN
1235 g_resource_perftab (p_resource_id).res_audit_enabled := p_attribute1;
1236 ELSIF (p_pref_code = 'TC_W_TCRD_ST_ALW_EDITS') THEN
1237 g_resource_perftab (p_resource_id).res_edits := p_attribute1;
1238
1239 IF p_attribute6 IS NULL THEN
1240 g_resource_perftab (p_resource_id).res_past_date := '0001/01/01';
1241 ELSE
1242 g_resource_perftab (p_resource_id).res_past_date :=
1243 TO_CHAR ((SYSDATE - TO_NUMBER (p_attribute6)), 'YYYY/MM/DD');
1244 END IF;
1245
1246 IF p_attribute11 IS NULL THEN
1247 g_resource_perftab (p_resource_id).res_future_date := '4712/12/31';
1248 ELSE
1249 g_resource_perftab (p_resource_id).res_future_date :=
1250 TO_CHAR ((SYSDATE + TO_NUMBER (p_attribute11)), 'YYYY/MM/DD');
1251 END IF;
1252 END IF;
1253 END;
1254
1255
1256 ----------------------------------------------------------------------------
1257 -- Called from timekeeper process to get the preference
1258 -- associated with a resource
1259 -- instead of calling preference evaluation cache the info.
1260 ----------------------------------------------------------------------------
1261 PROCEDURE get_emp_pref (
1262 p_resource_id IN NUMBER,
1263 neg_pref OUT NOCOPY VARCHAR2,
1264 recpref OUT NOCOPY NUMBER,
1265 appstyle OUT NOCOPY NUMBER,
1266 layout1 OUT NOCOPY NUMBER,
1267 layout2 OUT NOCOPY NUMBER,
1268 layout3 OUT NOCOPY NUMBER,
1269 layout4 OUT NOCOPY NUMBER,
1270 layout5 OUT NOCOPY NUMBER,
1271 layout6 OUT NOCOPY NUMBER,
1272 layout7 OUT NOCOPY NUMBER,
1273 layout8 OUT NOCOPY NUMBER,
1274 edits OUT NOCOPY VARCHAR2,
1275 l_pastdate OUT NOCOPY VARCHAR2,
1276 l_futuredate OUT NOCOPY VARCHAR2,
1277 l_emp_start_date OUT NOCOPY DATE,
1278 l_emp_terminate_date OUT NOCOPY DATE,
1279 l_audit_enabled OUT NOCOPY VARCHAR2
1280 ) IS
1281 l_index NUMBER
1282 /*ADVICE(1197): NUMBER has no precision [315] */
1283 := 0;
1284 l_pref_table hxc_preference_evaluation.t_pref_table;
1285
1286
1287 /*
1288 CURSOR c_emp_hireinfo (
1289 p_resource_id
1290 /*ADVICE(1203): This definition hides another one [556] */
1291 /* NUMBER
1292 ) IS
1293 select max(date_start) from
1294 per_periods_of_service
1295 where person_id = p_resource_id; */
1296
1297
1298
1299 -- Bug 7454062
1300 --
1301 -- Rewrote the above cursor as below.
1302 -- Changes :
1303 -- 1. Added a UNION with per_periods_of_placement, cos we need
1304 -- CWK information also.
1305 -- 2. Added two more input values, the timecard start and stop dates
1306 -- because we want to check which is the hire date which suits the
1307 -- given timecard.
1308 -- Example provided below before call to this cursor.
1309 -- 3. Coalesce function would pick up which ever is not NULL in the
1310 -- order specified. We dont want the AND clause to fail when
1311 -- the period of service is still active ( it has FPD and ATD Null in
1312 -- case of an active period of service ).
1313 -- 4. The TC start and stop dates are picked up from the timekeeper profiles
1314 -- and they take care of the below scenarios.
1315 --
1316 --
1317 --
1318 -- A-------------------A
1319 -- * T-------T
1320 -- * T---------T
1321 -- * T---------T
1322 --
1323 -- A -- Assigment
1324 -- T -- Timecard.
1325
1326
1327
1328 CURSOR c_emp_hireinfo ( p_resource_id NUMBER,
1329 p_tc_start_date DATE,
1330 p_tc_end_date DATE )
1331 IS SELECT date_start
1332 FROM per_periods_of_service
1333 WHERE person_id = p_resource_id
1334 AND date_start <= p_tc_end_date
1335 AND COALESCE(final_process_date,actual_termination_date,
1336 hr_general.end_of_time) >= p_tc_start_date
1337 UNION
1338 SELECT date_start
1339 FROM per_periods_of_placement
1340 WHERE person_id = p_resource_id
1341 AND date_start <= p_tc_end_date
1342 AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)),actual_termination_date,
1343 hr_general.end_of_time) >= p_tc_start_date
1344 ORDER BY date_start ;
1345
1346
1347 /*Cursor Modified By Mithun for CWK Terminate Bug*/
1348 /* changes done by senthil for emp terminate enhancement*/
1349 /* CURSOR c_emp_terminateinfo(
1350 p_resource_id NUMBER
1351 ) IS
1352 SELECT final_process_date, date_start
1353 FROM per_periods_of_service
1354 WHERE person_id = p_resource_id
1355 union all
1356 SELECT (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
1357 FROM PER_PERIODS_OF_placement
1358 WHERE person_id = p_resource_id
1359 ORDER BY date_start DESC;
1360 */
1361
1362
1363
1364 -- Bug 7454062
1365 --
1366 -- Rewrote the above cursor as below
1367 -- Changes :
1368 -- 1. Added tc start and stop input values to pick up
1369 -- the correct period of service suitable for
1370 -- the selected timecard range.
1371 --
1372
1373 CURSOR c_emp_terminateinfo( p_resource_id NUMBER,
1374 p_tc_start_date DATE,
1375 p_tc_end_date DATE )
1376 IS SELECT final_process_date,
1377 date_start
1378 FROM per_periods_of_service
1379 WHERE person_id = p_resource_id
1380 AND date_start <= p_tc_end_date
1381 AND COALESCE(final_process_date,actual_termination_date,
1382 hr_general.end_of_time) >= p_tc_start_date
1383 UNION
1384 SELECT (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date,
1385 date_start
1386 FROM PER_PERIODS_OF_placement
1387 WHERE person_id = p_resource_id
1388 AND date_start <= p_tc_end_date
1389 AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)),actual_termination_date,
1390 hr_general.end_of_time) >= p_tc_start_date
1391 ORDER BY date_start DESC;
1392
1393
1394
1395
1396
1397 --Added By Mithun for CWK Terminate Bug
1398 l_date_start DATE;
1399 /* end of changes made by senthil */
1400
1401
1402 -- Bug 7454062
1403 l_tc_start_date DATE;
1404 l_tc_end_date DATE;
1405
1406
1407 BEGIN
1408
1409
1410 -- Bug 7454062
1411
1412 l_tc_start_date := FND_PROFILE.VALUE('OTL_TK_START_DATE');
1413 l_tc_end_date := FND_PROFILE.VALUE('OTL_TK_END_DATE');
1414
1415 IF p_resource_id IS NOT NULL THEN
1416 IF NOT g_resource_perftab.EXISTS (p_resource_id) THEN
1417 hxc_preference_evaluation.resource_preferences (
1418 p_resource_id,
1419 'TC_W_TCRD_LAYOUT,TS_PER_APPROVAL_STYLE,TC_W_ALW_NEG_TIME,TC_W_TCRD_PERIOD,TC_W_TCRD_ST_ALW_EDITS,TS_PER_AUDIT_REQUIREMENTS',
1420 l_pref_table
1421 );
1422 l_index := l_pref_table.FIRST;
1423
1424 LOOP
1425 EXIT WHEN (NOT l_pref_table.EXISTS (l_index));
1426 add_resource_to_perftab (
1427 p_resource_id => p_resource_id,
1428 p_pref_code => l_pref_table (l_index).preference_code,
1429 p_attribute1 => l_pref_table (l_index).attribute1,
1430 p_attribute2 => l_pref_table (l_index).attribute2,
1431 p_attribute3 => l_pref_table (l_index).attribute3,
1432 p_attribute4 => l_pref_table (l_index).attribute4,
1433 p_attribute5 => l_pref_table (l_index).attribute5,
1434 p_attribute6 => l_pref_table (l_index).attribute6,
1435 p_attribute7 => l_pref_table (l_index).attribute7,
1436 p_attribute8 => l_pref_table (l_index).attribute8,
1437 p_attribute11 => l_pref_table (l_index).attribute11
1438 );
1439 l_index := l_pref_table.NEXT (l_index);
1440 END LOOP;
1441
1442 -- Bug 7454062
1443
1444 -- Moved the below code to outside the IF statement, as we want it to get calculated
1445 -- each time, a new date range may be specified for a second call.
1446
1447 /*
1448 OPEN c_emp_hireinfo (p_resource_id => p_resource_id);
1449 FETCH c_emp_hireinfo INTO g_resource_perftab (p_resource_id).res_emp_start_date;
1450 CLOSE c_emp_hireinfo;
1451 /* changes done by senthil for emp terminate enhancement
1452 OPEN c_emp_terminateinfo (p_resource_id => p_resource_id);
1453 /*Changed By Mithun for CWK Terminate Bug
1454 FETCH c_emp_terminateinfo INTO g_resource_perftab (p_resource_id).res_emp_terminate_date, l_date_start;
1455 CLOSE c_emp_terminateinfo;
1456 /* end of changes */
1457
1458
1459 END IF;
1460
1461
1462 -- Bug 7454062
1463 --
1464 -- Added start and stop dates of the timecards to the below cursors to have
1465 -- multiple periods of active assignment.
1466 -- For ex: First active assignment 1-Jan-2007 - 31-Dec-2007
1467 -- Second active assignment 1-Jun-2008 - end of time.
1468 -- Without the date inputs, the query for hire date would always return
1469 -- 1-Jun-2008, hence no timecard can be entered in the first period.
1470 -- Similarly, without the date inputs, the terminate query would always
1471 -- return the final process date of the last active assignment, which is
1472 -- end of time in this case. But for a timecard in 2007, we need termination
1473 -- date to be 31-Dec-2007.
1474
1475
1476 OPEN c_emp_hireinfo (p_resource_id => p_resource_id,
1477 p_tc_start_date => l_tc_start_date,
1478 p_tc_end_date => l_tc_end_date );
1479
1480 FETCH c_emp_hireinfo INTO g_resource_perftab (p_resource_id).res_emp_start_date;
1481 CLOSE c_emp_hireinfo;
1482
1483
1484 OPEN c_emp_terminateinfo (p_resource_id => p_resource_id,
1485 p_tc_start_date => l_tc_start_date,
1486 p_tc_end_date => l_tc_end_date );
1487
1488 FETCH c_emp_terminateinfo INTO g_resource_perftab (p_resource_id).res_emp_terminate_date, l_date_start;
1489 CLOSE c_emp_terminateinfo;
1490
1491
1492
1493 neg_pref := g_resource_perftab (p_resource_id).res_negentry;
1494 recpref := TO_NUMBER (g_resource_perftab (p_resource_id).res_recperiod);
1495 appstyle := TO_NUMBER (g_resource_perftab (p_resource_id).res_appstyle);
1496 layout1 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout1);
1497 layout2 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout2);
1498 layout3 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout3);
1499 layout4 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout4);
1500 layout5 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout5);
1501 layout6 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout6);
1502 layout7 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout7);
1503 layout8 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout8);
1504 edits := g_resource_perftab (p_resource_id).res_edits;
1505 l_pastdate := g_resource_perftab (p_resource_id).res_past_date;
1506 l_futuredate := g_resource_perftab (p_resource_id).res_future_date;
1507 l_emp_start_date := g_resource_perftab (p_resource_id).res_emp_start_date;
1508 l_emp_terminate_date := g_resource_perftab (p_resource_id).res_emp_terminate_date;
1509 l_audit_enabled := g_resource_perftab (p_resource_id).res_audit_enabled;
1510 END IF;
1511
1512 END;
1513
1514
1515 ----------------------------------------------------------------------------
1516 -- get_resource_time_periods return the list of period for
1517 -- a range of time
1518 -- The p_check_assignment is not used for the moment.
1519 ----------------------------------------------------------------------------
1520 PROCEDURE get_resource_time_periods (
1521 p_resource_id IN VARCHAR2,
1522 p_resource_type IN VARCHAR2,
1523 p_current_date IN DATE,
1524 p_max_date_in_futur IN DATE,
1525 p_max_date_in_past IN DATE,
1526 p_recurring_period_id IN NUMBER,
1527 p_check_assignment IN BOOLEAN,
1528 p_periodtab IN OUT NOCOPY hxc_timecard_utilities.periods
1529 ) IS
1530 CURSOR c_timecards (
1531 p_resource_id
1532 /*ADVICE(1285): This definition hides another one [556] */
1533 IN NUMBER,
1534 p_resource_type
1535 /*ADVICE(1288): This definition hides another one [556] */
1536 IN VARCHAR2,
1537 p_first_start_date IN DATE,
1538 p_last_end_date IN DATE
1539 ) IS
1540 SELECT TRUNC (start_time), TRUNC (stop_time)
1541 FROM hxc_time_building_blocks
1542 WHERE SCOPE = 'TIMECARD'
1543 AND date_to = hr_general.end_of_time
1544 AND resource_id = p_resource_id
1545 AND resource_type = p_resource_type
1546 AND stop_time >= p_first_start_date
1547 AND start_time <= p_last_end_date;
1548
1549
1550 --ORDER BY START_TIME;
1551 CURSOR c_period_info (
1552 p_recurring_period_id
1553 /*ADVICE(1306): This definition hides another one [556] */
1554 NUMBER
1555 ) IS
1556 SELECT hrp.period_type, hrp.duration_in_days, hrp.start_date
1557 FROM hxc_recurring_periods hrp
1558 WHERE hrp.recurring_period_id = p_recurring_period_id;
1559
1560 /*
1561 CURSOR c_emp_hireinfo (
1562 p_resource_id
1563 /*ADVICE(1315): This definition hides another one [556] */
1564 /* NUMBER
1565 ) IS
1566 select max(date_start) from
1567 per_periods_of_service
1568 where person_id = p_resource_id;
1569 /*SELECT MIN (effective_start_date)
1570 FROM per_people_f
1571 WHERE person_id = p_resource_id;*/
1572
1573
1574 -- Bug 7454062
1575 --
1576 -- Rewrote the above cursor as below.
1577 -- We need the first hire date or the first period of service start date
1578 -- which falls within the given max date in past and max date in future
1579 -- rather than the last assignment's start date. The above cursor
1580 -- would work only for the most recent assignment.
1581 -- COALESCE would pick up the first not NULL value amongst the ones
1582 -- given in bracket.
1583
1584
1585 CURSOR c_emp_hireinfo ( p_resource_id NUMBER,
1586 p_past_date DATE )
1587 IS SELECT date_start
1588 FROM per_periods_of_service
1589 WHERE person_id = p_resource_id
1590 AND COALESCE(final_process_date,actual_termination_date,
1591 hr_general.end_of_time) > p_past_date
1592 UNION
1593 SELECT date_start
1594 FROM per_periods_of_placement
1595 WHERE person_id = p_resource_id
1596 AND COALESCE(final_process_date,actual_termination_date,
1597 hr_general.end_of_time) > p_past_date
1598 ORDER BY date_start ;
1599
1600
1601
1602 /*Cursor Modified By Mithun for CWK Terminate Bug*/
1603 /* changes done by senthil for emp terminate enhancement*/
1604 CURSOR c_emp_terminateinfo(
1605 p_resource_id NUMBER
1606 ) IS
1607 SELECT final_process_date, date_start
1608 FROM per_periods_of_service
1609 WHERE person_id = p_resource_id
1610 union all
1611 select (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
1612 from per_periods_of_placement
1613 where person_id = p_resource_id
1614 ORDER BY date_start DESC;
1615
1616
1617 -- Bug 7454062
1618 --
1619 -- Added a cursor below to pick up the time periods for which the
1620 -- employee is inactive. From c_emp_hireinfo cursor, we pick up
1621 -- the earliest hire date for the person, and generate the time periods
1622 -- for this start date. The below cursor is used to trim off the
1623 -- inactive periods from the list of time periods.
1624 -- LEAD picks up the next records date_start, and returns a NULL for the
1625 -- last record retrieved. COALESCE picks up the first non NULL value from
1626 -- the ones given in bracket.
1627
1628
1629 CURSOR c_emp_inactive ( p_resource_id NUMBER )
1630 IS SELECT final_process_date start_date,
1631 NVL(LEAD(date_start) OVER (ORDER BY date_start)-1,hr_general.end_of_time) end_date
1632 FROM ( SELECT date_start,
1633 COALESCE(final_process_date+1,actual_termination_date+1,hr_general.end_of_time)
1634 final_process_date
1635 FROM per_periods_of_service
1636 WHERE person_id = p_resource_id
1637 UNION
1638 SELECT date_start,
1639 COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0))+1,actual_termination_date+1,hr_general.end_of_time)
1640 final_process_date
1641 FROM per_periods_of_placement
1642 WHERE person_id = p_resource_id
1643 )
1644 ORDER BY start_date;
1645
1646
1647 --Added By Mithun for CWK Terminate Bug
1648 l_date_start DATE;
1649
1650
1651 CURSOR c_term_timecards_exists(
1652 p_resource_id IN NUMBER,
1653 p_terminate_date IN DATE
1654 ) IS
1655 SELECT count(*)
1656 FROM hxc_time_building_blocks
1657 WHERE SCOPE = 'TIMECARD'
1658 AND (date_to = hr_general.end_of_time or APPROVAL_STATUS='ERROR')
1659 AND resource_id = p_resource_id
1660 AND p_terminate_date between start_time and stop_time;
1661 /* end of changes made by senthil */
1662
1663
1664 -- index
1665 l_index NUMBER (15) ;
1666 l_index_timecard_tab NUMBER (15);
1667
1668 -- PL/SQL Table
1669 l_timecard_tab hxc_timecard_utilities.periods;
1670 l_temp_periods hxc_period_evaluation.period_list;
1671
1672 l_inactive_periods hxc_period_evaluation.period_list;
1673
1674 l_period_start DATE;
1675 l_period_end DATE;
1676 l_tc_period_start_date DATE;
1677 l_tc_period_end_date DATE;
1678 l_rec_period_start_date DATE;
1679 l_person_effective_start_date DATE;
1680 l_person_effective_end_date DATE;
1681 l_touch_period_in_tc BOOLEAN;
1682
1683 --l_dividend NUMBER;
1684 l_period_type hxc_recurring_periods.period_type%TYPE;
1685 l_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
1686 l_emp_max_date_in_past DATE;
1687 l_emp_max_date_in_futur DATE;
1688 l_start_date DATE;
1689 l_end_date DATE;
1690 l_first_rowindex NUMBER(15);
1691 l_last_rowindex NUMBER(15);
1692 l_term_tc_exists NUMBER(15);
1693 BEGIN
1694 g_debug :=hr_utility.debug_enabled;
1695 --
1696 -- look for the recurring period type
1697 --
1698 OPEN c_period_info (p_recurring_period_id => p_recurring_period_id);
1699 FETCH c_period_info INTO l_period_type, l_duration_in_days, l_rec_period_start_date;
1700 /*ADVICE(1365): FETCH into a list of variables instead of a record [204] */
1701
1702 CLOSE c_period_info;
1703
1704 IF p_check_assignment = TRUE THEN
1705 OPEN c_emp_hireinfo (p_resource_id => p_resource_id,
1706 p_past_date => p_max_date_in_past);
1707 FETCH c_emp_hireinfo INTO l_person_effective_start_date;
1708 CLOSE c_emp_hireinfo;
1709
1710 /*Changes Done By Mithun for CWK Terminate Bug*/
1711 /* changes done by senthil for emp terminate enhancement*/
1712 OPEN c_emp_terminateinfo (p_resource_id => p_resource_id);
1713 FETCH c_emp_terminateinfo INTO l_person_effective_end_date, l_date_start;
1714 CLOSE c_emp_terminateinfo;
1715 /* end of changes */
1716
1717 --2789497
1718 IF p_max_date_in_past <= NVL (l_person_effective_start_date, p_max_date_in_past) THEN
1719 l_emp_max_date_in_past := NVL (l_person_effective_start_date, p_max_date_in_past);
1720 ELSE
1721 l_emp_max_date_in_past := p_max_date_in_past;
1722 END IF;
1723 --2789497
1724 /* changes done by senthil for emp terminate enhancement*/
1725 IF g_debug THEN
1726 hr_utility.trace('terminate end_date'||NVL(l_person_effective_end_date,p_max_date_in_futur));
1727 END if;
1728 IF p_max_date_in_futur >= NVL(l_person_effective_end_date,p_max_date_in_futur) THEN
1729 l_emp_max_date_in_futur := NVL(l_person_effective_end_date,p_max_date_in_futur);
1730 ELSE
1731 l_emp_max_date_in_futur := p_max_date_in_futur;
1732 END IF;
1733 IF g_debug THEN
1734 hr_utility.trace('p_max_date_in_futur'||p_max_date_in_futur);
1735 hr_utility.trace('l_emp_max_date_in_futur'||l_emp_max_date_in_futur);
1736 END if;
1737 /* Changes done by senthil */
1738
1739 ELSE
1740 l_emp_max_date_in_past := p_max_date_in_past;
1741 l_emp_max_date_in_futur := p_max_date_in_futur;
1742 END IF;
1743
1744
1745 -- We are finding the timecard for this period and add them
1746 -- to the pl/sql table
1747 OPEN c_timecards (
1748 p_resource_id => p_resource_id,
1749 p_resource_type => p_resource_type,
1750 p_first_start_date => l_emp_max_date_in_past,
1751 p_last_end_date => l_emp_max_date_in_futur
1752 );
1753
1754 LOOP
1755 FETCH c_timecards INTO l_period_start, l_period_end;
1756 /*ADVICE(1400): FETCH into a list of variables instead of a record [204] */
1757
1758 EXIT WHEN c_timecards%NOTFOUND;
1759 -- add the timecard in the pl/sql table
1760 -- here we are indexing by date JULIAN number to order the pl/sql table.
1761 l_timecard_tab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
1762 l_timecard_tab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_period_end;
1763 END LOOP;
1764
1765 CLOSE c_timecards;
1766
1767 -- add the timecard table into the period tab
1768 --p_periodtab := l_timecard_tab;
1769 -- we are calling to get the all period.
1770 -- between the start date and end date.
1771 l_temp_periods :=
1772 hxc_period_evaluation.get_period_list (
1773 p_current_date => p_current_date,
1774 p_recurring_period_type => l_period_type,
1775 p_duration_in_days => l_duration_in_days,
1776 p_rec_period_start_date => l_rec_period_start_date,
1777 p_max_date_in_futur => l_emp_max_date_in_futur,
1778 p_max_date_in_past => l_emp_max_date_in_past
1779 );
1780
1781
1782 -- Bug 7454062
1783 --
1784 -- The below construct added to trim off the inactive periods
1785 -- from the generated periods list l_temp_periods.
1786 -- Eg. Lets say this is how the periods of service are
1787 --
1788 --
1789 -- date_start final_process_date
1790 -- 1-jan-07 30-jun-07
1791 -- 1-jan-08 30-jun-08
1792 -- 1-jan-09
1793 --
1794 --
1795 -- The below cursor would return something like this.
1796 -- start_date end_date
1797 -- 1-Jul-07 31-Dec-07
1798 -- 1-Jul-08 31-Dec-08
1799 -- end_of_time end_of_time
1800 --
1801 -- These date ranges are inactive periods and anything which falls in these date
1802 -- ranges needs to be deleted from l_temp_periods.
1803 --
1804
1805 OPEN c_emp_inactive(p_resource_id);
1806 FETCH c_emp_inactive BULK
1807 COLLECT INTO l_inactive_periods;
1808 CLOSE c_emp_inactive;
1809
1810 -- l_temp_periods as well as l_inactive_periods is accessed in sorted order
1811 -- Hence we neednt Nest the GENERATED_PERIODS loop completely. The iterator
1812 -- can start from where it left for the last iteration of INACTIVE_PERIODS
1813 l_index := l_temp_periods.FIRST;
1814
1815 <<INACTIVE_PERIODS>>
1816 FOR i IN l_inactive_periods.FIRST..l_inactive_periods.LAST
1817 LOOP
1818 -- We neednt consider this inactive period if the start_date is end_of_time
1819 -- or the start_date and end_date dont have atleast one day in between.
1820 IF (l_inactive_periods(i).start_date <> hr_general.end_of_time)
1821 AND (l_inactive_periods(i).end_date - l_inactive_periods(i).start_date > 1)
1822
1823 THEN
1824 <<GENERATED_PERIODS>>
1825 LOOP
1826 EXIT WHEN NOT l_temp_periods.EXISTS(l_index);
1827 -- If the generated period is completely less than the inactive period
1828 -- do nothing, go to the next generated period.
1829 IF l_temp_periods(l_index).end_date < l_inactive_periods(i).start_date
1830 THEN
1831 NULL;
1832 -- If the generated period is completely greater than the inactive period
1833 -- exit this looping of generated period. Check for the same generated period
1834 -- and the next inactive period.
1835 ELSIF l_inactive_periods(i).end_date < l_temp_periods(l_index).start_date
1836 THEN
1837 EXIT GENERATED_PERIODS ;
1838 -- If generated period's start_date falls between the inactive period
1839 ELSE
1840 IF (l_temp_periods(l_index).start_date BETWEEN l_inactive_periods(i).start_date
1841 AND l_inactive_periods(i).end_date)
1842 THEN
1843 -- and if the generated period's end_date falls between the inactive period
1844 -- I-------------------------------------I
1845 -- G---------G
1846 IF l_temp_periods(l_index).end_date BETWEEN l_inactive_periods(i).start_date
1847 AND l_inactive_periods(i).end_date
1848 THEN
1849 -- delete the generated period.
1850 l_temp_periods.DELETE(l_index);
1851 ELSE
1852 -- generated period's end_date falls outside the inactive period.
1853 -- I------------------------------------I
1854 -- G------------G
1855 -- Reset the start date also to outside the inactive period.
1856 -- I------------------------------------I
1857 -- G----G
1858 l_temp_periods(l_index).start_date := l_inactive_periods(i).end_date+1;
1859 END IF;
1860 -- Generated period's end_date falls in the inactive period.
1861 -- I------------------------------------I
1862 -- G----------G
1863 --
1864 ELSIF l_temp_periods(l_index).end_date BETWEEN l_inactive_periods(i).start_date
1865 AND l_inactive_periods(i).end_date
1866 THEN
1867 -- Reset the end_date to outside the inactive period.
1868 -- I------------------------------------I
1869 -- G----G
1870 l_temp_periods(l_index).end_date := l_inactive_periods(i).start_date-1;
1871 END IF;
1872 END IF;
1873 l_index := l_temp_periods.NEXT(l_index);
1874 END LOOP GENERATED_PERIODS ;
1875 END IF;
1876 END LOOP INACTIVE_PERIODS ;
1877
1878 -- Bug 7454062
1879
1880
1881
1882
1883
1884
1885 -- add them to the pl/sql table
1886 -- we are now populating the periodtab pl/sql table
1887 -- and we are working out on the rows already in the table
1888 l_index := l_temp_periods.FIRST;
1889
1890 LOOP
1891 EXIT WHEN NOT l_temp_periods.EXISTS (l_index);
1892 if g_debug then
1893
1894 hr_utility.trace('###################');
1895 hr_utility.trace('#### NEW PERIOD #########');
1896 hr_utility.trace('###################');
1897 end if;
1898 l_period_start := l_temp_periods (l_index).start_date;
1899 l_period_end := l_temp_periods (l_index).end_date;
1900 -- help to know if during the process the period
1901 -- has been modified.
1902 l_touch_period_in_tc := FALSE;
1903 -- before to add the period we need to look if there is a tc period
1904 -- already into this table
1905 l_index_timecard_tab := l_timecard_tab.FIRST;
1906
1907 LOOP
1908 EXIT WHEN NOT l_timecard_tab.EXISTS (l_index_timecard_tab);
1909 l_tc_period_start_date := l_timecard_tab (l_index_timecard_tab).start_date;
1910 l_tc_period_end_date := l_timecard_tab (l_index_timecard_tab).end_date;
1911 if g_debug then
1912 hr_utility.trace('####NEW TIMECARD');
1913 hr_utility.trace('l_period_start :'||l_period_start);
1914 hr_utility.trace('l_period_end :'||l_period_end);
1915 hr_utility.trace('l_tc_period_start_date :'||to_char(l_tc_period_start_date,'DD-MON-YYYY'));
1916 hr_utility.trace('l_tc_period_end_date :'||to_char(l_tc_period_end_date,'DD-MON-YYYY'));
1917 hr_utility.trace('###################');
1918 end if;
1919 IF TRUNC (l_tc_period_start_date) > TRUNC (l_period_start)
1920 AND TRUNC (l_tc_period_end_date) < TRUNC (l_period_end) THEN
1921 -- the timecard is in the middle of a period
1922 -- we are splitting the period in 2 + the timecard which
1923 -- is already there.
1924 -- before the timecard
1925 if g_debug then
1926 hr_utility.trace('case 1 :');
1927 end if;
1928 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
1929 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_tc_period_start_date - 1;
1930 if g_debug then
1931 hr_utility.trace('l_tab_period_start_date :'||to_char(l_period_start,'DD-MON-YYYY'));
1932 hr_utility.trace('l_tab_period_end_date :'||to_char(l_tc_period_start_date - 1,'DD-MON-YYYY'));
1933 end if;
1934 -- after the timecard
1935 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).start_date :=
1936 l_tc_period_end_date + 1;
1937 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).end_date := l_period_end;
1938 -- now we are overritting the start period
1939 l_period_start := l_tc_period_end_date + 1;
1940 if g_debug then
1941 hr_utility.trace('l_tab_period_start_date :'||to_char(l_tc_period_end_date + 1,'DD-MON-YYYY'));
1942 hr_utility.trace('l_tab_period_end_date :'||to_char(l_period_end,'DD-MON-YYYY'));
1943 end if;
1944 l_touch_period_in_tc := TRUE;
1945 ELSIF TRUNC (l_tc_period_start_date) < TRUNC (l_period_start)
1946 AND TRUNC (l_tc_period_end_date) > TRUNC (l_period_end) THEN
1947 -- the timecard is outside of the period so we have nothing to
1948 -- do
1949 l_touch_period_in_tc := TRUE;
1950 if g_debug then
1951 hr_utility.trace('case 2 :');
1952 end if;
1953 ELSIF TRUNC (l_tc_period_start_date) > TRUNC (l_period_start)
1954 AND TRUNC (l_tc_period_start_date) <= TRUNC (l_period_end)
1955 AND TRUNC (l_tc_period_end_date) >= TRUNC (l_period_end) THEN
1956 if g_debug then
1957 hr_utility.trace('case 3 :');
1958 end if;
1959 -- we are splitting the period in 2
1960 -- we are adding the period just before the timecard
1961 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
1962 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_tc_period_start_date - 1;
1963 -- in this case we can exit of the loop;
1964 l_index_timecard_tab := l_timecard_tab.LAST;
1965 l_touch_period_in_tc := TRUE;
1966 if g_debug then
1967 hr_utility.trace('l_tab_period_start_date :'||to_char(l_period_start,'DD-MON-YYYY'));
1968 hr_utility.trace('l_tab_period_end_date :'||to_char(l_tc_period_start_date - 1,'DD-MON-YYYY'));
1969 end if;
1970 ELSIF TRUNC (l_tc_period_start_date) <= TRUNC (l_period_start)
1971 AND TRUNC (l_tc_period_end_date) >= TRUNC (l_period_start)
1972 AND TRUNC (l_tc_period_end_date) < TRUNC (l_period_end) THEN
1973 if g_debug then
1974 hr_utility.trace('case 4 :');
1975 end if;
1976 -- we are splitting the period in 2
1977 -- we are adding the period just before the timecard
1978 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).start_date :=
1979 l_tc_period_end_date + 1;
1980 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).end_date := l_period_end;
1981 if g_debug then
1982 hr_utility.trace('l_tab_period_start_date :'||to_char(l_tc_period_end_date + 1,'DD-MON-YYYY'));
1983 hr_utility.trace('l_tab_period_end_date :'||to_char(l_period_end,'DD-MON-YYYY'));
1984 end if;
1985 -- now we are overritting the end/start period
1986 l_period_start := l_tc_period_end_date + 1;
1987 l_touch_period_in_tc := TRUE;
1988 ELSIF ( TRUNC (l_tc_period_start_date) = TRUNC (l_period_start)
1989 AND TRUNC (l_tc_period_end_date) >= TRUNC (l_period_end)
1990 )
1991 OR ( TRUNC (l_tc_period_start_date) <= TRUNC (l_period_start)
1992 AND TRUNC (l_tc_period_end_date) = TRUNC (l_period_end)
1993 ) THEN
1994 if g_debug then
1995 hr_utility.trace('case 5 :');
1996 end if;
1997 -- we are splitting the period in 2
1998 -- we are adding the period just before the timecard
1999 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).start_date :=
2000 l_tc_period_start_date;
2001 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).end_date := l_tc_period_end_date;
2002 -- now we are overritting the end/start period
2003 l_period_start := l_tc_period_end_date + 1;
2004 l_touch_period_in_tc := TRUE;
2005 END IF;
2006
2007 -- in case of override
2008 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).start_date := l_tc_period_start_date;
2009 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).end_date := l_tc_period_end_date;
2010 l_index_timecard_tab := l_timecard_tab.NEXT (l_index_timecard_tab);
2011 END LOOP;
2012 /*ADVICE(1531): Nested LOOPs should all be labeled [406] */
2013
2014
2015 IF NOT (l_touch_period_in_tc) THEN
2016 if g_debug then
2017 hr_utility.trace('case 6 :');
2018 end if;
2019 -- we are adding the period
2020 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
2021 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_period_end;
2022 END IF;
2023
2024 if g_debug then
2025 hr_utility.trace('count'||p_periodtab.count);
2026 end if;
2027 l_index := l_temp_periods.NEXT (l_index);
2028 END LOOP;
2029
2030 /* changes done by senthil for emp terminate enhancement*/
2031 IF p_periodtab.COUNT > 0 THEN
2032 l_last_rowindex := p_periodtab.LAST;
2033 l_start_date := p_periodtab (l_last_rowindex).start_date;
2034 l_end_date := p_periodtab (l_last_rowindex).end_date;
2035 IF l_end_date > l_person_effective_end_date THEN
2036 open c_term_timecards_exists(p_resource_id,l_person_effective_end_date);
2037 fetch c_term_timecards_exists into l_term_tc_exists;
2038 close c_term_timecards_exists;
2039 if l_term_tc_exists = 0 then
2040 p_periodtab (l_last_rowindex).end_date := l_person_effective_end_date;
2041 end if;
2042 END IF;
2043 END IF;
2044 /* end of changes made by senthil */
2045
2046 IF p_periodtab.COUNT > 0 THEN
2047 l_first_rowindex := p_periodtab.FIRST;
2048 l_start_date := p_periodtab (l_first_rowindex).start_date;
2049 l_end_date := p_periodtab (l_first_rowindex).end_date;
2050
2051 IF l_person_effective_start_date < l_start_date THEN
2052 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).start_date :=
2053 l_person_effective_start_date;
2054 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).end_date := l_start_date - 1;
2055 ELSIF l_person_effective_start_date > l_start_date AND l_person_effective_start_date > l_end_date THEN --2975015
2056 p_periodtab.DELETE (l_first_rowindex);
2057 ELSIF l_person_effective_start_date > l_start_date THEN
2058 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).start_date :=
2059 l_person_effective_start_date;
2060 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).end_date :=
2061 p_periodtab (l_first_rowindex).end_date;
2062 p_periodtab.DELETE (l_first_rowindex);
2063 END IF;
2064 END IF;
2065 END get_resource_time_periods;
2066
2067 PROCEDURE tc_edit_allowed (
2068 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
2069 /*ADVICE(1568): Mode of parameter is not specified with IN parameter [521] */
2070 ,
2071 p_timecard_ovn
2072 /*ADVICE(1571): Unreferenced parameter [552] */
2073 hxc_time_building_blocks.object_version_number%TYPE
2074 /*ADVICE(1573): Mode of parameter is not specified with IN parameter [521] */
2075 ,
2076 p_timecard_status VARCHAR2
2077 /*ADVICE(1576): Mode of parameter is not specified with IN parameter [521] */
2078 ,
2079 p_edit_allowed_preference hxc_pref_hierarchies.attribute1%TYPE
2080 /*ADVICE(1579): Mode of parameter is not specified with IN parameter [521] */
2081 ,
2082 p_edit_allowed IN OUT NOCOPY VARCHAR2
2083 ) IS
2084 CURSOR csr_chk_transfer IS
2085 SELECT 1
2086 FROM DUAL
2087 WHERE EXISTS ( SELECT 1
2088 FROM hxc_transactions t, hxc_transaction_details td
2089 WHERE td.time_building_block_id = p_timecard_id
2090 /*ADVICE(1589): Cursor references an external variable (use a parameter) [209] */
2091
2092 AND t.transaction_id = td.transaction_id
2093 AND t.TYPE = 'RETRIEVAL'
2094 AND t.status = 'SUCCESS');
2095
2096 l_proc
2097 /*ADVICE(1596): Unreferenced variable [553] */
2098 VARCHAR2 (72);
2099 l_tc_status hxc_time_building_blocks.approval_status%TYPE;
2100 l_dummy NUMBER (1);
2101 BEGIN
2102 g_debug :=hr_utility.debug_enabled;
2103 if g_debug then
2104 l_proc := 'hxc_timekeeper_utilities'
2105 || 'tc_edit_allowed';
2106 hr_utility.set_location('Entering '||l_proc, 10);
2107 end if;
2108 --l_tc_status := hxc_timecard_search_pkg.get_timecard_status_code(p_timecard_id,p_Timecard_Ovn);
2109 l_tc_status := p_timecard_status;
2110
2111
2112 if g_debug then
2113 hr_utility.set_location('Processing '||l_proc, 20);
2114 end if;
2115 IF (p_edit_allowed_preference = 'NEW_WORKING_REJECTED') THEN
2116 if g_debug then
2117 hr_utility.set_location('Processing '||l_proc, 30);
2118 end if;
2119 IF ((l_tc_status = 'REJECTED') OR (l_tc_status = 'WORKING')) THEN
2120 p_edit_allowed := 'TRUE';
2121 ELSE
2122 p_edit_allowed := 'FALSE';
2123 END IF;
2124 ELSIF (p_edit_allowed_preference = 'SUBMITTED') THEN
2125 if g_debug then
2126 hr_utility.set_location('Processing '||l_proc, 40);
2127 end if;
2128 IF ((l_tc_status = 'REJECTED') OR (l_tc_status = 'WORKING') OR (l_tc_status = 'SUBMITTED')) THEN
2129 p_edit_allowed := 'TRUE';
2130 ELSE
2131 p_edit_allowed := 'FALSE';
2132 END IF;
2133 ELSIF (p_edit_allowed_preference = 'APPROVALS_INITIATED') THEN
2134 if g_debug then
2135 hr_utility.set_location('Processing '||l_proc, 50);
2136 end if;
2137 -- all we need to do here is check that this timecard
2138 -- has not been transferred successfully to any recipient
2139 -- applications
2140 OPEN csr_chk_transfer;
2141 FETCH csr_chk_transfer INTO l_dummy;
2142
2143 IF csr_chk_transfer%FOUND THEN
2144 p_edit_allowed := 'FALSE';
2145 ELSE
2146 p_edit_allowed := 'TRUE';
2147 END IF;
2148 ELSIF (p_edit_allowed_preference = 'RETRO') THEN
2149 if g_debug then
2150 hr_utility.set_location('Processing '||l_proc, 60);
2151 end if;
2152 IF ( (l_tc_status = 'REJECTED')
2153 OR (l_tc_status = 'WORKING')
2154 OR (l_tc_status = 'SUBMITTED')
2155 OR (l_tc_status = 'APPROVED')
2156 OR (l_tc_status = 'ERROR')
2157 ) THEN
2158 p_edit_allowed := 'TRUE';
2159 ELSE
2160 p_edit_allowed := 'FALSE';
2161 END IF;
2162 ELSE
2163 if g_debug then
2164 hr_utility.set_location('Processing '||l_proc, 70);
2165 end if;
2166 p_edit_allowed := 'FALSE';
2167 END IF;
2168
2169
2170 -- if the status is ERROR, we don't need to look at
2171 -- the pref -> JUST RETURN TRUE;
2172 IF (l_tc_status = 'ERROR') THEN
2173 p_edit_allowed := 'TRUE';
2174 END IF;
2175
2176 if g_debug then
2177 hr_utility.set_location('Leaving '||l_proc, 80);
2178 end if;
2179 END tc_edit_allowed;
2180
2181 PROCEDURE convert_type_to_message_table (
2182 p_old_messages IN hxc_message_table_type,
2183 p_messages OUT NOCOPY hxc_self_service_time_deposit.message_table
2184 ) IS
2185 l_index NUMBER
2186 /*ADVICE(1668): NUMBER has no precision [315] */
2187 ;
2188 l_new_index NUMBER
2189 /*ADVICE(1671): NUMBER has no precision [315] */
2190 ;
2191 BEGIN
2192 l_new_index := 1;
2193 l_index := p_old_messages.FIRST;
2194
2195 LOOP
2196 EXIT WHEN NOT p_old_messages.EXISTS (l_index);
2197 p_messages (l_new_index).message_name := p_old_messages (l_index).message_name;
2198 p_messages (l_new_index).message_level := p_old_messages (l_index).message_level;
2199 p_messages (l_new_index).message_field := p_old_messages (l_index).message_field;
2200 p_messages (l_new_index).message_tokens := p_old_messages (l_index).message_tokens;
2201 p_messages (l_new_index).application_short_name := p_old_messages (l_index).application_short_name;
2202 p_messages (l_new_index).time_building_block_id := p_old_messages (l_index).time_building_block_id;
2203 p_messages (l_new_index).time_building_block_ovn := p_old_messages (l_index).time_building_block_ovn;
2204 p_messages (l_new_index).time_attribute_id := p_old_messages (l_index).time_attribute_id;
2205 p_messages (l_new_index).time_attribute_ovn := p_old_messages (l_index).time_attribute_ovn;
2206 l_new_index := l_new_index + 1;
2207 l_index := p_old_messages.NEXT (l_index);
2208 END LOOP;
2209 END convert_type_to_message_table;
2210
2211 PROCEDURE manage_attributes (
2212 p_attribute_number IN NUMBER,
2213 p_insert_data_details IN hxc_timekeeper_process.t_time_info,
2214 p_old_value IN OUT NOCOPY VARCHAR2,
2215 p_new_value IN OUT NOCOPY VARCHAR2
2216 ) IS
2217 BEGIN
2218 IF p_attribute_number = 1 THEN
2219 p_new_value := p_insert_data_details.attr_id_1;
2220 p_old_value := p_insert_data_details.attr_oldid_1;
2221 ELSIF p_attribute_number = 2 THEN
2222 p_new_value := p_insert_data_details.attr_id_2;
2223 p_old_value := p_insert_data_details.attr_oldid_2;
2224 ELSIF p_attribute_number = 3 THEN
2225 p_new_value := p_insert_data_details.attr_id_3;
2226 p_old_value := p_insert_data_details.attr_oldid_3;
2227 ELSIF p_attribute_number = 4 THEN
2228 p_new_value := p_insert_data_details.attr_id_4;
2229 p_old_value := p_insert_data_details.attr_oldid_4;
2230 ELSIF p_attribute_number = 5 THEN
2231 p_new_value := p_insert_data_details.attr_id_5;
2232 p_old_value := p_insert_data_details.attr_oldid_5;
2233 ELSIF p_attribute_number = 6 THEN
2234 p_new_value := p_insert_data_details.attr_id_6;
2235 p_old_value := p_insert_data_details.attr_oldid_6;
2236 ELSIF p_attribute_number = 7 THEN
2237 p_new_value := p_insert_data_details.attr_id_7;
2238 p_old_value := p_insert_data_details.attr_oldid_7;
2239 ELSIF p_attribute_number = 8 THEN
2240 p_new_value := p_insert_data_details.attr_id_8;
2241 p_old_value := p_insert_data_details.attr_oldid_8;
2242 ELSIF p_attribute_number = 9 THEN
2243 p_new_value := p_insert_data_details.attr_id_9;
2244 p_old_value := p_insert_data_details.attr_oldid_9;
2245 ELSIF p_attribute_number = 10 THEN
2246 p_new_value := p_insert_data_details.attr_id_10;
2247 p_old_value := p_insert_data_details.attr_oldid_10;
2248 ELSIF p_attribute_number = 11 THEN
2249 p_new_value := p_insert_data_details.attr_id_11;
2250 p_old_value := p_insert_data_details.attr_oldid_11;
2251 ELSIF p_attribute_number = 12 THEN
2252 p_new_value := p_insert_data_details.attr_id_12;
2253 p_old_value := p_insert_data_details.attr_oldid_12;
2254 ELSIF p_attribute_number = 13 THEN
2255 p_new_value := p_insert_data_details.attr_id_13;
2256 p_old_value := p_insert_data_details.attr_oldid_13;
2257 ELSIF p_attribute_number = 14 THEN
2258 p_new_value := p_insert_data_details.attr_id_14;
2259 p_old_value := p_insert_data_details.attr_oldid_14;
2260 ELSIF p_attribute_number = 15 THEN
2261 p_new_value := p_insert_data_details.attr_id_15;
2262 p_old_value := p_insert_data_details.attr_oldid_15;
2263 ELSIF p_attribute_number = 16 THEN
2264 p_new_value := p_insert_data_details.attr_id_16;
2265 p_old_value := p_insert_data_details.attr_oldid_16;
2266 ELSIF p_attribute_number = 17 THEN
2267 p_new_value := p_insert_data_details.attr_id_17;
2268 p_old_value := p_insert_data_details.attr_oldid_17;
2269 ELSIF p_attribute_number = 18 THEN
2270 p_new_value := p_insert_data_details.attr_id_18;
2271 p_old_value := p_insert_data_details.attr_oldid_18;
2272 ELSIF p_attribute_number = 19 THEN
2273 p_new_value := p_insert_data_details.attr_id_19;
2274 p_old_value := p_insert_data_details.attr_oldid_19;
2275 ELSIF p_attribute_number = 20 THEN
2276 p_new_value := p_insert_data_details.attr_id_20;
2277 p_old_value := p_insert_data_details.attr_oldid_20;
2278 END IF;
2279 END;
2280
2281 PROCEDURE manage_timeinfo (
2282 p_day_counter IN NUMBER,
2283 p_insert_detail IN hxc_timekeeper_process.t_time_info,
2284 p_measure IN OUT NOCOPY NUMBER,
2285 p_detail_id IN OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2286 p_detail_ovn IN OUT NOCOPY NUMBER,
2287 p_detail_time_in IN OUT NOCOPY DATE,
2288 p_detail_time_out IN OUT NOCOPY DATE
2289 ) IS
2290 l_attribute_found
2291 /*ADVICE(1773): Unreferenced variable [553] */
2292 BOOLEAN;
2293 BEGIN
2294 IF p_day_counter = 0 THEN
2295 p_measure := p_insert_detail.day_1;
2296 p_detail_id := p_insert_detail.detail_id_1;
2297 p_detail_ovn := p_insert_detail.detail_ovn_1;
2298 p_detail_time_in := p_insert_detail.time_in_1;
2299 p_detail_time_out := p_insert_detail.time_out_1;
2300 ELSIF p_day_counter = 1 THEN
2301 p_measure := p_insert_detail.day_2;
2302 p_detail_id := p_insert_detail.detail_id_2;
2303 p_detail_ovn := p_insert_detail.detail_ovn_2;
2304 p_detail_time_in := p_insert_detail.time_in_2;
2305 p_detail_time_out := p_insert_detail.time_out_2;
2306 ELSIF p_day_counter = 2 THEN
2307 p_measure := p_insert_detail.day_3;
2308 p_detail_id := p_insert_detail.detail_id_3;
2309 p_detail_ovn := p_insert_detail.detail_ovn_3;
2310 p_detail_time_in := p_insert_detail.time_in_3;
2311 p_detail_time_out := p_insert_detail.time_out_3;
2312 ELSIF p_day_counter = 3 THEN
2313 p_measure := p_insert_detail.day_4;
2314 p_detail_id := p_insert_detail.detail_id_4;
2315 p_detail_ovn := p_insert_detail.detail_ovn_4;
2316 p_detail_time_in := p_insert_detail.time_in_4;
2317 p_detail_time_out := p_insert_detail.time_out_4;
2318 ELSIF p_day_counter = 4 THEN
2319 p_measure := p_insert_detail.day_5;
2320 p_detail_id := p_insert_detail.detail_id_5;
2321 p_detail_ovn := p_insert_detail.detail_ovn_5;
2322 p_detail_time_in := p_insert_detail.time_in_5;
2323 p_detail_time_out := p_insert_detail.time_out_5;
2324 ELSIF p_day_counter = 5 THEN
2325 p_measure := p_insert_detail.day_6;
2326 p_detail_id := p_insert_detail.detail_id_6;
2327 p_detail_ovn := p_insert_detail.detail_ovn_6;
2328 p_detail_time_in := p_insert_detail.time_in_6;
2329 p_detail_time_out := p_insert_detail.time_out_6;
2330 ELSIF p_day_counter = 6 THEN
2331 p_measure := p_insert_detail.day_7;
2332 p_detail_id := p_insert_detail.detail_id_7;
2333 p_detail_ovn := p_insert_detail.detail_ovn_7;
2334 p_detail_time_in := p_insert_detail.time_in_7;
2335 p_detail_time_out := p_insert_detail.time_out_7;
2336 ELSIF p_day_counter = 7 THEN
2337 p_measure := p_insert_detail.day_8;
2338 p_detail_id := p_insert_detail.detail_id_8;
2339 p_detail_ovn := p_insert_detail.detail_ovn_8;
2340 p_detail_time_in := p_insert_detail.time_in_8;
2341 p_detail_time_out := p_insert_detail.time_out_8;
2342 ELSIF p_day_counter = 8 THEN
2343 p_measure := p_insert_detail.day_9;
2344 p_detail_id := p_insert_detail.detail_id_9;
2345 p_detail_ovn := p_insert_detail.detail_ovn_9;
2346 p_detail_time_in := p_insert_detail.time_in_9;
2347 p_detail_time_out := p_insert_detail.time_out_9;
2348 ELSIF p_day_counter = 9 THEN
2349 p_measure := p_insert_detail.day_10;
2350 p_detail_id := p_insert_detail.detail_id_10;
2351 p_detail_ovn := p_insert_detail.detail_ovn_10;
2352 p_detail_time_in := p_insert_detail.time_in_10;
2353 p_detail_time_out := p_insert_detail.time_out_10;
2354 ELSIF p_day_counter = 10 THEN
2355 p_measure := p_insert_detail.day_11;
2356 p_detail_id := p_insert_detail.detail_id_11;
2357 p_detail_ovn := p_insert_detail.detail_ovn_11;
2358 p_detail_time_in := p_insert_detail.time_in_11;
2359 p_detail_time_out := p_insert_detail.time_out_11;
2360 ELSIF p_day_counter = 11 THEN
2361 p_measure := p_insert_detail.day_12;
2362 p_detail_id := p_insert_detail.detail_id_12;
2363 p_detail_ovn := p_insert_detail.detail_ovn_12;
2364 p_detail_time_in := p_insert_detail.time_in_12;
2365 p_detail_time_out := p_insert_detail.time_out_12;
2366 ELSIF p_day_counter = 12 THEN
2367 p_measure := p_insert_detail.day_13;
2368 p_detail_id := p_insert_detail.detail_id_13;
2369 p_detail_ovn := p_insert_detail.detail_ovn_13;
2370 p_detail_time_in := p_insert_detail.time_in_13;
2371 p_detail_time_out := p_insert_detail.time_out_13;
2372 ELSIF p_day_counter = 13 THEN
2373 p_measure := p_insert_detail.day_14;
2374 p_detail_id := p_insert_detail.detail_id_14;
2375 p_detail_ovn := p_insert_detail.detail_ovn_14;
2376 p_detail_time_in := p_insert_detail.time_in_14;
2377 p_detail_time_out := p_insert_detail.time_out_14;
2378 ELSIF p_day_counter = 14 THEN
2379 p_measure := p_insert_detail.day_15;
2380 p_detail_id := p_insert_detail.detail_id_15;
2381 p_detail_ovn := p_insert_detail.detail_ovn_15;
2382 p_detail_time_in := p_insert_detail.time_in_15;
2383 p_detail_time_out := p_insert_detail.time_out_15;
2384 ELSIF p_day_counter = 15 THEN
2385 p_measure := p_insert_detail.day_16;
2386 p_detail_id := p_insert_detail.detail_id_16;
2387 p_detail_ovn := p_insert_detail.detail_ovn_16;
2388 p_detail_time_in := p_insert_detail.time_in_16;
2389 p_detail_time_out := p_insert_detail.time_out_16;
2390 ELSIF p_day_counter = 16 THEN
2391 p_measure := p_insert_detail.day_17;
2392 p_detail_id := p_insert_detail.detail_id_17;
2393 p_detail_ovn := p_insert_detail.detail_ovn_17;
2394 p_detail_time_in := p_insert_detail.time_in_17;
2395 p_detail_time_out := p_insert_detail.time_out_17;
2396 ELSIF p_day_counter = 17 THEN
2397 p_measure := p_insert_detail.day_18;
2398 p_detail_id := p_insert_detail.detail_id_18;
2399 p_detail_ovn := p_insert_detail.detail_ovn_18;
2400 p_detail_time_in := p_insert_detail.time_in_18;
2401 p_detail_time_out := p_insert_detail.time_out_18;
2402 ELSIF p_day_counter = 18 THEN
2403 p_measure := p_insert_detail.day_19;
2404 p_detail_id := p_insert_detail.detail_id_19;
2405 p_detail_ovn := p_insert_detail.detail_ovn_19;
2406 p_detail_time_in := p_insert_detail.time_in_19;
2407 p_detail_time_out := p_insert_detail.time_out_19;
2408 ELSIF p_day_counter = 19 THEN
2409 p_measure := p_insert_detail.day_20;
2410 p_detail_id := p_insert_detail.detail_id_20;
2411 p_detail_ovn := p_insert_detail.detail_ovn_20;
2412 p_detail_time_in := p_insert_detail.time_in_20;
2413 p_detail_time_out := p_insert_detail.time_out_20;
2414 ELSIF p_day_counter = 20 THEN
2415 p_measure := p_insert_detail.day_21;
2416 p_detail_id := p_insert_detail.detail_id_21;
2417 p_detail_ovn := p_insert_detail.detail_ovn_21;
2418 p_detail_time_in := p_insert_detail.time_in_21;
2419 p_detail_time_out := p_insert_detail.time_out_21;
2420 ELSIF p_day_counter = 21 THEN
2421 p_measure := p_insert_detail.day_22;
2422 p_detail_id := p_insert_detail.detail_id_22;
2423 p_detail_ovn := p_insert_detail.detail_ovn_22;
2424 p_detail_time_in := p_insert_detail.time_in_22;
2425 p_detail_time_out := p_insert_detail.time_out_22;
2426 ELSIF p_day_counter = 22 THEN
2427 p_measure := p_insert_detail.day_23;
2428 p_detail_id := p_insert_detail.detail_id_23;
2429 p_detail_ovn := p_insert_detail.detail_ovn_23;
2430 p_detail_time_in := p_insert_detail.time_in_23;
2431 p_detail_time_out := p_insert_detail.time_out_23;
2432 ELSIF p_day_counter = 23 THEN
2433 p_measure := p_insert_detail.day_24;
2434 p_detail_id := p_insert_detail.detail_id_24;
2435 p_detail_ovn := p_insert_detail.detail_ovn_24;
2436 p_detail_time_in := p_insert_detail.time_in_24;
2437 p_detail_time_out := p_insert_detail.time_out_24;
2438 ELSIF p_day_counter = 24 THEN
2439 p_measure := p_insert_detail.day_25;
2440 p_detail_id := p_insert_detail.detail_id_25;
2441 p_detail_ovn := p_insert_detail.detail_ovn_25;
2442 p_detail_time_in := p_insert_detail.time_in_25;
2443 p_detail_time_out := p_insert_detail.time_out_25;
2444 ELSIF p_day_counter = 25 THEN
2445 p_measure := p_insert_detail.day_26;
2446 p_detail_id := p_insert_detail.detail_id_26;
2447 p_detail_ovn := p_insert_detail.detail_ovn_26;
2448 p_detail_time_in := p_insert_detail.time_in_26;
2449 p_detail_time_out := p_insert_detail.time_out_26;
2450 ELSIF p_day_counter = 26 THEN
2451 p_measure := p_insert_detail.day_27;
2452 p_detail_id := p_insert_detail.detail_id_27;
2453 p_detail_ovn := p_insert_detail.detail_ovn_27;
2454 p_detail_time_in := p_insert_detail.time_in_27;
2455 p_detail_time_out := p_insert_detail.time_out_27;
2456 ELSIF p_day_counter = 27 THEN
2457 p_measure := p_insert_detail.day_28;
2458 p_detail_id := p_insert_detail.detail_id_28;
2459 p_detail_ovn := p_insert_detail.detail_ovn_28;
2460 p_detail_time_in := p_insert_detail.time_in_28;
2461 p_detail_time_out := p_insert_detail.time_out_28;
2462 ELSIF p_day_counter = 28 THEN
2463 p_measure := p_insert_detail.day_29;
2464 p_detail_id := p_insert_detail.detail_id_29;
2465 p_detail_ovn := p_insert_detail.detail_ovn_29;
2466 p_detail_time_in := p_insert_detail.time_in_29;
2467 p_detail_time_out := p_insert_detail.time_out_29;
2468 ELSIF p_day_counter = 29 THEN
2469 p_measure := p_insert_detail.day_30;
2470 p_detail_id := p_insert_detail.detail_id_30;
2471 p_detail_ovn := p_insert_detail.detail_ovn_30;
2472 p_detail_time_in := p_insert_detail.time_in_30;
2473 p_detail_time_out := p_insert_detail.time_out_30;
2474 ELSIF p_day_counter = 30 THEN
2475 p_measure := p_insert_detail.day_31;
2476 p_detail_id := p_insert_detail.detail_id_31;
2477 p_detail_ovn := p_insert_detail.detail_ovn_31;
2478 p_detail_time_in := p_insert_detail.time_in_31;
2479 p_detail_time_out := p_insert_detail.time_out_31;
2480 END IF;
2481 END;
2482
2483 PROCEDURE manage_detaildffinfo (
2484 p_detail_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2485 p_detail_ovn IN NUMBER,
2486 p_det_details IN OUT NOCOPY hxc_timekeeper_process.g_detail_data%TYPE,
2487 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
2488 p_attribute_category IN VARCHAR2,
2489 p_tbb_id_reference_table IN OUT NOCOPY hxc_alias_utility.t_tbb_id_reference,
2490 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info,
2491 p_timecard_index_info
2492 /*ADVICE(1974): Unreferenced parameter [552] */
2493 IN OUT NOCOPY hxc_timekeeper_process.t_timecard_index_info
2494 ) IS
2495 l_bldtyp_id NUMBER
2496 /*ADVICE(1978): NUMBER has no precision [315] */
2497 ;
2498 l_attribute_index NUMBER
2499 /*ADVICE(1981): NUMBER has no precision [315] */
2500 ;
2501 l_attribute_found BOOLEAN;
2502 BEGIN
2503 IF p_detail_id IS NOT NULL AND p_det_details.EXISTS (p_detail_id) THEN
2504 IF p_attribute_category IS NOT NULL THEN
2505 IF NVL (p_attribute_category, '-999') <> NVL (p_det_details (p_detail_id).dff_catg, '-999') THEN
2506 p_det_details (p_detail_id).dff_catg := p_attribute_category;
2507 p_det_details (p_detail_id).dff_attr1 := NULL;
2508 p_det_details (p_detail_id).dff_attr2 := NULL;
2509 p_det_details (p_detail_id).dff_attr3 := NULL;
2510 p_det_details (p_detail_id).dff_attr4 := NULL;
2511 p_det_details (p_detail_id).dff_attr5 := NULL;
2512 p_det_details (p_detail_id).dff_attr6 := NULL;
2513 p_det_details (p_detail_id).dff_attr7 := NULL;
2514 p_det_details (p_detail_id).dff_attr8 := NULL;
2515 p_det_details (p_detail_id).dff_attr9 := NULL;
2516 p_det_details (p_detail_id).dff_attr10 := NULL;
2517 p_det_details (p_detail_id).dff_attr11 := NULL;
2518 p_det_details (p_detail_id).dff_attr12 := NULL;
2519 p_det_details (p_detail_id).dff_attr13 := NULL;
2520 p_det_details (p_detail_id).dff_attr14 := NULL;
2521 p_det_details (p_detail_id).dff_attr15 := NULL;
2522 p_det_details (p_detail_id).dff_attr16 := NULL;
2523 p_det_details (p_detail_id).dff_attr17 := NULL;
2524 p_det_details (p_detail_id).dff_attr18 := NULL;
2525 p_det_details (p_detail_id).dff_attr19 := NULL;
2526 p_det_details (p_detail_id).dff_attr20 := NULL;
2527 END IF;
2528 ELSE
2529 p_det_details (p_detail_id).dff_catg := p_det_details (p_detail_id).dff_catg;
2530 p_det_details (p_detail_id).dff_attr1 := NULL;
2531 p_det_details (p_detail_id).dff_attr2 := NULL;
2532 p_det_details (p_detail_id).dff_attr3 := NULL;
2533 p_det_details (p_detail_id).dff_attr4 := NULL;
2534 p_det_details (p_detail_id).dff_attr5 := NULL;
2535 p_det_details (p_detail_id).dff_attr6 := NULL;
2536 p_det_details (p_detail_id).dff_attr7 := NULL;
2537 p_det_details (p_detail_id).dff_attr8 := NULL;
2538 p_det_details (p_detail_id).dff_attr9 := NULL;
2539 p_det_details (p_detail_id).dff_attr10 := NULL;
2540 p_det_details (p_detail_id).dff_attr11 := NULL;
2541 p_det_details (p_detail_id).dff_attr12 := NULL;
2542 p_det_details (p_detail_id).dff_attr13 := NULL;
2543 p_det_details (p_detail_id).dff_attr14 := NULL;
2544 p_det_details (p_detail_id).dff_attr15 := NULL;
2545 p_det_details (p_detail_id).dff_attr16 := NULL;
2546 p_det_details (p_detail_id).dff_attr17 := NULL;
2547 p_det_details (p_detail_id).dff_attr18 := NULL;
2548 p_det_details (p_detail_id).dff_attr19 := NULL;
2549 p_det_details (p_detail_id).dff_attr20 := NULL;
2550 END IF;
2551
2552 IF p_det_details.EXISTS (p_detail_id)
2553 AND ( NVL (p_det_details (p_detail_id).dff_catg, '-999') <>
2554 NVL (p_det_details (p_detail_id).dff_oldcatg, '-999')
2555 OR NVL (p_det_details (p_detail_id).dff_attr1, '-999') <>
2556 NVL (p_det_details (p_detail_id).dff_oldattr1, '-999')
2557 OR NVL (p_det_details (p_detail_id).dff_attr2, '-999') <>
2558 NVL (p_det_details (p_detail_id).dff_oldattr2, '-999')
2559 OR NVL (p_det_details (p_detail_id).dff_attr3, '-999') <>
2560 NVL (p_det_details (p_detail_id).dff_oldattr3, '-999')
2561 OR NVL (p_det_details (p_detail_id).dff_attr4, '-999') <>
2562 NVL (p_det_details (p_detail_id).dff_oldattr4, '-999')
2563 OR NVL (p_det_details (p_detail_id).dff_attr5, '-999') <>
2564 NVL (p_det_details (p_detail_id).dff_oldattr5, '-999')
2565 OR NVL (p_det_details (p_detail_id).dff_attr6, '-999') <>
2566 NVL (p_det_details (p_detail_id).dff_oldattr6, '-999')
2567 OR NVL (p_det_details (p_detail_id).dff_attr7, '-999') <>
2568 NVL (p_det_details (p_detail_id).dff_oldattr7, '-999')
2569 OR NVL (p_det_details (p_detail_id).dff_attr8, '-999') <>
2570 NVL (p_det_details (p_detail_id).dff_oldattr8, '-999')
2571 OR NVL (p_det_details (p_detail_id).dff_attr9, '-999') <>
2572 NVL (p_det_details (p_detail_id).dff_oldattr9, '-999')
2573 OR NVL (p_det_details (p_detail_id).dff_attr10, '-999') <>
2574 NVL (p_det_details (p_detail_id).dff_oldattr10, '-999')
2575 OR NVL (p_det_details (p_detail_id).dff_attr11, '-999') <>
2576 NVL (p_det_details (p_detail_id).dff_oldattr11, '-999')
2577 OR NVL (p_det_details (p_detail_id).dff_attr12, '-999') <>
2578 NVL (p_det_details (p_detail_id).dff_oldattr12, '-999')
2579 OR NVL (p_det_details (p_detail_id).dff_attr13, '-999') <>
2580 NVL (p_det_details (p_detail_id).dff_oldattr13, '-999')
2581 OR NVL (p_det_details (p_detail_id).dff_attr14, '-999') <>
2582 NVL (p_det_details (p_detail_id).dff_oldattr14, '-999')
2583 OR NVL (p_det_details (p_detail_id).dff_attr15, '-999') <>
2584 NVL (p_det_details (p_detail_id).dff_oldattr15, '-999')
2585 OR NVL (p_det_details (p_detail_id).dff_attr16, '-999') <>
2586 NVL (p_det_details (p_detail_id).dff_oldattr16, '-999')
2587 OR NVL (p_det_details (p_detail_id).dff_attr17, '-999') <>
2588 NVL (p_det_details (p_detail_id).dff_oldattr17, '-999')
2589 OR NVL (p_det_details (p_detail_id).dff_attr18, '-999') <>
2590 NVL (p_det_details (p_detail_id).dff_oldattr18, '-999')
2591 OR NVL (p_det_details (p_detail_id).dff_attr19, '-999') <>
2592 NVL (p_det_details (p_detail_id).dff_oldattr19, '-999')
2593 OR NVL (p_det_details (p_detail_id).dff_attr20, '-999') <>
2594 NVL (p_det_details (p_detail_id).dff_oldattr20, '-999')
2595 OR NVL (p_det_details (p_detail_id).dff_attr21, '-999') <>
2596 NVL (p_det_details (p_detail_id).dff_oldattr21, '-999')
2597 OR NVL (p_det_details (p_detail_id).dff_attr22, '-999') <>
2598 NVL (p_det_details (p_detail_id).dff_oldattr22, '-999')
2599 OR NVL (p_det_details (p_detail_id).dff_attr23, '-999') <>
2600 NVL (p_det_details (p_detail_id).dff_oldattr23, '-999')
2601 OR NVL (p_det_details (p_detail_id).dff_attr24, '-999') <>
2602 NVL (p_det_details (p_detail_id).dff_oldattr24, '-999')
2603 OR NVL (p_det_details (p_detail_id).dff_attr25, '-999') <>
2604 NVL (p_det_details (p_detail_id).dff_oldattr25, '-999')
2605 OR NVL (p_det_details (p_detail_id).dff_attr26, '-999') <>
2606 NVL (p_det_details (p_detail_id).dff_oldattr26, '-999')
2607 OR NVL (p_det_details (p_detail_id).dff_attr27, '-999') <>
2608 NVL (p_det_details (p_detail_id).dff_oldattr27, '-999')
2609 OR NVL (p_det_details (p_detail_id).dff_attr28, '-999') <>
2610 NVL (p_det_details (p_detail_id).dff_oldattr28, '-999')
2611 OR NVL (p_det_details (p_detail_id).dff_attr29, '-999') <>
2612 NVL (p_det_details (p_detail_id).dff_oldattr29, '-999')
2613 OR NVL (p_det_details (p_detail_id).dff_attr30, '-999') <>
2614 NVL (p_det_details (p_detail_id).dff_oldattr30, '-999')
2615 ) THEN
2616 BEGIN
2617 SELECT bld_blk_info_type_id
2618 INTO l_bldtyp_id
2619 FROM hxc_bld_blk_info_types
2620 WHERE bld_blk_info_type = 'Dummy Paexpitdff Context';
2621 EXCEPTION
2622 WHEN OTHERS THEN
2623 NULL;
2624 /*ADVICE(2106): Use of NULL statements [532] */
2625
2626 /*ADVICE(2108): Exception masked by a NULL statement [533] */
2627
2628 /*ADVICE(2110): A WHEN OTHERS clause is used in the exception section without any other specific handlers
2629 [201] */
2630
2631 END;
2632
2633 IF NVL (l_bldtyp_id, -999) <> -999 THEN
2634 l_attribute_found := FALSE;
2635 --l_attribute_index :=
2636 hxc_alias_utility.attribute_check (
2637 p_bld_blk_info_type_id => l_bldtyp_id,
2638 p_time_building_block_id => p_detail_id,
2639 p_attributes => p_attributes,
2640 p_tbb_id_reference_table => p_tbb_id_reference_table,
2641 p_attribute_found => l_attribute_found,
2642 p_attribute_index => l_attribute_index
2643 );
2644
2645 -- now we need to check if we need to create an attribute or do an update
2646 --IF l_attribute_index = -1 THEN
2647 IF NOT (l_attribute_found) THEN
2648 hxc_timekeeper_process.g_negative_index := hxc_timekeeper_process.g_negative_index - 1;
2649 l_attribute_index := hxc_timekeeper_process.g_negative_index;
2650 hxc_timekeeper_utilities.add_dff_attribute (
2651 p_attribute => p_attributes,
2652 p_attribute_id => l_attribute_index,
2653 p_tbb_id => p_detail_id,
2654 p_tbb_ovn => p_detail_ovn,
2655 p_blk_type => 'Dummy Paexpitdff Context',
2656 p_blk_id => l_bldtyp_id,
2657 p_att_category => p_det_details (p_detail_id).dff_catg,
2658 p_att_1 => p_det_details (p_detail_id).dff_attr1,
2659 p_att_2 => p_det_details (p_detail_id).dff_attr2,
2660 p_att_3 => p_det_details (p_detail_id).dff_attr3,
2661 p_att_4 => p_det_details (p_detail_id).dff_attr4,
2662 p_att_5 => p_det_details (p_detail_id).dff_attr5,
2663 p_att_6 => p_det_details (p_detail_id).dff_attr6,
2664 p_att_7 => p_det_details (p_detail_id).dff_attr7,
2665 p_att_8 => p_det_details (p_detail_id).dff_attr8,
2666 p_att_9 => p_det_details (p_detail_id).dff_attr9,
2667 p_att_10 => p_det_details (p_detail_id).dff_attr10,
2668 p_att_11 => p_det_details (p_detail_id).dff_attr11,
2669 p_att_12 => p_det_details (p_detail_id).dff_attr12,
2670 p_att_13 => p_det_details (p_detail_id).dff_attr13,
2671 p_att_14 => p_det_details (p_detail_id).dff_attr14,
2672 p_att_15 => p_det_details (p_detail_id).dff_attr15,
2673 p_att_16 => p_det_details (p_detail_id).dff_attr16,
2674 p_att_17 => p_det_details (p_detail_id).dff_attr17,
2675 p_att_18 => p_det_details (p_detail_id).dff_attr18,
2676 p_att_19 => p_det_details (p_detail_id).dff_attr19,
2677 p_att_20 => p_det_details (p_detail_id).dff_attr20,
2678 p_att_21 => p_det_details (p_detail_id).dff_attr21,
2679 p_att_22 => p_det_details (p_detail_id).dff_attr22,
2680 p_att_23 => p_det_details (p_detail_id).dff_attr23,
2681 p_att_24 => p_det_details (p_detail_id).dff_attr24,
2682 p_att_25 => p_det_details (p_detail_id).dff_attr25,
2683 p_att_26 => p_det_details (p_detail_id).dff_attr26,
2684 p_att_27 => p_det_details (p_detail_id).dff_attr27,
2685 p_att_28 => p_det_details (p_detail_id).dff_attr28,
2686 p_att_29 => p_det_details (p_detail_id).dff_attr29,
2687 p_att_30 => p_det_details (p_detail_id).dff_attr30,
2688 p_attribute_index_info => p_attribute_index_info
2689 );
2690
2691 -- add the new attribute in the ref table
2692 IF p_tbb_id_reference_table.EXISTS (p_detail_id) THEN
2693 p_tbb_id_reference_table (p_detail_id).attribute_index :=
2694 p_tbb_id_reference_table (p_detail_id).attribute_index || '|'
2695 || hxc_timekeeper_process.g_negative_index;
2696 ELSE
2697 p_tbb_id_reference_table (p_detail_id).attribute_index :=
2698 '|' || hxc_timekeeper_process.g_negative_index;
2699 END IF;
2700 ELSE
2701 l_attribute_index := p_attributes (l_attribute_index).time_attribute_id;
2702 hxc_timekeeper_utilities.add_dff_attribute (
2703 p_attribute => p_attributes,
2704 p_attribute_id => l_attribute_index,
2705 p_tbb_id => p_detail_id,
2706 p_tbb_ovn => p_detail_ovn,
2707 p_blk_type => 'Dummy Paexpitdff Context',
2708 p_blk_id => l_bldtyp_id,
2709 p_att_category => p_det_details (p_detail_id).dff_catg,
2710 p_att_1 => p_det_details (p_detail_id).dff_attr1,
2711 p_att_2 => p_det_details (p_detail_id).dff_attr2,
2712 p_att_3 => p_det_details (p_detail_id).dff_attr3,
2713 p_att_4 => p_det_details (p_detail_id).dff_attr4,
2714 p_att_5 => p_det_details (p_detail_id).dff_attr5,
2715 p_att_6 => p_det_details (p_detail_id).dff_attr6,
2716 p_att_7 => p_det_details (p_detail_id).dff_attr7,
2717 p_att_8 => p_det_details (p_detail_id).dff_attr8,
2718 p_att_9 => p_det_details (p_detail_id).dff_attr9,
2719 p_att_10 => p_det_details (p_detail_id).dff_attr10,
2720 p_att_11 => p_det_details (p_detail_id).dff_attr11,
2721 p_att_12 => p_det_details (p_detail_id).dff_attr12,
2722 p_att_13 => p_det_details (p_detail_id).dff_attr13,
2723 p_att_14 => p_det_details (p_detail_id).dff_attr14,
2724 p_att_15 => p_det_details (p_detail_id).dff_attr15,
2725 p_att_16 => p_det_details (p_detail_id).dff_attr16,
2726 p_att_17 => p_det_details (p_detail_id).dff_attr17,
2727 p_att_18 => p_det_details (p_detail_id).dff_attr18,
2728 p_att_19 => p_det_details (p_detail_id).dff_attr19,
2729 p_att_20 => p_det_details (p_detail_id).dff_attr20,
2730 p_att_21 => p_det_details (p_detail_id).dff_attr21,
2731 p_att_22 => p_det_details (p_detail_id).dff_attr22,
2732 p_att_23 => p_det_details (p_detail_id).dff_attr23,
2733 p_att_24 => p_det_details (p_detail_id).dff_attr24,
2734 p_att_25 => p_det_details (p_detail_id).dff_attr25,
2735 p_att_26 => p_det_details (p_detail_id).dff_attr26,
2736 p_att_27 => p_det_details (p_detail_id).dff_attr27,
2737 p_att_28 => p_det_details (p_detail_id).dff_attr28,
2738 p_att_29 => p_det_details (p_detail_id).dff_attr29,
2739 p_att_30 => p_det_details (p_detail_id).dff_attr30,
2740 p_attribute_index_info => p_attribute_index_info
2741 );
2742 END IF;
2743 END IF; ----no dff context in database
2744 END IF;
2745 END IF;
2746 END;
2747
2748 PROCEDURE check_msg_set_process_flag (
2749 p_blocks IN OUT NOCOPY hxc_block_table_type,
2750 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
2751 p_messages IN OUT NOCOPY hxc_message_table_type
2752 ) IS
2753 l_change_process_flag BOOLEAN := FALSE;
2754 l_index BINARY_INTEGER
2755 /*ADVICE(2237): Consider using PLS_INTEGER instead of INTEGER and BINARY_INTEGER if on Oracle 7.3 or above
2756 [302] */
2757 ;
2758 l_delete_index BINARY_INTEGER
2759 /*ADVICE(2241): Consider using PLS_INTEGER instead of INTEGER and BINARY_INTEGER if on Oracle 7.3 or above
2760 [302] */
2761 ;
2762 l_timecard_status VARCHAR2 (50) := NULL;
2763 /*ADVICE(2245): Initialization to NULL is superfluous [417] */
2764
2765 l_timecard_id NUMBER
2766 /*ADVICE(2248): NUMBER has no precision [315] */
2767 ;
2768 l_timecard_ovn NUMBER
2769 /*ADVICE(2251): NUMBER has no precision [315] */
2770 ;
2771 BEGIN
2772 l_change_process_flag := FALSE;
2773 l_delete_index := NULL;
2774 l_index := p_messages.FIRST;
2775
2776 WHILE l_index IS NOT NULL LOOP
2777 IF p_messages (l_index).message_name = 'HXC_TIMECARD_NOT_SUBMITTED' THEN
2778 l_change_process_flag := TRUE;
2779 l_delete_index := l_index;
2780 EXIT;
2781 /*ADVICE(2263): An EXIT statement is used in a WHILE loop [502] */
2782
2783 END IF;
2784
2785 l_index := p_messages.NEXT (l_index);
2786 END LOOP;
2787
2788 IF l_change_process_flag THEN
2789 -- if we passed the above then we need to change the status
2790 -- to be process so that the TC get's submitted this
2791 -- is when rejected TC is submitted without any change
2792 l_timecard_id :=
2793 p_blocks (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).time_building_block_id;
2794 l_timecard_ovn :=
2795 p_blocks (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).object_version_number;
2796 l_timecard_status := hxc_timecard_search_pkg.get_timecard_status_code (l_timecard_id, l_timecard_ovn);
2797
2798 IF l_timecard_status = 'REJECTED' THEN
2799 l_index := p_blocks.FIRST;
2800
2801 WHILE l_index IS NOT NULL LOOP
2802 p_blocks (l_index).process := 'Y';
2803 p_blocks (l_index).changed := 'Y';
2804 l_index := p_blocks.NEXT (l_index);
2805 END LOOP;
2806
2807 l_index := p_attributes.FIRST;
2808
2809 WHILE l_index IS NOT NULL LOOP
2810 p_attributes (l_index).process := 'Y';
2811 p_attributes (l_index).changed := 'Y';
2812 l_index := p_attributes.NEXT (l_index);
2813 END LOOP;
2814
2815 IF l_delete_index IS NOT NULL THEN
2816 --now delete the error as we have handled it
2817 p_messages.DELETE (l_delete_index);
2818 END IF;
2819 END IF;
2820 END IF;
2821 END check_msg_set_process_flag;
2822
2823
2824 -- ----------------------------------------------------------------------------
2825 -- Used to loop through all the persons in TK group
2826 -- and do bulk pref evaluation
2827 -- ----------------------------------------------------------------------------
2828 PROCEDURE cache_employee_pref_in_group (
2829 p_group_id IN NUMBER,
2830 p_timekeeper_id IN NUMBER
2831 ) IS
2832 l_resource_pref_index NUMBER
2833 /*ADVICE(2315): NUMBER has no precision [315] */
2834 ;
2835 l_pref_table hxc_preference_evaluation.t_pref_table;
2836 l_resource_table hxc_preference_evaluation.t_resource_pref_table;
2837 l_query_append VARCHAR2 (32000);
2838 /*ADVICE(2320): VARCHAR2 declaration with length greater than 500 characters [307] */
2839
2840
2841 CURSOR c_emp_hireinfo (
2842 p_resource_id NUMBER
2843 ) IS
2844 SELECT MIN (effective_start_date)
2845 FROM per_people_f
2846 WHERE person_id = p_resource_id;
2847
2848 /*Cursor Modified By Mithun for CWK Terminate Bug*/
2849 /* changes done by senthil for emp terminate enhancement*/
2850 CURSOR c_emp_terminateinfo(
2851 p_resource_id NUMBER
2852 ) IS
2853 SELECT final_process_date, date_start
2854 FROM per_periods_of_service
2855 WHERE person_id = p_resource_id
2856 union all
2857 select (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
2858 from per_periods_of_placement
2859 where person_id = p_resource_id
2860 ORDER BY date_start DESC;
2861
2862 --Added By Mithun for CWK Terminate Bug
2863 date_start DATE;
2864
2865 /*end of changes by senthil */
2866 BEGIN
2867 l_query_append := ' in (select htgqc.criteria_id resource_id ';
2868 l_query_append :=
2869 l_query_append || ' from hxc_tk_group_queries htgq, HXC_TK_GROUP_QUERY_CRITERIA htgqc ';
2870 l_query_append := l_query_append
2871 || ' where htgq.tk_group_query_id = htgqc.tk_group_query_id and htgq.tk_group_id='
2872 || p_group_id || ' ) ';
2873 l_pref_table.DELETE;
2874 l_resource_table.DELETE;
2875 g_start_stop_pref_cache.DELETE;
2876 hxc_preference_evaluation.resource_prefs_bulk (
2877 p_evaluation_date => SYSDATE,
2878 p_pref_table => l_pref_table,
2879 p_resource_pref_table => l_resource_table,
2880 p_resource_sql => l_query_append
2881 );
2882 l_resource_pref_index := l_resource_table.FIRST;
2883
2884 LOOP
2885 EXIT WHEN (NOT l_resource_table.EXISTS (l_resource_pref_index));
2886
2887 IF p_timekeeper_id <> l_resource_pref_index THEN
2888 IF g_start_stop_pref_cache.EXISTS (l_resource_table (l_resource_pref_index).start_index) THEN
2889 g_resource_perftab (l_resource_pref_index) :=
2890 g_start_stop_pref_cache (l_resource_table (l_resource_pref_index).start_index);
2891 OPEN c_emp_hireinfo (p_resource_id => l_resource_pref_index);
2892 FETCH c_emp_hireinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_start_date;
2893 CLOSE c_emp_hireinfo;
2894 /*Changes Done By Mithun for CWK Terminate Bug*/
2895 /* changes done by senthil for emp terminate enhancement*/
2896 OPEN c_emp_terminateinfo (p_resource_id => l_resource_pref_index);
2897 FETCH c_emp_terminateinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_terminate_date, date_start;
2898 CLOSE c_emp_terminateinfo;
2899 /*end of changes by senthil*/
2900 ELSE
2901 OPEN c_emp_hireinfo (p_resource_id => l_resource_pref_index);
2902 FETCH c_emp_hireinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_start_date;
2903 CLOSE c_emp_hireinfo;
2904
2905 /*Changes Done By Mithun for CWK Terminate Bug*/
2906 /* changes done by senthil for emp terminate enhancement*/
2907 OPEN c_emp_terminateinfo (p_resource_id => l_resource_pref_index);
2908 FETCH c_emp_terminateinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_terminate_date, date_start;
2909 CLOSE c_emp_terminateinfo;
2910 /*end of changes by senthil*/
2911 FOR l_index IN
2912 l_resource_table (l_resource_pref_index).start_index .. l_resource_table (l_resource_pref_index).stop_index LOOP
2913 hxc_timekeeper_utilities.add_resource_to_perftab (
2914 p_resource_id => l_resource_pref_index,
2915 p_pref_code => l_pref_table (l_index).preference_code,
2916 p_attribute1 => l_pref_table (l_index).attribute1,
2917 p_attribute2 => l_pref_table (l_index).attribute2,
2918 p_attribute3 => l_pref_table (l_index).attribute3,
2919 p_attribute4 => l_pref_table (l_index).attribute4,
2920 p_attribute5 => l_pref_table (l_index).attribute5,
2921 p_attribute6 => l_pref_table (l_index).attribute6,
2922 p_attribute7 => l_pref_table (l_index).attribute7,
2923 p_attribute8 => l_pref_table (l_index).attribute8,
2924 p_attribute11 => l_pref_table (l_index).attribute11
2925 );
2926 END LOOP;
2927 /*ADVICE(2378): Nested LOOPs should all be labeled [406] */
2928
2929
2930 g_start_stop_pref_cache (l_resource_table (l_resource_pref_index).start_index) :=
2931 g_resource_perftab (l_resource_pref_index);
2932 END IF;
2933 END IF;
2934
2935 l_resource_pref_index := l_resource_table.NEXT (l_resource_pref_index);
2936 END LOOP;
2937 END cache_employee_pref_in_group;
2938
2939
2940 -- ----------------------------------------------------------------------------
2941 -- Used to loop through all the persons in TK group
2942 -- and get the recurring period informations
2943 -- ----------------------------------------------------------------------------
2944 PROCEDURE get_group_period_list (
2945 p_group_id IN NUMBER,
2946 p_business_group_id IN NUMBER,
2947 p_periodname_list OUT NOCOPY hxc_timekeeper_utilities.t_group_list
2948 ) IS
2949 /*start of fix 5083261*/
2950 CURSOR c_pref_cursor IS
2951 select recurring_period_id rec_period,rownum periodname_index from (
2952 SELECT distinct c.recurring_period_id,c.name
2953 FROM hxc_pref_hierarchies a, hxc_resource_rules b, hxc_recurring_periods c
2954 WHERE a.attribute_category = 'TC_W_TCRD_PERIOD'
2955 and a.attribute1 = c.recurring_period_id
2956 AND a.top_level_parent_id = b.pref_hierarchy_id
2957 AND p_business_group_id = NVL (a.business_group_id, p_business_group_id) order by c.name);
2958 /*end of fix 5083261*/
2959 l_pref_resource
2960 /*ADVICE(2415): Unreferenced variable [553] */
2961 hxc_preference_evaluation.t_pref_table;
2962 l_periodname_list
2963 /*ADVICE(2418): Unreferenced variable [553] */
2964 hxc_timekeeper_utilities.t_group_list;
2965 l_pref_index
2966 /*ADVICE(2421): Unreferenced variable [553] */
2967 NUMBER
2968 /*ADVICE(2423): NUMBER has no precision [315] */
2969 ;
2970 l_periodname_index NUMBER
2971 /*ADVICE(2426): NUMBER has no precision [315] */
2972 ;
2973 BEGIN
2974 p_periodname_list.DELETE;
2975
2976 FOR l_pref IN c_pref_cursor LOOP
2977 /*start fix for 5083261 */
2978 l_periodname_index := l_pref.periodname_index;
2979 /*end of fix 5083261*/
2980 p_periodname_list (l_periodname_index).recurring_period_id := l_pref.rec_period;
2981 p_periodname_list (l_periodname_index).GROUP_ID := p_group_id;
2982 END LOOP;
2983 END;
2984
2985
2986 -----------------------------------------------------------------------------
2987 -- Used to get the select statement depending upon the alias
2988 ------------------------------------------------------------------------------
2989 PROCEDURE get_type_sql (
2990 p_aliasid IN NUMBER,
2991 p_person_type IN VARCHAR2 DEFAULT NULL,
2992 p_alias_typ OUT NOCOPY VARCHAR2,
2993 p_alias_sql OUT NOCOPY LONG
2994 /*ADVICE(2447): Use of LONG [117] */
2995 ,
2996 p_maxsize OUT NOCOPY NUMBER,
2997 p_minvalue OUT NOCOPY NUMBER,
2998 p_maxvalue OUT NOCOPY NUMBER,
2999 p_precision OUT NOCOPY NUMBER,
3000 p_colmtype OUT NOCOPY VARCHAR2
3001 ) IS
3002 l_alias_type VARCHAR2 (80) := NULL;
3003 /*ADVICE(2456): Initialization to NULL is superfluous [417] */
3004
3005 l_ref_obj VARCHAR2 (2000) := NULL;
3006 /*ADVICE(2459): VARCHAR2 declaration with length greater than 500 characters [307] */
3007
3008 /*ADVICE(2461): Initialization to NULL is superfluous [417] */
3009
3010 l_s_query LONG
3011 /*ADVICE(2464): Use of LONG [117] */
3012 := NULL;
3013 /*ADVICE(2466): Initialization to NULL is superfluous [417] */
3014
3015 l_index
3016 /*ADVICE(2469): Unreferenced variable [553] */
3017 NUMBER
3018 /*ADVICE(2471): NUMBER has no precision [315] */
3019 := 0;
3020 l_a VARCHAR2 (300);
3021 l_max_size NUMBER
3022 /*ADVICE(2475): NUMBER has no precision [315] */
3023 := 0;
3024 l_min_value NUMBER
3025 /*ADVICE(2478): NUMBER has no precision [315] */
3026 := 0;
3027 l_max_value NUMBER
3028 /*ADVICE(2481): NUMBER has no precision [315] */
3029 := 0;
3030 l_number_precision NUMBER
3031 /*ADVICE(2484): NUMBER has no precision [315] */
3032 := 0;
3033 l_colmtype VARCHAR2 (10);
3034 BEGIN
3035 hxc_alias_utility.get_alias_definition_info (
3036 p_alias_definition_id => p_aliasid,
3037 p_alias_type => l_alias_type,
3038 p_reference_object => l_ref_obj,
3039 p_prompt => l_a
3040 );
3041
3042 IF l_alias_type = 'VALUE_SET_TABLE' THEN
3043 hxc_alias_utility.get_vset_table_type_select (
3044 p_alias_definition_id => p_aliasid,
3045 x_select => l_s_query,
3046 p_id_type => l_colmtype
3047 );
3048 l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
3049 ELSIF l_alias_type = 'VALUE_SET_INDEPENDENT' THEN
3050 hxc_alias_utility.get_vset_indep_type_select (
3051 p_alias_definition_id => p_aliasid,
3052 x_select => l_s_query
3053 );
3054 l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
3055 l_colmtype := 'NUMBER';
3056 ELSIF l_alias_type = 'OTL_ALT_DDF' THEN
3057 hxc_alias_utility.get_otl_an_context_type_select (
3058 p_alias_definition_id => p_aliasid,
3059 p_timekeeper_person_type => p_person_type,
3060 x_select => l_s_query
3061 );
3062 l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
3063 l_colmtype := 'NUMBER';
3064 ELSIF l_alias_type = 'VALUE_SET_NONE' THEN
3065 hxc_alias_utility.get_vset_none_type_property (
3066 p_alias_definition_id => p_aliasid,
3067 p_format_type => l_s_query,
3068 p_maximum_size => l_max_size,
3069 p_minimum_value => l_min_value,
3070 p_maximum_value => l_max_value,
3071 p_number_precision => l_number_precision
3072 );
3073 END IF;
3074
3075 p_alias_typ := l_alias_type;
3076 p_alias_sql := l_s_query;
3077 p_maxsize := l_max_size;
3078 p_minvalue := l_min_value;
3079 p_maxvalue := l_max_value;
3080 p_precision := l_number_precision;
3081 p_colmtype := l_colmtype;
3082 END get_type_sql;
3083
3084
3085 -- ----------------------------------------------------------------------------
3086 -- Used in form to get alias information for all the Timekeeper layout attributes
3087 -- ----------------------------------------------------------------------------
3088 PROCEDURE populate_alias_table (
3089 p_timekeeper_id IN NUMBER,
3090 p_tk_layout_info OUT NOCOPY hxc_timekeeper_utilities.tk_layout_tab,
3091 p_att_alias_table OUT NOCOPY hxc_timekeeper_utilities.att_alias_list
3092 ) IS
3093 l_pref_table hxc_preference_evaluation.t_pref_table;
3094 l_index NUMBER
3095 /*ADVICE(2548): NUMBER has no precision [315] */
3096 := 0;
3097 m_alias_typ VARCHAR2 (80) := NULL;
3098 /*ADVICE(2551): Initialization to NULL is superfluous [417] */
3099
3100 m_alias_sql LONG
3101 /*ADVICE(2554): Use of LONG [117] */
3102 := NULL;
3103 /*ADVICE(2556): Initialization to NULL is superfluous [417] */
3104
3105 m_alias_maxsize NUMBER
3106 /*ADVICE(2559): NUMBER has no precision [315] */
3107 ;
3108 m_alias_minvalue NUMBER
3109 /*ADVICE(2562): NUMBER has no precision [315] */
3110 ;
3111 m_alias_maxvalue NUMBER
3112 /*ADVICE(2565): NUMBER has no precision [315] */
3113 ;
3114 m_alias_precision NUMBER
3115 /*ADVICE(2568): NUMBER has no precision [315] */
3116 ;
3117 m_alias_lovcoltype VARCHAR2 (10);
3118 /*Start fix for Bug 5055770 */
3119 l_person_type PER_PERSON_TYPES.SYSTEM_PERSON_TYPE%TYPE:=NULL;
3120 /*End fix for Bug 5055770 */
3121 /*ADVICE(2572): Initialization to NULL is superfluous [417] */
3122
3123
3124 CURSOR c_get_type (
3125 p_person_id NUMBER
3126 ) IS
3127 SELECT typ.system_person_type
3128 FROM per_person_types typ, per_person_type_usages_f ptu
3129 WHERE typ.person_type_id = ptu.person_type_id
3130 AND SYSDATE BETWEEN ptu.effective_start_date AND ptu.effective_end_date
3131 AND ptu.person_id = p_person_id;
3132 BEGIN
3133 hxc_preference_evaluation.resource_preferences (p_timekeeper_id, l_pref_table, SYSDATE);
3134 OPEN c_get_type (p_timekeeper_id);
3135 FETCH c_get_type INTO l_person_type;
3136 CLOSE c_get_type;
3137 l_index := l_pref_table.FIRST;
3138
3139 LOOP
3140 EXIT WHEN (NOT l_pref_table.EXISTS (l_index));
3141
3142 IF (l_pref_table (l_index).preference_code = 'TK_TCARD_SETUP') THEN
3143 p_tk_layout_info (1).tk_timeflag := l_pref_table (l_index).attribute1;
3144 p_tk_layout_info (1).tk_empno := l_pref_table (l_index).attribute3;
3145 p_tk_layout_info (1).tk_empname := l_pref_table (l_index).attribute2;
3146 p_tk_layout_info (1).tk_base_attr := l_pref_table (l_index).attribute4;
3147 p_tk_layout_info (1).tk_applset := l_pref_table (l_index).attribute5;
3148
3149 -- Added for Enh 3303359
3150 -- Caching the pref value for Default Recurring Period
3151 hxc_timekeeper_utilities.g_default_rec_period:= l_pref_table (l_index).attribute6;
3152
3153
3154 ELSIF (l_pref_table (l_index).preference_code = 'TK_TCARD_CLA') THEN --CTK
3155 p_tk_layout_info (1).tk_audit_enabled := l_pref_table (l_index).attribute1;
3156 p_tk_layout_info (1).tk_data_entry_required := l_pref_table (l_index).attribute2;
3157 p_tk_layout_info (1).tk_notification_to := l_pref_table (l_index).attribute3;
3158 p_tk_layout_info (1).tk_notification_type := l_pref_table (l_index).attribute4;
3159 ELSIF (l_pref_table (l_index).preference_code = 'TK_TCARD_ATTRIBUTES_DEFINITION') THEN
3160 m_alias_sql := NULL;
3161 m_alias_typ := NULL;
3162 p_att_alias_table (1).attr_name := 'ATTRIBUTE1';
3163 p_att_alias_table (1).alias_id := l_pref_table (l_index).attribute1;
3164
3165 IF p_att_alias_table (1).alias_id IS NOT NULL THEN
3166 get_type_sql (
3167 p_aliasid => p_att_alias_table (1).alias_id,
3168 p_person_type => l_person_type,
3169 p_alias_typ => m_alias_typ,
3170 p_alias_sql => m_alias_sql,
3171 p_maxsize => m_alias_maxsize,
3172 p_minvalue => m_alias_minvalue,
3173 p_maxvalue => m_alias_maxvalue,
3174 p_precision => m_alias_precision,
3175 p_colmtype => m_alias_lovcoltype
3176 );
3177 p_att_alias_table (1).alias_sql := m_alias_sql;
3178 p_att_alias_table (1).alias_type := m_alias_typ;
3179 p_att_alias_table (1).alias_maxsize := m_alias_maxsize;
3180 p_att_alias_table (1).alias_minvalue := m_alias_minvalue;
3181 p_att_alias_table (1).alias_maxvalue := m_alias_maxvalue;
3182 p_att_alias_table (1).alias_precision := m_alias_precision;
3183 p_att_alias_table (1).alias_lovcoltype := m_alias_lovcoltype;
3184 END IF;
3185
3186 p_att_alias_table (2).attr_name := 'ATTRIBUTE2';
3187 p_att_alias_table (2).alias_id := l_pref_table (l_index).attribute2;
3188
3189 IF p_att_alias_table (2).alias_id IS NOT NULL THEN
3190 get_type_sql (
3191 p_aliasid => p_att_alias_table (2).alias_id,
3192 p_person_type => l_person_type,
3193 p_alias_typ => m_alias_typ,
3194 p_alias_sql => m_alias_sql,
3195 p_maxsize => m_alias_maxsize,
3196 p_minvalue => m_alias_minvalue,
3197 p_maxvalue => m_alias_maxvalue,
3198 p_precision => m_alias_precision,
3199 p_colmtype => m_alias_lovcoltype
3200 );
3201 p_att_alias_table (2).alias_sql := m_alias_sql;
3202 p_att_alias_table (2).alias_type := m_alias_typ;
3203 p_att_alias_table (2).alias_maxsize := m_alias_maxsize;
3204 p_att_alias_table (2).alias_minvalue := m_alias_minvalue;
3205 p_att_alias_table (2).alias_maxvalue := m_alias_maxvalue;
3206 p_att_alias_table (2).alias_precision := m_alias_precision;
3207 p_att_alias_table (2).alias_lovcoltype := m_alias_lovcoltype;
3208 END IF;
3209
3210 p_att_alias_table (3).attr_name := 'ATTRIBUTE3';
3211 p_att_alias_table (3).alias_id := l_pref_table (l_index).attribute3;
3212
3213 IF p_att_alias_table (3).alias_id IS NOT NULL THEN
3214 get_type_sql (
3215 p_aliasid => p_att_alias_table (3).alias_id,
3216 p_person_type => l_person_type,
3217 p_alias_typ => m_alias_typ,
3218 p_alias_sql => m_alias_sql,
3219 p_maxsize => m_alias_maxsize,
3220 p_minvalue => m_alias_minvalue,
3221 p_maxvalue => m_alias_maxvalue,
3222 p_precision => m_alias_precision,
3223 p_colmtype => m_alias_lovcoltype
3224 );
3225 p_att_alias_table (3).alias_sql := m_alias_sql;
3226 p_att_alias_table (3).alias_type := m_alias_typ;
3227 p_att_alias_table (3).alias_maxsize := m_alias_maxsize;
3228 p_att_alias_table (3).alias_minvalue := m_alias_minvalue;
3229 p_att_alias_table (3).alias_maxvalue := m_alias_maxvalue;
3230 p_att_alias_table (3).alias_precision := m_alias_precision;
3231 p_att_alias_table (3).alias_lovcoltype := m_alias_lovcoltype;
3232 END IF;
3233
3234 p_att_alias_table (4).attr_name := 'ATTRIBUTE4';
3235 p_att_alias_table (4).alias_id := l_pref_table (l_index).attribute4;
3236
3237 IF p_att_alias_table (4).alias_id IS NOT NULL THEN
3238 get_type_sql (
3239 p_aliasid => p_att_alias_table (4).alias_id,
3240 p_person_type => l_person_type,
3241 p_alias_typ => m_alias_typ,
3242 p_alias_sql => m_alias_sql,
3243 p_maxsize => m_alias_maxsize,
3244 p_minvalue => m_alias_minvalue,
3245 p_maxvalue => m_alias_maxvalue,
3246 p_precision => m_alias_precision,
3247 p_colmtype => m_alias_lovcoltype
3248 );
3249 p_att_alias_table (4).alias_sql := m_alias_sql;
3250 p_att_alias_table (4).alias_type := m_alias_typ;
3251 p_att_alias_table (4).alias_maxsize := m_alias_maxsize;
3252 p_att_alias_table (4).alias_minvalue := m_alias_minvalue;
3253 p_att_alias_table (4).alias_maxvalue := m_alias_maxvalue;
3254 p_att_alias_table (4).alias_precision := m_alias_precision;
3255 p_att_alias_table (4).alias_lovcoltype := m_alias_lovcoltype;
3256 END IF;
3257
3258 p_att_alias_table (5).attr_name := 'ATTRIBUTE5';
3259 p_att_alias_table (5).alias_id := l_pref_table (l_index).attribute5;
3260
3261 IF p_att_alias_table (5).alias_id IS NOT NULL THEN
3262 get_type_sql (
3263 p_aliasid => p_att_alias_table (5).alias_id,
3264 p_person_type => l_person_type,
3265 p_alias_typ => m_alias_typ,
3266 p_alias_sql => m_alias_sql,
3267 p_maxsize => m_alias_maxsize,
3268 p_minvalue => m_alias_minvalue,
3269 p_maxvalue => m_alias_maxvalue,
3270 p_precision => m_alias_precision,
3271 p_colmtype => m_alias_lovcoltype
3272 );
3273 p_att_alias_table (5).alias_sql := m_alias_sql;
3274 p_att_alias_table (5).alias_type := m_alias_typ;
3275 p_att_alias_table (5).alias_maxsize := m_alias_maxsize;
3276 p_att_alias_table (5).alias_minvalue := m_alias_minvalue;
3277 p_att_alias_table (5).alias_maxvalue := m_alias_maxvalue;
3278 p_att_alias_table (5).alias_precision := m_alias_precision;
3279 p_att_alias_table (5).alias_lovcoltype := m_alias_lovcoltype;
3280 END IF;
3281
3282 p_att_alias_table (6).attr_name := 'ATTRIBUTE6';
3283 p_att_alias_table (6).alias_id := l_pref_table (l_index).attribute6;
3284
3285 IF p_att_alias_table (6).alias_id IS NOT NULL THEN
3286 get_type_sql (
3287 p_aliasid => p_att_alias_table (6).alias_id,
3288 p_person_type => l_person_type,
3289 p_alias_typ => m_alias_typ,
3290 p_alias_sql => m_alias_sql,
3291 p_maxsize => m_alias_maxsize,
3292 p_minvalue => m_alias_minvalue,
3293 p_maxvalue => m_alias_maxvalue,
3294 p_precision => m_alias_precision,
3295 p_colmtype => m_alias_lovcoltype
3296 );
3297 p_att_alias_table (6).alias_sql := m_alias_sql;
3298 p_att_alias_table (6).alias_type := m_alias_typ;
3299 p_att_alias_table (6).alias_maxsize := m_alias_maxsize;
3300 p_att_alias_table (6).alias_minvalue := m_alias_minvalue;
3301 p_att_alias_table (6).alias_maxvalue := m_alias_maxvalue;
3302 p_att_alias_table (6).alias_precision := m_alias_precision;
3303 p_att_alias_table (6).alias_lovcoltype := m_alias_lovcoltype;
3304 END IF;
3305
3306 p_att_alias_table (7).attr_name := 'ATTRIBUTE7';
3307 p_att_alias_table (7).alias_id := l_pref_table (l_index).attribute7;
3308
3309 IF p_att_alias_table (7).alias_id IS NOT NULL THEN
3310 get_type_sql (
3311 p_aliasid => p_att_alias_table (7).alias_id,
3312 p_person_type => l_person_type,
3313 p_alias_typ => m_alias_typ,
3314 p_alias_sql => m_alias_sql,
3315 p_maxsize => m_alias_maxsize,
3316 p_minvalue => m_alias_minvalue,
3317 p_maxvalue => m_alias_maxvalue,
3318 p_precision => m_alias_precision,
3319 p_colmtype => m_alias_lovcoltype
3320 );
3321 p_att_alias_table (7).alias_sql := m_alias_sql;
3322 p_att_alias_table (7).alias_type := m_alias_typ;
3323 p_att_alias_table (7).alias_maxsize := m_alias_maxsize;
3324 p_att_alias_table (7).alias_minvalue := m_alias_minvalue;
3325 p_att_alias_table (7).alias_maxvalue := m_alias_maxvalue;
3326 p_att_alias_table (7).alias_precision := m_alias_precision;
3327 p_att_alias_table (7).alias_lovcoltype := m_alias_lovcoltype;
3328 END IF;
3329
3330 p_att_alias_table (8).attr_name := 'ATTRIBUTE8';
3331 p_att_alias_table (8).alias_id := l_pref_table (l_index).attribute8;
3332
3333 IF p_att_alias_table (8).alias_id IS NOT NULL THEN
3334 get_type_sql (
3335 p_aliasid => p_att_alias_table (8).alias_id,
3336 p_person_type => l_person_type,
3337 p_alias_typ => m_alias_typ,
3338 p_alias_sql => m_alias_sql,
3339 p_maxsize => m_alias_maxsize,
3340 p_minvalue => m_alias_minvalue,
3341 p_maxvalue => m_alias_maxvalue,
3342 p_precision => m_alias_precision,
3343 p_colmtype => m_alias_lovcoltype
3344 );
3345 p_att_alias_table (8).alias_sql := m_alias_sql;
3346 p_att_alias_table (8).alias_type := m_alias_typ;
3347 p_att_alias_table (8).alias_maxsize := m_alias_maxsize;
3348 p_att_alias_table (8).alias_minvalue := m_alias_minvalue;
3349 p_att_alias_table (8).alias_maxvalue := m_alias_maxvalue;
3350 p_att_alias_table (8).alias_precision := m_alias_precision;
3351 p_att_alias_table (8).alias_lovcoltype := m_alias_lovcoltype;
3352 END IF;
3353
3354 p_att_alias_table (9).attr_name := 'ATTRIBUTE9';
3355 p_att_alias_table (9).alias_id := l_pref_table (l_index).attribute9;
3356
3357 IF p_att_alias_table (9).alias_id IS NOT NULL THEN
3358 get_type_sql (
3359 p_aliasid => p_att_alias_table (9).alias_id,
3360 p_person_type => l_person_type,
3361 p_alias_typ => m_alias_typ,
3362 p_alias_sql => m_alias_sql,
3363 p_maxsize => m_alias_maxsize,
3364 p_minvalue => m_alias_minvalue,
3365 p_maxvalue => m_alias_maxvalue,
3366 p_precision => m_alias_precision,
3367 p_colmtype => m_alias_lovcoltype
3368 );
3369 p_att_alias_table (9).alias_sql := m_alias_sql;
3370 p_att_alias_table (9).alias_type := m_alias_typ;
3371 p_att_alias_table (9).alias_maxsize := m_alias_maxsize;
3372 p_att_alias_table (9).alias_minvalue := m_alias_minvalue;
3373 p_att_alias_table (9).alias_maxvalue := m_alias_maxvalue;
3374 p_att_alias_table (9).alias_precision := m_alias_precision;
3375 p_att_alias_table (9).alias_lovcoltype := m_alias_lovcoltype;
3376 END IF;
3377
3378 p_att_alias_table (10).attr_name := 'ATTRIBUTE10';
3379 p_att_alias_table (10).alias_id := l_pref_table (l_index).attribute10;
3380
3381 IF p_att_alias_table (10).alias_id IS NOT NULL THEN
3382 get_type_sql (
3383 p_aliasid => p_att_alias_table (10).alias_id,
3384 p_person_type => l_person_type,
3385 p_alias_typ => m_alias_typ,
3386 p_alias_sql => m_alias_sql,
3387 p_maxsize => m_alias_maxsize,
3388 p_minvalue => m_alias_minvalue,
3389 p_maxvalue => m_alias_maxvalue,
3390 p_precision => m_alias_precision,
3391 p_colmtype => m_alias_lovcoltype
3392 );
3393 p_att_alias_table (10).alias_sql := m_alias_sql;
3394 p_att_alias_table (10).alias_type := m_alias_typ;
3395 p_att_alias_table (10).alias_maxsize := m_alias_maxsize;
3396 p_att_alias_table (10).alias_minvalue := m_alias_minvalue;
3397 p_att_alias_table (10).alias_maxvalue := m_alias_maxvalue;
3398 p_att_alias_table (10).alias_precision := m_alias_precision;
3399 p_att_alias_table (10).alias_lovcoltype := m_alias_lovcoltype;
3400 END IF;
3401
3402 p_att_alias_table (11).attr_name := 'ATTRIBUTE11';
3403 p_att_alias_table (11).alias_id := l_pref_table (l_index).attribute11;
3404
3405 IF p_att_alias_table (11).alias_id IS NOT NULL THEN
3406 get_type_sql (
3407 p_aliasid => p_att_alias_table (11).alias_id,
3408 p_person_type => l_person_type,
3409 p_alias_typ => m_alias_typ,
3410 p_alias_sql => m_alias_sql,
3411 p_maxsize => m_alias_maxsize,
3412 p_minvalue => m_alias_minvalue,
3413 p_maxvalue => m_alias_maxvalue,
3414 p_precision => m_alias_precision,
3415 p_colmtype => m_alias_lovcoltype
3416 );
3417 p_att_alias_table (11).alias_sql := m_alias_sql;
3418 p_att_alias_table (11).alias_type := m_alias_typ;
3419 p_att_alias_table (11).alias_maxsize := m_alias_maxsize;
3420 p_att_alias_table (11).alias_minvalue := m_alias_minvalue;
3421 p_att_alias_table (11).alias_maxvalue := m_alias_maxvalue;
3422 p_att_alias_table (11).alias_precision := m_alias_precision;
3423 p_att_alias_table (11).alias_lovcoltype := m_alias_lovcoltype;
3424 END IF;
3425
3426 p_att_alias_table (12).attr_name := 'ATTRIBUTE12';
3427 p_att_alias_table (12).alias_id := l_pref_table (l_index).attribute12;
3428
3429 IF p_att_alias_table (12).alias_id IS NOT NULL THEN
3430 get_type_sql (
3431 p_aliasid => p_att_alias_table (12).alias_id,
3432 p_person_type => l_person_type,
3433 p_alias_typ => m_alias_typ,
3434 p_alias_sql => m_alias_sql,
3435 p_maxsize => m_alias_maxsize,
3436 p_minvalue => m_alias_minvalue,
3437 p_maxvalue => m_alias_maxvalue,
3438 p_precision => m_alias_precision,
3439 p_colmtype => m_alias_lovcoltype
3440 );
3441 p_att_alias_table (12).alias_sql := m_alias_sql;
3442 p_att_alias_table (12).alias_type := m_alias_typ;
3443 p_att_alias_table (12).alias_maxsize := m_alias_maxsize;
3444 p_att_alias_table (12).alias_minvalue := m_alias_minvalue;
3445 p_att_alias_table (12).alias_maxvalue := m_alias_maxvalue;
3446 p_att_alias_table (12).alias_precision := m_alias_precision;
3447 p_att_alias_table (12).alias_lovcoltype := m_alias_lovcoltype;
3448 END IF;
3449
3450 p_att_alias_table (13).attr_name := 'ATTRIBUTE13';
3451 p_att_alias_table (13).alias_id := l_pref_table (l_index).attribute13;
3452
3453 IF p_att_alias_table (13).alias_id IS NOT NULL THEN
3454 get_type_sql (
3455 p_aliasid => p_att_alias_table (13).alias_id,
3456 p_person_type => l_person_type,
3457 p_alias_typ => m_alias_typ,
3458 p_alias_sql => m_alias_sql,
3459 p_maxsize => m_alias_maxsize,
3460 p_minvalue => m_alias_minvalue,
3461 p_maxvalue => m_alias_maxvalue,
3462 p_precision => m_alias_precision,
3463 p_colmtype => m_alias_lovcoltype
3464 );
3465 p_att_alias_table (13).alias_sql := m_alias_sql;
3466 p_att_alias_table (13).alias_type := m_alias_typ;
3467 p_att_alias_table (13).alias_maxsize := m_alias_maxsize;
3468 p_att_alias_table (13).alias_minvalue := m_alias_minvalue;
3469 p_att_alias_table (13).alias_maxvalue := m_alias_maxvalue;
3470 p_att_alias_table (13).alias_precision := m_alias_precision;
3471 p_att_alias_table (13).alias_lovcoltype := m_alias_lovcoltype;
3472 END IF;
3473
3474 p_att_alias_table (14).attr_name := 'ATTRIBUTE14';
3475 p_att_alias_table (14).alias_id := l_pref_table (l_index).attribute14;
3476
3477 IF p_att_alias_table (14).alias_id IS NOT NULL THEN
3478 get_type_sql (
3479 p_aliasid => p_att_alias_table (14).alias_id,
3480 p_person_type => l_person_type,
3481 p_alias_typ => m_alias_typ,
3482 p_alias_sql => m_alias_sql,
3483 p_maxsize => m_alias_maxsize,
3484 p_minvalue => m_alias_minvalue,
3485 p_maxvalue => m_alias_maxvalue,
3486 p_precision => m_alias_precision,
3487 p_colmtype => m_alias_lovcoltype
3488 );
3489 p_att_alias_table (14).alias_sql := m_alias_sql;
3490 p_att_alias_table (14).alias_type := m_alias_typ;
3491 p_att_alias_table (14).alias_maxsize := m_alias_maxsize;
3492 p_att_alias_table (14).alias_minvalue := m_alias_minvalue;
3493 p_att_alias_table (14).alias_maxvalue := m_alias_maxvalue;
3494 p_att_alias_table (14).alias_precision := m_alias_precision;
3495 p_att_alias_table (14).alias_lovcoltype := m_alias_lovcoltype;
3496 END IF;
3497
3498 p_att_alias_table (15).attr_name := 'ATTRIBUTE15';
3499 p_att_alias_table (15).alias_id := l_pref_table (l_index).attribute15;
3500
3501 IF p_att_alias_table (15).alias_id IS NOT NULL THEN
3502 get_type_sql (
3503 p_aliasid => p_att_alias_table (15).alias_id,
3504 p_person_type => l_person_type,
3505 p_alias_typ => m_alias_typ,
3506 p_alias_sql => m_alias_sql,
3507 p_maxsize => m_alias_maxsize,
3508 p_minvalue => m_alias_minvalue,
3509 p_maxvalue => m_alias_maxvalue,
3510 p_precision => m_alias_precision,
3511 p_colmtype => m_alias_lovcoltype
3512 );
3513 p_att_alias_table (15).alias_sql := m_alias_sql;
3514 p_att_alias_table (15).alias_type := m_alias_typ;
3515 p_att_alias_table (15).alias_maxsize := m_alias_maxsize;
3516 p_att_alias_table (15).alias_minvalue := m_alias_minvalue;
3517 p_att_alias_table (15).alias_maxvalue := m_alias_maxvalue;
3518 p_att_alias_table (15).alias_precision := m_alias_precision;
3519 p_att_alias_table (15).alias_lovcoltype := m_alias_lovcoltype;
3520 END IF;
3521
3522 p_att_alias_table (16).attr_name := 'ATTRIBUTE16';
3523 p_att_alias_table (16).alias_id := l_pref_table (l_index).attribute16;
3524
3525 IF p_att_alias_table (16).alias_id IS NOT NULL THEN
3526 get_type_sql (
3527 p_aliasid => p_att_alias_table (16).alias_id,
3528 p_person_type => l_person_type,
3529 p_alias_typ => m_alias_typ,
3530 p_alias_sql => m_alias_sql,
3531 p_maxsize => m_alias_maxsize,
3532 p_minvalue => m_alias_minvalue,
3533 p_maxvalue => m_alias_maxvalue,
3534 p_precision => m_alias_precision,
3535 p_colmtype => m_alias_lovcoltype
3536 );
3537 p_att_alias_table (16).alias_sql := m_alias_sql;
3538 p_att_alias_table (16).alias_type := m_alias_typ;
3539 p_att_alias_table (16).alias_maxsize := m_alias_maxsize;
3540 p_att_alias_table (16).alias_minvalue := m_alias_minvalue;
3541 p_att_alias_table (16).alias_maxvalue := m_alias_maxvalue;
3542 p_att_alias_table (16).alias_precision := m_alias_precision;
3543 p_att_alias_table (16).alias_lovcoltype := m_alias_lovcoltype;
3544 END IF;
3545
3546 p_att_alias_table (17).attr_name := 'ATTRIBUTE17';
3547 p_att_alias_table (17).alias_id := l_pref_table (l_index).attribute17;
3548
3549 IF p_att_alias_table (17).alias_id IS NOT NULL THEN
3550 get_type_sql (
3551 p_aliasid => p_att_alias_table (17).alias_id,
3552 p_person_type => l_person_type,
3553 p_alias_typ => m_alias_typ,
3554 p_alias_sql => m_alias_sql,
3555 p_maxsize => m_alias_maxsize,
3556 p_minvalue => m_alias_minvalue,
3557 p_maxvalue => m_alias_maxvalue,
3558 p_precision => m_alias_precision,
3559 p_colmtype => m_alias_lovcoltype
3560 );
3561 p_att_alias_table (17).alias_sql := m_alias_sql;
3562 p_att_alias_table (17).alias_type := m_alias_typ;
3563 p_att_alias_table (17).alias_maxsize := m_alias_maxsize;
3564 p_att_alias_table (17).alias_minvalue := m_alias_minvalue;
3565 p_att_alias_table (17).alias_maxvalue := m_alias_maxvalue;
3566 p_att_alias_table (17).alias_precision := m_alias_precision;
3567 p_att_alias_table (17).alias_lovcoltype := m_alias_lovcoltype;
3568 END IF;
3569
3570 p_att_alias_table (18).attr_name := 'ATTRIBUTE18';
3571 p_att_alias_table (18).alias_id := l_pref_table (l_index).attribute18;
3572
3573 IF p_att_alias_table (18).alias_id IS NOT NULL THEN
3574 get_type_sql (
3575 p_aliasid => p_att_alias_table (18).alias_id,
3576 p_person_type => l_person_type,
3577 p_alias_typ => m_alias_typ,
3578 p_alias_sql => m_alias_sql,
3579 p_maxsize => m_alias_maxsize,
3580 p_minvalue => m_alias_minvalue,
3581 p_maxvalue => m_alias_maxvalue,
3582 p_precision => m_alias_precision,
3583 p_colmtype => m_alias_lovcoltype
3584 );
3585 p_att_alias_table (18).alias_sql := m_alias_sql;
3586 p_att_alias_table (18).alias_type := m_alias_typ;
3587 p_att_alias_table (18).alias_maxsize := m_alias_maxsize;
3588 p_att_alias_table (18).alias_minvalue := m_alias_minvalue;
3589 p_att_alias_table (18).alias_maxvalue := m_alias_maxvalue;
3590 p_att_alias_table (18).alias_precision := m_alias_precision;
3591 p_att_alias_table (18).alias_lovcoltype := m_alias_lovcoltype;
3592 END IF;
3593
3594 p_att_alias_table (19).attr_name := 'ATTRIBUTE19';
3595 p_att_alias_table (19).alias_id := l_pref_table (l_index).attribute19;
3596
3597 IF p_att_alias_table (19).alias_id IS NOT NULL THEN
3598 get_type_sql (
3599 p_aliasid => p_att_alias_table (19).alias_id,
3600 p_person_type => l_person_type,
3601 p_alias_typ => m_alias_typ,
3602 p_alias_sql => m_alias_sql,
3603 p_maxsize => m_alias_maxsize,
3604 p_minvalue => m_alias_minvalue,
3605 p_maxvalue => m_alias_maxvalue,
3606 p_precision => m_alias_precision,
3607 p_colmtype => m_alias_lovcoltype
3608 );
3609 p_att_alias_table (19).alias_sql := m_alias_sql;
3610 p_att_alias_table (19).alias_type := m_alias_typ;
3611 p_att_alias_table (19).alias_maxsize := m_alias_maxsize;
3612 p_att_alias_table (19).alias_minvalue := m_alias_minvalue;
3613 p_att_alias_table (19).alias_maxvalue := m_alias_maxvalue;
3614 p_att_alias_table (19).alias_precision := m_alias_precision;
3615 p_att_alias_table (19).alias_lovcoltype := m_alias_lovcoltype;
3616 END IF;
3617
3618 p_att_alias_table (20).attr_name := 'ATTRIBUTE20';
3619 p_att_alias_table (20).alias_id := l_pref_table (l_index).attribute20;
3620
3621 IF p_att_alias_table (20).alias_id IS NOT NULL THEN
3622 get_type_sql (
3623 p_aliasid => p_att_alias_table (20).alias_id,
3624 p_person_type => l_person_type,
3625 p_alias_typ => m_alias_typ,
3626 p_alias_sql => m_alias_sql,
3627 p_maxsize => m_alias_maxsize,
3628 p_minvalue => m_alias_minvalue,
3629 p_maxvalue => m_alias_maxvalue,
3630 p_precision => m_alias_precision,
3631 p_colmtype => m_alias_lovcoltype
3632 );
3633 p_att_alias_table (20).alias_sql := m_alias_sql;
3634 p_att_alias_table (20).alias_type := m_alias_typ;
3635 p_att_alias_table (20).alias_maxsize := m_alias_maxsize;
3636 p_att_alias_table (20).alias_minvalue := m_alias_minvalue;
3637 p_att_alias_table (20).alias_maxvalue := m_alias_maxvalue;
3638 p_att_alias_table (20).alias_precision := m_alias_precision;
3639 p_att_alias_table (20).alias_lovcoltype := m_alias_lovcoltype;
3640 END IF;
3641
3642 ELSIF (l_pref_table (l_index).preference_code = 'TS_ABS_PREFERENCES') THEN
3643
3644 if ( nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'),'N') = 'Y'
3645 and nvl(l_pref_table (l_index).attribute1,'N') = 'Y'
3646 and nvl(l_pref_table (l_index).attribute5,'N') = 'Y') then
3647
3648 hxc_timekeeper_utilities.g_tk_show_absences:= 1;
3649
3650 else
3651
3652 hxc_timekeeper_utilities.g_tk_show_absences:= 0;
3653
3654 end if;
3655
3656 END IF;
3657
3658 l_index := l_pref_table.NEXT (l_index);
3659 END LOOP;
3660 END;
3661
3662
3663 -- ----------------------------------------------------------------------------
3664 -- Used in form in post query to enable /disable days in timecard row
3665 -- ----------------------------------------------------------------------------
3666 PROCEDURE populate_disable_tc_tab (
3667 resource_id IN NUMBER,
3668 tc_frdt IN DATE,
3669 tc_todt IN DATE,
3670 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab
3671 ) IS
3672 CURSOR get_tc_data (
3673 p_resource_id NUMBER,
3674 p_tc_frdt DATE,
3675 p_tc_todt DATE
3676 ) IS
3677 SELECT time_building_block_id tbbid, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
3678 FROM hxc_time_building_blocks
3679 WHERE resource_id
3680 /*ADVICE(3110): Cursor references an external variable (use a parameter) [209] */
3681 = p_resource_id
3682 AND SCOPE = 'TIMECARD'
3683 AND date_to = hr_general.end_of_time
3684 AND TO_DATE (start_time, 'dd-mm-rrrr') < TO_DATE (p_tc_todt, 'dd-mm-rrrr')
3685 AND TO_DATE (stop_time, 'dd-mm-rrrr') > TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
3686 AND ( TO_DATE (start_time, 'dd-mm-rrrr') <> TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
3687 OR TO_DATE (stop_time, 'dd-mm-rrrr') <> TO_DATE (p_tc_todt, 'dd-mm-rrrr')
3688 )
3689 ORDER BY 4;
3690
3691 tc_tab_rec get_tc_data%ROWTYPE;
3692 p_index NUMBER
3693 /*ADVICE(3123): NUMBER has no precision [315] */
3694 := 0;
3695 BEGIN
3696 OPEN get_tc_data (resource_id, tc_frdt, tc_todt);
3697
3698 LOOP
3699 FETCH get_tc_data INTO tc_tab_rec;
3700 EXIT WHEN get_tc_data%NOTFOUND;
3701
3702 IF p_emptcinfo.COUNT > 0 THEN
3703 p_index := p_emptcinfo.LAST + 1;
3704 ELSE
3705 p_index := 1;
3706 END IF;
3707
3708 p_emptcinfo (p_index).timecard_id := tc_tab_rec.tbbid;
3709 p_emptcinfo (p_index).resource_id := resource_id;
3710 p_emptcinfo (p_index).tc_frdt := tc_tab_rec.start_time;
3711 p_emptcinfo (p_index).tc_todt := tc_tab_rec.stop_time;
3712 END LOOP;
3713
3714 CLOSE get_tc_data;
3715 END;
3716
3717
3718 -- ----------------------------------------------------------------------------
3719 -- Used in form to enable /disable days in timecard row
3720 -- when new row is created for a person manually
3721 -- ----------------------------------------------------------------------------
3722 PROCEDURE new_timecard (
3723 p_resource_id IN NUMBER,
3724 p_start_date IN DATE,
3725 p_end_date IN DATE,
3726 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab
3727 ) IS
3728 m_periods
3729 /*ADVICE(3159): Unreferenced variable [553] */
3730 VARCHAR2 (2000);
3731 /*ADVICE(3161): VARCHAR2 declaration with length greater than 500 characters [307] */
3732
3733 l_newtab hxc_timecard_utilities.periods;
3734 l_emp_tab_index NUMBER
3735 /*ADVICE(3165): NUMBER has no precision [315] */
3736 := 0;
3737 l_new_tab_index
3738 /*ADVICE(3168): Unreferenced variable [553] */
3739 NUMBER
3740 /*ADVICE(3170): NUMBER has no precision [315] */
3741 := 0;
3742 l_emp_negpref
3743 /*ADVICE(3173): This item should be defined in a deeper scope [558] */
3744 VARCHAR2 (150);
3745 l_emp_recpref NUMBER
3746 /*ADVICE(3176): NUMBER has no precision [315] */
3747 ;
3748 l_emp_appstyle
3749 /*ADVICE(3179): This item should be defined in a deeper scope [558] */
3750 NUMBER
3751 /*ADVICE(3181): NUMBER has no precision [315] */
3752 ;
3753 l_emp_layout1
3754 /*ADVICE(3184): This item should be defined in a deeper scope [558] */
3755 NUMBER
3756 /*ADVICE(3186): NUMBER has no precision [315] */
3757 ;
3758 l_emp_layout2
3759 /*ADVICE(3189): This item should be defined in a deeper scope [558] */
3760 NUMBER
3761 /*ADVICE(3191): NUMBER has no precision [315] */
3762 ;
3763 l_emp_layout3
3764 /*ADVICE(3194): This item should be defined in a deeper scope [558] */
3765 NUMBER
3766 /*ADVICE(3196): NUMBER has no precision [315] */
3767 ;
3768 l_emp_layout4
3769 /*ADVICE(3199): This item should be defined in a deeper scope [558] */
3770 NUMBER
3771 /*ADVICE(3201): NUMBER has no precision [315] */
3772 ;
3773 l_emp_layout5
3774 /*ADVICE(3204): This item should be defined in a deeper scope [558] */
3775 NUMBER
3776 /*ADVICE(3206): NUMBER has no precision [315] */
3777 ;
3778 l_emp_layout6
3779 /*ADVICE(3209): This item should be defined in a deeper scope [558] */
3780 NUMBER
3781 /*ADVICE(3211): NUMBER has no precision [315] */
3782 ;
3783 l_emp_layout7
3784 /*ADVICE(3214): This item should be defined in a deeper scope [558] */
3785 NUMBER
3786 /*ADVICE(3216): NUMBER has no precision [315] */
3787 ;
3788 l_emp_layout8
3789 /*ADVICE(3219): This item should be defined in a deeper scope [558] */
3790 NUMBER
3791 /*ADVICE(3221): NUMBER has no precision [315] */
3792 ;
3793 l_emp_edits
3794 /*ADVICE(3224): This item should be defined in a deeper scope [558] */
3795 VARCHAR2 (150);
3796 l_pastdt
3797 /*ADVICE(3227): This item should be defined in a deeper scope [558] */
3798 VARCHAR2 (30);
3799 l_futuredt
3800 /*ADVICE(3230): This item should be defined in a deeper scope [558] */
3801 VARCHAR2 (30);
3802 l_emp_start_date
3803 /*ADVICE(3233): This item should be defined in a deeper scope [558] */
3804 DATE;
3805 l_emp_terminate_date
3806 /*ADVICE(3233): This item should be defined in a deeper scope [558] */
3807 DATE;
3808 l_index NUMBER
3809 /*ADVICE(3236): NUMBER has no precision [315] */
3810 := 0;
3811 l_audit_enabled
3812 /*ADVICE(3239): This item should be defined in a deeper scope [558] */
3813 VARCHAR2 (150);
3814 BEGIN
3815 BEGIN
3816 hxc_timekeeper_utilities.get_emp_pref (
3817 p_resource_id,
3818 l_emp_negpref,
3819 l_emp_recpref,
3820 l_emp_appstyle,
3821 l_emp_layout1,
3822 l_emp_layout2,
3823 l_emp_layout3,
3824 l_emp_layout4,
3825 l_emp_layout5,
3826 l_emp_layout6,
3827 l_emp_layout7,
3828 l_emp_layout8,
3829 l_emp_edits,
3830 l_pastdt,
3831 l_futuredt,
3832 l_emp_start_date,
3833 l_emp_terminate_date,
3834 l_audit_enabled
3835 );
3836 EXCEPTION
3837 WHEN OTHERS THEN
3838 l_emp_recpref := NULL;
3839 /*ADVICE(3265): A WHEN OTHERS clause is used in the exception section without any other specific handlers
3840 [201] */
3841
3842 END;
3843
3844 l_newtab.DELETE;
3845 hxc_timekeeper_utilities.get_resource_time_periods (
3846 p_resource_id => p_resource_id,
3847 p_resource_type => 'PERSON',
3848 p_current_date => SYSDATE,
3849 p_max_date_in_futur => TO_DATE (p_end_date, 'dd-mm-rrrr') + 1,
3850 p_max_date_in_past => TO_DATE (p_start_date, 'dd-mm-rrrr') - 1,
3851 p_recurring_period_id => l_emp_recpref,
3852 p_check_assignment => TRUE,
3853 p_periodtab => l_newtab
3854 );
3855 l_emp_tab_index := l_newtab.FIRST;
3856
3857 LOOP
3858 EXIT WHEN NOT l_newtab.EXISTS (l_emp_tab_index);
3859
3860 IF TO_NUMBER (l_emp_tab_index) >= TO_NUMBER (TO_CHAR (TO_DATE (p_start_date, 'dd-mm-rrrr'), 'J'))
3861 AND TO_NUMBER (TO_CHAR (l_newtab (l_emp_tab_index).end_date, 'J')) <=
3862 TO_NUMBER (TO_CHAR (TO_DATE (p_end_date, 'dd-mm-rrrr'), 'J')) THEN
3863 IF p_emptcinfo.COUNT > 0 THEN
3864 l_index := p_emptcinfo.LAST + 1;
3865 ELSE
3866 l_index := 1;
3867 END IF;
3868
3869 p_emptcinfo (l_index).resource_id := p_resource_id;
3870 p_emptcinfo (l_index).tc_frdt := l_newtab (l_emp_tab_index).start_date;
3871 p_emptcinfo (l_index).tc_todt := l_newtab (l_emp_tab_index).end_date;
3872 END IF;
3873
3874 l_emp_tab_index := l_newtab.NEXT (l_emp_tab_index);
3875 END LOOP;
3876 END;
3877
3878 -------------------------
3879 --POP_DETAIL_TEMP
3880 -----------------
3881 /* This procedure is used to populate the pl/sql blocks from hxc_tk_detail_temp after the save process
3882 and then repoulate it back to the table once database commit is triggered from the forms
3883 has one parameter action, it has following values
3884 1 - hxc_tk_detail_temp--->pl/sql table
3885 2 - pl/sql table --> hxc_tk_detail_temp
3886 */
3887 procedure populate_detail_temp(p_action in number) is
3888 j binary_integer :=0;
3889 indx binary_integer;
3890 l_detail_blocks hxc_tk_detail_temp_tab;
3891 cursor csr_tk is select * from hxc_tk_detail_temp;
3892 begin
3893 l_detail_blocks:=hxc_timekeeper_utilities.g_hxc_tk_detail_temp_tab;
3894 if p_action =1 then
3895 open csr_tk;
3896 loop
3897 j:=j+1;
3898 /* Fetch entire row into record stored by jth element. */
3899 fetch csr_tk into l_detail_blocks(j);
3900 exit when csr_tk%NOTFOUND;
3901 end loop;
3902 close csr_tk;
3903
3904 ELSE
3905 /* start of fix for 5398144 */
3906 if l_detail_blocks.count > 0 then
3907 FOR indx IN l_detail_blocks.FIRST .. l_detail_blocks.LAST loop
3908 insert into hxc_tk_detail_temp
3909 (RESOURCE_ID,
3910 TIMECARD_ID,
3911 DETAILID,
3912 COMMENT_TEXT,
3913 DFF_CATG ,
3914 DETAIL_ACTION ,
3915 DFF_ATTR1 ,
3916 DFF_ATTR2 ,
3917 DFF_ATTR3 ,
3918 DFF_ATTR4 ,
3919 DFF_ATTR5 ,
3920 DFF_ATTR6 ,
3921 DFF_ATTR7 ,
3922 DFF_ATTR8 ,
3923 DFF_ATTR9 ,
3924 DFF_ATTR10 ,
3925 DFF_ATTR11 ,
3926 DFF_ATTR12 ,
3927 DFF_ATTR13 ,
3928 DFF_ATTR14 ,
3929 DFF_ATTR15 ,
3930 DFF_ATTR16 ,
3931 DFF_ATTR17 ,
3932 DFF_ATTR18 ,
3933 DFF_ATTR19 ,
3934 DFF_ATTR20 ,
3935 DFF_ATTR21 ,
3936 DFF_ATTR22 ,
3937 DFF_ATTR23 ,
3938 DFF_ATTR24 ,
3939 DFF_ATTR25 ,
3940 DFF_ATTR26 ,
3941 DFF_ATTR27 ,
3942 DFF_ATTR28 ,
3943 DFF_ATTR29 ,
3944 DFF_ATTR30 ,
3945 DFF_OLDATTR1 ,
3946 DFF_OLDATTR2 ,
3947 DFF_OLDATTR3 ,
3948 DFF_OLDATTR4 ,
3949 DFF_OLDATTR5 ,
3950 DFF_OLDATTR6 ,
3951 DFF_OLDATTR7 ,
3952 DFF_OLDATTR8 ,
3953 DFF_OLDATTR9 ,
3954 DFF_OLDATTR10 ,
3955 DFF_OLDATTR11 ,
3956 DFF_OLDATTR12 ,
3957 DFF_OLDATTR13 ,
3958 DFF_OLDATTR14 ,
3959 DFF_OLDATTR15 ,
3960 DFF_OLDATTR16 ,
3961 DFF_OLDATTR17 ,
3962 DFF_OLDATTR18 ,
3963 DFF_OLDATTR19 ,
3964 DFF_OLDATTR20 ,
3965 DFF_OLDATTR21 ,
3966 DFF_OLDATTR22 ,
3967 DFF_OLDATTR23 ,
3968 DFF_OLDATTR24 ,
3969 DFF_OLDATTR25 ,
3970 DFF_OLDATTR26 ,
3971 DFF_OLDATTR27 ,
3972 DFF_OLDATTR28 ,
3973 DFF_OLDATTR29 ,
3974 DFF_OLDATTR30 ,
3975 DFF_OLDCATG ,
3976 CHANGE_REASON ,
3977 CHANGE_COMMENT ,
3978 LATE_REASON ,
3979 LATE_COMMENT ,
3980 LATE_CHANGE ,
3981 DESC_FLEX ,
3982 ATTRIBUTE_CATEGORY ,
3983 AUDIT_DATETIME ,
3984 AUDIT_HISTORY ,
3985 DISP_INDEX ,
3986 OLD_CHANGE_REASON ,
3987 OLD_CHANGE_COMMENT ,
3988 OLD_LATE_REASON ,
3989 OLD_LATE_COMMENT ,
3990 OLD_AUDIT_HISTORY ,
3991 OLD_LATE_CHANGE ,
3992 OLD_AUDIT_DATETIME )
3993 values
3994 (
3995 l_detail_blocks(indx).RESOURCE_ID,
3996 l_detail_blocks(indx).TIMECARD_ID,
3997 l_detail_blocks(indx).DETAILID,
3998 l_detail_blocks(indx).COMMENT_TEXT,
3999 l_detail_blocks(indx).DFF_CATG ,
4000 l_detail_blocks(indx).DETAIL_ACTION ,
4001 l_detail_blocks(indx).DFF_ATTR1 ,
4002 l_detail_blocks(indx).DFF_ATTR2 ,
4003 l_detail_blocks(indx).DFF_ATTR3 ,
4004 l_detail_blocks(indx).DFF_ATTR4 ,
4005 l_detail_blocks(indx).DFF_ATTR5 ,
4006 l_detail_blocks(indx).DFF_ATTR6 ,
4007 l_detail_blocks(indx).DFF_ATTR7 ,
4008 l_detail_blocks(indx).DFF_ATTR8 ,
4009 l_detail_blocks(indx).DFF_ATTR9 ,
4010 l_detail_blocks(indx).DFF_ATTR10 ,
4011 l_detail_blocks(indx).DFF_ATTR11 ,
4012 l_detail_blocks(indx).DFF_ATTR12 ,
4013 l_detail_blocks(indx).DFF_ATTR13 ,
4014 l_detail_blocks(indx).DFF_ATTR14 ,
4015 l_detail_blocks(indx).DFF_ATTR15 ,
4016 l_detail_blocks(indx).DFF_ATTR16 ,
4017 l_detail_blocks(indx).DFF_ATTR17 ,
4018 l_detail_blocks(indx).DFF_ATTR18 ,
4019 l_detail_blocks(indx).DFF_ATTR19 ,
4020 l_detail_blocks(indx).DFF_ATTR20 ,
4021 l_detail_blocks(indx).DFF_ATTR21 ,
4022 l_detail_blocks(indx).DFF_ATTR22 ,
4023 l_detail_blocks(indx).DFF_ATTR23 ,
4024 l_detail_blocks(indx).DFF_ATTR24 ,
4025 l_detail_blocks(indx).DFF_ATTR25 ,
4026 l_detail_blocks(indx).DFF_ATTR26 ,
4027 l_detail_blocks(indx).DFF_ATTR27 ,
4028 l_detail_blocks(indx).DFF_ATTR28 ,
4029 l_detail_blocks(indx).DFF_ATTR29 ,
4030 l_detail_blocks(indx).DFF_ATTR30 ,
4031 l_detail_blocks(indx).DFF_OLDATTR1 ,
4032 l_detail_blocks(indx).DFF_OLDATTR2 ,
4033 l_detail_blocks(indx).DFF_OLDATTR3 ,
4034 l_detail_blocks(indx).DFF_OLDATTR4 ,
4035 l_detail_blocks(indx).DFF_OLDATTR5 ,
4036 l_detail_blocks(indx).DFF_OLDATTR6 ,
4037 l_detail_blocks(indx).DFF_OLDATTR7 ,
4038 l_detail_blocks(indx).DFF_OLDATTR8 ,
4039 l_detail_blocks(indx).DFF_OLDATTR9 ,
4040 l_detail_blocks(indx).DFF_OLDATTR10 ,
4041 l_detail_blocks(indx).DFF_OLDATTR11 ,
4042 l_detail_blocks(indx).DFF_OLDATTR12 ,
4043 l_detail_blocks(indx).DFF_OLDATTR13 ,
4044 l_detail_blocks(indx).DFF_OLDATTR14 ,
4045 l_detail_blocks(indx).DFF_OLDATTR15 ,
4046 l_detail_blocks(indx).DFF_OLDATTR16 ,
4047 l_detail_blocks(indx).DFF_OLDATTR17 ,
4048 l_detail_blocks(indx).DFF_OLDATTR18 ,
4049 l_detail_blocks(indx).DFF_OLDATTR19 ,
4050 l_detail_blocks(indx).DFF_OLDATTR20 ,
4051 l_detail_blocks(indx).DFF_OLDATTR21 ,
4052 l_detail_blocks(indx).DFF_OLDATTR22 ,
4053 l_detail_blocks(indx).DFF_OLDATTR23 ,
4054 l_detail_blocks(indx).DFF_OLDATTR24 ,
4055 l_detail_blocks(indx).DFF_OLDATTR25 ,
4056 l_detail_blocks(indx).DFF_OLDATTR26 ,
4057 l_detail_blocks(indx).DFF_OLDATTR27 ,
4058 l_detail_blocks(indx).DFF_OLDATTR28 ,
4059 l_detail_blocks(indx).DFF_OLDATTR29 ,
4060 l_detail_blocks(indx).DFF_OLDATTR30 ,
4061 l_detail_blocks(indx).DFF_OLDCATG ,
4062 l_detail_blocks(indx).CHANGE_REASON ,
4063 l_detail_blocks(indx).CHANGE_COMMENT ,
4064 l_detail_blocks(indx).LATE_REASON ,
4065 l_detail_blocks(indx).LATE_COMMENT ,
4066 l_detail_blocks(indx).LATE_CHANGE ,
4067 l_detail_blocks(indx).DESC_FLEX ,
4068 l_detail_blocks(indx).ATTRIBUTE_CATEGORY ,
4069 l_detail_blocks(indx).AUDIT_DATETIME ,
4070 l_detail_blocks(indx).AUDIT_HISTORY ,
4071 l_detail_blocks(indx).DISP_INDEX ,
4072 l_detail_blocks(indx).OLD_CHANGE_REASON ,
4073 l_detail_blocks(indx).OLD_CHANGE_COMMENT ,
4074 l_detail_blocks(indx).OLD_LATE_REASON ,
4075 l_detail_blocks(indx).OLD_LATE_COMMENT ,
4076 l_detail_blocks(indx).OLD_AUDIT_HISTORY ,
4077 l_detail_blocks(indx).OLD_LATE_CHANGE ,
4078 l_detail_blocks(indx).OLD_AUDIT_DATETIME );
4079 end loop;
4080 END if;
4081 /* end of fix for 5398144 */
4082 end if;
4083 hxc_timekeeper_utilities.g_hxc_tk_detail_temp_tab:=l_detail_blocks;
4084 end;
4085
4086
4087 FUNCTION get_exp_type_from_alias (
4088 p_alias_value_id IN VARCHAR2
4089 )
4090 RETURN VARCHAR2 IS
4091 CURSOR c_alias_to_pa_info (
4092 p_id IN hxc_alias_values.alias_value_id%TYPE
4093 ) IS
4094 SELECT attribute2
4095 FROM hxc_alias_values
4096 WHERE alias_value_id = p_id AND attribute_category = 'ELEMENTS_EXPENDITURE_SLF';
4097
4098 l_expenditure_type VARCHAR2 (250);
4099 BEGIN
4100 OPEN c_alias_to_pa_info (p_alias_value_id);
4101 FETCH c_alias_to_pa_info INTO l_expenditure_type;
4102 CLOSE c_alias_to_pa_info;
4103 RETURN l_expenditure_type;
4104 END get_exp_type_from_alias;
4105
4106 FUNCTION check_global_context (
4107 p_context_prefix IN VARCHAR2
4108 )
4109 RETURN BOOLEAN IS
4110 l_dummy VARCHAR2 (10);
4111 BEGIN
4112 SELECT 'Y'
4113 INTO l_dummy
4114 FROM fnd_descr_flex_contexts
4115 WHERE application_id = 809
4116 AND descriptive_flexfield_name = 'OTC Information Types'
4117 AND descriptive_flex_context_code LIKE p_context_prefix || '%GLOBAL%';
4118
4119 RETURN TRUE;
4120 EXCEPTION
4121 WHEN NO_DATA_FOUND THEN
4122 RETURN FALSE;
4123 END;
4124
4125
4126 /*Added for Enh 3303359
4127 Caching the pref value for Default Recurring Period
4128
4129 A form cant reference pkg global variables and hence a separate function to
4130 get the global variable
4131
4132 */
4133
4134
4135 FUNCTION get_pref_setting(p_pref IN VARCHAR2)
4136 RETURN NUMBER
4137 is
4138
4139 begin
4140
4141 if p_pref is null then
4142
4143 return -1;
4144
4145 elsif p_pref = 'DEFAULT_RECURRING_PERIOD' then
4146
4147 return to_number(g_default_rec_period);
4148
4149 elsif p_pref = 'VIEW_ABSENCE_STATUS' then
4150
4151 return to_number(g_tk_show_absences);
4152
4153 else
4154
4155 return -1;
4156
4157 end if;
4158
4159 END; -- get_pref_setting
4160
4161
4162
4163
4164
4165 /*Added 3 procs for HR OTL Absence Integration: Bug: 8775740
4166 --SVG START
4167 */
4168
4169 ---------------------------------------------------------------------------------------
4170
4171 FUNCTION get_pref_eval_date
4172 (p_resource_id IN NUMBER
4173 ,p_tc_start_date IN DATE
4174 ,p_tc_end_date IN DATE)
4175
4176 RETURN DATE
4177
4178 IS
4179
4180 Cursor active_periods(p_resource_id IN NUMBER
4181 ,p_tc_start_date IN DATE
4182 ,p_tc_end_date IN DATE)
4183 is
4184
4185 SELECT date_start,
4186 final_process_date
4187 FROM per_periods_of_service
4188 WHERE person_id = p_resource_id
4189 AND date_start <= p_tc_end_date
4190 AND COALESCE(final_process_date,
4191 actual_termination_date,
4192 hr_general.end_of_time) >= p_tc_start_date
4193 UNION
4194 SELECT date_start,
4195 (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date
4196 FROM per_periods_of_placement
4197 WHERE person_id = p_resource_id
4198 AND date_start <= p_tc_end_date
4199 AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0))
4200 ,actual_termination_date,
4201 hr_general.end_of_time) >= p_tc_start_date
4202 ORDER BY date_start ;
4203
4204
4205 l_start_date DATE;
4206 l_end_date DATE;
4207
4208
4209 BEGIN
4210
4211 if g_debug then
4212
4213 hr_utility.trace('Entered get_pref_eval_date ');
4214
4215 end if;
4216
4217 OPEN active_periods(p_resource_id,
4218 p_tc_start_date,
4219 p_tc_end_date);
4220
4221 if g_debug then
4222
4223 hr_utility.trace('Opened active_periods ');
4224
4225 end if;
4226
4227 FETCH active_periods into l_start_date, l_end_date;
4228
4229 if g_debug then
4230
4231 hr_utility.trace('Fetched active_periods ');
4232 hr_utility.trace('l_start_date = '||l_start_date);
4233 hr_utility.trace('l_end_date = '||l_end_date);
4234 hr_utility.trace('p_tc_start_date = '||p_tc_start_date);
4235 hr_utility.trace('p_tc_end_date = '||p_tc_end_date);
4236
4237 end if;
4238
4239 if (p_tc_start_date between nvl(l_start_date, hr_general.start_of_time)
4240 and nvl(l_end_date, hr_general.end_of_time)) then
4241
4242 if g_debug then
4243
4244 hr_utility.trace('Going to return p_tc_start_date ='|| p_tc_start_date);
4245
4246 end if;
4247
4248 return p_tc_start_date;
4249
4250 else
4251
4252 if g_debug then
4253
4254 hr_utility.trace('Going to return l_start_date ='|| l_start_date);
4255
4256 end if;
4257
4258
4259 return l_start_date;
4260 end if;
4261
4262
4263 END; --get_pref_eval_date
4264 ---------------------------------------------------------------------------------------
4265
4266
4267 PROCEDURE populate_prepop_detail_id_info
4268 (p_timekeeper_data_rec IN hxc_timekeeper_process.t_time_info,
4269 p_tk_prepop_detail_id_tab IN OUT NOCOPY hxc_timekeeper_process.g_tk_prepop_detail_id_tab_type)
4270
4271 IS
4272
4273 l_index NUMBER;
4274
4275
4276 BEGIN
4277
4278 if g_debug then
4279 hr_utility.trace(' Entered hxctkutil.populate_prepop_detail_id_info');
4280 end if;
4281
4282 if (p_timekeeper_data_rec.detail_id_1 is not null and
4283 p_timekeeper_data_rec.detail_id_1 < 0 ) then
4284
4285 l_index:= p_timekeeper_data_rec.detail_id_1 * -1 ;
4286
4287 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4288
4289 end if;
4290
4291 if (p_timekeeper_data_rec.detail_id_2 is not null and
4292 p_timekeeper_data_rec.detail_id_2 < 0 ) then
4293
4294 l_index:= p_timekeeper_data_rec.detail_id_2 * -1 ;
4295
4296 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4297
4298 end if;
4299
4300 if (p_timekeeper_data_rec.detail_id_3 is not null and
4301 p_timekeeper_data_rec.detail_id_3 < 0 ) then
4302
4303 l_index:= p_timekeeper_data_rec.detail_id_3 * -1 ;
4304
4305 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4306
4307 end if;
4308
4309 if (p_timekeeper_data_rec.detail_id_4 is not null and
4310 p_timekeeper_data_rec.detail_id_4 < 0 ) then
4311
4312 l_index:= p_timekeeper_data_rec.detail_id_4 * -1 ;
4313
4314 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4315
4316 end if;
4317
4318 if (p_timekeeper_data_rec.detail_id_5 is not null and
4319 p_timekeeper_data_rec.detail_id_5 < 0 ) then
4320
4321 l_index:= p_timekeeper_data_rec.detail_id_5 * -1 ;
4322
4323 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4324
4325 end if;
4326
4327 if (p_timekeeper_data_rec.detail_id_6 is not null and
4328 p_timekeeper_data_rec.detail_id_6 < 0 ) then
4329
4330 l_index:= p_timekeeper_data_rec.detail_id_6 * -1 ;
4331
4332 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4333
4334 end if;
4335
4336 if (p_timekeeper_data_rec.detail_id_7 is not null and
4337 p_timekeeper_data_rec.detail_id_7 < 0 ) then
4338
4339 l_index:= p_timekeeper_data_rec.detail_id_7 * -1 ;
4340
4341 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4342
4343 end if;
4344
4345 if (p_timekeeper_data_rec.detail_id_8 is not null and
4346 p_timekeeper_data_rec.detail_id_8 < 0 ) then
4347
4348 l_index:= p_timekeeper_data_rec.detail_id_8 * -1 ;
4349
4350 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4351
4352 end if;
4353
4354 if (p_timekeeper_data_rec.detail_id_9 is not null and
4355 p_timekeeper_data_rec.detail_id_9 < 0 ) then
4356
4357 l_index:= p_timekeeper_data_rec.detail_id_9 * -1 ;
4358
4359 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4360
4361 end if;
4362
4363 if (p_timekeeper_data_rec.detail_id_10 is not null and
4364 p_timekeeper_data_rec.detail_id_10 < 0 ) then
4365
4366 l_index:= p_timekeeper_data_rec.detail_id_10 * -1 ;
4367
4368 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4369
4370 end if;
4371
4372 if (p_timekeeper_data_rec.detail_id_11 is not null and
4373 p_timekeeper_data_rec.detail_id_11 < 0 ) then
4374
4375 l_index:= p_timekeeper_data_rec.detail_id_11 * -1 ;
4376
4377 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4378
4379 end if;
4380
4381 if (p_timekeeper_data_rec.detail_id_12 is not null and
4382 p_timekeeper_data_rec.detail_id_12 < 0 ) then
4383
4384 l_index:= p_timekeeper_data_rec.detail_id_12 * -1 ;
4385
4386 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4387
4388 end if;
4389
4390 if (p_timekeeper_data_rec.detail_id_13 is not null and
4391 p_timekeeper_data_rec.detail_id_13 < 0 ) then
4392
4393 l_index:= p_timekeeper_data_rec.detail_id_13 * -1 ;
4394
4395 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4396
4397 end if;
4398
4399 if (p_timekeeper_data_rec.detail_id_14 is not null and
4400 p_timekeeper_data_rec.detail_id_14 < 0 ) then
4401
4402 l_index:= p_timekeeper_data_rec.detail_id_14 * -1 ;
4403
4404 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4405
4406 end if;
4407
4408 if (p_timekeeper_data_rec.detail_id_15 is not null and
4409 p_timekeeper_data_rec.detail_id_15 < 0 ) then
4410
4411 l_index:= p_timekeeper_data_rec.detail_id_15 * -1 ;
4412
4413 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4414
4415 end if;
4416
4417 if (p_timekeeper_data_rec.detail_id_16 is not null and
4418 p_timekeeper_data_rec.detail_id_16 < 0 ) then
4419
4420 l_index:= p_timekeeper_data_rec.detail_id_16 * -1 ;
4421
4422 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4423
4424 end if;
4425
4426 if (p_timekeeper_data_rec.detail_id_17 is not null and
4427 p_timekeeper_data_rec.detail_id_17 < 0 ) then
4428
4429 l_index:= p_timekeeper_data_rec.detail_id_17 * -1 ;
4430
4431 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4432
4433 end if;
4434
4435 if (p_timekeeper_data_rec.detail_id_18 is not null and
4436 p_timekeeper_data_rec.detail_id_18 < 0 ) then
4437
4438 l_index:= p_timekeeper_data_rec.detail_id_18 * -1 ;
4439
4440 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4441
4442 end if;
4443
4444 if (p_timekeeper_data_rec.detail_id_19 is not null and
4445 p_timekeeper_data_rec.detail_id_19 < 0 ) then
4446
4447 l_index:= p_timekeeper_data_rec.detail_id_19 * -1 ;
4448
4449 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4450
4451 end if;
4452
4453 if (p_timekeeper_data_rec.detail_id_20 is not null and
4454 p_timekeeper_data_rec.detail_id_20 < 0 ) then
4455
4456 l_index:= p_timekeeper_data_rec.detail_id_20 * -1 ;
4457
4458 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4459
4460 end if;
4461
4462 if (p_timekeeper_data_rec.detail_id_21 is not null and
4463 p_timekeeper_data_rec.detail_id_21 < 0 ) then
4464
4465 l_index:= p_timekeeper_data_rec.detail_id_21 * -1 ;
4466
4467 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4468
4469 end if;
4470
4471 if (p_timekeeper_data_rec.detail_id_22 is not null and
4472 p_timekeeper_data_rec.detail_id_22 < 0 ) then
4473
4474 l_index:= p_timekeeper_data_rec.detail_id_22 * -1 ;
4475
4476 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4477
4478 end if;
4479
4480 if (p_timekeeper_data_rec.detail_id_23 is not null and
4481 p_timekeeper_data_rec.detail_id_23 < 0 ) then
4482
4483 l_index:= p_timekeeper_data_rec.detail_id_23 * -1 ;
4484
4485 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4486
4487 end if;
4488
4489
4490 if (p_timekeeper_data_rec.detail_id_24 is not null and
4491 p_timekeeper_data_rec.detail_id_24 < 0 ) then
4492
4493 l_index:= p_timekeeper_data_rec.detail_id_24 * -1 ;
4494
4495 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4496
4497 end if;
4498
4499 if (p_timekeeper_data_rec.detail_id_25 is not null and
4500 p_timekeeper_data_rec.detail_id_25 < 0 ) then
4501
4502 l_index:= p_timekeeper_data_rec.detail_id_25 * -1 ;
4503
4504 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4505
4506 end if;
4507
4508 if (p_timekeeper_data_rec.detail_id_26 is not null and
4509 p_timekeeper_data_rec.detail_id_26 < 0 ) then
4510
4511 l_index:= p_timekeeper_data_rec.detail_id_26 * -1 ;
4512
4513 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4514
4515 end if;
4516
4517 if (p_timekeeper_data_rec.detail_id_27 is not null and
4518 p_timekeeper_data_rec.detail_id_27 < 0 ) then
4519
4520 l_index:= p_timekeeper_data_rec.detail_id_27 * -1 ;
4521
4522 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4523
4524 end if;
4525
4526 if (p_timekeeper_data_rec.detail_id_28 is not null and
4527 p_timekeeper_data_rec.detail_id_28 < 0 ) then
4528
4529 l_index:= p_timekeeper_data_rec.detail_id_28 * -1 ;
4530
4531 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4532
4533 end if;
4534
4535 if (p_timekeeper_data_rec.detail_id_29 is not null and
4536 p_timekeeper_data_rec.detail_id_29 < 0 ) then
4537
4538 l_index:= p_timekeeper_data_rec.detail_id_29 * -1 ;
4539
4540 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4541
4542 end if;
4543
4544 if (p_timekeeper_data_rec.detail_id_30 is not null and
4545 p_timekeeper_data_rec.detail_id_30 < 0 ) then
4546
4547 l_index:= p_timekeeper_data_rec.detail_id_30 * -1 ;
4548
4549 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4550
4551 end if;
4552
4553 if (p_timekeeper_data_rec.detail_id_31 is not null and
4554 p_timekeeper_data_rec.detail_id_31 < 0 ) then
4555
4556 l_index:= p_timekeeper_data_rec.detail_id_31 * -1 ;
4557
4558 p_tk_prepop_detail_id_tab(l_index):=p_timekeeper_data_rec.resource_id;
4559
4560 end if;
4561
4562 if g_debug then
4563 hr_utility.trace(' Leaving hxctkutil.populate_prepop_detail_id_info');
4564 end if;
4565
4566
4567 END; -- populate_prepop_detail_id_info
4568
4569
4570
4571 ---------------------------------------------------------------------------------------
4572
4573
4574 FUNCTION get_abs_co_absence_detail_id
4575 (p_absence_duration IN NUMBER DEFAULT NULL,
4576 p_absence_start_time IN DATE DEFAULT NULL,
4577 p_absence_stop_time IN DATE DEFAULT NULL,
4578 p_absence_attendance_id IN NUMBER,
4579 p_transaction_id IN NUMBER,
4580 p_lock_row_id IN ROWID,
4581 p_resource_id IN NUMBER,
4582 p_start_period IN DATE,
4583 p_end_period IN DATE,
4584 p_tc_start IN DATE,
4585 p_tc_end IN DATE,
4586 p_day_value IN NUMBER
4587 )
4588
4589 RETURN NUMBER IS
4590
4591 l_detail_id NUMBER;
4592 l_day_value NUMBER;
4593
4594 BEGIN
4595
4596
4597 l_day_value:= p_day_value -
4598 (trunc(to_number(p_start_period - p_tc_start))
4599 );
4600 if g_debug then
4601 hr_utility.trace('l_day_value = '||l_day_value);
4602 end if;
4603 -- Take care if Empty Paramter handliing routine
4604
4605 if g_debug then
4606 hr_utility.trace (' SVG bfore l_detail_id select query');
4607 end if;
4608
4609 SELECT time_building_block_id
4610 INTO l_detail_id
4611 FROM hxc_abs_co_details
4612 WHERE start_time = p_start_period
4613 AND trunc(stop_time) = trunc(p_end_period)
4614 AND resource_id = p_resource_id
4615 --AND nvl(absence_attendance_id,transaction_id) = p_absence_attendance_id
4616 AND lock_rowid = p_lock_row_id
4617 --AND stage = 'PREP'
4618 AND ((stage='PREP' AND absence_attendance_id = p_absence_attendance_id)
4619 OR
4620 (stage='PREP-SS' AND transaction_id = p_transaction_id))
4621
4622 AND (
4623 ( UOM='D'
4624 and measure = p_absence_duration
4625 and start_date = end_date
4626 and trunc(to_number(start_date - start_time)) = (l_day_value - 1)
4627 )
4628 OR
4629 ( UOM='H'
4630 and start_date = p_absence_start_time
4631 and end_date = p_absence_stop_time
4632 ))
4633 AND sessionid = userenv('SESSIONID')
4634 AND rownum<2;
4635
4636 if g_debug then
4637 hr_utility.trace (' SVG after l_detail_id select query');
4638 hr_utility.trace('SVG l_detail_id ='||l_detail_id);
4639 end if;
4640
4641 return (l_detail_id);
4642
4643
4644
4645
4646 END ; -- get_abs_co_absence_detail_id
4647
4648
4649
4650
4651 ---------------------------------------------------------------------------------------------------
4652
4653
4654 PROCEDURE build_absence_prepop_table
4655 (p_tk_prepop_info IN hxc_timekeeper_utilities.t_tk_prepop_info_type,
4656 p_tk_abs_tab OUT NOCOPY hxc_timekeeper_process.t_tk_abs_tab_type,
4657 p_start_period IN DATE,
4658 p_end_period IN DATE,
4659 p_tc_start IN DATE,
4660 p_tc_end IN DATE,
4661 p_lock_row_id IN ROWID,
4662 p_resource_id IN NUMBER,
4663 p_timekeeper_id IN NUMBER
4664 )
4665
4666 IS
4667
4668 TYPE tmp_sort_rec IS RECORD
4669 (
4670 index_value BINARY_INTEGER,
4671 clash_flag VARCHAR2(1)
4672 );
4673
4674 TYPE tmp_sort_tab_type IS TABLE OF tmp_sort_rec
4675 INDEX BY VARCHAR2(200);
4676
4677 tmp_sort_tab tmp_sort_tab_type;
4678 index_string VARCHAR2(200);
4679 clash_counter NUMBER:=0;
4680 l_clash_flag VARCHAR2(1):='N';
4681
4682 l_prev_attr_id VARCHAR2(150);
4683 l_prev_attr_category VARCHAR2(80);
4684
4685 l_abs_tab_index BINARY_INTEGER:=1;
4686 l_clash_index BINARY_INTEGER:=0;
4687 l_clash_index_tmp BINARY_INTEGER:=0;
4688
4689 attr_assg VARCHAR2(1000);
4690 day_assg1 VARCHAR2(3000);
4691 day_assg2 VARCHAR2(3000);
4692
4693 attr_value VARCHAR2(2);
4694 day_value VARCHAR2(2);
4695
4696 tmp_index VARCHAR2(200);
4697 tmp_bin_index BINARY_INTEGER;
4698 l_detail_id NUMBER;
4699 /*
4700 p_block_array HXC_BLOCK_TABLE_TYPE;
4701 p_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
4702
4703 l_resp_id NUMBER;
4704 l_approval_style_id hxc_time_building_blocks.approval_style_id%TYPE;
4705 l_resp_appl_id NUMBER;
4706
4707 */
4708
4709 BEGIN
4710
4711 /*
4712 p_block_array:= HXC_BLOCK_TABLE_TYPE();
4713
4714 p_attribute_array:= HXC_ATTRIBUTE_TABLE_TYPE();
4715
4716 if g_debug then
4717 hr_utility.trace('Before hxc_preference_evaluation.get_tc_resp');
4718 end if;
4719
4720 hxc_preference_evaluation.get_tc_resp
4721 (p_resource_id,
4722 p_start_period,
4723 p_end_period,
4724 l_resp_id,
4725 l_resp_appl_id);
4726
4727 if g_debug then
4728 hr_utility.trace('After hxc_preference_evaluation.get_tc_resp');
4729 end if;
4730
4731 l_approval_style_id := hxc_preference_evaluation.resource_preferences(
4732 p_resource_id,
4733 'TS_PER_APPROVAL_STYLE',
4734 1,
4735 l_resp_id
4736 );
4737
4738 if g_debug then
4739 hr_utility.trace('l_approval_style_id = '||l_approval_style_id);
4740 end if;
4741
4742 HXC_RETRIEVE_ABSENCES.add_absence_types ( p_person_id => p_resource_id,
4743 p_start_date => p_start_period,
4744 p_end_date => p_end_period,
4745 p_block_array => p_block_array,
4746 p_approval_style_id =>l_approval_style_id,
4747 p_attribute_array => p_attribute_array,
4748 p_lock_rowid => p_lock_row_id,
4749 p_source => 'TK',
4750 p_timekeeper_id => p_timekeeper_id,
4751 p_iteration_count => hxc_timekeeper_process.g_resource_prepop_count
4752 );
4753 */
4754 if g_debug then
4755 hr_utility.trace('Entering build_absence_prepop_table');
4756
4757 hr_utility.trace('BAPT 1');
4758
4759
4760 hr_utility.trace('p_tk_prepop_info.count = '||p_tk_prepop_info.count);
4761
4762
4763
4764 if p_tk_prepop_info.count>0 then
4765
4766 FOR i in p_tk_prepop_info.FIRST .. p_tk_prepop_info.LAST
4767 LOOP
4768
4769 if p_tk_prepop_info.EXISTS(i) then
4770
4771 hr_utility.trace('p_tk_prepop_info(i).ALIAS_VALUE_ID = '||p_tk_prepop_info(i).ALIAS_VALUE_ID);
4772 hr_utility.trace('p_tk_prepop_info(i).ITEM_ATTRIBUTE_CATEGORY = '||p_tk_prepop_info(i).ITEM_ATTRIBUTE_CATEGORY);
4773 hr_utility.trace('p_tk_prepop_info(i).ABSENCE_DATE = '||p_tk_prepop_info(i).ABSENCE_DATE);
4774 hr_utility.trace('p_tk_prepop_info(i).ABSENCE_DURATION = '||p_tk_prepop_info(i).ABSENCE_DURATION);
4775 hr_utility.trace('p_tk_prepop_info(i).ABSENCE_START_TIME = '||p_tk_prepop_info(i).ABSENCE_START_TIME);
4776 hr_utility.trace('p_tk_prepop_info(i).ABSENCE_STOP_TIME = '||p_tk_prepop_info(i).ABSENCE_STOP_TIME);
4777 hr_utility.trace('p_tk_prepop_info(i).ABSENCE_ATTENDANCE_ID = '||p_tk_prepop_info(i).ABSENCE_ATTENDANCE_ID);
4778 hr_utility.trace('p_tk_prepop_info(i).TRANSACTION_ID = '||p_tk_prepop_info(i).TRANSACTION_ID);
4779
4780 end if;
4781
4782 END LOOP;
4783
4784 end if;
4785
4786 end if;
4787
4788
4789
4790
4791 IF p_tk_prepop_info.COUNT > 0 THEN
4792
4793
4794
4795
4796
4797
4798
4799 FOR i in p_tk_prepop_info.FIRST .. p_tk_prepop_info.LAST
4800 LOOP
4801
4802
4803
4804 index_string:= to_char(p_tk_prepop_info(i).ALIAS_VALUE_ID) || '-' ||
4805 to_char(p_tk_prepop_info(i).ITEM_ATTRIBUTE_CATEGORY) || '-' ||
4806 to_char(p_tk_prepop_info(i).ABSENCE_DATE,'YYYYMMDD');
4807
4808 IF tmp_sort_tab.EXISTS(index_string) THEN
4809
4810 clash_counter:= clash_counter + 1;
4811 l_clash_flag:= 'Y';
4812 index_string:= index_string||to_char(clash_counter);
4813
4814 ELSE
4815
4816 l_clash_flag:= 'N';
4817
4818 END IF;
4819
4820
4821
4822 tmp_sort_tab(index_string).index_value:= i;
4823 tmp_sort_tab(index_string).clash_flag:= l_clash_flag;
4824
4825 if g_debug then
4826 hr_utility.trace('index_string = '||index_string);
4827 hr_utility.trace('index_value = '||tmp_sort_tab(index_string).index_value);
4828 hr_utility.trace('clash_flag = '||tmp_sort_tab(index_string).clash_flag);
4829 end if;
4830
4831
4832
4833 END LOOP; -- p_tk_prepop_info plsql table
4834
4835 END IF; -- p_tk_prepop_info.COUNT
4836
4837 if g_debug then
4838 hr_utility.trace('BAPT 2');
4839 end if;
4840
4841 l_prev_attr_id:= p_tk_prepop_info(tmp_sort_tab(tmp_sort_tab.FIRST).index_value).ALIAS_VALUE_ID;
4842 l_prev_attr_category:= p_tk_prepop_info(tmp_sort_tab(tmp_sort_tab.FIRST).index_value).ITEM_ATTRIBUTE_CATEGORY;
4843
4844 if g_debug then
4845 hr_utility.trace('BAPT 3');
4846 end if;
4847
4848 IF tmp_sort_tab.COUNT>0 THEN
4849
4850
4851 tmp_index:= tmp_sort_tab.FIRST;
4852
4853 WHILE tmp_index is not null
4854
4855 LOOP
4856
4857 if tmp_sort_tab.EXISTS(tmp_index) then
4858
4859 if g_debug then
4860 hr_utility.trace('BAPT 3001');
4861 end if;
4862
4863 l_clash_index_tmp:=0;
4864
4865 if g_debug then
4866 hr_utility.trace(' BAPT 301');
4867 end if;
4868
4869 IF to_char(p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID) = l_prev_attr_id AND
4870 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ITEM_ATTRIBUTE_CATEGORY = l_prev_attr_category THEN
4871
4872 if g_debug then
4873 hr_utility.trace(' BAPT 302');
4874 end if;
4875
4876 if tmp_sort_tab(tmp_index).clash_flag='Y' then
4877
4878 l_clash_index:= l_clash_index+1;
4879 l_clash_index_tmp:= l_clash_index;
4880
4881 end if;
4882 if g_debug then
4883 hr_utility.trace(' BAPT 303');
4884 end if;
4885
4886 ELSE
4887 if g_debug then
4888 hr_utility.trace(' BAPT 304');
4889 end if;
4890
4891 l_abs_tab_index:= l_abs_tab_index + l_clash_index + 1;
4892 l_clash_index:=0;
4893
4894 if g_debug then
4895 hr_utility.trace(' BAPT 305');
4896 end if;
4897
4898 END IF;
4899
4900 if g_debug then
4901 hr_utility.trace('BAPT 31');
4902 end if;
4903
4904 attr_value := substr(p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ITEM_ATTRIBUTE_CATEGORY,
4905 16);
4906
4907 if g_debug then
4908 hr_utility.trace('ABSENCE_DATE = '||p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DATE);
4909 end if;
4910
4911 day_value := to_char (
4912 trunc( to_number( p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DATE
4913 -- - p_start_period
4914 - p_tc_start
4915 )
4916 ) +1
4917 );
4918
4919 if g_debug then
4920 hr_utility.trace('BAPT 32');
4921 end if;
4922
4923 tmp_bin_index:=l_abs_tab_index+l_clash_index_tmp;
4924
4925
4926 if g_debug then
4927 hr_utility.trace('SVG inputs to get_abs_co_absence_detail_id');
4928 hr_utility.trace('-------');
4929 hr_utility.trace('p_absence_duration = '||p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION);
4930 hr_utility.trace('p_absence_start_time = '||p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME);
4931 hr_utility.trace('p_absence_stop_time = ' ||p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME);
4932 hr_utility.trace('p_absence_attendance_id = '||p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_ATTENDANCE_ID);
4933 hr_utility.trace('p_transaction_id = '||p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).transaction_id);
4934 hr_utility.trace('p_lock_row_id = '||p_lock_row_id);
4935 hr_utility.trace('p_resource_id = '||p_resource_id);
4936 hr_utility.trace('p_start_period = '||p_start_period);
4937 hr_utility.trace('p_end_period = '||p_end_period);
4938 hr_utility.trace('p_tc_start = '||p_tc_start);
4939 hr_utility.trace('p_tc_end = '||p_tc_end);
4940 hr_utility.trace('p_day_value = '||day_value);
4941 end if;
4942
4943
4944
4945 l_detail_id:= get_abs_co_absence_detail_id
4946 (p_absence_duration => p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION
4947 ,p_absence_start_time => p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME
4948 ,p_absence_stop_time => p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME
4949 ,p_absence_attendance_id => p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_ATTENDANCE_ID
4950 ,p_transaction_id => p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).TRANSACTION_ID
4951 ,p_lock_row_id => p_lock_row_id
4952 ,p_resource_id => p_resource_id
4953 ,p_start_period => p_start_period
4954 ,p_end_period => p_end_period
4955 ,p_tc_start => p_tc_start
4956 ,p_tc_end => p_tc_end
4957 ,p_day_value => to_number(day_value)
4958 );
4959
4960 if g_debug then
4961 hr_utility.trace('attr_value = '||attr_value);
4962 end if;
4963
4964 CASE attr_value
4965
4966 WHEN '1' THEN
4967
4968 p_tk_abs_tab(tmp_bin_index).attr_id_1:=
4969 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
4970
4971 WHEN '2' THEN
4972
4973 p_tk_abs_tab(tmp_bin_index).attr_id_2:=
4974 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
4975
4976 WHEN '3' THEN
4977
4978 p_tk_abs_tab(tmp_bin_index).attr_id_3:=
4979 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
4980
4981 WHEN '4' THEN
4982
4983 p_tk_abs_tab(tmp_bin_index).attr_id_4:=
4984 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
4985
4986 WHEN '5' THEN
4987
4988 p_tk_abs_tab(tmp_bin_index).attr_id_5:=
4989 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
4990
4991 WHEN '6' THEN
4992
4993 p_tk_abs_tab(tmp_bin_index).attr_id_6:=
4994 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
4995
4996 WHEN '7' THEN
4997
4998 p_tk_abs_tab(tmp_bin_index).attr_id_7:=
4999 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5000
5001 WHEN '8' THEN
5002
5003 p_tk_abs_tab(tmp_bin_index).attr_id_8:=
5004 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5005
5006 WHEN '9' THEN
5007
5008 p_tk_abs_tab(tmp_bin_index).attr_id_9:=
5009 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5010
5011 WHEN '10' THEN
5012
5013 p_tk_abs_tab(tmp_bin_index).attr_id_10:=
5014 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5015
5016 WHEN '11' THEN
5017
5018 p_tk_abs_tab(tmp_bin_index).attr_id_11:=
5019 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5020
5021 WHEN '12' THEN
5022
5023 p_tk_abs_tab(tmp_bin_index).attr_id_12:=
5024 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5025
5026 WHEN '13' THEN
5027
5028 p_tk_abs_tab(tmp_bin_index).attr_id_13:=
5029 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5030
5031 WHEN '14' THEN
5032
5033 p_tk_abs_tab(tmp_bin_index).attr_id_14:=
5034 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5035
5036 WHEN '15' THEN
5037
5038 p_tk_abs_tab(tmp_bin_index).attr_id_15:=
5039 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5040
5041 WHEN '16' THEN
5042
5043 p_tk_abs_tab(tmp_bin_index).attr_id_16:=
5044 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5045
5046 WHEN '17' THEN
5047
5048 p_tk_abs_tab(tmp_bin_index).attr_id_17:=
5049 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5050
5051 WHEN '18' THEN
5052
5053 p_tk_abs_tab(tmp_bin_index).attr_id_18:=
5054 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5055
5056
5057 WHEN '19' THEN
5058
5059 p_tk_abs_tab(tmp_bin_index).attr_id_19:=
5060 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5061
5062 WHEN '20' THEN
5063
5064 p_tk_abs_tab(tmp_bin_index).attr_id_20:=
5065 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5066
5067
5068
5069 END CASE;
5070
5071
5072 CASE day_value
5073
5074 WHEN '1' THEN
5075
5076 p_tk_abs_tab(tmp_bin_index).time_in_1:=
5077 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5078
5079 p_tk_abs_tab(tmp_bin_index).time_out_1:=
5080 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5081
5082 if ((p_tk_abs_tab(tmp_bin_index).time_in_1 is null) AND
5083 (p_tk_abs_tab(tmp_bin_index).time_out_1 is null)) then
5084
5085 p_tk_abs_tab(tmp_bin_index).day_1:=
5086 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5087
5088 end if;
5089
5090
5091 p_tk_abs_tab(tmp_bin_index).detail_id_1:= l_detail_id;
5092 p_tk_abs_tab(tmp_bin_index).detail_ovn_1:= 1;
5093
5094
5095
5096
5097 WHEN '2' THEN
5098
5099
5100 p_tk_abs_tab(tmp_bin_index).time_in_2:=
5101 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5102
5103 p_tk_abs_tab(tmp_bin_index).time_out_2:=
5104 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5105
5106 if ((p_tk_abs_tab(tmp_bin_index).time_in_2 is null) AND
5107 (p_tk_abs_tab(tmp_bin_index).time_out_2 is null)) then
5108
5109 p_tk_abs_tab(tmp_bin_index).day_2:=
5110 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5111
5112 end if;
5113
5114 p_tk_abs_tab(tmp_bin_index).detail_id_2:= l_detail_id;
5115 p_tk_abs_tab(tmp_bin_index).detail_ovn_2:= 1;
5116
5117
5118 WHEN '3' THEN
5119
5120
5121 p_tk_abs_tab(tmp_bin_index).time_in_3:=
5122 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5123
5124 p_tk_abs_tab(tmp_bin_index).time_out_3:=
5125 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5126
5127 if ((p_tk_abs_tab(tmp_bin_index).time_in_3 is null) AND
5128 (p_tk_abs_tab(tmp_bin_index).time_out_3 is null)) then
5129
5130 p_tk_abs_tab(tmp_bin_index).day_3:=
5131 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5132
5133 end if;
5134
5135 p_tk_abs_tab(tmp_bin_index).detail_id_3:= l_detail_id;
5136 p_tk_abs_tab(tmp_bin_index).detail_ovn_3:= 1;
5137
5138
5139 WHEN '4' THEN
5140
5141
5142 p_tk_abs_tab(tmp_bin_index).time_in_4:=
5143 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5144
5145 p_tk_abs_tab(tmp_bin_index).time_out_4:=
5146 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5147
5148 if ((p_tk_abs_tab(tmp_bin_index).time_in_4 is null) AND
5149 (p_tk_abs_tab(tmp_bin_index).time_out_4 is null)) then
5150
5151 p_tk_abs_tab(tmp_bin_index).day_4:=
5152 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5153
5154 end if;
5155
5156 p_tk_abs_tab(tmp_bin_index).detail_id_4:= l_detail_id;
5157 p_tk_abs_tab(tmp_bin_index).detail_ovn_4:= 1;
5158
5159
5160 WHEN '5' THEN
5161
5162
5163 p_tk_abs_tab(tmp_bin_index).time_in_5:=
5164 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5165
5166 p_tk_abs_tab(tmp_bin_index).time_out_5:=
5167 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5168
5169 if ((p_tk_abs_tab(tmp_bin_index).time_in_5 is null) AND
5170 (p_tk_abs_tab(tmp_bin_index).time_out_5 is null)) then
5171
5172 p_tk_abs_tab(tmp_bin_index).day_5:=
5173 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5174
5175 end if;
5176
5177 p_tk_abs_tab(tmp_bin_index).detail_id_5:= l_detail_id;
5178 p_tk_abs_tab(tmp_bin_index).detail_ovn_5:= 1;
5179
5180
5181 WHEN '6' THEN
5182
5183
5184 p_tk_abs_tab(tmp_bin_index).time_in_6:=
5185 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5186
5187 p_tk_abs_tab(tmp_bin_index).time_out_6:=
5188 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5189
5190 if ((p_tk_abs_tab(tmp_bin_index).time_in_6 is null) AND
5191 (p_tk_abs_tab(tmp_bin_index).time_out_6 is null)) then
5192
5193 p_tk_abs_tab(tmp_bin_index).day_6:=
5194 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5195
5196 end if;
5197
5198 p_tk_abs_tab(tmp_bin_index).detail_id_6:= l_detail_id;
5199 p_tk_abs_tab(tmp_bin_index).detail_ovn_6:= 1;
5200
5201
5202 WHEN '7' THEN
5203
5204
5205 p_tk_abs_tab(tmp_bin_index).time_in_7:=
5206 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5207
5208 p_tk_abs_tab(tmp_bin_index).time_out_7:=
5209 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5210
5211 if ((p_tk_abs_tab(tmp_bin_index).time_in_7 is null) AND
5212 (p_tk_abs_tab(tmp_bin_index).time_out_7 is null)) then
5213
5214 p_tk_abs_tab(tmp_bin_index).day_7:=
5215 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5216
5217 end if;
5218
5219 p_tk_abs_tab(tmp_bin_index).detail_id_7:= l_detail_id;
5220 p_tk_abs_tab(tmp_bin_index).detail_ovn_7:= 1;
5221
5222
5223 WHEN '8' THEN
5224
5225
5226 p_tk_abs_tab(tmp_bin_index).time_in_8:=
5227 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5228
5229 p_tk_abs_tab(tmp_bin_index).time_out_8:=
5230 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5231
5232 if ((p_tk_abs_tab(tmp_bin_index).time_in_8 is null) AND
5233 (p_tk_abs_tab(tmp_bin_index).time_out_8 is null)) then
5234
5235 p_tk_abs_tab(tmp_bin_index).day_8:=
5236 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5237
5238 end if;
5239
5240 p_tk_abs_tab(tmp_bin_index).detail_id_8:= l_detail_id;
5241 p_tk_abs_tab(tmp_bin_index).detail_ovn_8:= 1;
5242
5243
5244 WHEN '9' THEN
5245
5246
5247 p_tk_abs_tab(tmp_bin_index).time_in_9:=
5248 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5249
5250 p_tk_abs_tab(tmp_bin_index).time_out_9:=
5251 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5252
5253 if ((p_tk_abs_tab(tmp_bin_index).time_in_9 is null) AND
5254 (p_tk_abs_tab(tmp_bin_index).time_out_9 is null)) then
5255
5256 p_tk_abs_tab(tmp_bin_index).day_9:=
5257 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5258
5259 end if;
5260
5261 p_tk_abs_tab(tmp_bin_index).detail_id_9:= l_detail_id;
5262 p_tk_abs_tab(tmp_bin_index).detail_ovn_9:= 1;
5263
5264
5265 WHEN '10' THEN
5266
5267
5268 p_tk_abs_tab(tmp_bin_index).time_in_10:=
5269 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5270
5271 p_tk_abs_tab(tmp_bin_index).time_out_10:=
5272 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5273
5274 if ((p_tk_abs_tab(tmp_bin_index).time_in_10 is null) AND
5275 (p_tk_abs_tab(tmp_bin_index).time_out_10 is null)) then
5276
5277 p_tk_abs_tab(tmp_bin_index).day_10:=
5278 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5279
5280 end if;
5281
5282 p_tk_abs_tab(tmp_bin_index).detail_id_10:= l_detail_id;
5283 p_tk_abs_tab(tmp_bin_index).detail_ovn_10:= 1;
5284
5285
5286 WHEN '11' THEN
5287
5288
5289 p_tk_abs_tab(tmp_bin_index).time_in_11:=
5290 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5291
5292 p_tk_abs_tab(tmp_bin_index).time_out_11:=
5293 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5294
5295 if ((p_tk_abs_tab(tmp_bin_index).time_in_11 is null) AND
5296 (p_tk_abs_tab(tmp_bin_index).time_out_11 is null)) then
5297
5298 p_tk_abs_tab(tmp_bin_index).day_11:=
5299 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5300
5301 end if;
5302
5303 p_tk_abs_tab(tmp_bin_index).detail_id_11:= l_detail_id;
5304 p_tk_abs_tab(tmp_bin_index).detail_ovn_11:= 1;
5305
5306
5307
5308 WHEN '12' THEN
5309
5310
5311 p_tk_abs_tab(tmp_bin_index).time_in_12:=
5312 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5313
5314 p_tk_abs_tab(tmp_bin_index).time_out_12:=
5315 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5316
5317 if ((p_tk_abs_tab(tmp_bin_index).time_in_12 is null) AND
5318 (p_tk_abs_tab(tmp_bin_index).time_out_12 is null)) then
5319
5320 p_tk_abs_tab(tmp_bin_index).day_12:=
5321 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5322
5323 end if;
5324
5325 p_tk_abs_tab(tmp_bin_index).detail_id_12:= l_detail_id;
5326 p_tk_abs_tab(tmp_bin_index).detail_ovn_12:= 1;
5327
5328
5329
5330 WHEN '13' THEN
5331
5332
5333 p_tk_abs_tab(tmp_bin_index).time_in_13:=
5334 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5335
5336 p_tk_abs_tab(tmp_bin_index).time_out_13:=
5337 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5338
5339 if ((p_tk_abs_tab(tmp_bin_index).time_in_13 is null) AND
5340 (p_tk_abs_tab(tmp_bin_index).time_out_13 is null)) then
5341
5342 p_tk_abs_tab(tmp_bin_index).day_13:=
5343 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5344
5345 end if;
5346
5347 p_tk_abs_tab(tmp_bin_index).detail_id_13:= l_detail_id;
5348 p_tk_abs_tab(tmp_bin_index).detail_ovn_13:= 1;
5349
5350
5351 WHEN '14' THEN
5352
5353
5354 p_tk_abs_tab(tmp_bin_index).time_in_14:=
5355 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5356
5357 p_tk_abs_tab(tmp_bin_index).time_out_14:=
5358 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5359
5360 if ((p_tk_abs_tab(tmp_bin_index).time_in_14 is null) AND
5361 (p_tk_abs_tab(tmp_bin_index).time_out_14 is null)) then
5362
5363 p_tk_abs_tab(tmp_bin_index).day_14:=
5364 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5365
5366 end if;
5367
5368 p_tk_abs_tab(tmp_bin_index).detail_id_14:= l_detail_id;
5369 p_tk_abs_tab(tmp_bin_index).detail_ovn_14:= 1;
5370
5371
5372 WHEN '15' THEN
5373
5374
5375 p_tk_abs_tab(tmp_bin_index).time_in_15:=
5376 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5377
5378 p_tk_abs_tab(tmp_bin_index).time_out_15:=
5379 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5380
5381 if ((p_tk_abs_tab(tmp_bin_index).time_in_15 is null) AND
5382 (p_tk_abs_tab(tmp_bin_index).time_out_15 is null)) then
5383
5384 p_tk_abs_tab(tmp_bin_index).day_15:=
5385 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5386
5387 end if;
5388
5389 p_tk_abs_tab(tmp_bin_index).detail_id_15:= l_detail_id;
5390 p_tk_abs_tab(tmp_bin_index).detail_ovn_15:= 1;
5391
5392
5393 WHEN '16' THEN
5394
5395
5396 p_tk_abs_tab(tmp_bin_index).time_in_16:=
5397 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5398
5399 p_tk_abs_tab(tmp_bin_index).time_out_16:=
5400 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5401
5402 if ((p_tk_abs_tab(tmp_bin_index).time_in_16 is null) AND
5403 (p_tk_abs_tab(tmp_bin_index).time_out_16 is null)) then
5404
5405 p_tk_abs_tab(tmp_bin_index).day_16:=
5406 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5407
5408 end if;
5409
5410 p_tk_abs_tab(tmp_bin_index).detail_id_16:= l_detail_id;
5411 p_tk_abs_tab(tmp_bin_index).detail_ovn_16:= 1;
5412
5413
5414 WHEN '17' THEN
5415
5416
5417 p_tk_abs_tab(tmp_bin_index).time_in_17:=
5418 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5419
5420 p_tk_abs_tab(tmp_bin_index).time_out_17:=
5421 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5422
5423 if ((p_tk_abs_tab(tmp_bin_index).time_in_17 is null) AND
5424 (p_tk_abs_tab(tmp_bin_index).time_out_17 is null)) then
5425
5426 p_tk_abs_tab(tmp_bin_index).day_17:=
5427 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5428
5429 end if;
5430
5431 p_tk_abs_tab(tmp_bin_index).detail_id_17:= l_detail_id;
5432 p_tk_abs_tab(tmp_bin_index).detail_ovn_17:= 1;
5433
5434
5435 WHEN '18' THEN
5436
5437
5438 p_tk_abs_tab(tmp_bin_index).time_in_18:=
5439 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5440
5441 p_tk_abs_tab(tmp_bin_index).time_out_18:=
5442 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5443
5444 if ((p_tk_abs_tab(tmp_bin_index).time_in_18 is null) AND
5445 (p_tk_abs_tab(tmp_bin_index).time_out_18 is null)) then
5446
5447 p_tk_abs_tab(tmp_bin_index).day_18:=
5448 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5449
5450 end if;
5451
5452 p_tk_abs_tab(tmp_bin_index).detail_id_18:= l_detail_id;
5453 p_tk_abs_tab(tmp_bin_index).detail_ovn_18:= 1;
5454
5455
5456 WHEN '19' THEN
5457
5458
5459 p_tk_abs_tab(tmp_bin_index).time_in_19:=
5460 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5461
5462 p_tk_abs_tab(tmp_bin_index).time_out_19:=
5463 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5464
5465 if ((p_tk_abs_tab(tmp_bin_index).time_in_19 is null) AND
5466 (p_tk_abs_tab(tmp_bin_index).time_out_19 is null)) then
5467
5468 p_tk_abs_tab(tmp_bin_index).day_19:=
5469 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5470
5471 end if;
5472
5473 p_tk_abs_tab(tmp_bin_index).detail_id_19:= l_detail_id;
5474 p_tk_abs_tab(tmp_bin_index).detail_ovn_19:= 1;
5475
5476
5477 WHEN '20' THEN
5478
5479
5480 p_tk_abs_tab(tmp_bin_index).time_in_20:=
5481 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5482
5483 p_tk_abs_tab(tmp_bin_index).time_out_20:=
5484 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5485
5486 if ((p_tk_abs_tab(tmp_bin_index).time_in_20 is null) AND
5487 (p_tk_abs_tab(tmp_bin_index).time_out_20 is null)) then
5488
5489 p_tk_abs_tab(tmp_bin_index).day_20:=
5490 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5491
5492 end if;
5493
5494 p_tk_abs_tab(tmp_bin_index).detail_id_20:= l_detail_id;
5495 p_tk_abs_tab(tmp_bin_index).detail_ovn_20:= 1;
5496
5497
5498 WHEN '21' THEN
5499
5500
5501 p_tk_abs_tab(tmp_bin_index).time_in_21:=
5502 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5503
5504 p_tk_abs_tab(tmp_bin_index).time_out_21:=
5505 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5506
5507 if ((p_tk_abs_tab(tmp_bin_index).time_in_21 is null) AND
5508 (p_tk_abs_tab(tmp_bin_index).time_out_21 is null)) then
5509
5510 p_tk_abs_tab(tmp_bin_index).day_21:=
5511 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5512
5513 end if;
5514
5515 p_tk_abs_tab(tmp_bin_index).detail_id_21:= l_detail_id;
5516 p_tk_abs_tab(tmp_bin_index).detail_ovn_21:= 1;
5517
5518
5519 WHEN '22' THEN
5520
5521
5522 p_tk_abs_tab(tmp_bin_index).time_in_22:=
5523 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5524
5525 p_tk_abs_tab(tmp_bin_index).time_out_22:=
5526 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5527
5528 if ((p_tk_abs_tab(tmp_bin_index).time_in_22 is null) AND
5529 (p_tk_abs_tab(tmp_bin_index).time_out_22 is null)) then
5530
5531 p_tk_abs_tab(tmp_bin_index).day_22:=
5532 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5533
5534 end if;
5535
5536 p_tk_abs_tab(tmp_bin_index).detail_id_22:= l_detail_id;
5537 p_tk_abs_tab(tmp_bin_index).detail_ovn_22:= 1;
5538
5539
5540 WHEN '23' THEN
5541
5542
5543 p_tk_abs_tab(tmp_bin_index).time_in_23:=
5544 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5545
5546 p_tk_abs_tab(tmp_bin_index).time_out_23:=
5547 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5548
5549 if ((p_tk_abs_tab(tmp_bin_index).time_in_23 is null) AND
5550 (p_tk_abs_tab(tmp_bin_index).time_out_23 is null)) then
5551
5552 p_tk_abs_tab(tmp_bin_index).day_23:=
5553 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5554
5555 end if;
5556
5557 p_tk_abs_tab(tmp_bin_index).detail_id_23:= l_detail_id;
5558 p_tk_abs_tab(tmp_bin_index).detail_ovn_23:= 1;
5559
5560
5561 WHEN '24' THEN
5562
5563
5564 p_tk_abs_tab(tmp_bin_index).time_in_24:=
5565 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5566
5567 p_tk_abs_tab(tmp_bin_index).time_out_24:=
5568 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5569
5570 if ((p_tk_abs_tab(tmp_bin_index).time_in_24 is null) AND
5571 (p_tk_abs_tab(tmp_bin_index).time_out_24 is null)) then
5572
5573 p_tk_abs_tab(tmp_bin_index).day_24:=
5574 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5575
5576 end if;
5577
5578 p_tk_abs_tab(tmp_bin_index).detail_id_24:= l_detail_id;
5579 p_tk_abs_tab(tmp_bin_index).detail_ovn_24:= 1;
5580
5581
5582 WHEN '25' THEN
5583
5584
5585 p_tk_abs_tab(tmp_bin_index).time_in_25:=
5586 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5587
5588 p_tk_abs_tab(tmp_bin_index).time_out_25:=
5589 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5590
5591 if ((p_tk_abs_tab(tmp_bin_index).time_in_25 is null) AND
5592 (p_tk_abs_tab(tmp_bin_index).time_out_25 is null)) then
5593
5594 p_tk_abs_tab(tmp_bin_index).day_25:=
5595 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5596
5597 end if;
5598
5599 p_tk_abs_tab(tmp_bin_index).detail_id_25:= l_detail_id;
5600 p_tk_abs_tab(tmp_bin_index).detail_ovn_25:= 1;
5601
5602
5603 WHEN '26' THEN
5604
5605
5606 p_tk_abs_tab(tmp_bin_index).time_in_26:=
5607 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5608
5609 p_tk_abs_tab(tmp_bin_index).time_out_26:=
5610 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5611
5612 if ((p_tk_abs_tab(tmp_bin_index).time_in_26 is null) AND
5613 (p_tk_abs_tab(tmp_bin_index).time_out_26 is null)) then
5614
5615 p_tk_abs_tab(tmp_bin_index).day_26:=
5616 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5617
5618 end if;
5619
5620 p_tk_abs_tab(tmp_bin_index).detail_id_26:= l_detail_id;
5621 p_tk_abs_tab(tmp_bin_index).detail_ovn_26:= 1;
5622
5623
5624 WHEN '27' THEN
5625
5626 p_tk_abs_tab(tmp_bin_index).time_in_27:=
5627 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5628
5629 p_tk_abs_tab(tmp_bin_index).time_out_27:=
5630 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5631
5632 if ((p_tk_abs_tab(tmp_bin_index).time_in_27 is null) AND
5633 (p_tk_abs_tab(tmp_bin_index).time_out_27 is null)) then
5634
5635 p_tk_abs_tab(tmp_bin_index).day_27:=
5636 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5637
5638 end if;
5639
5640 p_tk_abs_tab(tmp_bin_index).detail_id_27:= l_detail_id;
5641 p_tk_abs_tab(tmp_bin_index).detail_ovn_27:= 1;
5642
5643
5644 WHEN '28' THEN
5645
5646
5647 p_tk_abs_tab(tmp_bin_index).time_in_28:=
5648 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5649
5650 p_tk_abs_tab(tmp_bin_index).time_out_28:=
5651 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5652
5653 if ((p_tk_abs_tab(tmp_bin_index).time_in_28 is null) AND
5654 (p_tk_abs_tab(tmp_bin_index).time_out_28 is null)) then
5655
5656 p_tk_abs_tab(tmp_bin_index).day_28:=
5657 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5658
5659 end if;
5660
5661 p_tk_abs_tab(tmp_bin_index).detail_id_28:= l_detail_id;
5662 p_tk_abs_tab(tmp_bin_index).detail_ovn_28:= 1;
5663
5664
5665 WHEN '29' THEN
5666
5667
5668 p_tk_abs_tab(tmp_bin_index).time_in_29:=
5669 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5670
5671 p_tk_abs_tab(tmp_bin_index).time_out_29:=
5672 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5673
5674 if ((p_tk_abs_tab(tmp_bin_index).time_in_29 is null) AND
5675 (p_tk_abs_tab(tmp_bin_index).time_out_29 is null)) then
5676
5677 p_tk_abs_tab(tmp_bin_index).day_29:=
5678 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5679
5680 end if;
5681
5682 p_tk_abs_tab(tmp_bin_index).detail_id_29:= l_detail_id;
5683 p_tk_abs_tab(tmp_bin_index).detail_ovn_29:= 1;
5684
5685
5686 WHEN '30' THEN
5687
5688
5689 p_tk_abs_tab(tmp_bin_index).time_in_30:=
5690 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5691
5692 p_tk_abs_tab(tmp_bin_index).time_out_30:=
5693 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5694
5695 if ((p_tk_abs_tab(tmp_bin_index).time_in_30 is null) AND
5696 (p_tk_abs_tab(tmp_bin_index).time_out_30 is null)) then
5697
5698 p_tk_abs_tab(tmp_bin_index).day_30:=
5699 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5700
5701 end if;
5702
5703 p_tk_abs_tab(tmp_bin_index).detail_id_30:= l_detail_id;
5704 p_tk_abs_tab(tmp_bin_index).detail_ovn_30:= 1;
5705
5706
5707 WHEN '31' THEN
5708
5709
5710 p_tk_abs_tab(tmp_bin_index).time_in_31:=
5711 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME;
5712
5713 p_tk_abs_tab(tmp_bin_index).time_out_31:=
5714 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME;
5715
5716 if ((p_tk_abs_tab(tmp_bin_index).time_in_31 is null) AND
5717 (p_tk_abs_tab(tmp_bin_index).time_out_31 is null)) then
5718
5719 p_tk_abs_tab(tmp_bin_index).day_31:=
5720 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION;
5721
5722 end if;
5723
5724 p_tk_abs_tab(tmp_bin_index).detail_id_31:= l_detail_id;
5725 p_tk_abs_tab(tmp_bin_index).detail_ovn_31:= 1;
5726
5727
5728
5729 END CASE;
5730
5731
5732
5733 /*
5734 attr_assg:='p_tk_abs_tab(tmp_bin_index).attr_id_' || attr_value || ' := ' ||
5735 'p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID ;' ;
5736
5737
5738 day_assg1:= 'p_tk_abs_tab(tmp_bin_index).day_' || day_value || ' := ' ||
5739 'p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION ; ' ;
5740
5741
5742 day_assg2:= 'p_tk_abs_tab(tmp_bin_index).time_in_' || day_value || ':=' ||
5743 'p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME ; '||
5744 'p_tk_abs_tab(l_abs_tab_index+l_clash_index_tmp).time_out_' || day_value || ':=' ||
5745 'p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME ; ' ;
5746
5747
5748 attr_assg:= 'BEGIN '||attr_assg||' END;';
5749 day_assg1:= 'BEGIN '||day_assg1||' END;' ;
5750 day_assg2:= 'BEGIN '||day_assg2||' END;' ;
5751
5752 hr_utility.trace('BAPT 33');
5753
5754 hr_utility.trace('attr_assg ='||attr_assg);
5755 hr_utility.trace('day_assg1 ='||day_assg1);
5756 hr_utility.trace('day_assg2 ='||day_assg2);
5757
5758 EXECUTE IMMEDIATE attr_assg;
5759
5760 hr_utility.trace('BAPT 34');
5761
5762 IF g_debug THEN
5763
5764
5765 hr_utility.trace('Alias Value = '||
5766 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID);
5767 hr_utility.trace('ABSENCE_DURATION = '||
5768 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_DURATION);
5769 hr_utility.trace('ABSENCE_START_TIME = '||
5770 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME);
5771 hr_utility.trace('ABSENCE_STOP_TIME = '||
5772 p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_STOP_TIME);
5773
5774
5775 END IF;
5776
5777
5778 if p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ABSENCE_START_TIME IS NOT NULL THEN
5779
5780 EXECUTE IMMEDIATE (day_assg2);
5781
5782 else
5783
5784 EXECUTE IMMEDIATE (day_assg1);
5785
5786 end if;
5787 */
5788
5789 l_prev_attr_id:= p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ALIAS_VALUE_ID;
5790 l_prev_attr_category:= p_tk_prepop_info(tmp_sort_tab(tmp_index).index_value).ITEM_ATTRIBUTE_CATEGORY;
5791
5792 end if;
5793
5794 tmp_index:=tmp_sort_tab.NEXT(tmp_index);
5795
5796 END LOOP;
5797
5798 END IF;
5799
5800 if g_debug then
5801 hr_utility.trace('BAPT 4');
5802 end if;
5803
5804 END ;
5805
5806
5807
5808 ---------------------------------------------------------------------------------------------------
5809
5810
5811 PROCEDURE PRE_POPULATE_ABSENCE_DETAILS
5812 (p_timekeeper_id IN NUMBER,
5813 p_start_period IN DATE,
5814 p_end_period IN DATE,
5815 p_tc_start IN DATE,
5816 p_tc_end IN DATE,
5817 p_resource_id IN NUMBER,
5818 p_lock_row_id IN ROWID,
5819 p_tk_abs_tab OUT NOCOPY hxc_timekeeper_process.t_tk_abs_tab_type
5820 )
5821
5822 IS
5823
5824 CURSOR get_alias_info (
5825 p_element_type_id IN NUMBER,
5826 p_alias_definition_id IN NUMBER,
5827 p_start_period IN DATE,
5828 p_end_period IN DATE
5829 ) IS
5830
5831 SELECT hav.alias_value_id
5832
5833 FROM hxc_alias_values hav,
5834 hxc_alias_definitions had,
5835 hxc_alias_types hat
5836
5837 WHERE hav.attribute1=to_char(p_element_type_id) and
5838 hav.enabled_flag='Y' and
5839 hav.attribute_category='PAYROLL_ELEMENTS' and
5840 --nvl(hav.date_from,hr_general.start_of_time) <= p_start_period and
5841 --nvl(hav.date_to,hr_general.end_of_time) >= p_end_period and
5842 nvl(hav.date_from,hr_general.start_of_time) <= p_end_period and
5843 nvl(hav.date_to,hr_general.end_of_time) >= p_start_period and
5844 hav.alias_definition_id=p_alias_definition_id and
5845 hav.alias_definition_id=had.alias_definition_id and
5846 had.alias_type_id=hat.alias_type_id and
5847 hat.alias_type = 'OTL_ALT_DDF' and
5848 hat.reference_object = 'PAYROLL_ELEMENTS'and
5849 rownum < 2;
5850
5851
5852 l_abs_tab HXC_RETRIEVE_ABSENCES.ABS_TAB;
5853 l_alias_def_item HXC_ALIAS_UTILITY.t_alias_def_item;
5854
5855 l_tk_prepop_info_counter binary_integer:=0;
5856 l_tk_prepop_info t_tk_prepop_info_type ;
5857
5858 l_abs_pending_appr_notif EXCEPTION;
5859
5860 p_block_array HXC_BLOCK_TABLE_TYPE;
5861 p_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
5862 p_messages HXC_MESSAGE_TABLE_TYPE;
5863
5864 l_resp_id NUMBER;
5865 l_approval_style_id hxc_time_building_blocks.approval_style_id%TYPE;
5866 l_resp_appl_id NUMBER;
5867
5868 l_index NUMBER;
5869
5870
5871 BEGIN
5872
5873 p_block_array:= HXC_BLOCK_TABLE_TYPE();
5874
5875 p_attribute_array:= HXC_ATTRIBUTE_TABLE_TYPE();
5876
5877 p_messages:= HXC_MESSAGE_TABLE_TYPE();
5878
5879 if g_debug then
5880 hr_utility.trace('Before hxc_preference_evaluation.get_tc_resp');
5881 end if;
5882
5883 hxc_preference_evaluation.get_tc_resp
5884 (p_resource_id,
5885 p_start_period,
5886 p_end_period,
5887 l_resp_id,
5888 l_resp_appl_id);
5889
5890 if g_debug then
5891 hr_utility.trace('After hxc_preference_evaluation.get_tc_resp');
5892 end if;
5893
5894 l_approval_style_id := hxc_preference_evaluation.resource_preferences(
5895 p_resource_id,
5896 'TS_PER_APPROVAL_STYLE',
5897 1,
5898 l_resp_id
5899 );
5900
5901 if g_debug then
5902 hr_utility.trace('l_approval_style_id = '||l_approval_style_id);
5903 end if;
5904
5905
5906 HXC_RETRIEVE_ABSENCES.add_absence_types ( p_person_id => p_resource_id,
5907 p_start_date => p_start_period,
5908 p_end_date => p_end_period,
5909 p_block_array => p_block_array,
5910 p_approval_style_id =>l_approval_style_id,
5911 p_attribute_array => p_attribute_array,
5912 p_lock_rowid => p_lock_row_id,
5913 p_source => 'TK',
5914 p_timekeeper_id => p_timekeeper_id,
5915 p_iteration_count => hxc_timekeeper_process.g_resource_prepop_count
5916 );
5917
5918 if g_debug then
5919 hr_utility.trace('INSIDE pre_populate absences');
5920 hr_utility.trace('PPA 1');
5921 end if;
5922
5923 hxc_retrieve_absences.g_message_string:=null;
5924 g_abs_message_string:=null;
5925 hxc_retrieve_absences.g_messages := HXC_MESSAGE_TABLE_TYPE();
5926 hxc_retrieve_absences.g_messages.DELETE;
5927 g_exception_detected:= 'N';
5928
5929 if g_debug then
5930 hr_utility.trace(' Resource= '||p_resource_id);
5931 hr_utility.trace('After instantiating - hxc_retrieve_absences.g_messages.count = '
5932 ||hxc_retrieve_absences.g_messages.count);
5933 end if;
5934
5935 hxc_retrieve_absences.retrieve_absences(p_person_id => p_resource_id,
5936 p_start_date => p_start_period,
5937 p_end_date => p_end_period,
5938 p_abs_tab => l_abs_tab);
5939 p_messages:= hxc_retrieve_absences.g_messages;
5940 if g_debug then
5941 hr_utility.trace(' Resource= '||p_resource_id);
5942 hr_utility.trace('After populating - hxc_retrieve_absences.g_messages.count = '
5943 || hxc_retrieve_absences.g_messages.count);
5944 hr_utility.trace('After populating -p_messages.count = '
5945 ||p_messages.count);
5946 end if;
5947
5948 if p_messages.count>0 then
5949 l_index:= p_messages.first;
5950 g_abs_message_string:= p_messages(l_index).message_name;
5951 end if;
5952 if g_debug then
5953 hr_utility.trace('PPA 2');
5954 end if;
5955
5956 g_abs_message_string:= hxc_retrieve_absences.g_message_string;
5957
5958 if g_debug then
5959 hr_utility.trace('g_message_string = '||g_abs_message_string);
5960 end if;
5961
5962 if (g_abs_message_string = 'HXC_ABS_PEND_APPR_DELETE' OR
5963 g_abs_message_string = 'HXC_ABS_PEND_APPR_ERROR' ) then
5964
5965 hr_utility.trace('Raising exception for '|| p_resource_id);
5966 raise l_abs_pending_appr_notif;
5967
5968 end if;
5969
5970
5971
5972
5973
5974
5975 IF g_debug then
5976
5977 IF l_abs_tab.COUNT > 0
5978 THEN
5979 FOR i IN l_abs_tab.FIRST..l_abs_tab.LAST
5980 LOOP
5981
5982 hr_utility.trace('SVG entered loop');
5983
5984 hr_utility.trace(l_abs_tab(i).abs_date||
5985 '-'||l_abs_tab(i).element_type_id||
5986 '-'||l_abs_tab(i).abs_type_id||
5987 '-'||l_abs_tab(i).duration||
5988 '-'||l_abs_tab(i).abs_attendance_id||
5989 '-'||l_abs_tab(i).transaction_id);
5990 END LOOP;
5991 END IF;
5992 END IF;
5993
5994
5995
5996 HXC_ALIAS_UTILITY.get_alias_def_item (p_timekeeper_id => p_timekeeper_id,
5997 p_alias_def_item => l_alias_def_item
5998 );
5999
6000 if g_debug then
6001 hr_utility.trace('PPA 3');
6002 end if;
6003
6004 IF g_debug then
6005
6006 IF l_alias_def_item.COUNT > 0
6007 THEN
6008 FOR i IN l_alias_def_item.FIRST..l_alias_def_item.LAST
6009 LOOP
6010 hr_utility.trace('ALIAS_DEFINITION_ID = '|| l_alias_def_item(i).ALIAS_DEFINITION_ID);
6011 hr_utility.trace('ITEM_ATTRIBUTE_CATEGORY = '|| l_alias_def_item(i).ITEM_ATTRIBUTE_CATEGORY);
6012 hr_utility.trace('RESOURCE_ID = '|| l_alias_def_item(i).RESOURCE_ID);
6013 hr_utility.trace('LAYOUT_ID = '|| l_alias_def_item(i).LAYOUT_ID);
6014 hr_utility.trace('ALIAS_LABEL = '|| l_alias_def_item(i).ALIAS_LABEL);
6015 hr_utility.trace('PREF_START_DATE = '|| l_alias_def_item(i).PREF_START_DATE);
6016 hr_utility.trace('PREF_END_DATE = '|| l_alias_def_item(i).PREF_END_DATE);
6017 END LOOP;
6018 END IF;
6019
6020 END IF;
6021
6022
6023
6024 IF l_abs_tab.COUNT > 0 THEN
6025
6026 FOR abs_count in l_abs_tab.FIRST .. l_abs_tab.LAST
6027 LOOP
6028 if g_debug then
6029 hr_utility.trace('entering l_abs_tab loop');
6030 hr_utility.trace('l_abs_tab(i).element_type_id='||l_abs_tab(abs_count).element_type_id);
6031 end if;
6032
6033 IF l_alias_def_item.COUNT > 0 THEN
6034
6035 FOR alias_def_count in l_alias_def_item.FIRST .. l_alias_def_item.LAST
6036 LOOP
6037 if g_debug then
6038 hr_utility.trace('l_abs_tab(abs_count).element_type_id = '|| l_abs_tab(abs_count).element_type_id);
6039 hr_utility.trace('l_alias_def_item(alias_def_count).ALIAS_DEFINITION_ID = '|| l_alias_def_item(alias_def_count).ALIAS_DEFINITION_ID);
6040 hr_utility.trace('p_start_period = '|| p_start_period);
6041 hr_utility.trace('p_end_period = '|| p_end_period);
6042 end if;
6043
6044 FOR alias_info in get_alias_info(
6045 l_abs_tab(abs_count).element_type_id,
6046 l_alias_def_item(alias_def_count).ALIAS_DEFINITION_ID,
6047 p_start_period,
6048 p_end_period
6049 )
6050 LOOP
6051
6052 l_tk_prepop_info_counter:= l_tk_prepop_info_counter + 1;
6053
6054 if g_debug then
6055 hr_utility.trace('PPA entered the loop');
6056 end if;
6057
6058 l_tk_prepop_info(l_tk_prepop_info_counter).ALIAS_VALUE_ID:=
6059 alias_info.alias_value_id;
6060
6061 l_tk_prepop_info(l_tk_prepop_info_counter).ITEM_ATTRIBUTE_CATEGORY:=
6062 l_alias_def_item(alias_def_count).ITEM_ATTRIBUTE_CATEGORY;
6063
6064 l_tk_prepop_info(l_tk_prepop_info_counter).ABSENCE_DATE:=
6065 l_abs_tab(abs_count).abs_date;
6066
6067 l_tk_prepop_info(l_tk_prepop_info_counter).ABSENCE_DURATION:=
6068 l_abs_tab(abs_count).duration;
6069
6070 l_tk_prepop_info(l_tk_prepop_info_counter).ABSENCE_START_TIME:=
6071 l_abs_tab(abs_count).abs_start;
6072
6073 l_tk_prepop_info(l_tk_prepop_info_counter).ABSENCE_STOP_TIME:=
6074 l_abs_tab(abs_count).abs_end;
6075
6076 l_tk_prepop_info(l_tk_prepop_info_counter).ABSENCE_ATTENDANCE_ID:=
6077 l_abs_tab(abs_count).abs_attendance_id;
6078
6079 l_tk_prepop_info(l_tk_prepop_info_counter).transaction_id:=
6080 l_abs_tab(abs_count).transaction_id;
6081
6082 END LOOP;
6083
6084 END LOOP;
6085
6086
6087 END IF;
6088
6089 END LOOP;
6090
6091
6092 END IF;
6093
6094 if g_debug then
6095 hr_utility.trace('PPA 4');
6096 end if;
6097
6098 IF l_tk_prepop_info.COUNT > 0 THEN
6099
6100 build_absence_prepop_table(p_tk_prepop_info => l_tk_prepop_info,
6101 p_tk_abs_tab => p_tk_abs_tab,
6102 p_start_period => p_start_period,
6103 p_end_period => p_end_period,
6104 p_tc_start => p_tc_start,
6105 p_tc_end => p_tc_end,
6106 p_lock_row_id => p_lock_row_id,
6107 p_resource_id => p_resource_id ,
6108 p_timekeeper_id => p_timekeeper_id
6109 );
6110
6111 END IF;
6112
6113 EXCEPTION
6114 WHEN l_abs_pending_appr_notif then
6115
6116 g_exception_detected:= 'Y';
6117
6118 END; -- PRE_POPULATE_ABSENCE_DETAILS
6119
6120
6121
6122
6123 ------------------------------------------------------------------------------------------------
6124
6125
6126 END hxc_timekeeper_utilities;