[Home] [Help]
PACKAGE BODY: APPS.PER_ASSIGNMENTS_V8_PKG
Source
1 PACKAGE BODY PER_ASSIGNMENTS_V8_PKG as
2 /* $Header: peasg08t.pkb 120.1 2006/01/23 06:35:44 eumenyio noship $ */
3 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
4 X_Assignment_Id IN OUT NOCOPY NUMBER,
5 X_Effective_Start_Date DATE,
6 X_Effective_End_Date DATE,
7 X_Business_Group_Id NUMBER,
8 X_Job_Id NUMBER,
9 X_Position_Id NUMBER,
10 X_Assignment_Status_Type_Id IN OUT NOCOPY NUMBER,
11 X_Person_Id NUMBER,
12 X_Organization_Id NUMBER,
13 X_Assignment_Sequence IN OUT NOCOPY NUMBER,
14 X_Assignment_Type VARCHAR2,
15 X_Primary_Flag IN OUT NOCOPY VARCHAR2,
16 X_Assignment_Number IN OUT NOCOPY VARCHAR2,
17 X_Comment_Id NUMBER,
18 X_Period_Of_Service_Id NUMBER,
19 X_Default_Code_Comb_Id NUMBER,
20 X_Set_Of_Books_Id NUMBER,
21 X_Location_Id NUMBER,
22 X_Supervisor_Id NUMBER,
23 X_Ass_Attribute_Category VARCHAR2,
24 X_Ass_Attribute1 VARCHAR2,
25 X_Ass_Attribute2 VARCHAR2,
26 X_Ass_Attribute3 VARCHAR2,
27 X_Ass_Attribute4 VARCHAR2,
28 X_Ass_Attribute5 VARCHAR2,
29 X_Ass_Attribute6 VARCHAR2,
30 X_Ass_Attribute7 VARCHAR2,
31 X_Ass_Attribute8 VARCHAR2,
32 X_Ass_Attribute9 VARCHAR2,
33 X_Ass_Attribute10 VARCHAR2,
34 X_Ass_Attribute11 VARCHAR2,
35 X_Ass_Attribute12 VARCHAR2,
36 X_Ass_Attribute13 VARCHAR2,
37 X_Ass_Attribute14 VARCHAR2,
38 X_Ass_Attribute15 VARCHAR2,
39 X_Ass_Attribute16 VARCHAR2,
40 X_Ass_Attribute17 VARCHAR2,
41 X_Ass_Attribute18 VARCHAR2,
42 X_Ass_Attribute19 VARCHAR2,
43 X_Ass_Attribute20 VARCHAR2,
44 X_Ass_Attribute21 VARCHAR2,
45 X_Ass_Attribute22 VARCHAR2,
46 X_Ass_Attribute23 VARCHAR2,
47 X_Ass_Attribute24 VARCHAR2,
48 X_Ass_Attribute25 VARCHAR2,
49 X_Ass_Attribute26 VARCHAR2,
50 X_Ass_Attribute27 VARCHAR2,
51 X_Ass_Attribute28 VARCHAR2,
52 X_Ass_Attribute29 VARCHAR2,
53 X_Ass_Attribute30 VARCHAR2,
54 X_Last_Update_Date DATE,
55 X_Last_Updated_By NUMBER,
56 X_Last_Update_Login NUMBER,
57 X_Created_By NUMBER,
58 X_Creation_Date DATE,
59 X_Title VARCHAR2
60 ) IS
61 --
62 -- Cursor to get rest of the fields not used/required by PA but
63 -- which may have had values added by other apps and therfore preserve them
64 -- for these other APPS.
65 --
66 CURSOR C0 is select * from per_assignments_f
67 where rowid = X_ROWID;
68 --
69 --
70 -- Get the rowid effective as of start date
71 -- May not necessarily be one if EFS appears between another record.
72 --
73 CURSOR C IS SELECT rowid FROM PER_ASSIGNMENTS_F
74 WHERE assignment_id = X_Assignment_Id
75 and effective_start_date = X_effective_start_date
76 for update of assignment_id;
77 --
78 --
79 -- Cursor to ensure the Assignment was entered.
80 --
81 CURSOR C2 is
82 select rowid
83 from per_assignments_f
84 where assignment_id = X_ASSIGNMENT_ID
85 and effective_start_Date = X_EFFECTIVE_START_DATE
86 and effective_end_date = X_EFFECTIVE_END_DATE;
87 --
88 --
89 -- Update any records that exist between start and
90 -- end of the new assignment.
91 --
92 CURSOR C3 is select rowid from per_assignments_f
93 where rowid <> X_ROWID
94 and effective_end_date between
95 X_EFFECTIVE_START_DATE
96 and X_EFFECTIVE_END_DATE
97 and assignment_id = X_ASSIGNMENT_ID
98 for update of assignment_id;
99 --
100 ass_rec c0%ROWTYPE;
101 asg_rec c3%ROWTYPE;
102 l_sql_count NUMBER :=0;
103 --
104 BEGIN
105 if X_ROWID is not null
106 then
107 open C0;
108 fetch c0 into ass_rec;
109 if C0%NOTFOUND
110 then
111 raise NO_DATA_FOUND;
112 end if;
113 close C0;
114 end if;
115 --
116 open C;
117 fetch C into X_Rowid;
118 if C%FOUND
119 then
120 per_assignments_v8_pkg.delete_record(X_ROWID);
121 end if;
122 --
123
124 hr_utility.set_location('Enterring:per_assignments_v8_pkg.insert_row',10);
125
126 INSERT INTO PER_ASSIGNMENTS_F(
127 assignment_id,
128 effective_start_date,
129 effective_end_date,
130 business_group_id,
131 recruiter_id,
132 grade_id,
133 position_id,
134 job_id,
135 assignment_status_type_id,
136 payroll_id,
137 location_id,
138 person_referred_by_id,
139 supervisor_id,
140 special_ceiling_step_id,
141 person_id,
142 recruitment_activity_id,
143 source_organization_id,
144 organization_id,
145 people_group_id,
146 soft_coding_keyflex_id,
147 vacancy_id,
148 pay_basis_id,
149 assignment_sequence,
150 assignment_type,
151 primary_flag,
152 application_id,
153 assignment_number,
154 change_reason,
155 comment_id,
156 date_probation_end,
157 default_code_comb_id,
158 employment_category,
159 frequency,
160 internal_address_line,
161 manager_flag,
162 normal_hours,
163 perf_review_period,
164 perf_review_period_frequency,
165 period_of_service_id,
166 probation_period,
167 probation_unit,
168 sal_review_period,
169 sal_review_period_frequency,
170 set_of_books_id,
171 source_type,
172 time_normal_finish,
173 time_normal_start,
174 ass_attribute_category,
175 ass_attribute1,
176 ass_attribute2,
177 ass_attribute3,
178 ass_attribute4,
179 ass_attribute5,
180 ass_attribute6,
181 ass_attribute7,
182 ass_attribute8,
183 ass_attribute9,
184 ass_attribute10,
185 ass_attribute11,
186 ass_attribute12,
187 ass_attribute13,
188 ass_attribute14,
189 ass_attribute15,
190 ass_attribute16,
191 ass_attribute17,
192 ass_attribute18,
193 ass_attribute19,
194 ass_attribute20,
195 ass_attribute21,
196 ass_attribute22,
197 ass_attribute23,
198 ass_attribute24,
199 ass_attribute25,
200 ass_attribute26,
201 ass_attribute27,
202 ass_attribute28,
203 ass_attribute29,
204 ass_attribute30,
205 last_update_date,
206 last_updated_by,
207 last_update_login,
208 created_by,
209 creation_date,
210 title
211 ) VALUES (
212 X_Assignment_Id,
213 X_Effective_Start_Date,
214 X_Effective_End_Date,
215 X_Business_Group_Id,
216 ass_rec.Recruiter_Id,
217 ass_rec.Grade_Id,
218 X_Position_Id, -- ass_rec.Position_Id,
219 X_Job_Id,
220 X_Assignment_Status_Type_Id,
221 ass_rec.Payroll_Id,
222 X_Location_Id,
223 ass_rec.Person_Referred_By_Id,
224 X_Supervisor_Id,
225 ass_rec.Special_Ceiling_Step_Id,
226 X_Person_Id,
227 ass_rec.Recruitment_Activity_Id,
228 ass_rec.Source_Organization_Id,
229 X_Organization_Id,
230 ass_rec.People_Group_Id,
231 ass_rec.Soft_Coding_Keyflex_Id,
232 ass_rec.Vacancy_Id,
233 ass_rec.Pay_Basis_Id,
234 X_Assignment_Sequence,
235 X_Assignment_Type,
236 X_Primary_Flag,
237 ass_rec.Application_Id,
238 X_Assignment_Number,
239 ass_rec.Change_Reason,
240 X_Comment_Id,
241 ass_rec.Date_Probation_End,
242 X_Default_Code_Comb_Id, --ass_rec.Default_Code_Comb_Id,
243 ass_rec.Employment_Category,
244 ass_rec.Frequency,
245 ass_rec.Internal_Address_Line,
246 ass_rec.Manager_Flag,
247 ass_rec.Normal_Hours,
248 ass_rec.Perf_Review_Period,
249 ass_rec.Perf_Review_Period_Frequency,
250 X_Period_Of_Service_Id,
251 ass_rec.Probation_Period,
252 ass_rec.Probation_Unit,
253 ass_rec.Sal_Review_Period,
254 ass_rec.Sal_Review_Period_Frequency,
255 X_Set_Of_Books_Id,
256 ass_rec.Source_Type,
257 ass_rec.Time_Normal_Finish,
258 ass_rec.Time_Normal_Start,
259 X_Ass_Attribute_Category,
260 X_Ass_Attribute1,
261 X_Ass_Attribute2,
262 X_Ass_Attribute3,
263 X_Ass_Attribute4,
264 X_Ass_Attribute5,
265 X_Ass_Attribute6,
266 X_Ass_Attribute7,
267 X_Ass_Attribute8,
268 X_Ass_Attribute9,
269 X_Ass_Attribute10,
270 X_Ass_Attribute11,
271 X_Ass_Attribute12,
272 X_Ass_Attribute13,
273 X_Ass_Attribute14,
274 X_Ass_Attribute15,
275 X_Ass_Attribute16,
276 X_Ass_Attribute17,
277 X_Ass_Attribute18,
278 X_Ass_Attribute19,
279 X_Ass_Attribute20,
280 X_Ass_Attribute21,
281 X_Ass_Attribute22,
282 X_Ass_Attribute23,
283 X_Ass_Attribute24,
284 X_Ass_Attribute25,
285 X_Ass_Attribute26,
286 X_Ass_Attribute27,
287 X_Ass_Attribute28,
288 X_Ass_Attribute29,
289 X_Ass_Attribute30,
290 X_Last_Update_Date,
291 X_Last_Updated_By,
292 X_Last_Update_Login,
293 X_Created_By,
294 X_Creation_Date,
295 X_Title
296 );
297 open c2;
298 fetch c2 into X_ROWID;
299 if c2%notfound
300 then
301 raise NO_DATA_FOUND;
302 end if;
303 close c2;
304 --
305 open c3;
306 fetch c3 into asg_rec;
307 while c3%found loop
308 UPDATE PER_ASSIGNMENTS_F
309 SET EFFECTIVE_END_DATE = X_EFFECTIVE_START_DATE - 1,
310 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
311 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
312 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
313 WHERE CURRENT OF c3;
314 l_sql_count := l_sql_count + SQL%ROWCOUNT;
315 fetch c3 into asg_rec;
316 end loop;
317 --
318 -- If the rows do not tally raise an exception.
319 --
320 if c3%ROWCOUNT < l_sql_count then
321 raise NO_DATA_FOUND;
322 end if;
323 close c3;
324 END Insert_Row;
325 --
326 -- overload
327 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
328 X_Assignment_Id IN OUT NOCOPY NUMBER,
329 X_Effective_Start_Date DATE,
330 X_Effective_End_Date DATE,
331 X_Business_Group_Id NUMBER,
332 X_Job_Id NUMBER,
333 X_Assignment_Status_Type_Id IN OUT NOCOPY NUMBER,
334 X_Person_Id NUMBER,
335 X_Organization_Id NUMBER,
336 X_Assignment_Sequence IN OUT NOCOPY NUMBER,
337 X_Assignment_Type VARCHAR2,
338 X_Primary_Flag IN OUT NOCOPY VARCHAR2,
339 X_Assignment_Number IN OUT NOCOPY VARCHAR2,
340 X_Comment_Id NUMBER,
341 X_Period_Of_Service_Id NUMBER,
342 X_Default_Code_Comb_Id NUMBER,
343 X_Set_Of_Books_Id NUMBER,
344 X_Location_Id NUMBER,
345 X_Supervisor_Id NUMBER,
346 X_Ass_Attribute_Category VARCHAR2,
347 X_Ass_Attribute1 VARCHAR2,
348 X_Ass_Attribute2 VARCHAR2,
349 X_Ass_Attribute3 VARCHAR2,
350 X_Ass_Attribute4 VARCHAR2,
351 X_Ass_Attribute5 VARCHAR2,
352 X_Ass_Attribute6 VARCHAR2,
353 X_Ass_Attribute7 VARCHAR2,
354 X_Ass_Attribute8 VARCHAR2,
355 X_Ass_Attribute9 VARCHAR2,
356 X_Ass_Attribute10 VARCHAR2,
357 X_Ass_Attribute11 VARCHAR2,
358 X_Ass_Attribute12 VARCHAR2,
359 X_Ass_Attribute13 VARCHAR2,
360 X_Ass_Attribute14 VARCHAR2,
361 X_Ass_Attribute15 VARCHAR2,
362 X_Ass_Attribute16 VARCHAR2,
363 X_Ass_Attribute17 VARCHAR2,
364 X_Ass_Attribute18 VARCHAR2,
365 X_Ass_Attribute19 VARCHAR2,
366 X_Ass_Attribute20 VARCHAR2,
367 X_Ass_Attribute21 VARCHAR2,
368 X_Ass_Attribute22 VARCHAR2,
369 X_Ass_Attribute23 VARCHAR2,
370 X_Ass_Attribute24 VARCHAR2,
371 X_Ass_Attribute25 VARCHAR2,
372 X_Ass_Attribute26 VARCHAR2,
373 X_Ass_Attribute27 VARCHAR2,
374 X_Ass_Attribute28 VARCHAR2,
375 X_Ass_Attribute29 VARCHAR2,
376 X_Ass_Attribute30 VARCHAR2,
377 X_Last_Update_Date DATE,
378 X_Last_Updated_By NUMBER,
379 X_Last_Update_Login NUMBER,
380 X_Created_By NUMBER,
381 X_Creation_Date DATE,
382 X_Title VARCHAR2
383 ) IS
384 l_position_id NUMBER;
385 BEGIN
386 Insert_Row(X_Rowid ,
387 X_Assignment_Id ,
388 X_Effective_Start_Date ,
389 X_Effective_End_Date ,
390 X_Business_Group_Id ,
391 X_Job_Id ,
392 l_Position_Id ,
393 X_Assignment_Status_Type_Id ,
394 X_Person_Id ,
395 X_Organization_Id ,
396 X_Assignment_Sequence ,
397 X_Assignment_Type ,
398 X_Primary_Flag ,
399 X_Assignment_Number ,
400 X_Comment_Id ,
401 X_Period_Of_Service_Id ,
402 X_Default_Code_Comb_Id ,
403 X_Set_Of_Books_Id ,
404 X_Location_Id ,
405 X_Supervisor_Id ,
406 X_Ass_Attribute_Category ,
407 X_Ass_Attribute1 ,
408 X_Ass_Attribute2 ,
409 X_Ass_Attribute3 ,
410 X_Ass_Attribute4 ,
411 X_Ass_Attribute5 ,
412 X_Ass_Attribute6 ,
413 X_Ass_Attribute7 ,
414 X_Ass_Attribute8 ,
415 X_Ass_Attribute9 ,
416 X_Ass_Attribute10 ,
417 X_Ass_Attribute11 ,
418 X_Ass_Attribute12 ,
419 X_Ass_Attribute13 ,
420 X_Ass_Attribute14 ,
421 X_Ass_Attribute15 ,
422 X_Ass_Attribute16 ,
423 X_Ass_Attribute17 ,
424 X_Ass_Attribute18 ,
425 X_Ass_Attribute19 ,
426 X_Ass_Attribute20 ,
427 X_Ass_Attribute21 ,
428 X_Ass_Attribute22 ,
429 X_Ass_Attribute23 ,
430 X_Ass_Attribute24 ,
431 X_Ass_Attribute25 ,
432 X_Ass_Attribute26 ,
433 X_Ass_Attribute27 ,
434 X_Ass_Attribute28 ,
435 X_Ass_Attribute29 ,
436 X_Ass_Attribute30 ,
437 X_Last_Update_Date ,
438 X_Last_Updated_By ,
439 X_Last_Update_Login ,
440 X_Created_By ,
441 X_Creation_Date ,
442 X_Title);
443 END Insert_Row;
444 --
445 procedure delete_record(p_rowid VARCHAR2) is
446 begin
447 delete from per_assignments_f
448 where rowid = chartorowid(p_rowid);
449 end;
450 --
451 procedure get_enddate_and_defaults(p_effective_start_date IN OUT NOCOPY DATE
452 ,p_job_name IN OUT NOCOPY VARCHAR2
453 ,p_job_id IN OUT NOCOPY NUMBER
454 ,p_position_name IN OUT NOCOPY VARCHAR2
455 ,p_position_id IN OUT NOCOPY NUMBER
456 ,p_organization_name IN OUT NOCOPY VARCHAR2
457 ,p_organization_id IN OUT NOCOPY NUMBER
458 ,p_Assignment_Id IN OUT NOCOPY NUMBER
459 ,p_Effective_End_Date IN OUT NOCOPY DATE
460 ,p_Business_Group_Id IN OUT NOCOPY NUMBER
461 ,p_Assignment_Status_Type_Id IN OUT NOCOPY NUMBER
462 ,p_Person_Id IN OUT NOCOPY NUMBER
463 ,p_Period_of_service_id IN OUT NOCOPY NUMBER
464 ,p_Assignment_Sequence IN OUT NOCOPY NUMBER
465 ,p_Assignment_Type IN OUT NOCOPY VARCHAR2
466 ,p_Primary_Flag IN OUT NOCOPY VARCHAR2
467 ,p_Assignment_Number IN OUT NOCOPY VARCHAR2
468 ,p_Comment_Id IN OUT NOCOPY NUMBER
469 ,p_Set_Of_Books_Id IN OUT NOCOPY NUMBER
470 ,p_location_code IN OUT NOCOPY VARCHAR2
471 ,p_Location_Id IN OUT NOCOPY NUMBER
472 ,p_Supervisor_name IN OUT NOCOPY VARCHAR2
473 ,p_Supervisor_Id IN OUT NOCOPY NUMBER
474 ,p_Title IN OUT NOCOPY VARCHAR2
475 ,p_Ass_Attribute_Category IN OUT NOCOPY VARCHAR2
476 ,p_Ass_Attribute1 IN OUT NOCOPY VARCHAR2
477 ,p_Ass_Attribute2 IN OUT NOCOPY VARCHAR2
478 ,p_Ass_Attribute3 IN OUT NOCOPY VARCHAR2
479 ,p_Ass_Attribute4 IN OUT NOCOPY VARCHAR2
480 ,p_Ass_Attribute5 IN OUT NOCOPY VARCHAR2
481 ,p_Ass_Attribute6 IN OUT NOCOPY VARCHAR2
482 ,p_Ass_Attribute7 IN OUT NOCOPY VARCHAR2
483 ,p_Ass_Attribute8 IN OUT NOCOPY VARCHAR2
484 ,p_Ass_Attribute9 IN OUT NOCOPY VARCHAR2
485 ,p_Ass_Attribute10 IN OUT NOCOPY VARCHAR2
486 ,p_Ass_Attribute11 IN OUT NOCOPY VARCHAR2
487 ,p_Ass_Attribute12 IN OUT NOCOPY VARCHAR2
488 ,p_Ass_Attribute13 IN OUT NOCOPY VARCHAR2
489 ,p_Ass_Attribute14 IN OUT NOCOPY VARCHAR2
490 ,p_Ass_Attribute15 IN OUT NOCOPY VARCHAR2
491 ,p_Ass_Attribute16 IN OUT NOCOPY VARCHAR2
492 ,p_Ass_Attribute17 IN OUT NOCOPY VARCHAR2
493 ,p_Ass_Attribute18 IN OUT NOCOPY VARCHAR2
494 ,p_Ass_Attribute19 IN OUT NOCOPY VARCHAR2
495 ,p_Ass_Attribute20 IN OUT NOCOPY VARCHAR2
496 ,p_Ass_Attribute21 IN OUT NOCOPY VARCHAR2
497 ,p_Ass_Attribute22 IN OUT NOCOPY VARCHAR2
498 ,p_Ass_Attribute23 IN OUT NOCOPY VARCHAR2
499 ,p_Ass_Attribute24 IN OUT NOCOPY VARCHAR2
500 ,p_Ass_Attribute25 IN OUT NOCOPY VARCHAR2
501 ,p_Ass_Attribute26 IN OUT NOCOPY VARCHAR2
502 ,p_Ass_Attribute27 IN OUT NOCOPY VARCHAR2
503 ,p_Ass_Attribute28 IN OUT NOCOPY VARCHAR2
504 ,p_Ass_Attribute29 IN OUT NOCOPY VARCHAR2
505 ,p_Ass_Attribute30 IN OUT NOCOPY VARCHAR2
506 ,p_warning_message IN OUT NOCOPY VARCHAR2)
507 IS
508 --
509 -- Get tthe assignment whose effective_start_date is equal to the date
510 -- entered by the user.
511 --
512 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked position requirement
513 --
514 cursor get_asg_of_start_date
515 IS
516 SELECT NVL(p_ORGANIZATION_NAME, PO.NAME) ORGANIZATION_NAME,
517 NVL(p_ORGANIZATION_ID, PO.ORGANIZATION_ID) ORGANIZATION_ID,
518 NVL(p_JOB_NAME, PJ.NAME) JOB_NAME,
519 NVL(p_JOB_ID,PJ.JOB_ID) JOB_ID,
520 NVL(p_POSITION_NAME, PP.NAME) POSITION_NAME,
521 NVL(p_POSITION_ID,PP.POSITION_ID) POSITION_ID,
522 PAS.EFFECTIVE_END_DATE,
523 PAS.ASSIGNMENT_ID,
524 PAS.ASSIGNMENT_STATUS_TYPE_ID,
525 PAS.BUSINESS_GROUP_ID,
526 PAS.ASSIGNMENT_TYPE,
527 PAS.PRIMARY_FLAG,
528 PAS.COMMENT_ID,
529 PAS.ASSIGNMENT_SEQUENCE,
530 PAS.ASSIGNMENT_NUMBER,
531 PAS.PERIOD_OF_SERVICE_ID,
532 PAS.SET_OF_BOOKS_ID,
533 LOC.LOCATION_CODE,
534 PAS.LOCATION_ID,
535 PER.FULL_NAME SUPERVISOR_NAME,
536 PAS.TITLE,
537 PAS.SUPERVISOR_ID,
538 PAS.ASS_ATTRIBUTE_CATEGORY,
539 PAS.ASS_ATTRIBUTE1,
540 PAS.ASS_ATTRIBUTE2,
541 PAS.ASS_ATTRIBUTE3,
542 PAS.ASS_ATTRIBUTE4,
543 PAS.ASS_ATTRIBUTE5,
544 PAS.ASS_ATTRIBUTE6,
545 PAS.ASS_ATTRIBUTE7,
546 PAS.ASS_ATTRIBUTE8,
547 PAS.ASS_ATTRIBUTE9,
548 PAS.ASS_ATTRIBUTE10,
549 PAS.ASS_ATTRIBUTE11,
550 PAS.ASS_ATTRIBUTE12,
551 PAS.ASS_ATTRIBUTE13,
552 PAS.ASS_ATTRIBUTE14,
553 PAS.ASS_ATTRIBUTE15,
554 PAS.ASS_ATTRIBUTE16,
555 PAS.ASS_ATTRIBUTE17,
556 PAS.ASS_ATTRIBUTE18,
557 PAS.ASS_ATTRIBUTE19,
558 PAS.ASS_ATTRIBUTE20,
559 PAS.ASS_ATTRIBUTE21,
560 PAS.ASS_ATTRIBUTE22,
561 PAS.ASS_ATTRIBUTE23,
562 PAS.ASS_ATTRIBUTE24,
563 PAS.ASS_ATTRIBUTE25,
564 PAS.ASS_ATTRIBUTE26,
565 PAS.ASS_ATTRIBUTE27,
566 PAS.ASS_ATTRIBUTE28,
567 PAS.ASS_ATTRIBUTE29,
568 PAS.ASS_ATTRIBUTE30,
569 PAS.EFFECTIVE_START_DATE
570 FROM PER_ALL_PEOPLE_F PER,
571 PER_JOBS_V PJ,
572 HR_POSITIONS_F PP,
573 HR_LOCATIONS LOC,
574 PER_ORGANIZATION_UNITS_PERF PO,
575 PER_ASSIGNMENTS_F PAS
576 WHERE PJ.JOB_ID(+) = PAS.JOB_ID
577 AND PP.POSITION_ID(+) = PAS.POSITION_ID
578 AND PP.JOB_ID(+) = PAS.JOB_ID
579 AND PAS.ORGANIZATION_ID = PO.ORGANIZATION_ID
580 AND LOC.LOCATION_ID (+) = PAS.LOCATION_ID
581 AND PER.PERSON_ID (+) = PAS.SUPERVISOR_ID
582 AND PAS.EFFECTIVE_START_DATE
583 between PP.EFFECTIVE_START_DATE
584 and PP.EFFECTIVE_END_DATE
585 AND PAS.EFFECTIVE_START_DATE
586 between nvl(PER.EFFECTIVE_START_DATE,PAS.EFFECTIVE_START_DATE)
587 and PAS.EFFECTIVE_END_DATE
588 AND PAS.PERSON_ID = p_person_id
589 AND PAS.ASSIGNMENT_ID = p_assignment_ID
590 AND PAS.EFFECTIVE_START_DATE = p_effective_start_date
591 AND PAS.PERIOD_OF_SERVICE_ID = p_period_of_service_id;
592 --
593 -- Get the minimum ene date for the current assignment
594 -- where a row exists after the date entered.
595 --
596 cursor get_minimum_end
597 IS
598 SELECT MIN(EFFECTIVE_START_DATE) - 1
599 FROM PER_ASSIGNMENTS_F
600 WHERE EFFECTIVE_START_DATE >
601 p_EFFECTIVE_START_DATE
602 AND PERSON_ID = p_PERSON_ID
603 and assignment_id =p_assignment_id
604 and period_of_service_id = p_period_of_service_id;
605 --
606 -- Get the minimum effective end date
607 -- where the effective end date is greater than the
608 -- current start.
609 --
610 cursor get_end_date
611 IS
612 SELECT MIN(EFFECTIVE_END_DATE)
613 FROM PER_ASSIGNMENTS_F
614 WHERE EFFECTIVE_END_DATE >
615 p_effective_start_date
616 AND PERSON_ID = p_person_id
617 and assignment_id =p_assignment_id
618 and period_of_service_id = p_period_of_service_id;
619 --
620 -- Get's the row which exists aroun the date entered
621 -- if none exists then get the first row
622 --
623 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked position requirement
624 --
625 -- #2720080: added effective date parameter, and removed UNION statement.
626 --
627 cursor get_row_between(p_effective_date DATE)
628 IS
629 SELECT NVL(p_ORGANIZATION_NAME, PO.NAME) ORGANIZATION_NAME,
630 NVL(p_ORGANIZATION_ID, PO.ORGANIZATION_ID) ORGANIZATION_ID,
631 NVL(p_JOB_NAME, PJ.NAME) JOB_NAME,
632 NVL(p_JOB_ID,PJ.JOB_ID) JOB_ID,
633 NVL(p_POSITION_NAME, PP.NAME) POSITION_NAME,
634 NVL(p_POSITION_ID,PP.POSITION_ID) POSITION_ID,
635 PAS.EFFECTIVE_END_DATE,
636 PAS.ASSIGNMENT_ID,
637 PAS.ASSIGNMENT_STATUS_TYPE_ID,
638 PAS.BUSINESS_GROUP_ID,
639 PAS.ASSIGNMENT_TYPE,
640 PAS.PRIMARY_FLAG,
641 PAS.COMMENT_ID,
642 PAS.ASSIGNMENT_SEQUENCE,
643 PAS.ASSIGNMENT_NUMBER,
644 PAS.PERIOD_OF_SERVICE_ID,
645 PAS.SET_OF_BOOKS_ID,
646 LOC.LOCATION_CODE,
647 PAS.LOCATION_ID,
648 PER.FULL_NAME SUPERVISOR_NAME,
649 PAS.TITLE,
650 PAS.SUPERVISOR_ID,
651 PAS.ASS_ATTRIBUTE_CATEGORY,
652 PAS.ASS_ATTRIBUTE1,
653 PAS.ASS_ATTRIBUTE2,
654 PAS.ASS_ATTRIBUTE3,
655 PAS.ASS_ATTRIBUTE4,
656 PAS.ASS_ATTRIBUTE5,
657 PAS.ASS_ATTRIBUTE6,
658 PAS.ASS_ATTRIBUTE7,
659 PAS.ASS_ATTRIBUTE8,
660 PAS.ASS_ATTRIBUTE9,
661 PAS.ASS_ATTRIBUTE10,
662 PAS.ASS_ATTRIBUTE11,
663 PAS.ASS_ATTRIBUTE12,
664 PAS.ASS_ATTRIBUTE13,
665 PAS.ASS_ATTRIBUTE14,
666 PAS.ASS_ATTRIBUTE15,
667 PAS.ASS_ATTRIBUTE16,
668 PAS.ASS_ATTRIBUTE17,
669 PAS.ASS_ATTRIBUTE18,
670 PAS.ASS_ATTRIBUTE19,
671 PAS.ASS_ATTRIBUTE20,
672 PAS.ASS_ATTRIBUTE21,
673 PAS.ASS_ATTRIBUTE22,
674 PAS.ASS_ATTRIBUTE23,
675 PAS.ASS_ATTRIBUTE24,
676 PAS.ASS_ATTRIBUTE25,
677 PAS.ASS_ATTRIBUTE26,
678 PAS.ASS_ATTRIBUTE27,
679 PAS.ASS_ATTRIBUTE28,
680 PAS.ASS_ATTRIBUTE29,
681 PAS.ASS_ATTRIBUTE30,
682 PAS.EFFECTIVE_START_DATE
683 FROM PER_PEOPLE_F PER,
684 PER_JOBS_V PJ,
685 HR_POSITIONS_F PP,
686 HR_LOCATIONS LOC,
687 PER_ORGANIZATION_UNITS_PERF PO,
688 PER_ASSIGNMENTS_F PAS
689 WHERE
690 PAS.JOB_ID = PJ.JOB_ID (+)
691 AND PP.POSITION_ID(+) = PAS.POSITION_ID
692 AND PP.JOB_ID(+) = PAS.JOB_ID
693 AND PAS.ORGANIZATION_ID = PO.ORGANIZATION_ID
694 AND PAS.PERSON_ID = p_person_id
695 AND LOC.LOCATION_ID (+) = PAS.LOCATION_ID
696 --
697 AND PER.PERSON_ID (+) = PAS.SUPERVISOR_ID
698 AND p_effective_date between PER.effective_start_date(+) and PER.effective_end_date(+)
699 --
700 AND PAS.EFFECTIVE_START_DATE
701 between PP.EFFECTIVE_START_DATE(+) -- #2720080
702 and PP.EFFECTIVE_END_DATE(+)
703 --
704 AND PAS.ASSIGNMENT_ID = p_assignment_ID
705 AND p_effective_start_date BETWEEN
706 PAS.EFFECTIVE_START_DATE AND PAS.EFFECTIVE_END_DATE
707 AND PAS.PERIOD_OF_SERVICE_ID = p_period_of_service_id;
708 --
709 ass_rec get_asg_of_start_date%rowtype;
710 l_effective_date date; --#2720080
711 --
712 begin
713 --
714 -- check the date is valid.
715 --
716 per_assignments_v8_pkg.validate_effective_start(p_effective_start_date
717 ,p_person_id
718 ,p_period_of_service_id);
719 open get_asg_of_start_date;
720 fetch get_asg_of_start_date into ass_rec;
721 --
722 -- If record is found , then display warning message
723 -- otherwise open secondary cursor.
724 --
725 if get_asg_of_start_date%FOUND
726 then
727 p_warning_message := 'Y';
728 p_effective_end_Date := ass_rec.effective_end_Date;
729 fnd_message.set_name('PA','PA_SU_DUP_ASSIGNMENT');
730 else
731 --
732 --
733 close get_asg_of_start_date;
734 --
735 open get_minimum_end;
736 fetch get_minimum_end into p_effective_end_date;
737 close get_minimum_end;
738 --
739 if p_effective_end_date is null
740 then
741 open get_end_date;
742 fetch get_end_date into p_effective_end_date;
743 close get_end_date;
744 end if;
745 --
746 -- #2720080: needs to get greatest of sysdate or new effective start date
747 -- this is to ensure supervisor_LOV does not fail when defaulting value.
748 --
749 if sysdate > p_effective_start_date then
750 l_effective_date := sysdate;
751 else
752 l_effective_date := p_effective_start_date;
753 end if;
754 --
755 open get_row_between(l_effective_date);
756 fetch get_row_between into ass_rec;
757 close get_row_between;
758 end if;
759 --
760 p_assignment_id := ass_rec.assignment_id;
761 p_business_group_id := ass_rec.business_group_id;
762 p_job_id := ass_rec.job_id;
763 p_job_name := ass_rec.job_name;
764 p_position_id := ass_rec.position_id;
765 p_position_name := ass_rec.position_name;
766 p_organization_id := ass_rec.organization_id;
767 p_organization_name := ass_rec.organization_name;
768 p_assignment_sequence := ass_rec.assignment_sequence;
769 p_assignment_status_type_id := ass_rec.assignment_status_type_id;
770 p_assignment_type := ass_rec.assignment_type;
771 p_primary_flag := ass_rec.primary_flag;
772 p_comment_id := ass_rec.comment_id;
773 p_set_of_books_id := ass_rec.set_of_books_id;
774 p_supervisor_name := ass_rec.supervisor_name;
775 p_supervisor_id := ass_rec.supervisor_id;
776 p_location_code := ass_rec.location_code;
777 p_location_id := ass_rec.location_id;
778 p_title := ass_rec.title;
779 p_ass_attribute_category := ass_rec.ass_attribute_category;
780 p_ass_attribute1 := ass_rec.ass_attribute1;
781 p_ass_attribute2 := ass_rec.ass_attribute2;
782 p_ass_attribute3 := ass_rec.ass_attribute3;
783 p_ass_attribute4 := ass_rec.ass_attribute4;
784 p_ass_attribute5 := ass_rec.ass_attribute5;
785 p_ass_attribute6 := ass_rec.ass_attribute6;
786 p_ass_attribute7 := ass_rec.ass_attribute7;
787 p_ass_attribute8 := ass_rec.ass_attribute8;
788 p_ass_attribute9 := ass_rec.ass_attribute9;
789 p_ass_attribute10 := ass_rec.ass_attribute10;
790 p_ass_attribute11 := ass_rec.ass_attribute11;
791 p_ass_attribute12 := ass_rec.ass_attribute12;
792 p_ass_attribute13 := ass_rec.ass_attribute13;
793 p_ass_attribute14 := ass_rec.ass_attribute14;
794 p_ass_attribute15 := ass_rec.ass_attribute15;
795 p_ass_attribute16 := ass_rec.ass_attribute16;
796 p_ass_attribute17 := ass_rec.ass_attribute17;
797 p_ass_attribute18 := ass_rec.ass_attribute18;
798 p_ass_attribute19 := ass_rec.ass_attribute19;
799 p_ass_attribute20 := ass_rec.ass_attribute20;
800 p_ass_attribute21 := ass_rec.ass_attribute21;
801 p_ass_attribute22 := ass_rec.ass_attribute22;
802 p_ass_attribute23 := ass_rec.ass_attribute23;
803 p_ass_attribute24 := ass_rec.ass_attribute24;
804 p_ass_attribute25 := ass_rec.ass_attribute25;
805 p_ass_attribute26 := ass_rec.ass_attribute26;
806 p_ass_attribute27 := ass_rec.ass_attribute27;
807 p_ass_attribute28 := ass_rec.ass_attribute28;
808 p_ass_attribute29 := ass_rec.ass_attribute29;
809 p_ass_attribute30 := ass_rec.ass_attribute30;
810 if p_effective_end_date is null
811 then
812 p_effective_end_date := ass_rec.effective_end_date;
813 end if;
814 end;
815 --
816 -- overload
817 procedure get_enddate_and_defaults(p_effective_start_date IN OUT NOCOPY DATE
818 ,p_job_name IN OUT NOCOPY VARCHAR2
819 ,p_job_id IN OUT NOCOPY NUMBER
820 ,p_organization_name IN OUT NOCOPY VARCHAR2
821 ,p_organization_id IN OUT NOCOPY NUMBER
822 ,p_Assignment_Id IN OUT NOCOPY NUMBER
823 ,p_Effective_End_Date IN OUT NOCOPY DATE
824 ,p_Business_Group_Id IN OUT NOCOPY NUMBER
825 ,p_Assignment_Status_Type_Id IN OUT NOCOPY NUMBER
826 ,p_Person_Id IN OUT NOCOPY NUMBER
827 ,p_Period_of_service_id IN OUT NOCOPY NUMBER
828 ,p_Assignment_Sequence IN OUT NOCOPY NUMBER
829 ,p_Assignment_Type IN OUT NOCOPY VARCHAR2
830 ,p_Primary_Flag IN OUT NOCOPY VARCHAR2
831 ,p_Assignment_Number IN OUT NOCOPY VARCHAR2
832 ,p_Comment_Id IN OUT NOCOPY NUMBER
833 ,p_Set_Of_Books_Id IN OUT NOCOPY NUMBER
834 ,p_location_code IN OUT NOCOPY VARCHAR2
835 ,p_Location_Id IN OUT NOCOPY NUMBER
836 ,p_Supervisor_name IN OUT NOCOPY VARCHAR2
837 ,p_Supervisor_Id IN OUT NOCOPY NUMBER
838 ,p_Title IN OUT NOCOPY VARCHAR2
839 ,p_Ass_Attribute_Category IN OUT NOCOPY VARCHAR2
840 ,p_Ass_Attribute1 IN OUT NOCOPY VARCHAR2
841 ,p_Ass_Attribute2 IN OUT NOCOPY VARCHAR2
842 ,p_Ass_Attribute3 IN OUT NOCOPY VARCHAR2
843 ,p_Ass_Attribute4 IN OUT NOCOPY VARCHAR2
844 ,p_Ass_Attribute5 IN OUT NOCOPY VARCHAR2
845 ,p_Ass_Attribute6 IN OUT NOCOPY VARCHAR2
846 ,p_Ass_Attribute7 IN OUT NOCOPY VARCHAR2
847 ,p_Ass_Attribute8 IN OUT NOCOPY VARCHAR2
848 ,p_Ass_Attribute9 IN OUT NOCOPY VARCHAR2
849 ,p_Ass_Attribute10 IN OUT NOCOPY VARCHAR2
850 ,p_Ass_Attribute11 IN OUT NOCOPY VARCHAR2
851 ,p_Ass_Attribute12 IN OUT NOCOPY VARCHAR2
852 ,p_Ass_Attribute13 IN OUT NOCOPY VARCHAR2
853 ,p_Ass_Attribute14 IN OUT NOCOPY VARCHAR2
854 ,p_Ass_Attribute15 IN OUT NOCOPY VARCHAR2
855 ,p_Ass_Attribute16 IN OUT NOCOPY VARCHAR2
856 ,p_Ass_Attribute17 IN OUT NOCOPY VARCHAR2
857 ,p_Ass_Attribute18 IN OUT NOCOPY VARCHAR2
858 ,p_Ass_Attribute19 IN OUT NOCOPY VARCHAR2
859 ,p_Ass_Attribute20 IN OUT NOCOPY VARCHAR2
860 ,p_Ass_Attribute21 IN OUT NOCOPY VARCHAR2
861 ,p_Ass_Attribute22 IN OUT NOCOPY VARCHAR2
862 ,p_Ass_Attribute23 IN OUT NOCOPY VARCHAR2
863 ,p_Ass_Attribute24 IN OUT NOCOPY VARCHAR2
864 ,p_Ass_Attribute25 IN OUT NOCOPY VARCHAR2
865 ,p_Ass_Attribute26 IN OUT NOCOPY VARCHAR2
866 ,p_Ass_Attribute27 IN OUT NOCOPY VARCHAR2
867 ,p_Ass_Attribute28 IN OUT NOCOPY VARCHAR2
868 ,p_Ass_Attribute29 IN OUT NOCOPY VARCHAR2
869 ,p_Ass_Attribute30 IN OUT NOCOPY VARCHAR2
870 ,p_warning_message IN OUT NOCOPY VARCHAR2)
871 IS
872 --
873 l_position_name VARCHAR2(30);
874 l_position_id NUMBER;
875 BEGIN
876 get_enddate_and_defaults(p_effective_start_date
877 ,p_job_name
878 ,p_job_id
879 ,l_position_name
880 ,l_position_id
881 ,p_organization_name
882 ,p_organization_id
883 ,p_Assignment_Id
884 ,p_Effective_End_Date
885 ,p_Business_Group_Id
886 ,p_Assignment_Status_Type_Id
887 ,p_Person_Id
888 ,p_Period_of_service_id
889 ,p_Assignment_Sequence
890 ,p_Assignment_Type
891 ,p_Primary_Flag
892 ,p_Assignment_Number
893 ,p_Comment_Id
894 ,p_Set_Of_Books_Id
895 ,p_location_code
896 ,p_Location_Id
897 ,p_Supervisor_name
898 ,p_Supervisor_Id
899 ,p_Title
900 ,p_Ass_Attribute_Category
901 ,p_Ass_Attribute1
902 ,p_Ass_Attribute2
903 ,p_Ass_Attribute3
904 ,p_Ass_Attribute4
905 ,p_Ass_Attribute5
906 ,p_Ass_Attribute6
907 ,p_Ass_Attribute7
908 ,p_Ass_Attribute8
909 ,p_Ass_Attribute9
910 ,p_Ass_Attribute10
911 ,p_Ass_Attribute11
912 ,p_Ass_Attribute12
913 ,p_Ass_Attribute13
914 ,p_Ass_Attribute14
915 ,p_Ass_Attribute15
916 ,p_Ass_Attribute16
917 ,p_Ass_Attribute17
918 ,p_Ass_Attribute18
919 ,p_Ass_Attribute19
920 ,p_Ass_Attribute20
921 ,p_Ass_Attribute21
922 ,p_Ass_Attribute22
923 ,p_Ass_Attribute23
924 ,p_Ass_Attribute24
925 ,p_Ass_Attribute25
926 ,p_Ass_Attribute26
927 ,p_Ass_Attribute27
928 ,p_Ass_Attribute28
929 ,p_Ass_Attribute29
930 ,p_Ass_Attribute30
931 ,p_warning_message);
932 END get_enddate_and_defaults;
933 --
934 procedure validate_effective_start(p_effective_start_date DATE
935 ,p_person_id NUMBER
936 ,p_period_of_service_id NUMBER)
937 IS
938 --
939 -- Get start date from current period of service
940 -- Not as before in getting the
941 -- Earliest effective start date from per_people_f
942 -- As this may cause an assignment to span multiple periods_of_service
943 -- Which it cannot by business_rule.
944 --
945 cursor get_per_start_date
946 is
947 select p.date_start
948 from per_periods_of_service p
949 where p.person_id = p_person_id
950 and p.period_of_service_id = p_period_of_service_id;
951 --
952 cursor get_termination_date
953 is
954 select pos.actual_termination_date
955 from per_periods_of_service pos
956 where pos.person_id = p_person_id
957 and pos.period_of_service_id = p_period_of_service_id;
958 --
959 p_termination_date DATE;
960 p_start_date DATE;
961 begin
962 open get_per_start_date;
963 fetch get_per_start_date into p_start_date;
964 close get_per_start_date;
965 --
966 open get_termination_date;
967 fetch get_termination_date into p_termination_date;
968 close get_termination_date;
969 --
970 if p_start_date > p_effective_start_date
971 then
972 FND_MESSAGE.SET_NAME('PA','PA_ALL_START_DATE_AFTER');
973 FND_MESSAGE.SET_TOKEN('S_DATE',to_char(p_start_date,'DD-MON-YYYY'));
974 HR_UTILITY.RAISE_ERROR;
975 elsif ((p_termination_date is not null)
976 and (p_effective_start_date > p_termination_date))
977 then
978 FND_MESSAGE.SET_NAME('PA','PA_ALL_START_DATE_BEFORE');
979 FND_MESSAGE.SET_TOKEN('T_DATE',
980 to_char(p_termination_date,'DD-MON-YYYY'));
981 HR_UTILITY.RAISE_ERROR;
982 end if;
983 end;
984 END PER_ASSIGNMENTS_V8_PKG;