[Home] [Help]
PACKAGE BODY: APPS.PAY_BATCH_LINK_PROCESS_PKG
Source
1 PACKAGE BODY pay_batch_link_process_pkg AS
2 /* $Header: pybatlnk.pkb 120.6 2007/03/21 10:34:47 thabara noship $ */
3
4 g_package constant varchar2(31):= 'pay_batch_link_process_pkg.';
5 --
6 -- Global Types
7 --
8 type t_number_tab is table of number index by binary_integer;
9
10 type t_element_link_rec is record
11 (element_link_id number
12 ,element_type_id number
13 ,effective_start_date date
14 ,effective_end_date date
15 ,link_to_all_payrolls_flag pay_element_links_f.link_to_all_payrolls_flag%type
16 ,payroll_id number
17 ,job_id number
18 ,grade_id number
19 ,position_id number
20 ,organization_id number
21 ,location_id number
22 ,pay_basis_id number
23 ,employment_category pay_element_links_f.employment_category%type
24 ,people_group_id number
25 );
26 --
27 type t_element_link_tab is table of t_element_link_rec
28 index by binary_integer;
29
30 type t_payroll_action_rec is record
31 (payroll_action_id number
32 ,business_group_id number
33 ,effective_date date
34 -- Batch Link Specific Parameters
35 ,gen_link_type varchar2(30) -- generate type. A(All) or S(Single)
36 ,bat_link_id number -- batch element link id
37 ,ele_link_id number -- element link id
38 );
39
40 --
41 -- Global Variables
42 --
43 g_standard_links t_element_link_tab;
44 g_pg_links pay_asg_link_usages_pkg.t_pg_link_tab;
45 g_link_initialized boolean:= false;
46 g_pact_rec t_payroll_action_rec;
47 g_err_batch_link_id number;
48 g_batch_links t_number_tab;
49 g_lock_timeout number;
50 g_lock_interval number;
51 g_max_lock_count constant number:= 20;
52
53 --
54 -- ---------------------------------------------------------------------------
55 -- lock_wait
56 --
57 -- Description
58 -- Returns true if the current time is before the lock timeout.
59 -- If p_start_time is null, sysdate is set and returned.
60 --
61 -- ---------------------------------------------------------------------------
62 function lock_wait
63 (p_start_time in out nocopy date
64 ,p_count in out nocopy number
65 ,p_lock_timeout in number default null
66 ) return boolean
67 is
68 l_current_time date;
69 l_lock_timeout number;
70 --
71 l_proc varchar2(72):= g_package||'lock_wait';
72 begin
73 --
74 hr_utility.set_location('Entering: '||l_proc, 5);
75 --
76 l_lock_timeout := nvl(p_lock_timeout, g_lock_timeout);
77 --
78 -- Continue only when valid timeout is set.
79 --
80 if nvl(l_lock_timeout, 0) <= 0 then
81 hr_utility.trace('Lock timeout is not set.');
82 return false;
83 end if;
84 --
85 -- Increment the count.
86 --
87 p_count := nvl(p_count, 0)+1;
88 --
89 if p_count > g_max_lock_count then
90 hr_utility.trace('Lock count exceeded the max count.');
91 return false;
92 end if;
93 --
94 select sysdate into l_current_time
95 from dual;
96 --
97 if p_start_time is null then
98 --
99 p_start_time := l_current_time;
100 hr_utility.trace('Setting the lock start time: '
101 ||to_char(p_start_time,'yyyy/mm/dd hh24:mi:ss'));
102 end if;
103
104 if ((l_current_time - p_start_time)*86400 < l_lock_timeout) then
105 --
106 return true;
107 end if;
108
109 return false;
110
111 end lock_wait;
112 --
113 -- ---------------------------------------------------------------------------
114 -- lock_sleep
115 --
116 -- Description
117 -- Wrapper call to dbms_lock.sleep.
118 --
119 -- ---------------------------------------------------------------------------
120 procedure lock_sleep
121 (p_seconds in number default null
122 )
123 is
124 l_seconds number;
125 --
126 l_proc varchar2(72):= g_package||'lock_sleep';
127 begin
128 --
129 hr_utility.set_location('Entering: '||l_proc, 5);
130 --
131 l_seconds := nvl(p_seconds, g_lock_interval);
132
133 if l_seconds > 0 then
134 hr_utility.trace('Sleep '||to_char(l_seconds));
135 dbms_lock.sleep(l_seconds);
136 end if;
137 end lock_sleep;
138 --
139 -- ---------------------------------------------------------------------------
140 -- load_element_link
141 --
142 -- Description
143 -- Creates element link from the batch element link record.
144 -- ---------------------------------------------------------------------------
145 procedure load_element_link
146 (p_payroll_action_id in number
147 ,p_batch_element_link_id in number
148 ,p_element_link_id out nocopy number
149 )
150 is
151 --
152 cursor csr_bat
153 is
154 select *
155 from pay_batch_element_links
156 where batch_element_link_id = p_batch_element_link_id;
157 --
158 l_bat_rec csr_bat%rowtype;
159 l_link_rec t_element_link_rec;
160 l_ovn number;
161 l_comment_id number;
162 l_message varchar2(240);
163 l_proc varchar2(72):= g_package||'load_element_link';
164 --
165 begin
166 --
167 hr_utility.set_location('Entering: '||l_proc, 5);
168 --
169 open csr_bat;
170 fetch csr_bat into l_bat_rec;
171 close csr_bat;
172 --
173 -- Set the status of this record
174 --
175 pay_batch_object_status_pkg.set_status
176 (p_object_type => 'BEL'
177 ,p_object_id => p_batch_element_link_id
178 ,p_object_status => 'P'
179 ,p_payroll_action_id => p_payroll_action_id
180 );
181
182 --
183 pay_element_link_internal.create_element_link
184 (p_effective_date => l_bat_rec.effective_date
185 ,p_element_type_id => l_bat_rec.element_type_id
186 ,p_business_group_id => l_bat_rec.business_group_id
187 ,p_costable_type => l_bat_rec.costable_type
188 ,p_payroll_id => l_bat_rec.payroll_id
189 ,p_job_id => l_bat_rec.job_id
190 ,p_position_id => l_bat_rec.position_id
191 ,p_people_group_id => l_bat_rec.people_group_id
192 ,p_cost_allocation_keyflex_id => l_bat_rec.cost_allocation_keyflex_id
193 ,p_organization_id => l_bat_rec.organization_id
194 ,p_location_id => l_bat_rec.location_id
195 ,p_grade_id => l_bat_rec.grade_id
196 ,p_balancing_keyflex_id => l_bat_rec.balancing_keyflex_id
197 ,p_element_set_id => l_bat_rec.element_set_id
198 ,p_pay_basis_id => l_bat_rec.pay_basis_id
199 ,p_link_to_all_payrolls_flag => l_bat_rec.link_to_all_payrolls_flag
200 ,p_standard_link_flag => l_bat_rec.standard_link_flag
201 ,p_transfer_to_gl_flag => l_bat_rec.transfer_to_gl_flag
202 ,p_comments => null
203 ,p_employment_category => l_bat_rec.employment_category
204 ,p_qualifying_age => l_bat_rec.qualifying_age
205 ,p_qualifying_length_of_service => l_bat_rec.qualifying_length_of_service
206 ,p_qualifying_units => l_bat_rec.qualifying_units
207 ,p_attribute_category => l_bat_rec.attribute_category
208 ,p_attribute1 => l_bat_rec.attribute1
209 ,p_attribute2 => l_bat_rec.attribute2
210 ,p_attribute3 => l_bat_rec.attribute3
211 ,p_attribute4 => l_bat_rec.attribute4
212 ,p_attribute5 => l_bat_rec.attribute5
213 ,p_attribute6 => l_bat_rec.attribute6
214 ,p_attribute7 => l_bat_rec.attribute7
215 ,p_attribute8 => l_bat_rec.attribute8
216 ,p_attribute9 => l_bat_rec.attribute9
217 ,p_attribute10 => l_bat_rec.attribute10
218 ,p_attribute11 => l_bat_rec.attribute11
219 ,p_attribute12 => l_bat_rec.attribute12
220 ,p_attribute13 => l_bat_rec.attribute13
221 ,p_attribute14 => l_bat_rec.attribute14
222 ,p_attribute15 => l_bat_rec.attribute15
223 ,p_attribute16 => l_bat_rec.attribute16
224 ,p_attribute17 => l_bat_rec.attribute17
225 ,p_attribute18 => l_bat_rec.attribute18
226 ,p_attribute19 => l_bat_rec.attribute19
227 ,p_attribute20 => l_bat_rec.attribute20
228 --
229 ,p_cost_concat_segments => null
230 ,p_balance_concat_segments => null
231 ,p_element_link_id => l_link_rec.element_link_id
232 ,p_comment_id => l_comment_id
233 ,p_object_version_number => l_ovn
234 ,p_effective_start_date => l_link_rec.effective_start_date
235 ,p_effective_end_date => l_link_rec.effective_end_date
236 );
237 --
238 -- Update the values that were bypassed in creating the link.
239 --
240 if (l_bat_rec.comment_id is not null) then
241 --
242 update pay_element_links_f
243 set comment_id = l_bat_rec.comment_id
244 where
245 element_link_id = l_link_rec.element_link_id
246 and effective_start_date = l_link_rec.effective_start_date
247 and effective_end_date = l_link_rec.effective_end_date
248 ;
249 end if;
250 --
251 -- If this is a standard link or people group link then
252 -- further processing needs to be done in batch mode.
253 --
254 if (l_bat_rec.standard_link_flag = 'Y')
255 or (l_bat_rec.people_group_id is not null) then
256 --
257 -- Set the batch object status
258 --
259 pay_batch_object_status_pkg.set_status
260 (p_object_type => 'EL'
261 ,p_object_id => l_link_rec.element_link_id
262 ,p_object_status => 'U'
263 ,p_payroll_action_id => p_payroll_action_id
264 );
265
266 end if;
267 --
268 -- Update the batch record as complete.
269 --
270 pay_batch_object_status_pkg.set_status
271 (p_object_type => 'BEL'
272 ,p_object_id => p_batch_element_link_id
273 ,p_object_status => 'C'
274 ,p_payroll_action_id => p_payroll_action_id
275 );
276 --
277 -- Update the batch link
278 --
279 update pay_batch_element_links
280 set element_link_id = l_link_rec.element_link_id
281 where batch_element_link_id = p_batch_element_link_id;
282
283 --
284 -- Set out variable
285 --
286 p_element_link_id := l_link_rec.element_link_id;
287 --
288 hr_utility.set_location('Leaving: '||l_proc, 100);
289 --
290 exception
291 when others then
292 --
293 -- Remember the batch element link ID
294 --
295 g_err_batch_link_id := p_batch_element_link_id;
296 --
297 raise;
298
299 end load_element_link;
300 --
301 -- ---------------------------------------------------------------------------
302 -- init_ppa
303 --
304 -- Description
305 -- Initialises payroll action.
306 -- ---------------------------------------------------------------------------
307 procedure init_ppa
308 (p_payroll_action_id in number)
309 is
310 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
311 --
312 l_null_ppa t_payroll_action_rec;
313 --
314 cursor csr_ppa
315 is
316 select
317 payroll_action_id
318 ,business_group_id
319 ,effective_date
320 ,legislative_parameters
321 ,pay_core_utils.get_parameter
322 ('GEN_LINK_TYPE', legislative_parameters) gen_link_type
323 ,pay_core_utils.get_parameter
324 ('ELE_LINK_ID', legislative_parameters) ele_link_id
325 ,pay_core_utils.get_parameter
326 ('BAT_LINK_ID', legislative_parameters) bat_link_id
327 from pay_payroll_actions ppa
331 --
328 where payroll_action_id = p_payroll_action_id;
329 --
330 l_ppa_rec csr_ppa%rowtype;
332 l_timeout_char pay_action_parameters.parameter_value%type;
333 l_timeout_found boolean;
334 l_interval_char pay_action_parameters.parameter_value%type;
335 l_interval_found boolean;
336 --
337 l_proc varchar2(72):= g_package||'init_ppa';
338 begin
339 --
340 hr_utility.set_location('Entering: '||l_proc, 5);
341 --
342 -- Check to see if the payroll action has been initialized.
343 --
344 if g_pact_rec.payroll_action_id = p_payroll_action_id then
345 return;
346 end if;
347 --
348 -- Reset the payroll action global record.
349 --
350 g_pact_rec := l_null_ppa;
351
352 --
353 -- Get payroll action info.
354 --
355 open csr_ppa;
356 fetch csr_ppa into l_ppa_rec;
357 close csr_ppa;
358 --
359 pay_core_utils.assert_condition
360 (l_proc||':1'
361 ,l_ppa_rec.payroll_action_id is not null);
362 --
363 g_pact_rec.payroll_action_id := p_payroll_action_id;
364 g_pact_rec.business_group_id := l_ppa_rec.business_group_id;
365 g_pact_rec.effective_date := l_ppa_rec.effective_date;
366 g_pact_rec.gen_link_type := l_ppa_rec.gen_link_type;
367 g_pact_rec.bat_link_id := l_ppa_rec.bat_link_id;
368 g_pact_rec.ele_link_id := l_ppa_rec.ele_link_id;
369
370 --
371 -- Get action parameters.
372 --
373 pay_core_utils.get_action_parameter
374 ('BEE_LOCK_MAX_WAIT_SEC', l_timeout_char, l_timeout_found);
375
376 if l_timeout_found then
377 g_lock_timeout := fnd_number.canonical_to_number(l_timeout_char);
378 else
379 g_lock_timeout := 0;
380 end if;
381 --
382 pay_core_utils.get_action_parameter
383 ('BEE_LOCK_INTERVAL_WAIT_SEC', l_interval_char, l_interval_found);
384
385 if l_interval_found then
386 g_lock_interval := fnd_number.canonical_to_number(l_interval_char);
387 else
388 g_lock_interval := 0;
389 end if;
390
391 hr_utility.set_location('Leaving: '||l_proc, 100);
392 --
393 end init_ppa;
394 --
395 -- ---------------------------------------------------------------------------
396 -- transfer_batch_links
397 --
398 -- Description
399 -- Transfers the batch element links to element links.
400 -- ---------------------------------------------------------------------------
401 procedure transfer_batch_links
402 (p_pact_rec in t_payroll_action_rec
403 )
404 is
405 --
406 -- Batch links to lock
407 --
408 cursor csr_batlink(p_gen_link_type varchar2
409 ,p_batlink_id number
410 ,p_bgid number
411 )
412 is
413 select
414 bat.batch_element_link_id
415 from
416 pay_batch_element_links bat
417 where
418 bat.business_group_id = p_bgid
419 and (bat.batch_element_link_id = p_batlink_id
420 or ( p_gen_link_type = 'A'
421 and p_batlink_id is null
422 and nvl(pay_batch_object_status_pkg.get_status
423 ('BEL',bat.batch_element_link_id)
424 ,'U') <> 'C')
425 )
426 and bat.element_link_id is null
427 --
428 -- Ensure element type exists.
429 --
430 and exists
431 (select 1 from pay_element_types_f pet
432 where pet.element_type_id = bat.element_type_id)
433 order by
434 bat.element_type_id
435 ,bat.effective_date
436 for update nowait
437 ;
438 --
439 l_bat_link_id number;
440 l_link_id_out number;
441 l_proc varchar2(72):= g_package||'transfer_batch_links';
442 begin
443 --
444 hr_utility.set_location('Entering: '||l_proc, 5);
445 --
446 g_err_batch_link_id := null;
447 g_batch_links.delete;
448 --
449 if (p_pact_rec.gen_link_type = 'A') or
450 (p_pact_rec.gen_link_type = 'S' and p_pact_rec.bat_link_id is not null)
451 then
452 --
453 -- Lock batch links.
454 --
455 begin
456 open csr_batlink
457 (p_pact_rec.gen_link_type
458 ,p_pact_rec.bat_link_id
459 ,p_pact_rec.business_group_id);
460 fetch csr_batlink bulk collect into g_batch_links;
461 close csr_batlink;
462
463 exception
464 when hr_api.object_locked then
465 --
466 -- Failed to lock the batch link.
467 --
468 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
469 hr_utility.set_message_token('TABLE_NAME', 'pay_batch_element_links');
470 hr_utility.raise_error;
471 end;
472 --
473 -- Load batch element links
474 --
475 for i in 1..g_batch_links.count loop
476
477 l_bat_link_id := g_batch_links(i);
478 --
479 load_element_link
480 (p_pact_rec.payroll_action_id
481 ,l_bat_link_id
482 ,l_link_id_out
483 );
484 end loop;
485 --
486 end if;
487 --
488 hr_utility.set_location('Leaving: '||l_proc, 100);
489 --
490 end transfer_batch_links;
491 --
492 -- ---------------------------------------------------------------------------
493 -- init_links
494 --
495 -- Description
496 -- Initialises element links.
497 -- p_phase should be 1 - Payroll action level initialization.
498 -- or 2 - Assignment action level initialization.
502 ,p_phase in number
499 -- ---------------------------------------------------------------------------
500 procedure init_links
501 (p_pact_rec in t_payroll_action_rec
503 )
504 is
505 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
506 --
507 l_link t_element_link_rec;
508 l_pg_link pay_asg_link_usages_pkg.t_pg_link_rec;
509 --
510 cursor csr_elelink
511 (p_gen_link_type varchar2
512 ,p_elelink_id number
513 ,p_bgid number
514 ,p_pact_id number
515 ,p_phase number)
516 is
517 --
518 -- element links to process
519 --
520 select
521 pel.element_link_id
522 ,pel.element_type_id
523 ,min(pel.effective_start_date) effective_start_date
524 ,max(pel.effective_end_date) effective_end_date
525 ,pel.link_to_all_payrolls_flag
526 ,pel.payroll_id
527 ,pel.job_id
528 ,pel.grade_id
529 ,pel.position_id
530 ,pel.organization_id
531 ,pel.location_id
532 ,pel.pay_basis_id
533 ,pel.employment_category
534 ,pel.people_group_id
535 ,pel.standard_link_flag
536 --
537 ,bos.payroll_action_id
538 ,bos.object_status
539 from
540 pay_element_links_f pel
541 ,pay_batch_object_status bos
542 where
543 p_phase = 1
544 and ( pel.element_link_id = p_elelink_id
545 or ( p_gen_link_type = 'A'
546 and bos.object_status <> 'C'))
547 and pel.business_group_id = p_bgid
548 and (pel.standard_link_flag = 'Y'
549 or pel.people_group_id is not null)
550 and bos.object_type = 'EL'
551 and bos.object_id = pel.element_link_id
552 -- not processed by this payroll action
553 and nvl(bos.payroll_action_id, -999) <> p_pact_id
554 and (nvl(bos.object_status, 'C') <> 'P'
555 or not exists
556 (select null from pay_payroll_actions
557 where payroll_action_id = bos.payroll_action_id))
558 group by
559 pel.element_link_id
560 ,pel.element_type_id
561 ,pel.link_to_all_payrolls_flag
562 ,pel.payroll_id
563 ,pel.job_id
564 ,pel.grade_id
565 ,pel.position_id
566 ,pel.organization_id
567 ,pel.location_id
568 ,pel.pay_basis_id
569 ,pel.employment_category
570 ,pel.people_group_id
571 ,pel.standard_link_flag
572 --
573 ,bos.payroll_action_id
574 ,bos.object_status
575 --
576 UNION ALL
577 --
578 -- element links being processed
579 --
580 select
581 pel.element_link_id
582 ,pel.element_type_id
583 ,min(pel.effective_start_date) effective_start_date
584 ,max(pel.effective_end_date) effective_end_date
585 ,pel.link_to_all_payrolls_flag
586 ,pel.payroll_id
587 ,pel.job_id
588 ,pel.grade_id
589 ,pel.position_id
590 ,pel.organization_id
591 ,pel.location_id
592 ,pel.pay_basis_id
593 ,pel.employment_category
594 ,pel.people_group_id
595 ,pel.standard_link_flag
596 --
597 ,bos.payroll_action_id
598 ,bos.object_status
599 from
600 pay_batch_object_status bos
601 ,pay_element_links_f pel
602 where
603 bos.payroll_action_id = p_pact_id
604 and bos.object_type = 'EL'
605 and pel.element_link_id = bos.object_id
606 and pel.business_group_id = p_bgid
607 and (pel.standard_link_flag = 'Y'
608 or pel.people_group_id is not null)
609 group by
610 pel.element_link_id
611 ,pel.element_type_id
612 ,pel.link_to_all_payrolls_flag
613 ,pel.payroll_id
614 ,pel.job_id
615 ,pel.grade_id
616 ,pel.position_id
617 ,pel.organization_id
618 ,pel.location_id
619 ,pel.pay_basis_id
620 ,pel.employment_category
621 ,pel.people_group_id
622 ,pel.standard_link_flag
623 --
624 ,bos.payroll_action_id
625 ,bos.object_status
626 order by
627 people_group_id
628 ,element_link_id
629 ;
630 --
631 l_idx number:=0;
632 l_proc varchar2(72):= g_package||'init_links';
633 --
634
635 --
636 -- This procedure sets the Processing status for the specified
637 -- element link and commit immediately within this process so
638 -- it can be reflected to multi-threads.
639 --
640 procedure set_processing_status
641 (p_element_link_id in number
642 ,p_payroll_action_id in number
643 )
644 is
645 pragma autonomous_transaction;
646 begin
647 --
648 pay_batch_object_status_pkg.set_status
649 (p_object_type => 'EL'
650 ,p_object_id => p_element_link_id
651 ,p_object_status => 'P'
652 ,p_payroll_action_id => p_payroll_action_id
653 );
654 --
655 commit;
656 --
657 end set_processing_status;
658 --
659 begin
660 --
661 hr_utility.set_location('Entering: '||l_proc, 5);
662 --
663 -- Check to see if the link info has been initialized.
664 --
665 if g_link_initialized then
666 return;
667 end if;
668 --
669 -- Initialise globals
670 --
671 g_standard_links.delete;
672 g_pg_links.delete;
673 --
674 for l_rec in csr_elelink
675 (p_pact_rec.gen_link_type
676 ,p_pact_rec.ele_link_id
677 ,p_pact_rec.business_group_id
678 ,p_pact_rec.payroll_action_id
679 ,p_phase)
683 --
680 loop
681
682 if l_rec.standard_link_flag = 'Y' then
684 -- Add a standard link record.
685 --
686 l_link.element_link_id := l_rec.element_link_id;
687 l_link.element_type_id := l_rec.element_type_id;
688 l_link.effective_start_date := l_rec.effective_start_date;
689 l_link.effective_end_date := l_rec.effective_end_date;
690 l_link.link_to_all_payrolls_flag := l_rec.link_to_all_payrolls_flag;
691 l_link.payroll_id := l_rec.payroll_id;
692 l_link.job_id := l_rec.job_id;
693 l_link.grade_id := l_rec.grade_id;
694 l_link.position_id := l_rec.position_id;
695 l_link.organization_id := l_rec.organization_id;
696 l_link.location_id := l_rec.location_id;
697 l_link.pay_basis_id := l_rec.pay_basis_id;
698 l_link.employment_category := l_rec.employment_category;
699 l_link.people_group_id := l_rec.people_group_id;
700
701 g_standard_links(g_standard_links.count+1) := l_link;
702
703 end if;
704 --
705 if l_rec.people_group_id is not null then
706 --
707 -- Add a people group link record.
708 --
709 l_pg_link.people_group_id := l_rec.people_group_id;
710 l_pg_link.element_link_id := l_rec.element_link_id;
711 l_pg_link.effective_start_date := l_rec.effective_start_date;
712 l_pg_link.effective_end_date := l_rec.effective_end_date;
713
714 g_pg_links(g_pg_links.count+1) := l_pg_link;
715
716 end if;
717 --
718 -- Now lock this record.
719 --
720 if l_rec.object_status = 'P'
721 and l_rec.payroll_action_id = p_pact_rec.payroll_action_id then
722 --
723 -- The status has already been set.
724 --
725 null;
726
727 elsif p_phase = 1 then
728 --
729 pay_batch_object_status_pkg.set_status
730 (p_object_type => 'EL'
731 ,p_object_id => l_rec.element_link_id
732 ,p_object_status => 'P'
733 ,p_payroll_action_id => p_pact_rec.payroll_action_id
734 );
735 elsif p_phase = 2 then
736 --
737 -- The process reaches here only when rerunning.
738 -- We need commit for multi-threads environment.
739 --
740 set_processing_status
741 (p_element_link_id => l_rec.element_link_id
742 ,p_payroll_action_id => p_pact_rec.payroll_action_id
743 );
744 end if;
745 --
746 end loop;
747 --
748 g_link_initialized := true;
749 --
750 hr_utility.set_location('Leaving: '||l_proc, 100);
751 --
752 exception
753 when hr_api.object_locked then
754 --
755 -- Failed to lock element link.
756 --
757 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
758 hr_utility.set_message_token('TABLE_NAME', 'pay_element_links_f');
759 hr_utility.raise_error;
760 end init_links;
761 --
762 -- ---------------------------------------------------------------------------
763 -- action_archinit
764 --
765 -- Description
766 -- Assignment action level initialization.
767 -- ---------------------------------------------------------------------------
768 procedure action_archinit
769 (p_payroll_action_id in number)
770 is
771 l_start_time date;
772 l_lock_count number:= 0;
773 l_proc varchar2(72):= g_package||'action_archinit';
774 begin
775 --
776 hr_utility.set_location('Entering: '||l_proc, 5);
777 --
778 -- Initialize payroll action info.
779 --
780 init_ppa(p_payroll_action_id);
781
782 --
783 -- Loop until timeout setting allows for locking.
784 --
785 loop
786 --
787 begin
788 --
789 -- Issue a savepoint
790 --
791 savepoint action_archinit_sp;
792 --
793 -- Initialize element link info.
794 --
795 init_links(g_pact_rec, 2);
796 --
797 -- If succeeded, exit the loop.
798 --
799 exit;
800 exception
801 when hr_api.object_locked then
802 --
803 if lock_wait(l_start_time, l_lock_count) then
804 --
805 rollback to action_archinit_sp;
806 lock_sleep;
807 else
808 raise;
809 end if;
810 --
811 when others then
812 hr_message.provide_error;
813 if (hr_message.last_message_name = 'HR_7165_OBJECT_LOCKED') and
814 lock_wait(l_start_time, l_lock_count) then
815 --
816 rollback to action_archinit_sp;
817 lock_sleep;
818 else
819 raise;
820 end if;
821 end;
822 --
823 end loop;
824
825 hr_utility.set_location('Leaving: '||l_proc, 100);
826 --
827 end action_archinit;
828 --
829 -- ---------------------------------------------------------------------------
830 -- action_range_cursor
831 --
832 -- Description
833 -- Creates element links from the saved batch element links,
834 -- then returns the sql statement for the people processed.
835 --
836 -- ---------------------------------------------------------------------------
837 procedure action_range_cursor
838 (p_payroll_action_id in number
839 ,p_sqlstr out nocopy varchar2
840 )
841 is
842 l_start_time date;
843 l_lock_count number:= 0;
847 begin
844 l_sql varchar2(32000);
845 l_link_id number;
846 l_proc varchar2(72):= g_package||'action_range_cursor';
848 --
849 hr_utility.set_location('Entering: '||l_proc, 5);
850 --
851 --
852 -- Initialize payroll action info.
853 --
854 init_ppa(p_payroll_action_id);
855
856 --
857 -- Loop until timeout setting allows for locking.
858 --
859 loop
860 --
861 begin
862 --
863 -- Issue a savepoint
864 --
865 savepoint action_range_cursor_sp;
866 --
867 -- Load the batch element links.
868 --
869 transfer_batch_links(g_pact_rec);
870 --
871 -- Initialize element link info and lock the element links.
872 --
873 init_links(g_pact_rec, 1);
874 --
875 -- If succeeded, exit the loop.
876 --
877 exit;
878 exception
879 when hr_api.object_locked then
880 --
881 if lock_wait(l_start_time, l_lock_count) then
882 --
883 rollback to action_range_cursor_sp;
884 lock_sleep;
885 else
886 raise;
887 end if;
888 --
889 when others then
890 hr_message.provide_error;
891 if (hr_message.last_message_name = 'HR_7165_OBJECT_LOCKED') and
892 lock_wait(l_start_time, l_lock_count) then
893 --
894 rollback to action_range_cursor_sp;
895 lock_sleep;
896 else
897 raise;
898 end if;
899 end;
900 --
901 end loop;
902
903 --
904 -- Create assignment actions only when there are element links
905 -- to process.
906 --
907 if g_standard_links.count = 0 and
908 g_pg_links.count = 0 then
909
910 l_sql := 'select nvl(1,:payroll_action_id) from dual where 1 = 0';
911
912 elsif g_pg_links.count > 0 then
913 -- include non-emp assignments for ALUs.
914 l_sql :=
915 'select distinct asg.person_id
916 from
917 pay_payroll_actions ppa
918 ,per_all_assignments_f asg
919 where
920 ppa.payroll_action_id = :payroll_action_id
921 and asg.business_group_id = ppa.business_group_id
922 and (asg.assignment_type = ''E''
923 or (asg.people_group_id is not null
924 and asg.assignment_type not in (''A'',''O'')))
925 order by asg.person_id';
926
927 else
928 l_sql :=
929 'select distinct asg.person_id
930 from
931 pay_payroll_actions ppa
932 ,per_all_assignments_f asg
933 ,per_periods_of_service pos
934 where
935 ppa.payroll_action_id = :payroll_action_id
936 and asg.business_group_id = ppa.business_group_id
937 and pos.person_id = asg.person_id
938 and pos.period_of_service_id = asg.period_of_service_id
939 and pos.business_group_id = ppa.business_group_id
940 order by asg.person_id';
941 --
942 end if;
943
944 p_sqlstr := l_sql;
945
946 --
947 hr_utility.set_location('Leaving: '||l_proc, 100);
948 --
949 end action_range_cursor;
950
951 -- ---------------------------------------------------------------------------
952 -- action_action_creation
953 --
954 -- Description:
955 -- Creates assignment actions.
956 --
957 -- ---------------------------------------------------------------------------
958 procedure action_action_creation
959 (p_payroll_action_id in number
960 ,p_start_person_id in number
961 ,p_end_person_id in number
962 ,p_chunk in number
963 )
964 is
965 --
966 l_asgact_id number;
967 l_creating_alu_flag varchar2(1);
968 --
969 cursor csr_asg
970 (p_stperson in number
971 ,p_endperson in number
972 ,p_bgid in number
973 ,p_creating_alu_flag in varchar2
974 )
975 is
976 select distinct
977 paf.assignment_id
978 ,paf.person_id
979 from
980 per_periods_of_service pos
981 ,per_all_assignments_f paf
982 where
983 pos.person_id between p_stperson and p_endperson
984 and pos.business_group_id = p_bgid
985 and paf.period_of_service_id = pos.period_of_service_id
986 and p_creating_alu_flag = 'N'
987 --
988 union all
989 -- We should handle all assignment types for ALUs.
990 select distinct
991 paf.assignment_id
992 ,paf.person_id
993 from
994 per_all_assignments_f paf
995 where
996 paf.person_id between p_stperson and p_endperson
997 and paf.business_group_id = p_bgid
998 and (paf.assignment_type = 'E'
999 or (paf.people_group_id is not null
1000 and paf.assignment_type not in ('A','O')))
1001 and p_creating_alu_flag = 'Y'
1002 order by 1, 2;
1003 --
1004 l_proc varchar2(72):= g_package||'action_action_creation';
1005 --
1006 begin
1007 --
1008 hr_utility.set_location('Entering: '||l_proc, 5);
1009 --
1010 -- Initialize payroll action info.
1011 --
1012 init_ppa(p_payroll_action_id);
1013
1014 --
1015 -- Initialize element link info.
1016 --
1017 init_links(g_pact_rec, 2);
1018
1019 if g_pg_links.count > 0 then
1020 l_creating_alu_flag := 'Y';
1021 else
1022 l_creating_alu_flag := 'N';
1023 end if;
1024
1025 --
1026 for l_asg in csr_asg
1027 (p_start_person_id
1031 )
1028 ,p_end_person_id
1029 ,g_pact_rec.business_group_id
1030 ,l_creating_alu_flag
1032 loop
1033 --
1034 select pay_assignment_actions_s.nextval into l_asgact_id
1035 from dual;
1036 --
1037 -- Create assignment action.
1038 --
1039 hr_nonrun_asact.insact
1040 (l_asgact_id
1041 ,l_asg.assignment_id
1042 ,p_payroll_action_id
1043 ,p_chunk
1044 ,null
1045 ,null
1046 ,'U'
1047 ,null);
1048
1049 --
1050 end loop;
1051 --
1052 hr_utility.set_location('Leaving: '||l_proc, 100);
1053 --
1054 end action_action_creation;
1055 --
1056 -- ---------------------------------------------------------------------------
1057 -- asg_action_main
1058 --
1059 -- Description:
1060 -- Assignment action level main process.
1061 -- Creates ALUs and standard link entries.
1062 -- ---------------------------------------------------------------------------
1063 procedure asg_action_main
1064 (p_assignment_id in number
1065 )
1066 is
1067 l_link t_element_link_rec;
1068 l_proc varchar2(72):= g_package||'asg_action_main';
1069 begin
1070 --
1071 hr_utility.set_location('Entering: '||l_proc, 5);
1072 --
1073 -- Populate ALUs
1074 --
1075 if g_pg_links.count > 0 then
1076
1077 pay_asg_link_usages_pkg.create_alu_asg
1078 (p_assignment_id => p_assignment_id
1079 ,p_pg_link_tab => g_pg_links
1080 );
1081 end if;
1082 --
1083 hr_utility.set_location(l_proc, 10);
1084 --
1085 -- Populate element entries
1086 --
1087 for i in 1..g_standard_links.count loop
1088 --
1089 l_link := g_standard_links(i);
1090 --
1091 hrentmnt.maintain_entries_el
1092 (p_business_group_id => g_pact_rec.business_group_id
1093 ,p_element_link_id => l_link.element_link_id
1094 ,p_element_type_id => l_link.element_type_id
1095 ,p_effective_start_date => l_link.effective_start_date
1096 ,p_effective_end_date => l_link.effective_end_date
1097 ,p_payroll_id => l_link.payroll_id
1098 ,p_link_to_all_payrolls_flag => l_link.link_to_all_payrolls_flag
1099 ,p_job_id => l_link.job_id
1100 ,p_grade_id => l_link.grade_id
1101 ,p_position_id => l_link.position_id
1102 ,p_organization_id => l_link.organization_id
1103 ,p_location_id => l_link.location_id
1104 ,p_pay_basis_id => l_link.pay_basis_id
1105 ,p_employment_category => l_link.employment_category
1106 ,p_people_group_id => l_link.people_group_id
1107 ,p_assignment_id => p_assignment_id
1108 );
1109
1110 end loop;
1111 --
1112 hr_utility.set_location('Leaving: '||l_proc, 100);
1113 --
1114 end asg_action_main;
1115 --
1116 -- ---------------------------------------------------------------------------
1117 -- action_archive_data
1118 --
1119 -- Description:
1120 -- Archiver assignment process.
1121 --
1122 -- ---------------------------------------------------------------------------
1123 procedure action_archive_data
1124 (p_assactid in number
1125 ,p_effective_date in date
1126 )
1127 is
1128 l_asgid number;
1129 l_start_time date;
1130 l_lock_count number:= 0;
1131 l_proc varchar2(72):= g_package||'action_archive_data';
1132 begin
1133 --
1134 hr_utility.set_location('Entering: '||l_proc, 5);
1135 --
1136 select assignment_id into l_asgid
1137 from pay_assignment_actions
1138 where assignment_action_id = p_assactid;
1139
1140 --
1141 -- Loop until timeout setting allows for locking.
1142 --
1143 loop
1144 --
1145 begin
1146 --
1147 -- Issue a savepoint
1148 --
1149 savepoint action_archive_data_sp;
1150 --
1151 -- Asg action level main process
1152 --
1153 asg_action_main(l_asgid);
1154 --
1155 -- If succeeded, exit the loop.
1156 --
1157 exit;
1158 exception
1159 when hr_api.object_locked then
1160 --
1161 if lock_wait(l_start_time, l_lock_count) then
1162 --
1163 rollback to action_archive_data_sp;
1164 lock_sleep;
1165 else
1166 raise;
1167 end if;
1168 --
1169 when others then
1170 hr_message.provide_error;
1171 if hr_message.last_message_name = 'HR_7165_OBJECT_LOCKED' and
1172 lock_wait(l_start_time, l_lock_count) then
1173 --
1174 rollback to action_archive_data_sp;
1175 lock_sleep;
1176 else
1177 raise;
1178 end if;
1179 end;
1180 --
1181 end loop;
1182 --
1183 hr_utility.set_location('Leaving: '||l_proc, 100);
1184 --
1185 end action_archive_data;
1186 --
1187 -- ---------------------------------------------------------------------------
1188 -- action_deinit
1189 --
1190 -- Description:
1191 -- Deinitialize the payroll action.
1192 --
1193 -- ---------------------------------------------------------------------------
1194 procedure action_deinit
1195 (p_payroll_action_id in number)
1196 is
1197 --
1198 cursor csr_action_status
1199 is
1200 select 'I'
1201 from dual
1202 where exists
1203 (select null
1204 from pay_assignment_actions paa
1205 where paa.payroll_action_id = p_payroll_action_id
1206 and paa.action_status <> 'C')
1207 union all
1208 select ppa.action_status
1209 from pay_payroll_actions ppa
1210 where ppa.payroll_action_id = p_payroll_action_id
1211 ;
1212 --
1213 l_pact_id number;
1214 l_status varchar2(5);
1215 l_remove_act varchar2(30);
1216 l_count number;
1217 --
1218 l_proc varchar2(72):= g_package||'action_deinit';
1219 begin
1220 --
1221 hr_utility.set_location('Entering: '||l_proc, 5);
1222 --
1223 select
1224 pay_core_utils.get_parameter
1225 ('REMOVE_ACT', legislative_parameters) remove_act
1226 into l_remove_act
1227 from pay_payroll_actions
1228 where payroll_action_id = p_payroll_action_id
1229 ;
1230
1231 open csr_action_status;
1232 fetch csr_action_status into l_status;
1233 close csr_action_status;
1234 --
1235 pay_core_utils.assert_condition
1236 (l_proc||':1'
1237 ,(l_status in ('I','C','E'))
1238 );
1239 --
1240 l_pact_id := p_payroll_action_id;
1241
1242 --
1243 -- Check batch transfer error
1244 --
1245 if g_err_batch_link_id is not null then
1246 --
1247 -- Reset the batch line status that were rolled back.
1248 --
1249 for i in 1..g_batch_links.count loop
1250 --
1251 begin
1252 if g_batch_links(i) = g_err_batch_link_id then
1253 pay_batch_object_status_pkg.set_status
1254 (p_object_type => 'BEL'
1255 ,p_object_id => g_err_batch_link_id
1256 ,p_object_status => 'E'
1257 ,p_payroll_action_id => p_payroll_action_id
1258 );
1259 else
1260 pay_batch_object_status_pkg.set_status
1261 (p_object_type => 'BEL'
1262 ,p_object_id => g_batch_links(i)
1263 ,p_object_status => 'U'
1264 ,p_payroll_action_id => p_payroll_action_id
1265 );
1266 end if;
1267 exception
1268 when others then
1269 -- Because the batch transfer might have failed due to
1270 -- locking, this update could fail in that case.
1271 -- We can ignore errors here.
1272 null;
1273 end;
1274 end loop;
1275 --
1276 end if;
1277
1278 if l_status='C' and nvl(l_remove_act,'Y')='Y' then
1279 --
1280 pay_archive.remove_report_actions(p_payroll_action_id);
1281
1282 --
1283 -- Delete the object status records.
1284 --
1285 delete from pay_batch_object_status
1286 where payroll_action_id = p_payroll_action_id
1287 and object_status in ('P', 'C')
1288 ;
1289 --
1290 -- Unset payroll action id for other status.
1291 --
1292 update pay_batch_object_status
1293 set payroll_action_id = null
1294 where payroll_action_id = p_payroll_action_id
1295 ;
1296 elsif l_status in ('C','I') then
1297 --
1298 -- Update the status for the processing records.
1299 --
1300 update pay_batch_object_status
1301 set object_status = l_status
1302 where payroll_action_id = p_payroll_action_id
1303 and object_status = 'P'
1304 ;
1305 else
1306 --
1307 -- Ensure there is no processing records just in case.
1308 --
1309 select count(1) into l_count
1310 from pay_batch_object_status
1311 where payroll_action_id = p_payroll_action_id
1312 and object_status = 'P'
1313 ;
1314 --
1315 pay_core_utils.assert_condition
1316 (l_proc||':2'
1317 ,(l_count = 0)
1318 );
1319 end if;
1320
1321 --
1322 hr_utility.set_location('Leaving: '||l_proc, 100);
1323 --
1324 end action_deinit;
1325 --
1326
1327 end pay_batch_link_process_pkg;