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