1 package body WF_ACTIVITY_ATTRIBUTES_PKG as
2 /* $Header: wfacab.pls 120.2 2005/07/02 03:41:27 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ACTIVITY_ITEM_TYPE in VARCHAR2,
6 X_ACTIVITY_NAME in VARCHAR2,
7 X_ACTIVITY_VERSION in NUMBER,
8 X_NAME in VARCHAR2,
9 X_SEQUENCE in NUMBER,
10 X_TYPE in VARCHAR2,
11 X_VALUE_TYPE in VARCHAR2,
12 X_PROTECT_LEVEL in NUMBER,
13 X_CUSTOM_LEVEL in NUMBER,
14 X_SUBTYPE in VARCHAR2,
15 X_FORMAT in VARCHAR2,
16 X_TEXT_DEFAULT in VARCHAR2,
17 X_NUMBER_DEFAULT in NUMBER,
18 X_DATE_DEFAULT in DATE,
19 X_DISPLAY_NAME in VARCHAR2,
20 X_DESCRIPTION in VARCHAR2
21 ) is
22 cursor C is select ROWID from WF_ACTIVITY_ATTRIBUTES
23 where ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
24 and ACTIVITY_NAME = X_ACTIVITY_NAME
25 and ACTIVITY_VERSION = X_ACTIVITY_VERSION
26 and NAME = X_NAME
27 ;
28 old_version number default '';
29 begin
30 insert into WF_ACTIVITY_ATTRIBUTES (
31 ACTIVITY_ITEM_TYPE,
32 ACTIVITY_NAME,
33 ACTIVITY_VERSION,
34 NAME,
35 SEQUENCE,
36 TYPE,
37 VALUE_TYPE,
38 PROTECT_LEVEL,
39 CUSTOM_LEVEL,
40 SUBTYPE,
41 FORMAT,
42 TEXT_DEFAULT,
43 NUMBER_DEFAULT,
44 DATE_DEFAULT
45 ) values (
46 X_ACTIVITY_ITEM_TYPE,
47 X_ACTIVITY_NAME,
48 X_ACTIVITY_VERSION,
49 X_NAME,
50 X_SEQUENCE,
51 X_TYPE,
52 X_VALUE_TYPE,
53 X_PROTECT_LEVEL,
54 X_CUSTOM_LEVEL,
55 X_SUBTYPE,
56 X_FORMAT,
57 X_TEXT_DEFAULT,
58 X_NUMBER_DEFAULT,
59 X_DATE_DEFAULT
60 );
61
62 -- *** VERSION CUSTOMIZATION
63 -- Insert translations. Default the translations for all but the
64 -- current language from a previous version of this attribute, if one
65 -- is available.
66 -- Note: Use _VL instead of base table to prevent _tl integrity errors
67 -- from propagating.
68 select max(WAA.ACTIVITY_VERSION)
69 into old_version
70 from WF_ACTIVITY_ATTRIBUTES_TL WAA
71 where WAA.NAME = X_NAME
72 and WAA.ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
73 and WAA.ACTIVITY_NAME = X_ACTIVITY_NAME
74 and WAA.ACTIVITY_VERSION < X_ACTIVITY_VERSION;
75
76 if (old_version is not null) then
77
78 insert into WF_ACTIVITY_ATTRIBUTES_TL (
79 ACTIVITY_ITEM_TYPE,
80 ACTIVITY_NAME,
81 ACTIVITY_VERSION,
82 NAME,
83 DISPLAY_NAME,
84 PROTECT_LEVEL,
85 CUSTOM_LEVEL,
86 DESCRIPTION,
87 LANGUAGE,
88 SOURCE_LANG
89 ) select
90 X_ACTIVITY_ITEM_TYPE,
91 X_ACTIVITY_NAME,
92 X_ACTIVITY_VERSION,
93 X_NAME,
94 decode(L.CODE,
95 userenv('LANG'), X_DISPLAY_NAME,
96 OLD.DISPLAY_NAME),
97 X_PROTECT_LEVEL,
98 X_CUSTOM_LEVEL,
99 decode(L.CODE,
100 userenv('LANG'), X_DESCRIPTION,
101 OLD.DESCRIPTION),
102 L.CODE,
103 decode(L.CODE,
104 userenv('LANG'), L.CODE,
105 OLD.SOURCE_LANG)
106 from WF_LANGUAGES L, WF_ACTIVITY_ATTRIBUTES_TL OLD
107 where L.INSTALLED_FLAG = 'Y'
108 and OLD.ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
109 and OLD.ACTIVITY_NAME = X_ACTIVITY_NAME
110 and OLD.ACTIVITY_VERSION = old_version
111 and OLD.NAME = X_NAME
112 and OLD.LANGUAGE = L.CODE
113 and not exists
114 (select NULL
115 from WF_ACTIVITY_ATTRIBUTES_TL T
116 where T.ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
117 and T.ACTIVITY_NAME = X_ACTIVITY_NAME
118 and T.ACTIVITY_VERSION = X_ACTIVITY_VERSION
119 and T.NAME = X_NAME
120 and T.LANGUAGE = L.CODE);
121
122 else
123
124 insert into WF_ACTIVITY_ATTRIBUTES_TL (
125 ACTIVITY_ITEM_TYPE,
126 ACTIVITY_NAME,
127 ACTIVITY_VERSION,
128 NAME,
129 DISPLAY_NAME,
130 PROTECT_LEVEL,
131 CUSTOM_LEVEL,
132 DESCRIPTION,
133 LANGUAGE,
134 SOURCE_LANG
135 ) select
136 X_ACTIVITY_ITEM_TYPE,
137 X_ACTIVITY_NAME,
138 X_ACTIVITY_VERSION,
139 X_NAME,
140 X_DISPLAY_NAME,
141 X_PROTECT_LEVEL,
142 X_CUSTOM_LEVEL,
143 X_DESCRIPTION,
144 L.CODE,
145 userenv('LANG')
146 from WF_LANGUAGES L
147 where L.INSTALLED_FLAG = 'Y'
148 and not exists
149 (select NULL
150 from WF_ACTIVITY_ATTRIBUTES_TL T
151 where T.ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
152 and T.ACTIVITY_NAME = X_ACTIVITY_NAME
153 and T.ACTIVITY_VERSION = X_ACTIVITY_VERSION
154 and T.NAME = X_NAME
155 and T.LANGUAGE = L.CODE);
156
157 end if;
158
159 open c;
160 fetch c into X_ROWID;
161 if (c%notfound) then
162 close c;
163 raise no_data_found;
164 end if;
165 close c;
166
167 exception
168 when others then
169 wf_core.context('Wf_Activity_Attributes_Pkg', 'Insert_Row',
170 x_activity_item_type, x_activity_name, to_char(x_activity_version),
171 x_name);
172 raise;
173 end INSERT_ROW;
174
175 procedure LOCK_ROW (
176 X_ACTIVITY_ITEM_TYPE in VARCHAR2,
177 X_ACTIVITY_NAME in VARCHAR2,
178 X_ACTIVITY_VERSION in NUMBER,
179 X_NAME in VARCHAR2,
180 X_SEQUENCE in NUMBER,
181 X_TYPE in VARCHAR2,
182 X_VALUE_TYPE in VARCHAR2,
183 X_PROTECT_LEVEL in NUMBER,
184 X_CUSTOM_LEVEL in NUMBER,
185 X_SUBTYPE in VARCHAR2,
186 X_FORMAT in VARCHAR2,
187 X_TEXT_DEFAULT in VARCHAR2,
188 X_NUMBER_DEFAULT in NUMBER,
189 X_DATE_DEFAULT in DATE,
190 X_DISPLAY_NAME in VARCHAR2,
191 X_DESCRIPTION in VARCHAR2
192 ) is
193 cursor c is select
194 SEQUENCE,
195 TYPE,
196 VALUE_TYPE,
197 PROTECT_LEVEL,
198 CUSTOM_LEVEL,
199 SUBTYPE,
200 FORMAT,
201 TEXT_DEFAULT,
202 NUMBER_DEFAULT,
203 DATE_DEFAULT
204 from WF_ACTIVITY_ATTRIBUTES
205 where ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
206 and ACTIVITY_NAME = X_ACTIVITY_NAME
207 and ACTIVITY_VERSION = X_ACTIVITY_VERSION
208 and NAME = X_NAME
209 for update of ACTIVITY_ITEM_TYPE nowait;
210 recinfo c%rowtype;
211
212 cursor c1 is select
213 DISPLAY_NAME,
214 DESCRIPTION
215 from WF_ACTIVITY_ATTRIBUTES_TL
216 where ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
217 and ACTIVITY_NAME = X_ACTIVITY_NAME
218 and ACTIVITY_VERSION = X_ACTIVITY_VERSION
219 and NAME = X_NAME
220 and LANGUAGE = userenv('LANG')
221 for update of ACTIVITY_ITEM_TYPE nowait;
222 tlinfo c1%rowtype;
223
224 begin
225 open c;
226 fetch c into recinfo;
227 if (c%notfound) then
228 close c;
229 wf_core.raise('WF_RECORD_DELETED');
230 end if;
231 close c;
232 if ( (recinfo.SEQUENCE = X_SEQUENCE)
233 AND (recinfo.TYPE = X_TYPE)
234 AND (recinfo.VALUE_TYPE = X_VALUE_TYPE)
235 AND (recinfo.PROTECT_LEVEL = X_PROTECT_LEVEL)
236 AND (recinfo.CUSTOM_LEVEL = X_CUSTOM_LEVEL)
237 AND ((recinfo.SUBTYPE = X_SUBTYPE)
238 OR ((recinfo.SUBTYPE is null) AND (X_SUBTYPE is null)))
239 AND ((recinfo.FORMAT = X_FORMAT)
240 OR ((recinfo.FORMAT is null) AND (X_FORMAT is null)))
241 AND ((recinfo.TEXT_DEFAULT = X_TEXT_DEFAULT)
242 OR ((recinfo.TEXT_DEFAULT is null) AND (X_TEXT_DEFAULT is null)))
243 AND ((recinfo.NUMBER_DEFAULT = X_NUMBER_DEFAULT)
244 OR ((recinfo.NUMBER_DEFAULT is null) AND (X_NUMBER_DEFAULT is null)))
245 AND ((recinfo.DATE_DEFAULT = X_DATE_DEFAULT)
246 OR ((recinfo.DATE_DEFAULT is null) AND (X_DATE_DEFAULT is null)))
247 ) then
248 null;
249 else
250 wf_core.raise('WF_RECORD_CHANGED');
251 end if;
252
253 open c1;
254 fetch c1 into tlinfo;
255 if (c1%notfound) then
256 close c1;
257 return;
258 end if;
259 close c1;
260
261 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
262 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
263 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
264 ) then
265 null;
266 else
267 wf_core.raise('WF_RECORD_CHANGED');
268 end if;
269 return;
270
271 exception
272 when others then
273 wf_core.context('Wf_Activity_Attributes_Pkg', 'Lock_Row',
274 x_activity_item_type, x_activity_name, to_char(x_activity_version),
275 x_name);
276 raise;
277 end LOCK_ROW;
278
279 procedure UPDATE_ROW (
280 X_ACTIVITY_ITEM_TYPE in VARCHAR2,
281 X_ACTIVITY_NAME in VARCHAR2,
282 X_ACTIVITY_VERSION in NUMBER,
283 X_NAME in VARCHAR2,
284 X_SEQUENCE in NUMBER,
285 X_TYPE in VARCHAR2,
286 X_VALUE_TYPE in VARCHAR2,
287 X_PROTECT_LEVEL in NUMBER,
288 X_CUSTOM_LEVEL in NUMBER,
289 X_SUBTYPE in VARCHAR2,
290 X_FORMAT in VARCHAR2,
291 X_TEXT_DEFAULT in VARCHAR2,
292 X_NUMBER_DEFAULT in NUMBER,
293 X_DATE_DEFAULT in DATE,
294 X_DISPLAY_NAME in VARCHAR2,
295 X_DESCRIPTION in VARCHAR2
296 ) is
297 begin
298 update WF_ACTIVITY_ATTRIBUTES set
299 SEQUENCE = X_SEQUENCE,
300 TYPE = X_TYPE,
301 VALUE_TYPE = X_VALUE_TYPE,
302 PROTECT_LEVEL = X_PROTECT_LEVEL,
303 CUSTOM_LEVEL = X_CUSTOM_LEVEL,
304 SUBTYPE = X_SUBTYPE,
305 FORMAT = X_FORMAT,
306 TEXT_DEFAULT = X_TEXT_DEFAULT,
307 NUMBER_DEFAULT = X_NUMBER_DEFAULT,
308 DATE_DEFAULT = X_DATE_DEFAULT
309 where ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
310 and ACTIVITY_NAME = X_ACTIVITY_NAME
311 and ACTIVITY_VERSION = X_ACTIVITY_VERSION
312 and NAME = X_NAME;
313
314 if (sql%notfound) then
315 raise no_data_found;
316 end if;
317
318 update WF_ACTIVITY_ATTRIBUTES_TL set
319 DISPLAY_NAME = X_DISPLAY_NAME,
320 DESCRIPTION = X_DESCRIPTION,
321 SOURCE_LANG = userenv('LANG')
322 where ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
323 and ACTIVITY_NAME = X_ACTIVITY_NAME
324 and ACTIVITY_VERSION = X_ACTIVITY_VERSION
325 and NAME = X_NAME
326 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
327
328 if (sql%notfound) then
329 raise no_data_found;
330 end if;
331
332 exception
333 when others then
334 wf_core.context('Wf_Activity_Attributes_Pkg', 'Update_Row',
335 x_activity_item_type, x_activity_name, to_char(x_activity_version),
336 x_name);
337 raise;
338 end UPDATE_ROW;
339
340 procedure DELETE_ROW (
341 X_ACTIVITY_ITEM_TYPE in VARCHAR2,
342 X_ACTIVITY_NAME in VARCHAR2,
343 X_ACTIVITY_VERSION in NUMBER,
344 X_NAME in VARCHAR2
345 ) is
346 begin
347 delete from WF_ACTIVITY_ATTRIBUTES_TL
348 where ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
349 and ACTIVITY_NAME = X_ACTIVITY_NAME
350 and ACTIVITY_VERSION = X_ACTIVITY_VERSION
351 and NAME = X_NAME;
352
353 if (sql%notfound) then
354 raise no_data_found;
355 end if;
356
357 delete from WF_ACTIVITY_ATTRIBUTES
358 where ACTIVITY_ITEM_TYPE = X_ACTIVITY_ITEM_TYPE
359 and ACTIVITY_NAME = X_ACTIVITY_NAME
360 and ACTIVITY_VERSION = X_ACTIVITY_VERSION
361 and NAME = X_NAME;
362
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366
367 exception
368 when others then
369 wf_core.context('Wf_Activity_Attributes_Pkg', 'Delete_Row',
370 x_activity_item_type, x_activity_name, to_char(x_activity_version),
371 x_name);
372 raise;
373 end DELETE_ROW;
374
375 procedure ADD_LANGUAGE
376 is
377 begin
378 /* Mar/19/03 requested by Ric Ginsberg */
379 /* The following delete and update statements are commented out */
380 /* as a quick workaround to fix the time-consuming table handler issue */
381 /* Eventually we'll need to turn them into a separate fix_language procedure */
382 /*
383
384 delete from WF_ACTIVITY_ATTRIBUTES_TL T
385 where not exists
386 (select NULL
387 from WF_ACTIVITY_ATTRIBUTES B
388 where B.ACTIVITY_ITEM_TYPE = T.ACTIVITY_ITEM_TYPE
389 and B.ACTIVITY_NAME = T.ACTIVITY_NAME
390 and B.ACTIVITY_VERSION = T.ACTIVITY_VERSION
391 and B.NAME = T.NAME
392 );
393
394 update WF_ACTIVITY_ATTRIBUTES_TL T set (
395 DISPLAY_NAME,
396 DESCRIPTION
397 ) = (select
398 B.DISPLAY_NAME,
399 B.DESCRIPTION
400 from WF_ACTIVITY_ATTRIBUTES_TL B
401 where B.ACTIVITY_ITEM_TYPE = T.ACTIVITY_ITEM_TYPE
402 and B.ACTIVITY_NAME = T.ACTIVITY_NAME
403 and B.ACTIVITY_VERSION = T.ACTIVITY_VERSION
404 and B.NAME = T.NAME
405 and B.LANGUAGE = T.SOURCE_LANG)
406 where (
407 T.ACTIVITY_ITEM_TYPE,
408 T.ACTIVITY_NAME,
409 T.ACTIVITY_VERSION,
410 T.NAME,
414 SUBT.ACTIVITY_NAME,
411 T.LANGUAGE
412 ) in (select
413 SUBT.ACTIVITY_ITEM_TYPE,
418 from WF_ACTIVITY_ATTRIBUTES_TL SUBB, WF_ACTIVITY_ATTRIBUTES_TL SUBT
415 SUBT.ACTIVITY_VERSION,
416 SUBT.NAME,
417 SUBT.LANGUAGE
419 where SUBB.ACTIVITY_ITEM_TYPE = SUBT.ACTIVITY_ITEM_TYPE
420 and SUBB.ACTIVITY_NAME = SUBT.ACTIVITY_NAME
421 and SUBB.ACTIVITY_VERSION = SUBT.ACTIVITY_VERSION
422 and SUBB.NAME = SUBT.NAME
423 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
424 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
425 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
426 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
427 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
428 ));
429 */
430
431 insert /*+ append parallel */ into WF_ACTIVITY_ATTRIBUTES_TL (
432 ACTIVITY_ITEM_TYPE,
433 ACTIVITY_NAME,
434 ACTIVITY_VERSION,
435 NAME,
436 DISPLAY_NAME,
437 PROTECT_LEVEL,
438 CUSTOM_LEVEL,
439 DESCRIPTION,
440 LANGUAGE,
441 SOURCE_LANG
442 ) select
443 B.ACTIVITY_ITEM_TYPE,
444 B.ACTIVITY_NAME,
445 B.ACTIVITY_VERSION,
446 B.NAME,
447 B.DISPLAY_NAME,
448 B.PROTECT_LEVEL,
449 B.CUSTOM_LEVEL,
450 B.DESCRIPTION,
451 L.CODE,
452 B.SOURCE_LANG
453 from WF_ACTIVITY_ATTRIBUTES_TL B, WF_LANGUAGES L
454 where L.INSTALLED_FLAG = 'Y'
455 and B.LANGUAGE = userenv('LANG')
456 and (B.ACTIVITY_ITEM_TYPE, B.ACTIVITY_NAME, B.ACTIVITY_VERSION , B.NAME,
457 L.CODE) NOT IN
458 (select /*+ hash_aj index_ffs(T,WF_ACTIVITY_ATTRIBUTES_TL_PK) */
459 T.ACTIVITY_ITEM_TYPE ,T.ACTIVITY_NAME ,T.ACTIVITY_VERSION , T.NAME , T.LANGUAGE from WF_ACTIVITY_ATTRIBUTES_TL T );
460 end ADD_LANGUAGE;
461
462 end WF_ACTIVITY_ATTRIBUTES_PKG;
[Home] [Help]