[Home] [Help]
PACKAGE BODY: APPS.JTF_CAL_SHIFTS_PKG
Source
1 package body JTF_CAL_SHIFTS_PKG as
2 /* $Header: jtfclshb.pls 115.20 2003/06/09 11:34:24 abraina ship $ */
3 procedure INSERT_ROW (
4 X_ERROR out NOCOPY VARCHAR2,
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_SHIFT_ID in out NOCOPY NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_START_DATE_ACTIVE in DATE,
9 X_END_DATE_ACTIVE in DATE,
10 X_ATTRIBUTE1 in VARCHAR2,
11 X_ATTRIBUTE2 in VARCHAR2,
12 X_ATTRIBUTE3 in VARCHAR2,
13 X_ATTRIBUTE4 in VARCHAR2,
14 X_ATTRIBUTE5 in VARCHAR2,
15 X_ATTRIBUTE6 in VARCHAR2,
16 X_ATTRIBUTE7 in VARCHAR2,
17 X_ATTRIBUTE8 in VARCHAR2,
18 X_ATTRIBUTE9 in VARCHAR2,
19 X_ATTRIBUTE10 in VARCHAR2,
20 X_ATTRIBUTE11 in VARCHAR2,
21 X_ATTRIBUTE12 in VARCHAR2,
22 X_ATTRIBUTE13 in VARCHAR2,
23 X_ATTRIBUTE14 in VARCHAR2,
24 X_ATTRIBUTE15 in VARCHAR2,
25 X_ATTRIBUTE_CATEGORY in VARCHAR2,
26 X_SHIFT_NAME in VARCHAR2,
27 X_DESCRIPTION in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34
35 v_error CHAR := 'N';
36 v_shift_id NUMBER;
37 cursor C is select ROWID from JTF_CAL_SHIFTS_B
38 where SHIFT_ID = X_SHIFT_ID;
39 begin
40 fnd_msg_pub.initialize;
41 IF JTF_CAL_SHIFTS_PKG.NOT_NULL(X_START_DATE_ACTIVE) = FALSE THEN
42 --fnd_message.set_name('JTF', 'START_DATE CANNOT BE NULL');
43 --app_exception.raise_exception;
44 fnd_message.set_name('JTF', 'JTF_CAL_START_DATE');
45 fnd_msg_pub.add;
46
47 v_error := 'Y';
48 END IF;
49
50
51 IF JTF_CAL_SHIFTS_PKG.END_GREATER_THAN_BEGIN(X_START_DATE_ACTIVE, X_END_DATE_ACTIVE) = FALSE THEN
52 --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
53 --app_exception.raise_exception;
54 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
55 fnd_message.set_token('P_Start_Date', X_START_DATE_ACTIVE);
56 fnd_message.set_token('P_End_Date', X_END_DATE_ACTIVE);
57 fnd_msg_pub.add;
58 v_error := 'Y';
59 END IF;
60
61 -- Code Added BY Venkata Putcha for UTF8 Compliance
62 -- Update the max length from 80 to 240 for bug # 2863830 By A.Raina
63 if Length(X_SHIFT_NAME) > 240 then
64 fnd_message.set_name('JTF', 'JTF_CAL_UTF8_COMP');
65 fnd_message.set_token('P_NAME', X_SHIFT_NAME);
66 fnd_msg_pub.add;
67 v_error := 'Y';
68 end if;
69
70 -- Update the max length from 80 to 240 for bug # 2863830 By A.Raina
71 if Length(X_DESCRIPTION) > 240 then
72 fnd_message.set_name('JTF', 'JTF_CAL_UTF8_COMP');
73 fnd_message.set_token('P_NAME', X_DESCRIPTION);
74 fnd_msg_pub.add;
75 v_error := 'Y';
76 end if;
77 -- Up to Here
78
79 IF v_error = 'Y' THEN
80 X_ERROR := 'Y';
81 return;
82 ELSE
83
84
85 SELECT JTF_CAL_SHIFTS_S.nextval
86 INTO v_shift_id
87 FROM dual;
88
89 X_SHIFT_ID := v_shift_id;
90
91 --commented the user hook code as this is not to be implemented
92 -- start of comment
93 /*
94
95 -- Add User Hook Check for INSERT by Jane Wang on 01/25/02
96 IF jtf_usr_hks.ok_to_execute(
97 'JTF_CAL_SHIFTS_PKG',
98 'INSERT_ROW',
99 'B',
100 'C')
101 THEN
102 JTF_CAL_SHIFT_CUHK.insert_shift_pre
103 (
104 X_ERROR => X_ERROR,
105 X_ROWID => X_ROWID,
106 X_SHIFT_ID => X_SHIFT_ID,
107 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
108 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
109 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
110 X_ATTRIBUTE1 => X_ATTRIBUTE1,
111 X_ATTRIBUTE2 => X_ATTRIBUTE2,
112 X_ATTRIBUTE3 => X_ATTRIBUTE3,
113 X_ATTRIBUTE4 => X_ATTRIBUTE4,
114 X_ATTRIBUTE5 => X_ATTRIBUTE5,
115 X_ATTRIBUTE6 => X_ATTRIBUTE6,
116 X_ATTRIBUTE7 => X_ATTRIBUTE7,
117 X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
118 X_ATTRIBUTE9 => X_ATTRIBUTE9,
119 X_ATTRIBUTE10 => X_ATTRIBUTE10,
120 X_ATTRIBUTE11 => X_ATTRIBUTE11,
121 X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
122 X_ATTRIBUTE13 => X_ATTRIBUTE13,
123 X_ATTRIBUTE14 => X_ATTRIBUTE14,
124 X_ATTRIBUTE15 => X_ATTRIBUTE15,
125 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
126 X_SHIFT_NAME => X_SHIFT_NAME,
127 X_DESCRIPTION => X_DESCRIPTION,
128 X_CREATION_DATE => X_CREATION_DATE,
129 X_CREATED_BY => X_CREATED_BY,
130 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
131 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
132 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
133 );
134
135 END IF; -- End of User Hook Check for INSERT
136 */
137 -- End of Comment
138
139 insert into JTF_CAL_SHIFTS_B (
140 OBJECT_VERSION_NUMBER,
141 SHIFT_ID,
142 START_DATE_ACTIVE,
143 END_DATE_ACTIVE,
144 ATTRIBUTE1,
145 ATTRIBUTE2,
146 ATTRIBUTE3,
147 ATTRIBUTE4,
148 ATTRIBUTE5,
149 ATTRIBUTE6,
150 ATTRIBUTE7,
151 ATTRIBUTE8,
152 ATTRIBUTE9,
153 ATTRIBUTE10,
154 ATTRIBUTE11,
155 ATTRIBUTE12,
156 ATTRIBUTE13,
157 ATTRIBUTE14,
158 ATTRIBUTE15,
159 ATTRIBUTE_CATEGORY,
160 CREATION_DATE,
161 CREATED_BY,
162 LAST_UPDATE_DATE,
163 LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN
165 ) values (
166 1,
167 v_shift_id,
168 X_START_DATE_ACTIVE,
169 X_END_DATE_ACTIVE,
170 X_ATTRIBUTE1,
171 X_ATTRIBUTE2,
172 X_ATTRIBUTE3,
173 X_ATTRIBUTE4,
174 X_ATTRIBUTE5,
175 X_ATTRIBUTE6,
176 X_ATTRIBUTE7,
177 X_ATTRIBUTE8,
178 X_ATTRIBUTE9,
179 X_ATTRIBUTE10,
180 X_ATTRIBUTE11,
181 X_ATTRIBUTE12,
182 X_ATTRIBUTE13,
183 X_ATTRIBUTE14,
184 X_ATTRIBUTE15,
185 X_ATTRIBUTE_CATEGORY,
186 SYSDATE,
187 FND_GLOBAL.USER_ID,
188 SYSDATE,
189 FND_GLOBAL.USER_ID,
190 NULL
191 );
192
193 insert into JTF_CAL_SHIFTS_TL (
194 SHIFT_ID,
195 SHIFT_NAME,
196 DESCRIPTION,
197 CREATED_BY,
198 CREATION_DATE,
199 LAST_UPDATED_BY,
200 LAST_UPDATE_DATE,
201 LAST_UPDATE_LOGIN,
202 LANGUAGE,
203 SOURCE_LANG
204 ) select
205 v_shift_id,
206 X_SHIFT_NAME,
207 X_DESCRIPTION,
208 FND_GLOBAL.USER_ID,
209 SYSDATE,
210 FND_GLOBAL.USER_ID,
211 SYSDATE,
212 NULL,
213 L.LANGUAGE_CODE,
214 userenv('LANG')
215 from FND_LANGUAGES L
216 where L.INSTALLED_FLAG in ('I', 'B')
217 and not exists
218 (select NULL
219 from JTF_CAL_SHIFTS_TL T
220 where T.SHIFT_ID = X_SHIFT_ID
221 and T.LANGUAGE = L.LANGUAGE_CODE);
222 /*
223 open c;
224 fetch c into X_ROWID;
225 if (c%notfound) then
226 close c;
227 raise no_data_found;
228 end if;
229 close c;
230 */
231 END IF;
232 end INSERT_ROW;
233
234 procedure LOCK_ROW (
235 X_SHIFT_ID in NUMBER,
236 X_OBJECT_VERSION_NUMBER in NUMBER,
237 X_START_DATE_ACTIVE in DATE,
238 X_END_DATE_ACTIVE in DATE,
239 X_ATTRIBUTE1 in VARCHAR2,
240 X_ATTRIBUTE2 in VARCHAR2,
241 X_ATTRIBUTE3 in VARCHAR2,
242 X_ATTRIBUTE4 in VARCHAR2,
243 X_ATTRIBUTE5 in VARCHAR2,
244 X_ATTRIBUTE6 in VARCHAR2,
245 X_ATTRIBUTE7 in VARCHAR2,
246 X_ATTRIBUTE8 in VARCHAR2,
247 X_ATTRIBUTE9 in VARCHAR2,
248 X_ATTRIBUTE10 in VARCHAR2,
249 X_ATTRIBUTE11 in VARCHAR2,
250 X_ATTRIBUTE12 in VARCHAR2,
251 X_ATTRIBUTE13 in VARCHAR2,
252 X_ATTRIBUTE14 in VARCHAR2,
253 X_ATTRIBUTE15 in VARCHAR2,
254 X_ATTRIBUTE_CATEGORY in VARCHAR2,
255 X_SHIFT_NAME in VARCHAR2,
256 X_DESCRIPTION in VARCHAR2
257 ) is
258 cursor c is select
259 OBJECT_VERSION_NUMBER,
260 START_DATE_ACTIVE,
261 END_DATE_ACTIVE,
262 ATTRIBUTE1,
263 ATTRIBUTE2,
264 ATTRIBUTE3,
265 ATTRIBUTE4,
266 ATTRIBUTE5,
267 ATTRIBUTE6,
268 ATTRIBUTE7,
269 ATTRIBUTE8,
270 ATTRIBUTE9,
271 ATTRIBUTE10,
272 ATTRIBUTE11,
273 ATTRIBUTE12,
274 ATTRIBUTE13,
275 ATTRIBUTE14,
276 ATTRIBUTE15,
277 ATTRIBUTE_CATEGORY
278 from JTF_CAL_SHIFTS_B
279 where SHIFT_ID = X_SHIFT_ID
280 for update of SHIFT_ID nowait;
281 recinfo c%rowtype;
282
283 cursor c1 is select
284 SHIFT_NAME,
285 DESCRIPTION,
286 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
287 from JTF_CAL_SHIFTS_TL
288 where SHIFT_ID = X_SHIFT_ID
289 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
290 for update of SHIFT_ID nowait;
291 begin
292 open c;
293 fetch c into recinfo;
294 if (c%notfound) then
295 close c;
296 fnd_message.set_name('JTF', 'FORM_RECORD_DELETED');
297 app_exception.raise_exception;
298 end if;
299 close c;
300 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
301 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
302 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
303 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
304 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
305 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
306 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
307 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
308 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
309 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
310 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
311 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
312 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
313 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
314 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
315 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
316 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
317 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
318 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
319 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
320 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
321 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
322 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
323 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
324 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
325 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
326 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
327 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
328 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
329 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
330 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
331 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
332 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
333 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
334 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
335 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
336 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
337 ) then
338 null;
339 else
340 fnd_message.set_name('JTF', 'FORM_RECORD_CHANGED');
341 app_exception.raise_exception;
342 end if;
343
344 for tlinfo in c1 loop
345 if (tlinfo.BASELANG = 'Y') then
346 if ( (tlinfo.SHIFT_NAME = X_SHIFT_NAME)
347 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
348 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
349 ) then
350 null;
351 else
352 fnd_message.set_name('JTF', 'FORM_RECORD_CHANGED');
353 app_exception.raise_exception;
354 end if;
355 end if;
356 end loop;
357 return;
358 end LOCK_ROW;
359
360 procedure UPDATE_ROW (
361 X_ERROR out NOCOPY VARCHAR2,
362 X_SHIFT_ID in NUMBER,
363 X_OBJECT_VERSION_NUMBER in OUT NOCOPY NUMBER,
364 X_START_DATE_ACTIVE in DATE,
365 X_END_DATE_ACTIVE in DATE,
366 X_ATTRIBUTE1 in VARCHAR2,
367 X_ATTRIBUTE2 in VARCHAR2,
368 X_ATTRIBUTE3 in VARCHAR2,
369 X_ATTRIBUTE4 in VARCHAR2,
370 X_ATTRIBUTE5 in VARCHAR2,
371 X_ATTRIBUTE6 in VARCHAR2,
372 X_ATTRIBUTE7 in VARCHAR2,
373 X_ATTRIBUTE8 in VARCHAR2,
374 X_ATTRIBUTE9 in VARCHAR2,
375 X_ATTRIBUTE10 in VARCHAR2,
376 X_ATTRIBUTE11 in VARCHAR2,
377 X_ATTRIBUTE12 in VARCHAR2,
378 X_ATTRIBUTE13 in VARCHAR2,
379 X_ATTRIBUTE14 in VARCHAR2,
380 X_ATTRIBUTE15 in VARCHAR2,
384 X_LAST_UPDATE_DATE in DATE,
381 X_ATTRIBUTE_CATEGORY in VARCHAR2,
382 X_SHIFT_NAME in VARCHAR2,
383 X_DESCRIPTION in VARCHAR2,
385 X_LAST_UPDATED_BY in NUMBER,
386 X_LAST_UPDATE_LOGIN in NUMBER
387 ) is
388
389 v_error CHAR := 'N';
390 begin
391 fnd_msg_pub.initialize;
392 IF JTF_CAL_SHIFTS_PKG.NOT_NULL(X_START_DATE_ACTIVE) = FALSE THEN
393 --fnd_message.set_name('JTF', 'START_DATE CANNOT BE NULL');
394 --app_exception.raise_exception;
395 fnd_message.set_name('JTF', 'JTF_CAL_START_DATE');
396 fnd_msg_pub.add;
397
398 v_error := 'Y';
399 END IF;
400
401
402 IF JTF_CAL_SHIFTS_PKG.END_GREATER_THAN_BEGIN(X_START_DATE_ACTIVE, X_END_DATE_ACTIVE) = FALSE THEN
403 --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
404 --app_exception.raise_exception;
405 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
406 fnd_message.set_token('P_Start_Date', X_START_DATE_ACTIVE);
407 fnd_message.set_token('P_End_Date', X_END_DATE_ACTIVE);
408 fnd_msg_pub.add;
409 v_error := 'Y';
410 END IF;
411
412 IF v_error = 'Y' THEN
413 X_ERROR := 'Y';
414 return;
415 ELSE
416 X_ERROR := 'N';
417 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
418
419 --commented the user hook code as this is not to be implemented
420 -- start of comment
421 /*
422 -- Add User Hook Check for UPDATE by Jane Wang on 01/25/02
423
424 IF jtf_usr_hks.ok_to_execute(
425 'JTF_CAL_SHIFTS_PKG',
426 'UPDATE_ROW',
427 'B',
428 'C')
429 THEN
430 JTF_CAL_SHIFT_CUHK.update_shift_pre
431 (X_ERROR => X_ERROR,
432 X_SHIFT_ID => X_SHIFT_ID,
433 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
434 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
435 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
436 X_ATTRIBUTE1 => X_ATTRIBUTE1,
437 X_ATTRIBUTE2 => X_ATTRIBUTE2,
438 X_ATTRIBUTE3 => X_ATTRIBUTE3,
439 X_ATTRIBUTE4 => X_ATTRIBUTE4,
440 X_ATTRIBUTE5 => X_ATTRIBUTE5,
441 X_ATTRIBUTE6 => X_ATTRIBUTE6,
442 X_ATTRIBUTE7 => X_ATTRIBUTE7,
443 X_ATTRIBUTE8 => X_ATTRIBUTE8,
444 X_ATTRIBUTE9 => X_ATTRIBUTE9,
445 X_ATTRIBUTE10 => X_ATTRIBUTE10,
446 X_ATTRIBUTE11 => X_ATTRIBUTE11,
447 X_ATTRIBUTE12 => X_ATTRIBUTE12,
448 X_ATTRIBUTE13 => X_ATTRIBUTE13,
449 X_ATTRIBUTE14 => X_ATTRIBUTE14,
450 X_ATTRIBUTE15 => X_ATTRIBUTE15,
451 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
452 X_SHIFT_NAME => X_SHIFT_NAME,
453 X_DESCRIPTION => X_DESCRIPTION,
454 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
455 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
456 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
457 );
458
459 END IF; -- End of User Hook Check for UPDATE
460
461 */
462 -- End of comment
463
464 update JTF_CAL_SHIFTS_B set
465 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
466 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
467 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
468 ATTRIBUTE1 = X_ATTRIBUTE1,
469 ATTRIBUTE2 = X_ATTRIBUTE2,
470 ATTRIBUTE3 = X_ATTRIBUTE3,
471 ATTRIBUTE4 = X_ATTRIBUTE4,
472 ATTRIBUTE5 = X_ATTRIBUTE5,
473 ATTRIBUTE6 = X_ATTRIBUTE6,
474 ATTRIBUTE7 = X_ATTRIBUTE7,
475 ATTRIBUTE8 = X_ATTRIBUTE8,
476 ATTRIBUTE9 = X_ATTRIBUTE9,
477 ATTRIBUTE10 = X_ATTRIBUTE10,
478 ATTRIBUTE11 = X_ATTRIBUTE11,
479 ATTRIBUTE12 = X_ATTRIBUTE12,
480 ATTRIBUTE13 = X_ATTRIBUTE13,
481 ATTRIBUTE14 = X_ATTRIBUTE14,
482 ATTRIBUTE15 = X_ATTRIBUTE15,
483 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
484 LAST_UPDATE_DATE = SYSDATE,
485 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
486 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
487 where SHIFT_ID = X_SHIFT_ID;
488
489 if (sql%notfound) then
490 raise no_data_found;
491 end if;
492
493 update JTF_CAL_SHIFTS_TL set
494 SHIFT_NAME = X_SHIFT_NAME,
495 DESCRIPTION = X_DESCRIPTION,
496 LAST_UPDATE_DATE = SYSDATE,
497 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
498 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
499 SOURCE_LANG = userenv('LANG')
500 where SHIFT_ID = X_SHIFT_ID
501 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
502
503 if (sql%notfound) then
504 raise no_data_found;
505 end if;
506 END IF;
507 end UPDATE_ROW;
508
509 Procedure TRANSLATE_ROW
510 (X_SHIFT_ID in number,
511 X_SHIFT_NAME in varchar2,
512 X_DESCRIPTION in varchar2,
513 X_LAST_UPDATE_DATE in date,
514 X_LAST_UPDATED_BY in number,
515 X_LAST_UPDATE_LOGIN in number)
516 is
517 begin
518
519 Update JTF_CAL_SHIFTS_TL set
520 shift_name = nvl(X_SHIFT_NAME,shift_name),
521 description = nvl(X_DESCRIPTION,description),
522 last_update_date = nvl(x_last_update_date,sysdate),
523 last_updated_by = x_last_updated_by,
527 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
524 last_update_login = 0,
525 source_lang = userenv('LANG')
526 where shift_id = X_SHIFT_ID
528
529 end TRANSLATE_ROW;
530
531 procedure DELETE_ROW (
532 X_SHIFT_ID in NUMBER
533 ) is
534 begin
535
536 --commented the user hook code as this is not to be implemented
537 -- start of comment
538 /*
539
540 -- Add User Hook Check for DELETE by Jane Wang on 01/25/02
541
542 IF jtf_usr_hks.ok_to_execute(
543 'JTF_CAL_SHIFTS_PKG',
544 'DELETE_ROW',
545 'B',
546 'C')
547 THEN
548 JTF_CAL_SHIFT_CUHK.delete_shift_pre
549 (
550 X_SHIFT_ID => X_SHIFT_ID
551 );
552 END IF; -- End of User Hook for DELETE
553 */
554 -- End of comment
555
556 delete from JTF_CAL_SHIFTS_TL
557 where SHIFT_ID = X_SHIFT_ID;
558
559 if (sql%notfound) then
560 raise no_data_found;
561 end if;
562
563 delete from JTF_CAL_SHIFTS_B
564 where SHIFT_ID = X_SHIFT_ID;
565
566 if (sql%notfound) then
567 raise no_data_found;
568 end if;
569 end DELETE_ROW;
570
571 procedure ADD_LANGUAGE
572 is
573 begin
574 delete from JTF_CAL_SHIFTS_TL T
575 where not exists
576 (select NULL
577 from JTF_CAL_SHIFTS_B B
578 where B.SHIFT_ID = T.SHIFT_ID
579 );
580
581 update JTF_CAL_SHIFTS_TL T set (
582 SHIFT_NAME,
583 DESCRIPTION
584 ) = (select
585 B.SHIFT_NAME,
586 B.DESCRIPTION
587 from JTF_CAL_SHIFTS_TL B
588 where B.SHIFT_ID = T.SHIFT_ID
589 and B.LANGUAGE = T.SOURCE_LANG)
590 where (
591 T.SHIFT_ID,
592 T.LANGUAGE
593 ) in (select
594 SUBT.SHIFT_ID,
595 SUBT.LANGUAGE
596 from JTF_CAL_SHIFTS_TL SUBB, JTF_CAL_SHIFTS_TL SUBT
597 where SUBB.SHIFT_ID = SUBT.SHIFT_ID
598 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
599 and (SUBB.SHIFT_NAME <> SUBT.SHIFT_NAME
600 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
601 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
602 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
603 ));
604
605 insert into JTF_CAL_SHIFTS_TL (
606 SHIFT_ID,
607 SHIFT_NAME,
608 DESCRIPTION,
609 CREATED_BY,
610 CREATION_DATE,
611 LAST_UPDATED_BY,
612 LAST_UPDATE_DATE,
613 LAST_UPDATE_LOGIN,
614 LANGUAGE,
615 SOURCE_LANG
616 ) select
617 B.SHIFT_ID,
618 B.SHIFT_NAME,
619 B.DESCRIPTION,
620 B.CREATED_BY,
621 B.CREATION_DATE,
622 B.LAST_UPDATED_BY,
623 B.LAST_UPDATE_DATE,
624 B.LAST_UPDATE_LOGIN,
625 L.LANGUAGE_CODE,
626 B.SOURCE_LANG
627 from JTF_CAL_SHIFTS_TL B, FND_LANGUAGES L
628 where L.INSTALLED_FLAG in ('I', 'B')
629 and B.LANGUAGE = userenv('LANG')
630 and not exists
631 (select NULL
632 from JTF_CAL_SHIFTS_TL T
633 where T.SHIFT_ID = B.SHIFT_ID
634 and T.LANGUAGE = L.LANGUAGE_CODE);
635 end ADD_LANGUAGE;
636 /*************************************************************************/
637 FUNCTION not_null(column_to_check IN CHAR) RETURN boolean IS
638 BEGIN
639 IF column_to_check IS NULL THEN
640 return(FALSE);
641 ELSE
642 return(TRUE);
643 END IF;
644 END;
645 /*************************************************************************/
646 FUNCTION end_greater_than_begin(start_date IN DATE, end_date IN DATE) RETURN boolean IS
647 BEGIN
648 IF start_date > end_date THEN
649 return(FALSE);
650 ELSE
651 return(TRUE);
652 END IF;
653 END;
654 end JTF_CAL_SHIFTS_PKG;