[Home] [Help]
PACKAGE BODY: APPS.CAC_NOTES_PVT
Source
1 PACKAGE BODY CAC_NOTES_PVT AS
2 /* $Header: cacvntb.pls 120.4 2006/06/15 10:07:24 sankgupt noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CAC_NOTES_PVT';
5
6 PROCEDURE Add_Invalid_Argument_Msg
7 ------------------------------------------------------------------------------
8 -- Procedure : Add_Invalid_Argument_Msg
9 ------------------------------------------------------------------------------
10 ( p_token_an IN VARCHAR2
11 , p_token_v IN VARCHAR2
12 , p_token_p IN VARCHAR2
13 )
14 IS
15 BEGIN
16 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
17 THEN
18 fnd_message.set_name('JTF', 'JTF_API_ALL_INVALID_ARGUMENT');
19 fnd_message.set_token('API_NAME', p_token_an);
20 fnd_message.set_token('VALUE', p_token_v);
21 fnd_message.set_token('PARAMETER', p_token_p);
22 fnd_msg_pub.add;
23 END IF;
24 END Add_Invalid_Argument_Msg;
25
26
27 PROCEDURE Add_Null_Parameter_Msg
28 ------------------------------------------------------------------------------
29 -- Procedure : Add_Null_Parameter_Msg
30 ------------------------------------------------------------------------------
31 ( p_token_an IN VARCHAR2
32 , p_token_np IN VARCHAR2
33 )
34 IS
35 BEGIN
36 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
37 THEN
38 fnd_message.set_name('JTF', 'JTF_API_ALL_NULL_PARAMETER');
39 fnd_message.set_token('API_NAME', p_token_an);
40 fnd_message.set_token('NULL_PARAM', p_token_np);
41 fnd_msg_pub.add;
42 END IF;
43 END Add_Null_Parameter_Msg;
44
45 PROCEDURE Validate_note_type
46 ------------------------------------------------------------------------------
47 -- Procedure : Validate_note_type
48 ------------------------------------------------------------------------------
49 ( p_api_name IN VARCHAR2
50 , p_parameter_name IN VARCHAR2
51 , p_note_type IN VARCHAR2
52 , x_return_status OUT NOCOPY VARCHAR2
53 )
54 IS
55 l_dummy VARCHAR2(1);
56 BEGIN
57 x_return_status := fnd_api.g_ret_sts_success;
58
59 SELECT 'x' INTO l_dummy
60 FROM fnd_lookup_values
61 WHERE lookup_code = p_note_type
62 AND lookup_type = 'JTF_NOTE_TYPE'
63 AND language = USERENV('LANG');
64
65 EXCEPTION
66 WHEN TOO_MANY_ROWS
67 THEN
68 NULL;
69 WHEN NO_DATA_FOUND
70 THEN
71 x_return_status := fnd_api.g_ret_sts_error;
72 add_invalid_argument_msg( p_api_name
73 , p_note_type
74 , p_parameter_name
75 );
76 WHEN OTHERS
77 THEN
78 x_return_status := fnd_api.g_ret_sts_error;
79 add_invalid_argument_msg( p_api_name
80 , p_note_type
81 , p_parameter_name
82 );
83
84
85 END Validate_note_type;
86
87 PROCEDURE Validate_object
88 ------------------------------------------------------------------------------
89 -- Procedure : Validate_object
90 ------------------------------------------------------------------------------
91 ( p_api_name IN VARCHAR2
92 , p_object_type_code IN VARCHAR2
93 , p_object_type_id IN NUMBER
94 , x_return_status OUT NOCOPY VARCHAR2
95 )
96 IS
97 l_return_status VARCHAR2(30);
98 l_result NUMBER := 0;
99 l_select_id VARCHAR2(200);
100 l_tablename VARCHAR2(200);
101 l_where_clause VARCHAR2(2000);
102 v_cursor NUMBER;
103 v_create_string VARCHAR2(32000);
104 v_numrows NUMBER;
105
106 CURSOR cur_object
107 IS SELECT select_id
108 , from_table
109 , where_clause
110 FROM jtf_objects_vl a
111 , jtf_object_usages b
112 WHERE a.object_code = p_object_type_code
113 AND a.object_code = b.object_code
114 AND b.object_user_code = 'NOTES';
115
116 BEGIN
117 OPEN cur_object;
118 FETCH cur_object INTO l_select_id,l_tablename,l_where_clause ;
119 CLOSE cur_object;
120
121 IF l_where_clause IS NULL
122 THEN
123 v_create_string := 'SELECT COUNT(*) FROM '||l_tablename||
124 ' WHERE '||l_select_id||' = :object_type_id ';
125 ELSE
126 v_create_string := 'SELECT COUNT(*) FROM '||l_tablename||
127 ' WHERE '||l_where_clause||
128 ' AND '||l_select_id||' = :object_type_id ';
129 END IF;
130
131 EXECUTE IMMEDIATE v_create_string
132 INTO l_result
133 USING p_object_type_id;
134
135
136 IF (l_result > 0)
137 THEN
138 x_return_status := fnd_api.g_ret_sts_success;
139 ELSE
140 add_invalid_argument_msg( p_api_name
141 , p_object_type_code
142 , 'Object Code'
143 );
144
145 add_invalid_argument_msg( p_api_name
146 , p_object_type_id
147 , 'Object Id'
148 );
149 x_return_status := fnd_api.g_ret_sts_error;
150 END IF;
151
152 EXCEPTION
153 WHEN OTHERS
154 THEN
155 IF (cur_object%ISOPEN)
156 THEN
157 CLOSE cur_object;
158 END IF;
159 x_return_status := fnd_api.g_ret_sts_error;
160 add_invalid_argument_msg( p_api_name
161 , p_object_type_code
162 , 'Object Code'
163 );
164 add_invalid_argument_msg( p_api_name
165 , p_object_type_id
166 , 'Object Id'
167 );
168 END Validate_Object;
169
170
171
172 PROCEDURE Trunc_String_length
173 ------------------------------------------------------------------------------
174 -- Procedure : Trunc_String_Length
175 ------------------------------------------------------------------------------
176 ( p_api_name IN VARCHAR2
177 , p_parameter_name IN VARCHAR2
178 , p_str IN VARCHAR2
179 , p_len IN NUMBER
180 , x_str OUT NOCOPY VARCHAR2
181 )
182 IS
183 l_len NUMBER;
184
185 BEGIN
186 l_len := LENGTHB(p_str);
187 IF (l_len > p_len)
188 THEN
189 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_success)
190 THEN
191 fnd_message.set_name('JTF', 'JTF_API_ALL_VALUE_TRUNCATED');
192 fnd_message.set_token('API_NAME', p_api_name);
193 fnd_message.set_token('TRUNCATED_PARAM', p_parameter_name);
194 fnd_message.set_token('VAL_LEN', l_len);
195 fnd_message.set_token('DB_LEN', p_len);
196 fnd_msg_pub.add;
197 END IF;
198 x_str := substrb(p_str, 1, p_len);
199 ELSE
200 x_str := p_str;
201 END IF;
202 END Trunc_String_Length;
203
204 PROCEDURE create_note
205 ------------------------------------------------------------------------------
206 -- Create_note
207 -- Inserts a note record in the JTF_NOTES_B, JTF_NOTES_TL tables
208 ------------------------------------------------------------------------------
209 ( p_jtf_note_id IN NUMBER := NULL
210 , p_source_object_id IN NUMBER
211 , p_source_object_code IN VARCHAR2
212 , p_notes IN VARCHAR2
213 , p_notes_detail IN CLOB := NULL
214 , p_note_status IN VARCHAR2 := NULL
215 , p_note_type IN VARCHAR2 := NULL
216 , p_attribute1 IN VARCHAR2 := NULL
217 , p_attribute2 IN VARCHAR2 := NULL
218 , p_attribute3 IN VARCHAR2 := NULL
219 , p_attribute4 IN VARCHAR2 := NULL
220 , p_attribute5 IN VARCHAR2 := NULL
221 , p_attribute6 IN VARCHAR2 := NULL
222 , p_attribute7 IN VARCHAR2 := NULL
223 , p_attribute8 IN VARCHAR2 := NULL
224 , p_attribute9 IN VARCHAR2 := NULL
225 , p_attribute10 IN VARCHAR2 := NULL
226 , p_attribute11 IN VARCHAR2 := NULL
227 , p_attribute12 IN VARCHAR2 := NULL
228 , p_attribute13 IN VARCHAR2 := NULL
229 , p_attribute14 IN VARCHAR2 := NULL
230 , p_attribute15 IN VARCHAR2 := NULL
231 , p_parent_note_id IN NUMBER := NULL
232 , p_entered_date IN DATE := NULL
233 , p_entered_by IN NUMBER := NULL
234 , p_creation_date IN DATE := NULL
235 , p_created_by IN NUMBER := NULL
236 , p_last_update_date IN DATE := NULL
237 , p_last_updated_by IN NUMBER := NULL
238 , p_last_update_login IN NUMBER := NULL
239 , x_jtf_note_id OUT NOCOPY NUMBER
240 , x_return_status OUT NOCOPY VARCHAR2
241 , x_msg_count OUT NOCOPY NUMBER
242 , x_msg_data OUT NOCOPY VARCHAR2
243 )
244 IS
245 l_api_name CONSTANT VARCHAR2(30) := 'Create_note';
246 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
247 l_api_version CONSTANT NUMBER := 1.0;
248
249 l_note_status VARCHAR2(1) := p_note_status;
250 l_jtf_note_id NUMBER := p_jtf_note_id;
251 l_return_status VARCHAR2(1);
252
253 l_notes VARCHAR2(32767) := p_notes;
254 l_msg_count NUMBER ;
255 l_msg_data VARCHAR2(2000);
256 l_bind_data_id NUMBER;
257
258
259 -- Used for keeping track of errors
260 l_missing_param VARCHAR2(30) := NULL;
261 l_null_param VARCHAR2(30) := NULL;
262
263 l_current_date DATE := SYSDATE;
264 l_debug VARCHAR2(2000) := '';
265
266
267 -- Cursor for getting the note ID from the sequence
268 CURSOR l_jtf_note_id_csr
269 IS SELECT JTF_NOTES_S.NEXTVAL
270 FROM DUAL;
271
272 BEGIN
273 -- API savepoint
274 SAVEPOINT create_note_pvt;
275
276
277 -- Initialize return status to SUCCESS
278 x_return_status := fnd_api.g_ret_sts_success;
279
280 --
281 -- Make the preprocessing call to the user hooks
282 --
283 -- Pre call to the Customer Type User Hook
284 --
285 IF jtf_usr_hks.ok_to_execute('JTF_NOTES_PUB'
286 ,'Create_Note'
287 ,'B'
288 ,'C'
289 )
290 THEN
291 jtf_notes_cuhk.create_note_pre
292 ( p_api_version => l_api_version
293 , x_msg_count => x_msg_count
294 , x_msg_data => x_msg_data
295 , p_source_object_id => p_source_object_id
296 , p_source_object_code => p_source_object_code
297 , p_notes => p_notes
298 , p_note_status => p_note_status
299 , p_entered_by => p_entered_by
300 , p_entered_date => p_entered_date
301 , x_jtf_note_id => x_jtf_note_id
302 , p_last_update_date => p_last_update_date
303 , p_last_updated_by => p_last_updated_by
304 , p_creation_date => p_creation_date
305 , p_created_by => p_created_by
306 , p_last_update_login => p_last_update_login
307 , p_attribute1 => p_attribute1
308 , p_attribute2 => p_attribute2
309 , p_attribute3 => p_attribute3
310 , p_attribute4 => p_attribute4
311 , p_attribute5 => p_attribute5
312 , p_attribute6 => p_attribute6
313 , p_attribute7 => p_attribute7
314 , p_attribute8 => p_attribute8
315 , p_attribute9 => p_attribute9
316 , p_attribute10 => p_attribute10
317 , p_attribute11 => p_attribute11
318 , p_attribute12 => p_attribute12
319 , p_attribute13 => p_attribute13
320 , p_attribute14 => p_attribute14
321 , p_attribute15 => p_attribute15
322 , p_note_type => p_note_type
323 , x_return_status => l_return_status
324 );
325
326 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
327 THEN
328 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_CUST_USR_HK');
329 FND_MSG_PUB.Add;
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END IF;
332 END IF;
333
334 -- Pre call to the Vertical Type User Hook
335 --
336 IF jtf_usr_hks.ok_to_execute('JTF_NOTES_PUB'
337 ,'Create_Note'
338 ,'B'
339 ,'V'
340 )
341 THEN
342 jtf_notes_vuhk.create_note_pre
343 ( p_api_version => l_api_version
344 , x_msg_count => l_msg_count
345 , x_msg_data => l_msg_data
346 , p_source_object_id => p_source_object_id
347 , p_source_object_code => p_source_object_code
348 , p_notes => p_notes
349 , p_note_status => p_note_status
350 , p_entered_by => p_entered_by
351 , p_entered_date => p_entered_date
352 , x_jtf_note_id => X_jtf_note_id
353 , p_last_update_date => p_last_update_date
354 , p_last_updated_by => p_last_updated_by
355 , p_creation_date => p_creation_date
356 , p_created_by => p_created_by
357 , p_last_update_login => p_last_update_login
358 , p_attribute1 => p_attribute1
359 , p_attribute2 => p_attribute2
360 , p_attribute3 => p_attribute3
361 , p_attribute4 => p_attribute4
362 , p_attribute5 => p_attribute5
363 , p_attribute6 => p_attribute6
364 , p_attribute7 => p_attribute7
365 , p_attribute8 => p_attribute8
366 , p_attribute9 => p_attribute9
367 , p_attribute10 => p_attribute10
368 , p_attribute11 => p_attribute11
369 , p_attribute12 => p_attribute12
370 , p_attribute13 => p_attribute13
371 , p_attribute14 => p_attribute14
372 , p_attribute15 => p_attribute15
373 , p_note_type => p_note_type
374 , x_return_status => l_return_status
375 );
376
377 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
378 THEN
379 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_VERT_USR_HK');
380 FND_MSG_PUB.Add;
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 END IF;
383 END IF;
384
385 -- Validate source object id and code against object_type_code
386 -- in jtf_object_types_b table
387 Validate_object( p_api_name => l_api_name_full
388 , p_object_type_code => p_source_object_code
389 , p_object_type_id => p_source_object_id
393 IF (l_return_status <> fnd_api.g_ret_sts_success)
390 , x_return_status => l_return_status
391 );
392
394 THEN
395 add_invalid_argument_msg( l_api_name_full
396 , p_source_object_id
397 , 'p_source_object_id'
398 );
399 RAISE fnd_api.g_exc_error;
400 END IF;
401
402 -- Validate note status
403 IF (p_note_status <> fnd_api.g_miss_char)
404 THEN
405 IF (p_note_status NOT IN ('P', 'I','E'))
406 THEN
407 add_invalid_argument_msg( l_api_name_full
408 , p_note_status
409 , 'p_note_status'
410 );
411 RAISE fnd_api.g_exc_error;
412 END IF;
413 END IF;
414
415 -- Validate note length
416 IF (p_notes IS NOT NULL)
417 THEN
418 trunc_string_length( l_api_name_full
419 , 'p_notes'
420 , p_notes
421 , 2000
422 , l_notes
423 );
424 -- Message added in trunc_string_length, no exception..
425 END IF;
426
427 --Validate note_Type
428 IF (p_note_type IS NOT NULL)
429 THEN
430 Validate_note_type( p_api_name => l_api_name_full
431 , p_parameter_name => 'p_note_type'
432 , p_note_type => p_note_type
433 , x_return_status => l_return_status
434 );
435 IF (l_return_status <> fnd_api.g_ret_sts_success)
436 THEN
437 -- Message added in Validate_note_type
438 RAISE fnd_api.g_exc_error;
439 END IF;
440
441 END IF;
442
443
444 IF p_note_status IS NOT NULL
445 THEN
446 l_note_status := p_note_status;
447 ELSE
448 l_note_status := 'I'; -- Internal is the default
449 END IF;
450
451
452 --
453 -- Get jtf_note_id from sequence
454 --
455 IF (l_jtf_note_id IS NULL)
456 THEN
457 OPEN l_jtf_note_id_csr;
458 FETCH l_jtf_note_id_csr INTO l_jtf_note_id;
459 CLOSE l_jtf_note_id_csr;
460 END IF;
461
462 insert into JTF_NOTES_B (
463 SOURCE_OBJECT_CODE,
464 NOTE_STATUS,
465 ENTERED_BY,
466 ENTERED_DATE,
467 NOTE_TYPE,
468 JTF_NOTE_ID,
469 SOURCE_OBJECT_ID,
470 CREATION_DATE,
471 CREATED_BY,
472 LAST_UPDATE_DATE,
473 LAST_UPDATED_BY,
474 LAST_UPDATE_LOGIN,
475 ATTRIBUTE1,
476 ATTRIBUTE2,
477 ATTRIBUTE3,
478 ATTRIBUTE4,
479 ATTRIBUTE5,
480 ATTRIBUTE6,
481 ATTRIBUTE7,
482 ATTRIBUTE8,
483 ATTRIBUTE9,
484 ATTRIBUTE10,
485 ATTRIBUTE11,
486 ATTRIBUTE12,
487 ATTRIBUTE13,
488 ATTRIBUTE14,
489 ATTRIBUTE15,
490 PARENT_NOTE_ID
491 ) values (
492 p_SOURCE_OBJECT_CODE,
493 L_NOTE_STATUS,
494 NVL(p_entered_by,fnd_global.user_id),
495 NVL(p_entered_date,l_current_date),
496 P_NOTE_TYPE,
497 L_JTF_NOTE_ID,
498 P_SOURCE_OBJECT_ID,
499 NVL(p_creation_date,l_current_date),
500 NVL(p_created_by,fnd_global.user_id),
501 NVL(p_last_update_date,l_current_date),
502 NVL(p_last_updated_by,fnd_global.user_id),
503 NVL(p_last_update_login,fnd_global.login_id),
504 P_ATTRIBUTE1,
505 P_ATTRIBUTE2,
506 P_ATTRIBUTE3,
507 P_ATTRIBUTE4,
508 P_ATTRIBUTE5,
509 P_ATTRIBUTE6,
510 P_ATTRIBUTE7,
511 P_ATTRIBUTE8,
512 P_ATTRIBUTE9,
513 P_ATTRIBUTE10,
514 P_ATTRIBUTE11,
515 P_ATTRIBUTE12,
516 P_ATTRIBUTE13,
517 P_ATTRIBUTE14,
518 P_ATTRIBUTE15,
519 P_PARENT_NOTE_ID
520 );
521
522 insert into JTF_NOTES_TL (
523 JTF_NOTE_ID,
524 NOTES,
525 NOTES_DETAIL,
526 CREATION_DATE,
527 CREATED_BY,
528 LAST_UPDATE_DATE,
529 LAST_UPDATED_BY,
530 LAST_UPDATE_LOGIN,
531 LANGUAGE,
532 SOURCE_LANG
533 ) select
534 L_JTF_NOTE_ID,
535 L_NOTES,
536 P_NOTES_DETAIL,
537 NVL(p_creation_date,l_current_date),
538 NVL(p_created_by,fnd_global.user_id),
539 NVL(p_last_update_date,l_current_date),
540 NVL(p_last_updated_by,fnd_global.user_id),
541 NVL(p_last_update_login,fnd_global.login_id),
542 L.LANGUAGE_CODE,
543 userenv('LANG')
544 from FND_LANGUAGES L
545 where L.INSTALLED_FLAG in ('I', 'B');
546
547 INSERT INTO JTF_NOTE_CONTEXTS
548 ( NOTE_CONTEXT_ID,
549 JTF_NOTE_ID,
550 NOTE_CONTEXT_TYPE_ID,
551 NOTE_CONTEXT_TYPE,
552 CREATION_DATE,
553 CREATED_BY,
554 LAST_UPDATE_DATE,
555 LAST_UPDATED_BY,
556 LAST_UPDATE_LOGIN)
557 VALUES (
558 jtf_notes_s.nextval,
559 L_jtf_note_id,
560 P_source_object_id,
561 P_source_object_code,
562 NVL(p_creation_date,l_current_date),
566 NVL(p_last_update_login,fnd_global.login_id)
563 NVL(p_created_by,fnd_global.user_id),
564 NVL(p_last_update_date,l_current_date),
565 NVL(p_last_updated_by,fnd_global.user_id),
567 );
568
569 --
570 -- Make the post processing call to the user hooks
571 --
572 -- Post call to the Customer Type User Hook
573 --
574 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
575 , 'Create_Note'
576 , 'A'
577 , 'C'
578 )
579 THEN
580 jtf_notes_cuhk.create_note_post
581 ( p_api_version => L_api_version
582 , x_msg_count => x_msg_count
583 , x_msg_data => x_msg_data
584 , p_source_object_id => p_source_object_id
585 , p_source_object_code => p_source_object_code
586 , p_notes => P_notes
587 , p_note_status => l_note_status
588 , p_entered_by => FND_GLOBAL.USER_ID
589 , p_entered_date => l_current_date
590 , x_jtf_note_id => X_jtf_note_id
591 , p_last_update_date => l_current_date
592 , p_last_updated_by => FND_GLOBAL.USER_ID
593 , p_creation_date => l_current_date
594 , p_created_by => FND_GLOBAL.USER_ID
595 , p_last_update_login => FND_GLOBAL.LOGIN_ID
596 , p_attribute1 => p_attribute1
597 , p_attribute2 => p_attribute2
598 , p_attribute3 => p_attribute3
599 , p_attribute4 => p_attribute4
600 , p_attribute5 => p_attribute5
601 , p_attribute6 => p_attribute6
602 , p_attribute7 => p_attribute7
603 , p_attribute8 => p_attribute8
604 , p_attribute9 => p_attribute9
605 , p_attribute10 => p_attribute10
606 , p_attribute11 => p_attribute11
607 , p_attribute12 => p_attribute12
608 , p_attribute13 => p_attribute13
609 , p_attribute14 => p_attribute14
610 , p_attribute15 => p_attribute15
611 , p_note_type => p_note_type
612 , x_return_status => l_return_status
613 , p_jtf_note_id => l_jtf_note_id
614 );
615
616 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
617 THEN
618 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_POST_CUST_USR_HK');
619 FND_MSG_PUB.Add;
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 END IF;
622 END IF;
623
624
625 -- Post call to the Vertical Type User Hook
626 --
627 IF jtf_usr_hks.ok_to_execute('JTF_NOTES_PUB'
628 ,'Create_Note'
629 ,'A'
630 ,'V'
631 )
632 THEN
633 jtf_notes_vuhk.create_note_post
634 ( p_api_version => l_api_version
635 , x_msg_count => x_msg_count
636 , x_msg_data => x_msg_data
637 , p_source_object_id => p_source_object_id
638 , p_source_object_code => p_source_object_code
639 , p_notes => l_notes
640 , p_note_status => l_note_status
641 , p_entered_by => FND_GLOBAL.USER_ID
642 , p_entered_date => l_current_date
643 , x_jtf_note_id => X_jtf_note_id
644 , p_last_update_date => l_current_date
645 , p_last_updated_by => FND_GLOBAL.USER_ID
646 , p_creation_date => l_current_date
647 , p_created_by => FND_GLOBAL.USER_ID
648 , p_last_update_login => FND_GLOBAL.LOGIN_ID
649 , p_attribute1 => p_attribute1
650 , p_attribute2 => p_attribute2
651 , p_attribute3 => p_attribute3
652 , p_attribute4 => p_attribute4
653 , p_attribute5 => p_attribute5
654 , p_attribute6 => p_attribute6
655 , p_attribute7 => p_attribute7
656 , p_attribute8 => p_attribute8
657 , p_attribute9 => p_attribute9
658 , p_attribute10 => p_attribute10
659 , p_attribute11 => p_attribute11
660 , p_attribute12 => p_attribute12
661 , p_attribute13 => p_attribute13
662 , p_attribute14 => p_attribute14
663 , p_attribute15 => p_attribute15
664 , p_note_type => p_note_type
665 , x_return_status => l_return_status
666 , p_jtf_note_id => l_jtf_note_id
667 );
668
669 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
670 FND_MESSAGE.Set_Name('JTF', 'JTF_ERR_POST_VERT_USR_HK');
671 FND_MSG_PUB.Add;
672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
673 END IF;
674 END IF;
675
676 -- Standard call for message generation
677 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
678 , 'Create_Note'
679 , 'M'
680 , 'M'
681 )
682 THEN
686 , x_msg_data => x_msg_data
683 IF jtf_notes_cuhk.Ok_to_generate_msg
684 ( p_api_version => L_api_version
685 , x_msg_count => x_msg_count
687 , p_source_object_id => p_source_object_id
688 , p_source_object_code => p_source_object_code
689 , p_notes => p_notes
690 , p_entered_by => FND_GLOBAL.USER_ID
691 , p_entered_date => l_current_date
692 , x_jtf_note_id => X_jtf_note_id
693 , p_last_update_date => l_current_date
694 , p_last_updated_by => FND_GLOBAL.USER_ID
695 , p_creation_date => l_current_date
696 )
697 THEN
698 l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;
699 JTF_USR_HKS.Load_bind_data( l_bind_data_id
700 , 'jtf_note_id'
701 , l_jtf_note_id
702 , 'S'
703 , 'N'
704 );
705
706 JTF_USR_HKS.generate_message( p_prod_code => 'JTF'
707 , p_bus_obj_code => 'NOTES'
708 , p_action_code => 'I'
709 , p_bind_data_id => l_bind_data_id
710 , x_return_code => l_return_status
711 );
712 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
713 THEN
714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715 END IF;
716 END IF;
717 END IF;
718
719 JTF_NOTES_EVENTS_PVT.RaiseCreateNote
720 ( p_NoteID => p_jtf_note_id
721 , p_SourceObjectCode => p_source_object_code
722 , p_SourceObjectID => p_source_object_id
723 );
724
725 fnd_msg_pub.count_and_get( p_encoded => 'F'
726 , p_count => x_msg_count
727 , p_data => x_msg_data
728 );
729
730 x_jtf_note_id := l_jtf_note_id;
731
732 EXCEPTION
733
734
735 WHEN fnd_api.g_exc_error
736 THEN
737 ROLLBACK TO create_note_pvt;
738 x_return_status := fnd_api.g_ret_sts_error;
739 fnd_msg_pub.count_and_get( p_encoded => 'F'
740 , p_count => x_msg_count
741 , p_data => x_msg_data
742 );
743
744 WHEN fnd_api.g_exc_unexpected_error
745 THEN
746 ROLLBACK TO create_note_pvt;
747 x_return_status := fnd_api.g_ret_sts_unexp_error;
748 fnd_msg_pub.count_and_get( p_encoded => 'F'
749 , p_count => x_msg_count
750 , p_data => x_msg_data
751 );
752
753 WHEN OTHERS
754 THEN
755 ROLLBACK TO create_note_pvt;
756 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
757 , l_api_name
758 , l_debug
759 );
760 x_return_status := fnd_api.g_ret_sts_unexp_error;
761 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
762 THEN
763 fnd_msg_pub.add_exc_msg( g_pkg_name
764 , l_api_name
765 );
766 END IF;
767 fnd_msg_pub.count_and_get( p_encoded => 'F'
768 , p_count => x_msg_count
769 , p_data => x_msg_data
770 );
771
772 END create_note;
773
774 PROCEDURE update_note
775 ------------------------------------------------------------------------------
776 -- Update_note
777 -- Updates a note record in the JTF_NOTES_B, JTF_NOTES_TL tables
778 ------------------------------------------------------------------------------
779 ( p_jtf_note_id IN NUMBER
780 , p_notes IN VARCHAR2 := NULL
781 , p_notes_detail IN CLOB := NULL
782 , p_note_status IN VARCHAR2 := NULL
783 , p_note_type IN VARCHAR2 := NULL
784 , p_attribute1 IN VARCHAR2 := NULL
785 , p_attribute2 IN VARCHAR2 := NULL
786 , p_attribute3 IN VARCHAR2 := NULL
787 , p_attribute4 IN VARCHAR2 := NULL
788 , p_attribute5 IN VARCHAR2 := NULL
789 , p_attribute6 IN VARCHAR2 := NULL
790 , p_attribute7 IN VARCHAR2 := NULL
791 , p_attribute8 IN VARCHAR2 := NULL
792 , p_attribute9 IN VARCHAR2 := NULL
793 , p_attribute10 IN VARCHAR2 := NULL
794 , p_attribute11 IN VARCHAR2 := NULL
795 , p_attribute12 IN VARCHAR2 := NULL
796 , p_attribute13 IN VARCHAR2 := NULL
797 , p_attribute14 IN VARCHAR2 := NULL
798 , p_attribute15 IN VARCHAR2 := NULL
799 , p_parent_note_id IN NUMBER := NULL
800 , p_last_update_date IN DATE := NULL
801 , p_last_updated_by IN NUMBER := NULL
802 , p_last_update_login IN NUMBER := NULL
803 , x_return_status OUT NOCOPY VARCHAR2
804 , x_msg_count OUT NOCOPY NUMBER
808 l_api_name CONSTANT VARCHAR2(30) := 'Update_note';
805 , x_msg_data OUT NOCOPY VARCHAR2
806 )
807 IS
809 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
810 l_api_version CONSTANT NUMBER := 1.0;
811 l_notes VARCHAR2(32767) := p_notes;
812 l_note_status VARCHAR2(2000) := p_note_status;
813 l_note_type VARCHAR2(2000) := p_note_type;
814 l_return_status VARCHAR2(1);
815 l_msg_count NUMBER ;
816 l_msg_data VARCHAR2(2000);
817 l_bind_data_id NUMBER;
818 l_current_date DATE := SYSDATE;
819 l_debug VARCHAR2(2000) := '';
820 l_source_object_code VARCHAR2(240);
821 l_source_object_id NUMBER;
822
823 BEGIN
824
825 -- API savepoint
826 SAVEPOINT update_note_pvt;
827
828
829 -- Initialize return status to SUCCESS
830 x_return_status := fnd_api.g_ret_sts_success;
831
832 --
833 -- Customer User Hook pre update
834 --
835 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
836 , 'Update Note'
837 , 'B'
838 , 'C'
839 )
840 THEN
841 jtf_notes_cuhk.update_note_pre
842 ( p_api_version => l_api_version
843 , x_msg_count => l_msg_count
844 , x_msg_data => l_msg_data
845 , p_jtf_note_id => p_jtf_note_id
846 , p_entered_by => FND_GLOBAL.USER_ID
847 , p_last_updated_by => p_last_updated_by
848 , p_notes => p_notes
849 , p_note_status => p_note_status
850 , p_note_type => p_note_type
851 , x_return_status => l_return_status
852 );
853
854 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
855 THEN
856 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_CUST_USR_HK');
857 FND_MSG_PUB.Add;
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 END IF;
860 END IF;
861
862 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
863 , 'Update Note'
864 , 'B'
865 , 'V'
866 )
867 THEN
868 jtf_notes_vuhk.update_note_pre
869 ( p_api_version => l_api_version
870 , x_msg_count => l_msg_count
871 , x_msg_data => l_msg_data
872 , p_jtf_note_id => p_jtf_note_id
873 , p_entered_by => FND_GLOBAL.USER_ID
874 , p_last_updated_by => p_last_updated_by
875 , p_notes => p_notes
876 , p_note_status => p_note_status
877 , p_note_type => p_note_type
878 , x_return_status => l_return_status
879 );
880
881 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
882 THEN
883 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_PRE_CUST_USR_HK');
884 FND_MSG_PUB.Add;
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886 END IF;
887 END IF;
888
889 IF (l_note_status = FND_API.G_MISS_CHAR)
890 THEN
891 l_note_status := NULL;
892 END IF;
893
894 IF (l_note_type = FND_API.G_MISS_CHAR)
895 THEN
896 l_note_type := NULL;
897 END IF;
898
899 -- Validate note length
900 IF (p_notes IS NOT NULL)
901 THEN
902 trunc_string_length( l_api_name_full
903 , 'p_notes'
904 , p_notes
905 , 2000
906 , l_notes
907 );
908 -- Message added in trunc_string_length, no exception..
909 END IF;
910
911 --
912 -- Perform the database operation.
913 --
914 UPDATE JTF_NOTES_B
915 SET last_updated_by = NVL(p_last_updated_by,fnd_global.user_id)
916 , last_update_date = NVL(p_last_update_date,l_current_date)
917 , last_update_login = NVL(p_last_update_login,fnd_global.login_id)
918 , note_status = NVL(l_note_status,note_status)
919 , note_type = l_note_type
920 , attribute1 = p_attribute1
921 , attribute2 = p_attribute2
922 , attribute3 = p_attribute3
923 , attribute4 = p_attribute4
924 , attribute5 = p_attribute5
925 , attribute6 = p_attribute6
926 , attribute7 = p_attribute7
927 , attribute8 = p_attribute8
928 , attribute9 = p_attribute9
929 , attribute10 = p_attribute10
930 , attribute11 = p_attribute11
931 , attribute12 = p_attribute12
932 , attribute13 = p_attribute13
933 , attribute14 = p_attribute14
934 , attribute15 = p_attribute15
935 , parent_note_id = p_parent_note_id
936 WHERE jtf_note_id = p_jtf_note_id;
937
938 IF (p_notes_detail IS NULL)
939 THEN
940 UPDATE JTF_NOTES_TL
944 , LAST_UPDATE_LOGIN = NVL(p_last_update_login,fnd_global.login_id)
941 SET NOTES = NVL(l_notes,NOTES)
942 , LAST_UPDATE_DATE = NVL(p_last_update_date,l_current_date)
943 , LAST_UPDATED_BY = NVL(p_last_updated_by,fnd_global.user_id)
945 , SOURCE_LANG = USERENV('LANG')
946 WHERE JTF_NOTE_ID = p_jtf_note_id;
947 ELSE
948 UPDATE JTF_NOTES_TL
949 SET NOTES = NVL(l_notes,NOTES)
950 , NOTES_DETAIL = p_notes_detail
951 , LAST_UPDATE_DATE = NVL(p_last_update_date,l_current_date)
952 , LAST_UPDATED_BY = NVL(p_last_updated_by,fnd_global.user_id)
953 , LAST_UPDATE_LOGIN = NVL(p_last_update_login,fnd_global.login_id)
954 , SOURCE_LANG = USERENV('LANG')
955 WHERE JTF_NOTE_ID = p_jtf_note_id;
956 END IF;
957
958 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
959 , 'Update Note'
960 , 'A'
961 , 'C'
962 )
963 THEN
964 jtf_notes_cuhk.update_note_post
965 ( p_api_version => l_api_version
966 , x_msg_count => l_msg_count
967 , x_msg_data => l_msg_data
968 , p_jtf_note_id => p_jtf_note_id
969 , p_entered_by => FND_GLOBAL.USER_ID
970 , p_last_updated_by => p_last_updated_by
971 , p_notes => l_notes
972 , p_note_status => l_note_status
973 , p_note_type => l_note_type
974 , x_return_status => l_return_status
975 );
976
977 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
978 THEN
979 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_POST_CUST_USR_HK');
980 FND_MSG_PUB.Add;
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982 END IF;
983 END IF;
984
985 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
986 , 'Update Note'
987 , 'A'
988 , 'V'
989 )
990 THEN
991 jtf_notes_vuhk.update_note_post
992 ( p_api_version => l_api_version
993 , x_msg_count => l_msg_count
994 , x_msg_data => l_msg_data
995 , p_jtf_note_id => p_jtf_note_id
996 , p_entered_by => FND_GLOBAL.USER_ID
997 , p_last_updated_by => p_last_updated_by
998 , p_notes => l_notes
999 , p_note_status => l_note_status
1000 , p_note_type => l_note_type
1001 , x_return_status => l_return_status
1002 );
1003
1004 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1005 THEN
1006 FND_MESSAGE.Set_Name('JTF', 'JTF_API_ERR_POST_VERT_USR_HK');
1007 FND_MSG_PUB.Add;
1008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1009 END IF;
1010 END IF;
1011
1012 -- Standard call for message generation
1013 IF jtf_usr_hks.ok_to_execute( 'JTF_NOTES_PUB'
1014 , 'Create_Note'
1015 , 'M'
1016 , 'M'
1017 )
1018 THEN
1019 IF jtf_notes_cuhk.ok_to_generate_msg
1020 ( p_api_version => l_api_version
1021 , x_msg_count => l_msg_count
1022 , x_msg_data => l_msg_data
1023 , p_jtf_note_id => p_jtf_note_id
1024 , p_entered_by => FND_GLOBAL.USER_ID
1025 , p_last_updated_by => p_last_updated_by
1026 , p_notes => l_notes
1027 , p_note_status => l_note_status
1028 , p_note_type => l_note_type
1029 , x_return_status => l_return_status
1030 )
1031 THEN
1032 l_bind_data_id := JTF_USR_HKS.Get_bind_data_id;
1033 JTF_USR_HKS.Load_bind_data( l_bind_data_id
1034 , 'jtf_note_id'
1035 , p_jtf_note_id
1036 , 'S'
1037 , 'N'
1038 );
1039
1040 JTF_USR_HKS.generate_message( p_prod_code => 'JTF'
1041 , p_bus_obj_code => 'NOTES'
1042 , p_action_code => 'I'
1043 , p_bind_data_id => l_bind_data_id
1044 , x_return_code => l_return_status
1045 );
1046 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1047 THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END IF;
1050 END IF;
1051 END IF;
1052
1053 --
1054 -- get the source onject information so we can pas it to the WF event
1055 --
1056 SELECT source_object_code
1057 , source_object_id
1058 INTO l_source_object_code
1059 , l_source_object_id
1060 FROM jtf_notes_b
1061 WHERE jtf_note_id = p_jtf_note_id;
1062
1063 JTF_NOTES_EVENTS_PVT.RaiseUpdateNote
1064 ( p_NoteID => p_jtf_note_id
1065 , p_SourceObjectCode => l_source_object_code
1066 , p_SourceObjectID => l_source_object_id
1070 , p_count => x_msg_count
1067 );
1068
1069 fnd_msg_pub.count_and_get( p_encoded => 'F'
1071 , p_data => x_msg_data
1072 );
1073
1074 EXCEPTION
1075
1076
1077 WHEN fnd_api.g_exc_error
1078 THEN
1079 ROLLBACK TO update_note_pvt;
1080 x_return_status := fnd_api.g_ret_sts_error;
1081 fnd_msg_pub.count_and_get( p_encoded => 'F'
1082 , p_count => x_msg_count
1083 , p_data => x_msg_data
1084 );
1085
1086 WHEN fnd_api.g_exc_unexpected_error
1087 THEN
1088 ROLLBACK TO update_note_pvt;
1089 x_return_status := fnd_api.g_ret_sts_unexp_error;
1090 fnd_msg_pub.count_and_get( p_encoded => 'F'
1091 , p_count => x_msg_count
1092 , p_data => x_msg_data
1093 );
1094
1095 WHEN OTHERS
1096 THEN
1097 ROLLBACK TO update_note_pvt;
1098 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1099 , l_api_name
1100 , l_debug
1101 );
1102 x_return_status := fnd_api.g_ret_sts_unexp_error;
1103 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1104 THEN
1105 fnd_msg_pub.add_exc_msg( g_pkg_name
1106 , l_api_name
1107 );
1108 END IF;
1109 fnd_msg_pub.count_and_get( p_encoded => 'F'
1110 , p_count => x_msg_count
1111 , p_data => x_msg_data
1112 );
1113 END update_note;
1114
1115 PROCEDURE delete_note
1116 ------------------------------------------------------------------------------
1117 -- delete_note
1118 -- deletes a note record in the JTF_NOTES_B, JTF_NOTES_TL tables
1119 ------------------------------------------------------------------------------
1120 ( p_jtf_note_id IN NUMBER
1121 , x_return_status OUT NOCOPY VARCHAR2
1122 , x_msg_count OUT NOCOPY NUMBER
1123 , x_msg_data OUT NOCOPY VARCHAR2
1124 )
1125 IS
1126 l_api_name CONSTANT VARCHAR2(30) := 'delete_note';
1127 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1128 l_source_object_code VARCHAR2(240);
1129 l_source_object_id NUMBER;
1130
1131 BEGIN
1132 -- API savepoint
1133 SAVEPOINT delete_note_pvt;
1134
1135 --
1136 -- Initialize API return status to success
1137 --
1138 x_return_status := FND_API.G_RET_STS_SUCCESS;
1139
1140 --
1141 -- get the source onject information so we can pas it to the WF event
1142 --
1143 SELECT source_object_code
1144 , source_object_id
1145 INTO l_source_object_code
1146 , l_source_object_id
1147 FROM jtf_notes_b
1148 WHERE jtf_note_id = p_jtf_note_id;
1149
1150 --
1151 -- Delete the note and it's references
1152 --
1153 DELETE FROM jtf_note_contexts WHERE jtf_note_id = p_jtf_note_id;
1154 DELETE FROM jtf_notes_tl WHERE jtf_note_id = p_jtf_note_id;
1155 DELETE FROM jtf_notes_b WHERE jtf_note_id = p_jtf_note_id;
1156
1157
1158 JTF_NOTES_EVENTS_PVT.RaiseDeleteNote
1159 ( p_NoteID => p_jtf_note_id
1160 , p_SourceObjectCode => l_source_object_code
1161 , p_SourceObjectID => l_source_object_id
1162 );
1163
1164 --
1165 -- Standard call to get message count and if count is 1, get message info
1166 --
1167 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1168 , p_data => x_msg_data
1169 );
1170
1171 EXCEPTION
1172 WHEN OTHERS
1173 THEN
1174 ROLLBACK TO delete_note_pvt;
1175 --
1176 -- Set status
1177 --
1178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1179
1180 --
1181 -- Push message onto CRM stack
1182 --
1183 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1184 , l_api_name
1185 , SQLERRM
1186 );
1187 --
1188 -- Get error message from FND stack
1189 --
1190 x_msg_data := FND_MESSAGE.GET;
1191
1192 --
1193 -- Count the messages on the CRM stack
1194 --
1195 x_msg_count := FND_MSG_PUB.COUNT_MSG;
1196
1197 END delete_note;
1198
1199 PROCEDURE create_note_context
1200 ------------------------------------------------------------------------------
1201 -- create_note_context
1202 -- creates a record in the JTF_NOTE_CONTEXTS table.
1203 ------------------------------------------------------------------------------
1204 ( p_note_context_id IN NUMBER
1205 , p_jtf_note_id IN NUMBER
1206 , p_note_context_type IN VARCHAR2
1207 , p_note_context_type_id IN NUMBER
1208 , p_creation_date IN DATE := NULL
1209 , p_created_by IN NUMBER := NULL
1210 , p_last_update_date IN DATE := NULL
1211 , p_last_updated_by IN NUMBER := NULL
1215 , x_msg_count OUT NOCOPY NUMBER
1212 , p_last_update_login IN NUMBER := NULL
1213 , x_note_context_id OUT NOCOPY NUMBER
1214 , x_return_status OUT NOCOPY VARCHAR2
1216 , x_msg_data OUT NOCOPY VARCHAR2
1217 )
1218 IS
1219 -- Cursor that will check for duplicates
1220 CURSOR c_duplicate
1221 ( b_jtf_note_id IN NUMBER
1222 , b_note_context_type IN VARCHAR2
1223 , b_note_context_type_id IN NUMBER
1224 )IS SELECT note_context_id
1225 FROM jtf_note_contexts
1226 WHERE jtf_note_id = b_jtf_note_id
1227 AND note_context_type = b_note_context_type
1228 AND note_context_type_id = b_note_context_type_id;
1229
1230 l_api_name CONSTANT VARCHAR2(200) := 'create_note_context';
1231 l_api_name_full CONSTANT VARCHAR2(200) := g_pkg_name || '.' || l_api_name;
1232 l_debug VARCHAR2(2000) := '';
1233 l_current_date DATE := SYSDATE;
1234
1235 BEGIN
1236
1237 -- API savepoint
1238 SAVEPOINT create_note_context_pvt;
1239
1240 -- Initialize return status to SUCCESS
1241 x_return_status := fnd_api.g_ret_sts_success;
1242
1243 -- Validate source object id and code
1244 Validate_object( p_api_name => l_api_name_full
1245 , p_object_type_code => p_note_context_type
1246 , p_object_type_id => p_note_context_type_id
1247 , x_return_status => x_return_status
1248 );
1249
1250 IF (x_return_status <> fnd_api.g_ret_sts_success)
1251 THEN
1252 RAISE fnd_api.g_exc_error;
1253 END IF;
1254
1255 -- we should only do the insert if the relation doesn't already exist
1256 OPEN c_duplicate( p_jtf_note_id
1257 , p_note_context_type
1258 , p_note_context_type_id
1259 );
1260
1261 FETCH c_duplicate INTO x_note_context_id;
1262
1263 IF (c_duplicate%NOTFOUND)
1264 THEN
1265 INSERT INTO JTF_NOTE_CONTEXTS
1266 (
1267 NOTE_CONTEXT_ID,
1268 JTF_NOTE_ID,
1269 NOTE_CONTEXT_TYPE_ID,
1270 NOTE_CONTEXT_TYPE,
1271 CREATION_DATE,
1272 CREATED_BY,
1273 LAST_UPDATE_DATE,
1274 LAST_UPDATED_BY,
1275 LAST_UPDATE_LOGIN
1276 )
1277 VALUES
1278 (
1279 NVL(p_note_context_id,jtf_notes_s.nextval),
1280 p_jtf_note_id,
1281 p_note_context_type_id,
1282 p_note_context_type,
1283 NVL(p_creation_date,l_current_date),
1284 NVL(p_created_by,fnd_global.user_id),
1285 NVL(p_last_update_date,l_current_date),
1286 NVL(p_last_updated_by,fnd_global.user_id),
1287 NVL(p_last_update_login,fnd_global.login_id)
1288 )
1289 RETURNING note_context_id INTO x_note_context_id;
1290
1291 ELSE
1292 -- pretend the insert was succesfull and return the ID for the (first)
1293 -- duplicate record
1294 NULL;
1295 END IF;
1296
1297 CLOSE c_duplicate;
1298
1299 fnd_msg_pub.count_and_get( p_encoded => 'F'
1300 , p_count => x_msg_count
1301 , p_data => x_msg_data
1302 );
1303
1304 EXCEPTION
1305
1306 WHEN fnd_api.g_exc_error
1307 THEN
1308 ROLLBACK TO create_note_context_pvt;
1309 x_return_status := fnd_api.g_ret_sts_error;
1310 fnd_msg_pub.count_and_get( p_encoded => 'F'
1311 , p_count => x_msg_count
1312 , p_data => x_msg_data
1313 );
1314
1315 WHEN fnd_api.g_exc_unexpected_error
1316 THEN
1317 ROLLBACK TO create_note_context_pvt;
1318 x_return_status := fnd_api.g_ret_sts_unexp_error;
1319 fnd_msg_pub.count_and_get( p_encoded => 'F'
1320 , p_count => x_msg_count
1321 , p_data => x_msg_data
1322 );
1323
1324 WHEN OTHERS
1325 THEN
1326 ROLLBACK TO create_note_context_pvt;
1327 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1328 , l_api_name
1329 , l_debug
1330 );
1331 x_return_status := fnd_api.g_ret_sts_unexp_error;
1332 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1333 THEN
1334 fnd_msg_pub.add_exc_msg( g_pkg_name
1335 , l_api_name
1336 );
1337 END IF;
1338 fnd_msg_pub.count_and_get( p_encoded => 'F'
1339 , p_count => x_msg_count
1340 , p_data => x_msg_data
1341 );
1342
1343 END create_note_context;
1344
1345
1346 PROCEDURE update_note_context
1347 ------------------------------------------------------------------------------
1348 -- update_note_context
1349 -- updates a record in the JTF_NOTE_CONTEXTS table.
1350 ------------------------------------------------------------------------------
1351 ( p_note_context_id IN NUMBER
1352 , p_jtf_note_id IN NUMBER := NULL
1353 , p_note_context_type IN VARCHAR2 := NULL
1357 , p_last_update_login IN NUMBER := NULL
1354 , p_note_context_type_id IN NUMBER := NULL
1355 , p_last_update_date IN DATE := NULL
1356 , p_last_updated_by IN NUMBER := NULL
1358 , x_return_status OUT NOCOPY VARCHAR2
1359 , x_msg_count OUT NOCOPY NUMBER
1360 , x_msg_data OUT NOCOPY VARCHAR2
1361 )
1362 IS
1363 l_api_name CONSTANT VARCHAR2(200) := 'update_note_context';
1364 l_api_name_full CONSTANT VARCHAR2(200) := g_pkg_name || '.' || l_api_name;
1365 l_debug VARCHAR2(2000) := '';
1366
1367 BEGIN
1368
1369 -- API savepoint
1370 SAVEPOINT update_note_context_pvt;
1371
1372 -- Initialize return status to SUCCESS
1373 x_return_status := fnd_api.g_ret_sts_success;
1374
1375 -- Validate source object id and code
1376 Validate_object( p_api_name => l_api_name_full
1377 , p_object_type_code => p_note_context_type
1378 , p_object_type_id => p_note_context_type_id
1379 , x_return_status => x_return_status
1380 );
1381
1382 IF (x_return_status <> fnd_api.g_ret_sts_success)
1383 THEN
1384 RAISE fnd_api.g_exc_error;
1385 END IF;
1386
1387 UPDATE JTF_NOTE_CONTEXTS
1388 SET
1389 JTF_NOTE_ID = NVL(p_jtf_note_id,JTF_NOTE_ID),
1390 NOTE_CONTEXT_TYPE_ID = NVL(p_note_context_type_id,NOTE_CONTEXT_TYPE_ID),
1391 NOTE_CONTEXT_TYPE = NVL(p_note_context_type,NOTE_CONTEXT_TYPE),
1392 LAST_UPDATE_DATE = NVL(p_last_update_date,SYSDATE),
1393 LAST_UPDATED_BY = NVL(p_last_updated_by,fnd_global.user_id),
1394 LAST_UPDATE_LOGIN = NVL(p_last_update_login,fnd_global.login_id)
1395 WHERE NOTE_CONTEXT_ID = p_note_context_id;
1396
1397 fnd_msg_pub.count_and_get( p_encoded => 'F'
1398 , p_count => x_msg_count
1399 , p_data => x_msg_data
1400 );
1401
1402 EXCEPTION
1403
1404 WHEN fnd_api.g_exc_error
1405 THEN
1406 ROLLBACK TO update_note_context_pvt;
1407 x_return_status := fnd_api.g_ret_sts_error;
1408 fnd_msg_pub.count_and_get( p_encoded => 'F'
1409 , p_count => x_msg_count
1410 , p_data => x_msg_data
1411 );
1412
1413 WHEN fnd_api.g_exc_unexpected_error
1414 THEN
1415 ROLLBACK TO update_note_context_pvt;
1416 x_return_status := fnd_api.g_ret_sts_unexp_error;
1417 fnd_msg_pub.count_and_get( p_encoded => 'F'
1418 , p_count => x_msg_count
1419 , p_data => x_msg_data
1420 );
1421
1422 WHEN OTHERS
1423 THEN
1424 ROLLBACK TO update_note_context_pvt;
1425 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1426 , l_api_name
1427 , l_debug
1428 );
1429 x_return_status := fnd_api.g_ret_sts_unexp_error;
1430 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1431 THEN
1432 fnd_msg_pub.add_exc_msg( g_pkg_name
1433 , l_api_name
1434 );
1435 END IF;
1436 fnd_msg_pub.count_and_get( p_encoded => 'F'
1437 , p_count => x_msg_count
1438 , p_data => x_msg_data
1439 );
1440
1441 END update_note_context;
1442
1443
1444 PROCEDURE delete_note_context
1445 ------------------------------------------------------------------------------
1446 -- delete_note_context
1447 -- deletes a record in the JTF_NOTE_CONTEXTS table.
1448 ------------------------------------------------------------------------------
1449 ( p_note_context_id IN NUMBER
1450 , x_return_status OUT NOCOPY VARCHAR2
1451 , x_msg_count OUT NOCOPY NUMBER
1452 , x_msg_data OUT NOCOPY VARCHAR2
1453 )
1454 IS
1455 l_api_name VARCHAR2(2000) := 'delete_note_context';
1456 l_debug VARCHAR2(2000) := '';
1457
1458 BEGIN
1459
1460 -- API savepoint
1461 SAVEPOINT delete_note_context_pvt;
1462
1463 -- Initialize return status to SUCCESS
1464 x_return_status := fnd_api.g_ret_sts_success;
1465
1466 DELETE FROM JTF_NOTE_CONTEXTS
1467 WHERE NOTE_CONTEXT_ID = p_note_context_id;
1468
1469 fnd_msg_pub.count_and_get( p_encoded => 'F'
1470 , p_count => x_msg_count
1471 , p_data => x_msg_data
1472 );
1473
1474 EXCEPTION
1475
1476 WHEN OTHERS
1477 THEN
1478 ROLLBACK TO delete_note_context_pvt;
1479 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1480 , l_api_name
1481 , l_debug
1482 );
1483 x_return_status := fnd_api.g_ret_sts_unexp_error;
1484 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1485 THEN
1486 fnd_msg_pub.add_exc_msg( g_pkg_name
1487 , l_api_name
1488 );
1489 END IF;
1490 fnd_msg_pub.count_and_get( p_encoded => 'F'
1491 , p_count => x_msg_count
1492 , p_data => x_msg_data
1493 );
1494
1495 END delete_note_context;
1496
1497 FUNCTION GET_ENTERED_BY_NAME
1498 /*******************************************************************************
1499 ** Given a USER_ID the function will return the username/partyname. This
1500 ** Function is used to display the CREATED_BY who column information on JTF
1501 ** transaction pages.
1502 *******************************************************************************/
1503 (p_user_id IN NUMBER
1504 )RETURN VARCHAR2
1505 IS
1506 CURSOR c_user
1507 /****************************************************************************
1508 ** Cursor used to fetch the foreign keys needed to access the source tables
1509 ****************************************************************************/
1510 (b_user_id IN NUMBER
1511 )IS SELECT employee_id
1512 , customer_id
1513 , supplier_id
1514 , user_name
1515 FROM fnd_user
1516 WHERE user_id = b_user_id;
1517
1518 CURSOR c_employee
1519 /****************************************************************************
1520 ** Cursor used to fetch the employee name in case the foreign key is to an
1521 ** Employee
1522 ****************************************************************************/
1523 (b_employee_id IN NUMBER
1524 )IS SELECT full_name
1525 , employee_number
1526 FROM per_all_people_f
1527 WHERE person_id = b_employee_id;
1528
1529 CURSOR c_party
1530 /****************************************************************************
1531 ** Cursor used to fetch the party name in case the foreign key is to a
1532 ** Customer or Supplier
1533 ****************************************************************************/
1534 (b_party_id IN NUMBER
1535 )IS SELECT party_name
1536 , party_number
1537 FROM hz_parties
1538 WHERE party_id = b_party_id;
1539
1540 CURSOR c_supplier
1541 /****************************************************************************
1542 ** Cursor used to fetch the supplier name in case the foreign key is to a
1543 ** Supplier
1544 ****************************************************************************/
1545 ( b_supplier_id IN NUMBER
1546 ) IS SELECT LAST_NAME|| ',' || FIRST_NAME || MIDDLE_NAME full_name,
1547 VENDOR_CONTACT_ID
1548 FROM po_vendor_contacts
1552 l_supplier_id NUMBER;
1549 WHERE VENDOR_CONTACT_ID = b_supplier_id ;
1550 l_employee_id NUMBER;
1551 l_customer_id NUMBER;
1553 l_user_name VARCHAR2(360);
1554
1555 l_number VARCHAR2(30);
1556 l_name VARCHAR2(240);
1557 l_display_info VARCHAR2(500);
1558
1559
1560 BEGIN
1561 /*****************************************************************************
1562 ** Get the foreigh keys to the user information
1563 *****************************************************************************/
1564 IF c_user%ISOPEN
1565 THEN
1566 CLOSE c_user;
1567 END IF;
1568
1569 OPEN c_user(p_user_id);
1570
1571 FETCH c_user INTO l_employee_id,l_customer_id,l_supplier_id,l_user_name;
1572
1573 IF c_user%ISOPEN
1574 THEN
1575 CLOSE c_user;
1576 END IF;
1577
1578 IF (l_employee_id IS NOT NULL)
1579 THEN
1580 -- get the employee information
1581 IF c_employee%ISOPEN
1582 THEN
1583 CLOSE c_employee;
1584 END IF;
1585
1586 OPEN c_employee(l_employee_id);
1587
1588 FETCH c_employee INTO l_name,l_number;
1589
1590 IF c_employee%ISOPEN
1591 THEN
1592 CLOSE c_employee;
1593 END IF;
1594
1595 --bug # 3178448, remove employee id
1596 l_number := NULL;
1597
1598 ELSIF (l_customer_id IS NOT NULL)
1599 THEN
1600 -- get the customer information
1601 IF c_party%ISOPEN
1602 THEN
1603 CLOSE c_party;
1604 END IF;
1605
1606 OPEN c_party(l_customer_id);
1607
1608 FETCH c_party INTO l_name, l_number;
1609
1610 IF c_party%ISOPEN
1611 THEN
1612 CLOSE c_party;
1613 END IF;
1614
1615 ELSIF (l_supplier_id IS NOT NULL)
1616 THEN
1617 -- get the supplier information
1618 IF c_supplier%ISOPEN
1619 THEN
1620 CLOSE c_supplier;
1621 END IF;
1622
1623 OPEN c_supplier(l_supplier_id);
1624
1625 FETCH c_supplier INTO l_name, l_number;
1626
1627 IF c_supplier%ISOPEN
1628 THEN
1629 CLOSE c_supplier;
1630 END IF;
1631 END IF;
1632
1633 IF l_name IS NULL
1634 THEN
1635 RETURN l_user_name;
1636 ELSE
1637 --bug # 3178448, remove employee id
1638 IF l_number IS NULL
1639 THEN
1640 RETURN l_name||'('||l_user_name||')';
1641 ELSE
1642 RETURN l_name||'('||l_user_name||','||l_number||')';
1643 END IF;
1644 END IF;
1645
1646 EXCEPTION
1647 WHEN OTHERS
1648 THEN
1649 IF c_employee%ISOPEN
1650 THEN
1651 CLOSE c_employee;
1652 END IF;
1653
1654 IF c_party%ISOPEN
1655 THEN
1656 CLOSE c_party;
1657 END IF;
1658 RETURN 'Not Found';
1659 END GET_ENTERED_BY_NAME;
1660
1661 END CAC_NOTES_PVT;