1 package body AMS_WEB_RULES_PKG as
2 /* $Header: amstwrlb.pls 115.2 2003/11/11 20:28:04 musman noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_ACTIVITY_RULE_ID in NUMBER,
7 X_RULE_PRIORITY in NUMBER,
8 X_RELATIONSHIP_CODE in VARCHAR2,
9 X_CONTEXT_TYPE_CODE in VARCHAR2,
10 X_CONTEXT_ID in NUMBER,
11 X_ORGANIZATION_ID in NUMBER,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_ATTRIBUTE_CATEGORY in VARCHAR2,
14 X_ATTRIBUTE1 in VARCHAR2,
15 X_ATTRIBUTE2 in VARCHAR2,
16 X_ATTRIBUTE3 in VARCHAR2,
17 X_ATTRIBUTE4 in VARCHAR2,
18 X_ATTRIBUTE5 in VARCHAR2,
19 X_ATTRIBUTE6 in VARCHAR2,
20 X_ATTRIBUTE7 in VARCHAR2,
21 X_ATTRIBUTE8 in VARCHAR2,
22 X_ATTRIBUTE9 in VARCHAR2,
23 X_ATTRIBUTE10 in VARCHAR2,
24 X_ATTRIBUTE11 in VARCHAR2,
25 X_ATTRIBUTE12 in VARCHAR2,
26 X_ATTRIBUTE13 in VARCHAR2,
27 X_ATTRIBUTE14 in VARCHAR2,
28 X_ATTRIBUTE15 in VARCHAR2,
29 X_RULE_NAME in VARCHAR2,
30 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER
35 ) is
36 cursor C is select ROWID from AMS_WEB_RULES_B
37 where RULE_ID = X_RULE_ID;
38 begin
39 insert into AMS_WEB_RULES_B (
40 RULE_ID,
41 ACTIVITY_RULE_ID,
42 RULE_PRIORITY,
43 RELATIONSHIP_CODE,
44 CONTEXT_TYPE_CODE,
45 CONTEXT_ID,
46 ORGANIZATION_ID,
47 OBJECT_VERSION_NUMBER,
48 ATTRIBUTE_CATEGORY,
49 ATTRIBUTE1,
50 ATTRIBUTE2,
51 ATTRIBUTE3,
52 ATTRIBUTE4,
53 ATTRIBUTE5,
54 ATTRIBUTE6,
55 ATTRIBUTE7,
56 ATTRIBUTE8,
57 ATTRIBUTE9,
58 ATTRIBUTE10,
59 ATTRIBUTE11,
60 ATTRIBUTE12,
61 ATTRIBUTE13,
62 ATTRIBUTE14,
63 ATTRIBUTE15,
64 CREATION_DATE,
65 CREATED_BY,
66 LAST_UPDATE_DATE,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_LOGIN
69 ) values (
70 X_RULE_ID,
71 X_ACTIVITY_RULE_ID,
72 X_RULE_PRIORITY,
73 X_RELATIONSHIP_CODE,
74 X_CONTEXT_TYPE_CODE,
75 X_CONTEXT_ID,
76 X_ORGANIZATION_ID,
77 X_OBJECT_VERSION_NUMBER,
78 X_ATTRIBUTE_CATEGORY,
79 X_ATTRIBUTE1,
80 X_ATTRIBUTE2,
81 X_ATTRIBUTE3,
82 X_ATTRIBUTE4,
83 X_ATTRIBUTE5,
84 X_ATTRIBUTE6,
85 X_ATTRIBUTE7,
86 X_ATTRIBUTE8,
87 X_ATTRIBUTE9,
88 X_ATTRIBUTE10,
89 X_ATTRIBUTE11,
90 X_ATTRIBUTE12,
91 X_ATTRIBUTE13,
92 X_ATTRIBUTE14,
93 X_ATTRIBUTE15,
94 X_CREATION_DATE,
95 X_CREATED_BY,
96 X_LAST_UPDATE_DATE,
97 X_LAST_UPDATED_BY,
98 X_LAST_UPDATE_LOGIN
99 );
100
101 insert into AMS_WEB_RULES_TL (
102 OBJECT_VERSION_NUMBER,
103 CREATED_BY,
104 CREATION_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_DATE,
107 LAST_UPDATE_LOGIN,
108 RULE_ID,
109 RULE_NAME,
110 LANGUAGE,
111 SOURCE_LANG
112 ) select
113 X_OBJECT_VERSION_NUMBER,
114 X_CREATED_BY,
115 X_CREATION_DATE,
116 X_LAST_UPDATED_BY,
117 X_LAST_UPDATE_DATE,
118 X_LAST_UPDATE_LOGIN,
119 X_RULE_ID,
120 X_RULE_NAME,
121 L.LANGUAGE_CODE,
122 userenv('LANG')
123 from FND_LANGUAGES L
124 where L.INSTALLED_FLAG in ('I', 'B')
125 and not exists
126 (select NULL
127 from AMS_WEB_RULES_TL T
128 where T.RULE_ID = X_RULE_ID
129 and T.LANGUAGE = L.LANGUAGE_CODE);
130
131 open c;
132 fetch c into X_ROWID;
133 if (c%notfound) then
134 close c;
135 raise no_data_found;
136 end if;
137 close c;
138
139 end INSERT_ROW;
140
141 procedure LOCK_ROW (
142 X_RULE_ID in NUMBER,
143 X_ACTIVITY_RULE_ID in NUMBER,
144 X_RULE_PRIORITY in NUMBER,
145 X_RELATIONSHIP_CODE in VARCHAR2,
146 X_CONTEXT_TYPE_CODE in VARCHAR2,
147 X_CONTEXT_ID in NUMBER,
148 X_ORGANIZATION_ID in NUMBER,
149 X_OBJECT_VERSION_NUMBER in NUMBER,
150 X_ATTRIBUTE_CATEGORY in VARCHAR2,
151 X_ATTRIBUTE1 in VARCHAR2,
152 X_ATTRIBUTE2 in VARCHAR2,
153 X_ATTRIBUTE3 in VARCHAR2,
154 X_ATTRIBUTE4 in VARCHAR2,
155 X_ATTRIBUTE5 in VARCHAR2,
156 X_ATTRIBUTE6 in VARCHAR2,
157 X_ATTRIBUTE7 in VARCHAR2,
158 X_ATTRIBUTE8 in VARCHAR2,
159 X_ATTRIBUTE9 in VARCHAR2,
160 X_ATTRIBUTE10 in VARCHAR2,
161 X_ATTRIBUTE11 in VARCHAR2,
162 X_ATTRIBUTE12 in VARCHAR2,
163 X_ATTRIBUTE13 in VARCHAR2,
164 X_ATTRIBUTE14 in VARCHAR2,
165 X_ATTRIBUTE15 in VARCHAR2,
166 X_RULE_NAME in VARCHAR2
167 ) is
168 cursor c is select
169 ACTIVITY_RULE_ID,
170 RULE_PRIORITY,
171 RELATIONSHIP_CODE,
172 CONTEXT_TYPE_CODE,
173 CONTEXT_ID,
174 ORGANIZATION_ID,
175 OBJECT_VERSION_NUMBER,
176 ATTRIBUTE_CATEGORY,
177 ATTRIBUTE1,
178 ATTRIBUTE2,
179 ATTRIBUTE3,
180 ATTRIBUTE4,
181 ATTRIBUTE5,
182 ATTRIBUTE6,
183 ATTRIBUTE7,
184 ATTRIBUTE8,
185 ATTRIBUTE9,
186 ATTRIBUTE10,
187 ATTRIBUTE11,
188 ATTRIBUTE12,
189 ATTRIBUTE13,
190 ATTRIBUTE14,
191 ATTRIBUTE15
192 from AMS_WEB_RULES_B
193 where RULE_ID = X_RULE_ID
194 for update of RULE_ID nowait;
195 recinfo c%rowtype;
196
197 cursor c1 is select
198 RULE_NAME,
199 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
200 from AMS_WEB_RULES_TL
201 where RULE_ID = X_RULE_ID
202 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
203 for update of RULE_ID nowait;
204 begin
205 open c;
206 fetch c into recinfo;
207 if (c%notfound) then
208 close c;
209 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
210 app_exception.raise_exception;
211 end if;
212 close c;
213 if ( (recinfo.ACTIVITY_RULE_ID = X_ACTIVITY_RULE_ID)
214 AND ((recinfo.RULE_PRIORITY = X_RULE_PRIORITY)
215 OR ((recinfo.RULE_PRIORITY is null) AND (X_RULE_PRIORITY is null)))
216 AND (recinfo.RELATIONSHIP_CODE = X_RELATIONSHIP_CODE)
217 AND (recinfo.CONTEXT_TYPE_CODE = X_CONTEXT_TYPE_CODE)
218 AND ((recinfo.CONTEXT_ID = X_CONTEXT_ID)
219 OR ((recinfo.CONTEXT_ID is null) AND (X_CONTEXT_ID is null)))
220 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
221 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
222 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
223 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
224 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
225 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
226 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
227 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
228 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
229 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
230 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
231 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
232 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
233 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
234 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
235 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
236 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
237 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
238 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
239 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
240 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
241 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
242 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
243 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
244 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
245 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
246 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
247 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
248 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
249 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
250 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
251 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
252 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
253 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
254 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
255 ) then
256 null;
257 else
258 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
259 app_exception.raise_exception;
260 end if;
261
262 for tlinfo in c1 loop
263 if (tlinfo.BASELANG = 'Y') then
264 if ( ((tlinfo.RULE_NAME = X_RULE_NAME)
265 OR ((tlinfo.RULE_NAME is null) AND (X_RULE_NAME is null)))
266 ) then
267 null;
268 else
269 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
270 app_exception.raise_exception;
271 end if;
272 end if;
273 end loop;
274 return;
275 end LOCK_ROW;
276
277 procedure UPDATE_ROW (
278 X_RULE_ID in NUMBER,
279 X_ACTIVITY_RULE_ID in NUMBER,
280 X_RULE_PRIORITY in NUMBER,
281 X_RELATIONSHIP_CODE in VARCHAR2,
282 X_CONTEXT_TYPE_CODE in VARCHAR2,
283 X_CONTEXT_ID in NUMBER,
284 X_ORGANIZATION_ID in NUMBER,
285 X_OBJECT_VERSION_NUMBER in NUMBER,
286 X_ATTRIBUTE_CATEGORY in VARCHAR2,
287 X_ATTRIBUTE1 in VARCHAR2,
288 X_ATTRIBUTE2 in VARCHAR2,
289 X_ATTRIBUTE3 in VARCHAR2,
290 X_ATTRIBUTE4 in VARCHAR2,
291 X_ATTRIBUTE5 in VARCHAR2,
292 X_ATTRIBUTE6 in VARCHAR2,
293 X_ATTRIBUTE7 in VARCHAR2,
294 X_ATTRIBUTE8 in VARCHAR2,
295 X_ATTRIBUTE9 in VARCHAR2,
296 X_ATTRIBUTE10 in VARCHAR2,
297 X_ATTRIBUTE11 in VARCHAR2,
298 X_ATTRIBUTE12 in VARCHAR2,
299 X_ATTRIBUTE13 in VARCHAR2,
300 X_ATTRIBUTE14 in VARCHAR2,
301 X_ATTRIBUTE15 in VARCHAR2,
302 X_RULE_NAME in VARCHAR2,
303 X_LAST_UPDATE_DATE in DATE,
304 X_LAST_UPDATED_BY in NUMBER,
305 X_LAST_UPDATE_LOGIN in NUMBER
306 ) is
307 begin
308 update AMS_WEB_RULES_B set
309 ACTIVITY_RULE_ID = X_ACTIVITY_RULE_ID,
310 RULE_PRIORITY = X_RULE_PRIORITY,
311 RELATIONSHIP_CODE = X_RELATIONSHIP_CODE,
312 CONTEXT_TYPE_CODE = X_CONTEXT_TYPE_CODE,
313 CONTEXT_ID = X_CONTEXT_ID,
314 ORGANIZATION_ID = X_ORGANIZATION_ID,
315 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
316 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
317 ATTRIBUTE1 = X_ATTRIBUTE1,
318 ATTRIBUTE2 = X_ATTRIBUTE2,
319 ATTRIBUTE3 = X_ATTRIBUTE3,
320 ATTRIBUTE4 = X_ATTRIBUTE4,
321 ATTRIBUTE5 = X_ATTRIBUTE5,
322 ATTRIBUTE6 = X_ATTRIBUTE6,
323 ATTRIBUTE7 = X_ATTRIBUTE7,
324 ATTRIBUTE8 = X_ATTRIBUTE8,
325 ATTRIBUTE9 = X_ATTRIBUTE9,
326 ATTRIBUTE10 = X_ATTRIBUTE10,
327 ATTRIBUTE11 = X_ATTRIBUTE11,
328 ATTRIBUTE12 = X_ATTRIBUTE12,
329 ATTRIBUTE13 = X_ATTRIBUTE13,
330 ATTRIBUTE14 = X_ATTRIBUTE14,
331 ATTRIBUTE15 = X_ATTRIBUTE15,
332 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
333 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
334 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
335 where RULE_ID = X_RULE_ID;
336
337 if (sql%notfound) then
338 raise no_data_found;
339 end if;
340
341 update AMS_WEB_RULES_TL set
342 RULE_NAME = X_RULE_NAME,
343 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
344 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
346 SOURCE_LANG = userenv('LANG')
347 where RULE_ID = X_RULE_ID
348 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
349
350 if (sql%notfound) then
351 raise no_data_found;
352 end if;
353 end UPDATE_ROW;
354
355 procedure DELETE_ROW (
356 X_RULE_ID in NUMBER
357 ) is
358 begin
359 delete from AMS_WEB_RULES_TL
360 where RULE_ID = X_RULE_ID;
361
362 if (sql%notfound) then
363 raise no_data_found;
364 end if;
365
366 delete from AMS_WEB_RULES_B
367 where RULE_ID = X_RULE_ID;
368
369 if (sql%notfound) then
370 raise no_data_found;
371 end if;
372 end DELETE_ROW;
373
374 procedure ADD_LANGUAGE
375 is
376 begin
377 delete from AMS_WEB_RULES_TL T
378 where not exists
379 (select NULL
380 from AMS_WEB_RULES_B B
381 where B.RULE_ID = T.RULE_ID
382 );
383
384 update AMS_WEB_RULES_TL T set (
385 RULE_NAME
386 ) = (select
387 B.RULE_NAME
388 from AMS_WEB_RULES_TL B
389 where B.RULE_ID = T.RULE_ID
390 and B.LANGUAGE = T.SOURCE_LANG)
391 where (
392 T.RULE_ID,
393 T.LANGUAGE
394 ) in (select
395 SUBT.RULE_ID,
396 SUBT.LANGUAGE
397 from AMS_WEB_RULES_TL SUBB, AMS_WEB_RULES_TL SUBT
398 where SUBB.RULE_ID = SUBT.RULE_ID
399 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
400 and (SUBB.RULE_NAME <> SUBT.RULE_NAME
401 or (SUBB.RULE_NAME is null and SUBT.RULE_NAME is not null)
402 or (SUBB.RULE_NAME is not null and SUBT.RULE_NAME is null)
403 ));
404
405 insert into AMS_WEB_RULES_TL (
406 OBJECT_VERSION_NUMBER,
407 CREATED_BY,
408 CREATION_DATE,
409 LAST_UPDATED_BY,
410 LAST_UPDATE_DATE,
411 LAST_UPDATE_LOGIN,
412 RULE_ID,
413 RULE_NAME,
414 LANGUAGE,
415 SOURCE_LANG
416 ) select /*+ ORDERED */
417 B.OBJECT_VERSION_NUMBER,
418 B.CREATED_BY,
419 B.CREATION_DATE,
420 B.LAST_UPDATED_BY,
421 B.LAST_UPDATE_DATE,
422 B.LAST_UPDATE_LOGIN,
423 B.RULE_ID,
424 B.RULE_NAME,
425 L.LANGUAGE_CODE,
426 B.SOURCE_LANG
427 from AMS_WEB_RULES_TL B, FND_LANGUAGES L
428 where L.INSTALLED_FLAG in ('I', 'B')
429 and B.LANGUAGE = userenv('LANG')
430 and not exists
431 (select NULL
432 from AMS_WEB_RULES_TL T
433 where T.RULE_ID = B.RULE_ID
434 and T.LANGUAGE = L.LANGUAGE_CODE);
435 end ADD_LANGUAGE;
436
437 end AMS_WEB_RULES_PKG;
[Home] [Help]