1 package body WF_ACTIVITIES_PKG as
2 /* $Header: wfactb.pls 120.3 2006/08/24 06:59:16 hgandiko ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ITEM_TYPE in VARCHAR2,
6 X_NAME in VARCHAR2,
7 X_VERSION in NUMBER,
8 X_TYPE in VARCHAR2,
9 X_RERUN in VARCHAR2,
10 X_EXPAND_ROLE in VARCHAR2,
11 X_PROTECT_LEVEL in NUMBER,
12 X_CUSTOM_LEVEL in NUMBER,
13 X_BEGIN_DATE in DATE,
14 X_END_DATE in DATE,
15 X_FUNCTION in VARCHAR2,
16 X_FUNCTION_TYPE in VARCHAR2,
17 X_RESULT_TYPE in VARCHAR2,
18 X_COST in NUMBER,
19 X_READ_ROLE in VARCHAR2,
20 X_WRITE_ROLE in VARCHAR2,
21 X_EXECUTE_ROLE in VARCHAR2,
22 X_ICON_NAME in VARCHAR2,
23 X_MESSAGE in VARCHAR2,
24 X_ERROR_PROCESS in VARCHAR2,
25 X_DISPLAY_NAME in VARCHAR2,
26 X_DESCRIPTION in VARCHAR2,
27 X_ERROR_ITEM_TYPE in VARCHAR2,
28 X_RUNNABLE_FLAG in VARCHAR2,
29 X_EVENT_FILTER in VARCHAR2 ,
30 X_EVENT_TYPE in VARCHAR2
31 ) is
32 cursor C is select ROWID from WF_ACTIVITIES
33 where ITEM_TYPE = X_ITEM_TYPE
34 and NAME = X_NAME
35 and VERSION = X_VERSION
36 ;
37 old_version number default '';
38 dummy number;
39 begin
40 insert into WF_ACTIVITIES (
41 ITEM_TYPE,
42 NAME,
43 VERSION,
44 TYPE,
45 RERUN,
46 EXPAND_ROLE,
47 PROTECT_LEVEL,
48 CUSTOM_LEVEL,
49 BEGIN_DATE,
50 END_DATE,
51 FUNCTION,
52 FUNCTION_TYPE,
53 RESULT_TYPE,
54 COST,
55 READ_ROLE,
56 WRITE_ROLE,
57 EXECUTE_ROLE,
58 ICON_NAME,
59 MESSAGE,
60 ERROR_PROCESS ,
61 ERROR_ITEM_TYPE,
62 RUNNABLE_FLAG,
63 EVENT_NAME,
64 DIRECTION
65 ) values (
66 X_ITEM_TYPE,
67 X_NAME,
68 X_VERSION,
69 X_TYPE,
70 X_RERUN,
71 X_EXPAND_ROLE,
72 X_PROTECT_LEVEL,
73 X_CUSTOM_LEVEL,
74 X_BEGIN_DATE,
75 X_END_DATE,
76 X_FUNCTION,
77 X_FUNCTION_TYPE,
78 X_RESULT_TYPE,
79 X_COST,
80 X_READ_ROLE,
81 X_WRITE_ROLE,
82 X_EXECUTE_ROLE,
83 X_ICON_NAME,
84 X_MESSAGE,
85 X_ERROR_PROCESS,
86 X_ERROR_ITEM_TYPE,
87 X_RUNNABLE_FLAG,
88 X_EVENT_FILTER,
89 X_EVENT_TYPE
90 );
91
92 -- *** VERSION CUSTOMIZATION
93 -- Insert translations. Default the translations for all but the
94 -- current language from a previous version of this activity, if one
95 -- is available.
96 -- Note: Use _VL instead of base table to prevent _tl integrity errors
97 -- from propagating.
98 select max(WA.VERSION)
99 into old_version
100 from WF_ACTIVITIES_VL WA
101 where WA.ITEM_TYPE = X_ITEM_TYPE
102 and WA.NAME = X_NAME
103 and WA.VERSION < X_VERSION;
104
105 if (old_version is not null) then
106 insert into WF_ACTIVITIES_TL (
107 ITEM_TYPE,
108 NAME,
109 VERSION,
110 DISPLAY_NAME,
111 PROTECT_LEVEL,
112 CUSTOM_LEVEL,
113 DESCRIPTION,
114 LANGUAGE,
115 SOURCE_LANG
116 ) select
117 X_ITEM_TYPE,
118 X_NAME,
119 X_VERSION,
120 decode(L.CODE,
121 userenv('LANG'), X_DISPLAY_NAME,
122 OLD.DISPLAY_NAME),
123 X_PROTECT_LEVEL,
124 X_CUSTOM_LEVEL,
125 decode(L.CODE,
126 userenv('LANG'), X_DESCRIPTION,
127 OLD.DESCRIPTION),
128 L.CODE,
129 decode(L.CODE,
130 userenv('LANG'), L.CODE,
131 OLD.SOURCE_LANG)
132 from WF_LANGUAGES L, WF_ACTIVITIES_TL OLD
133 where L.INSTALLED_FLAG = 'Y'
134 and OLD.ITEM_TYPE = X_ITEM_TYPE
135 and OLD.NAME = X_NAME
136 and OLD.VERSION = old_version
137 and OLD.LANGUAGE = L.CODE
138 and not exists
139 (select NULL
140 from WF_ACTIVITIES_TL T
141 where T.ITEM_TYPE = X_ITEM_TYPE
142 and T.NAME = X_NAME
143 and T.VERSION = X_VERSION
144 and T.LANGUAGE = L.CODE);
145 else
146 -- No other versions, default translations for all languages from
147 -- the current language.
148 insert into WF_ACTIVITIES_TL (
149 ITEM_TYPE,
150 NAME,
151 VERSION,
152 DISPLAY_NAME,
153 PROTECT_LEVEL,
154 CUSTOM_LEVEL,
155 DESCRIPTION,
156 LANGUAGE,
157 SOURCE_LANG
158 ) select
159 X_ITEM_TYPE,
160 X_NAME,
161 X_VERSION,
162 X_DISPLAY_NAME,
163 X_PROTECT_LEVEL,
164 X_CUSTOM_LEVEL,
165 X_DESCRIPTION,
166 L.CODE,
167 userenv('LANG')
168 from WF_LANGUAGES L
169 where L.INSTALLED_FLAG = 'Y'
170 and not exists
171 (select NULL
172 from WF_ACTIVITIES_TL T
173 where T.ITEM_TYPE = X_ITEM_TYPE
174 and T.NAME = X_NAME
175 and T.VERSION = X_VERSION
176 and T.LANGUAGE = L.CODE);
177
178 end if;
179
180 -- *** VERSION CUSTOMIZATION
181 -- Check rows just inserted for duplicate display_names among activities
182 -- within this itemtype over all versions active during the time
183 -- range of the version being added.
184 -- It is not sufficient to rely on WF_ACTIVITIES_TL_U2 unique index,
185 -- because versions may mask some duplicates.
186 begin
187 select /*+ leading(NEW,NEWTL,OLDTL,OLD) use_nl(NEWTL,OLDTL,OLD) */
188 OLD.NAME||':'||OLDTL.LANGUAGE||':'||OLDTL.DISPLAY_NAME
189 into Wf_Load.logbuf
190 from WF_ACTIVITIES NEW, WF_ACTIVITIES OLD,
191 WF_ACTIVITIES_TL NEWTL, WF_ACTIVITIES_TL OLDTL
192 where NEW.ITEM_TYPE = NEWTL.ITEM_TYPE
193 and NEW.NAME = NEWTL.NAME
194 and NEW.VERSION = NEWTL.VERSION
195 and OLD.ITEM_TYPE = OLDTL.ITEM_TYPE
196 and OLD.NAME = OLDTL.NAME
197 and OLD.VERSION = OLDTL.VERSION
198 and NEW.ITEM_TYPE = x_item_type
199 and NEW.NAME = x_name
200 and NEW.VERSION = x_version
201 and NEW.BEGIN_DATE < nvl(OLD.END_DATE, NEW.BEGIN_DATE+1)
202 and nvl(NEW.END_DATE, OLD.BEGIN_DATE+1) > OLD.BEGIN_DATE
203 and OLDTL.DISPLAY_NAME = NEWTL.DISPLAY_NAME
204 and OLD.ITEM_TYPE = NEW.ITEM_TYPE
205 and OLDTL.LANGUAGE = NEWTL.LANGUAGE
206 and OLDTL.ROWID <> NEWTL.ROWID
207 and rownum < 2;
208
209 exception
210 when no_data_found then
211 null;
212 -- No bad rows exist. Joy.
213 end;
214
215 open c;
216 fetch c into X_ROWID;
217 if (c%notfound) then
218 close c;
219 raise no_data_found;
220 end if;
221 close c;
222
223 exception
224 when others then
225 wf_core.context('Wf_Activities_Pkg', 'Insert_Row', x_item_type,
226 x_name, to_char(x_version));
227 raise;
228 end INSERT_ROW;
229
230 procedure LOCK_ROW (
231 X_ITEM_TYPE in VARCHAR2,
232 X_NAME in VARCHAR2,
233 X_VERSION in NUMBER,
234 X_TYPE in VARCHAR2,
235 X_RERUN in VARCHAR2,
236 X_EXPAND_ROLE in VARCHAR2,
237 X_PROTECT_LEVEL in NUMBER,
238 X_CUSTOM_LEVEL in NUMBER,
239 X_BEGIN_DATE in DATE,
240 X_END_DATE in DATE,
241 X_FUNCTION in VARCHAR2,
242 X_RESULT_TYPE in VARCHAR2,
243 X_COST in NUMBER,
244 X_READ_ROLE in VARCHAR2,
245 X_WRITE_ROLE in VARCHAR2,
246 X_EXECUTE_ROLE in VARCHAR2,
247 X_ICON_NAME in VARCHAR2,
248 X_MESSAGE in VARCHAR2,
249 X_ERROR_PROCESS in VARCHAR2,
250 X_DISPLAY_NAME in VARCHAR2,
251 X_DESCRIPTION in VARCHAR2
252 ) is
253 cursor c is select
254 TYPE,
255 RERUN,
256 EXPAND_ROLE,
257 PROTECT_LEVEL,
258 CUSTOM_LEVEL,
259 BEGIN_DATE,
260 END_DATE,
261 FUNCTION,
262 RESULT_TYPE,
263 COST,
264 READ_ROLE,
265 WRITE_ROLE,
266 EXECUTE_ROLE,
267 ICON_NAME,
268 MESSAGE,
269 ERROR_PROCESS
270 from WF_ACTIVITIES
271 where ITEM_TYPE = X_ITEM_TYPE
272 and NAME = X_NAME
273 and VERSION = X_VERSION
274 for update of ITEM_TYPE nowait;
275 recinfo c%rowtype;
276
277 cursor c1 is select
278 DISPLAY_NAME,
279 DESCRIPTION
280 from WF_ACTIVITIES_TL
281 where ITEM_TYPE = X_ITEM_TYPE
282 and NAME = X_NAME
283 and VERSION = X_VERSION
284 and LANGUAGE = userenv('LANG')
285 for update of ITEM_TYPE nowait;
286 tlinfo c1%rowtype;
287
288 begin
289 open c;
290 fetch c into recinfo;
291 if (c%notfound) then
292 close c;
293 wf_core.raise('WF_RECORD_DELETED');
294 end if;
295 close c;
296 if ( (recinfo.TYPE = X_TYPE)
297 AND (recinfo.RERUN = X_RERUN)
298 AND (recinfo.EXPAND_ROLE = X_EXPAND_ROLE)
299 AND (recinfo.PROTECT_LEVEL = X_PROTECT_LEVEL)
300 AND (recinfo.CUSTOM_LEVEL = X_CUSTOM_LEVEL)
301 AND (recinfo.BEGIN_DATE = X_BEGIN_DATE)
302 AND ((recinfo.END_DATE = X_END_DATE)
303 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
304 AND ((recinfo.FUNCTION = X_FUNCTION)
305 OR ((recinfo.FUNCTION is null) AND (X_FUNCTION is null)))
306 AND ((recinfo.RESULT_TYPE = X_RESULT_TYPE)
307 OR ((recinfo.RESULT_TYPE is null) AND (X_RESULT_TYPE is null)))
308 AND ((recinfo.COST = X_COST)
309 OR ((recinfo.COST is null) AND (X_COST is null)))
310 AND ((recinfo.READ_ROLE = X_READ_ROLE)
311 OR ((recinfo.READ_ROLE is null) AND (X_READ_ROLE is null)))
312 AND ((recinfo.WRITE_ROLE = X_WRITE_ROLE)
313 OR ((recinfo.WRITE_ROLE is null) AND (X_WRITE_ROLE is null)))
314 AND ((recinfo.EXECUTE_ROLE = X_EXECUTE_ROLE)
315 OR ((recinfo.EXECUTE_ROLE is null) AND (X_EXECUTE_ROLE is null)))
316 AND ((recinfo.ICON_NAME = X_ICON_NAME)
317 OR ((recinfo.ICON_NAME is null) AND (X_ICON_NAME is null)))
318 AND ((recinfo.MESSAGE = X_MESSAGE)
319 OR ((recinfo.MESSAGE is null) AND (X_MESSAGE is null)))
320 AND ((recinfo.ERROR_PROCESS = X_ERROR_PROCESS)
321 OR ((recinfo.ERROR_PROCESS is null) AND (X_ERROR_PROCESS is null)))
322 ) then
323 null;
324 else
325 wf_core.raise('WF_RECORD_CHANGED');
326 end if;
327
328 open c1;
329 fetch c1 into tlinfo;
330 if (c1%notfound) then
331 close c1;
332 return;
333 end if;
334 close c1;
335
336 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
337 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
338 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
339 ) then
340 null;
341 else
342 wf_core.raise('WF_RECORD_CHANGED');
343 end if;
344 return;
345
346 exception
347 when others then
348 wf_core.context('Wf_Activities_Pkg', 'Lock_Row', x_item_type,
349 x_name, to_char(x_version));
350 raise;
351 end LOCK_ROW;
352
353 procedure UPDATE_ROW (
354 X_ITEM_TYPE in VARCHAR2,
355 X_NAME in VARCHAR2,
356 X_VERSION in NUMBER,
357 X_TYPE in VARCHAR2,
358 X_RERUN in VARCHAR2,
359 X_EXPAND_ROLE in VARCHAR2,
360 X_PROTECT_LEVEL in NUMBER,
361 X_CUSTOM_LEVEL in NUMBER,
362 X_BEGIN_DATE in DATE,
363 X_END_DATE in DATE,
364 X_FUNCTION in VARCHAR2,
365 X_RESULT_TYPE in VARCHAR2,
366 X_COST in NUMBER,
367 X_READ_ROLE in VARCHAR2,
368 X_WRITE_ROLE in VARCHAR2,
369 X_EXECUTE_ROLE in VARCHAR2,
370 X_ICON_NAME in VARCHAR2,
371 X_MESSAGE in VARCHAR2,
372 X_ERROR_PROCESS in VARCHAR2,
373 X_DISPLAY_NAME in VARCHAR2,
374 X_DESCRIPTION in VARCHAR2
375 ) is
376 begin
377 update WF_ACTIVITIES set
378 TYPE = X_TYPE,
379 RERUN = X_RERUN,
380 EXPAND_ROLE = X_EXPAND_ROLE,
381 PROTECT_LEVEL = X_PROTECT_LEVEL,
382 CUSTOM_LEVEL = X_CUSTOM_LEVEL,
383 BEGIN_DATE = X_BEGIN_DATE,
384 END_DATE = X_END_DATE,
385 FUNCTION = X_FUNCTION,
386 RESULT_TYPE = X_RESULT_TYPE,
387 COST = X_COST,
388 READ_ROLE = X_READ_ROLE,
389 WRITE_ROLE = X_WRITE_ROLE,
390 EXECUTE_ROLE = X_EXECUTE_ROLE,
391 ICON_NAME = X_ICON_NAME,
392 MESSAGE = X_MESSAGE,
393 ERROR_PROCESS = X_ERROR_PROCESS
394 where ITEM_TYPE = X_ITEM_TYPE
395 and NAME = X_NAME
396 and VERSION = X_VERSION;
397
398 if (sql%notfound) then
399 raise no_data_found;
400 end if;
401
402 update WF_ACTIVITIES_TL set
403 DISPLAY_NAME = X_DISPLAY_NAME,
404 DESCRIPTION = X_DESCRIPTION,
405 SOURCE_LANG = userenv('LANG')
406 where ITEM_TYPE = X_ITEM_TYPE
407 and NAME = X_NAME
408 and VERSION = X_VERSION
409 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
410
411 if (sql%notfound) then
412 raise no_data_found;
413 end if;
414
415 exception
416 when others then
417 wf_core.context('Wf_Activities_Pkg', 'Update_Row', x_item_type,
418 x_name, to_char(x_version));
419 raise;
420 end UPDATE_ROW;
421
422 procedure DELETE_ROW (
423 X_ITEM_TYPE in VARCHAR2,
424 X_NAME in VARCHAR2,
425 X_VERSION in NUMBER
426 ) is
427 begin
428 delete from WF_ACTIVITIES_TL
429 where ITEM_TYPE = X_ITEM_TYPE
430 and NAME = X_NAME
431 and VERSION = X_VERSION;
432
433 if (sql%notfound) then
434 raise no_data_found;
435 end if;
436
437 delete from WF_ACTIVITIES
438 where ITEM_TYPE = X_ITEM_TYPE
439 and NAME = X_NAME
440 and VERSION = X_VERSION;
441
442 if (sql%notfound) then
443 raise no_data_found;
444 end if;
445
446 exception
447 when others then
448 wf_core.context('Wf_Activities_Pkg', 'Delete_Row', x_item_type,
449 x_name, to_char(x_version));
450 raise;
451 end DELETE_ROW;
452
453 procedure ADD_LANGUAGE
454 is
455 begin
456 /* Mar/19/03 requested by Ric Ginsberg */
457 /* The following delete and update statements are commented out */
458 /* as a quick workaround to fix the time-consuming table handler issue */
459 /* Eventually we'll need to turn them into a separate fix_language procedure */
460 /*
461
462 delete from WF_ACTIVITIES_TL T
463 where not exists
464 (select NULL
465 from WF_ACTIVITIES B
466 where B.ITEM_TYPE = T.ITEM_TYPE
467 and B.NAME = T.NAME
468 and B.VERSION = T.VERSION
469 );
470
471 update WF_ACTIVITIES_TL T set (
472 DISPLAY_NAME,
473 DESCRIPTION
474 ) = (select
475 B.DISPLAY_NAME,
476 B.DESCRIPTION
477 from WF_ACTIVITIES_TL B
478 where B.ITEM_TYPE = T.ITEM_TYPE
479 and B.NAME = T.NAME
480 and B.VERSION = T.VERSION
481 and B.LANGUAGE = T.SOURCE_LANG)
482 where (
483 T.ITEM_TYPE,
484 T.NAME,
485 T.VERSION,
486 T.LANGUAGE
487 ) in (select
488 SUBT.ITEM_TYPE,
489 SUBT.NAME,
490 SUBT.VERSION,
491 SUBT.LANGUAGE
492 from WF_ACTIVITIES_TL SUBB, WF_ACTIVITIES_TL SUBT
496 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
493 where SUBB.ITEM_TYPE = SUBT.ITEM_TYPE
494 and SUBB.NAME = SUBT.NAME
495 and SUBB.VERSION = SUBT.VERSION
497 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
498 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
499 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
500 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
501 ));
502 */
503
504 insert /*+ append parallel */ into WF_ACTIVITIES_TL (
505 ITEM_TYPE,
506 NAME,
507 VERSION,
508 DISPLAY_NAME,
509 PROTECT_LEVEL,
510 CUSTOM_LEVEL,
511 DESCRIPTION,
512 LANGUAGE,
513 SOURCE_LANG
514 ) select
515 B.ITEM_TYPE,
516 B.NAME,
517 B.VERSION,
518 B.DISPLAY_NAME,
519 B.PROTECT_LEVEL,
520 B.CUSTOM_LEVEL,
521 B.DESCRIPTION,
522 L.CODE,
523 B.SOURCE_LANG
524 from WF_ACTIVITIES_TL B, WF_LANGUAGES L
525 where L.INSTALLED_FLAG = 'Y'
526 and B.LANGUAGE = userenv('LANG')
527 and (B.ITEM_TYPE , b.name, B.VERSION , l.code) NOT IN
528 (select /*+ hash_aj index_ffs(T,WF_ACTIVITIES_TL_PK) */
529 T.ITEM_TYPE , T.NAME , T.VERSION ,T.LANGUAGE from WF_ACTIVITIES_TL T );
530 end ADD_LANGUAGE;
531
532 end WF_ACTIVITIES_PKG;
[Home] [Help]