[Home] [Help]
PACKAGE BODY: APPS.HR_PAY_RATE_GSP_SS
Source
1 PACKAGE BODY HR_PAY_RATE_GSP_SS as
2 /* $Header: hrpaygsp.pkb 120.5 2008/06/11 14:05:40 gpurohit ship $ */
3
4 -- get the updated salary because of Grade Ladder assignment
5 procedure get_employee_salary
6 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
7 P_Effective_Date In Date,
8 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ
9 ) IS
10
11 -- get_employee_salary local variables
12 ln_proposed_salary NUMBER;
13 lv_frequency VARCHAR2(100);
14 ln_annual_salary NUMBER;
15 lv_pay_basis_name VARCHAR2(100);
16 lv_reason_cd VARCHAR2(100);
17 ln_currency VARCHAR2(100);
18 ln_status NUMBER;
19 l_sal_effective_date date;
20
21 begin
22 l_sal_effective_date := P_Effective_Date;
23
24 -- get the salary from procedure provided by core API.
25 pqh_employee_salary.get_emp_proposed_salary(
26 P_Assignment_id => p_assignment_id,
27 P_Effective_Date => p_effective_date,
28 p_proposed_salary => ln_proposed_salary,
29 p_sal_chg_dt => l_sal_effective_date,
30 --p_salary => ln_proposed_salary,
31 p_frequency => lv_frequency,
32 p_annual_salary => ln_annual_salary,
33 p_pay_basis => lv_pay_basis_name,
34 p_reason_cd => lv_reason_cd,
35 p_currency => ln_currency,
36 p_status => ln_status);
37
38 p_ltt_salary_data(1).proposed_salary := ln_proposed_salary;
39 p_ltt_salary_data(1).proposal_reason := 'GSP';
40 p_ltt_salary_data(1).currency := ln_currency;
41 -- need to check pay basis name
42 p_ltt_salary_data(1).pay_basis_name := lv_pay_basis_name;
43 -- calculate the annual salary change to display
44 -- in the review page
45 if(p_ltt_salary_data(1).annual_equivalent is null) then
46 p_ltt_salary_data(1).annual_change := ln_annual_salary;
47 else
48 p_ltt_salary_data(1).annual_change := ln_annual_salary -
49 p_ltt_salary_data(1).annual_equivalent;
50 end if;
51
52 p_ltt_salary_data(1).annual_equivalent := ln_annual_salary;
53 p_ltt_salary_data(1).proposed_salary := ln_proposed_salary;
54 if(p_ltt_salary_data(1).current_salary is null OR p_ltt_salary_data(1).current_salary = 0) then
55 p_ltt_salary_data(1).salary_change_amount := ln_proposed_salary;
56 p_ltt_salary_data(1).salary_change_percent := null;
57 else
58 p_ltt_salary_data(1).salary_change_amount :=
59 ln_proposed_salary - p_ltt_salary_data(1).current_salary;
60 p_ltt_salary_data(1).salary_change_percent :=
61 (p_ltt_salary_data(1).salary_change_amount)*100/
62 (p_ltt_salary_data(1).current_salary);
63 end if;
64
65 p_ltt_salary_data(1).salary_effective_date := l_sal_effective_date;
66
67 EXCEPTION
68 WHEN hr_utility.hr_error THEN
69 hr_utility.trace('there is a hr_utility.hr_error in get_employee_salary');
70 hr_utility.trace('p_error_msg_text');
71 WHEN OTHERS THEN
72 hr_utility.trace(
73 'there is an OTHERS Exception in process_salary_java_gsp');
74 hr_utility.trace('p_error_msg_text' || SQLERRM);
75 end get_employee_salary;
76
77 -- get the current salary , called before updating the assignment
78 procedure get_employee_current_salary
79 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
80 P_Effective_Date In Date,
81 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ
82 ) IS
83
84 -- get_employee_current_salary local variables
85 ln_proposed_salary NUMBER;
86 lv_frequency VARCHAR2(100);
87 ln_annual_salary NUMBER;
88 lv_pay_basis_name VARCHAR2(100);
89 lv_reason_cd VARCHAR2(100);
90 ln_currency VARCHAR2(100);
91 ln_status NUMBER;
92 lv_pay_basis_frequency per_pay_bases.pay_basis%TYPE;
93 begin
94 -- populate default values
95 hr_pay_rate_ss.my_get_defaults(
96 p_assignment_id => P_Assignment_id
97 ,p_date => p_ltt_salary_data(1).effective_date
98 ,p_business_group_id => p_ltt_salary_data(1).default_bg_id
99 ,p_currency => p_ltt_salary_data(1).default_currency
100 ,p_format_string =>
101 p_ltt_salary_data(1).default_format_string
102 ,p_salary_basis_name =>
103 p_ltt_salary_data(1).default_salary_basis_name
104 ,p_pay_basis_name =>
105 p_ltt_salary_data(1).default_pay_basis_name
106 ,p_pay_basis => p_ltt_salary_data(1).default_pay_basis
107 ,p_pay_annualization_factor =>
108 p_ltt_salary_data(1).default_pay_annual_factor
109 ,p_grade => p_ltt_salary_data(1).default_grade
110 ,p_grade_annualization_factor =>
111 p_ltt_salary_data(1).default_grade_annual_factor
112 ,p_minimum_salary =>
113 p_ltt_salary_data(1).default_minimum_salary
114 ,p_maximum_salary =>
115 p_ltt_salary_data(1).default_maximum_salary
116 ,p_midpoint_salary =>
117 p_ltt_salary_data(1).default_midpoint_salary
118 ,p_prev_salary =>
119 p_ltt_salary_data(1).default_prev_salary
120 ,p_last_change_date =>
121 p_ltt_salary_data(1).default_last_change_date
122 ,p_element_entry_id =>
123 p_ltt_salary_data(1).default_element_entry_id
124 ,p_basis_changed =>
125 p_ltt_salary_data(1).default_basis_changed
126 ,p_uom => p_ltt_salary_data(1).default_uom
127 ,p_grade_uom => p_ltt_salary_data(1).default_grade_uom
128 ,p_change_amount =>
129 p_ltt_salary_data(1).default_change_amount
130 ,p_change_percent =>
131 p_ltt_salary_data(1).default_change_percent
132 ,p_quartile => p_ltt_salary_data(1).default_quartile
133 ,p_comparatio => p_ltt_salary_data(1).default_comparatio
134 ,p_last_pay_change =>
135 p_ltt_salary_data(1).default_last_pay_change
136 ,p_flsa_status =>
137 p_ltt_salary_data(1).default_flsa_status
138 ,p_currency_symbol =>
139 p_ltt_salary_data(1).default_currency_symbol
140 ,p_precision => p_ltt_salary_data(1).default_precision
141 ,p_job_id => null
142 );
143 -- End of my defaults
144
145 -- get the salary from procedure provided by core API.
146 pqh_employee_salary.get_employee_salary(
147 P_Assignment_id => p_assignment_id,
148 P_Effective_Date => p_effective_date,
149 p_salary => ln_proposed_salary,
150 p_frequency => lv_frequency,
151 p_annual_salary => ln_annual_salary,
152 p_pay_basis => lv_pay_basis_name,
153 p_reason_cd => lv_reason_cd,
154 p_currency => ln_currency,
155 p_status => ln_status,
156 p_pay_basis_frequency => lv_pay_basis_frequency);
157
158
159 p_ltt_salary_data(1).proposed_salary := ln_proposed_salary;
160 p_ltt_salary_data(1).proposal_reason := 'GSP';
161 p_ltt_salary_data(1).currency := ln_currency;
162 p_ltt_salary_data(1).pay_basis_name := lv_pay_basis_name;
163 p_ltt_salary_data(1).annual_equivalent := ln_annual_salary;
164 p_ltt_salary_data(1).current_salary := ln_proposed_salary;
165
166 EXCEPTION
167 WHEN hr_utility.hr_error THEN
168 hr_utility.trace('there is a hr_utility.hr_error in get_employee_salary');
169 hr_utility.trace('p_error_msg_text');
170 WHEN OTHERS THEN
171 hr_utility.trace(
172 'there is an OTHERS Exception in process_salary_java_gsp');
173 hr_utility.trace('p_error_msg_text' || SQLERRM);
174 end get_employee_current_salary;
175
176 -- save the GSP Txn , which will be called hr_process_assignment_api
177 procedure save_gsp_txn
178 (
179 p_item_type IN wf_items.item_type%type,
180 p_item_key IN wf_items.item_key%TYPE,
181 p_Act_id IN NUMBER,
182 p_ltt_salary_data IN sshr_sal_prop_tab_typ,
183 p_review_proc_call IN VARCHAR2,
184 p_flow_mode IN OUT nocopy varchar2, -- 2355929
185 p_step_id OUT NOCOPY NUMBER,
186 p_rptg_grp_id IN VARCHAR2 DEFAULT NULL,
187 p_plan_id IN VARCHAR2 DEFAULT NULL,
188 p_effective_date_option IN VARCHAR2 DEFAULT NULL
189 ) IS
190
191 -- save_gsp_txn local variables
192 ln_transaction_id NUMBER ;
193 lv_result VARCHAR2(100);
194 li_count INTEGER ;
195 lv_api_name hr_api_transaction_steps.api_name%type ;
196 ln_ovn hr_api_transaction_steps.object_version_number%TYPE;
197 ln_transaction_step_id hr_api_transaction_steps.transaction_step_id%TYPE default null;
198 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
199 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
200 ln_trans_step_rows number default 0;
201 lv_activity_name wf_item_activity_statuses_v.activity_name%TYPE;
202 ln_no_of_components NUMBER ;
203 lv_review_url VARCHAR2(1000) ;
204 lv_activity_display_name VARCHAR2(100);
205 message VARCHAR2(10000) := '';
206 ln_creator_person_id NUMBER;
207 result VARCHAR2(100);
208
209 cursor get_transaction_step_id(
210 c_item_type in wf_items.item_type%type
211 ,c_item_key in wf_items.item_key%type
212 ) IS
213 SELECT transaction_step_id
214 FROM hr_api_transaction_steps
215 WHERE item_type = c_item_type
216 AND item_key = c_item_key
217 --AND api_name = 'HR_PAY_RATE_SS.process_api_java';
218 AND api_name = 'HR_PAY_RATE_SS.PROCESS_API';
219
220 BEGIN
221 -- bug # 1641590
222
223 hr_utility.trace('Start save_gsp_txn');
224
225 lv_review_url := gv_package_name||'.salary_review';
226 lv_api_name := gv_package_name||'.PROCESS_API' ;
227
228 ln_creator_person_id := wf_engine.GetItemAttrNumber(p_item_type,
229 p_item_key,
230 'CREATOR_PERSON_ID');
231
232 hr_utility.trace('Creator Person Id ' || ln_creator_person_id);
233
234 -- prepare salary proposal data to be stored in transaction table
235 li_count := 1 ;
236
237 gtt_trans_steps(li_count).param_name := 'P_REVIEW_PROC_CALL' ;
238 gtt_trans_steps(li_count).param_value := p_review_proc_call;
239 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
240
241 li_count := li_count+1 ;
242
243 gtt_trans_steps(li_count).param_name := 'P_REVIEW_ACTID' ;
244 gtt_trans_steps(li_count).param_value := p_Act_id;
245 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
246
247 li_count := li_count+1 ;
248
249 -- 04/24/02 Change Begins
250 gtt_trans_steps(li_count).param_name := 'P_FLOW_MODE' ;
251 gtt_trans_steps(li_count).param_value := p_flow_mode;
252 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
253
254 li_count := li_count+1 ;
255 -- 04/24/02 Change Ends
256
257 gtt_trans_steps(li_count).param_name := 'p_current_salary' ;
258 gtt_trans_steps(li_count).param_value :=
259 p_ltt_salary_data(1).current_salary ;
260 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
261 li_count := li_count+1 ;
262
263
264 -- This variable indicates that, this is GSP PayRate Txn
265 gtt_trans_steps(li_count).param_name := 'p_gsp_dummy_txn' ;
266 gtt_trans_steps(li_count).param_value := 'YES';
267 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
268 li_count := li_count+1 ;
269
270
271 gtt_trans_steps(li_count).param_name := 'p_assignment_id' ;
272 gtt_trans_steps(li_count).param_value :=
273 p_ltt_salary_data(1).assignment_id ;
274 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
275 li_count := li_count+1 ;
276
277 gtt_trans_steps(li_count).param_name := 'p_bus_group_id' ;
278 gtt_trans_steps(li_count).param_value :=
279 p_ltt_salary_data(1).business_group_id ;
280 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
281 li_count := li_count+1 ;
282
283
284 gtt_trans_steps(li_count).param_name := 'p_effective_date' ;
285 gtt_trans_steps(li_count).param_value :=
286 to_char(p_ltt_salary_data(1).effective_date,hr_transaction_ss.g_date_format);
287 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
288 li_count := li_count+1 ;
289
290 gtt_trans_steps(li_count).param_name := 'p_salary_effective_date' ;
291 gtt_trans_steps(li_count).param_value :=
292 to_char(p_ltt_salary_data(1).salary_effective_date,hr_transaction_ss.g_date_format);
293 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
294 li_count := li_count+1 ;
295
296 gtt_trans_steps(li_count).param_name := 'p_change_amount' ;
297 gtt_trans_steps(li_count).param_value :=
298 p_ltt_salary_data(1).salary_change_amount ;
299 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
300
301 li_count:= li_count + 1 ;
302 gtt_trans_steps(li_count).param_name := 'p_proposed_salary' ;
303 gtt_trans_steps(li_count).param_value :=
304 p_ltt_salary_data(1).proposed_salary;
305 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
306
307 li_count:= li_count + 1 ;
308 gtt_trans_steps(li_count).param_name := 'p_proposal_reason' ;
309 gtt_trans_steps(li_count).param_value :=
310 p_ltt_salary_data(1).proposal_reason;
311 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
312
313 li_count := li_count + 1;
314 gtt_trans_steps(li_count).param_name := 'p_no_of_components';
315 -- for gsp this is Zero
316 -- for avoiding Char to Numeric conversion Error
317 -- in the get_transaction_details procedure
318 gtt_trans_steps(li_count).param_value := 0;
319 gtt_trans_steps(li_count).param_data_type := 'NUMBER';
320
321 li_count:= li_count + 1 ;
322 gtt_trans_steps(li_count).param_name := 'p_multiple_components' ;
323 gtt_trans_steps(li_count).param_value := 'N';
324 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
325
326
327 li_count:= li_count + 1 ;
328 gtt_trans_steps(li_count).param_name := 'p_currency' ;
329 gtt_trans_steps(li_count).param_value :=
330 p_ltt_salary_data(1).currency;
331 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
332
333 li_count:= li_count + 1 ;
334 gtt_trans_steps(li_count).param_name := 'p_pay_basis_name' ;
335 gtt_trans_steps(li_count).param_value :=
336 p_ltt_salary_data(1).pay_basis_name;
337 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
338
339 li_count:= li_count + 1 ;
340 gtt_trans_steps(li_count).param_name := 'p_annual_equivalent' ;
341 gtt_trans_steps(li_count).param_value :=
342 p_ltt_salary_data(1).annual_equivalent;
343 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
344
348 p_ltt_salary_data(1).annual_change;
345 li_count:= li_count + 1 ;
346 gtt_trans_steps(li_count).param_name := 'p_annual_change' ;
347 gtt_trans_steps(li_count).param_value :=
349 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
350
351 -- added default values to the Txn
352 li_count := li_count + 1 ;
353 gtt_trans_steps(li_count).param_name := 'p_default_date';
354 gtt_trans_steps(li_count).param_value :=
355 to_char(p_ltt_salary_data(1).default_date,
356 hr_transaction_ss.g_date_format);
357 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
358
359 li_count := li_count + 1 ;
360 gtt_trans_steps(li_count).param_name := 'p_default_bg_id';
361 gtt_trans_steps(li_count).param_value := p_ltt_salary_data(1).default_bg_id;
362 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
363
364 li_count := li_count + 1 ;
365 gtt_trans_steps(li_count).param_name := 'p_default_currency';
366 gtt_trans_steps(li_count).param_value :=
367 p_ltt_salary_data(1).default_currency;
368
369 if ((p_flow_mode is not null and
370 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration) or
371 p_ltt_salary_data(1).default_currency is null)
372 then
373 gtt_trans_steps(li_count).param_value :=
374 p_ltt_salary_data(1).currency;
375 end if;
376 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
377
378 li_count := li_count + 1 ;
379 gtt_trans_steps(li_count).param_name := 'p_default_format_string';
380 gtt_trans_steps(li_count).param_value :=
381 p_ltt_salary_data(1).default_format_string;
382 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
383
384 li_count := li_count + 1 ;
385 gtt_trans_steps(li_count).param_name := 'p_default_salary_basis_name';
386 gtt_trans_steps(li_count).param_value :=
387 p_ltt_salary_data(1).default_salary_basis_name;
388 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
389
390 li_count := li_count + 1 ;
391 gtt_trans_steps(li_count).param_name := 'p_default_pay_basis_name';
392 gtt_trans_steps(li_count).param_value :=
393 p_ltt_salary_data(1).default_pay_basis_name;
394 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
395
396 li_count := li_count + 1 ;
397 gtt_trans_steps(li_count).param_name := 'p_default_pay_basis';
398 gtt_trans_steps(li_count).param_value :=
399 p_ltt_salary_data(1).default_pay_basis;
400 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
401
402 li_count := li_count + 1 ;
403 gtt_trans_steps(li_count).param_name :=
404 'p_default_pay_annual_factor';
405 gtt_trans_steps(li_count).param_value :=
406 p_ltt_salary_data(1).default_pay_annual_factor;
407 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
408
409 li_count := li_count + 1 ;
410 gtt_trans_steps(li_count).param_name := 'p_default_grade';
411 gtt_trans_steps(li_count).param_value := p_ltt_salary_data(1).default_grade;
412 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
413
414 li_count := li_count + 1 ;
415 gtt_trans_steps(li_count).param_name :=
416 'p_default_grade_annual_factor';
417 gtt_trans_steps(li_count).param_value :=
418 p_ltt_salary_data(1).default_grade_annual_factor;
419 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
420
421 li_count := li_count + 1 ;
422 gtt_trans_steps(li_count).param_name := 'p_default_minimum_salary';
423 gtt_trans_steps(li_count).param_value :=
424 p_ltt_salary_data(1).default_minimum_salary;
425 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
426
427 li_count := li_count + 1 ;
428 gtt_trans_steps(li_count).param_name := 'p_default_maximum_salary';
429 gtt_trans_steps(li_count).param_value :=
430 p_ltt_salary_data(1).default_maximum_salary;
431 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
432
433 li_count := li_count + 1 ;
434 gtt_trans_steps(li_count).param_name := 'p_default_midpoint_salary';
435 gtt_trans_steps(li_count).param_value :=
436 p_ltt_salary_data(1).default_midpoint_salary;
437 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
438
439 li_count := li_count + 1 ;
440 gtt_trans_steps(li_count).param_name := 'p_default_prev_salary';
441 gtt_trans_steps(li_count).param_value :=
442 p_ltt_salary_data(1).default_prev_salary;
443 if p_flow_mode is not null and
444 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
445 then
446 gtt_trans_steps(li_count).param_value := 0;
447 end if;
448 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
449
450 li_count := li_count + 1 ;
451 gtt_trans_steps(li_count).param_name := 'p_default_last_change_date';
452 gtt_trans_steps(li_count).param_value :=
453 to_char(p_ltt_salary_data(1).default_last_change_date,
454 hr_transaction_ss.g_date_format);
455 gtt_trans_steps(li_count).param_data_type := 'DATE' ;
456
457 li_count := li_count + 1 ;
458 gtt_trans_steps(li_count).param_name := 'p_default_element_entry_id';
462
459 gtt_trans_steps(li_count).param_value :=
460 p_ltt_salary_data(1).default_element_entry_id;
461 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
463 li_count := li_count + 1 ;
464 gtt_trans_steps(li_count).param_name := 'p_default_basis_changed';
465 gtt_trans_steps(li_count).param_value :=
466 p_ltt_salary_data(1).default_basis_changed;
467 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
468
469 li_count := li_count + 1 ;
470 gtt_trans_steps(li_count).param_name := 'p_default_uom';
471 gtt_trans_steps(li_count).param_value := p_ltt_salary_data(1).default_uom;
472 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
473
474 li_count := li_count + 1 ;
475 gtt_trans_steps(li_count).param_name := 'p_default_grade_uom';
476 gtt_trans_steps(li_count).param_value :=
477 p_ltt_salary_data(1).default_grade_uom;
478 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
479
480 li_count := li_count + 1 ;
481 gtt_trans_steps(li_count).param_name := 'p_default_change_amount';
482 gtt_trans_steps(li_count).param_value :=
483 p_ltt_salary_data(1).default_change_amount;
484 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
485
486 li_count := li_count + 1 ;
487 gtt_trans_steps(li_count).param_name := 'p_default_change_percent';
488 gtt_trans_steps(li_count).param_value :=
489 p_ltt_salary_data(1).default_change_percent;
490 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
491
492 li_count := li_count + 1 ;
493 gtt_trans_steps(li_count).param_name := 'p_default_quartile';
494 gtt_trans_steps(li_count).param_value :=
495 p_ltt_salary_data(1).default_quartile;
496 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
497
498 li_count := li_count + 1 ;
499 gtt_trans_steps(li_count).param_name := 'p_default_comparatio';
500 gtt_trans_steps(li_count).param_value :=
501 p_ltt_salary_data(1).default_comparatio;
502 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
503
504 li_count := li_count + 1 ;
505 gtt_trans_steps(li_count).param_name := 'p_default_last_pay_change';
506 gtt_trans_steps(li_count).param_value :=
507 p_ltt_salary_data(1).default_last_pay_change;
508 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
509
510 li_count := li_count + 1 ;
511 gtt_trans_steps(li_count).param_name := 'p_default_flsa_status';
512 gtt_trans_steps(li_count).param_value :=
513 p_ltt_salary_data(1).default_flsa_status;
514 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
515
516 li_count := li_count + 1 ;
517 gtt_trans_steps(li_count).param_name := 'p_default_currency_symbol';
518 gtt_trans_steps(li_count).param_value :=
519 p_ltt_salary_data(1).default_currency_symbol;
520 gtt_trans_steps(li_count).param_data_type := 'VARCHAR2' ;
521
522 li_count := li_count + 1 ;
523 gtt_trans_steps(li_count).param_name := 'p_default_precision';
524 gtt_trans_steps(li_count).param_value :=
525 p_ltt_salary_data(1).default_precision;
526 gtt_trans_steps(li_count).param_data_type := 'NUMBER' ;
527
528
529 -- save the txn data
530 hr_utility.trace('Create Transaction and Transaction Step ');
531
532 open get_transaction_step_id
533 (c_item_type => p_item_type
534 ,c_item_key => p_item_key
535 );
536
537 fetch get_transaction_step_id into ln_transaction_step_id;
538 close get_transaction_step_id;
539
540 hr_utility.trace(' existing ln_transaction_step_id ' || ln_transaction_step_id);
541
542 hr_transaction_ss.save_transaction_step(
543 p_Item_Type => p_item_type
544 ,p_Item_Key => p_item_key
545 ,p_ActID => p_act_id
546 ,p_login_person_id => ln_creator_person_id
547 ,p_transaction_step_id => ln_transaction_step_id
548 ,p_transaction_data => gtt_trans_steps
549 ,p_api_name => lv_api_name
550 ,p_plan_id => p_plan_id
551 ,p_rptg_grp_id => p_rptg_grp_id
552 ,p_effective_date_option => p_effective_date_option);
553
554 p_step_id := ln_transaction_step_id;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 message := 'Exception in maintain_txn_java_for_gsp' || SQLERRM;
559
560 hr_utility.trace(message);
561 raise;
562 end save_gsp_txn;
563
564 procedure create_pay_txn
565 (
566 p_item_type IN wf_items.item_type%type,
567 p_item_key IN wf_items.item_key%TYPE,
568 p_ltt_salary_data IN sshr_sal_prop_tab_typ,
569 P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
570 P_Effective_Date In Date,
571 p_transaction_id in number,
572 p_transaction_step_id in number,
573 p_pay_basis_id in Per_All_Assignments_F.pay_basis_id%TYPE,
574 p_old_pay_basis_id in Per_All_Assignments_F.pay_basis_id%TYPE,
575 p_business_group_id in Per_All_Assignments_F.business_group_id%TYPE
576 )
577 is
578
579 cursor pay_txn is
580 select pay_transaction_id from per_pay_transactions where
581 item_type=p_item_type and item_key=p_item_key and status='NEW';
582
583 cursor proposals_id is
584 select pay_proposal_id from per_pay_proposals where assignment_id=p_assignment_id and
585 P_Effective_Date between change_date and date_to;
586
587 message VARCHAR2(10000) := '';
588 l_pay_basis_id Per_All_Assignments_F.pay_basis_id%TYPE;
589 l_old_pay_basis_id Per_All_Assignments_F.pay_basis_id%TYPE;
590 pay_txn_id per_pay_transactions.pay_transaction_id%type;
591 pay_prop_id per_pay_proposals.pay_proposal_id%type;
592
593 begin
594 l_pay_basis_id := p_pay_basis_id;
595 l_old_pay_basis_id := p_old_pay_basis_id;
596
597 if p_pay_basis_id is null then
598 l_pay_basis_id := -1;
599 end if;
600
601 if l_old_pay_basis_id is null then
602 l_old_pay_basis_id := -1;
603 end if;
604
605 open pay_txn;
606 fetch pay_txn into pay_txn_id;
607
608 if pay_txn%found then
609 update per_pay_transactions set
610 PAY_BASIS_ID = l_pay_basis_id,
611 change_amount_n = p_ltt_salary_data(1).salary_change_amount,
612 change_percentage = p_ltt_salary_data(1).salary_change_percent,
613 PROPOSED_SALARY_N = p_ltt_salary_data(1).proposed_salary
614 where PAY_TRANSACTION_ID = pay_txn_id;
615 close pay_txn;
616 return;
617 end if;
618
619 close pay_txn;
620
621 open proposals_id;
622 fetch proposals_id into pay_prop_id;
623 close proposals_id;
624
625 if pay_prop_id is not null then
626 insert into per_pay_transactions(
627 PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
628 TRANSACTION_ID, -- TRANSACTION_ID,
629 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
630 ITEM_TYPE,-- ITEM_TYPE,
631 ITEM_KEY,-- ITEM_KEY,
632 pay_proposal_id,
633 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
634 PAY_BASIS_ID,-- PAY_BASIS_ID,
635 business_group_id,
636 CHANGE_DATE,
640 multiple_components,
637 DATE_TO,
638 last_change_date,
639 reason,
641 change_amount_n,
642 change_percentage,
643 PROPOSED_SALARY_N,
644 parent_pay_transaction_id,
645 prior_pay_proposal_id,
646 PRIOR_PAY_TRANSACTION_ID,
647 PRIOR_PROPOSED_SALARY_N,
648 PRIOR_PAY_BASIS_ID,
649 approved,
650 STATUS,-- STATUS,
651 DML_OPERATION,-- DML_OPERATION,
652 object_version_number)
653 values(
654 PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
655 p_transaction_id, -- TRANSACTION_ID,
656 p_transaction_step_id,-- TRANSACTION_STEP_ID,
657 p_item_type,-- ITEM_TYPE,
658 p_item_key,-- ITEM_KEY,
659 pay_prop_id,
660 p_assignment_id,
661 l_old_pay_basis_id,
662 p_business_group_id,
663 p_ltt_salary_data(1).default_last_change_date,
664 p_ltt_salary_data(1).salary_effective_date - 1,
665 null,
666 'GSP',
667 'N', -- change_amount_n,
668 null,
669 null,
670 p_ltt_salary_data(1).current_salary,
671 null,
672 null,
673 null,
674 null,
675 null,
676 'Y',
677 'DATE_ADJUSTED',-- STATUS,
678 'UPDATE',-- DML_OPERATION,
679 1);
680
681 end if;
682
683 insert into per_pay_transactions(
684 PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
685 TRANSACTION_ID, -- TRANSACTION_ID,
686 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
687 ITEM_TYPE,-- ITEM_TYPE,
688 ITEM_KEY,-- ITEM_KEY,
689 pay_proposal_id,
690 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
691 PAY_BASIS_ID,-- PAY_BASIS_ID,
692 business_group_id,
693 CHANGE_DATE,
694 DATE_TO,
695 last_change_date,
696 reason,
697 multiple_components,
698 change_amount_n,
699 change_percentage,
700 PROPOSED_SALARY_N,
701 parent_pay_transaction_id,
702 prior_pay_proposal_id,
703 PRIOR_PAY_TRANSACTION_ID,
704 PRIOR_PROPOSED_SALARY_N,
705 PRIOR_PAY_BASIS_ID,
706 approved,
707 STATUS,-- STATUS,
708 DML_OPERATION,-- DML_OPERATION,
709 object_version_number)
710 values(
711 PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
712 p_transaction_id, -- TRANSACTION_ID,
713 p_transaction_step_id,-- TRANSACTION_STEP_ID,
714 p_item_type,-- ITEM_TYPE,
715 p_item_key,-- ITEM_KEY,
716 null,
717 p_assignment_id,
718 l_pay_basis_id,
719 p_business_group_id,
720 p_ltt_salary_data(1).salary_effective_date,
721 null,
722 null,
723 'GSP',
724 'N', -- change_amount_n,
725 p_ltt_salary_data(1).salary_change_amount,
726 p_ltt_salary_data(1).salary_change_percent,
727 p_ltt_salary_data(1).proposed_salary,
728 null,
729 pay_prop_id,
730 null,
731 p_ltt_salary_data(1).current_salary,
732 l_old_pay_basis_id,
733 'Y',
734 'NEW',-- STATUS,
735 'INSERT',-- DML_OPERATION,
736 1);
737
738 EXCEPTION
739 WHEN OTHERS THEN
740 message := 'Exception in create_pay_txn' || SQLERRM;
741
742 hr_utility.trace(message);
743 raise;
744 end create_pay_txn;
745 --
746 --
747 -- Follwing procedure returns true if there is a grade ladder setup for the
748 -- given business group.
749 --
750 Procedure check_grade_ladder_exists(p_business_group_id in number,
751 p_effective_date in date ,
752 p_grd_ldr_exists_flag out nocopy boolean)
753 is
754 --
755 l_status boolean := false;
756 l_proc varchar2(72) := 'check_grade_ladder_exists';
757 --
758 Begin
759 --
760 hr_utility.set_location('Entering:'||l_proc, 5);
761 --
762 pqh_employee_salary.check_grade_ladder_exists
763 (p_business_group_id => p_business_group_id,
764 p_effective_date => p_effective_date,
765 p_grd_ldr_exists_flag => l_status);
766
767 --
768 p_grd_ldr_exists_flag:= l_status;
769 hr_utility.set_location('Leaving:'||l_proc, 10);
770 --
771 --
772 Exception When others then
773 --
774 hr_utility.set_location('Exception:'||l_proc, 200);
775 raise;
776 --
777 End check_grade_ladder_exists;
778
779 -- newly added grade_ladder_id attribute decode function
780 ---------------------------------------------------------------
781 function getGradeLadderName (
782
783 --
784 p_grade_ladder_id number) return varchar2 is
785 --
786 cursor csr_lookup is
787 select name
788 from ben_pgm_f
789 where pgm_id = p_grade_ladder_id;
790 --
791 v_meaning ben_pgm_f.name%TYPE := null;
792 --
793 begin
794 --
795 -- Only open the cursor if the parameter is going to retrieve anything
796 --
797 if p_grade_ladder_id is not null then
798 --
799 open csr_lookup;
800 fetch csr_lookup into v_meaning;
801 close csr_lookup;
802 --
803 end if;
804 --
805 return v_meaning;
806 end getGradeLadderName;
807 --------------------------------------------------------------
808
809 end hr_pay_rate_gsp_ss;