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