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