[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMEKEEPER_UTILITIES
Source
1 PACKAGE BODY hxc_timekeeper_utilities AS
2 /* $Header: hxctkutil.pkb 120.15.12010000.4 2008/11/13 16:59:07 asrajago 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 --it is an update and we don't have to change detae from or date to
96 IF p_date_from IS NULL AND p_date_to IS NULL THEN
97 l_date_from := p_timecard (l_block_index).date_from;
98 l_date_to := p_timecard (l_block_index).date_to;
99 END IF;
100 -- check the changed flag
101 IF l_changed <> 'Y' THEN
102
103 IF ((nvl(p_timecard (l_block_index).measure,-999) <> nvl(p_measure,-999))
104 OR (nvl(fnd_date.date_to_canonical
105 (to_date(p_timecard (l_block_index).start_time,'YYYY/MM/DD HH24:MI:SS')),-999)
106 <> nvl(fnd_date.date_to_canonical(
107 to_date(p_start_period,'YYYY/MM/DD HH24:MI:SS')),-999))
108 OR (nvl(fnd_date.date_to_canonical (
109 to_date(p_timecard (l_block_index).stop_time,'YYYY/MM/DD HH24:MI:SS')),-999)
110 <> nvl(fnd_date.date_to_canonical(to_date(p_end_period,'YYYY/MM/DD HH24:MI:SS')),-999))
111 OR (nvl(p_timecard (l_block_index).comment_text,-999)
112 <> nvl(p_comment_text,-999))) THEN
113 l_changed := 'Y';
114 END IF;
115 END IF;
116
117 ELSE
118 p_timecard.EXTEND;
119 l_block_index := p_timecard.LAST;
120 END IF;
121
122
123
124 p_timecard (l_block_index) := hxc_block_type (
125 p_timecard_id,
126 l_type,
127 p_measure,
128 'HOURS',
129 fnd_date.date_to_canonical (p_start_period),
130 fnd_date.date_to_canonical (p_end_period),
131 p_parent_id,
132 'N',
133 p_scope,
134 nvl(p_ovn,1),
135 l_approval_status,
136 p_resource_id,
137 'PERSON',
138 p_approval_style_id,
139 l_date_from,
140 l_date_to,
141 p_comment_text,
142 p_parent_ovn,
143 l_new,
144 l_changed,
145 'N',
146 l_APPLICATION_SET_ID,
147 NULL
148 );
149 p_timecard_index_info (p_timecard_id).time_block_row_index := l_block_index;
150 END add_block;
151
152 -------------------------------------------------------------------------------
153 -- this procedure add a attribute in the attribute_table
154 -------------------------------------------------------------------------------
155 PROCEDURE add_attribute (
156 p_attribute IN OUT NOCOPY hxc_attribute_table_type,
157 p_attribute_id IN NUMBER,
158 p_tbb_id IN NUMBER,
159 p_tbb_ovn IN NUMBER,
160 p_blk_type IN VARCHAR2,
161 p_blk_id IN NUMBER,
162 p_att_category IN VARCHAR2,
163 p_att_1 IN VARCHAR2,
164 p_att_2 IN VARCHAR2,
165 p_att_3 IN VARCHAR2,
166 p_att_4 IN VARCHAR2,
167 p_att_5 IN VARCHAR2 DEFAULT NULL,
168 p_att_6 IN VARCHAR2 DEFAULT NULL,
169 p_att_7 IN VARCHAR2 DEFAULT NULL,
170 p_att_8 IN VARCHAR2 DEFAULT NULL,
171 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
172 ) IS
173 l_new VARCHAR2 (1);
174 l_attribute_index NUMBER
175 /*ADVICE(195): NUMBER has no precision [315] */
176 := 0;
177 BEGIN
178 IF p_attribute_index_info.EXISTS (p_attribute_id) THEN
179 l_attribute_index := p_attribute_index_info (p_attribute_id).attribute_block_row_index;
180 ELSE
181 p_attribute.EXTEND;
182 l_attribute_index := p_attribute.LAST;
183 END IF;
184
185 IF p_attribute_id > 0 THEN
186 l_new := 'N';
187 ELSE
188 l_new := 'Y';
189 END IF;
190
191 p_attribute (l_attribute_index) := hxc_attribute_type (
192 p_attribute_id,
193 p_tbb_id,
194 p_att_category,
195 p_att_1,
196 p_att_2,
197 p_att_3,
198 p_att_4,
199 p_att_5,
200 p_att_6,
201 p_att_7,
202 p_att_8,
203 NULL,
204 NULL,
205 NULL,
206 NULL,
207 NULL,
208 NULL,
209 NULL,
210 NULL,
211 NULL,
212 NULL,
213 NULL,
214 NULL,
215 NULL,
216 NULL,
217 NULL,
218 NULL,
219 NULL,
220 NULL,
221 NULL,
222 NULL,
223 NULL,
224 NULL,
225 p_blk_id,
226 NULL,
227 l_new,
228 'Y',
229 p_blk_type,
230 'N',
231 p_tbb_ovn
232 );
233 p_attribute_index_info (p_attribute_id).attribute_block_row_index := l_attribute_index;
234 END add_attribute;
235
236
237 -------------------------------------------------------------------------------
238 -- this procedure create the attribute structure for an timecard_id
239 -------------------------------------------------------------------------------
240 PROCEDURE create_attribute_structure (
241 p_timecard_id IN NUMBER,
242 p_timecard_ovn IN NUMBER,
243 p_resource_id IN NUMBER,
244 p_start_period
245 /*ADVICE(265): Unreferenced parameter [552] */
246 IN DATE,
247 p_end_period
248 /*ADVICE(268): Unreferenced parameter [552] */
249 IN DATE,
250 p_attributes OUT NOCOPY hxc_attribute_table_type,
251 p_add_hours_type_id
252 /*ADVICE(272): Unreferenced parameter [552] */
253 IN NUMBER,
254 p_attribute_index_info OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
255 ) IS
256 CURSOR c_detail_attribute (
257 timecard_id IN NUMBER,
258 timecard_ovn IN NUMBER,
259 l_resource_id IN NUMBER
260 ) IS
261 SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
262 a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
263 a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
264 a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
265 a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
266 a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
267 a.bld_blk_info_type_id, a.object_version_number
268 FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
269 WHERE a.time_attribute_id = au.time_attribute_id
270 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
271 AND (au.time_building_block_id, au.time_building_block_ovn) IN
272 (SELECT detail.time_building_block_id, detail.object_version_number
273 FROM hxc_time_building_blocks detail, hxc_time_building_blocks DAY
274 WHERE DAY.time_building_block_id = detail.parent_building_block_id
275 AND DAY.object_version_number = detail.parent_building_block_ovn
276 AND DAY.SCOPE = 'DAY'
277 AND detail.resource_id = l_resource_id
278 AND detail.SCOPE = 'DETAIL'
279 AND DAY.date_to = hr_general.end_of_time
280 AND detail.date_to = hr_general.end_of_time
281 AND DAY.parent_building_block_id = timecard_id
282 AND DAY.parent_building_block_ovn = timecard_ovn
283 AND DAY.resource_id = l_resource_id)
284 UNION
285 SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
286 a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
287 a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
288 a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
289 a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
290 a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
291 a.bld_blk_info_type_id, a.object_version_number
292 FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
293 WHERE a.time_attribute_id = au.time_attribute_id
294 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
295 AND (au.time_building_block_id, au.time_building_block_ovn) IN
296 (SELECT DAY.time_building_block_id, DAY.object_version_number
297 FROM hxc_time_building_blocks DAY
298 WHERE DAY.date_to = hr_general.end_of_time
299 AND DAY.SCOPE = 'DAY'
300 AND DAY.parent_building_block_id = timecard_id
301 AND DAY.parent_building_block_ovn = timecard_ovn
302 AND DAY.resource_id = l_resource_id)
303 UNION
304 SELECT a.time_attribute_id, au.time_building_block_id, bbit.bld_blk_info_type, a.attribute_category,
305 a.attribute1, a.attribute2, a.attribute3, a.attribute4, a.attribute5, a.attribute6,
306 a.attribute7, a.attribute8, a.attribute9, a.attribute10, a.attribute11, a.attribute12,
307 a.attribute13, a.attribute14, a.attribute15, a.attribute16, a.attribute17, a.attribute18,
308 a.attribute19, a.attribute20, a.attribute21, a.attribute22, a.attribute23, a.attribute24,
309 a.attribute25, a.attribute26, a.attribute27, a.attribute28, a.attribute29, a.attribute30,
310 a.bld_blk_info_type_id, a.object_version_number
311 FROM hxc_bld_blk_info_types bbit, hxc_time_attribute_usages au, hxc_time_attributes a
312 WHERE a.time_attribute_id = au.time_attribute_id
313 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
314 AND (au.time_building_block_id, au.time_building_block_ovn) IN
315 (SELECT time_building_block_id, object_version_number
316 FROM hxc_time_building_blocks htbb
317 WHERE htbb.date_to = hr_general.end_of_time
318 AND htbb.SCOPE = 'TIMECARD'
319 AND htbb.time_building_block_id = timecard_id
320 AND htbb.object_version_number = timecard_ovn
321 AND htbb.resource_id = l_resource_id)
322 ORDER BY time_building_block_id;
323
324 l_attribute_index NUMBER
325 /*ADVICE(345): NUMBER has no precision [315] */
326 := 0;
327 l_attribute8 varchar2(150);
328 l_attribute3 varchar2(150);
329 l_attribute4 varchar2(150);
330 BEGIN
331 p_attributes := hxc_attribute_table_type ();
332
333 FOR detail_attribute_info IN c_detail_attribute (p_timecard_id, p_timecard_ovn, p_resource_id) LOOP
334 -- index the attribute table with the attribute_id
335 p_attributes.EXTEND;
336 l_attribute_index := p_attributes.LAST;
337 /* start of changes made by senthil for 4295540 */
338 if hxc_timekeeper_process.g_submit and detail_attribute_info.attribute_category = 'REASON' then
339 l_attribute8:='N';
340 else
341 l_attribute8:=detail_attribute_info.attribute8;
342 end if;
343 /* end of changes made by senthil */
344
345 --Condition Added By Mithun for Persistent resp Enhancement
346 --condition was added so that Whenever TK makes modification to a timecard
347 --The resp_id and user_id stored in attribute3 and attribute4 gets refreshed.
348 if hxc_timekeeper_process.g_submit and detail_attribute_info.attribute_category = 'SECURITY' then
349 l_attribute3:=FND_GLOBAL.USER_ID;
350 l_attribute4:=FND_GLOBAL.RESP_ID;
351 else
352 l_attribute3:=detail_attribute_info.attribute3;
353 l_attribute4:=detail_attribute_info.attribute4;
354 end if;
355 --End of Condition Added By Mithun
356
357 p_attributes (l_attribute_index) := hxc_attribute_type (
358 detail_attribute_info.time_attribute_id,
359 detail_attribute_info.time_building_block_id,
360 detail_attribute_info.attribute_category,
361 detail_attribute_info.attribute1,
362 detail_attribute_info.attribute2,
363 l_attribute3,
364 l_attribute4,
365 detail_attribute_info.attribute5,
366 detail_attribute_info.attribute6,
367 detail_attribute_info.attribute7,
368 l_attribute8,
369 detail_attribute_info.attribute9,
370 detail_attribute_info.attribute10,
371 detail_attribute_info.attribute11,
372 detail_attribute_info.attribute12,
373 detail_attribute_info.attribute13,
374 detail_attribute_info.attribute14,
375 detail_attribute_info.attribute15,
376 detail_attribute_info.attribute16,
377 detail_attribute_info.attribute17,
378 detail_attribute_info.attribute18,
379 detail_attribute_info.attribute19,
380 detail_attribute_info.attribute20,
381 detail_attribute_info.attribute21,
382 detail_attribute_info.attribute22,
383 detail_attribute_info.attribute23,
384 detail_attribute_info.attribute24,
385 detail_attribute_info.attribute25,
386 detail_attribute_info.attribute26,
387 detail_attribute_info.attribute27,
388 detail_attribute_info.attribute28,
389 detail_attribute_info.attribute29,
390 detail_attribute_info.attribute30,
391 detail_attribute_info.bld_blk_info_type_id,
392 detail_attribute_info.object_version_number,
393 'N',
394 'N',
395 detail_attribute_info.bld_blk_info_type,
396 'N',
397 NULL
398 );
399 p_attribute_index_info (detail_attribute_info.time_attribute_id).attribute_block_row_index :=
400 l_attribute_index;
401 END LOOP;
402 END create_attribute_structure;
403
404
405 ----------------------------------------------------------------------------
406 -- This Function is used to get which attribute is used to calculate the
407 -- Attribute category of the details associated with
408 ----------------------------------------------------------------------------
409 FUNCTION get_tk_dff_attrname (
410 p_tkid
411 /*ADVICE(408): Unreferenced parameter [552] */
412 IN NUMBER,
413 p_insert_detail IN hxc_timekeeper_process.t_time_info,
414 p_base_dff IN VARCHAR2,
415 p_att_tab IN hxc_alias_utility.t_alias_att_info
416 )
417 RETURN VARCHAR2 IS
418 att_dep_item NUMBER
419 /*ADVICE(416): NUMBER has no precision [315] */
420 ;
421 new_att_catg VARCHAR2 (2000);
422 /*ADVICE(419): VARCHAR2 declaration with length greater than 500 characters [307] */
423
424 l_reference_field fnd_descriptive_flexs.default_context_field_name%TYPE;
425
426 CURSOR c_reference_field IS
427 SELECT d.default_context_field_name
428 FROM fnd_descriptive_flexs d, fnd_application a, fnd_product_installations z
429 WHERE d.application_id = a.application_id
430 AND z.application_id = a.application_id
431 AND a.application_short_name = 'PA'
432 AND z.status = 'I'
433 AND d.descriptive_flexfield_name = 'PA_EXPENDITURE_ITEMS_DESC_FLEX';
434
435 BEGIN
436 g_debug :=hr_utility.debug_enabled;
437 --get the number say 3 FROM ATTRIBUTE3 using substr function
438 att_dep_item := TO_NUMBER (SUBSTR (p_base_dff, 10));
439 if g_debug then
440 hr_utility.trace('att_dep_item is '||att_dep_item);
441 hr_utility.trace('in detail is '||p_insert_detail.attr_id_3);
442 end if;
443 ---depending upon the number select id value from the timecard block
444 -- for 3 it will be attr_id_3
445 IF att_dep_item = 1 THEN
446 new_att_catg := p_insert_detail.attr_id_1;
447 ELSIF att_dep_item = 2 THEN
448 new_att_catg := p_insert_detail.attr_id_2;
449 ELSIF att_dep_item = 3 THEN
450 new_att_catg := p_insert_detail.attr_id_3;
451 ELSIF att_dep_item = 4 THEN
452 new_att_catg := p_insert_detail.attr_id_4;
453 ELSIF att_dep_item = 5 THEN
454 new_att_catg := p_insert_detail.attr_id_5;
455 ELSIF att_dep_item = 6 THEN
456 new_att_catg := p_insert_detail.attr_id_6;
457 ELSIF att_dep_item = 7 THEN
458 new_att_catg := p_insert_detail.attr_id_7;
459 ELSIF att_dep_item = 8 THEN
460 new_att_catg := p_insert_detail.attr_id_8;
461 ELSIF att_dep_item = 9 THEN
462 new_att_catg := p_insert_detail.attr_id_9;
463 ELSIF att_dep_item = 10 THEN
464 new_att_catg := p_insert_detail.attr_id_10;
465 ELSIF att_dep_item = 11 THEN
466 new_att_catg := p_insert_detail.attr_id_11;
467 ELSIF att_dep_item = 11 THEN
468 new_att_catg := p_insert_detail.attr_id_11;
469 ELSIF att_dep_item = 12 THEN
470 new_att_catg := p_insert_detail.attr_id_12;
471 ELSIF att_dep_item = 13 THEN
472 new_att_catg := p_insert_detail.attr_id_13;
473 ELSIF att_dep_item = 14 THEN
474 new_att_catg := p_insert_detail.attr_id_14;
475 ELSIF att_dep_item = 15 THEN
476 new_att_catg := p_insert_detail.attr_id_15;
477 ELSIF att_dep_item = 16 THEN
478 new_att_catg := p_insert_detail.attr_id_16;
479 ELSIF att_dep_item = 17 THEN
480 new_att_catg := p_insert_detail.attr_id_17;
481 ELSIF att_dep_item = 18 THEN
482 new_att_catg := p_insert_detail.attr_id_18;
483 ELSIF att_dep_item = 19 THEN
484 new_att_catg := p_insert_detail.attr_id_19;
485 ELSIF att_dep_item = 20 THEN
486 new_att_catg := p_insert_detail.attr_id_20;
487 END IF;
488
489 if g_debug then
490 hr_utility.trace('new cat is '||new_att_catg);
491 end if;
492 IF new_att_catg IS NOT NULL THEN
493 l_reference_field := NULL;
494 if g_debug then
495 hr_utility.trace('new cat is '||new_att_catg);
496 end if;
497 OPEN c_reference_field;
498 FETCH c_reference_field INTO l_reference_field;
499 CLOSE c_reference_field;
500
501 IF l_reference_field = 'SYSTEM_LINKAGE_FUNCTION' THEN
502 IF p_att_tab (att_dep_item).alias_type LIKE 'VALUE%' THEN
503 SELECT DECODE (
504 SUBSTR (new_att_catg, INSTR (new_att_catg, 'ALIAS_SEPARATOR') + 15),
505 'OT', 'PAEXPITDFF - OT',
506 'PAEXPITDFF - ST'
507 )
508 INTO new_att_catg
509 FROM DUAL;
510 ELSE
511 SELECT DECODE (
512 hxc_alias_utility.get_sfl_from_alias_value (new_att_catg),
513 'OT', 'PAEXPITDFF - OT',
514 'PAEXPITDFF - ST'
515 )
516 INTO new_att_catg
517 FROM DUAL;
518 END IF;
519 ELSIF l_reference_field = 'EXPENDITURE_TYPE' THEN
520 IF p_att_tab (att_dep_item).alias_type LIKE 'VALUE%' THEN
521 SELECT hxc_deposit_wrapper_utilities.get_dupdff_code (
522 'PAEXPITDFF - '|| SUBSTR (new_att_catg, 1, INSTR (new_att_catg, 'ALIAS_SEPARATOR') - 1)
523 )
524 INTO new_att_catg
525 FROM DUAL;
526 ELSE
527 -- new_att_catg:=get_exp_type_from_alias( new_att_catg);
528 new_att_catg :=
529 hxc_deposit_wrapper_utilities.get_dupdff_code (
530 'PAEXPITDFF - '|| get_exp_type_from_alias (new_att_catg)
531 );
532 END IF;
533 -- :DETAIL_BLK.C_ATTRIBUTE_CATEGORY:=HXC_DEPOSIT_WRAPPER_UTILITIES.GET_DUPDFF_CODE(:DETAIL_BLK.C_ATTRIBUTE_CATEGORY) ;--3791698
534 ELSIF l_reference_field = ''
535 /*ADVICE(525): In Oracle 8, VARCHAR2 variables of zero length assigned to CHAR variables will blank-pad
536 these rather than making them NULL [111] */
537 OR l_reference_field IS NULL THEN
538 IF (check_global_context ('PAEXPITDFF')) THEN
539 new_att_catg := 'PAEXPITDFF - GLOBAL';
540 END IF;
541 END IF;
542 END IF;
543
544 /*if new_att_catg is not null then
545 --now look for the type of that attribute if it is like '%VALUE%' then
546 --it will be either PAEXPITDFF - OT OR PAEXPITDFF - ST
547 if p_att_tab(att_dep_item).alias_type like 'VALUE%' then
548 SELECT decode(SUBSTR(new_att_catg,INSTR(new_att_catg,'ALIAS_SEPARATOR')+15),'OT','PAEXPITDFF - OT','PAEXPITDFF - ST')
549 INTO new_att_catg
550 FROM DUAL;
551 else
552 select decode(hxc_alias_utility.get_sfl_from_alias_value(new_att_catg),'OT','PAEXPITDFF - OT','PAEXPITDFF - ST')
553 INTO new_att_catg
554 FROM DUAL;
555 end if;
556 end if;*/
557 if g_debug then
558 hr_utility.trace('return new cat is '||new_att_catg);
559 end if;
560 RETURN (new_att_catg);
561 END;
562
563
564 -------------------------------------------------------------------------------
565 -------------------------------------------------------------------------------
566 PROCEDURE add_dff_attribute (
567 p_attribute IN OUT NOCOPY hxc_attribute_table_type,
568 p_attribute_id IN NUMBER,
569 p_tbb_id IN NUMBER,
570 p_tbb_ovn
571 /*ADVICE(559): Unreferenced parameter [552] */
572 IN NUMBER,
573 p_blk_type IN VARCHAR2,
574 p_blk_id IN NUMBER,
575 p_att_category IN VARCHAR2,
576 p_att_1 IN VARCHAR2,
577 p_att_2 IN VARCHAR2,
578 p_att_3 IN VARCHAR2,
579 p_att_4 IN VARCHAR2,
580 p_att_5 IN VARCHAR2,
581 p_att_6 IN VARCHAR2,
582 p_att_7 IN VARCHAR2,
583 p_att_8 IN VARCHAR2,
584 p_att_9 IN VARCHAR2,
585 p_att_10 IN VARCHAR2,
586 p_att_11 IN VARCHAR2,
587 p_att_12 IN VARCHAR2,
588 p_att_13 IN VARCHAR2,
589 p_att_14 IN VARCHAR2,
590 p_att_15 IN VARCHAR2,
591 p_att_16 IN VARCHAR2,
592 p_att_17 IN VARCHAR2,
593 p_att_18 IN VARCHAR2,
594 p_att_19 IN VARCHAR2,
595 p_att_20 IN VARCHAR2,
596 p_att_21 IN VARCHAR2,
597 p_att_22 IN VARCHAR2,
598 p_att_23 IN VARCHAR2,
599 p_att_24 IN VARCHAR2,
600 p_att_25 IN VARCHAR2,
601 p_att_26 IN VARCHAR2,
602 p_att_27 IN VARCHAR2,
603 p_att_28 IN VARCHAR2,
604 p_att_29 IN VARCHAR2,
605 p_att_30 IN VARCHAR2,
606 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
607 ) IS
608 l_new VARCHAR2 (1);
609 l_attribute_index NUMBER
610 /*ADVICE(598): NUMBER has no precision [315] */
611 := 0;
612 BEGIN
613
614 --p_attribute :=HXC_ATTRIBUTE_TABLE_TYPE();
615 IF p_attribute_index_info.EXISTS (p_attribute_id) THEN
616 l_attribute_index := p_attribute_index_info (p_attribute_id).attribute_block_row_index;
617 ELSE
618 p_attribute.EXTEND;
619 l_attribute_index := p_attribute.LAST;
620 END IF;
621
622 IF p_attribute_id > 0 THEN
623 l_new := 'N';
624 ELSE
625 l_new := 'Y';
626 END IF;
627
628 p_attribute (l_attribute_index) := hxc_attribute_type (
629 p_attribute_id,
630 p_tbb_id,
631 p_att_category,
632 p_att_1,
633 p_att_2,
634 p_att_3,
635 p_att_4,
636 p_att_5,
637 p_att_6,
638 p_att_7,
639 p_att_8,
640 p_att_9,
641 p_att_10,
642 p_att_11,
643 p_att_12,
644 p_att_13,
645 p_att_14,
646 p_att_15,
647 p_att_16,
648 p_att_17,
649 p_att_18,
650 p_att_19,
651 p_att_20,
652 p_att_21,
653 p_att_22,
654 p_att_23,
655 p_att_24,
656 p_att_25,
657 p_att_26,
658 p_att_27,
659 p_att_28,
660 p_att_29,
661 p_att_30,
662 p_blk_id,
663 NULL,
664 l_new,
665 'Y',
666 p_blk_type,
667 'Y',
668 NULL
669 );
670 p_attribute_index_info (p_attribute_id).attribute_block_row_index := l_attribute_index;
671 END add_dff_attribute;
672
673
674 -------------------------------------------------------------------------------
675 --used to order the timecard create ...but not used in the timekeeper process
676 -------------------------------------------------------------------------------
677 PROCEDURE order_building_blocks (
678 p_timecard IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
679 p_ord_timecard IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info
680 ) IS
681 l_timecard_start NUMBER
682 /*ADVICE(670): NUMBER has no precision [315] */
683 := 1;
684 l_block_count NUMBER
685 /*ADVICE(673): NUMBER has no precision [315] */
686 := 0;
687 n
688 /*ADVICE(676): Unreferenced variable [553] */
689 NUMBER
690 /*ADVICE(678): NUMBER has no precision [315] */
691 := 0;
692 BEGIN
693 IF (p_ord_timecard.COUNT > 0) THEN
694 p_ord_timecard.DELETE;
695 END IF;
696
697 l_block_count := p_timecard.FIRST;
698
699 LOOP
700 EXIT WHEN NOT p_timecard.EXISTS (l_block_count);
701 p_ord_timecard (l_timecard_start) := p_timecard (l_block_count);
702 l_timecard_start := l_timecard_start + 1;
703 l_block_count := p_timecard.NEXT (l_block_count);
704 END LOOP;
705 END order_building_blocks;
706
707
708 -------------------------------------------------------------------------------
709 -- This procedure dump the timkeeper date table information
710 -------------------------------------------------------------------------------
711 PROCEDURE dump_timkeeper_data (
712 p_timekeeper_data IN hxc_timekeeper_process.t_timekeeper_table
713 ) IS
714 l_index NUMBER
715 /*ADVICE(703): NUMBER has no precision [315] */
716 ;
717 BEGIN
718 l_index := p_timekeeper_data.FIRST;
719
720 LOOP
721 EXIT WHEN (NOT p_timekeeper_data.EXISTS (l_index));
722 l_index := p_timekeeper_data.NEXT (l_index);
723 END LOOP;
724 END;
725
726
727 -------------------------------------------------------------------------------
728 -- This procedure dump the buffer table information
729 -------------------------------------------------------------------------------
730 PROCEDURE dump_buffer_table (
731 p_buffer_table hxc_timekeeper_process.t_buffer_table
732 /*ADVICE(720): Mode of parameter is not specified with IN parameter [521] */
733
734 ) IS
735 l_index NUMBER
736 /*ADVICE(724): NUMBER has no precision [315] */
737 ;
738 BEGIN
739 l_index := p_buffer_table.FIRST;
740
741 LOOP
742 EXIT WHEN (NOT p_buffer_table.EXISTS (l_index));
743 l_index := p_buffer_table.NEXT (l_index);
744 END LOOP;
745 END dump_buffer_table;
746
747
748 -------------------------------------------------------------------------------
749 -- this procedure dump the resource tc table information
750 -------------------------------------------------------------------------------
751 PROCEDURE dump_resource_tc_table (
752 l_resource_tc_table hxc_timekeeper_process.t_resource_tc_table
753 /*ADVICE(741): Mode of parameter is not specified with IN parameter [521] */
754
755 ) IS
756 l_index NUMBER
757 /*ADVICE(745): NUMBER has no precision [315] */
758 ;
759 BEGIN
760 l_index := l_resource_tc_table.FIRST;
761
762 LOOP
763 EXIT WHEN (NOT l_resource_tc_table.EXISTS (l_index));
764
765 IF (1 >= fnd_log.g_current_runtime_level) THEN
766 fnd_log.STRING (
767 1,
768 'hxc_timekeeper_process.l_resource_tc_table',
769 'index_string:' || l_resource_tc_table (l_index).index_string || ' index:' || l_index
770 );
771 END IF;
772
773 l_index := l_resource_tc_table.NEXT (l_index);
774 END LOOP;
775 END dump_resource_tc_table;
776
777
778 -------------------------------------------------------------------------------
779 -- this procedure dump the buffer table information
780 -------------------------------------------------------------------------------
781 PROCEDURE dump_timecard (
782 p_timecard IN hxc_self_service_time_deposit.timecard_info
783 ) IS
784 l_index NUMBER
785 /*ADVICE(773): NUMBER has no precision [315] */
786 ;
787 BEGIN
788 l_index := p_timecard.FIRST;
789
790 LOOP
791 EXIT WHEN (NOT p_timecard.EXISTS (l_index));
792
793 IF (1 >= fnd_log.g_current_runtime_level) THEN
794 fnd_log.STRING (
795 1,
796 'hxc_timekeeper_process.dump_buffer_table',
797 'time_building_block_id :' || p_timecard (l_index).time_building_block_id || ' ovn :'
798 || p_timecard (l_index).object_version_number || ' start_time :' || p_timecard (l_index).start_time
799 || ' stop_time :' || p_timecard (l_index).stop_time || ' parent_id :'
800 || p_timecard (l_index).parent_building_block_id || ' parent_ovn :'
801 || p_timecard (l_index).parent_building_block_ovn || ' resource_id :'
802 || p_timecard (l_index).resource_id || ' resource_type :' || p_timecard (l_index).resource_type
803 || ' type :' || p_timecard (l_index).TYPE
804 );
805 fnd_log.STRING (
806 1,
807 'hxc_timekeeper_process.dump_buffer_table',
808 ' measure :' || p_timecard (l_index).measure || ' OFM :' || p_timecard (l_index).unit_of_measure
809 || ' parent_is_new :' || p_timecard (l_index).parent_is_new || ' scope :' || p_timecard (l_index).SCOPE
810 || ' app_status :' || p_timecard (l_index).approval_status || ' app_style_id :'
811 || p_timecard (l_index).approval_style_id || ' date_from :' || p_timecard (l_index).date_from
812 || ' date_to :' || p_timecard (l_index).date_to || ' comment_text :'
813 || p_timecard (l_index).comment_text || ' new :' || p_timecard (l_index).NEW || ' changed :'
814 || p_timecard (l_index).changed || ' index:' || l_index
815 );
816 END IF;
817
818 l_index := p_timecard.NEXT (l_index);
819 END LOOP;
820 END dump_timecard;
821
822
823 -------------------------------------------------------------------------------
824 -- this procedure used to give all timecards including midperiod timecards
825 -- saved in that range
826 -------------------------------------------------------------------------------
827 PROCEDURE populate_tc_tab (
828 resource_id IN NUMBER,
829 tc_frdt IN DATE,
830 tc_todt IN DATE,
831 emp_tc_info OUT NOCOPY hxc_timekeeper_utilities.emptctab
832 ) IS
833 CURSOR get_tc_data (
834 p_resource_id NUMBER,
835 p_tc_frdt DATE,
836 p_tc_todt DATE
837 ) IS
838 SELECT time_building_block_id tbbid, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
839 FROM hxc_time_building_blocks
840 WHERE resource_id
841 /*ADVICE(829): Cursor references an external variable (use a parameter) [209] */
842 = p_resource_id
843 AND SCOPE = 'TIMECARD'
844 AND date_to = hr_general.end_of_time
845 AND TO_DATE (p_tc_frdt, 'dd-mm-rrrr') BETWEEN TO_DATE (start_time, 'dd-mm-rrrr')
846 AND TO_DATE (stop_time, 'dd-mm-rrrr')
847 UNION
848 SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
849 FROM hxc_time_building_blocks
850 WHERE resource_id
851 /*ADVICE(839): Cursor references an external variable (use a parameter) [209] */
852 = p_resource_id
853 AND SCOPE = 'TIMECARD'
854 AND date_to = hr_general.end_of_time
855 AND TO_DATE (p_tc_todt, 'dd-mm-rrrr') BETWEEN TO_DATE (start_time, 'dd-mm-rrrr')
856 AND TO_DATE (stop_time, 'dd-mm-rrrr')
857 UNION
858 SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
859 FROM hxc_time_building_blocks
860 WHERE resource_id
861 /*ADVICE(849): Cursor references an external variable (use a parameter) [209] */
862 = p_resource_id
863 AND SCOPE = 'TIMECARD'
864 AND date_to = hr_general.end_of_time
865 AND TO_DATE (start_time, 'dd-mm-rrrr') >= TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
866 AND TO_DATE (stop_time, 'dd-mm-rrrr') <= TO_DATE (p_tc_todt, 'dd-mm-rrrr')
867 MINUS
868 SELECT time_building_block_id, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
869 FROM hxc_time_building_blocks
870 WHERE resource_id
871 /*ADVICE(859): Cursor references an external variable (use a parameter) [209] */
872 = p_resource_id
873 AND SCOPE = 'TIMECARD'
874 AND date_to = hr_general.end_of_time
875 AND TO_DATE (start_time, 'dd-mm-rrrr') = TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
876 AND TO_DATE (stop_time, 'dd-mm-rrrr') = TO_DATE (p_tc_todt, 'dd-mm-rrrr')
877 ORDER BY 4;
878
879 tc_tab_rec get_tc_data%ROWTYPE;
880 p_index NUMBER
881 /*ADVICE(869): NUMBER has no precision [315] */
882 := 0;
883 BEGIN
884 --This procedure is used to get all the timecrds saved
885 --in the time building blocks which are between from and to date.
886 --used in timekeeper query and disabling of fields.
887 emp_tc_info.DELETE;
888 OPEN get_tc_data (resource_id, tc_frdt, tc_todt);
889
890 LOOP
891 FETCH get_tc_data INTO tc_tab_rec;
892 EXIT WHEN get_tc_data%NOTFOUND;
893 --We index the emp_tc_info table by julian date
894 p_index := TO_NUMBER (TO_CHAR (tc_tab_rec.start_time, 'J'));
895 emp_tc_info (p_index).timecard_id := tc_tab_rec.tbbid;
896 emp_tc_info (p_index).resource_id := resource_id;
897 emp_tc_info (p_index).tc_frdt := tc_tab_rec.start_time;
898 emp_tc_info (p_index).tc_todt := tc_tab_rec.stop_time;
899 END LOOP;
900 END;
901
902
903 -------------------------------------------------------------------------------
904 -- this procedure used to query mid period timecards
905 -------------------------------------------------------------------------------
906 PROCEDURE populate_query_tc_tab (
907 resource_id IN NUMBER,
908 tc_frdt IN DATE,
909 tc_todt IN DATE,
910 emp_qry_tc_info OUT NOCOPY hxc_timekeeper_utilities.emptctab
911 ) IS
912 CURSOR get_tc_data (
913 p_resource_id NUMBER,
914 p_tc_frdt DATE,
915 p_tc_todt DATE
916 ) IS
917 /*
918 select start_time,stop_time,to_date(start_time)
919 from hxc_time_building_blocks
920 where resource_id = p_resource_id
921 and scope='TIMECARD'
922 and date_to=hr_general.end_of_time
923 and to_date(start_time,'dd-mm-rrrr') >=to_date(p_tc_frdt,'dd-mm-rrrr') and
924 to_date(stop_time,'dd-mm-rrrr') <=to_date(p_tc_todt,'dd-mm-rrrr')
925 union
926 select tc_frdt,tc_todt,to_date(tc_frdt)
927 from dual
928 order by 3;
929 */
930 SELECT TO_DATE (start_time, 'dd-mm-rrrr hh24:mi:ss') start_time,
931 TO_DATE (stop_time, 'dd-mm-rrrr hh24:mi:ss') stop_time, TO_DATE (start_time, 'dd-mm-rrrr') orddt
932 FROM hxc_time_building_blocks
933 WHERE resource_id
934 /*ADVICE(922): Cursor references an external variable (use a parameter) [209] */
935 = p_resource_id
936 AND SCOPE = 'TIMECARD'
937 AND date_to = hr_general.end_of_time
938 AND TO_DATE (start_time, 'dd-mm-rrrr') >= TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
939 AND TO_DATE (stop_time, 'dd-mm-rrrr') <= TO_DATE (p_tc_todt, 'dd-mm-rrrr')
940 UNION
941 SELECT TO_DATE (tc_frdt
942 /*ADVICE(930): Cursor references an external variable (use a parameter) [209] */
943 , 'dd-mm-rrrr hh24:mi:ss'), TO_DATE (tc_todt
944 /*ADVICE(932): Cursor references an external variable (use a parameter) [209] */
945 , 'dd-mm-rrrr hh24:mi:ss'),
946 TO_DATE (tc_frdt
947 /*ADVICE(935): Cursor references an external variable (use a parameter) [209] */
948 , 'dd-mm-rrrr')
949 FROM DUAL
950 ORDER BY 3;
951
952 tc_tab_rec get_tc_data%ROWTYPE;
953 p_index NUMBER
954 /*ADVICE(942): NUMBER has no precision [315] */
955 := 0;
956 BEGIN
957 ---This procedure is used to get the timecards which are exactly between the range selected
958 ---used in timekeeper query and disabling of fields.
959 -- if this table contains more than 1 row means in that period person is
960 -- mid period case.
961 emp_qry_tc_info.DELETE;
962 OPEN get_tc_data (resource_id, tc_frdt, tc_todt);
963
964 LOOP
965 FETCH get_tc_data INTO tc_tab_rec;
966 EXIT WHEN get_tc_data%NOTFOUND;
967 p_index := p_index + 1;
968 --p_index:=to_number(to_char(tc_tab_rec.start_time,'J'));
969 emp_qry_tc_info (p_index).resource_id := resource_id;
970 emp_qry_tc_info (p_index).tc_frdt := tc_tab_rec.start_time;
971 emp_qry_tc_info (p_index).tc_todt := tc_tab_rec.stop_time;
972 END LOOP;
973 END;
974
975
976 -------------------------------------------------------------------------------
977 -- this procedure gives split of timecards
978 -- Used in save procedure to break the timecard
979 --when monthly timecard is
980 -------------------------------------------------------------------------------
981 PROCEDURE split_timecard (
982 p_resource_id IN NUMBER,
983 p_start_date IN DATE,
984 p_end_date IN DATE,
985 p_spemp_tc_info IN hxc_timekeeper_utilities.emptctab,
986 p_tc_list OUT NOCOPY hxc_timecard_utilities.periods
987 ) IS
988 m_periods
989 /*ADVICE(977): Unreferenced variable [553] */
990 VARCHAR2 (2000);
991 /*ADVICE(979): VARCHAR2 declaration with length greater than 500 characters [307] */
992
993 newtab hxc_timecard_utilities.periods;
994 emp_tab_index NUMBER
995 /*ADVICE(983): NUMBER has no precision [315] */
996 := 0;
997 new_tab_index
998 /*ADVICE(986): Unreferenced variable [553] */
999 NUMBER
1000 /*ADVICE(988): NUMBER has no precision [315] */
1001 := 0;
1002 l_emp_negpref VARCHAR2 (150);
1003 l_emp_recpref NUMBER
1004 /*ADVICE(992): NUMBER has no precision [315] */
1005 ;
1006 l_emp_appstyle NUMBER
1007 /*ADVICE(995): NUMBER has no precision [315] */
1008 ;
1009 l_emp_layout1 NUMBER
1010 /*ADVICE(998): NUMBER has no precision [315] */
1011 ;
1012 l_emp_layout2 NUMBER
1013 /*ADVICE(1001): NUMBER has no precision [315] */
1014 ;
1015 l_emp_layout3 NUMBER
1016 /*ADVICE(1004): NUMBER has no precision [315] */
1017 ;
1018 l_emp_layout4 NUMBER
1019 /*ADVICE(1007): NUMBER has no precision [315] */
1020 ;
1021 l_emp_layout5 NUMBER
1022 /*ADVICE(1010): NUMBER has no precision [315] */
1023 ;
1024 l_emp_layout6 NUMBER
1025 /*ADVICE(1013): NUMBER has no precision [315] */
1026 ;
1027 l_emp_layout7 NUMBER
1028 /*ADVICE(1016): NUMBER has no precision [315] */
1029 ;
1030 l_emp_layout8 NUMBER
1031 /*ADVICE(1019): NUMBER has no precision [315] */
1032 ;
1033 l_emp_edits VARCHAR2 (150);
1034 l_pastdt VARCHAR2 (30);
1035 l_futuredt VARCHAR2 (30);
1036 l_emp_start_date DATE;
1037 l_emp_terminate_date DATE;
1038 l_audit_enabled VARCHAR2 (150);
1039 BEGIN
1040 g_debug :=hr_utility.debug_enabled;
1041 newtab.DELETE;
1042 if g_debug then
1043 hr_utility.trace('p_resource_id '||p_resource_id);
1044 hr_utility.trace('p_end_date'||to_date(p_end_date,'dd-mm-rrrr'));
1045 hr_utility.trace('start '||to_date(p_start_date,'dd-mm-rrrr'));
1046 end if;
1047 l_emp_negpref := NULL;
1048 l_emp_recpref := NULL;
1049 l_emp_appstyle := NULL;
1050 l_emp_layout1 := NULL;
1051 l_emp_layout2 := NULL;
1052 l_emp_layout3 := NULL;
1053 l_emp_layout4 := NULL;
1054 l_emp_layout5 := NULL;
1055 l_emp_layout6 := NULL;
1056 l_emp_layout7 := NULL;
1057 l_emp_layout8 := NULL;
1058 l_emp_edits := NULL;
1059 hxc_timekeeper_utilities.get_emp_pref (
1060 p_resource_id,
1061 l_emp_negpref,
1062 l_emp_recpref,
1063 l_emp_appstyle,
1064 l_emp_layout1,
1065 l_emp_layout2,
1066 l_emp_layout3,
1067 l_emp_layout4,
1068 l_emp_layout5,
1069 l_emp_layout6,
1070 l_emp_layout7,
1071 l_emp_layout8,
1072 l_emp_edits,
1073 l_pastdt,
1074 l_futuredt,
1075 l_emp_start_date,
1076 l_emp_terminate_date,
1077 l_audit_enabled
1078 );
1079
1080 if g_debug then
1081 hr_utility.trace(' just checking cout'||p_spemp_tc_info.count);
1082 end if;
1083 IF p_spemp_tc_info.COUNT > 0 THEN --this means the person is a mid period change
1084 --and we need to split the timecard.
1085 if g_debug then
1086 hr_utility.trace('get the periods');
1087 end if;
1088
1089 get_resource_time_periods (
1090 p_resource_id => p_resource_id,
1091 p_resource_type => 'PERSON',
1092 p_current_date => SYSDATE,
1093 p_max_date_in_futur => TO_DATE (p_end_date, 'dd-mm-rrrr') + 1,
1094 p_max_date_in_past => TO_DATE (p_start_date, 'dd-mm-rrrr') - 1,
1095 p_recurring_period_id => l_emp_recpref,
1096 p_check_assignment => TRUE,
1097 p_periodtab => newtab
1098 );
1099 --New tab contains the timecard periods in that range through which
1100 --we loop and save the timecard
1101 if g_debug then
1102 hr_utility.trace('newtab.count'||newtab.count);
1103 end if;
1104 emp_tab_index := p_spemp_tc_info.FIRST;
1105
1106 LOOP
1107 EXIT WHEN NOT p_spemp_tc_info.EXISTS (emp_tab_index);
1108
1109 if g_debug then
1110 hr_utility.trace(emp_tab_index);
1111 end if;
1112 IF TO_NUMBER (emp_tab_index) < TO_NUMBER (TO_CHAR (TO_DATE (p_start_date, 'dd-mm-rrrr'), 'J'))
1113 OR TO_NUMBER (emp_tab_index) > TO_NUMBER (TO_CHAR (TO_DATE (p_end_date, 'dd-mm-rrrr'), 'J')) THEN
1114 --remove the rows from the pl/sql table table which are out of range
1115 --incomplete timecard
1116 IF newtab.EXISTS (emp_tab_index) THEN
1117 newtab.DELETE (emp_tab_index);
1118 if g_debug then
1119 hr_utility.trace('in delete emptab ');
1120 end if;
1121 END IF;
1122 END IF;
1123
1124 emp_tab_index := p_spemp_tc_info.NEXT (emp_tab_index);
1125 END LOOP;
1126 ELSE
1127 IF TO_DATE (TRUNC (p_start_date), 'dd-mm-rrrr') < TO_DATE (l_emp_start_date, 'dd-mm-rrrr') THEN
1128 if g_debug then
1129 hr_utility.trace('mid hire employee1 ');
1130 hr_utility.trace('p_start_date'|| l_emp_start_date);
1131 end if;
1132 newtab (TO_NUMBER (TO_CHAR (TO_DATE (l_emp_start_date, 'dd-mm-rrrr'), 'J'))).start_date := l_emp_start_date;
1133 /* changes done by senthil for emp terminate enhancement*/
1134 IF TO_DATE (TRUNC (p_end_date), 'dd-mm-rrrr') > TO_DATE (nvl(l_emp_terminate_date,p_end_date), 'dd-mm-rrrr') THEN
1135 if g_debug then
1136 hr_utility.trace('Terminated employee ');
1137 hr_utility.trace('p_end_date'|| nvl(l_emp_terminate_date,p_end_date));
1138 end if;
1139 newtab (TO_NUMBER (TO_CHAR (TO_DATE (l_emp_start_date, 'dd-mm-rrrr'), 'J'))).end_date := nvl(l_emp_terminate_date,p_end_date);
1140 else
1141 if g_debug then
1142 hr_utility.trace(' Normal employee ');
1143 hr_utility.trace('p_end_date'|| p_end_date);
1144 end if;
1145 newtab (TO_NUMBER (TO_CHAR (TO_DATE (l_emp_start_date, 'dd-mm-rrrr'), 'J'))).end_date := p_end_date;
1146 end if;
1147 /* end of changes made by senthil */
1148 ELSE
1149 if g_debug then
1150 hr_utility.trace(' Normal employee ');
1151 hr_utility.trace('p_start_date'|| p_start_date);
1152 end if;
1153 newtab (TO_NUMBER (TO_CHAR (TO_DATE (p_start_date, 'dd-mm-rrrr'), 'J'))).start_date := p_start_date;
1154 /* changes done by senthil for emp terminate enhancement*/
1155 IF TO_DATE (TRUNC (p_end_date), 'dd-mm-rrrr') > TO_DATE (nvl(l_emp_terminate_date,p_end_date), 'dd-mm-rrrr') THEN
1156 if g_debug then
1157 hr_utility.trace('Terminated employee ');
1158 hr_utility.trace('p_end_date'|| nvl(l_emp_terminate_date,p_end_date));
1159 end if;
1160 newtab (TO_NUMBER (TO_CHAR (TO_DATE (p_start_date, 'dd-mm-rrrr'), 'J'))).end_date := nvl(l_emp_terminate_date,p_end_date);
1161 else
1162 if g_debug then
1163 hr_utility.trace(' Normal employee ');
1164 hr_utility.trace('p_end_date'|| p_end_date);
1165 end if;
1166 newtab (TO_NUMBER (TO_CHAR (TO_DATE (p_start_date, 'dd-mm-rrrr'), 'J'))).end_date := p_end_date;
1167 end if;
1168 /* end of changes made by senthil */
1169 end if;
1170
1171 END IF;
1172 if g_debug then
1173 hr_utility.trace('newtab.count'||newtab.count);
1174 end if;
1175 p_tc_list := newtab;
1176
1177 if g_debug then
1178 hr_utility.trace('p_tc_list.count'||p_tc_list.count);
1179 end if;
1180 END;
1181
1182
1183 ----------------------------------------------------------------------------
1184 -- add_resource_to_perftab is used to popluate the global pl/sql resource
1185 -- preference table
1186 ----------------------------------------------------------------------------
1187 PROCEDURE add_resource_to_perftab (
1188 p_resource_id IN NUMBER,
1189 p_pref_code IN VARCHAR2,
1190 p_attribute1 IN VARCHAR2,
1191 p_attribute2 IN VARCHAR2,
1192 p_attribute3 IN VARCHAR2,
1193 p_attribute4 IN VARCHAR2,
1194 p_attribute5 IN VARCHAR2,
1195 p_attribute6 IN VARCHAR2,
1196 p_attribute7 IN VARCHAR2,
1197 p_attribute8 IN VARCHAR2,
1198 p_attribute11 IN VARCHAR2
1199 ) IS
1200 BEGIN
1201 IF (p_pref_code = 'TC_W_ALW_NEG_TIME') THEN
1202 g_resource_perftab (p_resource_id).res_negentry := p_attribute1;
1203 ELSIF (p_pref_code = 'TC_W_TCRD_PERIOD') THEN
1204 g_resource_perftab (p_resource_id).res_recperiod := p_attribute1;
1205 ELSIF (p_pref_code = 'TC_W_TCRD_LAYOUT') THEN
1206 g_resource_perftab (p_resource_id).res_layout1 := p_attribute1;
1207 g_resource_perftab (p_resource_id).res_layout2 := p_attribute2;
1208 g_resource_perftab (p_resource_id).res_layout3 := p_attribute3;
1209 g_resource_perftab (p_resource_id).res_layout4 := p_attribute4;
1210 g_resource_perftab (p_resource_id).res_layout5 := p_attribute5; --CTK
1211 g_resource_perftab (p_resource_id).res_layout6 := p_attribute6; --CHECK OUT
1212 g_resource_perftab (p_resource_id).res_layout7 := p_attribute7;
1213 g_resource_perftab (p_resource_id).res_layout8 := p_attribute8;
1214 ELSIF (p_pref_code = 'TS_PER_APPROVAL_STYLE') THEN
1215 g_resource_perftab (p_resource_id).res_appstyle := p_attribute1;
1216 ELSIF (p_pref_code = 'TS_PER_AUDIT_REQUIREMENTS') THEN
1217 g_resource_perftab (p_resource_id).res_audit_enabled := p_attribute1;
1218 ELSIF (p_pref_code = 'TC_W_TCRD_ST_ALW_EDITS') THEN
1219 g_resource_perftab (p_resource_id).res_edits := p_attribute1;
1220
1221 IF p_attribute6 IS NULL THEN
1222 g_resource_perftab (p_resource_id).res_past_date := '0001/01/01';
1223 ELSE
1224 g_resource_perftab (p_resource_id).res_past_date :=
1225 TO_CHAR ((SYSDATE - TO_NUMBER (p_attribute6)), 'YYYY/MM/DD');
1226 END IF;
1227
1228 IF p_attribute11 IS NULL THEN
1229 g_resource_perftab (p_resource_id).res_future_date := '4712/12/31';
1230 ELSE
1231 g_resource_perftab (p_resource_id).res_future_date :=
1232 TO_CHAR ((SYSDATE + TO_NUMBER (p_attribute11)), 'YYYY/MM/DD');
1233 END IF;
1234 END IF;
1235 END;
1236
1237
1238 ----------------------------------------------------------------------------
1239 -- Called from timekeeper process to get the preference
1240 -- associated with a resource
1241 -- instead of calling preference evaluation cache the info.
1242 ----------------------------------------------------------------------------
1243 PROCEDURE get_emp_pref (
1244 p_resource_id IN NUMBER,
1245 neg_pref OUT NOCOPY VARCHAR2,
1246 recpref OUT NOCOPY NUMBER,
1247 appstyle OUT NOCOPY NUMBER,
1248 layout1 OUT NOCOPY NUMBER,
1249 layout2 OUT NOCOPY NUMBER,
1250 layout3 OUT NOCOPY NUMBER,
1251 layout4 OUT NOCOPY NUMBER,
1252 layout5 OUT NOCOPY NUMBER,
1253 layout6 OUT NOCOPY NUMBER,
1254 layout7 OUT NOCOPY NUMBER,
1255 layout8 OUT NOCOPY NUMBER,
1256 edits OUT NOCOPY VARCHAR2,
1257 l_pastdate OUT NOCOPY VARCHAR2,
1258 l_futuredate OUT NOCOPY VARCHAR2,
1259 l_emp_start_date OUT NOCOPY DATE,
1260 l_emp_terminate_date OUT NOCOPY DATE,
1261 l_audit_enabled OUT NOCOPY VARCHAR2
1262 ) IS
1263 l_index NUMBER
1264 /*ADVICE(1197): NUMBER has no precision [315] */
1265 := 0;
1266 l_pref_table hxc_preference_evaluation.t_pref_table;
1267
1268
1269 /*
1270 CURSOR c_emp_hireinfo (
1271 p_resource_id
1272 /*ADVICE(1203): This definition hides another one [556] */
1273 /* NUMBER
1274 ) IS
1275 select max(date_start) from
1276 per_periods_of_service
1277 where person_id = p_resource_id; */
1278
1279
1280
1281 -- Bug 7454062
1282 --
1283 -- Rewrote the above cursor as below.
1284 -- Changes :
1285 -- 1. Added a UNION with per_periods_of_placement, cos we need
1286 -- CWK information also.
1287 -- 2. Added two more input values, the timecard start and stop dates
1288 -- because we want to check which is the hire date which suits the
1289 -- given timecard.
1290 -- Example provided below before call to this cursor.
1291 -- 3. Coalesce function would pick up which ever is not NULL in the
1292 -- order specified. We dont want the AND clause to fail when
1293 -- the period of service is still active ( it has FPD and ATD Null in
1294 -- case of an active period of service ).
1295 -- 4. The TC start and stop dates are picked up from the timekeeper profiles
1296 -- and they take care of the below scenarios.
1297 --
1298 --
1299 --
1300 -- A-------------------A
1301 -- * T-------T
1302 -- * T---------T
1303 -- * T---------T
1304 --
1305 -- A -- Assigment
1306 -- T -- Timecard.
1307
1308
1309
1310 CURSOR c_emp_hireinfo ( p_resource_id NUMBER,
1311 p_tc_start_date DATE,
1312 p_tc_end_date DATE )
1313 IS SELECT date_start
1314 FROM per_periods_of_service
1315 WHERE person_id = p_resource_id
1316 AND date_start <= p_tc_end_date
1317 AND COALESCE(final_process_date,actual_termination_date,
1318 hr_general.end_of_time) >= p_tc_start_date
1319 UNION
1320 SELECT date_start
1321 FROM per_periods_of_placement
1322 WHERE person_id = p_resource_id
1323 AND date_start <= p_tc_end_date
1324 AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)),actual_termination_date,
1325 hr_general.end_of_time) >= p_tc_start_date
1326 ORDER BY date_start ;
1327
1328
1329 /*Cursor Modified By Mithun for CWK Terminate Bug*/
1330 /* changes done by senthil for emp terminate enhancement*/
1331 /* CURSOR c_emp_terminateinfo(
1332 p_resource_id NUMBER
1333 ) IS
1334 SELECT final_process_date, date_start
1335 FROM per_periods_of_service
1336 WHERE person_id = p_resource_id
1337 union all
1338 SELECT (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
1339 FROM PER_PERIODS_OF_placement
1340 WHERE person_id = p_resource_id
1341 ORDER BY date_start DESC;
1342 */
1343
1344
1345
1346 -- Bug 7454062
1347 --
1348 -- Rewrote the above cursor as below
1349 -- Changes :
1350 -- 1. Added tc start and stop input values to pick up
1351 -- the correct period of service suitable for
1352 -- the selected timecard range.
1353 --
1354
1355 CURSOR c_emp_terminateinfo( p_resource_id NUMBER,
1356 p_tc_start_date DATE,
1357 p_tc_end_date DATE )
1358 IS SELECT final_process_date,
1359 date_start
1360 FROM per_periods_of_service
1361 WHERE person_id = p_resource_id
1362 AND date_start <= p_tc_end_date
1363 AND COALESCE(final_process_date,actual_termination_date,
1364 hr_general.end_of_time) >= p_tc_start_date
1365 UNION
1366 SELECT (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date,
1367 date_start
1368 FROM PER_PERIODS_OF_placement
1369 WHERE person_id = p_resource_id
1370 AND date_start <= p_tc_end_date
1371 AND COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)),actual_termination_date,
1372 hr_general.end_of_time) >= p_tc_start_date
1373 ORDER BY date_start DESC;
1374
1375
1376
1377
1378
1379 --Added By Mithun for CWK Terminate Bug
1380 l_date_start DATE;
1381 /* end of changes made by senthil */
1382
1383
1384 -- Bug 7454062
1385 l_tc_start_date DATE;
1386 l_tc_end_date DATE;
1387
1388
1389 BEGIN
1390
1391
1392 -- Bug 7454062
1393
1394 l_tc_start_date := FND_PROFILE.VALUE('OTL_TK_START_DATE');
1395 l_tc_end_date := FND_PROFILE.VALUE('OTL_TK_END_DATE');
1396
1397 IF p_resource_id IS NOT NULL THEN
1398 IF NOT g_resource_perftab.EXISTS (p_resource_id) THEN
1399 hxc_preference_evaluation.resource_preferences (
1400 p_resource_id,
1401 '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',
1402 l_pref_table
1403 );
1404 l_index := l_pref_table.FIRST;
1405
1406 LOOP
1407 EXIT WHEN (NOT l_pref_table.EXISTS (l_index));
1408 add_resource_to_perftab (
1409 p_resource_id => p_resource_id,
1410 p_pref_code => l_pref_table (l_index).preference_code,
1411 p_attribute1 => l_pref_table (l_index).attribute1,
1412 p_attribute2 => l_pref_table (l_index).attribute2,
1413 p_attribute3 => l_pref_table (l_index).attribute3,
1414 p_attribute4 => l_pref_table (l_index).attribute4,
1415 p_attribute5 => l_pref_table (l_index).attribute5,
1416 p_attribute6 => l_pref_table (l_index).attribute6,
1417 p_attribute7 => l_pref_table (l_index).attribute7,
1418 p_attribute8 => l_pref_table (l_index).attribute8,
1419 p_attribute11 => l_pref_table (l_index).attribute11
1420 );
1421 l_index := l_pref_table.NEXT (l_index);
1422 END LOOP;
1423
1424 -- Bug 7454062
1425
1426 -- Moved the below code to outside the IF statement, as we want it to get calculated
1427 -- each time, a new date range may be specified for a second call.
1428
1429 /*
1430 OPEN c_emp_hireinfo (p_resource_id => p_resource_id);
1431 FETCH c_emp_hireinfo INTO g_resource_perftab (p_resource_id).res_emp_start_date;
1432 CLOSE c_emp_hireinfo;
1433 /* changes done by senthil for emp terminate enhancement
1434 OPEN c_emp_terminateinfo (p_resource_id => p_resource_id);
1435 /*Changed By Mithun for CWK Terminate Bug
1436 FETCH c_emp_terminateinfo INTO g_resource_perftab (p_resource_id).res_emp_terminate_date, l_date_start;
1437 CLOSE c_emp_terminateinfo;
1438 /* end of changes */
1439
1440
1441 END IF;
1442
1443
1444 -- Bug 7454062
1445 --
1446 -- Added start and stop dates of the timecards to the below cursors to have
1447 -- multiple periods of active assignment.
1448 -- For ex: First active assignment 1-Jan-2007 - 31-Dec-2007
1449 -- Second active assignment 1-Jun-2008 - end of time.
1450 -- Without the date inputs, the query for hire date would always return
1451 -- 1-Jun-2008, hence no timecard can be entered in the first period.
1452 -- Similarly, without the date inputs, the terminate query would always
1453 -- return the final process date of the last active assignment, which is
1454 -- end of time in this case. But for a timecard in 2007, we need termination
1455 -- date to be 31-Dec-2007.
1456
1457
1458 OPEN c_emp_hireinfo (p_resource_id => p_resource_id,
1459 p_tc_start_date => l_tc_start_date,
1460 p_tc_end_date => l_tc_end_date );
1461
1462 FETCH c_emp_hireinfo INTO g_resource_perftab (p_resource_id).res_emp_start_date;
1463 CLOSE c_emp_hireinfo;
1464
1465
1466 OPEN c_emp_terminateinfo (p_resource_id => p_resource_id,
1467 p_tc_start_date => l_tc_start_date,
1468 p_tc_end_date => l_tc_end_date );
1469
1470 FETCH c_emp_terminateinfo INTO g_resource_perftab (p_resource_id).res_emp_terminate_date, l_date_start;
1471 CLOSE c_emp_terminateinfo;
1472
1473
1474
1475 neg_pref := g_resource_perftab (p_resource_id).res_negentry;
1476 recpref := TO_NUMBER (g_resource_perftab (p_resource_id).res_recperiod);
1477 appstyle := TO_NUMBER (g_resource_perftab (p_resource_id).res_appstyle);
1478 layout1 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout1);
1479 layout2 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout2);
1480 layout3 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout3);
1481 layout4 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout4);
1482 layout5 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout5);
1483 layout6 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout6);
1484 layout7 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout7);
1485 layout8 := TO_NUMBER (g_resource_perftab (p_resource_id).res_layout8);
1486 edits := g_resource_perftab (p_resource_id).res_edits;
1487 l_pastdate := g_resource_perftab (p_resource_id).res_past_date;
1488 l_futuredate := g_resource_perftab (p_resource_id).res_future_date;
1489 l_emp_start_date := g_resource_perftab (p_resource_id).res_emp_start_date;
1490 l_emp_terminate_date := g_resource_perftab (p_resource_id).res_emp_terminate_date;
1491 l_audit_enabled := g_resource_perftab (p_resource_id).res_audit_enabled;
1492 END IF;
1493
1494 END;
1495
1496
1497 ----------------------------------------------------------------------------
1498 -- get_resource_time_periods return the list of period for
1499 -- a range of time
1500 -- The p_check_assignment is not used for the moment.
1501 ----------------------------------------------------------------------------
1502 PROCEDURE get_resource_time_periods (
1503 p_resource_id IN VARCHAR2,
1504 p_resource_type IN VARCHAR2,
1505 p_current_date IN DATE,
1506 p_max_date_in_futur IN DATE,
1507 p_max_date_in_past IN DATE,
1508 p_recurring_period_id IN NUMBER,
1509 p_check_assignment IN BOOLEAN,
1510 p_periodtab IN OUT NOCOPY hxc_timecard_utilities.periods
1511 ) IS
1512 CURSOR c_timecards (
1513 p_resource_id
1514 /*ADVICE(1285): This definition hides another one [556] */
1515 IN NUMBER,
1516 p_resource_type
1517 /*ADVICE(1288): This definition hides another one [556] */
1518 IN VARCHAR2,
1519 p_first_start_date IN DATE,
1520 p_last_end_date IN DATE
1521 ) IS
1522 SELECT TRUNC (start_time), TRUNC (stop_time)
1523 FROM hxc_time_building_blocks
1524 WHERE SCOPE = 'TIMECARD'
1525 AND date_to = hr_general.end_of_time
1526 AND resource_id = p_resource_id
1527 AND resource_type = p_resource_type
1528 AND stop_time >= p_first_start_date
1529 AND start_time <= p_last_end_date;
1530
1531
1532 --ORDER BY START_TIME;
1533 CURSOR c_period_info (
1534 p_recurring_period_id
1535 /*ADVICE(1306): This definition hides another one [556] */
1536 NUMBER
1537 ) IS
1538 SELECT hrp.period_type, hrp.duration_in_days, hrp.start_date
1539 FROM hxc_recurring_periods hrp
1540 WHERE hrp.recurring_period_id = p_recurring_period_id;
1541
1542 /*
1543 CURSOR c_emp_hireinfo (
1544 p_resource_id
1545 /*ADVICE(1315): This definition hides another one [556] */
1546 /* NUMBER
1547 ) IS
1548 select max(date_start) from
1549 per_periods_of_service
1550 where person_id = p_resource_id;
1551 /*SELECT MIN (effective_start_date)
1552 FROM per_people_f
1553 WHERE person_id = p_resource_id;*/
1554
1555
1556 -- Bug 7454062
1557 --
1558 -- Rewrote the above cursor as below.
1559 -- We need the first hire date or the first period of service start date
1560 -- which falls within the given max date in past and max date in future
1561 -- rather than the last assignment's start date. The above cursor
1562 -- would work only for the most recent assignment.
1563 -- COALESCE would pick up the first not NULL value amongst the ones
1564 -- given in bracket.
1565
1566
1567 CURSOR c_emp_hireinfo ( p_resource_id NUMBER,
1568 p_past_date DATE )
1569 IS SELECT date_start
1570 FROM per_periods_of_service
1571 WHERE person_id = p_resource_id
1572 AND COALESCE(final_process_date,actual_termination_date,
1573 hr_general.end_of_time) > p_past_date
1574 UNION
1575 SELECT date_start
1576 FROM per_periods_of_placement
1577 WHERE person_id = p_resource_id
1578 AND COALESCE(final_process_date,actual_termination_date,
1579 hr_general.end_of_time) > p_past_date
1580 ORDER BY date_start ;
1581
1582
1583
1584 /*Cursor Modified By Mithun for CWK Terminate Bug*/
1585 /* changes done by senthil for emp terminate enhancement*/
1586 CURSOR c_emp_terminateinfo(
1587 p_resource_id NUMBER
1588 ) IS
1589 SELECT final_process_date, date_start
1590 FROM per_periods_of_service
1591 WHERE person_id = p_resource_id
1592 union all
1593 select (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
1594 from per_periods_of_placement
1595 where person_id = p_resource_id
1596 ORDER BY date_start DESC;
1597
1598
1599 -- Bug 7454062
1600 --
1601 -- Added a cursor below to pick up the time periods for which the
1602 -- employee is inactive. From c_emp_hireinfo cursor, we pick up
1603 -- the earliest hire date for the person, and generate the time periods
1604 -- for this start date. The below cursor is used to trim off the
1605 -- inactive periods from the list of time periods.
1606 -- LEAD picks up the next records date_start, and returns a NULL for the
1607 -- last record retrieved. COALESCE picks up the first non NULL value from
1608 -- the ones given in bracket.
1609
1610
1611 CURSOR c_emp_inactive ( p_resource_id NUMBER )
1612 IS SELECT final_process_date start_date,
1613 NVL(LEAD(date_start) OVER (ORDER BY date_start)-1,hr_general.end_of_time) end_date
1614 FROM ( SELECT date_start,
1615 COALESCE(final_process_date+1,actual_termination_date+1,hr_general.end_of_time)
1616 final_process_date
1617 FROM per_periods_of_service
1618 WHERE person_id = p_resource_id
1619 UNION
1620 SELECT date_start,
1621 COALESCE((final_process_date+NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0))+1,actual_termination_date+1,hr_general.end_of_time)
1622 final_process_date
1623 FROM per_periods_of_placement
1624 WHERE person_id = p_resource_id
1625 )
1626 ORDER BY start_date;
1627
1628
1629 --Added By Mithun for CWK Terminate Bug
1630 l_date_start DATE;
1631
1632
1633 CURSOR c_term_timecards_exists(
1634 p_resource_id IN NUMBER,
1635 p_terminate_date IN DATE
1636 ) IS
1637 SELECT count(*)
1638 FROM hxc_time_building_blocks
1639 WHERE SCOPE = 'TIMECARD'
1640 AND (date_to = hr_general.end_of_time or APPROVAL_STATUS='ERROR')
1641 AND resource_id = p_resource_id
1642 AND p_terminate_date between start_time and stop_time;
1643 /* end of changes made by senthil */
1644
1645
1646 -- index
1647 l_index NUMBER (15) ;
1648 l_index_timecard_tab NUMBER (15);
1649
1650 -- PL/SQL Table
1651 l_timecard_tab hxc_timecard_utilities.periods;
1652 l_temp_periods hxc_period_evaluation.period_list;
1653
1654 l_inactive_periods hxc_period_evaluation.period_list;
1655
1656 l_period_start DATE;
1657 l_period_end DATE;
1658 l_tc_period_start_date DATE;
1659 l_tc_period_end_date DATE;
1660 l_rec_period_start_date DATE;
1661 l_person_effective_start_date DATE;
1662 l_person_effective_end_date DATE;
1663 l_touch_period_in_tc BOOLEAN;
1664
1665 --l_dividend NUMBER;
1666 l_period_type hxc_recurring_periods.period_type%TYPE;
1667 l_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
1668 l_emp_max_date_in_past DATE;
1669 l_emp_max_date_in_futur DATE;
1670 l_start_date DATE;
1671 l_end_date DATE;
1672 l_first_rowindex NUMBER(15);
1673 l_last_rowindex NUMBER(15);
1674 l_term_tc_exists NUMBER(15);
1675 BEGIN
1676 g_debug :=hr_utility.debug_enabled;
1677 --
1678 -- look for the recurring period type
1679 --
1680 OPEN c_period_info (p_recurring_period_id => p_recurring_period_id);
1681 FETCH c_period_info INTO l_period_type, l_duration_in_days, l_rec_period_start_date;
1682 /*ADVICE(1365): FETCH into a list of variables instead of a record [204] */
1683
1684 CLOSE c_period_info;
1685
1686 IF p_check_assignment = TRUE THEN
1687 OPEN c_emp_hireinfo (p_resource_id => p_resource_id,
1688 p_past_date => p_max_date_in_past);
1689 FETCH c_emp_hireinfo INTO l_person_effective_start_date;
1690 CLOSE c_emp_hireinfo;
1691
1692 /*Changes Done By Mithun for CWK Terminate Bug*/
1693 /* changes done by senthil for emp terminate enhancement*/
1694 OPEN c_emp_terminateinfo (p_resource_id => p_resource_id);
1695 FETCH c_emp_terminateinfo INTO l_person_effective_end_date, l_date_start;
1696 CLOSE c_emp_terminateinfo;
1697 /* end of changes */
1698
1699 --2789497
1700 IF p_max_date_in_past <= NVL (l_person_effective_start_date, p_max_date_in_past) THEN
1701 l_emp_max_date_in_past := NVL (l_person_effective_start_date, p_max_date_in_past);
1702 ELSE
1703 l_emp_max_date_in_past := p_max_date_in_past;
1704 END IF;
1705 --2789497
1706 /* changes done by senthil for emp terminate enhancement*/
1707 IF g_debug THEN
1708 hr_utility.trace('terminate end_date'||NVL(l_person_effective_end_date,p_max_date_in_futur));
1709 END if;
1710 IF p_max_date_in_futur >= NVL(l_person_effective_end_date,p_max_date_in_futur) THEN
1711 l_emp_max_date_in_futur := NVL(l_person_effective_end_date,p_max_date_in_futur);
1712 ELSE
1713 l_emp_max_date_in_futur := p_max_date_in_futur;
1714 END IF;
1715 IF g_debug THEN
1716 hr_utility.trace('p_max_date_in_futur'||p_max_date_in_futur);
1717 hr_utility.trace('l_emp_max_date_in_futur'||l_emp_max_date_in_futur);
1718 END if;
1719 /* Changes done by senthil */
1720
1721 ELSE
1722 l_emp_max_date_in_past := p_max_date_in_past;
1723 l_emp_max_date_in_futur := p_max_date_in_futur;
1724 END IF;
1725
1726
1727 -- We are finding the timecard for this period and add them
1728 -- to the pl/sql table
1729 OPEN c_timecards (
1730 p_resource_id => p_resource_id,
1731 p_resource_type => p_resource_type,
1732 p_first_start_date => l_emp_max_date_in_past,
1733 p_last_end_date => l_emp_max_date_in_futur
1734 );
1735
1736 LOOP
1737 FETCH c_timecards INTO l_period_start, l_period_end;
1738 /*ADVICE(1400): FETCH into a list of variables instead of a record [204] */
1739
1740 EXIT WHEN c_timecards%NOTFOUND;
1741 -- add the timecard in the pl/sql table
1742 -- here we are indexing by date JULIAN number to order the pl/sql table.
1743 l_timecard_tab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
1744 l_timecard_tab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_period_end;
1745 END LOOP;
1746
1747 CLOSE c_timecards;
1748
1749 -- add the timecard table into the period tab
1750 --p_periodtab := l_timecard_tab;
1751 -- we are calling to get the all period.
1752 -- between the start date and end date.
1753 l_temp_periods :=
1754 hxc_period_evaluation.get_period_list (
1755 p_current_date => p_current_date,
1756 p_recurring_period_type => l_period_type,
1757 p_duration_in_days => l_duration_in_days,
1758 p_rec_period_start_date => l_rec_period_start_date,
1759 p_max_date_in_futur => l_emp_max_date_in_futur,
1760 p_max_date_in_past => l_emp_max_date_in_past
1761 );
1762
1763
1764 -- Bug 7454062
1765 --
1766 -- The below construct added to trim off the inactive periods
1767 -- from the generated periods list l_temp_periods.
1768 -- Eg. Lets say this is how the periods of service are
1769 --
1770 --
1771 -- date_start final_process_date
1772 -- 1-jan-07 30-jun-07
1773 -- 1-jan-08 30-jun-08
1774 -- 1-jan-09
1775 --
1776 --
1777 -- The below cursor would return something like this.
1778 -- start_date end_date
1779 -- 1-Jul-07 31-Dec-07
1780 -- 1-Jul-08 31-Dec-08
1781 -- end_of_time end_of_time
1782 --
1783 -- These date ranges are inactive periods and anything which falls in these date
1784 -- ranges needs to be deleted from l_temp_periods.
1785 --
1786
1787 OPEN c_emp_inactive(p_resource_id);
1788 FETCH c_emp_inactive BULK
1789 COLLECT INTO l_inactive_periods;
1790 CLOSE c_emp_inactive;
1791
1792 -- l_temp_periods as well as l_inactive_periods is accessed in sorted order
1793 -- Hence we neednt Nest the GENERATED_PERIODS loop completely. The iterator
1794 -- can start from where it left for the last iteration of INACTIVE_PERIODS
1795 l_index := l_temp_periods.FIRST;
1796
1797 <<INACTIVE_PERIODS>>
1798 FOR i IN l_inactive_periods.FIRST..l_inactive_periods.LAST
1799 LOOP
1800 -- We neednt consider this inactive period if the start_date is end_of_time
1801 -- or the start_date and end_date dont have atleast one day in between.
1802 IF (l_inactive_periods(i).start_date <> hr_general.end_of_time)
1803 AND (l_inactive_periods(i).end_date - l_inactive_periods(i).start_date > 1)
1804
1805 THEN
1806 <<GENERATED_PERIODS>>
1807 LOOP
1808 EXIT WHEN NOT l_temp_periods.EXISTS(l_index);
1809 -- If the generated period is completely less than the inactive period
1810 -- do nothing, go to the next generated period.
1811 IF l_temp_periods(l_index).end_date < l_inactive_periods(i).start_date
1812 THEN
1813 NULL;
1814 -- If the generated period is completely greater than the inactive period
1815 -- exit this looping of generated period. Check for the same generated period
1816 -- and the next inactive period.
1817 ELSIF l_inactive_periods(i).end_date < l_temp_periods(l_index).start_date
1818 THEN
1819 EXIT GENERATED_PERIODS ;
1820 -- If generated period's start_date falls between the inactive period
1821 ELSE
1822 IF (l_temp_periods(l_index).start_date BETWEEN l_inactive_periods(i).start_date
1823 AND l_inactive_periods(i).end_date)
1824 THEN
1825 -- and if the generated period's end_date falls between the inactive period
1826 -- I-------------------------------------I
1827 -- G---------G
1828 IF l_temp_periods(l_index).end_date BETWEEN l_inactive_periods(i).start_date
1829 AND l_inactive_periods(i).end_date
1830 THEN
1831 -- delete the generated period.
1832 l_temp_periods.DELETE(l_index);
1833 ELSE
1834 -- generated period's end_date falls outside the inactive period.
1835 -- I------------------------------------I
1836 -- G------------G
1837 -- Reset the start date also to outside the inactive period.
1838 -- I------------------------------------I
1839 -- G----G
1840 l_temp_periods(l_index).start_date := l_inactive_periods(i).end_date+1;
1841 END IF;
1842 -- Generated period's end_date falls in the inactive period.
1843 -- I------------------------------------I
1844 -- G----------G
1845 --
1846 ELSIF 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 -- Reset the end_date to outside the inactive period.
1850 -- I------------------------------------I
1851 -- G----G
1852 l_temp_periods(l_index).end_date := l_inactive_periods(i).start_date-1;
1853 END IF;
1854 END IF;
1855 l_index := l_temp_periods.NEXT(l_index);
1856 END LOOP GENERATED_PERIODS ;
1857 END IF;
1858 END LOOP INACTIVE_PERIODS ;
1859
1860 -- Bug 7454062
1861
1862
1863
1864
1865
1866
1867 -- add them to the pl/sql table
1868 -- we are now populating the periodtab pl/sql table
1869 -- and we are working out on the rows already in the table
1870 l_index := l_temp_periods.FIRST;
1871
1872 LOOP
1873 EXIT WHEN NOT l_temp_periods.EXISTS (l_index);
1874 if g_debug then
1875
1876 hr_utility.trace('###################');
1877 hr_utility.trace('#### NEW PERIOD #########');
1878 hr_utility.trace('###################');
1879 end if;
1880 l_period_start := l_temp_periods (l_index).start_date;
1881 l_period_end := l_temp_periods (l_index).end_date;
1882 -- help to know if during the process the period
1883 -- has been modified.
1884 l_touch_period_in_tc := FALSE;
1885 -- before to add the period we need to look if there is a tc period
1886 -- already into this table
1887 l_index_timecard_tab := l_timecard_tab.FIRST;
1888
1889 LOOP
1890 EXIT WHEN NOT l_timecard_tab.EXISTS (l_index_timecard_tab);
1891 l_tc_period_start_date := l_timecard_tab (l_index_timecard_tab).start_date;
1892 l_tc_period_end_date := l_timecard_tab (l_index_timecard_tab).end_date;
1893 if g_debug then
1894 hr_utility.trace('####NEW TIMECARD');
1895 hr_utility.trace('l_period_start :'||l_period_start);
1896 hr_utility.trace('l_period_end :'||l_period_end);
1897 hr_utility.trace('l_tc_period_start_date :'||to_char(l_tc_period_start_date,'DD-MON-YYYY'));
1898 hr_utility.trace('l_tc_period_end_date :'||to_char(l_tc_period_end_date,'DD-MON-YYYY'));
1899 hr_utility.trace('###################');
1900 end if;
1901 IF TRUNC (l_tc_period_start_date) > TRUNC (l_period_start)
1902 AND TRUNC (l_tc_period_end_date) < TRUNC (l_period_end) THEN
1903 -- the timecard is in the middle of a period
1904 -- we are splitting the period in 2 + the timecard which
1905 -- is already there.
1906 -- before the timecard
1907 if g_debug then
1908 hr_utility.trace('case 1 :');
1909 end if;
1910 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
1911 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_tc_period_start_date - 1;
1912 if g_debug then
1913 hr_utility.trace('l_tab_period_start_date :'||to_char(l_period_start,'DD-MON-YYYY'));
1914 hr_utility.trace('l_tab_period_end_date :'||to_char(l_tc_period_start_date - 1,'DD-MON-YYYY'));
1915 end if;
1916 -- after the timecard
1917 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).start_date :=
1918 l_tc_period_end_date + 1;
1919 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).end_date := l_period_end;
1920 -- now we are overritting the start period
1921 l_period_start := l_tc_period_end_date + 1;
1922 if g_debug then
1923 hr_utility.trace('l_tab_period_start_date :'||to_char(l_tc_period_end_date + 1,'DD-MON-YYYY'));
1924 hr_utility.trace('l_tab_period_end_date :'||to_char(l_period_end,'DD-MON-YYYY'));
1925 end if;
1926 l_touch_period_in_tc := TRUE;
1927 ELSIF TRUNC (l_tc_period_start_date) < TRUNC (l_period_start)
1928 AND TRUNC (l_tc_period_end_date) > TRUNC (l_period_end) THEN
1929 -- the timecard is outside of the period so we have nothing to
1930 -- do
1931 l_touch_period_in_tc := TRUE;
1932 if g_debug then
1933 hr_utility.trace('case 2 :');
1934 end if;
1935 ELSIF TRUNC (l_tc_period_start_date) > TRUNC (l_period_start)
1936 AND TRUNC (l_tc_period_start_date) <= TRUNC (l_period_end)
1937 AND TRUNC (l_tc_period_end_date) >= TRUNC (l_period_end) THEN
1938 if g_debug then
1939 hr_utility.trace('case 3 :');
1940 end if;
1941 -- we are splitting the period in 2
1942 -- we are adding the period just before the timecard
1943 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
1944 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_tc_period_start_date - 1;
1945 -- in this case we can exit of the loop;
1946 l_index_timecard_tab := l_timecard_tab.LAST;
1947 l_touch_period_in_tc := TRUE;
1948 if g_debug then
1949 hr_utility.trace('l_tab_period_start_date :'||to_char(l_period_start,'DD-MON-YYYY'));
1950 hr_utility.trace('l_tab_period_end_date :'||to_char(l_tc_period_start_date - 1,'DD-MON-YYYY'));
1951 end if;
1952 ELSIF TRUNC (l_tc_period_start_date) <= TRUNC (l_period_start)
1953 AND TRUNC (l_tc_period_end_date) >= TRUNC (l_period_start)
1954 AND TRUNC (l_tc_period_end_date) < TRUNC (l_period_end) THEN
1955 if g_debug then
1956 hr_utility.trace('case 4 :');
1957 end if;
1958 -- we are splitting the period in 2
1959 -- we are adding the period just before the timecard
1960 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).start_date :=
1961 l_tc_period_end_date + 1;
1962 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_end_date + 1, 'J'))).end_date := l_period_end;
1963 if g_debug then
1964 hr_utility.trace('l_tab_period_start_date :'||to_char(l_tc_period_end_date + 1,'DD-MON-YYYY'));
1965 hr_utility.trace('l_tab_period_end_date :'||to_char(l_period_end,'DD-MON-YYYY'));
1966 end if;
1967 -- now we are overritting the end/start period
1968 l_period_start := l_tc_period_end_date + 1;
1969 l_touch_period_in_tc := TRUE;
1970 ELSIF ( TRUNC (l_tc_period_start_date) = TRUNC (l_period_start)
1971 AND TRUNC (l_tc_period_end_date) >= TRUNC (l_period_end)
1972 )
1973 OR ( TRUNC (l_tc_period_start_date) <= TRUNC (l_period_start)
1974 AND TRUNC (l_tc_period_end_date) = TRUNC (l_period_end)
1975 ) THEN
1976 if g_debug then
1977 hr_utility.trace('case 5 :');
1978 end if;
1979 -- we are splitting the period in 2
1980 -- we are adding the period just before the timecard
1981 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).start_date :=
1982 l_tc_period_start_date;
1983 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).end_date := l_tc_period_end_date;
1984 -- now we are overritting the end/start period
1985 l_period_start := l_tc_period_end_date + 1;
1986 l_touch_period_in_tc := TRUE;
1987 END IF;
1988
1989 -- in case of override
1990 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).start_date := l_tc_period_start_date;
1991 p_periodtab (TO_NUMBER (TO_CHAR (l_tc_period_start_date, 'J'))).end_date := l_tc_period_end_date;
1992 l_index_timecard_tab := l_timecard_tab.NEXT (l_index_timecard_tab);
1993 END LOOP;
1994 /*ADVICE(1531): Nested LOOPs should all be labeled [406] */
1995
1996
1997 IF NOT (l_touch_period_in_tc) THEN
1998 if g_debug then
1999 hr_utility.trace('case 6 :');
2000 end if;
2001 -- we are adding the period
2002 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).start_date := l_period_start;
2003 p_periodtab (TO_NUMBER (TO_CHAR (l_period_start, 'J'))).end_date := l_period_end;
2004 END IF;
2005
2006 if g_debug then
2007 hr_utility.trace('count'||p_periodtab.count);
2008 end if;
2009 l_index := l_temp_periods.NEXT (l_index);
2010 END LOOP;
2011
2012 /* changes done by senthil for emp terminate enhancement*/
2013 IF p_periodtab.COUNT > 0 THEN
2014 l_last_rowindex := p_periodtab.LAST;
2015 l_start_date := p_periodtab (l_last_rowindex).start_date;
2016 l_end_date := p_periodtab (l_last_rowindex).end_date;
2017 IF l_end_date > l_person_effective_end_date THEN
2018 open c_term_timecards_exists(p_resource_id,l_person_effective_end_date);
2019 fetch c_term_timecards_exists into l_term_tc_exists;
2020 close c_term_timecards_exists;
2021 if l_term_tc_exists = 0 then
2022 p_periodtab (l_last_rowindex).end_date := l_person_effective_end_date;
2023 end if;
2024 END IF;
2025 END IF;
2026 /* end of changes made by senthil */
2027
2028 IF p_periodtab.COUNT > 0 THEN
2029 l_first_rowindex := p_periodtab.FIRST;
2030 l_start_date := p_periodtab (l_first_rowindex).start_date;
2031 l_end_date := p_periodtab (l_first_rowindex).end_date;
2032
2033 IF l_person_effective_start_date < l_start_date THEN
2034 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).start_date :=
2035 l_person_effective_start_date;
2036 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).end_date := l_start_date - 1;
2037 ELSIF l_person_effective_start_date > l_start_date AND l_person_effective_start_date > l_end_date THEN --2975015
2038 p_periodtab.DELETE (l_first_rowindex);
2039 ELSIF l_person_effective_start_date > l_start_date THEN
2040 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).start_date :=
2041 l_person_effective_start_date;
2042 p_periodtab (TO_NUMBER (TO_CHAR (l_person_effective_start_date, 'J'))).end_date :=
2043 p_periodtab (l_first_rowindex).end_date;
2044 p_periodtab.DELETE (l_first_rowindex);
2045 END IF;
2046 END IF;
2047 END get_resource_time_periods;
2048
2049 PROCEDURE tc_edit_allowed (
2050 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
2051 /*ADVICE(1568): Mode of parameter is not specified with IN parameter [521] */
2052 ,
2053 p_timecard_ovn
2054 /*ADVICE(1571): Unreferenced parameter [552] */
2055 hxc_time_building_blocks.object_version_number%TYPE
2056 /*ADVICE(1573): Mode of parameter is not specified with IN parameter [521] */
2057 ,
2058 p_timecard_status VARCHAR2
2059 /*ADVICE(1576): Mode of parameter is not specified with IN parameter [521] */
2060 ,
2061 p_edit_allowed_preference hxc_pref_hierarchies.attribute1%TYPE
2062 /*ADVICE(1579): Mode of parameter is not specified with IN parameter [521] */
2063 ,
2064 p_edit_allowed IN OUT NOCOPY VARCHAR2
2065 ) IS
2066 CURSOR csr_chk_transfer IS
2067 SELECT 1
2068 FROM DUAL
2069 WHERE EXISTS ( SELECT 1
2070 FROM hxc_transactions t, hxc_transaction_details td
2071 WHERE td.time_building_block_id = p_timecard_id
2072 /*ADVICE(1589): Cursor references an external variable (use a parameter) [209] */
2073
2074 AND t.transaction_id = td.transaction_id
2075 AND t.TYPE = 'RETRIEVAL'
2076 AND t.status = 'SUCCESS');
2077
2078 l_proc
2079 /*ADVICE(1596): Unreferenced variable [553] */
2080 VARCHAR2 (72);
2081 l_tc_status hxc_time_building_blocks.approval_status%TYPE;
2082 l_dummy NUMBER (1);
2083 BEGIN
2084 g_debug :=hr_utility.debug_enabled;
2085 if g_debug then
2086 l_proc := 'hxc_timekeeper_utilities'
2087 || 'tc_edit_allowed';
2088 hr_utility.set_location('Entering '||l_proc, 10);
2089 end if;
2090 --l_tc_status := hxc_timecard_search_pkg.get_timecard_status_code(p_timecard_id,p_Timecard_Ovn);
2091 l_tc_status := p_timecard_status;
2092
2093
2094 if g_debug then
2095 hr_utility.set_location('Processing '||l_proc, 20);
2096 end if;
2097 IF (p_edit_allowed_preference = 'NEW_WORKING_REJECTED') THEN
2098 if g_debug then
2099 hr_utility.set_location('Processing '||l_proc, 30);
2100 end if;
2101 IF ((l_tc_status = 'REJECTED') OR (l_tc_status = 'WORKING')) THEN
2102 p_edit_allowed := 'TRUE';
2103 ELSE
2104 p_edit_allowed := 'FALSE';
2105 END IF;
2106 ELSIF (p_edit_allowed_preference = 'SUBMITTED') THEN
2107 if g_debug then
2108 hr_utility.set_location('Processing '||l_proc, 40);
2109 end if;
2110 IF ((l_tc_status = 'REJECTED') OR (l_tc_status = 'WORKING') OR (l_tc_status = 'SUBMITTED')) THEN
2111 p_edit_allowed := 'TRUE';
2112 ELSE
2113 p_edit_allowed := 'FALSE';
2114 END IF;
2115 ELSIF (p_edit_allowed_preference = 'APPROVALS_INITIATED') THEN
2116 if g_debug then
2117 hr_utility.set_location('Processing '||l_proc, 50);
2118 end if;
2119 -- all we need to do here is check that this timecard
2120 -- has not been transferred successfully to any recipient
2121 -- applications
2122 OPEN csr_chk_transfer;
2123 FETCH csr_chk_transfer INTO l_dummy;
2124
2125 IF csr_chk_transfer%FOUND THEN
2126 p_edit_allowed := 'FALSE';
2127 ELSE
2128 p_edit_allowed := 'TRUE';
2129 END IF;
2130 ELSIF (p_edit_allowed_preference = 'RETRO') THEN
2131 if g_debug then
2132 hr_utility.set_location('Processing '||l_proc, 60);
2133 end if;
2134 IF ( (l_tc_status = 'REJECTED')
2135 OR (l_tc_status = 'WORKING')
2136 OR (l_tc_status = 'SUBMITTED')
2137 OR (l_tc_status = 'APPROVED')
2138 OR (l_tc_status = 'ERROR')
2139 ) THEN
2140 p_edit_allowed := 'TRUE';
2141 ELSE
2142 p_edit_allowed := 'FALSE';
2143 END IF;
2144 ELSE
2145 if g_debug then
2146 hr_utility.set_location('Processing '||l_proc, 70);
2147 end if;
2148 p_edit_allowed := 'FALSE';
2149 END IF;
2150
2151
2152 -- if the status is ERROR, we don't need to look at
2153 -- the pref -> JUST RETURN TRUE;
2154 IF (l_tc_status = 'ERROR') THEN
2155 p_edit_allowed := 'TRUE';
2156 END IF;
2157
2158 if g_debug then
2159 hr_utility.set_location('Leaving '||l_proc, 80);
2160 end if;
2161 END tc_edit_allowed;
2162
2163 PROCEDURE convert_type_to_message_table (
2164 p_old_messages IN hxc_message_table_type,
2165 p_messages OUT NOCOPY hxc_self_service_time_deposit.message_table
2166 ) IS
2167 l_index NUMBER
2168 /*ADVICE(1668): NUMBER has no precision [315] */
2169 ;
2170 l_new_index NUMBER
2171 /*ADVICE(1671): NUMBER has no precision [315] */
2172 ;
2173 BEGIN
2174 l_new_index := 1;
2175 l_index := p_old_messages.FIRST;
2176
2177 LOOP
2178 EXIT WHEN NOT p_old_messages.EXISTS (l_index);
2179 p_messages (l_new_index).message_name := p_old_messages (l_index).message_name;
2180 p_messages (l_new_index).message_level := p_old_messages (l_index).message_level;
2181 p_messages (l_new_index).message_field := p_old_messages (l_index).message_field;
2182 p_messages (l_new_index).message_tokens := p_old_messages (l_index).message_tokens;
2183 p_messages (l_new_index).application_short_name := p_old_messages (l_index).application_short_name;
2184 p_messages (l_new_index).time_building_block_id := p_old_messages (l_index).time_building_block_id;
2185 p_messages (l_new_index).time_building_block_ovn := p_old_messages (l_index).time_building_block_ovn;
2186 p_messages (l_new_index).time_attribute_id := p_old_messages (l_index).time_attribute_id;
2187 p_messages (l_new_index).time_attribute_ovn := p_old_messages (l_index).time_attribute_ovn;
2188 l_new_index := l_new_index + 1;
2189 l_index := p_old_messages.NEXT (l_index);
2190 END LOOP;
2191 END convert_type_to_message_table;
2192
2193 PROCEDURE manage_attributes (
2194 p_attribute_number IN NUMBER,
2195 p_insert_data_details IN hxc_timekeeper_process.t_time_info,
2196 p_old_value IN OUT NOCOPY VARCHAR2,
2197 p_new_value IN OUT NOCOPY VARCHAR2
2198 ) IS
2199 BEGIN
2200 IF p_attribute_number = 1 THEN
2201 p_new_value := p_insert_data_details.attr_id_1;
2202 p_old_value := p_insert_data_details.attr_oldid_1;
2203 ELSIF p_attribute_number = 2 THEN
2204 p_new_value := p_insert_data_details.attr_id_2;
2205 p_old_value := p_insert_data_details.attr_oldid_2;
2206 ELSIF p_attribute_number = 3 THEN
2207 p_new_value := p_insert_data_details.attr_id_3;
2208 p_old_value := p_insert_data_details.attr_oldid_3;
2209 ELSIF p_attribute_number = 4 THEN
2210 p_new_value := p_insert_data_details.attr_id_4;
2211 p_old_value := p_insert_data_details.attr_oldid_4;
2212 ELSIF p_attribute_number = 5 THEN
2213 p_new_value := p_insert_data_details.attr_id_5;
2214 p_old_value := p_insert_data_details.attr_oldid_5;
2215 ELSIF p_attribute_number = 6 THEN
2216 p_new_value := p_insert_data_details.attr_id_6;
2217 p_old_value := p_insert_data_details.attr_oldid_6;
2218 ELSIF p_attribute_number = 7 THEN
2219 p_new_value := p_insert_data_details.attr_id_7;
2220 p_old_value := p_insert_data_details.attr_oldid_7;
2221 ELSIF p_attribute_number = 8 THEN
2222 p_new_value := p_insert_data_details.attr_id_8;
2223 p_old_value := p_insert_data_details.attr_oldid_8;
2224 ELSIF p_attribute_number = 9 THEN
2225 p_new_value := p_insert_data_details.attr_id_9;
2226 p_old_value := p_insert_data_details.attr_oldid_9;
2227 ELSIF p_attribute_number = 10 THEN
2228 p_new_value := p_insert_data_details.attr_id_10;
2229 p_old_value := p_insert_data_details.attr_oldid_10;
2230 ELSIF p_attribute_number = 11 THEN
2231 p_new_value := p_insert_data_details.attr_id_11;
2232 p_old_value := p_insert_data_details.attr_oldid_11;
2233 ELSIF p_attribute_number = 12 THEN
2234 p_new_value := p_insert_data_details.attr_id_12;
2235 p_old_value := p_insert_data_details.attr_oldid_12;
2236 ELSIF p_attribute_number = 13 THEN
2237 p_new_value := p_insert_data_details.attr_id_13;
2238 p_old_value := p_insert_data_details.attr_oldid_13;
2239 ELSIF p_attribute_number = 14 THEN
2240 p_new_value := p_insert_data_details.attr_id_14;
2241 p_old_value := p_insert_data_details.attr_oldid_14;
2242 ELSIF p_attribute_number = 15 THEN
2243 p_new_value := p_insert_data_details.attr_id_15;
2244 p_old_value := p_insert_data_details.attr_oldid_15;
2245 ELSIF p_attribute_number = 16 THEN
2246 p_new_value := p_insert_data_details.attr_id_16;
2247 p_old_value := p_insert_data_details.attr_oldid_16;
2248 ELSIF p_attribute_number = 17 THEN
2249 p_new_value := p_insert_data_details.attr_id_17;
2250 p_old_value := p_insert_data_details.attr_oldid_17;
2251 ELSIF p_attribute_number = 18 THEN
2252 p_new_value := p_insert_data_details.attr_id_18;
2253 p_old_value := p_insert_data_details.attr_oldid_18;
2254 ELSIF p_attribute_number = 19 THEN
2255 p_new_value := p_insert_data_details.attr_id_19;
2256 p_old_value := p_insert_data_details.attr_oldid_19;
2257 ELSIF p_attribute_number = 20 THEN
2258 p_new_value := p_insert_data_details.attr_id_20;
2259 p_old_value := p_insert_data_details.attr_oldid_20;
2260 END IF;
2261 END;
2262
2263 PROCEDURE manage_timeinfo (
2264 p_day_counter IN NUMBER,
2265 p_insert_detail IN hxc_timekeeper_process.t_time_info,
2266 p_measure IN OUT NOCOPY NUMBER,
2267 p_detail_id IN OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE,
2268 p_detail_ovn IN OUT NOCOPY NUMBER,
2269 p_detail_time_in IN OUT NOCOPY DATE,
2270 p_detail_time_out IN OUT NOCOPY DATE
2271 ) IS
2272 l_attribute_found
2273 /*ADVICE(1773): Unreferenced variable [553] */
2274 BOOLEAN;
2275 BEGIN
2276 IF p_day_counter = 0 THEN
2277 p_measure := p_insert_detail.day_1;
2278 p_detail_id := p_insert_detail.detail_id_1;
2279 p_detail_ovn := p_insert_detail.detail_ovn_1;
2280 p_detail_time_in := p_insert_detail.time_in_1;
2281 p_detail_time_out := p_insert_detail.time_out_1;
2282 ELSIF p_day_counter = 1 THEN
2283 p_measure := p_insert_detail.day_2;
2284 p_detail_id := p_insert_detail.detail_id_2;
2285 p_detail_ovn := p_insert_detail.detail_ovn_2;
2286 p_detail_time_in := p_insert_detail.time_in_2;
2287 p_detail_time_out := p_insert_detail.time_out_2;
2288 ELSIF p_day_counter = 2 THEN
2289 p_measure := p_insert_detail.day_3;
2290 p_detail_id := p_insert_detail.detail_id_3;
2291 p_detail_ovn := p_insert_detail.detail_ovn_3;
2292 p_detail_time_in := p_insert_detail.time_in_3;
2293 p_detail_time_out := p_insert_detail.time_out_3;
2294 ELSIF p_day_counter = 3 THEN
2295 p_measure := p_insert_detail.day_4;
2296 p_detail_id := p_insert_detail.detail_id_4;
2297 p_detail_ovn := p_insert_detail.detail_ovn_4;
2298 p_detail_time_in := p_insert_detail.time_in_4;
2299 p_detail_time_out := p_insert_detail.time_out_4;
2300 ELSIF p_day_counter = 4 THEN
2301 p_measure := p_insert_detail.day_5;
2302 p_detail_id := p_insert_detail.detail_id_5;
2303 p_detail_ovn := p_insert_detail.detail_ovn_5;
2304 p_detail_time_in := p_insert_detail.time_in_5;
2305 p_detail_time_out := p_insert_detail.time_out_5;
2306 ELSIF p_day_counter = 5 THEN
2307 p_measure := p_insert_detail.day_6;
2308 p_detail_id := p_insert_detail.detail_id_6;
2309 p_detail_ovn := p_insert_detail.detail_ovn_6;
2310 p_detail_time_in := p_insert_detail.time_in_6;
2311 p_detail_time_out := p_insert_detail.time_out_6;
2312 ELSIF p_day_counter = 6 THEN
2313 p_measure := p_insert_detail.day_7;
2314 p_detail_id := p_insert_detail.detail_id_7;
2315 p_detail_ovn := p_insert_detail.detail_ovn_7;
2316 p_detail_time_in := p_insert_detail.time_in_7;
2317 p_detail_time_out := p_insert_detail.time_out_7;
2318 ELSIF p_day_counter = 7 THEN
2319 p_measure := p_insert_detail.day_8;
2320 p_detail_id := p_insert_detail.detail_id_8;
2321 p_detail_ovn := p_insert_detail.detail_ovn_8;
2322 p_detail_time_in := p_insert_detail.time_in_8;
2323 p_detail_time_out := p_insert_detail.time_out_8;
2324 ELSIF p_day_counter = 8 THEN
2325 p_measure := p_insert_detail.day_9;
2326 p_detail_id := p_insert_detail.detail_id_9;
2327 p_detail_ovn := p_insert_detail.detail_ovn_9;
2328 p_detail_time_in := p_insert_detail.time_in_9;
2329 p_detail_time_out := p_insert_detail.time_out_9;
2330 ELSIF p_day_counter = 9 THEN
2331 p_measure := p_insert_detail.day_10;
2332 p_detail_id := p_insert_detail.detail_id_10;
2333 p_detail_ovn := p_insert_detail.detail_ovn_10;
2334 p_detail_time_in := p_insert_detail.time_in_10;
2335 p_detail_time_out := p_insert_detail.time_out_10;
2336 ELSIF p_day_counter = 10 THEN
2337 p_measure := p_insert_detail.day_11;
2338 p_detail_id := p_insert_detail.detail_id_11;
2339 p_detail_ovn := p_insert_detail.detail_ovn_11;
2340 p_detail_time_in := p_insert_detail.time_in_11;
2341 p_detail_time_out := p_insert_detail.time_out_11;
2342 ELSIF p_day_counter = 11 THEN
2343 p_measure := p_insert_detail.day_12;
2344 p_detail_id := p_insert_detail.detail_id_12;
2345 p_detail_ovn := p_insert_detail.detail_ovn_12;
2346 p_detail_time_in := p_insert_detail.time_in_12;
2347 p_detail_time_out := p_insert_detail.time_out_12;
2348 ELSIF p_day_counter = 12 THEN
2349 p_measure := p_insert_detail.day_13;
2350 p_detail_id := p_insert_detail.detail_id_13;
2351 p_detail_ovn := p_insert_detail.detail_ovn_13;
2352 p_detail_time_in := p_insert_detail.time_in_13;
2353 p_detail_time_out := p_insert_detail.time_out_13;
2354 ELSIF p_day_counter = 13 THEN
2355 p_measure := p_insert_detail.day_14;
2356 p_detail_id := p_insert_detail.detail_id_14;
2357 p_detail_ovn := p_insert_detail.detail_ovn_14;
2358 p_detail_time_in := p_insert_detail.time_in_14;
2359 p_detail_time_out := p_insert_detail.time_out_14;
2360 ELSIF p_day_counter = 14 THEN
2361 p_measure := p_insert_detail.day_15;
2362 p_detail_id := p_insert_detail.detail_id_15;
2363 p_detail_ovn := p_insert_detail.detail_ovn_15;
2364 p_detail_time_in := p_insert_detail.time_in_15;
2365 p_detail_time_out := p_insert_detail.time_out_15;
2366 ELSIF p_day_counter = 15 THEN
2367 p_measure := p_insert_detail.day_16;
2368 p_detail_id := p_insert_detail.detail_id_16;
2369 p_detail_ovn := p_insert_detail.detail_ovn_16;
2370 p_detail_time_in := p_insert_detail.time_in_16;
2371 p_detail_time_out := p_insert_detail.time_out_16;
2372 ELSIF p_day_counter = 16 THEN
2373 p_measure := p_insert_detail.day_17;
2374 p_detail_id := p_insert_detail.detail_id_17;
2375 p_detail_ovn := p_insert_detail.detail_ovn_17;
2376 p_detail_time_in := p_insert_detail.time_in_17;
2377 p_detail_time_out := p_insert_detail.time_out_17;
2378 ELSIF p_day_counter = 17 THEN
2379 p_measure := p_insert_detail.day_18;
2380 p_detail_id := p_insert_detail.detail_id_18;
2381 p_detail_ovn := p_insert_detail.detail_ovn_18;
2382 p_detail_time_in := p_insert_detail.time_in_18;
2383 p_detail_time_out := p_insert_detail.time_out_18;
2384 ELSIF p_day_counter = 18 THEN
2385 p_measure := p_insert_detail.day_19;
2386 p_detail_id := p_insert_detail.detail_id_19;
2387 p_detail_ovn := p_insert_detail.detail_ovn_19;
2388 p_detail_time_in := p_insert_detail.time_in_19;
2389 p_detail_time_out := p_insert_detail.time_out_19;
2390 ELSIF p_day_counter = 19 THEN
2391 p_measure := p_insert_detail.day_20;
2392 p_detail_id := p_insert_detail.detail_id_20;
2393 p_detail_ovn := p_insert_detail.detail_ovn_20;
2394 p_detail_time_in := p_insert_detail.time_in_20;
2395 p_detail_time_out := p_insert_detail.time_out_20;
2396 ELSIF p_day_counter = 20 THEN
2397 p_measure := p_insert_detail.day_21;
2398 p_detail_id := p_insert_detail.detail_id_21;
2399 p_detail_ovn := p_insert_detail.detail_ovn_21;
2400 p_detail_time_in := p_insert_detail.time_in_21;
2401 p_detail_time_out := p_insert_detail.time_out_21;
2402 ELSIF p_day_counter = 21 THEN
2403 p_measure := p_insert_detail.day_22;
2404 p_detail_id := p_insert_detail.detail_id_22;
2405 p_detail_ovn := p_insert_detail.detail_ovn_22;
2406 p_detail_time_in := p_insert_detail.time_in_22;
2407 p_detail_time_out := p_insert_detail.time_out_22;
2408 ELSIF p_day_counter = 22 THEN
2409 p_measure := p_insert_detail.day_23;
2410 p_detail_id := p_insert_detail.detail_id_23;
2411 p_detail_ovn := p_insert_detail.detail_ovn_23;
2412 p_detail_time_in := p_insert_detail.time_in_23;
2413 p_detail_time_out := p_insert_detail.time_out_23;
2414 ELSIF p_day_counter = 23 THEN
2415 p_measure := p_insert_detail.day_24;
2416 p_detail_id := p_insert_detail.detail_id_24;
2417 p_detail_ovn := p_insert_detail.detail_ovn_24;
2418 p_detail_time_in := p_insert_detail.time_in_24;
2419 p_detail_time_out := p_insert_detail.time_out_24;
2420 ELSIF p_day_counter = 24 THEN
2421 p_measure := p_insert_detail.day_25;
2422 p_detail_id := p_insert_detail.detail_id_25;
2423 p_detail_ovn := p_insert_detail.detail_ovn_25;
2424 p_detail_time_in := p_insert_detail.time_in_25;
2425 p_detail_time_out := p_insert_detail.time_out_25;
2426 ELSIF p_day_counter = 25 THEN
2427 p_measure := p_insert_detail.day_26;
2428 p_detail_id := p_insert_detail.detail_id_26;
2429 p_detail_ovn := p_insert_detail.detail_ovn_26;
2430 p_detail_time_in := p_insert_detail.time_in_26;
2431 p_detail_time_out := p_insert_detail.time_out_26;
2432 ELSIF p_day_counter = 26 THEN
2433 p_measure := p_insert_detail.day_27;
2434 p_detail_id := p_insert_detail.detail_id_27;
2435 p_detail_ovn := p_insert_detail.detail_ovn_27;
2436 p_detail_time_in := p_insert_detail.time_in_27;
2437 p_detail_time_out := p_insert_detail.time_out_27;
2438 ELSIF p_day_counter = 27 THEN
2439 p_measure := p_insert_detail.day_28;
2440 p_detail_id := p_insert_detail.detail_id_28;
2441 p_detail_ovn := p_insert_detail.detail_ovn_28;
2442 p_detail_time_in := p_insert_detail.time_in_28;
2443 p_detail_time_out := p_insert_detail.time_out_28;
2444 ELSIF p_day_counter = 28 THEN
2445 p_measure := p_insert_detail.day_29;
2446 p_detail_id := p_insert_detail.detail_id_29;
2447 p_detail_ovn := p_insert_detail.detail_ovn_29;
2448 p_detail_time_in := p_insert_detail.time_in_29;
2449 p_detail_time_out := p_insert_detail.time_out_29;
2450 ELSIF p_day_counter = 29 THEN
2451 p_measure := p_insert_detail.day_30;
2452 p_detail_id := p_insert_detail.detail_id_30;
2453 p_detail_ovn := p_insert_detail.detail_ovn_30;
2454 p_detail_time_in := p_insert_detail.time_in_30;
2455 p_detail_time_out := p_insert_detail.time_out_30;
2456 ELSIF p_day_counter = 30 THEN
2457 p_measure := p_insert_detail.day_31;
2458 p_detail_id := p_insert_detail.detail_id_31;
2459 p_detail_ovn := p_insert_detail.detail_ovn_31;
2460 p_detail_time_in := p_insert_detail.time_in_31;
2461 p_detail_time_out := p_insert_detail.time_out_31;
2462 END IF;
2463 END;
2464
2465 PROCEDURE manage_detaildffinfo (
2466 p_detail_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
2467 p_detail_ovn IN NUMBER,
2468 p_det_details IN OUT NOCOPY hxc_timekeeper_process.g_detail_data%TYPE,
2469 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
2470 p_attribute_category IN VARCHAR2,
2471 p_tbb_id_reference_table IN OUT NOCOPY hxc_alias_utility.t_tbb_id_reference,
2472 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info,
2473 p_timecard_index_info
2474 /*ADVICE(1974): Unreferenced parameter [552] */
2475 IN OUT NOCOPY hxc_timekeeper_process.t_timecard_index_info
2476 ) IS
2477 l_bldtyp_id NUMBER
2478 /*ADVICE(1978): NUMBER has no precision [315] */
2479 ;
2480 l_attribute_index NUMBER
2481 /*ADVICE(1981): NUMBER has no precision [315] */
2482 ;
2483 l_attribute_found BOOLEAN;
2484 BEGIN
2485 IF p_detail_id IS NOT NULL AND p_det_details.EXISTS (p_detail_id) THEN
2486 IF p_attribute_category IS NOT NULL THEN
2487 IF NVL (p_attribute_category, '-999') <> NVL (p_det_details (p_detail_id).dff_catg, '-999') THEN
2488 p_det_details (p_detail_id).dff_catg := p_attribute_category;
2489 p_det_details (p_detail_id).dff_attr1 := NULL;
2490 p_det_details (p_detail_id).dff_attr2 := NULL;
2491 p_det_details (p_detail_id).dff_attr3 := NULL;
2492 p_det_details (p_detail_id).dff_attr4 := NULL;
2493 p_det_details (p_detail_id).dff_attr5 := NULL;
2494 p_det_details (p_detail_id).dff_attr6 := NULL;
2495 p_det_details (p_detail_id).dff_attr7 := NULL;
2496 p_det_details (p_detail_id).dff_attr8 := NULL;
2497 p_det_details (p_detail_id).dff_attr9 := NULL;
2498 p_det_details (p_detail_id).dff_attr10 := NULL;
2499 p_det_details (p_detail_id).dff_attr11 := NULL;
2500 p_det_details (p_detail_id).dff_attr12 := NULL;
2501 p_det_details (p_detail_id).dff_attr13 := NULL;
2502 p_det_details (p_detail_id).dff_attr14 := NULL;
2503 p_det_details (p_detail_id).dff_attr15 := NULL;
2504 p_det_details (p_detail_id).dff_attr16 := NULL;
2505 p_det_details (p_detail_id).dff_attr17 := NULL;
2506 p_det_details (p_detail_id).dff_attr18 := NULL;
2507 p_det_details (p_detail_id).dff_attr19 := NULL;
2508 p_det_details (p_detail_id).dff_attr20 := NULL;
2509 END IF;
2510 ELSE
2511 p_det_details (p_detail_id).dff_catg := p_det_details (p_detail_id).dff_catg;
2512 p_det_details (p_detail_id).dff_attr1 := NULL;
2513 p_det_details (p_detail_id).dff_attr2 := NULL;
2514 p_det_details (p_detail_id).dff_attr3 := NULL;
2515 p_det_details (p_detail_id).dff_attr4 := NULL;
2516 p_det_details (p_detail_id).dff_attr5 := NULL;
2517 p_det_details (p_detail_id).dff_attr6 := NULL;
2518 p_det_details (p_detail_id).dff_attr7 := NULL;
2519 p_det_details (p_detail_id).dff_attr8 := NULL;
2520 p_det_details (p_detail_id).dff_attr9 := NULL;
2521 p_det_details (p_detail_id).dff_attr10 := NULL;
2522 p_det_details (p_detail_id).dff_attr11 := NULL;
2523 p_det_details (p_detail_id).dff_attr12 := NULL;
2524 p_det_details (p_detail_id).dff_attr13 := NULL;
2525 p_det_details (p_detail_id).dff_attr14 := NULL;
2526 p_det_details (p_detail_id).dff_attr15 := NULL;
2527 p_det_details (p_detail_id).dff_attr16 := NULL;
2528 p_det_details (p_detail_id).dff_attr17 := NULL;
2529 p_det_details (p_detail_id).dff_attr18 := NULL;
2530 p_det_details (p_detail_id).dff_attr19 := NULL;
2531 p_det_details (p_detail_id).dff_attr20 := NULL;
2532 END IF;
2533
2534 IF p_det_details.EXISTS (p_detail_id)
2535 AND ( NVL (p_det_details (p_detail_id).dff_catg, '-999') <>
2536 NVL (p_det_details (p_detail_id).dff_oldcatg, '-999')
2537 OR NVL (p_det_details (p_detail_id).dff_attr1, '-999') <>
2538 NVL (p_det_details (p_detail_id).dff_oldattr1, '-999')
2539 OR NVL (p_det_details (p_detail_id).dff_attr2, '-999') <>
2540 NVL (p_det_details (p_detail_id).dff_oldattr2, '-999')
2541 OR NVL (p_det_details (p_detail_id).dff_attr3, '-999') <>
2542 NVL (p_det_details (p_detail_id).dff_oldattr3, '-999')
2543 OR NVL (p_det_details (p_detail_id).dff_attr4, '-999') <>
2544 NVL (p_det_details (p_detail_id).dff_oldattr4, '-999')
2545 OR NVL (p_det_details (p_detail_id).dff_attr5, '-999') <>
2546 NVL (p_det_details (p_detail_id).dff_oldattr5, '-999')
2547 OR NVL (p_det_details (p_detail_id).dff_attr6, '-999') <>
2548 NVL (p_det_details (p_detail_id).dff_oldattr6, '-999')
2549 OR NVL (p_det_details (p_detail_id).dff_attr7, '-999') <>
2550 NVL (p_det_details (p_detail_id).dff_oldattr7, '-999')
2551 OR NVL (p_det_details (p_detail_id).dff_attr8, '-999') <>
2552 NVL (p_det_details (p_detail_id).dff_oldattr8, '-999')
2553 OR NVL (p_det_details (p_detail_id).dff_attr9, '-999') <>
2554 NVL (p_det_details (p_detail_id).dff_oldattr9, '-999')
2555 OR NVL (p_det_details (p_detail_id).dff_attr10, '-999') <>
2556 NVL (p_det_details (p_detail_id).dff_oldattr10, '-999')
2557 OR NVL (p_det_details (p_detail_id).dff_attr11, '-999') <>
2558 NVL (p_det_details (p_detail_id).dff_oldattr11, '-999')
2559 OR NVL (p_det_details (p_detail_id).dff_attr12, '-999') <>
2560 NVL (p_det_details (p_detail_id).dff_oldattr12, '-999')
2561 OR NVL (p_det_details (p_detail_id).dff_attr13, '-999') <>
2562 NVL (p_det_details (p_detail_id).dff_oldattr13, '-999')
2563 OR NVL (p_det_details (p_detail_id).dff_attr14, '-999') <>
2564 NVL (p_det_details (p_detail_id).dff_oldattr14, '-999')
2565 OR NVL (p_det_details (p_detail_id).dff_attr15, '-999') <>
2566 NVL (p_det_details (p_detail_id).dff_oldattr15, '-999')
2567 OR NVL (p_det_details (p_detail_id).dff_attr16, '-999') <>
2568 NVL (p_det_details (p_detail_id).dff_oldattr16, '-999')
2569 OR NVL (p_det_details (p_detail_id).dff_attr17, '-999') <>
2570 NVL (p_det_details (p_detail_id).dff_oldattr17, '-999')
2571 OR NVL (p_det_details (p_detail_id).dff_attr18, '-999') <>
2572 NVL (p_det_details (p_detail_id).dff_oldattr18, '-999')
2573 OR NVL (p_det_details (p_detail_id).dff_attr19, '-999') <>
2574 NVL (p_det_details (p_detail_id).dff_oldattr19, '-999')
2575 OR NVL (p_det_details (p_detail_id).dff_attr20, '-999') <>
2576 NVL (p_det_details (p_detail_id).dff_oldattr20, '-999')
2577 OR NVL (p_det_details (p_detail_id).dff_attr21, '-999') <>
2578 NVL (p_det_details (p_detail_id).dff_oldattr21, '-999')
2579 OR NVL (p_det_details (p_detail_id).dff_attr22, '-999') <>
2580 NVL (p_det_details (p_detail_id).dff_oldattr22, '-999')
2581 OR NVL (p_det_details (p_detail_id).dff_attr23, '-999') <>
2582 NVL (p_det_details (p_detail_id).dff_oldattr23, '-999')
2583 OR NVL (p_det_details (p_detail_id).dff_attr24, '-999') <>
2584 NVL (p_det_details (p_detail_id).dff_oldattr24, '-999')
2585 OR NVL (p_det_details (p_detail_id).dff_attr25, '-999') <>
2586 NVL (p_det_details (p_detail_id).dff_oldattr25, '-999')
2587 OR NVL (p_det_details (p_detail_id).dff_attr26, '-999') <>
2588 NVL (p_det_details (p_detail_id).dff_oldattr26, '-999')
2589 OR NVL (p_det_details (p_detail_id).dff_attr27, '-999') <>
2590 NVL (p_det_details (p_detail_id).dff_oldattr27, '-999')
2591 OR NVL (p_det_details (p_detail_id).dff_attr28, '-999') <>
2592 NVL (p_det_details (p_detail_id).dff_oldattr28, '-999')
2593 OR NVL (p_det_details (p_detail_id).dff_attr29, '-999') <>
2594 NVL (p_det_details (p_detail_id).dff_oldattr29, '-999')
2595 OR NVL (p_det_details (p_detail_id).dff_attr30, '-999') <>
2596 NVL (p_det_details (p_detail_id).dff_oldattr30, '-999')
2597 ) THEN
2598 BEGIN
2599 SELECT bld_blk_info_type_id
2600 INTO l_bldtyp_id
2601 FROM hxc_bld_blk_info_types
2602 WHERE bld_blk_info_type = 'Dummy Paexpitdff Context';
2603 EXCEPTION
2604 WHEN OTHERS THEN
2605 NULL;
2606 /*ADVICE(2106): Use of NULL statements [532] */
2607
2608 /*ADVICE(2108): Exception masked by a NULL statement [533] */
2609
2610 /*ADVICE(2110): A WHEN OTHERS clause is used in the exception section without any other specific handlers
2611 [201] */
2612
2613 END;
2614
2615 IF NVL (l_bldtyp_id, -999) <> -999 THEN
2616 l_attribute_found := FALSE;
2617 --l_attribute_index :=
2618 hxc_alias_utility.attribute_check (
2619 p_bld_blk_info_type_id => l_bldtyp_id,
2620 p_time_building_block_id => p_detail_id,
2621 p_attributes => p_attributes,
2622 p_tbb_id_reference_table => p_tbb_id_reference_table,
2623 p_attribute_found => l_attribute_found,
2624 p_attribute_index => l_attribute_index
2625 );
2626
2627 -- now we need to check if we need to create an attribute or do an update
2628 --IF l_attribute_index = -1 THEN
2629 IF NOT (l_attribute_found) THEN
2630 hxc_timekeeper_process.g_negative_index := hxc_timekeeper_process.g_negative_index - 1;
2631 l_attribute_index := hxc_timekeeper_process.g_negative_index;
2632 hxc_timekeeper_utilities.add_dff_attribute (
2633 p_attribute => p_attributes,
2634 p_attribute_id => l_attribute_index,
2635 p_tbb_id => p_detail_id,
2636 p_tbb_ovn => p_detail_ovn,
2637 p_blk_type => 'Dummy Paexpitdff Context',
2638 p_blk_id => l_bldtyp_id,
2639 p_att_category => p_det_details (p_detail_id).dff_catg,
2640 p_att_1 => p_det_details (p_detail_id).dff_attr1,
2641 p_att_2 => p_det_details (p_detail_id).dff_attr2,
2642 p_att_3 => p_det_details (p_detail_id).dff_attr3,
2643 p_att_4 => p_det_details (p_detail_id).dff_attr4,
2644 p_att_5 => p_det_details (p_detail_id).dff_attr5,
2645 p_att_6 => p_det_details (p_detail_id).dff_attr6,
2646 p_att_7 => p_det_details (p_detail_id).dff_attr7,
2647 p_att_8 => p_det_details (p_detail_id).dff_attr8,
2648 p_att_9 => p_det_details (p_detail_id).dff_attr9,
2649 p_att_10 => p_det_details (p_detail_id).dff_attr10,
2650 p_att_11 => p_det_details (p_detail_id).dff_attr11,
2651 p_att_12 => p_det_details (p_detail_id).dff_attr12,
2652 p_att_13 => p_det_details (p_detail_id).dff_attr13,
2653 p_att_14 => p_det_details (p_detail_id).dff_attr14,
2654 p_att_15 => p_det_details (p_detail_id).dff_attr15,
2655 p_att_16 => p_det_details (p_detail_id).dff_attr16,
2656 p_att_17 => p_det_details (p_detail_id).dff_attr17,
2657 p_att_18 => p_det_details (p_detail_id).dff_attr18,
2658 p_att_19 => p_det_details (p_detail_id).dff_attr19,
2659 p_att_20 => p_det_details (p_detail_id).dff_attr20,
2660 p_att_21 => p_det_details (p_detail_id).dff_attr21,
2661 p_att_22 => p_det_details (p_detail_id).dff_attr22,
2662 p_att_23 => p_det_details (p_detail_id).dff_attr23,
2663 p_att_24 => p_det_details (p_detail_id).dff_attr24,
2664 p_att_25 => p_det_details (p_detail_id).dff_attr25,
2665 p_att_26 => p_det_details (p_detail_id).dff_attr26,
2666 p_att_27 => p_det_details (p_detail_id).dff_attr27,
2667 p_att_28 => p_det_details (p_detail_id).dff_attr28,
2668 p_att_29 => p_det_details (p_detail_id).dff_attr29,
2669 p_att_30 => p_det_details (p_detail_id).dff_attr30,
2670 p_attribute_index_info => p_attribute_index_info
2671 );
2672
2673 -- add the new attribute in the ref table
2674 IF p_tbb_id_reference_table.EXISTS (p_detail_id) THEN
2675 p_tbb_id_reference_table (p_detail_id).attribute_index :=
2676 p_tbb_id_reference_table (p_detail_id).attribute_index || '|'
2677 || hxc_timekeeper_process.g_negative_index;
2678 ELSE
2679 p_tbb_id_reference_table (p_detail_id).attribute_index :=
2680 '|' || hxc_timekeeper_process.g_negative_index;
2681 END IF;
2682 ELSE
2683 l_attribute_index := p_attributes (l_attribute_index).time_attribute_id;
2684 hxc_timekeeper_utilities.add_dff_attribute (
2685 p_attribute => p_attributes,
2686 p_attribute_id => l_attribute_index,
2687 p_tbb_id => p_detail_id,
2688 p_tbb_ovn => p_detail_ovn,
2689 p_blk_type => 'Dummy Paexpitdff Context',
2690 p_blk_id => l_bldtyp_id,
2691 p_att_category => p_det_details (p_detail_id).dff_catg,
2692 p_att_1 => p_det_details (p_detail_id).dff_attr1,
2693 p_att_2 => p_det_details (p_detail_id).dff_attr2,
2694 p_att_3 => p_det_details (p_detail_id).dff_attr3,
2695 p_att_4 => p_det_details (p_detail_id).dff_attr4,
2696 p_att_5 => p_det_details (p_detail_id).dff_attr5,
2697 p_att_6 => p_det_details (p_detail_id).dff_attr6,
2698 p_att_7 => p_det_details (p_detail_id).dff_attr7,
2699 p_att_8 => p_det_details (p_detail_id).dff_attr8,
2700 p_att_9 => p_det_details (p_detail_id).dff_attr9,
2701 p_att_10 => p_det_details (p_detail_id).dff_attr10,
2702 p_att_11 => p_det_details (p_detail_id).dff_attr11,
2703 p_att_12 => p_det_details (p_detail_id).dff_attr12,
2704 p_att_13 => p_det_details (p_detail_id).dff_attr13,
2705 p_att_14 => p_det_details (p_detail_id).dff_attr14,
2706 p_att_15 => p_det_details (p_detail_id).dff_attr15,
2707 p_att_16 => p_det_details (p_detail_id).dff_attr16,
2708 p_att_17 => p_det_details (p_detail_id).dff_attr17,
2709 p_att_18 => p_det_details (p_detail_id).dff_attr18,
2710 p_att_19 => p_det_details (p_detail_id).dff_attr19,
2711 p_att_20 => p_det_details (p_detail_id).dff_attr20,
2712 p_att_21 => p_det_details (p_detail_id).dff_attr21,
2713 p_att_22 => p_det_details (p_detail_id).dff_attr22,
2714 p_att_23 => p_det_details (p_detail_id).dff_attr23,
2715 p_att_24 => p_det_details (p_detail_id).dff_attr24,
2716 p_att_25 => p_det_details (p_detail_id).dff_attr25,
2717 p_att_26 => p_det_details (p_detail_id).dff_attr26,
2718 p_att_27 => p_det_details (p_detail_id).dff_attr27,
2719 p_att_28 => p_det_details (p_detail_id).dff_attr28,
2720 p_att_29 => p_det_details (p_detail_id).dff_attr29,
2721 p_att_30 => p_det_details (p_detail_id).dff_attr30,
2722 p_attribute_index_info => p_attribute_index_info
2723 );
2724 END IF;
2725 END IF; ----no dff context in database
2726 END IF;
2727 END IF;
2728 END;
2729
2730 PROCEDURE check_msg_set_process_flag (
2731 p_blocks IN OUT NOCOPY hxc_block_table_type,
2732 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
2733 p_messages IN OUT NOCOPY hxc_message_table_type
2734 ) IS
2735 l_change_process_flag BOOLEAN := FALSE;
2736 l_index BINARY_INTEGER
2737 /*ADVICE(2237): Consider using PLS_INTEGER instead of INTEGER and BINARY_INTEGER if on Oracle 7.3 or above
2738 [302] */
2739 ;
2740 l_delete_index BINARY_INTEGER
2741 /*ADVICE(2241): Consider using PLS_INTEGER instead of INTEGER and BINARY_INTEGER if on Oracle 7.3 or above
2742 [302] */
2743 ;
2744 l_timecard_status VARCHAR2 (50) := NULL;
2745 /*ADVICE(2245): Initialization to NULL is superfluous [417] */
2746
2747 l_timecard_id NUMBER
2748 /*ADVICE(2248): NUMBER has no precision [315] */
2749 ;
2750 l_timecard_ovn NUMBER
2751 /*ADVICE(2251): NUMBER has no precision [315] */
2752 ;
2753 BEGIN
2754 l_change_process_flag := FALSE;
2755 l_delete_index := NULL;
2756 l_index := p_messages.FIRST;
2757
2758 WHILE l_index IS NOT NULL LOOP
2759 IF p_messages (l_index).message_name = 'HXC_TIMECARD_NOT_SUBMITTED' THEN
2760 l_change_process_flag := TRUE;
2761 l_delete_index := l_index;
2762 EXIT;
2763 /*ADVICE(2263): An EXIT statement is used in a WHILE loop [502] */
2764
2765 END IF;
2766
2767 l_index := p_messages.NEXT (l_index);
2768 END LOOP;
2769
2770 IF l_change_process_flag THEN
2771 -- if we passed the above then we need to change the status
2772 -- to be process so that the TC get's submitted this
2773 -- is when rejected TC is submitted without any change
2774 l_timecard_id :=
2775 p_blocks (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).time_building_block_id;
2776 l_timecard_ovn :=
2777 p_blocks (hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).object_version_number;
2778 l_timecard_status := hxc_timecard_search_pkg.get_timecard_status_code (l_timecard_id, l_timecard_ovn);
2779
2780 IF l_timecard_status = 'REJECTED' THEN
2781 l_index := p_blocks.FIRST;
2782
2783 WHILE l_index IS NOT NULL LOOP
2784 p_blocks (l_index).process := 'Y';
2785 p_blocks (l_index).changed := 'Y';
2786 l_index := p_blocks.NEXT (l_index);
2787 END LOOP;
2788
2789 l_index := p_attributes.FIRST;
2790
2791 WHILE l_index IS NOT NULL LOOP
2792 p_attributes (l_index).process := 'Y';
2793 p_attributes (l_index).changed := 'Y';
2794 l_index := p_attributes.NEXT (l_index);
2795 END LOOP;
2796
2797 IF l_delete_index IS NOT NULL THEN
2798 --now delete the error as we have handled it
2799 p_messages.DELETE (l_delete_index);
2800 END IF;
2801 END IF;
2802 END IF;
2803 END check_msg_set_process_flag;
2804
2805
2806 -- ----------------------------------------------------------------------------
2807 -- Used to loop through all the persons in TK group
2808 -- and do bulk pref evaluation
2809 -- ----------------------------------------------------------------------------
2810 PROCEDURE cache_employee_pref_in_group (
2811 p_group_id IN NUMBER,
2812 p_timekeeper_id IN NUMBER
2813 ) IS
2814 l_resource_pref_index NUMBER
2815 /*ADVICE(2315): NUMBER has no precision [315] */
2816 ;
2817 l_pref_table hxc_preference_evaluation.t_pref_table;
2818 l_resource_table hxc_preference_evaluation.t_resource_pref_table;
2819 l_query_append VARCHAR2 (32000);
2820 /*ADVICE(2320): VARCHAR2 declaration with length greater than 500 characters [307] */
2821
2822
2823 CURSOR c_emp_hireinfo (
2824 p_resource_id NUMBER
2825 ) IS
2826 SELECT MIN (effective_start_date)
2827 FROM per_people_f
2828 WHERE person_id = p_resource_id;
2829
2830 /*Cursor Modified By Mithun for CWK Terminate Bug*/
2831 /* changes done by senthil for emp terminate enhancement*/
2832 CURSOR c_emp_terminateinfo(
2833 p_resource_id NUMBER
2834 ) IS
2835 SELECT final_process_date, date_start
2836 FROM per_periods_of_service
2837 WHERE person_id = p_resource_id
2838 union all
2839 select (final_process_date + NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0)) final_process_date, date_start
2840 from per_periods_of_placement
2841 where person_id = p_resource_id
2842 ORDER BY date_start DESC;
2843
2844 --Added By Mithun for CWK Terminate Bug
2845 date_start DATE;
2846
2847 /*end of changes by senthil */
2848 BEGIN
2849 l_query_append := ' in (select htgqc.criteria_id resource_id ';
2850 l_query_append :=
2851 l_query_append || ' from hxc_tk_group_queries htgq, HXC_TK_GROUP_QUERY_CRITERIA htgqc ';
2852 l_query_append := l_query_append
2853 || ' where htgq.tk_group_query_id = htgqc.tk_group_query_id and htgq.tk_group_id='
2854 || p_group_id || ' ) ';
2855 l_pref_table.DELETE;
2856 l_resource_table.DELETE;
2857 g_start_stop_pref_cache.DELETE;
2858 hxc_preference_evaluation.resource_prefs_bulk (
2859 p_evaluation_date => SYSDATE,
2860 p_pref_table => l_pref_table,
2861 p_resource_pref_table => l_resource_table,
2862 p_resource_sql => l_query_append
2863 );
2864 l_resource_pref_index := l_resource_table.FIRST;
2865
2866 LOOP
2867 EXIT WHEN (NOT l_resource_table.EXISTS (l_resource_pref_index));
2868
2869 IF p_timekeeper_id <> l_resource_pref_index THEN
2870 IF g_start_stop_pref_cache.EXISTS (l_resource_table (l_resource_pref_index).start_index) THEN
2871 g_resource_perftab (l_resource_pref_index) :=
2872 g_start_stop_pref_cache (l_resource_table (l_resource_pref_index).start_index);
2873 OPEN c_emp_hireinfo (p_resource_id => l_resource_pref_index);
2874 FETCH c_emp_hireinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_start_date;
2875 CLOSE c_emp_hireinfo;
2876 /*Changes Done By Mithun for CWK Terminate Bug*/
2877 /* changes done by senthil for emp terminate enhancement*/
2878 OPEN c_emp_terminateinfo (p_resource_id => l_resource_pref_index);
2879 FETCH c_emp_terminateinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_terminate_date, date_start;
2880 CLOSE c_emp_terminateinfo;
2881 /*end of changes by senthil*/
2882 ELSE
2883 OPEN c_emp_hireinfo (p_resource_id => l_resource_pref_index);
2884 FETCH c_emp_hireinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_start_date;
2885 CLOSE c_emp_hireinfo;
2886
2887 /*Changes Done By Mithun for CWK Terminate Bug*/
2888 /* changes done by senthil for emp terminate enhancement*/
2889 OPEN c_emp_terminateinfo (p_resource_id => l_resource_pref_index);
2890 FETCH c_emp_terminateinfo INTO g_resource_perftab (l_resource_pref_index).res_emp_terminate_date, date_start;
2891 CLOSE c_emp_terminateinfo;
2892 /*end of changes by senthil*/
2893 FOR l_index IN
2894 l_resource_table (l_resource_pref_index).start_index .. l_resource_table (l_resource_pref_index).stop_index LOOP
2895 hxc_timekeeper_utilities.add_resource_to_perftab (
2896 p_resource_id => l_resource_pref_index,
2897 p_pref_code => l_pref_table (l_index).preference_code,
2898 p_attribute1 => l_pref_table (l_index).attribute1,
2899 p_attribute2 => l_pref_table (l_index).attribute2,
2900 p_attribute3 => l_pref_table (l_index).attribute3,
2901 p_attribute4 => l_pref_table (l_index).attribute4,
2902 p_attribute5 => l_pref_table (l_index).attribute5,
2903 p_attribute6 => l_pref_table (l_index).attribute6,
2904 p_attribute7 => l_pref_table (l_index).attribute7,
2905 p_attribute8 => l_pref_table (l_index).attribute8,
2906 p_attribute11 => l_pref_table (l_index).attribute11
2907 );
2908 END LOOP;
2909 /*ADVICE(2378): Nested LOOPs should all be labeled [406] */
2910
2911
2912 g_start_stop_pref_cache (l_resource_table (l_resource_pref_index).start_index) :=
2913 g_resource_perftab (l_resource_pref_index);
2914 END IF;
2915 END IF;
2916
2917 l_resource_pref_index := l_resource_table.NEXT (l_resource_pref_index);
2918 END LOOP;
2919 END cache_employee_pref_in_group;
2920
2921
2922 -- ----------------------------------------------------------------------------
2923 -- Used to loop through all the persons in TK group
2924 -- and get the recurring period informations
2925 -- ----------------------------------------------------------------------------
2926 PROCEDURE get_group_period_list (
2927 p_group_id IN NUMBER,
2928 p_business_group_id IN NUMBER,
2929 p_periodname_list OUT NOCOPY hxc_timekeeper_utilities.t_group_list
2930 ) IS
2931 /*start of fix 5083261*/
2932 CURSOR c_pref_cursor IS
2933 select recurring_period_id rec_period,rownum periodname_index from (
2934 SELECT distinct c.recurring_period_id,c.name
2935 FROM hxc_pref_hierarchies a, hxc_resource_rules b, hxc_recurring_periods c
2936 WHERE a.attribute_category = 'TC_W_TCRD_PERIOD'
2937 and a.attribute1 = c.recurring_period_id
2938 AND a.top_level_parent_id = b.pref_hierarchy_id
2939 AND p_business_group_id = NVL (a.business_group_id, p_business_group_id) order by c.name);
2940 /*end of fix 5083261*/
2941 l_pref_resource
2942 /*ADVICE(2415): Unreferenced variable [553] */
2943 hxc_preference_evaluation.t_pref_table;
2944 l_periodname_list
2945 /*ADVICE(2418): Unreferenced variable [553] */
2946 hxc_timekeeper_utilities.t_group_list;
2947 l_pref_index
2948 /*ADVICE(2421): Unreferenced variable [553] */
2949 NUMBER
2950 /*ADVICE(2423): NUMBER has no precision [315] */
2951 ;
2952 l_periodname_index NUMBER
2953 /*ADVICE(2426): NUMBER has no precision [315] */
2954 ;
2955 BEGIN
2956 p_periodname_list.DELETE;
2957
2958 FOR l_pref IN c_pref_cursor LOOP
2959 /*start fix for 5083261 */
2960 l_periodname_index := l_pref.periodname_index;
2961 /*end of fix 5083261*/
2962 p_periodname_list (l_periodname_index).recurring_period_id := l_pref.rec_period;
2963 p_periodname_list (l_periodname_index).GROUP_ID := p_group_id;
2964 END LOOP;
2965 END;
2966
2967
2968 -----------------------------------------------------------------------------
2969 -- Used to get the select statement depending upon the alias
2970 ------------------------------------------------------------------------------
2971 PROCEDURE get_type_sql (
2972 p_aliasid IN NUMBER,
2973 p_person_type IN VARCHAR2 DEFAULT NULL,
2974 p_alias_typ OUT NOCOPY VARCHAR2,
2975 p_alias_sql OUT NOCOPY LONG
2976 /*ADVICE(2447): Use of LONG [117] */
2977 ,
2978 p_maxsize OUT NOCOPY NUMBER,
2979 p_minvalue OUT NOCOPY NUMBER,
2980 p_maxvalue OUT NOCOPY NUMBER,
2981 p_precision OUT NOCOPY NUMBER,
2982 p_colmtype OUT NOCOPY VARCHAR2
2983 ) IS
2984 l_alias_type VARCHAR2 (80) := NULL;
2985 /*ADVICE(2456): Initialization to NULL is superfluous [417] */
2986
2987 l_ref_obj VARCHAR2 (2000) := NULL;
2988 /*ADVICE(2459): VARCHAR2 declaration with length greater than 500 characters [307] */
2989
2990 /*ADVICE(2461): Initialization to NULL is superfluous [417] */
2991
2992 l_s_query LONG
2993 /*ADVICE(2464): Use of LONG [117] */
2994 := NULL;
2995 /*ADVICE(2466): Initialization to NULL is superfluous [417] */
2996
2997 l_index
2998 /*ADVICE(2469): Unreferenced variable [553] */
2999 NUMBER
3000 /*ADVICE(2471): NUMBER has no precision [315] */
3001 := 0;
3002 l_a VARCHAR2 (300);
3003 l_max_size NUMBER
3004 /*ADVICE(2475): NUMBER has no precision [315] */
3005 := 0;
3006 l_min_value NUMBER
3007 /*ADVICE(2478): NUMBER has no precision [315] */
3008 := 0;
3009 l_max_value NUMBER
3010 /*ADVICE(2481): NUMBER has no precision [315] */
3011 := 0;
3012 l_number_precision NUMBER
3013 /*ADVICE(2484): NUMBER has no precision [315] */
3014 := 0;
3015 l_colmtype VARCHAR2 (10);
3016 BEGIN
3017 hxc_alias_utility.get_alias_definition_info (
3018 p_alias_definition_id => p_aliasid,
3019 p_alias_type => l_alias_type,
3020 p_reference_object => l_ref_obj,
3021 p_prompt => l_a
3022 );
3023
3024 IF l_alias_type = 'VALUE_SET_TABLE' THEN
3025 hxc_alias_utility.get_vset_table_type_select (
3026 p_alias_definition_id => p_aliasid,
3027 x_select => l_s_query,
3028 p_id_type => l_colmtype
3029 );
3030 l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
3031 ELSIF l_alias_type = 'VALUE_SET_INDEPENDENT' THEN
3032 hxc_alias_utility.get_vset_indep_type_select (
3033 p_alias_definition_id => p_aliasid,
3034 x_select => l_s_query
3035 );
3036 l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
3037 l_colmtype := 'NUMBER';
3038 ELSIF l_alias_type = 'OTL_ALT_DDF' THEN
3039 hxc_alias_utility.get_otl_an_context_type_select (
3040 p_alias_definition_id => p_aliasid,
3041 p_timekeeper_person_type => p_person_type,
3042 x_select => l_s_query
3043 );
3044 l_s_query := 'select display_value,id_value from (' || l_s_query || ') ';
3045 l_colmtype := 'NUMBER';
3046 ELSIF l_alias_type = 'VALUE_SET_NONE' THEN
3047 hxc_alias_utility.get_vset_none_type_property (
3048 p_alias_definition_id => p_aliasid,
3049 p_format_type => l_s_query,
3050 p_maximum_size => l_max_size,
3051 p_minimum_value => l_min_value,
3052 p_maximum_value => l_max_value,
3053 p_number_precision => l_number_precision
3054 );
3055 END IF;
3056
3057 p_alias_typ := l_alias_type;
3058 p_alias_sql := l_s_query;
3059 p_maxsize := l_max_size;
3060 p_minvalue := l_min_value;
3061 p_maxvalue := l_max_value;
3062 p_precision := l_number_precision;
3063 p_colmtype := l_colmtype;
3064 END get_type_sql;
3065
3066
3067 -- ----------------------------------------------------------------------------
3068 -- Used in form to get alias information for all the Timekeeper layout attributes
3069 -- ----------------------------------------------------------------------------
3070 PROCEDURE populate_alias_table (
3071 p_timekeeper_id IN NUMBER,
3072 p_tk_layout_info OUT NOCOPY hxc_timekeeper_utilities.tk_layout_tab,
3073 p_att_alias_table OUT NOCOPY hxc_timekeeper_utilities.att_alias_list
3074 ) IS
3075 l_pref_table hxc_preference_evaluation.t_pref_table;
3076 l_index NUMBER
3077 /*ADVICE(2548): NUMBER has no precision [315] */
3078 := 0;
3079 m_alias_typ VARCHAR2 (80) := NULL;
3080 /*ADVICE(2551): Initialization to NULL is superfluous [417] */
3081
3082 m_alias_sql LONG
3083 /*ADVICE(2554): Use of LONG [117] */
3084 := NULL;
3085 /*ADVICE(2556): Initialization to NULL is superfluous [417] */
3086
3087 m_alias_maxsize NUMBER
3088 /*ADVICE(2559): NUMBER has no precision [315] */
3089 ;
3090 m_alias_minvalue NUMBER
3091 /*ADVICE(2562): NUMBER has no precision [315] */
3092 ;
3093 m_alias_maxvalue NUMBER
3094 /*ADVICE(2565): NUMBER has no precision [315] */
3095 ;
3096 m_alias_precision NUMBER
3097 /*ADVICE(2568): NUMBER has no precision [315] */
3098 ;
3099 m_alias_lovcoltype VARCHAR2 (10);
3100 /*Start fix for Bug 5055770 */
3101 l_person_type PER_PERSON_TYPES.SYSTEM_PERSON_TYPE%TYPE:=NULL;
3102 /*End fix for Bug 5055770 */
3103 /*ADVICE(2572): Initialization to NULL is superfluous [417] */
3104
3105
3106 CURSOR c_get_type (
3107 p_person_id NUMBER
3108 ) IS
3109 SELECT typ.system_person_type
3110 FROM per_person_types typ, per_person_type_usages_f ptu
3111 WHERE typ.person_type_id = ptu.person_type_id
3112 AND SYSDATE BETWEEN ptu.effective_start_date AND ptu.effective_end_date
3113 AND ptu.person_id = p_person_id;
3114 BEGIN
3115 hxc_preference_evaluation.resource_preferences (p_timekeeper_id, l_pref_table, SYSDATE);
3116 OPEN c_get_type (p_timekeeper_id);
3117 FETCH c_get_type INTO l_person_type;
3118 CLOSE c_get_type;
3119 l_index := l_pref_table.FIRST;
3120
3121 LOOP
3122 EXIT WHEN (NOT l_pref_table.EXISTS (l_index));
3123
3124 IF (l_pref_table (l_index).preference_code = 'TK_TCARD_SETUP') THEN
3125 p_tk_layout_info (1).tk_timeflag := l_pref_table (l_index).attribute1;
3126 p_tk_layout_info (1).tk_empno := l_pref_table (l_index).attribute3;
3127 p_tk_layout_info (1).tk_empname := l_pref_table (l_index).attribute2;
3128 p_tk_layout_info (1).tk_base_attr := l_pref_table (l_index).attribute4;
3129 p_tk_layout_info (1).tk_applset := l_pref_table (l_index).attribute5;
3130 ELSIF (l_pref_table (l_index).preference_code = 'TK_TCARD_CLA') THEN --CTK
3131 p_tk_layout_info (1).tk_audit_enabled := l_pref_table (l_index).attribute1;
3132 p_tk_layout_info (1).tk_data_entry_required := l_pref_table (l_index).attribute2;
3133 p_tk_layout_info (1).tk_notification_to := l_pref_table (l_index).attribute3;
3134 p_tk_layout_info (1).tk_notification_type := l_pref_table (l_index).attribute4;
3135 ELSIF (l_pref_table (l_index).preference_code = 'TK_TCARD_ATTRIBUTES_DEFINITION') THEN
3136 m_alias_sql := NULL;
3137 m_alias_typ := NULL;
3138 p_att_alias_table (1).attr_name := 'ATTRIBUTE1';
3139 p_att_alias_table (1).alias_id := l_pref_table (l_index).attribute1;
3140
3141 IF p_att_alias_table (1).alias_id IS NOT NULL THEN
3142 get_type_sql (
3143 p_aliasid => p_att_alias_table (1).alias_id,
3144 p_person_type => l_person_type,
3145 p_alias_typ => m_alias_typ,
3146 p_alias_sql => m_alias_sql,
3147 p_maxsize => m_alias_maxsize,
3148 p_minvalue => m_alias_minvalue,
3149 p_maxvalue => m_alias_maxvalue,
3150 p_precision => m_alias_precision,
3151 p_colmtype => m_alias_lovcoltype
3152 );
3153 p_att_alias_table (1).alias_sql := m_alias_sql;
3154 p_att_alias_table (1).alias_type := m_alias_typ;
3155 p_att_alias_table (1).alias_maxsize := m_alias_maxsize;
3156 p_att_alias_table (1).alias_minvalue := m_alias_minvalue;
3157 p_att_alias_table (1).alias_maxvalue := m_alias_maxvalue;
3158 p_att_alias_table (1).alias_precision := m_alias_precision;
3159 p_att_alias_table (1).alias_lovcoltype := m_alias_lovcoltype;
3160 END IF;
3161
3162 p_att_alias_table (2).attr_name := 'ATTRIBUTE2';
3163 p_att_alias_table (2).alias_id := l_pref_table (l_index).attribute2;
3164
3165 IF p_att_alias_table (2).alias_id IS NOT NULL THEN
3166 get_type_sql (
3167 p_aliasid => p_att_alias_table (2).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 (2).alias_sql := m_alias_sql;
3178 p_att_alias_table (2).alias_type := m_alias_typ;
3179 p_att_alias_table (2).alias_maxsize := m_alias_maxsize;
3180 p_att_alias_table (2).alias_minvalue := m_alias_minvalue;
3181 p_att_alias_table (2).alias_maxvalue := m_alias_maxvalue;
3182 p_att_alias_table (2).alias_precision := m_alias_precision;
3183 p_att_alias_table (2).alias_lovcoltype := m_alias_lovcoltype;
3184 END IF;
3185
3186 p_att_alias_table (3).attr_name := 'ATTRIBUTE3';
3187 p_att_alias_table (3).alias_id := l_pref_table (l_index).attribute3;
3188
3189 IF p_att_alias_table (3).alias_id IS NOT NULL THEN
3190 get_type_sql (
3191 p_aliasid => p_att_alias_table (3).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 (3).alias_sql := m_alias_sql;
3202 p_att_alias_table (3).alias_type := m_alias_typ;
3203 p_att_alias_table (3).alias_maxsize := m_alias_maxsize;
3204 p_att_alias_table (3).alias_minvalue := m_alias_minvalue;
3205 p_att_alias_table (3).alias_maxvalue := m_alias_maxvalue;
3206 p_att_alias_table (3).alias_precision := m_alias_precision;
3207 p_att_alias_table (3).alias_lovcoltype := m_alias_lovcoltype;
3208 END IF;
3209
3210 p_att_alias_table (4).attr_name := 'ATTRIBUTE4';
3211 p_att_alias_table (4).alias_id := l_pref_table (l_index).attribute4;
3212
3213 IF p_att_alias_table (4).alias_id IS NOT NULL THEN
3214 get_type_sql (
3215 p_aliasid => p_att_alias_table (4).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 (4).alias_sql := m_alias_sql;
3226 p_att_alias_table (4).alias_type := m_alias_typ;
3227 p_att_alias_table (4).alias_maxsize := m_alias_maxsize;
3228 p_att_alias_table (4).alias_minvalue := m_alias_minvalue;
3229 p_att_alias_table (4).alias_maxvalue := m_alias_maxvalue;
3230 p_att_alias_table (4).alias_precision := m_alias_precision;
3231 p_att_alias_table (4).alias_lovcoltype := m_alias_lovcoltype;
3232 END IF;
3233
3234 p_att_alias_table (5).attr_name := 'ATTRIBUTE5';
3235 p_att_alias_table (5).alias_id := l_pref_table (l_index).attribute5;
3236
3237 IF p_att_alias_table (5).alias_id IS NOT NULL THEN
3238 get_type_sql (
3239 p_aliasid => p_att_alias_table (5).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 (5).alias_sql := m_alias_sql;
3250 p_att_alias_table (5).alias_type := m_alias_typ;
3251 p_att_alias_table (5).alias_maxsize := m_alias_maxsize;
3252 p_att_alias_table (5).alias_minvalue := m_alias_minvalue;
3253 p_att_alias_table (5).alias_maxvalue := m_alias_maxvalue;
3254 p_att_alias_table (5).alias_precision := m_alias_precision;
3255 p_att_alias_table (5).alias_lovcoltype := m_alias_lovcoltype;
3256 END IF;
3257
3258 p_att_alias_table (6).attr_name := 'ATTRIBUTE6';
3259 p_att_alias_table (6).alias_id := l_pref_table (l_index).attribute6;
3260
3261 IF p_att_alias_table (6).alias_id IS NOT NULL THEN
3262 get_type_sql (
3263 p_aliasid => p_att_alias_table (6).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 (6).alias_sql := m_alias_sql;
3274 p_att_alias_table (6).alias_type := m_alias_typ;
3275 p_att_alias_table (6).alias_maxsize := m_alias_maxsize;
3276 p_att_alias_table (6).alias_minvalue := m_alias_minvalue;
3277 p_att_alias_table (6).alias_maxvalue := m_alias_maxvalue;
3278 p_att_alias_table (6).alias_precision := m_alias_precision;
3279 p_att_alias_table (6).alias_lovcoltype := m_alias_lovcoltype;
3280 END IF;
3281
3282 p_att_alias_table (7).attr_name := 'ATTRIBUTE7';
3283 p_att_alias_table (7).alias_id := l_pref_table (l_index).attribute7;
3284
3285 IF p_att_alias_table (7).alias_id IS NOT NULL THEN
3286 get_type_sql (
3287 p_aliasid => p_att_alias_table (7).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 (7).alias_sql := m_alias_sql;
3298 p_att_alias_table (7).alias_type := m_alias_typ;
3299 p_att_alias_table (7).alias_maxsize := m_alias_maxsize;
3300 p_att_alias_table (7).alias_minvalue := m_alias_minvalue;
3301 p_att_alias_table (7).alias_maxvalue := m_alias_maxvalue;
3302 p_att_alias_table (7).alias_precision := m_alias_precision;
3303 p_att_alias_table (7).alias_lovcoltype := m_alias_lovcoltype;
3304 END IF;
3305
3306 p_att_alias_table (8).attr_name := 'ATTRIBUTE8';
3307 p_att_alias_table (8).alias_id := l_pref_table (l_index).attribute8;
3308
3309 IF p_att_alias_table (8).alias_id IS NOT NULL THEN
3310 get_type_sql (
3311 p_aliasid => p_att_alias_table (8).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 (8).alias_sql := m_alias_sql;
3322 p_att_alias_table (8).alias_type := m_alias_typ;
3323 p_att_alias_table (8).alias_maxsize := m_alias_maxsize;
3324 p_att_alias_table (8).alias_minvalue := m_alias_minvalue;
3325 p_att_alias_table (8).alias_maxvalue := m_alias_maxvalue;
3326 p_att_alias_table (8).alias_precision := m_alias_precision;
3327 p_att_alias_table (8).alias_lovcoltype := m_alias_lovcoltype;
3328 END IF;
3329
3330 p_att_alias_table (9).attr_name := 'ATTRIBUTE9';
3331 p_att_alias_table (9).alias_id := l_pref_table (l_index).attribute9;
3332
3333 IF p_att_alias_table (9).alias_id IS NOT NULL THEN
3334 get_type_sql (
3335 p_aliasid => p_att_alias_table (9).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 (9).alias_sql := m_alias_sql;
3346 p_att_alias_table (9).alias_type := m_alias_typ;
3347 p_att_alias_table (9).alias_maxsize := m_alias_maxsize;
3348 p_att_alias_table (9).alias_minvalue := m_alias_minvalue;
3349 p_att_alias_table (9).alias_maxvalue := m_alias_maxvalue;
3350 p_att_alias_table (9).alias_precision := m_alias_precision;
3351 p_att_alias_table (9).alias_lovcoltype := m_alias_lovcoltype;
3352 END IF;
3353
3354 p_att_alias_table (10).attr_name := 'ATTRIBUTE10';
3355 p_att_alias_table (10).alias_id := l_pref_table (l_index).attribute10;
3356
3357 IF p_att_alias_table (10).alias_id IS NOT NULL THEN
3358 get_type_sql (
3359 p_aliasid => p_att_alias_table (10).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 (10).alias_sql := m_alias_sql;
3370 p_att_alias_table (10).alias_type := m_alias_typ;
3371 p_att_alias_table (10).alias_maxsize := m_alias_maxsize;
3372 p_att_alias_table (10).alias_minvalue := m_alias_minvalue;
3373 p_att_alias_table (10).alias_maxvalue := m_alias_maxvalue;
3374 p_att_alias_table (10).alias_precision := m_alias_precision;
3375 p_att_alias_table (10).alias_lovcoltype := m_alias_lovcoltype;
3376 END IF;
3377
3378 p_att_alias_table (11).attr_name := 'ATTRIBUTE11';
3379 p_att_alias_table (11).alias_id := l_pref_table (l_index).attribute11;
3380
3381 IF p_att_alias_table (11).alias_id IS NOT NULL THEN
3382 get_type_sql (
3383 p_aliasid => p_att_alias_table (11).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 (11).alias_sql := m_alias_sql;
3394 p_att_alias_table (11).alias_type := m_alias_typ;
3395 p_att_alias_table (11).alias_maxsize := m_alias_maxsize;
3396 p_att_alias_table (11).alias_minvalue := m_alias_minvalue;
3397 p_att_alias_table (11).alias_maxvalue := m_alias_maxvalue;
3398 p_att_alias_table (11).alias_precision := m_alias_precision;
3399 p_att_alias_table (11).alias_lovcoltype := m_alias_lovcoltype;
3400 END IF;
3401
3402 p_att_alias_table (12).attr_name := 'ATTRIBUTE12';
3403 p_att_alias_table (12).alias_id := l_pref_table (l_index).attribute12;
3404
3405 IF p_att_alias_table (12).alias_id IS NOT NULL THEN
3406 get_type_sql (
3407 p_aliasid => p_att_alias_table (12).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 (12).alias_sql := m_alias_sql;
3418 p_att_alias_table (12).alias_type := m_alias_typ;
3419 p_att_alias_table (12).alias_maxsize := m_alias_maxsize;
3420 p_att_alias_table (12).alias_minvalue := m_alias_minvalue;
3421 p_att_alias_table (12).alias_maxvalue := m_alias_maxvalue;
3422 p_att_alias_table (12).alias_precision := m_alias_precision;
3423 p_att_alias_table (12).alias_lovcoltype := m_alias_lovcoltype;
3424 END IF;
3425
3426 p_att_alias_table (13).attr_name := 'ATTRIBUTE13';
3427 p_att_alias_table (13).alias_id := l_pref_table (l_index).attribute13;
3428
3429 IF p_att_alias_table (13).alias_id IS NOT NULL THEN
3430 get_type_sql (
3431 p_aliasid => p_att_alias_table (13).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 (13).alias_sql := m_alias_sql;
3442 p_att_alias_table (13).alias_type := m_alias_typ;
3443 p_att_alias_table (13).alias_maxsize := m_alias_maxsize;
3444 p_att_alias_table (13).alias_minvalue := m_alias_minvalue;
3445 p_att_alias_table (13).alias_maxvalue := m_alias_maxvalue;
3446 p_att_alias_table (13).alias_precision := m_alias_precision;
3447 p_att_alias_table (13).alias_lovcoltype := m_alias_lovcoltype;
3448 END IF;
3449
3450 p_att_alias_table (14).attr_name := 'ATTRIBUTE14';
3451 p_att_alias_table (14).alias_id := l_pref_table (l_index).attribute14;
3452
3453 IF p_att_alias_table (14).alias_id IS NOT NULL THEN
3454 get_type_sql (
3455 p_aliasid => p_att_alias_table (14).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 (14).alias_sql := m_alias_sql;
3466 p_att_alias_table (14).alias_type := m_alias_typ;
3467 p_att_alias_table (14).alias_maxsize := m_alias_maxsize;
3468 p_att_alias_table (14).alias_minvalue := m_alias_minvalue;
3469 p_att_alias_table (14).alias_maxvalue := m_alias_maxvalue;
3470 p_att_alias_table (14).alias_precision := m_alias_precision;
3471 p_att_alias_table (14).alias_lovcoltype := m_alias_lovcoltype;
3472 END IF;
3473
3474 p_att_alias_table (15).attr_name := 'ATTRIBUTE15';
3475 p_att_alias_table (15).alias_id := l_pref_table (l_index).attribute15;
3476
3477 IF p_att_alias_table (15).alias_id IS NOT NULL THEN
3478 get_type_sql (
3479 p_aliasid => p_att_alias_table (15).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 (15).alias_sql := m_alias_sql;
3490 p_att_alias_table (15).alias_type := m_alias_typ;
3491 p_att_alias_table (15).alias_maxsize := m_alias_maxsize;
3492 p_att_alias_table (15).alias_minvalue := m_alias_minvalue;
3493 p_att_alias_table (15).alias_maxvalue := m_alias_maxvalue;
3494 p_att_alias_table (15).alias_precision := m_alias_precision;
3495 p_att_alias_table (15).alias_lovcoltype := m_alias_lovcoltype;
3496 END IF;
3497
3498 p_att_alias_table (16).attr_name := 'ATTRIBUTE16';
3499 p_att_alias_table (16).alias_id := l_pref_table (l_index).attribute16;
3500
3501 IF p_att_alias_table (16).alias_id IS NOT NULL THEN
3502 get_type_sql (
3503 p_aliasid => p_att_alias_table (16).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 (16).alias_sql := m_alias_sql;
3514 p_att_alias_table (16).alias_type := m_alias_typ;
3515 p_att_alias_table (16).alias_maxsize := m_alias_maxsize;
3516 p_att_alias_table (16).alias_minvalue := m_alias_minvalue;
3517 p_att_alias_table (16).alias_maxvalue := m_alias_maxvalue;
3518 p_att_alias_table (16).alias_precision := m_alias_precision;
3519 p_att_alias_table (16).alias_lovcoltype := m_alias_lovcoltype;
3520 END IF;
3521
3522 p_att_alias_table (17).attr_name := 'ATTRIBUTE17';
3523 p_att_alias_table (17).alias_id := l_pref_table (l_index).attribute17;
3524
3525 IF p_att_alias_table (17).alias_id IS NOT NULL THEN
3526 get_type_sql (
3527 p_aliasid => p_att_alias_table (17).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 (17).alias_sql := m_alias_sql;
3538 p_att_alias_table (17).alias_type := m_alias_typ;
3539 p_att_alias_table (17).alias_maxsize := m_alias_maxsize;
3540 p_att_alias_table (17).alias_minvalue := m_alias_minvalue;
3541 p_att_alias_table (17).alias_maxvalue := m_alias_maxvalue;
3542 p_att_alias_table (17).alias_precision := m_alias_precision;
3543 p_att_alias_table (17).alias_lovcoltype := m_alias_lovcoltype;
3544 END IF;
3545
3546 p_att_alias_table (18).attr_name := 'ATTRIBUTE18';
3547 p_att_alias_table (18).alias_id := l_pref_table (l_index).attribute18;
3548
3549 IF p_att_alias_table (18).alias_id IS NOT NULL THEN
3550 get_type_sql (
3551 p_aliasid => p_att_alias_table (18).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 (18).alias_sql := m_alias_sql;
3562 p_att_alias_table (18).alias_type := m_alias_typ;
3563 p_att_alias_table (18).alias_maxsize := m_alias_maxsize;
3564 p_att_alias_table (18).alias_minvalue := m_alias_minvalue;
3565 p_att_alias_table (18).alias_maxvalue := m_alias_maxvalue;
3566 p_att_alias_table (18).alias_precision := m_alias_precision;
3567 p_att_alias_table (18).alias_lovcoltype := m_alias_lovcoltype;
3568 END IF;
3569
3570 p_att_alias_table (19).attr_name := 'ATTRIBUTE19';
3571 p_att_alias_table (19).alias_id := l_pref_table (l_index).attribute19;
3572
3573 IF p_att_alias_table (19).alias_id IS NOT NULL THEN
3574 get_type_sql (
3575 p_aliasid => p_att_alias_table (19).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 (19).alias_sql := m_alias_sql;
3586 p_att_alias_table (19).alias_type := m_alias_typ;
3587 p_att_alias_table (19).alias_maxsize := m_alias_maxsize;
3588 p_att_alias_table (19).alias_minvalue := m_alias_minvalue;
3589 p_att_alias_table (19).alias_maxvalue := m_alias_maxvalue;
3590 p_att_alias_table (19).alias_precision := m_alias_precision;
3591 p_att_alias_table (19).alias_lovcoltype := m_alias_lovcoltype;
3592 END IF;
3593
3594 p_att_alias_table (20).attr_name := 'ATTRIBUTE20';
3595 p_att_alias_table (20).alias_id := l_pref_table (l_index).attribute20;
3596
3597 IF p_att_alias_table (20).alias_id IS NOT NULL THEN
3598 get_type_sql (
3599 p_aliasid => p_att_alias_table (20).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 (20).alias_sql := m_alias_sql;
3610 p_att_alias_table (20).alias_type := m_alias_typ;
3611 p_att_alias_table (20).alias_maxsize := m_alias_maxsize;
3612 p_att_alias_table (20).alias_minvalue := m_alias_minvalue;
3613 p_att_alias_table (20).alias_maxvalue := m_alias_maxvalue;
3614 p_att_alias_table (20).alias_precision := m_alias_precision;
3615 p_att_alias_table (20).alias_lovcoltype := m_alias_lovcoltype;
3616 END IF;
3617 END IF;
3618
3619 l_index := l_pref_table.NEXT (l_index);
3620 END LOOP;
3621 END;
3622
3623
3624 -- ----------------------------------------------------------------------------
3625 -- Used in form in post query to enable /disable days in timecard row
3626 -- ----------------------------------------------------------------------------
3627 PROCEDURE populate_disable_tc_tab (
3628 resource_id IN NUMBER,
3629 tc_frdt IN DATE,
3630 tc_todt IN DATE,
3631 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab
3632 ) IS
3633 CURSOR get_tc_data (
3634 p_resource_id NUMBER,
3635 p_tc_frdt DATE,
3636 p_tc_todt DATE
3637 ) IS
3638 SELECT time_building_block_id tbbid, start_time, stop_time, TO_DATE (start_time, 'dd-mm-rrrr')
3639 FROM hxc_time_building_blocks
3640 WHERE resource_id
3641 /*ADVICE(3110): Cursor references an external variable (use a parameter) [209] */
3642 = p_resource_id
3643 AND SCOPE = 'TIMECARD'
3644 AND date_to = hr_general.end_of_time
3645 AND TO_DATE (start_time, 'dd-mm-rrrr') < TO_DATE (p_tc_todt, 'dd-mm-rrrr')
3646 AND TO_DATE (stop_time, 'dd-mm-rrrr') > TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
3647 AND ( TO_DATE (start_time, 'dd-mm-rrrr') <> TO_DATE (p_tc_frdt, 'dd-mm-rrrr')
3648 OR TO_DATE (stop_time, 'dd-mm-rrrr') <> TO_DATE (p_tc_todt, 'dd-mm-rrrr')
3649 )
3650 ORDER BY 4;
3651
3652 tc_tab_rec get_tc_data%ROWTYPE;
3653 p_index NUMBER
3654 /*ADVICE(3123): NUMBER has no precision [315] */
3655 := 0;
3656 BEGIN
3657 OPEN get_tc_data (resource_id, tc_frdt, tc_todt);
3658
3659 LOOP
3660 FETCH get_tc_data INTO tc_tab_rec;
3661 EXIT WHEN get_tc_data%NOTFOUND;
3662
3663 IF p_emptcinfo.COUNT > 0 THEN
3664 p_index := p_emptcinfo.LAST + 1;
3665 ELSE
3666 p_index := 1;
3667 END IF;
3668
3669 p_emptcinfo (p_index).timecard_id := tc_tab_rec.tbbid;
3670 p_emptcinfo (p_index).resource_id := resource_id;
3671 p_emptcinfo (p_index).tc_frdt := tc_tab_rec.start_time;
3672 p_emptcinfo (p_index).tc_todt := tc_tab_rec.stop_time;
3673 END LOOP;
3674
3675 CLOSE get_tc_data;
3676 END;
3677
3678
3679 -- ----------------------------------------------------------------------------
3680 -- Used in form to enable /disable days in timecard row
3681 -- when new row is created for a person manually
3682 -- ----------------------------------------------------------------------------
3683 PROCEDURE new_timecard (
3684 p_resource_id IN NUMBER,
3685 p_start_date IN DATE,
3686 p_end_date IN DATE,
3687 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab
3688 ) IS
3689 m_periods
3690 /*ADVICE(3159): Unreferenced variable [553] */
3691 VARCHAR2 (2000);
3692 /*ADVICE(3161): VARCHAR2 declaration with length greater than 500 characters [307] */
3693
3694 l_newtab hxc_timecard_utilities.periods;
3695 l_emp_tab_index NUMBER
3696 /*ADVICE(3165): NUMBER has no precision [315] */
3697 := 0;
3698 l_new_tab_index
3699 /*ADVICE(3168): Unreferenced variable [553] */
3700 NUMBER
3701 /*ADVICE(3170): NUMBER has no precision [315] */
3702 := 0;
3703 l_emp_negpref
3704 /*ADVICE(3173): This item should be defined in a deeper scope [558] */
3705 VARCHAR2 (150);
3706 l_emp_recpref NUMBER
3707 /*ADVICE(3176): NUMBER has no precision [315] */
3708 ;
3709 l_emp_appstyle
3710 /*ADVICE(3179): This item should be defined in a deeper scope [558] */
3711 NUMBER
3712 /*ADVICE(3181): NUMBER has no precision [315] */
3713 ;
3714 l_emp_layout1
3715 /*ADVICE(3184): This item should be defined in a deeper scope [558] */
3716 NUMBER
3717 /*ADVICE(3186): NUMBER has no precision [315] */
3718 ;
3719 l_emp_layout2
3720 /*ADVICE(3189): This item should be defined in a deeper scope [558] */
3721 NUMBER
3722 /*ADVICE(3191): NUMBER has no precision [315] */
3723 ;
3724 l_emp_layout3
3725 /*ADVICE(3194): This item should be defined in a deeper scope [558] */
3726 NUMBER
3727 /*ADVICE(3196): NUMBER has no precision [315] */
3728 ;
3729 l_emp_layout4
3730 /*ADVICE(3199): This item should be defined in a deeper scope [558] */
3731 NUMBER
3732 /*ADVICE(3201): NUMBER has no precision [315] */
3733 ;
3734 l_emp_layout5
3735 /*ADVICE(3204): This item should be defined in a deeper scope [558] */
3736 NUMBER
3737 /*ADVICE(3206): NUMBER has no precision [315] */
3738 ;
3739 l_emp_layout6
3740 /*ADVICE(3209): This item should be defined in a deeper scope [558] */
3741 NUMBER
3742 /*ADVICE(3211): NUMBER has no precision [315] */
3743 ;
3744 l_emp_layout7
3745 /*ADVICE(3214): This item should be defined in a deeper scope [558] */
3746 NUMBER
3747 /*ADVICE(3216): NUMBER has no precision [315] */
3748 ;
3749 l_emp_layout8
3750 /*ADVICE(3219): This item should be defined in a deeper scope [558] */
3751 NUMBER
3752 /*ADVICE(3221): NUMBER has no precision [315] */
3753 ;
3754 l_emp_edits
3755 /*ADVICE(3224): This item should be defined in a deeper scope [558] */
3756 VARCHAR2 (150);
3757 l_pastdt
3758 /*ADVICE(3227): This item should be defined in a deeper scope [558] */
3759 VARCHAR2 (30);
3760 l_futuredt
3761 /*ADVICE(3230): This item should be defined in a deeper scope [558] */
3762 VARCHAR2 (30);
3763 l_emp_start_date
3764 /*ADVICE(3233): This item should be defined in a deeper scope [558] */
3765 DATE;
3766 l_emp_terminate_date
3767 /*ADVICE(3233): This item should be defined in a deeper scope [558] */
3768 DATE;
3769 l_index NUMBER
3770 /*ADVICE(3236): NUMBER has no precision [315] */
3771 := 0;
3772 l_audit_enabled
3773 /*ADVICE(3239): This item should be defined in a deeper scope [558] */
3774 VARCHAR2 (150);
3775 BEGIN
3776 BEGIN
3777 hxc_timekeeper_utilities.get_emp_pref (
3778 p_resource_id,
3779 l_emp_negpref,
3780 l_emp_recpref,
3781 l_emp_appstyle,
3782 l_emp_layout1,
3783 l_emp_layout2,
3784 l_emp_layout3,
3785 l_emp_layout4,
3786 l_emp_layout5,
3787 l_emp_layout6,
3788 l_emp_layout7,
3789 l_emp_layout8,
3790 l_emp_edits,
3791 l_pastdt,
3792 l_futuredt,
3793 l_emp_start_date,
3794 l_emp_terminate_date,
3795 l_audit_enabled
3796 );
3797 EXCEPTION
3798 WHEN OTHERS THEN
3799 l_emp_recpref := NULL;
3800 /*ADVICE(3265): A WHEN OTHERS clause is used in the exception section without any other specific handlers
3801 [201] */
3802
3803 END;
3804
3805 l_newtab.DELETE;
3806 hxc_timekeeper_utilities.get_resource_time_periods (
3807 p_resource_id => p_resource_id,
3808 p_resource_type => 'PERSON',
3809 p_current_date => SYSDATE,
3810 p_max_date_in_futur => TO_DATE (p_end_date, 'dd-mm-rrrr') + 1,
3811 p_max_date_in_past => TO_DATE (p_start_date, 'dd-mm-rrrr') - 1,
3812 p_recurring_period_id => l_emp_recpref,
3813 p_check_assignment => TRUE,
3814 p_periodtab => l_newtab
3815 );
3816 l_emp_tab_index := l_newtab.FIRST;
3817
3818 LOOP
3819 EXIT WHEN NOT l_newtab.EXISTS (l_emp_tab_index);
3820
3821 IF TO_NUMBER (l_emp_tab_index) >= TO_NUMBER (TO_CHAR (TO_DATE (p_start_date, 'dd-mm-rrrr'), 'J'))
3822 AND TO_NUMBER (TO_CHAR (l_newtab (l_emp_tab_index).end_date, 'J')) <=
3823 TO_NUMBER (TO_CHAR (TO_DATE (p_end_date, 'dd-mm-rrrr'), 'J')) THEN
3824 IF p_emptcinfo.COUNT > 0 THEN
3825 l_index := p_emptcinfo.LAST + 1;
3826 ELSE
3827 l_index := 1;
3828 END IF;
3829
3830 p_emptcinfo (l_index).resource_id := p_resource_id;
3831 p_emptcinfo (l_index).tc_frdt := l_newtab (l_emp_tab_index).start_date;
3832 p_emptcinfo (l_index).tc_todt := l_newtab (l_emp_tab_index).end_date;
3833 END IF;
3834
3835 l_emp_tab_index := l_newtab.NEXT (l_emp_tab_index);
3836 END LOOP;
3837 END;
3838
3839 -------------------------
3840 --POP_DETAIL_TEMP
3841 -----------------
3842 /* This procedure is used to populate the pl/sql blocks from hxc_tk_detail_temp after the save process
3843 and then repoulate it back to the table once database commit is triggered from the forms
3844 has one parameter action, it has following values
3845 1 - hxc_tk_detail_temp--->pl/sql table
3846 2 - pl/sql table --> hxc_tk_detail_temp
3847 */
3848 procedure populate_detail_temp(p_action in number) is
3849 j binary_integer :=0;
3850 indx binary_integer;
3851 l_detail_blocks hxc_tk_detail_temp_tab;
3852 cursor csr_tk is select * from hxc_tk_detail_temp;
3853 begin
3854 l_detail_blocks:=hxc_timekeeper_utilities.g_hxc_tk_detail_temp_tab;
3855 if p_action =1 then
3856 open csr_tk;
3857 loop
3858 j:=j+1;
3859 /* Fetch entire row into record stored by jth element. */
3860 fetch csr_tk into l_detail_blocks(j);
3861 exit when csr_tk%NOTFOUND;
3862 end loop;
3863 close csr_tk;
3864
3865 ELSE
3866 /* start of fix for 5398144 */
3867 if l_detail_blocks.count > 0 then
3868 FOR indx IN l_detail_blocks.FIRST .. l_detail_blocks.LAST loop
3869 insert into hxc_tk_detail_temp
3870 (RESOURCE_ID,
3871 TIMECARD_ID,
3872 DETAILID,
3873 COMMENT_TEXT,
3874 DFF_CATG ,
3875 DETAIL_ACTION ,
3876 DFF_ATTR1 ,
3877 DFF_ATTR2 ,
3878 DFF_ATTR3 ,
3879 DFF_ATTR4 ,
3880 DFF_ATTR5 ,
3881 DFF_ATTR6 ,
3882 DFF_ATTR7 ,
3883 DFF_ATTR8 ,
3884 DFF_ATTR9 ,
3885 DFF_ATTR10 ,
3886 DFF_ATTR11 ,
3887 DFF_ATTR12 ,
3888 DFF_ATTR13 ,
3889 DFF_ATTR14 ,
3890 DFF_ATTR15 ,
3891 DFF_ATTR16 ,
3892 DFF_ATTR17 ,
3893 DFF_ATTR18 ,
3894 DFF_ATTR19 ,
3895 DFF_ATTR20 ,
3896 DFF_ATTR21 ,
3897 DFF_ATTR22 ,
3898 DFF_ATTR23 ,
3899 DFF_ATTR24 ,
3900 DFF_ATTR25 ,
3901 DFF_ATTR26 ,
3902 DFF_ATTR27 ,
3903 DFF_ATTR28 ,
3904 DFF_ATTR29 ,
3905 DFF_ATTR30 ,
3906 DFF_OLDATTR1 ,
3907 DFF_OLDATTR2 ,
3908 DFF_OLDATTR3 ,
3909 DFF_OLDATTR4 ,
3910 DFF_OLDATTR5 ,
3911 DFF_OLDATTR6 ,
3912 DFF_OLDATTR7 ,
3913 DFF_OLDATTR8 ,
3914 DFF_OLDATTR9 ,
3915 DFF_OLDATTR10 ,
3916 DFF_OLDATTR11 ,
3917 DFF_OLDATTR12 ,
3918 DFF_OLDATTR13 ,
3919 DFF_OLDATTR14 ,
3920 DFF_OLDATTR15 ,
3921 DFF_OLDATTR16 ,
3922 DFF_OLDATTR17 ,
3923 DFF_OLDATTR18 ,
3924 DFF_OLDATTR19 ,
3925 DFF_OLDATTR20 ,
3926 DFF_OLDATTR21 ,
3927 DFF_OLDATTR22 ,
3928 DFF_OLDATTR23 ,
3929 DFF_OLDATTR24 ,
3930 DFF_OLDATTR25 ,
3931 DFF_OLDATTR26 ,
3932 DFF_OLDATTR27 ,
3933 DFF_OLDATTR28 ,
3934 DFF_OLDATTR29 ,
3935 DFF_OLDATTR30 ,
3936 DFF_OLDCATG ,
3937 CHANGE_REASON ,
3938 CHANGE_COMMENT ,
3939 LATE_REASON ,
3940 LATE_COMMENT ,
3941 LATE_CHANGE ,
3942 DESC_FLEX ,
3943 ATTRIBUTE_CATEGORY ,
3944 AUDIT_DATETIME ,
3945 AUDIT_HISTORY ,
3946 DISP_INDEX ,
3947 OLD_CHANGE_REASON ,
3948 OLD_CHANGE_COMMENT ,
3949 OLD_LATE_REASON ,
3950 OLD_LATE_COMMENT ,
3951 OLD_AUDIT_HISTORY ,
3952 OLD_LATE_CHANGE ,
3953 OLD_AUDIT_DATETIME )
3954 values
3955 (
3956 l_detail_blocks(indx).RESOURCE_ID,
3957 l_detail_blocks(indx).TIMECARD_ID,
3958 l_detail_blocks(indx).DETAILID,
3959 l_detail_blocks(indx).COMMENT_TEXT,
3960 l_detail_blocks(indx).DFF_CATG ,
3961 l_detail_blocks(indx).DETAIL_ACTION ,
3962 l_detail_blocks(indx).DFF_ATTR1 ,
3963 l_detail_blocks(indx).DFF_ATTR2 ,
3964 l_detail_blocks(indx).DFF_ATTR3 ,
3965 l_detail_blocks(indx).DFF_ATTR4 ,
3966 l_detail_blocks(indx).DFF_ATTR5 ,
3967 l_detail_blocks(indx).DFF_ATTR6 ,
3968 l_detail_blocks(indx).DFF_ATTR7 ,
3969 l_detail_blocks(indx).DFF_ATTR8 ,
3970 l_detail_blocks(indx).DFF_ATTR9 ,
3971 l_detail_blocks(indx).DFF_ATTR10 ,
3972 l_detail_blocks(indx).DFF_ATTR11 ,
3973 l_detail_blocks(indx).DFF_ATTR12 ,
3974 l_detail_blocks(indx).DFF_ATTR13 ,
3975 l_detail_blocks(indx).DFF_ATTR14 ,
3976 l_detail_blocks(indx).DFF_ATTR15 ,
3977 l_detail_blocks(indx).DFF_ATTR16 ,
3978 l_detail_blocks(indx).DFF_ATTR17 ,
3979 l_detail_blocks(indx).DFF_ATTR18 ,
3980 l_detail_blocks(indx).DFF_ATTR19 ,
3981 l_detail_blocks(indx).DFF_ATTR20 ,
3982 l_detail_blocks(indx).DFF_ATTR21 ,
3983 l_detail_blocks(indx).DFF_ATTR22 ,
3984 l_detail_blocks(indx).DFF_ATTR23 ,
3985 l_detail_blocks(indx).DFF_ATTR24 ,
3986 l_detail_blocks(indx).DFF_ATTR25 ,
3987 l_detail_blocks(indx).DFF_ATTR26 ,
3988 l_detail_blocks(indx).DFF_ATTR27 ,
3989 l_detail_blocks(indx).DFF_ATTR28 ,
3990 l_detail_blocks(indx).DFF_ATTR29 ,
3991 l_detail_blocks(indx).DFF_ATTR30 ,
3992 l_detail_blocks(indx).DFF_OLDATTR1 ,
3993 l_detail_blocks(indx).DFF_OLDATTR2 ,
3994 l_detail_blocks(indx).DFF_OLDATTR3 ,
3995 l_detail_blocks(indx).DFF_OLDATTR4 ,
3996 l_detail_blocks(indx).DFF_OLDATTR5 ,
3997 l_detail_blocks(indx).DFF_OLDATTR6 ,
3998 l_detail_blocks(indx).DFF_OLDATTR7 ,
3999 l_detail_blocks(indx).DFF_OLDATTR8 ,
4000 l_detail_blocks(indx).DFF_OLDATTR9 ,
4001 l_detail_blocks(indx).DFF_OLDATTR10 ,
4002 l_detail_blocks(indx).DFF_OLDATTR11 ,
4003 l_detail_blocks(indx).DFF_OLDATTR12 ,
4004 l_detail_blocks(indx).DFF_OLDATTR13 ,
4005 l_detail_blocks(indx).DFF_OLDATTR14 ,
4006 l_detail_blocks(indx).DFF_OLDATTR15 ,
4007 l_detail_blocks(indx).DFF_OLDATTR16 ,
4008 l_detail_blocks(indx).DFF_OLDATTR17 ,
4009 l_detail_blocks(indx).DFF_OLDATTR18 ,
4010 l_detail_blocks(indx).DFF_OLDATTR19 ,
4011 l_detail_blocks(indx).DFF_OLDATTR20 ,
4012 l_detail_blocks(indx).DFF_OLDATTR21 ,
4013 l_detail_blocks(indx).DFF_OLDATTR22 ,
4014 l_detail_blocks(indx).DFF_OLDATTR23 ,
4015 l_detail_blocks(indx).DFF_OLDATTR24 ,
4016 l_detail_blocks(indx).DFF_OLDATTR25 ,
4017 l_detail_blocks(indx).DFF_OLDATTR26 ,
4018 l_detail_blocks(indx).DFF_OLDATTR27 ,
4019 l_detail_blocks(indx).DFF_OLDATTR28 ,
4020 l_detail_blocks(indx).DFF_OLDATTR29 ,
4021 l_detail_blocks(indx).DFF_OLDATTR30 ,
4022 l_detail_blocks(indx).DFF_OLDCATG ,
4023 l_detail_blocks(indx).CHANGE_REASON ,
4024 l_detail_blocks(indx).CHANGE_COMMENT ,
4025 l_detail_blocks(indx).LATE_REASON ,
4026 l_detail_blocks(indx).LATE_COMMENT ,
4027 l_detail_blocks(indx).LATE_CHANGE ,
4028 l_detail_blocks(indx).DESC_FLEX ,
4029 l_detail_blocks(indx).ATTRIBUTE_CATEGORY ,
4030 l_detail_blocks(indx).AUDIT_DATETIME ,
4031 l_detail_blocks(indx).AUDIT_HISTORY ,
4032 l_detail_blocks(indx).DISP_INDEX ,
4033 l_detail_blocks(indx).OLD_CHANGE_REASON ,
4034 l_detail_blocks(indx).OLD_CHANGE_COMMENT ,
4035 l_detail_blocks(indx).OLD_LATE_REASON ,
4036 l_detail_blocks(indx).OLD_LATE_COMMENT ,
4037 l_detail_blocks(indx).OLD_AUDIT_HISTORY ,
4038 l_detail_blocks(indx).OLD_LATE_CHANGE ,
4039 l_detail_blocks(indx).OLD_AUDIT_DATETIME );
4040 end loop;
4041 END if;
4042 /* end of fix for 5398144 */
4043 end if;
4044 hxc_timekeeper_utilities.g_hxc_tk_detail_temp_tab:=l_detail_blocks;
4045 end;
4046
4047
4048 FUNCTION get_exp_type_from_alias (
4049 p_alias_value_id IN VARCHAR2
4050 )
4051 RETURN VARCHAR2 IS
4052 CURSOR c_alias_to_pa_info (
4053 p_id IN hxc_alias_values.alias_value_id%TYPE
4054 ) IS
4055 SELECT attribute2
4056 FROM hxc_alias_values
4057 WHERE alias_value_id = p_id AND attribute_category = 'ELEMENTS_EXPENDITURE_SLF';
4058
4059 l_expenditure_type VARCHAR2 (250);
4060 BEGIN
4061 OPEN c_alias_to_pa_info (p_alias_value_id);
4062 FETCH c_alias_to_pa_info INTO l_expenditure_type;
4063 CLOSE c_alias_to_pa_info;
4064 RETURN l_expenditure_type;
4065 END get_exp_type_from_alias;
4066
4067 FUNCTION check_global_context (
4068 p_context_prefix IN VARCHAR2
4069 )
4070 RETURN BOOLEAN IS
4071 l_dummy VARCHAR2 (10);
4072 BEGIN
4073 SELECT 'Y'
4074 INTO l_dummy
4075 FROM fnd_descr_flex_contexts
4076 WHERE application_id = 809
4077 AND descriptive_flexfield_name = 'OTC Information Types'
4078 AND descriptive_flex_context_code LIKE p_context_prefix || '%GLOBAL%';
4079
4080 RETURN TRUE;
4081 EXCEPTION
4082 WHEN NO_DATA_FOUND THEN
4083 RETURN FALSE;
4084 END;
4085 END hxc_timekeeper_utilities;