1 package body QP_PRC_CONTEXTS_PKG as
2 /* $Header: QPXCONTB.pls 120.3 2006/04/04 18:22:57 hwong noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_PRC_CONTEXT_ID in NUMBER,
6 X_ATTRIBUTE7 in VARCHAR2,
7 X_ATTRIBUTE8 in VARCHAR2,
8 X_ATTRIBUTE9 in VARCHAR2,
9 X_ATTRIBUTE10 in VARCHAR2,
10 X_ATTRIBUTE11 in VARCHAR2,
11 X_ATTRIBUTE12 in VARCHAR2,
12 X_ATTRIBUTE13 in VARCHAR2,
13 X_ATTRIBUTE14 in VARCHAR2,
14 X_ATTRIBUTE15 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE6 in VARCHAR2,
18 X_ATTRIBUTE1 in VARCHAR2,
19 X_ATTRIBUTE2 in VARCHAR2,
20 X_ATTRIBUTE3 in VARCHAR2,
21 X_PRC_CONTEXT_CODE in VARCHAR2,
22 X_PRC_CONTEXT_TYPE in VARCHAR2,
23 X_SEEDED_FLAG in VARCHAR2,
24 X_ENABLED_FLAG in VARCHAR2,
25 X_CONTEXT in VARCHAR2,
26 X_SEEDED_PRC_CONTEXT_NAME in VARCHAR2,
27 X_USER_PRC_CONTEXT_NAME in VARCHAR2,
28 X_SEEDED_DESCRIPTION in VARCHAR2,
29 X_USER_DESCRIPTION in VARCHAR2,
30 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER
35 ) is
36 cursor C is select ROWID from QP_PRC_CONTEXTS_B
37 where PRC_CONTEXT_ID = X_PRC_CONTEXT_ID
38 ;
39 l_x_rowid VARCHAR2(240); /* file.sql.39 changes */
40 begin
41 insert into QP_PRC_CONTEXTS_B (
42 ATTRIBUTE7,
43 ATTRIBUTE8,
44 ATTRIBUTE9,
45 ATTRIBUTE10,
46 ATTRIBUTE11,
47 ATTRIBUTE12,
48 ATTRIBUTE13,
49 ATTRIBUTE14,
50 ATTRIBUTE15,
51 ATTRIBUTE4,
52 ATTRIBUTE5,
53 ATTRIBUTE6,
54 ATTRIBUTE1,
55 ATTRIBUTE2,
56 ATTRIBUTE3,
57 PRC_CONTEXT_ID,
58 PRC_CONTEXT_CODE,
59 PRC_CONTEXT_TYPE,
60 SEEDED_FLAG,
61 ENABLED_FLAG,
62 CONTEXT,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN
68 ) values (
69 X_ATTRIBUTE7,
70 X_ATTRIBUTE8,
71 X_ATTRIBUTE9,
72 X_ATTRIBUTE10,
73 X_ATTRIBUTE11,
74 X_ATTRIBUTE12,
75 X_ATTRIBUTE13,
76 X_ATTRIBUTE14,
77 X_ATTRIBUTE15,
78 X_ATTRIBUTE4,
79 X_ATTRIBUTE5,
80 X_ATTRIBUTE6,
81 X_ATTRIBUTE1,
82 X_ATTRIBUTE2,
83 X_ATTRIBUTE3,
84 X_PRC_CONTEXT_ID,
85 X_PRC_CONTEXT_CODE,
86 X_PRC_CONTEXT_TYPE,
87 X_SEEDED_FLAG,
88 X_ENABLED_FLAG,
89 X_CONTEXT,
90 X_CREATION_DATE,
91 X_CREATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_LOGIN
95 );
96
97 insert into QP_PRC_CONTEXTS_TL (
98 PRC_CONTEXT_ID,
99 SEEDED_PRC_CONTEXT_NAME,
100 USER_PRC_CONTEXT_NAME,
101 SEEDED_DESCRIPTION,
102 USER_DESCRIPTION,
103 CREATED_BY,
104 CREATION_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_DATE,
107 LAST_UPDATE_LOGIN,
108 LANGUAGE,
109 SOURCE_LANG
110 ) select
111 X_PRC_CONTEXT_ID,
112 X_SEEDED_PRC_CONTEXT_NAME,
113 X_USER_PRC_CONTEXT_NAME,
114 X_SEEDED_DESCRIPTION,
115 X_USER_DESCRIPTION,
116 X_CREATED_BY,
117 X_CREATION_DATE,
118 X_LAST_UPDATED_BY,
119 X_LAST_UPDATE_DATE,
120 X_LAST_UPDATE_LOGIN,
121 L.LANGUAGE_CODE,
122 userenv('LANG')
123 from FND_LANGUAGES L
124 where L.INSTALLED_FLAG in ('I', 'B')
125 and not exists
126 (select NULL
127 from QP_PRC_CONTEXTS_TL T
128 where T.PRC_CONTEXT_ID = X_PRC_CONTEXT_ID
129 and T.LANGUAGE = L.LANGUAGE_CODE);
130
131 open c;
132 -- fetch c into X_ROWID; /* file.sql.39 changes */
133 fetch c into l_x_rowid;
134 if (c%notfound) then
135 close c;
136 raise no_data_found;
137 else
138 X_ROWID := l_x_rowid; /* file.sql.39 changes */
139 end if;
140 close c;
141
142 end INSERT_ROW;
143
144 procedure LOCK_ROW (
145 X_PRC_CONTEXT_ID in NUMBER,
146 X_ATTRIBUTE7 in VARCHAR2,
147 X_ATTRIBUTE8 in VARCHAR2,
148 X_ATTRIBUTE9 in VARCHAR2,
149 X_ATTRIBUTE10 in VARCHAR2,
150 X_ATTRIBUTE11 in VARCHAR2,
151 X_ATTRIBUTE12 in VARCHAR2,
152 X_ATTRIBUTE13 in VARCHAR2,
153 X_ATTRIBUTE14 in VARCHAR2,
154 X_ATTRIBUTE15 in VARCHAR2,
155 X_ATTRIBUTE4 in VARCHAR2,
156 X_ATTRIBUTE5 in VARCHAR2,
157 X_ATTRIBUTE6 in VARCHAR2,
158 X_ATTRIBUTE1 in VARCHAR2,
159 X_ATTRIBUTE2 in VARCHAR2,
160 X_ATTRIBUTE3 in VARCHAR2,
161 X_PRC_CONTEXT_CODE in VARCHAR2,
162 X_PRC_CONTEXT_TYPE in VARCHAR2,
163 X_SEEDED_FLAG in VARCHAR2,
164 X_ENABLED_FLAG in VARCHAR2,
165 X_CONTEXT in VARCHAR2,
166 X_SEEDED_PRC_CONTEXT_NAME in VARCHAR2,
167 X_USER_PRC_CONTEXT_NAME in VARCHAR2,
168 X_SEEDED_DESCRIPTION in VARCHAR2,
169 X_USER_DESCRIPTION in VARCHAR2
170 ) is
171 cursor c is select
172 ATTRIBUTE7,
173 ATTRIBUTE8,
174 ATTRIBUTE9,
175 ATTRIBUTE10,
176 ATTRIBUTE11,
177 ATTRIBUTE12,
178 ATTRIBUTE13,
179 ATTRIBUTE14,
180 ATTRIBUTE15,
181 ATTRIBUTE4,
182 ATTRIBUTE5,
183 ATTRIBUTE6,
184 ATTRIBUTE1,
185 ATTRIBUTE2,
186 ATTRIBUTE3,
187 PRC_CONTEXT_CODE,
188 PRC_CONTEXT_TYPE,
189 SEEDED_FLAG,
190 ENABLED_FLAG,
191 CONTEXT
192 from QP_PRC_CONTEXTS_B
193 where PRC_CONTEXT_ID = X_PRC_CONTEXT_ID
194 for update of PRC_CONTEXT_ID nowait;
195 recinfo c%rowtype;
196
197 cursor c1 is select
198 SEEDED_PRC_CONTEXT_NAME,
199 USER_PRC_CONTEXT_NAME,
200 SEEDED_DESCRIPTION,
201 USER_DESCRIPTION,
202 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
203 from QP_PRC_CONTEXTS_TL
204 where PRC_CONTEXT_ID = X_PRC_CONTEXT_ID
205 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
206 for update of PRC_CONTEXT_ID nowait;
207 begin
208 open c;
209 fetch c into recinfo;
210 if (c%notfound) then
211 close c;
212 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
213 app_exception.raise_exception;
214 end if;
215 close c;
216 if ( ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
217 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
218 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
219 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
220 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
221 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
222 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
223 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
224 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
225 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
226 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
227 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
228 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
229 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
230 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
231 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
232 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
233 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
234 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
235 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
236 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
237 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
238 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
239 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
240 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
241 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
242 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
243 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
244 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
245 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
246 AND (recinfo.PRC_CONTEXT_CODE = X_PRC_CONTEXT_CODE)
247 AND (recinfo.PRC_CONTEXT_TYPE = X_PRC_CONTEXT_TYPE)
248 AND (recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
249 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
250 AND ((recinfo.CONTEXT = X_CONTEXT)
251 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
252 ) then
253 null;
254 else
255 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
256 app_exception.raise_exception;
257 end if;
258
259 for tlinfo in c1 loop
260 if (tlinfo.BASELANG = 'Y') then
261 if ( ((tlinfo.SEEDED_PRC_CONTEXT_NAME = X_SEEDED_PRC_CONTEXT_NAME)
262 OR ((tlinfo.SEEDED_PRC_CONTEXT_NAME is null) AND (X_SEEDED_PRC_CONTEXT_NAME is null)))
263 AND (tlinfo.USER_PRC_CONTEXT_NAME = X_USER_PRC_CONTEXT_NAME)
264 AND ((tlinfo.SEEDED_DESCRIPTION = X_SEEDED_DESCRIPTION)
265 OR ((tlinfo.SEEDED_DESCRIPTION is null) AND (X_SEEDED_DESCRIPTION is null)))
266 AND (tlinfo.USER_DESCRIPTION = X_USER_DESCRIPTION)
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_PRC_CONTEXT_ID in NUMBER,
280 X_ATTRIBUTE7 in VARCHAR2,
281 X_ATTRIBUTE8 in VARCHAR2,
282 X_ATTRIBUTE9 in VARCHAR2,
283 X_ATTRIBUTE10 in VARCHAR2,
284 X_ATTRIBUTE11 in VARCHAR2,
285 X_ATTRIBUTE12 in VARCHAR2,
286 X_ATTRIBUTE13 in VARCHAR2,
287 X_ATTRIBUTE14 in VARCHAR2,
288 X_ATTRIBUTE15 in VARCHAR2,
289 X_ATTRIBUTE4 in VARCHAR2,
290 X_ATTRIBUTE5 in VARCHAR2,
291 X_ATTRIBUTE6 in VARCHAR2,
292 X_ATTRIBUTE1 in VARCHAR2,
293 X_ATTRIBUTE2 in VARCHAR2,
294 X_ATTRIBUTE3 in VARCHAR2,
295 X_PRC_CONTEXT_CODE in VARCHAR2,
296 X_PRC_CONTEXT_TYPE in VARCHAR2,
297 X_SEEDED_FLAG in VARCHAR2,
298 X_ENABLED_FLAG in VARCHAR2,
299 X_CONTEXT in VARCHAR2,
300 X_SEEDED_PRC_CONTEXT_NAME in VARCHAR2,
301 X_USER_PRC_CONTEXT_NAME in VARCHAR2,
302 X_SEEDED_DESCRIPTION in VARCHAR2,
303 X_USER_DESCRIPTION in VARCHAR2,
304 X_LAST_UPDATE_DATE in DATE,
305 X_LAST_UPDATED_BY in NUMBER,
306 X_LAST_UPDATE_LOGIN in NUMBER
307 ) is
308 begin
309 update QP_PRC_CONTEXTS_B set
310 ATTRIBUTE7 = X_ATTRIBUTE7,
311 ATTRIBUTE8 = X_ATTRIBUTE8,
312 ATTRIBUTE9 = X_ATTRIBUTE9,
313 ATTRIBUTE10 = X_ATTRIBUTE10,
314 ATTRIBUTE11 = X_ATTRIBUTE11,
315 ATTRIBUTE12 = X_ATTRIBUTE12,
316 ATTRIBUTE13 = X_ATTRIBUTE13,
317 ATTRIBUTE14 = X_ATTRIBUTE14,
318 ATTRIBUTE15 = X_ATTRIBUTE15,
319 ATTRIBUTE4 = X_ATTRIBUTE4,
320 ATTRIBUTE5 = X_ATTRIBUTE5,
321 ATTRIBUTE6 = X_ATTRIBUTE6,
322 ATTRIBUTE1 = X_ATTRIBUTE1,
323 ATTRIBUTE2 = X_ATTRIBUTE2,
324 ATTRIBUTE3 = X_ATTRIBUTE3,
325 PRC_CONTEXT_CODE = X_PRC_CONTEXT_CODE,
326 PRC_CONTEXT_TYPE = X_PRC_CONTEXT_TYPE,
327 SEEDED_FLAG = X_SEEDED_FLAG,
328 ENABLED_FLAG = X_ENABLED_FLAG,
329 CONTEXT = X_CONTEXT,
330 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
331 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
332 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
333 where PRC_CONTEXT_ID = X_PRC_CONTEXT_ID;
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338
339 update QP_PRC_CONTEXTS_TL set
340 SEEDED_PRC_CONTEXT_NAME = X_SEEDED_PRC_CONTEXT_NAME,
341 USER_PRC_CONTEXT_NAME = X_USER_PRC_CONTEXT_NAME,
342 SEEDED_DESCRIPTION = X_SEEDED_DESCRIPTION,
343 USER_DESCRIPTION = X_USER_DESCRIPTION,
344 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
345 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
346 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
347 SOURCE_LANG = userenv('LANG')
348 where PRC_CONTEXT_ID = X_PRC_CONTEXT_ID
349 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
350
351 if (sql%notfound) then
352 raise no_data_found;
353 end if;
354 end UPDATE_ROW;
355
356 procedure DELETE_ROW (
357 X_PRC_CONTEXT_ID in NUMBER
358 ) is
359 begin
360 delete from QP_PRC_CONTEXTS_TL
361 where PRC_CONTEXT_ID = X_PRC_CONTEXT_ID;
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366
367 delete from QP_PRC_CONTEXTS_B
368 where PRC_CONTEXT_ID = X_PRC_CONTEXT_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 QP_PRC_CONTEXTS_TL T
379 where not exists
380 (select NULL
381 from QP_PRC_CONTEXTS_B B
382 where B.PRC_CONTEXT_ID = T.PRC_CONTEXT_ID
383 );
384
385 update QP_PRC_CONTEXTS_TL T set (
386 SEEDED_PRC_CONTEXT_NAME,
387 USER_PRC_CONTEXT_NAME,
388 SEEDED_DESCRIPTION,
389 USER_DESCRIPTION
390 ) = (select
391 B.SEEDED_PRC_CONTEXT_NAME,
392 B.USER_PRC_CONTEXT_NAME,
393 B.SEEDED_DESCRIPTION,
394 B.USER_DESCRIPTION
395 from QP_PRC_CONTEXTS_TL B
396 where B.PRC_CONTEXT_ID = T.PRC_CONTEXT_ID
397 and B.LANGUAGE = T.SOURCE_LANG)
398 where (
399 T.PRC_CONTEXT_ID,
400 T.LANGUAGE
401 ) in (select
402 SUBT.PRC_CONTEXT_ID,
403 SUBT.LANGUAGE
404 from QP_PRC_CONTEXTS_TL SUBB, QP_PRC_CONTEXTS_TL SUBT
405 where SUBB.PRC_CONTEXT_ID = SUBT.PRC_CONTEXT_ID
406 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
407 and (SUBB.SEEDED_PRC_CONTEXT_NAME <> SUBT.SEEDED_PRC_CONTEXT_NAME
408 or (SUBB.SEEDED_PRC_CONTEXT_NAME is null and SUBT.SEEDED_PRC_CONTEXT_NAME is not null)
409 or (SUBB.SEEDED_PRC_CONTEXT_NAME is not null and SUBT.SEEDED_PRC_CONTEXT_NAME is null)
410 or SUBB.USER_PRC_CONTEXT_NAME <> SUBT.USER_PRC_CONTEXT_NAME
411 or SUBB.SEEDED_DESCRIPTION <> SUBT.SEEDED_DESCRIPTION
412 or (SUBB.SEEDED_DESCRIPTION is null and SUBT.SEEDED_DESCRIPTION is not null)
413 or (SUBB.SEEDED_DESCRIPTION is not null and SUBT.SEEDED_DESCRIPTION is null)
414 or SUBB.USER_DESCRIPTION <> SUBT.USER_DESCRIPTION
415 ));
416
417 insert into QP_PRC_CONTEXTS_TL (
418 PRC_CONTEXT_ID,
419 SEEDED_PRC_CONTEXT_NAME,
420 USER_PRC_CONTEXT_NAME,
421 SEEDED_DESCRIPTION,
422 USER_DESCRIPTION,
423 CREATED_BY,
424 CREATION_DATE,
425 LAST_UPDATED_BY,
426 LAST_UPDATE_DATE,
427 LAST_UPDATE_LOGIN,
428 LANGUAGE,
429 SOURCE_LANG
430 ) select /*+ index(B QP_PRC_CONTEXTS_TL_N1) */
431 B.PRC_CONTEXT_ID,
432 B.SEEDED_PRC_CONTEXT_NAME,
433 B.USER_PRC_CONTEXT_NAME,
434 B.SEEDED_DESCRIPTION,
435 B.USER_DESCRIPTION,
436 B.CREATED_BY,
437 B.CREATION_DATE,
438 B.LAST_UPDATED_BY,
439 B.LAST_UPDATE_DATE,
440 B.LAST_UPDATE_LOGIN,
441 L.LANGUAGE_CODE,
442 B.SOURCE_LANG
443 from QP_PRC_CONTEXTS_TL B, FND_LANGUAGES L
444 where L.INSTALLED_FLAG in ('I', 'B')
445 and B.LANGUAGE = userenv('LANG')
446 and not exists
447 (select NULL
448 from QP_PRC_CONTEXTS_TL T
449 where T.PRC_CONTEXT_ID = B.PRC_CONTEXT_ID
450 and T.LANGUAGE = L.LANGUAGE_CODE);
451 end ADD_LANGUAGE;
452
453 end QP_PRC_CONTEXTS_PKG;
[Home] [Help]