[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMEKEEPER_ERRORS
Source
1 Package Body hxc_timekeeper_errors AS
2 /* $Header: hxctkerror.pkb 120.3 2005/09/23 09:38:06 nissharm noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 --
7 -- public procedure
8 -- show_timecard_errors
9 --
10 -- description
11 -- See DESCRIPTION in header
12
13 procedure show_timecard_errors (
14 p_error_table IN OUT NOCOPY t_error_table
15 , p_timecard_id IN NUMBER
16 , p_timecard_ovn IN NUMBER
17 , p_full_name IN VARCHAR2)
18 IS
19
20
21 CURSOR csr_get_detail is
22
23 SELECT detail.time_building_block_id tbb_id,
24 detail.object_version_number tbb_ovn,
25 detail.scope scope,
26 nvl(detail.start_time,trunc(day.start_time)) start_time,
27 nvl(detail.stop_time,trunc(day.stop_time)) stop_time,
28 detail.measure measure
29 FROM hxc_time_building_blocks detail, hxc_time_building_blocks DAY
30 WHERE DAY.parent_building_block_id = p_timecard_id
31 AND DAY.parent_building_block_ovn = p_timecard_ovn
32 AND detail.date_to = hr_general.end_of_time
33 AND detail.SCOPE = 'DETAIL'
34 AND detail.parent_building_block_id = DAY.time_building_block_id
35 AND detail.parent_building_block_ovn = DAY.object_version_number
36 AND DAY.SCOPE = 'DAY'
37 AND DAY.date_to = hr_general.end_of_time
38 UNION
39 SELECT time_building_block_id detail_id,
40 object_version_number detail_ovn,
41 scope,
42 trunc(start_time),
43 trunc(stop_time),
44 measure
45 FROM hxc_time_building_blocks
46 WHERE time_building_block_id = p_timecard_id
47 and object_version_number = p_timecard_ovn
48 ORDER BY 4;
49
50 /*
51 CURSOR csr_get_timecard IS
52 select bb.time_building_block_id bb_id
53 , bb.object_version_number ovn
54 , bb.scope
55 , bb.start_time
56 , bb.stop_time
57 , bb.resource_id
58 FROM
59 hxc_time_building_blocks bb
60 WHERE EXISTS ( select 'x'
61 from hxc_errors tke
62 where tke.time_building_block_id = bb.time_building_block_id
63 and tke.time_building_block_ovn = bb.object_version_number)
64 START WITH bb.time_building_block_id = p_timecard_id
65 AND bb.object_version_number = p_timecard_ovn
66 AND bb.scope = 'TIMECARD'
67 CONNECT BY PRIOR bb.time_building_block_id = bb.parent_building_block_id
68 AND PRIOR bb.object_version_number = bb.parent_building_block_ovn;
69
70 */
71
72 CURSOR csr_get_error ( p_tbb_id IN NUMBER, p_ovn_id IN NUMBER )
73 IS
74 SELECT
75 tke.ERROR_ID
76 , tke.TRANSACTION_DETAIL_ID
77 , tke.TIME_BUILDING_BLOCK_ID
78 , tke.TIME_BUILDING_BLOCK_OVN
79 , tke.TIME_ATTRIBUTE_ID
80 , tke.TIME_ATTRIBUTE_OVN
81 , tke.MESSAGE_NAME
82 , tke.MESSAGE_LEVEL
83 , tke.MESSAGE_FIELD
84 , tke.MESSAGE_TOKENS
85 , tke.APPLICATION_SHORT_NAME
86 FROM
87 hxc_errors tke
88 WHERE
89 tke.time_building_block_id = p_tbb_id AND
90 tke.time_building_block_ovn = p_ovn_id AND
91 (tke.date_to=hr_general.end_of_time OR tke.date_to is NULL);
92
93
94 l_tbb_id number(15);
95 l_tbb_ovn number(15);
96
97 l_index NUMBER := 1;
98
99 l_scope VARCHAR2(80);
100 l_start_time DATE;
101 l_stop_time DATE;
102 l_resource_id NUMBER;
103
104 l_token_table hxc_deposit_wrapper_utilities.t_simple_table;
105
106 BEGIN
107
108 For timecard_rec in csr_get_detail LOOP
109
110 l_scope :=timecard_rec.scope;
111
112 FOR c_get_error in csr_get_error ( timecard_rec.tbb_id, timecard_rec.tbb_ovn ) LOOP
113
114 -- populate the error_table here
115 p_error_table(l_index).ERROR_ID := c_get_error.error_id;
116 p_error_table(l_index).TRANSACTION_DETAIL_ID := c_get_error.TRANSACTION_DETAIL_ID ;
117 p_error_table(l_index).TIME_BUILDING_BLOCK_ID := c_get_error.TIME_BUILDING_BLOCK_ID ;
118 p_error_table(l_index).TIME_BUILDING_BLOCK_OVN := c_get_error.TIME_BUILDING_BLOCK_OVN ;
119 p_error_table(l_index).TIME_ATTRIBUTE_ID := c_get_error.TIME_ATTRIBUTE_ID ;
120 p_error_table(l_index).TIME_ATTRIBUTE_OVN := c_get_error.TIME_ATTRIBUTE_OVN ;
121 p_error_table(l_index).MESSAGE_NAME := c_get_error.MESSAGE_NAME ;
122 p_error_table(l_index).MESSAGE_FIELD := c_get_error.MESSAGE_FIELD ;
123 p_error_table(l_index).MESSAGE_TOKENS := c_get_error.MESSAGE_TOKENS ;
124 p_error_table(l_index).APPLICATION_SHORT_NAME := c_get_error.APPLICATION_SHORT_NAME ;
125 p_error_table(l_index).SCOPE_LEVEL :=
126 hr_bis.bis_decode_lookup('HXC_BUILDING_BLOCK_SCOPE',timecard_rec.scope);
127 p_error_table(l_index).MESSAGE_LEVEL :=
128 hr_bis.bis_decode_lookup('HXC_TIME_ENTRY_RULE_OUTCOME',c_get_error.MESSAGE_LEVEL);
129
130 fnd_message.set_name(c_get_error.APPLICATION_SHORT_NAME,c_get_error.MESSAGE_NAME);
131
132 -- set the token if applicable
133 -- GPM v115.4
134
135 IF ( c_get_error.MESSAGE_TOKENS IS NOT NULL )
136 THEN
137 --
138 -- parse string into a more accessible form
139 --
140
141 hxc_deposit_wrapper_utilities.string_to_table('&',
142 '&'||c_get_error.MESSAGE_TOKENS,
143 l_token_table);
144
145 -- table should be full of TOKEN, VALUE pairs. The number of TOKEN, VALUE pairs is l_token_table/2
146
147 FOR l_token in 0..(l_token_table.count/2)-1
148 LOOP
149
150 FND_MESSAGE.SET_TOKEN
151 (TOKEN => l_token_table(2*l_token)
152 ,VALUE => l_token_table(2*l_token+1)
153 );
154
155 END LOOP;
156
157 END IF; -- c_get_error.MESSAGE_TOKEN IS NOT NULL
158
159 p_error_table(l_index).MESSAGE_TEXT := fnd_message.get() ;
160
161 fnd_message.clear;
162
163 p_error_table(l_index).PERSON_FULL_NAME := p_full_name ;
164 p_error_table(l_index).START_PERIOD := timecard_rec.start_time ;
165 p_error_table(l_index).END_PERIOD := timecard_rec.stop_time ;
166 p_error_table(l_index).MEASURE := timecard_rec.measure ;
167
168 l_index := l_index + 1;
169
170 END LOOP;
171
172 END LOOP;
173
174 IF (l_index = 1 and l_scope = 'ERROR') THEN
175
176 fnd_message.set_name('HXC','HXC_TIMEKEEPER_UNEXCEP_ERROR');
177
178 p_error_table(l_index).MESSAGE_TEXT := fnd_message.get();
179
180 ELSIF (l_index = 1) THEN
181
182 fnd_message.set_name('HXC','HXC_TIMEKEEPER_NO_ERROR');
183
184 p_error_table(l_index).MESSAGE_TEXT := fnd_message.get();
185
186 END IF;
187
188
189 -- Joel - we also need to process the l_error_tab(x).message_name
190 -- to the message_text param
191 -- IF message_name begins with HXC, HR, PER, PAY then call fnd message
192 -- to get text else just put message_name into text.
193
194 END show_timecard_errors;
195
196
197 --
198 -- public procedure
199 -- maintain_errors
200 --
201 -- description
202 -- See DESCRIPTION in header
203 PROCEDURE maintain_errors
204 (p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
205 ,p_timecard_id IN OUT NOCOPY NUMBER
206 ,p_timecard_ovn IN OUT NOCOPY NUMBER) IS
207
208 cursor c_max_ovn
209 (p_tbb_id in number) is
210 select max(object_version_number)
211 from hxc_time_building_blocks
212 where time_building_block_id = p_tbb_id;
213
214 cursor c_transaction_id
215 (p_tbb_id in number,
216 p_tbb_ovn in number) is
217 select transaction_id
218 from hxc_transaction_details
219 where time_building_block_id = p_tbb_id
220 and object_version_number = p_tbb_ovn;
221
222 cursor c_error_id
223 (p_tbb_id in number,
224 p_tbb_ovn in number) is
225 select *
226 from hxc_errors
227 where time_building_block_id = p_tbb_id
228 and time_building_block_ovn = p_tbb_ovn
229 and (date_to=hr_general.end_of_time OR date_to is NULL);
230
231 CURSOR c_detail_info (timecard_id IN NUMBER, timecard_ovn IN NUMBER)
232 IS
233 SELECT detail.time_building_block_id detail_id,
234 detail.object_version_number detail_ovn
235 FROM hxc_time_building_blocks detail, hxc_time_building_blocks DAY
236 WHERE DAY.parent_building_block_id = timecard_id
237 AND DAY.parent_building_block_ovn = timecard_ovn
238 AND detail.date_to = hr_general.end_of_time
239 AND detail.SCOPE = 'DETAIL'
240 AND detail.parent_building_block_id = DAY.time_building_block_id
241 AND detail.parent_building_block_ovn = DAY.object_version_number
242 AND DAY.SCOPE = 'DAY'
243 AND DAY.date_to = hr_general.end_of_time;
244
245 l_proc varchar2(72);
246
247 l_message_index NUMBER;
248 l_tbb_id NUMBER := NULL;
249 l_tbb_ovn NUMBER := NULL;
250 l_tx_id NUMBER := NULL;
251 l_error_id NUMBER := NULL;
252 l_ovn NUMBER := NULL;
253 l_errid NUMBER :=NULL;
254
255 PROCEDURE purge_duplicate_messages ( p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE ) IS
256
257 l_ind PLS_INTEGER;
258
259 BEGIN
260
261 l_ind := p_messages.FIRST;
262
263 WHILE l_ind IS NOT NULL
264 LOOP
265
266 BEGIN
267
268 delete from hxc_errors
269 where time_building_Block_id = p_messages(l_ind).time_building_block_id
270 and message_name = p_messages(l_ind).message_name
271 and NVL(message_tokens,'ZzZ') = NVL(p_messages(l_ind).message_tokens,'ZzZ');
272
273 EXCEPTION WHEN OTHERS THEN null;
274
275 END;
276
277 l_ind := p_messages.NEXT(l_ind);
278
279 END LOOP;
280
281 END purge_duplicate_messages;
282
283
284
285 BEGIN
286
287 g_debug := hr_utility.debug_enabled;
288
289 if g_debug then
290 l_proc := g_package||'.maintain_errors';
291 hr_utility.set_location('Entering '||l_proc, 10);
292 end if;
293
294
295 --first set the date_to to todays date if message already exists for saved timecard in error status
296 For error_rec in c_error_id(p_timecard_id,p_timecard_ovn) LOOP
297
298 hxc_err_upd.upd
299 (p_error_id => error_rec.error_id
300 ,p_object_version_number => error_rec.object_version_number
301 ,p_date_from => error_rec.date_from
302 ,p_date_to => sysdate-1
303 );
304
305 END LOOP;
306
307 For detail_rec in c_detail_info(p_timecard_id,p_timecard_ovn) LOOP
308
309 For error_rec in c_error_id(detail_rec.detail_id,detail_rec.detail_ovn) LOOP
310
311 hxc_err_upd.upd
312 (p_error_id => error_rec.error_id
313 ,p_object_version_number => error_rec.object_version_number
314 ,p_date_from => error_rec.date_from
315 ,p_date_to => sysdate-1
316 );
317 END LOOP;
318
319 END LOOP;
320
321 purge_duplicate_messages ( p_messages );
322
323 -- parse the transaction table to produce a mapping of time building blocks
324 -- to transaction details id- this will save traversing the table for every message
325
326 l_message_index := p_messages.FIRST;
327
328 LOOP
329 EXIT WHEN (NOT p_messages.EXISTS (l_message_index));
330
331 if g_debug then
332 hr_utility.set_location('Processing '||l_proc, 40);
333 end if;
334
335 -- assign to variables (makes it easier to read the following code)
336 l_tbb_id := p_messages(l_message_index).time_building_block_id;
337 l_tbb_ovn := p_messages(l_message_index).time_building_block_ovn;
338
339 if g_debug then
340 hr_utility.set_location('Processing '||l_proc, 70);
341 end if;
342
343 --added for bug no 2927608
344 If p_messages(l_message_index).message_level IN ('ERROR','WARNING','BUSINESS_MESSAGE') then
345
346 --find the transaction_id from the tbb
347 OPEN c_transaction_id(l_tbb_id,l_tbb_ovn);
348 FETCH c_transaction_id INTO l_tx_id;
349 CLOSE c_transaction_id;
350
351 if l_tbb_id is null or l_tbb_id < 0 then
352 l_tbb_id := p_timecard_id;
353 l_tbb_ovn := p_timecard_ovn;
354 end if;
355
356 -- IF l_tbb_ovn is null then --3420765
357 OPEN c_max_ovn(l_tbb_id);
358 FETCH c_max_ovn INTO l_tbb_ovn;
359 CLOSE c_max_ovn;
360 -- END IF;
361
362 IF l_tbb_ovn is null then
363 l_tbb_ovn := 1;
364 END IF;
365
366
367 IF (l_tx_id is null) THEN
368 l_tx_id := -1;
369 END IF;
370
371 hxc_err_ins.ins
372 (p_transaction_detail_id => l_tx_id
373 ,p_time_building_block_id => l_tbb_id
374 ,p_time_building_block_ovn => l_tbb_ovn
375 ,p_time_attribute_id =>p_messages(l_message_index).time_attribute_id
376 ,p_time_attribute_ovn =>p_messages(l_message_index).time_attribute_ovn
377 ,p_message_name =>p_messages(l_message_index).message_name
378 ,p_message_level =>p_messages(l_message_index).message_level
379 ,p_message_field =>p_messages(l_message_index).message_field
380 ,p_message_tokens =>p_messages(l_message_index).message_tokens
381 ,p_application_short_name =>p_messages(l_message_index).application_short_name
382 ,p_error_id => l_error_id
383 ,p_object_version_number => l_ovn
384 ,p_date_from => sysdate
385 ,p_date_to => hr_general.end_of_time
386 );
387 End if;
388 l_message_index := p_messages.NEXT(l_message_index);
389
390 l_tbb_id := NULL;
391 l_tbb_ovn := NULL;
392 l_tx_id := NULL;
393 l_error_id := NULL;
394
395 END LOOP;
396
397 if g_debug then
398 hr_utility.set_location('Leaving '||l_proc, 80);
399 end if;
400
401 END maintain_errors;
402
403 --
404 -- public procedure
405 -- maintain_errors
406 --
407 -- description
408 -- See DESCRIPTION in header
409 PROCEDURE rollback_tc_or_set_err_status
410 (p_message_table in out nocopy HXC_MESSAGE_TABLE_TYPE
411 ,p_blocks in out nocopy hxc_block_table_type
412 ,p_attributes in out nocopy hxc_attribute_table_type
413 ,p_rollback in out nocopy BOOLEAN
414 ,p_status_error out NOCOPY BOOLEAN) IS
415
416 l_index BINARY_INTEGER;
417
418 l_error_message_found BOOLEAN := FALSE;
419
420 BEGIN
421
422 p_rollback := FALSE;
423
424 l_index := p_message_table.first;
425 -- at this point we are checking if we need to
426 -- rollback.
427
428 WHILE l_index IS NOT NULL
429 LOOP
430
431 IF p_message_table(l_index).message_name = 'HXC_VLD_TC_STATUS_CHANGED'
432 OR
433 p_message_table(l_index).message_name = 'HXC_TIME_BLD_BLK_NOT_LATEST'
434 OR
435 p_message_table(l_index).message_name = 'PA_NO_DEL_EX_ITEM'
436 OR
437 p_message_table(l_index).message_name = 'PA_TR_ADJ_NO_NET_ZERO'
438 OR
439 p_message_table(l_index).message_name = 'HXT_TC_CANNOT_BE_DELETED'
440 OR
441 p_message_table(l_index).message_name = 'HXT_TC_CANNOT_BE_CHANGED_TODAY'
442 OR
443 p_message_table(l_index).message_name = 'HXC_TC_OFFLINE_PERIOD_CONFLICT'
444 THEN
445 -- we need to rollback;
446 p_rollback := TRUE;
447 END IF;
448
449 IF p_message_table(l_index).message_level =
450 hxc_timecard_deposit_common.c_error
451 THEN
452 l_error_message_found := TRUE;
453 END IF;
454
455 l_index := p_message_table.NEXT(l_index);
456 END LOOP;
457
458 IF l_error_message_found THEN
459 -- if we passed the above then we need to change the status
460 -- to be error
461 l_index := p_blocks.FIRST;
462
463 WHILE l_index IS NOT NULL
464 LOOP
465 p_blocks(l_index).approval_status := 'ERROR';
466 p_blocks(l_index).process := 'Y';
467 l_index := p_blocks.NEXT(l_index);
468 END LOOP;
469
470 hxc_block_attribute_update.set_process_flags
471 (p_blocks => p_blocks
472 ,p_attributes => p_attributes
473 );
474
475 -- set the status error
476 p_status_error := TRUE;
477 ELSE
478 -- set the status error
479 p_status_error := FALSE;
480
481
482 END IF;
483
484 END rollback_tc_or_set_err_status;
485
486 end hxc_timekeeper_errors;