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