[Home] [Help]
PACKAGE BODY: APPS.DEFCONDN_PKG
Source
1 PACKAGE BODY DEFCONDN_pkg AS
2 /* $Header: OEXVDCDB.pls 120.0 2005/05/31 23:47:49 appldev noship $ */
3
4
5 --------------------------------------------------------------------------
6 FUNCTION Check_References(p_condition_id in NUMBER)
7 --------------------------------------------------------------------------
8 RETURN BOOLEAN
9 IS
10 dummy NUMBER :=0;
11 CURSOR C1 IS
12 SELECT 1
13 FROM dual
14 WHERE EXISTS (SELECT 1
15 FROM OE_DEF_ATTR_CONDNS
16 WHERE CONDITION_ID = p_condition_id);
17 CURSOR C2 IS
18 SELECT 1
19 FROM dual
20 WHERE EXISTS (SELECT 1
21 FROM OE_DEF_CONDN_ELEMS
22 WHERE CONDITION_ID = p_condition_id);
23 BEGIN
24
25 -- Check if there are attribute level defaulting conditions that
26 -- reference this condition
27 OPEN C1;
28 FETCH C1 INTO dummy;
29 IF (C1%NOTFOUND) THEN
30 -- Check if there are condition elements or validation rules
31 -- associate with this condition
32 OPEN C2;
33 FETCH C2 INTO dummy;
34 IF (C2%FOUND) THEN
35 FND_MESSAGE.SET_NAME('ONT','OE_DEF_CONDN_REF_ELEM');
36 APP_EXCEPTION.RAISE_EXCEPTION;
37 END IF;
38 ELSE
39 FND_MESSAGE.SET_NAME('ONT','OE_DEF_CONDN_REF_ATTR');
40 APP_EXCEPTION.RAISE_EXCEPTION;
41 END IF;
42
43 IF dummy = 0 THEN
44 RETURN TRUE;
45 ELSE
46 RETURN FALSE;
47 END IF;
48
49 END Check_References;
50
51
52 --------------------------------------------------------------------------
53 FUNCTION Check_Unique(p_rowid in varchar2,
54 p_display_name IN VARCHAR2,
55 p_database_object_name IN VARCHAR2)
56 RETURN BOOLEAN
57 --------------------------------------------------------------------------
58 IS
59 l_condn_name VARCHAR2(255);
60
61 CURSOR CDN is
62 SELECT display_name from OE_DEF_CONDITIONS_VL
63 WHERE display_name = p_display_name
64 and database_object_name = p_database_object_name
65 and ((p_rowid is null) OR (row_id <> p_rowid));
66
67 BEGIN
68
69
70 OPEN CDN;
71 FETCH CDN INTO l_condn_name;
72 if (CDN%NOTFOUND) THEN
73 CLOSE CDN;
74 Raise NO_DATA_FOUND;
75 else
76 RETURN TRUE;
77 end if;
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN
81 RETURN FALSE;
82 END Check_Unique;
83
84 --------------------------------------------------------------------------
85 PROCEDURE Insert_Row(
86 --------------------------------------------------------------------------
87 p_rowid in out NOCOPY /* file.sql.39 change */ varchar2
88 ,p_condition_id in out NOCOPY /* file.sql.39 change */ number
89 ,p_display_name in varchar2
90 ,p_description in varchar2
91 ,p_database_object_name in varchar2
92 ,p_number_of_elements in number
93 ,p_system_flag in varchar2
94 ,p_created_by in number
95 ,p_creation_date in date
96 ,p_last_updated_by in number
97 ,p_last_update_date in date
98 ,p_last_update_login in number
99 ,p_attribute_category in varchar2
100 ,p_attribute1 in varchar2
101 ,p_attribute2 in varchar2
102 ,p_attribute3 in varchar2
103 ,p_attribute4 in varchar2
104 ,p_attribute5 in varchar2
105 ,p_attribute6 in varchar2
106 ,p_attribute7 in varchar2
107 ,p_attribute8 in varchar2
108 ,p_attribute9 in varchar2
109 ,p_attribute10 in varchar2
110 ,p_attribute11 in varchar2
111 ,p_attribute12 in varchar2
112 ,p_attribute13 in varchar2
113 ,p_attribute14 in varchar2
114 ,p_attribute15 in varchar2
115 ) IS
116
117 CURSOR C IS SELECT rowid from OE_DEF_CONDITIONS
118 WHERE condition_id = p_condition_id;
119
120 BEGIN
121
122 INSERT INTO OE_DEF_CONDITIONS(
123 condition_id,
124 database_object_name,
125 number_of_elements,
126 created_by,
127 creation_date,
128 last_updated_by,
129 system_flag,
130 last_update_date,last_update_login)
131 VALUES (
132 p_condition_id,
133 p_database_object_name,
134 p_number_of_elements,
135 p_created_by,
136 p_creation_date,
137 p_last_updated_by,
138 p_system_flag,
139 p_last_update_date,p_last_update_login);
140
141 INSERT INTO OE_DEF_CONDITIONS_TL(display_name,description,condition_id,
142 created_by,
143 creation_date,
144 last_updated_by,
145 last_update_date,
146 last_update_login,
147 language,source_lang)
148 SELECT
149 p_display_name,
150 p_description,
151 p_condition_id
152 ,p_created_by
153 ,p_creation_date
154 ,p_last_updated_by
155 ,p_last_update_date
156 ,p_last_update_login
157 ,l.language_code
158 ,USERENV('LANG')
159 FROM fnd_languages l
160 WHERE l.installed_flag in ('I', 'B')
161 AND not exists (
162 SELECT null
163 FROM oe_def_conditions_tl t
164 WHERE t.condition_id = p_condition_id
165 AND t.language = l.language_code);
166
167
168 OPEN C;
169 FETCH C INTO p_rowid;
170 if (C%NOTFOUND) THEN
171 CLOSE C;
172 Raise NO_DATA_FOUND;
173 end if;
174
175 CLOSE C;
176
177 EXCEPTION
178 WHEN OTHERS then
179 RAISE;
180 null;
181
182 END Insert_Row;
183
184
185 --------------------------------------------------------------------------
186 PROCEDURE Update_Row(
187 --------------------------------------------------------------------------
188 p_rowid in varchar2
189 ,p_condition_id in number
190 ,p_display_name in varchar2
191 ,p_description in varchar2
192 ,p_database_object_name in varchar2
193 ,p_number_of_elements in number
194 ,p_system_flag in varchar2
195 ,p_created_by in number
196 ,p_creation_date in date
197 ,p_last_updated_by in number
198 ,p_last_update_date in date
199 ,p_last_update_login in number
200 ,p_attribute_category in varchar2
201 ,p_attribute1 in varchar2
202 ,p_attribute2 in varchar2
203 ,p_attribute3 in varchar2
204 ,p_attribute4 in varchar2
205 ,p_attribute5 in varchar2
206 ,p_attribute6 in varchar2
207 ,p_attribute7 in varchar2
208 ,p_attribute8 in varchar2
209 ,p_attribute9 in varchar2
210 ,p_attribute10 in varchar2
211 ,p_attribute11 in varchar2
212 ,p_attribute12 in varchar2
213 ,p_attribute13 in varchar2
214 ,p_attribute14 in varchar2
215 ,p_attribute15 in varchar2
216 ) IS
217
218 BEGIN
219
220 UPDATE oe_def_conditions
221 SET
222 condition_id = p_condition_id
223 ,system_flag = p_system_flag
224 ,created_by = p_created_by
225 ,creation_date = p_creation_date
226 ,last_updated_by = p_last_updated_by
227 ,last_update_date = p_last_update_date
228 ,last_update_login = p_last_update_login
229 ,attribute_category = p_attribute_category
230 ,attribute1 = p_attribute1
231 ,attribute2 = p_attribute2
232 ,attribute3 = p_attribute3
233 ,attribute4 = p_attribute4
234 ,attribute5 = p_attribute5
235 ,attribute6 = p_attribute6
236 ,attribute7 = p_attribute7
237 ,attribute8 = p_attribute8
238 ,attribute9 = p_attribute9
239 ,attribute10 = p_attribute10
240 ,attribute11 = p_attribute11
241 ,attribute12 = p_attribute12
242 ,attribute13 = p_attribute13
243 ,attribute14 = p_attribute14
244 ,attribute15 = p_attribute15
245 WHERE condition_id = p_condition_id
246 AND database_object_name = p_database_object_name;
247 if (SQL%NOTFOUND) then
248 RAISE NO_DATA_FOUND;
249 end if;
250
251 UPDATE oe_def_conditions_tl
252 SET
253 source_lang = USERENV('LANG')
254 ,last_updated_by = p_last_updated_by
255 ,last_update_date = p_last_update_date
256 ,last_update_login = p_last_update_login
257 ,display_name = p_display_name
258 ,description = p_description
259 WHERE condition_id = p_condition_id
260 AND USERENV('LANG') in (language, source_lang);
261
262 IF (SQL%NOTFOUND) then
263 Raise NO_DATA_FOUND;
264 end if;
265
266 END Update_Row;
267
268
269 --------------------------------------------------------------------------
270 PROCEDURE Delete_Row(p_Rowid in VARCHAR2,
271 p_system_flag in VARCHAR2) IS
272 --------------------------------------------------------------------------
273 l_condition_id NUMBER;
274 BEGIN
275
276
277 SELECT condition_id INTO l_condition_id
278 FROM OE_DEF_CONDITIONS
279 WHERE rowid = p_rowid;
280
281
282 if (SQL%NOTFOUND) then
283 Raise NO_DATA_FOUND;
284 else
285
286 DELETE FROM OE_DEF_CONDITIONS
287 WHERE rowid = p_rowid;
288
289 DELETE FROM OE_DEF_CONDITIONS_TL
290 WHERE condition_id = l_condition_id;
291
292 DELETE FROM OE_DEF_CONDN_ELEMS
293 WHERE condition_id = l_condition_id;
294
295 DELETE FROM OE_DEF_ATTR_DEF_RULES
296 WHERE attr_def_condition_id = l_condition_id;
297
298 DELETE FROM OE_DEF_ATTR_CONDNS
299 WHERE condition_id = l_condition_id;
300
301 end if;
302
303 END Delete_Row;
304
305 --------------------------------------------------------------------------
306 PROCEDURE Lock_Row(
307 --------------------------------------------------------------------------
308 p_rowid in varchar2
309 ,p_condition_id in number
310 ,p_display_name in varchar2
311 ,p_description in varchar2
312 ,p_database_object_name in varchar2
313 ,p_number_of_elements in number
314 ,p_system_flag in varchar2
315 ,p_created_by in number
316 ,p_creation_date in date
317 ,p_last_updated_by in number
318 ,p_last_update_date in date
319 ,p_last_update_login in number
320 ,p_attribute_category in varchar2
321 ,p_attribute1 in varchar2
322 ,p_attribute2 in varchar2
323 ,p_attribute3 in varchar2
324 ,p_attribute4 in varchar2
325 ,p_attribute5 in varchar2
326 ,p_attribute6 in varchar2
327 ,p_attribute7 in varchar2
328 ,p_attribute8 in varchar2
329 ,p_attribute9 in varchar2
330 ,p_attribute10 in varchar2
331 ,p_attribute11 in varchar2
332 ,p_attribute12 in varchar2
333 ,p_attribute13 in varchar2
334 ,p_attribute14 in varchar2
335 ,p_attribute15 in varchar2
336 ) IS
337
338 CURSOR C IS
339 SELECT * FROM OE_DEF_CONDITIONS
340 WHERE rowid = p_Rowid
341 FOR UPDATE OF condition_id NOWAIT;
342
343 Recinfo C%ROWTYPE;
344
345 CURSOR C1 IS
346 SELECT *
347 FROM oe_def_conditions_tl t
348 WHERE condition_id = p_condition_id
349 AND language = userenv('LANG')
350 FOR UPDATE OF condition_id NOWAIT;
351
352 tlinfo C1%ROWTYPE;
353
354 BEGIN
355 OPEN C;
356 FETCH C into Recinfo;
357
358 IF (C%NOTFOUND) then
359 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
360 APP_EXCEPTION.Raise_Exception;
361 end if;
362 CLOSE C;
363
364
365 if (
366 (Recinfo.condition_id = p_condition_id)
367 AND (Recinfo.database_object_name = p_database_object_name)
368 AND (Recinfo.number_of_elements = p_number_of_elements)
369 AND (Recinfo.system_flag = p_system_flag)
370 AND ( (Recinfo.attribute_category = p_attribute_category)
371 OR ( ( Recinfo.attribute_category IS NULL)
372 AND (p_attribute_category is NULL)))
373 AND ( (Recinfo.attribute1 = p_attribute1)
374 OR ( ( Recinfo.attribute1 IS NULL)
375 AND (p_attribute1 is NULL)))
376 AND ( (Recinfo.attribute2 = p_attribute2)
377 OR ( ( Recinfo.attribute2 IS NULL)
378 AND (p_attribute2 is NULL)))
379 AND ( (Recinfo.attribute3 = p_attribute3)
380 OR ( ( Recinfo.attribute3 IS NULL)
381 AND (p_attribute3 is NULL)))
382 AND ( (Recinfo.attribute4 = p_attribute4)
383 OR ( ( Recinfo.attribute4 IS NULL)
384 AND (p_attribute4 is NULL)))
385 AND ( (Recinfo.attribute5 = p_attribute5)
386 OR ( ( Recinfo.attribute5 IS NULL)
387 AND (p_attribute5 is NULL)))
388 AND ( (Recinfo.attribute6 = p_attribute6)
389 OR ( ( Recinfo.attribute6 IS NULL)
390 AND (p_attribute6 is NULL)))
391 AND ( (Recinfo.attribute7 = p_attribute7)
392 OR ( ( Recinfo.attribute7 IS NULL)
393 AND (p_attribute7 is NULL)))
394 AND ( (Recinfo.attribute8 = p_attribute8)
395 OR ( ( Recinfo.attribute8 IS NULL)
396 AND (p_attribute8 is NULL)))
397 AND ( (Recinfo.attribute9 = p_attribute9)
398 OR ( ( Recinfo.attribute9 IS NULL)
399 AND (p_attribute9 is NULL)))
400 AND ( (Recinfo.attribute10 = p_attribute10)
401 OR ( ( Recinfo.attribute10 IS NULL)
402 AND (p_attribute10 is NULL)))
403 AND ( (Recinfo.attribute11 = p_attribute11)
404 OR ( ( Recinfo.attribute11 IS NULL)
405 AND (p_attribute11 is NULL)))
406 AND ( (Recinfo.attribute12 = p_attribute12)
407 OR ( ( Recinfo.attribute12 IS NULL)
408 AND (p_attribute12 is NULL)))
409 AND ( (Recinfo.attribute13 = p_attribute13)
410 OR ( ( Recinfo.attribute13 IS NULL)
411 AND (p_attribute13 is NULL)))
412 AND ( (Recinfo.attribute14 = p_attribute14)
413 OR ( ( Recinfo.attribute14 IS NULL)
414 AND (p_attribute14 is NULL)))
415 AND ( (Recinfo.attribute15 = p_attribute15)
416 OR ( ( Recinfo.attribute15 IS NULL)
417 AND (p_attribute15 is NULL)))
418 )
419 then return;
420 else
421 FND_MESSAGE.set_Name('FND','FORM_RECORD_CHANGED');
422 APP_EXCEPTION.Raise_Exception;
423 end if;
424
425 Open C1;
426 Fetch C1 into tlinfo;
427 if (C1%NOTFOUND) then
428 Close C1;
429 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
430 APP_EXCEPTION.Raise_Exception;
431 end if;
432 Close C1;
433
434 if (
435 (rtrim(tlinfo.display_name) = p_display_name)
436 AND (tlinfo.last_updated_by = p_last_updated_by)
437 AND (tlinfo.creation_date = p_creation_date)
438 AND (tlinfo.created_by = p_created_by)
439 AND (tlinfo.last_update_date = p_last_update_date)
440 AND (tlinfo.last_update_login = p_last_update_login)
441 AND ( (rtrim(tlinfo.description) = p_description)
442 OR ( (tlinfo.description IS NULL)
443 AND(p_description IS NULL)))
444 ) then
445 return;
446
447 else
448 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
449 APP_EXCEPTION.Raise_Exception;
450 end if;
451
452 END Lock_Row;
453
454 PROCEDURE TRANSLATE_ROW (
455 p_condition_id in VARCHAR2,
456 p_database_object_name in VARCHAR2,
457 p_owner in VARCHAR2,
458 p_display_name in VARCHAR2,
459 p_description in VARCHAR2)
460 IS
461 BEGIN
462
463 UPDATE oe_def_conditions_tl
464 SET
465 source_lang = USERENV('LANG')
466 ,last_updated_by = decode(p_OWNER, 'SEED', 1, 0)
467 ,last_update_date = sysdate
468 ,last_update_login = 0
469 ,display_name = p_display_name
470 ,description = p_description
471 WHERE condition_id = p_condition_id
472 AND USERENV('LANG') in (language, source_lang);
473
474 END TRANSLATE_ROW;
475
476 PROCEDURE LOAD_ROW (
477 p_condition_id in varchar2
478 ,p_owner in varchar2
479 ,p_display_name in varchar2
480 ,p_description in varchar2
481 ,p_database_object_name in varchar2
482 ,p_number_of_elements in varchar2
483 ,p_system_flag in varchar2
484 ,p_attribute_category in varchar2
485 ,p_attribute1 in varchar2
486 ,p_attribute2 in varchar2
487 ,p_attribute3 in varchar2
488 ,p_attribute4 in varchar2
489 ,p_attribute5 in varchar2
490 ,p_attribute6 in varchar2
491 ,p_attribute7 in varchar2
492 ,p_attribute8 in varchar2
493 ,p_attribute9 in varchar2
494 ,p_attribute10 in varchar2
495 ,p_attribute11 in varchar2
496 ,p_attribute12 in varchar2
497 ,p_attribute13 in varchar2
498 ,p_attribute14 in varchar2
499 ,p_attribute15 in varchar2)
500 IS
501 BEGIN
502
503 declare
504 user_id number := 0;
505 row_id varchar2(64);
506 l_condition_id number;
507
508 begin
509
510 if (P_OWNER = 'SEED') then
511 user_id := 1;
512 end if;
513
514 defcondn_pkg.UPDATE_ROW(
515 p_rowid => row_id
516 ,p_condition_id => p_condition_id
517 ,p_display_name => p_display_name
518 ,p_description => p_description
519 ,p_database_object_name => p_database_object_name
520 ,p_number_of_elements => p_number_of_elements
521 ,p_system_flag => p_system_flag
522 ,p_created_by => user_id
523 ,p_creation_date => sysdate
524 ,p_last_updated_by => user_id
525 ,p_last_update_date => sysdate
526 ,p_last_update_login => 0
527 ,p_attribute_category => p_attribute_category
528 ,p_attribute1 => p_attribute1
529 ,p_attribute2 => p_attribute2
530 ,p_attribute3 => p_attribute3
531 ,p_attribute4 => p_attribute4
532 ,p_attribute5 => p_attribute5
533 ,p_attribute6 => p_attribute6
534 ,p_attribute7 => p_attribute7
535 ,p_attribute8 => p_attribute8
536 ,p_attribute9 => p_attribute9
537 ,p_attribute10 => p_attribute10
538 ,p_attribute11 => p_attribute11
539 ,p_attribute12 => p_attribute12
540 ,p_attribute13 => p_attribute13
541 ,p_attribute14 => p_attribute14
542 ,p_attribute15 => p_attribute15
543 );
544
545 exception
546 when NO_DATA_FOUND then
547
548 l_condition_id := p_condition_id;
549
550 defcondn_pkg.INSERT_ROW(
551 p_rowid => row_id
552 ,p_condition_id => l_condition_id
553 ,p_display_name => p_display_name
554 ,p_description => p_description
555 ,p_database_object_name => p_database_object_name
556 ,p_number_of_elements => p_number_of_elements
557 ,p_system_flag => p_system_flag
558 ,p_created_by => user_id
559 ,p_creation_date => sysdate
560 ,p_last_updated_by => user_id
561 ,p_last_update_date => sysdate
562 ,p_last_update_login => 0
563 ,p_attribute_category => p_attribute_category
564 ,p_attribute1 => p_attribute1
565 ,p_attribute2 => p_attribute2
566 ,p_attribute3 => p_attribute3
567 ,p_attribute4 => p_attribute4
568 ,p_attribute5 => p_attribute5
569 ,p_attribute6 => p_attribute6
570 ,p_attribute7 => p_attribute7
571 ,p_attribute8 => p_attribute8
572 ,p_attribute9 => p_attribute9
573 ,p_attribute10 => p_attribute10
574 ,p_attribute11 => p_attribute11
575 ,p_attribute12 => p_attribute12
576 ,p_attribute13 => p_attribute13
577 ,p_attribute14 => p_attribute14
578 ,p_attribute15 => p_attribute15
579 );
580 end;
581
582 END LOAD_ROW;
583
584
585 PROCEDURE ADD_LANGUAGE
586 IS
587 BEGIN
588
589 DELETE FROM oe_def_conditions_tl t
590 WHERE NOT EXISTS
591 (SELECT null
592 FROM oe_def_conditions b
593 where b.condition_id = t.condition_id);
594
595 UPDATE oe_def_conditions_tl t
596 SET
597 (
598 display_name,
599 description
600 ) = (
601 SELECT
602 b.display_name,
603 b.description
604 FROM oe_def_conditions_tl b
605 WHERE b.condition_id = t.condition_id
606 AND b.language = t.source_lang
607 )
608 where
609 (
610 t.condition_id,
611 t.language
612 ) IN (
613 SELECT
614 subt.condition_id,
615 subt.language
616 FROM oe_def_conditions_tl subb, oe_def_conditions_tl subt
617 WHERE subb.condition_id = subt.condition_id
618 AND subb.language = subt.source_lang
619 AND(subb.display_name <> subt.display_name
620 OR subb.DESCRIPTION <> subt.description
621 OR (subb.description IS null AND subt.description IS NOT null)
622 OR (subb.description IS NOT null AND subt.description IS null)
623 )
624 );
625
626 INSERT INTO oe_def_conditions_tl
627 (
628 condition_id
629 ,language
630 ,source_lang
631 ,created_by
632 ,creation_date
633 ,last_updated_by
634 ,last_update_date
635 ,display_name
636 ,description
637 ,last_update_login
638 )
639 SELECT
640 b.condition_id
641 ,l.language_code
642 ,b.source_lang -- bug 2329327
643 ,b.created_by
644 ,b.creation_date
645 ,b.last_updated_by
646 ,b.last_update_date
647 ,b.display_name
648 ,b.description
649 ,b.last_update_login
650 FROM oe_def_conditions_tl b, fnd_languages l
651 WHERE l.installed_flag IN ('I', 'B')
652 AND b.language = USERENV('LANG')
653 AND NOT EXISTS
654 ( SELECT null
655 FROM oe_def_conditions_tl t
656 WHERE t.condition_id = b.condition_id
657 AND t.language = l.language_code);
658
659 END ADD_LANGUAGE;
660
661 END DEFCONDN_pkg;