[Home] [Help]
PACKAGE BODY: APPS.HXC_SELF_SERVICE_TIMECARD
Source
1 PACKAGE BODY hxc_self_service_timecard AS
2 /* $Header: hxctctprt.pkb 120.40.12020000.2 2012/09/13 09:11:24 asrajago ship $ */
3
4 g_package varchar2(30) := 'hxc_self_service_timecard.';
5 g_one_day NUMBER := (1-1/24/3600);
6 -- Constants for fragment view
7 c_for_fragment CONSTANT VARCHAR2(8) := 'FRAGMENT';
8 c_for_approver CONSTANT VARCHAR2(8) := 'APPROVER';
9
10 g_debug boolean := hr_utility.debug_enabled;
11
12 -- Types created for the Zero Hrs Template Enhancement.
13
14 TYPE v_att_ids IS RECORD (
15 ATTRIBUTE_CATEGORY HXC_LAYOUT_COMP_QUALIFIERS.QUALIFIER_ATTRIBUTE26%type,
16 ATTRIBUTE_COLUMN_NAME HXC_LAYOUT_COMP_QUALIFIERS.QUALIFIER_ATTRIBUTE27%type
17 );
18
19 TYPE r_att_ids IS TABLE OF v_att_ids
20 INDEX BY BINARY_INTEGER;
21
22
23 TYPE v_del_bb_ids IS RECORD (
24 TIME_BUILDING_BLOCK_ID NUMBER (15));
25
26 TYPE r_del_bb_ids IS TABLE OF v_del_bb_ids
27 INDEX BY BINARY_INTEGER;
28
29 --Function to get the timecard layout id.
30
31 FUNCTION get_timecard_layout_id(p_attribute_array IN HXC_ATTRIBUTE_TABLE_TYPE) RETURN varchar2
32 is
33 l_index number;
34
35 BEGIN
36 l_index := p_attribute_array.FIRST;
37 WHILE l_index IS NOT NULL
38 LOOP
39 IF(p_attribute_array(l_index).ATTRIBUTE_CATEGORY = 'LAYOUT') THEN
40 RETURN p_attribute_array(l_index).ATTRIBUTE1;
41 END IF;
42 l_index := p_attribute_array.NEXT(l_index);
43 END LOOP;
44 RETURN NULL;
45 end get_timecard_layout_id;
46
47 --Function to get the display attributes for the corresponding timecard layout.
48
49 PROCEDURE get_layout_display_attributes(p_layout_id in varchar2,
50 p_att_ids in out nocopy r_att_ids)
51 IS
52 cursor cur_layout_attributes(P_LAYOUT_ID in varchar2) is
53 select QUALIFIER_ATTRIBUTE26,UPPER(QUALIFIER_ATTRIBUTE27)
54 from HXC_LAYOUT_COMP_QUALIFIERS qualifiers
55 where
56 exists
57 (select LAYOUT_COMPONENT_ID from HXC_LAYOUT_COMPONENTS COMPONENTS where
58 layout_id =P_LAYOUT_ID and COMPONENTS.LAYOUT_COMPONENT_ID = qualifiers.LAYOUT_COMPONENT_ID )
59 and UPPER(QUALIFIER_ATTRIBUTE27) LIKE 'ATTRIBUTE%' and QUALIFIER_ATTRIBUTE_CATEGORY<>'HIDDEN_FIELD';
60 l_attribute HXC_LAYOUT_COMP_QUALIFIERS.QUALIFIER_ATTRIBUTE26%type;
61 l_attribute_category HXC_LAYOUT_COMP_QUALIFIERS.QUALIFIER_ATTRIBUTE27%type;
62 l_index number;
63 begin
64
65 l_index := 0;
66 OPEN cur_layout_attributes(p_layout_id);
67 LOOP
68 FETCH cur_layout_attributes INTO l_attribute_category,l_attribute;
69 EXIT WHEN cur_layout_attributes%NOTFOUND;
70 p_att_ids(l_index).ATTRIBUTE_CATEGORY := l_attribute_category;
71 p_att_ids(l_index).attribute_column_name := l_attribute;
72 l_index := l_index+1;
73 end loop;
74 end get_layout_display_attributes;
75
76
77 FUNCTION chk_template_override_appr_set(p_template_attributes IN HXC_ATTRIBUTE_TABLE_TYPE)
78 RETURN BOOLEAN IS
79 l_attribute_index NUMBER;
80 BEGIN
81 l_attribute_index := p_template_attributes.first;
82 LOOP
83 EXIT WHEN NOT p_template_attributes.exists(l_attribute_index);
84 IF (p_template_attributes(l_attribute_index).ATTRIBUTE_CATEGORY = 'APPROVAL') THEN
85 RETURN true;
86 END IF;
87 l_attribute_index := p_template_attributes.next(l_attribute_index);
88 END LOOP;
89 return false;
90 END chk_template_override_appr_set;
91
92 -- remove the override approver.
93 PROCEDURE remove_override_approver(p_timecard_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE)
94 IS
95 l_attribute_index NUMBER;
96 BEGIN
97 l_attribute_index := p_timecard_attributes.first;
98 LOOP
99 EXIT WHEN NOT p_timecard_attributes.exists(l_attribute_index);
100 IF (p_timecard_attributes(l_attribute_index).ATTRIBUTE_CATEGORY = 'APPROVAL') THEN
101 p_timecard_attributes.delete(l_attribute_index);
102 EXIT;
103 END IF;
104 l_attribute_index := p_timecard_attributes.next(l_attribute_index);
105 END LOOP;
106 END remove_override_approver;
107
108
109 --Removing the redudant attribute set from the last timecard when it is applied over a timecard, with zero
110 --Hrs Tempalte preference set.
111
112 PROCEDURE remove_redundant_attributesets(
113 p_timecard_blocks IN HXC_BLOCK_TABLE_TYPE
114 ,p_timecard_attributes IN HXC_ATTRIBUTE_TABLE_TYPE
115 ,p_zero_template_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
116 ,p_zero_template_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
117 )
118 IS
119
120 -- Type to contain the detail building blocks id which needs to be removed as index.
121 -- This would be indexed by timebuilding block id and a dummy value is stored.
122 -- Thus we dont need to loop thru this table and we can use the exists operator to
123 -- check while deletion.
124
125 TYPE v_bb_ids IS RECORD (
126 DUMMY VARCHAR2(1) );
127
128 TYPE r_bb_ids IS TABLE OF v_bb_ids
129 INDEX BY BINARY_INTEGER;
130
131
132 l_del_bb_ids r_bb_ids;
133
134
135 l_timecard_detail_attributes HXC_ATTRIBUTE_TABLE_TYPE;
136 l_zero_hrs_temp_dtl_attr HXC_ATTRIBUTE_TABLE_TYPE;
137
138 l_index BINARY_INTEGER;
139 l_index_day BINARY_INTEGER;
140 l_index_detail BINARY_INTEGER;
141 l_zero_hrs_index_detail BINARY_INTEGER;
142
143 l_tc_det_att_index BINARY_INTEGER;
144 l_zero_temp_det_att_index BINARY_INTEGER;
145 l_sub_index_detail BINARY_INTEGER;
146
147 l_det_row_found BOOLEAN;
148
149 l_attributes_table r_att_ids;
150
151 l_layout_id HXC_TIME_ATTRIBUTES.ATTRIBUTE1%TYPE;
152 l_attributes_table_index number;
153 l_building_block_id hxc_time_building_blocks.TIME_BUILDING_BLOCK_ID%TYPE;
154
155 BEGIN
156
157 --This procedure would simply build two temporary detail attributes
158 --array for the newly entered/existing timecard details and the
159 --zero_hrs_template details. Then it will compare only the display
160 --attributes in them based on the layout.
161
162 --If any attribute sets is matched, then the corresponding building
163 --block id would be stored in a temp.table and later the corresponding
164 --block and the attribute would be cleared from the zero_hrs_template
165 --details.
166
167
168 -- Initialize the Temp Attribute Table
169 l_zero_hrs_temp_dtl_attr := HXC_ATTRIBUTE_TABLE_TYPE ();
170 l_timecard_detail_attributes := HXC_ATTRIBUTE_TABLE_TYPE ();
171
172
173
174 -- Fetch all the detail attributes of the Zero Hrs Template records.
175 l_zero_hrs_index_detail := NULL;
176 l_zero_hrs_index_detail := p_zero_template_blocks.FIRST;
177
178 --First Capturing all the Zero Hrs template details attributes sets. Later we will
179 --loop through the each timecard detail attribute sets and compare for redundancy.
180
181 --Capturing the Timecard Detail Attribute Sets.
182
183 -- Initialize the indices.
184 --l_del_index := 1;
185 l_tc_det_att_index := 1;
186
187 --First Fetching the Layout Details.
188 l_layout_id := get_timecard_layout_id(p_timecard_attributes);
189 get_layout_display_attributes(l_layout_id,l_attributes_table);
190
191
192
193 -- Fetch all the detail attributes of the new/existing Timecard details.
194 l_index_detail := NULL;
195 l_index_detail := p_timecard_blocks.FIRST;
196
197 WHILE l_index_detail IS NOT NULL -- (While Loop)
198 LOOP
199 IF (p_timecard_blocks (l_index_detail).SCOPE = 'DETAIL'
200 AND p_timecard_blocks (l_index_detail).date_to =
201 fnd_date.date_to_canonical (hr_general.end_of_time)
202 )
203 THEN
204
205 -- Get all the ATTRIBUTES for this DETAIL Building Block into a Temp Table
206 -- Loop thru the attributes
207 l_index :=NULL;
208 l_index := p_timecard_attributes.FIRST;
209 if(l_timecard_detail_attributes.count>0) then
210 l_timecard_detail_attributes.delete;
211 end if;
212 l_tc_det_att_index := 1;
213 WHILE l_index IS NOT NULL
214 LOOP
215 IF (p_timecard_attributes (l_index).BUILDING_BLOCK_ID =
216 p_timecard_blocks(l_index_detail).time_building_block_id
217 )
218 THEN
219 --Get all the ATTRIBUTES for this DETAIL Building Block
220 --into a Temp Table
221 l_timecard_detail_attributes.EXTEND;
222 l_timecard_detail_attributes(l_tc_det_att_index) := p_timecard_attributes(l_index);
223 l_tc_det_att_index := l_timecard_detail_attributes.LAST +1;
224 END IF; -- attribute BB_ID = Time BB_ID
225 l_index := p_timecard_attributes.NEXT (l_index);
226 END LOOP; -- l_index IS NOT NULL
227
228 l_zero_hrs_index_detail := NULL;
229 l_zero_hrs_index_detail := p_zero_template_blocks.FIRST;
230
231 WHILE l_zero_hrs_index_detail IS NOT NULL -- (While Loop)
232 LOOP
233 IF (p_zero_template_blocks (l_zero_hrs_index_detail).SCOPE = 'DETAIL'
234 AND p_zero_template_blocks (l_zero_hrs_index_detail).date_to =
235 fnd_date.date_to_canonical (hr_general.end_of_time)
236 ) AND (NOT l_del_bb_ids.EXISTS(p_zero_template_blocks (l_zero_hrs_index_detail).TIME_BUILDING_BLOCK_ID))
237 THEN
238 -- Initialize the Temp Attribute Table
239 if(l_zero_hrs_temp_dtl_attr.count>0) then
240 l_zero_hrs_temp_dtl_attr.delete;
241 end if;
242
243 l_zero_temp_det_att_index := 1;
244 -- Get all the ATTRIBUTES for this DETAIL Building Block
245 -- into a Temp Table
246 l_index :=NULL;
247 l_index := p_zero_template_attributes.FIRST;
248
249 WHILE l_index IS NOT NULL
250 LOOP
251 IF (p_zero_template_attributes(l_index).BUILDING_BLOCK_ID = p_zero_template_blocks(l_zero_hrs_index_detail).time_building_block_id)
252 THEN
253
254 -- Get all the ATTRIBUTES for this DETAIL Building Block into a Temp Table
255 l_zero_hrs_temp_dtl_attr.EXTEND;
256 l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index) := p_zero_template_attributes(l_index);
257 l_zero_temp_det_att_index := l_zero_hrs_temp_dtl_attr.LAST +1;
258
259 END IF;
260 l_index := p_zero_template_attributes.NEXT (l_index);
261 END LOOP; -- l_index IS NOT NULL
262
263 l_det_row_found := false;
264 l_tc_det_att_index := null;
265 l_tc_det_att_index := l_timecard_detail_attributes.FIRST;
266
267 WHILE l_tc_det_att_index IS NOT NULL
268 LOOP
269
270 -- Start the inner Detail loop
271 -- Open up the Zero Hrs template attribute sets.
272
273 l_zero_temp_det_att_index := null;
274 l_zero_temp_det_att_index := l_zero_hrs_temp_dtl_attr.FIRST;
275
276 WHILE l_zero_temp_det_att_index IS NOT NULL
277 LOOP
278 --Only if the attribute category matches we loop thru... otherwise we will look for the next record.
279 -- Also, the ELEMENT% attribute category are not considered and only 'OTL_ALIAS%' is considered.
280 IF (l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE_CATEGORY =l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE_CATEGORY)
281 AND ((l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE_CATEGORY NOT LIKE ('ELEMENT%')) OR (l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE_CATEGORY NOT LIKE ('ELEMENT%') )) THEN
282 l_attributes_table_index := l_attributes_table.first;
283
284 while l_attributes_table_index IS NOT NULL
285 LOOP
286 --Check the condition only if the attribute category matches.
287 if(l_attributes_table(l_attributes_table_index).ATTRIBUTE_CATEGORY = l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE_CATEGORY) THEN --Just to make sure only needed attributes are compared.
288 IF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE1') THEN
289 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE1,-9999) =
290 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE1,-9999)) THEN
291 l_det_row_found :=TRUE;
292
293 ELSE
294 l_det_row_found :=FALSE;
295 END IF;
296 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE2') THEN
297 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE2,-9999) =
298 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE2,-9999)) THEN
299 l_det_row_found :=TRUE;
300 ELSE
301 l_det_row_found :=FALSE;
302 END IF;
303 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE3') THEN
304
305 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE3,-9999) =
306 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE3,-9999)) THEN
307 l_det_row_found :=TRUE;
308 ELSE
309 l_det_row_found :=FALSE;
310 END IF;
311 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE4') THEN
312
313 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE4,-9999) =
314 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE4,-9999)) THEN
315 l_det_row_found :=TRUE;
316 ELSE
317 l_det_row_found :=FALSE;
318 END IF;
319 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE5') THEN
320
321 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE5,-9999) =
322 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE5,-9999)) THEN
323 l_det_row_found :=TRUE;
324 ELSE
325 l_det_row_found :=FALSE;
326 END IF;
327 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE6') THEN
328
329 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE6,-9999) =
330 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE6,-9999)) THEN
331 l_det_row_found :=TRUE;
332 ELSE
333 l_det_row_found :=FALSE;
334 END IF;
335 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE7') THEN
336
337 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE7,-9999) =
338 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE7,-9999)) THEN
339 l_det_row_found :=TRUE;
340 ELSE
341 l_det_row_found :=FALSE;
342 END IF;
343 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE8') THEN
344 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE8,-9999) =
345 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE8,-9999)) THEN
346 l_det_row_found :=TRUE;
347 ELSE
348 l_det_row_found :=FALSE;
349 END IF;
350 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'Attribute9') THEN
351 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE9,-9999) =
352 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE9,-9999)) THEN
353 l_det_row_found :=TRUE;
354 ELSE
355 l_det_row_found :=FALSE;
356 END IF;
357 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE10') THEN
358
359 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE10,-9999) =
360 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE10,-9999)) THEN
361 l_det_row_found :=TRUE;
362 ELSE
363 l_det_row_found :=FALSE;
364 END IF;
365 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE11') THEN
366 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE11,-9999) =
367 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE11,-9999)) THEN
368 l_det_row_found :=TRUE;
369 ELSE
370 l_det_row_found :=FALSE;
371 END IF;
372 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE12') THEN
373 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE12,-9999) =
374 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE12,-9999)) THEN
375 l_det_row_found :=TRUE;
376 ELSE
377 l_det_row_found :=FALSE;
378 END IF;
379 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE13') THEN
380 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE13,-9999) =
381 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE13,-9999)) THEN
382 l_det_row_found :=TRUE;
383 ELSE
384 l_det_row_found :=FALSE;
385 END IF;
386 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE14') THEN
387 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE14,-9999) =
388 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE14,-9999)) THEN
389 l_det_row_found :=TRUE;
390 ELSE
391 l_det_row_found :=FALSE;
392 END IF;
393 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE15') THEN
394 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE15,-9999) =
395 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE15,-9999)) THEN
396 l_det_row_found :=TRUE;
397 ELSE
398 l_det_row_found :=FALSE;
399 END IF;
400 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE16') THEN
401 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE16,-9999) =
402 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE16,-9999)) THEN
403 l_det_row_found :=TRUE;
404 ELSE
405 l_det_row_found :=FALSE;
406 END IF;
407 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE17') THEN
408 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE17,-9999) =
409 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE17,-9999)) THEN
410 l_det_row_found :=TRUE;
411 ELSE
412 l_det_row_found :=FALSE;
413 END IF;
414 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE18') THEN
415 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE18,-9999) =
416 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE18,-9999)) THEN
417 l_det_row_found :=TRUE;
418 ELSE
419 l_det_row_found :=FALSE;
420 END IF;
421 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE19') THEN
422 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE19,-9999) =
423 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE19,-9999)) THEN
424 l_det_row_found :=TRUE;
425 ELSE
426 l_det_row_found :=FALSE;
427 END IF;
428 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE20') THEN
429 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE20,-9999) =
430 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE20,-9999)) THEN
431 l_det_row_found :=TRUE;
432 ELSE
433 l_det_row_found :=FALSE;
434 END IF;
435 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE21') THEN
436 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE21,-9999) =
437 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE21,-9999)) THEN
438 l_det_row_found :=TRUE;
439 ELSE
440 l_det_row_found :=FALSE;
441 END IF;
442 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE22') THEN
443 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE22,-9999) =
444 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE22,-9999)) THEN
445 l_det_row_found :=TRUE;
446 ELSE
447 l_det_row_found :=FALSE;
448 END IF;
449 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE23') THEN
450 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE23,-9999) =
451 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE23,-9999)) THEN
452 l_det_row_found :=TRUE;
453 ELSE
454 l_det_row_found :=FALSE;
455 END IF;
456 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE24') THEN
457 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE24,-9999) =
458 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE24,-9999)) THEN
459 l_det_row_found :=TRUE;
460 ELSE
461 l_det_row_found :=FALSE;
462 END IF;
463 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE25') THEN
464 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE25,-9999) =
465 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE25,-9999)) THEN
466 l_det_row_found :=TRUE;
467 ELSE
468 l_det_row_found :=FALSE;
469 END IF;
470 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE26') THEN
471 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE26,-9999) =
472 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE26,-9999)) THEN
473 l_det_row_found :=TRUE;
474 ELSE
475 l_det_row_found :=FALSE;
476 END IF;
477 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE27') THEN
478 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE27,-9999) =
479 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE27,-9999)) THEN
480 l_det_row_found :=TRUE;
481 ELSE
482 l_det_row_found :=FALSE;
483 END IF;
484 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE28') THEN
485 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE28,-9999) =
486 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE28,-9999)) THEN
487 l_det_row_found :=TRUE;
488 ELSE
489 l_det_row_found :=FALSE;
490 END IF;
491 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE29') THEN
492 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE29,-9999) =
493 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE29,-9999)) THEN
494 l_det_row_found :=TRUE;
495 ELSE
496 l_det_row_found :=FALSE;
497 END IF;
498 ELSIF(l_attributes_table(l_attributes_table_index).ATTRIBUTE_COLUMN_NAME = 'ATTRIBUTE30') THEN
499 IF(nvl(l_zero_hrs_temp_dtl_attr(l_zero_temp_det_att_index).ATTRIBUTE30,-9999) =
500 nvl(l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE30,-9999)) THEN
501 l_det_row_found :=TRUE;
502 ELSE
503 l_det_row_found :=FALSE;
504 END IF;
505 END IF;
506 IF l_det_row_found = FALSE THEN
507 l_attributes_table_index := NULL;
508 l_zero_temp_det_att_index := null;
509 else
510 l_attributes_table_index := l_attributes_table.NEXT(l_attributes_table_index);
511 end if;
512 else
513 l_attributes_table_index := l_attributes_table.NEXT(l_attributes_table_index);
514 END IF;
515
516 END LOOP;
517
518 END IF;
519
520 if(l_zero_temp_det_att_index is not null) then
521 l_zero_temp_det_att_index := l_zero_hrs_temp_dtl_attr.NEXT(l_zero_temp_det_att_index);
522 END IF;
523 END LOOP;
524 --We dont consider the ELEMENT% Attribute category.
525 if (l_timecard_detail_attributes(l_tc_det_att_index).ATTRIBUTE_CATEGORY NOT LIKE 'ELEMENT%') then
526 IF l_det_row_found = FALSE THEN
527 l_tc_det_att_index :=NULL;
528 else
529 l_tc_det_att_index := l_timecard_detail_attributes.NEXT(l_tc_det_att_index);
530
531 end if;
532 else
533 l_tc_det_att_index := l_timecard_detail_attributes.NEXT(l_tc_det_att_index);
534 end if;
535 END LOOP;
536 END IF;
537
538 IF l_det_row_found -- Got a match
539 THEN
540 -- Adding the Time Building Block id in the temp. table
541 -- Index it by Timebuilding block id.
542 if(NOT l_del_bb_ids.EXISTS(p_zero_template_blocks(l_zero_hrs_index_detail).time_building_block_id)) then
543 l_del_bb_ids(p_zero_template_blocks(l_zero_hrs_index_detail).time_building_block_id).DUMMY :=NULL;
544 END IF;
545 END IF;
546 l_det_row_found :=FALSE;
547 l_zero_hrs_index_detail := p_zero_template_blocks.NEXT (l_zero_hrs_index_detail);
548 END LOOP;
549 END IF;
550 l_index_detail := p_timecard_blocks.NEXT (l_index_detail);
551 END LOOP; -- (While Loop)
552
553 l_index :=NULL;
554 l_index := p_zero_template_blocks.FIRST;
555
556 WHILE l_index IS NOT NULL
557 LOOP
558 IF ( l_del_bb_ids.EXISTS(p_zero_template_blocks (l_index).TIME_BUILDING_BLOCK_ID)
559 )
560 THEN
561 -- Delete Block Row
562 p_zero_template_blocks.DELETE(l_index);
563 END IF; -- attribute BB_ID = Deleted Time BB_ID
564 l_index := p_zero_template_blocks.NEXT (l_index);
565 END LOOP; -- l_index IS NOT NULL
566
567 l_index :=NULL;
568 l_index := p_zero_template_attributes.FIRST;
569
570 WHILE l_index IS NOT NULL
571 LOOP
572 IF ( l_del_bb_ids.EXISTS(p_zero_template_attributes (l_index).BUILDING_BLOCK_ID)
573 )
574 THEN
575
576 -- Delete Attribute Row
577
578 p_zero_template_attributes.DELETE(l_index);
579 END IF; -- attribute BB_ID = Deleted Time BB_ID
580 l_index := p_zero_template_attributes.NEXT (l_index);
581 END LOOP; -- l_index IS NOT NULL
582
583 END remove_redundant_attributesets;
584
585 -- v115.64 ksethi ksethi adding new procedure
586 -- This will convert a Template to a Zero hours template
587
588 PROCEDURE modify_to_zero_hrs_template (
589 p_start_time IN VARCHAR2,
590 p_stop_time IN VARCHAR2,
591 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
592 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
593 p_clear_comment IN VARCHAR2
594 )
595 IS
596
597 -- Local variables
598 l_day_one_time_bb_id NUMBER;
599 l_day_one_ovn NUMBER;
600 l_first_day DATE;
601
602 -- Index variables
603 l_index BINARY_INTEGER;
604 l_index_day BINARY_INTEGER;
605 l_index_detail BINARY_INTEGER;
606 l_det_att_index BINARY_INTEGER;
607 l_det_sub_att_index BINARY_INTEGER;
608 l_sub_index_detail BINARY_INTEGER;
609 l_del_index BINARY_INTEGER;
610
611
612 -- Local Flags
613 l_day_found BOOLEAN;
614 l_det_row_found BOOLEAN;
615
616 -- Local Types used
617 l_detail_attributes HXC_ATTRIBUTE_TABLE_TYPE;
618 l_detail_sub_attributes HXC_ATTRIBUTE_TABLE_TYPE;
619
620
621 l_del_bb_ids r_del_bb_ids;
622
623 --
624
625 l_proc VARCHAR2 (70);
626
627 -- Bug 12323117
628 l_dup_attribute_set_found VARCHAR2(2) := 'N';
629 BEGIN
630
631
632 -- DBMS_PROFILER.START_PROFILER('Kunal');
633 IF g_debug THEN
634 l_proc := 'modify_to_zero_hrs_template';
635 hr_utility.set_location (g_package || l_proc, 120);
636 END IF;
637 -- Get the range calculated
638 l_first_day := fnd_date.canonical_to_date (p_start_time);
639 --
640 -- Get the Time Building Block ID and OVN for the first day
641 --
642 IF g_debug THEN
643 l_index := p_block_array.first;
644 LOOP
645 EXIT WHEN NOT p_block_array.exists(l_index);
646 hr_utility.trace(
647 'RESOURCE_ID :'||p_block_array(l_index).RESOURCE_ID
648 ||'BB id : '||p_block_array(l_index).TIME_BUILDING_BLOCK_ID
649 ||'['||p_block_array(l_index).OBJECT_VERSION_NUMBER||']'
650 ||' PARENT_BUILDING_BLOCK_ID: '||p_block_array(l_index).PARENT_BUILDING_BLOCK_ID
651 ||' DATE_TO: '||p_block_array(l_index).DATE_TO
652 ||' SCOPE: '||p_block_array(l_index).SCOPE
653 ||' MEASURE : '||p_block_array(l_index).MEASURE
654 ||' TRANSLATION_DISPLAY_KEY : '||p_block_array(l_index).TRANSLATION_DISPLAY_KEY
655 ||' START_TIME : '||p_block_array(l_index).START_TIME
656 ||' STOP_TIME : '||p_block_array(l_index).STOP_TIME
657 ||' APPROVAL_STATUS: '||p_block_array(l_index).APPROVAL_STATUS
658 ||' APPROVAL_STYLE_ID: '||p_block_array(l_index).APPROVAL_STYLE_ID
659 );
660
661 l_index := p_block_array.next(l_index);
662 END LOOP;
663
664 l_index := p_attribute_array.first;
665 LOOP
666 EXIT WHEN NOT p_attribute_array.exists(l_index);
667 -- l_attribute := p_attribute_array(l_index);
668
669 hr_utility.trace(
670 'BB id : '||p_attribute_array(l_index).BUILDING_BLOCK_ID
671 ||'['||p_attribute_array(l_index).BUILDING_BLOCK_OVN||']'
672 ||' ATT CAT: '||p_attribute_array(l_index).ATTRIBUTE_CATEGORY
673 ||' ATTRIBUTE1: '||p_attribute_array(l_index).ATTRIBUTE1
674 ||' ATTRIBUTE2: '||p_attribute_array(l_index).ATTRIBUTE2
675 ||' ATTRIBUTE3: '||p_attribute_array(l_index).ATTRIBUTE3
676 ||' ATTRIBUTE4: '||p_attribute_array(l_index).ATTRIBUTE4
677 ||' ATTRIBUTE5: '||p_attribute_array(l_index).ATTRIBUTE5
678 ||' ATTRIBUTE6: '||p_attribute_array(l_index).ATTRIBUTE6
679 ||' ATTRIBUTE7: '||p_attribute_array(l_index).ATTRIBUTE7
680 ||' ATTRIBUTE8: '||p_attribute_array(l_index).ATTRIBUTE8
681 ||' ATTRIBUTE9: '||p_attribute_array(l_index).ATTRIBUTE9
682 ||' ATT ID '||p_attribute_array(l_index).TIME_ATTRIBUTE_ID);
683
684 l_index := p_attribute_array.next(l_index);
685 END LOOP;
686 END IF;
687
688 l_index_day := NULL;
689 l_index_day := p_block_array.FIRST;
690 l_day_found := FALSE ;
691
692 WHILE l_index_day IS NOT NULL
693 LOOP
694 IF ( p_block_array (l_index_day).SCOPE = 'DAY'
695 AND p_block_array (l_index_day).start_time =
696 fnd_date.date_to_canonical (l_first_day)
697 AND p_block_array (l_index_day).date_to =
698 fnd_date.date_to_canonical (hr_general.end_of_time)
699 )
700 THEN
701 -- Store the info in local variables
702 l_day_one_time_bb_id :=p_block_array (l_index_day).TIME_BUILDING_BLOCK_ID;
703 l_day_one_ovn :=p_block_array (l_index_day).OBJECT_VERSION_NUMBER;
704 l_day_found := TRUE ;
705 END IF; -- Scope = DAY
706 IF l_day_found
707 THEN
708 l_index_day := NULL;
709 ELSE
710 l_index_day := p_block_array.NEXT (l_index_day);
711 END IF; -- l_day_found
712 END LOOP; -- l_index_day is not null
713
714 IF g_debug THEN
715 hr_utility.trace('Update all DETAILS to be children of DAY ONE');
716 END IF;
717
718 -- Update all DETAILS to be children of DAY ONE
719 l_index_detail := NULL;
720 l_index_detail := p_block_array.FIRST;
721
722 WHILE l_index_detail IS NOT NULL
723 LOOP
724 IF ( p_block_array (l_index_detail).SCOPE = 'DETAIL'
725 AND p_block_array (l_index_detail).date_to =
726 fnd_date.date_to_canonical (hr_general.end_of_time)
727 AND p_block_array (l_index_detail).PARENT_BUILDING_BLOCK_ID <>
728 l_day_one_time_bb_id
729 -- AND p_block_array (l_index_detail).PARENT_BUILDING_BLOCK_OVN <>
730 -- l_day_one_ovn
731 )
732 THEN
733 -- Update the DETAIL to have the DAY one as its parent
734 p_block_array (l_index_detail).PARENT_BUILDING_BLOCK_OVN := l_day_one_ovn;
735 p_block_array (l_index_detail).PARENT_BUILDING_BLOCK_ID := l_day_one_time_bb_id;
736 END IF; -- Scope = DETAIL
737 l_index_detail := p_block_array.NEXT (l_index_detail);
738 END LOOP; -- l_index_detail is not null
739
740
741 IF g_debug THEN
742 l_index := p_block_array.first;
743 LOOP
744 EXIT WHEN NOT p_block_array.exists(l_index);
745 hr_utility.trace(
746 'RESOURCE_ID :'||p_block_array(l_index).RESOURCE_ID
747 ||'BB id : '||p_block_array(l_index).TIME_BUILDING_BLOCK_ID
748 ||'['||p_block_array(l_index).OBJECT_VERSION_NUMBER||']'
749 ||' PARENT_BUILDING_BLOCK_ID: '||p_block_array(l_index).PARENT_BUILDING_BLOCK_ID
750 ||' DATE_TO: '||p_block_array(l_index).DATE_TO
751 ||' SCOPE: '||p_block_array(l_index).SCOPE
752 ||' MEASURE : '||p_block_array(l_index).MEASURE
753 ||' TRANSLATION_DISPLAY_KEY : '||p_block_array(l_index).TRANSLATION_DISPLAY_KEY
754 ||' START_TIME : '||p_block_array(l_index).START_TIME
755 ||' STOP_TIME : '||p_block_array(l_index).STOP_TIME
756 ||' APPROVAL_STATUS: '||p_block_array(l_index).APPROVAL_STATUS
757 ||' APPROVAL_STYLE_ID: '||p_block_array(l_index).APPROVAL_STYLE_ID
758 );
759
760 l_index := p_block_array.next(l_index);
761 END LOOP;
762 END IF;
763
764 -- Now that all DETAILS are children of DAY ONE,
765 -- Wipe out, such that only one DETAIL stays with one unique ATTRIBUTE SET
766
767 -- Since zero hours template is being used,
768 -- here we remove any DETAIL DDF attributes from the attribute list.
769
770 IF g_debug THEN
771 hr_utility.trace('Remove any DETAIL DDF attributes from the attribute list');
772 END IF;
773
774 l_index :=NULL;
775 l_index := p_attribute_array.FIRST;
776
777 WHILE l_index IS NOT NULL
778 LOOP
779 IF ( p_attribute_array (l_index).ATTRIBUTE_CATEGORY like
780 'PAEXPITDFF%'
781 )
782 THEN
783 -- Delete Attribute Row
784 p_attribute_array.DELETE(l_index);
785 END IF; -- ATTRIBUTE_CATEGORY like 'PAEXPITDFF%'
786 l_index := p_attribute_array.NEXT (l_index);
787 END LOOP; -- l_index IS NOT NULL
788
789
790
791 -- Initialize the Local Attribute Types
792 l_detail_attributes := HXC_ATTRIBUTE_TABLE_TYPE ();
793 l_detail_sub_attributes := HXC_ATTRIBUTE_TABLE_TYPE();
794
795
796 -- Initialize the TBB ID delete table
797 IF (l_del_bb_ids.COUNT > 0)
798 THEN
799 l_del_bb_ids.DELETE;
800 END IF;
801 l_del_index := 1;
802
803 IF g_debug THEN
804 hr_utility.trace('Processing start here...');
805 END IF;
806
807 -- Start with looping thru all the DEATILS
808 l_index_detail := NULL;
809 l_index_detail := p_block_array.FIRST;
810
811 WHILE l_index_detail IS NOT NULL -- (Main Processing)
812 LOOP
813 IF ( p_block_array (l_index_detail).SCOPE = 'DETAIL'
814 AND p_block_array (l_index_detail).date_to =
815 fnd_date.date_to_canonical (hr_general.end_of_time)
816 )
817 THEN
818
819 -- Get all the ATTRIBUTES for this DETAIL Building Block into a Temp Table
820
821
822
823
824 -- Initialize the Temp Attribute Table
825
826 IF (l_detail_attributes.COUNT > 0)
827 THEN
828 l_detail_attributes.DELETE;
829 END IF;
830 l_det_att_index := 1;
831
832 -- Loop thru the attributes
833 l_index :=NULL;
834 l_index := p_attribute_array.FIRST;
835
836 WHILE l_index IS NOT NULL
837 LOOP
838 IF ( p_attribute_array (l_index).BUILDING_BLOCK_ID =
839 p_block_array(l_index_detail).time_building_block_id
840 )
841 THEN
842
843 -- Get all the ATTRIBUTES for this DETAIL Building Block into a Temp Table
844 l_detail_attributes.EXTEND;
845 l_detail_attributes(l_det_att_index) := p_attribute_array(l_index);
846 l_det_att_index := l_detail_attributes.LAST +1;
847
848
849 /* IF g_debug THEN
850 hr_utility.trace('| '||p_attribute_array (l_index).BUILDING_BLOCK_ID||' | '||p_attribute_array (l_index).TIME_ATTRIBUTE_ID||' | '||p_attribute_array (l_index).ATTRIBUTE_CATEGORY);
851 END IF;
852 */
853 END IF; -- attribute BB_ID = Time BB_ID
854 l_index := p_attribute_array.NEXT (l_index);
855 END LOOP; -- l_index IS NOT NULL
856
857 /* IF g_debug THEN
858 hr_utility.trace(p_block_array(l_index_detail).time_building_block_id||' | '||l_detail_attributes.COUNT);
859 END IF;
860 */
861 -- Here we have a list of all attributes for the main DETAIL id.
862 -- Now loop thru the rest of the DETAILS to find other DETAILS
863 -- that have the same set of Attributes as in 'l_detail_attributes'
864 --
865
866 -- Start with looping thru to get all the SUB - DEATILS
867 l_sub_index_detail := NULL;
868 l_sub_index_detail := p_block_array.FIRST;
869
870 WHILE l_sub_index_detail IS NOT NULL -- (Sub Processing)
871 LOOP
872 IF ( p_block_array (l_sub_index_detail).SCOPE = 'DETAIL'
873 AND p_block_array (l_sub_index_detail).date_to =
874 fnd_date.date_to_canonical (hr_general.end_of_time)
875 AND p_block_array(l_sub_index_detail).time_building_block_id <>
876 p_block_array(l_index_detail).time_building_block_id
877
878 )
879 THEN
880 --
881 -- Get all the ATTRIBUTES for this Sub - DETAIL Building Block
882 -- into a Temp Table
883 l_index :=NULL;
884 l_index := p_attribute_array.FIRST;
885
886 -- Initialize the Temp Attribute Table
887
888 IF (l_detail_sub_attributes.COUNT > 0)
889 THEN
890 l_detail_sub_attributes.DELETE;
891 END IF;
892 l_det_sub_att_index := 1;
893
894 WHILE l_index IS NOT NULL
895 LOOP
896 IF ( p_attribute_array (l_index).BUILDING_BLOCK_ID =
897 p_block_array(l_sub_index_detail).time_building_block_id
898 )
899 THEN
900
901 -- Get all the ATTRIBUTES for this DETAIL Building Block into a Temp Table
902 l_detail_sub_attributes.EXTEND;
903 l_detail_sub_attributes(l_det_sub_att_index) := p_attribute_array(l_index);
904 l_det_sub_att_index := l_detail_sub_attributes.LAST +1;
905
906
907 END IF; -- attribute BB_ID = Time BB_ID and
908 -- Sub Detail BB ID <> Detail BB ID
909 l_index := p_attribute_array.NEXT (l_index);
910 END LOOP; -- l_index IS NOT NULL
911
912 -- Just a check to see all is well
913 /* IF g_debug THEN
914 hr_utility.trace(p_block_array(l_index_detail).time_building_block_id||' | '||l_detail_attributes.COUNT||' | '||p_block_array(l_sub_index_detail).time_building_block_id||' | '||l_detail_sub_attributes.COUNT);
915 END IF;
916 */
917
918
919 -- Now compare l_detail_sub_attributes and l_detail_attributes
920 -- If same, then remove the p_block_array(l_sub_index_detail)
921 -- and also add to a removed TBB table so to flush the
922 -- attributes at a later stage.
923
924 -- First check if the size is same
925 -- If yes then enter
926 IF ( l_detail_sub_attributes.COUNT = l_detail_attributes.COUNT )
927 THEN
928 --
929 -- Just a check to see all is well
930 /* IF g_debug THEN
931 hr_utility.trace(p_block_array(l_index_detail).time_building_block_id||' | '||l_detail_attributes.COUNT||' | '||p_block_array(l_sub_index_detail).time_building_block_id||' | '||l_detail_sub_attributes.COUNT);
932 END IF;
933 */
934 -- OK, here we go, Start the Comparison here
935 l_det_sub_att_index := null;
936 l_det_sub_att_index := l_detail_sub_attributes.FIRST;
937 -- Start the Loop
938 WHILE l_det_sub_att_index IS NOT NULL
939 LOOP
940 l_det_row_found := false;
941 l_det_att_index := null;
942 l_det_att_index := l_detail_attributes.FIRST;
943 -- Start the inner Detail loop
944 WHILE l_det_att_index IS NOT NULL
945 LOOP
946
947 -- Make the comparison
948 IF (
949 l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE_CATEGORY =
950 l_detail_attributes(l_det_att_index).ATTRIBUTE_CATEGORY
951 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE1,-9999) =
952 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE1,-9999)
953 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE2,-9999) =
954 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE2,-9999)
955 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE3,-9999) =
956 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE3,-9999)
957 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE4,-9999) =
958 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE4,-9999)
959 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE5,-9999) =
960 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE5,-9999)
961 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE6,-9999) =
962 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE6,-9999)
963 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE7,-9999) =
964 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE7,-9999)
965 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE8,-9999) =
966 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE8,-9999)
967 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE9,-9999) =
968 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE9,-9999)
969 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE10,-9999) =
970 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE10,-9999)
971 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE11,-9999) =
972 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE11,-9999)
973 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE12,-9999) =
974 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE12,-9999)
975 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE13,-9999) =
976 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE13,-9999)
977 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE14,-9999) =
978 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE14,-9999)
979 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE15,-9999) =
980 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE15,-9999)
981 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE16,-9999) =
982 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE16,-9999)
983 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE17,-9999) =
984 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE17,-9999)
985 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE18,-9999) =
986 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE18,-9999)
987 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE19,-9999) =
988 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE19,-9999)
989 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE20,-9999) =
990 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE20,-9999)
991 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE21,-9999) =
992 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE21,-9999)
993 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE22,-9999) =
994 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE22,-9999)
995 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE23,-9999) =
996 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE23,-9999)
997 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE24,-9999) =
998 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE24,-9999)
999 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE25,-9999) =
1000 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE25,-9999)
1001 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE26,-9999) =
1002 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE26,-9999)
1003 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE27,-9999) =
1004 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE27,-9999)
1005 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE28,-9999) =
1006 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE28,-9999)
1007 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE29,-9999) =
1008 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE29,-9999)
1009 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE30,-9999) =
1010 nvl(l_detail_attributes(l_det_att_index).ATTRIBUTE30,-9999)
1011 AND nvl(l_detail_sub_attributes(l_det_sub_att_index).BLD_BLK_INFO_TYPE,-9999) =
1012 nvl(l_detail_attributes(l_det_att_index).BLD_BLK_INFO_TYPE,-9999)
1013 )
1014 THEN
1015 -- Set the Same Attribute FOUND Flag
1016 l_det_row_found := TRUE;
1017 -- Incase we need to ignore DETAIL attributes
1018 /*
1019 ELSE
1020 IF (l_detail_sub_attributes(l_det_sub_att_index).ATTRIBUTE_CATEGORY like
1021 'PAEXPITDFF%'
1022 AND
1023 l_detail_attributes(l_det_att_index).ATTRIBUTE_CATEGORY like
1024 'PAEXPITDFF%' )
1025 THEN
1026 -- Set the Same Attribute FOUND Flag
1027 l_det_row_found := TRUE;
1028 End if;
1029 */
1030 END IF; -- Comparison between the two rows
1031
1032 IF l_det_row_found -- Got a match
1033 THEN
1034 l_det_att_index := NULL;
1035 ELSE
1036 l_det_att_index := l_detail_attributes.NEXT (l_det_att_index);
1037 END IF;
1038 END LOOP; -- l_det_att_index IS NOT NULL
1039 -- Here we have the final result if the row exists or not
1040 --
1041 IF NOT l_det_row_found
1042 THEN
1043 l_det_sub_att_index := NULL;
1044 ELSE -- Carry on to find if the next row exists or not
1045 l_det_sub_att_index := l_detail_sub_attributes.NEXT (l_det_sub_att_index);
1046 END IF; --l_det_row_found
1047 END LOOP; -- l_det_sub_att_index IS NOT NULL
1048
1049 -- Here finally check the flag to determine if the complete
1050 -- Attribute set is same or not
1051 -- If yes, delete the SUB Detail Row
1052 IF l_det_row_found
1053 THEN
1054
1055 -- Check to see if the correct TBB is obtained
1056 /* IF g_debug THEN
1057 hr_utility.trace(p_block_array(l_index_detail).time_building_block_id||' | '||p_block_array(l_sub_index_detail).time_building_block_id||' | ');
1058 END IF;
1059 */
1060 -- First add the TBB ID to the temp table
1061 -- that will be used later to clear the attributes
1062 IF g_debug THEN
1063 hr_utility.trace('Translation Display Key :: '||p_block_array(l_sub_index_detail).translation_display_key);
1064
1065 hr_utility.trace('Column Value ::'||SUBSTR(p_block_array(l_sub_index_detail).translation_display_key,
1066 INSTR(p_block_array(l_sub_index_detail).translation_display_key,'|',1,2)+1,3)
1067 );
1068 END IF;
1069
1070 IF SUBSTR(p_block_array(l_sub_index_detail).translation_display_key,
1071 INSTR(p_block_array(l_sub_index_detail).translation_display_key,'|',1,2)+1,3) = '0' THEN
1072
1073 hr_utility.trace('l_dup_attribute_set_found..make a note of it');
1074
1075 l_dup_attribute_set_found := 'Y';
1076 END IF;
1077
1078
1079 l_del_bb_ids(l_del_index).time_building_block_id := p_block_array(l_sub_index_detail).time_building_block_id;
1080 l_del_index := l_del_index+1;
1081 p_block_array.DELETE(l_sub_index_detail);
1082
1083
1084 END IF; -- l_det_row_found
1085 --
1086 END IF; -- l_detail_sub_attributes.COUNT = l_detail_attributes.COUNT
1087
1088 END IF; -- Scope = DETAIL
1089 -- And Sub Detail ID <> Main Detail ID
1090 --
1091 l_sub_index_detail := p_block_array.NEXT (l_sub_index_detail);
1092 END LOOP; -- l_sub_index_detail is not null -- (Sub Processing)
1093 --
1094
1095 --
1096 -- Fix 115.65 moving before End if Scope = Detail
1097 -- Here we are sure that this is the only Valid Detail ID and hence set
1098 -- the measure to zero
1099 -- Check if we need to null START / STOP TIME or Measure
1100 if (p_block_array(l_index_detail).MEASURE is not null)
1101 then
1102 p_block_array(l_index_detail).MEASURE := 0;
1103 p_block_array(l_index_detail).TRANSLATION_DISPLAY_KEY :=
1104 hxc_trans_display_key_utils.reset_column_index_to_zero(p_block_array(l_index_detail).translation_display_key);
1105
1106 IF (p_clear_comment = 'Y') THEN
1107 p_block_array(l_index_detail).COMMENT_TEXT := null;
1108 END IF;
1109
1110
1111 end if;
1112 if ((p_block_array(l_index_detail).START_TIME is not null) OR
1113 (p_block_array(l_index_detail).STOP_TIME is not null)
1114 )
1115 then
1116 p_block_array(l_index_detail).START_TIME := fnd_date.date_to_canonical(l_first_day);
1117 p_block_array(l_index_detail).STOP_TIME := fnd_date.date_to_canonical(l_first_day);
1118 p_block_array(l_index_detail).TRANSLATION_DISPLAY_KEY :=
1119 hxc_trans_display_key_utils.reset_column_index_to_zero(p_block_array(l_index_detail).translation_display_key);
1120
1121 IF (p_clear_comment = 'Y') THEN
1122 p_block_array(l_index_detail).COMMENT_TEXT := null;
1123 END IF;
1124
1125 end if;
1126 ELSIF (p_block_array (l_index_detail).SCOPE = 'TIMECARD' and (p_clear_comment = 'Y')) THEN
1127 p_block_array(l_index_detail).COMMENT_TEXT := null;
1128
1129 END IF; -- Scope = DETAIL
1130
1131
1132
1133 l_index_detail := p_block_array.NEXT (l_index_detail);
1134 END LOOP; -- l_index_detail is not null (Main Processing)
1135
1136 IF g_debug THEN
1137 hr_utility.trace('Processing ends here...');
1138
1139 l_index := p_block_array.first;
1140 LOOP
1141 EXIT WHEN NOT p_block_array.exists(l_index);
1142 hr_utility.trace(
1143 'RESOURCE_ID :'||p_block_array(l_index).RESOURCE_ID
1144 ||'BB id : '||p_block_array(l_index).TIME_BUILDING_BLOCK_ID
1145 ||'['||p_block_array(l_index).OBJECT_VERSION_NUMBER||']'
1146 ||' PARENT_BUILDING_BLOCK_ID: '||p_block_array(l_index).PARENT_BUILDING_BLOCK_ID
1147 ||' DATE_TO: '||p_block_array(l_index).DATE_TO
1148 ||' SCOPE: '||p_block_array(l_index).SCOPE
1149 ||' MEASURE : '||p_block_array(l_index).MEASURE
1150 ||' TRANSLATION_DISPLAY_KEY : '||p_block_array(l_index).TRANSLATION_DISPLAY_KEY
1151 ||' START_TIME : '||p_block_array(l_index).START_TIME
1152 ||' STOP_TIME : '||p_block_array(l_index).STOP_TIME
1153 ||' APPROVAL_STATUS: '||p_block_array(l_index).APPROVAL_STATUS
1154 ||' APPROVAL_STYLE_ID: '||p_block_array(l_index).APPROVAL_STYLE_ID
1155 );
1156
1157 l_index := p_block_array.next(l_index);
1158 END LOOP;
1159 END IF;
1160 -- Added for Bug 9715896
1161
1162 IF g_debug THEN
1163 hr_utility.trace('In hxc_self_service_timecard.modify_to_zero_hrs_template procedure..');
1164 hr_utility.trace('Before deleting the building blocks with duplicate translation display key...');
1165 hr_utility.trace('printing p_block_array... ');
1166
1167
1168 l_index := p_block_array.first;
1169 LOOP
1170 EXIT WHEN NOT p_block_array.exists(l_index);
1171 hr_utility.trace('> '||
1172 'RESOURCE_ID :'||p_block_array(l_index).RESOURCE_ID
1173 ||'BB id : '||p_block_array(l_index).TIME_BUILDING_BLOCK_ID
1174 ||'['||p_block_array(l_index).OBJECT_VERSION_NUMBER||']'
1175 ||' PARENT_BUILDING_BLOCK_ID: '||p_block_array(l_index).PARENT_BUILDING_BLOCK_ID
1176 ||' DATE_TO: '||p_block_array(l_index).DATE_TO
1177 ||' SCOPE: '||p_block_array(l_index).SCOPE
1178 ||' MEASURE : '||p_block_array(l_index).MEASURE
1179 ||' START_TIME : '||p_block_array(l_index).START_TIME
1180 ||' STOP_TIME : '||p_block_array(l_index).STOP_TIME
1181 ||' TRANSLATION_DISPLAY_KEY : '||p_block_array(l_index).TRANSLATION_DISPLAY_KEY
1182 ||' APPROVAL_STATUS: '||p_block_array(l_index).APPROVAL_STATUS
1183 ||' APPROVAL_STYLE_ID: '||p_block_array(l_index).APPROVAL_STYLE_ID
1184 );
1185
1186 l_index := p_block_array.next(l_index);
1187 END LOOP;
1188
1189 hr_utility.trace('l_del_bb_ids.COUNT ::'||l_del_bb_ids.COUNT);
1190
1191 IF l_del_bb_ids.COUNT > 0 THEN
1192
1193 hr_utility.trace('l_del_bb_ids.FIRST ::'||l_del_bb_ids.FIRST);
1194 hr_utility.trace('l_del_bb_ids.LAST ::'||l_del_bb_ids.LAST);
1195 END IF;
1196
1197 hr_utility.trace('looping through building blocks for dupliate traslation display key...');
1198
1199
1200 END IF;
1201
1202 -- Added for Bug 9715896
1203 -- Now to avoid data corruption in the timecard, delete duplicate timebuilding blocks with same translation display key.
1204
1205 IF l_del_bb_ids.COUNT > 0 THEN
1206 l_del_index := l_del_bb_ids.LAST + 1;
1207 ELSE
1208 l_del_index := 1;
1209 END IF;
1210
1211 l_index_detail := p_block_array.first;
1212 LOOP
1213 EXIT WHEN NOT p_block_array.exists(l_index_detail);
1214
1215 IF p_block_array(l_index_detail).SCOPE = 'DETAIL'
1216 AND p_block_array(l_index_detail).DATE_TO = fnd_date.date_to_canonical (hr_general.end_of_time)
1217 THEN
1218
1219 IF g_debug THEN
1220 hr_utility.trace('p_block_array(l_index_detail).TIME_BUILDING_BLOCK_ID ::'||p_block_array(l_index_detail).TIME_BUILDING_BLOCK_ID );
1221 hr_utility.trace('p_block_array(l_index_detail).START_TIME ::'||p_block_array(l_index_detail).START_TIME );
1222 hr_utility.trace('p_block_array(l_index_detail).STOP_TIME ::'||p_block_array(l_index_detail).STOP_TIME );
1223 hr_utility.trace('p_block_array(l_index_detail).TRANSLATION_DISPLAY_KEY ::'||p_block_array(l_index_detail).TRANSLATION_DISPLAY_KEY );
1224 END IF;
1225
1226 -- now check for duplicate translation display key and delete the block.
1227 l_sub_index_detail := p_block_array.first;
1228 LOOP
1229 EXIT WHEN NOT p_block_array.exists(l_sub_index_detail);
1230 IF p_block_array(l_sub_index_detail).SCOPE = 'DETAIL'
1231 AND p_block_array(l_index_detail).TIME_BUILDING_BLOCK_ID <> p_block_array(l_sub_index_detail).TIME_BUILDING_BLOCK_ID
1232 AND p_block_array(l_index_detail).TRANSLATION_DISPLAY_KEY = p_block_array(l_sub_index_detail).TRANSLATION_DISPLAY_KEY
1233 -- Bug 14588730
1234 -- Added this condition because TCs created from TK/API does not have TDK
1235 -- Such blocks would be initialized with a TDK '0'. All blocks would be duplicate of each other
1236 -- and would get deleted.
1237 AND p_block_array(l_sub_index_detail).TRANSLATION_DISPLAY_KEY <> '0'
1238 THEN
1239 IF g_debug THEN
1240 hr_utility.trace('duplicate found..');
1241 hr_utility.trace('p_block_array(l_sub_index_detail).TIME_BUILDING_BLOCK_ID ::'||p_block_array(l_sub_index_detail).TIME_BUILDING_BLOCK_ID );
1242 hr_utility.trace('p_block_array(l_sub_index_detail).START_TIME ::'||p_block_array(l_sub_index_detail).START_TIME );
1243 hr_utility.trace('p_block_array(l_sub_index_detail).STOP_TIME ::'||p_block_array(l_sub_index_detail).STOP_TIME );
1244 hr_utility.trace('p_block_array(l_sub_index_detail).TRANSLATION_DISPLAY_KEY ::'||p_block_array(l_sub_index_detail).TRANSLATION_DISPLAY_KEY );
1245 END IF;
1246
1247 l_del_bb_ids(l_del_index).time_building_block_id := p_block_array(l_sub_index_detail).time_building_block_id;
1248 l_del_index := l_del_index+1;
1249 p_block_array.DELETE(l_sub_index_detail);
1250
1251 END IF;
1252
1253
1254 l_sub_index_detail := p_block_array.next(l_sub_index_detail);
1255 END LOOP;
1256
1257 END IF;
1258
1259 l_index_detail := p_block_array.next(l_index_detail);
1260 END LOOP;
1261
1262 -- End of code for Bug 9715896
1263
1264 -- Now, for all Building Blocks deleted, also delete the attributes
1265 --
1266
1267 IF (l_del_bb_ids.COUNT > 0)
1268 THEN
1269 -- Loop thru the l_del_bb_ids table
1270 FOR x IN
1271 l_del_bb_ids.FIRST .. l_del_bb_ids.LAST
1272 LOOP
1273 -- Now for every TBB ID in l_del_bb_ids loop thru the attributes
1274 -- and delete if found
1275 -- Find attributes of deleted l_del_bb_ids (x).time_building_block_id
1276 l_index :=NULL;
1277 l_index := p_attribute_array.FIRST;
1278
1279 WHILE l_index IS NOT NULL
1280 LOOP
1281 IF ( p_attribute_array (l_index).BUILDING_BLOCK_ID =
1282 l_del_bb_ids (x).time_building_block_id
1283 )
1284 THEN
1285
1286 -- Delete Attribute Row
1287
1288 p_attribute_array.DELETE(l_index);
1289 END IF; -- attribute BB_ID = Deleted Time BB_ID
1290 l_index := p_attribute_array.NEXT (l_index);
1291 END LOOP; -- l_index IS NOT NULL
1292 END LOOP; -- x IN list_tim_rec_det
1293
1294 END IF; -- (l_del_bb_ids.COUNT > 0)
1295 --
1296 -- DBMS_PROFILER.STOP_PROFILER;
1297
1298 IF g_debug THEN
1299 hr_utility.trace('printing p_block_arrays...before null out of tdk values ');
1300
1301
1302 l_index := p_block_array.first;
1303 LOOP
1304 EXIT WHEN NOT p_block_array.exists(l_index);
1305 hr_utility.trace('> '||
1306 'RESOURCE_ID :'||p_block_array(l_index).RESOURCE_ID
1307 ||'BB id : '||p_block_array(l_index).TIME_BUILDING_BLOCK_ID
1308 ||'['||p_block_array(l_index).OBJECT_VERSION_NUMBER||']'
1309 ||' PARENT_BUILDING_BLOCK_ID: '||p_block_array(l_index).PARENT_BUILDING_BLOCK_ID
1310 ||' DATE_TO: '||p_block_array(l_index).DATE_TO
1311 ||' SCOPE: '||p_block_array(l_index).SCOPE
1312 ||' MEASURE : '||p_block_array(l_index).MEASURE
1313 ||' START_TIME : '||p_block_array(l_index).START_TIME
1314 ||' STOP_TIME : '||p_block_array(l_index).STOP_TIME
1315 ||' TRANSLATION_DISPLAY_KEY : '||p_block_array(l_index).TRANSLATION_DISPLAY_KEY
1316 ||' APPROVAL_STATUS: '||p_block_array(l_index).APPROVAL_STATUS
1317 ||' APPROVAL_STYLE_ID: '||p_block_array(l_index).APPROVAL_STYLE_ID
1318 );
1319
1320 l_index := p_block_array.next(l_index);
1321 END LOOP;
1322 END IF;
1323
1324 --- Bug 8662179
1325 --- Null out translation display key values for all detail blocks.
1326 IF l_dup_attribute_set_found = 'Y' THEN -- Bug 12323117
1327 l_index := p_block_array.first;
1328 LOOP
1329 EXIT WHEN NOT p_block_array.exists(l_index);
1330
1331 IF p_block_array(l_index).SCOPE = 'DETAIL' THEN
1332 p_block_array(l_index).TRANSLATION_DISPLAY_KEY := NULL;
1333 END IF;
1334
1335 l_index := p_block_array.next(l_index);
1336
1337 END LOOP;
1338 END IF;
1339
1340 IF g_debug THEN
1341 l_index := p_block_array.first;
1342 LOOP
1343 EXIT WHEN NOT p_block_array.exists(l_index);
1344 hr_utility.trace('> '||
1345 'RESOURCE_ID :'||p_block_array(l_index).RESOURCE_ID
1346 ||'BB id : '||p_block_array(l_index).TIME_BUILDING_BLOCK_ID
1347 ||'['||p_block_array(l_index).OBJECT_VERSION_NUMBER||']'
1348 ||' PARENT_BUILDING_BLOCK_ID: '||p_block_array(l_index).PARENT_BUILDING_BLOCK_ID
1349 ||' DATE_TO: '||p_block_array(l_index).DATE_TO
1350 ||' SCOPE: '||p_block_array(l_index).SCOPE
1351 ||' MEASURE : '||p_block_array(l_index).MEASURE
1352 ||' START_TIME : '||p_block_array(l_index).START_TIME
1353 ||' STOP_TIME : '||p_block_array(l_index).STOP_TIME
1354 ||' TRANSLATION_DISPLAY_KEY : '||p_block_array(l_index).TRANSLATION_DISPLAY_KEY
1355 ||' APPROVAL_STATUS: '||p_block_array(l_index).APPROVAL_STATUS
1356 ||' APPROVAL_STYLE_ID: '||p_block_array(l_index).APPROVAL_STYLE_ID
1357 );
1358
1359 l_index := p_block_array.next(l_index);
1360 END LOOP;
1361 END IF;
1362
1363
1364
1365 END modify_to_zero_hrs_template;
1366
1367 -- ========================================================================
1368 -- This function gets building block information type id from a field name
1369 -- ========================================================================
1370 FUNCTION get_info_type_id(
1371 p_field_name IN hxc_mapping_components.field_name%TYPE
1372 ,p_retrieval_process_id IN hxc_retrieval_processes.retrieval_process_id%TYPE
1373 )
1374 RETURN hxc_bld_blk_info_types.bld_blk_info_type%TYPE
1375 IS
1376 l_info_type_id hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1377
1378 CURSOR c_info_type_id(
1379 p_field_name IN hxc_mapping_components.field_name%TYPE
1380 ,p_retrieval_process_id IN hxc_retrieval_processes.retrieval_process_id%TYPE
1381 )
1382 IS
1383 SELECT mc.bld_blk_info_type_id
1384 FROM hxc_mapping_components mc
1385 ,hxc_mapping_comp_usages mcu
1386 ,hxc_mappings m
1387 ,hxc_retrieval_processes rp
1388 WHERE upper(mc.field_name) = upper(p_field_name)
1389 AND rp.mapping_id = m.mapping_id
1390 AND rp.retrieval_process_id = p_retrieval_process_id
1391 AND m.mapping_id = mcu.mapping_id
1392 AND mcu.mapping_component_id = mc.mapping_component_id;
1393
1394 BEGIN
1395 OPEN c_info_type_id(p_field_name, p_retrieval_process_id);
1396 FETCH c_info_type_id INTO l_info_type_id;
1397
1398 IF c_info_type_id%NOTFOUND
1399 THEN
1400 CLOSE c_info_type_id;
1401 FND_MESSAGE.set_name('HXC','HXC_NO_MAPPING_COMPONENT');
1402 FND_MESSAGE.RAISE_ERROR;
1403 ELSE
1404 CLOSE c_info_type_id;
1405 END IF;
1406
1407 RETURN l_info_type_id;
1408 END get_info_type_id;
1409
1410
1411 ---------------------------------------------------------------------
1412 -- get building block information type id from a building block
1413 -- information type
1414 ---------------------------------------------------------------------
1415 FUNCTION get_info_type_id_from_type(
1416 p_bld_blk_info_type IN hxc_bld_blk_info_types.bld_blk_info_type%TYPE
1417 )
1418 RETURN hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE
1419 IS
1420 l_bld_blk_info_type_id hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
1421
1422 CURSOR c_info_type_id(
1423 p_bld_blk_info_type IN hxc_bld_blk_info_types.bld_blk_info_type%TYPE
1424 )
1425 IS
1426 SELECT bld_blk_info_type_id
1427 FROM hxc_bld_blk_info_types
1428 WHERE bld_blk_info_type = p_bld_blk_info_type;
1429
1430 BEGIN
1431
1432 l_bld_blk_info_type_id := NULL;
1433
1434 OPEN c_info_type_id(
1435 p_bld_blk_info_type => p_bld_blk_info_type
1436 );
1437
1438 FETCH c_info_type_id INTO l_bld_blk_info_type_id;
1439
1440 IF c_info_type_id%notfound
1441 THEN
1442 --
1443 -- There isn't a corresponding info type id
1444 -- Show an error!
1445 --
1446 CLOSE c_info_type_id;
1447 FND_MESSAGE.SET_NAME('HXC','HXC_NO_BLD_BLK_INFO_TYPE');
1448 FND_MESSAGE.RAISE_ERROR;
1449
1450 ELSE
1451
1452 CLOSE c_info_type_id;
1453
1454 END IF;
1455
1456 RETURN l_bld_blk_info_type_id;
1457 END;
1458
1459 -- -----------------------------------------------------------------
1460 -- get building block information type from a building block
1461 -- information type id
1462 -- -----------------------------------------------------------------
1463 FUNCTION get_info_type(
1464 p_bld_blk_info_type_id IN hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE
1465 )
1466 RETURN hxc_bld_blk_info_types.bld_blk_info_type%TYPE
1467 IS
1468 l_bld_blk_info_type hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1469
1470 CURSOR c_info_type(
1471 p_bld_blk_info_type_id IN hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE
1472 )
1473 IS
1474 SELECT bld_blk_info_type
1475 FROM hxc_bld_blk_info_types
1476 WHERE bld_blk_info_type_id = p_bld_blk_info_type_id;
1477
1478 BEGIN
1479
1480 l_bld_blk_info_type:= NULL;
1481
1482 OPEN c_info_type(
1483 p_bld_blk_info_type_id => p_bld_blk_info_type_id
1484 );
1485
1486 FETCH c_info_type INTO l_bld_blk_info_type;
1487
1488 IF c_info_type%NOTFOUND
1489 THEN
1490 --
1491 -- There isn't a corresponding info type id
1492 -- Show an error!
1493 --
1494 CLOSE c_info_type;
1495 FND_MESSAGE.SET_NAME('HXC','HXC_NO_BLD_BLK_INFO_TYPE');
1496 FND_MESSAGE.RAISE_ERROR;
1497
1498 ELSE
1499
1500 CLOSE c_info_type;
1501
1502 END IF;
1503
1504 RETURN l_bld_blk_info_type;
1505 END;
1506
1507
1508 PROCEDURE build_attribute_detail(
1509 p_process_id IN hxc_retrieval_processes.retrieval_process_id%TYPE
1510 ,p_block_attribute IN OUT NOCOPY HXC_ATTRIBUTE_TYPE
1511 ,p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1512 ,p_template_type IN VARCHAR2
1513 )
1514 IS
1515 l_att_count NUMBER;
1516 l_bld_blk_info_type hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1517 l_info_type_id hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
1518 l_segment hxc_mapping_components.segment%TYPE;
1519
1520 CURSOR csr_segment(
1521 p_retrieval_process_id NUMBER
1522 ,p_attribute_category VARCHAR2
1523 ,p_field_name VARCHAR2
1524 )
1525 IS
1526 select mc.segment
1527 from hxc_mapping_components mc
1528 ,hxc_mapping_comp_usages mcu
1529 ,hxc_mappings m
1530 ,hxc_retrieval_processes rp
1531 ,hxc_bld_blk_info_types bbit
1532 ,hxc_bld_blk_info_type_usages bbui
1533 where rp.mapping_id = m.mapping_id
1534 AND mc.field_name = p_field_name
1535 and rp.retrieval_process_id = p_retrieval_process_id
1536 and m.mapping_id = mcu.mapping_id
1537 and mcu.mapping_component_id = mc.mapping_component_id
1538 and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
1539 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
1540 AND bbit.bld_blk_info_type = p_attribute_category;
1541
1542 BEGIN
1543 l_att_count := p_app_attributes.first;
1544
1545 LOOP
1546 EXIT WHEN NOT p_app_attributes.exists(l_att_count);
1547
1548 IF NVL(p_app_attributes(l_att_count).UPDATED, 'N') <> 'Y'
1549 AND p_app_attributes(l_att_count).time_attribute_id = p_block_attribute.TIME_ATTRIBUTE_ID
1550 THEN
1551 l_info_type_id :=
1552 get_info_type_id(
1553 p_field_name => p_app_attributes(l_att_count).ATTRIBUTE_NAME
1554 ,p_retrieval_process_id => p_process_id
1555 );
1556
1557 l_bld_blk_info_type := get_info_type(l_info_type_id);
1558
1559 --
1560 -- Because 'ELEMENT - XXXXX' typse share the same context definition,
1561 -- we need to use the 'Dummy Element Context' to find the mapping
1562 --
1563 IF SUBSTR(l_bld_blk_info_type, 1, 7) = 'ELEMENT'
1564 THEN
1565 l_bld_blk_info_type := 'Dummy Element Context';
1566 END IF;
1567
1568 --
1569 -- This name value pair belongs to the current attribute record
1570 -- and therefore we should update the record.
1571 -- Fetch the segment associated with this field name, so that we
1572 -- know where to store the modified value.
1573 --
1574 OPEN csr_segment(
1575 p_retrieval_process_id => p_process_id
1576 ,p_attribute_category => l_bld_blk_info_type
1577 ,p_field_name => p_app_attributes(l_att_count).attribute_name
1578 );
1579
1580 FETCH csr_segment INTO l_segment;
1581
1582 IF csr_segment%notfound THEN
1583 --
1584 -- The field specified is not found.
1585 -- We don't know where to put the data
1586 -- Show an error
1587 --
1588 CLOSE csr_segment;
1589 FND_MESSAGE.set_name('HXC','HXC_NO_MAPPING_COMPONENT');
1590 FND_MESSAGE.RAISE_ERROR;
1591 ELSE
1592 --
1593 -- We need to update the appropriate segment in the correct time
1594 -- attribute record.
1595 --
1596 CLOSE csr_segment;
1597
1598 IF l_segment = 'ATTRIBUTE1'
1599 THEN
1600 p_block_attribute.attribute1 := p_app_attributes(l_att_count).attribute_value;
1601 p_app_attributes(l_att_count).updated := 'Y';
1602 ELSIF l_segment = 'ATTRIBUTE2' THEN
1603 p_block_attribute.attribute2 := p_app_attributes(l_att_count).attribute_value;
1604 p_app_attributes(l_att_count).updated := 'Y';
1605 ELSIF l_segment = 'ATTRIBUTE3' THEN
1606 p_block_attribute.attribute3 := p_app_attributes(l_att_count).attribute_value;
1607 p_app_attributes(l_att_count).updated := 'Y';
1608 ELSIF l_segment = 'ATTRIBUTE4' THEN
1609 p_block_attribute.attribute4 := p_app_attributes(l_att_count).attribute_value;
1610 p_app_attributes(l_att_count).updated := 'Y';
1611 ELSIF l_segment = 'ATTRIBUTE5' THEN
1612 p_block_attribute.attribute5 := p_app_attributes(l_att_count).attribute_value;
1613 p_app_attributes(l_att_count).updated := 'Y';
1614 ELSIF l_segment = 'ATTRIBUTE6' THEN
1615 p_block_attribute.attribute6 := p_app_attributes(l_att_count).attribute_value;
1616 p_app_attributes(l_att_count).updated := 'Y';
1617 ELSIF l_segment = 'ATTRIBUTE7' THEN
1618 p_block_attribute.attribute7 := p_app_attributes(l_att_count).attribute_value;
1619 p_app_attributes(l_att_count).updated := 'Y';
1620 ELSIF l_segment = 'ATTRIBUTE8' THEN
1621 p_block_attribute.attribute8 := p_app_attributes(l_att_count).attribute_value;
1622 p_app_attributes(l_att_count).updated := 'Y';
1623 ELSIF l_segment = 'ATTRIBUTE9' THEN
1624 p_block_attribute.attribute9 := p_app_attributes(l_att_count).attribute_value;
1625 p_app_attributes(l_att_count).updated := 'Y';
1626 ELSIF l_segment = 'ATTRIBUTE10' THEN
1627 p_block_attribute.attribute10 := p_app_attributes(l_att_count).attribute_value;
1628 p_app_attributes(l_att_count).updated := 'Y';
1629 ELSIF l_segment = 'ATTRIBUTE11' THEN
1630 p_block_attribute.attribute11 := p_app_attributes(l_att_count).attribute_value;
1631 p_app_attributes(l_att_count).updated := 'Y';
1632 ELSIF l_segment = 'ATTRIBUTE12' THEN
1633 p_block_attribute.attribute12 := p_app_attributes(l_att_count).attribute_value;
1634 p_app_attributes(l_att_count).updated := 'Y';
1635 ELSIF l_segment = 'ATTRIBUTE13' THEN
1636 p_block_attribute.attribute13 := p_app_attributes(l_att_count).attribute_value;
1637 p_app_attributes(l_att_count).updated := 'Y';
1638 ELSIF l_segment = 'ATTRIBUTE14' THEN
1639 p_block_attribute.attribute14 := p_app_attributes(l_att_count).attribute_value;
1640 p_app_attributes(l_att_count).updated := 'Y';
1641 ELSIF l_segment = 'ATTRIBUTE15' THEN
1642 p_block_attribute.attribute15 := p_app_attributes(l_att_count).attribute_value;
1643 p_app_attributes(l_att_count).updated := 'Y';
1644 ELSIF l_segment = 'ATTRIBUTE16' THEN
1645 p_block_attribute.attribute16 := p_app_attributes(l_att_count).attribute_value;
1646 p_app_attributes(l_att_count).updated := 'Y';
1647 ELSIF l_segment = 'ATTRIBUTE17' THEN
1648 p_block_attribute.attribute17 := p_app_attributes(l_att_count).attribute_value;
1649 p_app_attributes(l_att_count).updated := 'Y';
1650 ELSIF l_segment = 'ATTRIBUTE18' THEN
1651 p_block_attribute.attribute18 := p_app_attributes(l_att_count).attribute_value;
1652 p_app_attributes(l_att_count).updated := 'Y';
1653 ELSIF l_segment = 'ATTRIBUTE19' THEN
1654 p_block_attribute.attribute19 := p_app_attributes(l_att_count).attribute_value;
1655 p_app_attributes(l_att_count).updated := 'Y';
1656 ELSIF l_segment = 'ATTRIBUTE20' THEN
1657 p_block_attribute.attribute20 := p_app_attributes(l_att_count).attribute_value;
1658 p_app_attributes(l_att_count).updated := 'Y';
1659 ELSIF l_segment = 'ATTRIBUTE21' THEN
1660 p_block_attribute.attribute21 := p_app_attributes(l_att_count).attribute_value;
1661 p_app_attributes(l_att_count).updated := 'Y';
1662 ELSIF l_segment = 'ATTRIBUTE22' THEN
1663 p_block_attribute.attribute22 := p_app_attributes(l_att_count).attribute_value;
1664 p_app_attributes(l_att_count).updated := 'Y';
1665 ELSIF l_segment = 'ATTRIBUTE23' THEN
1666 p_block_attribute.attribute23 := p_app_attributes(l_att_count).attribute_value;
1667 p_app_attributes(l_att_count).updated := 'Y';
1668 ELSIF l_segment = 'ATTRIBUTE24' THEN
1669 p_block_attribute.attribute24 := p_app_attributes(l_att_count).attribute_value;
1670 p_app_attributes(l_att_count).updated := 'Y';
1671 ELSIF l_segment = 'ATTRIBUTE25' THEN
1672 p_block_attribute.attribute25 := p_app_attributes(l_att_count).attribute_value;
1673 p_app_attributes(l_att_count).updated := 'Y';
1674 ELSIF l_segment = 'ATTRIBUTE26' THEN
1675 p_block_attribute.attribute26 := p_app_attributes(l_att_count).attribute_value;
1676 p_app_attributes(l_att_count).updated := 'Y';
1677 ELSIF l_segment = 'ATTRIBUTE27' THEN
1678 p_block_attribute.attribute27 := p_app_attributes(l_att_count).attribute_value;
1679 p_app_attributes(l_att_count).updated := 'Y';
1680 ELSIF l_segment = 'ATTRIBUTE28' THEN
1681 p_block_attribute.attribute28 := p_app_attributes(l_att_count).attribute_value;
1682 p_app_attributes(l_att_count).updated := 'Y';
1683 ELSIF l_segment = 'ATTRIBUTE29' THEN
1684 p_block_attribute.attribute29 := p_app_attributes(l_att_count).attribute_value;
1685 p_app_attributes(l_att_count).updated := 'Y';
1686 ELSIF l_segment = 'ATTRIBUTE30' THEN
1687 p_block_attribute.attribute30 := p_app_attributes(l_att_count).attribute_value;
1688 p_app_attributes(l_att_count).updated := 'Y';
1689 ELSIF l_segment = 'ATTRIBUTE_CATEGORY' THEN
1690 p_block_attribute.attribute_category := p_app_attributes(l_att_count).attribute_value;
1691 p_app_attributes(l_att_count).updated := 'Y';
1692 END IF;
1693 END IF;
1694
1695 END IF;
1696
1697 l_att_count := p_app_attributes.next(l_att_count);
1698
1699 END LOOP;
1700 END build_attribute_detail;
1701
1702
1703
1704 FUNCTION app_to_block_attributes(
1705 p_app_attributes IN hxc_self_service_time_deposit.app_attributes_info
1706 ,p_process_id IN hxc_retrieval_processes.retrieval_process_id%TYPE
1707 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1708 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
1709 ,p_template_type IN VARCHAR2
1710 )
1711 RETURN HXC_ATTRIBUTE_TABLE_TYPE
1712 IS
1713 l_block_attributes HXC_ATTRIBUTE_TABLE_TYPE;
1714 l_app_attributes hxc_self_service_time_deposit.app_attributes_info;
1715 l_info_type_id hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
1716 l_app_attribute_index NUMBER;
1717 l_block_attribute_index NUMBER := 0;
1718
1719 BEGIN
1720 l_block_attributes := HXC_ATTRIBUTE_TABLE_TYPE();
1721
1722 l_app_attributes := p_app_attributes;
1723 l_app_attribute_index := l_app_attributes.first;
1724
1725 LOOP
1726 EXIT WHEN NOT l_app_attributes.exists(l_app_attribute_index);
1727
1728 IF NVL(l_app_attributes(l_app_attribute_index).UPDATED, 'N') <> 'Y'
1729 THEN
1730 -- build a new time attribute record
1731 l_block_attributes.extend;
1732 l_block_attribute_index := l_block_attribute_index + 1;
1733
1734 l_block_attributes(l_block_attribute_index) :=
1735 HXC_ATTRIBUTE_TYPE(
1736 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1737 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1738 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1739 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1740
1741 l_block_attributes(l_block_attribute_index).TIME_ATTRIBUTE_ID
1742 := l_app_attributes(l_app_attribute_index).TIME_ATTRIBUTE_ID;
1743
1744 l_block_attributes(l_block_attribute_index).BUILDING_BLOCK_ID
1745 := l_app_attributes(l_app_attribute_index).BUILDING_BLOCK_ID;
1746
1747 l_block_attributes(l_block_attribute_index).BLD_BLK_INFO_TYPE
1748 := l_app_attributes(l_app_attribute_index).BLD_BLK_INFO_TYPE;
1749
1750 l_block_attributes(l_block_attribute_index).ATTRIBUTE_CATEGORY
1751 := l_app_attributes(l_app_attribute_index).BLD_BLK_INFO_TYPE;
1752
1753 l_info_type_id
1754 := get_info_type_id(
1755 p_field_name => l_app_attributes(l_app_attribute_index).ATTRIBUTE_NAME
1756 ,p_retrieval_process_id => p_process_id
1757 );
1758
1759 l_block_attributes(l_block_attribute_index).BLD_BLK_INFO_TYPE_ID
1760 := l_info_type_id;
1761
1762 l_block_attributes(l_block_attribute_index).OBJECT_VERSION_NUMBER
1763 := 1;
1764
1765 l_block_attributes(l_block_attribute_index).NEW
1766 := 'Y';
1767
1768 --set changed flag to indicate this is generated by applying a template
1769 l_block_attributes(l_block_attribute_index).CHANGED := 'TEMPLATE';
1770
1771 -- go through all the app attributes to fill in attribute1..30.
1772 build_attribute_detail(
1773 p_process_id => p_process_id
1774 ,p_block_attribute => l_block_attributes(l_block_attribute_index)
1775 ,p_app_attributes => l_app_attributes
1776 ,p_template_type => p_template_type
1777 );
1778
1779 END IF;
1780
1781 l_app_attribute_index := l_app_attributes.next(l_app_attribute_index);
1782 END LOOP;
1783
1784 -- create an attribute for LAYOUT
1785 l_block_attributes.extend;
1786 l_block_attribute_index := l_block_attribute_index + 1;
1787
1788 l_block_attributes(l_block_attribute_index) :=
1789 HXC_ATTRIBUTE_TYPE(
1790 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1791 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1792 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1793 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1794
1795
1796 l_block_attributes(l_block_attribute_index).TIME_ATTRIBUTE_ID := 0;
1797 l_block_attributes(l_block_attribute_index).BUILDING_BLOCK_ID := p_timecard_id;
1798 l_block_attributes(l_block_attribute_index).BLD_BLK_INFO_TYPE := 'LAYOUT';
1799 l_block_attributes(l_block_attribute_index).ATTRIBUTE_CATEGORY := 'LAYOUT';
1800 l_block_attributes(l_block_attribute_index).ATTRIBUTE1
1801 := hxc_preference_evaluation.resource_preferences(
1802 p_resource_id,
1803 'TC_W_TCRD_LAYOUT',
1804 1);
1805 l_block_attributes(l_block_attribute_index).ATTRIBUTE2
1806 := hxc_preference_evaluation.resource_preferences(
1807 p_resource_id,
1808 'TC_W_TCRD_LAYOUT',
1809 2);
1810 l_block_attributes(l_block_attribute_index).ATTRIBUTE3
1811 := hxc_preference_evaluation.resource_preferences(
1812 p_resource_id,
1813 'TC_W_TCRD_LAYOUT',
1814 3);
1815
1816 l_block_attributes(l_block_attribute_index).BLD_BLK_INFO_TYPE_ID
1817 := get_info_type_id_from_type('LAYOUT');
1818 l_block_attributes(l_block_attribute_index).OBJECT_VERSION_NUMBER := 1;
1819 l_block_attributes(l_block_attribute_index).NEW := 'Y';
1820 l_block_attributes(l_block_attribute_index).CHANGED := 'N';
1821
1822
1823 RETURN l_block_attributes;
1824
1825 END app_to_block_attributes;
1826
1827
1828
1829 -----------------------------------------------------------------------------
1830 -- This procedure will assignment dummy block ids for blocks starting with -2
1831 -- in order to avoid conflicts somehow.
1832 -- (NOTE: block ids cannot be -1!!! because of deposit logics)
1833 -----------------------------------------------------------------------------
1834 PROCEDURE assign_block_ids(
1835 p_start_id IN NUMBER
1836 ,p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
1837 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
1838 )
1839 IS
1840 l_timecard_block_id hxc_time_building_blocks.time_building_block_id%TYPE := p_start_id;
1841 l_day_block_id hxc_time_building_blocks.time_building_block_id%TYPE
1842 := l_timecard_block_id;
1843 l_detail_block_id hxc_time_building_blocks.time_building_block_id%TYPE
1844 := p_start_id - p_blocks.count;
1845 l_old_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
1846 l_block_index NUMBER;
1847 l_second_block_index NUMBER;
1848 l_attribute_index NUMBER;
1849 BEGIN
1850 l_block_index := p_blocks.first;
1851 LOOP
1852 EXIT WHEN NOT p_blocks.EXISTS(l_block_index);
1853
1854 l_old_block_id := p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID;
1855
1856 IF p_blocks(l_block_index).SCOPE = 'TIMECARD'
1857 THEN
1858 p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID := l_timecard_block_id;
1859 ELSIF p_blocks(l_block_index).SCOPE = 'DAY'
1860 THEN
1861 l_day_block_id := l_day_block_id - 1;
1862 p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID := l_day_block_id;
1863 ELSIF p_blocks(l_block_index).SCOPE = 'DETAIL'
1864 THEN
1865 l_detail_block_id := l_detail_block_id + 1;
1866 p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID := l_detail_block_id;
1867 END IF;
1868
1869 -- change parent referenes to this block
1870 l_second_block_index := p_blocks.first;
1871 LOOP
1872 EXIT WHEN NOT p_blocks.EXISTS(l_second_block_index);
1873
1874 IF p_blocks(l_second_block_index).PARENT_BUILDING_BLOCK_ID = l_old_block_id
1875 THEN
1876 p_blocks(l_second_block_index).PARENT_BUILDING_BLOCK_ID
1877 := p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID;
1878 END IF;
1879 l_second_block_index := p_blocks.next(l_second_block_index);
1880 END LOOP;
1881
1882 -- change attribute reference to this block
1883 l_attribute_index := p_attributes.first;
1884 LOOP
1885 EXIT WHEN NOT p_attributes.EXISTS(l_attribute_index);
1886
1887 IF P_attributes(l_attribute_index).BUILDING_BLOCK_ID = l_old_block_id
1888 THEN
1889 p_attributes(l_attribute_index).BUILDING_BLOCK_ID
1890 := p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID;
1891 p_attributes(l_attribute_index).OBJECT_VERSION_NUMBER := 1;
1892
1893 END IF;
1894
1895 l_attribute_index := p_attributes.next(l_attribute_index);
1896 END LOOP;
1897
1898 l_block_index := p_blocks.next(l_block_index);
1899 END LOOP;
1900
1901 END assign_block_ids;
1902
1903
1904 FUNCTION get_pref_template(
1905 p_resource_id IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
1906 )
1907 RETURN VARCHAR2
1908 IS
1909 l_template_code VARCHAR2(200) := NULL;
1910
1911 BEGIN
1912
1913 l_template_code := hxc_preference_evaluation.resource_preferences(
1914 p_resource_id,
1915 'TC_W_TMPLT_DFLT_VAL_USR',
1916 1
1917 );
1918
1919 IF l_template_code IS NULL
1920 THEN
1921 l_template_code := hxc_preference_evaluation.resource_preferences(
1922 p_resource_id,
1923 'TC_W_TMPLT_DFLT_VAL_ADMIN',
1924 1
1925 );
1926 END IF;
1927
1928 RETURN l_template_code;
1929 END get_pref_template;
1930
1931 PROCEDURE get_template_info(
1932 p_template_code IN VARCHAR2
1933 ,p_template_handle OUT NOCOPY VARCHAR2
1934 ,p_template_action OUT NOCOPY VARCHAR2
1935 )
1936 IS
1937 l_separator_pos NUMBER := 0;
1938 l_template_type VARCHAR2(50) := '';
1939 l_template_handle VARCHAR2(500) := '';
1940 l_template_action VARCHAR2(20) := '';
1941 BEGIN
1942 l_separator_pos := INSTR(p_template_code, '|');
1943
1944 IF l_separator_pos = 0
1945 THEN
1946 p_template_handle := NULL;
1947 p_template_action := 'INVALID';
1948 RETURN;
1949 END IF;
1950
1951 l_template_type := SUBSTR(p_template_code, 1, l_separator_pos - 1);
1952 l_template_handle := SUBSTR(p_template_code, l_separator_pos + 1);
1953
1954 IF l_template_type = 'DYNAMIC'
1955 THEN
1956 -- we need to retreive a dynamic template for a specific appl
1957 l_separator_pos := INSTR(l_template_handle, '|');
1958 IF l_separator_pos = 0
1959 THEN
1960 p_template_handle := NULL;
1961 p_template_action := 'INVALID';
1962 RETURN;
1963 END IF;
1964
1965 l_template_type := SUBSTR(l_template_handle, 1, l_separator_pos - 1);
1966 p_template_handle := SUBSTR(l_template_handle, l_separator_pos + 1);
1967
1968 IF l_template_type = 'APP'
1969 THEN
1970 p_template_action := 'APP';
1971 ELSE
1972 p_template_action := 'SYS';
1973 END IF;
1974 ELSE -- if not dynamic
1975 p_template_handle := l_template_handle;
1976 p_template_action := 'STATIC';
1977 END IF;
1978 END get_template_info;
1979
1980
1981 -- ==================================================================
1982 -- This procedure examines and updates blocks info returned by an
1983 -- application autogen routine. It also returns the id of the block
1984 -- which has a scope of 'TIMECARD'. It sets p_timecard_found to false
1985 -- if block count is 0, or if the structure of timecard data is wrong.
1986 -- ==================================================================
1987
1988 PROCEDURE update_blocks(
1989 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1990 ,p_approval_status IN hxc_time_building_blocks.approval_status%TYPE
1991 ,p_approval_style IN hxc_time_building_blocks.approval_style_id%TYPE
1992 ,p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
1993 ,p_timecard_id OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
1994 ,p_timecard_found OUT NOCOPY BOOLEAN
1995 )
1996 IS
1997 l_block_index NUMBER;
1998 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE := 0;
1999 l_approval_style_id hxc_time_building_blocks.approval_style_id%TYPE;
2000 BEGIN
2001 p_timecard_found := FALSE;
2002
2003 l_approval_style_id := TO_NUMBER(p_approval_style);
2004
2005 l_block_index := p_blocks.first;
2006
2007 LOOP
2008 EXIT WHEN NOT p_blocks.exists(l_block_index);
2009
2010 --populate the following fields as there is no guanrantee the
2011 --autogen routines will populate them correctly
2012 p_blocks(l_block_index).PARENT_IS_NEW := 'Y';
2013 p_blocks(l_block_index).OBJECT_VERSION_NUMBER := 1;
2014 p_blocks(l_block_index).APPROVAL_STATUS := p_approval_status;
2015 p_blocks(l_block_index).RESOURCE_ID := p_resource_id;
2016 p_blocks(l_block_index).RESOURCE_TYPE := 'PERSON';
2017 p_blocks(l_block_index).APPROVAL_STYLE_ID := p_approval_style;
2018 p_blocks(l_block_index).DATE_FROM := fnd_date.date_to_canonical(SYSDATE);
2019 p_blocks(l_block_index).DATE_TO := fnd_date.date_to_canonical(hr_general.end_of_time);
2020 p_blocks(l_block_index).PARENT_BUILDING_BLOCK_OVN := 1;
2021 p_blocks(l_block_index).NEW := 'Y';
2022
2023 IF p_blocks(l_block_index).SCOPE = 'TIMECARD'
2024 THEN
2025 p_timecard_id := p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID;
2026 p_timecard_found := TRUE;
2027 p_blocks(l_block_index).STOP_TIME := to_char(
2028 fnd_date.canonical_to_date(p_blocks(l_block_index).STOP_TIME),'YYYY/MM/DD')
2029 || ' 23:59:59'; --fnd_date.date_to_canonical(SYSDATE);
2030 END IF;
2031
2032
2033 IF p_blocks(l_block_index).SCOPE = 'DAY'
2034 THEN
2035 p_blocks(l_block_index).STOP_TIME := to_char(
2036 fnd_date.canonical_to_date(p_blocks(l_block_index).STOP_TIME),'YYYY/MM/DD')
2037 || ' 23:59:59'; --fnd_date.date_to_canonical(SYSDATE);
2038 END IF;
2039
2040 l_block_index := p_blocks.next(l_block_index);
2041 END LOOP;
2042
2043 END update_blocks;
2044
2045
2046 -- given a block (day, timecard), find the record index
2047 -- need p_found flag because block index could be any number
2048 PROCEDURE find_block(
2049 p_blocks IN HXC_BLOCK_TABLE_TYPE
2050 ,p_new_block IN HXC_BLOCK_TYPE
2051 ,p_index OUT NOCOPY NUMBER
2052 ,p_found OUT NOCOPY BOOLEAN
2053 )
2054 IS
2055 l_block_index NUMBER;
2056 l_date_to DATE;
2057 BEGIN
2058 l_block_index := p_blocks.first;
2059 LOOP
2060 EXIT WHEN NOT p_blocks.exists(l_block_index);
2061
2062 IF p_blocks(l_block_index).DATE_TO IS NULL
2063 THEN
2064 l_date_to := hr_general.end_of_time;
2065 ELSE
2066 l_date_to := fnd_date.canonical_to_date(p_blocks(l_block_index).DATE_TO);
2067 END IF;
2068
2069 IF p_new_block.SCOPE = 'TIMECARD'
2070 THEN
2071 IF p_blocks(l_block_index).SCOPE = 'TIMECARD'
2072 --array AND NVL(p_blocks(l_block_index).DATE_TO, hr_general.end_of_time) = hr_general.end_of_time
2073 AND l_date_to = hr_general.end_of_time
2074 THEN
2075 p_index := l_block_index;
2076 p_found := TRUE;
2077 RETURN;
2078 END IF;
2079 ELSIF p_new_block.SCOPE = 'DAY'
2080 THEN
2081 IF p_blocks(l_block_index).SCOPE = 'DAY'
2082 --array AND NVL(p_blocks(l_block_index).DATE_TO, hr_general.end_of_time) = hr_general.end_of_time
2083 --array AND TRUNC(p_blocks(l_block_index).START_TIME) = TRUNC(p_new_block.START_TIME)
2084 AND l_date_to = hr_general.end_of_time
2085 AND TRUNC(fnd_date.canonical_to_date(p_blocks(l_block_index).START_TIME))
2086 = TRUNC(fnd_date.canonical_to_date(p_new_block.START_TIME))
2087 THEN
2088 p_index := l_block_index;
2089 p_found := TRUE;
2090 RETURN;
2091 END IF;
2092 END IF;
2093
2094 l_block_index := p_blocks.next(l_block_index);
2095 END LOOP;
2096
2097 -- this should not be happening
2098 p_index := 0;
2099 p_found := FALSE;
2100
2101 END find_block;
2102
2103 --------------------------------------------------------------------------------
2104 -- find next available dummy block id for additional detail blocks
2105 --------------------------------------------------------------------------------
2106 FUNCTION get_next_block_id(
2107 p_blocks IN HXC_BLOCK_TABLE_TYPE
2108 )
2109 RETURN hxc_time_building_blocks.time_building_block_id%TYPE
2110 IS
2111 l_min_id hxc_time_building_blocks.time_building_block_id%TYPE;
2112 l_block_index NUMBER;
2113 BEGIN
2114 -- existing block structure is also generated by applying a template, we need
2115 -- to find the next available dummy block id for additional detail blocks
2116 l_block_index := p_blocks.first;
2117 l_min_id := p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID;
2118 LOOP
2119 EXIT WHEN NOT p_blocks.exists(l_block_index);
2120
2121 IF p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID < l_min_id
2122 THEN
2123 l_min_id := p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID;
2124 END IF;
2125
2126 l_block_index := p_blocks.next(l_block_index);
2127 END LOOP;
2128
2129 IF l_min_id > 0
2130 THEN
2131 l_min_id := -2;
2132 ELSE
2133 l_min_id := l_min_id - 1;
2134 END IF;
2135
2136 RETURN l_min_id;
2137 END get_next_block_id;
2138
2139
2140
2141 FUNCTION get_next_attribute_id(
2142 p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE
2143 )
2144 RETURN NUMBER
2145 IS
2146
2147 l_attribute_index NUMBER;
2148 l_next_att_id NUMBER;
2149 BEGIN
2150 l_next_att_id := -2;
2151
2152 l_attribute_index := p_attributes.first;
2153 LOOP
2154 EXIT WHEN NOT p_attributes.exists(l_attribute_index);
2155
2156 IF p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID <= l_next_att_id
2157 THEN
2158 l_next_att_id := p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID - 1;
2159 END IF;
2160
2161 l_attribute_index := p_attributes.next(l_attribute_index);
2162 END LOOP;
2163
2164 RETURN l_next_att_id;
2165 END;
2166
2167
2168 -- update new attributes to ensure we get unique attribute ids
2169 PROCEDURE assign_attribute_ids(
2170 p_start_id IN NUMBER
2171 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
2172 )
2173 IS
2174 l_new_attribute_id NUMBER := p_start_id;
2175 l_new_attribute_index NUMBER;
2176 BEGIN
2177 l_new_attribute_index := p_attributes.first;
2178 LOOP
2179 EXIT WHEN NOT p_attributes.exists(l_new_attribute_index);
2180
2181 p_attributes(l_new_attribute_index).time_attribute_id := l_new_attribute_id;
2182
2183 l_new_attribute_id := l_new_attribute_id - 1;
2184 l_new_attribute_index := p_attributes.next(l_new_attribute_index);
2185 END LOOP;
2186 END assign_attribute_ids;
2187
2188
2189 PROCEDURE add_attribute(
2190 p_total_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
2191 ,p_new_attribute IN HXC_ATTRIBUTE_TYPE
2192 ,p_new_block_id IN NUMBER
2193 ,p_new_attribute_id IN OUT NOCOPY NUMBER
2194 )
2195 IS
2196 l_attribute_count NUMBER;
2197 BEGIN
2198 l_attribute_count := NVL(p_total_attributes.last, 0);
2199 p_total_attributes.extend;
2200
2201 p_total_attributes(l_attribute_count + 1) := p_new_attribute;
2202 p_total_attributes(l_attribute_count + 1).BUILDING_BLOCK_ID := p_new_block_id;
2203 p_total_attributes(l_attribute_count + 1).TIME_ATTRIBUTE_ID := p_new_attribute_id;
2204
2205 p_new_attribute_id := p_new_attribute_id - 1;
2206 END add_attribute;
2207
2208
2209 PROCEDURE translate_alias(
2210 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
2211 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
2212 ,p_resource_id IN VARCHAR2
2213 ,p_start_time IN VARCHAR2
2214 ,p_stop_time IN VARCHAR2
2215 )
2216 IS
2217 -- l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
2218 -- l_blocks hxc_self_service_time_deposit.timecard_info;
2219 l_messages HXC_MESSAGE_TABLE_TYPE;
2220
2221 BEGIN
2222
2223 --temporary convertion
2224 --l_attributes := hxc_deposit_wrapper_utilities.array_to_attributes(
2225 -- p_attribute_array => p_attribute_array
2226 -- );
2227
2228 --l_blocks := hxc_deposit_wrapper_utilities.array_to_blocks(
2229 -- p_block_array => p_block_array
2230 -- );
2231
2232
2233 HXC_ALIAS_TRANSLATOR.do_retrieval_translation(
2234 p_attributes => p_attribute_array
2235 ,p_blocks => p_block_array
2236 ,p_start_time => FND_DATE.CANONICAL_TO_DATE(p_start_time)
2237 ,p_stop_time => FND_DATE.CANONICAL_TO_DATE(p_stop_time)
2238 ,p_resource_id => p_resource_id
2239 ,p_messages => l_messages
2240 );
2241
2242 --p_attribute_array := hxc_deposit_wrapper_utilities.attributes_to_array(
2243 -- p_attributes => l_attributes
2244 -- );
2245 END translate_alias;
2246
2247
2248 -- v115.58 kSethi adding new procedure
2249 -- to pad missing DAYS incase a template with a shorter time period
2250 -- is applied to a timecard with a greater time period.
2251 PROCEDURE chk_all_days_in_block (
2252 p_resource_id IN VARCHAR2,
2253 p_resource_type IN VARCHAR2,
2254 p_start_time IN VARCHAR2,
2255 p_stop_time IN VARCHAR2,
2256 p_template_code IN VARCHAR2,
2257 p_block_array IN OUT NOCOPY hxc_block_table_type
2258 )
2259 IS
2260 -- Variables....
2261 l_day_exists BOOLEAN;
2262 l_day_found BOOLEAN;
2263 l_cached BOOLEAN := FALSE ;
2264 l_cache_index BINARY_INTEGER;
2265 l_index_day BINARY_INTEGER;
2266 l_last_index BINARY_INTEGER;
2267 l_blocks hxc_block_table_type;
2268 l_next_block_id NUMBER;
2269 l_num_days NUMBER;
2270 l_new_start_time DATE;
2271 l_new_stop_time DATE;
2272 l_dummy_block boolean := FALSE;
2273 --
2274
2275 l_proc VARCHAR2 (70);
2276 BEGIN
2277
2278
2279 IF g_debug THEN
2280 l_proc := 'chk_all_days_in_block';
2281 hr_utility.set_location (g_package || l_proc, 120);
2282 END IF;
2283 -- Get the range calculated
2284 l_new_start_time := fnd_date.canonical_to_date (p_start_time);
2285 l_new_stop_time := fnd_date.canonical_to_date (p_stop_time);
2286 l_num_days := l_new_stop_time - l_new_start_time;
2287
2288 -- Loop for the total number of days in the time period.
2289 WHILE l_num_days <> -1
2290 LOOP
2291
2292 l_index_day := NULL;
2293 l_index_day := p_block_array.FIRST;
2294 l_day_exists := FALSE ;
2295
2296 WHILE l_index_day IS NOT NULL
2297 LOOP
2298 IF ( p_block_array (l_index_day).SCOPE = 'DAY'
2299 AND p_block_array (l_index_day).start_time =
2300 fnd_date.date_to_canonical (l_new_start_time)
2301 AND p_block_array (l_index_day).date_to =
2302 fnd_date.date_to_canonical (hr_general.end_of_time)
2303 AND p_block_array (l_index_day).object_version_number <> -99999
2304 )
2305 THEN
2306 l_day_exists := TRUE ;
2307 END IF; -- Scope = DAY
2308 l_index_day := p_block_array.NEXT (l_index_day);
2309 END LOOP; -- l_index_day is not null
2310
2311 -- Cache information incase an update is needed for further use
2312 IF NOT l_cached
2313 THEN
2314 IF NOT l_day_exists
2315 THEN
2316 -- cache day info that can be used for later updates as well...
2317 l_blocks := hxc_block_table_type ();
2318 l_cache_index := NULL;
2319 l_cache_index := p_block_array.FIRST;
2320 l_day_found := FALSE ;
2321
2322 WHILE l_cache_index IS NOT NULL
2323 LOOP
2324 IF ( p_block_array (l_cache_index).SCOPE = 'DAY'
2325 AND p_block_array (l_cache_index).date_to =
2326 fnd_date.date_to_canonical (hr_general.end_of_time)
2327 )
2328 THEN
2329 l_blocks.EXTEND;
2330 l_blocks (1) := p_block_array (l_cache_index);
2331 l_day_found := TRUE ;
2332 END IF; -- Scope = DAY
2333 IF l_day_found
2334 THEN
2335 l_cache_index := NULL;
2336 ELSE
2337 l_cache_index := p_block_array.NEXT (l_cache_index);
2338 END IF;
2339 END LOOP; -- l_cache_index is not null
2340 l_cached := TRUE ;
2341 END IF; -- l_day_exists
2342 END IF; -- l_cached
2343 IF NOT l_day_exists
2344 THEN
2345 l_last_index := NULL;
2346 l_last_index := p_block_array.LAST;
2347 l_last_index := l_last_index + 1;
2348 l_next_block_id := get_next_block_id (p_blocks => p_block_array);
2349 p_block_array.EXTEND;
2350
2351 If p_block_array.count < p_block_array.LAST then
2352 l_dummy_block:= true;
2353 else
2354 l_dummy_block:= false;
2355 end if;
2356
2357 p_block_array (l_last_index) :=
2358 hxc_block_type (
2359 l_next_block_id,
2360 l_blocks (1).TYPE,
2361 l_blocks (1).measure,
2362 l_blocks (1).unit_of_measure,
2363 fnd_date.date_to_canonical (l_new_start_time),
2364 TO_CHAR (l_new_start_time, 'YYYY/MM/DD') || ' 23:59:59',
2365 l_blocks (1).parent_building_block_id,
2366 l_blocks (1).parent_is_new,
2367 l_blocks (1).SCOPE,
2368 -99999,
2369 l_blocks (1).approval_status,
2370 l_blocks (1).resource_id,
2371 l_blocks (1).resource_type,
2372 l_blocks (1).approval_style_id,
2373 l_blocks (1).date_from,
2374 l_blocks (1).date_to,
2375 l_blocks (1).comment_text,
2376 l_blocks (1).parent_building_block_ovn,
2377 l_blocks (1).NEW,
2378 l_blocks (1).changed,
2379 l_blocks (1).process,
2380 l_blocks (1).application_set_id,
2381 NULL
2382 );
2383
2384 if l_dummy_block and p_block_array(p_block_array.LAST).TIME_BUILDING_BLOCK_ID is null then
2385 p_block_array.delete(p_block_array.LAST);
2386 end if;
2387
2388 END IF; -- l_day_exists
2389 l_new_start_time := l_new_start_time + 1;
2390 l_num_days := l_num_days - 1;
2391 END LOOP; -- l_num_days <> -1
2392
2393 -- Update OVNs to the correct value
2394 l_last_index := NULL;
2395 l_last_index := p_block_array.FIRST;
2396 WHILE l_last_index IS NOT NULL
2397 LOOP
2398 IF (p_block_array (l_last_index).object_version_number = -99999)
2399 THEN
2400 p_block_array (l_last_index).object_version_number := 1;
2401 END IF;
2402
2403 l_last_index := p_block_array.NEXT (l_last_index);
2404 END LOOP;
2405
2406 -- Temp check note sure if we need, hence not calling....
2407 /* assign_block_ids(
2408 p_start_id => -2
2409 ,p_blocks => p_block_array
2410 ,p_attributes => p_attribute_array
2411 );
2412 */
2413 END chk_all_days_in_block;
2414
2415
2416 PROCEDURE append_blocks(
2417 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
2418 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
2419 ,p_new_blocks IN HXC_BLOCK_TABLE_TYPE
2420 ,p_new_attributes IN HXC_ATTRIBUTE_TABLE_TYPE
2421 ,p_overwrite IN VARCHAR2
2422 ,p_start_time IN VARCHAR2
2423 ,p_stop_time IN VARCHAR2
2424 ,p_resource_id IN VARCHAR2
2425 ,p_resource_type IN VARCHAR2
2426 ,p_template_code IN VARCHAR2
2427 ,p_remove_redundant_entries IN BOOLEAN
2428 )
2429 IS
2430 l_complete_blocks HXC_BLOCK_TABLE_TYPE;
2431 l_complete_attributes HXC_ATTRIBUTE_TABLE_TYPE;
2432 l_new_blocks HXC_BLOCK_TABLE_TYPE;
2433 l_new_blocks_array HXC_BLOCK_TABLE_TYPE;
2434 l_new_attributes HXC_ATTRIBUTE_TABLE_TYPE;
2435 l_next_detail_id hxc_time_building_blocks.time_building_block_id%TYPE;
2436 l_complete_block_count NUMBER;
2437 l_complete_attribute_count NUMBER;
2438 l_new_block_index NUMBER;
2439 l_detail_index NUMBER;
2440 l_old_block_index NUMBER;
2441 l_attribute_index NUMBER;
2442 l_found BOOLEAN;
2443 --
2444 l_block_index NUMBER;
2445 l_new_attribute_index NUMBER;
2446 l_new_attribute_id NUMBER;
2447 l_existing_att_index NUMBER;
2448 l_next_block_id NUMBER;
2449 l_new_att_index NUMBER;
2450 l_timecard_row_count NUMBER;
2451 l_proc VARCHAR2(70);
2452 BEGIN
2453
2454
2455 IF g_debug THEN
2456 l_proc := 'append_blocks';
2457 hr_utility.set_location ( g_package||l_proc, 300);
2458 END IF;
2459
2460 l_new_blocks := p_new_blocks;
2461 l_new_attributes := p_new_attributes;
2462
2463 -- if in blocks is null, we only need to assign block ids for the newly generated
2464 -- blocks and attributes and return them
2465 IF p_block_array.count = 0
2466 THEN
2467 IF g_debug THEN
2468 hr_utility.set_location ( g_package||l_proc, 310);
2469 END IF;
2470
2471 assign_block_ids(
2472 p_start_id => -2
2473 ,p_blocks => l_new_blocks
2474 ,p_attributes => l_new_attributes
2475 );
2476
2477 IF g_debug THEN
2478 hr_utility.set_location ( g_package||l_proc, 320);
2479 END IF;
2480
2481 assign_attribute_ids(
2482 p_start_id => -2
2483 ,p_attributes => l_new_attributes
2484 );
2485
2486 IF g_debug THEN
2487 hr_utility.set_location ( g_package||l_proc, 330);
2488 END IF;
2489
2490
2491 p_block_array := l_new_blocks;
2492
2493 p_attribute_array := l_new_attributes;
2494
2495 --translate alias here
2496 translate_alias(
2497 p_attribute_array => p_attribute_array
2498 ,p_block_array => p_block_array
2499 ,p_resource_id => p_resource_id
2500 ,p_start_time => p_start_time
2501 ,p_stop_time => p_stop_time
2502 );
2503
2504 RETURN;
2505 END IF;
2506
2507 l_complete_blocks := HXC_BLOCK_TABLE_TYPE();
2508 l_complete_blocks := p_block_array;
2509
2510 l_next_block_id := get_next_block_id(p_blocks => l_complete_blocks);
2511
2512 --if overwrite flag is set, end date all the blocks and append new blocks.
2513 --
2514 IF p_overwrite = 'Y'
2515 THEN
2516 IF g_debug THEN
2517 hr_utility.set_location ( g_package||l_proc, 340);
2518 END IF;
2519
2520 --end date all the existing blocks
2521 l_block_index := l_complete_blocks.first;
2522 LOOP
2523 EXIT WHEN NOT l_complete_blocks.exists(l_block_index);
2524
2525 l_complete_blocks(l_block_index).DATE_TO := fnd_date.date_to_canonical(sysdate);
2526
2527 l_block_index := l_complete_blocks.next(l_block_index);
2528 END LOOP;
2529
2530 IF g_debug THEN
2531 hr_utility.set_location ( g_package||l_proc, 350);
2532 END IF;
2533
2534 -- append new blocks
2535 -- before we append new blocks, we need to assign new ids to new blocks
2536
2537 assign_block_ids(
2538 p_start_id => l_next_block_id
2539 ,p_blocks => l_new_blocks
2540 ,p_attributes => l_new_attributes
2541 );
2542
2543 IF g_debug THEN
2544 hr_utility.set_location ( g_package||l_proc, 360);
2545 END IF;
2546
2547 l_block_index := l_complete_blocks.last + 1;
2548 l_new_block_index := l_new_blocks.first;
2549 LOOP
2550 EXIT WHEN NOT l_new_blocks.exists(l_new_block_index);
2551
2552 l_complete_blocks.extend;
2553 l_complete_blocks(l_block_index) := l_new_blocks(l_new_block_index);
2554
2555 l_block_index := l_block_index + 1;
2556 l_new_block_index := l_new_blocks.next(l_new_block_index);
2557 END LOOP;
2558
2559 IF g_debug THEN
2560 hr_utility.set_location ( g_package||l_proc, 370);
2561 END IF;
2562
2563 l_new_attribute_id := get_next_attribute_id(
2564 p_attributes => p_attribute_array
2565 );
2566
2567 assign_attribute_ids(
2568 p_start_id => l_new_attribute_id
2569 ,p_attributes => l_new_attributes
2570 );
2571
2572
2573 p_block_array := l_complete_blocks;
2574
2575 --p_attribute_array := l_new_attributes;
2576
2577 --mbhammar : add the missing blocks before translation, so the new blocks get included in translation
2578 --ref : bug 4996639
2579 IF p_block_array.COUNT > 0 THEN
2580 chk_all_days_in_block(
2581 p_resource_id => p_resource_id
2582 ,p_resource_type => p_resource_type
2583 ,p_start_time => p_start_time
2584 ,p_stop_time => p_stop_time
2585 ,p_template_code => p_template_code
2586 ,p_block_array => p_block_array
2587 );
2588 END IF;
2589
2590 --translate alias here
2591 translate_alias(
2592 p_attribute_array => l_new_attributes
2593 ,p_block_array => p_block_array
2594 ,p_resource_id => p_resource_id
2595 ,p_start_time => p_start_time
2596 ,p_stop_time => p_stop_time
2597 );
2598
2599 --support for CLA
2600 l_new_att_index := NVL(l_new_attributes.last, 0);
2601 FOR i in p_attribute_array.first .. p_attribute_array.last
2602 LOOP
2603 l_new_attributes.extend;
2604
2605 l_new_att_index := l_new_att_index + 1;
2606 l_new_attributes(l_new_att_index) := p_attribute_array(i);
2607 END LOOP;
2608
2609 p_attribute_array := l_new_attributes;
2610
2611 RETURN;
2612 END IF;
2613
2614
2615 -- overwrite flag is not set, we need to end date all the in detail blocks and append
2616 -- new blocks and attributes to the existing block structure. We also need to append
2617 -- comment to the comment_text fields of timecard and day blocks.
2618 l_new_blocks_array := p_new_blocks;
2619 l_timecard_row_count := hxc_trans_display_key_utils.timecard_row_count
2620 (l_complete_blocks);
2621 --translate alias here
2622 translate_alias(
2623 p_attribute_array => l_new_attributes
2624 ,p_block_array => l_new_blocks_array
2625 ,p_resource_id => p_resource_id
2626 ,p_start_time => p_start_time
2627 ,p_stop_time => p_stop_time
2628 );
2629
2630 IF(p_remove_redundant_entries = TRUE) THEN
2631 remove_redundant_attributesets(p_timecard_blocks => p_block_array,
2632 p_timecard_attributes => p_attribute_array,
2633 p_zero_template_blocks => l_new_blocks_array,
2634 p_zero_template_attributes => l_new_attributes);
2635 END IF;
2636
2637 l_complete_attributes := p_attribute_array;
2638
2639 IF((p_overwrite = 'Y')
2640 AND
2641 (NOT chk_template_override_appr_set(l_new_attributes))
2642 )THEN
2643 -- we dont need to retain the overriding approver in the timecard,
2644 -- if the template *doesn't* contain one incase of overwriting.
2645 -- We do it well before the if condition to remove the redundant
2646 -- attributes, because we dont need to check for the following scenario again n again
2647 -- inside the loop.
2648 remove_override_approver(l_complete_attributes);
2649 END IF;
2650
2651 l_new_attribute_id := get_next_attribute_id(
2652 p_attributes => l_complete_attributes
2653 );
2654 IF g_debug THEN
2655 hr_utility.set_location ( g_package||l_proc, 380);
2656 END IF;
2657
2658 l_complete_block_count := l_complete_blocks.last + 1;
2659 l_complete_attribute_count := l_complete_attributes.last + 1;
2660
2661 --changed from p_new_blocks to l_new_blocks_array, since the removal of redundant entries
2662 --is done on l_new_blocks_array.
2663
2664 l_new_block_index := l_new_blocks_array.first;
2665 LOOP
2666 EXIT WHEN NOT l_new_blocks_array.exists(l_new_block_index);
2667
2668 -- we only want to add detail blocks to the corresponding day blocks in
2669 -- the existing block structure
2670 IF l_new_blocks_array(l_new_block_index).SCOPE = 'TIMECARD'
2671 OR l_new_blocks_array(l_new_block_index).SCOPE = 'DAY'
2672 THEN
2673 find_block(
2674 p_blocks => l_complete_blocks
2675 ,p_new_block => l_new_blocks_array(l_new_block_index)
2676 ,p_index => l_old_block_index
2677 ,p_found => l_found
2678 );
2679
2680 IF g_debug THEN
2681 hr_utility.set_location ( g_package||l_proc, 390);
2682 END IF;
2683
2684 -- append comment to corrresponding block
2685 -- attach attributes to corrresponding block
2686 IF l_found
2687 THEN
2688 IF g_debug THEN
2689 hr_utility.set_location ( g_package||l_proc, 400);
2690 END IF;
2691
2692 --append comments
2693 IF l_new_blocks_array(l_new_block_index).COMMENT_TEXT IS NOT NULL
2694 THEN
2695 IF l_complete_blocks(l_old_block_index).COMMENT_TEXT IS NULL
2696 THEN
2697 l_complete_blocks(l_old_block_index).COMMENT_TEXT
2698 := l_new_blocks_array(l_new_block_index).COMMENT_TEXT;
2699 ELSE
2700 l_complete_blocks(l_old_block_index).COMMENT_TEXT
2701 :=
2702 -- Bug 8937768
2703 SUBSTR(l_complete_blocks(l_old_block_index).COMMENT_TEXT
2704 || fnd_global.local_chr(10)
2705 || l_new_blocks_array(l_new_block_index).COMMENT_TEXT,1,2000);
2706 END IF;
2707 END IF;
2708
2709 IF g_debug THEN
2710 hr_utility.set_location ( g_package||l_proc, 410);
2711 END IF;
2712
2713 --append attributes
2714 l_attribute_index := l_new_attributes.first;
2715 LOOP
2716 EXIT WHEN NOT l_new_attributes.exists(l_attribute_index);
2717
2718 IF l_new_attributes(l_attribute_index).BUILDING_BLOCK_ID
2719 = l_new_blocks_array(l_new_block_index).TIME_BUILDING_BLOCK_ID
2720 THEN
2721 --ignore layout and security attributes
2722 --override other attributes
2723 IF l_new_attributes(l_attribute_index).ATTRIBUTE_CATEGORY <> 'LAYOUT'
2724 AND l_new_attributes(l_attribute_index).ATTRIBUTE_CATEGORY <> 'SECURITY'
2725 THEN
2726 --check if there is a redundant attribute, if so, remove it
2727 l_existing_att_index := l_complete_attributes.first;
2728 LOOP
2729 EXIT WHEN NOT l_complete_attributes.exists(l_existing_att_index);
2730
2731 IF l_complete_attributes(l_existing_att_index).BUILDING_BLOCK_ID
2732 = l_complete_blocks(l_old_block_index).TIME_BUILDING_BLOCK_ID
2733 THEN
2734 IF l_complete_attributes(l_existing_att_index).ATTRIBUTE_CATEGORY
2735 = l_new_attributes(l_attribute_index).ATTRIBUTE_CATEGORY
2736 THEN
2737 -- New Code Addced here.
2738 IF ((l_new_attributes(l_attribute_index).ATTRIBUTE_CATEGORY = 'APPROVAL')
2739 AND (p_overwrite <>'Y')
2740 )THEN
2741 -- Retain the Overriding Approver if the template has a NULL entry.
2742 -- while appending.
2743 l_new_attributes(l_attribute_index).ATTRIBUTE10 :=
2744 l_complete_attributes(l_existing_att_index).ATTRIBUTE10;
2745 END IF;
2746 l_complete_attributes.delete(l_existing_att_index);
2747 EXIT;
2748 END IF;
2749 END IF;
2750
2751 l_existing_att_index := l_complete_attributes.next(l_existing_att_index);
2752 END LOOP;
2753
2754 add_attribute(
2755 p_total_attributes => l_complete_attributes
2756 ,p_new_attribute => l_new_attributes(l_attribute_index)
2757 ,p_new_block_id => l_complete_blocks(l_old_block_index).TIME_BUILDING_BLOCK_ID
2758 ,p_new_attribute_id => l_new_attribute_id
2759 );
2760
2761 END IF;
2762 END IF;
2763
2764 l_attribute_index := l_new_attributes.next(l_attribute_index);
2765 END LOOP;
2766
2767 IF g_debug THEN
2768 hr_utility.set_location ( g_package||l_proc, 420);
2769 END IF;
2770
2771 IF l_new_blocks_array(l_new_block_index).SCOPE = 'DAY'
2772 THEN
2773 -- find the details for the day. add them to the existing
2774 -- block structure. We also need to add the attributes associated
2775 -- with the detail blocks to the existing attribute structure.
2776 l_detail_index := l_new_blocks_array.first;
2777 LOOP
2778 EXIT WHEN NOT l_new_blocks_array.exists(l_detail_index);
2779
2780 IF l_new_blocks_array(l_detail_index).SCOPE = 'DETAIL'
2781 AND l_new_blocks_array(l_detail_index).PARENT_BUILDING_BLOCK_ID
2782 = l_new_blocks_array(l_new_block_index).TIME_BUILDING_BLOCK_ID
2783 THEN
2784 -- add detail blocks first
2785 l_complete_blocks.extend;
2786 l_complete_blocks(l_complete_block_count) := l_new_blocks_array(l_detail_index);
2787 l_complete_blocks(l_complete_block_count).TIME_BUILDING_BLOCK_ID
2788 := l_next_block_id;
2789 l_complete_blocks(l_complete_block_count).PARENT_BUILDING_BLOCK_ID
2790 := l_complete_blocks(l_old_block_index).TIME_BUILDING_BLOCK_ID;
2791 l_complete_blocks(l_complete_block_count).PARENT_IS_NEW
2792 := l_complete_blocks(l_old_block_index).NEW;
2793 l_complete_blocks(l_complete_block_count).PARENT_BUILDING_BLOCK_OVN
2794 := l_complete_blocks(l_old_block_index).OBJECT_VERSION_NUMBER;
2795 l_complete_blocks(l_complete_block_count).TRANSLATION_DISPLAY_KEY
2796 := hxc_trans_display_key_utils.new_display_key
2797 (l_complete_blocks(l_complete_block_count).translation_display_key,
2798 l_timecard_row_count
2799 );
2800
2801 -- then add attributes
2802 l_attribute_index := l_new_attributes.first;
2803 LOOP
2804 EXIT WHEN NOT l_new_attributes.exists(l_attribute_index);
2805
2806 IF l_new_attributes(l_attribute_index).BUILDING_BLOCK_ID
2807 = l_new_blocks_array(l_detail_index).TIME_BUILDING_BLOCK_ID
2808 THEN
2809 add_attribute(
2810 p_total_attributes => l_complete_attributes
2811 ,p_new_attribute => l_new_attributes(l_attribute_index)
2812 ,p_new_block_id => l_next_block_id
2813 ,p_new_attribute_id => l_new_attribute_id
2814 );
2815
2816 END IF;
2817
2818 l_attribute_index := l_new_attributes.next(l_attribute_index);
2819 END LOOP;
2820
2821 l_complete_block_count := l_complete_block_count + 1;
2822 l_next_block_id := l_next_block_id - 1;
2823 END IF;
2824
2825 l_detail_index := l_new_blocks_array.next(l_detail_index);
2826 END LOOP;
2827 END IF;
2828 END IF;
2829 END IF;
2830
2831 l_new_block_index := l_new_blocks_array.next(l_new_block_index);
2832 END LOOP;
2833
2834 IF g_debug THEN
2835 hr_utility.set_location ( g_package||l_proc, 430);
2836 END IF;
2837
2838 p_block_array := l_complete_blocks;
2839
2840 p_attribute_array := l_complete_attributes;
2841
2842 END append_blocks;
2843
2844
2845 /*
2846 FUNCTION attributes_to_array(
2847 p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
2848 )
2849 RETURN HXC_ATTRIBUTE_TABLE_TYPE
2850 IS
2851 l_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
2852 l_attribute HXC_ATTRIBUTE_TYPE;
2853 l_array_index NUMBER := 0;
2854 l_attribute_index NUMBER;
2855 l_proc VARCHAR2(50);
2856 BEGIN
2857
2858
2859 IF g_debug THEN
2860 l_proc := 'attributes_to_array';
2861 hr_utility.set_location ( g_package||l_proc, 10);
2862 END IF;
2863
2864 --initialize attribute array
2865 l_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
2866
2867 l_attribute_index := p_attributes.first;
2868 LOOP
2869 EXIT WHEN NOT p_attributes.exists(l_attribute_index);
2870
2871 l_array_index := l_array_index + 1;
2872 l_attribute_array.extend;
2873
2874 l_attribute_array(l_array_index) :=
2875 HXC_ATTRIBUTE_TYPE(
2876 p_attributes(l_attribute_index).TIME_ATTRIBUTE_ID
2877 ,p_attributes(l_attribute_index).BUILDING_BLOCK_ID
2878 ,p_attributes(l_attribute_index).ATTRIBUTE_CATEGORY
2879 ,p_attributes(l_attribute_index).ATTRIBUTE1
2880 ,p_attributes(l_attribute_index).ATTRIBUTE2
2881 ,p_attributes(l_attribute_index).ATTRIBUTE3
2882 ,p_attributes(l_attribute_index).ATTRIBUTE4
2883 ,p_attributes(l_attribute_index).ATTRIBUTE5
2884 ,p_attributes(l_attribute_index).ATTRIBUTE6
2885 ,p_attributes(l_attribute_index).ATTRIBUTE7
2886 ,p_attributes(l_attribute_index).ATTRIBUTE8
2887 ,p_attributes(l_attribute_index).ATTRIBUTE9
2888 ,p_attributes(l_attribute_index).ATTRIBUTE10
2889 ,p_attributes(l_attribute_index).ATTRIBUTE11
2890 ,p_attributes(l_attribute_index).ATTRIBUTE12
2891 ,p_attributes(l_attribute_index).ATTRIBUTE13
2892 ,p_attributes(l_attribute_index).ATTRIBUTE14
2893 ,p_attributes(l_attribute_index).ATTRIBUTE15
2894 ,p_attributes(l_attribute_index).ATTRIBUTE16
2895 ,p_attributes(l_attribute_index).ATTRIBUTE17
2896 ,p_attributes(l_attribute_index).ATTRIBUTE18
2897 ,p_attributes(l_attribute_index).ATTRIBUTE19
2898 ,p_attributes(l_attribute_index).ATTRIBUTE20
2899 ,p_attributes(l_attribute_index).ATTRIBUTE21
2900 ,p_attributes(l_attribute_index).ATTRIBUTE22
2901 ,p_attributes(l_attribute_index).ATTRIBUTE23
2902 ,p_attributes(l_attribute_index).ATTRIBUTE24
2903 ,p_attributes(l_attribute_index).ATTRIBUTE25
2904 ,p_attributes(l_attribute_index).ATTRIBUTE26
2905 ,p_attributes(l_attribute_index).ATTRIBUTE27
2906 ,p_attributes(l_attribute_index).ATTRIBUTE28
2907 ,p_attributes(l_attribute_index).ATTRIBUTE29
2908 ,p_attributes(l_attribute_index).ATTRIBUTE30
2909 ,p_attributes(l_attribute_index).BLD_BLK_INFO_TYPE_ID
2910 ,p_attributes(l_attribute_index).OBJECT_VERSION_NUMBER
2911 ,p_attributes(l_attribute_index).NEW
2912 ,p_attributes(l_attribute_index).CHANGED
2913 ,p_attributes(l_attribute_index).BLD_BLK_INFO_TYPE
2914 ,'N'
2915 ,NULL);
2916
2917 l_attribute_index := p_attributes.next(l_attribute_index);
2918 END LOOP;
2919
2920 IF g_debug THEN
2921 hr_utility.set_location ( g_package||l_proc, 20);
2922 END IF;
2923 RETURN l_attribute_array;
2924 END attributes_to_array;
2925 */
2926
2927 /*
2928 FUNCTION blocks_to_array(
2929 p_blocks IN hxc_self_service_time_deposit.timecard_info
2930 )
2931 RETURN HXC_BLOCK_TABLE_TYPE
2932 IS
2933 l_block_array HXC_BLOCK_TABLE_TYPE;
2934 l_array_index NUMBER := 0;
2935 l_block_index NUMBER;
2936 l_proc VARCHAR2(50);
2937 l_block HXC_BLOCK_TYPE;
2938
2939 BEGIN
2940
2941
2942 IF g_debug THEN
2943 l_proc := 'blocks_to_array';
2944 hr_utility.set_location ( g_package||l_proc, 10);
2945 END IF;
2946
2947 l_block_array := HXC_BLOCK_TABLE_TYPE();
2948
2949 l_block_index := p_blocks.first;
2950 LOOP
2951 EXIT WHEN NOT p_blocks.exists(l_block_index);
2952
2953 l_array_index := l_array_index + 1;
2954 l_block_array.extend;
2955
2956 l_block_array(l_array_index) :=
2957 HXC_BLOCK_TYPE(
2958 p_blocks(l_block_index).TIME_BUILDING_BLOCK_ID
2959 ,p_blocks(l_block_index).TYPE
2960 ,p_blocks(l_block_index).MEASURE
2961 ,p_blocks(l_block_index).UNIT_OF_MEASURE
2962 ,fnd_date.date_to_canonical(p_blocks(l_block_index).START_TIME)
2963 ,fnd_date.date_to_canonical(p_blocks(l_block_index).STOP_TIME)
2964 ,p_blocks(l_block_index).PARENT_BUILDING_BLOCK_ID
2965 ,p_blocks(l_block_index).PARENT_IS_NEW
2966 ,p_blocks(l_block_index).SCOPE
2967 ,p_blocks(l_block_index).OBJECT_VERSION_NUMBER
2968 ,p_blocks(l_block_index).APPROVAL_STATUS
2969 ,p_blocks(l_block_index).RESOURCE_ID
2970 ,p_blocks(l_block_index).RESOURCE_TYPE
2971 ,p_blocks(l_block_index).APPROVAL_STYLE_ID
2972 ,fnd_date.date_to_canonical(p_blocks(l_block_index).DATE_FROM)
2973 ,fnd_date.date_to_canonical(p_blocks(l_block_index).DATE_TO)
2974 ,p_blocks(l_block_index).COMMENT_TEXT
2975 ,p_blocks(l_block_index).PARENT_BUILDING_BLOCK_OVN
2976 ,p_blocks(l_block_index).NEW
2977 ,p_blocks(l_block_index).CHANGED
2978 );
2979
2980 l_block_index := p_blocks.next(l_block_index);
2981 END LOOP;
2982
2983 IF g_debug THEN
2984 hr_utility.set_location ( g_package||l_proc, 140);
2985 END IF;
2986 RETURN l_block_array;
2987 END blocks_to_array;
2988 */
2989
2990 PROCEDURE remove_blocks
2991 (p_blocks IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
2992 p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
2993 p_block_index IN NUMBER
2994 ) is
2995 l_index NUMBER;
2996 l_attr_index NUMBER;
2997 BEGIN
2998 --
2999 -- First clean up any attributes
3000 --
3001 l_attr_index := p_attributes.first;
3002 Loop
3003 Exit when not p_attributes.exists(l_attr_index);
3004 if(p_attributes(l_attr_index).building_block_id = p_blocks(p_block_index).time_building_block_id) then
3005 p_attributes.delete(l_attr_index);
3006 end if;
3007 l_attr_index := p_attributes.next(l_attr_index);
3008 End Loop;
3009 -- Remove the block itself.
3010 --
3011 p_blocks.delete(p_block_index);
3012 End remove_blocks;
3013
3014 procedure get_dynamic_templates_info(
3015 p_template_procedure HXC_TIME_RECIPIENTS.APPL_DYNAMIC_TEMPLATE_PROCESS%TYPE,
3016 p_tp_resource_id IN NUMBER,
3017 p_tp_start_time IN DATE,
3018 p_tp_stop_time IN DATE,
3019 p_attribute_string IN OUT NOCOPY VARCHAR2,
3020 p_block_string IN OUT NOCOPY VARCHAR2,
3021 p_message_string IN OUT NOCOPY VARCHAR2,
3022 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE )
3023
3024 IS
3025 l_dyn_template_sql VARCHAR2(2000);
3026
3027 begin
3028
3029
3030 IF g_debug THEN
3031 hr_utility.trace ('p_template_procedure='||p_template_procedure);
3032 END IF;
3033 hxc_timecard_message_helper.initializeErrors;
3034
3035 l_dyn_template_sql := 'BEGIN '||fnd_global.newline
3036 ||p_template_procedure ||fnd_global.newline
3037 ||'(p_resource_id => :1' ||fnd_global.newline
3038 ||',p_start_date => :2' ||fnd_global.newline
3039 ||',p_stop_date => :3' ||fnd_global.newline
3040 ||',p_attributes => :4' ||fnd_global.newline
3041 ||',p_timecard => :5' ||fnd_global.newline
3042 ||',p_messages => :6);'||fnd_global.newline
3043 ||'END;';
3044
3045 IF g_debug THEN
3046 hr_utility.trace ('l_dyn_template_sql='||l_dyn_template_sql);
3047 END IF;
3048
3049
3050 EXECUTE IMMEDIATE l_dyn_template_sql
3051 using IN p_tp_resource_id, IN p_tp_start_time, IN
3052 p_tp_stop_time, IN OUT p_attribute_string,IN OUT p_block_string,IN OUT
3053 p_message_string;
3054
3055 EXCEPTION
3056 when others then
3057
3058 hxc_timecard_message_helper.addErrorToCollection
3059 (p_messages
3060 ,'HXC_INVALID_DYNAMIC_TEMPL'
3061 ,hxc_timecard.c_error
3062 ,null
3063 ,null
3064 ,hxc_timecard.c_hxc
3065 ,null
3066 ,null
3067 ,null
3068 ,null
3069 );
3070
3071 end get_dynamic_templates_info;
3072
3073 PROCEDURE get_blocks_from_template(
3074 p_resource_id IN VARCHAR2
3075 ,p_resource_type IN VARCHAR2
3076 ,p_start_time IN VARCHAR2
3077 ,p_stop_time IN VARCHAR2
3078 ,p_template_code IN VARCHAR2
3079 ,p_approval_status IN VARCHAR2
3080 ,p_approval_style IN VARCHAR2
3081 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
3082 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
3083 ,p_message_string OUT NOCOPY VARCHAR2
3084 ,p_overwrite IN VARCHAR2 DEFAULT 'Y'
3085 ,p_exclude_hours_template in VARCHAR2 DEFAULT 'N'
3086 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
3087 )
3088 IS
3089 l_template_type VARCHAR2(50) := '';
3090 l_template_handle VARCHAR2(500) := '';
3091 l_template_action VARCHAR2(20) :='';
3092 l_temp_blocks hxc_self_service_time_deposit.timecard_info;
3093 l_blocks HXC_BLOCK_TABLE_TYPE;
3094 l_attributes HXC_ATTRIBUTE_TABLE_TYPE;
3095 l_temp_attributes hxc_self_service_time_deposit.building_block_attribute_info;
3096 l_app_attributes hxc_self_service_time_deposit.app_attributes_info;
3097 l_block_string VARCHAR2(32767) := '';
3098 l_attribute_string VARCHAR2(32767) := '';
3099 l_message_string VARCHAR2(32767) := '';
3100 l_temp VARCHAR2(32767) := '';
3101 l_block_index NUMBER := 1;
3102 l_attribute_index NUMBER := 1;
3103 l_detail_start_date DATE;
3104 l_detail_stop_date DATE;
3105 l_zero_template VARCHAR2(1);
3106 l_template_procedure HXC_TIME_RECIPIENTS.APPL_DYNAMIC_TEMPLATE_PROCESS%TYPE;
3107 l_dyn_template_sql VARCHAR2(2000);
3108 l_process_id HXC_RETRIEVAL_PROCESSES.RETRIEVAL_PROCESS_ID%TYPE;
3109 l_tp_resource_id NUMBER := TO_NUMBER(p_resource_id);
3110 l_tp_start_time DATE := TO_DATE(p_start_time, 'YYYY/MM/DD');
3111 l_tp_stop_time DATE := TO_DATE(p_stop_time, 'YYYY/MM/DD');
3112 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
3113 l_old_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
3114 l_template_id hxc_time_building_blocks.time_building_block_id%TYPE;
3115 l_timecard_block_id NUMBER := -1;
3116 l_day_block_id NUMBER := -1;
3117 l_next_id NUMBER := -1;
3118 l_detail_block_id NUMBER;
3119 l_new_start_time DATE;
3120 l_new_stop_time DATE;
3121 l_old_start_time DATE;
3122 l_old_stop_time DATE;
3123 l_difference NUMBER;
3124 l_start_time DATE;
3125 l_second_block_index NUMBER;
3126 l_timecard_found BOOLEAN;
3127 l_remove_redundant_entries BOOLEAN;
3128 l_found BOOLEAN;
3129 l_template_display_key BOOLEAN;
3130 l_timecard_display_key BOOLEAN;
3131 l_block_array_idx PLS_INTEGER;
3132 l_proc VARCHAR2(70);
3133 l_clear_comment VARCHAR2(1);
3134 l_index number;
3135 CURSOR c_dyn_template_procedure(
3136 p_dyn_template_app IN VARCHAR2
3137 )
3138 IS
3139 select htr.appl_dynamic_template_process
3140 from hxc_time_recipients htr,
3141 fnd_application fa
3142 where fa.application_short_name = p_dyn_template_app
3143 and htr.application_id = fa.application_id;
3144
3145 CURSOR c_last_timecard(
3146 p_resource_id IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
3147 ,p_resource_type IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_TYPE%TYPE
3148 ,p_start_time IN VARCHAR2
3149 )
3150 IS
3151 SELECT time_building_block_id
3152 FROM (
3153 SELECT time_building_block_id
3154 FROM hxc_time_building_blocks tbb1
3155 WHERE tbb1.resource_id = p_resource_id
3156 AND tbb1.resource_type = p_resource_type
3157 AND tbb1.scope = 'TIMECARD'
3158 AND to_char(tbb1.stop_time,'YYYY/MM/DD') < p_start_time
3159 AND date_to = hr_general.end_of_time
3160 ORDER BY tbb1.stop_time desc
3161 )
3162 WHERE rownum = 1;
3163
3164 CURSOR c_otm_retrieval_process(
3165 p_process_name IN hxc_retrieval_processes.NAME%TYPE
3166 )
3167 IS
3168 SELECT retrieval_process_id
3169 FROM hxc_retrieval_processes
3170 WHERE name = p_process_name;
3171
3172 CURSOR c_retrieval_process(
3173 p_dyn_template_process IN hxc_time_recipients.appl_dynamic_template_process%TYPE
3174 )
3175 IS
3176 SELECT hrp.retrieval_process_id
3177 FROM hxc_retrieval_processes hrp,
3178 hxc_time_recipients htr
3179 WHERE htr.appl_dynamic_template_process = p_dyn_template_process
3180 AND htr.time_recipient_id = hrp.time_recipient_id;
3181
3182 BEGIN
3183
3184
3185 l_remove_redundant_entries := FALSE;
3186 IF g_debug THEN
3187 l_proc := 'get_blocks_from_template';
3188 hr_utility.set_location ( g_package||l_proc, 120);
3189 END IF;
3190 l_template_type := SUBSTR(p_template_code, 1, INSTR(p_template_code, '|') - 1);
3191 get_template_info(
3192 p_template_code => p_template_code
3193 ,p_template_handle => l_template_handle
3194 ,p_template_action => l_template_action
3195 );
3196
3197 IF g_debug THEN
3198 hr_utility.set_location ( g_package||l_proc, 130);
3199 END IF;
3200
3201 IF l_template_action = 'INVALID'
3202 THEN
3203 RETURN;
3204 END IF;
3205
3206 IF l_template_action = 'APP'
3207 OR (l_template_action = 'SYS' AND l_template_handle = 'WORK_SCHEDULE')
3208 THEN
3209
3210 -- for OTM work schedule
3211 IF l_template_handle = 'WORK_SCHEDULE'
3212 THEN
3213
3214 IF g_debug THEN
3215 hr_utility.set_location ( g_package||l_proc, 140);
3216 END IF;
3217
3218 HXT_TIMECARD_INFO.GENERATE_TIME(
3219 p_resource_id => TO_NUMBER(p_resource_id)
3220 ,p_start_time => TO_DATE(p_start_time, 'YYYY/MM/DD')
3221 ,p_stop_time => TO_DATE(p_stop_time, 'YYYY/MM/DD')
3222 ,p_app_attributes => l_app_attributes
3223 ,p_timecard => l_temp_blocks
3224 ,p_messages => p_messages
3225 );
3226
3227 OPEN c_otm_retrieval_process(
3228 p_process_name => 'BEE Retrieval Process'
3229 );
3230 FETCH c_otm_retrieval_process INTO l_process_id;
3231
3232 IF c_otm_retrieval_process%NOTFOUND
3233 THEN
3234 CLOSE c_otm_retrieval_process;
3235 FND_MESSAGE.SET_NAME('HXC','HXC_NO_RETRIEVAL_PROCESS');
3236 FND_MESSAGE.RAISE_ERROR;
3237 ELSE
3238 CLOSE c_otm_retrieval_process;
3239 END IF;
3240
3241 ELSE
3242 IF g_debug THEN
3243 hr_utility.set_location ( g_package||l_proc, 150);
3244 END IF;
3245
3246 -- find the corresponding dynamic template function for the
3247 -- specific application
3248 OPEN c_dyn_template_procedure(
3249 p_dyn_template_app => l_template_handle
3250 );
3251
3252 FETCH c_dyn_template_procedure INTO l_template_procedure;
3253
3254 IF c_dyn_template_procedure%NOTFOUND
3255 THEN
3256 CLOSE c_dyn_template_procedure;
3257
3258 RETURN;
3259 END IF;
3260
3261 CLOSE c_dyn_template_procedure;
3262
3263
3264 -- call the procedure to get the blocks info
3265 /* l_dyn_template_sql := 'BEGIN '||fnd_global.newline
3266 ||l_template_procedure ||fnd_global.newline
3267 ||'(p_resource_id => :1' ||fnd_global.newline
3268 ||',p_start_date => :2' ||fnd_global.newline
3269 ||',p_stop_date => :3' ||fnd_global.newline
3270 ||',p_attributes => :4' ||fnd_global.newline
3271 ||',p_timecard => :5' ||fnd_global.newline
3272 ||',p_messages => :6);'||fnd_global.newline
3273 ||'END;';
3274
3275 EXECUTE IMMEDIATE l_dyn_template_sql
3276 using IN l_tp_resource_id, IN l_tp_start_time, IN
3277 l_tp_stop_time, IN OUT l_attribute_string,IN OUT l_block_string,IN OUT
3278 l_message_string;
3279 */
3280 get_dynamic_templates_info
3281 ( l_template_procedure,
3282 l_tp_resource_id,
3283 l_tp_start_time,
3284 l_tp_stop_time,
3285 l_attribute_string,
3286 l_block_string,
3287 l_message_string,
3288 p_messages);
3289
3290 IF g_debug THEN
3291 hr_utility.set_location ( g_package||l_proc, 160);
3292 END IF;
3293
3294 OPEN c_retrieval_process(
3295 p_dyn_template_process => l_template_procedure
3296 );
3297
3298 FETCH c_retrieval_process INTO l_process_id;
3299 IF c_retrieval_process%NOTFOUND
3300 THEN
3301 CLOSE c_retrieval_process;
3302
3303 FND_MESSAGE.SET_NAME('HXC','HXC_NO_RETRIEVAL_PROCESS');
3304 FND_MESSAGE.RAISE_ERROR;
3305 ELSE
3306 CLOSE c_retrieval_process;
3307 END IF;
3308
3309 IF g_debug THEN
3310 hr_utility.set_location ( g_package||l_proc, 170);
3311 END IF;
3312
3313 l_temp_blocks := hxc_deposit_wrapper_utilities.string_to_blocks(
3314 p_block_string => l_block_string
3315 );
3316
3317
3318 l_app_attributes := hxc_deposit_wrapper_utilities.string_to_attributes(
3319 p_attribute_string => l_attribute_string
3320 );
3321
3322
3323 END IF;
3324
3325 IF g_debug THEN
3326 hr_utility.set_location ( g_package||l_proc, 180);
3327 END IF;
3328
3329 --now we need to update the returned block info
3330 l_blocks := hxc_deposit_wrapper_utilities.blocks_to_array(p_blocks => l_temp_blocks);
3331
3332 -- Added for bug 9530086
3333 IF (l_template_type = 'DYNAMIC' and l_template_handle = 'PA' and p_exclude_hours_template = 'Y') THEN
3334 IF l_blocks.count > 0 THEN
3335 FOR l_block_index IN 1..l_blocks.count LOOP
3336 IF l_blocks.exists(l_block_index) THEN
3337 IF l_blocks(l_block_index).SCOPE = 'DETAIL'
3338 THEN
3339 l_blocks(l_block_index).MEASURE := null;
3340 l_blocks(l_block_index).COMMENT_TEXT := null;
3341 l_blocks(l_block_index).START_TIME := null;
3342 l_blocks(l_block_index).STOP_TIME := null;
3343 END IF;
3344 END IF;
3345 END LOOP;
3346 END IF;
3347 END IF;
3348
3349
3350 update_blocks(
3351 p_resource_id => p_resource_id
3352 ,p_approval_status => p_approval_status
3353 ,p_approval_style => p_approval_style
3354 ,p_blocks => l_blocks
3355 ,p_timecard_id => l_timecard_id
3356 ,p_timecard_found => l_timecard_found
3357 );
3358
3359
3360 IF NOT l_timecard_found
3361 THEN
3362 RETURN;
3363 END IF;
3364
3365 IF g_debug THEN
3366 hr_utility.trace('block count=' || l_blocks.count);
3367 hr_utility.set_location ( g_package||l_proc, 190);
3368 END IF;
3369
3370 -- update the returned apps attributes and convert them to block attributes
3371 l_attributes := app_to_block_attributes(
3372 p_app_attributes => l_app_attributes
3373 ,p_process_id => l_process_id
3374 ,p_resource_id => p_resource_id
3375 ,p_timecard_id => l_timecard_id
3376 ,p_template_type => l_template_handle
3377 );
3378
3379 IF g_debug THEN
3380 hr_utility.set_location ( g_package||l_proc, 200);
3381 END IF;
3382
3383 append_blocks(
3384 p_block_array => p_block_array
3385 ,p_attribute_array => p_attribute_array
3386 ,p_new_blocks => l_blocks
3387 ,p_new_attributes => l_attributes
3388 ,p_overwrite => p_overwrite
3389 ,p_start_time => p_start_time
3390 ,p_stop_time => p_stop_time
3391 ,p_resource_id => p_resource_id
3392 ,p_resource_type => p_resource_type
3393 ,p_template_code => p_template_code
3394 ,p_remove_redundant_entries => FALSE
3395 );
3396
3397 IF g_debug THEN
3398 hr_utility.set_location ( g_package||l_proc, 210);
3399 END IF;
3400
3401 p_message_string := l_message_string;
3402 RETURN;
3403 END IF;
3404
3405
3406 -- need to work out the last time card
3407 IF l_template_action = 'SYS' AND l_template_handle = 'LAST_TIMECARD'
3408 THEN
3409 IF g_debug THEN
3410 hr_utility.set_location ( g_package||l_proc, 220);
3411 END IF;
3412
3413 OPEN c_last_timecard(
3414 p_resource_id => TO_NUMBER(p_resource_id)
3415 ,p_resource_type => p_resource_type
3416 ,p_start_time => p_start_time
3417 );
3418
3419 FETCH c_last_timecard INTO l_template_id;
3420 IF c_last_timecard%NOTFOUND
3421 THEN
3422 CLOSE c_last_timecard;
3423
3424
3425 RETURN;
3426 END IF;
3427
3428 CLOSE c_last_timecard;
3429
3430 END IF;
3431 --
3432 -- Check the display key setting in the current blocks
3433 -- This only matters if we are appending, not if overwriting
3434 -- Ultimately, when all timecards have the display key set
3435 -- we can remove this check
3436 --
3437 l_template_display_key := true;
3438 l_timecard_display_key := true;
3439
3440 l_block_array_idx := p_block_array.first;
3441 l_found := false;
3442 Loop
3443 Exit when ((l_found) or (not p_block_array.exists(l_block_array_idx)));
3444 if(p_block_array(l_block_array_idx).scope = hxc_timecard.c_detail_scope) then
3445 l_found := true;
3446 if((p_block_array(l_block_array_idx).translation_display_key is null)
3447 OR
3448 (p_block_array(l_block_array_idx).translation_display_key ='')) then
3449 l_timecard_display_key := false;
3450 end if;
3451 end if;
3452 l_block_array_idx := p_block_array.next(l_block_array_idx);
3453 End Loop;
3454
3455 IF g_debug THEN
3456 hr_utility.set_location ( g_package||l_proc, 230);
3457 END IF;
3458
3459 -- we need to retrieve the template from time building blocks table
3460 IF l_template_action = 'STATIC'
3461 THEN
3462 l_template_id := TO_NUMBER(l_template_handle);
3463 END IF;
3464 --
3465 -- Get the blocks and attributes associated with the template.
3466 --
3467 hxc_block_collection_utils.get_template
3468 (p_template_id => l_template_id,
3469 p_blocks => l_blocks,
3470 p_attributes => l_attributes,
3471 p_template_start_time => l_old_start_time,
3472 p_template_stop_time => l_old_stop_time
3473 );
3474
3475 IF l_blocks.count > 0 THEN
3476 -- we need to adjust data based on the IN parameters
3477 l_new_start_time := TO_DATE(p_start_time, 'YYYY/MM/DD');
3478 l_new_stop_time := TO_DATE(p_stop_time, 'YYYY/MM/DD');
3479 --
3480 -- Using new routine, we know the first block is the top-level block
3481 -- which might be a timecard or a timecard-template.
3482 --
3483 l_blocks(1).START_TIME := fnd_date.date_to_canonical(l_new_start_time);
3484 -- joel asked for this stop time set like this!
3485 l_blocks(1).STOP_TIME := to_char(l_new_stop_time, 'YYYY/MM/DD') || ' 23:59:59';
3486 l_blocks(1).SCOPE := 'TIMECARD';
3487
3488 FOR l_block_index IN 1..l_blocks.count LOOP
3489 IF l_blocks.exists(l_block_index) THEN
3490 l_blocks(l_block_index).DATE_FROM := fnd_date.date_to_canonical(SYSDATE);
3491 l_blocks(l_block_index).DATE_TO := fnd_date.date_to_canonical(hr_general.end_of_time);
3492 l_blocks(l_block_index).RESOURCE_ID := p_resource_id;
3493 l_blocks(l_block_index).RESOURCE_TYPE := p_resource_type;
3494 l_blocks(l_block_index).OBJECT_VERSION_NUMBER := 1;
3495 l_blocks(l_block_index).new := hxc_timecard.c_yes;
3496
3497 if((l_blocks(l_block_index).translation_display_key is null)
3498 OR
3499 (l_blocks(l_block_index).translation_display_key = '')) then
3500 l_template_display_key := false;
3501 end if;
3502
3503 IF g_debug THEN
3504 hr_utility.set_location ( g_package||l_proc, 246);
3505 END IF;
3506
3507 IF l_template_action = 'LAST_TIMECARD'
3508 THEN
3509 l_blocks(l_block_index).APPROVAL_STATUS := p_approval_status;
3510 l_blocks(l_block_index).COMMENT_TEXT := NULL;
3511 END IF;
3512
3513 IF l_blocks(l_block_index).SCOPE = 'DAY'
3514 THEN
3515 l_difference := fnd_date.canonical_to_date(l_blocks(l_block_index).START_TIME)
3516 - l_old_start_time;
3517 l_start_time := l_new_start_time + l_difference;
3518
3519 IF l_start_time > l_new_stop_time
3520 THEN
3521
3522 -- we need to remove the details also bug 3174721
3523 l_second_block_index := l_blocks.first;
3524 LOOP
3525 EXIT WHEN
3526 (NOT l_blocks.exists(l_second_block_index));
3527 IF l_blocks(l_second_block_index).PARENT_BUILDING_BLOCK_ID
3528 = l_blocks(l_block_index).TIME_BUILDING_BLOCK_ID
3529 THEN
3530 remove_blocks
3531 (p_blocks => l_blocks,
3532 p_attributes => l_attributes,
3533 p_block_index => l_second_block_index
3534 );
3535 END IF;
3536 l_second_block_index := l_blocks.next(l_second_block_index);
3537 END LOOP;
3538
3539 remove_blocks
3540 (p_blocks => l_blocks,
3541 p_attributes => l_attributes,
3542 p_block_index => l_block_index
3543 );
3544
3545 ELSE
3546 l_blocks(l_block_index).START_TIME := fnd_date.date_to_canonical(l_start_time);
3547 l_blocks(l_block_index).STOP_TIME := TO_CHAR(l_start_time, 'YYYY/MM/DD')
3548 || ' 23:59:59';
3549
3550 l_blocks(l_block_index).PARENT_IS_NEW := 'Y';
3551 l_blocks(l_block_index).PARENT_BUILDING_BLOCK_OVN := 1;
3552
3553 --modify detail blocks
3554 FOR l_second_block_index IN 1..l_blocks.count LOOP
3555 IF l_blocks(l_second_block_index).PARENT_BUILDING_BLOCK_ID
3556 = l_blocks(l_block_index).TIME_BUILDING_BLOCK_ID
3557 THEN
3558 IF l_blocks(l_second_block_index).START_TIME IS NOT NULL
3559 THEN
3560
3561 l_detail_start_date :=
3562 fnd_date.canonical_to_date(l_blocks(l_second_block_index).START_TIME)
3563 - l_old_start_time + l_new_start_time;
3564 l_blocks(l_second_block_index).START_TIME :=
3565 TO_CHAR(l_detail_start_date, 'YYYY/MM/DD')
3566 || TO_CHAR(
3567 TO_DATE(l_blocks(l_second_block_index).START_TIME, 'YYYY/MM/DD HH24:MI:SS'),
3568 ' HH24:MI:SS');
3569
3570 l_detail_stop_date :=
3571 fnd_date.canonical_to_date(l_blocks(l_second_block_index).STOP_TIME)
3572 - l_old_start_time + l_new_start_time;
3573
3574 l_blocks(l_second_block_index).STOP_TIME :=
3575 TO_CHAR(l_detail_stop_date, 'YYYY/MM/DD')
3576 || TO_CHAR(
3577 TO_DATE(l_blocks(l_second_block_index).STOP_TIME, 'YYYY/MM/DD HH24:MI:SS'),
3578 ' HH24:MI:SS');
3579
3580 END IF;
3581
3582 l_blocks(l_second_block_index).PARENT_IS_NEW := 'Y';
3583 l_blocks(l_second_block_index).PARENT_BUILDING_BLOCK_OVN := 1;
3584 END IF;
3585 END LOOP;
3586
3587 END IF;
3588 ELSIF l_blocks(l_block_index).SCOPE = 'DETAIL' and p_exclude_hours_template = 'Y'
3589 THEN
3590 l_blocks(l_block_index).MEASURE := null;
3591 l_blocks(l_block_index).COMMENT_TEXT := null;
3592 l_blocks(l_block_index).START_TIME := null;
3593 l_blocks(l_block_index).STOP_TIME := null;
3594 END IF;
3595
3596 END IF;
3597 END LOOP;
3598
3599 --ER:12563216 - EXCLUDE HOURS ALSO CLEAR OUT PROJECTS DFF IN PROJECT DETAILS PAGE.
3600 IF (NVL(p_exclude_hours_template,'N') = 'Y')
3601 THEN
3602 l_index := l_attributes.FIRST;
3603 WHILE l_index IS NOT NULL
3604 LOOP
3605 IF ( l_attributes (l_index).ATTRIBUTE_CATEGORY like
3606 'PAEXPITDFF%'
3607 )
3608 THEN
3609 -- Delete Attribute Row for that index
3610 l_attributes.DELETE(l_index);
3611 END IF;
3612 l_index := l_attributes.NEXT (l_index);
3613 END LOOP;
3614 END IF;
3615
3616 IF g_debug THEN
3617 hr_utility.set_location ( g_package||l_proc, 250);
3618 END IF;
3619
3620 if((l_blocks.COUNT > 0) AND (l_template_action = 'SYS' AND l_template_handle = 'LAST_TIMECARD'))
3621 then
3622 -- Check if the Zero hours preference is set
3623 l_zero_template := hxc_preference_evaluation.resource_preferences(
3624 p_resource_id,
3625 'TC_W_TMPLT_FCNLTY',
3626 2 );
3627
3628 l_clear_comment := hxc_preference_evaluation.resource_preferences(
3629 p_resource_id,
3630 'TC_W_TMPLT_FCNLTY',
3631 3 );
3632
3633 -- added check for p_exclude_hours_template bug5955838
3634
3635 if (nvl(l_zero_template,'N') ='Y'AND NVL(p_exclude_hours_template,'N') <> 'Y') then
3636
3637 modify_to_zero_hrs_template (
3638 p_start_time => p_start_time
3639 ,p_stop_time => p_stop_time
3640 ,p_block_array => l_blocks
3641 ,p_attribute_array => l_attributes
3642 ,p_clear_comment => nvl(l_clear_comment,'N')
3643 );
3644 if(nvl(p_overwrite,'N') = 'N') then -- Only in the case of Appending.
3645 l_remove_redundant_entries :=TRUE;
3646 end if;
3647 end if; -- (l_zero_template ='Y') then
3648 end if;
3649
3650 append_blocks(
3651 p_block_array => p_block_array
3652 ,p_attribute_array => p_attribute_array
3653 ,p_new_blocks => l_blocks
3654 ,p_new_attributes => l_attributes
3655 ,p_overwrite => p_overwrite
3656 ,p_start_time => p_start_time
3657 ,p_stop_time => p_stop_time
3658 ,p_resource_id => p_resource_id
3659 ,p_resource_type => p_resource_type
3660 ,p_template_code => p_template_code
3661 ,p_remove_redundant_entries =>l_remove_redundant_entries
3662 );
3663
3664 IF g_debug THEN
3665 hr_utility.set_location ( g_package||l_proc, 260);
3666 END IF;
3667 --
3668 -- If the timecard, or the template are missing the display keys
3669 -- we must permit the timecard to dynamically allocate the row
3670 -- for the timecard entries.
3671 -- I.e. reset the display key.
3672 --
3673 if((NOT l_timecard_display_key) OR (NOT l_template_display_key)) then
3674 l_block_array_idx := p_block_array.first;
3675 Loop
3676 Exit when not p_block_array.exists(l_block_array_idx);
3677 p_block_array(l_block_array_idx).translation_display_key := '';
3678 l_block_array_idx := p_block_array.next(l_block_array_idx);
3679 End Loop;
3680 end if;
3681
3682 IF g_debug THEN
3683 hr_utility.set_location ( g_package||l_proc, 270);
3684 END IF;
3685
3686 p_message_string := NULL;
3687
3688 END IF;
3689
3690
3691 END get_blocks_from_template;
3692
3693
3694 /*
3695 PROCEDURE get_attributes(
3696 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3697 ,p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
3698 ,p_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info
3699 ,p_review IN VARCHAR2
3700 )
3701 IS
3702 l_attribute_index NUMBER;
3703 l_temp_attribute hxc_self_service_time_deposit.attribute_info;
3704
3705 CURSOR c_block_attributes(
3706 p_building_block_id IN HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
3707 ,p_ovn IN HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
3708 )
3709 IS
3710 select a.time_attribute_id
3711 ,au.time_building_block_id
3712 ,bbit.bld_blk_info_type
3713 ,a.attribute_category
3714 ,a.attribute1
3715 ,a.attribute2
3716 ,a.attribute3
3717 ,a.attribute4
3718 ,a.attribute5
3719 ,a.attribute6
3720 ,a.attribute7
3721 ,a.attribute8
3722 ,a.attribute9
3723 ,a.attribute10
3724 ,a.attribute11
3725 ,a.attribute12
3726 ,a.attribute13
3727 ,a.attribute14
3728 ,a.attribute15
3729 ,a.attribute16
3730 ,a.attribute17
3731 ,a.attribute18
3732 ,a.attribute19
3733 ,a.attribute20
3734 ,a.attribute21
3735 ,a.attribute22
3736 ,a.attribute23
3737 ,a.attribute24
3738 ,a.attribute25
3739 ,a.attribute26
3740 ,a.attribute27
3741 ,a.attribute28
3742 ,a.attribute29
3743 ,a.attribute30
3744 ,a.bld_blk_info_type_id
3745 ,a.object_version_number
3746 ,'N' NEW
3747 ,'N' CHANGED
3748 from hxc_time_attributes a,
3749 hxc_time_attribute_usages au,
3750 hxc_bld_blk_info_types bbit
3751 where au.time_building_block_id = p_building_block_id
3752 and au.time_building_block_ovn = p_ovn
3753 and au.time_attribute_id = a.time_attribute_id
3754 and (not (a.attribute_category = 'SECURITY'))
3755 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
3756
3757
3758 BEGIN
3759 IF p_attributes.count = 0
3760 THEN
3761 l_attribute_index := 1;
3762 ELSE
3763 l_attribute_index := p_attributes.last + 1;
3764 END IF;
3765
3766 OPEN c_block_attributes(
3767 p_building_block_id => p_block_id
3768 ,p_ovn => p_block_ovn
3769 );
3770
3771 LOOP
3772 FETCH c_block_attributes INTO l_temp_attribute;
3773 EXIT WHEN c_block_attributes%NOTFOUND;
3774
3775 IF p_review <> 'TIMECARD-REVIEW'
3776 AND l_temp_attribute.attribute_category = 'REASON'
3777 THEN
3778 NULL;
3779 ELSE
3780 p_attributes(l_attribute_index) := l_temp_attribute;
3781
3782 l_attribute_index := l_attribute_index + 1;
3783 END IF;
3784 END LOOP;
3785
3786 CLOSE c_block_attributes;
3787 END get_attributes;
3788 */
3789
3790 PROCEDURE get_attributes(
3791 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3792 ,p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
3793 ,p_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info
3794 ,p_review IN VARCHAR2
3795 ,p_new_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE DEFAULT NULL
3796 )
3797 IS
3798 l_attribute_index NUMBER;
3799 l_temp_attribute hxc_self_service_time_deposit.attribute_info;
3800
3801 CURSOR c_block_attributes(
3802 p_building_block_id IN HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
3803 ,p_ovn IN HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
3804 )
3805 IS
3806 select a.time_attribute_id
3807 ,au.time_building_block_id
3808 ,bbit.bld_blk_info_type
3809 ,a.attribute_category
3810 ,a.attribute1
3811 ,a.attribute2
3812 ,a.attribute3
3813 ,a.attribute4
3814 ,a.attribute5
3815 ,a.attribute6
3816 ,a.attribute7
3817 ,a.attribute8
3818 ,a.attribute9
3819 ,a.attribute10
3820 ,a.attribute11
3821 ,a.attribute12
3822 ,a.attribute13
3823 ,a.attribute14
3824 ,a.attribute15
3825 ,a.attribute16
3826 ,a.attribute17
3827 ,a.attribute18
3828 ,a.attribute19
3829 ,a.attribute20
3830 ,a.attribute21
3831 ,a.attribute22
3832 ,a.attribute23
3833 ,a.attribute24
3834 ,a.attribute25
3835 ,a.attribute26
3836 ,a.attribute27
3837 ,a.attribute28
3838 ,a.attribute29
3839 ,a.attribute30
3840 ,a.bld_blk_info_type_id
3841 ,a.object_version_number
3842 ,'N' NEW
3843 ,'N' CHANGED
3844 ,'N' PROCESS
3845 from hxc_time_attributes a,
3846 hxc_time_attribute_usages au,
3847 hxc_bld_blk_info_types bbit
3848 where au.time_building_block_id = p_building_block_id
3849 and au.time_building_block_ovn = p_ovn
3850 and au.time_attribute_id = a.time_attribute_id
3851 and (not (a.attribute_category = 'SECURITY'))
3852 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
3853
3854
3855 BEGIN
3856 IF p_attributes.count = 0
3857 THEN
3858 l_attribute_index := 1;
3859 ELSE
3860 l_attribute_index := p_attributes.last + 1;
3861 END IF;
3862
3863 OPEN c_block_attributes(
3864 p_building_block_id => p_block_id
3865 ,p_ovn => p_block_ovn
3866 );
3867
3868 LOOP
3869 FETCH c_block_attributes INTO l_temp_attribute;
3870 EXIT WHEN c_block_attributes%NOTFOUND;
3871
3872 IF p_review <> 'TIMECARD-REVIEW'
3873 AND l_temp_attribute.attribute_category = 'REASON'
3874 THEN
3875 NULL;
3876 ELSE
3877 p_attributes(l_attribute_index) := l_temp_attribute;
3878
3879 IF p_new_block_id IS NOT NULL
3880 THEN
3881 p_attributes(l_attribute_index).building_block_id := p_new_block_id;
3882
3883 END IF;
3884
3885 l_attribute_index := l_attribute_index + 1;
3886 END IF;
3887 END LOOP;
3888
3889 CLOSE c_block_attributes;
3890 END get_attributes;
3891
3892
3893
3894
3895
3896 PROCEDURE translate_alias_timecards(
3897 p_resource_id IN VARCHAR2
3898 ,p_start_time IN VARCHAR2
3899 ,p_stop_time IN VARCHAR2
3900 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
3901 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
3902 )
3903 IS
3904 l_resource_id VARCHAR2(50) := NULL;
3905
3906 l_proc VARCHAR2(50);
3907
3908 l_messages_table HXC_MESSAGE_TABLE_TYPE;
3909
3910 BEGIN
3911
3912
3913 -- call translator alias package
3914 l_resource_id := p_resource_id;
3915
3916 IF l_resource_id IS NULL
3917 THEN
3918 l_resource_id := p_block_array(1).resource_id;
3919 END IF;
3920
3921 IF g_debug THEN
3922 l_proc := 'translate_alias_timecards';
3923 hr_utility.set_location ( g_package||l_proc, 20);
3924 END IF;
3925
3926 HXC_ALIAS_TRANSLATOR.do_retrieval_translation(
3927 p_attributes => p_attribute_array
3928 ,p_blocks => p_block_array
3929 ,p_start_time => FND_DATE.CANONICAL_TO_DATE(p_start_time)
3930 ,p_stop_time => FND_DATE.CANONICAL_TO_DATE(p_stop_time)
3931 ,p_resource_id => l_resource_id
3932 ,p_messages => l_messages_table
3933 );
3934
3935 IF g_debug THEN
3936 hr_utility.set_location ( g_package||l_proc, 30);
3937 END IF;
3938
3939 END translate_alias_timecards;
3940
3941
3942 --return a structure of timecard, day and details that
3943
3944 --corresponds to an application period
3945
3946 PROCEDURE get_application_period_blocks(
3947 p_resource_id IN VARCHAR2
3948 ,p_resource_type IN VARCHAR2
3949 ,p_start_time IN VARCHAR2
3950 ,p_stop_time IN VARCHAR2
3951 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
3952 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
3953 ,p_message_string OUT NOCOPY VARCHAR2
3954 ,p_review IN VARCHAR2
3955 )
3956 IS
3957 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
3958 l_blocks hxc_self_service_time_deposit.timecard_info;
3959 l_block_index NUMBER;
3960 l_parent_id hxc_time_building_blocks.time_building_block_id%TYPE;
3961
3962
3963 CURSOR c_day_blocks(
3964 p_resource_id IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
3965 ,p_resource_type IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_TYPE%TYPE
3966 ,p_start_time IN VARCHAR2
3967 ,p_stop_time IN VARCHAR2
3968 )
3969 IS
3970 select
3971 tbb1.TIME_BUILDING_BLOCK_ID
3972 ,tbb1.TYPE
3973 ,tbb1.MEASURE
3974 ,tbb1.UNIT_OF_MEASURE
3975 ,tbb1.START_TIME
3976 ,tbb1.STOP_TIME
3977 ,tbb1.PARENT_BUILDING_BLOCK_ID
3978 ,'N' PARENT_IS_NEW
3979 ,tbb1.SCOPE
3980 ,tbb1.OBJECT_VERSION_NUMBER
3981 ,tbb1.APPROVAL_STATUS
3982 ,tbb1.RESOURCE_ID
3983 ,tbb1.RESOURCE_TYPE
3984 ,tbb1.APPROVAL_STYLE_ID
3985 ,tbb1.DATE_FROM
3986 ,tbb1.DATE_TO
3987 ,tbb1.COMMENT_TEXT
3988 ,tbb1.PARENT_BUILDING_BLOCK_OVN
3989 ,'N' NEW
3990 ,'N' CHANGED
3991 ,'N' PROCESS
3992 ,tbb1.application_set_id
3993 ,tbb1.translation_display_key
3994 from hxc_time_building_blocks tbb1
3995 ,hxc_time_building_blocks tc
3996 where tbb1.date_to = hr_general.end_of_time
3997 and tbb1.resource_id = p_resource_id
3998 and tbb1.resource_type = p_resource_type
3999 and tbb1.scope = 'DAY'
4000 and tbb1.parent_building_block_id = tc.time_building_block_id
4001 and tbb1.parent_building_block_ovn = tc.object_version_number
4002 and tc.scope = 'TIMECARD'
4003 and tc.date_to = hr_general.end_of_time
4004 and to_char(tbb1.start_time,'YYYY/MM/DD') >= p_start_time
4005 and to_char(tbb1.start_time,'YYYY/MM/DD') <= p_stop_time
4006 and tbb1.date_to = hr_general.end_of_time
4007 order by tbb1.start_time asc;
4008
4009 CURSOR c_detail_blocks(
4010 p_resource_id IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
4011 ,p_resource_type IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_TYPE%TYPE
4012 ,p_start_time IN VARCHAR2
4013 ,p_stop_time IN VARCHAR2
4014 )
4015 IS
4016 select
4017 tbb1.TIME_BUILDING_BLOCK_ID
4018 ,tbb1.TYPE
4019 ,tbb1.MEASURE
4020 ,tbb1.UNIT_OF_MEASURE
4021 ,tbb1.START_TIME
4022 ,tbb1.STOP_TIME
4023 ,tbb1.PARENT_BUILDING_BLOCK_ID
4024 ,'N' PARENT_IS_NEW
4025 ,tbb1.SCOPE
4026 ,tbb1.OBJECT_VERSION_NUMBER
4027 ,tbb1.APPROVAL_STATUS
4028 ,tbb1.RESOURCE_ID
4029 ,tbb1.RESOURCE_TYPE
4030 ,tbb1.APPROVAL_STYLE_ID
4031 ,tbb1.DATE_FROM
4032 ,tbb1.DATE_TO
4033 ,tbb1.COMMENT_TEXT
4034 ,tbb1.PARENT_BUILDING_BLOCK_OVN
4035 ,'N' NEW
4036 ,'N' CHANGED
4037 ,'N' PROCESS
4038 ,tbb1.application_set_id
4039 ,tbb1.translation_display_key
4040 from hxc_time_building_blocks tbb1
4041 ,hxc_time_building_blocks tc
4042 ,hxc_time_building_blocks days
4043 where tbb1.date_to = hr_general.end_of_time
4044 and tbb1.resource_id = p_resource_id
4045 and tbb1.resource_type = p_resource_type
4046 and tbb1.scope = 'DETAIL'
4047 and tbb1.parent_building_block_id = days.time_building_block_id
4048 and tbb1.parent_building_block_ovn = days.object_version_number
4049 and days.scope = 'DAY'
4050 and to_char(days.start_time,'YYYY/MM/DD') >= p_start_time
4051 and to_char(days.start_time,'YYYY/MM/DD') <= p_stop_time
4052 and days.date_to = hr_general.end_of_time
4053 and days.parent_building_block_id = tc.time_building_block_id
4054 and days.parent_building_block_ovn = tc.object_version_number
4055 and tc.date_to = hr_general.end_of_time
4056 and tc.scope = 'TIMECARD'
4057 order by tbb1.start_time asc;
4058
4059 CURSOR c_timecard_block(
4060 p_resource_id IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
4061 ,p_resource_type IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_TYPE%TYPE
4062 ,p_start_time IN VARCHAR2
4063 ,p_stop_time IN VARCHAR2
4064 )
4065 IS
4066 select
4067 tbb1.TIME_BUILDING_BLOCK_ID
4068 ,tbb1.TYPE
4069 ,tbb1.MEASURE
4070 ,tbb1.UNIT_OF_MEASURE
4071 ,tbb1.START_TIME
4072 ,tbb1.STOP_TIME
4073 ,tbb1.PARENT_BUILDING_BLOCK_ID
4074 ,'N' PARENT_IS_NEW
4075 ,tbb1.SCOPE
4076 ,tbb1.OBJECT_VERSION_NUMBER
4077 ,tbb1.APPROVAL_STATUS
4078 ,tbb1.RESOURCE_ID
4079 ,tbb1.RESOURCE_TYPE
4080 ,tbb1.APPROVAL_STYLE_ID
4081 ,tbb1.DATE_FROM
4082 ,tbb1.DATE_TO
4083 ,tbb1.COMMENT_TEXT
4084 ,tbb1.PARENT_BUILDING_BLOCK_OVN
4085 ,'N' NEW
4086 ,'N' CHANGED
4087 ,'N' PROCESS
4088 ,tbb1.application_set_id
4089 ,tbb1.translation_display_key
4090 from hxc_time_building_blocks tbb1
4091 where tbb1.date_to = hr_general.end_of_time
4092 and tbb1.resource_id = p_resource_id
4093 and tbb1.resource_type = p_resource_type
4094 and tbb1.scope = 'TIMECARD'
4095 and to_char(tbb1.start_time,'YYYY/MM/DD') >= p_start_time
4096 and to_char(tbb1.start_time,'YYYY/MM/DD') <= p_stop_time;
4097
4098 BEGIN
4099 p_message_string := NULL;
4100
4101 l_block_index := 1;
4102
4103 --get a timecard block that includes the first day of the
4104 --application period, we need layout attributes associated
4105 --with this timecard
4106
4107 OPEN c_timecard_block(
4108 p_resource_id => p_resource_id
4109 ,p_resource_type => p_resource_type
4110 ,p_start_time => p_start_time
4111 ,p_stop_time => p_stop_time
4112 );
4113
4114 -- one of the related timecards has been deleted. This notification
4115 -- should have been cancelled. (this should not happen in the first place
4116 FETCH c_timecard_block INTO l_blocks(l_block_index);
4117 IF c_timecard_block%NOTFOUND
4118 THEN
4119 CLOSE c_timecard_block;
4120
4121 RETURN;
4122 END IF;
4123
4124 CLOSE c_timecard_block;
4125
4126 l_blocks(l_block_index).start_time := to_date(p_start_time, 'YYYY/MM/DD');
4127 l_blocks(l_block_index).stop_time := to_date(p_stop_time, 'YYYY/MM/DD');
4128 l_parent_id := l_blocks(l_block_index).time_building_block_id;
4129
4130 get_attributes(
4131 p_block_id => l_blocks(l_block_index).time_building_block_id
4132 ,p_block_ovn => l_blocks(l_block_index).object_version_number
4133 ,p_attributes => l_attributes
4134 ,p_review => p_review
4135 );
4136
4137 --now get all the day blocks
4138 OPEN c_day_blocks(
4139 p_resource_id => p_resource_id
4140 ,p_resource_type => p_resource_type
4141 ,p_start_time => p_start_time
4142 ,p_stop_time => p_stop_time
4143 );
4144
4145 l_block_index := l_block_index + 1;
4146 LOOP
4147 FETCH c_day_blocks INTO l_blocks(l_block_index);
4148 EXIT WHEN c_day_blocks%NOTFOUND;
4149
4150 l_blocks(l_block_index).PARENT_BUILDING_BLOCK_ID := l_parent_id;
4151
4152 get_attributes(
4153 p_block_id => l_blocks(l_block_index).time_building_block_id
4154 ,p_block_ovn => l_blocks(l_block_index).object_version_number
4155 ,p_attributes => l_attributes
4156 ,p_review => p_review
4157 );
4158
4159 l_block_index := l_block_index + 1;
4160
4161 END LOOP;
4162
4163 CLOSE c_day_blocks;
4164
4165 OPEN c_detail_blocks(
4166 p_resource_id => p_resource_id
4167 ,p_resource_type => p_resource_type
4168 ,p_start_time => p_start_time
4169 ,p_stop_time => p_stop_time
4170 );
4171
4172 l_block_index := l_block_index + 1;
4173 LOOP
4174 FETCH c_detail_blocks INTO l_blocks(l_block_index);
4175 EXIT WHEN c_detail_blocks%NOTFOUND;
4176
4177
4178 get_attributes(
4179 p_block_id => l_blocks(l_block_index).time_building_block_id
4180 ,p_block_ovn => l_blocks(l_block_index).object_version_number
4181 ,p_attributes => l_attributes
4182 ,p_review => p_review
4183 );
4184
4185 l_block_index := l_block_index + 1;
4186
4187 END LOOP;
4188
4189 CLOSE c_detail_blocks;
4190
4191 p_block_array := hxc_deposit_wrapper_utilities.blocks_to_array(
4192 p_blocks => l_blocks
4193 );
4194 p_attribute_array := hxc_deposit_wrapper_utilities.attributes_to_array(
4195 p_attributes => l_attributes
4196 );
4197
4198 translate_alias_timecards(
4199 p_resource_id => p_resource_id
4200 ,p_start_time => p_start_time
4201 ,p_stop_time => p_stop_time
4202 ,p_block_array => p_block_array
4203 ,p_attribute_array => p_attribute_array
4204 );
4205
4206 END get_application_period_blocks;
4207
4208 PROCEDURE request_lock
4209 (p_resource_id IN VARCHAR2
4210 ,p_resource_type IN VARCHAR2
4211 ,p_start_time IN VARCHAR2
4212 ,p_stop_time IN VARCHAR2
4213 ,p_timecard_id IN NUMBER
4214 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
4215 ,p_lock_success OUT NOCOPY BOOLEAN
4216 ,p_lock_rowid IN OUT NOCOPY ROWID
4217 ,p_timecard_action in VARCHAR2
4218 ) is
4219
4220 cursor c_find_timecard_ovn
4221 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
4222 select object_version_number
4223 from hxc_time_building_blocks
4224 where time_building_block_id = p_timecard_id
4225 and date_to = hr_general.end_of_time;
4226
4227 l_process_locker_type varchar2(80) := hxc_lock_util.c_ss_timecard_action;
4228 l_timecard_ovn hxc_time_building_blocks.object_version_number%type;
4229
4230 Begin
4231
4232 p_messages := HXC_MESSAGE_TABLE_TYPE();
4233
4234 if(p_timecard_id is not null) then
4235 open c_find_timecard_ovn(p_timecard_id);
4236 fetch c_find_timecard_ovn into l_timecard_ovn;
4237 close c_find_timecard_ovn;
4238 else
4239 l_timecard_ovn := null;
4240 end if;
4241
4242 if(
4243 (p_timecard_action = 'ApprovalDetail')
4244 OR
4245 (p_timecard_action = 'Detail')
4246 ) then
4247 l_process_locker_type := hxc_lock_util.c_ss_timecard_view;
4248 end if;
4249
4250 --
4251 -- Try obtaining a lock
4252 --
4253 hxc_lock_api.request_lock
4254 (P_PROCESS_LOCKER_TYPE => l_process_locker_type
4255 ,P_RESOURCE_ID => p_resource_id
4256 ,P_START_TIME => to_date(p_start_time,'YYYY/MM/DD')
4257 ,P_STOP_TIME => to_date(p_stop_time,'YYYY/MM/DD')
4258 ,P_TIME_BUILDING_BLOCK_ID => p_timecard_id
4259 ,P_TIME_BUILDING_BLOCK_OVN=> l_timecard_ovn
4260 ,P_EXPIRATION_TIME => 10
4261 ,P_ROW_LOCK_ID => p_lock_rowid
4262 ,P_MESSAGES => p_messages
4263 ,P_LOCKED_SUCCESS => p_lock_success
4264 );
4265
4266 End request_lock;
4267
4268
4269 FUNCTION get_name(
4270 p_person_id IN per_all_people_f.person_id%TYPE
4271 )
4272 RETURN VARCHAR2
4273 IS
4274 CURSOR c_person_name(
4275 p_person_id per_all_people_f.person_id%TYPE
4276 )
4277 IS
4278 SELECT full_name
4279 FROM per_all_people_f ppf
4280 WHERE person_id = p_person_id
4281 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
4282
4283 l_person_name per_all_people_f.full_name%TYPE := NULL;
4284
4285 BEGIN
4286 IF p_person_id IS NULL
4287 THEN
4288 RETURN hr_general.decode_lookup('HXC_APPROVAL_MECHANISM','AUTO_APPROVE');
4289 END IF;
4290
4291 OPEN c_person_name(p_person_id);
4292 FETCH c_person_name INTO l_person_name;
4293 CLOSE c_person_name;
4294
4295 RETURN l_person_name;
4296
4297 END get_name;
4298
4299 FUNCTION get_timecard_comment(
4300 p_app_period_id IN hxc_time_building_blocks.time_building_block_id%TYPE
4301 )
4302 RETURN varchar2
4303 IS
4304 CURSOR c_timecards(
4305 p_app_period_id IN hxc_time_building_blocks.time_building_block_id%TYPE
4306 )
4307 IS
4308 SELECT timecards.comment_text
4309 FROM hxc_tc_ap_links links
4310 ,hxc_time_building_blocks timecards
4311 WHERE links.application_period_id = p_app_period_id
4312 AND links.timecard_id = timecards.time_building_block_id
4313 AND timecards.date_to = hr_general.end_of_time;
4314
4315 l_comment hxc_time_building_blocks.comment_text%TYPE := '';
4316 l_combined_comment hxc_time_building_blocks.comment_text%TYPE := '';
4317 l_extra number := 0;
4318 l_comment_len number := 0;
4319 BEGIN
4320 OPEN c_timecards(p_app_period_id);
4321
4322 LOOP
4323 FETCH c_timecards INTO l_comment;
4324 EXIT WHEN c_timecards%NOTFOUND;
4325
4326 IF l_combined_comment IS NOT NULL
4327 AND l_comment IS NOT NULL
4328 THEN
4329 l_comment_len := length(l_comment);
4330 l_extra := length(l_combined_comment) + l_comment_len - 2000;
4331 l_combined_comment := l_combined_comment
4332 || substr(l_comment, 1, l_comment_len - l_extra);
4333
4334 ELSE
4335 l_combined_comment := l_combined_comment || l_comment;
4336 END IF;
4337
4338 IF length(l_combined_comment) = 2000
4339 THEN
4340 RETURN l_combined_comment;
4341 END IF;
4342 END LOOP;
4343
4344 CLOSE c_timecards;
4345
4346 RETURN l_combined_comment;
4347 END get_timecard_comment;
4348 --
4349 -- New version, supporting the fragment page.
4350 --
4351 Procedure get_app_period_blocks_by_id
4352 (p_app_period_id in hxc_time_building_blocks.time_building_block_id%type,
4353 p_start_time in date,
4354 p_stop_time in date,
4355 p_blocks in out nocopy hxc_block_table_type,
4356 p_attributes in out nocopy hxc_attribute_table_type
4357 ) IS
4358
4359 Begin
4360
4361 hxc_block_collection_utils.get_application_period
4362 (p_app_period_id,
4363 p_start_time,
4364 p_stop_time,
4365 p_blocks,
4366 p_attributes
4367 );
4368
4369 if(p_blocks is not null) then
4370 translate_alias_timecards
4371 (p_resource_id => p_blocks(1).resource_id,
4372 p_start_time => substrb(fnd_date.date_to_canonical(p_start_time),1,10),
4373 p_stop_time => substrb(fnd_date.date_to_canonical(p_stop_time),1,10),
4374 p_block_array => p_blocks,
4375 p_attribute_array => p_attributes
4376 );
4377 end if;
4378
4379 End get_app_period_blocks_by_id;
4380 --
4381 -- Old version, called from?
4382 --
4383 PROCEDURE get_app_period_blocks_by_id(
4384 p_resource_id IN VARCHAR2
4385 ,p_resource_type IN VARCHAR2
4386 ,p_app_period_id IN VARCHAR2
4387 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
4388 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
4389 ,p_message_string OUT NOCOPY VARCHAR2
4390 ,p_review IN VARCHAR2
4391 ,p_return_timecard IN VARCHAR2 DEFAULT 'Y'
4392 ,p_mode IN VARCHAR2 DEFAULT c_for_approver
4393 ,p_notif_id IN VARCHAR2 DEFAULT null
4394 )
4395 IS
4396 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
4397 l_blocks hxc_self_service_time_deposit.timecard_info;
4398 l_app_period hxc_self_service_time_deposit.building_block_info;
4399 l_block_index NUMBER;
4400 l_parent_id hxc_time_building_blocks.time_building_block_id%TYPE;
4401 l_parent_ovn hxc_time_building_blocks.object_version_number%TYPE;
4402 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
4403 l_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE;
4404 l_app_status VARCHAR2(500);
4405 l_app_recipient fnd_application_tl.application_name%TYPE;
4406 l_approver_id per_all_people_f.person_id%TYPE;
4407 l_attribute_index NUMBER;
4408 l_dummy varchar2(1);
4409 l_time_category_id number;
4410 l_row_data hxc_trans_display_key_utils.translation_row_used;
4411 l_same_app_time_period varchar2(1);
4412 CURSOR c_day_blocks(
4413 p_resource_id IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
4414 ,p_resource_type IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_TYPE%TYPE
4415 ,p_start_time IN hxc_time_building_blocks.start_time%TYPE
4416 ,p_stop_time IN hxc_time_building_blocks.stop_time%TYPE
4417 )
4418 IS
4419 select
4420 tbb1.TIME_BUILDING_BLOCK_ID
4421 ,tbb1.TYPE
4422 ,tbb1.MEASURE
4423 ,tbb1.UNIT_OF_MEASURE
4424 ,tbb1.START_TIME
4425 ,tbb1.STOP_TIME
4426 ,tbb1.PARENT_BUILDING_BLOCK_ID
4427 ,'N' PARENT_IS_NEW
4428 ,tbb1.SCOPE
4429 ,tbb1.OBJECT_VERSION_NUMBER
4430 ,tbb1.APPROVAL_STATUS
4431 ,tbb1.RESOURCE_ID
4432 ,tbb1.RESOURCE_TYPE
4433 ,tbb1.APPROVAL_STYLE_ID
4434 ,tbb1.DATE_FROM
4435 ,tbb1.DATE_TO
4436 ,tbb1.COMMENT_TEXT
4437 ,tbb1.PARENT_BUILDING_BLOCK_OVN
4438 ,'N' NEW
4439 ,'N' CHANGED
4440 ,'N' PROCESS
4441 ,tbb1.application_set_id
4442 ,tbb1.translation_display_key
4443 from hxc_time_building_blocks tbb1
4444 ,hxc_time_building_blocks tc
4445 where tbb1.date_to = hr_general.end_of_time
4446 and tbb1.resource_id = p_resource_id
4447 and tbb1.resource_type = p_resource_type
4448 and tbb1.scope = 'DAY'
4449 and tbb1.start_time >= p_start_time
4450 and tbb1.start_time <= p_stop_time
4451 and tbb1.parent_building_block_id = tc.time_building_block_id
4452 and tbb1.parent_building_block_ovn = tc.object_version_number
4453 and tc.scope = 'TIMECARD'
4454 and tc.date_to = hr_general.end_of_time
4455 order by tbb1.start_time asc;
4456
4457 CURSOR c_app_period(
4458 p_app_period_id hxc_time_building_blocks.time_building_block_id%TYPE
4459 )
4460 IS
4461 select
4462 tbb1.TIME_BUILDING_BLOCK_ID
4463 ,tbb1.TYPE
4464 ,tbb1.MEASURE
4465 ,tbb1.UNIT_OF_MEASURE
4466 ,tbb1.START_TIME
4467 ,tbb1.STOP_TIME
4468 ,tbb1.PARENT_BUILDING_BLOCK_ID
4469 ,'N' PARENT_IS_NEW
4470 ,tbb1.SCOPE
4471 ,tbb1.OBJECT_VERSION_NUMBER
4472 ,tbb1.APPROVAL_STATUS
4473 ,tbb1.RESOURCE_ID
4474 ,tbb1.RESOURCE_TYPE
4475 ,tbb1.APPROVAL_STYLE_ID
4476 ,tbb1.DATE_FROM
4477 ,tbb1.DATE_TO
4478 ,tbb1.COMMENT_TEXT
4479 ,tbb1.PARENT_BUILDING_BLOCK_OVN
4480 ,'N' NEW
4481 ,'N' CHANGED
4482 ,'N' PROCESS
4483 ,tbb1.application_set_id
4484 ,tbb1.translation_display_key
4485 from hxc_time_building_blocks tbb1
4486 where tbb1.time_building_block_id = p_app_period_id
4487 and tbb1.date_to = hr_general.end_of_time;
4488
4489 CURSOR c_timecard_block(
4490 p_resource_id IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
4491 ,p_resource_type IN HXC_TIME_BUILDING_BLOCKS.RESOURCE_TYPE%TYPE
4492 ,p_start_time IN HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
4493 ,p_stop_time IN HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
4494 )
4495 IS
4496 select
4497 tbb1.TIME_BUILDING_BLOCK_ID
4498 ,tbb1.TYPE
4499 ,tbb1.MEASURE
4500 ,tbb1.UNIT_OF_MEASURE
4501 ,tbb1.START_TIME
4502 ,tbb1.STOP_TIME
4503 ,tbb1.PARENT_BUILDING_BLOCK_ID
4504 ,'N' PARENT_IS_NEW
4505 ,tbb1.SCOPE
4506 ,tbb1.OBJECT_VERSION_NUMBER
4507 ,tbb1.APPROVAL_STATUS
4508 ,tbb1.RESOURCE_ID
4509 ,tbb1.RESOURCE_TYPE
4510 ,tbb1.APPROVAL_STYLE_ID
4511 ,tbb1.DATE_FROM
4512 ,tbb1.DATE_TO
4513 ,tbb1.COMMENT_TEXT
4514 ,tbb1.PARENT_BUILDING_BLOCK_OVN
4515 ,'N' NEW
4516 ,'N' CHANGED
4517 ,'N' PROCESS
4518 ,tbb1.application_set_id
4519 ,tbb1.translation_display_key
4520 from hxc_time_building_blocks tbb1
4521 where tbb1.date_to = hr_general.end_of_time
4522 and tbb1.resource_id = p_resource_id
4523 and tbb1.resource_type = p_resource_type
4524 and tbb1.scope = 'TIMECARD'
4525 and p_start_time <= tbb1.stop_time
4526 and p_stop_time >= tbb1.start_time;
4527
4528 CURSOR c_detail_blocks(
4529 p_app_period_id hxc_time_building_blocks.time_building_block_id%TYPE
4530 )
4531 IS
4532 SELECT
4533 tbb1.TIME_BUILDING_BLOCK_ID
4534 ,tbb1.TYPE
4535 ,tbb1.MEASURE
4536 ,tbb1.UNIT_OF_MEASURE
4537 ,tbb1.START_TIME
4538 ,tbb1.STOP_TIME
4539 ,tbb1.PARENT_BUILDING_BLOCK_ID
4540 ,'N' PARENT_IS_NEW
4541 ,tbb1.SCOPE
4542 ,tbb1.OBJECT_VERSION_NUMBER
4543 ,tbb1.APPROVAL_STATUS
4544 ,tbb1.RESOURCE_ID
4545 ,tbb1.RESOURCE_TYPE
4546 ,tbb1.APPROVAL_STYLE_ID
4547 ,tbb1.DATE_FROM
4548 ,tbb1.DATE_TO
4549 ,tbb1.COMMENT_TEXT
4550 ,tbb1.PARENT_BUILDING_BLOCK_OVN
4551 ,'N' NEW
4552 ,'N' CHANGED
4553 ,'N' PROCESS
4554 ,tbb1.application_set_id
4555 ,tbb1.translation_display_key
4556 FROM hxc_ap_detail_links adlinks
4557 ,hxc_time_building_blocks tbb1
4558 WHERE adlinks.application_period_id = p_app_period_id
4559 AND adlinks.time_building_block_id = tbb1.time_building_block_id
4560 AND adlinks.time_building_block_ovn = tbb1.object_version_number
4561 AND tbb1.date_to = hr_general.end_of_time;
4562
4563 CURSOR c_detail_blocks_sup(
4564 p_app_period_id hxc_time_building_blocks.time_building_block_id%TYPE,
4565 p_start_time date,
4566 p_stop_time date
4567 )
4568 IS
4569 SELECT
4570 details.TIME_BUILDING_BLOCK_ID
4571 ,details.TYPE
4572 ,details.MEASURE
4573 ,details.UNIT_OF_MEASURE
4574 ,details.START_TIME
4575 ,details.STOP_TIME
4576 ,details.PARENT_BUILDING_BLOCK_ID
4577 ,'N' PARENT_IS_NEW
4578 ,details.SCOPE
4579 ,details.OBJECT_VERSION_NUMBER
4580 ,details.APPROVAL_STATUS
4581 ,details.RESOURCE_ID
4582 ,details.RESOURCE_TYPE
4583 ,details.APPROVAL_STYLE_ID
4584 ,details.DATE_FROM
4585 ,details.DATE_TO
4586 ,details.COMMENT_TEXT
4587 ,details.PARENT_BUILDING_BLOCK_OVN
4588 ,'N' NEW
4589 ,'N' CHANGED
4590 ,'N' PROCESS
4591 ,details.application_set_id
4592 ,details.translation_display_key
4593 from hxc_time_building_blocks timecard,
4594 hxc_time_building_blocks details,
4595 hxc_time_building_blocks days,
4596 hxc_tc_ap_links hal
4597 where
4598 days.time_building_block_id = details.parent_building_block_id
4599 and days.object_version_number = details.parent_building_block_ovn
4600 and hal.APPLICATION_PERIOD_ID = p_app_period_id
4601 and hal.timecard_id = timecard.time_building_block_id
4602 and days.parent_building_block_id = timecard.time_building_block_id
4603 and days.parent_building_block_ovn = timecard.object_version_number
4604 and details.date_to = hr_general.end_of_time
4605 and days.start_time <= p_stop_time
4606 and days.stop_time >= p_start_time
4607 order by details.translation_display_key;
4608
4609
4610 CURSOR c_app_attribute(
4611 p_app_period_id hxc_time_building_blocks.time_building_block_id%TYPE
4612 )
4613 IS
4614 SELECT hr_general.decode_lookup('HXC_APPROVAL_STATUS', apsum.approval_status)
4615 ,favtl.application_name
4616 ,apsum.approver_id
4617 ,apsum.time_category_id
4618 FROM hxc_app_period_summary apsum
4619 ,fnd_application_tl favtl
4620 ,hxc_time_recipients htr
4621 WHERE apsum.application_period_id = p_app_period_id
4622 AND favtl.application_id = htr.application_id
4623 AND htr.time_recipient_id = apsum.time_recipient_id
4624 AND favtl.language = userenv('LANG');
4625
4626 CURSOR c_is_sup_notification(p_notif_id in wf_notifications.notification_id%TYPE)
4627 is
4628 select 'Y'
4629 from wf_notification_attributes wna,
4630 wf_notification_attributes wnb
4631 where wna.notification_id = wnb.notification_id
4632 and wna.notification_id = p_notif_id
4633 and wna.name = 'FYI_ACTION_CODE'
4634 and wna.text_value = hxc_app_comp_notifications_api.c_action_request_approval
4635 and wnb.name ='FYI_RECIPIENT_CODE'
4636 and wnb.text_value = hxc_app_comp_notifications_api.c_recipient_supervisor;
4637
4638 BEGIN
4639
4640
4641
4642 p_message_string := NULL;
4643
4644 OPEN c_app_period(
4645 p_app_period_id => p_app_period_id
4646 );
4647
4648 FETCH c_app_period INTO l_app_period;
4649
4650 IF c_app_period%NOTFOUND
4651 THEN
4652 RETURN;
4653 END IF;
4654
4655 CLOSE c_app_period;
4656
4657
4658 --find a timecard that overlaps with this app period
4659 l_block_index := 1;
4660
4661 OPEN c_timecard_block(
4662 p_resource_id => p_resource_id
4663 ,p_resource_type => p_resource_type
4664 ,p_start_time => l_app_period.start_time
4665 ,p_stop_time => l_app_period.stop_time
4666 );
4667
4668
4669
4670 FETCH c_timecard_block INTO l_blocks(l_block_index);
4671 IF c_timecard_block%NOTFOUND
4672 THEN
4673 CLOSE c_timecard_block;
4674
4675 RETURN;
4676 END IF;
4677
4678 CLOSE c_timecard_block;
4679
4680 IF g_debug THEN
4681 hr_utility.trace('found timecard');
4682 END IF;
4683
4684 IF trunc(l_blocks(l_block_index).START_TIME) = trunc(l_app_period.start_time)
4685 AND trunc(l_blocks(l_block_index).STOP_TIME) = trunc(l_app_period.stop_time) THEN
4686
4687 l_same_app_time_period := 'Y' ;
4688 ELSE
4689 l_same_app_time_period := 'N' ;
4690
4691 END IF;
4692
4693 IF p_return_timecard = 'N'
4694 THEN
4695 l_blocks(l_block_index).scope := 'APPLICATION_PERIOD';
4696 END IF;
4697
4698
4699 get_attributes(
4700 p_block_id => l_blocks(l_block_index).time_building_block_id
4701 ,p_block_ovn => l_blocks(l_block_index).object_version_number
4702 ,p_attributes => l_attributes
4703 ,p_review => p_review
4704 ,p_new_block_id => l_app_period.time_building_block_id
4705 );
4706
4707 --add approval attribute
4708 OPEN c_app_attribute(p_app_period_id);
4709 FETCH c_app_attribute INTO l_app_status, l_app_recipient, l_approver_id,l_time_category_id;
4710 CLOSE c_app_attribute;
4711
4712 IF l_attributes.count = 0
4713 THEN
4714 l_attribute_index := 1;
4715 ELSE
4716 l_attribute_index := l_attributes.last + 1;
4717 END IF;
4718
4719 l_attributes(l_attribute_index).time_attribute_id := -2;
4720 l_attributes(l_attribute_index).building_block_id := p_app_period_id;
4721 l_attributes(l_attribute_index).attribute_category := 'APPROVAL';
4722 l_attributes(l_attribute_index).attribute1 := l_app_recipient;
4723 l_attributes(l_attribute_index).attribute3 := get_name(l_approver_id);
4724 l_attributes(l_attribute_index).attribute7 := l_app_status;
4725 --add approval attribute
4726
4727 l_blocks(l_block_index).time_building_block_id := l_app_period.time_building_block_id;
4728 l_blocks(l_block_index).object_version_number := l_app_period.object_version_number;
4729 l_blocks(l_block_index).start_time := l_app_period.start_time;
4730 l_blocks(l_block_index).stop_time := l_app_period.stop_time;
4731 if(p_mode = c_for_approver) then
4732 l_blocks(l_block_index).comment_text := get_timecard_comment(p_app_period_id);
4733 else
4734 l_blocks(l_block_index).comment_text := l_app_period.comment_text;
4735 end if;
4736 l_blocks(l_block_index).approval_status := l_app_period.approval_status;
4737
4738
4739 --now get all the day blocks
4740 OPEN c_day_blocks(
4741 p_resource_id => p_resource_id
4742 ,p_resource_type => p_resource_type
4743 ,p_start_time => l_app_period.start_time
4744 ,p_stop_time => l_app_period.stop_time
4745 );
4746
4747 l_block_index := l_block_index + 1;
4748 LOOP
4749 FETCH c_day_blocks INTO l_blocks(l_block_index);
4750 EXIT WHEN c_day_blocks%NOTFOUND;
4751
4752
4753 l_blocks(l_block_index).PARENT_BUILDING_BLOCK_ID := l_app_period.time_building_block_id;
4754 l_blocks(l_block_index).PARENT_BUILDING_BLOCK_OVN := l_app_period.object_version_number;
4755 get_attributes(
4756 p_block_id => l_blocks(l_block_index).time_building_block_id
4757 ,p_block_ovn => l_blocks(l_block_index).object_version_number
4758 ,p_attributes => l_attributes
4759 ,p_review => p_review
4760 );
4761
4762 l_block_index := l_block_index + 1;
4763 END LOOP;
4764
4765 CLOSE c_day_blocks;
4766
4767 --For the 'Notify supervisor on approval request' notification We need to show all the details
4768 --associated with the application period.
4769 if p_notif_id is not null then
4770 open c_is_sup_notification(p_notif_id);
4771 fetch c_is_sup_notification into l_dummy;
4772 end if;
4773 --get detail blocks
4774 if p_notif_id is not null and c_is_sup_notification%found then
4775 close c_is_sup_notification;
4776 OPEN c_detail_blocks_sup(
4777 p_app_period_id => p_app_period_id,
4778 p_start_time => l_app_period.start_time,
4779 p_stop_time => l_app_period.stop_time
4780 );
4781
4782 LOOP
4783 FETCH c_detail_blocks_sup INTO l_blocks(l_block_index);
4784 EXIT WHEN c_detail_blocks_sup%NOTFOUND;
4785
4786 get_attributes(
4787 p_block_id => l_blocks(l_block_index).time_building_block_id
4788 ,p_block_ovn => l_blocks(l_block_index).object_version_number
4789 ,p_attributes => l_attributes
4790 ,p_review => p_review
4791 );
4792
4793 l_block_index := l_block_index + 1;
4794 END LOOP;
4795
4796 CLOSE c_detail_blocks_sup;
4797 ELSE
4798 OPEN c_detail_blocks(
4799 p_app_period_id => p_app_period_id
4800 );
4801
4802 LOOP
4803 FETCH c_detail_blocks INTO l_blocks(l_block_index);
4804 EXIT WHEN c_detail_blocks%NOTFOUND;
4805
4806 get_attributes(
4807 p_block_id => l_blocks(l_block_index).time_building_block_id
4808 ,p_block_ovn => l_blocks(l_block_index).object_version_number
4809 ,p_attributes => l_attributes
4810 ,p_review => p_review
4811 );
4812
4813 ----Bug 5565773
4814 if l_time_category_id is not null then
4815 hxc_trans_display_key_utils.set_row_data
4816 (l_blocks(l_block_index).translation_display_key,
4817 l_row_data);
4818 end if;
4819 l_block_index := l_block_index + 1;
4820 END LOOP;
4821
4822 CLOSE c_detail_blocks;
4823 end if;
4824
4825 p_block_array := hxc_deposit_wrapper_utilities.blocks_to_array(
4826 p_blocks => l_blocks
4827 );
4828 IF l_same_app_time_period = 'N' THEN
4829
4830 l_block_index:= p_block_array.first;
4831 LOOP
4832 EXIT WHEN NOT p_block_array.exists(l_block_index);
4833
4834 p_block_array(l_block_index).TRANSLATION_DISPLAY_KEY := null;
4835 l_block_index:= p_block_array.next(l_block_index);
4836
4837 END LOOP;
4838 END IF;
4839 --Bug 5565773
4840
4841 if l_same_app_time_period = 'Y' AND HXC_TRANS_DISPLAY_KEY_UTILS.missing_rows(l_row_data) then
4842 HXC_TRANS_DISPLAY_KEY_UTILS.remove_empty_rows(l_row_data,
4843 p_block_array);
4844 end if;
4845 p_attribute_array := hxc_deposit_wrapper_utilities.attributes_to_array(
4846 p_attributes => l_attributes
4847 );
4848
4849 translate_alias_timecards(
4850 p_resource_id => p_resource_id
4851 ,p_start_time => to_char(l_app_period.start_time, 'YYYY/MM/DD')
4852 ,p_stop_time => to_char(l_app_period.stop_time, 'YYYY/MM/DD')
4853 ,p_block_array => p_block_array
4854 ,p_attribute_array => p_attribute_array
4855 );
4856
4857 END get_app_period_blocks_by_id;
4858
4859
4860 FUNCTION is_app_period(
4861 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
4862 )
4863 RETURN BOOLEAN
4864 IS
4865
4866 CURSOR c_app_period(
4867 p_block_id hxc_time_building_blocks.time_building_block_id%TYPE
4868 )
4869 IS
4870 SELECT 'Y'
4871 FROM hxc_app_period_summary
4872 WHERE application_period_id = p_block_id;
4873
4874 l_result VARCHAR2(1) := NULL;
4875 BEGIN
4876
4877 OPEN c_app_period(p_block_id);
4878 FETCH c_app_period INTO l_result;
4879 CLOSE c_app_period;
4880
4881 IF l_result IS NULL
4882 THEN
4883 RETURN FALSE;
4884 END IF;
4885
4886 RETURN TRUE;
4887
4888 END is_app_period;
4889
4890
4891 PROCEDURE fetch_blocks_and_attributes(
4892 p_resource_id IN VARCHAR2
4893 ,p_resource_type IN VARCHAR2
4894 ,p_start_time IN VARCHAR2
4895 ,p_stop_time IN VARCHAR2
4896 ,p_timecard_id IN VARCHAR2
4897 ,p_template_code IN VARCHAR2
4898 ,p_approval_status IN VARCHAR2
4899 ,p_create_template IN VARCHAR2
4900 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
4901 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
4902 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
4903 ,p_message_string OUT NOCOPY VARCHAR2
4904 ,p_overwrite IN VARCHAR2
4905 ,p_review IN VARCHAR2
4906 ,p_lock_rowid IN OUT NOCOPY ROWID
4907 ,p_timecard_action in VARCHAR2
4908 )
4909 IS
4910
4911 BEGIN
4912
4913 fetch_blocks_and_attributes(
4914 p_resource_id => p_resource_id
4915 ,p_resource_type => p_resource_type
4916 ,p_start_time => p_start_time
4917 ,p_stop_time => p_stop_time
4918 ,p_timecard_id => p_timecard_id
4919 ,p_template_code => p_template_code
4920 ,p_approval_status => p_approval_status
4921 ,p_create_template => p_create_template
4922 ,p_block_array => p_block_array
4923 ,p_attribute_array => p_attribute_array
4924 ,p_messages => p_messages
4925 ,p_message_string => p_message_string
4926 ,p_overwrite => p_overwrite
4927 ,p_review => p_review
4928 ,p_lock_rowid => p_lock_rowid
4929 ,p_timecard_action => p_timecard_action
4930 ,p_exclude_hours_template => null
4931 );
4932
4933 END;
4934
4935
4936 PROCEDURE fetch_blocks_and_attributes(
4937 p_resource_id IN VARCHAR2
4938 ,p_resource_type IN VARCHAR2
4939 ,p_start_time IN VARCHAR2
4940 ,p_stop_time IN VARCHAR2
4941 ,p_timecard_id IN VARCHAR2
4942 ,p_template_code IN VARCHAR2
4943 ,p_approval_status IN VARCHAR2
4944 ,p_create_template IN VARCHAR2
4945 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
4946 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
4947 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
4948 ,p_message_string OUT NOCOPY VARCHAR2
4949 ,p_overwrite IN VARCHAR2
4950 ,p_review IN VARCHAR2
4951 ,p_lock_rowid IN OUT NOCOPY ROWID
4952 ,p_timecard_action in VARCHAR2
4953 ,p_exclude_hours_template in VARCHAR2
4954 )
4955
4956 is
4957
4958 BEGIN
4959
4960 fetch_blocks_and_attributes(
4961 p_resource_id => p_resource_id
4962 ,p_resource_type => p_resource_type
4963 ,p_start_time => p_start_time
4964 ,p_stop_time => p_stop_time
4965 ,p_timecard_id => p_timecard_id
4966 ,p_template_code => p_template_code
4967 ,p_approval_status => p_approval_status
4968 ,p_create_template => p_create_template
4969 ,p_block_array => p_block_array
4970 ,p_attribute_array => p_attribute_array
4971 ,p_messages => p_messages
4972 ,p_message_string => p_message_string
4973 ,p_overwrite => p_overwrite
4974 ,p_review => p_review
4975 ,p_lock_rowid => p_lock_rowid
4976 ,p_timecard_action => p_timecard_action
4977 ,p_exclude_hours_template => p_exclude_hours_template
4978 ,p_notif_id => null);
4979
4980 END;
4981
4982 PROCEDURE fetch_blocks_and_attributes(
4983 p_resource_id IN VARCHAR2
4984 ,p_resource_type IN VARCHAR2
4985 ,p_start_time IN VARCHAR2
4986 ,p_stop_time IN VARCHAR2
4987 ,p_timecard_id IN VARCHAR2
4988 ,p_template_code IN VARCHAR2
4989 ,p_approval_status IN VARCHAR2
4990 ,p_create_template IN VARCHAR2
4991 ,p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
4992 ,p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
4993 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
4994 ,p_message_string OUT NOCOPY VARCHAR2
4995 ,p_overwrite IN VARCHAR2
4996 ,p_review IN VARCHAR2
4997 ,p_lock_rowid IN OUT NOCOPY ROWID
4998 ,p_timecard_action in VARCHAR2
4999 ,p_exclude_hours_template in VARCHAR2
5000 ,p_notif_id in VARCHAR2)
5001 IS
5002 cursor c_timecard_id
5003 (p_resource_id in number,
5004 p_start_time in date,
5005 p_stop_time in date) is
5006 select timecard_id
5007 from hxc_timecard_summary
5008 where resource_id = p_resource_id
5009 and trunc(start_time) = trunc(p_start_time)
5010 and trunc(stop_time) = trunc(p_stop_time);
5011
5012
5013 l_blocks hxc_self_service_time_deposit.timecard_info;
5014 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
5015 l_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
5016 l_block_index NUMBER := 1;
5017 l_attribute_index NUMBER := 1;
5018 l_block_string VARCHAR2(32767) := NULL;
5019 l_attribute_string VARCHAR2(32767) := NULL;
5020 l_message_string VARCHAR2(2000) := NULL;
5021 l_template_code VARCHAR2(500);
5022 l_pref_template VARCHAR2(2000);
5023 l_template_fcnlty VARCHAR2(1);
5024 l_approval_style_id VARCHAR2(20) := NULL;
5025 l_resource_id VARCHAR2(50) := NULL;
5026 l_lock_success BOOLEAN := FALSE;
5027 l_lock_rowid ROWID;
5028 l_temp_block HXC_BLOCK_TYPE;
5029 l_row_data hxc_trans_display_key_utils.translation_row_used;
5030 l_missing_rows boolean;
5031 l_timecard_id hxc_timecard_summary.timecard_id%type;
5032 l_temp_attributes hxc_attribute_table_type;
5033 i number;
5034
5035 l_proc VARCHAR2(50);
5036
5037 l_resp_id NUMBER;
5038 l_resp_appl_id NUMBER;
5039 BEGIN
5040 g_debug := hr_utility.debug_enabled;
5041
5042 IF g_debug THEN
5043 l_proc := 'fetch_blocks_and_attributes';
5044 hr_utility.set_location (g_package||l_proc, 20);
5045 END IF;
5046
5047 --if(p_timecard_action <> 'Template') then
5048 if(p_timecard_action NOT IN ('Template','Export')) then
5049 --
5050 -- Try getting a lock for the period, or the timecard
5051 --
5052 request_lock
5053 (p_resource_id => p_resource_id
5054 ,p_resource_type => p_resource_type
5055 ,p_start_time => p_start_time
5056 ,p_stop_time => p_stop_time
5057 ,p_timecard_id => p_timecard_id
5058 ,p_messages => p_messages
5059 ,p_lock_success => l_lock_success
5060 ,p_lock_rowid => p_lock_rowid
5061 ,p_timecard_action => p_timecard_action
5062 );
5063
5064 else
5065 p_messages := hxc_message_table_type();
5066 end if;
5067
5068 if(p_messages.count=0) then
5069 --
5070 -- Lock was sucessful, we can continue
5071 --
5072
5073 -- Commented for bug 8468802
5074 -- Making this call at a later point
5075 /* IF p_resource_id IS NOT NULL
5076 THEN
5077 hxc_preference_evaluation.get_tc_resp(p_resource_id, TO_DATE(p_start_time, 'YYYY/MM/DD'),TO_DATE(p_stop_time,'YYYY/MM/DD'),l_resp_id,l_resp_appl_id);
5078 l_approval_style_id := hxc_preference_evaluation.resource_preferences(
5079 p_resource_id,
5080 'TS_PER_APPROVAL_STYLE',
5081 1,
5082 l_resp_id
5083 );
5084 END IF;*/
5085
5086 IF g_debug THEN
5087 hr_utility.set_location ( g_package||l_proc, 20);
5088 END IF;
5089
5090 IF p_timecard_id IS NOT NULL
5091 AND is_app_period(TO_NUMBER(p_timecard_id))
5092 THEN
5093 get_app_period_blocks_by_id(
5094 p_resource_id => p_resource_id
5095 ,p_resource_type => p_resource_type
5096 ,p_app_period_id => p_timecard_id
5097 ,p_block_array => p_block_array
5098 ,p_attribute_array => p_attribute_array
5099 ,p_message_string => l_message_string
5100 ,p_review => p_review
5101 ,p_notif_id => p_notif_id);
5102
5103 p_message_string := l_message_string;
5104
5105 RETURN;
5106
5107 END IF;
5108
5109 -- Moved this call to this place for bug 8468802
5110 IF p_resource_id IS NOT NULL
5111 THEN
5112 hxc_preference_evaluation.get_tc_resp(p_resource_id, TO_DATE(p_start_time, 'YYYY/MM/DD'),TO_DATE(p_stop_time,'YYYY/MM/DD'),l_resp_id,l_resp_appl_id);
5113 l_approval_style_id := hxc_preference_evaluation.resource_preferences(
5114 p_resource_id,
5115 'TS_PER_APPROVAL_STYLE',
5116 1,
5117 l_resp_id
5118 );
5119 END IF;
5120
5121 IF g_debug THEN
5122 hr_utility.set_location ( g_package||l_proc, 28);
5123 END IF;
5124
5125
5126 --(1) First check if we need to apply a template
5127 IF p_template_code IS NOT NULL
5128 THEN
5129 get_blocks_from_template(
5130 p_resource_id => p_resource_id
5131 ,p_resource_type => p_resource_type
5132 ,p_start_time => p_start_time
5133 ,p_stop_time => p_stop_time
5134 ,p_template_code => p_template_code
5135 ,p_approval_status => p_approval_status
5136 ,p_approval_style => l_approval_style_id
5137 ,p_block_array => p_block_array
5138 ,p_attribute_array => p_attribute_array
5139 ,p_message_string => l_message_string
5140 ,p_overwrite => p_overwrite
5141 ,p_exclude_hours_template => p_exclude_hours_template -- pass the new param
5142 ,p_messages => p_messages
5143 );
5144
5145 IF g_debug THEN
5146 hr_utility.set_location ( g_package||l_proc, 30);
5147 END IF;
5148
5149 p_message_string := l_message_string;
5150
5151 -- v115.58 kSethi
5152 -- Adding new check to verify all DAYS are present
5153 -- 115.60 ARundell
5154 -- Added and clause. We should only call this check
5155 -- if there are some blocks to check!
5156 -- 115.86 mbhammar
5157 -- append blocks would take care of chk_all_days_in_block for p_overwrite = 'Y'
5158 /*
5159 IF ((p_overwrite = 'Y') AND (p_block_array.COUNT > 0))
5160 then
5161
5162 chk_all_days_in_block(
5163 p_resource_id => p_resource_id
5164 ,p_resource_type => p_resource_type
5165 ,p_start_time => p_start_time
5166 ,p_stop_time => p_stop_time
5167 ,p_template_code => p_template_code
5168 ,p_block_array => p_block_array
5169 );
5170 end if;
5171 */
5172
5173 RETURN;
5174 END IF;
5175
5176 --(2)if this flag is set to 'Y', create an empty template from scratch
5177 --This was built at Joel's request, may not be in use
5178 IF p_create_template = 'Y'
5179 THEN
5180 RETURN;
5181 END IF;
5182
5183 IF g_debug THEN
5184 hr_utility.set_location ( g_package||l_proc, 40);
5185
5186 hr_utility.trace(' p_resource_id=' || p_resource_id);
5187 hr_utility.trace('p_resource_type=' || p_resource_type);
5188 hr_utility.trace('p_start_time=' || p_start_time);
5189 hr_utility.trace('p_stop_time=' || p_stop_time);
5190 hr_utility.trace('p_timecard_id=' || p_timecard_id);
5191 END IF;
5192
5193 --(3)retrieve an existing timecard
5194 -- Make sure we replace the display key
5195 -- if possible.
5196 --
5197 l_missing_rows := true;
5198 if(p_timecard_id is null) then
5199 open c_timecard_id
5200 (p_resource_id,
5201 to_date(p_start_time,'YYYY/MM/DD'),
5202 to_date(p_stop_time,'YYYY/MM/DD')
5203 );
5204 fetch c_timecard_id into l_timecard_id;
5205 close c_timecard_id;
5206 else
5207 l_timecard_id := p_timecard_id;
5208 end if;
5209
5210 if(p_attribute_array is null) then
5211 l_temp_attributes := hxc_attribute_table_type();
5212 p_attribute_array := hxc_attribute_table_type();
5213 else
5214 l_temp_attributes := p_attribute_array;
5215 end if;
5216
5217 hxc_block_collection_utils.get_timecard
5218 (p_timecard_id => l_timecard_id,
5219 p_blocks => p_block_array,
5220 p_attributes => l_temp_attributes,
5221 p_row_data => l_row_data,
5222 p_missing_rows => l_missing_rows
5223 );
5224
5225 if(l_temp_attributes is not null) then
5226 i := l_temp_attributes.first;
5227 Loop
5228 Exit when not l_temp_attributes.exists(i);
5229 if(l_temp_attributes(i).attribute_category in ('SECURITY','REASON')) then
5230 -- Do not send these attributes back to the middle tier
5231 null;
5232 else
5233 p_attribute_array.extend;
5234 p_attribute_array(p_attribute_array.last) := l_temp_attributes(i);
5235 end if;
5236 i := l_temp_attributes.next(i);
5237 End Loop;
5238 end if;
5239
5240 IF g_debug THEN
5241 hr_utility.set_location ( g_package||l_proc, 50);
5242 END IF;
5243
5244 -- if there is an existing timecard, return the info in string
5245 IF p_block_array is not null THEN
5246
5247 if (l_missing_rows) then
5248 hxc_trans_display_key_utils.remove_empty_rows
5249 (p_row_data => l_row_data,
5250 p_blocks => p_block_array
5251 );
5252 end if;
5253
5254 IF g_debug THEN
5255 hr_utility.set_location ( g_package||l_proc, 60);
5256 END IF;
5257
5258 -- call translator alias package
5259 translate_alias_timecards(
5260 p_resource_id => p_resource_id
5261 ,p_start_time => p_start_time
5262 ,p_stop_time => p_stop_time
5263 ,p_block_array => p_block_array
5264 ,p_attribute_array => p_attribute_array
5265 );
5266
5267 if(p_review = 'TIMECARD-TEMPLATE_DUPLICATE') THEN
5268 hxc_block_attribute_update.replace_ids
5269 (p_blocks => p_block_array
5270 ,p_attributes => p_attribute_array,
5271 p_duplicate_template => TRUE
5272 );
5273 --Only in case of duplicate mode, we replace the resourceids if they dont match.
5274 --((i.e) if an administrator tries to duplicate a template created by someone else.)
5275 HXC_DEPOSIT_WRAPPER_UTILITIES.replace_resource_id(p_block_array,p_resource_id);
5276 END IF;
5277
5278 p_message_string := NULL;
5279 RETURN;
5280 ELSE
5281 p_block_array := hxc_block_table_type();
5282 p_attribute_array := hxc_attribute_table_type();
5283 END IF;
5284
5285
5286 IF g_debug THEN
5287 hr_utility.trace('app period jxtan');
5288 END IF;
5289 --(4) there is no existing timecard, check if this period
5290 -- corresponds to an application period
5291 -- this part is here so that the url we created for
5292 -- workflow notification before ELA will still work
5293
5294 get_application_period_blocks(
5295 p_resource_id => p_resource_id
5296 ,p_resource_type => p_resource_type
5297 ,p_start_time => p_start_time
5298 ,p_stop_time => p_stop_time
5299 ,p_block_array => p_block_array
5300 ,p_attribute_array => p_attribute_array
5301 ,p_message_string => l_message_string
5302 ,p_review => p_review
5303 );
5304
5305
5306 IF p_block_array.count > 0
5307 THEN
5308 p_message_string := l_message_string;
5309
5310 RETURN;
5311 END IF;
5312
5313
5314
5315 --(5) if there is no existing timecard and no application
5316 -- period for this time period, check default templates
5317 IF (p_resource_id IS NOT NULL
5318 AND p_resource_type IS NOT NULL
5319 AND p_start_time IS NOT NULL
5320 AND p_stop_time IS NOT NULL
5321 AND p_timecard_id IS NULL
5322 )
5323 THEN
5324
5325 l_template_fcnlty := hxc_preference_evaluation.resource_preferences(
5326 p_resource_id,
5327 'TC_W_TMPLT_FCNLTY',
5328 1,
5329 l_resp_id
5330 );
5331 -- return NULL if this person doesn't have template functionality
5332 IF l_template_fcnlty <> 'Y'
5333 THEN
5334 p_message_string := NULL;
5335
5336 -- OTL- ABS Integration
5337
5338 hxc_retrieve_absences.add_absence_types ( p_person_id => p_resource_id,
5339 p_start_date => TO_DATE(p_start_time,'yyyy/mm/dd'),
5340 p_end_date => TO_DATE(p_stop_time,'yyyy/mm/dd'),
5341 p_approval_style_id => l_approval_style_id,
5342 p_lock_rowid => p_lock_rowid,
5343 p_block_array => p_block_array,
5344 p_attribute_array => p_attribute_array );
5345
5346 p_messages := hxc_retrieve_absences.g_messages;
5347 hr_utility.trace('ABS Message count = '||p_messages.count);
5348 p_message_string := hxc_retrieve_absences.g_message_string;
5349 IF p_messages.COUNT > 0
5350 THEN
5351 IF g_debug
5352 THEN
5353 for i in p_messages.first..p_messages.last
5354 loop
5355 hr_utility.trace('ABS : message_name '||p_messages(i).message_name);
5356 hr_utility.trace('ABS : message_level '||p_messages(i).message_level);
5357 END LOOP;
5358 END IF;
5359 END IF;
5360
5361 RETURN;
5362 END IF;
5363
5364 -- this user has template functionality, thus
5365 -- check template_code or pref setting for user or admin default
5366 l_template_code := p_template_code;
5367 IF l_template_code IS NULL
5368 THEN
5369 -- we need to check preference to get the template associated to
5370 -- this user
5371 l_template_code := get_pref_template(
5372 p_resource_id => TO_NUMBER(p_resource_id)
5373 );
5374 END IF;
5375
5376 IF g_debug THEN
5377 hr_utility.set_location ( g_package||l_proc, 100);
5378 END IF;
5379
5380 IF l_template_code IS NOT NULL
5381 THEN
5382
5383 -- Bug 8854684
5384 -- Added the below condition to restrict
5385 -- Default template in case the action is Import of timecard
5386 -- from a spreadsheet.
5387 IF p_timecard_action <> 'CreateImportTC'
5388 THEN
5389
5390
5391 get_blocks_from_template(
5392 p_resource_id => p_resource_id
5393 ,p_resource_type => p_resource_type
5394 ,p_start_time => p_start_time
5395 ,p_stop_time => p_stop_time
5396 ,p_template_code => l_template_code
5397 ,p_approval_status => p_approval_status
5398 ,p_approval_style => l_approval_style_id
5399 ,p_block_array => p_block_array
5400 ,p_attribute_array => p_attribute_array
5401 ,p_message_string => l_message_string
5402 ,p_messages => p_messages
5403 );
5404
5405 IF g_debug THEN
5406 hr_utility.set_location ( g_package||l_proc, 110);
5407 END IF;
5408
5409 p_message_string := l_message_string;
5410
5411 -- v115.58 kSethi
5412 -- Adding new check to verify all DAYS are present
5413 -- Note: here we don't need to check for p_overwrite 'coz the above call
5414 -- to get_blocks_from_templates does not pass any p_overwrite, in which case
5415 -- it is always taken as 'Y'
5416 -- 115.60 ARundell
5417 -- Only check the blocks if there are some to return.
5418 IF(p_block_array.COUNT>0) then
5419
5420 chk_all_days_in_block(
5421 p_resource_id => p_resource_id
5422 ,p_resource_type => p_resource_type
5423 ,p_start_time => p_start_time
5424 ,p_stop_time => p_stop_time
5425 ,p_template_code => p_template_code
5426 ,p_block_array => p_block_array
5427 );
5428
5429 END IF;
5430 END IF;
5431
5432 END IF; -- l_template_code IS NOT NULL
5433
5434 -- OTL - ABS Integration
5435 hxc_retrieve_absences.add_absence_types ( p_person_id => p_resource_id,
5436 p_start_date => TO_DATE(p_start_time,'yyyy/mm/dd'),
5437 p_end_date => TO_DATE(p_stop_time,'yyyy/mm/dd'),
5438 p_approval_style_id => l_approval_style_id,
5439 p_lock_rowid => p_lock_rowid,
5440 p_block_array => p_block_array,
5441 p_attribute_array => p_attribute_array );
5442 p_messages := hxc_retrieve_absences.g_messages;
5443 hr_utility.trace('ABS Message count2 = '||p_messages.count);
5444
5445 IF p_messages.COUNT > 0
5446 THEN
5447 IF g_debug
5448 THEN
5449 for i in p_messages.first..p_messages.last
5450 loop
5451 hr_utility.trace('ABS : message_name '||p_messages(i).message_name);
5452 hr_utility.trace('ABS : message_level '||p_messages(i).message_level);
5453 END LOOP;
5454 END IF;
5455 END IF;
5456
5457 p_message_string := hxc_retrieve_absences.g_message_string;
5458
5459
5460 RETURN;
5461
5462
5463 END IF;
5464
5465 end if; -- was this a sucessful lock?
5466
5467 END fetch_blocks_and_attributes;
5468
5469
5470 PROCEDURE add_block_attributes(
5471 p_final_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE
5472 ,p_final_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
5473 ,p_new_block_array IN HXC_BLOCK_TABLE_TYPE
5474 ,p_new_attribute_array IN HXC_ATTRIBUTE_TABLE_TYPE
5475 ,p_start_att_id IN NUMBER
5476 )
5477 IS
5478 l_final_index NUMBER;
5479 l_new_index NUMBER;
5480 l_start_att_id NUMBER := p_start_att_id;
5481 BEGIN
5482 l_final_index := NVL(p_final_block_array.last, 0);
5483 l_new_index := p_new_block_array.first;
5484
5485 LOOP
5486 EXIT WHEN NOT p_new_block_array.exists(l_new_index);
5487
5488 p_final_block_array.extend;
5489 l_final_index := l_final_index + 1;
5490 p_final_block_array(l_final_index) := p_new_block_array(l_new_index);
5491
5492 l_new_index := p_new_block_array.next(l_new_index);
5493 END LOOP;
5494
5495 l_final_index := NVL(p_final_attribute_array.last, 0);
5496 l_new_index := p_new_attribute_array.first;
5497
5498 LOOP
5499 EXIT WHEN NOT p_new_attribute_array.exists(l_new_index);
5500
5501 p_final_attribute_array.extend;
5502 l_final_index := l_final_index + 1;
5503 p_final_attribute_array(l_final_index) := p_new_attribute_array(l_new_index);
5504 p_final_attribute_array(l_final_index).time_attribute_id := l_start_att_id;
5505 l_start_att_id := l_start_att_id - 1;
5506
5507 l_new_index := p_new_attribute_array.next(l_new_index);
5508 END LOOP;
5509
5510 END add_block_attributes;
5511
5512
5513 PROCEDURE fetch_appl_periods(
5514 p_resource_id IN VARCHAR2
5515 ,p_resource_type IN VARCHAR2
5516 ,p_timecard_id IN VARCHAR2
5517 ,p_block_array OUT NOCOPY HXC_BLOCK_TABLE_TYPE
5518 ,p_attribute_array OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
5519 ,p_message_string OUT NOCOPY VARCHAR2
5520 )
5521 IS
5522
5523 CURSOR c_app_periods(
5524 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
5525 )
5526 IS
5527 SELECT tal.application_period_id,
5528 ts.start_time,
5529 ts.stop_time
5530 FROM hxc_tc_ap_links tal, hxc_timecard_summary ts
5531 WHERE ts.timecard_id = p_timecard_id
5532 and ts.timecard_id = tal.timecard_id;
5533
5534 l_app_period_id hxc_time_building_blocks.time_building_block_id%TYPE;
5535 l_start_time hxc_timecard_summary.start_time%type;
5536 l_stop_time hxc_timecard_summary.stop_time%type;
5537 l_block_array HXC_BLOCK_TABLE_TYPE;
5538 l_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
5539 l_message_string VARCHAR(2000) := NULL;
5540 l_count NUMBER;
5541 l_start_block_id NUMBER;
5542 l_start_att_id NUMBER;
5543 BEGIN
5544 p_block_array := HXC_BLOCK_TABLE_TYPE();
5545 p_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
5546
5547 l_block_array := HXC_BLOCK_TABLE_TYPE();
5548 l_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
5549
5550 l_count := 0;
5551 l_start_block_id := -2;
5552 l_start_att_id := -2;
5553
5554 OPEN c_app_periods(p_timecard_id);
5555
5556 LOOP
5557 FETCH c_app_periods INTO l_app_period_id, l_start_time, l_stop_time;
5558 EXIT WHEN c_app_periods%NOTFOUND;
5559
5560 get_app_period_blocks_by_id
5561 (l_app_period_id,
5562 l_start_time,
5563 l_stop_time,
5564 l_block_array,
5565 l_attribute_array
5566 );
5567
5568 if l_count > 0 then
5569 assign_block_ids
5570 (p_start_id => l_start_block_id,
5571 p_blocks => l_block_array,
5572 p_attributes => l_attribute_array
5573 );
5574
5575 l_start_block_id := l_start_block_id - l_block_array.count;
5576 l_start_att_id := l_start_att_id - l_attribute_array.count;
5577
5578 end if;
5579
5580 add_block_attributes
5581 (p_final_block_array => p_block_array,
5582 p_final_attribute_array => p_attribute_array,
5583 p_new_block_array => l_block_array,
5584 p_new_attribute_array => l_attribute_array,
5585 p_start_att_id => l_start_att_id
5586 );
5587
5588 l_count := l_count + 1;
5589
5590 END LOOP;
5591
5592 CLOSE c_app_periods;
5593
5594 END fetch_appl_periods;
5595
5596 PROCEDURE check_blocks_from_template(
5597 p_resource_id IN VARCHAR2
5598 ,p_resource_type IN VARCHAR2
5599 ,p_start_time IN VARCHAR2
5600 ,p_stop_time IN VARCHAR2
5601 ,p_template_code IN VARCHAR2
5602 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
5603 )
5604 IS
5605
5606 l_template_handle VARCHAR2(500) := '';
5607 l_template_action VARCHAR2(20) :='';
5608 l_temp_blocks hxc_self_service_time_deposit.timecard_info;
5609 l_blocks HXC_BLOCK_TABLE_TYPE;
5610 l_attributes HXC_ATTRIBUTE_TABLE_TYPE;
5611 l_temp_attributes hxc_self_service_time_deposit.building_block_attribute_info;
5612 l_app_attributes hxc_self_service_time_deposit.app_attributes_info;
5613 l_block_string VARCHAR2(32767) := '';
5614 l_attribute_string VARCHAR2(32767) := '';
5615 l_message_string VARCHAR2(32767) := '';
5616 l_block_index NUMBER := 1;
5617 l_attribute_index NUMBER := 1;
5618 l_attribute_count NUMBER;
5619 l_detail_start_date DATE;
5620 l_detail_stop_date DATE;
5621
5622 l_template_procedure HXC_TIME_RECIPIENTS.APPL_DYNAMIC_TEMPLATE_PROCESS%TYPE;
5623
5624 l_process_id HXC_RETRIEVAL_PROCESSES.RETRIEVAL_PROCESS_ID%TYPE;
5625 l_tp_resource_id NUMBER := TO_NUMBER(p_resource_id);
5626 l_tp_start_time DATE := TO_DATE(p_start_time, 'YYYY/MM/DD');
5627 l_tp_stop_time DATE := TO_DATE(p_stop_time, 'YYYY/MM/DD');
5628 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
5629 l_block_count NUMBER;
5630 l_new_stop_time DATE;
5631 l_old_start_time DATE;
5632 l_old_stop_time DATE;
5633 l_proc VARCHAR2(70);
5634
5635 CURSOR c_dyn_template_procedure(
5636 p_dyn_template_app IN VARCHAR2
5637 )
5638 IS
5639 select htr.appl_dynamic_template_process
5640 from hxc_time_recipients htr,
5641 fnd_application fa
5642 where fa.application_short_name = p_dyn_template_app
5643 and htr.application_id = fa.application_id;
5644
5645 CURSOR c_otm_retrieval_process(
5646 p_process_name IN hxc_retrieval_processes.NAME%TYPE
5647 )
5648 IS
5649 SELECT retrieval_process_id
5650 FROM hxc_retrieval_processes
5651 WHERE name = p_process_name;
5652
5653 CURSOR c_retrieval_process(
5654 p_dyn_template_process IN hxc_time_recipients.appl_dynamic_template_process%TYPE
5655 )
5656 IS
5657 SELECT hrp.retrieval_process_id
5658 FROM hxc_retrieval_processes hrp,
5659 hxc_time_recipients htr
5660 WHERE htr.appl_dynamic_template_process = p_dyn_template_process
5661 AND htr.time_recipient_id = hrp.time_recipient_id;
5662
5663 BEGIN
5664
5665 g_debug := hr_utility.debug_enabled;
5666
5667 get_template_info(
5668 p_template_code => p_template_code
5669 ,p_template_handle => l_template_handle
5670 ,p_template_action => l_template_action
5671 );
5672
5673 IF g_debug THEN
5674 l_proc := 'get_blocks_from_template';
5675 hr_utility.set_location ( g_package||l_template_action, 130);
5676 END IF;
5677
5678
5679 IF l_template_action = 'APP'
5680 OR (l_template_action = 'SYS' AND l_template_handle = 'WORK_SCHEDULE')
5681 THEN
5682
5683 -- for OTM work schedule
5684 IF l_template_handle = 'WORK_SCHEDULE'
5685 THEN
5686
5687 IF g_debug THEN
5688 hr_utility.set_location ( g_package||l_proc, 140);
5689 END IF;
5690
5691 HXT_TIMECARD_INFO.GENERATE_TIME(
5692 p_resource_id => TO_NUMBER(p_resource_id)
5693 ,p_start_time => TO_DATE(p_start_time, 'YYYY/MM/DD')
5694 ,p_stop_time => TO_DATE(p_stop_time, 'YYYY/MM/DD')
5695 ,p_app_attributes => l_app_attributes
5696 ,p_timecard => l_temp_blocks
5697 ,p_messages => p_messages
5698 );
5699
5700 OPEN c_otm_retrieval_process(
5701 p_process_name => 'BEE Retrieval Process'
5702 );
5703 FETCH c_otm_retrieval_process INTO l_process_id;
5704
5705 IF c_otm_retrieval_process%NOTFOUND
5706 THEN
5707 CLOSE c_otm_retrieval_process;
5708 FND_MESSAGE.SET_NAME('HXC','HXC_NO_RETRIEVAL_PROCESS');
5709 FND_MESSAGE.RAISE_ERROR;
5710 ELSE
5711 CLOSE c_otm_retrieval_process;
5712 END IF;
5713
5714 ELSE
5715 -- find the corresponding dynamic template function for the
5716 -- specific application
5717 OPEN c_dyn_template_procedure(
5718 p_dyn_template_app => l_template_handle
5719 );
5720
5721 FETCH c_dyn_template_procedure INTO l_template_procedure;
5722
5723 IF g_debug THEN
5724 hr_utility.set_location ( 'PJRM=='||l_template_procedure, 150);
5725 END IF;
5726
5727 IF c_dyn_template_procedure%NOTFOUND
5728 THEN
5729 CLOSE c_dyn_template_procedure;
5730
5731 RETURN;
5732 END IF;
5733
5734 CLOSE c_dyn_template_procedure;
5735
5736 IF g_debug THEN
5737 hr_utility.set_location ( 'calling get_dynamic_templates_info==', 160);
5738 END IF;
5739 get_dynamic_templates_info
5740 ( l_template_procedure,
5741 l_tp_resource_id,
5742 l_tp_start_time,
5743 l_tp_stop_time,
5744 l_attribute_string,
5745 l_block_string,
5746 l_message_string,
5747 p_messages);
5748
5749 IF g_debug THEN
5750 hr_utility.set_location ( 'completed get_dynamic_templates_info=='||p_messages.count, 170);
5751 END IF;
5752
5753 OPEN c_retrieval_process(
5754 p_dyn_template_process => l_template_procedure
5755 );
5756
5757 FETCH c_retrieval_process INTO l_process_id;
5758 IF c_retrieval_process%NOTFOUND
5759 THEN
5760 CLOSE c_retrieval_process;
5761
5762 FND_MESSAGE.SET_NAME('HXC','HXC_NO_RETRIEVAL_PROCESS');
5763 FND_MESSAGE.RAISE_ERROR;
5764 ELSE
5765 CLOSE c_retrieval_process;
5766 END IF;
5767
5768 l_temp_blocks := hxc_deposit_wrapper_utilities.string_to_blocks(
5769 p_block_string => l_block_string
5770 );
5771 l_app_attributes := hxc_deposit_wrapper_utilities.string_to_attributes(
5772 p_attribute_string => l_attribute_string
5773 );
5774 END IF;
5775 RETURN;
5776 END IF;
5777
5778 -- if there is an existing template, adjust the data
5779 l_block_count := l_temp_blocks.count;
5780 l_attribute_count := l_temp_attributes.count;
5781
5782 IF l_block_count < 0
5783 THEN
5784 hxc_timecard_message_helper.addErrorToCollection
5785 (p_messages
5786 ,'HXC_INVALID_DYNAMIC_TEMPL1'
5787 ,hxc_timecard.c_error
5788 ,null
5789 ,null
5790 ,hxc_timecard.c_hxc
5791 ,null
5792 ,null
5793 ,null
5794 ,null
5795 );
5796 END IF;
5797
5798
5799 END check_blocks_from_template;
5800
5801
5802 FUNCTION get_timecard_transferred_to(f_timecard_id HXC_TIMECARD_SUMMARY.TIMECARD_ID%TYPE,
5803 f_timecard_ovn HXC_TIMECARD_SUMMARY.TIMECARD_OVN%TYPE) RETURN varchar2
5804 is
5805
5806 CURSOR c_get_latest_timecard_details(l_timecard_id hxc_timecard_summary.timecard_id%TYPE,
5807 l_timecard_ovn hxc_timecard_summary.timecard_ovn%TYPE) IS
5808 SELECT /*+ ordered */
5809 lat.time_building_block_id,
5810 lat.object_version_number
5811 FROM hxc_timecard_summary sum,
5812 hxc_time_building_blocks day,
5813 hxc_time_building_blocks det,
5814 hxc_latest_details lat
5815 WHERE sum.timecard_id = l_timecard_id
5816 AND sum.timecard_ovn = l_timecard_ovn
5817 AND day.parent_building_block_id = sum.timecard_id
5818 AND day.parent_building_block_ovn = sum.timecard_ovn
5819 AND det.parent_building_block_id = day.time_building_block_id
5820 AND det.parent_building_block_ovn = day.object_version_number
5821 AND(det.measure IS NOT NULL OR(det.start_time IS NOT NULL AND det.stop_time IS NOT NULL))
5822 AND lat.time_building_block_id = det.time_building_block_id ;
5823
5824
5825 CURSOR c_get_transaction_id(l_bb_id hxc_transaction_details.time_building_block_id%TYPE,
5826 l_bb_ovn hxc_transaction_details.time_building_block_ovn%TYPE) IS
5827 SELECT ht.transaction_id,
5828 ht.transaction_process_id
5829 FROM hxc_transaction_details htd,
5830 hxc_transactions ht
5831 WHERE htd.transaction_id = ht.transaction_id
5832 AND ht.type = 'RETRIEVAL'
5833 AND ht.status = 'SUCCESS'
5834 AND htd.status = 'SUCCESS'
5835 AND htd.time_building_block_id = l_bb_id
5836 AND htd.time_building_block_ovn = l_bb_ovn;
5837
5838
5839 --------------------------------------------------------------------------
5840
5841 l_timecard_id hxc_timecard_summary.timecard_id%TYPE;
5842 l_timecard_ovn hxc_timecard_summary.timecard_ovn%TYPE;
5843 l_resource_id hxc_timecard_summary.resource_id%TYPE;
5844 l_start_time hxc_timecard_summary.start_time%TYPE;
5845
5846
5847 TYPE building_blocks_tab IS TABLE OF NUMBER;
5848
5849 bb_id_tab building_blocks_tab;
5850 bb_ovn_tab building_blocks_tab;
5851 l_transaction_id_tab building_blocks_tab;
5852 l_transaction_process_id_tab building_blocks_tab;
5853
5854 l_index NUMBER;
5855 l_transfer_to VARCHAR2(400) := 'None';
5856 l_time_recipient_name hxc_application_set_comps_v.time_recipient_name%TYPE;
5857
5858 -----------------------------------------------------------------------------
5859
5860 BEGIN
5861
5862 l_timecard_id := f_timecard_id;
5863 l_timecard_ovn := f_timecard_ovn;
5864
5865
5866 ----------------------------- get latest timecard details------------------------------------------
5867
5868
5869 OPEN c_get_latest_timecard_details(l_timecard_id,l_timecard_ovn);
5870 FETCH c_get_latest_timecard_details bulk collect
5871 INTO bb_id_tab,
5872 bb_ovn_tab;
5873 CLOSE c_get_latest_timecard_details;
5874
5875 hr_utility.trace('Detail Building Blocks Count :' || bb_id_tab.COUNT);
5876
5877
5878
5879 ------------------------------ check whether data has transfer or not------------------------------
5880
5881
5882 l_index := bb_id_tab.FIRST;
5883 LOOP
5884 EXIT
5885 WHEN NOT bb_id_tab.EXISTS(l_index);
5886
5887 hr_utility.trace(bb_id_tab(l_index) || '-' || bb_ovn_tab(l_index));
5888
5889 OPEN c_get_transaction_id(bb_id_tab(l_index), bb_ovn_tab(l_index));
5890 FETCH c_get_transaction_id bulk collect
5891 INTO l_transaction_id_tab,l_transaction_process_id_tab;
5892 CLOSE c_get_transaction_id;
5893
5894 IF l_transaction_id_tab IS NULL THEN
5895 l_transfer_to := 'None';
5896 EXIT;
5897 END IF;
5898
5899 l_index := bb_id_tab.NEXT(l_index);
5900
5901 END LOOP;
5902
5903
5904
5905
5906 --------------------------------- get time recipient name's for which timecard has transferred------------
5907
5908
5909
5910 IF l_transaction_id_tab IS NOT NULL THEN
5911
5912 IF g_debug
5913 THEN
5914 hr_utility.trace('l_transfer_to : YET TO DECIDE');
5915 hr_utility.trace('Transaction Ids Count :' || l_transaction_id_tab.COUNT);
5916 hr_utility.trace('l_transfer_to :' || l_transfer_to);
5917 END IF;
5918
5919 l_index := l_transaction_id_tab.FIRST;
5920
5921 LOOP
5922 EXIT
5923 WHEN NOT l_transaction_id_tab.EXISTS(l_index);
5924 hr_utility.trace('l_transaction_id_tab('||l_index||'):'||l_transaction_id_tab(l_index)
5925 ||'-'||'l_transaction_process_id_tab('||l_index||'):'||l_transaction_process_id_tab(l_index));
5926
5927 IF l_transaction_process_id_tab(l_index) = -1 THEN
5928 SELECT retrieval_process_id
5929 INTO l_transaction_process_id_tab(l_index)
5930 FROM hxc_retrieval_processes
5931 WHERE name = 'BEE Retrieval Process';
5932 END IF;
5933
5934 SELECT time_recipient_name
5935 INTO l_time_recipient_name
5936 FROM hxc_application_set_comps_v
5937 WHERE application_set_id IN
5938 (SELECT application_set_id
5939 FROM hxc_time_building_blocks
5940 WHERE time_building_block_id = l_timecard_id
5941 and object_version_number = l_timecard_ovn
5942 )
5943 AND time_recipient_id IN
5944 (SELECT time_recipient_id
5945 FROM hxc_retrieval_processes
5946 WHERE retrieval_process_id = l_transaction_process_id_tab(l_index));
5947
5948
5949
5950 hr_utility.trace('l_time_recipient_name :' || l_time_recipient_name);
5951
5952 IF l_transfer_to = 'None' AND l_time_recipient_name IS NOT NULL THEN
5953 l_transfer_to := l_time_recipient_name;
5954 ELSIF instrb(l_transfer_to, l_time_recipient_name) = 0 THEN
5955 l_transfer_to := l_transfer_to || ', ' || l_time_recipient_name;
5956 END IF;
5957
5958 l_index := l_transaction_id_tab.NEXT(l_index);
5959
5960 END LOOP;
5961
5962 END IF; -- IF l_transaction_id IS NOT NULL THEN
5963
5964 ------------------------------- pass the transfer_to data to TcActivitiesPG .------------------
5965
5966 hr_utility.trace('l_transfer_to :' || l_transfer_to);
5967 hr_utility.trace('end');
5968
5969
5970 RETURN l_transfer_to;
5971
5972 END get_timecard_transferred_to;
5973
5974
5975 END hxc_self_service_timecard;