[Home] [Help]
PACKAGE BODY: APPS.PAY_PRG_PROCESS_EVENTS
Source
1 PACKAGE BODY PAY_PRG_PROCESS_EVENTS AS
2 /* $Header: pyprgevt.pkb 120.4 2011/04/11 09:07:14 kskoduri noship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'PAY_PRG_PROCESS_EVENTS';
4 gv_process_name CONSTANT varchar2(10) := 'PAYPRGEVT';
5
6 -------------------------------------------------------------------------------------
7 -- Name : Purge_process_events _PAY_MGR
8 -- Function : This is the Manager Process called by Conc Program
9 -- Purge Process Events
10 -------------------------------------------------------------------------------------
11
12 PROCEDURE Purge_process_events_PAY_MGR (
13 X_errbuf out NOCOPY varchar2,
14 X_retcode out NOCOPY varchar2,
15 p_purge_date in varchar2,
16 X_batch_size in number default 1000,
17 X_Num_Workers in number default 5)
18 IS
19
20 l_module CONSTANT VARCHAR2(90) := 'PAY_PRG_PROCESS_EVENTS.Purge_process_events_PAY_MGR';
21
22 l_stmt_num number;
23 l_api_name CONSTANT VARCHAR2(30) := 'Purge_process_events_PAY_MGR';
24 l_api_version CONSTANT NUMBER := 1.0;
25
26 l_conc_status BOOLEAN;
27
28 submit_conc_failed EXCEPTION;
29
30 l_phase varchar2(80);
31 l_status_code varchar2(80);
32 l_dev_phase varchar2(15);
33 l_message varchar2(255);
34
35 L_SUB_REQTAB fnd_concurrent.requests_tab_type;
36 req_data varchar2(10);
37 submit_req boolean;
38
39 l_prg_appid number;
40 l_program_name varchar2(15);
41 l_reqid_count number;
42
43 l_ret_code varchar2(10);
44
45 l_product varchar2(30);
46 l_status varchar2(30);
47 l_industry varchar2(30);
48 l_retstatus boolean;
49 l_table_owner varchar2(30);
50 lv_param_found boolean default FALSE;
51 lv_Num_Workers number default 1;
52 lv_debug_enabled boolean default FALSE;
53 lv_no_periods boolean default TRUE;
54
55 lv_logging varchar2(30);
56 lv_debug_flag varchar2(2);
57 lv_batch_size number;
58 l_pap_group_id_frm_profile number := 0;
59
60 BEGIN
61
62 /* Get pay action parameter group id */
63 l_pap_group_id_frm_profile := fnd_profile.value('ACTION_PARAMETER_GROUPS');
64 if (pay_core_utils.pay_action_parameter_group_id is null) then
65 pay_core_utils.pay_action_parameter_group_id := l_pap_group_id_frm_profile;
66 end if;
67
68 fnd_file.put_line(fnd_file.log,'PAP Group ID:'||l_pap_group_id_frm_profile);
69
70 /* Check if logging for pl/sql code is set. If yes, enable log statments */
71 lv_debug_flag := 'N';
72 pay_core_utils.get_action_parameter('LOGGING',lv_logging,lv_param_found);
73
74 lv_debug_enabled := FALSE;
75 if (lv_param_found) then
76 if (instr(upper(lv_logging), 'Z') <> 0 or instr(upper(lv_logging), 'T') <> 0)
77 then
78 lv_debug_enabled := TRUE;
79 lv_debug_flag := 'Y';
80 end if;
81 end if;
82 lv_param_found := FALSE;
83
84 /* Set batch size using action parameter */
85 pay_core_utils.get_action_parameter('PPE_BATCH_SIZE',lv_batch_size,
86 lv_param_found);
87 if (not lv_param_found) then
88 lv_batch_size := X_batch_size;
89 end if;
90 lv_param_found := FALSE;
91
92 If lv_debug_enabled then
93 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
94 fnd_file.put_line(fnd_file.log,'In P_PURGE_DATE :'|| p_purge_date);
95 fnd_file.put_line(fnd_file.log,'PPE_BATCH_SIZE :'|| lv_batch_size);
96 End if;
97
98 l_stmt_num :=0;
99 l_stmt_num :=5;
100 l_prg_appid := 801;
101 l_program_name := 'PAYPRGEVT';
102 l_reqid_count := 0;
103 lv_no_periods := TRUE;
104
105 req_data := fnd_conc_global.request_data;
106
107 if (req_data is null) then
108 submit_req := TRUE;
109 else
110 submit_req := FALSE;
111 end if;
112
113 if (submit_req = TRUE) then
114
115 if (nvl(fnd_global.conc_request_id, -1) < 0) then
116 raise_application_error(-20001, 'SUBMIT_SUBREQUESTS() must be called from a concurrent request');
117 end if;
118
119 -- Standard call to check for call compatibility
120 IF NOT FND_API.Compatible_API_Call (
121 l_api_version,
122 1.0,
123 l_api_name,
124 G_PKG_NAME ) THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127
128 x_retcode := FND_API.G_RET_STS_SUCCESS;
129
130 l_product :='PAY';
131
132 l_stmt_num :=10;
133 l_retstatus := fnd_installation.get_app_info(
134 l_product, l_status, l_industry, l_table_owner);
135
136 if ((l_retstatus = TRUE) AND (l_table_owner is not null)) then
137
138 Begin
139
140 lv_Num_Workers := X_Num_Workers;
141
142 /* Get the Pay Action Parameter Value of THREADS */
143 If lv_debug_enabled then
144 fnd_file.put_line(fnd_file.log,'Before pay_core_utils.get_action_parameter ');
145 End if;
146
147 pay_core_utils.get_action_parameter('THREADS',lv_Num_Workers,lv_param_found);
148
149 if (not lv_param_found) then
150 lv_Num_Workers := x_Num_Workers;
151 end if;
152
153 If lv_debug_enabled then
154 fnd_file.put_line(fnd_file.log,'No of Workers :'||to_char(lv_Num_Workers));
155 End if;
156
157 If lv_debug_enabled then
158 fnd_file.put_line(fnd_file.log,'After pay_core_utils.get_action_parameter ');
159 End if;
160
161 begin
162
163 If lv_debug_enabled then
164 fnd_file.put_line(fnd_file.log,'Before AD_CONC_UTILS_PKG.submit_subrequests ');
165 End if;
166
167 /* Call worker program based on number of threads */
168 AD_CONC_UTILS_PKG.submit_subrequests(
169 X_errbuf=>X_errbuf,
170 X_retcode=>X_retcode,
171 X_WorkerConc_app_shortname=>'PAY',
172 X_WorkerConc_progname=>'PAYPRGEVTW',
173 X_Batch_size=>lv_batch_size,
174 X_Num_Workers=>lv_Num_Workers,
175 X_Argument4 => lv_debug_flag,
176 X_Argument5 => P_Purge_Date,
177 X_Argument6 => fnd_global.conc_request_id,
178 X_Argument7 => null,
179 X_Argument8 => null,
180 X_Argument9 => null,
181 X_Argument10 => null);
182
183 exception when others then
184 fnd_file.put_line(FND_FILE.LOG,'Error '||sqlcode ||' '||sqlerrm);
185
186 end;
187
188 If lv_debug_enabled then
189 fnd_file.put_line(fnd_file.log,'After AD_CONC_UTILS_PKG.submit_subrequests ');
190 End if;
191
192 exception
193 when others then
194 fnd_file.put_line(FND_FILE.LOG,'Error '||sqlcode ||' '||sqlerrm);
195
196 end;
197 end if;
198
199 else
200
201 l_sub_reqtab := fnd_concurrent.get_sub_requests(fnd_global.conc_request_id);
202
203 x_retcode := FND_API.G_RET_STS_SUCCESS;
204
205 for i IN 1..l_sub_reqtab.COUNT()
206 loop
207
208 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
209 X_retcode := FND_API.g_ret_sts_unexp_error;
210 end if;
211
212 end loop;
213
214 /* Display process completion message */
215 if (X_retcode = FND_API.G_RET_STS_SUCCESS) then
216 fnd_file.put_line(FND_FILE.LOG,'***** Process completed successfully *****');
217 end if;
218
219 end if;
220
221 If lv_debug_enabled then
222 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
223 End if;
224
225 EXCEPTION
226 WHEN submit_conc_failed THEN
227 X_retcode := FND_API.g_ret_sts_unexp_error;
228 X_errbuf:=l_module||'.'||l_stmt_num||': Submit concurrent request failed.';
229 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
230
231 WHEN fnd_api.g_exc_unexpected_error THEN
232 X_retcode := FND_API.g_ret_sts_unexp_error;
233
234
235 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
236 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
237
238 WHEN fnd_api.g_exc_error THEN
239 X_retcode := FND_API.g_ret_sts_error;
240
241 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
242 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
243
244 WHEN OTHERS THEN
245 X_retcode := FND_API.g_ret_sts_unexp_error;
246 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
247 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
248
249 END Purge_process_events_PAY_MGR;
250
251 ----------------------------------------------------------------------------------------
252 -- Name : Purge_process_events_PAY_WKR
253 -- Function : Worker process to Purge Process Events.
254 -- This is called by #Purge_process_events_PAY_MGR
255
256 ----------------------------------------------------------------------------------------
257 PROCEDURE Purge_process_events_PAY_WKR (
258 X_errbuf out NOCOPY varchar2,
259 X_retcode out NOCOPY varchar2,
260 X_batch_size in number,
261 X_Worker_Id in number,
262 X_Num_Workers in number,
263 X_Argument4 in varchar2 default null,
264 X_Argument5 in varchar2 default null,
265 X_Argument6 in varchar2 default null,
266 X_Argument7 in varchar2 default null,
267 X_Argument8 in varchar2 default null,
268 X_Argument9 in varchar2 default null,
269 X_Argument10 in varchar2 default null)
270 IS
271
272 l_module constant varchar2(90) := 'PAY_PRG_PROCESS_EVENTS.Purge_process_events_PAY_WKR';
273 l_worker_id number;
274 l_product varchar2(30);
275 l_table_name varchar2(30) := 'PAY_PROCESS_EVENTS';
276 l_id_column varchar2(30) := 'ASSIGNMENT_ID';
277 l_update_name varchar2(30);
278
279 l_table_owner varchar2(30);
280 l_status varchar2(30);
281 l_industry varchar2(30);
282 l_retstatus boolean;
283 l_any_rows_to_process boolean;
284
285 l_start_id number;
286 l_end_id number;
287 l_rows_processed number;
288
289 l_stmt_num number;
290 purge_events_exception exception;
291 l_conc_status boolean;
292 lv_debug_enabled boolean default FALSE;
293 lv_purge_date varchar2(30);
294
295 BEGIN
296
297 l_stmt_num :=0;
298 if X_Argument4 = 'Y' then
299 lv_debug_enabled := TRUE;
300 end if;
301
302 If lv_debug_enabled then
303 fnd_file.put_line(fnd_file.log,'Enter:'||l_module);
304 fnd_file.put_line(fnd_file.log,'In Purge Date :'||X_Argument5);
305 End if;
306
307 BEGIN
308 l_stmt_num :=10;
309
310 l_update_name := 'PAYPRGEVT' ||X_Argument6;
311 l_retstatus := FND_INSTALLATION.GET_APP_INFO('PAY', l_status, l_industry, l_table_owner);
312 /*l_table_owner:='HR'; Commented out for bug 11937056*/
313
314
315 /* Call to AD utility to enable process in multi-threaded mode */
316 ad_parallel_updates_pkg.initialize_id_range(
317 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
318 X_owner=>l_table_owner,
319 X_table=>l_table_name,
320 X_script=>l_update_name,
321 X_ID_column=>l_id_column,
322 X_worker_id=>X_Worker_Id,
323 X_num_workers=>X_num_workers,
324 X_batch_size=>X_batch_size,
325 X_debug_level=>0);
326
327
328 ad_parallel_updates_pkg.get_id_range(
329 l_start_id,
330 l_end_id,
331 l_any_rows_to_process,
332 X_batch_size,
333 TRUE);
334
335
336 while (l_any_rows_to_process = TRUE)
337 loop
338 l_stmt_num :=30;
339 If lv_debug_enabled then
340 fnd_file.put_line(fnd_file.log,'Before calling Purge Process Events');
341 fnd_file.put_line(fnd_file.log,'l_start_id :'||l_start_id);
342 fnd_file.put_line(fnd_file.log,'l_end_id :'||l_end_id);
343 End if;
344
345 /* Call to code for prcoessing purge process events request */
346 PAY_PRG_PROCESS_EVENTS.Purge_process_events(
347 X_errbuf=>X_errbuf,
348 X_retcode=>X_retcode,
349 X_start_id=>l_start_id,
350 X_end_id=>l_end_id,
351 P_Purge_Date=>X_Argument5,
352 P_Debug_Flag=>X_Argument4);
353
354 If lv_debug_enabled then
355 fnd_file.put_line(fnd_file.log,'After calling Purge Process Events' );
356 End if;
357
358 if (X_retcode <>FND_API.G_RET_STS_SUCCESS) then
359 raise purge_events_exception;
360 end if;
361
362 l_rows_processed := X_batch_size;
363 l_stmt_num := 40;
364
365 ad_parallel_updates_pkg.processed_id_range(
366 l_rows_processed,
367 l_end_id);
368
369 commit;
370
371 l_stmt_num := 50;
372 ad_parallel_updates_pkg.get_id_range(
373 l_start_id,
374 l_end_id,
375 l_any_rows_to_process,
376 X_batch_size,
377 FALSE);
378
379 end loop;
380
381 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
382
383 EXCEPTION
384 WHEN OTHERS THEN
385 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
386 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
387 raise;
388 END;
389
390 If lv_debug_enabled then
391 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
392 End if;
393
394 EXCEPTION
395
396 WHEN purge_events_exception THEN
397 ROLLBACK;
398 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
399 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
400
401 WHEN fnd_api.g_exc_unexpected_error THEN
402 ROLLBACK;
403 X_retcode := FND_API.g_ret_sts_unexp_error;
404 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
405 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
406
407 WHEN fnd_api.g_exc_error THEN
408 ROLLBACK;
409 X_retcode := FND_API.g_ret_sts_error;
410 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
411 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
412
413 WHEN OTHERS THEN
414 ROLLBACK;
415 X_retcode := FND_API.g_ret_sts_unexp_error;
416 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
417 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
418
419 END Purge_process_events_PAY_WKR;
420
421
422 -------------------------------------------------------------------------------------
423 -- Name : Purge_process_events
424 -- Type : Private
425 -- Function : To purge data from pay_process_events table and archive it deleted
426 -- deleted data into pay_process_events_shadow table.
427 -- Pre-reqs :
428 -- Parameters :
429 -- IN : p_purge_date
430 -- p_debug_flag
431 -- x_start_id
432 -- x_end_id
433 --
434 -- OUT : X_errbuf out NOCOPY varchar2,
435 -- X_retcode out NOCOPY varchar2
436 --
437 -- Notes : The Procedure is called from Purge_process_events_PAY_WKR
438 --
439 -- End of comments
440 -------------------------------------------------------------------------------------
441
442 PROCEDURE Purge_process_events (
443 x_errbuf out nocopy varchar2,
444 x_retcode out nocopy varchar2,
445 x_start_id in number,
446 x_end_id in number,
447 p_purge_date in varchar2,
448 p_debug_flag in varchar2 )
449 IS
450
451 l_module constant varchar2(90) := 'PAY_PRG_PRCESS_EVENTS.Purge_process_events';
452 lv_application_id constant number := 801;
453 lv_Error_msg varchar2(1000);
454 lv_application_name constant varchar2(10) := 'Payroll';
455 lv_debug_enabled boolean default FALSE;
456 l_effective_start_date date;
457 v_time number;
458 lv_bulk_limit number;
459 lv_param_found boolean default FALSE;
460
461
462 type ppe_t is table of PAY_PROCESS_EVENTS%ROWTYPE index by binary_integer;
463 type ppes_t is table of PAY_PROCESS_EVENTS_SHADOW%ROWTYPE index by binary_integer;
464 type process_event_t is table of PAY_PROCESS_EVENTS.PROCESS_EVENT_ID%TYPE index by binary_integer;
465
466 ppe_table_rec ppe_t;
467 ppes_table_rec ppes_t;
468 process_event_rec process_event_t;
469
470 /* Cursor that defines what data to purge and archive */
471 cursor get_archive_events is
472 select ppe.*
473 from pay_process_events ppe
474 where ppe.creation_date < fnd_date.canonical_to_date(p_purge_date)
475 and ppe.assignment_id between x_start_id and x_end_id
476 and (not exists
477 (select 1
478 from pay_recorded_requests prr
479 where prr.attribute1 = ppe.assignment_id
480 and prr.attribute_category = 'RETRONOT_ASG')
481 or ppe.creation_date < (select prr.recorded_date
482 from pay_recorded_requests prr
483 where prr.attribute1 = ppe.assignment_id
484 and prr.attribute_category = 'RETRONOT_ASG'));
485
486 BEGIN
487
488 if p_debug_flag = 'Y' then
489 lv_debug_enabled := TRUE;
490 end if;
491
492 pay_core_utils.get_action_parameter('PPE_BULK_LIMIT',lv_bulk_limit,lv_param_found);
493
494 /* If PRG_BULK_LIMIT action parameter is not set, default bulk limit to 10000 */
495 if (not lv_param_found) then
496 lv_bulk_limit := 10000;
497 end if;
498
499 If lv_debug_enabled then
500 fnd_file.put_line(fnd_file.log,'Enter :'|| l_module);
501 fnd_file.put_line(fnd_file.log,'In P_Purge_Date :'|| P_Purge_Date);
502 fnd_file.put_line(fnd_file.log,'In X_START_ID :'|| to_char(X_start_id));
503 fnd_file.put_line(fnd_file.log,'In X_END_ID :'|| to_char(X_end_id));
504 fnd_file.put_line(fnd_file.log,'PPE_BULK_LIMIT :'|| lv_bulk_limit);
505 End if;
506
507 v_time := dbms_utility.get_time;
508
509
510 open get_archive_events;
511
512 loop
513
514 fetch get_archive_events bulk collect into ppe_table_rec limit lv_bulk_limit;
515
516 FOR j IN 1 .. ppe_table_rec.count
517 LOOP
518 process_event_rec(j) := ppe_table_rec(j).process_event_id;
519 END LOOP;
520
521 FOR k IN 1 .. ppe_table_rec.count
522 LOOP
523 ppes_table_rec(k).process_event_id := ppe_table_rec(k).process_event_id;
524 ppes_table_rec(k).assignment_id := ppe_table_rec(k).assignment_id;
525 ppes_table_rec(k).effective_date := ppe_table_rec(k).effective_date;
526 ppes_table_rec(k).change_type := ppe_table_rec(k).change_type;
527 ppes_table_rec(k).status := ppe_table_rec(k).status;
528 ppes_table_rec(k).description := ppe_table_rec(k).description;
529 ppes_table_rec(k).event_update_id := ppe_table_rec(k).event_update_id;
530 ppes_table_rec(k).business_group_id := ppe_table_rec(k).business_group_id;
531 ppes_table_rec(k).org_process_event_group_id := ppe_table_rec(k).org_process_event_group_id;
532 ppes_table_rec(k).surrogate_key := ppe_table_rec(k).surrogate_key;
533 ppes_table_rec(k).object_version_number := ppe_table_rec(k).object_version_number;
534 ppes_table_rec(k).last_update_date := ppe_table_rec(k).last_update_date;
535 ppes_table_rec(k).last_updated_by := ppe_table_rec(k).last_updated_by;
536 ppes_table_rec(k).last_update_login := ppe_table_rec(k).last_update_login;
537 ppes_table_rec(k).created_by := ppe_table_rec(k).created_by;
538 ppes_table_rec(k).creation_date := ppe_table_rec(k).creation_date;
539 ppes_table_rec(k).calculation_date := ppe_table_rec(k).calculation_date;
540 ppes_table_rec(k).retroactive_status := ppe_table_rec(k).retroactive_status;
541 ppes_table_rec(k).noted_value := ppe_table_rec(k).noted_value;
542
543 END LOOP;
544
545 forall x in 1 .. ppe_table_rec.count
546 insert into PAY_PROCESS_EVENTS_SHADOW
547 values ppes_table_rec(x);
548
549 forall x in 1 .. process_event_rec.count
550 delete from PAY_PROCESS_EVENTS
551 where process_event_id = process_event_rec(x);
552
553 exit when get_archive_events%notfound;
554 commit;
555
556 end loop;
557
558 if lv_debug_enabled then
559 fnd_file.put_line(fnd_file.log,'Time used: ' || (dbms_utility.get_time - v_time) / 100 || ' secs');
560 end if;
561
562 close get_archive_events;
563
564 commit;
565
566 If lv_debug_enabled then
567 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
568 End if;
569
570 EXCEPTION when others then
571 Rollback;
572 X_errbuf:=l_module||': '|| SQLERRM;
573 fnd_file.put_line(FND_FILE.LOG,'Error Purge_process_events '||SQLCODE||' '||SQLERRM);
574
575 END Purge_process_events;
576
577 END PAY_PRG_PROCESS_EVENTS;