1 package body WF_DIRECTORY_PARTITIONS_PKG as
2 /* $Header: wfdpb.pls 120.2 2005/09/01 08:19:36 hgandiko noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_ORIG_SYSTEM in VARCHAR2,
7 X_PARTITION_ID in NUMBER,
8 X_DISPLAY_NAME in VARCHAR2
9 ) is
10 cursor C is select ROWID from WF_DIRECTORY_PARTITIONS
11 where ORIG_SYSTEM = X_ORIG_SYSTEM
12 ;
13 begin
14 begin
15 insert into WF_DIRECTORY_PARTITIONS (
16 PARTITION_ID,
17 ORIG_SYSTEM
18 ) values (
19 X_PARTITION_ID,
20 X_ORIG_SYSTEM
21 );
22 exception
23 -- handle a special case where translated data may not have been uploaded
24 -- e.g. the first time tl table is created.
25 when DUP_VAL_ON_INDEX then
26 null;
27 end;
28
29 insert into WF_DIRECTORY_PARTITIONS_TL (
30 ORIG_SYSTEM,
31 DISPLAY_NAME,
32 LANGUAGE,
33 SOURCE_LANG
34 ) select
35 X_ORIG_SYSTEM,
36 X_DISPLAY_NAME,
37 L.CODE,
38 userenv('LANG')
39 from WF_LANGUAGES L
40 where L.INSTALLED_FLAG = 'Y'
41 and not exists
42 (select NULL
43 from WF_DIRECTORY_PARTITIONS_TL T
44 where T.ORIG_SYSTEM = X_ORIG_SYSTEM
45 and T.LANGUAGE = L.CODE);
46
47 open c;
48 fetch c into X_ROWID;
49 if (c%notfound) then
50 close c;
51 raise no_data_found;
52 end if;
53 close c;
54
55 exception
56 when others then
57 wf_core.context('Wf_Directory_Partitions_Pkg','Insert_Row',x_orig_system);
58 raise;
59 end INSERT_ROW;
60
61 procedure LOCK_ROW (
62 X_ORIG_SYSTEM in VARCHAR2,
63 X_PARTITION_ID in NUMBER,
64 X_DISPLAY_NAME in VARCHAR2
65 ) is
66 cursor c is select
67 PARTITION_ID
68 from WF_DIRECTORY_PARTITIONS
69 where ORIG_SYSTEM = X_ORIG_SYSTEM
70 for update of ORIG_SYSTEM nowait;
71 recinfo c%rowtype;
72
73 cursor c1 is select
74 DISPLAY_NAME,
75 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
76 from WF_DIRECTORY_PARTITIONS_TL
77 where ORIG_SYSTEM = X_ORIG_SYSTEM
78 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
79 for update of ORIG_SYSTEM nowait;
80 tlinfo c1%rowtype;
81 begin
82 open c;
83 fetch c into recinfo;
84 if (c%notfound) then
85 close c;
86 wf_core.raise('WF_RECORD_DELETED');
87 end if;
88 close c;
89 if ( ((recinfo.PARTITION_ID = X_PARTITION_ID)
90 OR ((recinfo.PARTITION_ID is null) AND (X_PARTITION_ID is null)))
91 ) then
92 null;
93 else
94 wf_core.raise('WF_RECORD_CHANGED');
95 end if;
96
97 for tlinfo in c1 loop
98 if (tlinfo.BASELANG = 'Y') then
99 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
100 ) then
101 null;
102 else
103 wf_core.raise('WF_RECORD_CHANGED');
104 end if;
105 end if;
106 end loop;
107 return;
108
109 exception
110 when others then
111 wf_core.context('Wf_Directory_Partitions_Pkg', 'Lock_Row', x_orig_system);
112 raise;
113 end LOCK_ROW;
114
115 procedure UPDATE_ROW (
116 X_ORIG_SYSTEM in VARCHAR2,
117 X_PARTITION_ID in NUMBER,
118 X_DISPLAY_NAME in VARCHAR2
119 ) is
120 begin
121 update WF_DIRECTORY_PARTITIONS set
122 PARTITION_ID = X_PARTITION_ID
123 where ORIG_SYSTEM = X_ORIG_SYSTEM;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128
129 update WF_DIRECTORY_PARTITIONS_TL set
130 DISPLAY_NAME = X_DISPLAY_NAME,
131 SOURCE_LANG = userenv('LANG')
132 where ORIG_SYSTEM = X_ORIG_SYSTEM
133 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
134
135 if (sql%notfound) then
136 raise no_data_found;
137 end if;
138
139 exception
140 when others then
141 wf_core.context('Wf_Directory_Partitions_Pkg','Update_Row',x_orig_system);
142 raise;
143 end UPDATE_ROW;
144
145 procedure DELETE_ROW (
146 X_ORIG_SYSTEM in VARCHAR2
147 ) is
148 begin
149 delete from WF_DIRECTORY_PARTITIONS_TL
150 where ORIG_SYSTEM = X_ORIG_SYSTEM;
151
152 if (sql%notfound) then
153 raise no_data_found;
154 end if;
155
156 delete from WF_DIRECTORY_PARTITIONS
157 where ORIG_SYSTEM = X_ORIG_SYSTEM;
158
159 if (sql%notfound) then
160 raise no_data_found;
161 end if;
162
163 exception
164 when others then
165 wf_core.context('Wf_Directory_Partitions_Pkg','Delete_Row',x_orig_system);
166 raise;
167 end DELETE_ROW;
168
169 procedure ADD_LANGUAGE
170 is
171 begin
172 delete from WF_DIRECTORY_PARTITIONS_TL T
173 where not exists
174 (select NULL
175 from WF_DIRECTORY_PARTITIONS B
176 where B.ORIG_SYSTEM = T.ORIG_SYSTEM
177 );
178
179 update WF_DIRECTORY_PARTITIONS_TL T set (
180 DISPLAY_NAME
181 ) = (select
182 B.DISPLAY_NAME
183 from WF_DIRECTORY_PARTITIONS_TL B
184 where B.ORIG_SYSTEM = T.ORIG_SYSTEM
185 and B.LANGUAGE = T.SOURCE_LANG)
186 where (
187 T.ORIG_SYSTEM,
188 T.LANGUAGE
189 ) in (select
190 SUBT.ORIG_SYSTEM,
191 SUBT.LANGUAGE
192 from WF_DIRECTORY_PARTITIONS_TL SUBB, WF_DIRECTORY_PARTITIONS_TL SUBT
193 where SUBB.ORIG_SYSTEM = SUBT.ORIG_SYSTEM
194 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
195 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
196 ));
197
198 insert into WF_DIRECTORY_PARTITIONS_TL (
199 ORIG_SYSTEM,
200 DISPLAY_NAME,
201 LANGUAGE,
202 SOURCE_LANG
203 ) select /*+ ORDERED */
204 B.ORIG_SYSTEM,
205 B.DISPLAY_NAME,
206 L.CODE,
207 B.SOURCE_LANG
208 from WF_DIRECTORY_PARTITIONS_TL B, WF_LANGUAGES L
209 where L.INSTALLED_FLAG = 'Y'
210 and B.LANGUAGE = userenv('LANG')
211 and not exists
212 (select NULL
213 from WF_DIRECTORY_PARTITIONS_TL T
214 where T.ORIG_SYSTEM = B.ORIG_SYSTEM
215 and T.LANGUAGE = L.CODE);
216
217 exception
218 when others then
219 wf_core.context('Wf_Directory_Partitions_Pkg', 'Add_Language');
220 raise;
221 end ADD_LANGUAGE;
222
223 procedure LOAD_ROW (
224 X_ORIG_SYSTEM in VARCHAR2,
225 X_PARTITION_ID in NUMBER,
226 X_DISPLAY_NAME in VARCHAR2
227 )
228 is
229 l_rowid varchar2(30);
230 l_orig_system varchar2(30);
231 begin
232 begin
233 wf_directory_partitions_pkg.Update_row(
234 X_ORIG_SYSTEM => l_orig_system,
235 X_PARTITION_ID => X_PARTITION_ID,
236 X_DISPLAY_NAME => X_DISPLAY_NAME);
237 exception
238 when NO_DATA_FOUND then
239 wf_directory_partitions_pkg.Insert_Row(
240 X_ROWID => l_rowid,
241 X_ORIG_SYSTEM => X_ORIG_SYSTEM,
242 X_PARTITION_ID => X_PARTITION_ID,
243 X_DISPLAY_NAME => X_DISPLAY_NAME);
244 end;
245 exception
246 when others then
247 wf_core.context('Wf_Directory_Partitions_Pkg','Load_Row',x_orig_system);
248 raise;
249 end LOAD_ROW;
250
251 procedure TRANSLATE_ROW (
252 X_ORIG_SYSTEM in VARCHAR2,
253 X_DISPLAY_NAME in VARCHAR2
254 )
255 is
256 begin
257 update WF_DIRECTORY_PARTITIONS_TL
258 set DISPLAY_NAME = X_DISPLAY_NAME,
259 SOURCE_LANG = userenv('LANG')
260 where ORIG_SYSTEM = X_ORIG_SYSTEM
261 and userenv('LANG') in (language, source_lang);
262 exception
263 when others then
264 wf_core.context('Wf_Directory_Partitions_Pkg','Translate_Row',x_orig_system);
265 raise;
266 end TRANSLATE_ROW;
267
268 --<rwunderl:2901155>
269 procedure UPDATE_VIEW_NAMES (
270 X_ORIG_SYSTEM in VARCHAR2,
271 X_PARTITION_ID in NUMBER,
272 X_ROLE_VIEW in VARCHAR2,
273 X_USER_ROLE_VIEW in VARCHAR2,
274 X_ROLE_TL_VIEW in VARCHAR2,
275 X_LAST_UPDATE_DATE in DATE
276 ) is
277 begin
278 update WF_DIRECTORY_PARTITIONS wdp
279 set
280 wdp.ROLE_VIEW = nvl(X_ROLE_VIEW, wdp.ROLE_VIEW),
281 wdp.USER_ROLE_VIEW = nvl(X_USER_ROLE_VIEW, wdp.USER_ROLE_VIEW),
282 wdp.ROLE_TL_VIEW = nvl(X_ROLE_TL_VIEW, wdp.ROLE_TL_VIEW),
283 wdp.LAST_UPDATE_DATE = nvl(X_LAST_UPDATE_DATE, trunc(sysdate))
284 where wdp.ORIG_SYSTEM = X_ORIG_SYSTEM
285 and wdp.PARTITION_ID = X_PARTITION_ID;
286
287 if (sql%notfound) then
288 raise no_data_found;
289 end if;
290
291 exception
292 when others then
293 wf_core.context('Wf_Directory_Partitions_Pkg','Update_View_Names',
294 x_orig_system);
295 raise;
296 end UPDATE_VIEW_NAMES;
297
298 end WF_DIRECTORY_PARTITIONS_PKG;
[Home] [Help]