[Home] [Help]
PACKAGE BODY: APPS.HR_PROCESS_ASSIGNMENT_STEP_SS
Source
1 PACKAGE BODY HR_PROCESS_ASSIGNMENT_STEP_SS as
2 /* $Header: hrpspwrs.pkb 120.13 2010/09/14 10:22:39 ckondapi ship $ */
3
4 g_package constant varchar2(75):='HR_PROCESS_ASSIGNMENT_STEP_SS.';
5 g_api_name constant varchar2(75):=g_package || 'PROCESS_API';
6
7 procedure process_step_save
8 (p_item_type IN varchar2,
9 p_item_key IN varchar2,
10 p_actId in varchar2,
11 p_login_person_id in varchar2,
12 p_effective_date in varchar2,
13 p_effective_date_option in varchar2,
14 p_assignment_id in number,
15 p_placement_id in number,
16 p_step_id in number,
17 p_grade_id in number,
18 p_grade_ladder_pgm_id in number,
19 p_object_version_number in number,
20 p_business_group_id in number,
21 p_effective_start_date in date,
22 p_effective_end_date in date,
23 p_reason in varchar2 default hr_api.g_varchar2,
24 p_salary_change_warning in out nocopy varchar2,
25 p_gsp_post_process_warning out nocopy varchar2,
26 p_gsp_salary_effective_date out nocopy date,
27 p_flow_mode in varchar2 default null,
28 p_rptg_grp_id IN VARCHAR2 DEFAULT NULL,
29 p_plan_id IN VARCHAR2 DEFAULT NULL,
30 p_page_error in out nocopy varchar2,
31 p_page_error_msg in out nocopy varchar2
32 )
33 IS
34 l_datetrack_update_mode varchar2(50);
35 dummy varchar2(5);
36
37 lb_grd_ldr_exists_flag boolean default false;
38 l_effective_date date;
39 ltt_salary_data sshr_sal_prop_tab_typ;
40
41 l_transaction_id number;
42 l_transaction_step_id number default null;
43 l_trns_object_version_number number default null;
44 l_trans_tbl hr_transaction_ss.transaction_table;
45 l_count number;
46 l_result varchar2(100) default null;
47
48 l_assignment_id number;
49 l_grade_ladder_pgm_id number;
50 l_last_step_change_date date;
51 lv_gsp_review_proc_call VARCHAR2(30) default null;
52 lv_gsp_flow_mode VARCHAR2(30) default null;
53 ln_gsp_step_id NUMBER;
54 ln_gsp_activity_id NUMBER;
55
56 l_grade_id number;
57 l_pay_basis_id number;
58 l_pay_basis_id_proc number;
59 ln_gsp_txn_id number;
60 ln_gsp_update_mode varchar2(10);
61 lc_temp_grade_ladder_id NUMBER;
62 lc_temp_upd_sal_cd varchar2(30);
63
64 l_proc varchar2(72) := g_package||'process_step_save';
65
66 cursor last_step_change_date is
67 select max(pspp.effective_start_date)
68 from per_spinal_point_placements_f pspp
69 where pspp.placement_id=p_placement_id;
70
71 cursor step_correction_date is
72 select null
73 from per_spinal_point_placements_f pspp
74 where pspp.placement_id = p_placement_id
75 and pspp.effective_start_date = l_effective_date;
76
77 cursor csr_assignment is
78 select assignment_id,assignment_type, pay_basis_id
79 from per_all_assignments_f
80 where assignment_id = p_assignment_id and
81 l_effective_date between effective_start_date and effective_end_date;
82
83 cursor csr_assignment_proc is
84 select grade_id,grade_ladder_pgm_id,pay_basis_id
85 from per_all_assignments_f
86 where assignment_id = l_assignment_id and
87 l_effective_date between effective_start_date and effective_end_date;
88
89 cursor csr_placement_db is
90 select step_id,reason
91 from per_spinal_point_placements_f
92 where placement_id=p_placement_id and
93 l_effective_date between effective_start_date and effective_end_date;
94
95 cursor csr_placement is
96 select step_id, reason, object_version_number,
97 effective_start_date, effective_end_date
98 from per_spinal_point_placements_f
99 where placement_id = p_placement_id and
100 l_effective_date between effective_start_date and effective_end_date;
101
102 CURSOR lc_sal_updateable_grade_ladder is
103 select pgm_id, update_salary_cd from ben_pgm_f
104 where
105 pgm_id = l_grade_ladder_pgm_id
106 and l_effective_date between effective_start_date and effective_end_date;
107
108 l_person_id number;
109 l_assignment_type varchar2(5);
110 l_step_id number;
111 l_reason varchar2(30);
112 l_step_id_db number;
113 l_reason_db varchar2(30);
114 l_object_version_number number;
115 l_effective_start_date date;
116 l_effective_end_date date;
117
118 g_registration boolean :=false;
119 g_applicant_hire boolean := false;
120 g_normal_flow boolean := false;
121
122 begin
123
124 hr_utility.set_location('Entering:'||l_proc, 5);
125
126 if p_step_id is null then
127 return;
128 end if;
129
130 hr_assignment_common_save_web.get_step
131 (p_item_type => p_item_type
132 ,p_item_key => p_item_key
133 ,p_api_name => g_api_name
134 ,p_transaction_step_id => l_transaction_step_id
135 ,p_transaction_id => l_transaction_id);
136
137
138 l_effective_date := to_date(p_effective_date,g_date_format);
139 p_gsp_salary_effective_date := l_effective_date;
140 l_assignment_id := p_assignment_id;
141
142 open csr_placement_db;
143 fetch csr_placement_db into l_step_id_db, l_reason_db;
144 close csr_placement_db;
145
146 if (nvl(p_step_id,hr_api.g_number) = nvl(l_step_id_db,hr_api.g_number)) AND
147 (nvl(p_reason,hr_api.g_varchar2) = nvl(l_reason_db,hr_api.g_varchar2)) then
148
149 if l_transaction_step_id is not null then
150 hr_transaction_ss.delete_transaction_step
151 (p_transaction_step_id => l_transaction_step_id
152 ,p_login_person_id => p_login_person_id);
153 delete_pay_step(
154 p_item_type => p_item_type,
155 p_item_key => p_item_key,
156 p_login_person_id => p_login_person_id);
157 end if;
158
159 return;
160 end if;
161
162 if p_flow_mode is not null and
163 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
164 then
165 hr_utility.set_location('p_flow_mode = hr_process_assignment_ss.g_new_hire_registration:'||l_proc,10);
166 g_registration := true;
167 end if;
168
169 hr_pay_rate_gsp_ss.check_grade_ladder_exists(
170 p_business_group_id => p_business_group_id,
171 p_effective_date => l_effective_date,
172 p_grd_ldr_exists_flag => lb_grd_ldr_exists_flag);
173
174 if(lb_grd_ldr_exists_flag) then
175 ltt_salary_data := sshr_sal_prop_tab_typ(sshr_sal_prop_obj_typ(
176 null,-- pay_proposal_id NUMBER,
177 l_assignment_id,-- assignment_id NUMBER,
178 p_business_group_id,--business_group_id NUMBER,
179 l_effective_date,--effective_date DATE,
180 null,--comments VARCHAR2(2000),
181 null,--next_sal_review_date DATE,
182 null,--salary_change_amount NUMBER ,
183 null,--salary_change_percent NUMBER ,
184 null,--annual_change NUMBER ,
185 null,--proposed_salary NUMBER ,
186 null,--proposed_percent NUMBER ,
187 null,--proposal_reason VARCHAR2(30),
188 null,--ranking NUMBER,
189 null,--current_salary NUMBER,
190 null,--performance_review_id NUMBER,
191 null,--multiple_components VARCHAR2(1),
192 null,--element_entry_id NUMBER ,
193 null,--selection_mode VARCHAR2(1),
194 null,--ovn NUMBER,
195 null,--currency VARCHAR2(15),
196 null,--pay_basis_name VARCHAR2(80),
197 null,--annual_equivalent NUMBER ,
198 null,--total_percent NUMBER ,
199 null,--quartile NUMBER ,
200 null,--comparatio NUMBER ,
201 null,--lv_selection_mode VARCHAR2(1),
202 null,--attribute_category VARCHAR2(150),
203 null,--attribute1 VARCHAR2(150),
204 null,--attribute2 VARCHAR2(150),
205 null,--attribute3 VARCHAR2(150),
206 null,--attribute4 VARCHAR2(150),
207 null,--attribute5 VARCHAR2(150),
208 null,--attribute6 VARCHAR2(150),
209 null,--attribute7 VARCHAR2(150),
210 null,--attribute8 VARCHAR2(150),
211 null,--attribute9 VARCHAR2(150),
212 null,--attribute10 VARCHAR2(150),
213 null,--attribute11 VARCHAR2(150),
214 null,--attribute12 VARCHAR2(150),
215 null,--attribute13 VARCHAR2(150),
216 null,--attribute14 VARCHAR2(150),
217 null,--attribute15 VARCHAR2(150),
218 null,--attribute16 VARCHAR2(150),
219 null,--attribute17 VARCHAR2(150),
220 null,--attribute18 VARCHAR2(150),
221 null,--attribute19 VARCHAR2(150),
222 null,--attribute20 VARCHAR2(150),
223 null, --no_of_components NUMBER,
224 null, -- salary_basis_change_type varchar2(30)
225 null, -- default_date date
226 null, -- default_bg_id number
227 null, -- default_currency VARCHAR2(15)
228 null, -- default_format_string VARCHAR2(40)
229 null, -- default_salary_basis_name varchar2(30)
230 null, -- default_pay_basis_name varchar2(80)
231 null, -- default_pay_basis varchar2(30)
232 null, -- default_pay_annual_factor number
233 null, -- default_grade VARCHAR2(240)
234 null, -- default_grade_annual_factor number
235 null, -- default_minimum_salary number
236 null, -- default_maximum_salary number
237 null, -- default_midpoint_salary number
238 null, -- default_prev_salary number
239 null, -- default_last_change_date date
240 null, -- default_element_entry_id number
241 null, -- default_basis_changed number
242 null, -- default_uom VARCHAR2(30)
243 null, -- default_grade_uom VARCHAR2(30)
244 null, -- default_change_amount number
245 null, -- default_change_percent number
246 null, -- default_quartile number
247 null, -- default_comparatio number
248 null, -- default_last_pay_change varchar2(200)
249 null, -- default_flsa_status varchar2(80)
250 null, -- default_currency_symbol varchar2(4)
251 null, -- default_precision number
252 null, -- salary_effective_date date
253 null, -- gsp_dummy_txn varchar2(30)
254 null,
255 null,
256 null,
257 null,
258 null
259 ));
260 -- store the current salary in the ltt_salary_data
261 hr_pay_rate_gsp_ss.get_employee_current_salary(
262 p_assignment_id => l_assignment_id,
263 P_effective_date => l_effective_date,
264 p_ltt_salary_data => ltt_salary_data);
265
266 if p_flow_mode is not null and
267 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
268 then
269 ltt_salary_data(1).current_salary := 0;
270 end if;
271 end if;
272
273 open csr_assignment;
274 fetch csr_assignment into l_assignment_id,l_assignment_type, l_pay_basis_id;
275 if (csr_assignment%notfound and g_registration) then
276 hr_utility.set_location('New Hire:'||l_proc, 15);
277 savepoint new_hire;
278 hr_new_user_reg_ss.processNewUserTransaction
279 (WfItemType => p_item_type
280 ,WfItemKey => p_item_key
281 ,PersonId => l_person_id
282 ,AssignmentId => l_assignment_id);
283 elsif (l_assignment_type = 'A') then
284 hr_utility.set_location('Applicant Hire:'||l_proc, 20);
285 g_applicant_hire := true;
286 SAVEPOINT applicant_hire;
287
288 hr_new_user_reg_ss.process_selected_transaction(p_item_type => p_item_type,
289 p_item_key => p_item_key
290 ,p_api_name => 'HR_PROCESS_PERSON_SS.PROCESS_API');
291 hr_new_user_reg_ss.process_selected_transaction(p_item_type => p_item_type,
292 p_item_key => p_item_key
293 ,p_api_name => 'HR_PROCESS_ADDRESS_SS.PROCESS_API');
294 hr_new_user_reg_ss.process_selected_transaction(p_item_type => p_item_type,
295 p_item_key => p_item_key
296 ,p_api_name => 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API');
297 else
298 hr_utility.set_location('Normal Flow:'||l_proc, 25);
299 g_normal_flow := true;
300 savepoint normal_flow;
301 hr_new_user_reg_ss.process_selected_transaction(p_item_type => p_item_type,
302 p_item_key => p_item_key
303 ,p_api_name => 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API');
304 end if;
305 close csr_assignment;
306
307 open csr_assignment_proc;
308 fetch csr_assignment_proc into l_grade_id, l_grade_ladder_pgm_id,l_pay_basis_id_proc;
309 close csr_assignment_proc;
310
311 open csr_placement;
312 fetch csr_placement into l_step_id, l_reason, l_object_version_number,
313 l_effective_start_date, l_effective_end_date;
314 close csr_placement;
315
316 open last_step_change_date;
317 fetch last_step_change_date into l_last_step_change_date;
318 if last_step_change_date%found then
319 if l_effective_date>l_last_step_change_date then
320 hr_utility.set_location('Datetrack Mode Update:'||l_proc, 30);
321 l_datetrack_update_mode:='UPDATE';
322 else
323 open step_correction_date;
324 fetch step_correction_date into dummy;
325 if step_correction_date%FOUND then
326 hr_utility.set_location('Datetrack Mode Correct:'||l_proc, 35);
327 l_datetrack_update_mode:='CORRECTION';
328 else
329 hr_utility.set_location('Datetrack Mode Update Change Insert:'||l_proc, 40);
330 l_datetrack_update_mode:='UPDATE_CHANGE_INSERT';
331 end if;
332 close step_correction_date;
333 end if;
334 end if;
335 close last_step_change_date;
336
337 if p_placement_id is null then
338 create_step
339 (p_validate => true,
340 p_effective_date => l_effective_date,
341 p_business_group_id => p_business_group_id,
342 p_assignment_id => l_assignment_id,
343 p_step_id => p_step_id,
344 p_reason => p_reason,
345 p_placement_id => p_placement_id,
346 p_object_version_number => l_object_version_number,
347 p_effective_start_date => l_effective_start_date,
348 p_effective_end_date => l_effective_end_date,
349 p_gsp_post_process_warning => p_gsp_post_process_warning,
350 p_ltt_salary_data => ltt_salary_data,
351 p_page_error => p_page_error,
352 p_page_error_msg => p_page_error_msg);
353 else
354 update_step
355 (p_validate => true,
356 p_effective_date => l_effective_date,
357 p_datetrack_update_mode => l_datetrack_update_mode,
358 p_step_id => p_step_id,
359 p_reason => p_reason,
360 p_business_group_id => p_business_group_id,
361 p_assignment_id => l_assignment_id,
362 p_placement_id => p_placement_id,
363 p_object_version_number => l_object_version_number,
364 p_effective_start_date => l_effective_start_date,
365 p_effective_end_date => l_effective_end_date,
366 p_gsp_post_process_warning => p_gsp_post_process_warning,
367 p_ltt_salary_data => ltt_salary_data,
368 p_page_error => p_page_error,
369 p_page_error_msg => p_page_error_msg);
370 end if;
371
372 hr_utility.set_location('After calling placement api:'||l_proc, 45);
373
374 if p_flow_mode is not null and
375 p_flow_mode = hr_process_assignment_ss.g_new_hire_registration
376 then
377 rollback to new_hire;
378 end if;
379
380 if (g_applicant_hire) then
381 rollback to applicant_hire;
382 end if;
383
384 if (g_normal_flow) then
385 rollback to normal_flow;
386 end if;
387
388 if l_transaction_step_id is null then
389 l_transaction_id := hr_transaction_ss.get_transaction_id
390 (p_item_type => p_item_type
391 ,p_item_key => p_item_key);
392
393 if l_transaction_id is null then
394 hr_transaction_ss.start_transaction
395 (itemtype => p_item_type
396 ,itemkey => p_item_key
397 ,actid => -1
398 ,funmode => 'RUN'
399 ,p_login_person_id => p_login_person_id
400 ,result => l_result
401 ,p_plan_id => p_plan_id
402 ,p_rptg_grp_id => p_rptg_grp_id
403 ,p_effective_date_option => p_effective_date_option);
404
405 l_transaction_id:=hr_transaction_ss.get_transaction_id
406 (p_item_type => p_item_type
407 ,p_item_key => p_item_key);
408 end if;
409
410 hr_transaction_api.Set_Process_Order_String(p_item_type => p_item_type
411 ,p_item_key => p_item_key
412 ,p_actid => -1);
413
414 hr_transaction_api.create_transaction_step
415 (p_validate => false
416 ,p_creator_person_id => p_login_person_id
417 ,p_transaction_id => l_transaction_id
418 ,p_api_name => g_api_name
419 ,p_item_type => p_item_type
420 ,p_item_key => p_item_key
421 ,p_activity_id => -1
422 ,p_transaction_step_id => l_transaction_step_id
423 ,p_object_version_number => l_trns_object_version_number
424 );
425 end if;
426
427
428 l_count:=1;
429 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
430 l_trans_tbl(l_count).param_value := l_assignment_id;
431 l_trans_tbl(l_count).param_original_value := l_assignment_id; --ns
432 l_trans_tbl(l_count).param_data_type := 'NUMBER';
433
434 l_count:=l_count+1;
435 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE_OPTION';
436 l_trans_tbl(l_count).param_value := p_effective_date_option;
437 l_trans_tbl(l_count).param_original_value := p_effective_date_option;
438 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
439
440 l_count:=l_count+1;
441 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
442 l_trans_tbl(l_count).param_value := p_login_person_id;
443 l_trans_tbl(l_count).param_data_type := 'NUMBER';
444
445 l_count:=l_count+1;
446 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
447 l_trans_tbl(l_count).param_value := p_effective_date;
448 l_trans_tbl(l_count).param_original_value := p_effective_date; --ns
449 l_trans_tbl(l_count).param_data_type := 'DATE';
450
451 l_count:=l_count+1;
452 l_trans_tbl(l_count).param_name := 'P_PLACEMENT_ID';
453 l_trans_tbl(l_count).param_value := p_placement_id;
454 l_trans_tbl(l_count).param_original_value := p_placement_id; --ns
455 l_trans_tbl(l_count).param_data_type := 'NUMBER';
456
457 l_count:=l_count+1;
458 l_trans_tbl(l_count).param_name := 'P_STEP_ID';
459 l_trans_tbl(l_count).param_value := p_step_id;
460 l_trans_tbl(l_count).param_original_value := l_step_id_db;
461 l_trans_tbl(l_count).param_data_type := 'NUMBER';
462
463 l_count:=l_count+1; -- grade and grade_ladder_pgm not used anywhere
464 l_trans_tbl(l_count).param_name := 'P_GRADE_ID';
465 l_trans_tbl(l_count).param_value := l_grade_id;
466 l_trans_tbl(l_count).param_original_value := p_grade_id;
467 l_trans_tbl(l_count).param_data_type := 'NUMBER';
468
469 l_count:=l_count+1;
470 l_trans_tbl(l_count).param_name := 'P_GRADE_LADDER_PGM_ID';
471 l_trans_tbl(l_count).param_value := l_grade_ladder_pgm_id;
472 l_trans_tbl(l_count).param_original_value := p_grade_ladder_pgm_id; --ns
473 l_trans_tbl(l_count).param_data_type := 'NUMBER';
474
475 l_count:=l_count+1;
476 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
477 l_trans_tbl(l_count).param_value := p_business_group_id;
478 l_trans_tbl(l_count).param_original_value := p_business_group_id; --ns
479 l_trans_tbl(l_count).param_data_type := 'NUMBER';
480
481 l_count:=l_count+1;
482 l_trans_tbl(l_count).param_name := 'P_REASON';
483 l_trans_tbl(l_count).param_value := p_reason;
484 l_trans_tbl(l_count).param_original_value := l_reason_db; --ns
485 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
486
487 l_count:=l_count+1;
488 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_START_DATE';
489 l_trans_tbl(l_count).param_value := to_char(l_effective_start_date,g_date_format);
490 l_trans_tbl(l_count).param_original_value := to_char(p_effective_start_date,g_date_format);
491 l_trans_tbl(l_count).param_data_type := 'DATE';
492
493 l_count:=l_count+1;
494 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_END_DATE';
495 l_trans_tbl(l_count).param_value := to_char(l_effective_end_date,g_date_format);
496 l_trans_tbl(l_count).param_original_value := to_char(p_effective_end_date,g_date_format);
497 l_trans_tbl(l_count).param_data_type := 'DATE';
498
499 l_count:=l_count+1;
500 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
501 l_trans_tbl(l_count).param_value := l_object_version_number;
502 l_trans_tbl(l_count).param_original_value := p_object_version_number;
503 l_trans_tbl(l_count).param_data_type := 'NUMBER';
504
505 l_count:=l_count+1;
506 l_trans_tbl(l_count).param_name := 'P_FLOW_MODE';
507 l_trans_tbl(l_count).param_value := p_flow_mode;
508 l_trans_tbl(l_count).param_original_value := p_flow_mode;
509 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
510
511 l_count:=l_count+1;
512 l_trans_tbl(l_count).param_name := 'P_DATETRACK_UPDATE_MODE';
513 l_trans_tbl(l_count).param_value := l_datetrack_update_mode;
514 l_trans_tbl(l_count).param_original_value := l_datetrack_update_mode;
515 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
516
517 l_count:=l_count+1;
518 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
519 l_trans_tbl(l_count).param_value := 'HrAssignment';
520 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
521
522 l_count:=l_count+1;
523 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
524 l_trans_tbl(l_count).param_value := -1;
525 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
526
527
528 hr_transaction_ss.save_transaction_step
529 (p_item_type => p_item_type
530 ,p_item_key => p_item_key
531 ,p_actid => -1
532 ,p_login_person_id => p_login_person_id
533 ,p_transaction_step_id => l_transaction_step_id
534 ,p_transaction_data => l_trans_tbl
535 ,p_plan_id => p_plan_id
536 ,p_rptg_grp_id => p_rptg_grp_id
537 ,p_effective_date_option => p_effective_date_option
538 );
539
540 open lc_sal_updateable_grade_ladder;
541 fetch lc_sal_updateable_grade_ladder into lc_temp_grade_ladder_id,lc_temp_upd_sal_cd;
542 close lc_sal_updateable_grade_ladder;
543
544 if(lb_grd_ldr_exists_flag) then
545
546 if (( (ltt_salary_data(1).salary_change_amount < 0)
547 or (ltt_salary_data(1).salary_change_amount > 0))) THEN
548 lv_gsp_review_proc_call := 'HrPayRate';
549 lv_gsp_flow_mode := p_flow_mode ;
550 ln_gsp_activity_id := -1;
551 -- increment the process order for displaying pay rate page
552 -- in review page
553 hr_transaction_api.Set_Process_Order_String(p_item_type => p_item_type
554 ,p_item_key => p_item_key
555 ,p_actid => ln_gsp_activity_id);
556
557 -- display warning only once, thats next time onwards
558 -- ignore salary change warning
559
560 if(p_salary_change_warning <> 'IGNORE') then
561 p_salary_change_warning := 'WARNING';
562 end if;
563
564 p_gsp_salary_effective_date := ltt_salary_data(1).salary_effective_date;
565
566 -- Save the Pay Rate GSP Txn
567 if (lc_temp_upd_sal_cd = 'SALARY_BASIS') then
568 PER_SSHR_CHANGE_PAY.get_transaction_step(
569 p_item_type => p_item_type,
570 p_item_key => p_item_key,
571 p_activity_id => -1,
572 p_login_person_id => p_login_person_id,
573 p_api_name => 'PER_SSHR_CHANGE_PAY.PROCESS_API',
574 p_transaction_id => ln_gsp_txn_id,
575 p_transaction_step_id => ln_gsp_step_id,
576 p_update_mode => ln_gsp_update_mode,
577 p_effective_date_option => p_effective_date_option);
578
579 hr_pay_rate_gsp_ss.create_pay_txn(
580 p_ltt_salary_data => ltt_salary_data,
581 p_transaction_id => ln_gsp_txn_id,
582 p_transaction_step_id => ln_gsp_step_id,
583 p_item_type => p_item_type,
584 p_item_key => p_item_key,
585 p_assignment_id => l_assignment_id,
586 p_effective_date => l_effective_date,
587 p_pay_basis_id => l_pay_basis_id_proc,
588 p_old_pay_basis_id => l_pay_basis_id,
589 p_business_group_id => p_business_group_id
590 );
591 else
592
593 hr_pay_rate_gsp_ss.save_gsp_txn(
594 p_item_type => p_item_type,
595 p_item_key => p_item_key,
596 p_act_id => ln_gsp_activity_id,
597 p_ltt_salary_data => ltt_salary_data,
598 p_review_proc_call => lv_gsp_review_proc_call,
599 p_flow_mode => lv_gsp_flow_mode,
600 p_step_id => ln_gsp_step_id,
601 p_rptg_grp_id => p_rptg_grp_id,
602 p_plan_id => p_plan_id,
603 p_effective_date_option => p_effective_date_option
604 );
605 end if;
606
607 else
608 -- there is no change in grade or step and no change in salary
609 -- then remove the existing PayRate Transaction if any with
610 -- activityId = -1
611
612 -- Need to see if an asg txn step id exists or not.
613 delete_pay_step(
614 p_item_type => p_item_type,
615 p_item_key => p_item_key,
616 p_login_person_id => p_login_person_id);
617 end if;
618 end if;
619
620 hr_utility.set_location('Exiting:'||l_proc, 50);
621 EXCEPTION
622 when hr_utility.hr_error then
623 hr_message.provide_error;
624 p_page_error := hr_message.last_message_app;
625 p_page_error_msg := hr_message.get_message_text;
626 hr_utility.set_location('Exiting: p_page_error '||p_page_error||l_proc, 50);
627 hr_utility.set_location('Exiting: p_page_error_msg '||p_page_error_msg||l_proc, 50);
628 if(g_registration) then
629 rollback to new_hire;
630 end if;
631 if (g_applicant_hire) then
632 rollback to applicant_hire;
633 end if;
634 if (g_normal_flow) then
635 rollback to normal_flow;
636 end if;
637
638 when others then
639 if(g_registration) then
640 rollback to new_hire;
641 end if;
642 if (g_applicant_hire) then
643 rollback to applicant_hire;
644 end if;
645 if (g_normal_flow) then
646 rollback to normal_flow;
647 end if;
648 raise;
649
650 end process_step_save;
651
652 PROCEDURE create_step
653 ( p_validate IN boolean default false,
654 p_effective_date in date,
655 p_business_group_id in number,
656 p_assignment_id in number,
657 p_placement_id in number,
658 p_step_id in number,
659 p_object_version_number in number,
660 p_effective_start_date in date,
661 p_effective_end_date in date,
662 p_reason in varchar2 default hr_api.g_varchar2,
663 p_gsp_post_process_warning out nocopy varchar2,
664 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
665 p_page_error in out nocopy varchar2,
666 p_page_error_msg in out nocopy varchar2
667 )
668 is
669 l_object_version_number number;
670 l_placement_id number;
671 l_effective_start_date date;
672 l_effective_end_date date;
673 lb_grd_ldr_exists_flag boolean default false;
674
675 l_proc varchar2(72) := g_package||'create_step';
676
677 begin
678
679 hr_utility.set_location('Entering:'||l_proc, 5);
680
681 l_object_version_number := p_object_version_number;
682 savepoint create_step;
683 hr_sp_placement_api.create_spp
684 (p_effective_date => p_effective_date,
685 p_assignment_id => p_assignment_id,
686 p_business_group_id => p_business_group_id,
687 p_placement_id => l_placement_id,
688 p_object_version_number => l_object_version_number,
689 p_step_id => p_step_id,
690 p_reason=> p_reason,
691 p_effective_start_date => l_effective_start_date,
692 p_effective_end_date => l_effective_end_date,
693 p_gsp_post_process_warning => p_gsp_post_process_warning
694 );
695
696 hr_utility.set_location('After calling placement api:'||l_proc, 10);
697
698 hr_pay_rate_gsp_ss.check_grade_ladder_exists(
699 p_business_group_id => p_business_group_id,
700 p_effective_date => p_effective_date,
701 p_grd_ldr_exists_flag => lb_grd_ldr_exists_flag);
702
703 if(lb_grd_ldr_exists_flag) then
704 hr_pay_rate_gsp_ss.get_employee_salary(
705 p_assignment_id => p_assignment_id,
706 P_effective_date => p_effective_date,
707 p_ltt_salary_data => p_ltt_salary_data);
708 end if;
709
710 if p_validate then
711 rollback to create_step;
712 end if;
713
714 hr_utility.set_location('Leaving:'||l_proc, 15);
715 EXCEPTION
716 WHEN others THEN
717 rollback to create_step;
718 raise;
719
720 END create_step;
721
722
723 PROCEDURE update_step
724 (p_validate in boolean default false,
725 p_effective_date in date,
726 p_datetrack_update_mode in varchar2 default 'UPDATE',
727 p_placement_id in number,
728 p_business_group_id in number,
729 p_assignment_id in number,
730 p_step_id in number,
731 p_object_version_number in number,
732 p_effective_start_date in date,
733 p_effective_end_date in date,
734 p_reason in varchar2 default hr_api.g_varchar2,
735 p_gsp_post_process_warning out nocopy varchar2,
736 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
737 p_page_error in out nocopy varchar2,
738 p_page_error_msg in out nocopy varchar2
739 )
740 IS
741 l_object_version_number number;
742 l_effective_start_date date;
743 l_effective_end_date date;
744 lb_grd_ldr_exists_flag boolean default false;
745 l_proc varchar2(72) := g_package||'update_step';
746
747 BEGIN
748
749 hr_utility.set_location('Entering:'||l_proc, 5);
750
751 l_object_version_number := p_object_version_number;
752 l_effective_start_date := p_effective_start_date;
753 l_effective_end_date := p_effective_end_date;
754 savepoint update_step;
755 hr_sp_placement_api.update_spp
756 (p_effective_date => p_effective_date,
757 p_datetrack_mode => p_datetrack_update_mode,
758 p_placement_id => p_placement_id,
759 p_object_version_number => l_object_version_number,
760 p_step_id => p_step_id,
761 p_reason=> p_reason,
762 p_effective_start_date => l_effective_start_date,
763 p_effective_end_date => l_effective_end_date,
764 p_gsp_post_process_warning => p_gsp_post_process_warning
765 );
766
767 hr_utility.set_location('After calling placement api:'||l_proc, 10);
768
769 hr_pay_rate_gsp_ss.check_grade_ladder_exists(
770 p_business_group_id => p_business_group_id,
771 p_effective_date => p_effective_date,
772 p_grd_ldr_exists_flag => lb_grd_ldr_exists_flag);
773
774 if(lb_grd_ldr_exists_flag) then
775 hr_utility.set_location('if(lb_grd_ldr_exists_flag) then:'||l_proc,15);
776 hr_pay_rate_gsp_ss.get_employee_salary(
777 p_assignment_id => p_assignment_id,
778 P_effective_date => p_effective_date,
779 p_ltt_salary_data => p_ltt_salary_data);
780 end if;
781
782 if p_validate then
783 rollback to update_step;
784 end if;
785
786 hr_utility.set_location('Leaving:'||l_proc, 20);
787
788 EXCEPTION
789 WHEN others THEN
790 rollback to update_step;
791 raise;
792 END update_step;
793
794 procedure process_api
795 (p_validate in boolean default false
796 ,p_transaction_step_id in number
797 ,p_effective_date in varchar2 default null
798 )
799 is
800 l_gsp_post_process_warning varchar2(1000);
801 l_effective_date date;
802 l_object_version_number number;
803 l_effective_start_date date;
804 l_effective_end_date date;
805 l_placement_id number;
806 l_assignment_id number;
807 l_step_id number;
808 l_reason varchar2(30);
809 l_spinal_point varchar2(30);
810 l_business_group_id number;
811 l_datetrack_update_mode varchar2(50);
812 ltt_salary_data sshr_sal_prop_tab_typ;
813 l_page_error varchar2(2000);
814 l_page_error_msg varchar2(2000);
815
816 l_proc varchar2(72) := g_package||'process_api';
817
818 begin
819
820 hr_utility.set_location('Entering:'||l_proc, 5);
821
822 if (p_effective_date is not null) then
823 l_effective_date:= to_date(p_effective_date,g_date_format);
824 else
825 l_effective_date:= to_date(
826 hr_transaction_ss.get_wf_effective_date
827 (p_transaction_step_id => p_transaction_step_id),g_date_format);
828 end if;
829
830 get_transaction_data
831 (p_transaction_step_id => p_transaction_step_id
832 ,p_assignment_id => l_assignment_id
833 ,p_step_id => l_step_id
834 ,p_placement_id => l_placement_id
835 ,p_effective_start_date => l_effective_start_date
836 ,p_effective_end_date => l_effective_end_date
837 ,p_object_version_number => l_object_version_number
838 ,p_reason => l_reason
839 ,p_business_group_id => l_business_group_id
840 ,p_spinal_point => l_spinal_point
841 );
842
843 if (( hr_process_person_ss.g_assignment_id is not null) and
844 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID))
845 then
846 l_assignment_id := hr_process_person_ss.g_assignment_id;
847 end if;
848
849
850 l_datetrack_update_mode:=
851 hr_transaction_api.get_varchar2_value
852 (p_transaction_step_id => p_transaction_step_id
853 ,p_name => 'P_DATETRACK_UPDATE_MODE');
854
855 ltt_salary_data := sshr_sal_prop_tab_typ(sshr_sal_prop_obj_typ(
856 null,-- pay_proposal_id NUMBER,
857 null,-- assignment_id NUMBER,
858 null,--business_group_id NUMBER,
859 null,--effective_date DATE,
860 null,--comments VARCHAR2(2000),
861 null,--next_sal_review_date DATE,
862 null,--salary_change_amount NUMBER ,
863 null,--salary_change_percent NUMBER ,
864 null,--annual_change NUMBER ,
865 null,--proposed_salary NUMBER ,
866 null,--proposed_percent NUMBER ,
867 null,--proposal_reason VARCHAR2(30),
868 null,--ranking NUMBER,
869 null,--current_salary NUMBER,
870 null,--performance_review_id NUMBER,
871 null,--multiple_components VARCHAR2(1),
872 null,--element_entry_id NUMBER ,
873 null,--selection_mode VARCHAR2(1),
874 null,--ovn NUMBER,
875 null,--currency VARCHAR2(15),
876 null,--pay_basis_name VARCHAR2(80),
877 null,--annual_equivalent NUMBER ,
878 null,--total_percent NUMBER ,
879 null,--quartile NUMBER ,
880 null,--comparatio NUMBER ,
881 null,--lv_selection_mode VARCHAR2(1),
882 null,--attribute_category VARCHAR2(150),
883 null,--attribute1 VARCHAR2(150),
884 null,--attribute2 VARCHAR2(150),
885 null,--attribute3 VARCHAR2(150),
886 null,--attribute4 VARCHAR2(150),
887 null,--attribute5 VARCHAR2(150),
888 null,--attribute6 VARCHAR2(150),
889 null,--attribute7 VARCHAR2(150),
890 null,--attribute8 VARCHAR2(150),
891 null,--attribute9 VARCHAR2(150),
892 null,--attribute10 VARCHAR2(150),
893 null,--attribute11 VARCHAR2(150),
894 null,--attribute12 VARCHAR2(150),
895 null,--attribute13 VARCHAR2(150),
896 null,--attribute14 VARCHAR2(150),
897 null,--attribute15 VARCHAR2(150),
898 null,--attribute16 VARCHAR2(150),
899 null,--attribute17 VARCHAR2(150),
900 null,--attribute18 VARCHAR2(150),
901 null,--attribute19 VARCHAR2(150),
902 null,--attribute20 VARCHAR2(150),
903 null, --no_of_components NUMBER,
904 null, -- salary_basis_change_type varchar2(30)
905 null, -- default_date date
906 null, -- default_bg_id number
907 null, -- default_currency VARCHAR2(15)
908 null, -- default_format_string VARCHAR2(40)
909 null, -- default_salary_basis_name varchar2(30)
910 null, -- default_pay_basis_name varchar2(80)
911 null, -- default_pay_basis varchar2(30)
912 null, -- default_pay_annual_factor number
913 null, -- default_grade VARCHAR2(240)
914 null, -- default_grade_annual_factor number
915 null, -- default_minimum_salary number
916 null, -- default_maximum_salary number
917 null, -- default_midpoint_salary number
918 null, -- default_prev_salary number
919 null, -- default_last_change_date date
920 null, -- default_element_entry_id number
921 null, -- default_basis_changed number
922 null, -- default_uom VARCHAR2(30)
923 null, -- default_grade_uom VARCHAR2(30)
924 null, -- default_change_amount number
925 null, -- default_change_percent number
926 null, -- default_quartile number
927 null, -- default_comparatio number
928 null, -- default_last_pay_change varchar2(200)
929 null, -- default_flsa_status varchar2(80)
930 null, -- default_currency_symbol varchar2(4)
931 null, -- default_precision number
932 null, -- salary_effective_date date
933 null,
934 null,
935 null,
936 null,
937 null,
938 null
939 ));
940
941 if l_placement_id is null then
942 create_step
943 (p_validate => p_validate,
944 p_effective_date => l_effective_date,
945 p_business_group_id => l_business_group_id,
946 p_assignment_id => l_assignment_id,
947 p_step_id => l_step_id,
948 p_reason => l_reason
949 ,p_placement_id => l_placement_id
950 ,p_object_version_number => l_object_version_number
951 ,p_effective_start_date => l_effective_start_date
952 ,p_effective_end_date => l_effective_end_date
953 ,p_gsp_post_process_warning => l_gsp_post_process_warning
954 ,p_ltt_salary_data => ltt_salary_data
955 ,p_page_error => l_page_error
956 ,p_page_error_msg => l_page_error_msg);
957 else
958 update_step
959 (p_validate => p_validate,
960 p_effective_date => l_effective_date,
961 p_datetrack_update_mode => l_datetrack_update_mode,
962 p_step_id => l_step_id,
963 p_reason => l_reason,
964 p_business_group_id => l_business_group_id,
965 p_assignment_id => l_assignment_id
966 ,p_placement_id => l_placement_id
967 ,p_object_version_number => l_object_version_number
968 ,p_effective_start_date => l_effective_start_date
969 ,p_effective_end_date => l_effective_end_date
970 ,p_gsp_post_process_warning => l_gsp_post_process_warning
971 ,p_ltt_salary_data => ltt_salary_data
972 ,p_page_error => l_page_error
973 ,p_page_error_msg => l_page_error_msg);
974 end if;
975
976 hr_utility.set_location('Leaving:'||l_proc, 10);
977
978 end process_api;
979
980 procedure get_transaction_data
981 (p_transaction_step_id in number
982 ,p_assignment_id out nocopy number
983 ,p_step_id out nocopy number
984 ,p_placement_id out nocopy number
985 ,p_effective_start_date out nocopy date
986 ,p_effective_end_date out nocopy date
987 ,p_object_version_number out nocopy number
988 ,p_reason out nocopy varchar2
989 ,p_business_group_id out nocopy number
990 ,p_spinal_point out nocopy varchar2
991 )
992 is
993
994 l_spinal_point per_spinal_points.spinal_point%type;
995 l_proc varchar2(72) := g_package||'get_transaction_data';
996
997 cursor get_spinal_point is
998 select spinal_point from
999 per_spinal_points psp, per_spinal_point_steps_f psps where
1000 psp.spinal_point_id=psps.spinal_point_id and psps.step_id=p_step_id;
1001
1002 begin
1003
1004 hr_utility.set_location('Entering:'||l_proc, 5);
1005
1006 p_assignment_id:=
1007 hr_transaction_api.get_number_value
1008 (p_transaction_step_id => p_transaction_step_id
1009 ,p_name => 'P_ASSIGNMENT_ID');
1010 p_step_id:=
1011 hr_transaction_api.get_number_value
1012 (p_transaction_step_id => p_transaction_step_id
1013 ,p_name => 'P_STEP_ID');
1014
1015 open get_spinal_point;
1016 fetch get_spinal_point into l_spinal_point;
1017 close get_spinal_point;
1018
1019 p_spinal_point := l_spinal_point;
1020
1021 p_placement_id:=
1022 hr_transaction_api.get_number_value
1023 (p_transaction_step_id => p_transaction_step_id
1024 ,p_name => 'P_PLACEMENT_ID');
1025
1026 p_effective_start_date:=
1027 hr_transaction_api.get_date_value
1028 (p_transaction_step_id => p_transaction_step_id
1029 ,p_name => 'P_EFFECTIVE_START_DATE');
1030
1031 p_effective_end_date:=
1032 hr_transaction_api.get_date_value
1033 (p_transaction_step_id => p_transaction_step_id
1034 ,p_name => 'P_EFFECTIVE_END_DATE');
1035
1036 p_object_version_number:=
1037 hr_transaction_api.get_number_value
1038 (p_transaction_step_id => p_transaction_step_id
1039 ,p_name => 'P_OBJECT_VERSION_NUMBER');
1040
1041 p_reason:=
1042 hr_transaction_api.get_varchar2_value
1043 (p_transaction_step_id => p_transaction_step_id
1044 ,p_name => 'P_REASON');
1045
1046 p_business_group_id:=
1047 hr_transaction_api.get_number_value
1048 (p_transaction_step_id => p_transaction_step_id
1049 ,p_name => 'P_BUSINESS_GROUP_ID');
1050
1051 hr_utility.set_location('Leaving:'||l_proc, 10);
1052
1053 end get_transaction_data;
1054
1055 procedure delete_pay_step
1056 (p_item_type in wf_items.item_type%TYPE
1057 ,p_item_key in wf_items.item_key%TYPE
1058 ,p_login_person_id in varchar2) is
1059
1060 l_transaction_id number;
1061 ln_gsp_step_id NUMBER;
1062 lv_gsp_activity_id VARCHAR2(30) default null;
1063
1064 begin
1065 hr_assignment_common_save_web.get_step
1066 (p_item_type => p_item_type
1067 ,p_item_key => p_item_key
1068 ,p_api_name => 'PER_SSHR_CHANGE_PAY.PROCESS_API'
1069 ,p_transaction_step_id => ln_gsp_step_id
1070 ,p_transaction_id => l_transaction_id);
1071
1072 if (ln_gsp_step_id is null) then
1073 hr_assignment_common_save_web.get_step
1074 (p_item_type => p_item_type
1075 ,p_item_key => p_item_key
1076 ,p_api_name => 'HR_PAY_RATE_SS.PROCESS_API'
1077 ,p_transaction_step_id => ln_gsp_step_id
1078 ,p_transaction_id => l_transaction_id);
1079 end if;
1080
1081 IF (ln_gsp_step_id IS NOT NULL)
1082 THEN
1083 lv_gsp_activity_id := hr_transaction_api.get_varchar2_value
1084 (p_transaction_step_id => ln_gsp_step_id
1085 ,p_name => 'P_REVIEW_ACTID');
1086 -- for Pay Rate GSP Txn, Review Activity Id is -1
1087 if((lv_gsp_activity_id is not null) and (to_number(lv_gsp_activity_id) = -1))
1088 THEN
1089 hr_transaction_ss.delete_transaction_step
1090 (p_transaction_step_id => ln_gsp_step_id
1091 ,p_login_person_id => p_login_person_id);
1092 delete from per_pay_transactions where TRANSACTION_STEP_ID=ln_gsp_step_id;
1093 end if;
1094 END IF;
1095 end delete_pay_step;
1096 --------------------------------------------------------------
1097
1098 end hr_process_assignment_step_ss;