[Home] [Help]
PACKAGE BODY: APPS.JTF_NAV_VIEWBYS_PKG
Source
1 package body JTF_NAV_VIEWBYS_PKG as
2 /* $Header: jtfnvbyb.pls 120.2 2005/12/12 15:48:32 stopiwal ship $ */
3
4 procedure INSERT_ROW (
5 X_VIEWBY_VALUE in VARCHAR2,
6 X_TAB_id in number,
7 X_SEQUENCE_NUMBER in NUMBER,
8 X_VIEWBY_LABEL in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER) is
14
15 l_viewby_id number := 0;
16
17 BEGIN
18 SELECT jtf_nav_viewbys_s.nextval
19 INTO l_viewby_id
20 FROM dual;
21 insert into JTF_NAV_VIEWBYS_B
22 (viewby_id,
23 VIEWBY_VALUE,
24 TAB_id,
25 SEQUENCE_NUMBER,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 l_viewby_id,
33 X_VIEWBY_VALUE,
34 X_TAB_id,
35 X_SEQUENCE_NUMBER,
36 X_CREATION_DATE,
37 X_CREATED_BY,
38 X_LAST_UPDATE_DATE,
39 X_LAST_UPDATED_BY,
40 X_LAST_UPDATE_LOGIN);
41
42 insert into JTF_NAV_VIEWBYS_TL (
43 viewby_id,
44 VIEWBY_LABEL,
45 CREATED_BY,
46 CREATION_DATE,
47 LAST_UPDATE_LOGIN,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LANGUAGE,
51 SOURCE_LANG)
52 select
53 l_viewby_id,
54 X_VIEWBY_LABEL,
55 X_CREATED_BY,
56 X_CREATION_DATE,
57 X_LAST_UPDATE_LOGIN,
58 X_LAST_UPDATE_DATE,
59 X_LAST_UPDATED_BY,
60 L.LANGUAGE_CODE,
61 userenv('LANG')
62 from FND_LANGUAGES L
63 where L.INSTALLED_FLAG in ('I', 'B')
64 and not exists
65 (select NULL
66 from JTF_NAV_VIEWBYS_TL T
67 where T.VIEWBY_id = l_viewby_id
68 and T.LANGUAGE = L.LANGUAGE_CODE);
69
70 end INSERT_ROW;
71
72 procedure UPDATE_ROW
73 (x_viewby_id IN number,
74 X_VIEWBY_VALUE in VARCHAR2,
75 X_TAB_id in number,
76 X_SEQUENCE_NUMBER in NUMBER,
77 X_VIEWBY_LABEL in VARCHAR2,
78 X_LAST_UPDATE_DATE in DATE,
79 X_LAST_UPDATED_BY in NUMBER,
80 X_LAST_UPDATE_LOGIN in NUMBER) is
81 begin
82 update JTF_NAV_VIEWBYS_B SET
83 viewby_value = x_viewby_value,
84 TAB_id = X_TAB_id,
85 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
86 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
87 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
88 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
89 where VIEWBY_id = X_VIEWBY_id;
90
91 if (sql%notfound) then
92 raise no_data_found;
93 end if;
94
95 update JTF_NAV_VIEWBYS_TL set
96 VIEWBY_LABEL = X_VIEWBY_LABEL,
97 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
98 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
99 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
100 SOURCE_LANG = userenv('LANG')
101 where VIEWBY_id = X_VIEWBY_id
102 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
103
104 if (sql%notfound) then
105 raise no_data_found;
106 end if;
107 end UPDATE_ROW;
108
109 procedure LOCK_ROW (
110 X_VIEWBY_id in number,
111 X_TAB_id in number,
112 X_VIEWBY_VALUE in VARCHAR2,
113 X_SEQUENCE_NUMBER in NUMBER,
114 X_VIEWBY_LABEL in VARCHAR2) is
115
116 cursor c IS
117 select viewby_value, TAB_id, SEQUENCE_NUMBER
118 from JTF_NAV_VIEWBYS_B
119 where VIEWBY_id = X_VIEWBY_id
120 for update of VIEWBY_id nowait;
121 recinfo c%rowtype;
122
123 cursor c1 is select
124 VIEWBY_LABEL,
125 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
126 from JTF_NAV_VIEWBYS_TL
127 where VIEWBY_id = X_VIEWBY_id
128 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
129 for update of VIEWBY_id nowait;
130 begin
131 open c;
132 fetch c into recinfo;
133 if (c%notfound) then
134 close c;
135 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
136 app_exception.raise_exception;
137 end if;
138 close c;
139 if ( ((recinfo.TAB_id = X_TAB_id)
140 OR ((recinfo.TAB_id is null) AND (X_TAB_id is null)))
141 AND (recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
142 AND (recinfo.viewby_value = x_viewby_value)
143 ) then
144 null;
145 else
146 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
147 app_exception.raise_exception;
148 end if;
149
150 for tlinfo in c1 loop
151 if (tlinfo.BASELANG = 'Y') then
152 if ( (tlinfo.VIEWBY_LABEL = X_VIEWBY_LABEL)
153 ) then
154 null;
155 else
156 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
157 app_exception.raise_exception;
158 end if;
159 end if;
160 end loop;
161 return;
162 end LOCK_ROW;
163
164 procedure DELETE_ROW (
165 X_VIEWBY_id in number) is
166 begin
167 delete from JTF_NAV_VIEWBYS_TL
168 where VIEWBY_id = X_VIEWBY_id;
169
170 if (sql%notfound) then
171 raise no_data_found;
172 end if;
173
174 delete from JTF_NAV_VIEWBYS_B
175 where VIEWBY_id = X_VIEWBY_id;
176
177 if (sql%notfound) then
178 raise no_data_found;
179 end if;
180 end DELETE_ROW;
181
182 procedure ADD_LANGUAGE
183 is
184 begin
185 delete from JTF_NAV_VIEWBYS_TL T
186 where not exists
187 (select NULL
188 from JTF_NAV_VIEWBYS_B B
189 where B.VIEWBY_id = T.VIEWBY_id
190 );
191
192 update JTF_NAV_VIEWBYS_TL T set (
193 VIEWBY_LABEL
194 ) = (select
195 B.VIEWBY_LABEL
196 from JTF_NAV_VIEWBYS_TL B
197 where B.VIEWBY_id = T.VIEWBY_id
198 and B.LANGUAGE = T.SOURCE_LANG)
199 where (
200 T.VIEWBY_id,
201 T.LANGUAGE
202 ) in (select
203 SUBT.VIEWBY_id,
204 SUBT.LANGUAGE
205 from JTF_NAV_VIEWBYS_TL SUBB, JTF_NAV_VIEWBYS_TL SUBT
206 where SUBB.VIEWBY_id = SUBT.VIEWBY_id
207 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
208 and (SUBB.VIEWBY_LABEL <> SUBT.VIEWBY_LABEL
209 ));
210
211 insert into JTF_NAV_VIEWBYS_TL (
212 VIEWBY_id,
213 VIEWBY_LABEL,
214 CREATED_BY,
215 CREATION_DATE,
216 LAST_UPDATE_LOGIN,
217 LAST_UPDATE_DATE,
218 LAST_UPDATED_BY,
219 LANGUAGE,
220 SOURCE_LANG
221 ) select
222 B.VIEWBY_id,
223 B.VIEWBY_LABEL,
224 B.CREATED_BY,
225 B.CREATION_DATE,
226 B.LAST_UPDATE_LOGIN,
227 B.LAST_UPDATE_DATE,
228 B.LAST_UPDATED_BY,
229 L.LANGUAGE_CODE,
230 B.SOURCE_LANG
231 from JTF_NAV_VIEWBYS_TL B, FND_LANGUAGES L
232 where L.INSTALLED_FLAG in ('I', 'B')
233 and B.LANGUAGE = userenv('LANG')
234 and not exists
235 (select NULL
236 from JTF_NAV_VIEWBYS_TL T
237 where T.VIEWBY_id = B.VIEWBY_id
238 and T.LANGUAGE = L.LANGUAGE_CODE);
239 end ADD_LANGUAGE;
240
241 -- --------------------------------------------------------------------
242 -- Procedure : LOAD_ROW
243 -- Description : Called by FNDLOAD to upload seed datas, this procedure
244 -- only handle seed data.
245 -- --------------------------------------------------------------------
246 PROCEDURE LOAD_row
247 (X_VIEWBY_VALUE in VARCHAR2,
248 X_TAB_VALUE in VARCHAR2,
249 X_SEQUENCE_NUMBER in NUMBER,
250 X_VIEWBY_LABEL in VARCHAR2,
251 X_OWNER in VARCHAR2) IS
252 user_id NUMBER;
253 l_tab_id number;
254 l_viewby_id number;
255
256 BEGIN
257 -- Validate input data
258 IF (x_viewby_value IS NULL) OR (x_viewby_label IS NULL)
259 OR (x_tab_value IS NULL) THEN
260 GOTO end_load_row;
261 END IF;
262
263 /*
264 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
265 user_id := 1;
266 ELSE
267 user_id := 0;
268 END IF;
269 */
270 user_id := fnd_load_util.owner_id(x_owner);
271
272 --select the tab id
273 SELECT tab_id
274 INTO l_tab_id
275 FROM jtf_nav_tabs_b
276 WHERE tab_value = x_tab_value;
277
278 -- Load The record to _B table
279 UPDATE jtf_nav_viewbys_b SET
280 tab_id = l_tab_id,
281 sequence_number = x_sequence_number,
282 last_update_date = sysdate,
283 last_updated_by = user_id,
284 last_update_login = 0
285 WHERE viewby_value = x_viewby_value;
286
287 IF (SQL%NOTFOUND) THEN
288 -- Insert new record to _B table
289 SELECT jtf_nav_viewbys_s.nextval
290 INTO l_viewby_id
291 FROM dual;
292
293 INSERT INTO jtf_nav_viewbys_b
294 (viewby_id,
295 viewby_value,
296 tab_id,
297 sequence_number,
298 creation_date,
299 created_by,
300 last_update_date,
301 last_updated_by,
302 last_update_login
303 ) VALUES
304 (l_viewby_id,
305 x_viewby_value,
306 l_tab_id,
307 x_sequence_number,
308 sysdate,
309 user_id,
310 sysdate,
311 user_id,
312 0
313 );
314 END IF;
315
316 --select the viewby id
317 SELECT viewby_id
318 INTO l_viewby_id
319 FROM jtf_nav_viewbys_b
320 WHERE viewby_value = x_viewby_value;
321
322 -- Load The record to _TL table
323 UPDATE jtf_nav_viewbys_tl SET
324 viewby_label = x_viewby_label,
325 last_update_date = sysdate,
326 last_updated_by = user_id,
327 last_update_login = 0,
328 source_lang = userenv('LANG')
329 WHERE viewby_id = l_viewby_id
330 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
331
332 IF (SQL%NOTFOUND) THEN
333 -- Insert new record to _TL table
334 INSERT INTO jtf_nav_viewbys_tl
335 (viewby_id,
336 viewby_label,
337 creation_date,
338 created_by,
339 last_update_date,
340 last_updated_by,
341 last_update_login,
342 language,
343 source_lang)
344 SELECT
345 l_viewby_id,
346 x_viewby_label,
347 sysdate,
348 user_id,
349 sysdate,
350 user_id,
351 0,
352 l.language_code,
353 userenv('LANG')
354 FROM fnd_languages l
355 WHERE l.installed_flag IN ('I', 'B')
356 AND NOT EXISTS
357 (SELECT NULL
358 FROM jtf_nav_viewbys_tl t
359 WHERE t.viewby_id = l_viewby_id
360 AND t.language = l.language_code);
361 END IF;
362 << end_load_row >>
363 NULL;
364 END LOAD_ROW ;
365
366 -- --------------------------------------------------------------------
367 -- Procedure : TRANSLATE_ROW
368 -- Description : Called by FNDLOAD to translate seed datas, this procedure
369 -- only handle seed datas.
370 -- --------------------------------------------------------------------
371 PROCEDURE TRANSLATE_ROW
372 ( x_viewby_VALUE IN VARCHAR2,
373 x_viewby_label IN VARCHAR2,
374 x_owner IN VARCHAR2) IS
375 user_id NUMBER;
376 l_viewby_id number;
377
378 BEGIN
379 -- Validate input data
380 IF (x_viewby_value IS NULL) OR (x_viewby_label IS NULL) THEN
381 GOTO end_translate_row;
382 END IF;
383
384 /*
385 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
386 user_id := 1;
387 ELSE
388 user_id := 0;
389 END IF;
390 */
391 user_id := fnd_load_util.owner_id(x_owner);
392
393 --select the viewby_id
394 SELECT viewby_id
395 INTO l_viewby_id
396 FROM jtf_nav_viewbys_b
397 WHERE viewby_value = x_viewby_value;
398
399 -- Update the translation
400 UPDATE jtf_nav_viewbys_tl SET
401 viewby_label = x_viewby_label,
402 last_update_date = sysdate,
403 last_updated_by = user_id,
404 last_update_login = 0,
405 source_lang = userenv('LANG')
406 WHERE viewby_id = l_viewby_id
407 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
408
409 << end_translate_row >>
410 NULL;
411 END TRANSLATE_ROW ;
412
413 END JTF_NAV_VIEWBYS_PKG;