[Home] [Help]
PACKAGE BODY: APPS.PAY_RETRO_UTILS_PKG
Source
1 Package BODY PAY_RETRO_UTILS_PKG as
2 /* $Header: pyretutl.pkb 120.8.12010000.3 2010/03/18 05:54:39 pgongada 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) := 'A'; --dflt auto-included in next RetroPay run
137 begin
138 hr_utility.set_location('Entering '||l_proc, 10);
139 return l_creation_status;
140 END get_creation_status;
141
142 -- ----------------------------------------------------------------------------
143 -- retro_asg_tab_insert --
144 -- This procedure populates the retro_assignments table --
145 -- Mostly called from maintain_retro_asg procedure --
146 -- --
147 -- ----------------------------------------------------------------------------
148 --
149 Procedure retro_asg_tab_insert(
150 p_assignment_id IN NUMBER
151 , p_payroll_id IN NUMBER
152 , p_reprocess_date IN DATE
153 , p_start_date IN DATE
154 , p_retro_assignment_id OUT nocopy NUMBER) is
155 --
156 l_proc varchar2(30) := 'retro_asg_tab_insert';
157 l_creation_status varchar2(15);
158 Begin
159 --
160 hr_utility.set_location(l_proc,10);
161 select pay_retro_assignments_s.nextval
162 into p_retro_assignment_id
163 from sys.dual;
164 --
165 l_creation_status := get_creation_status(p_payroll_id);
166
167 INSERT INTO pay_retro_assignments
168 ( retro_assignment_id
169 , assignment_id
170 , reprocess_date
171 , start_date
172 , approval_status
173 , retro_assignment_action_id
174 )
175 VALUES
176 ( p_retro_assignment_id
177 , p_assignment_id
178 , p_reprocess_date
179 , p_start_date
180 , l_creation_status
181 , null
182 );
183 --
184 hr_utility.set_location(l_proc,900);
185 End retro_asg_tab_insert;
186
187 --
188 -- ----------------------------------------------------------------------------
189 -- create_super_retro_asg
190 -- This procedure populates the retro_assignments table --
191 -- This creates a superceding Retro Assignment (if needed) --
192 -- --
193 -- ----------------------------------------------------------------------------
194 procedure create_super_retro_asg(p_asg_id IN NUMBER
195 ,p_payroll_id IN NUMBER
196 ,p_reprocess_date IN DATE
197 ,p_retro_asg_id OUT nocopy NUMBER)
198 is
199 Cursor c_retro_asg (cp_asg NUMBER,
200 p_ret_asg_id number) is
201 SELECT
202 pra.start_date
203 ,pra.retro_assignment_id ret_asg_id
204 ,pra.created_by
205 FROM pay_retro_assignments pra
206 WHERE pra.assignment_id = cp_asg
207 AND pra.retro_assignment_action_id is null
208 AND pra.superseding_retro_asg_id is null
209 AND pra.retro_assignment_id <> p_ret_asg_id
210 AND approval_status in ('P','A','D');
211 --
212 cursor get_unproc(p_ret_asg_id in number)
213 is
214 select pra.retro_assignment_id,
215 pre.element_entry_id,
216 pre.element_type_id,
217 pre.reprocess_date,
218 pre.effective_date,
219 pre.retro_component_id,
220 pre.owner_type,
221 pre.system_reprocess_date,
222 pre.created_by
223 from pay_retro_assignments pra,
224 pay_retro_entries pre
225 where pra.retro_assignment_id = p_ret_asg_id
226 and pra.retro_assignment_id = pre.retro_assignment_id;
227 --
228 l_ret_asg_id number;
229 l_min_reprocess_date date;
230 l_created_by number;
231 begin
232 --
233 l_min_reprocess_date := p_reprocess_date;
234 --
235 retro_asg_tab_insert(
236 p_assignment_id => p_asg_id
237 , p_payroll_id => p_payroll_id
238 , p_reprocess_date => p_reprocess_date
239 , p_start_date => hr_api.g_eot
240 , p_retro_assignment_id => l_ret_asg_id);
241 --
242 for rarec in c_retro_asg(p_asg_id, l_ret_asg_id) loop
243 update pay_retro_assignments
244 set superseding_retro_asg_id = l_ret_asg_id
245 where retro_assignment_id = rarec.ret_asg_id;
246 --
247 update pay_retro_assignments
248 set start_date = rarec.start_date
249 where retro_assignment_id = l_ret_asg_id;
250 --
251 for unprocrec in get_unproc(rarec.ret_asg_id) loop
252 --
253 -- Either update or insert rows to represent those that
254 -- exist on our unproc RA.
255 pay_retro_pkg.maintain_retro_entry(l_ret_asg_id,
256 unprocrec.element_entry_id,
257 unprocrec.element_type_id,
258 unprocrec.reprocess_date,
259 unprocrec.effective_date,
260 unprocrec.retro_component_id,
261 unprocrec.owner_type,
262 unprocrec.system_reprocess_date
263 );
264 -- inherit created_by.
265 update pay_retro_entries
266 set created_by = unprocrec.created_by
267 where retro_assignment_id = l_ret_asg_id
268 and element_entry_id = unprocrec.element_entry_id;
269 --
270 l_min_reprocess_date := least(l_min_reprocess_date,
271 unprocrec.reprocess_date);
272 --
273 end loop;
274 -- remember created_by.
275 l_created_by := rarec.created_by;
276 end loop;
277
278 -- currently created_by is referred by the UI to
279 -- distinguish the system created record, hence
280 -- it has to be inherited.
281 update pay_retro_assignments
282 set reprocess_date = l_min_reprocess_date
283 ,created_by = nvl(l_created_by, created_by)
284 where retro_assignment_id = l_ret_asg_id;
285 --
286 --
287 -- Set out variable.
288 --
289 p_retro_asg_id := l_ret_asg_id;
290 --
291 end create_super_retro_asg;
292 -- ----------------------------------------------------------------------------
293 -- maintain_retro_asg
294 -- This procedure populates the retro_assignments table --
295 -- The reason it is in a different procedure is that it is called from --
296 -- both the event driven retro-notification and the SRS version --
297 -- The method to expunge an existing row may change thus creating an --
298 -- archive for retro-asg --
299 -- --
300 -- ----------------------------------------------------------------------------
301 --
302
303 Procedure maintain_retro_asg(
304 p_asg_id IN NUMBER
305 ,p_payroll_id IN NUMBER
306 ,p_min_date IN DATE
307 ,p_eff_date IN DATE
308 ,p_retro_asg_id OUT nocopy NUMBER) IS
309
310
311 Cursor c_retro_asg (cp_asg NUMBER) is
312 SELECT
313 pra.start_date
314 ,pra.retro_assignment_id ret_asg_id
315 FROM pay_retro_assignments pra
316 WHERE pra.assignment_id = cp_asg
317 AND pra.retro_assignment_action_id is null
318 AND pra.superseding_retro_asg_id is null
319 AND approval_status in ('P','A','D');
320
321
322 l_ret_asg_id NUMBER;
323
324 l_proc varchar2(80) := g_package||'.maintain_retro_asg';
325
326 BEGIN
327 hr_utility.set_location(l_proc,10);
328
329 for exist_retro_asg in c_retro_asg(p_asg_id) loop
330 --just one row, but fetch neatly
331 --
332 l_ret_asg_id := exist_retro_asg.ret_asg_id;
333
334 -- Make sure status is back to unApproved
335 hr_utility.trace('+ RetroAsg exists so update, retro-asg = '||l_ret_asg_id);
336 update PAY_RETRO_ASSIGNMENTS
337 set APPROVAL_STATUS = 'P'
338 where ASSIGNMENT_ID = p_asg_id
339 /*Bug#8306525*/
340 and RETRO_ASSIGNMENT_ACTION_ID IS NULL;
341
342
343 -- Delete any system retro-element records as were about to repopulate
344 delete from PAY_RETRO_ENTRIES
345 where RETRO_ASSIGNMENT_ID = exist_retro_asg.ret_asg_id
346 and owner_type = 'S';
347
348 end loop;
349
350 if l_ret_asg_id is null then --There was no record for this asg
351
352 -- Create our retro_asg row
353 retro_asg_tab_insert(
354 p_assignment_id => p_asg_id
355 ,p_payroll_id => p_payroll_id
356 ,p_reprocess_date => p_eff_date -- overriden after child
357 -- entries are created in pay_retro_notif
358 ,p_start_date => p_min_date
359 ,p_retro_assignment_id => l_ret_asg_id);
360
361 end if;
362
363 p_retro_asg_id := l_ret_asg_id;
364 hr_utility.set_location(l_proc,900);
365
366 end maintain_retro_asg;
367
368 --
369 -- ----------------------------------------------------------------------------
370 -- --
371 -- get_user --
372 -- This Procedure is called from workflow to derive the user that caused --
373 -- the Retro-Assignments to be created. --
374 -- --
375 -- resultout : not required
376 -- ----------------------------------------------------------------------------
377 --
378 PROCEDURE get_user (itemtype in varchar2,
379 itemkey in varchar2,
380 actid in number,
381 funcmode in varchar2,
382 resultout out nocopy varchar2) is
383 --
384
385 cursor csr_usr_name (cp_usr_id number) is
386 select user_name
387 from fnd_user
388 where user_id = cp_usr_id
389 and sysdate between start_date and nvl(end_date,hr_api.g_eot);
390
391 cursor csr_upd_row_info (cp_ppe_id number) is
392 select pdt.table_name,peu.column_name,
393 pdt.start_date_name,pdt.end_date_name,
394 pdt.surrogate_key_name,ppe.surrogate_key,ppe.effective_date
395 from pay_process_events ppe
396 ,Pay_event_updates peu
397 ,pay_dated_tables pdt
398 where ppe.process_event_id = cp_ppe_id
399 and ppe.event_update_id = peu.event_update_id
400 and peu.dated_table_id = pdt.dated_table_id;
401
402 l_ppe_id pay_process_events.process_event_id%type;
403 l_usr_id fnd_user.user_id%type;
404 l_user fnd_user.user_name%type := 'ANONYMOUS';
405 l_statement varchar2(2000);
406
407 l_table_name varchar2(80);
408 l_column_name varchar2(80);
409 l_sd_name varchar2(80);
410 l_ed_name varchar2(80);
411 l_surr_key_name varchar2(80);
412 l_surr_key varchar2(80);
413 l_eff_date date;
414 --
415 BEGIN
416
417 --
418 l_ppe_id := wf_engine.getItemAttrText
419 (itemtype => itemtype,
420 itemkey => itemkey,
421 aname => an_event_id);
422
423
424 open csr_upd_row_info(l_ppe_id);
425 fetch csr_upd_row_info into l_table_name,l_column_name,l_sd_name,l_ed_name,
426 l_surr_key_name,l_surr_key,l_eff_date;
427 close csr_upd_row_info;
428 --
429 l_statement := 'SELECT last_updated_by' ||
430 ' FROM ' || l_table_name ||
431 ' WHERE ' || l_surr_key_name || ' = '||l_surr_key||
432 ' AND ' || 'to_date('''||
433 to_char(l_eff_date,'DD-MON-RR')
434 ||''',''DD-MON-RR'') '
435 || ' BETWEEN ' || l_sd_name || ' AND ' || l_ed_name ;
436 hr_utility.trace('Col getting Statement ' || l_statement);
437
438 wf_engine.setItemAttrText
439 (itemtype => itemtype,
440 itemkey => itemkey,
441 aname => an_retro_asg_nums,
442 aValue => l_ppe_id||' <- ->'||l_column_name);
443 --execute immediate 'select 1 from dual' into l_usr_id;
444
445 execute immediate l_statement into l_usr_id;
446
447 if (l_usr_id is not null and l_usr_id <> -1) then
448 open csr_usr_name(l_usr_id);
449 fetch csr_usr_name into l_user;
450 close csr_usr_name;
451 end if;
452 hr_utility.trace('User ID, Name: '||l_usr_id||', '||l_user);
453
454 wf_engine.setItemAttrText
455 (itemtype => itemtype,
456 itemkey => itemkey,
457 aname => an_user,
458 aValue => l_user);
459
460 wf_engine.setItemAttrText
461 (itemtype => itemtype,
462 itemkey => itemkey,
463 aname => an_tab,
464 aValue => l_table_name);
465
466 wf_engine.setItemAttrText
467 (itemtype => itemtype,
468 itemkey => itemkey,
469 aname => an_col,
470 aValue => l_column_name);
471
472 return;
473
474 END get_user;
475
476
477 --
478 -- ----------------------------------------------------------------------------
479 -- --
480 -- cc_reqd --
481 -- This Procedure is called from workflow to run the cc process to mark --
482 -- asg for retry, eg event has changed something but no retro-asg created --
483 -- --
484 -- resultout : Yes or No
485 -- ----------------------------------------------------------------------------
486 --
487 PROCEDURE cc_reqd (itemtype in varchar2,
488 itemkey in varchar2,
489 actid in number,
490 funcmode in varchar2,
491 resultout out nocopy varchar2) is
492 --
493 begin
494
495 resultout := 'COMPLETE:Y';
496 return;
497 end cc_reqd;
498
499
500 PROCEDURE is_retropay_scheduled (itemtype in varchar2,
501 itemkey in varchar2,
502 actid in number,
503 funcmode in varchar2,
504 resultout out nocopy varchar2) is
505 --
506 l_proc varchar2(80) := 'is_retropay_scheduled';
507 l_schedule_date date;
508 r_itemtype varchar2(30) := 'PYRETRO';
509 r_itemkey varchar2(30);
510
511 cursor csr_retro_scheduled is
512 select item_key from wf_items
513 where item_type = 'PYRETRO'
514 and root_activity = 'PAY_RETROPAY'
515 AND end_date is null;
516
517 begin
518
519 hr_utility.set_location(g_package||l_proc,10);
520 open csr_retro_scheduled;
521 fetch csr_retro_scheduled into r_itemkey;
522 close csr_retro_scheduled;
523
524 if (r_itemkey is null) then --not currently scheduled
525 resultout := 'COMPLETE:N';
526 else
527 resultout := 'COMPLETE:Y';
528 end if;
529
530 hr_utility.set_location(g_package||l_proc,900);
531 return;
532 end is_retropay_scheduled;
533
534
535 --
536 -- ----------------------------------------------------------------------------
537 -- --
538 -- cc_perform --
539 -- This Procedure is called from workflow to run the cc process to mark --
540 -- asg for retry, eg event has changed something but no retro-asg created --
541 -- --
542 -- resultout : not required
543 -- ----------------------------------------------------------------------------
544 --
545 PROCEDURE cc_perform (itemtype in varchar2,
546 itemkey in varchar2,
547 actid in number,
548 funcmode in varchar2,
549 resultout out nocopy varchar2) is
550 --
551 begin
552
553 null;
554
555 end cc_perform;
556
557 --
558 -- ----------------------------------------------------------------------------
559 -- --
560 -- get_retro_component_id --
561 -- This Function is called during the process to insert the retro_entry --
562 -- A "Recalculation Reason" (or Retro-Component) is need to associate with --
563 -- the entry details. EG What kind of change has required this entry to be--
564 -- recalculated
565 --
566 -- Result: An ID of the seeded retro_component
567 -- ----------------------------------------------------------------------------
568 --
569 FUNCTION get_retro_component_id (
570 p_element_entry_id in number,
571 p_ef_date in date,
572 p_element_type_id in number,
573 p_asg_id in number default NULL) return number IS
574
575 -- Select the default component stored against this element type
576 -- (standard method of getting retro_component_id)
577 --
578
579 cursor csr_get_default_id (cp_et_id in number,
580 cp_ef_date in date,
581 cp_bus_grp in number,
582 cp_leg_code in varchar2) is
583 select prcu.retro_component_id
584 from
585 pay_retro_component_usages prcu
586 where prcu.creator_id = cp_et_id
587 and prcu.creator_type = 'ET'
588 and prcu.default_component = 'Y'
589 and (( prcu.business_group_id = cp_bus_grp
590 and prcu.legislation_code is null)
591 or
592 ( prcu.legislation_code = cp_leg_code
593 and prcu.business_group_id is null)
594 or
595 ( prcu.legislation_code is null
596 and prcu.business_group_id is null)
597 );
598
599 cursor csr_get_asg_id (cp_ee_id in number) IS
600 select distinct pee.assignment_id
601 from pay_element_entries_f pee
602 where pee.element_entry_id = p_element_entry_id;
603
604 cursor csr_get_bg_id (cp_asg_id in number) IS
605 select distinct paf.business_group_id
606 from per_all_assignments_f paf
607 where paf.assignment_id = cp_asg_id;
608
609 cursor csr_get_leg_code (cp_bg_id in number) IS
610 select pbg.legislation_code
611 from per_business_groups_perf pbg
612 where pbg.business_group_id = cp_bg_id;
613
614 l_legrul_name varchar2(40) := 'RETRO_COMP_DFLT_OVERRIDE';
615 l_asg_id number;
616 l_leg_code varchar2(15);
617 l_bus_grp varchar2(15);
618 l_legrul_value_out varchar2(40);
619 l_found_out boolean;
620
621 l_retro_comp_id number := -1;
622 l_sql varchar2(240);
623
624 BEGIN
625 -- The standard way of obtaining the retro-component_id is to look for
626 -- the default value that has been seeded against the element_type.
627 -- If legislations require an alternate method, they can write their own
628 -- procedure, and put a row in pay_legislative pointing at it
629 --
630
631 --Get Asg_id
632 --
633 if p_asg_id is not NULL then
634 l_asg_id := p_asg_id;
635 else
636 OPEN csr_get_asg_id(p_element_entry_id);
637 FETCH csr_get_asg_id INTO l_asg_id;
638 CLOSE csr_get_asg_id;
639 end if;
640
641 if (g_asg_id is not null and
642 l_asg_id = g_asg_id) then
643 l_leg_code := g_leg_code;
644 l_bus_grp := g_bus_grp;
645 l_legrul_value_out := g_legrul_value_out;
646
647 else
648 g_asg_id := l_asg_id;
649
650 --Get Bg_id
651 --
652 OPEN csr_get_bg_id(l_asg_id);
653 FETCH csr_get_bg_id INTO l_bus_grp;
654 CLOSE csr_get_bg_id;
655
656 if (g_bus_grp is not null and
657 l_bus_grp = g_bus_grp) then
658 l_leg_code := g_leg_code;
659 l_legrul_value_out := g_legrul_value_out;
660 else
661 g_bus_grp := l_bus_grp;
662
663 --Get Legislation code
664 --
665 OPEN csr_get_leg_code(l_bus_grp);
666 FETCH csr_get_leg_code INTO l_leg_code;
667 CLOSE csr_get_leg_code;
668
669 g_leg_code := l_leg_code;
670
671 --Look for legislative override
672 pay_core_utils.get_legislation_rule(
673 p_legrul_name => l_legrul_name
674 ,p_legislation => l_leg_code
675 ,p_legrul_value => l_legrul_value_out
676 ,p_found => l_found_out );
677
678 if (l_found_out) then
679 g_legrul_value_out := l_legrul_value_out;
680 else
681 g_legrul_value_out := 'N';
682 end if;
683
684 end if;
685 end if;
686
687 if (l_legrul_value_out = 'Y') then
688 -- This legislation does not want to use the seeded default component id
689 -- but must have delivered an alternate procedure to return the id
690 -- eg PAY_NL_RULES.get_retro_component_id
691
692 if (g_ee_id is not null and
693 p_element_type_id = g_ee_id) then
694 l_retro_comp_id := g_retro_comp_id;
695 else
696
697 --build up sql string
698 l_sql := 'begin PAY_'||l_leg_code||'_RULES.get_retro_component_id( p_ee_id => :l_ee_id'||
699 ', p_element_type_id => :p_et_id'||
700 ', p_retro_component_id => :l_rc_id'||
701 '); end;' ;
702
703 hr_utility.trace(l_sql);
704 execute immediate (l_sql)
705 using in p_element_entry_id, in p_element_type_id, in out l_retro_comp_id;
706
707 g_ee_id := p_element_type_id;
708 g_retro_comp_id := l_retro_comp_id;
709 end if;
710
711 else
712 --Use the original method
713
714 if (g_et_id is not null and
715 p_element_type_id = g_et_id and
716 p_ef_date = g_ef_date and
717 l_bus_grp = g_rbus_grp) then
718 l_retro_comp_id := g_retro_comp_id;
719 else
720
721 open csr_get_default_id(p_element_type_id,p_ef_date, l_bus_grp, l_leg_code);
722 fetch csr_get_default_id into l_retro_comp_id;
723 close csr_get_default_id;
724
725 g_et_id := p_element_type_id;
726 g_ef_date := p_ef_date;
727 g_rbus_grp := l_bus_grp;
728 g_retro_comp_id := l_retro_comp_id;
729 end if;
730 end if;
731 -- hr_utility.trace(' Returned component_id is '||l_retro_comp_id);
732 return l_retro_comp_id;
733 END get_retro_component_id;
734
735
736 END PAY_RETRO_UTILS_PKG;