1 package body WF_RESOURCES_PKG as
2 /* $Header: wfresb.pls 120.1 2005/07/02 03:53:07 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_ID in NUMBER,
10 X_TEXT in VARCHAR2
11 ) is
12 cursor C is select ROWID from WF_RESOURCES
13 where NAME = X_NAME
14 and TYPE = X_TYPE
15 and LANGUAGE = userenv('LANG')
16 ;
17 begin
18 insert into WF_RESOURCES (
19 TYPE,
20 NAME,
21 ID,
22 TEXT,
23 PROTECT_LEVEL,
24 CUSTOM_LEVEL,
25 LANGUAGE,
26 SOURCE_LANG
27 ) select
28 X_TYPE,
29 X_NAME,
30 X_ID,
31 REPLACE(X_TEXT, WF_CORE.CR),
32 X_PROTECT_LEVEL,
33 X_CUSTOM_LEVEL,
34 L.CODE,
35 userenv('LANG')
36 from WF_LANGUAGES L
37 where L.INSTALLED_FLAG = 'Y'
38 and not exists
39 (select NULL
40 from WF_RESOURCES T
41 where T.NAME = X_NAME
42 and T.TYPE = X_TYPE
43 and T.LANGUAGE = L.CODE);
44
45 open c;
46 fetch c into X_ROWID;
47 if (c%notfound) then
48 close c;
49 raise no_data_found;
50 end if;
51 close c;
52
53 exception
54 when others then
55 wf_core.context('Wf_Resources_Pkg', 'Insert_Row',
56 x_name, x_type);
57 raise;
58 end INSERT_ROW;
59
60 procedure LOCK_ROW (
61 X_TYPE in VARCHAR2,
62 X_NAME in VARCHAR2,
63 X_PROTECT_LEVEL in NUMBER,
64 X_CUSTOM_LEVEL in NUMBER,
65 X_ID in NUMBER,
66 X_TEXT in VARCHAR2
67 ) is
68 cursor c1 is select
69 ID,
70 PROTECT_LEVEL,
71 CUSTOM_LEVEL,
72 TEXT
73 from WF_RESOURCES
74 where NAME = X_NAME
75 and TYPE = X_TYPE
76 and LANGUAGE = userenv('LANG')
77 for update of NAME nowait;
78 tlinfo c1%rowtype;
79
80 begin
81 open c1;
82 fetch c1 into tlinfo;
83 if (c1%notfound) then
84 close c1;
85 wf_core.raise('WF_RECORD_DELETED');
86 end if;
87 close c1;
88
89 if ( (tlinfo.TEXT = X_TEXT)
90 AND (tlinfo.ID = X_ID)
91 AND (tlinfo.PROTECT_LEVEL = X_PROTECT_LEVEL)
92 AND (tlinfo.CUSTOM_LEVEL = X_CUSTOM_LEVEL)
93 ) then
94 null;
95 else
96 wf_core.raise('WF_RECORD_CHANGED');
97 end if;
98 return;
99
100 exception
101 when others then
102 wf_core.context('Wf_Resources_Pkg', 'Lock_Row',
103 x_name, x_type);
104 raise;
105 end LOCK_ROW;
106
107 procedure UPDATE_ROW (
108 X_TYPE in VARCHAR2,
109 X_NAME in VARCHAR2,
110 X_PROTECT_LEVEL in NUMBER,
111 X_CUSTOM_LEVEL in NUMBER,
112 X_ID in NUMBER,
113 X_TEXT in VARCHAR2
114 ) is
115 begin
116 update WF_RESOURCES set
117 ID = X_ID,
118 PROTECT_LEVEL = X_PROTECT_LEVEL,
119 CUSTOM_LEVEL = X_CUSTOM_LEVEL,
120 TEXT = REPLACE(X_TEXT, WF_CORE.CR),
121 SOURCE_LANG = userenv('LANG')
122 where NAME = X_NAME
123 and TYPE = X_TYPE
124 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
125
126 if (sql%notfound) then
127 raise no_data_found;
128 end if;
129
130 exception
131 when others then
132 wf_core.context('Wf_Resources_Pkg', 'Update_Row',
133 x_name, x_type);
134 raise;
135 end UPDATE_ROW;
136
137 procedure DELETE_ROW (
138 X_TYPE in VARCHAR2,
139 X_NAME in VARCHAR2
140 ) is
141 begin
142 delete from WF_RESOURCES
143 where NAME = X_NAME
144 and TYPE = X_TYPE;
145
146 if (sql%notfound) then
147 raise no_data_found;
148 end if;
149
150 exception
151 when others then
152 wf_core.context('Wf_Resources_Pkg', 'Delete_Row',
153 x_name, x_type);
154 raise;
155 end DELETE_ROW;
156
157 procedure ADD_LANGUAGE
158 is
159 begin
160 /* Mar/19/03 requested by Ric Ginsberg */
161 /* The following delete and update statements are commented out */
162 /* as a quick workaround to fix the time-consuming table handler issue */
163 /* Eventually we'll need to turn them into a separate fix_language procedure */
164 /*
165
166 update WF_RESOURCES T set (
167 TEXT
168 ) = (select
169 B.TEXT
170 from WF_RESOURCES B
171 where B.NAME = T.NAME
172 and B.TYPE = T.TYPE
173 and B.LANGUAGE = T.SOURCE_LANG)
174 where (
175 T.NAME,
176 T.TYPE,
177 T.LANGUAGE
178 ) in (select
179 SUBT.NAME,
180 SUBT.TYPE,
181 SUBT.LANGUAGE
182 from WF_RESOURCES SUBB, WF_RESOURCES SUBT
183 where SUBB.NAME = SUBT.NAME
184 and SUBB.TYPE = SUBT.TYPE
185 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
186 and (SUBB.TEXT <> SUBT.TEXT
187 ));
188 */
189
190 insert into WF_RESOURCES (
191 TYPE,
192 NAME,
193 ID,
194 TEXT,
195 PROTECT_LEVEL,
196 CUSTOM_LEVEL,
197 LANGUAGE,
198 SOURCE_LANG
199 ) select
200 B.TYPE,
201 B.NAME,
202 B.ID,
203 B.TEXT,
204 B.PROTECT_LEVEL,
205 B.CUSTOM_LEVEL,
206 L.CODE,
207 B.SOURCE_LANG
208 from WF_RESOURCES B, WF_LANGUAGES L
209 where L.INSTALLED_FLAG = 'Y'
210 and B.LANGUAGE = userenv('LANG')
211 and ( B.NAME ,B.TYPE ,L.CODE) NOT IN
212 (select /*+ hash_aj index_ffs(T,WF_RESOURCES_PK ) */
213 T.NAME ,T.TYPE , T.LANGUAGE
214 from WF_RESOURCES T);
215 end ADD_LANGUAGE;
216
217 end WF_RESOURCES_PKG;