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