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