[Home] [Help]
PACKAGE BODY: APPS.JTF_DPF_RULES_PKG
Source
1 package body JTF_DPF_RULES_PKG as
2 /* $Header: jtfdpfrb.pls 120.2 2005/10/25 05:18:33 psanyal ship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_APPLICATION_ID in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_RULE_NAME in VARCHAR2,
9 X_RULE_DESCRIPTION in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from JTF_DPF_RULES_B
17 where RULE_ID = X_RULE_ID
18 ;
19 begin
20 insert into JTF_DPF_RULES_B (
21 APPLICATION_ID,
22 OBJECT_VERSION_NUMBER,
23 RULE_ID,
24 RULE_NAME,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_APPLICATION_ID,
32 X_OBJECT_VERSION_NUMBER,
33 X_RULE_ID,
34 X_RULE_NAME,
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN
40 );
41
42 insert into JTF_DPF_RULES_TL (
43 LAST_UPDATED_BY,
44 LAST_UPDATE_LOGIN,
45 RULE_ID,
46 RULE_DESCRIPTION,
47 CREATED_BY,
48 LAST_UPDATE_DATE,
49 LANGUAGE,
50 SOURCE_LANG
51 ) select
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_LOGIN,
54 X_RULE_ID,
55 X_RULE_DESCRIPTION,
56 X_CREATED_BY,
57 X_LAST_UPDATE_DATE,
58 L.LANGUAGE_CODE,
59 userenv('LANG')
60 from FND_LANGUAGES L
61 where L.INSTALLED_FLAG in ('I', 'B')
62 and not exists
63 (select NULL
64 from JTF_DPF_RULES_TL T
65 where T.RULE_ID = X_RULE_ID
66 and T.LANGUAGE = L.LANGUAGE_CODE);
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75
76 end INSERT_ROW;
77
78 procedure LOCK_ROW (
79 X_RULE_ID in NUMBER,
80 X_APPLICATION_ID in NUMBER,
81 X_OBJECT_VERSION_NUMBER in NUMBER,
82 X_RULE_NAME in VARCHAR2,
83 X_RULE_DESCRIPTION in VARCHAR2
84 ) is
85 cursor c is select
86 APPLICATION_ID,
87 OBJECT_VERSION_NUMBER,
88 RULE_NAME
89 from JTF_DPF_RULES_B
90 where RULE_ID = X_RULE_ID
91 for update of RULE_ID nowait;
92 recinfo c%rowtype;
93
94 cursor c1 is select
95 RULE_DESCRIPTION,
96 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
97 from JTF_DPF_RULES_TL
98 where RULE_ID = X_RULE_ID
99 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
100 for update of RULE_ID nowait;
101 begin
102 open c;
103 fetch c into recinfo;
104 if (c%notfound) then
105 close c;
106 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
107 app_exception.raise_exception;
108 end if;
109 close c;
110 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
111 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
112 AND (recinfo.RULE_NAME = X_RULE_NAME)
113 ) then
114 null;
115 else
116 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
117 app_exception.raise_exception;
118 end if;
119
120 for tlinfo in c1 loop
121 if (tlinfo.BASELANG = 'Y') then
122 if ( ((tlinfo.RULE_DESCRIPTION = X_RULE_DESCRIPTION)
123 OR ((tlinfo.RULE_DESCRIPTION is null) AND (X_RULE_DESCRIPTION is null)))
124 ) then
125 null;
126 else
127 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128 app_exception.raise_exception;
129 end if;
130 end if;
131 end loop;
132 return;
133 end LOCK_ROW;
134
135 procedure UPDATE_ROW (
136 X_RULE_ID in NUMBER,
137 X_APPLICATION_ID in NUMBER,
138 X_OBJECT_VERSION_NUMBER in NUMBER,
139 X_RULE_NAME in VARCHAR2,
140 X_RULE_DESCRIPTION in VARCHAR2,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) is
145 begin
146 update JTF_DPF_RULES_B set
147 APPLICATION_ID = X_APPLICATION_ID,
148 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
149 RULE_NAME = X_RULE_NAME,
150 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
151 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
152 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
153 where RULE_ID = X_RULE_ID;
154
155 if (sql%notfound) then
156 raise no_data_found;
157 end if;
158
159 update JTF_DPF_RULES_TL set
160 RULE_DESCRIPTION = X_RULE_DESCRIPTION,
161 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
162 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
164 SOURCE_LANG = userenv('LANG')
165 where RULE_ID = X_RULE_ID
166 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
167
168 if (sql%notfound) then
169 raise no_data_found;
170 end if;
171 end UPDATE_ROW;
172
173 procedure DELETE_ROW (
174 X_RULE_ID in NUMBER
175 ) is
176 begin
177 delete from JTF_DPF_RULES_TL
178 where RULE_ID = X_RULE_ID;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183
184 delete from JTF_DPF_RULES_B
185 where RULE_ID = X_RULE_ID;
186
187 if (sql%notfound) then
188 raise no_data_found;
189 end if;
190 end DELETE_ROW;
191
192 procedure ADD_LANGUAGE
193 is
194 begin
195 delete from JTF_DPF_RULES_TL T
196 where not exists
197 (select NULL
198 from JTF_DPF_RULES_B B
199 where B.RULE_ID = T.RULE_ID
200 );
201
202 update JTF_DPF_RULES_TL T set (
203 RULE_DESCRIPTION
204 ) = (select
205 B.RULE_DESCRIPTION
206 from JTF_DPF_RULES_TL B
207 where B.RULE_ID = T.RULE_ID
208 and B.LANGUAGE = T.SOURCE_LANG)
209 where (
210 T.RULE_ID,
211 T.LANGUAGE
212 ) in (select
213 SUBT.RULE_ID,
214 SUBT.LANGUAGE
215 from JTF_DPF_RULES_TL SUBB, JTF_DPF_RULES_TL SUBT
216 where SUBB.RULE_ID = SUBT.RULE_ID
217 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
218 and (SUBB.RULE_DESCRIPTION <> SUBT.RULE_DESCRIPTION
219 or (SUBB.RULE_DESCRIPTION is null and SUBT.RULE_DESCRIPTION is not null)
220 or (SUBB.RULE_DESCRIPTION is not null and SUBT.RULE_DESCRIPTION is null)
221 ));
222
223 insert into JTF_DPF_RULES_TL (
224 LAST_UPDATED_BY,
225 LAST_UPDATE_LOGIN,
226 RULE_ID,
227 RULE_DESCRIPTION,
228 CREATED_BY,
229 LAST_UPDATE_DATE,
230 LANGUAGE,
231 SOURCE_LANG
232 ) select
233 B.LAST_UPDATED_BY,
234 B.LAST_UPDATE_LOGIN,
235 B.RULE_ID,
236 B.RULE_DESCRIPTION,
237 B.CREATED_BY,
238 B.LAST_UPDATE_DATE,
239 L.LANGUAGE_CODE,
240 B.SOURCE_LANG
241 from JTF_DPF_RULES_TL B, FND_LANGUAGES L
242 where L.INSTALLED_FLAG in ('I', 'B')
243 and B.LANGUAGE = userenv('LANG')
244 and not exists
245 (select NULL
246 from JTF_DPF_RULES_TL T
247 where T.RULE_ID = B.RULE_ID
248 and T.LANGUAGE = L.LANGUAGE_CODE);
249 end ADD_LANGUAGE;
250
251 procedure TRANSLATE_ROW (
252 X_RULE_NAME IN VARCHAR2,
253 X_APPLICATION_ID IN VARCHAR2,
254 X_RULE_DESCRIPTION IN VARCHAR2,
255 X_OWNER IN VARCHAR2) is
256 l_rule_id number;
257 l_user_id number;
258 begin
259 l_user_id := 0;
260 if x_owner = 'SEED' then l_user_id := 1; end if;
261
262 l_rule_id := find(x_rule_name, x_application_id);
263
264 update jtf_dpf_rules_tl set
265 rule_description = x_rule_description
266 where userenv('LANG') in (LANGUAGE, SOURCE_LANG) and
267 rule_id = l_rule_id;
268 end;
269
270 procedure LOAD_ROW (
271 X_RULE_NAME in VARCHAR2,
272 X_APPLICATION_ID in VARCHAR2,
273 X_RULE_DESCRIPTION in VARCHAR2,
274 X_NUM_PARAMS IN VARCHAR2,
275 X_OWNER in VARCHAR2
276 ) is
277 -- select instances of this rule, identified by appid and name
278 cursor c is select rule_id from jtf_dpf_rules_b
279 where application_id = x_application_id and
280 rule_name=x_rule_name;
281
282 t_old_rule_id number;
283 t_new_rule_id number;
284 t_rowid rowid;
285 t_user number;
286 begin
287 t_user := 0;
288 if x_owner = 'SEED' then t_user := 1; end if;
289
290 -- see whether a row with this appid and rule_name already exists
291 open c;
292 fetch c into t_old_rule_id;
293
294 -- if it's not already there
295 if c%notfound then
296 close c;
297
298 -- get a new pseudo-sequence number
299 -- arsingh: prevent use of same id by different threads.
300 select JTF_DPF_RULES_S.nextval into t_new_rule_id from dual;
301 -- select max(rule_id) into t_new_rule_id from jtf_dpf_rules_b
302 -- where rule_id<10000;
303 -- if t_new_rule_id is null then
304 -- t_new_rule_id := 1;
305 -- else
306 -- t_new_rule_id := t_new_rule_id+1;
307 -- end if;
308
309 -- call _pkg.insert_row to handle _b and _tl tables
310 insert_row (
311 X_ROWID => t_rowid,
312 X_RULE_ID => t_new_rule_id,
313 X_APPLICATION_ID => x_application_id,
314 X_OBJECT_VERSION_NUMBER => 1,
315 X_RULE_NAME => x_rule_name,
316 X_RULE_DESCRIPTION => x_rule_description,
317 X_CREATION_DATE => SYSDATE,
318 X_CREATED_BY => t_user,
319 X_LAST_UPDATE_DATE => SYSDATE,
320 X_LAST_UPDATED_BY => t_user,
321 X_LAST_UPDATE_LOGIN => 0); -- FND_GLOBAL.CONC_LOGIN_ID);
322 -- else, if this rule already exists, so update it
323 else
324 close c;
325 -- call _pkg.update_row to handle _b and _tl tables
326 update_row (
327 X_RULE_ID => t_old_rule_id,
328 X_APPLICATION_ID => x_application_id,
329 X_OBJECT_VERSION_NUMBER => 1,
330 X_RULE_NAME => x_rule_name,
331 X_RULE_DESCRIPTION => x_rule_description,
332 X_LAST_UPDATE_DATE => sysdate,
333 X_LAST_UPDATED_BY => t_user,
334 X_LAST_UPDATE_LOGIN => 0) ; -- fnd_global.conc_login_id);
335
336 -- the seed data specifies that the rule (appid, rule_name)
337 -- has no param with sequence higher than x_num_params
338 delete from jtf_dpf_rule_params where
339 rule_id = t_old_rule_id and
340 rule_param_sequence > x_num_params;
341 end if;
342 end;
343
344 procedure INSERT_RULE_PARAMS(
345 X_RULE_PARAM_SEQUENCE NUMBER,
346 X_RULE_ID NUMBER,
347 X_RULE_PARAM_CONDITION VARCHAR2,
348 X_RULE_PARAM_NAME VARCHAR2,
349 X_RULE_PARAM_VALUE VARCHAR2,
350 X_OWNER IN VARCHAR2
351 ) is
352 l_user_id number;
353 begin
354 l_user_id := 0;
355 if x_owner = 'SEED' then l_user_id := 1; end if;
356
357 insert into jtf_dpf_rule_params(
358 rule_param_sequence,
359 rule_id,
360 rule_param_condition,
361 rule_param_name,
362 rule_param_value,
363 OBJECT_VERSION_NUMBER,
364 CREATED_BY,
365 CREATION_DATE,
366 LAST_UPDATE_DATE,
367 LAST_UPDATED_BY,
368 LAST_UPDATE_LOGIN)
369 values (
370 X_RULE_PARAM_SEQUENCE,
371 X_rule_id,
372 X_RULE_PARAM_CONDITION,
373 X_RULE_PARAM_NAME,
374 X_RULE_PARAM_VALUE,
375 1,
376 l_user_id,
377 sysdate,
378 sysdate,
379 l_user_id,
380 0);
381 end;
382
383 procedure UPDATE_RULE_PARAMS(
384 X_RULE_PARAM_SEQUENCE NUMBER,
385 X_RULE_ID NUMBER,
386 X_RULE_PARAM_CONDITION VARCHAR2,
387 X_RULE_PARAM_NAME VARCHAR2,
388 X_RULE_PARAM_VALUE VARCHAR2,
389 X_OWNER IN VARCHAR2
390 ) is
391 l_user_id number;
392 begin
393 l_user_id := 0;
394 if x_owner = 'SEED' then l_user_id := 1; end if;
395
396 update jtf_dpf_rule_params set
397 rule_param_name = x_rule_param_name,
398 rule_param_value = x_rule_param_value,
399 rule_param_condition = x_rule_param_condition,
400 object_version_number = object_version_number +1,
401 last_update_date = sysdate,
402 last_updated_by = l_user_id,
403 last_update_login = 0
404 where rule_id = x_rule_id and
405 rule_param_sequence = x_rule_param_sequence;
406 end;
407
408 function find(
409 x_rule_name varchar2,
410 x_application_id in varchar2
411 ) return number is
412 cursor c1(p_rule_name varchar2, p_application_id number) is
413 select rule_id from jtf_dpf_rules_b
414 where rule_name = p_rule_name and application_id = p_application_id;
415 retval number := null;
416 begin
417 open c1(x_rule_name, x_application_id);
418 fetch c1 into retval;
419 close c1;
420 return retval;
421 end;
422
423 end JTF_DPF_RULES_PKG;