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