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