[Home] [Help]
PACKAGE BODY: APPS.HR_PAY_RATE_SS
Source
1 PACKAGE BODY hr_pay_rate_ss AS
2 /* $Header: hrpaywrs.pkb 120.18 2010/10/17 05:25:24 ckondapi ship $*/
3
4
5 g_package varchar2(30) := 'hr_pay_rate_ss';
6
7 inv_next_sal_date_warn boolean;
8 proposed_salary_warn boolean;
9 approved_warn boolean;
10 payroll_warn boolean;
11 basischanged boolean;
12 -- Package scope global variables.
13 l_transaction_table hr_transaction_ss.transaction_table;
14
15 l_count INTEGER := 0;
16 p_count INTEGER := 0;
17 l_praddr_ovrlap VARCHAR2(2);
18 -- ln_no_of_components INTEGER := 0;
19
20 -- p_login_person_id per_all_people_f.person_id%TYPE;
21 l_trans_step_id hr_api_transaction_steps.transaction_step_id%type;
22 ltt_trans_obj_vers_num hr_api_transaction_steps.object_version_number%type;
23 p_trans_rec_count INTEGER;
24 g_data_error exception;
25 g_exceeded_grade_range exception;
26 g_asg_api_name constant varchar2(80)
27 default 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
28 -- 04/12/02 Salary Basis Enhancement Change Begins
29 g_mid_pay_period_change constant varchar2(30) := 'HR_MID_PAY_PERIOD_CHANGE';
30
31 function check_ele_eligibility(p_asg_id in number,
32 p_eff_date in varchar2) Return boolean IS
33
34 l_proc varchar2(200) := g_package || 'check_ele_eligibility';
35
36 Cursor c1 (p_aid number, p_date date) IS
37 select ivf.element_type_id
38 from per_all_assignments_f paf,
39 per_pay_bases pb, pay_input_values_f ivf
40 where paf.assignment_id = p_aid
41 --and paf.assignment_type = 'E'
42 and pb.pay_basis_id = paf.pay_basis_id
43 and pb.input_value_id = ivf.input_value_id
44 and p_date between paf.effective_start_date and paf.effective_end_date
45 and p_date between ivf.effective_start_date and ivf.effective_end_date;
46
47
48 l_tmp number := null;
49 begin
50
51 hr_utility.set_location(' Entering:' || l_proc,5);
52
53 Open c1 (p_asg_id, to_date(p_eff_date,'RRRR-MM-DD'));
54 Fetch c1 into l_tmp;
55 Close c1;
56 l_tmp := hr_entry_api.get_link(p_asg_id, l_tmp,
57 to_date(p_eff_date,'RRRR-MM-DD'));
58
59 if (l_tmp is not null) then
60 hr_utility.set_location(' Leaving:' || l_proc,10);
61 return true;
62 end If;
63 hr_utility.set_location(' Leaving:' || l_proc,15);
64 return false;
65 Exception when others then
66 hr_utility.set_location(' Leaving:' || l_proc,555);
67 return false;
68 end check_ele_eligibility;
69
70
71 -- 05/14/2002 - Bug 2374140 Fix Begins
72 -- ------------------------------------------------------------------------
73 -- |------------------ < check_mid_pay_period_change > --------------------|
74 -- ------------------------------------------------------------------------
75 --
76 -- Description
77 --
78 -- Determine if a mid pay period change was performed when a salary basis
79 -- was changed. If yes, we need to set the WF item attribute
80 -- HR_MID_PAY_PERIOD_CHANGE ='Y' so that a notification will be sent to the
81 -- Payroll Contact.
82 --
83 -- This procedure is invoked by the WF HR_CHK_SAL_BASIS_MID_PAY_PERIOD process.
84 --
85 -- ------------------------------------------------------------------------
86 procedure check_mid_pay_period_change
87 (p_item_type in varchar2,
88 p_item_key in varchar2,
89 p_act_id in number,
90 funmode in varchar2,
91 result out nocopy varchar2 ) IS
92
93
94 l_proc varchar2(200) := g_package || 'check_mid_pay_period_change';
95 l_assignment_id per_all_assignments_f.assignment_id%type default null;
96 l_payroll_id per_all_assignments_f.payroll_id%type default null;
97 l_old_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
98 l_new_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
99 l_pay_period_start_date date default null;
100 l_pay_period_end_date date default null;
101
102 l_asg_txn_step_id hr_api_transaction_steps.transaction_step_id%type
103 default null;
104 l_effective_date date default null;
105
106
107 CURSOR csr_check_mid_pay_period(p_eff_date_csr in date
108 ,p_payroll_id_csr in number) IS
109 select start_date, end_date
110 from per_time_periods
111 where p_eff_date_csr > start_date
112 and p_eff_date_csr <= end_date
113 and payroll_id = p_payroll_id_csr;
114
115 -- The following cursor is copied from hr_transaction_ss.process_transaction.
116 CURSOR csr_trs is
117 select trs.transaction_step_id
118 ,trs.api_name
119 ,trs.item_type
120 ,trs.item_key
121 ,trs.activity_id
122 ,trs.creator_person_id
123 from hr_api_transaction_steps trs
124 where trs.item_type = p_item_type
125 and trs.item_key = p_item_key
126 order by trs.processing_order
127 ,trs.transaction_step_id ; --#2313279
128 --
129
130 -- Get existing assignment data
131 CURSOR csr_get_old_asg_data IS
132 SELECT pay_basis_id
133 FROM per_all_assignments_f
134 WHERE assignment_id = l_assignment_id
135 AND l_effective_date between effective_start_date
136 and effective_end_date
137 AND assignment_type = 'E';
138
139
140 BEGIN
141 hr_utility.set_location(' Entering:' || l_proc,5);
142 IF ( funmode = 'RUN' )
143 THEN
144 hr_utility.set_location(l_proc,10);
145 -- Get the ASG and Pay Rate transaction step id
146 FOR I in csr_trs
147 LOOP
148 IF I.api_name = g_asg_api_name
149 THEN
150 l_asg_txn_step_id := I.transaction_step_id;
151 EXIT;
152 END IF;
153 END LOOP;
154
155 IF l_asg_txn_step_id IS NOT NULL
156 THEN
157 hr_utility.set_location(l_proc,15);
158 l_effective_date := to_date(
159 hr_transaction_ss.get_wf_effective_date
160 (p_transaction_step_id => l_asg_txn_step_id),
161 hr_transaction_ss.g_date_format);
162
163 -- Get the pay_basis_id and payroll_id
164 l_new_pay_basis_id := hr_transaction_api.get_number_value
165 (p_transaction_step_id => l_asg_txn_step_id
166 ,p_name => 'P_PAY_BASIS_ID');
167
168 l_payroll_id := hr_transaction_api.get_number_value
169 (p_transaction_step_id => l_asg_txn_step_id
170 ,p_name => 'P_PAYROLL_ID');
171
172 l_assignment_id := hr_transaction_api.get_number_value
173 (p_transaction_step_id => l_asg_txn_step_id
174 ,p_name => 'P_ASSIGNMENT_ID');
175
176 -- Now get the old pay basis id
177 OPEN csr_get_old_asg_data;
178 FETCH csr_get_old_asg_data into l_old_pay_basis_id;
179 IF csr_get_old_asg_data%NOTFOUND
180 THEN
181 hr_utility.set_location(l_proc,20);
182 -- could be a new hire or applicant hire, there is no asg rec
183 CLOSE csr_get_old_asg_data;
184 ELSE
185 CLOSE csr_get_old_asg_data;
186 END IF;
187
188 IF l_old_pay_basis_id IS NOT NULL and
189 l_new_pay_basis_id IS NOT NULL and
190 l_old_pay_basis_id <> l_new_pay_basis_id and
191 l_payroll_id IS NOT NULL
192 THEN
193 hr_utility.set_location(l_proc,25);
194 -- perform mid pay period check
195 OPEN csr_check_mid_pay_period
196 (p_eff_date_csr => l_effective_date
197 ,p_payroll_id_csr => l_payroll_id);
198 FETCH csr_check_mid_pay_period into l_pay_period_start_date
199 ,l_pay_period_end_date;
200 IF csr_check_mid_pay_period%NOTFOUND
201 THEN
202 hr_utility.set_location(l_proc,30);
203 -- That means the effective date is not in mid pay period
204 CLOSE csr_check_mid_pay_period;
205 -- Need to set the item attribute to 'N' because this may be
206 -- a Return For Correction and the value of the item attribute
207 -- was set to 'Y' previously.
208 wf_engine.setItemAttrText
209 (itemtype => p_item_type
210 ,itemkey => p_item_key
211 ,aname => g_mid_pay_period_change
212 ,avalue => 'N');
213 ELSE
214 hr_utility.set_location(l_proc,35);
215 -- Only set the WF Item attribute HR_MID_PAY_PERIOD_CHANGE to
216 -- 'Y' when there is payroll installed and the employee is not a
217 -- new hire (ie. first time salary basis was entered).
218 -- We determine New Hire by looking at the old db assignment rec
219 -- pay_basis_id. If that is null, then this is the first time
220 -- salary basis was entered. We don't need to perform the check
221 -- because there is no element type changed.
222 CLOSE csr_check_mid_pay_period;
223 wf_engine.setItemAttrText
224 (itemtype => p_item_type
225 ,itemkey => p_item_key
226 ,aname => g_mid_pay_period_change
227 ,avalue => 'Y');
228
229 result := 'COMPLETE:'||'Y';
230
231 END IF;
232 END IF;
233 ELSE
234 hr_utility.set_location(l_proc,40);
235 result := 'COMPLETE:'||'N';
236 END IF; -- asg txn step is not null
237 ELSIF ( funmode = 'CANCEL' ) then
238 hr_utility.set_location(l_proc,45);
239 --
240 NULL;
241 --
242 END IF;
243
244 hr_utility.set_location(' Leaving:' || l_proc,50);
245 END check_mid_pay_period_change;
246
247 -- 05/14/2002 - Bug 2374140 Fix Ends
248
249 /*-----------------------------------------------------------
250 -- This function calculates and returns quartile value for a pay
251 -- rate
252 --------------------------------------------------------------*/
253 FUNCTION get_quartile (
254 p_annual_salary NUMBER ,
255 p_grade_min NUMBER ,
256 p_grade_max NUMBER ,
257 p_grade_mid NUMBER )
258 RETURN NUMBER
259 IS
260
261 l_proc varchar2(200) := g_package || 'get_quartile';
262 ln_quartile NUMBER ;
263 ln_grade_dif NUMBER ;
264
265 BEGIN
266
267 hr_utility.set_location(' Entering:' || l_proc,5);
268 IF p_grade_min IS NULL OR
269 p_grade_max IS NULL OR
270 p_grade_mid IS NULL
271 THEN
272 hr_utility.set_location(' Leaving:' || l_proc,10);
273 return NULL ;
274 END IF ;
275
276 ln_grade_dif := p_grade_max- p_grade_min ;
277
278
279 IF p_annual_salary < ( p_grade_min + ln_grade_dif/4)
280 THEN
281 hr_utility.set_location(l_proc,15);
282 ln_quartile := 1 ;
283 ELSIF p_annual_salary < ( p_grade_min + ln_grade_dif/2 )
284 THEN
285 hr_utility.set_location(l_proc,20);
286 ln_quartile := 2 ;
287 ELSIF p_annual_salary < ( p_grade_min + (ln_grade_dif * 3/4))
288 THEN
289 hr_utility.set_location(l_proc,25);
290 ln_quartile := 3;
291
292 ELSIF p_annual_salary < p_grade_max
293 THEN
294 hr_utility.set_location(l_proc,30);
295 ln_quartile := 4 ;
296 ELSE
297 hr_utility.set_location(l_proc,35);
298 ln_quartile := NULL ;
299 END IF ;
300
301 hr_utility.set_location(' Leaving:' || l_proc,40);
302 return ln_quartile ;
303
304 END get_quartile;
305
306
307 FUNCTION get_quotient(p_divider integer,
308 p_divisor integer) return integer is
309
310 l_proc varchar2(200) := g_package || 'get_quotient';
311 i integer := 1;
312 diff integer;
313 cnt integer := 20;
314 BEGIN
315 hr_utility.set_location(' Entering:' || l_proc,5);
316 if (p_divisor = p_divider) then
317 hr_utility.set_location(' Leaving:' || l_proc,10);
318 return 1;
319 end if;
320 diff := p_divisor;
321 loop
322 diff := diff - p_divider;
323 if(diff < p_divider) then
324 exit;
325 end if;
326 i := i + 1;
327 -- put a limit of 20 times .. incase we get into infinite loop
328 if(i = cnt) then
329 exit;
330 end if;
331 end loop;
332 hr_utility.set_location(' Leaving:' || l_proc,15);
333 return i;
334 END;
335
336 FUNCTION format_number(p_number number,
337 p_precision number) return varchar2 is
338
339 l_proc varchar2(200) := g_package || 'format_number';
340
341 BEGIN
342 hr_utility.set_location(' Entering:' || l_proc,5);
343 IF (fnd_profile.value('CURRENCY:THOUSANDS_SEPARATOR') = 'Y' AND
344 p_number is not null) THEN
345
346 declare
347 p_input varchar2(200);
348 p_decimalSep CHAR(1);
349 p_groupSep CHAR(1);
350 p_afDec VARCHAR2(80);
351 p_wNum VARCHAR2(80);
352 p_len INTEGER;
353 p_times INTEGER;
354 p_prc_len INTEGER;
355 i INTEGER;
356 p_rem NUMBER;
357 p_quo NUMBER;
358 p_bf VARCHAR2(80);
359 p_af VARCHAR2(80);
360 p_indx NUMBER;
361 p_negative BOOLEAN := false;
362 begin
363 hr_utility.set_location( l_proc,10);
364 p_input := to_char(p_number);
365 -- check if we have negative number
366 IF (substr(p_input, 1, 1) = '-') THEN
367 hr_utility.set_location( l_proc,15);
368 p_negative := true;
369 p_input := substr(p_input, 2, length(p_input));
370 END IF;
371 p_decimalSep := substr(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),1,1);
372 p_groupSep := substr(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),2,1);
373 if(p_decimalSep = ',') then
374 hr_utility.set_location( l_proc,20);
375 p_input := replace(p_input,p_decimalSep,p_groupSep);
376 end if;
377 if(instr(p_input, '.') = 0) then
378 hr_utility.set_location( l_proc,25);
379 p_afDec := '.0';
380 p_wNum := p_input;
381 else
382 hr_utility.set_location( l_proc,30);
383 p_afDec := substr(p_input, instr(p_input, '.'));
384 p_wNum := substr(p_input, 1, (length(p_input) - length(p_afDec)));
385 end if;
386 p_len := length(p_wNum);
387 p_times := 0;
388 p_rem := MOD(p_len, 3);
389 if(p_len is null) then
390 hr_utility.set_location( l_proc,35);
391 p_quo := 0;
392 else
393 hr_utility.set_location( l_proc,40);
394 p_quo := get_quotient(3, p_len);
395 end if;
396 p_bf := null;
397 p_af := null;
398 p_indx := 3;
399
400 IF (p_quo > 1 AND p_rem = 0) THEN
401 hr_utility.set_location( l_proc,45);
402 p_times := p_quo - 1;
403 ELSE
404 IF (p_quo <> 1 OR p_len > 3) THEN
405 hr_utility.set_location( l_proc,50);
406 p_times := p_quo;
407 END IF;
408 END IF;
409
410 FOR i in 1..p_times LOOP
411 p_bf := substr(p_wNum, 1, (p_len - p_indx));
412 p_af := substr(p_wNum, length(p_bf)+ 1);
413 p_wNum := p_bf || p_groupSep || p_af;
414 p_indx := p_indx + 3;
415 END LOOP;
416
417 if(p_decimalSep = ',') then
418 hr_utility.set_location( l_proc,55);
419 p_afDec := replace(p_afDec,'.',p_decimalSep);
420 end if;
421 -- append zeroes equal to precision length
422 p_prc_len := length(p_afDec);
423 for i IN 0.. p_precision - p_prc_len
424 loop
425 p_afDec := p_afDec || '0';
426 end loop;
427 -- if negative, then append it back
428 if(p_negative) then
429 hr_utility.set_location( l_proc,60);
430 p_wNum := '-'||p_wNum;
431 end if;
432 hr_utility.set_location(' Leaving:' || l_proc,65);
433 return (p_wNum || p_afDec);
434 END;
435 END IF;
436 hr_utility.set_location(' Leaving:' || l_proc,70);
437 return to_char(p_number);
438 END;
439
440
441 FUNCTION get_last_pay_change (
442 p_assignment_id NUMBER ,
443 p_bus_group_id NUMBER ,
444 p_precision NUMBER,
445 p_percent OUT NOCOPY VARCHAR2) return VARCHAR2 IS
446
447 l_proc varchar2(200) := g_package || 'get_last_pay_change';
448 ln_last_pay1 NUMBER ;
449 ln_last_pay2 NUMBER ;
450
451 CURSOR c_last_pay IS
452 Select pp.proposed_salary_n*pay_annualization_factor
453 From per_pay_proposals pp, per_assignments_f paf,
454 per_pay_bases ppb
455 Where pp.assignment_id = p_assignment_id
456 And pp.business_group_id = p_bus_group_id
457 And pp.approved = 'Y'
458 And pp.assignment_id = paf.assignment_id
459 And trunc(pp.change_date) between paf.effective_start_date and paf.effective_end_date
460 And paf.pay_basis_id = ppb.pay_basis_id
461 Order By change_date desc;
462
463 BEGIN
464 hr_utility.set_location(' Entering:' || l_proc,5);
465 OPEN c_last_pay ;
466 FETCH c_last_pay INTO ln_last_pay1;
467 FETCH c_last_pay INTO ln_last_pay2 ;
468 IF c_last_pay%NOTFOUND
469 THEN
470 CLOSE c_last_pay ;
471 hr_utility.set_location(' Leaving:' || l_proc,10);
472 return NULL ;
473 END IF ;
474 CLOSE c_last_pay ;
475
476 if not ln_last_pay2 = 0 then
477 hr_utility.set_location(l_proc,15);
478 p_percent := format_number(round(((ln_last_pay1-ln_last_pay2)/ln_last_pay2)*100,p_precision), p_precision);
479 end if;
480
481 hr_utility.set_location(' Leaving:' || l_proc,20);
482 return format_number(round((ln_last_pay1-ln_last_pay2), p_precision), p_precision);
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 hr_utility.set_location(' Leaving:' || l_proc,555);
487 raise;
488
489 END ;
490
491
492
493
494 FUNCTION get_flsa_status (
495 p_assignment_id NUMBER ,
496 p_bus_group_id NUMBER ,
497 p_date DATE ,
498 p_job_id NUMBER) return VARCHAR2
499 IS
500
501 l_proc varchar2(200) := g_package || 'get_flsa_status';
502
503 CURSOR lc_flsa_status IS
504 SELECT job_information3
505 FROM per_jobs jobs , per_assignments_f asg
506 WHERE jobs.job_id = asg.job_id
507 -- AND jobs.business_group_id = asg.business_group_id
508 -- Fix 2094081
509 AND p_date between asg.effective_start_date and asg.effective_end_date
510 -- End Fix 2094081
511 AND asg.assignment_id = p_assignment_id
512 AND asg.business_group_id = p_bus_group_id ;
513
514 CURSOR lc_txn_flsa_status IS
515 SELECT job_information3
516 FROM per_jobs jobs
517 WHERE jobs.job_id = p_job_id
518 AND jobs.business_group_id = p_bus_group_id ;
519
520 lv_flsa_status VARCHAR2(150);
521 lv_flsa_meaning VARCHAr2(80);
522
523 BEGIN
524
525 hr_utility.set_location(' Entering:' || l_proc,5);
526 IF( p_job_id is not null) THEN
527 hr_utility.set_location( l_proc,10);
528 OPEN lc_txn_flsa_status ;
529 FETCH lc_txn_flsa_status into lv_flsa_status ;
530 IF lc_txn_flsa_status%NOTFOUND THEN
531 CLOSE lc_txn_flsa_status;
532 hr_utility.set_location(' Leaving:' || l_proc,15);
533 return NULL ;
534 END IF;
535 CLOSE lc_txn_flsa_status;
536 ELSE
537 hr_utility.set_location( l_proc,20);
538 OPEN lc_flsa_status ;
539 FETCH lc_flsa_status into lv_flsa_status ;
540 IF lc_flsa_status%NOTFOUND THEN
541 CLOSE lc_flsa_status;
542 hr_utility.set_location(' Leaving:' || l_proc,25);
543 return NULL ;
544 END IF;
545 CLOSE lc_flsa_status;
546 END IF ;
547
548 lv_flsa_meaning := hr_misc_web.get_lookup_meaning (
549 lv_flsa_status ,
550 'US_EXEMPT_NON_EXEMPT',
551 p_date);
552 hr_utility.set_location(' Leaving:' || l_proc,30);
553 return lv_flsa_meaning ;
554 END get_flsa_status;
555
556 /* ======================================================================
557 || Function: get_precision
558 ||----------------------------------------------------------------------
559 || Description: Gets precisions for a given currency
560 ||
561 ||
562 || Pre Conditions: a valid currency code
563 ||
564 ||
565 || In Parameters: p_uom
566 || p_currency_code
567 || p_date
568 ||
569 ||
570 || out nocopy Parameters:
571 ||
572 ||
573 || In out nocopy Parameters:
574 ||
575 ||
576 || Post Success:
577 ||
578 || returns precision
579 ||
580 || Post Failure:
581 || Raises Error
582 ||
583 || Access Status:
584 || Public.
585 ||
586 ||=================================================================== */
587
588
589
590 FUNCTION get_precision(
591 p_uom VARCHAR2 ,
592 p_currency_code VARCHAR2 ,
593 p_date DATE ) RETURN NUMBER
594 IS
595
596 l_proc varchar2(200) := g_package || 'get_precision';
597 CURSOR c_precision IS
598 SELECT CUR.PRECISION
599 FROM FND_CURRENCIES_VL CUR
600 WHERE CUR.CURRENCY_CODE=p_currency_code
601 AND p_date BETWEEN
602 NVL(CUR.START_DATE_ACTIVE,p_date) AND
603 NVL(CUR.END_DATE_ACTIVE,p_date+1);
604
605 ln_precision NUMBER ;
606
607 BEGIN
608 hr_utility.set_location(' Entering:' || l_proc,5);
609 IF p_uom = 'N'
610 THEN
611 hr_utility.set_location(l_proc,10);
612 ln_precision:= 5 ;
613 ELSE
614 hr_utility.set_location(l_proc,15);
615 OPEN c_precision ;
616 FETCH c_precision into ln_precision ;
617 CLOSE c_precision ;
618
619 END IF ;
620 hr_utility.set_location(' Leaving:' || l_proc,20);
621 return ln_precision ;
622 EXCEPTION
623 WHEN OTHERS THEN
624 hr_utility.set_location(' Leaving:' || l_proc,25);
625 raise;
626 END get_precision ;
627
628 /*===============================================================
629 | Procedure: check_gsp_txn
630 | Function: This is called from process_api to check
631 | whether current txn is gsp txn by reading 'p_gsp_dummy_txn_value'
632 | parameter value from the hr_api_transaction_values table
633 |================================================================
634 */
635 PROCEDURE check_gsp_txn
636 (p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%type
637 ,p_effective_date in varchar2
638 ,p_gsp_assignment out nocopy varchar2
639 )
640 IS
641
642 l_proc varchar2(200) := g_package || 'check_gsp_txn';
643 begin
644 hr_utility.set_location(' Entering:' || l_proc,5);
645 p_gsp_assignment := null; -- default value
646 IF p_transaction_step_id IS NOT NULL
647 THEN
648 hr_utility.set_location(l_proc,10);
649 p_gsp_assignment :=
650 hr_transaction_api.get_varchar2_value
651 (p_transaction_step_id => p_transaction_step_id,
652 p_name =>'p_gsp_dummy_txn');
653 END IF;
654 hr_utility.set_location(' Leaving:' || l_proc,15);
655 end check_gsp_txn;
656
657
658 /*===============================================================
659 | Procedure: check_gsp_asg_txn_data
660 | Function: This is a cover routine invoked by Java.
661 |
662 |================================================================
663 */
664 PROCEDURE check_gsp_asg_txn_data
665 (p_item_type in varchar2
666 ,p_item_key in varchar2
667 ,p_act_id in number
668 ,p_effective_date in date
669 ,p_assignment_id in number
670 ,p_asg_txn_step_id in number
671 ,p_get_defaults_date in date
672 ,p_excep_message out nocopy varchar2
673 ,p_flow_mode in varchar2 -- 2355929
674 ) IS
675
676 -- lv_grade_ladder_excep exception;
677 l_proc varchar2(200) := g_package || 'check_gsp_asg_txn_data';
678 ln_txn_pay_basis_id number;
679 ln_transaction_step_id number default null;
680 ln_transaction_id number default null;
681 lv_msg_text varchar2(32000) default null;
682 l_effective_date date default null;
683
684 -- cursor to get the grade ladder id from the assignment record
685 CURSOR lc_asg_grade_ladder_id IS
686 SELECT paf.grade_ladder_pgm_id,paf.assignment_type
687 FROM per_all_assignments_f paf
688 WHERE assignment_id = p_assignment_id
689 and p_effective_date between effective_start_date
690 and effective_end_date;
691
692 -- cursor to find whether assigned grade ladder id updates
693 -- the salary using Grade Step Progression
694 CURSOR lc_sal_updateable_grade_ladder
695 (p_grade_ladder_id in per_all_assignments_f.grade_ladder_pgm_id%TYPE,
696 p_effective_date in date
697 ) IS
698 select pgm_id from ben_pgm_f
699 where
700 -- grade ladder does not allow update of salary
701 (update_salary_cd <> 'NO_UPDATE' and update_salary_cd is not null)
702 -- salary updated by the progression system should not be manually overidden
703 --and (gsp_allow_override_flag is null or gsp_allow_override_flag = 'N')
704 and
705 pgm_id = p_grade_ladder_id
706 and p_effective_date between effective_start_date
707 and effective_end_date;
708
709
710
711 -- Bug 2355929 Fix Begins
712 lv_applicant_asg_type PER_ALL_ASSIGNMENTS_F.assignment_type%TYPE
713 default null;
714 -- Bug 2355929 Fix Ends
715 --ln_job_id number default null;
716 -- GSP
717 lc_temp_grade_ladder_id PER_ALL_ASSIGNMENTS_F.grade_ladder_pgm_id%TYPE default null;
718 p_old_grade_ladder_id PER_ALL_ASSIGNMENTS_F.grade_ladder_pgm_id%TYPE default null;
719 p_new_grade_ladder_id PER_ALL_ASSIGNMENTS_F.grade_ladder_pgm_id%TYPE default null;
720 ln_txn_grade_ladder_id PER_ALL_ASSIGNMENTS_F.grade_ladder_pgm_id%TYPE default null;
721
722 BEGIN
723
724 hr_utility.set_location(' Entering:' || l_proc,5);
725 lv_msg_text := '';
726 lv_msg_text := lv_msg_text ||
727 hr_util_misc_web.return_msg_text(
728 p_message_name =>'HR_PAY_RATE_GSP_NO_UPD'
729 ,p_Application_id =>'PER');
730
731 -- Read from the database to determine if there is an existing
732 -- assignment and also grade ladder assignment.
733 IF p_flow_mode IS NOT NULL and
734 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
735 THEN
736 hr_utility.set_location(l_proc,10);
737 NULL;
738 ELSE
739 hr_utility.set_location(l_proc,15);
740 OPEN lc_asg_grade_ladder_id;
741 FETCH lc_asg_grade_ladder_id into p_old_grade_ladder_id,lv_applicant_asg_type;
742 CLOSE lc_asg_grade_ladder_id;
743 END IF;
744 -- GSP won't support for CWK
745 IF lv_applicant_asg_type = 'C'
746 THEN
747 hr_utility.set_location(' Leaving:' || l_proc,20);
748 return;
749 END IF;
750
751 IF lv_applicant_asg_type = 'A'
752 THEN
753 hr_utility.set_location(l_proc,25);
754 -- zap the p_old_grade_ladder_pgm_id
755 p_old_grade_ladder_id := null;
756 --p_flow_mode := hr_process_assignment_ss.g_hire_an_applicant;
757 END IF;
758
759 IF p_asg_txn_step_id IS NOT NULL
760 THEN
761 hr_utility.set_location(l_proc,30);
762 -- In a chained process navigated from the Assignment page.
763 -- Get the grade_ladder_id from the txn step
764 ln_transaction_step_id := p_asg_txn_step_id;
765 ln_txn_grade_ladder_id := hr_transaction_api.get_number_value
766 (p_transaction_step_id => ln_transaction_step_id
767 ,p_name => 'P_GRADE_LADDER_PGM_ID');
768
769 p_new_grade_ladder_id := ln_txn_grade_ladder_id;
770 -- check whether grade ladder won't allow salary update
771 open lc_sal_updateable_grade_ladder(p_grade_ladder_id => p_new_grade_ladder_id,
772 p_effective_date => p_effective_date);
773 fetch lc_sal_updateable_grade_ladder into lc_temp_grade_ladder_id;
774 if (lc_sal_updateable_grade_ladder%FOUND) THEN
775 -- set exception message
776 -- it's an error, we cannot proceed further
777 p_excep_message := lv_msg_text;
778 END IF;
779
780 ELSE
781 hr_utility.set_location(l_proc,35);
782 -- 2 possibilities:
783 -- 1) On re-entry of a Save For Later transaction where the user last
784 -- stopped at the Pay Rate page, asg_txn_step_id is not known now
785 -- OR
786 -- 2) In Pay Rate standalone mode, thus there is no asg_txn_step_id.
787 --
788 -- Need to see if an asg txn step id exists or not.
789 hr_assignment_common_save_web.get_step
790 (p_item_type => p_item_type
791 ,p_item_key => p_item_key
792 ,p_api_name => g_asg_api_name
793 ,p_transaction_step_id => ln_transaction_step_id
794 ,p_transaction_id => ln_transaction_id);
795
796 IF nvl(ln_transaction_step_id, -1) > 0
797 THEN
798 hr_utility.set_location(l_proc,40);
799 -- It's a Save For Later transaction, Pay Rate was being chained
800 -- to the Assignment page but user last stopped at the Pay Rate
801 -- page.
802 -- Get the grade_ladder_id from the txn step
803 -- p_asg_txn_step_id := ln_transaction_step_id;
804 ln_txn_grade_ladder_id := hr_transaction_api.get_number_value
805 (p_transaction_step_id => ln_transaction_step_id
806 ,p_name => 'P_GRADE_LADDER_PGM_ID');
807 p_new_grade_ladder_id := ln_txn_grade_ladder_id;
808
809 -- check whether grade ladder won't allow salary update
810 open lc_sal_updateable_grade_ladder( p_grade_ladder_id => p_new_grade_ladder_id,
811 p_effective_date => p_effective_date);
812 fetch lc_sal_updateable_grade_ladder into lc_temp_grade_ladder_id;
813 if (lc_sal_updateable_grade_ladder%FOUND) THEN
814 hr_utility.set_location(l_proc,45);
815 -- set exception message
816 -- it's an error, we cannot proceed further
817 p_excep_message := lv_msg_text;
818 END IF;
819
820 ELSE
821 hr_utility.set_location(l_proc,50);
822 -- There is no passed ASG transaction step id and
823 -- It's Pay Rate standalone.
824 IF p_old_grade_ladder_id IS NOT NULL
825 THEN
826 hr_utility.set_location(l_proc,55);
827 -- check whether grade ladder won't allow salary update
828 open lc_sal_updateable_grade_ladder(p_grade_ladder_id => p_old_grade_ladder_id,
829 p_effective_date => p_effective_date);
830 fetch lc_sal_updateable_grade_ladder into lc_temp_grade_ladder_id;
831 if (lc_sal_updateable_grade_ladder%FOUND) THEN
832 -- set exception message
833 -- it's an error, we cannot proceed further
834 p_excep_message := lv_msg_text;
835 END IF;
836
837 END IF; -- p_old_grade_ladder_pgm_id is not null
838 END IF;
839 END IF; -- end p_asg_txn_step_id is NOT null
840
841 hr_utility.set_location(' Leaving:' || l_proc,60);
842 EXCEPTION
843 -- WHEN lv_grade_ladder_excep THEN
844
845 -- The Java caller PayRateAMImpl.java will throw the exception.
846 -- null;
847
848 WHEN OTHERS THEN
849 hr_utility.set_location(' Leaving:' || l_proc,560);
850 RAISE;
851
852 END check_gsp_asg_txn_data;
853
854 -- End of GSP changes
855
856 /*===============================================================
857 | Procedure: check_asg_txn_data
858 | Function: This is a cover routine invoked by Java.
859 |
860 |================================================================
861 */
862 PROCEDURE check_asg_txn_data
863 (p_item_type in varchar2
864 ,p_item_key in varchar2
865 ,p_act_id in number
866 ,p_effective_date in date
867 ,p_assignment_id in number
868 ,p_asg_txn_step_id in out nocopy number
869 ,p_get_defaults_date in out nocopy date
870 ,p_business_group_id out nocopy number
871 ,p_currency out nocopy varchar2
872 ,p_format_string out nocopy varchar2
873 ,p_salary_basis_name out nocopy varchar2
874 ,p_pay_basis_name out nocopy varchar2
875 ,p_pay_basis out nocopy varchar2
876 ,p_grade_basis out nocopy varchar2
877 ,p_pay_annualization_factor out nocopy number
878 ,p_fte_factor out nocopy number
879 ,p_grade out nocopy varchar2
880 ,p_grade_annualization_factor out nocopy number
881 ,p_minimum_salary out nocopy number
882 ,p_maximum_salary out nocopy number
883 ,p_midpoint_salary out nocopy number
884 ,p_prev_salary out nocopy number
885 ,p_last_change_date out nocopy date
886 ,p_element_entry_id out nocopy number
887 ,p_basis_changed out nocopy number
888 ,p_uom out nocopy varchar2
889 ,p_grade_uom out nocopy varchar2
890 ,p_change_amount out nocopy number
891 ,p_change_percent out nocopy number
892 ,p_quartile out nocopy number
893 ,p_comparatio out nocopy number
894 ,p_last_pay_change out nocopy varchar2
895 ,p_flsa_status out nocopy varchar2
896 ,p_currency_symbol out nocopy varchar2
897 ,p_precision out nocopy number
898 ,p_excep_message out nocopy varchar2
899 ,p_pay_proposal_id out nocopy number
900 ,p_current_salary out nocopy number
901 ,p_proposal_ovn out nocopy number
902 ,p_api_mode out nocopy varchar2
903 ,p_warning_message out nocopy varchar2
904 ,p_new_pay_basis_id out nocopy number
905 ,p_old_pay_basis_id out nocopy number
906 ,p_old_pay_annualization_factor out nocopy number
907 ,p_old_fte_factor out nocopy number
908 ,p_old_salary_basis_name out nocopy varchar2
909 ,p_salary_basis_change_type out nocopy varchar2
910 ,p_flow_mode in out nocopy varchar2 -- 2355929
911 ,p_element_type_id_changed out nocopy varchar2
912 ,p_old_currency_code out nocopy varchar2
913 ,p_old_currency_symbol out nocopy varchar2
914 ,p_old_pay_basis out nocopy varchar2 --4002387
915 ,p_old_to_new_currency_rate out nocopy number --4002387
916 ,p_offered_salary out nocopy number
917 ,p_proc_sel_txn in varchar2 default null
918 ) IS
919
920 l_proc varchar2(200) := g_package || 'check_asg_txn_data';
921 lv_no_sal_basis_excep exception;
922 ln_percent varchar2(300);
923 ln_txn_pay_basis_id number;
924 ln_transaction_step_id number default null;
925 ln_transaction_id number default null;
926 lv_msg_text varchar2(32000) default null;
927 ln_business_group_id per_all_people_f.business_group_id%type
928 default null;
929 -- The following defintions were copied from pepaprpo.pkb get_defaults proc
930 lv_currency VARCHAR2(15) default null;
931 lv_format_string VARCHAR2(40) default null;
932 lv_salary_basis_name per_pay_bases.name%type default null;
933 lv_pay_basis_name VARCHAR2(80) default null;
934 lv_pay_basis per_pay_bases.pay_basis%type default null;
935 lv_grade_basis per_pay_bases.rate_basis%type default null;
936 lv_pay_annualization_factor per_pay_bases.pay_annualization_factor%type;
937 lv_fte_factor number;
938 lv_grade VARCHAR2(240) default null;
939 lv_grade_annualization_factor per_pay_bases.grade_annualization_factor%type;
940 ln_minimum_salary number default null;
941 ln_maximum_salary number default null;
942 ln_midpoint_salary number default null;
943 ln_prev_salary number default null;
944 ld_last_change_date date default null;
945 ln_element_entry_id number default null;
946 ln_basis_changed number default null;
947 lv_uom VARCHAR2(30) default null;
948 lv_grade_uom VARCHAR2(30) default null;
949 ln_change_amount number default null;
950 ln_change_percent number default null;
951 ln_quartile number default null;
952 ln_comparatio number default null;
953 lv_last_pay_change varchar2(200) default null;
954 lv_flsa_status hr_lookups.meaning%type default null;
955 lv_currency_symbol fnd_currencies.symbol%type default null;
956 ln_precision number default null;
957 ln_pay_proposal_id per_pay_proposals.pay_proposal_id%type ;
958 ln_current_salary number default null;
959 ln_proposal_ovn per_pay_proposals.object_version_number%type;
960 lv_api_mode varchar2(30) default null;
961 ld_get_defaults_date date default null;
962 lv_warning_message varchar2(4000) default '';
963 ln_old_pay_annual_factor number default null;
964 ln_old_fte_factor number default null;
965 lv_old_salary_basis_name per_pay_bases.name%type default null;
966 lv_excep_message varchar2(4000) default null;
967 lv_salary_basis_change_type varchar2(30) default 'NEW';
968 ln_payroll_id per_all_assignments_f.payroll_id%type;
969 lb_savepoint_exists boolean default null;
970 lb_changed boolean default false;
971
972 lv_sal_rev_period_frequency
973 per_all_assignments_f.sal_review_period_frequency%TYPE;
974 lv_message varchar2(32000) default null;
975 ld_temp_date date default null;
976 ln_prev_salary2 number default null;
977
978
979 CURSOR lc_get_curr_asg_pay_basis_id IS
980 SELECT paf.payroll_id
981 ,paf.pay_basis_id
982 ,paf.business_group_id
983 ,paf.assignment_type
984 ,ppb.name old_salary_basis_name
985 ,ppb.pay_annualization_factor old_pay_annual_factor
986 FROM per_all_assignments_f paf
987 ,per_pay_bases ppb
988 WHERE assignment_id = p_assignment_id
989 and p_effective_date between effective_start_date
990 and effective_end_date
991 and paf.pay_basis_id = ppb.pay_basis_id(+);
992
993 -- 05/11/02 - Bug 2340234 Fix Begins
994 -- The following cursor was copied from
995 -- per_pay_proposals_populate.get_prev_salary procedure.
996 CURSOR lc_previous_pay IS
997 SELECT pro.proposed_salary_n
998 ,pro.change_date
999 FROM per_pay_proposals pro
1000 WHERE pro.assignment_id = p_assignment_id
1001 AND pro.change_date =(select max(pro2.change_date)
1002 from per_pay_proposals pro2
1003 where pro2.assignment_id = p_assignment_id
1004 and pro2.change_date < p_effective_date);
1005 -- 05/11/02 - Bug 2340234 Fix Begins
1006
1007 cursor csr_applicant_offer is
1008 select paf.pay_basis_id,
1009 paf.assignment_type,
1010 ppp.proposed_salary_n
1011 from
1012 per_all_assignments_f paf,
1013 per_pay_proposals ppp
1014 where paf.assignment_id = p_assignment_id
1015 and p_effective_date between paf.effective_start_date and paf.effective_end_date
1016 and ppp.assignment_id = paf.assignment_id
1017 and p_effective_date between ppp.change_date and ppp.date_to;
1018
1019 p_offered_salary_basis_id number;
1020 p_offered_asg_type varchar2(2);
1021
1022 -- Bug 2355929 Fix Begins
1023 lv_applicant_asg_type per_all_assignments_f.assignment_type%type
1024 default null;
1025
1026 -- Bug 2355929 Fix Ends
1027 ln_job_id number default null;
1028
1029 lv_tmp_currency PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE%TYPE;
1030 lv_tmp_salary_basis_name PER_PAY_BASES.NAME%TYPE;
1031 lv_tmp_pay_basis_name HR_LOOKUPS.MEANING%TYPE;
1032 lv_tmp_pay_basis PER_PAY_BASES.PAY_BASIS%TYPE;
1033 ln_tmp_pay_annual_factor PER_PAY_BASES.PAY_ANNUALIZATION_FACTOR%TYPE;
1034 lv_tmp_grade_basis PER_PAY_BASES.RATE_BASIS%TYPE;
1035 ln_tmp_grade_annual_factor PER_PAY_BASES.GRADE_ANNUALIZATION_FACTOR%TYPE;
1036 ln_tmp_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
1037 lv_tmp_uom PAY_INPUT_VALUES_F.UOM%TYPE;
1038 lv_tmp_currency_symbol FND_CURRENCIES_VL.SYMBOL%TYPE;
1039
1040
1041 BEGIN
1042
1043 hr_utility.set_location(' Entering:' || l_proc,5);
1044
1045 ld_get_defaults_date := p_effective_date;
1046
1047 -- Read from the database to determine if there is an existing
1048 -- assignment and also salary basis.
1049 --
1050 -- Bug 2355929 Fix Begins
1051 -- The input parm p_flow_mode will have the following values:
1052 -- i) REGISTRATION - hiring a new employee
1053 -- ii) HrCommonUpdateOab - hiring an applicant or updating an existing
1054 -- employee record.
1055 -- We need to set p_flow_mode to 'APPLICANT_HIRE' so that we will not
1056 -- call hr_applicant_api.hire_applicant in the ASG process_api when we
1057 -- are updating an existing employee record.
1058 -- If the flow mode is registration, don't read from the db. In a New Hire
1059 -- process, the Salary Basis added in the ASG page will be in the database.
1060 -- If we read from the db, the p_old_pay_basis_id will be set and will be the
1061 -- same as the new pay basis id. The change type will become NOCHANGE and
1062 -- validate_salary_details proc will issue the following errors:
1063 -- i) No previous approved pay proposal exists for this person.
1064 -- ii) Future dated proposals exist for this assignment.
1065 --
1066
1067 IF p_flow_mode IS NOT NULL and
1068 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
1069 THEN
1070 hr_utility.set_location(l_proc,10);
1071 NULL;
1072 ELSE
1073 hr_utility.set_location(l_proc,15);
1074 OPEN lc_get_curr_asg_pay_basis_id;
1075 FETCH lc_get_curr_asg_pay_basis_id into ln_payroll_id
1076 ,p_old_pay_basis_id
1077 ,ln_business_group_id
1078 ,lv_applicant_asg_type
1079 ,lv_old_salary_basis_name
1080 ,ln_old_pay_annual_factor;
1081 CLOSE lc_get_curr_asg_pay_basis_id;
1082
1083 ln_old_fte_factor := per_saladmin_utility.get_fte_factor(p_assignment_id,
1084 p_effective_date);
1085
1086 PER_PAY_PROPOSALS_POPULATE.GET_BASIS_DETAILS(p_effective_date => p_effective_date
1087 ,p_assignment_id => p_assignment_id
1088 ,p_currency => lv_tmp_currency
1089 ,p_salary_basis_name => lv_tmp_salary_basis_name
1090 ,p_pay_basis_name => lv_tmp_pay_basis_name
1091 ,p_pay_basis => lv_tmp_pay_basis
1092 ,p_pay_annualization_factor => ln_tmp_pay_annual_factor
1093 ,p_grade_basis => lv_tmp_grade_basis
1094 ,p_grade_annualization_factor => ln_tmp_grade_annual_factor
1095 ,p_element_type_id => ln_tmp_element_type_id
1096 ,p_uom => lv_tmp_uom);
1097
1098 lv_tmp_currency_symbol:= hr_salary2_web.get_currency_symbol(
1099 lv_tmp_currency,
1100 p_effective_date ) ;
1101
1102 p_old_currency_code := lv_tmp_currency;
1103 p_old_currency_symbol := lv_tmp_currency_symbol;
1104
1105 END IF;
1106
1107 IF lv_applicant_asg_type = 'A'
1108 THEN
1109 hr_utility.set_location(l_proc,20);
1110 -- zap the ln_payroll_id, lv_old_salary_basis_name and
1111 -- ln_old_pay_annual_factor to null
1112 -- Change the p_flow_mode to reflect APPLICANT_HIRE
1113 ln_payroll_id := null;
1114 p_old_pay_basis_id := null;
1115 lv_old_salary_basis_name := null;
1116 ln_old_pay_annual_factor := null;
1117 ln_old_fte_factor := null;
1118 p_flow_mode := hr_process_assignment_ss.g_hire_an_applicant;
1119 END IF;
1120
1121 -- Bug 2355929 Fix Ends
1122
1123 IF p_asg_txn_step_id IS NOT NULL
1124 THEN
1125 hr_utility.set_location(l_proc,25);
1126 -- In a chained process navigated from the Assignment page.
1127 -- Get the pay_basis_id from the txn step
1128 ln_transaction_step_id := p_asg_txn_step_id;
1129 ln_txn_pay_basis_id := hr_transaction_api.get_number_value
1130 (p_transaction_step_id => ln_transaction_step_id
1131 ,p_name => 'P_PAY_BASIS_ID');
1132 p_new_pay_basis_id := ln_txn_pay_basis_id;
1133
1134 ln_payroll_id := hr_transaction_api.get_number_value
1135 (p_transaction_step_id => ln_transaction_step_id
1136 ,p_name => 'P_PAYROLL_ID');
1137
1138 lv_sal_rev_period_frequency := hr_transaction_api.get_varchar2_value
1139 (p_transaction_step_id => ln_transaction_step_id
1140 ,p_name => 'P_SAL_REVIEW_PERIOD_FREQUENCY');
1141
1142 ln_job_id := hr_transaction_api.get_number_value
1143 (p_transaction_step_id => ln_transaction_step_id
1144 ,p_name => 'P_JOB_ID');
1145
1146 -- 05/03/2002 Fix Begins
1147 -- Issue an error when the old and new pay basis id are null regardless
1148 -- it's a New Hire or Applicant Hire.
1149 IF p_old_pay_basis_id IS NULL and p_new_pay_basis_id IS NULL
1150 THEN
1151 hr_utility.set_location(l_proc,30);
1152 -- it's an error, we cannot proceed further
1153 -- commenting this for bug 10208443.
1154 /*
1155 lv_msg_text := lv_msg_text ||
1156 hr_util_misc_web.return_msg_text(
1157 p_message_name =>'HR_289855_SAL_ASS_NOT_SAL_ELIG'
1158 ,p_Application_id =>'PER'); */
1159
1160 lv_msg_text := 'HR_289855_SAL_ASS_NOT_SAL_ELIG';
1161
1162
1163 p_excep_message := lv_msg_text;
1164 raise lv_no_sal_basis_excep;
1165 END IF;
1166 -- 05/03/2002 Fix Ends
1167 ELSE
1168 hr_utility.set_location(l_proc,35);
1169 -- 2 possibilities:
1170 -- 1) On re-entry of a Save For Later transaction where the user last
1171 -- stopped at the Pay Rate page, asg_txn_step_id is not known now
1172 -- OR
1173 -- 2) In Pay Rate standalone mode, thus there is no asg_txn_step_id.
1174 --
1175 -- Need to see if an asg txn step id exists or not.
1176 hr_assignment_common_save_web.get_step
1177 (p_item_type => p_item_type
1178 ,p_item_key => p_item_key
1179 ,p_api_name => g_asg_api_name
1180 ,p_transaction_step_id => ln_transaction_step_id
1181 ,p_transaction_id => ln_transaction_id);
1182
1183 IF nvl(ln_transaction_step_id, -1) > 0
1184 THEN
1185 hr_utility.set_location(l_proc,40);
1186 -- It's a Save For Later transaction, Pay Rate was being chained
1187 -- to the Assignment page but user last stopped at the Pay Rate
1188 -- page.
1189 -- Get the value of pay basis id from the txn table.
1190 p_asg_txn_step_id := ln_transaction_step_id;
1191
1192 ln_txn_pay_basis_id := hr_transaction_api.get_number_value
1193 (p_transaction_step_id => ln_transaction_step_id
1194 ,p_name => 'P_PAY_BASIS_ID');
1195
1196 ln_payroll_id := hr_transaction_api.get_number_value
1197 (p_transaction_step_id => ln_transaction_step_id
1198 ,p_name => 'P_PAYROLL_ID');
1199
1200 p_new_pay_basis_id := ln_txn_pay_basis_id;
1201
1202 lv_sal_rev_period_frequency := hr_transaction_api.get_varchar2_value
1203 (p_transaction_step_id => ln_transaction_step_id
1204 ,p_name => 'P_SAL_REVIEW_PERIOD_FREQUENCY');
1205
1206
1207 ln_job_id := hr_transaction_api.get_number_value
1208 (p_transaction_step_id => ln_transaction_step_id
1209 ,p_name => 'P_JOB_ID');
1210
1211 -- 05/03/2002 Fix Begins
1212 -- Issue an error when the old and new pay basis id are null regardless
1213 -- it's a New Hire or Applicant Hire.
1214 IF p_old_pay_basis_id IS NULL and p_new_pay_basis_id IS NULL
1215 THEN
1216 hr_utility.set_location(l_proc,45);
1217 -- it's an error, we cannot proceed further
1218 lv_msg_text := lv_msg_text ||
1219 hr_util_misc_web.return_msg_text(
1220 p_message_name =>'HR_289855_SAL_ASS_NOT_SAL_ELIG'
1221 ,p_Application_id =>'PER');
1222 p_excep_message := lv_msg_text;
1223 raise lv_no_sal_basis_excep;
1224 END IF;
1225 -- 05/03/2002 Fix Ends
1226 ELSE
1227 hr_utility.set_location(l_proc,50);
1228 -- There is no passed ASG transaction step id and none in
1229 -- the database.
1230 -- It's Pay Rate standalone. Get the pay basis id from the asg
1231 -- rec in the database.
1232 -- Set the old and new pay basis id to be the same.
1233 IF p_old_pay_basis_id IS NULL
1234 THEN
1235 hr_utility.set_location(l_proc,55);
1236 -- it's an error, we cannot proceed further
1237 lv_msg_text := lv_msg_text ||
1238 hr_util_misc_web.return_msg_text(
1239 p_message_name =>'HR_289855_SAL_ASS_NOT_SAL_ELIG'
1240 ,p_Application_id =>'PER');
1241 p_excep_message := lv_msg_text;
1242 raise lv_no_sal_basis_excep;
1243 ELSE
1244 hr_utility.set_location(l_proc,60);
1245 -- p_old_pay_basis_id is already set
1246 p_new_pay_basis_id := p_old_pay_basis_id;
1247 END IF;
1248 END IF;
1249 END IF; -- end p_asg_txn_step_id is NOT null
1250
1251 open csr_applicant_offer;
1252 fetch csr_applicant_offer into p_offered_salary_basis_id,
1253 p_offered_asg_type, p_offered_salary ;
1254 close csr_applicant_offer;
1255
1256 if (p_offered_salary_basis_id <> p_new_pay_basis_id OR p_offered_asg_type <> 'A') then
1257 p_offered_salary := null;
1258 end if;
1259
1260 IF p_new_pay_basis_id IS NOT NULL and
1261 nvl(p_old_pay_basis_id, -1) <> p_new_pay_basis_id
1262 THEN
1263 hr_utility.set_location(l_proc,65);
1264 IF p_old_pay_basis_id IS NULL
1265 THEN
1266 hr_utility.set_location(l_proc,70);
1267 -- Adding a new salary basis
1268 lv_salary_basis_change_type := 'NEW';
1269 ELSE
1270 hr_utility.set_location(l_proc,75);
1271 -- Changing an existing salary basis
1272 lv_salary_basis_change_type := 'CHANGE';
1273 END IF;
1274 ELSE
1275 hr_utility.set_location(l_proc,80);
1276 IF p_old_pay_basis_id = p_new_pay_basis_id
1277 THEN
1278 hr_utility.set_location(l_proc,85);
1279 -- An existing salary basis is already in the db, the user is trying to
1280 -- add pay proposal data.
1281 -- get the default values based on the existing assignment id
1282 lv_salary_basis_change_type := 'NOCHANGE';
1283 END IF;
1284 END IF;
1285
1286 IF lv_salary_basis_change_type = 'NEW' Or
1287 lv_salary_basis_change_type = 'CHANGE' Or
1288 nvl(ln_transaction_step_id, -1) > 0 -- bug# 2343933
1289 THEN
1290 hr_utility.set_location(l_proc,90);
1291 -- that means p_new_pay_basis_id IS NOT NULL and
1292 -- p_old_pay_basis_id <> p_new_pay_basis_id
1293 -- We need to simulate saving of the assignment data in the
1294 -- transaction table to the database because the get_defaults call
1295 -- only operates on data which is already in the database.
1296 -- A rollback will remove all simulation of saving ASG data.
1297
1298 savepoint check_asg_txn_data_save;
1299 lb_savepoint_exists := TRUE;
1300
1301 -- Must set the element warning to TRUE so that the ASG wrapper will not
1302 -- roll back the ASG changes if element_warning = 'TRUE' whenever there
1303 -- is element entries changed.
1304 --HR_PROCESS_ASSIGNMENT_SS.PROCESS_API
1305 -- (p_validate => FALSE
1306 -- ,p_transaction_step_id => ln_transaction_step_id
1307 -- ,p_flow_mode => p_flow_mode); -- 2355929 Fix
1308 -- Bug 2547283: need to update person info and asg info.
1309 IF NOT (p_flow_mode IS NOT NULL and
1310 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration)
1311 THEN
1312 hr_utility.set_location(l_proc,95);
1313 if p_proc_sel_txn is null then
1314 hr_new_user_reg_ss.process_selected_transaction
1315 (p_item_type => p_item_type,
1316 p_item_key => p_item_key);
1317 end if;
1318 END IF;
1319 END IF;
1320
1321 -- Call my_get_defaults first because we need to pass the new element entery
1322 -- to the validate salary procedure.
1323 my_get_defaults
1324 (p_assignment_id => p_assignment_id
1325 ,p_date => ld_get_defaults_date
1326 ,p_business_group_id => ln_business_group_id
1327 ,p_currency => lv_currency
1328 ,p_format_string => lv_format_string
1329 ,p_salary_basis_name => lv_salary_basis_name
1330 ,p_pay_basis_name => lv_pay_basis_name
1331 ,p_pay_basis => lv_pay_basis
1332 ,p_grade_basis => lv_grade_basis
1333 ,p_pay_annualization_factor => lv_pay_annualization_factor
1334 ,p_fte_factor => lv_fte_factor
1335 ,p_grade => lv_grade
1336 ,p_grade_annualization_factor => lv_grade_annualization_factor
1337 ,p_minimum_salary => ln_minimum_salary
1338 ,p_maximum_salary => ln_maximum_salary
1339 ,p_midpoint_salary => ln_midpoint_salary
1340 ,p_prev_salary => ln_prev_salary
1341 ,p_last_change_date => ld_last_change_date
1342 ,p_element_entry_id => ln_element_entry_id
1343 ,p_basis_changed => ln_basis_changed
1344 ,p_uom => lv_uom
1345 ,p_grade_uom => lv_grade_uom
1346 ,p_change_amount => ln_change_amount
1347 ,p_change_percent => ln_change_percent
1348 ,p_quartile => ln_quartile
1349 ,p_comparatio => ln_comparatio
1350 ,p_last_pay_change => lv_last_pay_change
1351 ,p_flsa_status => lv_flsa_status
1352 ,p_currency_symbol => lv_currency_symbol
1353 ,p_precision => ln_precision
1354 ,p_job_id => ln_job_id);
1355
1356 -- Validate Salary Details first
1357 validate_salary_details (p_assignment_id => p_assignment_id
1358 ,p_bg_id => ln_business_group_id
1359 ,p_effective_date => to_char(p_effective_date
1360 ,'RRRR-MM-DD')
1361 ,p_payroll_id => ln_payroll_id
1362 ,p_old_pay_basis_id => p_old_pay_basis_id
1363 ,p_new_pay_basis_id => p_new_pay_basis_id
1364 ,excep_message => lv_excep_message
1365 ,p_pay_proposal_id => ln_pay_proposal_id
1366 ,p_current_salary => ln_current_salary
1367 ,p_ovn => ln_proposal_ovn
1368 ,p_api_mode => lv_api_mode
1369 ,p_warning_message => lv_warning_message
1370 ,p_asg_type => p_offered_asg_type);
1371
1372 -- 05/09/02 Bug 2367833 Fix Begins
1373 -- When the lv_salary_basis_change_type = 'NOCHANGE', we need to check that
1374 -- if there is existence of pay proposal data. If there is none, we need
1375 -- to set lv_salary_basis_change_type to 'NOCHANGE_NOPAYPROPOSAL' so that
1376 -- the Pay Rate page will disable the radio button for Multiple Components
1377 -- in the Proposed Pay Rate region.
1378 IF lv_salary_basis_change_type = 'NOCHANGE' and
1379 ln_pay_proposal_id is NULL
1380 THEN
1381 hr_utility.set_location(l_proc,100);
1382 lv_salary_basis_change_type := 'NOCHANGE_NOPAYPROPOSAL';
1383 END IF;
1384 -- 05/09/02 Bug 2367833 Fix Ends
1385
1386 --4002387 start
1387 IF lv_salary_basis_change_type = 'CHANGE' and
1388 ln_pay_proposal_id is NULL
1389 THEN
1390 hr_utility.set_location(l_proc,100);
1391 lv_salary_basis_change_type := 'CHANGE_NOPAYPROPOSAL';
1392 END IF;
1393 --4002387 end
1394
1395 -- Need to simulate the core api per_pyp_bus.derive_next_sal_perf_date
1396 -- to check the Salary Review Period Frequency when there is an ASG txn
1397 -- step id exists. The Salary Review Period Frequency is entered in ASG
1398 -- page but the ASG api validates against the lookup code only but the
1399 -- Pay Rate api restricts the lookup code to be only Year, Month, Week and
1400 -- Day. The Frequency lookup code also has Quarter Minute and Hour.
1401 -- When a user select Quarter Minute or Hour in ASG Salary Review Period
1402 -- Frequency, the user won't get an error in the ASG api but will get the
1403 -- following error on reentry of the Pay Rate page in a Return for Correction: -- HR_51258_INVL_FREQ_PERIOD / HR_51258_PYP_INVAL_FREQ_PERIOD.
1404 -- The per_pyp_bus (pepyprhi.pkb) only checks the sal_review_period_frequency
1405 -- but not the perf_review_period_frequency. So, we will just check the
1406 -- sal_review_period_frequency only.
1407 BEGIN
1408 IF lv_sal_rev_period_frequency IS NOT NULL
1409 THEN
1410 hr_utility.set_location(l_proc,105);
1411 -- Check the frequency to see if it is 'Y', 'M', 'W' or 'D'. I can't
1412 -- call the function derive_next_sal_perf_date directly because it is
1413 -- private.
1414 IF lv_sal_rev_period_frequency = 'Y' OR
1415 lv_sal_rev_period_frequency = 'M' OR
1416 lv_sal_rev_period_frequency = 'W' OR
1417 lv_sal_rev_period_frequency = 'D'
1418 THEN
1419 -- it's ok
1420 hr_utility.set_location(l_proc,110);
1421 NULL;
1422 ELSE
1423 hr_utility.set_location(l_proc,115);
1424 lv_message := hr_util_misc_web.return_msg_text(
1425 p_message_name =>'HR_51258_PYP_INVAL_FREQ_PERIOD'
1426 ,p_Application_id =>'PAY');
1427 END IF;
1428 END IF;
1429 END;
1430
1431 IF lv_message IS NOT NULL
1432 THEN
1433 hr_utility.set_location(l_proc,120);
1434 lv_message := lv_message || ' ';
1435 END IF;
1436
1437 p_warning_message := lv_warning_message;
1438 p_excep_message := lv_message || lv_excep_message;
1439 --
1440 -- 04/23/02 Salary Basis Enhancement Change Begins
1441 IF lv_salary_basis_change_type = 'CHANGE'
1442 THEN
1443 hr_utility.set_location(l_proc,125);
1444 -- If it is a change to a salary basis, we need to derive previous salary
1445 -- again. This is because in my_get_defaults which calls
1446 -- per_pay_proposals_populate.get_defaults, that procedure will set prev
1447 -- salary to null when the old and new salary basis's element type id is
1448 -- different. The Current Pay Rate region will show zero in the Salary
1449 -- and Annual Equivalent columns.
1450 -- This zapping of prev salary to null happens in
1451 -- per_pay_proposals_populate.get_prev_salary procedure.
1452
1453 OPEN lc_previous_pay;
1454 FETCH lc_previous_pay into ln_prev_salary2
1455 ,ld_last_change_date;
1456 CLOSE lc_previous_pay;
1457
1458 IF ln_prev_salary is NULL and ln_prev_salary2 IS NOT NULL
1459 THEN
1460 hr_utility.set_location(l_proc,130);
1461 -- Element type id is changed because in the procedure
1462 -- per_pay_proposals_populate.get_prev_salary, it will zap the
1463 -- previous salary to null if element type id are not equal between
1464 -- the old and new salary basis.
1465 p_element_type_id_changed := 'Y';
1466 ln_prev_salary := ln_prev_salary2;
1467 ELSE
1468 hr_utility.set_location(l_proc,135);
1469 p_element_type_id_changed := 'N';
1470 END IF;
1471 END IF;
1472
1473 --4002387 start
1474 BEGIN
1475 SELECT pay_basis INTO p_old_pay_basis
1476 FROM per_pay_bases
1477 WHERE pay_basis_id = p_old_pay_basis_id;
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 p_old_pay_basis := '';
1481 END;
1482
1483 p_old_to_new_currency_rate := hr_currency_pkg.get_rate_sql(
1484 p_from_currency => lv_tmp_currency,
1485 p_to_currency => lv_currency,
1486 p_conversion_date => p_effective_date,
1487 p_rate_type => hr_currency_pkg.get_rate_type (
1488 p_business_group_id => ln_business_group_id,
1489 p_conversion_date => p_effective_date,
1490 p_processing_type => 'P'));
1491
1492 --4002387 end
1493
1494 IF lb_savepoint_exists
1495 THEN
1496 hr_utility.set_location(l_proc,140);
1497 rollback to check_asg_txn_data_save;
1498 lb_savepoint_exists := FALSE;
1499 END IF;
1500
1501 p_asg_txn_step_id := ln_transaction_step_id;
1502 p_get_defaults_date := ld_get_defaults_date;
1503 p_business_group_id := ln_business_group_id;
1504 p_currency := lv_currency;
1505 p_format_string := lv_format_string;
1506 p_salary_basis_name := lv_salary_basis_name;
1507 p_pay_basis_name := lv_pay_basis_name;
1508 p_pay_basis := lv_pay_basis;
1509 p_grade_basis := lv_grade_basis;
1510 p_pay_annualization_factor := lv_pay_annualization_factor;
1511 p_fte_factor := lv_fte_factor;
1512 p_grade := lv_grade;
1513 p_grade_annualization_factor := lv_grade_annualization_factor;
1514 p_minimum_salary := ln_minimum_salary;
1515 p_maximum_salary := ln_maximum_salary;
1516 p_midpoint_salary := ln_midpoint_salary;
1517 p_prev_salary := ln_prev_salary;
1518 p_last_change_date := ld_last_change_date;
1519 p_element_entry_id := ln_element_entry_id;
1520 p_basis_changed := ln_basis_changed;
1521 p_uom := lv_uom;
1522 p_grade_uom := lv_grade_uom;
1523 p_change_amount := ln_change_amount;
1524 p_change_percent := ln_change_percent;
1525 p_quartile := ln_quartile;
1526 p_comparatio := ln_comparatio;
1527 p_last_pay_change := lv_last_pay_change;
1528 p_flsa_status := lv_flsa_status;
1529 p_currency_symbol := lv_currency_symbol;
1530 p_precision := ln_precision;
1531 p_pay_proposal_id := ln_pay_proposal_id;
1532 p_current_salary := ln_current_salary;
1533 p_proposal_ovn := ln_proposal_ovn;
1534 p_api_mode := lv_api_mode;
1535 p_old_pay_annualization_factor := ln_old_pay_annual_factor;
1536 p_old_fte_factor := ln_old_fte_factor;
1537 p_old_salary_basis_name := lv_old_salary_basis_name;
1538 p_salary_basis_change_type := lv_salary_basis_change_type;
1539
1540 hr_utility.set_location(' Leaving:' || l_proc,145);
1541
1542 EXCEPTION
1543 WHEN lv_no_sal_basis_excep THEN
1544 hr_utility.set_location(' Leaving:' || l_proc,555);
1545 -- The Java caller PayRateAMImpl.java will throw the exception.
1546 null;
1547
1548 WHEN OTHERS THEN
1549 IF lb_savepoint_exists
1550 THEN
1551 rollback to check_asg_txn_data_save;
1552 END IF;
1553 hr_utility.set_location(' Leaving:' || l_proc,560);
1554 RAISE;
1555
1556 END check_asg_txn_data;
1557
1558
1559 PROCEDURE MY_GET_DEFAULTS(p_assignment_id IN NUMBER
1560 ,p_job_id IN NUMBER
1561 ,p_date IN OUT NOCOPY DATE
1562 ,p_business_group_id OUT NOCOPY NUMBER
1563 ,p_currency OUT NOCOPY VARCHAR2
1564 ,p_format_string OUT NOCOPY VARCHAR2
1565 ,p_salary_basis_name OUT NOCOPY VARCHAR2
1566 ,p_pay_basis_name OUT NOCOPY VARCHAR2
1567 ,p_pay_basis OUT NOCOPY VARCHAR2
1568 ,p_grade_basis OUT NOCOPY VARCHAR2
1569 ,p_pay_annualization_factor OUT NOCOPY NUMBER
1570 ,p_fte_factor OUT NOCOPY NUMBER
1571 ,p_grade OUT NOCOPY VARCHAR2
1572 ,p_grade_annualization_factor OUT NOCOPY NUMBER
1573 ,p_minimum_salary OUT NOCOPY NUMBER
1574 ,p_maximum_salary OUT NOCOPY NUMBER
1575 ,p_midpoint_salary OUT NOCOPY NUMBER
1576 ,p_prev_salary OUT NOCOPY NUMBER
1577 ,p_last_change_date OUT NOCOPY DATE
1578 ,p_element_entry_id OUT NOCOPY NUMBER
1579 ,p_basis_changed OUT NOCOPY NUMBER
1580 ,p_uom OUT NOCOPY VARCHAR2
1581 ,p_grade_uom OUT NOCOPY VARCHAR2
1582 ,p_change_amount out nocopy number
1583 ,p_change_percent out nocopy number
1584 , p_quartile out nocopy number
1585 , p_comparatio out nocopy number
1586 , p_last_pay_change out nocopy varchar2
1587 , p_flsa_status out nocopy varchar2
1588 , p_currency_symbol out nocopy varchar2
1589 , p_precision out nocopy number
1590 ) IS
1591
1592 l_proc varchar2(200) := g_package || 'MY_GET_DEFAULTS';
1593 ln_percent varchar2(300) ;
1594 ln_ann_sal number;
1595
1596 cursor grade_basis is
1597 SELECT ppb.rate_basis
1598 FROM PER_ALL_ASSIGNMENTS_F PAF, per_pay_bases ppb
1599 WHERE PAF.ASSIGNMENT_ID=p_assignment_id
1600 AND p_date BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
1601 and paf.pay_basis_id = ppb.pay_basis_id;
1602
1603 cursor grade_details is
1604 SELECT fnd_number.canonical_to_number(PGR.MINIMUM)
1605 , fnd_number.canonical_to_number(PGR.MAXIMUM)
1606 , fnd_number.canonical_to_number(PGR.MID_VALUE)
1607 FROM PER_PAY_BASES PPB
1608 , PAY_GRADE_RULES_F PGR
1609 , PER_ALL_ASSIGNMENTS_F ASG
1610 WHERE ASG.ASSIGNMENT_ID=p_assignment_id
1611 AND PPB.PAY_BASIS_ID=ASG.PAY_BASIS_ID
1612 AND PPB.RATE_ID=PGR.RATE_ID
1613 AND ASG.GRADE_ID=PGR.GRADE_OR_SPINAL_POINT_ID
1614 AND p_date BETWEEN asg.effective_start_date
1615 AND asg.effective_end_date
1616 AND p_date BETWEEN pgr.effective_start_date
1617 AND pgr.effective_end_date;
1618
1619 BEGIN
1620 hr_utility.set_location(' Entering:' || l_proc,5);
1621 per_pay_proposals_populate.get_defaults(
1622 p_assignment_id => p_assignment_id ,
1623 p_date => p_date,
1624 p_business_group_id => p_business_group_id,
1625 p_currency => p_currency,
1626 p_format_string => p_format_string ,
1627 p_salary_basis_name => p_salary_basis_name ,
1628 p_pay_basis_name => p_pay_basis_name ,
1629 p_pay_basis => p_pay_basis,
1630 p_pay_annualization_factor => p_pay_annualization_factor,
1631 p_grade => p_grade,
1632 p_grade_annualization_factor => p_grade_annualization_factor,
1633 p_minimum_salary => p_minimum_salary,
1634 p_maximum_salary => p_maximum_salary,
1635 p_midpoint_salary => p_midpoint_salary,
1636 p_prev_salary => p_prev_salary ,
1637 p_last_change_date => p_last_change_date,
1638 p_element_entry_id => p_element_entry_id,
1639 p_basis_changed => basischanged,
1640 p_uom => p_uom,
1641 p_grade_uom => p_grade_uom );
1642
1643 open grade_basis;
1644 fetch grade_basis into p_grade_basis;
1645 close grade_basis;
1646
1647 if (p_grade_basis = 'HOURLY' and p_pay_basis = 'HOURLY') then
1648 open grade_details;
1649 fetch grade_details into p_minimum_salary, p_maximum_salary, p_midpoint_salary;
1650 close grade_details;
1651 end if;
1652
1653 p_basis_changed := hr_java_conv_util_ss.get_number(p_boolean => basischanged );
1654
1655 p_fte_factor := per_saladmin_utility.get_fte_factor(p_assignment_id,p_date);
1656
1657 if ( p_midpoint_salary = 0) then
1658 p_comparatio := 0;
1659 elsif (p_grade_basis = 'HOURLY' and p_pay_basis = 'HOURLY') then
1660 p_comparatio := round(( (100*p_prev_salary)/p_midpoint_salary), 3 );
1661 elsif ((fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') is null OR
1662 fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') = 'Y') AND p_pay_basis <> 'HOURLY') then
1663 p_comparatio := round(( (100*p_prev_salary *p_pay_annualization_factor)/
1664 (p_midpoint_salary*p_fte_factor)), 3 );
1665 else
1666 p_comparatio :=round(( (100*p_prev_salary *
1667 p_pay_annualization_factor)/
1668 p_midpoint_salary), 3 );
1669 end if;
1670
1671 if (p_grade_basis = 'HOURLY' and p_pay_basis = 'HOURLY') then
1672 ln_ann_sal := p_prev_salary;
1673 elsif ((fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') is null OR
1674 fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') = 'Y') AND p_pay_basis <> 'HOURLY') then
1675 ln_ann_sal := (p_prev_salary * p_pay_annualization_factor)/p_fte_factor;
1676 else
1677 ln_ann_sal := p_prev_salary * p_pay_annualization_factor;
1678 end if;
1679
1680 p_quartile := get_quartile (
1681 ln_ann_sal,
1682 p_minimum_salary ,
1683 p_maximum_salary ,
1684 p_midpoint_salary );
1685
1686 p_currency_symbol:= hr_salary2_web.get_currency_symbol(
1687 p_currency,
1688 p_date ) ;
1689
1690 p_precision := get_precision(
1691 p_uom ,
1692 p_currency,
1693 p_date );
1694
1695 p_last_pay_change := get_last_pay_change (
1696 p_assignment_id ,
1697 p_business_group_id,
1698 p_precision,
1699 ln_percent ) ;
1700
1701 p_last_pay_change := p_last_pay_change || ' (' || ln_percent || '%)';
1702
1703
1704 p_flsa_status := get_flsa_status
1705 ( p_assignment_id ,
1706 p_business_group_id ,
1707 p_date ,
1708 p_job_id ) ;
1709
1710
1711
1712 hr_utility.set_location(' Leaving:' || l_proc,10);
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 hr_utility.set_location(' Leaving:' || l_proc,555);
1717 RAISE;
1718
1719 END my_get_defaults;
1720
1721 -------------------------------------------------
1722 -- Function
1723 -- get_rate_type
1724 --
1725 --
1726 -- Purpose
1727 --
1728 -- Returns the rate type given the business group, effective date and
1729 -- processing type
1730 --
1731 -- Current processing types are:-
1732 -- P - Payroll Processing
1733 -- R - General HRMS reporting
1734 -- I - Business Intelligence System
1735 --
1736 -- History
1737 -- 22/01/99 wkerr.uk Created
1738 --
1739 -- Argumnents
1740 -- p_business_group_id The business group
1741 -- p_effective_date The date for which to return the rate type
1742 -- p_processing_type The processing type of which to return the rate
1743 --
1744 -- Returns null if no rate type found
1745 --
1746 --
1747 FUNCTION get_rate_type (
1748 p_business_group_id NUMBER,
1749 p_conversion_date DATE,
1750 p_processing_type VARCHAR2 ) RETURN VARCHAR2 IS
1751 --
1752 l_proc varchar2(200) := g_package || 'get_rate_type';
1753 l_row_name varchar2(30);
1754 l_value pay_user_column_instances_f.value%type;
1755 l_conversion_type varchar2(30);
1756 BEGIN
1757 --
1758 hr_utility.set_location(' Entering:' || l_proc,5);
1759 if p_processing_type = 'P' then
1760 hr_utility.set_location(l_proc,10);
1761 l_row_name := 'PAY' ;
1762 elsif p_processing_type = 'R' then
1763 hr_utility.set_location(l_proc,15);
1764 l_row_name := 'HRMS';
1765 elsif p_processing_type = 'I' then
1766 hr_utility.set_location(l_proc,20);
1767 l_row_name := 'BIS';
1768 else
1769 hr_utility.set_location(' Leaving:' || l_proc,25);
1770 return null;
1771 end if;
1772 --
1773 l_value := hruserdt.get_table_value(p_business_group_id,
1774 'EXCHANGE_RATE_TYPES',
1775 'Conversion Rate Type' ,
1776 l_row_name ,
1777 p_conversion_date) ;
1778 --
1779 -- l_value is a user_conversion_type
1780 -- we want to return the conversion_type, hence:
1781 --
1782 select conversion_type
1783 into l_conversion_type
1784 from gl_daily_conversion_types
1785 where user_conversion_type = l_value;
1786 --
1787 hr_utility.set_location(' Leaving:' || l_proc,30);
1788 return l_conversion_type;
1789 --
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 hr_utility.set_location(' Leaving:' || l_proc,555);
1793 RETURN null; -- Don't know what the problem was with the user the table.
1794 -- However don't want to percolate an exception from get_table_value
1795 -- Request from payroll team for this to be put in.
1796 END get_rate_type;
1797 -----------------------------------------------------
1798
1799 --
1800 --
1801 -- Function
1802 -- get_rate
1803 --
1804 -- Purpose
1805 -- Returns the rate between the two currencies for a given conversion
1806 -- date and rate type.
1807 --
1808 -- History
1809 -- 22-APR-1998 wkerr.uk Created
1810 --
1811 --
1812 --
1813 -- Arguments
1814 -- p_from_currency From currency
1815 -- p_to_currency To currency
1816 -- p_conversion_date Conversion date
1817 -- p_rate_type Rate Type
1818 --
1819 FUNCTION get_rate (
1820 p_from_currency VARCHAR2,
1821 p_to_currency VARCHAR2,
1822 p_conversion_date DATE,
1823 p_rate_type VARCHAR2) RETURN NUMBER IS
1824
1825
1826
1827 BEGIN
1828
1829 -- Check if both currencies are identical
1830 IF ( p_from_currency = p_to_currency ) THEN
1831
1832 return( 1 );
1833 END IF;
1834
1835
1836 RETURN gl_currency_api.get_rate(p_from_currency,
1837 p_to_currency,
1838 p_conversion_date,
1839 p_rate_type) ;
1840
1841 END get_rate;
1842
1843
1844
1845
1846 -- Function
1847 -- convert_amount
1848 --
1849 -- Purpose
1850 -- Returns the amount converted from the from currency into the
1851 -- to currency for a given conversion date and conversion type.
1852 -- The amount returned is rounded to the precision and minimum
1853 -- account unit of the to currency.
1854 --
1855 -- History
1856 -- 02-Jun-1998 wkerr.uk Created
1857 --
1858 --
1859 -- Arguments
1860 -- p_from_currency From currency
1861 -- p_to_currency To currency
1862 -- p_conversion_date Conversion date
1863 -- p_amount Amount to be converted from the from currency
1864 -- into the to currency
1865 -- p_rate_type Rate Type
1866 --
1867 FUNCTION convert_amount (
1868 p_from_currency VARCHAR2,
1869 p_to_currency VARCHAR2,
1870 p_conversion_date DATE,
1871 p_amount NUMBER,
1872 p_rate_type VARCHAR2) RETURN NUMBER IS
1873
1874
1875 BEGIN
1876
1877 -- Check if both currencies are identical
1878 IF ( p_from_currency = p_to_currency ) THEN
1879
1880 return( p_amount );
1881 END IF;
1882
1883 RETURN gl_currency_api.convert_amount(p_from_currency,
1884 p_to_currency,
1885 p_conversion_date,
1886 p_rate_type,
1887 p_amount);
1888 END convert_amount;
1889
1890 -- Start of Procedure start_transaction
1891 Procedure start_transaction(itemtype in varchar2
1892 ,itemkey in varchar2
1893 ,actid in number
1894 ,funmode in varchar2
1895 ,p_creator_person_id in number
1896 ,result out nocopy varchar2 ) is
1897 -- --------------------------------------------------------------------------
1898 -- declare local variables
1899 -- --------------------------------------------------------------------------
1900 l_proc varchar2(200) := g_package || 'start_transaction';
1901 l_transaction_privilege hr_api_transactions.transaction_privilege%type;
1902 l_transaction_id hr_api_transactions.transaction_id%type;
1903 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
1904
1905 -- l_person_id hr_api_transactions.creator_person_id%type := p_selected_person_id;
1906
1907 Cursor c_get_transaction_step_id
1908 is
1909 select
1910 transaction_step_id
1911 from
1912 hr_api_transaction_steps
1913 where
1914 transaction_id = l_transaction_id;
1915
1916 Begin
1917 hr_utility.set_location(' Entering:' || l_proc,5);
1918 if funmode = 'RUN' then
1919 hr_utility.set_location( l_proc,10);
1920 savepoint start_transaction;
1921
1922 -- check to see if the TRANSACTION_ID attribute has been created
1923 if hr_workflow_utility.item_attribute_exists(p_item_type => itemtype
1924 ,p_item_key => itemkey
1925 ,p_name => 'TRANSACTION_ID') then
1926
1927 -- the TRANSACTION_ID exists so ensure that it is null
1928
1929 if hr_transaction_ss.get_transaction_id(p_item_type => itemtype
1930 ,p_item_key => itemkey) is not null then
1931
1932 -- a current transaction is in progress we cannot overwrite it
1933 -- get the Transaction Step Id
1934 hr_utility.set_location( l_proc,15);
1935 l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type => itemtype
1936 ,p_item_key => itemkey);
1937 open c_get_transaction_step_id;
1938 fetch c_get_transaction_step_id into l_transaction_step_id;
1939 /*if c_get_transaction_step_id%found then
1940 close c_get_transaction_step_id;
1941 hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
1942 hr_utility.raise_error;
1943 end if;*/
1944 close c_get_transaction_step_id;
1945 hr_transaction_ss.delete_transaction_step(l_transaction_step_id,null,p_creator_person_id);
1946 end if;
1947
1948 else
1949 hr_utility.set_location( l_proc,20);
1950 -- the TRANSACTION_ID does not exist so create it
1951 wf_engine.additemattr(itemtype => itemtype
1952 ,itemkey => itemkey
1953 ,aname => 'TRANSACTION_ID');
1954
1955 end if;
1956
1957 -- check to see if the TRANSACTION_PRIVILEGE attribute has been created
1958 if not hr_workflow_utility.item_attribute_exists(p_item_type => itemtype
1959 ,p_item_key => itemkey
1960 ,p_name => 'TRANSACTION_PRIVILEGE') then
1961
1962 -- the TRANSACTION_PRIVILEGE does not exist so create it
1963 hr_utility.set_location( l_proc,25);
1964 wf_engine.additemattr(itemtype => itemtype
1965 ,itemkey => itemkey
1966 ,aname => 'TRANSACTION_PRIVILEGE');
1967 end if;
1968
1969 -- get the TRANSACTION_PRIVILEGE
1970 l_transaction_privilege := wf_engine.getitemattrtext(itemtype => itemtype
1971 ,itemkey => itemkey
1972 ,aname => 'TRANSACTION_PRIVILEGE');
1973 -- check to see if the TRANSACTION_PRIVILEGE is null
1974 if l_transaction_privilege is null then
1975 hr_utility.set_location( l_proc,30);
1976 -- default the TRANSACTION_PRIVILEGE to PRIVATE
1977 l_transaction_privilege := 'PRIVATE';
1978 wf_engine.setitemattrtext(itemtype => itemtype
1979 ,itemkey => itemkey
1980 ,aname => 'TRANSACTION_PRIVILEGE'
1981 ,avalue => l_transaction_privilege);
1982 end if;
1983
1984 -- call the BP API to create the transaction
1985 hr_transaction_api.create_transaction(p_validate => false
1986 ,p_creator_person_id => p_creator_person_id
1987 ,p_transaction_privilege => l_transaction_privilege
1988 ,p_transaction_id => l_transaction_id );
1989 -- set the TRANSACTION_ID
1990 wf_engine.setitemattrnumber(itemtype => itemtype
1991 ,itemkey => itemkey
1992 ,aname => 'TRANSACTION_ID'
1993 ,avalue => l_transaction_id);
1994
1995 -- transaction has been successfully created so commit and return success
1996 -- commit;
1997 result := 'SUCCESS';
1998
1999 elsif funmode = 'CANCEL' then
2000 hr_utility.set_location( l_proc,35);
2001 null;
2002 end if;
2003
2004 hr_utility.set_location(' Leaving:' || l_proc,40);
2005
2006 Exception
2007 when others then
2008 rollback to start_transaction;
2009 hr_utility.set_location(' Leaving:' || l_proc,555);
2010 raise;
2011 End start_transaction;
2012
2013 -- End of Procedure start_transaction
2014
2015 /********************************************************/
2016 /**** Implementation change using Oracle Object Types ***/
2017 /********************************************************/
2018
2019 /** Procedure called from Actions Page to derive
2020 Effective Date **/
2021
2022 PROCEDURE validate_salary_details (
2023 p_assignment_id IN VARCHAR2,
2024 p_effective_date IN date DEFAULT NULL,
2025 p_item_type IN VARCHAR2 DEFAULT NULL,
2026 p_item_key IN VARCHAR2 DEFAULT NULL,
2027 excep_message OUT NOCOPY VARCHAR2,
2028 p_pay_proposal_id OUT NOCOPY NUMBER,
2029 p_current_salary OUT NOCOPY NUMBER,
2030 p_ovn OUT NOCOPY NUMBER,
2031 p_api_mode OUT NOCOPY VARCHAR2,
2032 p_proposal_change_date OUT NOCOPY DATE
2033 )
2034 IS
2035
2036 l_proc varchar2(200) := g_package || 'validate_salary_details';
2037 Cursor c_assignment IS
2038 SELECT
2039 paf.BUSINESS_GROUP_ID,
2040 paf.payroll_id
2041 FROM per_all_assignments_f paf
2042 WHERE paf.assignment_id = p_assignment_id
2043 AND NVL(p_effective_date, TRUNC(SYSDATE)) BETWEEN
2044 paf.effective_start_date
2045 AND paf.effective_end_date
2046 AND assignment_type = 'E';
2047
2048
2049 Cursor c_last_salary (p_bg_id VARCHAR2) IS
2050 Select pay_proposal_id ,
2051 change_date ,
2052 next_sal_review_date,
2053 next_perf_review_date ,
2054 proposal_reason,
2055 proposed_salary_n,
2056 approved,
2057 review_date,
2058 multiple_components,
2059 object_version_number
2060 from PER_PAY_PROPOSALS
2061 Where assignment_id = p_assignment_id
2062 AND business_group_id = p_bg_id
2063 order by change_date desc ;
2064
2065
2066 l_pay_proposal_id NUMBER ;
2067 l_approved VARCHAR2(1) ;
2068 l_change_date DATE ;
2069 l_next_sal_review_date DATE;
2070 l_next_perf_Review_date DATE ;
2071 l_proposal_reason VARCHAR2(30) ;
2072 l_current_salary NUMBER ;
2073 l_review_date DATE ;
2074 l_multiple_components Varchar2(1) ;
2075 ln_ovn NUMBER ;
2076 lv_system_status
2077 per_assignment_status_types.per_system_status%TYPE;
2078 lv_payroll_status per_time_periods.status%TYPE;
2079 lv_exists VARCHAR2(1);
2080 ld_payroll_start_date DATE ;
2081 message VARCHAR2(500);
2082 temp VARCHAR2(100);
2083 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
2084 l_bg_id per_all_assignments_f.business_group_id%TYPE;
2085 l_person_type VARCHAR2(5) := 'E';
2086 BEGIN
2087
2088 hr_utility.set_location(' Entering:' || l_proc,5);
2089 begin
2090 if (p_item_type is not null) and (p_item_key is not null)
2091 then
2092 hr_utility.set_location(l_proc,10);
2093 l_person_type := wf_engine.GetItemAttrText(itemtype => p_item_type,
2094 itemkey => p_item_key,
2095 aname => 'HR_SELECTED_PERSON_TYPE_ATTR');
2096 end if;
2097 exception
2098 when others then
2099 l_person_type := 'E';
2100 end;
2101
2102 if l_person_type <> 'C'
2103 then
2104
2105 hr_utility.set_location(l_proc,15);
2106 Open c_assignment ;
2107 fetch c_assignment into l_bg_id, l_payroll_id;
2108 IF (c_assignment%NOTFOUND) THEN
2109 message := message || hr_util_misc_web.return_msg_text(
2110 p_message_name=>'HR_PR_INI_MSG05',
2111 p_Application_id=>'PER');
2112 message := message || '( ' || p_assignment_id || ',' || p_effective_date || ')';
2113 message := message || ' ';
2114 END IF;
2115 close c_assignment;
2116
2117
2118 Open c_last_Salary (l_bg_id) ;
2119 fetch c_last_Salary into l_pay_Proposal_Id ,
2120 l_Change_Date ,
2121 l_next_Sal_Review_Date,
2122 l_Next_Perf_Review_Date ,
2123 l_Proposal_Reason,
2124 l_Current_Salary,
2125 l_Approved,
2126 l_Review_Date,
2127 l_Multiple_Components,
2128 ln_ovn ;
2129
2130 IF (c_last_salary%NOTFOUND) OR (l_pay_proposal_id IS NULL )
2131 -- first proposal can not be created
2132 THEN
2133 hr_utility.set_location(l_proc,20);
2134 -- No previous approved pay proposal exists for this person.
2135 message := message || hr_util_misc_web.return_msg_text(
2136 p_message_name=>'HR_PR_INI_MSG01',
2137 p_Application_id=>'PER');
2138
2139 message := message || ' ';
2140 END IF ;
2141 close c_last_Salary ;
2142
2143
2144 -- check if a valid payroll exist for the assignment
2145 -- validation 1
2146 -- check if this proposal is approved
2147 -- if not approved , raise error
2148
2149 IF l_Approved <>'Y'
2150 THEN
2151 hr_utility.set_location(l_proc,25);
2152 message := message || hr_util_misc_web.return_msg_text(
2153 p_message_name=>'HR_PR_INI_MSG02',
2154 p_Application_id=>'PER');
2155 message := message || ' ';
2156 END IF ;
2157
2158 -- check if eligible for salary basis/element
2159 IF (NOT check_ele_eligibility(p_assignment_id,
2160 to_char(p_effective_date,'RRRR-MM-DD'))) THEN
2161 hr_utility.set_location(l_proc,30);
2162 message := message || hr_util_misc_web.return_msg_text(
2163 p_message_name=>'HR_13016_SAL_ELE_NOT_ELIG',
2164 p_Application_id=>'PER');
2165 message := message || ' ';
2166 END IF;
2167
2168 -- check that last_change date is <= effective date
2169 -- if equal to effective date we are in correction mode
2170
2171 IF l_change_date is not null THEN
2172 hr_utility.set_location(l_proc,35);
2173 p_proposal_change_date := l_change_date + 1;
2174 --message := message || hr_util_misc_web.return_msg_text(
2175 -- p_message_name=>'HR_PR_INI_MSG04',
2176 -- p_Application_id=>'PER');
2177 --message := message || ' ' || p_proposal_change_date;
2178 END IF;
2179 excep_message := message;
2180 p_pay_proposal_id := l_pay_Proposal_Id;
2181 p_current_salary := l_Current_Salary;
2182 p_ovn := ln_ovn;
2183 p_api_mode := 'INSERT';
2184
2185 else
2186 hr_utility.set_location(l_proc,40);
2187 excep_message := null;
2188 p_pay_proposal_id := null;
2189 p_current_salary := null;
2190 p_ovn := null;
2191 p_api_mode := null;
2192 end if;
2193
2194 hr_utility.set_location(' Leaving:' || l_proc,45);
2195 END validate_salary_details;
2196
2197
2198 PROCEDURE validate_salary_details (
2199 p_assignment_id IN VARCHAR2,
2200 p_bg_id IN VARCHAR2,
2201 p_effective_date IN VARCHAR2,
2202 p_payroll_id IN VARCHAR2,
2203 p_old_pay_basis_id in number,
2204 p_new_pay_basis_id in number,
2205 excep_message OUT NOCOPY VARCHAR2,
2206 p_pay_proposal_id OUT NOCOPY NUMBER,
2207 p_current_salary OUT NOCOPY NUMBER,
2208 p_ovn OUT NOCOPY NUMBER,
2209 p_api_mode OUT NOCOPY VARCHAR2,
2210 p_warning_message OUT NOCOPY VARCHAR2,
2211 p_asg_type in varchar2 default 'E'
2212 )
2213 IS
2214
2215 l_proc varchar2(200) := g_package || 'validate_salary_details';
2216 Cursor c_last_salary IS
2217 Select pay_proposal_id ,
2218 change_date ,
2219 next_sal_review_date,
2220 next_perf_review_date ,
2221 proposal_reason,
2222 proposed_salary_n,
2223 approved,
2224 review_date,
2225 multiple_components,
2226 object_version_number
2227 from PER_PAY_PROPOSALS
2228 Where assignment_id = p_assignment_id
2229 AND business_group_id = p_bg_id
2230 order by change_date desc ;
2231
2232 CURSOR c_assignment_status IS
2233 SELECT ast.per_system_status,
2234 ptp.status
2235 FROM per_all_assignments_f asg,
2236 per_time_periods ptp,
2237 per_assignment_status_types ast
2238 WHERE asg.assignment_id = p_assignment_id
2239 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2240 AND to_date(p_effective_date,'RRRR-MM-DD')
2241 between asg.effective_start_date
2242 and asg.effective_end_date
2243 AND asg.payroll_id=ptp.payroll_id;
2244
2245 CURSOR c_future_assignment_changes IS
2246 Select 'Y'
2247 FROM per_all_assignments_f
2248 WHERE effective_start_date > to_date(p_effective_date,'RRRR-MM-DD')
2249 AND assignment_id = p_assignment_id
2250 AND business_group_id = p_bg_id ;
2251
2252 CURSOR c_grade_step_placement IS
2253 SELECT 'Y'
2254 FROM per_spinal_point_placements_f pspp
2255 WHERE pspp.assignment_id=p_assignment_id
2256 AND to_date(p_effective_date,'RRRR-MM-DD') between
2257 pspp.effective_start_date and pspp.effective_end_date ;
2258
2259 CURSOR c_payroll_period ( p_payroll_id NUMBER ) IS
2260 SELECT start_date
2261 FROM per_time_periods
2262 WHERE trunc(sysdate) between start_date and end_date
2263 AND payroll_id = p_payroll_id;
2264
2265 l_pay_proposal_id NUMBER ;
2266 l_approved VARCHAR2(1) ;
2267 l_change_date DATE ;
2268 l_next_sal_review_date DATE;
2269 l_next_perf_Review_date DATE ;
2270 l_proposal_reason VARCHAR2(30) ;
2271 l_current_salary NUMBER ;
2272 l_review_date DATE ;
2273 l_multiple_components Varchar2(1) ;
2274 ln_ovn NUMBER ;
2275 lv_system_status
2276 per_assignment_status_types.per_system_status%TYPE;
2277 lv_payroll_status per_time_periods.status%TYPE;
2278 lv_exists VARCHAR2(1);
2279 ld_payroll_start_date DATE ;
2280 message VARCHAR2(500) := '';
2281 temp VARCHAR2(100) := '';
2282 warn_message VARCHAR2(1000) := '';
2283
2284 -- Bug 2354730 Fix Begins
2285 lv_pay_proposal_rec_found boolean default false;
2286 -- Bug 2354730 Fix Ends
2287
2288 BEGIN
2289
2290 hr_utility.set_location(' Entering:' || l_proc,5);
2291
2292 -- get last salary details
2293 -- p_new_pay_basis_id always has a value
2294 -- p_old_pay_basis_id can be null
2295 -- Only validate if salary basis is the same or different but not when
2296 -- p_old_pay_basis_id is null, which is new.
2297
2298 --4002387 previously we were doing validations only when salary basis is not changed
2299 -- now we will do validations when previous proposal data exists
2300 -- no matter salary basis is changed or not
2301 IF p_old_pay_basis_id IS NOT NULL OR p_new_pay_basis_id IS NOT NULL
2302 THEN
2303 hr_utility.set_location( l_proc,10);
2304 -- Bug 2354730 Fix Begins
2305 -- Only validate pay proposal data when there's no change to the
2306 -- salary basis. That means Pay Rate is standalone, in which case
2307 -- pay proposal data may exist. If we cannot find a record in
2308 -- per_pay_proposal, bypass the rest of validation. If a record is
2309 -- found, then do validation.
2310
2311 -- In other cases, such as adding a new salary basis or changing an
2312 -- existing salary basis in which new pay proposal data has not been
2313 -- entered yet, we want to bypass the logic to look for pay proposal
2314 -- data.
2315
2316 Open c_last_Salary ;
2317 fetch c_last_Salary into l_pay_Proposal_Id ,
2318 l_Change_Date ,
2319 l_next_Sal_Review_Date,
2320 l_Next_Perf_Review_Date ,
2321 l_Proposal_Reason,
2322 l_Current_Salary,
2323 l_Approved,
2324 l_Review_Date,
2325 l_Multiple_Components,
2326 ln_ovn ;
2327
2328 IF (c_last_salary%NOTFOUND)
2329 THEN
2330 hr_utility.set_location( l_proc,15);
2331 NULL; -- bypass the rest of the validations
2332 ELSE
2333 hr_utility.set_location( l_proc,20);
2334 lv_pay_proposal_rec_found := TRUE;
2335 END IF;
2336
2337 close c_last_Salary ;
2338
2339 END IF;
2340
2341 -- hr_utility.trace(' first proposal can not be created ' );
2342 -- hr_utility.trace(message );
2343 -- check if a valid payroll exist for the assignment
2344 -- validation 1
2345 -- check if this proposal is approved
2346 -- if not approved , raise error
2347
2348 IF lv_pay_proposal_rec_found
2349 THEN
2350 hr_utility.set_location( l_proc,25);
2351 IF l_Approved <>'Y'
2352 THEN
2353 hr_utility.set_location( l_proc,30);
2354 message := message || hr_util_misc_web.return_msg_text(
2355 p_message_name=>'HR_PR_MSG02_WEB',
2356 p_Application_id=>'PER');
2357 message := message || ' ';
2358 END IF ;
2359
2360 -- check if eligible for salary basis/element
2361 IF (NOT check_ele_eligibility(p_assignment_id,
2362 p_effective_date)) THEN
2363 hr_utility.set_location( l_proc,35);
2364 message := message || hr_util_misc_web.return_msg_text(
2365 p_message_name=>'HR_13016_SAL_ELE_NOT_ELIG',
2366 p_Application_id=>'PER');
2367 message := message || ' ';
2368 END IF;
2369
2370 --hr_utility.trace(message );
2371 -- validation 2
2372 -- check that last_change date is <= effective date
2373 -- if equal to effective date we are in correction mode
2374
2375 IF l_change_date <= to_date(p_effective_date,'RRRR-MM-DD')
2376 THEN
2377 IF (l_change_date = to_date(p_effective_date,'RRRR-MM-DD') AND p_asg_type <> 'A')
2378 THEN
2379 hr_utility.set_location( l_proc,40);
2380 message := message || hr_util_misc_web.return_msg_text(
2381 p_message_name=>'HR_PR_MSG03_WEB',
2382 p_Application_id=>'PER');
2383 message := message || ' ';
2384 END IF ;
2385 ELSE
2386 hr_utility.set_location( l_proc,45);
2387 message := message || hr_util_misc_web.return_msg_text(
2388 p_message_name=>'HR_PR_MSG04_WEB',
2389 p_Application_id=>'PER');
2390 message := message || ' ';
2391 END IF ;
2392 END IF; -- lv_pay_proposal_rec_found = TRUE
2393 -- Bug 2354730 Fix Ends
2394
2395 -- hr_utility.trace(message );
2396 -- validate that assignment is not terminated on the date of the
2397 -- change
2398 open c_assignment_status ;
2399 fetch c_assignment_status into
2400 lv_system_status, lv_payroll_status ;
2401
2402 IF lv_system_status = 'TERM_ASSIGN'
2403 THEN
2404 hr_utility.set_location( l_proc,50);
2405 message := message || hr_util_misc_web.return_msg_text(
2406 p_message_name=>'HR_PR_MSG16_WEB',
2407 p_Application_id=>'PER');
2408 message := message || ' ';
2409 END IF ;
2410
2411 close c_assignment_status ;
2412 -- hr_utility.trace(message );
2413 -- validation 3
2414 -- validate that assignment does not have future changes to
2415 -- be effective after the effective date , if so raise warning
2416 OPEN c_future_assignment_changes ;
2417 FETCH c_future_assignment_changes into lv_exists ;
2418 IF c_future_assignment_changes%NOTFOUND
2419 THEN
2420 hr_utility.set_location( l_proc,55);
2421 lv_exists := 'N';
2422 END IF ;
2423
2424 CLOSE c_future_assignment_changes ;
2425
2426 -- hr_utility.trace(message );
2427 IF lv_exists ='Y' THEN
2428 hr_utility.set_location( l_proc,60);
2429 -- future assignment changes exists warning
2430 -- bug 3033365 raise a warning
2431 /*message := message || hr_util_misc_web.return_msg_text(
2432 p_message_name=>'HR_PR_MSG05_WEB',
2433 p_Application_id=>'PER');
2434 message := message || ' ';*/
2435 warn_message := warn_message || hr_util_misc_web.return_msg_text(
2436 p_message_name=>'HR_PR_MSG05_WEB',
2437 p_Application_id=>'PER');
2438 warn_message := warn_message || ' ';
2439 END IF ;
2440
2441 -- raise a warnign if effective date is before the last payroll
2442 -- period
2443
2444 OPEN c_payroll_period ( p_payroll_id ) ;
2445 FETCH c_payroll_period into ld_payroll_start_date ;
2446 CLOSE c_payroll_period ;
2447
2448 IF to_date(p_effective_date,'RRRR-MM-DD') < ld_payroll_start_date
2449 AND p_payroll_id is not null
2450 THEN
2451 hr_utility.set_location( l_proc,65);
2452 -- hr_utility.trace('effective Date is less than ld_payroll_start_date ');
2453 warn_message := warn_message || hr_util_misc_web.return_msg_text(
2454 p_message_name=>'HR_PR_MSG17_WEB',
2455 p_Application_id=>'PER');
2456 warn_message := warn_message || ' ';
2457 END IF ;
2458
2459 -- validation 4
2460 -- raise a warning if assignment is placed on the grade step
2461 -- hr_utility.trace(message );
2462 OPEN c_grade_step_placement ;
2463 FETCH c_grade_step_placement into lv_exists ;
2464
2465 IF c_grade_step_placement%NOTFOUND
2466 THEN
2467 hr_utility.set_location( l_proc,70);
2468 lv_exists := 'N' ;
2469 END IF ;
2470
2471 -- statement here for error checking ;
2472 CLOSE c_grade_step_placement ;
2473
2474 IF lv_exists ='Y'
2475 THEN
2476 hr_utility.set_location( l_proc,75);
2477 -- hr_utility.trace('c_grade_step_placement warning');
2478 warn_message := warn_message || hr_util_misc_web.return_msg_text(
2479 p_message_name=>'HR_PR_MSG06_WEB',
2480 p_Application_id=>'PER');
2481 warn_message := warn_message || ' ';
2482 END IF ;
2483
2484 -- hr_utility.trace(message );
2485
2486 excep_message := message;
2487 p_warning_message := warn_message;
2488 p_pay_proposal_id := l_pay_Proposal_Id;
2489 p_current_salary := l_Current_Salary;
2490 p_ovn := ln_ovn;
2491 p_api_mode := 'INSERT';
2492
2493 -- hr_utility.trace(excep_message );
2494
2495
2496 hr_utility.set_location(' Leaving:' || l_proc,80);
2497 END validate_salary_details;
2498
2499
2500 -- GSP changes
2501 PROCEDURE get_transaction_step_details(p_item_type IN VARCHAR2,
2502 p_item_key IN VARCHAR2,
2503 p_transaction_step_id IN VARCHAR2,
2504 trans_exists OUT NOCOPY VARCHAR2,
2505 no_of_components OUT NOCOPY NUMBER ,
2506 is_multiple_payrate OUT NOCOPY VARCHAR2 )
2507 IS
2508
2509 l_proc varchar2(200) := g_package || 'get_transaction_step_details';
2510
2511 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
2512 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
2513 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
2514 ln_trans_step_rows NUMBER ;
2515 lv_activity_name VARCHAR2(100);
2516 lv_activity_display_name VARCHAR2(100);
2517 ln_no_of_components NUMBER ;
2518 lv_trans_exists VARCHAR2(10) := 'NO';
2519 ln_transaction_step_id NUMBER;
2520 lv_pay_rate_type VARCHAR2(10) := '';
2521
2522
2523 BEGIN
2524
2525 hr_utility.set_location(' Entering:' || l_proc,5);
2526 trans_exists := '';
2527 no_of_components := 0;
2528 is_multiple_payrate := '';
2529
2530 --hr_utility.trace(' ********** In is_transaction_exists ');
2531 --hr_utility.trace(' p_item_type' || p_item_type || ' ' ||
2532 -- ' p_item_key' || p_item_key || ' ' ||
2533 -- ' p_transaction_step_id' || p_transaction_step_id );
2534
2535 ln_transaction_id := hr_transaction_ss.get_transaction_id
2536 (p_Item_Type => p_item_type,
2537 p_Item_Key => p_item_key);
2538
2539 IF p_transaction_step_id IS NOT NULL
2540 THEN
2541 hr_utility.set_location(l_proc,10);
2542 ln_no_of_components :=
2543 hr_transaction_api.get_number_value
2544 (p_transaction_step_id => p_transaction_step_id,
2545 p_name => 'p_no_of_components');
2546
2547 IF ln_no_of_components IS NOT NULL THEN
2548 hr_utility.set_location(l_proc,15);
2549 no_of_components := ln_no_of_components;
2550 END IF;
2551
2552 lv_pay_rate_type :=
2553 hr_transaction_api.get_varchar2_value
2554 (p_transaction_step_id => p_transaction_step_id,
2555 p_name => 'P_MULTIPLE_COMPONENTS');
2556
2557 IF lv_pay_rate_type IS NOT NULL THEN
2558 hr_utility.set_location(l_proc,20);
2559 is_multiple_payrate := lv_pay_rate_type;
2560 END IF;
2561
2562 lv_trans_exists := 'YES';
2563 trans_exists := lv_trans_exists;
2564
2565 END IF;
2566
2567 hr_utility.set_location(' Leaving:' || l_proc,25);
2568 EXCEPTION
2569 WHEN OTHERS THEN
2570 hr_utility.set_location(' Leaving:' || l_proc,555);
2571 raise;
2572 END get_transaction_step_details;
2573 -- End of GSP changes
2574
2575
2576
2577 PROCEDURE is_transaction_exists(p_item_type IN VARCHAR2,
2578 p_item_key IN VARCHAR2,
2579 p_act_id IN VARCHAR2,
2580 trans_exists OUT NOCOPY VARCHAR2,
2581 no_of_components OUT NOCOPY NUMBER ,
2582 is_multiple_payrate OUT NOCOPY VARCHAR2 )
2583 IS
2584
2585
2586 l_proc varchar2(200) := g_package || 'is_transaction_exists';
2587 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
2588 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
2589 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
2590 ln_trans_step_rows NUMBER ;
2591 lv_activity_name VARCHAR2(100);
2592 lv_activity_display_name VARCHAR2(100);
2593 ln_no_of_components NUMBER ;
2594 lv_trans_exists VARCHAR2(10) := 'NO';
2595 ln_transaction_step_id NUMBER;
2596 lv_pay_rate_type VARCHAR2(10) := '';
2597
2598
2599 BEGIN
2600 hr_utility.set_location(' Entering:' || l_proc,5);
2601 trans_exists := '';
2602 no_of_components := 0;
2603 is_multiple_payrate := '';
2604
2605 -- hr_utility.trace_on(null,'dev_log');
2606
2607 -- hr_utility.trace(' ********** In is_transaction_exists ');
2608 --hr_utility.trace(' p_item_type' || p_item_type || ' ' ||
2609 -- ' p_item_key' || p_item_key || ' ' ||
2610 -- ' p_act_id' || p_act_id );
2611
2612 IF( hr_transaction_ss.check_txn_step_exists(
2613 p_item_type,
2614 p_item_key,
2615 p_act_id )= TRUE )
2616 THEN
2617
2618 hr_utility.set_location(l_proc,10);
2619 ln_transaction_id := hr_transaction_ss.get_transaction_id
2620 (p_Item_Type => p_item_type,
2621 p_Item_Key => p_item_key);
2622
2623 -- hr_utility.trace(' ln_transaction_id ' || ln_transaction_id);
2624
2625 IF ln_transaction_id IS NOT NULL
2626 THEN
2627 hr_utility.set_location(l_proc,15);
2628 hr_transaction_api.get_transaction_step_info
2629 (p_Item_Type => p_item_type,
2630 p_Item_Key => p_item_key,
2631 p_activity_id =>p_act_id,
2632 p_transaction_step_id => ltt_trans_step_ids,
2633 p_object_version_number => ltt_trans_obj_vers_num,
2634 p_rows => ln_trans_step_rows);
2635
2636
2637 -- if no transaction steps are found , return
2638 IF ln_trans_step_rows < 1
2639 THEN
2640 hr_utility.set_location(' Leaving:' || l_proc,20);
2641 RETURN ;
2642 ELSE
2643 hr_utility.set_location(l_proc,25);
2644 hr_mee_workflow_service.get_activity_name
2645 (p_item_type => p_item_type
2646 ,p_item_key => p_item_key
2647 ,p_actid => p_act_id
2648 ,p_activity_name => lv_activity_name
2649 ,p_activity_display_name => lv_activity_display_name);
2650
2651
2652 -- hr_utility.trace(' lv_activity_name ' || lv_activity_name);
2653
2654 ln_transaction_step_id :=
2655 hr_transaction_ss.get_activity_trans_step_id
2656 (p_activity_name =>lv_activity_name,
2657 p_trans_step_id_tbl => ltt_trans_step_ids);
2658
2659
2660 ln_no_of_components :=
2661 hr_transaction_api.get_number_value
2662 (p_transaction_step_id => ln_transaction_step_id,
2663 p_name => 'p_no_of_components');
2664
2665 IF ln_no_of_components IS NOT NULL THEN
2666 hr_utility.set_location(l_proc,30);
2667 no_of_components := ln_no_of_components;
2668 END IF;
2669
2670 -- hr_utility.trace(' ln_no_of_components ' || ln_no_of_components);
2671
2672 lv_pay_rate_type :=
2673 hr_transaction_api.get_varchar2_value
2674 (p_transaction_step_id => ln_transaction_step_id,
2675 p_name => 'P_MULTIPLE_COMPONENTS');
2676
2677 IF lv_pay_rate_type IS NOT NULL THEN
2678 hr_utility.set_location(l_proc,35);
2679 is_multiple_payrate := lv_pay_rate_type;
2680 END IF;
2681
2682 -- hr_utility.trace(' is_multiple_payrate ' || is_multiple_payrate);
2683
2684 lv_trans_exists := 'YES';
2685 trans_exists := lv_trans_exists;
2686
2687 -- hr_utility.trace(' trans_exists ' || trans_exists);
2688 END IF;
2689 END IF;
2690 END IF;
2691
2692
2693 hr_utility.set_location(' Leaving:' || l_proc,40);
2694 EXCEPTION
2695 WHEN OTHERS THEN
2696 hr_utility.set_location(' Leaving:' || l_proc,555);
2697 raise;
2698 END is_transaction_exists;
2699
2700
2701
2702 PROCEDURE validate_component_api_java(
2703 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
2704 p_ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ ,
2705 p_validate IN BOOLEAN DEFAULT FALSE )
2706 IS
2707
2708 l_proc varchar2(200) := g_package || 'validate_component_api_java';
2709 ln_count NUMBER ;
2710 i NUMBER ;
2711 ln_object_version_number NUMBER ;
2712 ln_component_id NUMBER ;
2713 lv_message_number VARCHAR2(15);
2714
2715 BEGIN
2716 hr_utility.set_location(' Entering:' || l_proc,5);
2717 -- hr_utility.trace(' ***** IN validate_component_api_java *****');
2718
2719 -- get the no of components
2720 --ln_count := p_ltt_component.count ;
2721
2722 ln_count := p_ltt_salary_data(1).no_of_components;
2723
2724 -- hr_utility.trace('No Of Components In Component Proc' || ln_count);
2725 -- now call the component API for each component
2726 FOR i in 1..ln_count
2727 LOOP
2728 hr_maintain_proposal_api.insert_proposal_component(
2729 p_component_id=>ln_component_id ,
2730 p_pay_proposal_id=>p_ltt_salary_data(1).pay_proposal_id,
2731 p_business_group_id=>p_ltt_salary_data(1).business_group_id,
2732 p_approved => p_ltt_component(i).approved,
2733 p_component_reason =>p_ltt_component(i).component_reason,
2734 p_change_amount_n => to_number(p_ltt_component(i).change_amount),
2735 p_change_percentage =>p_ltt_component(i).change_percent,
2736 p_comments =>NULL,
2737 p_attribute_category =>p_ltt_component(i).attribute_category,
2738 p_attribute1 => p_ltt_component(i).attribute1,
2739 p_attribute2 => p_ltt_component(i).attribute2,
2740 p_attribute3 => p_ltt_component(i).attribute3,
2741 p_attribute4 => p_ltt_component(i).attribute4,
2742 p_attribute5 => p_ltt_component(i).attribute5,
2743 p_attribute6 => p_ltt_component(i).attribute6,
2744 p_attribute7 => p_ltt_component(i).attribute7,
2745 p_attribute8 => p_ltt_component(i).attribute8,
2746 p_attribute9 => p_ltt_component(i).attribute9,
2747 p_attribute10 => p_ltt_component(i).attribute10,
2748 p_attribute11 => p_ltt_component(i).attribute11,
2749 p_attribute12 => p_ltt_component(i).attribute12,
2750 p_attribute13 => p_ltt_component(i).attribute13,
2751 p_attribute14 => p_ltt_component(i).attribute14,
2752 p_attribute15 => p_ltt_component(i).attribute15,
2753 p_attribute16 => p_ltt_component(i).attribute16,
2754 p_attribute17 => p_ltt_component(i).attribute17,
2755 p_attribute18 => p_ltt_component(i).attribute18,
2756 p_attribute19 => p_ltt_component(i).attribute19,
2757 p_attribute20 => p_ltt_component(i).attribute20,
2758 p_object_version_number =>ln_object_version_number,
2759 p_validate=>FALSE,
2760 p_validation_strength=>'WEAK');
2761
2762 p_ltt_component(i).component_id := ln_component_id ;
2763 END LOOP ;
2764
2765 -- hr_utility.trace(' ***** OUT validate_component_api_java *****');
2766
2767
2768 hr_utility.set_location(' Leaving:' || l_proc,10);
2769 EXCEPTION
2770 WHEN hr_utility.hr_error THEN
2771 hr_utility.set_location(' Leaving:' || l_proc,555);
2772 -- ROLLBACK;
2773 -- hr_utility.trace('Utility Exception in validate_component_api_java');
2774 raise;
2775 --hr_message.provide_error;
2776 -- lv_message_number := hr_message.last_message_number;
2777 --hr_errors_api.addErrorToTable(
2778 -- p_errormsg => hr_message.get_message_text,
2779 -- p_errorcode => lv_message_number);
2780
2781 WHEN OTHERS THEN
2782 hr_utility.set_location(' Leaving:' || l_proc,560);
2783 ROLLBACK;
2784 -- hr_utility.trace(' Others Exception in validate_component_api_java');
2785 raise;
2786 --hr_util_disp_web.display_fatal_errors (
2787 -- p_message => UPPER(gv_package_name ||
2788 -- '.validate_component_api: '|| SQLERRM));
2789 END validate_component_api_java ;
2790
2791
2792
2793 PROCEDURE validate_salary_ins_api_java (
2794 p_item_type IN wf_items.item_type%type ,
2795 p_item_key IN wf_items.item_key%TYPE ,
2796 p_Act_id IN NUMBER,
2797 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ ,
2798 p_ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ,
2799 p_validate IN BOOLEAN DEFAULT FALSE ,
2800 p_effective_date IN VARCHAR2 DEFAULT NULL,
2801 p_inv_next_sal_date_warning out nocopy boolean,
2802 p_proposed_salary_warning out nocopy boolean,
2803 p_approved_warning out nocopy boolean,
2804 p_payroll_warning out nocopy boolean
2805 ) IS
2806
2807
2808 l_proc varchar2(200) := g_package || 'validate_salary_ins_api_java';
2809 l_pay_proposal_id NUMBER ;
2810 l_object_version_number NUMBER ;
2811 l_next_sal_date_warning BOOLEAN ;
2812 l_proposed_sal_warning BOOLEAN ;
2813 l_approved_warning BOOLEAN ;
2814 l_payroll_warning BOOLEAN ;
2815 l_element_entry_id NUMBER ;
2816 ln_count NUMBER ;
2817 i NUMBER ;
2818 l_component_id NUMBER ;
2819 lv_message_number VARCHAR2(15);
2820 lv_proposal_reason VARCHAR2(250);
2821 message VARCHAR2(2500) default '';
2822 ld_effec_date date DEFAULT NULL;
2823 lb_save_point_exists boolean default false;
2824
2825 cursor csr_payproposal is
2826 select pay_proposal_id, object_version_number
2827 from per_pay_proposals
2828 where assignment_id = p_ltt_salary_data(1).assignment_id
2829 and change_date = ld_effec_date;
2830
2831 BEGIN
2832
2833 -- hr_utility.trace(' ***** IN validate_salary_ins_api_java *****');
2834
2835 /*
2836 message := message ||
2837 ' attribute_category' || p_ltt_salary_data(1).attribute_category ||
2838 ' attribute1' || p_ltt_salary_data(1).attribute1 ||
2839 ' attribute2' || p_ltt_salary_data(1).attribute2 ||
2840 ' attribute3' || p_ltt_salary_data(1).attribute3 ||
2841 ' attribute4' || p_ltt_salary_data(1).attribute4 ||
2842 ' attribute5' || p_ltt_salary_data(1).attribute5 ||
2843 ' attribute6' || p_ltt_salary_data(1).attribute6 ||
2844 ' attribute7' || p_ltt_salary_data(1).attribute7 ||
2845 ' attribute8' || p_ltt_salary_data(1).attribute8 ||
2846 ' attribute9' || p_ltt_salary_data(1).attribute9 ||
2847 ' attribute10' || p_ltt_salary_data(1).attribute10 ||
2848 ' attribute11' || p_ltt_salary_data(1).attribute11 ||
2849 ' attribute12' || p_ltt_salary_data(1).attribute12 ||
2850 ' attribute13' || p_ltt_salary_data(1).attribute13 ||
2851 ' attribute14' || p_ltt_salary_data(1).attribute14 ||
2852 ' attribute15' || p_ltt_salary_data(1).attribute15 ||
2853 ' attribute16' || p_ltt_salary_data(1).attribute16 ||
2854 ' attribute17' || p_ltt_salary_data(1).attribute17 ||
2855 ' attribute18' || p_ltt_salary_data(1).attribute18 ||
2856 ' attribute19' || p_ltt_salary_data(1).attribute19 ||
2857 ' attribute20' || p_ltt_salary_data(1).attribute20 ||
2858 ' no_of_components' || p_ltt_salary_data(1).no_of_components ;
2859
2860
2861 hr_utility.trace(message);
2862 */
2863
2864 hr_utility.set_location(' Entering:' || l_proc,5);
2865 if(p_effective_date is null) then
2866 hr_utility.set_location(l_proc,10);
2867 ld_effec_date := p_ltt_salary_data(1).effective_date;
2868 else
2869 hr_utility.set_location(l_proc,15);
2870 ld_effec_date := to_date(p_effective_date, 'RRRR-MM-DD');
2871 end if;
2872 -- check if multiple components exists
2873 -- call the salary API in validate mode
2874 SAVEPOINT insert_salary ;
2875 lb_save_point_exists := TRUE;
2876
2877 -- start a block for the salary proposal api
2878 l_element_entry_id := p_ltt_salary_data(1).element_entry_id ;
2879
2880
2881 IF p_ltt_salary_data(1).multiple_components = 'Y'
2882 THEN
2883 hr_utility.set_location(l_proc,20);
2884 lv_proposal_reason := NULL ;
2885 ELSE
2886 hr_utility.set_location(l_proc,25);
2887 lv_proposal_reason := p_ltt_salary_data(1).proposal_reason ;
2888 END IF ;
2889
2890 -- hr_utility.trace(' *****************************************************');
2891 -- hr_utility.trace(' Start hr_maintain_proposal_api.insert_salary_proposal');
2892 -- hr_utility.trace(' *****************************************************');
2893
2894
2895
2896
2897 /* validate = FALSE it is not rolledback
2898 validate = TRUE it is rolledback
2899 For multiple components to validated we need to insert the proposal record
2900 that is the reason this API is called in FALSE mode and later it is rollbacked
2901 */
2902
2903 open csr_payproposal;
2904 fetch csr_payproposal into l_pay_proposal_id, l_object_version_number;
2905 IF csr_payproposal%found THEN
2906
2907 /* although this code is called unconditionally this scenario will only occur for an applicant having
2908 an offer. for other cases this will never be true since we throw warning from validate_salary_details
2909 and do not show the Pay Rate page itself */
2910
2911 hr_maintain_proposal_api.update_salary_proposal(
2912 p_pay_proposal_id=>l_pay_proposal_id ,
2913 p_change_date=> ld_effec_date, -- 2355929
2914 p_comments=>p_ltt_salary_data(1).comments,
2915 p_next_sal_review_date=>p_ltt_salary_data(1).next_sal_review_date,
2916 p_proposal_reason =>lv_proposal_reason,
2917 p_proposed_salary_n =>p_ltt_salary_data(1).proposed_salary ,
2918 p_forced_ranking =>to_number(p_ltt_salary_data(1).ranking) ,
2919 p_performance_review_id=>
2920 p_ltt_salary_data(1).Performance_review_id,
2921 p_attribute_category =>p_ltt_salary_data(1).attribute_category,
2922 p_attribute1 => p_ltt_salary_data(1).attribute1,
2923 p_attribute2 => p_ltt_salary_data(1).attribute2,
2924 p_attribute3 => p_ltt_salary_data(1).attribute3,
2925 p_attribute4 => p_ltt_salary_data(1).attribute4,
2926 p_attribute5 => p_ltt_salary_data(1).attribute5,
2927 p_attribute6 => p_ltt_salary_data(1).attribute6,
2928 p_attribute7 => p_ltt_salary_data(1).attribute7,
2929 p_attribute8 => p_ltt_salary_data(1).attribute8,
2930 p_attribute9 => p_ltt_salary_data(1).attribute9,
2931 p_attribute10 => p_ltt_salary_data(1).attribute10,
2932 p_attribute11 => p_ltt_salary_data(1).attribute11,
2933 p_attribute12 => p_ltt_salary_data(1).attribute12,
2934 p_attribute13 => p_ltt_salary_data(1).attribute13,
2935 p_attribute14 => p_ltt_salary_data(1).attribute14,
2936 p_attribute15 => p_ltt_salary_data(1).attribute15,
2937 p_attribute16 => p_ltt_salary_data(1).attribute16,
2938 p_attribute17 => p_ltt_salary_data(1).attribute17,
2939 p_attribute18 => p_ltt_salary_data(1).attribute18,
2940 p_attribute19 => p_ltt_salary_data(1).attribute19,
2941 p_attribute20 => p_ltt_salary_data(1).attribute20,
2942 p_object_version_number=>l_object_version_number ,
2943 p_multiple_components=>p_ltt_salary_data(1).multiple_components,
2944 p_approved=>'Y',
2945 p_validate=>FALSE ,
2946 p_inv_next_sal_date_warning=>l_next_sal_date_warning,
2947 p_proposed_salary_warning=>l_proposed_sal_warning,
2948 p_approved_warning=>l_approved_warning,
2949 p_payroll_warning =>l_payroll_warning) ;
2950
2951 ELSE
2952
2953 hr_maintain_proposal_api.insert_salary_proposal(
2954 p_pay_proposal_id=>l_pay_proposal_id ,
2955 p_assignment_id=>p_ltt_salary_data(1).assignment_id ,
2956 p_business_group_id=>p_ltt_salary_data(1).business_group_id ,
2957 --p_change_date=>p_ltt_salary_data(1).effective_date,
2958 p_change_date=> ld_effec_date, -- 2355929
2959 p_comments=>p_ltt_salary_data(1).comments,
2960 p_next_sal_review_date=>p_ltt_salary_data(1).next_sal_review_date,
2961 p_proposal_reason =>lv_proposal_reason,
2962 p_proposed_salary_n =>p_ltt_salary_data(1).proposed_salary ,
2963 p_forced_ranking =>to_number(p_ltt_salary_data(1).ranking) ,
2964 p_performance_review_id=>
2965 p_ltt_salary_data(1).Performance_review_id,
2966 p_attribute_category =>p_ltt_salary_data(1).attribute_category,
2967 p_attribute1 => p_ltt_salary_data(1).attribute1,
2968 p_attribute2 => p_ltt_salary_data(1).attribute2,
2969 p_attribute3 => p_ltt_salary_data(1).attribute3,
2970 p_attribute4 => p_ltt_salary_data(1).attribute4,
2971 p_attribute5 => p_ltt_salary_data(1).attribute5,
2972 p_attribute6 => p_ltt_salary_data(1).attribute6,
2973 p_attribute7 => p_ltt_salary_data(1).attribute7,
2974 p_attribute8 => p_ltt_salary_data(1).attribute8,
2975 p_attribute9 => p_ltt_salary_data(1).attribute9,
2976 p_attribute10 => p_ltt_salary_data(1).attribute10,
2977 p_attribute11 => p_ltt_salary_data(1).attribute11,
2978 p_attribute12 => p_ltt_salary_data(1).attribute12,
2979 p_attribute13 => p_ltt_salary_data(1).attribute13,
2980 p_attribute14 => p_ltt_salary_data(1).attribute14,
2981 p_attribute15 => p_ltt_salary_data(1).attribute15,
2982 p_attribute16 => p_ltt_salary_data(1).attribute16,
2983 p_attribute17 => p_ltt_salary_data(1).attribute17,
2984 p_attribute18 => p_ltt_salary_data(1).attribute18,
2985 p_attribute19 => p_ltt_salary_data(1).attribute19,
2986 p_attribute20 => p_ltt_salary_data(1).attribute20,
2987 p_object_version_number=>l_object_version_number ,
2988 p_multiple_components=>p_ltt_salary_data(1).multiple_components,
2989 p_approved=>'Y',
2990 p_validate=>FALSE ,
2991 p_element_entry_id=>l_element_entry_id,
2992 p_inv_next_sal_date_warning=>l_next_sal_date_warning,
2993 p_proposed_salary_warning=>l_proposed_sal_warning,
2994 p_approved_warning=>l_approved_warning,
2995 p_payroll_warning =>l_payroll_warning) ;
2996
2997 END IF;
2998
2999
3000 -- hr_utility.trace(' *****************************************************');
3001 -- hr_utility.trace(' l_pay_proposal_id' || l_pay_proposal_id);
3002 -- hr_utility.trace(' l_element_entry_id ' || l_element_entry_id);
3003 if ( l_next_sal_date_warning) then hr_utility.trace(' l_next_sal_date_warning ' ); end if;
3004 if ( l_proposed_sal_warning) then hr_utility.trace(' l_proposed_sal_warning ' ); end if;
3005 if ( l_approved_warning) then hr_utility.trace(' l_approved_warning ' ); end if;
3006 if ( l_payroll_warning) then hr_utility.trace(' l_payroll_warning ' ); end if;
3007 -- hr_utility.trace(' *****************************************************');
3008
3009
3010
3011 -- hr_utility.trace(' End hr_maintain_proposal_api.insert_salary_proposal');
3012
3013 p_ltt_salary_data(1).pay_proposal_id := l_pay_proposal_id ;
3014
3015 p_ltt_salary_data(1).element_entry_id := l_element_entry_id ;
3016
3017
3018 IF p_ltt_salary_data(1).multiple_components = 'Y'
3019 THEN
3020 hr_utility.set_location(l_proc,30);
3021 -- hr_utility.trace(' Start validate_component_api_java.insert_salary_proposal');
3022
3023 validate_component_api_java(
3024 p_ltt_salary_data,
3025 p_ltt_component ,
3026 p_validate ) ;
3027 -- hr_utility.trace(' End validate_component_api_java.insert_salary_proposal');
3028
3029
3030 END IF ;
3031
3032
3033 IF p_validate
3034 THEN
3035 hr_utility.set_location(l_proc,40);
3036 ROLLBACK to insert_salary ;
3037 lb_save_point_exists := FALSE;
3038 END IF ;
3039
3040 p_inv_next_sal_date_warning := l_next_sal_date_warning;
3041 p_proposed_salary_warning := l_proposed_sal_warning;
3042 p_approved_warning := l_approved_warning;
3043 p_payroll_warning := l_payroll_warning;
3044
3045
3046 hr_utility.set_location(' Leaving:' || l_proc,45);
3047 EXCEPTION
3048 WHEN hr_utility.hr_error THEN
3049
3050 IF lb_save_point_exists
3051 THEN
3052 ROLLBACK to insert_salary;
3053 END IF;
3054
3055 --hr_utility.trace(' validate_salary_ins_api_java Execption hr_utility ');
3056 hr_utility.set_location(' Leaving:' || l_proc,555);
3057 raise;
3058 --hr_message.provide_error;
3059 --lv_message_number := hr_message.last_message_number;
3060 --hr_errors_api.addErrorToTable(
3061 -- p_errormsg => hr_message.get_message_text,
3062 -- p_errorcode => lv_message_number
3063 -- );
3064
3065 WHEN OTHERS THEN
3066 --hr_util_disp_web.display_fatal_errors (
3067 -- p_message => UPPER(gv_package_name || '.validate_salary_insert_api: '
3068 -- ||SQLERRM));
3069 --hr_utility.trace(' validate_salary_ins_api_java When Others Execption '|| SQLERRM);
3070 IF lb_save_point_exists
3071 THEN
3072 ROLLBACK to insert_salary;
3073 END IF;
3074 hr_utility.set_location(' Leaving:' || l_proc,560);
3075 raise;
3076
3077
3078 END validate_salary_ins_api_java;
3079
3080
3081
3082
3083
3084 /* -------------------------------------------------------
3085 -- Procedure: maintain_txn_java
3086 -- Procedure to store data into txn table
3087 ----------------------------------------------------------*/
3088 PROCEDURE maintain_txn_java (
3089 p_item_type IN wf_items.item_type%type
3090 ,p_item_key IN wf_items.item_key%TYPE
3091 ,p_Act_id IN NUMBER
3092 ,p_ltt_salary_data in sshr_sal_prop_tab_typ
3093 ,p_ltt_component in sshr_sal_comp_tab_typ
3094 ,p_review_proc_call in VARCHAR2
3095 ,p_flow_mode in varchar2
3096 ,p_step_id out nocopy NUMBER
3097 ,p_rptg_grp_id IN VARCHAR2 DEFAULT NULL
3098 ,p_plan_id IN VARCHAR2 DEFAULT NULL
3099 ,p_effective_date_option IN VARCHAR2 DEFAULT NULL
3100 ) IS
3101
3102 l_proc varchar2(200) := g_package || 'maintain_txn_java';
3103 ln_transaction_id NUMBER ;
3104 lv_result VARCHAR2(100);
3105 li_count INTEGER ;
3106 lv_api_name hr_api_transaction_steps.api_name%type ;
3107 ln_ovn hr_api_transaction_steps.object_version_number%TYPE;
3108 ln_transaction_step_id hr_api_transaction_steps.transaction_step_id%TYPE default null;
3109 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
3110 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
3111 ln_trans_step_rows number default 0;
3112 lv_activity_name wf_item_activity_statuses_v.activity_name%TYPE;
3113 ln_no_of_components NUMBER ;
3114 lv_review_url VARCHAR2(1000) ;
3115 lv_activity_display_name VARCHAR2(100);
3116 message VARCHAR2(10000) := '';
3117 ln_creator_person_id NUMBER;
3118 result VARCHAR2(100);
3119
3120 cursor get_transaction_step_id(
3121 c_item_type in wf_items.item_type%type
3122 ,c_item_key in wf_items.item_key%type
3123 ) IS
3124 SELECT transaction_step_id
3125 FROM hr_api_transaction_steps
3126 WHERE item_type = c_item_type
3127 AND item_key = c_item_key
3128 --AND api_name = 'HR_PAY_RATE_SS.process_api_java';
3129 AND api_name = 'HR_PAY_RATE_SS.PROCESS_API';
3130
3131 gtt_trans_steps hr_transaction_ss.transaction_table;
3132
3133 BEGIN
3134 hr_utility.set_location(' Entering:' || l_proc,5);
3135 -- bug # 1641590
3136
3137 -- hr_utility.trace('Start Maintain Transaction');
3138
3139 lv_review_url := gv_package_name||'.salary_review';
3140 --lv_api_name := gv_package_name||'.process_api_java' ;
3141 lv_api_name := gv_package_name||'.PROCESS_API' ;
3142
3143
3144
3145 ln_creator_person_id := wf_engine.GetItemAttrNumber(p_item_type,
3146 p_item_key,
3147 'CREATOR_PERSON_ID');
3148
3149 -- hr_utility.trace('Creator Person Id ' || ln_creator_person_id);
3150
3151 --insert into dev_test values (' In Maintain Transaction ');
3152 -- commit;
3153
3154
3155 -- prepare salary proposal data to be stored in transaction table
3156 li_count := 1 ;
3157
3158 gtt_trans_steps(li_count).param_name := 'P_REVIEW_PROC_CALL' ;
3159 gtt_trans_steps(li_count).param_value := p_review_proc_call;
3160 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3161
3162 li_count := li_count+1 ;
3163
3164 gtt_trans_steps(li_count).param_name := 'P_EFFECTIVE_DATE_OPTION' ;
3165 gtt_trans_steps(li_count).param_value := p_effective_date_option;
3166 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3167
3168 li_count := li_count+1 ;
3169
3170 gtt_trans_steps(li_count).param_name := 'P_REVIEW_ACTID' ;
3171 gtt_trans_steps(li_count).param_value := p_Act_id;
3172 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3173
3174 li_count := li_count+1 ;
3175
3176 -- 04/24/02 Change Begins
3177 gtt_trans_steps(li_count).param_name := 'P_FLOW_MODE' ;
3178 gtt_trans_steps(li_count).param_value := p_flow_mode;
3179 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3180
3181 li_count := li_count+1 ;
3182 -- 04/24/02 Change Ends
3183
3184 -- GSP changes
3185 gtt_trans_steps(li_count).param_name := 'p_gsp_dummy_txn' ;
3186 gtt_trans_steps(li_count).param_value := 'NO';
3187 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3188 li_count := li_count+1 ;
3189 -- end of GSP changes
3190
3191
3192 gtt_trans_steps(li_count).param_name := 'p_current_salary' ;
3193 gtt_trans_steps(li_count).param_value :=
3194 p_ltt_salary_data(1).current_salary ;
3195 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3196
3197 li_count := li_count+1 ;
3198
3199 gtt_trans_steps(li_count).param_name := 'p_assignment_id' ;
3200 gtt_trans_steps(li_count).param_value :=
3201 p_ltt_salary_data(1).assignment_id ;
3202 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3203 li_count := li_count+1 ;
3204
3205 gtt_trans_steps(li_count).param_name := 'p_bus_group_id' ;
3206 gtt_trans_steps(li_count).param_value :=
3207 p_ltt_salary_data(1).business_group_id ;
3208 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3209 li_count := li_count+1 ;
3210
3211
3212 gtt_trans_steps(li_count).param_name := 'p_effective_date' ;
3213 gtt_trans_steps(li_count).param_value :=
3214 to_char(p_ltt_salary_data(1).effective_date,hr_transaction_ss.g_date_format);
3215 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
3216 li_count := li_count+1 ;
3217
3218
3219 gtt_trans_steps(li_count).param_name := 'p_salary_effective_date' ;
3220 gtt_trans_steps(li_count).param_value :=
3221 to_char(p_ltt_salary_data(1).salary_effective_date,hr_transaction_ss.g_date_format);
3222 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
3223 li_count := li_count+1 ;
3224
3225 hr_mee_workflow_service.get_activity_name
3226 (p_item_type => p_item_type
3227 ,p_item_key => p_item_key
3228 ,p_actid => p_act_id
3229 ,p_activity_name => lv_activity_name
3230 ,p_activity_display_name => lv_activity_display_name);
3231
3232 gtt_trans_steps(li_count).param_name := 'p_activity_name' ;
3233 gtt_trans_steps(li_count).param_value := lv_activity_name ;
3234 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3235
3236 li_count := li_count+1 ;
3237
3238
3239 gtt_trans_steps(li_count).param_name := 'p_change_amount' ;
3240 gtt_trans_steps(li_count).param_value :=
3241 p_ltt_salary_data(1).salary_change_amount ;
3242 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3243
3244 li_count:= li_count + 1 ;
3245 gtt_trans_steps(li_count).param_name := 'p_change_percent' ;
3246 gtt_trans_steps(li_count).param_value :=
3247 p_ltt_salary_data(1).salary_change_percent ;
3248 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3249
3250
3251 li_count:= li_count + 1 ;
3252 gtt_trans_steps(li_count).param_name := 'p_proposed_salary' ;
3253 gtt_trans_steps(li_count).param_value :=
3254 p_ltt_salary_data(1).proposed_salary;
3255 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3256
3257 li_count:= li_count + 1 ;
3258 gtt_trans_steps(li_count).param_name := 'p_annual_change' ;
3259 gtt_trans_steps(li_count).param_value :=
3260 p_ltt_salary_data(1).annual_change;
3261 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3262
3263 -- hr_utility.trace('setting p_annual_change ' || p_ltt_salary_data(1).annual_change);
3264
3265
3266 li_count:= li_count + 1 ;
3267 gtt_trans_steps(li_count).param_name := 'p_proposal_reason' ;
3268 gtt_trans_steps(li_count).param_value :=
3269 p_ltt_salary_data(1).proposal_reason;
3270 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3271
3272 li_count:= li_count + 1 ;
3273 gtt_trans_steps(li_count).param_name := 'p_currency' ;
3274 gtt_trans_steps(li_count).param_value :=
3275 p_ltt_salary_data(1).currency;
3276 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3277
3278 li_count:= li_count + 1 ;
3279 gtt_trans_steps(li_count).param_name := 'p_pay_basis_name' ;
3280 gtt_trans_steps(li_count).param_value :=
3281 p_ltt_salary_data(1).pay_basis_name;
3282 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3283
3284 li_count:= li_count + 1 ;
3285 gtt_trans_steps(li_count).param_name := 'p_annual_equivalent' ;
3286 gtt_trans_steps(li_count).param_value :=
3287 p_ltt_salary_data(1).annual_equivalent;
3288 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3289
3290 li_count:= li_count + 1 ;
3291 gtt_trans_steps(li_count).param_name := 'p_total_percent' ;
3292 gtt_trans_steps(li_count).param_value :=
3293 p_ltt_salary_data(1).total_percent;
3294 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3295
3296 li_count:= li_count + 1 ;
3297 gtt_trans_steps(li_count).param_name := 'p_selection_mode' ;
3298 gtt_trans_steps(li_count).param_value :=
3299 p_ltt_salary_data(1).selection_mode;
3300 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3301
3302 li_count:= li_count + 1 ;
3303 gtt_trans_steps(li_count).param_name := 'p_quartile' ;
3304 gtt_trans_steps(li_count).param_value :=
3305 p_ltt_salary_data(1).quartile;
3306 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3307
3308 -- hr_utility.trace('setting p_quartile ' || p_ltt_salary_data(1).quartile);
3309
3310 li_count:= li_count + 1 ;
3311 gtt_trans_steps(li_count).param_name := 'p_comparatio' ;
3312 gtt_trans_steps(li_count).param_value :=
3313 p_ltt_salary_data(1).comparatio;
3314 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3315
3316 -- hr_utility.trace('setting p_comparatio ' || p_ltt_salary_data(1).comparatio);
3317
3318 li_count:= li_count + 1 ;
3319 gtt_trans_steps(li_count).param_name := 'p_ranking' ;
3320 gtt_trans_steps(li_count).param_value :=
3321 p_ltt_salary_data(1).ranking;
3322 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3323
3324
3325 li_count:= li_count + 1 ;
3326 gtt_trans_steps(li_count).param_name := 'p_comments' ;
3327 gtt_trans_steps(li_count).param_value :=
3328 p_ltt_salary_data(1).comments;
3329 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3330
3331 -- hr_utility.trace('setting p_comments ' || p_ltt_salary_data(1).comments);
3332
3333 li_count:= li_count + 1 ;
3334 gtt_trans_steps(li_count).param_name := 'p_element_entry_id' ;
3335 gtt_trans_steps(li_count).param_value :=
3336 p_ltt_salary_data(1).element_entry_id;
3337 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3338
3339 li_count:= li_count + 1 ;
3340 gtt_trans_steps(li_count).param_name := 'p_multiple_components' ;
3341 gtt_trans_steps(li_count).param_value :=
3342 p_ltt_salary_data(1).multiple_components;
3343 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3344
3345 -- store flexes here
3346 li_count := li_count + 1;
3347 gtt_trans_steps(li_count).param_name
3348 := 'p_attribute_category';
3349 gtt_trans_steps(li_count).param_value
3350 := p_ltt_salary_data(1).attribute_category;
3351 gtt_trans_steps(li_count).param_data_type
3352 := 'VARCHAR2';
3353
3354 li_count := li_count + 1;
3355 gtt_trans_steps(li_count).param_name
3356 := 'p_no_of_components';
3357 gtt_trans_steps(li_count).param_value
3358 := p_ltt_salary_data(1).no_of_components;
3359 gtt_trans_steps(li_count).param_data_type
3360 := 'NUMBER';
3361
3362 -- hr_utility.trace(' p_no_of_components ' || p_ltt_salary_data(1).no_of_components);
3363
3364 li_count := li_count + 1;
3365 gtt_trans_steps(li_count).param_name
3366 := 'p_attribute1';
3367 gtt_trans_steps(li_count).param_value
3368 := p_ltt_salary_data(1).attribute1;
3369 gtt_trans_steps(li_count).param_data_type
3370 := 'VARCHAR2';
3371
3372 li_count := li_count + 1;
3373 gtt_trans_steps(li_count).param_name
3374 := 'p_attribute2';
3375 gtt_trans_steps(li_count).param_value
3376 := p_ltt_salary_data(1).attribute2;
3377 gtt_trans_steps(li_count).param_data_type
3378 := 'VARCHAR2';
3379
3380 li_count := li_count + 1;
3381 gtt_trans_steps(li_count).param_name
3382 := 'p_attribute3';
3383 gtt_trans_steps(li_count).param_value
3384 := p_ltt_salary_data(1).attribute3;
3385 gtt_trans_steps(li_count).param_data_type
3386 := 'VARCHAR2';
3387
3388
3389 li_count := li_count + 1;
3390 gtt_trans_steps(li_count).param_name
3391 := 'p_attribute4';
3392 gtt_trans_steps(li_count).param_value
3393 := p_ltt_salary_data(1).attribute4;
3394 gtt_trans_steps(li_count).param_data_type
3395 := 'VARCHAR2';
3396
3397 li_count := li_count + 1;
3398 gtt_trans_steps(li_count).param_name
3399 := 'p_attribute5';
3400 gtt_trans_steps(li_count).param_value
3401 := p_ltt_salary_data(1).attribute5;
3402 gtt_trans_steps(li_count).param_data_type
3403 := 'VARCHAR2';
3404
3405 li_count := li_count + 1 ;
3406 gtt_trans_steps(li_count).param_name
3407 := 'p_attribute6';
3408 gtt_trans_steps(li_count).param_value
3409 := p_ltt_salary_data(1).attribute6;
3410 gtt_trans_steps(li_count).param_data_type
3411 := 'VARCHAR2';
3412
3413 li_count := li_count + 1;
3414 gtt_trans_steps(li_count).param_name
3415 := 'p_attribute7';
3416 gtt_trans_steps(li_count).param_value
3417 := p_ltt_salary_data(1).attribute7;
3418 gtt_trans_steps(li_count).param_data_type
3419 := 'VARCHAR2';
3420
3421 li_count := li_count + 1;
3422 gtt_trans_steps(li_count).param_name
3423 := 'p_attribute8';
3424 gtt_trans_steps(li_count).param_value
3425 := p_ltt_salary_data(1).attribute8;
3426 gtt_trans_steps(li_count).param_data_type
3427 := 'VARCHAR2';
3428
3429 li_count := li_count + 1;
3430 gtt_trans_steps(li_count).param_name
3431 := 'p_attribute9';
3432 gtt_trans_steps(li_count).param_value
3433 := p_ltt_salary_data(1).attribute9;
3434 gtt_trans_steps(li_count).param_data_type
3435 := 'VARCHAR2';
3436
3437 li_count := li_count + 1;
3438 gtt_trans_steps(li_count).param_name
3439 := 'p_attribute10';
3440 gtt_trans_steps(li_count).param_value
3441 := p_ltt_salary_data(1).attribute10;
3442 gtt_trans_steps(li_count).param_data_type
3443 := 'VARCHAR2';
3444
3445 li_count := li_count + 1;
3446 gtt_trans_steps(li_count).param_name
3447 := 'p_attribute11';
3448 gtt_trans_steps(li_count).param_value
3449 := p_ltt_salary_data(1).attribute11;
3450 gtt_trans_steps(li_count).param_data_type
3451 := 'VARCHAR2';
3452
3453 li_count := li_count + 1;
3454 gtt_trans_steps(li_count).param_name
3455 := 'p_attribute12';
3456 gtt_trans_steps(li_count).param_value
3457 := p_ltt_salary_data(1).attribute12;
3458 gtt_trans_steps(li_count).param_data_type
3459 := 'VARCHAR2';
3460
3461 li_count := li_count + 1;
3462 gtt_trans_steps(li_count).param_name
3463 := 'p_attribute13';
3464 gtt_trans_steps(li_count).param_value
3465 := p_ltt_salary_data(1).attribute13;
3466 gtt_trans_steps(li_count).param_data_type
3467 := 'VARCHAR2';
3468
3469 li_count := li_count + 1;
3470 gtt_trans_steps(li_count).param_name
3471 := 'p_attribute14';
3472 gtt_trans_steps(li_count).param_value
3473 := p_ltt_salary_data(1).attribute14;
3474 gtt_trans_steps(li_count).param_data_type
3475 := 'VARCHAR2';
3476
3477 li_count := li_count + 1;
3478 gtt_trans_steps(li_count).param_name
3479 := 'p_attribute15';
3480 gtt_trans_steps(li_count).param_value
3481 := p_ltt_salary_data(1).attribute15;
3482 gtt_trans_steps(li_count).param_data_type
3483 := 'VARCHAR2';
3484
3485 li_count := li_count + 1;
3486 gtt_trans_steps(li_count).param_name
3487 := 'p_attribute16';
3488 gtt_trans_steps(li_count).param_value
3489 := p_ltt_salary_data(1).attribute16;
3490 gtt_trans_steps(li_count).param_data_type
3491 := 'VARCHAR2';
3492
3493 li_count := li_count + 1;
3494 gtt_trans_steps(li_count).param_name
3495 := 'p_attribute17';
3496 gtt_trans_steps(li_count).param_value
3497 := p_ltt_salary_data(1).attribute17;
3498 gtt_trans_steps(li_count).param_data_type
3499 := 'VARCHAR2';
3500
3501 li_count := li_count + 1;
3502 gtt_trans_steps(li_count).param_name
3503 := 'p_attribute18';
3504 gtt_trans_steps(li_count).param_value
3505 := p_ltt_salary_data(1).attribute18;
3506 gtt_trans_steps(li_count).param_data_type
3507 := 'VARCHAR2';
3508
3509 li_count := li_count + 1;
3510 gtt_trans_steps(li_count).param_name
3511 := 'p_attribute19';
3512 gtt_trans_steps(li_count).param_value
3513 := p_ltt_salary_data(1).attribute19;
3514 gtt_trans_steps(li_count).param_data_type
3515 := 'VARCHAR2';
3516
3517 li_count := li_count + 1;
3518 gtt_trans_steps(li_count).param_name
3519 := 'p_attribute20';
3520 gtt_trans_steps(li_count).param_value
3521 := p_ltt_salary_data(1).attribute20;
3522 gtt_trans_steps(li_count).param_data_type
3523 := 'VARCHAR2';
3524
3525 -- hr_utility.trace('Populated Proposal Values ');
3526 -- store components record here
3527 -- each component record will be stored as individual parameters with
3528 -- suffix no identifying the record number
3529 -- for example, component row 1 will be stored as component_id1,
3530 -- change_amount1, change_percent1 etc.
3531
3532 -- ln_no_of_components := p_ltt_component.count - 1 ;
3533 -- ln_no_of_components := to_number(p_ltt_salary_data(1).comments);
3534
3535 ln_no_of_components := p_ltt_salary_data(1).no_of_components;
3536
3537
3538 li_count:= li_count +1 ;
3539 gtt_trans_steps(li_count).param_name := 'p_no_of_components' ;
3540 gtt_trans_steps(li_count).param_value := ln_no_of_components ;
3541 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3542
3543 --insert into dev_test values (' Written Proposal Values ');
3544 message := ' ln_no_of_components =' || ln_no_of_components;
3545 --insert into dev_test values (' Written Proposal Values ');
3546 -- commit;
3547
3548 -- hr_utility.trace('No Of Components' || ln_no_of_components);
3549 FOR i in 1..ln_no_of_components
3550 LOOP
3551 li_count := li_count + 1 ;
3552 gtt_trans_steps(li_count).param_name := 'p_approved'||i ;
3553 gtt_trans_steps(li_count).param_value:=
3554 p_ltt_component(i).approved ;
3555 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3556
3557 li_count := li_count + 1 ;
3558 gtt_trans_steps(li_count).param_name := 'p_component_reason'||i ;
3559 gtt_trans_steps(li_count).param_value:=
3560 p_ltt_component(i).component_reason ;
3561 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3562
3563 li_count := li_count + 1 ;
3564 gtt_trans_steps(li_count).param_name := 'p_reason_meaning'||i ;
3565 gtt_trans_steps(li_count).param_value:=
3566 p_ltt_component(i).reason_meaning ;
3567 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3568
3569 li_count := li_count + 1 ;
3570 gtt_trans_steps(li_count).param_name := 'p_change_amount'||i ;
3571 gtt_trans_steps(li_count).param_value:=
3572 p_ltt_component(i).change_amount ;
3573 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3574
3575 li_count := li_count + 1 ;
3576 gtt_trans_steps(li_count).param_name := 'p_change_percent'||i ;
3577 gtt_trans_steps(li_count).param_value:=
3578 p_ltt_component(i).change_percent ;
3579 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3580
3581
3582 li_count := li_count + 1 ;
3583 gtt_trans_steps(li_count).param_name := 'p_change_annual'||i ;
3584 gtt_trans_steps(li_count).param_value:=
3585 p_ltt_component(i).change_annual ;
3586 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3587
3588 li_count := li_count + 1 ;
3589 gtt_trans_steps(li_count).param_name := 'p_comments'||i ;
3590 gtt_trans_steps(li_count).param_value:= p_ltt_component(i).comments;
3591 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3592
3593 li_count := li_count + 1 ;
3594 gtt_trans_steps(li_count).param_name := 'p_cattribute_category'||i ;
3595 gtt_trans_steps(li_count).param_value:=
3596 p_ltt_component(i).attribute_category;
3597 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3598
3599
3600 li_count := li_count + 1 ;
3601 gtt_trans_steps(li_count).param_name := 'p_cattribute1'||i ;
3602 gtt_trans_steps(li_count).param_value:=
3603 p_ltt_component(i).attribute1;
3604 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3605
3606 li_count := li_count + 1 ;
3607 gtt_trans_steps(li_count).param_name := 'p_cattribute2'||i ;
3608 gtt_trans_steps(li_count).param_value:=
3609 p_ltt_component(i).attribute2;
3610 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3611
3612 li_count := li_count + 1 ;
3613 gtt_trans_steps(li_count).param_name := 'p_cattribute3'||i ;
3614 gtt_trans_steps(li_count).param_value:=
3615 p_ltt_component(i).attribute3;
3616 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3617
3618 li_count := li_count + 1 ;
3619 gtt_trans_steps(li_count).param_name := 'p_cattribute4'||i ;
3620 gtt_trans_steps(li_count).param_value:=
3621 p_ltt_component(i).attribute4;
3622 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3623
3624 li_count := li_count + 1 ;
3625 gtt_trans_steps(li_count).param_name := 'p_cattribute5'||i ;
3626 gtt_trans_steps(li_count).param_value:=
3627 p_ltt_component(i).attribute5;
3628 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3629
3630 li_count := li_count + 1 ;
3631 gtt_trans_steps(li_count).param_name := 'p_cattribute6'||i ;
3632 gtt_trans_steps(li_count).param_value:=
3633 p_ltt_component(i).attribute6;
3634 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3635
3636 li_count := li_count + 1 ;
3637 gtt_trans_steps(li_count).param_name := 'p_cattribute7'||i ;
3638 gtt_trans_steps(li_count).param_value:=
3639 p_ltt_component(i).attribute7;
3640 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3641
3642
3643 li_count := li_count + 1 ;
3644 gtt_trans_steps(li_count).param_name := 'p_cattribute8'||i ;
3645 gtt_trans_steps(li_count).param_value:=
3646 p_ltt_component(i).attribute8;
3647 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3648
3649
3650 li_count := li_count + 1 ;
3651 gtt_trans_steps(li_count).param_name := 'p_cattribute9'||i ;
3652 gtt_trans_steps(li_count).param_value:=
3653 p_ltt_component(i).attribute9;
3654 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3655
3656 li_count := li_count + 1 ;
3657 gtt_trans_steps(li_count).param_name := 'p_cattribute10'||i ;
3658 gtt_trans_steps(li_count).param_value:=
3659 p_ltt_component(i).attribute10;
3660 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3661
3662 li_count := li_count + 1 ;
3663 gtt_trans_steps(li_count).param_name := 'p_cattribute11'||i ;
3664 gtt_trans_steps(li_count).param_value:=
3665 p_ltt_component(i).attribute11;
3666 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3667
3668 li_count := li_count + 1 ;
3669 gtt_trans_steps(li_count).param_name := 'p_cattribute12'||i ;
3670 gtt_trans_steps(li_count).param_value:=
3671 p_ltt_component(i).attribute12;
3672 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3673
3674 li_count := li_count + 1 ;
3675 gtt_trans_steps(li_count).param_name := 'p_cattribute13'||i ;
3676 gtt_trans_steps(li_count).param_value:=
3677 p_ltt_component(i).attribute13;
3678 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3679
3680 li_count := li_count + 1 ;
3681 gtt_trans_steps(li_count).param_name := 'p_cattribute14'||i ;
3682 gtt_trans_steps(li_count).param_value:=
3683 p_ltt_component(i).attribute14;
3684 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3685
3686 li_count := li_count + 1 ;
3687 gtt_trans_steps(li_count).param_name := 'p_cattribute15'||i ;
3688 gtt_trans_steps(li_count).param_value:=
3689 p_ltt_component(i).attribute15;
3690 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3691
3692 li_count := li_count + 1 ;
3693 gtt_trans_steps(li_count).param_name := 'p_cattribute16'||i ;
3694 gtt_trans_steps(li_count).param_value:=
3695 p_ltt_component(i).attribute16;
3696 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3697
3698 li_count := li_count + 1 ;
3699 gtt_trans_steps(li_count).param_name := 'p_cattribute17'||i ;
3700 gtt_trans_steps(li_count).param_value:=
3701 p_ltt_component(i).attribute17;
3702 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3703
3704 li_count := li_count + 1 ;
3705 gtt_trans_steps(li_count).param_name := 'p_cattribute18'||i ;
3706 gtt_trans_steps(li_count).param_value:=
3707 p_ltt_component(i).attribute18;
3708 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3709
3710 li_count := li_count + 1 ;
3711 gtt_trans_steps(li_count).param_name := 'p_cattribute19'||i ;
3712 gtt_trans_steps(li_count).param_value:=
3713 p_ltt_component(i).attribute19;
3714 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3715
3716 li_count := li_count + 1 ;
3717 gtt_trans_steps(li_count).param_name := 'p_cattribute20'||i ;
3718 gtt_trans_steps(li_count).param_value:=
3719 p_ltt_component(i).attribute20;
3720 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3721
3722 END LOOP ; -- components loop end here
3723
3724
3725 -- 04/12/02 Salary Basis Enhancement Begins
3726 -- The following are the output parameters from the call to my_get_defaults
3727 -- in process_salary_java procedure.
3728 -- The literal "default" stands for "output parameters from the
3729 -- my_get_defaults call.
3730 -- We need to save the output parameters with the prefix of default is
3731 -- because we want to avoid duplicate name, such as p_comparatio which
3732 -- is saved in the beginning of the procedure, from being saved into
3733 -- the transaction table.
3734
3735 li_count := li_count + 1 ;
3736 gtt_trans_steps(li_count).param_name := 'p_salary_basis_change_type';
3737 gtt_trans_steps(li_count).param_value :=
3738 p_ltt_salary_data(1).salary_basis_change_type;
3739 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3740
3741 li_count := li_count + 1 ;
3742 gtt_trans_steps(li_count).param_name := 'p_default_date';
3743 gtt_trans_steps(li_count).param_value :=
3744 to_char(p_ltt_salary_data(1).default_date,
3745 hr_transaction_ss.g_date_format);
3746 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
3747
3748 li_count := li_count + 1 ;
3749 gtt_trans_steps(li_count).param_name := 'p_default_bg_id';
3750 gtt_trans_steps(li_count).param_value := p_ltt_salary_data(1).default_bg_id;
3751 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3752
3753 li_count := li_count + 1 ;
3754 gtt_trans_steps(li_count).param_name := 'p_default_currency';
3755 gtt_trans_steps(li_count).param_value :=
3756 p_ltt_salary_data(1).default_currency;
3757 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3758
3759 li_count := li_count + 1 ;
3760 gtt_trans_steps(li_count).param_name := 'p_default_format_string';
3761 gtt_trans_steps(li_count).param_value :=
3762 p_ltt_salary_data(1).default_format_string;
3763 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3764
3765 li_count := li_count + 1 ;
3766 gtt_trans_steps(li_count).param_name := 'p_default_salary_basis_name';
3767 gtt_trans_steps(li_count).param_value :=
3768 p_ltt_salary_data(1).default_salary_basis_name;
3769 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3770
3771 li_count := li_count + 1 ;
3772 gtt_trans_steps(li_count).param_name := 'p_default_pay_basis_name';
3773 gtt_trans_steps(li_count).param_value :=
3774 p_ltt_salary_data(1).default_pay_basis_name;
3775 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3776
3777 li_count := li_count + 1 ;
3778 gtt_trans_steps(li_count).param_name := 'p_default_pay_basis';
3779 gtt_trans_steps(li_count).param_value :=
3780 p_ltt_salary_data(1).default_pay_basis;
3781 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3782
3783 li_count := li_count + 1 ;
3784 gtt_trans_steps(li_count).param_name :=
3785 'p_default_pay_annual_factor';
3786 gtt_trans_steps(li_count).param_value :=
3787 p_ltt_salary_data(1).default_pay_annual_factor;
3788 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3789
3790 li_count := li_count + 1 ;
3791 gtt_trans_steps(li_count).param_name := 'p_default_grade';
3792 gtt_trans_steps(li_count).param_value := p_ltt_salary_data(1).default_grade;
3793 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3794
3795 li_count := li_count + 1 ;
3796 gtt_trans_steps(li_count).param_name :=
3797 'p_default_grade_annual_factor';
3798 gtt_trans_steps(li_count).param_value :=
3799 p_ltt_salary_data(1).default_grade_annual_factor;
3800 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3801
3802 li_count := li_count + 1 ;
3803 gtt_trans_steps(li_count).param_name := 'p_default_minimum_salary';
3804 gtt_trans_steps(li_count).param_value :=
3805 p_ltt_salary_data(1).default_minimum_salary;
3806 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3807
3808 li_count := li_count + 1 ;
3809 gtt_trans_steps(li_count).param_name := 'p_default_maximum_salary';
3810 gtt_trans_steps(li_count).param_value :=
3811 p_ltt_salary_data(1).default_maximum_salary;
3812 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3813
3814 li_count := li_count + 1 ;
3815 gtt_trans_steps(li_count).param_name := 'p_default_midpoint_salary';
3816 gtt_trans_steps(li_count).param_value :=
3817 p_ltt_salary_data(1).default_midpoint_salary;
3818 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3819
3820 li_count := li_count + 1 ;
3821 gtt_trans_steps(li_count).param_name := 'p_default_prev_salary';
3822 gtt_trans_steps(li_count).param_value :=
3823 p_ltt_salary_data(1).default_prev_salary;
3824 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3825
3826 li_count := li_count + 1 ;
3827 gtt_trans_steps(li_count).param_name := 'p_default_last_change_date';
3828 gtt_trans_steps(li_count).param_value :=
3829 to_char(p_ltt_salary_data(1).default_last_change_date,
3830 hr_transaction_ss.g_date_format);
3831 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
3832
3833 li_count := li_count + 1 ;
3834 gtt_trans_steps(li_count).param_name := 'p_default_element_entry_id';
3835 gtt_trans_steps(li_count).param_value :=
3836 p_ltt_salary_data(1).default_element_entry_id;
3837 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3838
3839 li_count := li_count + 1 ;
3840 gtt_trans_steps(li_count).param_name := 'p_default_basis_changed';
3841 gtt_trans_steps(li_count).param_value :=
3842 p_ltt_salary_data(1).default_basis_changed;
3843 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3844
3845 li_count := li_count + 1 ;
3846 gtt_trans_steps(li_count).param_name := 'p_default_uom';
3847 gtt_trans_steps(li_count).param_value := p_ltt_salary_data(1).default_uom;
3848 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3849
3850 li_count := li_count + 1 ;
3851 gtt_trans_steps(li_count).param_name := 'p_default_grade_uom';
3852 gtt_trans_steps(li_count).param_value :=
3853 p_ltt_salary_data(1).default_grade_uom;
3854 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3855
3856 li_count := li_count + 1 ;
3857 gtt_trans_steps(li_count).param_name := 'p_default_change_amount';
3858 gtt_trans_steps(li_count).param_value :=
3859 p_ltt_salary_data(1).default_change_amount;
3860 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3861
3862 li_count := li_count + 1 ;
3863 gtt_trans_steps(li_count).param_name := 'p_default_change_percent';
3864 gtt_trans_steps(li_count).param_value :=
3865 p_ltt_salary_data(1).default_change_percent;
3866 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3867
3868 li_count := li_count + 1 ;
3869 gtt_trans_steps(li_count).param_name := 'p_default_quartile';
3870 gtt_trans_steps(li_count).param_value :=
3871 p_ltt_salary_data(1).default_quartile;
3872 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3873
3874 li_count := li_count + 1 ;
3875 gtt_trans_steps(li_count).param_name := 'p_default_comparatio';
3876 gtt_trans_steps(li_count).param_value :=
3877 p_ltt_salary_data(1).default_comparatio;
3878 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3879
3880 li_count := li_count + 1 ;
3881 gtt_trans_steps(li_count).param_name := 'p_default_last_pay_change';
3882 gtt_trans_steps(li_count).param_value :=
3883 p_ltt_salary_data(1).default_last_pay_change;
3884 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3885
3886 li_count := li_count + 1 ;
3887 gtt_trans_steps(li_count).param_name := 'p_default_flsa_status';
3888 gtt_trans_steps(li_count).param_value :=
3889 p_ltt_salary_data(1).default_flsa_status;
3890 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3891
3892 li_count := li_count + 1 ;
3893 gtt_trans_steps(li_count).param_name := 'p_default_currency_symbol';
3894 gtt_trans_steps(li_count).param_value :=
3895 p_ltt_salary_data(1).default_currency_symbol;
3896 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
3897
3898 li_count := li_count + 1 ;
3899 gtt_trans_steps(li_count).param_name := 'p_default_precision';
3900 gtt_trans_steps(li_count).param_value :=
3901 p_ltt_salary_data(1).default_precision;
3902 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
3903
3904
3905 -- 04/12/02 Salary Basis Enhancement Ends
3906
3907 --insert into dev_test values (' prepared component values for writing to Txn Tables');
3908 --commit;
3909
3910 -- hr_utility.trace('Populated Component values in Global Struct');
3911
3912 /********
3913 -- check if there is a txn for this transaction
3914 ln_transaction_id :=
3915 hr_transaction_ss.get_transaction_id
3916 (p_Item_Type => p_item_type
3917 ,p_Item_Key => p_item_key);
3918
3919 hr_utility.trace('No Txn Exists' || ln_transaction_id);
3920 -- insert into dev_test values (' Txn does not exist in Maintain function');
3921 --commit;
3922
3923 -- if txn is not started , create a new one
3924 IF ln_transaction_id IS NULL
3925 THEN
3926 hr_utility.set_location(l_proc,10);
3927 hr_utility.trace('Start Txn' );
3928
3929 start_transaction(p_item_type
3930 ,p_item_key
3931 ,p_act_id
3932 ,'RUN'
3933 ,ln_creator_person_id
3934 ,result );
3935
3936 hr_utility.trace('Started Txn with result '||result);
3937 -- insert into dev_test values (' result of start_transaction' || lv_result);
3938 -- commit;
3939
3940 ln_transaction_id :=
3941 hr_transaction_ss.get_transaction_id
3942 (p_Item_Type => p_item_type
3943 ,p_Item_Key => p_item_key);
3944
3945 --insert into dev_test values (' ln_transaction_id = ' || ln_transaction_id);
3946 -- commit;
3947 END IF;
3948
3949
3950
3951
3952 hr_utility.trace('Created Txn' || ln_transaction_id);
3953 --insert into dev_test values (' Created Txn');
3954 --commit;
3955
3956 -- now we have a valid txn id , let's find out txn steps
3957 hr_transaction_api.get_transaction_step_info
3958 (p_Item_Type => p_item_type
3959 ,p_Item_Key => p_item_key
3960 ,p_activity_id => p_act_id
3961 ,p_transaction_step_id => ltt_trans_step_ids
3962 ,p_object_version_number => ltt_trans_obj_vers_num
3963 ,p_rows => ln_trans_step_rows);
3964
3965 hr_utility.trace('get_transaction_step_info completed');
3966
3967 IF ln_trans_step_rows < 1 THEN
3968 hr_utility.set_location(l_proc,15);
3969
3970 --There is no transaction step for this transaction.
3971 --Create a step within this new transaction
3972
3973 hr_utility.trace('create_transaction_step ');
3974
3975 hr_transaction_api.create_transaction_step
3976 (p_validate => false
3977 ,p_creator_person_id => ln_creator_person_id
3978 ,p_transaction_id => ln_transaction_id
3979 ,p_api_name => lv_api_name
3980 ,p_Item_Type => p_item_type
3981 ,p_Item_Key => p_item_key
3982 ,p_activity_id => p_act_id
3983 ,p_transaction_step_id => ln_transaction_step_id
3984 ,p_object_version_number =>ln_ovn ) ;
3985
3986 hr_utility.trace('ln_transaction_id ' || ln_transaction_id);
3987 hr_utility.trace('ln_transaction_step_id ' || ln_transaction_step_id);
3988 ELSE
3989
3990 hr_utility.set_location(l_proc,20);
3991 --There are transaction steps for this transaction.
3992 --Get the Transaction Step ID for this activity.
3993
3994 hr_utility.trace('Txn Step Id'|| ln_transaction_step_id);
3995 ln_transaction_step_id :=
3996 hr_Transaction_ss.get_activity_trans_step_id (
3997 p_activity_name => lv_activity_name,
3998 p_trans_step_id_tbl => ltt_trans_step_ids);
3999
4000 END IF;
4001
4002 message := '';
4003 FOR i in 1..li_count
4004 LOOP
4005 message := message || gtt_trans_steps(i).param_value;
4006 END LOOP;
4007
4008 hr_utility.trace('GLOBAL STRUCTURE' || message );
4009
4010 --insert into dev_test values (' created Txn Step');
4011 --commit;
4012 ************/
4013
4014 -- save the txn data
4015 --hr_transaction_ss.save_transaction_step(
4016
4017 -- hr_utility.trace('Create Transaction and Transaction Step ');
4018
4019 open get_transaction_step_id
4020 (c_item_type => p_item_type
4021 ,c_item_key => p_item_key
4022 );
4023
4024 fetch get_transaction_step_id into ln_transaction_step_id;
4025 close get_transaction_step_id;
4026
4027 -- hr_utility.trace(' existing ln_transaction_step_id ' || ln_transaction_step_id);
4028
4029 hr_transaction_ss.save_transaction_step(
4030 p_Item_Type => p_item_type
4031 ,p_Item_Key => p_item_key
4032 ,p_ActID => p_act_id
4033 ,p_login_person_id => ln_creator_person_id
4034 ,p_transaction_step_id => ln_transaction_step_id
4035 ,p_transaction_data => gtt_trans_steps
4036 ,p_api_name => lv_api_name
4037 ,p_plan_id => p_plan_id
4038 ,p_rptg_grp_id => p_rptg_grp_id
4039 ,p_effective_date_option => p_effective_date_option);
4040
4041
4042 -- hr_utility.trace(' Transaction Step Id ' || ln_transaction_step_id );
4043
4044 -- hr_utility.trace('Saved Txn Steps to Txn Tables');
4045 --insert into dev_test values (' written values to Txn Tables');
4046 --commit;
4047 -- transaction data has been stored ,
4048 -- transition this activity
4049 -- hr_mee_workflow_service.transition_activity
4050 -- (p_item_type => p_item_type
4051 -- ,p_item_key => p_item_key
4052 -- ,p_actid => p_act_id
4053 -- ,p_result_code => 'NEXT');
4054
4055
4056 p_step_id := ln_transaction_step_id;
4057
4058 hr_utility.set_location(' Leaving:' || l_proc,25);
4059 EXCEPTION
4060 WHEN OTHERS THEN
4061 message := 'Exception in maintain_txn_java' || SQLERRM;
4062
4063 -- hr_utility.trace(message);
4064 hr_utility.set_location(' Leaving:' || l_proc,555);
4065 raise;
4066 --insert into dev_test values(message);
4067 --commit;
4068 END maintain_txn_java;
4069
4070
4071 PROCEDURE process_salary_java (
4072 p_item_type IN VARCHAR ,
4073 p_item_key IN VARCHAR2 ,
4074 p_act_id IN VARCHAR2 ,
4075 ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
4076 ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ,
4077 p_api_mode IN VARCHAR2,
4078 p_review_proc_call IN VARCHAR2,
4079 p_save_mode IN VARCHAR2,
4080 p_flow_mode in out nocopy varchar2, -- 2355929
4081 p_step_id OUT NOCOPY NUMBER,
4082 p_warning_msg_name IN OUT NOCOPY varchar2,
4083 p_error_msg_text IN OUT NOCOPY varchar2,
4084 p_rptg_grp_id IN VARCHAR2 DEFAULT NULL,
4085 p_plan_id IN VARCHAR2 DEFAULT NULL,
4086 p_effective_date_option IN VARCHAR2 DEFAULT NULL
4087 )
4088 IS
4089
4090 l_proc varchar2(200) := g_package || 'process_salary_java';
4091 --ltt_salary_data ltt_salary ;
4092 --ltt_component ltt_components ;
4093 ln_count NUMBER ;
4094 i NUMBER ;
4095 lv_message_number VARCHAR2(80);
4096 lv_action_value VARCHAR2(30);
4097 ln_transaction_id NUMBER ;
4098 ln_transaction_step_id NUMBER ;
4099 message VARCHAR2(5000) := '';
4100 ln_assignment_id per_all_assignments_f.assignment_id%type;
4101
4102 -- 04/19/02 Salary Basis Enhancement Change Begins
4103 lb_inv_next_sal_date_warning boolean default false;
4104 lb_proposed_salary_warning boolean default false;
4105 lb_approved_warning boolean default false;
4106 lb_payroll_warning boolean default false;
4107 ln_save_prev_salary number default null;
4108 ln_save_pay_annual_factor number default null;
4109 lb_save_point_pay2_exists boolean default false;
4110 -- 04/19/02 Salary Basis Enhancement Change Ends
4111
4112 ln_job_id number default null;
4113 ln_fte_factor number default null;
4114 ln_grade_basis varchar2(30) default null;
4115 lv_disp_warn_error_max_rate varchar2(20) default null;
4116
4117 BEGIN
4118
4119 hr_utility.set_location(' Entering:' || l_proc,5);
4120 -- 04/24/02 Change Begins
4121 -- Save the previous salary and old pay annualization factor because
4122 -- my_get_defaults will overwrite these 2 values when a salary basis is
4123 -- changed and element_type_id is different between the old and the new.
4124 ln_save_prev_salary := ltt_salary_data(1).default_prev_salary;
4125 ln_save_pay_annual_factor := ltt_salary_data(1).default_pay_annual_factor;
4126 -- 04/24/02 Change Ends
4127
4128 if(p_save_mode = 'SAVE_FOR_LATER') then
4129 if p_flow_mode is not null and
4130 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
4131 then
4132 hr_utility.set_location(l_proc,10);
4133 rollback;
4134 end if;
4135
4136 maintain_txn_java(p_item_type => p_item_type
4137 ,p_item_key => p_item_key
4138 ,p_Act_id => p_Act_id
4139 ,p_ltt_salary_data => ltt_salary_data
4140 ,p_ltt_component => ltt_component
4141 ,p_review_proc_call => p_review_proc_call
4142 ,p_step_id => p_step_id
4143 ,p_flow_mode => p_flow_mode
4144 ,p_rptg_grp_id => p_rptg_grp_id
4145 ,p_plan_id => p_plan_id
4146 ,p_effective_date_option => p_effective_date_option
4147 );
4148 else
4149
4150 -- Parameters Passed
4151 /*
4152 message := ' PROPOSAL VALUES' ||
4153 ' p_item_type' || p_item_type ||
4154 ' p_item_key' || p_item_key ||
4155 ' p_act_id' || p_act_id ||
4156 ' pay_proposal_id' || ltt_salary_data(1).pay_proposal_id ||
4157 ' assignment_id' || ltt_salary_data(1).assignment_id ||
4158 ' business_group_id' || ltt_salary_data(1).business_group_id ||
4159 ' effective_date' || ltt_salary_data(1).effective_date ||
4160 ' comments' || ltt_salary_data(1).comments ||
4161 ' next_sal_review_date' || ltt_salary_data(1).next_sal_review_date ||
4162 ' salary_change_amount' || ltt_salary_data(1).salary_change_amount ||
4163 ' salary_change_percent' || ltt_salary_data(1).salary_change_percent ||
4164 ' annual_change' || ltt_salary_data(1).annual_change ||
4165 ' proposed_salary' || ltt_salary_data(1).proposed_salary ||
4166 ' proposed_percent' || ltt_salary_data(1).proposed_percent ||
4167 ' proposal_reason' || ltt_salary_data(1).proposal_reason ||
4168 ' ranking' || ltt_salary_data(1).ranking ||
4169 ' current_salary' || ltt_salary_data(1).current_salary ||
4170 ' performance_review_id' || ltt_salary_data(1).performance_review_id ||
4171 ' multiple_components' || ltt_salary_data(1).multiple_components ||
4172 ' element_entry_id' || ltt_salary_data(1).element_entry_id ||
4173 ' selection_mode' || ltt_salary_data(1).selection_mode ||
4174 ' ovn' || ltt_salary_data(1).ovn ||
4175 ' currency' || ltt_salary_data(1).currency ||
4176 ' pay_basis_name' || ltt_salary_data(1).pay_basis_name ||
4177 ' annual_equivalent' || ltt_salary_data(1).annual_equivalent ||
4178 ' total_percent' || ltt_salary_data(1).total_percent ||
4179 ' quartile' || ltt_salary_data(1).quartile ||
4180 ' comparatio' || ltt_salary_data(1).comparatio ||
4181 ' lv_selection_mode' || ltt_salary_data(1).lv_selection_mode ||
4182 ' attribute_category' || ltt_salary_data(1).attribute_category ||
4183 ' attribute1' || ltt_salary_data(1).attribute1 ||
4184 ' attribute2' || ltt_salary_data(1).attribute2 ||
4185 ' attribute3' || ltt_salary_data(1).attribute3 ||
4186 ' attribute4' || ltt_salary_data(1).attribute4 ||
4187 ' attribute5' || ltt_salary_data(1).attribute5 ||
4188 ' attribute6' || ltt_salary_data(1).attribute6 ||
4189 ' attribute7' || ltt_salary_data(1).attribute7 ||
4190 ' attribute8' || ltt_salary_data(1).attribute8 ||
4191 ' attribute9' || ltt_salary_data(1).attribute9 ||
4192 ' attribute10' || ltt_salary_data(1).attribute10 ||
4193 ' attribute11' || ltt_salary_data(1).attribute11 ||
4194 ' attribute12' || ltt_salary_data(1).attribute12 ||
4195 ' attribute13' || ltt_salary_data(1).attribute13 ||
4196 ' attribute14' || ltt_salary_data(1).attribute14 ||
4197 ' attribute15' || ltt_salary_data(1).attribute15 ||
4198 ' attribute16' || ltt_salary_data(1).attribute16 ||
4199 ' attribute17' || ltt_salary_data(1).attribute17 ||
4200 ' attribute18' || ltt_salary_data(1).attribute18 ||
4201 ' attribute19' || ltt_salary_data(1).attribute19 ||
4202 ' attribute20' || ltt_salary_data(1).attribute20 ||
4203 ' no_of_components' || ltt_salary_data(1).no_of_components ;
4204
4205 hr_utility.trace(message);
4206
4207 for i in 1 .. ltt_component(i).
4208 message := ' COMPONENT VALUES' ||
4209 'component_id'|| ltt_component(i).component_id ||
4210 'pay_proposal_id'|| ltt_component(i).pay_proposal_id ||
4211 'approved'|| ltt_component(i).approved ||
4212 'component_reason'|| ltt_component(i).component_reason ||
4213 'reason_meaning'|| ltt_component(i).reason_meaning ||
4214 'change_amount'|| ltt_component(i).change_amount ||
4215 'change_percent'|| ltt_component(i).change_percent ||
4216 'change_annual'|| ltt_component(i).change_annual ||
4217 'comments'|| ltt_component(i).comments ||
4218 'ovn'|| ltt_component(i).ovn ||
4219 'attribute_category'|| ltt_component(i).attribute_category ||
4220 'attribute1'|| ltt_component(i).attribute1 ||
4221 'attribute2'|| ltt_component(i).attribute2 ||
4222 'attribute3'|| ltt_component(i).attribute3 ||
4223 'attribute4'|| ltt_component(i).attribute4 ||
4224 'attribute5'|| ltt_component(i).attribute5 ||
4225 'attribute6'|| ltt_component(i).attribute6 ||
4226 'attribute7'|| ltt_component(i).attribute7 ||
4227 'attribute8'|| ltt_component(i).attribute8 ||
4228 'attribute9'|| ltt_component(i).attribute9 ||
4229 'attribute10'|| ltt_component(i).attribute10 ||
4230 'attribute11'|| ltt_component(i).attribute11 ||
4231 'attribute12'|| ltt_component(i).attribute12 ||
4232 'attribute13'|| ltt_component(i).attribute13 ||
4233 'attribute14'|| ltt_component(i).attribute14 ||
4234 'attribute15'|| ltt_component(i).attribute15 ||
4235 'attribute16'|| ltt_component(i).attribute16 ||
4236 'attribute17'|| ltt_component(i).attribute17 ||
4237 'attribute18'|| ltt_component(i).attribute18 ||
4238 'attribute19'|| ltt_component(i).attribute19 ||
4239 'attribute20'|| ltt_component(i).attribute20 ||
4240 'object_version_number'|| ltt_component(i).object_version_number ;
4241
4242 hr_utility.trace(message);
4243 */
4244 --insert into dev_test values (message);
4245 --commit;
4246
4247 -- check here for date flex fields format
4248
4249 -- check here for component date flex fields format
4250 -- if error , then redisplay form
4251
4252 -- prepare data to call salary APIs
4253 -- store components in the table
4254 -- call salary APIs to validate data
4255 -- if validation succeeds , store data in txn doc
4256
4257 -- set a savepoint
4258
4259 savepoint pay2 ;
4260 lb_save_point_pay2_exists := TRUE;
4261
4262 hr_assignment_common_save_web.get_step
4263 (p_item_type => p_item_type
4264 ,p_item_key => p_item_key
4265 ,p_api_name => g_asg_api_name
4266 ,p_transaction_step_id => ln_transaction_step_id
4267 ,p_transaction_id => ln_transaction_id);
4268
4269 if ln_transaction_step_id is not null then
4270 -- if an assignment step already exists then apply the
4271 -- assignment data
4272
4273 ln_assignment_id := hr_transaction_api.get_number_value
4274 (p_transaction_step_id => ln_transaction_step_id
4275 ,p_name => 'P_ASSIGNMENT_ID');
4276
4277 ln_job_id := hr_transaction_api.get_number_value
4278 (p_transaction_step_id => ln_transaction_step_id
4279 ,p_name => 'P_JOB_ID');
4280
4281 -- Must set the element warning to TRUE so that the ASG wrapper will
4282 -- not roll back the ASG changes if element_warning = 'TRUE' whenever
4283 -- there is element entries changed.
4284 --HR_PROCESS_ASSIGNMENT_SS.PROCESS_API
4285 -- (p_validate => FALSE
4286 -- ,p_transaction_step_id => ln_transaction_step_id
4287 -- ,p_flow_mode => p_flow_mode);
4288 -- Bug 2547283: need to update person info and asg info.
4289 hr_new_user_reg_ss.process_selected_transaction
4290 (p_item_type => p_item_type,
4291 p_item_key => p_item_key);
4292 IF (( hr_process_person_ss.g_assignment_id is not null) and
4293 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID))
4294 THEN
4295 hr_utility.set_location(l_proc,15);
4296 -- Set the Assignment Id to the one just created, don't use the
4297 -- transaction table.
4298 ltt_salary_data(1).assignment_id := hr_process_person_ss.g_assignment_id;
4299 END IF;
4300 end if;
4301
4302
4303 -- 04/12/02 Salary Basis Enhancement Begins
4304 -- Call my_get_defaults to save the default values to the
4305 -- transaction table before rolling back.
4306
4307 ltt_salary_data(1).default_date := ltt_salary_data(1).effective_date;
4308
4309 my_get_defaults
4310 (p_assignment_id => ltt_salary_data(1).assignment_id
4311 ,p_date => ltt_salary_data(1).default_date
4312 ,p_business_group_id => ltt_salary_data(1).default_bg_id
4313 ,p_currency => ltt_salary_data(1).default_currency
4314 ,p_format_string =>
4315 ltt_salary_data(1).default_format_string
4316 ,p_salary_basis_name =>
4317 ltt_salary_data(1).default_salary_basis_name
4318 ,p_pay_basis_name =>
4319 ltt_salary_data(1).default_pay_basis_name
4320 ,p_pay_basis => ltt_salary_data(1).default_pay_basis
4321 ,p_grade_basis => ln_grade_basis
4322 ,p_pay_annualization_factor =>
4323 ltt_salary_data(1).default_pay_annual_factor
4324 ,p_fte_factor => ln_fte_factor
4325 ,p_grade => ltt_salary_data(1).default_grade
4326 ,p_grade_annualization_factor =>
4327 ltt_salary_data(1).default_grade_annual_factor
4328 ,p_minimum_salary =>
4329 ltt_salary_data(1).default_minimum_salary
4330 ,p_maximum_salary =>
4331 ltt_salary_data(1).default_maximum_salary
4332 ,p_midpoint_salary =>
4333 ltt_salary_data(1).default_midpoint_salary
4334 ,p_prev_salary =>
4335 ltt_salary_data(1).default_prev_salary
4336 ,p_last_change_date =>
4337 ltt_salary_data(1).default_last_change_date
4338 ,p_element_entry_id =>
4339 ltt_salary_data(1).default_element_entry_id
4340 ,p_basis_changed =>
4341 ltt_salary_data(1).default_basis_changed
4342 ,p_uom => ltt_salary_data(1).default_uom
4343 ,p_grade_uom => ltt_salary_data(1).default_grade_uom
4344 ,p_change_amount =>
4345 ltt_salary_data(1).default_change_amount
4346 ,p_change_percent =>
4347 ltt_salary_data(1).default_change_percent
4348 ,p_quartile => ltt_salary_data(1).default_quartile
4349 ,p_comparatio => ltt_salary_data(1).default_comparatio
4350 ,p_last_pay_change =>
4351 ltt_salary_data(1).default_last_pay_change
4352 ,p_flsa_status =>
4353 ltt_salary_data(1).default_flsa_status
4354 ,p_currency_symbol =>
4355 ltt_salary_data(1).default_currency_symbol
4356 ,p_precision => ltt_salary_data(1).default_precision
4357 ,p_job_id => ln_job_id
4358 );
4359
4360
4361 -- 04/12/02 Salary Basis Enhancement Begins
4362 -- Need to set the element_entry_id returned from the my_get_defaults
4363 -- before calling validate_salary_ins_api_java. Otherwise, you'll get
4364 -- the following error:
4365 -- Procedure hr_entry_api.up_ele_entry_param_val at Step 1.
4366 -- Cause: The procedure hr_entry_api.up_ele_entry_param_val has created
4367 -- an error at Step 1.
4368
4369 ltt_salary_data(1).element_entry_id :=
4370 ltt_salary_data(1).default_element_entry_id;
4371
4372 -- Now call validate_salary_ins_api_java which requires an
4373 -- element entry id existing.
4374
4375 -- 04/24/02 Change Begins
4376 -- Always reset the previous salary and old annualization factor
4377 -- with the values passed in which were saved at the beginning of the
4378 -- procedure. The reason is that my_get_defaults will call
4379 -- per_pay_proposals_populate.get_defaults which will zap the
4380 -- previous salary to null if element_type_id is different
4381 -- between the old and the new.
4382 ltt_salary_data(1).default_prev_salary := ln_save_prev_salary;
4383 ltt_salary_data(1).default_pay_annual_factor :=ln_save_pay_annual_factor;
4384 -- 04/24/02 Change Ends
4385
4386
4387 if (p_api_mode = 'INSERT') then
4388 hr_utility.set_location(l_proc,20);
4389 validate_salary_ins_api_java (
4390 p_item_type => p_item_type
4391 ,p_item_key => p_item_key
4392 ,p_act_id => p_act_id
4393 ,p_ltt_salary_data => ltt_salary_data
4394 ,p_ltt_component => ltt_component
4395 ,p_validate => TRUE
4396 ,p_inv_next_sal_date_warning => lb_inv_next_sal_date_warning
4397 ,p_proposed_salary_warning => lb_proposed_salary_warning
4398 ,p_approved_warning => lb_approved_warning
4399 ,p_payroll_warning => lb_payroll_warning
4400 );
4401 end if;
4402
4403 -- The following warning message names were obtained from PERWSEPY.fmb.
4404 IF lb_inv_next_sal_date_warning
4405 THEN
4406 hr_utility.set_location(l_proc,25);
4407 -- Need to construct the output p_warning_msg_name parm with the
4408 -- following format:
4409 -- PER,HR_7340_SAL_ASS_TERMINATED|PAY,HR_APP_PROPOS_APP_COMP| .....
4410 p_warning_msg_name := 'PER,HR_7340_SAL_ASS_TERMINATED|';
4411 END IF;
4412
4413 IF lb_proposed_salary_warning
4414 THEN
4415 hr_utility.set_location(l_proc,30);
4416 -- fix for bug#2826852
4417 -- new config option
4418 -- get the activity attribute DISP_WARN_ERROR_PAY_RATE
4419 lv_disp_warn_error_max_rate:= wf_engine.GetActivityAttrText(itemtype =>p_item_type,
4420 itemkey =>p_item_key,
4421 actid =>p_act_id,
4422 aname =>'DISP_WARN_ERROR_PAY_RATE',
4423 ignore_notfound =>true);
4424 if(lv_disp_warn_error_max_rate='WARNING') then
4425 p_warning_msg_name := p_warning_msg_name ||
4426 'PAY,HR_SAL_SAL_ELE_RANGE_WARN|';
4427 elsif(lv_disp_warn_error_max_rate='ERROR') then
4428 hr_utility.set_message(801, 'HR_SAL_SAL_ELE_RANGE_WARN');
4429 raise g_exceeded_grade_range;
4430 end if;
4431 END IF;
4432
4433 IF lb_approved_warning
4434 THEN
4435 hr_utility.set_location(l_proc,35);
4436 p_warning_msg_name := p_warning_msg_name ||
4437 'PAY,HR_APP_PROPOS_APP_COMP|';
4438 END IF;
4439
4440 IF lb_payroll_warning
4441 THEN
4442 hr_utility.set_location(l_proc,40);
4443 p_warning_msg_name := p_warning_msg_name ||
4444 'PER,HR_SAL_PAYROLL_PERIOD_CLOSED|';
4445 END IF;
4446
4447
4448 rollback to pay2 ;
4449 lb_save_point_pay2_exists := false;
4450
4451 -- hr_utility.trace('Validated Proposal and Components');
4452
4453 -- 05/03/02 Salary Basis Enhancement Begins
4454
4455 --start registration
4456 -- This is to rollback the dummy person created during the process
4457 -- request phase of the assignment page for the new user registration.
4458 -- The rollback is only for New Hire, not for Applicant Hire because
4459 -- there is no dummy person created in Applicant Hire in the txn.
4460 IF p_flow_mode is not null and
4461 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
4462 THEN
4463 hr_utility.set_location(l_proc,45);
4464 rollback;
4465 END IF;
4466 --end registration
4467
4468 -- Pass the default value obtained from my_get_defaults to
4469 -- maintain_txn_java in order to be saved to the txn table.
4470
4471 maintain_txn_java (
4472 p_item_type => p_item_type
4473 ,p_item_key => p_item_key
4474 ,p_act_id => p_act_id
4475 ,p_ltt_salary_data => ltt_salary_data
4476 ,p_ltt_component => ltt_component
4477 ,p_review_proc_call => p_review_proc_call
4478 ,p_step_id => p_step_id
4479 ,p_flow_mode => p_flow_mode
4480 ,p_rptg_grp_id => p_rptg_grp_id
4481 ,p_plan_id => p_plan_id
4482 ,p_effective_date_option => p_effective_date_option
4483 );
4484
4485 -- hr_utility.trace('End of process_salary_java');
4486 -- hr_utility.trace_off;
4487 END IF; -- end of save for later
4488
4489 hr_utility.set_location(' Leaving:' || l_proc,50);
4490 EXCEPTION
4491 WHEN hr_utility.hr_error THEN
4492 p_error_msg_text := hr_message.get_message_text;
4493
4494 --start registration
4495 -- This is to rollback the dummy person created during the process
4496 -- request phase of the assignment page for the new user registration.
4497 -- The rollback is only for New Hire, not for Applicant Hire because
4498 -- there is no dummy person created in Applicant Hire in the txn.
4499 IF p_flow_mode is not null and
4500 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
4501 THEN
4502 rollback;
4503 END IF;
4504 --end registration
4505
4506 IF lb_save_point_pay2_exists
4507 THEN
4508 rollback;
4509 END IF;
4510 hr_utility.set_location(' Leaving:' || l_proc,555);
4511
4512 WHEN g_exceeded_grade_range THEN
4513 p_error_msg_text := hr_util_misc_web.return_msg_text(
4514 p_message_name=>'HR_SAL_SAL_ELE_RANGE_WARN',
4515 p_Application_id=>'PAY');
4516
4517 --start registration
4518 -- This is to rollback the dummy person created during the process
4519 -- request phase of the assignment page for the new user registration.
4520 -- The rollback is only for New Hire, not for Applicant Hire because
4521 -- there is no dummy person created in Applicant Hire in the txn.
4522
4523 IF p_flow_mode is not null and
4524 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
4525 THEN
4526 rollback;
4527 END IF;
4528 --end registration
4529
4530 IF lb_save_point_pay2_exists
4531 THEN
4532 rollback;
4533 END IF;
4534 hr_utility.set_location(' Leaving:' || l_proc,560);
4535
4536 WHEN OTHERS THEN
4537 p_error_msg_text := hr_message.get_message_text;
4538
4539 --start registration
4540 -- This is to rollback the dummy person created during the process
4541 -- request phase of the assignment page for the new user registration.
4542 -- The rollback is only for New Hire, not for Applicant Hire because
4543 -- there is no dummy person created in Applicant Hire in the txn.
4544
4545 IF p_flow_mode is not null and
4546 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
4547 THEN
4548 rollback;
4549 END IF;
4550 --end registration
4551
4552 IF lb_save_point_pay2_exists
4553 THEN
4554 rollback;
4555 END IF;
4556 hr_utility.set_location(' Leaving:' || l_proc,565);
4557
4558 END process_salary_java ;
4559
4560 -- ---------------------------------------------------------
4561 -- Procedure to get salary details from transaction tables
4562 -- --------------------------------------------------------------
4563 PROCEDURE get_transaction_details (
4564 p_item_type IN wf_items.item_type%type ,
4565 p_item_key IN wf_items.item_key%TYPE ,
4566 p_Act_id IN VARCHAR2,
4567 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
4568 p_ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ ) IS
4569
4570 l_proc varchar2(200) := g_package || 'get_transaction_details';
4571 ln_transaction_step_id NUMBER;
4572 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
4573 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
4574 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
4575 ln_trans_step_rows NUMBER ;
4576 lv_activity_name wf_item_activity_statuses_v.activity_name%type ;
4577 ln_no_of_components NUMBER ;
4578 i INTEGER ;
4579 lv_activity_display_name VARCHAR2(100);
4580 BEGIN
4581 hr_utility.set_location(' Entering:' || l_proc,5);
4582
4583 -- hr_utility.trace_on(null,'dev_log');
4584
4585
4586
4587 ln_transaction_id := hr_transaction_ss.get_transaction_id
4588 (p_Item_Type => p_item_type,
4589 p_Item_Key => p_item_key);
4590
4591 IF ln_transaction_id IS NOT NULL
4592 THEN
4593 hr_utility.set_location(l_proc,10);
4594 -- hr_utility.trace('Transaction Exists');
4595 hr_transaction_api.get_transaction_step_info
4596 (p_Item_Type => p_item_type,
4597 p_Item_Key => p_item_key,
4598 p_activity_id =>p_act_id,
4599 p_transaction_step_id => ltt_trans_step_ids,
4600 p_object_version_number => ltt_trans_obj_vers_num,
4601 p_rows => ln_trans_step_rows);
4602
4603
4604 -- if no transaction steps are found , return
4605 IF ln_trans_step_rows < 1
4606 THEN
4607 hr_utility.set_location(' Leaving:' || l_proc,15);
4608 -- hr_utility.trace('no transaction steps are found ');
4609 RETURN ;
4610 ELSE
4611 hr_utility.set_location(l_proc,20);
4612 -- hr_utility.trace(' Transaction Step Found');
4613 hr_mee_workflow_service.get_activity_name
4614 (p_item_type => p_item_type
4615 ,p_item_key => p_item_key
4616 ,p_actid => p_act_id
4617 ,p_activity_name => lv_activity_name
4618 ,p_activity_display_name => lv_activity_display_name);
4619
4620 ln_transaction_step_id :=
4621 hr_transaction_ss.get_activity_trans_step_id
4622 (p_activity_name =>lv_activity_name,
4623 p_trans_step_id_tbl => ltt_trans_step_ids);
4624
4625 -- hr_utility.trace(' ln_transaction_step_id ' || ln_transaction_step_id);
4626
4627 -- now get the individual salary data
4628 p_ltt_salary_data(1).current_salary :=
4629 hr_transaction_api.get_number_value
4630 (p_transaction_step_id => ln_transaction_step_id,
4631 p_name =>'p_current_salary');
4632
4633 -- hr_utility.trace(' p_current_salary ' || p_ltt_salary_data(1).current_salary);
4634
4635 p_ltt_salary_data(1).assignment_id :=
4636 hr_transaction_api.get_number_value
4637 (p_transaction_step_id => ln_transaction_step_id,
4638 p_name =>'p_assignment_id');
4639
4640
4641
4642 p_ltt_salary_data(1).business_group_id :=
4643 hr_transaction_api.get_number_value
4644 (p_transaction_step_id => ln_transaction_step_id,
4645 p_name =>'p_bus_group_id');
4646
4647 -- hr_utility.trace(' p_bus_group_id ' || p_ltt_salary_data(1).business_group_id);
4648
4649 p_ltt_salary_data(1).effective_date :=
4650 hr_transaction_api.get_date_value
4651 (p_transaction_step_id => ln_transaction_step_id,
4652 p_name =>'p_effective_date');
4653
4654 -- GSP change
4655 p_ltt_salary_data(1).salary_effective_date :=
4656 hr_transaction_api.get_date_value
4657 (p_transaction_step_id => ln_transaction_step_id,
4658 p_name =>'p_effective_date');
4659
4660 p_ltt_salary_data(1).gsp_dummy_txn :=
4661 hr_transaction_api.get_varchar2_value
4662 (p_transaction_step_id => ln_transaction_step_id,
4663 p_name =>'p_gsp_dummy_txn');
4664 --End of GSP change
4665
4666 p_ltt_salary_data(1).salary_change_amount:=
4667 hr_transaction_api.get_number_value
4668 (p_transaction_step_id => ln_transaction_step_id,
4669 p_name =>'p_change_amount');
4670
4671 -- hr_utility.trace(' p_change_amount ' || p_ltt_salary_data(1).salary_change_amount);
4672
4673 p_ltt_salary_data(1).annual_change:=
4674 hr_transaction_api.get_number_value
4675 (p_transaction_step_id => ln_transaction_step_id,
4676 p_name =>'p_annual_change');
4677
4678 -- hr_utility.trace(' p_annual_change ' || p_ltt_salary_data(1).annual_change);
4679
4680
4681 p_ltt_salary_data(1).salary_change_percent:=
4682 hr_transaction_api.get_number_value
4683 (p_transaction_step_id => ln_transaction_step_id,
4684 p_name =>'p_change_percent');
4685
4686 p_ltt_salary_data(1).proposed_salary:=
4687 hr_transaction_api.get_number_value
4688 (p_transaction_step_id => ln_transaction_step_id,
4689 p_name => 'p_proposed_salary');
4690
4691 p_ltt_salary_data(1).proposal_reason:=
4692 hr_transaction_api.get_varchar2_value
4693 (p_transaction_step_id => ln_transaction_step_id,
4694 p_name => 'p_proposal_reason');
4695
4696 p_ltt_salary_data(1).currency:=
4697 hr_transaction_api.get_varchar2_value
4698 (p_transaction_step_id => ln_transaction_step_id,
4699 p_name => 'p_currency');
4700
4701
4702 p_ltt_salary_data(1).pay_basis_name:=
4703 hr_transaction_api.get_varchar2_value
4704 (p_transaction_step_id => ln_transaction_step_id,
4705 p_name => 'p_pay_basis_name');
4706
4707 p_ltt_salary_data(1).element_entry_id:=
4708 hr_transaction_api.get_number_value
4709 (p_transaction_step_id => ln_transaction_step_id,
4710 p_name => 'p_element_entry_id');
4711
4712
4713 p_ltt_salary_data(1).annual_equivalent:=
4714 hr_transaction_api.get_number_value
4715 (p_transaction_step_id => ln_transaction_step_id,
4716 p_name => 'p_annual_equivalent');
4717
4718
4719 p_ltt_salary_data(1).total_percent:=
4720 hr_transaction_api.get_number_value
4721 (p_transaction_step_id => ln_transaction_step_id,
4722 p_name => 'p_total_percent');
4723
4724 p_ltt_salary_data(1).selection_mode:=
4725 hr_transaction_api.get_varchar2_value
4726 (p_transaction_step_id => ln_transaction_step_id,
4727 p_name => 'p_selection_mode');
4728
4729 p_ltt_salary_data(1).quartile:=
4730 hr_transaction_api.get_number_value
4731 (p_transaction_step_id => ln_transaction_step_id,
4732 p_name => 'p_quartile');
4733
4734 p_ltt_salary_data(1).comparatio:=
4735 hr_transaction_api.get_number_value
4736 (p_transaction_step_id => ln_transaction_step_id,
4737 p_name => 'p_comparatio');
4738
4739 p_ltt_salary_data(1).ranking:=
4740 hr_transaction_api.get_number_value
4741 (p_transaction_step_id => ln_transaction_step_id,
4742 p_name => 'p_ranking');
4743
4744
4745 p_ltt_salary_data(1).multiple_components:=
4746 hr_transaction_api.get_varchar2_value
4747 (p_transaction_step_id => ln_transaction_step_id,
4748 p_name => 'p_multiple_components');
4749
4750
4751 p_ltt_salary_data(1).comments:=
4752 hr_transaction_api.get_varchar2_value
4753 (p_transaction_step_id => ln_transaction_step_id,
4754 p_name => 'p_comments');
4755
4756
4757 p_ltt_salary_data(1).attribute_category :=
4758 hr_transaction_api.get_varchar2_value (
4759 p_transaction_step_id => ln_transaction_step_id,
4760 p_name => 'p_attribute_category'
4761 );
4762
4763 p_ltt_salary_data(1).attribute1 :=
4764 hr_transaction_api.get_varchar2_value (
4765 p_transaction_step_id => ln_transaction_step_id,
4766 p_name => 'p_attribute1'
4767 );
4768
4769 -- hr_utility.trace(' p_attribute1 ' || p_ltt_salary_data(1).attribute1);
4770
4771 p_ltt_salary_data(1).attribute2 :=
4772 hr_transaction_api.get_varchar2_value (
4773 p_transaction_step_id => ln_transaction_step_id,
4774 p_name => 'p_attribute2'
4775 );
4776
4777 -- hr_utility.trace(' p_attribute2 ' || p_ltt_salary_data(1).attribute2);
4778
4779 p_ltt_salary_data(1).attribute3 :=
4780 hr_transaction_api.get_varchar2_value (
4781 p_transaction_step_id => ln_transaction_step_id,
4782 p_name => 'p_attribute3'
4783 );
4784
4785 p_ltt_salary_data(1).attribute4 :=
4786 hr_transaction_api.get_varchar2_value (
4787 p_transaction_step_id => ln_transaction_step_id,
4788 p_name => 'p_attribute4'
4789 );
4790
4791
4792 p_ltt_salary_data(1).attribute5 :=
4793 hr_transaction_api.get_varchar2_value (
4794 p_transaction_step_id => ln_transaction_step_id,
4795 p_name => 'p_attribute5'
4796 );
4797
4798 p_ltt_salary_data(1).attribute6 :=
4799 hr_transaction_api.get_varchar2_value (
4800 p_transaction_step_id => ln_transaction_step_id,
4801 p_name => 'p_attribute6'
4802 );
4803
4804
4805 p_ltt_salary_data(1).attribute7 :=
4806 hr_transaction_api.get_varchar2_value (
4807 p_transaction_step_id => ln_transaction_step_id,
4808 p_name => 'p_attribute7'
4809 );
4810
4811 p_ltt_salary_data(1).attribute8 :=
4812 hr_transaction_api.get_varchar2_value (
4813 p_transaction_step_id => ln_transaction_step_id,
4814 p_name => 'p_attribute8'
4815 );
4816
4817 p_ltt_salary_data(1).attribute9 :=
4818 hr_transaction_api.get_varchar2_value (
4819 p_transaction_step_id => ln_transaction_step_id,
4820 p_name => 'p_attribute9'
4821 );
4822
4823 p_ltt_salary_data(1).attribute10 :=
4824 hr_transaction_api.get_varchar2_value (
4825 p_transaction_step_id => ln_transaction_step_id,
4826 p_name => 'p_attribute10'
4827 );
4828
4829 p_ltt_salary_data(1).attribute11 :=
4830 hr_transaction_api.get_varchar2_value (
4831 p_transaction_step_id => ln_transaction_step_id,
4832 p_name => 'p_attribute11'
4833 );
4834
4835 p_ltt_salary_data(1).attribute12 :=
4836 hr_transaction_api.get_varchar2_value (
4837 p_transaction_step_id => ln_transaction_step_id,
4838 p_name => 'p_attribute12'
4839 );
4840
4841 p_ltt_salary_data(1).attribute13 :=
4842 hr_transaction_api.get_varchar2_value (
4843 p_transaction_step_id => ln_transaction_step_id,
4844 p_name => 'p_attribute13'
4845 );
4846
4847 p_ltt_salary_data(1).attribute14 :=
4848 hr_transaction_api.get_varchar2_value (
4849 p_transaction_step_id => ln_transaction_step_id,
4850 p_name => 'p_attribute14'
4851 );
4852
4853 p_ltt_salary_data(1).attribute15 :=
4854 hr_transaction_api.get_varchar2_value (
4855 p_transaction_step_id => ln_transaction_step_id,
4856 p_name => 'p_attribute15'
4857 );
4858
4859 p_ltt_salary_data(1).attribute16 :=
4860 hr_transaction_api.get_varchar2_value (
4861 p_transaction_step_id => ln_transaction_step_id,
4862 p_name => 'p_attribute16'
4863 );
4864
4865
4866 p_ltt_salary_data(1).attribute17 :=
4867 hr_transaction_api.get_varchar2_value (
4868 p_transaction_step_id => ln_transaction_step_id,
4869 p_name => 'p_attribute17'
4870 );
4871
4872 p_ltt_salary_data(1).attribute18 :=
4873 hr_transaction_api.get_varchar2_value (
4874 p_transaction_step_id => ln_transaction_step_id,
4875 p_name => 'p_attribute18'
4876 );
4877
4878 p_ltt_salary_data(1).attribute19 :=
4879 hr_transaction_api.get_varchar2_value (
4880 p_transaction_step_id => ln_transaction_step_id,
4881 p_name => 'p_attribute19'
4882 );
4883
4884 p_ltt_salary_data(1).attribute20 :=
4885 hr_transaction_api.get_varchar2_value (
4886 p_transaction_step_id => ln_transaction_step_id,
4887 p_name => 'p_attribute20'
4888 );
4889
4890 p_ltt_salary_data(1).no_of_components :=
4891 hr_transaction_api.get_number_value (
4892 p_transaction_step_id => ln_transaction_step_id,
4893 p_name => 'p_no_of_components'
4894 );
4895
4896 -- 04/12/02 Salary Basis Enhancement Begins
4897 p_ltt_salary_data(1).salary_basis_change_type :=
4898 hr_transaction_api.get_varchar2_value (
4899 p_transaction_step_id => ln_transaction_step_id,
4900 p_name => 'p_salary_basis_change_type'
4901 );
4902
4903 p_ltt_salary_data(1).default_date :=
4904 hr_transaction_api.get_date_value (
4905 p_transaction_step_id => ln_transaction_step_id,
4906 p_name => 'p_default_date'
4907 );
4908
4909 p_ltt_salary_data(1).default_bg_id :=
4910 hr_transaction_api.get_number_value (
4911 p_transaction_step_id => ln_transaction_step_id,
4912 p_name => 'p_default_bg_id'
4913 );
4914
4915 p_ltt_salary_data(1).default_currency :=
4916 hr_transaction_api.get_varchar2_value (
4917 p_transaction_step_id => ln_transaction_step_id,
4918 p_name => 'p_default_currency'
4919 );
4920
4921 p_ltt_salary_data(1).default_format_string :=
4922 hr_transaction_api.get_varchar2_value (
4923 p_transaction_step_id => ln_transaction_step_id,
4924 p_name => 'p_default_format_string'
4925 );
4926
4927 p_ltt_salary_data(1).default_salary_basis_name :=
4928 hr_transaction_api.get_varchar2_value (
4929 p_transaction_step_id => ln_transaction_step_id,
4930 p_name => 'p_default_salary_basis_name'
4931 );
4932
4933 p_ltt_salary_data(1).default_pay_basis_name :=
4934 hr_transaction_api.get_varchar2_value (
4935 p_transaction_step_id => ln_transaction_step_id,
4936 p_name => 'p_default_pay_basis_name'
4937 );
4938
4939 p_ltt_salary_data(1).default_pay_basis :=
4940 hr_transaction_api.get_varchar2_value (
4941 p_transaction_step_id => ln_transaction_step_id,
4942 p_name => 'p_default_pay_basis'
4943 );
4944
4945 p_ltt_salary_data(1).default_pay_annual_factor :=
4946 hr_transaction_api.get_number_value (
4947 p_transaction_step_id => ln_transaction_step_id,
4948 p_name => 'p_default_pay_annual_factor'
4949 );
4950
4951 p_ltt_salary_data(1).default_grade :=
4952 hr_transaction_api.get_varchar2_value (
4953 p_transaction_step_id => ln_transaction_step_id,
4954 p_name => 'p_default_grade'
4955 );
4956
4957 p_ltt_salary_data(1).default_grade_annual_factor :=
4958 hr_transaction_api.get_number_value (
4959 p_transaction_step_id => ln_transaction_step_id,
4960 p_name => 'p_default_grade_annual_factor'
4961 );
4962
4963 p_ltt_salary_data(1).default_minimum_salary :=
4964 hr_transaction_api.get_number_value (
4965 p_transaction_step_id => ln_transaction_step_id,
4966 p_name => 'p_default_minimum_salary'
4967 );
4968
4969 p_ltt_salary_data(1).default_maximum_salary :=
4970 hr_transaction_api.get_number_value (
4971 p_transaction_step_id => ln_transaction_step_id,
4972 p_name => 'p_default_maximum_salary'
4973 );
4974
4975 p_ltt_salary_data(1).default_midpoint_salary :=
4976 hr_transaction_api.get_number_value (
4977 p_transaction_step_id => ln_transaction_step_id,
4978 p_name => 'p_default_midpoint_salary'
4979 );
4980
4981 p_ltt_salary_data(1).default_prev_salary :=
4982 hr_transaction_api.get_number_value (
4983 p_transaction_step_id => ln_transaction_step_id,
4984 p_name => 'p_default_prev_salary'
4985 );
4986
4987 p_ltt_salary_data(1).default_last_change_date :=
4988 hr_transaction_api.get_date_value (
4989 p_transaction_step_id => ln_transaction_step_id,
4990 p_name => 'p_default_last_change_date'
4991 );
4992
4993 p_ltt_salary_data(1).default_element_entry_id :=
4994 hr_transaction_api.get_number_value (
4995 p_transaction_step_id => ln_transaction_step_id,
4996 p_name => 'p_default_element_entry_id'
4997 );
4998
4999 p_ltt_salary_data(1).default_basis_changed :=
5000 hr_transaction_api.get_number_value (
5001 p_transaction_step_id => ln_transaction_step_id,
5002 p_name => 'p_default_basis_changed'
5003 );
5004
5005 p_ltt_salary_data(1).default_uom :=
5006 hr_transaction_api.get_varchar2_value (
5007 p_transaction_step_id => ln_transaction_step_id,
5008 p_name => 'p_default_uom'
5009 );
5010
5011 p_ltt_salary_data(1).default_grade_uom :=
5012 hr_transaction_api.get_varchar2_value (
5013 p_transaction_step_id => ln_transaction_step_id,
5014 p_name => 'p_default_grade_uom'
5015 );
5016
5017 p_ltt_salary_data(1).default_change_amount :=
5018 hr_transaction_api.get_number_value (
5019 p_transaction_step_id => ln_transaction_step_id,
5020 p_name => 'p_default_change_amount'
5021 );
5022
5023 p_ltt_salary_data(1).default_change_percent :=
5024 hr_transaction_api.get_number_value (
5025 p_transaction_step_id => ln_transaction_step_id,
5026 p_name => 'p_default_change_percent'
5027 );
5028
5029 p_ltt_salary_data(1).default_quartile :=
5030 hr_transaction_api.get_number_value (
5031 p_transaction_step_id => ln_transaction_step_id,
5032 p_name => 'p_default_quartile'
5033 );
5034
5035 p_ltt_salary_data(1).default_comparatio :=
5036 hr_transaction_api.get_number_value (
5037 p_transaction_step_id => ln_transaction_step_id,
5038 p_name => 'p_default_comparatio'
5039 );
5040
5041 p_ltt_salary_data(1).default_last_pay_change :=
5042 hr_transaction_api.get_varchar2_value (
5043 p_transaction_step_id => ln_transaction_step_id,
5044 p_name => 'p_default_last_pay_change'
5045 );
5046
5047 p_ltt_salary_data(1).default_flsa_status :=
5048 hr_transaction_api.get_varchar2_value (
5049 p_transaction_step_id => ln_transaction_step_id,
5050 p_name => 'p_default_flsa_status'
5051 );
5052
5053 p_ltt_salary_data(1).default_currency_symbol :=
5054 hr_transaction_api.get_varchar2_value (
5055 p_transaction_step_id => ln_transaction_step_id,
5056 p_name => 'p_default_currency_symbol'
5057 );
5058
5059 p_ltt_salary_data(1).default_precision :=
5060 hr_transaction_api.get_number_value (
5061 p_transaction_step_id => ln_transaction_step_id,
5062 p_name => 'p_default_precision'
5063 );
5064
5065 -- 04/12/02 Salary Basis Enhancement Ends'
5066
5067 -- hr_utility.trace('Populated Proposal Values');
5068
5069 -- now get the component records
5070 ln_no_of_components :=
5071 hr_transaction_api.get_number_value
5072 (p_transaction_step_id => ln_transaction_step_id,
5073 p_name => 'p_no_of_components');
5074
5075 -- hr_utility.trace('ln_no_of_components' || ln_no_of_components);
5076
5077 FOR i in 1..ln_no_of_components
5078 LOOP
5079
5080 -- hr_utility.trace(' Retrieving ' || i || ' compoent ');
5081 p_ltt_component(i).change_amount :=
5082 hr_transaction_api.get_number_value
5083 (p_transaction_step_id => ln_transaction_step_id,
5084 p_name => 'p_change_amount'||i);
5085 -- hr_utility.trace('change_amount ' || p_ltt_component(i).change_amount);
5086
5087
5088
5089 p_ltt_component(i).component_reason :=
5090 hr_transaction_api.get_varchar2_value
5091 (p_transaction_step_id => ln_transaction_step_id,
5092 p_name => 'p_component_reason'||i);
5093 -- hr_utility.trace('p_component_reason ' || p_ltt_component(i).component_reason);
5094
5095 p_ltt_component(i).reason_meaning :=
5096 hr_transaction_api.get_varchar2_value
5097 (p_transaction_step_id => ln_transaction_step_id,
5098 p_name => 'p_reason_meaning'||i);
5099 -- hr_utility.trace('p_reason_meaning ' || p_ltt_component(i).reason_meaning);
5100
5101 /*
5102 hr_misc_web.get_lookup_meaning(
5103 p_ltt_component(i).component_reason,
5104 'PROPOSAL_REASON',
5105 p_ltt_salary_data(1).effective_date);
5106 hr_utility.trace('reason_meaning ' || p_ltt_component(i).reason_meaning);
5107 */
5108
5109 p_ltt_component(i).change_percent :=
5110 hr_transaction_api.get_number_value
5111 (p_transaction_step_id => ln_transaction_step_id,
5112 p_name => 'p_change_percent'||i);
5113
5114 -- hr_utility.trace('p_change_percent ' || p_ltt_component(i).change_percent);
5115
5116 p_ltt_component(i).change_annual :=
5117 hr_transaction_api.get_number_value
5118 (p_transaction_step_id => ln_transaction_step_id,
5119 p_name => 'p_change_annual'||i);
5120
5121 p_ltt_component(i).comments :=
5122 hr_transaction_api.get_varchar2_value
5123 (p_transaction_step_id => ln_transaction_step_id,
5124 p_name => 'p_comments'||i);
5125
5126
5127 p_ltt_component(i).approved :=
5128 hr_transaction_api.get_varchar2_value
5129 (p_transaction_step_id => ln_transaction_step_id,
5130 p_name => 'p_approved'||i);
5131
5132 p_ltt_component(i).attribute_category :=
5133 hr_transaction_api.get_varchar2_value
5134 (p_transaction_step_id => ln_transaction_step_id,
5135 p_name => 'p_cattribute_category'||i);
5136
5137 p_ltt_component(i).attribute1 :=
5138 hr_transaction_api.get_varchar2_value
5139 (p_transaction_step_id => ln_transaction_step_id,
5140 p_name => 'p_cattribute1'||i);
5141
5142 -- hr_utility.trace('p_cattribute1 ' || p_ltt_component(i).attribute1);
5143
5144
5145 p_ltt_component(i).attribute2:=
5146 hr_transaction_api.get_varchar2_value
5147 (p_transaction_step_id => ln_transaction_step_id,
5148 p_name => 'p_cattribute2'||i);
5149
5150 -- hr_utility.trace('p_cattribute1 ' || p_ltt_component(i).attribute2);
5151
5152
5153 p_ltt_component(i).attribute3 :=
5154 hr_transaction_api.get_varchar2_value
5155 (p_transaction_step_id => ln_transaction_step_id,
5156 p_name => 'p_cattribute3'||i);
5157
5158 -- hr_utility.trace('p_cattribute1 ');
5159
5160 p_ltt_component(i).attribute4 :=
5161 hr_transaction_api.get_varchar2_value
5162 (p_transaction_step_id => ln_transaction_step_id,
5163 p_name => 'p_cattribute4'||i);
5164
5165 p_ltt_component(i).attribute5 :=
5166 hr_transaction_api.get_varchar2_value
5167 (p_transaction_step_id => ln_transaction_step_id,
5168 p_name => 'p_cattribute5'||i);
5169
5170 -- hr_utility.trace('p_cattribute5 ');
5171
5172 p_ltt_component(i).attribute6 :=
5173 hr_transaction_api.get_varchar2_value
5174 (p_transaction_step_id => ln_transaction_step_id,
5175 p_name => 'p_cattribute6'||i);
5176
5177 p_ltt_component(i).attribute7 :=
5178 hr_transaction_api.get_varchar2_value
5179 (p_transaction_step_id => ln_transaction_step_id,
5180 p_name => 'p_cattribute7'||i);
5181
5182 -- hr_utility.trace('p_cattribute7 ');
5183
5184
5185 p_ltt_component(i).attribute8 :=
5186 hr_transaction_api.get_varchar2_value
5187 (p_transaction_step_id => ln_transaction_step_id,
5188 p_name => 'p_cattribute8'||i);
5189
5190 p_ltt_component(i).attribute9 :=
5191 hr_transaction_api.get_varchar2_value
5192 (p_transaction_step_id => ln_transaction_step_id,
5193 p_name => 'p_cattribute9'||i);
5194
5195 -- hr_utility.trace('p_cattribute9 ');
5196
5197 p_ltt_component(i).attribute10 :=
5198 hr_transaction_api.get_varchar2_value
5199 (p_transaction_step_id => ln_transaction_step_id,
5200 p_name => 'p_cattribute10'||i);
5201
5202 p_ltt_component(i).attribute11 :=
5203 hr_transaction_api.get_varchar2_value
5204 (p_transaction_step_id => ln_transaction_step_id,
5205 p_name => 'p_cattribute11'||i);
5206
5207 p_ltt_component(i).attribute12 :=
5208 hr_transaction_api.get_varchar2_value
5209 (p_transaction_step_id => ln_transaction_step_id,
5210 p_name => 'p_cattribute12'||i);
5211
5212 -- hr_utility.trace('p_cattribute12 ');
5213
5214 p_ltt_component(i).attribute13 :=
5215 hr_transaction_api.get_varchar2_value
5216 (p_transaction_step_id => ln_transaction_step_id,
5217 p_name => 'p_cattribute13'||i);
5218
5219 p_ltt_component(i).attribute14 :=
5220 hr_transaction_api.get_varchar2_value
5221 (p_transaction_step_id => ln_transaction_step_id,
5222 p_name => 'p_cattribute14'||i);
5223
5224 -- hr_utility.trace('p_cattribute14 ');
5225
5226 p_ltt_component(i).attribute15 :=
5227 hr_transaction_api.get_varchar2_value
5228 (p_transaction_step_id => ln_transaction_step_id,
5229 p_name => 'p_cattribute15'||i);
5230
5231 p_ltt_component(i).attribute16 :=
5232 hr_transaction_api.get_varchar2_value
5233 (p_transaction_step_id => ln_transaction_step_id,
5234 p_name => 'p_cattribute16'||i);
5235
5236 -- hr_utility.trace('p_cattribute16 ');
5237
5238 p_ltt_component(i).attribute17 :=
5239 hr_transaction_api.get_varchar2_value
5240 (p_transaction_step_id => ln_transaction_step_id,
5241 p_name => 'p_cattribute17'||i);
5242
5243 p_ltt_component(i).attribute18 :=
5244 hr_transaction_api.get_varchar2_value
5245 (p_transaction_step_id => ln_transaction_step_id,
5246 p_name => 'p_cattribute18'||i);
5247
5248 -- hr_utility.trace('p_cattribute18 ');
5249
5250 p_ltt_component(i).attribute19 :=
5251 hr_transaction_api.get_varchar2_value
5252 (p_transaction_step_id => ln_transaction_step_id,
5253 p_name => 'p_cattribute19'||i);
5254
5255 p_ltt_component(i).attribute20 :=
5256 hr_transaction_api.get_varchar2_value
5257 (p_transaction_step_id => ln_transaction_step_id,
5258 p_name => 'p_cattribute20'||i);
5259
5260 -- hr_utility.trace('p_cattribute20 ' || p_ltt_component(i).attribute20);
5261
5262 END LOOP ;
5263
5264 END IF ;
5265 END IF ;
5266
5267
5268 hr_utility.set_location(' Leaving:' || l_proc,25);
5269 -- hr_utility.trace_off;
5270
5271 EXCEPTION
5272 WHEN OTHERS THEN
5273 hr_utility.set_location(' Leaving:' || l_proc,555);
5274 raise;
5275 END get_transaction_details;
5276
5277 --GSP changes
5278 -- ---------------------------------------------------------
5279 -- Procedure to get salary details from transaction tables
5280 -- --------------------------------------------------------------
5281 PROCEDURE get_txn_details_for_review (
5282 p_item_type IN wf_items.item_type%type ,
5283 p_item_key IN wf_items.item_key%TYPE ,
5284 p_transaction_step_id IN VARCHAR2,
5285 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
5286 p_ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ ) IS
5287
5288 l_proc varchar2(200) := g_package || 'get_txn_details_for_review';
5289 ln_transaction_step_id NUMBER;
5290 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
5291 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
5292 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
5293 ln_trans_step_rows NUMBER ;
5294 lv_activity_name wf_item_activity_statuses_v.activity_name%type ;
5295 ln_no_of_components NUMBER ;
5296 i INTEGER ;
5297 lv_activity_display_name VARCHAR2(100);
5298 lv_currency VARCHAR2(10);
5299 uom PAY_INPUT_VALUES_F.UOM%TYPE;
5300 return_mask VARCHAR2(100);
5301
5302 lv_old_currency PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE%TYPE;
5303 lv_old_salary_basis_name PER_PAY_BASES.NAME%TYPE;
5304 lv_old_pay_basis_name HR_LOOKUPS.MEANING%TYPE;
5305 lv_old_pay_basis PER_PAY_BASES.PAY_BASIS%TYPE;
5306 ln_old_pay_annual_factor PER_PAY_BASES.PAY_ANNUALIZATION_FACTOR%TYPE;
5307 lv_old_grade_basis PER_PAY_BASES.RATE_BASIS%TYPE;
5308 ln_old_grade_annual_factor PER_PAY_BASES.GRADE_ANNUALIZATION_FACTOR%TYPE;
5309 ln_old_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
5310 lv_old_uom PAY_INPUT_VALUES_F.UOM%TYPE;
5311 lv_old_currency_symbol FND_CURRENCIES_VL.SYMBOL%TYPE;
5312 lv_tmp_currency PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE%TYPE;
5313 lv_old_fte_factor number default null;
5314 l_factor number default null;
5315
5316 BEGIN
5317
5318 hr_utility.set_location(' Entering:' || l_proc,5);
5319 -- hr_utility.trace('starting of get_transaction_details');
5320
5321 IF p_transaction_step_id IS NOT NULL
5322 THEN
5323
5324 hr_utility.set_location(l_proc,10);
5325 ln_transaction_step_id := p_transaction_step_id;
5326
5327 PER_PAY_PROPOSALS_POPULATE.GET_BASIS_DETAILS(p_effective_date => hr_transaction_api.get_date_value
5328 (p_transaction_step_id => ln_transaction_step_id,
5329 p_name => 'p_effective_date')
5330 ,p_assignment_id => hr_transaction_api.get_number_value
5331 (p_transaction_step_id => ln_transaction_step_id,
5332 p_name => 'p_assignment_id')
5333 ,p_currency => lv_old_currency
5334 ,p_salary_basis_name => lv_old_salary_basis_name
5335 ,p_pay_basis_name => lv_old_pay_basis_name
5336 ,p_pay_basis => lv_old_pay_basis
5337 ,p_pay_annualization_factor => ln_old_pay_annual_factor
5338 ,p_grade_basis => lv_old_grade_basis
5339 ,p_grade_annualization_factor => ln_old_grade_annual_factor
5340 ,p_element_type_id => ln_old_element_type_id
5341 ,p_uom => lv_old_uom);
5342
5343 lv_old_fte_factor := per_saladmin_utility.get_fte_factor(
5344 hr_transaction_api.get_number_value (p_transaction_step_id => ln_transaction_step_id,
5345 p_name => 'p_assignment_id'),
5346 hr_transaction_api.get_date_value (p_transaction_step_id => ln_transaction_step_id,
5347 p_name => 'p_effective_date'));
5348
5349 lv_currency :=
5350 hr_transaction_api.get_varchar2_value
5351 (p_transaction_step_id => ln_transaction_step_id,
5352 p_name => 'p_currency');
5353
5354 uom := hr_transaction_api.get_varchar2_value (
5355 p_transaction_step_id => ln_transaction_step_id,
5356 p_name => 'p_default_uom');
5357
5358 fnd_currency.BUILD_FORMAT_MASK(return_mask, 25, 5, null);
5359
5360 IF lv_old_currency <> lv_currency THEN
5361 lv_tmp_currency := lv_old_currency;
5362 ELSE
5363 lv_tmp_currency := lv_currency;
5364 END IF;
5365
5366 -- now get the individual salary data
5367 p_ltt_salary_data(1).current_salary :=
5368 hr_transaction_api.get_number_value
5369 (p_transaction_step_id => ln_transaction_step_id,
5370 p_name =>'p_current_salary');
5371
5372 if (uom='N') then
5373 p_ltt_salary_data(1).curr_sal_mc := to_char(p_ltt_salary_data(1).current_salary,return_mask) || ' ' || lv_tmp_currency;
5374 else
5375 p_ltt_salary_data(1).curr_sal_mc := hr_util_misc_ss.get_in_preferred_currency_str(p_ltt_salary_data(1).current_salary,lv_tmp_currency,trunc(sysdate));
5376 end if;
5377
5378 p_ltt_salary_data(1).assignment_id :=
5379 hr_transaction_api.get_number_value
5380 (p_transaction_step_id => ln_transaction_step_id,
5381 p_name =>'p_assignment_id');
5382
5383
5384 p_ltt_salary_data(1).business_group_id :=
5385 hr_transaction_api.get_number_value
5386 (p_transaction_step_id => ln_transaction_step_id,
5387 p_name =>'p_bus_group_id');
5388
5389 p_ltt_salary_data(1).effective_date :=
5390 hr_transaction_api.get_date_value
5391 (p_transaction_step_id => ln_transaction_step_id,
5392 p_name =>'p_effective_date');
5393
5394 -- GSP change
5395 p_ltt_salary_data(1).salary_effective_date :=
5396 hr_transaction_api.get_date_value
5397 (p_transaction_step_id => ln_transaction_step_id,
5398 p_name =>'p_effective_date');
5399
5400 p_ltt_salary_data(1).gsp_dummy_txn :=
5401 hr_transaction_api.get_varchar2_value
5402 (p_transaction_step_id => ln_transaction_step_id,
5403 p_name =>'p_gsp_dummy_txn');
5404 -- End of GSP change
5405
5406 p_ltt_salary_data(1).salary_change_amount:=
5407 hr_transaction_api.get_number_value
5408 (p_transaction_step_id => ln_transaction_step_id,
5409 p_name =>'p_change_amount');
5410
5411 if (uom='N') then
5412 if (p_ltt_salary_data(1).salary_change_amount is not null) then
5413 p_ltt_salary_data(1).chg_amt_mc := to_char(p_ltt_salary_data(1).salary_change_amount,return_mask) || ' ' || lv_currency;
5414 else
5415 p_ltt_salary_data(1).chg_amt_mc := null;
5416 end if;
5417 else
5418 p_ltt_salary_data(1).chg_amt_mc := hr_util_misc_ss.get_in_preferred_currency_str(p_ltt_salary_data(1).salary_change_amount,lv_currency,trunc(sysdate));
5419 end if;
5420
5421 p_ltt_salary_data(1).annual_change:=
5422 hr_transaction_api.get_number_value
5423 (p_transaction_step_id => ln_transaction_step_id,
5424 p_name =>'p_annual_change');
5425
5426
5427 p_ltt_salary_data(1).salary_change_percent:=
5428 hr_transaction_api.get_number_value
5429 (p_transaction_step_id => ln_transaction_step_id,
5430 p_name =>'p_change_percent');
5431
5432 p_ltt_salary_data(1).proposed_salary:=
5433 hr_transaction_api.get_number_value
5434 (p_transaction_step_id => ln_transaction_step_id,
5435 p_name => 'p_proposed_salary');
5436
5437 if (uom='N') then
5438 p_ltt_salary_data(1).prop_sal_mc := to_char(p_ltt_salary_data(1).proposed_salary,return_mask) || ' ' || lv_currency;
5439 else
5440 p_ltt_salary_data(1).prop_sal_mc := hr_util_misc_ss.get_in_preferred_currency_str(p_ltt_salary_data(1).proposed_salary,lv_currency,trunc(sysdate));
5441 end if;
5442
5443 p_ltt_salary_data(1).proposal_reason:=
5444 hr_transaction_api.get_varchar2_value
5445 (p_transaction_step_id => ln_transaction_step_id,
5446 p_name => 'p_proposal_reason');
5447
5448 p_ltt_salary_data(1).currency:=
5449 hr_transaction_api.get_varchar2_value
5450 (p_transaction_step_id => ln_transaction_step_id,
5451 p_name => 'p_currency');
5452
5453 p_ltt_salary_data(1).pay_basis_name:=
5454 hr_transaction_api.get_varchar2_value
5455 (p_transaction_step_id => ln_transaction_step_id,
5456 p_name => 'p_pay_basis_name');
5457
5458 p_ltt_salary_data(1).element_entry_id:=
5459 hr_transaction_api.get_number_value
5460 (p_transaction_step_id => ln_transaction_step_id,
5461 p_name => 'p_element_entry_id');
5462
5463 p_ltt_salary_data(1).annual_equivalent:=
5464 hr_transaction_api.get_number_value
5465 (p_transaction_step_id => ln_transaction_step_id,
5466 p_name => 'p_annual_equivalent');
5467
5468 if (uom='N') then
5469 p_ltt_salary_data(1).prop_ann_eqv_mc := to_char(p_ltt_salary_data(1).annual_equivalent,return_mask) || ' ' || lv_currency;
5470 else
5471 p_ltt_salary_data(1).prop_ann_eqv_mc := hr_util_misc_ss.get_in_preferred_currency_str(p_ltt_salary_data(1).annual_equivalent,lv_currency,trunc(sysdate));
5472 end if;
5473
5474 p_ltt_salary_data(1).total_percent:=
5475 hr_transaction_api.get_number_value
5476 (p_transaction_step_id => ln_transaction_step_id,
5477 p_name => 'p_total_percent');
5478
5479 p_ltt_salary_data(1).selection_mode:=
5480 hr_transaction_api.get_varchar2_value
5481 (p_transaction_step_id => ln_transaction_step_id,
5482 p_name => 'p_selection_mode');
5483
5484 p_ltt_salary_data(1).quartile:=
5485 hr_transaction_api.get_number_value
5486 (p_transaction_step_id => ln_transaction_step_id,
5487 p_name => 'p_quartile');
5488
5489 p_ltt_salary_data(1).comparatio:=
5490 hr_transaction_api.get_number_value
5491 (p_transaction_step_id => ln_transaction_step_id,
5492 p_name => 'p_comparatio');
5493
5494 p_ltt_salary_data(1).ranking:=
5495 hr_transaction_api.get_number_value
5496 (p_transaction_step_id => ln_transaction_step_id,
5497 p_name => 'p_ranking');
5498
5499 p_ltt_salary_data(1).multiple_components:=
5500 hr_transaction_api.get_varchar2_value
5501 (p_transaction_step_id => ln_transaction_step_id,
5502 p_name => 'p_multiple_components');
5503
5504 p_ltt_salary_data(1).comments:=
5505 hr_transaction_api.get_varchar2_value
5506 (p_transaction_step_id => ln_transaction_step_id,
5507 p_name => 'p_comments');
5508
5509 p_ltt_salary_data(1).attribute_category :=
5510 hr_transaction_api.get_varchar2_value (
5511 p_transaction_step_id => ln_transaction_step_id,
5512 p_name => 'p_attribute_category'
5513 );
5514
5515 p_ltt_salary_data(1).attribute1 :=
5516 hr_transaction_api.get_varchar2_value (
5517 p_transaction_step_id => ln_transaction_step_id,
5518 p_name => 'p_attribute1'
5519 );
5520
5521 p_ltt_salary_data(1).attribute2 :=
5522 hr_transaction_api.get_varchar2_value (
5523 p_transaction_step_id => ln_transaction_step_id,
5524 p_name => 'p_attribute2'
5525 );
5526
5527 p_ltt_salary_data(1).attribute3 :=
5528 hr_transaction_api.get_varchar2_value (
5529 p_transaction_step_id => ln_transaction_step_id,
5530 p_name => 'p_attribute3'
5531 );
5532
5533 p_ltt_salary_data(1).attribute4 :=
5534 hr_transaction_api.get_varchar2_value (
5535 p_transaction_step_id => ln_transaction_step_id,
5536 p_name => 'p_attribute4'
5537 );
5538
5539
5540 p_ltt_salary_data(1).attribute5 :=
5541 hr_transaction_api.get_varchar2_value (
5542 p_transaction_step_id => ln_transaction_step_id,
5543 p_name => 'p_attribute5'
5544 );
5545
5546 p_ltt_salary_data(1).attribute6 :=
5547 hr_transaction_api.get_varchar2_value (
5548 p_transaction_step_id => ln_transaction_step_id,
5549 p_name => 'p_attribute6'
5550 );
5551
5552
5553 p_ltt_salary_data(1).attribute7 :=
5554 hr_transaction_api.get_varchar2_value (
5555 p_transaction_step_id => ln_transaction_step_id,
5556 p_name => 'p_attribute7'
5557 );
5558
5559 p_ltt_salary_data(1).attribute8 :=
5560 hr_transaction_api.get_varchar2_value (
5561 p_transaction_step_id => ln_transaction_step_id,
5562 p_name => 'p_attribute8'
5563 );
5564
5565 p_ltt_salary_data(1).attribute9 :=
5566 hr_transaction_api.get_varchar2_value (
5567 p_transaction_step_id => ln_transaction_step_id,
5568 p_name => 'p_attribute9'
5569 );
5570
5571 p_ltt_salary_data(1).attribute10 :=
5572 hr_transaction_api.get_varchar2_value (
5573 p_transaction_step_id => ln_transaction_step_id,
5574 p_name => 'p_attribute10'
5575 );
5576
5577 p_ltt_salary_data(1).attribute11 :=
5578 hr_transaction_api.get_varchar2_value (
5579 p_transaction_step_id => ln_transaction_step_id,
5580 p_name => 'p_attribute11'
5581 );
5582
5583 p_ltt_salary_data(1).attribute12 :=
5584 hr_transaction_api.get_varchar2_value (
5585 p_transaction_step_id => ln_transaction_step_id,
5586 p_name => 'p_attribute12'
5587 );
5588
5589 p_ltt_salary_data(1).attribute13 :=
5590 hr_transaction_api.get_varchar2_value (
5591 p_transaction_step_id => ln_transaction_step_id,
5592 p_name => 'p_attribute13'
5593 );
5594
5595 p_ltt_salary_data(1).attribute14 :=
5596 hr_transaction_api.get_varchar2_value (
5597 p_transaction_step_id => ln_transaction_step_id,
5598 p_name => 'p_attribute14'
5599 );
5600
5601 p_ltt_salary_data(1).attribute15 :=
5602 hr_transaction_api.get_varchar2_value (
5603 p_transaction_step_id => ln_transaction_step_id,
5604 p_name => 'p_attribute15'
5605 );
5606
5607 p_ltt_salary_data(1).attribute16 :=
5608 hr_transaction_api.get_varchar2_value (
5609 p_transaction_step_id => ln_transaction_step_id,
5610 p_name => 'p_attribute16'
5611 );
5612
5613
5614 p_ltt_salary_data(1).attribute17 :=
5615 hr_transaction_api.get_varchar2_value (
5616 p_transaction_step_id => ln_transaction_step_id,
5617 p_name => 'p_attribute17'
5618 );
5619
5620 p_ltt_salary_data(1).attribute18 :=
5621 hr_transaction_api.get_varchar2_value (
5622 p_transaction_step_id => ln_transaction_step_id,
5623 p_name => 'p_attribute18'
5624 );
5625
5626 p_ltt_salary_data(1).attribute19 :=
5627 hr_transaction_api.get_varchar2_value (
5628 p_transaction_step_id => ln_transaction_step_id,
5629 p_name => 'p_attribute19'
5630 );
5631
5632 p_ltt_salary_data(1).attribute20 :=
5633 hr_transaction_api.get_varchar2_value (
5634 p_transaction_step_id => ln_transaction_step_id,
5635 p_name => 'p_attribute20'
5636 );
5637
5638 p_ltt_salary_data(1).no_of_components :=
5639 hr_transaction_api.get_number_value (
5640 p_transaction_step_id => ln_transaction_step_id,
5641 p_name => 'p_no_of_components'
5642 );
5643
5644 -- 04/12/02 Salary Basis Enhancement Begins
5645 p_ltt_salary_data(1).salary_basis_change_type :=
5646 hr_transaction_api.get_varchar2_value (
5647 p_transaction_step_id => ln_transaction_step_id,
5648 p_name => 'p_salary_basis_change_type'
5649 );
5650
5651 p_ltt_salary_data(1).default_date :=
5652 hr_transaction_api.get_date_value (
5653 p_transaction_step_id => ln_transaction_step_id,
5654 p_name => 'p_default_date'
5655 );
5656
5657 p_ltt_salary_data(1).default_bg_id :=
5658 hr_transaction_api.get_number_value (
5659 p_transaction_step_id => ln_transaction_step_id,
5660 p_name => 'p_default_bg_id'
5661 );
5662
5663 p_ltt_salary_data(1).default_currency :=
5664 hr_transaction_api.get_varchar2_value (
5665 p_transaction_step_id => ln_transaction_step_id,
5666 p_name => 'p_default_currency'
5667 );
5668
5669 p_ltt_salary_data(1).default_format_string :=
5670 hr_transaction_api.get_varchar2_value (
5671 p_transaction_step_id => ln_transaction_step_id,
5672 p_name => 'p_default_format_string'
5673 );
5674
5675 p_ltt_salary_data(1).default_salary_basis_name :=
5676 hr_transaction_api.get_varchar2_value (
5677 p_transaction_step_id => ln_transaction_step_id,
5678 p_name => 'p_default_salary_basis_name'
5679 );
5680
5681 p_ltt_salary_data(1).default_pay_basis_name :=
5682 hr_transaction_api.get_varchar2_value (
5683 p_transaction_step_id => ln_transaction_step_id,
5684 p_name => 'p_default_pay_basis_name'
5685 );
5686
5687 p_ltt_salary_data(1).default_pay_basis :=
5688 hr_transaction_api.get_varchar2_value (
5689 p_transaction_step_id => ln_transaction_step_id,
5690 p_name => 'p_default_pay_basis'
5691 );
5692
5693 p_ltt_salary_data(1).default_pay_annual_factor :=
5694 hr_transaction_api.get_number_value (
5695 p_transaction_step_id => ln_transaction_step_id,
5696 p_name => 'p_default_pay_annual_factor'
5697 );
5698
5699 if ((fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') is null OR
5700 fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') = 'Y') and
5701 p_ltt_salary_data(1).default_pay_basis = 'HOURLY') then
5702 l_factor := p_ltt_salary_data(1).default_pay_annual_factor*lv_old_fte_factor;
5703 else
5704 l_factor := p_ltt_salary_data(1).default_pay_annual_factor;
5705 end if;
5706
5707 if (uom='N') then
5708 p_ltt_salary_data(1).curr_ann_eqv_mc := to_char((l_factor*p_ltt_salary_data(1).current_salary),return_mask) || ' ' || lv_tmp_currency;
5709 else
5710 p_ltt_salary_data(1).curr_ann_eqv_mc := hr_util_misc_ss.get_in_preferred_currency_str((l_factor*p_ltt_salary_data(1).current_salary),lv_tmp_currency,trunc(sysdate));
5711 end if;
5712
5713 p_ltt_salary_data(1).default_grade :=
5714 hr_transaction_api.get_varchar2_value (
5715 p_transaction_step_id => ln_transaction_step_id,
5716 p_name => 'p_default_grade'
5717 );
5718
5719 p_ltt_salary_data(1).default_grade_annual_factor :=
5720 hr_transaction_api.get_number_value (
5721 p_transaction_step_id => ln_transaction_step_id,
5722 p_name => 'p_default_grade_annual_factor'
5723 );
5724
5725 p_ltt_salary_data(1).default_minimum_salary :=
5726 hr_transaction_api.get_number_value (
5727 p_transaction_step_id => ln_transaction_step_id,
5728 p_name => 'p_default_minimum_salary'
5729 );
5730
5731 p_ltt_salary_data(1).default_maximum_salary :=
5732 hr_transaction_api.get_number_value (
5733 p_transaction_step_id => ln_transaction_step_id,
5734 p_name => 'p_default_maximum_salary'
5735 );
5736
5737 p_ltt_salary_data(1).default_midpoint_salary :=
5738 hr_transaction_api.get_number_value (
5739 p_transaction_step_id => ln_transaction_step_id,
5740 p_name => 'p_default_midpoint_salary'
5741 );
5742
5743 p_ltt_salary_data(1).default_prev_salary :=
5744 hr_transaction_api.get_number_value (
5745 p_transaction_step_id => ln_transaction_step_id,
5746 p_name => 'p_default_prev_salary'
5747 );
5748
5749 p_ltt_salary_data(1).default_last_change_date :=
5750 hr_transaction_api.get_date_value (
5751 p_transaction_step_id => ln_transaction_step_id,
5752 p_name => 'p_default_last_change_date'
5753 );
5754
5755 p_ltt_salary_data(1).default_element_entry_id :=
5756 hr_transaction_api.get_number_value (
5757 p_transaction_step_id => ln_transaction_step_id,
5758 p_name => 'p_default_element_entry_id'
5759 );
5760
5761 p_ltt_salary_data(1).default_basis_changed :=
5762 hr_transaction_api.get_number_value (
5763 p_transaction_step_id => ln_transaction_step_id,
5764 p_name => 'p_default_basis_changed'
5765 );
5766
5767 p_ltt_salary_data(1).default_uom :=
5768 hr_transaction_api.get_varchar2_value (
5769 p_transaction_step_id => ln_transaction_step_id,
5770 p_name => 'p_default_uom'
5771 );
5772
5773 p_ltt_salary_data(1).default_grade_uom :=
5774 hr_transaction_api.get_varchar2_value (
5775 p_transaction_step_id => ln_transaction_step_id,
5776 p_name => 'p_default_grade_uom'
5777 );
5778
5779 p_ltt_salary_data(1).default_change_amount :=
5780 hr_transaction_api.get_number_value (
5781 p_transaction_step_id => ln_transaction_step_id,
5782 p_name => 'p_default_change_amount'
5783 );
5784
5785 p_ltt_salary_data(1).default_change_percent :=
5786 hr_transaction_api.get_number_value (
5787 p_transaction_step_id => ln_transaction_step_id,
5788 p_name => 'p_default_change_percent'
5789 );
5790
5791 p_ltt_salary_data(1).default_quartile :=
5792 hr_transaction_api.get_number_value (
5793 p_transaction_step_id => ln_transaction_step_id,
5794 p_name => 'p_default_quartile'
5795 );
5796
5797 p_ltt_salary_data(1).default_comparatio :=
5798 hr_transaction_api.get_number_value (
5799 p_transaction_step_id => ln_transaction_step_id,
5800 p_name => 'p_default_comparatio'
5801 );
5802
5803 p_ltt_salary_data(1).default_last_pay_change :=
5804 hr_transaction_api.get_varchar2_value (
5805 p_transaction_step_id => ln_transaction_step_id,
5806 p_name => 'p_default_last_pay_change'
5807 );
5808
5809 p_ltt_salary_data(1).default_flsa_status :=
5810 hr_transaction_api.get_varchar2_value (
5811 p_transaction_step_id => ln_transaction_step_id,
5812 p_name => 'p_default_flsa_status'
5813 );
5814
5815 p_ltt_salary_data(1).default_currency_symbol :=
5816 hr_transaction_api.get_varchar2_value (
5817 p_transaction_step_id => ln_transaction_step_id,
5818 p_name => 'p_default_currency_symbol'
5819 );
5820
5821 p_ltt_salary_data(1).default_precision :=
5822 hr_transaction_api.get_number_value (
5823 p_transaction_step_id => ln_transaction_step_id,
5824 p_name => 'p_default_precision'
5825 );
5826
5827 -- 04/12/02 Salary Basis Enhancement Ends'
5828
5829 -- now get the component records
5830 ln_no_of_components :=
5831 hr_transaction_api.get_number_value
5832 (p_transaction_step_id => ln_transaction_step_id,
5833 p_name => 'p_no_of_components');
5834
5835 FOR i in 1..ln_no_of_components
5836 LOOP
5837
5838 --hr_utility.trace(' Retrieving ' || i || ' compoent ');
5839 p_ltt_component(i).change_amount :=
5840 hr_transaction_api.get_number_value
5841 (p_transaction_step_id => ln_transaction_step_id,
5842 p_name => 'p_change_amount'||i);
5843
5844 if (uom='N') then
5845 p_ltt_component(i).chg_amt_mc := to_char(p_ltt_component(i).change_amount,return_mask) || ' ' || lv_currency;
5846 else
5847 p_ltt_component(i).chg_amt_mc := hr_util_misc_ss.get_in_preferred_currency_str(p_ltt_component(i).change_amount,lv_currency,trunc(sysdate));
5848 end if;
5849
5850 p_ltt_component(i).component_reason :=
5851 hr_transaction_api.get_varchar2_value
5852 (p_transaction_step_id => ln_transaction_step_id,
5853 p_name => 'p_component_reason'||i);
5854
5855 p_ltt_component(i).reason_meaning :=
5856 hr_transaction_api.get_varchar2_value
5857 (p_transaction_step_id => ln_transaction_step_id,
5858 p_name => 'p_reason_meaning'||i);
5859 /*
5860 hr_misc_web.get_lookup_meaning(
5861 p_ltt_component(i).component_reason,
5862 'PROPOSAL_REASON',
5863 p_ltt_salary_data(1).effective_date);
5864 hr_utility.trace('reason_meaning ' || p_ltt_component(i).reason_meaning);
5865 */
5866
5867 p_ltt_component(i).change_percent :=
5868 hr_transaction_api.get_number_value
5869 (p_transaction_step_id => ln_transaction_step_id,
5870 p_name => 'p_change_percent'||i);
5871
5872 p_ltt_component(i).change_annual :=
5873 hr_transaction_api.get_number_value
5874 (p_transaction_step_id => ln_transaction_step_id,
5875 p_name => 'p_change_annual'||i);
5876
5877 p_ltt_component(i).comments :=
5878 hr_transaction_api.get_varchar2_value
5879 (p_transaction_step_id => ln_transaction_step_id,
5880 p_name => 'p_comments'||i);
5881
5882 p_ltt_component(i).approved :=
5883 hr_transaction_api.get_varchar2_value
5884 (p_transaction_step_id => ln_transaction_step_id,
5885 p_name => 'p_approved'||i);
5886
5887 p_ltt_component(i).attribute_category :=
5888 hr_transaction_api.get_varchar2_value
5889 (p_transaction_step_id => ln_transaction_step_id,
5890 p_name => 'p_cattribute_category'||i);
5891
5892 p_ltt_component(i).attribute1 :=
5893 hr_transaction_api.get_varchar2_value
5894 (p_transaction_step_id => ln_transaction_step_id,
5895 p_name => 'p_cattribute1'||i);
5896
5897 p_ltt_component(i).attribute2:=
5898 hr_transaction_api.get_varchar2_value
5899 (p_transaction_step_id => ln_transaction_step_id,
5900 p_name => 'p_cattribute2'||i);
5901
5902 p_ltt_component(i).attribute3 :=
5903 hr_transaction_api.get_varchar2_value
5904 (p_transaction_step_id => ln_transaction_step_id,
5905 p_name => 'p_cattribute3'||i);
5906
5907 p_ltt_component(i).attribute4 :=
5908 hr_transaction_api.get_varchar2_value
5909 (p_transaction_step_id => ln_transaction_step_id,
5910 p_name => 'p_cattribute4'||i);
5911
5912 p_ltt_component(i).attribute5 :=
5913 hr_transaction_api.get_varchar2_value
5914 (p_transaction_step_id => ln_transaction_step_id,
5915 p_name => 'p_cattribute5'||i);
5916
5917 -- hr_utility.trace('p_cattribute5 ');
5918
5919 p_ltt_component(i).attribute6 :=
5920 hr_transaction_api.get_varchar2_value
5921 (p_transaction_step_id => ln_transaction_step_id,
5922 p_name => 'p_cattribute6'||i);
5923
5924 p_ltt_component(i).attribute7 :=
5925 hr_transaction_api.get_varchar2_value
5926 (p_transaction_step_id => ln_transaction_step_id,
5927 p_name => 'p_cattribute7'||i);
5928
5929 -- hr_utility.trace('p_cattribute7 ');
5930
5931
5932 p_ltt_component(i).attribute8 :=
5933 hr_transaction_api.get_varchar2_value
5934 (p_transaction_step_id => ln_transaction_step_id,
5935 p_name => 'p_cattribute8'||i);
5936
5937 p_ltt_component(i).attribute9 :=
5938 hr_transaction_api.get_varchar2_value
5939 (p_transaction_step_id => ln_transaction_step_id,
5940 p_name => 'p_cattribute9'||i);
5941
5942 -- hr_utility.trace('p_cattribute9 ');
5943
5944 p_ltt_component(i).attribute10 :=
5945 hr_transaction_api.get_varchar2_value
5946 (p_transaction_step_id => ln_transaction_step_id,
5947 p_name => 'p_cattribute10'||i);
5948
5949 p_ltt_component(i).attribute11 :=
5950 hr_transaction_api.get_varchar2_value
5951 (p_transaction_step_id => ln_transaction_step_id,
5952 p_name => 'p_cattribute11'||i);
5953
5954 p_ltt_component(i).attribute12 :=
5955 hr_transaction_api.get_varchar2_value
5956 (p_transaction_step_id => ln_transaction_step_id,
5957 p_name => 'p_cattribute12'||i);
5958
5959 -- hr_utility.trace('p_cattribute12 ');
5960
5961 p_ltt_component(i).attribute13 :=
5962 hr_transaction_api.get_varchar2_value
5963 (p_transaction_step_id => ln_transaction_step_id,
5964 p_name => 'p_cattribute13'||i);
5965
5966 p_ltt_component(i).attribute14 :=
5967 hr_transaction_api.get_varchar2_value
5968 (p_transaction_step_id => ln_transaction_step_id,
5969 p_name => 'p_cattribute14'||i);
5970
5971 -- hr_utility.trace('p_cattribute14 ');
5972
5973 p_ltt_component(i).attribute15 :=
5974 hr_transaction_api.get_varchar2_value
5975 (p_transaction_step_id => ln_transaction_step_id,
5976 p_name => 'p_cattribute15'||i);
5977
5978 p_ltt_component(i).attribute16 :=
5979 hr_transaction_api.get_varchar2_value
5980 (p_transaction_step_id => ln_transaction_step_id,
5981 p_name => 'p_cattribute16'||i);
5982
5983 -- hr_utility.trace('p_cattribute16 ');
5984
5985 p_ltt_component(i).attribute17 :=
5986 hr_transaction_api.get_varchar2_value
5987 (p_transaction_step_id => ln_transaction_step_id,
5988 p_name => 'p_cattribute17'||i);
5989
5990 p_ltt_component(i).attribute18 :=
5991 hr_transaction_api.get_varchar2_value
5992 (p_transaction_step_id => ln_transaction_step_id,
5993 p_name => 'p_cattribute18'||i);
5994
5995 -- hr_utility.trace('p_cattribute18 ');
5996
5997 p_ltt_component(i).attribute19 :=
5998 hr_transaction_api.get_varchar2_value
5999 (p_transaction_step_id => ln_transaction_step_id,
6000 p_name => 'p_cattribute19'||i);
6001
6002 p_ltt_component(i).attribute20 :=
6003 hr_transaction_api.get_varchar2_value
6004 (p_transaction_step_id => ln_transaction_step_id,
6005 p_name => 'p_cattribute20'||i);
6006
6007 -- hr_utility.trace('p_cattribute20 ' || p_ltt_component(i).attribute20);
6008
6009 END LOOP ;
6010
6011 END IF ;
6012
6013 hr_utility.set_location(' Leaving:' || l_proc,15);
6014 EXCEPTION
6015 WHEN OTHERS THEN
6016 hr_utility.trace('There is an exception in get_transaction_details' || SQLERRM);
6017 hr_utility.set_location(' Leaving:' || l_proc,555);
6018 raise;
6019 END get_txn_details_for_review;
6020 --End of GSP changes
6021
6022 PROCEDURE PROCESS_API (
6023 p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%type,
6024 p_effective_date in varchar2,
6025 p_validate IN boolean
6026 ) IS
6027
6028 l_proc varchar2(200) := g_package || 'PROCESS_API';
6029 ltt_salary_data sshr_sal_prop_tab_typ;
6030 ltt_component sshr_sal_comp_tab_typ;
6031 lv_item_type VARCHAR2(100);
6032 lv_item_key VARCHAR2(100);
6033 ln_act_id NUMBER ;
6034 message VARCHAR2(10000);
6035
6036
6037
6038 -- 04/19/02 Salary Basis Enhancement Change Begins
6039 lb_inv_next_sal_date_warning boolean default false;
6040 lb_proposed_salary_warning boolean default false;
6041 lb_approved_warning boolean default false;
6042 lb_payroll_warning boolean default false;
6043 lv_warning_msg_name varchar2(8000) default null;
6044 ln_payroll_id per_all_assignments_f.payroll_id%type
6045 default null;
6046 -- 04/19/02 Salary Basis Enhancement Change Ends
6047 l_effective_date VARCHAR2(100) default null;
6048 ld_effective_date date default null;
6049
6050
6051 -- 05/13/02 - Bug 2360907 Fix Begins
6052 ld_date date default null;
6053 -- 05/13/02 - Bug 2360907 Fix Ends
6054
6055 -- GSP changes
6056 lc_gsp_assignment varchar2(30) default null;
6057 -- End of GSP changes
6058
6059 --
6060 l_business_group_id number;
6061 l_currency varchar2(250);
6062 l_format_string varchar2(250);
6063 l_salary_basis_name varchar2(250);
6064 l_pay_basis_name varchar2(250);
6065 l_pay_basis varchar2(250);
6066 l_grade_basis varchar2(250);
6067 l_pay_annualization_factor number;
6068 l_fte_factor number;
6069 l_grade varchar2(250);
6070 l_grade_annualization_factor number;
6071 l_minimum_salary number;
6072 l_maximum_salary number;
6073 l_midpoint_salary number;
6074 l_prev_salary number;
6075 l_last_change_date date;
6076 l_element_entry_id number;
6077 l_basis_changed number;
6078 l_uom varchar2(250);
6079 l_grade_uom varchar2(250);
6080 l_change_amount number;
6081 l_change_percent number;
6082 l_quartile number;
6083 l_comparatio number;
6084 l_last_pay_change varchar2(250);
6085 l_flsa_status varchar2(250);
6086 l_currency_symbol varchar2(250);
6087 l_precision number;
6088 l_excep_message varchar2(250);
6089 l_pay_proposal_id number;
6090 l_current_salary number;
6091 l_proposal_ovn number;
6092 l_api_mode varchar2(250);
6093 l_warning_message varchar2(250);
6094 l_new_pay_basis_id number;
6095 l_old_pay_basis_id number;
6096 l_old_pay_annualization_factor number;
6097 l_old_fte_factor number;
6098 l_old_salary_basis_name varchar2(250);
6099 l_salary_basis_change_type varchar2(250);
6100 l_flow_mode varchar2(250);
6101 l_element_type_id_changed varchar2(250);
6102 l_old_currency_code varchar2(250);
6103 l_old_currency_symbol varchar2(250);
6104 l_old_pay_basis varchar2(250);
6105 l_old_to_new_currency_rate number;
6106 l_offered_salary number;
6107
6108 l_asg_txn_step_id varchar2(250);
6109 l_assignment_id number;
6110 l_get_defaults_date date;
6111 --
6112
6113 BEGIN
6114 hr_utility.set_location(' Entering:' || l_proc,5);
6115 if (p_effective_date is not null) then
6116 hr_utility.set_location(l_proc,10);
6117 l_effective_date:= p_effective_date;
6118 else
6119 hr_utility.set_location(l_proc,15);
6120 l_effective_date:= hr_transaction_ss.get_wf_effective_date
6121 (p_transaction_step_id => p_transaction_step_id);
6122 end if;
6123
6124 -- check for GSP assignment
6125 check_gsp_txn(p_transaction_step_id,l_effective_date,lc_gsp_assignment);
6126 if( lc_gsp_assignment = 'YES')THEN
6127 return;
6128 END IF;
6129 --end of GSP changes
6130
6131 savepoint insert_salary_details;
6132
6133 ltt_salary_data := sshr_sal_prop_tab_typ(sshr_sal_prop_obj_typ(
6134 null,-- pay_proposal_id NUMBER,
6135 null,-- assignment_id NUMBER,
6136 null,--business_group_id NUMBER,
6137 null,--effective_date DATE,
6138 null,--comments VARCHAR2(2000),
6139 null,--next_sal_review_date DATE,
6140 null,--salary_change_amount NUMBER ,
6141 null,--salary_change_percent NUMBER ,
6142 null,--annual_change NUMBER ,
6143 null,--proposed_salary NUMBER ,
6144 null,--proposed_percent NUMBER ,
6145 null,--proposal_reason VARCHAR2(30),
6146 null,--ranking NUMBER,
6147 null,--current_salary NUMBER,
6148 null,--performance_review_id NUMBER,
6149 null,--multiple_components VARCHAR2(1),
6150 null,--element_entry_id NUMBER ,
6151 null,--selection_mode VARCHAR2(1),
6152 null,--ovn NUMBER,
6153 null,--currency VARCHAR2(15),
6154 null,--pay_basis_name VARCHAR2(80),
6155 null,--annual_equivalent NUMBER ,
6156 null,--total_percent NUMBER ,
6157 null,--quartile NUMBER ,
6158 null,--comparatio NUMBER ,
6159 null,--lv_selection_mode VARCHAR2(1),
6160 null,--attribute_category VARCHAR2(150),
6161 null,--attribute1 VARCHAR2(150),
6162 null,--attribute2 VARCHAR2(150),
6163 null,--attribute3 VARCHAR2(150),
6164 null,--attribute4 VARCHAR2(150),
6165 null,--attribute5 VARCHAR2(150),
6166 null,--attribute6 VARCHAR2(150),
6167 null,--attribute7 VARCHAR2(150),
6168 null,--attribute8 VARCHAR2(150),
6169 null,--attribute9 VARCHAR2(150),
6170 null,--attribute10 VARCHAR2(150),
6171 null,--attribute11 VARCHAR2(150),
6172 null,--attribute12 VARCHAR2(150),
6173 null,--attribute13 VARCHAR2(150),
6174 null,--attribute14 VARCHAR2(150),
6175 null,--attribute15 VARCHAR2(150),
6176 null,--attribute16 VARCHAR2(150),
6177 null,--attribute17 VARCHAR2(150),
6178 null,--attribute18 VARCHAR2(150),
6179 null,--attribute19 VARCHAR2(150),
6180 null,--attribute20 VARCHAR2(150),
6181 null, --no_of_components NUMBER,
6182 -- 04/12/02 Salary Basis Enhancement Begins
6183 null, -- salary_basis_change_type varchar2(30)
6184 null, -- default_date date
6185 null, -- default_bg_id number
6186 null, -- default_currency VARCHAR2(15)
6187 null, -- default_format_string VARCHAR2(40)
6188 null, -- default_salary_basis_name varchar2(30)
6189 null, -- default_pay_basis_name varchar2(80)
6190 null, -- default_pay_basis varchar2(30)
6191 null, -- default_pay_annual_factor number
6192 null, -- default_grade VARCHAR2(240)
6193 null, -- default_grade_annual_factor number
6194 null, -- default_minimum_salary number
6195 null, -- default_maximum_salary number
6196 null, -- default_midpoint_salary number
6197 null, -- default_prev_salary number
6198 null, -- default_last_change_date date
6199 null, -- default_element_entry_id number
6200 null, -- default_basis_changed number
6201 null, -- default_uom VARCHAR2(30)
6202 null, -- default_grade_uom VARCHAR2(30)
6203 null, -- default_change_amount number
6204 null, -- default_change_percent number
6205 null, -- default_quartile number
6206 null, -- default_comparatio number
6207 null, -- default_last_pay_change varchar2(200)
6208 null, -- default_flsa_status varchar2(80)
6209 null, -- default_currency_symbol varchar2(4)
6210 null, -- default_precision number
6211 -- 04/12/02 Salary Basis Enhancement Ends
6212 -- GSP
6213 null, -- salary_effective_date date
6214 null, -- gsp_dummy_txn flag
6215 --End of GSP
6216 null,
6217 null,
6218 null,
6219 null,
6220 null
6221 ));
6222
6223
6224
6225
6226 ltt_component := sshr_sal_comp_tab_typ(
6227 sshr_sal_comp_obj_typ(
6228 null, null, null, null, null, null, null, null, null, null, null, null,
6229 null, null, null, null, null, null, null, null, null, null, null, null,
6230 null, null, null, null, null, null, null, null, null ),
6231 sshr_sal_comp_obj_typ(
6232 null, null, null, null, null, null, null, null, null, null, null, null,
6233 null, null, null, null, null, null, null, null, null, null, null, null,
6234 null, null, null, null, null, null, null, null, null ),
6235 sshr_sal_comp_obj_typ(
6236 null, null, null, null, null, null, null, null, null, null, null, null,
6237 null, null, null, null, null, null, null, null, null, null, null, null,
6238 null, null, null, null, null, null, null, null, null ),
6239 sshr_sal_comp_obj_typ(
6240 null, null, null, null, null, null, null, null, null, null, null, null,
6241 null, null, null, null, null, null, null, null, null, null, null, null,
6242 null, null, null, null, null, null, null, null, null ),
6243 sshr_sal_comp_obj_typ(
6244 null, null, null, null, null, null, null, null, null, null, null, null,
6245 null, null, null, null, null, null, null, null, null, null, null, null,
6246 null, null, null, null, null, null, null, null, null ),
6247 sshr_sal_comp_obj_typ(
6248 null, null, null, null, null, null, null, null, null, null, null, null,
6249 null, null, null, null, null, null, null, null, null, null, null, null,
6250 null, null, null, null, null, null, null, null, null ),
6251 sshr_sal_comp_obj_typ(
6252 null, null, null, null, null, null, null, null, null, null, null, null,
6253 null, null, null, null, null, null, null, null, null, null, null, null,
6254 null, null, null, null, null, null, null, null, null ),
6255 sshr_sal_comp_obj_typ(
6256 null, null, null, null, null, null, null, null, null, null, null, null,
6257 null, null, null, null, null, null, null, null, null, null, null, null,
6258 null, null, null, null, null, null, null, null, null ),
6259 sshr_sal_comp_obj_typ(
6260 null, null, null, null, null, null, null, null, null, null, null, null,
6261 null, null, null, null, null, null, null, null, null, null, null, null,
6262 null, null, null, null, null, null, null, null, null ),
6263 sshr_sal_comp_obj_typ(
6264 null, null, null, null, null, null, null, null, null, null, null, null,
6265 null, null, null, null, null, null, null, null, null, null, null, null,
6266 null, null, null, null, null, null, null, null, null )
6267 );
6268
6269
6270 hr_transaction_api.get_transaction_step_info(
6271 p_transaction_step_id => p_transaction_step_id
6272 ,p_item_type => lv_item_type
6273 ,p_item_key => lv_item_key
6274 ,p_activity_id => ln_act_id);
6275
6276 -- get salary data from txn table
6277 get_transaction_details ( lv_item_type ,
6278 lv_item_key,
6279 ln_act_id ,
6280 ltt_salary_data ,
6281 ltt_component) ;
6282
6283
6284 -- 04/12/02 Salary Basis Enhancement Begins
6285 -- start registration
6286 -- If it's a new user registration flow than the assignmentId which is
6287 -- coming from transaction table will not be valid because the person
6288 -- has just been created by the process_api of the
6289 -- hr_process_person_ss.process_api we can get that person Id and
6290 -- assignment id by making a call to the global parameters but we need
6291 -- to branch out the code.
6292
6293 -- Adding the session id check to avoid connection pooling problems.
6294 IF (( hr_process_person_ss.g_assignment_id is not null) and
6295 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID))
6296 THEN
6297 hr_utility.set_location(l_proc,20);
6298 -- Set the Assignment Id to the one just created, don't use the
6299 -- transaction table.
6300 ltt_salary_data(1).assignment_id := hr_process_person_ss.g_assignment_id;
6301 END IF;
6302
6303 l_asg_txn_step_id := p_transaction_step_id;
6304 l_assignment_id := ltt_salary_data(1).assignment_id;
6305 l_get_defaults_date := ltt_salary_data(1).default_date;
6306 ld_effective_date := to_date(l_effective_date,'RRRR-MM-DD');
6307
6308 begin
6309 select VARCHAR2_VALUE into l_flow_mode from hr_api_transaction_values where TRANSACTION_STEP_ID = p_transaction_step_id and NAME = 'P_FLOW_MODE';
6310 exception
6311 when no_data_found then
6312 l_flow_mode := null;
6313 end;
6314 hr_pay_rate_ss.check_asg_txn_data
6315 (lv_item_type
6316 ,lv_item_key
6317 ,ln_act_id
6318 ,ld_effective_date
6319 ,l_assignment_id
6320 ,l_asg_txn_step_id
6321 ,l_get_defaults_date
6322 ,l_business_group_id
6323 ,l_currency
6324 ,l_format_string
6325 ,l_salary_basis_name
6326 ,l_pay_basis_name
6327 ,l_pay_basis
6328 ,l_grade_basis
6329 ,l_pay_annualization_factor
6330 ,l_fte_factor
6331 ,l_grade
6332 ,l_grade_annualization_factor
6333 ,l_minimum_salary
6334 ,l_maximum_salary
6335 ,l_midpoint_salary
6336 ,l_prev_salary
6337 ,l_last_change_date
6338 ,l_element_entry_id
6339 ,l_basis_changed
6340 ,l_uom
6341 ,l_grade_uom
6342 ,l_change_amount
6343 ,l_change_percent
6344 ,l_quartile
6345 ,l_comparatio
6346 ,l_last_pay_change
6347 ,l_flsa_status
6348 ,l_currency_symbol
6349 ,l_precision
6350 ,l_excep_message
6351 ,l_pay_proposal_id
6352 ,l_current_salary
6353 ,l_proposal_ovn
6354 ,l_api_mode
6355 ,l_warning_message
6356 ,l_new_pay_basis_id
6357 ,l_old_pay_basis_id
6358 ,l_old_pay_annualization_factor
6359 ,l_old_fte_factor
6360 ,l_old_salary_basis_name
6361 ,l_salary_basis_change_type
6362 ,l_flow_mode
6363 ,l_element_type_id_changed
6364 ,l_old_currency_code
6365 ,l_old_currency_symbol
6366 ,l_old_pay_basis
6367 ,l_old_to_new_currency_rate
6368 ,l_offered_salary
6369 ,'N'
6370 );
6371 -- 04/12/02 Salary Basis Enhancement Ends
6372
6373 -- 04/25/02 Salary Basis Enhancement Change Begins
6374 -- For all scenarios, especially the two listed below:
6375 -- i) A change in salary basis whereby the element type id is different
6376 -- between the old and new salary basis
6377 -- ii)A New Hire flow;
6378 -- We need to get the element_entry_id
6379 -- from the database instead of using the one in transaction table
6380 -- because the element entry id will be different when it comes to
6381 -- commit the New Hire flow. Also, in a re-entry of a Save For Later
6382 -- for a new hire, you will get the following error if we don't get
6383 -- the element entry id from the database:
6384 -- Procedure hr_entry_api.upd_ele_entry_param_val at Step 1.
6385 -- Cause: The procedure hr_entry_api.upd_ele_entry_param_val has created
6386 -- an error at Step 1.
6387
6388 -- 05/13/02 - Bug 2360907 Fix Begins
6389 -- If the input parm p_effective_date is not null,that means it's a re-entry
6390 -- of a Save For Later transaction and the user has changed the date. We
6391 -- should use the l_effective_date which is set to either the input parm
6392 -- p_effective_date value or the WF effective date.
6393 ld_date := to_date(l_effective_date,
6394 hr_process_assignment_ss.g_date_format);
6395
6396 PER_PAY_PROPOSALS_POPULATE.get_element_id
6397 (p_assignment_id => ltt_salary_data(1).assignment_id
6398 ,p_business_group_id => ltt_salary_data(1).business_group_id
6399 ,p_change_date => ld_date -- Bug 2360907 Fix
6400 ,p_payroll_value => ln_payroll_id
6401 ,p_element_entry_id => ltt_salary_data(1).element_entry_id);
6402
6403 -- 05/13/02 - Bug 2360907 Fix Ends
6404 -- 04/25/02 Salary Basis Enhancement Change Ends
6405
6406
6407 -- hr_utility.trace('********* After get_transaction_details *********' );
6408 /*
6409
6410
6411 message := ' PROPOSAL VALUES' ||
6412 ' pay_proposal_id' || ltt_salary_data(1).pay_proposal_id ||
6413 ' assignment_id' || ltt_salary_data(1).assignment_id ||
6414 ' business_group_id' || ltt_salary_data(1).business_group_id ||
6415 ' effective_date' || ltt_salary_data(1).effective_date ||
6416 ' comments' || ltt_salary_data(1).comments ||
6417 ' next_sal_review_date' || ltt_salary_data(1).next_sal_review_date ||
6418 ' salary_change_amount' || ltt_salary_data(1).salary_change_amount ||
6419 ' salary_change_percent' || ltt_salary_data(1).salary_change_percent ||
6420 ' annual_change' || ltt_salary_data(1).annual_change ||
6421 ' proposed_salary' || ltt_salary_data(1).proposed_salary ||
6422 ' proposed_percent' || ltt_salary_data(1).proposed_percent ||
6423 ' proposal_reason' || ltt_salary_data(1).proposal_reason ||
6424 ' ranking' || ltt_salary_data(1).ranking ||
6425 ' current_salary' || ltt_salary_data(1).current_salary ||
6426 ' performance_review_id' || ltt_salary_data(1).performance_review_id ||
6427 ' multiple_components' || ltt_salary_data(1).multiple_components ||
6428 ' element_entry_id' || ltt_salary_data(1).element_entry_id ||
6429 ' selection_mode' || ltt_salary_data(1).selection_mode ||
6430 ' ovn' || ltt_salary_data(1).ovn ||
6431 ' currency' || ltt_salary_data(1).currency ||
6432 ' pay_basis_name' || ltt_salary_data(1).pay_basis_name ||
6433 ' annual_equivalent' || ltt_salary_data(1).annual_equivalent ||
6434 ' total_percent' || ltt_salary_data(1).total_percent ||
6435 ' quartile' || ltt_salary_data(1).quartile ||
6436 ' comparatio' || ltt_salary_data(1).comparatio ||
6437 ' lv_selection_mode' || ltt_salary_data(1).lv_selection_mode ||
6438 ' attribute_category' || ltt_salary_data(1).attribute_category ||
6439 ' attribute1' || ltt_salary_data(1).attribute1 ||
6440 ' attribute2' || ltt_salary_data(1).attribute2 ||
6441 ' attribute3' || ltt_salary_data(1).attribute3 ||
6442 ' attribute4' || ltt_salary_data(1).attribute4 ||
6443 ' attribute5' || ltt_salary_data(1).attribute5 ||
6444 ' attribute6' || ltt_salary_data(1).attribute6 ||
6445 ' attribute7' || ltt_salary_data(1).attribute7 ||
6446 ' attribute8' || ltt_salary_data(1).attribute8 ||
6447 ' attribute9' || ltt_salary_data(1).attribute9 ||
6448 ' attribute10' || ltt_salary_data(1).attribute10 ||
6449 ' attribute11' || ltt_salary_data(1).attribute11 ||
6450 ' attribute12' || ltt_salary_data(1).attribute12 ||
6451 ' attribute13' || ltt_salary_data(1).attribute13 ||
6452 ' attribute14' || ltt_salary_data(1).attribute14 ||
6453 ' attribute15' || ltt_salary_data(1).attribute15 ||
6454 ' attribute16' || ltt_salary_data(1).attribute16 ||
6455 ' attribute17' || ltt_salary_data(1).attribute17 ||
6456 ' attribute18' || ltt_salary_data(1).attribute18 ||
6457 ' attribute19' || ltt_salary_data(1).attribute19 ||
6458 ' attribute20' || ltt_salary_data(1).attribute20 ;
6459
6460 hr_utility.trace(message);
6461 */
6462
6463
6464 -- 04/19/02 Salary Basis Enhancement Change Begins
6465 validate_salary_ins_api_java (
6466 p_item_type => lv_item_type
6467 ,p_item_key => lv_item_key
6468 ,p_Act_id => ln_act_id
6469 ,p_ltt_salary_data => ltt_salary_data
6470 ,p_ltt_component => ltt_component
6471 ,p_validate => p_validate
6472 ,p_effective_date => l_effective_date
6473 ,p_inv_next_sal_date_warning => lb_inv_next_sal_date_warning
6474 ,p_proposed_salary_warning => lb_proposed_salary_warning
6475 ,p_approved_warning => lb_approved_warning
6476 ,p_payroll_warning => lb_payroll_warning
6477 );
6478
6479 -- hr_utility.trace('After validate_salary_ins_api_java' );
6480
6481 -- The following warning message names were obtained from PERWSEPY.fmb.
6482 IF lb_inv_next_sal_date_warning
6483 THEN
6484 hr_utility.set_location(l_proc,25);
6485 -- Need to construct the output p_warning_msg_name parm with the
6486 -- following format:
6487 -- PER,HR_7340_SAL_ASS_TERMINATED|PAY,HR_APP_PROPOS_APP_COMP| .....
6488 lv_warning_msg_name := 'PER,HR_7340_SAL_ASS_TERMINATED|';
6489 END IF;
6490
6491 IF lb_proposed_salary_warning
6492 THEN
6493 hr_utility.set_location(l_proc,30);
6494 lv_warning_msg_name := lv_warning_msg_name ||
6495 'PAY,HR_SAL_SAL_ELE_RANGE_WARN|';
6496 END IF;
6497
6498 IF lb_approved_warning
6499 THEN
6500 hr_utility.set_location(l_proc,35);
6501 lv_warning_msg_name := lv_warning_msg_name ||
6502 'PAY,HR_APP_PROPOS_APP_COMP|';
6503 END IF;
6504
6505 IF lb_payroll_warning
6506 THEN
6507 hr_utility.set_location(l_proc,40);
6508 lv_warning_msg_name := lv_warning_msg_name ||
6509 'PER,HR_SAL_PAYROLL_PERIOD_CLOSED|';
6510 END IF;
6511
6512 -- The warnings are for debugging only. In the actual commit phase, we
6513 -- don't issue any warnings.
6514
6515 hr_utility.set_location(' Leaving:' || l_proc,45);
6516 EXCEPTION
6517 WHEN hr_utility.hr_error THEN
6518 rollback to insert_salary_details;
6519
6520 hr_utility.set_location(' Leaving:' || l_proc,555);
6521 -- ---------------------------------------------------
6522 -- an application error has been raised
6523 -- ----------------------------------------------------
6524 RAISE;
6525
6526 WHEN OTHERS THEN
6527 ROLLBACK to insert_salary_details;
6528 hr_utility.set_location(' Leaving:' || l_proc,560);
6529 RAISE;
6530
6531 -- 04/19/02 Salary Basis Enhancement Change Ends
6532
6533 END PROCESS_API;
6534
6535
6536 procedure prate_applicant_hire
6537 (p_person_id in number,
6538 p_bg_id in number,
6539 p_org_id in number,
6540 p_effective_date in date default sysdate,
6541 p_salaray_basis_id out nocopy varchar,
6542 p_offered_salary out nocopy varchar,
6543 p_offered_salary_basis out nocopy varchar
6544 ) is
6545
6546 cursor csr_applicant_offer is
6547 select pay.pay_proposal_id,
6548 asf.assignment_id,
6549 asf.pay_basis_id,
6550 pay.proposed_salary_n,
6551 pb.name
6552 from
6553 per_all_assignments_f asf,
6554 per_pay_proposals pay,
6555 per_pay_bases pb,
6556 pay_input_values_f pv
6557 where asf.person_id = p_person_id
6558 and asf.assignment_type = 'O'
6559 and asf.business_group_id = p_bg_id
6560 and asf.organization_id = p_org_id
6561 and p_effective_date between asf.effective_start_date and asf.effective_end_date
6562 and pay.assignment_id =asf.assignment_id
6563 and pb.business_group_id = p_bg_id
6564 and pb.pay_basis_id = nvl(asf.pay_basis_id,-1)
6565 and pb.input_value_id = pv.input_value_id
6566 and p_effective_date between pv.effective_start_date and pv.effective_end_date;
6567
6568
6569 app_offer_row csr_applicant_offer%ROWTYPE;
6570
6571
6572 begin
6573 -- Default return values
6574 p_salaray_basis_id :='-1';
6575 p_offered_salary :='-1';
6576 p_offered_salary_basis :='-1';
6577
6578
6579
6580 open csr_applicant_offer;
6581 fetch csr_applicant_offer into app_offer_row;
6582 close csr_applicant_offer;
6583
6584 if app_offer_row.pay_proposal_id is not null then
6585
6586 p_salaray_basis_id := to_char(app_offer_row.pay_basis_id);
6587
6588 p_offered_salary := to_char(app_offer_row.proposed_salary_n);
6589
6590 p_offered_salary_basis := app_offer_row.name;
6591
6592 end if;
6593
6594 exception
6595 when others then
6596 close csr_applicant_offer;
6597
6598 end prate_applicant_hire;
6599
6600
6601
6602 procedure delete_transaction_step
6603 (p_transaction_id in number,
6604 p_login_person_id in number ) is
6605
6606 l_proc varchar2(200) := g_package || 'delete_transaction_step';
6607 l_transaction_step_id number;
6608 cursor c1(p_transaction_id number) is
6609 select transaction_step_id
6610 from hr_api_transaction_steps
6611 where transaction_id = p_transaction_id
6612 and api_name = 'HR_PAY_RATE_SS.PROCESS_API';
6613 BEGIN
6614 hr_utility.set_location(' Entering:' || l_proc,5);
6615 open c1(p_transaction_id);
6616 fetch c1 into l_transaction_step_id;
6617 close c1;
6618 HR_TRANSACTION_SS.delete_transaction_step(
6619 p_transaction_step_id =>l_transaction_step_id,
6620 p_login_person_id => p_login_person_id);
6621 hr_utility.set_location(' Leaving:' || l_proc,10);
6622 END;
6623
6624
6625 END hr_pay_rate_ss;