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