[Home] [Help]
PACKAGE BODY: APPS.HXC_RDB_ATT_SNAPSHOT
Source
1 PACKAGE BODY HXC_RDB_ATT_SNAPSHOT as
2 /* $Header: hxcrdbsnpsht.pkb 120.1.12010000.8 2010/04/06 13:00:51 sabvenug noship $ */
3
4
5 ----------------------------------------------------------------
6 TYPE LOCKED_TCS_TAB_TYPE is TABLE OF VARCHAR2(1)
7 INDEX BY PLS_INTEGER;
8
9
10 TYPE VTAB IS TABLE OF VARCHAR2(2500);
11 TYPE NTAB IS TABLE OF NUMBER(15,5);
12
13 g_lock_transaction_id NUMBER;
14
15
16 NO_UPG_DONE EXCEPTION;
17 NO_LATEST_DETAILS_UPG EXCEPTION;
18
19 TABLE_EXCEPTION EXCEPTION;
20 PRAGMA EXCEPTION_INIT(TABLE_EXCEPTION,-24381);
21 -----------------------------------------------------------------
22
23
24
25 PROCEDURE generate_attribute_info(errbuff OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY NUMBER)
27
28 IS
29
30 t_locked_tcs LOCKED_TCS_TAB_TYPE;
31
32 l_process_lock_type varchar2(30):= HXC_LOCK_UTIL.c_plsql_deposit_action;
33 l_row_id rowid;
34 l_tc_lock_boolean boolean;
35 l_messages hxc_self_service_time_deposit.message_table;
36
37 l_update_warning VARCHAR2(1);
38
39 l_lock_warning VARCHAR2(1);
40
41
42 CURSOR get_pay_attribute_info
43 IS
44 SELECT ROWIDTOCHAR(hld.rowid),
45 hta.attribute_category,
46 REPLACE(hta.attribute_category,'ELEMENT - '),--hta.attribute1,
47 hta.attribute2,
48 hta.attribute3,
49 hta.attribute4,
50 hta.attribute5,
51 hta.attribute6,
52 hta.attribute7,
53 DECODE (TBB.DATE_TO, HR_GENERAL.end_of_time, nvl (tbb.measure,
54 TO_NUMBER (tbb.stop_time - tbb.start_time) * 24 ) ,
55 0 ) measure,
56 hld.timecard_id
57 FROM hxc_pay_latest_details hld,
58 hxc_time_attribute_usages htau,
59 hxc_time_attributes hta,
60 hxc_bld_blk_info_types bld,
61 hxc_time_building_blocks tbb
62 WHERE htau.time_building_block_id = hld.time_building_block_id
63 AND htau.time_building_block_ovn = hld.object_version_number
64 AND tbb.time_building_block_id = hld.time_building_block_id
65 AND tbb.object_version_number = hld.object_version_number
66 AND ( hld.attribute_category is null
67 OR hld.measure is null)
68 AND hta.time_attribute_id = htau.time_attribute_id
69 AND hta.bld_blk_info_type_id = bld.bld_blk_info_type_id
70 AND bld.bld_blk_info_type = 'Dummy Element Context' -- 'PROJECTS'
71 /*AND NOT EXISTS ( SELECT 1
72 FROM hxc_locks hlk
73 WHERE (hlk.time_building_block_id = hld.timecard_id
74 OR
75 (hlk.time_building_block_id = hld.time_building_block_id
76 AND
77 hlk.time_building_block_ovn = hld.object_version_number
78 )
79 )
80 AND lock_date <= sysdate - ( 20 / (24*60) )
81 )*/
82 ;
83
84
85
86 CURSOR get_pa_attribute_info
87 IS
88 SELECT ROWIDTOCHAR(hld.rowid),
89 hta.attribute_category,
90 hta.attribute1,
91 hta.attribute2,
92 hta.attribute3,
93 hta.attribute4,
94 hta.attribute5,
95 hta.attribute6,
96 hta.attribute7,
97 DECODE (TBB.DATE_TO, HR_GENERAL.end_of_time, nvl (tbb.measure,
98 TO_NUMBER (tbb.stop_time - tbb.start_time) * 24 ) ,
99 0 ) measure,
100 hld.timecard_id
101 FROM hxc_pa_latest_details hld,
102 hxc_time_attribute_usages htau,
103 hxc_time_attributes hta,
104 hxc_bld_blk_info_types bld,
105 hxc_time_building_blocks tbb
106 WHERE htau.time_building_block_id = hld.time_building_block_id
107 AND htau.time_building_block_ovn = hld.object_version_number
108 AND tbb.time_building_block_id = hld.time_building_block_id
109 AND tbb.object_version_number = hld.object_version_number
110 AND ( hld.attribute_category is null
111 OR hld.measure is null)
112 AND hta.time_attribute_id = htau.time_attribute_id
113 AND hta.bld_blk_info_type_id = bld.bld_blk_info_type_id
114 AND bld.bld_blk_info_type = 'PROJECTS' -- 'PROJECTS'
115 /*AND NOT EXISTS ( SELECT 1
116 FROM hxc_locks hlk
117 WHERE (hlk.time_building_block_id = hld.timecard_id
118 OR
119 (hlk.time_building_block_id = hld.time_building_block_id
120 AND
121 hlk.time_building_block_ovn = hld.object_version_number
122 )
123 )
124 AND lock_date <= sysdate - ( 20 / (24*60) )
125 )*/
126 ;
127
128
129 ROW_ID_TAB VTAB;
130 ATTRIBUTE_CATEGORY_TAB VTAB;
131 ATTRIBUTE1_TAB VTAB;
132 ATTRIBUTE2_TAB VTAB;
133 ATTRIBUTE3_TAB VTAB;
134 ATTRIBUTE4_TAB VTAB;
135 ATTRIBUTE5_TAB VTAB;
136 ATTRIBUTE6_TAB VTAB;
137 ATTRIBUTE7_TAB VTAB;
138 MEASURE_TAB NTAB;
139 TIMECARD_ID_TAB NTAB;
140 TIMECARD_ID_STATUS LOCKED_TCS_TAB_TYPE;
141
142 LOCK_TCS_TAB NTAB;
143
144 l_latest_details_upg VARCHAR2(10);
145 l_pay_upg VARCHAR2(10);
146 l_pa_upg VARCHAR2(10);
147
148 -- Pvt procedure to lock timecards, if not already locked -- Bug 8888905
149 FUNCTION get_locked_timecards(p_lock_tcs_tab IN NTAB) RETURN LOCKED_TCS_TAB_TYPE
150
151 IS
152
153 l_resource_id NUMBER;
154 l_start_time DATE;
155 l_stop_time DATE;
156 l_tc_id NUMBER;
157 l_tc_ovn NUMBER;
158
159 TIMECARD_STATUS LOCKED_TCS_TAB_TYPE;
160
161
162 CURSOR get_tc_info (p_timecard_id IN NUMBER)
163 IS
164 SELECT
165 tc.timecard_id,
166 tc.timecard_ovn,
167 tc.resource_id,
168 tc.start_time,
169 tc.stop_time
170 FROM
171 hxc_timecard_summary tc
172 WHERE
173 tc.timecard_id = p_timecard_id;
174
175
176 BEGIN
177
178 --TIMECARD_STATUS := p_lock_tcs_tab;
179
180 FOR i in p_lock_tcs_tab.first .. p_lock_tcs_tab.last LOOP
181
182 if NOT (t_locked_tcs.EXISTS(p_lock_tcs_tab(i))) then
183
184 OPEN get_tc_info (p_lock_tcs_tab(i));
185 FETCH get_tc_info into l_tc_id, l_tc_ovn, l_resource_id, l_start_time, l_stop_time;
186
187 IF get_tc_info%FOUND then
188
189 if g_debug then
190 fnd_file.put_line (fnd_file.LOG,'Acquiring Locking for TC ID: '||p_lock_tcs_tab(i));
191 end if;
192
193 hxc_lock_api.request_lock (p_process_locker_type => l_process_lock_type,
194 p_resource_id => l_resource_id,
195 p_start_time => l_start_time,
196 p_stop_time => l_stop_time ,
197 p_time_building_block_id => l_tc_id,
198 p_time_building_block_ovn => l_tc_ovn,
199 p_transaction_lock_id => g_lock_transaction_id,
200 p_messages => l_messages,
201 p_row_lock_id => l_row_id,
202 p_locked_success => l_tc_lock_boolean
203 );
204
205 /* Need to handle those cases which fail to acquire lock*/
206
207 if l_tc_lock_boolean then
208 t_locked_tcs(p_lock_tcs_tab(i)):= 'Y';
209
210 if g_debug then
211 fnd_file.put_line (fnd_file.LOG,'Obtained lock for TC ID: '||p_lock_tcs_tab(i));
212 end if;
213 else
214 if g_debug then
215 fnd_file.put_line (fnd_file.LOG,'SKIPPING: The timecard seems to be locked by another process TC ID: '||p_lock_tcs_tab(i));
216 end if;
217 t_locked_tcs(p_lock_tcs_tab(i)):= 'N';
218 end if; -- l_tc_lock_boolean
219
220 else
221
222 t_locked_tcs(p_lock_tcs_tab(i)):= 'Y';
223
224 if g_debug then
225 fnd_file.put_line (fnd_file.LOG,'The Timecard seems to be deleted. TC ID: '||p_lock_tcs_tab(i));
226 end if;
227
228 end if; -- get_tc_info%FOUND
229
230 CLOSE get_tc_info;
231
232
233 end if; --t_locked_tcs.EXISTS
234
235 TIMECARD_STATUS(i) := t_locked_tcs(p_lock_tcs_tab(i));
236
237 END LOOP; --p_lock_tcs_tab
238
239 RETURN TIMECARD_STATUS;
240
241 END get_locked_timecards;
242
243
244
245 BEGIN -- generate_attribute_info
246
247 -- check for upgrade completion -- Bug 8888905
248
249 begin
250 SELECT 'Y'
251 INTO l_latest_details_upg
252 FROM hxc_upgrade_definitions
253 WHERE upg_type = 'LATEST_DETAILS'
254 AND status = 'COMPLETE';
255 exception
256 WHEN OTHERS THEN
257
258 fnd_file.put_line(fnd_file.LOG,' The Latest Details Upgrade has not been completed ');
259 raise NO_LATEST_DETAILS_UPG;
260
261 end;
262
263 --get the values for pay/pa upgrade
264
265 begin
266 SELECT 'Y'
267 INTO l_pay_upg
268 FROM hxc_upgrade_definitions
269 WHERE upg_type = 'RETRIEVAL_PAY'
270 AND status = 'COMPLETE';
271 exception
272 WHEN OTHERS THEN
273
274 l_pay_upg:='N';
275
276 end;
277
278 begin
279 SELECT 'Y'
280 INTO l_pa_upg
281 FROM hxc_upgrade_definitions
282 WHERE upg_type = 'RETRIEVAL_PA'
283 AND status = 'COMPLETE';
284 exception
285 WHEN OTHERS THEN
286
287 l_pa_upg:='N';
288
289 end;
290
291 if l_pay_upg = 'N' and l_pa_upg = 'N' then
292
293 fnd_file.put_line(fnd_file.LOG, 'Both Payroll and Projects upgrades have not been completed');
294 raise NO_UPG_DONE;
295
296 end if;
297
298
299
300 -- initialize the g_lock_transaction_id
301
302 begin
303
304 SELECT hxc_transactions_s.NEXTVAL
305 INTO g_lock_transaction_id
306 FROM SYS.DUAL;
307
308 exception
309 WHEN NO_DATA_FOUND THEN
310
311 fnd_file.put_line(fnd_file.LOG,' No transaction id obtained from sequence hxc_transactions_s ');
312 raise ;
313
314 end;
315
316 if g_debug then
317 fnd_file.put_line (fnd_file.LOG,'g_lock_transaction_id = '||g_lock_transaction_id);
318 end if;
319
320
321 if l_pay_upg = 'Y' then
322
323 if g_debug then
324 fnd_file.put_line (fnd_file.LOG,'******************************');
325 fnd_file.put_line (fnd_file.LOG,'STARTING WITH HXC_PAY_LATEST_DETAILS');
326 fnd_file.put_line (fnd_file.LOG,'******************************');
327 end if;
328
329 OPEN get_pay_attribute_info;
330
331 LOOP
332 FETCH get_pay_attribute_info BULK COLLECT into ROW_ID_TAB, ATTRIBUTE_CATEGORY_TAB, ATTRIBUTE1_TAB, ATTRIBUTE2_TAB,
333 ATTRIBUTE3_TAB, ATTRIBUTE4_TAB, ATTRIBUTE5_TAB, ATTRIBUTE6_TAB,
334 ATTRIBUTE7_TAB, MEASURE_TAB,TIMECARD_ID_TAB
335 LIMIT 500;
336
337 -- We need to lock the timecards
338
339 EXIT WHEN ROW_ID_TAB.COUNT = 0;
340
341 if g_debug then
342 fnd_file.put_line (fnd_file.LOG,'******************************');
343 fnd_file.put_line (fnd_file.LOG,'Getting a set of bulk data');
344 fnd_file.put_line (fnd_file.LOG,'******************************');
345 end if;
346
347 TIMECARD_ID_STATUS:= get_locked_timecards(TIMECARD_ID_TAB);
348
349 if g_debug then
350 fnd_file.put_line (fnd_file.LOG,'Updating hxc_pay_latest_details');
351 fnd_file.put_line (fnd_file.LOG,'ROW_ID_TAB.COUNT ='||ROW_ID_TAB.COUNT);
352 end if;
353
354
355 begin
356 FORALL i in ROW_ID_TAB.first .. ROW_ID_TAB.last SAVE EXCEPTIONS
357 UPDATE hxc_pay_latest_details
358 SET ATTRIBUTE_CATEGORY = ATTRIBUTE_CATEGORY_TAB(i),
359 ATTRIBUTE1 = ATTRIBUTE1_TAB(i),
360 ATTRIBUTE2 = ATTRIBUTE2_TAB(i),
361 ATTRIBUTE3 = ATTRIBUTE3_TAB(i),
362 ATTRIBUTE4 = ATTRIBUTE4_TAB(i),
363 ATTRIBUTE5 = ATTRIBUTE5_TAB(i),
364 ATTRIBUTE6 = ATTRIBUTE6_TAB(i),
365 ATTRIBUTE7 = ATTRIBUTE7_TAB(i),
366 MEASURE = MEASURE_TAB(i)
367 WHERE rowid = CHARTOROWID (ROW_ID_TAB(i))
368 AND TIMECARD_ID_STATUS(i) = 'Y';
369 exception
370 when table_exception then --ORA 24381 do nothing.. ignore..
371 --null;
372 l_update_warning:= 'Y';
373
374 end;
375 commit;
376
377 END LOOP;
378
379 CLOSE get_pay_attribute_info;
380 end if; -- l_pay_upg
381
382
383 if l_pa_upg = 'Y' then
384
385 if g_debug then
386 fnd_file.put_line (fnd_file.LOG,'******************************');
387 fnd_file.put_line (fnd_file.LOG,'STARTING WITH HXC_PA_LATEST_DETAILS');
388 fnd_file.put_line (fnd_file.LOG,'******************************');
389 end if;
390
391 OPEN get_pa_attribute_info;
392
393 LOOP
394 FETCH get_pa_attribute_info BULK COLLECT into ROW_ID_TAB, ATTRIBUTE_CATEGORY_TAB, ATTRIBUTE1_TAB, ATTRIBUTE2_TAB,
395 ATTRIBUTE3_TAB, ATTRIBUTE4_TAB, ATTRIBUTE5_TAB, ATTRIBUTE6_TAB,
396 ATTRIBUTE7_TAB, MEASURE_TAB,TIMECARD_ID_TAB
397 LIMIT 500;
398 EXIT WHEN ROW_ID_TAB.COUNT = 0;
399
400 if g_debug then
401 fnd_file.put_line (fnd_file.LOG,'******************************');
402 fnd_file.put_line (fnd_file.LOG,'Getting a set of bulk data');
403 fnd_file.put_line (fnd_file.LOG,'******************************');
404 end if;
405
406 TIMECARD_ID_STATUS := get_locked_timecards(TIMECARD_ID_TAB);
407
408 if g_debug then
409 fnd_file.put_line (fnd_file.LOG,'Updating hxc_pa_latest_details');
410 fnd_file.put_line (fnd_file.LOG,'ROW_ID_TAB.COUNT ='||ROW_ID_TAB.COUNT);
411 end if;
412
413
414 begin
415 FORALL i in ROW_ID_TAB.first .. ROW_ID_TAB.last SAVE EXCEPTIONS
416 UPDATE hxc_pa_latest_details
417 SET ATTRIBUTE_CATEGORY = ATTRIBUTE_CATEGORY_TAB(i),
418 ATTRIBUTE1 = ATTRIBUTE1_TAB(i),
419 ATTRIBUTE2 = ATTRIBUTE2_TAB(i),
420 ATTRIBUTE3 = ATTRIBUTE3_TAB(i),
421 ATTRIBUTE4 = ATTRIBUTE4_TAB(i),
422 ATTRIBUTE5 = ATTRIBUTE5_TAB(i),
423 ATTRIBUTE6 = ATTRIBUTE6_TAB(i),
424 ATTRIBUTE7 = ATTRIBUTE7_TAB(i),
425 MEASURE = MEASURE_TAB(i)
426 WHERE rowid = CHARTOROWID (ROW_ID_TAB(i))
427 AND TIMECARD_ID_STATUS(i) = 'Y';
428 exception
429 when table_exception then
430 --null;
431 l_update_warning:= 'Y';
432 end;
433 commit;
434
435 END LOOP;
436
437 CLOSE get_pa_attribute_info;
438 end if; -- l_pa_upg
439
440 -- Release all locks
441
442 hxc_lock_api.release_lock
443 (p_row_lock_id => NULL,
444 p_process_locker_type => l_process_lock_type,
445 p_transaction_lock_id => g_lock_transaction_id,
446 p_released_success => l_tc_lock_boolean
447 );
448
449
450 fnd_file.put_line (fnd_file.LOG,'******************************');
451 fnd_file.put_line (fnd_file.LOG,'List of skipped TC IDs');
452 fnd_file.put_line (fnd_file.LOG,'******************************');
453
454 if t_locked_tcs.COUNT > 0 then
455 for i in t_locked_tcs.FIRST .. t_locked_tcs.LAST LOOP
456
457 if t_locked_tcs.EXISTS(i) then
458
459 if t_locked_tcs(i) <>'Y' then
460
461 fnd_file.put_line (fnd_file.LOG,i);
462 l_lock_warning :='Y';
463 end if; -- t_locked_tcs(i) <>'Y'
464
465 end if;-- t_locked_tcs.EXISTS
466
467 END LOOP; -- t_locked_tcs
468 end if; -- t_locked_tcs.COUNT
469
470 fnd_file.put_line (fnd_file.LOG,'******************************');
471
472 if (l_lock_warning = 'Y' ) then
473
474 retcode:= 1;
475 fnd_file.put_line (fnd_file.LOG,' Warning: Some Timecards were locked. ');
476 fnd_file.put_line (fnd_file.LOG,' Please clear the locks, and retry the process');
477
478 end if;
479
480
481 if (l_update_warning = 'Y') then
482
483 retcode:= 1;
484 fnd_file.put_line (fnd_file.LOG,' Warning: Some Detail Records were locked. ');
485 fnd_file.put_line (fnd_file.LOG,' Please clear the locks, and retry the process');
486
487 end if;
488
489 EXCEPTION
490 WHEN NO_UPG_DONE then
491
492 fnd_file.put_line(fnd_file.LOG, 'Both Payroll and Projects upgrades have not been completed');
493 retcode:=2;
494 RAISE;
495
496 WHEN NO_LATEST_DETAILS_UPG then
497
498 fnd_file.put_line(fnd_file.LOG, 'The Latest Details Upgrade has not been completed');
499 retcode:=2;
500 RAISE;
501
502 WHEN OTHERS THEN
503
504 hxc_lock_api.release_lock
505 (p_row_lock_id => NULL,
506 p_process_locker_type => l_process_lock_type,
507 p_transaction_lock_id => g_lock_transaction_id,
508 p_released_success => l_tc_lock_boolean
509 );
510
511
512
513 fnd_file.put_line(fnd_file.LOG,' The Program has encountered an unexpected error');
514 fnd_file.put_line(fnd_file.LOG,' Tracing the error as follows - ');
515 fnd_file.put_line(fnd_file.LOG,'-------------------------------');
516 fnd_file.put_line(fnd_file.LOG,dbms_utility.format_error_backtrace);
517 fnd_file.put_line(fnd_file.LOG,SQLERRM);
518 fnd_file.put_line(fnd_file.LOG,'-------------------------------');
519 retcode:=2;
520
521 RAISE;
522
523 END generate_attribute_info;
524
525
526
527
528 end;