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