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