[Home] [Help]
PACKAGE BODY: APPS.WSH_SAVED_QUERIES_PKG
Source
1 package body wsh_saved_queries_pkg as
2 /* $Header: WSHQACTB.pls 115.7 2004/04/06 00:53:42 anxsharm ship $ */
3
4
5 -- ===========================================================================
6 --
7 -- Name:
8 --
9 -- insert_row
10 --
11 -- Description:
12 --
13 -- Called by the client to insert a row into the
14 -- WSH_SAVED_QUERIES table.
15 --
16 -- ===========================================================================
17
18 --
19 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SAVED_QUERIES_PKG';
20 --
21 procedure insert_row(
22 X_rowid out NOCOPY varchar2,
23 X_query_id out NOCOPY number,
24 X_name varchar2,
25 X_description varchar2,
26 X_entity_type varchar2,
27 X_shared_flag varchar2,
28 X_pseudo_query long,
29 X_attribute_category varchar2,
30 X_attribute1 varchar2,
31 X_attribute2 varchar2,
32 X_attribute3 varchar2,
33 X_attribute4 varchar2,
34 X_attribute5 varchar2,
35 X_attribute6 varchar2,
36 X_attribute7 varchar2,
37 X_attribute8 varchar2,
38 X_attribute9 varchar2,
39 X_attribute10 varchar2,
40 X_attribute11 varchar2,
41 X_attribute12 varchar2,
42 X_attribute13 varchar2,
43 X_attribute14 varchar2,
44 X_attribute15 varchar2,
45 X_creation_date date,
46 X_created_by number,
47 X_last_update_date date,
48 X_last_updated_by number,
49 X_last_update_login number,
50 X_program_application_id number,
51 X_program_id number,
52 X_program_update_date date,
53 X_request_id number
54 )
55 is
56
57 X_dummy varchar2(18);
58
59 cursor id_sequence is
60 select wsh_saved_queries_s.nextval
61 from sys.dual;
62
63 cursor row_id is
64 select rowid
65 from wsh_saved_queries_b
66 where query_id = X_query_id;
67
68 --
69 l_debug_on BOOLEAN;
70 --
71 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
72 --
73 begin
74
75 --
76 --
77 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
78 --
79 IF l_debug_on IS NULL
80 THEN
81 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
82 END IF;
83 --
84 IF l_debug_on THEN
85 WSH_DEBUG_SV.push(l_module_name);
86 --
87 WSH_DEBUG_SV.log(l_module_name,'X_NAME',X_NAME);
88 WSH_DEBUG_SV.log(l_module_name,'X_DESCRIPTION',X_DESCRIPTION);
89 WSH_DEBUG_SV.log(l_module_name,'X_ENTITY_TYPE',X_ENTITY_TYPE);
90 WSH_DEBUG_SV.log(l_module_name,'X_SHARED_FLAG',X_SHARED_FLAG);
91 WSH_DEBUG_SV.log(l_module_name,'X_REQUEST_ID',X_REQUEST_ID);
92 END IF;
93 --
94 open id_sequence;
95 fetch id_sequence into X_query_id;
96 close id_sequence;
97
98 insert into wsh_saved_queries_b(
99
100 query_id,
101 entity_type,
102 shared_flag,
103 pseudo_query,
104 attribute_category,
105 attribute1,
106 attribute2,
107 attribute3,
108 attribute4,
109 attribute5,
110 attribute6,
111 attribute7,
112 attribute8,
113 attribute9,
114 attribute10,
115 attribute11,
116 attribute12,
117 attribute13,
118 attribute14,
119 attribute15,
120 creation_date,
121 created_by,
122 last_update_date,
123 last_updated_by,
124 last_update_login,
125 program_application_id,
126 program_id,
127 program_update_date,
128 request_id
129
130 ) values(
131
132 X_query_id,
133 X_entity_type,
134 X_shared_flag,
135 X_pseudo_query,
136 X_attribute_category,
137 X_attribute1,
138 X_attribute2,
139 X_attribute3,
140 X_attribute4,
141 X_attribute5,
142 X_attribute6,
143 X_attribute7,
144 X_attribute8,
145 X_attribute9,
146 X_attribute10,
147 X_attribute11,
148 X_attribute12,
149 X_attribute13,
150 X_attribute14,
151 X_attribute15,
152 X_creation_date,
153 X_created_by,
154 X_last_update_date,
155 X_last_updated_by,
156 X_last_update_login,
157 X_program_application_id,
158 X_program_id,
159 X_program_update_date,
160 X_request_id
161 );
162
163 insert into wsh_saved_queries_tl (
164
165 query_id,
166 name,
167 description,
168 creation_date,
169 created_by,
170 last_update_date,
171 last_updated_by,
172 last_update_login,
173 language,
174 source_lang
175
176 ) select
177
178 x_query_id,
179 x_name,
180 x_description,
181 x_creation_date,
182 x_created_by,
183 x_last_update_date,
184 x_last_updated_by,
185 x_last_update_login,
186 l.language_code,
187 userenv('LANG')
188
189 from
190 fnd_languages l
191 where
192 l.installed_flag in ('I', 'B')
193 and not exists
194 (select null
195 from wsh_saved_queries_tl t
196 where t.query_id = x_query_id
197 and t.language = l.language_code);
198
199 open row_id;
200
201 fetch row_id into X_rowid;
202
203 if (row_id%NOTFOUND) then
204 close row_id;
205 IF l_debug_on THEN
206 WSH_DEBUG_SV.pop(l_module_name,'no_data_found');
207 END IF;
208 raise NO_DATA_FOUND;
209 end if;
210
211 close row_id;
212 --
213 IF l_debug_on THEN
214 WSH_DEBUG_SV.pop(l_module_name);
215 END IF;
216 --
217 exception
218 when DUP_VAL_ON_INDEX then
219 fnd_message.set_name('WSH', 'WSH_DUPLICATE_RECORD');
220 --
221 IF l_debug_on THEN
222 WSH_DEBUG_SV.logmsg(l_module_name,'DUP_VAL_ON_INDEX exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
223 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DUP_VAL_ON_INDEX');
224 END IF;
225 --
226 app_exception.raise_exception;
227 when app_exception.record_lock_exception then
228 fnd_message.set_name('OE', 'WSH_NO_LOCK');
229 --
230 IF l_debug_on THEN
231 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.RECORD_LOCK_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
232 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.RECORD_LOCK_EXCEPTION');
233 END IF;
234 --
235 app_exception.raise_exception;
236 end insert_row;
237
238
239 -- ===========================================================================
240 --
241 -- Name:
242 --
243 -- lock_row
244 --
245 -- Description:
246 --
247 -- Called by the client to lock a row into the
248 -- WSH_SAVED_QUERIES table.
249 --
250 -- ===========================================================================
251
252 procedure lock_row(
253 X_query_id number,
254 X_name varchar2,
255 X_description varchar2,
256 X_entity_type varchar2,
257 X_shared_flag varchar2,
258 X_pseudo_query long,
259 X_attribute_category varchar2,
260 X_attribute1 varchar2,
261 X_attribute2 varchar2,
262 X_attribute3 varchar2,
263 X_attribute4 varchar2,
264 X_attribute5 varchar2,
265 X_attribute6 varchar2,
266 X_attribute7 varchar2,
267 X_attribute8 varchar2,
268 X_attribute9 varchar2,
269 X_attribute10 varchar2,
270 X_attribute11 varchar2,
271 X_attribute12 varchar2,
272 X_attribute13 varchar2,
273 X_attribute14 varchar2,
274 X_attribute15 varchar2,
275 X_creation_date date,
276 X_created_by number,
277 X_last_update_date date,
278 X_last_updated_by number,
279 X_last_update_login number,
280 X_program_application_id number,
281 X_program_id number,
282 X_program_update_date date,
283 X_request_id number
284 )
285 is
286
287 cursor lock_record is
288 select *
289 from wsh_saved_queries_b
290 where query_id = X_query_id
291 for update nowait;
292
293 rec_info lock_record%ROWTYPE;
294
295 cursor c1 is
296 select
297 name,
298 description,
299 decode(language, userenv('LANG'), 'Y', 'N') baselang
300 from wsh_saved_queries_tl
301 where query_id = x_query_id
302 and userenv('LANG') in (language, source_lang)
303 for update of query_id nowait;
304
305 --
306 l_debug_on BOOLEAN;
307 --
308 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
309 --
310 begin
311
312 --
313 --
314 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
315 --
316 IF l_debug_on IS NULL
317 THEN
318 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
319 END IF;
320 --
321 IF l_debug_on THEN
322 WSH_DEBUG_SV.push(l_module_name);
323 --
324 WSH_DEBUG_SV.log(l_module_name,'X_QUERY_ID',X_QUERY_ID);
325 WSH_DEBUG_SV.log(l_module_name,'X_NAME',X_NAME);
326 WSH_DEBUG_SV.log(l_module_name,'X_DESCRIPTION',X_DESCRIPTION);
327 WSH_DEBUG_SV.log(l_module_name,'X_ENTITY_TYPE',X_ENTITY_TYPE);
328 WSH_DEBUG_SV.log(l_module_name,'X_REQUEST_ID',X_REQUEST_ID);
329 END IF;
330 --
331 open lock_record;
332
333 fetch lock_record into rec_info;
334
335 if (lock_record%NOTFOUND) then
336 close lock_record;
337
338 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
339 IF l_debug_on THEN
340 WSH_DEBUG_SV.pop(l_module_name,'FORM_RECORD_DELETED');
341 END IF;
342 app_exception.raise_exception;
343
344 end if;
345
346 close lock_record;
347
348 if (
349 (rec_info.query_id = X_query_id)
350 and
351 (rec_info.entity_type = X_entity_type)
352 and
353 (rec_info.shared_flag = X_shared_flag)
354 and
355 ((rec_info.pseudo_query = X_pseudo_query)
356 or
357 ((rec_info.pseudo_query is null)
358 and (X_pseudo_query is null)))
359 and
360 ((rec_info.attribute_category = X_attribute_category)
361 or
362 ((rec_info.attribute_category is null)
363 and (X_attribute_category is null)))
364 and
365 ((rec_info.attribute1 = X_attribute1)
366 or
367 ((rec_info.attribute1 is null)
368 and (X_attribute1 is null)))
369 and
370 ((rec_info.attribute2 = X_attribute2)
371 or
372 ((rec_info.attribute2 is null)
373 and (X_attribute2 is null)))
374 and
375 ((rec_info.attribute3 = X_attribute3)
376 or
377 ((rec_info.attribute3 is null)
378 and (X_attribute3 is null)))
379 and
380 ((rec_info.attribute4 = X_attribute4)
381 or
382 ((rec_info.attribute4 is null)
383 and (X_attribute4 is null)))
384 and
385 ((rec_info.attribute5 = X_attribute5)
386 or
387 ((rec_info.attribute5 is null)
388 and (X_attribute5 is null)))
389 and
390 ((rec_info.attribute6 = X_attribute6)
391 or
392 ((rec_info.attribute6 is null)
393 and (X_attribute6 is null)))
394 and
395 ((rec_info.attribute7 = X_attribute7)
396 or
397 ((rec_info.attribute7 is null)
398 and (X_attribute7 is null)))
399 and
400 ((rec_info.attribute8 = X_attribute8)
401 or
402 ((rec_info.attribute8 is null)
403 and (X_attribute8 is null)))
404 and
405 ((rec_info.attribute9 = X_attribute9)
406 or
407 ((rec_info.attribute9 is null)
408 and (X_attribute9 is null)))
409 and
410 ((rec_info.attribute10 = X_attribute10)
411 or
412 ((rec_info.attribute10 is null)
413 and (X_attribute10 is null)))
414 and
415 ((rec_info.attribute11 = X_attribute11)
416 or
417 ((rec_info.attribute11 is null)
418 and (X_attribute11 is null)))
419 and
420 ((rec_info.attribute12 = X_attribute12)
421 or
422 ((rec_info.attribute12 is null)
423 and (X_attribute12 is null)))
424 and
425 ((rec_info.attribute13 = X_attribute13)
426 or
427 ((rec_info.attribute13 is null)
428 and (X_attribute13 is null)))
429 and
430 ((rec_info.attribute14 = X_attribute14)
431 or
432 ((rec_info.attribute14 is null)
433 and (X_attribute14 is null)))
434 and
435 ((rec_info.attribute15 = X_attribute15)
436 or
437 ((rec_info.attribute15 is null)
438 and (X_attribute15 is null)))
439 and
440 (rec_info.creation_date = X_creation_date)
441 and
442 (rec_info.created_by = X_created_by)
443 and
444 (rec_info.last_update_date = X_last_update_date)
445 and
446 (rec_info.last_updated_by = X_last_updated_by)
447 and
448 ((rec_info.last_update_login = X_last_update_login)
449 or
450 ((rec_info.last_update_login is null)
451 and (X_last_update_login is null)))
452 and
453 ((rec_info.program_application_id = X_program_application_id)
454 or
455 ((rec_info.program_application_id is null)
456 and (X_program_application_id is null)))
457 and
458 ((rec_info.program_id = X_program_id)
459 or
460 ((rec_info.program_id is null)
461 and (X_program_id is null)))
462 and
463 ((rec_info.program_update_date = X_program_update_date)
464 or
465 ((rec_info.program_update_date is null)
466 and (X_program_update_date is null)))
467 and
468 ((rec_info.request_id = X_request_id)
469 or
470 ((rec_info.request_id is null)
471 and (X_request_id is null)))
472 ) then
473
474 --
475 IF l_debug_on THEN
476 WSH_DEBUG_SV.pop(l_module_name,'Nothing changed');
477 END IF;
478 --
479 return;
480
481 else
482
483 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
484 IF l_debug_on THEN
485 WSH_DEBUG_SV.pop(l_module_name,'FORM_RECORD_CHANGED');
486 END IF;
487 app_exception.raise_exception;
488
489 end if;
490
491 for tlinfo in c1 loop
492 IF l_debug_on THEN
493 WSH_DEBUG_SV.log(l_module_name,'tlinfo.BASELANG',tlinfo.BASELANG);
494 END IF;
495 if (tlinfo.BASELANG = 'Y') then
496 if ( ((tlinfo.NAME = X_NAME)
497 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
498 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
499 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
500 ) then
501 null;
502 else
503 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
504 IF l_debug_on THEN
505 WSH_DEBUG_SV.pop(l_module_name,'FORM_RECORD_CHANGED');
506 END IF;
507 app_exception.raise_exception;
508 end if;
509 end if;
510 end loop;
511 --
512 IF l_debug_on THEN
513 WSH_DEBUG_SV.pop(l_module_name);
514 END IF;
515 --
516 end lock_row;
517
518
519 -- ===========================================================================
520 --
521 -- Name:
522 --
523 -- update_row
524 --
525 -- Description:
526 --
527 -- Called by the client to update a row into the
528 -- WSH_SAVED_QUERIES table.
529 --
530 -- ===========================================================================
531
532 procedure update_row(
533 X_query_id number,
534 X_name varchar2,
535 X_description varchar2,
536 X_entity_type varchar2,
537 X_shared_flag varchar2,
538 X_pseudo_query long,
539 X_attribute_category varchar2,
540 X_attribute1 varchar2,
541 X_attribute2 varchar2,
542 X_attribute3 varchar2,
543 X_attribute4 varchar2,
544 X_attribute5 varchar2,
545 X_attribute6 varchar2,
546 X_attribute7 varchar2,
547 X_attribute8 varchar2,
548 X_attribute9 varchar2,
549 X_attribute10 varchar2,
550 X_attribute11 varchar2,
551 X_attribute12 varchar2,
552 X_attribute13 varchar2,
553 X_attribute14 varchar2,
554 X_attribute15 varchar2,
555 X_last_update_date date,
556 X_last_updated_by number,
557 X_last_update_login number,
558 X_program_application_id number,
559 X_program_id number,
560 X_program_update_date date,
561 X_request_id number
562 )
563 is
564 X_dummy varchar2(18);
565 --
566 l_debug_on BOOLEAN;
567 --
568 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
569 --
570 begin
571 --
572 --
573 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
574 --
575 IF l_debug_on IS NULL
576 THEN
577 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
578 END IF;
579 --
580 IF l_debug_on THEN
581 WSH_DEBUG_SV.push(l_module_name);
582 --
583 WSH_DEBUG_SV.log(l_module_name,'X_QUERY_ID',X_QUERY_ID);
584 WSH_DEBUG_SV.log(l_module_name,'X_NAME',X_NAME);
585 WSH_DEBUG_SV.log(l_module_name,'X_DESCRIPTION',X_DESCRIPTION);
586 WSH_DEBUG_SV.log(l_module_name,'X_ENTITY_TYPE',X_ENTITY_TYPE);
587 WSH_DEBUG_SV.log(l_module_name,'X_SHARED_FLAG',X_SHARED_FLAG);
588 WSH_DEBUG_SV.log(l_module_name,'X_REQUEST_ID',X_REQUEST_ID);
589 END IF;
590 --
591 update wsh_saved_queries_b set
592
593 entity_type = X_entity_type,
594 shared_flag = X_shared_flag,
595 pseudo_query = X_pseudo_query,
596 attribute_category = X_attribute_category,
597 attribute1 = X_attribute1,
598 attribute2 = X_attribute2,
599 attribute3 = X_attribute3,
600 attribute4 = X_attribute4,
601 attribute5 = X_attribute5,
602 attribute6 = X_attribute6,
603 attribute7 = X_attribute7,
604 attribute8 = X_attribute8,
605 attribute9 = X_attribute9,
606 attribute10 = X_attribute10,
607 attribute11 = X_attribute11,
608 attribute12 = X_attribute12,
609 attribute13 = X_attribute13,
610 attribute14 = X_attribute14,
611 attribute15 = X_attribute15,
612 last_update_date = X_last_update_date,
613 last_updated_by = X_last_updated_by,
614 last_update_login = X_last_update_login,
618 request_id = X_request_id
615 program_application_id = X_program_application_id,
616 program_id = X_program_id,
617 program_update_date = X_program_update_date,
619
620 where query_id = X_query_id;
621
622 IF l_debug_on THEN
623 WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
624 END IF;
625 if (SQL%NOTFOUND) then
626 IF l_debug_on THEN
627 WSH_DEBUG_SV.pop(l_module_name,'no_data_found');
628 END IF;
629 raise NO_DATA_FOUND;
630 end if;
631
632 update WSH_SAVED_QUERIES_TL set
633 NAME = X_NAME,
634 DESCRIPTION = X_DESCRIPTION,
635 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
636 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
637 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
638 SOURCE_LANG = userenv('LANG')
639 where QUERY_ID = X_QUERY_ID
640 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
641
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
644 END IF;
645 if (sql%notfound) then
646 IF l_debug_on THEN
647 WSH_DEBUG_SV.pop(l_module_name,'no_data_found');
648 END IF;
649 raise no_data_found;
650 end if;
651 --
652 IF l_debug_on THEN
653 WSH_DEBUG_SV.pop(l_module_name);
654 END IF;
655 --
656 exception
657 when DUP_VAL_ON_INDEX then
658 fnd_message.set_name('WSH', 'WSH_DUPLICATE_RECORD');
659 --
660 IF l_debug_on THEN
661 WSH_DEBUG_SV.logmsg(l_module_name,'DUP_VAL_ON_INDEX exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
662 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DUP_VAL_ON_INDEX');
663 END IF;
664 --
665 app_exception.raise_exception;
666 when app_exception.record_lock_exception then
667 fnd_message.set_name('OE', 'WSH_NO_LOCK');
668 --
669 IF l_debug_on THEN
670 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.RECORD_LOCK_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
671 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.RECORD_LOCK_EXCEPTION');
672 END IF;
673 --
674 app_exception.raise_exception;
675 end update_row;
676
677
678 -- ===========================================================================
679 --
680 -- Name:
681 --
682 -- delete_row
683 --
684 -- Description:
685 --
686 -- Called by the client to delete a row into the
687 -- WSH_SAVED_QUERIES table.
688 --
689 -- ===========================================================================
690
691 procedure delete_row(X_query_id wsh_saved_queries_b.query_id%type)
692 is
693 --
694 l_debug_on BOOLEAN;
695 --
696 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
697 --
698 begin
699 --
700 --
701 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
702 --
703 IF l_debug_on IS NULL
704 THEN
705 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
706 END IF;
707 --
708 IF l_debug_on THEN
709 WSH_DEBUG_SV.push(l_module_name);
710 END IF;
711 --
712 delete from WSH_SAVED_QUERIES_TL
713 where QUERY_ID = X_QUERY_ID;
714
715 IF l_debug_on THEN
716 WSH_DEBUG_SV.log(l_module_name,'Rows Deleted',SQL%ROWCOUNT);
717 END IF;
718 if (sql%notfound) then
719 IF l_debug_on THEN
720 WSH_DEBUG_SV.pop(l_module_name,'NO_DATA_FOUND');
721 END IF;
722 raise no_data_found;
723 end if;
724
725 delete from wsh_saved_queries_b
726 where query_id = X_query_id;
727
728 IF l_debug_on THEN
729 WSH_DEBUG_SV.log(l_module_name,'Rows Deleted',SQL%ROWCOUNT);
730 END IF;
731 if (SQL%NOTFOUND) then
732 IF l_debug_on THEN
733 WSH_DEBUG_SV.pop(l_module_name,'NO_DATA_FOUND');
734 END IF;
735 raise NO_DATA_FOUND;
736 end if;
737 --
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.pop(l_module_name);
740 END IF;
741 --
742 end delete_row;
743
744
745 procedure ADD_LANGUAGE
746 is
747 --
748 l_debug_on BOOLEAN;
749 --
750 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_LANGUAGE';
751 --
752 begin
753 --
754 --
755 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
756 --
757 IF l_debug_on IS NULL
758 THEN
759 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
760 END IF;
761 --
762 IF l_debug_on THEN
763 WSH_DEBUG_SV.push(l_module_name);
764 END IF;
765 --
766 delete from WSH_SAVED_QUERIES_TL T
767 where not exists
768 (select NULL
769 from WSH_SAVED_QUERIES_B B
770 where B.QUERY_ID = T.QUERY_ID
771 );
772 IF l_debug_on THEN
773 WSH_DEBUG_SV.log(l_module_name,'Rows deleted',SQL%ROWCOUNT);
774 END IF;
775
776 update WSH_SAVED_QUERIES_TL T set (
777 NAME,
778 DESCRIPTION
779 ) = (select
780 B.NAME,
781 B.DESCRIPTION
782 from WSH_SAVED_QUERIES_TL B
783 where B.QUERY_ID = T.QUERY_ID
784 and B.LANGUAGE = T.SOURCE_LANG)
785 where (
786 T.QUERY_ID,
787 T.LANGUAGE
788 ) in (select
789 SUBT.QUERY_ID,
790 SUBT.LANGUAGE
791 from WSH_SAVED_QUERIES_TL SUBB, WSH_SAVED_QUERIES_TL SUBT
792 where SUBB.QUERY_ID = SUBT.QUERY_ID
793 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
794 and (SUBB.NAME <> SUBT.NAME
795 or (SUBB.NAME is null and SUBT.NAME is not null)
796 or (SUBB.NAME is not null and SUBT.NAME is null)
797 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
798 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
799 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
800 ));
801 IF l_debug_on THEN
802 WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
803 END IF;
804 insert into WSH_SAVED_QUERIES_TL (
805 QUERY_ID,
806 NAME,
807 DESCRIPTION,
808 CREATION_DATE,
809 CREATED_BY,
810 LAST_UPDATE_DATE,
811 LAST_UPDATED_BY,
812 LAST_UPDATE_LOGIN,
813 LANGUAGE,
814 SOURCE_LANG
815 ) select
816 B.QUERY_ID,
817 B.NAME,
818 B.DESCRIPTION,
819 B.CREATION_DATE,
820 B.CREATED_BY,
821 B.LAST_UPDATE_DATE,
822 B.LAST_UPDATED_BY,
823 B.LAST_UPDATE_LOGIN,
824 L.LANGUAGE_CODE,
825 B.SOURCE_LANG
826 from WSH_SAVED_QUERIES_TL B, FND_LANGUAGES L
827 where L.INSTALLED_FLAG in ('I', 'B')
828 and B.LANGUAGE = userenv('LANG')
829 and not exists
830 (select NULL
831 from WSH_SAVED_QUERIES_TL T
832 where T.QUERY_ID = B.QUERY_ID
833 and T.LANGUAGE = L.LANGUAGE_CODE);
834
835 IF l_debug_on THEN
836 WSH_DEBUG_SV.log(l_module_name,'Rows inserted',SQL%ROWCOUNT);
837 END IF;
838 --
839 IF l_debug_on THEN
840 WSH_DEBUG_SV.pop(l_module_name);
841 END IF;
842 --
843 end ADD_LANGUAGE;
844
845
846 end wsh_saved_queries_pkg;