1 package body AK_OBJECTS_PKG as
2 /* $Header: AKDOBJTB.pls 120.3 2006/01/25 15:58:21 tshort ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_DATABASE_OBJECT_NAME in VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_NAME in VARCHAR2,
8 X_DESCRIPTION in VARCHAR2,
9 X_PRIMARY_KEY_NAME IN VARCHAR2,
10 X_DEFAULTING_API_PKG IN VARCHAR2,
11 X_DEFAULTING_API_PROC IN VARCHAR2,
12 X_VALIDATION_API_PKG IN VARCHAR2,
13 X_VALIDATION_API_PROC IN VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER,
19 X_ATTRIBUTE_CATEGORY in VARCHAR2,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2
35 ) is
36 cursor C is select ROWID from AK_OBJECTS
37 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME;
38 begin
39 insert into AK_OBJECTS (
40 DATABASE_OBJECT_NAME,
41 APPLICATION_ID,
42 PRIMARY_KEY_NAME,
43 DEFAULTING_API_PKG,
44 DEFAULTING_API_PROC,
45 VALIDATION_API_PKG,
46 VALIDATION_API_PROC,
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_LOGIN,
52 ATTRIBUTE_CATEGORY,
53 ATTRIBUTE1,
54 ATTRIBUTE2,
55 ATTRIBUTE3,
56 ATTRIBUTE4,
57 ATTRIBUTE5,
58 ATTRIBUTE6,
59 ATTRIBUTE7,
60 ATTRIBUTE8,
61 ATTRIBUTE9,
62 ATTRIBUTE10,
63 ATTRIBUTE11,
64 ATTRIBUTE12,
65 ATTRIBUTE13,
66 ATTRIBUTE14,
67 ATTRIBUTE15
68 ) values (
69 X_DATABASE_OBJECT_NAME,
70 X_APPLICATION_ID,
71 X_PRIMARY_KEY_NAME,
72 X_DEFAULTING_API_PKG,
73 X_DEFAULTING_API_PROC,
74 X_VALIDATION_API_PKG,
75 X_VALIDATION_API_PROC,
76 X_CREATION_DATE,
77 X_CREATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_LOGIN,
81 X_ATTRIBUTE_CATEGORY,
82 X_ATTRIBUTE1,
83 X_ATTRIBUTE2,
84 X_ATTRIBUTE3,
85 X_ATTRIBUTE4,
86 X_ATTRIBUTE5,
87 X_ATTRIBUTE6,
88 X_ATTRIBUTE7,
89 X_ATTRIBUTE8,
90 X_ATTRIBUTE9,
91 X_ATTRIBUTE10,
92 X_ATTRIBUTE11,
93 X_ATTRIBUTE12,
94 X_ATTRIBUTE13,
95 X_ATTRIBUTE14,
96 X_ATTRIBUTE15
97 );
98
99 open c;
100 fetch c into X_ROWID;
101 if (c%notfound) then
102 close c;
103 raise no_data_found;
104 end if;
105 close c;
106
107 insert into AK_OBJECTS_TL (
108 DATABASE_OBJECT_NAME,
109 LANGUAGE,
110 NAME,
111 DESCRIPTION,
112 SOURCE_LANG,
113 CREATED_BY,
114 CREATION_DATE,
115 LAST_UPDATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATE_LOGIN
118 ) select
119 X_DATABASE_OBJECT_NAME,
120 L.LANGUAGE_CODE,
121 X_NAME,
122 X_DESCRIPTION,
123 userenv('LANG'),
124 X_CREATED_BY,
125 X_CREATION_DATE,
126 X_LAST_UPDATED_BY,
127 X_LAST_UPDATE_DATE,
128 X_LAST_UPDATE_LOGIN
129 from FND_LANGUAGES L
130 where L.INSTALLED_FLAG in ('I', 'B')
131 and not exists
132 (select NULL
133 from AK_OBJECTS_TL T
134 where T.DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
135 and T.LANGUAGE = L.LANGUAGE_CODE);
136 end INSERT_ROW;
137
138 procedure LOCK_ROW (
139 X_DATABASE_OBJECT_NAME in VARCHAR2,
140 X_ATTRIBUTE_CATEGORY in VARCHAR2,
141 X_ATTRIBUTE1 in VARCHAR2,
142 X_ATTRIBUTE2 in VARCHAR2,
143 X_ATTRIBUTE3 in VARCHAR2,
144 X_ATTRIBUTE4 in VARCHAR2,
145 X_ATTRIBUTE5 in VARCHAR2,
146 X_ATTRIBUTE6 in VARCHAR2,
147 X_ATTRIBUTE7 in VARCHAR2,
148 X_ATTRIBUTE8 in VARCHAR2,
149 X_ATTRIBUTE9 in VARCHAR2,
150 X_ATTRIBUTE10 in VARCHAR2,
151 X_ATTRIBUTE11 in VARCHAR2,
152 X_ATTRIBUTE12 in VARCHAR2,
153 X_ATTRIBUTE13 in VARCHAR2,
154 X_ATTRIBUTE14 in VARCHAR2,
155 X_ATTRIBUTE15 in VARCHAR2,
156 X_APPLICATION_ID in NUMBER,
157 X_NAME in VARCHAR2,
158 X_DESCRIPTION in VARCHAR2,
159 X_PRIMARY_KEY_NAME IN VARCHAR2,
160 X_DEFAULTING_API_PKG IN VARCHAR2,
161 X_DEFAULTING_API_PROC IN VARCHAR2,
162 X_VALIDATION_API_PKG IN VARCHAR2,
163 X_VALIDATION_API_PROC IN VARCHAR2
164 ) is
165 cursor c is select
166 ATTRIBUTE_CATEGORY,
167 ATTRIBUTE1,
168 ATTRIBUTE2,
169 ATTRIBUTE3,
170 ATTRIBUTE4,
171 ATTRIBUTE5,
172 ATTRIBUTE6,
173 ATTRIBUTE7,
174 ATTRIBUTE8,
175 ATTRIBUTE9,
176 ATTRIBUTE10,
177 ATTRIBUTE11,
178 ATTRIBUTE12,
179 ATTRIBUTE13,
180 ATTRIBUTE14,
181 ATTRIBUTE15,
182 APPLICATION_ID,
183 PRIMARY_KEY_NAME,
184 DEFAULTING_API_PKG,
185 DEFAULTING_API_PROC,
186 VALIDATION_API_PKG,
187 VALIDATION_API_PROC
188 from AK_OBJECTS
189 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
190 for update of DATABASE_OBJECT_NAME nowait;
191 recinfo c%rowtype;
192
193 cursor c1 is select
194 NAME,
195 DESCRIPTION
196 from AK_OBJECTS_TL
197 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
198 and LANGUAGE = userenv('LANG')
199 for update of DATABASE_OBJECT_NAME nowait;
200 tlinfo c1%rowtype;
201
202 begin
203 open c;
204 fetch c into recinfo;
205 if (c%notfound) then
206 close c;
207 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
208 app_exception.raise_exception;
209 end if;
210 close c;
211 if ( ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
212 OR ((recinfo.ATTRIBUTE_CATEGORY is null)
213 AND (X_ATTRIBUTE_CATEGORY is null)))
214 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
215 OR ((recinfo.ATTRIBUTE1 is null)
216 AND (X_ATTRIBUTE1 is null)))
217 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
218 OR ((recinfo.ATTRIBUTE2 is null)
219 AND (X_ATTRIBUTE2 is null)))
220 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
221 OR ((recinfo.ATTRIBUTE3 is null)
222 AND (X_ATTRIBUTE3 is null)))
223 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
224 OR ((recinfo.ATTRIBUTE4 is null)
225 AND (X_ATTRIBUTE4 is null)))
226 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
227 OR ((recinfo.ATTRIBUTE5 is null)
228 AND (X_ATTRIBUTE5 is null)))
229 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
230 OR ((recinfo.ATTRIBUTE6 is null)
231 AND (X_ATTRIBUTE6 is null)))
232 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
233 OR ((recinfo.ATTRIBUTE7 is null)
234 AND (X_ATTRIBUTE7 is null)))
235 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
236 OR ((recinfo.ATTRIBUTE8 is null)
237 AND (X_ATTRIBUTE8 is null)))
238 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
239 OR ((recinfo.ATTRIBUTE9 is null)
240 AND (X_ATTRIBUTE9 is null)))
241 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
242 OR ((recinfo.ATTRIBUTE10 is null)
243 AND (X_ATTRIBUTE10 is null)))
244 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
245 OR ((recinfo.ATTRIBUTE11 is null)
246 AND (X_ATTRIBUTE11 is null)))
247 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
248 OR ((recinfo.ATTRIBUTE12 is null)
249 AND (X_ATTRIBUTE12 is null)))
250 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
251 OR ((recinfo.ATTRIBUTE13 is null)
252 AND (X_ATTRIBUTE13 is null)))
253 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
254 OR ((recinfo.ATTRIBUTE14 is null)
255 AND (X_ATTRIBUTE14 is null)))
256 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
257 OR ((recinfo.ATTRIBUTE15 is null)
258 AND (X_ATTRIBUTE15 is null)))
259 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
260 AND ((recinfo.PRIMARY_KEY_NAME = X_PRIMARY_KEY_NAME)
261 OR ((recinfo.PRIMARY_KEY_NAME is null)
262 AND (X_PRIMARY_KEY_NAME is null)))
263 AND ((recinfo.DEFAULTING_API_PKG = X_DEFAULTING_API_PKG)
264 OR ((recinfo.DEFAULTING_API_PKG is null)
265 AND (X_DEFAULTING_API_PKG is null)))
266 AND ((recinfo.DEFAULTING_API_PROC = X_DEFAULTING_API_PROC)
267 OR ((recinfo.DEFAULTING_API_PROC is null)
268 AND (X_DEFAULTING_API_PROC is null)))
269 AND ((recinfo.VALIDATION_API_PKG = X_VALIDATION_API_PKG)
270 OR ((recinfo.VALIDATION_API_PKG is null)
271 AND (X_VALIDATION_API_PKG is null)))
272 AND ((recinfo.VALIDATION_API_PROC = X_VALIDATION_API_PROC)
273 OR ((recinfo.VALIDATION_API_PROC is null)
274 AND (X_VALIDATION_API_PROC is null)))
275 ) then
276 null;
277 else
278 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
279 app_exception.raise_exception;
280 end if;
281
282 open c1;
283 fetch c1 into tlinfo;
284 if (c1%notfound) then
285 close c1;
286 return;
287 end if;
288 close c1;
289
290 if ( (tlinfo.NAME = X_NAME)
291 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
292 OR ((tlinfo.DESCRIPTION is null)
293 AND (X_DESCRIPTION is null)))
294 ) then
295 null;
296 else
297 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
298 app_exception.raise_exception;
299 end if;
300 return;
301 end LOCK_ROW;
302
303 procedure UPDATE_ROW (
304 X_DATABASE_OBJECT_NAME in VARCHAR2,
305 X_APPLICATION_ID in NUMBER,
306 X_NAME in VARCHAR2,
307 X_DESCRIPTION in VARCHAR2,
308 X_PRIMARY_KEY_NAME IN VARCHAR2,
309 X_DEFAULTING_API_PKG IN VARCHAR2,
310 X_DEFAULTING_API_PROC IN VARCHAR2,
311 X_VALIDATION_API_PKG IN VARCHAR2,
312 X_VALIDATION_API_PROC IN VARCHAR2,
313 X_LAST_UPDATE_DATE in DATE,
314 X_LAST_UPDATED_BY in NUMBER,
315 X_LAST_UPDATE_LOGIN in NUMBER,
316 X_ATTRIBUTE_CATEGORY in VARCHAR2,
317 X_ATTRIBUTE1 in VARCHAR2,
318 X_ATTRIBUTE2 in VARCHAR2,
319 X_ATTRIBUTE3 in VARCHAR2,
320 X_ATTRIBUTE4 in VARCHAR2,
321 X_ATTRIBUTE5 in VARCHAR2,
322 X_ATTRIBUTE6 in VARCHAR2,
323 X_ATTRIBUTE7 in VARCHAR2,
324 X_ATTRIBUTE8 in VARCHAR2,
325 X_ATTRIBUTE9 in VARCHAR2,
326 X_ATTRIBUTE10 in VARCHAR2,
327 X_ATTRIBUTE11 in VARCHAR2,
328 X_ATTRIBUTE12 in VARCHAR2,
329 X_ATTRIBUTE13 in VARCHAR2,
330 X_ATTRIBUTE14 in VARCHAR2,
331 X_ATTRIBUTE15 in VARCHAR2
332 ) is
333 begin
334 update AK_OBJECTS set
335 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
336 ATTRIBUTE1 = X_ATTRIBUTE1,
337 ATTRIBUTE2 = X_ATTRIBUTE2,
338 ATTRIBUTE3 = X_ATTRIBUTE3,
339 ATTRIBUTE4 = X_ATTRIBUTE4,
340 ATTRIBUTE5 = X_ATTRIBUTE5,
341 ATTRIBUTE6 = X_ATTRIBUTE6,
342 ATTRIBUTE7 = X_ATTRIBUTE7,
343 ATTRIBUTE8 = X_ATTRIBUTE8,
344 ATTRIBUTE9 = X_ATTRIBUTE9,
345 ATTRIBUTE10 = X_ATTRIBUTE10,
346 ATTRIBUTE11 = X_ATTRIBUTE11,
347 ATTRIBUTE12 = X_ATTRIBUTE12,
348 ATTRIBUTE13 = X_ATTRIBUTE13,
349 ATTRIBUTE14 = X_ATTRIBUTE14,
350 ATTRIBUTE15 = X_ATTRIBUTE15,
351 APPLICATION_ID = X_APPLICATION_ID,
352 PRIMARY_KEY_NAME = X_PRIMARY_KEY_NAME,
353 DEFAULTING_API_PKG = X_DEFAULTING_API_PKG,
354 DEFAULTING_API_PROC = X_DEFAULTING_API_PROC,
355 VALIDATION_API_PKG = X_VALIDATION_API_PKG,
356 VALIDATION_API_PROC = X_VALIDATION_API_PROC,
357 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
358 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
359 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
360 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME;
361
362 if (sql%notfound) then
363 raise no_data_found;
364 end if;
365
366 update AK_OBJECTS_TL set
367 NAME = X_NAME,
368 DESCRIPTION = X_DESCRIPTION,
369 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
370 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
371 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
372 SOURCE_LANG = userenv('LANG')
373 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
374 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
375
376 if (sql%notfound) then
377 raise no_data_found;
378 end if;
379 end UPDATE_ROW;
380
381 procedure DELETE_ROW (
382 X_DATABASE_OBJECT_NAME in VARCHAR2
383 ) is
384 begin
385 delete from AK_OBJECTS
386 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME;
387
388 if (sql%notfound) then
389 raise no_data_found;
390 end if;
391
392 delete from AK_OBJECTS_TL
393 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME;
394 if (sql%notfound) then
395 raise no_data_found;
396 end if;
397 end DELETE_ROW;
398
399 procedure ADD_LANGUAGE
400 is
401 begin
402
403 /* Mar/19/03 requested by Ric Ginsberg */
404 /* The following delete and update statements are commented out */
405 /* as a quick workaround to fix the time-consuming table handler issue */
406 /* Eventually we'll need to turn them into a separate fix_language procedure */
407 /*
408 delete from AK_OBJECTS_TL T
409 where not exists
410 (select NULL
411 from AK_OBJECTS B
412 where B.DATABASE_OBJECT_NAME = T.DATABASE_OBJECT_NAME
413 );
414
415 update AK_OBJECTS_TL T set (
416 NAME,
417 DESCRIPTION
418 ) = (select
419 B.NAME,
420 B.DESCRIPTION
421 from AK_OBJECTS_TL B
422 where B.DATABASE_OBJECT_NAME = T.DATABASE_OBJECT_NAME
423 and B.LANGUAGE = T.SOURCE_LANG)
424 where (
425 T.DATABASE_OBJECT_NAME,
426 T.LANGUAGE
427 ) in (select
428 SUBT.DATABASE_OBJECT_NAME,
429 SUBT.LANGUAGE
430 from AK_OBJECTS_TL SUBB, AK_OBJECTS_TL SUBT
431 where SUBB.DATABASE_OBJECT_NAME = SUBT.DATABASE_OBJECT_NAME
432 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
433 and (SUBB.NAME <> SUBT.NAME
434 or (SUBB.NAME is null and SUBT.NAME is not null)
435 or (SUBB.NAME is not null and SUBT.NAME is null)
436 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
437 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
438 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
439 ));
440 */
441
442 insert /*+ append parallel(tt) */ into AK_OBJECTS_TL tt (
443 DATABASE_OBJECT_NAME,
444 NAME,
445 DESCRIPTION,
446 CREATED_BY,
447 CREATION_DATE,
448 LAST_UPDATED_BY,
449 LAST_UPDATE_DATE,
450 LAST_UPDATE_LOGIN,
451 LANGUAGE,
452 SOURCE_LANG
453 ) select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
454 (select /*+ no_merge ordered parallel(b) */
455 B.DATABASE_OBJECT_NAME,
456 B.NAME,
457 B.DESCRIPTION,
458 B.CREATED_BY,
459 B.CREATION_DATE,
460 B.LAST_UPDATED_BY,
461 B.LAST_UPDATE_DATE,
462 B.LAST_UPDATE_LOGIN,
463 L.LANGUAGE_CODE,
464 B.SOURCE_LANG
465 from AK_OBJECTS_TL B, FND_LANGUAGES L
466 where L.INSTALLED_FLAG in ('I', 'B')
467 and B.LANGUAGE = userenv('LANG')
468 ) v, AK_OBJECTS_TL T
469 where T.DATABASE_OBJECT_NAME(+) = v.DATABASE_OBJECT_NAME
470 and T.LANGUAGE(+) = v.LANGUAGE_CODE
471 and T.DATABASE_OBJECT_NAME is NULL;
472
473 end ADD_LANGUAGE;
474
475 end AK_OBJECTS_PKG;
[Home] [Help]