[Home] [Help]
PACKAGE BODY: APPS.BNE_QUERIES_PKG
Source
1 package body BNE_QUERIES_PKG as
2 /* $Header: bnequeriesb.pls 120.2 2005/06/29 03:40:48 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_QUERY_CODE in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_QUERY_CLASS in VARCHAR2,
10 X_USER_NAME in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER,
16 X_DIRECTIVE_APP_ID in NUMBER DEFAULT NULL,
17 X_DIRECTIVE_CODE in VARCHAR2 DEFAULT NULL
18 ) is
19 cursor C is select ROWID from BNE_QUERIES_B
20 where APPLICATION_ID = X_APPLICATION_ID
21 and QUERY_CODE = X_QUERY_CODE
22 ;
23 begin
24 insert into BNE_QUERIES_B (
25 APPLICATION_ID,
26 QUERY_CODE,
27 OBJECT_VERSION_NUMBER,
28 QUERY_CLASS,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN,
34 DIRECTIVE_APP_ID,
35 DIRECTIVE_CODE
36 ) values (
37 X_APPLICATION_ID,
38 X_QUERY_CODE,
39 X_OBJECT_VERSION_NUMBER,
40 X_QUERY_CLASS,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN,
46 X_DIRECTIVE_APP_ID,
47 X_DIRECTIVE_CODE
48 );
49
50 insert into BNE_QUERIES_TL (
51 APPLICATION_ID,
52 QUERY_CODE,
53 USER_NAME,
54 CREATED_BY,
55 CREATION_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN,
58 LAST_UPDATE_DATE,
59 LANGUAGE,
60 SOURCE_LANG
61 ) select
62 X_APPLICATION_ID,
63 X_QUERY_CODE,
64 X_USER_NAME,
65 X_CREATED_BY,
66 X_CREATION_DATE,
67 X_LAST_UPDATED_BY,
68 X_LAST_UPDATE_LOGIN,
69 X_LAST_UPDATE_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_QUERIES_TL T
77 where T.APPLICATION_ID = X_APPLICATION_ID
78 and T.QUERY_CODE = X_QUERY_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_QUERY_CODE in VARCHAR2,
94 X_OBJECT_VERSION_NUMBER in NUMBER,
95 X_QUERY_CLASS in VARCHAR2,
96 X_USER_NAME in VARCHAR2,
97 X_DIRECTIVE_APP_ID in NUMBER DEFAULT NULL,
98 X_DIRECTIVE_CODE in VARCHAR2 DEFAULT NULL
99 ) is
100 cursor c is select
101 OBJECT_VERSION_NUMBER,
102 QUERY_CLASS,
103 DIRECTIVE_APP_ID,
104 DIRECTIVE_CODE
105 from BNE_QUERIES_B
106 where APPLICATION_ID = X_APPLICATION_ID
107 and QUERY_CODE = X_QUERY_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_QUERIES_TL
115 where APPLICATION_ID = X_APPLICATION_ID
116 and QUERY_CODE = X_QUERY_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.QUERY_CLASS = X_QUERY_CLASS)
130 AND ((recinfo.DIRECTIVE_APP_ID = X_DIRECTIVE_APP_ID)
131 OR ((recinfo.DIRECTIVE_APP_ID is null) AND (X_DIRECTIVE_APP_ID is null)))
132 AND ((recinfo.DIRECTIVE_CODE = X_DIRECTIVE_CODE)
133 OR ((recinfo.DIRECTIVE_CODE is null) AND (X_DIRECTIVE_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 OR ((tlinfo.USER_NAME is null) AND (X_USER_NAME is null)))
145 ) then
146 null;
147 else
148 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
149 app_exception.raise_exception;
150 end if;
151 end if;
152 end loop;
153 return;
154 end LOCK_ROW;
155
156 procedure UPDATE_ROW (
157 X_APPLICATION_ID in NUMBER,
158 X_QUERY_CODE in VARCHAR2,
159 X_OBJECT_VERSION_NUMBER in NUMBER,
160 X_QUERY_CLASS in VARCHAR2,
161 X_USER_NAME in VARCHAR2,
162 X_LAST_UPDATE_DATE in DATE,
163 X_LAST_UPDATED_BY in NUMBER,
164 X_LAST_UPDATE_LOGIN in NUMBER,
165 X_DIRECTIVE_APP_ID in NUMBER DEFAULT NULL,
166 X_DIRECTIVE_CODE in VARCHAR2 DEFAULT NULL
167 ) is
168 begin
169 update BNE_QUERIES_B set
170 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
171 QUERY_CLASS = X_QUERY_CLASS,
172 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
173 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
174 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
175 DIRECTIVE_APP_ID = X_DIRECTIVE_APP_ID,
176 DIRECTIVE_CODE = X_DIRECTIVE_CODE
177 where APPLICATION_ID = X_APPLICATION_ID
178 and QUERY_CODE = X_QUERY_CODE;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183
184 update BNE_QUERIES_TL set
185 USER_NAME = X_USER_NAME,
186 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
187 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
188 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
189 SOURCE_LANG = userenv('LANG')
190 where APPLICATION_ID = X_APPLICATION_ID
191 and QUERY_CODE = X_QUERY_CODE
192 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end UPDATE_ROW;
198
199 procedure DELETE_ROW (
200 X_APPLICATION_ID in NUMBER,
201 X_QUERY_CODE in VARCHAR2
202 ) is
203 begin
204 delete from BNE_QUERIES_TL
205 where APPLICATION_ID = X_APPLICATION_ID
206 and QUERY_CODE = X_QUERY_CODE;
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211
212 delete from BNE_QUERIES_B
213 where APPLICATION_ID = X_APPLICATION_ID
214 and QUERY_CODE = X_QUERY_CODE;
215
216 if (sql%notfound) then
217 raise no_data_found;
218 end if;
219 end DELETE_ROW;
220
221 procedure ADD_LANGUAGE
222 is
223 begin
224 delete from BNE_QUERIES_TL T
225 where not exists
226 (select NULL
227 from BNE_QUERIES_B B
228 where B.APPLICATION_ID = T.APPLICATION_ID
229 and B.QUERY_CODE = T.QUERY_CODE
230 );
231
232 update BNE_QUERIES_TL T set (
233 USER_NAME
234 ) = (select
235 B.USER_NAME
236 from BNE_QUERIES_TL B
237 where B.APPLICATION_ID = T.APPLICATION_ID
238 and B.QUERY_CODE = T.QUERY_CODE
239 and B.LANGUAGE = T.SOURCE_LANG)
240 where (
241 T.APPLICATION_ID,
242 T.QUERY_CODE,
243 T.LANGUAGE
244 ) in (select
245 SUBT.APPLICATION_ID,
246 SUBT.QUERY_CODE,
247 SUBT.LANGUAGE
248 from BNE_QUERIES_TL SUBB, BNE_QUERIES_TL SUBT
249 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
250 and SUBB.QUERY_CODE = SUBT.QUERY_CODE
251 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
252 and (SUBB.USER_NAME <> SUBT.USER_NAME
253 or (SUBB.USER_NAME is null and SUBT.USER_NAME is not null)
254 or (SUBB.USER_NAME is not null and SUBT.USER_NAME is null)
255 ));
256
257 insert into BNE_QUERIES_TL (
258 APPLICATION_ID,
259 QUERY_CODE,
260 USER_NAME,
261 CREATED_BY,
262 CREATION_DATE,
263 LAST_UPDATED_BY,
264 LAST_UPDATE_LOGIN,
265 LAST_UPDATE_DATE,
266 LANGUAGE,
267 SOURCE_LANG
268 ) select /*+ ORDERED */
269 B.APPLICATION_ID,
270 B.QUERY_CODE,
271 B.USER_NAME,
272 B.CREATED_BY,
273 B.CREATION_DATE,
274 B.LAST_UPDATED_BY,
275 B.LAST_UPDATE_LOGIN,
276 B.LAST_UPDATE_DATE,
277 L.LANGUAGE_CODE,
278 B.SOURCE_LANG
279 from BNE_QUERIES_TL B, FND_LANGUAGES L
280 where L.INSTALLED_FLAG in ('I', 'B')
281 and B.LANGUAGE = userenv('LANG')
282 and not exists
283 (select NULL
284 from BNE_QUERIES_TL T
285 where T.APPLICATION_ID = B.APPLICATION_ID
286 and T.QUERY_CODE = B.QUERY_CODE
287 and T.LANGUAGE = L.LANGUAGE_CODE);
288 end ADD_LANGUAGE;
289
290
291 --------------------------------------------------------------------------------
292 -- PROCEDURE: TRANSLATE_ROW --
293 -- --
294 -- DESCRIPTION: Load a translation into the BNE_QUERIES entity. --
295 -- This proc is called from the apps loader. --
296 -- --
297 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
298 -- --
299 -- --
300 -- MODIFICATION HISTORY --
301 -- Date Username Description --
302 -- 21-Apr-04 DGROVES CREATED --
303 --------------------------------------------------------------------------------
304 procedure TRANSLATE_ROW(
305 x_query_asn IN VARCHAR2,
306 x_query_code IN VARCHAR2,
307 x_user_name IN VARCHAR2,
308 x_owner IN VARCHAR2,
309 x_last_update_date IN VARCHAR2,
310 x_custom_mode IN VARCHAR2
311 )
312 is
313 l_app_id number;
314 f_luby number; -- entity owner in file
315 f_ludate date; -- entity update date in file
316 db_luby number; -- entity owner in db
317 db_ludate date; -- entity update date in db
318 begin
319 -- translate values to IDs
320 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_query_asn);
321
322 -- Translate owner to file_last_updated_by
323 f_luby := fnd_load_util.owner_id(x_owner);
324
325 -- Translate char last_update_date to date
326 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
327 begin
328 select LAST_UPDATED_BY, LAST_UPDATE_DATE
329 into db_luby, db_ludate
330 from BNE_QUERIES_TL
331 where APPLICATION_ID = l_app_id
332 and QUERY_CODE = x_query_code
333 and LANGUAGE = userenv('LANG');
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
339 update BNE_QUERIES_TL
340 set USER_NAME = x_user_name,
341 LAST_UPDATE_DATE = f_ludate,
342 LAST_UPDATED_BY = f_luby,
343 LAST_UPDATE_LOGIN = 0,
344 SOURCE_LANG = userenv('LANG')
345 where APPLICATION_ID = l_app_id
346 AND QUERY_CODE = x_query_code
347 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
348 ;
349 end if;
350 exception
351 when no_data_found then
352 -- Do not insert missing translations, skip this row
353 null;
354 end;
355 end TRANSLATE_ROW;
356
357
358 --------------------------------------------------------------------------------
359 -- PROCEDURE: LOAD_ROW --
360 -- --
361 -- DESCRIPTION: Load a row into the BNE_QUERIES entity. --
362 -- This proc is called from the apps loader. --
363 -- --
364 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
365 -- --
366 -- --
367 -- MODIFICATION HISTORY --
368 -- Date Username Description --
369 -- 21-Apr-04 DGROVES CREATED --
370 --------------------------------------------------------------------------------
371 procedure LOAD_ROW(
372 x_query_asn IN VARCHAR2,
373 x_query_code IN VARCHAR2,
374 x_object_version_number IN VARCHAR2,
375 x_query_class IN VARCHAR2,
376 x_user_name IN VARCHAR2,
377 x_owner IN VARCHAR2,
378 x_last_update_date IN VARCHAR2,
379 x_custom_mode IN VARCHAR2,
380 x_directive_asn IN VARCHAR2 DEFAULT NULL,
381 x_directive_code IN VARCHAR2 DEFAULT NULL
382 )
383 is
384 l_app_id number;
385 l_directive_app_id number;
386 l_row_id varchar2(64);
387 f_luby number; -- entity owner in file
388 f_ludate date; -- entity update date in file
389 db_luby number; -- entity owner in db
390 db_ludate date; -- entity update date in db
391 begin
392 -- translate values to IDs
393 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_query_asn);
394 l_directive_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_directive_asn);
395
396 -- Translate owner to file_last_updated_by
397 f_luby := fnd_load_util.owner_id(x_owner);
398
399 -- Translate char last_update_date to date
400 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
401 begin
402 select LAST_UPDATED_BY, LAST_UPDATE_DATE
403 into db_luby, db_ludate
404 from BNE_QUERIES_B
405 where APPLICATION_ID = l_app_id
406 and QUERY_CODE = x_query_code;
407
408 -- Test for customization and version
409 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
410 db_ludate, x_custom_mode)) then
411 -- Update existing row
412 BNE_QUERIES_PKG.Update_Row(
413 X_APPLICATION_ID => l_app_id,
414 X_QUERY_CODE => x_query_code,
415 X_OBJECT_VERSION_NUMBER => x_object_version_number,
416 X_QUERY_CLASS => x_query_class,
417 X_USER_NAME => x_user_name,
418 X_LAST_UPDATE_DATE => f_ludate,
419 X_LAST_UPDATED_BY => f_luby,
420 X_LAST_UPDATE_LOGIN => 0,
421 X_DIRECTIVE_APP_ID => l_directive_app_id,
422 X_DIRECTIVE_CODE => x_directive_code
423 );
424
425 end if;
426 exception
427 when no_data_found then
428 -- Record doesn't exist - insert in all cases
429 BNE_QUERIES_PKG.Insert_Row(
430 X_ROWID => l_row_id,
431 X_APPLICATION_ID => l_app_id,
432 X_QUERY_CODE => x_query_code,
433 X_OBJECT_VERSION_NUMBER => x_object_version_number,
434 X_QUERY_CLASS => x_query_class,
435 X_USER_NAME => x_user_name,
436 X_CREATION_DATE => f_ludate,
437 X_CREATED_BY => f_luby,
438 X_LAST_UPDATE_DATE => f_ludate,
439 X_LAST_UPDATED_BY => f_luby,
440 X_LAST_UPDATE_LOGIN => 0,
441 X_DIRECTIVE_APP_ID => l_directive_app_id,
442 X_DIRECTIVE_CODE => x_directive_code
443 );
444 end;
445 end LOAD_ROW;
446
447
448 end BNE_QUERIES_PKG;