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