4 g_debug boolean := hr_utility.debug_enabled;
1 PACKAGE BODY hxc_block_attribute_update AS
2 /* $Header: hxcbkatup.pkb 120.6 2011/10/28 09:24:24 bbayragi ship $ */
3
5
6 type block_list is table of number index by binary_integer;
7 --
8 -- Use this procedure to convert the ids prior to a successful
9 -- save as a template
10 -- (otherwise the timecard will be overwritten with the template)
11 --
12 PROCEDURE replace_ids
13 (p_blocks in out nocopy hxc_block_table_type
14 ,p_attributes in out nocopy hxc_attribute_table_type
15 ,p_duplicate_template in BOOLEAN
16 ) is
17
18 cursor c_template_is_a_timecard
19 (p_id in hxc_time_building_blocks.time_building_block_id%type
20 ,p_ovn in hxc_time_building_blocks.object_version_number%type) is
21 select scope
22 from hxc_time_building_blocks
23 where time_building_block_id = p_id
24 and object_version_number = p_ovn;
25
26 l_block_start number := -1000000;
27 l_attribute_start number := -1000000;
28 l_block_index number;
29 l_attribute_index number;
30 l_template_index number;
31 l_block_replacement_ids block_list;
32 l_replace boolean := false;
33 l_dummy_scope hxc_time_building_blocks.scope%type;
34
35 Begin
36
37 --
38 -- First check to see if we need to replace
39 -- the ids
40 --
41 IF(p_duplicate_template = TRUE) THEN
42 l_replace := true;
43 ELSE
44 l_template_index := hxc_timecard_block_utils.find_active_timecard_index
45 (p_blocks);
46
47
48 open c_template_is_a_timecard
49 (p_blocks(l_template_index).time_building_block_id
50 ,p_blocks(l_template_index).object_version_number
51 );
52
53 fetch c_template_is_a_timecard into l_dummy_scope;
54 if(c_template_is_a_timecard%NOTFOUND) then
55 l_replace := true;
56 else
57 if(l_dummy_scope = hxc_timecard.c_timecard_scope) then
58 l_replace := true;
59 else
60 l_replace := false;
61 end if;
62 end if;
63 close c_template_is_a_timecard;
64 END IF;
65 if(l_replace) then
66 --
67 -- replace the block ids
68 --
69
70 l_block_index := p_blocks.first;
71 Loop
72 Exit When Not p_blocks.exists(l_block_index);
73 l_block_replacement_ids(p_blocks(l_block_index).time_building_block_id) := l_block_start;
74 p_blocks(l_block_index).time_building_block_id := l_block_start;
75 p_blocks(l_block_index).object_version_number := 1;
76 p_blocks(l_block_index).changed := hxc_timecard.c_yes;
77 p_blocks(l_block_index).new := hxc_timecard.c_yes;
78 p_blocks(l_block_index).process := hxc_timecard.c_yes;
79 p_blocks(l_block_index).parent_is_new := hxc_timecard.c_yes;
80
81 l_block_start := l_block_start -1;
82 l_block_index := p_blocks.next(l_block_index);
83 End Loop;
84 --
85 -- Update the parent child relationships
86 -- to take account of the new ids
87 --
88 l_block_index := p_blocks.first;
89 Loop
90 Exit When Not p_blocks.exists(l_block_index);
91 if((p_blocks(l_block_index).parent_building_block_id is not null) AND (p_blocks(l_block_index).parent_building_block_id <> -1)) then
92 p_blocks(l_block_index).parent_building_block_id :=
93 l_block_replacement_ids(p_blocks(l_block_index).parent_building_block_id);
94 p_blocks(l_block_index).parent_building_block_ovn := 1;
95 end if;
96 l_block_index := p_blocks.next(l_block_index);
97 End Loop;
98 --
99 -- Update the attributes
100 --
101 l_attribute_index := p_attributes.first;
102 Loop
103 Exit when not p_attributes.exists(l_attribute_index);
104 p_attributes(l_attribute_index).time_attribute_id := l_attribute_start;
105 p_attributes(l_attribute_index).object_version_number := 1;
106 p_attributes(l_attribute_index).building_block_ovn :=1;
107 p_attributes(l_attribute_index).building_block_id :=
108 l_block_replacement_ids(p_attributes(l_attribute_index).building_block_id);
109 p_attributes(l_attribute_index).changed := hxc_timecard.c_yes;
110 p_attributes(l_attribute_index).new := hxc_timecard.c_yes;
111 p_attributes(l_attribute_index).process := hxc_timecard.c_yes;
112 l_attribute_start := l_attribute_start -1;
113 l_attribute_index := p_attributes.next(l_attribute_index);
114 End Loop;
115
116 end if; -- do we actually need to replace these ids
117
118 End replace_ids;
119
120
121 PROCEDURE denormalize_time
122 (p_blocks in out nocopy hxc_block_table_type
123 ,p_mode in varchar2) IS
124
125 l_block_index number;
126
127 BEGIN
128 --
129 -- No need to check inputs, since if invalid mode we just will not do anything.
130 --
131 -- note that we denormalise measure for ALL range start_time stop_times (regardless of scope)
132 -- note also that the UOM for these blocks is HOURS
136 l_block_index := p_blocks.first;
133 -- This is done for ALL scopes of building blocks since we dont need to
134 -- start adding scope specific code.
135
137
138 WHILE ( l_block_index is NOT NULL ) LOOP
139
140 IF (p_mode = 'ADD') THEN
141
142 IF(p_blocks(l_block_index).type = 'RANGE' ) THEN
143 p_blocks(l_block_index).measure:=
144 (hxc_timecard_block_utils.date_value(p_blocks(l_block_index).stop_time)
145 -hxc_timecard_block_utils.date_value(p_blocks(l_block_index).start_time)
146 )*24;
147 p_blocks(l_block_index).unit_of_measure:= 'HOURS';
148 END IF;
149
150 ELSIF (p_mode = 'REMOVE') THEN
151
152 IF(p_blocks(l_block_index).type = 'RANGE' ) THEN
153 p_blocks(l_block_index).measure:= null;
154 -- p_blocks(l_block_index).unit_of_measure:= null;
155 END IF;
156
157 END IF;
158
159 l_block_index := p_blocks.next(l_block_index);
160
161 END LOOP;
162
163 END denormalize_time;
164
165 Procedure set_block_process_flags
166 (p_blocks in out nocopy hxc_block_table_type
167 ) is
168
169
170 l_index NUMBER;
171 l_block HXC_BLOCK_TYPE;
172 l_old_block HXC_BLOCK_TYPE;
173 l_proc varchar2(72):= 'blockattrup.setprocessflags';
174 Begin
175
176 IF g_debug THEN
177 hr_utility.trace('In hxc_block_attribute_update.set_block_process_flags 0');
178 hr_utility.trace('hxc_block_attribute_update.g_tc_api_flag is set to ::'|| hxc_block_attribute_update.g_tc_api_flag);
179 END IF;
180
181
182 l_index := p_blocks.first;
183
184 LOOP
185 EXIT WHEN NOT p_blocks.exists(l_index);
186
187 l_block := p_blocks(l_index);
188
189 if(hxc_timecard_block_utils.is_new_block(l_block)) then
190
191 IF g_debug THEN
192 hr_utility.trace('In hxc_block_attribute_update.set_block_process_flags 1');
193 END IF;
194
195 hxc_block_attribute_update.g_tc_api_flag := 'N'; -- Added for Bug 12774447
196
197 IF g_debug THEN
198 hr_utility.trace('In hxc_block_attribute_update.g_tc_api_flag ::'|| hxc_block_attribute_update.g_tc_api_flag);
199 END IF;
200
201 if(hxc_timecard_block_utils.is_active_block(l_block)) then
202 p_blocks(l_index).process := 'Y';
203 else
204 p_blocks(l_index).process := 'N';
205 end if;
206
207 else
208 begin
209 l_old_block := hxc_timecard_block_utils.build_block
210 (p_time_building_block_id => l_block.time_building_block_id
211 ,p_time_building_block_ovn => l_block.object_version_number
212 );
213
214 IF g_debug THEN
215 hr_utility.trace('In hxc_block_attribute_update.set_block_process_flags 2');
216 hr_utility.trace('calling hxc_timecard_block_utils.blocks_are_different proc.');
217 END IF;
218
219
220
221 if(hxc_timecard_block_utils.blocks_are_different
222 (p_block1 => l_block
223 ,p_block2 => l_old_block
224 )
225 ) then
226
227
228
229 p_blocks(l_index).process := 'Y';
230
231 else
232 p_blocks(l_index).process := 'N';
233 p_blocks(l_index).changed := 'N';
234 end if;
235
236 IF g_debug THEN
237 hr_utility.trace('In hxc_block_attribute_update.g_tc_api_flag ::'|| hxc_block_attribute_update.g_tc_api_flag);
238 hr_utility.trace('p_blocks(l_index).process ::'|| p_blocks(l_index).process);
239 hr_utility.trace('p_blocks(l_index).changed ::'|| p_blocks(l_index).changed);
240 END IF;
241
242 exception
243 when others then
244 p_blocks(l_index).process := 'N';
245
246 end;
247
248 if(hxc_timecard_block_utils.parent_has_changed(p_blocks,p_blocks(l_index).parent_building_block_id)) then
249 p_blocks(l_index).process := 'Y';
250 end if;
251
252 end if;
253
254 l_index := p_blocks.next(l_index);
255
256 END LOOP;
257
258 End set_block_process_flags;
259
260 Procedure set_attribute_process_flags
261 (p_attributes in out nocopy hxc_attribute_table_type
262 ) is
263
264 l_index NUMBER;
265 l_attribute HXC_ATTRIBUTE_TYPE;
266 l_old_attribute HXC_ATTRIBUTE_TYPE;
267
268 l_test boolean;
269
270 Begin
271
272 IF g_debug THEN
273 hr_utility.trace('In hxc_block_attribute_update.set_attribute_process_flags 0');
274 hr_utility.trace('hxc_block_attribute_update.g_tc_api_flag is set to ::'|| hxc_block_attribute_update.g_tc_api_flag);
275 END IF;
276
277
278 l_index := p_attributes.first;
279
280 LOOP
281 EXIT WHEN NOT p_attributes.exists(l_index);
282
283 l_attribute := p_attributes(l_index);
284
285 if(l_attribute.new='Y') then
286
287 p_attributes(l_index).process := 'Y';
288
289 IF g_debug THEN
290 hr_utility.trace('In hxc_block_attribute_update.set_attribute_process_flags 1');
291 END IF;
292
293 hxc_block_attribute_update.g_tc_api_flag := 'N'; -- Added for Bug 12774447
294
295 IF g_debug THEN
296 hr_utility.trace('hxc_block_attribute_update.g_tc_api_flag is set to ::'|| hxc_block_attribute_update.g_tc_api_flag);
297 END IF;
298
299 else
300 if(NOT hxc_timecard_attribute_utils.is_system_context(l_attribute)) then
301 begin
302 l_old_attribute := hxc_timecard_attribute_utils.build_attribute
306 ,p_time_building_block_ovn => l_attribute.building_block_ovn
303 (p_time_attribute_id => l_attribute.time_attribute_id
304 ,p_object_version_number => l_attribute.object_version_number
305 ,p_time_building_block_id => l_attribute.building_block_id
307 );
308
309 if(hxc_timecard_attribute_utils.attributes_are_different
310 (p_attribute1 => l_attribute
311 ,p_attribute2 => l_old_attribute
312 )
313 ) then
314 p_attributes(l_index).process := 'Y';
315
316 -- Bug 10077002
317 -- Copy the old attribute if its a payroll element and a different one
318 -- from the old element entered -- happens when an attribute is changed
319 -- without building block change.
320 IF l_attribute.attribute_category LIKE 'ELEMENT%'
321 AND l_old_attribute.attribute_category LIKE 'ELEMENT%'
322 AND l_attribute.attribute_category <> l_old_attribute.attribute_category
323 THEN
324 hr_utility.trace(' New element added for '||l_attribute.building_block_id);
325 pay_hxc_deposit_interface.g_old_attribute(TO_NUMBER(l_attribute.building_block_id)) :=
326 l_old_attribute;
327 END IF;
328
329 else
330 p_attributes(l_index).process := 'N'; --3025733
331 end if;
332
333 IF g_debug THEN
334 hr_utility.trace('hxc_block_attribute_update.g_tc_api_flag is set to ::'|| hxc_block_attribute_update.g_tc_api_flag);
335 hr_utility.trace('In p_attributes(l_index).process ::'|| p_attributes(l_index).process);
336 END IF;
337
338 if p_attributes(l_index).process='N' then --3025733
339 p_attributes(l_index).changed:='N';
340 end if;
341
342 exception
343 when others then
344 null;
345 end;
346 end if; -- is this a system context, and therefore we don't care about differences.
347
348 end if;
349
350 l_index := p_attributes.next(l_index);
351
352 END LOOP;
353
354 End set_attribute_process_flags;
355
356 --
357 -- Procedure added for version 115.1
358 --
359 Procedure set_corresponding_attributes
360 (p_attributes in out nocopy hxc_attribute_table_type
361 ,p_attribute_index in number
362 ,p_process_value in varchar2
363 ) is
364
365 l_attribute_index number;
366 l_block_id number;
367
368 Begin
369
370 l_block_id := p_attributes(p_attribute_index).building_block_id;
371
372 l_attribute_index := p_attributes.first;
373
374 Loop
375 Exit when ((l_attribute_index=p_attribute_index) or (not p_attributes.exists(l_attribute_index)));
376 if(p_attributes(l_attribute_index).building_block_id = l_block_id) then
377 p_attributes(l_attribute_index).process := p_process_value;
378 end if;
379 l_attribute_index := p_attributes.next(l_attribute_index);
380 End Loop;
381
382 End set_corresponding_attributes;
383
384 Procedure set_dependent_process_flags
385 (p_blocks in out nocopy hxc_block_table_type
386 ,p_attributes in out nocopy hxc_attribute_table_type
387 ) is
388
389 l_block_index NUMBER;
390 l_attribute_index NUMBER;
391 l_attribute hxc_attribute_type;
392 l_process_blocks block_list;
393
394 Begin
395 --
396 -- For performance, remove the nested loops.
397 -- Use an indexed PL/SQL table for better
398 -- performnace.
399 --
400 -- 1. Build the index of blocks to process
401 -- based on the block process flags
402 --
403 l_block_index := p_blocks.first;
404 Loop
405 Exit when not p_blocks.exists(l_block_index);
406
407 if(p_blocks(l_block_index).process = hxc_timecard.c_yes) then
408 l_process_blocks(p_blocks(l_block_index).time_building_block_id) := l_block_index;
409 end if;
410
411 l_block_index := p_blocks.next(l_block_index);
412 End Loop;
413 --
414 -- 2. Loop over all the attributes and set the corresponding
415 -- process flag for them if the process flag of the block is
416 -- set. We do this twice to make sure that we get *all* the
417 -- attributes set correctly. The reason for this is that the
418 -- attribute set is not ordered by process flag. So attribute n
419 -- might be set to be processed, but attribute n-1 and n-5 say,
420 -- which correspond to the same block are not set to be processed
421 -- if we didn't do this twice, the n-1 and n-5 attributes would
422 -- not get processed properly along with there block and the
423 -- n attribute.
424 --
425 for i in 1..2 Loop
426 l_attribute_index := p_attributes.first;
427 Loop
428 Exit when not p_attributes.exists(l_attribute_index);
429
430 if(p_attributes(l_attribute_index).process = hxc_timecard.c_yes) then
431 l_process_blocks(p_attributes(l_attribute_index).building_block_id):= l_attribute_index;
432 -- p_attributes(l_attribute_index).changed := hxc_timecard.c_yes; -- SHIV
433 else
434 if(l_process_blocks.exists(p_attributes(l_attribute_index).building_block_id))then
435 -- p_attributes(l_attribute_index).changed := hxc_timecard.c_yes; --Doubt
436 p_attributes(l_attribute_index).process := hxc_timecard.c_yes; --Doubt
437 else
438 -- p_attributes(l_attribute_index).changed := hxc_timecard.c_no;
439 p_attributes(l_attribute_index).process := hxc_timecard.c_no;
440 end if;
441 end if;
445 --
442 l_attribute_index := p_attributes.next(l_attribute_index);
443 End Loop;
444 end loop;
446 -- 3. Now finally, we loop over the blocks again, and set the process
447 -- and changed flags appropriately in case any of the attributes
448 -- had been changed, but the blocks had not.
449 --
450 l_block_index := p_blocks.first;
451 Loop
452 Exit when not p_blocks.exists(l_block_index);
453 if(l_process_blocks.exists(p_blocks(l_block_index).time_building_block_id)) then
454 p_blocks(l_block_index).process := hxc_timecard.c_yes;
455 else
456 p_blocks(l_block_index).process := hxc_timecard.c_no;
457 end if;
458
459 -- Modified the following PA Validation code for bug 12916249
460 hr_utility.trace(' Allow Deletion - Profile Value: ' ||FND_PROFILE.VALUE('HXC_ALLW_TC_DEL_CLSD_PRJ'));
461
462 IF NVL(FND_PROFILE.VALUE('HXC_ALLW_TC_DEL_CLSD_PRJ'),'N') = 'N'
463 THEN
464 -- This is for PA validation
465 hr_utility.trace(' Prof In IF ');
466
467 if(hxc_timecard_block_utils.parent_has_changed(p_blocks,p_blocks(l_block_index).parent_building_block_id)) then
468 p_blocks(l_block_index).changed := hxc_timecard.c_yes;
469 end if;
470 END IF;
471
472 l_block_index := p_blocks.next(l_block_index);
473 End Loop;
474
475 End set_dependent_process_flags;
476
477 Procedure set_process_flags
478 (p_blocks in out nocopy hxc_block_table_type
479 ,p_attributes in out nocopy hxc_attribute_table_type
480 ) is
481 Begin
482
483 IF g_debug THEN
484 hr_utility.trace('In hxc_block_attribute_update.set_process_flage 0');
485 END IF;
486
487 hxc_block_attribute_update.g_tc_api_flag := null; -- Added for Bug 12774447
488
489 set_block_process_flags(p_blocks);
490 set_attribute_process_flags(p_attributes);
491 set_dependent_process_flags
492 (p_blocks
493 ,p_attributes
494 );
495
496 End set_process_flags;
497
498 END hxc_block_attribute_update;
[Home] [Help]