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