[Home] [Help]
PACKAGE BODY: APPS.JTF_CAL_SHIFT_CONSTRUCTS_PKG
Source
1 PACKAGE BODY jtf_cal_shift_constructs_pkg AS
2 /* $Header: jtfclscb.pls 120.4 2011/03/22 06:24:46 anangupt ship $ */
3 PROCEDURE insert_row(
4 x_error OUT NOCOPY VARCHAR2
5 , x_rowid IN OUT NOCOPY VARCHAR2
6 , x_shift_construct_id IN OUT NOCOPY NUMBER
7 , x_shift_id IN NUMBER
8 , x_unit_of_time_value IN VARCHAR2
9 , x_begin_time IN DATE
10 , x_end_time IN DATE
11 , x_start_date_active IN DATE
12 , x_end_date_active IN DATE
13 , x_availability_type_code IN VARCHAR2
14 , x_attribute1 IN VARCHAR2
15 , x_attribute2 IN VARCHAR2
16 , x_attribute3 IN VARCHAR2
17 , x_attribute4 IN VARCHAR2
18 , x_attribute5 IN VARCHAR2
19 , x_attribute6 IN VARCHAR2
20 , x_attribute7 IN VARCHAR2
21 , x_attribute8 IN VARCHAR2
22 , x_attribute9 IN VARCHAR2
23 , x_attribute10 IN VARCHAR2
24 , x_attribute11 IN VARCHAR2
25 , x_attribute12 IN VARCHAR2
26 , x_attribute13 IN VARCHAR2
27 , x_attribute14 IN VARCHAR2
28 , x_attribute15 IN VARCHAR2
29 , x_attribute_category 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 /* -- Commented By Sarvi B'cos was not used else where and also was raising an exception.
37 cursor C is select ROWID from JTF_CAL_SHIFT_CONSTRUCTS
38 where SHIFT_CONSTRUCT_ID = X_SHIFT_CONSTRUCT_ID;
39 */
40 v_error CHAR := 'N';
41 x_object_version_number NUMBER;
42 v_shift_construct_id NUMBER;
43 p_rec NUMBER;
44 chk_shift NUMBER;
45 v_begin_time DATE;
46 v_end_time DATE;
47 BEGIN
48 fnd_msg_pub.initialize;
49 x_object_version_number := 1;
50
51 IF jtf_cal_shift_constructs_pkg.not_null(x_begin_time) = FALSE THEN
52 fnd_message.set_name('JTF', 'JTF_CAL_BEGIN_TIME');
53 fnd_msg_pub.ADD;
54 v_error := 'Y';
55 END IF;
56
57 IF jtf_cal_shift_constructs_pkg.not_null(x_end_time) = FALSE THEN
58 fnd_message.set_name('JTF', 'JTF_CAL_END_TIME');
59 fnd_msg_pub.ADD;
60 v_error := 'Y';
61 END IF;
62
63 IF jtf_cal_shift_constructs_pkg.end_greater_than_begin(x_begin_time, x_end_time) = FALSE THEN
64 fnd_message.set_name('JTF', 'JTF_CAL_SHIFT_END_TIME');
65 fnd_msg_pub.ADD;
66 v_error := 'Y';
67 END IF;
68
69 IF jtf_cal_shift_constructs_pkg.end_greater_than_begin(x_start_date_active, x_end_date_active) = FALSE THEN
70 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
71 fnd_message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>x_start_date_active,calendar_aware=>fnd_date.calendar_aware));
72 fnd_message.set_token('P_End_Date', fnd_date.date_to_chardate(dateval=>x_end_date_active,calendar_aware=>fnd_date.calendar_aware));
73 fnd_msg_pub.ADD;
74 v_error := 'Y';
75 END IF;
76
77
78 IF jtf_cal_shift_constructs_pkg.not_null_char(x_availability_type_code) = FALSE THEN
79 fnd_message.set_name('JTF', 'JTF_CAL_AVAILABILITY_TYPE_CODE');
80 fnd_msg_pub.ADD;
81 v_error := 'Y';
82 END IF;
83
84 SELECT COUNT(*)
85 INTO p_rec
86 FROM jtf_cal_shifts_b
87 WHERE shift_id = x_shift_id;
88
89 IF p_rec = 0 THEN
90 fnd_message.set_name('JTF', 'JTF_CAL_PATTERN_SHIFT');
91 fnd_msg_pub.ADD;
92 v_error := 'Y';
93 END IF;
94
95 --End of Validation
96 IF v_error = 'Y' THEN
97 x_error := 'Y';
98 RETURN;
99 ELSE
100 SELECT jtf_cal_shift_constructs_s.NEXTVAL
101 INTO v_shift_construct_id
102 FROM DUAL;
103
104 -- Code Added by Venkat Putcha for duplicate sequence checking
105 SELECT COUNT(*)
106 INTO chk_shift
107 FROM jtf_cal_shift_constructs
108 WHERE shift_construct_id = v_shift_construct_id;
109
110 IF chk_shift > 0 THEN
111 fnd_message.set_name('JTF', 'JTF_CAL_SHIFT_SEQ_NUM');
112 fnd_message.set_token('P_SHIFT_SEQ_NUM', v_shift_construct_id);
113 fnd_msg_pub.ADD;
114 v_error := 'Y';
115 x_error := 'Y';
116 RETURN;
117 END IF;
118
119 -- End Of Validation
120 x_shift_construct_id := v_shift_construct_id;
121
122 /* Add User Hook Check for INSERT by Jane Wang on 01/25/02 */
123 /* Comment the User Hook Check out by Jane Wang on 03/12/02 */
124 /*
125 IF jtf_usr_hks.ok_to_execute(
126 'JTF_CAL_SHIFT_CONSTRUCTS_PKG',
127 'INSERT_ROW',
128 'B',
129 'C')
130 THEN
131 JTF_CAL_SHIFT_CUHK.insert_shift_constructs_pre
132 (X_ERROR => X_ERROR,
133 X_ROWID => X_ROWID,
134 X_SHIFT_CONSTRUCT_ID => X_SHIFT_CONSTRUCT_ID ,
135 X_SHIFT_ID => X_SHIFT_ID,
136 X_UNIT_OF_TIME_VALUE => X_UNIT_OF_TIME_VALUE ,
137 X_BEGIN_TIME => X_BEGIN_TIME ,
138 X_END_TIME => X_END_TIME,
139 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE ,
140 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE ,
141 X_AVAILABILITY_TYPE_CODE => X_AVAILABILITY_TYPE_CODE ,
142 X_ATTRIBUTE1 => X_ATTRIBUTE1,
143 X_ATTRIBUTE2 => X_ATTRIBUTE2,
144 X_ATTRIBUTE3 => X_ATTRIBUTE3 ,
145 X_ATTRIBUTE4 => X_ATTRIBUTE4 ,
146 X_ATTRIBUTE5 => X_ATTRIBUTE5,
147 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
148 X_ATTRIBUTE7 => X_ATTRIBUTE7 ,
149 X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
150 X_ATTRIBUTE9 => X_ATTRIBUTE9 ,
151 X_ATTRIBUTE10 => X_ATTRIBUTE10 ,
152 X_ATTRIBUTE11 => X_ATTRIBUTE11 ,
153 X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
154 X_ATTRIBUTE13 => X_ATTRIBUTE13 ,
155 X_ATTRIBUTE14 => X_ATTRIBUTE14 ,
156 X_ATTRIBUTE15 => X_ATTRIBUTE15 ,
157 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY ,
158 X_CREATION_DATE => X_CREATION_DATE,
159 X_CREATED_BY => X_CREATED_BY ,
160 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE ,
161 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY ,
162 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
163 );
164
165 END IF; -- End of User Hook Check for INSERT
166 */
167 BEGIN
168 INSERT INTO jtf_cal_shift_constructs
169 (
170 shift_construct_id
171 , shift_id
172 , unit_of_time_value
173 , begin_time
174 , end_time
175 , start_date_active
176 , end_date_active
177 , availability_type_code
178 , created_by
179 , creation_date
180 , last_updated_by
181 , last_update_date
182 , last_update_login
183 , attribute1
184 , attribute2
185 , attribute3
186 , attribute4
187 , attribute5
188 , attribute6
189 , attribute7
190 , attribute8
191 , attribute9
192 , attribute10
193 , attribute11
194 , attribute12
195 , attribute13
196 , attribute14
197 , attribute15
198 , attribute_category
199 , object_version_number
200 )
201 VALUES (
202 v_shift_construct_id
203 , x_shift_id
204 , x_unit_of_time_value
205 , x_begin_time
206 , x_end_time
207 , x_start_date_active
208 , x_end_date_active
209 , x_availability_type_code
210 , fnd_global.user_id
211 , SYSDATE
212 , fnd_global.user_id
213 , SYSDATE
214 , fnd_global.login_id
215 , x_attribute1
216 , x_attribute2
217 , x_attribute3
218 , x_attribute4
219 , x_attribute5
220 , x_attribute6
221 , x_attribute7
222 , x_attribute8
223 , x_attribute9
224 , x_attribute10
225 , x_attribute11
226 , x_attribute12
227 , x_attribute13
228 , x_attribute14
229 , x_attribute15
230 , x_attribute_category
231 , x_object_version_number
232 );
233 EXCEPTION
234 WHEN OTHERS THEN
235 -- fnd_message.set_name('JTF', 'JTF_FM_ADMIN_ADDERROR');
236 fnd_message.set_name('JTF', SQLERRM);
237 fnd_msg_pub.ADD;
238 v_error := 'Y';
239 x_error := 'Y';
240 END;
241 /*
242 open c;
243 fetch c into X_ROWID;
244 if (c%notfound) then
245 close c;
246 raise no_data_found;
247 end if;
248 close c;
249 */
250 END IF;
251 END insert_row;
252
253 PROCEDURE lock_row(
254 x_error OUT NOCOPY VARCHAR2
255 , x_shift_construct_id IN NUMBER
256 , x_shift_id IN NUMBER
257 , x_unit_of_time_value IN VARCHAR2
258 , x_begin_time IN DATE
259 , x_end_time IN DATE
260 , x_start_date_active IN DATE
261 , x_end_date_active IN DATE
262 , x_availability_type_code IN VARCHAR2
263 , x_attribute1 IN VARCHAR2
264 , x_attribute2 IN VARCHAR2
265 , x_attribute3 IN VARCHAR2
266 , x_attribute4 IN VARCHAR2
267 , x_attribute5 IN VARCHAR2
268 , x_attribute6 IN VARCHAR2
269 , x_attribute7 IN VARCHAR2
270 , x_attribute8 IN VARCHAR2
271 , x_attribute9 IN VARCHAR2
272 , x_attribute10 IN VARCHAR2
273 , x_attribute11 IN VARCHAR2
274 , x_attribute12 IN VARCHAR2
275 , x_attribute13 IN VARCHAR2
276 , x_attribute14 IN VARCHAR2
277 , x_attribute15 IN VARCHAR2
278 , x_attribute_category IN VARCHAR2
279 ) IS
280 CURSOR c1 IS
281 SELECT shift_id
282 , unit_of_time_value
283 , begin_time
284 , end_time
285 , start_date_active
286 , end_date_active
287 , availability_type_code
288 , attribute1
289 , attribute2
290 , attribute3
291 , attribute4
292 , attribute5
293 , attribute6
294 , attribute7
295 , attribute8
296 , attribute9
297 , attribute10
298 , attribute11
299 , attribute12
300 , attribute13
301 , attribute14
302 , attribute15
303 , attribute_category
304 , shift_construct_id
305 FROM jtf_cal_shift_constructs
306 WHERE shift_construct_id = x_shift_construct_id
307 FOR UPDATE OF shift_construct_id NOWAIT;
308
309 v_error CHAR := 'N';
310 BEGIN
311 fnd_msg_pub.initialize;
312
313 FOR tlinfo IN c1 LOOP
314 IF (
315 (tlinfo.shift_construct_id = x_shift_construct_id)
316 AND (tlinfo.shift_id = x_shift_id)
317 AND (tlinfo.unit_of_time_value = x_unit_of_time_value)
318 /* AND (tlinfo.BEGIN_TIME = X_BEGIN_TIME)
319 AND (tlinfo.END_TIME = X_END_TIME)
320 */
321 AND (
322 (tlinfo.start_date_active = x_start_date_active)
323 OR ((tlinfo.start_date_active IS NULL) AND(x_start_date_active IS NULL))
324 )
325 AND (
326 (tlinfo.end_date_active = x_end_date_active)
327 OR ((tlinfo.end_date_active IS NULL) AND(x_end_date_active IS NULL))
328 )
329 AND (tlinfo.availability_type_code = x_availability_type_code)
330 AND ((tlinfo.attribute1 = x_attribute1) OR((tlinfo.attribute1 IS NULL) AND(x_attribute1 IS NULL)))
331 AND ((tlinfo.attribute2 = x_attribute2) OR((tlinfo.attribute2 IS NULL) AND(x_attribute2 IS NULL)))
332 AND ((tlinfo.attribute3 = x_attribute3) OR((tlinfo.attribute3 IS NULL) AND(x_attribute3 IS NULL)))
333 AND ((tlinfo.attribute4 = x_attribute4) OR((tlinfo.attribute4 IS NULL) AND(x_attribute4 IS NULL)))
334 AND ((tlinfo.attribute5 = x_attribute5) OR((tlinfo.attribute5 IS NULL) AND(x_attribute5 IS NULL)))
335 AND ((tlinfo.attribute6 = x_attribute6) OR((tlinfo.attribute6 IS NULL) AND(x_attribute6 IS NULL)))
336 AND ((tlinfo.attribute7 = x_attribute7) OR((tlinfo.attribute7 IS NULL) AND(x_attribute7 IS NULL)))
337 AND ((tlinfo.attribute8 = x_attribute8) OR((tlinfo.attribute8 IS NULL) AND(x_attribute8 IS NULL)))
338 AND ((tlinfo.attribute9 = x_attribute9) OR((tlinfo.attribute9 IS NULL) AND(x_attribute9 IS NULL)))
339 AND ((tlinfo.attribute10 = x_attribute10) OR((tlinfo.attribute10 IS NULL) AND(x_attribute10 IS NULL)))
340 AND ((tlinfo.attribute11 = x_attribute11) OR((tlinfo.attribute11 IS NULL) AND(x_attribute11 IS NULL)))
341 AND ((tlinfo.attribute12 = x_attribute12) OR((tlinfo.attribute12 IS NULL) AND(x_attribute12 IS NULL)))
342 AND ((tlinfo.attribute13 = x_attribute13) OR((tlinfo.attribute13 IS NULL) AND(x_attribute13 IS NULL)))
343 AND ((tlinfo.attribute14 = x_attribute14) OR((tlinfo.attribute14 IS NULL) AND(x_attribute14 IS NULL)))
344 AND ((tlinfo.attribute15 = x_attribute15) OR((tlinfo.attribute15 IS NULL) AND(x_attribute15 IS NULL)))
345 AND (
346 (tlinfo.attribute_category = x_attribute_category)
347 OR ((tlinfo.attribute_category IS NULL) AND(x_attribute_category IS NULL))
348 )
349 ) THEN
350 NULL;
351 ELSE
352 fnd_message.set_name('JTF', 'FORM_RECORD_CHANGED');
353 fnd_msg_pub.ADD;
354 v_error := 'Y';
355 -- fnd_message.set_name('JTF', 'FORM_RECORD_CHANGED');
356 -- app_exception.raise_exception;
357 END IF;
358
359 IF v_error = 'Y' THEN
360 x_error := 'Y';
361 RETURN;
362 END IF;
363 END LOOP;
364
365 RETURN;
366 EXCEPTION
367 WHEN app_exception.record_lock_exception THEN
368 fnd_message.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
369 fnd_msg_pub.ADD;
370 v_error := 'Y';
371
372 IF v_error = 'Y' THEN
373 x_error := 'Y';
374 RETURN;
375 END IF;
376 END lock_row;
377
378 PROCEDURE update_row(
379 x_error OUT NOCOPY VARCHAR2
380 , x_shift_construct_id IN NUMBER
381 , x_shift_id IN NUMBER
382 , x_unit_of_time_value IN VARCHAR2
383 , x_begin_time IN DATE
384 , x_end_time IN DATE
385 , x_start_date_active IN DATE
386 , x_end_date_active IN DATE
387 , x_availability_type_code IN VARCHAR2
388 , x_attribute1 IN VARCHAR2
389 , x_attribute2 IN VARCHAR2
390 , x_attribute3 IN VARCHAR2
391 , x_attribute4 IN VARCHAR2
392 , x_attribute5 IN VARCHAR2
393 , x_attribute6 IN VARCHAR2
394 , x_attribute7 IN VARCHAR2
395 , x_attribute8 IN VARCHAR2
396 , x_attribute9 IN VARCHAR2
397 , x_attribute10 IN VARCHAR2
398 , x_attribute11 IN VARCHAR2
399 , x_attribute12 IN VARCHAR2
400 , x_attribute13 IN VARCHAR2
401 , x_attribute14 IN VARCHAR2
402 , x_attribute15 IN VARCHAR2
403 , x_attribute_category IN VARCHAR2
404 , x_last_update_date IN DATE
405 , x_last_updated_by IN NUMBER
406 , x_last_update_login IN NUMBER
407 ) IS
408 v_error CHAR := 'N';
409 l_object_version_number NUMBER;
410 BEGIN
411 fnd_msg_pub.initialize;
412
413 IF jtf_cal_shift_constructs_pkg.not_null(x_begin_time) = FALSE THEN
414 --fnd_message.set_name('JTF', 'BEGIN_TIME CANNOT BE NULL');
415 --app_exception.raise_exception;
416 fnd_message.set_name('JTF', 'JTF_CAL_BEGIN_TIME');
417 --fnd_message.set_token('P_Name', 'BEGIN_TIME');
418 fnd_msg_pub.ADD;
419 v_error := 'Y';
420 END IF;
421
422 IF jtf_cal_shift_constructs_pkg.not_null(x_end_time) = FALSE THEN
423 --fnd_message.set_name('JTF', 'END_TIME CANNOT BE NULL');
424 --app_exception.raise_exception;
425 fnd_message.set_name('JTF', 'JTF_CAL_END_TIME');
426 --fnd_message.set_token('P_Name', 'END_TIME');
427 fnd_msg_pub.ADD;
428 v_error := 'Y';
429 END IF;
430
431 IF jtf_cal_shift_constructs_pkg.end_greater_than_begin(x_begin_time, x_end_time) = FALSE THEN
432 --fnd_message.set_name('JTF', 'START_TIME IS GREATER THAN END_TIME');
433 --app_exception.raise_exception;
434 fnd_message.set_name('JTF', 'JTF_CAL_SHIFT_END_TIME');
435 --fnd_message.set_token('P_Start_Date', X_BEGIN_TIME);
436 --fnd_message.set_token('P_End_Date', X_END_TIME);
437 fnd_msg_pub.ADD;
438 v_error := 'Y';
439 END IF;
440
441 IF jtf_cal_shift_constructs_pkg.end_greater_than_begin(x_start_date_active, x_end_date_active) = FALSE THEN
442 --fnd_message.set_name('JTF', 'START_DATE IS GREATER THAN END DATE');
443 --app_exception.raise_exception;
444 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
445 fnd_message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>x_start_date_active,calendar_aware=>fnd_date.calendar_aware));
446 fnd_message.set_token('P_End_Date', fnd_date.date_to_chardate(dateval=>x_end_date_active,calendar_aware=>fnd_date.calendar_aware));
447 fnd_msg_pub.ADD;
448 v_error := 'Y';
449 END IF;
450
451 /*
452 IF JTF_CAL_SHIFT_CONSTRUCTS_PKG.NOT_NULL(X_AVAILABILITY_TYPE_CODE) = FALSE THEN
453 --fnd_message.set_name('JTF', 'AVAILABILITY_TYPE_CODE CANNOT BE NULL');
454 --app_exception.raise_exception;
455 fnd_message.set_name('JTF', 'JTF_CAL_AVAILABILITY_TYPE_CODE');
456 --fnd_message.set_token('P_Name', 'AVAILABILITY_TYPE_CODE');
457 fnd_msg_pub.add;
458 v_error := 'Y';
459 END IF;
460
461 --IF JTF_CAL_SHIFT_CONSTRUCTS_PKG.VALIDATE_FND_LOOKUPS(X_AVAILABILITY_TYPE_CODE, 'AVAILABILITY TYPE')
462 -- = FALSE THEN
463 -- fnd_message.set_name('JTF', 'AVAILABILITY_TYPE_CODE DOES NOT EXIST IN FND_LOOKUPS');
464 -- app_exception.raise_exception;
465 -- v_error := 'Y';
466 --END IF;
467
468 IF JTF_CAL_SHIFT_CONSTRUCTS_PKG.DUPLICATION_SHIFT(X_SHIFT_ID, X_UNIT_OF_TIME_VALUE,
469 X_BEGIN_TIME, X_END_TIME,
470 X_START_DATE_ACTIVE, X_END_DATE_ACTIVE) = FALSE THEN
471 --fnd_message.set_name('JTF', 'SHIFT WITH THESE PARAMETERS ALREADY EXISTS');
472 --app_exception.raise_exception;
473 fnd_message.set_name('JTF', 'JTF_CAL_ALREADY_EXISTS');
474 fnd_message.set_token('P_Name', 'SHIFT');
475 fnd_msg_pub.add;
476 v_error := 'Y';
477 END IF;
478 */
479 IF jtf_cal_shift_constructs_pkg.not_null(x_start_date_active) = FALSE THEN
480 --fnd_message.set_name('JTF', 'START_DATE_ACTIVE CANNOT BE NULL');
481 --app_exception.raise_exception;
482 fnd_message.set_name('JTF', 'JTF_CAL_START_DATE');
483 fnd_msg_pub.ADD;
484 v_error := 'Y';
485 END IF;
486
487 -- Comment out by Jane Wang on 03/12/2002
488 -- To allow a user to define two shift patterns for the same day
489 /*
490 IF JTF_CAL_SHIFT_CONSTRUCTS_PKG.OVERLAP_SHIFT(X_SHIFT_ID, X_UNIT_OF_TIME_VALUE,
491 X_BEGIN_TIME, X_END_TIME,
492 X_START_DATE_ACTIVE, X_END_DATE_ACTIVE,
493 X_SHIFT_CONSTRUCT_ID) = FALSE THEN
494 --fnd_message.set_name('JTF', 'SHIFT WITH THESE PARAMETERS ALREADY EXISTS');
495 --app_exception.raise_exception;
496 fnd_message.set_name('JTF', 'JTF_CAL_SHIFT_PATTERN_OVERLAPS');
497 fnd_msg_pub.add;
498 v_error := 'Y';
499 END IF;
500 */
501 IF v_error = 'Y' THEN
502 x_error := 'Y';
503 RETURN;
504 ELSE
505 /* Add User Hook Check for UPDATE by Jane Wang on 01/25/02 */
506 /* Comment the User Hook Check by Jane Wang on 03/12/02 */
507
508 /*
509 IF jtf_usr_hks.ok_to_execute(
510 'JTF_CAL_SHIFT_CONSTRUCTS_PKG',
511 'UPDATE_ROW',
512 'B',
513 'C')
514 THEN
515 JTF_CAL_SHIFT_CUHK.update_shift_pre
516 (X_ERROR => X_ERROR,
517 X_SHIFT_ID => X_SHIFT_ID,
518 X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
519 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
520 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
521 X_ATTRIBUTE1 => X_ATTRIBUTE1,
522 X_ATTRIBUTE2 => X_ATTRIBUTE2,
523 X_ATTRIBUTE3 => X_ATTRIBUTE3,
524 X_ATTRIBUTE4 => X_ATTRIBUTE4,
525 X_ATTRIBUTE5 => X_ATTRIBUTE5,
526 X_ATTRIBUTE6 => X_ATTRIBUTE6,
527 X_ATTRIBUTE7 => X_ATTRIBUTE7,
528 X_ATTRIBUTE8 => X_ATTRIBUTE8,
529 X_ATTRIBUTE9 => X_ATTRIBUTE9,
530 X_ATTRIBUTE10 => X_ATTRIBUTE10,
531 X_ATTRIBUTE11 => X_ATTRIBUTE11,
532 X_ATTRIBUTE12 => X_ATTRIBUTE12,
533 X_ATTRIBUTE13 => X_ATTRIBUTE13,
534 X_ATTRIBUTE14 => X_ATTRIBUTE14,
535 X_ATTRIBUTE15 => X_ATTRIBUTE15,
536 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
537 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
538 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
539 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
540 );
541 END IF; -- End of User Hook Check for UPDATE
542 */
543 BEGIN
544 UPDATE jtf_cal_shift_constructs
545 SET
546 -- SHIFT_ID = X_SHIFT_ID,
547 unit_of_time_value = x_unit_of_time_value
548 , begin_time = x_begin_time
549 , end_time = x_end_time
550 , start_date_active = x_start_date_active
551 , end_date_active = x_end_date_active
552 , availability_type_code = x_availability_type_code
553 , attribute1 = x_attribute1
554 , attribute2 = x_attribute2
555 , attribute3 = x_attribute3
556 , attribute4 = x_attribute4
557 , attribute5 = x_attribute5
558 , attribute6 = x_attribute6
559 , attribute7 = x_attribute7
560 , attribute8 = x_attribute8
561 , attribute9 = x_attribute9
562 , attribute10 = x_attribute10
563 , attribute11 = x_attribute11
564 , attribute12 = x_attribute12
565 , attribute13 = x_attribute13
566 , attribute14 = x_attribute14
567 , attribute15 = x_attribute15
568 , attribute_category = x_attribute_category
569 , shift_construct_id = x_shift_construct_id
570 , last_update_date = SYSDATE
571 , last_updated_by = fnd_global.user_id
572 , last_update_login = fnd_global.login_id
573 , object_version_number = NVL(object_version_number, 0) + 1
574 WHERE shift_construct_id = x_shift_construct_id;
575 EXCEPTION
576 WHEN OTHERS THEN
577 fnd_message.set_name('JTF', 'JTF_FM_ADMIN_EDITERROR');
578 fnd_msg_pub.ADD;
579 v_error := 'Y';
580 x_error := 'Y';
581 END;
582 -- if (sql%notfound) then
583 -- raise no_data_found;
584 -- end if;
585 END IF;
586 END update_row;
587
588 PROCEDURE delete_row(x_error OUT NOCOPY VARCHAR2, x_shift_construct_id IN NUMBER) IS
589 CURSOR c_sh_exist_in_task_assmt(p_shift_cons_id NUMBER) IS
590 SELECT 1
591 FROM jtf_task_assignments
592 WHERE shift_construct_id = p_shift_cons_id AND ROWNUM = 1;
593
594 l_exists NUMBER := NULL;
595 v_error VARCHAR2(1) := 'N';
596 BEGIN
597 OPEN c_sh_exist_in_task_assmt(x_shift_construct_id);
598
599 FETCH c_sh_exist_in_task_assmt
600 INTO l_exists;
601
602 CLOSE c_sh_exist_in_task_assmt;
603
604 IF NVL(l_exists, 2) = 1 THEN
605 fnd_message.set_name('JTF', 'JTF_CAL_SHIFT_DEL_VAL');
606 fnd_msg_pub.ADD;
607 v_error := 'Y';
608 END IF;
609
610 IF v_error = 'Y' THEN
611 x_error := 'Y';
612 RETURN;
613 ELSE
614 DELETE FROM jtf_cal_shift_constructs
615 WHERE shift_construct_id = x_shift_construct_id;
616 END IF;
617 END delete_row;
618
619 /*************************************************************************/
620 FUNCTION not_null(column_to_check IN DATE)
621 RETURN BOOLEAN IS
622 BEGIN
623 IF column_to_check IS NULL THEN
624 RETURN(FALSE);
625 ELSE
626 RETURN(TRUE);
627 END IF;
628 END;
629
630 /*************************************************************************/
631 FUNCTION not_null_char(column_to_check IN CHAR)
632 RETURN BOOLEAN IS
633 BEGIN
634 IF column_to_check IS NULL THEN
635 RETURN(FALSE);
636 ELSE
637 RETURN(TRUE);
638 END IF;
639 END;
640
641 /*************************************************************************/
642 FUNCTION end_greater_than_begin(start_date IN DATE, end_date IN DATE)
643 RETURN BOOLEAN IS
644 BEGIN
645 IF (start_date > end_date) THEN
646 RETURN(FALSE);
647 ELSE
648 RETURN(TRUE);
649 END IF;
650 END;
651
652 /*************************************************************************/
653 FUNCTION duplication_shift(
654 x_shift_id IN NUMBER
655 , x_unit_of_time_value IN CHAR
656 , x_begin_time IN DATE
657 , x_end_time IN DATE
658 , x_start_date_active IN DATE
659 , x_end_date_active IN DATE
660 )
661 RETURN BOOLEAN IS
662 CURSOR dup IS
663 SELECT shift_id
664 , unit_of_time_value
665 , begin_time
666 , end_time
667 , start_date_active
668 , end_date_active
669 FROM jtf_cal_shift_constructs
670 WHERE shift_id = x_shift_id;
671 BEGIN
672 -- Shift is unique
673 FOR dup_rec IN dup LOOP
674 IF (
675 dup_rec.shift_id = x_shift_id
676 AND dup_rec.unit_of_time_value = x_unit_of_time_value
677 AND dup_rec.begin_time = x_begin_time
678 AND dup_rec.end_time = x_end_time
679 AND dup_rec.start_date_active = x_start_date_active
680 AND dup_rec.end_date_active = x_end_date_active
681 ) THEN
682 RETURN(FALSE);
683 ELSE
684 RETURN(TRUE);
685 END IF;
686 END LOOP;
687 END duplication_shift;
688
689 /*******************************************************************************************/
690 FUNCTION overlap_shift(
691 x_shift_id IN NUMBER
692 , x_unit_of_time_value IN CHAR
693 , x_start_date_time IN DATE
694 , x_end_date_time IN DATE
695 , x_start_date_active IN DATE
696 , x_end_date_active IN DATE
697 , x_shift_construct_id IN NUMBER
698 )
699 RETURN BOOLEAN IS
700 CURSOR dup IS
701 SELECT shift_id
702 , unit_of_time_value
703 , begin_time
704 , end_time
705 , shift_construct_id
706 FROM jtf_cal_shift_constructs
707 WHERE shift_id = x_shift_id;
708
709 l_error NUMBER := 1;
710
711 CURSOR c_check_1(l_shift_id NUMBER, l_day DATE) IS
712 SELECT shift_construct_id
713 FROM jtf_cal_shift_constructs
714 WHERE shift_id = l_shift_id AND begin_time <= l_day;
715
716 r_check_1 c_check_1%ROWTYPE;
717
718 CURSOR c_check_2(l_shift_id NUMBER, l_day DATE) IS
719 SELECT begin_time
720 , end_time
721 , shift_construct_id
722 FROM jtf_cal_shift_constructs
723 WHERE shift_id = l_shift_id AND TRUNC(end_time) > TO_DATE('07/01/1995', 'DD/MM/YYYY');
724
725 r_check_2 c_check_2%ROWTYPE;
726 l_start_date DATE;
727 l_end_date DATE;
728 l_no NUMBER;
729 l_day DATE;
730 BEGIN
731 -- Shift is unique
732 IF (x_shift_construct_id IS NULL) THEN
733 FOR dup_rec IN dup LOOP
734 IF (
735 dup_rec.shift_id = x_shift_id
736 AND (
737 (
738 x_start_date_time <= dup_rec.begin_time
739 AND NVL(x_end_date_time, fnd_api.g_miss_date) >= NVL(dup_rec.end_time, fnd_api.g_miss_date)
740 )
741 OR (x_start_date_time BETWEEN dup_rec.begin_time AND NVL(dup_rec.end_time, fnd_api.g_miss_date))
742 OR (
743 NVL(x_end_date_time, fnd_api.g_miss_date) BETWEEN dup_rec.begin_time
744 AND NVL(dup_rec.end_time, fnd_api.g_miss_date)
745 )
746 OR (
747 (x_start_date_time > dup_rec.begin_time)
748 AND (NVL(x_end_date_time, fnd_api.g_miss_date) < NVL(dup_rec.end_time, fnd_api.g_miss_date))
749 )
750 )
751 ) THEN
752 l_error := 0;
753 EXIT;
754 END IF;
755 END LOOP;
756
757 IF (TRUNC(x_end_date_time) > TO_DATE('07/01/1995', 'DD/MM/YYYY')) THEN
758 l_no := TRUNC(x_end_date_time) - TO_DATE('07/01/1995', 'DD/MM/YYYY');
759 l_day := (TO_DATE('01/01/1995', 'DD/MM/YYYY') +(l_no - 1)) +(x_end_date_time - TRUNC(x_end_date_time));
760
761 OPEN c_check_1(x_shift_id, l_day);
762
763 FETCH c_check_1
764 INTO r_check_1;
765
766 IF (c_check_1%FOUND) THEN
767 l_error := 0;
768 END IF;
769
770 CLOSE c_check_1;
771 ELSE
772 OPEN c_check_2(x_shift_id, l_day);
773
774 FETCH c_check_2
775 INTO r_check_2;
776
777 IF (c_check_2%FOUND) THEN
778 l_no := TRUNC(r_check_2.end_time) - TO_DATE('07/01/1995', 'DD/MM/YYYY');
779 l_day := (TO_DATE('01/01/1995', 'DD/MM/YYYY') +(l_no - 1))
780 +(r_check_2.end_time - TRUNC(r_check_2.end_time));
781
782 IF ((x_start_date_time < l_day) OR(x_end_date_time < l_day)) THEN
783 l_error := 0;
784 END IF;
785 END IF;
786
787 CLOSE c_check_2;
788 END IF;
789 ELSE
790 FOR dup_rec IN dup LOOP
791 IF (
792 dup_rec.shift_id = x_shift_id
793 AND dup_rec.shift_construct_id <> x_shift_construct_id
794 AND (
795 (
796 x_start_date_time <= dup_rec.begin_time
797 AND NVL(x_end_date_time, fnd_api.g_miss_date) >= NVL(dup_rec.end_time, fnd_api.g_miss_date)
798 )
799 OR (x_start_date_time BETWEEN dup_rec.begin_time AND NVL(dup_rec.end_time, fnd_api.g_miss_date))
800 OR (
801 NVL(x_end_date_time, fnd_api.g_miss_date) BETWEEN dup_rec.begin_time
802 AND NVL(dup_rec.end_time, fnd_api.g_miss_date)
803 )
804 OR (
805 (x_start_date_time > dup_rec.begin_time)
806 AND (NVL(x_end_date_time, fnd_api.g_miss_date) < NVL(dup_rec.end_time, fnd_api.g_miss_date))
807 )
808 )
809 ) THEN
810 l_error := 0;
811 EXIT;
812 END IF;
813 END LOOP;
814
815 IF (TRUNC(x_end_date_time) > TO_DATE('07/01/1995', 'DD/MM/YYYY')) THEN
816 l_no := TRUNC(x_end_date_time) - TO_DATE('07/01/1995', 'DD/MM/YYYY');
817 l_day := (TO_DATE('01/01/1995', 'DD/MM/YYYY') +(l_no - 1)) +(x_end_date_time - TRUNC(x_end_date_time));
818
819 OPEN c_check_1(x_shift_id, l_day);
820
821 FETCH c_check_1
822 INTO r_check_1;
823
824 IF ((c_check_1%FOUND) AND(r_check_1.shift_construct_id <> x_shift_construct_id)) THEN
825 l_error := 0;
826 END IF;
827
828 CLOSE c_check_1;
829 ELSE
830 OPEN c_check_2(x_shift_id, l_day);
831
832 FETCH c_check_2
833 INTO r_check_2;
834
835 IF (c_check_2%FOUND) THEN
836 l_no := TRUNC(r_check_2.end_time) - TO_DATE('07/01/1995', 'DD/MM/YYYY');
837 l_day := (TO_DATE('01/01/1995', 'DD/MM/YYYY') +(l_no - 1))
838 +(r_check_2.end_time - TRUNC(r_check_2.end_time));
839
840 IF (
841 ((x_start_date_time <= l_day) OR(x_end_date_time <= l_day))
842 AND (r_check_2.shift_construct_id <> x_shift_construct_id)
843 ) THEN
844 l_error := 0;
845 END IF;
846 END IF;
847
848 CLOSE c_check_2;
849 END IF;
850 END IF;
851
852 IF (l_error = 0) THEN
853 RETURN(FALSE);
854 ELSE
855 RETURN(TRUE);
856 END IF;
857 END overlap_shift;
858 /*************************************************************************/
859 /* FUNCTION overlap_shift(X_SHIFT_ID IN NUMBER, X_UNIT_OF_TIME_VALUE IN CHAR,
860 X_BEGIN_TIME IN DATE, X_END_TIME IN DATE,
861 X_START_DATE_ACTIVE IN DATE, X_END_DATE_ACTIVE IN DATE) RETURN boolean IS
862 cursor dup is
863 select shift_id, unit_of_time_value, begin_time, end_time, start_date_active, end_date_active
864 from jtf_cal_shift_constructs
865 where shift_id = X_SHIFT_ID;
866
867 BEGIN
868 -- Shift is unique
869 for dup_rec in dup loop
870 IF (dup_rec.SHIFT_ID = X_SHIFT_ID
871 AND dup_rec.unit_of_time_value = X_UNIT_OF_TIME_VALUE
872 AND dup_rec.begin_time BETWEEN X_BEGIN_TIME AND X_END_TIME
873 AND dup_rec.start_date_active = X_START_DATE_ACTIVE
874 AND dup_rec.end_date_active = X_END_DATE_ACTIVE) THEN
875
876 return(FALSE);
877 ELSE
878 return(TRUE);
879 END IF;
880 end loop;
881 END overlap_shift;
882 */
883 END jtf_cal_shift_constructs_pkg;