[Home] [Help]
PACKAGE BODY: APPS.PAY_BALANCE_FEED_DEL_PKG
Source
1 PACKAGE BODY PAY_BALANCE_FEED_DEL_PKG AS
2 /* $Header: pyscd.pkb 120.2 2011/04/11 09:34:44 kskoduri noship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'PAY_BALANCE_FEED_DEL_PKG';
4 g_debug boolean;
5 g_lat_bal_check_mode pay_action_parameters.parameter_value%TYPE := null;
6
7 PROCEDURE bal_feed_main_proc(
8 errbuf out nocopy varchar2,
9 retcode out nocopy varchar2,
10 c_element_type_id in number,
11 c_pur_mode in varchar2,
12 c_dummy_param in varchar2 default null,
13 c_dummy_param_1 in varchar2 default null,
14 c_bal_feed_id in number default null,
15 c_sub_class_id in number default null,
16 c_batch_size in number default null) is
17
18
19 Cursor csr_bal_feed_rowid is
20 select rowid,
21 balance_type_id,
22 input_value_id,
23 effective_start_date
24 from PAY_BALANCE_FEEDS_F
25 where BALANCE_FEED_ID=c_bal_feed_id;
26
27 Cursor csr_sub_class_rowid is
28 select rowid
29 from pay_sub_classification_rules_f
30 where SUB_CLASSIFICATION_RULE_ID = c_sub_class_id;
31
32 Cursor csr_bal_feeds_sub_class_rule
33 (
34 p_sub_classification_rule_id number,
35 p_pay_value_name varchar2
36 ) is
37 select bf.rowid row_id,
38 bf.balance_type_id,
39 bf.input_value_id,
40 bf.effective_start_date
41 from pay_sub_classification_rules_f scr,
42 pay_input_values_f iv,
43 pay_balance_feeds_f bf,
44 pay_balance_classifications bc,
45 pay_balance_types bt
46 where scr.sub_classification_rule_id = p_sub_classification_rule_id
47 and iv.element_type_id = scr.element_type_id
48 and iv.name = p_pay_value_name
49 and bc.classification_id = scr.classification_id
50 and bt.balance_type_id = bc.balance_type_id
51 and bf.balance_type_id = bt.balance_type_id
52 and bf.input_value_id = iv.input_value_id
53 and bf.effective_start_date = scr.effective_start_date
54 and bf.effective_end_date = scr.effective_end_date
55 and scr.effective_start_date between iv.effective_start_date
56 and iv.effective_end_date
57 for update;
58
59
60 g_val_start_date date default hr_general.start_of_time;
61 g_val_end_date date default hr_general.end_of_time;
62 g_dt_mode varchar2(10) default 'ZAP';
63
64 bal_rowid rowid;
65 bal_type_id pay_balance_types.balance_type_id%type;
66 inp_value_id pay_input_values_f.input_value_id%type;
67 eff_start_date Date;
68 sub_rowid rowid;
69
70 begin
71
72 if c_pur_mode='Balance feed' and c_bal_feed_id is not null then
73
74 Open csr_bal_feed_rowid;
75 Fetch csr_bal_feed_rowid into bal_rowid,bal_type_id,inp_value_id,eff_start_date;
76 Close csr_bal_feed_rowid;
77
78
79
80 /*
81 hrassact.trash_latest_balances gets called from trigger
82 pay_balance_feeds_ard , due to performance issues in delting from
83 pay_assignment_latest_balances we are performing this operation here
84 before delete from pay_balance_feeds_f.
85
86 */
87
88 If bal_type_id is not null then
89 hr_balance_feeds.lock_balance_type(bal_type_id);
90 End if;
91
92 pay_balance_feed_del_pkg.trash_latest_balances_threaded(errbuf,
93 retcode,
94 bal_type_id,
95 inp_value_id,
96 eff_start_date,
97 c_batch_size);
98
99 Delete from pay_balance_feeds_f
100 where rowid = bal_rowid;
101
102 -- pay_balance_feeds_f_pkg.delete_row (bal_rowid,bal_type_id);
103
104 elsif c_pur_mode='Sub Classification' and c_sub_class_id is not null then
105
106 Open csr_sub_class_rowid;
107 Fetch csr_sub_class_rowid into sub_rowid;
108 Close csr_sub_class_rowid;
109
110
111 for v_bf_rec in csr_bal_feeds_sub_class_rule(c_sub_class_id,'Pay Value') loop
112
113 pay_balance_feed_del_pkg.trash_latest_balances_threaded(errbuf,
114 retcode,
115 v_bf_rec.balance_type_id,
116 v_bf_rec.input_value_id,
117 v_bf_rec.effective_start_date,
118 c_batch_size);
119
120
121 delete from pay_balance_feeds_f bf
122 where bf.rowid = v_bf_rec.row_id;
123
124 end loop;
125
126 If sub_rowid is not null then
127 delete from pay_sub_classification_rules_f
128 where rowid = sub_rowid;
129 End If;
130 if sql%notfound then -- system error trap
131 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
132 hr_utility.set_message_token('PROCEDURE',
133 'PAY_BALANCE_FEED_DEL_PKG.BAL_FEED_MAIN_PROC');
134 hr_utility.set_message_token('STEP','2');
135 hr_utility.raise_error;
136 end if;
137
138 delete from hr_application_ownerships
139 where key_name = 'SUB_CLASSIFICATION_RULE_ID'
140 and key_value = c_sub_class_id;
141
142 --pay_sub_class_rules_pkg.delete_row (sub_rowid,p_sub_classification_rule_id,g_dt_mode,g_val_start_date,g_val_end_date);
143
144
145 else
146
147 errbuf:='Please give value for '||c_pur_mode||' to purge';
148 retcode:=2;
149 end if;
150
151 commit;
152
153 end bal_feed_main_proc;
154
155 /*------------------------- trash_latest_balances_threaded -----------------------*/
156 /*
157 * This procedure is copied from hrassact.trash_latest_balances.
158 * Deltion from pay_assignment_latest_balances will now be done in multiple threads.
159 * Master Procedure will be called from here which will spawn slave process.
160 */
161
162
163 procedure trash_latest_balances_threaded(X_errbuf out NOCOPY varchar2,
164 X_retcode out NOCOPY varchar2,
165 l_balance_type_id number,
166 l_input_value_id number,
167 l_trash_date date,
168 l_batch_size number) is
169 --
170 -- Select all person latest balances to delete.
171 cursor plbc is
172 select /*+ ORDERED INDEX (PLB PAY_PERSON_LATEST_BALANCES_FK1)
173 USE_NL (PLB) */
174 plb.latest_balance_id
175 from pay_defined_balances pdb,
176 pay_person_latest_balances plb
177 where pdb.balance_type_id = l_balance_type_id
178 and plb.defined_balance_id = pdb.defined_balance_id
179 and exists (
180 select null
181 from pay_run_results prr,
182 pay_run_result_values rrv
183 where rrv.input_value_id = l_input_value_id
184 and prr.run_result_id = rrv.run_result_id
185 and prr.status in ('P', 'PA')
186 and nvl(rrv.result_value, '0') <> '0');
187 --
188 cursor lbc is
189 select
190 lb.latest_balance_id
191 from pay_defined_balances pdb,
192 pay_latest_balances lb
193 where pdb.balance_type_id = l_balance_type_id
194 and lb.defined_balance_id = pdb.defined_balance_id
195 and exists (
196 select null
197 from pay_run_results prr,
198 pay_run_result_values rrv
199 where rrv.input_value_id = l_input_value_id
200 and prr.run_result_id = rrv.run_result_id
201 and prr.status in ('P', 'PA')
202 and nvl(rrv.result_value, '0') <> '0');
203 --
204
205 --
206 cursor platbalc is
207 select /*+ ORDERED INDEX (PLB PAY_PERSON_LATEST_BALANCES_FK1)
208 USE_NL (PLB) */
209 plb.latest_balance_id
210 from pay_defined_balances pdb,
211 pay_person_latest_balances plb
212 where pdb.balance_type_id = l_balance_type_id
213 and plb.defined_balance_id = pdb.defined_balance_id;
214 --
215
216 --
217 -- Select all latest balances to delete.
218 cursor latbalc is
219 select /*+ ORDERED INDEX (PLB PAY_LATEST_BALANCES_FK1)
220 USE_NL (PLB) */
221 plb.latest_balance_id
222 from pay_defined_balances pdb,
223 pay_latest_balances plb
224 where pdb.balance_type_id = l_balance_type_id
225 and plb.defined_balance_id = pdb.defined_balance_id;
226 --
227 -- Select if run result value exists for input value
228 cursor ivchk is
229 select '1' from dual
230 where exists (select 1
231 from pay_run_results prr,
232 pay_run_result_values rrv
233 where rrv.input_value_id = l_input_value_id
234 and prr.run_result_id = rrv.run_result_id
235 and prr.status in ('P', 'PA')
236 and nvl(rrv.result_value, '0') <> '0');
237 --
238
239
240 cursor pl_feed_chk is
241 select plb.latest_balance_id,
242 'P' balance_type
243 from pay_person_latest_balances plb,
244 pay_defined_balances pdb,
245 pay_balance_dimensions pbd
246 where pdb.balance_type_id = l_balance_type_id
247 and pdb.defined_balance_id = plb.defined_balance_id
248 and pdb.balance_dimension_id = pbd.balance_dimension_id
249 and pbd.feed_checking_type = 'F'
250 union
251 select plb.latest_balance_id,
252 'B' balance_type
253 from pay_latest_balances plb,
254 pay_defined_balances pdb,
255 pay_balance_dimensions pbd
256 where pdb.balance_type_id = l_balance_type_id
257 and pdb.defined_balance_id = plb.defined_balance_id
258 and pdb.balance_dimension_id = pbd.balance_dimension_id
259 and pbd.feed_checking_type = 'F';
260
261 l_ivchk varchar2(2);
262 l_rrv_found number := -1;
263 --Added following type for Bug:6595092 bulk delete
264 Type t_latbal is table of pay_assignment_latest_balances.latest_balance_id%type;
265 lat_bal_list t_latbal;
266 lv_proc_name varchar2(80);
267
268 --
269 begin
270 g_debug := hr_utility.debug_enabled;
271 lv_proc_name := 'pay_balance_feed_del_pkg.trash_latest_balances_threaded';
272 --
273 if g_debug then
274 hr_utility.set_location(lv_proc_name,10);
275 end if;
276
277 if (g_lat_bal_check_mode is null) then
278 begin
279 if g_debug then
280 hr_utility.set_location(lv_proc_name,15);
281 end if;
282 select parameter_value
283 into g_lat_bal_check_mode
284 from pay_action_parameters
285 where parameter_name = 'LAT_BAL_CHECK_MODE';
286
287 exception
288 when others then
289 g_lat_bal_check_mode := 'N';
290 end;
291
292 if (g_lat_bal_check_mode = 'B') then
293 HRASSACT.CHECK_LAT_BALS_FIRST := TRUE;
294 elsif (g_lat_bal_check_mode = 'R') then
295 HRASSACT.CHECK_RRVS_FIRST := TRUE;
296 end if;
297 end if;
298 --
299 if HRASSACT.CHECK_LATEST_BALANCES = TRUE then
300
301 if HRASSACT.CHECK_RRVS_FIRST = TRUE then
302
303 if g_debug then
304 hr_utility.set_location(lv_proc_name,20);
305 end if;
306 --
307 -- Check for existance of run result value for input value
308 --
309 open ivchk;
310 fetch ivchk
311 into l_ivchk;
312
313 if ivchk%FOUND then
314 --
315 if g_debug then
316 hr_utility.set_location(lv_proc_name,30);
317 end if;
318 -- Delete all balance context values and
319 -- person latest balances.
320 for plbcrec in platbalc loop
321 delete from pay_balance_context_values BCV
322 where BCV.latest_balance_id = plbcrec.latest_balance_id;
323 --
324 delete from pay_person_latest_balances PLB
325 where PLB.latest_balance_id = plbcrec.latest_balance_id;
326 end loop;
327
328 if g_debug then
329 hr_utility.set_location(lv_proc_name,40);
330 end if;
331 -- Delete all balance context values and
332 -- assignment latest balances.
333
334 --Commented the following and added a block with cusrsor and bulk delete
335 --for Bug:6595092
336 /* for albcrec in alatbalc loop
337 delete from pay_balance_context_values BCV
338 where BCV.latest_balance_id = albcrec.latest_balance_id;
339 --
340 delete from pay_assignment_latest_balances ALB
341 where ALB.latest_balance_id = albcrec.latest_balance_id;
342 end loop; */
343
344 Delete_Proc_PAY_MGR (X_errbuf => X_errbuf,
345 X_retcode => X_retcode,
346 p_cursor => 'alatbalc',
347 p_balance_type_id => to_char(l_balance_type_id),
348 X_batch_size => l_batch_size);
349
350 --
351 -- Delete all latest Balanaces.
352 for lbcrec in latbalc loop
353 --
354 delete from pay_latest_balances LB
355 where LB.latest_balance_id = lbcrec.latest_balance_id;
356 end loop;
357 --
358 end if;
359 close ivchk;
360
361 elsif HRASSACT.CHECK_LAT_BALS_FIRST = TRUE then
362
363 if g_debug then
364 hr_utility.set_location(lv_proc_name,50);
365 end if;
366 --
367 -- Check for any latest balances before relevant run result value
368 --
369 for plbcrec in platbalc loop
370 if l_rrv_found = -1 then
371 open ivchk;
372
373 fetch ivchk
374 into l_ivchk;
375
376 if ivchk%FOUND then
377 l_rrv_found := 1;
378 else
379 l_rrv_found := 0;
380 close ivchk;
381 exit;
382 end if;
383 close ivchk;
384 end if;
385 if l_rrv_found = 1 then
386 delete from pay_balance_context_values BCV
387 where BCV.latest_balance_id = plbcrec.latest_balance_id;
388 --
389 delete from pay_person_latest_balances PLB
390 where PLB.latest_balance_id = plbcrec.latest_balance_id;
391 end if;
392 end loop;
393 --
394 if g_debug then
395 hr_utility.set_location(lv_proc_name,60);
396 end if;
397 -- Delete all balance context values and
398 -- assignment latest balances.
399 if l_rrv_found <> 0 then
400 if l_rrv_found = -1 then
401 open ivchk;
402 fetch ivchk
403 into l_ivchk;
404
405 if ivchk%FOUND then
406 l_rrv_found := 1;
407 else
408 l_rrv_found := 0;
409 close ivchk;
410 end if;
411 close ivchk;
412 end if;
413 if l_rrv_found = 1 then
414 Delete_Proc_PAY_MGR (X_errbuf => X_errbuf,
415 X_retcode => X_retcode,
416 p_cursor => 'alatbalc',
417 p_balance_type_id => to_char(l_balance_type_id),
418 X_batch_size => l_batch_size);
419
420 end if;
421 end if;
422 --
423 for lbcrec in latbalc loop
424 if l_rrv_found = -1 then
425 open ivchk;
426
427 fetch ivchk
428 into l_ivchk;
429
430 if ivchk%FOUND then
431 l_rrv_found := 1;
432 else
433 l_rrv_found := 0;
434 close ivchk;
435 exit;
436 end if;
437 close ivchk;
438 end if;
439 if l_rrv_found = 1 then
440 delete from pay_latest_balances ALB
441 where ALB.latest_balance_id = lbcrec.latest_balance_id;
442 end if;
443 end loop;
444
445 else
446 --
447 -- Original Code
448 --
449 if g_debug then
450 hr_utility.set_location(lv_proc_name,70);
451 end if;
452 -- Delete all balance context values and
453 -- person latest balances.
454 for plbcrec in plbc loop
455 delete from pay_balance_context_values BCV
456 where BCV.latest_balance_id = plbcrec.latest_balance_id;
457 --
458 delete from pay_person_latest_balances PLB
459 where PLB.latest_balance_id = plbcrec.latest_balance_id;
460 end loop;
461 --
462 if g_debug then
463 hr_utility.set_location(lv_proc_name,80);
464 end if;
465 -- Delete all balance context values and
466 -- assignment latest balances.
467
468 Delete_Proc_PAY_MGR (X_errbuf => X_errbuf,
469 X_retcode => X_retcode,
470 p_cursor => 'albc',
471 p_balance_type_id => to_char(l_balance_type_id),
472 p_input_value_id => to_char(l_input_value_id),
473 X_batch_size => l_batch_size);
474
475 --
476 if g_debug then
477 hr_utility.set_location(lv_proc_name,70);
478 end if;
479 --
480 for lbcrec in lbc loop
481 delete from pay_latest_balances ALB
482 where ALB.latest_balance_id = lbcrec.latest_balance_id;
483 end loop;
484 --
485 end if;
486 --
487 if g_debug then
488 hr_utility.set_location(lv_proc_name,90);
489 end if;
490 --
491 for plrec in pl_feed_chk loop
492 --
493 if g_debug then
494 hr_utility.set_location(lv_proc_name,100);
495 end if;
496
497 delete from pay_balance_context_values BCV
498 where BCV.latest_balance_id = plrec.latest_balance_id;
499 --
500 if (plrec.balance_type = 'P') then
501 delete from pay_person_latest_balances PLB
502 where PLB.latest_balance_id = plrec.latest_balance_id;
503 else
504 delete from pay_latest_balances PLB
505 where PLB.latest_balance_id = plrec.latest_balance_id;
506 end if;
507 --
508 end loop;
509
510 Delete_Proc_PAY_MGR (X_errbuf => X_errbuf,
511 X_retcode => X_retcode,
512 p_cursor => 'pl_feed_chk_a',
513 p_balance_type_id => to_char(l_balance_type_id),
514 X_batch_size => l_batch_size);
515
516 --
517 end if;
518 --
519 if g_debug then
520 hr_utility.set_location(lv_proc_name,110);
521 end if;
522 --
523 return;
524 --
525 end trash_latest_balances_threaded;
526
527 -------------------------------------------------------------------------------------
528 -- Name : Delete_Proc_PAY_MGR
529 -- Function : This is the Manager Process called by trash_latest_balances_threaded
530 --
531 -------------------------------------------------------------------------------------
532 PROCEDURE Delete_Proc_PAY_MGR (
533 X_errbuf out NOCOPY varchar2,
534 X_retcode out NOCOPY varchar2,
535 p_cursor in varchar2,
536 p_balance_type_id in varchar2,
537 p_input_value_id in varchar2 default null,
538 X_batch_size in number default 1000,
539 X_Num_Workers in number default 5)
540 IS
541
542 l_module CONSTANT VARCHAR2(90) := 'PAY_BALANCE_FEED_DEL_PKG.Delete_Proc_PAY_MGR';
543
544 l_stmt_num number;
545 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Proc_PAY_MGR';
546 l_api_version CONSTANT NUMBER := 1.0;
547
548 l_conc_status BOOLEAN;
549
550 submit_conc_failed EXCEPTION;
551
552 l_phase varchar2(80);
553 l_status_code varchar2(80);
554 l_dev_phase varchar2(15);
555 l_message varchar2(255);
556
557 L_SUB_REQTAB fnd_concurrent.requests_tab_type;
558 req_data varchar2(10);
559 submit_req boolean;
560
561 PAY_UPGRADE_RUNNING exception;
562 l_prg_appid number;
563 l_program_name varchar2(15);
564 l_reqid_count number;
565
566 l_ret_code varchar2(10);
567
568 l_argument4 number;
569 l_argument5 number;
570 l_product varchar2(30);
571 l_status varchar2(30);
572 l_industry varchar2(30);
573 l_retstatus boolean;
574 l_table_owner varchar2(30);
575 lv_param_found boolean default FALSE;
576 lv_Num_Workers number default 1;
577 lv_debug_enabled boolean default FALSE;
578
579 sub_rowid rowid;
580
581 BEGIN
582
583
584 lv_debug_enabled := FALSE;
585
586
587 If lv_debug_enabled then
588 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
589 End if;
590
591 l_stmt_num :=0;
592 l_stmt_num :=5;
593 l_prg_appid := 801;
594 l_program_name := 'PAYSCDW';
595 l_reqid_count := 0;
596
597 req_data := fnd_conc_global.request_data;
598
599 if (req_data is null) then
600 submit_req := TRUE;
601 else
602 submit_req := FALSE;
603 end if;
604
605
606 if (submit_req = TRUE) then
607
608 if (nvl(fnd_global.conc_request_id, -1) < 0) then
609 raise_application_error(-20001, 'SUBMIT_SUBREQUESTS() must be called from a concurrent request');
610 end if;
611
612 -- Standard call to check for call compatibility
613 IF NOT FND_API.Compatible_API_Call (
614 l_api_version,
615 1.0,
616 l_api_name,
617 G_PKG_NAME ) THEN
618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
619 END IF;
620
621 x_retcode := FND_API.G_RET_STS_SUCCESS;
622
623 l_product :='PAY';
624
625 l_stmt_num :=10;
626 l_retstatus := fnd_installation.get_app_info(
627 l_product, l_status, l_industry, l_table_owner);
628
629 if ((l_retstatus = TRUE) AND (l_table_owner is not null)) then
630
631 Begin
632 lv_Num_Workers := X_Num_Workers;
633 /* Get the Pay Action Parameter Value of THREADS */
634
635 If lv_debug_enabled then
636 fnd_file.put_line(fnd_file.log,'Before pay_core_utils.get_action_parameter ');
637 End if;
638
639 pay_core_utils.get_action_parameter('THREADS',lv_Num_Workers,lv_param_found);
640
641 if (not lv_param_found) then
642 lv_Num_Workers := x_Num_Workers;
643 end if;
644
645 If lv_debug_enabled then
646 fnd_file.put_line(fnd_file.log,'No of Workers :'||to_char(lv_Num_Workers));
647 End if;
648
649 If lv_debug_enabled then
650 fnd_file.put_line(fnd_file.log,'After pay_core_utils.get_action_parameter ');
651 End if;
652
653 begin
654
655 If lv_debug_enabled then
656 fnd_file.put_line(fnd_file.log,'Before AD_CONC_UTILS_PKG.submit_subrequests ');
657 End if;
658
659 AD_CONC_UTILS_PKG.submit_subrequests(
660 X_errbuf=>X_errbuf,
661 X_retcode=>X_retcode,
662 X_WorkerConc_app_shortname=>'PAY',
663 X_WorkerConc_progname=>'PAYSCDW',
664 X_Batch_size=> X_batch_size,
665 X_Num_Workers=>lv_Num_Workers,
666 X_Argument4 => p_cursor,
667 X_Argument5 => p_balance_type_id,
668 X_Argument6 => p_input_value_id,
669 X_Argument7 => fnd_global.conc_request_id,
670 X_Argument8 => null,
671 X_Argument9 => null,
672 X_Argument10 => null);
673
674 exception when others then
675 fnd_file.put_line(FND_FILE.LOG,'Error '||sqlcode ||' '||sqlerrm);
676
677 end;
678
679 If lv_debug_enabled then
680 fnd_file.put_line(fnd_file.log,'After AD_CONC_UTILS_PKG.submit_subrequests ');
681 End if;
682 exception
683 when no_data_found then
684 fnd_file.put_line(FND_FILE.LOG, 'No more deletions in pay_assignment_latest_balances.');
685
686 end;
687 end if;
688 else
689
690 l_sub_reqtab := fnd_concurrent.get_sub_requests(fnd_global.conc_request_id);
691
692 x_retcode := FND_API.G_RET_STS_SUCCESS;
693
694 for i IN 1..l_sub_reqtab.COUNT()
695 loop
696
697 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
698 X_retcode := FND_API.g_ret_sts_unexp_error;
699 end if;
700
701 end loop;
702 end if;
703
704 If lv_debug_enabled then
705 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
706 End if;
707
708 EXCEPTION
709 WHEN submit_conc_failed THEN
710 X_retcode := FND_API.g_ret_sts_unexp_error;
711 X_errbuf:=l_module||'.'||l_stmt_num||': Submit concurrent request failed.';
712 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
713
714 WHEN fnd_api.g_exc_unexpected_error THEN
715 X_retcode := FND_API.g_ret_sts_unexp_error;
716
717 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
718 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
719
720 WHEN fnd_api.g_exc_error THEN
721 X_retcode := FND_API.g_ret_sts_error;
722
723 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
724 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
725
726 WHEN OTHERS THEN
727 X_retcode := FND_API.g_ret_sts_unexp_error;
728 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
729 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
730
731 END Delete_Proc_PAY_MGR;
732
733 ----------------------------------------------------------------------------------------
734 -- Name : Delete_Proc_PAY_WKR
735 -- Function : Worker process to delete from pay_assignment_latest_balances
736
737 ----------------------------------------------------------------------------------------
738 PROCEDURE Delete_Proc_PAY_WKR (
739 X_errbuf out NOCOPY varchar2,
740 X_retcode out NOCOPY varchar2,
741 X_batch_size in number,
742 X_Worker_Id in number,
743 X_Num_Workers in number,
744 X_Argument4 in varchar2 default null,
745 X_Argument5 in varchar2 default null,
746 X_Argument6 in varchar2 default null,
747 X_Argument7 in varchar2 default null,
748 X_Argument8 in varchar2 default null,
749 X_Argument9 in varchar2 default null,
750 X_Argument10 in varchar2 default null)
751 IS
752
753 l_module CONSTANT VARCHAR2(90) := 'PAY_BALANCE_FEED_DEL_PKG.Delete_Proc_PAY_WKR';
754 l_worker_id number;
755 l_product varchar2(30);
756 l_table_name varchar2(30) := 'PER_ALL_ASSIGNMENTS_F';
757 l_id_column varchar2(30) := 'ASSIGNMENT_ID';
758 l_update_name varchar2(30);
759
760 l_table_owner varchar2(30);
761 l_status VARCHAR2(30);
762 l_industry VARCHAR2(30);
763 l_retstatus BOOLEAN;
764 l_any_rows_to_process boolean;
765
766 l_start_id number;
767 l_end_id number;
768 l_rows_processed number;
769
770 l_stmt_num number;
771 update_exception exception;
772 l_conc_status BOOLEAN;
773 lv_debug_enabled boolean default FALSE;
774
775 BEGIN
776 l_stmt_num :=0;
777
778 lv_debug_enabled := FALSE;
779
780
781 If lv_debug_enabled then
782 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
783 End if;
784 --
785 BEGIN
786 l_stmt_num :=10;
787
788 l_update_name := 'PAYSCD' ||X_Argument7;
789 l_retstatus := FND_INSTALLATION.GET_APP_INFO('PAY', l_status, l_industry, l_table_owner);
790 /*l_table_owner:='HR'; Commented out for bug 12343825*/
791
792
793 ad_parallel_updates_pkg.initialize_id_range(
794 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
795 X_owner=>l_table_owner,
796 X_table=>l_table_name,
797 X_script=>l_update_name,
798 X_ID_column=>l_id_column,
799 X_worker_id=>X_Worker_Id,
800 X_num_workers=>X_num_workers,
801 X_batch_size=>X_batch_size,
802 X_debug_level=>0);
803
804
805 ad_parallel_updates_pkg.get_id_range(
806 l_start_id,
807 l_end_id,
808 l_any_rows_to_process,
809 X_batch_size,
810 TRUE);
811
812
813 while (l_any_rows_to_process = TRUE)
814 loop
815 l_stmt_num :=30;
816 If lv_debug_enabled then
817 fnd_file.put_line(fnd_file.log,'Before calling Update_Payroll_Subledger');
818 fnd_file.put_line(fnd_file.log,'l_start_id :'||l_start_id);
819 fnd_file.put_line(fnd_file.log,'l_end_id :'||l_end_id);
820 End if;
821
822 PAY_BALANCE_FEED_DEL_PKG.Delete_assnmnt_lat_bal(
823 X_errbuf=>X_errbuf,
824 X_retcode=>X_retcode,
825 x_assnmnt_start_id=>l_start_id,
826 x_assnmnt_end_id=>l_end_id,
827 p_cursor=>X_Argument4,
828 p_balance_type_id=>X_Argument5,
829 P_input_value_id=>X_Argument6);
830
831 If lv_debug_enabled then
832 fnd_file.put_line(fnd_file.log,'After calling Update_Payroll_Subledger');
833 End if;
834
835 if (X_retcode <>FND_API.G_RET_STS_SUCCESS) then
836 raise update_exception;
837 end if;
838
839 l_rows_processed := X_batch_size;
840
841 l_stmt_num :=40;
842
843 ad_parallel_updates_pkg.processed_id_range(
844 l_rows_processed,
845 l_end_id);
846
847 commit;
848
849 l_stmt_num :=50;
850 ad_parallel_updates_pkg.get_id_range(
851 l_start_id,
852 l_end_id,
853 l_any_rows_to_process,
854 X_batch_size,
855 FALSE);
856
857 end loop;
858
859 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
864 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
865 raise;
866 END;
867
868 If lv_debug_enabled then
869 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
870 End if;
871
872 EXCEPTION
873 WHEN update_exception THEN
874 ROLLBACK;
875 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
876 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
877
878 WHEN fnd_api.g_exc_unexpected_error THEN
879 ROLLBACK;
880 X_retcode := FND_API.g_ret_sts_unexp_error;
881 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
882 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
883
884 WHEN fnd_api.g_exc_error THEN
885 ROLLBACK;
886 X_retcode := FND_API.g_ret_sts_error;
887 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
888 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
889
890 WHEN OTHERS THEN
891 ROLLBACK;
892 X_retcode := FND_API.g_ret_sts_unexp_error;
893 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
894 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
895
896 END Delete_Proc_PAY_WKR;
897
898 -------------------------------------------------------------------------------------
899 -- Name : Delete_assnmnt_lat_bal
900 -- Function : To delete from pay_assignment_latest_balances for Assignment ID's
901 -- between to x_assnmnt_start_id and x_assnmnt_end_id .
902 -- Pre-reqs :
903 -- Parameters :
904 -- IN : x_assnmnt_start_id in number
905 -- x_assnmnt_end_id in number
906 -- p_cursor in varchar2
907 -- p_balance_type_id in varchar2
908 -- p_input_value_id in varchar2
909 --
910 -- OUT : X_errbuf out NOCOPY varchar2,
911 -- X_retcode out NOCOPY varchar2
912 --
913 -- Notes : The Procedure is called from Update_Proc_PAY_WKR.
914 --
915 -- End of comments
916 -------------------------------------------------------------------------------------
917
918 PROCEDURE Delete_assnmnt_lat_bal(
919 x_errbuf out nocopy varchar2,
920 x_retcode out nocopy varchar2,
921 x_assnmnt_start_id in number,
922 x_assnmnt_end_id in number,
923 p_cursor in varchar2,
924 p_balance_type_id in varchar2,
925 p_input_value_id in varchar2
926 )
927 IS
928
929 -- Select all assignment latest balances to delete.
930 cursor albc(p_assnmnt_start_id in varchar2,
931 p_assnmnt_end_id in varchar2,
932 p_balance_type_id in varchar2,
933 p_input_value_id in varchar2) is
934 select /*+ ORDERED INDEX (PLB PAY_ASSIGNMENT_LATEST_BALA_FK2)
935 USE_NL (PLB) */
936 plb.latest_balance_id
937 from pay_defined_balances pdb,
938 pay_assignment_latest_balances plb
939 where pdb.balance_type_id = p_balance_type_id
940 and plb.defined_balance_id = pdb.defined_balance_id
941 and plb.assignment_id between p_assnmnt_start_id and p_assnmnt_end_id
942 and exists (
943 select null
944 from pay_run_results prr,
945 pay_run_result_values rrv
946 where rrv.input_value_id = p_input_value_id
947 and prr.run_result_id = rrv.run_result_id
948 and prr.status in ('P', 'PA')
949 and nvl(rrv.result_value, '0') <> '0');
950
951 -- Select all assignment latest balances to delete.
952 cursor alatbalc(p_assnmnt_start_id in varchar2,
953 p_assnmnt_end_id in varchar2,
954 p_balance_type_id in varchar2) is
955 select /*+ ORDERED INDEX (PLB PAY_ASSIGNMENT_LATEST_BALA_FK2)
956 USE_NL (PLB) */
957 plb.latest_balance_id
958 from pay_defined_balances pdb,
959 pay_assignment_latest_balances plb
960 where pdb.balance_type_id = p_balance_type_id
961 and plb.defined_balance_id = pdb.defined_balance_id
962 and plb.assignment_id between p_assnmnt_start_id and p_assnmnt_end_id;
963
964 -- Select the balances that are PL/SQL fed.
965 cursor pl_feed_chk_a(p_assnmnt_start_id in varchar2,
966 p_assnmnt_end_id in varchar2,
967 p_balance_type_id in varchar2) is
968 select alb.latest_balance_id
969 from pay_assignment_latest_balances alb,
970 pay_defined_balances pdb,
971 pay_balance_dimensions pbd
972 where pdb.balance_type_id = p_balance_type_id
973 and pdb.defined_balance_id = alb.defined_balance_id
974 and pdb.balance_dimension_id = pbd.balance_dimension_id
975 and pbd.feed_checking_type = 'F'
976 and alb.assignment_id between p_assnmnt_start_id and p_assnmnt_end_id;
977
978 l_module CONSTANT VARCHAR2(90) := 'PAY_BALANCE_FEED_DEL_PKG.Delete_assnmnt_lat_bal';
979 lv_application_id CONSTANT number := 801;
980 l_upg_batch_id number;
981 lv_Error_msg varchar2(1000);
982 lv_application_name CONSTANT varchar2(10) := 'Payroll';
983 lv_debug_enabled boolean default FALSE;
984
985 Type t_latbal is table of pay_assignment_latest_balances.latest_balance_id%type;
986 lat_bal_list t_latbal;
987 --
988 v_pay_value_name varchar2(80);
989
990 BEGIN
991
992
993 lv_debug_enabled := FALSE;
994
995
996 If lv_debug_enabled then
997 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
998 fnd_file.put_line(fnd_file.log,'In X_ASSNMNT_START_ID :'|| to_char(x_assnmnt_start_id));
999 fnd_file.put_line(fnd_file.log,'In X_ASSNMNT_END_ID :'|| to_char(x_assnmnt_end_id));
1000 fnd_file.put_line(fnd_file.log,'In P_CURSOR :'|| to_char(p_cursor));
1001 fnd_file.put_line(fnd_file.log,'In P_BALANCE_TYPE_ID :'|| to_char(p_balance_type_id));
1002 fnd_file.put_line(fnd_file.log,'In P_INPUT_VALUE_ID :'|| to_char(p_input_value_id));
1003 End if;
1004
1005 If p_cursor = 'alatbalc' then
1006
1007 open alatbalc(x_assnmnt_start_id,x_assnmnt_end_id,p_balance_type_id);
1008 loop
1009 fetch alatbalc bulk collect into lat_bal_list limit 10000;
1010
1011 forall i in 1..lat_bal_list.count
1012 delete from pay_balance_context_values BCV
1013 where BCV.latest_balance_id = lat_bal_list(i);
1014
1015 forall i in 1..lat_bal_list.count
1016 delete from pay_assignment_latest_balances ALB
1017 where ALB.latest_balance_id =lat_bal_list(i);
1018
1019 exit when alatbalc%notfound;
1020 end loop;
1021 lat_bal_list.delete;
1022 if alatbalc%isopen then
1023 close alatbalc;
1024 end if;
1025
1026 elsif p_cursor = 'albc' then
1027 open albc(x_assnmnt_start_id,x_assnmnt_end_id,p_balance_type_id,p_input_value_id);
1028 loop
1029 fetch albc bulk collect into lat_bal_list limit 10000;
1030
1031 forall i in 1..lat_bal_list.count
1032 delete from pay_balance_context_values BCV
1033 where BCV.latest_balance_id = lat_bal_list(i);
1034
1035 forall i in 1..lat_bal_list.count
1036 delete from pay_assignment_latest_balances ALB
1037 where ALB.latest_balance_id =lat_bal_list(i);
1038
1039 exit when albc%notfound;
1040 end loop;
1041
1042 lat_bal_list.delete;
1043
1044 if albc%isopen then
1045 close albc;
1046 end if;
1047
1048 elsif p_cursor = 'pl_feed_chk_a' then
1049
1050 open pl_feed_chk_a(x_assnmnt_start_id,x_assnmnt_end_id,p_balance_type_id);
1051 loop
1052 fetch pl_feed_chk_a bulk collect into lat_bal_list limit 10000;
1053
1054 forall i in 1..lat_bal_list.count
1055 delete from pay_balance_context_values bcv
1056 where bcv.latest_balance_id = lat_bal_list(i);
1057
1058 forall i in 1..lat_bal_list.count
1059 delete from pay_assignment_latest_balances alb
1060 where alb.latest_balance_id =lat_bal_list(i);
1061
1062 exit when pl_feed_chk_a%notfound;
1063 end loop;
1064
1065 lat_bal_list.delete;
1066
1067 if pl_feed_chk_a%isopen then
1068 close pl_feed_chk_a;
1069 end if;
1070
1071 end if;
1072
1073 --
1074
1075 If lv_debug_enabled then
1076 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
1077 End if;
1078 commit;
1079
1080 EXCEPTION when others then
1081 Rollback;
1082 X_errbuf:=l_module||': '|| SQLERRM;
1083 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
1084 fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||SQLCODE||' '||SQLERRM);
1085 end Delete_assnmnt_lat_bal;
1086
1087 END PAY_BALANCE_FEED_DEL_PKG;