[Home] [Help]
PACKAGE BODY: APPS.IRC_OFFERS_API
Source
1 Package Body IRC_OFFERS_API as
2 /* $Header: iriofapi.pkb 120.41 2011/04/08 12:04:28 amikukum ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'IRC_OFFERS_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------< create_offer_assignment_copy >---------------------|
10 -- ----------------------------------------------------------------------------
11 -- This procedure duplicates an offer assignment from the source assignment id
12 -- which could either be the applicant assignment or the previous offer
13 -- assignment.
14 -- ----------------------------------------------------------------------------
15 --
16 procedure create_offer_assignment_copy
17 ( P_VALIDATE IN boolean default false
18 ,P_EFFECTIVE_DATE IN date default null
19 ,P_SOURCE_ASSIGNMENT_ID IN number
20 ,P_OFFER_ASSIGNMENT_ID OUT nocopy number
21 ) Is
22 --
23 l_proc varchar2(72) := g_package||'create_offer_assignment_copy';
24 --
25 -- Out and In Out variables
26 --
27 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
28 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
29 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
30 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
31 l_offer_assignment_id per_all_assignments_f.assignment_id%TYPE;
32 l_comment_id per_all_assignments_f.comment_id%TYPE;
33 l_object_version_number per_all_assignments_f.object_version_number%TYPE;
34 l_other_manager_warning boolean;
35 l_hourly_salaried_warning boolean;
36
37 l_validation_start_date date;
38 l_validation_end_date date;
39 --
40 -- variables to be set
41 --
42 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
43 l_primary_flag per_all_assignments_f.primary_flag%TYPE;
44 --
45 -- Date Variables
46 --
47 l_date_probation_end per_all_assignments_f.date_probation_end%TYPE;
48 l_effective_date date;
49 l_program_update_date per_all_assignments_f.program_update_date%TYPE;
50 --
51 -- Define cursor
52 --
53 cursor csr_assignment_record is
54 select
55 business_group_id
56 ,recruiter_id
57 ,grade_id
58 ,position_id
59 ,job_id
60 ,assignment_status_type_id
61 ,payroll_id
62 ,location_id
63 ,person_referred_by_id
64 ,supervisor_id
65 ,special_ceiling_step_id
66 ,person_id
67 ,recruitment_activity_id
68 ,source_organization_id
69 ,organization_id
70 ,people_group_id
71 ,soft_coding_keyflex_id
72 ,vacancy_id
73 ,pay_basis_id
74 ,assignment_sequence
75 ,assignment_type
76 ,primary_flag
77 ,application_id
78 ,assignment_number
79 ,change_reason
80 ,comment_id
81 ,date_probation_end
82 ,default_code_comb_id
83 ,employment_category
84 ,frequency
85 ,internal_address_line
86 ,manager_flag
87 ,normal_hours
88 ,perf_review_period
89 ,perf_review_period_frequency
90 ,period_of_service_id
91 ,probation_period
92 ,probation_unit
93 ,sal_review_period
94 ,sal_review_period_frequency
95 ,set_of_books_id
96 ,source_type
97 ,time_normal_finish
98 ,time_normal_start
99 ,bargaining_unit_code
100 ,labour_union_member_flag
101 ,hourly_salaried_code
102 ,request_id
103 ,program_application_id
104 ,program_id
105 ,program_update_date
106 ,ass_attribute_category
107 ,ass_attribute1
108 ,ass_attribute2
109 ,ass_attribute3
110 ,ass_attribute4
111 ,ass_attribute5
112 ,ass_attribute6
113 ,ass_attribute7
114 ,ass_attribute8
115 ,ass_attribute9
116 ,ass_attribute10
117 ,ass_attribute11
118 ,ass_attribute12
119 ,ass_attribute13
120 ,ass_attribute14
121 ,ass_attribute15
122 ,ass_attribute16
123 ,ass_attribute17
124 ,ass_attribute18
125 ,ass_attribute19
126 ,ass_attribute20
127 ,ass_attribute21
128 ,ass_attribute22
129 ,ass_attribute23
130 ,ass_attribute24
131 ,ass_attribute25
132 ,ass_attribute26
133 ,ass_attribute27
134 ,ass_attribute28
135 ,ass_attribute29
136 ,ass_attribute30
137 ,title
138 ,contract_id
139 ,establishment_id
140 ,collective_agreement_id
141 ,cagr_grade_def_id
142 ,cagr_id_flex_num
143 ,notice_period
144 ,notice_period_uom
145 ,employee_category
146 ,work_at_home
147 ,job_post_source_name
148 ,posting_content_id
149 ,vendor_id
150 ,vendor_employee_number
151 ,vendor_assignment_number
152 ,assignment_category
153 ,project_title
154 ,applicant_rank
155 ,grade_ladder_pgm_id
156 ,supervisor_assignment_id
157 ,object_version_number
158 from per_all_assignments_f
159 where assignment_id = p_source_assignment_id
160 and p_effective_date
161 between effective_start_date
162 and effective_end_date;
163
164 l_offer_assignment csr_assignment_record%ROWTYPE;
165 --
166 Begin
167
168 hr_utility.set_location('Entering:'||l_proc,10);
169 --
170 -- Issue a savepoint
171 --
172 savepoint CREATE_OFFER_ASSIGNMENT_COPY;
173 --
174 open csr_assignment_record;
175 fetch csr_assignment_record into l_offer_assignment;
176 --
177 if csr_assignment_record%notfound
178 then
179 --
180 close csr_assignment_record;
181 fnd_message.set_name('PER','HR_289541_PJU_INV_ASG_ID');
182 fnd_message.raise_error;
183 --
184 end if;
185 close csr_assignment_record;
186 --
187 hr_utility.set_location(l_proc,20);
188 --
189 hr_utility.set_location(l_proc,30);
190 --
191 --
192 -- Truncate the time portion from all IN date parameters
193 --
194 l_effective_date := trunc(p_effective_date);
195 l_date_probation_end := trunc(l_offer_assignment.date_probation_end);
196 l_program_update_date := trunc(l_offer_assignment.program_update_date);
197 --
198 -- The offer record must have an assignment_type = 'O'
199 -- and the primary_flag must be set to 'N'
200 --
201 l_assignment_type := 'O';
202 l_primary_flag := 'N';
203 --
204 hr_utility.set_location(l_proc,40);
205 --
206 -- Create Offer Assignment record
207 --
208 per_asg_ins.ins
209 ( p_assignment_id => l_offer_assignment_id
210 ,p_effective_start_date => l_effective_start_date
211 ,p_effective_end_date => l_effective_end_date
212 ,p_business_group_id => l_offer_assignment.business_group_id
213 ,p_recruiter_id => l_offer_assignment.recruiter_id
214 ,p_grade_id => l_offer_assignment.grade_id
215 ,p_position_id => l_offer_assignment.position_id
216 ,p_job_id => l_offer_assignment.job_id
217 ,p_assignment_status_type_id => l_offer_assignment.assignment_status_type_id
218 ,p_payroll_id => l_offer_assignment.payroll_id
219 ,p_location_id => l_offer_assignment.location_id
220 ,p_person_referred_by_id => l_offer_assignment.person_referred_by_id
221 ,p_supervisor_id => l_offer_assignment.supervisor_id
222 ,p_special_ceiling_step_id => l_offer_assignment.special_ceiling_step_id
223 ,p_person_id => l_offer_assignment.person_id
224 ,p_recruitment_activity_id => l_offer_assignment.recruitment_activity_id
225 ,p_source_organization_id => l_offer_assignment.source_organization_id
226 ,p_organization_id => l_offer_assignment.organization_id
227 ,p_people_group_id => l_offer_assignment.people_group_id
228 ,p_soft_coding_keyflex_id => l_offer_assignment.soft_coding_keyflex_id
229 ,p_vacancy_id => l_offer_assignment.vacancy_id
230 ,p_pay_basis_id => l_offer_assignment.pay_basis_id
231 ,p_assignment_sequence => l_assignment_sequence
232 ,p_assignment_type => l_assignment_type
233 ,p_primary_flag => l_primary_flag
234 ,p_application_id => l_offer_assignment.application_id
235 ,p_assignment_number => l_assignment_number
236 ,p_change_reason => l_offer_assignment.change_reason
237 ,p_comment_id => l_comment_id
238 ,p_date_probation_end => l_date_probation_end
239 ,p_default_code_comb_id => l_offer_assignment.default_code_comb_id
240 ,p_employment_category => l_offer_assignment.employment_category
241 ,p_frequency => l_offer_assignment.frequency
242 ,p_internal_address_line => l_offer_assignment.internal_address_line
243 ,p_manager_flag => l_offer_assignment.manager_flag
244 ,p_normal_hours => l_offer_assignment.normal_hours
245 ,p_perf_review_period => l_offer_assignment.perf_review_period
246 ,p_perf_review_period_frequency => l_offer_assignment.perf_review_period_frequency
247 ,p_period_of_service_id => l_offer_assignment.period_of_service_id
248 ,p_probation_period => l_offer_assignment.probation_period
249 ,p_probation_unit => l_offer_assignment.probation_unit
250 ,p_sal_review_period => l_offer_assignment.sal_review_period
251 ,p_sal_review_period_frequency => l_offer_assignment.sal_review_period_frequency
252 ,p_set_of_books_id => l_offer_assignment.set_of_books_id
253 ,p_source_type => l_offer_assignment.source_type
254 ,p_time_normal_finish => l_offer_assignment.time_normal_finish
255 ,p_time_normal_start => l_offer_assignment.time_normal_start
256 ,p_bargaining_unit_code => l_offer_assignment.bargaining_unit_code
257 ,p_labour_union_member_flag => l_offer_assignment.labour_union_member_flag
258 ,p_hourly_salaried_code => l_offer_assignment.hourly_salaried_code
259 ,p_request_id => l_offer_assignment.request_id
260 ,p_program_application_id => l_offer_assignment.program_application_id
261 ,p_program_id => l_offer_assignment.program_id
262 ,p_program_update_date => l_program_update_date
263 ,p_ass_attribute_category => l_offer_assignment.ass_attribute_category
264 ,p_ass_attribute1 => l_offer_assignment.ass_attribute1
265 ,p_ass_attribute2 => l_offer_assignment.ass_attribute2
266 ,p_ass_attribute3 => l_offer_assignment.ass_attribute3
267 ,p_ass_attribute4 => l_offer_assignment.ass_attribute4
268 ,p_ass_attribute5 => l_offer_assignment.ass_attribute5
269 ,p_ass_attribute6 => l_offer_assignment.ass_attribute6
270 ,p_ass_attribute7 => l_offer_assignment.ass_attribute7
271 ,p_ass_attribute8 => l_offer_assignment.ass_attribute8
272 ,p_ass_attribute9 => l_offer_assignment.ass_attribute9
273 ,p_ass_attribute10 => l_offer_assignment.ass_attribute10
274 ,p_ass_attribute11 => l_offer_assignment.ass_attribute11
275 ,p_ass_attribute12 => l_offer_assignment.ass_attribute12
276 ,p_ass_attribute13 => l_offer_assignment.ass_attribute13
277 ,p_ass_attribute14 => l_offer_assignment.ass_attribute14
278 ,p_ass_attribute15 => l_offer_assignment.ass_attribute15
279 ,p_ass_attribute16 => l_offer_assignment.ass_attribute16
280 ,p_ass_attribute17 => l_offer_assignment.ass_attribute17
281 ,p_ass_attribute18 => l_offer_assignment.ass_attribute18
282 ,p_ass_attribute19 => l_offer_assignment.ass_attribute19
283 ,p_ass_attribute20 => l_offer_assignment.ass_attribute20
284 ,p_ass_attribute21 => l_offer_assignment.ass_attribute21
285 ,p_ass_attribute22 => l_offer_assignment.ass_attribute22
286 ,p_ass_attribute23 => l_offer_assignment.ass_attribute23
287 ,p_ass_attribute24 => l_offer_assignment.ass_attribute24
288 ,p_ass_attribute25 => l_offer_assignment.ass_attribute25
289 ,p_ass_attribute26 => l_offer_assignment.ass_attribute26
290 ,p_ass_attribute27 => l_offer_assignment.ass_attribute27
291 ,p_ass_attribute28 => l_offer_assignment.ass_attribute28
292 ,p_ass_attribute29 => l_offer_assignment.ass_attribute29
293 ,p_ass_attribute30 => l_offer_assignment.ass_attribute30
294 ,p_title => l_offer_assignment.title
295 ,p_object_version_number => l_object_version_number
296 ,p_other_manager_warning => l_other_manager_warning
297 ,p_hourly_salaried_warning => l_hourly_salaried_warning
298 ,p_effective_date => l_effective_date
299 ,p_validate => p_validate
300 ,p_contract_id => l_offer_assignment.contract_id
301 ,p_establishment_id => l_offer_assignment.establishment_id
302 ,p_collective_agreement_id => l_offer_assignment.collective_agreement_id
303 ,p_cagr_grade_def_id => l_offer_assignment.cagr_grade_def_id
304 ,p_cagr_id_flex_num => l_offer_assignment.cagr_id_flex_num
305 ,p_notice_period => l_offer_assignment.notice_period
306 ,p_notice_period_uom => l_offer_assignment.notice_period_uom
307 ,p_employee_category => l_offer_assignment.employee_category
308 ,p_work_at_home => l_offer_assignment.work_at_home
309 ,p_job_post_source_name => l_offer_assignment.job_post_source_name
310 ,p_posting_content_id => l_offer_assignment.posting_content_id
311 ,p_vendor_id => l_offer_assignment.vendor_id
312 ,p_vendor_employee_number => l_offer_assignment.vendor_employee_number
313 ,p_vendor_assignment_number => l_offer_assignment.vendor_assignment_number
314 ,p_assignment_category => l_offer_assignment.assignment_category
315 ,p_project_title => l_offer_assignment.project_title
316 ,p_applicant_rank => l_offer_assignment.applicant_rank
317 ,p_grade_ladder_pgm_id => l_offer_assignment.grade_ladder_pgm_id
318 ,p_supervisor_assignment_id => l_offer_assignment.supervisor_assignment_id
319 );
320 --
321 hr_utility.set_location(l_proc, 50);
322 --
323 --
324 -- When in validation only mode raise the Validate_Enabled exception
325 --
326 if p_validate then
327 raise hr_api.validate_enabled;
328 end if;
329 --
330 p_offer_assignment_id := l_offer_assignment_id;
331 --
332 hr_utility.set_location(' Leaving:'||l_proc, 60);
333 --
334 exception
335 when hr_api.validate_enabled then
336 --
337 p_offer_assignment_id := null;
338 --
339 -- As the Validate_Enabled exception has been raised
340 -- we must rollback to the savepoint
341 --
342 rollback to CREATE_OFFER_ASSIGNMENT_COPY;
343 --
344 hr_utility.set_location(' Leaving:'||l_proc, 70);
345 when others then
346 --
347 p_offer_assignment_id := null;
348 --
349 -- A validation or unexpected error has occured
350 --
351 rollback to CREATE_OFFER_ASSIGNMENT_COPY;
352 --
353 hr_utility.set_location(' Leaving:'||l_proc, 80);
354 raise;
355 end create_offer_assignment_copy;
356 --
357 -- ----------------------------------------------------------------------------
358 -- |-----------------------< update_appl_assg_status >------------------------|
359 -- ----------------------------------------------------------------------------
360 --
361 procedure update_appl_assg_status
362 ( P_VALIDATE IN boolean default false
363 ,P_EFFECTIVE_DATE IN date default null
364 ,P_APPLICANT_ASSIGNMENT_ID IN NUMBER
365 ,P_OFFER_STATUS IN VARCHAR2
366 ,P_CHANGE_REASON IN VARCHAR2 default null
367 ,P_CALL_FOR_BASE_ASG IN boolean default false
368 ) is
369 --
370 l_proc varchar2(72) := g_package||'update_appl_assg_status';
371 l_prev_assg_status_type per_all_assignments_f.assignment_status_type_id%TYPE;
372 l_assignment_status_type_id per_all_assignments_f.assignment_status_type_id%TYPE := -1;
373 l_curr_assg_status_type_id per_all_assignments_f.assignment_status_type_id%TYPE;
374 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
375 --
376 l_datetrack_mode varchar2(30);
377 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
378 l_expected_system_status per_assignment_status_types.per_system_status%TYPE;
379 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
380 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
381 l_base_asg_id per_all_assignments_f.assignment_id%TYPE;
382 --
383 cursor csr_assignment_ovn is
384 select object_version_number
385 ,effective_start_date
386 ,assignment_status_type_id
387 ,business_group_id
388 from per_all_assignments_f
389 where assignment_id = p_applicant_assignment_id
390 and p_effective_date
391 between effective_start_date
392 and effective_end_date;
393 --
394 --
395 cursor csr_get_offer_user_status(p_business_group_id number) is
396 select ASSIGNMENT_STATUS_TYPE_ID
397 from PER_ASSIGNMENT_STATUS_TYPES_V
398 where PER_SYSTEM_STATUS='OFFER'
399 and DEFAULT_FLAG = 'Y'
400 and BUSINESS_GROUP_ID = p_business_group_id;
401 --
402 cursor csr_get_accepted_user_status(p_business_group_id number) is
403 select ASSIGNMENT_STATUS_TYPE_ID
404 from PER_ASSIGNMENT_STATUS_TYPES_V
405 where PER_SYSTEM_STATUS='ACCEPTED'
406 and DEFAULT_FLAG = 'Y'
407 and BUSINESS_GROUP_ID = p_business_group_id;
408 --
409 cursor csr_prev_assg_status_type(p_business_group_id number) is
410 select ias1.assignment_status_type_id
411 ,past.per_system_status
412 from irc_assignment_statuses ias1
413 ,per_assignment_status_types past
414 where past.assignment_status_type_id = ias1.assignment_status_type_id
415 and ias1.assignment_id = p_applicant_assignment_id
416 and ias1.creation_date = (select max(ias2.creation_date)
417 from irc_assignment_statuses ias2
418 where ias2.assignment_id = p_applicant_assignment_id
419 and ias2.assignment_status_type_id not in (5,6)
420 and ias2.assignment_status_type_id not in (select ASSIGNMENT_STATUS_TYPE_ID
421 from PER_ASSIGNMENT_STATUS_TYPES_V
422 where PER_SYSTEM_STATUS in ('OFFER','ACCEPTED')
423 and BUSINESS_GROUP_ID = p_business_group_id));
424 --
425 cursor csr_linked_base_asg is
426 select src_apl_asg_id
427 from per_vac_linked_assignments
428 where tgt_apl_asg_id=p_applicant_assignment_id;
429 --
430 Begin
431 --
432 hr_utility.set_location('Entering:'||l_proc,10);
433 --
434 -- Since this procedure is called only when the status is changed
435 -- we do not need to see if the assignment_status_type_id is already
436 -- set to the required value.
437 --
438 open csr_assignment_ovn;
439 fetch csr_assignment_ovn into l_asg_object_version_number
440 ,l_effective_start_date
441 ,l_curr_assg_status_type_id
442 ,l_business_group_id;
443 if csr_assignment_ovn%notfound
444 then
445 close csr_assignment_ovn;
446 --
447 hr_utility.set_location(l_proc,70);
448 --
449 --
450 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
451 fnd_message.raise_error;
452 --
453 end if;
454 close csr_assignment_ovn;
455 --
456 if p_change_reason = 'UPDATED' or p_change_reason = 'APL_DECLINED_ACCEPTANCE' or p_change_reason = 'MGR_WITHDRAW'
457 then
458 --
459 -- This is the case where the offer is updated when in EXTENDED or CLOSED status.
460 -- 1) The EXTENDED offer is closed and a new offer version is
461 -- created. We hence need to set back the Applicant's assignment status to
462 -- the status which existed before the most recent 'OFFER' Applicant assignment status.
463 -- 2) The offer was previously in CLOSED status before the Applicant
464 -- WITHDREW his application it could have been CLOSED previously due to 2
465 -- reasons:
466 -- 1. Applicant Accepted the offer - We need to rollback 2 steps here.
467 -- One, ACCEPTED assignment status (6)
468 -- Two, OFFER assignment status (5)
469 -- 2. Applicant Declined the offer - Do nothing in this case
470 -- or, Offer Duration Expired.
471 --
472 open csr_prev_assg_status_type(l_business_group_id);
473 fetch csr_prev_assg_status_type into l_prev_assg_status_type
474 ,l_expected_system_status;
475 if csr_prev_assg_status_type%notfound
476 then
477 close csr_prev_assg_status_type;
478 --
479 hr_utility.set_location(l_proc,40);
480 --
481 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
482 fnd_message.raise_error;
483 --
484 end if;
485 close csr_prev_assg_status_type;
486 --
487 if( l_prev_assg_status_type <> l_curr_assg_status_type_id )
488 then
489 --
490 l_assignment_status_type_id := l_prev_assg_status_type;
491 --
492 end if;
493 --
494 elsif p_offer_status = 'EXTENDED'
495 then
496 --
497 hr_utility.set_location(l_proc,20);
498 --
499 -- Update the applicant assignment record and set the assignment
500 -- status type to 'Offer' ( assignment_status_type_id = 5 )
501 --
502 open csr_get_offer_user_status(l_business_group_id);
503 fetch csr_get_offer_user_status into l_assignment_status_type_id;
504 if csr_get_offer_user_status%notfound then
505 hr_utility.set_location(l_proc,21);
506 l_assignment_status_type_id := 5;
507 end if;
508 close csr_get_offer_user_status;
509 --
510 elsif p_offer_status = 'CLOSED'
511 then
512 --
513 hr_utility.set_location(l_proc,25);
514 --
515 if p_change_reason = 'EXPIRED'
516 or p_change_reason = 'APL_DECLINED'
517 then
518 --
519 -- If the offer has either expired or the applicant has declined the offer,
520 -- the applicant assignment status should be set to the assignment status
521 -- prior to offer.
522 --
523 hr_utility.set_location(l_proc,30);
524 --
525 -- In this scenario, we need to pick up the applicant assignment status
526 -- of the assignment record prior to the latest assignment record, becasue
527 -- the latest assignment record would have applicant assignment status = 'OFFER'
528 --
529 open csr_prev_assg_status_type(l_business_group_id);
530 fetch csr_prev_assg_status_type into l_prev_assg_status_type
531 ,l_expected_system_status;
532 if csr_prev_assg_status_type%notfound
533 then
534 close csr_prev_assg_status_type;
535 --
536 hr_utility.set_location(l_proc,40);
537 --
538 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
539 fnd_message.raise_error;
540 --
541 end if;
542 close csr_prev_assg_status_type;
543 --Update only if it is an applicant assignment
544 if l_expected_system_status <> 'ACTIVE_APL'
545 and l_expected_system_status <> 'OFFER'
546 and l_expected_system_status <> 'ACCEPTED'
547 and l_expected_system_status <> 'INTERVIEW1'
548 and l_expected_system_status <> 'INTERVIEW2'
549 then
550 l_assignment_status_type_id := -1;
551 else
552 l_assignment_status_type_id := l_prev_assg_status_type;
553 end if;
554 --
555 elsif p_change_reason = 'APL_ACCEPTED'
556 then
557 --
558 hr_utility.set_location(l_proc,50);
559 --
560 -- if the offer has been accepted by the applicant, the applicant assignment
561 -- status should be set to 'Accepted' ( assignment_status_type_id = 6 )
562 --
563 open csr_get_accepted_user_status(l_business_group_id);
564 fetch csr_get_accepted_user_status into l_assignment_status_type_id;
565 if csr_get_accepted_user_status%notfound then
566 hr_utility.set_location(l_proc,51);
567 l_assignment_status_type_id := 6;
568 end if;
569 close csr_get_accepted_user_status;
570 --
571 end if;
572 --
573 end if;
574 --
575 -- only if the assignment_status_type has changed, call 'upd' procedure.
576 --
577 if l_assignment_status_type_id <> -1
578 then
579 --
580 hr_utility.set_location(l_proc,60);
581 --
582 -- Decide the date track mode.
583 --
584 if l_effective_start_date = p_effective_date
585 then
586 --
587 hr_utility.set_location(l_proc,80);
588 --
589 -- Since the current record has started today, we need to
590 -- correct the existing record.
591 --
592 l_datetrack_mode := hr_api.g_correction;
593 else
594 --
595 hr_utility.set_location(l_proc,90);
596 --
597 -- End the existing record and create a new record.
598 --
599 l_datetrack_mode := hr_api.g_update;
600 end if;
601 --
602 -- Call update_status_type_apl_asg in the required date track mode.
603 --
604 hr_assignment_internal.update_status_type_apl_asg
605 ( p_effective_date => p_effective_date
606 ,p_datetrack_update_mode => l_datetrack_mode
607 ,p_assignment_id => p_applicant_assignment_id
608 ,p_object_version_number => l_asg_object_version_number
609 ,p_expected_system_status => l_expected_system_status
610 ,p_assignment_status_type_id => l_assignment_status_type_id
611 ,p_effective_start_date => l_effective_start_date
612 ,p_effective_end_date => l_effective_end_date
613 );
614 --
615 end if;
616 --
617 if P_CALL_FOR_BASE_ASG = false then
618 --
619 open csr_linked_base_asg;
620 fetch csr_linked_base_asg into L_BASE_ASG_ID;
621 --
622 if csr_linked_base_asg%found then
623 update_appl_assg_status
624 ( P_VALIDATE =>P_VALIDATE
625 ,P_EFFECTIVE_DATE =>P_EFFECTIVE_DATE
626 ,P_APPLICANT_ASSIGNMENT_ID =>L_BASE_ASG_ID
627 ,P_OFFER_STATUS =>P_OFFER_STATUS
628 ,P_CHANGE_REASON =>P_CHANGE_REASON
629 ,P_CALL_FOR_BASE_ASG =>true
630 );
631 end if;
632 --
633 close csr_linked_base_asg;
634 --
635 end if;
636 --
637 hr_utility.set_location(' Leaving:'||l_proc, 100);
638 --
639 exception
640 when others then
641 hr_utility.set_location(' Leaving:'||l_proc, 110);
642 raise;
643 --
644 end update_appl_assg_status;
645 --
646 -- ----------------------------------------------------------------------------
647 -- |----------------------< update_latest_offer_flag >------------------------|
648 -- ----------------------------------------------------------------------------
649 --
650 procedure update_latest_offer_flag
651 ( P_VALIDATE IN boolean default false
652 ,P_EFFECTIVE_DATE IN date default null
653 ,P_OFFER_ID IN NUMBER
654 ,P_OFFER_STATUS IN VARCHAR2 default hr_api.g_varchar2
655 ,P_LATEST_OFFER IN VARCHAR2
656 ,P_CHANGE_REASON IN VARCHAR2 default null
657 ,P_STATUS_CHANGE_DATE IN date default null
658 ) is
659 --
660 l_proc varchar2(72) := g_package||'update_latest_offer_flag';
661 l_object_version_number irc_offers.object_version_number%TYPE;
662 l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
663 l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
664 l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
665 l_offer_version irc_offers.offer_version%TYPE;
666 l_updated_offer_status irc_offers.offer_status%TYPE;
667 --
668 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
669 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
670 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
671 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
672 l_validation_start_date date;
673 l_validation_end_date date;
674 l_org_now_no_manager_warning boolean;
675 --
676 cursor csr_offer_details is
677 select offer_assignment_id
678 ,object_version_number
679 ,offer_status
680 from irc_offers
681 where offer_id = p_offer_id;
682 --
683 cursor csr_assignment_ovn is
684 select object_version_number
685 from per_all_assignments_f
686 where assignment_id = l_offer_assignment_id;
687 --
688 Begin
689 hr_utility.set_location('Entering:'||l_proc,10);
690 --
691 open csr_offer_details;
692 fetch csr_offer_details into l_offer_assignment_id
693 ,l_object_version_number
694 ,l_updated_offer_status;
695 if csr_offer_details%notfound
696 then
697 --
698 close csr_offer_details;
699 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
700 fnd_message.raise_error;
701 --
702 end if;
703 close csr_offer_details;
704 --
705 hr_utility.set_location(l_proc,20);
706 --
707 -- Call upd with new offer_status value, new
708 -- latest_offer value.
709 --
710 irc_iof_upd.upd
711 ( p_effective_date => p_effective_date
712 ,p_offer_id => p_offer_id
713 ,p_object_version_number => l_object_version_number
714 ,p_offer_version => l_offer_version
715 ,p_latest_offer => p_latest_offer
716 ,p_offer_status => p_offer_status
717 );
718 --
719 -- Insert a new Offer History record with the change_reason only if
720 -- the Status has changed.
721 --
722 if p_offer_status <> hr_api.g_varchar2
723 and p_offer_status <> l_updated_offer_status
724 then
725 --
726 hr_utility.set_location(l_proc, 50);
727 --
728 irc_offer_status_history_api.create_offer_status_history
729 ( p_validate => p_validate
730 ,p_effective_date => p_effective_date
731 ,p_offer_status_history_id => l_offer_status_history_id
732 ,p_offer_id => p_offer_id
733 ,p_status_change_date => p_status_change_date
734 ,p_offer_status => p_offer_status
735 ,p_change_reason => p_change_reason
736 ,p_object_version_number => l_osh_object_version_number
737 );
738 --
739 end if;
740 --
741 -- If the offer has been CLOSED, and if the offer was previously not
742 -- closed, end date the offer assignment.
743 --
744 if p_offer_status = 'CLOSED'
745 and p_offer_status <> l_updated_offer_status
746 then
747 --
748 hr_utility.set_location(l_proc, 60);
749 --
750 open csr_assignment_ovn;
751 fetch csr_assignment_ovn into l_asg_object_version_number;
752 if csr_assignment_ovn%notfound
753 then
754 --
755 hr_utility.set_location(l_proc, 65);
756 --
757 close csr_assignment_ovn;
758 fnd_message.set_name('PER','HR_289541_PJU_INV_ASG_ID');
759 fnd_message.raise_error;
760 --
761 end if;
762 close csr_assignment_ovn;
763 --
764 per_asg_del.del
765 (
766 p_assignment_id => l_offer_assignment_id
767 ,p_effective_start_date => l_effective_start_date
768 ,p_effective_end_date => l_effective_end_date
769 ,p_business_group_id => l_business_group_id
770 ,p_object_version_number => l_asg_object_version_number
771 ,p_effective_date => p_effective_date
772 ,p_validation_start_date => l_validation_start_date
773 ,p_validation_end_date => l_validation_end_date
774 ,p_datetrack_mode => hr_api.g_delete
775 ,p_validate => p_validate
776 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
777 );
778 --
779 end if;
780 --
781 hr_utility.set_location(' Leaving:'||l_proc, 70);
782 --
783 exception
784 when others then
785 hr_utility.set_location(' Leaving:'||l_proc, 80);
786 raise;
787 end update_latest_offer_flag;
788 --
789 -- ----------------------------------------------------------------------------
790 -- |----------------------< copy_offer_asg_to_appl_asg >----------------------|
791 -- ----------------------------------------------------------------------------
792 -- This procedure copies the offer assignment details into the applicant
793 -- assignment record once the applicant accepts the offer
794 -- ----------------------------------------------------------------------------
795 --
796 procedure copy_offer_asg_to_appl_asg
797 (P_OFFER_ID IN NUMBER
798 ,P_EFFECTIVE_DATE IN DATE
799 ,P_VALIDATE IN BOOLEAN default false
800 )
801 Is
802 --
803 l_proc varchar2(72) := g_package||'copy_offer_asg_to_appl_asg';
804 l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
805 l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
806 --
807 -- Out and In Out variables
808 --
809 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
810 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
811 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
812 l_comment_id per_all_assignments_f.comment_id%TYPE;
813 l_object_version_number per_all_assignments_f.object_version_number%TYPE;
814 l_payroll_id_updated boolean;
815 l_other_manager_warning boolean;
816 l_hourly_salaried_warning boolean;
817 l_no_managers_warning boolean;
818 l_org_now_no_manager_warning boolean;
819 l_validation_start_date date;
820 l_validation_end_date date;
821 l_effective_date date;
822 l_datetrack_mode varchar2(30) := hr_api.g_update;
823 --
824 -- Define cursors
825 --
826 cursor csr_assignment_ids is
827 select applicant_assignment_id
828 ,offer_assignment_id
829 from irc_offers
830 where offer_id = p_offer_id;
831 --
832 cursor csr_appl_asg_effective_date is
833 select effective_start_date
834 ,object_version_number
835 from per_all_assignments_f
836 where assignment_id = l_applicant_assignment_id
837 and p_effective_date
838 between effective_start_date
839 and effective_end_date;
840 --
841 cursor csr_assignment_record is
842 select
843 business_group_id
844 ,recruiter_id
845 ,grade_id
846 ,position_id
847 ,job_id
848 ,assignment_status_type_id
849 ,payroll_id
850 ,location_id
851 ,person_referred_by_id
852 ,supervisor_id
853 ,special_ceiling_step_id
854 ,person_id
855 ,recruitment_activity_id
856 ,source_organization_id
857 ,organization_id
858 ,people_group_id
859 ,soft_coding_keyflex_id
860 ,vacancy_id
861 ,pay_basis_id
862 ,assignment_sequence
863 ,assignment_type
864 ,primary_flag
865 ,application_id
866 ,assignment_number
867 ,change_reason
868 ,comment_id
869 ,date_probation_end
870 ,default_code_comb_id
871 ,employment_category
872 ,frequency
873 ,internal_address_line
874 ,manager_flag
875 ,normal_hours
876 ,perf_review_period
877 ,perf_review_period_frequency
878 ,period_of_service_id
879 ,probation_period
880 ,probation_unit
881 ,sal_review_period
882 ,sal_review_period_frequency
883 ,set_of_books_id
884 ,source_type
885 ,time_normal_finish
886 ,time_normal_start
887 ,request_id
888 ,program_application_id
889 ,program_id
890 ,program_update_date
891 ,ass_attribute_category
892 ,ass_attribute1
893 ,ass_attribute2
894 ,ass_attribute3
895 ,ass_attribute4
896 ,ass_attribute5
897 ,ass_attribute6
898 ,ass_attribute7
899 ,ass_attribute8
900 ,ass_attribute9
901 ,ass_attribute10
902 ,ass_attribute11
903 ,ass_attribute12
904 ,ass_attribute13
905 ,ass_attribute14
906 ,ass_attribute15
907 ,ass_attribute16
908 ,ass_attribute17
909 ,ass_attribute18
910 ,ass_attribute19
911 ,ass_attribute20
912 ,ass_attribute21
913 ,ass_attribute22
914 ,ass_attribute23
915 ,ass_attribute24
916 ,ass_attribute25
917 ,ass_attribute26
918 ,ass_attribute27
919 ,ass_attribute28
920 ,ass_attribute29
921 ,ass_attribute30
922 ,title
923 ,object_version_number
924 ,bargaining_unit_code
925 ,labour_union_member_flag
926 ,hourly_salaried_code
927 ,contract_id
928 ,collective_agreement_id
929 ,cagr_id_flex_num
930 ,cagr_grade_def_id
931 ,establishment_id
932 ,notice_period
933 ,notice_period_uom
934 ,employee_category
935 ,work_at_home
936 ,job_post_source_name
937 ,posting_content_id
938 ,applicant_rank
939 ,period_of_placement_date_start
940 ,vendor_id
941 ,vendor_employee_number
942 ,vendor_assignment_number
943 ,assignment_category
944 ,project_title
945 ,grade_ladder_pgm_id
946 ,supervisor_assignment_id
947 ,vendor_site_id
948 ,po_header_id
949 ,po_line_id
950 ,projected_assignment_end
951 from per_all_assignments_f
952 where assignment_id = l_offer_assignment_id
953 and p_effective_date
954 between effective_start_date
955 and effective_end_date;
956
957 l_offer_assignment csr_assignment_record%ROWTYPE;
958 --
959 Begin
960 hr_utility.set_location('Entering:'||l_proc,10);
961 --
962 -- Issue a savepoint
963 --
964 savepoint COPY_OFFER_ASG_TO_APPL_ASG;
965 --
966 open csr_assignment_ids;
967 fetch csr_assignment_ids into l_applicant_assignment_id
968 ,l_offer_assignment_id;
969 --
970 if csr_assignment_ids%notfound
971 then
972 --
973 close csr_assignment_ids;
974 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
975 fnd_message.raise_error;
976 --
977 end if;
978 close csr_assignment_ids;
979 --
980 open csr_assignment_record;
981 fetch csr_assignment_record into l_offer_assignment;
982 --
983 if csr_assignment_record%notfound
984 then
985 --
986 close csr_assignment_record;
987 fnd_message.set_name('PER','HR_289541_PJU_INV_ASG_ID');
988 fnd_message.raise_error;
989 --
990 end if;
991 close csr_assignment_record;
992 --
993 hr_utility.set_location(l_proc,20);
994 --
995 --
996 -- Truncate the time portion from all IN date parameters
997 --
998 l_effective_date := trunc(p_effective_date);
999 --
1000 -- Find out the datetrack mode to be employed
1001 --
1002 open csr_appl_asg_effective_date;
1003 fetch csr_appl_asg_effective_date into l_effective_start_date
1004 ,l_object_version_number;
1005 --
1006 if csr_appl_asg_effective_date%notfound
1007 then
1008 --
1009 close csr_appl_asg_effective_date;
1010 fnd_message.set_name('PER','HR_289541_PJU_INV_ASG_ID');
1011 fnd_message.raise_error;
1012 --
1013 end if;
1014 close csr_appl_asg_effective_date;
1015 --
1016 if ( l_effective_start_date = trunc(sysdate) )
1017 then
1018 --
1019 hr_utility.set_location(l_proc,30);
1020 --
1021 -- Since the applicant assignment record is being modified multiple
1022 -- times on the same day, the datetrack mode should be correction.
1023 --
1024 l_datetrack_mode := hr_api.g_correction;
1025 --
1026 end if;
1027 --
1028 hr_utility.set_location(l_proc,40);
1029 --
1030 -- Copy the Offer Assignment details into Applicant Assignment record
1031 --
1032 per_asg_upd.upd
1033 ( p_assignment_id => l_applicant_assignment_id
1034 ,p_effective_start_date => l_effective_start_date
1035 ,p_effective_end_date => l_effective_end_date
1036 ,p_business_group_id => l_business_group_id
1037 ,p_recruiter_id => l_offer_assignment.recruiter_id
1038 ,p_grade_id => l_offer_assignment.grade_id
1039 ,p_position_id => l_offer_assignment.position_id
1040 ,p_job_id => l_offer_assignment.job_id
1041 ,p_assignment_status_type_id => l_offer_assignment.assignment_status_type_id
1042 ,p_payroll_id => l_offer_assignment.payroll_id
1043 ,p_location_id => l_offer_assignment.location_id
1044 ,p_person_referred_by_id => l_offer_assignment.person_referred_by_id
1045 ,p_supervisor_id => l_offer_assignment.supervisor_id
1046 ,p_special_ceiling_step_id => l_offer_assignment.special_ceiling_step_id
1047 ,p_recruitment_activity_id => l_offer_assignment.recruitment_activity_id
1048 ,p_source_organization_id => l_offer_assignment.source_organization_id
1049 ,p_organization_id => l_offer_assignment.organization_id
1050 ,p_people_group_id => l_offer_assignment.people_group_id
1051 ,p_soft_coding_keyflex_id => l_offer_assignment.soft_coding_keyflex_id
1052 ,p_vacancy_id => l_offer_assignment.vacancy_id
1053 ,p_pay_basis_id => l_offer_assignment.pay_basis_id
1054 -- Do not modify the Assignment Type and Primary Flag
1055 ,p_application_id => l_offer_assignment.application_id
1056 ,p_assignment_number => l_offer_assignment.assignment_number
1057 ,p_change_reason => l_offer_assignment.change_reason
1058 ,p_comment_id => l_comment_id
1059 ,p_date_probation_end => l_offer_assignment.date_probation_end
1060 ,p_default_code_comb_id => l_offer_assignment.default_code_comb_id
1061 ,p_employment_category => l_offer_assignment.employment_category
1062 ,p_frequency => l_offer_assignment.frequency
1063 ,p_internal_address_line => l_offer_assignment.internal_address_line
1064 ,p_manager_flag => l_offer_assignment.manager_flag
1065 ,p_normal_hours => l_offer_assignment.normal_hours
1066 ,p_perf_review_period => l_offer_assignment.perf_review_period
1067 ,p_perf_review_period_frequency => l_offer_assignment.perf_review_period_frequency
1068 ,p_period_of_service_id => l_offer_assignment.period_of_service_id
1069 ,p_probation_period => l_offer_assignment.probation_period
1070 ,p_probation_unit => l_offer_assignment.probation_unit
1071 ,p_sal_review_period => l_offer_assignment.sal_review_period
1072 ,p_sal_review_period_frequency => l_offer_assignment.sal_review_period_frequency
1073 ,p_set_of_books_id => l_offer_assignment.set_of_books_id
1074 ,p_source_type => l_offer_assignment.source_type
1075 ,p_time_normal_finish => l_offer_assignment.time_normal_finish
1076 ,p_time_normal_start => l_offer_assignment.time_normal_start
1077 ,p_request_id => l_offer_assignment.request_id
1078 ,p_program_application_id => l_offer_assignment.program_application_id
1079 ,p_program_id => l_offer_assignment.program_id
1080 ,p_program_update_date => l_offer_assignment.program_update_date
1081 ,p_ass_attribute_category => l_offer_assignment.ass_attribute_category
1082 ,p_ass_attribute1 => l_offer_assignment.ass_attribute1
1083 ,p_ass_attribute2 => l_offer_assignment.ass_attribute2
1084 ,p_ass_attribute3 => l_offer_assignment.ass_attribute3
1085 ,p_ass_attribute4 => l_offer_assignment.ass_attribute4
1086 ,p_ass_attribute5 => l_offer_assignment.ass_attribute5
1087 ,p_ass_attribute6 => l_offer_assignment.ass_attribute6
1088 ,p_ass_attribute7 => l_offer_assignment.ass_attribute7
1089 ,p_ass_attribute8 => l_offer_assignment.ass_attribute8
1090 ,p_ass_attribute9 => l_offer_assignment.ass_attribute9
1091 ,p_ass_attribute10 => l_offer_assignment.ass_attribute10
1092 ,p_ass_attribute11 => l_offer_assignment.ass_attribute11
1093 ,p_ass_attribute12 => l_offer_assignment.ass_attribute12
1094 ,p_ass_attribute13 => l_offer_assignment.ass_attribute13
1095 ,p_ass_attribute14 => l_offer_assignment.ass_attribute14
1096 ,p_ass_attribute15 => l_offer_assignment.ass_attribute15
1097 ,p_ass_attribute16 => l_offer_assignment.ass_attribute16
1098 ,p_ass_attribute17 => l_offer_assignment.ass_attribute17
1099 ,p_ass_attribute18 => l_offer_assignment.ass_attribute18
1100 ,p_ass_attribute19 => l_offer_assignment.ass_attribute19
1101 ,p_ass_attribute20 => l_offer_assignment.ass_attribute20
1102 ,p_ass_attribute21 => l_offer_assignment.ass_attribute21
1103 ,p_ass_attribute22 => l_offer_assignment.ass_attribute22
1104 ,p_ass_attribute23 => l_offer_assignment.ass_attribute23
1105 ,p_ass_attribute24 => l_offer_assignment.ass_attribute24
1106 ,p_ass_attribute25 => l_offer_assignment.ass_attribute25
1107 ,p_ass_attribute26 => l_offer_assignment.ass_attribute26
1108 ,p_ass_attribute27 => l_offer_assignment.ass_attribute27
1109 ,p_ass_attribute28 => l_offer_assignment.ass_attribute28
1110 ,p_ass_attribute29 => l_offer_assignment.ass_attribute29
1111 ,p_ass_attribute30 => l_offer_assignment.ass_attribute30
1112 ,p_title => l_offer_assignment.title
1113 ,p_object_version_number => l_object_version_number
1114 ,p_bargaining_unit_code => l_offer_assignment.bargaining_unit_code
1115 ,p_labour_union_member_flag => l_offer_assignment.labour_union_member_flag
1116 ,p_hourly_salaried_code => l_offer_assignment.hourly_salaried_code
1117 ,p_contract_id => l_offer_assignment.contract_id
1118 ,p_collective_agreement_id => l_offer_assignment.collective_agreement_id
1119 ,p_cagr_id_flex_num => l_offer_assignment.cagr_id_flex_num
1120 ,p_cagr_grade_def_id => l_offer_assignment.cagr_grade_def_id
1121 ,p_establishment_id => l_offer_assignment.establishment_id
1122 ,p_notice_period => l_offer_assignment.notice_period
1123 ,p_notice_period_uom => l_offer_assignment.notice_period_uom
1124 ,p_employee_category => l_offer_assignment.employee_category
1125 ,p_work_at_home => l_offer_assignment.work_at_home
1126 ,p_job_post_source_name => l_offer_assignment.job_post_source_name
1127 ,p_posting_content_id => l_offer_assignment.posting_content_id
1128 ,p_applicant_rank => l_offer_assignment.applicant_rank
1129 ,p_placement_date_start => l_offer_assignment.period_of_placement_date_start
1130 ,p_vendor_id => l_offer_assignment.vendor_id
1131 ,p_vendor_employee_number => l_offer_assignment.vendor_employee_number
1132 ,p_vendor_assignment_number => l_offer_assignment.vendor_assignment_number
1133 ,p_assignment_category => l_offer_assignment.assignment_category
1134 ,p_project_title => l_offer_assignment.project_title
1135 ,p_grade_ladder_pgm_id => l_offer_assignment.grade_ladder_pgm_id
1136 ,p_supervisor_assignment_id => l_offer_assignment.supervisor_assignment_id
1137 ,p_vendor_site_id => l_offer_assignment.vendor_site_id
1138 ,p_po_header_id => l_offer_assignment.po_header_id
1139 ,p_po_line_id => l_offer_assignment.po_line_id
1140 ,p_projected_assignment_end => l_offer_assignment.projected_assignment_end
1141 ,p_payroll_id_updated => l_payroll_id_updated
1142 ,p_other_manager_warning => l_other_manager_warning
1143 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1144 ,p_no_managers_warning => l_no_managers_warning
1145 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1146 ,p_validation_start_date => l_validation_start_date
1147 ,p_validation_end_date => l_validation_end_date
1148 ,p_effective_date => l_effective_date
1149 ,p_datetrack_mode => l_datetrack_mode
1150 ,p_validate => p_validate
1151 );
1152 --
1153 hr_utility.set_location(l_proc, 50);
1154 --
1155 --
1156 -- When in validation only mode raise the Validate_Enabled exception
1157 --
1158 if p_validate then
1159 raise hr_api.validate_enabled;
1160 end if;
1161 --
1162 hr_utility.set_location(' Leaving:'||l_proc, 60);
1163 --
1164 exception
1165 when hr_api.validate_enabled then
1166 --
1167 -- As the Validate_Enabled exception has been raised
1168 -- we must rollback to the savepoint
1169 --
1170 rollback to COPY_OFFER_ASG_TO_APPL_ASG;
1171 --
1172 hr_utility.set_location(' Leaving:'||l_proc, 70);
1173 when others then
1174 --
1175 -- A validation or unexpected error has occured
1176 --
1177 rollback to COPY_OFFER_ASG_TO_APPL_ASG;
1178 --
1179 hr_utility.set_location(' Leaving:'||l_proc, 80);
1180 raise;
1181 end copy_offer_asg_to_appl_asg;
1182 --
1183 -- ----------------------------------------------------------------------------
1184 -- |----------------------< copy_offer_pay_to_appl_pay >----------------------|
1185 -- ----------------------------------------------------------------------------
1186 -- This procedure copies the offer pay proposal record details into the applicant
1187 -- pay proposal record once the applicant accepts the offer
1188 -- ----------------------------------------------------------------------------
1189 --
1190 procedure copy_offer_pay_to_appl_pay
1191 (P_VALIDATE IN BOOLEAN default false
1192 ,P_OFFER_ID IN NUMBER
1193 ,P_EFFECTIVE_DATE IN DATE
1194 )
1195 Is
1196 --
1197 l_proc varchar2(72) := g_package||'copy_offer_pay_to_appl_pay';
1198 --
1199 -- Out and In Out variables
1200 --
1201 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
1202 l_pay_proposal_ovn per_pay_proposals.object_version_number%TYPE;
1203 l_inv_next_sal_date_warning boolean;
1204 l_proposed_salary_warning boolean;
1205 l_approved_warning boolean;
1206 l_payroll_warning boolean;
1207 l_salary_warning boolean;
1208
1209 l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
1210 l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
1211 --
1212 -- Define cursors
1213 --
1214 cursor csr_assignment_ids is
1215 select applicant_assignment_id
1216 ,offer_assignment_id
1217 from irc_offers
1218 where offer_id = p_offer_id;
1219 --
1220 cursor csr_appl_pay_proposal is
1221 select pay_proposal_id
1222 ,object_version_number
1223 from per_pay_proposals
1224 where assignment_id = l_applicant_assignment_id
1225 and approved = 'N';
1226 --
1227 cursor csr_pay_proposal_record(p_assignment_id per_pay_proposals.assignment_id%TYPE) is
1228 select
1229 pay_proposal_id
1230 ,assignment_id
1231 ,event_id
1232 ,business_group_id
1233 ,change_date
1234 ,comments
1235 ,last_change_date
1236 ,next_perf_review_date
1237 ,next_sal_review_date
1238 ,performance_rating
1239 ,proposal_reason
1240 ,proposed_salary
1241 ,review_date
1242 ,attribute_category
1243 ,attribute1
1244 ,attribute2
1245 ,attribute3
1246 ,attribute4
1247 ,attribute5
1248 ,attribute6
1249 ,attribute7
1250 ,attribute8
1251 ,attribute9
1252 ,attribute10
1253 ,attribute11
1254 ,attribute12
1255 ,attribute13
1256 ,attribute14
1257 ,attribute15
1258 ,attribute16
1259 ,attribute17
1260 ,attribute18
1261 ,attribute19
1262 ,attribute20
1263 ,last_update_date
1264 ,last_updated_by
1265 ,last_update_login
1266 ,created_by
1267 ,creation_date
1268 ,object_version_number
1269 ,approved
1270 ,multiple_components
1271 ,forced_ranking
1272 ,performance_review_id
1273 ,proposed_salary_n
1274 from per_pay_proposals
1275 where assignment_id = p_assignment_id
1276 and approved = 'N';
1277
1278 l_pay_proposal_record csr_pay_proposal_record%ROWTYPE;
1279 --
1280 Begin
1281 hr_utility.set_location('Entering:'||l_proc,10);
1282 --
1283 -- Issue a savepoint
1284 --
1285 savepoint COPY_OFFER_PAY_TO_APPL_PAY;
1286 --
1287 open csr_assignment_ids;
1288 fetch csr_assignment_ids into l_applicant_assignment_id
1289 ,l_offer_assignment_id;
1290 if csr_assignment_ids%notfound
1291 then
1292 --
1293 close csr_assignment_ids;
1294 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
1295 fnd_message.raise_error;
1296 --
1297 end if;
1298 close csr_assignment_ids;
1299
1300 --
1301 open csr_appl_pay_proposal;
1302 fetch csr_appl_pay_proposal into l_pay_proposal_id
1303 ,l_pay_proposal_ovn;
1304 --
1305 if csr_appl_pay_proposal%notfound
1306 then
1307 --
1308 hr_utility.set_location(l_proc,30);
1309 --
1310 -- Pay Proposal does not exist for this applicant.
1311 -- Hence create a new pay proposal record.
1312 --
1313 l_pay_proposal_id := null;
1314 l_pay_proposal_ovn := null;
1315 --
1316 end if;
1317 close csr_appl_pay_proposal;
1318 --
1319 open csr_pay_proposal_record(l_offer_assignment_id);
1320 fetch csr_pay_proposal_record into l_pay_proposal_record;
1321 --
1322 if csr_pay_proposal_record%found
1323 then
1324 --
1325 hr_utility.set_location(l_proc,40);
1326 --
1327 -- We need to copy the pay proposal details to the record corresponding
1328 -- to applicant assignment only if the pay proposal record exists for
1329 -- offer assignment.
1330 --
1331 --
1332 -- Call the cre_or_upd_salary_proposal procedure with the corresponding values
1333 -- so that it decides whether a new record needs to be created or an existing record
1334 -- needs to be updated.
1335 --
1336 hr_maintain_proposal_api.cre_or_upd_salary_proposal
1337 (
1338 p_validate => p_validate
1339 ,p_pay_proposal_id => l_pay_proposal_id
1340 ,p_object_version_number => l_pay_proposal_ovn
1341 ,p_business_group_id => l_pay_proposal_record.business_group_id
1342 ,p_assignment_id => l_applicant_assignment_id
1343 ,p_change_date => p_effective_date
1344 ,p_comments => l_pay_proposal_record.comments
1345 ,p_next_sal_review_date => l_pay_proposal_record.next_sal_review_date
1346 ,p_proposal_reason => l_pay_proposal_record.proposal_reason
1347 ,p_proposed_salary_n => l_pay_proposal_record.proposed_salary_n
1348 ,p_forced_ranking => l_pay_proposal_record.forced_ranking
1349 ,p_performance_review_id => l_pay_proposal_record.performance_review_id
1350 ,p_attribute_category => l_pay_proposal_record.attribute_category
1351 ,p_attribute1 => l_pay_proposal_record.attribute1
1352 ,p_attribute2 => l_pay_proposal_record.attribute2
1353 ,p_attribute3 => l_pay_proposal_record.attribute3
1354 ,p_attribute4 => l_pay_proposal_record.attribute4
1355 ,p_attribute5 => l_pay_proposal_record.attribute5
1356 ,p_attribute6 => l_pay_proposal_record.attribute6
1357 ,p_attribute7 => l_pay_proposal_record.attribute7
1358 ,p_attribute8 => l_pay_proposal_record.attribute8
1359 ,p_attribute9 => l_pay_proposal_record.attribute9
1360 ,p_attribute10 => l_pay_proposal_record.attribute10
1361 ,p_attribute11 => l_pay_proposal_record.attribute11
1362 ,p_attribute12 => l_pay_proposal_record.attribute12
1363 ,p_attribute13 => l_pay_proposal_record.attribute13
1364 ,p_attribute14 => l_pay_proposal_record.attribute14
1365 ,p_attribute15 => l_pay_proposal_record.attribute15
1366 ,p_attribute16 => l_pay_proposal_record.attribute16
1367 ,p_attribute17 => l_pay_proposal_record.attribute17
1368 ,p_attribute18 => l_pay_proposal_record.attribute18
1369 ,p_attribute19 => l_pay_proposal_record.attribute19
1370 ,p_attribute20 => l_pay_proposal_record.attribute20
1371 ,p_multiple_components => l_pay_proposal_record.multiple_components
1372 ,p_approved => l_pay_proposal_record.approved
1373 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1374 ,p_proposed_salary_warning => l_proposed_salary_warning
1375 ,p_approved_warning => l_approved_warning
1376 ,p_payroll_warning => l_payroll_warning
1377 );
1378 --
1379 close csr_pay_proposal_record;
1380 --
1381 else
1382 --
1383 hr_utility.set_location(l_proc,45);
1384 --
1385 --
1386 close csr_pay_proposal_record;
1387 --
1388 open csr_pay_proposal_record(l_applicant_assignment_id);
1389 fetch csr_pay_proposal_record into l_pay_proposal_record;
1390 if csr_pay_proposal_record%found then
1391 --
1392 hr_utility.set_location(l_proc,46);
1393 --
1394 hr_maintain_proposal_api.delete_salary_proposal
1395 (p_validate => p_validate
1396 ,p_pay_proposal_id => l_pay_proposal_record.pay_proposal_id
1397 ,p_business_group_id => l_pay_proposal_record.business_group_id
1398 ,p_object_version_number => l_pay_proposal_record.object_version_number
1399 ,p_salary_warning => l_salary_warning
1400 );
1401 --
1402 end if;
1403 --
1404 close csr_pay_proposal_record;
1405 --
1406 end if;
1407 --
1408 hr_utility.set_location(l_proc, 50);
1409 --
1410 -- When in validation only mode raise the Validate_Enabled exception
1411 --
1412 if p_validate then
1413 raise hr_api.validate_enabled;
1414 end if;
1415 --
1416 hr_utility.set_location(' Leaving:'||l_proc, 60);
1417 --
1418 exception
1419 when hr_api.validate_enabled then
1420 --
1421 -- As the Validate_Enabled exception has been raised
1422 -- we must rollback to the savepoint
1423 --
1424 rollback to COPY_OFFER_PAY_TO_APPL_PAY;
1425 --
1426 hr_utility.set_location(' Leaving:'||l_proc, 70);
1427 when others then
1428 --
1429 -- A validation or unexpected error has occured
1430 --
1431 rollback to COPY_OFFER_PAY_TO_APPL_PAY;
1432 --
1433 hr_utility.set_location(' Leaving:'||l_proc, 80);
1434 raise;
1435 end copy_offer_pay_to_appl_pay;
1436 --
1437 -- ----------------------------------------------------------------------------
1438 -- |-------------------< other_extended_offers_count >------------------------|
1439 -- This functions returns the number of offers that have been already Extended
1440 -- or Accepted by the candidate
1441 -- ----------------------------------------------------------------------------
1442 --
1443 PROCEDURE other_extended_offers_count
1444 ( p_applicant_assignment_id IN NUMBER default null
1445 ,p_effective_date IN DATE
1446 ,p_person_id IN NUMBER default null
1447 ,p_other_extended_offer_count OUT nocopy NUMBER
1448 ) IS
1449 --
1450 l_proc VARCHAR2(72) := g_package||'other_extended_offers_count';
1451 l_other_extended_offer_count number := 0;
1452 l_person_id per_all_assignments_f.person_id%TYPE := p_person_id;
1453 --
1454 CURSOR csr_person_id is
1455 SELECT person_id
1456 FROM per_all_assignments_f
1457 WHERE assignment_id = p_applicant_assignment_id
1458 AND p_effective_date
1459 BETWEEN effective_start_date
1460 AND effective_end_date;
1461 --
1462 CURSOR csr_other_extended_offers IS
1463 SELECT count(otheroffer.offer_id)
1464 FROM IRC_OFFERS otheroffer
1465 ,PER_ALL_ASSIGNMENTS_F otherasg
1466 ,PER_ALL_PEOPLE_F per
1467 ,PER_ALL_PEOPLE_F linkper
1468 ,IRC_OFFER_STATUS_HISTORY otherhistory
1469 WHERE (
1470 -- Other Extended Offers
1471 otheroffer.offer_status = 'EXTENDED'
1472 OR
1473 ( -- Other Closed and Accepted Offers
1474 otheroffer.offer_status = 'CLOSED'
1475 AND otherhistory.change_reason = 'APL_ACCEPTED'
1476 )
1477 )
1478 AND otherasg.effective_start_date = (select max(effective_start_date)
1479 from per_assignments_f asg2
1480 where otherasg.assignment_id=asg2.assignment_id
1481 and asg2.effective_start_date <= trunc(sysdate)
1482 )
1483 AND p_effective_date BETWEEN trunc(per.effective_start_date) and trunc(nvl(per.effective_end_date,p_effective_date))
1484 AND p_effective_date BETWEEN trunc(linkper.effective_start_date) and trunc(nvl(linkper.effective_end_date,p_effective_date))
1485 AND otheroffer.offer_assignment_id = otherasg.assignment_id
1486 AND otherasg.person_id = linkper.person_id
1487 AND per.person_id = l_person_id
1488 AND linkper.party_id = per.party_id
1489 AND otheroffer.latest_offer = 'Y'
1490 AND otheroffer.applicant_assignment_id <> nvl(p_applicant_assignment_id, -1)
1491 AND otheroffer.applicant_assignment_id <> nvl((select tgt_apl_asg_id from per_vac_linked_assignments where src_apl_asg_id=p_applicant_assignment_id and rownum<2), -1)
1492 AND decode(hr_general.get_xbg_profile,'Y', otherasg.business_group_id , hr_general.get_business_group_id) = otherasg.business_group_id
1493 AND otheroffer.offer_id = otherhistory.offer_id
1494 AND NOT EXISTS
1495 (SELECT 1
1496 FROM irc_offer_status_history iosh1
1497 WHERE iosh1.offer_id = otherhistory.offer_id
1498 AND iosh1.status_change_date > otherhistory.status_change_date
1499 )
1500 AND otherhistory.offer_status_history_id =
1501 (SELECT MAX(iosh2.offer_status_history_id)
1502 FROM irc_offer_status_history iosh2
1503 WHERE iosh2.offer_id = otherhistory.offer_id
1504 AND iosh2.status_change_date = otherhistory.status_change_date
1505 );
1506 --
1507 BEGIN
1508 --
1509 if l_person_id is null
1510 then
1511 --
1512 open csr_person_id;
1513 fetch csr_person_id into l_person_id;
1514 if csr_person_id%notfound
1515 then
1516 --
1517 close csr_person_id;
1518 --
1519 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
1520 fnd_message.raise_error;
1521 --
1522 end if;
1523 close csr_person_id;
1524 --
1525 end if;
1526 --
1527 open csr_other_extended_offers;
1528 fetch csr_other_extended_offers into l_other_extended_offer_count;
1529 if csr_other_extended_offers%NOTFOUND
1530 then
1531 --
1532 close csr_other_extended_offers;
1533 l_other_extended_offer_count := 0;
1534 --
1535 else
1536 --
1537 close csr_other_extended_offers;
1538 --
1539 end if;
1540 --
1541 p_other_extended_offer_count := l_other_extended_offer_count;
1542 --
1543 END other_extended_offers_count;
1544 --
1545 --
1546 --
1547 -- ----------------------------------------------------------------------------
1548 -- |-----------------------< close_notifications >------------------------|
1549 -- ----------------------------------------------------------------------------
1550 --
1551 procedure close_notifications
1552 ( P_APPLICANT_ASSIGNMENT_ID IN number
1553 ,P_OFFER_ID IN number
1554 ) Is
1555 --
1556 l_proc varchar2(72) := g_package||'close_notifications';
1557 l_transaction_id hr_api_transactions.transaction_id%TYPE;
1558 l_item_type hr_api_transactions.item_type%TYPE;
1559 l_item_key hr_api_transactions.item_key%TYPE;
1560 --
1561 cursor csr_get_txn_details is
1562 select transaction_id
1563 ,item_type
1564 ,item_key
1565 from hr_api_transactions
1566 where transaction_ref_table='IRC_OFFERS'
1567 and transaction_ref_id = p_offer_id;
1568 --
1569 cursor csr_get_sfl_txn_details is
1570 select hat.transaction_id
1571 ,wn.MESSAGE_TYPE
1572 ,wn.item_key
1573 from hr_api_transactions hat,
1574 wf_notifications wn
1575 where hat.transaction_ref_table='IRC_OFFERS'
1576 and hat.STATUS='S'
1577 and wn.MESSAGE_TYPE='HRSFL'
1578 and to_char(hat.transaction_id)=wn.user_key
1579 and hat.ASSIGNMENT_ID = p_applicant_assignment_id;
1580 --
1581 begin
1582 --
1583 --
1584 hr_utility.set_location(' Entering:'||l_proc,10);
1585 --
1586 savepoint CLOSE_NOTIFICATIONS;
1587 --
1588 -- Close if any SFL notifications exists
1589 open csr_get_sfl_txn_details;
1590 fetch csr_get_sfl_txn_details into l_transaction_id,l_item_type,l_item_key;
1591 if csr_get_sfl_txn_details%found then
1592 --
1593 hr_utility.set_location(' SFL Transaction Found '||l_proc,20);
1594 hr_transaction_api.rollback_transaction(
1595 p_transaction_id => l_transaction_id);
1596 --
1597 hr_utility.set_location(' Rolled back SFL Transaction '||l_proc,30);
1598 --
1599 if l_item_key is not null then
1600 wf_engine.abortprocess(itemtype => l_item_type
1601 ,itemkey => l_item_key
1602 ,process =>null
1603 ,result => wf_engine.eng_force
1604 ,verify_lock=> true
1605 ,cascade=> true);
1606 end if;
1607 --
1608 hr_utility.set_location(' Cancelled SFL notification '||l_proc,40);
1609 --
1610 end if;
1611 close csr_get_sfl_txn_details;
1612 --
1613 -- Close any open notifications
1614 open csr_get_txn_details;
1615 fetch csr_get_txn_details into l_transaction_id,l_item_type,l_item_key;
1616 if csr_get_txn_details%found then
1617 --
1618 hr_utility.set_location(' API Transaction Found '||l_proc,50);
1619 hr_transaction_api.rollback_transaction(
1620 p_transaction_id => l_transaction_id);
1621 --
1622 hr_utility.set_location(' Rolled back API Transaction '||l_proc,60);
1623 --
1624 if l_item_key is not null then
1625 wf_engine.abortprocess(itemtype => l_item_type
1626 ,itemkey => l_item_key
1627 ,process =>null
1628 ,result => wf_engine.eng_force
1629 ,verify_lock=> true
1630 ,cascade=> true);
1631 end if;
1632 --
1633 hr_utility.set_location(' Cancelled Open notification '||l_proc,70);
1634 --
1635 end if;
1636 close csr_get_txn_details;
1637 --
1638 --
1639 --
1640 exception
1641 when others then
1642 rollback to CLOSE_NOTIFICATIONS;
1643 hr_utility.set_location(' Leaving: '||l_proc, 80);
1644 raise;
1645 end close_notifications;
1646 --
1647 -- ----------------------------------------------------------------------------
1648 -- |---------------------------< create_offer >-------------------------------|
1649 -- ----------------------------------------------------------------------------
1650 --
1651 procedure create_offer
1652 ( P_VALIDATE IN boolean default false
1653 , P_EFFECTIVE_DATE IN date default null
1654 , P_OFFER_STATUS IN VARCHAR2
1655 , P_DISCRETIONARY_JOB_TITLE IN VARCHAR2 default null
1656 , P_OFFER_EXTENDED_METHOD IN VARCHAR2 default null
1657 , P_RESPONDENT_ID IN NUMBER default null
1658 , P_EXPIRY_DATE IN DATE default null
1659 , P_PROPOSED_START_DATE IN DATE default null
1660 , P_OFFER_LETTER_TRACKING_CODE IN VARCHAR2 default null
1661 , P_OFFER_POSTAL_SERVICE IN VARCHAR2 default null
1662 , P_OFFER_SHIPPING_DATE IN DATE default null
1663 , P_APPLICANT_ASSIGNMENT_ID IN NUMBER
1664 , P_OFFER_ASSIGNMENT_ID IN NUMBER
1665 , P_ADDRESS_ID IN NUMBER default null
1666 , P_TEMPLATE_ID IN NUMBER default null
1667 , P_OFFER_LETTER_FILE_TYPE IN VARCHAR2 default null
1668 , P_OFFER_LETTER_FILE_NAME IN VARCHAR2 default null
1669 , P_ATTRIBUTE_CATEGORY IN VARCHAR2 default null
1670 , P_ATTRIBUTE1 IN VARCHAR2 default null
1671 , P_ATTRIBUTE2 IN VARCHAR2 default null
1672 , P_ATTRIBUTE3 IN VARCHAR2 default null
1673 , P_ATTRIBUTE4 IN VARCHAR2 default null
1674 , P_ATTRIBUTE5 IN VARCHAR2 default null
1675 , P_ATTRIBUTE6 IN VARCHAR2 default null
1676 , P_ATTRIBUTE7 IN VARCHAR2 default null
1677 , P_ATTRIBUTE8 IN VARCHAR2 default null
1678 , P_ATTRIBUTE9 IN VARCHAR2 default null
1679 , P_ATTRIBUTE10 IN VARCHAR2 default null
1680 , P_ATTRIBUTE11 IN VARCHAR2 default null
1681 , P_ATTRIBUTE12 IN VARCHAR2 default null
1682 , P_ATTRIBUTE13 IN VARCHAR2 default null
1683 , P_ATTRIBUTE14 IN VARCHAR2 default null
1684 , P_ATTRIBUTE15 IN VARCHAR2 default null
1685 , P_ATTRIBUTE16 IN VARCHAR2 default null
1686 , P_ATTRIBUTE17 IN VARCHAR2 default null
1687 , P_ATTRIBUTE18 IN VARCHAR2 default null
1688 , P_ATTRIBUTE19 IN VARCHAR2 default null
1689 , P_ATTRIBUTE20 IN VARCHAR2 default null
1690 , P_ATTRIBUTE21 IN VARCHAR2 default null
1691 , P_ATTRIBUTE22 IN VARCHAR2 default null
1692 , P_ATTRIBUTE23 IN VARCHAR2 default null
1693 , P_ATTRIBUTE24 IN VARCHAR2 default null
1694 , P_ATTRIBUTE25 IN VARCHAR2 default null
1695 , P_ATTRIBUTE26 IN VARCHAR2 default null
1696 , P_ATTRIBUTE27 IN VARCHAR2 default null
1697 , P_ATTRIBUTE28 IN VARCHAR2 default null
1698 , P_ATTRIBUTE29 IN VARCHAR2 default null
1699 , P_ATTRIBUTE30 IN VARCHAR2 default null
1700 , P_STATUS_CHANGE_DATE IN DATE default null
1701 , P_VACANCY_ID IN NUMBER default null
1702 , P_SRC_APL_ASG_ID IN NUMBER default null
1703 , P_OFFER_ID OUT nocopy NUMBER
1704 , P_OFFER_VERSION OUT nocopy NUMBER
1705 , P_OBJECT_VERSION_NUMBER OUT nocopy NUMBER
1706
1707 ) is
1708 --
1709 -- Declare cursors and local variables
1710 --
1711 l_proc varchar2(72) := g_package||'create_offer';
1712 l_offer_id irc_offers.offer_id%TYPE;
1713 l_object_version_number irc_offers.object_version_number%TYPE := 1;
1714 l_effective_date date;
1715 l_expiry_date irc_offers.expiry_date%TYPE;
1716 l_proposed_start_date irc_offers.proposed_start_date%TYPE;
1717 l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
1718 l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
1719 l_latest_offer irc_offers.latest_offer%TYPE;
1720 l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
1721 l_status_change_date irc_offer_status_history.status_change_date%TYPE;
1722 l_offer_version irc_offers.offer_version%TYPE;
1723 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
1724 --
1725 l_updated_offer_id irc_offers.offer_id%TYPE;
1726 l_updated_offer_status irc_offers.offer_status%TYPE;
1727 l_updated_appl_assignment_id irc_offers.applicant_assignment_id%TYPE;
1728 --
1729 cursor csr_latest_offer is
1730 select offer_id
1731 ,offer_status
1732 ,applicant_assignment_id
1733 from irc_offers
1734 where latest_offer = 'Y'
1735 and ( applicant_assignment_id = nvl(p_applicant_assignment_id,-1)
1736 OR
1737 applicant_assignment_id = nvl(p_src_apl_asg_id,-1)
1738 OR
1739 applicant_assignment_id =
1740 (
1741 select tgt_apl_asg_id from per_vac_linked_assignments
1742 where src_apl_asg_id = p_src_apl_asg_id
1743 and sysdate between nvl(start_date,sysdate)
1744 and nvl(end_date,sysdate)
1745 )
1746 );
1747 --
1748 begin
1749 hr_utility.set_location('Entering:'|| l_proc, 10);
1750 --
1751 -- Issue a savepoint
1752 --
1753 savepoint CREATE_OFFER;
1754 --
1755 -- Truncate the time portion from all IN date parameters
1756 --
1757 l_effective_date := trunc(p_effective_date);
1758 l_expiry_date := trunc(p_expiry_date);
1759 l_proposed_start_date := trunc(p_proposed_start_date);
1760 l_offer_shipping_date := trunc(p_offer_shipping_date);
1761 l_status_change_date := p_status_change_date;
1762 --
1763 g_src_apl_asg_id := p_src_apl_asg_id;
1764 --
1765 hr_utility.set_location(l_proc,20);
1766 --
1767 -- If a prev offer exists for this applicant assignment, we need
1768 -- to close it and set this offer as the Latest.
1769 --
1770 --
1771 -- Check if there is some other offer record
1772 -- for the same applicant which has latest_offer
1773 -- set to 'Y'
1774 --
1775 open csr_latest_offer;
1776 fetch csr_latest_offer into l_updated_offer_id
1777 ,l_updated_offer_status
1778 ,l_updated_appl_assignment_id;
1779 --
1780 hr_utility.set_location(l_proc,40);
1781 --
1782 if (csr_latest_offer%found)
1783 then
1784 --
1785 close csr_latest_offer;
1786 --
1787 hr_utility.set_location(l_proc,45);
1788 --
1789 -- Set the offer_status of the record to be
1790 -- updated to 'Closed', the latest_offer
1791 -- to 'N' and change_reason to 'Offer Updated'
1792 --
1793 update_latest_offer_flag
1794 ( p_offer_id => l_updated_offer_id
1795 ,p_effective_date => l_effective_date
1796 ,p_validate => p_validate
1797 ,p_offer_status => 'CLOSED'
1798 ,p_latest_offer => 'N'
1799 ,p_change_reason => 'UPDATED'
1800 ,p_status_change_date => l_status_change_date
1801 );
1802 --
1803 -- Change the Applicant's assignment status to what it was previously.
1804 -- Do this only if the updated offer was in EXTENDED or CLOSED State as
1805 -- only then would the application status have been changed.
1806 --
1807 if l_updated_offer_status in ('EXTENDED', 'CLOSED')
1808 then
1809 --
1810 update_appl_assg_status
1811 ( p_validate => p_validate
1812 ,p_effective_date => l_effective_date
1813 ,p_applicant_assignment_id => l_updated_appl_assignment_id
1814 ,p_offer_status => l_updated_offer_status
1815 ,p_change_reason => 'UPDATED'
1816 );
1817 --
1818 end if;
1819 --
1820 else
1821 close csr_latest_offer;
1822 end if;
1823 --
1824 -- This offer should now be the latest offer
1825 --
1826 l_latest_offer := 'Y';
1827 --
1828 hr_utility.set_location(l_proc,48);
1829 --
1830 -- Call Before Process User Hook
1831 --
1832 begin
1833 IRC_OFFERS_BK1.create_offer_b
1834 ( P_EFFECTIVE_DATE => l_effective_date
1835 , P_LATEST_OFFER => l_latest_offer
1836 , P_OFFER_STATUS => P_OFFER_STATUS
1837 , P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE
1838 , P_OFFER_EXTENDED_METHOD => P_OFFER_EXTENDED_METHOD
1839 , P_RESPONDENT_ID => P_RESPONDENT_ID
1840 , P_EXPIRY_DATE => l_expiry_date
1841 , P_PROPOSED_START_DATE => l_proposed_start_date
1842 , P_OFFER_LETTER_TRACKING_CODE => P_OFFER_LETTER_TRACKING_CODE
1843 , P_OFFER_POSTAL_SERVICE => P_OFFER_POSTAL_SERVICE
1844 , P_OFFER_SHIPPING_DATE => l_offer_shipping_date
1845 , P_APPLICANT_ASSIGNMENT_ID => P_APPLICANT_ASSIGNMENT_ID
1846 , P_OFFER_ASSIGNMENT_ID => P_OFFER_ASSIGNMENT_ID
1847 , P_ADDRESS_ID => P_ADDRESS_ID
1848 , P_TEMPLATE_ID => P_TEMPLATE_ID
1849 , P_OFFER_LETTER_FILE_TYPE => P_OFFER_LETTER_FILE_TYPE
1850 , P_OFFER_LETTER_FILE_NAME => P_OFFER_LETTER_FILE_NAME
1851 , P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
1852 , P_ATTRIBUTE1 => P_ATTRIBUTE1
1853 , P_ATTRIBUTE2 => P_ATTRIBUTE2
1854 , P_ATTRIBUTE3 => P_ATTRIBUTE3
1855 , P_ATTRIBUTE4 => P_ATTRIBUTE4
1856 , P_ATTRIBUTE5 => P_ATTRIBUTE5
1857 , P_ATTRIBUTE6 => P_ATTRIBUTE6
1858 , P_ATTRIBUTE7 => P_ATTRIBUTE7
1859 , P_ATTRIBUTE8 => P_ATTRIBUTE8
1860 , P_ATTRIBUTE9 => P_ATTRIBUTE9
1861 , P_ATTRIBUTE10 => P_ATTRIBUTE10
1862 , P_ATTRIBUTE11 => P_ATTRIBUTE11
1863 , P_ATTRIBUTE12 => P_ATTRIBUTE12
1864 , P_ATTRIBUTE13 => P_ATTRIBUTE13
1865 , P_ATTRIBUTE14 => P_ATTRIBUTE14
1866 , P_ATTRIBUTE15 => P_ATTRIBUTE15
1867 , P_ATTRIBUTE16 => P_ATTRIBUTE16
1868 , P_ATTRIBUTE17 => P_ATTRIBUTE17
1869 , P_ATTRIBUTE18 => P_ATTRIBUTE18
1870 , P_ATTRIBUTE19 => P_ATTRIBUTE19
1871 , P_ATTRIBUTE20 => P_ATTRIBUTE20
1872 , P_ATTRIBUTE21 => P_ATTRIBUTE21
1873 , P_ATTRIBUTE22 => P_ATTRIBUTE22
1874 , P_ATTRIBUTE23 => P_ATTRIBUTE23
1875 , P_ATTRIBUTE24 => P_ATTRIBUTE24
1876 , P_ATTRIBUTE25 => P_ATTRIBUTE25
1877 , P_ATTRIBUTE26 => P_ATTRIBUTE26
1878 , P_ATTRIBUTE27 => P_ATTRIBUTE27
1879 , P_ATTRIBUTE28 => P_ATTRIBUTE28
1880 , P_ATTRIBUTE29 => P_ATTRIBUTE29
1881 , P_ATTRIBUTE30 => P_ATTRIBUTE30
1882 );
1883 exception
1884 when hr_api.cannot_find_prog_unit then
1885 hr_api.cannot_find_prog_unit_error
1886 (p_module_name => 'create_offer'
1887 ,p_hook_type => 'BP'
1888 );
1889 end;
1890 --
1891 -- Validation in addition to Row Handlers
1892 --
1893 --
1894 -- Process Logic
1895 --
1896 irc_iof_ins.ins
1897 ( p_effective_date => l_effective_date
1898 , p_latest_offer => l_latest_offer
1899 , p_applicant_assignment_id => p_applicant_assignment_id
1900 , p_offer_assignment_id => p_offer_assignment_id
1901 , p_offer_status => p_offer_status
1902 , p_discretionary_job_title => p_discretionary_job_title
1903 , p_offer_extended_method => p_offer_extended_method
1904 , p_respondent_id => p_respondent_id
1905 , p_expiry_date => l_expiry_date
1906 , p_proposed_start_date => l_proposed_start_date
1907 , p_offer_letter_tracking_code => p_offer_letter_tracking_code
1908 , p_offer_postal_service => p_offer_postal_service
1909 , p_offer_shipping_date => l_offer_shipping_date
1910 , p_address_id => p_address_id
1911 , p_template_id => p_template_id
1912 , p_offer_letter_file_type => p_offer_letter_file_type
1913 , p_offer_letter_file_name => p_offer_letter_file_name
1914 , p_attribute_category => p_attribute_category
1915 , p_attribute1 => p_attribute1
1916 , p_attribute2 => p_attribute2
1917 , p_attribute3 => p_attribute3
1918 , p_attribute4 => p_attribute4
1919 , p_attribute5 => p_attribute5
1920 , p_attribute6 => p_attribute6
1921 , p_attribute7 => p_attribute7
1922 , p_attribute8 => p_attribute8
1923 , p_attribute9 => p_attribute9
1924 , p_attribute10 => p_attribute10
1925 , p_attribute11 => p_attribute11
1926 , p_attribute12 => p_attribute12
1927 , p_attribute13 => p_attribute13
1928 , p_attribute14 => p_attribute14
1929 , p_attribute15 => p_attribute15
1930 , p_attribute16 => p_attribute16
1931 , p_attribute17 => p_attribute17
1932 , p_attribute18 => p_attribute18
1933 , p_attribute19 => p_attribute19
1934 , p_attribute20 => p_attribute20
1935 , p_attribute21 => p_attribute21
1936 , p_attribute22 => p_attribute22
1937 , p_attribute23 => p_attribute23
1938 , p_attribute24 => p_attribute24
1939 , p_attribute25 => p_attribute25
1940 , p_attribute26 => p_attribute26
1941 , p_attribute27 => p_attribute27
1942 , p_attribute28 => p_attribute28
1943 , p_attribute29 => p_attribute29
1944 , p_attribute30 => p_attribute30
1945 , p_vacancy_id => p_vacancy_id
1946 , p_offer_id => l_offer_id
1947 , p_offer_version => l_offer_version
1948 , p_object_version_number => l_object_version_number
1949 );
1950 hr_utility.set_location(l_proc,50);
1951 --
1952 -- Create offer history record for the newly created
1953 -- Offer record.
1954 --
1955 irc_offer_status_history_api.create_offer_status_history
1956 ( p_validate => p_validate
1957 , p_effective_date => l_effective_date
1958 , p_offer_status_history_id => l_offer_status_history_id
1959 , p_offer_id => l_offer_id
1960 , p_offer_status => p_offer_status
1961 , p_change_reason => null
1962 , p_decline_reason => null
1963 , p_note_text => null
1964 , p_object_version_number => l_osh_object_version_number
1965 , p_status_change_date => l_status_change_date
1966 );
1967 hr_utility.set_location(l_proc,60);
1968 --
1969 -- Call After Process User Hook
1970 --
1971 begin
1972 IRC_OFFERS_BK1.create_offer_a
1973 ( P_EFFECTIVE_DATE => l_effective_date
1974 , P_LATEST_OFFER => l_latest_offer
1975 , P_OFFER_STATUS => P_OFFER_STATUS
1976 , P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE
1977 , P_OFFER_EXTENDED_METHOD => P_OFFER_EXTENDED_METHOD
1978 , P_RESPONDENT_ID => P_RESPONDENT_ID
1979 , P_EXPIRY_DATE => l_expiry_date
1980 , P_PROPOSED_START_DATE => l_proposed_start_date
1981 , P_OFFER_LETTER_TRACKING_CODE => P_OFFER_LETTER_TRACKING_CODE
1982 , P_OFFER_POSTAL_SERVICE => P_OFFER_POSTAL_SERVICE
1983 , P_OFFER_SHIPPING_DATE => l_offer_shipping_date
1984 , P_APPLICANT_ASSIGNMENT_ID => P_APPLICANT_ASSIGNMENT_ID
1985 , P_OFFER_ASSIGNMENT_ID => P_OFFER_ASSIGNMENT_ID
1986 , P_ADDRESS_ID => P_ADDRESS_ID
1987 , P_TEMPLATE_ID => P_TEMPLATE_ID
1988 , P_OFFER_LETTER_FILE_TYPE => P_OFFER_LETTER_FILE_TYPE
1989 , P_OFFER_LETTER_FILE_NAME => P_OFFER_LETTER_FILE_NAME
1990 , P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
1991 , P_ATTRIBUTE1 => P_ATTRIBUTE1
1992 , P_ATTRIBUTE2 => P_ATTRIBUTE2
1993 , P_ATTRIBUTE3 => P_ATTRIBUTE3
1994 , P_ATTRIBUTE4 => P_ATTRIBUTE4
1995 , P_ATTRIBUTE5 => P_ATTRIBUTE5
1996 , P_ATTRIBUTE6 => P_ATTRIBUTE6
1997 , P_ATTRIBUTE7 => P_ATTRIBUTE7
1998 , P_ATTRIBUTE8 => P_ATTRIBUTE8
1999 , P_ATTRIBUTE9 => P_ATTRIBUTE9
2000 , P_ATTRIBUTE10 => P_ATTRIBUTE10
2001 , P_ATTRIBUTE11 => P_ATTRIBUTE11
2002 , P_ATTRIBUTE12 => P_ATTRIBUTE12
2003 , P_ATTRIBUTE13 => P_ATTRIBUTE13
2004 , P_ATTRIBUTE14 => P_ATTRIBUTE14
2005 , P_ATTRIBUTE15 => P_ATTRIBUTE15
2006 , P_ATTRIBUTE16 => P_ATTRIBUTE16
2007 , P_ATTRIBUTE17 => P_ATTRIBUTE17
2008 , P_ATTRIBUTE18 => P_ATTRIBUTE18
2009 , P_ATTRIBUTE19 => P_ATTRIBUTE19
2010 , P_ATTRIBUTE20 => P_ATTRIBUTE20
2011 , P_ATTRIBUTE21 => P_ATTRIBUTE21
2012 , P_ATTRIBUTE22 => P_ATTRIBUTE22
2013 , P_ATTRIBUTE23 => P_ATTRIBUTE23
2014 , P_ATTRIBUTE24 => P_ATTRIBUTE24
2015 , P_ATTRIBUTE25 => P_ATTRIBUTE25
2016 , P_ATTRIBUTE26 => P_ATTRIBUTE26
2017 , P_ATTRIBUTE27 => P_ATTRIBUTE27
2018 , P_ATTRIBUTE28 => P_ATTRIBUTE28
2019 , P_ATTRIBUTE29 => P_ATTRIBUTE29
2020 , P_ATTRIBUTE30 => P_ATTRIBUTE30
2021 , P_OFFER_ID => l_offer_id
2022 , P_OFFER_VERSION => l_offer_version
2023 , P_OBJECT_VERSION_NUMBER => l_object_version_number
2024 );
2025 exception
2026 when hr_api.cannot_find_prog_unit then
2027 hr_api.cannot_find_prog_unit_error
2028 (p_module_name => 'create_offer'
2029 ,p_hook_type => 'AP'
2030 );
2031 end;
2032 --
2033 -- When in validation only mode raise the Validate_Enabled exception
2034 --
2035 if p_validate then
2036 raise hr_api.validate_enabled;
2037 end if;
2038 --
2039 -- Set all IN OUT and OUT parameters with out values
2040 --
2041 p_offer_id := l_offer_id;
2042 p_object_version_number := l_object_version_number;
2043 p_offer_version := l_offer_version;
2044 --
2045 hr_utility.set_location(' Leaving:'||l_proc, 70);
2046 exception
2047 when hr_api.validate_enabled then
2048 --
2049 -- As the Validate_Enabled exception has been raised
2050 -- we must rollback to the savepoint
2051 --
2052 rollback to CREATE_OFFER;
2053 --
2054 -- Reset IN OUT parameters and set OUT parameters
2055 -- (Any key or derived arguments must be set to null
2056 -- when validation only mode is being used.)
2057 --
2058 p_offer_id := null;
2059 p_offer_version := null;
2060 p_object_version_number := null;
2061 --
2062 hr_utility.set_location(' Leaving:'||l_proc, 80);
2063 when others then
2064 --
2065 -- Reset IN OUT parameters and set all
2066 -- OUT parameters, including warnings, to null
2067 --
2068 p_offer_id := null;
2069 p_offer_version := null;
2070 p_object_version_number := null;
2071 --
2072 -- A validation or unexpected error has occured
2073 --
2074 rollback to CREATE_OFFER;
2075 --
2076 hr_utility.set_location(' Leaving:'||l_proc, 90);
2077 raise;
2078 end create_offer;
2079 --
2080 -- ----------------------------------------------------------------------------
2081 -- |---------------------------< update_offer >-------------------------------|
2082 -- ----------------------------------------------------------------------------
2083 --
2084 procedure update_offer
2085 ( P_VALIDATE IN boolean default false
2086 ,P_EFFECTIVE_DATE IN date default null
2087 ,P_OFFER_STATUS IN VARCHAR2 default hr_api.g_varchar2
2088 ,P_DISCRETIONARY_JOB_TITLE IN VARCHAR2 default hr_api.g_varchar2
2089 ,P_OFFER_EXTENDED_METHOD IN VARCHAR2 default hr_api.g_varchar2
2090 ,P_RESPONDENT_ID IN NUMBER default hr_api.g_number
2091 ,P_EXPIRY_DATE IN DATE default hr_api.g_date
2092 ,P_PROPOSED_START_DATE IN DATE default hr_api.g_date
2093 ,P_OFFER_LETTER_TRACKING_CODE IN VARCHAR2 default hr_api.g_varchar2
2094 ,P_OFFER_POSTAL_SERVICE IN VARCHAR2 default hr_api.g_varchar2
2095 ,P_OFFER_SHIPPING_DATE IN DATE default hr_api.g_date
2096 ,P_APPLICANT_ASSIGNMENT_ID IN NUMBER default hr_api.g_number
2097 ,P_OFFER_ASSIGNMENT_ID IN NUMBER default hr_api.g_number
2098 ,P_ADDRESS_ID IN NUMBER default hr_api.g_number
2099 ,P_TEMPLATE_ID IN NUMBER default hr_api.g_number
2100 ,P_OFFER_LETTER_FILE_TYPE IN VARCHAR2 default hr_api.g_varchar2
2101 ,P_OFFER_LETTER_FILE_NAME IN VARCHAR2 default hr_api.g_varchar2
2102 ,P_ATTRIBUTE_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
2103 ,P_ATTRIBUTE1 IN VARCHAR2 default hr_api.g_varchar2
2104 ,P_ATTRIBUTE2 IN VARCHAR2 default hr_api.g_varchar2
2105 ,P_ATTRIBUTE3 IN VARCHAR2 default hr_api.g_varchar2
2106 ,P_ATTRIBUTE4 IN VARCHAR2 default hr_api.g_varchar2
2107 ,P_ATTRIBUTE5 IN VARCHAR2 default hr_api.g_varchar2
2108 ,P_ATTRIBUTE6 IN VARCHAR2 default hr_api.g_varchar2
2109 ,P_ATTRIBUTE7 IN VARCHAR2 default hr_api.g_varchar2
2110 ,P_ATTRIBUTE8 IN VARCHAR2 default hr_api.g_varchar2
2111 ,P_ATTRIBUTE9 IN VARCHAR2 default hr_api.g_varchar2
2112 ,P_ATTRIBUTE10 IN VARCHAR2 default hr_api.g_varchar2
2113 ,P_ATTRIBUTE11 IN VARCHAR2 default hr_api.g_varchar2
2114 ,P_ATTRIBUTE12 IN VARCHAR2 default hr_api.g_varchar2
2115 ,P_ATTRIBUTE13 IN VARCHAR2 default hr_api.g_varchar2
2116 ,P_ATTRIBUTE14 IN VARCHAR2 default hr_api.g_varchar2
2117 ,P_ATTRIBUTE15 IN VARCHAR2 default hr_api.g_varchar2
2118 ,P_ATTRIBUTE16 IN VARCHAR2 default hr_api.g_varchar2
2119 ,P_ATTRIBUTE17 IN VARCHAR2 default hr_api.g_varchar2
2120 ,P_ATTRIBUTE18 IN VARCHAR2 default hr_api.g_varchar2
2121 ,P_ATTRIBUTE19 IN VARCHAR2 default hr_api.g_varchar2
2122 ,P_ATTRIBUTE20 IN VARCHAR2 default hr_api.g_varchar2
2123 ,P_ATTRIBUTE21 IN VARCHAR2 default hr_api.g_varchar2
2124 ,P_ATTRIBUTE22 IN VARCHAR2 default hr_api.g_varchar2
2125 ,P_ATTRIBUTE23 IN VARCHAR2 default hr_api.g_varchar2
2126 ,P_ATTRIBUTE24 IN VARCHAR2 default hr_api.g_varchar2
2127 ,P_ATTRIBUTE25 IN VARCHAR2 default hr_api.g_varchar2
2128 ,P_ATTRIBUTE26 IN VARCHAR2 default hr_api.g_varchar2
2129 ,P_ATTRIBUTE27 IN VARCHAR2 default hr_api.g_varchar2
2130 ,P_ATTRIBUTE28 IN VARCHAR2 default hr_api.g_varchar2
2131 ,P_ATTRIBUTE29 IN VARCHAR2 default hr_api.g_varchar2
2132 ,P_ATTRIBUTE30 IN VARCHAR2 default hr_api.g_varchar2
2133 ,P_CHANGE_REASON IN VARCHAR2 default null
2134 ,P_DECLINE_REASON IN VARCHAR2 default null
2135 ,P_NOTE_TEXT IN VARCHAR2 default null
2136 ,P_STATUS_CHANGE_DATE IN DATE default null
2137 ,P_OFFER_ID IN OUT nocopy NUMBER
2138 ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
2139 ,P_OFFER_VERSION OUT nocopy NUMBER
2140 ) is
2141 --
2142 -- Declare local variables
2143 --
2144 l_proc varchar2(72) := g_package||'update_offer';
2145 l_object_version_number irc_offers.object_version_number%TYPE;
2146 l_effective_date date;
2147 l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
2148 l_expiry_date irc_offers.expiry_date%TYPE;
2149 l_prev_expiry_date irc_offers.expiry_date%TYPE;
2150 l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2151 l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2152 l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
2153 l_prev_offer_status irc_offers.offer_status%TYPE;
2154 l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
2155 l_latest_offer irc_offers.latest_offer%TYPE := hr_api.g_varchar2;
2156 l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
2157 l_status_change_date irc_offer_status_history.status_change_date%TYPE;
2158 l_mutiple_fields_updated boolean;
2159 l_offer_version irc_offers.offer_version%TYPE;
2160 l_offer_status irc_offers.offer_status%TYPE := p_offer_status;
2161 l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2162
2163 l_create_new_version boolean := false;
2164 l_other_extended_offer_count number := 0;
2165 --
2166 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
2167 l_validation_start_date date;
2168 l_validation_end_date date;
2169 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
2170 l_org_now_no_manager_warning boolean;
2171 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
2172 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
2173 --
2174 l_prev_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2175 l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
2176 l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;
2177 l_offer_postal_service irc_offers.offer_postal_service%TYPE;
2178 l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2179 l_prev_offer_ovn irc_offers.object_version_number%TYPE;
2180 l_prev_offer_change_reason irc_offer_status_history.change_reason%TYPE;
2181 --
2182 -- Declare cursors
2183 --
2184 cursor csr_prev_offer_details is
2185 select offer_status
2186 ,expiry_date
2187 ,applicant_assignment_id
2188 ,offer_assignment_id
2189 ,offer_letter_tracking_code
2190 ,offer_postal_service
2191 ,offer_shipping_date
2192 ,object_version_number
2193 from irc_offers
2194 where offer_id = p_offer_id;
2195 --
2196 cursor csr_assignment_record is
2197 select object_version_number
2198 ,effective_end_date
2199 from per_all_assignments_f
2200 where assignment_id = l_prev_offer_assignment_id;
2201 --
2202 cursor csr_offer_status_history_id is
2203 select HISTORY.offer_status_history_id
2204 from irc_offer_status_history HISTORY
2205 where HISTORY.offer_id = p_offer_id
2206 and NOT EXISTS
2207 (SELECT 1
2208 FROM irc_offer_status_history iosh1
2209 WHERE iosh1.offer_id = HISTORY.offer_id
2210 AND iosh1.status_change_date > HISTORY.status_change_date
2211 )
2212 AND HISTORY.offer_status_history_id =
2213 (SELECT MAX(iosh2.offer_status_history_id)
2214 FROM irc_offer_status_history iosh2
2215 WHERE iosh2.offer_id = HISTORY.offer_id
2216 AND iosh2.status_change_date = HISTORY.status_change_date
2217 );
2218 --
2219 cursor csr_offer_status_history_dets(p_status_history_id number) is
2220 select change_reason
2221 from irc_offer_status_history
2222 where offer_status_history_id = p_status_history_id;
2223 --
2224 cursor csr_offer_record is
2225 select
2226 offer_version
2227 ,offer_status
2228 ,discretionary_job_title
2229 ,offer_extended_method
2230 ,respondent_id
2231 ,expiry_date
2232 ,proposed_start_date
2233 ,offer_letter_tracking_code
2234 ,offer_postal_service
2235 ,offer_shipping_date
2236 ,vacancy_id
2237 ,applicant_assignment_id
2238 ,offer_assignment_id
2239 ,address_id
2240 ,template_id
2241 ,offer_letter_file_type
2242 ,offer_letter_file_name
2243 ,attribute_category
2244 ,attribute1
2245 ,attribute2
2246 ,attribute3
2247 ,attribute4
2248 ,attribute5
2249 ,attribute6
2250 ,attribute7
2251 ,attribute8
2252 ,attribute9
2253 ,attribute10
2254 ,attribute11
2255 ,attribute12
2256 ,attribute13
2257 ,attribute14
2258 ,attribute15
2259 ,attribute16
2260 ,attribute17
2261 ,attribute18
2262 ,attribute19
2263 ,attribute20
2264 ,attribute21
2265 ,attribute22
2266 ,attribute23
2267 ,attribute24
2268 ,attribute25
2269 ,attribute26
2270 ,attribute27
2271 ,attribute28
2272 ,attribute29
2273 ,attribute30
2274 from irc_offers
2275 where offer_id = p_offer_id;
2276
2277 l_offer_record csr_offer_record%ROWTYPE;
2278 l_temp_offer_record csr_offer_record%ROWTYPE;
2279 --
2280 --
2281 begin
2282 hr_utility.set_location('Entering:'|| l_proc, 10);
2283 --
2284 -- Issue a savepoint
2285 --
2286 savepoint UPDATE_OFFER;
2287 --
2288 -- Truncate the time portion from all IN date parameters
2289 --
2290 l_effective_date := trunc(p_effective_date);
2291 l_expiry_date := trunc(p_expiry_date);
2292 l_proposed_start_date := trunc(p_proposed_start_date);
2293 l_offer_shipping_date := trunc(p_offer_shipping_date);
2294 l_status_change_date := p_status_change_date;
2295 --
2296 open csr_prev_offer_details;
2297 fetch csr_prev_offer_details into l_prev_offer_status
2298 ,l_prev_expiry_date
2299 ,l_prev_applicant_assignment_id
2300 ,l_prev_offer_assignment_id
2301 ,l_offer_letter_tracking_code
2302 ,l_offer_postal_service
2303 ,l_prev_offer_shipping_date
2304 ,l_prev_offer_ovn;
2305 --
2306 if csr_prev_offer_details%notfound
2307 then
2308 --
2309 close csr_prev_offer_details;
2310 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
2311 fnd_message.raise_error;
2312 --
2313 end if;
2314 close csr_prev_offer_details;
2315 --
2316 if l_offer_status = hr_api.g_varchar2
2317 then
2318 --
2319 l_offer_status := l_prev_offer_status;
2320 --
2321 end if;
2322 --
2323 open csr_assignment_record;
2324 fetch csr_assignment_record into l_asg_object_version_number
2325 ,l_effective_end_date;
2326 if csr_assignment_record%notfound
2327 then
2328 --
2329 close csr_assignment_record;
2330 fnd_message.set_name('PER','HR_289541_PJU_INV_ASG_ID');
2331 fnd_message.raise_error;
2332 --
2333 end if;
2334 close csr_assignment_record;
2335 --
2336 -- The following check is for the creation of a new offer version.
2337 --
2338 if l_prev_offer_status = 'CLOSED'
2339 then
2340 --
2341 -- Check if the offer_status has been set to Extended Already.
2342 -- This will save us some work :)
2343 --
2344 if p_offer_status = 'EXTENDED'
2345 then
2346 --
2347 -- Re-Open the Offer Assignment
2348 --
2349 l_effective_date := trunc(l_effective_end_date);
2350 --
2351 per_asg_del.del
2352 (
2353 p_assignment_id => l_prev_offer_assignment_id
2354 ,p_effective_start_date => l_effective_start_date
2355 ,p_effective_end_date => l_effective_end_date
2356 ,p_business_group_id => l_business_group_id
2357 ,p_object_version_number => l_asg_object_version_number
2358 ,p_effective_date => l_effective_date
2359 ,p_validation_start_date => l_validation_start_date
2360 ,p_validation_end_date => l_validation_end_date
2361 ,p_datetrack_mode => 'FUTURE_CHANGE'
2362 ,p_validate => p_validate
2363 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2364 );
2365 --
2366 l_effective_date := trunc(p_effective_date);
2367 --
2368 else
2369 --
2370 -- The Offer has not yet been set to EXTENDED, do the job of
2371 -- checking for new offer version.
2372 --
2373 -- The code to check new offer version creation has been disabled
2374 -- since this will now be taken care of in the Java Layer itself.
2375 -- New Offer version creation code is no longer supported.
2376 --
2377 --
2378 -- l_create_new_version := true;
2379 --
2380 hr_utility.set_location(l_proc,50);
2381 --
2382 -- 'CLOSED' offer can be updated under only 1 circumstance:
2383 -- 1) The offer duration has been Extended
2384 --
2385 -- Under all other circumstances, a new offer version should be created.
2386 --
2387 if (( nvl(l_expiry_date,hr_api.g_date) <> nvl(l_prev_expiry_date,hr_api.g_date))
2388 )
2389 then
2390 --
2391 irc_iof_bus.chk_multiple_fields_updated
2392 ( p_mutiple_fields_updated => l_mutiple_fields_updated
2393 ,p_offer_id => p_offer_id
2394 ,p_offer_status => p_offer_status
2395 ,p_discretionary_job_title => p_discretionary_job_title
2396 ,p_offer_extended_method => p_offer_extended_method
2397 ,p_expiry_date => l_expiry_date
2398 ,p_proposed_start_date => p_proposed_start_date
2399 ,p_offer_letter_tracking_code => p_offer_letter_tracking_code
2400 ,p_offer_postal_service => p_offer_postal_service
2401 ,p_offer_shipping_date => p_offer_shipping_date
2402 ,p_applicant_assignment_id => p_applicant_assignment_id
2403 ,p_offer_assignment_id => p_offer_assignment_id
2404 ,p_address_id => p_address_id
2405 ,p_template_id => p_template_id
2406 ,p_offer_letter_file_type => p_offer_letter_file_type
2407 ,p_offer_letter_file_name => p_offer_letter_file_name
2408 ,p_attribute_category => p_attribute_category
2409 ,p_attribute1 => p_attribute1
2410 ,p_attribute2 => p_attribute2
2411 ,p_attribute3 => p_attribute3
2412 ,p_attribute4 => p_attribute4
2413 ,p_attribute5 => p_attribute5
2414 ,p_attribute6 => p_attribute6
2415 ,p_attribute7 => p_attribute7
2416 ,p_attribute8 => p_attribute8
2417 ,p_attribute9 => p_attribute9
2418 ,p_attribute10 => p_attribute10
2419 ,p_attribute11 => p_attribute11
2420 ,p_attribute12 => p_attribute12
2421 ,p_attribute13 => p_attribute13
2422 ,p_attribute14 => p_attribute14
2423 ,p_attribute15 => p_attribute15
2424 ,p_attribute16 => p_attribute16
2425 ,p_attribute17 => p_attribute17
2426 ,p_attribute18 => p_attribute18
2427 ,p_attribute19 => p_attribute19
2428 ,p_attribute20 => p_attribute20
2429 ,p_attribute21 => p_attribute21
2430 ,p_attribute22 => p_attribute22
2431 ,p_attribute23 => p_attribute23
2432 ,p_attribute24 => p_attribute24
2433 ,p_attribute25 => p_attribute25
2434 ,p_attribute26 => p_attribute26
2435 ,p_attribute27 => p_attribute27
2436 ,p_attribute28 => p_attribute28
2437 ,p_attribute29 => p_attribute29
2438 ,p_attribute30 => p_attribute30
2439 );
2440 --
2441 if ( l_mutiple_fields_updated = false
2442 AND p_offer_status <> 'HOLD' -- Work Around! if ofeer is set to HOLD and the expiry date
2443 -- is not passed in, the flow can come this far.
2444 -- Atleast stop it here.
2445 )
2446
2447 then
2448 l_create_new_version := false;
2449 --
2450 if P_CHANGE_REASON<>'APL_DECLINED_ACCEPTANCE' AND P_CHANGE_REASON<>'MGR_WITHDRAW' AND P_CHANGE_REASON<>'APL_HIRED' then
2451 --
2452 -- If the offer duration has been extended, we need to change
2453 -- the offer status to 'EXTENDED'
2454 --
2455 l_offer_status := 'EXTENDED';
2456 --
2457 -- Re-Open the Offer Assignment
2458 --
2459 l_effective_date := trunc(l_effective_end_date);
2460 --
2461 per_asg_del.del
2462 (
2463 p_assignment_id => l_prev_offer_assignment_id
2464 ,p_effective_start_date => l_effective_start_date
2465 ,p_effective_end_date => l_effective_end_date
2466 ,p_business_group_id => l_business_group_id
2467 ,p_object_version_number => l_asg_object_version_number
2468 ,p_effective_date => l_effective_date
2469 ,p_validation_start_date => l_validation_start_date
2470 ,p_validation_end_date => l_validation_end_date
2471 ,p_datetrack_mode => 'FUTURE_CHANGE'
2472 ,p_validate => p_validate
2473 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2474 );
2475 --
2476 l_effective_date := trunc(p_effective_date);
2477 --
2478 end if;
2479 end if;
2480 end if; -- P_CHANGE_REASON<>'APL_DECLINED_ACCEPTANCE'
2481 end if; -- offer_status = 'EXTENDED'
2482 --
2483 end if; -- prev_offer_status = 'CLOSED'
2484 --
2485 hr_utility.set_location(l_proc,60);
2486 --
2487 -- If l_create_new_version is true, a new offer version should be
2488 -- created and the current offer version should be closed.
2489 --
2490 if l_create_new_version = true
2491 then
2492 --
2493 hr_utility.set_location(l_proc,70);
2494 --
2495 -- Fetch the previous offer version's record.
2496 --
2497 open csr_offer_record;
2498 fetch csr_offer_record into l_offer_record;
2499 --
2500 if csr_offer_record%notfound
2501 then
2502 --
2503 hr_utility.set_location(l_proc, 75);
2504 --
2505 close csr_offer_record;
2506 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
2507 fnd_message.raise_error;
2508 --
2509 end if;
2510 close csr_offer_record;
2511 --
2512 -- Store current values in a temporary variable
2513 --
2514 l_temp_offer_record.discretionary_job_title := p_discretionary_job_title;
2515 l_temp_offer_record.offer_extended_method := p_offer_extended_method;
2516 l_temp_offer_record.respondent_id := p_respondent_id;
2517 l_temp_offer_record.expiry_date := l_expiry_date;
2518 l_temp_offer_record.proposed_start_date := p_proposed_start_date;
2519 l_temp_offer_record.offer_letter_tracking_code := p_offer_letter_tracking_code;
2520 l_temp_offer_record.offer_postal_service := p_offer_postal_service;
2521 l_temp_offer_record.offer_shipping_date := p_offer_shipping_date;
2522 l_temp_offer_record.applicant_assignment_id := p_applicant_assignment_id;
2523 l_temp_offer_record.offer_assignment_id := p_offer_assignment_id;
2524 l_temp_offer_record.address_id := p_address_id;
2525 l_temp_offer_record.template_id := p_template_id;
2526 l_temp_offer_record.offer_letter_file_type := p_offer_letter_file_type;
2527 l_temp_offer_record.offer_letter_file_name := p_offer_letter_file_name;
2528 l_temp_offer_record.attribute_category := p_attribute_category;
2529 l_temp_offer_record.attribute1 := p_attribute1;
2530 l_temp_offer_record.attribute2 := p_attribute2;
2531 l_temp_offer_record.attribute3 := p_attribute3;
2532 l_temp_offer_record.attribute4 := p_attribute4;
2533 l_temp_offer_record.attribute5 := p_attribute5;
2534 l_temp_offer_record.attribute6 := p_attribute6;
2535 l_temp_offer_record.attribute7 := p_attribute7;
2536 l_temp_offer_record.attribute8 := p_attribute8;
2537 l_temp_offer_record.attribute9 := p_attribute9;
2538 l_temp_offer_record.attribute10 := p_attribute10;
2539 l_temp_offer_record.attribute11 := p_attribute11;
2540 l_temp_offer_record.attribute12 := p_attribute12;
2541 l_temp_offer_record.attribute13 := p_attribute13;
2542 l_temp_offer_record.attribute14 := p_attribute14;
2543 l_temp_offer_record.attribute15 := p_attribute15;
2544 l_temp_offer_record.attribute16 := p_attribute16;
2545 l_temp_offer_record.attribute17 := p_attribute17;
2546 l_temp_offer_record.attribute18 := p_attribute18;
2547 l_temp_offer_record.attribute19 := p_attribute19;
2548 l_temp_offer_record.attribute20 := p_attribute20;
2549 l_temp_offer_record.attribute21 := p_attribute21;
2550 l_temp_offer_record.attribute22 := p_attribute22;
2551 l_temp_offer_record.attribute23 := p_attribute23;
2552 l_temp_offer_record.attribute24 := p_attribute24;
2553 l_temp_offer_record.attribute25 := p_attribute25;
2554 l_temp_offer_record.attribute26 := p_attribute26;
2555 l_temp_offer_record.attribute27 := p_attribute27;
2556 l_temp_offer_record.attribute28 := p_attribute28;
2557 l_temp_offer_record.attribute29 := p_attribute29;
2558 l_temp_offer_record.attribute30 := p_attribute30;
2559 --
2560 -- Check if any of the variables have system default values,
2561 -- if so, set them to their previous values.
2562 --
2563 if ( l_temp_offer_record.discretionary_job_title = hr_api.g_varchar2) then
2564 l_temp_offer_record.discretionary_job_title := l_offer_record.discretionary_job_title;
2565 end if;
2566 if ( l_temp_offer_record.offer_extended_method = hr_api.g_varchar2) then
2567 l_temp_offer_record.offer_extended_method := l_offer_record.offer_extended_method;
2568 end if;
2569 if ( l_temp_offer_record.respondent_id = hr_api.g_number) then
2570 l_temp_offer_record.respondent_id := l_offer_record.respondent_id;
2571 end if;
2572 if ( l_temp_offer_record.expiry_date = hr_api.g_date) then
2573 l_temp_offer_record.expiry_date := l_offer_record.expiry_date;
2574 end if;
2575 if ( l_temp_offer_record.proposed_start_date = hr_api.g_date) then
2576 l_temp_offer_record.proposed_start_date := l_offer_record.proposed_start_date;
2577 end if;
2578 if ( l_temp_offer_record.offer_letter_tracking_code = hr_api.g_varchar2) then
2579 l_temp_offer_record.offer_letter_tracking_code := l_offer_record.offer_letter_tracking_code;
2580 end if;
2581 if ( l_temp_offer_record.offer_postal_service = hr_api.g_varchar2) then
2582 l_temp_offer_record.offer_postal_service := l_offer_record.offer_postal_service;
2583 end if;
2584 if ( l_temp_offer_record.offer_shipping_date = hr_api.g_date) then
2585 l_temp_offer_record.offer_shipping_date := l_offer_record.offer_shipping_date;
2586 end if;
2587 if ( l_temp_offer_record.applicant_assignment_id = hr_api.g_number) then
2588 l_temp_offer_record.applicant_assignment_id := l_offer_record.applicant_assignment_id;
2589 end if;
2590 if ( l_temp_offer_record.offer_assignment_id = hr_api.g_number) then
2591 l_temp_offer_record.offer_assignment_id := l_offer_record.offer_assignment_id;
2592 end if;
2593 if ( l_temp_offer_record.address_id = hr_api.g_number) then
2594 l_temp_offer_record.address_id := l_offer_record.address_id;
2595 end if;
2596 if ( l_temp_offer_record.template_id = hr_api.g_number) then
2597 l_temp_offer_record.template_id := l_offer_record.template_id;
2598 end if;
2599 if ( l_temp_offer_record.offer_letter_file_type = hr_api.g_varchar2) then
2600 l_temp_offer_record.offer_letter_file_type := l_offer_record.offer_letter_file_type;
2601 end if;
2602 if ( l_temp_offer_record.offer_letter_file_name = hr_api.g_varchar2) then
2603 l_temp_offer_record.offer_letter_file_name := l_offer_record.offer_letter_file_name;
2604 end if;
2605 if ( l_temp_offer_record.attribute_category = hr_api.g_varchar2) then
2606 l_temp_offer_record.attribute_category := l_offer_record.attribute_category;
2607 end if;
2608 if ( l_temp_offer_record.attribute1 = hr_api.g_varchar2) then
2609 l_temp_offer_record.attribute1 := l_offer_record.attribute1;
2610 end if;
2611 if ( l_temp_offer_record.attribute2 = hr_api.g_varchar2) then
2612 l_temp_offer_record.attribute2 := l_offer_record.attribute2;
2613 end if;
2614 if ( l_temp_offer_record.attribute3 = hr_api.g_varchar2) then
2615 l_temp_offer_record.attribute3 := l_offer_record.attribute3;
2616 end if;
2617 if ( l_temp_offer_record.attribute4 = hr_api.g_varchar2) then
2618 l_temp_offer_record.attribute4 := l_offer_record.attribute4;
2619 end if;
2620 if ( l_temp_offer_record.attribute5 = hr_api.g_varchar2) then
2621 l_temp_offer_record.attribute5 := l_offer_record.attribute5;
2622 end if;
2623 if ( l_temp_offer_record.attribute6 = hr_api.g_varchar2) then
2624 l_temp_offer_record.attribute6 := l_offer_record.attribute6;
2625 end if;
2626 if ( l_temp_offer_record.attribute7 = hr_api.g_varchar2) then
2627 l_temp_offer_record.attribute7 := l_offer_record.attribute7;
2628 end if;
2629 if ( l_temp_offer_record.attribute8 = hr_api.g_varchar2) then
2630 l_temp_offer_record.attribute8 := l_offer_record.attribute8;
2631 end if;
2632 if ( l_temp_offer_record.attribute9 = hr_api.g_varchar2) then
2633 l_temp_offer_record.attribute9 := l_offer_record.attribute9;
2634 end if;
2635 if ( l_temp_offer_record.attribute10 = hr_api.g_varchar2) then
2636 l_temp_offer_record.attribute10 := l_offer_record.attribute10;
2637 end if;
2638 if ( l_temp_offer_record.attribute11 = hr_api.g_varchar2) then
2639 l_temp_offer_record.attribute11 := l_offer_record.attribute11;
2640 end if;
2641 if ( l_temp_offer_record.attribute12 = hr_api.g_varchar2) then
2642 l_temp_offer_record.attribute12 := l_offer_record.attribute12;
2643 end if;
2644 if ( l_temp_offer_record.attribute13 = hr_api.g_varchar2) then
2645 l_temp_offer_record.attribute13 := l_offer_record.attribute13;
2646 end if;
2647 if ( l_temp_offer_record.attribute14 = hr_api.g_varchar2) then
2648 l_temp_offer_record.attribute14 := l_offer_record.attribute14;
2649 end if;
2650 if ( l_temp_offer_record.attribute15 = hr_api.g_varchar2) then
2651 l_temp_offer_record.attribute15 := l_offer_record.attribute15;
2652 end if;
2653 if ( l_temp_offer_record.attribute16 = hr_api.g_varchar2) then
2654 l_temp_offer_record.attribute16 := l_offer_record.attribute16;
2655 end if;
2656 if ( l_temp_offer_record.attribute17 = hr_api.g_varchar2) then
2657 l_temp_offer_record.attribute17 := l_offer_record.attribute17;
2658 end if;
2659 if ( l_temp_offer_record.attribute18 = hr_api.g_varchar2) then
2660 l_temp_offer_record.attribute18 := l_offer_record.attribute18;
2661 end if;
2662 if ( l_temp_offer_record.attribute19 = hr_api.g_varchar2) then
2663 l_temp_offer_record.attribute19 := l_offer_record.attribute19;
2664 end if;
2665 if ( l_temp_offer_record.attribute20 = hr_api.g_varchar2) then
2666 l_temp_offer_record.attribute20 := l_offer_record.attribute20;
2667 end if;
2668 if ( l_temp_offer_record.attribute21 = hr_api.g_varchar2) then
2669 l_temp_offer_record.attribute21 := l_offer_record.attribute21;
2670 end if;
2671 if ( l_temp_offer_record.attribute22 = hr_api.g_varchar2) then
2672 l_temp_offer_record.attribute22 := l_offer_record.attribute22;
2673 end if;
2674 if ( l_temp_offer_record.attribute23 = hr_api.g_varchar2) then
2675 l_temp_offer_record.attribute23 := l_offer_record.attribute23;
2676 end if;
2677 if ( l_temp_offer_record.attribute24 = hr_api.g_varchar2) then
2678 l_temp_offer_record.attribute24 := l_offer_record.attribute24;
2679 end if;
2680 if ( l_temp_offer_record.attribute25 = hr_api.g_varchar2) then
2681 l_temp_offer_record.attribute25 := l_offer_record.attribute25;
2682 end if;
2683 if ( l_temp_offer_record.attribute26 = hr_api.g_varchar2) then
2684 l_temp_offer_record.attribute26 := l_offer_record.attribute26;
2685 end if;
2686 if ( l_temp_offer_record.attribute27 = hr_api.g_varchar2) then
2687 l_temp_offer_record.attribute27 := l_offer_record.attribute27;
2688 end if;
2689 if ( l_temp_offer_record.attribute28 = hr_api.g_varchar2) then
2690 l_temp_offer_record.attribute28 := l_offer_record.attribute28;
2691 end if;
2692 if ( l_temp_offer_record.attribute29 = hr_api.g_varchar2) then
2693 l_temp_offer_record.attribute29 := l_offer_record.attribute29;
2694 end if;
2695 if ( l_temp_offer_record.attribute30 = hr_api.g_varchar2) then
2696 l_temp_offer_record.attribute30 := l_offer_record.attribute30;
2697 end if;
2698 --
2699 -- Create a new offer assignment copied from prev offer assignment.
2700 --
2701 hr_utility.set_location(l_proc,77);
2702 --
2703 create_offer_assignment_copy
2704 ( p_validate => p_validate
2705 , p_effective_date => l_effective_date
2706 , p_source_assignment_id => l_prev_offer_assignment_id
2707 , p_offer_assignment_id => l_offer_assignment_id
2708 );
2709 --
2710 -- Create a new offer version.
2711 --
2712 create_offer
2713 (p_validate => p_validate
2714 ,p_effective_date => l_effective_date
2715 ,p_offer_status => 'SAVED'
2716 ,p_discretionary_job_title => l_temp_offer_record.discretionary_job_title
2717 ,p_offer_extended_method => l_temp_offer_record.offer_extended_method
2718 ,p_respondent_id => l_temp_offer_record.respondent_id
2719 ,p_expiry_date => l_temp_offer_record.expiry_date
2720 ,p_proposed_start_date => l_temp_offer_record.proposed_start_date
2721 ,p_offer_letter_tracking_code => l_temp_offer_record.offer_letter_tracking_code
2722 ,p_offer_postal_service => l_temp_offer_record.offer_postal_service
2723 ,p_offer_shipping_date => l_temp_offer_record.offer_shipping_date
2724 ,p_applicant_assignment_id => l_temp_offer_record.applicant_assignment_id
2725 ,p_address_id => l_temp_offer_record.address_id
2726 ,p_template_id => l_temp_offer_record.template_id
2727 ,p_offer_letter_file_type => l_temp_offer_record.offer_letter_file_type
2728 ,p_offer_letter_file_name => l_temp_offer_record.offer_letter_file_name
2729 ,p_attribute_category => l_temp_offer_record.attribute_category
2730 ,p_attribute1 => l_temp_offer_record.attribute1
2731 ,p_attribute2 => l_temp_offer_record.attribute2
2732 ,p_attribute3 => l_temp_offer_record.attribute3
2733 ,p_attribute4 => l_temp_offer_record.attribute4
2734 ,p_attribute5 => l_temp_offer_record.attribute5
2735 ,p_attribute6 => l_temp_offer_record.attribute6
2736 ,p_attribute7 => l_temp_offer_record.attribute7
2737 ,p_attribute8 => l_temp_offer_record.attribute8
2738 ,p_attribute9 => l_temp_offer_record.attribute9
2739 ,p_attribute10 => l_temp_offer_record.attribute10
2740 ,p_attribute11 => l_temp_offer_record.attribute11
2741 ,p_attribute12 => l_temp_offer_record.attribute12
2742 ,p_attribute13 => l_temp_offer_record.attribute13
2743 ,p_attribute14 => l_temp_offer_record.attribute14
2744 ,p_attribute15 => l_temp_offer_record.attribute15
2745 ,p_attribute16 => l_temp_offer_record.attribute16
2746 ,p_attribute17 => l_temp_offer_record.attribute17
2747 ,p_attribute18 => l_temp_offer_record.attribute18
2748 ,p_attribute19 => l_temp_offer_record.attribute19
2749 ,p_attribute20 => l_temp_offer_record.attribute20
2750 ,p_attribute21 => l_temp_offer_record.attribute21
2751 ,p_attribute22 => l_temp_offer_record.attribute22
2752 ,p_attribute23 => l_temp_offer_record.attribute23
2753 ,p_attribute24 => l_temp_offer_record.attribute24
2754 ,p_attribute25 => l_temp_offer_record.attribute25
2755 ,p_attribute26 => l_temp_offer_record.attribute26
2756 ,p_attribute27 => l_temp_offer_record.attribute27
2757 ,p_attribute28 => l_temp_offer_record.attribute28
2758 ,p_attribute29 => l_temp_offer_record.attribute29
2759 ,p_attribute30 => l_temp_offer_record.attribute30
2760 ,p_status_change_date => l_status_change_date
2761 ,p_offer_assignment_id => l_offer_assignment_id
2762 ,p_offer_id => l_offer_id
2763 ,p_offer_version => l_offer_version
2764 ,p_object_version_number => l_object_version_number
2765 );
2766 --
2767 hr_utility.set_location(' Leaving:'||l_proc, 78);
2768 --
2769 else -- l_create_new_version = false
2770 --
2771 -- Proceed with normal Update.
2772 --
2773 begin
2774 IRC_OFFERS_BK2.update_offer_b
2775 ( P_EFFECTIVE_DATE => l_effective_date
2776 , P_OFFER_ID => P_OFFER_ID
2777 , P_LATEST_OFFER => l_latest_offer
2778 , P_OFFER_STATUS => l_offer_status
2779 , P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE
2780 , P_OFFER_EXTENDED_METHOD => P_OFFER_EXTENDED_METHOD
2781 , P_RESPONDENT_ID => P_RESPONDENT_ID
2782 , P_EXPIRY_DATE => l_expiry_date
2783 , P_PROPOSED_START_DATE => l_proposed_start_date
2784 , P_OFFER_LETTER_TRACKING_CODE => P_OFFER_LETTER_TRACKING_CODE
2785 , P_OFFER_POSTAL_SERVICE => P_OFFER_POSTAL_SERVICE
2786 , P_OFFER_SHIPPING_DATE => l_offer_shipping_date
2787 , P_APPLICANT_ASSIGNMENT_ID => P_APPLICANT_ASSIGNMENT_ID
2788 , P_OFFER_ASSIGNMENT_ID => P_OFFER_ASSIGNMENT_ID
2789 , P_ADDRESS_ID => P_ADDRESS_ID
2790 , P_TEMPLATE_ID => P_TEMPLATE_ID
2791 , P_OFFER_LETTER_FILE_TYPE => P_OFFER_LETTER_FILE_TYPE
2792 , P_OFFER_LETTER_FILE_NAME => P_OFFER_LETTER_FILE_NAME
2793 , P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
2794 , P_ATTRIBUTE1 => P_ATTRIBUTE1
2795 , P_ATTRIBUTE2 => P_ATTRIBUTE2
2796 , P_ATTRIBUTE3 => P_ATTRIBUTE3
2797 , P_ATTRIBUTE4 => P_ATTRIBUTE4
2798 , P_ATTRIBUTE5 => P_ATTRIBUTE5
2799 , P_ATTRIBUTE6 => P_ATTRIBUTE6
2800 , P_ATTRIBUTE7 => P_ATTRIBUTE7
2801 , P_ATTRIBUTE8 => P_ATTRIBUTE8
2802 , P_ATTRIBUTE9 => P_ATTRIBUTE9
2803 , P_ATTRIBUTE10 => P_ATTRIBUTE10
2804 , P_ATTRIBUTE11 => P_ATTRIBUTE11
2805 , P_ATTRIBUTE12 => P_ATTRIBUTE12
2806 , P_ATTRIBUTE13 => P_ATTRIBUTE13
2807 , P_ATTRIBUTE14 => P_ATTRIBUTE14
2808 , P_ATTRIBUTE15 => P_ATTRIBUTE15
2809 , P_ATTRIBUTE16 => P_ATTRIBUTE16
2810 , P_ATTRIBUTE17 => P_ATTRIBUTE17
2811 , P_ATTRIBUTE18 => P_ATTRIBUTE18
2812 , P_ATTRIBUTE19 => P_ATTRIBUTE19
2813 , P_ATTRIBUTE20 => P_ATTRIBUTE20
2814 , P_ATTRIBUTE21 => P_ATTRIBUTE21
2815 , P_ATTRIBUTE22 => P_ATTRIBUTE22
2816 , P_ATTRIBUTE23 => P_ATTRIBUTE23
2817 , P_ATTRIBUTE24 => P_ATTRIBUTE24
2818 , P_ATTRIBUTE25 => P_ATTRIBUTE25
2819 , P_ATTRIBUTE26 => P_ATTRIBUTE26
2820 , P_ATTRIBUTE27 => P_ATTRIBUTE27
2821 , P_ATTRIBUTE28 => P_ATTRIBUTE28
2822 , P_ATTRIBUTE29 => P_ATTRIBUTE29
2823 , P_ATTRIBUTE30 => P_ATTRIBUTE30
2824 , P_OBJECT_VERSION_NUMBER => p_object_version_number
2825 );
2826 exception
2827 when hr_api.cannot_find_prog_unit then
2828 hr_api.cannot_find_prog_unit_error
2829 (p_module_name => 'update_offer'
2830 ,p_hook_type => 'BP'
2831 );
2832 end;
2833 --
2834 -- Validation in addition to Row Handlers
2835 --
2836 -- Process Logic
2837 --
2838 l_object_version_number := p_object_version_number;
2839 l_offer_id := p_offer_id;
2840 --
2841 -- Check if offer_status is 'EXTENDED'
2842 --
2843 if l_offer_status = 'EXTENDED'
2844 then
2845 --
2846 if 'N' = fnd_profile.value('IRC_ALLOW_MULTI_OFFERS')
2847 OR 'E' = fnd_profile.value('IRC_ALLOW_MULTI_OFFERS')
2848 OR fnd_profile.value('IRC_ALLOW_MULTI_OFFERS') is null
2849 then
2850 --
2851 other_extended_offers_count
2852 ( p_applicant_assignment_id => l_prev_applicant_assignment_id
2853 ,p_effective_date => p_effective_date
2854 ,p_other_extended_offer_count => l_other_extended_offer_count
2855 );
2856 --
2857 if l_other_extended_offer_count > 0
2858 then
2859 --
2860 -- This happens when an offer is being EXTENDED. Throw an error refusing to
2861 -- EXTEND another offer to the candidate
2862 --
2863 fnd_message.set_name('PER','IRC_412377_MULTIPLE_OFFER_SEND'); -- You cannot send another offer to this applicant as
2864 -- an active offer exists for this application
2865 fnd_message.raise_error;
2866 --
2867 end if;
2868 end if;
2869 --
2870 -- check if the Previous status is CLOSED
2871 -- and handle the situation where the PENDING_EXTENDED gets Approved
2872 -- but the Offer has already EXPIRED
2873 if l_prev_offer_status = 'CLOSED' then
2874 -- get the last Offer Status History change record
2875 open csr_offer_status_history_id;
2876 fetch csr_offer_status_history_id into l_offer_status_history_id;
2877 close csr_offer_status_history_id;
2878
2879 if l_offer_status_history_id is not null then
2880 -- get the Change Reason for last Offer Status Change
2881 open csr_offer_status_history_dets(l_offer_status_history_id);
2882 fetch csr_offer_status_history_dets into l_prev_offer_change_reason;
2883 close csr_offer_status_history_dets;
2884 -- check if this Offer has been closed EARLIER due to EXPIRY
2885 if l_prev_offer_change_reason = 'EXPIRED' then
2886 -- get the old OVN and set it as NEW
2887 l_object_version_number := l_prev_offer_ovn;
2888 end if;
2889 end if;
2890 end if;
2891 end if;
2892 --
2893 -- Check if offer_status is 'CLOSED'
2894 --
2895 if ( l_offer_status = 'CLOSED'
2896 ) then
2897 --
2898 -- Check that a closure reason is provided before closing the offer
2899 --
2900 if p_change_reason is null
2901 then
2902 --
2903 fnd_message.set_name('PER','IRC_412299_CLOSE_REASON_MNDTRY');
2904 fnd_message.raise_error;
2905 --
2906 end if;
2907 --
2908 -- Check if the change_reason is 'WITHDRAWAL'. If yes, close
2909 -- the offer irrespective of the current offer status.
2910 -- Else, check for the current offer_status.
2911 --
2912 hr_utility.set_location(l_proc,100);
2913 --
2914 if (( p_change_reason = 'EXPIRED'
2915 OR p_change_reason = 'APL_DECLINED'
2916 OR p_change_reason = 'APL_ACCEPTED'
2917 )
2918 AND (l_prev_offer_status <> 'EXTENDED'
2919 AND l_prev_offer_status <> 'HOLD'
2920 AND l_prev_offer_status <> 'PENDING_EXTENDED')
2921 )
2922 then
2923 --
2924 hr_utility.set_location(l_proc,105);
2925 --
2926 fnd_message.set_name('PER','IRC_412300_OFFER_NOT_EXTENDED');
2927 fnd_message.raise_error;
2928 --
2929 end if;
2930 --
2931 if ( p_change_reason <> 'WITHDRAWAL'
2932 OR p_change_reason is null -- Null check
2933 ) then
2934 --
2935 hr_utility.set_location(l_proc,110);
2936 --
2937 -- Check to see if the offer status is Closed. If yes,
2938 -- throw an error saying that the offer cannot be closed.
2939 --
2940 if ( l_prev_offer_status = 'CLOSED'
2941 ) then
2942 --
2943 hr_utility.set_location(l_proc,120);
2944 --
2945 -- Close offer can be called on a closed offer with change
2946 -- reason APL_DECLINED_ACCEPTANCE, MGR_WITHDRAW, APL_HIRED
2947 --
2948 if p_change_reason<>'APL_DECLINED_ACCEPTANCE' and p_change_reason<>'MGR_WITHDRAW' and p_change_reason<>'APL_HIRED' then
2949 fnd_message.set_name('PER','IRC_412301_OFR_CANT_BE_CLOSED');
2950 fnd_message.raise_error;
2951 end if;
2952 end if;
2953 end if;
2954 --
2955 -- If the Applicant has Accepted the offer, copy the offer assignment details into
2956 -- applicant assignment record and copy the details from pay proposal record for the
2957 -- offer assignment to the pay proposal record for applicant assignment.
2958 --
2959 if ( p_change_reason = 'APL_ACCEPTED' )
2960 then
2961 --
2962 copy_offer_asg_to_appl_asg
2963 ( p_offer_id => P_OFFER_ID
2964 ,p_effective_date => p_effective_date
2965 ,p_validate => p_validate
2966 );
2967 --
2968 copy_offer_pay_to_appl_pay
2969 ( p_validate => p_validate
2970 ,p_offer_id => P_offer_id
2971 ,p_effective_date => p_effective_date
2972 );
2973 --
2974 end if;
2975 end if;
2976 --
2977 -- Check if the offer has been approved from PENDING_EXTENDED status
2978 -- If no other field has been modified, set the offer_status to EXTENDED.
2979 --
2980 if l_prev_offer_status = 'PENDING_EXTENDED'
2981 and l_offer_status = 'EXTENDED'
2982 then
2983 --
2984 hr_utility.set_location(l_proc,122);
2985 --
2986 -- pass in old status PENDING_EXTENDED to this method
2987 irc_iof_bus.chk_multiple_fields_updated
2988 ( p_mutiple_fields_updated => l_mutiple_fields_updated
2989 ,p_offer_id => p_offer_id
2990 ,p_offer_status => l_prev_offer_status
2991 ,p_discretionary_job_title => p_discretionary_job_title
2992 ,p_offer_extended_method => p_offer_extended_method
2993 ,p_expiry_date => l_expiry_date
2994 ,p_proposed_start_date => p_proposed_start_date
2995 ,p_offer_letter_tracking_code => p_offer_letter_tracking_code
2996 ,p_offer_postal_service => p_offer_postal_service
2997 ,p_offer_shipping_date => p_offer_shipping_date
2998 ,p_applicant_assignment_id => p_applicant_assignment_id
2999 ,p_offer_assignment_id => p_offer_assignment_id
3000 ,p_address_id => p_address_id
3001 ,p_template_id => p_template_id
3002 ,p_offer_letter_file_type => p_offer_letter_file_type
3003 ,p_offer_letter_file_name => p_offer_letter_file_name
3004 ,p_attribute_category => p_attribute_category
3005 ,p_attribute1 => p_attribute1
3006 ,p_attribute2 => p_attribute2
3007 ,p_attribute3 => p_attribute3
3008 ,p_attribute4 => p_attribute4
3009 ,p_attribute5 => p_attribute5
3010 ,p_attribute6 => p_attribute6
3011 ,p_attribute7 => p_attribute7
3012 ,p_attribute8 => p_attribute8
3013 ,p_attribute9 => p_attribute9
3014 ,p_attribute10 => p_attribute10
3015 ,p_attribute11 => p_attribute11
3016 ,p_attribute12 => p_attribute12
3017 ,p_attribute13 => p_attribute13
3018 ,p_attribute14 => p_attribute14
3019 ,p_attribute15 => p_attribute15
3020 ,p_attribute16 => p_attribute16
3021 ,p_attribute17 => p_attribute17
3022 ,p_attribute18 => p_attribute18
3023 ,p_attribute19 => p_attribute19
3024 ,p_attribute20 => p_attribute20
3025 ,p_attribute21 => p_attribute21
3026 ,p_attribute22 => p_attribute22
3027 ,p_attribute23 => p_attribute23
3028 ,p_attribute24 => p_attribute24
3029 ,p_attribute25 => p_attribute25
3030 ,p_attribute26 => p_attribute26
3031 ,p_attribute27 => p_attribute27
3032 ,p_attribute28 => p_attribute28
3033 ,p_attribute29 => p_attribute29
3034 ,p_attribute30 => p_attribute30
3035 );
3036 --
3037 if ( l_mutiple_fields_updated = false )
3038 then
3039 --
3040 hr_utility.set_location(l_proc,125);
3041 --
3042 l_offer_status := 'EXTENDED';
3043 --
3044 else
3045 --
3046 fnd_message.set_name('PER','IRC_412302_PNDNG_EXTNDD_MODFD');
3047 fnd_message.raise_error;
3048 --
3049 end if;
3050 --
3051 end if;
3052
3053 hr_utility.set_location(l_proc,130);
3054 --
3055 irc_iof_upd.upd
3056 ( p_effective_date => l_effective_date
3057 , p_offer_id => l_offer_id
3058 , p_object_version_number => l_object_version_number
3059 , p_offer_version => l_offer_version
3060 , p_latest_offer => l_latest_offer
3061 , p_applicant_assignment_id => p_applicant_assignment_id
3062 , p_offer_assignment_id => p_offer_assignment_id
3063 , p_offer_status => l_offer_status
3064 , p_discretionary_job_title => p_discretionary_job_title
3065 , p_offer_extended_method => p_offer_extended_method
3066 , p_respondent_id => p_respondent_id
3067 , p_expiry_date => l_expiry_date
3068 , p_proposed_start_date => l_proposed_start_date
3069 , p_offer_letter_tracking_code => p_offer_letter_tracking_code
3070 , p_offer_postal_service => p_offer_postal_service
3071 , p_offer_shipping_date => l_offer_shipping_date
3072 , p_address_id => p_address_id
3073 , p_template_id => p_template_id
3074 , p_offer_letter_file_type => p_offer_letter_file_type
3075 , p_offer_letter_file_name => p_offer_letter_file_name
3076 , p_attribute_category => p_attribute_category
3077 , p_attribute1 => p_attribute1
3078 , p_attribute2 => p_attribute2
3079 , p_attribute3 => p_attribute3
3080 , p_attribute4 => p_attribute4
3081 , p_attribute5 => p_attribute5
3082 , p_attribute6 => p_attribute6
3083 , p_attribute7 => p_attribute7
3084 , p_attribute8 => p_attribute8
3085 , p_attribute9 => p_attribute9
3086 , p_attribute10 => p_attribute10
3087 , p_attribute11 => p_attribute11
3088 , p_attribute12 => p_attribute12
3089 , p_attribute13 => p_attribute13
3090 , p_attribute14 => p_attribute14
3091 , p_attribute15 => p_attribute15
3092 , p_attribute16 => p_attribute16
3093 , p_attribute17 => p_attribute17
3094 , p_attribute18 => p_attribute18
3095 , p_attribute19 => p_attribute19
3096 , p_attribute20 => p_attribute20
3097 , p_attribute21 => p_attribute21
3098 , p_attribute22 => p_attribute22
3099 , p_attribute23 => p_attribute23
3100 , p_attribute24 => p_attribute24
3101 , p_attribute25 => p_attribute25
3102 , p_attribute26 => p_attribute26
3103 , p_attribute27 => p_attribute27
3104 , p_attribute28 => p_attribute28
3105 , p_attribute29 => p_attribute29
3106 , p_attribute30 => p_attribute30
3107 );
3108
3109 --
3110 -- Get the previous change reason
3111 --
3112 if l_offer_status = 'CLOSED' then
3113 -- get the last Offer Status History change record
3114 open csr_offer_status_history_id;
3115 fetch csr_offer_status_history_id into l_offer_status_history_id;
3116 close csr_offer_status_history_id;
3117 --
3118 if l_offer_status_history_id is not null then
3119 -- get the Change Reason for last Offer Status Change
3120 open csr_offer_status_history_dets(l_offer_status_history_id);
3121 fetch csr_offer_status_history_dets into l_prev_offer_change_reason;
3122 close csr_offer_status_history_dets;
3123 end if;
3124 end if;
3125
3126 --
3127 -- Create offer history record. Call this in the following scenarios:
3128 -- 1) Status has changed
3129 -- 2) The offer duration has been extended and the offer is in EXTENDED status.
3130 -- 3) The offer status change reason has changed.
3131 --
3132 if (l_offer_status <> l_prev_offer_status) OR
3133 (l_offer_status = 'EXTENDED' AND
3134 (nvl(l_expiry_date,hr_api.g_date) <> nvl(l_prev_expiry_date,hr_api.g_date))
3135 ) OR
3136 (l_prev_offer_change_reason <> p_change_reason)
3137 then
3138 --
3139 -- IF any one of offer_letter_tracking_code or offer_postal_service or
3140 -- offer_shipping_date is set, add an entry in status history table only if
3141 -- expiry_date has been manually set.
3142 --
3143 if not
3144 (
3145 (
3146 (
3147 ((l_offer_status = 'EXTENDED') or (l_offer_status = hr_api.g_varchar2))
3148 and (nvl(p_offer_letter_tracking_code,hr_api.g_varchar2) <> nvl(l_offer_letter_tracking_code,hr_api.g_varchar2))
3149 and p_offer_letter_tracking_code <> hr_api.g_varchar2
3150 )
3151 or (
3152 ((l_offer_status = 'EXTENDED') or (l_offer_status = hr_api.g_varchar2))
3153 and (nvl(p_offer_postal_service,hr_api.g_varchar2) <> nvl(l_offer_postal_service,hr_api.g_varchar2))
3154 and p_offer_postal_service <> hr_api.g_varchar2
3155 )
3156 or (
3157 ((l_offer_status = 'EXTENDED') or (l_offer_status = hr_api.g_varchar2))
3158 and (nvl(l_offer_shipping_date,hr_api.g_date) <> nvl(l_prev_offer_shipping_date,hr_api.g_date))
3159 and l_offer_shipping_date <> hr_api.g_date
3160 )
3161 )
3162 and
3163 (l_expiry_date = hr_api.g_date)
3164 )
3165 then
3166 --
3167 hr_utility.set_location(l_proc,140);
3168 --
3169 irc_offer_status_history_api.create_offer_status_history
3170 ( p_validate => p_validate
3171 , p_effective_date => l_effective_date
3172 , p_offer_status_history_id => l_offer_status_history_id
3173 , p_offer_id => p_offer_id
3174 , p_offer_status => l_offer_status
3175 , p_change_reason => p_change_reason
3176 , p_decline_reason => p_decline_reason
3177 , p_note_text => p_note_text
3178 , p_object_version_number => l_osh_object_version_number
3179 , p_status_change_date => l_status_change_date
3180 );
3181 --
3182 -- Also, if the offer status has changed, call update_appl_assg_status
3183 -- to set the appropriate Assignment Status for the application.
3184 --
3185 hr_utility.set_location(l_proc,141);
3186 --
3187 irc_offers_api.update_appl_assg_status
3188 ( p_validate => p_validate
3189 , p_effective_date => l_effective_date
3190 , p_applicant_assignment_id => l_prev_applicant_assignment_id
3191 , p_offer_status => l_offer_status
3192 , p_change_reason => p_change_reason
3193 );
3194 --
3195 -- Also, end date the offer assignment if the offer has just been closed.
3196 -- If the offer has already been closed, no need to end date the offer
3197 -- assignment again
3198 if l_offer_status = 'CLOSED' and l_prev_offer_status<>'CLOSED'
3199 then
3200 --
3201 hr_utility.set_location(l_proc,142);
3202 --
3203 if trunc(l_effective_end_date) = hr_api.g_eot then
3204 --
3205 hr_utility.set_location('Delete the offer assignment ',143);
3206 per_asg_del.del
3207 (
3208 p_assignment_id => l_prev_offer_assignment_id
3209 ,p_effective_start_date => l_effective_start_date
3210 ,p_effective_end_date => l_effective_end_date
3211 ,p_business_group_id => l_business_group_id
3212 ,p_object_version_number => l_asg_object_version_number
3213 ,p_effective_date => l_effective_date
3214 ,p_validation_start_date => l_validation_start_date
3215 ,p_validation_end_date => l_validation_end_date
3216 ,p_datetrack_mode => hr_api.g_delete
3217 ,p_validate => p_validate
3218 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3219 );
3220 hr_utility.set_location('After deleting the offer assignment ',144);
3221 --
3222 else
3223 --
3224 hr_utility.set_location(l_proc,145);
3225 hr_utility.set_location('Offer assignment is not active ',146);
3226 --
3227 end if;
3228 end if;
3229 --
3230 --
3231 --Check if the offer has been Approved or Returned For Correction or
3232 --Extended when it was on HOLD, if so, place the offer on HOLD again.
3233 --
3234 if ( l_prev_offer_status ='HOLD'
3235 and ( (l_offer_status = 'CORRECTION')
3236 or (l_offer_status = 'APPROVED')
3237 or (l_offer_status = 'EXTENDED')
3238 )
3239 )
3240 then
3241 hold_offer
3242 (P_VALIDATE => p_validate
3243 ,P_EFFECTIVE_DATE => l_effective_date
3244 ,P_OFFER_ID => p_offer_id
3245 ,P_RESPONDENT_ID => p_respondent_id
3246 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
3247 );
3248 end if;
3249 --
3250 end if;
3251 --
3252 elsif l_offer_status = l_prev_offer_status
3253 then
3254 --
3255 -- If the offer status has not changed, we need to call
3256 -- update_offer_status_history to update the note text.
3257 --
3258 open csr_offer_status_history_id;
3259 fetch csr_offer_status_history_id into l_offer_status_history_id;
3260 --
3261 if csr_offer_status_history_id%notfound
3262 then
3263 --
3264 close csr_offer_status_history_id;
3265 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
3266 fnd_message.raise_error;
3267 --
3268 end if;
3269 close csr_offer_status_history_id;
3270 --
3271 irc_offer_status_history_api.update_offer_status_history
3272 ( p_validate => p_validate
3273 , p_effective_date => l_effective_date
3274 , p_offer_status_history_id => l_offer_status_history_id
3275 , p_offer_id => p_offer_id
3276 , p_offer_status => l_offer_status
3277 , p_change_reason => p_change_reason
3278 , p_decline_reason => p_decline_reason
3279 , p_note_text => p_note_text
3280 , p_object_version_number => l_osh_object_version_number
3281 , p_status_change_date => l_status_change_date
3282 );
3283 --
3284 end if;
3285 --
3286 -- Call After Process User Hook
3287 --
3288 begin
3289 IRC_OFFERS_BK2.update_offer_a
3290 ( P_EFFECTIVE_DATE => l_effective_date
3291 , P_OFFER_ID => P_OFFER_ID
3292 , P_OFFER_VERSION => l_offer_version
3293 , P_LATEST_OFFER => l_latest_offer
3294 , P_OFFER_STATUS => l_offer_status
3295 , P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE
3296 , P_OFFER_EXTENDED_METHOD => P_OFFER_EXTENDED_METHOD
3297 , P_RESPONDENT_ID => P_RESPONDENT_ID
3298 , P_EXPIRY_DATE => l_expiry_date
3299 , P_PROPOSED_START_DATE => l_proposed_start_date
3300 , P_OFFER_LETTER_TRACKING_CODE => P_OFFER_LETTER_TRACKING_CODE
3301 , P_OFFER_POSTAL_SERVICE => P_OFFER_POSTAL_SERVICE
3302 , P_OFFER_SHIPPING_DATE => l_offer_shipping_date
3303 , P_APPLICANT_ASSIGNMENT_ID => P_APPLICANT_ASSIGNMENT_ID
3304 , P_OFFER_ASSIGNMENT_ID => P_OFFER_ASSIGNMENT_ID
3305 , P_ADDRESS_ID => P_ADDRESS_ID
3306 , P_TEMPLATE_ID => P_TEMPLATE_ID
3307 , P_OFFER_LETTER_FILE_TYPE => P_OFFER_LETTER_FILE_TYPE
3308 , P_OFFER_LETTER_FILE_NAME => P_OFFER_LETTER_FILE_NAME
3309 , P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
3310 , P_ATTRIBUTE1 => P_ATTRIBUTE1
3311 , P_ATTRIBUTE2 => P_ATTRIBUTE2
3312 , P_ATTRIBUTE3 => P_ATTRIBUTE3
3313 , P_ATTRIBUTE4 => P_ATTRIBUTE4
3314 , P_ATTRIBUTE5 => P_ATTRIBUTE5
3315 , P_ATTRIBUTE6 => P_ATTRIBUTE6
3316 , P_ATTRIBUTE7 => P_ATTRIBUTE7
3317 , P_ATTRIBUTE8 => P_ATTRIBUTE8
3318 , P_ATTRIBUTE9 => P_ATTRIBUTE9
3319 , P_ATTRIBUTE10 => P_ATTRIBUTE10
3320 , P_ATTRIBUTE11 => P_ATTRIBUTE11
3321 , P_ATTRIBUTE12 => P_ATTRIBUTE12
3322 , P_ATTRIBUTE13 => P_ATTRIBUTE13
3323 , P_ATTRIBUTE14 => P_ATTRIBUTE14
3324 , P_ATTRIBUTE15 => P_ATTRIBUTE15
3325 , P_ATTRIBUTE16 => P_ATTRIBUTE16
3326 , P_ATTRIBUTE17 => P_ATTRIBUTE17
3327 , P_ATTRIBUTE18 => P_ATTRIBUTE18
3328 , P_ATTRIBUTE19 => P_ATTRIBUTE19
3329 , P_ATTRIBUTE20 => P_ATTRIBUTE20
3330 , P_ATTRIBUTE21 => P_ATTRIBUTE21
3331 , P_ATTRIBUTE22 => P_ATTRIBUTE22
3332 , P_ATTRIBUTE23 => P_ATTRIBUTE23
3333 , P_ATTRIBUTE24 => P_ATTRIBUTE24
3334 , P_ATTRIBUTE25 => P_ATTRIBUTE25
3335 , P_ATTRIBUTE26 => P_ATTRIBUTE26
3336 , P_ATTRIBUTE27 => P_ATTRIBUTE27
3337 , P_ATTRIBUTE28 => P_ATTRIBUTE28
3338 , P_ATTRIBUTE29 => P_ATTRIBUTE29
3339 , P_ATTRIBUTE30 => P_ATTRIBUTE30
3340 , P_OBJECT_VERSION_NUMBER => p_object_version_number
3341 );
3342 exception
3343 when hr_api.cannot_find_prog_unit then
3344 hr_api.cannot_find_prog_unit_error
3345 (p_module_name => 'update_offer'
3346 ,p_hook_type => 'AP'
3347 );
3348 end;
3349 --
3350 -- When in validation only mode raise the Validate_Enabled exception
3351 --
3352 if p_validate then
3353 raise hr_api.validate_enabled;
3354 end if;
3355 --
3356 end if; -- l_create_new_version
3357 --
3358 -- Set all IN OUT and OUT parameters with out values
3359 -- If a new offer version has been created, the new offer_id
3360 -- will be returned, else the old one will be returned.
3361 --
3362 p_offer_id := l_offer_id;
3363 p_object_version_number := l_object_version_number;
3364 p_offer_version := l_offer_version;
3365 --
3366 hr_utility.set_location(' Leaving:'||l_proc, 145);
3367 --
3368 exception
3369 when hr_api.validate_enabled then
3370 --
3371 -- Reset IN OUT parameters and set OUT parameters
3372 -- (Any key or derived arguments must be set to null
3373 -- when validation only mode is being used.)
3374 --
3375 p_offer_id := l_offer_id;
3376 p_object_version_number := l_object_version_number;
3377 p_offer_version := l_offer_version;
3378 --
3379 -- As the Validate_Enabled exception has been raised
3380 -- we must rollback to the savepoint
3381 --
3382 rollback to UPDATE_OFFER;
3383 --
3384 hr_utility.set_location(' Leaving:'||l_proc, 150);
3385 when others then
3386 --
3387 -- Reset IN OUT parameters and set all
3388 -- OUT parameters, including warnings, to null
3389 --
3390 p_offer_id := l_offer_id;
3391 p_object_version_number := l_object_version_number;
3392 p_offer_version := l_offer_version;
3393 --
3394 -- A validation or unexpected error has occured
3395 --
3396 rollback to UPDATE_OFFER;
3397 --
3398 hr_utility.set_location(' Leaving:'||l_proc, 160);
3399 raise;
3400 end update_offer;
3401 --
3402 -- ----------------------------------------------------------------------------
3403 -- |-----------------------------< delete_offer >-----------------------------|
3404 -- ----------------------------------------------------------------------------
3405 --
3406 procedure delete_offer
3407 (
3408 P_VALIDATE in boolean default false
3409 , P_OBJECT_VERSION_NUMBER in number
3410 , P_OFFER_ID in number
3411 , P_EFFECTIVE_DATE in date default null
3412 )
3413 is
3414 --
3415 -- Declare cursors and local variables
3416 --
3417 l_proc varchar2(72) := g_package||'delete_offer';
3418 l_offer_id irc_offers.offer_id%TYPE;
3419 l_prev_offer_status irc_offers.offer_status%TYPE;
3420 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
3421 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
3422 l_effective_date date;
3423 l_validation_start_date date;
3424 l_validation_end_date date;
3425 l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3426 l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
3427 l_offer_status irc_offers.offer_status%TYPE;
3428 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3429 l_asg_object_version_number number(9);
3430 l_org_now_no_manager_warning boolean;
3431 --
3432 cursor csr_prev_offer_rec is
3433 select offer_status
3434 ,applicant_assignment_id
3435 ,offer_assignment_id
3436 from irc_offers
3437 where offer_id = p_offer_id;
3438 --
3439 l_prev_offer_rec csr_prev_offer_rec%ROWTYPE;
3440 --
3441 cursor csr_latest_offer is
3442 select offer_id
3443 ,offer_status
3444 from irc_offers
3445 where applicant_assignment_id = l_applicant_assignment_id
3446 and offer_version = ( select max(offer_version) from irc_offers
3447 where applicant_assignment_id = l_applicant_assignment_id );
3448 --
3449 cursor csr_assignment_ovn is
3450 select object_version_number
3451 from per_all_assignments_f
3452 where assignment_id = l_offer_assignment_id;
3453 --
3454 begin
3455 hr_utility.set_location('Entering:'|| l_proc, 10);
3456 --
3457 -- Issue a savepoint
3458 --
3459 savepoint DELETE_OFFER;
3460 --
3461 open csr_prev_offer_rec;
3462 fetch csr_prev_offer_rec into l_prev_offer_rec;
3463 if csr_prev_offer_rec%notfound
3464 then
3465 --
3466 close csr_prev_offer_rec;
3467 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
3468 fnd_message.raise_error;
3469 --
3470 end if;
3471 --
3472 close csr_prev_offer_rec;
3473 --
3474 -- Check to see if the current offer status is 'Saved for Later'
3475 -- or 'Returned for Correction'. If NOT, throw an error
3476 -- saying that the offer cannot be deleted.
3477 --
3478 if ( l_prev_offer_rec.offer_status <> 'SAVED'
3479 AND l_prev_offer_rec.offer_status <> 'CORRECTION'
3480 ) then
3481 --
3482 fnd_message.set_name('PER','IRC_412303_OFFER_CANT_BE_DLTD');
3483 fnd_message.raise_error;
3484 --
3485 end if;
3486 --
3487 -- Truncate the time portion from all IN date parameters
3488 --
3489 l_effective_date := trunc(p_effective_date);
3490 --
3491 -- Call Before Process User Hook
3492 --
3493 begin
3494 IRC_OFFERS_BK3.delete_offer_b
3495 (
3496 P_OBJECT_VERSION_NUMBER
3497 ,P_OFFER_ID
3498 );
3499 exception
3500 when hr_api.cannot_find_prog_unit then
3501 hr_api.cannot_find_prog_unit_error
3502 (p_module_name => 'delete_offer'
3503 ,p_hook_type => 'BP'
3504 );
3505 end;
3506 --
3507 -- Validation in addition to Row Handlers
3508 --
3509 irc_iof_del.del
3510 (
3511 p_offer_id => P_OFFER_ID
3512 ,p_object_version_number => P_OBJECT_VERSION_NUMBER
3513 );
3514 --
3515 -- Process Logic
3516 --
3517 --
3518 -- Also delete the irc_offer_status_history record.
3519 --
3520 irc_offer_status_history_api.delete_offer_status_history
3521 (
3522 p_validate => p_validate
3523 ,p_offer_id => p_offer_id
3524 ,p_effective_date => l_effective_date
3525 );
3526 --
3527 l_offer_assignment_id := l_prev_offer_rec.offer_assignment_id ;
3528 --
3529 -- get offer assignment's object_version_number
3530 --
3531 open csr_assignment_ovn;
3532 fetch csr_assignment_ovn into l_asg_object_version_number;
3533 if csr_assignment_ovn%notfound
3534 then
3535 --
3536 close csr_assignment_ovn;
3537 fnd_message.set_name('PER','HR_289541_PJU_INV_ASG_ID');
3538 fnd_message.raise_error;
3539 --
3540 end if;
3541 close csr_assignment_ovn;
3542 --
3543 -- Purge the offer assignment record
3544 --
3545 per_asg_del.del
3546 (
3547 p_assignment_id => l_offer_assignment_id
3548 ,p_effective_start_date => l_effective_start_date
3549 ,p_effective_end_date => l_effective_end_date
3550 ,p_business_group_id => l_business_group_id
3551 ,p_object_version_number => l_asg_object_version_number
3552 ,p_effective_date => l_effective_date
3553 ,p_validation_start_date => l_validation_start_date
3554 ,p_validation_end_date => l_validation_end_date
3555 ,p_datetrack_mode => hr_api.g_zap
3556 ,p_validate => p_validate
3557 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
3558 );
3559 --
3560 -- store the applicant_assignment_id
3561 --
3562 l_applicant_assignment_id := l_prev_offer_rec.applicant_assignment_id;
3563 --
3564 -- Check if there are any old offer records for this
3565 -- applicant_assignment_id. if yes, set the latest_offer
3566 -- for that record to 'Y'.
3567 --
3568 open csr_latest_offer;
3569 fetch csr_latest_offer into l_offer_id, l_offer_status;
3570 --
3571 hr_utility.set_location(l_proc,20);
3572 --
3573 if (csr_latest_offer%found)
3574 then
3575 close csr_latest_offer;
3576 --
3577 -- Update the offer record and set the latest_offer to 'Y'
3578 --
3579 if l_offer_status <> 'SAVED'
3580 and l_offer_id < p_offer_id --> Check that we are only modifying an earlier offer version
3581 then
3582 --
3583 -- If the offer_status of this offer record is SAVED,
3584 -- do not set the latest_offer flag to 'Y' because any SAVED
3585 -- offer cannot be the latest offer.
3586 -- NOTE: This scenario will not occur unless the offer creation is manupulated.
3587 -- Eg. First creating a SAVED offer as offer_version = 1 and again
3588 -- calling create_offer to create a PENDING offer as offer_version = 2.
3589 --
3590 update_latest_offer_flag
3591 ( p_offer_id => l_offer_id
3592 ,p_effective_date => l_effective_date
3593 ,p_validate => p_validate
3594 ,p_latest_offer => 'Y'
3595 );
3596 --
3597 end if;
3598 else
3599 close csr_latest_offer;
3600 end if;
3601 --
3602 -- Call After Process User Hook
3603 --
3604 begin
3605 IRC_OFFERS_BK3.delete_offer_a
3606 (
3607 P_OBJECT_VERSION_NUMBER
3608 ,P_OFFER_ID
3609 );
3610 exception
3611 when hr_api.cannot_find_prog_unit then
3612 hr_api.cannot_find_prog_unit_error
3613 (p_module_name => 'delete_offer'
3614 ,p_hook_type => 'AP'
3615 );
3616 end;
3617 --
3618 -- When in validation only mode raise the Validate_Enabled exception
3619 --
3620 if p_validate then
3621 raise hr_api.validate_enabled;
3622 end if;
3623 --
3624 -- Set all output arguments
3625 --
3626 --
3627 hr_utility.set_location(' Leaving:'||l_proc, 50);
3628 exception
3629 when hr_api.validate_enabled then
3630 --
3631 -- As the Validate_Enabled exception has been raised
3632 -- we must rollback to the savepoint
3633 --
3634 rollback to DELETE_OFFER;
3635 --
3636 -- Only set output warning arguments
3637 -- (Any key or derived arguments must be set to null
3638 -- when validation only mode is being used.)
3639 --
3640 hr_utility.set_location(' Leaving:'||l_proc, 60);
3641 when others then
3642 --
3643 -- A validation or unexpected error has occured
3644 --
3645 rollback to DELETE_OFFER;
3646 hr_utility.set_location(' Leaving:'||l_proc, 70);
3647 raise;
3648 end delete_offer;
3649 --
3650 --
3651 -- ----------------------------------------------------------------------------
3652 -- |-----------------------------< get_offer_close_reason >--------------------|
3653 -- ----------------------------------------------------------------------------
3654 --
3655 --
3656 function get_offer_close_reason
3657 ( P_EFFECTIVE_DATE IN date
3658 ,P_APPLICANT_ASSIGNMENT_ID IN number
3659 ) RETURN VARCHAR2 Is
3660 l_proc varchar2(72) := g_package||'get_offer_close_reason';
3661 l_change_reason irc_offer_status_history.change_reason%TYPE;
3662 l_manager_terminates varchar2(1);
3663 l_user_id varchar2(250);
3664 --
3665 CURSOR csr_applicant_userid
3666 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3667 ,p_effective_date IN DATE
3668 )
3669 IS
3670 select user_id
3671 from per_all_assignments_f paf, fnd_user usr, per_all_people_f ppf,
3672 per_all_people_f linkppf
3673 where p_effective_date between paf.effective_start_date and
3674 paf.effective_end_date
3675 and p_effective_date between usr.start_date and
3676 nvl(usr.end_date,p_effective_date)
3677 and p_effective_date between ppf.effective_start_date and
3678 ppf.effective_end_date
3679 and p_effective_date between linkppf.effective_start_date and
3680 linkppf.effective_end_date
3681 and usr.employee_id=linkppf.person_id
3682 and ppf.party_id = linkppf.party_id
3683 and ppf.person_id = paf.person_id
3684 and paf.assignment_id= p_assignment_id
3685 and usr.user_id = fnd_global.user_id;
3686 --
3687 begin
3688 --
3689 hr_utility.set_location(' Entering: '|| l_proc, 10);
3690 --
3691 OPEN csr_applicant_userid
3692 (p_assignment_id => p_applicant_assignment_id
3693 ,p_effective_date => p_effective_date
3694 );
3695 FETCH csr_applicant_userid INTO l_user_id;
3696 IF csr_applicant_userid%NOTFOUND
3697 THEN
3698 l_manager_terminates:='Y';
3699 END IF;
3700 CLOSE csr_applicant_userid;
3701 --
3702 hr_utility.set_location('l_user_id: '||l_user_id,20);
3703 hr_utility.set_location('g_user_id: '||fnd_global.user_id,30);
3704 --
3705 if l_user_id=fnd_global.user_id then
3706 l_manager_terminates:='N';
3707 else
3708 l_manager_terminates:='Y';
3709 end if;
3710 --
3711 if fnd_profile.value('IRC_AGENCY_NAME') is not null then
3712 --
3713 l_change_reason := 'AGENCY_TERMINATE_APPL';
3714 --
3715 elsif l_manager_terminates = 'Y' then
3716 l_change_reason := 'MGR_TERMINATE_APPL';
3717 else
3718 l_change_reason := 'WITHDRAWAL';
3719 end if;
3720 --
3721 hr_utility.set_location(' l_change_reason: '||l_change_reason,40);
3722 hr_utility.set_location(' Leaving: '|| l_proc, 50);
3723 --
3724 RETURN l_change_reason;
3725 end get_offer_close_reason;
3726 --
3727 --
3728 -- ----------------------------------------------------------------------------
3729 -- |-----------------------------< close_offer >------------------------------|
3730 -- ----------------------------------------------------------------------------
3731 --
3732 procedure close_offer
3733 ( P_VALIDATE IN boolean default false
3734 ,P_EFFECTIVE_DATE IN date default null
3735 ,P_APPLICANT_ASSIGNMENT_ID IN number default null
3736 ,P_OFFER_ID IN number default null
3737 ,P_RESPONDENT_ID IN number default null
3738 ,P_CHANGE_REASON IN VARCHAR2 default null
3739 ,P_DECLINE_REASON IN VARCHAR2 default null
3740 ,P_NOTE_TEXT IN VARCHAR2 default null
3741 ,P_STATUS_CHANGE_DATE IN date default null
3742 ) Is
3743 --
3744 l_proc varchar2(72) := g_package||'close_offer';
3745 l_offer_assignment_id irc_offers.offer_assignment_id%TYPE := null;
3746 l_offer_id irc_offers.offer_id%TYPE := null;
3747 l_prev_offer_status irc_offers.offer_status%TYPE;
3748 l_offer_version irc_offers.offer_version%TYPE;
3749 l_offer_assignment_exists boolean := true;
3750 l_iof_object_version_number irc_offers.object_version_number%TYPE;
3751 l_change_reason irc_offer_status_history.change_reason%TYPE;
3752 -- Date variables
3753 l_effective_date date;
3754 l_status_change_date irc_offer_status_history.status_change_date%TYPE;
3755 -- Cursors
3756 cursor csr_offer_assignment_1 is
3757 select offer_assignment_id
3758 ,offer_id
3759 ,offer_status
3760 from irc_offers
3761 where applicant_assignment_id = p_applicant_assignment_id
3762 and latest_offer = 'Y';
3763 --
3764 cursor csr_saved_offers is
3765 select offer_id
3766 ,object_version_number
3767 from irc_offers
3768 where applicant_assignment_id = p_applicant_assignment_id
3769 and offer_status = 'SAVED';
3770 --
3771 cursor csr_offer_assignment_2 is
3772 select offer_assignment_id
3773 ,offer_status
3774 from irc_offers
3775 where offer_id = p_offer_id;
3776 --
3777 cursor csr_iof_object_version_number is
3778 select object_version_number
3779 from irc_offers
3780 where offer_id = l_offer_id;
3781 --
3782 Begin
3783 --
3784 hr_utility.set_location('Entering:'||l_proc,10);
3785 --
3786 -- issue a savepoint
3787 --
3788 savepoint CLOSE_OFFER;
3789 --
3790 -- Truncate the time portion from all IN date parameters
3791 --
3792 l_effective_date := trunc(p_effective_date);
3793 --
3794 -- If p_offer_id is not null, we can use 'csr_offer_assignment_2'
3795 -- This scenario would occur when the applicant declines the offer.
3796 --
3797 if (p_offer_id is not null)
3798 then
3799 --
3800 hr_utility.set_location(l_proc,20);
3801 --
3802 open csr_offer_assignment_2;
3803 fetch csr_offer_assignment_2 into l_offer_assignment_id
3804 ,l_prev_offer_status;
3805 if csr_offer_assignment_2%notfound
3806 then
3807 --
3808 close csr_offer_assignment_2;
3809 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
3810 fnd_message.raise_error;
3811 --
3812 end if;
3813 close csr_offer_assignment_2;
3814 -- Store the offer_id
3815 l_offer_id := p_offer_id;
3816 l_offer_assignment_exists := true;
3817 else
3818 --
3819 -- If p_offer_id is null we need to use 'csr_offer_assignment_1'.
3820 -- This scenario would occur when the applicant withdraws from the
3821 -- Application, in which case we would not know if there is an offer
3822 -- for this applicant in the first place, and if there is, what the
3823 -- offer_id for that record is.
3824 --
3825 l_offer_assignment_exists := false;
3826 --
3827 hr_utility.set_location(l_proc,30);
3828 --
3829 open csr_offer_assignment_1;
3830 fetch csr_offer_assignment_1 into l_offer_assignment_id
3831 ,l_offer_id
3832 ,l_prev_offer_status;
3833 --
3834 --
3835 -- Check if there is an offer record for this applicant.
3836 -- If yes, set l_offer_assignment_exists so that we can
3837 -- close the offer.
3838 --
3839 if csr_offer_assignment_1%found
3840 then
3841 --
3842 hr_utility.set_location(l_proc,40);
3843 --
3844 close csr_offer_assignment_1;
3845 --
3846 -- Set the variable l_offer_assignment_exists to true;
3847 --
3848 l_offer_assignment_exists := true;
3849 --
3850 else
3851 close csr_offer_assignment_1;
3852 end if;
3853 --
3854 hr_utility.set_location(l_proc,50);
3855 --
3856 end if;
3857 --
3858 hr_utility.set_location(l_proc,60);
3859 --
3860 -- Check if the offer assginment exists. Only if it exists call the
3861 -- update_offer the close the offer and end date the offer assignment.
3862 --
3863 if l_offer_assignment_exists = true
3864 then
3865 --
3866 hr_utility.set_location(l_proc,65);
3867 --
3868 -- Fetch Offer OVN
3869 --
3870 open csr_iof_object_version_number;
3871 fetch csr_iof_object_version_number into l_iof_object_version_number;
3872 --
3873 if csr_iof_object_version_number%notfound
3874 then
3875 --
3876 close csr_iof_object_version_number;
3877 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
3878 fnd_message.raise_error;
3879 --
3880 end if;
3881 --
3882 close csr_iof_object_version_number;
3883 --
3884 if p_change_reason='WITHDRAWAL' or p_change_reason='MGR_TERMINATE_APPL' then
3885 l_change_reason := get_offer_close_reason(p_effective_date,p_applicant_assignment_id);
3886 else
3887 l_change_reason := p_change_reason;
3888 end if;
3889 --
3890 -- Update the offer record in IRC_OFFERS and set the offer_status to
3891 -- Closed and end date the offer assignment.
3892 -- Do this only if the offer is previously not in CLOSED status.
3893 -- Also do this if the change reason is MGR_WITHDRAW, APL_DECLINED_ACCEPTANCE, APL_HIRED
3894 if l_prev_offer_status <> 'CLOSED' or p_change_reason='MGR_WITHDRAW' or p_change_reason='APL_DECLINED_ACCEPTANCE' or p_change_reason='APL_HIRED'
3895 then
3896 update_offer
3897 (p_validate => p_validate
3898 ,p_effective_date => l_effective_date
3899 ,p_offer_id => l_offer_id
3900 ,p_offer_version => l_offer_version
3901 ,p_offer_status => 'CLOSED'
3902 ,p_respondent_id => p_respondent_id
3903 ,p_change_reason => l_change_reason
3904 ,p_decline_reason => p_decline_reason
3905 ,p_note_text => p_note_text
3906 ,p_status_change_date => p_status_change_date
3907 ,p_object_version_number => l_iof_object_version_number
3908 );
3909 --
3910 if p_change_reason='MGR_WITHDRAW' or p_change_reason='WITHDRAWAL' or p_change_reason='APL_HIRED' or p_change_reason='EXPIRED' or p_change_reason='MGR_TERMINATE_APPL' then
3911 close_notifications(P_APPLICANT_ASSIGNMENT_ID,l_OFFER_ID);
3912 end if;
3913 --
3914 end if;
3915 --
3916 hr_utility.set_location(l_proc,70);
3917 --
3918 end if;
3919 --
3920 -- check if there are any SAVED offers for this applicant assignment ID.
3921 -- If so, close all of them if the change_reason is WITHDRAWAL.
3922 --
3923 if l_offer_assignment_exists = true
3924 and p_change_reason in ('WITHDRAWAL','MGR_TERMINATE_APPL')
3925 then
3926 --
3927 for c_rec in csr_saved_offers loop
3928 --
3929 -- Close all these offers.
3930 --
3931 update_offer
3932 (p_validate => p_validate
3933 ,p_effective_date => l_effective_date
3934 ,p_offer_id => c_rec.offer_id
3935 ,p_offer_version => l_offer_version
3936 ,p_offer_status => 'CLOSED'
3937 ,p_respondent_id => p_respondent_id
3938 ,p_change_reason => l_change_reason
3939 ,p_decline_reason => p_decline_reason
3940 ,p_note_text => p_note_text
3941 ,p_status_change_date => p_status_change_date
3942 ,p_object_version_number => c_rec.object_version_number
3943 );
3944 --
3945 close_notifications(P_APPLICANT_ASSIGNMENT_ID,c_rec.offer_id);
3946 --
3947 end loop;
3948 --
3949 end if;
3950
3951 --
3952 hr_utility.set_location(l_proc,90);
3953 --
3954 -- When in validation only mode raise the Validate_Enabled exception
3955 --
3956 if p_validate then
3957 raise hr_api.validate_enabled;
3958 end if;
3959 --
3960 hr_utility.set_location(' Leaving:'||l_proc, 100);
3961 exception
3962 when hr_api.validate_enabled then
3963 --
3964 -- As the Validate_Enabled exception has been raised
3965 -- we must rollback to the savepoint
3966 --
3967 rollback to CLOSE_OFFER;
3968 --
3969 hr_utility.set_location(' Leaving:'||l_proc, 110);
3970 when others then
3971 --
3972 -- A validation or unexpected error has occured
3973 --
3974 rollback to CLOSE_OFFER;
3975 --
3976 hr_utility.set_location(' Leaving:'||l_proc, 120);
3977 raise;
3978 end close_offer;
3979 --
3980 --
3981 -- ----------------------------------------------------------------------------
3982 -- |------------------------------< hold_offer >------------------------------|
3983 -- ----------------------------------------------------------------------------
3984 --
3985 procedure hold_offer
3986 ( P_VALIDATE IN boolean default false
3987 ,P_EFFECTIVE_DATE IN date default null
3988 ,P_OFFER_ID IN NUMBER
3989 ,P_RESPONDENT_ID IN NUMBER default hr_api.g_number
3990 ,P_CHANGE_REASON IN VARCHAR2 default null
3991 ,P_STATUS_CHANGE_DATE IN date default null
3992 ,P_NOTE_TEXT IN VARCHAR2 default null
3993 ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
3994 ) is
3995 --
3996 l_proc varchar2(72) := g_package||'hold_offer';
3997 l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
3998 l_offer_version irc_offers.offer_version%TYPE;
3999 --
4000 Begin
4001 hr_utility.set_location('Entering:'||l_proc,10);
4002 --
4003 -- Call update_offer with offer_status = 'HOLD'
4004 --
4005 update_offer
4006 ( p_validate => p_validate
4007 ,p_effective_date => p_effective_date
4008 ,p_offer_id => l_offer_id
4009 ,p_offer_version => l_offer_version
4010 ,p_object_version_number => p_object_version_number
4011 ,p_offer_status => 'HOLD'
4012 ,p_respondent_id => p_respondent_id
4013 ,p_change_reason => p_change_reason
4014 ,p_note_text => p_note_text
4015 ,p_status_change_date => p_status_change_date
4016 );
4017 --
4018 exception
4019 when others then
4020 hr_utility.set_location(' Leaving:'||l_proc, 30);
4021 raise;
4022 end hold_offer;
4023 --
4024 -- ----------------------------------------------------------------------------
4025 -- |-----------------------------< release_offer >----------------------------|
4026 -- ----------------------------------------------------------------------------
4027 --
4028 procedure release_offer
4029 ( P_VALIDATE IN boolean default false
4030 ,P_EFFECTIVE_DATE IN date default null
4031 ,P_OFFER_ID IN NUMBER
4032 ,P_RESPONDENT_ID IN NUMBER default hr_api.g_number
4033 ,P_CHANGE_REASON IN VARCHAR2 default null
4034 ,P_STATUS_CHANGE_DATE IN date default null
4035 ,P_NOTE_TEXT IN VARCHAR2 default null
4036 ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
4037 ) is
4038 --
4039 l_proc varchar2(72) := g_package||'release_offer';
4040 l_prev_offer_status irc_offers.offer_status%TYPE;
4041 l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
4042 l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
4043 l_prev_to_prev_change_reason irc_offer_status_history.change_reason%TYPE;
4044 l_prev_to_prev_decline_reason irc_offer_status_history.decline_reason%TYPE;
4045 l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
4046 l_offer_version irc_offers.offer_version%TYPE;
4047 l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
4048 l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
4049 l_other_extended_offer_count number := 0;
4050 l_prev_expiry_date irc_offers.expiry_date%TYPE;
4051 --
4052 cursor csr_prev_to_prev_offer_status is
4053 SELECT ios1.offer_status,
4054 ios1.change_reason,
4055 ios1.decline_reason
4056 FROM irc_offer_status_history ios1
4057 WHERE EXISTS ( SELECT 1
4058 FROM irc_offer_status_history iosh1
4059 WHERE iosh1.offer_id = p_offer_id
4060 AND iosh1.status_change_date > ios1.status_change_date
4061 )
4062 AND ios1.offer_status_history_id = (SELECT MAX(iosh2.offer_status_history_id)
4063 FROM irc_offer_status_history iosh2
4064 WHERE iosh2.offer_id = p_offer_id
4065 AND iosh2.status_change_date = ios1.status_change_date
4066 )
4067 AND 1 =
4068 (SELECT COUNT(*)
4069 FROM irc_offer_status_history ios3
4070 WHERE ios3.offer_id = p_offer_id
4071 AND ios3.status_change_date > ios1.status_change_date
4072 );
4073 --
4074 cursor csr_prev_offer_status is
4075 select offer_status
4076 ,applicant_assignment_id
4077 ,expiry_date
4078 from irc_offers
4079 where offer_id = p_offer_id;
4080 --
4081 Begin
4082 --
4083 hr_utility.set_location('Entering:'||l_proc,10);
4084 --
4085 -- Check if the offer is currently in HOLD
4086 --
4087 open csr_prev_offer_status;
4088 fetch csr_prev_offer_status into l_prev_offer_status
4089 ,l_prev_applicant_assignment_id
4090 ,l_prev_expiry_date;
4091 --
4092 if csr_prev_offer_status%notfound
4093 then
4094 --
4095 close csr_prev_offer_status;
4096 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
4097 fnd_message.raise_error;
4098 --
4099 end if;
4100 close csr_prev_offer_status;
4101 --
4102 if l_prev_offer_status <> 'HOLD'
4103 then
4104 --
4105 fnd_message.set_name('PER','IRC_412304_OFFER_NOT_HELD');
4106 fnd_message.raise_error;
4107 --
4108 end if;
4109 --
4110 open csr_prev_to_prev_offer_status;
4111 fetch csr_prev_to_prev_offer_status into l_prev_to_prev_offer_status
4112 ,l_prev_to_prev_change_reason
4113 ,l_prev_to_prev_decline_reason;
4114 --
4115 if csr_prev_to_prev_offer_status%notfound
4116 then
4117 --
4118 close csr_prev_to_prev_offer_status;
4119 fnd_message.set_name('PER','IRC_412305_INV_PREVTOPREV_OFR');
4120 fnd_message.raise_error;
4121 --
4122 end if;
4123 close csr_prev_to_prev_offer_status;
4124
4125 if l_prev_to_prev_offer_status = 'EXTENDED' and
4126 l_prev_expiry_date is not null and
4127 l_prev_expiry_date < p_effective_date
4128 then
4129 --
4130 close_offer
4131 ( p_validate => p_validate
4132 ,p_effective_date => p_effective_date
4133 ,p_applicant_assignment_id => l_prev_applicant_assignment_id
4134 ,p_offer_id => p_offer_id
4135 ,p_respondent_id => p_respondent_id
4136 ,p_change_reason => 'EXPIRED'
4137 ,p_note_text => p_note_text
4138 ,p_status_change_date => p_status_change_date
4139 );
4140 else
4141 --
4142 -- Checks to not Extend the offer if already an offer has been Extended
4143 --
4144 if l_prev_to_prev_offer_status = 'EXTENDED' -- Extended Offer which was on Hold
4145 OR ( l_prev_to_prev_offer_status = 'CLOSED' -- Closed and Accepted offer which was on Hold
4146 AND l_prev_to_prev_change_reason = 'APL_ACCEPTED'
4147 )
4148 then
4149 --
4150 if 'N' = fnd_profile.value('IRC_ALLOW_MULTI_OFFERS')
4151 OR 'E' = fnd_profile.value('IRC_ALLOW_MULTI_OFFERS')
4152 OR fnd_profile.value('IRC_ALLOW_MULTI_OFFERS') is null
4153 then
4154 --
4155 -- This happens when an offer which was previously EXTENDED or was CLOSED and ACCEPTED
4156 -- is being taken off Hold.
4157 -- Refuse to take it off Hold if another EXTENDED Offer or CLOSED and ACCEPTED Offer Exists
4158 --
4159 other_extended_offers_count
4160 ( p_applicant_assignment_id => l_prev_applicant_assignment_id
4161 ,p_effective_date => p_effective_date
4162 ,p_other_extended_offer_count => l_other_extended_offer_count
4163 );
4164 --
4165 if l_other_extended_offer_count > 0
4166 then
4167 --
4168 fnd_message.set_name('PER','IRC_412377_MULTIPLE_OFFER_SEND'); -- You cannot send another offer to this applicant as
4169 -- an active offer exists for this application
4170 fnd_message.raise_error;
4171 --
4172 end if;
4173 --
4174 end if;
4175 --
4176 end if;
4177 --
4178 -- Update the offer with offer_status as the offer status
4179 -- before the offer was held.
4180 --
4181 irc_iof_upd.upd
4182 ( p_effective_date => p_effective_date
4183 ,p_offer_id => l_offer_id
4184 ,p_object_version_number => p_object_version_number
4185 ,p_offer_version => l_offer_version
4186 ,p_offer_status => l_prev_to_prev_offer_status
4187 ,p_respondent_id => p_respondent_id
4188 );
4189 --
4190 -- Create the offer status history record with the details
4191 --
4192 irc_offer_status_history_api.create_offer_status_history
4193 ( p_validate => p_validate
4194 , p_effective_date => p_effective_date
4195 , p_offer_id => l_offer_id
4196 , p_offer_status => l_prev_to_prev_offer_status
4197 , p_change_reason => l_prev_to_prev_change_reason
4198 , p_decline_reason => l_prev_to_prev_decline_reason
4199 , p_note_text => p_note_text
4200 , p_offer_status_history_id => l_offer_status_history_id
4201 , p_object_version_number => l_osh_object_version_number
4202 );
4203 --
4204 end if;
4205 --
4206 exception
4207 when others then
4208 hr_utility.set_location(' Leaving:'||l_proc, 30);
4209 raise;
4210 end release_offer;
4211 --
4212 -- ----------------------------------------------------------------------------
4213 -- |-----------------------< create_offer_assignment >------------------------|
4214 -- ----------------------------------------------------------------------------
4215 --
4216 Procedure create_offer_assignment
4217 (
4218 p_assignment_id OUT NOCOPY NUMBER
4219 ,p_effective_start_date OUT NOCOPY DATE
4220 ,p_effective_end_date OUT NOCOPY DATE
4221 ,p_business_group_id IN NUMBER
4222 ,p_recruiter_id IN NUMBER default null
4223 ,p_grade_id IN NUMBER default null
4224 ,p_position_id IN NUMBER default null
4225 ,p_job_id IN NUMBER default null
4226 ,p_assignment_status_type_id IN NUMBER
4227 ,p_payroll_id IN NUMBER default null
4228 ,p_location_id IN NUMBER default null
4229 ,p_person_referred_by_id IN NUMBER default null
4230 ,p_supervisor_id IN NUMBER default null
4231 ,p_special_ceiling_step_id IN NUMBER default null
4232 ,p_person_id IN NUMBER
4233 ,p_recruitment_activity_id IN NUMBER default null
4234 ,p_source_organization_id IN NUMBER default null
4235 ,p_organization_id IN NUMBER
4236 ,p_people_group_id IN NUMBER default null
4237 ,p_soft_coding_keyflex_id IN NUMBER default null
4238 ,p_vacancy_id IN NUMBER default null
4239 ,p_pay_basis_id IN NUMBER default null
4240 ,p_assignment_sequence OUT NOCOPY NUMBER
4241 ,p_assignment_type IN VARCHAR2
4242 ,p_primary_flag IN VARCHAR2
4243 ,p_application_id IN NUMBER default null
4244 ,p_assignment_number IN OUT NOCOPY VARCHAr2
4245 ,p_change_reason IN VARCHAR2 default null
4246 ,p_comment_id OUT NOCOPY NUMBER
4247 ,p_comments IN VARCHAR2 default null
4248 ,p_date_probation_end IN DATE default null
4249 ,p_default_code_comb_id IN NUMBER default null
4250 ,p_employment_category IN VARCHAR2 default null
4251 ,p_frequency IN VARCHAR2 default null
4252 ,p_internal_address_line IN VARCHAR2 default null
4253 ,p_manager_flag IN VARCHAR2 default null
4254 ,p_normal_hours IN NUMBER default null
4255 ,p_perf_review_period IN NUMBER default null
4256 ,p_perf_review_period_frequency IN VARCHAR2 default null
4257 ,p_period_of_service_id IN NUMBER default null
4258 ,p_probation_period IN NUMBER default null
4259 ,p_probation_unit IN VARCHAR2 default null
4260 ,p_sal_review_period IN NUMBER default null
4261 ,p_sal_review_period_frequency IN VARCHAR2 default null
4262 ,p_set_of_books_id IN NUMBER default null
4263 ,p_source_type IN VARCHAR2 default null
4264 ,p_time_normal_finish IN VARCHAR2 default null
4265 ,p_time_normal_start IN VARCHAR2 default null
4266 ,p_bargaining_unit_code IN VARCHAR2 default null
4267 ,p_labour_union_member_flag IN VARCHAR2 default 'N'
4268 ,p_hourly_salaried_code IN VARCHAR2 default null
4269 ,p_request_id IN NUMBER default null
4270 ,p_program_application_id IN NUMBER default null
4271 ,p_program_id IN NUMBER default null
4272 ,p_program_update_date IN DATE default null
4273 ,p_ass_attribute_category IN VARCHAR2 default null
4274 ,p_ass_attribute1 IN VARCHAR2 default null
4275 ,p_ass_attribute2 IN VARCHAR2 default null
4276 ,p_ass_attribute3 IN VARCHAR2 default null
4277 ,p_ass_attribute4 IN VARCHAR2 default null
4278 ,p_ass_attribute5 IN VARCHAR2 default null
4279 ,p_ass_attribute6 IN VARCHAR2 default null
4280 ,p_ass_attribute7 IN VARCHAR2 default null
4281 ,p_ass_attribute8 IN VARCHAR2 default null
4282 ,p_ass_attribute9 IN VARCHAR2 default null
4283 ,p_ass_attribute10 IN VARCHAR2 default null
4284 ,p_ass_attribute11 IN VARCHAR2 default null
4285 ,p_ass_attribute12 IN VARCHAR2 default null
4286 ,p_ass_attribute13 IN VARCHAR2 default null
4287 ,p_ass_attribute14 IN VARCHAR2 default null
4288 ,p_ass_attribute15 IN VARCHAR2 default null
4289 ,p_ass_attribute16 IN VARCHAR2 default null
4290 ,p_ass_attribute17 IN VARCHAR2 default null
4291 ,p_ass_attribute18 IN VARCHAR2 default null
4292 ,p_ass_attribute19 IN VARCHAR2 default null
4293 ,p_ass_attribute20 IN VARCHAR2 default null
4294 ,p_ass_attribute21 IN VARCHAR2 default null
4295 ,p_ass_attribute22 IN VARCHAR2 default null
4296 ,p_ass_attribute23 IN VARCHAR2 default null
4297 ,p_ass_attribute24 IN VARCHAR2 default null
4298 ,p_ass_attribute25 IN VARCHAR2 default null
4299 ,p_ass_attribute26 IN VARCHAR2 default null
4300 ,p_ass_attribute27 IN VARCHAR2 default null
4301 ,p_ass_attribute28 IN VARCHAR2 default null
4302 ,p_ass_attribute29 IN VARCHAR2 default null
4303 ,p_ass_attribute30 IN VARCHAR2 default null
4304 ,p_title IN VARCHAR2 default null
4305 ,p_validate_df_flex IN BOOLEAN default true
4306 ,p_object_version_number OUT NOCOPY NUMBER
4307 ,p_other_manager_warning OUT NOCOPY BOOLEAN
4308 ,p_hourly_salaried_warning OUT NOCOPY BOOLEAN
4309 ,p_effective_date IN DATE
4310 ,p_validate IN BOOLEAN default false
4311 ,p_contract_id IN NUMBER default null
4312 ,p_establishment_id IN NUMBER default null
4313 ,p_collective_agreement_id IN NUMBER default null
4314 ,p_cagr_grade_def_id IN NUMBER default null
4315 ,p_cagr_id_flex_num IN NUMBER default null
4316 ,p_notice_period IN NUMBER default null
4317 ,p_notice_period_uom IN VARCHAR2 default null
4318 ,p_employee_category IN VARCHAR2 default null
4319 ,p_work_at_home IN VARCHAR2 default null
4320 ,p_job_post_source_name IN VARCHAR2 default null
4321 ,p_posting_content_id IN NUMBER default null
4322 ,p_placement_date_start IN DATE default null
4323 ,p_vendor_id IN NUMBER default null
4324 ,p_vendor_employee_number IN VARCHAR2 default null
4325 ,p_vendor_assignment_number IN VARCHAR2 default null
4326 ,p_assignment_category IN VARCHAR2 default null
4327 ,p_project_title IN VARCHAR2 default null
4328 ,p_applicant_rank IN NUMBER default null
4329 ,p_grade_ladder_pgm_id IN NUMBER default null
4330 ,p_supervisor_assignment_id IN NUMBER default null
4331 ,p_vendor_site_id IN NUMBER default null
4332 ,p_po_header_id IN NUMBER default null
4333 ,p_po_line_id IN NUMBER default null
4334 ,p_projected_assignment_end IN DATE default null
4335 )Is
4336 --
4337 l_proc varchar2(72) := g_package||'create_offer_assignment';
4338 --
4339 -- Out and In Out variables
4340 --
4341 l_offer_assignment_id per_all_assignments_f.assignment_id%TYPE;
4342 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
4343 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
4344 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
4345 l_assignment_number per_all_assignments_f.assignment_number%TYPE := p_assignment_number;
4346 l_comment_id per_all_assignments_f.comment_id%TYPE;
4347 l_object_version_number per_all_assignments_f.object_version_number%TYPE;
4348 l_other_manager_warning boolean;
4349 l_hourly_salaried_warning boolean;
4350 --
4351 -- variables to be set
4352 --
4353 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
4354 l_primary_flag per_all_assignments_f.primary_flag%TYPE;
4355 --
4356 -- Date Variables
4357 --
4358 l_date_probation_end per_all_assignments_f.date_probation_end%TYPE;
4359 l_program_update_date per_all_assignments_f.program_update_date%TYPE;
4360 l_placement_date_start date;
4361 l_projected_assignment_end date;
4362 l_effective_date date;
4363 Begin
4364
4365 hr_utility.set_location('Entering:'||l_proc,10);
4366 --
4367 -- Issue a savepoint
4368 --
4369 savepoint CREATE_OFFER_ASSIGNMENT;
4370 --
4371 hr_utility.set_location(l_proc,20);
4372 --
4373 --
4374 -- Truncate the time portion from all IN date parameters
4375 --
4376 l_date_probation_end := trunc(p_date_probation_end);
4377 l_program_update_date := trunc(p_program_update_date);
4378 l_effective_date := trunc(p_effective_date);
4379 l_placement_date_start := trunc(p_placement_date_start);
4380 l_projected_assignment_end := trunc(p_projected_assignment_end);
4381 --
4382 -- The offer record must have an assignment_type = 'O'
4383 -- and the primary_flag must be set to 'N'
4384 --
4385 l_assignment_type := 'O';
4386 l_primary_flag := 'N';
4387 --
4388 hr_utility.set_location(l_proc,40);
4389 --
4390 -- Create Offer Assignment record
4391 --
4392 per_asg_ins.ins
4393 ( p_assignment_id => l_offer_assignment_id
4394 ,p_effective_start_date => l_effective_start_date
4395 ,p_effective_end_date => l_effective_end_date
4396 ,p_business_group_id => p_business_group_id
4397 ,p_recruiter_id => p_recruiter_id
4398 ,p_grade_id => p_grade_id
4399 ,p_position_id => p_position_id
4400 ,p_job_id => p_job_id
4401 ,p_assignment_status_type_id => p_assignment_status_type_id
4402 ,p_payroll_id => p_payroll_id
4403 ,p_location_id => p_location_id
4404 ,p_person_referred_by_id => p_person_referred_by_id
4405 ,p_supervisor_id => p_supervisor_id
4406 ,p_special_ceiling_step_id => p_special_ceiling_step_id
4407 ,p_person_id => p_person_id
4408 ,p_recruitment_activity_id => p_recruitment_activity_id
4409 ,p_source_organization_id => p_source_organization_id
4410 ,p_organization_id => p_organization_id
4411 ,p_people_group_id => p_people_group_id
4412 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
4413 ,p_vacancy_id => p_vacancy_id
4414 ,p_pay_basis_id => p_pay_basis_id
4415 ,p_assignment_sequence => l_assignment_sequence
4416 ,p_assignment_type => l_assignment_type
4417 ,p_primary_flag => l_primary_flag
4418 ,p_application_id => p_application_id
4419 ,p_assignment_number => l_assignment_number
4420 ,p_change_reason => p_change_reason
4421 ,p_comment_id => l_comment_id
4422 ,p_comments => p_comments
4423 ,p_date_probation_end => l_date_probation_end
4424 ,p_default_code_comb_id => p_default_code_comb_id
4425 ,p_employment_category => p_employment_category
4426 ,p_frequency => p_frequency
4427 ,p_internal_address_line => p_internal_address_line
4428 ,p_manager_flag => p_manager_flag
4429 ,p_normal_hours => p_normal_hours
4430 ,p_perf_review_period => p_perf_review_period
4431 ,p_perf_review_period_frequency => p_perf_review_period_frequency
4432 ,p_period_of_service_id => p_period_of_service_id
4433 ,p_probation_period => p_probation_period
4434 ,p_probation_unit => p_probation_unit
4435 ,p_sal_review_period => p_sal_review_period
4436 ,p_sal_review_period_frequency => p_sal_review_period_frequency
4437 ,p_set_of_books_id => p_set_of_books_id
4438 ,p_source_type => p_source_type
4439 ,p_time_normal_finish => p_time_normal_finish
4440 ,p_time_normal_start => p_time_normal_start
4441 ,p_bargaining_unit_code => p_bargaining_unit_code
4442 ,p_labour_union_member_flag => p_labour_union_member_flag
4443 ,p_hourly_salaried_code => p_hourly_salaried_code
4444 ,p_request_id => p_request_id
4445 ,p_program_application_id => p_program_application_id
4446 ,p_program_id => p_program_id
4447 ,p_program_update_date => l_program_update_date
4448 ,p_ass_attribute_category => p_ass_attribute_category
4449 ,p_ass_attribute1 => p_ass_attribute1
4450 ,p_ass_attribute2 => p_ass_attribute2
4451 ,p_ass_attribute3 => p_ass_attribute3
4452 ,p_ass_attribute4 => p_ass_attribute4
4453 ,p_ass_attribute5 => p_ass_attribute5
4454 ,p_ass_attribute6 => p_ass_attribute6
4455 ,p_ass_attribute7 => p_ass_attribute7
4456 ,p_ass_attribute8 => p_ass_attribute8
4457 ,p_ass_attribute9 => p_ass_attribute9
4458 ,p_ass_attribute10 => p_ass_attribute10
4459 ,p_ass_attribute11 => p_ass_attribute11
4460 ,p_ass_attribute12 => p_ass_attribute12
4461 ,p_ass_attribute13 => p_ass_attribute13
4462 ,p_ass_attribute14 => p_ass_attribute14
4463 ,p_ass_attribute15 => p_ass_attribute15
4464 ,p_ass_attribute16 => p_ass_attribute16
4465 ,p_ass_attribute17 => p_ass_attribute17
4466 ,p_ass_attribute18 => p_ass_attribute18
4467 ,p_ass_attribute19 => p_ass_attribute19
4468 ,p_ass_attribute20 => p_ass_attribute20
4469 ,p_ass_attribute21 => p_ass_attribute21
4470 ,p_ass_attribute22 => p_ass_attribute22
4471 ,p_ass_attribute23 => p_ass_attribute23
4472 ,p_ass_attribute24 => p_ass_attribute24
4473 ,p_ass_attribute25 => p_ass_attribute25
4474 ,p_ass_attribute26 => p_ass_attribute26
4475 ,p_ass_attribute27 => p_ass_attribute27
4476 ,p_ass_attribute28 => p_ass_attribute28
4477 ,p_ass_attribute29 => p_ass_attribute29
4478 ,p_ass_attribute30 => p_ass_attribute30
4479 ,p_title => p_title
4480 ,p_validate_df_flex => p_validate_df_flex
4481 ,p_object_version_number => l_object_version_number
4482 ,p_other_manager_warning => l_other_manager_warning
4483 ,p_hourly_salaried_warning => l_hourly_salaried_warning
4484 ,p_effective_date => l_effective_date
4485 ,p_validate => p_validate
4486 ,p_contract_id => p_contract_id
4487 ,p_establishment_id => p_establishment_id
4488 ,p_collective_agreement_id => p_collective_agreement_id
4489 ,p_cagr_grade_def_id => p_cagr_grade_def_id
4490 ,p_cagr_id_flex_num => p_cagr_id_flex_num
4491 ,p_notice_period => p_notice_period
4492 ,p_notice_period_uom => p_notice_period_uom
4493 ,p_employee_category => p_employee_category
4494 ,p_work_at_home => p_work_at_home
4495 ,p_job_post_source_name => p_job_post_source_name
4496 ,p_posting_content_id => p_posting_content_id
4497 ,p_placement_date_start => l_placement_date_start
4498 ,p_vendor_id => p_vendor_id
4499 ,p_vendor_employee_number => p_vendor_employee_number
4500 ,p_vendor_assignment_number => p_vendor_assignment_number
4501 ,p_assignment_category => p_assignment_category
4502 ,p_project_title => p_project_title
4503 ,p_applicant_rank => p_applicant_rank
4504 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
4505 ,p_supervisor_assignment_id => p_supervisor_assignment_id
4506 ,p_vendor_site_id => p_vendor_site_id
4507 ,p_po_header_id => p_po_header_id
4508 ,p_po_line_id => p_po_line_id
4509 ,p_projected_assignment_end => p_projected_assignment_end
4510 );
4511 --
4512 hr_utility.set_location(l_proc, 50);
4513 --
4514 --
4515 -- When in validation only mode raise the Validate_Enabled exception
4516 --
4517 if p_validate then
4518 raise hr_api.validate_enabled;
4519 end if;
4520 --
4521 -- Assign all the OUT and IN OUT Variables
4522 --
4523 p_assignment_id := l_offer_assignment_id;
4524 p_effective_start_date := l_effective_start_date;
4525 p_effective_end_date := l_effective_end_date;
4526 p_assignment_sequence := l_assignment_sequence;
4527 p_assignment_number := l_assignment_number;
4528 p_comment_id := l_comment_id;
4529 p_object_version_number := l_object_version_number;
4530 p_other_manager_warning := l_other_manager_warning;
4531 p_hourly_salaried_warning := l_hourly_salaried_warning;
4532 --
4533 hr_utility.set_location(' Leaving:'||l_proc, 60);
4534 --
4535 exception
4536 when hr_api.validate_enabled then
4537 --
4538 p_assignment_id := null;
4539 p_effective_start_date := null;
4540 p_effective_end_date := null;
4541 p_assignment_sequence := null;
4542 p_assignment_number := l_assignment_number;
4543 p_comment_id := null;
4544 p_object_version_number := null;
4545 --
4546 -- As the Validate_Enabled exception has been raised
4547 -- we must rollback to the savepoint
4548 --
4549 rollback to CREATE_OFFER_ASSIGNMENT;
4550 --
4551 hr_utility.set_location(' Leaving:'||l_proc, 70);
4552 when others then
4553 --
4554 p_assignment_id := null;
4555 p_effective_start_date := null;
4556 p_effective_end_date := null;
4557 p_assignment_sequence := null;
4558 p_assignment_number := l_assignment_number;
4559 p_comment_id := null;
4560 p_object_version_number := null;
4561 --
4562 -- A validation or unexpected error has occured
4563 --
4564 rollback to CREATE_OFFER_ASSIGNMENT;
4565 --
4566 hr_utility.set_location(' Leaving:'||l_proc, 80);
4567 raise;
4568 end create_offer_assignment;
4569 --
4570 -- ----------------------------------------------------------------------------
4571 -- |-----------------------< update_offer_assignment >------------------------|
4572 -- ----------------------------------------------------------------------------
4573 --
4574 procedure update_offer_assignment
4575 ( P_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
4576 ,P_EFFECTIVE_START_DATE OUT NOCOPY DATE
4577 ,P_EFFECTIVE_END_DATE OUT NOCOPY DATE
4578 ,P_BUSINESS_GROUP_ID OUT NOCOPY NUMBER
4579
4580 ,P_RECRUITER_ID IN NUMBER default hr_api.g_number
4581 ,P_GRADE_ID IN NUMBER default hr_api.g_number
4582 ,P_POSITION_ID IN NUMBER default hr_api.g_number
4583 ,P_JOB_ID IN NUMBER default hr_api.g_number
4584 ,P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER default hr_api.g_number
4585 ,P_PAYROLL_ID IN NUMBER default hr_api.g_number
4586 ,P_LOCATION_ID IN NUMBER default hr_api.g_number
4587 ,P_PERSON_REFERRED_BY_ID IN NUMBER default hr_api.g_number
4588 ,P_SUPERVISOR_ID IN NUMBER default hr_api.g_number
4589 ,P_SPECIAL_CEILING_STEP_ID IN NUMBER default hr_api.g_number
4590 ,P_RECRUITMENT_ACTIVITY_ID IN NUMBER default hr_api.g_number
4591 ,P_SOURCE_ORGANIZATION_ID IN NUMBER default hr_api.g_number
4592
4593 ,P_ORGANIZATION_ID IN NUMBER default hr_api.g_number
4594 ,P_PEOPLE_GROUP_ID IN NUMBER default hr_api.g_number
4595 ,P_SOFT_CODING_KEYFLEX_ID IN NUMBER default hr_api.g_number
4596 ,P_VACANCY_ID IN NUMBER default hr_api.g_number
4597 ,P_PAY_BASIS_ID IN NUMBER default hr_api.g_number
4598 ,P_ASSIGNMENT_TYPE IN VARCHAR2 default hr_api.g_varchar2
4599 ,P_PRIMARY_FLAG IN VARCHAR2 default hr_api.g_varchar2
4600 ,P_APPLICATION_ID IN NUMBER default hr_api.g_number
4601 ,P_ASSIGNMENT_NUMBER IN VARCHAR2 default hr_api.g_varchar2
4602 ,P_CHANGE_REASON IN VARCHAR2 default hr_api.g_varchar2
4603 ,P_COMMENT_ID OUT NOCOPY NUMBER
4604 ,P_COMMENTS IN VARCHAR2 default hr_api.g_varchar2
4605 ,P_DATE_PROBATION_END IN DATE default hr_api.g_date
4606
4607 ,P_DEFAULT_CODE_COMB_ID IN NUMBER default hr_api.g_number
4608 ,P_EMPLOYMENT_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
4609 ,P_FREQUENCY IN VARCHAR2 default hr_api.g_varchar2
4610 ,P_INTERNAL_ADDRESS_LINE IN VARCHAR2 default hr_api.g_varchar2
4611 ,P_MANAGER_FLAG IN VARCHAR2 default hr_api.g_varchar2
4612 ,P_NORMAL_HOURS IN NUMBER default hr_api.g_number
4613 ,P_PERF_REVIEW_PERIOD IN NUMBER default hr_api.g_number
4614 ,P_PERF_REVIEW_PERIOD_FREQUENCY IN VARCHAR2 default hr_api.g_varchar2
4615 ,P_PERIOD_OF_SERVICE_ID IN NUMBER default hr_api.g_number
4616 ,P_PROBATION_PERIOD IN NUMBER default hr_api.g_number
4617 ,P_PROBATION_UNIT IN VARCHAR2 default hr_api.g_varchar2
4618 ,P_SAL_REVIEW_PERIOD IN NUMBER default hr_api.g_number
4619 ,P_SAL_REVIEW_PERIOD_FREQUENCY IN VARCHAR2 default hr_api.g_varchar2
4620 ,P_SET_OF_BOOKS_ID IN NUMBER default hr_api.g_number
4621
4622 ,P_SOURCE_TYPE IN VARCHAR2 default hr_api.g_varchar2
4623 ,P_TIME_NORMAL_FINISH IN VARCHAR2 default hr_api.g_varchar2
4624 ,P_TIME_NORMAL_START IN VARCHAR2 default hr_api.g_varchar2
4625 ,P_BARGAINING_UNIT_CODE IN VARCHAR2 default hr_api.g_varchar2
4626 ,P_LABOUR_UNION_MEMBER_FLAG IN VARCHAR2 default hr_api.g_varchar2
4627 ,P_HOURLY_SALARIED_CODE IN VARCHAR2 default hr_api.g_varchar2
4628 ,P_REQUEST_ID IN NUMBER default hr_api.g_number
4629 ,P_PROGRAM_APPLICATION_ID IN NUMBER default hr_api.g_number
4630 ,P_PROGRAM_ID IN NUMBER default hr_api.g_number
4631 ,P_PROGRAM_UPDATE_DATE IN DATE default hr_api.g_date
4632 ,P_ASS_ATTRIBUTE_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
4633 ,P_ASS_ATTRIBUTE1 IN VARCHAR2 default hr_api.g_varchar2
4634 ,P_ASS_ATTRIBUTE2 IN VARCHAR2 default hr_api.g_varchar2
4635 ,P_ASS_ATTRIBUTE3 IN VARCHAR2 default hr_api.g_varchar2
4636 ,P_ASS_ATTRIBUTE4 IN VARCHAR2 default hr_api.g_varchar2
4637 ,P_ASS_ATTRIBUTE5 IN VARCHAR2 default hr_api.g_varchar2
4638
4639 ,P_ASS_ATTRIBUTE6 IN VARCHAR2 default hr_api.g_varchar2
4640 ,P_ASS_ATTRIBUTE7 IN VARCHAR2 default hr_api.g_varchar2
4641 ,P_ASS_ATTRIBUTE8 IN VARCHAR2 default hr_api.g_varchar2
4642 ,P_ASS_ATTRIBUTE9 IN VARCHAR2 default hr_api.g_varchar2
4643 ,P_ASS_ATTRIBUTE10 IN VARCHAR2 default hr_api.g_varchar2
4644 ,P_ASS_ATTRIBUTE11 IN VARCHAR2 default hr_api.g_varchar2
4645 ,P_ASS_ATTRIBUTE12 IN VARCHAR2 default hr_api.g_varchar2
4646 ,P_ASS_ATTRIBUTE13 IN VARCHAR2 default hr_api.g_varchar2
4647 ,P_ASS_ATTRIBUTE14 IN VARCHAR2 default hr_api.g_varchar2
4648 ,P_ASS_ATTRIBUTE15 IN VARCHAR2 default hr_api.g_varchar2
4649 ,P_ASS_ATTRIBUTE16 IN VARCHAR2 default hr_api.g_varchar2
4650 ,P_ASS_ATTRIBUTE17 IN VARCHAR2 default hr_api.g_varchar2
4651 ,P_ASS_ATTRIBUTE18 IN VARCHAR2 default hr_api.g_varchar2
4652
4653 ,P_ASS_ATTRIBUTE19 IN VARCHAR2 default hr_api.g_varchar2
4654 ,P_ASS_ATTRIBUTE20 IN VARCHAR2 default hr_api.g_varchar2
4655 ,P_ASS_ATTRIBUTE21 IN VARCHAR2 default hr_api.g_varchar2
4656 ,P_ASS_ATTRIBUTE22 IN VARCHAR2 default hr_api.g_varchar2
4657 ,P_ASS_ATTRIBUTE23 IN VARCHAR2 default hr_api.g_varchar2
4658 ,P_ASS_ATTRIBUTE24 IN VARCHAR2 default hr_api.g_varchar2
4659 ,P_ASS_ATTRIBUTE25 IN VARCHAR2 default hr_api.g_varchar2
4660 ,P_ASS_ATTRIBUTE26 IN VARCHAR2 default hr_api.g_varchar2
4661 ,P_ASS_ATTRIBUTE27 IN VARCHAR2 default hr_api.g_varchar2
4662 ,P_ASS_ATTRIBUTE28 IN VARCHAR2 default hr_api.g_varchar2
4663 ,P_ASS_ATTRIBUTE29 IN VARCHAR2 default hr_api.g_varchar2
4664 ,P_ASS_ATTRIBUTE30 IN VARCHAR2 default hr_api.g_varchar2
4665 ,P_TITLE IN VARCHAR2 default hr_api.g_varchar2
4666 ,P_CONTRACT_ID IN NUMBER default hr_api.g_number
4667 ,P_ESTABLISHMENT_ID IN NUMBER default hr_api.g_number
4668 ,P_COLLECTIVE_AGREEMENT_ID IN NUMBER default hr_api.g_number
4669 ,P_CAGR_GRADE_DEF_ID IN NUMBER default hr_api.g_number
4670 ,P_CAGR_ID_FLEX_NUM IN NUMBER default hr_api.g_number
4671 ,P_ASG_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER
4672 ,P_NOTICE_PERIOD IN NUMBER default hr_api.g_number
4673 ,P_NOTICE_PERIOD_UOM IN VARCHAR2 default hr_api.g_varchar2
4674 ,P_EMPLOYEE_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
4675 ,P_WORK_AT_HOME IN VARCHAR2 default hr_api.g_varchar2
4676 ,P_JOB_POST_SOURCE_NAME IN VARCHAR2 default hr_api.g_varchar2
4677 ,P_POSTING_CONTENT_ID IN NUMBER default hr_api.g_number
4678 ,P_PLACEMENT_DATE_START IN DATE default hr_api.g_date
4679 ,P_VENDOR_ID IN NUMBER default hr_api.g_number
4680 ,P_VENDOR_EMPLOYEE_NUMBER IN VARCHAR2 default hr_api.g_varchar2
4681 ,P_VENDOR_ASSIGNMENT_NUMBER IN VARCHAR2 default hr_api.g_varchar2
4682 ,P_ASSIGNMENT_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
4683 ,P_PROJECT_TITLE IN VARCHAR2 default hr_api.g_varchar2
4684 ,P_APPLICANT_RANK IN NUMBER default hr_api.g_number
4685 ,P_GRADE_LADDER_PGM_ID IN NUMBER default hr_api.g_number
4686 ,P_SUPERVISOR_ASSIGNMENT_ID IN NUMBER default hr_api.g_number
4687 ,P_VENDOR_SITE_ID IN NUMBER default hr_api.g_number
4688 ,P_PO_HEADER_ID IN NUMBER default hr_api.g_number
4689 ,P_PO_LINE_ID IN NUMBER default hr_api.g_number
4690 ,P_PROJECTED_ASSIGNMENT_END IN DATE default hr_api.g_date
4691 ,P_PAYROLL_ID_UPDATED OUT NOCOPY BOOLEAN
4692 ,P_OTHER_MANAGER_WARNING OUT NOCOPY BOOLEAN
4693 ,P_HOURLY_SALARIED_WARNING OUT NOCOPY BOOLEAN
4694 ,P_NO_MANAGERS_WARNING OUT NOCOPY BOOLEAN
4695 ,P_ORG_NOW_NO_MANAGER_WARNING OUT NOCOPY BOOLEAN
4696 ,P_VALIDATION_START_DATE OUT NOCOPY DATE
4697 ,P_VALIDATION_END_DATE OUT NOCOPY DATE
4698 ,P_EFFECTIVE_DATE IN DATE default null
4699 ,P_DATETRACK_MODE IN VARCHAR2 default hr_api.g_update
4700 ,P_VALIDATE IN BOOLEAN default false
4701 ,P_OFFER_ID IN OUT NOCOPY NUMBER
4702 ,P_OFFER_STATUS IN VARCHAR2 default null
4703 ) Is
4704 --
4705 l_proc varchar2(72) := g_package||'update_offer_assignment';
4706 l_create_new_version boolean := false;
4707
4708 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
4709 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
4710 l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
4711 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
4712 l_comment_id per_all_assignments_f.comment_id%TYPE;
4713 l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE := p_asg_object_version_number;
4714 l_payroll_id_updated boolean;
4715 l_other_manager_warning boolean;
4716 l_hourly_salaried_warning boolean;
4717 l_no_managers_warning boolean;
4718 l_org_now_no_manager_warning boolean;
4719 l_validation_start_date date;
4720 l_validation_end_date date;
4721 l_effective_date date;
4722 l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
4723
4724 l_prev_offer_status irc_offers.offer_status%TYPE;
4725 l_iof_object_version_number irc_offers.object_version_number%TYPE;
4726 l_offer_version irc_offers.offer_version%TYPE;
4727 l_datetrack_mode varchar2(30) := hr_api.g_update;
4728 --
4729 cursor csr_prev_offer_details is
4730 select offer_status
4731 ,offer_assignment_id
4732 from irc_offers
4733 where offer_id = p_offer_id;
4734 --
4735 cursor csr_asg_effective_start_date is
4736 select effective_start_date
4737 from per_all_assignments_f
4738 where assignment_id = P_ASSIGNMENT_ID
4739 and p_effective_date
4740 between effective_start_date
4741 and effective_end_date;
4742 --
4743 cursor csr_offer_record is
4744 select
4745 offer_version
4746 ,offer_status
4747 ,discretionary_job_title
4748 ,offer_extended_method
4749 ,respondent_id
4750 ,expiry_date
4751 ,proposed_start_date
4752 ,offer_letter_tracking_code
4753 ,offer_postal_service
4754 ,offer_shipping_date
4755 ,vacancy_id
4756 ,applicant_assignment_id
4757 ,offer_assignment_id
4758 ,address_id
4759 ,template_id
4760 ,offer_letter_file_type
4761 ,offer_letter_file_name
4762 ,attribute_category
4763 ,attribute1
4764 ,attribute2
4765 ,attribute3
4766 ,attribute4
4767 ,attribute5
4768 ,attribute6
4769 ,attribute7
4770 ,attribute8
4771 ,attribute9
4772 ,attribute10
4773 ,attribute11
4774 ,attribute12
4775 ,attribute13
4776 ,attribute14
4777 ,attribute15
4778 ,attribute16
4779 ,attribute17
4780 ,attribute18
4781 ,attribute19
4782 ,attribute20
4783 ,attribute21
4784 ,attribute22
4785 ,attribute23
4786 ,attribute24
4787 ,attribute25
4788 ,attribute26
4789 ,attribute27
4790 ,attribute28
4791 ,attribute29
4792 ,attribute30
4793 from irc_offers
4794 where offer_id = p_offer_id;
4795
4796 l_offer_record csr_offer_record%ROWTYPE;
4797 --
4798 Begin
4799 hr_utility.set_location('Entering:'|| l_proc, 10);
4800 --
4801 -- Issue a savepoint
4802 --
4803 savepoint UPDATE_OFFER_ASSIGNMENT;
4804 --
4805 -- Truncate the time portion from all IN date parameters
4806 --
4807 l_effective_date := trunc(p_effective_date);
4808 --
4809 --
4810 open csr_prev_offer_details;
4811 fetch csr_prev_offer_details into l_prev_offer_status
4812 ,l_assignment_id;
4813 --
4814 if csr_prev_offer_details%notfound
4815 then
4816 --
4817 hr_utility.set_location(l_proc, 20);
4818 --
4819 close csr_prev_offer_details;
4820 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
4821 fnd_message.raise_error;
4822 --
4823 end if;
4824 close csr_prev_offer_details;
4825 --
4826 open csr_asg_effective_start_date;
4827 fetch csr_asg_effective_start_date into l_effective_start_date;
4828 --
4829 if csr_asg_effective_start_date%notfound
4830 then
4831 --
4832 close csr_asg_effective_start_date;
4833 fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
4834 fnd_message.raise_error;
4835 --
4836 end if;
4837 close csr_asg_effective_start_date;
4838 --
4839 if l_effective_start_date <= p_effective_date
4840 then
4841 --
4842 -- Since the current record has started today, we need to
4843 -- correct the existing record.
4844 --
4845 l_datetrack_mode := hr_api.g_correction;
4846 else
4847 --
4848 -- End the existing record and create a new record.
4849 --
4850 l_datetrack_mode := hr_api.g_update;
4851 --
4852 end if;
4853 --
4854 if l_prev_offer_status = 'HOLD'
4855 OR p_offer_status = 'HOLD'
4856 then
4857 --
4858 hr_utility.set_location(l_proc, 25);
4859 --
4860 fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
4861 fnd_message.raise_error;
4862 --
4863 elsif l_prev_offer_status in ('EXTENDED','APPROVED','CLOSED')
4864 OR p_offer_status in ('EXTENDED','APPROVED','CLOSED')
4865 then
4866 --
4867 hr_utility.set_location(l_proc, 30);
4868 --
4869 -- The code to check new offer version creation has been disabled
4870 -- since this will now be taken care of in the Java Layer itself.
4871 -- New Offer version creation code is no longer supported.
4872 --
4873 --
4874 -- l_create_new_version := true;
4875 --
4876 end if;
4877 --
4878 -- Should there be a check to see if any value in the offer assignment
4879 -- record has indeed been modified ?
4880 --
4881 if l_create_new_version = true
4882 then
4883 --
4884 hr_utility.set_location(l_proc, 40);
4885 --
4886 -- close the current offer and offer assignment.
4887 --
4888 close_offer
4889 ( p_validate => p_validate
4890 ,p_effective_date => l_effective_date
4891 ,p_offer_id => p_offer_id
4892 ,p_change_reason => 'UPDATED'
4893 ,p_status_change_date => l_effective_date
4894 );
4895 --
4896 -- create a new offer and offer assignment.
4897 --
4898 open csr_offer_record;
4899 fetch csr_offer_record into l_offer_record;
4900 --
4901 if csr_offer_record%notfound
4902 then
4903 --
4904 hr_utility.set_location(l_proc, 50);
4905 --
4906 close csr_offer_record;
4907 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
4908 fnd_message.raise_error;
4909 --
4910 end if;
4911 close csr_offer_record;
4912 --
4913 l_assignment_id := l_offer_record.offer_assignment_id;
4914 --
4915 create_offer
4916 (p_validate => p_validate
4917 ,p_effective_date => l_effective_date
4918 ,p_offer_status => 'SAVED'
4919 ,p_discretionary_job_title => l_offer_record.discretionary_job_title
4920 ,p_offer_extended_method => l_offer_record.offer_extended_method
4921 ,p_respondent_id => l_offer_record.respondent_id
4922 ,p_expiry_date => l_offer_record.expiry_date
4923 ,p_proposed_start_date => l_offer_record.proposed_start_date
4924 ,p_offer_letter_tracking_code => l_offer_record.offer_letter_tracking_code
4925 ,p_offer_postal_service => l_offer_record.offer_postal_service
4926 ,p_offer_shipping_date => l_offer_record.offer_shipping_date
4927 ,p_applicant_assignment_id => l_offer_record.applicant_assignment_id
4928 -- Get the newly created offer assignment ID
4929 ,p_offer_assignment_id => l_assignment_id
4930 ,p_address_id => l_offer_record.address_id
4931 ,p_template_id => l_offer_record.template_id
4932 ,p_offer_letter_file_type => l_offer_record.offer_letter_file_type
4933 ,p_offer_letter_file_name => l_offer_record.offer_letter_file_name
4934 ,p_attribute_category => l_offer_record.attribute_category
4935 ,p_attribute1 => l_offer_record.attribute1
4936 ,p_attribute2 => l_offer_record.attribute2
4937 ,p_attribute3 => l_offer_record.attribute3
4938 ,p_attribute4 => l_offer_record.attribute4
4939 ,p_attribute5 => l_offer_record.attribute5
4940 ,p_attribute6 => l_offer_record.attribute6
4941 ,p_attribute7 => l_offer_record.attribute7
4942 ,p_attribute8 => l_offer_record.attribute8
4943 ,p_attribute9 => l_offer_record.attribute9
4944 ,p_attribute10 => l_offer_record.attribute10
4945 ,p_attribute11 => l_offer_record.attribute11
4946 ,p_attribute12 => l_offer_record.attribute12
4947 ,p_attribute13 => l_offer_record.attribute13
4948 ,p_attribute14 => l_offer_record.attribute14
4949 ,p_attribute15 => l_offer_record.attribute15
4950 ,p_attribute16 => l_offer_record.attribute16
4951 ,p_attribute17 => l_offer_record.attribute17
4952 ,p_attribute18 => l_offer_record.attribute18
4953 ,p_attribute19 => l_offer_record.attribute19
4954 ,p_attribute20 => l_offer_record.attribute20
4955 ,p_attribute21 => l_offer_record.attribute21
4956 ,p_attribute22 => l_offer_record.attribute22
4957 ,p_attribute23 => l_offer_record.attribute23
4958 ,p_attribute24 => l_offer_record.attribute24
4959 ,p_attribute25 => l_offer_record.attribute25
4960 ,p_attribute26 => l_offer_record.attribute26
4961 ,p_attribute27 => l_offer_record.attribute27
4962 ,p_attribute28 => l_offer_record.attribute28
4963 ,p_attribute29 => l_offer_record.attribute29
4964 ,p_attribute30 => l_offer_record.attribute30
4965 ,p_status_change_date => l_effective_date
4966 ,p_offer_id => l_offer_id
4967 ,p_offer_version => l_offer_version
4968 ,p_object_version_number => l_iof_object_version_number
4969 );
4970 --
4971 -- Since a new offer assignment record has been created, we will
4972 -- be updating the assignment_record again on the same day of creation.
4973 -- We hence need to set the date track mode to 'hr_api.g_correction'
4974 --
4975 l_datetrack_mode := hr_api.g_correction;
4976 --
4977 end if; -- l_create_new_version
4978 --
4979 hr_utility.set_location(l_proc, 60);
4980 --
4981 -- update the offer assignment record.
4982 --
4983 per_asg_upd.upd
4984 ( p_assignment_id => l_assignment_id
4985 ,p_effective_start_date => l_effective_start_date
4986 ,p_effective_end_date => l_effective_end_date
4987 ,p_business_group_id => l_business_group_id
4988
4989 ,p_recruiter_id => p_recruiter_id
4990 ,p_grade_id => p_grade_id
4991 ,p_position_id => p_position_id
4992 ,p_job_id => p_job_id
4993 ,p_assignment_status_type_id => p_assignment_status_type_id
4994 ,p_payroll_id => p_payroll_id
4995 ,p_location_id => p_location_id
4996 ,p_person_referred_by_id => p_person_referred_by_id
4997 ,p_supervisor_id => p_supervisor_id
4998 ,p_special_ceiling_step_id => p_special_ceiling_step_id
4999 ,p_recruitment_activity_id => p_recruitment_activity_id
5000 ,p_source_organization_id => p_source_organization_id
5001
5002 ,p_organization_id => p_organization_id
5003 ,p_people_group_id => p_people_group_id
5004 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
5005 ,p_vacancy_id => p_vacancy_id
5006 ,p_pay_basis_id => p_pay_basis_id
5007 ,p_assignment_type => p_assignment_type
5008 ,p_primary_flag => p_primary_flag
5009 ,p_application_id => p_application_id
5010 ,p_assignment_number => p_assignment_number
5011 ,p_change_reason => p_change_reason
5012 ,p_comment_id => l_comment_id
5013 ,p_comments => p_comments
5014 ,p_date_probation_end => p_date_probation_end
5015
5016 ,p_default_code_comb_id => p_default_code_comb_id
5017 ,p_employment_category => p_employment_category
5018 ,p_frequency => p_frequency
5019 ,p_internal_address_line => p_internal_address_line
5020 ,p_manager_flag => p_manager_flag
5021 ,p_normal_hours => p_normal_hours
5022 ,p_perf_review_period => p_perf_review_period
5023 ,p_perf_review_period_frequency => p_perf_review_period_frequency
5024 ,p_period_of_service_id => p_period_of_service_id
5025 ,p_probation_period => p_probation_period
5026 ,p_probation_unit => p_probation_unit
5027 ,p_sal_review_period => p_sal_review_period
5028 ,p_sal_review_period_frequency => p_sal_review_period_frequency
5029 ,p_set_of_books_id => p_set_of_books_id
5030
5031 ,p_source_type => p_source_type
5032 ,p_time_normal_finish => p_time_normal_finish
5033 ,p_time_normal_start => p_time_normal_start
5034 ,p_bargaining_unit_code => p_bargaining_unit_code
5035 ,p_labour_union_member_flag => p_labour_union_member_flag
5036 ,p_hourly_salaried_code => p_hourly_salaried_code
5037 ,p_request_id => p_request_id
5038 ,p_program_application_id => p_program_application_id
5039 ,p_program_id => p_program_id
5040 ,p_program_update_date => p_program_update_date
5041 ,p_ass_attribute_category => p_ass_attribute_category
5042 ,p_ass_attribute1 => p_ass_attribute1
5043 ,p_ass_attribute2 => p_ass_attribute2
5044 ,p_ass_attribute3 => p_ass_attribute3
5045 ,p_ass_attribute4 => p_ass_attribute4
5046 ,p_ass_attribute5 => p_ass_attribute5
5047
5048 ,p_ass_attribute6 => p_ass_attribute6
5049 ,p_ass_attribute7 => p_ass_attribute7
5050 ,p_ass_attribute8 => p_ass_attribute8
5051 ,p_ass_attribute9 => p_ass_attribute9
5052 ,p_ass_attribute10 => p_ass_attribute10
5053 ,p_ass_attribute11 => p_ass_attribute11
5054 ,p_ass_attribute12 => p_ass_attribute12
5055 ,p_ass_attribute13 => p_ass_attribute13
5056 ,p_ass_attribute14 => p_ass_attribute14
5057 ,p_ass_attribute15 => p_ass_attribute15
5058 ,p_ass_attribute16 => p_ass_attribute16
5059 ,p_ass_attribute17 => p_ass_attribute17
5060 ,p_ass_attribute18 => p_ass_attribute18
5061
5062 ,p_ass_attribute19 => p_ass_attribute19
5063 ,p_ass_attribute20 => p_ass_attribute20
5064 ,p_ass_attribute21 => p_ass_attribute21
5065 ,p_ass_attribute22 => p_ass_attribute22
5066 ,p_ass_attribute23 => p_ass_attribute23
5067 ,p_ass_attribute24 => p_ass_attribute24
5068 ,p_ass_attribute25 => p_ass_attribute25
5069 ,p_ass_attribute26 => p_ass_attribute26
5070 ,p_ass_attribute27 => p_ass_attribute27
5071 ,p_ass_attribute28 => p_ass_attribute28
5072 ,p_ass_attribute29 => p_ass_attribute29
5073 ,p_ass_attribute30 => p_ass_attribute30
5074 ,p_title => p_title
5075 ,p_contract_id => p_contract_id
5076 ,p_establishment_id => p_establishment_id
5077 ,p_collective_agreement_id => p_collective_agreement_id
5078 ,p_cagr_grade_def_id => p_cagr_grade_def_id
5079 ,p_cagr_id_flex_num => p_cagr_id_flex_num
5080 ,p_object_version_number => l_asg_object_version_number
5081 ,p_notice_period => p_notice_period
5082 ,p_notice_period_uom => p_notice_period_uom
5083 ,p_employee_category => p_employee_category
5084 ,p_work_at_home => p_work_at_home
5085 ,p_job_post_source_name => p_job_post_source_name
5086 ,p_posting_content_id => p_posting_content_id
5087 ,p_placement_date_start => p_placement_date_start
5088 ,p_vendor_id => p_vendor_id
5089 ,p_vendor_employee_number => p_vendor_employee_number
5090 ,p_vendor_assignment_number => p_vendor_assignment_number
5091 ,p_assignment_category => p_assignment_category
5092 ,p_project_title => p_project_title
5093 ,p_applicant_rank => p_applicant_rank
5094 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
5095 ,p_supervisor_assignment_id => p_supervisor_assignment_id
5096 ,p_vendor_site_id => p_vendor_site_id
5097 ,p_po_header_id => p_po_header_id
5098 ,p_po_line_id => p_po_line_id
5099 ,p_projected_assignment_end => p_projected_assignment_end
5100 ,p_payroll_id_updated => l_payroll_id_updated
5101 ,p_other_manager_warning => l_other_manager_warning
5102 ,p_hourly_salaried_warning => l_hourly_salaried_warning
5103 ,p_no_managers_warning => l_no_managers_warning
5104 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
5105 ,p_validation_start_date => l_validation_start_date
5106 ,p_validation_end_date => l_validation_end_date
5107 ,p_effective_date => l_effective_date
5108 ,p_datetrack_mode => l_datetrack_mode
5109 ,p_validate => p_validate
5110 );
5111 --
5112 --
5113 -- When in validation only mode raise the Validate_Enabled exception
5114 --
5115 if p_validate then
5116 raise hr_api.validate_enabled;
5117 end if;
5118 --
5119 -- Set all IN OUT and OUT parameters with out values
5120 --
5121 p_assignment_id := l_assignment_id;
5122 p_effective_start_date := l_effective_start_date;
5123 p_effective_end_date := l_effective_end_date;
5124 p_business_group_id := l_business_group_id;
5125 p_comment_id := l_comment_id;
5126 p_payroll_id_updated := l_payroll_id_updated;
5127 p_other_manager_warning := l_other_manager_warning;
5128 p_hourly_salaried_warning := l_hourly_salaried_warning;
5129 p_no_managers_warning := l_no_managers_warning;
5130 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
5131 p_validation_start_date := l_validation_start_date;
5132 p_validation_end_date := l_validation_end_date;
5133 p_offer_id := l_offer_id;
5134 p_asg_object_version_number := l_asg_object_version_number;
5135
5136 hr_utility.set_location(' Leaving:'||l_proc, 145);
5137 --
5138 exception
5139 when hr_api.validate_enabled then
5140 --
5141 -- As the Validate_Enabled exception has been raised
5142 -- we must rollback to the savepoint
5143 --
5144 rollback to UPDATE_OFFER_ASSIGNMENT;
5145 --
5146 -- Reset IN OUT parameters and set OUT parameters
5147 -- (Any key or derived arguments must be set to null
5148 -- when validation only mode is being used.)
5149 --
5150 p_assignment_id := l_assignment_id;
5151 p_effective_start_date := l_effective_start_date;
5152 p_effective_end_date := l_effective_end_date;
5153 p_business_group_id := l_business_group_id;
5154 p_comment_id := l_comment_id;
5155 p_payroll_id_updated := l_payroll_id_updated;
5156 p_other_manager_warning := l_other_manager_warning;
5157 p_hourly_salaried_warning := l_hourly_salaried_warning;
5158 p_no_managers_warning := l_no_managers_warning;
5159 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
5160 p_validation_start_date := l_validation_start_date;
5161 p_validation_end_date := l_validation_end_date;
5162 p_offer_id := l_offer_id;
5163 p_asg_object_version_number := l_asg_object_version_number;
5164 --
5165 hr_utility.set_location(' Leaving:'||l_proc, 150);
5166 when others then
5167 --
5168 -- Reset IN OUT parameters and set all
5169 -- OUT parameters, including warnings, to null
5170 --
5171 p_assignment_id := l_assignment_id;
5172 p_effective_start_date := l_effective_start_date;
5173 p_effective_end_date := l_effective_end_date;
5174 p_business_group_id := l_business_group_id;
5175 p_comment_id := l_comment_id;
5176 p_payroll_id_updated := l_payroll_id_updated;
5177 p_other_manager_warning := l_other_manager_warning;
5178 p_hourly_salaried_warning := l_hourly_salaried_warning;
5179 p_no_managers_warning := l_no_managers_warning;
5180 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
5181 p_validation_start_date := l_validation_start_date;
5182 p_validation_end_date := l_validation_end_date;
5183 p_offer_id := l_offer_id;
5184 p_asg_object_version_number := l_asg_object_version_number;
5185 --
5186 -- A validation or unexpected error has occured
5187 --
5188 rollback to UPDATE_OFFER_ASSIGNMENT;
5189 --
5190 hr_utility.set_location(' Leaving:'||l_proc, 160);
5191 raise;
5192 end update_offer_assignment;
5193 --
5194 --
5195 -- ----------------------------------------------------------------------------
5196 -- |-----------------------< delete_offer_assignment >------------------------|
5197 -- ----------------------------------------------------------------------------
5198 --
5199 procedure delete_offer_assignment
5200 ( P_VALIDATE IN boolean default false
5201 ,P_EFFECTIVE_DATE IN date default null
5202 ,P_OFFER_ASSIGNMENT_ID IN number
5203 ) Is
5204 --
5205 l_proc varchar2(72) := g_package||'delete_offer_assignment';
5206 l_offer_id irc_offers.offer_id%TYPE;
5207 l_offer_object_version_number irc_offers.object_version_number%TYPE;
5208 --
5209 cursor csr_offer_id is
5210 select offer_id
5211 ,object_version_number
5212 from irc_offers
5213 where offer_assignment_id = p_offer_assignment_id;
5214 --
5215 Begin
5216 hr_utility.set_location('Entering:'|| l_proc, 10);
5217 --
5218 -- Issue a savepoint
5219 --
5220 savepoint DELETE_OFFER_ASSIGNMENT;
5221 --
5222 open csr_offer_id;
5223 fetch csr_offer_id into l_offer_id,l_offer_object_version_number;
5224 --
5225 if csr_offer_id%notfound
5226 then
5227 --
5228 hr_utility.set_location(l_proc, 20);
5229 --
5230 close csr_offer_id;
5231 fnd_message.set_name('PER','IRC_412322_INVALID_OFFER_ID');
5232 fnd_message.raise_error;
5233 --
5234 end if;
5235 close csr_offer_id;
5236 --
5237 -- If the offer is valid, call the delete procedure of offer
5238 -- so that both the offer record and the offer assignment
5239 -- record are deleted.
5240 --
5241 hr_utility.set_location(l_proc, 30);
5242 --
5243 delete_offer
5244 ( p_validate => p_validate
5245 ,p_object_version_number => l_offer_object_version_number
5246 ,p_offer_id => l_offer_id
5247 ,p_effective_date => p_effective_date
5248 );
5249 --
5250 --
5251 -- When in validation only mode raise the Validate_Enabled exception
5252 --
5253 if p_validate then
5254 raise hr_api.validate_enabled;
5255 end if;
5256 --
5257 hr_utility.set_location(' Leaving:'||l_proc, 40);
5258 --
5259 exception
5260 when hr_api.validate_enabled then
5261 --
5262 -- As the Validate_Enabled exception has been raised
5263 -- we must rollback to the savepoint
5264 --
5265 rollback to DELETE_OFFER_ASSIGNMENT;
5266 --
5267 -- Only set output warning arguments
5268 -- (Any key or derived arguments must be set to null
5269 -- when validation only mode is being used.)
5270 --
5271 hr_utility.set_location(' Leaving:'||l_proc, 60);
5272 when others then
5273 --
5274 -- A validation or unexpected error has occured
5275 --
5276 rollback to DELETE_OFFER_ASSIGNMENT;
5277 hr_utility.set_location(' Leaving:'||l_proc, 70);
5278 raise;
5279 end delete_offer_assignment;
5280 --
5281 --
5282 -- ----------------------------------------------------------------------------
5283 -- |------------------------< upload_offer_letter >---------------------------|
5284 -- ----------------------------------------------------------------------------
5285 --
5286 procedure upload_offer_letter
5287 ( P_VALIDATE IN boolean default false
5288 ,P_OFFER_LETTER IN BLOB
5289 ,P_OFFER_ID IN NUMBER
5290 ,P_OBJECT_VERSION_NUMBER IN NUMBER
5291 ) Is
5292 --
5293 l_proc varchar2(72) := g_package||'upload_offer_letter';
5294 --
5295 Begin
5296 hr_utility.set_location('Entering:'|| l_proc, 10);
5297 --
5298 -- Issue a savepoint
5299 --
5300 savepoint UPLOAD_OFFER_LETTER;
5301 --
5302 -- Call blob_dml to pass on the blob to be uploaded
5303 --
5304 hr_utility.set_location(l_proc, 30);
5305 --
5306 irc_iof_shd.blob_dml
5307 ( p_offer_letter => p_offer_letter
5308 ,p_offer_id => p_offer_id
5309 ,p_object_version_number => p_object_version_number
5310 );
5311 --
5312 -- When in validation only mode raise the Validate_Enabled exception
5313 --
5314 if p_validate then
5315 raise hr_api.validate_enabled;
5316 end if;
5317 --
5318 hr_utility.set_location(' Leaving:'||l_proc, 40);
5319 exception
5320 when hr_api.validate_enabled then
5321 --
5322 -- As the Validate_Enabled exception has been raised
5323 -- we must rollback to the savepoint
5324 --
5325 rollback to UPLOAD_OFFER_LETTER;
5326 --
5327 hr_utility.set_location(' Leaving:'||l_proc, 50);
5328 when others then
5329 --
5330 -- A validation or unexpected error has occured
5331 --
5332 rollback to UPLOAD_OFFER_LETTER;
5333 hr_utility.set_location(' Leaving:'||l_proc, 60);
5334 raise;
5335 end upload_offer_letter;
5336 --
5337 end IRC_OFFERS_API;