[Home] [Help]
PACKAGE BODY: APPS.PER_SECONDARY_ASS_STATUSES_PKG
Source
1 PACKAGE BODY PER_SECONDARY_ASS_STATUSES_PKG as
2 /* $Header: pesas01t.pkb 115.2 2003/02/10 17:09:39 eumenyio ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8 /*-----------------------------------------------------------------------------
9
10 Description
11 -----------
12
13
14 History
15 -------
16 Date Author Version Description
17 --------- ---------- ------ ----------------------------------
18 18-Apr-93 JRhodes 80.1 Changed Lock-Row to rtrim varchar2 columns
19 27-Jan-95 JRhodes 70.5 Removed AOL WHO Columns
20 05-Mar-97 JAlloun 70.6 Changed all occurances of system.dual to
21 sys.dual for next release requirements.
22 -----------------------------------------------------------------------------*/
23
24 procedure check_unique_row(p_assignment_id number
25 ,p_assignment_status_type_id number
26 ,p_start_date date
27 ,p_rowid varchar2);
28 --
29 procedure validate_date(p_assignment_id number
30 ,p_date date
31 ,p_date_type varchar2);
32 --
33 procedure validate_start_end_date(p_start_date date
34 ,p_end_date date);
35 --
36 function set_end_date(p_assignment_id number
37 ,p_end_date date) return date;
38 --
39 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
40 X_Secondary_Ass_Status_Id IN OUT NOCOPY NUMBER,
41 X_Business_Group_Id NUMBER,
42 X_Assignment_Id NUMBER,
43 X_Assignment_Status_Type_Id NUMBER,
44 X_Start_Date DATE,
45 X_Comments VARCHAR2,
46 X_End_Date IN OUT NOCOPY DATE,
47 X_Reason VARCHAR2,
48 X_Attribute_Category VARCHAR2,
49 X_Attribute1 VARCHAR2,
50 X_Attribute2 VARCHAR2,
51 X_Attribute3 VARCHAR2,
52 X_Attribute4 VARCHAR2,
53 X_Attribute5 VARCHAR2,
54 X_Attribute6 VARCHAR2,
55 X_Attribute7 VARCHAR2,
56 X_Attribute8 VARCHAR2,
57 X_Attribute9 VARCHAR2,
58 X_Attribute10 VARCHAR2,
59 X_Attribute11 VARCHAR2,
60 X_Attribute12 VARCHAR2,
61 X_Attribute13 VARCHAR2,
62 X_Attribute14 VARCHAR2,
63 X_Attribute15 VARCHAR2,
64 X_Attribute16 VARCHAR2,
65 X_Attribute17 VARCHAR2,
66 X_Attribute18 VARCHAR2,
67 X_Attribute19 VARCHAR2,
68 X_Attribute20 VARCHAR2
69 ) IS
70 CURSOR C IS SELECT rowid FROM PER_SECONDARY_ASS_STATUSES
71 WHERE secondary_ass_status_id = X_Secondary_Ass_Status_Id;
72
73 CURSOR C2 IS SELECT per_secondary_ass_statuses_s.nextval FROM sys.dual;
74 BEGIN
75 --
76 check_unique_row(X_Assignment_Id
77 ,X_Assignment_Status_Type_Id
78 ,X_Start_Date
79 ,X_Rowid);
80 --
81 validate_date(X_Assignment_Id
82 ,X_Start_Date
83 ,'S');
84 --
85 if X_End_Date is not null then
86 validate_date(X_Assignment_Id
87 ,X_End_Date
88 ,'E');
89 --
90 validate_start_end_date(X_Start_Date
91 ,X_End_Date);
92 --
93 end if;
94 --
95 X_End_Date := set_end_date(X_Assignment_Id,X_End_Date);
96 --
97 if (X_Secondary_Ass_Status_Id is NULL) then
98 OPEN C2;
99 FETCH C2 INTO X_Secondary_Ass_Status_Id;
100 CLOSE C2;
101 end if;
102 INSERT INTO PER_SECONDARY_ASS_STATUSES(
103 secondary_ass_status_id,
104 business_group_id,
105 assignment_id,
106 assignment_status_type_id,
107 start_date,
108 comments,
109 end_date,
110 reason,
111 attribute_category,
112 attribute1,
113 attribute2,
114 attribute3,
115 attribute4,
116 attribute5,
117 attribute6,
118 attribute7,
119 attribute8,
120 attribute9,
121 attribute10,
122 attribute11,
123 attribute12,
124 attribute13,
125 attribute14,
126 attribute15,
127 attribute16,
128 attribute17,
129 attribute18,
130 attribute19,
131 attribute20
132 ) VALUES (
133 X_Secondary_Ass_Status_Id,
134 X_Business_Group_Id,
135 X_Assignment_Id,
136 X_Assignment_Status_Type_Id,
137 X_Start_Date,
138 X_Comments,
139 X_End_Date,
140 X_Reason,
141 X_Attribute_Category,
142 X_Attribute1,
143 X_Attribute2,
144 X_Attribute3,
145 X_Attribute4,
146 X_Attribute5,
147 X_Attribute6,
148 X_Attribute7,
149 X_Attribute8,
150 X_Attribute9,
151 X_Attribute10,
152 X_Attribute11,
153 X_Attribute12,
154 X_Attribute13,
155 X_Attribute14,
156 X_Attribute15,
157 X_Attribute16,
158 X_Attribute17,
159 X_Attribute18,
160 X_Attribute19,
161 X_Attribute20
162 );
163
164 OPEN C;
165 FETCH C INTO X_Rowid;
166 if (C%NOTFOUND) then
167 CLOSE C;
168 RAISE NO_DATA_FOUND;
169 end if;
170 CLOSE C;
171 END Insert_Row;
172
173 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
174 X_Secondary_Ass_Status_Id NUMBER,
175 X_Business_Group_Id NUMBER,
176 X_Assignment_Id NUMBER,
177 X_Assignment_Status_Type_Id NUMBER,
178 X_Start_Date DATE,
182 X_Attribute_Category VARCHAR2,
179 X_Comments VARCHAR2,
180 X_End_Date DATE,
181 X_Reason VARCHAR2,
183 X_Attribute1 VARCHAR2,
184 X_Attribute2 VARCHAR2,
185 X_Attribute3 VARCHAR2,
186 X_Attribute4 VARCHAR2,
187 X_Attribute5 VARCHAR2,
188 X_Attribute6 VARCHAR2,
189 X_Attribute7 VARCHAR2,
190 X_Attribute8 VARCHAR2,
191 X_Attribute9 VARCHAR2,
192 X_Attribute10 VARCHAR2,
193 X_Attribute11 VARCHAR2,
194 X_Attribute12 VARCHAR2,
195 X_Attribute13 VARCHAR2,
196 X_Attribute14 VARCHAR2,
197 X_Attribute15 VARCHAR2,
198 X_Attribute16 VARCHAR2,
199 X_Attribute17 VARCHAR2,
200 X_Attribute18 VARCHAR2,
201 X_Attribute19 VARCHAR2,
202 X_Attribute20 VARCHAR2
203 ) IS
204 CURSOR C IS
205 SELECT *
206 FROM PER_SECONDARY_ASS_STATUSES
207 WHERE rowid = X_Rowid
208 FOR UPDATE of Secondary_Ass_Status_Id NOWAIT;
209 Recinfo C%ROWTYPE;
210 BEGIN
211 OPEN C;
212 FETCH C INTO Recinfo;
213 if (C%NOTFOUND) then
214 CLOSE C;
215 RAISE NO_DATA_FOUND;
216 end if;
217 CLOSE C;
218 --
219 Recinfo.comments := rtrim(Recinfo.comments);
220 Recinfo.reason := rtrim(Recinfo.reason);
221 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
222 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
223 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
224 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
225 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
226 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
227 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
228 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
229 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
230 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
231 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
232 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
233 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
234 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
235 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
236 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
237 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
238 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
239 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
240 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
241 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
242 --
243 if (
244 ( (Recinfo.secondary_ass_status_id = X_Secondary_Ass_Status_Id)
245 OR ( (Recinfo.secondary_ass_status_id IS NULL)
246 AND (X_Secondary_Ass_Status_Id IS NULL)))
247 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
248 OR ( (Recinfo.business_group_id IS NULL)
249 AND (X_Business_Group_Id IS NULL)))
250 AND ( (Recinfo.assignment_id = X_Assignment_Id)
251 OR ( (Recinfo.assignment_id IS NULL)
252 AND (X_Assignment_Id IS NULL)))
253 AND ( (Recinfo.assignment_status_type_id = X_Assignment_Status_Type_Id)
254 OR ( (Recinfo.assignment_status_type_id IS NULL)
255 AND (X_Assignment_Status_Type_Id IS NULL)))
256 AND ( (Recinfo.start_date = X_Start_Date)
257 OR ( (Recinfo.start_date IS NULL)
258 AND (X_Start_Date IS NULL)))
259 AND ( (Recinfo.comments = X_Comments)
260 OR ( (Recinfo.comments IS NULL)
261 AND (X_Comments IS NULL)))
262 AND ( (Recinfo.end_date = X_End_Date)
263 OR ( (Recinfo.end_date IS NULL)
264 AND (X_End_Date IS NULL)))
265 AND ( (Recinfo.reason = X_Reason)
266 OR ( (Recinfo.reason IS NULL)
267 AND (X_Reason IS NULL)))
268 AND ( (Recinfo.attribute_category = X_Attribute_Category)
269 OR ( (Recinfo.attribute_category IS NULL)
270 AND (X_Attribute_Category IS NULL)))
271 AND ( (Recinfo.attribute1 = X_Attribute1)
272 OR ( (Recinfo.attribute1 IS NULL)
273 AND (X_Attribute1 IS NULL)))
274 AND ( (Recinfo.attribute2 = X_Attribute2)
275 OR ( (Recinfo.attribute2 IS NULL)
276 AND (X_Attribute2 IS NULL)))
277 AND ( (Recinfo.attribute3 = X_Attribute3)
278 OR ( (Recinfo.attribute3 IS NULL)
279 AND (X_Attribute3 IS NULL)))
280 AND ( (Recinfo.attribute4 = X_Attribute4)
281 OR ( (Recinfo.attribute4 IS NULL)
282 AND (X_Attribute4 IS NULL)))
283 AND ( (Recinfo.attribute5 = X_Attribute5)
284 OR ( (Recinfo.attribute5 IS NULL)
285 AND (X_Attribute5 IS NULL)))
286 AND ( (Recinfo.attribute6 = X_Attribute6)
287 OR ( (Recinfo.attribute6 IS NULL)
288 AND (X_Attribute6 IS NULL)))
289 AND ( (Recinfo.attribute7 = X_Attribute7)
290 OR ( (Recinfo.attribute7 IS NULL)
291 AND (X_Attribute7 IS NULL)))
292 AND ( (Recinfo.attribute8 = X_Attribute8)
293 OR ( (Recinfo.attribute8 IS NULL)
294 AND (X_Attribute8 IS NULL)))
295 AND ( (Recinfo.attribute9 = X_Attribute9)
296 OR ( (Recinfo.attribute9 IS NULL)
297 AND (X_Attribute9 IS NULL)))
298 AND ( (Recinfo.attribute10 = X_Attribute10)
299 OR ( (Recinfo.attribute10 IS NULL)
300 AND (X_Attribute10 IS NULL)))
301 AND ( (Recinfo.attribute11 = X_Attribute11)
302 OR ( (Recinfo.attribute11 IS NULL)
303 AND (X_Attribute11 IS NULL)))
304 AND ( (Recinfo.attribute12 = X_Attribute12)
305 OR ( (Recinfo.attribute12 IS NULL)
306 AND (X_Attribute12 IS NULL)))
307 AND ( (Recinfo.attribute13 = X_Attribute13)
308 OR ( (Recinfo.attribute13 IS NULL)
309 AND (X_Attribute13 IS NULL)))
310 AND ( (Recinfo.attribute14 = X_Attribute14)
311 OR ( (Recinfo.attribute14 IS NULL)
312 AND (X_Attribute14 IS NULL)))
313 AND ( (Recinfo.attribute15 = X_Attribute15)
314 OR ( (Recinfo.attribute15 IS NULL)
315 AND (X_Attribute15 IS NULL)))
316 AND ( (Recinfo.attribute16 = X_Attribute16)
317 OR ( (Recinfo.attribute16 IS NULL)
318 AND (X_Attribute16 IS NULL)))
319 AND ( (Recinfo.attribute17 = X_Attribute17)
320 OR ( (Recinfo.attribute17 IS NULL)
321 AND (X_Attribute17 IS NULL)))
322 AND ( (Recinfo.attribute18 = X_Attribute18)
323 OR ( (Recinfo.attribute18 IS NULL)
324 AND (X_Attribute18 IS NULL)))
325 AND ( (Recinfo.attribute19 = X_Attribute19)
326 OR ( (Recinfo.attribute19 IS NULL)
327 AND (X_Attribute19 IS NULL)))
328 AND ( (Recinfo.attribute20 = X_Attribute20)
329 OR ( (Recinfo.attribute20 IS NULL)
330 AND (X_Attribute20 IS NULL)))
331 ) then
332 return;
333 else
334 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
335 APP_EXCEPTION.RAISE_EXCEPTION;
336 end if;
337 END Lock_Row;
338
339 PROCEDURE Update_Row(X_Rowid VARCHAR2,
340 X_Secondary_Ass_Status_Id NUMBER,
341 X_Business_Group_Id NUMBER,
342 X_Assignment_Id NUMBER,
343 X_Assignment_Status_Type_Id NUMBER,
344 X_Start_Date DATE,
345 X_Comments VARCHAR2,
346 X_End_Date IN OUT NOCOPY DATE,
347 X_Reason VARCHAR2,
348 X_Attribute_Category VARCHAR2,
349 X_Attribute1 VARCHAR2,
350 X_Attribute2 VARCHAR2,
351 X_Attribute3 VARCHAR2,
352 X_Attribute4 VARCHAR2,
353 X_Attribute5 VARCHAR2,
354 X_Attribute6 VARCHAR2,
355 X_Attribute7 VARCHAR2,
356 X_Attribute8 VARCHAR2,
357 X_Attribute9 VARCHAR2,
358 X_Attribute10 VARCHAR2,
359 X_Attribute11 VARCHAR2,
360 X_Attribute12 VARCHAR2,
361 X_Attribute13 VARCHAR2,
362 X_Attribute14 VARCHAR2,
363 X_Attribute15 VARCHAR2,
364 X_Attribute16 VARCHAR2,
365 X_Attribute17 VARCHAR2,
366 X_Attribute18 VARCHAR2,
367 X_Attribute19 VARCHAR2,
368 X_Attribute20 VARCHAR2
369 ) IS
370 BEGIN
371 --
372 check_unique_row(X_Assignment_Id
373 ,X_Assignment_Status_Type_Id
374 ,X_Start_Date
375 ,X_Rowid);
376 --
377 validate_date(X_Assignment_Id
378 ,X_Start_Date
379 ,'S');
380 --
381 if X_End_Date is not null then
382 validate_date(X_Assignment_Id
383 ,X_End_Date
384 ,'E');
385 --
386 validate_start_end_date(X_Start_Date
387 ,X_End_Date);
388 end if;
389 --
390 X_End_Date := set_end_date(X_Assignment_Id,X_End_Date);
391 --
392 UPDATE PER_SECONDARY_ASS_STATUSES
393 SET
394 secondary_ass_status_id = X_Secondary_Ass_Status_Id,
395 business_group_id = X_Business_Group_Id,
396 assignment_id = X_Assignment_Id,
397 assignment_status_type_id = X_Assignment_Status_Type_Id,
398 start_date = X_Start_Date,
399 comments = X_Comments,
400 end_date = X_End_Date,
401 reason = X_Reason,
402 attribute_category = X_Attribute_Category,
403 attribute1 = X_Attribute1,
404 attribute2 = X_Attribute2,
405 attribute3 = X_Attribute3,
406 attribute4 = X_Attribute4,
407 attribute5 = X_Attribute5,
408 attribute6 = X_Attribute6,
409 attribute7 = X_Attribute7,
410 attribute8 = X_Attribute8,
411 attribute9 = X_Attribute9,
412 attribute10 = X_Attribute10,
413 attribute11 = X_Attribute11,
414 attribute12 = X_Attribute12,
415 attribute13 = X_Attribute13,
416 attribute14 = X_Attribute14,
417 attribute15 = X_Attribute15,
418 attribute16 = X_Attribute16,
419 attribute17 = X_Attribute17,
420 attribute18 = X_Attribute18,
421 attribute19 = X_Attribute19,
422 attribute20 = X_Attribute20
423 WHERE rowid = X_rowid;
424
425 if (SQL%NOTFOUND) then
426 RAISE NO_DATA_FOUND;
427 end if;
428
429 END Update_Row;
430
431 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
432 BEGIN
433 DELETE FROM PER_SECONDARY_ASS_STATUSES
434 WHERE rowid = X_Rowid;
435
436 if (SQL%NOTFOUND) then
437 RAISE NO_DATA_FOUND;
438 end if;
439 END Delete_Row;
440 --
441 --
442 procedure check_unique_row(p_assignment_id number
443 ,p_assignment_status_type_id number
444 ,p_start_date date
445 ,p_rowid varchar2) is
446 cursor c is
447 select 'x'
448 from per_secondary_ass_statuses
449 where assignment_id = p_assignment_id
450 and assignment_status_type_id = p_assignment_status_type_id
451 and start_date = p_start_date
452 AND ((p_rowid IS NULL)
453 OR (p_rowid is not null and
454 rowid <> chartorowid(p_rowid))
455 );
456 l_exists varchar2(1);
457 begin
458 open c;
459 fetch c into l_exists;
460 if c%found then
461 close c;
462 hr_utility.set_message(801,'HR_6436_EMP_STATUS_STAT_EXIST');
463 hr_utility.raise_error;
464 end if;
465 close c;
466 end check_unique_row;
467 --
468 --
469 procedure validate_date(p_assignment_id number
470 ,p_date date
471 ,p_date_type varchar2) is
472 cursor c is
473 select 'x'
474 from per_assignments_f x
475 where x.assignment_id = p_assignment_id
476 and p_date >=
477 (select min(y.effective_start_date)
478 from per_assignments_f y
479 where y.assignment_id = x.assignment_id)
480 and p_date <=
481 (select max(y.effective_end_date)
482 from per_assignments_f y
483 where y.assignment_id = x.assignment_id);
484 --
485 l_exists varchar2(1);
486 begin
487 open c;
488 fetch c into l_exists;
489 if c%notfound then
490 close c;
491 if p_date_type = 'S' then
492 hr_utility.set_message(801,'HR_6464_EMP_STATUS_ASS_DATE');
493 else
494 hr_utility.set_message(801,'HR_6851_EMP_STATUS_END_DATE');
495 end if;
496 hr_utility.raise_error;
497 end if;
498 close c;
499 end validate_date;
500 --
501 --
502 procedure validate_start_end_date(p_start_date date
503 ,p_end_date date) is
504 begin
505 if p_start_date > p_end_date then
506 hr_utility.set_message(801,'HR_6021_ALL_START_END_DATE');
507 hr_utility.raise_error;
508 end if;
509 end validate_start_end_date;
510 --
511 --
512 function set_end_date(p_assignment_id number
513 ,p_end_date date) return date is
514 cursor c is
515 select max(effective_end_date)
516 from per_assignments_f
517 where assignment_id = p_assignment_id;
518 --
519 l_new_end_date date;
520 begin
521 if p_end_date is not null then
522 l_new_end_date := p_end_date;
523 else
524 open c;
525 fetch c into l_new_end_date;
526 close c;
527 end if;
528 --
529 if l_new_end_date <> hr_general.end_of_time then
530 return(l_new_end_date);
531 else
532 return(null);
533 end if;
534 end set_end_date;
535 --
536 --
537 END PER_SECONDARY_ASS_STATUSES_PKG;