[Home] [Help]
PACKAGE BODY: APPS.JTF_NOTES_PKG
Source
1 package body JTF_NOTES_PKG as
2 /* $Header: jtfnttbb.pls 120.1.12010000.2 2009/05/26 11:44:25 ipananil ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_JTF_NOTE_ID in NUMBER,
6 X_SOURCE_OBJECT_CODE in VARCHAR2,
7 X_NOTE_STATUS in VARCHAR2,
8 X_ENTERED_BY in NUMBER,
9 X_ENTERED_DATE in DATE,
10 X_NOTE_TYPE in VARCHAR2,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_CONTEXT in VARCHAR2,
27 X_PARENT_NOTE_ID in NUMBER,
28 X_SOURCE_OBJECT_ID in NUMBER,
29 X_NOTES in VARCHAR2,
30 X_NOTES_DETAIL in VARCHAR2,
31 X_CREATION_DATE in DATE,
32 X_CREATED_BY in NUMBER,
33 X_LAST_UPDATE_DATE in DATE,
34 X_LAST_UPDATED_BY in NUMBER,
35 X_LAST_UPDATE_LOGIN in NUMBER
36 ) is
37 cursor C is select ROWID from JTF_NOTES_B
38 where JTF_NOTE_ID = X_JTF_NOTE_ID
39 ;
40 begin
41 insert into JTF_NOTES_B (
42 SOURCE_OBJECT_CODE,
43 NOTE_STATUS,
44 ENTERED_BY,
45 ENTERED_DATE,
46 NOTE_TYPE,
47 ATTRIBUTE1,
48 ATTRIBUTE2,
49 ATTRIBUTE3,
50 ATTRIBUTE4,
51 ATTRIBUTE5,
52 ATTRIBUTE6,
53 ATTRIBUTE7,
54 ATTRIBUTE8,
55 ATTRIBUTE9,
56 ATTRIBUTE10,
57 ATTRIBUTE11,
58 ATTRIBUTE12,
59 ATTRIBUTE13,
60 ATTRIBUTE14,
61 ATTRIBUTE15,
62 CONTEXT,
63 JTF_NOTE_ID,
64 PARENT_NOTE_ID,
65 SOURCE_OBJECT_ID,
66 CREATION_DATE,
67 CREATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_LOGIN
71 ) values (
72 X_SOURCE_OBJECT_CODE,
73 X_NOTE_STATUS,
74 X_ENTERED_BY,
75 X_ENTERED_DATE,
76 X_NOTE_TYPE,
77 X_ATTRIBUTE1,
78 X_ATTRIBUTE2,
79 X_ATTRIBUTE3,
80 X_ATTRIBUTE4,
81 X_ATTRIBUTE5,
82 X_ATTRIBUTE6,
83 X_ATTRIBUTE7,
84 X_ATTRIBUTE8,
85 X_ATTRIBUTE9,
86 X_ATTRIBUTE10,
87 X_ATTRIBUTE11,
88 X_ATTRIBUTE12,
89 X_ATTRIBUTE13,
90 X_ATTRIBUTE14,
91 X_ATTRIBUTE15,
92 X_CONTEXT,
93 X_JTF_NOTE_ID,
94 X_PARENT_NOTE_ID,
95 X_SOURCE_OBJECT_ID,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_DATE,
99 X_LAST_UPDATED_BY,
100 X_LAST_UPDATE_LOGIN
101 );
102
103 insert into JTF_NOTES_TL (
104 JTF_NOTE_ID,
105 NOTES,
106 NOTES_DETAIL,
107 LAST_UPDATE_DATE,
108 LAST_UPDATED_BY,
109 CREATION_DATE,
110 CREATED_BY,
111 LAST_UPDATE_LOGIN,
112 LANGUAGE,
113 SOURCE_LANG
114 ) select
115 X_JTF_NOTE_ID,
116 X_NOTES,
117 EMPTY_CLOB(),
118 X_LAST_UPDATE_DATE,
119 X_LAST_UPDATED_BY,
120 X_CREATION_DATE,
121 X_CREATED_BY,
122 X_LAST_UPDATE_LOGIN,
123 L.LANGUAGE_CODE,
124 userenv('LANG')
125 from FND_LANGUAGES L
126 where L.INSTALLED_FLAG in ('I', 'B');
127
128 If X_Notes_Detail is not null then
129 JTF_NOTES_PUB.writedatatolob(X_JTF_NOTE_ID,X_NOTES_DETAIL);
130
131 end If;
132
133 INSERT INTO JTF_NOTE_CONTEXTS
134 ( NOTE_CONTEXT_ID,
135 JTF_NOTE_ID,
136 NOTE_CONTEXT_TYPE_ID,
137 NOTE_CONTEXT_TYPE,
138 CREATION_DATE,
139 CREATED_BY,
140 LAST_UPDATE_DATE,
141 LAST_UPDATED_BY,
142 LAST_UPDATE_LOGIN)
143 VALUES (
144 jtf_notes_s.nextval,
145 x_jtf_note_id,
146 x_source_object_id,
147 x_source_object_code,
148 x_creation_date,
149 x_created_by,
150 x_last_update_date,
151 x_last_updated_by,
152 x_last_update_login
153 );
154 open c;
155 fetch c into X_ROWID;
156 if (c%notfound) then
157 close c;
158 raise no_data_found;
159 end if;
160 close c;
161
162 end INSERT_ROW;
163
164 procedure LOCK_ROW (
165 X_JTF_NOTE_ID in NUMBER,
166 X_SOURCE_OBJECT_CODE in VARCHAR2,
167 X_NOTE_STATUS in VARCHAR2,
168 X_ENTERED_BY in NUMBER,
169 X_ENTERED_DATE in DATE,
170 X_NOTE_TYPE in VARCHAR2,
171 X_ATTRIBUTE1 in VARCHAR2,
172 X_ATTRIBUTE2 in VARCHAR2,
173 X_ATTRIBUTE3 in VARCHAR2,
174 X_ATTRIBUTE4 in VARCHAR2,
175 X_ATTRIBUTE5 in VARCHAR2,
176 X_ATTRIBUTE6 in VARCHAR2,
177 X_ATTRIBUTE7 in VARCHAR2,
178 X_ATTRIBUTE8 in VARCHAR2,
179 X_ATTRIBUTE9 in VARCHAR2,
180 X_ATTRIBUTE10 in VARCHAR2,
181 X_ATTRIBUTE11 in VARCHAR2,
182 X_ATTRIBUTE12 in VARCHAR2,
183 X_ATTRIBUTE13 in VARCHAR2,
184 X_ATTRIBUTE14 in VARCHAR2,
185 X_ATTRIBUTE15 in VARCHAR2,
186 X_CONTEXT in VARCHAR2,
187 X_PARENT_NOTE_ID in NUMBER,
188 X_SOURCE_OBJECT_ID in NUMBER,
189 X_NOTES in VARCHAR2,
190 X_NOTES_DETAIL in VARCHAR2
191 ) is
192 cursor c is select
193 SOURCE_OBJECT_CODE,
194 NOTE_STATUS,
195 ENTERED_BY,
196 ENTERED_DATE,
197 NOTE_TYPE,
198 ATTRIBUTE1,
199 ATTRIBUTE2,
200 ATTRIBUTE3,
201 ATTRIBUTE4,
202 ATTRIBUTE5,
203 ATTRIBUTE6,
204 ATTRIBUTE7,
205 ATTRIBUTE8,
206 ATTRIBUTE9,
207 ATTRIBUTE10,
208 ATTRIBUTE11,
209 ATTRIBUTE12,
210 ATTRIBUTE13,
211 ATTRIBUTE14,
212 ATTRIBUTE15,
213 CONTEXT,
214 PARENT_NOTE_ID,
215 SOURCE_OBJECT_ID
216 from JTF_NOTES_B
217 where JTF_NOTE_ID = X_JTF_NOTE_ID
218 for update of JTF_NOTE_ID nowait;
219 recinfo c%rowtype;
220
221 cursor c1 is select
222 NOTES,
223 NOTES_DETAIL,
224 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
225 from JTF_NOTES_TL
226 where JTF_NOTE_ID = X_JTF_NOTE_ID
227 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
228 for update of JTF_NOTE_ID nowait;
229 begin
230 open c;
231 fetch c into recinfo;
232 if (c%notfound) then
233 close c;
234 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
235 app_exception.raise_exception;
236 end if;
237 close c;
238 if ( (recinfo.SOURCE_OBJECT_CODE = X_SOURCE_OBJECT_CODE)
239 AND ((recinfo.NOTE_STATUS = X_NOTE_STATUS)
240 OR ((recinfo.NOTE_STATUS is null) AND (X_NOTE_STATUS is null)))
241 AND (recinfo.ENTERED_BY = X_ENTERED_BY)
242 AND (recinfo.ENTERED_DATE = X_ENTERED_DATE)
243 AND ((recinfo.NOTE_TYPE = X_NOTE_TYPE)
244 OR ((recinfo.NOTE_TYPE is null) AND (X_NOTE_TYPE is null)))
245 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
246 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
247 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
248 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
249 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
250 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
251 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
252 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
253 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
254 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
255 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
256 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
257 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
258 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
259 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
260 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
261 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
262 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
263 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
264 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
265 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
266 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
267 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
268 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
269 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
270 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
271 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
272 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
273 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
274 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
275 AND ((recinfo.CONTEXT = X_CONTEXT)
276 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
277 AND ((recinfo.PARENT_NOTE_ID = X_PARENT_NOTE_ID)
278 OR ((recinfo.PARENT_NOTE_ID is null) AND (X_PARENT_NOTE_ID is null)))
279 AND (recinfo.SOURCE_OBJECT_ID = X_SOURCE_OBJECT_ID)
280 ) then
281 null;
282 else
283 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
284 app_exception.raise_exception;
285 end if;
286 for tlinfo in c1 loop
287 if (tlinfo.BASELANG = 'Y') then
288 if (tlinfo.NOTES = X_NOTES) then
289 /*
290 AND ((DBMS_LOB.COMPARE(tlinfo.NOTES_DETAIL , X_NOTES_DETAIL,DBMS_LOB.GETLENGTH(X_NOTES_DETAIL),1,1) = 0)
291 OR ((tlinfo.NOTES_DETAIL is null) AND (X_NOTES_DETAIL is null)))
292 )then */
293 null;
294 else
295 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
296 app_exception.raise_exception;
297 end if;
298 end if;
299 end loop;
300 return;
301 end LOCK_ROW;
302
303 procedure UPDATE_ROW (
304 X_JTF_NOTE_ID in NUMBER,
305 X_SOURCE_OBJECT_CODE in VARCHAR2,
306 X_NOTE_STATUS in VARCHAR2,
307 X_ENTERED_BY in NUMBER,
308 X_ENTERED_DATE in DATE,
309 X_NOTE_TYPE in VARCHAR2,
310 X_ATTRIBUTE1 in VARCHAR2,
311 X_ATTRIBUTE2 in VARCHAR2,
312 X_ATTRIBUTE3 in VARCHAR2,
313 X_ATTRIBUTE4 in VARCHAR2,
314 X_ATTRIBUTE5 in VARCHAR2,
315 X_ATTRIBUTE6 in VARCHAR2,
316 X_ATTRIBUTE7 in VARCHAR2,
317 X_ATTRIBUTE8 in VARCHAR2,
318 X_ATTRIBUTE9 in VARCHAR2,
319 X_ATTRIBUTE10 in VARCHAR2,
320 X_ATTRIBUTE11 in VARCHAR2,
321 X_ATTRIBUTE12 in VARCHAR2,
322 X_ATTRIBUTE13 in VARCHAR2,
323 X_ATTRIBUTE14 in VARCHAR2,
324 X_ATTRIBUTE15 in VARCHAR2,
325 X_CONTEXT in VARCHAR2,
326 X_PARENT_NOTE_ID in NUMBER,
327 X_SOURCE_OBJECT_ID in NUMBER,
328 X_NOTES in VARCHAR2,
329 X_NOTES_DETAIL in VARCHAR2,
330 X_LAST_UPDATE_DATE in DATE,
331 X_LAST_UPDATED_BY in NUMBER,
332 X_LAST_UPDATE_LOGIN in NUMBER
333 ) is
334 begin
335 update JTF_NOTES_B set
336 SOURCE_OBJECT_CODE = X_SOURCE_OBJECT_CODE,
337 NOTE_STATUS = X_NOTE_STATUS,
338 ENTERED_BY = X_ENTERED_BY,
339 ENTERED_DATE = X_ENTERED_DATE,
340 NOTE_TYPE = X_NOTE_TYPE,
341 ATTRIBUTE1 = X_ATTRIBUTE1,
342 ATTRIBUTE2 = X_ATTRIBUTE2,
343 ATTRIBUTE3 = X_ATTRIBUTE3,
344 ATTRIBUTE4 = X_ATTRIBUTE4,
345 ATTRIBUTE5 = X_ATTRIBUTE5,
346 ATTRIBUTE6 = X_ATTRIBUTE6,
347 ATTRIBUTE7 = X_ATTRIBUTE7,
348 ATTRIBUTE8 = X_ATTRIBUTE8,
349 ATTRIBUTE9 = X_ATTRIBUTE9,
350 ATTRIBUTE10 = X_ATTRIBUTE10,
351 ATTRIBUTE11 = X_ATTRIBUTE11,
352 ATTRIBUTE12 = X_ATTRIBUTE12,
353 ATTRIBUTE13 = X_ATTRIBUTE13,
354 ATTRIBUTE14 = X_ATTRIBUTE14,
355 ATTRIBUTE15 = X_ATTRIBUTE15,
356 CONTEXT = X_CONTEXT,
357 PARENT_NOTE_ID = X_PARENT_NOTE_ID,
358 SOURCE_OBJECT_ID = X_SOURCE_OBJECT_ID,
359 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
360 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
361 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
362 where JTF_NOTE_ID = X_JTF_NOTE_ID;
363
364 if (sql%notfound) then
365 raise no_data_found;
366 end if;
367
368 update JTF_NOTES_TL set
369 NOTES = X_NOTES,
370 NOTES_DETAIL = X_NOTES_DETAIL,
371 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
372 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
373 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
374 SOURCE_LANG = userenv('LANG')
375 where JTF_NOTE_ID = X_JTF_NOTE_ID
376 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
377
378 if (sql%notfound) then
379 raise no_data_found;
380 end if;
381 end UPDATE_ROW;
382
383 procedure DELETE_ROW (
384 X_JTF_NOTE_ID in NUMBER
385 ) is
386 begin
387 delete from JTF_NOTES_TL
388 where JTF_NOTE_ID = X_JTF_NOTE_ID;
389
390 if (sql%notfound) then
391 raise no_data_found;
392 end if;
393
394 delete from JTF_NOTES_B
395 where JTF_NOTE_ID = X_JTF_NOTE_ID;
396
397 if (sql%notfound) then
398 raise no_data_found;
399 end if;
400 end DELETE_ROW;
401
402 procedure ADD_LANGUAGE
403 is
404 begin
405
406 /** Commented for now for the bug #4229850
407 delete from JTF_NOTES_TL T
408 where not exists
409 (select NULL
410 from JTF_NOTES_B B
411 where B.JTF_NOTE_ID = T.JTF_NOTE_ID
412 );
413
414 update JTF_NOTES_TL T set (
415 NOTES,
416 NOTES_DETAIL
417 ) = (select
418 B.NOTES,
419 B.NOTES_DETAIL
420 from JTF_NOTES_TL B
421 where B.JTF_NOTE_ID = T.JTF_NOTE_ID
422 and B.LANGUAGE = T.SOURCE_LANG)
423 where (
424 T.JTF_NOTE_ID,
425 T.LANGUAGE
426 ) in (select
427 SUBT.JTF_NOTE_ID,
428 SUBT.LANGUAGE
429 from JTF_NOTES_TL SUBB, JTF_NOTES_TL SUBT
430 where SUBB.JTF_NOTE_ID = SUBT.JTF_NOTE_ID
431 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
432 and (SUBB.NOTES <> SUBT.NOTES
433 or (( DBMS_LOB.COMPARE(SUBB.NOTES_DETAIL, SUBT.NOTES_DETAIL,DBMS_LOB.GETLENGTH(SUBB.NOTES_DETAIL),1,1)) <> 0)
434 or (SUBB.NOTES_DETAIL is null and SUBT.NOTES_DETAIL is not null)
435 or (SUBB.NOTES_DETAIL is not null and SUBT.NOTES_DETAIL is null)
436 ));
437 **/
438 /* Commented the code for Perf. Bug 4229850 */
439 /*
440 insert into JTF_NOTES_TL (
441 JTF_NOTE_ID,
442 NOTES,
443 NOTES_DETAIL,
444 LAST_UPDATE_DATE,
445 LAST_UPDATED_BY,
446 CREATION_DATE,
447 CREATED_BY,
448 LAST_UPDATE_LOGIN,
449 LANGUAGE,
450 SOURCE_LANG
451 ) select
452 B.JTF_NOTE_ID,
453 B.NOTES,
454 B.NOTES_DETAIL,
455 B.LAST_UPDATE_DATE,
456 B.LAST_UPDATED_BY,
457 B.CREATION_DATE,
458 B.CREATED_BY,
459 B.LAST_UPDATE_LOGIN,
460 L.LANGUAGE_CODE,
461 B.SOURCE_LANG
462 from JTF_NOTES_TL B, FND_LANGUAGES L
463 where L.INSTALLED_FLAG in ('I', 'B')
464 and B.LANGUAGE = userenv('LANG')
465 and not exists
466 (select NULL
467 from JTF_NOTES_TL T
468 where T.JTF_NOTE_ID = B.JTF_NOTE_ID
469 and T.LANGUAGE = L.LANGUAGE_CODE);
470 */
471 --
472 -- Added this code Perf. Bug 3723927
473 --
474 insert /*+ append parallel(tl) */ into JTF_NOTES_TL tl
475 (
476 JTF_NOTE_ID,
477 NOTES,
478 NOTES_DETAIL,
479 LAST_UPDATE_DATE,
480 LAST_UPDATED_BY,
481 CREATION_DATE,
482 CREATED_BY,
483 LAST_UPDATE_LOGIN,
484 LANGUAGE,
485 SOURCE_LANG
486 )
487 SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v.*
488 FROM ( SELECT /*+ no_merge ordered parallel(b) */
489 B.JTF_NOTE_ID,
490 B.NOTES,
491 B.NOTES_DETAIL,
492 B.LAST_UPDATE_DATE,
493 B.LAST_UPDATED_BY,
494 B.CREATION_DATE,
495 B.CREATED_BY,
496 B.LAST_UPDATE_LOGIN,
497 L.LANGUAGE_CODE,
498 B.SOURCE_LANG
499 from JTF_NOTES_TL B, FND_LANGUAGES L
500 where L.INSTALLED_FLAG in ('I', 'B')
501 and B.LANGUAGE = userenv('LANG')
502 ) v, JTF_NOTES_TL t
503 where t.JTF_NOTE_ID(+) = v.JTF_NOTE_ID
504 AND t.language(+) = v.language_code
505 AND t.JTF_NOTE_ID is null;
506
507 end ADD_LANGUAGE;
508
509
510 PROCEDURE writeDatatoLob
511 (X_jtf_note_id NUMBER,
512 X_BUFFER VARCHAR2)
513 Is
514 lob_loc CLOB;
515 Position INTEGER := 1;
516 Buffer VARCHAR2(32767);
517
518 cursor c1 is
519 select notes_detail
520 from jtf_notes_tl
521 where jtf_note_id = x_jtf_note_id
522 for update;
523 Begin
524
525 for i in c1
526 loop
527 DBMS_LOB.WRITE(i.notes_detail,length(X_BUFFER),position,x_buffer);
528 end loop;
529 End WriteDataToLob;
530
531 FUNCTION get_note_context_value( p_note_context_type IN VARCHAR2
532 , p_note_context_type_id IN NUMBER )
533 RETURN VARCHAR2 IS
534 l_note_context_value VARCHAR2(240);
535 l_select_id jtf_objects_b.select_id%TYPE;
536 l_select_name jtf_objects_b.select_name%TYPE;
537 l_tablename jtf_objects_b.from_table%TYPE;
538 l_where_clause jtf_objects_b.where_clause%TYPE;
539 v_cursor NUMBER;
540 v_create_string VARCHAR2(3000);
541 v_numrows NUMBER;
542
543 CURSOR cur_object IS
544 SELECT select_id
545 , select_name
546 , from_table
547 , where_clause
548 FROM jtf_objects_vl a
549 , jtf_object_usages b
550 WHERE a.object_code = p_note_context_type
551 AND a.object_code = b.object_code
552 AND b.object_user_code = 'NOTES';
553 BEGIN
554 OPEN cur_object;
555
556 FETCH cur_object
557 INTO l_select_id, l_select_name, l_tablename, l_where_clause;
558
559 CLOSE cur_object;
560
561 v_cursor := DBMS_SQL.open_cursor;
562
563 IF l_where_clause IS NOT NULL THEN
564 v_create_string :=
565 'SELECT '
566 || l_select_name
567 || ' FROM '
568 || l_tablename
569 || ' WHERE '
570 || l_select_id
571 || ' = :note_context_type_id'
572 || ' AND '
573 || l_where_clause;
574 ELSE
575 v_create_string :=
576 'SELECT '
577 || l_select_name
578 || ' FROM '
579 || l_tablename
580 || ' WHERE '
581 || l_select_id
582 || ' = :note_context_type_id';
583 END IF;
584
585 DBMS_SQL.parse(v_cursor, v_create_string, DBMS_SQL.v7);
586
587 DBMS_SQL.bind_variable(v_cursor, 'note_context_type_id', p_note_context_type_id);
588 DBMS_SQL.define_column(v_cursor, 1, l_note_context_value, 80);
589
590 v_numrows := DBMS_SQL.EXECUTE(v_cursor);
591 v_numrows := DBMS_SQL.fetch_rows(v_cursor);
592
593 DBMS_SQL.column_value(v_cursor, 1, l_note_context_value);
594 DBMS_SQL.close_cursor(v_cursor);
595
596 RETURN l_note_context_value;
597 EXCEPTION
598 WHEN OTHERS THEN
599 RETURN NULL;
600 END get_note_context_value;
601
602 procedure LOAD_ROW (
603 X_OWNER in VARCHAR2,
604 X_JTF_NOTE_ID in NUMBER,
605 X_SOURCE_OBJECT_CODE in VARCHAR2,
606 X_SOURCE_OBJECT_ID in NUMBER,
607 X_SOURCE_NUMBER in VARCHAR2,
608 X_NOTE_STATUS in VARCHAR2,
609 X_NOTE_TYPE_MEANING in VARCHAR2,
610 X_NOTE_TYPE in VARCHAR2,
611 X_CONTEXT in VARCHAR2,
612 X_PARENT_NOTE_ID in NUMBER,
613 X_SOURCE_OBJECT_MEANING in VARCHAR2,
614 X_NOTES in VARCHAR2,
615 X_NOTES_DETAIL in VARCHAR2,
616 X_OBJECT_VERSION_NUMBER in NUMBER,
617 X_ATTRIBUTE1 in VARCHAR2,
618 X_ATTRIBUTE2 in VARCHAR2,
619 X_ATTRIBUTE3 in VARCHAR2,
620 X_ATTRIBUTE4 in VARCHAR2,
621 X_ATTRIBUTE5 in VARCHAR2,
622 X_ATTRIBUTE6 in VARCHAR2,
623 X_ATTRIBUTE7 in VARCHAR2,
624 X_ATTRIBUTE8 in VARCHAR2,
625 X_ATTRIBUTE9 in VARCHAR2,
626 X_ATTRIBUTE10 in VARCHAR2,
627 X_ATTRIBUTE11 in VARCHAR2,
628 X_ATTRIBUTE12 in VARCHAR2,
629 X_ATTRIBUTE13 in VARCHAR2,
630 X_ATTRIBUTE14 in VARCHAR2,
631 X_ATTRIBUTE15 in VARCHAR2
632
633 ) IS
634 begin
635 declare
636 user_id NUMBER := 0;
637 row_id VARCHAR2(64);
638 l_api_version NUMBER := 1.0;
639 l_return_status VARCHAR2(1);
640 l_msg_count NUMBER;
641 l_msg_data VARCHAR2(100);
642 l_init_msg_list VARCHAR2(1) := 'F';
643 l_commit VARCHAR2(1) := 'F';
644 l_validation_level NUMBER := 100;
645 l_jtf_note_id NUMBER;
646 l_source_object_code VARCHAR2(240);
647 l_source_object_id NUMBER;
648 l_source_number VARCHAR2(4000);
649 l_note_status VARCHAR2(1);
650 l_note_type_meaning VARCHAR2(80);
651 l_object_version_number NUMBER;
652 l_note_type VARCHAR2(30);
653 l_context VARCHAR2(240);
654 l_parent_note_id NUMBER;
655 l_source_object_meaning VARCHAR2(30);
656 l_notes VARCHAR2(4000);
657 l_notes_detail VARCHAR2(32000);
658 l_last_update_date DATE;
659 l_last_updated_by NUMBER;
660 l_last_update_login NUMBER;
661 l_creation_date DATE;
662 l_created_by NUMBER;
663 l_entered_by NUMBER;
664 begin
665 if (x_owner = 'SEED') then
666 user_id := -1;
667 end if;
668 l_jtf_note_id:= X_JTF_NOTE_ID;
669 l_object_version_number := 1;
670 l_source_object_id :=X_SOURCE_OBJECT_ID;
671 l_source_object_code :=X_SOURCE_OBJECT_CODE;
672 --l_source_number :=X_SOURCE_NUMBER;
673 l_note_status :=X_NOTE_STATUS;
674 --l_note_type_meaning :=X_NOTE_TYPE_MEANING;
675 l_object_version_number := 1;
676 l_note_type :=X_NOTE_TYPE;
677 l_context :=X_CONTEXT;
678 l_parent_note_id :=X_PARENT_NOTE_ID;
679 --l_source_object_meaning :=X_SOURCE_OBJECT_MEANING;
680 l_notes :=X_NOTES;
681 l_notes_detail:= NULL;
682 l_last_update_date := sysdate;
683 l_last_updated_by := user_id;
684 l_last_update_login := 0;
685 l_entered_by:= user_id;
686 UPDATE_ROW (
687 X_JTF_NOTE_ID =>l_jtf_note_id,
688 X_SOURCE_OBJECT_CODE=>l_source_object_code,
689 X_NOTE_STATUS =>l_note_status,
690 X_ENTERED_BY =>l_entered_by,
691 X_ENTERED_DATE => sysdate,
692 X_NOTE_TYPE =>l_note_type,
693 X_ATTRIBUTE1 => null,
694 X_ATTRIBUTE2=> null,
695 X_ATTRIBUTE3 => null,
696 X_ATTRIBUTE4 => null,
697 X_ATTRIBUTE5 => null,
698 X_ATTRIBUTE6 => null,
699 X_ATTRIBUTE7 => null,
700 X_ATTRIBUTE8 => null,
701 X_ATTRIBUTE9 => null,
702 X_ATTRIBUTE10 => null,
703 X_ATTRIBUTE11 => null,
704 X_ATTRIBUTE12 => null,
705 X_ATTRIBUTE13 => null,
706 X_ATTRIBUTE14 => null,
707 X_ATTRIBUTE15 => null,
708 X_CONTEXT =>l_context,
709 X_PARENT_NOTE_ID =>l_parent_note_id,
710 X_SOURCE_OBJECT_ID =>l_source_object_id,
711 X_NOTES => l_notes,
712 X_NOTES_DETAIL => l_notes_detail,
713 X_LAST_UPDATE_DATE =>l_last_update_date,
714 X_LAST_UPDATED_BY =>l_last_updated_by,
715 X_LAST_UPDATE_LOGIN =>l_last_update_login
716 );
717
718 EXCEPTION
719 when no_data_found then
720 l_creation_date := sysdate;
721 l_created_by := user_id;
722
723 INSERT_ROW (
724 X_ROWID => row_id,
725 X_JTF_NOTE_ID =>l_jtf_note_id ,
726 X_SOURCE_OBJECT_CODE =>l_source_object_code,
727 X_NOTE_STATUS =>l_note_status,
728 X_ENTERED_BY =>l_entered_by,
729 X_ENTERED_DATE => sysdate,
730 X_NOTE_TYPE =>l_note_type,
731 X_ATTRIBUTE1 => null,
732 X_ATTRIBUTE2 => null,
733 X_ATTRIBUTE3 => null,
734 X_ATTRIBUTE4 => null,
735 X_ATTRIBUTE5 => null,
736 X_ATTRIBUTE6 => null,
737 X_ATTRIBUTE7 => null,
738 X_ATTRIBUTE8 => null,
739 X_ATTRIBUTE9 => null,
740 X_ATTRIBUTE10 => null,
741 X_ATTRIBUTE11 => null,
742 X_ATTRIBUTE12 => null,
743 X_ATTRIBUTE13 => null,
744 X_ATTRIBUTE14 => null,
745 X_ATTRIBUTE15 => null,
746 X_CONTEXT => l_context,
747 X_PARENT_NOTE_ID => l_parent_note_id,
748 X_SOURCE_OBJECT_ID => l_source_object_id,
749 X_NOTES => l_notes,
750 X_NOTES_DETAIL => l_notes_detail,
751 X_CREATION_DATE => l_creation_date,
752 X_CREATED_BY => l_created_by,
753 X_LAST_UPDATE_DATE =>sysdate,
754 X_LAST_UPDATED_BY => l_last_updated_by,
755 X_LAST_UPDATE_LOGIN =>l_last_update_login);
756
757 end;
758 end LOAD_ROW;
759
760 procedure TRANSLATE_ROW (
761 X_JTF_NOTE_ID in NUMBER,
762 X_NOTES in VARCHAR2,
763 X_NOTES_DETAIL in VARCHAR2,
764 X_OBJECT_VERSION_NUMBER in NUMBER,
765 X_OWNER in VARCHAR2
766 ) IS
767
768 begin
769 UPDATE jtf_notes_tl SET
770 notes=X_NOTES,
771 notes_detail = X_NOTES_DETAIL,
772 last_update_date = sysdate,
773 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
774 last_update_login = 0,
775 source_lang = userenv('LANG')
776 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
777 jtf_note_id = X_JTF_NOTE_ID;
778 end TRANSLATE_ROW;
779
780
781
782 end JTF_NOTES_PKG;