[Home] [Help]
PACKAGE BODY: APPS.EDR_IDX_XML_ELEMENT_PKG
Source
1 package body EDR_IDX_XML_ELEMENT_PKG as
2 /* $Header: EDRGMLB.pls 120.2.12000000.1 2007/01/18 05:53:38 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ELEMENT_ID in NUMBER,
6 X_XML_ELEMENT in VARCHAR2,
7 X_DTD_ROOT_ELEMENT in VARCHAR2,
8 X_APPLICATION_ID in NUMBER,
9 X_INDEX_SECTION_NAME in VARCHAR2,
10 X_INDEX_TAG in VARCHAR2,
11 X_STATUS in CHAR,
12 X_CREATED_BY in NUMBER,
13 X_CREATION_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATE_LOGIN in NUMBER,
17 X_DISPLAY_NAME in VARCHAR2,
18 X_DESCRIPTION in VARCHAR2
19 ) is
20 cursor C is select ROWID from EDR_IDX_XML_ELEMENT_B
21 where ELEMENT_ID = X_ELEMENT_ID
22 ;
23 begin
24 --Bug 3783242 : Start
25 --comment the original insert statements
26 --and calling the new insert_row proc instead
27 /*
28 insert into EDR_IDX_XML_ELEMENT_B (
29 ELEMENT_ID,
30 XML_ELEMENT,
31 DTD_ROOT_ELEMENT,
32 APPLICATION_ID,
33 INDEX_SECTION_NAME,
34 INDEX_TAG,
35 STATUS,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_ELEMENT_ID,
43 X_XML_ELEMENT,
44 X_DTD_ROOT_ELEMENT,
45 X_APPLICATION_ID,
46 X_INDEX_SECTION_NAME,
47 X_INDEX_TAG,
48 X_STATUS,
49 X_CREATION_DATE,
50 X_CREATED_BY,
51 X_LAST_UPDATE_DATE,
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_LOGIN
54 );
55
56 insert into EDR_IDX_XML_ELEMENT_TL (
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN,
60 CREATED_BY,
61 CREATION_DATE,
62 ELEMENT_ID,
63 DISPLAY_NAME,
64 DESCRIPTION,
65 LANGUAGE,
66 SOURCE_LANG
67 ) select
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_CREATED_BY,
72 X_CREATION_DATE,
73 X_ELEMENT_ID,
74 X_DISPLAY_NAME,
75 X_DESCRIPTION,
76 L.LANGUAGE_CODE,
77 userenv('LANG')
78 from FND_LANGUAGES L
79 where L.INSTALLED_FLAG in ('I', 'B')
80 and not exists
81 (select NULL
82 from EDR_IDX_XML_ELEMENT_TL T
83 where T.ELEMENT_ID = X_ELEMENT_ID
84 and T.LANGUAGE = L.LANGUAGE_CODE);
85 */
86 INSERT_ROW
87 (X_ELEMENT_ID => x_element_id,
88 X_XML_ELEMENT => x_xml_element,
89 X_DTD_ROOT_ELEMENT => x_dtd_root_element,
90 X_APPLICATION_ID => x_application_id,
91 X_INDEX_TAG => x_index_tag,
92 X_STATUS => x_status,
93 X_CREATED_BY => x_created_by,
94 X_CREATION_DATE => x_creation_date,
95 X_LAST_UPDATED_BY => x_last_updated_by,
96 X_LAST_UPDATE_DATE => x_last_update_date,
97 X_LAST_UPDATE_LOGIN => x_last_update_login,
98 X_DISPLAY_NAME => x_display_name,
99 X_DESCRIPTION => x_description);
100 --Bug 3783242 : End
101
102 open c;
103 fetch c into X_ROWID;
104 if (c%notfound) then
105 close c;
106 raise no_data_found;
107 end if;
108 close c;
109 end INSERT_ROW;
110
111 --Bug 3783242 : Start
112 procedure INSERT_ROW (
113 X_ELEMENT_ID in NUMBER,
114 X_XML_ELEMENT in VARCHAR2,
115 X_DTD_ROOT_ELEMENT in VARCHAR2,
116 X_APPLICATION_ID in NUMBER,
117 X_INDEX_TAG in VARCHAR2,
118 X_STATUS in CHAR,
119 X_CREATED_BY in NUMBER,
120 X_CREATION_DATE in DATE,
121 X_LAST_UPDATED_BY in NUMBER,
122 X_LAST_UPDATE_DATE in DATE,
123 X_LAST_UPDATE_LOGIN in NUMBER,
124 X_DISPLAY_NAME in VARCHAR2,
125 X_DESCRIPTION in VARCHAR2
126 ) is
127
128 L_INDEX_SECTION_NAME VARCHAR2(30);
129
130 --Define a cursor to query the indexed xml elements table based
131 --on the display name.
132 cursor C1(P_DISPLAY_NAME VARCHAR2) is
133 select distinct index_section_name
134 from edr_idx_xml_element_vl
135 where display_name = P_DISPLAY_NAME;
136
137 begin
138 open C1(X_DISPLAY_NAME);
139 fetch C1 into L_INDEX_SECTION_NAME;
140 if (C1%notfound) then
141 --Since no record was found in the cursor, assign the index section name with
142 --specified element id
143 L_INDEX_SECTION_NAME := 'S'||X_ELEMENT_ID;
144 end if;
145 --If a record was found, then the existing section name gets reused.
146 close C1;
147
148 insert into EDR_IDX_XML_ELEMENT_B (
149 ELEMENT_ID,
150 XML_ELEMENT,
151 DTD_ROOT_ELEMENT,
152 APPLICATION_ID,
153 INDEX_SECTION_NAME,
154 INDEX_TAG,
155 STATUS,
156 CREATION_DATE,
157 CREATED_BY,
158 LAST_UPDATE_DATE,
159 LAST_UPDATED_BY,
160 LAST_UPDATE_LOGIN
161 ) values (
162 X_ELEMENT_ID,
163 X_XML_ELEMENT,
164 X_DTD_ROOT_ELEMENT,
165 X_APPLICATION_ID,
166 L_INDEX_SECTION_NAME,
167 X_INDEX_TAG,
168 X_STATUS,
169 X_CREATION_DATE,
170 X_CREATED_BY,
171 X_LAST_UPDATE_DATE,
172 X_LAST_UPDATED_BY,
173 X_LAST_UPDATE_LOGIN
174 );
175
176 insert into EDR_IDX_XML_ELEMENT_TL (
177 LAST_UPDATE_DATE,
178 LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN,
180 CREATED_BY,
181 CREATION_DATE,
182 ELEMENT_ID,
183 DISPLAY_NAME,
184 DESCRIPTION,
185 LANGUAGE,
186 SOURCE_LANG
187 ) select
188 X_LAST_UPDATE_DATE,
189 X_LAST_UPDATED_BY,
190 X_LAST_UPDATE_LOGIN,
191 X_CREATED_BY,
192 X_CREATION_DATE,
193 X_ELEMENT_ID,
194 X_DISPLAY_NAME,
195 X_DESCRIPTION,
196 L.LANGUAGE_CODE,
197 userenv('LANG')
198 from FND_LANGUAGES L
199 where L.INSTALLED_FLAG in ('I', 'B')
200 and not exists
201 (select NULL
202 from EDR_IDX_XML_ELEMENT_TL T
203 where T.ELEMENT_ID = X_ELEMENT_ID
204 and T.LANGUAGE = L.LANGUAGE_CODE);
205 end INSERT_ROW;
206 --Bug 3783242 : End
207
208 procedure LOCK_ROW (
209 X_ELEMENT_ID in NUMBER,
210 X_XML_ELEMENT in VARCHAR2,
211 X_DTD_ROOT_ELEMENT in VARCHAR2,
212 X_APPLICATION_ID in NUMBER,
213 X_INDEX_SECTION_NAME in VARCHAR2,
214 X_INDEX_TAG in VARCHAR2,
215 X_STATUS in CHAR,
216 X_DISPLAY_NAME in VARCHAR2,
217 X_DESCRIPTION in VARCHAR2
218 ) is
219 cursor c is select
220 XML_ELEMENT,
221 DTD_ROOT_ELEMENT,
222 APPLICATION_ID,
223 INDEX_SECTION_NAME,
224 INDEX_TAG,
225 STATUS
226 from EDR_IDX_XML_ELEMENT_B
227 where ELEMENT_ID = X_ELEMENT_ID
228 for update of ELEMENT_ID nowait;
229 recinfo c%rowtype;
230
231 cursor c1 is select
232 DISPLAY_NAME,
233 DESCRIPTION,
234 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
235 from EDR_IDX_XML_ELEMENT_TL
236 where ELEMENT_ID = X_ELEMENT_ID
237 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
238 for update of ELEMENT_ID nowait;
239 begin
240 open c;
241 fetch c into recinfo;
242 if (c%notfound) then
243 close c;
244 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
245 app_exception.raise_exception;
246 end if;
247 close c;
248 if ( (recinfo.XML_ELEMENT = X_XML_ELEMENT)
249 AND ((recinfo.DTD_ROOT_ELEMENT = X_DTD_ROOT_ELEMENT)
250 OR ((recinfo.DTD_ROOT_ELEMENT is null) AND (X_DTD_ROOT_ELEMENT is null)))
251 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
252 AND (recinfo.INDEX_SECTION_NAME = X_INDEX_SECTION_NAME)
253 AND (recinfo.INDEX_TAG = X_INDEX_TAG)
254 AND (recinfo.STATUS = X_STATUS)
255 ) then
256 null;
257 else
258 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
259 app_exception.raise_exception;
260 end if;
261
262 for tlinfo in c1 loop
263 if (tlinfo.BASELANG = 'Y') then
264 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
265 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
266 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
267 ) then
268 null;
269 else
270 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
271 app_exception.raise_exception;
272 end if;
273 end if;
274 end loop;
275 return;
276 end LOCK_ROW;
277
278 procedure UPDATE_ROW (
279 X_ELEMENT_ID in NUMBER,
280 X_XML_ELEMENT in VARCHAR2,
281 X_DTD_ROOT_ELEMENT in VARCHAR2,
282 X_APPLICATION_ID in NUMBER,
283 X_INDEX_SECTION_NAME in VARCHAR2,
284 X_INDEX_TAG in VARCHAR2,
285 X_STATUS in CHAR,
286 X_LAST_UPDATE_DATE in DATE,
287 X_LAST_UPDATED_BY in NUMBER,
288 X_LAST_UPDATE_LOGIN in NUMBER,
289 X_DISPLAY_NAME in VARCHAR2,
290 X_DESCRIPTION in VARCHAR2
291 ) is
292 begin
293 update EDR_IDX_XML_ELEMENT_B set
294 XML_ELEMENT = X_XML_ELEMENT,
295 DTD_ROOT_ELEMENT = X_DTD_ROOT_ELEMENT,
296 APPLICATION_ID = X_APPLICATION_ID,
297 INDEX_SECTION_NAME = X_INDEX_SECTION_NAME,
298 INDEX_TAG = X_INDEX_TAG,
299 STATUS = X_STATUS,
300 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
301 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
302 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
303 where ELEMENT_ID = X_ELEMENT_ID;
304
305 if (sql%notfound) then
306 raise no_data_found;
307 end if;
308
309 update EDR_IDX_XML_ELEMENT_TL set
310 DISPLAY_NAME = X_DISPLAY_NAME,
311 DESCRIPTION = X_DESCRIPTION,
312 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
313 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
314 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
315 SOURCE_LANG = userenv('LANG')
316 where ELEMENT_ID = X_ELEMENT_ID
317 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
318
319 if (sql%notfound) then
320 --Bug 4417387: Start
321 --raise no_data_found;
322 insert into EDR_IDX_XML_ELEMENT_TL (
323 LAST_UPDATE_DATE,
324 LAST_UPDATED_BY,
325 LAST_UPDATE_LOGIN,
326 CREATED_BY,
327 CREATION_DATE,
328 ELEMENT_ID,
329 DISPLAY_NAME,
330 DESCRIPTION,
331 LANGUAGE,
332 SOURCE_LANG
333 ) select
334 X_LAST_UPDATE_DATE,
335 X_LAST_UPDATED_BY,
336 X_LAST_UPDATE_LOGIN,
337 X_LAST_UPDATED_BY, -- 'created by' same as 'last updated by' in this case
338 X_LAST_UPDATE_DATE, -- 'creation date' same as 'last update date' in this case
339 X_ELEMENT_ID,
340 X_DISPLAY_NAME,
341 X_DESCRIPTION,
342 L.LANGUAGE_CODE,
343 userenv('LANG')
344 from FND_LANGUAGES L
345 where L.INSTALLED_FLAG in ('I', 'B')
346 and not exists
347 (select NULL
348 from EDR_IDX_XML_ELEMENT_TL T
349 where T.ELEMENT_ID = X_ELEMENT_ID
350 and T.LANGUAGE = L.LANGUAGE_CODE);
351 --Bug 4417387: End
352 end if;
353
354 end UPDATE_ROW;
355
356 procedure DELETE_ROW (
357 X_ELEMENT_ID in NUMBER
358 ) is
359 begin
360 delete from EDR_IDX_XML_ELEMENT_TL
361 where ELEMENT_ID = X_ELEMENT_ID;
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366
367 delete from EDR_IDX_XML_ELEMENT_B
368 where ELEMENT_ID = X_ELEMENT_ID;
369
370 if (sql%notfound) then
371 raise no_data_found;
372 end if;
373 end DELETE_ROW;
374
375 procedure ADD_LANGUAGE
376 is
377 begin
378 delete from EDR_IDX_XML_ELEMENT_TL T
379 where not exists
380 (select NULL
381 from EDR_IDX_XML_ELEMENT_B B
382 where B.ELEMENT_ID = T.ELEMENT_ID
383 );
384
385 update EDR_IDX_XML_ELEMENT_TL T set (
386 DISPLAY_NAME,
387 DESCRIPTION
388 ) = (select
389 B.DISPLAY_NAME,
390 B.DESCRIPTION
391 from EDR_IDX_XML_ELEMENT_TL B
392 where B.ELEMENT_ID = T.ELEMENT_ID
393 and B.LANGUAGE = T.SOURCE_LANG)
394 where (
395 T.ELEMENT_ID,
396 T.LANGUAGE
397 ) in (select
398 SUBT.ELEMENT_ID,
399 SUBT.LANGUAGE
400 from EDR_IDX_XML_ELEMENT_TL SUBB, EDR_IDX_XML_ELEMENT_TL SUBT
401 where SUBB.ELEMENT_ID = SUBT.ELEMENT_ID
402 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
403 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
404 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
405 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
406 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
407 ));
408
409 insert into EDR_IDX_XML_ELEMENT_TL (
410 LAST_UPDATE_DATE,
411 LAST_UPDATED_BY,
412 LAST_UPDATE_LOGIN,
413 CREATED_BY,
414 CREATION_DATE,
415 ELEMENT_ID,
416 DISPLAY_NAME,
417 DESCRIPTION,
418 LANGUAGE,
419 SOURCE_LANG
420 ) select
421 B.LAST_UPDATE_DATE,
422 B.LAST_UPDATED_BY,
423 B.LAST_UPDATE_LOGIN,
424 B.CREATED_BY,
425 B.CREATION_DATE,
426 B.ELEMENT_ID,
427 B.DISPLAY_NAME,
428 B.DESCRIPTION,
429 L.LANGUAGE_CODE,
430 B.SOURCE_LANG
431 from EDR_IDX_XML_ELEMENT_TL B, FND_LANGUAGES L
432 where L.INSTALLED_FLAG in ('I', 'B')
433 and B.LANGUAGE = userenv('LANG')
434 and not exists
435 (select NULL
436 from EDR_IDX_XML_ELEMENT_TL T
437 where T.ELEMENT_ID = B.ELEMENT_ID
438 and T.LANGUAGE = L.LANGUAGE_CODE);
439 end ADD_LANGUAGE;
440
441 end EDR_IDX_XML_ELEMENT_PKG;