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