[Home] [Help]
PACKAGE BODY: APPS.BNE_PERF_SETS_PKG
Source
1 package body BNE_PERF_SETS_PKG as
2 /* $Header: bneperfsetb.pls 120.2 2005/06/29 03:40:42 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_SET_CODE in VARCHAR2,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_USER_NAME in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from BNE_PERF_SETS_B
17 where SET_CODE = X_SET_CODE
18 ;
19 begin
20 insert into BNE_PERF_SETS_B (
21 SET_CODE,
22 OBJECT_VERSION_NUMBER,
23 ENABLED_FLAG,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN
29 ) values (
30 X_SET_CODE,
31 X_OBJECT_VERSION_NUMBER,
32 X_ENABLED_FLAG,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN
38 );
39
40 insert into BNE_PERF_SETS_TL (
41 SET_CODE,
42 USER_NAME,
43 CREATED_BY,
44 CREATION_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 LAST_UPDATE_DATE,
48 LANGUAGE,
49 SOURCE_LANG
50 ) select
51 X_SET_CODE,
52 X_USER_NAME,
53 X_CREATED_BY,
54 X_CREATION_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_LOGIN,
57 X_LAST_UPDATE_DATE,
58 L.LANGUAGE_CODE,
59 userenv('LANG')
60 from FND_LANGUAGES L
61 where L.INSTALLED_FLAG in ('I', 'B')
62 and not exists
63 (select NULL
64 from BNE_PERF_SETS_TL T
65 where T.SET_CODE = X_SET_CODE
66 and T.LANGUAGE = L.LANGUAGE_CODE);
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75
76 end INSERT_ROW;
77
78 procedure LOCK_ROW (
79 X_SET_CODE in VARCHAR2,
80 X_OBJECT_VERSION_NUMBER in NUMBER,
81 X_ENABLED_FLAG in VARCHAR2,
82 X_USER_NAME in VARCHAR2
83 ) is
84 cursor c is select
85 OBJECT_VERSION_NUMBER,
86 ENABLED_FLAG
87 from BNE_PERF_SETS_B
88 where SET_CODE = X_SET_CODE
89 for update of SET_CODE nowait;
90 recinfo c%rowtype;
91
92 cursor c1 is select
93 USER_NAME,
94 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
95 from BNE_PERF_SETS_TL
96 where SET_CODE = X_SET_CODE
97 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
98 for update of SET_CODE nowait;
99 begin
100 open c;
101 fetch c into recinfo;
102 if (c%notfound) then
103 close c;
104 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
105 app_exception.raise_exception;
106 end if;
107 close c;
108 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
109 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
110 ) then
111 null;
112 else
113 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
114 app_exception.raise_exception;
115 end if;
116
117 for tlinfo in c1 loop
118 if (tlinfo.BASELANG = 'Y') then
119 if ( (tlinfo.USER_NAME = X_USER_NAME)
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126 end if;
127 end loop;
128 return;
129 end LOCK_ROW;
130
131 procedure UPDATE_ROW (
132 X_SET_CODE in VARCHAR2,
133 X_OBJECT_VERSION_NUMBER in NUMBER,
134 X_ENABLED_FLAG in VARCHAR2,
135 X_USER_NAME in VARCHAR2,
136 X_LAST_UPDATE_DATE in DATE,
137 X_LAST_UPDATED_BY in NUMBER,
138 X_LAST_UPDATE_LOGIN in NUMBER
139 ) is
140 begin
141 update BNE_PERF_SETS_B set
142 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
143 ENABLED_FLAG = X_ENABLED_FLAG,
144 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
145 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
146 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
147 where SET_CODE = X_SET_CODE;
148
149 if (sql%notfound) then
150 raise no_data_found;
151 end if;
152
153 update BNE_PERF_SETS_TL set
154 USER_NAME = X_USER_NAME,
155 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
158 SOURCE_LANG = userenv('LANG')
159 where SET_CODE = X_SET_CODE
160 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
161
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165 end UPDATE_ROW;
166
167 procedure DELETE_ROW (
168 X_SET_CODE in VARCHAR2
169 ) is
170 begin
171 delete from BNE_PERF_SETS_TL
172 where SET_CODE = X_SET_CODE;
173
174 if (sql%notfound) then
175 raise no_data_found;
176 end if;
177
178 delete from BNE_PERF_SETS_B
179 where SET_CODE = X_SET_CODE;
180
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184 end DELETE_ROW;
185
186 procedure ADD_LANGUAGE
187 is
188 begin
189 delete from BNE_PERF_SETS_TL T
190 where not exists
191 (select NULL
192 from BNE_PERF_SETS_B B
193 where B.SET_CODE = T.SET_CODE
194 );
195
196 update BNE_PERF_SETS_TL T set (
197 USER_NAME
198 ) = (select
199 B.USER_NAME
200 from BNE_PERF_SETS_TL B
201 where B.SET_CODE = T.SET_CODE
202 and B.LANGUAGE = T.SOURCE_LANG)
203 where (
204 T.SET_CODE,
205 T.LANGUAGE
206 ) in (select
207 SUBT.SET_CODE,
208 SUBT.LANGUAGE
209 from BNE_PERF_SETS_TL SUBB, BNE_PERF_SETS_TL SUBT
210 where SUBB.SET_CODE = SUBT.SET_CODE
211 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
212 and (SUBB.USER_NAME <> SUBT.USER_NAME
213 ));
214
215 insert into BNE_PERF_SETS_TL (
216 SET_CODE,
217 USER_NAME,
218 CREATED_BY,
219 CREATION_DATE,
220 LAST_UPDATED_BY,
221 LAST_UPDATE_LOGIN,
222 LAST_UPDATE_DATE,
223 LANGUAGE,
224 SOURCE_LANG
225 ) select /*+ ORDERED */
226 B.SET_CODE,
227 B.USER_NAME,
228 B.CREATED_BY,
229 B.CREATION_DATE,
230 B.LAST_UPDATED_BY,
231 B.LAST_UPDATE_LOGIN,
232 B.LAST_UPDATE_DATE,
233 L.LANGUAGE_CODE,
234 B.SOURCE_LANG
235 from BNE_PERF_SETS_TL B, FND_LANGUAGES L
236 where L.INSTALLED_FLAG in ('I', 'B')
237 and B.LANGUAGE = userenv('LANG')
238 and not exists
239 (select NULL
240 from BNE_PERF_SETS_TL T
241 where T.SET_CODE = B.SET_CODE
242 and T.LANGUAGE = L.LANGUAGE_CODE);
243 end ADD_LANGUAGE;
244
245 --------------------------------------------------------------------------------
246 -- PROCEDURE: TRANSLATE_ROW --
247 -- --
248 -- DESCRIPTION: Load a translation into the BNE_PERF_SETS entity. --
249 -- This proc is called from the apps loader. --
250 -- --
251 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
252 -- --
253 -- --
254 -- MODIFICATION HISTORY --
255 -- Date Username Description --
256 -- 28-May-04 DGROVES CREATED --
257 --------------------------------------------------------------------------------
258 procedure TRANSLATE_ROW(
259 x_set_code in VARCHAR2,
260 x_user_name in VARCHAR2,
261 x_owner in VARCHAR2,
262 x_last_update_date in VARCHAR2,
263 x_custom_mode in VARCHAR2
264 )
265 is
266 f_luby number; -- entity owner in file
267 f_ludate date; -- entity update date in file
268 db_luby number; -- entity owner in db
269 db_ludate date; -- entity update date in db
270 begin
271 -- Translate owner to file_last_updated_by
272 f_luby := fnd_load_util.owner_id(x_owner);
273
274 -- Translate char last_update_date to date
275 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
276 begin
277 select LAST_UPDATED_BY, LAST_UPDATE_DATE
278 into db_luby, db_ludate
279 from BNE_PERF_SETS_TL
280 where SET_CODE = x_set_code
281 and LANGUAGE = userenv('LANG');
282
283 -- Test for customization and version
284 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
285 db_ludate, x_custom_mode)) then
286
287 update BNE_PERF_SETS_TL
288 set USER_NAME = x_user_name,
289 LAST_UPDATE_DATE = f_ludate,
290 LAST_UPDATED_BY = f_luby,
291 LAST_UPDATE_LOGIN = 0,
292 SOURCE_LANG = userenv('LANG')
293 where SET_CODE = x_set_code
294 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
295 ;
296 end if;
297 exception
298 when no_data_found then
299 -- Do not insert missing translations, skip this row
300 null;
301 end;
302 end TRANSLATE_ROW;
303
304 --------------------------------------------------------------------------------
305 -- PROCEDURE: LOAD_ROW --
306 -- --
307 -- DESCRIPTION: Load a row into the BNE_PERF_SETS 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 -- 28-May-04 DGROVES CREATED --
316 --------------------------------------------------------------------------------
317 procedure LOAD_ROW(
318 x_set_code in VARCHAR2,
319 x_object_version_number in VARCHAR2,
320 x_enabled_flag in VARCHAR2,
321 x_user_name in VARCHAR2,
322 x_owner in VARCHAR2,
323 x_last_update_date in VARCHAR2,
324 x_custom_mode in VARCHAR2
325 )
326 is
327 l_row_id varchar2(64);
328 f_luby number; -- entity owner in file
329 f_ludate date; -- entity update date in file
330 db_luby number; -- entity owner in db
331 db_ludate date; -- entity update date in db
332 begin
333 -- Translate owner to file_last_updated_by
334 f_luby := fnd_load_util.owner_id(x_owner);
335
336 -- Translate char last_update_date to date
337 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
338 begin
339 select LAST_UPDATED_BY, LAST_UPDATE_DATE
340 into db_luby, db_ludate
341 from BNE_PERF_SETS_B
342 where SET_CODE = x_set_code;
343
344 -- Test for customization and version
345 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
346 db_ludate, x_custom_mode)) then
347 -- Update existing row
348 BNE_PERF_SETS_PKG.Update_Row(
349 X_SET_CODE => x_set_code,
350 X_OBJECT_VERSION_NUMBER => x_object_version_number,
351 X_ENABLED_FLAG => x_enabled_flag,
352 X_USER_NAME => x_user_name,
353 X_LAST_UPDATE_DATE => f_ludate,
354 X_LAST_UPDATED_BY => f_luby,
355 X_LAST_UPDATE_LOGIN => 0
356 );
357
358 end if;
359 exception
360 when no_data_found then
361 -- Record doesn't exist - insert in all cases
365 X_OBJECT_VERSION_NUMBER => x_object_version_number,
362 BNE_PERF_SETS_PKG.Insert_Row(
363 X_ROWID => l_row_id,
364 X_SET_CODE => x_set_code,
366 X_ENABLED_FLAG => x_enabled_flag,
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 end BNE_PERF_SETS_PKG;