eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BSC_TAB_VIEW_LABELS_PKG

Source


1 package body BSC_TAB_VIEW_LABELS_PKG as
2 /* $Header: BSCTBVLB.pls 120.0 2005/06/01 16:45:44 appldev noship $ */
3 
4 PROCEDURE TRANSLATE_ROW
5 (
6     X_TAB_ID            IN      NUMBER,
7     X_TAB_VIEW_ID       IN      NUMBER,
8     X_LABEL_ID          IN      NUMBER,
9     X_NAME              IN      VARCHAR2,
10     X_NOTE              IN      VARCHAR2,
11     X_LAST_UPDATE_DATE  IN      DATE,
12     X_LAST_UPDATED_BY   IN      NUMBER,
13     X_LAST_UPDATE_LOGIN IN      NUMBER
14 ) IS
15     L_TAB           NUMBER := -1;
16 BEGIN
17     SELECT COUNT(*) INTO L_TAB   FROM   BSC_TAB_VIEW_LABELS_TL WHERE  TAB_ID = X_TAB_ID
18                                  AND    TAB_VIEW_ID = X_TAB_VIEW_ID
19                                  AND    LABEL_ID    = X_LABEL_ID;
20     IF (L_TAB > 0) THEN
21         UPDATE  BSC_TAB_VIEW_LABELS_TL SET
22                 NAME              =   NVL(X_NAME,            NAME),
23                 NOTE              =   NVL(X_NOTE,            NOTE),
24                 LAST_UPDATE_DATE  =   NVL(X_LAST_UPDATE_DATE, SYSDATE),
25                 LAST_UPDATED_BY   =   X_LAST_UPDATED_BY,
26                 LAST_UPDATE_LOGIN =   X_LAST_UPDATE_LOGIN,
27                 SOURCE_LANG       =   USERENV('LANG')
28         WHERE   USERENV('LANG')   IN  (LANGUAGE, SOURCE_LANG)
29         AND     TAB_ID            =   X_TAB_ID
30         AND     TAB_VIEW_ID       =   X_TAB_VIEW_ID
31         AND     LABEL_ID          =   X_LABEL_ID
32         AND     LAST_UPDATE_DATE  <=  X_LAST_UPDATE_DATE;
33     ELSE
34         RAISE NO_DATA_FOUND;
35     END IF;
36 
37 END TRANSLATE_ROW;
38 
39 
40 procedure INSERT_ROW (
41   X_ROWID 		in out NOCOPY VARCHAR2,
42   X_TAB_ID 		in NUMBER,
43   X_TAB_VIEW_ID 	in NUMBER,
44   X_LABEL_ID 		in NUMBER,
45   X_LABEL_TYPE 		in NUMBER,
46   X_LINK_ID 		in NUMBER,
47   X_NAME 		in VARCHAR2,
48   X_NOTE 		in VARCHAR2,
49   X_TEXT_FLAG 		in NUMBER,
50   X_LEFT_POSITION 	in NUMBER,
51   X_TOP_POSITION 	in NUMBER,
52   X_WIDTH 		in NUMBER,
53   X_HEIGHT 		in NUMBER,
54   X_FONT_SIZE 		in NUMBER,
55   X_FONT_STYLE 		in NUMBER,
56   X_FONT_COLOR 		in NUMBER,
57   X_URL 		in VARCHAR2,
58   X_FUNCTION_ID in NUMBER,
59   X_CREATION_DATE 	in DATE,
60   X_CREATED_BY 		in NUMBER,
61   X_LAST_UPDATE_DATE 	in DATE,
62   X_LAST_UPDATED_BY 	in NUMBER,
63   X_LAST_UPDATE_LOGIN 	in NUMBER
64 ) is
65   cursor C is select ROWID from BSC_TAB_VIEW_LABELS_B
66     where TAB_ID = X_TAB_ID
67     and TAB_VIEW_ID = X_TAB_VIEW_ID
68     and LABEL_ID = X_LABEL_ID
69     ;
70 
71 begin
72 
73   insert into BSC_TAB_VIEW_LABELS_B (
74 	  TAB_ID,
75 	  TAB_VIEW_ID,
76 	  LABEL_ID,
77 	  LABEL_TYPE,
78 	  LINK_ID,
79       FUNCTION_ID,
80 	  CREATION_DATE ,
81 	  CREATED_BY,
82 	  LAST_UPDATE_DATE,
83 	  LAST_UPDATED_BY ,
84 	  LAST_UPDATE_LOGIN
85   ) values (
86 	  X_TAB_ID,
87 	  X_TAB_VIEW_ID,
88   	  X_LABEL_ID,
89 	  X_LABEL_TYPE,
90 	  X_LINK_ID,
91       X_FUNCTION_ID,
92 	  NVL(X_CREATION_DATE , SYSDATE),
93 	  X_CREATED_BY,
94 	  NVL(X_LAST_UPDATE_DATE, SYSDATE),
95 	  X_LAST_UPDATED_BY ,
96 	  X_LAST_UPDATE_LOGIN
97   );
98 
99   insert into BSC_TAB_VIEW_LABELS_TL (
100 	TAB_ID,
101 	TAB_VIEW_ID,
102 	LABEL_ID,
103 	NAME,
104 	NOTE,
105 	TEXT_FLAG,
106 	LEFT_POSITION,
107 	TOP_POSITION,
108 	WIDTH,
109 	HEIGHT,
110 	FONT_SIZE,
111 	FONT_STYLE,
112 	FONT_COLOR,
113 	URL,
114 	CREATION_DATE ,
115 	CREATED_BY,
116 	LAST_UPDATE_DATE,
117 	LAST_UPDATED_BY ,
118 	LAST_UPDATE_LOGIN,
119     	LANGUAGE,
120 	SOURCE_LANG
121   ) select
122 	X_TAB_ID,
123 	X_TAB_VIEW_ID,
124 	X_LABEL_ID,
125 	X_NAME,
126 	X_NOTE,
127 	X_TEXT_FLAG,
128 	X_LEFT_POSITION,
129 	X_TOP_POSITION,
130 	X_WIDTH,
131 	X_HEIGHT,
132 	X_FONT_SIZE,
133 	X_FONT_STYLE,
134 	X_FONT_COLOR,
135 	X_URL,
136 	NVL(X_CREATION_DATE, SYSDATE),
137 	X_CREATED_BY,
138 	NVL(X_LAST_UPDATE_DATE, SYSDATE),
139 	X_LAST_UPDATED_BY ,
140 	X_LAST_UPDATE_LOGIN,
141     L.LANGUAGE_CODE,
142     userenv('LANG')
143   from FND_LANGUAGES L
144   where L.INSTALLED_FLAG in ('I', 'B')
145   and not exists
146     (select NULL
147     from BSC_TAB_VIEW_LABELS_TL T
148     where T.TAB_ID = X_TAB_ID
149     and T.TAB_VIEW_ID = X_TAB_VIEW_ID
150     and T.LABEL_ID = X_LABEL_ID
151     and T.LANGUAGE = L.LANGUAGE_CODE);
152 
153   open c;
154   fetch c into X_ROWID;
155   if (c%notfound) then
156     close c;
157     raise no_data_found;
158   end if;
159   close c;
160 
161 end INSERT_ROW;
162 
163 procedure LOCK_ROW (
164   X_TAB_ID in NUMBER,
165   X_TAB_VIEW_ID in NUMBER,
166   X_LABEL_ID in NUMBER,
167   X_LABEL_TYPE in NUMBER,
168   X_LINK_ID in NUMBER,
169   X_NAME in VARCHAR2,
170   X_NOTE in VARCHAR2,
171   X_TEXT_FLAG in NUMBER,
172   X_LEFT_POSITION in NUMBER,
173   X_TOP_POSITION in NUMBER,
174   X_WIDTH in NUMBER,
175   X_HEIGHT in NUMBER,
176   X_FONT_SIZE in NUMBER,
177   X_FONT_STYLE in NUMBER,
178   X_FONT_COLOR in NUMBER,
179   X_URL in VARCHAR2
180 ) is
181   cursor c is select
182 	LABEL_TYPE,
183 	LINK_ID
184     from BSC_TAB_VIEW_LABELS_B
185     where TAB_ID = X_TAB_ID
186     and TAB_VIEW_ID = X_TAB_VIEW_ID
187     and LABEL_ID = X_LABEL_ID
188     for update of TAB_ID nowait;
189   recinfo c%rowtype;
190 
191   cursor c1 is select
192   	NAME,
193 	NOTE,
194 	TEXT_FLAG,
195 	LEFT_POSITION,
196 	TOP_POSITION,
197 	WIDTH,
198 	HEIGHT,
199 	FONT_SIZE,
200 	FONT_STYLE,
201 	FONT_COLOR,
202 	URL,
203       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
204     from BSC_TAB_VIEW_LABELS_TL
205     where TAB_ID = X_TAB_ID
206     and TAB_VIEW_ID = X_TAB_VIEW_ID
207     and LABEL_ID = X_LABEL_ID
208     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
209     for update of TAB_ID nowait;
210 begin
211   open c;
212   fetch c into recinfo;
213   if (c%notfound) then
214     close c;
215     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
216     app_exception.raise_exception;
217   end if;
218   close c;
219   if (    (recinfo.LABEL_TYPE = X_LABEL_TYPE)
220   ) then
221     null;
222   else
223     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
224     app_exception.raise_exception;
225   end if;
226 
227   for tlinfo in c1 loop
228     if (tlinfo.BASELANG = 'Y') then
229       if (    (tlinfo.NAME = X_NAME)
230           AND (tlinfo.NOTE = X_NOTE)
231           AND (tlinfo.TEXT_FLAG = X_TEXT_FLAG)
232           AND (tlinfo.LEFT_POSITION =  X_LEFT_POSITION)
233           AND (tlinfo.TOP_POSITION = X_TOP_POSITION)
234           AND (tlinfo.WIDTH = X_WIDTH)
235           AND (tlinfo.HEIGHT = X_HEIGHT)
236           AND (tlinfo.FONT_SIZE = X_FONT_SIZE)
237           AND (tlinfo.FONT_STYLE = X_FONT_STYLE)
238           AND (tlinfo.FONT_COLOR = X_FONT_COLOR)
239           AND (tlinfo.URL= X_URL)
240       ) then
241         null;
242       else
243         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
244         app_exception.raise_exception;
245       end if;
246     end if;
247   end loop;
248   return;
249 end LOCK_ROW;
250 
251 procedure UPDATE_ROW (
252   X_TAB_ID 		in NUMBER,
253   X_TAB_VIEW_ID 	in NUMBER,
254   X_LABEL_ID 		in NUMBER,
255   X_LABEL_TYPE 		in NUMBER,
256   X_LINK_ID 		in NUMBER,
257   X_NAME 		in VARCHAR2,
258   X_NOTE 		in VARCHAR2,
259   X_TEXT_FLAG 		in NUMBER,
260   X_LEFT_POSITION 	in NUMBER,
261   X_TOP_POSITION 	in NUMBER,
262   X_WIDTH 		in NUMBER,
263   X_HEIGHT 		in NUMBER,
264   X_FONT_SIZE 		in NUMBER,
265   X_FONT_STYLE 		in NUMBER,
266   X_FONT_COLOR 		in NUMBER,
267   X_URL 		in VARCHAR2,
268   X_FUNCTION_ID     in NUMBER,
269   X_CREATION_DATE 	in DATE,
270   X_CREATED_BY 		in NUMBER,
271   X_LAST_UPDATE_DATE 	in DATE,
272   X_LAST_UPDATED_BY 	in NUMBER,
273   X_LAST_UPDATE_LOGIN 	in NUMBER
274 ) is
275 
276 begin
277 
278   update BSC_TAB_VIEW_LABELS_B set
279 	LABEL_TYPE = NVL(X_LABEL_TYPE, LABEL_TYPE),
280 	LINK_ID = X_LINK_ID,
281     FUNCTION_ID = X_FUNCTION_ID,
282 	LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE, LAST_UPDATE_DATE),
283 	LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY, LAST_UPDATED_BY),
284 	LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN, LAST_UPDATE_LOGIN)
285   where TAB_ID = X_TAB_ID
286   and LABEL_ID = X_LABEL_ID
287   and TAB_VIEW_ID = X_TAB_VIEW_ID;
288 
289   if (sql%notfound) then
290     raise no_data_found;
291   end if;
292 
293   update BSC_TAB_VIEW_LABELS_TL set
294   	NAME = X_NAME,
295 	NOTE = X_NOTE,
296 	TEXT_FLAG = X_TEXT_FLAG,
297 	LEFT_POSITION = X_LEFT_POSITION,
298 	TOP_POSITION = X_TOP_POSITION,
299 	WIDTH = X_WIDTH,
300 	HEIGHT = X_HEIGHT,
301 	FONT_SIZE = X_FONT_SIZE,
302 	FONT_STYLE = X_FONT_STYLE,
303 	FONT_COLOR = X_FONT_COLOR,
304 	URL = X_URL,
305         LAST_UPDATE_DATE    =   NVL(X_LAST_UPDATE_DATE, SYSDATE),
306         LAST_UPDATED_BY     =   X_LAST_UPDATED_BY,
307         LAST_UPDATE_LOGIN   =   X_LAST_UPDATE_LOGIN,
308     SOURCE_LANG = userenv('LANG')
309   where TAB_ID = X_TAB_ID
310   and TAB_VIEW_ID = X_TAB_VIEW_ID
311   and LABEL_ID = X_LABEL_ID
312   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
313 
314   if (sql%notfound) then
315     raise no_data_found;
316   end if;
317 end UPDATE_ROW;
318 
319 procedure DELETE_ROW (
320   X_TAB_ID in NUMBER,
321   X_TAB_VIEW_ID in NUMBER,
322   X_LABEL_ID in NUMBER
323 ) is
324 begin
325   delete from BSC_TAB_VIEW_LABELS_TL
326   where TAB_ID = X_TAB_ID
327   and LABEL_ID = X_LABEL_ID
328   and TAB_VIEW_ID = X_TAB_VIEW_ID;
329 
330   if (sql%notfound) then
331     raise no_data_found;
332   end if;
333 
334   delete from BSC_TAB_VIEW_LABELS_B
335   where TAB_ID = X_TAB_ID
336   and LABEL_ID = X_LABEL_ID
337   and TAB_VIEW_ID = X_TAB_VIEW_ID;
338 
339   if (sql%notfound) then
340     raise no_data_found;
341   end if;
342 end DELETE_ROW;
343 
344 procedure ADD_LANGUAGE is
345 l_user NUMBER;
346 begin
347   SELECT VS.USER#
348   INTO  l_user
349   FROM V$SESSION VS WHERE VS.AUDSID =USERENV('SESSIONID');
350 
351   delete from BSC_TAB_VIEW_LABELS_TL T
352   where not exists
353     (select NULL
354     from BSC_TAB_VIEW_LABELS_B B
355     where B.TAB_ID = T.TAB_ID
356     and B.LABEL_ID = T.LABEL_ID
357     and B.TAB_VIEW_ID = T.TAB_VIEW_ID
358     );
359 
360   update BSC_TAB_VIEW_LABELS_TL T set (
361   	NAME,
362 	NOTE,
363 	TEXT_FLAG,
364 	LEFT_POSITION,
365 	TOP_POSITION,
366 	WIDTH,
367 	HEIGHT,
368 	FONT_SIZE,
369 	FONT_STYLE,
370 	FONT_COLOR,
371 	URL
372     ) = (select
373   	B.NAME,
374 	B.NOTE,
375 	B.TEXT_FLAG,
376 	B.LEFT_POSITION,
377 	B.TOP_POSITION,
378 	B.WIDTH,
379 	B.HEIGHT,
380 	B.FONT_SIZE,
381 	B.FONT_STYLE,
382 	B.FONT_COLOR,
383 	B.URL
384     from BSC_TAB_VIEW_LABELS_TL B
385     where B.TAB_ID = T.TAB_ID
386     and B.LABEL_ID = T.LABEL_ID
387     and B.TAB_VIEW_ID = T.TAB_VIEW_ID
388     and B.LANGUAGE = T.SOURCE_LANG)
389   where (
390       T.TAB_ID,
391       T.TAB_VIEW_ID,
392       T.LABEL_ID,
393       T.LANGUAGE
394   ) in (select
395       SUBT.TAB_ID,
396       SUBT.TAB_VIEW_ID,
397       SUBT.LABEL_ID,
398       SUBT.LANGUAGE
399     from BSC_TAB_VIEW_LABELS_TL SUBB, BSC_TAB_VIEW_LABELS_TL SUBT
400     where SUBB.TAB_ID = SUBT.TAB_ID
401     and SUBB.TAB_VIEW_ID = SUBT.TAB_VIEW_ID
402     and SUBB.LABEL_ID = SUBT.LABEL_ID
403     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
404     and (SUBB.NAME <> SUBT.NAME
405       or SUBB.NOTE <> SUBT.NOTE
406       or SUBB.TEXT_FLAG <> SUBT.TEXT_FLAG
407       or SUBB.LEFT_POSITION <> SUBT.LEFT_POSITION
408       or SUBB.TOP_POSITION <> SUBT.TOP_POSITION
409       or SUBB.WIDTH <> SUBT.WIDTH
410       or SUBB.HEIGHT <> SUBT.HEIGHT
411       or SUBB.FONT_SIZE <> SUBT.FONT_SIZE
412       or SUBB.FONT_STYLE <> SUBT.FONT_STYLE
413       or SUBB.FONT_COLOR <> SUBT.FONT_COLOR
414       or SUBB.URL <> SUBT.URL
415   ));
416 
417   insert into BSC_TAB_VIEW_LABELS_TL (
418 	TAB_ID,
419 	TAB_VIEW_ID,
420 	LABEL_ID,
421 	NAME,
422 	NOTE,
423 	TEXT_FLAG,
424 	LEFT_POSITION,
425 	TOP_POSITION,
426 	WIDTH,
427 	HEIGHT,
428 	FONT_SIZE,
429 	FONT_STYLE,
430 	FONT_COLOR,
431 	URL,
432 	CREATION_DATE ,
433 	CREATED_BY,
434 	LAST_UPDATE_DATE,
435 	LAST_UPDATED_BY ,
436 	LAST_UPDATE_LOGIN,
437 	LANGUAGE,
438 	SOURCE_LANG
439   ) select
440     B.TAB_ID,
441     B.TAB_VIEW_ID,
442     B.LABEL_ID,
443     B.NAME,
444     B.NOTE,
445     B.TEXT_FLAG,
446     B.LEFT_POSITION,
447     B.TOP_POSITION,
448     B.WIDTH,
449     B.HEIGHT,
450     B.FONT_SIZE,
451     B.FONT_STYLE,
452     B.FONT_COLOR,
453     B.URL,
454     SYSDATE,
455     l_user,
456     SYSDATE,
457     l_user,
458     l_user,
459     L.LANGUAGE_CODE,
460     B.SOURCE_LANG
461   from BSC_TAB_VIEW_LABELS_TL B, FND_LANGUAGES L
462   where L.INSTALLED_FLAG in ('I', 'B')
463   and B.LANGUAGE = userenv('LANG')
464   and not exists
465     (select NULL
466     from BSC_TAB_VIEW_LABELS_TL T
467     where T.TAB_ID = B.TAB_ID
468     and T.TAB_VIEW_ID = B.TAB_VIEW_ID
469     and T.LABEL_ID = B.LABEL_ID
470     and T.LANGUAGE = L.LANGUAGE_CODE);
471 end ADD_LANGUAGE;
472 
473 PROCEDURE UPDATE_LINK
474 (
475         X_SHORT_NAME        IN  VARCHAR2,
476         X_TAB_VIEW_ID       IN  NUMBER,
477         X_LABEL_ID          IN  NUMBER,
478         X_MENU_ID           IN  NUMBER,
479         X_LAST_UPDATE_DATE  IN  DATE
480 ) IS
481     L_SHORT         NUMBER := -1;
482 BEGIN
483     SELECT COUNT(*) INTO L_SHORT FROM BSC_TAB_VIEW_LABELS_B
484                                  WHERE  TAB_ID = (SELECT TAB_ID FROM BSC_TABS_B WHERE SHORT_NAME = X_SHORT_NAME)
485                                  AND TAB_VIEW_ID = X_TAB_VIEW_ID
486                                  AND LABEL_ID    = X_LABEL_ID;
487     IF (L_SHORT > 0) THEN
488         UPDATE BSC_TAB_VIEW_LABELS_B
489         SET    LINK_ID          =   X_MENU_ID
490         WHERE  TAB_ID           =   (SELECT TAB_ID FROM BSC_TABS_B WHERE SHORT_NAME = X_SHORT_NAME)
491         AND    TAB_VIEW_ID      =   X_TAB_VIEW_ID
492         AND    LABEL_ID         =   X_LABEL_ID
493         AND    LABEL_TYPE       =   2;
494       --  AND    LAST_UPDATE_DATE <=   X_LAST_UPDATE_DATE;
495     ELSE
496         FND_MESSAGE.SET_NAME('BSC',     'NO DATA FOUND');
497         FND_MESSAGE.SET_TOKEN('PACKAGE',  'BSC_TAB_VIEW_LABELS_PKG');
498         FND_MESSAGE.SET_TOKEN('TABLE',  'BSC_TAB_VIEW_LABELS_B');
499         FND_MESSAGE.SET_TOKEN('COLUMN', 'TAB_ID');
500         FND_MESSAGE.SET_TOKEN('VALUE',   X_SHORT_NAME);
501         FND_MESSAGE.SET_TOKEN('COLUMN', 'X_TAB_VIEW_ID');
502         FND_MESSAGE.SET_TOKEN('VALUE',   X_TAB_VIEW_ID);
503         FND_MESSAGE.SET_TOKEN('COLUMN', 'X_LABEL_ID');
504         FND_MESSAGE.SET_TOKEN('VALUE',   X_LABEL_ID);
505         APP_EXCEPTION.RAISE_EXCEPTION;
506     END IF;
507 END UPDATE_LINK;
508 
509 
510 end BSC_TAB_VIEW_LABELS_PKG;