[Home] [Help]
PACKAGE BODY: APPS.JTF_CAL_RESOURCE_ASSIGN_PKG
Source
1 PACKAGE BODY Jtf_Cal_Resource_Assign_Pkg AS
2 /* $Header: jtfclrab.pls 120.5 2011/03/22 06:31:04 anangupt ship $ */
3 PROCEDURE INSERT_ROW (
4 X_ERROR OUT NOCOPY VARCHAR2,
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_CAL_RESOURCE_ASSIGN_ID IN OUT NOCOPY NUMBER,
7 X_OBJECT_VERSION_NUMBER IN NUMBER,
8 X_ATTRIBUTE5 IN VARCHAR2,
9 X_ATTRIBUTE6 IN VARCHAR2,
10 X_ATTRIBUTE7 IN VARCHAR2,
11 X_ATTRIBUTE8 IN VARCHAR2,
12 X_ATTRIBUTE9 IN VARCHAR2,
13 X_ATTRIBUTE10 IN VARCHAR2,
14 X_ATTRIBUTE11 IN VARCHAR2,
15 X_ATTRIBUTE12 IN VARCHAR2,
16 X_ATTRIBUTE13 IN VARCHAR2,
17 X_ATTRIBUTE14 IN VARCHAR2,
18 X_ATTRIBUTE15 IN VARCHAR2,
19 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
20 X_START_DATE_TIME IN DATE,
21 X_END_DATE_TIME IN DATE,
22 X_CALENDAR_ID IN NUMBER,
23 X_RESOURCE_ID IN NUMBER,
24 X_RESOURCE_TYPE_CODE IN VARCHAR2,
25 X_PRIMARY_CALENDAR_FLAG IN VARCHAR2,
26 X_ATTRIBUTE1 IN VARCHAR2,
27 X_ATTRIBUTE2 IN VARCHAR2,
28 X_ATTRIBUTE3 IN VARCHAR2,
29 X_ATTRIBUTE4 IN VARCHAR2,
30 X_CREATION_DATE IN DATE,
31 X_CREATED_BY IN NUMBER,
32 X_LAST_UPDATE_DATE IN DATE,
33 X_LAST_UPDATED_BY IN NUMBER,
34 X_LAST_UPDATE_LOGIN IN NUMBER
35 ) IS
36
37 v_flag CHAR := 'F';
38 v_count NUMBER := 0;
39 v_dup NUMBER := 0;
40 v_calendar_id NUMBER := 0;
41 v_resource_name VARCHAR2(100);
42 v_calendar_name VARCHAR2(100);
43 prime_flag_count NUMBER := 0;
44
45 -- Added by jawang on 06/05/2002 to fix bug 2180182
46 l_temp_fnd_end_date date := to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR');
47
48 CURSOR C IS SELECT ROWID FROM JTF_CAL_RESOURCE_ASSIGN
49 WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID;
50
51 CURSOR C_RES_NAME IS SELECT RESOURCE_NAME
52 FROM JTF_TASK_RESOURCES_VL
53 WHERE RESOURCE_ID = X_RESOURCE_ID
54 AND RESOURCE_TYPE = X_RESOURCE_TYPE_CODE;
55
56 CURSOR C_CAL_NAME(p_calendar_id NUMBER) IS SELECT CALENDAR_NAME
57 FROM JTF_CALENDARS_TL
58 WHERE CALENDAR_ID = p_calendar_id
59 AND LANGUAGE=USERENV('LANG');
60
61 CURSOR C_CAL_ID(p_primary_calendar_flag VARCHAR2) IS SELECT CALENDAR_ID
62 FROM JTF_CAL_RESOURCE_ASSIGN
63 WHERE resource_id = X_RESOURCE_ID
64 AND resource_type_code = X_RESOURCE_TYPE_CODE
65 AND primary_calendar_flag = p_primary_calendar_flag
66 AND (( X_START_DATE_TIME <= start_date_time AND NVL(X_END_DATE_TIME,l_temp_fnd_end_date)
67 >= NVL(end_date_time,l_temp_fnd_end_date) )
68 OR ( X_START_DATE_TIME BETWEEN start_date_time AND NVL(end_date_time,l_temp_fnd_end_date))
69 OR ( NVL(X_END_DATE_TIME,Fnd_Api.g_miss_date) BETWEEN start_date_time AND
70 NVL(end_date_time,l_temp_fnd_end_date))
71 OR ((X_START_DATE_TIME < start_date_time) AND (NVL(X_END_DATE_TIME,l_temp_fnd_end_date)
72 > NVL(end_date_time,l_temp_fnd_end_date)))
73 OR ((X_START_DATE_TIME > start_date_time) AND (NVL(X_END_DATE_TIME,l_temp_fnd_end_date) <
74 NVL(end_date_time,l_temp_fnd_end_date))));
75
76
77 v_error CHAR := 'N';
78 v_shift_id NUMBER;
79 v_cal_resource_assign_id NUMBER;
80 BEGIN
81 Fnd_Msg_Pub.initialize;
82 IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(TO_CHAR(X_RESOURCE_ID)) = FALSE THEN
83 Fnd_Message.set_name('JTF', 'JTF_CAL_REQUIRED');
84 Fnd_Message.set_token('P_NAME', X_RESOURCE_ID);
85 Fnd_Msg_Pub.ADD;
86 v_error := 'Y';
87 END IF;
88
89 IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(X_START_DATE_TIME) = FALSE THEN
90 Fnd_Message.set_name('JTF', 'JTF_CAL_START_DATE');
91 Fnd_Msg_Pub.ADD;
92
93 v_error := 'Y';
94 END IF;
95
96 IF Jtf_Cal_Resource_Assign_Pkg.END_GREATER_THAN_BEGIN(X_START_DATE_TIME, X_END_DATE_TIME) = FALSE THEN
97 --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
98 --app_exception.raise_exception;
99 Fnd_Message.set_name('JTF', 'JTF_CAL_END_DATE');
100 Fnd_Message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>X_START_DATE_TIME,calendar_aware=>fnd_date.calendar_aware));
101 Fnd_Message.set_token('P_End_Date', fnd_date.date_to_chardate(dateval=>X_END_DATE_TIME,calendar_aware=>fnd_date.calendar_aware));
102 Fnd_Msg_Pub.ADD;
103 v_error := 'Y';
104 END IF;
105
106 OPEN C_CAL_ID('Y');
107 FETCH C_CAL_ID INTO v_calendar_id;
108 prime_flag_count := C_CAL_ID%ROWCOUNT;
109 CLOSE C_CAL_ID;
110
111 IF prime_flag_count >= 1 AND X_PRIMARY_CALENDAR_FLAG = 'Y' THEN
112 --get resource name
113 OPEN C_RES_NAME;
114 FETCH C_RES_NAME INTO v_resource_name;
115 CLOSE C_RES_NAME;
116 --get calendar name
117 OPEN C_CAL_NAME(v_calendar_id);
118 FETCH C_CAL_NAME INTO v_calendar_name;
119 CLOSE C_CAL_NAME;
120 Fnd_Message.set_name('JTF', 'JTF_CAL_DUP_PRIMARY_CAL_FLAG');
121 Fnd_Message.set_token('RESOURCE_NAME',v_resource_name);
122 Fnd_Message.set_token('CALENDAR_NAME',v_calendar_name);
123 Fnd_Msg_Pub.ADD;
124 v_error := 'Y';
125 END IF;
126
127 OPEN C_CAL_ID('N');
128 FETCH C_CAL_ID INTO v_calendar_id;
129 v_count := C_CAL_ID%ROWCOUNT;
130 CLOSE C_CAL_ID;
131
132 IF v_count >= 1 AND X_PRIMARY_CALENDAR_FLAG = 'N' THEN
133 Fnd_Message.set_name('JTF', 'JTF_CAL_DUPLICATE_ROW');
134 Fnd_Msg_Pub.ADD;
135 v_error := 'Y';
136 END IF;
137
138
139 IF v_error = 'Y' THEN
140 X_ERROR := 'Y';
141 RETURN;
142 ELSE
143 SELECT jtf_cal_resource_assign_s.NEXTVAL
144 INTO v_cal_resource_assign_id
145 FROM dual;
146
147 X_CAL_RESOURCE_ASSIGN_ID := v_cal_resource_assign_id;
148
149 INSERT INTO JTF_CAL_RESOURCE_ASSIGN (
150 OBJECT_VERSION_NUMBER,
151 ATTRIBUTE5,
152 ATTRIBUTE6,
153 ATTRIBUTE7,
154 ATTRIBUTE8,
155 ATTRIBUTE9,
156 ATTRIBUTE10,
157 ATTRIBUTE11,
158 ATTRIBUTE12,
159 ATTRIBUTE13,
160 ATTRIBUTE14,
161 ATTRIBUTE15,
162 ATTRIBUTE_CATEGORY,
163 CAL_RESOURCE_ASSIGN_ID,
164 START_DATE_TIME,
165 END_DATE_TIME,
166 CALENDAR_ID,
167 RESOURCE_ID,
168 RESOURCE_TYPE_CODE,
169 PRIMARY_CALENDAR_FLAG,
170 CREATED_BY,
171 CREATION_DATE,
172 LAST_UPDATED_BY,
173 LAST_UPDATE_DATE,
174 LAST_UPDATE_LOGIN,
175 ATTRIBUTE1,
176 ATTRIBUTE2,
177 ATTRIBUTE3,
178 ATTRIBUTE4
179 ) VALUES
180 ( 1,
181 X_ATTRIBUTE5,
182 X_ATTRIBUTE6,
183 X_ATTRIBUTE7,
184 X_ATTRIBUTE8,
185 X_ATTRIBUTE9,
186 X_ATTRIBUTE10,
187 X_ATTRIBUTE11,
188 X_ATTRIBUTE12,
189 X_ATTRIBUTE13,
190 X_ATTRIBUTE14,
191 X_ATTRIBUTE15,
192 X_ATTRIBUTE_CATEGORY,
193 v_CAL_RESOURCE_ASSIGN_ID,
194 X_START_DATE_TIME,
195 X_END_DATE_TIME,
196 X_CALENDAR_ID,
197 X_RESOURCE_ID,
198 X_RESOURCE_TYPE_CODE,
199 X_PRIMARY_CALENDAR_FLAG,
200 Fnd_Global.USER_ID,
201 SYSDATE,
202 Fnd_Global.USER_ID,
203 SYSDATE,
204 NULL,
205 X_ATTRIBUTE1,
206 X_ATTRIBUTE2,
207 X_ATTRIBUTE3,
208 X_ATTRIBUTE4);
209
210 END IF;
211 /*
212 open c;
213 fetch c into X_ROWID;
214 if (c%notfound) then
215 close c;
216 raise no_data_found;
217 end if;
218 close c;
219 */
220 END INSERT_ROW;
221
222 PROCEDURE LOCK_ROW (
223 X_CAL_RESOURCE_ASSIGN_ID IN NUMBER,
224 X_OBJECT_VERSION_NUMBER IN NUMBER,
225 X_ATTRIBUTE5 IN VARCHAR2,
226 X_ATTRIBUTE6 IN VARCHAR2,
227 X_ATTRIBUTE7 IN VARCHAR2,
228 X_ATTRIBUTE8 IN VARCHAR2,
229 X_ATTRIBUTE9 IN VARCHAR2,
230 X_ATTRIBUTE10 IN VARCHAR2,
231 X_ATTRIBUTE11 IN VARCHAR2,
232 X_ATTRIBUTE12 IN VARCHAR2,
233 X_ATTRIBUTE13 IN VARCHAR2,
234 X_ATTRIBUTE14 IN VARCHAR2,
235 X_ATTRIBUTE15 IN VARCHAR2,
236 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
237 X_START_DATE_TIME IN DATE,
238 X_END_DATE_TIME IN DATE,
239 X_CALENDAR_ID IN NUMBER,
240 X_RESOURCE_ID IN NUMBER,
241 X_PRIMARY_CALENDAR_FLAG IN VARCHAR2,
242 X_ATTRIBUTE1 IN VARCHAR2,
243 X_ATTRIBUTE2 IN VARCHAR2,
244 X_ATTRIBUTE3 IN VARCHAR2,
245 X_ATTRIBUTE4 IN VARCHAR2
246 ) IS
247 CURSOR c1 IS SELECT
248 OBJECT_VERSION_NUMBER,
249 ATTRIBUTE5,
250 ATTRIBUTE6,
251 ATTRIBUTE7,
252 ATTRIBUTE8,
253 ATTRIBUTE9,
254 ATTRIBUTE10,
255 ATTRIBUTE11,
256 ATTRIBUTE12,
257 ATTRIBUTE13,
258 ATTRIBUTE14,
259 ATTRIBUTE15,
260 ATTRIBUTE_CATEGORY,
261 START_DATE_TIME,
262 END_DATE_TIME,
263 CALENDAR_ID,
264 RESOURCE_ID,
265 PRIMARY_CALENDAR_FLAG,
266 ATTRIBUTE1,
267 ATTRIBUTE2,
268 ATTRIBUTE3,
269 ATTRIBUTE4,
270 CAL_RESOURCE_ASSIGN_ID
271 FROM JTF_CAL_RESOURCE_ASSIGN
272 WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID
273 FOR UPDATE OF CAL_RESOURCE_ASSIGN_ID NOWAIT;
274 BEGIN
275 FOR tlinfo IN c1 LOOP
276 -- if (tlinfo.BASELANG = 'Y') then
277 IF ( (tlinfo.CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID)
278 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
279 OR ((tlinfo.OBJECT_VERSION_NUMBER IS NULL) AND (X_OBJECT_VERSION_NUMBER IS NULL)))
280 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
281 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL)))
282 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
283 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL)))
284 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
285 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL)))
286 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
287 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL)))
288 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
289 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL)))
290 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
291 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL)))
292 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
293 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL)))
294 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
295 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL)))
296 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
297 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL)))
298 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
299 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL)))
300 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
301 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL)))
302 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
303 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL)))
304 AND (tlinfo.START_DATE_TIME = X_START_DATE_TIME)
305 AND ((tlinfo.END_DATE_TIME = X_END_DATE_TIME)
306 OR ((tlinfo.END_DATE_TIME IS NULL) AND (X_END_DATE_TIME IS NULL)))
307 AND (tlinfo.CALENDAR_ID = X_CALENDAR_ID)
308 AND (tlinfo.RESOURCE_ID = X_RESOURCE_ID)
309 AND ((tlinfo.PRIMARY_CALENDAR_FLAG = X_PRIMARY_CALENDAR_FLAG)
310 OR ((tlinfo.PRIMARY_CALENDAR_FLAG IS NULL) AND (X_PRIMARY_CALENDAR_FLAG IS NULL)))
311 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
312 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL)))
313 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
314 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL)))
315 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
316 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL)))
317 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
318 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL)))
319 ) THEN
320 NULL;
321 ELSE
322 Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
323 App_Exception.raise_exception;
324 END IF;
325 -- end if;
326 END LOOP;
327 RETURN;
328 END LOCK_ROW;
329
330 PROCEDURE UPDATE_ROW (
331 X_ERROR OUT NOCOPY VARCHAR2,
332 X_CAL_RESOURCE_ASSIGN_ID IN NUMBER,
333 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
334 X_ATTRIBUTE5 IN VARCHAR2,
335 X_ATTRIBUTE6 IN VARCHAR2,
336 X_ATTRIBUTE7 IN VARCHAR2,
337 X_ATTRIBUTE8 IN VARCHAR2,
338 X_ATTRIBUTE9 IN VARCHAR2,
339 X_ATTRIBUTE10 IN VARCHAR2,
340 X_ATTRIBUTE11 IN VARCHAR2,
341 X_ATTRIBUTE12 IN VARCHAR2,
342 X_ATTRIBUTE13 IN VARCHAR2,
343 X_ATTRIBUTE14 IN VARCHAR2,
344 X_ATTRIBUTE15 IN VARCHAR2,
345 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
346 X_START_DATE_TIME IN DATE,
347 X_END_DATE_TIME IN DATE,
348 X_CALENDAR_ID IN NUMBER,
349 X_RESOURCE_ID IN NUMBER,
350 X_RESOURCE_TYPE_CODE IN VARCHAR2,
351 X_PRIMARY_CALENDAR_FLAG IN VARCHAR2,
352 X_ATTRIBUTE1 IN VARCHAR2,
353 X_ATTRIBUTE2 IN VARCHAR2,
354 X_ATTRIBUTE3 IN VARCHAR2,
355 X_ATTRIBUTE4 IN VARCHAR2,
356 X_LAST_UPDATE_DATE IN DATE,
357 X_LAST_UPDATED_BY IN NUMBER,
358 X_LAST_UPDATE_LOGIN IN NUMBER
359 ) IS
360
361 v_error CHAR := 'N';
362 v_shift_id NUMBER;
363 v_flag CHAR := 'F';
364 v_count NUMBER := 1;
365 v_dup NUMBER := 1;
366 v_calendar_id NUMBER := 0;
367 v_resource_name VARCHAR2(100);
368 v_calendar_name VARCHAR2(100);
369 prime_flag_count NUMBER := 0;
370 l_temp_fnd_end_date date := to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR');
371
372 CURSOR C_RES_NAME IS SELECT RESOURCE_NAME
373 FROM JTF_TASK_RESOURCES_VL
374 WHERE RESOURCE_ID = X_RESOURCE_ID
375 AND RESOURCE_TYPE = X_RESOURCE_TYPE_CODE;
376
377 CURSOR C_CAL_NAME(p_calendar_id NUMBER) IS SELECT CALENDAR_NAME
378 FROM JTF_CALENDARS_TL
379 WHERE CALENDAR_ID = p_calendar_id
380 AND LANGUAGE=USERENV('LANG');
381
382 CURSOR C_CAL_ID(p_primary_calendar_flag VARCHAR2) IS SELECT CALENDAR_ID
383 FROM JTF_CAL_RESOURCE_ASSIGN
384 WHERE resource_id = X_RESOURCE_ID
385 AND resource_type_code = X_RESOURCE_TYPE_CODE
386 AND primary_calendar_flag = p_primary_calendar_flag
387 AND cal_resource_assign_id <> X_CAL_RESOURCE_ASSIGN_ID
388 AND (( X_START_DATE_TIME <= start_date_time AND NVL(X_END_DATE_TIME,l_temp_fnd_end_date)
389 >= NVL(end_date_time,l_temp_fnd_end_date) )
390 OR ( X_START_DATE_TIME BETWEEN start_date_time AND NVL(end_date_time,l_temp_fnd_end_date))
391 OR ( NVL(X_END_DATE_TIME,Fnd_Api.g_miss_date) BETWEEN start_date_time AND
392 NVL(end_date_time,l_temp_fnd_end_date))
393 OR ((X_START_DATE_TIME < start_date_time) AND (NVL(X_END_DATE_TIME,l_temp_fnd_end_date)
394 > NVL(end_date_time,l_temp_fnd_end_date)))
395 OR ((X_START_DATE_TIME > start_date_time) AND (NVL(X_END_DATE_TIME,l_temp_fnd_end_date) <
396 NVL(end_date_time,l_temp_fnd_end_date))));
397
398 -- Added by abraina to fix bug 4200240
399
400 BEGIN
401 Fnd_Msg_Pub.initialize;
402 IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(TO_CHAR(X_RESOURCE_ID)) = FALSE THEN
403 Fnd_Message.set_name('JTF', 'JTF_CAL_REQUIRED');
404 Fnd_Message.set_token('P_NAME', X_RESOURCE_ID);
405 Fnd_Msg_Pub.ADD;
406 v_error := 'Y';
407 END IF;
408
409 IF Jtf_Cal_Resource_Assign_Pkg.NOT_NULL(X_START_DATE_TIME) = FALSE THEN
410 --fnd_message.set_name('JTF', 'START_DATE CANNOT BE NULL');
411 --app_exception.raise_exception;
412 Fnd_Message.set_name('JTF', 'JTF_CAL_START_DATE');
413 Fnd_Msg_Pub.ADD;
414
415 v_error := 'Y';
416 END IF;
417
418 IF Jtf_Cal_Resource_Assign_Pkg.END_GREATER_THAN_BEGIN(X_START_DATE_TIME, X_END_DATE_TIME) = FALSE THEN
419 --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
420 --app_exception.raise_exception;
421 Fnd_Message.set_name('JTF', 'JTF_CAL_END_DATE');
422 Fnd_Message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>X_START_DATE_TIME,calendar_aware=>fnd_date.calendar_aware));
423 Fnd_Message.set_token('P_End_Date', fnd_date.date_to_chardate(dateval=>X_END_DATE_TIME,calendar_aware=>fnd_date.calendar_aware));
424 Fnd_Msg_Pub.ADD;
425 v_error := 'Y';
426 END IF;
427
428 -- Used l_temp_fnd_end_date instead of FND_API.G_MISS_DATE to fix bug 4200240
429
430 OPEN C_CAL_ID('Y');
431 FETCH C_CAL_ID INTO v_calendar_id;
432 prime_flag_count := C_CAL_ID%ROWCOUNT;
433 CLOSE C_CAL_ID;
434
435 IF prime_flag_count = 1 AND X_PRIMARY_CALENDAR_FLAG = 'Y' THEN
436 --get resource name
437 OPEN C_RES_NAME;
438 FETCH C_RES_NAME INTO v_resource_name;
439 CLOSE C_RES_NAME;
440 --get calendar name
441 OPEN C_CAL_NAME(v_calendar_id);
442 FETCH C_CAL_NAME INTO v_calendar_name;
443 CLOSE C_CAL_NAME;
444 Fnd_Message.set_name('JTF', 'JTF_CAL_DUP_PRIMARY_CAL_FLAG');
445 Fnd_Message.set_token('RESOURCE_NAME',v_resource_name);
446 Fnd_Message.set_token('CALENDAR_NAME',v_calendar_name);
447 Fnd_Msg_Pub.ADD;
448 v_error := 'Y';
449 END IF;
450
451 -- Used l_temp_fnd_end_date instead of FND_API.G_MISS_DATE to fix bug 4200240
452
453 OPEN C_CAL_ID('N');
454 FETCH C_CAL_ID INTO v_calendar_id;
455 v_count := C_CAL_ID%ROWCOUNT;
456 CLOSE C_CAL_ID;
457
458 IF v_count = 1 AND X_PRIMARY_CALENDAR_FLAG = 'N' THEN
459 Fnd_Message.set_name('JTF', 'JTF_CAL_DUPLICATE_ROW');
460 Fnd_Msg_Pub.ADD;
461 v_error := 'Y';
462 END IF;
463
464 IF v_error = 'Y' THEN
465 X_ERROR := 'Y';
466 RETURN;
467 ELSE
468 X_ERROR := 'N';
469 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
470
471 UPDATE JTF_CAL_RESOURCE_ASSIGN SET
472 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
473 ATTRIBUTE5 = X_ATTRIBUTE5,
474 ATTRIBUTE6 = X_ATTRIBUTE6,
475 ATTRIBUTE7 = X_ATTRIBUTE7,
476 ATTRIBUTE8 = X_ATTRIBUTE8,
477 ATTRIBUTE9 = X_ATTRIBUTE9,
478 ATTRIBUTE10 = X_ATTRIBUTE10,
479 ATTRIBUTE11 = X_ATTRIBUTE11,
480 ATTRIBUTE12 = X_ATTRIBUTE12,
481 ATTRIBUTE13 = X_ATTRIBUTE13,
482 ATTRIBUTE14 = X_ATTRIBUTE14,
483 ATTRIBUTE15 = X_ATTRIBUTE15,
484 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
485 START_DATE_TIME = X_START_DATE_TIME,
486 END_DATE_TIME = X_END_DATE_TIME,
487 CALENDAR_ID = X_CALENDAR_ID,
488 RESOURCE_ID = X_RESOURCE_ID,
489 PRIMARY_CALENDAR_FLAG = X_PRIMARY_CALENDAR_FLAG,
490 ATTRIBUTE1 = X_ATTRIBUTE1,
491 ATTRIBUTE2 = X_ATTRIBUTE2,
492 ATTRIBUTE3 = X_ATTRIBUTE3,
493 ATTRIBUTE4 = X_ATTRIBUTE4,
494 CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID,
495 LAST_UPDATE_DATE = SYSDATE,
496 LAST_UPDATED_BY = Fnd_Global.USER_ID,
497 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
498 WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID;
499
500 END IF;
501 IF (SQL%NOTFOUND) THEN
502 RAISE NO_DATA_FOUND;
503 END IF;
504 END UPDATE_ROW;
505
506 PROCEDURE DELETE_ROW (
507 X_CAL_RESOURCE_ASSIGN_ID IN NUMBER
508 ) IS
509 BEGIN
510 DELETE FROM JTF_CAL_RESOURCE_ASSIGN
511 WHERE CAL_RESOURCE_ASSIGN_ID = X_CAL_RESOURCE_ASSIGN_ID;
512
513 IF (SQL%NOTFOUND) THEN
514 RAISE NO_DATA_FOUND;
515 END IF;
516
517 END DELETE_ROW;
518 /************************************************************************/
519 -- This procedure is under construction. The logic to prevent duplicate --
520 -- will come here. --
521 /***********************************************************************/
522
523 PROCEDURE check_dup_rec
524 (X_COUNT OUT NOCOPY NUMBER,
525 X_CALENDAR_ID NUMBER,
526 X_RESOURCE_TYPE_CODE IN VARCHAR2,
527 X_RESOURCE_ID IN NUMBER,
528 X_START_DATE_TIME IN DATE,
529 X_END_DATE_TIME IN DATE,
530 X_DUP OUT NOCOPY NUMBER) IS
531
532
533 v_start_date_time DATE;
534 v_end_date_time DATE;
535 v_count NUMBER:= 0;
536 v_flag VARCHAR2(1) := 'T';
537 --X_COUNT NUMBER := 0;
538 temp_count NUMBER := 0;
539 v_dup NUMBER := 0;
540
541
542
543 CURSOR DUP IS
544 SELECT resource_type_code,resource_id,start_date_time,end_date_time
545 FROM JTF_CAL_RESOURCE_ASSIGN
546 WHERE calendar_id = X_CALENDAR_ID
547 AND resource_id = X_RESOURCE_ID
548 AND RESOURCE_TYPE_CODE = X_RESOURCE_TYPE_CODE
549 ORDER BY start_date_time
550 ;
551 BEGIN
552
553 --v_start_date_time := TO_DATE(X_START_DATE_TIME,'DD-MON-RRRR HH24:MI');
554 --v_end_date_time := TO_DATE(X_END_DATE_TIME,'DD-MON-RRRR HH24:MI');
555
556 -- Jane Wang modified on 03/08/2002 to fix the GSCC Warning
557 v_start_date_time := TO_DATE(X_START_DATE_TIME,'DD/MM/YYYY HH24:MI');
558 v_end_date_time := TO_DATE(X_END_DATE_TIME,'DD/MM/YYYY HH24:MI');
559
560 SELECT COUNT(*) INTO temp_count
561 FROM JTF_CAL_RESOURCE_ASSIGN
562 WHERE calendar_id = X_CALENDAR_ID
563 AND resource_id = X_RESOURCE_ID
564 AND RESOURCE_TYPE_CODE = X_RESOURCE_TYPE_CODE
565 AND TRUNC(start_date_time) = TRUNC(v_start_date_time)
566 AND TRUNC(end_date_time) = TRUNC(v_end_date_time);
567
568 --fnd_msg_pub.initialize;
569
570 v_count := 0;
571
572 IF temp_count < 1 THEN
573 FOR dup_rec IN dup LOOP
574
575 IF dup_rec.end_date_time IS NULL THEN
576 IF TRUNC(v_end_date_time) IS NULL THEN
577
578 IF TRUNC(dup_rec.start_date_time) <= TRUNC(v_start_date_time)
579 OR TRUNC(dup_rec.start_date_time) > TRUNC(v_start_date_time) THEN
580
581 v_count := v_count+1;
582
583 END IF;
584 ELSIF TRUNC(v_end_date_time) > TRUNC(dup_rec.start_date_time) THEN
585 v_count := v_count+1;
586 END IF;
587 END IF;
588 IF TRUNC(dup_rec.end_date_time) IS NOT NULL THEN
589 IF (TRUNC(dup_rec.start_date_time) = TRUNC(v_start_date_time) AND TRUNC(dup_rec.end_date_time)
590 = TRUNC(v_end_date_time)) THEN
591 v_count :=v_count+1;
592
593 ELSIF (TRUNC(dup_rec.start_date_time) < TRUNC(v_start_date_time) AND TRUNC(dup_rec.end_date_time)
594 > TRUNC(v_end_date_time))THEN
595 v_count :=v_count+1;
596
597 ELSIF (TRUNC(dup_rec.start_date_time) > TRUNC(v_start_date_time) AND TRUNC(dup_rec.end_date_time) < TRUNC(v_end_date_time))THEN
598 v_count :=v_count+1;
599
600 ELSIF (TRUNC(dup_rec.start_date_time) = TRUNC(v_start_date_time) AND TRUNC(dup_rec.end_date_time) > TRUNC(v_end_date_time))THEN
601 v_count :=v_count+1;
602
603 ELSIF (TRUNC(dup_rec.start_date_time) = TRUNC(v_start_date_time) AND TRUNC(dup_rec.end_date_time) < TRUNC(v_end_date_time))THEN
604 v_count :=v_count+1;
605
606 ELSIF (TRUNC(dup_rec.start_date_time) > TRUNC(v_start_date_time) AND TRUNC(dup_rec.end_date_time) = TRUNC(v_end_date_time))THEN
607 v_count :=v_count+1;
608
609 ELSIF (TRUNC(dup_rec.start_date_time) < TRUNC(v_start_date_time) AND TRUNC(dup_rec.end_date_time) = TRUNC(v_end_date_time))THEN
610 v_count :=v_count+1;
611
612 END IF;
613 END IF;
614 END LOOP;
615 END IF;
616 x_count := v_count;
617 x_dup := temp_count;
618 RETURN;
619 /*IF x_count > 0 OR temp_count = 1 THEN
620 v_flag :='T'; -- Duplicate row
621 X_FLAG := 'T';
622 return;
623 ELSE
624 v_flag :='F';
625 X_FLAG := 'F';
626 return;
627 END IF;
628 */
629 EXCEPTION
630 WHEN OTHERS THEN
631 NULL;
632 RETURN;
633
634 END;
635
636
637
638
639 /*************************************************************************/
640 FUNCTION not_null(column_to_check IN CHAR) RETURN BOOLEAN IS
641 BEGIN
642 IF column_to_check IS NULL THEN
643 RETURN(FALSE);
644 ELSE
645 RETURN(TRUE);
646 END IF;
647 END;
648
649 /*************************************************************************/
650 FUNCTION end_greater_than_begin(start_date IN DATE, end_date IN DATE) RETURN BOOLEAN IS
651 BEGIN
652 IF start_date > end_date THEN
653 RETURN(FALSE);
654 ELSE
655 RETURN(TRUE);
656 END IF;
657 END;
658
659
660 END Jtf_Cal_Resource_Assign_Pkg;