[Home] [Help]
PACKAGE BODY: APPS.GR_PHRASE_CONCS_PKG
Source
1 PACKAGE BODY GR_PHRASE_CONCS_PKG AS
2 /*$Header: GRHIPHCB.pls 115.5 2002/10/28 19:58:32 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_phrase_code IN VARCHAR2,
7 p_phrase_concentration_low IN NUMBER,
8 p_phrase_concentration_high IN NUMBER,
9 p_phrase_type_code IN VARCHAR2,
10 p_phrase_hierarchy IN NUMBER,
11 p_attribute_category IN VARCHAR2,
12 p_attribute1 IN VARCHAR2,
13 p_attribute2 IN VARCHAR2,
14 p_attribute3 IN VARCHAR2,
15 p_attribute4 IN VARCHAR2,
16 p_attribute5 IN VARCHAR2,
17 p_attribute6 IN VARCHAR2,
18 p_attribute7 IN VARCHAR2,
19 p_attribute8 IN VARCHAR2,
20 p_attribute9 IN VARCHAR2,
21 p_attribute10 IN VARCHAR2,
22 p_attribute11 IN VARCHAR2,
23 p_attribute12 IN VARCHAR2,
24 p_attribute13 IN VARCHAR2,
25 p_attribute14 IN VARCHAR2,
26 p_attribute15 IN VARCHAR2,
27 p_attribute16 IN VARCHAR2,
28 p_attribute17 IN VARCHAR2,
29 p_attribute18 IN VARCHAR2,
30 p_attribute19 IN VARCHAR2,
31 p_attribute20 IN VARCHAR2,
32 p_attribute21 IN VARCHAR2,
33 p_attribute22 IN VARCHAR2,
34 p_attribute23 IN VARCHAR2,
35 p_attribute24 IN VARCHAR2,
36 p_attribute25 IN VARCHAR2,
37 p_attribute26 IN VARCHAR2,
38 p_attribute27 IN VARCHAR2,
39 p_attribute28 IN VARCHAR2,
40 p_attribute29 IN VARCHAR2,
41 p_attribute30 IN VARCHAR2,
42 p_created_by IN NUMBER,
43 p_creation_date IN DATE,
44 p_last_updated_by IN NUMBER,
45 p_last_update_date IN DATE,
46 p_last_update_login IN NUMBER,
47 x_rowid OUT NOCOPY VARCHAR2,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_oracle_error OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2)
51 IS
52 /* Alpha Variables */
53
54 L_RETURN_STATUS VARCHAR2(1) := 'S';
55 L_KEY_EXISTS VARCHAR2(1);
56 L_MSG_DATA VARCHAR2(2000);
57 L_ROWID VARCHAR2(18);
58
59 /* Number Variables */
60
61 L_ORACLE_ERROR NUMBER;
62
63 /* Exceptions */
64
65 FOREIGN_KEY_ERROR EXCEPTION;
66 LABEL_EXISTS_ERROR EXCEPTION;
67 ROW_MISSING_ERROR EXCEPTION;
68
69 /* Declare cursors */
70
71 BEGIN
72
73 /* Initialization Routine */
74
75 SAVEPOINT Insert_Row;
76 x_return_status := 'S';
77 x_oracle_error := 0;
78 x_msg_data := NULL;
79
80 /* Now call the check foreign key procedure */
81
82 Check_Foreign_Keys
83 (p_phrase_code,
84 p_phrase_concentration_low,
85 p_phrase_concentration_high,
86 p_phrase_type_code,
87 p_phrase_hierarchy,
88 p_attribute_category,
89 p_attribute1,
90 p_attribute2,
91 p_attribute3,
92 p_attribute4,
93 p_attribute5,
94 p_attribute6,
95 p_attribute7,
96 p_attribute8,
97 p_attribute9,
98 p_attribute10,
99 p_attribute11,
100 p_attribute12,
101 p_attribute13,
102 p_attribute14,
103 p_attribute15,
104 p_attribute16,
105 p_attribute17,
106 p_attribute18,
107 p_attribute19,
108 p_attribute20,
109 p_attribute21,
110 p_attribute22,
111 p_attribute23,
112 p_attribute24,
113 p_attribute25,
114 p_attribute26,
115 p_attribute27,
116 p_attribute28,
117 p_attribute29,
118 p_attribute30,
119 l_return_status,
120 l_oracle_error,
121 l_msg_data);
122 IF l_return_status <> 'S' THEN
123 RAISE Foreign_Key_Error;
124 END IF;
125
126 /* Now check the primary key doesn't already exist */
127
128 Check_Primary_Key
129 (p_phrase_code,
130 p_phrase_concentration_low,
131 p_phrase_concentration_high,
132 'F',
133 l_rowid,
134 l_key_exists);
135
136 IF FND_API.To_Boolean(l_key_exists) THEN
137 RAISE Label_Exists_Error;
138 END IF;
139
140 INSERT INTO gr_phrase_concs
141 (phrase_code,
142 phrase_concentration_low,
143 phrase_concentration_high,
144 phrase_type_code,
145 phrase_hierarchy,
146 attribute_category,
147 attribute1,
148 attribute2,
149 attribute3,
150 attribute4,
151 attribute5,
152 attribute6,
153 attribute7,
154 attribute8,
155 attribute9,
156 attribute10,
157 attribute11,
158 attribute12,
159 attribute13,
160 attribute14,
161 attribute15,
162 attribute16,
163 attribute17,
164 attribute18,
165 attribute19,
166 attribute20,
167 attribute21,
168 attribute22,
169 attribute23,
170 attribute24,
171 attribute25,
172 attribute26,
173 attribute27,
174 attribute28,
175 attribute29,
176 attribute30,
177 created_by,
178 creation_date,
179 last_updated_by,
180 last_update_date,
181 last_update_login)
182 VALUES
183 (p_phrase_code,
184 p_phrase_concentration_low,
185 p_phrase_concentration_high,
186 p_phrase_type_code,
187 p_phrase_hierarchy,
188 p_attribute_category,
189 p_attribute1,
190 p_attribute2,
191 p_attribute3,
192 p_attribute4,
193 p_attribute5,
194 p_attribute6,
195 p_attribute7,
196 p_attribute8,
197 p_attribute9,
198 p_attribute10,
199 p_attribute11,
200 p_attribute12,
201 p_attribute13,
202 p_attribute14,
203 p_attribute15,
204 p_attribute16,
205 p_attribute17,
206 p_attribute18,
207 p_attribute19,
208 p_attribute20,
209 p_attribute21,
210 p_attribute22,
211 p_attribute23,
212 p_attribute24,
213 p_attribute25,
214 p_attribute26,
215 p_attribute27,
216 p_attribute28,
217 p_attribute29,
218 p_attribute30,
219 p_created_by,
220 p_creation_date,
221 p_last_updated_by,
222 p_last_update_date,
223 p_last_update_login);
224
225 /* Now get the row id of the inserted record */
226
227 Check_Primary_Key
228 (p_phrase_code,
229 p_phrase_concentration_low,
230 p_phrase_concentration_high,
231 'F',
232 l_rowid,
233 l_key_exists);
234
235 IF FND_API.To_Boolean(l_key_exists) THEN
236 x_rowid := l_rowid;
237 ELSE
238 RAISE Row_Missing_Error;
239 END IF;
240
241 /* Check the commit flag and if set, then commit the work. */
242
243 IF FND_API.To_Boolean(p_commit) THEN
244 COMMIT WORK;
245 END IF;
246
247 EXCEPTION
248
249 WHEN Foreign_Key_Error THEN
250 ROLLBACK TO SAVEPOINT Insert_Row;
251 x_return_status := l_return_status;
252 x_oracle_error := l_oracle_error;
253 FND_MESSAGE.SET_NAME('GR',
254 'GR_FOREIGN_KEY_ERROR');
255 FND_MESSAGE.SET_TOKEN('TEXT',
256 l_msg_data,
257 FALSE);
258 IF FND_API.To_Boolean(p_called_by_form) THEN
259 APP_EXCEPTION.Raise_Exception;
260 ELSE
261 x_msg_data := FND_MESSAGE.Get;
262 END IF;
263
264 WHEN Label_Exists_Error THEN
265 ROLLBACK TO SAVEPOINT Insert_Row;
266 x_return_status := 'E';
267 x_oracle_error := APP_EXCEPTION.Get_Code;
268 FND_MESSAGE.SET_NAME('GR',
269 'GR_RECORD_EXISTS');
270 FND_MESSAGE.SET_TOKEN('CODE',
271 p_phrase_code,
272 FALSE);
273 IF FND_API.To_Boolean(p_called_by_form) THEN
274 APP_EXCEPTION.Raise_Exception;
275 ELSE
276 x_msg_data := FND_MESSAGE.Get;
277 END IF;
278
279 WHEN Row_Missing_Error THEN
280 ROLLBACK TO SAVEPOINT Insert_Row;
281 x_return_status := 'E';
282 x_oracle_error := APP_EXCEPTION.Get_Code;
283 FND_MESSAGE.SET_NAME('GR',
284 'GR_NO_RECORD_INSERTED');
285 FND_MESSAGE.SET_TOKEN('CODE',
286 p_phrase_code,
287 FALSE);
288 IF FND_API.To_Boolean(p_called_by_form) THEN
289 APP_EXCEPTION.Raise_Exception;
290 ELSE
291 x_msg_data := FND_MESSAGE.Get;
292 END IF;
293
294 WHEN OTHERS THEN
295 ROLLBACK TO SAVEPOINT Insert_Row;
296 x_return_status := 'U';
297 x_oracle_error := APP_EXCEPTION.Get_Code;
298 l_msg_data := APP_EXCEPTION.Get_Text;
299 FND_MESSAGE.SET_NAME('GR',
300 'GR_UNEXPECTED_ERROR');
301 FND_MESSAGE.SET_TOKEN('TEXT',
302 l_msg_data,
303 FALSE);
304 IF FND_API.To_Boolean(p_called_by_form) THEN
305 APP_EXCEPTION.Raise_Exception;
306 ELSE
307 x_msg_data := FND_MESSAGE.Get;
308 END IF;
309
310 END Insert_Row;
311
312 PROCEDURE Update_Row
313 (p_commit IN VARCHAR2,
314 p_called_by_form IN VARCHAR2,
315 p_rowid IN VARCHAR2,
316 p_phrase_code IN VARCHAR2,
317 p_phrase_concentration_low IN NUMBER,
318 p_phrase_concentration_high IN NUMBER,
319 p_phrase_type_code IN VARCHAR2,
320 p_phrase_hierarchy IN NUMBER,
321 p_attribute_category IN VARCHAR2,
322 p_attribute1 IN VARCHAR2,
323 p_attribute2 IN VARCHAR2,
324 p_attribute3 IN VARCHAR2,
325 p_attribute4 IN VARCHAR2,
326 p_attribute5 IN VARCHAR2,
327 p_attribute6 IN VARCHAR2,
328 p_attribute7 IN VARCHAR2,
329 p_attribute8 IN VARCHAR2,
330 p_attribute9 IN VARCHAR2,
331 p_attribute10 IN VARCHAR2,
332 p_attribute11 IN VARCHAR2,
333 p_attribute12 IN VARCHAR2,
334 p_attribute13 IN VARCHAR2,
335 p_attribute14 IN VARCHAR2,
336 p_attribute15 IN VARCHAR2,
337 p_attribute16 IN VARCHAR2,
338 p_attribute17 IN VARCHAR2,
339 p_attribute18 IN VARCHAR2,
340 p_attribute19 IN VARCHAR2,
341 p_attribute20 IN VARCHAR2,
342 p_attribute21 IN VARCHAR2,
343 p_attribute22 IN VARCHAR2,
344 p_attribute23 IN VARCHAR2,
345 p_attribute24 IN VARCHAR2,
346 p_attribute25 IN VARCHAR2,
347 p_attribute26 IN VARCHAR2,
348 p_attribute27 IN VARCHAR2,
349 p_attribute28 IN VARCHAR2,
350 p_attribute29 IN VARCHAR2,
351 p_attribute30 IN VARCHAR2,
352 p_created_by IN NUMBER,
353 p_creation_date IN DATE,
354 p_last_updated_by IN NUMBER,
355 p_last_update_date IN DATE,
356 p_last_update_login IN NUMBER,
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_oracle_error OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2)
360 IS
361
362 /* Alpha Variables */
363
364 L_RETURN_STATUS VARCHAR2(1) := 'S';
365 L_MSG_DATA VARCHAR2(2000);
366
367 /* Number Variables */
368
369 L_ORACLE_ERROR NUMBER;
370
371 /* Exceptions */
372
373 FOREIGN_KEY_ERROR EXCEPTION;
374 ROW_MISSING_ERROR EXCEPTION;
375
376 BEGIN
377
378 /* Initialization Routine */
379
380 SAVEPOINT Update_Row;
381 x_return_status := 'S';
382 x_oracle_error := 0;
383 x_msg_data := NULL;
384
385 /* Now call the check foreign key procedure */
386
387 Check_Foreign_Keys
388 (p_phrase_code,
389 p_phrase_concentration_low,
390 p_phrase_concentration_high,
391 p_phrase_type_code,
392 p_phrase_hierarchy,
393 p_attribute_category,
394 p_attribute1,
395 p_attribute2,
396 p_attribute3,
397 p_attribute4,
398 p_attribute5,
399 p_attribute6,
400 p_attribute7,
401 p_attribute8,
402 p_attribute9,
403 p_attribute10,
404 p_attribute11,
405 p_attribute12,
406 p_attribute13,
407 p_attribute14,
408 p_attribute15,
409 p_attribute16,
410 p_attribute17,
411 p_attribute18,
412 p_attribute19,
413 p_attribute20,
414 p_attribute21,
415 p_attribute22,
416 p_attribute23,
417 p_attribute24,
418 p_attribute25,
419 p_attribute26,
420 p_attribute27,
421 p_attribute28,
422 p_attribute29,
423 p_attribute30,
424 l_return_status,
425 l_oracle_error,
426 l_msg_data);
427
428 IF l_return_status <> 'S' THEN
429 RAISE Foreign_Key_Error;
430 ELSE
431 UPDATE gr_phrase_concs
432 SET phrase_code = p_phrase_code,
433 phrase_concentration_low = p_phrase_concentration_low,
434 phrase_concentration_high = p_phrase_concentration_high,
435 phrase_type_code = p_phrase_type_code,
436 phrase_hierarchy = p_phrase_hierarchy,
437 attribute_category = p_attribute_category,
438 attribute1 = p_attribute1,
439 attribute2 = p_attribute2,
440 attribute3 = p_attribute3,
441 attribute4 = p_attribute4,
442 attribute5 = p_attribute5,
443 attribute6 = p_attribute6,
444 attribute7 = p_attribute7,
448 attribute11 = p_attribute11,
445 attribute8 = p_attribute8,
446 attribute9 = p_attribute9,
447 attribute10 = p_attribute10,
449 attribute12 = p_attribute12,
450 attribute13 = p_attribute13,
451 attribute14 = p_attribute14,
452 attribute15 = p_attribute15,
453 attribute16 = p_attribute16,
454 attribute17 = p_attribute17,
455 attribute18 = p_attribute18,
456 attribute19 = p_attribute19,
457 attribute20 = p_attribute20,
458 attribute21 = p_attribute11,
459 attribute22 = p_attribute22,
460 attribute23 = p_attribute23,
461 attribute24 = p_attribute24,
462 attribute25 = p_attribute25,
463 attribute26 = p_attribute26,
464 attribute27 = p_attribute27,
465 attribute28 = p_attribute28,
466 attribute29 = p_attribute29,
467 attribute30 = p_attribute30,
468 created_by = p_created_by,
469 creation_date = p_creation_date,
470 last_updated_by = p_last_updated_by,
471 last_update_date = p_last_update_date,
472 last_update_login = p_last_update_login
473 WHERE rowid = p_rowid;
474 IF SQL%NOTFOUND THEN
475 RAISE Row_Missing_Error;
476 END IF;
477 END IF;
478
479 /* Check the commit flag and if set, then commit the work. */
480
481 IF FND_API.To_Boolean(p_commit) THEN
482 COMMIT WORK;
483 END IF;
484
485 EXCEPTION
486
487 WHEN Foreign_Key_Error THEN
488 ROLLBACK TO SAVEPOINT Update_Row;
489 x_return_status := l_return_status;
490 x_oracle_error := l_oracle_error;
491 FND_MESSAGE.SET_NAME('GR',
492 'GR_FOREIGN_KEY_ERROR');
493 FND_MESSAGE.SET_TOKEN('TEXT',
494 l_msg_data,
495 FALSE);
496 IF FND_API.To_Boolean(p_called_by_form) THEN
497 APP_EXCEPTION.Raise_Exception;
498 ELSE
499 x_msg_data := FND_MESSAGE.Get;
500 END IF;
501
502 WHEN Row_Missing_Error THEN
503 ROLLBACK TO SAVEPOINT Update_Row;
504 x_return_status := 'E';
505 x_oracle_error := APP_EXCEPTION.Get_Code;
506 FND_MESSAGE.SET_NAME('GR',
507 'GR_NO_RECORD_INSERTED');
508 FND_MESSAGE.SET_TOKEN('CODE',
509 p_phrase_code,
510 FALSE);
511 IF FND_API.To_Boolean(p_called_by_form) THEN
512 APP_EXCEPTION.Raise_Exception;
513 ELSE
514 x_msg_data := FND_MESSAGE.Get;
515 END IF;
516
517 WHEN OTHERS THEN
518 ROLLBACK TO SAVEPOINT Update_Row;
519 x_return_status := 'U';
520 x_oracle_error := APP_EXCEPTION.Get_Code;
521 l_msg_data := APP_EXCEPTION.Get_Text;
522 FND_MESSAGE.SET_NAME('GR',
523 'GR_UNEXPECTED_ERROR');
524 FND_MESSAGE.SET_TOKEN('TEXT',
525 l_msg_data,
526 FALSE);
527 IF FND_API.To_Boolean(p_called_by_form) THEN
528 APP_EXCEPTION.Raise_Exception;
529 ELSE
530 x_msg_data := FND_MESSAGE.Get;
531 END IF;
532
533 END Update_Row;
534
535 PROCEDURE Lock_Row
536 (p_commit IN VARCHAR2,
537 p_called_by_form IN VARCHAR2,
538 p_rowid IN VARCHAR2,
539 p_phrase_code IN VARCHAR2,
540 p_phrase_concentration_low IN NUMBER,
541 p_phrase_concentration_high IN NUMBER,
542 p_phrase_type_code IN VARCHAR2,
543 p_phrase_hierarchy IN NUMBER,
547 p_attribute3 IN VARCHAR2,
544 p_attribute_category IN VARCHAR2,
545 p_attribute1 IN VARCHAR2,
546 p_attribute2 IN VARCHAR2,
548 p_attribute4 IN VARCHAR2,
549 p_attribute5 IN VARCHAR2,
550 p_attribute6 IN VARCHAR2,
551 p_attribute7 IN VARCHAR2,
552 p_attribute8 IN VARCHAR2,
553 p_attribute9 IN VARCHAR2,
554 p_attribute10 IN VARCHAR2,
555 p_attribute11 IN VARCHAR2,
556 p_attribute12 IN VARCHAR2,
557 p_attribute13 IN VARCHAR2,
558 p_attribute14 IN VARCHAR2,
559 p_attribute15 IN VARCHAR2,
560 p_attribute16 IN VARCHAR2,
561 p_attribute17 IN VARCHAR2,
562 p_attribute18 IN VARCHAR2,
563 p_attribute19 IN VARCHAR2,
564 p_attribute20 IN VARCHAR2,
565 p_attribute21 IN VARCHAR2,
566 p_attribute22 IN VARCHAR2,
567 p_attribute23 IN VARCHAR2,
568 p_attribute24 IN VARCHAR2,
569 p_attribute25 IN VARCHAR2,
570 p_attribute26 IN VARCHAR2,
571 p_attribute27 IN VARCHAR2,
572 p_attribute28 IN VARCHAR2,
573 p_attribute29 IN VARCHAR2,
574 p_attribute30 IN VARCHAR2,
575 p_created_by IN NUMBER,
576 p_creation_date IN DATE,
577 p_last_updated_by IN NUMBER,
578 p_last_update_date IN DATE,
579 p_last_update_login IN NUMBER,
580 x_return_status OUT NOCOPY VARCHAR2,
581 x_oracle_error OUT NOCOPY NUMBER,
582 x_msg_data OUT NOCOPY VARCHAR2)
583 IS
584
585 /* Alpha Variables */
586
587 L_RETURN_STATUS VARCHAR2(1) := 'S';
588 L_MSG_DATA VARCHAR2(2000);
589
590 /* Number Variables */
591
592 L_ORACLE_ERROR NUMBER;
593
594 /* Exceptions */
595
596 NO_DATA_FOUND_ERROR EXCEPTION;
597 RECORD_CHANGED_ERROR EXCEPTION;
598
599 /* Define the cursors */
600
601 CURSOR c_lock_concs
602 IS
603 SELECT last_update_date
604 FROM gr_phrase_concs
605 WHERE rowid = p_rowid
606 FOR UPDATE NOWAIT;
607 LockConcRcd c_lock_concs%ROWTYPE;
608
609 BEGIN
610
611 /* Initialization Routine */
612
613 SAVEPOINT Lock_Row;
614 x_return_status := 'S';
615 x_oracle_error := 0;
616 x_msg_data := NULL;
617
618 /* Now lock the record */
619
620 OPEN c_lock_concs;
621 FETCH c_lock_concs INTO LockConcRcd;
622 IF c_lock_concs%NOTFOUND THEN
623 CLOSE c_lock_concs;
624 RAISE No_Data_Found_Error;
625 END IF;
626 CLOSE c_lock_concs;
627
628 IF LockConcRcd.last_update_date <> p_last_update_date THEN
629 RAISE RECORD_CHANGED_ERROR;
630 END IF;
631
632 IF FND_API.To_Boolean(p_commit) THEN
633 COMMIT WORK;
634 END IF;
635
636 EXCEPTION
637
638 WHEN No_Data_Found_Error THEN
639 ROLLBACK TO SAVEPOINT Lock_Row;
640 x_return_status := 'E';
641 FND_MESSAGE.SET_NAME('GR',
642 'GR_RECORD_NOT_FOUND');
643 FND_MESSAGE.SET_TOKEN('CODE',
644 p_phrase_code,
645 FALSE);
646 IF FND_API.To_Boolean(p_called_by_form) THEN
647 APP_EXCEPTION.Raise_Exception;
648 ELSE
649 x_msg_data := FND_MESSAGE.Get;
650 END IF;
651
652 WHEN RECORD_CHANGED_ERROR THEN
653 ROLLBACK TO SAVEPOINT Lock_Row;
654 X_return_status := 'E';
658 APP_EXCEPTION.Raise_Exception;
655 FND_MESSAGE.SET_NAME('FND',
656 'FORM_RECORD_CHANGED');
657 IF FND_API.To_Boolean(p_called_by_form) THEN
659 ELSE
660 x_msg_data := FND_MESSAGE.Get;
661 END IF;
662 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
663 ROLLBACK TO SAVEPOINT Lock_Row;
664 x_return_status := 'L';
665 x_oracle_error := APP_EXCEPTION.Get_Code;
666 IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
667 FND_MESSAGE.SET_NAME('GR',
668 'GR_ROW_IS_LOCKED');
669 x_msg_data := FND_MESSAGE.Get;
670 END IF;
671
672 WHEN OTHERS THEN
673 ROLLBACK TO SAVEPOINT Lock_Row;
674 x_return_status := 'U';
675 x_oracle_error := APP_EXCEPTION.Get_Code;
676 l_msg_data := APP_EXCEPTION.Get_Text;
677 FND_MESSAGE.SET_NAME('GR',
678 'GR_UNEXPECTED_ERROR');
679 FND_MESSAGE.SET_TOKEN('TEXT',
680 l_msg_data,
681 FALSE);
682 IF FND_API.To_Boolean(p_called_by_form) THEN
683 APP_EXCEPTION.Raise_Exception;
684 ELSE
685 x_msg_data := FND_MESSAGE.Get;
686 END IF;
687
688 END Lock_Row;
689
690 PROCEDURE Delete_Row
691 (p_commit IN VARCHAR2,
692 p_called_by_form IN VARCHAR2,
693 p_rowid IN VARCHAR2,
694 p_phrase_code IN VARCHAR2,
695 p_phrase_concentration_low IN NUMBER,
696 p_phrase_concentration_high IN NUMBER,
697 p_phrase_type_code IN VARCHAR2,
698 p_phrase_hierarchy IN NUMBER,
699 p_attribute_category IN VARCHAR2,
700 p_attribute1 IN VARCHAR2,
701 p_attribute2 IN VARCHAR2,
702 p_attribute3 IN VARCHAR2,
703 p_attribute4 IN VARCHAR2,
704 p_attribute5 IN VARCHAR2,
705 p_attribute6 IN VARCHAR2,
706 p_attribute7 IN VARCHAR2,
707 p_attribute8 IN VARCHAR2,
708 p_attribute9 IN VARCHAR2,
709 p_attribute10 IN VARCHAR2,
710 p_attribute11 IN VARCHAR2,
711 p_attribute12 IN VARCHAR2,
712 p_attribute13 IN VARCHAR2,
713 p_attribute14 IN VARCHAR2,
714 p_attribute15 IN VARCHAR2,
715 p_attribute16 IN VARCHAR2,
716 p_attribute17 IN VARCHAR2,
717 p_attribute18 IN VARCHAR2,
718 p_attribute19 IN VARCHAR2,
719 p_attribute20 IN VARCHAR2,
720 p_attribute21 IN VARCHAR2,
721 p_attribute22 IN VARCHAR2,
722 p_attribute23 IN VARCHAR2,
723 p_attribute24 IN VARCHAR2,
724 p_attribute25 IN VARCHAR2,
725 p_attribute26 IN VARCHAR2,
726 p_attribute27 IN VARCHAR2,
727 p_attribute28 IN VARCHAR2,
728 p_attribute29 IN VARCHAR2,
729 p_attribute30 IN VARCHAR2,
730 p_created_by IN NUMBER,
731 p_creation_date IN DATE,
732 p_last_updated_by IN NUMBER,
733 p_last_update_date IN DATE,
734 p_last_update_login IN NUMBER,
735 x_return_status OUT NOCOPY VARCHAR2,
736 x_oracle_error OUT NOCOPY NUMBER,
737 x_msg_data OUT NOCOPY VARCHAR2)
738 IS
739
740 /* Alpha Variables */
741
742 L_RETURN_STATUS VARCHAR2(1) := 'S';
743 L_MSG_DATA VARCHAR2(2000);
744 L_CALLED_BY_FORM VARCHAR2(1);
745
746 /* Number Variables */
747
748 L_ORACLE_ERROR NUMBER;
749
750 /* Exceptions */
751
755
752 CHECK_INTEGRITY_ERROR EXCEPTION;
753 ROW_MISSING_ERROR EXCEPTION;
754 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
756 BEGIN
757
758 /* Initialization Routine */
759
760 SAVEPOINT Delete_Row;
761 x_return_status := 'S';
762 l_called_by_form := 'F';
763 x_oracle_error := 0;
764 x_msg_data := NULL;
765
766 /* Now call the check integrity procedure */
767
768 Check_Integrity
769 (l_called_by_form,
770 p_phrase_code,
771 p_phrase_concentration_low,
772 p_phrase_concentration_high,
773 p_phrase_type_code,
774 p_phrase_hierarchy,
775 p_attribute_category,
776 p_attribute1,
777 p_attribute2,
778 p_attribute3,
779 p_attribute4,
780 p_attribute5,
781 p_attribute6,
782 p_attribute7,
783 p_attribute8,
784 p_attribute9,
785 p_attribute10,
786 p_attribute11,
787 p_attribute12,
788 p_attribute13,
789 p_attribute14,
790 p_attribute15,
791 p_attribute16,
792 p_attribute17,
793 p_attribute18,
794 p_attribute19,
795 p_attribute20,
796 p_attribute21,
797 p_attribute22,
798 p_attribute23,
799 p_attribute24,
800 p_attribute25,
801 p_attribute26,
802 p_attribute27,
803 p_attribute28,
804 p_attribute29,
805 p_attribute30,
806 l_return_status,
807 l_oracle_error,
808 l_msg_data);
809
810 IF l_return_status <> 'S' THEN
811 RAISE Check_Integrity_Error;
812 END IF;
813
814 DELETE FROM gr_phrase_concs
815 WHERE rowid = p_rowid;
816
817 /* Check the commit flag and if set, then commit the work. */
818
819 IF FND_API.TO_Boolean(p_commit) THEN
820 COMMIT WORK;
821 END IF;
822
823 EXCEPTION
824
825 WHEN Check_Integrity_Error THEN
826 ROLLBACK TO SAVEPOINT Delete_Row;
827 x_return_status := l_return_status;
828 x_oracle_error := l_oracle_error;
829 IF FND_API.To_Boolean(p_called_by_form) THEN
830 APP_EXCEPTION.Raise_Exception;
831 ELSE
832 x_msg_data := FND_MESSAGE.Get;
833 END IF;
834
835 WHEN Row_Missing_Error THEN
836 ROLLBACK TO SAVEPOINT Delete_Row;
837 x_return_status := 'E';
838 x_oracle_error := APP_EXCEPTION.Get_Code;
839 FND_MESSAGE.SET_NAME('GR',
840 'GR_RECORD_NOT_FOUND');
841 FND_MESSAGE.SET_TOKEN('CODE',
842 p_phrase_code,
843 FALSE);
844 IF FND_API.To_Boolean(p_called_by_form) THEN
845 APP_EXCEPTION.Raise_Exception;
846 ELSE
847 x_msg_data := FND_MESSAGE.Get;
848 END IF;
849
850 WHEN OTHERS THEN
851 ROLLBACK TO SAVEPOINT Delete_Row;
852 x_return_status := 'U';
853 x_oracle_error := APP_EXCEPTION.Get_Code;
854 l_msg_data := APP_EXCEPTION.Get_Text;
855 l_msg_data := APP_EXCEPTION.Get_Text;
856 FND_MESSAGE.SET_NAME('GR',
857 'GR_UNEXPECTED_ERROR');
858 FND_MESSAGE.SET_TOKEN('TEXT',
859 l_msg_data,
860 FALSE);
861 IF FND_API.To_Boolean(p_called_by_form) THEN
862 APP_EXCEPTION.Raise_Exception;
863 ELSE
864 x_msg_data := FND_MESSAGE.Get;
865 END IF;
866
867 END Delete_Row;
868
869 PROCEDURE Delete_Rows
870 (p_commit IN VARCHAR2,
874 x_oracle_error OUT NOCOPY NUMBER,
871 p_called_by_form IN VARCHAR2,
872 p_phrase_code IN VARCHAR2,
873 x_return_status OUT NOCOPY VARCHAR2,
875 x_msg_data OUT NOCOPY VARCHAR2)
876 IS
877
878 /* Alpha Variables */
879
880 L_RETURN_STATUS VARCHAR2(1) := 'S';
881 L_MSG_DATA VARCHAR2(2000);
882 L_MSG_TOKEN VARCHAR2(100);
883
884 /* Number Variables */
885
886 L_ORACLE_ERROR NUMBER;
887
888 /* Define the cursors */
889
890 BEGIN
891
892 /* Initialization Routine */
893
894 SAVEPOINT Delete_Rows;
895 x_return_status := 'S';
896 x_oracle_error := 0;
897 x_msg_data := NULL;
898 l_msg_token := p_phrase_code;
899
900 DELETE FROM gr_phrase_concs
901 WHERE phrase_code = p_phrase_code;
902
903 IF FND_API.To_Boolean(p_commit) THEN
904 COMMIT WORK;
905 END IF;
906
907 EXCEPTION
908
909 WHEN OTHERS THEN
910 ROLLBACK TO SAVEPOINT Delete_Rows;
911 x_return_status := 'U';
912 x_oracle_error := APP_EXCEPTION.Get_Code;
913 l_msg_data := APP_EXCEPTION.Get_Text;
914 FND_MESSAGE.SET_NAME('GR',
915 'GR_UNEXPECTED_ERROR');
916 FND_MESSAGE.SET_TOKEN('TEXT',
917 l_msg_token,
918 FALSE);
919 IF FND_API.To_Boolean(p_called_by_form) THEN
920 APP_EXCEPTION.Raise_Exception;
921 ELSE
922 x_msg_data := FND_MESSAGE.Get;
923 END IF;
924
925 END Delete_Rows;
926
927 PROCEDURE Check_Foreign_Keys
928 (p_phrase_code IN VARCHAR2,
929 p_phrase_concentration_low IN NUMBER,
930 p_phrase_concentration_high IN NUMBER,
931 p_phrase_type_code IN VARCHAR2,
932 p_phrase_hierarchy IN NUMBER,
933 p_attribute_category IN VARCHAR2,
934 p_attribute1 IN VARCHAR2,
935 p_attribute2 IN VARCHAR2,
936 p_attribute3 IN VARCHAR2,
937 p_attribute4 IN VARCHAR2,
938 p_attribute5 IN VARCHAR2,
939 p_attribute6 IN VARCHAR2,
940 p_attribute7 IN VARCHAR2,
941 p_attribute8 IN VARCHAR2,
942 p_attribute9 IN VARCHAR2,
943 p_attribute10 IN VARCHAR2,
944 p_attribute11 IN VARCHAR2,
945 p_attribute12 IN VARCHAR2,
946 p_attribute13 IN VARCHAR2,
947 p_attribute14 IN VARCHAR2,
948 p_attribute15 IN VARCHAR2,
949 p_attribute16 IN VARCHAR2,
950 p_attribute17 IN VARCHAR2,
951 p_attribute18 IN VARCHAR2,
952 p_attribute19 IN VARCHAR2,
953 p_attribute20 IN VARCHAR2,
954 p_attribute21 IN VARCHAR2,
955 p_attribute22 IN VARCHAR2,
956 p_attribute23 IN VARCHAR2,
957 p_attribute24 IN VARCHAR2,
958 p_attribute25 IN VARCHAR2,
959 p_attribute26 IN VARCHAR2,
960 p_attribute27 IN VARCHAR2,
961 p_attribute28 IN VARCHAR2,
962 p_attribute29 IN VARCHAR2,
963 p_attribute30 IN VARCHAR2,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_oracle_error OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2)
967 IS
968
969 /* Alpha Variables */
970
971 L_RETURN_STATUS VARCHAR2(1) := 'S';
972 L_MSG_DATA VARCHAR2(2000);
973 L_ROWID VARCHAR2(18);
974 L_KEY_EXISTS VARCHAR2(1);
975
976 /* Number Variables */
977
978 L_ORACLE_ERROR NUMBER;
979
980 /* Define the cursors */
981
982 BEGIN
983
984 /* Initialization Routine */
985
986 SAVEPOINT Check_Foreign_Keys;
987 x_return_status := 'S';
988 x_oracle_error := 0;
989 x_msg_data := NULL;
990
991 /* Check the phrase type code */
992
993 IF p_phrase_type_code IS NOT NULL THEN
994 l_key_exists := 'T';
995 GR_PHRASE_TYPES_PKG.check_primary_key
996 (p_phrase_type_code,
997 'F',
998 l_rowid,
999 l_key_exists);
1000 END IF;
1001
1002 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1003 x_return_status := 'E';
1004 FND_MESSAGE.SET_NAME('GR',
1005 'GR_RECORD_NOT_FOUND');
1006 FND_MESSAGE.SET_TOKEN('CODE',
1007 p_phrase_type_code,
1008 FALSE);
1009 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1010 END IF;
1011
1012 IF x_return_status <> 'S' THEN
1013 x_msg_data := l_msg_data;
1014 END IF;
1015
1016 EXCEPTION
1017
1018 WHEN OTHERS THEN
1019 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1020 x_return_status := 'U';
1021 x_oracle_error := APP_EXCEPTION.Get_Code;
1022 l_msg_data := APP_EXCEPTION.Get_Text;
1023 FND_MESSAGE.SET_NAME('GR',
1024 'GR_UNEXPECTED_ERROR');
1025 FND_MESSAGE.SET_TOKEN('TEXT',
1026 l_msg_data,
1027 FALSE);
1028 x_msg_data := FND_MESSAGE.Get;
1029
1030 END Check_Foreign_Keys;
1031
1032 PROCEDURE Check_Integrity
1033 (p_called_by_form IN VARCHAR2,
1034 p_phrase_code IN VARCHAR2,
1035 p_phrase_concentration_low IN NUMBER,
1036 p_phrase_concentration_high IN NUMBER,
1037 p_phrase_type_code IN VARCHAR2,
1038 p_phrase_hierarchy IN NUMBER,
1039 p_attribute_category IN VARCHAR2,
1040 p_attribute1 IN VARCHAR2,
1041 p_attribute2 IN VARCHAR2,
1042 p_attribute3 IN VARCHAR2,
1043 p_attribute4 IN VARCHAR2,
1044 p_attribute5 IN VARCHAR2,
1045 p_attribute6 IN VARCHAR2,
1046 p_attribute7 IN VARCHAR2,
1047 p_attribute8 IN VARCHAR2,
1048 p_attribute9 IN VARCHAR2,
1049 p_attribute10 IN VARCHAR2,
1050 p_attribute11 IN VARCHAR2,
1051 p_attribute12 IN VARCHAR2,
1052 p_attribute13 IN VARCHAR2,
1053 p_attribute14 IN VARCHAR2,
1054 p_attribute15 IN VARCHAR2,
1055 p_attribute16 IN VARCHAR2,
1056 p_attribute17 IN VARCHAR2,
1057 p_attribute18 IN VARCHAR2,
1058 p_attribute19 IN VARCHAR2,
1059 p_attribute20 IN VARCHAR2,
1060 p_attribute21 IN VARCHAR2,
1061 p_attribute22 IN VARCHAR2,
1062 p_attribute23 IN VARCHAR2,
1063 p_attribute24 IN VARCHAR2,
1064 p_attribute25 IN VARCHAR2,
1065 p_attribute26 IN VARCHAR2,
1066 p_attribute27 IN VARCHAR2,
1067 p_attribute28 IN VARCHAR2,
1068 p_attribute29 IN VARCHAR2,
1069 p_attribute30 IN VARCHAR2,
1070 x_return_status OUT NOCOPY VARCHAR2,
1071 x_oracle_error OUT NOCOPY NUMBER,
1072 x_msg_data OUT NOCOPY VARCHAR2)
1073 IS
1074
1075 /* Alpha Variables */
1076
1077 L_RETURN_STATUS VARCHAR2(1) := 'S';
1078 L_MSG_DATA VARCHAR2(2000);
1079 L_CODE_BLOCK VARCHAR2(100);
1080
1081 /* Number Variables */
1082
1083 L_ORACLE_ERROR NUMBER;
1084 L_RECORD_COUNT NUMBER;
1085
1086 /* Define the Cursors */
1087
1088 BEGIN
1089
1090 /* Initialization Routine */
1091
1092 SAVEPOINT Check_Integrity;
1093 x_return_status := 'S';
1094 x_oracle_error := 0;
1095 x_msg_data := NULL;
1096
1097 /* No integrity checking is required */
1098
1099 /* Now sort out the error messaging */
1100
1101 IF l_return_status <> 'S' THEN
1102 x_return_status := l_return_status;
1103 x_msg_data := l_msg_data;
1104 END IF;
1105
1106 EXCEPTION
1107
1108 WHEN OTHERS THEN
1109 ROLLBACK TO SAVEPOINT Check_Integrity;
1110 x_return_status := 'U';
1111 x_oracle_error := APP_EXCEPTION.Get_Code;
1112 l_msg_data := APP_EXCEPTION.Get_Text;
1113 FND_MESSAGE.SET_NAME('GR',
1114 'GR_UNEXPECTED_ERROR');
1115 FND_MESSAGE.SET_TOKEN('TEXT',
1116 l_msg_data,
1117 FALSE);
1118 IF FND_API.To_Boolean(p_called_by_form) THEN
1119 APP_EXCEPTION.Raise_Exception;
1120 ELSE
1121 x_msg_data := FND_MESSAGE.Get;
1122 END IF;
1123
1127 /* p_phrase_code is the phrase code to check.
1124 END Check_Integrity;
1125
1126 PROCEDURE Check_Primary_Key
1128 ** p_phrase_concentration_low is the low value.
1129 ** p_phrase_concentration_high is the high value.
1130 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1131 ** x_rowid is the row id of the record if found.
1132 ** x_key_exists is 'T' is the record is found, 'F' if not.
1133 */
1134 (p_phrase_code IN VARCHAR2,
1135 p_phrase_concentration_low IN NUMBER,
1136 p_phrase_concentration_high IN NUMBER,
1137 p_called_by_form IN VARCHAR2,
1138 x_rowid OUT NOCOPY VARCHAR2,
1139 x_key_exists OUT NOCOPY VARCHAR2)
1140 IS
1141 /* Alphanumeric variables */
1142
1143 L_MSG_DATA VARCHAR2(80);
1144
1145 /* Declare any variables and the cursor */
1146
1147
1148 CURSOR c_get_conc_rowid
1149 IS
1150 SELECT phc.rowid
1151 FROM gr_phrase_concs phc
1152 WHERE phc.phrase_code = p_phrase_code
1153 AND phc.phrase_concentration_low = p_phrase_concentration_low
1154 AND phc.phrase_concentration_high = p_phrase_concentration_high;
1155 ConcRecord c_get_conc_rowid%ROWTYPE;
1156
1157 BEGIN
1158
1159 x_key_exists := 'F';
1160 l_msg_data := p_phrase_code;
1161 OPEN c_get_conc_rowid;
1162 FETCH c_get_conc_rowid INTO ConcRecord;
1163 IF c_get_conc_rowid%FOUND THEN
1164 x_key_exists := 'T';
1165 x_rowid := ConcRecord.rowid;
1166 ELSE
1167 x_key_exists := 'F';
1168 END IF;
1169 CLOSE c_get_conc_rowid;
1170
1171 EXCEPTION
1172
1173 WHEN Others THEN
1174 l_msg_data := APP_EXCEPTION.Get_Text;
1175 FND_MESSAGE.SET_NAME('GR',
1176 'GR_UNEXPECTED_ERROR');
1177 FND_MESSAGE.SET_TOKEN('TEXT',
1178 l_msg_data,
1179 FALSE);
1180 IF FND_API.To_Boolean(p_called_by_form) THEN
1181 APP_EXCEPTION.Raise_Exception;
1182 END IF;
1183
1184 END Check_Primary_Key;
1185
1186 END GR_PHRASE_CONCS_PKG;