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