[Home] [Help]
PACKAGE BODY: APPS.JTF_NOTES_PUB
Source
1 PACKAGE BODY JTF_NOTES_PUB AS
2 /* $Header: jtfnoteb.pls 120.1 2005/07/02 00:50:14 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_NOTES_PUB';
5
6 PROCEDURE Create_note
7 ------------------------------------------------------------------------------
8 -- Create_note
9 -- Inserts a note record in the JTF_NOTES_B, JTF_NOTES_TL
10 -- and JTF_NOTE_CONTEXTS table
11 ------------------------------------------------------------------------------
12 ( p_parent_note_id IN NUMBER DEFAULT 9.99E125
13 , p_jtf_note_id IN NUMBER DEFAULT 9.99E125
14 , p_api_version IN NUMBER
15 , p_init_msg_list IN VARCHAR2 DEFAULT 'F'
16 , p_commit IN VARCHAR2 DEFAULT 'F'
17 , p_validation_level IN NUMBER DEFAULT 100
18 , x_return_status OUT NOCOPY VARCHAR2
19 , x_msg_count OUT NOCOPY NUMBER
20 , x_msg_data OUT NOCOPY VARCHAR2
21 , p_org_id IN NUMBER DEFAULT NULL
22 , p_source_object_id IN NUMBER DEFAULT 9.99E125
23 , p_source_object_code IN VARCHAR2 DEFAULT CHR(0)
24 , p_notes IN VARCHAR2 DEFAULT CHR(0)
25 , p_notes_detail IN VARCHAR2 DEFAULT NULL
26 , p_note_status IN VARCHAR2 DEFAULT 'I'
27 , p_entered_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
28 , p_entered_date IN DATE DEFAULT TO_DATE('1','j')
29 , x_jtf_note_id OUT NOCOPY NUMBER
30 , p_last_update_date IN DATE DEFAULT TO_DATE('1','j')
31 , p_last_updated_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
32 , p_creation_date IN DATE DEFAULT TO_DATE('1','j')
33 , p_created_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
34 , p_last_update_login IN NUMBER DEFAULT FND_GLOBAL.LOGIN_ID
35 , p_attribute1 IN VARCHAR2 DEFAULT NULL
36 , p_attribute2 IN VARCHAR2 DEFAULT NULL
37 , p_attribute3 IN VARCHAR2 DEFAULT NULL
38 , p_attribute4 IN VARCHAR2 DEFAULT NULL
39 , p_attribute5 IN VARCHAR2 DEFAULT NULL
40 , p_attribute6 IN VARCHAR2 DEFAULT NULL
41 , p_attribute7 IN VARCHAR2 DEFAULT NULL
42 , p_attribute8 IN VARCHAR2 DEFAULT NULL
43 , p_attribute9 IN VARCHAR2 DEFAULT NULL
44 , p_attribute10 IN VARCHAR2 DEFAULT NULL
45 , p_attribute11 IN VARCHAR2 DEFAULT NULL
46 , p_attribute12 IN VARCHAR2 DEFAULT NULL
47 , p_attribute13 IN VARCHAR2 DEFAULT NULL
48 , p_attribute14 IN VARCHAR2 DEFAULT NULL
49 , p_attribute15 IN VARCHAR2 DEFAULT NULL
50 , p_context IN VARCHAR2 DEFAULT NULL
51 , p_note_type IN VARCHAR2 DEFAULT NULL
52 , p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
53 DEFAULT jtf_note_contexts_tab_dflt
54 )
55 IS
56 l_api_name CONSTANT VARCHAR2(30) := 'Create_note';
57 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
58 l_api_version CONSTANT NUMBER := 1.0;
59
60 BEGIN
61 -- API savepoint
62 SAVEPOINT create_note_pub;
63
64 -- Check version number
65 IF NOT fnd_api.compatible_api_call
66 ( l_api_version
67 , p_api_version
68 , l_api_name
69 , g_pkg_name
70 )
71 THEN
72 RAISE fnd_api.g_exc_unexpected_error;
73 END IF;
74
75 -- Initialize message list
76 IF fnd_api.to_boolean( p_init_msg_list )
77 THEN
78 fnd_msg_pub.initialize;
79 END IF;
80
81 -- Initialize return status to SUCCESS
82 x_return_status := fnd_api.g_ret_sts_success;
83
84
85 -- Call the new Note API
86 Secure_Create_note( p_parent_note_id => p_parent_note_id
87 , p_jtf_note_id => p_jtf_note_id
88 , p_api_version => p_api_version
89 , p_init_msg_list => p_init_msg_list
90 , p_commit => fnd_api.g_false
91 , p_validation_level => p_validation_level
92 , x_return_status => x_return_status
93 , x_msg_count => x_msg_count
94 , x_msg_data => x_msg_data
95 , p_org_id => p_org_id
96 , p_source_object_id => p_source_object_id
97 , p_source_object_code => p_source_object_code
98 , p_notes => p_notes
99 , p_notes_detail => p_notes_detail
100 , p_note_status => p_note_status
101 , p_entered_by => p_entered_by
102 , p_entered_date => p_entered_date
103 , x_jtf_note_id => x_jtf_note_id
104 , p_last_update_date => p_last_update_date
105 , p_last_updated_by => p_last_updated_by
106 , p_creation_date => p_creation_date
107 , p_created_by => p_created_by
108 , p_last_update_login => p_last_update_login
109 , p_attribute1 => p_attribute1
110 , p_attribute2 => p_attribute2
111 , p_attribute3 => p_attribute3
112 , p_attribute4 => p_attribute4
113 , p_attribute5 => p_attribute5
114 , p_attribute6 => p_attribute6
115 , p_attribute7 => p_attribute7
116 , p_attribute8 => p_attribute8
117 , p_attribute9 => p_attribute9
118 , p_attribute10 => p_attribute10
119 , p_attribute11 => p_attribute11
120 , p_attribute12 => p_attribute12
121 , p_attribute13 => p_attribute13
122 , p_attribute14 => p_attribute14
123 , p_attribute15 => p_attribute15
124 , p_context => p_context
125 , p_note_type => p_note_type
126 , p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
127 , p_use_AOL_security => 'F'
128 );
129
130 IF fnd_api.to_boolean(p_commit)
131 THEN
132 COMMIT WORK;
133 END IF;
134
135 fnd_msg_pub.count_and_get( p_encoded => 'F'
136 , p_count => x_msg_count
137 , p_data => x_msg_data
138 );
139 EXCEPTION
140 WHEN fnd_api.g_exc_error
141 THEN
142 ROLLBACK TO create_note_pub;
143 x_return_status := fnd_api.g_ret_sts_error;
144 fnd_msg_pub.count_and_get( p_encoded => 'F'
145 , p_count => x_msg_count
146 , p_data => x_msg_data
147 );
148
149 WHEN fnd_api.g_exc_unexpected_error
150 THEN
151 ROLLBACK TO create_note_pub;
152 x_return_status := fnd_api.g_ret_sts_unexp_error;
153 fnd_msg_pub.count_and_get( p_encoded => 'F'
154 , p_count => x_msg_count
155 , p_data => x_msg_data
156 );
157
158 WHEN OTHERS
159 THEN
160 ROLLBACK TO create_note_pub;
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
163 THEN
164 fnd_msg_pub.add_exc_msg( g_pkg_name
165 , l_api_name
166 );
167 END IF;
168 fnd_msg_pub.count_and_get( p_encoded => 'F'
169 , p_count => x_msg_count
170 , p_data => x_msg_data
171 );
172 END Create_note;
173
174
175 PROCEDURE Update_note
176 ------------------------------------------------------------------------------
177 -- Update_note
178 -- Updates a note record in the JTF_NOTES table
179 ------------------------------------------------------------------------------
180 ( p_api_version IN NUMBER
181 , p_init_msg_list IN VARCHAR2 DEFAULT 'F'
182 , p_commit IN VARCHAR2 DEFAULT 'F'
183 , p_validation_level IN NUMBER DEFAULT 100
184 , x_return_status OUT NOCOPY VARCHAR2
185 , x_msg_count OUT NOCOPY NUMBER
186 , x_msg_data OUT NOCOPY VARCHAR2
187 , p_jtf_note_id IN NUMBER
188 , p_entered_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
189 , p_last_updated_by IN NUMBER
190 , p_last_update_date IN DATE DEFAULT SYSDATE
191 , p_last_update_login IN NUMBER DEFAULT NULL
192 , p_notes IN VARCHAR2 DEFAULT CHR(0)
193 , p_notes_detail IN VARCHAR2 DEFAULT CHR(0)
194 , p_append_flag IN VARCHAR2 DEFAULT CHR(0)
195 , p_note_status IN VARCHAR2 DEFAULT 'I'
196 , p_note_type IN VARCHAR2 DEFAULT CHR(0)
197 , p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
198 DEFAULT jtf_note_contexts_tab_dflt
199 )
200 IS
201 l_api_name CONSTANT VARCHAR2(30) := 'Update_note';
202 l_api_version CONSTANT NUMBER := 1.0;
203 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
204
205 BEGIN
206 --
207 -- Standard start of API savepoint
208 --
209 SAVEPOINT update_note_pub;
210
211 --
212 -- Standard call to check for call compatibility
213 --
214 IF NOT fnd_api.compatible_api_call( l_api_version
215 , p_api_version
216 , l_api_name, g_pkg_name
217 )
218 THEN
219 RAISE fnd_api.g_exc_unexpected_error;
220 END IF;
221
222 --
223 -- Initialize message list if p_init_msg_list is set to TRUE
224 --
225 IF fnd_api.to_boolean(p_init_msg_list)
226 THEN
227 fnd_msg_pub.initialize;
228 END IF;
229
230 --
231 -- Initialize API return status to success
232 --
233 x_return_status := fnd_api.g_ret_sts_success;
234
235
236 -- Call the new API
237 Secure_Update_note( p_api_version => p_api_version
238 , p_init_msg_list => p_init_msg_list
239 , p_commit => fnd_api.g_false
240 , p_validation_level => p_validation_level
241 , x_return_status => x_return_status
242 , x_msg_count => x_msg_count
243 , x_msg_data => x_msg_data
244 , p_jtf_note_id => p_jtf_note_id
245 , p_entered_by => p_entered_by
246 , p_last_updated_by => p_last_updated_by
247 , p_last_update_date => p_last_update_date
248 , p_last_update_login => p_last_update_login
249 , p_notes => p_notes
250 , p_notes_detail => p_notes_detail
251 , p_append_flag => p_append_flag
252 , p_note_status => p_note_status
253 , p_note_type => p_note_type
254 , p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
255 , p_use_AOL_security => 'F'
256 );
257
258 -- Standard check of p_commit
259 IF fnd_api.to_boolean(p_commit)
260 THEN
261 COMMIT WORK;
262 END IF;
263
264 -- Standard call to get message count and if count is 1, get message info
265 fnd_msg_pub.count_and_get( p_encoded => 'F'
266 , p_count => x_msg_count
267 , p_data => x_msg_data
268 );
269
270 EXCEPTION
271 WHEN fnd_api.g_exc_error
272 THEN
273 ROLLBACK TO update_note_pub;
274
275 x_return_status := fnd_api.g_ret_sts_error;
276
277 fnd_msg_pub.count_and_get( p_encoded => 'F'
278 , p_count => x_msg_count
279 , p_data => x_msg_data
280 );
281 WHEN fnd_api.g_exc_unexpected_error
282 THEN
283 ROLLBACK TO update_note_pub;
284
285 x_return_status := fnd_api.g_ret_sts_unexp_error;
286
287 fnd_msg_pub.count_and_get( p_encoded => 'F'
288 , p_count => x_msg_count
289 , p_data => x_msg_data
290 );
291 WHEN OTHERS
292 THEN
293 ROLLBACK TO update_note_pub;
294
295 x_return_status := fnd_api.g_ret_sts_unexp_error;
296
297 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
298 THEN
299 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
300 END IF;
301
302 fnd_msg_pub.count_and_get( p_encoded => 'F'
303 , p_count => x_msg_count
304 , p_data => x_msg_data
305 );
306 END Update_note;
307
308
309 PROCEDURE Validate_note_type
310 ------------------------------------------------------------------------------
311 -- Procedure : Validate_note_type
312 ------------------------------------------------------------------------------
313 ( p_api_name IN VARCHAR2
314 , p_parameter_name IN VARCHAR2
315 , p_note_type IN VARCHAR2
316 , x_return_status OUT NOCOPY VARCHAR2
317 )
318 IS
319 l_dummy VARCHAR2(1);
320 BEGIN
321 x_return_status := fnd_api.g_ret_sts_success;
322
323 SELECT 'x' INTO l_dummy
324 FROM fnd_lookup_values
325 WHERE lookup_code = p_note_type
326 AND lookup_type = 'JTF_NOTE_TYPE'
327 AND language = USERENV('LANG');
328
329 EXCEPTION
330 WHEN TOO_MANY_ROWS
331 THEN
332 NULL;
333 WHEN NO_DATA_FOUND
334 THEN
335 x_return_status := fnd_api.g_ret_sts_error;
336 add_invalid_argument_msg( p_api_name
337 , p_note_type
338 , p_parameter_name
339 );
340 WHEN OTHERS
341 THEN
342 x_return_status := fnd_api.g_ret_sts_error;
343 add_invalid_argument_msg( p_api_name
344 , p_note_type
345 , p_parameter_name
346 );
347
348
349 END Validate_note_type;
350
351 PROCEDURE Create_note_context
352 ------------------------------------------------------------------------------
353 -- Procedure : Create_note_context
354 ------------------------------------------------------------------------------
355 ( p_validation_level IN NUMBER DEFAULT 100
356 , x_return_status OUT NOCOPY VARCHAR2
357 , p_jtf_note_id IN NUMBER
358 , p_last_update_date IN DATE
359 , p_last_updated_by IN NUMBER
360 , p_creation_date IN DATE
361 , p_created_by IN NUMBER DEFAULT FND_GLOBAL.USER_ID
362 , p_last_update_login IN NUMBER DEFAULT FND_GLOBAL.LOGIN_ID
363 , p_note_context_type_id IN NUMBER DEFAULT 9.99E125
364 , p_note_context_type IN VARCHAR2 DEFAULT CHR(0)
365 , x_note_context_id OUT NOCOPY NUMBER
366 )
367 IS
368 l_api_name CONSTANT VARCHAR2(30) := 'Create_note_context';
369 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
370 l_api_version CONSTANT NUMBER := 1.0;
371 l_jtf_note_id NUMBER := NULL;
372 l_note_context_id NUMBER := NULL;
373 l_return_status VARCHAR2(1);
374 l_last_update_date DATE;
375 l_creation_date DATE;
376 l_dummy VARCHAR2(1);
377
378 l_insert_failure EXCEPTION;
379
380 -- Cursor for retrieving from the table to verify insertion
381 CURSOR c_inserted
382 (b_note_context_id IN NUMBER
383 )IS SELECT 'x'
384 FROM jtf_note_contexts
385 WHERE note_context_id = b_note_context_id;
386
387 -- Cursor that will check for duplicates
388 CURSOR c_duplicate
389 ( b_jtf_note_id IN NUMBER
390 , b_note_context_type IN VARCHAR2
391 , b_note_context_type_id IN NUMBER
392 )IS SELECT note_context_id
393 FROM jtf_note_contexts
394 WHERE jtf_note_id = b_jtf_note_id
395 AND note_context_type = b_note_context_type
396 AND note_context_type_id = b_note_context_type_id;
397
398 BEGIN
399
400 -- Initialize return status to SUCCESS
401 x_return_status := fnd_api.g_ret_sts_success;
402
403 -- Defaulting
404 IF p_last_update_date = fnd_api.g_miss_date
405 THEN
406 l_last_update_date := (SYSDATE);
407 ELSE
408 l_last_update_date := p_last_update_date;
409 END IF;
410
411 IF p_creation_date = fnd_api.g_miss_date
412 THEN
413 l_creation_date := (SYSDATE);
414 ELSE
415 l_creation_date := p_creation_date;
416 END IF;
417
418 -- Validation
419 IF (p_validation_level > fnd_api.g_valid_level_none)
420 THEN
421 --Validate note_context_id based on the note_context_type
422 IF ( p_note_context_type IS NULL
423 AND p_note_context_type_id IS NOT NULL
424 )
425 OR
426 ( p_note_context_type_id IS NULL
427 AND p_note_context_type IS NOT NULL
428 )
429 THEN
430 RAISE fnd_api.g_exc_error;
431 END IF;
432
433 -- Validate source object id and code
434 Validate_object( p_api_name => l_api_name_full
435 , p_object_type_code => p_note_context_type
436 , p_object_type_id => p_note_context_type_id
437 , x_return_status => l_return_status
438 );
439
440 IF (l_return_status <> fnd_api.g_ret_sts_success)
441 THEN
442 RAISE fnd_api.g_exc_error;
443 END IF;
444 END IF;
445
446 -- we should only do the insert if the relation doesn't already exist
447 OPEN c_duplicate( p_jtf_note_id
448 , p_note_context_type
449 , p_note_context_type_id
450 );
451
452 FETCH c_duplicate INTO l_note_context_id;
453
454 IF (c_duplicate%NOTFOUND)
455 THEN
456 INSERT INTO jtf_note_contexts
457 ( note_context_id
458 , jtf_note_id
459 , note_context_type_id
460 , note_context_type
461 , last_update_date
462 , last_updated_by
463 , creation_date
464 , created_by
465 , last_update_login
466 )
467 VALUES
468 ( jtf_notes_s.NEXTVAL
469 , p_jtf_note_id
470 , p_note_context_type_id
471 , p_note_context_type
472 , l_last_update_date
473 , p_last_updated_by
474 , l_creation_date
475 , p_created_by
476 , p_last_update_login
477 )
478 RETURNING note_context_id INTO l_note_context_id;
479
480 -- Retrieve from the table to verify insertion
481 OPEN c_inserted(l_note_context_id);
482
483 FETCH c_inserted INTO l_dummy;
484
485 IF (c_inserted%NOTFOUND)
486 THEN
487 -- Insert failed, raise error
488 IF (c_inserted%ISOPEN)
489 THEN
490 CLOSE c_inserted;
491 END IF;
492
493 IF (c_duplicate%ISOPEN)
494 THEN
495 CLOSE c_duplicate;
496 END IF;
497
498 RAISE l_insert_failure;
499 ELSE
500 -- Insert was succesfull
501 IF (c_inserted%ISOPEN)
502 THEN
503 CLOSE c_inserted;
504 END IF;
505
506 IF (c_duplicate%ISOPEN)
507 THEN
508 CLOSE c_duplicate;
509 END IF;
510 -- return the context_id
511 x_note_context_id := l_note_context_id;
512 END IF;
513 ELSE
514 -- pretend the insert was succesfull and return the ID for the (first)
515 -- duplicate record
516 IF (c_inserted%ISOPEN)
517 THEN
518 CLOSE c_inserted;
519 END IF;
520
521 IF (c_duplicate%ISOPEN)
522 THEN
523 CLOSE c_duplicate;
524 END IF;
525
526 x_note_context_id := l_note_context_id;
527
528 END IF;
529
530 END Create_Note_Context;
531
532 PROCEDURE Update_note_context
533 ------------------------------------------------------------------------------
534 -- Procedure : Update_note_context
535 --
536 -- Updates a context record in the JTF_NOTE_CONTEXTS table
537 ------------------------------------------------------------------------------
538 ( p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
539 , x_return_status OUT NOCOPY VARCHAR2
540 , p_note_context_id IN NUMBER
541 , p_jtf_note_id IN NUMBER
542 , p_note_context_type_id IN NUMBER
543 , p_note_context_type IN VARCHAR2
544 , p_last_updated_by IN NUMBER
545 , p_last_update_date IN DATE DEFAULT SYSDATE
546 , p_last_update_login IN NUMBER DEFAULT NULL
547 )
548 IS
549 l_api_name CONSTANT VARCHAR2(30) := 'Update_Note_Context';
550 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
551 l_return_status VARCHAR2(1);
552 l_note_context_type VARCHAR2(240);
553 l_note_context_type_id NUMBER;
554
555 CURSOR lrec_note_context
556 IS SELECT *
557 FROM JTF_NOTE_CONTEXTS
558 WHERE note_context_id = p_note_context_id
559 FOR UPDATE OF note_context_id;
560
561 l_context_rec lrec_note_context%ROWTYPE;
562
563 BEGIN
564 -- Fetch and get the original values
565 OPEN lrec_note_context;
566
567 FETCH lrec_note_context INTO l_context_rec;
568
569 IF (lrec_note_context%notfound)
570 THEN
571 add_invalid_argument_msg( l_api_name_full
572 , p_jtf_note_id
573 , 'p_jtf_note_id'
574 );
575
576 IF (lrec_note_context%ISOPEN)
577 THEN
578 CLOSE lrec_note_context;
579 END IF;
580
581 RAISE fnd_api.g_exc_error;
582
583 END IF;
584
585 --
586 -- For each column in the table, we have a corresponding local variable.
587 -- These local variables are used in the actual UPDATE SQL statement. If a
588 -- column is being updated, we initialize the corresponding local variable
589 -- to the value of the parameter that is passed in; otherwise, the local
590 -- variable is set to the original value in the database.
591 --
592
593 IF ( p_note_context_type IS NULL)
594 THEN
595 l_note_context_type := l_context_rec.note_context_type;
596 ELSE
597 l_note_context_type := p_note_context_type;
598 END IF;
599
600 IF ( p_note_context_type_id IS NULL)
601 THEN
602 l_note_context_type_id := l_context_rec.note_context_type_id;
603 ELSE
604 l_note_context_type_id := p_note_context_type_id;
605 END IF;
606
607 IF (p_validation_level > fnd_api.g_valid_level_none)
608 THEN
609 -- Validate source object id and code
610 Validate_object( p_api_name => l_api_name_full
611 , p_object_type_code => p_note_context_type
612 , p_object_type_id => p_note_context_type_id
613 , x_return_status => l_return_status
614 );
615 IF (l_return_status <> fnd_api.g_ret_sts_success)
616 THEN
617 RAISE fnd_api.g_exc_error;
618 END IF;
619 END IF;
620
621 --
622 -- Perform the database operation.
623 --
624 UPDATE JTF_NOTE_CONTEXTS
625 SET last_updated_by = p_last_updated_by
626 , last_update_date = p_last_update_date
627 , last_update_login = p_last_update_login
628 , note_context_type_id = l_note_context_type_id
629 , note_context_type = l_note_context_type
630 WHERE CURRENT OF lrec_note_context;
631
632 IF (lrec_note_context%ISOPEN)
633 THEN
634 CLOSE lrec_note_context;
635 END IF;
636
637 EXCEPTION
638 WHEN OTHERS
639 THEN
640 IF (lrec_note_context%ISOPEN)
641 THEN
642 CLOSE lrec_note_context;
643 END IF;
644
645 RAISE fnd_api.g_exc_unexpected_error;
646
647 END Update_note_Context;
648
649 PROCEDURE Add_Invalid_Argument_Msg
650 ------------------------------------------------------------------------------
651 -- Procedure : Add_Invalid_Argument_Msg
652 ------------------------------------------------------------------------------
653 ( p_token_an IN VARCHAR2
654 , p_token_v IN VARCHAR2
655 , p_token_p IN VARCHAR2
656 )
657 IS
658 BEGIN
659 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
660 THEN
661 fnd_message.set_name('JTF', 'JTF_API_ALL_INVALID_ARGUMENT');
662 fnd_message.set_token('API_NAME', p_token_an);
663 fnd_message.set_token('VALUE', p_token_v);
664 fnd_message.set_token('PARAMETER', p_token_p);
665 fnd_msg_pub.add;
666 END IF;
667 END Add_Invalid_Argument_Msg;
668
669
670 PROCEDURE Add_Null_Parameter_Msg
671 ------------------------------------------------------------------------------
672 -- Procedure : Add_Null_Parameter_Msg
673 ------------------------------------------------------------------------------
674 ( p_token_an IN VARCHAR2
675 , p_token_np IN VARCHAR2
676 )
677 IS
678 BEGIN
679 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
680 THEN
681 fnd_message.set_name('JTF', 'JTF_API_ALL_NULL_PARAMETER');
682 fnd_message.set_token('API_NAME', p_token_an);
683 fnd_message.set_token('NULL_PARAM', p_token_np);
684 fnd_msg_pub.add;
685 END IF;
686 END Add_Null_Parameter_Msg;
687
688 PROCEDURE Trunc_String_length
689 ------------------------------------------------------------------------------
690 -- Procedure : Trunc_String_Length
691 ------------------------------------------------------------------------------
692 ( p_api_name IN VARCHAR2
693 , p_parameter_name IN VARCHAR2
694 , p_str IN VARCHAR2
695 , p_len IN NUMBER
696 , x_str OUT NOCOPY VARCHAR2
697 )
698 IS
699 l_len NUMBER;
700
701 BEGIN
702 l_len := LENGTHB(p_str);
703 IF (l_len > p_len)
704 THEN
705 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_success)
706 THEN
707 fnd_message.set_name('JTF', 'JTF_API_ALL_VALUE_TRUNCATED');
708 fnd_message.set_token('API_NAME', p_api_name);
709 fnd_message.set_token('TRUNCATED_PARAM', p_parameter_name);
710 fnd_message.set_token('VAL_LEN', l_len);
711 fnd_message.set_token('DB_LEN', p_len);
712 fnd_msg_pub.add;
713 END IF;
714 x_str := substrb(p_str, 1, p_len);
715 ELSE
716 x_str := p_str;
717 END IF;
718 END Trunc_String_Length;
719
720
721
722 PROCEDURE Add_Missing_Param_Msg
723 ------------------------------------------------------------------------------
724 -- Procedure : Add_Missing_Param_Msg
725 ------------------------------------------------------------------------------
726 ( p_token_an IN VARCHAR2
727 , p_token_mp IN VARCHAR2
728 )
729 IS
730 BEGIN
731 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
732 THEN
733 fnd_message.set_name('JTF', 'JTF_API_ALL_MISSING_PARAM');
734 fnd_message.set_token('API_NAME', p_token_an);
735 fnd_message.set_token('MISSING_PARAM', p_token_mp);
736 fnd_msg_pub.add;
737 END IF;
738 END Add_MIssing_Param_Msg;
739
740 PROCEDURE Validate_object
741 ------------------------------------------------------------------------------
742 -- Procedure : Validate_object
743 ------------------------------------------------------------------------------
744 ( p_api_name IN VARCHAR2
745 , p_object_type_code IN VARCHAR2
746 , p_object_type_id IN NUMBER
747 , x_return_status OUT NOCOPY VARCHAR2
748 )
749 IS
750 l_return_status VARCHAR2(30);
751 l_result NUMBER := 0;
752 l_select_id VARCHAR2(200);
753 l_tablename VARCHAR2(200);
754 l_where_clause VARCHAR2(2000);
755 v_cursor NUMBER;
756 v_create_string VARCHAR2(32000);
757 v_numrows NUMBER;
758
759 CURSOR cur_object
760 IS SELECT select_id
761 , from_table
762 , where_clause
763 FROM jtf_objects_vl a
764 , jtf_object_usages b
765 WHERE a.object_code = p_object_type_code
766 AND a.object_code = b.object_code
767 AND b.object_user_code = 'NOTES';
768
769 BEGIN
770 OPEN cur_object;
771 FETCH cur_object INTO l_select_id,l_tablename,l_where_clause ;
772 CLOSE cur_object;
773
774 IF l_where_clause IS NULL
775 THEN
776 v_create_string := 'SELECT COUNT(*) FROM '||l_tablename||
777 ' WHERE '||l_select_id||' = :object_type_id ';
778 ELSE
779 v_create_string := 'SELECT COUNT(*) FROM '||l_tablename||
780 ' WHERE '||l_where_clause||
781 ' AND '||l_select_id||' = :object_type_id ';
782 END IF;
783
784 EXECUTE IMMEDIATE v_create_string
785 INTO l_result
786 USING p_object_type_id;
787
788
789 IF (l_result > 0)
790 THEN
791 x_return_status := fnd_api.g_ret_sts_success;
792 ELSE
793 add_invalid_argument_msg( p_api_name
794 , p_object_type_code
795 , 'Object Code'
796 );
797
798 add_invalid_argument_msg( p_api_name
799 , p_object_type_id
800 , 'Object Id'
801 );
802 x_return_status := fnd_api.g_ret_sts_error;
803 END IF;
804
805 EXCEPTION
806 WHEN OTHERS
807 THEN
808 IF (cur_object%ISOPEN)
809 THEN
810 CLOSE cur_object;
811 END IF;
812 x_return_status := fnd_api.g_ret_sts_error;
813 add_invalid_argument_msg( p_api_name
814 , p_object_type_code
815 , 'Object Code'
816 );
817 add_invalid_argument_msg( p_api_name
818 , p_object_type_id
819 , 'Object Id'
820 );
821 END Validate_Object;
822
823
824 PROCEDURE writeDatatoLob
825 ------------------------------------------------------------------------------
826 -- Procedure : writeDatatoLob
827 ------------------------------------------------------------------------------
828 ( x_jtf_note_id IN NUMBER
829 , x_buffer IN VARCHAR2
830 )
831 IS
832
833 Position INTEGER := 1;
834
835 CURSOR c1
836 IS SELECT notes_detail
837 FROM jtf_notes_tl
838 WHERE jtf_note_id = x_jtf_note_id
839 FOR UPDATE;
840
841 BEGIN
842 FOR i IN c1
843 LOOP
844 DBMS_LOB.WRITE(i.notes_detail,LENGTH(x_buffer),position,x_buffer);
845 END LOOP;
846 END WriteDataToLob;
847
848
849 PROCEDURE writeLobToData
850 ------------------------------------------------------------------------------
851 -- Procedure : writeLobToData
852 ------------------------------------------------------------------------------
853 ( x_jtf_note_id NUMBER
854 , x_buffer OUT NOCOPY VARCHAR2
855 )
856 IS
857 lob_loc CLOB;
858 Amount BINARY_INTEGER := 32767;
859 Position INTEGER := 1;
860 Buffer VARCHAR2(32767);
861 Chunksize INTEGER;
862
863 BEGIN
864
865 SELECT notes_detail
866 INTO lob_loc
867 FROM jtf_notes_vl
868 WHERE jtf_note_id = x_jtf_note_id;
869
870 Chunksize := DBMS_LOB.GETCHUNKSIZE(lob_loc);
871
872 IF Chunksize IS NOT NULL
873 THEN
874 IF chunksize < 32767
875 THEN
876 amount := (32767/chunksize) * chunksize;
877 END IF;
878
879 DBMS_LOB.READ(lob_loc,amount,position,buffer);
880
881 END IF;
882
883 x_buffer := buffer;
884
885 EXCEPTION
886 WHEN NO_DATA_FOUND
887 THEN
888 x_buffer := NULL;
889 WHEN TOO_MANY_ROWS
890 THEN
891 x_buffer := NULL;
892 END WriteLobtoData;
893
894
895 PROCEDURE validate_entered_by
896 ------------------------------------------------------------------------------
897 -- Procedure : writeDatatoLob
898 ------------------------------------------------------------------------------
899 ( p_entered_by IN NUMBER
900 , x_return_status OUT NOCOPY VARCHAR2
901 , x_entered_by OUT NOCOPY NUMBER
902 )
903 IS
904 CURSOR c_entered_by
905 IS SELECT user_id
906 FROM fnd_user
907 WHERE user_id = p_entered_by
908 AND NVL (end_date, SYSDATE) >= SYSDATE
909 AND NVL (start_date, SYSDATE) <= SYSDATE;
910
911 BEGIN
912 x_return_status := fnd_api.g_ret_sts_success;
913
914 IF p_entered_by IS NOT NULL
915 THEN
916 OPEN c_entered_by;
917 FETCH c_entered_by INTO x_entered_by;
918
919 IF (c_entered_by%NOTFOUND)
920 THEN
921 fnd_message.set_name('JTF', 'JTF_API_ALL_INVALID_ARGUMENT');
922 fnd_message.set_token('API_NAME', 'JTF_NOTES_PUB.CREATE_NOTE');
923 fnd_message.set_token('PARAMETER', 'p_entered_by');
924 fnd_message.set_token('VALUE', p_entered_by);
925 fnd_msg_pub.ADD;
926 x_return_status := fnd_api.g_ret_sts_unexp_error;
927
928 IF (c_entered_by%ISOPEN)
929 THEN
930 CLOSE c_entered_by;
931 END IF;
932
933 RAISE fnd_api.g_exc_unexpected_error;
934 END IF;
935
936 IF (c_entered_by%ISOPEN)
937 THEN
938 CLOSE c_entered_by;
939 END IF;
940 END IF;
941 EXCEPTION
942 WHEN OTHERS
943 THEN
944 IF (c_entered_by%ISOPEN)
945 THEN
946 CLOSE c_entered_by;
947 END IF;
948
949 RAISE fnd_api.g_exc_unexpected_error;
950
951 END validate_entered_by;
952
953 PROCEDURE Secure_Delete_note
954 -- --------------------------------------------------------------------------
955 -- Start of notes
956 -- API Name : Delete_Note will only work when the user is granted the
957 -- JTF_NOTE_DELETE privilege through AOL security framework
958 -- Type : Public
959 -- Usage : Deletes a note record in the table JTF_NOTES_B/JTF_NOTES_TL
960 -- and JTF_NOTE_CONTEXTS
961 -- Pre-reqs : None
962 -- Parameters :
963 -- p_api_version IN NUMBER Required
964 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
965 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
966 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
967 -- x_return_status OUT VARCHAR2 Required
968 -- x_msg_count OUT NUMBER Required
969 -- x_msg_data OUT VARCHAR2 Required
970 -- p_jtf_note_id IN NUMBER Required Primary key of the note record
971 -- p_use_AOL_security IN VARCHAR2 Optional Default FND_API.G_TRUE
972 --
973 -- Version : Initial version 1.0
974 --
975 --
976 -- End of notes
977 -- --------------------------------------------------------------------------
978 ( p_api_version IN NUMBER
979 , p_init_msg_list IN VARCHAR2 DEFAULT 'F'
980 , p_commit IN VARCHAR2 DEFAULT 'F'
981 , p_validation_level IN NUMBER DEFAULT 100
982 , x_return_status OUT NOCOPY VARCHAR2
983 , x_msg_count OUT NOCOPY NUMBER
984 , x_msg_data OUT NOCOPY VARCHAR2
985 , p_jtf_note_id IN NUMBER
986 , p_use_AOL_security IN VARCHAR2 DEFAULT 'T'
987 )IS
988 l_api_name CONSTANT VARCHAR2(30) := 'Delete_note';
989 l_api_version CONSTANT NUMBER := 1.0;
990 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
991
992 l_return_status VARCHAR2(1);
993 l_msg_count NUMBER;
994 l_msg_data VARCHAR2(2000);
995 l_note_delete NUMBER;
996
997 l_source_object_code VARCHAR2(240);
998 l_source_object_id NUMBER;
999
1000
1001 BEGIN
1002 -- API savepoint
1003 SAVEPOINT delete_note_pub;
1004
1005 --
1006 -- Standard call to check for call compatibility
1007 --
1008 IF NOT FND_API.Compatible_API_Call( l_api_version
1009 , p_api_version
1010 , l_api_name
1011 , G_PKG_NAME
1012 )
1013 THEN
1014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1015 END IF;
1016
1017 --
1018 -- Initialize message list if p_init_msg_list is set to TRUE
1019 --
1020 IF FND_API.To_Boolean(p_init_msg_list)
1021 THEN
1022 FND_MSG_PUB.Initialize;
1023 END IF;
1024
1025 --
1026 -- Initialize API return status to success
1027 --
1028 x_return_status := FND_API.G_RET_STS_SUCCESS;
1029
1030 --
1031 -- Security validations
1032 --
1033 IF (p_use_AOL_security = fnd_api.g_true)
1034 THEN
1035 --
1036 -- Check if the user is allowed to delete this note
1037 --
1038 JTF_NOTES_SECURITY_PVT.check_function
1039 ( p_api_version => 1.0
1040 , p_init_msg_list => FND_API.G_FALSE
1041 , p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_DELETE
1042 , p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
1043 , p_instance_pk1_value => p_jtf_note_id
1044 , x_grant => l_note_delete
1045 , x_return_status => l_return_status
1046 , x_msg_count => l_msg_count
1047 , x_msg_data => l_msg_data
1048 );
1049
1050 --
1051 -- If there's an error push it onto the stack
1052 --
1053 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1054 THEN
1055 FND_MESSAGE.SET_NAME('JTF', 'JTF_UNABLE_TO_CHECK_FUNCTION'); -- Unable to verify whether Security &FUNCTION function was granted
1056 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_DELETE');
1057 FND_MSG_PUB.ADD;
1058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1059 END IF;
1060
1061 --
1062 -- If the create function was not granted throw an error
1063 --
1064 IF (l_note_delete = 0)
1065 THEN
1066 FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
1067 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_DELETE');
1068 FND_MSG_PUB.ADD;
1069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1070 END IF;
1071 END IF; -- end of Security validations
1072
1073 --
1074 -- get the source onject information so we can pas it to the WF event
1075 --
1076 SELECT source_object_code
1077 , source_object_id
1078 INTO l_source_object_code
1079 , l_source_object_id
1080 FROM jtf_notes_b
1081 WHERE jtf_note_id = p_jtf_note_id;
1082
1083 --
1084 -- Delete the note and it's references
1085 --
1086 DELETE FROM jtf_note_contexts WHERE jtf_note_id = p_jtf_note_id;
1087 DELETE FROM jtf_notes_tl WHERE jtf_note_id = p_jtf_note_id;
1088 DELETE FROM jtf_notes_b WHERE jtf_note_id = p_jtf_note_id;
1089
1090 JTF_NOTES_EVENTS_PVT.RaiseDeleteNote
1091 ( p_NoteID => p_jtf_note_id
1092 , p_SourceObjectCode => l_source_object_code
1093 , p_SourceObjectID => l_source_object_id
1094 );
1095
1096 -- Standard check of p_commit
1097 IF fnd_api.to_boolean(p_commit)
1098 THEN
1099 COMMIT WORK;
1100 END IF;
1101
1102 --
1103 -- Standard call to get message count and if count is 1, get message info
1104 --
1105 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1106 , p_data => x_msg_data
1107 );
1108
1109 EXCEPTION
1110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1111 THEN
1112 ROLLBACK TO delete_note_pub;
1113 --
1114 -- Set status
1115 --
1116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1117
1118 --
1119 -- Get error message from FND stack
1120 --
1121 l_msg_data := FND_MESSAGE.GET;
1122
1123 --
1124 -- Push message onto CRM stack
1125 --
1126 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1127 , l_api_name
1128 , l_msg_data
1129 );
1130 --
1131 -- Count the messages on the CRM stack
1132 --
1133 x_msg_count := FND_MSG_PUB.COUNT_MSG;
1134
1135 WHEN OTHERS
1136 THEN
1137 ROLLBACK TO delete_note_pub;
1138 --
1139 -- Set status
1140 --
1141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1142
1143 --
1144 -- Push message onto CRM stack
1145 --
1146 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1147 , l_api_name
1148 , SQLERRM
1149 );
1150 --
1151 -- Count the messages on the CRM stack
1152 --
1153 x_msg_count := FND_MSG_PUB.COUNT_MSG;
1154
1155 END Secure_Delete_Note;
1156
1157
1158 PROCEDURE Secure_Create_note
1159 ------------------------------------------------------------------------------
1160 -- Create_note
1161 -- Inserts a note record in the JTF_NOTES_B, JTF_NOTES_TL
1162 -- and JTF_NOTE_CONTEXTS table
1163 ------------------------------------------------------------------------------
1164 ( p_parent_note_id IN NUMBER DEFAULT 9.99E125
1165 , p_jtf_note_id IN NUMBER DEFAULT 9.99E125
1166 , p_api_version IN NUMBER
1167 , p_init_msg_list IN VARCHAR2 DEFAULT 'F'
1168 , p_commit IN VARCHAR2 DEFAULT 'F'
1169 , p_validation_level IN NUMBER DEFAULT 100
1170 , x_return_status OUT NOCOPY VARCHAR2
1171 , x_msg_count OUT NOCOPY NUMBER
1172 , x_msg_data OUT NOCOPY VARCHAR2
1173 , p_org_id IN NUMBER DEFAULT NULL
1174 , p_source_object_id IN NUMBER DEFAULT 9.99E125
1175 , p_source_object_code IN VARCHAR2 DEFAULT CHR(0)
1176 , p_notes IN VARCHAR2 DEFAULT CHR(0)
1177 , p_notes_detail IN VARCHAR2 DEFAULT NULL
1178 , p_note_status IN VARCHAR2 DEFAULT 'I'
1179 , p_entered_by IN NUMBER DEFAULT fnd_global.user_id
1180 , p_entered_date IN DATE DEFAULT TO_DATE('1','j')
1181 , x_jtf_note_id OUT NOCOPY NUMBER
1182 , p_last_update_date IN DATE DEFAULT TO_DATE('1','j')
1183 , p_last_updated_by IN NUMBER DEFAULT fnd_global.user_id
1184 , p_creation_date IN DATE DEFAULT TO_DATE('1','j')
1185 , p_created_by IN NUMBER DEFAULT fnd_global.user_id
1186 , p_last_update_login IN NUMBER DEFAULT fnd_global.login_id
1187 , p_attribute1 IN VARCHAR2 DEFAULT NULL
1188 , p_attribute2 IN VARCHAR2 DEFAULT NULL
1189 , p_attribute3 IN VARCHAR2 DEFAULT NULL
1190 , p_attribute4 IN VARCHAR2 DEFAULT NULL
1191 , p_attribute5 IN VARCHAR2 DEFAULT NULL
1192 , p_attribute6 IN VARCHAR2 DEFAULT NULL
1193 , p_attribute7 IN VARCHAR2 DEFAULT NULL
1194 , p_attribute8 IN VARCHAR2 DEFAULT NULL
1195 , p_attribute9 IN VARCHAR2 DEFAULT NULL
1196 , p_attribute10 IN VARCHAR2 DEFAULT NULL
1197 , p_attribute11 IN VARCHAR2 DEFAULT NULL
1198 , p_attribute12 IN VARCHAR2 DEFAULT NULL
1199 , p_attribute13 IN VARCHAR2 DEFAULT NULL
1200 , p_attribute14 IN VARCHAR2 DEFAULT NULL
1201 , p_attribute15 IN VARCHAR2 DEFAULT NULL
1202 , p_context IN VARCHAR2 DEFAULT NULL
1203 , p_note_type IN VARCHAR2 DEFAULT NULL
1204 , p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
1205 DEFAULT jtf_note_contexts_tab_dflt
1206 , p_use_AOL_security IN VARCHAR2 DEFAULT 'T'
1207
1208 )
1209 IS
1210 l_api_name CONSTANT VARCHAR2(30) := 'Secure_Create_note';
1211 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
1212 l_api_version CONSTANT NUMBER := 1.0;
1213
1214 l_note_status VARCHAR2(1) := p_note_status;
1215 l_jtf_note_id NUMBER := p_jtf_note_id;
1216 l_note_context_id NUMBER := NULL;
1217 l_return_status VARCHAR2(1);
1218 l_entered_by NUMBER := p_entered_by;
1219 l_entered_date DATE := p_entered_date;
1220 l_last_update_date DATE := p_last_update_date;
1221 l_last_updated_by NUMBER := p_last_updated_by;
1222 l_creation_date DATE;
1223 l_parent_note_id NUMBER;
1224
1225 l_dummy VARCHAR2(1);
1226 l_notes_detail VARCHAR2(32767) := p_notes_detail;
1227 l_notes VARCHAR2(32767) := p_notes;
1228 l_rowid ROWID;
1229 l_msg_count NUMBER ;
1230 l_msg_data VARCHAR2(2000);
1231 l_source_object_id NUMBER := p_source_object_id;
1232 l_source_object_code VARCHAR2(240) := p_source_object_code;
1233 l_bind_data_id NUMBER;
1234
1235 l_grant_select NUMBER := 1;
1236 l_grant_select_type NUMBER := 1;
1237
1238 -- Used for keeping track of errors
1239 l_missing_param VARCHAR2(30) := NULL;
1240 l_null_param VARCHAR2(30) := NULL;
1241
1242
1243
1244 -- Cursor for getting the note ID from the sequence
1245 CURSOR l_jtf_note_id_csr
1246 IS SELECT JTF_NOTES_S.NEXTVAL
1247 FROM DUAL;
1248
1249 -- Cursor for retrieving from the table to verify insertion
1250 CURSOR l_insert_check_csr
1251 IS SELECT 'x'
1252 FROM JTF_NOTES_B
1253 WHERE jtf_note_id = l_jtf_note_id;
1254
1255
1256 -- Local exceptions
1257 l_missing_parameter EXCEPTION;
1258 l_null_parameter EXCEPTION;
1259 l_insert_failure EXCEPTION;
1260 l_duplicate_note EXCEPTION;
1261
1262 BEGIN
1263 -- API savepoint
1264 SAVEPOINT create_note_pvt;
1265
1266 -- Check version number
1267 IF NOT fnd_api.compatible_api_call
1268 ( l_api_version
1269 , p_api_version
1270 , l_api_name
1271 , g_pkg_name
1272 )
1273 THEN
1274 RAISE fnd_api.g_exc_unexpected_error;
1275 END IF;
1276
1277 -- Initialize message list
1278 IF fnd_api.to_boolean( p_init_msg_list )
1279 THEN
1280 fnd_msg_pub.initialize;
1281 END IF;
1282
1283 -- Initialize return status to SUCCESS
1284 x_return_status := fnd_api.g_ret_sts_success;
1285
1286 --
1287 -- Make the preprocessing call to the user hooks
1288 --
1289 -- Pre call to the Customer Type User Hook
1290 --
1291 IF jtf_usr_hks.ok_to_execute('JTF_NOTES_PUB'
1292 ,'Create_Note'
1293 ,'B'
1294 ,'C'
1295 )
1296 THEN
1297 jtf_notes_cuhk.create_note_pre
1298 ( p_parent_note_id => p_parent_note_id
1299 , p_api_version => p_api_version
1300 , p_init_msg_list => p_init_msg_list
1301 , p_commit => FND_API.G_FALSE
1302 , p_validation_level => p_validation_level
1303 , x_msg_count => x_msg_count
1304 , x_msg_data => x_msg_data
1305 , p_org_id => p_org_id
1306 , p_source_object_id => p_source_object_id
1307 , p_source_object_code => p_source_object_code
1308 , p_notes => p_notes
1309 , p_notes_detail => p_notes_detail
1310 , p_note_status => p_note_status
1311 , p_entered_by => p_entered_by
1312 , p_entered_date => p_entered_date
1313 , x_jtf_note_id => x_jtf_note_id
1314 , p_last_update_date => p_last_update_date
1315 , p_last_updated_by => p_last_updated_by
1316 , p_creation_date => p_creation_date
1317 , p_created_by => p_created_by
1318 , p_last_update_login => p_last_update_login
1319 , p_attribute1 => p_attribute1
1320 , p_attribute2 => p_attribute2
1321 , p_attribute3 => p_attribute3
1322 , p_attribute4 => p_attribute4
1323 , p_attribute5 => p_attribute5
1324 , p_attribute6 => p_attribute6
1325 , p_attribute7 => p_attribute7
1326 , p_attribute8 => p_attribute8
1327 , p_attribute9 => p_attribute9
1328 , p_attribute10 => p_attribute10
1329 , p_attribute11 => p_attribute11
1330 , p_attribute12 => p_attribute12
1331 , p_attribute13 => p_attribute13
1332 , p_attribute14 => p_attribute14
1333 , p_attribute15 => p_attribute15
1334 , p_context => p_context
1335 , p_note_type => p_note_type
1336 , p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
1337 , x_return_status => l_return_status
1338 );
1339
1340 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1341 THEN
1342 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_CUST_USR_HK');
1343 FND_MSG_PUB.Add;
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1345 END IF;
1346 END IF;
1347
1348 -- Pre call to the Vertical Type User Hook
1349 --
1350 IF jtf_usr_hks.ok_to_execute('JTF_NOTES_PUB'
1351 ,'Create_Note'
1352 ,'B'
1353 ,'V'
1354 )
1355 THEN
1356 jtf_notes_vuhk.create_note_pre
1357 ( p_parent_note_id => p_parent_note_id
1358 , p_api_version => p_api_version
1359 , p_init_msg_list => p_init_msg_list
1360 , p_commit => FND_API.G_FALSE
1361 , p_validation_level => p_validation_level
1362 , x_msg_count => l_msg_count
1363 , x_msg_data => l_msg_data
1364 , p_org_id => p_org_id
1365 , p_source_object_id => p_source_object_id
1366 , p_source_object_code => p_source_object_code
1367 , p_notes => p_notes
1368 , p_notes_detail => p_notes_detail
1369 , p_note_status => p_note_status
1370 , p_entered_by => p_entered_by
1371 , p_entered_date => p_entered_date
1372 , x_jtf_note_id => x_jtf_note_id
1373 , p_last_update_date => p_last_update_date
1374 , p_last_updated_by => p_last_updated_by
1375 , p_creation_date => p_creation_date
1376 , p_created_by => p_created_by
1377 , p_last_update_login => p_last_update_login
1378 , p_attribute1 => p_attribute1
1379 , p_attribute2 => p_attribute2
1380 , p_attribute3 => p_attribute3
1381 , p_attribute4 => p_attribute4
1382 , p_attribute5 => p_attribute5
1383 , p_attribute6 => p_attribute6
1384 , p_attribute7 => p_attribute7
1385 , p_attribute8 => p_attribute8
1386 , p_attribute9 => p_attribute9
1387 , p_attribute10 => p_attribute10
1388 , p_attribute11 => p_attribute11
1389 , p_attribute12 => p_attribute12
1390 , p_attribute13 => p_attribute13
1391 , p_attribute14 => p_attribute14
1392 , p_attribute15 => p_attribute15
1393 , p_context => p_context
1394 , p_note_type => p_note_type
1395 , p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
1396 , x_return_status => l_return_status
1397 );
1398
1399 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1400 THEN
1401 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_VERT_USR_HK');
1402 FND_MSG_PUB.Add;
1403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1404 END IF;
1405 END IF;
1406
1407
1408 -- Item level validation
1409 IF (p_validation_level > fnd_api.g_valid_level_none)
1410 THEN
1411 --
1412 -- Check if the note id passed is unique
1413 --
1414 BEGIN
1415 IF ( (l_jtf_note_id IS NOT NULL)
1416 OR (l_jtf_note_id <> FND_API.G_MISS_NUM)
1417 )
1418 THEN
1419 SELECT jtf_note_id
1420 INTO l_jtf_note_id
1421 FROM jtf_notes_b
1422 WHERE jtf_note_id = p_jtf_note_id;
1423
1424 --Exit if another note exists and the calling page is Notes JSP
1425 IF (p_validation_level = 0.5)
1426 THEN
1427 x_jtf_note_id := l_jtf_note_id;
1428 RAISE l_duplicate_note;
1429 ELSE
1430 Add_Invalid_Argument_Msg( p_token_an => l_api_name_full
1431 , p_token_v => p_jtf_note_id
1432 , p_token_p => 'p_jtf_note_id'
1433 );
1434 RAISE FND_API.G_EXC_ERROR;
1435 END IF;
1436 END IF;
1437 EXCEPTION
1438 WHEN NO_DATA_FOUND
1439 THEN
1440 NULL;
1441 WHEN l_duplicate_note
1442 THEN
1443 RAISE l_duplicate_note;
1444 WHEN OTHERS
1445 THEN
1446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1447 END;
1448
1449 -- If mobile passes ID they should never clash with the sequence
1450 IF ( (l_jtf_note_id IS NOT NULL)
1451 OR (l_jtf_note_id <> FND_API.G_MISS_NUM)
1452 )
1453 AND (p_validation_level <> 0.5) -- JSP pages will be able to override
1454 -- this check so we can avoid the refresh problem
1455 AND (NVL(FND_PROFILE.Value('APPS_MAINTENANCE_MODE'),'X') <> 'FUZZY')
1456 -- For HA, if the API is called in Replay mode
1457 -- then don't restrict jtf_note_id
1458 THEN
1459 IF (p_jtf_note_id < 1e+12)
1460 THEN
1461 Add_Invalid_Argument_Msg( p_token_an => l_api_name_full
1462 , p_token_v => p_jtf_note_id
1463 , p_token_p => 'p_jtf_note_id'
1464 );
1465 RAISE FND_API.G_EXC_ERROR;
1466 END IF ;
1467 END IF;
1468
1469 -- Validate source object id and code against object_type_code
1470 -- in jtf_object_types_b table
1471 Validate_object( p_api_name => l_api_name_full
1472 , p_object_type_code => p_source_object_code
1473 , p_object_type_id => p_source_object_id
1474 , x_return_status => l_return_status
1475 );
1476
1477 IF (l_return_status <> fnd_api.g_ret_sts_success)
1478 THEN
1479 add_invalid_argument_msg( l_api_name_full
1480 , p_source_object_id
1481 , 'p_source_object_id'
1482 );
1483 RAISE fnd_api.g_exc_error;
1484 END IF;
1485
1486 -- Validate note status
1487 IF (p_note_status <> fnd_api.g_miss_char)
1488 THEN
1489 IF (p_note_status NOT IN ('P', 'I','E'))
1490 THEN
1491 add_invalid_argument_msg( l_api_name_full
1492 , p_note_status
1493 , 'p_note_status'
1494 );
1495 RAISE fnd_api.g_exc_error;
1496 END IF;
1497 END IF;
1498
1499 -- Validate note length
1500 IF (p_notes IS NOT NULL)
1501 THEN
1502 trunc_string_length( l_api_name_full
1503 , 'p_notes'
1504 , p_notes
1505 , 2000
1506 , l_notes
1507 );
1508 -- Message added in trunc_string_length, no exception..
1509 END IF;
1510
1511 --Validate note_Type
1512 IF (p_note_type IS NOT NULL)
1513 THEN
1514 Validate_note_type( p_api_name => l_api_name_full
1515 , p_parameter_name => 'p_note_type'
1516 , p_note_type => p_note_type
1517 , x_return_status => l_return_status
1518 );
1519 IF (l_return_status <> fnd_api.g_ret_sts_success)
1520 THEN
1521 -- Message added in Validate_note_type
1522 RAISE fnd_api.g_exc_error;
1523 END IF;
1524
1525 END IF;
1526
1527 --Validate entered by
1528 IF p_entered_by IS NOT NULL
1529 THEN
1530 validate_entered_by( p_entered_by => l_entered_by
1531 , x_return_status => l_return_status
1532 , x_entered_by => l_entered_by
1533 );
1534 IF (l_return_status <> fnd_api.g_ret_sts_success)
1535 THEN
1536 RAISE fnd_api.g_exc_error;
1537 END IF;
1538 END IF;
1539 END IF;
1540
1541 -- Defaulting
1542 IF p_parent_note_id = fnd_api.g_miss_num
1543 THEN
1544 l_parent_note_id := NULL;
1545 ELSE
1546 l_parent_note_id := p_parent_note_id;
1547 END IF;
1548 IF p_entered_date = fnd_api.g_miss_date
1549 THEN
1550 l_entered_date := (SYSDATE);
1551 ELSE
1552 l_entered_date := p_entered_date;
1553 END IF;
1554
1555 IF p_last_update_date = fnd_api.g_miss_date
1556 THEN
1557 l_last_update_date := (SYSDATE);
1558 ELSE
1559 l_last_update_date := p_last_update_date;
1560 END IF;
1561
1562 IF p_creation_date = fnd_api.g_miss_date
1563 THEN
1564 l_creation_date := (SYSDATE);
1565 ELSE
1566 l_creation_date := p_creation_date;
1567 END IF;
1568
1569 IF p_note_status IS NOT NULL
1570 THEN
1571 l_note_status := p_note_status;
1572 ELSE
1573 l_note_status := 'I'; -- Internal is the default
1574 END IF;
1575
1576 --
1577 -- AOL Security validations
1578 --
1579 IF (p_use_AOL_security = fnd_api.g_true)
1580 THEN
1581 --
1582 -- Check if the user is allowed to create notes at all
1583 --
1584 JTF_NOTES_SECURITY_PVT.check_function
1585 ( p_api_version => 1.0
1586 , p_init_msg_list => FND_API.G_FALSE
1587 , p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_CREATE
1588 , p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
1589 , x_grant => l_grant_select
1590 , x_return_status => l_return_status
1591 , x_msg_count => l_msg_count
1592 , x_msg_data => l_msg_data
1593 );
1594
1595 --
1596 -- If there's an error push it onto the stack
1597 --
1598 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1599 THEN
1600 FND_MESSAGE.SET_NAME('JTF', 'JTF_UNABLE_TO_CHECK_FUNCTION'); -- Unable to verify whether Security &FUNCTION function was granted
1601 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_CREATE');
1602 FND_MSG_PUB.ADD;
1603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1604 END IF;
1605
1606 --
1607 -- If the create function was not granted throw an error
1608 --
1609 IF (l_grant_select = 0)
1610 THEN
1611 FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
1612 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_CREATE');
1613 FND_MSG_PUB.ADD;
1614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1615 END IF;
1616
1617 --
1618 -- Function was granted now we'll verify whether user is allowed to create notes of the given type
1619 --
1620 JTF_NOTES_SECURITY_PVT.check_note_type
1621 ( p_api_version => 1.0
1622 , p_init_msg_list => FND_API.G_FALSE
1623 , p_note_type => p_note_type
1624 , x_return_status => l_return_status
1625 , x_grant => l_grant_select_type
1626 , x_msg_count => l_msg_count
1627 , x_msg_data => l_msg_data
1628 );
1629
1630 --
1631 -- If there's an error push it onto the stack
1632 --
1633 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1634 THEN
1635 FND_MESSAGE.SET_NAME('JTF', 'JTF_UNABLE_TO_CHECK_FUNCTION'); -- Unable to verify whether Security &FUNCTION function was granted
1636 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_CREATE');
1637 FND_MSG_PUB.ADD;
1638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1639 END IF;
1640
1641 --
1642 -- user is not allowed to create notes of this type
1643 --
1644 IF (l_grant_select = 0)
1645 THEN
1646 FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
1647 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_TYPE_CREATE');
1648 FND_MSG_PUB.ADD;
1649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1650 END IF;
1651
1652 END IF; -- Security validations
1653
1654 --
1655 -- Get jtf_note_id from sequence
1656 --
1657 IF ( (l_jtf_note_id IS NULL)
1658 OR (l_jtf_note_id = FND_API.G_MISS_NUM)
1659 )
1660 THEN
1661 OPEN l_jtf_note_id_csr;
1662 FETCH l_jtf_note_id_csr INTO l_jtf_note_id;
1663 CLOSE l_jtf_note_id_csr;
1664 END IF;
1665
1666 JTF_NOTES_PKG.INSERT_ROW
1667 ( x_rowid => l_rowid
1668 , x_jtf_note_id => l_jtf_note_id
1669 , x_source_object_code => p_source_object_code
1670 , x_note_status => l_note_status
1671 , x_entered_by => p_entered_by
1672 , x_entered_date => l_entered_date
1673 , x_note_type => p_note_type
1674 , x_attribute1 => p_attribute1
1675 , x_attribute2 => p_attribute2
1676 , x_attribute3 => p_attribute3
1677 , x_attribute4 => p_attribute4
1678 , x_attribute5 => p_attribute5
1679 , x_attribute6 => p_attribute6
1680 , x_attribute7 => p_attribute7
1681 , x_attribute8 => p_attribute8
1682 , x_attribute9 => p_attribute9
1683 , x_attribute10 => p_attribute10
1684 , x_attribute11 => p_attribute11
1685 , x_attribute12 => p_attribute12
1686 , x_attribute13 => p_attribute13
1687 , x_attribute14 => p_attribute14
1688 , x_attribute15 => p_attribute15
1689 , x_context => p_context
1690 , x_parent_note_id => l_parent_note_id
1691 , x_source_object_id => p_source_object_id
1692 , x_notes => l_notes
1693 , x_notes_detail => p_notes_detail
1694 , x_creation_date => l_creation_date
1695 , x_created_by => p_created_by
1696 , x_last_update_date => l_last_update_date
1697 , x_last_updated_by => p_last_updated_by
1698 , x_last_update_login => p_last_update_login
1699 );
1700
1701 -- Retrieve from the table to verify insertion
1702 OPEN l_insert_check_csr;
1703 FETCH l_insert_check_csr INTO l_dummy;
1704 IF (l_insert_check_csr%notfound)
1705 THEN
1706 CLOSE l_insert_check_csr;
1707 RAISE l_insert_failure;
1708 END IF;
1709 CLOSE l_insert_check_csr;
1710
1711 -- Insert the contexts
1712 IF ( p_jtf_note_contexts_tab.COUNT > 0 )
1713 THEN
1714 FOR i IN 1..p_jtf_note_contexts_tab.COUNT
1715 LOOP
1716 create_note_context
1717 ( p_validation_level => p_validation_level
1718 , x_return_status => l_return_status
1719 , p_jtf_note_id => l_jtf_note_id
1720 , p_last_update_date => p_jtf_note_contexts_tab(i).last_update_date
1721 , p_last_updated_by => p_jtf_note_contexts_tab(i).last_updated_by
1722 , p_creation_date => p_jtf_note_contexts_tab(i).creation_date
1723 , p_created_by => p_jtf_note_contexts_tab(i).created_by
1724 , p_last_update_login => p_jtf_note_contexts_tab(i).last_update_login
1725 , p_note_context_type_id => p_jtf_note_contexts_tab(i).note_context_type_id
1726 , p_note_context_type => p_jtf_note_contexts_tab(i).note_context_type
1727 , x_note_context_id => l_note_context_id
1728 );
1729 IF (l_return_status <> fnd_api.g_ret_sts_success)
1730 THEN
1731 RAISE fnd_api.g_exc_error;
1732 EXIT;
1733 END IF;
1734 END LOOP;
1735 END IF;
1736 --
1737 -- Make the post processing call to the user hooks
1738 --
1739 -- Post call to the Customer Type User Hook
1740 --
1741 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
1742 , 'Create_Note'
1743 , 'A'
1744 , 'C'
1745 )
1746 THEN
1747 jtf_notes_cuhk.create_note_post
1748 ( p_parent_note_id => l_parent_note_id
1749 , p_api_version => p_api_version
1750 , p_init_msg_list => p_init_msg_list
1751 , p_commit => FND_API.G_FALSE
1752 , p_validation_level => p_validation_level
1753 , x_msg_count => x_msg_count
1754 , x_msg_data => x_msg_data
1755 , p_org_id => p_org_id
1756 , p_source_object_id => p_source_object_id
1757 , p_source_object_code => p_source_object_code
1758 , p_notes => l_notes
1759 , p_notes_detail => p_notes_detail
1760 , p_note_status => l_note_status
1761 , p_entered_by => p_entered_by
1762 , p_entered_date => p_entered_date
1763 , x_jtf_note_id => x_jtf_note_id
1764 , p_last_update_date => l_last_update_date
1765 , p_last_updated_by => p_last_updated_by
1766 , p_creation_date => l_creation_date
1767 , p_created_by => p_created_by
1768 , p_last_update_login => p_last_update_login
1769 , p_attribute1 => p_attribute1
1770 , p_attribute2 => p_attribute2
1771 , p_attribute3 => p_attribute3
1772 , p_attribute4 => p_attribute4
1773 , p_attribute5 => p_attribute5
1774 , p_attribute6 => p_attribute6
1775 , p_attribute7 => p_attribute7
1776 , p_attribute8 => p_attribute8
1777 , p_attribute9 => p_attribute9
1778 , p_attribute10 => p_attribute10
1779 , p_attribute11 => p_attribute11
1780 , p_attribute12 => p_attribute12
1781 , p_attribute13 => p_attribute13
1782 , p_attribute14 => p_attribute14
1783 , p_attribute15 => p_attribute15
1784 , p_context => p_context
1785 , p_note_type => p_note_type
1786 , p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
1787 , x_return_status => l_return_status
1788 , p_jtf_note_id => l_jtf_note_id
1789 );
1790
1791 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1792 THEN
1793 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_POST_CUST_USR_HK');
1794 FND_MSG_PUB.Add;
1795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1796 END IF;
1797 END IF;
1798
1799
1800 -- Post call to the Vertical Type User Hook
1801 --
1802 IF jtf_usr_hks.ok_to_execute('JTF_NOTES_PUB'
1803 ,'Create_Note'
1804 ,'A'
1805 ,'V'
1806 )
1807 THEN
1808 jtf_notes_vuhk.create_note_post
1809 ( p_parent_note_id => l_parent_note_id
1810 , p_api_version => p_api_version
1811 , p_init_msg_list => p_init_msg_list
1812 , p_commit => FND_API.G_FALSE
1813 , p_validation_level => p_validation_level
1814 , x_msg_count => x_msg_count
1815 , x_msg_data => x_msg_data
1816 , p_org_id => p_org_id
1817 , p_source_object_id => p_source_object_id
1818 , p_source_object_code => p_source_object_code
1819 , p_notes => l_notes
1820 , p_notes_detail => p_notes_detail
1821 , p_note_status => l_note_status
1822 , p_entered_by => p_entered_by
1823 , p_entered_date => p_entered_date
1824 , x_jtf_note_id => x_jtf_note_id
1825 , p_last_update_date => l_last_update_date
1826 , p_last_updated_by => p_last_updated_by
1827 , p_creation_date => l_creation_date
1828 , p_created_by => p_created_by
1829 , p_last_update_login => p_last_update_login
1830 , p_attribute1 => p_attribute1
1831 , p_attribute2 => p_attribute2
1832 , p_attribute3 => p_attribute3
1833 , p_attribute4 => p_attribute4
1834 , p_attribute5 => p_attribute5
1835 , p_attribute6 => p_attribute6
1836 , p_attribute7 => p_attribute7
1837 , p_attribute8 => p_attribute8
1838 , p_attribute9 => p_attribute9
1839 , p_attribute10 => p_attribute10
1840 , p_attribute11 => p_attribute11
1841 , p_attribute12 => p_attribute12
1842 , p_attribute13 => p_attribute13
1843 , p_attribute14 => p_attribute14
1844 , p_attribute15 => p_attribute15
1845 , p_context => p_context
1846 , p_note_type => p_note_type
1847 , p_jtf_note_contexts_tab => p_jtf_note_contexts_tab
1848 , x_return_status => l_return_status
1849 , p_jtf_note_id => l_jtf_note_id
1850 );
1851
1852 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1853 FND_MESSAGE.Set_Name('JTF', 'JTF_ERR_POST_VERT_USR_HK');
1854 FND_MSG_PUB.Add;
1855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1856 END IF;
1857 END IF;
1858
1859 -- Standard call for message generation
1860 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
1861 , 'Create_Note'
1862 , 'M'
1863 , 'M'
1864 )
1865 THEN
1866 IF jtf_notes_cuhk.Ok_to_generate_msg
1867 ( p_parent_note_id => p_parent_note_id
1868 , p_api_version => p_api_version
1869 , x_msg_count => x_msg_count
1870 , x_msg_data => x_msg_data
1871 , p_source_object_id => p_source_object_id
1872 , p_source_object_code => p_source_object_code
1873 , p_notes => p_notes
1874 , p_entered_by => p_entered_by
1875 , p_entered_date => p_entered_date
1876 , x_jtf_note_id => x_jtf_note_id
1877 , p_last_update_date => p_last_update_date
1878 , p_last_updated_by => p_last_updated_by
1879 , p_creation_date => p_creation_date
1880 )
1881 THEN
1882 l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;
1883 JTF_USR_HKS.Load_bind_data( l_bind_data_id
1884 , 'jtf_note_id'
1885 , l_jtf_note_id
1886 , 'S'
1887 , 'N'
1888 );
1889
1890 JTF_USR_HKS.generate_message( p_prod_code => 'JTF'
1891 , p_bus_obj_code => 'NOTES'
1892 , p_action_code => 'I'
1893 , p_bind_data_id => l_bind_data_id
1894 , x_return_code => l_return_status
1895 );
1896 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1897 THEN
1898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1899 END IF;
1900 END IF;
1901 END IF;
1902
1903
1904 JTF_NOTES_EVENTS_PVT.RaiseCreateNote
1905 ( p_NoteID => l_jtf_note_id
1906 , p_SourceObjectCode => p_source_object_code
1907 , p_SourceObjectID => p_source_object_id
1908 );
1909
1910 IF fnd_api.to_boolean(p_commit)
1911 THEN
1912 COMMIT WORK;
1913 END IF;
1914
1915 fnd_msg_pub.count_and_get( p_encoded => 'F'
1916 , p_count => x_msg_count
1917 , p_data => x_msg_data
1918 );
1919
1920 x_jtf_note_id := l_jtf_note_id;
1921
1922 EXCEPTION
1923 WHEN l_duplicate_note
1924 THEN
1925 -- User hit 'Refresh' button, pretend it never happend and all is well
1926 ROLLBACK TO create_note_pvt;
1927 x_return_status := fnd_api.g_ret_sts_success;
1928
1929 WHEN l_missing_parameter
1930 THEN
1931 -- A required parameter is missing
1932 ROLLBACK TO create_note_pvt;
1933 x_return_status := fnd_api.g_ret_sts_unexp_error;
1934 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1935 THEN
1936 fnd_message.set_name('JTF', 'JTF_API_ALL_MISSING_PARAM');
1937 fnd_message.set_token('API_NAME', g_pkg_name||'.'||l_api_name);
1938 fnd_message.set_token('MISSING_PARAM', l_missing_param);
1939 fnd_msg_pub.ADD;
1940 END IF;
1941
1942 fnd_msg_pub.count_and_get( p_encoded => 'F' -- Not encoding so HTML can use the message
1943 , p_count => x_msg_count
1944 , p_data => x_msg_data
1945 );
1946
1947 WHEN l_null_parameter
1948 THEN
1949 -- A required field is NULL
1950 ROLLBACK TO create_note_pvt;
1951 x_return_status := fnd_api.g_ret_sts_unexp_error;
1952 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1953 THEN
1954 fnd_message.set_name('JTF', 'JTF_API_ALL_NULL_PARAMETER');
1955 fnd_message.set_token('API_NAME', g_pkg_name||'.'||l_api_name);
1956 fnd_message.set_token('NULL_PARAM', l_null_param);
1957 fnd_msg_pub.ADD;
1958 END IF;
1959 fnd_msg_pub.count_and_get( p_encoded => 'F'
1960 , p_count => x_msg_count
1961 , p_data => x_msg_data
1962 );
1963
1964 WHEN fnd_api.g_exc_error
1965 THEN
1966 ROLLBACK TO create_note_pvt;
1967 x_return_status := fnd_api.g_ret_sts_error;
1968 fnd_msg_pub.count_and_get( p_encoded => 'F'
1969 , p_count => x_msg_count
1970 , p_data => x_msg_data
1971 );
1972
1973 WHEN fnd_api.g_exc_unexpected_error
1974 THEN
1975 ROLLBACK TO create_note_pvt;
1976 x_return_status := fnd_api.g_ret_sts_unexp_error;
1977 fnd_msg_pub.count_and_get( p_encoded => 'F'
1978 , p_count => x_msg_count
1979 , p_data => x_msg_data
1980 );
1981
1982 WHEN OTHERS
1983 THEN
1984 ROLLBACK TO create_note_pvt;
1985 x_return_status := fnd_api.g_ret_sts_unexp_error;
1986 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1987 THEN
1988 fnd_msg_pub.add_exc_msg( g_pkg_name
1989 , l_api_name
1990 );
1991 END IF;
1992 fnd_msg_pub.count_and_get( p_encoded => 'F'
1993 , p_count => x_msg_count
1994 , p_data => x_msg_data
1995 );
1996 END Secure_Create_note;
1997
1998
1999 PROCEDURE Secure_Update_note
2000 ------------------------------------------------------------------------------
2001 -- Update_note
2002 -- Updates a note record in the JTF_NOTES table
2003 ------------------------------------------------------------------------------
2004 ( p_api_version IN NUMBER
2005 , p_init_msg_list IN VARCHAR2 DEFAULT 'F'
2006 , p_commit IN VARCHAR2 DEFAULT 'F'
2007 , p_validation_level IN NUMBER DEFAULT 100
2008 , x_return_status OUT NOCOPY VARCHAR2
2009 , x_msg_count OUT NOCOPY NUMBER
2010 , x_msg_data OUT NOCOPY VARCHAR2
2011 , p_jtf_note_id IN NUMBER
2012 , p_entered_by IN NUMBER DEFAULT fnd_global.user_id
2013 , p_last_updated_by IN NUMBER
2014 , p_last_update_date IN DATE DEFAULT SYSDATE
2015 , p_last_update_login IN NUMBER DEFAULT NULL
2016 , p_notes IN VARCHAR2 DEFAULT CHR(0)
2017 , p_notes_detail IN VARCHAR2 DEFAULT CHR(0)
2018 , p_append_flag IN VARCHAR2 DEFAULT CHR(0)
2019 , p_note_status IN VARCHAR2 DEFAULT 'I'
2020 , p_note_type IN VARCHAR2 DEFAULT CHR(0)
2021 , p_jtf_note_contexts_tab IN jtf_note_contexts_tbl_type
2022 DEFAULT jtf_note_contexts_tab_dflt
2023 , p_attribute1 IN VARCHAR2 DEFAULT CHR(0)
2024 , p_attribute2 IN VARCHAR2 DEFAULT CHR(0)
2025 , p_attribute3 IN VARCHAR2 DEFAULT CHR(0)
2026 , p_attribute4 IN VARCHAR2 DEFAULT CHR(0)
2027 , p_attribute5 IN VARCHAR2 DEFAULT CHR(0)
2028 , p_attribute6 IN VARCHAR2 DEFAULT CHR(0)
2029 , p_attribute7 IN VARCHAR2 DEFAULT CHR(0)
2030 , p_attribute8 IN VARCHAR2 DEFAULT CHR(0)
2031 , p_attribute9 IN VARCHAR2 DEFAULT CHR(0)
2032 , p_attribute10 IN VARCHAR2 DEFAULT CHR(0)
2033 , p_attribute11 IN VARCHAR2 DEFAULT CHR(0)
2034 , p_attribute12 IN VARCHAR2 DEFAULT CHR(0)
2035 , p_attribute13 IN VARCHAR2 DEFAULT CHR(0)
2036 , p_attribute14 IN VARCHAR2 DEFAULT CHR(0)
2037 , p_attribute15 IN VARCHAR2 DEFAULT CHR(0)
2038 , p_context IN VARCHAR2 DEFAULT CHR(0)
2039 , p_use_AOL_security IN VARCHAR2 DEFAULT 'T'
2040 )
2041 IS
2042 l_api_name CONSTANT VARCHAR2(30) := 'Secure_Update_note';
2043 l_api_version CONSTANT NUMBER := 1.0;
2044 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
2045 l_return_status VARCHAR2(1);
2046 --substrb added for bug 4227634 by abraina
2047 l_notes VARCHAR2(2000) := substrb(p_notes,1,2000);
2048 l_notes_detail VARCHAR2(32767):= p_notes_detail;
2049 l_notes_detail_old VARCHAR2(32767);
2050 l_note_status VARCHAR2(1) := p_note_status;
2051 l_note_type VARCHAR2(30) := p_note_type;
2052 l_msg_count NUMBER;
2053 l_msg_data VARCHAR2(2000);
2054 l_last_updated_by NUMBER;
2055 l_last_update_login NUMBER;
2056 l_last_update_date DATE;
2057 l_entered_by NUMBER := p_entered_by;
2058 l_bind_data_id NUMBER;
2059 l_new_clob_length NUMBER;
2060 l_old_clob_length NUMBER;
2061 l_total_clob_length NUMBER;
2062 l_notes_detail_truncated VARCHAR2(32767);
2063 l_append_flag VARCHAR2(1) := 'N';
2064 l_attribute1 VARCHAR2(150);
2065 l_attribute2 VARCHAR2(150);
2066 l_attribute3 VARCHAR2(150);
2067 l_attribute4 VARCHAR2(150);
2068 l_attribute5 VARCHAR2(150);
2069 l_attribute6 VARCHAR2(150);
2070 l_attribute7 VARCHAR2(150);
2071 l_attribute8 VARCHAR2(150);
2072 l_attribute9 VARCHAR2(150);
2073 l_attribute10 VARCHAR2(150);
2074 l_attribute11 VARCHAR2(150);
2075 l_attribute12 VARCHAR2(150);
2076 l_attribute13 VARCHAR2(150);
2077 l_attribute14 VARCHAR2(150);
2078 l_attribute15 VARCHAR2(150);
2079 l_context VARCHAR2(30);
2080 l_note_update_primary NUMBER;
2081 l_note_update_secondary NUMBER;
2082 l_note_select_type NUMBER;
2083
2084 CURSOR l_com_csr
2085 IS SELECT *
2086 FROM JTF_NOTES_B
2087 WHERE jtf_note_id = p_jtf_note_id
2088 FOR UPDATE OF jtf_note_id NOWAIT;
2089
2090 CURSOR l_tl_csr
2091 IS SELECT *
2092 FROM JTF_NOTES_TL
2093 WHERE JTF_NOTE_ID = p_JTF_NOTE_ID
2094 AND USERENV('LANG') = LANGUAGE;
2095
2096 l_com_rec l_com_csr%ROWTYPE;
2097 l_tl_rec l_tl_csr%ROWTYPE;
2098
2099 e_resource_busy EXCEPTION;
2100 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
2101
2102
2103 BEGIN
2104 --
2105 -- Standard start of API savepoint
2106 --
2107 SAVEPOINT update_note_pvt;
2108
2109 --
2110 -- Standard call to check for call compatibility
2111 --
2112 IF NOT fnd_api.compatible_api_call( l_api_version
2113 , p_api_version
2114 , l_api_name, g_pkg_name
2115 )
2116 THEN
2117 RAISE fnd_api.g_exc_unexpected_error;
2118 END IF;
2119
2120 --
2121 -- Initialize message list if p_init_msg_list is set to TRUE
2122 --
2123 IF fnd_api.to_boolean(p_init_msg_list)
2124 THEN
2125 fnd_msg_pub.initialize;
2126 END IF;
2127
2128 --
2129 -- Initialize API return status to success
2130 --
2131 x_return_status := fnd_api.g_ret_sts_success;
2132
2133 --
2134 -- Fetch the original values for defaulting/comparison
2135 --
2136 OPEN l_com_csr; -- _B table
2137
2138 FETCH l_com_csr INTO l_com_rec;
2139
2140 IF (l_com_csr%NOTFOUND)
2141 THEN
2142 add_invalid_argument_msg( l_api_name_full
2143 , p_jtf_note_id
2144 , 'p_jtf_note_id'
2145 );
2146 RAISE fnd_api.g_exc_error;
2147 END IF;
2148
2149 OPEN l_tl_csr; -- _TL table
2150
2151 FETCH l_tl_csr INTO l_tl_rec;
2152
2153 IF (l_tl_csr%NOTFOUND)
2154 THEN
2155 add_invalid_argument_msg( l_api_name_full
2156 , p_jtf_note_id
2157 , 'p_jtf_note_id'
2158 );
2159 RAISE fnd_api.g_exc_error;
2160 END IF;
2161
2162 --
2163 -- Defaulting values that are missing
2164 --
2165 IF (p_notes = fnd_api.g_miss_char)
2166 THEN
2167 --substrb added for bug 4227634 by abraina
2168 l_notes := substrb(l_tl_rec.notes,1,2000);
2169 ELSE
2170 l_notes := substrb(p_notes,1,2000);
2171 END IF;
2172 --
2173 IF (p_note_status = fnd_api.g_miss_char)
2174 THEN
2175 l_note_status := l_com_rec.note_status;
2176 ELSE
2177 l_note_status := p_note_status;
2178 END IF;
2179 --
2180 IF (p_note_type = fnd_api.g_miss_char)
2181 THEN
2182 l_note_type := l_com_rec.note_type;
2183 ELSE
2184 l_note_type := p_note_type;
2185 END IF;
2186 --
2187 IF p_last_update_date = fnd_api.g_miss_date
2188 THEN
2189 l_last_update_date := SYSDATE;
2190 ELSE
2191 l_last_update_date := p_last_update_date;
2192 END IF;
2193 --
2194 IF p_last_updated_by = fnd_api.g_miss_num
2195 THEN
2196 l_last_updated_by := FND_GLOBAL.USER_ID;
2197 ELSE
2198 l_last_updated_by := p_last_updated_by;
2199 END IF;
2200 --
2201 IF p_last_update_login = fnd_api.g_miss_num
2202 THEN
2203 l_last_update_login := FND_GLOBAL.USER_ID;
2204 ELSE
2205 l_last_update_login := p_last_update_login;
2206 END IF;
2207 --
2208 IF (p_append_flag = 'Y')
2209 THEN
2210 l_append_flag := 'Y';
2211 ELSE
2212 l_append_flag := 'N';
2213 END IF;
2214 --
2215 IF (p_attribute1 = fnd_api.g_miss_char)
2216 THEN
2217 l_attribute1 := l_com_rec.attribute1;
2218 ELSE
2219 l_attribute1 := p_attribute1;
2220 END IF;
2221 --
2222 IF (p_attribute2 = fnd_api.g_miss_char)
2223 THEN
2224 l_attribute2 := l_com_rec.attribute2;
2225 ELSE
2226 l_attribute2 := p_attribute2;
2227 END IF;
2228 --
2229 IF (p_attribute3 = fnd_api.g_miss_char)
2230 THEN
2231 l_attribute3 := l_com_rec.attribute3;
2232 ELSE
2233 l_attribute3 := p_attribute3;
2234 END IF;
2235 --
2236 IF (p_attribute4 = fnd_api.g_miss_char)
2237 THEN
2238 l_attribute4 := l_com_rec.attribute4;
2239 ELSE
2240 l_attribute4 := p_attribute4;
2241 END IF;
2242 --
2243 IF (p_attribute5 = fnd_api.g_miss_char)
2244 THEN
2245 l_attribute5 := l_com_rec.attribute5;
2246 ELSE
2247 l_attribute5 := p_attribute5;
2248 END IF;
2249 --
2250 IF (p_attribute6 = fnd_api.g_miss_char)
2251 THEN
2252 l_attribute6 := l_com_rec.attribute6;
2253 ELSE
2254 l_attribute6 := p_attribute6;
2255 END IF;
2256 --
2257 IF (p_attribute7 = fnd_api.g_miss_char)
2258 THEN
2259 l_attribute7 := l_com_rec.attribute7;
2260 ELSE
2261 l_attribute7 := p_attribute7;
2262 END IF;
2263 --
2264 IF (p_attribute8 = fnd_api.g_miss_char)
2265 THEN
2266 l_attribute8 := l_com_rec.attribute8;
2267 ELSE
2268 l_attribute8 := p_attribute8;
2269 END IF;
2270 --
2271 IF (p_attribute9 = fnd_api.g_miss_char)
2272 THEN
2273 l_attribute9 := l_com_rec.attribute9;
2274 ELSE
2275 l_attribute9 := p_attribute9;
2276 END IF;
2277 --
2278 IF (p_attribute10 = fnd_api.g_miss_char)
2279 THEN
2280 l_attribute10 := l_com_rec.attribute10;
2281 ELSE
2282 l_attribute10 := p_attribute10;
2283 END IF;
2284 --
2285 IF (p_attribute11 = fnd_api.g_miss_char)
2286 THEN
2287 l_attribute11 := l_com_rec.attribute11;
2288 ELSE
2289 l_attribute11 := p_attribute11;
2290 END IF;
2291 --
2292 IF (p_attribute12 = fnd_api.g_miss_char)
2293 THEN
2294 l_attribute12 := l_com_rec.attribute12;
2295 ELSE
2296 l_attribute12 := p_attribute12;
2297 END IF;
2298 --
2299 IF (p_attribute13 = fnd_api.g_miss_char)
2300 THEN
2301 l_attribute13 := l_com_rec.attribute13;
2302 ELSE
2303 l_attribute13 := p_attribute13;
2304 END IF;
2305 --
2306 IF (p_attribute14 = fnd_api.g_miss_char)
2307 THEN
2308 l_attribute14 := l_com_rec.attribute14;
2309 ELSE
2310 l_attribute14 := p_attribute14;
2311 END IF;
2312 --
2313 IF (p_attribute15 = fnd_api.g_miss_char)
2314 THEN
2315 l_attribute15 := l_com_rec.attribute15;
2316 ELSE
2317 l_attribute15 := p_attribute15;
2318 END IF;
2319 --
2320 IF (p_context = fnd_api.g_miss_char)
2321 THEN
2322 l_context := l_com_rec.context;
2323 ELSE
2324 l_context := p_context;
2325 END IF;
2326 --
2327 -- Defaulting the Note and Note details
2328 --
2329 writelobtoData(l_tl_rec.jtf_note_id,l_notes_detail_old); -- Copy the CLOB into a VARCHAR2 so we can use it
2330 --
2331 IF (p_notes_detail = fnd_api.g_miss_char)
2332 THEN
2333 -- use existing value, copy to local so we can append if nesecary
2334 l_notes_detail := l_notes_detail_old;
2335 ELSIF (p_notes_detail IS NULL )
2336 THEN
2337 l_notes_detail := NULL;
2338 ELSE
2339 l_notes_detail := p_notes_detail;
2340 END IF;
2341 --
2342 -- Append if needed..
2343 --
2344 IF ( l_append_flag = 'Y')
2345 THEN
2346
2347 writelobtodata(p_jtf_note_id,l_notes_detail);
2348
2349 l_old_clob_length := LENGTHB(l_notes_detail);
2350 l_new_clob_length := LENGTHB(p_notes_detail);
2351 l_total_clob_length := l_old_clob_length + l_new_clob_length;
2352
2353 IF (l_total_clob_length > 32766) -- 32367 minus 1 since we'll append a space
2354 THEN
2355 -- we'll need to truncate before we append
2356 l_notes_detail_truncated := substrb( l_notes_detail
2357 , 1
2358 , (32766 - l_old_clob_length)
2359 );-- 32367
2360
2361 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_success)
2362 THEN
2363 fnd_message.set_name('JTF', 'JTF_API_ALL_VALUE_TRUNCATED');
2364 fnd_message.set_token('API_NAME', l_api_name);
2365 fnd_message.set_token('TRUNCATED_PARAM', 'p_notes_detail');
2366 fnd_message.set_token('VAL_LEN', l_total_clob_length);
2367 fnd_message.set_token('DB_LEN', 32767);
2368 fnd_msg_pub.add;
2369 END IF;
2370
2371 IF l_notes_detail_truncated IS NOT NULL
2372 THEN
2373
2374 l_notes_detail := l_notes_detail||' '||l_notes_detail_truncated;
2375 END IF;
2376 ELSE
2377 l_notes_detail := p_notes_detail||' '||l_notes_detail;
2378 END IF;
2379 END IF;
2380
2381 --
2382 -- Customer User Hook pre update
2383 --
2384 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
2385 , 'Update Note'
2386 , 'B'
2387 , 'C'
2388 )
2389 THEN
2390 jtf_notes_cuhk.update_note_pre
2391 ( p_api_version => l_api_version
2392 , x_msg_count => l_msg_count
2393 , x_msg_data => l_msg_data
2394 , p_jtf_note_id => p_jtf_note_id
2395 , p_entered_by => l_entered_by
2396 , p_last_updated_by => p_last_updated_by
2397 , p_notes => l_notes
2398 , p_notes_detail => l_notes_detail
2399 , p_append_flag => p_append_flag
2400 , p_note_status => l_note_status
2401 , p_note_type => l_note_type
2402 , x_return_status => l_return_status
2403 );
2404
2405 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2406 THEN
2407 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_CUST_USR_HK');
2408 FND_MSG_PUB.Add;
2409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2410 END IF;
2411 END IF;
2412
2413 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
2414 , 'Update Note'
2415 , 'B'
2416 , 'V'
2417 )
2418 THEN
2419 jtf_notes_vuhk.update_note_pre
2420 ( p_api_version => l_api_version
2421 , x_msg_count => l_msg_count
2422 , x_msg_data => l_msg_data
2423 , p_jtf_note_id => p_jtf_note_id
2424 , p_entered_by => l_entered_by
2425 , p_last_updated_by => p_last_updated_by
2426 , p_notes => l_notes
2427 , p_notes_detail => l_notes_detail
2428 , p_append_flag => p_append_flag
2429 , p_note_status => l_note_status
2430 , p_note_type => l_note_type
2431 , x_return_status => l_return_status
2432 );
2433
2434 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2435 THEN
2436 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_CUST_USR_HK');
2437 FND_MSG_PUB.Add;
2438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2439 END IF;
2440 END IF;
2441
2442 IF (p_validation_level > fnd_api.g_valid_level_none)
2443 THEN
2444 -- Validate notes
2445 IF (p_notes IS NULL)
2446 THEN
2447 add_null_parameter_msg(l_api_name_full, 'p_notes');
2448 RAISE fnd_api.g_exc_error;
2449 END IF;
2450
2451 -- Validate note status
2452 IF (p_note_status <> fnd_api.g_miss_char)
2453 THEN
2454 IF (p_note_status NOT IN ('P', 'I','E'))
2455 THEN
2456 add_invalid_argument_msg( l_api_name_full
2457 , p_note_status
2458 , 'p_note_status'
2459 );
2460 RAISE fnd_api.g_exc_error;
2461 END IF;
2462 END IF;
2463
2464 --Validate note_type
2465 IF l_note_type IS NOT NULL
2466 THEN
2467 Validate_note_type( p_api_name => l_api_name_full
2468 , p_parameter_name => 'p_note_type'
2469 , p_note_type => l_note_type
2470 , x_return_status => l_return_status
2471 );
2472
2473 IF (l_return_status <> fnd_api.g_ret_sts_success)
2474 THEN
2475 RAISE fnd_api.g_exc_error;
2476 END IF;
2477 END IF;
2478 END IF;
2479 --
2480 -- AOL Security validations
2481 --
2482 IF (p_use_AOL_security = fnd_api.g_true)
2483 THEN
2484 --
2485 -- Check if the note is being updated
2486 --
2487 IF (l_notes <> l_tl_rec.notes)
2488 THEN
2489 --
2490 -- Check if the user is allowed to update note text for this note
2491 --
2492 JTF_NOTES_SECURITY_PVT.check_function
2493 ( p_api_version => 1.0
2494 , p_init_msg_list => FND_API.G_FALSE
2495 , p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_UPDATE_NOTE
2496 , p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
2497 , p_instance_pk1_value => p_jtf_note_id
2498 , x_grant => l_note_update_primary
2499 , x_return_status => l_return_status
2500 , x_msg_count => l_msg_count
2501 , x_msg_data => l_msg_data
2502 );
2503
2504 --
2505 -- If there's an error push it onto the stack
2506 --
2507 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2508 THEN
2509 FND_MESSAGE.SET_NAME('JTF', 'JTF_UNABLE_TO_CHECK_FUNCTION'); -- Unable to verify whether Security &FUNCTION function was granted
2510 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE');
2511 FND_MSG_PUB.ADD;
2512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2513 END IF;
2514
2515 --
2516 -- If the create function was not granted throw an error
2517 --
2518 IF (l_note_update_primary = 0)
2519 THEN
2520 FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
2521 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE');
2522 FND_MSG_PUB.ADD;
2523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2524 END IF;
2525 END IF;
2526
2527 --
2528 -- Check if the note detail is being updated
2529 --
2530 IF (l_notes_detail <> l_notes_detail_old)
2531 THEN
2532 --
2533 -- Check if the user is allowed to update note details for this note
2534 --
2535 JTF_NOTES_SECURITY_PVT.check_function
2536 ( p_api_version => 1.0
2537 , p_init_msg_list => FND_API.G_FALSE
2538 , p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_UPDATE_NOTE_DTLS
2539 , p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
2540 , p_instance_pk1_value => p_jtf_note_id
2541 , x_grant => l_note_update_primary
2542 , x_return_status => l_return_status
2543 , x_msg_count => l_msg_count
2544 , x_msg_data => l_msg_data
2545 );
2546
2547 --
2548 -- If there's an error push it onto the stack
2549 --
2550 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2551 THEN
2552 FND_MESSAGE.SET_NAME('JTF', 'JTF_UNABLE_TO_CHECK_FUNCTION'); -- Unable to verify whether Security &FUNCTION function was granted
2553 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE_DETAILS');
2554 FND_MSG_PUB.ADD;
2555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2556 END IF;
2557
2558 --
2559 -- If the create function was not granted throw an error
2560 --
2561 IF (l_note_update_primary = 0)
2562 THEN
2563 FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
2564 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_NOTE_DETAILS');
2565 FND_MSG_PUB.ADD;
2566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2567 END IF;
2568 END IF;
2569
2570 --
2571 -- Check if any of the secondary attributes are beeing updated
2572 --
2573 IF ( (l_note_status <> l_com_rec.note_status)
2574 OR (l_note_type <> l_com_rec.note_type)
2575 OR (l_entered_by <> l_com_rec.entered_by)
2576 )
2577 THEN
2578 --
2579 -- Check if the user is allowed to update primary attributes for this note
2580 --
2581 JTF_NOTES_SECURITY_PVT.check_function
2582 ( p_api_version => 1.0
2583 , p_init_msg_list => FND_API.G_FALSE
2584 , p_function => JTF_NOTES_SECURITY_PVT.G_FUNCTION_UPDATE_SEC
2585 , p_object_name => JTF_NOTES_SECURITY_PVT.G_OBJECT_NOTE
2586 , p_instance_pk1_value => p_jtf_note_id
2587 , x_grant => l_note_update_secondary
2588 , x_return_status => l_return_status
2589 , x_msg_count => l_msg_count
2590 , x_msg_data => l_msg_data
2591 );
2592
2593 --
2594 -- If there's an error push it onto the stack
2595 --
2596 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2597 THEN
2598 FND_MESSAGE.SET_NAME('JTF', 'JTF_UNABLE_TO_CHECK_FUNCTION'); -- Unable to verify whether Security &FUNCTION function was granted
2599 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE');
2600 FND_MSG_PUB.ADD;
2601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603
2604 --
2605 -- If the create function was not granted throw an error
2606 --
2607 IF (l_note_update_secondary = 0)
2608 THEN
2609 FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
2610 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE_PRIMARY');
2611 FND_MSG_PUB.ADD;
2612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2613 ELSE
2614 --
2615 -- Function was granted now we'll verify whether user is allowed to create notes of the given type
2616 --
2617 JTF_NOTES_SECURITY_PVT.check_note_type
2618 ( p_api_version => 1.0
2619 , p_init_msg_list => FND_API.G_FALSE
2620 , p_note_type => p_note_type
2621 , x_return_status => l_return_status
2622 , x_grant => l_note_select_type
2623 , x_msg_count => l_msg_count
2624 , x_msg_data => l_msg_data
2625 );
2626
2627 --
2628 -- If there's an error push it onto the stack
2629 --
2630 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2631 THEN
2632 FND_MESSAGE.SET_NAME('JTF', 'JTF_UNABLE_TO_CHECK_FUNCTION'); -- Unable to verify whether Security &FUNCTION function was granted
2633 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_UPDATE');
2634 FND_MSG_PUB.ADD;
2635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2636 END IF;
2637
2638 --
2639 -- user is not allowed to create notes of this type
2640 --
2641 IF (l_note_select_type = 0)
2642 THEN
2643 FND_MESSAGE.SET_NAME('JTF', 'JTF_FUNCTION_NOT_GRANTED'); -- Security &FUNCTION function was not granted
2644 FND_MESSAGE.SET_TOKEN('FUNCTION', 'JTF_NOTE_TYPE_UPDATE');
2645 FND_MSG_PUB.ADD;
2646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2647 END IF;
2648 END IF;
2649 END IF;
2650 END IF; -- end of Security validations
2651
2652 --
2653 -- Perform the database operation.
2654 --
2655 UPDATE JTF_NOTES_B
2656 SET last_updated_by = l_last_updated_by
2657 , last_update_date = l_last_update_date
2658 , last_update_login = l_last_update_login
2659 , note_status = l_note_status
2660 , note_type = l_note_type
2661 , attribute1 = l_attribute1
2662 , attribute2 = l_attribute2
2663 , attribute3 = l_attribute3
2664 , attribute4 = l_attribute4
2665 , attribute5 = l_attribute5
2666 , attribute6 = l_attribute6
2667 , attribute7 = l_attribute7
2668 , attribute8 = l_attribute8
2669 , attribute9 = l_attribute9
2670 , attribute10 = l_attribute10
2671 , attribute11 = l_attribute11
2672 , attribute12 = l_attribute12
2673 , attribute13 = l_attribute13
2674 , attribute14 = l_attribute14
2675 , attribute15 = l_attribute15
2676 , context = l_context
2677 WHERE CURRENT OF l_com_csr;
2678
2679 --
2680 -- CLOB handling
2681 --
2682 IF ( l_notes_detail IS NULL
2683 AND l_append_flag = 'N'
2684 )
2685 THEN
2686 -- empty the clob..
2687 UPDATE JTF_NOTES_TL
2688 SET NOTES = l_notes
2689 , NOTES_DETAIL = EMPTY_CLOB()
2690 , LAST_UPDATE_DATE = l_last_update_date
2691 , LAST_UPDATED_BY = l_last_updated_by
2692 , LAST_UPDATE_LOGIN = l_last_update_login
2693 , SOURCE_LANG = USERENV('LANG')
2694 WHERE JTF_NOTE_ID = p_jtf_note_id;
2695
2696 ELSIF ( l_notes_detail IS NULL
2697 AND l_append_flag = 'Y'
2698 )
2699 THEN
2700 -- don't do anything with clob
2701 UPDATE JTF_NOTES_TL
2702 SET NOTES = l_notes
2703 , LAST_UPDATE_DATE = l_last_update_date
2704 , LAST_UPDATED_BY = l_last_updated_by
2705 , LAST_UPDATE_LOGIN = l_last_update_login
2706 , SOURCE_LANG = USERENV('LANG')
2707 WHERE JTF_NOTE_ID = p_jtf_note_id;
2708
2709 ELSE
2710 UPDATE JTF_NOTES_TL
2711 SET NOTES = l_notes
2712 , NOTES_DETAIL = EMPTY_CLOB()
2713 , LAST_UPDATE_DATE = l_last_update_date
2714 , LAST_UPDATED_BY = l_last_updated_by
2715 , LAST_UPDATE_LOGIN = l_last_update_login
2716 , SOURCE_LANG = USERENV('LANG')
2717 WHERE JTF_NOTE_ID = p_jtf_note_id;
2718
2719 -- Update the CLOB
2720 writeDatatoLob(p_jtf_note_id,l_notes_detail);
2721 END IF;
2722
2723 --
2724 -- Update the Note Context records
2725 --
2726 IF ( p_jtf_note_contexts_tab.COUNT > 0 )
2727 THEN
2728 FOR i IN 1..p_jtf_note_contexts_tab.COUNT
2729 LOOP
2730 Update_note_context
2731 ( p_validation_level => p_validation_level
2732 , x_return_status => l_return_status
2733 , p_note_context_id => p_jtf_note_contexts_tab(i).note_context_id
2734 , p_jtf_note_id => p_jtf_note_id
2735 , p_note_context_type_id => p_jtf_note_contexts_tab(i).note_context_type_id
2736 , p_note_context_type => p_jtf_note_contexts_tab(i).note_context_type
2737 , p_last_updated_by => p_jtf_note_contexts_tab(i).last_updated_by
2738 , p_last_update_date => p_jtf_note_contexts_tab(i).last_update_date
2739 , p_last_update_login => p_jtf_note_contexts_tab(i).last_update_login
2740 );
2741
2742 IF (l_return_status <> fnd_api.g_ret_sts_success)
2743 THEN
2744 RAISE fnd_api.g_exc_error;
2745 END IF;
2746 END LOOP;
2747 END IF;
2748
2749 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
2750 , 'Create_Note'
2751 , 'A'
2752 , 'C'
2753 )
2754 THEN
2755 jtf_notes_cuhk.update_note_post
2756 ( p_api_version => l_api_version
2757 , x_msg_count => l_msg_count
2758 , x_msg_data => l_msg_data
2759 , p_jtf_note_id => p_jtf_note_id
2760 , p_entered_by => l_entered_by
2761 , p_last_updated_by => p_last_updated_by
2762 , p_notes => l_notes
2763 , p_notes_detail => l_notes_detail
2764 , p_append_flag => p_append_flag
2765 , p_note_status => l_note_status
2766 , p_note_type => l_note_type
2767 , x_return_status => l_return_status
2768 );
2769
2770 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2771 THEN
2772 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_POST_CUST_USR_HK');
2773 FND_MSG_PUB.Add;
2774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2775 END IF;
2776 END IF;
2777
2778 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
2779 , 'Update Note'
2780 , 'A'
2781 , 'V'
2782 )
2783 THEN
2784 jtf_notes_vuhk.update_note_post
2785 ( p_api_version => l_api_version
2786 , x_msg_count => l_msg_count
2787 , x_msg_data => l_msg_data
2788 , p_jtf_note_id => p_jtf_note_id
2789 , p_entered_by => l_entered_by
2790 , p_last_updated_by => p_last_updated_by
2791 , p_notes => l_notes
2792 , p_notes_detail => l_notes_detail
2793 , p_append_flag => p_append_flag
2794 , p_note_status => l_note_status
2795 , p_note_type => l_note_type
2796 , x_return_status => l_return_status
2797 );
2798
2799 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2800 THEN
2801 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_POST_VERT_USR_HK');
2802 FND_MSG_PUB.Add;
2803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2804 END IF;
2805 END IF;
2806
2807 -- Standard call for message generation
2808 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
2809 , 'Create_Note'
2810 , 'M'
2811 , 'M'
2812 )
2813 THEN
2814 IF jtf_notes_cuhk.ok_to_generate_msg
2815 ( p_api_version => l_api_version
2816 , x_msg_count => l_msg_count
2817 , x_msg_data => l_msg_data
2818 , p_jtf_note_id => p_jtf_note_id
2819 , p_entered_by => l_entered_by
2820 , p_last_updated_by => p_last_updated_by
2821 , p_notes => l_notes
2822 , p_notes_detail => l_notes_detail
2823 , p_append_flag => p_append_flag
2824 , p_note_status => l_note_status
2825 , p_note_type => l_note_type
2826 , x_return_status => l_return_status
2827 )
2828 THEN
2829 l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;
2830 JTF_USR_HKS.Load_bind_data( l_bind_data_id
2831 , 'jtf_note_id'
2832 , p_jtf_note_id
2833 , 'S'
2834 , 'N'
2835 );
2836
2837 JTF_USR_HKS.generate_message( p_prod_code => 'JTF'
2838 , p_bus_obj_code => 'NOTES'
2839 , p_action_code => 'I'
2840 , p_bind_data_id => l_bind_data_id
2841 , x_return_code => l_return_status
2842 );
2843 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2844 THEN
2845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2846 END IF;
2847 END IF;
2848 END IF;
2849
2850
2851 JTF_NOTES_EVENTS_PVT.RaiseUpdateNote
2852 ( p_NoteID => p_jtf_note_id
2853 , p_SourceObjectCode => l_com_rec.source_object_code
2854 , p_SourceObjectID => l_com_rec.source_object_id
2855 );
2856
2857 -- Standard check of p_commit
2858 IF fnd_api.to_boolean(p_commit)
2859 THEN
2860 COMMIT WORK;
2861 END IF;
2862
2863
2864 -- Standard call to get message count and if count is 1, get message info
2865 fnd_msg_pub.count_and_get( p_encoded => 'F'
2866 , p_count => x_msg_count
2867 , p_data => x_msg_data
2868 );
2869
2870 EXCEPTION
2871 WHEN e_resource_busy
2872 THEN
2873 ROLLBACK TO update_note_pvt;
2874 /**********************************************************
2875 ** Clean up cursors
2876 **********************************************************/
2877 IF (l_com_csr%ISOPEN)
2878 THEN
2879 CLOSE l_com_csr;
2880 END IF;
2881
2882 IF (l_tl_csr%ISOPEN)
2883 THEN
2884 CLOSE l_tl_csr;
2885 END IF;
2886
2887 /**********************************************************
2888 ** Set Status to error
2889 **********************************************************/
2890 x_return_status := fnd_api.g_ret_sts_error;
2891
2892 /**********************************************************
2893 ** Set the error
2894 **********************************************************/
2895 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
2896 fnd_msg_pub.add;
2897 fnd_msg_pub.count_and_get( p_encoded => 'F'
2898 , p_count => x_msg_count
2899 , p_data => x_msg_data
2900 );
2901
2902 WHEN fnd_api.g_exc_error
2903 THEN
2904 ROLLBACK TO update_note_pvt;
2905 IF (l_com_csr%ISOPEN)
2906 THEN
2907 CLOSE l_com_csr;
2908 END IF;
2909
2910 IF (l_tl_csr%ISOPEN)
2911 THEN
2912 CLOSE l_tl_csr;
2913 END IF;
2914
2915 x_return_status := fnd_api.g_ret_sts_error;
2916
2917 fnd_msg_pub.count_and_get( p_encoded => 'F'
2918 , p_count => x_msg_count
2919 , p_data => x_msg_data
2920 );
2921 WHEN fnd_api.g_exc_unexpected_error
2922 THEN
2923 ROLLBACK TO update_note_pvt;
2924 IF (l_com_csr%ISOPEN)
2925 THEN
2926 CLOSE l_com_csr;
2927 END IF;
2928
2929 IF (l_tl_csr%ISOPEN)
2930 THEN
2931 CLOSE l_tl_csr;
2932 END IF;
2933
2934 x_return_status := fnd_api.g_ret_sts_unexp_error;
2935
2936 fnd_msg_pub.count_and_get( p_encoded => 'F'
2937 , p_count => x_msg_count
2938 , p_data => x_msg_data
2939 );
2940 WHEN OTHERS
2941 THEN
2942 ROLLBACK TO update_note_pvt;
2943 IF (l_com_csr%ISOPEN)
2944 THEN
2945 CLOSE l_com_csr;
2946 END IF;
2947
2948 IF (l_tl_csr%ISOPEN)
2949 THEN
2950 CLOSE l_tl_csr;
2951 END IF;
2952
2953 x_return_status := fnd_api.g_ret_sts_unexp_error;
2954
2955 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2956 THEN
2957 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2958 END IF;
2959
2960 fnd_msg_pub.count_and_get( p_encoded => 'F'
2961 , p_count => x_msg_count
2962 , p_data => x_msg_data
2963 );
2964 END Secure_Update_note;
2965
2966 END JTF_NOTES_PUB;