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