1 package body QP_SEGMENTS_PKG as
2 /* $Header: QPXSEGMB.pls 120.1 2005/06/09 03:04:44 appldev $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_SEGMENT_ID in NUMBER,
6 X_ATTRIBUTE12 in VARCHAR2,
7 X_ATTRIBUTE13 in VARCHAR2,
8 X_ATTRIBUTE14 in VARCHAR2,
9 X_ATTRIBUTE8 in VARCHAR2,
10 X_ATTRIBUTE9 in VARCHAR2,
11 X_ATTRIBUTE10 in VARCHAR2,
12 X_ATTRIBUTE11 in VARCHAR2,
13 X_SEGMENT_CODE in VARCHAR2,
14 X_PRC_CONTEXT_ID in NUMBER,
15 X_AVAILABILITY_IN_BASIC in VARCHAR2,
16 X_SEGMENT_MAPPING_COLUMN in VARCHAR2,
17 X_SEEDED_FLAG in VARCHAR2,
18 X_APPLICATION_ID in NUMBER,
19 X_SEEDED_PRECEDENCE in NUMBER,
20 X_USER_PRECEDENCE in NUMBER,
21 X_SEEDED_VALUESET_ID in NUMBER,
22 X_USER_VALUESET_ID in NUMBER,
23 X_SEEDED_FORMAT_TYPE in VARCHAR2,
24 X_USER_FORMAT_TYPE in VARCHAR2,
25 X_CONTEXT in VARCHAR2,
26 X_ATTRIBUTE1 in VARCHAR2,
27 X_ATTRIBUTE2 in VARCHAR2,
28 X_ATTRIBUTE3 in VARCHAR2,
29 X_ATTRIBUTE4 in VARCHAR2,
30 X_ATTRIBUTE5 in VARCHAR2,
31 X_ATTRIBUTE6 in VARCHAR2,
32 X_ATTRIBUTE7 in VARCHAR2,
33 X_ATTRIBUTE15 in VARCHAR2,
34 X_SEEDED_SEGMENT_NAME in VARCHAR2,
35 X_USER_SEGMENT_NAME in VARCHAR2,
36 X_SEEDED_DESCRIPTION in VARCHAR2,
37 X_USER_DESCRIPTION in VARCHAR2,
38 X_REQUIRED_FLAG in VARCHAR2,
39 X_CREATION_DATE in DATE,
40 X_CREATED_BY in NUMBER,
41 X_LAST_UPDATE_DATE in DATE,
42 X_LAST_UPDATED_BY in NUMBER,
43 X_LAST_UPDATE_LOGIN in NUMBER
44 ) is
45 cursor C is select ROWID from QP_SEGMENTS_B
46 where SEGMENT_ID = X_SEGMENT_ID
47 ;
48 begin
49 insert into QP_SEGMENTS_B (
50 ATTRIBUTE12,
51 ATTRIBUTE13,
52 ATTRIBUTE14,
53 ATTRIBUTE8,
54 ATTRIBUTE9,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 SEGMENT_ID,
58 SEGMENT_CODE,
59 PRC_CONTEXT_ID,
60 AVAILABILITY_IN_BASIC,
61 SEGMENT_MAPPING_COLUMN,
62 SEEDED_FLAG,
63 APPLICATION_ID,
64 SEEDED_PRECEDENCE,
65 USER_PRECEDENCE,
66 SEEDED_VALUESET_ID,
67 USER_VALUESET_ID,
68 SEEDED_FORMAT_TYPE,
69 USER_FORMAT_TYPE,
70 REQUIRED_FLAG,
71 CONTEXT,
72 ATTRIBUTE1,
73 ATTRIBUTE2,
74 ATTRIBUTE3,
75 ATTRIBUTE4,
76 ATTRIBUTE5,
77 ATTRIBUTE6,
78 ATTRIBUTE7,
79 ATTRIBUTE15,
80 CREATION_DATE,
81 CREATED_BY,
82 LAST_UPDATE_DATE,
83 LAST_UPDATED_BY,
84 LAST_UPDATE_LOGIN
85 ) values (
86 X_ATTRIBUTE12,
87 X_ATTRIBUTE13,
88 X_ATTRIBUTE14,
89 X_ATTRIBUTE8,
90 X_ATTRIBUTE9,
91 X_ATTRIBUTE10,
92 X_ATTRIBUTE11,
93 X_SEGMENT_ID,
94 X_SEGMENT_CODE,
95 X_PRC_CONTEXT_ID,
96 X_AVAILABILITY_IN_BASIC,
97 X_SEGMENT_MAPPING_COLUMN,
98 X_SEEDED_FLAG,
99 X_APPLICATION_ID,
100 X_SEEDED_PRECEDENCE,
101 X_USER_PRECEDENCE,
102 X_SEEDED_VALUESET_ID,
103 X_USER_VALUESET_ID,
104 X_SEEDED_FORMAT_TYPE,
105 X_USER_FORMAT_TYPE,
106 X_REQUIRED_FLAG,
107 X_CONTEXT,
108 X_ATTRIBUTE1,
109 X_ATTRIBUTE2,
110 X_ATTRIBUTE3,
111 X_ATTRIBUTE4,
112 X_ATTRIBUTE5,
113 X_ATTRIBUTE6,
114 X_ATTRIBUTE7,
115 X_ATTRIBUTE15,
116 X_CREATION_DATE,
117 X_CREATED_BY,
118 X_LAST_UPDATE_DATE,
119 X_LAST_UPDATED_BY,
120 X_LAST_UPDATE_LOGIN
121 );
122
123 insert into QP_SEGMENTS_TL (
124 SEGMENT_ID,
125 SEEDED_SEGMENT_NAME,
126 USER_SEGMENT_NAME,
127 SEEDED_DESCRIPTION,
128 USER_DESCRIPTION,
129 CREATED_BY,
130 CREATION_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_DATE,
133 LAST_UPDATE_LOGIN,
134 LANGUAGE,
135 SOURCE_LANG
136 ) select
137 X_SEGMENT_ID,
138 X_SEEDED_SEGMENT_NAME,
139 X_USER_SEGMENT_NAME,
140 X_SEEDED_DESCRIPTION,
141 X_USER_DESCRIPTION,
142 X_CREATED_BY,
143 X_CREATION_DATE,
144 X_LAST_UPDATED_BY,
145 X_LAST_UPDATE_DATE,
146 X_LAST_UPDATE_LOGIN,
147 L.LANGUAGE_CODE,
148 userenv('LANG')
149 from FND_LANGUAGES L
150 where L.INSTALLED_FLAG in ('I', 'B')
151 and not exists
152 (select NULL
153 from QP_SEGMENTS_TL T
154 where T.SEGMENT_ID = X_SEGMENT_ID
155 and T.LANGUAGE = L.LANGUAGE_CODE);
156
157 open c;
158 fetch c into X_ROWID;
159 if (c%notfound) then
160 close c;
161 raise no_data_found;
162 end if;
163 close c;
164
165 end INSERT_ROW;
166
167 procedure LOCK_ROW (
168 X_SEGMENT_ID in NUMBER,
169 X_ATTRIBUTE12 in VARCHAR2,
170 X_ATTRIBUTE13 in VARCHAR2,
171 X_ATTRIBUTE14 in VARCHAR2,
172 X_ATTRIBUTE8 in VARCHAR2,
173 X_ATTRIBUTE9 in VARCHAR2,
174 X_ATTRIBUTE10 in VARCHAR2,
175 X_ATTRIBUTE11 in VARCHAR2,
176 X_SEGMENT_CODE in VARCHAR2,
177 X_PRC_CONTEXT_ID in NUMBER,
178 X_AVAILABILITY_IN_BASIC in VARCHAR2,
179 X_SEGMENT_MAPPING_COLUMN in VARCHAR2,
180 X_SEEDED_FLAG in VARCHAR2,
181 X_APPLICATION_ID in NUMBER,
182 X_SEEDED_PRECEDENCE in NUMBER,
183 X_USER_PRECEDENCE in NUMBER,
184 X_SEEDED_VALUESET_ID in NUMBER,
185 X_USER_VALUESET_ID in NUMBER,
186 X_SEEDED_FORMAT_TYPE in VARCHAR2,
187 X_USER_FORMAT_TYPE in VARCHAR2,
188 X_CONTEXT in VARCHAR2,
189 X_ATTRIBUTE1 in VARCHAR2,
190 X_ATTRIBUTE2 in VARCHAR2,
191 X_ATTRIBUTE3 in VARCHAR2,
192 X_ATTRIBUTE4 in VARCHAR2,
193 X_ATTRIBUTE5 in VARCHAR2,
194 X_ATTRIBUTE6 in VARCHAR2,
195 X_ATTRIBUTE7 in VARCHAR2,
196 X_ATTRIBUTE15 in VARCHAR2,
197 X_SEEDED_SEGMENT_NAME in VARCHAR2,
198 X_USER_SEGMENT_NAME in VARCHAR2,
199 X_SEEDED_DESCRIPTION in VARCHAR2,
200 X_USER_DESCRIPTION in VARCHAR2,
201 X_REQUIRED_FLAG in VARCHAR2
202 ) is
203 cursor c is select
204 ATTRIBUTE12,
205 ATTRIBUTE13,
206 ATTRIBUTE14,
207 ATTRIBUTE8,
208 ATTRIBUTE9,
209 ATTRIBUTE10,
210 ATTRIBUTE11,
211 SEGMENT_CODE,
212 PRC_CONTEXT_ID,
213 AVAILABILITY_IN_BASIC,
214 SEGMENT_MAPPING_COLUMN,
215 SEEDED_FLAG,
216 APPLICATION_ID,
217 SEEDED_PRECEDENCE,
218 USER_PRECEDENCE,
219 SEEDED_VALUESET_ID,
220 USER_VALUESET_ID,
221 SEEDED_FORMAT_TYPE,
222 USER_FORMAT_TYPE,
223 REQUIRED_FLAG,
224 CONTEXT,
225 ATTRIBUTE1,
226 ATTRIBUTE2,
227 ATTRIBUTE3,
228 ATTRIBUTE4,
229 ATTRIBUTE5,
230 ATTRIBUTE6,
231 ATTRIBUTE7,
232 ATTRIBUTE15
233 from QP_SEGMENTS_B
234 where SEGMENT_ID = X_SEGMENT_ID
235 for update of SEGMENT_ID nowait;
236 recinfo c%rowtype;
237
238 cursor c1 is select
239 SEEDED_SEGMENT_NAME,
240 USER_SEGMENT_NAME,
241 SEEDED_DESCRIPTION,
242 USER_DESCRIPTION,
243 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
244 from QP_SEGMENTS_TL
245 where SEGMENT_ID = X_SEGMENT_ID
246 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
247 for update of SEGMENT_ID nowait;
248 begin
249 open c;
250 fetch c into recinfo;
251 if (c%notfound) then
252 close c;
253 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
254 app_exception.raise_exception;
255 end if;
256 close c;
257 if ( ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
258 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
259 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
260 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
261 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
262 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
263 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
264 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
265 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
266 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
267 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
268 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
269 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
270 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
271 AND (recinfo.SEGMENT_CODE = X_SEGMENT_CODE)
272 AND (recinfo.PRC_CONTEXT_ID = X_PRC_CONTEXT_ID)
273 AND (recinfo.AVAILABILITY_IN_BASIC = X_AVAILABILITY_IN_BASIC)
274 AND (recinfo.SEGMENT_MAPPING_COLUMN = X_SEGMENT_MAPPING_COLUMN)
275 AND (recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
276 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
277 AND ((recinfo.SEEDED_PRECEDENCE = X_SEEDED_PRECEDENCE)
278 OR ((recinfo.SEEDED_PRECEDENCE is null) AND (X_SEEDED_PRECEDENCE is null)))
279 AND (recinfo.USER_PRECEDENCE = X_USER_PRECEDENCE)
280 AND ((recinfo.SEEDED_VALUESET_ID = X_SEEDED_VALUESET_ID)
281 OR ((recinfo.SEEDED_VALUESET_ID is null) AND (X_SEEDED_VALUESET_ID is null)))
282 AND ((recinfo.USER_VALUESET_ID = X_USER_VALUESET_ID)
283 OR ((recinfo.USER_VALUESET_ID is null) AND (X_USER_VALUESET_ID is null)))
284 AND ((recinfo.SEEDED_FORMAT_TYPE = X_SEEDED_FORMAT_TYPE)
285 OR ((recinfo.SEEDED_FORMAT_TYPE is null) AND (X_SEEDED_FORMAT_TYPE is null)))
286 AND ((recinfo.USER_FORMAT_TYPE = X_USER_FORMAT_TYPE)
287 OR ((recinfo.USER_FORMAT_TYPE is null) AND (X_USER_FORMAT_TYPE is null)))
288 AND ((recinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
289 OR ((recinfo.REQUIRED_FLAG is null) AND (X_REQUIRED_FLAG is null)))
290 AND ((recinfo.CONTEXT = X_CONTEXT)
291 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
292 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
293 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
294 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
295 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
296 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
297 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
298 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
299 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
300 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
301 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
302 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
303 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
304 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
305 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
306 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
307 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
308 ) then
309 null;
310 else
311 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
312 app_exception.raise_exception;
313 end if;
314
315 for tlinfo in c1 loop
316 if (tlinfo.BASELANG = 'Y') then
317 if (( ((tlinfo.SEEDED_SEGMENT_NAME = X_SEEDED_SEGMENT_NAME)
318 OR ((tlinfo.SEEDED_SEGMENT_NAME is null) AND (X_SEEDED_SEGMENT_NAME is null)))
319 AND (tlinfo.USER_SEGMENT_NAME = X_USER_SEGMENT_NAME)
320 ) AND
321 ( ((tlinfo.SEEDED_DESCRIPTION = X_SEEDED_DESCRIPTION)
322 OR ((tlinfo.SEEDED_DESCRIPTION is null) AND (X_SEEDED_DESCRIPTION is null)))
323 AND (tlinfo.USER_DESCRIPTION = X_USER_DESCRIPTION))) then
324 null;
325 else
326 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
327 app_exception.raise_exception;
328 end if;
329 end if;
330 end loop;
331 return;
332 end LOCK_ROW;
333
334 procedure UPDATE_ROW (
335 X_SEGMENT_ID in NUMBER,
336 X_ATTRIBUTE12 in VARCHAR2,
337 X_ATTRIBUTE13 in VARCHAR2,
338 X_ATTRIBUTE14 in VARCHAR2,
339 X_ATTRIBUTE8 in VARCHAR2,
340 X_ATTRIBUTE9 in VARCHAR2,
341 X_ATTRIBUTE10 in VARCHAR2,
342 X_ATTRIBUTE11 in VARCHAR2,
343 X_SEGMENT_CODE in VARCHAR2,
344 X_PRC_CONTEXT_ID in NUMBER,
345 X_AVAILABILITY_IN_BASIC in VARCHAR2,
346 X_SEGMENT_MAPPING_COLUMN in VARCHAR2,
347 X_SEEDED_FLAG in VARCHAR2,
348 X_APPLICATION_ID in NUMBER,
349 X_SEEDED_PRECEDENCE in NUMBER,
350 X_USER_PRECEDENCE in NUMBER,
351 X_SEEDED_VALUESET_ID in NUMBER,
352 X_USER_VALUESET_ID in NUMBER,
353 X_SEEDED_FORMAT_TYPE in VARCHAR2,
354 X_USER_FORMAT_TYPE in VARCHAR2,
355 X_CONTEXT in VARCHAR2,
356 X_ATTRIBUTE1 in VARCHAR2,
357 X_ATTRIBUTE2 in VARCHAR2,
358 X_ATTRIBUTE3 in VARCHAR2,
359 X_ATTRIBUTE4 in VARCHAR2,
360 X_ATTRIBUTE5 in VARCHAR2,
361 X_ATTRIBUTE6 in VARCHAR2,
362 X_ATTRIBUTE7 in VARCHAR2,
363 X_ATTRIBUTE15 in VARCHAR2,
364 X_SEEDED_SEGMENT_NAME in VARCHAR2,
365 X_USER_SEGMENT_NAME in VARCHAR2,
366 X_SEEDED_DESCRIPTION in VARCHAR2,
367 X_USER_DESCRIPTION in VARCHAR2,
368 X_REQUIRED_FLAG in VARCHAR2,
369 X_LAST_UPDATE_DATE in DATE,
370 X_LAST_UPDATED_BY in NUMBER,
371 X_LAST_UPDATE_LOGIN in NUMBER
372 ) is
373 begin
374 update QP_SEGMENTS_B set
375 ATTRIBUTE12 = X_ATTRIBUTE12,
376 ATTRIBUTE13 = X_ATTRIBUTE13,
377 ATTRIBUTE14 = X_ATTRIBUTE14,
378 ATTRIBUTE8 = X_ATTRIBUTE8,
379 ATTRIBUTE9 = X_ATTRIBUTE9,
380 ATTRIBUTE10 = X_ATTRIBUTE10,
381 ATTRIBUTE11 = X_ATTRIBUTE11,
382 SEGMENT_CODE = X_SEGMENT_CODE,
383 PRC_CONTEXT_ID = X_PRC_CONTEXT_ID,
384 AVAILABILITY_IN_BASIC = X_AVAILABILITY_IN_BASIC,
385 SEGMENT_MAPPING_COLUMN = X_SEGMENT_MAPPING_COLUMN,
386 SEEDED_FLAG = X_SEEDED_FLAG,
387 APPLICATION_ID = X_APPLICATION_ID,
388 SEEDED_PRECEDENCE = X_SEEDED_PRECEDENCE,
389 USER_PRECEDENCE = X_USER_PRECEDENCE,
390 SEEDED_VALUESET_ID = X_SEEDED_VALUESET_ID,
391 USER_VALUESET_ID = X_USER_VALUESET_ID,
392 SEEDED_FORMAT_TYPE = X_SEEDED_FORMAT_TYPE,
393 USER_FORMAT_TYPE = X_USER_FORMAT_TYPE,
394 REQUIRED_FLAG = X_REQUIRED_FLAG,
395 CONTEXT = X_CONTEXT,
396 ATTRIBUTE1 = X_ATTRIBUTE1,
397 ATTRIBUTE2 = X_ATTRIBUTE2,
398 ATTRIBUTE3 = X_ATTRIBUTE3,
399 ATTRIBUTE4 = X_ATTRIBUTE4,
400 ATTRIBUTE5 = X_ATTRIBUTE5,
401 ATTRIBUTE6 = X_ATTRIBUTE6,
402 ATTRIBUTE7 = X_ATTRIBUTE7,
403 ATTRIBUTE15 = X_ATTRIBUTE15,
404 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
405 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
406 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
407 where SEGMENT_ID = X_SEGMENT_ID;
408
409 if (sql%notfound) then
410 raise no_data_found;
411 end if;
412
413 update QP_SEGMENTS_TL set
414 SEEDED_SEGMENT_NAME = X_SEEDED_SEGMENT_NAME,
415 USER_SEGMENT_NAME = X_USER_SEGMENT_NAME,
416 SEEDED_DESCRIPTION = X_SEEDED_DESCRIPTION,
417 USER_DESCRIPTION = X_USER_DESCRIPTION,
418 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
419 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
420 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
421 SOURCE_LANG = userenv('LANG')
422 where SEGMENT_ID = X_SEGMENT_ID
423 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
424
425 if (sql%notfound) then
426 raise no_data_found;
427 end if;
428 end UPDATE_ROW;
429
430 procedure DELETE_ROW (
431 X_SEGMENT_ID in NUMBER
432 ) is
433 begin
434 delete from QP_SEGMENTS_TL
435 where SEGMENT_ID = X_SEGMENT_ID;
436
437 if (sql%notfound) then
438 raise no_data_found;
439 end if;
440
441 delete from QP_SEGMENTS_B
442 where SEGMENT_ID = X_SEGMENT_ID;
443
444 if (sql%notfound) then
445 raise no_data_found;
446 end if;
447 end DELETE_ROW;
448
449 procedure ADD_LANGUAGE
450 is
451 begin
452 delete from QP_SEGMENTS_TL T
453 where not exists
454 (select NULL
455 from QP_SEGMENTS_B B
456 where B.SEGMENT_ID = T.SEGMENT_ID
457 );
458
459 update QP_SEGMENTS_TL T set (
460 SEEDED_SEGMENT_NAME,
461 USER_SEGMENT_NAME,
462 SEEDED_DESCRIPTION,
463 USER_DESCRIPTION
464 ) = (select
465 B.SEEDED_SEGMENT_NAME,
466 B.USER_SEGMENT_NAME,
467 B.SEEDED_DESCRIPTION,
468 B.USER_DESCRIPTION
469 from QP_SEGMENTS_TL B
470 where B.SEGMENT_ID = T.SEGMENT_ID
471 and B.LANGUAGE = T.SOURCE_LANG)
472 where (
473 T.SEGMENT_ID,
474 T.LANGUAGE
475 ) in (select
476 SUBT.SEGMENT_ID,
477 SUBT.LANGUAGE
478 from QP_SEGMENTS_TL SUBB, QP_SEGMENTS_TL SUBT
479 where SUBB.SEGMENT_ID = SUBT.SEGMENT_ID
480 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
481 and (SUBB.SEEDED_SEGMENT_NAME <> SUBT.SEEDED_SEGMENT_NAME
482 or (SUBB.SEEDED_SEGMENT_NAME is null and SUBT.SEEDED_SEGMENT_NAME is not null)
483 or (SUBB.SEEDED_SEGMENT_NAME is not null and SUBT.SEEDED_SEGMENT_NAME is null)
484 or SUBB.USER_SEGMENT_NAME <> SUBT.USER_SEGMENT_NAME
485 ));
486
487 insert into QP_SEGMENTS_TL (
488 SEGMENT_ID,
489 SEEDED_SEGMENT_NAME,
490 USER_SEGMENT_NAME,
491 SEEDED_DESCRIPTION,
492 USER_DESCRIPTION,
493 CREATED_BY,
494 CREATION_DATE,
495 LAST_UPDATED_BY,
496 LAST_UPDATE_DATE,
497 LAST_UPDATE_LOGIN,
498 LANGUAGE,
499 SOURCE_LANG
500 ) select
501 B.SEGMENT_ID,
502 B.SEEDED_SEGMENT_NAME,
503 B.USER_SEGMENT_NAME,
504 B.SEEDED_DESCRIPTION,
505 B.USER_DESCRIPTION,
506 B.CREATED_BY,
507 B.CREATION_DATE,
508 B.LAST_UPDATED_BY,
509 B.LAST_UPDATE_DATE,
510 B.LAST_UPDATE_LOGIN,
511 L.LANGUAGE_CODE,
512 B.SOURCE_LANG
513 from QP_SEGMENTS_TL B, FND_LANGUAGES L
514 where L.INSTALLED_FLAG in ('I', 'B')
515 and B.LANGUAGE = userenv('LANG')
516 and not exists
517 (select NULL
518 from QP_SEGMENTS_TL T
519 where T.SEGMENT_ID = B.SEGMENT_ID
520 and T.LANGUAGE = L.LANGUAGE_CODE);
521 end ADD_LANGUAGE;
522
523 end QP_SEGMENTS_PKG;