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