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