1 Package Body hxc_elp_utils as
2 /* $Header: hxcelputl.pkb 120.4.12000000.2 2007/03/27 12:52:17 anuthi noship $ */
3
4 -- global package data type and variables
5 g_debug boolean :=hr_utility.debug_enabled;
6 -- public procedure
7
8
9 -- ksethi start new proc for appliation set updation
10
11 PROCEDURE set_time_bb_appl_set_id
12 (P_TIME_BUILDING_BLOCKS IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
13 ,P_TIME_ATTRIBUTES IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
14 ,P_MESSAGES IN OUT NOCOPY hxc_self_service_time_deposit.message_table
15 ,P_PTE_TERG_ID IN number
16 ,P_APPLICATION_SET_ID IN number
17 )
18 IS
19
20 app_set_ok varchar2(1);
21 time_rec_present varchar2(1);
22 time_rec_transfer varchar2(1);
23 l_null_application_set_id varchar2(1);
24 l_next_blk_id NUMBER(15);
25 l_next_attr_id NUMBER(15);
26 l_next_blk_ovn NUMBER(15);
27
28 -- 2905369 start
29 l_rev_application_set_id varchar2(1);
30 -- 2905369 end
31
32 TYPE v_app_set IS RECORD (
33 application_set_id NUMBER (15));
34
35 TYPE r_app_set IS TABLE OF v_app_set
36 INDEX BY BINARY_INTEGER;
37
38 valid_app_set r_app_set;
39 temp_app_set r_app_set;
40 temp_app_set_drops r_app_set;
41
42 TYPE cac_app_set IS RECORD (
43 application_set_id NUMBER (15),
44 rec_totals NUMBER (15));
45
46 TYPE c_app_set IS TABLE OF cac_app_set
47 INDEX BY BINARY_INTEGER;
48
49 cached_app_set c_app_set;
50
51 TYPE cac_tm_set IS RECORD (
52 application_set_id NUMBER (15),
53 time_recipient_id NUMBER (15));
54
55 TYPE c_tm_set IS TABLE OF cac_tm_set
56 INDEX BY BINARY_INTEGER;
57
58 cached_tm_set c_tm_set;
59
60
61 TYPE v_tim_rec IS RECORD (
62 time_recipient_id NUMBER (15));
63
64 TYPE r_tim_rec IS TABLE OF v_tim_rec
65 INDEX BY BINARY_INTEGER;
66
67 list_tim_rec_day r_tim_rec;
68 list_tim_rec_det r_tim_rec;
69 list_tim_rec_tc r_tim_rec;
70 list_tim_rec_drops r_tim_rec;
71
72 l_index BINARY_INTEGER;
73 l_index_tc BINARY_INTEGER;
74 l_index_day BINARY_INTEGER;
75 l_index_det BINARY_INTEGER;
76 l_index_mess BINARY_INTEGER;
77 l_ind_tim_rec_day BINARY_INTEGER;
78 l_ind_tim_rec_det BINARY_INTEGER;
79 l_ind_tim_rec_tc BINARY_INTEGER;
80 l_index_tmp_app BINARY_INTEGER;
81 l_index_tmp_rec BINARY_INTEGER;
85 l_last_index BINARY_INTEGER;
82 l_index_tim_rec_drops BINARY_INTEGER;
83 l_index_drops BINARY_INTEGER;
84 l_index_attr BINARY_INTEGER;
86 l_all_days_entered varchar2(10) := 'Y';
87
88 CURSOR csr_tm_sets
89 IS
90 SELECT DISTINCT application_set_id, time_recipient_id
91 FROM hxc_application_set_comps_v
92 ORDER BY application_set_id;
93
94 CURSOR csr_app_set_id (p_app_set_id NUMBER)
95 IS
96 SELECT DISTINCT (application_set_id) a
97 FROM hxc_application_set_comps_v
98 WHERE time_recipient_id IN
99 (SELECT DISTINCT (time_recipient_id)
100 FROM hxc_application_set_comps_v
101 WHERE application_set_id =
102 p_app_set_id)
103 AND application_set_id NOT IN
104 (SELECT application_set_id a
105 FROM hxc_application_set_comps_v
106 WHERE time_recipient_id NOT IN
107 (SELECT DISTINCT (time_recipient_id)
108 FROM hxc_application_set_comps_v
109 WHERE application_set_id =
110 p_app_set_id));
111
112 CURSOR cache_app_set
113 IS
114 SELECT application_set_id a, COUNT (*) cnt
115 FROM hxc_application_set_comps_v
116 GROUP BY application_set_id;
117
118 CURSOR tm_rec_transfer(p_tbb_id NUMBER, p_tbb_ovn NUMBER, p_time_rec_id NUMBER)
119 IS
120 SELECT 'Y' status
121 FROM sys.DUAL
122 WHERE EXISTS ( SELECT htd.time_building_block_id, htd.time_building_block_ovn
123 FROM hxc_transaction_details htd,
124 hxc_transactions ht,
125 hxc_retrieval_processes hrp
126 WHERE ht.transaction_id = htd.transaction_id
127 AND ht.TYPE = 'RETRIEVAL'
128 AND ht.status = 'SUCCESS'
129 AND htd.status = 'SUCCESS'
130 AND ht.transaction_process_id = hrp.retrieval_process_id
131 AND hrp.time_recipient_id = p_time_rec_id
132 AND htd.time_building_block_id = p_tbb_id
133 AND htd.time_building_block_ovn = p_tbb_ovn);
134
135 CURSOR c_get_dropped_attribs(p_tbb_id NUMBER, p_tbb_ovn NUMBER)
136 IS
137 SELECT hta.attribute_category attribute_category, hta.attribute1 attribute1,
138 hta.attribute2 attribute2, hta.attribute3 attribute3,
139 hta.attribute4 attribute4, hta.attribute5 attribute5,
140 hta.attribute6 attribute6, hta.attribute7 attribute7,
141 hta.attribute8 attribute8, hta.attribute9 attribute9,
142 hta.attribute10 attribute10, hta.attribute11 attribute11,
143 hta.attribute12 attribute12, hta.attribute13 attribute13,
144 hta.attribute14 attribute14, hta.attribute15 attribute15,
145 hta.attribute16 attribute16, hta.attribute17 attribute17,
146 hta.attribute18 attribute18, hta.attribute19 attribute19,
147 hta.attribute20 attribute20, hta.attribute21 attribute21,
148 hta.attribute22 attribute22, hta.attribute23 attribute23,
149 hta.attribute24 attribute24, hta.attribute25 attribute25,
150 hta.attribute26 attribute26, hta.attribute27 attribute27,
151 hta.attribute28 attribute28, hta.attribute29 attribute29,
152 hta.attribute30 attribute30,
153 hta.bld_blk_info_type_id bld_blk_info_type_id,
154 hbbi.bld_blk_info_type bld_blk_info_type, hta.time_attribute_id
155 FROM hxc_time_attributes hta,
156 hxc_time_attribute_usages htau,
157 hxc_bld_blk_info_types hbbi
158 WHERE hta.time_attribute_id IN (SELECT hau.time_attribute_id
159 FROM hxc_time_attribute_usages hau
160 WHERE hau.time_building_block_id = p_tbb_id
161 AND hau.time_building_block_ovn =
162 p_tbb_ovn)
163 AND htau.time_attribute_id = hta.time_attribute_id
164 AND hbbi.bld_blk_info_type_id = hta.bld_blk_info_type_id
165 AND hta.attribute_category NOT LIKE ('SECURITY')
166 ORDER BY hta.time_attribute_id ;
167
168
169 l_proc VARCHAR2 (30);
170 PROCEDURE create_reverse_entry(P_TIME_BUILDING_BLOCKS IN HXC_BLOCK_TABLE_TYPE,
171 P_TIME_ATTRIBUTES IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
172 P_INDEX_BB IN BINARY_INTEGER) AS
173 l_in_attr_st binary_integer;
174 l_proc VARCHAR2 (30);
175 BEGIN
176
177 g_debug:=hr_utility.debug_enabled;
178 if g_debug then
179 l_proc := 'create_reverse_entry';
180 hr_utility.set_location('Processing '||l_proc, 10);
181 END if;
182 FOR c_get_att in c_get_dropped_attribs(P_TIME_BUILDING_BLOCKS (p_index_bb).TIME_BUILDING_BLOCK_ID,
183 P_TIME_BUILDING_BLOCKS (p_index_bb).OBJECT_VERSION_NUMBER)
184 LOOP
185 l_in_attr_st := null;
186 l_in_attr_st := P_TIME_ATTRIBUTES.first;
187 while l_in_attr_st is not null
188 LOOP
189 if P_TIME_ATTRIBUTES(l_in_attr_st).time_attribute_id=c_get_att.time_attribute_id then
190 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE_CATEGORY := c_get_att.ATTRIBUTE_CATEGORY;
191 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE1 := c_get_att.ATTRIBUTE1;
192 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE2 := c_get_att.ATTRIBUTE2;
193 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE3 := c_get_att.ATTRIBUTE3;
194 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE4 := c_get_att.ATTRIBUTE4;
195 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE5 := c_get_att.ATTRIBUTE5;
196 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE6 := c_get_att.ATTRIBUTE6;
197 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE7 := c_get_att.ATTRIBUTE7;
198 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE8 := c_get_att.ATTRIBUTE8;
199 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE9 := c_get_att.ATTRIBUTE9;
200 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE10 := c_get_att.ATTRIBUTE10;
201 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE11 := c_get_att.ATTRIBUTE11;
202 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE12 := c_get_att.ATTRIBUTE12;
203 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE13 := c_get_att.ATTRIBUTE13;
204 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE14 := c_get_att.ATTRIBUTE14;
208 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE18 := c_get_att.ATTRIBUTE18;
205 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE15 := c_get_att.ATTRIBUTE15;
206 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE16 := c_get_att.ATTRIBUTE16;
207 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE17 := c_get_att.ATTRIBUTE17;
209 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE19 := c_get_att.ATTRIBUTE19;
210 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE20 := c_get_att.ATTRIBUTE20;
211 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE21 := c_get_att.ATTRIBUTE21;
212 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE22 := c_get_att.ATTRIBUTE22;
213 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE23 := c_get_att.ATTRIBUTE23;
214 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE24 := c_get_att.ATTRIBUTE24;
215 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE25 := c_get_att.ATTRIBUTE25;
216 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE26 := c_get_att.ATTRIBUTE26;
217 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE27 := c_get_att.ATTRIBUTE27;
218 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE28 := c_get_att.ATTRIBUTE28;
219 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE29 := c_get_att.ATTRIBUTE29;
220 P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE30 := c_get_att.ATTRIBUTE30;
221 IF g_debug then
222 hr_utility.trace('-----------------------------');
223 hr_utility.trace('| ATTRIB_ID |TBB ID | BLD_BLK_INFO_TYPE| ATTRIBUTE_CATEGORY | A1 | A2 | A3| TBB_ID| NEW| CHANGED|');
224 hr_utility.trace('-----------------------------');
225 hr_utility.trace('| '||P_TIME_ATTRIBUTES(l_in_attr_st).TIME_ATTRIBUTE_ID
226 ||' | '||P_TIME_ATTRIBUTES(l_in_attr_st).BUILDING_BLOCK_ID
227 ||' | '||P_TIME_ATTRIBUTES(l_in_attr_st).BLD_BLK_INFO_TYPE
228 ||' | '||P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE_CATEGORY
229 ||' | '||P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE1
230 ||' | '||P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE2
231 ||' | '||P_TIME_ATTRIBUTES(l_in_attr_st).ATTRIBUTE3
232 || ' | '||P_TIME_ATTRIBUTES(l_in_attr_st).BUILDING_BLOCK_ID
233 ||' | '||P_TIME_ATTRIBUTES(l_in_attr_st).NEW||' | '
234 ||P_TIME_ATTRIBUTES(l_in_attr_st).CHANGED);
235 END if;
236 end if;
237 l_in_attr_st := P_TIME_ATTRIBUTES.NEXT(l_in_attr_st);
238 end loop;
239
240 END LOOP;
241 if g_debug then
242 hr_utility.set_location('Leaving '||l_proc, 20);
243 END if;
244 END create_reverse_entry;
245
246 BEGIN
247 -- Check if Entry Level Processing has been set or not
248 g_debug:=hr_utility.debug_enabled;
249 if g_debug then
250 l_proc := 'set_time_bb_appl_set_id';
251 hr_utility.set_location('Processing '||l_proc, 10);
252 end if;
253 --
254 -- Issue a SavePoint
255 savepoint set_time_bb_appl_set;
256 --
257 --if g_debug then
258 -- hr_utility.trace('Entered..........'||p_pte_terg_id);
259 --end if;
260 if (p_pte_terg_id is not null) then
261 /*
262 -- ******************START TRACE SECTION***************************************
263 --
264 if g_debug then
265 hr_utility.trace('*******BEFORE PROCESSING******');
266 hr_utility.trace('-----------------------------');
267 hr_utility.trace('| TBB_ID | APPL_SET_ID |SCOPE | OVN| NEW| CHANGED|');
268 hr_utility.trace('-----------------------------');
269 for x IN P_TIME_BUILDING_BLOCKS.first .. P_TIME_BUILDING_BLOCKS.last
270 loop
271 hr_utility.trace('| '||P_TIME_BUILDING_BLOCKS(x).time_building_block_id||
272 ' | '||P_TIME_BUILDING_BLOCKS(x).application_set_id||
273 ' | '||p_time_building_blocks(x).SCOPE||' | '||
274 P_TIME_BUILDING_BLOCKS (x).object_version_number||' | '||
275 P_TIME_BUILDING_BLOCKS(x).NEW||' | '||
276 P_TIME_BUILDING_BLOCKS(x).CHANGED);
277 end loop;
278
279
280 hr_utility.trace('-----------------------------');
281 hr_utility.trace('| ATTRIB_ID |TBB ID | BLD_BLK_INFO_TYPE| ATTRIBUTE_CATEGORY | TBB_ID| NEW| CHANGED|');
282 hr_utility.trace('-----------------------------');
283 l_index := null;
284 l_index := P_TIME_ATTRIBUTES.first;
285 while l_index is not null
286 loop
287
288 hr_utility.trace('| '||P_TIME_ATTRIBUTES(l_index).TIME_ATTRIBUTE_ID
289 ||' | '||P_TIME_ATTRIBUTES(l_index).BUILDING_BLOCK_ID
290 ||' | '||P_TIME_ATTRIBUTES(l_index).BLD_BLK_INFO_TYPE
291 ||' | '||P_TIME_ATTRIBUTES(l_index).ATTRIBUTE_CATEGORY
292 || ' | '||P_TIME_ATTRIBUTES(l_index).BUILDING_BLOCK_ID
293 ||' | '||P_TIME_ATTRIBUTES(l_index).NEW||' | '
294 ||P_TIME_ATTRIBUTES(l_index).CHANGED);
295 l_index := P_TIME_ATTRIBUTES.NEXT(l_index);
296 end loop;
297
298
299 hr_utility.trace('-----------------------------');
300 hr_utility.trace('| MESSAGE_LEVEL | message_name| TBB_OVN | TBB_ID');
301 hr_utility.trace('-----------------------------');
302 for x IN P_MESSAGES.first .. P_MESSAGES.last
303 loop
304 hr_utility.trace('| '||P_MESSAGES(x).MESSAGE_LEVEL||' | '
305 ||P_messages(x).message_name||' | '
306 ||p_messages(x).time_building_block_ovn|| ' | '
307 ||p_messages(x).time_building_block_id);
308 end loop;
309 end if;
310 -- ************* END TRACE SECTION*************************************************
311 */
312
313 -- do processing of message table
314 -- to derive and set the application set id along with the TBB
315 --
316 -- First cache all valid Appl Sets that can be set
317 -- using a temp table, here to have better performance
318 --
319 l_index := 1;
320 --
321 --if g_debug then
322 -- hr_utility.trace('p_application_set_id'||p_application_set_id);
323 --end if;
324 --
325 FOR appl_set IN csr_app_set_id(p_application_set_id) LOOP
326
327 --
328 -- Get all the sub sets of the
332 --
329 -- current application set attached to
330 -- the resource preference, and use them to populate the
331 -- temporary table 'valid_app_set'
333
334 valid_app_set(l_index).application_set_id := appl_set.A;
335
336 l_index := l_index+1;
337 END LOOP;
338
339 -- Here cache the application sets with the count of Time recipients
340 -- attached to them and populate the
341 -- temporary table 'cached_app_set'
342 -- which will be used for all further processing
343 --
344 l_index := 0;
345
346 FOR appl_set IN cache_app_set
347 LOOP
348 --
349 cached_app_set (l_index).application_set_id := appl_set.a;
350 cached_app_set (l_index).rec_totals := appl_set.cnt;
351 l_index := l_index
352 + 1;
353 END LOOP; -- appl_set IN cache_app_set
354
355
356 -- Here cache the application sets with the Time recipients
357 -- attached to them and populate the
358 -- temporary table 'cached_tm_set'
359 -- which will be used for all further processing
360 --
361 l_index := 0;
362
363 FOR tm_rec IN csr_tm_sets
364 LOOP
365 --
366 cached_tm_set (l_index).application_set_id :=
367 tm_rec.application_set_id;
368 cached_tm_set (l_index).time_recipient_id :=
369 tm_rec.time_recipient_id;
370 l_index := l_index
371 + 1;
372 END LOOP; -- appl_set IN cache_app_set
373
374 -- if g_debug then
375 --hr_utility.trace('cached_tm_set.COUNT'||cached_tm_set.COUNT);
376 -- end if;
377 --
378
379 -- here starts the real processing
380
381 -- Initialize to read the global timecard object
382 --
383 l_index_tc := NULL;
384 l_index_tc := P_TIME_BUILDING_BLOCKS.FIRST;
385
386 WHILE l_index_tc IS NOT NULL
387 LOOP
388 IF (P_TIME_BUILDING_BLOCKS (l_index_tc).scope = 'TIMECARD')
389 THEN
390
391 -- Initialize the Temp Time Recipient table
392 -- for SCOPE = 'TIMECARD' to null.
393 --
394 IF (list_tim_rec_tc.COUNT > 0)
395 THEN
396 list_tim_rec_tc.DELETE;
397 END IF;
398
399 l_index_day := NULL;
400 l_index_day := P_TIME_BUILDING_BLOCKS.FIRST;
401
402 WHILE l_index_day IS NOT NULL
403 LOOP
404 IF ( P_TIME_BUILDING_BLOCKS (l_index_day).scope = 'DAY'
405 AND P_TIME_BUILDING_BLOCKS (l_index_day).parent_building_block_id =
406 P_TIME_BUILDING_BLOCKS (l_index_tc).time_building_block_id
407 AND P_TIME_BUILDING_BLOCKS (l_index_day).parent_building_block_ovn =
408 P_TIME_BUILDING_BLOCKS (l_index_tc).object_version_number
409 )
410 THEN
411
412 -- Initialize the Temp Time Recipient table
413 -- for SCOPE = 'DAY' to null.
414 --
415
416 IF (list_tim_rec_day.COUNT > 0)
417 THEN
418 list_tim_rec_day.DELETE;
419 END IF;
420
421 l_index_det := NULL;
422 l_index_det := P_TIME_BUILDING_BLOCKS.FIRST;
423
424
425 -- Check for existing DETIALS for the current DAY
426 WHILE l_index_det IS NOT NULL
427 LOOP
428 IF ( P_TIME_BUILDING_BLOCKS (l_index_det).scope = 'DETAIL'
429 AND P_TIME_BUILDING_BLOCKS (l_index_det).parent_building_block_id =
430 P_TIME_BUILDING_BLOCKS (l_index_day).time_building_block_id
431 AND P_TIME_BUILDING_BLOCKS (l_index_det).parent_building_block_ovn =
432 P_TIME_BUILDING_BLOCKS (l_index_day).object_version_number
433 AND P_TIME_BUILDING_BLOCKS (l_index_det).DATE_TO = fnd_date.date_to_canonical(hr_general.end_of_time)
434 AND P_TIME_BUILDING_BLOCKS (l_index_det).OBJECT_VERSION_NUMBER <> -9999
435 )
436 THEN
437
438 -- Initialize the Temp Time Recipient table
439 -- for SCOPE = 'DETAIL' to null.
440 --
441
442 IF (list_tim_rec_det.COUNT > 0)
443 THEN
444 list_tim_rec_det.DELETE;
445 END IF;
446
447
448 -- Initialize the Temp Time Recipient drops table
449 -- for SCOPE = 'DETAIL' to null.
450 --
451 IF (list_tim_rec_drops.COUNT > 0)
452 THEN
453 list_tim_rec_drops.DELETE;
454 END IF;
455
456
457 -- Start processing the message table to get the
458 -- Time recipient for the DETAIL BB_ID
459
460 l_index_mess := NULL;
461 l_index_mess := p_messages.FIRST;
462
463 WHILE l_index_mess IS NOT NULL
464 LOOP
465
466 IF ( p_messages (l_index_mess).message_level =
467 'PTE'
468 AND p_messages (l_index_mess).time_building_block_id =
469 P_TIME_BUILDING_BLOCKS (l_index_det).time_building_block_id
470 AND (p_messages (l_index_mess).time_building_block_ovn - 1) =
471 P_TIME_BUILDING_BLOCKS (l_index_det).object_version_number
472 )
473 THEN
474
475
476 -- populating for SCOPE = DETAIL
477
478 time_rec_present := 'N';
479
480 IF (list_tim_rec_det.COUNT > 0)
481 THEN
482 FOR x IN
483 list_tim_rec_det.FIRST .. list_tim_rec_det.LAST
484 LOOP
485 IF (p_messages (l_index_mess).message_name =
486 list_tim_rec_det (x).time_recipient_id
487 )
488 THEN
489 time_rec_present := 'Y';
490 END IF; -- check if Time Recipient is already present
491 END LOOP; -- x IN list_tim_rec_det
492
493 -- get the index to point to the last record, in case we need
494 -- to insert
495 l_ind_tim_rec_det :=
496 list_tim_rec_det.LAST
497 + 1;
501
498 ELSE -- no records in list_tim_rec_day
499 l_ind_tim_rec_det := 0;
500 END IF; -- list_tim_rec_det.COUNT > 0
502 IF (time_rec_present = 'N')
503 THEN
504
505 list_tim_rec_det (l_ind_tim_rec_det).time_recipient_id :=
506 p_messages (l_index_mess).message_name;
507 END IF; -- time_rec_present = 'N'
508
509 -- end population for SCOPE = DETAIL
510
511 -- Now, populate the time recipinets table for DAY n TIMECARD
512 -- first check if id is already present in the table or not
513
514 -- populating for SCOPE = DAY
515
516 time_rec_present := 'N';
517
518 IF (list_tim_rec_day.COUNT > 0)
519 THEN
520 FOR x IN
521 list_tim_rec_day.FIRST .. list_tim_rec_day.LAST
522 LOOP
523 IF (p_messages (l_index_mess).message_name =
524 list_tim_rec_day (x).time_recipient_id
525 )
526 THEN
527 time_rec_present := 'Y';
528 END IF; -- check if Time Recipient is already present
529 END LOOP; -- x IN list_tim_rec_day
530
531 -- get the index to point to the last record, in case we need
532 -- to insert
533 l_ind_tim_rec_day :=
534 list_tim_rec_day.LAST
535 + 1;
536 ELSE -- no records in list_tim_rec_day
537 l_ind_tim_rec_day := 0;
538 END IF; -- list_tim_rec_day.COUNT > 0
539
540 IF (time_rec_present = 'N')
541 THEN
542 list_tim_rec_day (l_ind_tim_rec_day).time_recipient_id :=
543 p_messages (l_index_mess).message_name;
544 END IF; -- time_rec_present = 'N'
545
546 -- end population for SCOPE = DAY
547
548 -- populating for SCOPE = TIMECARD
549
550 time_rec_present := 'N';
551
552 IF (list_tim_rec_tc.COUNT > 0)
553 THEN
554 FOR x IN
555 list_tim_rec_tc.FIRST .. list_tim_rec_tc.LAST
556 LOOP
557 IF (p_messages (l_index_mess).message_name =
558 list_tim_rec_tc (x).time_recipient_id
559 )
560 THEN
561 time_rec_present := 'Y';
562 END IF; -- check if Time Recipient is already present
563 END LOOP; -- x IN list_tim_rec_day
564
565 -- get the index to point to the last record, in case we need
566 -- to insert
567 l_ind_tim_rec_tc :=
568 list_tim_rec_tc.LAST
569 + 1;
570 ELSE -- no records in list_tim_rec_day
571 l_ind_tim_rec_tc := 0;
572 END IF; -- list_tim_rec_tc.COUNT > 0
573
574 IF (time_rec_present = 'N')
575 THEN
576 list_tim_rec_tc (l_ind_tim_rec_tc).time_recipient_id :=
577 p_messages (l_index_mess).message_name;
578 END IF; -- time_rec_present = 'N'
579
580 -- end population for SCOPE = TIMECARD
581
582
583 END IF; -- Message Table level = PTE and match for TBB_ID
584
585 l_index_mess := p_messages.NEXT (l_index_mess);
586 END LOOP; -- l_index_mess is not null
587
588
589 -- Start DETAIL's Time Recipient table processing
590 -- in order to set the DETAIL SCOPE Application Set ID
591 --
592
593 -- Initialize a new temproary table to
594 -- derive the application set id :-)
595 -- Populate it using the cached Application Set Table
596 -- i.e. table 'cached_app_set'
597
598 IF (temp_app_set.COUNT > 0)
599 THEN
600 temp_app_set.DELETE;
601 END IF;
602
603 l_index := 0;
604
605 l_index_tmp_app := NULL;
606 l_index_tmp_app := cached_app_set.FIRST;
607
608 WHILE l_index_tmp_app IS NOT NULL
609 LOOP
610
611 IF (cached_app_set (l_index_tmp_app).rec_totals =
612 list_tim_rec_det.COUNT
613 )
614 THEN
615 temp_app_set (l_index).application_set_id :=
616 cached_app_set (l_index_tmp_app).application_set_id;
617 l_index := l_index
618 + 1;
619 END IF;
620
621 l_index_tmp_app :=
622 cached_app_set.NEXT (l_index_tmp_app);
623 END LOOP; -- l_index_tmp_app IS NOT NULL
624
625
626 -- Now check for individual Time Recipients
627
628 l_index_tmp_app := NULL;
629 l_index_tmp_app := temp_app_set.FIRST;
630
631 WHILE l_index_tmp_app IS NOT NULL
632 LOOP
633 app_set_ok := 'Y';
634
635 l_index_tmp_rec := NULL;
636 l_index_tmp_rec := cached_tm_set.FIRST;
637
638
639 WHILE l_index_tmp_rec IS NOT NULL
640
641 LOOP
642
643 IF (cached_tm_set (l_index_tmp_rec).application_set_id =
644 temp_app_set (l_index_tmp_app).application_set_id
645 )
646 THEN
647 time_rec_present := 'N';
648
649 --
650
651 FOR x IN
652 list_tim_rec_det.FIRST .. list_tim_rec_det.LAST
653 LOOP
654
655 IF (cached_tm_set (l_index_tmp_rec).time_recipient_id =
656 list_tim_rec_det (x).time_recipient_id
657 )
658 THEN
659 time_rec_present := 'Y';
660 END IF;
661 END LOOP; -- x IN temp_app_set
662
663 IF (time_rec_present = 'N')
664 THEN
665 app_set_ok := 'N';
666 END IF;
667 END IF; -- match for same appl set in both tables as same
668
669 l_index_tmp_rec :=
670 cached_tm_set.NEXT (l_index_tmp_rec);
671 END LOOP; -- l_index_tmp_rec is not null
672
673 -- Check flag to delete or not to delete
677 END IF;
674 IF (app_set_ok = 'N')
675 THEN
676 temp_app_set.DELETE (l_index_tmp_app);
678
679 l_index_tmp_app :=
680 temp_app_set.NEXT (l_index_tmp_app);
681 END LOOP; -- l_index_tmp_app is not null
682
683
684 l_index := NULL;
685 l_index := temp_app_set.FIRST;
686 -- 115.6 start
687
688 l_null_application_set_id := 'N';
689 if (temp_app_set.COUNT = 0)
690 then
691 l_null_application_set_id := 'Y';
692 l_index := 0;
693 temp_app_set (l_index).application_set_id := -999999;
694 -- if g_debug then
695 --hr_utility.trace('ks ...nulled and temp_app_set (l_index).application_set_id = '||temp_app_set (l_index).application_set_id);
696 -- end if;
697 end if;
698
699 -- 115.6 end
700 -- Check to see if the application set id that is
701 -- to be set is a subset of the main Preference level
702 -- then only update
703 -- here check that this falls in the temp appl set ids table
704
705 app_set_ok := 'N';
706
707
708 FOR x IN valid_app_set.FIRST .. valid_app_set.LAST
709 LOOP
710 -- 115.6
711 IF(l_null_application_set_id = 'N') then
712 IF (temp_app_set (l_index).application_set_id =
713 valid_app_set (x).application_set_id
714 )
715 THEN
716 app_set_ok := 'Y';
717 END IF;
718 END IF; -- l_null_application_set_id = 'N'
719 END LOOP;
720 -- 115.6 start
721 IF(l_null_application_set_id = 'Y') then
722 app_set_ok := 'Y';
723 END IF;
724 -- 115.6 end
725
726 -- if application set Id is not in the temp
727 -- table of application set Ids then
728 -- raise an error as the time recipient is not contained in
729 -- the application set id attached to the resource preference
730 IF (app_set_ok = 'N')
731 THEN
732 -- 115.6
733 hxc_time_entry_rules_utils_pkg.add_error_to_table (
734 p_message_table => p_messages
735 , p_message_name => 'HXC_VLD_ELP_VIOLATION'
736 , p_message_token => NULL
737 , p_message_level => 'ERROR'
738 , p_message_field => NULL
739 , p_application_short_name => 'HXC'
740 , p_timecard_bb_id => null
741 , p_time_attribute_id => NULL
742 , p_timecard_bb_ovn => null
743 , p_time_attribute_ovn => NULL );
744
745 ELSE -- update the application set id on the TBB
746 -- here one more check has to be made for checkin if
747 -- already some application set id is present or not
748 -- in which case all time transfer etc needs to be
749 -- checked, postponing that check for now, will add laterz
750
751
752 -- adding check...
753 IF(P_TIME_BUILDING_BLOCKS (l_index_det).application_set_id is not null
754 AND P_TIME_BUILDING_BLOCKS (l_index_det).application_set_id <>
755 temp_app_set (l_index).application_set_id)
756 Then
757 -- Initialize the Temp Time Recipient drops table
758 -- for SCOPE = 'DETAIL' to null.
759 --
760 IF (list_tim_rec_drops.COUNT > 0)
761 THEN
762 list_tim_rec_drops.DELETE;
763 END IF;
764
765 l_index_tim_rec_drops := 0;
766
767 l_index_tmp_rec := NULL;
768 l_index_tmp_rec := cached_tm_set.FIRST;
769 --
770 --
771 WHILE l_index_tmp_rec IS NOT NULL
772 --
773 LOOP
774 IF (cached_tm_set (l_index_tmp_rec).application_set_id =
775 P_TIME_BUILDING_BLOCKS (l_index_det).application_set_id
776 )
777 THEN
778 time_rec_present := 'N';
779 --
780 --
781 -- 115.6
782 l_index := null;
783 l_index := list_tim_rec_det.FIRST;
784 While l_index IS NOT NULL
785 LOOP
786 IF (cached_tm_set (l_index_tmp_rec).time_recipient_id =
787 list_tim_rec_det (l_index).time_recipient_id
788 )
789 THEN
790 time_rec_present := 'Y';
791 END IF;
792 l_index := list_tim_rec_det.NEXT (l_index);
793 END LOOP; -- l_index IS NOT NULL
794
795 IF (time_rec_present = 'N')
796 THEN
797 -- if g_debug then
798 --hr_utility.trace(' Yes drops and dropped timerecid ='||cached_tm_set (l_index_tmp_rec).time_recipient_id);
799 -- addding 115.5
800 -- check here first that if time has been transferred to
801 -- this time recipinet.
802 -- hr_utility.trace(' Now to check if time transfer has taken place for this Dropped TimeRec or not....');
803 --end if;
804 --
805 time_rec_transfer := null;
806 --
807 OPEN tm_rec_transfer(P_TIME_BUILDING_BLOCKS (l_index_det).time_building_block_id,P_TIME_BUILDING_BLOCKS (l_index_det).object_version_number,cached_tm_set (l_index_tmp_rec).time_recipient_id);
808 FETCH tm_rec_transfer into time_rec_transfer;
809 CLOSE tm_rec_transfer;
810 IF( 'Y' = time_rec_transfer)
811 THEN
812 -- if g_debug then
813 -- hr_utility.trace(' Yes there is a drop n the time transfer has taken palce.... and recp id = '||cached_tm_set (l_index_tmp_rec).time_recipient_id);
814 -- end if;
815
816 list_tim_rec_drops(l_index_tim_rec_drops).time_recipient_id :=
817 cached_tm_set (l_index_tmp_rec).time_recipient_id;
818 l_index_tim_rec_drops := l_index_tim_rec_drops +1;
819
820 -- Also add the dropped timce recipient to the DAY n TIMECARD Scopes Tables
821
822 -- populating for SCOPE = DAY when drop is occured
823 time_rec_present := 'N';
824
825 IF (list_tim_rec_day.COUNT > 0)
826 THEN
827 FOR x IN
831 list_tim_rec_day (x).time_recipient_id
828 list_tim_rec_day.FIRST .. list_tim_rec_day.LAST
829 LOOP
830 IF (cached_tm_set (l_index_tmp_rec).time_recipient_id =
832 )
833 THEN
834 -- if g_debug then
835 -- hr_utility.trace (
836 -- ' yes TR present in DAY'
837 -- );
838 -- end if;
839 time_rec_present := 'Y';
840 END IF; -- check if Time Recipient is already present
841 END LOOP; -- x IN list_tim_rec_day
842
843 -- get the index to point to the last record, in case we need
844 -- to insert
845 l_ind_tim_rec_day :=
846 list_tim_rec_day.LAST
847 + 1;
848 ELSE -- no records in list_tim_rec_day
849 l_ind_tim_rec_day := 0;
850 END IF; -- list_tim_rec_day.COUNT > 0
851
852 IF (time_rec_present = 'N')
853 THEN
854 -- if g_debug then
855 --hr_utility.trace ('poop');
856 -- end if;
857 list_tim_rec_day (l_ind_tim_rec_day).time_recipient_id :=
858 cached_tm_set (l_index_tmp_rec).time_recipient_id;
859 END IF; -- time_rec_present = 'N'
860
861 -- end population for SCOPE = DAY when drop has occured
862
863
864 -- populating for SCOPE = TIMECARD and drop has occured
865
866 time_rec_present := 'N';
867
868 IF (list_tim_rec_tc.COUNT > 0)
869 THEN
870 FOR x IN
871 list_tim_rec_tc.FIRST .. list_tim_rec_tc.LAST
872 LOOP
873 IF (cached_tm_set (l_index_tmp_rec).time_recipient_id =
874 list_tim_rec_tc (x).time_recipient_id
875 )
876 THEN
877 time_rec_present := 'Y';
878 END IF; -- check if Time Recipient is already present
879 END LOOP; -- x IN list_tim_rec_day
880
881 -- get the index to point to the last record, in case we need
882 -- to insert
883 l_ind_tim_rec_tc :=
884 list_tim_rec_tc.LAST
885 + 1;
886 ELSE -- no records in list_tim_rec_day
887 l_ind_tim_rec_tc := 0;
888 END IF; -- list_tim_rec_tc.COUNT > 0
889
890 IF (time_rec_present = 'N')
891 THEN
892 list_tim_rec_tc (l_ind_tim_rec_tc).time_recipient_id :=
893 cached_tm_set (l_index_tmp_rec).time_recipient_id;
894 END IF; -- time_rec_present = 'N'
895
896 -- end population for SCOPE = TIMECARD and drop has occured
897 --
898
899 END IF; -- 'Y' = time_rec_transfer , The cond that this time rec has been transferred.
900 --
901 END IF; -- time_rec_present = 'N'
902 END IF; -- match for same appl set in both tables as same
903 --
904 l_index_tmp_rec :=
905 cached_tm_set.NEXT (l_index_tmp_rec);
906 END LOOP; -- l_index_tmp_rec is not null
907
908 END IF; -- P_TIME_BUILDING_BLOCKS (l_index_det).application_set_id is not null...
909
910 -- IT is here that i check for the drops table
911 -- and add new rows for TBB and ATTribs
912 -- 2905369 adding flag to determine if reversing entry is done or not..
913 l_rev_application_set_id := 'N';
914
915 IF ( list_tim_rec_drops.COUNT > 0 )
916 THEN
917 -- Start DROPS's Time Recipient table processing
918 -- in order to get the Application Set ID
919
920
921 -- Initialize a new temproary table to
922 -- derive the application set id :-)
923 -- Populate it using the cached Application Set Table
924 -- i.e. table 'cached_app_set'
925
926 IF (temp_app_set_drops.COUNT > 0)
927 THEN
928 temp_app_set_drops.DELETE;
929 END IF;
930
931 l_index_drops := 0;
932
933 l_index_tmp_app := NULL;
934 l_index_tmp_app := cached_app_set.FIRST;
935
936 WHILE l_index_tmp_app IS NOT NULL
937 LOOP
938 IF (cached_app_set (l_index_tmp_app).rec_totals =
939 list_tim_rec_drops.COUNT
940 )
941 THEN
942 temp_app_set_drops(l_index_drops).application_set_id :=
943 cached_app_set (l_index_tmp_app).application_set_id;
944 l_index_drops := l_index_drops
945 + 1;
946 END IF;
947
948 l_index_tmp_app := cached_app_set.NEXT (l_index_tmp_app);
949 END LOOP; -- l_index_tmp_app IS NOT NULL
950
951 -- Now check for individual Time Recipients
952
956 WHILE l_index_tmp_app IS NOT NULL
953 l_index_tmp_app := NULL;
954 l_index_tmp_app := temp_app_set_drops.FIRST;
955
957 LOOP
958 app_set_ok := 'Y';
959
960
961 l_index_tmp_rec := NULL;
962 l_index_tmp_rec := cached_tm_set.FIRST;
963
964
965 WHILE l_index_tmp_rec IS NOT NULL
966 LOOP
967 IF (cached_tm_set (l_index_tmp_rec).application_set_id =
968 temp_app_set_drops (l_index_tmp_app).application_set_id
969 )
970 THEN
971 time_rec_present := 'N';
972
973 --
974 FOR x IN
975 list_tim_rec_drops.FIRST .. list_tim_rec_drops.LAST
976 LOOP
977 IF (cached_tm_set (l_index_tmp_rec).time_recipient_id =
978 list_tim_rec_drops(x).time_recipient_id
979 )
980 THEN
981 time_rec_present := 'Y';
982 END IF;
983 END LOOP; -- x IN temp_app_set
984
985 IF (time_rec_present = 'N')
986 THEN
987 app_set_ok := 'N';
988 END IF;
989 END IF; -- match for same appl set in both tables as same
990
991 l_index_tmp_rec :=
992 cached_tm_set.NEXT (l_index_tmp_rec);
993 END LOOP; -- l_index_tmp_rec is not null
994
995 --Check flag to delete or not to delete
996
997 IF (app_set_ok = 'N')
998 THEN
999 temp_app_set_drops.DELETE (l_index_tmp_app);
1000 END IF;
1001
1002 l_index_tmp_app := temp_app_set_drops.NEXT (l_index_tmp_app);
1003 END LOOP; -- l_index_tmp_app is not null
1004
1005 l_index_drops := NULL;
1006 l_index_drops := temp_app_set_drops.FIRST;
1007
1008 -- Check to see if the application set id that is
1009 -- to be set is a subset of the main Preference level
1010 -- then only update
1011 -- here check that this falls in the temp appl set ids table
1012 app_set_ok := 'N';
1013
1014 FOR x IN valid_app_set.FIRST .. valid_app_set.LAST
1015 LOOP
1016 IF (temp_app_set_drops(l_index_drops).application_set_id =
1017 valid_app_set (x).application_set_id
1018 )
1019 THEN
1020 app_set_ok := 'Y';
1021 END IF;
1022 END LOOP;
1023
1024 -- if application set Id is not in the temp
1025 -- table of application set Ids then
1026 -- raise an error as the time recipient is not contained in
1027 -- the application set id attached to the resource preference
1028 IF (app_set_ok = 'N')
1029 THEN
1030 -- 115.6
1031 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1032 p_message_table => p_messages
1033 , p_message_name => 'HXC_VLD_ELP_VIOLATION'
1034 , p_message_token => NULL
1035 , p_message_level => 'ERROR'
1036 , p_message_field => NULL
1037 , p_application_short_name => 'HXC'
1038 , p_timecard_bb_id => null
1039 , p_time_attribute_id => NULL
1040 , p_timecard_bb_ovn => null
1041 , p_time_attribute_ovn => NULL );
1042 ELSE
1043
1044 -- and now finally ADD NEW ROWS FOR DROPS IN TBB AND ATTRIBS
1045 -- if g_debug then
1046 --hr_utility.trace('ADDING.....ROWS HERE....DROPS IN ELP SEEN');
1047 -- end if;
1048
1049 -- Initilaize the index etc to pop
1050 -- 115.7
1051 -- removing the addition to collection and instead calling the api proc directly
1052 -- 115.8 2905369 start
1053 -- Again going to use collection and commenting the call to the api proc.
1054
1055
1056 l_next_blk_id := HXC_TIMECARD_BLOCK_UTILS.NEXT_BLOCK_ID(P_TIME_BUILDING_BLOCKS);
1057
1058 l_index:=null;
1059 l_index := P_TIME_BUILDING_BLOCKS.last;
1060 l_index := l_index +1;
1061
1062 --
1063 -- Insert the new building block record
1064 --
1065 -- The application set id for this will be the latest valid application set id
1066 --
1067 l_last_index := null;
1068 l_last_index := temp_app_set.FIRST;
1069
1070 -- Fix for Bug 3025823
1071 -- check if current application set id needs to be null
1072
1073 if(l_null_application_set_id = 'N')
1074 then
1075 P_TIME_BUILDING_BLOCKS.extend();
1076 P_TIME_BUILDING_BLOCKS(l_index) :=
1077 HXC_BLOCK_TYPE
1078 (l_next_blk_id
1079 ,P_TIME_BUILDING_BLOCKS (l_index_det).TYPE
1080 ,P_TIME_BUILDING_BLOCKS (l_index_det).MEASURE
1081 ,P_TIME_BUILDING_BLOCKS (l_index_det).UNIT_OF_MEASURE
1082 ,P_TIME_BUILDING_BLOCKS (l_index_det).START_TIME
1083 ,P_TIME_BUILDING_BLOCKS (l_index_det).STOP_TIME
1084 ,P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_BUILDING_BLOCK_ID
1085 ,P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_IS_NEW
1086 ,P_TIME_BUILDING_BLOCKS (l_index_det).SCOPE
1087 ,-9999
1088 ,P_TIME_BUILDING_BLOCKS (l_index_det).APPROVAL_STATUS
1089 ,P_TIME_BUILDING_BLOCKS (l_index_det).RESOURCE_ID
1090 ,P_TIME_BUILDING_BLOCKS (l_index_det).RESOURCE_TYPE
1091 ,P_TIME_BUILDING_BLOCKS (l_index_det).APPROVAL_STYLE_ID
1092 ,P_TIME_BUILDING_BLOCKS (l_index_det).DATE_FROM
1093 ,fnd_date.date_to_canonical(hr_general.end_of_time)
1094 ,P_TIME_BUILDING_BLOCKS (l_index_det).COMMENT_TEXT
1095 ,P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_BUILDING_BLOCK_OVN
1096 ,'Y'
1097 ,'N'
1098 ,P_TIME_BUILDING_BLOCKS (l_index_det).PROCESS
1099 ,temp_app_set (l_last_index).application_set_id
1103 P_TIME_BUILDING_BLOCKS.extend();
1100 ,P_TIME_BUILDING_BLOCKS (l_index_det).TRANSLATION_DISPLAY_KEY
1101 );
1102 else
1104 P_TIME_BUILDING_BLOCKS(l_index) :=
1105 HXC_BLOCK_TYPE
1106 (l_next_blk_id
1107 ,P_TIME_BUILDING_BLOCKS (l_index_det).TYPE
1108 ,P_TIME_BUILDING_BLOCKS (l_index_det).MEASURE
1109 ,P_TIME_BUILDING_BLOCKS (l_index_det).UNIT_OF_MEASURE
1110 ,P_TIME_BUILDING_BLOCKS (l_index_det).START_TIME
1111 ,P_TIME_BUILDING_BLOCKS (l_index_det).STOP_TIME
1112 ,P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_BUILDING_BLOCK_ID
1113 ,P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_IS_NEW
1114 ,P_TIME_BUILDING_BLOCKS (l_index_det).SCOPE
1115 ,-9999
1116 ,P_TIME_BUILDING_BLOCKS (l_index_det).APPROVAL_STATUS
1117 ,P_TIME_BUILDING_BLOCKS (l_index_det).RESOURCE_ID
1118 ,P_TIME_BUILDING_BLOCKS (l_index_det).RESOURCE_TYPE
1119 ,P_TIME_BUILDING_BLOCKS (l_index_det).APPROVAL_STYLE_ID
1120 ,P_TIME_BUILDING_BLOCKS (l_index_det).DATE_FROM
1121 ,fnd_date.date_to_canonical(hr_general.end_of_time)
1122 ,P_TIME_BUILDING_BLOCKS (l_index_det).COMMENT_TEXT
1123 ,P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_BUILDING_BLOCK_OVN
1124 ,'Y'
1125 ,'N'
1126 ,P_TIME_BUILDING_BLOCKS (l_index_det).PROCESS
1127 ,null
1128 ,P_TIME_BUILDING_BLOCKS (l_index_det).TRANSLATION_DISPLAY_KEY
1129 );
1130 end if; -- l_null_application_set_id = 'N'
1131
1132 -- 115.8 2905369
1133 -- Now change the information on the current
1134 -- detail TBB here itself, in effect just get to end date it
1135 -- with the application set id as the one for dropped time recipients.
1136 --
1137 l_next_blk_ovn := 1;
1138 P_TIME_BUILDING_BLOCKS (l_index_det).MEASURE := null;
1139 P_TIME_BUILDING_BLOCKS (l_index_det).DATE_TO := fnd_date.date_to_canonical(sysdate);
1140 -- ksethi fix for bug 3831067
1141 -- Comment the following line.
1142 -- P_TIME_BUILDING_BLOCKS (l_index_det).application_set_id := temp_app_set_drops(l_index_drops).application_set_id;
1143 l_rev_application_set_id := 'Y';
1144 --
1145 -- end change for current details block.... 2905369 end
1146
1147 -- 115.8 comment the following call to API and instead using above collection.
1148
1149 /*
1150 l_next_blk_id := null;
1151 l_next_blk_ovn := null;
1152
1153 -- call the create_reversing_entry directly.....
1154 -- to insert this reversed TBB in the db
1155
1156 hxc_building_block_api.create_reversing_entry
1157 (p_effective_date => sysdate
1158 ,p_type => P_TIME_BUILDING_BLOCKS (l_index_det).TYPE
1159 ,p_measure => P_TIME_BUILDING_BLOCKS (l_index_det).MEASURE
1160 ,p_unit_of_measure => P_TIME_BUILDING_BLOCKS (l_index_det).UNIT_OF_MEASURE
1161 ,p_start_time => hxc_timecard_block_utils.date_value(P_TIME_BUILDING_BLOCKS (l_index_det).START_TIME)
1162 ,p_stop_time => hxc_timecard_block_utils.date_value(P_TIME_BUILDING_BLOCKS (l_index_det).STOP_TIME)
1163 ,p_parent_building_block_id => P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_BUILDING_BLOCK_ID
1164 ,p_parent_building_block_ovn => P_TIME_BUILDING_BLOCKS (l_index_det).PARENT_BUILDING_BLOCK_OVN
1165 ,p_scope => P_TIME_BUILDING_BLOCKS (l_index_det).SCOPE
1166 ,p_approval_style_id => P_TIME_BUILDING_BLOCKS (l_index_det).APPROVAL_STYLE_ID
1167 ,p_approval_status => P_TIME_BUILDING_BLOCKS (l_index_det).APPROVAL_STATUS
1168 ,p_resource_id => P_TIME_BUILDING_BLOCKS (l_index_det).RESOURCE_ID
1169 ,p_resource_type => P_TIME_BUILDING_BLOCKS (l_index_det).RESOURCE_TYPE
1170 ,p_comment_text => P_TIME_BUILDING_BLOCKS (l_index_det).COMMENT_TEXT
1171 ,p_application_set_id => temp_app_set_drops(l_index_drops).application_set_id
1172 ,p_date_to => sysdate
1173 ,p_time_building_block_id => l_next_blk_id
1174 ,p_object_version_number => l_next_blk_ovn
1175 );
1176 */
1177 -- Now for attributes
1178
1179 l_index :=null;
1180 l_index := P_TIME_ATTRIBUTES.last;
1181 l_index := l_index +1;
1182
1183 -- start 2986524
1184 -- 115.10 Reverting the change of 115.6 moving back to add current
1185 -- attributes no need for old attributes now.
1186 -- 115.6
1187 l_last_index := null;
1188 l_last_index := P_TIME_ATTRIBUTES.last;
1189
1190 --
1191 -- Insert the new attribute records
1192 --
1193
1194 l_index_attr := null;
1195 l_index_attr := P_TIME_ATTRIBUTES.FIRST;
1196 -- 115.11 Fix for 2995655
1197 -- commenting the P_TIME_ATTRIBUTES(l_index_attr).BUILDING_BLOCK_OVN part of if condition
1198 While l_index_attr is NOT NULL
1199 LOOP
1200 IF(P_TIME_ATTRIBUTES(l_index_attr).BUILDING_BLOCK_ID = P_TIME_BUILDING_BLOCKS (l_index_det).TIME_BUILDING_BLOCK_ID
1201 -- and P_TIME_ATTRIBUTES(l_index_attr).BUILDING_BLOCK_OVN = P_TIME_BUILDING_BLOCKS (l_index_det).OBJECT_VERSION_NUMBER)
1202 )
1203 THEN
1204
1205 -- Commenting the below
1206 -- for 2986524
1207 -- no need of this cursor now.
1208 --
1209 -- FOR c_get_att in c_get_dropped_attribs(P_TIME_BUILDING_BLOCKS (l_index_det).TIME_BUILDING_BLOCK_ID,
1210 -- P_TIME_BUILDING_BLOCKS (l_index_det).OBJECT_VERSION_NUMBER)
1211 --
1212 -- LOOP
1213
1214 l_next_attr_id := HXC_TIMECARD_ATTRIBUTE_UTILS.NEXT_TIME_ATTRIBUTE_ID(P_TIME_ATTRIBUTES);
1215
1219 P_TIME_ATTRIBUTES.extend();
1216 -- Commenting population from cursor and instead adding current attributes.
1217 --
1218 /*
1220 P_TIME_ATTRIBUTES(l_index) :=
1221 HXC_ATTRIBUTE_TYPE
1222 (l_next_attr_id
1223 ,l_next_blk_id
1224 ,c_get_att.ATTRIBUTE_CATEGORY
1225 ,c_get_att.ATTRIBUTE1
1226 ,c_get_att.ATTRIBUTE2
1227 ,c_get_att.ATTRIBUTE3
1228 ,c_get_att.ATTRIBUTE4
1229 ,c_get_att.ATTRIBUTE5
1230 ,c_get_att.ATTRIBUTE6
1231 ,c_get_att.ATTRIBUTE7
1232 ,c_get_att.ATTRIBUTE8
1233 ,c_get_att.ATTRIBUTE9
1234 ,c_get_att.ATTRIBUTE10
1235 ,c_get_att.ATTRIBUTE11
1236 ,c_get_att.ATTRIBUTE12
1237 ,c_get_att.ATTRIBUTE13
1238 ,c_get_att.ATTRIBUTE14
1239 ,c_get_att.ATTRIBUTE15
1240 ,c_get_att.ATTRIBUTE16
1241 ,c_get_att.ATTRIBUTE17
1242 ,c_get_att.ATTRIBUTE18
1243 ,c_get_att.ATTRIBUTE19
1244 ,c_get_att.ATTRIBUTE20
1245 ,c_get_att.ATTRIBUTE21
1246 ,c_get_att.ATTRIBUTE22
1247 ,c_get_att.ATTRIBUTE23
1248 ,c_get_att.ATTRIBUTE24
1249 ,c_get_att.ATTRIBUTE25
1250 ,c_get_att.ATTRIBUTE26
1251 ,c_get_att.ATTRIBUTE27
1252 ,c_get_att.ATTRIBUTE28
1253 ,c_get_att.ATTRIBUTE29
1254 ,c_get_att.ATTRIBUTE30
1255 ,c_get_att.BLD_BLK_INFO_TYPE_ID
1256 ,1
1257 ,'Y'
1258 ,'N'
1259 ,c_get_att.BLD_BLK_INFO_TYPE
1260 ,'Y' -- PROCESS Flag
1261 ,l_next_blk_ovn
1262 );
1263 */
1264 --
1265 -- Adding population from current structure
1266 --
1267 P_TIME_ATTRIBUTES.extend();
1268 P_TIME_ATTRIBUTES(l_index) :=
1269 HXC_ATTRIBUTE_TYPE
1270 (l_next_attr_id
1271 ,l_next_blk_id
1272 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE_CATEGORY
1273 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE1
1274 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE2
1275 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE3
1276 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE4
1277 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE5
1278 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE6
1279 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE7
1280 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE8
1281 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE9
1282 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE10
1283 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE11
1284 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE12
1285 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE13
1286 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE14
1287 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE15
1288 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE16
1289 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE17
1290 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE18
1291 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE19
1292 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE20
1293 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE21
1294 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE22
1295 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE23
1296 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE24
1297 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE25
1298 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE26
1299 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE27
1300 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE28
1301 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE29
1302 ,P_TIME_ATTRIBUTES(l_index_attr).ATTRIBUTE30
1303 ,P_TIME_ATTRIBUTES(l_index_attr).BLD_BLK_INFO_TYPE_ID
1304 ,1
1305 ,'Y'
1306 ,'N'
1307 ,P_TIME_ATTRIBUTES(l_index_attr).BLD_BLK_INFO_TYPE
1308 ,P_TIME_ATTRIBUTES(l_index_attr).PROCESS
1309 ,1
1310 );
1311
1312 --
1313 l_index:= l_index+1;
1314 --
1315 END IF; -- P_TIME_ATTRIBUTES(l_index_attr).BUILDING_BLOCK_ID = P_TIME_BUILDING_BLOCKS
1316 --
1317 l_index_attr := P_TIME_ATTRIBUTES.NEXT(l_index_attr);
1318 --
1319
1320 --Bug 3025823 Sonarasi 26-Jun-2003
1321 --Commenting the following line. If this line is not commented then the loop
1322 --would not be executed for the last iteration. Since we want the loop to be completely
1323 --executed, we are commenting this line.
1324 --EXIT WHEN l_index_attr = l_last_index;
1325 --Bug 3025823 Sonarasi 26-Jun-2003 Over
1326
1327 END LOOP; -- end loop for l_index_attr not null -- Ignore -> FOR c_get_att in c_get_dropped_attribs
1328
1329 -- Bug 4644401 Sechandr 22-Nov-2005
1330 -- calling this procedure to restore the old attributes in the end date TBB detail row if there is an updation on the timeccard.
1331 create_reverse_entry(P_TIME_BUILDING_BLOCKS,P_TIME_ATTRIBUTES,l_index_det);
1332
1333 END IF; -- app_set_ok = 'N'
1334
1335 /*
1336 -- ****************** CHECKS for STRUCTURE After DROPS ******************
1337 if g_debug then
1338 hr_utility.trace('*******POST DROPS STATS******');
1339 hr_utility.trace('-----------------------------');
1340 hr_utility.trace('| TBB_ID | APPL_SET_ID |SCOPE | OVN| NEW| CHANGED|');
1341 hr_utility.trace('-----------------------------');
1342 for x IN P_TIME_BUILDING_BLOCKS.first .. P_TIME_BUILDING_BLOCKS.last
1343 loop
1344 hr_utility.trace('| '
1345 ||P_TIME_BUILDING_BLOCKS(x).time_building_block_id
1346 ||' | '||P_TIME_BUILDING_BLOCKS(x).application_set_id
1347 ||' | '||p_time_building_blocks(x).SCOPE||' | '
1348 ||P_TIME_BUILDING_BLOCKS (x).object_version_number||' | '
1349 ||P_TIME_BUILDING_BLOCKS(x).NEW||' | '
1350 ||P_TIME_BUILDING_BLOCKS(x).CHANGED);
1351 end loop;
1352
1353
1354 hr_utility.trace('-----------------------------');
1355 hr_utility.trace('| ATTRIB_ID |TBB ID | BLD_BLK_INFO_TYPE| ATTRIBUTE_CATEGORY | TBB_ID| NEW| CHANGED|');
1356 hr_utility.trace('-----------------------------');
1360 loop
1357 l_index := null;
1358 l_index := P_TIME_ATTRIBUTES.first;
1359 while l_index is not null
1361
1362 hr_utility.trace('| '||P_TIME_ATTRIBUTES(l_index).TIME_ATTRIBUTE_ID
1363 ||' | '||P_TIME_ATTRIBUTES(l_index).BUILDING_BLOCK_ID
1364 ||' | '||P_TIME_ATTRIBUTES(l_index).BLD_BLK_INFO_TYPE
1365 ||' | '||P_TIME_ATTRIBUTES(l_index).ATTRIBUTE_CATEGORY
1366 || ' | '||P_TIME_ATTRIBUTES(l_index).BUILDING_BLOCK_ID
1367 ||' | '||P_TIME_ATTRIBUTES(l_index).NEW||' | '
1368 ||P_TIME_ATTRIBUTES(l_index).CHANGED);
1369 l_index := P_TIME_ATTRIBUTES.NEXT(l_index);
1370 end loop;
1371
1372
1373 hr_utility.trace('-----------------------------');
1374 end if;
1375 -- ****************** ENDING CHECKS for STRUCTURE After DROPS ******************
1376 */
1377
1378 END IF; -- list_tim_rec_drops.COUNT > 0
1379
1380
1381 l_index:=null;
1382 l_index:=temp_app_set.FIRST;
1383 -- 115.8 2905369 start
1384 -- Adding check if this DETAIL TBB has been updated in the reversing section
1385 --
1386 if (l_rev_application_set_id ='N')
1387 then
1388 -- 115.6 start
1389 P_TIME_BUILDING_BLOCKS (l_index_det).application_set_id := null;
1390 -- and now finally update the Appl SET id in the DETAIL SCOPE... :-)
1391 if(l_null_application_set_id = 'N')
1392 then
1393 P_TIME_BUILDING_BLOCKS (l_index_det).application_set_id :=
1394 temp_app_set (l_index).application_set_id;
1395 end if; -- l_null_application_set_id = 'N'
1396 end if; -- l_rev_application_set_id ='N'
1397 END IF; -- app_set_ok = 'N'
1398 -- la la ksethi end DETAIL table processin
1399
1400
1401 END IF; -- Scope = DETAIL
1402
1403 l_index_det := P_TIME_BUILDING_BLOCKS.NEXT (l_index_det);
1404 END LOOP; -- l_index_det is not null
1405
1406 -- Check if for the current DAY, any DETAIL was entered or not,
1407 -- if not then default both DAY and TIMECARD to the application
1408 -- set at the user level.
1409
1410 IF (list_tim_rec_day.COUNT = 0 )
1411 THEN
1412
1413 -- Populate both Detail n Timecard as masters
1414 --
1415 l_all_days_entered := 'N';
1416 list_tim_rec_day.DELETE;
1417 list_tim_rec_tc.DELETE;
1418
1419 l_index := 0;
1420
1421 l_index_tmp_app := NULL;
1422 l_index_tmp_app := cached_tm_set.FIRST;
1423
1424 WHILE l_index_tmp_app IS NOT NULL
1425 LOOP
1426 IF (cached_tm_set (l_index_tmp_app).application_set_id =
1427 P_APPLICATION_SET_ID
1428 )
1429 THEN
1430 list_tim_rec_day(l_index).time_recipient_id :=
1431 cached_tm_set (l_index_tmp_app).time_recipient_id;
1432 list_tim_rec_tc(l_index).time_recipient_id :=
1433 cached_tm_set (l_index_tmp_app).time_recipient_id;
1434 l_index:=l_index+1;
1435 END IF;
1436
1437 l_index_tmp_app := cached_tm_set.NEXT (l_index_tmp_app);
1438 END LOOP; -- l_index_tmp_app IS NOT NULL
1439 END IF; -- list_tim_rec_day.COUNT = 0
1440
1441 -- Start DAY's Time Recipient table processing
1442 -- in order to set the DAY SCOPE Application Set ID
1443
1444
1445
1446 -- Initialize a new temproary table to
1447 -- derive the application set id :-)
1448 -- Populate it using the cached Application Set Table
1449 -- i.e. table 'cached_app_set'
1450
1451 IF (temp_app_set.COUNT > 0)
1452 THEN
1453 temp_app_set.DELETE;
1454 END IF;
1455
1456 l_index := 0;
1457
1458 l_index_tmp_app := NULL;
1459 l_index_tmp_app := cached_app_set.FIRST;
1460
1461 WHILE l_index_tmp_app IS NOT NULL
1462 LOOP
1463 IF (cached_app_set (l_index_tmp_app).rec_totals =
1464 list_tim_rec_day.COUNT
1465 )
1466 THEN
1467 temp_app_set (l_index).application_set_id :=
1468 cached_app_set (l_index_tmp_app).application_set_id;
1469 l_index := l_index
1470 + 1;
1471 END IF;
1472
1473 l_index_tmp_app := cached_app_set.NEXT (l_index_tmp_app);
1474 END LOOP; -- l_index_tmp_app IS NOT NULL
1475
1476
1477
1478 -- Now check for individual Time Recipients
1479
1480
1481 l_index_tmp_app := NULL;
1482 l_index_tmp_app := temp_app_set.FIRST;
1483
1484 WHILE l_index_tmp_app IS NOT NULL
1485 LOOP
1486 app_set_ok := 'Y';
1487
1488
1489 l_index_tmp_rec := NULL;
1490 l_index_tmp_rec := cached_tm_set.FIRST;
1491
1492
1493 WHILE l_index_tmp_rec IS NOT NULL
1494 LOOP
1495 IF (cached_tm_set (l_index_tmp_rec).application_set_id =
1496 temp_app_set (l_index_tmp_app).application_set_id
1497 )
1498 THEN
1499 time_rec_present := 'N';
1500
1501 --
1502 FOR x IN
1503 list_tim_rec_day.FIRST .. list_tim_rec_day.LAST
1504 LOOP
1505 IF (cached_tm_set (l_index_tmp_rec).time_recipient_id =
1506 list_tim_rec_day (x).time_recipient_id
1507 )
1508 THEN
1509 time_rec_present := 'Y';
1510 END IF;
1511 END LOOP; -- x IN temp_app_set
1512
1513 IF (time_rec_present = 'N')
1514 THEN
1515 app_set_ok := 'N';
1516 END IF;
1517 END IF; -- match for same appl set in both tables as same
1518
1519 l_index_tmp_rec :=
1520 cached_tm_set.NEXT (l_index_tmp_rec);
1521 END LOOP; -- l_index_tmp_rec is not null
1522
1523 --Check flag to delete or not to delete
1524
1525 IF (app_set_ok = 'N')
1526 THEN
1527 temp_app_set.DELETE (l_index_tmp_app);
1528 END IF;
1529
1530 l_index_tmp_app := temp_app_set.NEXT (l_index_tmp_app);
1531 END LOOP; -- l_index_tmp_app is not null
1535
1532
1533 l_index := NULL;
1534 l_index := temp_app_set.FIRST;
1536
1537 -- Check to see if the application set id that is
1538 -- to be set is a subset of the main Preference level
1539 -- then only update
1540 -- here check that this falls in the temp appl set ids table
1541 app_set_ok := 'N';
1542
1543 FOR x IN valid_app_set.FIRST .. valid_app_set.LAST
1544 LOOP
1545 IF (temp_app_set (l_index).application_set_id =
1546 valid_app_set (x).application_set_id
1547 )
1548 THEN
1549 app_set_ok := 'Y';
1550 END IF;
1551 END LOOP;
1552
1553 -- if application set Id is not in the temp
1554 -- table of application set Ids then
1555 -- raise an error as the time recipient is not contained in
1556 -- the application set id attached to the resource preference
1557 IF (app_set_ok = 'N')
1558 THEN
1559 -- 115.6
1560 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1561 p_message_table => p_messages
1562 , p_message_name => 'HXC_VLD_ELP_VIOLATION'
1563 , p_message_token => NULL
1564 , p_message_level => 'ERROR'
1565 , p_message_field => NULL
1566 , p_application_short_name => 'HXC'
1567 , p_timecard_bb_id => null
1568 , p_time_attribute_id => NULL
1569 , p_timecard_bb_ovn => null
1570 , p_time_attribute_ovn => NULL );
1571 ELSE -- update the application set id on the TBB
1572 -- here one more check has to be made for checkin if
1573 -- already some application set id is present or not
1574 -- in which case all time transfer etc needs to be
1575 -- checked, postponing that check for now, will add laterz
1576
1577 -- and now finally update the Appl SET id in the DAY SCOPE... :-)
1578 P_TIME_BUILDING_BLOCKS (l_index_day).application_set_id :=
1579 temp_app_set (l_index).application_set_id;
1580
1581 END IF; -- app_set_ok = 'N'
1582
1583 END IF; -- Scope = DAY
1584
1585
1586 l_index_day := P_TIME_BUILDING_BLOCKS.NEXT (l_index_day);
1587 END LOOP; -- l_index_day is not null
1588
1589 -- Start TIMECARD's Time Recipient table processing
1590 -- in order to set the TIMECARD SCOPE Application Set ID
1591
1592 -- Populate TIMECARD's Time Recipient table when detail for all days is entered
1593 IF(l_all_days_entered = 'Y')
1594 THEN
1595 if(list_tim_rec_tc.COUNT > 0) then
1596 list_tim_rec_tc.DELETE;
1597 end if;
1598 l_index := 0;
1599 l_index_tmp_app := NULL;
1600 l_index_tmp_app := cached_tm_set.FIRST;
1601
1602 WHILE l_index_tmp_app IS NOT NULL
1603 LOOP
1604 IF (cached_tm_set (l_index_tmp_app).application_set_id =
1605 p_application_set_id
1606 )
1607 THEN
1608 list_tim_rec_tc (l_index).time_recipient_id :=
1609 cached_tm_set (l_index_tmp_app).time_recipient_id;
1610 l_index := l_index
1611 + 1;
1612 END IF;
1613
1614 l_index_tmp_app :=
1615 cached_tm_set.NEXT (l_index_tmp_app);
1616 END LOOP; -- l_index_tmp_app IS NOT NULL
1617 END IF;
1618 -- initialize a new temproary table to
1619 -- SCOPE = TIMECARD
1620 -- derive the application set id :-)
1621 -- Populate it using the cached Application Set Table
1622 -- i.e. table 'cached_app_set'
1623
1624
1625 IF (temp_app_set.COUNT > 0)
1626 THEN
1627 temp_app_set.DELETE;
1628 END IF;
1629
1630 l_index := 0;
1631
1632 l_index_tmp_app := NULL;
1633 l_index_tmp_app := cached_app_set.FIRST;
1634
1635 WHILE l_index_tmp_app IS NOT NULL
1636 LOOP
1637 IF (cached_app_set (l_index_tmp_app).rec_totals =
1638 list_tim_rec_tc.COUNT
1639 )
1640 THEN
1641 temp_app_set (l_index).application_set_id :=
1642 cached_app_set (l_index_tmp_app).application_set_id;
1643 l_index := l_index
1644 + 1;
1645 END IF;
1646
1647 l_index_tmp_app := cached_app_set.NEXT (l_index_tmp_app);
1648 END LOOP; -- l_index_tmp_app IS NOT NULL
1649
1650
1651 -- Now check for individual Time Recipients
1652
1653
1654 l_index_tmp_app := NULL;
1655 l_index_tmp_app := temp_app_set.FIRST;
1656
1657 WHILE l_index_tmp_app IS NOT NULL
1658 LOOP
1659 app_set_ok := 'Y';
1660
1661
1662 l_index_tmp_rec := NULL;
1663 l_index_tmp_rec := cached_tm_set.FIRST;
1664
1665
1666 WHILE l_index_tmp_rec IS NOT NULL
1667 LOOP
1668 IF (cached_tm_set (l_index_tmp_rec).application_set_id =
1669 temp_app_set (l_index_tmp_app).application_set_id
1670 )
1671 THEN
1672 time_rec_present := 'N';
1673
1674 --
1675 FOR x IN list_tim_rec_tc.FIRST .. list_tim_rec_tc.LAST
1676 LOOP
1677 IF (cached_tm_set (l_index_tmp_rec).time_recipient_id =
1678 list_tim_rec_tc (x).time_recipient_id
1679 )
1680 THEN
1681 time_rec_present := 'Y';
1682 END IF;
1683 END LOOP; -- x IN temp_app_set
1684
1685 IF (time_rec_present = 'N')
1686 THEN
1687 app_set_ok := 'N';
1688 END IF;
1689 END IF; -- match for same appl set in both tables as same
1690
1694 --Check flag to delete or not to delete
1691 l_index_tmp_rec := cached_tm_set.NEXT (l_index_tmp_rec);
1692 END LOOP; -- l_index_tmp_rec is not null
1693
1695
1696 IF (app_set_ok = 'N')
1697 THEN
1698 temp_app_set.DELETE (l_index_tmp_app);
1699 END IF;
1700
1701 l_index_tmp_app := temp_app_set.NEXT (l_index_tmp_app);
1702 END LOOP; -- l_index_tmp_app is not null
1703
1704
1705 l_index := NULL;
1706 l_index := temp_app_set.FIRST;
1707
1708 -- Check to see if the application set id that is
1709 -- to be set is a subset of the main Preference level
1710 -- then only update
1711 -- here check that this falls in the temp appl set ids table
1712 app_set_ok := 'N';
1713
1714 FOR x IN valid_app_set.FIRST .. valid_app_set.LAST
1715 LOOP
1716 IF (temp_app_set (l_index).application_set_id =
1717 valid_app_set (x).application_set_id
1718 )
1719 THEN
1720 app_set_ok := 'Y';
1721 END IF;
1722 END LOOP;
1723
1724 -- if application set Id is not in the temp
1725 -- table of application set Ids then
1726 -- raise an error as the time recipient is not contained in
1727 -- the application set id attached to the resource preference
1728 IF (app_set_ok = 'N')
1729 THEN
1730 -- 115.6
1731 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1732 p_message_table => p_messages
1733 , p_message_name => 'HXC_VLD_ELP_VIOLATION'
1734 , p_message_token => NULL
1735 , p_message_level => 'ERROR'
1736 , p_message_field => NULL
1737 , p_application_short_name => 'HXC'
1738 , p_timecard_bb_id => null
1739 , p_time_attribute_id => NULL
1740 , p_timecard_bb_ovn => null
1741 , p_time_attribute_ovn => NULL );
1742 ELSE -- update the application set id on the TBB
1743 -- here one more check has to be made for checkin if
1744 -- already some application set id is present or not
1745 -- in which case all time transfer etc needs to be
1746 -- checked, postponing that check for now, will add laterz
1747
1748 --- and now finally update the Appl SET id in the DAY TIMECARD... :-)
1749 P_TIME_BUILDING_BLOCKS (l_index_tc).application_set_id :=
1750 temp_app_set (l_index).application_set_id;
1751 END IF; -- app_set_ok = 'N'
1752 END IF; -- scope = TIMECARD
1753
1754 l_index_tc := P_TIME_BUILDING_BLOCKS.NEXT (l_index_tc);
1755 END LOOP; -- l_index_tc is not null
1756
1757
1758
1759
1760
1761 -- here ends the real processing
1762 else -- p_pte_terg_id is null
1763
1764 -- No Entry Level Processing is set
1765 -- update all TBB to have the application set id
1766 -- as the one set on the resource preference
1767 --
1768 -- if g_debug then
1769 -- hr_utility.trace('ks NO ELP');
1770 -- end if;
1771 l_index_tc := NULL;
1772
1773 l_index_tc := P_TIME_BUILDING_BLOCKS.FIRST;
1774
1775 WHILE l_index_tc IS NOT NULL
1776 LOOP
1777
1778 P_TIME_BUILDING_BLOCKS(l_index_tc).application_set_id := P_APPLICATION_SET_ID;
1779
1780 l_index_tc := P_TIME_BUILDING_BLOCKS.NEXT(l_index_tc);
1781
1782
1783 END LOOP;
1784
1785 end if; -- (l_pte_terg_id <> null)
1786
1787 -- 2905369 start 115.8
1788 -- Change back the OVN for the new TBB back to 1
1789 --
1790 l_index := null;
1791 l_index := P_TIME_BUILDING_BLOCKS.first;
1792 while l_index is not null
1793 loop
1794 if (P_TIME_BUILDING_BLOCKS(l_index).OBJECT_VERSION_NUMBER = -9999)
1795 then
1796 -- if g_debug then
1797 -- hr_utility.trace('ks ... Yes -ve TBB OVN found which is for the new entry.');
1798 -- end if;
1799 P_TIME_BUILDING_BLOCKS(l_index).OBJECT_VERSION_NUMBER := 1;
1800 end if;
1801 l_index := P_TIME_BUILDING_BLOCKS.NEXT(l_index);
1802 end loop;
1803
1804 -- 2905369 end 115.8
1805
1806
1807 /*
1808 -- ************* After Call to Update/Populate Appliation Set
1809 if g_debug then
1810 hr_utility.trace('*******AFTER PROCESSING******');
1811 hr_utility.trace('-----------------------------');
1812 hr_utility.trace('| TBB_ID | APPL_SET_ID|');
1813 hr_utility.trace('-----------------------------');
1814 for x IN P_TIME_BUILDING_BLOCKS.first .. P_TIME_BUILDING_BLOCKS.last
1815 loop
1816 hr_utility.trace('| '||P_TIME_BUILDING_BLOCKS(x).time_building_block_id
1817 ||' | '||P_TIME_BUILDING_BLOCKS(x).application_set_id
1818 ||' |');
1819 end loop;
1820
1821 hr_utility.trace('Leaves and messges cnt ='||p_messages.count);
1822 end if;
1823 */
1824
1825 EXCEPTION
1826 WHEN OTHERS THEN
1827 -- 115.7
1828 -- hr_utility.set_message (809, 'HXC_ELP_APPL_PROCESS_ERROR');
1829 -- hr_utility.raise_error;
1830 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1831 p_message_table => p_messages
1832 , p_message_name => 'EXCEPTION'
1833 , p_message_token => NULL
1834 , p_message_level => 'ERROR'
1835 , p_message_field => NULL
1836 , p_application_short_name => 'HXC'
1837 , p_timecard_bb_id => null
1838 , p_time_attribute_id => NULL
1839 , p_timecard_bb_ovn => null
1840 , p_time_attribute_ovn => NULL );
1841
1842
1843
1844 END set_time_bb_appl_set_id;
1845
1846
1847 -- ksethi end the proc...
1848
1849
1850 FUNCTION build_elp_objects
1851 (P_ELP_TIME_BUILDING_BLOCKS HXC_BLOCK_TABLE_TYPE
1855 IS
1852 ,P_ELP_TIME_ATTRIBUTES HXC_ATTRIBUTE_TABLE_TYPE
1853 ,P_TIME_RECIPIENT_ID number
1854 ) RETURN HXC_BLOCK_TABLE_TYPE
1856
1857 time_rec_present_det varchar2(1);
1858 time_rec_present_day varchar2(1);
1859 time_rec_present_tc varchar2(1);
1860
1861
1862 TYPE v_app_set IS RECORD (
1863 application_set_id NUMBER (15));
1864
1865 TYPE r_app_set IS TABLE OF v_app_set
1866 INDEX BY BINARY_INTEGER;
1867
1868 cached_app_set r_app_set;
1869 l_elp_time_builidng_block HXC_BLOCK_TABLE_TYPE;
1870
1871 l_index BINARY_INTEGER;
1872 l_index_tc BINARY_INTEGER;
1873 l_index_day BINARY_INTEGER;
1874 l_index_det BINARY_INTEGER;
1875
1876
1877 CURSOR csr_app_set_id (p_tim_rec_id NUMBER)
1878 IS
1879 SELECT DISTINCT (application_set_id) A
1880 FROM hxc_application_set_comps_v
1881 WHERE time_recipient_id = p_tim_rec_id;
1882
1883
1884 l_proc VARCHAR2 (30);
1885 --
1886 BEGIN
1887 --
1888 g_debug:=hr_utility.debug_enabled;
1889 if g_debug then
1890 l_proc := 'build_elp_objects';
1891 hr_utility.set_location('Processing '||l_proc, 10);
1892 end if;
1893 --
1894 -- issue a savepoint
1895 savepoint build_elp_objects;
1896 --
1897 /*
1898 -- **************************** START TRACE SECTION ********************
1899 if g_debug then
1900 hr_utility.trace('ks enters BUILD P_TIME_RECIPIENT_ID = '||P_TIME_RECIPIENT_ID);
1901 -- Initialize a local copy of the time builidng block
1902 hr_utility.trace('*******BEFORE PROCESSING******');
1903 hr_utility.trace('-----------------------------');
1904 hr_utility.trace('| TBB_ID | APPL_SET_ID|');
1905 hr_utility.trace('-----------------------------');
1906 for x IN P_ELP_TIME_BUILDING_BLOCKS.first .. P_ELP_TIME_BUILDING_BLOCKS.last
1907 loop
1908 hr_utility.trace('| '||P_ELP_TIME_BUILDING_BLOCKS(x).time_building_block_id
1909 ||' | '||P_ELP_TIME_BUILDING_BLOCKS(x).application_set_id
1910 ||' |');
1911 end loop;
1912 end if;
1913 -- **************************** END TRACE SECTION ************************
1914 */
1915
1916 l_elp_time_builidng_block := p_elp_time_building_blocks;
1917
1918
1919 l_index := 1;
1920
1921 FOR appl_set IN csr_app_set_id(p_time_recipient_id) LOOP
1922
1923 --
1924 -- Get all the valid application sets for the
1925 -- current time recipient
1926 -- and use them to populate the
1927 -- temporary table 'cached_app_set'
1928 --
1929 cached_app_set(l_index).application_set_id := appl_set.A;
1930
1931 l_index := l_index+1;
1932 END LOOP;
1933 -- if g_debug then
1934 -- hr_utility.trace('valid cached_app_set.COUNT = '||cached_app_set.COUNT);
1935 -- end if;
1936
1937 -- here starts the real processing
1938
1939 -- Initialize to read the global timecard object
1940 --
1941 l_index_tc := NULL;
1942 l_index_tc := l_elp_time_builidng_block.FIRST;
1943
1944 WHILE l_index_tc IS NOT NULL
1945 LOOP
1946 IF (l_elp_time_builidng_block (l_index_tc).scope = 'TIMECARD')
1947 THEN
1948 -- Intialize the flag to include timecard row to 'N for SCOPE = TIMECARD
1949
1950 time_rec_present_tc := 'N';
1951
1952 l_index_day := NULL;
1953 l_index_day := l_elp_time_builidng_block.FIRST;
1954
1955 WHILE l_index_day IS NOT NULL
1956 LOOP
1957 IF ( l_elp_time_builidng_block (l_index_day).scope = 'DAY'
1958 AND l_elp_time_builidng_block (l_index_day).parent_building_block_id =
1959 l_elp_time_builidng_block (l_index_tc).time_building_block_id
1960 AND l_elp_time_builidng_block (l_index_day).parent_building_block_ovn =
1961 l_elp_time_builidng_block (l_index_tc).object_version_number
1962 )
1963 THEN
1964 -- Intialize the flag to include timecard row to 'N for SCOPE = DAY
1965 -- if g_debug then
1966 --hr_utility.trace('ks 1208');
1967 -- end if;
1968 time_rec_present_day := 'N';
1969
1970 l_index_det := NULL;
1971 l_index_det := l_elp_time_builidng_block.FIRST;
1972
1973 WHILE l_index_det IS NOT NULL
1974 LOOP
1975 IF ( l_elp_time_builidng_block (l_index_det).scope = 'DETAIL'
1976 AND l_elp_time_builidng_block (l_index_det).parent_building_block_id =
1977 l_elp_time_builidng_block (l_index_day).time_building_block_id
1978 AND l_elp_time_builidng_block (l_index_det).parent_building_block_ovn =
1979 l_elp_time_builidng_block (l_index_day).object_version_number
1980 )
1981 THEN
1982
1983 -- Intialize the flag to include timecard row to 'N for SCOPE = DETAIL
1984
1985 time_rec_present_det := 'N';
1986
1987 -- Start processing the
1988 -- Time recipient for the DETAIL BB_ID
1989
1990 FOR x IN
1991 cached_app_set.FIRST .. cached_app_set.LAST
1992 LOOP
1993 IF (l_elp_time_builidng_block(l_index_det).application_set_id =
1994 cached_app_set(x).application_set_id
1995 )
1996 THEN
1997 time_rec_present_det := 'Y';
1998 time_rec_present_day := 'Y';
1999 time_rec_present_tc := 'Y';
2000 -- if g_debug then
2001 --hr_utility.trace('ks in Yes for all 1240');
2002 -- end if;
2003 END IF; -- check if Time Recipient is already present
2004 END LOOP; -- x IN cached_app_set
2005
2006 -- check n keep or del for DETAIL
2007
2008 IF(time_rec_present_det = 'N') then
2009 l_elp_time_builidng_block.DELETE(l_index_det);
2010 END IF;
2011
2012 END IF; -- Scope = DETAIL
2013
2014 l_index_det := l_elp_time_builidng_block.NEXT (l_index_det);
2015
2019 -- check n keep or del for DAY
2016 END LOOP; -- l_index_det is not null
2017
2018
2020
2021 IF(time_rec_present_day = 'N') then
2022 l_elp_time_builidng_block.DELETE(l_index_day);
2023 END IF;
2024
2025 END IF; -- Scope = DAY
2026
2027
2028 l_index_day := l_elp_time_builidng_block.NEXT (l_index_day);
2029 END LOOP; -- l_index_day is not null
2030
2031 -- check n keep or del for TIMECARD
2032
2033 -- version 115.4 commenting below
2034
2035 -- IF(time_rec_present_tc = 'N') then
2036 -- l_elp_time_builidng_block.DELETE(l_index_tc);
2037 -- END IF;
2038
2039 END IF; -- scope = TIMECARD
2040
2041 l_index_tc := l_elp_time_builidng_block.NEXT (l_index_tc);
2042 END LOOP; -- l_index_tc is not null
2043
2044 /*
2045 -- ********************** START TRACE AFTER PROCESSING***********************
2046 if g_debug then
2047 hr_utility.trace('*******AFTER PROCESSING******');
2048 hr_utility.trace('-----------------------------');
2049 hr_utility.trace('| TBB_ID | APPL_SET_ID|');
2050 hr_utility.trace('-----------------------------');
2051 hr_utility.trace('CNT l_elp_time_builidng_block='||l_elp_time_builidng_block.COUNT);
2052 if(l_elp_time_builidng_block.COUNT > 0 )
2053 then
2054 l_index:=0;
2055 l_index:=l_elp_time_builidng_block.FIRST;
2056 while l_index is not null
2057 loop
2058 hr_utility.trace('| '||l_elp_time_builidng_block(l_index).time_building_block_id||' | '||l_elp_time_builidng_block(l_index).application_set_id||' |');
2059 l_index:=l_elp_time_builidng_block.NEXT(l_index);
2060 end loop; -- l_index is not null
2061 end if; -- l_elp_time_builidng_block.COUNT > 0
2062 end if;
2063 -- ********************** END TRACE AFTER PROCESSING**************************
2064 */
2065 RETURN l_elp_time_builidng_block;
2066
2067 -- here ends the processing
2068 EXCEPTION
2069 WHEN OTHERS THEN
2070
2071 hr_utility.set_message (809, 'HXC_ELP_TC_FILTER_ERROR');
2072 hr_utility.raise_error;
2073
2074
2075 END build_elp_objects;
2076
2077 -- public procedure
2078
2079 PROCEDURE set_time_bb_appl_set_tk
2080 (P_TIME_BUILDING_BLOCKS IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info
2081 ,P_APPLICATION_SET_ID IN number
2082 )
2083 IS
2084
2085 l_index_tc BINARY_INTEGER;
2086 l_proc VARCHAR2 (30);
2087
2088 BEGIN
2089 g_debug:=hr_utility.debug_enabled;
2090 if g_debug then
2091 l_proc := 'set_time_bb_appl_set_tk';
2092 hr_utility.set_location('Processing '||l_proc, 10);
2093 end if;
2094 --
2095 -- Issue a SavePoint
2096 savepoint set_time_bb_appl_tk;
2097 --
2098 -- if g_debug then
2099 --hr_utility.trace('Entered..........'||p_pte_terg_id);
2100 -- end if;
2101
2102 -- Here we just update the g_timecard with the
2103 -- Application Set id set at the user pref
2104 -- level for all the TBBs
2105 --
2106 l_index_tc := NULL;
2107 l_index_tc := P_TIME_BUILDING_BLOCKS.FIRST;
2108
2109 WHILE l_index_tc IS NOT NULL
2110 LOOP
2111 --
2112 P_TIME_BUILDING_BLOCKS(l_index_tc).application_set_id := P_APPLICATION_SET_ID;
2113 --
2114 l_index_tc := P_TIME_BUILDING_BLOCKS.NEXT(l_index_tc);
2115 --
2116 END LOOP;
2117 --
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 hr_utility.set_message (809, 'HXC_ELP_APPL_PROCESS_ERROR');
2121 hr_utility.raise_error;
2122 --
2123 END set_time_bb_appl_set_tk;
2124
2125 end hxc_elp_utils;
[Home] [Help]