[Home] [Help]
PACKAGE BODY: APPS.HR_INTERVIEW
Source
1 PACKAGE BODY hr_interview AS
2 /* $Header: peintviw.pkb 120.1 2006/01/13 06:10:33 irgonzal noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : hr_interview (BODY)
24
25 Description : This package declares procedures required to
26 INSERT, UPDATE and DELETE Assignment Statuses for
27 Applicant Interviews called from PERREAB.
28
29 Change List
30 -----------
31
32 Version Date Author ER/CR No. Description of Change
33 -------+---------+----------+---------+-----------------------
34 70.0 09-FEB-93 PShergill Date Created
35 70.1 11-MAR-93 Nkhan Added 'exit' to the end
36 70.2 17-AUG-93 Nkhan Added SOURCE_TYPE field
37 references to per_assignments_f
38 70.3 16-JUN-94 PShergill Fix 220466 added ATTRIBUTE21..30
39 70.11 23-NOV-94 RFine Suppressed index on business_group_id
40 115.3 23-DEC-03 bsubrama Bug 3333891 - Changed the NVL values
41 for salary rewiew period, performance
42 review period and pay basis to -99999
43 rather than ' '. Also made GSCC
44 compliant.
45 115.4 13-Jan-06 irgonzal Pef bug 4894555. Added function
46 chk_duplicate.
47 ================================================================= */
48
49 --
50 --
51 ------------------- insert_interview -----------------------------
52 /*
53 NAME
54 insert_interview
55
56 DESCRIPTION
57 Inserts an assignment of type specified in the paramenter list
58 starting from applicant interview start date
59 PARAMETERS
60 p_assignment_id - assignment_id of applicant
61 p_idate - New Interview Date
62 p_assignment_status_type_id - Assignment Status Type Id of Interview
63 p_last_updated_by - Required for Auditing
64 p_last_update_login - Required for Auditing
65 */
66 PROCEDURE insert_interview
67 (
68 p_assignment_id IN INTEGER,
69 p_idate IN DATE,
70 p_assignment_status_type_id IN INTEGER,
71 p_last_updated_by IN INTEGER,
72 p_last_update_login IN INTEGER
73 ) IS
74 -----------------------------------------------------------
75 -- DECLARE THE LOCAL VARIABLES
76 -----------------------------------------------------------
77 p_int_date DATE;
78 p_dummy VARCHAR2(1);
79 --
80 CURSOR select_ass_for_insert IS
81 SELECT *
82 FROM per_assignments_f
83 WHERE assignment_id = p_assignment_id
84 FOR UPDATE;
85 --
86 --
87 BEGIN
88 --
89 p_int_date := p_idate;
90 --
91 BEGIN
92 --
93 hr_utility.set_location('hr_interview.insert_interview',1);
94 --
95 -- This for loop has a purpose to the lock all the assignment records
96 -- specified by the cursor
97 --
98 FOR ass_rec_ins IN select_ass_for_insert LOOP
99 NULL;
100 END LOOP;
101 --
102 hr_utility.set_location('hr_interview.insert_interview',2);
103 --
104 SELECT 'Y'
105 INTO p_dummy
106 FROM sys.dual
107 WHERE EXISTS
108 ( SELECT '1'
109 FROM per_assignments_f
110 WHERE assignment_id = p_assignment_id
111 AND ((effective_end_date <> to_date('31/12/4712','DD/MM/YYYY')
112 AND effective_end_date >= p_int_date)
113 OR (effective_start_date = p_int_date)));
114 --
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN NULL;
117 END;
118 --
119 IF p_dummy = 'Y' THEN
120 hr_utility.set_message(801,'HR_6456_APP_ASS_FUTURE_CHANGES');
121 hr_utility.raise_error;
122 END IF;
123 --
124 -- Insert the Interview
125 hr_utility.set_location('hr_interview.insert_interview',3);
126 --
127 INSERT INTO per_assignments_f
131 ,effective_end_date
128 (
129 assignment_id
130 ,effective_start_date
132 ,business_group_id
133 ,grade_id
134 ,position_id
135 ,job_id
136 ,assignment_status_type_id
137 ,payroll_id
138 ,location_id
139 ,person_referred_by_id
140 ,person_id
141 ,recruitment_activity_id
142 ,source_organization_id
143 ,organization_id
144 ,people_group_id
145 ,soft_coding_keyflex_id
146 ,vacancy_id
147 ,assignment_sequence
148 ,assignment_type
149 ,manager_flag
150 ,primary_flag
151 ,application_id
152 ,assignment_number
153 ,change_reason
154 ,comment_id
155 ,date_probation_end
156 ,default_code_comb_id
157 ,frequency
158 ,internal_address_line
159 ,normal_hours
160 ,period_of_service_id
161 ,probation_period
162 ,probation_unit
163 ,recruiter_id
164 ,set_of_books_id
165 ,special_ceiling_step_id
166 ,supervisor_id
167 ,time_normal_finish
168 ,time_normal_start
169 ,request_id
170 ,program_application_id
171 ,program_id
172 ,program_update_date
173 ,ass_attribute_category
174 ,ass_attribute1
175 ,ass_attribute2
176 ,ass_attribute3
177 ,ass_attribute4
178 ,ass_attribute5
179 ,ass_attribute6
180 ,ass_attribute7
181 ,ass_attribute8
182 ,ass_attribute9
183 ,ass_attribute10
184 ,ass_attribute11
185 ,ass_attribute12
186 ,ass_attribute13
187 ,ass_attribute14
188 ,ass_attribute15
189 ,ass_attribute16
190 ,ass_attribute17
191 ,ass_attribute18
192 ,ass_attribute19
193 ,ass_attribute20
194 ,ass_attribute21
195 ,ass_attribute22
196 ,ass_attribute23
197 ,ass_attribute24
198 ,ass_attribute25
199 ,ass_attribute26
200 ,ass_attribute27
201 ,ass_attribute28
202 ,ass_attribute29
203 ,ass_attribute30
204 ,sal_review_period
205 ,sal_review_period_frequency
206 ,perf_review_period
207 ,perf_review_period_frequency
208 ,pay_basis_id
209 ,employment_category
210 ,bargaining_unit_code
211 ,labour_union_member_flag
212 ,hourly_salaried_code
213 ,last_update_date
214 ,last_updated_by
215 ,last_update_login
216 ,created_by
217 ,creation_date
218 ,source_type
219 )
220 SELECT
221 assignment_id
222 ,effective_start_date
223 ,p_int_date - 1
224 ,business_group_id
225 ,grade_id
226 ,position_id
227 ,job_id
228 ,assignment_status_type_id
229 ,payroll_id
230 ,location_id
231 ,person_referred_by_id
232 ,person_id
233 ,recruitment_activity_id
234 ,source_organization_id
235 ,organization_id
236 ,people_group_id
237 ,soft_coding_keyflex_id
238 ,vacancy_id
239 ,assignment_sequence
240 ,assignment_type
241 ,manager_flag
242 ,primary_flag
243 ,application_id
244 ,assignment_number
245 ,change_reason
246 ,comment_id
247 ,date_probation_end
248 ,default_code_comb_id
249 ,frequency
250 ,internal_address_line
251 ,normal_hours
252 ,period_of_service_id
253 ,probation_period
254 ,probation_unit
255 ,recruiter_id
256 ,set_of_books_id
257 ,special_ceiling_step_id
258 ,supervisor_id
259 ,time_normal_finish
260 ,time_normal_start
261 ,request_id
262 ,program_application_id
263 ,program_id
264 ,program_update_date
265 ,ass_attribute_category
266 ,ass_attribute1
267 ,ass_attribute2
268 ,ass_attribute3
269 ,ass_attribute4
270 ,ass_attribute5
271 ,ass_attribute6
272 ,ass_attribute7
273 ,ass_attribute8
274 ,ass_attribute9
275 ,ass_attribute10
276 ,ass_attribute11
277 ,ass_attribute12
278 ,ass_attribute13
279 ,ass_attribute14
280 ,ass_attribute15
281 ,ass_attribute16
282 ,ass_attribute17
283 ,ass_attribute18
284 ,ass_attribute19
285 ,ass_attribute20
286 ,ass_attribute21
287 ,ass_attribute22
288 ,ass_attribute23
289 ,ass_attribute24
290 ,ass_attribute25
291 ,ass_attribute26
292 ,ass_attribute27
293 ,ass_attribute28
294 ,ass_attribute29
295 ,ass_attribute30
296 ,sal_review_period
297 ,sal_review_period_frequency
298 ,perf_review_period
299 ,perf_review_period_frequency
300 ,pay_basis_id
301 ,employment_category
302 ,bargaining_unit_code
303 ,labour_union_member_flag
304 ,hourly_salaried_code
305 ,last_update_date
306 ,last_updated_by
307 ,last_update_login
308 ,created_by
309 ,creation_date
310 ,source_type
311 FROM per_assignments_f
312 WHERE assignment_id = p_assignment_id
313 AND p_int_date
314 BETWEEN effective_start_date and effective_end_date;
315 --
316 IF SQL%ROWCOUNT <> 1 THEN
317 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
318 hr_utility.set_message_token('PROCEDURE','INSERT_INTERVIEW');
319 hr_utility.set_message_token('STEP','3');
320 hr_utility.raise_error;
321 ELSE
322 hr_utility.set_location('hr_interview.insert_interview',4);
323 --
324 UPDATE per_assignments_f
325 SET effective_start_date = p_int_date
326 , assignment_status_type_id = p_assignment_status_type_id
327 , last_updated_by = p_last_updated_by
328 , last_update_login = p_last_update_login
329 , last_update_date = sysdate
330 WHERE assignment_id = p_assignment_id
331 AND p_int_date
332 BETWEEN effective_start_date and effective_end_date;
333 --
334 IF SQL%ROWCOUNT <> 1 THEN
335 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
336 hr_utility.set_message_token('PROCEDURE','INSERT_INTERVIEW');
337 hr_utility.set_message_token('STEP','4');
338 hr_utility.raise_error;
339 END IF;
340 END IF;
341 -- End the insert
342 END insert_interview;
343 --
344 --
345 -- --------------------------------------------------------------+
346 -- ---------------<< chk_duplicate >>----------------------------|
347 -- --------------------------------------------------------------+
348 -- Returns 'Y' if previous record matches current record.
349 -- Otherwise, it returns 'N'.
350 --
351 -- Parameters:
352 -- Name Description
353 -- --------------- -------------------------------------------
354 -- p_assignment_id Uniquely identifies an assignment
355 -- p_effective_date Date to be used to compare records
356 -- p_use_asg_date Determines how to retrieve the previous
357 -- record. If TRUE, it uses start date of
358 -- current assignment, if FALSE it uses the
359 -- effective date - 1.
360 --
361 FUNCTION chk_duplicate (p_assignment_id IN number
362 ,p_effective_date IN date
363 ,p_use_asg_date IN boolean) return varchar2 is
364 --
365 l_results varchar2(10);
366 --
367 cursor csr_asg is
368 SELECT *
369 FROM per_assignments_f c
370 WHERE c.assignment_id = p_assignment_id
371 AND p_effective_date BETWEEN
372 c.effective_start_date AND c.effective_end_date;
373 --
374 cursor csr_prev_asg(cp_asg_id number, cp_effective_date date, cp_bg_id number) is
375 SELECT *
376 FROM per_assignments_f o
377 WHERE o.assignment_id = cp_asg_id
378 AND o.effective_end_date = cp_effective_date
379 AND o.business_group_id + 0 = cp_bg_id + 0;
380 --
381 l_prev_rec csr_prev_asg%ROWTYPE;
382 l_current_asg_rec csr_asg%ROWTYPE;
383 --
384 begin
385 l_results := 'N';
386 --
387 open csr_asg;
388 fetch csr_asg into l_current_asg_rec;
389 if csr_asg%FOUND then
390 close csr_asg;
391 if p_use_asg_date then
392 open csr_prev_asg(l_current_asg_rec.assignment_id,
393 l_current_asg_rec.effective_start_date -1,
397 p_effective_date -1,
394 l_current_asg_rec.business_group_id);
395 else
396 open csr_prev_asg(l_current_asg_rec.assignment_id,
398 l_current_asg_rec.business_group_id);
399 end if;
400 LOOP
401 fetch csr_prev_asg into l_prev_rec;
402 exit when csr_prev_asg%NOTFOUND or l_results = 'Y';
403 if NVL(l_prev_rec.grade_id,-99999) = NVL(l_current_asg_rec.grade_id,-99999)
404 AND NVL(l_prev_rec.position_id,-99999) = NVL(l_current_asg_rec.position_id,-99999)
405 AND NVL(l_prev_rec.job_id,-99999) = NVL(l_current_asg_rec.job_id,-99999)
406 AND NVL(l_prev_rec.payroll_id,-99999) = NVL(l_current_asg_rec.payroll_id,-99999)
407 AND NVL(l_prev_rec.location_id,-99999) = NVL(l_current_asg_rec.location_id,-99999)
408 AND NVL(l_prev_rec.person_referred_by_id,-99999)
409 = NVL(l_current_asg_rec.person_referred_by_id,-99999)
410 AND l_prev_rec.person_id = l_current_asg_rec.person_id
411 AND NVL(l_prev_rec.recruitment_activity_id,-99999)
412 = NVL(l_current_asg_rec.recruitment_activity_id,-99999)
413 AND NVL(l_prev_rec.source_organization_id,-99999)
414 = NVL(l_current_asg_rec.source_organization_id,-99999)
415 AND l_prev_rec.organization_id = l_current_asg_rec.organization_id
416 AND NVL(l_prev_rec.people_group_id,-99999) = NVL(l_current_asg_rec.people_group_id,-99999)
417 AND NVL(l_prev_rec.soft_coding_keyflex_id,-99999)
418 = NVL(l_current_asg_rec.soft_coding_keyflex_id,-99999)
419 AND NVL(l_prev_rec.vacancy_id,-99999) = NVL(l_current_asg_rec.vacancy_id,-99999)
420 AND l_prev_rec.assignment_sequence = l_current_asg_rec.assignment_sequence
421 AND l_prev_rec.assignment_type = l_current_asg_rec.assignment_type
422 AND l_prev_rec.manager_flag = l_current_asg_rec.manager_flag
423 AND l_prev_rec.primary_flag = l_current_asg_rec.primary_flag
424 AND NVL(l_prev_rec.application_id,-99999) = NVL(l_current_asg_rec.application_id,-99999)
425 AND NVL(l_prev_rec.assignment_number,' ') = NVL(l_current_asg_rec.assignment_number,' ')
426 AND NVL(l_prev_rec.change_reason,' ') = NVL(l_current_asg_rec.change_reason,' ')
427 AND NVL(l_prev_rec.comment_id,-99999) = NVL(l_current_asg_rec.comment_id,-99999)
428 AND NVL(l_prev_rec.date_probation_end,to_date('01/01/0001','DD/MM/YYYY'))
429 = NVL(l_current_asg_rec.date_probation_end,to_date('01/01/0001','DD/MM/YYYY'))
430 AND NVL(l_prev_rec.default_code_comb_id,-99999)
431 = NVL(l_current_asg_rec.default_code_comb_id,-99999)
432 AND NVL(l_prev_rec.frequency,' ') = NVL(l_current_asg_rec.frequency,' ')
433 AND NVL(l_prev_rec.internal_address_line,' ') = NVL(l_current_asg_rec.internal_address_line,' ')
434 AND NVL(l_prev_rec.normal_hours,-99999.99) = NVL(l_current_asg_rec.normal_hours,-99999.99)
435 AND NVL(l_prev_rec.period_of_service_id,-99999)
436 = NVL(l_current_asg_rec.period_of_service_id,-99999)
437 AND NVL(l_prev_rec.probation_period,-99999.99)
438 = NVL(l_current_asg_rec.probation_period,-99999.99)
439 AND NVL(l_prev_rec.probation_unit,' ') = NVL(l_current_asg_rec.probation_unit,' ')
440 AND NVL(l_prev_rec.recruiter_id,-99999) = NVL(l_current_asg_rec.recruiter_id,-99999)
441 AND NVL(l_prev_rec.set_of_books_id,-99999) = NVL(l_current_asg_rec.set_of_books_id,-99999)
442 AND NVL(l_prev_rec.special_ceiling_step_id,-99999)
443 = NVL(l_current_asg_rec.special_ceiling_step_id,-99999)
444 AND NVL(l_prev_rec.supervisor_id,-99999) = NVL(l_current_asg_rec.supervisor_id,-99999)
445 AND NVL(l_prev_rec.time_normal_finish,' ') = NVL(l_current_asg_rec.time_normal_finish,' ')
446 AND NVL(l_prev_rec.time_normal_start,' ') = NVL(l_current_asg_rec.time_normal_start,' ')
447 AND NVL(l_prev_rec.source_type,' ') = NVL(l_current_asg_rec.source_type,' ')
448 AND NVL(l_prev_rec.ass_attribute_category,' ')
449 = NVL(l_current_asg_rec.ass_attribute_category,' ')
450 AND NVL(l_prev_rec.ass_attribute1|| l_prev_rec.ass_attribute2|| l_prev_rec.ass_attribute3||
451 l_prev_rec.ass_attribute4|| l_prev_rec.ass_attribute5|| l_prev_rec.ass_attribute6||
452 l_prev_rec.ass_attribute7|| l_prev_rec.ass_attribute8|| l_prev_rec.ass_attribute9||
453 l_prev_rec.ass_attribute10|| l_prev_rec.ass_attribute11|| l_prev_rec.ass_attribute12||
454 l_prev_rec.ass_attribute13|| l_prev_rec.ass_attribute14|| l_prev_rec.ass_attribute15||
455 l_prev_rec.ass_attribute16|| l_prev_rec.ass_attribute17|| l_prev_rec.ass_attribute18||
456 l_prev_rec.ass_attribute19|| l_prev_rec.ass_attribute20|| l_prev_rec.ass_attribute21||
457 l_prev_rec.ass_attribute22|| l_prev_rec.ass_attribute23|| l_prev_rec.ass_attribute24||
458 l_prev_rec.ass_attribute25|| l_prev_rec.ass_attribute26|| l_prev_rec.ass_attribute27||
459 l_prev_rec.ass_attribute28|| l_prev_rec.ass_attribute29|| l_prev_rec.ass_attribute30,' ') =
460 NVL(l_current_asg_rec.ass_attribute1|| l_current_asg_rec.ass_attribute2|| l_current_asg_rec.ass_attribute3||
461 l_current_asg_rec.ass_attribute4|| l_current_asg_rec.ass_attribute5|| l_current_asg_rec.ass_attribute6||
462 l_current_asg_rec.ass_attribute7|| l_current_asg_rec.ass_attribute8|| l_current_asg_rec.ass_attribute9||
463 l_current_asg_rec.ass_attribute10|| l_current_asg_rec.ass_attribute11|| l_current_asg_rec.ass_attribute12||
464 l_current_asg_rec.ass_attribute13|| l_current_asg_rec.ass_attribute14|| l_current_asg_rec.ass_attribute15||
465 l_current_asg_rec.ass_attribute16|| l_current_asg_rec.ass_attribute17|| l_current_asg_rec.ass_attribute18||
469 l_current_asg_rec.ass_attribute28|| l_current_asg_rec.ass_attribute29|| l_current_asg_rec.ass_attribute30,' ')
466 l_current_asg_rec.ass_attribute19|| l_current_asg_rec.ass_attribute20|| l_current_asg_rec.ass_attribute21||
467 l_current_asg_rec.ass_attribute22|| l_current_asg_rec.ass_attribute23|| l_current_asg_rec.ass_attribute24||
468 l_current_asg_rec.ass_attribute25|| l_current_asg_rec.ass_attribute26|| l_current_asg_rec.ass_attribute27||
470 AND NVL(l_prev_rec.sal_review_period,-99999) = NVL(l_current_asg_rec.sal_review_period,-99999) -- Bug 3333891
471 AND NVL(l_prev_rec.sal_review_period_frequency,' ') = NVL(l_current_asg_rec.sal_review_period_frequency,' ')
472 AND NVL(l_prev_rec.perf_review_period,-99999) = NVL(l_current_asg_rec.perf_review_period,-99999) -- Bug 3333891
473 AND NVL(l_prev_rec.perf_review_period_frequency,' ') = NVL(l_current_asg_rec.perf_review_period_frequency,' ')
474 AND NVL(l_prev_rec.pay_basis_id,-99999) = NVL(l_current_asg_rec.pay_basis_id,-99999) -- Bug 3333891
475 AND NVL(l_prev_rec.employment_category,' ') = NVL(l_current_asg_rec.employment_category,' ')
476 AND NVL(l_prev_rec.bargaining_unit_code,' ') = NVL(l_current_asg_rec.bargaining_unit_code,' ')
477 AND NVL(l_prev_rec.labour_union_member_flag,' ') = NVL(l_current_asg_rec.labour_union_member_flag,' ')
478 AND NVL(l_prev_rec.hourly_salaried_code,' ') = NVL(l_current_asg_rec.hourly_salaried_code,' ')
479 THEN
480 l_results := 'Y';
481 END IF;
482
483 end loop;
484 close csr_prev_asg;
485 else
486 close csr_asg;
487 end if;
488 --
489 RETURN l_results;
490 --
491 END chk_duplicate;
492 --
493 ------------------- delete_interview -----------------------------
494 /*
495 NAME
496 delete_interview
497 DESCRIPTION
498 Deletes assignment for associated applicant interview
499 PARAMETERS
500 p_assignment_id - assignment_id of applicant
501 p_idate - New Interview Date
502 p_last_updated_by - Required for Auditing
503 p_last_update_login - Required for Auditing
504 */
505 PROCEDURE delete_interview
506 (p_assignment_id IN INTEGER,
507 p_idate IN DATE,
508 p_last_updated_by IN INTEGER,
509 p_last_update_login IN INTEGER
510 ) IS
511 -----------------------------------------------------------
512 -- DECLARE THE LOCAL VARIABLES
513 -----------------------------------------------------------
514 p_int_date DATE;
515 p_dummy_date DATE;
516 p_dummy VARCHAR2(1);
517 p_dummy_x VARCHAR2(1);
518 p_previous_status INTEGER;
519 p_old_interview_status INTEGER;
520 p_nxt_interview_date DATE;
521 p_new_status_type_date DATE;
522 --
523 CURSOR select_ass_for_delete IS
524 SELECT *
525 FROM per_assignments_f
526 WHERE assignment_id = p_assignment_id
527 FOR UPDATE;
528 --
529 BEGIN
530 --
531 p_int_date := p_idate;
532 p_dummy := 'N';
533 --
534 -- Check if status was the only field to change
535 -- IF it is then CASE A B and C
536 --
537 -- CASE A B C D
538 hr_utility.set_location('hr_interview.delete_interview',1);
539 --
540 -- This for loop has a purpose to the lock all the assignment records
541 -- specified by the cursor
542 FOR ass_rec_del IN select_ass_for_delete LOOP
543 NULL;
544 END LOOP;
545 --
546 hr_utility.set_location('hr_interview.delete_interview',2);
547 --
548 -- #4894555: replaced SQL statement with function call.
549 --
550 p_dummy := chk_duplicate(p_assignment_id, p_int_date, TRUE);
551 --
552 hr_utility.set_location('hr_interview.delete_interview',25);
553 --
554 --
555 BEGIN
556 --
557 hr_utility.set_location('hr_interview.delete_interview',3);
558 --
559 SELECT a.assignment_status_type_id
560 INTO p_old_interview_status
561 FROM per_assignments_f a
562 WHERE a.assignment_id = p_assignment_id
563 AND a.effective_start_date = p_int_date;
564 --
565 EXCEPTION
566 WHEN NO_DATA_FOUND THEN
567 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
568 hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
569 hr_utility.set_message_token('STEP','2');
570 hr_utility.raise_error;
571 END;
572 --
573 IF p_dummy = 'Y' THEN
574 --
575 --
576 -- set the end date of the previous row to the
577 -- effective end date of the row that begins on the
578 -- interview start date
579 -- Done For CASE A, B, and C
580 --
581 -- delete the row that has effective start date = interview start date
582 -- done For CASE A, B, and C
583 --
584 hr_utility.set_location('hr_interview.delete_interview',4);
585 --
586 UPDATE per_assignments_f a
587 SET effective_end_date = (SELECT effective_end_date
588 FROM per_assignments_f b
589 WHERE b.assignment_id = p_assignment_id
590 AND b.effective_start_date =
591 p_int_date)
592 , last_updated_by = p_last_updated_by
593 , last_update_login = p_last_update_login
594 , last_update_date = sysdate
595 WHERE a.assignment_id = p_assignment_id
596 AND a.effective_end_date = p_int_date -1;
597 --
598 IF SQL%ROWCOUNT <> 1 THEN
599 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
600 hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
601 hr_utility.set_message_token('STEP','3');
602 hr_utility.raise_error;
603 END IF;
604 --
605 hr_utility.set_location('hr_interview.delete_interview',5);
606 --
607 DELETE per_assignments_f a
608 WHERE a.assignment_id = p_assignment_id
609 AND a.effective_start_date = p_int_date;
610 --
611 IF SQL%ROWCOUNT <> 1 THEN
612 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
613 hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
614 hr_utility.set_message_token('STEP','4');
615 hr_utility.raise_error;
616 END IF;
617 --
618 END IF;
619 --
620 --
621 -- Check for CASE A if effective_end_date = EOT then no need to ripple
622 --
623 BEGIN
624 --
625 hr_utility.set_location('hr_interview.delete_interview',6);
626 --
627 SELECT a.effective_end_date
628 INTO p_dummy_date
629 FROM per_assignments_f a
630 WHERE a.assignment_id = p_assignment_id
631 AND p_int_date BETWEEN a.effective_start_date AND a.effective_end_date;
632 --
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN
635 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
636 hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
637 hr_utility.set_message_token('STEP','5');
638 hr_utility.raise_error;
639 END;
640 --
641 IF p_dummy_date = TO_DATE('31/12/4712','DD/MM/YYYY') AND
642 p_dummy = 'N' THEN
643 --
644 BEGIN
645 --
646 -- CASE D when the last record no need to ripple forward.
647 --
648 hr_utility.set_location('hr_interview.delete_interview',7);
649 --
650 SELECT d.assignment_status_type_id
651 INTO p_previous_status
652 FROM per_assignments_f d
653 WHERE d.assignment_id = p_assignment_id
654 AND d.effective_end_date = p_int_date -1;
655 --
656 EXCEPTION
657 WHEN NO_DATA_FOUND THEN
658 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
659 hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
660 hr_utility.set_message_token('STEP','6');
661 hr_utility.raise_error;
662 END;
663 --
664 UPDATE per_assignments_f a
665 SET assignment_status_type_id = p_previous_status
666 , last_updated_by = p_last_updated_by
667 , last_update_login = p_last_update_login
668 , last_update_date = sysdate
669 WHERE a.assignment_id = p_assignment_id
670 AND a.effective_start_date = p_int_date;
671 --
672 END IF;
673 --
674 IF p_dummy_date <> TO_DATE('31/12/4712','DD/MM/YYYY') THEN
675 --
676 -- Test for Case C and D a new assignment corresponds to start date of
677 -- another interview.
678 --
679 hr_utility.set_location('hr_interview.delete_interview',8);
680 --
681 SELECT MIN(e.date_start)
682 INTO p_nxt_interview_date
683 FROM per_events e
684 WHERE e.assignment_id = p_assignment_id
685 AND e.date_start > p_int_date;
686 --
687 hr_utility.trace('p_nxt_interview_date '||
688 substr(to_char(p_nxt_interview_date,'DD-MON-YYYY'),1,11));
689 --
690 -- Test for Case B and D the assignment status changes.
691 --
692 -- Get previous status
693 --
694 IF p_dummy = 'Y' THEN
695 --
696 BEGIN
697 --
698 hr_utility.set_location('hr_interview.delete_interview',9);
699 --
700 SELECT d.assignment_status_type_id
701 INTO p_previous_status
702 FROM per_assignments_f d
703 WHERE d.assignment_id = p_assignment_id
704 AND p_int_date
705 BETWEEN d.effective_start_date AND d.effective_end_date;
706 --
707 EXCEPTION
708 WHEN NO_DATA_FOUND THEN
709 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
710 hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
711 hr_utility.set_message_token('STEP','7');
712 hr_utility.raise_error;
713 END;
714 --
715 ELSE
716 --
717 BEGIN
718 --
719 hr_utility.set_location('hr_interview.delete_interview',10);
720 --
721 SELECT d.assignment_status_type_id
722 INTO p_previous_status
726 --
723 FROM per_assignments_f d
724 WHERE d.assignment_id = p_assignment_id
725 AND d.effective_end_date = p_int_date -1;
727 EXCEPTION
728 WHEN NO_DATA_FOUND THEN
729 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
730 hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
731 hr_utility.set_message_token('STEP','8');
732 hr_utility.raise_error;
733 END;
734 --
735 END IF;
736 --
737 hr_utility.trace('p_previous_status '|| p_previous_status);
738 hr_utility.trace('p_old_interview_status '|| p_old_interview_status);
739 --
740 hr_utility.set_location('hr_interview.delete_interview',11);
741 --
742 SELECT MIN(c.effective_start_date)
743 INTO p_new_status_type_date
744 FROM per_assignments_f c
745 WHERE c.assignment_id = p_assignment_id
746 AND c.effective_start_date > p_int_date
747 AND c.assignment_status_type_id <> p_old_interview_status;
748 --
749 hr_utility.trace('p_new_status_type_date '||
750 substr(to_char(p_new_status_type_date,'DD-MON-YYYY'),1,11));
751 --
752 -- Ripple the pre interview status CASE B,C and D.
753 --
754 hr_utility.set_location('hr_interview.delete_interview',12);
755 --
756 UPDATE per_assignments_f a
757 SET assignment_status_type_id = p_previous_status
758 , last_updated_by = p_last_updated_by
759 , last_update_login = p_last_update_login
760 , last_update_date = sysdate
761 WHERE a.assignment_id = p_assignment_id
762 AND a.effective_start_date >= p_int_date
763 AND ((a.effective_end_date < least(nvl(p_nxt_interview_date,
764 to_date('31/12/4712','DD/MM/YYYY')),
765 nvl(p_new_status_type_date,
766 to_date('31/12/4712','DD/MM/YYYY')))));
767 --
768 IF SQL%ROWCOUNT = 0 THEN
769 --
770 hr_utility.set_location('hr_interview.delete_interview',13);
771 --
772 IF p_nxt_interview_date IS NULL AND p_new_status_type_date IS NULL THEN
773 --
774 hr_utility.set_location('hr_interview.delete_interview',14);
775 --
776 UPDATE per_assignments_f a
777 SET assignment_status_type_id = p_previous_status
778 , last_updated_by = p_last_updated_by
779 , last_update_login = p_last_update_login
780 , last_update_date = sysdate
781 WHERE a.assignment_id = p_assignment_id
782 AND a.effective_start_date >= p_int_date
783 AND a.effective_end_date = to_date('31/12/4712','DD/MM/YYYY');
784 --
785 END IF;
786 --
787 END IF;
788 --
789 END IF;
790 --
791 END delete_interview;
792 ------------------- update_interview -------------------------------
793 /*
794 NAME
795 update_interview
796 DESCRIPTION
797 Update assignment for associated applicant interview
798 PARAMETERS
799 p_assignment_id - assignment_id of applicant
800 p_idate - New Interview Date
801 p_odate - Old Interview Date
802 p_last_updated_by - Required for Auditing
803 p_last_update_login - Required for Auditing
804 */
805 PROCEDURE update_interview
806 (p_assignment_id IN INTEGER,
807 p_idate IN DATE,
808 p_odate IN DATE,
809 p_last_updated_by IN INTEGER,
810 p_last_update_login IN INTEGER
811 ) IS
812 -----------------------------------------------------------
813 -- DECLARE THE LOCAL VARIABLES
814 -----------------------------------------------------------
815 p_int_date DATE;
816 p_old_int_date DATE;
817 p_old_prev_start_date DATE;
818 p_dummy_date DATE;
819 p_dummy VARCHAR2(1);
820 p_matches VARCHAR2(1);
821 p_no_update VARCHAR2(1);
822 p_ass_status_type_id INTEGER;
823 --
824 CURSOR select_ass_for_update IS
825 SELECT *
826 FROM per_assignments_f
827 WHERE assignment_id = p_assignment_id
828 FOR UPDATE;
829 --
830 BEGIN
831 --
832 p_int_date := p_idate;
833 p_old_int_date := p_odate;
834 p_no_update := 'N';
835 p_dummy := 'N';
836 p_matches := 'N';
837 --
838 BEGIN
839 --
840 hr_utility.set_location('hr_interview.update_interview',1);
841 --
842 -- This for loop has a purpose to the lock all the assignment records
843 -- specified by the cursor
844 --
845 FOR ass_rec_upd IN select_ass_for_update LOOP
846 NULL;
847 END LOOP;
848 --
849 hr_utility.set_location('hr_interview.update_interview',2);
850 --
851 SELECT 'Y'
852 INTO p_no_update
853 FROM sys.dual
854 WHERE EXISTS
855 (SELECT '1'
856 FROM per_assignments_f a
857 WHERE a.assignment_id = p_assignment_id
858 AND (((a.effective_start_date
859 BETWEEN p_old_int_date + 1 AND p_int_date -1)
860 AND p_old_int_date < p_int_date)
861 OR
862 ((a.effective_start_date
863 BETWEEN p_int_date AND p_old_int_date -1)
864 AND p_old_int_date > p_int_date)));
865 --
866 EXCEPTION
867 WHEN NO_DATA_FOUND THEN NULL;
868 END;
872 hr_utility.raise_error;
869 --
870 IF p_no_update = 'Y' THEN /* CASE D or E */
871 hr_utility.set_message(801,'HR_6629_APPL_STATUS_MOVE');
873 END IF;
874 --
875 hr_utility.set_location('hr_interview.update_interview',3);
876 --
877 -- #4894555: replace SQL with function call.
878 --
879 p_dummy := chk_duplicate(p_assignment_id, p_old_int_date, FALSE);
880 --
881 --
882 hr_utility.trace('p_dummy is'||p_dummy);
883 IF p_dummy = 'Y' THEN
884 --
885 hr_utility.set_location('hr_interview.update_interview',4);
886 BEGIN
887 SELECT 'Y'
888 INTO p_matches
889 FROM per_assignments_f a
890 WHERE a.assignment_id = p_assignment_id
891 AND a.effective_start_date = p_int_date;
892 EXCEPTION
893 WHEN NO_DATA_FOUND THEN NULL;
894 END;
895 --
896 hr_utility.set_location('hr_interview.update_interview',5);
897 --
898 IF p_matches = 'N' THEN /* CASE A */
899 --
900 hr_utility.set_location('hr_interview.update_interview',6);
901 UPDATE per_assignments_f a
902 SET a.effective_start_date = p_int_date
903 , a.last_updated_by = p_last_updated_by
904 , a.last_update_login = p_last_update_login
905 , a.last_update_date = sysdate
906 WHERE a.assignment_id = p_assignment_id
907 AND a.effective_start_date = p_old_int_date;
908 --
909 IF SQL%ROWCOUNT <> 1 THEN
910 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
911 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
912 hr_utility.set_message_token('STEP','1');
913 hr_utility.raise_error;
914 END IF;
915 --
916 hr_utility.set_location('hr_interview.update_interview',7);
917 UPDATE per_assignments_f a
918 SET a.effective_end_date = p_int_date -1
919 , a.last_updated_by = p_last_updated_by
920 , a.last_update_login = p_last_update_login
921 , a.last_update_date = sysdate
922 WHERE a.assignment_id = p_assignment_id
923 AND a.effective_end_date = p_old_int_date -1;
924 --
925 IF SQL%ROWCOUNT <> 1 THEN
926 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
927 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
928 hr_utility.set_message_token('STEP','2');
929 hr_utility.raise_error;
930 END IF;
931 --
932 ELSE /* CASE C */
933 --
934 hr_utility.set_location('hr_interview.update_interview',8);
935 DELETE per_assignments_f a
936 WHERE a.assignment_id = p_assignment_id
937 AND a.effective_start_date = p_old_int_date;
938 --
939 IF SQL%ROWCOUNT <> 1 THEN
940 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
941 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
942 hr_utility.set_message_token('STEP','3');
943 hr_utility.raise_error;
944 END IF;
945 --
946 hr_utility.set_location('hr_interview.update_interview',9);
947 UPDATE per_assignments_f a
948 SET a.effective_end_date = p_int_date -1
949 , a.last_updated_by = p_last_updated_by
950 , a.last_update_login = p_last_update_login
951 , a.last_update_date = sysdate
952 WHERE a.assignment_id = p_assignment_id
953 AND a.effective_end_date = p_old_int_date -1;
954 --
955 IF SQL%ROWCOUNT <> 1 THEN
956 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
957 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
958 hr_utility.set_message_token('STEP','4');
959 hr_utility.raise_error;
960 END IF;
961 --
962 END IF;
963 --
964 ELSE /* CASE B */
965 --
966 -- check whether insert is forward or backward
967 --
968 IF p_int_date > p_old_int_date THEN
969 --
970 p_no_update := 'N';
971 --
972 hr_utility.set_location('hr_interview.update_interview',10);
973 BEGIN
974 --
975 SELECT 'Y'
976 INTO p_no_update
977 FROM sys.dual
978 WHERE EXISTS
979 (SELECT '1'
980 FROM per_assignments_f a
981 WHERE a.assignment_id = p_assignment_id
982 AND a.effective_start_date >= p_int_date);
983 EXCEPTION
984 --
985 WHEN NO_DATA_FOUND THEN NULL;
986 --
987 END;
988 --
989 IF p_no_update = 'Y' THEN
990 hr_utility.set_message(801,'HR_6629_APPL_STATUS_MOVE');
991 hr_utility.raise_error;
992 END IF;
993 --
994 hr_utility.set_location('hr_interview.update_interview',11);
995 --
996 SELECT a.assignment_status_type_id
997 INTO p_ass_status_type_id
998 FROM per_assignments_f a
999 WHERE a.assignment_id = p_assignment_id
1000 AND a.effective_end_date = p_old_int_date -1;
1001 --
1002 IF SQL%ROWCOUNT <> 1 THEN
1003 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1004 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1005 hr_utility.set_message_token('STEP','5');
1006 hr_utility.raise_error;
1007 END IF;
1008 --
1009 hr_utility.set_location('hr_interview.update_interview',12);
1013 assignment_id
1010 --
1011 INSERT INTO per_assignments_f
1012 (
1014 ,effective_start_date
1015 ,effective_end_date
1016 ,business_group_id
1017 ,grade_id
1018 ,position_id
1019 ,job_id
1020 ,assignment_status_type_id
1021 ,payroll_id
1022 ,location_id
1023 ,person_referred_by_id
1024 ,person_id
1025 ,recruitment_activity_id
1026 ,source_organization_id
1027 ,organization_id
1028 ,people_group_id
1029 ,soft_coding_keyflex_id
1030 ,vacancy_id
1031 ,assignment_sequence
1032 ,assignment_type
1033 ,manager_flag
1034 ,primary_flag
1035 ,application_id
1036 ,assignment_number
1037 ,change_reason
1038 ,comment_id
1039 ,date_probation_end
1040 ,default_code_comb_id
1041 ,frequency
1042 ,internal_address_line
1043 ,normal_hours
1044 ,period_of_service_id
1045 ,probation_period
1046 ,probation_unit
1047 ,recruiter_id
1048 ,set_of_books_id
1049 ,special_ceiling_step_id
1050 ,supervisor_id
1051 ,time_normal_finish
1052 ,time_normal_start
1053 ,request_id
1054 ,program_application_id
1055 ,program_id
1056 ,program_update_date
1057 ,ass_attribute_category
1058 ,ass_attribute1
1059 ,ass_attribute2
1060 ,ass_attribute3
1061 ,ass_attribute4
1062 ,ass_attribute5
1063 ,ass_attribute6
1064 ,ass_attribute7
1065 ,ass_attribute8
1066 ,ass_attribute9
1067 ,ass_attribute10
1068 ,ass_attribute11
1069 ,ass_attribute12
1070 ,ass_attribute13
1071 ,ass_attribute14
1072 ,ass_attribute15
1073 ,ass_attribute16
1074 ,ass_attribute17
1075 ,ass_attribute18
1076 ,ass_attribute19
1077 ,ass_attribute20
1078 ,ass_attribute21
1079 ,ass_attribute22
1080 ,ass_attribute23
1081 ,ass_attribute24
1082 ,ass_attribute25
1083 ,ass_attribute26
1084 ,ass_attribute27
1085 ,ass_attribute28
1086 ,ass_attribute29
1087 ,ass_attribute30
1088 ,sal_review_period
1089 ,sal_review_period_frequency
1090 ,perf_review_period
1091 ,perf_review_period_frequency
1092 ,pay_basis_id
1093 ,employment_category
1094 ,bargaining_unit_code
1095 ,labour_union_member_flag
1096 ,hourly_salaried_code
1097 ,last_update_date
1098 ,last_updated_by
1099 ,last_update_login
1100 ,created_by
1101 ,creation_date
1102 ,source_type
1103 )
1104 SELECT
1105 assignment_id
1106 ,p_old_int_date
1107 ,p_int_date - 1
1108 ,business_group_id
1109 ,grade_id
1110 ,position_id
1111 ,job_id
1112 ,p_ass_status_type_id
1113 ,payroll_id
1114 ,location_id
1115 ,person_referred_by_id
1116 ,person_id
1117 ,recruitment_activity_id
1118 ,source_organization_id
1119 ,organization_id
1120 ,people_group_id
1121 ,soft_coding_keyflex_id
1122 ,vacancy_id
1123 ,assignment_sequence
1124 ,assignment_type
1125 ,manager_flag
1126 ,primary_flag
1127 ,application_id
1128 ,assignment_number
1129 ,change_reason
1130 ,comment_id
1131 ,date_probation_end
1132 ,default_code_comb_id
1133 ,frequency
1134 ,internal_address_line
1135 ,normal_hours
1136 ,period_of_service_id
1137 ,probation_period
1138 ,probation_unit
1139 ,recruiter_id
1140 ,set_of_books_id
1141 ,special_ceiling_step_id
1142 ,supervisor_id
1143 ,time_normal_finish
1144 ,time_normal_start
1145 ,request_id
1146 ,program_application_id
1147 ,program_id
1148 ,program_update_date
1149 ,ass_attribute_category
1150 ,ass_attribute1
1151 ,ass_attribute2
1152 ,ass_attribute3
1153 ,ass_attribute4
1154 ,ass_attribute5
1155 ,ass_attribute6
1156 ,ass_attribute7
1157 ,ass_attribute8
1158 ,ass_attribute9
1159 ,ass_attribute10
1160 ,ass_attribute11
1161 ,ass_attribute12
1162 ,ass_attribute13
1163 ,ass_attribute14
1164 ,ass_attribute15
1165 ,ass_attribute16
1166 ,ass_attribute17
1167 ,ass_attribute18
1168 ,ass_attribute19
1169 ,ass_attribute20
1170 ,ass_attribute21
1171 ,ass_attribute22
1172 ,ass_attribute23
1173 ,ass_attribute24
1174 ,ass_attribute25
1175 ,ass_attribute26
1176 ,ass_attribute27
1177 ,ass_attribute28
1178 ,ass_attribute29
1179 ,ass_attribute30
1180 ,sal_review_period
1181 ,sal_review_period_frequency
1185 ,employment_category
1182 ,perf_review_period
1183 ,perf_review_period_frequency
1184 ,pay_basis_id
1186 ,bargaining_unit_code
1187 ,labour_union_member_flag
1188 ,hourly_salaried_code
1189 ,last_update_date
1190 ,last_updated_by
1191 ,last_update_login
1192 ,created_by
1193 ,creation_date
1194 ,source_type
1195 FROM per_assignments_f
1196 WHERE assignment_id = p_assignment_id
1197 AND effective_start_date = p_old_int_date;
1198 --
1199 IF SQL%ROWCOUNT <> 1 THEN
1200 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1201 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1202 hr_utility.set_message_token('STEP','6');
1203 hr_utility.raise_error;
1204 ELSE
1205 hr_utility.set_location('hr_interview.update_interview',13);
1206 UPDATE per_assignments_f a
1207 SET a.effective_start_date = p_int_date
1208 , a.last_updated_by = p_last_updated_by
1209 , a.last_update_login = p_last_update_login
1210 , a.last_update_date = sysdate
1211 WHERE assignment_id = p_assignment_id
1212 AND effective_end_date = TO_DATE('31/12/4712','DD/MM/YYYY');
1213 --
1214 IF SQL%ROWCOUNT <> 1 THEN
1215 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1216 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1217 hr_utility.set_message_token('STEP','7');
1218 hr_utility.raise_error;
1219 END IF;
1220 END IF;
1221 ELSE
1222 -- insert back in time
1223 --
1224 hr_utility.set_location('hr_interview.update_interview',14);
1225 SELECT a.assignment_status_type_id
1226 INTO p_ass_status_type_id
1227 FROM per_assignments_f a
1228 WHERE a.assignment_id = p_assignment_id
1229 AND a.effective_start_date = p_old_int_date;
1230 --
1231 IF SQL%ROWCOUNT <> 1 THEN
1232 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1233 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1234 hr_utility.set_message_token('STEP','8');
1235 hr_utility.raise_error;
1236 END IF;
1237 --
1238 hr_utility.set_location('hr_interview.update_interview',15);
1239 SELECT a.effective_start_date
1240 INTO p_old_prev_start_date
1241 FROM per_assignments_f a
1242 WHERE a.assignment_id = p_assignment_id
1243 AND a.effective_end_date = p_old_int_date -1;
1244 --
1245 IF SQL%ROWCOUNT <> 1 THEN
1246 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1247 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1248 hr_utility.set_message_token('STEP','9');
1249 hr_utility.raise_error;
1250 END IF;
1251 --
1252 -- Insert the Interview
1253 hr_utility.set_location('hr_interview.update_interview',16);
1254 --
1255 INSERT INTO per_assignments_f
1256 (
1257 assignment_id
1258 ,effective_start_date
1259 ,effective_end_date
1260 ,business_group_id
1261 ,grade_id
1262 ,position_id
1263 ,job_id
1264 ,assignment_status_type_id
1265 ,payroll_id
1266 ,location_id
1267 ,person_referred_by_id
1268 ,person_id
1269 ,recruitment_activity_id
1270 ,source_organization_id
1271 ,organization_id
1272 ,people_group_id
1273 ,soft_coding_keyflex_id
1274 ,vacancy_id
1275 ,assignment_sequence
1276 ,assignment_type
1277 ,manager_flag
1278 ,primary_flag
1279 ,application_id
1280 ,assignment_number
1281 ,change_reason
1282 ,comment_id
1283 ,date_probation_end
1284 ,default_code_comb_id
1285 ,frequency
1286 ,internal_address_line
1287 ,normal_hours
1288 ,period_of_service_id
1289 ,probation_period
1290 ,probation_unit
1291 ,recruiter_id
1292 ,set_of_books_id
1293 ,special_ceiling_step_id
1294 ,supervisor_id
1295 ,time_normal_finish
1296 ,time_normal_start
1297 ,request_id
1298 ,program_application_id
1299 ,program_id
1300 ,program_update_date
1301 ,ass_attribute_category
1302 ,ass_attribute1
1303 ,ass_attribute2
1304 ,ass_attribute3
1305 ,ass_attribute4
1306 ,ass_attribute5
1307 ,ass_attribute6
1308 ,ass_attribute7
1309 ,ass_attribute8
1310 ,ass_attribute9
1311 ,ass_attribute10
1312 ,ass_attribute11
1313 ,ass_attribute12
1314 ,ass_attribute13
1315 ,ass_attribute14
1316 ,ass_attribute15
1317 ,ass_attribute16
1318 ,ass_attribute17
1319 ,ass_attribute18
1320 ,ass_attribute19
1321 ,ass_attribute20
1322 ,ass_attribute21
1323 ,ass_attribute22
1324 ,ass_attribute23
1325 ,ass_attribute24
1326 ,ass_attribute25
1327 ,ass_attribute26
1328 ,ass_attribute27
1329 ,ass_attribute28
1330 ,ass_attribute29
1331 ,ass_attribute30
1332 ,sal_review_period
1333 ,sal_review_period_frequency
1334 ,perf_review_period
1335 ,perf_review_period_frequency
1336 ,pay_basis_id
1337 ,employment_category
1338 ,bargaining_unit_code
1339 ,labour_union_member_flag
1340 ,hourly_salaried_code
1341 ,last_update_date
1342 ,last_updated_by
1343 ,last_update_login
1344 ,created_by
1345 ,creation_date
1346 ,source_type
1347 )
1348 SELECT
1349 assignment_id
1350 ,p_int_date
1351 ,p_old_int_date - 1
1352 ,business_group_id
1353 ,grade_id
1354 ,position_id
1355 ,job_id
1356 ,p_ass_status_type_id
1357 ,payroll_id
1358 ,location_id
1359 ,person_referred_by_id
1360 ,person_id
1361 ,recruitment_activity_id
1362 ,source_organization_id
1363 ,organization_id
1364 ,people_group_id
1365 ,soft_coding_keyflex_id
1366 ,vacancy_id
1367 ,assignment_sequence
1368 ,assignment_type
1369 ,manager_flag
1370 ,primary_flag
1371 ,application_id
1372 ,assignment_number
1373 ,change_reason
1374 ,comment_id
1375 ,date_probation_end
1376 ,default_code_comb_id
1377 ,frequency
1378 ,internal_address_line
1379 ,normal_hours
1380 ,period_of_service_id
1381 ,probation_period
1382 ,probation_unit
1383 ,recruiter_id
1384 ,set_of_books_id
1385 ,special_ceiling_step_id
1386 ,supervisor_id
1387 ,time_normal_finish
1388 ,time_normal_start
1389 ,request_id
1390 ,program_application_id
1391 ,program_id
1392 ,program_update_date
1393 ,ass_attribute_category
1394 ,ass_attribute1
1395 ,ass_attribute2
1396 ,ass_attribute3
1397 ,ass_attribute4
1398 ,ass_attribute5
1399 ,ass_attribute6
1400 ,ass_attribute7
1401 ,ass_attribute8
1402 ,ass_attribute9
1403 ,ass_attribute10
1404 ,ass_attribute11
1405 ,ass_attribute12
1406 ,ass_attribute13
1407 ,ass_attribute14
1408 ,ass_attribute15
1409 ,ass_attribute16
1410 ,ass_attribute17
1411 ,ass_attribute18
1412 ,ass_attribute19
1413 ,ass_attribute20
1414 ,ass_attribute21
1415 ,ass_attribute22
1416 ,ass_attribute23
1417 ,ass_attribute24
1418 ,ass_attribute25
1419 ,ass_attribute26
1420 ,ass_attribute27
1421 ,ass_attribute28
1422 ,ass_attribute29
1423 ,ass_attribute30
1424 ,sal_review_period
1425 ,sal_review_period_frequency
1426 ,perf_review_period
1427 ,perf_review_period_frequency
1428 ,pay_basis_id
1429 ,employment_category
1430 ,bargaining_unit_code
1431 ,labour_union_member_flag
1432 ,hourly_salaried_code
1433 ,last_update_date
1434 ,last_updated_by
1435 ,last_update_login
1436 ,created_by
1437 ,creation_date
1438 ,source_type
1439 FROM per_assignments_f
1440 WHERE assignment_id = p_assignment_id
1441 AND effective_end_date = p_old_int_date -1;
1442 --
1443 IF SQL%ROWCOUNT <>1 THEN
1444 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1445 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1446 hr_utility.set_message_token('STEP','10');
1447 hr_utility.raise_error;
1448 ELSE
1449 hr_utility.set_location('hr_interview.update_interview',17);
1450 --
1451 UPDATE per_assignments_f a
1452 SET a.effective_end_date = p_int_date -1
1453 , a.last_updated_by = p_last_updated_by
1454 , a.last_update_login = p_last_update_login
1455 , a.last_update_date = sysdate
1456 WHERE assignment_id = p_assignment_id
1457 AND effective_start_date = p_old_prev_start_date
1458 AND effective_end_date = p_old_int_date -1;
1459 --
1460 IF SQL%ROWCOUNT <> 1 THEN
1461 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1462 hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1463 hr_utility.set_message_token('STEP','11');
1464 hr_utility.raise_error;
1465 END IF;
1466 END IF;
1467 -- End the insert backward
1468 END IF;
1469 -- End the insert forward
1470 END IF;
1471 --
1472 END update_interview;
1473 --
1474 --
1475 end hr_interview;