1 package body JTF_R_RULES_PKG as
2 /* $Header: ibagrulb.pls 115.1 2000/11/08 15:14:43 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_START_DATE in DATE,
7 X_END_DATE in DATE,
8 X_PRIORITY in NUMBER,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_RULESET_ID in NUMBER,
11 X_STATUS in VARCHAR2,
12 X_NAME in VARCHAR2,
13 X_COND_DESC in VARCHAR2,
14 X_ACTION_DESC in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from JTF_R_RULES_B
22 where RULE_ID = X_RULE_ID
23 ;
24 begin
25 insert into JTF_R_RULES_B (
26 START_DATE,
27 END_DATE,
28 PRIORITY,
29 OBJECT_VERSION_NUMBER,
30 RULESET_ID,
31 STATUS,
32 RULE_ID,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_START_DATE,
40 X_END_DATE,
41 X_PRIORITY,
42 X_OBJECT_VERSION_NUMBER,
43 X_RULESET_ID,
44 X_STATUS,
45 X_RULE_ID,
46 X_CREATION_DATE,
47 X_CREATED_BY,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_LAST_UPDATE_LOGIN
51 );
52
53 insert into JTF_R_RULES_TL (
54 RULE_ID,
55 CREATED_BY,
56 CREATION_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_DATE,
59 LAST_UPDATE_LOGIN,
60 NAME,
61 COND_DESC,
62 ACTION_DESC,
63 OBJECT_VERSION_NUMBER,
64 LANGUAGE,
65 SOURCE_LANG
66 ) select
67 X_RULE_ID,
68 X_CREATED_BY,
69 X_CREATION_DATE,
70 X_LAST_UPDATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATE_LOGIN,
73 X_NAME,
74 X_COND_DESC,
75 X_ACTION_DESC,
76 X_OBJECT_VERSION_NUMBER,
77 L.LANGUAGE_CODE,
78 userenv('LANG')
79 from FND_LANGUAGES L
80 where L.INSTALLED_FLAG in ('I', 'B')
81 and not exists
82 (select NULL
83 from JTF_R_RULES_TL T
84 where T.RULE_ID = X_RULE_ID
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_RULE_ID in NUMBER,
99 X_START_DATE in DATE,
100 X_END_DATE in DATE,
101 X_PRIORITY in NUMBER,
102 X_OBJECT_VERSION_NUMBER in NUMBER,
103 X_RULESET_ID in NUMBER,
104 X_STATUS in VARCHAR2,
105 X_NAME in VARCHAR2,
106 X_COND_DESC in VARCHAR2,
107 X_ACTION_DESC in VARCHAR2
108 ) is
109 cursor c is select
110 START_DATE,
111 END_DATE,
112 PRIORITY,
113 OBJECT_VERSION_NUMBER,
114 RULESET_ID,
115 STATUS
116 from JTF_R_RULES_B
117 where RULE_ID = X_RULE_ID
118 for update of RULE_ID nowait;
119 recinfo c%rowtype;
120
121 cursor c1 is select
122 NAME,
123 COND_DESC,
124 ACTION_DESC,
125 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
126 from JTF_R_RULES_TL
127 where RULE_ID = X_RULE_ID
128 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
129 for update of RULE_ID nowait;
130 begin
131 open c;
132 fetch c into recinfo;
133 if (c%notfound) then
134 close c;
135 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
136 app_exception.raise_exception;
137 end if;
138 close c;
139 if ( ((recinfo.START_DATE = X_START_DATE)
140 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
141 AND ((recinfo.END_DATE = X_END_DATE)
142 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
143 AND ((recinfo.PRIORITY = X_PRIORITY)
144 OR ((recinfo.PRIORITY is null) AND (X_PRIORITY is null)))
145 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
146 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
147 AND (recinfo.RULESET_ID = X_RULESET_ID)
148 AND (recinfo.STATUS = X_STATUS)
149 ) then
150 null;
151 else
152 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
153 app_exception.raise_exception;
154 end if;
155
156 for tlinfo in c1 loop
157 if (tlinfo.BASELANG = 'Y') then
158 if ( (tlinfo.NAME = X_NAME)
159 AND ((tlinfo.COND_DESC = X_COND_DESC)
160 OR ((tlinfo.COND_DESC is null) AND (X_COND_DESC is null)))
161 AND ((tlinfo.ACTION_DESC = X_ACTION_DESC)
162 OR ((tlinfo.ACTION_DESC is null) AND (X_ACTION_DESC is null)))
163 ) then
164 null;
165 else
166 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
167 app_exception.raise_exception;
168 end if;
169 end if;
170 end loop;
171 return;
172 end LOCK_ROW;
173
174 procedure UPDATE_ROW (
175 X_RULE_ID in NUMBER,
176 X_START_DATE in DATE,
177 X_END_DATE in DATE,
178 X_PRIORITY in NUMBER,
179 X_OBJECT_VERSION_NUMBER in NUMBER,
180 X_RULESET_ID in NUMBER,
181 X_STATUS in VARCHAR2,
182 X_NAME in VARCHAR2,
183 X_COND_DESC in VARCHAR2,
184 X_ACTION_DESC in VARCHAR2,
185 X_LAST_UPDATE_DATE in DATE,
186 X_LAST_UPDATED_BY in NUMBER,
187 X_LAST_UPDATE_LOGIN in NUMBER
188 ) is
189 begin
190 update JTF_R_RULES_B set
191 START_DATE = X_START_DATE,
192 END_DATE = X_END_DATE,
193 PRIORITY = X_PRIORITY,
194 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
195 RULESET_ID = X_RULESET_ID,
196 STATUS = X_STATUS,
197 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
200 where RULE_ID = X_RULE_ID;
201
202 if (sql%notfound) then
203 raise no_data_found;
204 end if;
205
206 update JTF_R_RULES_TL set
207 NAME = X_NAME,
208 COND_DESC = X_COND_DESC,
209 ACTION_DESC = X_ACTION_DESC,
210 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
211 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
213 SOURCE_LANG = userenv('LANG')
214 where RULE_ID = X_RULE_ID
215 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220 end UPDATE_ROW;
221
222 procedure DELETE_ROW (
223 X_RULE_ID in NUMBER
224 ) is
225 begin
226 delete from JTF_R_RULES_TL
227 where RULE_ID = X_RULE_ID;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232
233 delete from JTF_R_RULES_B
234 where RULE_ID = X_RULE_ID;
235
236 if (sql%notfound) then
237 raise no_data_found;
238 end if;
239 end DELETE_ROW;
240
241 procedure ADD_LANGUAGE
242 is
243 begin
244 delete from JTF_R_RULES_TL T
245 where not exists
246 (select NULL
247 from JTF_R_RULES_B B
248 where B.RULE_ID = T.RULE_ID
249 );
250
251 update JTF_R_RULES_TL T set (
252 NAME,
253 COND_DESC,
254 ACTION_DESC
255 ) = (select
256 B.NAME,
257 B.COND_DESC,
258 B.ACTION_DESC
259 from JTF_R_RULES_TL B
260 where B.RULE_ID = T.RULE_ID
261 and B.LANGUAGE = T.SOURCE_LANG)
262 where (
263 T.RULE_ID,
264 T.LANGUAGE
265 ) in (select
266 SUBT.RULE_ID,
267 SUBT.LANGUAGE
268 from JTF_R_RULES_TL SUBB, JTF_R_RULES_TL SUBT
269 where SUBB.RULE_ID = SUBT.RULE_ID
270 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
271 and (SUBB.NAME <> SUBT.NAME
272 or SUBB.COND_DESC <> SUBT.COND_DESC
273 or (SUBB.COND_DESC is null and SUBT.COND_DESC is not null)
274 or (SUBB.COND_DESC is not null and SUBT.COND_DESC is null)
275 or SUBB.ACTION_DESC <> SUBT.ACTION_DESC
276 or (SUBB.ACTION_DESC is null and SUBT.ACTION_DESC is not null)
277 or (SUBB.ACTION_DESC is not null and SUBT.ACTION_DESC is null)
278 ));
279
280 insert into JTF_R_RULES_TL (
281 RULE_ID,
282 CREATED_BY,
283 CREATION_DATE,
284 LAST_UPDATED_BY,
285 LAST_UPDATE_DATE,
286 LAST_UPDATE_LOGIN,
287 NAME,
288 COND_DESC,
289 ACTION_DESC,
290 OBJECT_VERSION_NUMBER,
291 LANGUAGE,
292 SOURCE_LANG
293 ) select
294 B.RULE_ID,
295 B.CREATED_BY,
296 B.CREATION_DATE,
297 B.LAST_UPDATED_BY,
298 B.LAST_UPDATE_DATE,
299 B.LAST_UPDATE_LOGIN,
300 B.NAME,
301 B.COND_DESC,
302 B.ACTION_DESC,
303 B.OBJECT_VERSION_NUMBER,
304 L.LANGUAGE_CODE,
305 B.SOURCE_LANG
306 from JTF_R_RULES_TL B, FND_LANGUAGES L
307 where L.INSTALLED_FLAG in ('I', 'B')
308 and B.LANGUAGE = userenv('LANG')
309 and not exists
310 (select NULL
311 from JTF_R_RULES_TL T
312 where T.RULE_ID = B.RULE_ID
313 and T.LANGUAGE = L.LANGUAGE_CODE);
314 end ADD_LANGUAGE;
315
316 procedure TRANSLATE_ROW(
317 X_RULE_ID in NUMBER,
318 X_OWNER in VARCHAR2,
319 X_NAME in VARCHAR2,
320 X_COND_DESC in VARCHAR2,
321 X_ACTION_DESC in VARCHAR2
322 ) is
323 begin
324 update JTF_R_RULES_TL set
325 name = nvl(X_NAME, name),
326 cond_desc = nvl(X_COND_DESC, cond_desc),
327 action_desc = nvl(X_ACTION_DESC, action_desc),
328 last_update_date = sysdate,
329 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
330 last_update_login = 0,
331 source_lang = userenv('LANG')
332 where rule_id = X_RULE_ID
333 and userenv('LANG') in (language, source_lang);
334
335 end TRANSLATE_ROW;
336
337 end JTF_R_RULES_PKG;
[Home] [Help]