1 package body BSC_TABS_PKG as
2 /* $Header: BSCTABB.pls 115.9 2003/02/12 14:29:43 adeulgao ship $ */
3
4 PROCEDURE TRANSLATE_ROW
5 (
6 X_SHORT_NAME IN VARCHAR2,
7 X_NAME IN VARCHAR2,
8 X_HELP IN VARCHAR2,
9 X_ADDITIONAL_INFO IN VARCHAR2
10 )IS
11 BEGIN
12 UPDATE BSC_TABS_TL SET
13 NAME = NVL(X_NAME, NAME),
14 HELP = NVL(X_HELP, HELP),
15 ADDITIONAL_INFO = NVL(X_ADDITIONAL_INFO, ADDITIONAL_INFO),
16 SOURCE_LANG = USERENV('LANG')
17 WHERE USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
18 AND TAB_ID = (SELECT TAB_ID FROM BSC_TABS_B WHERE SHORT_NAME = X_SHORT_NAME);
19
20 IF (SQL%NOTFOUND) THEN
21 RAISE NO_DATA_FOUND;
22 END IF;
23
24 END TRANSLATE_ROW;
25
26
27 PROCEDURE INSERT_ROW
28 (
29 X_ROWID in out NOCOPY VARCHAR2,
30 X_TAB_ID IN NUMBER,
31 X_KPI_MODEL IN NUMBER,
32 X_BSC_MODEL IN NUMBER,
33 X_CROSS_MODEL IN NUMBER,
34 X_DEFAULT_MODEL IN NUMBER,
35 X_ZOOM_FACTOR IN NUMBER,
36 X_CREATED_BY IN NUMBER,
37 X_LAST_UPDATED_BY IN NUMBER,
38 X_LAST_UPDATE_LOGIN IN NUMBER, /* DEFAULT 0 */
39 X_PARENT_TAB_ID IN NUMBER,
40 X_OWNER_ID IN NUMBER,
41 X_SHORT_NAME IN VARCHAR2,
42 X_NAME IN VARCHAR2,
43 X_HELP IN VARCHAR2,
44 X_ADDITIONAL_INFO IN VARCHAR2
45 ) IS
46 CURSOR C IS SELECT ROWID FROM BSC_TABS_B WHERE TAB_ID = X_TAB_ID;
47
48 BEGIN
49 INSERT INTO BSC_TABS_B
50 (
51 TAB_ID,
52 KPI_MODEL,
53 BSC_MODEL,
54 CROSS_MODEL,
55 DEFAULT_MODEL,
56 ZOOM_FACTOR,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_LOGIN,
62 PARENT_TAB_ID,
63 OWNER_ID,
64 SHORT_NAME
65 )
66 VALUES
67 (
68 X_TAB_ID,
69 X_KPI_MODEL,
70 X_BSC_MODEL,
71 X_CROSS_MODEL,
72 X_DEFAULT_MODEL,
73 X_ZOOM_FACTOR,
74 SYSDATE,
75 X_CREATED_BY,
76 SYSDATE,
77 X_LAST_UPDATED_BY,
78 X_LAST_UPDATE_LOGIN,
79 X_PARENT_TAB_ID,
80 X_OWNER_ID,
81 X_SHORT_NAME
82 );
83 INSERT INTO BSC_TABS_TL
84 (
85 TAB_ID,
86 NAME,
87 HELP,
88 ADDITIONAL_INFO,
89 LANGUAGE,
90 SOURCE_LANG
91 ) SELECT
92 X_TAB_ID,
93 X_NAME,
94 X_HELP,
95 X_ADDITIONAL_INFO,
96 L.LANGUAGE_CODE,
97 USERENV('LANG')
98 FROM FND_LANGUAGES L
99 WHERE L.INSTALLED_FLAG IN ('I', 'B')
100 AND NOT EXISTS
101 (
102 SELECT NULL
103 FROM BSC_TABS_TL T
104 WHERE T.TAB_ID = X_TAB_ID
105 AND T.LANGUAGE = L.LANGUAGE_CODE
106 );
107 OPEN C;
108 FETCH C INTO X_ROWID;
109 IF (C%NOTFOUND) THEN
110 CLOSE C;
111 RAISE NO_DATA_FOUND;
112 END IF;
113 CLOSE C;
114
115 END INSERT_ROW;
116
117
118 procedure LOCK_ROW (
119 X_TAB_ID in NUMBER,
120 X_KPI_MODEL in NUMBER,
121 X_BSC_MODEL in NUMBER,
122 X_CROSS_MODEL in NUMBER,
123 X_DEFAULT_MODEL in NUMBER,
124 X_ZOOM_FACTOR in NUMBER,
125 X_NAME in VARCHAR2,
126 X_HELP in VARCHAR2
127 ) is
128 cursor c is select
129 KPI_MODEL,
130 BSC_MODEL,
131 CROSS_MODEL,
132 DEFAULT_MODEL,
133 ZOOM_FACTOR
134 from BSC_TABS_B
135 where TAB_ID = X_TAB_ID
136 for update of TAB_ID nowait;
137 recinfo c%rowtype;
138
139 cursor c1 is select
140 NAME,
141 HELP,
142 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
143 from BSC_TABS_TL
144 where TAB_ID = X_TAB_ID
145 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
146 for update of TAB_ID nowait;
147 begin
148 open c;
149 fetch c into recinfo;
150 if (c%notfound) then
151 close c;
152 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
153 app_exception.raise_exception;
154 end if;
155 close c;
156 if ( ((recinfo.KPI_MODEL = X_KPI_MODEL)
157 OR ((recinfo.KPI_MODEL is null) AND (X_KPI_MODEL is null)))
158 AND ((recinfo.BSC_MODEL = X_BSC_MODEL)
159 OR ((recinfo.BSC_MODEL is null) AND (X_BSC_MODEL is null)))
160 AND ((recinfo.CROSS_MODEL = X_CROSS_MODEL)
161 OR ((recinfo.CROSS_MODEL is null) AND (X_CROSS_MODEL is null)))
162 AND ((recinfo.DEFAULT_MODEL = X_DEFAULT_MODEL)
163 OR ((recinfo.DEFAULT_MODEL is null) AND (X_DEFAULT_MODEL is null)))
164 AND ((recinfo.ZOOM_FACTOR = X_ZOOM_FACTOR)
165 OR ((recinfo.ZOOM_FACTOR is null) AND (X_ZOOM_FACTOR is null)))
166 ) then
167 null;
168 else
169 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
170 app_exception.raise_exception;
171 end if;
172
173 for tlinfo in c1 loop
174 if (tlinfo.BASELANG = 'Y') then
175 if ( (tlinfo.NAME = X_NAME)
176 AND ((tlinfo.HELP = X_HELP)
177 OR ((tlinfo.HELP is null) AND (X_HELP is null)))
178 ) then
179 null;
180 else
181 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
182 app_exception.raise_exception;
183 end if;
184 end if;
185 end loop;
186 return;
187 end LOCK_ROW;
188
189 PROCEDURE UPDATE_ROW
190 (
191 X_TAB_ID IN NUMBER,
192 X_SHORT_NAME IN VARCHAR2,
193 X_KPI_MODEL IN NUMBER,
194 X_BSC_MODEL IN NUMBER,
195 X_CROSS_MODEL IN NUMBER,
196 X_DEFAULT_MODEL IN NUMBER,
197 X_ZOOM_FACTOR IN NUMBER,
198 X_TAB_INDEX IN NUMBER,
199 X_PARENT_TAB_ID IN NUMBER,
200 X_OWNER_ID IN NUMBER,
201 X_LAST_UPDATE_DATE IN DATE, /* DEFAULT SYSDATE */
202 X_LAST_UPDATED_BY IN NUMBER,
203 X_NAME IN VARCHAR2,
204 X_HELP IN VARCHAR2,
205 X_LAST_UPDATE_LOGIN IN NUMBER,
206 X_ADDITIONAL_INFO IN VARCHAR2
207 ) IS
208
209 BEGIN
210
211 IF X_TAB_ID IS NULL THEN
212 IF X_SHORT_NAME IS NOT NULL THEN
213 UPDATE BSC_TABS_B
214 SET KPI_MODEL = NVL(X_KPI_MODEL, KPI_MODEL),
215 BSC_MODEL = NVL(X_BSC_MODEL, BSC_MODEL),
216 CROSS_MODEL = NVL(X_CROSS_MODEL, CROSS_MODEL),
217 DEFAULT_MODEL = NVL(X_DEFAULT_MODEL, DEFAULT_MODEL),
218 ZOOM_FACTOR = NVL(X_ZOOM_FACTOR, ZOOM_FACTOR),
219 TAB_INDEX = NVL(X_TAB_INDEX, TAB_INDEX),
220 PARENT_TAB_ID = X_PARENT_TAB_ID,
221 OWNER_ID = X_OWNER_ID,
222 SHORT_NAME = X_SHORT_NAME,
223 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE, SYSDATE),
224 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
225 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
226 WHERE SHORT_NAME = X_SHORT_NAME
227 AND LAST_UPDATE_DATE < X_LAST_UPDATE_DATE;
228 IF (NOT SQL%NOTFOUND) THEN
229 UPDATE BSC_TABS_TL
230 SET NAME = NVL(X_NAME, NAME),
231 HELP = X_HELP,
232 ADDITIONAL_INFO = X_ADDITIONAL_INFO,
233 SOURCE_LANG = USERENV('LANG')
234 WHERE TAB_ID = (SELECT TAB_ID FROM BSC_TABS_B WHERE SHORT_NAME = X_SHORT_NAME)
235 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
236 END IF;
237 END IF;
238 ELSE
239
240 UPDATE BSC_TABS_B
241 SET KPI_MODEL = NVL(X_KPI_MODEL, KPI_MODEL),
242 BSC_MODEL = NVL(X_BSC_MODEL, BSC_MODEL),
243 CROSS_MODEL = NVL(X_CROSS_MODEL, CROSS_MODEL),
244 DEFAULT_MODEL = NVL(X_DEFAULT_MODEL, DEFAULT_MODEL),
245 ZOOM_FACTOR = NVL(X_ZOOM_FACTOR, ZOOM_FACTOR),
246 TAB_INDEX = NVL(X_TAB_INDEX, TAB_INDEX),
247 PARENT_TAB_ID = X_PARENT_TAB_ID,
248 OWNER_ID = X_OWNER_ID,
249 SHORT_NAME = X_SHORT_NAME,
250 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE, SYSDATE),
251 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
252 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
253 WHERE TAB_ID = X_TAB_ID;
254 IF (NOT SQL%NOTFOUND) THEN
255 UPDATE BSC_TABS_TL
256 SET NAME = NVL(X_NAME, NAME),
257 HELP = HELP,
258 ADDITIONAL_INFO = X_ADDITIONAL_INFO,
259 SOURCE_LANG = USERENV('LANG')
260 WHERE TAB_ID = X_TAB_ID
261 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
262 END IF;
263
264 END IF;
265
266 END UPDATE_ROW;
267
268 procedure DELETE_ROW (
269 X_TAB_ID in NUMBER
270 ) is
271 begin
272 delete from BSC_TABS_TL
273 where TAB_ID = X_TAB_ID;
274
275 if (sql%notfound) then
276 raise no_data_found;
277 end if;
278
279 delete from BSC_TABS_B
280 where TAB_ID = X_TAB_ID;
281
282 if (sql%notfound) then
283 raise no_data_found;
284 end if;
285 end DELETE_ROW;
286
287 procedure ADD_LANGUAGE
288 is
289 begin
290 delete from BSC_TABS_TL T
291 where not exists
292 (select NULL
293 from BSC_TABS_B B
294 where B.TAB_ID = T.TAB_ID
295 );
296
297 update BSC_TABS_TL T set (
298 NAME,
299 HELP,
300 ADDITIONAL_INFO
301 ) = (select
302 B.NAME,
303 B.HELP,
304 ADDITIONAL_INFO
305 from BSC_TABS_TL B
306 where B.TAB_ID = T.TAB_ID
307 and B.LANGUAGE = T.SOURCE_LANG)
308 where (
309 T.TAB_ID,
310 T.LANGUAGE
311 ) in (select
312 SUBT.TAB_ID,
313 SUBT.LANGUAGE
314 from BSC_TABS_TL SUBB, BSC_TABS_TL SUBT
315 where SUBB.TAB_ID = SUBT.TAB_ID
316 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
317 and (SUBB.NAME <> SUBT.NAME
318 or SUBB.HELP <> SUBT.HELP
319 or SUBB.ADDITIONAL_INFO <> SUBT.ADDITIONAL_INFO
320 or (SUBB.HELP is null and SUBT.HELP is not null)
321 or (SUBB.HELP is not null and SUBT.HELP is null)
322 or (SUBB.ADDITIONAL_INFO is not null and SUBT.ADDITIONAL_INFO is null)
323 ));
324
325 insert into BSC_TABS_TL (
326 TAB_ID,
327 NAME,
328 HELP,
329 ADDITIONAL_INFO,
330 LANGUAGE,
331 SOURCE_LANG
332 ) select
333 B.TAB_ID,
334 B.NAME,
335 B.HELP,
336 ADDITIONAL_INFO,
337 L.LANGUAGE_CODE,
338 B.SOURCE_LANG
339 from BSC_TABS_TL B, FND_LANGUAGES L
340 where L.INSTALLED_FLAG in ('I', 'B')
341 and B.LANGUAGE = userenv('LANG')
342 and not exists
343 (select NULL
344 from BSC_TABS_TL T
345 where T.TAB_ID = B.TAB_ID
346 and T.LANGUAGE = L.LANGUAGE_CODE);
347 end ADD_LANGUAGE;
348
349 end BSC_TABS_PKG;
[Home] [Help]