[Home] [Help]
PACKAGE BODY: APPS.CSP_BUSINESS_RULES_PKG
Source
1 PACKAGE BODY CSP_BUSINESS_RULES_PKG as
2 /* $Header: csptbrub.pls 120.2 2007/12/09 20:26:47 hhaugeru ship $ */
3 -- Start of Comments
4 -- Package name : CSP_BUSINESS_RULES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_BUSINESS_RULES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptbrub.pls';
13
14
15 PROCEDURE Insert_Row(
16 px_BUSINESS_RULE_ID IN OUT NOCOPY NUMBER,
17 p_CREATED_BY NUMBER,
18 p_CREATION_DATE DATE,
19 p_LAST_UPDATED_BY NUMBER,
20 p_LAST_UPDATE_DATE DATE,
21 p_LAST_UPDATE_LOGIN NUMBER,
22 p_BUSINESS_RULE_NAME VARCHAR2,
23 p_DESCRIPTION VARCHAR2,
24 p_BUSINESS_RULE_TYPE VARCHAR2,
25 p_BUSINESS_RULE_VALUE1 NUMBER,
26 p_BUSINESS_RULE_VALUE2 NUMBER,
27 p_BUSINESS_RULE_VALUE3 NUMBER,
28 p_BUSINESS_RULE_VALUE4 NUMBER,
29 p_BUSINESS_RULE_VALUE5 NUMBER,
30 p_BUSINESS_RULE_VALUE6 NUMBER,
31 p_BUSINESS_RULE_VALUE7 NUMBER,
32 p_BUSINESS_RULE_VALUE8 NUMBER,
33 p_BUSINESS_RULE_VALUE9 NUMBER,
34 p_BUSINESS_RULE_VALUE10 NUMBER,
35 p_BUSINESS_RULE_VALUE11 NUMBER,
36 p_BUSINESS_RULE_VALUE12 NUMBER,
37 p_BUSINESS_RULE_VALUE13 NUMBER,
38 p_BUSINESS_RULE_VALUE14 NUMBER,
39 p_BUSINESS_RULE_VALUE15 NUMBER,
40 p_BUSINESS_RULE_VALUE16 NUMBER,
41 p_BUSINESS_RULE_VALUE17 NUMBER,
42 p_BUSINESS_RULE_VALUE18 NUMBER,
43 p_BUSINESS_RULE_VALUE19 NUMBER,
44 p_BUSINESS_RULE_VALUE20 NUMBER,
45 p_ATTRIBUTE_CATEGORY VARCHAR2,
46 p_ATTRIBUTE1 VARCHAR2,
47 p_ATTRIBUTE2 VARCHAR2,
48 p_ATTRIBUTE3 VARCHAR2,
49 p_ATTRIBUTE4 VARCHAR2,
50 p_ATTRIBUTE5 VARCHAR2,
51 p_ATTRIBUTE6 VARCHAR2,
52 p_ATTRIBUTE7 VARCHAR2,
53 p_ATTRIBUTE8 VARCHAR2,
54 p_ATTRIBUTE9 VARCHAR2,
55 p_ATTRIBUTE10 VARCHAR2,
56 p_ATTRIBUTE11 VARCHAR2,
57 p_ATTRIBUTE12 VARCHAR2,
58 p_ATTRIBUTE13 VARCHAR2,
59 p_ATTRIBUTE14 VARCHAR2,
60 p_ATTRIBUTE15 VARCHAR2
61 )
62 IS
63 CURSOR C2 IS SELECT CSP_BUSINESS_RULES_B_S1.nextval FROM sys.dual;
64 BEGIN
65 If (px_BUSINESS_RULE_ID IS NULL) then
66 OPEN C2;
67 FETCH C2 INTO px_BUSINESS_RULE_ID;
68 CLOSE C2;
69 End If;
70 INSERT INTO CSP_BUSINESS_RULES_B(
71 BUSINESS_RULE_ID,
72 CREATED_BY,
73 CREATION_DATE,
74 LAST_UPDATED_BY,
75 LAST_UPDATE_DATE,
76 LAST_UPDATE_LOGIN,
77 BUSINESS_RULE_NAME,
78 BUSINESS_RULE_TYPE,
79 BUSINESS_RULE_VALUE1,
80 BUSINESS_RULE_VALUE2,
81 BUSINESS_RULE_VALUE3,
82 BUSINESS_RULE_VALUE4,
83 BUSINESS_RULE_VALUE5,
84 BUSINESS_RULE_VALUE6,
85 BUSINESS_RULE_VALUE7,
86 BUSINESS_RULE_VALUE8,
87 BUSINESS_RULE_VALUE9,
88 BUSINESS_RULE_VALUE10,
89 BUSINESS_RULE_VALUE11,
90 BUSINESS_RULE_VALUE12,
91 BUSINESS_RULE_VALUE13,
92 BUSINESS_RULE_VALUE14,
93 BUSINESS_RULE_VALUE15,
94 BUSINESS_RULE_VALUE16,
95 BUSINESS_RULE_VALUE17,
96 BUSINESS_RULE_VALUE18,
97 BUSINESS_RULE_VALUE19,
98 BUSINESS_RULE_VALUE20,
99 ATTRIBUTE_CATEGORY,
100 ATTRIBUTE1,
101 ATTRIBUTE2,
102 ATTRIBUTE3,
103 ATTRIBUTE4,
104 ATTRIBUTE5,
105 ATTRIBUTE6,
106 ATTRIBUTE7,
107 ATTRIBUTE8,
108 ATTRIBUTE9,
109 ATTRIBUTE10,
110 ATTRIBUTE11,
111 ATTRIBUTE12,
112 ATTRIBUTE13,
113 ATTRIBUTE14,
114 ATTRIBUTE15
115 ) VALUES (
116 px_BUSINESS_RULE_ID,
117 p_CREATED_BY,
118 p_CREATION_DATE,
119 p_LAST_UPDATED_BY,
120 p_LAST_UPDATE_DATE,
121 p_LAST_UPDATE_LOGIN,
122 p_BUSINESS_RULE_NAME,
123 p_BUSINESS_RULE_TYPE,
124 p_BUSINESS_RULE_VALUE1,
125 p_BUSINESS_RULE_VALUE2,
126 p_BUSINESS_RULE_VALUE3,
127 p_BUSINESS_RULE_VALUE4,
128 p_BUSINESS_RULE_VALUE5,
129 p_BUSINESS_RULE_VALUE6,
130 p_BUSINESS_RULE_VALUE7,
131 p_BUSINESS_RULE_VALUE8,
132 p_BUSINESS_RULE_VALUE9,
133 p_BUSINESS_RULE_VALUE10,
134 p_BUSINESS_RULE_VALUE11,
135 p_BUSINESS_RULE_VALUE12,
136 p_BUSINESS_RULE_VALUE13,
137 p_BUSINESS_RULE_VALUE14,
138 p_BUSINESS_RULE_VALUE15,
139 p_BUSINESS_RULE_VALUE16,
140 p_BUSINESS_RULE_VALUE17,
141 p_BUSINESS_RULE_VALUE18,
142 p_BUSINESS_RULE_VALUE19,
143 p_BUSINESS_RULE_VALUE20,
144 p_ATTRIBUTE_CATEGORY,
145 p_ATTRIBUTE1,
146 p_ATTRIBUTE2,
147 p_ATTRIBUTE3,
148 p_ATTRIBUTE4,
149 p_ATTRIBUTE5,
150 p_ATTRIBUTE6,
151 p_ATTRIBUTE7,
152 p_ATTRIBUTE8,
153 p_ATTRIBUTE9,
154 p_ATTRIBUTE10,
155 p_ATTRIBUTE11,
156 p_ATTRIBUTE12,
157 p_ATTRIBUTE13,
158 p_ATTRIBUTE14,
159 p_ATTRIBUTE15);
160
161 insert into CSP_BUSINESS_RULES_TL (
162 BUSINESS_RULE_ID,
163 CREATED_BY,
164 CREATION_DATE,
165 LAST_UPDATED_BY,
166 LAST_UPDATE_DATE,
167 LAST_UPDATE_LOGIN,
168 DESCRIPTION,
169 LANGUAGE,
170 SOURCE_LANG
171 ) select
172 pX_BUSINESS_RULE_ID,
173 p_CREATED_BY,
174 p_CREATION_DATE,
175 p_LAST_UPDATED_BY,
176 p_last_update_DATE,
177 p_LAST_UPDATE_LOGIN,
178 p_DESCRIPTION,
179 L.LANGUAGE_CODE,
180 userenv('LANG')
181 from FND_LANGUAGES L
182 where L.INSTALLED_FLAG in ('I', 'B')
183 and not exists
184 (select NULL
185 from CSP_BUSINESS_RULES_TL T
186 where T.BUSINESS_RULE_ID = pX_BUSINESS_RULE_ID
187 and T.LANGUAGE = L.LANGUAGE_CODE);
188
189 End Insert_Row;
190
191 PROCEDURE Update_Row(
192 p_BUSINESS_RULE_ID NUMBER,
193 p_CREATED_BY NUMBER,
194 p_CREATION_DATE DATE,
195 p_LAST_UPDATED_BY NUMBER,
196 p_LAST_UPDATE_DATE DATE,
197 p_LAST_UPDATE_LOGIN NUMBER,
198 p_BUSINESS_RULE_NAME VARCHAR2,
199 p_DESCRIPTION VARCHAR2,
200 p_BUSINESS_RULE_TYPE VARCHAR2,
201 p_BUSINESS_RULE_VALUE1 NUMBER,
202 p_BUSINESS_RULE_VALUE2 NUMBER,
203 p_BUSINESS_RULE_VALUE3 NUMBER,
204 p_BUSINESS_RULE_VALUE4 NUMBER,
205 p_BUSINESS_RULE_VALUE5 NUMBER,
206 p_BUSINESS_RULE_VALUE6 NUMBER,
207 p_BUSINESS_RULE_VALUE7 NUMBER,
208 p_BUSINESS_RULE_VALUE8 NUMBER,
209 p_BUSINESS_RULE_VALUE9 NUMBER,
210 p_BUSINESS_RULE_VALUE10 NUMBER,
211 p_BUSINESS_RULE_VALUE11 NUMBER,
212 p_BUSINESS_RULE_VALUE12 NUMBER,
213 p_BUSINESS_RULE_VALUE13 NUMBER,
214 p_BUSINESS_RULE_VALUE14 NUMBER,
215 p_BUSINESS_RULE_VALUE15 NUMBER,
216 p_BUSINESS_RULE_VALUE16 NUMBER,
217 p_BUSINESS_RULE_VALUE17 NUMBER,
218 p_BUSINESS_RULE_VALUE18 NUMBER,
219 p_BUSINESS_RULE_VALUE19 NUMBER,
220 p_BUSINESS_RULE_VALUE20 NUMBER,
221 p_ATTRIBUTE_CATEGORY VARCHAR2,
222 p_ATTRIBUTE1 VARCHAR2,
223 p_ATTRIBUTE2 VARCHAR2,
224 p_ATTRIBUTE3 VARCHAR2,
225 p_ATTRIBUTE4 VARCHAR2,
226 p_ATTRIBUTE5 VARCHAR2,
227 p_ATTRIBUTE6 VARCHAR2,
228 p_ATTRIBUTE7 VARCHAR2,
229 p_ATTRIBUTE8 VARCHAR2,
230 p_ATTRIBUTE9 VARCHAR2,
231 p_ATTRIBUTE10 VARCHAR2,
232 p_ATTRIBUTE11 VARCHAR2,
233 p_ATTRIBUTE12 VARCHAR2,
234 p_ATTRIBUTE13 VARCHAR2,
235 p_ATTRIBUTE14 VARCHAR2,
236 p_ATTRIBUTE15 VARCHAR2)
237 IS
238 BEGIN
239 Update CSP_BUSINESS_RULES_B
240 SET
241 CREATED_BY = p_CREATED_BY,
242 CREATION_DATE = p_CREATION_DATE,
243 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
244 LAST_UPDATE_DATE = p_last_update_DATE,
245 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
246 BUSINESS_RULE_NAME = p_BUSINESS_RULE_NAME,
247 BUSINESS_RULE_TYPE = p_BUSINESS_RULE_TYPE,
248 BUSINESS_RULE_VALUE1 = p_BUSINESS_RULE_VALUE1,
249 BUSINESS_RULE_VALUE2 = p_BUSINESS_RULE_VALUE2,
250 BUSINESS_RULE_VALUE3 = p_BUSINESS_RULE_VALUE3,
251 BUSINESS_RULE_VALUE4 = p_BUSINESS_RULE_VALUE4,
252 BUSINESS_RULE_VALUE5 = p_BUSINESS_RULE_VALUE5,
253 BUSINESS_RULE_VALUE6 = p_BUSINESS_RULE_VALUE6,
254 BUSINESS_RULE_VALUE7 = p_BUSINESS_RULE_VALUE7,
255 BUSINESS_RULE_VALUE8 = p_BUSINESS_RULE_VALUE8,
256 BUSINESS_RULE_VALUE9 = p_BUSINESS_RULE_VALUE9,
257 BUSINESS_RULE_VALUE10 = p_BUSINESS_RULE_VALUE10,
258 BUSINESS_RULE_VALUE11 = p_BUSINESS_RULE_VALUE11,
259 BUSINESS_RULE_VALUE12 = p_BUSINESS_RULE_VALUE12,
260 BUSINESS_RULE_VALUE13 = p_BUSINESS_RULE_VALUE13,
261 BUSINESS_RULE_VALUE14 = p_BUSINESS_RULE_VALUE14,
262 BUSINESS_RULE_VALUE15 = p_BUSINESS_RULE_VALUE15,
263 BUSINESS_RULE_VALUE16 = p_BUSINESS_RULE_VALUE16,
264 BUSINESS_RULE_VALUE17 = p_BUSINESS_RULE_VALUE17,
265 BUSINESS_RULE_VALUE18 = p_BUSINESS_RULE_VALUE18,
266 BUSINESS_RULE_VALUE19 = p_BUSINESS_RULE_VALUE19,
267 BUSINESS_RULE_VALUE20 = p_BUSINESS_RULE_VALUE20,
268 ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY,
269 ATTRIBUTE1 = p_ATTRIBUTE1,
270 ATTRIBUTE2 = p_ATTRIBUTE2,
271 ATTRIBUTE3 = p_ATTRIBUTE3,
272 ATTRIBUTE4 = p_ATTRIBUTE4,
273 ATTRIBUTE5 = p_ATTRIBUTE5,
274 ATTRIBUTE6 = p_ATTRIBUTE6,
275 ATTRIBUTE7 = p_ATTRIBUTE7,
276 ATTRIBUTE8 = p_ATTRIBUTE8,
277 ATTRIBUTE9 = p_ATTRIBUTE9,
278 ATTRIBUTE10 = p_ATTRIBUTE10,
279 ATTRIBUTE11 = p_ATTRIBUTE11,
280 ATTRIBUTE12 = p_ATTRIBUTE12,
281 ATTRIBUTE13 = p_ATTRIBUTE13,
282 ATTRIBUTE14 = p_ATTRIBUTE14,
283 ATTRIBUTE15 = p_ATTRIBUTE15
284 where BUSINESS_RULE_ID = p_BUSINESS_RULE_ID;
285
286 if (sql%notfound) then
287 raise no_data_found;
288 end if;
289
290 update CSP_BUSINESS_RULES_TL set
291 DESCRIPTION = p_DESCRIPTION,
292 LAST_UPDATE_DATE = p_last_update_DATE,
293 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
294 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
295 SOURCE_LANG = userenv('LANG')
296 where BUSINESS_RULE_ID = p_BUSINESS_RULE_ID
297 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302
303
304 END Update_Row;
305
306 PROCEDURE Delete_Row(
307 p_BUSINESS_RULE_ID NUMBER)
308 IS
309 BEGIN
310 delete from CSP_BUSINESS_RULES_TL
311 where BUSINESS_RULE_ID = p_BUSINESS_RULE_ID;
312
313 if (sql%notfound) then
314 raise no_data_found;
315 end if;
316
317 DELETE FROM CSP_BUSINESS_RULES_B
318 WHERE BUSINESS_RULE_ID = p_BUSINESS_RULE_ID;
319 If (SQL%NOTFOUND) then
320 RAISE NO_DATA_FOUND;
321 End If;
322 END Delete_Row;
323
324 PROCEDURE Lock_Row(
325 p_BUSINESS_RULE_ID NUMBER,
326 p_CREATED_BY NUMBER,
327 p_CREATION_DATE DATE,
328 p_LAST_UPDATED_BY NUMBER,
329 p_LAST_UPDATE_DATE DATE,
330 p_LAST_UPDATE_LOGIN NUMBER,
331 p_BUSINESS_RULE_NAME VARCHAR2,
332 p_DESCRIPTION VARCHAR2,
333 p_BUSINESS_RULE_TYPE VARCHAR2,
334 p_BUSINESS_RULE_VALUE1 NUMBER,
335 p_BUSINESS_RULE_VALUE2 NUMBER,
336 p_BUSINESS_RULE_VALUE3 NUMBER,
337 p_BUSINESS_RULE_VALUE4 NUMBER,
338 p_BUSINESS_RULE_VALUE5 NUMBER,
342 p_BUSINESS_RULE_VALUE9 NUMBER,
339 p_BUSINESS_RULE_VALUE6 NUMBER,
340 p_BUSINESS_RULE_VALUE7 NUMBER,
341 p_BUSINESS_RULE_VALUE8 NUMBER,
343 p_BUSINESS_RULE_VALUE10 NUMBER,
344 p_BUSINESS_RULE_VALUE11 NUMBER,
345 p_BUSINESS_RULE_VALUE12 NUMBER,
346 p_BUSINESS_RULE_VALUE13 NUMBER,
347 p_BUSINESS_RULE_VALUE14 NUMBER,
348 p_BUSINESS_RULE_VALUE15 NUMBER,
349 p_BUSINESS_RULE_VALUE16 NUMBER,
350 p_BUSINESS_RULE_VALUE17 NUMBER,
351 p_BUSINESS_RULE_VALUE18 NUMBER,
352 p_BUSINESS_RULE_VALUE19 NUMBER,
353 p_BUSINESS_RULE_VALUE20 NUMBER,
354 p_ATTRIBUTE_CATEGORY VARCHAR2,
355 p_ATTRIBUTE1 VARCHAR2,
356 p_ATTRIBUTE2 VARCHAR2,
357 p_ATTRIBUTE3 VARCHAR2,
358 p_ATTRIBUTE4 VARCHAR2,
359 p_ATTRIBUTE5 VARCHAR2,
360 p_ATTRIBUTE6 VARCHAR2,
361 p_ATTRIBUTE7 VARCHAR2,
362 p_ATTRIBUTE8 VARCHAR2,
363 p_ATTRIBUTE9 VARCHAR2,
364 p_ATTRIBUTE10 VARCHAR2,
365 p_ATTRIBUTE11 VARCHAR2,
366 p_ATTRIBUTE12 VARCHAR2,
367 p_ATTRIBUTE13 VARCHAR2,
368 p_ATTRIBUTE14 VARCHAR2,
369 p_ATTRIBUTE15 VARCHAR2)
370 IS
371 CURSOR C IS
372 SELECT *
373 FROM CSP_BUSINESS_RULES_B
374 WHERE BUSINESS_RULE_ID = p_BUSINESS_RULE_ID
375 FOR UPDATE of BUSINESS_RULE_ID NOWAIT;
376
377 cursor c1 is select
378 DESCRIPTION,
379 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
380 from CSP_BUSINESS_RULES_TL
381 where BUSINESS_RULE_ID = p_BUSINESS_RULE_ID
382 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
383 for update of BUSINESS_RULE_ID nowait;
384
385 Recinfo C%ROWTYPE;
386 BEGIN
387 OPEN C;
388 FETCH C INTO Recinfo;
389 If (C%NOTFOUND) then
390 CLOSE C;
391 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
392 APP_EXCEPTION.RAISE_EXCEPTION;
393 End If;
394 CLOSE C;
395
396 if (
397 ( Recinfo.BUSINESS_RULE_ID = p_BUSINESS_RULE_ID)
398 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
399 OR ( ( Recinfo.CREATED_BY IS NULL )
400 AND ( p_CREATED_BY IS NULL )))
401 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
402 OR ( ( Recinfo.CREATION_DATE IS NULL )
403 AND ( p_CREATION_DATE IS NULL )))
404 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
405 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
406 AND ( p_LAST_UPDATED_BY IS NULL )))
407 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
408 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
409 AND ( p_LAST_UPDATE_DATE IS NULL )))
410 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
411 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
412 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
413 AND ( ( Recinfo.BUSINESS_RULE_NAME = p_BUSINESS_RULE_NAME)
414 OR ( ( Recinfo.BUSINESS_RULE_NAME IS NULL )
415 AND ( p_BUSINESS_RULE_NAME IS NULL )))
416 AND ( ( Recinfo.BUSINESS_RULE_TYPE = p_BUSINESS_RULE_TYPE)
417 OR ( ( Recinfo.BUSINESS_RULE_TYPE IS NULL )
418 AND ( p_BUSINESS_RULE_TYPE IS NULL )))
419 AND ( ( Recinfo.BUSINESS_RULE_VALUE1 = p_BUSINESS_RULE_VALUE1)
420 OR ( ( Recinfo.BUSINESS_RULE_VALUE1 IS NULL )
421 AND ( p_BUSINESS_RULE_VALUE1 IS NULL )))
422 AND ( ( Recinfo.BUSINESS_RULE_VALUE2 = p_BUSINESS_RULE_VALUE2)
423 OR ( ( Recinfo.BUSINESS_RULE_VALUE2 IS NULL )
424 AND ( p_BUSINESS_RULE_VALUE2 IS NULL )))
425 AND ( ( Recinfo.BUSINESS_RULE_VALUE3 = p_BUSINESS_RULE_VALUE3)
426 OR ( ( Recinfo.BUSINESS_RULE_VALUE3 IS NULL )
427 AND ( p_BUSINESS_RULE_VALUE3 IS NULL )))
428 AND ( ( Recinfo.BUSINESS_RULE_VALUE4 = p_BUSINESS_RULE_VALUE4)
429 OR ( ( Recinfo.BUSINESS_RULE_VALUE4 IS NULL )
430 AND ( p_BUSINESS_RULE_VALUE4 IS NULL )))
431 AND ( ( Recinfo.BUSINESS_RULE_VALUE5 = p_BUSINESS_RULE_VALUE5)
432 OR ( ( Recinfo.BUSINESS_RULE_VALUE5 IS NULL )
433 AND ( p_BUSINESS_RULE_VALUE5 IS NULL )))
434 AND ( ( Recinfo.BUSINESS_RULE_VALUE6 = p_BUSINESS_RULE_VALUE6)
435 OR ( ( Recinfo.BUSINESS_RULE_VALUE6 IS NULL )
436 AND ( p_BUSINESS_RULE_VALUE6 IS NULL )))
437 AND ( ( Recinfo.BUSINESS_RULE_VALUE7 = p_BUSINESS_RULE_VALUE7)
438 OR ( ( Recinfo.BUSINESS_RULE_VALUE7 IS NULL )
439 AND ( p_BUSINESS_RULE_VALUE7 IS NULL )))
440 AND ( ( Recinfo.BUSINESS_RULE_VALUE8 = p_BUSINESS_RULE_VALUE8)
441 OR ( ( Recinfo.BUSINESS_RULE_VALUE8 IS NULL )
442 AND ( p_BUSINESS_RULE_VALUE8 IS NULL )))
443 AND ( ( Recinfo.BUSINESS_RULE_VALUE9 = p_BUSINESS_RULE_VALUE9)
444 OR ( ( Recinfo.BUSINESS_RULE_VALUE9 IS NULL )
445 AND ( p_BUSINESS_RULE_VALUE9 IS NULL )))
446 AND ( ( Recinfo.BUSINESS_RULE_VALUE10 = p_BUSINESS_RULE_VALUE10)
447 OR ( ( Recinfo.BUSINESS_RULE_VALUE10 IS NULL )
448 AND ( p_BUSINESS_RULE_VALUE10 IS NULL )))
449 AND ( ( Recinfo.BUSINESS_RULE_VALUE11 = p_BUSINESS_RULE_VALUE11)
450 OR ( ( Recinfo.BUSINESS_RULE_VALUE11 IS NULL )
451 AND ( p_BUSINESS_RULE_VALUE11 IS NULL )))
452 AND ( ( Recinfo.BUSINESS_RULE_VALUE12 = p_BUSINESS_RULE_VALUE12)
453 OR ( ( Recinfo.BUSINESS_RULE_VALUE12 IS NULL )
454 AND ( p_BUSINESS_RULE_VALUE12 IS NULL )))
455 AND ( ( Recinfo.BUSINESS_RULE_VALUE13 = p_BUSINESS_RULE_VALUE13)
459 OR ( ( Recinfo.BUSINESS_RULE_VALUE14 IS NULL )
456 OR ( ( Recinfo.BUSINESS_RULE_VALUE13 IS NULL )
457 AND ( p_BUSINESS_RULE_VALUE13 IS NULL )))
458 AND ( ( Recinfo.BUSINESS_RULE_VALUE14 = p_BUSINESS_RULE_VALUE14)
460 AND ( p_BUSINESS_RULE_VALUE14 IS NULL )))
461 AND ( ( Recinfo.BUSINESS_RULE_VALUE15 = p_BUSINESS_RULE_VALUE15)
462 OR ( ( Recinfo.BUSINESS_RULE_VALUE15 IS NULL )
463 AND ( p_BUSINESS_RULE_VALUE15 IS NULL )))
464 AND ( ( Recinfo.BUSINESS_RULE_VALUE16 = p_BUSINESS_RULE_VALUE16)
465 OR ( ( Recinfo.BUSINESS_RULE_VALUE16 IS NULL )
466 AND ( p_BUSINESS_RULE_VALUE16 IS NULL )))
467 AND ( ( Recinfo.BUSINESS_RULE_VALUE17 = p_BUSINESS_RULE_VALUE17)
468 OR ( ( Recinfo.BUSINESS_RULE_VALUE17 IS NULL )
469 AND ( p_BUSINESS_RULE_VALUE17 IS NULL )))
470 AND ( ( Recinfo.BUSINESS_RULE_VALUE18 = p_BUSINESS_RULE_VALUE18)
471 OR ( ( Recinfo.BUSINESS_RULE_VALUE18 IS NULL )
472 AND ( p_BUSINESS_RULE_VALUE18 IS NULL )))
473 AND ( ( Recinfo.BUSINESS_RULE_VALUE19 = p_BUSINESS_RULE_VALUE19)
474 OR ( ( Recinfo.BUSINESS_RULE_VALUE19 IS NULL )
475 AND ( p_BUSINESS_RULE_VALUE19 IS NULL )))
476 AND ( ( Recinfo.BUSINESS_RULE_VALUE20 = p_BUSINESS_RULE_VALUE20)
477 OR ( ( Recinfo.BUSINESS_RULE_VALUE20 IS NULL )
478 AND ( p_BUSINESS_RULE_VALUE20 IS NULL )))
479 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
480 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
481 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
482 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
483 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
484 AND ( p_ATTRIBUTE1 IS NULL )))
485 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
486 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
487 AND ( p_ATTRIBUTE2 IS NULL )))
488 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
489 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
490 AND ( p_ATTRIBUTE3 IS NULL )))
491 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
492 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
493 AND ( p_ATTRIBUTE4 IS NULL )))
494 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
495 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
496 AND ( p_ATTRIBUTE5 IS NULL )))
497 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
498 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
499 AND ( p_ATTRIBUTE6 IS NULL )))
500 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
501 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
502 AND ( p_ATTRIBUTE7 IS NULL )))
503 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
504 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
505 AND ( p_ATTRIBUTE8 IS NULL )))
506 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
507 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
508 AND ( p_ATTRIBUTE9 IS NULL )))
509 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
510 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
511 AND ( p_ATTRIBUTE10 IS NULL )))
512 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
513 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
514 AND ( p_ATTRIBUTE11 IS NULL )))
515 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
516 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
517 AND ( p_ATTRIBUTE12 IS NULL )))
518 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
519 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
520 AND ( p_ATTRIBUTE13 IS NULL )))
521 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
522 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
523 AND ( p_ATTRIBUTE14 IS NULL )))
524 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
525 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
526 AND ( p_ATTRIBUTE15 IS NULL )))
527 ) then
528
529 null;
530 else
531 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
532 APP_EXCEPTION.RAISE_EXCEPTION;
533 End If;
534
535 for tlinfo in c1 loop
536 if (tlinfo.BASELANG = 'Y') then
537 if ( ((tlinfo.DESCRIPTION = p_DESCRIPTION)
538 OR ((tlinfo.DESCRIPTION is null) AND (p_DESCRIPTION is null)))
539 ) then
540 null;
541 else
542 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
543 app_exception.raise_exception;
544 end if;
545 end if;
546 end loop;
547 return;
548
549 END Lock_Row;
550
551 procedure ADD_LANGUAGE
552 is
553 begin
554 delete from CSP_BUSINESS_RULES_TL T
555 where not exists
556 (select NULL
557 from CSP_BUSINESS_RULES_B B
558 where B.BUSINESS_RULE_ID = T.BUSINESS_RULE_ID
559 );
560
561 update CSP_BUSINESS_RULES_TL T set (
562 DESCRIPTION
563 ) = (select
564 B.DESCRIPTION
565 from CSP_BUSINESS_RULES_TL B
566 where B.BUSINESS_RULE_ID = T.BUSINESS_RULE_ID
567 and B.LANGUAGE = T.SOURCE_LANG)
568 where (
569 T.BUSINESS_RULE_ID,
570 T.LANGUAGE
571 ) in (select
572 SUBT.BUSINESS_RULE_ID,
573 SUBT.LANGUAGE
574 from CSP_BUSINESS_RULES_TL SUBB, CSP_BUSINESS_RULES_TL SUBT
575 where SUBB.BUSINESS_RULE_ID = SUBT.BUSINESS_RULE_ID
576 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
577 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
578 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
579 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
580 ));
581
585 CREATION_DATE,
582 insert into CSP_BUSINESS_RULES_TL (
583 BUSINESS_RULE_ID,
584 CREATED_BY,
586 LAST_UPDATED_BY,
587 LAST_UPDATE_DATE,
588 LAST_UPDATE_LOGIN,
589 DESCRIPTION,
590 LANGUAGE,
591 SOURCE_LANG
592 ) select
593 B.BUSINESS_RULE_ID,
594 B.CREATED_BY,
595 B.CREATION_DATE,
596 B.LAST_UPDATED_BY,
597 B.LAST_UPDATE_DATE,
598 B.LAST_UPDATE_LOGIN,
599 B.DESCRIPTION,
600 L.LANGUAGE_CODE,
601 B.SOURCE_LANG
602 from CSP_BUSINESS_RULES_TL B, FND_LANGUAGES L
603 where L.INSTALLED_FLAG in ('I', 'B')
604 and B.LANGUAGE = userenv('LANG')
605 and not exists
606 (select NULL
607 from CSP_BUSINESS_RULES_TL T
608 where T.BUSINESS_RULE_ID = B.BUSINESS_RULE_ID
609 and T.LANGUAGE = L.LANGUAGE_CODE);
610 end ADD_LANGUAGE;
611
612 PROCEDURE Translate_Row
613 ( p_business_rule_id IN NUMBER
614 , p_description IN VARCHAR2
615 , p_owner IN VARCHAR2
616 )
617 IS
618 l_user_id NUMBER := 0;
619 BEGIN
620
621 if p_owner = 'SEED' then
622 l_user_id := 1;
623 end if;
624
625 UPDATE csp_business_rules_tl
626 SET description = p_description
627 , last_update_date = SYSDATE
628 , last_updated_by = l_user_id
629 , last_update_login = 0
630 , source_lang = userenv('LANG')
631 WHERE business_rule_id = p_business_rule_id
632 AND userenv('LANG') IN (language, source_lang);
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
637 THEN
638 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Translate_Row');
639 END IF;
640 RAISE;
641
642 END Translate_Row;
643
644 PROCEDURE Load_Row
645 ( p_business_rule_id IN NUMBER
646 , p_description IN VARCHAR2
647 , p_owner IN VARCHAR2
648 )
649 IS
650
651 l_business_rule_id NUMBER;
652 l_user_id NUMBER := 0;
653
654 BEGIN
655
656 -- assign user ID
657 if p_owner = 'SEED' then
658 l_user_id := 1; --SEED
659 end if;
660
661 BEGIN
662 -- update row if present
663 Update_Row(
664 p_business_rule_id => p_business_rule_id,
665 p_CREATED_BY => FND_API.G_MISS_NUM,
666 p_CREATION_DATE => FND_API.G_MISS_DATE,
667 p_LAST_UPDATED_BY => l_user_id,
668 p_LAST_UPDATE_DATE => SYSDATE,
669 p_LAST_UPDATE_LOGIN => 0,
670 p_business_rule_name => FND_API.G_MISS_CHAR,
671 p_business_rule_type => FND_API.G_MISS_CHAR,
672 p_business_rule_value1 => FND_API.G_MISS_NUM,
673 p_business_rule_value2 => FND_API.G_MISS_NUM,
674 p_business_rule_value3 => FND_API.G_MISS_NUM,
675 p_business_rule_value4 => FND_API.G_MISS_NUM,
676 p_business_rule_value5 => FND_API.G_MISS_NUM,
677 p_business_rule_value6 => FND_API.G_MISS_NUM,
678 p_business_rule_value7 => FND_API.G_MISS_NUM,
679 p_business_rule_value8 => FND_API.G_MISS_NUM,
680 p_business_rule_value9 => FND_API.G_MISS_NUM,
681 p_business_rule_value10 => FND_API.G_MISS_NUM,
682 p_business_rule_value11 => FND_API.G_MISS_NUM,
683 p_business_rule_value12 => FND_API.G_MISS_NUM,
684 p_business_rule_value13 => FND_API.G_MISS_NUM,
685 p_business_rule_value14 => FND_API.G_MISS_NUM,
686 p_business_rule_value15 => FND_API.G_MISS_NUM,
687 p_business_rule_value16 => FND_API.G_MISS_NUM,
688 p_business_rule_value17 => FND_API.G_MISS_NUM,
689 p_business_rule_value18 => FND_API.G_MISS_NUM,
690 p_business_rule_value19 => FND_API.G_MISS_NUM,
691 p_business_rule_value20 => FND_API.G_MISS_NUM,
692 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
693 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
694 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
695 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
696 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
697 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
698 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
699 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
700 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
701 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
702 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
703 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
704 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
705 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
706 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
707 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
708 p_DESCRIPTION => p_description);
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 -- insert row
712 Insert_Row(
713 px_business_rule_id => l_business_rule_id,
714 p_CREATED_BY => FND_API.G_MISS_NUM,
715 p_CREATION_DATE => FND_API.G_MISS_DATE,
716 p_LAST_UPDATED_BY => l_user_id,
717 p_LAST_UPDATE_DATE => SYSDATE,
718 p_LAST_UPDATE_LOGIN => 0,
722 p_business_rule_value2 => FND_API.G_MISS_NUM,
719 p_business_rule_name => FND_API.G_MISS_CHAR,
720 p_business_rule_type => FND_API.G_MISS_CHAR,
721 p_business_rule_value1 => FND_API.G_MISS_NUM,
723 p_business_rule_value3 => FND_API.G_MISS_NUM,
724 p_business_rule_value4 => FND_API.G_MISS_NUM,
725 p_business_rule_value5 => FND_API.G_MISS_NUM,
726 p_business_rule_value6 => FND_API.G_MISS_NUM,
727 p_business_rule_value7 => FND_API.G_MISS_NUM,
728 p_business_rule_value8 => FND_API.G_MISS_NUM,
729 p_business_rule_value9 => FND_API.G_MISS_NUM,
730 p_business_rule_value10 => FND_API.G_MISS_NUM,
731 p_business_rule_value11 => FND_API.G_MISS_NUM,
732 p_business_rule_value12 => FND_API.G_MISS_NUM,
733 p_business_rule_value13 => FND_API.G_MISS_NUM,
734 p_business_rule_value14 => FND_API.G_MISS_NUM,
735 p_business_rule_value15 => FND_API.G_MISS_NUM,
736 p_business_rule_value16 => FND_API.G_MISS_NUM,
737 p_business_rule_value17 => FND_API.G_MISS_NUM,
738 p_business_rule_value18 => FND_API.G_MISS_NUM,
739 p_business_rule_value19 => FND_API.G_MISS_NUM,
740 p_business_rule_value20 => FND_API.G_MISS_NUM,
741 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
742 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
743 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
744 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
745 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
746 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
747 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
748 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
749 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
750 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
751 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
752 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
753 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
754 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
755 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
756 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
757 p_DESCRIPTION => p_description);
758 END;
759
760 EXCEPTION
761 WHEN OTHERS THEN
762 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
763 THEN
764 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Load_Row');
765 END IF;
766 RAISE;
767
768 END Load_Row;
769
770 End CSP_BUSINESS_RULES_PKG;