[Home] [Help]
PACKAGE BODY: APPS.HRHIRAPL
Source
1 PACKAGE BODY hrhirapl AS
2 /* $Header: pehirapl.pkb 120.48.12020000.4 2013/02/04 14:33:20 sclakkar ship $ */
3
4 --
5 ------------------------- BEGIN: employ_applicant --------------------
6 PROCEDURE employ_applicant (p_person_id IN INTEGER
7 ,p_business_group_id IN INTEGER
8 ,p_legislation_code IN VARCHAR2
9 ,p_new_primary_id IN INTEGER
10 ,p_assignment_status_type_id IN INTEGER
11 ,p_user_id IN INTEGER
12 ,p_login_id IN INTEGER
13 ,p_start_date IN DATE
14 ,p_end_of_time IN DATE
15 ,p_current_date IN DATE
16 ,p_update_primary_flag VARCHAR2
17 ,p_employee_number VARCHAR2
18 ,p_set_of_books_id IN INTEGER
19 ,p_emp_apl VARCHAR2
20 ,p_adjusted_svc_date IN DATE
21 ,p_session_date IN DATE -- Bug 3564129
22 -- #2264569
23 ,p_table IN HR_EMPLOYEE_APPLICANT_API.t_ApplTable
24 ) IS
25 /*
26 NAME
27 employ_applicant
28 DESCRIPTION
29 Procedures fired when applicant is hired.
30 PARAMETERS
31 p_business_group_id : Current business group.
32 p_legislation_code : Legislation code.
33 p_new_primary_id : Id of new primary assignment.
34 p_assignment_status_type_id: Current assignment status id.
35 p_user_id : user id
36 p_login_id : Login id of user.
37 p_start_date : Start date.
38 p_end_of_time : Maximum date that can be held by an Oracle system.
39 p_current_date : Today's Date
40 p_update_primary_flag : Flag whether to update the primary assignment or not
41 p_set_of_books_id : Current set of books_id
42 p_emp_apl : Whether EMP_APL or APL.
43 p_session_date : Session Date -- Bug 3564129
44 -- #2264569
45 p_table : PL/SQL table that has information about the type of
46 processing performed to the appl assignment.
47
48 */
49 --
50 v_period_of_service_id INTEGER;
51 p_assignment_id INTEGER;
52 v_tabrows varchar2(4000); -- Bug 3214063
53 l_return_code number; -- #2433154
54 l_return_text varchar2(240); -- #2433154
55 l_delete_warn boolean; -- #2933750
56 --fix for bug 7119614 starts here.
57 cursor c_pgp_segments(l_pg_id number) is
58 select segment1,
59 segment2,
60 segment3,
61 segment4,
62 segment5,
63 segment6,
64 segment7,
65 segment8,
66 segment9,
67 segment10,
68 segment11,
69 segment12,
70 segment13,
71 segment14,
72 segment15,
73 segment16,
74 segment17,
75 segment18,
76 segment19,
77 segment20,
78 segment21,
79 segment22,
80 segment23,
81 segment24,
82 segment25,
83 segment26,
84 segment27,
85 segment28,
86 segment29,
87 segment30
88 from pay_people_groups
89 where people_group_id = l_pg_id;
90 l_pgp_segment1 varchar2(60) ;
91 l_pgp_segment2 varchar2(60) ;
92 l_pgp_segment3 varchar2(60) ;
93 l_pgp_segment4 varchar2(60) ;
94 l_pgp_segment5 varchar2(60) ;
95 l_pgp_segment6 varchar2(60) ;
96 l_pgp_segment7 varchar2(60) ;
97 l_pgp_segment8 varchar2(60) ;
98 l_pgp_segment9 varchar2(60) ;
99 l_pgp_segment10 varchar2(60) ;
100 l_pgp_segment11 varchar2(60) ;
101 l_pgp_segment12 varchar2(60) ;
102 l_pgp_segment13 varchar2(60) ;
103 l_pgp_segment14 varchar2(60) ;
104 l_pgp_segment15 varchar2(60) ;
105 l_pgp_segment16 varchar2(60) ;
106 l_pgp_segment17 varchar2(60) ;
107 l_pgp_segment18 varchar2(60) ;
108 l_pgp_segment19 varchar2(60) ;
109 l_pgp_segment20 varchar2(60) ;
110 l_pgp_segment21 varchar2(60) ;
111 l_pgp_segment22 varchar2(60) ;
112 l_pgp_segment23 varchar2(60) ;
113 l_pgp_segment24 varchar2(60) ;
114 l_pgp_segment25 varchar2(60) ;
115 l_pgp_segment26 varchar2(60) ;
116 l_pgp_segment27 varchar2(60) ;
117 l_pgp_segment28 varchar2(60) ;
118 l_pgp_segment29 varchar2(60) ;
119 l_pgp_segment30 varchar2(60) ;
120 --fix for bug 7119614 ends here.
121 --
122 -- Bug 14596826
123
124 l_primary_asg per_all_assignments_f.assignment_id%type;
125
126 cursor prim_asg is
127 select assignment_id
128 from per_all_assignments_f
129 where person_id = p_person_id
130 and business_group_id = p_business_group_id
131 and primary_flag = 'Y'
132 and p_start_date between effective_start_date and effective_end_date;
133
134 l_proposal_id per_pay_proposals.pay_proposal_id%type;
135 l_proposal_exists varchar2(1);
136
137 cursor get_primary_approved_proposal(ass_id per_all_assignments_f.assignment_id%type) is
138 select pay_proposal_id
139 from per_pay_proposals
140 where assignment_id=ass_id
141 and APPROVED='Y';
142
143 function table_contents return varchar2 is
144 l_appls varchar2(4000); -- Bug 3214063
145 l_max number;
146 BEGIN
147 hr_utility.set_location('IN hrhirapl.table_contents',490);
148 l_max := p_table.COUNT;
149 hr_utility.trace('table rows : '||to_char(l_max));
150 for v_index in 1..l_max loop
151
152 l_appls := l_appls||' ('||to_char(p_table(v_index).id)||')'
153 ||p_table(v_index).process_flag;
154
155 END LOOP;
156 hr_utility.set_location('OUT hrhirapl.table_contents',495);
157 return(l_appls);
158 END;
159 --
160 --
161 FUNCTION get_period_of_service (p_business_group_id IN INTEGER
162 ,p_person_id IN INTEGER
163 ,p_legislation_code IN VARCHAR2
164 ,p_emp_apl IN VARCHAR2
165 ) return INTEGER is
166 --
167 -- Get new or existing period of service.
168 --
169 --
170 v_dummy INTEGER;
171 --
172 -- START WWBUG fix for 1390173
173 --
174 l_old ben_pps_ler.g_pps_ler_rec;
175 l_new ben_pps_ler.g_pps_ler_rec;
176 --
177 -- END WWBUG fix for 1390173
178 --
179 --
180 begin
181 hr_utility.set_location('hr_person.get_period_of_service',1);
182 --
183 if p_emp_apl ='Y' then
184 begin
185 select pps.period_of_service_id
186 into v_dummy
187 from per_periods_of_service pps
188 where p_start_date between pps.date_start
189 and nvl(pps.ACTUAL_TERMINATION_DATE,p_end_of_time)
190 and pps.person_id = p_person_id
191 and pps.business_group_id + 0 = p_business_group_id;
192 --
193 return v_dummy;
194 --
195 exception
196 when no_data_found then
197 hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
198 hr_utility.raise_error;
199 when others then
200 null;
201 end;
202 else
203 hr_utility.set_location('hr_person.get_period_of_service',2);
204 begin
205 select per_periods_of_service_s.nextval
206 into v_dummy
207 from sys.dual;
208 exception
209 when no_data_found then
210 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
211 hr_utility.set_message_token('PROCEDURE','get_period_of_service');
212 hr_utility.set_message_token('STEP',1);
213 hr_utility.raise_error;
214 when others then null;
215 end;
216 --
217 hr_utility.set_location('hr_person.get_period_of_service',3);
218 begin
219 insert into per_periods_of_service
220 (period_of_service_id
221 ,business_group_id
222 ,person_id
223 ,date_start
224 ,last_update_date
225 ,last_update_login
226 ,last_updated_by
227 ,created_by
228 ,creation_date
229 ,adjusted_svc_date)
230 values
231 (v_dummy
232 ,p_business_group_id
233 ,p_person_id
234 ,p_start_date
235 ,null
236 ,null
237 ,null
238 ,null
239 ,null
240 ,p_adjusted_svc_date
241 );
242 --
243 -- Bug No 4457579 Moved this check above call to ben_pps_ler.ler_chk
244 --
245 if SQL%ROWCOUNT < 1 then
246 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
247 hr_utility.set_message_token('PROCEDURE','get_period_of_service');
248 hr_utility.set_message_token('STEP',2);
249 hr_utility.raise_error;
250 end if;
251 hr_utility.set_location('hr_person.get_period_of_service',4);
252 --
253 -- START WWBUG fix for 1390173
254 --
255 l_new.PERSON_ID := p_person_id;
256 l_new.BUSINESS_GROUP_ID := p_business_group_id;
257 l_new.DATE_START := p_start_date;
258 l_new.ACTUAL_TERMINATION_DATE := null;
259 l_new.LEAVING_REASON := null;
260 l_new.ADJUSTED_SVC_DATE := null;
261 l_new.ATTRIBUTE1 := null;
262 l_new.ATTRIBUTE2 := null;
263 l_new.ATTRIBUTE3 := null;
264 l_new.ATTRIBUTE4 := null;
265 l_new.ATTRIBUTE5 := null;
266 l_new.final_process_date := null;
267 --
268 ben_pps_ler.ler_chk(p_old => l_old
269 ,p_new => l_new
270 ,p_event => 'INSERTING'
271 ,p_effective_date => p_start_date);
272 --
273 -- END WWBUG fix for 1390173
274 --
275 --
276 return v_dummy;
277 --
278 end;
279 end if;
280 --
281 end get_period_of_service;
282 --
283 --
284 PROCEDURE update_primary_assignment(p_business_group_id IN INTEGER
285 ,p_person_id IN INTEGER
286 ,p_start_date IN DATE
287 ,p_current_date IN DATE
288 ,p_user_id IN INTEGER
289 ,p_login_id IN INTEGER
290 ) is
291 --
292 -- Date effectively end the current primary assignment
293 --
294 --
295 begin
296 --
297 hr_utility.set_location('hr_person.update_primary_assignment',1);
298 --
299 insert into per_assignments_f
300 (assignment_id
301 ,effective_start_date
302 ,effective_end_date
303 ,business_group_id
304 ,grade_id
305 ,position_id
306 ,job_id
307 ,assignment_status_type_id
308 ,payroll_id
309 ,location_id
310 ,person_id
311 ,organization_id
312 ,people_group_id
313 ,soft_coding_keyflex_id
314 ,vacancy_id
315 ,assignment_sequence
316 ,assignment_type
317 ,manager_flag
318 ,primary_flag
319 ,application_id
320 ,assignment_number
321 ,change_reason
322 ,comment_id
323 ,date_probation_end
324 ,default_code_comb_id
325 ,frequency
326 ,internal_address_line
327 ,normal_hours
328 ,period_of_service_id
329 ,probation_period
330 ,probation_unit
331 ,recruiter_id
332 ,set_of_books_id
333 ,special_ceiling_step_id
334 ,supervisor_id
335 ,time_normal_finish
336 ,time_normal_start
337 ,request_id
338 ,program_application_id
339 ,program_id
340 ,program_update_date
341 ,ass_attribute_category
342 ,ass_attribute1
343 ,ass_attribute2
344 ,ass_attribute3
345 ,ass_attribute4
346 ,ass_attribute5
347 ,ass_attribute6
348 ,ass_attribute7
349 ,ass_attribute8
350 ,ass_attribute9
351 ,ass_attribute10
352 ,ass_attribute11
353 ,ass_attribute12
354 ,ass_attribute13
355 ,ass_attribute14
356 ,ass_attribute15
357 ,ass_attribute16
358 ,ass_attribute17
359 ,ass_attribute18
360 ,ass_attribute19
361 ,ass_attribute20
362 ,ass_attribute21
363 ,ass_attribute22
364 ,ass_attribute23
365 ,ass_attribute24
366 ,ass_attribute25
367 ,ass_attribute26
368 ,ass_attribute27
369 ,ass_attribute28
370 ,ass_attribute29
371 ,ass_attribute30
372 ,last_update_date
373 ,last_updated_by
374 ,last_update_login
375 ,created_by
376 ,creation_date
377 ,pay_basis_id
378 ,person_referred_by_id
379 ,recruitment_activity_id
380 ,source_organization_id
381 ,source_type
382 ,employment_category /* columns added Bug 978981 */
383 ,perf_review_period
384 ,perf_review_period_frequency
385 ,sal_review_period
386 ,sal_review_period_frequency
387 ,bargaining_unit_code
388 ,labour_union_member_flag
389 ,hourly_salaried_code
390 ,title
391 ,supervisor_assignment_id --- #Added for fix of 4053244
392 ,EMPLOYEE_CATEGORY -- Added for fix of 4212826
393 ,COLLECTIVE_AGREEMENT_ID
394 ,CAGR_ID_FLEX_NUM
395 ,CAGR_GRADE_DEF_ID
396 ,GRADE_LADDER_PGM_ID
397 ,contract_id -- fix of bug 14784589
398 )
399 select pa.assignment_id
400 ,pa.effective_start_date
401 ,p_start_date - 1
402 ,pa.business_group_id
403 ,pa.grade_id
404 ,pa.position_id
405 ,pa.job_id
406 ,pa.assignment_status_type_id
407 ,pa.payroll_id
408 ,pa.location_id
409 ,pa.person_id
410 ,pa.organization_id
411 ,pa.people_group_id
412 ,pa.soft_coding_keyflex_id
413 ,pa.vacancy_id
414 ,pa.assignment_sequence
415 ,pa.assignment_type
416 ,pa.manager_flag
417 ,pa.primary_flag
418 ,pa.application_id
419 ,pa.assignment_number
420 ,pa.change_reason
421 ,pa.comment_id
422 ,pa.date_probation_end
423 ,pa.default_code_comb_id
424 ,pa.frequency
425 ,pa.internal_address_line
426 ,pa.normal_hours
427 ,pa.period_of_service_id
428 ,pa.probation_period
429 ,pa.probation_unit
430 ,pa.recruiter_id
431 ,pa.set_of_books_id
432 ,pa.special_ceiling_step_id
433 ,pa.supervisor_id
434 ,pa.time_normal_finish
435 ,pa.time_normal_start
436 ,pa.request_id
437 ,pa.program_application_id
438 ,pa.program_id
439 ,pa.program_update_date
440 ,pa.ass_attribute_category
441 ,pa.ass_attribute1
442 ,pa.ass_attribute2
443 ,pa.ass_attribute3
444 ,pa.ass_attribute4
445 ,pa.ass_attribute5
446 ,pa.ass_attribute6
447 ,pa.ass_attribute7
448 ,pa.ass_attribute8
449 ,pa.ass_attribute9
450 ,pa.ass_attribute10
451 ,pa.ass_attribute11
452 ,pa.ass_attribute12
453 ,pa.ass_attribute13
454 ,pa.ass_attribute14
455 ,pa.ass_attribute15
456 ,pa.ass_attribute16
457 ,pa.ass_attribute17
458 ,pa.ass_attribute18
459 ,pa.ass_attribute19
460 ,pa.ass_attribute20
461 ,pa.ass_attribute21
462 ,pa.ass_attribute22
463 ,pa.ass_attribute23
464 ,pa.ass_attribute24
465 ,pa.ass_attribute25
466 ,pa.ass_attribute26
467 ,pa.ass_attribute27
468 ,pa.ass_attribute28
469 ,pa.ass_attribute29
470 ,pa.ass_attribute30
471 ,p_current_date
472 ,p_user_id
473 ,p_login_id
474 ,pa.created_by
475 ,pa.creation_date
476 ,pa.pay_basis_id
477 ,pa.person_referred_by_id
478 ,pa.recruitment_activity_id
479 ,pa.source_organization_id
480 ,pa.source_type
481 ,employment_category /* columns added Bug 978981 */
482 ,perf_review_period
483 ,perf_review_period_frequency
484 ,sal_review_period
485 ,sal_review_period_frequency
486 ,bargaining_unit_code
487 ,labour_union_member_flag
488 ,hourly_salaried_code
489 ,title
490 ,pa.supervisor_assignment_id --- #Added for fix of 4053244
491 ,pa.EMPLOYEE_CATEGORY -- Added for fix of 4212826
492 ,pa.COLLECTIVE_AGREEMENT_ID
493 ,pa.CAGR_ID_FLEX_NUM
494 ,pa.CAGR_GRADE_DEF_ID
495 ,pa.GRADE_LADDER_PGM_ID -- fix of bug 5513751
496 ,pa.contract_id -- fix of bug 14784589
497 from per_assignments_f pa
498 where pa.person_id = p_person_id
499 and pa.business_group_id + 0 = p_business_group_id
500 and pa.primary_flag = 'Y'
501 and p_start_date between pa.effective_start_date
502 and pa.effective_end_date
503 and p_start_date > pa.effective_start_date; -- #1981550
504 --
505 -- Previous statement could fail when the hire date is the same as the
506 -- effective start date of the current assignment.(#1981550)
507 --
508 --
509 --if SQL%ROWCOUNT < 1 THEN
510 -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
511 -- hr_utility.set_message_token('PROCEDURE','update_primary_assignment');
512 -- hr_utility.set_message_token('STEP','1');
513 -- hr_utility.raise_error;
514 --end if;
515 --
516 end update_primary_assignment;
517 -- +-----------------------------------------------------------------------+
518 -- --------------------BEGIN: make_secondary ------------------------------+
519 -- +-----------------------------------------------------------------------+
520 PROCEDURE make_secondary(p_business_group_id IN INTEGER
521 ,p_person_id IN INTEGER
522 ,p_legislation_code IN VARCHAR2
523 ,p_assignment_status_type_id IN INTEGER
524 ,p_update_primary_flag IN VARCHAR2
525 ,p_new_primary_id IN INTEGER
526 ,p_user_id IN INTEGER
527 ,p_login_id IN INTEGER
528 ,p_start_date IN DATE
529 ,p_end_of_time IN DATE
530 ,p_employee_number IN VARCHAR2
531 ,p_set_of_books_id IN INTEGER
532 ,p_current_date IN DATE
533 ) is
534 --
535 -- Make other accepted assignment rows secondary
536 -- as long as the user does not want to keep them in the system
537 -- i.e. (R)etain value exists in p_table
538 --
539 -- counter to hold number of assignments inserted
540 -- used to check that all are updated.
541 v_count INTEGER;
542 --
543 l_chk_assg_end_dated varchar2(1); -- bug6310975
544 p_assignment_number VARCHAR2(30);
545 p_assignment_sequence INTEGER;
546 p_rowid ROWID;
547 --
548 l_dummy VARCHAR2(1);
549 --
550 -- Start of bug 3564129
551 l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
552 l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
553 -- End of bug 3564129
554 --
555 -- Bug 518669. Increased length of l_col_name from 30 to 200, so it matches
556 -- the max len of the column in the DB. Pashun. 16-Sep-97.
557 --
558 l_col_name VARCHAR2(200);
559 cursor get_flex_def is
560 select default_context_field_name
561 from fnd_descriptive_flexs
562 where application_id = 800 -- bug 5469726
563 and descriptive_flexfield_name = 'PER_ASSIGNMENTS';
564 --
565 -- #2264569
566 --
567 l_asg_rec per_assignments_f%ROWTYPE;
568 --
569 cursor ass_cur is
570 select pa.*
571 from per_assignments_f pa
572 , per_assignment_status_types past
573 where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
574 and nvl(past.legislation_code, p_legislation_code)
575 = p_legislation_code
576 and past.per_system_status = 'ACCEPTED'
577 and pa.assignment_type = 'A'
578 and pa.business_group_id + 0 = p_business_group_id
579 and pa.person_id = p_person_id
580 and past.assignment_status_type_id = pa.assignment_status_type_id
581 and ((p_update_primary_flag in ('Y','V')
582 and pa.assignment_id <> p_new_primary_id
583 )
584 or (p_update_primary_flag not in ('Y','V')
585 )
586 )
587 and p_start_date between pa.effective_start_date
588 and pa.effective_end_date
589 order by decode(pa.assignment_id,p_new_primary_id,1,0) desc --added for bug 5589928
590 for update of pa.assignment_status_type_id;
591 --
592 --
593 /*fix for the bug 5498344 starts here
594 cursor csr_ass_cur_for_primary is
595 select pa.*
596 from per_assignments_f pa
597 , per_assignment_status_types past
598 where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
599 and nvl(past.legislation_code, p_legislation_code)
600 = p_legislation_code
601 and past.per_system_status = 'ACCEPTED'
602 and pa.assignment_type = 'A'
603 and pa.business_group_id + 0 = p_business_group_id
604 and pa.person_id = p_person_id
605 and past.assignment_status_type_id = pa.assignment_status_type_id
606 and ((p_update_primary_flag in ('Y','V')
607 and pa.assignment_id <> p_new_primary_id
608 )
609 or (p_update_primary_flag not in ('Y','V')
610 )
611 )
612 and p_start_date between pa.effective_start_date
613 and pa.effective_end_date
614 and pa.assignment_id = p_new_primary_id
615 for update of pa.assignment_status_type_id;
616 --
617
618 cursor csr_ass_cur_for_nonprimary is
619 select pa.*
620 from per_assignments_f pa
621 , per_assignment_status_types past
622 where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
623 and nvl(past.legislation_code, p_legislation_code)
624 = p_legislation_code
625 and past.per_system_status = 'ACCEPTED'
626 and pa.assignment_type = 'A'
627 and pa.business_group_id + 0 = p_business_group_id
628 and pa.person_id = p_person_id
629 and past.assignment_status_type_id = pa.assignment_status_type_id
630 and ((p_update_primary_flag in ('Y','V')
631 and pa.assignment_id <> p_new_primary_id
632 )
633 or (p_update_primary_flag not in ('Y','V')
634 )
635 )
636 and p_start_date between pa.effective_start_date
637 and pa.effective_end_date
638 and pa.assignment_id <> p_new_primary_id
639 for update of pa.assignment_status_type_id;
640 --
641 --
642 end of fix 5498344*/
643
644 --
645 -- Bug 1248710 incxreased variable length to 150
646
647 l_app_col_name VARCHAR2(30);
648 l_ass_attribute1 VARCHAR2(150);
649 l_ass_attribute2 VARCHAR2(150);
650 l_ass_attribute3 VARCHAR2(150);
651 l_ass_attribute4 VARCHAR2(150);
652 l_ass_attribute5 VARCHAR2(150);
653 l_ass_attribute6 VARCHAR2(150);
654 l_ass_attribute7 VARCHAR2(150);
655 l_ass_attribute8 VARCHAR2(150);
656 l_ass_attribute9 VARCHAR2(150);
657 l_ass_attribute10 VARCHAR2(150);
658 l_ass_attribute11 VARCHAR2(150);
659 l_ass_attribute12 VARCHAR2(150);
660 l_ass_attribute13 VARCHAR2(150);
661 l_ass_attribute14 VARCHAR2(150);
662 l_ass_attribute15 VARCHAR2(150);
663 l_ass_attribute16 VARCHAR2(150);
664 l_ass_attribute17 VARCHAR2(150);
665 l_ass_attribute18 VARCHAR2(150);
666 l_ass_attribute19 VARCHAR2(150);
667 l_ass_attribute20 VARCHAR2(150);
668 l_ass_attribute21 VARCHAR2(150);
669 l_ass_attribute22 VARCHAR2(150);
670 l_ass_attribute23 VARCHAR2(150);
671 l_ass_attribute24 VARCHAR2(150);
672 l_ass_attribute25 VARCHAR2(150);
673 l_ass_attribute26 VARCHAR2(150);
674 l_ass_attribute27 VARCHAR2(150);
675 l_ass_attribute28 VARCHAR2(150);
676 l_ass_attribute29 VARCHAR2(150);
677 l_ass_attribute30 VARCHAR2(150);
678 --
679 --
680 -- Bug 401669 Created cursor to fetch non global columns
681 -- Cursor to fetch record from per_assignments_f
682 --
683 cursor get_application_column_name is
684 select application_column_name
685 from fnd_descr_flex_column_usages fdfcu,
686 fnd_descr_flex_contexts fdfc
687 where fdfcu.descriptive_flexfield_name = 'PER_ASSIGNMENTS'
688 and fdfcu.descriptive_flexfield_name = fdfc.descriptive_flexfield_name
689 and fdfcu.descriptive_flex_context_code = fdfc.descriptive_flex_context_code
690 and fdfcu.application_id = fdfc.application_id --- bug 5469726
691 and fdfc.application_id = 800 --- bug 5469726
692 and fdfc.global_flag = 'N'
693 and l_col_name = 'ASSIGNMENT_TYPE';
694
695
696 --added by amigarg for bug 4882512 start
697
698 cursor get_pay_proposal(ass_id per_all_assignments_f.assignment_id%type) is
699 select pay_proposal_id,object_version_number,proposed_salary_n, change_date
700 from per_pay_proposals
701 where assignment_id=ass_id
702 and approved = 'N'
703 order by change_date desc;
704 l_pay_pspl_id per_pay_proposals.pay_proposal_id%TYPE;
705 l_pay_obj_number per_pay_proposals.object_version_number%TYPE;
706 l_proposed_sal_n per_pay_proposals.proposed_salary_n%TYPE;
707 l_dummy_change_date per_pay_proposals.change_date%TYPE;
708 l_inv_next_sal_date_warning boolean := false;
709 l_proposed_salary_warning boolean := false;
710 l_approved_warning boolean := false;
711 l_payroll_warning boolean := false;
712
713 --added by amigarg for bug 4882512 end
714 --
715 --
716 -- 11853433
717
718 l_irc_link_exists number := -1;
719 l_irc_spl_status number;
720
721 -- 11853433
722
723 -- # end 2264569
724 -- +--------------------------------------------------------------------------+
725 -- +---------------------- main make secondary -------------------------------+
726 -- +--------------------------------------------------------------------------+
727 begin
728 -- # 2366672
729 -- Application needs to be end dated before the apl asg get updated.
730 --
731 hr_utility.set_location('hrhirapl.make_secondary',1);
732 -- +-----------------------------------------------------------------------+
733 -- +------------------------ End Application -----------------------------+
734 -- +-----------------------------------------------------------------------+
735 -- Does the Retain value exist in the table ?
736 if not hr_employee_applicant_api.retain_exists(p_table)
737 then
738 -- we are "double-checking" that previous updates were successfull
739 -- that is why where clause checks for accepted and unaccepted, despite
740 -- the fact the table does not have stored a retain value .
741 --
742 hr_utility.set_location('hrhiapl.make_secondary',2);
743 --
744 update per_applications pap
745 set date_end = p_start_date -1,
746 successful_flag = 'Y'
747 where pap.person_id = p_person_id -- added for bug 5469726
748 and exists (select '1'
749 from per_assignments_f pa,
750 per_assignment_status_types past
751 where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
752 and nvl(past.legislation_code, p_legislation_code)
753 = p_legislation_code
754 and past.per_system_status = 'ACCEPTED'
755 and pa.assignment_type = 'A'
756 and pa.business_group_id + 0 = p_business_group_id
757 and pa.person_id = p_person_id
758 and pa.person_id = pap.person_id
759 and past.assignment_status_type_id = pa.assignment_status_type_id
760 and p_start_date between pap.date_received and nvl(pap.date_end,p_start_date)
761 and p_start_date between pa.effective_start_date
762 and pa.effective_end_date)
763 and not exists (select '1'
764 from per_assignments_f pa,
765 per_assignment_status_types past
766 where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
767 and nvl(past.legislation_code, p_legislation_code)
768 = p_legislation_code
769 and past.per_system_status <> 'ACCEPTED'
770 and pa.assignment_type = 'A'
771 and pa.business_group_id + 0 = p_business_group_id
772 and pa.person_id = p_person_id
773 and pa.person_id = pap.person_id
774 and past.assignment_status_type_id = pa.assignment_status_type_id
775 and p_start_date between pa.effective_start_date
776 and pa.effective_end_date);
777
778 if SQL%NOTFOUND then
779 -- could not update the application
780 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
781 hr_utility.set_message_token('PROCEDURE','make_secondary ');
782 hr_utility.set_message_token('STEP','5');
783 hr_utility.raise_error;
784 end if;
785 end if; -- retain value in table?
786 --
787
788 open get_flex_def;
789 fetch get_flex_def into l_col_name;
790 close get_flex_def;
791 --
792 hr_utility.set_location('hrhirapl.make_secondary',2);
793 --
794 -- Update all accepted assignments making them all
795 -- secondary (also end dates the assignment type 'A'
796 -- Unless the update primary_flag is set, in which case
797 -- update all save the chosen assignment.
798 --
799 /* fix for the bug 5498344
800 hr_utility.set_location('p_update_primary_flag '||p_update_primary_flag,2);
801 if p_update_primary_flag not in ('C','N') then commented for bug 5589928*/
802
803 open ass_cur;
804 loop
805 fetch ass_cur into l_asg_rec; --#2119831
806 exit when ass_cur%NOTFOUND;
807 --
808 -- #2483319
809 p_assignment_id := l_asg_rec.assignment_id;
810 --
811 --
812 -- Ensure (R)etain or (E)nd date flags have not been set
813 if hr_employee_applicant_api.is_convert(p_table
814 ,l_asg_rec.assignment_id)
815 then
816 hr_utility.set_location('hrhirapl.make_secondary',333);
817 hr_utility.trace(' asg id = '||to_char(l_asg_rec.assignment_id));
818 hr_utility.trace(' start date = '||to_char(l_asg_rec.effective_start_date,'dd/mm/yy'));
819
820 -- +--------------------------------------------------+
821 -- +--- End Date assignment type 'A' -----------------+
822 -- +--------------------------------------------------+
823 begin
824 insert into per_assignments_f
825 (assignment_id
826 ,effective_start_date
827 ,effective_end_date
828 ,business_group_id
829 ,grade_id
830 ,position_id
831 ,job_id
832 ,assignment_status_type_id
833 ,payroll_id
834 ,location_id
835 ,person_id
836 ,organization_id
837 ,people_group_id
838 ,soft_coding_keyflex_id
839 ,vacancy_id
840 ,assignment_sequence
841 ,assignment_type
842 ,manager_flag
843 ,primary_flag
844 ,application_id
845 ,assignment_number
846 ,change_reason
847 ,comment_id
848 ,date_probation_end
849 ,default_code_comb_id
850 ,frequency
851 ,internal_address_line
852 ,normal_hours
853 ,period_of_service_id
854 ,probation_period
855 ,probation_unit
856 ,recruiter_id
857 ,set_of_books_id
858 ,special_ceiling_step_id
859 ,supervisor_id
860 ,time_normal_finish
861 ,time_normal_start
862 ,request_id
863 ,program_application_id
864 ,program_id
865 ,program_update_date
866 ,ass_attribute_category
867 ,ass_attribute1
868 ,ass_attribute2
869 ,ass_attribute3
870 ,ass_attribute4
871 ,ass_attribute5
872 ,ass_attribute6
873 ,ass_attribute7
874 ,ass_attribute8
875 ,ass_attribute9
876 ,ass_attribute10
877 ,ass_attribute11
878 ,ass_attribute12
879 ,ass_attribute13
880 ,ass_attribute14
881 ,ass_attribute15
882 ,ass_attribute16
883 ,ass_attribute17
884 ,ass_attribute18
885 ,ass_attribute19
886 ,ass_attribute20
887 ,ass_attribute21
888 ,ass_attribute22
889 ,ass_attribute23
890 ,ass_attribute24
891 ,ass_attribute25
892 ,ass_attribute26
893 ,ass_attribute27
894 ,ass_attribute28
895 ,ass_attribute29
896 ,ass_attribute30
897 ,last_update_date
898 ,last_updated_by
899 ,last_update_login
900 ,created_by
901 ,creation_date
902 ,pay_basis_id
903 ,person_referred_by_id
904 ,recruitment_activity_id
905 ,source_organization_id
906 ,source_type
907 ,employment_category /* columns added Bug 978981 */
908 ,perf_review_period
909 ,perf_review_period_frequency
910 ,sal_review_period
911 ,sal_review_period_frequency
912 ,bargaining_unit_code
913 ,labour_union_member_flag
914 ,hourly_salaried_code
915 ,title
916 ,job_post_source_name -- added for 4486233
917 ,supervisor_assignment_id) ---#4053244
918 values
919 (l_asg_rec.assignment_id
920 ,l_asg_rec.effective_start_date
921 ,p_start_date - 1
922 ,l_asg_rec.business_group_id
923 ,l_asg_rec.grade_id
924 ,l_asg_rec.position_id
925 ,l_asg_rec.job_id
926 ,l_asg_rec.assignment_status_type_id
927 ,l_asg_rec.payroll_id
928 ,l_asg_rec.location_id
929 ,l_asg_rec.person_id
930 ,l_asg_rec.organization_id
931 ,l_asg_rec.people_group_id
932 ,l_asg_rec.soft_coding_keyflex_id
933 ,l_asg_rec.vacancy_id
934 ,l_asg_rec.assignment_sequence
935 ,l_asg_rec.assignment_type
936 ,l_asg_rec.manager_flag
937 ,l_asg_rec.primary_flag
938 ,l_asg_rec.application_id
939 ,l_asg_rec.assignment_number
940 ,l_asg_rec.change_reason
941 ,l_asg_rec.comment_id
942 ,l_asg_rec.date_probation_end
943 ,l_asg_rec.default_code_comb_id
944 ,l_asg_rec.frequency
945 ,l_asg_rec.internal_address_line
946 ,l_asg_rec.normal_hours
947 ,l_asg_rec.period_of_service_id
948 ,l_asg_rec.probation_period
949 ,l_asg_rec.probation_unit
950 ,l_asg_rec.recruiter_id
951 ,l_asg_rec.set_of_books_id
952 ,l_asg_rec.special_ceiling_step_id
953 ,l_asg_rec.supervisor_id
954 ,l_asg_rec.time_normal_finish
955 ,l_asg_rec.time_normal_start
956 ,l_asg_rec.request_id
957 ,l_asg_rec.program_application_id
958 ,l_asg_rec.program_id
959 ,l_asg_rec.program_update_date
960 ,l_asg_rec.ass_attribute_category
961 ,l_asg_rec.ass_attribute1
962 ,l_asg_rec.ass_attribute2
963 ,l_asg_rec.ass_attribute3
964 ,l_asg_rec.ass_attribute4
965 ,l_asg_rec.ass_attribute5
966 ,l_asg_rec.ass_attribute6
967 ,l_asg_rec.ass_attribute7
968 ,l_asg_rec.ass_attribute8
969 ,l_asg_rec.ass_attribute9
970 ,l_asg_rec.ass_attribute10
971 ,l_asg_rec.ass_attribute11
972 ,l_asg_rec.ass_attribute12
973 ,l_asg_rec.ass_attribute13
974 ,l_asg_rec.ass_attribute14
975 ,l_asg_rec.ass_attribute15
976 ,l_asg_rec.ass_attribute16
977 ,l_asg_rec.ass_attribute17
978 ,l_asg_rec.ass_attribute18
979 ,l_asg_rec.ass_attribute19
980 ,l_asg_rec.ass_attribute20
981 ,l_asg_rec.ass_attribute21
982 ,l_asg_rec.ass_attribute22
983 ,l_asg_rec.ass_attribute23
984 ,l_asg_rec.ass_attribute24
985 ,l_asg_rec.ass_attribute25
986 ,l_asg_rec.ass_attribute26
987 ,l_asg_rec.ass_attribute27
988 ,l_asg_rec.ass_attribute28
989 ,l_asg_rec.ass_attribute29
990 ,l_asg_rec.ass_attribute30
991 ,p_current_date
992 ,p_user_id
993 ,p_login_id
994 ,l_asg_rec.created_by
995 ,l_asg_rec.creation_date
996 ,l_asg_rec.pay_basis_id
997 ,l_asg_rec.person_referred_by_id
998 ,l_asg_rec.recruitment_activity_id
999 ,l_asg_rec.source_organization_id
1000 ,l_asg_rec.source_type
1001 ,l_asg_rec.employment_category /* columns added Bug 978981 */
1002 ,l_asg_rec.perf_review_period
1003 ,l_asg_rec.perf_review_period_frequency
1004 ,l_asg_rec.sal_review_period
1005 ,l_asg_rec.sal_review_period_frequency
1006 ,l_asg_rec.bargaining_unit_code
1007 ,l_asg_rec.labour_union_member_flag
1008 ,l_asg_rec.hourly_salaried_code
1009 ,l_asg_rec.title
1010 ,l_asg_rec.job_post_source_name -- added for 4486233
1011 ,l_asg_rec.supervisor_assignment_id); ---#4053244
1012 exception
1013 when others then
1014 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1015 hr_utility.set_message_token('PROCEDURE','make_secondary => ASGID: '
1016 ||to_char(l_asg_rec.assignment_id));
1017 hr_utility.set_message_token('STEP','2');
1018 hr_utility.raise_error;
1019
1020 end;
1021 -- +----------- END end date of assignment -----------+
1022 -- +--------------------------------------------------+
1023 --
1024 -- +--------------------------------------------------+
1025 -- +--- Convert assignment into secondary ------------+
1026 -- +--------------------------------------------------+
1027 -- # 2582838
1028 -- Bug - 401669
1029 -- select all ass_attribute columns
1030 --
1031 l_ass_attribute1 := l_asg_rec.ass_attribute1;
1032 l_ass_attribute2 := l_asg_rec.ass_attribute2;
1033 l_ass_attribute3 := l_asg_rec.ass_attribute3;
1034 l_ass_attribute4 := l_asg_rec.ass_attribute4;
1035 l_ass_attribute5 := l_asg_rec.ass_attribute5;
1036 l_ass_attribute6 := l_asg_rec.ass_attribute6;
1037 l_ass_attribute7 := l_asg_rec.ass_attribute7;
1038 l_ass_attribute8 := l_asg_rec.ass_attribute8;
1039 l_ass_attribute9 := l_asg_rec.ass_attribute9;
1040 l_ass_attribute10 := l_asg_rec.ass_attribute10;
1041 l_ass_attribute11 := l_asg_rec.ass_attribute11;
1042 l_ass_attribute12 := l_asg_rec.ass_attribute12;
1043 l_ass_attribute13 := l_asg_rec.ass_attribute13;
1044 l_ass_attribute14 := l_asg_rec.ass_attribute14;
1045 l_ass_attribute15 := l_asg_rec.ass_attribute15;
1046 l_ass_attribute16 := l_asg_rec.ass_attribute16;
1047 l_ass_attribute17 := l_asg_rec.ass_attribute17;
1048 l_ass_attribute18 := l_asg_rec.ass_attribute18;
1049 l_ass_attribute19 := l_asg_rec.ass_attribute19;
1050 l_ass_attribute20 := l_asg_rec.ass_attribute20;
1051 l_ass_attribute21 := l_asg_rec.ass_attribute21;
1052 l_ass_attribute22 := l_asg_rec.ass_attribute22;
1053 l_ass_attribute23 := l_asg_rec.ass_attribute23;
1054 l_ass_attribute24 := l_asg_rec.ass_attribute24;
1055 l_ass_attribute25 := l_asg_rec.ass_attribute25;
1056 l_ass_attribute26 := l_asg_rec.ass_attribute26;
1057 l_ass_attribute27 := l_asg_rec.ass_attribute27;
1058 l_ass_attribute28 := l_asg_rec.ass_attribute28;
1059 l_ass_attribute29 := l_asg_rec.ass_attribute29;
1060 l_ass_attribute30 := l_asg_rec.ass_attribute30;
1061
1062 open get_application_column_name;
1063 loop
1064 fetch get_application_column_name into l_app_col_name;
1065 exit when get_application_column_name%NOTFOUND;
1066 --
1067 if l_app_col_name = 'ASS_ATTRIBUTE1' then
1068 l_ass_attribute1 := NULL;
1069 end if;
1070 if l_app_col_name = 'ASS_ATTRIBUTE2' then
1071 l_ass_attribute2 := NULL;
1072 end if;
1073 if l_app_col_name = 'ASS_ATTRIBUTE3' then
1074 l_ass_attribute3 := NULL;
1075 end if;
1076 if l_app_col_name = 'ASS_ATTRIBUTE4' then
1077 l_ass_attribute4 := NULL;
1078 end if;
1079 if l_app_col_name = 'ASS_ATTRIBUTE5' then
1080 l_ass_attribute5 := NULL;
1081 end if;
1082 if l_app_col_name = 'ASS_ATTRIBUTE6' then
1083 l_ass_attribute6 := NULL;
1084 end if;
1085 if l_app_col_name = 'ASS_ATTRIBUTE7' then
1086 l_ass_attribute7 := NULL;
1087 end if;
1088 if l_app_col_name = 'ASS_ATTRIBUTE8' then
1089 l_ass_attribute8 := NULL;
1090 end if;
1091 if l_app_col_name = 'ASS_ATTRIBUTE9' then
1092 l_ass_attribute9 := NULL;
1093 end if;
1094 if l_app_col_name = 'ASS_ATTRIBUTE10' then
1095 l_ass_attribute10 := NULL;
1096 end if;
1097 if l_app_col_name = 'ASS_ATTRIBUTE11' then
1098 l_ass_attribute11 := NULL;
1099 end if;
1100 if l_app_col_name = 'ASS_ATTRIBUTE12' then
1101 l_ass_attribute12 := NULL;
1102 end if;
1103 if l_app_col_name = 'ASS_ATTRIBUTE13' then
1104 l_ass_attribute13 := NULL;
1105 end if;
1106 if l_app_col_name = 'ASS_ATTRIBUTE14' then
1107 l_ass_attribute14 := NULL;
1108 end if;
1109 if l_app_col_name = 'ASS_ATTRIBUTE15' then
1110 l_ass_attribute15 := NULL;
1111 end if;
1112 if l_app_col_name = 'ASS_ATTRIBUTE16' then
1113 l_ass_attribute16 := NULL;
1114 end if;
1115 if l_app_col_name = 'ASS_ATTRIBUTE17' then
1116 l_ass_attribute17 := NULL;
1117 end if;
1118 if l_app_col_name = 'ASS_ATTRIBUTE18' then
1119 l_ass_attribute18 := NULL;
1120 end if;
1121 if l_app_col_name = 'ASS_ATTRIBUTE19' then
1122 l_ass_attribute19 := NULL;
1123 end if;
1124 if l_app_col_name = 'ASS_ATTRIBUTE20' then
1125 l_ass_attribute20 := NULL;
1126 end if;
1127 if l_app_col_name = 'ASS_ATTRIBUTE21' then
1128 l_ass_attribute21 := NULL;
1129 end if;
1130 if l_app_col_name = 'ASS_ATTRIBUTE22' then
1131 l_ass_attribute22 := NULL;
1132 end if;
1133 if l_app_col_name = 'ASS_ATTRIBUTE23' then
1134 l_ass_attribute23 := NULL;
1135 end if;
1136 if l_app_col_name = 'ASS_ATTRIBUTE24' then
1137 l_ass_attribute24 := NULL;
1138 end if;
1139 if l_app_col_name = 'ASS_ATTRIBUTE25' then
1140 l_ass_attribute25 := NULL;
1141 end if;
1142 if l_app_col_name = 'ASS_ATTRIBUTE26' then
1143 l_ass_attribute26 := NULL;
1144 end if;
1145 if l_app_col_name = 'ASS_ATTRIBUTE27' then
1146 l_ass_attribute27 := NULL;
1147 end if;
1148 if l_app_col_name = 'ASS_ATTRIBUTE28' then
1149 l_ass_attribute28 := NULL;
1150 end if;
1151 if l_app_col_name = 'ASS_ATTRIBUTE29' then
1152 l_ass_attribute29 := NULL;
1153 end if;
1154 if l_app_col_name = 'ASS_ATTRIBUTE30' then
1155 l_ass_attribute30 := NULL;
1156 end if;
1157 end loop;
1158 --
1159 close get_application_column_name;
1160 -- +-----------------------------------------------------+
1161 --
1162 hrentmnt.check_payroll_changes_asg(p_assignment_id
1163 ,NULL
1164 ,'INSERT'
1165 ,p_start_date
1166 ,p_end_of_time);
1167 --
1168 -- Before doing the update make sure that what we are doing is valid
1169 -- especially for positions.
1170 --
1171 per_asg_bus1.chk_frozen_single_pos
1172 (p_assignment_id => p_assignment_id,
1173 p_position_id => l_asg_rec.position_id,
1174 p_effective_date => p_start_date,
1175 p_assignment_type => l_asg_rec.assignment_type);
1176 --
1177 --
1178 hr_assignment.gen_new_ass_sequence
1179 ( p_person_id
1180 , 'E'
1181 , p_assignment_sequence
1182 );
1183 --
1184 hr_assignment.gen_new_ass_number
1185 (p_assignment_id
1186 ,p_business_group_id
1187 ,p_employee_number
1188 ,p_assignment_sequence
1189 ,p_assignment_number);
1190 --
1191 hr_utility.set_location('hrhirapl.make_secondary',3);
1192 --
1193 -- fix for 7120387
1194 declare
1195
1196 l_date_probation_end date;
1197 l_proj_hire_date date;
1198
1199
1200 cursor appl_rec_det(l_appl_id number) is
1201 select projected_hire_date
1202 from per_applications
1203 where application_id =l_appl_id;
1204
1205
1206
1207 begin
1208
1209 open appl_rec_det(l_asg_rec.application_id) ;
1210 fetch appl_rec_det into l_proj_hire_date;
1211 close appl_rec_det;
1212
1213 hr_utility.set_location('l_asg_rec .assignment_id :'||l_asg_rec.assignment_id,20);
1214 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,20);
1215 hr_utility.set_location('make secondary proj end details ',20);
1216 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,20);
1217
1218 if l_proj_hire_date is null then
1219
1220 if ( l_asg_rec.probation_period is not null)
1221 and
1222 (l_asg_rec.probation_unit is not null ) then
1223
1224
1225 hr_utility.set_location('p_start_date :'||p_start_date,11);
1226 hr_utility.set_location('l_asg_rec.assignment_id :'||l_asg_rec.assignment_id,11);
1227 hr_utility.set_location('l_asg_probation_det.probation_period :'||l_asg_rec.probation_period,12);
1228 hr_utility.set_location('l_asg_probation_det.probation_unit :'||l_asg_rec.probation_unit,15);
1229 l_date_probation_end :=NULL;
1230 hr_assignment.gen_probation_end
1231 (p_assignment_id => l_asg_rec.assignment_id
1232 ,p_probation_period => l_asg_rec.probation_period
1233 ,p_probation_unit => l_asg_rec.probation_unit
1234 ,p_start_date => p_start_date
1235 ,p_date_probation_end => l_date_probation_end
1236 );
1237 hr_utility.set_location('l_date_probation_end :'||l_date_probation_end,10);
1238 l_asg_rec.date_probation_end :=l_date_probation_end;
1239 end if;
1240 end if; -- proj hire end
1241
1242 end;
1243 -- fix for 7120387
1244 --
1245 begin
1246 update per_assignments_f pa
1247 set pa.assignment_status_type_id = p_assignment_status_type_id
1248 , pa.assignment_type = 'E'
1249 , pa.effective_start_date = p_start_date
1250 , pa.effective_end_date = p_end_of_time
1251 , pa.period_of_service_id = v_period_of_service_id
1252 , pa.primary_flag = 'N'
1253 , pa.assignment_number = p_assignment_number
1254 , pa.assignment_sequence = p_assignment_sequence
1255 , pa.last_update_date = p_current_date
1256 , pa.last_updated_by = p_user_id
1257 , pa.last_update_login = p_login_id
1258 , pa.set_of_books_id = p_set_of_books_id
1259 , pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E'
1260 ,pa.ass_attribute_category)
1261 , pa.ass_attribute1 = l_ass_attribute1
1262 , pa.ass_attribute2 = l_ass_attribute2
1263 , pa.ass_attribute3 = l_ass_attribute3
1264 , pa.ass_attribute4 = l_ass_attribute4
1265 , pa.ass_attribute5 = l_ass_attribute5
1266 , pa.ass_attribute6 = l_ass_attribute6
1267 , pa.ass_attribute7 = l_ass_attribute7
1268 , pa.ass_attribute8 = l_ass_attribute8
1269 , pa.ass_attribute9 = l_ass_attribute9
1270 , pa.ass_attribute10 = l_ass_attribute10
1271 , pa.ass_attribute11 = l_ass_attribute11
1272 , pa.ass_attribute12 = l_ass_attribute12
1273 , pa.ass_attribute13 = l_ass_attribute13
1274 , pa.ass_attribute14 = l_ass_attribute14
1275 , pa.ass_attribute15 = l_ass_attribute15
1276 , pa.ass_attribute16 = l_ass_attribute16
1277 , pa.ass_attribute17 = l_ass_attribute17
1278 , pa.ass_attribute18 = l_ass_attribute18
1279 , pa.ass_attribute19 = l_ass_attribute19
1280 , pa.ass_attribute20 = l_ass_attribute20
1281 , pa.ass_attribute21 = l_ass_attribute21
1282 , pa.ass_attribute22 = l_ass_attribute22
1283 , pa.ass_attribute23 = l_ass_attribute23
1284 , pa.ass_attribute24 = l_ass_attribute24
1285 , pa.ass_attribute25 = l_ass_attribute25
1286 , pa.ass_attribute26 = l_ass_attribute26
1287 , pa.ass_attribute27 = l_ass_attribute27
1288 , pa.ass_attribute28 = l_ass_attribute28
1289 , pa.ass_attribute29 = l_ass_attribute29
1290 , pa.ass_attribute30 = l_ass_attribute30
1291 , pa.date_probation_end =l_asg_rec.date_probation_end --7120387
1292 where current of ass_cur; -- pa.rowid = p_rowid;
1293 exception
1294 when others then
1295 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1296 hr_utility.set_message_token('PROCEDURE','make_secondary => ASGID: '
1297 ||to_char(l_asg_rec.assignment_id));
1298 hr_utility.set_message_token('STEP','3');
1299 hr_utility.raise_error;
1300
1301 end;
1302 --Start of fix 12812436
1303 hr_security_internal.add_to_person_list(
1304 p_effective_date => l_asg_rec.effective_start_date,
1305 p_assignment_id => l_asg_rec.assignment_id);
1306 --End of fix 12812436
1307
1308 -- Start of fix 3564129
1309
1310 ----------------------
1311 -- IRC FIX 11853433
1312 ----------------------
1313 /*
1314 select nvl(src_apl_asg_id,-1) into l_irc_link_exists
1315 from PER_VAC_LINKED_ASSIGNMENTS
1316 where tgt_apl_asg_id = p_assignment_id
1317 and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate) ;
1318
1319 hr_utility.set_location('p_assignment_id :'||p_assignment_id,10);
1320 hr_utility.set_location('l_irc_link_exists :'||l_irc_link_exists,10);
1321
1322
1323 if l_irc_link_exists <> -1 then
1324
1325 select assignment_status_type_id into l_irc_spl_status
1326 from per_assignment_status_types
1327 where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
1328 and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
1329
1330 hr_utility.set_location('l_irc_spl_status :'||l_irc_spl_status,10);
1331
1332 IRC_ASG_STATUS_API.create_irc_asg_status
1333 (p_assignment_id => p_assignment_id
1334 ,p_assignment_status_type_id => l_irc_spl_status
1335 ,p_status_change_date => p_session_date
1336 ,p_assignment_status_id => l_asg_status_id
1337 ,p_object_version_number => l_asg_status_ovn);
1338 else
1339
1340
1341 IRC_ASG_STATUS_API.create_irc_asg_status
1342 (p_assignment_id => p_assignment_id
1343 ,p_assignment_status_type_id => p_assignment_status_type_id
1344 ,p_status_change_date => p_session_date
1345 ,p_assignment_status_id => l_asg_status_id
1346 ,p_object_version_number => l_asg_status_ovn);
1347
1348 end if; */
1349
1350
1351 IRC_ASG_STATUS_API.create_irc_asg_status
1352 (p_assignment_id => p_assignment_id
1353 ,p_assignment_status_type_id => p_assignment_status_type_id
1354 ,p_status_change_date => p_session_date
1355 ,p_assignment_status_id => l_asg_status_id
1356 ,p_object_version_number => l_asg_status_ovn);
1357 -- while hiring you no need to set the new assgt status . Only when terminating
1358 --- you need to set the status to new status " " instead of term_% '
1359
1360 ----------------------
1361 -- IRC FIX 11853433
1362 ----------------------
1363
1364 -- End of fix 3564129
1365 -- Start of fix 7289811
1366 IRC_OFFERS_API.close_offer
1367 ( p_validate => false
1368 ,p_effective_date => p_start_date-1
1369 ,p_applicant_assignment_id => p_assignment_id
1370 ,p_change_reason => 'APL_HIRED'-- Fix for bug 7540870
1371 );
1372 -- End of fix 7289811
1373 -- Bug 401669
1374 --
1375 -- Bug#12594760 fix starts
1376
1377 if per_otherbg_apl_api.isMultiRegVac(p_assignment_id) then
1378 per_otherbg_apl_api.close_otherbg_applications(p_assignment_id,p_start_date -1,'HIRE_CLOSE');
1379 end if;
1380
1381 --Bug#12594760 fix ends
1382 -- Bug 11822430
1383
1384 if (p_legislation_code = 'GB' ) then
1385 PER_GB_PENSRV_SVPN.AUTO_CALC_FTE(P_ASSIGNMENT_ID => p_assignment_id,P_EFFECTIVE_START_DATE => p_session_date) ;
1386 end if;
1387
1388 hr_utility.set_location('hrhirapl.make_secondary',4);
1389 --
1390 hr_assignment.load_budget_values(p_assignment_id
1391 ,p_business_group_id
1392 ,p_user_id
1393 ,p_login_id
1394 ,p_start_date
1395 ,p_end_of_time
1396 );
1397 --
1398 hrentmnt.maintain_entries_asg(p_assignment_id
1399 ,p_business_group_id
1400 ,'HIRE_APPL' --,'ASG_CRITERIA' for bug 5547271
1401 ,NULL
1402 ,NULL
1403 ,NULL
1404 ,'INSERT'
1405 ,p_start_date
1406 ,p_end_of_time);
1407 -- set assignment number back to null;
1408 p_assignment_number := NULL;
1409 -- +--------------------------------------------------+
1410 -- +--- END Convert assignment into secondary --------+
1411 -- +--------------------------------------------------+
1412 --
1413 -- Did user explicity choose END Date ?
1414 l_chk_assg_end_dated :='N'; -- bug 6310975
1415 elsif hr_employee_applicant_api.end_date_exists(p_table
1416 ,l_asg_rec.assignment_id) = 1
1417 then
1418 -- +--------------------------------------------------+
1419 -- +--- End Date assignment --------------------------+
1420 -- +--------------------------------------------------+
1421 --
1422 hr_utility.set_location('hrhirapl.make_secondary',5);
1423 l_chk_assg_end_dated := 'Y'; -- bug 6310975
1424 begin
1425 update per_assignments_f
1426 set effective_end_date = p_start_date -1
1427 where current of ass_cur;
1428
1429 if per_otherbg_apl_api.isMultiRegVac(l_asg_rec.assignment_id) then
1430 per_otherbg_apl_api.close_otherbg_applications(l_asg_rec.assignment_id,p_start_date -1,'HIRE_END');
1431 end if;
1432 exception
1433 when others then
1434 hr_utility.set_location('hrhirapl.make_secondary ASGID : '||
1435 to_char(l_asg_rec.assignment_id),66);
1436 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1437 hr_utility.set_message_token('PROCEDURE','make_secondary ');
1438 hr_utility.set_message_token('STEP','4');
1439 hr_utility.raise_error;
1440 end;
1441 end if; -- convert flag is set.
1442 --
1443
1444 --added by amigarg for bug 4882512 start
1445 /*
1446 OPEN get_pay_proposal(l_asg_rec.assignment_id);
1447 FETCH get_pay_proposal INTO l_pay_pspl_id,l_pay_obj_number,l_proposed_sal_n, l_dummy_change_date;
1448 if get_pay_proposal%found then
1449 close get_pay_proposal;
1450 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
1451 p_validate => false,
1452 p_pay_proposal_id => l_pay_pspl_id ,
1453 p_object_version_number => l_pay_obj_number,
1454 p_change_date => p_start_date,
1455 p_approved => 'Y',
1456 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1457 p_proposed_salary_warning => l_proposed_salary_warning,
1458 p_approved_warning => l_approved_warning,
1459 p_payroll_warning => l_payroll_warning,
1460 p_proposed_salary_n => l_proposed_sal_n,
1461 p_business_group_id => p_business_group_id);
1462
1463 else
1464 close get_pay_proposal;
1465 end if;
1466 */
1467 --
1468 --bug fix 6310975
1469
1470 hr_utility.set_location('hrhirapl.make_secondary',500);
1471 IF l_chk_assg_end_dated <> 'Y' THEN
1472
1473
1474 OPEN get_pay_proposal(l_asg_rec.assignment_id);
1475 FETCH get_pay_proposal INTO l_pay_pspl_id,l_pay_obj_number,l_proposed_sal_n, l_dummy_change_date;
1476 if get_pay_proposal%found then
1477 close get_pay_proposal;
1478 hr_utility.set_location('hrhirapl.make_secondary',501);
1479 hr_utility.set_location('hrhirapl.make_secondary '||l_asg_rec.assignment_id,502);
1480 hr_utility.set_location(' make_secondary.l_dummy_change_date '||l_dummy_change_date,502);
1481 hr_utility.set_location(' make_secondary.l_pay_pspl_id '||l_pay_pspl_id,502);
1482 hr_utility.set_location(' make_secondary.l_proposed_sal_n '|| l_proposed_sal_n,502);
1483
1484 -- fix for the bug 7636109 passing the value as null as requested by sal admin team.
1485 l_pay_pspl_id:=null;
1486 l_pay_obj_number:=null;
1487
1488 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
1489 p_validate => false,
1490 p_pay_proposal_id => l_pay_pspl_id ,
1491 p_object_version_number => l_pay_obj_number,
1492 p_change_date => p_start_date,
1493 p_assignment_id => l_asg_rec.assignment_id, -- fix for the bug 7636109
1494
1495 p_approved => 'Y',
1496 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1497 p_proposed_salary_warning => l_proposed_salary_warning,
1498 p_approved_warning => l_approved_warning,
1499 p_payroll_warning => l_payroll_warning,
1500 p_proposed_salary_n => l_proposed_sal_n,
1501 p_business_group_id => p_business_group_id);
1502
1503 else
1504 close get_pay_proposal;
1505 end if;
1506
1507 ELSE
1508
1509 OPEN get_pay_proposal(l_asg_rec.assignment_id);
1510 FETCH get_pay_proposal INTO l_pay_pspl_id,l_pay_obj_number,l_proposed_sal_n, l_dummy_change_date;
1511 if get_pay_proposal%found then
1512 close get_pay_proposal;
1513 hr_utility.set_location('hrhirapl.make_secondary',521);
1514 hr_utility.set_location('hrhirapl.make_secondary '||l_asg_rec.assignment_id,502);
1515
1516 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
1517 p_validate => false,
1518 p_pay_proposal_id => l_pay_pspl_id ,
1519 p_object_version_number => l_pay_obj_number,
1520 -- p_change_date => p_start_date,
1521 -- p_approved => 'Y',
1522 p_date_to => p_start_date -1,
1523 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1524 p_proposed_salary_warning => l_proposed_salary_warning,
1525 p_approved_warning => l_approved_warning,
1526 p_payroll_warning => l_payroll_warning,
1527 p_proposed_salary_n => l_proposed_sal_n,
1528 p_business_group_id => p_business_group_id);
1529 hr_utility.set_location('hrhirapl.make_secondary',522);
1530 else
1531 close get_pay_proposal;
1532 end if;
1533
1534 end if;
1535 hr_utility.set_location('hrhirapl.make_secondary',524);
1536
1537 --
1538 --bug fix 6310975
1539 --added by amigarg for bug 4882512 end
1540 --
1541 end loop;
1542 --
1543 close ass_cur;
1544 --
1545 /*else
1546 -- fix for the bug
1547 -- first process the primary assignment id so that the assignment number is
1548 -- correclty generated
1549 hr_utility.set_location('hrhirapl.make_secondary',400);
1550 open csr_ass_cur_for_primary;
1551 loop
1552 fetch csr_ass_cur_for_primary into l_asg_rec; --#2119831
1553 exit when csr_ass_cur_for_primary%NOTFOUND;
1554 --
1555 -- #2483319
1556 p_assignment_id := l_asg_rec.assignment_id;
1557 --
1558 --
1559 -- Ensure (R)etain or (E)nd date flags have not been set
1560 if hr_employee_applicant_api.is_convert(p_table
1561 ,l_asg_rec.assignment_id)
1562 then
1563 hr_utility.set_location('hrhirapl.make_secondary',401);
1564 hr_utility.trace(' asg id = '||to_char(l_asg_rec.assignment_id));
1565 hr_utility.trace(' start date = '||to_char(l_asg_rec.effective_start_date,'dd/mm/yy'));
1566
1567 -- +--------------------------------------------------+
1568 -- +--- End Date assignment type 'A' -----------------+
1569 -- +--------------------------------------------------+
1570 begin
1571 insert into per_assignments_f
1572 (assignment_id
1573 ,effective_start_date
1574 ,effective_end_date
1575 ,business_group_id
1576 ,grade_id
1577 ,position_id
1578 ,job_id
1579 ,assignment_status_type_id
1580 ,payroll_id
1581 ,location_id
1582 ,person_id
1583 ,organization_id
1584 ,people_group_id
1585 ,soft_coding_keyflex_id
1586 ,vacancy_id
1587 ,assignment_sequence
1588 ,assignment_type
1589 ,manager_flag
1590 ,primary_flag
1591 ,application_id
1592 ,assignment_number
1593 ,change_reason
1594 ,comment_id
1595 ,date_probation_end
1596 ,default_code_comb_id
1597 ,frequency
1598 ,internal_address_line
1599 ,normal_hours
1600 ,period_of_service_id
1601 ,probation_period
1602 ,probation_unit
1603 ,recruiter_id
1604 ,set_of_books_id
1605 ,special_ceiling_step_id
1606 ,supervisor_id
1607 ,time_normal_finish
1608 ,time_normal_start
1609 ,request_id
1610 ,program_application_id
1611 ,program_id
1612 ,program_update_date
1613 ,ass_attribute_category
1614 ,ass_attribute1
1615 ,ass_attribute2
1616 ,ass_attribute3
1617 ,ass_attribute4
1618 ,ass_attribute5
1619 ,ass_attribute6
1620 ,ass_attribute7
1621 ,ass_attribute8
1622 ,ass_attribute9
1623 ,ass_attribute10
1624 ,ass_attribute11
1625 ,ass_attribute12
1626 ,ass_attribute13
1627 ,ass_attribute14
1628 ,ass_attribute15
1629 ,ass_attribute16
1630 ,ass_attribute17
1631 ,ass_attribute18
1632 ,ass_attribute19
1633 ,ass_attribute20
1634 ,ass_attribute21
1635 ,ass_attribute22
1636 ,ass_attribute23
1637 ,ass_attribute24
1638 ,ass_attribute25
1639 ,ass_attribute26
1640 ,ass_attribute27
1641 ,ass_attribute28
1642 ,ass_attribute29
1643 ,ass_attribute30
1644 ,last_update_date
1645 ,last_updated_by
1646 ,last_update_login
1647 ,created_by
1648 ,creation_date
1649 ,pay_basis_id
1650 ,person_referred_by_id
1651 ,recruitment_activity_id
1652 ,source_organization_id
1653 ,source_type
1654 ,employment_category --columns added Bug 978981
1655 ,perf_review_period
1656 ,perf_review_period_frequency
1657 ,sal_review_period
1658 ,sal_review_period_frequency
1659 ,bargaining_unit_code
1660 ,labour_union_member_flag
1661 ,hourly_salaried_code
1662 ,title
1663 ,job_post_source_name -- added for 4486233
1664 ,supervisor_assignment_id) ---#4053244
1665 values
1666 (l_asg_rec.assignment_id
1667 ,l_asg_rec.effective_start_date
1668 ,p_start_date - 1
1669 ,l_asg_rec.business_group_id
1670 ,l_asg_rec.grade_id
1671 ,l_asg_rec.position_id
1672 ,l_asg_rec.job_id
1673 ,l_asg_rec.assignment_status_type_id
1674 ,l_asg_rec.payroll_id
1675 ,l_asg_rec.location_id
1676 ,l_asg_rec.person_id
1677 ,l_asg_rec.organization_id
1678 ,l_asg_rec.people_group_id
1679 ,l_asg_rec.soft_coding_keyflex_id
1680 ,l_asg_rec.vacancy_id
1681 ,l_asg_rec.assignment_sequence
1682 ,l_asg_rec.assignment_type
1683 ,l_asg_rec.manager_flag
1684 ,l_asg_rec.primary_flag
1685 ,l_asg_rec.application_id
1686 ,l_asg_rec.assignment_number
1687 ,l_asg_rec.change_reason
1688 ,l_asg_rec.comment_id
1689 ,l_asg_rec.date_probation_end
1690 ,l_asg_rec.default_code_comb_id
1691 ,l_asg_rec.frequency
1692 ,l_asg_rec.internal_address_line
1693 ,l_asg_rec.normal_hours
1694 ,l_asg_rec.period_of_service_id
1695 ,l_asg_rec.probation_period
1696 ,l_asg_rec.probation_unit
1697 ,l_asg_rec.recruiter_id
1698 ,l_asg_rec.set_of_books_id
1699 ,l_asg_rec.special_ceiling_step_id
1700 ,l_asg_rec.supervisor_id
1701 ,l_asg_rec.time_normal_finish
1702 ,l_asg_rec.time_normal_start
1703 ,l_asg_rec.request_id
1704 ,l_asg_rec.program_application_id
1705 ,l_asg_rec.program_id
1706 ,l_asg_rec.program_update_date
1707 ,l_asg_rec.ass_attribute_category
1708 ,l_asg_rec.ass_attribute1
1709 ,l_asg_rec.ass_attribute2
1710 ,l_asg_rec.ass_attribute3
1711 ,l_asg_rec.ass_attribute4
1712 ,l_asg_rec.ass_attribute5
1713 ,l_asg_rec.ass_attribute6
1714 ,l_asg_rec.ass_attribute7
1715 ,l_asg_rec.ass_attribute8
1716 ,l_asg_rec.ass_attribute9
1717 ,l_asg_rec.ass_attribute10
1718 ,l_asg_rec.ass_attribute11
1719 ,l_asg_rec.ass_attribute12
1720 ,l_asg_rec.ass_attribute13
1721 ,l_asg_rec.ass_attribute14
1722 ,l_asg_rec.ass_attribute15
1723 ,l_asg_rec.ass_attribute16
1724 ,l_asg_rec.ass_attribute17
1725 ,l_asg_rec.ass_attribute18
1726 ,l_asg_rec.ass_attribute19
1727 ,l_asg_rec.ass_attribute20
1728 ,l_asg_rec.ass_attribute21
1729 ,l_asg_rec.ass_attribute22
1730 ,l_asg_rec.ass_attribute23
1731 ,l_asg_rec.ass_attribute24
1732 ,l_asg_rec.ass_attribute25
1733 ,l_asg_rec.ass_attribute26
1734 ,l_asg_rec.ass_attribute27
1735 ,l_asg_rec.ass_attribute28
1736 ,l_asg_rec.ass_attribute29
1737 ,l_asg_rec.ass_attribute30
1738 ,p_current_date
1739 ,p_user_id
1740 ,p_login_id
1741 ,l_asg_rec.created_by
1742 ,l_asg_rec.creation_date
1743 ,l_asg_rec.pay_basis_id
1744 ,l_asg_rec.person_referred_by_id
1745 ,l_asg_rec.recruitment_activity_id
1746 ,l_asg_rec.source_organization_id
1747 ,l_asg_rec.source_type
1748 ,l_asg_rec.employment_category -- columns added Bug 978981
1749 ,l_asg_rec.perf_review_period
1750 ,l_asg_rec.perf_review_period_frequency
1751 ,l_asg_rec.sal_review_period
1752 ,l_asg_rec.sal_review_period_frequency
1753 ,l_asg_rec.bargaining_unit_code
1754 ,l_asg_rec.labour_union_member_flag
1755 ,l_asg_rec.hourly_salaried_code
1756 ,l_asg_rec.title
1757 ,l_asg_rec.job_post_source_name -- added for 4486233
1758 ,l_asg_rec.supervisor_assignment_id); ---#4053244
1759 exception
1760 when others then
1761 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1762 hr_utility.set_message_token('PROCEDURE','make_secondary => ASGID: '
1763 ||to_char(l_asg_rec.assignment_id));
1764 hr_utility.set_message_token('STEP','2');
1765 hr_utility.raise_error;
1766
1767 end;
1768 -- +----------- END end date of assignment -----------+
1769 -- +--------------------------------------------------+
1770 --
1771 -- +--------------------------------------------------+
1772 -- +--- Convert assignment into secondary ------------+
1773 -- +--------------------------------------------------+
1774 -- # 2582838
1775 -- Bug - 401669
1776 -- select all ass_attribute columns
1777 --
1778 l_ass_attribute1 := l_asg_rec.ass_attribute1;
1779 l_ass_attribute2 := l_asg_rec.ass_attribute2;
1780 l_ass_attribute3 := l_asg_rec.ass_attribute3;
1781 l_ass_attribute4 := l_asg_rec.ass_attribute4;
1782 l_ass_attribute5 := l_asg_rec.ass_attribute5;
1783 l_ass_attribute6 := l_asg_rec.ass_attribute6;
1784 l_ass_attribute7 := l_asg_rec.ass_attribute7;
1785 l_ass_attribute8 := l_asg_rec.ass_attribute8;
1786 l_ass_attribute9 := l_asg_rec.ass_attribute9;
1787 l_ass_attribute10 := l_asg_rec.ass_attribute10;
1788 l_ass_attribute11 := l_asg_rec.ass_attribute11;
1789 l_ass_attribute12 := l_asg_rec.ass_attribute12;
1790 l_ass_attribute13 := l_asg_rec.ass_attribute13;
1791 l_ass_attribute14 := l_asg_rec.ass_attribute14;
1792 l_ass_attribute15 := l_asg_rec.ass_attribute15;
1793 l_ass_attribute16 := l_asg_rec.ass_attribute16;
1794 l_ass_attribute17 := l_asg_rec.ass_attribute17;
1795 l_ass_attribute18 := l_asg_rec.ass_attribute18;
1796 l_ass_attribute19 := l_asg_rec.ass_attribute19;
1797 l_ass_attribute20 := l_asg_rec.ass_attribute20;
1798 l_ass_attribute21 := l_asg_rec.ass_attribute21;
1799 l_ass_attribute22 := l_asg_rec.ass_attribute22;
1800 l_ass_attribute23 := l_asg_rec.ass_attribute23;
1801 l_ass_attribute24 := l_asg_rec.ass_attribute24;
1802 l_ass_attribute25 := l_asg_rec.ass_attribute25;
1803 l_ass_attribute26 := l_asg_rec.ass_attribute26;
1804 l_ass_attribute27 := l_asg_rec.ass_attribute27;
1805 l_ass_attribute28 := l_asg_rec.ass_attribute28;
1806 l_ass_attribute29 := l_asg_rec.ass_attribute29;
1807 l_ass_attribute30 := l_asg_rec.ass_attribute30;
1808
1809 open get_application_column_name;
1810 loop
1811 fetch get_application_column_name into l_app_col_name;
1812 exit when get_application_column_name%NOTFOUND;
1813 --
1814 if l_app_col_name = 'ASS_ATTRIBUTE1' then
1815 l_ass_attribute1 := NULL;
1816 end if;
1817 if l_app_col_name = 'ASS_ATTRIBUTE2' then
1818 l_ass_attribute2 := NULL;
1819 end if;
1820 if l_app_col_name = 'ASS_ATTRIBUTE3' then
1821 l_ass_attribute3 := NULL;
1822 end if;
1823 if l_app_col_name = 'ASS_ATTRIBUTE4' then
1824 l_ass_attribute4 := NULL;
1825 end if;
1826 if l_app_col_name = 'ASS_ATTRIBUTE5' then
1827 l_ass_attribute5 := NULL;
1828 end if;
1829 if l_app_col_name = 'ASS_ATTRIBUTE6' then
1830 l_ass_attribute6 := NULL;
1831 end if;
1832 if l_app_col_name = 'ASS_ATTRIBUTE7' then
1833 l_ass_attribute7 := NULL;
1834 end if;
1835 if l_app_col_name = 'ASS_ATTRIBUTE8' then
1836 l_ass_attribute8 := NULL;
1837 end if;
1838 if l_app_col_name = 'ASS_ATTRIBUTE9' then
1839 l_ass_attribute9 := NULL;
1840 end if;
1841 if l_app_col_name = 'ASS_ATTRIBUTE10' then
1842 l_ass_attribute10 := NULL;
1843 end if;
1844 if l_app_col_name = 'ASS_ATTRIBUTE11' then
1845 l_ass_attribute11 := NULL;
1846 end if;
1847 if l_app_col_name = 'ASS_ATTRIBUTE12' then
1848 l_ass_attribute12 := NULL;
1849 end if;
1850 if l_app_col_name = 'ASS_ATTRIBUTE13' then
1851 l_ass_attribute13 := NULL;
1852 end if;
1853 if l_app_col_name = 'ASS_ATTRIBUTE14' then
1854 l_ass_attribute14 := NULL;
1855 end if;
1856 if l_app_col_name = 'ASS_ATTRIBUTE15' then
1857 l_ass_attribute15 := NULL;
1858 end if;
1859 if l_app_col_name = 'ASS_ATTRIBUTE16' then
1860 l_ass_attribute16 := NULL;
1861 end if;
1862 if l_app_col_name = 'ASS_ATTRIBUTE17' then
1863 l_ass_attribute17 := NULL;
1864 end if;
1865 if l_app_col_name = 'ASS_ATTRIBUTE18' then
1866 l_ass_attribute18 := NULL;
1867 end if;
1868 if l_app_col_name = 'ASS_ATTRIBUTE19' then
1869 l_ass_attribute19 := NULL;
1870 end if;
1871 if l_app_col_name = 'ASS_ATTRIBUTE20' then
1872 l_ass_attribute20 := NULL;
1873 end if;
1874 if l_app_col_name = 'ASS_ATTRIBUTE21' then
1875 l_ass_attribute21 := NULL;
1876 end if;
1877 if l_app_col_name = 'ASS_ATTRIBUTE22' then
1878 l_ass_attribute22 := NULL;
1879 end if;
1880 if l_app_col_name = 'ASS_ATTRIBUTE23' then
1881 l_ass_attribute23 := NULL;
1882 end if;
1883 if l_app_col_name = 'ASS_ATTRIBUTE24' then
1884 l_ass_attribute24 := NULL;
1885 end if;
1886 if l_app_col_name = 'ASS_ATTRIBUTE25' then
1887 l_ass_attribute25 := NULL;
1888 end if;
1889 if l_app_col_name = 'ASS_ATTRIBUTE26' then
1890 l_ass_attribute26 := NULL;
1891 end if;
1892 if l_app_col_name = 'ASS_ATTRIBUTE27' then
1893 l_ass_attribute27 := NULL;
1894 end if;
1895 if l_app_col_name = 'ASS_ATTRIBUTE28' then
1896 l_ass_attribute28 := NULL;
1897 end if;
1898 if l_app_col_name = 'ASS_ATTRIBUTE29' then
1899 l_ass_attribute29 := NULL;
1900 end if;
1901 if l_app_col_name = 'ASS_ATTRIBUTE30' then
1902 l_ass_attribute30 := NULL;
1903 end if;
1904 end loop;
1905 --
1906 close get_application_column_name;
1907 -- +-----------------------------------------------------+
1908 --
1909 hrentmnt.check_payroll_changes_asg(p_assignment_id
1910 ,NULL
1911 ,'INSERT'
1912 ,p_start_date
1913 ,p_end_of_time);
1914 --
1915 -- Before doing the update make sure that what we are doing is valid
1916 -- especially for positions.
1917 --
1918 per_asg_bus1.chk_frozen_single_pos
1919 (p_assignment_id => p_assignment_id,
1920 p_position_id => l_asg_rec.position_id,
1921 p_effective_date => p_start_date);
1922 --
1923 --
1924 hr_assignment.gen_new_ass_sequence
1925 ( p_person_id
1926 , 'E'
1927 , p_assignment_sequence
1928 );
1929 --
1930 hr_assignment.gen_new_ass_number
1931 (p_assignment_id
1932 ,p_business_group_id
1933 ,p_employee_number
1934 ,p_assignment_sequence
1935 ,p_assignment_number);
1936 --
1937 hr_utility.set_location('hrhirapl.make_secondary',402);
1938 begin
1939 update per_assignments_f pa
1940 set pa.assignment_status_type_id = p_assignment_status_type_id
1941 , pa.assignment_type = 'E'
1942 , pa.effective_start_date = p_start_date
1943 , pa.effective_end_date = p_end_of_time
1944 , pa.period_of_service_id = v_period_of_service_id
1945 , pa.primary_flag = 'N'
1946 , pa.assignment_number = p_assignment_number
1947 , pa.assignment_sequence = p_assignment_sequence
1948 , pa.last_update_date = p_current_date
1949 , pa.last_updated_by = p_user_id
1950 , pa.last_update_login = p_login_id
1951 , pa.set_of_books_id = p_set_of_books_id
1952 , pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E'
1953 ,pa.ass_attribute_category)
1954 , pa.ass_attribute1 = l_ass_attribute1
1955 , pa.ass_attribute2 = l_ass_attribute2
1956 , pa.ass_attribute3 = l_ass_attribute3
1957 , pa.ass_attribute4 = l_ass_attribute4
1958 , pa.ass_attribute5 = l_ass_attribute5
1959 , pa.ass_attribute6 = l_ass_attribute6
1960 , pa.ass_attribute7 = l_ass_attribute7
1961 , pa.ass_attribute8 = l_ass_attribute8
1962 , pa.ass_attribute9 = l_ass_attribute9
1963 , pa.ass_attribute10 = l_ass_attribute10
1964 , pa.ass_attribute11 = l_ass_attribute11
1965 , pa.ass_attribute12 = l_ass_attribute12
1966 , pa.ass_attribute13 = l_ass_attribute13
1967 , pa.ass_attribute14 = l_ass_attribute14
1968 , pa.ass_attribute15 = l_ass_attribute15
1969 , pa.ass_attribute16 = l_ass_attribute16
1970 , pa.ass_attribute17 = l_ass_attribute17
1971 , pa.ass_attribute18 = l_ass_attribute18
1972 , pa.ass_attribute19 = l_ass_attribute19
1973 , pa.ass_attribute20 = l_ass_attribute20
1974 , pa.ass_attribute21 = l_ass_attribute21
1975 , pa.ass_attribute22 = l_ass_attribute22
1976 , pa.ass_attribute23 = l_ass_attribute23
1977 , pa.ass_attribute24 = l_ass_attribute24
1978 , pa.ass_attribute25 = l_ass_attribute25
1979 , pa.ass_attribute26 = l_ass_attribute26
1980 , pa.ass_attribute27 = l_ass_attribute27
1981 , pa.ass_attribute28 = l_ass_attribute28
1982 , pa.ass_attribute29 = l_ass_attribute29
1983 , pa.ass_attribute30 = l_ass_attribute30
1984 where current of csr_ass_cur_for_primary; -- pa.rowid = p_rowid;
1985 exception
1986 when others then
1987 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1988 hr_utility.set_message_token('PROCEDURE','make_secondary => ASGID: '
1989 ||to_char(l_asg_rec.assignment_id));
1990 hr_utility.set_message_token('STEP','3');
1991 hr_utility.raise_error;
1992
1993 end;
1994 -- Start of fix 3564129
1995 if l_asg_rec.vacancy_id is not null then --fix for bug8488222
1996 IRC_ASG_STATUS_API.create_irc_asg_status
1997 (p_assignment_id => p_assignment_id
1998 ,p_assignment_status_type_id => p_assignment_status_type_id
1999 ,p_status_change_date => p_session_date
2000 ,p_assignment_status_id => l_asg_status_id
2001 ,p_object_version_number => l_asg_status_ovn);
2002 end if;
2003 -- End of fix 3564129
2004 -- Bug 401669
2005 --
2006 hr_utility.set_location('hrhirapl.make_secondary',4);
2007 --
2008 hr_assignment.load_budget_values(p_assignment_id
2009 ,p_business_group_id
2010 ,p_user_id
2011 ,p_login_id
2012 ,p_start_date
2013 ,p_end_of_time
2014 );
2015 --
2016 hrentmnt.maintain_entries_asg(p_assignment_id
2017 ,p_business_group_id
2018 ,'HIRE_APPL' -- ,'ASG_CRITERIA' for bug 5547271
2019 ,NULL
2020 ,NULL
2021 ,NULL
2022 ,'INSERT'
2023 ,p_start_date
2024 ,p_end_of_time);
2025 -- set assignment number back to null;
2026 p_assignment_number := NULL;
2027 -- +--------------------------------------------------+
2028 -- +--- END Convert assignment into secondary --------+
2029 -- +--------------------------------------------------+
2030 --
2031 -- Did user explicity choose END Date ?
2032 elsif hr_employee_applicant_api.end_date_exists(p_table
2033 ,l_asg_rec.assignment_id) = 1
2034 then
2035 -- +--------------------------------------------------+
2036 -- +--- End Date assignment --------------------------+
2037 -- +--------------------------------------------------+
2038 --
2039 hr_utility.set_location('hrhirapl.make_secondary',5);
2040 begin
2041 update per_assignments_f
2042 set effective_end_date = p_start_date -1
2043 where current of csr_ass_cur_for_primary;
2044 exception
2045 when others then
2046 hr_utility.set_location('hrhirapl.make_secondary ASGID : '||
2047 to_char(l_asg_rec.assignment_id),66);
2048 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2049 hr_utility.set_message_token('PROCEDURE','make_secondary ');
2050 hr_utility.set_message_token('STEP','4');
2051 hr_utility.raise_error;
2052 end;
2053 end if; -- convert flag is set.
2054 --
2055
2056 --added by amigarg for bug 4882512 start
2057
2058 OPEN get_pay_proposal(l_asg_rec.assignment_id);
2059 FETCH get_pay_proposal INTO l_pay_pspl_id,l_pay_obj_number,l_proposed_sal_n, l_dummy_change_date;
2060 if get_pay_proposal%found then
2061 close get_pay_proposal;
2062 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
2063 p_validate => false,
2064 p_pay_proposal_id => l_pay_pspl_id ,
2065 p_object_version_number => l_pay_obj_number,
2066 p_change_date => p_start_date,
2067 p_approved => 'Y',
2068 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
2069 p_proposed_salary_warning => l_proposed_salary_warning,
2070 p_approved_warning => l_approved_warning,
2071 p_payroll_warning => l_payroll_warning,
2072 p_proposed_salary_n => l_proposed_sal_n,
2073 p_business_group_id => p_business_group_id);
2074
2075 else
2076 close get_pay_proposal;
2077 end if;
2078
2079 --added by amigarg for bug 4882512 end
2080
2081 end loop;
2082 --
2083 close csr_ass_cur_for_primary;
2084
2085 -- now process all seconday or non primary assignments
2086
2087
2088 hr_utility.set_location('hrhirapl.make_secondary',403);
2089 open csr_ass_cur_for_nonprimary;
2090 loop
2091 fetch csr_ass_cur_for_nonprimary into l_asg_rec; --#2119831
2092 exit when csr_ass_cur_for_nonprimary%NOTFOUND;
2093 --
2094 -- #2483319
2095 p_assignment_id := l_asg_rec.assignment_id;
2096 --
2097 --
2098 -- Ensure (R)etain or (E)nd date flags have not been set
2099 if hr_employee_applicant_api.is_convert(p_table
2100 ,l_asg_rec.assignment_id)
2101 then
2102 hr_utility.set_location('hrhirapl.make_secondary',404);
2103 hr_utility.trace(' asg id = '||to_char(l_asg_rec.assignment_id));
2104 hr_utility.trace(' start date = '||to_char(l_asg_rec.effective_start_date,'dd/mm/yy'));
2105
2106 -- +--------------------------------------------------+
2107 -- +--- End Date assignment type 'A' -----------------+
2108 -- +--------------------------------------------------+
2109 begin
2110 insert into per_assignments_f
2111 (assignment_id
2112 ,effective_start_date
2113 ,effective_end_date
2114 ,business_group_id
2115 ,grade_id
2116 ,position_id
2117 ,job_id
2118 ,assignment_status_type_id
2119 ,payroll_id
2120 ,location_id
2121 ,person_id
2122 ,organization_id
2123 ,people_group_id
2124 ,soft_coding_keyflex_id
2125 ,vacancy_id
2126 ,assignment_sequence
2127 ,assignment_type
2128 ,manager_flag
2129 ,primary_flag
2130 ,application_id
2131 ,assignment_number
2132 ,change_reason
2133 ,comment_id
2134 ,date_probation_end
2135 ,default_code_comb_id
2136 ,frequency
2137 ,internal_address_line
2138 ,normal_hours
2139 ,period_of_service_id
2140 ,probation_period
2141 ,probation_unit
2142 ,recruiter_id
2143 ,set_of_books_id
2144 ,special_ceiling_step_id
2145 ,supervisor_id
2146 ,time_normal_finish
2147 ,time_normal_start
2148 ,request_id
2149 ,program_application_id
2150 ,program_id
2151 ,program_update_date
2152 ,ass_attribute_category
2153 ,ass_attribute1
2154 ,ass_attribute2
2155 ,ass_attribute3
2156 ,ass_attribute4
2157 ,ass_attribute5
2158 ,ass_attribute6
2159 ,ass_attribute7
2160 ,ass_attribute8
2161 ,ass_attribute9
2162 ,ass_attribute10
2163 ,ass_attribute11
2164 ,ass_attribute12
2165 ,ass_attribute13
2166 ,ass_attribute14
2167 ,ass_attribute15
2168 ,ass_attribute16
2169 ,ass_attribute17
2170 ,ass_attribute18
2171 ,ass_attribute19
2172 ,ass_attribute20
2173 ,ass_attribute21
2174 ,ass_attribute22
2175 ,ass_attribute23
2176 ,ass_attribute24
2177 ,ass_attribute25
2178 ,ass_attribute26
2179 ,ass_attribute27
2180 ,ass_attribute28
2181 ,ass_attribute29
2182 ,ass_attribute30
2183 ,last_update_date
2184 ,last_updated_by
2185 ,last_update_login
2186 ,created_by
2187 ,creation_date
2188 ,pay_basis_id
2189 ,person_referred_by_id
2190 ,recruitment_activity_id
2191 ,source_organization_id
2192 ,source_type
2193 ,employment_category -- columns added Bug 978981
2194 ,perf_review_period
2195 ,perf_review_period_frequency
2196 ,sal_review_period
2197 ,sal_review_period_frequency
2198 ,bargaining_unit_code
2199 ,labour_union_member_flag
2200 ,hourly_salaried_code
2201 ,title
2202 ,job_post_source_name -- added for 4486233
2203 ,supervisor_assignment_id) ---#4053244
2204 values
2205 (l_asg_rec.assignment_id
2206 ,l_asg_rec.effective_start_date
2207 ,p_start_date - 1
2208 ,l_asg_rec.business_group_id
2209 ,l_asg_rec.grade_id
2210 ,l_asg_rec.position_id
2211 ,l_asg_rec.job_id
2212 ,l_asg_rec.assignment_status_type_id
2213 ,l_asg_rec.payroll_id
2214 ,l_asg_rec.location_id
2215 ,l_asg_rec.person_id
2216 ,l_asg_rec.organization_id
2217 ,l_asg_rec.people_group_id
2218 ,l_asg_rec.soft_coding_keyflex_id
2219 ,l_asg_rec.vacancy_id
2220 ,l_asg_rec.assignment_sequence
2221 ,l_asg_rec.assignment_type
2222 ,l_asg_rec.manager_flag
2223 ,l_asg_rec.primary_flag
2224 ,l_asg_rec.application_id
2225 ,l_asg_rec.assignment_number
2226 ,l_asg_rec.change_reason
2227 ,l_asg_rec.comment_id
2228 ,l_asg_rec.date_probation_end
2229 ,l_asg_rec.default_code_comb_id
2230 ,l_asg_rec.frequency
2231 ,l_asg_rec.internal_address_line
2232 ,l_asg_rec.normal_hours
2233 ,l_asg_rec.period_of_service_id
2234 ,l_asg_rec.probation_period
2235 ,l_asg_rec.probation_unit
2236 ,l_asg_rec.recruiter_id
2237 ,l_asg_rec.set_of_books_id
2238 ,l_asg_rec.special_ceiling_step_id
2239 ,l_asg_rec.supervisor_id
2240 ,l_asg_rec.time_normal_finish
2241 ,l_asg_rec.time_normal_start
2242 ,l_asg_rec.request_id
2243 ,l_asg_rec.program_application_id
2244 ,l_asg_rec.program_id
2245 ,l_asg_rec.program_update_date
2246 ,l_asg_rec.ass_attribute_category
2247 ,l_asg_rec.ass_attribute1
2248 ,l_asg_rec.ass_attribute2
2249 ,l_asg_rec.ass_attribute3
2250 ,l_asg_rec.ass_attribute4
2251 ,l_asg_rec.ass_attribute5
2252 ,l_asg_rec.ass_attribute6
2253 ,l_asg_rec.ass_attribute7
2254 ,l_asg_rec.ass_attribute8
2255 ,l_asg_rec.ass_attribute9
2256 ,l_asg_rec.ass_attribute10
2257 ,l_asg_rec.ass_attribute11
2258 ,l_asg_rec.ass_attribute12
2259 ,l_asg_rec.ass_attribute13
2260 ,l_asg_rec.ass_attribute14
2261 ,l_asg_rec.ass_attribute15
2262 ,l_asg_rec.ass_attribute16
2263 ,l_asg_rec.ass_attribute17
2264 ,l_asg_rec.ass_attribute18
2265 ,l_asg_rec.ass_attribute19
2266 ,l_asg_rec.ass_attribute20
2267 ,l_asg_rec.ass_attribute21
2268 ,l_asg_rec.ass_attribute22
2269 ,l_asg_rec.ass_attribute23
2270 ,l_asg_rec.ass_attribute24
2271 ,l_asg_rec.ass_attribute25
2272 ,l_asg_rec.ass_attribute26
2273 ,l_asg_rec.ass_attribute27
2274 ,l_asg_rec.ass_attribute28
2275 ,l_asg_rec.ass_attribute29
2276 ,l_asg_rec.ass_attribute30
2277 ,p_current_date
2278 ,p_user_id
2279 ,p_login_id
2280 ,l_asg_rec.created_by
2281 ,l_asg_rec.creation_date
2282 ,l_asg_rec.pay_basis_id
2283 ,l_asg_rec.person_referred_by_id
2284 ,l_asg_rec.recruitment_activity_id
2285 ,l_asg_rec.source_organization_id
2286 ,l_asg_rec.source_type
2287 ,l_asg_rec.employment_category -- columns added Bug 978981
2288 ,l_asg_rec.perf_review_period
2289 ,l_asg_rec.perf_review_period_frequency
2290 ,l_asg_rec.sal_review_period
2291 ,l_asg_rec.sal_review_period_frequency
2292 ,l_asg_rec.bargaining_unit_code
2293 ,l_asg_rec.labour_union_member_flag
2294 ,l_asg_rec.hourly_salaried_code
2295 ,l_asg_rec.title
2296 ,l_asg_rec.job_post_source_name -- added for 4486233
2297 ,l_asg_rec.supervisor_assignment_id); ---#4053244
2298 exception
2299 when others then
2300 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2301 hr_utility.set_message_token('PROCEDURE','make_secondary => ASGID: '
2302 ||to_char(l_asg_rec.assignment_id));
2303 hr_utility.set_message_token('STEP','2');
2304 hr_utility.raise_error;
2305
2306 end;
2307 -- +----------- END end date of assignment -----------+
2308 -- +--------------------------------------------------+
2309 --
2310 -- +--------------------------------------------------+
2311 -- +--- Convert assignment into secondary ------------+
2312 -- +--------------------------------------------------+
2313 -- # 2582838
2314 -- Bug - 401669
2315 -- select all ass_attribute columns
2316 --
2317 l_ass_attribute1 := l_asg_rec.ass_attribute1;
2318 l_ass_attribute2 := l_asg_rec.ass_attribute2;
2319 l_ass_attribute3 := l_asg_rec.ass_attribute3;
2320 l_ass_attribute4 := l_asg_rec.ass_attribute4;
2321 l_ass_attribute5 := l_asg_rec.ass_attribute5;
2322 l_ass_attribute6 := l_asg_rec.ass_attribute6;
2323 l_ass_attribute7 := l_asg_rec.ass_attribute7;
2324 l_ass_attribute8 := l_asg_rec.ass_attribute8;
2325 l_ass_attribute9 := l_asg_rec.ass_attribute9;
2326 l_ass_attribute10 := l_asg_rec.ass_attribute10;
2327 l_ass_attribute11 := l_asg_rec.ass_attribute11;
2328 l_ass_attribute12 := l_asg_rec.ass_attribute12;
2329 l_ass_attribute13 := l_asg_rec.ass_attribute13;
2330 l_ass_attribute14 := l_asg_rec.ass_attribute14;
2331 l_ass_attribute15 := l_asg_rec.ass_attribute15;
2332 l_ass_attribute16 := l_asg_rec.ass_attribute16;
2333 l_ass_attribute17 := l_asg_rec.ass_attribute17;
2334 l_ass_attribute18 := l_asg_rec.ass_attribute18;
2335 l_ass_attribute19 := l_asg_rec.ass_attribute19;
2336 l_ass_attribute20 := l_asg_rec.ass_attribute20;
2337 l_ass_attribute21 := l_asg_rec.ass_attribute21;
2338 l_ass_attribute22 := l_asg_rec.ass_attribute22;
2339 l_ass_attribute23 := l_asg_rec.ass_attribute23;
2340 l_ass_attribute24 := l_asg_rec.ass_attribute24;
2341 l_ass_attribute25 := l_asg_rec.ass_attribute25;
2342 l_ass_attribute26 := l_asg_rec.ass_attribute26;
2343 l_ass_attribute27 := l_asg_rec.ass_attribute27;
2344 l_ass_attribute28 := l_asg_rec.ass_attribute28;
2345 l_ass_attribute29 := l_asg_rec.ass_attribute29;
2346 l_ass_attribute30 := l_asg_rec.ass_attribute30;
2347
2348 open get_application_column_name;
2349 loop
2350 fetch get_application_column_name into l_app_col_name;
2351 exit when get_application_column_name%NOTFOUND;
2352 --
2353 if l_app_col_name = 'ASS_ATTRIBUTE1' then
2354 l_ass_attribute1 := NULL;
2355 end if;
2356 if l_app_col_name = 'ASS_ATTRIBUTE2' then
2357 l_ass_attribute2 := NULL;
2358 end if;
2359 if l_app_col_name = 'ASS_ATTRIBUTE3' then
2360 l_ass_attribute3 := NULL;
2361 end if;
2362 if l_app_col_name = 'ASS_ATTRIBUTE4' then
2363 l_ass_attribute4 := NULL;
2364 end if;
2365 if l_app_col_name = 'ASS_ATTRIBUTE5' then
2366 l_ass_attribute5 := NULL;
2367 end if;
2368 if l_app_col_name = 'ASS_ATTRIBUTE6' then
2369 l_ass_attribute6 := NULL;
2370 end if;
2371 if l_app_col_name = 'ASS_ATTRIBUTE7' then
2372 l_ass_attribute7 := NULL;
2373 end if;
2374 if l_app_col_name = 'ASS_ATTRIBUTE8' then
2375 l_ass_attribute8 := NULL;
2376 end if;
2377 if l_app_col_name = 'ASS_ATTRIBUTE9' then
2378 l_ass_attribute9 := NULL;
2379 end if;
2380 if l_app_col_name = 'ASS_ATTRIBUTE10' then
2381 l_ass_attribute10 := NULL;
2382 end if;
2383 if l_app_col_name = 'ASS_ATTRIBUTE11' then
2384 l_ass_attribute11 := NULL;
2385 end if;
2386 if l_app_col_name = 'ASS_ATTRIBUTE12' then
2387 l_ass_attribute12 := NULL;
2388 end if;
2389 if l_app_col_name = 'ASS_ATTRIBUTE13' then
2390 l_ass_attribute13 := NULL;
2391 end if;
2392 if l_app_col_name = 'ASS_ATTRIBUTE14' then
2393 l_ass_attribute14 := NULL;
2394 end if;
2395 if l_app_col_name = 'ASS_ATTRIBUTE15' then
2396 l_ass_attribute15 := NULL;
2397 end if;
2398 if l_app_col_name = 'ASS_ATTRIBUTE16' then
2399 l_ass_attribute16 := NULL;
2400 end if;
2401 if l_app_col_name = 'ASS_ATTRIBUTE17' then
2402 l_ass_attribute17 := NULL;
2403 end if;
2404 if l_app_col_name = 'ASS_ATTRIBUTE18' then
2405 l_ass_attribute18 := NULL;
2406 end if;
2407 if l_app_col_name = 'ASS_ATTRIBUTE19' then
2408 l_ass_attribute19 := NULL;
2409 end if;
2410 if l_app_col_name = 'ASS_ATTRIBUTE20' then
2411 l_ass_attribute20 := NULL;
2412 end if;
2413 if l_app_col_name = 'ASS_ATTRIBUTE21' then
2414 l_ass_attribute21 := NULL;
2415 end if;
2416 if l_app_col_name = 'ASS_ATTRIBUTE22' then
2417 l_ass_attribute22 := NULL;
2418 end if;
2419 if l_app_col_name = 'ASS_ATTRIBUTE23' then
2420 l_ass_attribute23 := NULL;
2421 end if;
2422 if l_app_col_name = 'ASS_ATTRIBUTE24' then
2423 l_ass_attribute24 := NULL;
2424 end if;
2425 if l_app_col_name = 'ASS_ATTRIBUTE25' then
2426 l_ass_attribute25 := NULL;
2427 end if;
2428 if l_app_col_name = 'ASS_ATTRIBUTE26' then
2429 l_ass_attribute26 := NULL;
2430 end if;
2431 if l_app_col_name = 'ASS_ATTRIBUTE27' then
2432 l_ass_attribute27 := NULL;
2433 end if;
2434 if l_app_col_name = 'ASS_ATTRIBUTE28' then
2435 l_ass_attribute28 := NULL;
2436 end if;
2437 if l_app_col_name = 'ASS_ATTRIBUTE29' then
2438 l_ass_attribute29 := NULL;
2439 end if;
2440 if l_app_col_name = 'ASS_ATTRIBUTE30' then
2441 l_ass_attribute30 := NULL;
2442 end if;
2443 end loop;
2444 --
2445 close get_application_column_name;
2446 -- +-----------------------------------------------------+
2447 --
2448 hrentmnt.check_payroll_changes_asg(p_assignment_id
2449 ,NULL
2450 ,'INSERT'
2451 ,p_start_date
2452 ,p_end_of_time);
2453 --
2454 -- Before doing the update make sure that what we are doing is valid
2455 -- especially for positions.
2456 --
2457 per_asg_bus1.chk_frozen_single_pos
2458 (p_assignment_id => p_assignment_id,
2459 p_position_id => l_asg_rec.position_id,
2460 p_effective_date => p_start_date);
2461 --
2462 --
2463 hr_assignment.gen_new_ass_sequence
2464 ( p_person_id
2465 , 'E'
2466 , p_assignment_sequence
2467 );
2468 --
2469 hr_assignment.gen_new_ass_number
2470 (p_assignment_id
2471 ,p_business_group_id
2472 ,p_employee_number
2473 ,p_assignment_sequence
2474 ,p_assignment_number);
2475 --
2476 hr_utility.set_location('hrhirapl.make_secondary',405);
2477 begin
2478 update per_assignments_f pa
2479 set pa.assignment_status_type_id = p_assignment_status_type_id
2480 , pa.assignment_type = 'E'
2481 , pa.effective_start_date = p_start_date
2482 , pa.effective_end_date = p_end_of_time
2483 , pa.period_of_service_id = v_period_of_service_id
2484 , pa.primary_flag = 'N'
2485 , pa.assignment_number = p_assignment_number
2486 , pa.assignment_sequence = p_assignment_sequence
2487 , pa.last_update_date = p_current_date
2488 , pa.last_updated_by = p_user_id
2489 , pa.last_update_login = p_login_id
2490 , pa.set_of_books_id = p_set_of_books_id
2491 , pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E'
2492 ,pa.ass_attribute_category)
2493 , pa.ass_attribute1 = l_ass_attribute1
2494 , pa.ass_attribute2 = l_ass_attribute2
2495 , pa.ass_attribute3 = l_ass_attribute3
2496 , pa.ass_attribute4 = l_ass_attribute4
2497 , pa.ass_attribute5 = l_ass_attribute5
2498 , pa.ass_attribute6 = l_ass_attribute6
2499 , pa.ass_attribute7 = l_ass_attribute7
2500 , pa.ass_attribute8 = l_ass_attribute8
2501 , pa.ass_attribute9 = l_ass_attribute9
2502 , pa.ass_attribute10 = l_ass_attribute10
2503 , pa.ass_attribute11 = l_ass_attribute11
2504 , pa.ass_attribute12 = l_ass_attribute12
2505 , pa.ass_attribute13 = l_ass_attribute13
2506 , pa.ass_attribute14 = l_ass_attribute14
2507 , pa.ass_attribute15 = l_ass_attribute15
2508 , pa.ass_attribute16 = l_ass_attribute16
2509 , pa.ass_attribute17 = l_ass_attribute17
2510 , pa.ass_attribute18 = l_ass_attribute18
2511 , pa.ass_attribute19 = l_ass_attribute19
2512 , pa.ass_attribute20 = l_ass_attribute20
2513 , pa.ass_attribute21 = l_ass_attribute21
2514 , pa.ass_attribute22 = l_ass_attribute22
2515 , pa.ass_attribute23 = l_ass_attribute23
2516 , pa.ass_attribute24 = l_ass_attribute24
2517 , pa.ass_attribute25 = l_ass_attribute25
2518 , pa.ass_attribute26 = l_ass_attribute26
2519 , pa.ass_attribute27 = l_ass_attribute27
2520 , pa.ass_attribute28 = l_ass_attribute28
2521 , pa.ass_attribute29 = l_ass_attribute29
2522 , pa.ass_attribute30 = l_ass_attribute30
2523 where current of csr_ass_cur_for_nonprimary; -- pa.rowid = p_rowid;
2524 exception
2525 when others then
2526 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2527 hr_utility.set_message_token('PROCEDURE','make_secondary => ASGID: '
2528 ||to_char(l_asg_rec.assignment_id));
2529 hr_utility.set_message_token('STEP','3');
2530 hr_utility.raise_error;
2531
2532 end;
2533 -- Start of fix 3564129
2534 IRC_ASG_STATUS_API.create_irc_asg_status
2535 (p_assignment_id => p_assignment_id
2536 ,p_assignment_status_type_id => p_assignment_status_type_id
2537 ,p_status_change_date => p_session_date
2538 ,p_assignment_status_id => l_asg_status_id
2539 ,p_object_version_number => l_asg_status_ovn);
2540 -- End of fix 3564129
2541 -- Bug 401669
2542 --
2543 hr_utility.set_location('hrhirapl.make_secondary',406);
2544 --
2545 hr_assignment.load_budget_values(p_assignment_id
2546 ,p_business_group_id
2547 ,p_user_id
2548 ,p_login_id
2549 ,p_start_date
2550 ,p_end_of_time
2551 );
2552 --
2553 hrentmnt.maintain_entries_asg(p_assignment_id
2554 ,p_business_group_id
2555 ,'HIRE_APPL' --,'ASG_CRITERIA' for bug 5547271
2556 ,NULL
2557 ,NULL
2558 ,NULL
2559 ,'INSERT'
2560 ,p_start_date
2561 ,p_end_of_time);
2562 -- set assignment number back to null;
2563 p_assignment_number := NULL;
2564 -- +--------------------------------------------------+
2565 -- +--- END Convert assignment into secondary --------+
2566 -- +--------------------------------------------------+
2567 --
2568 -- Did user explicity choose END Date ?
2569 elsif hr_employee_applicant_api.end_date_exists(p_table
2570 ,l_asg_rec.assignment_id) = 1
2571 then
2572 -- +--------------------------------------------------+
2573 -- +--- End Date assignment --------------------------+
2574 -- +--------------------------------------------------+
2575 --
2576 hr_utility.set_location('hrhirapl.make_secondary',407);
2577 begin
2578 update per_assignments_f
2579 set effective_end_date = p_start_date -1
2580 where current of csr_ass_cur_for_nonprimary;
2581 exception
2582 when others then
2583 hr_utility.set_location('hrhirapl.make_secondary ASGID : '||
2584 to_char(l_asg_rec.assignment_id),408);
2585 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2586 hr_utility.set_message_token('PROCEDURE','make_secondary ');
2587 hr_utility.set_message_token('STEP','4');
2588 hr_utility.raise_error;
2589 end;
2590 end if; -- convert flag is set.
2591 --
2592
2593 --added by amigarg for bug 4882512 start
2594
2595 OPEN get_pay_proposal(l_asg_rec.assignment_id);
2596 FETCH get_pay_proposal INTO l_pay_pspl_id,l_pay_obj_number,l_proposed_sal_n, l_dummy_change_date;
2597 if get_pay_proposal%found then
2598 close get_pay_proposal;
2599 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
2600 p_validate => false,
2601 p_pay_proposal_id => l_pay_pspl_id ,
2602 p_object_version_number => l_pay_obj_number,
2603 p_change_date => p_start_date,
2604 p_approved => 'Y',
2605 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
2606 p_proposed_salary_warning => l_proposed_salary_warning,
2607 p_approved_warning => l_approved_warning,
2608 p_payroll_warning => l_payroll_warning,
2609 p_proposed_salary_n => l_proposed_sal_n,
2610 p_business_group_id => p_business_group_id);
2611
2612 else
2613 close get_pay_proposal;
2614 end if;
2615
2616 --added by amigarg for bug 4882512 end
2617
2618 end loop;
2619 --
2620 close csr_ass_cur_for_nonprimary;
2621 hr_utility.set_location('hrhirapl.make_secondary',409);
2622 end if;
2623 hr_utility.set_location('hrhirapl.make_secondary',410);
2624 -- end of bug 5498344
2625 commented for bug 5589928*/
2626 --
2627 -- add condition: number of updates should be the same as number
2628 -- of inserts
2629 /*
2630 if ass_cur%ROWCOUNT > v_count then
2631 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2632 hr_utility.set_message_token('PROCEDURE','make_secondary');
2633 hr_utility.set_message_token('STEP','2');
2634 hr_utility.raise_error;
2635 end if;
2636 */
2637 --
2638 --
2639 end make_secondary;
2640 -- +-----------------------------------------------------------------------+
2641 -- # 1769702
2642 -- When deleting future assignments, need to verify this action
2643 -- will not affect the element eligibility
2644 -- nor pending assignments actions exist.
2645
2646 procedure Verify_future_assignments(p_business_group_id IN INTEGER
2647 ,p_person_id IN INTEGER
2648 ,p_start_date IN DATE
2649 ) is
2650 --
2651 cursor fut_asg is
2652 select rowid, assignment_id, effective_start_date, effective_end_date
2653 from per_assignments_f
2654 where primary_flag = 'Y'
2655 and business_group_id + 0 = p_business_group_id
2656 and person_id = p_person_id
2657 and assignment_type = 'E'
2658 and effective_start_date > p_start_date
2659 for update of effective_start_date;
2660
2661 l_rowid ROWID;
2662 l_future_asg_id number;
2663 l_asg_start_date date;
2664 l_asg_end_date date;
2665
2666 begin
2667 hr_utility.set_location('IN Verify future assignments',901);
2668 open fut_asg;
2669 loop
2670 fetch fut_asg into l_rowid, l_future_asg_id,l_asg_start_date, l_asg_end_date
2671 ;
2672 exit when fut_asg%NOTFOUND;
2673
2674 begin
2675
2676 hrentmnt.check_payroll_changes_asg(l_future_asg_id
2677 ,NULL
2678 ,'DELETE'
2679 ,l_asg_start_date
2680 ,l_asg_end_date);
2681 --
2682 hrentmnt.maintain_entries_asg(l_future_asg_id
2683 ,p_business_group_id
2684 ,'HIRE_APPL' -- ,'ASG_CRITERIA' for bug 5547271
2685 ,NULL
2686 ,NULL
2687 ,NULL
2688 ,'DELETE'
2689 ,l_asg_start_date
2690 ,l_asg_end_date);
2691
2692 exception
2693 when others then
2694 close fut_asg;
2695 --
2696 -- Show any errors raised by previous routines
2697 --
2698 hr_utility.raise_error;
2699 end;
2700 end loop;
2701 close fut_asg;
2702 hr_utility.set_location('OUT Verify future assignments',903);
2703 end Verify_future_assignments;
2704 --
2705 --
2706 -- +-----------------------------------------------------------------------+
2707 PROCEDURE create_primary_assignment (p_business_group_id IN INTEGER
2708 ,p_person_id IN INTEGER
2709 ,p_new_primary_id IN INTEGER
2710 ,p_start_date IN DATE
2711 ,p_end_of_time IN DATE
2712 ,p_login_id IN INTEGER
2713 ,p_user_id IN INTEGER
2714 ,p_update_primary_flag IN VARCHAR2
2715 ,p_employee_number IN VARCHAR2
2716 ,p_set_of_books_id IN INTEGER
2717 ,p_emp_apl IN VARCHAR2
2718 ) is
2719 --
2720 -- Create a new primary assignment.
2721 --
2722 --
2723 p_assignment_number VARCHAR2(30);
2724 p_assignment_sequence INTEGER;
2725 p_rowid ROWID;
2726 -- # 1769702
2727 l_asg_end_date date;
2728 l_future_asg_id number;
2729 l_fut_start_date date;
2730 l_fut_end_date date;
2731 --
2732 l_grades_notequal varchar2(1) :='N'; -- bug 4736269
2733 -- Bug 518669. Increased length of l_col_name from 30 to 200, so it matches
2734 -- the max len of the column in the DB. Pashun. 16-Sep-97.
2735 --
2736 l_col_name VARCHAR2(200);
2737 cursor get_flex_def is
2738 select default_context_field_name
2739 from fnd_descriptive_flexs
2740 where application_id = 800 -- bug 5469726
2741 and descriptive_flexfield_name = 'PER_ASSIGNMENTS';
2742 --
2743 -- VT 06/14/00
2744 apl_asg_rec PER_ALL_ASSIGNMENTS_F%ROWTYPE;
2745 emp_asg_rec PER_ALL_ASSIGNMENTS_F%ROWTYPE;
2746 --
2747 cursor cur_apl_asg is
2748 select * from per_all_assignments_f paf
2749 where paf.assignment_id = p_new_primary_id
2750 and paf.business_group_id +0 = p_business_group_id
2751 and p_start_date between paf.effective_start_date
2752 and paf.effective_end_date;
2753 --
2754 cursor cur_emp_asg is
2755 select * from per_all_assignments_f paf
2756 where paf.person_id = p_person_id
2757 and paf.business_group_id +0 = p_business_group_id
2758 -- #1981550
2759 and paf.primary_flag = 'Y'
2760 and paf.assignment_type = 'E'
2761 and p_start_date between paf.effective_start_date
2762 -- #1981550
2763 and paf.effective_end_date;
2764
2765 -- Added for the bug 6497082 starts here
2766
2767 l_assignment_status_type_id number;
2768 l_assignment_status_id irc_assignment_statuses.assignment_status_id%type;
2769 l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
2770
2771 -- Added for the bug 6497082 Ends here
2772
2773
2774 -- Added for the bug 6512520 starts here
2775
2776 cursor get_pay_proposal(ass_id per_all_assignments_f.assignment_id%type) is
2777 select pay_proposal_id,object_version_number,proposed_salary_n, change_date
2778 from per_pay_proposals
2779 where assignment_id=ass_id
2780 and approved = 'N'
2781 order by change_date desc;
2782
2783 cursor get_pay_proposal_emp(ass_id per_all_assignments_f.assignment_id%type) is
2784 select pay_proposal_id,object_version_number,proposed_salary_n, change_date
2785 from per_pay_proposals
2786 where assignment_id=ass_id
2787 and approved = 'N'
2788 order by change_date desc;
2789
2790 l_apl_pay_pspl_id per_pay_proposals.pay_proposal_id%TYPE;
2791 l_apl_pay_obj_number per_pay_proposals.object_version_number%TYPE;
2792 l_apl_proposed_sal_n per_pay_proposals.proposed_salary_n%TYPE;
2793 l_apl_dummy_change_date per_pay_proposals.change_date%TYPE;
2794 l_emp_pay_pspl_id per_pay_proposals.pay_proposal_id%TYPE;
2795 l_emp_pay_obj_number per_pay_proposals.object_version_number%TYPE;
2796 l_emp_proposed_sal_n per_pay_proposals.proposed_salary_n%TYPE;
2797 l_emp_dummy_change_date per_pay_proposals.change_date%TYPE;
2798 l_emp_next_sal_date_warning boolean := false;
2799 l_emp_proposed_salary_warning boolean := false;
2800 l_emp_approved_warning boolean := false;
2801 l_emp_payroll_warning boolean := false;
2802
2803 -- Added for the bug 6512520 ends here
2804 --11853433
2805 l_irc_link_exists number := -1;
2806 l_irc_spl_status number;
2807 --11853433
2808
2809 -- Start changes for bug 9644377
2810
2811 Cursor csr_pay_actions(lp_assignment_id number) is
2812 select 'Y'
2813 from dual
2814 where exists
2815 (SELECT NULL
2816 FROM pay_payroll_actions pac,
2817 pay_assignment_actions act,
2818 per_assignments_f asg
2819 WHERE asg.person_id = p_person_id
2820 AND act.assignment_id = asg.assignment_id
2821 AND pac.payroll_action_id = act.payroll_action_id
2822 AND asg.assignment_id = lp_assignment_id
2823 AND asg.assignment_type = 'E'
2824 AND asg.primary_flag = 'Y'
2825 AND pac.action_status = 'C'
2826 AND ( p_start_date < pac.date_earned
2827 OR p_start_date < pac.effective_date));
2828
2829 Cursor cur_pri_asg_info(lp_assignment_id number) is
2830 select object_version_number
2831 from per_all_assignments_f
2832 where person_id = p_person_id
2833 and assignment_id = lp_assignment_id
2834 and p_start_date between effective_start_date and effective_end_date;
2835
2836 Cursor cur_get_future_asgs(lp_assignment_id number) is
2837 select paaf.effective_start_date
2838 from per_all_assignments_f paaf
2839 where paaf.business_group_id = p_business_group_id
2840 and paaf.person_id = p_person_id
2841 and paaf.primary_flag = 'Y'
2842 and paaf.assignment_type = 'E'
2843 and paaf.assignment_id <> lp_assignment_id
2844 and paaf.effective_start_date > p_start_date
2845 order by paaf.effective_start_date;
2846
2847 Cursor cur_get_asg_num(lp_assignment_id number) is
2848 select paaf.assignment_number
2849 from per_all_assignments_f paaf
2850 where paaf.business_group_id = p_business_group_id
2851 and paaf.person_id = p_person_id
2852 and paaf.primary_flag = 'Y'
2853 and paaf.assignment_type = 'E'
2854 and paaf.assignment_id = lp_assignment_id
2855 order by paaf.effective_start_date;
2856
2857 l_effective_start_date PER_ALL_ASSIGNMENTS_F.effective_start_date%type;
2858 l_effective_end_date PER_ALL_ASSIGNMENTS_F.effective_end_date%type;
2859 l_object_version_number PER_ALL_ASSIGNMENTS_F.object_version_number%type;
2860 l_dummy VARCHAR2(1);
2861 l_asg_num PER_ALL_ASSIGNMENTS_F.assignment_number%type;
2862 l_chg_date PER_ALL_ASSIGNMENTS_F.effective_start_date%type;
2863 -- End changes for bug 9644377
2864
2865 -- Bug 14551643
2866 l_rowid rowid;
2867 CURSOR emp_asg IS
2868 SELECT rowid
2869 FROM per_all_assignments_f
2870 WHERE person_id = p_person_id
2871 AND effective_start_date = p_start_date
2872 AND business_group_id = p_business_group_id
2873 AND primary_flag = 'Y'
2874 AND assignment_type = 'E'
2875 AND assignment_id = p_new_primary_id;
2876
2877
2878 CURSOR apl_asg IS
2879 SELECT *
2880 FROM per_all_assignments_f
2881 WHERE assignment_type = 'A'
2882 AND person_id = p_person_id
2883 AND business_group_id = p_business_group_id
2884 AND effective_start_date < p_start_date
2885 AND assignment_id = p_new_primary_id;
2886 apl_rec apl_asg%ROWTYPE;
2887 -- Bug 14551643
2888
2889 -- # 1769702
2890 -- These future dated assignments get deleted when updating
2891 -- EMP primary assignment with APL details or converting the
2892 -- APL assignment into primary.
2893 --
2894 procedure Delete_future_assignments(p_assignment_id number) is -- added for bug 9644377
2895 --
2896 cursor fut_asg is
2897 select rowid, assignment_id, effective_start_date, effective_end_date
2898 from per_assignments_f
2899 where primary_flag = 'Y'
2900 and business_group_id + 0 = p_business_group_id
2901 and person_id = p_person_id
2902 and assignment_type = 'E'
2903 and effective_start_date > p_start_date
2904 and assignment_id = p_assignment_id -- added for bug 9644377
2905 for update of effective_start_date;
2906
2907 l_rowid ROWID;
2908 l_future_asg_id number;
2909 l_asg_start_date date;
2910 l_asg_end_date date;
2911
2912 begin
2913 hr_utility.set_location('IN Delete future assignments',201);
2914 open fut_asg;
2915 loop
2916 fetch fut_asg into l_rowid, l_future_asg_id,l_asg_start_date, l_asg_end_date
2917 ;
2918 exit when fut_asg%NOTFOUND;
2919
2920 begin
2921
2922 delete from per_assignments_f
2923 where rowid = l_rowid;
2924
2925 exception
2926 when others then
2927 close fut_asg;
2928 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2929 hr_utility.set_message_token('PROCEDURE','Delete_future_assignments');
2930 hr_utility.set_message_token('STEP','911');
2931 hr_utility.raise_error;
2932 end;
2933 end loop;
2934 close fut_asg;
2935 hr_utility.set_location('OUT Delete future assignments',203);
2936 end Delete_future_assignments;
2937 --
2938 --
2939 begin
2940 --
2941 hr_utility.set_location('hrhirapl.create_primary_assignment',1);
2942 --
2943 -- VT 06/14/00
2944 open cur_apl_asg;
2945 fetch cur_apl_asg into apl_asg_rec;
2946 close cur_apl_asg;
2947 --
2948 open cur_emp_asg;
2949 fetch cur_emp_asg into emp_asg_rec;
2950 close cur_emp_asg;
2951
2952 -- Start changes for bug 9644377
2953 if p_update_primary_flag in ('Y','V') then
2954 if emp_asg_rec.payroll_id is not null
2955 and nvl(apl_asg_rec.payroll_id,emp_asg_rec.payroll_id)<> emp_asg_rec.payroll_id then
2956
2957 open csr_pay_actions(emp_asg_rec.assignment_id);
2958 fetch csr_pay_actions into l_dummy;
2959 close csr_pay_actions;
2960
2961 if l_dummy = 'Y' then
2962 hr_utility.set_message(800,'PER_449877_FUTURE_PAY_ACTIONS');
2963 hr_utility.raise_error;
2964 end if;
2965
2966 end if;
2967 end if;
2968 -- End changes for bug 9644377
2969 --
2970 if p_update_primary_flag not in ('Y','V') then
2971 if p_emp_apl = 'Y' then
2972 begin
2973 -- #1769702
2974
2975
2976 -- Start changes for bug 9644377
2977 open cur_get_future_asgs(emp_asg_rec.assignment_id);
2978 fetch cur_get_future_asgs into l_chg_date;
2979 close cur_get_future_asgs;
2980
2981 if l_chg_date is not null then
2982
2983 open cur_get_asg_num(emp_asg_rec.assignment_id);
2984 fetch cur_get_asg_num into l_asg_num;
2985 close cur_get_asg_num;
2986
2987 hr_utility.set_message(800,'PER_449891_FUT_PRI_CHG_EXISTS');
2988 hr_utility.set_message_token('ASG_NUM',l_asg_num);
2989 hr_utility.set_message_token('CHG_DATE',l_chg_date);
2990 hr_utility.raise_error;
2991 end if;
2992 -- End changes for bug 9644377
2993
2994 Delete_future_assignments(p_assignment_id => emp_asg_rec.assignment_id);
2995 --
2996 update per_assignments_f pa
2997 set -- pa.primary_flag = 'N', Changed for bug 9644377
2998 pa.effective_start_date =p_start_date
2999 , pa.effective_end_date = p_end_of_time
3000 , pa.last_update_login = p_login_id
3001 , pa.last_updated_by = p_user_id
3002 , pa.last_update_date = p_start_date
3003 where pa.primary_flag = 'Y'
3004 and pa.business_group_id + 0 = p_business_group_id
3005 and pa.person_id = p_person_id
3006 and p_start_date between pa.effective_start_date
3007 and pa.effective_end_date;
3008 --
3009 if SQL%ROWCOUNT <1 then
3010 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3011 hr_utility.set_message_token('PROCEDURE','create_primary_assignment');
3012 hr_utility.set_message_token('STEP','1');
3013 hr_utility.raise_error;
3014 end if;
3015 end;
3016 end if;
3017 --
3018 hr_utility.set_location('hrhirapl.create_primary_assignment',2);
3019 --
3020 begin
3021 --
3022 --
3023 hrentmnt.check_payroll_changes_asg(p_new_primary_id
3024 ,NULL
3025 ,'INSERT'
3026 ,p_start_date
3027 ,p_end_of_time);
3028 --
3029 --
3030 -- Start of Fix for WWBUG 1485666.
3031 --
3032 -- Before doing the update make sure that what we are doing is valid
3033 -- especially for positions.
3034 --
3035 per_asg_bus1.chk_frozen_single_pos
3036 (p_assignment_id => p_new_primary_id,
3037 p_position_id => apl_asg_rec.position_id,
3038 p_effective_date => p_start_date,
3039 p_assignment_type => apl_asg_rec.assignment_type); -- 6356978
3040 --
3041 -- End of fix for WWBUG 1485666
3042 --
3043 -- Change Reason set to null since applicant change reason based on
3044 -- lookup APL_ASSIGN_REASON whereas employee change reason based on
3045 -- lookup EMP_CHANGE_REASON. The Two are not always compatible.
3046 -- WWBUG 1727576.
3047 -- Added clause pa.change_reason = null
3048 --
3049 -- Changed the value for set_of_books_if from p_set_of_books_id to
3050 -- emp_asg_rec.set_of_books_id bug #2398327
3051
3052 /* update per_all_assignments_f pa
3053 set pa.primary_flag = 'Y'
3054 , pa.effective_start_date = p_start_date
3055 , pa.effective_end_date = p_end_of_time
3056 , pa.set_of_books_id = emp_asg_rec.set_of_books_id
3057 , pa.change_reason = null
3058 where pa.business_group_id + 0 = p_business_group_id
3059 and pa.assignment_id = p_new_primary_id
3060 and p_start_date between pa.effective_start_date
3061 and pa.effective_end_date;
3062 --
3063 --
3064 if SQL%ROWCOUNT <1 then
3065 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3066 hr_utility.set_message_token('PROCEDURE','create_primary_assignment');
3067 hr_utility.set_message_token('STEP','2');
3068 hr_utility.raise_error;
3069 end if; */
3070
3071 -- Start changes for bug 9644377
3072 if p_update_primary_flag = 'C' and p_emp_apl = 'Y' then
3073
3074 update per_all_assignments_f pa
3075 set pa.effective_start_date = p_start_date
3076 , pa.effective_end_date = p_end_of_time
3077 , pa.set_of_books_id = emp_asg_rec.set_of_books_id
3078 , pa.change_reason = null
3079 where pa.business_group_id + 0 = p_business_group_id
3080 and pa.assignment_id = p_new_primary_id
3081 and p_start_date between pa.effective_start_date and pa.effective_end_date;
3082
3083 if SQL%ROWCOUNT <1 then
3084 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3085 hr_utility.set_message_token('PROCEDURE','create_primary_assignment');
3086 hr_utility.set_message_token('STEP','2');
3087 hr_utility.raise_error;
3088 end if;
3089
3090 open cur_pri_asg_info(p_new_primary_id);
3091 fetch cur_pri_asg_info into l_object_version_number;
3092 close cur_pri_asg_info;
3093
3094 hr_assignment_api.set_new_primary_asg(
3095 p_effective_date => p_start_date
3096 ,p_person_id => p_person_id
3097 ,p_assignment_id => p_new_primary_id
3098 ,p_object_version_number => l_object_version_number
3099 ,p_effective_start_date => l_effective_start_date
3100 ,p_effective_end_date => l_effective_end_date
3101 );
3102
3103 else
3104
3105 update per_all_assignments_f pa
3106 set pa.primary_flag = 'Y'
3107 , pa.effective_start_date = p_start_date
3108 , pa.effective_end_date = p_end_of_time
3109 , pa.set_of_books_id = emp_asg_rec.set_of_books_id
3110 , pa.change_reason = null
3111 where pa.business_group_id + 0 = p_business_group_id
3112 and pa.assignment_id = p_new_primary_id
3113 and p_start_date between pa.effective_start_date
3114 and pa.effective_end_date;
3115
3116 if SQL%ROWCOUNT <1 then
3117 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3118 hr_utility.set_message_token('PROCEDURE','create_primary_assignment');
3119 hr_utility.set_message_token('STEP','2');
3120 hr_utility.raise_error;
3121 end if;
3122
3123 end if;
3124 -- End changes for bug 9644377
3125 --
3126 -- Bug #4462906 Moved the hrutility call
3127 -- after SQL%ROWCOUNT check
3128 --
3129 hr_utility.set_location('hrhirapl.employ_applicant',99);
3130 --
3131 hrentmnt.maintain_entries_asg(p_new_primary_id
3132 ,p_business_group_id
3133 ,'HIRE_APPL' -- ,'ASG_CRITERIA' for bug 5547271
3134 ,NULL
3135 ,NULL
3136 ,NULL
3137 ,'INSERT'
3138 ,p_start_date
3139 ,p_end_of_time);
3140 end;
3141 -- Bug 14551643
3142 hr_utility.set_location('hrhirapl.employ_applicant',100);
3143 OPEN emp_asg;
3144 FETCH emp_asg INTO l_rowid;
3145 CLOSE emp_asg;
3146
3147 OPEN apl_asg;
3148 FETCH apl_asg INTO apl_rec;
3149 CLOSE apl_asg;
3150
3151 ben_dt_trgr_handle.assignment
3152 (p_rowid => l_rowid
3153 ,p_assignment_id => apl_rec.assignment_id
3154 ,p_business_group_id => apl_rec.business_group_id
3155 ,p_person_id => apl_rec.person_id
3156 ,p_effective_start_date => apl_rec.effective_start_date
3157 ,p_effective_end_date => apl_rec.effective_end_date
3158 ,p_assignment_status_type_id => apl_rec.assignment_status_type_id
3159 ,p_assignment_type => apl_rec.assignment_type
3160 ,p_organization_id => apl_rec.organization_id
3161 ,p_primary_flag => apl_rec.primary_flag
3162 ,p_change_reason => apl_rec.change_reason
3163 ,p_employment_category => apl_rec.employment_category
3164 ,p_frequency => apl_rec.frequency
3165 ,p_grade_id => apl_rec.grade_id
3166 ,p_job_id => apl_rec.job_id
3167 ,p_position_id => apl_rec.position_id
3168 ,p_location_id => apl_rec.location_id
3169 ,p_normal_hours => apl_rec.normal_hours
3170 ,p_payroll_id => apl_rec.payroll_id
3171 ,p_pay_basis_id => apl_rec.pay_basis_id
3172 ,p_bargaining_unit_code => apl_rec.bargaining_unit_code
3173 ,p_labour_union_member_flag => apl_rec.labour_union_member_flag
3174 ,p_hourly_salaried_code => apl_rec.hourly_salaried_code
3175 ,p_people_group_id => apl_rec.people_group_id
3176 ,p_ass_attribute1 => apl_rec.ass_attribute1
3177 ,p_ass_attribute2 => apl_rec.ass_attribute2
3178 ,p_ass_attribute3 => apl_rec.ass_attribute3
3179 ,p_ass_attribute4 => apl_rec.ass_attribute4
3180 ,p_ass_attribute5 => apl_rec.ass_attribute5
3181 ,p_ass_attribute6 => apl_rec.ass_attribute6
3182 ,p_ass_attribute7 => apl_rec.ass_attribute7
3183 ,p_ass_attribute8 => apl_rec.ass_attribute8
3184 ,p_ass_attribute9 => apl_rec.ass_attribute9
3185 ,p_ass_attribute10 => apl_rec.ass_attribute10
3186 ,p_ass_attribute11 => apl_rec.ass_attribute11
3187 ,p_ass_attribute12 => apl_rec.ass_attribute12
3188 ,p_ass_attribute13 => apl_rec.ass_attribute13
3189 ,p_ass_attribute14 => apl_rec.ass_attribute14
3190 ,p_ass_attribute15 => apl_rec.ass_attribute15
3191 ,p_ass_attribute16 => apl_rec.ass_attribute16
3192 ,p_ass_attribute17 => apl_rec.ass_attribute17
3193 ,p_ass_attribute18 => apl_rec.ass_attribute18
3194 ,p_ass_attribute19 => apl_rec.ass_attribute19
3195 ,p_ass_attribute20 => apl_rec.ass_attribute20
3196 ,p_ass_attribute21 => apl_rec.ass_attribute21
3197 ,p_ass_attribute22 => apl_rec.ass_attribute22
3198 ,p_ass_attribute23 => apl_rec.ass_attribute23
3199 ,p_ass_attribute24 => apl_rec.ass_attribute24
3200 ,p_ass_attribute25 => apl_rec.ass_attribute25
3201 ,p_ass_attribute26 => apl_rec.ass_attribute26
3202 ,p_ass_attribute27 => apl_rec.ass_attribute27
3203 ,p_ass_attribute28 => apl_rec.ass_attribute28
3204 ,p_ass_attribute29 => apl_rec.ass_attribute29
3205 ,p_ass_attribute30 => apl_rec.ass_attribute30);
3206 hr_utility.set_location('hrhirapl.employ_applicant',101);
3207 -- Bug 14551643
3208 else
3209 --
3210 hr_utility.set_location('hrhirapl.create_primary_assignment',3);
3211 --
3212
3213 declare
3214 --
3215 -- 7120387
3216
3217 l_proj_hire_date date;
3218 l_date_probation_end date;
3219
3220 cursor appl_rec_det1(l_appl_id number) is
3221 select projected_hire_date
3222 from per_applications
3223 where application_id =l_appl_id;
3224
3225 --7120387
3226 --
3227 cursor ass_cur is
3228 select assignment_id
3229 , rowid
3230 , effective_end_date -- #1769702
3231 from per_assignments_f pa
3232 where pa.primary_flag='Y'
3233 and pa.person_id = p_person_id
3234 and pa.business_group_id + 0 = p_business_group_id
3235 and p_start_date between pa.effective_start_date
3236 and pa.effective_end_date
3237 for update of pa.organization_id;
3238
3239 -- variables l_prev_location_id, p_old_assignment_id, l_assignment_type added by sneelapa for bug 6409982
3240 l_prev_location_id per_all_assignments_f.location_id%type;
3241 p_old_assignment_id per_all_assignments_f.assignment_id%type;
3242 l_assignment_type per_all_assignments_f.assignment_type%type;
3243
3244 -- cursor cur_asg_type is declared by sneelapa for bug 6409982
3245 cursor cur_asg_type is
3246 select assignment_type
3247 from per_all_assignments_f paf
3248 where paf.assignment_id = p_old_assignment_id;
3249
3250 --
3251 -- #1769702
3252 -- Need to update the future dated assignments
3253 --
3254 cursor future_ass_cur is
3255 select assignment_id
3256 , rowid
3257 , effective_start_date
3258 , effective_end_date
3259 from per_assignments_f pa
3260 where pa.primary_flag = 'Y'
3261 and pa.business_group_id + 0 = p_business_group_id
3262 and pa.person_id = p_person_id
3263 and pa.assignment_type = 'E'
3264 and pa.effective_start_date > p_start_date
3265 for update of pa.organization_id;
3266 --
3267
3268 -- Start changes for bug 13059935
3269 cursor ASS_CUR1(p_row_id rowid) is
3270 select *
3271 from per_assignments_f a
3272 where a.rowid = P_ROW_ID;
3273 --
3274 ass_rec ASS_CUR1%rowtype;
3275 -- End changes for bug 13059935
3276
3277 begin
3278
3279 -- variable l_prev_location_id is added by sneelapa for bug 6409982
3280 l_prev_location_id := emp_asg_rec.location_id;
3281
3282 open get_flex_def;
3283 fetch get_flex_def into l_col_name;
3284 close get_flex_def;
3285 --
3286 open ass_cur;
3287 loop
3288 fetch ass_cur into p_assignment_id,p_rowid, l_asg_end_date; --#1769702
3289 exit when ass_cur%NOTFOUND;
3290 --
3291
3292 -- Start changes for bug 13059935
3293 open ASS_CUR1(p_rowid);
3294 fetch ASS_CUR1 into ass_rec;
3295 close ASS_CUR1;
3296 -- End changes for bug 13059935
3297
3298 --
3299 --
3300 -- VT 06/14/00
3301 if p_update_primary_flag = 'Y' then
3302
3303 hr_utility.set_location('primary_flag = Y',480);
3304 hr_utility.trace(' p_start_date = '||to_char(p_start_date,'dd-MON-yyyy'));
3305 hr_utility.trace(' p_end_of_time = '||to_char(p_end_of_time,'dd-MON-yyyy'));
3306 hr_utility.trace(' assignment_id = '||to_char(p_assignment_id));
3307 hr_utility.trace(' l_asg_end_date = '||to_char(l_asg_end_date,'dd-MON-yyyy'));
3308 --
3309 -- Start of BEN Call
3310 -- Bug 3506363
3311 hr_utility.set_location('OAB Call',485);
3312
3313 -- Start changes for bug 13059935
3314 /*ben_dt_trgr_handle.assignment
3315 (p_rowid => p_rowid
3316 ,p_assignment_id => p_assignment_id
3317 ,p_business_group_id => p_business_group_id
3318 ,p_person_id => p_person_id
3319 ,p_effective_start_date => p_start_date
3320 ,p_effective_end_date => p_end_of_time
3321 ,p_assignment_status_type_id => apl_asg_rec.assignment_status_type_id
3322 ,p_assignment_type => apl_asg_rec.assignment_type
3323 ,p_organization_id => apl_asg_rec.organization_id
3324 ,p_primary_flag => apl_asg_rec.primary_flag
3325 ,p_change_reason => apl_asg_rec.change_reason
3326 ,p_employment_category => apl_asg_rec.employment_category
3327 ,p_frequency => apl_asg_rec.frequency
3328 ,p_grade_id => apl_asg_rec.grade_id
3329 ,p_job_id => apl_asg_rec.job_id
3330 ,p_position_id => apl_asg_rec.position_id
3331 ,p_location_id => apl_asg_rec.location_id
3332 ,p_normal_hours => apl_asg_rec.normal_hours
3333 ,p_payroll_id => apl_asg_rec.payroll_id
3334 ,p_pay_basis_id => apl_asg_rec.pay_basis_id
3335 ,p_bargaining_unit_code => apl_asg_rec.bargaining_unit_code
3336 ,p_labour_union_member_flag => apl_asg_rec.labour_union_member_flag
3337 ,p_hourly_salaried_code => apl_asg_rec.hourly_salaried_code
3338 ,p_people_group_id => apl_asg_rec.people_group_id
3339 ,p_ass_attribute1 => apl_asg_rec.ass_attribute1
3340 ,p_ass_attribute2 => apl_asg_rec.ass_attribute2
3341 ,p_ass_attribute3 => apl_asg_rec.ass_attribute3
3342 ,p_ass_attribute4 => apl_asg_rec.ass_attribute4
3343 ,p_ass_attribute5 => apl_asg_rec.ass_attribute5
3344 ,p_ass_attribute6 => apl_asg_rec.ass_attribute6
3345 ,p_ass_attribute7 => apl_asg_rec.ass_attribute7
3346 ,p_ass_attribute8 => apl_asg_rec.ass_attribute8
3347 ,p_ass_attribute9 => apl_asg_rec.ass_attribute9
3348 ,p_ass_attribute10 => apl_asg_rec.ass_attribute10
3349 ,p_ass_attribute11 => apl_asg_rec.ass_attribute11
3350 ,p_ass_attribute12 => apl_asg_rec.ass_attribute12
3351 ,p_ass_attribute13 => apl_asg_rec.ass_attribute13
3352 ,p_ass_attribute14 => apl_asg_rec.ass_attribute14
3353 ,p_ass_attribute15 => apl_asg_rec.ass_attribute15
3354 ,p_ass_attribute16 => apl_asg_rec.ass_attribute16
3355 ,p_ass_attribute17 => apl_asg_rec.ass_attribute17
3356 ,p_ass_attribute18 => apl_asg_rec.ass_attribute18
3357 ,p_ass_attribute19 => apl_asg_rec.ass_attribute19
3358 ,p_ass_attribute20 => apl_asg_rec.ass_attribute20
3359 ,p_ass_attribute21 => apl_asg_rec.ass_attribute21
3360 ,p_ass_attribute22 => apl_asg_rec.ass_attribute22
3361 ,p_ass_attribute23 => apl_asg_rec.ass_attribute23
3362 ,p_ass_attribute24 => apl_asg_rec.ass_attribute24
3363 ,p_ass_attribute25 => apl_asg_rec.ass_attribute25
3364 ,p_ass_attribute26 => apl_asg_rec.ass_attribute26
3365 ,p_ass_attribute27 => apl_asg_rec.ass_attribute27
3366 ,p_ass_attribute28 => apl_asg_rec.ass_attribute28
3367 ,p_ass_attribute29 => apl_asg_rec.ass_attribute29
3368 ,p_ass_attribute30 => apl_asg_rec.ass_attribute30
3369 );*/
3370 -- End changes for bug 13059935
3371 hr_utility.set_location('After OAB Call',490);
3372 -- End of Bug 3506363
3373 -- End of BEN Call
3374 -- 7120387
3375 open appl_rec_det1(apl_asg_rec.application_id);
3376 fetch appl_rec_det1 into l_proj_hire_date;
3377 close appl_rec_det1;
3378
3379 hr_utility.set_location('l_asg_probation_det.assignment_id :'|| apl_asg_rec.assignment_id,10);
3380 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,10);
3381 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,10);
3382
3383 if l_proj_hire_date is null then -- proj hire date
3384
3385 if ( apl_asg_rec.probation_period is not null)
3386 and
3387 (apl_asg_rec.probation_unit is not null ) then
3388
3389 hr_utility.set_location('p_start_date :'||p_start_date,10);
3390 hr_utility.set_location('l_asg_probation_det.assignment_id :'||apl_asg_rec.assignment_id,10);
3391 hr_utility.set_location('l_asg_probation_det.probation_period :'||apl_asg_rec.probation_period,10);
3392 hr_utility.set_location('l_asg_probation_det.probation_unit :'||apl_asg_rec.probation_unit,10);
3393 l_date_probation_end :=NULL;
3394 hr_assignment.gen_probation_end
3395 (p_assignment_id => apl_asg_rec.assignment_id
3396 ,p_probation_period => apl_asg_rec.probation_period
3397 ,p_probation_unit => apl_asg_rec.probation_unit
3398 ,p_start_date => p_start_date
3399 ,p_date_probation_end => l_date_probation_end
3400 );
3401 hr_utility.set_location('l_date_probation_end :'||l_date_probation_end,10);
3402 apl_asg_rec.date_probation_end :=l_date_probation_end;
3403
3404 end if;
3405 end if; --proj hire date
3406
3407 --7120387
3408 --
3409
3410 update per_assignments_f pa
3411 set pa.organization_id = apl_asg_rec.organization_id
3412 ,pa.effective_start_date = p_start_date
3413 --,pa.effective_end_date = p_end_of_time -- commented for bug 9644377
3414 ,pa.recruiter_id = apl_asg_rec.recruiter_id
3415 ,pa.grade_id = apl_asg_rec.grade_id
3416 ,pa.position_id = apl_asg_rec.position_id
3417 ,pa.job_id = apl_asg_rec.job_id
3418 ,pa.payroll_id = apl_asg_rec.payroll_id
3419 ,pa.location_id = apl_asg_rec.location_id
3420 ,pa.person_referred_by_id = apl_asg_rec.person_referred_by_id
3421 ,pa.supervisor_id = apl_asg_rec.supervisor_id
3422 ,pa.supervisor_assignment_id = apl_asg_rec.supervisor_assignment_id -- #4053244
3423 ,pa.special_ceiling_step_id = apl_asg_rec.special_ceiling_step_id
3424 ,pa.recruitment_activity_id = apl_asg_rec.recruitment_activity_id
3425 ,pa.source_organization_id = apl_asg_rec.source_organization_id
3426 ,pa.people_group_id = apl_asg_rec.people_group_id
3427 ,pa.soft_coding_keyflex_id = apl_asg_rec.soft_coding_keyflex_id
3428 ,pa.vacancy_id = apl_asg_rec.vacancy_id
3429 ,pa.application_id = apl_asg_rec.application_id
3430 ,pa.comment_id = apl_asg_rec.comment_id
3431 ,pa.date_probation_end = apl_asg_rec.date_probation_end
3432 ,pa.default_code_comb_id = apl_asg_rec.default_code_comb_id
3433 ,pa.employment_category = apl_asg_rec.employment_category
3434 ,pa.frequency = apl_asg_rec.frequency
3435 ,pa.internal_address_line = apl_asg_rec.internal_address_line
3436 ,pa.manager_flag = apl_asg_rec.manager_flag
3437 ,pa.normal_hours = apl_asg_rec.normal_hours
3438 ,pa.probation_period = apl_asg_rec.probation_period
3439 ,pa.probation_unit = apl_asg_rec.probation_unit
3440 ,pa.set_of_books_id = p_set_of_books_id
3441 ,pa.source_type = apl_asg_rec.source_type
3442 ,pa.time_normal_finish = apl_asg_rec.time_normal_finish
3443 ,pa.time_normal_start = apl_asg_rec.time_normal_start
3444 ,pa.pay_basis_id = apl_asg_rec.pay_basis_id
3445 ,pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E',pa.ass_attribute_category)
3446 ,pa.ass_attribute1 = apl_asg_rec.ass_attribute1
3447 ,pa.ass_attribute2 = apl_asg_rec.ass_attribute2
3448 ,pa.ass_attribute3 = apl_asg_rec.ass_attribute3
3449 ,pa.ass_attribute4 = apl_asg_rec.ass_attribute4
3450 ,pa.ass_attribute5 = apl_asg_rec.ass_attribute5
3451 ,pa.ass_attribute6 = apl_asg_rec.ass_attribute6
3452 ,pa.ass_attribute7 = apl_asg_rec.ass_attribute7
3453 ,pa.ass_attribute8 = apl_asg_rec.ass_attribute8
3454 ,pa.ass_attribute9 = apl_asg_rec.ass_attribute9
3455 ,pa.ass_attribute10 = apl_asg_rec.ass_attribute10
3456 ,pa.ass_attribute11 = apl_asg_rec.ass_attribute11
3457 ,pa.ass_attribute12 = apl_asg_rec.ass_attribute12
3458 ,pa.ass_attribute13 = apl_asg_rec.ass_attribute13
3459 ,pa.ass_attribute14 = apl_asg_rec.ass_attribute14
3460 ,pa.ass_attribute15 = apl_asg_rec.ass_attribute15
3461 ,pa.ass_attribute16 = apl_asg_rec.ass_attribute16
3462 ,pa.ass_attribute17 = apl_asg_rec.ass_attribute17
3463 ,pa.ass_attribute18 = apl_asg_rec.ass_attribute18
3464 ,pa.ass_attribute19 = apl_asg_rec.ass_attribute19
3465 ,pa.ass_attribute20 = apl_asg_rec.ass_attribute20
3466 ,pa.ass_attribute21 = apl_asg_rec.ass_attribute21
3467 ,pa.ass_attribute22 = apl_asg_rec.ass_attribute22
3468 ,pa.ass_attribute23 = apl_asg_rec.ass_attribute23
3469 ,pa.ass_attribute24 = apl_asg_rec.ass_attribute24
3470 ,pa.ass_attribute25 = apl_asg_rec.ass_attribute25
3471 ,pa.ass_attribute26 = apl_asg_rec.ass_attribute26
3472 ,pa.ass_attribute27 = apl_asg_rec.ass_attribute27
3473 ,pa.ass_attribute28 = apl_asg_rec.ass_attribute28
3474 ,pa.ass_attribute29 = apl_asg_rec.ass_attribute29
3475 ,pa.ass_attribute30 = apl_asg_rec.ass_attribute30
3476 ,pa.GRADE_LADDER_PGM_ID=apl_asg_rec.GRADE_LADDER_PGM_ID-- added for bug 5513751
3477 ,pa.EMPLOYEE_CATEGORY=apl_asg_rec.EMPLOYEE_CATEGORY--added for bug 5513751
3478 ,pa.COLLECTIVE_AGREEMENT_id=apl_asg_rec.COLLECTIVE_AGREEMENT_id-- added for bug 5513751
3479 where pa.rowid = p_rowid;
3480 --
3481
3482 -- Start changes for bug 13059935
3483 ben_dt_trgr_handle.assignment
3484 (p_rowid => p_rowid
3485 ,p_assignment_id => ass_rec.assignment_id
3486 ,p_business_group_id => ass_rec.business_group_id
3487 ,p_person_id => ass_rec.person_id
3488 ,p_effective_start_date => ass_rec.effective_start_date
3489 ,p_effective_end_date => ass_rec.effective_end_date
3490 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
3491 ,p_assignment_type => ass_rec.assignment_type
3492 ,p_organization_id => ass_rec.organization_id
3493 ,p_primary_flag => ass_rec.primary_flag
3494 ,p_change_reason => ass_rec.change_reason
3495 ,p_employment_category => ass_rec.employment_category
3496 ,p_frequency => ass_rec.frequency
3497 ,p_grade_id => ass_rec.grade_id
3498 ,p_job_id => ass_rec.job_id
3499 ,p_position_id => ass_rec.position_id
3500 ,p_location_id => ass_rec.location_id
3501 ,p_normal_hours => ass_rec.normal_hours
3502 ,p_payroll_id => ass_rec.payroll_id
3503 ,p_pay_basis_id => ass_rec.pay_basis_id
3504 ,p_bargaining_unit_code => ass_rec.bargaining_unit_code
3505 ,p_labour_union_member_flag => ass_rec.labour_union_member_flag
3506 ,p_hourly_salaried_code => ass_rec.hourly_salaried_code
3507 ,p_people_group_id => ass_rec.people_group_id
3508 ,p_ass_attribute1 => ass_rec.ass_attribute1
3509 ,p_ass_attribute2 => ass_rec.ass_attribute2
3510 ,p_ass_attribute3 => ass_rec.ass_attribute3
3511 ,p_ass_attribute4 => ass_rec.ass_attribute4
3512 ,p_ass_attribute5 => ass_rec.ass_attribute5
3513 ,p_ass_attribute6 => ass_rec.ass_attribute6
3514 ,p_ass_attribute7 => ass_rec.ass_attribute7
3515 ,p_ass_attribute8 => ass_rec.ass_attribute8
3516 ,p_ass_attribute9 => ass_rec.ass_attribute9
3517 ,p_ass_attribute10 => ass_rec.ass_attribute10
3518 ,p_ass_attribute11 => ass_rec.ass_attribute11
3519 ,p_ass_attribute12 => ass_rec.ass_attribute12
3520 ,p_ass_attribute13 => ass_rec.ass_attribute13
3521 ,p_ass_attribute14 => ass_rec.ass_attribute14
3522 ,p_ass_attribute15 => ass_rec.ass_attribute15
3523 ,p_ass_attribute16 => ass_rec.ass_attribute16
3524 ,p_ass_attribute17 => ass_rec.ass_attribute17
3525 ,p_ass_attribute18 => ass_rec.ass_attribute18
3526 ,p_ass_attribute19 => ass_rec.ass_attribute19
3527 ,p_ass_attribute20 => ass_rec.ass_attribute20
3528 ,p_ass_attribute21 => ass_rec.ass_attribute21
3529 ,p_ass_attribute22 => ass_rec.ass_attribute22
3530 ,p_ass_attribute23 => ass_rec.ass_attribute23
3531 ,p_ass_attribute24 => ass_rec.ass_attribute24
3532 ,p_ass_attribute25 => ass_rec.ass_attribute25
3533 ,p_ass_attribute26 => ass_rec.ass_attribute26
3534 ,p_ass_attribute27 => ass_rec.ass_attribute27
3535 ,p_ass_attribute28 => ass_rec.ass_attribute28
3536 ,p_ass_attribute29 => ass_rec.ass_attribute29
3537 ,p_ass_attribute30 => ass_rec.ass_attribute30
3538 );
3539 -- End changes for bug 13059935
3540
3541 -- Added for the bug 6497082 starts here
3542
3543 hr_utility.set_location('### 3: hrhirapl.create_primary_assignment ',3979);
3544 hr_utility.set_location('### 3: hrhirapl.create_primary_assignment '||apl_asg_rec.assignment_status_type_id,3989);
3545 per_people3_pkg.get_default_person_type
3546 (p_required_type => 'ACTIVE_ASSIGN'
3547 ,p_business_group_id => p_business_group_id
3548 ,p_legislation_code => p_legislation_code
3549 ,p_person_type => l_assignment_status_type_id
3550 );
3551 hr_utility.set_location('### 3: hrhirapl.create_primary_assignment '||l_assignment_status_type_id,3999);
3552
3553 -- Fix For Bug # 7046591 Starts. Added If Clause ---
3554 -- if emp_asg_rec.vacancy_id is not null then -- fix for the bug#11778052
3555 ----------------------
3556 -- IRC FIX 11853433
3557 ----------------------
3558
3559 /*
3560 select nvl(src_apl_asg_id,-1) into l_irc_link_exists
3561 from PER_VAC_LINKED_ASSIGNMENTS
3562 where tgt_apl_asg_id = emp_asg_rec.assignment_id
3563 and sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate) ;
3564
3565 hr_utility.set_location('emp_asg_rec.assignment_id :'||emp_asg_rec.assignment_id,10);
3566 hr_utility.set_location('l_irc_link_exists :'||l_irc_link_exists,10);
3567
3568 if l_irc_link_exists <> -1 then
3569
3570 select assignment_status_type_id into l_irc_spl_status
3571 from per_assignment_status_types
3572 where PER_SYSTEM_STATUS= 'ACTIVE_ASG_IN_OTH_BG'
3573 and ACTIVE_FLAG ='Y' and DEFAULT_FLAG='Y';
3574
3575 hr_utility.set_location('l_irc_spl_status :'||l_irc_spl_status,10);
3576
3577 IRC_ASG_STATUS_API.create_irc_asg_status
3578 (p_assignment_id => emp_asg_rec.assignment_id
3579 ,p_assignment_status_type_id => l_irc_spl_status
3580 ,p_status_change_date => p_start_date
3581 ,p_assignment_status_id => l_assignment_status_id
3582 ,p_object_version_number => l_asg_status_ovn);
3583
3584 else
3585
3586
3587 IRC_ASG_STATUS_API.create_irc_asg_status
3588 (p_assignment_id => emp_asg_rec.assignment_id
3589 ,p_assignment_status_type_id => l_assignment_status_type_id
3590 ,p_status_change_date => p_start_date
3591 ,p_assignment_status_id => l_assignment_status_id
3592 ,p_object_version_number => l_asg_status_ovn);
3593
3594 end if;
3595
3596 */
3597
3598 /*
3599 IRC_ASG_STATUS_API.create_irc_asg_status
3600 (p_assignment_id => emp_asg_rec.assignment_id
3601 ,p_assignment_status_type_id => l_assignment_status_type_id
3602 ,p_status_change_date => p_start_date
3603 ,p_assignment_status_id => l_assignment_status_id
3604 ,p_object_version_number => l_asg_status_ovn);
3605 */
3606
3607
3608
3609
3610 IRC_ASG_STATUS_API.create_irc_asg_status
3611 (p_assignment_id => emp_asg_rec.assignment_id
3612 ,p_assignment_status_type_id => l_assignment_status_type_id
3613 ,p_status_change_date => p_start_date
3614 ,p_assignment_status_id => l_assignment_status_id
3615 ,p_object_version_number => l_asg_status_ovn);
3616
3617
3618 -- while hiring you NO need to set the new assgt status . Only when terminating
3619 --- you need to set the status to new status " " instead of term_% ' when the hire as happend in trgt bg
3620
3621 ----------------------
3622 -- IRC FIX 11853433
3623 ----------------------
3624
3625 -- end if; -- fix for the bug#11778052
3626 -- Fix For Bug # 7046591 Ends. Added If Clause ---
3627
3628 -- Bug 11822430
3629 if (p_legislation_code = 'GB' ) then
3630 PER_GB_PENSRV_SVPN.AUTO_CALC_FTE(P_ASSIGNMENT_ID => p_assignment_id,P_EFFECTIVE_START_DATE => p_start_date) ;
3631 end if;
3632
3633 -- Added for the bug 6497082 Ends here
3634
3635
3636 hr_utility.set_location('Updated EMP assignment',11);
3637 -- # 1769702
3638
3639 -- add the sal admin call here
3640 -- Code for the bug 6512520 starts here
3641 OPEN get_pay_proposal(apl_asg_rec.assignment_id);
3642 FETCH get_pay_proposal
3643 INTO l_apl_pay_pspl_id,l_apl_pay_obj_number,l_apl_proposed_sal_n, l_apl_dummy_change_date;
3644 if get_pay_proposal%found then
3645 -- close get_pay_proposal;
3646
3647 OPEN get_pay_proposal_emp(p_assignment_id);
3648 FETCH get_pay_proposal_emp
3649 INTO l_emp_pay_pspl_id,l_emp_pay_obj_number,l_emp_proposed_sal_n,
3650 l_emp_dummy_change_date;
3651 if get_pay_proposal_emp%found then
3652 close get_pay_proposal_emp ;
3653
3654 -- fix for the bug 7636109 passing the value as null as requested by sal admin team.
3655 l_emp_pay_pspl_id:=null;
3656 l_emp_pay_obj_number:=null;
3657
3658 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
3659 p_validate => false,
3660 p_pay_proposal_id => l_emp_pay_pspl_id ,
3661 p_object_version_number => l_emp_pay_obj_number,
3662 p_change_date => p_start_date,
3663 p_assignment_id => p_assignment_id, -- bug 7636109
3664
3665 p_approved => 'Y',
3666 p_inv_next_sal_date_warning => l_emp_next_sal_date_warning,
3667 p_proposed_salary_warning => l_emp_proposed_salary_warning,
3668 p_approved_warning => l_emp_approved_warning,
3669 p_payroll_warning => l_emp_payroll_warning,
3670 p_proposed_salary_n => l_apl_proposed_sal_n,
3671 p_business_group_id => p_business_group_id);
3672 else
3673 close get_pay_proposal_emp;
3674 l_apl_pay_pspl_id:=null;
3675 l_apl_pay_obj_number:=null;
3676 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
3677 p_validate => false,
3678 p_pay_proposal_id => l_apl_pay_pspl_id,
3679 p_assignment_id => p_assignment_id,
3680 p_object_version_number => l_apl_pay_obj_number,
3681 p_change_date => p_start_date,
3682 p_approved => 'Y',
3683 p_inv_next_sal_date_warning => l_emp_next_sal_date_warning,
3684 p_proposed_salary_warning => l_emp_proposed_salary_warning,
3685 p_approved_warning => l_emp_approved_warning,
3686 p_payroll_warning => l_emp_payroll_warning,
3687 p_proposed_salary_n => l_apl_proposed_sal_n,
3688 p_business_group_id => p_business_group_id);
3689 end if ;
3690
3691 end if;
3692 close get_pay_proposal;
3693 -- Code for the bug 6512520 ends here
3694 --Delete_future_assignments;
3695 Delete_future_assignments(p_assignment_id => emp_asg_rec.assignment_id); -- modified for bug 9644377
3696 --
3697 -- VT 06/14/00
3698 elsif p_update_primary_flag = 'V' then
3699 hr_utility.set_location('Overwrite EMP assignment',15);
3700 -- overwrite some columns from applicant assignment
3701 if apl_asg_rec.organization_id is not null then
3702 emp_asg_rec.organization_id := apl_asg_rec.organization_id;
3703 end if;
3704 if apl_asg_rec.recruiter_id is not null then
3705 emp_asg_rec.recruiter_id := apl_asg_rec.recruiter_id;
3706 end if;
3707
3708 if apl_asg_rec.grade_id is not null then
3709 if (emp_asg_rec.grade_id is not null) and (apl_asg_rec.grade_id <> emp_asg_rec.grade_id) then
3710 l_grades_notequal:='Y';
3711 end if; -- bug 4736269
3712 emp_asg_rec.grade_id := apl_asg_rec.grade_id;
3713 end if;
3714 if apl_asg_rec.position_id is not null then
3715 emp_asg_rec.position_id := apl_asg_rec.position_id;
3716 end if;
3717 if apl_asg_rec.job_id is not null then
3718 emp_asg_rec.job_id := apl_asg_rec.job_id;
3719 end if;
3720 if apl_asg_rec.payroll_id is not null then
3721 emp_asg_rec.payroll_id := apl_asg_rec.payroll_id;
3722 end if;
3723 if apl_asg_rec.location_id is not null then
3724 emp_asg_rec.location_id := apl_asg_rec.location_id;
3725 end if;
3726 if apl_asg_rec.person_referred_by_id is not null then
3727 emp_asg_rec.person_referred_by_id := apl_asg_rec.person_referred_by_id;
3728 end if;
3729 if apl_asg_rec.supervisor_id is not null then
3730 emp_asg_rec.supervisor_id := apl_asg_rec.supervisor_id;
3731 end if;
3732 --- Fix of #4053244 start
3733 if apl_asg_rec.supervisor_assignment_id is not null then
3734 emp_asg_rec.supervisor_assignment_id := apl_asg_rec.supervisor_assignment_id;
3735 end if;
3736 --- Fix of #4053244 end
3737 if apl_asg_rec.special_ceiling_step_id is not null then
3738 emp_asg_rec.special_ceiling_step_id := apl_asg_rec.special_ceiling_step_id;
3739 end if;
3740 if apl_asg_rec.recruitment_activity_id is not null then
3741 emp_asg_rec.recruitment_activity_id := apl_asg_rec.recruitment_activity_id;
3742 end if;
3743 if apl_asg_rec.source_organization_id is not null then
3744 emp_asg_rec.source_organization_id := apl_asg_rec.source_organization_id;
3745 end if;
3746 --fix for bug 7119614 starts here.
3747 open c_pgp_segments(apl_asg_rec.people_group_id);
3748 fetch c_pgp_segments into l_pgp_segment1,
3749 l_pgp_segment2,
3750 l_pgp_segment3,
3751 l_pgp_segment4,
3752 l_pgp_segment5,
3753 l_pgp_segment6,
3754 l_pgp_segment7,
3755 l_pgp_segment8,
3756 l_pgp_segment9,
3757 l_pgp_segment10,
3758 l_pgp_segment11,
3759 l_pgp_segment12,
3760 l_pgp_segment13,
3761 l_pgp_segment14,
3762 l_pgp_segment15,
3763 l_pgp_segment16,
3764 l_pgp_segment17,
3765 l_pgp_segment18,
3766 l_pgp_segment19,
3767 l_pgp_segment20,
3768 l_pgp_segment21,
3769 l_pgp_segment22,
3770 l_pgp_segment23,
3771 l_pgp_segment24,
3772 l_pgp_segment25,
3773 l_pgp_segment26,
3774 l_pgp_segment27,
3775 l_pgp_segment28,
3776 l_pgp_segment29,
3777 l_pgp_segment30;
3778 close c_pgp_segments;
3779
3780 if apl_asg_rec.people_group_id is not null
3781 and (l_pgp_segment1 is not null
3782 or l_pgp_segment2 is not null
3783 or l_pgp_segment3 is not null
3784 or l_pgp_segment4 is not null
3785 or l_pgp_segment5 is not null
3786 or l_pgp_segment6 is not null
3787 or l_pgp_segment7 is not null
3788 or l_pgp_segment8 is not null
3789 or l_pgp_segment9 is not null
3790 or l_pgp_segment10 is not null
3791 or l_pgp_segment11 is not null
3792 or l_pgp_segment12 is not null
3793 or l_pgp_segment13 is not null
3794 or l_pgp_segment14 is not null
3795 or l_pgp_segment15 is not null
3796 or l_pgp_segment16 is not null
3797 or l_pgp_segment17 is not null
3798 or l_pgp_segment18 is not null
3799 or l_pgp_segment19 is not null
3800 or l_pgp_segment20 is not null
3801 or l_pgp_segment21 is not null
3802 or l_pgp_segment22 is not null
3803 or l_pgp_segment23 is not null
3804 or l_pgp_segment24 is not null
3805 or l_pgp_segment25 is not null
3806 or l_pgp_segment26 is not null
3807 or l_pgp_segment27 is not null
3808 or l_pgp_segment28 is not null
3809 or l_pgp_segment29 is not null
3810 or l_pgp_segment30 is not null)then
3811
3812 emp_asg_rec.people_group_id := apl_asg_rec.people_group_id;
3813 end if;
3814 --fix for bug 7119614 ends here.
3815 if apl_asg_rec.soft_coding_keyflex_id is not null then
3816 emp_asg_rec.soft_coding_keyflex_id := apl_asg_rec.soft_coding_keyflex_id;
3817 end if;
3818 if apl_asg_rec.vacancy_id is not null then
3819 emp_asg_rec.vacancy_id := apl_asg_rec.vacancy_id;
3820 end if;
3821 if apl_asg_rec.application_id is not null then
3822 emp_asg_rec.application_id := apl_asg_rec.application_id;
3823 end if;
3824 if apl_asg_rec.comment_id is not null then
3825 emp_asg_rec.comment_id := apl_asg_rec.comment_id;
3826 end if;
3827 if apl_asg_rec.date_probation_end is not null then
3828 emp_asg_rec.date_probation_end := apl_asg_rec.date_probation_end;
3829 end if;
3830 if apl_asg_rec.default_code_comb_id is not null then
3831 emp_asg_rec.default_code_comb_id := apl_asg_rec.default_code_comb_id;
3832 end if;
3833 if apl_asg_rec.employment_category is not null then
3834 emp_asg_rec.employment_category := apl_asg_rec.employment_category;
3835 end if;
3836 if apl_asg_rec.frequency is not null then
3837 emp_asg_rec.frequency := apl_asg_rec.frequency;
3838 end if;
3839 if apl_asg_rec.internal_address_line is not null then
3840 emp_asg_rec.internal_address_line := apl_asg_rec.internal_address_line;
3841 end if;
3842 if apl_asg_rec.manager_flag is not null then
3843 emp_asg_rec.manager_flag := apl_asg_rec.manager_flag;
3844 end if;
3845 if apl_asg_rec.normal_hours is not null then
3846 emp_asg_rec.normal_hours := apl_asg_rec.normal_hours;
3847 end if;
3848 if apl_asg_rec.probation_period is not null then
3849 emp_asg_rec.probation_period := apl_asg_rec.probation_period;
3850 end if;
3851 if apl_asg_rec.probation_unit is not null then
3852 emp_asg_rec.probation_unit := apl_asg_rec.probation_unit;
3853 end if;
3854 if apl_asg_rec.source_type is not null then
3855 emp_asg_rec.source_type := apl_asg_rec.source_type;
3856 end if;
3857 if apl_asg_rec.time_normal_finish is not null then
3858 emp_asg_rec.time_normal_finish := apl_asg_rec.time_normal_finish;
3859 end if;
3860 if apl_asg_rec.time_normal_start is not null then
3861 emp_asg_rec.time_normal_start := apl_asg_rec.time_normal_start;
3862 end if;
3863 if apl_asg_rec.pay_basis_id is not null then
3864 emp_asg_rec.pay_basis_id := apl_asg_rec.pay_basis_id;
3865 end if;
3866 -- fix for the bug 5513751
3867 if apl_asg_rec.GRADE_LADDER_PGM_ID is not null then
3868 emp_asg_rec.GRADE_LADDER_PGM_ID := apl_asg_rec.GRADE_LADDER_PGM_ID;
3869 end if;
3870 if apl_asg_rec.EMPLOYEE_CATEGORY is not null then
3871 emp_asg_rec.EMPLOYEE_CATEGORY := apl_asg_rec.EMPLOYEE_CATEGORY;
3872 end if;
3873 if apl_asg_rec.COLLECTIVE_AGREEMENT_id is not null then
3874 hr_utility.set_location('Updated EMP assignment',90);
3875 emp_asg_rec.COLLECTIVE_AGREEMENT_id := apl_asg_rec.COLLECTIVE_AGREEMENT_id;
3876 end if;
3877 -- fix for the bug 5513751
3878 --
3879 -- 7120387
3880 --
3881 open appl_rec_det1(apl_asg_rec.application_id);
3882 fetch appl_rec_det1 into l_proj_hire_date;
3883 close appl_rec_det1;
3884
3885 hr_utility.set_location('l_asg_probation_det.assignment_id :'|| apl_asg_rec.assignment_id,10);
3886 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,10);
3887 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,10);
3888
3889 if l_proj_hire_date is null then -- proj hire date
3890
3891 if ( apl_asg_rec.probation_period is not null)
3892 and
3893 (apl_asg_rec.probation_unit is not null ) then
3894
3895 hr_utility.set_location('p_start_date :'||p_start_date,10);
3896 hr_utility.set_location('l_asg_probation_det.assignment_id :'||apl_asg_rec.assignment_id,10);
3897 hr_utility.set_location('l_asg_probation_det.probation_period :'||apl_asg_rec.probation_period,10);
3898 hr_utility.set_location('l_asg_probation_det.probation_unit :'||apl_asg_rec.probation_unit,10);
3899 l_date_probation_end :=NULL;
3900 hr_assignment.gen_probation_end
3901 (p_assignment_id => apl_asg_rec.assignment_id
3902 ,p_probation_period => apl_asg_rec.probation_period
3903 ,p_probation_unit => apl_asg_rec.probation_unit
3904 ,p_start_date => p_start_date
3905 ,p_date_probation_end => l_date_probation_end
3906 );
3907 hr_utility.set_location('l_date_probation_end :'||l_date_probation_end,10);
3908 apl_asg_rec.date_probation_end :=l_date_probation_end;
3909 emp_asg_rec.date_probation_end := apl_asg_rec.date_probation_end;
3910 end if;
3911 end if; --proj hire date
3912
3913 -- 7120387
3914 --
3915 -- # 2582838
3916 emp_asg_rec.ass_attribute1 := nvl(apl_asg_rec.ass_attribute1,emp_asg_rec.ass_attribute1);
3917 emp_asg_rec.ass_attribute2 := nvl(apl_asg_rec.ass_attribute2,emp_asg_rec.ass_attribute2);
3918 emp_asg_rec.ass_attribute3 := nvl(apl_asg_rec.ass_attribute3,emp_asg_rec.ass_attribute3);
3919 emp_asg_rec.ass_attribute4 := nvl(apl_asg_rec.ass_attribute4,emp_asg_rec.ass_attribute4);
3920 emp_asg_rec.ass_attribute5 := nvl(apl_asg_rec.ass_attribute5,emp_asg_rec.ass_attribute5);
3921 emp_asg_rec.ass_attribute6 := nvl(apl_asg_rec.ass_attribute6,emp_asg_rec.ass_attribute6);
3922 emp_asg_rec.ass_attribute7 := nvl(apl_asg_rec.ass_attribute7,emp_asg_rec.ass_attribute7);
3923 emp_asg_rec.ass_attribute8 := nvl(apl_asg_rec.ass_attribute8,emp_asg_rec.ass_attribute8);
3924 emp_asg_rec.ass_attribute9 := nvl(apl_asg_rec.ass_attribute9,emp_asg_rec.ass_attribute9);
3925 emp_asg_rec.ass_attribute10 := nvl(apl_asg_rec.ass_attribute10,emp_asg_rec.ass_attribute10);
3926 emp_asg_rec.ass_attribute11 := nvl(apl_asg_rec.ass_attribute11,emp_asg_rec.ass_attribute11);
3927 emp_asg_rec.ass_attribute12 := nvl(apl_asg_rec.ass_attribute12,emp_asg_rec.ass_attribute12);
3928 emp_asg_rec.ass_attribute13 := nvl(apl_asg_rec.ass_attribute13,emp_asg_rec.ass_attribute13);
3929 emp_asg_rec.ass_attribute14 := nvl(emp_asg_rec.ass_attribute14,apl_asg_rec.ass_attribute14);
3930 emp_asg_rec.ass_attribute15 := nvl(apl_asg_rec.ass_attribute15,emp_asg_rec.ass_attribute15);
3931 emp_asg_rec.ass_attribute16 := nvl(apl_asg_rec.ass_attribute16,emp_asg_rec.ass_attribute16);
3932 emp_asg_rec.ass_attribute17 := nvl(apl_asg_rec.ass_attribute17,emp_asg_rec.ass_attribute17);
3933 emp_asg_rec.ass_attribute18 := nvl(apl_asg_rec.ass_attribute18,emp_asg_rec.ass_attribute18);
3934 emp_asg_rec.ass_attribute19 := nvl(apl_asg_rec.ass_attribute19,emp_asg_rec.ass_attribute19);
3935 emp_asg_rec.ass_attribute20 := nvl(apl_asg_rec.ass_attribute20,emp_asg_rec.ass_attribute20);
3936 emp_asg_rec.ass_attribute21 := nvl(apl_asg_rec.ass_attribute21,emp_asg_rec.ass_attribute21);
3937 emp_asg_rec.ass_attribute22 := nvl(apl_asg_rec.ass_attribute22,emp_asg_rec.ass_attribute22);
3938 emp_asg_rec.ass_attribute23 := nvl(apl_asg_rec.ass_attribute23,emp_asg_rec.ass_attribute23);
3939 emp_asg_rec.ass_attribute24 := nvl(apl_asg_rec.ass_attribute24,emp_asg_rec.ass_attribute24);
3940 emp_asg_rec.ass_attribute25 := nvl(apl_asg_rec.ass_attribute25,emp_asg_rec.ass_attribute25);
3941 emp_asg_rec.ass_attribute26 := nvl(apl_asg_rec.ass_attribute26,emp_asg_rec.ass_attribute26);
3942 emp_asg_rec.ass_attribute27 := nvl(apl_asg_rec.ass_attribute27,emp_asg_rec.ass_attribute27);
3943 emp_asg_rec.ass_attribute28 := nvl(apl_asg_rec.ass_attribute28,emp_asg_rec.ass_attribute28);
3944 emp_asg_rec.ass_attribute29 := nvl(apl_asg_rec.ass_attribute29,emp_asg_rec.ass_attribute29);
3945 emp_asg_rec.ass_attribute30 := nvl(apl_asg_rec.ass_attribute30,emp_asg_rec.ass_attribute30);
3946 -- End # 2582838
3947
3948
3949 -- Start of BEN Call
3950 -- Bug 3506363
3951 hr_utility.set_location('Start OAB Call',487);
3952 -- Start changes for bug 13059935
3953 /* ben_dt_trgr_handle.assignment
3954 (p_rowid => p_rowid
3955 ,p_assignment_id => p_assignment_id
3956 ,p_business_group_id => p_business_group_id
3957 ,p_person_id => p_person_id
3958 ,p_effective_start_date => p_start_date
3959 ,p_effective_end_date => p_end_of_time
3960 ,p_assignment_status_type_id => emp_asg_rec.assignment_status_type_id
3961 ,p_assignment_type => emp_asg_rec.assignment_type
3962 ,p_organization_id => emp_asg_rec.organization_id
3963 ,p_primary_flag => emp_asg_rec.primary_flag
3964 ,p_change_reason => emp_asg_rec.change_reason
3965 ,p_employment_category => emp_asg_rec.employment_category
3966 ,p_frequency => emp_asg_rec.frequency
3967 ,p_grade_id => emp_asg_rec.grade_id
3968 ,p_job_id => emp_asg_rec.job_id
3969 ,p_position_id => emp_asg_rec.position_id
3970 ,p_location_id => emp_asg_rec.location_id
3971 ,p_normal_hours => emp_asg_rec.normal_hours
3972 ,p_payroll_id => emp_asg_rec.payroll_id
3973 ,p_pay_basis_id => emp_asg_rec.pay_basis_id
3974 ,p_bargaining_unit_code => emp_asg_rec.bargaining_unit_code
3975 ,p_labour_union_member_flag => emp_asg_rec.labour_union_member_flag
3976 ,p_hourly_salaried_code => emp_asg_rec.hourly_salaried_code
3977 ,p_people_group_id => emp_asg_rec.people_group_id
3978 ,p_ass_attribute1 => emp_asg_rec.ass_attribute1
3979 ,p_ass_attribute2 => emp_asg_rec.ass_attribute2
3980 ,p_ass_attribute3 => emp_asg_rec.ass_attribute3
3981 ,p_ass_attribute4 => emp_asg_rec.ass_attribute4
3982 ,p_ass_attribute5 => emp_asg_rec.ass_attribute5
3983 ,p_ass_attribute6 => emp_asg_rec.ass_attribute6
3984 ,p_ass_attribute7 => emp_asg_rec.ass_attribute7
3985 ,p_ass_attribute8 => emp_asg_rec.ass_attribute8
3986 ,p_ass_attribute9 => emp_asg_rec.ass_attribute9
3987 ,p_ass_attribute10 => emp_asg_rec.ass_attribute10
3988 ,p_ass_attribute11 => emp_asg_rec.ass_attribute11
3989 ,p_ass_attribute12 => emp_asg_rec.ass_attribute12
3990 ,p_ass_attribute13 => emp_asg_rec.ass_attribute13
3991 ,p_ass_attribute14 => emp_asg_rec.ass_attribute14
3992 ,p_ass_attribute15 => emp_asg_rec.ass_attribute15
3993 ,p_ass_attribute16 => emp_asg_rec.ass_attribute16
3994 ,p_ass_attribute17 => emp_asg_rec.ass_attribute17
3995 ,p_ass_attribute18 => emp_asg_rec.ass_attribute18
3996 ,p_ass_attribute19 => emp_asg_rec.ass_attribute19
3997 ,p_ass_attribute20 => emp_asg_rec.ass_attribute20
3998 ,p_ass_attribute21 => emp_asg_rec.ass_attribute21
3999 ,p_ass_attribute22 => emp_asg_rec.ass_attribute22
4000 ,p_ass_attribute23 => emp_asg_rec.ass_attribute23
4001 ,p_ass_attribute24 => emp_asg_rec.ass_attribute24
4002 ,p_ass_attribute25 => emp_asg_rec.ass_attribute25
4003 ,p_ass_attribute26 => emp_asg_rec.ass_attribute26
4004 ,p_ass_attribute27 => emp_asg_rec.ass_attribute27
4005 ,p_ass_attribute28 => emp_asg_rec.ass_attribute28
4006 ,p_ass_attribute29 => emp_asg_rec.ass_attribute29
4007 ,p_ass_attribute30 => emp_asg_rec.ass_attribute30
4008 );*/
4009 -- End changes for bug 13059935
4010 hr_utility.set_location('End of OAB Call',489);
4011 -- End of Bug 3506363
4012 -- End of BEN Call
4013
4014 --
4015 hr_utility.set_location('hrhirapl.create_primary_assignment',10);
4016
4017 --
4018 -- Changed the value for set_of_books_if from p_set_of_books_id to
4019 -- emp_asg_rec.set_of_books_id bug #2398327
4020
4021 update per_assignments_f pa
4022 set pa.organization_id = emp_asg_rec.organization_id
4023 ,pa.effective_start_date = p_start_date
4024 --,pa.effective_end_date = p_end_of_time -- #1769702 what if future asg exist?
4025 ,pa.recruiter_id = emp_asg_rec.recruiter_id
4026 ,pa.grade_id = emp_asg_rec.grade_id
4027 ,pa.position_id = emp_asg_rec.position_id
4028 ,pa.job_id = emp_asg_rec.job_id
4029 ,pa.payroll_id = emp_asg_rec.payroll_id
4030 ,pa.location_id = emp_asg_rec.location_id
4031 ,pa.person_referred_by_id = emp_asg_rec.person_referred_by_id
4032 ,pa.supervisor_id = emp_asg_rec.supervisor_id
4033 ,pa.supervisor_assignment_id = emp_asg_rec.supervisor_assignment_id -- #4053244
4034 ,pa.special_ceiling_step_id = emp_asg_rec.special_ceiling_step_id
4035 ,pa.recruitment_activity_id = emp_asg_rec.recruitment_activity_id
4036 ,pa.source_organization_id = emp_asg_rec.source_organization_id
4037 ,pa.people_group_id = emp_asg_rec.people_group_id
4038 ,pa.soft_coding_keyflex_id = emp_asg_rec.soft_coding_keyflex_id
4039 ,pa.vacancy_id = emp_asg_rec.vacancy_id
4040 ,pa.application_id = emp_asg_rec.application_id
4041 ,pa.comment_id = emp_asg_rec.comment_id
4042 ,pa.date_probation_end = emp_asg_rec.date_probation_end
4043 ,pa.default_code_comb_id = emp_asg_rec.default_code_comb_id
4044 ,pa.employment_category = emp_asg_rec.employment_category
4045 ,pa.frequency = emp_asg_rec.frequency
4046 ,pa.internal_address_line = emp_asg_rec.internal_address_line
4047 ,pa.manager_flag = emp_asg_rec.manager_flag
4048 ,pa.normal_hours = emp_asg_rec.normal_hours
4049 ,pa.probation_period = emp_asg_rec.probation_period
4050 ,pa.probation_unit = emp_asg_rec.probation_unit
4051 ,pa.set_of_books_id = emp_asg_rec.set_of_books_id
4052 ,pa.source_type = emp_asg_rec.source_type
4053 ,pa.time_normal_finish = emp_asg_rec.time_normal_finish
4054 ,pa.time_normal_start = emp_asg_rec.time_normal_start
4055 ,pa.pay_basis_id = emp_asg_rec.pay_basis_id
4056 ,pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E',pa.ass_attribute_category)
4057 ,pa.ass_attribute1 = emp_asg_rec.ass_attribute1
4058 ,pa.ass_attribute2 = emp_asg_rec.ass_attribute2
4059 ,pa.ass_attribute3 = emp_asg_rec.ass_attribute3
4060 ,pa.ass_attribute4 = emp_asg_rec.ass_attribute4
4061 ,pa.ass_attribute5 = emp_asg_rec.ass_attribute5
4062 ,pa.ass_attribute6 = emp_asg_rec.ass_attribute6
4063 ,pa.ass_attribute7 = emp_asg_rec.ass_attribute7
4064 ,pa.ass_attribute8 = emp_asg_rec.ass_attribute8
4065 ,pa.ass_attribute9 = emp_asg_rec.ass_attribute9
4066 ,pa.ass_attribute10 = emp_asg_rec.ass_attribute10
4067 ,pa.ass_attribute11 = emp_asg_rec.ass_attribute11
4068 ,pa.ass_attribute12 = emp_asg_rec.ass_attribute12
4069 ,pa.ass_attribute13 = emp_asg_rec.ass_attribute13
4070 ,pa.ass_attribute14 = emp_asg_rec.ass_attribute14
4071 ,pa.ass_attribute15 = emp_asg_rec.ass_attribute15
4072 ,pa.ass_attribute16 = emp_asg_rec.ass_attribute16
4073 ,pa.ass_attribute17 = emp_asg_rec.ass_attribute17
4074 ,pa.ass_attribute18 = emp_asg_rec.ass_attribute18
4075 ,pa.ass_attribute19 = emp_asg_rec.ass_attribute19
4076 ,pa.ass_attribute20 = emp_asg_rec.ass_attribute20
4077 ,pa.ass_attribute21 = emp_asg_rec.ass_attribute21
4078 ,pa.ass_attribute22 = emp_asg_rec.ass_attribute22
4079 ,pa.ass_attribute23 = emp_asg_rec.ass_attribute23
4080 ,pa.ass_attribute24 = emp_asg_rec.ass_attribute24
4081 ,pa.ass_attribute25 = emp_asg_rec.ass_attribute25
4082 ,pa.ass_attribute26 = emp_asg_rec.ass_attribute26
4083 ,pa.ass_attribute27 = emp_asg_rec.ass_attribute27
4084 ,pa.ass_attribute28 = emp_asg_rec.ass_attribute28
4085 ,pa.ass_attribute29 = emp_asg_rec.ass_attribute29
4086 ,pa.ass_attribute30 = emp_asg_rec.ass_attribute30
4087 ,pa.GRADE_LADDER_PGM_ID= emp_asg_rec.GRADE_LADDER_PGM_ID -- 5513751
4088 ,pa.EMPLOYEE_CATEGORY= emp_asg_rec.EMPLOYEE_CATEGORY -- 5513751
4089 ,pa.COLLECTIVE_AGREEMENT_id= emp_asg_rec.COLLECTIVE_AGREEMENT_id -- 5513751
4090 where pa.rowid = p_rowid;
4091 --
4092 -- Added for the bug 6497082 starts here
4093
4094 hr_utility.set_location('### 4: hrhirapl.create_primary_assignment',3981);
4095
4096 -- Start changes for bug 13059935
4097 ben_dt_trgr_handle.assignment
4098 (p_rowid => p_rowid
4099 ,p_assignment_id => ass_rec.assignment_id
4100 ,p_business_group_id => ass_rec.business_group_id
4101 ,p_person_id => ass_rec.person_id
4102 ,p_effective_start_date => ass_rec.effective_start_date
4103 ,p_effective_end_date => ass_rec.effective_end_date
4104 ,p_assignment_status_type_id => ass_rec.assignment_status_type_id
4105 ,p_assignment_type => ass_rec.assignment_type
4106 ,p_organization_id => ass_rec.organization_id
4107 ,p_primary_flag => ass_rec.primary_flag
4108 ,p_change_reason => ass_rec.change_reason
4109 ,p_employment_category => ass_rec.employment_category
4110 ,p_frequency => ass_rec.frequency
4111 ,p_grade_id => ass_rec.grade_id
4112 ,p_job_id => ass_rec.job_id
4113 ,p_position_id => ass_rec.position_id
4114 ,p_location_id => ass_rec.location_id
4115 ,p_normal_hours => ass_rec.normal_hours
4116 ,p_payroll_id => ass_rec.payroll_id
4117 ,p_pay_basis_id => ass_rec.pay_basis_id
4118 ,p_bargaining_unit_code => ass_rec.bargaining_unit_code
4119 ,p_labour_union_member_flag => ass_rec.labour_union_member_flag
4120 ,p_hourly_salaried_code => ass_rec.hourly_salaried_code
4121 ,p_people_group_id => ass_rec.people_group_id
4122 ,p_ass_attribute1 => ass_rec.ass_attribute1
4123 ,p_ass_attribute2 => ass_rec.ass_attribute2
4124 ,p_ass_attribute3 => ass_rec.ass_attribute3
4125 ,p_ass_attribute4 => ass_rec.ass_attribute4
4126 ,p_ass_attribute5 => ass_rec.ass_attribute5
4127 ,p_ass_attribute6 => ass_rec.ass_attribute6
4128 ,p_ass_attribute7 => ass_rec.ass_attribute7
4129 ,p_ass_attribute8 => ass_rec.ass_attribute8
4130 ,p_ass_attribute9 => ass_rec.ass_attribute9
4131 ,p_ass_attribute10 => ass_rec.ass_attribute10
4132 ,p_ass_attribute11 => ass_rec.ass_attribute11
4133 ,p_ass_attribute12 => ass_rec.ass_attribute12
4134 ,p_ass_attribute13 => ass_rec.ass_attribute13
4135 ,p_ass_attribute14 => ass_rec.ass_attribute14
4136 ,p_ass_attribute15 => ass_rec.ass_attribute15
4137 ,p_ass_attribute16 => ass_rec.ass_attribute16
4138 ,p_ass_attribute17 => ass_rec.ass_attribute17
4139 ,p_ass_attribute18 => ass_rec.ass_attribute18
4140 ,p_ass_attribute19 => ass_rec.ass_attribute19
4141 ,p_ass_attribute20 => ass_rec.ass_attribute20
4142 ,p_ass_attribute21 => ass_rec.ass_attribute21
4143 ,p_ass_attribute22 => ass_rec.ass_attribute22
4144 ,p_ass_attribute23 => ass_rec.ass_attribute23
4145 ,p_ass_attribute24 => ass_rec.ass_attribute24
4146 ,p_ass_attribute25 => ass_rec.ass_attribute25
4147 ,p_ass_attribute26 => ass_rec.ass_attribute26
4148 ,p_ass_attribute27 => ass_rec.ass_attribute27
4149 ,p_ass_attribute28 => ass_rec.ass_attribute28
4150 ,p_ass_attribute29 => ass_rec.ass_attribute29
4151 ,p_ass_attribute30 => ass_rec.ass_attribute30
4152 );
4153 -- End changes for bug 13059935
4154
4155 hr_utility.set_location('### 4: hrhirapl.create_primary_assignment'||emp_asg_rec.assignment_status_type_id,3991);
4156 per_people3_pkg.get_default_person_type
4157 (p_required_type => 'ACTIVE_ASSIGN'
4158 ,p_business_group_id => p_business_group_id
4159 ,p_legislation_code => p_legislation_code
4160 ,p_person_type => l_assignment_status_type_id
4161 );
4162
4163 -- Fix For Bug # 7046591 Starts. Added If Clause ---
4164 -- if emp_asg_rec.vacancy_id is not null then -- fix for the issue in the bug#11778052
4165 IRC_ASG_STATUS_API.create_irc_asg_status
4166 (p_assignment_id => emp_asg_rec.assignment_id
4167 ,p_assignment_status_type_id => l_assignment_status_type_id
4168 ,p_status_change_date => p_start_date
4169 ,p_assignment_status_id => l_assignment_status_id
4170 ,p_object_version_number => l_asg_status_ovn);
4171
4172 -- end if; -- fix for the issue in the bug#11778052
4173 -- Fix For Bug # 7046591 Ends. Added If Clause ---
4174 -- Bug 11822430
4175 if (p_legislation_code = 'GB' ) then
4176 PER_GB_PENSRV_SVPN.AUTO_CALC_FTE(P_ASSIGNMENT_ID => emp_asg_rec.assignment_id,P_EFFECTIVE_START_DATE => p_start_date) ;
4177 end if;
4178
4179 -- Added for the bug 6497082 Ends here
4180
4181 -- #1769702
4182
4183 -- Code for the bug 6512520 starts here
4184 OPEN get_pay_proposal(apl_asg_rec.assignment_id);
4185 FETCH get_pay_proposal
4186 INTO l_apl_pay_pspl_id,l_apl_pay_obj_number,l_apl_proposed_sal_n, l_apl_dummy_change_date;
4187 if get_pay_proposal%found then
4188 -- close get_pay_proposal;
4189
4190 OPEN get_pay_proposal_emp(p_assignment_id);
4191 FETCH get_pay_proposal_emp
4192 INTO l_emp_pay_pspl_id,l_emp_pay_obj_number,l_emp_proposed_sal_n,
4193 l_emp_dummy_change_date;
4194 if get_pay_proposal_emp%found then
4195 close get_pay_proposal_emp ;
4196
4197
4198 -- fix for the bug 7636109 passing the value as null as requested by sal admin team.
4199 l_emp_pay_pspl_id:=null;
4200 l_emp_pay_obj_number:=null;
4201
4202 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
4203 p_validate => false,
4204 p_pay_proposal_id => l_emp_pay_pspl_id ,
4205 p_object_version_number => l_emp_pay_obj_number,
4206 p_assignment_id => p_assignment_id, -- bug7636109
4207 p_change_date => p_start_date,
4208 p_approved => 'Y',
4209 p_inv_next_sal_date_warning => l_emp_next_sal_date_warning,
4210 p_proposed_salary_warning => l_emp_proposed_salary_warning,
4211 p_approved_warning => l_emp_approved_warning,
4212 p_payroll_warning => l_emp_payroll_warning,
4213 p_proposed_salary_n => l_apl_proposed_sal_n,
4214 p_business_group_id => p_business_group_id);
4215 else
4216 close get_pay_proposal_emp;
4217 l_apl_pay_pspl_id:=null;
4218 l_apl_pay_obj_number:=null;
4219 hr_maintain_proposal_api.cre_or_upd_salary_proposal(
4220 p_validate => false,
4221 p_pay_proposal_id => l_apl_pay_pspl_id,
4222 p_assignment_id => p_assignment_id,
4223 p_object_version_number => l_apl_pay_obj_number,
4224 p_change_date => p_start_date,
4225 p_approved => 'Y',
4226 p_inv_next_sal_date_warning => l_emp_next_sal_date_warning,
4227 p_proposed_salary_warning => l_emp_proposed_salary_warning,
4228 p_approved_warning => l_emp_approved_warning,
4229 p_payroll_warning => l_emp_payroll_warning,
4230 p_proposed_salary_n => l_apl_proposed_sal_n,
4231 p_business_group_id => p_business_group_id);
4232 end if ;
4233
4234 end if;
4235 close get_pay_proposal;
4236 -- Code for the bug 6512520 ends here
4237
4238 -- All future dated assignments should get ovewritten.
4239 --
4240 hr_utility.set_location('IN Overwrite future dated assignments',14);
4241 open future_ass_cur;
4242 loop
4243 fetch future_ass_cur into l_future_asg_id,p_rowid
4244 ,l_fut_start_date, l_fut_end_date;
4245 exit when future_ass_cur%NOTFOUND;
4246
4247 -- Changed the value for set_of_books_if from p_set_of_books_id to
4248 -- emp_asg_rec.set_of_books_id bug #2398327
4249
4250 update per_assignments_f pa
4251 set pa.organization_id = emp_asg_rec.organization_id
4252 ,pa.recruiter_id = emp_asg_rec.recruiter_id
4253 ,pa.grade_id = emp_asg_rec.grade_id
4254 ,pa.position_id = emp_asg_rec.position_id
4255 ,pa.job_id = emp_asg_rec.job_id
4256 ,pa.payroll_id = emp_asg_rec.payroll_id
4257 ,pa.location_id = emp_asg_rec.location_id
4258 ,pa.person_referred_by_id = emp_asg_rec.person_referred_by_id
4259 ,pa.supervisor_id = emp_asg_rec.supervisor_id
4260 ,pa.supervisor_assignment_id = emp_asg_rec.supervisor_assignment_id -- #4053244
4261 ,pa.special_ceiling_step_id = emp_asg_rec.special_ceiling_step_id
4262 ,pa.recruitment_activity_id = emp_asg_rec.recruitment_activity_id
4263 ,pa.source_organization_id = emp_asg_rec.source_organization_id
4264 ,pa.people_group_id = emp_asg_rec.people_group_id
4265 ,pa.soft_coding_keyflex_id = emp_asg_rec.soft_coding_keyflex_id
4266 ,pa.vacancy_id = emp_asg_rec.vacancy_id
4267 ,pa.application_id = emp_asg_rec.application_id
4268 ,pa.comment_id = emp_asg_rec.comment_id
4269 ,pa.date_probation_end = emp_asg_rec.date_probation_end
4270 ,pa.default_code_comb_id = emp_asg_rec.default_code_comb_id
4271 ,pa.employment_category = emp_asg_rec.employment_category
4272 ,pa.frequency = emp_asg_rec.frequency
4273 ,pa.internal_address_line = emp_asg_rec.internal_address_line
4274 ,pa.manager_flag = emp_asg_rec.manager_flag
4275 ,pa.normal_hours = emp_asg_rec.normal_hours
4276 ,pa.probation_period = emp_asg_rec.probation_period
4277 ,pa.probation_unit = emp_asg_rec.probation_unit
4278 ,pa.set_of_books_id = emp_asg_rec.set_of_books_id
4279 ,pa.source_type = emp_asg_rec.source_type
4280 ,pa.time_normal_finish = emp_asg_rec.time_normal_finish
4281 ,pa.time_normal_start = emp_asg_rec.time_normal_start
4282 ,pa.pay_basis_id = emp_asg_rec.pay_basis_id
4283 ,pa.ass_attribute_category = decode(l_col_name,'ASSIGNMENT_TYPE','E',pa.ass_attribute_category)
4284 ,pa.ass_attribute1 = emp_asg_rec.ass_attribute1
4285 ,pa.ass_attribute2 = emp_asg_rec.ass_attribute2
4286 ,pa.ass_attribute3 = emp_asg_rec.ass_attribute3
4287 ,pa.ass_attribute4 = emp_asg_rec.ass_attribute4
4288 ,pa.ass_attribute5 = emp_asg_rec.ass_attribute5
4289 ,pa.ass_attribute6 = emp_asg_rec.ass_attribute6
4290 ,pa.ass_attribute7 = emp_asg_rec.ass_attribute7
4291 ,pa.ass_attribute8 = emp_asg_rec.ass_attribute8
4292 ,pa.ass_attribute9 = emp_asg_rec.ass_attribute9
4293 ,pa.ass_attribute10 = emp_asg_rec.ass_attribute10
4294 ,pa.ass_attribute11 = emp_asg_rec.ass_attribute11
4295 ,pa.ass_attribute12 = emp_asg_rec.ass_attribute12
4296 ,pa.ass_attribute13 = emp_asg_rec.ass_attribute13
4297 ,pa.ass_attribute14 = emp_asg_rec.ass_attribute14
4298 ,pa.ass_attribute15 = emp_asg_rec.ass_attribute15
4299 ,pa.ass_attribute16 = emp_asg_rec.ass_attribute16
4300 ,pa.ass_attribute17 = emp_asg_rec.ass_attribute17
4301 ,pa.ass_attribute18 = emp_asg_rec.ass_attribute18
4302 ,pa.ass_attribute19 = emp_asg_rec.ass_attribute19
4303 ,pa.ass_attribute20 = emp_asg_rec.ass_attribute20
4304 ,pa.ass_attribute21 = emp_asg_rec.ass_attribute21
4305 ,pa.ass_attribute22 = emp_asg_rec.ass_attribute22
4306 ,pa.ass_attribute23 = emp_asg_rec.ass_attribute23
4307 ,pa.ass_attribute24 = emp_asg_rec.ass_attribute24
4308 ,pa.ass_attribute25 = emp_asg_rec.ass_attribute25
4309 ,pa.ass_attribute26 = emp_asg_rec.ass_attribute26
4310 ,pa.ass_attribute27 = emp_asg_rec.ass_attribute27
4311 ,pa.ass_attribute28 = emp_asg_rec.ass_attribute28
4312 ,pa.ass_attribute29 = emp_asg_rec.ass_attribute29
4313 ,pa.ass_attribute30 = emp_asg_rec.ass_attribute30
4314 ,pa.GRADE_LADDER_PGM_ID= emp_asg_rec.GRADE_LADDER_PGM_ID -- 5513751
4315 ,pa.EMPLOYEE_CATEGORY= emp_asg_rec.EMPLOYEE_CATEGORY -- 5513751
4316 ,pa.COLLECTIVE_AGREEMENT_id= emp_asg_rec.COLLECTIVE_AGREEMENT_id -- 5513751
4317 where pa.rowid = p_rowid;
4318 --
4319 -- Bug 11822430
4320 if (p_legislation_code = 'GB' ) then
4321 PER_GB_PENSRV_SVPN.AUTO_CALC_FTE(P_ASSIGNMENT_ID => l_future_asg_id,P_EFFECTIVE_START_DATE => l_fut_start_date);
4322 end if;
4323
4324 hrentmnt.check_payroll_changes_asg(l_future_asg_id
4325 ,NULL
4326 ,'INSERT'
4327 ,l_fut_start_date
4328 ,l_fut_end_date);
4329 --
4330 hrentmnt.maintain_entries_asg(l_future_asg_id
4331 ,p_business_group_id
4332 ,'HIRE_APPL' -- ,'ASG_CRITERIA' for bug 5547271
4333 ,NULL
4334 ,NULL
4335 ,NULL
4336 ,'INSERT'
4337 ,l_fut_start_date
4338 ,l_fut_end_date);
4339 -- #2433154
4340 -- US Leg: Tax records might need to be updated
4341 --
4342
4343 -- and l_prev_location_id <> apl_asg_rec.location_id condition
4344 -- added by sneelapa for bug 6409982.
4345
4346 if p_legislation_code = 'US'
4347 and l_prev_location_id <> apl_asg_rec.location_id then
4348
4349 hr_utility.set_location('Updating tax records..',16);
4350
4351 -- code added by sneelapa for bug 6409982 starts
4352 p_old_assignment_id := l_future_asg_id;
4353
4354 open cur_asg_type;
4355 fetch cur_asg_type into l_assignment_type;
4356 close cur_asg_type;
4357
4358 if l_assignment_type <> 'B' then
4359 -- code added by sneelapa for bug 6409982 ends
4360
4361 pay_us_emp_dt_tax_rules.default_tax_with_validation
4362 (p_assignment_id => l_future_asg_id
4363 ,p_person_id => p_person_id
4364 ,p_effective_start_date => l_fut_start_date
4365 ,p_effective_end_date => l_fut_end_date
4366 ,p_session_date => l_fut_start_date
4367 ,p_business_group_id => p_business_group_id
4368 ,p_from_form => 'Assignment'
4369 ,p_mode => 'UPDATE'
4370 ,p_location_id => emp_asg_rec.location_id
4371 ,p_return_code => l_return_code
4372 ,p_return_text => l_return_text
4373 );
4374 hr_utility.set_location('END Updating tax records..',17);
4375 end if;
4376 end if; -- leg=US
4377 -- end 2433154
4378 --
4379 end loop; -- updating future dated assignments
4380 close future_ass_cur; -- fix for bug#3057451
4381 -- # 1769702
4382 hr_utility.set_location('OUT Overwrite future dated assignments',18);
4383 else
4384 null;
4385 end if;
4386 -- Start of bug fix 2933750
4387 -- Start of bug 3631834
4388 if (apl_asg_rec.grade_id <> emp_asg_rec.grade_id) or
4389 (apl_asg_rec.grade_id is null and emp_asg_rec.grade_id is not null)
4390 or (l_grades_notequal='Y') then -- bug 4736269
4391 -- End of bug 3631834
4392 hr_assignment_internal.maintain_spp_asg(
4393 p_assignment_id => p_assignment_id,
4394 p_datetrack_mode => hr_api.g_update,
4395 p_validation_start_date => p_start_date,
4396 p_validation_end_date => p_end_of_time,
4397 p_grade_id => apl_asg_rec.grade_id,
4398 p_spp_delete_warning => l_delete_warn);
4399 end if;
4400 --
4401 -- End of fix 2933750
4402 --
4403 hrentmnt.check_payroll_changes_asg(p_assignment_id
4404 ,NULL
4405 ,'INSERT'
4406 ,p_start_date
4407 ,l_asg_end_date); -- #1769702
4408 --
4409 --
4410 -- Before doing the update make sure that what we are doing is valid
4411 -- especially for positions.
4412 --
4413 per_asg_bus1.chk_frozen_single_pos
4414 (p_assignment_id => p_assignment_id,
4415 p_position_id => apl_asg_rec.position_id,
4416 p_effective_date => p_start_date,
4417 p_assignment_type => emp_asg_rec.assignment_type); -- 7348032
4418 --p_assignment_type => apl_asg_rec.assignment_type); -- 6356978
4419 --
4420 hrentmnt.maintain_entries_asg(p_assignment_id
4421 ,p_business_group_id
4422 ,'HIRE_APPL' --,'ASG_CRITERIA' for bug 5547271
4423 ,NULL
4424 ,NULL
4425 ,NULL
4426 ,'INSERT'
4427 ,p_start_date
4428 ,l_asg_end_date); -- #1769702
4429 -- #2433154
4430 -- US Leg: Tax records might need to be updated
4431 --
4432
4433 -- and l_prev_location_id <> apl_asg_rec.location_id condition
4434 -- added by sneelapa for bug 6409982.
4435
4436 if p_legislation_code = 'US'
4437 and l_prev_location_id <> apl_asg_rec.location_id then
4438
4439 -- code added by sneelapa for bug 6409982 starts
4440 p_old_assignment_id := p_assignment_id;
4441
4442 open cur_asg_type;
4443 fetch cur_asg_type into l_assignment_type;
4444 close cur_asg_type;
4445
4446 if l_assignment_type <> 'B' then
4447 -- code added by sneelapa for bug 6409982 ends
4448
4449 pay_us_emp_dt_tax_rules.default_tax_with_validation
4450 (p_assignment_id => p_assignment_id
4451 ,p_person_id => p_person_id
4452 ,p_effective_start_date => p_start_date
4453 ,p_effective_end_date => l_asg_end_date
4454 ,p_session_date => p_start_date
4455 ,p_business_group_id => p_business_group_id
4456 ,p_from_form => 'Assignment'
4457 ,p_mode => 'UPDATE'
4458 ,p_location_id => emp_asg_rec.location_id
4459 ,p_return_code => l_return_code
4460 ,p_return_text => l_return_text
4461 );
4462 end if;
4463 end if; -- leg=US
4464 -- end 2433154
4465 end loop;
4466 --
4467 if ass_cur%ROWCOUNT <1 then
4468 hr_utility.set_location('hrhirapl.create_primary_assignment',33);
4469 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4470 hr_utility.set_message_token('PROCEDURE','create_primary_assignment');
4471 hr_utility.set_message_token('STEP','3');
4472 hr_utility.raise_error;
4473 end if;
4474 close ass_cur;
4475 -- +---- End Date chosen APL primary assignment id ------+
4476 begin
4477 declare cursor app_cur is
4478 select pa.rowid
4479 from per_assignments_f pa
4480 where pa.assignment_id = p_new_primary_id
4481 and p_start_date between pa.effective_start_date
4482 and pa.effective_end_date
4483 for update of pa.effective_end_date;
4484 begin
4485 hr_utility.set_location('hrhirapl.create_primary_assignment',22);
4486 hr_utility.trace(' Update APL asg id => '||to_char(p_new_primary_id));
4487 open app_cur;
4488 loop
4489 fetch app_cur into p_rowid;
4490 exit when app_cur%notfound;
4491 hr_utility.set_location('hrhirapl.create_primary_assignment',23);
4492 update per_assignments_f
4493 set effective_end_date = p_start_date - 1
4494 where rowid = p_rowid;
4495 end loop;
4496 if app_cur%ROWCOUNT <1 then
4497 hr_utility.set_location('hrhirapl.create_primary_assignment',44);
4498 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4499 hr_utility.set_message_token('PROCEDURE','create_primary_assignment');
4500 hr_utility.set_message_token('STEP','4');
4501 hr_utility.raise_error;
4502 end if;
4503 close app_cur;
4504 end;
4505 end;
4506 end;
4507 end if;
4508 --
4509
4510 --start changes for bug 6598795
4511 hr_assignment.update_assgn_context_value (p_business_group_id,
4512 p_person_id,
4513 p_assignment_id,
4514 p_start_date);
4515 --start changes for bug 6598795
4516 --start changes for bug 7289811
4517 IRC_OFFERS_API.close_offer
4518 ( p_validate => false
4519 ,p_effective_date => p_start_date-1
4520 ,p_applicant_assignment_id => apl_asg_rec.assignment_id
4521 ,p_change_reason => 'APL_HIRED'-- Fix for bug 7540870
4522 );
4523 --end changes for bug 7289811
4524
4525 end create_primary_assignment;
4526 --
4527 --
4528 --
4529 -- *** MAIN employ_applicant ***
4530 begin
4531 --
4532 -- hr_utility.trace_on;
4533 hr_utility.set_location('hr_person.employ_applicant',1);
4534 --
4535 v_tabrows := table_contents; -- #2264569
4536 hr_utility.trace(' ***** Table ==> '||v_tabrows);
4537 --
4538 -- Get new or existing period of service.
4539 --
4540 v_period_of_service_id := get_period_of_service(p_business_group_id
4541 ,p_person_id
4542 ,p_legislation_code
4543 ,p_emp_apl);
4544 --
4545 --
4546 if p_emp_apl = 'Y' then
4547 if p_update_primary_flag in ('Y','C','V') then
4548 --
4549 hr_utility.set_location('hr_person.employ_applicant',2);
4550 --
4551 --
4552 -- Date effectively end the current primary assignment
4553 --
4554 if p_new_primary_id is not null then
4555
4556 -- Bug 14596826
4557 l_proposal_exists := 'N';
4558 open prim_asg;
4559 fetch prim_asg into l_primary_asg;
4560 close prim_asg;
4561
4562 open get_primary_approved_proposal(l_primary_asg);
4563 fetch get_primary_approved_proposal into l_proposal_id;
4564 if get_primary_approved_proposal%found then
4565 l_proposal_exists := 'Y';
4566 end if;
4567 close get_primary_approved_proposal;
4568
4569 update_primary_assignment(p_business_group_id
4570 ,p_person_id
4571 ,p_start_date
4572 ,p_current_date
4573 ,p_user_id
4574 ,p_login_id
4575 );
4576 end if;
4577 --
4578 hr_utility.set_location('hr_person.employ_applicant',3);
4579 --
4580 --
4581 -- Make other accepted rows secondary
4582 --
4583 make_secondary(p_business_group_id
4584 ,p_person_id
4585 ,p_legislation_code
4586 ,p_assignment_status_type_id
4587 ,p_update_primary_flag
4588 ,p_new_primary_id
4589 ,p_user_id
4590 ,p_login_id
4591 ,p_start_date
4592 ,p_end_of_time
4593 ,p_employee_number
4594 ,p_set_of_books_id
4595 ,p_current_date);
4596 --
4597 hr_utility.set_location('hr_person.employ_applicant',4);
4598 --
4599 --
4600 -- Make a new primary assignment.
4601 --
4602 if p_new_primary_id is not null then
4603 create_primary_assignment(p_business_group_id
4604 ,p_person_id
4605 ,p_new_primary_id
4606 ,p_start_date
4607 ,p_end_of_time
4608 ,p_login_id
4609 ,p_user_id
4610 ,p_update_primary_flag
4611 ,p_employee_number
4612 ,p_set_of_books_id
4613 ,p_emp_apl
4614 );
4615 end if;
4616
4617 -- Bug 14596826
4618 if l_proposal_exists ='Y' and p_update_primary_flag = 'Y' then
4619 open get_primary_approved_proposal(p_new_primary_id);
4620 fetch get_primary_approved_proposal into l_proposal_id;
4621 if get_primary_approved_proposal%notfound then
4622 close get_primary_approved_proposal;
4623 hr_utility.set_message(800,'HR_289767_SALARY_BASIS_IS_NULL');
4624 hr_utility.raise_error;
4625 else
4626 close get_primary_approved_proposal;
4627 end if;
4628 end if;
4629
4630
4631 else -- update_primary_flag = 'N'
4632 --
4633 hr_utility.set_location('hr_person.employ_applicant',5);
4634 --
4635 --
4636 -- Create the accepted assignments as secondary assignments
4637 --
4638 make_secondary(p_business_group_id
4639 ,p_person_id
4640 ,p_legislation_code
4641 ,p_assignment_status_type_id
4642 ,p_update_primary_flag
4643 ,p_new_primary_id
4644 ,p_user_id
4645 ,p_login_id
4646 ,p_start_date
4647 ,p_end_of_time
4648 ,p_employee_number
4649 ,p_set_of_books_id
4650 ,p_current_date);
4651 end if;
4652
4653 else -- employing an applicant; update_primary_flag = 'N'
4654 --
4655 hr_utility.set_location('hr_person.employ_applicant',6);
4656 --
4657 --
4658 -- Create the accepted as secondary but make the chosen a primary.
4659 --
4660 make_secondary(p_business_group_id
4661 ,p_person_id
4662 ,p_legislation_code
4663 ,p_assignment_status_type_id
4664 ,p_update_primary_flag
4665 ,p_new_primary_id
4666 ,p_user_id
4667 ,p_login_id
4668 ,p_start_date
4669 ,p_end_of_time
4670 ,p_employee_number
4671 ,p_set_of_books_id
4672 ,p_current_date);
4673 --
4674 --
4675 hr_utility.set_location('hr_person.employ_applicant',7);
4676 --
4677 create_primary_assignment(p_business_group_id
4678 ,p_person_id
4679 ,p_new_primary_id
4680 ,p_start_date
4681 ,p_end_of_time
4682 ,p_login_id
4683 ,p_user_id
4684 ,p_update_primary_flag
4685 ,p_employee_number
4686 ,p_set_of_books_id
4687 ,p_emp_apl
4688 );
4689
4690 --
4691 -- 115.50 (START)
4692 --
4693 --
4694 -- Handle potentially overlapping PDS due to rehire before FPD
4695 --
4696 hr_employee_api.manage_rehire_primary_asgs
4697 (p_person_id => p_person_id
4698 ,p_rehire_date => p_start_date
4699 ,p_cancel => 'N'
4700 );
4701 --
4702 -- 115.50 (END)
4703 --
4704
4705 end if;
4706
4707 --Fix for the bug#12607547
4708 if per_otherbg_apl_api.isMultiRegVac(p_new_primary_id) and p_update_primary_flag in ('Y','V') then
4709 -- if per_otherbg_apl_api.isMultiRegVac(p_new_primary_id) then
4710 per_otherbg_apl_api.close_otherbg_applications(p_new_primary_id,p_start_date-1,'HIRE_CLOSE');
4711 end if;
4712 --
4713 -- hr_utility.trace_off;
4714 --
4715 --start WWBUG 2130950 hrwf synchronization --tpapired
4716 --
4717 declare
4718 l_asg_rec per_all_assignments_f%rowtype;
4719 cursor asg_cur is select *
4720 from per_all_assignments_f
4721 where primary_flag ='Y'
4722 and assignment_type = 'E'
4723 and person_id = p_person_id
4724 and business_group_id + 0 = p_business_group_id
4725 and p_start_date between effective_start_date
4726 and effective_end_date;
4727 begin
4728 open asg_cur;
4729 fetch asg_cur into l_asg_rec;
4730 close asg_cur;
4731 per_hrwf_synch.per_asg_wf(
4732 p_rec => l_asg_rec,
4733 p_action => 'INSERT');
4734 --
4735 end;
4736 --
4737 --End WWBUG 2130950 for hrwf synchronization -tpapired
4738 --
4739 -- fix 7120387
4740 if p_emp_apl <> 'Y' then -- handling only for Apl case as there are many probabilities
4741 -- which can cause regression if handled for emp.apl case and may also need Project Management
4742 -- approval
4743
4744 declare
4745
4746 l_asg_probation_det per_all_assignments_f%rowtype;
4747 cursor asg_cur is select *
4748 from per_all_assignments_f
4749 where
4750 assignment_type = 'E'
4751 and person_id = p_person_id
4752 and business_group_id + 0 = p_business_group_id
4753 and p_start_date between effective_start_date
4754 and effective_end_date;
4755
4756 cursor appl_rec_det(l_appl_id number) is
4757 select projected_hire_date
4758 from per_applications
4759 where application_id =l_appl_id;
4760
4761 l_date_probation_end date;
4762 l_proj_hire_date date;
4763
4764 begin
4765
4766 open asg_cur;
4767 loop
4768 fetch asg_cur into l_asg_probation_det;
4769 exit when asg_cur%notfound;
4770
4771 open appl_rec_det(l_asg_probation_det.application_id) ;
4772 fetch appl_rec_det into l_proj_hire_date;
4773 close appl_rec_det;
4774
4775 hr_utility.set_location('l_asg_probation_det.assignment_id :'||l_asg_probation_det.assignment_id,7);
4776 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,10);
4777 hr_utility.set_location('l_proj_hire_date :'||l_proj_hire_date,10);
4778
4779 if l_proj_hire_date is null then
4780
4781 if ( l_asg_probation_det.probation_period is not null)
4782 and
4783 (l_asg_probation_det.probation_unit is not null ) then
4784
4785
4786 hr_utility.set_location('p_start_date :'||p_start_date,10);
4787 hr_utility.set_location('l_asg_probation_det.assignment_id :'||l_asg_probation_det.assignment_id,10);
4788 hr_utility.set_location('l_asg_probation_det.probation_period :'||l_asg_probation_det.probation_period,10);
4789 hr_utility.set_location('l_asg_probation_det.probation_unit :'||l_asg_probation_det.probation_unit,10);
4790 l_date_probation_end :=NULL;
4791 hr_assignment.gen_probation_end
4792 (p_assignment_id => l_asg_probation_det.assignment_id
4793 ,p_probation_period => l_asg_probation_det.probation_period
4794 ,p_probation_unit => l_asg_probation_det.probation_unit
4795 ,p_start_date => p_start_date
4796 ,p_date_probation_end => l_date_probation_end
4797 );
4798 hr_utility.set_location('l_date_probation_end :'||l_date_probation_end,10);
4799
4800
4801 update per_all_assignments_f
4802 set date_probation_end =l_date_probation_end
4803 where
4804 assignment_type = 'E'
4805 and person_id = p_person_id
4806 and business_group_id + 0 = p_business_group_id
4807 and p_start_date between effective_start_date
4808 and effective_end_date
4809 and assignment_id = l_asg_probation_det.assignment_id;
4810
4811
4812 end if;
4813
4814
4815
4816 end if;
4817
4818 end loop;
4819 close asg_cur;
4820 end;
4821
4822 end if;
4823 -- end of date probation end
4824 -- fix 7120387
4825 -- Re-evaluate security access for the person.
4826 --
4827 hr_utility.set_location('hr_person.employ_applicant',8);
4828 --
4829 -- Bug 2534026
4830 -- Hard-code p_emp and p_apl as 'Y'. This will cause both employee and
4831 -- applicant assignments to beincluded when security access is re-evaluated
4832 --
4833 ins_per_list(p_person_id => p_person_id
4834 ,p_business_group_id => p_business_group_id
4835 ,p_legislation_code => p_legislation_code
4836 ,p_start_date => p_start_date
4837 ,p_apl => 'Y'
4838 ,p_emp => 'Y');
4839 end employ_applicant;
4840 --------------------------- END: employ_applicant --------------------
4841 procedure ins_per_list(p_person_id IN number
4842 ,p_business_group_id IN number
4843 ,p_legislation_code IN VARCHAR2
4844 ,p_start_date in date
4845 ,p_apl IN VARCHAR2
4846 ,p_emp IN VARCHAR2 ) is
4847 --
4848 l_dummy number;
4849 p_organization_id number;
4850 p_position_id number;
4851 p_payroll_id number;
4852 --
4853 -- Bug 605034. This cursor which gets run after the person list entries
4854 -- have been deleted must select from the base table rather than secure
4855 -- view
4856 --
4857 cursor ass_cur is
4858 select pa.assignment_id
4859 , pa.effective_start_date
4860 from per_all_assignments_f pa
4861 , per_assignment_status_types past
4862 where nvl(past.business_group_id,p_business_group_id) = pa.business_group_id + 0
4863 and pa.person_id = p_person_id
4864 and pa.business_group_id + 0 = p_business_group_id
4865 and
4866 (( p_apl = 'Y'
4867 and nvl(past.legislation_code, p_legislation_code) = p_legislation_code
4868 and past.per_system_status = 'ACCEPTED'
4869 and pa.assignment_type = 'A'
4870 and past.assignment_status_type_id = pa.assignment_status_type_id
4871 and p_start_date between pa.effective_start_date
4872 and pa.effective_end_date
4873 )
4874 --if this is a current employee, no need to check legislation code,or
4875 --system status.
4876 or
4877 (p_emp = 'Y'
4878 and pa.assignment_type = 'E'
4879 and p_start_date between pa.effective_start_date
4880 and pa.effective_end_date
4881 and past.assignment_status_type_id = pa.assignment_status_type_id));
4882
4883 --
4884 cursor check_past_pds is
4885 select 1
4886 from per_periods_of_service pps
4887 where pps.person_id =p_person_id
4888 and date_start <= (select effective_date
4889 from fnd_sessions
4890 where session_id =
4891 userenv('sessionid'));
4892 cursor check_pds is
4893 select 1
4894 from per_periods_of_service pps
4895 where pps.person_id =p_person_id;
4896
4897 begin
4898 hr_utility.set_location('Entering : hr_person.ins_per_list',5);
4899 open check_past_pds;
4900 fetch check_past_pds into l_dummy;
4901 if check_past_pds%FOUND then
4902 close check_past_pds;
4903 hr_utility.set_location('hr_person.ins_per_list',10);
4904 hr_security_internal.clear_from_person_list(p_person_id);
4905 else
4906 close check_past_pds;
4907 open check_pds;
4908 fetch check_pds into l_dummy;
4909 if check_pds%notfound then
4910 close check_pds;
4911 hr_utility.set_location('hr_person.ins_per_list',15);
4912 hr_security_internal.clear_from_person_list(p_person_id);
4913 else
4914 close check_pds;
4915 end if;
4916 end if;
4917 --
4918 hr_utility.set_location('hr_person.ins_per_list',20);
4919 --
4920 for asg_rec in ass_cur loop
4921 hr_security_internal.add_to_person_list
4922 (p_assignment_id => asg_rec.assignment_id
4923 ,p_effective_date => asg_rec.effective_start_date);
4924 end loop;
4925 --
4926 hr_utility.set_location('Leaving : hr_person.ins_per_list',30);
4927 --
4928 end ins_per_list;
4929 -- +------------------END: ins_per_list ----------------------------------+
4930 --
4931 -- +-----------------------------------------------------------------------+
4932 -- +------------------ BEGIN: end_unaccepted_app_assign -------------------+
4933 -- +-----------------------------------------------------------------------+
4934 procedure end_unaccepted_app_assign(p_person_id IN INTEGER
4935 ,p_business_group_id IN INTEGER
4936 ,p_legislation_code IN VARCHAR2
4937 ,p_end_date IN DATE
4938 -- #2264569
4939 ,p_table IN HR_EMPLOYEE_APPLICANT_API.t_ApplTable
4940 ) IS
4941
4942 /*
4943 NAME
4944 end_unaccepted_app_assign
4945 DESCRIPTION
4946 End all Unaccepted assignments. ~~~ End CHOSEN unaccepted assignments
4947 PARAMETERS
4948 p_business_group_id : Current business group.
4949 p_legislation_code : Current Operating Legislation.
4950 p_end_date : Date the applicant hired.
4951 */
4952 l_end_date DATE; -- Day before hire.
4953 --
4954 -- # 2264569
4955 --
4956 l_asgid per_assignments_f.assignment_id%TYPE;
4957 --
4958 cursor unacc_cur is
4959 select pa.assignment_id
4960 from per_assignments_f pa
4961 where pa.person_id = p_person_id
4962 and pa.business_group_id + 0 = p_business_group_id
4963 and pa.assignment_type = 'A'
4964 and p_end_date between pa.effective_start_date
4965 and pa.effective_end_date -- fix for bug 6036285
4966 and pa.assignment_status_type_id IN (
4967 select past.assignment_status_type_id
4968 from per_assignment_status_types past
4969 , per_ass_status_type_amends pasa
4970 where pasa.assignment_status_type_id(+)=
4971 past.assignment_status_type_id
4972 and pasa.business_group_id(+) + 0 = p_business_group_id
4973 and nvl(past.business_group_id,p_business_group_id) =
4974 p_business_group_id
4975 and nvl(past.legislation_code, p_legislation_code) =
4976 p_legislation_code
4977 and nvl(pasa.per_system_status,past.per_system_status) <>
4978 'ACCEPTED'
4979 )
4980 for update of pa.effective_end_date;
4981 --
4982 l_rowcount number;
4983
4984 --fix for bug 6036285 Starts here.
4985 l_assignment_status_type_id number;
4986 l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
4987 l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
4988
4989 --fix for bug 6036285 ends here.
4990
4991 --
4992 begin
4993 --
4994 l_end_date := p_end_date -1;
4995 --
4996 -- #2264569
4997 --
4998 hr_utility.set_location('IN hrhirapl.end_unaccepted_app_assign',303);
4999 open unacc_cur;
5000 loop
5001 fetch unacc_cur into l_asgid;
5002 exit when unacc_cur%NOTFOUND;
5003 begin
5004 if hr_employee_applicant_api.end_date_exists(p_table,l_asgid) <> 2 then
5005 hr_utility.set_location('hrhirapl.end_unaccepted_app_assign',305);
5006 hr_utility.trace(' **** Asg ID : '||to_char(l_asgid));
5007
5008 update per_assignments pa
5009 set pa.effective_end_date = l_end_date
5010 where current of unacc_cur;
5011 --Fix for bug 6514078 starts here
5012 --fix for bug 6036285 Starts here.
5013 per_people3_pkg.get_default_person_type
5014 (p_required_type => 'TERM_APL'
5015 ,p_business_group_id => p_business_group_id
5016 ,p_legislation_code => p_legislation_code
5017 ,p_person_type => l_assignment_status_type_id
5018 );
5019
5020 IRC_ASG_STATUS_API.create_irc_asg_status
5021 (p_assignment_id => l_asgid
5022 ,p_assignment_status_type_id => l_assignment_status_type_id
5023 ,p_status_change_date => p_end_date
5024 ,p_assignment_status_id => l_asg_status_id
5025 ,p_object_version_number => l_asg_status_ovn);
5026 --fix for bug 6036285 ends here.
5027 --Fix for bug 6514078 ends here
5028 --Fix for bug 7289811 starts here
5029 IRC_OFFERS_API.close_offer
5030 ( p_validate => false
5031 ,p_effective_date => l_end_date
5032 ,p_applicant_assignment_id => l_asgid
5033 ,p_change_reason => 'MANUAL_CLOSURE'
5034 );
5035 --Fix for bug 7289811 end here
5036
5037 if per_otherbg_apl_api.isMultiRegVac(l_asgid) then
5038 per_otherbg_apl_api.close_otherbg_applications(l_asgid,l_end_date-1,'HIRE_END');
5039 end if;
5040 end if;
5041 exception
5042 when others then
5043 close unacc_cur;
5044 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5045 hr_utility.set_message_token('PROCEDURE','end_unaccepted_app_assign');
5046 hr_utility.set_message_token('STEP','1');
5047 hr_utility.raise_error;
5048 end;
5049 end loop;
5050 close unacc_cur;
5051 hr_utility.set_location('OUT hrhirapl.end_unaccepted_app_assign',310);
5052
5053 end end_unaccepted_app_assign;
5054 --
5055 -- +----------------------END: end_unaccepted_app_assign -------------+
5056 --
5057 -- +--------------------------BEGIN: end_bookings --------------------+
5058 procedure end_bookings(p_person_id number
5059 ,p_business_group_id number
5060 ,p_start_date DATE) is
5061 --
5062 l_event_id number;
5063 l_booking_id number;
5064 l_no_of_rows number;
5065 l_final_no number;
5066 --
5067 cursor events is
5068 select pe.event_id
5069 from per_events pe
5070 , per_assignments_f a
5071 where pe.business_group_id +0 = a.business_group_id
5072 and a.business_group_id = p_business_group_id
5073 and pe.assignment_id = a.assignment_id
5074 and pe.date_start >=p_start_date
5075 and a.person_id = p_person_id
5076 and p_start_date between a.effective_start_date
5077 and a.effective_end_date
5078 and pe.event_or_interview = 'E'
5079 for update of event_id;
5080 --
5081 cursor bookings is
5082 select booking_id
5083 from per_bookings pb
5084 where pb.event_id = l_event_id
5085 for update of booking_id;
5086 --
5087 begin
5088 --
5089 -- Lock the Events and bookings.
5090 --
5091 open events;
5092 loop
5093 fetch events into l_event_id;
5094 exit when events%NOTFOUND;
5095 open bookings;
5096 loop
5097 fetch bookings into l_booking_id;
5098 exit when bookings%notfound;
5099 end loop;
5100 close bookings;
5101 end loop;
5102 l_no_of_rows := events%rowcount; -- get the number of events locked.
5103 close events;
5104 --
5105 -- delete the bookings.
5106 --
5107 l_final_no := l_no_of_rows; -- set counter same.
5108 open events;
5109 loop
5110 fetch events into l_event_id;
5111 exit when events%NOTFOUND;
5112 --
5113 delete from per_bookings pb
5114 where pb.event_id = l_event_id;
5115 --
5116 end loop;
5117 close events;
5118 --
5119 -- Delete the events.
5120 --
5121 open events;
5122 loop
5123 fetch events into l_event_id;
5124 exit when events%NOTFOUND;
5125 --
5126 delete from per_events
5127 where event_id = l_event_id;
5128 l_final_no := l_final_no - sql%rowcount;
5129 end loop;
5130 if l_final_no <> 0 then
5131 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5132 hr_utility.set_message_token('PROCEDURE','end_bookings');
5133 hr_utility.set_message_token('STEP','10');
5134 hr_utility.raise_error;
5135 end if;
5136 --
5137 end end_bookings;
5138 ----------------------------END: end_bookings --------------------
5139 end hrhirapl;