[Home] [Help]
PACKAGE BODY: APPS.JTF_CAL_ITEMS_PVT
Source
1 PACKAGE BODY JTF_CAL_ITEMS_PVT AS
2 /* $Header: jtfvcib.pls 115.11 2003/03/06 01:39:50 rdespoto ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_CAL_ITEMS_PVT';
5
6 PROCEDURE Lock_Row
7 /*****************************************************************************
8 ** INTERNAL ONLY: This procedure is used in Update_Row and Delete_Row to make
9 ** sure the record isn't being used by a different user.
10 *****************************************************************************/
11 ( p_cal_item_id IN NUMBER
12 , p_object_version_number IN NUMBER
13 )AS
14
15 CURSOR c_lock
16 /***************************************************************************
17 ** Cursor to lock the record
18 ***************************************************************************/
19 ( b_cal_item_id IN NUMBER
20 )IS SELECT object_version_number
21 FROM jtf_cal_items_b
22 WHERE cal_item_id = b_cal_item_id
23 FOR UPDATE OF object_version_number NOWAIT;
24
25 l_object_version_number NUMBER;
26
27 BEGIN
28 /***************************************************************************
29 ** Making sure the cursor is closed
30 ***************************************************************************/
31 IF (c_lock%ISOPEN)
32 THEN
33 CLOSE c_lock;
34 END IF;
35 /***************************************************************************
36 ** Try to lock the record
37 ***************************************************************************/
38 OPEN c_lock(p_Cal_Item_ID);
39
40 /***************************************************************************
41 ** Get the object version number of the record that got locked
42 ***************************************************************************/
43 FETCH c_lock INTO l_object_version_number;
44
45 /***************************************************************************
46 ** The record no longer exists in the database: raise exception
47 ***************************************************************************/
48 IF (c_lock%NOTFOUND)
49 THEN
50 CLOSE c_lock;
51 fnd_message.set_name ('JTF', 'JTF_CAL_RECORD_DELETED');
52 fnd_msg_pub.add;
53 RAISE fnd_api.g_exc_unexpected_error;
54 END IF;
55
56 /***************************************************************************
57 ** If the object version number has changed, the record has changed:
58 ** raise exception
59 ***************************************************************************/
60 CLOSE c_lock;
61 IF (l_object_version_number <> p_object_version_number)
62 THEN
63 fnd_message.set_name ('JTF', 'JTF_CAL_RECORD_CHANGED');
64 fnd_msg_pub.add;
65 RAISE fnd_api.g_exc_unexpected_error;
66 END IF;
67 END Lock_Row;
68
69 PROCEDURE Insert_Row
70 ( p_api_version IN NUMBER
71 , p_init_msg_list IN VARCHAR2
72 , p_commit IN VARCHAR2
73 , p_validation_level IN NUMBER
74 , x_return_status OUT NOCOPY VARCHAR2
75 , x_msg_count OUT NOCOPY NUMBER
76 , x_msg_data OUT NOCOPY VARCHAR2
77 , p_itm_rec IN cal_item_rec_type
78 , x_cal_item_id OUT NOCOPY NUMBER
79 )
80 IS
81 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
82 l_api_version CONSTANT NUMBER := 1.1;
83 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
84 l_rowid ROWID;
85 l_cal_item_id NUMBER;
86
87 CURSOR c_record_exists
88 (b_cal_item_id NUMBER
89 )IS SELECT ROWID
90 FROM JTF_CAL_ITEMS_B
91 WHERE cal_item_id = b_cal_item_id;
92
93 BEGIN
94 SAVEPOINT create_calitems_pvt;
95 /***************************************************************************
96 ** Standard call to check for call compatibility
97 ***************************************************************************/
98 IF NOT FND_API.Compatible_API_Call( l_api_version
99 , p_api_version
100 , l_api_name
101 , G_PKG_NAME
102 )
103 THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 END IF;
106 /***************************************************************************
107 ** Initialize message list if p_init_msg_list is set to TRUE
108 ***************************************************************************/
109
110 IF FND_API.To_Boolean(p_init_msg_list)
111 THEN
112 FND_MSG_PUB.Initialize;
113 END IF;
114 /***************************************************************************
115 ** Initialize API return status to success
116 ***************************************************************************/
117 x_return_status := FND_API.G_RET_STS_SUCCESS;
118 /***************************************************************************
119 ** Insert into table. Generate the ID from the
120 ** sequence and return it
121 ***************************************************************************/
122
123 INSERT INTO JTF_CAL_ITEMS_B
124 ( CAL_ITEM_ID
125 , RESOURCE_ID
126 , RESOURCE_TYPE
127 , ITEM_TYPE
128 , ITEM_TYPE_CODE
129 , SOURCE_CODE
130 , SOURCE_ID
131 , START_DATE
132 , END_DATE
133 , TIMEZONE_ID
134 , URL
135 , CREATED_BY
136 , CREATION_DATE
137 , LAST_UPDATED_BY
138 , LAST_UPDATE_DATE
139 , LAST_UPDATE_LOGIN
140 , OBJECT_VERSION_NUMBER
141 , APPLICATION_ID
142 ) VALUES
143 ( JTF_CAL_ITEMS_S.NEXTVAL -- returning into l_cal_item_id
144 , p_itm_rec.RESOURCE_ID
145 , p_itm_rec.RESOURCE_TYPE
146 , p_itm_rec.ITEM_TYPE
147 , p_itm_rec.ITEM_TYPE_CODE
148 , p_itm_rec.SOURCE_CODE
149 , p_itm_rec.SOURCE_ID
150 , p_itm_rec.START_DATE
151 , p_itm_rec.END_DATE
152 , p_itm_rec.TIMEZONE_ID
153 , GetUrl(p_itm_rec.SOURCE_CODE)
154 , p_itm_rec.CREATED_BY
155 , p_itm_rec.CREATION_DATE
156 , p_itm_rec.LAST_UPDATED_BY
157 , p_itm_rec.LAST_UPDATE_DATE
158 , p_itm_rec.LAST_UPDATE_LOGIN
159 , p_itm_rec.OBJECT_VERSION_NUMBER -- always 1 for a new object
160 , p_itm_rec.APPLICATION_ID
161 )RETURNING CAL_ITEM_ID INTO l_cal_item_id;
162 /***************************************************************************
163 ** Insert into _TL table
164 ***************************************************************************/
165 INSERT INTO JTF_CAL_ITEMS_TL
166 ( CAL_ITEM_ID
167 , LANGUAGE
168 , SOURCE_LANG
169 , ITEM_NAME
170 , ITEM_DESCRIPTION
171 , CREATED_BY
172 , CREATION_DATE
173 , LAST_UPDATED_BY
174 , LAST_UPDATE_DATE
175 , LAST_UPDATE_LOGIN
176 , APPLICATION_ID
177 ) SELECT l_cal_item_id
178 , l.LANGUAGE_CODE
179 , userenv('LANG')
180 , NVL(p_itm_rec.ITEM_NAME, ' ')
181 , p_itm_rec.ITEM_DESCRIPTION
182 , p_itm_rec.CREATED_BY
183 , p_itm_rec.CREATION_DATE
184 , p_itm_rec.LAST_UPDATED_BY
185 , p_itm_rec.LAST_UPDATE_DATE
186 , p_itm_rec.LAST_UPDATE_LOGIN
187 , p_itm_rec.APPLICATION_ID
188 FROM FND_LANGUAGES l
189 WHERE l.INSTALLED_FLAG IN ('I','B')
190 AND NOT EXISTS ( SELECT NULL
191 FROM JTF_CAL_ITEMS_TL t
192 WHERE t.CAL_ITEM_ID = l_cal_item_id
193 AND t.LANGUAGE = l.LANGUAGE_CODE);
194 /***************************************************************************
195 ** Check whether the insert was succesfull
196 ***************************************************************************/
197 IF (c_record_exists%ISOPEN)THEN
198 CLOSE c_record_exists;
199 END IF;
200
201 OPEN c_record_exists(l_cal_item_id);
202 FETCH c_record_exists INTO l_rowid;
203 IF (c_record_exists%NOTFOUND)THEN
204 IF (c_record_exists%ISOPEN)
205 THEN
206 CLOSE c_record_exists;
207 END IF;
208 RAISE no_data_found;
209 END IF;
210
211 IF (c_record_exists%ISOPEN) THEN
212 CLOSE c_record_exists;
213 END IF;
214 /***************************************************************************
215 ** Return the key value to the caller
216 ***************************************************************************/
217 x_cal_item_id := l_cal_item_id;
218 /***************************************************************************
219 ** Standard check of p_commit
220 ***************************************************************************/
221 IF FND_API.To_Boolean(p_commit)
222 THEN
223 COMMIT WORK;
224 END IF;
225 /***************************************************************************
226 ** Standard call to get message count and if count is 1, get message info
227 ***************************************************************************/
228 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
229 , p_data => x_msg_data
230 );
231 EXCEPTION
232 WHEN FND_API.G_EXC_ERROR THEN
233 IF (c_record_exists%ISOPEN)THEN
234 CLOSE c_record_exists;
235 END IF;
236 ROLLBACK TO create_calitems_pvt;
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
239 , p_data => x_msg_data
240 );
241
242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
243 IF (c_record_exists%ISOPEN) THEN
244 CLOSE c_record_exists;
245 END IF;
246 ROLLBACK TO create_calitems_pvt;
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
249 , p_data => x_msg_data
250 );
251 WHEN OTHERS THEN
252 IF (c_record_exists%ISOPEN) THEN
253 CLOSE c_record_exists;
254 END IF;
255 ROLLBACK TO create_calitems_pvt;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
258 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME
259 , l_api_name
260 );
261 END IF;
262 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
263 , p_data => x_msg_data
264 );
265
266 END Insert_Row;
267
268 PROCEDURE Update_Row
269 ( p_api_version IN NUMBER
270 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
271 , p_commit IN VARCHAR2 := fnd_api.g_false
272 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
273 , x_return_status OUT NOCOPY VARCHAR2
274 , x_msg_count OUT NOCOPY NUMBER
275 , x_msg_data OUT NOCOPY VARCHAR2
276 , p_itm_rec IN cal_item_rec_type
277 , x_object_version_number OUT NOCOPY NUMBER
278 )IS
279 CURSOR c_item (l_cal_item_id IN NUMBER)
280 IS
281 SELECT DECODE (
282 p_itm_rec.resource_id,
283 fnd_api.g_miss_num, resource_id,
284 p_itm_rec.resource_id
285 ) resource_id,
286 DECODE (
287 p_itm_rec.resource_type,
288 fnd_api.g_miss_char, resource_type,
289 p_itm_rec.resource_type
290 ) resource_type,
291 DECODE (
292 p_itm_rec.source_code,
293 fnd_api.g_miss_char, source_code,
294 p_itm_rec.source_code
295 ) source_code,
296 DECODE (
297 p_itm_rec.source_id,
298 fnd_api.g_miss_num, source_id,
299 p_itm_rec.source_id
300 ) source_id,
301 DECODE (
302 p_itm_rec.start_date,
303 fnd_api.g_miss_date, start_date,
304 p_itm_rec.start_date
305 ) start_date,
306 DECODE (
307 p_itm_rec.end_date,
308 fnd_api.g_miss_date, end_date,
309 p_itm_rec.end_date
310 ) end_date,
311 DECODE (
312 p_itm_rec.timezone_id,
313 fnd_api.g_miss_num, timezone_id,
314 p_itm_rec.timezone_id
315 ) timezone_id,
316 DECODE (
317 p_itm_rec.url,
318 fnd_api.g_miss_char, url,
319 p_itm_rec.url
320 ) url,
321 DECODE (
322 p_itm_rec.created_by,
323 fnd_api.g_miss_num, jtf_cal_items_b.created_by,
324 p_itm_rec.created_by
325 ) created_by,
326 DECODE (
327 p_itm_rec.creation_date,
328 fnd_api.g_miss_date, jtf_cal_items_b.creation_date,
329 p_itm_rec.creation_date
330 ) creation_date,
331 DECODE (
332 p_itm_rec.last_updated_by,
333 fnd_api.g_miss_num, jtf_cal_items_b.last_updated_by,
334 p_itm_rec.last_updated_by
335 ) last_updated_by,
336 DECODE (
337 p_itm_rec.last_update_date,
338 fnd_api.g_miss_date, jtf_cal_items_b.last_update_date,
339 p_itm_rec.last_update_date
340 ) last_update_date,
341 DECODE (
342 p_itm_rec.last_update_login,
343 fnd_api.g_miss_num, jtf_cal_items_b.last_update_login,
344 p_itm_rec.last_update_login
345 ) last_update_login,
346 DECODE (
347 p_itm_rec.application_id,
348 fnd_api.g_miss_num, jtf_cal_items_b.application_id,
349 p_itm_rec.application_id
350 ) application_id,
351 DECODE (
352 p_itm_rec.item_name,
353 fnd_api.g_miss_char, item_name,
354 p_itm_rec.item_name
355 ) item_name
356 FROM jtf_cal_items_b, jtf_cal_items_tl
357 WHERE jtf_cal_items_b.cal_item_id = l_cal_item_id
358 AND jtf_cal_items_tl.cal_item_id = jtf_cal_items_b.cal_item_id;
359
360
361 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
362 l_api_version CONSTANT NUMBER := 1.0;
363 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
364 l_url VARCHAR2(4000) := fnd_api.g_miss_char;
365 l_item_rec c_item%ROWTYPE;
366
367 BEGIN
368
369 SAVEPOINT update_calitems_pvt;
370
371 /***************************************************************************
372 ** Standard call to check for call compatibility
373 ***************************************************************************/
374 IF NOT FND_API.Compatible_API_Call( l_api_version
375 , p_api_version
376 , l_api_name
377 , G_PKG_NAME
378 )
379 THEN
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 END IF;
382 /***************************************************************************
383 ** Initialize message list if p_init_msg_list is set to TRUE
384 ***************************************************************************/
385 IF FND_API.To_Boolean(p_init_msg_list)
386 THEN
387 FND_MSG_PUB.initialize;
388 END IF;
389 /***************************************************************************
390 ** Initialize API return status to success
391 ***************************************************************************/
392 x_return_status := FND_API.G_RET_STS_SUCCESS;
393 IF (c_item%ISOPEN) THEN
394 CLOSE c_item;
395 END IF;
396 OPEN c_item(p_itm_rec.cal_item_id);
397 FETCH c_item INTO l_item_rec;
398 IF (c_item%NOTFOUND) THEN
399 IF (c_item%ISOPEN) THEN
400 CLOSE c_item;
401 END IF;
402 fnd_message.set_name ('JTF', 'JTF_CAL_INV_CAL_ITEM');
403 fnd_message.set_token ('CAL_ITEM_ID', p_itm_rec.cal_item_id);
404 fnd_msg_pub.add;
405 RAISE no_data_found;
406 END IF;
407
408 IF (c_item%ISOPEN)THEN
409 CLOSE c_item;
410 END IF;
411 /***************************************************************************
412 ** End date cannot be before start date
413 ***************************************************************************/
414
415 IF (l_item_rec.START_DATE > l_item_rec.END_DATE) THEN
416 FND_MESSAGE.set_name('JTF', 'JTF_CAL_END_DATE');
417 FND_MESSAGE.set_token('P_START_DATE', to_char(l_item_rec.START_DATE, 'YYYYMMDDHH24MISS'));
418 FND_MESSAGE.set_token('P_END_DATE', to_char(l_item_rec.END_DATE, 'YYYYMMDDHH24MISS'));
419 FND_MSG_pub.ADD;
420 RAISE FND_API.g_exc_unexpected_error;
421 END IF;
422 /***************************************************************************
423 ** Try to lock the row before updating it
424 ***************************************************************************/
425 Lock_Row( p_itm_rec.cal_item_id
426 , p_itm_rec.object_version_number
427 );
428 l_url := GetUrl(l_item_rec.source_code);
429 /***************************************************************************
430 ** Update the record
431 ***************************************************************************/
432 UPDATE JTF_CAL_ITEMS_B
433 SET RESOURCE_ID = l_item_rec.resource_id
434 , RESOURCE_TYPE = l_item_rec.resource_type
435 , SOURCE_CODE = l_item_rec.source_code
436 , SOURCE_ID = l_item_rec.source_id
437 , START_DATE = l_item_rec.start_date
438 , END_DATE = l_item_rec.end_date
439 , TIMEZONE_ID = l_item_rec.timezone_id
440 , URL = l_url
441 , LAST_UPDATED_BY = l_item_rec.last_updated_by
442 , LAST_UPDATE_DATE = l_item_rec.last_update_date
443 , LAST_UPDATE_LOGIN = l_item_rec.last_update_login
444 , OBJECT_VERSION_NUMBER = jtf_cal_object_version_s.NEXTVAL
445 , APPLICATION_ID = l_item_rec.application_id
446 WHERE CAL_ITEM_ID = p_itm_rec.cal_item_id
447 RETURNING OBJECT_VERSION_NUMBER INTO x_object_version_number; -- return new object version number
448
449 /***************************************************************************
450 ** Check if the update was succesful
451 ***************************************************************************/
452 IF (SQL%NOTFOUND)THEN
453 RAISE no_data_found;
454 END IF;
455
456 UPDATE JTF_CAL_ITEMS_TL
457 SET ITEM_NAME = NVL(l_item_rec.ITEM_NAME, ' ')
458 , LAST_UPDATE_DATE = l_item_rec.LAST_UPDATE_DATE
459 , LAST_UPDATED_BY = l_item_rec.LAST_UPDATED_BY
460 , LAST_UPDATE_LOGIN = l_item_rec.LAST_UPDATE_LOGIN
461 , SOURCE_LANG = userenv('LANG')
462 , APPLICATION_ID = l_item_rec.APPLICATION_ID
463 WHERE CAL_ITEM_ID = p_itm_rec.CAL_ITEM_ID
464 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
465 IF (SQL%NOTFOUND)
466 THEN
467 RAISE NO_DATA_FOUND;
468 END IF;
469 /***************************************************************************
470 ** Standard check of p_commit
471 ***************************************************************************/
472 IF FND_API.To_Boolean(p_commit) THEN
473 COMMIT WORK;
474 END IF;
475 /***************************************************************************
476 ** Standard call to get message count and if count is 1, get message info
477 ***************************************************************************/
478 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
479 , p_data => x_msg_data
480 );
481
482 EXCEPTION
483 WHEN FND_API.G_EXC_ERROR THEN
484 IF (c_item%ISOPEN)THEN
485 CLOSE c_item;
486 END IF;
487 ROLLBACK TO update_calitems_pvt;
488 x_return_status := FND_API.G_RET_STS_ERROR;
489 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
490 , p_data => x_msg_data
491 );
492
493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
494 IF (c_item%ISOPEN) THEN
495 CLOSE c_item;
496 END IF;
497 ROLLBACK TO update_calitems_pvt;
498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
499 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
500 , p_data => x_msg_data
501 );
502 WHEN OTHERS THEN
503 IF (c_item%ISOPEN) THEN
504 CLOSE c_item;
505 END IF;
506 ROLLBACK TO update_calitems_pvt;
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
509 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME
510 , l_api_name
511 );
512 END IF;
513 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
514 , p_data => x_msg_data
515 );
516 END Update_Row;
517
518 PROCEDURE Delete_Row
519 ( p_api_version IN NUMBER
520 , p_init_msg_list IN VARCHAR2
521 , p_commit IN VARCHAR2
522 , p_validation_level IN NUMBER
523 , x_return_status OUT NOCOPY VARCHAR2
524 , x_msg_count OUT NOCOPY NUMBER
525 , x_msg_data OUT NOCOPY VARCHAR2
526 , p_cal_item_id IN NUMBER
527 , p_object_version_number IN NUMBER
528 )IS
529 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
530 l_api_version CONSTANT NUMBER := 1.0;
531 l_api_name_full CONSTANT VARCHAR2(62) := G_PKG_NAME||'.'||l_api_name;
532 l_return_status VARCHAR2(1);
533 l_msg_count NUMBER;
534 l_msg_data VARCHAR2(2000);
535 BEGIN
536 SAVEPOINT delete_calitems_pvt;
537
538 /***************************************************************************
539 ** Standard call to check for call compatibility
540 ***************************************************************************/
541 IF NOT FND_API.Compatible_API_Call( l_api_version
542 , p_api_version
543 , l_api_name
544 , G_PKG_NAME
545 )
546 THEN
547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
548 END IF;
549 /***************************************************************************
550 ** Initialize message list if requested
551 ***************************************************************************/
552 IF FND_API.to_Boolean( p_init_msg_list )
553 THEN
554 FND_MSG_PUB.initialize;
555 END IF;
556 /***************************************************************************
557 ** Initialize return status to SUCCESS
558 ***************************************************************************/
559 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
560
561 DELETE FROM JTF_CAL_ITEMS_TL
562 WHERE CAL_ITEM_ID = p_cal_item_id;
563 /***************************************************************************
564 ** Check whether delete was succesful
565 ***************************************************************************/
566 IF (SQL%NOTFOUND)
567 THEN
568 RAISE no_data_found;
569 END IF;
570 /***************************************************************************
571 ** Try to lock the row before updating it
572 ***************************************************************************/
573 Lock_Row( p_cal_item_id
574 , p_object_version_number
575 );
576
577 DELETE FROM JTF_CAL_ITEMS_B
578 WHERE CAL_ITEM_ID = p_cal_item_id;
579 /***************************************************************************
580 ** Check whether delete was succesful
581 ***************************************************************************/
582 IF (SQL%NOTFOUND)THEN
583 RAISE no_data_found;
584 END IF;
585
586 IF FND_API.To_Boolean(p_commit) THEN
587 COMMIT WORK;
588 END IF;
589
590 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
591 , p_data => X_MSG_DATA
592 );
593 EXCEPTION
594 WHEN FND_API.G_EXC_ERROR
595 THEN
596 ROLLBACK TO delete_calitems_pvt;
597 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
598 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
599 , p_data => X_MSG_DATA
600 );
601
602 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
603 THEN
604 ROLLBACK TO delete_calitems_pvt;
605 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
606 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
607 , p_data => X_MSG_DATA
608 );
609 WHEN OTHERS THEN
610 ROLLBACK TO delete_calitems_pvt;
611 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
612 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
613 THEN
614 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
615 , l_api_name
616 );
617 END IF;
618 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
619 , p_data => X_MSG_DATA
620 );
621 END Delete_Row;
622
623 PROCEDURE Get_Object_Version
624 ( p_api_version IN NUMBER
625 , p_init_msg_list IN VARCHAR2
626 , p_validation_level IN NUMBER
627 , x_return_status OUT NOCOPY VARCHAR2
628 , x_msg_count OUT NOCOPY NUMBER
629 , x_msg_data OUT NOCOPY VARCHAR2
630 , p_cal_item_id IN NUMBER
631 , x_object_version_number OUT NOCOPY NUMBER
632 )
633 IS
634 l_api_name CONSTANT VARCHAR2(30) := 'Get_Object_Version';
635 l_api_version CONSTANT NUMBER := 1.0;
636 l_api_name_full CONSTANT VARCHAR2(62) := G_PKG_NAME||'.'||l_api_name;
637 l_return_status VARCHAR2(1);
638
639 CURSOR c_cal_item
640 (b_cal_item_id NUMBER
641 )IS SELECT object_version_number
642 FROM JTF_CAL_ITEMS_B
643 WHERE cal_item_id = b_cal_item_id;
644
645 BEGIN
646 /***************************************************************************
647 ** Standard call to check for call compatibility
648 ***************************************************************************/
649 IF NOT FND_API.Compatible_API_Call( l_api_version
650 , p_api_version
651 , l_api_name
652 , G_PKG_NAME
653 )
654 THEN
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656 END IF;
657 /***************************************************************************
658 ** Initialize message list if p_init_msg_list is set to TRUE
659 ***************************************************************************/
660 IF FND_API.To_Boolean(p_init_msg_list)
661 THEN
662 FND_MSG_PUB.Initialize;
663 END IF;
664 /***************************************************************************
665 ** Initialize API return status to success
666 ***************************************************************************/
667 x_return_status := FND_API.G_RET_STS_SUCCESS;
668 /***************************************************************************
669 ** Get the object_version_number
670 ***************************************************************************/
671 IF (c_cal_item%ISOPEN)
672 THEN
673 CLOSE c_cal_item;
674 END IF;
675
676 OPEN c_cal_item(p_cal_item_id);
677
678 FETCH c_cal_item INTO x_object_version_number;
679
680 IF (c_cal_item%NOTFOUND) THEN
681 IF (c_cal_item%ISOPEN)
682 THEN
683 CLOSE c_cal_item;
684 END IF;
685 RAISE no_data_found;
686 END IF;
687 IF (c_cal_item%ISOPEN)
688 THEN
689 CLOSE c_cal_item;
690 END IF;
691
692 /***************************************************************************
693 ** Standard call to get message count and if count is 1, get message info
694 ***************************************************************************/
695 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
696 , p_data => x_msg_data
697 );
698
699 END Get_Object_Version;
700
701 FUNCTION GetUrl(p_source_code IN VARCHAR2)
702 RETURN VARCHAR2
703 IS
704 CURSOR c_url
705 IS
706 SELECT fff.WEB_HTML_CALL call
707 FROM JTF_OBJECTS_B job, FND_FORM_FUNCTIONS fff
708 WHERE job.OBJECT_CODE = p_source_code
709 AND fff.FUNCTION_NAME = job.WEB_FUNCTION_NAME;
710
711 l_function_url VARCHAR2(2000);
712
713 BEGIN
714 OPEN c_url;
715 FETCH c_url INTO l_function_url;
716 IF c_url%NOTFOUND THEN
717 CLOSE c_url;
718 RETURN NULL;
719 ELSE
720 CLOSE c_url;
721 RETURN l_function_url;
722 END IF;
723
724 END GetUrl;
725
726 FUNCTION GetUrlParams(p_source_code IN VARCHAR2,
727 p_source_id IN NUMBER)
728 RETURN VARCHAR2
729 IS
730 CURSOR c_params
731 IS
732 SELECT job.WEB_FUNCTION_PARAMETERS par
733 FROM JTF_OBJECTS_B job
734 WHERE job.OBJECT_CODE = p_source_code;
735
736 l_function_parameters VARCHAR2(2000);
737
738 BEGIN
739 OPEN c_params;
740 FETCH c_params INTO l_function_parameters;
741 IF c_params%NOTFOUND
742 THEN
743 CLOSE c_params;
744 RETURN NULL;
745 ELSE
746 CLOSE c_params;
747 /***************************************************************************
748 ** Replace &ID with p_source_id in parameter list
749 ***************************************************************************/
750 l_function_parameters := REPLACE(l_function_parameters,'&ID',p_source_id);
751 RETURN l_function_parameters;
752 END IF;
753
754 END GetUrlParams;
755
756
757 FUNCTION GetName(p_source_code IN VARCHAR2,
758 p_source_id IN NUMBER)
759 RETURN VARCHAR2
760 IS
761
762 CURSOR c_name
763 IS
764 SELECT job.select_id, select_name, from_table, where_clause
765 FROM
766 JTF_OBJECTS_B job
767 WHERE
768 job.OBJECT_CODE = p_source_code;
769
770 l_id VARCHAR2(200);
771 l_name VARCHAR2(200);
772 l_from VARCHAR2(200);
773 l_where VARCHAR2(2000);
774 l_query VARCHAR2(32000);
775 l_item_name VARCHAR2(2000);
776
777 BEGIN
778 OPEN c_name;
779 FETCH c_name INTO l_id, l_name, l_from, l_where;
780 IF c_name%NOTFOUND
781 THEN
782 CLOSE c_name;
783 RETURN NULL;
784 ELSE
785 CLOSE c_name;
786 IF l_where IS NULL
787 THEN
788 /***************************************************************************
789 ** l_from is a view that has userenv('LANG') clause to enable translation
790 ***************************************************************************/
791 l_query := 'select ' || l_name || ' from ' ||
792 l_from || ' where ' || l_id || ' =: source_object_id';
793
794 ELSE
795 l_query := 'select ' || l_name || ' from ' ||
796 l_from || ' where ' || l_where ||
797 ' and '|| l_id || ' =: source_object_id';
798 END IF;
799 EXECUTE IMMEDIATE l_query INTO l_item_name USING p_source_id;
800 RETURN l_item_name;
801 END IF;
802 END GetName;
803
804 --------------------------------------------------------------------------
805 -- Start of comments
806 -- API Name : Add_Language
807 -- Type : Private
808 -- Description : Additional Language processing for JTF_CAL_ITEMS_B and
809 -- _TL tables. Used by JTFNLINS.sql
810 -- Pre-reqs : None
811 -- Parameters : None
812 -- Version : Current version 1.0
813 -- Previous version none
814 -- Initial version 1.0
815 --
816 -- Notes: :
817 --
818 -- End of comments
819 --------------------------------------------------------------------------
820 PROCEDURE Add_Language
821 IS
822 BEGIN
823 -- Delete all records that don't have a base record
824 DELETE FROM JTF_CAL_ITEMS_TL t
825 WHERE NOT EXISTS (SELECT NULL
826 FROM JTF_CAL_ITEMS_B b
827 WHERE b.CAL_ITEM_ID = t.CAL_ITEM_ID
828 );
829 -- Translate the records that already exists
830 UPDATE JTF_CAL_ITEMS_TL T
831 SET ( ITEM_NAME
832 , ITEM_DESCRIPTION
833 ) = ( SELECT B.ITEM_NAME
834 , B.ITEM_DESCRIPTION
835 FROM JTF_CAL_ITEMS_TL B
836 WHERE B.CAL_ITEM_ID = T.CAL_ITEM_ID
837 AND B.LANGUAGE = T.SOURCE_LANG
838 )
839 WHERE ( T.CAL_ITEM_ID
840 , T.LANGUAGE
841 ) IN ( SELECT SUBT.CAL_ITEM_ID
842 , SUBT.LANGUAGE
843 FROM JTF_CAL_ITEMS_TL SUBB
844 , JTF_CAL_ITEMS_TL SUBT
845 WHERE SUBB.CAL_ITEM_ID = SUBT.CAL_ITEM_ID
846 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
847 AND ( SUBB.ITEM_NAME <> SUBT.ITEM_NAME
848 OR SUBB.ITEM_DESCRIPTION <> SUBT.ITEM_DESCRIPTION
849 OR ( SUBB.ITEM_DESCRIPTION IS NULL
850 AND SUBT.ITEM_DESCRIPTION IS NOT NULL
851 )
852 OR ( SUBB.ITEM_DESCRIPTION IS NOT NULL
853 AND SUBT.ITEM_DESCRIPTION IS NULL
854 )
855 )
856 );
857 -- add records for new languages
858 INSERT INTO JTF_CAL_ITEMS_TL
859 ( CAL_ITEM_ID
860 , CREATED_BY
861 , CREATION_DATE
862 , LAST_UPDATED_BY
863 , LAST_UPDATE_DATE
864 , LAST_UPDATE_LOGIN
865 , ITEM_NAME
866 , ITEM_DESCRIPTION
867 , LANGUAGE
868 , SOURCE_LANG
869 , APPLICATION_ID
870 ) SELECT B.CAL_ITEM_ID
871 , B.CREATED_BY
872 , B.CREATION_DATE
873 , B.LAST_UPDATED_BY
874 , B.LAST_UPDATE_DATE
875 , B.LAST_UPDATE_LOGIN
876 , B.ITEM_NAME
877 , B.ITEM_DESCRIPTION
878 , L.LANGUAGE_CODE
879 , B.SOURCE_LANG
880 , B.APPLICATION_ID
881 FROM JTF_CAL_ITEMS_TL B
882 , FND_LANGUAGES L
883 WHERE L.INSTALLED_FLAG IN ('I', 'B')
884 AND B.LANGUAGE = USERENV('LANG')
885 AND NOT EXISTS (SELECT NULL
886 FROM JTF_CAL_ITEMS_TL T
887 WHERE T.CAL_ITEM_ID = B.CAL_ITEM_ID
888 AND T.LANGUAGE = L.LANGUAGE_CODE
889 );
890 END Add_Language;
891
892 --------------------------------------------------------------------------
893 -- Start of comments
894 -- API Name : Translate_Row
895 -- Type : Private
896 -- Description : Additional Language processing for JTF_CAL_ITEMS_B and
897 -- _TL tables. Used in the FNDLOAD definition file (.lct)
898 -- Pre-reqs : None
899 -- Parameters : None
900 -- Version : Current version 1.0
901 -- Previous version none
902 -- Initial version 1.0
903 --
904 -- Notes: :
905 --
906 -- End of comments
907 --------------------------------------------------------------------------
908 PROCEDURE Translate_Row
909 ( p_cal_item_id IN NUMBER
910 , p_item_name IN VARCHAR2
911 , p_item_description IN VARCHAR2
912 , p_owner IN VARCHAR2
913 )
914 IS
915 BEGIN
916 UPDATE JTF_CAL_ITEMS_TL
917 SET ITEM_NAME = p_item_name
918 , ITEM_DESCRIPTION = p_item_description
919 , LAST_UPDATE_DATE = SYSDATE
920 , LAST_UPDATED_BY = DECODE(p_owner, 'SEED',1,0)
921 , LAST_UPDATE_LOGIN = 0
922 , SOURCE_LANG = userenv('LANG')
923 WHERE userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
924 AND CAL_ITEM_ID = p_cal_item_id;
925 END Translate_Row;
926
927 --------------------------------------------------------------------------
928 -- Start of comments
929 -- API Name : Load_Row
930 -- Type : Private
931 -- Description : Additional Language processing for JTF_CAL_ITEMS_B and
932 -- _TL tables. Used in the FNDLOAD definition file (.lct)
933 -- Pre-reqs : None
934 -- Parameters : None
935 -- Version : Current version 1.0
936 -- Previous version none
937 -- Initial version 1.0
938 --
939 -- Notes: :
940 --
941 -- End of comments
942 --------------------------------------------------------------------------
943 PROCEDURE Load_Row
944 ( p_cal_item_id IN NUMBER
945 , p_itm_rec IN Cal_Item_rec_type
946 , p_owner IN VARCHAR2
947 )
948 IS
949 l_user_id NUMBER;
950
951 BEGIN
952 ------------------------------------------------------------------------
953 -- Determine the user_id from p_owner
954 ------------------------------------------------------------------------
955 IF (p_owner IS NOT NULL) AND (p_owner = 'SEED')
956 THEN
957 l_user_id := 1;
958 ELSE
959 l_user_id := 0;
960 END IF;
961
962 ------------------------------------------------------------------------
963 -- Try to update the record in the base table
964 ------------------------------------------------------------------------
965 UPDATE JTF_CAL_ITEMS_B
966 SET ITEM_TYPE = p_itm_rec.ITEM_TYPE
967 , ITEM_TYPE_CODE = p_itm_rec.ITEM_TYPE_CODE
968 , SOURCE_CODE = p_itm_rec.SOURCE_CODE
969 , SOURCE_ID = p_itm_rec.SOURCE_ID
970 , START_DATE = p_itm_rec.START_DATE
971 , END_DATE = p_itm_rec.END_DATE
972 , TIMEZONE_ID = p_itm_rec.TIMEZONE_ID
973 , URL = p_itm_rec.URL
974 , LAST_UPDATE_DATE = SYSDATE
975 , LAST_UPDATED_BY = l_user_id
976 , LAST_UPDATE_LOGIN = 0
977 , OBJECT_VERSION_NUMBER = p_itm_rec.OBJECT_VERSION_NUMBER
978 , APPLICATION_ID = p_itm_rec.APPLICATION_ID
979 WHERE CAL_ITEM_ID = p_cal_item_id;
980
981 ------------------------------------------------------------------------
982 -- Apparently the record doesn't exist so create it
983 ------------------------------------------------------------------------
984 IF (SQL%NOTFOUND)
985 THEN
986 INSERT INTO JTF_CAL_ITEMS_B
987 ( CAL_ITEM_ID
988 , ITEM_TYPE
989 , ITEM_TYPE_CODE
990 , SOURCE_CODE
991 , SOURCE_ID
992 , START_DATE
993 , END_DATE
994 , TIMEZONE_ID
995 , URL
996 , CREATED_BY
997 , CREATION_DATE
998 , LAST_UPDATED_BY
999 , LAST_UPDATE_DATE
1000 , LAST_UPDATE_LOGIN
1001 , OBJECT_VERSION_NUMBER
1002 , APPLICATION_ID
1003 ) VALUES
1004 ( p_cal_item_id
1005 , p_itm_rec.ITEM_TYPE
1006 , p_itm_rec.ITEM_TYPE_CODE
1007 , p_itm_rec.SOURCE_CODE
1008 , p_itm_rec.SOURCE_ID
1009 , p_itm_rec.START_DATE
1010 , p_itm_rec.END_DATE
1011 , p_itm_rec.TIMEZONE_ID
1012 , p_itm_rec.URL
1013 , l_user_id
1014 , SYSDATE
1015 , l_user_id
1016 , SYSDATE
1017 , 0
1018 , p_itm_rec.OBJECT_VERSION_NUMBER
1019 , p_itm_rec.APPLICATION_ID
1020 );
1021 END IF;
1022
1023 ------------------------------------------------------------------------
1024 -- Try to update the record in the language table
1025 ------------------------------------------------------------------------
1026 UPDATE JTF_CAL_ITEMS_TL
1027 SET ITEM_NAME = p_itm_rec.item_name
1028 , ITEM_DESCRIPTION = p_itm_rec.item_description
1029 , LAST_UPDATE_DATE = SYSDATE
1030 , LAST_UPDATED_BY = l_user_id
1031 , LAST_UPDATE_LOGIN = 0
1032 , SOURCE_LANG = userenv('LANG')
1033 WHERE CAL_ITEM_ID = p_cal_item_id
1034 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1035
1036 ------------------------------------------------------------------------
1037 -- Apparently the record doesn't exist so create it
1038 ------------------------------------------------------------------------
1039 IF (SQL%NOTFOUND)
1040 THEN
1041 INSERT INTO JTF_CAL_ITEMS_TL
1042 ( CAL_ITEM_ID
1043 , CREATED_BY
1044 , CREATION_DATE
1045 , LAST_UPDATED_BY
1046 , LAST_UPDATE_DATE
1047 , LAST_UPDATE_LOGIN
1048 , ITEM_NAME
1049 , ITEM_DESCRIPTION
1050 , LANGUAGE
1051 , SOURCE_LANG
1052 , APPLICATION_ID
1053 ) SELECT p_cal_item_id
1054 , l_user_id
1055 , SYSDATE
1056 , l_user_id
1057 , SYSDATE
1058 , 0
1059 , p_itm_rec.item_name
1060 , p_itm_rec.item_description
1061 , l.LANGUAGE_CODE
1062 , userenv('LANG')
1063 , p_itm_rec.application_id
1064 FROM FND_LANGUAGES l
1065 WHERE l.INSTALLED_FLAG IN ('I', 'B')
1066 AND NOT EXISTS( SELECT NULL
1067 FROM JTF_CAL_ITEMS_TL t
1068 WHERE t.CAL_ITEM_ID = p_cal_item_id
1069 AND t.LANGUAGE = l.LANGUAGE_CODE
1070 );
1071 END IF;
1072 END Load_Row;
1073
1074
1075
1076 END JTF_CAL_Items_PVT;