[Home] [Help]
PACKAGE BODY: APPS.PAY_SIMULATOR_PKG
Source
1 PACKAGE BODY pay_simulator_pkg AS
2 /* $Header: paysim.pkb 120.1.12020000.1 2013/03/20 08:41:52 kskoduri noship $
3 ------------------------------------------------------------------------------
4 +==============================================================================+
5 | Copyright (c) 2013 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==============================================================================+
9 Name
10 Payroll Simulation Package
11 Purpose
12 This package handles the updating of the element entries with the user
13 provided what-if values .This procedure also provides procedures for the
14 archival of simulation information etc
15 History
16 04 Feb 2013 kskoduri Created
17 20 Mar 2013 kskoduri Included all the changes from 12.1 Branch
18 to enable dual maintenance
19 */
20 --
21 --
22 g_dbg BOOLEAN := TRUE; --Used for diagnosing issues by dev, more outputs
23 gv_package VARCHAR2(100) := 'pay_simulator_pkg';
24 --
25 PROCEDURE check_entry_exists
26 (
27 p_ele_typ_id IN VARCHAR2,
28 p_ass_id IN NUMBER,
29 p_start_date IN DATE,
30 p_end_date IN DATE,
31 p_exists OUT NOCOPY BOOLEAN
32 ) IS
33 v_dummy VARCHAR2(1);
34 BEGIN
35 p_exists := FALSE;
36
37 SELECT '1'
38 INTO v_dummy
39 FROM DUAL
40 WHERE exists (
41 SELECT '1'
42 FROM pay_element_entries_f peef
43 WHERE peef.assignment_id = p_ass_id
44 AND peef.element_type_id =p_ele_typ_id
45 AND p_start_date <= peef.effective_end_date
46 AND p_end_date >= peef.effective_start_date
47 );
48
49 p_exists := TRUE;
50
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 p_exists := FALSE;
54 END check_entry_exists;
55 --
56 --
57 /*
58 This function retrieves the id of the exclusion element for which the
59 */
60 FUNCTION get_element_set_id
61 (
62 p_business_group_id IN number
63 )RETURN number IS
64
65 CURSOR c_doc_type is
66 select application_column_name
67 FROM fnd_descr_flex_column_usages
68 WHERE descriptive_flexfield_name = 'Org Developer DF'
69 AND descriptive_flex_context_code = 'HR_SELF_SERVICE_BG_PREFERENCE'
70 AND end_user_column_name = 'Document Type';
71 l_doc_type_col fnd_descr_flex_column_usages.application_column_name%TYPE;
72
73 CURSOR c_element_set_column_name IS
74 SELECT application_column_name
75 FROM fnd_descr_flex_column_usages
76 WHERE descriptive_flexfield_name = 'Org Developer DF'
77 AND descriptive_flex_context_code = 'HR_SELF_SERVICE_BG_PREFERENCE'
78 AND end_user_column_name = 'Modeling Exclusion Element Set';
79 l_column_name fnd_descr_flex_column_usages.application_column_name%TYPE;
80 c_ref sys_refcursor;
81 l_element_set_id NUMBER;
82
83
84 BEGIN
85 OPEN c_doc_type;
86 fetch c_doc_type into l_doc_type_col;
87 close c_doc_type;
88
89 OPEN c_element_set_column_name;
90
91 FETCH c_element_set_column_name
92 INTO l_column_name;
93
94 CLOSE c_element_set_column_name;
95
96 IF l_column_name IS NULL THEN
97 RETURN NULL;
98 END IF;
99
100 OPEN c_ref
101 FOR 'select '
102 || l_column_name
103 || ' from hr_organization_information
104 where organization_id = '
105 || p_business_group_id
106 || ' and org_information_context = ''HR_SELF_SERVICE_BG_PREFERENCE'''
107 || ' and '||l_doc_type_col||' = ''SIMPAYSLIP''';
108
109 FETCH c_ref
110 INTO l_element_set_id;
111
112 CLOSE c_ref;
113
114 RETURN l_element_set_id;
115 END get_element_set_id;
116 --
117 --
118 /*
119 This function checks whether the given element is a replacement for an element whose
120 multiple_entries_allowed flag is set as Y or whether it is a direct element
121 */
122 FUNCTION check_if_multi_entry_elem
123 (
124 p_ele_typ_id IN NUMBER
125 )RETURN BOOLEAN IS
126 v_original_element pay_element_types_f.element_type_id%TYPE;
127 lv_procedure_name VARCHAR2(100) := '.check_if_multi_entry_elem';
128 BEGIN
129
130 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
131
132 SELECT eei_information17 original_element
133 INTO v_original_element
134 FROM pay_element_type_extra_info
135 WHERE element_type_id = p_ele_typ_id
136 AND information_type = 'SIMULATION_ELEMENTS'
137 AND eei_information1 = 'Y'
138 ;
139
140 IF (v_original_element IS NOT NULL) THEN
141 hr_utility.trace('The original element type is et_id='||v_original_element);
142 RETURN TRUE;
143 ELSE
144 hr_utility.trace('It is the direct element case');
145 RETURN FALSE;
146 END IF ;
147
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 RETURN FALSE;
151 END check_if_multi_entry_elem;
152 --
153 --
154 FUNCTION is_multiple_entries_allowed
155 (
156 p_ele_typ_id IN NUMBER,
157 p_eff_date IN DATE
158 )RETURN BOOLEAN IS
159 v_mul_allowed pay_element_types_f.multiple_entries_allowed_flag%TYPE;
160 lv_procedure_name VARCHAR2(100) := '.is_multiple_entries_allowed';
161 BEGIN
162
163 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
164
165 SELECT multiple_entries_allowed_flag
166 INTO v_mul_allowed
167 FROM pay_element_types_f
168 WHERE element_type_id = p_ele_typ_id
169 AND p_eff_date BETWEEN effective_start_date and effective_end_date;
170
171 hr_utility.trace('Multiple Entry Allowed Flag allowed is '||v_mul_allowed);
172
173 IF (v_mul_allowed = 'Y') THEN
174 RETURN TRUE;
175 ELSIF (v_mul_allowed = 'N') THEN
176 RETURN FALSE;
177 ELSE
178 RETURN FALSE;
179 END IF ;
180
181 END is_multiple_entries_allowed;
182 --
183 --
184 PROCEDURE insert_new_element_entry
185 (
186 p_start_date DATE,
187 p_end_date DATE,
188 p_assignment_id NUMBER,
189 p_element_type_id NUMBER,
190 p_num_ent_vals NUMBER,
191 p_inp_val_id_tbl hr_entry.number_table,
192 p_ent_val_tbl hr_entry.varchar2_table
193 ) IS
194 v_ele_link_id pay_element_links_f.element_link_id%TYPE;
195 v_new_ee_id pay_element_entries_f.element_entry_id%TYPE;
196 v_start_date DATE;
197 v_end_date DATE;
198 lv_procedure_name VARCHAR2(100) := '.insert_new_element_entry';
199 emesg VARCHAR2(2000);
200 elem_name VARCHAR2(100);
201 BEGIN
202 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
203 hr_utility.trace('The Element Type id is '||p_element_type_id);
204 hr_utility.trace('The Assignment id is '||p_assignment_id);
205 hr_utility.trace('The start date is '||to_char(p_start_date));
206 hr_utility.trace('The end date is '||to_char(p_end_date));
207
208 v_start_date := p_start_date ;
209 /*v_end_date := p_end_date;*/
210 v_ele_link_id := hr_entry_api.get_link(p_assignment_id,p_element_type_id,p_start_date);
211
212 /*to check the usage the insert api and see what are the dates to be passed to the api*/
213
214 IF v_ele_link_id IS NOT NULL THEN
215 hr_utility.trace('The element link id is link_id='||v_ele_link_id);
216 hr_entry_api.insert_element_entry(
217 p_effective_start_date => v_start_date,
218 p_effective_end_date => v_end_date,
219 p_element_entry_id => v_new_ee_id,
220 p_assignment_id => p_assignment_id,
221 p_element_link_id => v_ele_link_id,
222 p_creator_type => 'F',
223 p_entry_type => 'E',
224 p_num_entry_values => p_num_ent_vals,
225 p_input_value_id_tbl => p_inp_val_id_tbl,
226 p_entry_value_tbl => p_ent_val_tbl);
227 ELSE
228 hr_utility.trace('Unable to find the element link raise error');
229 END IF ;
230
231 hr_utility.trace('The Newly created Element Entry id is '||v_new_ee_id);
232 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
233
234 EXCEPTION
235
236 WHEN OTHERS THEN
237 hr_utility.trace('Exception occurred in'||gv_package||lv_procedure_name);
238 IF p_element_type_id IS NOT NULL THEN
239
240 emesg := SQLERRM;
241 insert_error_message(
242 p_assignment_id
243 ,'ERROR'
244 ,p_assignment_id
245 ,p_end_date
246 ,emesg
247 );
248
249 SELECT nvl (nvl(eit.eei_information18
250 ,petl.reporting_name)
251 ,petl.element_name)
252 INTO elem_name
253 FROM pay_element_type_extra_info eit
254 ,pay_element_types_f pet
255 ,pay_element_types_f_tl petl
256 WHERE eit.information_type = 'SIMULATION_ELEMENTS'
257 AND eit.eei_information1 = 'Y'
258 AND eit.element_type_id = pet.element_type_id
259 AND pet.element_type_id = p_element_type_id
260 AND pet.element_type_id = petl.element_type_id
261 AND petl.language = userenv ('LANG')
262 AND p_end_date BETWEEN pet.effective_start_date
263 AND pet.effective_end_date
264 ;
265
266 hr_utility.set_message(801, 'HR_51119_HRPROC_ERR_OCC_ON_ET');
267 hr_utility.set_message_token('ETNAME',elem_name);
268 hr_utility.raise_error;
269
270 END IF;
271 raise;
272
273 END insert_new_element_entry;
274 --
275 --
276 PROCEDURE handle_multi_entry_case
277 (
278 p_start_date DATE,
279 p_end_date DATE,
280 p_assignment_id NUMBER,
281 p_element_type_id NUMBER,
282 p_num_ent_vals NUMBER,
283 p_inp_val_id_tbl hr_entry.number_table,
284 p_ent_val_tbl hr_entry.varchar2_table
285 ) IS
286 v_original_element pay_element_types_f.element_type_id%TYPE;
287 v_exists BOOLEAN;
288 lv_procedure_name VARCHAR2(100) := '.handle_multi_entry_case';
289
290 CURSOR csr_orig_ent_del(p_assignment_id NUMBER,
291 p_element_type_id NUMBER,
292 p_start_date DATE,
293 p_end_date DATE)
294 IS
295 SELECT element_entry_id,effective_start_date,effective_end_date
296 FROM pay_element_entries_f
297 WHERE assignment_id = p_assignment_id
298 AND element_type_id = p_element_type_id
299 AND p_start_date <= effective_end_date
300 AND p_end_date >= effective_start_date
301 ;
302 BEGIN
303 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
304
305 SELECT eei_information17 original_element
306 INTO v_original_element
307 FROM pay_element_type_extra_info
308 WHERE element_type_id = p_element_type_id
309 AND information_type = 'SIMULATION_ELEMENTS'
310 AND eei_information1 = 'Y'
311 ;
312
313 /*Deleting all the entries of the original element */
314 FOR ele_ent IN csr_orig_ent_del (p_assignment_id,v_original_element,p_start_date,p_end_date) LOOP
315
316 hr_utility.trace('Deleting the element entry elem_ent_id='||ele_ent.element_entry_id);
317
318 hr_entry_api.delete_element_entry(
319 p_dt_delete_mode => hr_api.g_zap,
320 p_session_date => ele_ent.effective_end_date,
321 p_element_entry_id => ele_ent.element_entry_id
322 );
323 END LOOP;
324
325 /*Do extra check that the entry to be created already exists
326 These elements created for handling multiple entries during simulation should not be used
327 by the customer in normal payroll runs */
328 check_entry_exists
329 (
330 p_element_type_id,
331 p_assignment_id,
332 p_start_date,
333 p_end_date,
334 v_exists
335 );
336
337 IF(v_exists) THEN
338 hr_utility.trace('Simulation entry already being used in element entries et_id='||p_element_type_id);
339 RETURN;
340 END IF;
341
342 /*Now create an entry for the element type configured for simulation*/
343 insert_new_element_entry (
344 p_start_date => p_start_date,
345 p_end_date => p_end_date,
346 p_assignment_id => p_assignment_id,
347 p_element_type_id => p_element_type_id,
348 p_num_ent_vals => p_num_ent_vals,
349 p_inp_val_id_tbl => p_inp_val_id_tbl,
350 p_ent_val_tbl => p_ent_val_tbl);
351
352
353 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
354
355 END handle_multi_entry_case;
356 --
357 --
358 PROCEDURE del_retro_entries
359 (
360 p_assignment_id NUMBER,
361 p_start_date DATE,
362 p_end_date DATE
363 ) IS
364 lv_procedure_name VARCHAR2(100) := '.del_retro_entries';
365
366 CURSOR csr_retro_entries(p_assignment_id NUMBER,
367 p_start_date DATE,
368 p_end_date DATE)
369 IS
370 SELECT element_entry_id,effective_start_date,effective_end_date
371 FROM pay_element_entries_f
372 WHERE assignment_id = p_assignment_id
373 AND p_start_date <= effective_end_date
374 AND p_end_date >= effective_start_date
375 AND CREATOR_TYPE IN ('RR','NR','EE','PR','R','P')
376 ;
377 BEGIN
378 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
379
380 FOR ele_ent IN csr_retro_entries(p_assignment_id,p_start_date,p_end_date) LOOP
381
382 hr_utility.trace('Retro entry to be deleted is ee_id='||ele_ent.element_entry_id);
383 hr_entry_api.delete_element_entry(
384 p_dt_delete_mode => hr_api.g_zap,
385 p_session_date => ele_ent.effective_end_date,
386 p_element_entry_id => ele_ent.element_entry_id
387 );
388 END LOOP;
389
390
391 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
392
393 END del_retro_entries;
394 --
395 --
396 PROCEDURE del_excluded_entries
397 (
398 p_assignment_id NUMBER,
399 p_start_date DATE,
400 p_end_date DATE,
401 p_business_grp_id NUMBER
402 ) IS
403 lv_procedure_name VARCHAR2(100) := '.del_excluded_entries';
404 l_elemset_id NUMBER;
405
406 CURSOR csr_excl_entries(p_elemset_id NUMBER,
407 p_start_date DATE,
408 p_end_date DATE)
409 IS
410 SELECT element_entry_id
411 ,effective_start_date
412 ,effective_end_date
413 FROM pay_element_entries_f peef
414 ,pay_element_set_members pesm
415 WHERE assignment_id = p_assignment_id
416 AND pesm.element_set_id = p_elemset_id
417 AND peef.element_type_id = pesm.element_type_id
418 AND p_start_date <= effective_end_date
419 AND p_end_date >= effective_start_date
420 ;
421 BEGIN
422 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
423
424 l_elemset_id := get_element_set_id(p_business_grp_id);
425 hr_utility.trace('The exclusion element set id is '||l_elemset_id);
426
427 FOR ele_ent IN csr_excl_entries(l_elemset_id,p_start_date,p_end_date) LOOP
428
429 hr_utility.trace('Excluded entry to be deleted is ee_id='||ele_ent.element_entry_id);
430 hr_entry_api.delete_element_entry(
431 p_dt_delete_mode => hr_api.g_zap,
432 p_session_date => ele_ent.effective_end_date,
433 p_element_entry_id => ele_ent.element_entry_id
434 );
435 END LOOP;
436
437
438 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
439
440 END del_excluded_entries;
441 --
442 --
443 PROCEDURE del_adj_add_entries
444 (
445 p_element_type_id NUMBER,
446 p_assignment_id NUMBER,
447 p_start_date DATE,
448 p_end_date DATE
449 ) IS
450 lv_procedure_name VARCHAR2(100) := '.del_adj_add_entries';
451
452 CURSOR csr_adj_add_entries(p_assignment_id NUMBER,
453 p_element_type_id NUMBER,
454 p_start_date DATE,
455 p_end_date DATE)
456 IS
457 SELECT element_entry_id,effective_start_date,effective_end_date
458 FROM pay_element_entries_f
459 WHERE assignment_id = p_assignment_id
460 AND element_type_id = p_element_type_id
461 AND p_start_date <= effective_end_date
462 AND p_end_date >= effective_start_date
463 AND ENTRY_TYPE IN ('A','D','R','S')
464 ;
465 BEGIN
466 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
467
468 FOR ele_ent IN csr_adj_add_entries(p_assignment_id,p_element_type_id,p_start_date,p_end_date) LOOP
469
470 hr_utility.trace('Adjustment entry to be deleted is ee_id='||ele_ent.element_entry_id);
471 hr_entry_api.delete_element_entry(
472 p_dt_delete_mode => hr_api.g_zap,
473 p_session_date => ele_ent.effective_end_date,
474 p_element_entry_id => ele_ent.element_entry_id
475 );
476 END LOOP;
477
478
479 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
480
481 END del_adj_add_entries;
482 --
483 /*This procedure updates the Element Entries of a given assignment with the what if values provided
484 by the user.
485 */
486 --
487 PROCEDURE update_element_entries
488 (
489 p_assignment_id NUMBER,
490 p_period_start_date DATE,
491 p_period_end_date DATE
492 ) IS
493
494 v_value VARCHAR2(40);
495 v_num_inp_val_mod NUMBER :=0; /*variable used to identify the number of input values modified*/
496 v_inp_val_id VARCHAR2(40);
497 v_exists BOOLEAN;
498 scr_valuetbl hr_entry.varchar2_table;
499 inp_value_id_tbl hr_entry.number_table;
500 v_legislation per_business_groups_perf.legislation_code%TYPE;
501
502 v_sim_region3 VARCHAR2(30);
503 v_sim_region4 VARCHAR2(30);
504 lv_procedure_name VARCHAR2(100) := '.update_element_entries';
505
506
507 CURSOR csr_simulation_elements(p_ass_id NUMBER, p_sim_region3 VARCHAR2, p_sim_region4 VARCHAR2)
508 IS
509 SELECT DISTINCT
510 piv.element_type_id
511 FROM per_assignment_extra_info pae,
512 pay_input_values_f piv
513 WHERE pae.assignment_id = p_ass_id
514 AND pae.information_type IN (p_sim_region3,p_sim_region4)
515 AND piv.input_value_id = pae.aei_information1
516 ;
517
518 CURSOR csr_sim_entry_values(p_ass_id NUMBER, p_sim_region3 VARCHAR2, p_sim_region4 VARCHAR2, p_ele_type_id NUMBER)
519 IS
520 SELECT DISTINCT
521 pae.aei_information1 input_value_id,
522 pae.aei_information2 what_if_value
523 FROM per_assignment_extra_info pae,
524 pay_input_values_f piv
525 WHERE pae.assignment_id = p_ass_id
526 AND pae.information_type IN (p_sim_region3,p_sim_region4)
527 AND piv.input_value_id = pae.aei_information1
528 AND piv.element_type_id = p_ele_type_id
529 ;
530
531 CURSOR csr_element_entries(p_assignment_id NUMBER,
532 p_element_type_id NUMBER,
533 p_start_date DATE,
534 p_end_date DATE)
535 IS
536 SELECT element_entry_id,effective_start_date,effective_end_date
537 FROM pay_element_entries_f
538 WHERE assignment_id = p_assignment_id
539 AND element_type_id = p_element_type_id
540 AND p_start_date <= effective_end_date
541 AND p_end_date >= effective_start_date
542 AND ENTRY_TYPE = 'E'
543 AND CREATOR_TYPE IN ('F','H','SP','UT')
544 ;
545
546 BEGIN
547
548 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
549
550 hr_utility.trace('The assignment id is '||to_char(p_assignment_id));
551 hr_utility.trace('The period start date is '||to_char(p_period_start_date));
552 hr_utility.trace('The period end date is '||to_char(p_period_end_date));
553
554 SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
555 pbg.legislation_code
556 INTO v_legislation
557 FROM per_all_assignments_f paf,
558 per_business_groups_perf pbg
559 WHERE paf.assignment_id = p_assignment_id
560 AND p_period_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
561 AND paf.business_group_id = pbg.business_group_id
562 ;
563
564 hr_utility.trace('The Legislation code is '||v_legislation);
565 v_sim_region3 := v_legislation||'_SIMULATION_REGION3' ;
566 v_sim_region4 := v_legislation||'_SIMULATION_REGION4' ;
567
568 FOR ele_type IN csr_simulation_elements(p_assignment_id, v_sim_region3, v_sim_region4) LOOP
569 hr_utility.trace('The element type id is '||ele_type.element_type_id);
570 v_num_inp_val_mod := 0; /*Resetting the value to zero for the next element */
571
572 IF (is_multiple_entries_allowed(ele_type.element_type_id,p_period_end_date)) THEN
573 /*An element with the multiple entries allowed flag set as Y is configured in simulation directly
574 It is incorrect The value set corresponding to information1 of pay_element_type_extra_info should have handled it
575 Ignoring the particular element type gracefully in simulation*/
576 hr_utility.trace('setup is incorrect multiple entries case et_id='||ele_type.element_type_id);
577 /*CONTINUE;*/ /*Continue statement cannot be used since it is only from 11g*/
578 GOTO end_loop;
579 END IF ;
580
581 -----------------------------Get the element Input values---------------------------------
582 OPEN csr_sim_entry_values(p_assignment_id, v_sim_region3, v_sim_region4,ele_type.element_type_id);
583
584 LOOP
585 FETCH csr_sim_entry_values
586 INTO v_inp_val_id
587 ,v_value;
588
589 EXIT WHEN csr_sim_entry_values%NOTFOUND;
590
591 hr_utility.trace('The input value id is '||v_inp_val_id);
592
593 IF v_value IS NOT NULL THEN
594 v_num_inp_val_mod:=v_num_inp_val_mod+1;
595 inp_value_id_tbl(v_num_inp_val_mod) := v_inp_val_id;
596 scr_valuetbl(v_num_inp_val_mod) := v_value;
597 hr_utility.trace('The entry value is '||v_value);
598 END IF;
599 END LOOP;
600 CLOSE csr_sim_entry_values;
601
602 IF (v_num_inp_val_mod < 1) THEN
603 /*No What if values are provided for this element process the element as it is */
604 hr_utility.trace('No What if values are provided for the element et_id='||ele_type.element_type_id);
605 /*CONTINUE;*/ /*Continue statement cannot be used since it is only from 11g*/
606 GOTO end_loop;
607 END IF;
608
609 ---------------------------Get the element Entry id ---------------------------------------
610
611 IF (check_if_multi_entry_elem(ele_type.element_type_id)) THEN
612 hr_utility.trace('Multiple Entries allowed case check carefully et_id='||ele_type.element_type_id);
613 handle_multi_entry_case(
614 p_start_date => p_period_start_date,
615 p_end_date => p_period_end_date,
616 p_assignment_id => p_assignment_id,
617 p_element_type_id => ele_type.element_type_id,
618 p_num_ent_vals => v_num_inp_val_mod,
619 p_inp_val_id_tbl => inp_value_id_tbl,
620 p_ent_val_tbl => scr_valuetbl);
621 ELSE
622 /*Remove any of the adjustment entries,replacement entries,override entries,additional entries
623 Since these type of will change the behavior of the original element entry and we are populating the
624 original entries with the whatif values*/
625 del_adj_add_entries
626 (
627 ele_type.element_type_id,
628 p_assignment_id,
629 p_period_start_date,
630 p_period_end_date
631 );
632 hr_utility.trace('Multiple Entries Not allowed get the entry');
633 check_entry_exists (ele_type.element_type_id,p_assignment_id,p_period_start_date,p_period_end_date,v_exists);
634
635 IF(v_exists) THEN
636 /*For all the entries existing in the current period will update each datetracked row with the correspodning simulated value*/
637 /*have to check if there are any adjustment entries present for the same period */
638 FOR ele_ent IN csr_element_entries(p_assignment_id,ele_type.element_type_id,p_period_start_date,p_period_end_date) LOOP
639 hr_entry_api.update_element_entry(
640 p_dt_update_mode => hr_api.g_correction,
641 p_session_date => ele_ent.effective_end_date,
642 p_element_entry_id => ele_ent.element_entry_id,
643 p_num_entry_values => v_num_inp_val_mod,
644 p_input_value_id_tbl => inp_value_id_tbl,
645 p_entry_value_tbl => scr_valuetbl);
646 END LOOP;
647 ELSE
648 /*The entry for the simulation element does not exist will have to create an entry for the
649 simulation period
650 The effective date on which the element entry is created has to be decided */
651 insert_new_element_entry (
652 p_start_date => p_period_start_date,
653 p_end_date => p_period_end_date,
654 p_assignment_id => p_assignment_id,
655 p_element_type_id => ele_type.element_type_id,
656 p_num_ent_vals => v_num_inp_val_mod,
657 p_inp_val_id_tbl => inp_value_id_tbl,
658 p_ent_val_tbl => scr_valuetbl);
659 END IF;
660 END IF;
661 <<end_loop>> -- not allowed unless an executable statement follows
662 NULL; -- add NULL statement to avoid error
663
664 END LOOP;
665
666 hr_utility.set_location('In update_element_entries updated with what-if values',80);
667
668 /*Delete all the retro entries for the assignment in the simulation period*/
669 del_retro_entries(p_assignment_id,p_period_start_date,p_period_end_date);
670
671 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
672
673 END update_element_entries ;
674 --
675 --
676 PROCEDURE sim_rollback_actions
677 (
678 p_assact_id IN NUMBER
679 ,p_ass_id IN NUMBER
680 ,p_period_start_date IN DATE
681 ,p_period_end_date IN DATE
682 ) IS
683 lv_procedure_name VARCHAR2(100) := '.sim_rollback_actions';
684 ecode VARCHAR2(50);
685 emesg VARCHAR2(2000);
686
687 CURSOR csr_rollback_actions IS
688 SELECT paa.assignment_action_id assact
689 ,ppa.payroll_action_id pact_id
690 ,ppa.action_type act_type
691 FROM pay_assignment_actions paa
692 ,pay_payroll_actions ppa
693 WHERE paa.assignment_id = p_ass_id
694 AND paa.source_action_id IS NULL
695 AND ppa.payroll_action_id = paa.payroll_action_id
696 AND ppa.action_type IN ('Q','R','V'
697 ,'B','L','F'
698 ,'W','O','G')
699 AND ppa.effective_date BETWEEN p_period_start_date AND p_period_end_date
700 AND NVL(ppa.legislative_parameters,'TEST') <> 'PAY_SIM'
701 ORDER BY paa.action_sequence DESC
702 ;
703
704 BEGIN
705 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
706
707 FOR rollbackact IN csr_rollback_actions LOOP
708 IF (rollbackact.act_type = 'Q') THEN
709 hr_utility.trace('Rolling back Quickpay payroll action '||rollbackact.pact_id);
710 py_rollback_pkg.rollback_payroll_action(rollbackact.pact_id);
711 ELSE
712 hr_utility.trace('Rolling back assact : '||rollbackact.assact||'action_type'||rollbackact.act_type);
713 py_rollback_pkg.rollback_ass_action(rollbackact.assact);
714 END IF;
715 END LOOP;
716
717 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
718 EXCEPTION
719
720 WHEN OTHERS THEN
721 hr_utility.trace('Exception occurred in'||gv_package||lv_procedure_name);
722 ecode := SQLCODE;
723 emesg := SQLERRM;
724 insert_error_message(
725 p_assact_id
726 ,'ERROR'
727 ,p_ass_id
728 ,p_period_end_date
729 ,emesg
730 );
731
732 hr_utility.set_message(801, 'PAY_500027_SIM_ROLLBACK_FAIL');
733 hr_utility.raise_error;
734 END sim_rollback_actions;
735 --
736 --
737 PROCEDURE simulator_preprocessing
738 (
739 p_assact_id IN NUMBER
740 ) IS
741 v_ass_id NUMBER;
742 v_period_start_date DATE;
743 v_period_end_date DATE;
744 sql_cursor INTEGER;
745 l_rows INTEGER;
746 preprocess_statement VARCHAR2(2000);
747 l_legislation per_business_groups_perf.legislation_code%TYPE;
748 l_bg_id NUMBER;
749 ecode VARCHAR2(50);
750 emesg VARCHAR2(2000);
751 lv_procedure_name VARCHAR2(100) := '.simulator_preprocessing';
752 BEGIN
753 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
754
755 SELECT paa.assignment_id, ptp.start_date, ptp.end_date
756 INTO v_ass_id,v_period_start_date,v_period_end_date
757 FROM pay_assignment_actions paa,
758 pay_payroll_actions ppa,
759 per_time_periods ptp
760 WHERE paa.assignment_action_id = p_assact_id
761 AND ppa.payroll_action_id = paa.payroll_action_id
762 AND ptp.time_period_id = ppa.time_period_id
763 ;
764
765 SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
766 pbg.legislation_code
767 ,paf.business_group_id
768 INTO l_legislation
769 ,l_bg_id
770 FROM per_all_assignments_f paf,
771 per_business_groups_perf pbg
772 WHERE paf.assignment_id = v_ass_id
773 AND v_period_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
774 AND paf.business_group_id = pbg.business_group_id
775 ;
776
777 /*Rollback any payroll runs in the current period*/
778 sim_rollback_actions(p_assact_id,v_ass_id,v_period_start_date,v_period_end_date);
779
780 /*Delete the element entries of the elements in the simulation exclusion set*/
781 del_excluded_entries(v_ass_id,v_period_start_date,v_period_end_date,l_bg_id);
782
783 preprocess_statement := 'begin pay_'||l_legislation||'_simulation.update_asg_data(
784 p_source_action_id =>:p_assact_id); end;';
785
786 hr_utility.set_location ('pay_simulator_pkg.simulator_preprocessing',20);
787 hr_utility.trace(preprocess_statement);
788
789 sql_cursor := dbms_sql.open_cursor;
790 dbms_sql.parse(sql_cursor,preprocess_statement,dbms_sql.v7);
791 dbms_sql.bind_variable(sql_cursor, ':p_assact_id',p_assact_id);
792 l_rows := dbms_sql.execute(sql_cursor);
793 dbms_sql.close_cursor(sql_cursor);
794
795 hr_utility.set_location('After executing localization preprocessing',30);
796
797 update_element_entries(v_ass_id,v_period_start_date,v_period_end_date);
798 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
799
800 EXCEPTION
801
802 WHEN OTHERS THEN
803 hr_utility.trace('Exception occurred in'||gv_package||lv_procedure_name);
804 ecode := SQLCODE;
805 emesg := SQLERRM;
806 insert_error_message(
807 p_assact_id
808 ,'ERROR'
809 ,v_ass_id
810 ,v_period_end_date
811 ,emesg
812 );
813 raise;
814
815 END simulator_preprocessing;
816 --
817 --
818 PROCEDURE arch_err_msgs
819 (
820 p_assact_id IN NUMBER
821 ) IS
822
823 CURSOR csr_error_messages(p_asg_act_id NUMBER)
824 IS
825 SELECT source_id
826 ,source_type
827 ,line_text
828 FROM pay_message_lines
829 WHERE source_id = p_asg_act_id
830 AND source_type = 'A'
831 ;
832 v_asg_id NUMBER;
833 v_effective_date DATE;
834 err_msg_tab pay_emp_action_arch.action_info_table ;
835 v_index NUMBER;
836 lv_procedure_name VARCHAR2(100) := '.arch_err_msgs';
837
838 BEGIN
839 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
840
841 SELECT paa.assignment_id, ptp.end_date
842 INTO v_asg_id,v_effective_date
843 FROM pay_assignment_actions paa,
844 pay_payroll_actions ppa,
845 per_time_periods ptp
846 WHERE paa.assignment_action_id = p_assact_id
847 AND ppa.payroll_action_id = paa.payroll_action_id
848 AND ptp.time_period_id = ppa.time_period_id
849 ;
850
851 FOR errmsg IN csr_error_messages(p_assact_id) LOOP
852 v_index := err_msg_tab.count;
853 err_msg_tab(v_index).action_info_category := 'SIMULATION_ERROR';
854 err_msg_tab(v_index).act_info1 := errmsg.line_text;
855 END LOOP;
856
857 insert_simulation_rows(p_action_context_id => p_assact_id
858 ,p_action_context_type => 'ERROR'
859 ,p_assignment_id => v_asg_id
860 ,p_tax_unit_id => NULL
861 ,p_effective_date => v_effective_date
862 ,p_tab_rec_data => err_msg_tab
863 );
864
865 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
866
867 END arch_err_msgs;
868 --
869 --
870 PROCEDURE archive_run_results
871 (
872 p_assact_id IN NUMBER
873 )
874 IS
875 v_ass_id NUMBER;
876 v_period_start_date DATE;
877 v_period_end_date DATE;
878 sql_cursor INTEGER;
879 l_rows INTEGER;
880 preprocess_statement VARCHAR2(2000);
881 ecode VARCHAR2(50);
882 emesg VARCHAR2(2000);
883 l_legislation per_business_groups_perf.legislation_code%TYPE;
884 n_assact_id NUMBER;
885 lv_procedure_name VARCHAR2(100) := '.archive_run_results';
886
887 CURSOR run_results (v_assact_id NUMBER) IS
888 SELECT DISTINCT
889 pet.element_name
890 ,piv.name
891 ,prr.run_result_id
892 ,prv.result_value
893 ,prr.entry_type
894 ,prr.source_id
895 ,prr.source_type
896 ,prr.start_date
897 ,prr.end_date
898 ,prr.element_entry_id
899 ,prv.formula_result_flag
900 FROM pay_element_types_f pet
901 ,pay_input_values_f piv
902 ,pay_run_results prr
903 ,pay_run_result_values prv
904 WHERE prr.assignment_action_id = v_assact_id
905 AND prr.run_result_id = prv.run_result_id
906 AND prv.input_value_id = piv.input_value_id
907 AND pet.element_type_id = prr.element_type_id
908 AND piv.element_type_id = pet.element_type_id
909 ORDER BY 1
910 ,2
911 ,3
912 ,4
913 ,9
914 ,10;
915
916 cursor ass_actions is
917 select assignment_action_id
918 from pay_assignment_actions
919 where source_action_id = p_assact_id;
920 BEGIN
921
922 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
923 hr_utility.trace('The assignment action id is '|| p_assact_id);
924
925 IF (g_dbg) THEN
926 for assact in ass_actions
927 LOOP
928 hr_utility.trace('The child action is '||assact.assignment_action_id||' for the master action'||p_assact_id);
929 n_assact_id := assact.assignment_action_id;
930 END LOOP;
931
932 hr_utility.trace('The value of n_assact_id is '|| to_char (n_assact_id));
933
934 FOR test in run_results(p_assact_id)
935 LOOP
936 hr_utility.trace('Element Name '||test.element_name||' INP name '||test.name||' RRV ='||test.result_value);
937 END LOOP;
938
939 FOR rrid in run_results(n_assact_id)
940 LOOP
941 hr_utility.trace('Element Name '||rrid.element_name||' INP name '||rrid.name||' RRV ='||rrid.result_value);
942 END LOOP;
943 END IF;
944
945
946 SELECT paa.assignment_id, ptp.start_date, ptp.end_date
947 INTO v_ass_id,v_period_start_date,v_period_end_date
948 FROM pay_assignment_actions paa,
949 pay_payroll_actions ppa,
950 per_time_periods ptp
951 WHERE paa.assignment_action_id = p_assact_id
952 AND ppa.payroll_action_id = paa.payroll_action_id
953 AND ptp.time_period_id = ppa.time_period_id
954 ;
955
956 SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
957 pbg.legislation_code
958 INTO l_legislation
959 FROM per_all_assignments_f paf,
960 per_business_groups_perf pbg
961 WHERE paf.assignment_id = v_ass_id
962 AND v_period_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
963 AND paf.business_group_id = pbg.business_group_id
964 ;
965
966 hr_utility.set_location ('pay_simulator_pkg.archive_results',20);
967
968 preprocess_statement := 'begin pay_'||l_legislation||'_simulation.archive_data(
969 p_source_action_id =>:p_assact_id); end;';
970
971 hr_utility.trace(preprocess_statement);
972
973 sql_cursor := dbms_sql.open_cursor;
974 dbms_sql.parse(sql_cursor,preprocess_statement,dbms_sql.v7);
975 dbms_sql.bind_variable(sql_cursor, ':p_assact_id',p_assact_id);
976 l_rows := dbms_sql.execute(sql_cursor);
977 dbms_sql.close_cursor(sql_cursor);
978
979 hr_utility.set_location('After executing localization archive data',30);
980
981 hr_utility.set_location('pay_simulator_pkg.archive_run_results }',90);
982
983 EXCEPTION
984
985 WHEN OTHERS THEN
986 hr_utility.trace('Exception occurred in'||gv_package||lv_procedure_name);
987 ecode := SQLCODE;
988 emesg := SQLERRM;
989 insert_error_message(
990 p_assact_id
991 ,'ERROR'
992 ,v_ass_id
993 ,v_period_end_date
994 ,emesg
995 );
996
997 END archive_run_results ;
998 --
999 PROCEDURE insert_simulation_rows(
1000 p_action_context_id NUMBER
1001 ,p_action_context_type VARCHAR2
1002 ,p_assignment_id NUMBER
1003 ,p_tax_unit_id NUMBER
1004 ,p_effective_date DATE
1005 ,p_tab_rec_data pay_emp_action_arch.action_info_table
1006 )
1007 IS
1008 pragma AUTONOMOUS_TRANSACTION;
1009 lv_procedure_name VARCHAR2(100) := '.insert_simulation_rows';
1010
1011 BEGIN
1012 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1013
1014 IF p_tab_rec_data.COUNT > 0 THEN
1015 FOR i in p_tab_rec_data.FIRST .. p_tab_rec_data.LAST
1016 LOOP
1017
1018 INSERT INTO pay_simulation_information
1019 (
1020 ACTION_INFORMATION_ID,
1021 ACTION_CONTEXT_ID,
1022 ACTION_CONTEXT_TYPE,
1023 TAX_UNIT_ID,
1024 JURISDICTION_CODE,
1025 SOURCE_ID,
1026 SOURCE_TEXT,
1027 TAX_GROUP,
1028 ACTION_INFORMATION_CATEGORY,
1029 ACTION_INFORMATION1,
1030 ACTION_INFORMATION2,
1031 ACTION_INFORMATION3,
1032 ACTION_INFORMATION4,
1033 ACTION_INFORMATION5,
1034 ACTION_INFORMATION6,
1035 ACTION_INFORMATION7,
1036 ACTION_INFORMATION8,
1037 ACTION_INFORMATION9,
1038 ACTION_INFORMATION10,
1039 ACTION_INFORMATION11,
1040 ACTION_INFORMATION12,
1041 ACTION_INFORMATION13,
1042 ACTION_INFORMATION14,
1043 ACTION_INFORMATION15,
1044 ACTION_INFORMATION16,
1045 ACTION_INFORMATION17,
1046 ACTION_INFORMATION18,
1047 ACTION_INFORMATION19,
1048 ACTION_INFORMATION20,
1049 ACTION_INFORMATION21,
1050 ACTION_INFORMATION22,
1051 ACTION_INFORMATION23,
1052 ACTION_INFORMATION24,
1053 ACTION_INFORMATION25,
1054 ACTION_INFORMATION26,
1055 ACTION_INFORMATION27,
1056 ACTION_INFORMATION28,
1057 ACTION_INFORMATION29,
1058 ACTION_INFORMATION30,
1059 EFFECTIVE_DATE,
1060 ASSIGNMENT_ID
1061 )
1062 VALUES
1063 (
1064 pay_simulation_information_s.NEXTVAL,
1065 p_action_context_id,
1066 p_action_context_type,
1067 p_tax_unit_id,
1068 p_tab_rec_data(i).jurisdiction_code,
1069 NULL,
1070 NULL,
1071 NULL,
1072 p_tab_rec_data(i).action_info_category,
1073 p_tab_rec_data(i).act_info1,
1074 p_tab_rec_data(i).act_info2,
1075 p_tab_rec_data(i).act_info3,
1076 p_tab_rec_data(i).act_info4,
1077 p_tab_rec_data(i).act_info5,
1078 p_tab_rec_data(i).act_info6,
1079 p_tab_rec_data(i).act_info7,
1080 p_tab_rec_data(i).act_info8,
1081 p_tab_rec_data(i).act_info9,
1082 p_tab_rec_data(i).act_info10,
1083 p_tab_rec_data(i).act_info11,
1084 p_tab_rec_data(i).act_info12,
1085 p_tab_rec_data(i).act_info13,
1086 p_tab_rec_data(i).act_info14,
1087 p_tab_rec_data(i).act_info15,
1088 p_tab_rec_data(i).act_info16,
1089 p_tab_rec_data(i).act_info17,
1090 p_tab_rec_data(i).act_info18,
1091 p_tab_rec_data(i).act_info19,
1092 p_tab_rec_data(i).act_info20,
1093 p_tab_rec_data(i).act_info21,
1094 p_tab_rec_data(i).act_info22,
1095 p_tab_rec_data(i).act_info23,
1096 p_tab_rec_data(i).act_info24,
1097 p_tab_rec_data(i).act_info25,
1098 p_tab_rec_data(i).act_info26,
1099 p_tab_rec_data(i).act_info27,
1100 p_tab_rec_data(i).act_info28,
1101 p_tab_rec_data(i).act_info29,
1102 p_tab_rec_data(i).act_info30,
1103 p_effective_date,
1104 p_assignment_id
1105 );
1106 END LOOP;
1107 END IF;
1108 COMMIT;
1109
1110 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 hr_utility.trace('Exception occurred in'||gv_package||lv_procedure_name);
1115 ROLLBACK;
1116 END insert_simulation_rows;
1117 --
1118 --
1119 PROCEDURE insert_error_message(
1120 p_action_context_id NUMBER
1121 ,p_action_context_type VARCHAR2
1122 ,p_assignment_id NUMBER
1123 ,p_effective_date DATE
1124 ,p_err_msg VARCHAR2
1125 )
1126 IS
1127 pragma AUTONOMOUS_TRANSACTION;
1128 lv_procedure_name VARCHAR2(100) := '.insert_error_message';
1129 BEGIN
1130 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1131 INSERT INTO pay_simulation_information
1132 (
1133 ACTION_INFORMATION_ID,
1134 ACTION_CONTEXT_ID,
1135 ACTION_CONTEXT_TYPE,
1136 ACTION_INFORMATION_CATEGORY,
1137 ACTION_INFORMATION1,
1138 EFFECTIVE_DATE,
1139 ASSIGNMENT_ID
1140 )
1141 VALUES
1142 (
1143 pay_simulation_information_s.NEXTVAL,
1144 p_action_context_id,
1145 p_action_context_type,
1146 'SIMULATION_ERROR',
1147 p_err_msg,
1148 p_effective_date,
1149 p_assignment_id
1150 );
1151 COMMIT;
1152 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 hr_utility.trace('Exception occurred in'||gv_package||lv_procedure_name);
1156 ROLLBACK;
1157 END;
1158 --
1159 PROCEDURE clear_previous_sim_data(p_assignment_id IN NUMBER)
1160 IS
1161 pragma AUTONOMOUS_TRANSACTION;
1162
1163 CURSOR c_get_action_parameter IS
1164 SELECT parameter_value
1165 FROM pay_action_parameters
1166 WHERE parameter_name = 'RETAIN_SIMULATION_DATA';
1167
1168 lv_procedure_name VARCHAR2(100) := '.clear_previous_sim_data';
1169
1170 lv_action_parameter_value pay_action_parameters.parameter_value%TYPE;
1171 lv_retain_simulation_data VARCHAR2(10);
1172
1173 ln_assignment_id pay_assignment_actions.assignment_id%TYPE;
1174 BEGIN
1175 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1176
1177 lv_retain_simulation_data := 'NO';
1178
1179 OPEN c_get_action_parameter;
1180 FETCH c_get_action_parameter INTO lv_action_parameter_value;
1181
1182 IF lv_action_parameter_value = 'ALL' OR lv_action_parameter_value = p_assignment_id THEN
1183 lv_retain_simulation_data := 'YES';
1184 END IF;
1185
1186 CLOSE c_get_action_parameter;
1187
1188 IF lv_retain_simulation_data = 'NO' THEN
1189
1190 hr_utility.trace('Cleaning Archive Data for Assignment ID : '||p_assignment_id);
1191
1192 DELETE FROM pay_simulation_information
1193 WHERE assignment_id = p_assignment_id
1194 ;
1195
1196 END IF;
1197
1198 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1199 COMMIT;
1200
1201 END clear_previous_sim_data;
1202 --
1203 --
1204 PROCEDURE capture_user_inputs (p_assignment_id NUMBER
1205 ,p_source_id NUMBER
1206 ,p_effective_date DATE)
1207 IS
1208 pragma AUTONOMOUS_TRANSACTION;
1209 lv_procedure_name VARCHAR2(100) := '.capture_user_inputs';
1210 BEGIN
1211 hr_utility.trace('Entering '||gv_package||lv_procedure_name);
1212
1213 INSERT
1214 INTO pay_simulation_information
1215 (action_information_id
1216 ,action_context_id
1217 ,action_context_type
1218 ,action_information_category
1219 ,effective_date
1220 ,assignment_id
1221 ,action_information1
1222 ,action_information2
1223 ,action_information3
1224 ,action_information4
1225 ,action_information5
1226 ,action_information6
1227 ,action_information7
1228 ,action_information8
1229 ,action_information9
1230 ,action_information10
1231 ,action_information11
1232 ,action_information12
1233 ,action_information13
1234 ,action_information14
1235 ,action_information15
1236 ,action_information16
1237 ,action_information17
1238 ,action_information18
1239 ,action_information19
1240 ,action_information20
1241 ,action_information21
1242 ,action_information22
1243 ,action_information23
1244 ,action_information24
1245 ,action_information25
1246 ,action_information26
1247 ,action_information27
1248 ,action_information28
1249 ,action_information29
1250 ,action_information30
1251 )
1252 SELECT pay_simulation_information_s.nextval
1253 ,p_source_id
1254 ,'USER_INPUTS'
1255 ,information_type
1256 ,p_effective_date
1257 ,p_assignment_id
1258 ,aei_information1
1259 ,aei_information2
1260 ,aei_information3
1261 ,aei_information4
1262 ,aei_information5
1263 ,aei_information6
1264 ,aei_information7
1265 ,aei_information8
1266 ,aei_information9
1267 ,aei_information10
1268 ,aei_information11
1269 ,aei_information12
1270 ,aei_information13
1271 ,aei_information14
1272 ,aei_information15
1273 ,aei_information16
1274 ,aei_information17
1275 ,aei_information18
1276 ,aei_information19
1277 ,aei_information20
1278 ,aei_information21
1279 ,aei_information22
1280 ,aei_information23
1281 ,aei_information24
1282 ,aei_information25
1283 ,aei_information26
1284 ,aei_information27
1285 ,aei_information28
1286 ,aei_information29
1287 ,aei_information30
1288 FROM per_assignment_extra_info
1289 WHERE assignment_id = p_assignment_id;
1290
1291 COMMIT;
1292 hr_utility.trace('Leaving '||gv_package||lv_procedure_name);
1293 END;
1294 --
1295 --
1296 END pay_simulator_pkg ;
1297 --