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