[Home] [Help]
PACKAGE BODY: APPS.JTF_CALENDARS_PKG
Source
1 package body JTF_CALENDARS_PKG as
2 /* $Header: jtfcldcb.pls 120.8 2011/03/22 06:28:15 anangupt ship $ */
3 procedure INSERT_ROW (
4 X_ERROR out NOCOPY VARCHAR2,
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_CALENDAR_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_CALENDAR_TYPE in VARCHAR2,
27 X_CALENDAR_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 cursor C is select ROWID from JTF_CALENDARS_B
36 where CALENDAR_ID = X_CALENDAR_ID;
37
38 v_error CHAR := 'N';
39 v_calendar_id NUMBER;
40
41 temp_count number := 0;
42 begin
43 fnd_msg_pub.initialize;
44
45 IF JTF_CALENDARS_PKG.NOT_NULL(X_CALENDAR_NAME) = FALSE THEN
46 fnd_message.set_name('JTF', 'JTF_CAL_CALENDAR_NAME');
47 --fnd_message.set_token('P_NAME', nvl(X_CALENDAR_NAME,'Calendar Name '));
48 fnd_msg_pub.add;
49 v_error := 'Y';
50 END IF;
51
52 IF JTF_CALENDARS_PKG.NOT_NULL(X_CALENDAR_TYPE) = FALSE THEN
53 fnd_message.set_name('JTF', 'JTF_CAL_CALENDAR_TYPE');
54 --fnd_message.set_token('P_NAME', nvl(X_CALENDAR_TYPE,'Calendar Type '));
55 fnd_msg_pub.add;
56 v_error := 'Y';
57 END IF;
58
59
60 select count(*) into temp_count
61 FROM JTF_CALENDARS_VL
62 WHERE upper(calendar_name) = upper(X_CALENDAR_NAME)
63 AND UPPER(calendar_type) = upper(X_CALENDAR_TYPE)
64 --- Added for bug 5123027 by abraina
65 and (
66 start_date_active <= nvl(X_END_DATE_ACTIVE,to_date('12/31/9999','mm/dd/yyyy'))
67 and
68 nvl(end_date_active,to_date('12/31/9999','mm/dd/yyyy')) >= X_START_DATE_ACTIVE
69 ) ;
70
71 IF temp_count > 0 THEN
72 fnd_message.set_name('JTF', 'JTF_CAL_DUP_NAME');
73 fnd_message.set_token('P_NAME', X_CALENDAR_NAME);
74 fnd_msg_pub.add;
75 v_error := 'Y';
76 END IF;
77
78
79 IF JTF_CALENDARS_PKG.NOT_NULL(X_START_DATE_ACTIVE) = FALSE THEN
80
81 fnd_message.set_name('JTF', 'JTF_CAL_START_DATE');
82 fnd_msg_pub.add;
83
84 v_error := 'Y';
85 END IF;
86
87
88 IF JTF_CALENDARS_PKG.END_GREATER_THAN_BEGIN(X_START_DATE_ACTIVE, X_END_DATE_ACTIVE) = FALSE THEN
89 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
90 fnd_message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>X_START_DATE_ACTIVE,calendar_aware=>fnd_date.calendar_aware));
91 fnd_message.set_token('P_End_Date', fnd_date.date_to_chardate(dateval=>X_END_DATE_ACTIVE,calendar_aware=>fnd_date.calendar_aware));
92 fnd_msg_pub.add;
93 v_error := 'Y';
94 END IF;
95
96 IF v_error = 'Y' THEN
97 X_ERROR := 'Y';
98 return;
99 ELSE
100 SELECT JTF_CALENDARS_S.nextval
101 INTO v_calendar_id
102 FROM dual;
103
104 X_CALENDAR_ID := v_calendar_id;
105
106 insert into JTF_CALENDARS_B (
107 OBJECT_VERSION_NUMBER,
108 CALENDAR_ID,
109 START_DATE_ACTIVE,
110 END_DATE_ACTIVE,
111 ATTRIBUTE1,
112 ATTRIBUTE2,
113 ATTRIBUTE3,
114 ATTRIBUTE4,
115 ATTRIBUTE5,
116 ATTRIBUTE6,
117 ATTRIBUTE7,
118 ATTRIBUTE8,
119 ATTRIBUTE9,
120 ATTRIBUTE10,
121 ATTRIBUTE11,
122 ATTRIBUTE12,
123 ATTRIBUTE13,
124 ATTRIBUTE14,
125 ATTRIBUTE15,
126 ATTRIBUTE_CATEGORY,
127 CALENDAR_TYPE,
128 CREATION_DATE,
129 CREATED_BY,
130 LAST_UPDATE_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_LOGIN
133 ) values (
134 nvl(X_OBJECT_VERSION_NUMBER,1),
135 v_calendar_id,
136 X_START_DATE_ACTIVE,
137 X_END_DATE_ACTIVE,
138 X_ATTRIBUTE1,
139 X_ATTRIBUTE2,
140 X_ATTRIBUTE3,
141 X_ATTRIBUTE4,
142 X_ATTRIBUTE5,
143 X_ATTRIBUTE6,
144 X_ATTRIBUTE7,
145 X_ATTRIBUTE8,
146 X_ATTRIBUTE9,
147 X_ATTRIBUTE10,
148 X_ATTRIBUTE11,
149 X_ATTRIBUTE12,
150 X_ATTRIBUTE13,
151 X_ATTRIBUTE14,
152 X_ATTRIBUTE15,
153 X_ATTRIBUTE_CATEGORY,
154 X_CALENDAR_TYPE,
155 sysdate,
156 FND_GLOBAL.USER_ID,
157 sysdate,
158 FND_GLOBAL.USER_ID,
159 NULL
160 );
161
162 insert into JTF_CALENDARS_TL (
163 CALENDAR_ID,
164 CALENDAR_NAME,
165 DESCRIPTION,
166 CREATED_BY,
167 CREATION_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_DATE,
170 LAST_UPDATE_LOGIN,
171 LANGUAGE,
172 SOURCE_LANG
173 ) select
174 v_calendar_id,
175 X_CALENDAR_NAME,
176 X_DESCRIPTION,
177 FND_GLOBAL.USER_ID,
178 sysdate,
179 FND_GLOBAL.USER_ID,
180 sysdate,
181 NULL,
182 L.LANGUAGE_CODE,
183 userenv('LANG')
184 from FND_LANGUAGES L
185 where L.INSTALLED_FLAG in ('I', 'B')
186 and not exists
187 (select NULL
188 from JTF_CALENDARS_TL T
189 where T.CALENDAR_ID = X_CALENDAR_ID
190 and T.LANGUAGE = L.LANGUAGE_CODE);
191 /*
192 open c;
193 fetch c into X_ROWID;
194 if (c%notfound) then
195 close c;
196 raise no_data_found;
197 end if;
198 close c;
199 */
200 END IF;
201 end INSERT_ROW;
202
203 procedure LOCK_ROW (
204 X_CALENDAR_ID in NUMBER,
205 X_OBJECT_VERSION_NUMBER in NUMBER,
206 X_START_DATE_ACTIVE in DATE,
207 X_END_DATE_ACTIVE in DATE,
208 X_ATTRIBUTE1 in VARCHAR2,
209 X_ATTRIBUTE2 in VARCHAR2,
210 X_ATTRIBUTE3 in VARCHAR2,
211 X_ATTRIBUTE4 in VARCHAR2,
212 X_ATTRIBUTE5 in VARCHAR2,
213 X_ATTRIBUTE6 in VARCHAR2,
214 X_ATTRIBUTE7 in VARCHAR2,
215 X_ATTRIBUTE8 in VARCHAR2,
216 X_ATTRIBUTE9 in VARCHAR2,
220 X_ATTRIBUTE13 in VARCHAR2,
217 X_ATTRIBUTE10 in VARCHAR2,
218 X_ATTRIBUTE11 in VARCHAR2,
219 X_ATTRIBUTE12 in VARCHAR2,
221 X_ATTRIBUTE14 in VARCHAR2,
222 X_ATTRIBUTE15 in VARCHAR2,
223 X_ATTRIBUTE_CATEGORY in VARCHAR2,
224 X_CALENDAR_TYPE in VARCHAR2,
225 X_CALENDAR_NAME in VARCHAR2,
226 X_DESCRIPTION in VARCHAR2
227 ) is
228 cursor c is select
229 OBJECT_VERSION_NUMBER,
230 START_DATE_ACTIVE,
231 END_DATE_ACTIVE,
232 ATTRIBUTE1,
233 ATTRIBUTE2,
234 ATTRIBUTE3,
235 ATTRIBUTE4,
236 ATTRIBUTE5,
237 ATTRIBUTE6,
238 ATTRIBUTE7,
239 ATTRIBUTE8,
240 ATTRIBUTE9,
241 ATTRIBUTE10,
242 ATTRIBUTE11,
243 ATTRIBUTE12,
244 ATTRIBUTE13,
245 ATTRIBUTE14,
246 ATTRIBUTE15,
247 ATTRIBUTE_CATEGORY,
248 CALENDAR_TYPE
249 from JTF_CALENDARS_B
250 where CALENDAR_ID = X_CALENDAR_ID
251 for update of CALENDAR_ID nowait;
252 recinfo c%rowtype;
253
254 cursor c1 is select
255 CALENDAR_NAME,
256 DESCRIPTION,
257 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
258 from JTF_CALENDARS_TL
259 where CALENDAR_ID = X_CALENDAR_ID
260 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
261 for update of CALENDAR_ID nowait;
262 begin
263 open c;
264 fetch c into recinfo;
265 if (c%notfound) then
266 close c;
267 fnd_message.set_name('JTF', 'FORM_RECORD_DELETED');
268 app_exception.raise_exception;
269 end if;
270 close c;
271 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
272 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
273 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
274 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
275 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
276 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
277 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
278 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
279 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
280 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
281 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
282 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
283 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
284 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
285 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
286 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
287 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
288 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
289 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
290 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
291 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
292 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
293 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
294 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
295 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
296 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
297 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
298 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
299 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
300 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
301 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
302 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
303 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
304 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
305 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
306 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
307 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
308 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
309 AND ((recinfo.CALENDAR_TYPE = X_CALENDAR_TYPE)
310 OR ((recinfo.CALENDAR_TYPE is null) AND (X_CALENDAR_TYPE is null)))
311 ) then
312 null;
313 else
314 fnd_message.set_name('JTF', 'FORM_RECORD_CHANGED');
315 app_exception.raise_exception;
316 end if;
317
318 for tlinfo in c1 loop
319 if (tlinfo.BASELANG = 'Y') then
320 if ( ((tlinfo.CALENDAR_NAME = X_CALENDAR_NAME)
321 OR ((tlinfo.CALENDAR_NAME is null) AND (X_CALENDAR_NAME is null)))
322 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
323 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
324 ) then
325 null;
326 else
327 fnd_message.set_name('JTF', 'FORM_RECORD_CHANGED');
328 app_exception.raise_exception;
329 end if;
330 end if;
331 end loop;
332 return;
333 end LOCK_ROW;
334
335 procedure UPDATE_ROW (
336 X_ERROR out NOCOPY VARCHAR2,
337 X_CALENDAR_ID in NUMBER,
338 X_OBJECT_VERSION_NUMBER in OUT NOCOPY NUMBER,
339 X_START_DATE_ACTIVE in DATE,
340 X_END_DATE_ACTIVE in DATE,
341 X_ATTRIBUTE1 in VARCHAR2,
342 X_ATTRIBUTE2 in VARCHAR2,
343 X_ATTRIBUTE3 in VARCHAR2,
344 X_ATTRIBUTE4 in VARCHAR2,
345 X_ATTRIBUTE5 in VARCHAR2,
346 X_ATTRIBUTE6 in VARCHAR2,
347 X_ATTRIBUTE7 in VARCHAR2,
348 X_ATTRIBUTE8 in VARCHAR2,
349 X_ATTRIBUTE9 in VARCHAR2,
350 X_ATTRIBUTE10 in VARCHAR2,
351 X_ATTRIBUTE11 in VARCHAR2,
352 X_ATTRIBUTE12 in VARCHAR2,
356 X_ATTRIBUTE_CATEGORY in VARCHAR2,
353 X_ATTRIBUTE13 in VARCHAR2,
354 X_ATTRIBUTE14 in VARCHAR2,
355 X_ATTRIBUTE15 in VARCHAR2,
357 X_CALENDAR_TYPE in VARCHAR2,
358 X_CALENDAR_NAME in VARCHAR2,
359 X_DESCRIPTION in VARCHAR2,
360 X_LAST_UPDATE_DATE in DATE,
361 X_LAST_UPDATED_BY in NUMBER,
362 X_LAST_UPDATE_LOGIN in NUMBER
363 ) is
364 v_error CHAR := 'N';
365 -- changed var length from 100 to 240 for bug 2863718 by A.Raina
366 v_desc varchar2(240);
367 v_start_date DATE;
368 temp_count NUMBER := 0;
369
370 begin
371 fnd_msg_pub.initialize;
372
373 IF JTF_CALENDARS_PKG.NOT_NULL(X_CALENDAR_NAME) = FALSE THEN
374 fnd_message.set_name('JTF', 'JTF_CAL_CALENDAR_NAME');
375 --fnd_message.set_token('P_NAME', nvl(X_CALENDAR_NAME,'Calendar Name '));
376 fnd_msg_pub.add;
377 v_error := 'Y';
378 END IF;
379
380 IF JTF_CALENDARS_PKG.NOT_NULL(X_CALENDAR_TYPE) = FALSE THEN
381 fnd_message.set_name('JTF', 'JTF_CAL_CALENDAR_TYPE');
382 --fnd_message.set_token('P_NAME', nvl(X_CALENDAR_TYPE,'Calendar Type '));
383 fnd_msg_pub.add;
384 v_error := 'Y';
385 END IF;
386
387 IF v_error <> 'Y' THEN
388
389 select count(*) into temp_count
390 FROM jtf_calendars_vl
391 WHERE upper(calendar_name) = upper(X_CALENDAR_NAME)
392 AND UPPER(calendar_type) = upper(X_CALENDAR_TYPE)
393 --- Added for bug 5123027 by abraina
394 and (
395 start_date_active <= nvl(X_END_DATE_ACTIVE,to_date('12/31/9999','mm/dd/yyyy'))
396 and
397 nvl(end_date_active,to_date('12/31/9999','mm/dd/yyyy')) >= X_START_DATE_ACTIVE
398 )
399 AND calendar_id <> X_CALENDAR_ID;
400
401
402 IF temp_count > 0 THEN
403 fnd_message.set_name('JTF', 'JTF_CAL_DUP_NAME');
404 fnd_message.set_token('P_NAME', X_CALENDAR_NAME);
405 fnd_msg_pub.add;
406 v_error := 'Y';
407 END IF;
408 END IF;
409 IF JTF_CALENDARS_PKG.NOT_NULL(X_START_DATE_ACTIVE) = FALSE THEN
410
411 fnd_message.set_name('JTF', 'JTF_CAL_START_DATE');
412 fnd_msg_pub.add;
413
414 v_error := 'Y';
415 END IF;
416
417
418 IF JTF_CALENDARS_PKG.END_GREATER_THAN_BEGIN(X_START_DATE_ACTIVE, X_END_DATE_ACTIVE) = FALSE THEN
419 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
420 fnd_message.set_token('P_Start_Date', fnd_date.date_to_chardate(dateval=>X_START_DATE_ACTIVE,calendar_aware=>fnd_date.calendar_aware));
421 fnd_message.set_token('P_End_Date', fnd_date.date_to_chardate(dateval=>X_END_DATE_ACTIVE,calendar_aware=>fnd_date.calendar_aware));
422 fnd_msg_pub.add;
423 v_error := 'Y';
424 END IF;
425
426 IF v_error = 'Y' THEN
427 X_ERROR := 'Y';
428 return;
429 ELSE
430 X_ERROR := 'N';
431 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
432
433 update JTF_CALENDARS_B set
434 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
435 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
436 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
437 ATTRIBUTE1 = X_ATTRIBUTE1,
438 ATTRIBUTE2 = X_ATTRIBUTE2,
439 ATTRIBUTE3 = X_ATTRIBUTE3,
440 ATTRIBUTE4 = X_ATTRIBUTE4,
441 ATTRIBUTE5 = X_ATTRIBUTE5,
442 ATTRIBUTE6 = X_ATTRIBUTE6,
443 ATTRIBUTE7 = X_ATTRIBUTE7,
444 ATTRIBUTE8 = X_ATTRIBUTE8,
445 ATTRIBUTE9 = X_ATTRIBUTE9,
446 ATTRIBUTE10 = X_ATTRIBUTE10,
447 ATTRIBUTE11 = X_ATTRIBUTE11,
448 ATTRIBUTE12 = X_ATTRIBUTE12,
449 ATTRIBUTE13 = X_ATTRIBUTE13,
450 ATTRIBUTE14 = X_ATTRIBUTE14,
451 ATTRIBUTE15 = X_ATTRIBUTE15,
452 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
453 CALENDAR_TYPE = X_CALENDAR_TYPE,
454 LAST_UPDATE_DATE = sysdate,
455 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
456 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
457 where CALENDAR_ID = X_CALENDAR_ID;
458
459 if (sql%notfound) then
460 raise no_data_found;
461 end if;
462
463 update JTF_CALENDARS_TL set
464 CALENDAR_NAME = X_CALENDAR_NAME,
465 DESCRIPTION = X_DESCRIPTION,
466 LAST_UPDATE_DATE = sysdate,
467 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
468 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
469 SOURCE_LANG = userenv('LANG')
470 where CALENDAR_ID = X_CALENDAR_ID
471 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
472
473 if (sql%notfound) then
474 raise no_data_found;
475 end if;
476 END IF;
477 end UPDATE_ROW;
478
479 Procedure TRANSLATE_ROW
480 (X_CALENDAR_ID in number,
481 X_CALENDAR_NAME in varchar2,
482 X_DESCRIPTION in varchar2,
483 X_LAST_UPDATE_DATE in date,
484 X_LAST_UPDATED_BY in number,
485 X_LAST_UPDATE_LOGIN in number)
486 is
487 begin
488
489 Update JTF_CALENDARS_TL set
490 calendar_name = nvl(X_CALENDAR_NAME,calendar_name),
491 description = nvl(X_DESCRIPTION,description),
492 last_update_date = nvl(x_last_update_date,sysdate),
493 last_updated_by = x_last_updated_by,
494 last_update_login = 0,
495 source_lang = userenv('LANG')
496 where calendar_id = X_CALENDAR_ID
497 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
498
499 end TRANSLATE_ROW;
500
501 procedure DELETE_ROW (
502 X_CALENDAR_ID in NUMBER
503 ) is
504 begin
505 delete from JTF_CALENDARS_TL
506 where CALENDAR_ID = X_CALENDAR_ID;
507
508 if (sql%notfound) then
509 raise no_data_found;
510 end if;
511
512 delete from JTF_CALENDARS_B
513 where CALENDAR_ID = X_CALENDAR_ID;
517 end if;
514
515 if (sql%notfound) then
516 raise no_data_found;
518 end DELETE_ROW;
519
520 procedure ADD_LANGUAGE
521 is
522 begin
523 delete from JTF_CALENDARS_TL T
524 where not exists
525 (select NULL
526 from JTF_CALENDARS_B B
527 where B.CALENDAR_ID = T.CALENDAR_ID
528 );
529
530 update JTF_CALENDARS_TL T set (
531 CALENDAR_NAME,
532 DESCRIPTION
533 ) = (select
534 B.CALENDAR_NAME,
535 B.DESCRIPTION
536 from JTF_CALENDARS_TL B
537 where B.CALENDAR_ID = T.CALENDAR_ID
538 and B.LANGUAGE = T.SOURCE_LANG)
539 where (
540 T.CALENDAR_ID,
541 T.LANGUAGE
542 ) in (select
543 SUBT.CALENDAR_ID,
544 SUBT.LANGUAGE
545 from JTF_CALENDARS_TL SUBB, JTF_CALENDARS_TL SUBT
546 where SUBB.CALENDAR_ID = SUBT.CALENDAR_ID
547 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
548 and (SUBB.CALENDAR_NAME <> SUBT.CALENDAR_NAME
549 or (SUBB.CALENDAR_NAME is null and SUBT.CALENDAR_NAME is not null)
550 or (SUBB.CALENDAR_NAME is not null and SUBT.CALENDAR_NAME is null)
551 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
552 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
553 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
554 ));
555
556 insert into JTF_CALENDARS_TL (
557 CALENDAR_ID,
558 CALENDAR_NAME,
559 DESCRIPTION,
560 CREATED_BY,
561 CREATION_DATE,
562 LAST_UPDATED_BY,
563 LAST_UPDATE_DATE,
564 LAST_UPDATE_LOGIN,
565 LANGUAGE,
566 SOURCE_LANG
567 ) select
568 B.CALENDAR_ID,
569 B.CALENDAR_NAME,
570 B.DESCRIPTION,
571 B.CREATED_BY,
572 B.CREATION_DATE,
573 B.LAST_UPDATED_BY,
574 B.LAST_UPDATE_DATE,
575 B.LAST_UPDATE_LOGIN,
576 L.LANGUAGE_CODE,
577 B.SOURCE_LANG
578 from JTF_CALENDARS_TL B, FND_LANGUAGES L
579 where L.INSTALLED_FLAG in ('I', 'B')
580 and B.LANGUAGE = userenv('LANG')
581 and not exists
582 (select NULL
583 from JTF_CALENDARS_TL T
584 where T.CALENDAR_ID = B.CALENDAR_ID
585 and T.LANGUAGE = L.LANGUAGE_CODE);
586 end ADD_LANGUAGE;
587 /*************************************************************************/
588 FUNCTION not_null(column_to_check IN CHAR) RETURN boolean IS
589 BEGIN
590 IF column_to_check IS NULL THEN
591 return(FALSE);
592 ELSE
593 return(TRUE);
594 END IF;
595 END;
596 /*************************************************************************/
597 FUNCTION end_greater_than_begin(start_date IN DATE, end_date IN DATE) RETURN boolean IS
598 BEGIN
599 IF start_date > end_date THEN
600 return(FALSE);
601 ELSE
602 return(TRUE);
603 END IF;
604 END;
605 /*************************************************************************/
606 procedure UPDATE_TASK(task_id IN NUMBER, task_name IN CHAR, description IN CHAR, priority IN NUMBER, planned_start_date IN DATE,
607 planned_end_date IN DATE, task_status_id IN NUMBER) IS
608 l_rs varchar2(1) ;
609 x number := 3;
610 l_msg_count number ;
611 l_msg_data varchar2(2000) ;
612 v_task_id number := task_id;
613 v_task_assignment_id number;
614
615 BEGIN
616
617 select jtf_tasks_b.object_version_number
618 into x
619 from jtf_tasks_b
620 where task_id = v_task_id;
621
622 jtf_tasks_pub.update_task(
623 P_API_VERSION => 1.0 ,
624 P_INIT_MSG_LIST => fnd_api.g_false ,
625 P_COMMIT => fnd_api.g_false ,
626 P_TASK_ID => v_task_id ,
627 P_TASK_NAME => task_name ,
628 P_TASK_STATUS_ID => task_status_id,
629 P_DESCRIPTION => 'Hard Coded',
630 P_TASK_PRIORITY_ID => priority,
631 p_object_version_number => x ,
632 p_planned_start_date => planned_start_date ,
633 p_planned_end_date => planned_end_date ,
634 p_bound_mode_code => 'y',
635 X_RETURN_STATUS => l_rs,
636 X_MSG_COUNT => l_msg_count,
637 X_MSG_DATA => l_msg_data);
638
639
640 select task_assignment_id
641 into v_task_assignment_id
642 from jtf_task_assignments
643 where task_id = v_task_id;
644
645 select object_version_number
646 into x
647 from jtf_task_assignments
648 where task_assignment_id = v_task_assignment_id;
649
650 jtf_task_assignments_pub.update_task_assignment(
651 P_API_VERSION => 1 ,
652 p_init_msg_list => fnd_api.g_true ,
653 p_commit => fnd_api.g_true ,
654 p_task_assignment_id => v_task_assignment_id,
655 p_object_version_number => x ,
656 P_ACTUAL_EFFORT => NULL,
657 P_ACTUAL_EFFORT_UOM => NULL,
658 P_ALARM_TYPE_CODE => NULL ,
659 P_ALARM_CONTACT => NULL ,
660 P_SCHED_TRAVEL_DURATION => NULL,
661 P_SCHED_TRAVEL_DURATION_UOM => NULL ,
662 p_shift_construct_id => null ,
663 P_ASSIGNMENT_STATUS_ID => task_status_id,
664 X_RETURN_STATUS => l_rs ,
665 X_MSG_COUNT => l_msg_count ,
666 X_MSG_DATA => l_msg_data ) ;
667
668
669 END;
670 /*************************************************************************/
671 procedure CREATE_TASK(task_name IN CHAR, description IN CHAR, priority IN NUMBER, planned_start_date IN DATE,
672 planned_end_date IN DATE, resource_type IN CHAR, resource_id IN NUMBER,
673 task_status_id IN NUMBER) IS
674
675
676
677
678
679 l_rs varchar2(1) ;
680 x number ;
681 v_task_assignment_id NUMBER;
682 l_msg_count number ;
683 l_msg_data varchar2(2000) ;
684 recurs jtf_tasks_pub.task_recur_rec ;
685 rsc jtf_tasks_pub.task_rsrc_req_tbl ;
686 ass jtf_tasks_pub.task_assign_tbl ;
687 notes jtf_tasks_pub.task_notes_tbl ;
688 begin
689 recurs.OCCURS_WHICH := 2 ;
690 recurs.DAY_OF_WEEK := 2 ;
691 recurs.DATE_OF_MONTH := NULL ;
692 recurs.OCCURS_MONTH := 1 ;
693 recurs.OCCURS_UOM := 'YR' ;
694 recurs.OCCURS_EVERY := 1 ;
695 recurs.OCCURS_NUMBER := 50 ;
696 recurs.START_DATE_ACTIVE := sysdate ;
697 recurs.end_DATE_ACTIVE := NULL ;
698 rsc(1).resource_type_code := 'SO' ;
699 rsc(1).required_units := 100 ;
700 rsc(1).enabled_flag := 'Y' ;
701 ass(1).resource_type_code := 'SO' ;
702 ass(1).resource_id := 100 ;
703 notes(1).parent_note_id := null;
704 notes(1).org_id := 173 ;
705 notes(1).notes := 'Notes' ;
706 notes(1).notes_detail := null ;
707 notes(1).note_status := null ;
708 notes(1).entered_by := -1 ;
709 notes(1).entered_date := sysdate ;
710 notes(1).note_type := null ;
711 jtf_tasks_pub.create_task(
712 P_API_VERSION => 1.0 ,
713 P_INIT_MSG_LIST => fnd_api.g_true ,
714 P_COMMIT => fnd_api.g_true ,
715 p_task_id => null ,
716 P_TASK_NAME => task_name ,
717 P_TASK_TYPE_NAME => null ,
718 P_TASK_TYPE_ID => 3 ,
719 P_DESCRIPTION => description ,
720 P_TASK_STATUS_NAME => null,
721 P_TASK_STATUS_ID => task_status_id,
722 P_TASK_PRIORITY_NAME => 'Low',
723 P_TASK_PRIORITY_ID => priority,
724 P_OWNER_TYPE_NAME => NULL,
725 P_OWNER_TYPE_CODE => 'RS_EMPLOYEE',
726 P_OWNER_ID => 101 ,
727 P_OWNER_TERRITORY_ID => NULL ,
728 P_ASSIGNED_BY_NAME => NULL ,
729 P_ASSIGNED_BY_ID => NULL ,
730 P_CUSTOMER_NUMBER => NULL ,
731 P_CUSTOMER_ID => NULL ,
732 P_CUST_ACCOUNT_NUMBER => NULL ,
733 P_CUST_ACCOUNT_ID => NULL ,
734 P_ADDRESS_ID => NULL ,
735 P_ADDRESS_NUMBER => NULL ,
736 P_PLANNED_START_DATE => planned_start_date,
737 P_PLANNED_END_DATE => planned_end_date,
738 P_SCHEDULED_START_DATE => planned_start_date,
739 P_SCHEDULED_END_DATE => planned_end_date,
740 P_ACTUAL_START_DATE => NULL ,
741 P_ACTUAL_END_DATE => NULL ,
742 P_TIMEZONE_ID => NULL ,
743 P_TIMEZONE_NAME => NULL ,
744 P_SOURCE_OBJECT_TYPE_CODE => 'SR' ,
745 P_SOURCE_OBJECT_ID => 16515,
746 P_SOURCE_OBJECT_NAME => 3753,
747 --P_SOURCE_OBJECT_ID => 21653 ,
748 --P_SOURCE_OBJECT_NAME => 21191 ,
749 --P_DURATION => 10 ,
750 p_escalation_level => null,
751 --P_DURATION_UOM => 'DAY' ,
752 P_PLANNED_EFFORT => NULL ,
753 P_PLANNED_EFFORT_UOM => NULL ,
754 P_ACTUAL_EFFORT => NULL ,
755 P_ACTUAL_EFFORT_UOM => NULL ,
756 P_PERCENTAGE_COMPLETE => NULL ,
757 P_REASON_CODE => NULL ,
758 P_PRIVATE_FLAG => 'Y' ,
759 P_PUBLISH_FLAG => NULL ,
760 P_RESTRICT_CLOSURE_FLAG => NULL ,
761 P_MULTI_BOOKED_FLAG => NULL ,
762 P_MILESTONE_FLAG => NULL ,
763 P_HOLIDAY_FLAG => NULL ,
764 P_BILLABLE_FLAG => NULL ,
765 P_BOUND_MODE_CODE => 'x' ,
766 P_SOFT_BOUND_FLAG => NULL ,
767 P_NOTIFICATION_FLAG => NULL ,
768 P_NOTIFICATION_PERIOD => NULL ,
769 P_NOTIFICATION_PERIOD_UOM => NULL ,
770 P_PARENT_TASK_NUMBER => NULL ,
771 P_PARENT_TASK_ID => NULL ,
772 P_ALARM_START => NULL ,
773 P_ALARM_START_UOM => NULL ,
774 P_ALARM_ON => NULL ,
775 P_ALARM_COUNT => NULL ,
776 P_ALARM_INTERVAL => NULL ,
777 P_ALARM_INTERVAL_UOM => NULL ,
778 P_PALM_FLAG => NULL ,
779 P_WINCE_FLAG => NULL ,
780 P_LAPTOP_FLAG => NULL ,
781 P_DEVICE1_FLAG => NULL ,
782 P_DEVICE2_FLAG => NULL ,
783 P_DEVICE3_FLAG => NULL ,
784 P_COSTS => NULL ,
785 P_CURRENCY_CODE => NULL ,
786 --P_TASK_RECUR_REC => recurs ,
787 --p_task_rsrc_req_tbl => rsc ,
788 --p_task_assign_tbl => ass ,
789 --p_task_notes_tbl => notes ,
790 X_RETURN_STATUS => l_rs ,
791 X_MSG_COUNT => l_msg_count ,
792 X_MSG_DATA => l_msg_data ,
793 X_TASK_ID => x ) ;
794
795
796 jtf_task_assignments_pub.create_task_assignment(
797 P_API_VERSION => 1.0,
798 p_init_msg_list => fnd_api.g_false,
799 p_commit => fnd_api.g_false,
800 P_TASK_ID => x,
801 P_RESOURCE_TYPE_CODE => resource_type,
802 P_RESOURCE_ID => resource_id,
803 P_ASSIGNMENT_STATUS_ID => task_status_id,
804 X_TASK_ASSIGNMENT_ID => v_task_assignment_id,
805 X_RETURN_STATUS => l_rs,
806 X_MSG_COUNT => l_msg_count,
807 X_MSG_DATA => l_msg_data);
808
809
810 end;
811 end JTF_CALENDARS_PKG;