[Home] [Help]
PACKAGE BODY: APPS.PER_BOOKINGS_PKG
Source
1 PACKAGE BODY PER_BOOKINGS_PKG as
2 /* $Header: pebkg01t.pkb 120.2 2008/01/02 08:06:23 uuddavol ship $ */
3 /*
4
5 25-JUL-95 AForte 70.3 Changed tokenised message
6 HR_7149_BOOKINGS_FLAG_CHANGE
7 to tokenised messages
8 HR_7676_BOOKING_FLAG_CHANGE
9 HR_7677_BOOKING_FLAG_CHANGE
10 HR_7678_BOOKING_FLAG_CHANGE
11 17-OCT-00 DCasemor 115.2 Bug 1432014 Removed hard-coded date
12 format and used the
13 canonical function to convert
14 p_new_date parameter.
15 26-OCT-07 uuddavol 115.3 Interview Management changes
16 02-JAN-08 uuddavol 120.2 set default values to
17 X_Primary_Interviewer_Flag
18 */
19
20 -- **************************************************************************
21 -- *** THIS PACKAGE IS USED BY THREE FORMS - PERWSERW, PERWSGEB, PERWSBEP ***
22 -- **************************************************************************
23
24 -- This procedure is used only by PERWSBEP to perfrom extra validation
25 -- when the session date is changed
26 PROCEDURE Validate_Person(P_Person_id VARCHAR2,
27 P_Current_Flag VARCHAR2,
28 P_New_Date VARCHAR2) is
29
30
31 D_DUMMY NUMBER(1);
32 l_New_Date date;
33
34 CURSOR C1 IS
35 SELECT 1
36 FROM PER_PEOPLE_F PPF
37 WHERE PPF.PERSON_ID = to_number(P_Person_id)
38 AND PPF.CURRENT_EMPLOYEE_FLAG = 'Y'
39 AND l_New_Date between PPF.EFFECTIVE_START_DATE AND
40 PPF.EFFECTIVE_END_DATE;
41
42 CURSOR C2 IS
43 SELECT 1
44 FROM PER_PEOPLE_F PPF
45 WHERE PPF.PERSON_ID = to_number(P_Person_id)
46 AND PPF.CURRENT_APPLICANT_FLAG = 'Y'
47 AND l_New_Date between PPF.EFFECTIVE_START_DATE AND
48 PPF.EFFECTIVE_END_DATE;
49
50 CURSOR C3 IS
51 SELECT 1
52 FROM PER_PEOPLE_F PPF
53 WHERE PPF.PERSON_ID = to_number(P_Person_id)
54 AND PPF.CURRENT_APPLICANT_FLAG = 'Y'
55 AND PPF.CURRENT_EMPLOYEE_FLAG = 'Y'
56 AND l_New_Date between PPF.EFFECTIVE_START_DATE AND
57 PPF.EFFECTIVE_END_DATE;
58
59 BEGIN
60
61 l_New_Date := fnd_date.canonical_to_date(P_New_Date);
62
63 IF P_Current_Flag = 'E' THEN
64 OPEN C1;
65 FETCH C1 INTO D_DUMMY;
66 IF C1%NOTFOUND THEN
67 CLOSE C1;
68 HR_UTILITY.SET_MESSAGE('801','HR_7676_BOOKING_FLAG_CHANGE');
69 HR_UTILITY.RAISE_ERROR;
70 END IF;
71 CLOSE C1;
72 END IF;
73
74 IF P_Current_Flag = 'A' THEN
75 OPEN C2;
76 FETCH C2 INTO D_DUMMY;
77 IF C2%NOTFOUND THEN
78 CLOSE C2;
79 HR_UTILITY.SET_MESSAGE('801','HR_7677_BOOKING_FLAG_CHANGE');
80 HR_UTILITY.RAISE_ERROR;
81 END IF;
82 CLOSE C2;
83 END IF;
84
85 IF P_Current_Flag = 'B' THEN
86 OPEN C3;
87 FETCH C3 INTO D_DUMMY;
88 IF C3%NOTFOUND THEN
89 CLOSE C3;
90 HR_UTILITY.SET_MESSAGE('801','HR_7678_BOOKING_FLAG_CHANGE');
91 HR_UTILITY.RAISE_ERROR;
92 END IF;
93 CLOSE C3;
94 END IF;
95
96 END Validate_Person;
97
98
99 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
100 X_Booking_Id IN OUT NOCOPY NUMBER,
101 X_Business_Group_Id NUMBER,
102 X_Person_Id NUMBER,
103 X_Event_Id NUMBER,
104 X_Message VARCHAR2,
105 X_Token VARCHAR2,
106 X_Comments VARCHAR2,
107 X_Attribute_Category VARCHAR2,
108 X_Attribute1 VARCHAR2,
109 X_Attribute2 VARCHAR2,
110 X_Attribute3 VARCHAR2,
111 X_Attribute4 VARCHAR2,
112 X_Attribute5 VARCHAR2,
113 X_Attribute6 VARCHAR2,
114 X_Attribute7 VARCHAR2,
115 X_Attribute8 VARCHAR2,
116 X_Attribute9 VARCHAR2,
117 X_Attribute10 VARCHAR2,
118 X_Attribute11 VARCHAR2,
119 X_Attribute12 VARCHAR2,
120 X_Attribute13 VARCHAR2,
121 X_Attribute14 VARCHAR2,
122 X_Attribute15 VARCHAR2,
123 X_Attribute16 VARCHAR2,
124 X_Attribute17 VARCHAR2,
125 X_Attribute18 VARCHAR2,
126 X_Attribute19 VARCHAR2,
127 X_Attribute20 VARCHAR2,
128 X_Primary_Interviewer_Flag VARCHAR2 default null
129 ) IS
130
131 L_DUMMY NUMBER;
132
133 CURSOR C IS
134 SELECT rowid FROM PER_BOOKINGS
135 WHERE booking_id = X_Booking_Id;
136
137 CURSOR C2 IS
138 SELECT PER_BOOKINGS_S.NEXTVAL
139 FROM SYS.DUAL;
140
141 CURSOR UNIQUE_CHECK IS
142 SELECT 1
143 FROM PER_BOOKINGS PB
144 WHERE (PB.ROWID <> X_Rowid OR X_Rowid IS NULL)
145 AND PB.PERSON_ID = X_PERSON_ID
146 AND PB.BUSINESS_GROUP_ID + 0 = X_BUSINESS_GROUP_ID
147 AND PB.EVENT_ID = X_EVENT_ID;
148
149 BEGIN
150 OPEN UNIQUE_CHECK;
151 FETCH UNIQUE_CHECK INTO L_DUMMY;
152 IF UNIQUE_CHECK%FOUND THEN
153 CLOSE UNIQUE_CHECK;
154 -- Check to see if X_Token is an EMPLOYEE or an APPLICANT
155 -- and then an error message is raised for that person type.
156 if X_Token = 'EMPLOYEE' then
157 FND_MESSAGE.SET_NAME('PER', 'PER_51973_EMP_EVENT_ONCE');
158 elsif X_Token = 'APPLICANT' then
159 FND_MESSAGE.SET_NAME('PER', 'PER_51974_APP_EVENT_ONCE');
160 end if;
161 FND_MESSAGE.RAISE_ERROR;
162 ELSE
163 CLOSE UNIQUE_CHECK;
164 END IF;
165
166 OPEN C2;
167 FETCH C2 INTO X_Booking_Id;
168 CLOSE C2;
169
170 INSERT INTO PER_BOOKINGS(
171 booking_id,
172 business_group_id,
173 person_id,
174 event_id,
175 comments,
176 attribute_category,
177 attribute1,
178 attribute2,
179 attribute3,
180 attribute4,
181 attribute5,
182 attribute6,
183 attribute7,
184 attribute8,
185 attribute9,
186 attribute10,
187 attribute11,
188 attribute12,
189 attribute13,
190 attribute14,
191 attribute15,
192 attribute16,
193 attribute17,
194 attribute18,
195 attribute19,
196 attribute20,
197 primary_interviewer_flag
198 ) VALUES (
199 X_Booking_Id,
200 X_Business_Group_Id,
201 X_Person_Id,
202 X_Event_Id,
203 X_Comments,
204 X_Attribute_Category,
205 X_Attribute1,
206 X_Attribute2,
207 X_Attribute3,
208 X_Attribute4,
209 X_Attribute5,
210 X_Attribute6,
211 X_Attribute7,
212 X_Attribute8,
213 X_Attribute9,
214 X_Attribute10,
215 X_Attribute11,
216 X_Attribute12,
217 X_Attribute13,
218 X_Attribute14,
219 X_Attribute15,
220 X_Attribute16,
221 X_Attribute17,
222 X_Attribute18,
223 X_Attribute19,
224 X_Attribute20,
225 X_Primary_Interviewer_Flag
226 );
227
228 OPEN C;
229 FETCH C INTO X_Rowid;
230 if (C%NOTFOUND) then
231 CLOSE C;
232 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
233 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','INSERT_ROW');
234 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
235 HR_UTILITY.RAISE_ERROR;
236 end if;
237 CLOSE C;
238 END Insert_Row;
239
240 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
241 X_Booking_Id NUMBER,
242 X_Business_Group_Id NUMBER,
243 X_Person_Id NUMBER,
244 X_Event_Id NUMBER,
245 X_Comments VARCHAR2,
246 X_Attribute_Category VARCHAR2,
247 X_Attribute1 VARCHAR2,
248 X_Attribute2 VARCHAR2,
249 X_Attribute3 VARCHAR2,
250 X_Attribute4 VARCHAR2,
251 X_Attribute5 VARCHAR2,
252 X_Attribute6 VARCHAR2,
253 X_Attribute7 VARCHAR2,
254 X_Attribute8 VARCHAR2,
255 X_Attribute9 VARCHAR2,
256 X_Attribute10 VARCHAR2,
257 X_Attribute11 VARCHAR2,
258 X_Attribute12 VARCHAR2,
259 X_Attribute13 VARCHAR2,
260 X_Attribute14 VARCHAR2,
264 X_Attribute18 VARCHAR2,
261 X_Attribute15 VARCHAR2,
262 X_Attribute16 VARCHAR2,
263 X_Attribute17 VARCHAR2,
265 X_Attribute19 VARCHAR2,
266 X_Attribute20 VARCHAR2,
267 X_Primary_Interviewer_Flag VARCHAR2 default null
268 ) IS
269
270 CURSOR C IS
271 SELECT *
272 FROM PER_BOOKINGS
273 WHERE rowid = X_Rowid
274 FOR UPDATE of Booking_Id NOWAIT;
275 Recinfo C%ROWTYPE;
276 BEGIN
277 OPEN C;
278 FETCH C INTO Recinfo;
279 if (C%NOTFOUND) then
280 CLOSE C;
281 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
282 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','LOCK_ROW');
283 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
284 HR_UTILITY.RAISE_ERROR;
285 end if;
286 CLOSE C;
287
288 Recinfo.comments := rtrim(Recinfo.comments);
289 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
290 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
291 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
292 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
293 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
294 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
295 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
296 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
297 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
298 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
299 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
300 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
301 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
302 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
303 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
304 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
305 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
306 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
307 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
308 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
309 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
310
311 if (
312 ( (Recinfo.booking_id = X_Booking_Id)
313 OR ( (Recinfo.booking_id IS NULL)
314 AND (X_Booking_Id IS NULL)))
315 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
316 OR ( (Recinfo.business_group_id IS NULL)
317 AND (X_Business_Group_Id IS NULL)))
318 AND ( (Recinfo.person_id = X_Person_Id)
319 OR ( (Recinfo.person_id IS NULL)
320 AND (X_Person_Id IS NULL)))
321 AND ( (Recinfo.event_id = X_Event_Id)
322 OR ( (Recinfo.event_id IS NULL)
323 AND (X_Event_Id IS NULL)))
324 AND ( (Recinfo.comments = X_Comments)
325 OR ( (Recinfo.comments IS NULL)
326 AND (X_Comments IS NULL)))
327 AND ( (Recinfo.attribute_category = X_Attribute_Category)
328 OR ( (Recinfo.attribute_category IS NULL)
329 AND (X_Attribute_Category IS NULL)))
330 AND ( (Recinfo.attribute1 = X_Attribute1)
331 OR ( (Recinfo.attribute1 IS NULL)
332 AND (X_Attribute1 IS NULL)))
333 AND ( (Recinfo.attribute2 = X_Attribute2)
334 OR ( (Recinfo.attribute2 IS NULL)
335 AND (X_Attribute2 IS NULL)))
336 AND ( (Recinfo.attribute3 = X_Attribute3)
337 OR ( (Recinfo.attribute3 IS NULL)
338 AND (X_Attribute3 IS NULL)))
339 AND ( (Recinfo.attribute4 = X_Attribute4)
340 OR ( (Recinfo.attribute4 IS NULL)
341 AND (X_Attribute4 IS NULL)))
342 AND ( (Recinfo.attribute5 = X_Attribute5)
343 OR ( (Recinfo.attribute5 IS NULL)
344 AND (X_Attribute5 IS NULL)))
345 AND ( (Recinfo.attribute6 = X_Attribute6)
346 OR ( (Recinfo.attribute6 IS NULL)
347 AND (X_Attribute6 IS NULL)))
348 AND ( (Recinfo.attribute7 = X_Attribute7)
349 OR ( (Recinfo.attribute7 IS NULL)
350 AND (X_Attribute7 IS NULL)))
351 AND ( (Recinfo.attribute8 = X_Attribute8)
352 OR ( (Recinfo.attribute8 IS NULL)
353 AND (X_Attribute8 IS NULL)))
354 AND ( (Recinfo.attribute9 = X_Attribute9)
355 OR ( (Recinfo.attribute9 IS NULL)
356 AND (X_Attribute9 IS NULL)))
357 AND ( (Recinfo.attribute10 = X_Attribute10)
358 OR ( (Recinfo.attribute10 IS NULL)
359 AND (X_Attribute10 IS NULL)))
360 AND ( (Recinfo.attribute11 = X_Attribute11)
361 OR ( (Recinfo.attribute11 IS NULL)
362 AND (X_Attribute11 IS NULL)))
363 AND ( (Recinfo.attribute12 = X_Attribute12)
364 OR ( (Recinfo.attribute12 IS NULL)
365 AND (X_Attribute12 IS NULL)))
366 AND ( (Recinfo.attribute13 = X_Attribute13)
367 OR ( (Recinfo.attribute13 IS NULL)
368 AND (X_Attribute13 IS NULL)))
369 AND ( (Recinfo.attribute14 = X_Attribute14)
370 OR ( (Recinfo.attribute14 IS NULL)
371 AND (X_Attribute14 IS NULL)))
372 AND ( (Recinfo.attribute15 = X_Attribute15)
373 OR ( (Recinfo.attribute15 IS NULL)
374 AND (X_Attribute15 IS NULL)))
375 AND ( (Recinfo.attribute16 = X_Attribute16)
376 OR ( (Recinfo.attribute16 IS NULL)
377 AND (X_Attribute16 IS NULL)))
378 AND ( (Recinfo.attribute17 = X_Attribute17)
379 OR ( (Recinfo.attribute17 IS NULL)
380 AND (X_Attribute17 IS NULL)))
381 AND ( (Recinfo.attribute18 = X_Attribute18)
382 OR ( (Recinfo.attribute18 IS NULL)
383 AND (X_Attribute18 IS NULL)))
384 AND ( (Recinfo.attribute19 = X_Attribute19)
385 OR ( (Recinfo.attribute19 IS NULL)
386 AND (X_Attribute19 IS NULL)))
387 AND ( (Recinfo.attribute20 = X_Attribute20)
388 OR ( (Recinfo.attribute20 IS NULL)
389 AND (X_Attribute20 IS NULL)))
390 AND ( (Recinfo.primary_interviewer_flag = X_Primary_Interviewer_Flag)
391 OR ( (Recinfo.primary_interviewer_flag IS NULL)
392 AND (X_Primary_Interviewer_Flag IS NULL)))
393 ) then
394 return;
395 else
396 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
397 APP_EXCEPTION.RAISE_EXCEPTION;
398 end if;
399 END Lock_Row;
400
401 PROCEDURE Update_Row(X_Rowid VARCHAR2,
402 X_Booking_Id NUMBER,
403 X_Business_Group_Id NUMBER,
404 X_Person_Id NUMBER,
405 X_Event_Id NUMBER,
406 X_Message VARCHAR2,
407 X_Token VARCHAR2,
408 X_Comments VARCHAR2,
409 X_Attribute_Category VARCHAR2,
410 X_Attribute1 VARCHAR2,
411 X_Attribute2 VARCHAR2,
412 X_Attribute3 VARCHAR2,
413 X_Attribute4 VARCHAR2,
414 X_Attribute5 VARCHAR2,
415 X_Attribute6 VARCHAR2,
416 X_Attribute7 VARCHAR2,
417 X_Attribute8 VARCHAR2,
418 X_Attribute9 VARCHAR2,
419 X_Attribute10 VARCHAR2,
420 X_Attribute11 VARCHAR2,
421 X_Attribute12 VARCHAR2,
422 X_Attribute13 VARCHAR2,
423 X_Attribute14 VARCHAR2,
424 X_Attribute15 VARCHAR2,
425 X_Attribute16 VARCHAR2,
426 X_Attribute17 VARCHAR2,
427 X_Attribute18 VARCHAR2,
428 X_Attribute19 VARCHAR2,
429 X_Attribute20 VARCHAR2,
430 X_Primary_Interviewer_Flag VARCHAR2 default null
431 ) IS
432
433 L_DUMMY NUMBER;
434
435 CURSOR UNIQUE_CHECK IS
436 SELECT 1
437 FROM PER_BOOKINGS PB
438 WHERE (PB.ROWID <> X_Rowid OR X_Rowid IS NULL)
439 AND PB.PERSON_ID = X_PERSON_ID
440 AND PB.BUSINESS_GROUP_ID + 0 = X_BUSINESS_GROUP_ID
441 AND PB.EVENT_ID = X_EVENT_ID;
442
443
444 BEGIN
445 OPEN UNIQUE_CHECK;
446 FETCH UNIQUE_CHECK INTO L_DUMMY;
447 IF UNIQUE_CHECK%FOUND THEN
448 CLOSE UNIQUE_CHECK;
449 -- Check to see if X_Token is an EMPLOYEE or an APPLICANT
450 -- and then an error message is raised for that person type.
451 if X_Token = 'EMPLOYEE' then
452 HR_UTILITY.SET_MESSAGE('801', 'PER_51973_EMP_EVENT_ONCE');
453 elsif X_Token = 'APPLICANT' then
454 HR_UTILITY.SET_MESSAGE('801', 'PER_51974_APP_EVENT_ONCE');
455 end if;
456 HR_UTILITY.RAISE_ERROR;
457 ELSE
458 CLOSE UNIQUE_CHECK;
459 END IF;
460
461 UPDATE PER_BOOKINGS
462 SET
463
464 booking_id = X_Booking_Id,
465 business_group_id = X_Business_Group_Id,
466 person_id = X_Person_Id,
467 event_id = X_Event_Id,
468 comments = X_Comments,
469 attribute_category = X_Attribute_Category,
470 attribute1 = X_Attribute1,
471 attribute2 = X_Attribute2,
472 attribute3 = X_Attribute3,
473 attribute4 = X_Attribute4,
474 attribute5 = X_Attribute5,
475 attribute6 = X_Attribute6,
476 attribute7 = X_Attribute7,
477 attribute8 = X_Attribute8,
478 attribute9 = X_Attribute9,
479 attribute10 = X_Attribute10,
480 attribute11 = X_Attribute11,
481 attribute12 = X_Attribute12,
482 attribute13 = X_Attribute13,
483 attribute14 = X_Attribute14,
484 attribute15 = X_Attribute15,
485 attribute16 = X_Attribute16,
486 attribute17 = X_Attribute17,
487 attribute18 = X_Attribute18,
488 attribute19 = X_Attribute19,
489 attribute20 = X_Attribute20,
490 primary_interviewer_flag = X_Primary_Interviewer_Flag
491 WHERE rowid = X_rowid;
492
493 if (SQL%NOTFOUND) then
494 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
495 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','UPDATE_ROW');
496 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
497 HR_UTILITY.RAISE_ERROR;
498 end if;
499
500 END Update_Row;
501
502 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
503 BEGIN
504 DELETE FROM PER_BOOKINGS
505 WHERE rowid = X_Rowid;
506
507 if (SQL%NOTFOUND) then
508 HR_UTILITY.SET_MESSAGE(801,'HR_6153_ALL_PROCEDURE_FAIL');
509 HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','DELETE_ROW');
510 HR_UTILITY.SET_MESSAGE_TOKEN('STEP','1');
511 HR_UTILITY.RAISE_ERROR;
512 end if;
513 END Delete_Row;
514
515 END PER_BOOKINGS_PKG;