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