[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_ASSIGNMENT_API
Source
1 PACKAGE BODY PQH_FR_ASSIGNMENT_API As
2 /* $Header: pqasgapi.pkb 120.1.12000000.2 2007/08/17 13:17:11 abhaduri noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := ' hr_assignment_api.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 --
10 -- Procedure update_primary_assg_affectation
11
12 procedure update_primary_asg_affectation
13 (
14 p_validate in boolean default false,
15 p_assignment_id in number,
16 p_effective_date in date,
17 p_primary_affectation in varchar2,
18 p_organization_id in number,
19 p_job_id in number,
20 p_position_id in number,
21 p_datetrack_update_mode in varchar2,
22 p_object_version_number in number,
23 p_person_id in number,
24 p_supervisor_id in number default null
25 )
26 is
27
28 l_validate boolean;
29 l_no_managers_warning boolean;
30 l_other_manager_warning boolean;
31 l_other_manager_warning2 boolean;
32 l_hourly_salaried_warning boolean;
33 l_soft_coding_keyflex_id number;
34 l_cagr_grade_def_id number;
35 l_cagr_concatenated_segments varchar2(1000);
36 l_concatenated_segments varchar2(1000);
37 l_comment_id number;
38 l_effective_start_date date;
39 l_effective_end_date date;
40 l_ovn number;
41 --
42 -- Variables for IN/OUT parameters
43 l_object_version_number number;
44 l_assignment_id number;
45 l_admin_career_id number;
46
47 -- scl - 1146
48 -- people_group_id 1103
49 -- common info variables
50 l_people_group_id number ;
51 l_establishment_id number;
52 l_fr_emp_category varchar2(1000);
53 l_special_ceiling_step_id number;
54 l_group_name varchar2(1000);
55 l_org_now_no_manager_warning boolean;
56 l_spp_delete_warning boolean;
57 l_entries_changed_warning varchar2(1000);
58 l_tax_district_changed_warning boolean;
59 l_scl_id number ;
60 l_fut_pri_start_date Date;
61
62 -- Cursor to get the future assignment dates.
63
64 cursor csr_fut_primary_asg is
65 Select effective_start_date from
66 per_all_assignments_f
67 where primary_flag = 'Y'
68 and person_id = p_person_id
69 and assignment_id = p_assignment_id
70 and effective_start_date > p_effective_date;
71
72 begin
73
74 if (p_primary_affectation = 'Y' )then
75 --
76 l_ovn := p_object_version_number;
77
78 -- If Employee the following routine will be Called
79 If (pqh_fr_utility.is_worker_employee(p_person_id,p_effective_date)) then
80 --
81 hr_assignment_api.update_emp_asg_criteria
82 (
83 p_validate => p_validate
84 ,p_effective_date => p_effective_date
85 ,p_datetrack_update_mode => p_datetrack_update_mode
86 ,p_assignment_id => p_assignment_id
87 ,p_organization_id => p_organization_id
88 ,p_position_id => p_position_id
89 ,p_job_id => p_job_id
90 -- Out Variables
91 ,p_people_group_id => l_people_group_id
92 ,p_object_version_number => l_ovn -- In OUT
93 ,p_special_ceiling_step_id => l_special_ceiling_step_id
94 ,p_group_name => l_group_name
95 ,p_effective_start_date => l_effective_start_date
96 ,p_effective_end_date => l_effective_end_date
97 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
98 ,p_other_manager_warning => l_other_manager_warning
99 ,p_spp_delete_warning => l_spp_delete_warning
100 ,p_entries_changed_warning => l_entries_changed_warning
101 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
102 --
103 update per_all_assignments_f
104 set supervisor_id = p_supervisor_id
105 where
106 person_id = p_person_id
107 and p_effective_date between effective_start_date and effective_end_date
108 and assignment_id = p_assignment_id
109 and primary_flag = 'Y';
110
111 ElsIf (pqh_fr_utility.is_worker_cwk(p_person_id,p_effective_date)) Then
112
113 hr_assignment_api.update_cwk_asg_criteria
114 (
115 p_validate => p_validate
116 ,p_effective_date => p_effective_date
117 ,p_datetrack_update_mode => p_datetrack_update_mode
118 ,p_assignment_id => p_assignment_id
119 ,p_organization_id => p_organization_id
120 ,p_position_id => p_position_id
121 ,p_job_id => p_job_id
122 -- Out Variables
123 ,p_people_group_id => l_people_group_id
124 ,p_object_version_number => l_ovn -- In OUT
125 ,p_people_group_name => l_group_name
126 ,p_effective_start_date => l_effective_start_date
127 ,p_effective_end_date => l_effective_end_date
128 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
129 ,p_other_manager_warning => l_other_manager_warning
130 ,p_spp_delete_warning => l_spp_delete_warning
131 ,p_entries_changed_warning => l_entries_changed_warning
132 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
133
134 update per_all_assignments_f
135 set supervisor_id = p_supervisor_id
136 where
137 person_id = p_person_id
138 and p_effective_date between effective_start_date and effective_end_date
139 and assignment_id = p_assignment_id
140 and primary_flag = 'Y';
141
142
143 End if;
144
145 --
146 open csr_fut_primary_asg;
147 loop
148 fetch csr_fut_primary_asg into l_fut_pri_start_date;
149 exit when csr_fut_primary_asg%notfound;
150
151 update per_all_assignments_f
152 set position_id = p_position_id,
153 job_id = p_job_id,
154 organization_id = p_organization_id,
155 supervisor_id = p_supervisor_id
156 where
157 person_id = p_person_id
158 and effective_start_date = l_fut_pri_start_date
159 and assignment_id = p_assignment_id
160 and primary_flag = 'Y';
161
162 end loop;
163
164 close csr_fut_primary_asg;
165 end if;
166
167 end update_primary_asg_affectation;
168 --
169 --
170 procedure create_affectation
171 (p_validate in boolean
172 ,p_effective_date in date
173 ,p_organization_id in number
174 ,p_position_id in number
175 ,p_person_id in number
176 ,p_job_id in number
177 ,p_supervisor_id in number
178 ,p_assignment_number in out nocopy varchar2
179 ,p_assignment_status_type_id in number
180
181 ,p_identifier in varchar2
182 ,p_affectation_type in varchar2
183 ,p_percent_effected in varchar2
184 ,p_primary_affectation in varchar2 default 'N'
185 ,p_group_name out nocopy varchar2
186
187 ,p_scl_concat_segments in varchar2
188
189 ,p_assignment_id out nocopy number
190 ,p_soft_coding_keyflex_id in out nocopy number
191
192 ,p_object_version_number out nocopy number
193 ,p_effective_start_date out nocopy date
194 ,p_effective_end_date out nocopy date
195 ,p_assignment_sequence out nocopy number
196 )
197 IS
198
199 l_validate boolean;
200 l_no_managers_warning boolean;
201 l_other_manager_warning boolean;
202 l_hourly_salaried_warning boolean;
203 l_soft_coding_keyflex_id number;
204 l_cagr_grade_def_id number;
205 l_cagr_concatenated_segments varchar2(1000);
206 l_concatenated_segments varchar2(1000);
207 l_comment_id number;
208 l_position_id number;
209 l_frequency varchar2(30);
210 l_p_normal_hours number;
211 l_business_group_id number;
212
213
214 --
215 -- Variables for IN/OUT parameters
216 l_object_version_number number;
217 l_assignment_id number;
218
219 -- common info variables
220 l_people_group_id number ;
221 l_establishment_id number;
222 l_fr_emp_category varchar2(1000);
223 l_admin_career_id number;
224 l_p_asg_ovn number;
225
226
227 --
228 -- Other variables
229 l_proc varchar2(72) := g_package ||'create_affectation';
230 l_temp varchar2(10);
231
232 -- Cursors to Fetch all primary assignment's People group Segements, the same will be passed
233 -- to the secondary assingment.
234
235
236 cursor common_info_csr is
237 Select people_group_id, establishment_id , scl.segment10 FrEmpCategory,
238 assignment_id,object_version_number,normal_hours,frequency,business_group_id
239 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
240 where person_id = p_person_id
241 and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
242 and p_effective_date between effective_start_date and effective_end_date
243 and primary_flag ='Y';
244
245
246
247 -- Note : CAGR_GRADE_DEF_ID is not used, assuming that will not be used by the customer, as the functionality is not delivered.
248 --
249 l_identifier varchar2(30);
250
251 Begin
252
253 hr_utility.set_location(' Entering:' || l_proc,10);
254 hr_utility.set_location(' Parameters' || l_proc,10);
255 hr_utility.set_location(' p_organization_id:' || p_organization_id,10);
256 hr_utility.set_location(' p_position_id:' || p_position_id,10);
257 hr_utility.set_location(' p_person_id:' || p_person_id,10);
258 hr_utility.set_location(' p_job_id:' || p_job_id,10);
259 hr_utility.set_location(' p_supervisor_id:' || p_supervisor_id,10);
260 hr_utility.set_location(' p_assignment_number:' || p_assignment_number,10);
261 hr_utility.set_location(' p_assignment_status_type_id:' || p_assignment_status_type_id,10);
262 hr_utility.set_location(' p_identifier:' || p_identifier,10);
263 hr_utility.set_location(' p_affectation_type:' || p_affectation_type,10);
264 hr_utility.set_location(' p_percent_effected:' || p_percent_effected,10);
265 hr_utility.set_location(' p_primary_affectation:' || p_primary_affectation,10);
266
267 --
268
269 -- Issue a savepoint
270 --
271 savepoint create_secondary_emp_asg_swi;
272
273 --
274 -- Remember IN OUT parameter IN values
275 --
276 l_soft_coding_keyflex_id := null;
277 --
278 -- Register Surrogate ID or user key values
279 --
280 per_asg_ins.set_base_key_value
281 (p_assignment_id => p_assignment_id
282 );
283 --
284 -- Call API
285 --
286
287 -- Fetch common values from Primary assignment
288 Open common_info_csr;
289 fetch common_info_csr into l_people_group_id,l_establishment_id,
290 l_fr_emp_category,l_admin_career_id,l_p_asg_ovn,l_p_normal_hours,l_frequency,l_business_group_id;
291 Close common_info_csr;
292
293 hr_utility.set_location(' CC Id '|| p_soft_coding_keyflex_id,10);
294
295 -- Create Affectation : If Affectation is a Primary, then donot pass Position
296 -- instead position will be updated on the assignment by using l_admin_career_id = assignment_id
297 -- on primary assignment
298 -- else Position details will be copied to Affecation details
299 --
300 if (p_primary_affectation = 'Y') then
301 hr_utility.set_location(' Admin career id '|| to_char(l_admin_career_id),10);
302 PQH_FR_ASSIGNMENT_CHK.chk_primary_affectation(p_person_id, p_effective_date,l_admin_career_id);
303 l_position_id := null;
304 else
305 l_position_id := p_position_id;
306 end if;
307
308 l_identifier := p_identifier;
309 pqh_fr_assignment_chk.chk_situation(p_person_id,p_effective_date);
310 pqh_fr_assignment_chk.chk_percent_affected(p_percent_effected, p_person_id, p_effective_date );
311 pqh_fr_assignment_chk.chk_position(p_position_id,p_person_id,p_effective_date);
312 pqh_fr_assignment_chk.chk_Identifier(l_identifier);
313 pqh_fr_assignment_chk.chk_type(p_affectation_type,p_person_id,p_effective_date,p_position_id);
314
315 If (pqh_fr_utility.is_worker_employee(p_person_id,p_effective_date)) Then
316 --
317 hr_assignment_api.create_secondary_emp_asg(
318 p_validate => p_validate
319 ,p_effective_date => p_effective_date
320 ,p_person_id => p_person_id
321 ,p_organization_id => p_organization_id
322 ,p_frequency => l_frequency
323 ,p_normal_hours => l_p_normal_hours * p_percent_effected/100
324 ,p_position_id => l_position_id
325 ,p_job_id => p_job_id
326 ,p_supervisor_id => p_supervisor_id
327 ,p_assignment_number => p_assignment_number
328 ,p_assignment_status_type_id => p_assignment_status_type_id
329 ,p_establishment_id => l_establishment_id
330 ,p_scl_segment2 => 'CIVIL'
331 ,p_scl_segment10 => l_fr_emp_category
332 ,p_scl_segment23 => p_identifier
333 ,p_scl_segment24 => p_affectation_type
334 ,p_scl_segment25 => p_percent_effected
335 ,p_scl_segment26 => l_admin_career_id
336 ,p_scl_segment27 => p_primary_affectation
337
338 -- Following are Out Parameters
339 ,p_group_name => p_group_name
340 ,p_concatenated_segments => l_concatenated_segments
341 ,p_cagr_grade_def_id => l_cagr_grade_def_id
342 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
343 ,p_assignment_id => p_assignment_id
344 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
345 ,p_people_group_id => l_people_group_id
346 ,p_object_version_number => p_object_version_number
347 ,p_effective_start_date => p_effective_start_date
348 ,p_effective_end_date => p_effective_end_date
349 ,p_assignment_sequence => p_assignment_sequence
350 ,p_comment_id => l_comment_id
351 ,p_other_manager_warning => l_other_manager_warning
352 );
353 --
354 ElsIf (pqh_fr_utility.is_worker_cwk(p_person_id,p_effective_date)) Then
355 --
356
357 hr_assignment_api.create_secondary_cwk_asg(
358 p_validate => p_validate
359 ,p_effective_date => p_effective_date
360 ,p_business_group_id => l_business_group_id
361 ,p_person_id => p_person_id
362 ,p_organization_id => p_organization_id
363 ,p_frequency => l_frequency
364 ,p_normal_hours => l_p_normal_hours * p_percent_effected/100
365 ,p_position_id => l_position_id
366 ,p_job_id => p_job_id
367 ,p_supervisor_id => p_supervisor_id
368 ,p_assignment_number => p_assignment_number
369 ,p_assignment_status_type_id => p_assignment_status_type_id
370 ,p_establishment_id => l_establishment_id
371 ,p_scl_segment2 => 'CIVIL'
372 ,p_scl_segment10 => l_fr_emp_category
373 ,p_scl_segment23 => p_identifier
374 ,p_scl_segment24 => p_affectation_type
375 ,p_scl_segment25 => p_percent_effected
376 ,p_scl_segment26 => l_admin_career_id
377 ,p_scl_segment27 => p_primary_affectation
378
379 -- Following are Out Parameters
380 ,p_assignment_id => p_assignment_id
381 ,p_object_version_number => p_object_version_number
382 ,p_effective_start_date => p_effective_start_date
383 ,p_effective_end_date => p_effective_end_date
384 ,p_assignment_sequence => p_assignment_sequence
385 ,p_comment_id => l_comment_id
386 ,p_people_group_id => l_people_group_id
387 ,p_people_group_name => p_group_name
388 ,p_other_manager_warning => l_other_manager_warning
389 ,p_hourly_salaried_warning => l_hourly_salaried_warning
390 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
391 );
392
393 --
394 End if;
395
396
397 --
398 -- Convert API warning boolean parameter values to specific
399 -- messages and add them to Multiple Message List
400 --
401 --
402 -- Convert API non-warning boolean parameter values
403 --
404 --
405 -- Derive the API return status value based on whether
406 -- messages of any type exist in the Multiple Message List.
407 -- Also disable Multiple Message Detection.
408 --
409 update_primary_asg_affectation
410 (
411 p_validate => p_validate,
412 p_assignment_id => l_admin_career_id,
413 p_effective_date => p_effective_date,
414 p_primary_affectation => p_primary_affectation,
415 p_organization_id => p_organization_id,
416 p_job_id => p_job_id,
417 p_position_id => p_position_id,
418 p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
419 p_object_version_number => l_p_asg_ovn,
420 p_person_id => p_person_id,
421 p_supervisor_id => p_supervisor_id
422 ) ;
423
424 hr_utility.set_location(' Leaving:' || l_proc,20);
425 --
426 End create_affectation;
427 ---
428 ---
429 ---
430
431 procedure update_affectation
432 (p_validate in boolean default false
433 ,p_datetrack_update_mode in varchar2
434 ,p_effective_date in date
435 ,p_organization_id in number
436 ,p_position_id in number
437 ,p_person_id in number
438 ,p_job_id in number
439 ,p_supervisor_id in number
440 ,p_assignment_number in varchar2
441 ,p_assignment_status_type_id in number
442
443 ,p_identifier in varchar2
444 ,p_affectation_type in varchar2
445 ,p_percent_effected in varchar2
446 ,p_primary_affectation in varchar2
447 ,p_group_name out nocopy varchar2
448
449 ,p_scl_concat_segments in varchar2
450
451 ,p_assignment_id in number
452 ,p_soft_coding_keyflex_id out nocopy number
453 ,p_object_version_number in out nocopy number
454 ,p_effective_start_date out nocopy date
455 ,p_effective_end_date out nocopy date
456 ,p_assignment_sequence out nocopy number
457 ) IS
458
459
460 l_validate boolean;
461 l_no_managers_warning boolean;
462 l_other_manager_warning boolean;
463 l_other_manager_warning2 boolean;
464 l_hourly_salaried_warning boolean;
465 l_soft_coding_keyflex_id number;
466 l_cagr_grade_def_id number;
467 l_cagr_concatenated_segments varchar2(1000);
468 l_concatenated_segments varchar2(1000);
469 l_comment_id number;
470 l_old_primary_affectation varchar2(100);
471 l_temp varchar2(10);
472 --
473 -- Variables for IN/OUT parameters
474 l_object_version_number number;
475 l_assignment_id number;
476 l_admin_career_id number;
477 l_p_asg_ovn number;
478
479 -- common info variables
480 l_people_group_id number ;
481 l_establishment_id number;
482 l_fr_emp_category varchar2(1000);
483 l_special_ceiling_step_id number;
484 l_group_name varchar2(1000);
485 l_org_now_no_manager_warning boolean;
486 l_spp_delete_warning boolean;
487 l_entries_changed_warning varchar2(1000);
488 l_tax_district_changed_warning boolean;
489
490 --
491 -- Other variables
492 l_proc varchar2(72) := g_package ||'update_affecations';
493
494 -- Cursors to Fetch all primary assignment's People group Segements, the same will be passed
495 -- to the secondary assingment.
496
497
498 cursor common_info_csr is
499 Select people_group_id, establishment_id , scl.segment10 FrEmpCategory,assignment_id
500 ,object_version_number,normal_hours
501 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
502 where person_id = p_person_id
503 and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
504 and p_effective_date between effective_start_date and effective_end_date
505 and primary_flag ='Y';
506 -- Note : CAGR_GRADE_DEF_ID is not used, assuming that will not be used by the customer, as the functionality is not delivered.
507
508 Cursor earlier_affect_det_csr IS
509 Select segment27 PrimayAffectation
510 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
511 where assignment_id = p_assignment_id
512 and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
513 and p_effective_date between effective_start_date and effective_end_date;
514
515
516
517 l_j_id number;
518 l_p_id number;
519 l_normal_hours per_all_assignments_f.normal_hours%type;
520 Begin
521
522 hr_utility.set_location(' Entering:' || l_proc,10);
523 --
524
525 -- Issue a savepoint
526 --
527 savepoint create_secondary_emp_asg_swi;
528
529 --
530 -- Remember IN OUT parameter IN values
531 --
532 l_soft_coding_keyflex_id := p_soft_coding_keyflex_id;
533 --
534 -- Register Surrogate ID or user key values
535 --
536 per_asg_ins.set_base_key_value
537 (p_assignment_id => p_assignment_id
538 );
539 --
540 -- Call API
541 --
542
543 -- Fetch common values from Primary assignment
544 Open common_info_csr;
545 fetch common_info_csr into l_people_group_id,l_establishment_id,
546 l_fr_emp_category,l_admin_career_id,
547 l_p_asg_ovn,l_normal_hours;
548
549 Close common_info_csr;
550
551 Open earlier_affect_det_csr;
552 --
553 Fetch earlier_affect_det_csr into l_old_primary_affectation;
554 --
555 Close earlier_affect_det_csr;
556
557
558
559 /*
560 Priamary Affecation Value Can be in the following ways
561 Earlier Current Action
562 Y Y 1. Update Primary Assignment with Position, Job , org
563 2. Update Affecation with Remaining Details
564
565 N N 1. Update Affecation with complete details
566
567 N Y 1. Check are there any Primary affecations in the System, If so Throw an error
568 Saying effective affectation is already exist, Else Similar to first case
569
570 Y N 1. Update the Primary assignment by removing Job and Position
571 2. Update Affecation with Other details + Position + Job
572
573 Earlier Value: Retrieve from assignments table for the affection id
574 Current Value: Value which is passed from UI
575
576 */
577
578 If (l_old_primary_affectation is null) then
579 -- Creating Affectation for the first time, <=> there are no affecations earlier
580 --
581 l_old_primary_affectation := 'N';
582 --
583 End if;
584
585
586
587 hr_utility.set_location(' Primary Affectation Old :'||l_old_primary_affectation||'New '||p_primary_affectation,10);
588
589 pqh_fr_assignment_chk.chk_percent_affected(p_percent_effected,p_person_id,p_effective_date,p_assignment_id);
590 pqh_fr_assignment_chk.chk_position(p_position_id,p_person_id,p_effective_date);
591 pqh_fr_assignment_chk.chk_type(p_affectation_type,p_person_id,p_effective_date,p_position_id);
592
593 l_normal_hours := l_normal_hours * p_percent_effected/100;
594
595 If (l_old_primary_affectation='Y' and p_primary_affectation = 'Y') Then
596 --
597 If (pqh_fr_utility.is_worker_cwk(p_person_id,p_effective_date)) Then
598 --
599
600 hr_assignment_api.update_cwk_asg_criteria
601 (
602 p_validate => p_validate
603 ,p_effective_date => p_effective_date
604 ,p_datetrack_update_mode => p_datetrack_update_mode
605 ,p_assignment_id => p_assignment_id
606 ,p_organization_id => p_organization_id
607 ,p_position_id => null
608 ,p_job_id => p_job_id
609 -- Out Variables
610 ,p_people_group_id => l_people_group_id
611 ,p_object_version_number => p_object_version_number -- In OUT
612 ,p_people_group_name => l_group_name
613 ,p_effective_start_date => p_effective_start_date
614 ,p_effective_end_date => p_effective_end_date
615 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
616 ,p_other_manager_warning => l_other_manager_warning
617 ,p_spp_delete_warning => l_spp_delete_warning
618 ,p_entries_changed_warning => l_entries_changed_warning
619 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
620
621 hr_assignment_api.update_cwk_asg(
622 p_validate => p_validate
623 ,p_effective_date => p_effective_date
624 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
625 ,p_assignment_id => p_assignment_id
626 ,p_object_version_number => p_object_version_number
627 ,p_normal_hours => l_normal_hours
628
629 ,p_supervisor_id => p_supervisor_id
630 ,p_assignment_number => p_assignment_number
631 -- ,p_assignment_status_type_id => p_assignment_status_type_id
632
633 ,p_establishment_id => l_establishment_id
634 ,p_scl_segment23 => p_identifier
635 ,p_scl_segment24 => p_affectation_type
636 ,p_scl_segment25 => p_percent_effected
637 ,p_scl_segment26 => l_admin_career_id
638 ,p_scl_segment27 => p_primary_affectation
639 ,p_scl_segment2 => 'CIVIL'
640 ,p_scl_segment10 => l_fr_emp_category
641
642 -- Following are Out Parameters
643 ,p_concatenated_segments => l_concatenated_segments
644 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
645 ,p_comment_id => l_comment_id
646 ,p_effective_start_date => p_effective_start_date
647 ,p_effective_end_date => p_effective_end_date
648 ,p_no_managers_warning => l_other_manager_warning
649 ,p_other_manager_warning => l_other_manager_warning2
650 ,p_org_now_no_manager_warning => l_other_manager_warning2
651 ,p_hourly_salaried_warning => l_other_manager_warning2 );
652
653 ElsIf (pqh_fr_utility.is_worker_employee(p_person_id,p_effective_date)) Then
654
655 --
656 hr_assignment_api.update_emp_asg_criteria
657 (
658 p_validate => p_validate
659 ,p_effective_date => p_effective_date
660 ,p_datetrack_update_mode => p_datetrack_update_mode
661 ,p_assignment_id => p_assignment_id
662 ,p_organization_id => p_organization_id
663 ,p_position_id => null
664 ,p_job_id => p_job_id
665 -- Out Variables
666 ,p_people_group_id => l_people_group_id
667 ,p_object_version_number => p_object_version_number -- In OUT
668 ,p_special_ceiling_step_id => l_special_ceiling_step_id
669 ,p_group_name => l_group_name
670 ,p_effective_start_date => p_effective_start_date
671 ,p_effective_end_date => p_effective_end_date
672 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
673 ,p_other_manager_warning => l_other_manager_warning
674 ,p_spp_delete_warning => l_spp_delete_warning
675 ,p_entries_changed_warning => l_entries_changed_warning
676 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
677
678 hr_assignment_api.update_emp_asg(
679 p_validate => p_validate
680 ,p_effective_date => p_effective_date
681 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
682 ,p_assignment_id => p_assignment_id
683 ,p_object_version_number => p_object_version_number
684 ,p_normal_hours => l_normal_hours
685
686 ,p_supervisor_id => p_supervisor_id
687 ,p_assignment_number => p_assignment_number
688 ,p_assignment_status_type_id => p_assignment_status_type_id
689
690 ,p_establishment_id => l_establishment_id
691 ,p_segment23 => p_identifier
692 ,p_segment24 => p_affectation_type
693 ,p_segment25 => p_percent_effected
694 ,p_segment26 => l_admin_career_id
695 ,p_segment27 => p_primary_affectation
696 ,p_segment2 => 'CIVIL'
697 ,p_segment10 => l_fr_emp_category
698
699 -- Following are Out Parameters
700 ,p_cagr_grade_def_id => l_cagr_grade_def_id
701 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
702 ,p_concatenated_segments => l_concatenated_segments
703 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
704 ,p_comment_id => l_comment_id
705 ,p_effective_start_date => p_effective_start_date
706 ,p_effective_end_date => p_effective_end_date
707 ,p_no_managers_warning => l_other_manager_warning
708 ,p_other_manager_warning => l_other_manager_warning2 );
709 -- Employee Completed
710 End if;
711
712 -- If above updates are successful then update the primary assignment with affectation
713 -- detais
714
715 update_primary_asg_affectation
716 (
717 p_validate => p_validate,
718 p_assignment_id => l_admin_career_id,
719 p_effective_date => p_effective_date,
720 p_primary_affectation => p_primary_affectation,
721 p_organization_id => p_organization_id,
722 p_job_id => p_job_id,
723 p_position_id => p_position_id,
724 p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
725 p_object_version_number => l_p_asg_ovn,
726 p_person_id => p_person_id,
727 p_supervisor_id => p_supervisor_id
728 ) ;
729
730 ElsIf (l_old_primary_affectation='N' and p_primary_affectation = 'N') Then
731 --
732 If (pqh_fr_utility.is_worker_employee(p_person_id,p_effective_date)) Then
733 --
734 hr_utility.set_location('In condition when an no chages in primary affectation value assignment_id N ', 10);
735
736 hr_assignment_api.update_emp_asg_criteria
737 (
738 p_validate => p_validate
739 ,p_effective_date => p_effective_date
740 ,p_datetrack_update_mode => p_datetrack_update_mode
741 ,p_assignment_id => p_assignment_id
742 ,p_organization_id => p_organization_id
743 ,p_position_id => p_position_id
744 ,p_job_id => p_job_id
745 -- Out Variables
746 ,p_people_group_id => l_people_group_id
747 ,p_object_version_number => p_object_version_number -- In OUT
748 ,p_special_ceiling_step_id => l_special_ceiling_step_id
749 ,p_group_name => l_group_name
750 ,p_effective_start_date => p_effective_start_date
751 ,p_effective_end_date => p_effective_end_date
752 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
753 ,p_other_manager_warning => l_other_manager_warning
754 ,p_spp_delete_warning => l_spp_delete_warning
755 ,p_entries_changed_warning => l_entries_changed_warning
756 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
757
758 hr_assignment_api.update_emp_asg(
759 p_validate => p_validate
760 ,p_effective_date => p_effective_date
761 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
762 ,p_assignment_id => p_assignment_id
763 ,p_object_version_number => p_object_version_number
764 ,p_normal_hours => l_normal_hours
765 ,p_supervisor_id => p_supervisor_id
766 ,p_assignment_number => p_assignment_number
767 ,p_assignment_status_type_id => p_assignment_status_type_id
768
769 ,p_establishment_id => l_establishment_id
770 ,p_segment23 => p_identifier
771 ,p_segment24 => p_affectation_type
772 ,p_segment25 => p_percent_effected
773 ,p_segment26 => l_admin_career_id
774 ,p_segment27 => p_primary_affectation
775 ,p_segment2 => 'CIVIL'
776 ,p_segment10 => l_fr_emp_category
777
778 -- Following are Out Parameters
779 ,p_cagr_grade_def_id => l_cagr_grade_def_id
780 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
781 ,p_concatenated_segments => l_concatenated_segments
782 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
783 ,p_comment_id => l_comment_id
784 ,p_effective_start_date => p_effective_start_date
785 ,p_effective_end_date => p_effective_end_date
786 ,p_no_managers_warning => l_other_manager_warning
787 ,p_other_manager_warning => l_other_manager_warning2 );
788 --
789 ElsIf pqh_fr_utility.is_worker_cwk(p_person_id,p_effective_date) then
790 ---
791
792 hr_assignment_api.update_cwk_asg_criteria
793 (
794 p_validate => p_validate
795 ,p_effective_date => p_effective_date
796 ,p_datetrack_update_mode => p_datetrack_update_mode
797 ,p_assignment_id => p_assignment_id
798 ,p_organization_id => p_organization_id
799 ,p_position_id => p_position_id
800 ,p_job_id => p_job_id
801 -- Out Variables
802 ,p_people_group_id => l_people_group_id
803 ,p_object_version_number => p_object_version_number -- In OUT
804 ,p_people_group_name => l_group_name
805 ,p_effective_start_date => p_effective_start_date
806 ,p_effective_end_date => p_effective_end_date
807 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
808 ,p_other_manager_warning => l_other_manager_warning
809 ,p_spp_delete_warning => l_spp_delete_warning
810 ,p_entries_changed_warning => l_entries_changed_warning
811 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
812
813
814 hr_assignment_api.update_cwk_asg(
815 p_validate => p_validate
816 ,p_effective_date => p_effective_date
817 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
818 ,p_assignment_id => p_assignment_id
819 ,p_object_version_number => p_object_version_number
820 ,p_normal_hours => l_normal_hours
821
822 ,p_supervisor_id => p_supervisor_id
823 ,p_assignment_number => p_assignment_number
824 -- ,p_assignment_status_type_id => p_assignment_status_type_id
825
826 ,p_establishment_id => l_establishment_id
827 ,p_scl_segment23 => p_identifier
828 ,p_scl_segment24 => p_affectation_type
829 ,p_scl_segment25 => p_percent_effected
830 ,p_scl_segment26 => l_admin_career_id
831 ,p_scl_segment27 => p_primary_affectation
832 ,p_scl_segment2 => 'CIVIL'
833 ,p_scl_segment10 => l_fr_emp_category
834
835 -- Following are Out Parameters
836 ,p_concatenated_segments => l_concatenated_segments
837 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
838 ,p_comment_id => l_comment_id
839 ,p_effective_start_date => p_effective_start_date
840 ,p_effective_end_date => p_effective_end_date
841 ,p_no_managers_warning => l_other_manager_warning
842 ,p_other_manager_warning => l_other_manager_warning2
843 ,p_org_now_no_manager_warning => l_other_manager_warning2
844 ,p_hourly_salaried_warning => l_other_manager_warning2 );
845
846
847 ---
848 End If;
849
850 ElsIf (l_old_primary_affectation='N' and p_primary_affectation = 'Y') Then
851
852 --
853 PQH_FR_ASSIGNMENT_CHK.chk_primary_affectation(p_person_id, p_effective_date,l_admin_career_id);
854
855 If pqh_fr_utility.is_worker_employee(p_person_id,p_effective_date) Then
856 --
857 hr_assignment_api.update_emp_asg_criteria
858 (
859 p_validate => p_validate
860 ,p_effective_date => p_effective_date
861 ,p_datetrack_update_mode => p_datetrack_update_mode
862 ,p_assignment_id => p_assignment_id
863 ,p_organization_id => p_organization_id
864 ,p_position_id => null
865 ,p_job_id => p_job_id
866 -- Out Variables
867 ,p_people_group_id => l_people_group_id
868 ,p_object_version_number => p_object_version_number -- In OUT
869 ,p_special_ceiling_step_id => l_special_ceiling_step_id
870 ,p_group_name => l_group_name
871 ,p_effective_start_date => p_effective_start_date
872 ,p_effective_end_date => p_effective_end_date
873 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
874 ,p_other_manager_warning => l_other_manager_warning
875 ,p_spp_delete_warning => l_spp_delete_warning
876 ,p_entries_changed_warning => l_entries_changed_warning
877 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
878
879 hr_assignment_api.update_emp_asg(
880 p_validate => p_validate
881 ,p_effective_date => p_effective_date
882 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
883 ,p_assignment_id => p_assignment_id
884 ,p_object_version_number => p_object_version_number
885 ,p_normal_hours => l_normal_hours
886
887 ,p_supervisor_id => p_supervisor_id
888 ,p_assignment_number => p_assignment_number
889 ,p_assignment_status_type_id => p_assignment_status_type_id
890
891 ,p_establishment_id => l_establishment_id
892 ,p_segment23 => p_identifier
893 ,p_segment24 => p_affectation_type
894 ,p_segment25 => p_percent_effected
895 ,p_segment26 => l_admin_career_id
896 ,p_segment27 => p_primary_affectation
897 ,p_segment2 => 'CIVIL'
898 ,p_segment10 => l_fr_emp_category
899
900 -- Following are Out Parameters
901 ,p_cagr_grade_def_id => l_cagr_grade_def_id
902 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
903 ,p_concatenated_segments => l_concatenated_segments
904 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
905 ,p_comment_id => l_comment_id
906 ,p_effective_start_date => p_effective_start_date
907 ,p_effective_end_date => p_effective_end_date
908 ,p_no_managers_warning => l_other_manager_warning
909 ,p_other_manager_warning => l_other_manager_warning2 );
910
911 --
912 ElsIf pqh_fr_utility.is_worker_cwk(p_person_id,p_effective_date) Then
913 --
914
915 hr_assignment_api.update_cwk_asg_criteria
916 (
917 p_validate => p_validate
918 ,p_effective_date => p_effective_date
919 ,p_datetrack_update_mode => p_datetrack_update_mode
920 ,p_assignment_id => p_assignment_id
921 ,p_organization_id => p_organization_id
922 ,p_position_id => null
923 ,p_job_id => p_job_id
924 -- Out Variables
925 ,p_people_group_id => l_people_group_id
926 ,p_object_version_number => p_object_version_number -- In OUT
927 ,p_people_group_name => l_group_name
928 ,p_effective_start_date => p_effective_start_date
929 ,p_effective_end_date => p_effective_end_date
930 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
931 ,p_other_manager_warning => l_other_manager_warning
932 ,p_spp_delete_warning => l_spp_delete_warning
933 ,p_entries_changed_warning => l_entries_changed_warning
934 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
935
936 hr_assignment_api.update_cwk_asg(
937 p_validate => p_validate
938 ,p_effective_date => p_effective_date
939 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
940 ,p_assignment_id => p_assignment_id
941 ,p_object_version_number => p_object_version_number
942 ,p_supervisor_id => p_supervisor_id
943 ,p_assignment_number => p_assignment_number
944 ,p_establishment_id => l_establishment_id
945 ,p_normal_hours => l_normal_hours
946
947 ,p_scl_segment23 => p_identifier
948 ,p_scl_segment24 => p_affectation_type
949 ,p_scl_segment25 => p_percent_effected
950 ,p_scl_segment26 => l_admin_career_id
951 ,p_scl_segment27 => p_primary_affectation
952 ,p_scl_segment2 => 'CIVIL'
953 ,p_scl_segment10 => l_fr_emp_category
954
955 -- Following are Out Parameters
956 ,p_concatenated_segments => l_concatenated_segments
957 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
958 ,p_comment_id => l_comment_id
959 ,p_effective_start_date => p_effective_start_date
960 ,p_effective_end_date => p_effective_end_date
961 ,p_no_managers_warning => l_other_manager_warning
962 ,p_other_manager_warning => l_other_manager_warning2
963 ,p_org_now_no_manager_warning => l_other_manager_warning2
964 ,p_hourly_salaried_warning => l_other_manager_warning2 );
965
966
967
968 --
969 End If;
970
971
972
973 -- If above updates are successful then update the primary assignment with affectation
974 -- detais
975
976 update_primary_asg_affectation
977 (
978 p_validate => p_validate,
979 p_assignment_id => l_admin_career_id,
980 p_effective_date => p_effective_date,
981 p_primary_affectation => p_primary_affectation,
982 p_organization_id => p_organization_id,
983 p_job_id => p_job_id,
984 p_position_id => p_position_id,
985 p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
986 p_object_version_number => l_p_asg_ovn,
987 p_person_id => p_person_id,
988 p_supervisor_id => p_supervisor_id
989 ) ;
990 --
991 ElsIf(l_old_primary_affectation='Y' and p_primary_affectation = 'N') Then
992 --
993
994 update_primary_asg_affectation
995 (
996 p_validate => p_validate,
997 p_assignment_id => l_admin_career_id,
998 p_effective_date => p_effective_date,
999 p_primary_affectation => l_old_primary_affectation,
1000 p_organization_id => p_organization_id,
1001 p_job_id => p_job_id,
1002 p_position_id => null,
1003 p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
1004 p_object_version_number => l_p_asg_ovn,
1005 p_person_id => p_person_id,
1006 p_supervisor_id => p_supervisor_id
1007 ) ;
1008
1009 If (pqh_fr_utility.is_worker_employee(p_person_id,p_effective_date)) then
1010 --
1011 hr_assignment_api.update_emp_asg_criteria
1012 (
1013 p_validate => p_validate
1014 ,p_effective_date => p_effective_date
1015 ,p_datetrack_update_mode => p_datetrack_update_mode
1016 ,p_assignment_id => p_assignment_id
1017 ,p_organization_id => p_organization_id
1018 ,p_position_id => p_position_id
1019 ,p_job_id => p_job_id
1020 -- Out Variables
1021 ,p_people_group_id => l_people_group_id
1022 ,p_object_version_number => p_object_version_number -- In OUT
1023 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1024 ,p_group_name => l_group_name
1025 ,p_effective_start_date => p_effective_start_date
1026 ,p_effective_end_date => p_effective_end_date
1027 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1028 ,p_other_manager_warning => l_other_manager_warning
1029 ,p_spp_delete_warning => l_spp_delete_warning
1030 ,p_entries_changed_warning => l_entries_changed_warning
1031 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
1032
1033 hr_assignment_api.update_emp_asg(
1034 p_validate => p_validate
1035 ,p_effective_date => p_effective_date
1036 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
1037 ,p_assignment_id => p_assignment_id
1038 ,p_object_version_number => p_object_version_number
1039 ,p_normal_hours => l_normal_hours
1040
1041 ,p_supervisor_id => p_supervisor_id
1042 ,p_assignment_number => p_assignment_number
1043 ,p_assignment_status_type_id => p_assignment_status_type_id
1044
1045 ,p_establishment_id => l_establishment_id
1046 ,p_segment23 => p_identifier
1047 ,p_segment24 => p_affectation_type
1048 ,p_segment25 => p_percent_effected
1049 ,p_segment26 => l_admin_career_id
1050 ,p_segment27 => p_primary_affectation
1051 ,p_segment2 => 'CIVIL'
1052 ,p_segment10 => l_fr_emp_category
1053
1054 -- Following are Out Parameters
1055 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1056 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1057 ,p_concatenated_segments => l_concatenated_segments
1058 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1059 ,p_comment_id => l_comment_id
1060 ,p_effective_start_date => p_effective_start_date
1061 ,p_effective_end_date => p_effective_end_date
1062 ,p_no_managers_warning => l_other_manager_warning
1063 ,p_other_manager_warning => l_other_manager_warning2 );
1064 --
1065 ElsIf pqh_fr_utility.is_worker_cwk(p_person_id,p_effective_date) Then
1066 --
1067 hr_assignment_api.update_cwk_asg_criteria
1068 (
1069 p_validate => p_validate
1070 ,p_effective_date => p_effective_date
1071 ,p_datetrack_update_mode => p_datetrack_update_mode
1072 ,p_assignment_id => p_assignment_id
1073 ,p_organization_id => p_organization_id
1074 ,p_position_id => p_position_id
1075 ,p_job_id => p_job_id
1076 -- Out Variables
1077 ,p_people_group_id => l_people_group_id
1078 ,p_object_version_number => p_object_version_number -- In OUT
1079 ,p_people_group_name => l_group_name
1080 ,p_effective_start_date => p_effective_start_date
1081 ,p_effective_end_date => p_effective_end_date
1082 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1083 ,p_other_manager_warning => l_other_manager_warning
1084 ,p_spp_delete_warning => l_spp_delete_warning
1085 ,p_entries_changed_warning => l_entries_changed_warning
1086 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
1087
1088 hr_assignment_api.update_cwk_asg(
1089 p_validate => p_validate
1090 ,p_effective_date => p_effective_date
1091 ,p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id)
1092 ,p_assignment_id => p_assignment_id
1093 ,p_object_version_number => p_object_version_number
1094 ,p_normal_hours => l_normal_hours
1095
1096 ,p_supervisor_id => p_supervisor_id
1097 ,p_assignment_number => p_assignment_number
1098 ,p_establishment_id => l_establishment_id
1099 ,p_scl_segment23 => p_identifier
1100 ,p_scl_segment24 => p_affectation_type
1101 ,p_scl_segment25 => p_percent_effected
1102 ,p_scl_segment26 => l_admin_career_id
1103 ,p_scl_segment27 => p_primary_affectation
1104 ,p_scl_segment2 => 'CIVIL'
1105 ,p_scl_segment10 => l_fr_emp_category
1106
1107 -- Following are Out Parameters
1108 ,p_concatenated_segments => l_concatenated_segments
1109 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1110 ,p_comment_id => l_comment_id
1111 ,p_effective_start_date => p_effective_start_date
1112 ,p_effective_end_date => p_effective_end_date
1113 ,p_no_managers_warning => l_other_manager_warning
1114 ,p_other_manager_warning => l_other_manager_warning2
1115 ,p_org_now_no_manager_warning => l_other_manager_warning2
1116 ,p_hourly_salaried_warning => l_other_manager_warning2 );
1117
1118 --
1119 End if;
1120
1121 -- If above updates are successful then update the primary assignment with affectation
1122 -- detais
1123
1124
1125 ---
1126 End if;
1127
1128 --
1129 -- Convert API warning boolean parameter values to specific
1130 -- messages and add them to Multiple Message List
1131 --
1132 --
1133 -- Convert API non-warning boolean parameter values
1134 --
1135 --
1136 -- Derive the API return status value based on whether
1137 -- messages of any type exist in the Multiple Message List.
1138 -- Also disable Multiple Message Detection.
1139 --
1140 hr_utility.set_location(' Leaving:' || l_proc,20);
1141
1142
1143 end update_affectation;
1144 --
1145 -- Employment Terms Update Routine
1146 --
1147 PROCEDURE update_employment_terms
1148 (p_validate IN BOOLEAN DEFAULT FALSE
1149 ,p_datetrack_update_mode IN VARCHAR2
1150 ,p_effective_date IN DATE
1151 ,p_assignment_id IN NUMBER
1152 ,p_establishment_id IN NUMBER
1153 ,p_comments IN VARCHAR2
1154 ,p_assignment_category IN VARCHAR2
1155 ,p_reason_for_parttime IN VARCHAR2
1156 ,p_working_hours_share IN VARCHAR2
1157 ,p_contract_id IN NUMBER
1158 ,p_change_reason IN VARCHAR2
1159 ,p_normal_hours IN NUMBER
1160 ,p_frequency IN VARCHAR2
1161 ,p_soft_coding_keyflex_id OUT NOCOPY NUMBER
1162 ,p_object_version_number IN OUT NOCOPY NUMBER
1163 ,p_effective_start_date OUT NOCOPY DATE
1164 ,p_effective_end_date OUT NOCOPY DATE
1165 ,p_assignment_sequence OUT NOCOPY NUMBER
1166 )
1167 IS
1168 --
1169 --Cursor to fetch Current Assignment
1170 CURSOR csr_asg_information IS
1171 SELECT asg.assignment_type, ast.per_system_status
1172 FROM per_all_assignments_f asg,
1173 per_assignment_status_types ast
1174 WHERE asg.assignment_id = p_assignment_id
1175 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1176 AND asg.assignment_status_type_id = ast.assignment_status_type_id;
1177 --
1178 --Cursor to fetch assignment values to be updated.
1179 CURSOR csr_update_admin_career(p_effective_end_date DATE
1180 ,p_employment_category VARCHAR2
1181 ,p_contract_id NUMBER
1182 ,p_reason_for_parttime VARCHAR2
1183 ,p_comments VARCHAR2) IS
1184 SELECT asg.assignment_id,
1185 asg.object_version_number,
1186 asg.soft_coding_keyflex_id,
1187 asg.grade_ladder_pgm_id,
1188 asg.grade_id,
1189 scl.segment10 "Employee Category",
1190 asg.effective_start_date,
1191 asg.effective_end_date,
1192 'ET',
1193 asg.employment_category,
1194 asg.contract_id,
1195 asg.establishment_id,
1196 scl.segment9 "Working Hours Share",
1197 scl.segment19 "Reason For Part",
1198 scl.segment20 "Comments"
1199 FROM per_all_assignments_f asg,
1200 hr_soft_coding_keyflex scl
1201 WHERE asg.assignment_id = p_assignment_id
1202 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
1203 AND asg.effective_start_date >= p_effective_end_date
1204 AND NVL(asg.employment_category,p_employment_category) = p_employment_category
1205 AND NVL(asg.contract_id,p_contract_id) = p_contract_id
1206 AND NVL(asg.establishment_id,p_establishment_id) = p_establishment_id
1207 AND NVL(scl.segment9,p_working_hours_share) = p_working_hours_share
1208 AND NVL(scl.segment19,p_reason_for_parttime) = p_reason_for_parttime
1209 AND NVL(scl.segment20,p_comments) = p_comments;
1210 --
1211 --Cursor to fetch Working Hours from Corps for Fonctionaire else from Estab Info for Non Titulaires.
1212 CURSOR csr_normal_working_hour IS
1213 SELECT normal_hours hours, normal_hours_frequency frequency
1214 FROM pqh_corps_definitions
1215 WHERE ben_pgm_id = (SELECT grade_ladder_pgm_id
1216 FROM per_all_assignments_f
1217 WHERE assignment_id = p_assignment_id
1218 AND p_effective_date BETWEEN effective_start_date AND effective_end_date)
1219 UNION
1220 SELECT fnd_number.canonical_to_number (org_information4) hours, 'M' frequency
1221 FROM hr_organization_information_v
1222 WHERE org_information_context = 'FR_ESTAB_INFO'
1223 AND organization_id = p_establishment_id;
1224 --
1225 --Variable Declaration
1226 l_asg_type VARCHAR2(10);
1227 l_asg_status VARCHAR2(30);
1228 l_rec_wrk_type csr_normal_working_hour%ROWTYPE;
1229 l_frequency VARCHAR2(30);
1230 l_wrk_hours NUMBER;
1231 l_validate BOOLEAN;
1232 l_no_managers_warning BOOLEAN;
1233 l_other_manager_warning BOOLEAN;
1234 l_other_manager_warning2 BOOLEAN;
1235 l_hourly_salaried_warning BOOLEAN;
1236 l_soft_coding_keyflex_id NUMBER;
1237 l_cagr_grade_def_id NUMBER;
1238 l_cagr_concatenated_segments VARCHAR2(1000);
1239 l_concatenated_segments VARCHAR2(1000);
1240 l_comment_id NUMBER;
1241 l_effective_start_date DATE;
1242 l_effective_end_date DATE;
1243 l_ovn NUMBER;
1244 l_datetrack_mode VARCHAR2(100);
1245 --Variables for IN/OUT parameters
1246 l_object_version_number NUMBER;
1247 l_assignment_id NUMBER;
1248 l_admin_career_id NUMBER;
1249 --Common info variables
1250 l_people_group_id NUMBER;
1251 l_establishment_id NUMBER;
1252 l_fr_emp_category VARCHAR2(1000);
1253 l_special_ceiling_step_id NUMBER;
1254 l_group_name VARCHAR2(1000);
1255 l_org_now_no_manager_warning BOOLEAN;
1256 l_spp_delete_warning BOOLEAN;
1257 l_entries_changed_warning VARCHAR2(1000);
1258 l_tax_district_changed_warning BOOLEAN;
1259 --
1260 BEGIN
1261 --
1262 OPEN csr_asg_information;
1263 FETCH csr_asg_information INTO l_asg_type, l_asg_status;
1264 CLOSE csr_asg_information;
1265 IF UPPER(l_asg_status) NOT LIKE '%ACTIVE%' THEN
1266 FND_MESSAGE.set_name('PQH','FR_PQH_NO_EMPLOYTERM_UPDT');
1267 FND_MESSAGE.raise_error;
1268 END IF;
1269
1270 OPEN csr_normal_working_hour;
1271 FETCH csr_normal_working_hour INTO l_rec_wrk_type;
1272 CLOSE csr_normal_working_hour;
1273 --
1274 --Logic for calculating the workign hours.
1275 l_frequency := NULL;
1276 l_wrk_hours := NULL;
1277 IF l_rec_wrk_type.hours IS NOT NULL AND p_assignment_category <> 'IT' THEN
1278 l_wrk_hours := ROUND((l_rec_wrk_type.hours*p_working_hours_share)/100,3);
1279 l_frequency := l_rec_wrk_type.frequency;
1280 ELSE
1281 l_wrk_hours := p_normal_hours;
1282 l_frequency := p_frequency;
1283 END IF;
1284 --
1285 /*
1286 Mappings
1287 update_emp_asg_criteria
1288 -p_employment_category -> p_assignment_category
1289
1290 update_emp_asg
1291 -p_establishment_id
1292 -p_comments - scl.20
1293 -p_reason_for_parttime - scl.19
1294 -p_working_hours_share - scl.9
1295 -p_contract_id
1296 -p_change_reason
1297 */
1298 --
1299 IF l_asg_type = 'E' THEN
1300 --Employee Assignment
1301 hr_assignment_api.update_emp_asg_criteria
1302 (p_validate => p_validate
1303 ,p_effective_date => p_effective_date
1304 ,p_datetrack_update_mode => p_datetrack_update_mode
1305 ,p_assignment_id => p_assignment_id
1306 ,p_employment_category => p_assignment_category
1307 --Out Variables
1308 ,p_people_group_id => l_people_group_id
1309 ,p_object_version_number => p_object_version_number -- IN OUT
1310 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1311 ,p_group_name => l_group_name
1312 ,p_effective_start_date => p_effective_start_date
1313 ,p_effective_end_date => p_effective_end_date
1314 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1315 ,p_other_manager_warning => l_other_manager_warning
1316 ,p_spp_delete_warning => l_spp_delete_warning
1317 ,p_entries_changed_warning => l_entries_changed_warning
1318 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
1319 --
1320 l_datetrack_mode := pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',p_assignment_id);
1321 --
1322 hr_assignment_api.update_emp_asg
1323 (p_validate => p_validate
1324 ,p_effective_date => p_effective_date
1325 ,p_datetrack_update_mode => l_datetrack_mode
1326 ,p_assignment_id => p_assignment_id
1327 ,p_object_version_number => p_object_version_number
1328 ,p_segment17 => p_change_reason
1329 ,p_contract_id => p_contract_id
1330 ,p_establishment_id => p_establishment_id
1331 ,p_segment20 => p_comments
1332 ,p_segment19 => p_reason_for_parttime
1333 ,p_segment9 => p_working_hours_share
1334 ,p_normal_hours => l_wrk_hours
1335 ,p_frequency => l_frequency
1336 ,p_segment2 => 'CIVIL'
1337 --Following are Out Parameters
1338 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1339 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1340 ,p_concatenated_segments => l_concatenated_segments
1341 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1342 ,p_comment_id => l_comment_id
1343 ,p_effective_start_date => p_effective_start_date
1344 ,p_effective_end_date => p_effective_end_date
1345 ,p_no_managers_warning => l_other_manager_warning
1346 ,p_other_manager_warning => l_other_manager_warning2 );
1347 /*
1348 If Employment Terms record is updated will have to keep the Career Information
1349 record in Sync with Employment Terms record: Steps as follows
1350 1. Find the all Assignment records whose effective start date > the p_effective_end_date
1351 2. For all those assignment records compare career details with current rec career detial
1352 If It matches update Employment Terms Information for those records
1353 */
1354 hr_utility.set_location(' Before Loop ' , 10);
1355 --
1356 FOR l_asg_rec in csr_update_admin_career(p_effective_end_date,p_assignment_category,-1
1357 ,NVL(p_reason_for_parttime,-1),NVL(p_comments,-1))
1358 LOOP
1359 --
1360 l_ovn := l_asg_rec.object_version_number;
1361 hr_assignment_api.update_emp_asg_criteria
1362 (p_validate => p_validate
1363 ,p_effective_date => l_asg_rec.effective_start_date
1364 ,p_datetrack_update_mode => 'CORRECTION'
1365 ,p_assignment_id => l_asg_rec.assignment_id
1366 ,p_employment_category => p_assignment_category
1367 --Out Variables
1368 ,p_people_group_id => l_people_group_id
1369 ,p_object_version_number => l_ovn -- In OUT
1370 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1371 ,p_group_name => l_group_name
1372 ,p_effective_start_date => p_effective_start_date
1373 ,p_effective_end_date => p_effective_end_date
1374 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1375 ,p_other_manager_warning => l_other_manager_warning
1376 ,p_spp_delete_warning => l_spp_delete_warning
1377 ,p_entries_changed_warning => l_entries_changed_warning
1378 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
1379 --
1380 l_datetrack_mode := 'CORRECTION';
1381 --
1382 hr_assignment_api.update_emp_asg
1383 (p_validate => p_validate
1384 ,p_effective_date => l_asg_rec.effective_start_date
1385 ,p_datetrack_update_mode => l_datetrack_mode
1386 ,p_assignment_id => l_asg_rec.assignment_id
1387 ,p_object_version_number => l_ovn
1388 ,p_segment17 => p_change_reason
1389 ,p_contract_id => p_contract_id
1390 ,p_establishment_id => p_establishment_id
1391 ,p_segment20 => p_comments
1392 ,p_segment19 => p_reason_for_parttime
1393 ,p_segment9 => p_working_hours_share
1394 ,p_normal_hours => l_wrk_hours
1395 ,p_frequency => l_frequency
1396 ,p_segment2 => 'CIVIL'
1397 --Out Parameters
1398 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1399 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1400 ,p_concatenated_segments => l_concatenated_segments
1401 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1402 ,p_comment_id => l_comment_id
1403 ,p_effective_start_date => p_effective_start_date
1404 ,p_effective_end_date => p_effective_end_date
1405 ,p_no_managers_warning => l_other_manager_warning
1406 ,p_other_manager_warning => l_other_manager_warning2);
1407 --
1408 END LOOP;
1409 --
1410 ELSIF l_asg_type = 'C' THEN
1411 --
1412 hr_assignment_api.update_cwk_asg_criteria
1413 (p_validate => p_validate
1414 ,p_effective_date => p_effective_date
1415 ,p_datetrack_update_mode => p_datetrack_update_mode
1416 ,p_assignment_id => p_assignment_id
1417 --,p_employment_category => p_assignment_category
1418 --Out Variables
1419 ,p_people_group_id => l_people_group_id
1420 ,p_object_version_number => p_object_version_number -- In OUT
1421 ,p_people_group_name => l_group_name
1422 ,p_effective_start_date => p_effective_start_date
1423 ,p_effective_end_date => p_effective_end_date
1424 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1425 ,p_other_manager_warning => l_other_manager_warning
1426 ,p_spp_delete_warning => l_spp_delete_warning
1427 ,p_entries_changed_warning => l_entries_changed_warning
1428 ,p_tax_district_changed_warning => l_tax_district_changed_warning);
1429 --
1430 hr_assignment_api.update_cwk_asg
1431 (p_validate => p_validate
1432 ,p_effective_date => p_effective_date
1433 ,p_datetrack_update_mode => p_datetrack_update_mode
1434 ,p_assignment_id => p_assignment_id
1435 ,p_object_version_number => p_object_version_number
1436 ,p_change_reason => p_change_reason
1437 --,p_contract_id => p_contract_id
1438 ,p_establishment_id => p_establishment_id
1439 ,p_scl_segment20 => p_comments
1440 ,p_scl_segment19 => p_reason_for_parttime
1441 ,p_scl_segment9 => p_working_hours_share
1442 ,p_normal_hours => l_wrk_hours
1443 ,p_frequency => l_frequency
1444 ,p_scl_segment2 => 'CIVIL'
1445 --Out Parameters
1446 ,p_concatenated_segments => l_concatenated_segments
1447 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1448 ,p_comment_id => l_comment_id
1449 ,p_effective_start_date => p_effective_start_date
1450 ,p_effective_end_date => p_effective_end_date
1451 ,p_no_managers_warning => l_other_manager_warning
1452 ,p_other_manager_warning => l_other_manager_warning2
1453 ,p_org_now_no_manager_warning => l_other_manager_warning2
1454 ,p_hourly_salaried_warning => l_other_manager_warning2);
1455 --
1456 END IF;
1457 --
1458 END update_employment_terms;
1459 --
1460 --
1461 PROCEDURE update_administrative_career (
1462 p_validate IN BOOLEAN DEFAULT FALSE,
1463 p_datetrack_update_mode IN VARCHAR2,
1464 p_effective_date IN DATE,
1465 p_assignment_id IN NUMBER,
1466 p_corps_id IN NUMBER,
1467 p_grade_id IN NUMBER,
1468 p_step_id IN NUMBER,
1469 p_progression_speed IN VARCHAR2,
1470 p_personal_gross_index IN VARCHAR2,
1471 p_employee_category IN VARCHAR2,
1472 p_soft_coding_keyflex_id OUT NOCOPY NUMBER,
1473 p_object_version_number IN OUT NOCOPY NUMBER,
1474 p_effective_start_date OUT NOCOPY DATE,
1475 p_effective_end_date OUT NOCOPY DATE,
1476 p_assignment_sequence OUT NOCOPY NUMBER
1477 )
1478 IS
1479 l_validate BOOLEAN;
1480 l_no_managers_warning BOOLEAN;
1481 l_other_manager_warning BOOLEAN;
1482 l_other_manager_warning2 BOOLEAN;
1483 l_hourly_salaried_warning BOOLEAN;
1484 l_soft_coding_keyflex_id NUMBER;
1485 l_cagr_grade_def_id NUMBER;
1486 l_cagr_concatenated_segments VARCHAR2 (1000);
1487 l_concatenated_segments VARCHAR2 (1000);
1488 l_comment_id NUMBER;
1489 l_effective_start_date DATE;
1490 l_effective_end_date DATE;
1491 l_ovn NUMBER;
1492 l_establishment_id NUMBER DEFAULT hr_api.g_number;
1493 l_datetrack_mode VARCHAR2 (100);
1494 l_normal_hours NUMBER;
1495 l_frequency VARCHAR2 (30);
1496 --
1497 -- Variables for IN/OUT parameters
1498 l_object_version_number NUMBER;
1499 l_assignment_id NUMBER;
1500 l_admin_career_id NUMBER;
1501 -- common info variables
1502 l_people_group_id NUMBER;
1503 l_fr_emp_category VARCHAR2 (1000);
1504 l_special_ceiling_step_id NUMBER;
1505 l_group_name VARCHAR2 (1000);
1506 l_org_now_no_manager_warning BOOLEAN;
1507 l_spp_delete_warning BOOLEAN;
1508 l_entries_changed_warning VARCHAR2 (1000);
1509 l_tax_district_changed_warning BOOLEAN;
1510
1511 CURSOR csr_placement_info
1512 IS
1513 SELECT placement_id, object_version_number ovn, information3,
1514 information4, step_id
1515 FROM per_spinal_point_placements_f
1516 WHERE assignment_id = p_assignment_id
1517 AND p_effective_date BETWEEN effective_start_date
1518 AND effective_end_date;
1519
1520 --
1521 CURSOR csr_asg_information
1522 IS
1523 SELECT assignment_type
1524 FROM per_all_assignments_f
1525 WHERE assignment_id = p_assignment_id
1526 AND p_effective_date BETWEEN effective_start_date
1527 AND effective_end_date;
1528
1529 --
1530 CURSOR csr_get_corps_info
1531 IS
1532 SELECT normal_hours, normal_hours_frequency
1533 FROM pqh_corps_definitions
1534 WHERE ben_pgm_id = p_corps_id
1535 AND p_effective_date BETWEEN date_from
1536 AND NVL (date_to, hr_general.end_of_time);
1537
1538 --
1539 CURSOR csr_old_assign_record
1540 IS
1541 SELECT grade_ladder_pgm_id, grade_id, normal_hours, frequency,
1542 segment10 employee_category
1543 FROM per_all_assignments_f, hr_soft_coding_keyflex
1544 WHERE assignment_id = p_assignment_id
1545 AND p_effective_date BETWEEN effective_start_date
1546 AND effective_end_date
1547 AND per_all_assignments_f.soft_coding_keyflex_id = hr_soft_coding_keyflex.soft_coding_keyflex_id(+);
1548
1549 --
1550 CURSOR csr_work_hrs_share IS
1551 SELECT nvl(scl.segment9,100) work_hour_share,
1552 nvl(employment_category,'CF') employment_category,
1553 normal_hours,
1554 frequency
1555 FROM per_all_assignments_f asg,
1556 hr_soft_coding_keyflex scl
1557 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1558 AND assignment_id = p_assignment_id
1559 AND p_effective_date between effective_start_date and effective_end_date
1560 AND primary_flag = 'Y';
1561
1562 lr_old_assign_record csr_old_assign_record%ROWTYPE;
1563 placement_record csr_placement_info%ROWTYPE;
1564 l_asg_type VARCHAR2 (10);
1565 l_work_hour_share NUMBER;
1566 l_agreed_normal_hrs NUMBER;
1567 l_emp_cat VARCHAR2(10);
1568 l_it_frequency VARCHAR2(10);
1569 -- added for bug 6334604
1570 l_business_group_id NUMBER;
1571
1572 BEGIN
1573 -- getting the business group id
1574 l_business_group_id := hr_general.get_business_group_id;
1575 --
1576 OPEN csr_asg_information;
1577
1578 FETCH csr_asg_information
1579 INTO l_asg_type;
1580
1581 CLOSE csr_asg_information;
1582
1583 --
1584 OPEN csr_get_corps_info;
1585
1586 FETCH csr_get_corps_info
1587 INTO l_normal_hours, l_frequency;
1588
1589 CLOSE csr_get_corps_info;
1590
1591 --
1592 OPEN csr_old_assign_record;
1593
1594 FETCH csr_old_assign_record
1595 INTO lr_old_assign_record;
1596
1597 CLOSE csr_old_assign_record;
1598
1599 OPEN csr_work_hrs_share;
1600
1601 FETCH csr_work_hrs_share
1602 INTO l_work_hour_share,l_emp_cat,l_agreed_normal_hrs,l_it_frequency;
1603
1604 CLOSE csr_work_hrs_share;
1605
1606 if l_emp_cat <> 'IT' then
1607 l_normal_hours := (l_normal_hours * l_work_hour_share)/100;
1608 else
1609 l_normal_hours := l_agreed_normal_hrs;
1610 l_frequency := l_it_frequency;
1611 end if;
1612
1613
1614 IF (l_asg_type = 'E')
1615 THEN
1616 -- Employee Assignment
1617 IF ( NVL (lr_old_assign_record.grade_ladder_pgm_id, -1) <>
1618 NVL (p_corps_id,
1619 -1)
1620 OR NVL (lr_old_assign_record.grade_id, -1) <> NVL (p_grade_id,
1621 -1)
1622 )
1623 THEN
1624 hr_assignment_api.update_emp_asg_criteria
1625 (p_validate => p_validate,
1626 p_effective_date => p_effective_date,
1627 p_datetrack_update_mode => p_datetrack_update_mode,
1628 p_assignment_id => p_assignment_id,
1629 p_grade_ladder_pgm_id => p_corps_id,
1630 p_grade_id => p_grade_id
1631 -- Out Variables
1632 ,
1633 p_people_group_id => l_people_group_id,
1634 p_object_version_number => p_object_version_number
1635 -- In OUT
1636 ,
1637 p_special_ceiling_step_id => l_special_ceiling_step_id,
1638 p_group_name => l_group_name,
1639 p_effective_start_date => p_effective_start_date,
1640 p_effective_end_date => p_effective_end_date,
1641 p_org_now_no_manager_warning => l_org_now_no_manager_warning,
1642 p_other_manager_warning => l_other_manager_warning,
1643 p_spp_delete_warning => l_spp_delete_warning,
1644 p_entries_changed_warning => l_entries_changed_warning,
1645 p_tax_district_changed_warning => l_tax_district_changed_warning
1646 );
1647 END IF;
1648
1649 l_datetrack_mode :=
1650 pqh_fr_utility.get_datetrack_mode (p_effective_date,
1651 'PER_ALL_ASSIGNMENTS_F',
1652 'ASSIGNMENT_ID',
1653 p_assignment_id
1654 );
1655
1656 IF ( NVL (lr_old_assign_record.normal_hours, -1) <>
1657 NVL (l_normal_hours,
1658 -1)
1659 OR NVL (lr_old_assign_record.frequency, hr_api.g_varchar2) <>
1660 NVL (l_frequency, hr_api.g_varchar2)
1661 OR NVL (lr_old_assign_record.employee_category,
1662 hr_api.g_varchar2) <>
1663 NVL (p_employee_category, hr_api.g_varchar2)
1664 )
1665 THEN
1666 hr_assignment_api.update_emp_asg
1667 (p_validate => p_validate,
1668 p_effective_date => p_effective_date,
1669 p_datetrack_update_mode => l_datetrack_mode,
1670 p_assignment_id => p_assignment_id,
1671 p_object_version_number => p_object_version_number,
1672 p_segment10 => p_employee_category,
1673 p_segment2 => 'CIVIL',
1674 p_normal_hours => l_normal_hours,
1675 p_frequency => l_frequency
1676 -- Following are Out Parameters
1677 ,
1678 p_cagr_grade_def_id => l_cagr_grade_def_id,
1679 p_cagr_concatenated_segments => l_cagr_concatenated_segments,
1680 p_concatenated_segments => l_concatenated_segments,
1681 p_soft_coding_keyflex_id => p_soft_coding_keyflex_id,
1682 p_comment_id => l_comment_id,
1683 p_effective_start_date => p_effective_start_date,
1684 p_effective_end_date => p_effective_end_date,
1685 p_no_managers_warning => l_other_manager_warning,
1686 p_other_manager_warning => l_other_manager_warning2
1687 );
1688 END IF;
1689 --
1690 ELSIF l_asg_type = 'C'
1691 THEN
1692 -- CWK Worker
1693 IF (NVL (lr_old_assign_record.employee_category, hr_api.g_varchar2) <>
1694 NVL (p_employee_category, hr_api.g_varchar2)
1695 )
1696 THEN
1697 hr_assignment_api.update_cwk_asg
1698 (p_validate => p_validate,
1699 p_effective_date => p_effective_date,
1700 p_datetrack_update_mode => p_datetrack_update_mode,
1701 p_assignment_id => p_assignment_id,
1702 p_object_version_number => p_object_version_number,
1703 p_scl_segment10 => p_employee_category,
1704 p_scl_segment2 => 'CIVIL',
1705 p_establishment_id => l_establishment_id
1706 -- Following are Out Parameters
1707 ,
1708 p_concatenated_segments => l_concatenated_segments,
1709 p_soft_coding_keyflex_id => p_soft_coding_keyflex_id,
1710 p_comment_id => l_comment_id,
1711 p_effective_start_date => p_effective_start_date,
1712 p_effective_end_date => p_effective_end_date,
1713 p_no_managers_warning => l_other_manager_warning,
1714 p_other_manager_warning => l_other_manager_warning2,
1715 p_org_now_no_manager_warning => l_other_manager_warning2,
1716 p_hourly_salaried_warning => l_other_manager_warning2
1717 );
1718 hr_assignment_api.update_cwk_asg_criteria
1719 (p_validate => p_validate,
1720 p_effective_date => p_effective_date,
1721 p_datetrack_update_mode => p_datetrack_update_mode,
1722 p_assignment_id => p_assignment_id
1723 -- Out Variables
1724 ,
1725 p_people_group_id => l_people_group_id,
1726 p_object_version_number => p_object_version_number
1727 -- In OUT
1728 ,
1729 p_people_group_name => l_group_name,
1730 p_effective_start_date => p_effective_start_date,
1731 p_effective_end_date => p_effective_end_date,
1732 p_org_now_no_manager_warning => l_org_now_no_manager_warning,
1733 p_other_manager_warning => l_other_manager_warning,
1734 p_spp_delete_warning => l_spp_delete_warning,
1735 p_entries_changed_warning => l_entries_changed_warning,
1736 p_tax_district_changed_warning => l_tax_district_changed_warning
1737 );
1738 END IF;
1739 -- Update cwk_asg
1740 END IF;
1741
1742 /*
1743
1744 hr_sp_placement_api.update_spp(
1745 p_validate => p_validate
1746 ,p_effective_date => p_effective_date
1747 ,p_datetrack_update_mode => p_datetrack_update_mode
1748 ,p_object_version_number => p_object_version_number
1749 ,p_placement_id => p_object_version_number
1750 ,p_information2 => p_personal_gross_index
1751 ,p_effective_start_date => p_effective_start_date
1752 ,p_effective_end_date => p_effective_end_date);
1753 */
1754 OPEN csr_placement_info;
1755
1756 FETCH csr_placement_info
1757 INTO placement_record;
1758
1759 CLOSE csr_placement_info;
1760
1761 --Modified condition below. Added p_step IS NOT NULL by deenath 12/13/04
1762 IF p_step_id IS NOT NULL AND placement_record.placement_id IS NOT NULL
1763 -- IF placement_record.placement_id IS NOT NULL
1764 THEN
1765 l_datetrack_mode :=
1766 pqh_fr_utility.get_datetrack_mode
1767 (p_effective_date,
1768 'PER_SPINAL_POINT_PLACEMENTS_F',
1769 'PLACEMENT_ID',
1770 placement_record.placement_id
1771 );
1772
1773 IF ( NVL (placement_record.information3, hr_api.g_varchar2) <>
1774 NVL (p_progression_speed, hr_api.g_varchar2)
1775 OR NVL (placement_record.information4, hr_api.g_varchar2) <>
1776 NVL (p_personal_gross_index, hr_api.g_varchar2)
1777 OR NVL (placement_record.step_id, -1) <> NVL (p_step_id, -1)
1778 )
1779 THEN
1780 hr_sp_placement_api.update_spp
1781 (p_effective_date => p_effective_date,
1782 p_placement_id => placement_record.placement_id,
1783 p_object_version_number => placement_record.ovn,
1784 p_datetrack_mode => l_datetrack_mode,
1785 p_effective_start_date => p_effective_start_date,
1786 p_effective_end_date => p_effective_end_date,
1787 p_validate => p_validate,
1788 p_information3 => p_progression_speed,
1789 p_information4 => p_personal_gross_index,
1790 p_step_id => p_step_id
1791 );
1792 END IF;
1793 END IF;
1794 --Replaced ELSE clause with below IF Condition - deenath 12/13/04.
1795 IF p_step_id IS NOT NULL AND placement_record.placement_id IS NULL THEN
1796 -- replaced function with variable l_business_group_id
1797 -- for bug 6334604
1798 hr_sp_placement_api.create_spp
1799 (p_effective_date => p_effective_date,
1800 p_validate => p_validate,
1801 p_information3 => p_progression_speed,
1802 p_information4 => p_personal_gross_index,
1803 p_step_id => p_step_id,
1804 p_assignment_id => p_assignment_id,
1805 p_business_group_id => l_business_group_id,
1806 p_placement_id => placement_record.placement_id,
1807 p_object_version_number => p_object_version_number,
1808 p_effective_start_date => p_effective_start_date,
1809 p_effective_end_date => p_effective_end_date
1810 );
1811 END IF;
1812 END update_administrative_career;
1813
1814 procedure terminate_affectation
1815 (p_validate in boolean
1816 ,p_assignment_id in number
1817 ,p_effective_date in date
1818 ,p_assignment_status_type_id in number
1819 ,p_primary_affectation in varchar2 default 'N'
1820 ,p_group_name out nocopy varchar2
1821 ,p_object_version_number in out nocopy number
1822 ,p_effective_start_date out nocopy date
1823 ,p_effective_end_date out nocopy date
1824 )
1825 IS
1826
1827 l_validate boolean;
1828 l_no_managers_warning boolean;
1829 l_other_manager_warning boolean;
1830 l_hourly_salaried_warning boolean;
1831 l_soft_coding_keyflex_id number;
1832 l_cagr_grade_def_id number;
1833 l_cagr_concatenated_segments varchar2(1000);
1834 l_concatenated_segments varchar2(1000);
1835 l_comment_id number;
1836 l_position_id number;
1837 l_frequency varchar2(30);
1838 l_p_normal_hours number;
1839 l_business_group_id number;
1840 l_person_id number;
1841 l_asg_future_changes_warning boolean;
1842 l_entries_changed_warning varchar2(2000);
1843 l_pay_proposal_warning boolean;
1844
1845
1846 --
1847 -- Variables for IN/OUT parameters
1848 l_obj_ver_no number;
1849 l_object_version_number number;
1850 l_assignment_id number;
1851
1852 -- common info variables
1853 l_people_group_id number ;
1854 l_establishment_id number;
1855 l_fr_emp_category varchar2(1000);
1856 l_admin_career_id number;
1857 l_p_asg_ovn number;
1858 l_organization_id number;
1859 l_job_id number;
1860
1861
1862
1863 --
1864 -- Other variables
1865 l_proc varchar2(72) := g_package ||'terminate_affectation';
1866 l_temp varchar2(10);
1867
1868 -- Cursors to Fetch all primary assignment's People group Segements, the same will be passed
1869 -- to the secondary assingment.
1870
1871
1872 cursor info_admin_career_id is
1873 Select person_id, scl.segment26 admin_career_id, object_version_number
1874 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
1875 where assignment_id = p_assignment_id
1876 and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1877 and p_effective_date between effective_start_date and effective_end_date;
1878
1879 cursor common_info_csr (p_person_id in number)is
1880 Select people_group_id, establishment_id , scl.segment10 FrEmpCategory,
1881 assignment_id,object_version_number,normal_hours,frequency,business_group_id,
1882 organization_id, job_id, position_id
1883 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
1884 where person_id = p_person_id
1885 and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1886 and p_effective_date between effective_start_date and effective_end_date
1887 and primary_flag ='Y';
1888
1889
1890 -- Note : CAGR_GRADE_DEF_ID is not used, assuming that will not be used by the customer, as the functionality is not delivered.
1891 --
1892 l_identifier varchar2(30);
1893
1894 Begin
1895
1896 hr_utility.set_location(' Entering:' || l_proc,10);
1897 hr_utility.set_location(' Parameters' || l_proc,10);
1898 hr_utility.set_location(' p_assignment_status_type_id:' || p_assignment_status_type_id,10);
1899 hr_utility.set_location(' p_primary_affectation:' || p_primary_affectation,10);
1900
1901 --
1902
1903 -- Issue a savepoint
1904 --
1905 savepoint terminate_sec_emp_asg_swi;
1906
1907 --
1908 -- Remember IN OUT parameter IN values
1909 --
1910 l_soft_coding_keyflex_id := null;
1911 --
1912 -- Register Surrogate ID or user key values
1913 --
1914 --
1915 -- Call API
1916 --
1917
1918 -- Fetch common values from Primary assignment
1919 Open info_admin_career_id;
1920 fetch info_admin_career_id into l_person_id,l_admin_career_id,l_obj_ver_no;
1921 Close info_admin_career_id;
1922
1923 Open common_info_csr(l_person_id);
1924 fetch common_info_csr into l_people_group_id,l_establishment_id,
1925 l_fr_emp_category,l_admin_career_id,l_p_asg_ovn,l_p_normal_hours,l_frequency,l_business_group_id,
1926 l_organization_id, l_job_id, l_position_id;
1927 Close common_info_csr;
1928
1929
1930
1931 -- Create Affectation : If Affectation is a Primary, then donot pass Position
1932 -- instead position will be updated on the assignment by using l_admin_career_id = assignment_id
1933 -- on primary assignment
1934 -- else Position details will be copied to Affecation details
1935 --
1936
1937 If (pqh_fr_utility.is_worker_employee(l_person_id,p_effective_date)) Then
1938 --
1939 hr_assignment_api.actual_termination_emp_asg(
1940 p_validate => p_validate
1941 ,p_assignment_id => p_assignment_id
1942 ,p_actual_termination_date => p_effective_date
1943 ,p_assignment_status_type_id => p_assignment_status_type_id
1944
1945 -- Following are Out Parameters
1946 ,p_object_version_number => l_obj_ver_no
1947 ,p_effective_start_date => p_effective_start_date
1948 ,p_effective_end_date => p_effective_end_date
1949 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1950 ,p_entries_changed_warning => l_entries_changed_warning
1951 ,p_pay_proposal_warning => l_pay_proposal_warning
1952 );
1953 --
1954 ElsIf (pqh_fr_utility.is_worker_cwk(l_person_id,p_effective_date)) Then
1955 --
1956 hr_assignment_api.actual_termination_cwk_asg(
1957 p_validate => p_validate
1958 ,p_assignment_id => p_assignment_id
1959 ,p_actual_termination_date => p_effective_date
1960 ,p_assignment_status_type_id => p_assignment_status_type_id
1961
1962 -- Following are Out Parameters
1963
1964 ,p_object_version_number => l_obj_ver_no
1965 ,p_effective_start_date => p_effective_start_date
1966 ,p_effective_end_date => p_effective_end_date
1967 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1968 ,p_entries_changed_warning => l_entries_changed_warning
1969 ,p_pay_proposal_warning => l_pay_proposal_warning
1970 );
1971 --
1972 End if;
1973
1974
1975 --
1976 -- Convert API warning boolean parameter values to specific
1977 -- messages and add them to Multiple Message List
1978 --
1979 --
1980 -- Convert API non-warning boolean parameter values
1981 --
1982 --
1983 -- Derive the API return status value based on whether
1984 -- messages of any type exist in the Multiple Message List.
1985 -- Also disable Multiple Message Detection.
1986 --
1987 update_primary_asg_affectation
1988 (
1989 p_validate => p_validate,
1990 p_assignment_id => l_admin_career_id,
1991 p_effective_date => p_effective_date,
1992 p_primary_affectation => p_primary_affectation,
1993 p_organization_id => l_organization_id,
1994 p_job_id => l_job_id,
1995 p_position_id => NULL,
1996 p_datetrack_update_mode => pqh_fr_utility.get_DateTrack_Mode(p_effective_date,'PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_ID',l_admin_career_id),
1997 p_object_version_number => l_p_asg_ovn,
1998 p_person_id => l_person_id
1999 ) ;
2000
2001 hr_utility.set_location(' Leaving:' || l_proc,20);
2002 --
2003 End terminate_affectation;
2004
2005 procedure suspend_affectation
2006 (p_validate in boolean
2007 ,p_assignment_id in number
2008 ,p_effective_date in date
2009 ,p_assignment_status_type_id in number
2010 ,p_object_version_number in out nocopy number
2011 ,p_effective_start_date out nocopy date
2012 ,p_effective_end_date out nocopy date
2013 )
2014 IS
2015
2016 l_validate boolean;
2017 l_soft_coding_keyflex_id number;
2018 l_cagr_grade_def_id number;
2019 l_cagr_concatenated_segments varchar2(1000);
2020 l_concatenated_segments varchar2(1000);
2021 l_comment_id number;
2022 l_position_id number;
2023 l_frequency varchar2(30);
2024 l_p_normal_hours number;
2025 l_business_group_id number;
2026 l_person_id number;
2027
2028 --
2029 -- Variables for IN/OUT parameters
2030 l_obj_ver_no number;
2031 l_assignment_id number;
2032
2033 -- common info variables
2034 l_fr_emp_category varchar2(1000);
2035 l_admin_career_id number;
2036 l_p_asg_ovn number;
2037 l_organization_id number;
2038 l_job_id number;
2039
2040
2041
2042 --
2043 -- Other variables
2044 l_proc varchar2(72) := g_package ||'suspend_affectation';
2045 l_temp varchar2(10);
2046
2047 -- Cursors to Fetch all primary assignment's People group Segements, the same will be passed
2048 -- to the secondary assingment.
2049
2050
2051 cursor info_admin_career_id is
2052 Select person_id, scl.segment26 admin_career_id, object_version_number
2053 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
2054 where assignment_id = p_assignment_id
2055 and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
2056 and p_effective_date between effective_start_date and effective_end_date;
2057
2058
2059 -- Note : CAGR_GRADE_DEF_ID is not used, assuming that will not be used by the customer, as the functionality is not delivered.
2060 --
2061 l_identifier varchar2(30);
2062
2063 Begin
2064
2065 hr_utility.set_location(' Entering:' || l_proc,10);
2066 hr_utility.set_location(' Parameters' || l_proc,10);
2067 hr_utility.set_location(' p_assignment_status_type_id:' || p_assignment_status_type_id,10);
2068
2069
2070 --
2071
2072 -- Issue a savepoint
2073 --
2074 savepoint suspend_sec_emp_asg_swi;
2075
2076 --
2077 -- Remember IN OUT parameter IN values
2078 --
2079 l_soft_coding_keyflex_id := null;
2080 --
2081 -- Register Surrogate ID or user key values
2082 --
2083 --
2084 -- Call API
2085 --
2086
2087 -- Fetch common values from Primary assignment
2088 Open info_admin_career_id;
2089 fetch info_admin_career_id into l_person_id,l_admin_career_id,l_obj_ver_no;
2090 Close info_admin_career_id;
2091
2092 /* Open common_info_csr(l_person_id);
2093 fetch common_info_csr into l_people_group_id,l_establishment_id,
2094 l_fr_emp_category,l_admin_career_id,l_p_asg_ovn,l_p_normal_hours,l_frequency,l_business_group_id,
2095 l_organization_id, l_job_id, l_position_id;
2096 Close common_info_csr;*/
2097
2098
2099 If (pqh_fr_utility.is_worker_employee(l_person_id,p_effective_date)) Then
2100 --
2101 hr_assignment_api.suspend_emp_asg(
2102 p_validate => p_validate
2103 ,p_assignment_id => p_assignment_id
2104 ,p_effective_date => p_effective_date
2105 ,p_assignment_status_type_id => p_assignment_status_type_id
2106 ,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
2107 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2108 ,p_base_key_column => 'ASSIGNMENT_ID'
2109 ,p_base_key_value => p_assignment_id)
2110 -- Following are Out Parameters
2111 ,p_object_version_number => l_obj_ver_no
2112 ,p_effective_start_date => p_effective_start_date
2113 ,p_effective_end_date => p_effective_end_date
2114 );
2115 --
2116 ElsIf (pqh_fr_utility.is_worker_cwk(l_person_id,p_effective_date)) Then
2117 --
2118 hr_assignment_api.suspend_cwk_asg(
2119 p_validate => p_validate
2120 ,p_assignment_id => p_assignment_id
2121 ,p_effective_date => p_effective_date
2122 ,p_assignment_status_type_id => p_assignment_status_type_id
2123 ,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
2124 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2125 ,p_base_key_column => 'ASSIGNMENT_ID'
2126 ,p_base_key_value => p_assignment_id)
2127 -- Following are Out Parameters
2128 ,p_object_version_number => l_obj_ver_no
2129 ,p_effective_start_date => p_effective_start_date
2130 ,p_effective_end_date => p_effective_end_date
2131 );
2132 --
2133 End if;
2134
2135
2136 --
2137 -- Convert API warning boolean parameter values to specific
2138 -- messages and add them to Multiple Message List
2139 --
2140 --
2141 -- Convert API non-warning boolean parameter values
2142 --
2143 --
2144 -- Derive the API return status value based on whether
2145 -- messages of any type exist in the Multiple Message List.
2146 -- Also disable Multiple Message Detection.
2147 --
2148
2149 hr_utility.set_location(' Leaving:' || l_proc,20);
2150 --
2151 End suspend_affectation;
2152
2153 procedure activate_affectation
2154 (p_validate in boolean
2155 ,p_assignment_id in number
2156 ,p_effective_date in date
2157 ,p_assignment_status_type_id in number
2158 ,p_object_version_number in out nocopy number
2159 ,p_effective_start_date out nocopy date
2160 ,p_effective_end_date out nocopy date
2161 )
2162 IS
2163
2164 l_validate boolean;
2165 l_soft_coding_keyflex_id number;
2166 l_cagr_grade_def_id number;
2167 l_cagr_concatenated_segments varchar2(1000);
2168 l_concatenated_segments varchar2(1000);
2169 l_comment_id number;
2170 l_position_id number;
2171 l_frequency varchar2(30);
2172 l_p_normal_hours number;
2173 l_business_group_id number;
2174 l_person_id number;
2175
2176 --
2177 -- Variables for IN/OUT parameters
2178 l_obj_ver_no number;
2179 l_assignment_id number;
2180
2181 -- common info variables
2182 l_fr_emp_category varchar2(1000);
2183 l_admin_career_id number;
2184 l_p_asg_ovn number;
2185 l_organization_id number;
2186 l_job_id number;
2187
2188
2189
2190 --
2191 -- Other variables
2192 l_proc varchar2(72) := g_package ||'activate_affectation';
2193 l_temp varchar2(10);
2194
2195 -- Cursors to Fetch all primary assignment's People group Segements, the same will be passed
2196 -- to the secondary assingment.
2197
2198 cursor info_admin_career_id is
2199 Select person_id, scl.segment26 admin_career_id, object_version_number
2200 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
2201 where assignment_id = p_assignment_id
2202 and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
2203 and p_effective_date between effective_start_date and effective_end_date;
2204
2205
2206 Cursor assign_percent_affected is
2207 Select nvl(scl.segment25,0) Percenteffected
2208 From per_all_assignments_f assign,
2209 hr_soft_coding_keyflex scl
2210 Where person_id = l_person_id
2211 And assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2212 And assign.primary_flag = 'N'
2213 And p_effective_date Between effective_start_date And effective_end_date
2214 And assign.assignment_status_type_id = 2
2215 And assign.assignment_id = p_assignment_id;
2216
2217
2218
2219 l_identifier varchar2(30);
2220 l_percent_affected varchar2(30);
2221
2222 Begin
2223
2224 hr_utility.set_location(' Entering:' || l_proc,10);
2225 hr_utility.set_location(' Parameters' || l_proc,10);
2226 hr_utility.set_location(' p_assignment_status_type_id:' || p_assignment_status_type_id,10);
2227
2228
2229 --
2230
2231 -- Issue a savepoint
2232 --
2233 savepoint activate_sec_emp_asg_swi;
2234
2235 --
2236 -- Remember IN OUT parameter IN values
2237 --
2238 l_soft_coding_keyflex_id := null;
2239 --
2240 -- Register Surrogate ID or user key values
2241 --
2242 --
2243 -- Call API
2244 --
2245
2246 -- Fetch common values from assignment
2247 Open info_admin_career_id;
2248 fetch info_admin_career_id into l_person_id,l_admin_career_id,l_obj_ver_no;
2249 Close info_admin_career_id;
2250
2251
2252
2253 Open assign_percent_affected ;
2254 fetch assign_percent_affected into l_percent_affected;
2255 Close assign_percent_affected;
2256
2257 pqh_fr_assignment_chk.chk_situation(l_person_id,p_effective_date);
2258 pqh_fr_assignment_chk.chk_percent_affected(l_percent_affected, l_person_id, p_effective_date);
2259
2260
2261 If (pqh_fr_utility.is_worker_employee(l_person_id,p_effective_date)) Then
2262 --
2263 hr_assignment_api.activate_emp_asg(
2264 p_validate => p_validate
2265 ,p_assignment_id => p_assignment_id
2266 ,p_effective_date => p_effective_date
2267 ,p_assignment_status_type_id => p_assignment_status_type_id
2268 ,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
2269 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2270 ,p_base_key_column => 'ASSIGNMENT_ID'
2271 ,p_base_key_value => p_assignment_id)
2272 -- Following are Out Parameters
2273 ,p_object_version_number => l_obj_ver_no
2274 ,p_effective_start_date => p_effective_start_date
2275 ,p_effective_end_date => p_effective_end_date
2276 );
2277 --
2278 ElsIf (pqh_fr_utility.is_worker_cwk(l_person_id,p_effective_date)) Then
2279 --
2280 hr_assignment_api.activate_cwk_asg(
2281 p_validate => p_validate
2282 ,p_assignment_id => p_assignment_id
2283 ,p_effective_date => p_effective_date
2284 ,p_assignment_status_type_id => p_assignment_status_type_id
2285 ,p_datetrack_update_mode => pqh_fr_utility.get_datetrack_mode(p_effective_date => p_effective_date
2286 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2287 ,p_base_key_column => 'ASSIGNMENT_ID'
2288 ,p_base_key_value => p_assignment_id)
2289 -- Following are Out Parameters
2290 ,p_object_version_number => l_obj_ver_no
2291 ,p_effective_start_date => p_effective_start_date
2292 ,p_effective_end_date => p_effective_end_date
2293 );
2294 --
2295 End if;
2296
2297
2298 --
2299 -- Convert API warning boolean parameter values to specific
2300 -- messages and add them to Multiple Message List
2301 --
2302 --
2303 -- Convert API non-warning boolean parameter values
2304 --
2305 --
2306 -- Derive the API return status value based on whether
2307 -- messages of any type exist in the Multiple Message List.
2308 -- Also disable Multiple Message Detection.
2309 --
2310
2311 hr_utility.set_location(' Leaving:' || l_proc,20);
2312 --
2313 End activate_affectation;
2314 --
2315 end pqh_fr_assignment_api;