1 package body WF_ITEM_TYPES_PKG as
2 /* $Header: wfittb.pls 120.3 2005/10/04 05:13:04 rtodi ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_NAME in VARCHAR2,
6 X_PROTECT_LEVEL in NUMBER,
7 X_CUSTOM_LEVEL in NUMBER,
8 X_WF_SELECTOR in VARCHAR2,
9 X_READ_ROLE in VARCHAR2,
10 X_WRITE_ROLE in VARCHAR2,
11 X_EXECUTE_ROLE in VARCHAR2,
12 X_DISPLAY_NAME in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2,
14 X_PERSISTENCE_TYPE in VARCHAR2,
15 X_PERSISTENCE_DAYS in NUMBER
16 ) is
17 cursor C is select ROWID from WF_ITEM_TYPES
18 where NAME = X_NAME
19 ;
20 begin
21 insert into WF_ITEM_TYPES (
22 NAME,
23 PROTECT_LEVEL,
24 CUSTOM_LEVEL,
25 WF_SELECTOR,
26 READ_ROLE,
27 WRITE_ROLE,
28 EXECUTE_ROLE,
29 PERSISTENCE_TYPE,
30 PERSISTENCE_DAYS
31 ) values (
32 X_NAME,
33 X_PROTECT_LEVEL,
34 X_CUSTOM_LEVEL,
35 X_WF_SELECTOR,
36 X_READ_ROLE,
37 X_WRITE_ROLE,
38 X_EXECUTE_ROLE,
39 X_PERSISTENCE_TYPE,
40 X_PERSISTENCE_DAYS
41 );
42
43 insert into WF_ITEM_TYPES_TL (
44 NAME,
45 DISPLAY_NAME,
46 PROTECT_LEVEL,
47 CUSTOM_LEVEL,
48 DESCRIPTION,
49 LANGUAGE,
50 SOURCE_LANG
51 ) select
52 X_NAME,
53 X_DISPLAY_NAME,
54 X_PROTECT_LEVEL,
55 X_CUSTOM_LEVEL,
56 X_DESCRIPTION,
57 L.CODE,
58 userenv('LANG')
59 from WF_LANGUAGES L
60 where L.INSTALLED_FLAG = 'Y'
61 and not exists
62 (select NULL
63 from WF_ITEM_TYPES_TL T
64 where T.NAME = X_NAME
65 and T.LANGUAGE = L.CODE);
66
67 open c;
68 fetch c into X_ROWID;
69 if (c%notfound) then
70 close c;
71 raise no_data_found;
72 end if;
73 close c;
74
75 exception
76 when others then
77 wf_core.context('Wf_Item_Types_Pkg', 'Insert_Row', x_name);
78 raise;
79 end INSERT_ROW;
80
81 procedure LOCK_ROW (
82 X_NAME in VARCHAR2,
83 X_PROTECT_LEVEL in NUMBER,
84 X_CUSTOM_LEVEL in NUMBER,
85 X_WF_SELECTOR in VARCHAR2,
86 X_READ_ROLE in VARCHAR2,
87 X_WRITE_ROLE in VARCHAR2,
88 X_EXECUTE_ROLE in VARCHAR2,
89 X_DISPLAY_NAME in VARCHAR2,
90 X_DESCRIPTION in VARCHAR2
91 ) is
92 cursor c is select
93 PROTECT_LEVEL,
94 CUSTOM_LEVEL,
95 WF_SELECTOR,
96 READ_ROLE,
97 WRITE_ROLE,
98 EXECUTE_ROLE
99 from WF_ITEM_TYPES
100 where NAME = X_NAME
101 for update of NAME nowait;
102 recinfo c%rowtype;
103
104 cursor c1 is select
105 DISPLAY_NAME,
106 DESCRIPTION
107 from WF_ITEM_TYPES_TL
108 where NAME = X_NAME
109 and LANGUAGE = userenv('LANG')
110 for update of NAME nowait;
111 tlinfo c1%rowtype;
112
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 wf_core.raise('WF_RECORD_DELETED');
119 end if;
120 close c;
121 if ( (recinfo.PROTECT_LEVEL = X_PROTECT_LEVEL)
122 AND (recinfo.CUSTOM_LEVEL = X_CUSTOM_LEVEL)
123 AND ((recinfo.WF_SELECTOR = X_WF_SELECTOR)
124 OR ((recinfo.WF_SELECTOR is null) AND (X_WF_SELECTOR is null)))
125 AND ((recinfo.READ_ROLE = X_READ_ROLE)
126 OR ((recinfo.READ_ROLE is null) AND (X_READ_ROLE is null)))
127 AND ((recinfo.WRITE_ROLE = X_WRITE_ROLE)
128 OR ((recinfo.WRITE_ROLE is null) AND (X_WRITE_ROLE is null)))
129 AND ((recinfo.EXECUTE_ROLE = X_EXECUTE_ROLE)
130 OR ((recinfo.EXECUTE_ROLE is null) AND (X_EXECUTE_ROLE is null)))
131 ) then
132 null;
133 else
134 wf_core.raise('WF_RECORD_CHANGED');
135 end if;
136
137 open c1;
138 fetch c1 into tlinfo;
139 if (c1%notfound) then
140 close c1;
141 return;
142 end if;
143 close c1;
144
145 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
146 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
147 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
148 ) then
149 null;
150 else
151 wf_core.raise('WF_RECORD_CHANGED');
152 end if;
153 return;
154 exception
155 when others then
156 wf_core.context('Wf_Item_Types_Pkg', 'Lock_Row', x_name);
157 raise;
158 end LOCK_ROW;
159
160 procedure UPDATE_ROW (
161 X_NAME in VARCHAR2,
162 X_PROTECT_LEVEL in NUMBER,
163 X_CUSTOM_LEVEL in NUMBER,
164 X_WF_SELECTOR in VARCHAR2,
165 X_READ_ROLE in VARCHAR2,
166 X_WRITE_ROLE in VARCHAR2,
167 X_EXECUTE_ROLE in VARCHAR2,
168 X_DISPLAY_NAME in VARCHAR2,
169 X_DESCRIPTION in VARCHAR2,
170 X_PERSISTENCE_TYPE in VARCHAR2,
171 X_PERSISTENCE_DAYS in NUMBER
172 ) is
173 begin
174 update WF_ITEM_TYPES set
175 PROTECT_LEVEL = X_PROTECT_LEVEL,
176 CUSTOM_LEVEL = X_CUSTOM_LEVEL,
177 WF_SELECTOR = X_WF_SELECTOR,
178 READ_ROLE = X_READ_ROLE,
179 WRITE_ROLE = X_WRITE_ROLE,
180 EXECUTE_ROLE = X_EXECUTE_ROLE,
181 PERSISTENCE_TYPE = X_PERSISTENCE_TYPE,
182 PERSISTENCE_DAYS = X_PERSISTENCE_DAYS
183 where NAME = X_NAME;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188
189 update WF_ITEM_TYPES_TL set
190 DISPLAY_NAME = X_DISPLAY_NAME,
191 DESCRIPTION = X_DESCRIPTION,
192 SOURCE_LANG = userenv('LANG')
193 where NAME = X_NAME
194 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199 exception
200 when others then
201 wf_core.context('Wf_Item_Types_Pkg', 'Update_Row', x_name);
202 raise;
203 end UPDATE_ROW;
204
205 procedure DELETE_ROW (
206 X_NAME in VARCHAR2
207 ) is
208 begin
209 delete from WF_ITEM_TYPES_TL
210 where NAME = X_NAME;
211
212 if (sql%notfound) then
213 raise no_data_found;
214 end if;
215
216 delete from WF_ITEM_TYPES
217 where NAME = X_NAME;
218
219 if (sql%notfound) then
220 raise no_data_found;
221 end if;
222
223 exception
224 when others then
225 wf_core.context('Wf_Item_Types_Pkg', 'Delete_Row', x_name);
226 raise;
227 end DELETE_ROW;
228
229 procedure ADD_LANGUAGE
230 is
231 begin
232 /* The following delete and update statements are commented out */
233 /* as a quick workaround to fix the time-consuming table handler issue */
234 /* Eventually we'll need to turn them into a separate fix_language procedure */
235 /*
236
237 delete from WF_ITEM_TYPES_TL T
238 where not exists
239 (select NULL
240 from WF_ITEM_TYPES B
241 where B.NAME = T.NAME
242 );
243
244 update WF_ITEM_TYPES_TL T set (
245 DISPLAY_NAME,
246 DESCRIPTION
247 ) = (select
248 B.DISPLAY_NAME,
249 B.DESCRIPTION
250 from WF_ITEM_TYPES_TL B
251 where B.NAME = T.NAME
252 and B.LANGUAGE = T.SOURCE_LANG)
253 where (
254 T.NAME,
255 T.LANGUAGE
256 ) in (select
257 SUBT.NAME,
258 SUBT.LANGUAGE
259 from WF_ITEM_TYPES_TL SUBB, WF_ITEM_TYPES_TL SUBT
260 where SUBB.NAME = SUBT.NAME
261 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
262 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
263 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
264 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
265 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
266 ));
267
268
269 */
270 insert into WF_ITEM_TYPES_TL (
271 NAME,
272 DISPLAY_NAME,
273 PROTECT_LEVEL,
274 CUSTOM_LEVEL,
275 DESCRIPTION,
276 LANGUAGE,
277 SOURCE_LANG
278 ) select
279 B.NAME,
280 B.DISPLAY_NAME,
281 B.PROTECT_LEVEL,
282 B.CUSTOM_LEVEL,
283 B.DESCRIPTION,
284 L.CODE,
285 B.SOURCE_LANG
286 from WF_ITEM_TYPES_TL B, WF_LANGUAGES L
287 where L.INSTALLED_FLAG = 'Y'
288 and B.LANGUAGE = userenv('LANG')
289 and (b.name,l.code) NOT IN
290 (select /*+ hash_aj index_ffs(T,WF_ITEM_TYPES_TL_PK) */
291 T.NAME,T.LANGUAGE
292 from WF_ITEM_TYPES_TL T) ;
293
294 end ADD_LANGUAGE;
295
296 end WF_ITEM_TYPES_PKG;
[Home] [Help]