1 package body WF_NTF_RULES_PKG as
2 /* $Header: WFNTFRLB.pls 120.3 2005/12/15 22:06:24 hgandiko noship $ */
3
4 --Private Variables
5 type txt_tbl_type is table of varchar2(240) index by binary_integer;
6 type num_tbl_type is table of number index by binary_integer;
7
8
9 --Private Function
10 procedure fetch_custom_level(x_owner_tag in varchar2,
11 x_rule_name in varchar2,
12 x_customization_level out nocopy varchar2)
13 is
14 begin
15 select customization_level
16 into x_customization_level
17 from wf_ntf_rules
18 where owner_tag = x_owner_tag
19 and rule_name = x_rule_name;
20
21 exception
22 when no_data_found then
23 raise no_data_found;
24 when others then
25 raise;
26 end fetch_custom_level;
27
28
29 procedure insert_row(x_owner_tag in varchar2,
30 x_rule_name in varchar2,
31 x_user_rule_name in varchar2,
32 x_description in varchar2,
33 x_customization_level in varchar2,
34 x_phase in number,
35 x_status in varchar2)
36 is
37 begin
38 insert into wf_ntf_rules (
39 owner_tag,
40 creation_date,
41 rule_name,
42 customization_level,
43 phase,
44 status
45 ) values (
46 x_owner_tag,
47 sysdate,
48 x_rule_name,
49 x_customization_level,
50 x_phase,
51 x_status);
52
53 insert into wf_ntf_rules_tl (
54 rule_name,
55 user_rule_name,
56 description,
57 language,
58 source_lang,
59 creation_date)
60 select x_rule_name,
61 x_user_rule_name,
62 x_description,
63 l.code,
64 userenv('LANG'),
65 sysdate
66 from wf_languages l
67 where l.installed_flag = 'Y'
68 and not exists
69 (select null
70 from wf_ntf_rules_tl t
71 where t.rule_name = x_rule_name
72 and t.language = l.code);
73 exception
74 when others then
75 wf_core.context('Wf_ntf_rules_pkg', 'Insert_Row', x_owner_tag, x_rule_name);
76 raise;
77 end insert_row;
78
79 procedure delete_row(x_owner_tag in varchar2,
80 x_rule_name in varchar2)
81 is
82 begin
83
84 delete from wf_ntf_rules_tl
85 where rule_name = x_rule_name;
86
87 if (sql%notfound) then
88 raise no_data_found;
89 end if;
90
91 delete from wf_ntf_rules
92 where owner_tag = x_owner_tag
93 and rule_name = x_rule_name;
94
95 if (sql%notfound) then
96 raise no_data_found;
97 end if;
98
99 exception
100 when others then
101 wf_core.context('Wf_ntf_rules_pkg', 'Delete_Row', x_owner_tag, x_rule_name);
102 raise;
103 end delete_row;
104
105 procedure update_row(x_owner_tag in varchar2,
106 x_rule_name in varchar2,
107 x_user_rule_name in varchar2,
108 x_description in varchar2,
109 x_customization_level in varchar2,
110 x_phase in number,
111 x_status in varchar2)
112 is
113 l_custom_level varchar2(10);
114 l_update_allowed varchar2(1);
115 begin
116 fetch_custom_level(x_owner_tag,x_rule_name,l_custom_level);
117 l_update_allowed := is_update_allowed(x_customization_level,l_custom_level);
118
119 if g_mode = 'FORCE' then
120 update wf_ntf_rules
121 set status = x_status,
122 customization_level = x_customization_level,
123 phase = x_phase
124 where owner_tag = x_owner_tag
125 and rule_name = x_rule_name;
126
127 update wf_ntf_rules_tl
128 set user_rule_name = x_user_rule_name,
129 description = x_description,
130 source_lang = userenv('LANG')
131 where rule_name = x_rule_name
132 and userenv('LANG') in (language, source_lang);
133 else
134 -- User is not seed
135 if l_update_allowed = 'N' then
136 wf_core.context('WF_NTF_RULES_PKG','UPDATE_ROW',
137 x_rule_name,
138 l_custom_level,
139 X_CUSTOMIZATION_LEVEL);
140 return;
141 end if;
142
143 if x_customization_level ='L' then
144 update wf_ntf_rules
145 set status = x_status
146 where owner_tag = x_owner_tag
147 and rule_name = x_rule_name;
148
149 elsif x_customization_level = 'U' then
150 if g_mode ='CUSTOM' then
151 update wf_ntf_rules
152 set status = x_status,
153 customization_level = x_customization_level,
154 phase = x_phase
155 where owner_tag = x_owner_tag
156 and rule_name = x_rule_name;
157
158 update wf_ntf_rules_tl
159 set user_rule_name = x_user_rule_name,
160 description = x_description,
161 source_lang = userenv('LANG')
162 where rule_name = x_rule_name
163 and userenv('LANG') in (language, source_lang);
164 end if;
165
166 elsif x_customization_level <> 'C' then
167 -- Raise error..
168 Wf_Core.Token('REASON','Invalid Customization Level:' ||
169 l_custom_level);
170 Wf_Core.Raise('WFSQL_INTERNAL');
171 end if;
172 end if;
173 exception
174 when others then
175 wf_core.context('Wf_ntf_rules_pkg', 'Update_Row', x_owner_tag, x_rule_name);
176 raise;
177 end update_row;
178
179 procedure load_row(x_owner_tag in varchar2,
180 x_rule_name in varchar2,
181 x_user_rule_name in varchar2,
182 x_description in varchar2,
183 x_customization_level in varchar2,
184 x_phase in number,
185 x_status in varchar2,
186 x_custom_mode in varchar2)
187 is
188 begin
189 if x_customization_level = 'C' or x_customization_level = 'L' then
190 wf_ntf_rules_pkg.g_mode := 'FORCE';
191 else
192 wf_ntf_rules_pkg.g_mode := x_custom_mode;
193 end if;
194
195 if wf_ntf_rules_pkg.g_mode = 'FORCE' then
196 wf_ntf_rules_pkg.update_row(x_owner_tag,
197 x_rule_name,
198 x_user_rule_name,
199 x_description,
200 x_customization_level,
201 x_phase,
202 x_status);
203 end if;
204 exception
205 when others then
206 wf_ntf_rules_pkg.insert_row(x_owner_tag,
207 x_rule_name,
208 x_user_rule_name,
209 x_description,
210 x_customization_level,
211 x_phase,
212 x_status);
213 end load_row;
214
215 procedure FWKsetMode
216 is
217 uname varchar2(320);
218 begin
219 if g_Mode is null then
220 uname := wfa_sec.GetFWKUserName;
221 end if;
222
223 if uname = g_SeedUser then
224 g_Mode := 'FORCE';
225 else
226 g_Mode := 'CUSTOM';
227 end if;
228 end FWKsetMode;
229
230 function is_update_allowed(X_CUSTOM_LEVEL_NEW in varchar2,
231 X_CUSTOM_LEVEL_OLD in varchar2) return varchar2
232 is
233 begin
234
235 -- Cannot overwrite data with a higher customization level
236 if X_CUSTOM_LEVEL_NEW = 'U' then
237 if X_CUSTOM_LEVEL_OLD in ('C','L') then
238 -- Error will be logged
239 return ('N');
240 elsif X_CUSTOM_LEVEL_OLD = 'U' then
241 -- Return Y. Update is based on the caller
242 return ('Y');
243 end if;
244 elsif X_CUSTOM_LEVEL_NEW = 'L' then
245 if X_CUSTOM_LEVEL_OLD = 'C' then
246 -- Error will be logged
247 return('N');
248 elsif X_CUSTOM_LEVEL_OLD = 'U' then
249 -- Override it
250 return('Y');
251 else
252 -- Customization Level is L
253 return('Y');
254 end if;
255 elsif X_CUSTOM_LEVEL_NEW = 'C' then
256 -- Override the values in the database irrespective of the value
257 -- Return Y. Update is based on the caller
258 return('Y');
259 end if;
260 end is_update_allowed;
261
262 procedure add_language
263 is
264 begin
265 insert into wf_ntf_rules_tl (
266 rule_name,
267 user_rule_name,
268 description,
269 language,
270 source_lang,
271 creation_date
272 ) select
273 b.rule_name,
274 b.user_rule_name,
275 b.description,
276 l.code,
277 b.source_lang,
278 sysdate
279 from WF_ntf_rules_tl b, wf_languages l
280 where l.installed_flag = 'Y'
281 and b.language = userenv('LANG')
282 and (b.rule_name,l.code) not in
283 (select /*+ hash_aj index_ffs(T,WF_NTF_RULES_TL_PK) */
284 t.rule_name,t.language
285 from wf_ntf_rules_tl t) ;
286 exception
287 when others then
288 wf_core.context('Wf_ntf_rules_Pkg', 'Add_Language');
289 raise;
290 end add_language;
291
292 procedure translate_row(x_rule_name in varchar2,
293 x_user_rule_name in varchar2,
294 x_description in varchar2)
295 is
296 begin
297 update wf_ntf_rules_tl
298 set user_rule_name=x_user_rule_name,
299 description=x_description,
300 source_lang=userenv('LANG')
301 where rule_name=x_rule_name
302 and userenv('LANG') in (language, source_lang);
303 exception
304 when others then
305 wf_core.context('Wf_NTF_Rules_Pkg', 'Translate_Row');
306 raise;
307 end translate_row;
308
309 end WF_NTF_RULES_PKG;
310
[Home] [Help]