1 Package BODY PAY_RETRO_UTILS_PKG as
2 /* $Header: pyretutl.pkb 120.8.12010000.1 2008/07/27 23:33:04 appldev ship $ */
3
4 ------------------------------------------------------------------------------
5 --GLOBALS
6 --
7 g_itemkey wf_items.item_key%type;
8 g_package varchar2(80) := 'PAY_RETRO_UTILS_PKG.';
9
10 --g_transaction_id hr_wip_transactions.transaction_id%type;
11 --g_transaction_mode hr_wip_transactions.dml_mode%type;
12
13 -- GLOBAL NAMES OF ITEMS USED IN PAYRETRO WF file pyretwf
14 --
15 gn_cand_asg_list varchar2(240):= 'ASG_CAND_LIST';
16 gn_event_id varchar2(30) := 'EVENT_ID';
17 gn_chg_date varchar2(30) := 'CHG_DATE';
18 gn_retro_sched_date varchar2(30) := 'RETROPAY_PERFORM_DATE';
19 gn_change_desc varchar2(30) := 'CHANGE_DESCRIPTION';
20 an_event_id varchar2(30) := 'EVENT_ID';
21 an_user varchar2(30) := 'USER';
22 an_retro_asg_nums varchar2(30) := 'RETRO_ASG_NUMS';
23 an_col varchar2(30) := 'UPD_COL';
24 an_tab varchar2(30) := 'UPD_TAB';
25 an_asg_id varchar2(30) := 'ASG_ID';
26 an_mst_asg_list varchar2(30) := 'MST_ASG_LIST';
27 an_bg_id varchar2(30) := 'BG_ID';
28
29 g_asg_id number := null;
30 g_leg_code varchar2(15) := null;
31 g_bus_grp varchar2(15) := null;
32 g_legrul_value_out varchar2(40);
33 g_ee_id number := null;
34 g_et_id number := null;
35 g_ef_date date;
36 g_rbus_grp varchar2(15);
37 g_retro_comp_id number;
38
39 ------------------------------------------------------------------------------
40 Type g_temp_tab_type is table of number index by binary_integer;
41 g_temp_tab g_temp_tab_type;
42
43 --SUB FUNCTION, turn input comma-delimeted list in to table
44 FUNCTION string_to_table ( id_list IN VARCHAR2)
45 RETURN g_temp_tab_type
46 IS
47 i NUMBER := 1;
48 l_value VARCHAR2(100) :='';
49 l_pos_of_ith_comma NUMBER := -1;
50 l_pos_last_comma NUMBER := 0;
51 l_temp_table g_temp_tab_type;
52
53 BEGIN
54 <<next_comma_loop>>
55 WHILE l_pos_of_ith_comma <> 0
56 LOOP
57 l_pos_of_ith_comma := nvl(instr(id_list,',',1,i),0);
58
59 -- Take substring between commas or to end if no last comma
60 if (l_pos_of_ith_comma <> 0) then
61 l_value := SUBSTR(id_list,l_pos_last_comma +1,
62 l_pos_of_ith_comma - l_pos_last_comma -1 );
63 else l_value := SUBSTR(id_list,l_pos_last_comma +1);
64 end if;
65 l_temp_table(i) := to_number(l_value);
66 l_pos_last_comma := l_pos_of_ith_comma;
67 i := i + 1;
68
69 l_pos_last_comma := l_pos_of_ith_comma;
70 END LOOP next_comma_loop;
71 RETURN l_temp_table;
72
73 END string_to_table;
74
75 -------------------------------------------------------------------------------
76 -- UTILITY PROCEDURES
77 -------------------------------------------------------------------------------
78 -- ----------------------------------------------------------------------------
79 -- retro_ent_tab_insert --
80 -- This procedure populates the retro_entries table --
81 -- Mostly called from populate_retro_tables procedure --
82 -- --
83 -- ----------------------------------------------------------------------------
84 --
85 /*
86 Procedure retro_ent_tab_insert(
87 p_retro_assignment_id IN NUMBER
88 , p_element_entry_id IN NUMBER
89 , p_reprocess_date IN DATE
90 , p_eff_date IN DATE) is
91 --
92 l_retro_component_id NUMBER;
93 l_proc varchar2(80) := g_package||'.retro_ent_tab_insert';
94
95 Begin
96 hr_utility.set_location(l_proc,10);
97
98
99 l_retro_component_id := get_retro_component_id(
100 p_element_entry_id,
101 p_eff_date);
102
103 hr_utility.set_location(l_proc,20);
104 --
105 INSERT INTO pay_retro_entries
106 ( retro_assignment_id
107 , element_entry_id
108 , reprocess_date
109 , effective_date
110 , retro_component_id
111 )
112 VALUES
113 ( p_retro_assignment_id
114 , p_element_entry_id
115 , p_reprocess_date
116 , p_eff_date
117 , l_retro_component_id
118 );
119 --
120
121 hr_utility.set_location(l_proc,900);
122 End retro_ent_tab_insert;
123 */
124
125 -- ----------------------------------------------------------------------------
126 -- get_creation_status --
127 -- This procedure gets the status value to assign to the newly created --
128 -- retro_assignment. --
129 -- --
130 -- ----------------------------------------------------------------------------
131 --
132 FUNCTION get_creation_status(
133 p_payroll_id IN NUMBER ) return varchar2 is
134 --
135 l_proc varchar2(30) := 'get_creation_status';
136 l_creation_status varchar2(15) := 'P'; --dflt auto-included in next RetroPay run
137
138 -- For 11.5.10 baseline, flag for manual intervention is set at legislative
139 -- level. In the future it will be possible to set this at payroll level
140 cursor csr_manual_flag(cp_pay_id in number) is
141 select lr.rule_mode
142 from pay_all_payrolls_f paa
143 , per_business_groups_perf pbg
144 , pay_legislation_rules lr
145 where paa.business_group_id = pbg.business_group_id
146 and lr.legislation_code = pbg.legislation_code
147 and lr.rule_type = 'RETRO_STATUS_USER_UPD'
148 and paa.payroll_id = cp_pay_id;
149
150 BEGIN
151 -- if payroll has manual_retro_confirm_flag as yes, then status starts
152 -- at D, if not then status is P
153 /*
154 "RETRO_STATUS_USER_UPD", values are
155 Y = YES User is allowed to update status (default will include in next RetroPay)
156 eg create with status A[waiting RetroPay] (user can D[efer])
157 N = NO User is not allowed to update status (default will include in next RetroPay)
158 eg create with status P[ending RetroPay] (user cannot upd)
159 R = REQUIRED User HAS to manually confirm retro_asgs before they will be process
160 eg create with status D[efer] (user has to A[waiting RetroPay])
161 */
162 for val_rec in csr_manual_flag(p_payroll_id) loop
163 if (val_rec.rule_mode = 'Y') then
164 l_creation_status := 'A';
165 elsif (val_rec.rule_mode = 'R') then
166 l_creation_status := 'D';
167 else --N
168 l_creation_status := 'P';
169 end if;
170 end loop;
171
172 return l_creation_status;
173 END;
174
175 -- ----------------------------------------------------------------------------
176 -- retro_asg_tab_insert --
177 -- This procedure populates the retro_assignments table --
178 -- Mostly called from maintain_retro_asg procedure --
179 -- --
180 -- ----------------------------------------------------------------------------
181 --
182 Procedure retro_asg_tab_insert(
183 p_assignment_id IN NUMBER
184 , p_payroll_id IN NUMBER
185 , p_reprocess_date IN DATE
186 , p_start_date IN DATE
187 , p_retro_assignment_id OUT nocopy NUMBER) is
188 --
189 l_proc varchar2(30) := 'retro_asg_tab_insert';
190 l_creation_status varchar2(15);
191 Begin
192 --
193 hr_utility.set_location(l_proc,10);
194 select pay_retro_assignments_s.nextval
195 into p_retro_assignment_id
196 from sys.dual;
197 --
198 l_creation_status := get_creation_status(p_payroll_id);
199
200 INSERT INTO pay_retro_assignments
201 ( retro_assignment_id
202 , assignment_id
203 , reprocess_date
204 , start_date
205 , approval_status
206 , retro_assignment_action_id
207 )
208 VALUES
209 ( p_retro_assignment_id
210 , p_assignment_id
211 , p_reprocess_date
212 , p_start_date
213 , l_creation_status
214 , null
215 );
216 --
217 hr_utility.set_location(l_proc,900);
218 End retro_asg_tab_insert;
219
220 --
221 -- ----------------------------------------------------------------------------
222 -- create_super_retro_asg
223 -- This procedure populates the retro_assignments table --
224 -- This creates a superceding Retro Assignment (if needed) --
225 -- --
226 -- ----------------------------------------------------------------------------
227 procedure create_super_retro_asg(p_asg_id IN NUMBER
228 ,p_payroll_id IN NUMBER
229 ,p_reprocess_date IN DATE
230 ,p_retro_asg_id OUT nocopy NUMBER)
231 is
232 Cursor c_retro_asg (cp_asg NUMBER,
233 p_ret_asg_id number) is
234 SELECT
235 pra.start_date
236 ,pra.retro_assignment_id ret_asg_id
237 ,pra.created_by
238 FROM pay_retro_assignments pra
239 WHERE pra.assignment_id = cp_asg
240 AND pra.retro_assignment_action_id is null
241 AND pra.superseding_retro_asg_id is null
242 AND pra.retro_assignment_id <> p_ret_asg_id
243 AND approval_status in ('P','A','D');
244 --
245 cursor get_unproc(p_ret_asg_id in number)
246 is
247 select pra.retro_assignment_id,
248 pre.element_entry_id,
249 pre.element_type_id,
250 pre.reprocess_date,
251 pre.effective_date,
252 pre.retro_component_id,
253 pre.owner_type,
254 pre.system_reprocess_date,
255 pre.created_by
256 from pay_retro_assignments pra,
257 pay_retro_entries pre
258 where pra.retro_assignment_id = p_ret_asg_id
259 and pra.retro_assignment_id = pre.retro_assignment_id;
260 --
261 l_ret_asg_id number;
262 l_min_reprocess_date date;
263 l_created_by number;
264 begin
265 --
266 l_min_reprocess_date := p_reprocess_date;
267 --
268 retro_asg_tab_insert(
269 p_assignment_id => p_asg_id
270 , p_payroll_id => p_payroll_id
271 , p_reprocess_date => p_reprocess_date
272 , p_start_date => hr_api.g_eot
273 , p_retro_assignment_id => l_ret_asg_id);
274 --
275 for rarec in c_retro_asg(p_asg_id, l_ret_asg_id) loop
276 update pay_retro_assignments
277 set superseding_retro_asg_id = l_ret_asg_id
278 where retro_assignment_id = rarec.ret_asg_id;
279 --
280 update pay_retro_assignments
281 set start_date = rarec.start_date
282 where retro_assignment_id = l_ret_asg_id;
283 --
284 for unprocrec in get_unproc(rarec.ret_asg_id) loop
285 --
286 -- Either update or insert rows to represent those that
287 -- exist on our unproc RA.
288 pay_retro_pkg.maintain_retro_entry(l_ret_asg_id,
289 unprocrec.element_entry_id,
290 unprocrec.element_type_id,
291 unprocrec.reprocess_date,
292 unprocrec.effective_date,
293 unprocrec.retro_component_id,
294 unprocrec.owner_type,
295 unprocrec.system_reprocess_date
296 );
297 -- inherit created_by.
298 update pay_retro_entries
299 set created_by = unprocrec.created_by
300 where retro_assignment_id = l_ret_asg_id
301 and element_entry_id = unprocrec.element_entry_id;
302 --
303 l_min_reprocess_date := least(l_min_reprocess_date,
304 unprocrec.reprocess_date);
305 --
306 end loop;
307 -- remember created_by.
308 l_created_by := rarec.created_by;
309 end loop;
310
311 -- currently created_by is referred by the UI to
312 -- distinguish the system created record, hence
313 -- it has to be inherited.
314 update pay_retro_assignments
315 set reprocess_date = l_min_reprocess_date
316 ,created_by = nvl(l_created_by, created_by)
317 where retro_assignment_id = l_ret_asg_id;
318 --
319 --
320 -- Set out variable.
321 --
322 p_retro_asg_id := l_ret_asg_id;
323 --
324 end create_super_retro_asg;
325 -- ----------------------------------------------------------------------------
326 -- maintain_retro_asg
327 -- This procedure populates the retro_assignments table --
328 -- The reason it is in a different procedure is that it is called from --
329 -- both the event driven retro-notification and the SRS version --
330 -- The method to expunge an existing row may change thus creating an --
331 -- archive for retro-asg --
332 -- --
333 -- ----------------------------------------------------------------------------
334 --
335
336 Procedure maintain_retro_asg(
337 p_asg_id IN NUMBER
338 ,p_payroll_id IN NUMBER
339 ,p_min_date IN DATE
340 ,p_eff_date IN DATE
341 ,p_retro_asg_id OUT nocopy NUMBER) IS
342
343
344 Cursor c_retro_asg (cp_asg NUMBER) is
345 SELECT
346 pra.start_date
347 ,pra.retro_assignment_id ret_asg_id
348 FROM pay_retro_assignments pra
349 WHERE pra.assignment_id = cp_asg
350 AND pra.retro_assignment_action_id is null
351 AND pra.superseding_retro_asg_id is null
352 AND approval_status in ('P','A','D');
353
354
355 l_ret_asg_id NUMBER;
356
357 l_proc varchar2(80) := g_package||'.maintain_retro_asg';
358
359 BEGIN
360 hr_utility.set_location(l_proc,10);
361
362 for exist_retro_asg in c_retro_asg(p_asg_id) loop
363 --just one row, but fetch neatly
364 --
365 l_ret_asg_id := exist_retro_asg.ret_asg_id;
366
367 -- Make sure status is back to unApproved
368 hr_utility.trace('+ RetroAsg exists so update, retro-asg = '||l_ret_asg_id);
369 update PAY_RETRO_ASSIGNMENTS
370 set APPROVAL_STATUS = 'P'
371 where ASSIGNMENT_ID = p_asg_id;
372
373 -- Delete any system retro-element records as were about to repopulate
374 delete from PAY_RETRO_ENTRIES
375 where RETRO_ASSIGNMENT_ID = exist_retro_asg.ret_asg_id
376 and owner_type = 'S';
377
378 end loop;
379
380 if l_ret_asg_id is null then --There was no record for this asg
381
382 -- Create our retro_asg row
383 retro_asg_tab_insert(
384 p_assignment_id => p_asg_id
385 ,p_payroll_id => p_payroll_id
386 ,p_reprocess_date => p_eff_date -- overriden after child
387 -- entries are created in pay_retro_notif
388 ,p_start_date => p_min_date
389 ,p_retro_assignment_id => l_ret_asg_id);
390
391 end if;
392
393 p_retro_asg_id := l_ret_asg_id;
394 hr_utility.set_location(l_proc,900);
395
396 end maintain_retro_asg;
397
398 --
399 -- ----------------------------------------------------------------------------
400 -- --
401 -- get_user --
402 -- This Procedure is called from workflow to derive the user that caused --
403 -- the Retro-Assignments to be created. --
404 -- --
405 -- resultout : not required
406 -- ----------------------------------------------------------------------------
407 --
408 PROCEDURE get_user (itemtype in varchar2,
409 itemkey in varchar2,
410 actid in number,
411 funcmode in varchar2,
412 resultout out nocopy varchar2) is
413 --
414
415 cursor csr_usr_name (cp_usr_id number) is
416 select user_name
417 from fnd_user
418 where user_id = cp_usr_id
419 and sysdate between start_date and nvl(end_date,hr_api.g_eot);
420
421 cursor csr_upd_row_info (cp_ppe_id number) is
422 select pdt.table_name,peu.column_name,
423 pdt.start_date_name,pdt.end_date_name,
424 pdt.surrogate_key_name,ppe.surrogate_key,ppe.effective_date
425 from pay_process_events ppe
426 ,Pay_event_updates peu
427 ,pay_dated_tables pdt
428 where ppe.process_event_id = cp_ppe_id
429 and ppe.event_update_id = peu.event_update_id
430 and peu.dated_table_id = pdt.dated_table_id;
431
432 l_ppe_id pay_process_events.process_event_id%type;
433 l_usr_id fnd_user.user_id%type;
434 l_user fnd_user.user_name%type := 'ANONYMOUS';
435 l_statement varchar2(2000);
436
437 l_table_name varchar2(80);
438 l_column_name varchar2(80);
439 l_sd_name varchar2(80);
440 l_ed_name varchar2(80);
441 l_surr_key_name varchar2(80);
442 l_surr_key varchar2(80);
443 l_eff_date date;
444 --
445 BEGIN
446
447 --
448 l_ppe_id := wf_engine.getItemAttrText
449 (itemtype => itemtype,
450 itemkey => itemkey,
451 aname => an_event_id);
452
453
454 open csr_upd_row_info(l_ppe_id);
455 fetch csr_upd_row_info into l_table_name,l_column_name,l_sd_name,l_ed_name,
456 l_surr_key_name,l_surr_key,l_eff_date;
457 close csr_upd_row_info;
458 --
459 l_statement := 'SELECT last_updated_by' ||
460 ' FROM ' || l_table_name ||
461 ' WHERE ' || l_surr_key_name || ' = '||l_surr_key||
462 ' AND ' || 'to_date('''||
463 to_char(l_eff_date,'DD-MON-RR')
464 ||''',''DD-MON-RR'') '
465 || ' BETWEEN ' || l_sd_name || ' AND ' || l_ed_name ;
466 hr_utility.trace('Col getting Statement ' || l_statement);
467
468 wf_engine.setItemAttrText
469 (itemtype => itemtype,
470 itemkey => itemkey,
471 aname => an_retro_asg_nums,
472 aValue => l_ppe_id||' <- ->'||l_column_name);
473 --execute immediate 'select 1 from dual' into l_usr_id;
474
475 execute immediate l_statement into l_usr_id;
476
477 if (l_usr_id is not null and l_usr_id <> -1) then
478 open csr_usr_name(l_usr_id);
479 fetch csr_usr_name into l_user;
480 close csr_usr_name;
481 end if;
482 hr_utility.trace('User ID, Name: '||l_usr_id||', '||l_user);
483
484 wf_engine.setItemAttrText
485 (itemtype => itemtype,
486 itemkey => itemkey,
487 aname => an_user,
488 aValue => l_user);
489
490 wf_engine.setItemAttrText
491 (itemtype => itemtype,
492 itemkey => itemkey,
493 aname => an_tab,
494 aValue => l_table_name);
495
496 wf_engine.setItemAttrText
497 (itemtype => itemtype,
498 itemkey => itemkey,
499 aname => an_col,
500 aValue => l_column_name);
501
502 return;
503
504 END get_user;
505
506
507 --
508 -- ----------------------------------------------------------------------------
509 -- --
510 -- cc_reqd --
511 -- This Procedure is called from workflow to run the cc process to mark --
512 -- asg for retry, eg event has changed something but no retro-asg created --
513 -- --
514 -- resultout : Yes or No
515 -- ----------------------------------------------------------------------------
516 --
517 PROCEDURE cc_reqd (itemtype in varchar2,
518 itemkey in varchar2,
519 actid in number,
520 funcmode in varchar2,
521 resultout out nocopy varchar2) is
522 --
523 begin
524
525 resultout := 'COMPLETE:Y';
526 return;
527 end cc_reqd;
528
529
530 PROCEDURE is_retropay_scheduled (itemtype in varchar2,
531 itemkey in varchar2,
532 actid in number,
533 funcmode in varchar2,
534 resultout out nocopy varchar2) is
535 --
536 l_proc varchar2(80) := 'is_retropay_scheduled';
537 l_schedule_date date;
538 r_itemtype varchar2(30) := 'PYRETRO';
539 r_itemkey varchar2(30);
540
541 cursor csr_retro_scheduled is
542 select item_key from wf_items
543 where item_type = 'PYRETRO'
544 and root_activity = 'PAY_RETROPAY'
545 AND end_date is null;
546
547 begin
548
549 hr_utility.set_location(g_package||l_proc,10);
550 open csr_retro_scheduled;
551 fetch csr_retro_scheduled into r_itemkey;
552 close csr_retro_scheduled;
553
554 if (r_itemkey is null) then --not currently scheduled
555 resultout := 'COMPLETE:N';
556 else
557 resultout := 'COMPLETE:Y';
558 end if;
559
560 hr_utility.set_location(g_package||l_proc,900);
561 return;
562 end is_retropay_scheduled;
563
564
565 --
566 -- ----------------------------------------------------------------------------
567 -- --
568 -- cc_perform --
569 -- This Procedure is called from workflow to run the cc process to mark --
570 -- asg for retry, eg event has changed something but no retro-asg created --
571 -- --
572 -- resultout : not required
573 -- ----------------------------------------------------------------------------
574 --
575 PROCEDURE cc_perform (itemtype in varchar2,
576 itemkey in varchar2,
577 actid in number,
578 funcmode in varchar2,
579 resultout out nocopy varchar2) is
580 --
581 begin
582
583 null;
584
585 end cc_perform;
586
587 --
588 -- ----------------------------------------------------------------------------
589 -- --
590 -- get_retro_component_id --
591 -- This Function is called during the process to insert the retro_entry --
592 -- A "Recalculation Reason" (or Retro-Component) is need to associate with --
593 -- the entry details. EG What kind of change has required this entry to be--
594 -- recalculated
595 --
596 -- Result: An ID of the seeded retro_component
597 -- ----------------------------------------------------------------------------
598 --
599 FUNCTION get_retro_component_id (
600 p_element_entry_id in number,
601 p_ef_date in date,
602 p_element_type_id in number,
603 p_asg_id in number default NULL) return number IS
604
605 -- Select the default component stored against this element type
606 -- (standard method of getting retro_component_id)
607 --
608
609 cursor csr_get_default_id (cp_et_id in number,
610 cp_ef_date in date,
611 cp_bus_grp in number,
612 cp_leg_code in varchar2) is
613 select prcu.retro_component_id
614 from
615 pay_retro_component_usages prcu
616 where prcu.creator_id = cp_et_id
617 and prcu.creator_type = 'ET'
618 and prcu.default_component = 'Y'
619 and (( prcu.business_group_id = cp_bus_grp
620 and prcu.legislation_code is null)
621 or
622 ( prcu.legislation_code = cp_leg_code
623 and prcu.business_group_id is null)
624 or
625 ( prcu.legislation_code is null
626 and prcu.business_group_id is null)
627 );
628
629 cursor csr_get_asg_id (cp_ee_id in number) IS
630 select distinct pee.assignment_id
631 from pay_element_entries_f pee
635 select distinct paf.business_group_id
632 where pee.element_entry_id = p_element_entry_id;
633
634 cursor csr_get_bg_id (cp_asg_id in number) IS
636 from per_all_assignments_f paf
637 where paf.assignment_id = cp_asg_id;
638
639 cursor csr_get_leg_code (cp_bg_id in number) IS
640 select pbg.legislation_code
641 from per_business_groups_perf pbg
642 where pbg.business_group_id = cp_bg_id;
643
644 l_legrul_name varchar2(40) := 'RETRO_COMP_DFLT_OVERRIDE';
645 l_asg_id number;
646 l_leg_code varchar2(15);
647 l_bus_grp varchar2(15);
648 l_legrul_value_out varchar2(40);
649 l_found_out boolean;
650
651 l_retro_comp_id number := -1;
652 l_sql varchar2(240);
653
654 BEGIN
655 -- The standard way of obtaining the retro-component_id is to look for
656 -- the default value that has been seeded against the element_type.
657 -- If legislations require an alternate method, they can write their own
658 -- procedure, and put a row in pay_legislative pointing at it
659 --
660
661 --Get Asg_id
662 --
663 if p_asg_id is not NULL then
664 l_asg_id := p_asg_id;
665 else
666 OPEN csr_get_asg_id(p_element_entry_id);
667 FETCH csr_get_asg_id INTO l_asg_id;
668 CLOSE csr_get_asg_id;
669 end if;
670
671 if (g_asg_id is not null and
672 l_asg_id = g_asg_id) then
673 l_leg_code := g_leg_code;
674 l_bus_grp := g_bus_grp;
675 l_legrul_value_out := g_legrul_value_out;
676
677 else
678 g_asg_id := l_asg_id;
679
680 --Get Bg_id
681 --
682 OPEN csr_get_bg_id(l_asg_id);
683 FETCH csr_get_bg_id INTO l_bus_grp;
684 CLOSE csr_get_bg_id;
685
686 if (g_bus_grp is not null and
687 l_bus_grp = g_bus_grp) then
688 l_leg_code := g_leg_code;
689 l_legrul_value_out := g_legrul_value_out;
690 else
691 g_bus_grp := l_bus_grp;
692
693 --Get Legislation code
694 --
695 OPEN csr_get_leg_code(l_bus_grp);
696 FETCH csr_get_leg_code INTO l_leg_code;
697 CLOSE csr_get_leg_code;
698
699 g_leg_code := l_leg_code;
700
701 --Look for legislative override
702 pay_core_utils.get_legislation_rule(
703 p_legrul_name => l_legrul_name
704 ,p_legislation => l_leg_code
705 ,p_legrul_value => l_legrul_value_out
706 ,p_found => l_found_out );
707
708 if (l_found_out) then
709 g_legrul_value_out := l_legrul_value_out;
710 else
711 g_legrul_value_out := 'N';
712 end if;
713
714 end if;
715 end if;
716
717 if (l_legrul_value_out = 'Y') then
718 -- This legislation does not want to use the seeded default component id
719 -- but must have delivered an alternate procedure to return the id
720 -- eg PAY_NL_RULES.get_retro_component_id
721
722 if (g_ee_id is not null and
723 p_element_type_id = g_ee_id) then
724 l_retro_comp_id := g_retro_comp_id;
725 else
726
727 --build up sql string
728 l_sql := 'begin PAY_'||l_leg_code||'_RULES.get_retro_component_id( p_ee_id => :l_ee_id'||
729 ', p_element_type_id => :p_et_id'||
730 ', p_retro_component_id => :l_rc_id'||
731 '); end;' ;
732
733 hr_utility.trace(l_sql);
734 execute immediate (l_sql)
735 using in p_element_entry_id, in p_element_type_id, in out l_retro_comp_id;
736
737 g_ee_id := p_element_type_id;
738 g_retro_comp_id := l_retro_comp_id;
739 end if;
740
741 else
742 --Use the original method
743
744 if (g_et_id is not null and
745 p_element_type_id = g_et_id and
746 p_ef_date = g_ef_date and
747 l_bus_grp = g_rbus_grp) then
748 l_retro_comp_id := g_retro_comp_id;
749 else
750
751 open csr_get_default_id(p_element_type_id,p_ef_date, l_bus_grp, l_leg_code);
752 fetch csr_get_default_id into l_retro_comp_id;
753 close csr_get_default_id;
754
755 g_et_id := p_element_type_id;
756 g_ef_date := p_ef_date;
757 g_rbus_grp := l_bus_grp;
758 g_retro_comp_id := l_retro_comp_id;
759 end if;
760 end if;
761 -- hr_utility.trace(' Returned component_id is '||l_retro_comp_id);
762 return l_retro_comp_id;
763 END get_retro_component_id;
764
765
766 END PAY_RETRO_UTILS_PKG;
[Home] [Help]