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