[Home] [Help]
PACKAGE BODY: APPS.JTF_NOTES_PKG
Source
1 package body JTF_NOTES_PKG as
2 /* $Header: jtfnttbb.pls 120.1 2005/07/02 00:51:50 appldev 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
532 ( p_note_context_type IN VARCHAR2,
533 p_note_context_type_id IN NUMBER) RETURN VARCHAR2
534 IS
535
536 l_note_context_value VARCHAR2(240);
537 l_select_id jtf_objects_b.select_id%type;
538 l_select_name jtf_objects_b.select_name%type;
539 l_tablename jtf_objects_b.from_table%type;
540 l_where_clause jtf_objects_b.where_clause%type;
541 v_cursor number;
542 v_create_string varchar2(3000);
543 v_numrows number;
544
545 CURSOR cur_object
546 IS
547 SELECT select_id,select_name,from_table, where_clause
548 FROM jtf_objects_vl a,jtf_object_usages b
549 WHERE a.object_code = p_note_context_type
550 AND a.object_code = b.object_code
551 AND b.object_user_code = 'NOTES';
552
553 BEGIN
554 OPEN cur_object;
555 FETCH cur_object INTO l_select_id,l_select_name,l_tablename,l_where_clause;
556 CLOSE cur_object;
557
558 v_cursor := DBMS_SQL.OPEN_CURSOR;
559
560 If l_where_clause is not null then
561 v_create_string := 'SELECT '||l_select_name||' FROM '||l_tablename||
562 ' WHERE '||l_select_id||' = '||p_note_context_type_id||
563 ' AND '||l_where_clause;
564 else
565 v_create_string := 'SELECT '||l_select_name||' FROM '||l_tablename||
566 ' WHERE '||l_select_id||' = '||p_note_context_type_id;
567 end if;
568 -- dbms_output.put_line('string is '||v_create_string);
569
570 DBMS_SQL.PARSE(v_cursor,v_create_string,DBMS_SQL.V7);
571 DBMS_SQL.DEFINE_COLUMN(v_cursor,1,l_note_context_value,80);
572 v_numrows := DBMS_SQL.EXECUTE(v_cursor);
573
574 v_numrows := DBMS_SQL.FETCH_ROWS(v_cursor);
575
576 DBMS_SQL.COLUMN_VALUE(v_cursor,1,l_note_context_value);
577
578
579 DBMS_SQL.CLOSE_CURSOR(v_cursor);
580
581 return l_note_context_value;
582
583
584 EXCEPTION
585 WHEN OTHERS THEN
586 -- dbms_output.put_line('Error '||SQLERRM);
587
588 return null;
589 END get_note_context_value;
590
591 procedure LOAD_ROW (
592 X_OWNER in VARCHAR2,
593 X_JTF_NOTE_ID in NUMBER,
594 X_SOURCE_OBJECT_CODE in VARCHAR2,
595 X_SOURCE_OBJECT_ID in NUMBER,
596 X_SOURCE_NUMBER in VARCHAR2,
597 X_NOTE_STATUS in VARCHAR2,
598 X_NOTE_TYPE_MEANING in VARCHAR2,
599 X_NOTE_TYPE in VARCHAR2,
600 X_CONTEXT in VARCHAR2,
601 X_PARENT_NOTE_ID in NUMBER,
602 X_SOURCE_OBJECT_MEANING in VARCHAR2,
603 X_NOTES in VARCHAR2,
604 X_NOTES_DETAIL in VARCHAR2,
605 X_OBJECT_VERSION_NUMBER in NUMBER,
606 X_ATTRIBUTE1 in VARCHAR2,
607 X_ATTRIBUTE2 in VARCHAR2,
608 X_ATTRIBUTE3 in VARCHAR2,
609 X_ATTRIBUTE4 in VARCHAR2,
610 X_ATTRIBUTE5 in VARCHAR2,
611 X_ATTRIBUTE6 in VARCHAR2,
612 X_ATTRIBUTE7 in VARCHAR2,
613 X_ATTRIBUTE8 in VARCHAR2,
614 X_ATTRIBUTE9 in VARCHAR2,
615 X_ATTRIBUTE10 in VARCHAR2,
616 X_ATTRIBUTE11 in VARCHAR2,
617 X_ATTRIBUTE12 in VARCHAR2,
618 X_ATTRIBUTE13 in VARCHAR2,
619 X_ATTRIBUTE14 in VARCHAR2,
620 X_ATTRIBUTE15 in VARCHAR2
621
622 ) IS
623 begin
624 declare
625 user_id NUMBER := 0;
626 row_id VARCHAR2(64);
627 l_api_version NUMBER := 1.0;
628 l_return_status VARCHAR2(1);
629 l_msg_count NUMBER;
630 l_msg_data VARCHAR2(100);
631 l_init_msg_list VARCHAR2(1) := 'F';
632 l_commit VARCHAR2(1) := 'F';
633 l_validation_level NUMBER := 100;
634 l_jtf_note_id NUMBER;
635 l_source_object_code VARCHAR2(240);
636 l_source_object_id NUMBER;
637 l_source_number VARCHAR2(4000);
638 l_note_status VARCHAR2(1);
639 l_note_type_meaning VARCHAR2(80);
640 l_object_version_number NUMBER;
641 l_note_type VARCHAR2(30);
642 l_context VARCHAR2(240);
643 l_parent_note_id NUMBER;
644 l_source_object_meaning VARCHAR2(30);
645 l_notes VARCHAR2(4000);
646 l_notes_detail VARCHAR2(32000);
647 l_last_update_date DATE;
648 l_last_updated_by NUMBER;
649 l_last_update_login NUMBER;
650 l_creation_date DATE;
651 l_created_by NUMBER;
652 l_entered_by NUMBER;
653 begin
654 if (x_owner = 'SEED') then
655 user_id := -1;
656 end if;
657 l_jtf_note_id:= X_JTF_NOTE_ID;
658 l_object_version_number := 1;
659 l_source_object_id :=X_SOURCE_OBJECT_ID;
660 l_source_object_code :=X_SOURCE_OBJECT_CODE;
661 --l_source_number :=X_SOURCE_NUMBER;
662 l_note_status :=X_NOTE_STATUS;
663 --l_note_type_meaning :=X_NOTE_TYPE_MEANING;
664 l_object_version_number := 1;
665 l_note_type :=X_NOTE_TYPE;
666 l_context :=X_CONTEXT;
667 l_parent_note_id :=X_PARENT_NOTE_ID;
668 --l_source_object_meaning :=X_SOURCE_OBJECT_MEANING;
669 l_notes :=X_NOTES;
670 l_notes_detail:= NULL;
671 l_last_update_date := sysdate;
672 l_last_updated_by := user_id;
673 l_last_update_login := 0;
674 l_entered_by:= user_id;
675 UPDATE_ROW (
676 X_JTF_NOTE_ID =>l_jtf_note_id,
677 X_SOURCE_OBJECT_CODE=>l_source_object_code,
678 X_NOTE_STATUS =>l_note_status,
679 X_ENTERED_BY =>l_entered_by,
680 X_ENTERED_DATE => sysdate,
681 X_NOTE_TYPE =>l_note_type,
682 X_ATTRIBUTE1 => null,
683 X_ATTRIBUTE2=> null,
684 X_ATTRIBUTE3 => null,
685 X_ATTRIBUTE4 => null,
686 X_ATTRIBUTE5 => null,
687 X_ATTRIBUTE6 => null,
688 X_ATTRIBUTE7 => null,
689 X_ATTRIBUTE8 => null,
690 X_ATTRIBUTE9 => null,
691 X_ATTRIBUTE10 => null,
692 X_ATTRIBUTE11 => null,
693 X_ATTRIBUTE12 => null,
694 X_ATTRIBUTE13 => null,
695 X_ATTRIBUTE14 => null,
696 X_ATTRIBUTE15 => null,
697 X_CONTEXT =>l_context,
698 X_PARENT_NOTE_ID =>l_parent_note_id,
699 X_SOURCE_OBJECT_ID =>l_source_object_id,
700 X_NOTES => l_notes,
701 X_NOTES_DETAIL => l_notes_detail,
702 X_LAST_UPDATE_DATE =>l_last_update_date,
703 X_LAST_UPDATED_BY =>l_last_updated_by,
704 X_LAST_UPDATE_LOGIN =>l_last_update_login
705 );
706
707 EXCEPTION
708 when no_data_found then
709 l_creation_date := sysdate;
710 l_created_by := user_id;
711
712 INSERT_ROW (
713 X_ROWID => row_id,
714 X_JTF_NOTE_ID =>l_jtf_note_id ,
715 X_SOURCE_OBJECT_CODE =>l_source_object_code,
716 X_NOTE_STATUS =>l_note_status,
717 X_ENTERED_BY =>l_entered_by,
718 X_ENTERED_DATE => sysdate,
719 X_NOTE_TYPE =>l_note_type,
720 X_ATTRIBUTE1 => null,
721 X_ATTRIBUTE2 => null,
722 X_ATTRIBUTE3 => null,
723 X_ATTRIBUTE4 => null,
724 X_ATTRIBUTE5 => null,
725 X_ATTRIBUTE6 => null,
726 X_ATTRIBUTE7 => null,
727 X_ATTRIBUTE8 => null,
728 X_ATTRIBUTE9 => null,
729 X_ATTRIBUTE10 => null,
730 X_ATTRIBUTE11 => null,
731 X_ATTRIBUTE12 => null,
732 X_ATTRIBUTE13 => null,
733 X_ATTRIBUTE14 => null,
734 X_ATTRIBUTE15 => null,
735 X_CONTEXT => l_context,
736 X_PARENT_NOTE_ID => l_parent_note_id,
737 X_SOURCE_OBJECT_ID => l_source_object_id,
738 X_NOTES => l_notes,
739 X_NOTES_DETAIL => l_notes_detail,
740 X_CREATION_DATE => l_creation_date,
741 X_CREATED_BY => l_created_by,
742 X_LAST_UPDATE_DATE =>sysdate,
743 X_LAST_UPDATED_BY => l_last_updated_by,
744 X_LAST_UPDATE_LOGIN =>l_last_update_login);
745
746 end;
747 end LOAD_ROW;
748
749 procedure TRANSLATE_ROW (
750 X_JTF_NOTE_ID in NUMBER,
751 X_NOTES in VARCHAR2,
752 X_NOTES_DETAIL in VARCHAR2,
753 X_OBJECT_VERSION_NUMBER in NUMBER,
754 X_OWNER in VARCHAR2
755 ) IS
756
757 begin
758 UPDATE jtf_notes_tl SET
759 notes=X_NOTES,
760 notes_detail = X_NOTES_DETAIL,
761 last_update_date = sysdate,
762 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
763 last_update_login = 0,
764 source_lang = userenv('LANG')
765 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
766 jtf_note_id = X_JTF_NOTE_ID;
767 end TRANSLATE_ROW;
768
769
770
771 end JTF_NOTES_PKG;