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