[Home] [Help]
PACKAGE BODY: APPS.BNE_VIEWERS_PKG
Source
1 package body BNE_VIEWERS_PKG as
2 /* $Header: bneviewb.pls 120.3 2005/08/18 06:39:07 dagroves noship $ */
3
4 --------------------------------------------------------------------------------
5 -- Much of the following code was created using the tltblgen utility. --
6 -- --
7 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/mldata.txt --
8 -- --
9 -- --
10 --------------------------------------------------------------------------------
11
12 procedure INSERT_ROW (
13 X_ROWID in out NOCOPY VARCHAR2,
14 X_APPLICATION_ID in NUMBER,
15 X_VIEWER_CODE in VARCHAR2,
16 X_PARAM_LIST_APP_ID in NUMBER,
17 X_PARAM_LIST_CODE in VARCHAR2,
18 X_OBJECT_VERSION_NUMBER in NUMBER,
19 X_VIEWER_JAVA_CLASS in VARCHAR2,
20 X_USER_NAME in VARCHAR2,
21 X_CREATION_DATE in DATE,
22 X_CREATED_BY in NUMBER,
23 X_LAST_UPDATE_DATE in DATE,
24 X_LAST_UPDATED_BY in NUMBER,
25 X_LAST_UPDATE_LOGIN in NUMBER,
26 X_CREATE_DOC_LIST_APP_ID in NUMBER,
27 X_CREATE_DOC_LIST_CODE in VARCHAR2,
28 X_ENABLED_FLAG in VARCHAR2
29 ) is
30 cursor C is select ROWID from BNE_VIEWERS_B
31 where APPLICATION_ID = X_APPLICATION_ID
32 and VIEWER_CODE = X_VIEWER_CODE
33 ;
34 begin
35 insert into BNE_VIEWERS_B (
36 PARAM_LIST_APP_ID,
37 PARAM_LIST_CODE,
38 APPLICATION_ID,
39 VIEWER_CODE,
40 OBJECT_VERSION_NUMBER,
41 VIEWER_JAVA_CLASS,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 CREATE_DOC_LIST_APP_ID,
48 CREATE_DOC_LIST_CODE,
49 ENABLED_FLAG
50 ) values (
51 X_PARAM_LIST_APP_ID,
52 X_PARAM_LIST_CODE,
53 X_APPLICATION_ID,
54 X_VIEWER_CODE,
55 X_OBJECT_VERSION_NUMBER,
56 X_VIEWER_JAVA_CLASS,
57 X_CREATION_DATE,
58 X_CREATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_LOGIN,
62 X_CREATE_DOC_LIST_APP_ID,
63 X_CREATE_DOC_LIST_CODE,
64 X_ENABLED_FLAG
65 );
66
67 insert into BNE_VIEWERS_TL (
68 APPLICATION_ID,
69 VIEWER_CODE,
70 USER_NAME,
71 CREATED_BY,
72 CREATION_DATE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_LOGIN,
75 LAST_UPDATE_DATE,
76 LANGUAGE,
77 SOURCE_LANG
78 ) select
79 X_APPLICATION_ID,
80 X_VIEWER_CODE,
81 X_USER_NAME,
82 X_CREATED_BY,
83 X_CREATION_DATE,
84 X_LAST_UPDATED_BY,
85 X_LAST_UPDATE_LOGIN,
86 X_LAST_UPDATE_DATE,
87 L.LANGUAGE_CODE,
88 userenv('LANG')
89 from FND_LANGUAGES L
90 where L.INSTALLED_FLAG in ('I', 'B')
91 and not exists
92 (select NULL
93 from BNE_VIEWERS_TL T
94 where T.APPLICATION_ID = X_APPLICATION_ID
95 and T.VIEWER_CODE = X_VIEWER_CODE
96 and T.LANGUAGE = L.LANGUAGE_CODE);
97
98 open c;
99 fetch c into X_ROWID;
100 if (c%notfound) then
101 close c;
102 raise no_data_found;
103 end if;
104 close c;
105
106 end INSERT_ROW;
107
108 procedure LOCK_ROW (
109 X_APPLICATION_ID in NUMBER,
110 X_VIEWER_CODE in VARCHAR2,
111 X_PARAM_LIST_APP_ID in NUMBER,
112 X_PARAM_LIST_CODE in VARCHAR2,
113 X_OBJECT_VERSION_NUMBER in NUMBER,
114 X_VIEWER_JAVA_CLASS in VARCHAR2,
115 X_USER_NAME in VARCHAR2,
116 X_CREATE_DOC_LIST_APP_ID in NUMBER,
117 X_CREATE_DOC_LIST_CODE in VARCHAR2,
118 X_ENABLED_FLAG in VARCHAR2
119 ) is
120 cursor c is select
121 PARAM_LIST_APP_ID,
122 PARAM_LIST_CODE,
123 OBJECT_VERSION_NUMBER,
124 VIEWER_JAVA_CLASS,
125 CREATE_DOC_LIST_APP_ID,
126 CREATE_DOC_LIST_CODE,
127 ENABLED_FLAG
128 from BNE_VIEWERS_B
129 where APPLICATION_ID = X_APPLICATION_ID
130 and VIEWER_CODE = X_VIEWER_CODE
131 for update of APPLICATION_ID nowait;
132 recinfo c%rowtype;
133
134 cursor c1 is select
135 USER_NAME,
136 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
137 from BNE_VIEWERS_TL
138 where APPLICATION_ID = X_APPLICATION_ID
139 and VIEWER_CODE = X_VIEWER_CODE
140 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
141 for update of APPLICATION_ID nowait;
142 begin
143 open c;
144 fetch c into recinfo;
145 if (c%notfound) then
146 close c;
147 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
148 app_exception.raise_exception;
149 end if;
150 close c;
151 if ( ((recinfo.PARAM_LIST_APP_ID = X_PARAM_LIST_APP_ID)
152 OR ((recinfo.PARAM_LIST_APP_ID is null) AND (X_PARAM_LIST_APP_ID is null)))
153 AND ((recinfo.PARAM_LIST_CODE = X_PARAM_LIST_CODE)
154 OR ((recinfo.PARAM_LIST_CODE is null) AND (X_PARAM_LIST_CODE is null)))
155 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
156 AND (recinfo.VIEWER_JAVA_CLASS = X_VIEWER_JAVA_CLASS)
157 AND ((recinfo.CREATE_DOC_LIST_APP_ID = X_CREATE_DOC_LIST_APP_ID)
158 OR ((recinfo.CREATE_DOC_LIST_APP_ID is null) AND (X_CREATE_DOC_LIST_APP_ID is null)))
159 AND ((recinfo.CREATE_DOC_LIST_CODE = X_CREATE_DOC_LIST_CODE)
160 OR ((recinfo.CREATE_DOC_LIST_CODE is null) AND (X_CREATE_DOC_LIST_CODE is null)))
161 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
162 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
163 ) then
164 null;
165 else
166 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
167 app_exception.raise_exception;
168 end if;
169
170 for tlinfo in c1 loop
171 if (tlinfo.BASELANG = 'Y') then
172 if ( (tlinfo.USER_NAME = X_USER_NAME)
173 ) then
174 null;
175 else
176 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
177 app_exception.raise_exception;
178 end if;
179 end if;
180 end loop;
181 return;
182 end LOCK_ROW;
183
184 procedure UPDATE_ROW (
185 X_APPLICATION_ID in NUMBER,
186 X_VIEWER_CODE in VARCHAR2,
187 X_PARAM_LIST_APP_ID in NUMBER,
188 X_PARAM_LIST_CODE in VARCHAR2,
189 X_OBJECT_VERSION_NUMBER in NUMBER,
190 X_VIEWER_JAVA_CLASS in VARCHAR2,
191 X_USER_NAME in VARCHAR2,
192 X_LAST_UPDATE_DATE in DATE,
193 X_LAST_UPDATED_BY in NUMBER,
194 X_LAST_UPDATE_LOGIN in NUMBER,
195 X_CREATE_DOC_LIST_APP_ID in NUMBER,
196 X_CREATE_DOC_LIST_CODE in VARCHAR2,
197 X_ENABLED_FLAG in VARCHAR2
198 ) is
199 begin
200 update BNE_VIEWERS_B set
201 PARAM_LIST_APP_ID = X_PARAM_LIST_APP_ID,
202 PARAM_LIST_CODE = X_PARAM_LIST_CODE,
203 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
204 VIEWER_JAVA_CLASS = X_VIEWER_JAVA_CLASS,
205 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
206 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
207 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
208 CREATE_DOC_LIST_APP_ID = X_CREATE_DOC_LIST_APP_ID,
209 CREATE_DOC_LIST_CODE = X_CREATE_DOC_LIST_CODE,
210 ENABLED_FLAG = X_ENABLED_FLAG
211 where APPLICATION_ID = X_APPLICATION_ID
212 and VIEWER_CODE = X_VIEWER_CODE;
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217
218 update BNE_VIEWERS_TL set
219 USER_NAME = X_USER_NAME,
220 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
223 SOURCE_LANG = userenv('LANG')
224 where APPLICATION_ID = X_APPLICATION_ID
225 and VIEWER_CODE = X_VIEWER_CODE
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
227
228 if (sql%notfound) then
229 raise no_data_found;
230 end if;
231 end UPDATE_ROW;
232
233 procedure DELETE_ROW (
234 X_APPLICATION_ID in NUMBER,
235 X_VIEWER_CODE in VARCHAR2
236 ) is
237 begin
238 delete from BNE_VIEWERS_TL
239 where APPLICATION_ID = X_APPLICATION_ID
240 and VIEWER_CODE = X_VIEWER_CODE;
241
242 if (sql%notfound) then
243 raise no_data_found;
244 end if;
245
246 delete from BNE_VIEWERS_B
247 where APPLICATION_ID = X_APPLICATION_ID
248 and VIEWER_CODE = X_VIEWER_CODE;
249
250 if (sql%notfound) then
251 raise no_data_found;
252 end if;
253 end DELETE_ROW;
254
255 procedure ADD_LANGUAGE
256 is
257 begin
258 delete from BNE_VIEWERS_TL T
259 where not exists
260 (select NULL
261 from BNE_VIEWERS_B B
262 where B.APPLICATION_ID = T.APPLICATION_ID
263 and B.VIEWER_CODE = T.VIEWER_CODE
264 );
265
266 update BNE_VIEWERS_TL T set (
267 USER_NAME
268 ) = (select
269 B.USER_NAME
270 from BNE_VIEWERS_TL B
271 where B.APPLICATION_ID = T.APPLICATION_ID
272 and B.VIEWER_CODE = T.VIEWER_CODE
273 and B.LANGUAGE = T.SOURCE_LANG)
274 where (
275 T.APPLICATION_ID,
276 T.VIEWER_CODE,
277 T.LANGUAGE
278 ) in (select
279 SUBT.APPLICATION_ID,
280 SUBT.VIEWER_CODE,
281 SUBT.LANGUAGE
282 from BNE_VIEWERS_TL SUBB, BNE_VIEWERS_TL SUBT
283 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
284 and SUBB.VIEWER_CODE = SUBT.VIEWER_CODE
285 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
286 and (SUBB.USER_NAME <> SUBT.USER_NAME
287 ));
288
289 insert into BNE_VIEWERS_TL (
290 APPLICATION_ID,
291 VIEWER_CODE,
292 USER_NAME,
293 CREATED_BY,
294 CREATION_DATE,
295 LAST_UPDATED_BY,
296 LAST_UPDATE_LOGIN,
297 LAST_UPDATE_DATE,
298 LANGUAGE,
299 SOURCE_LANG
300 ) select
301 B.APPLICATION_ID,
302 B.VIEWER_CODE,
303 B.USER_NAME,
304 B.CREATED_BY,
305 B.CREATION_DATE,
306 B.LAST_UPDATED_BY,
307 B.LAST_UPDATE_LOGIN,
308 B.LAST_UPDATE_DATE,
309 L.LANGUAGE_CODE,
310 B.SOURCE_LANG
311 from BNE_VIEWERS_TL B, FND_LANGUAGES L
312 where L.INSTALLED_FLAG in ('I', 'B')
313 and B.LANGUAGE = userenv('LANG')
314 and not exists
315 (select NULL
316 from BNE_VIEWERS_TL T
317 where T.APPLICATION_ID = B.APPLICATION_ID
318 and T.VIEWER_CODE = B.VIEWER_CODE
319 and T.LANGUAGE = L.LANGUAGE_CODE);
320 end ADD_LANGUAGE;
321
322 --------------------------------------------------------------------------------
323 -- PROCEDURE: LOAD_ROW --
324 -- --
325 -- DESCRIPTION: Load a row into the BNE_VIEWERS entity. --
326 -- This proc is called from the apps loader. --
327 -- --
328 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
329 -- --
330 -- --
331 -- MODIFICATION HISTORY --
332 -- Date Username Description --
333 -- 1-Oct-02 DGROVES CREATED --
334 --------------------------------------------------------------------------------
335
336 procedure LOAD_ROW(
337 x_viewer_asn IN VARCHAR2,
338 x_viewer_code IN VARCHAR2,
339 x_object_version_number IN VARCHAR2,
340 x_viewer_java_class IN VARCHAR2,
341 x_param_list_asn IN VARCHAR2,
342 x_param_list_code IN VARCHAR2,
343 x_user_name IN VARCHAR2,
344 x_owner IN VARCHAR2,
345 x_last_update_date IN VARCHAR2,
346 x_custom_mode IN VARCHAR2,
347 x_create_doc_list_asn in VARCHAR2,
348 x_create_doc_list_code in VARCHAR2,
349 x_enabled_flag in VARCHAR2
350 )
351 is
352 l_app_id number;
353 l_param_app_id number;
354 l_create_doc_list_app_id number;
355 l_meaning varchar2(80);
356 l_row_id varchar2(64);
357 f_luby number; -- entity owner in file
358 f_ludate date; -- entity update date in file
359 db_luby number; -- entity owner in db
360 db_ludate date; -- entity update date in db
361 begin
362 -- translate values to IDs
363 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_viewer_asn);
364 l_param_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_param_list_asn);
365 l_create_doc_list_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_create_doc_list_asn);
366
367 -- Translate owner to file_last_updated_by
368 f_luby := fnd_load_util.owner_id(x_owner);
369
370 -- Translate char last_update_date to date
371 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
372 begin
373 select LAST_UPDATED_BY, LAST_UPDATE_DATE
374 into db_luby, db_ludate
375 from BNE_VIEWERS_B
376 where APPLICATION_ID = l_app_id
377 and VIEWER_CODE = x_viewer_code;
378
379 -- Test for customization and version
380 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
381 db_ludate, x_custom_mode)) then
382 -- Update existing row
383 BNE_VIEWERS_PKG.Update_Row(
384 X_APPLICATION_ID => l_app_id,
385 X_VIEWER_CODE => x_viewer_code,
386 X_PARAM_LIST_APP_ID => l_param_app_id,
387 X_PARAM_LIST_CODE => x_param_list_code,
388 X_OBJECT_VERSION_NUMBER => x_object_version_number,
389 X_VIEWER_JAVA_CLASS => x_viewer_java_class,
390 X_USER_NAME => x_user_name,
391 X_LAST_UPDATE_DATE => f_ludate,
392 X_LAST_UPDATED_BY => f_luby,
393 X_LAST_UPDATE_LOGIN => 0,
394 X_CREATE_DOC_LIST_APP_ID=> l_create_doc_list_app_id,
395 X_CREATE_DOC_LIST_CODE => x_create_doc_list_code,
396 X_ENABLED_FLAG => x_enabled_flag
397 );
398 end if;
399 exception
400 when no_data_found then
401 -- Record doesn't exist - insert in all cases
402 BNE_VIEWERS_PKG.Insert_Row(
403 X_ROWID => l_row_id,
404 X_APPLICATION_ID => l_app_id,
405 X_VIEWER_CODE => x_viewer_code,
406 X_PARAM_LIST_APP_ID => l_param_app_id,
407 X_PARAM_LIST_CODE => x_param_list_code,
408 X_OBJECT_VERSION_NUMBER => x_object_version_number,
409 X_VIEWER_JAVA_CLASS => x_viewer_java_class,
410 X_USER_NAME => x_user_name,
411 X_CREATION_DATE => f_ludate,
412 X_CREATED_BY => f_luby,
413 X_LAST_UPDATE_DATE => f_ludate,
414 X_LAST_UPDATED_BY => f_luby,
415 X_LAST_UPDATE_LOGIN => 0,
416 X_CREATE_DOC_LIST_APP_ID=> l_create_doc_list_app_id,
417 X_CREATE_DOC_LIST_CODE => x_create_doc_list_code,
418 X_ENABLED_FLAG => x_enabled_flag
419 );
420 end;
421 end LOAD_ROW;
422
423
424 --------------------------------------------------------------------------------
425 -- PROCEDURE: TRANSLATE_ROW --
426 -- --
427 -- DESCRIPTION: Load a translation into the BNE_VIEWERS entity. --
428 -- This proc is called from the apps loader. --
429 -- --
430 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
431 -- --
432 -- --
433 -- MODIFICATION HISTORY --
434 -- Date Username Description --
435 -- 1-Oct-02 DGROVES CREATED --
436 --------------------------------------------------------------------------------
437
438 procedure TRANSLATE_ROW(
439 x_viewer_asn IN VARCHAR2,
440 x_viewer_code IN VARCHAR2,
441 x_user_name IN VARCHAR2,
442 x_owner IN VARCHAR2,
443 x_last_update_date IN VARCHAR2,
444 x_custom_mode IN VARCHAR2
445 )
446 is
447 l_app_id number;
448 f_luby number; -- entity owner in file
449 f_ludate date; -- entity update date in file
450 db_luby number; -- entity owner in db
451 db_ludate date; -- entity update date in db
452 begin
453 -- translate values to IDs
454 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_viewer_asn);
455
456 -- Translate owner to file_last_updated_by
457 f_luby := fnd_load_util.owner_id(x_owner);
458
459 -- Translate char last_update_date to date
460 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
461 begin
462 select LAST_UPDATED_BY, LAST_UPDATE_DATE
463 into db_luby, db_ludate
464 from BNE_VIEWERS_TL
465 where APPLICATION_ID = l_app_id
466 and VIEWER_CODE = x_viewer_code
467 and LANGUAGE = userenv('LANG');
468
469 -- Test for customization and version
470 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
471 db_ludate, x_custom_mode)) then
472
473 update BNE_VIEWERS_TL
474 set USER_NAME = x_user_name,
475 LAST_UPDATE_DATE = f_ludate,
476 LAST_UPDATED_BY = f_luby,
477 LAST_UPDATE_LOGIN = 0,
478 SOURCE_LANG = userenv('LANG')
479 where APPLICATION_ID = l_app_id
480 AND VIEWER_CODE = x_viewer_code
481 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
482 ;
483 end if;
484 exception
485 when no_data_found then
486 -- Do not insert missing translations, skip this row
487 null;
488 end;
489 end TRANSLATE_ROW;
490
491 end BNE_VIEWERS_PKG;