[Home] [Help]
PACKAGE BODY: APPS.GR_SAFETY_COMBINATIONS_PKG
Source
1 PACKAGE BODY GR_SAFETY_COMBINATIONS_PKG AS
2 /*$Header: GRHISCB.pls 115.4 2002/10/28 16:49:37 gkelly ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_combination_sequence IN NUMBER,
7 p_safety_phrase_code IN VARCHAR2,
8 p_combination_group_number IN NUMBER,
9 p_safety_phrase_code_combo IN VARCHAR2,
10 p_display_order IN NUMBER,
11 p_created_by IN NUMBER,
12 p_creation_date IN DATE,
13 p_last_updated_by IN NUMBER,
14 p_last_update_date IN DATE,
15 p_last_update_login IN NUMBER,
16 x_rowid OUT NOCOPY VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_oracle_error OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2)
20 IS
21 /* Alpha Variables */
22
23 L_RETURN_STATUS VARCHAR2(1) := 'S';
24 L_KEY_EXISTS VARCHAR2(1);
25 L_MSG_DATA VARCHAR2(2000);
26 L_ROWID VARCHAR2(18);
27
28 /* Number Variables */
29
30 L_ORACLE_ERROR NUMBER;
31 L_CURRENT_SEQ NUMBER;
32
33 /* Exceptions */
34
35 FOREIGN_KEY_ERROR EXCEPTION;
36 ITEM_EXISTS_ERROR EXCEPTION;
37 ROW_MISSING_ERROR EXCEPTION;
38
39 /* Declare cursors */
40
41 BEGIN
42
43 /* Initialization Routine */
44
45 SAVEPOINT Insert_Row;
46 x_return_status := 'S';
47 x_oracle_error := 0;
48 x_msg_data := NULL;
49
50 /* Now call the check foreign key procedure */
51
52 Check_Foreign_Keys
53 (p_combination_sequence,
54 p_safety_phrase_code,
55 p_combination_group_number,
56 p_safety_phrase_code_combo,
57 p_display_order,
58 l_return_status,
59 l_oracle_error,
60 l_msg_data);
61 IF l_return_status <> 'S' THEN
62 RAISE Foreign_Key_Error;
63 END IF;
64
65 /* Now check the primary key doesn't already exist */
66
67 Check_Primary_Key
68 (p_combination_sequence,
69 'F',
70 l_rowid,
71 l_key_exists);
72
73 IF FND_API.To_Boolean(l_key_exists) THEN
74 RAISE Item_Exists_Error;
75 END IF;
76
77 INSERT INTO gr_safety_combinations
78 (combination_sequence_number,
79 safety_phrase_code,
80 combination_group_number,
81 safety_phrase_code_combo,
82 display_order,
83 created_by,
84 creation_date,
85 last_updated_by,
86 last_update_date,
87 last_update_login)
88 VALUES
89 (gr_safety_combinations_s.nextval,
90 p_safety_phrase_code,
91 p_combination_group_number,
92 p_safety_phrase_code_combo,
93 p_display_order,
94 p_created_by,
95 p_creation_date,
96 p_last_updated_by,
97 p_last_update_date,
98 p_last_update_login);
99
100 /* Get the assigned sequence number */
101
102 SELECT gr_safety_combinations_s.currval
103 INTO l_current_seq
104 FROM dual;
105
106 /* Now get the row id of the inserted record */
107
108 Check_Primary_Key
109 (l_current_seq,
110 'F',
111 l_rowid,
112 l_key_exists);
113
114 IF FND_API.To_Boolean(l_key_exists) THEN
115 x_rowid := l_rowid;
116 ELSE
117 RAISE Row_Missing_Error;
118 END IF;
119
120 /* Check the commit flag and if set, then commit the work. */
121
122 IF FND_API.To_Boolean(p_commit) THEN
123 COMMIT WORK;
124 END IF;
125
126 EXCEPTION
127
128 WHEN Foreign_Key_Error THEN
129 ROLLBACK TO SAVEPOINT Insert_Row;
130 x_return_status := l_return_status;
131 x_oracle_error := l_oracle_error;
132 FND_MESSAGE.SET_NAME('GR',
133 'GR_FOREIGN_KEY_ERROR');
134 FND_MESSAGE.SET_TOKEN('TEXT',
135 l_msg_data,
136 FALSE);
137 IF FND_API.To_Boolean(p_called_by_form) THEN
138 APP_EXCEPTION.Raise_Exception;
139 ELSE
140 x_msg_data := FND_MESSAGE.Get;
141 END IF;
142
143 WHEN Item_Exists_Error THEN
144 ROLLBACK TO SAVEPOINT Insert_Row;
145 x_return_status := 'E';
146 x_oracle_error := APP_EXCEPTION.Get_Code;
147 FND_MESSAGE.SET_NAME('GR',
148 'GR_RECORD_EXISTS');
149 FND_MESSAGE.SET_TOKEN('CODE',
150 p_safety_phrase_code,
151 FALSE);
152 IF FND_API.To_Boolean(p_called_by_form) THEN
153 APP_EXCEPTION.Raise_Exception;
154 ELSE
155 x_msg_data := FND_MESSAGE.Get;
156 END IF;
157
158 WHEN Row_Missing_Error THEN
159 ROLLBACK TO SAVEPOINT Insert_Row;
160 x_return_status := 'E';
161 x_oracle_error := APP_EXCEPTION.Get_Code;
162 FND_MESSAGE.SET_NAME('GR',
163 'GR_NO_RECORD_INSERTED');
164 FND_MESSAGE.SET_TOKEN('CODE',
165 p_safety_phrase_code,
166 FALSE);
167 IF FND_API.To_Boolean(p_called_by_form) THEN
168 APP_EXCEPTION.Raise_Exception;
169 ELSE
170 x_msg_data := FND_MESSAGE.Get;
171 END IF;
172
173 WHEN OTHERS THEN
174 ROLLBACK TO SAVEPOINT Insert_Row;
175 x_return_status := 'U';
176 x_oracle_error := APP_EXCEPTION.Get_Code;
177 l_msg_data := APP_EXCEPTION.Get_Text;
178 FND_MESSAGE.SET_NAME('GR',
179 'GR_UNEXPECTED_ERROR');
180 FND_MESSAGE.SET_TOKEN('TEXT',
181 l_msg_data,
182 FALSE);
183 IF FND_API.To_Boolean(p_called_by_form) THEN
184 APP_EXCEPTION.Raise_Exception;
185 ELSE
186 x_msg_data := FND_MESSAGE.Get;
187 END IF;
188
189 END Insert_Row;
190
191 PROCEDURE Update_Row
192 (p_commit IN VARCHAR2,
193 p_called_by_form IN VARCHAR2,
194 p_rowid IN VARCHAR2,
195 p_combination_sequence IN NUMBER,
196 p_safety_phrase_code IN VARCHAR2,
197 p_combination_group_number IN NUMBER,
198 p_safety_phrase_code_combo IN VARCHAR2,
199 p_display_order IN NUMBER,
200 p_created_by IN NUMBER,
201 p_creation_date IN DATE,
202 p_last_updated_by IN NUMBER,
203 p_last_update_date IN DATE,
204 p_last_update_login IN NUMBER,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_oracle_error OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2)
208 IS
209
210 /* Alpha Variables */
211
212 L_RETURN_STATUS VARCHAR2(1) := 'S';
213 L_MSG_DATA VARCHAR2(2000);
214
215 /* Number Variables */
216
217 L_ORACLE_ERROR NUMBER;
218
219 /* Exceptions */
220
221 FOREIGN_KEY_ERROR EXCEPTION;
222 ROW_MISSING_ERROR EXCEPTION;
223
224 BEGIN
225
226 /* Initialization Routine */
227
228 SAVEPOINT Update_Row;
229 x_return_status := 'S';
230 x_oracle_error := 0;
231 x_msg_data := NULL;
232
233 /* Now call the check foreign key procedure */
234
235 Check_Foreign_Keys
236 (p_combination_sequence,
237 p_safety_phrase_code,
238 p_combination_group_number,
239 p_safety_phrase_code_combo,
240 p_display_order,
241 l_return_status,
242 l_oracle_error,
243 l_msg_data);
244
245 IF l_return_status <> 'S' THEN
246 RAISE Foreign_Key_Error;
247 ELSE
248 UPDATE gr_safety_combinations
249 SET combination_sequence_number = p_combination_sequence,
250 safety_phrase_code = p_safety_phrase_code,
251 combination_group_number = p_combination_group_number,
252 safety_phrase_code_combo = p_safety_phrase_code_combo,
253 display_order = p_display_order,
254 created_by = p_created_by,
255 creation_date = p_creation_date,
256 last_updated_by = p_last_updated_by,
257 last_update_date = p_last_update_date,
258 last_update_login = p_last_update_login
259 WHERE rowid = p_rowid;
260 IF SQL%NOTFOUND THEN
261 RAISE Row_Missing_Error;
262 END IF;
263 END IF;
264
265 /* Check the commit flag and if set, then commit the work. */
266
267 IF FND_API.To_Boolean(p_commit) THEN
268 COMMIT WORK;
269 END IF;
270
271 EXCEPTION
272
273 WHEN Foreign_Key_Error THEN
274 ROLLBACK TO SAVEPOINT Update_Row;
275 x_return_status := l_return_status;
276 x_oracle_error := l_oracle_error;
277 FND_MESSAGE.SET_NAME('GR',
278 'GR_FOREIGN_KEY_ERROR');
279 FND_MESSAGE.SET_TOKEN('TEXT',
280 l_msg_data,
281 FALSE);
282 IF FND_API.To_Boolean(p_called_by_form) THEN
283 APP_EXCEPTION.Raise_Exception;
284 ELSE
285 x_msg_data := FND_MESSAGE.Get;
286 END IF;
287
288 WHEN Row_Missing_Error THEN
289 ROLLBACK TO SAVEPOINT Update_Row;
290 x_return_status := 'E';
291 x_oracle_error := APP_EXCEPTION.Get_Code;
292 FND_MESSAGE.SET_NAME('GR',
293 'GR_NO_RECORD_INSERTED');
294 FND_MESSAGE.SET_TOKEN('CODE',
295 p_safety_phrase_code,
296 FALSE);
297 IF FND_API.To_Boolean(p_called_by_form) THEN
298 APP_EXCEPTION.Raise_Exception;
299 ELSE
300 x_msg_data := FND_MESSAGE.Get;
301 END IF;
302
303 WHEN OTHERS THEN
304 ROLLBACK TO SAVEPOINT Update_Row;
305 x_return_status := 'U';
306 x_oracle_error := APP_EXCEPTION.Get_Code;
307 l_msg_data := APP_EXCEPTION.Get_Text;
308 FND_MESSAGE.SET_NAME('GR',
309 'GR_UNEXPECTED_ERROR');
310 FND_MESSAGE.SET_TOKEN('TEXT',
311 l_msg_data,
312 FALSE);
313 IF FND_API.To_Boolean(p_called_by_form) THEN
314 APP_EXCEPTION.Raise_Exception;
315 ELSE
316 x_msg_data := FND_MESSAGE.Get;
317 END IF;
318
319 END Update_Row;
320
321 PROCEDURE Lock_Row
322 (p_commit IN VARCHAR2,
323 p_called_by_form IN VARCHAR2,
324 p_rowid IN VARCHAR2,
325 p_combination_sequence IN NUMBER,
326 p_safety_phrase_code IN VARCHAR2,
327 p_combination_group_number IN NUMBER,
328 p_safety_phrase_code_combo IN VARCHAR2,
329 p_display_order IN NUMBER,
330 p_created_by IN NUMBER,
331 p_creation_date IN DATE,
332 p_last_updated_by IN NUMBER,
333 p_last_update_date IN DATE,
334 p_last_update_login IN NUMBER,
335 x_return_status OUT NOCOPY VARCHAR2,
336 x_oracle_error OUT NOCOPY NUMBER,
337 x_msg_data OUT NOCOPY VARCHAR2)
338 IS
339
340 /* Alpha Variables */
341
342 L_RETURN_STATUS VARCHAR2(1) := 'S';
343 L_MSG_DATA VARCHAR2(2000);
344
345 /* Number Variables */
346
347 L_ORACLE_ERROR NUMBER;
348
349 /* Exceptions */
350
351 NO_DATA_FOUND_ERROR EXCEPTION;
352 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
353 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
354
355 /* Define the cursors */
356
357 CURSOR c_lock_combinations
358 IS
359 SELECT *
360 FROM gr_safety_combinations
361 WHERE rowid = p_rowid
362 FOR UPDATE NOWAIT;
366
363 LockComboRcd c_lock_combinations%ROWTYPE;
364
365 BEGIN
367 /* Initialization Routine */
368
369 SAVEPOINT Lock_Row;
370 x_return_status := 'S';
371 x_oracle_error := 0;
372 x_msg_data := NULL;
373
374 /* Now lock the record */
375
376 OPEN c_lock_combinations;
377 FETCH c_lock_combinations INTO LockComboRcd;
378 IF c_lock_combinations%NOTFOUND THEN
379 CLOSE c_lock_combinations;
380 RAISE No_Data_Found_Error;
381 END IF;
382 CLOSE c_lock_combinations;
383
384 IF FND_API.To_Boolean(p_commit) THEN
385 COMMIT WORK;
386 END IF;
387
388 EXCEPTION
389
390 WHEN No_Data_Found_Error THEN
391 ROLLBACK TO SAVEPOINT Lock_Row;
392 x_return_status := 'E';
393 FND_MESSAGE.SET_NAME('GR',
394 'GR_RECORD_NOT_FOUND');
395 FND_MESSAGE.SET_TOKEN('CODE',
396 p_safety_phrase_code,
397 FALSE);
398 IF FND_API.To_Boolean(p_called_by_form) THEN
399 APP_EXCEPTION.Raise_Exception;
400 ELSE
401 x_msg_data := FND_MESSAGE.Get;
402 END IF;
403
404 WHEN Row_Already_Locked_Error THEN
405 ROLLBACK TO SAVEPOINT Lock_Row;
406 x_return_status := 'E';
407 x_oracle_error := APP_EXCEPTION.Get_Code;
408 FND_MESSAGE.SET_NAME('GR',
409 'GR_ROW_IS_LOCKED');
410 IF FND_API.To_Boolean(p_called_by_form) THEN
411 APP_EXCEPTION.Raise_Exception;
412 ELSE
413 x_msg_data := FND_MESSAGE.Get;
414 END IF;
415
416 WHEN OTHERS THEN
417 ROLLBACK TO SAVEPOINT Lock_Row;
418 x_return_status := 'U';
419 x_oracle_error := APP_EXCEPTION.Get_Code;
420 l_msg_data := APP_EXCEPTION.Get_Text;
421 FND_MESSAGE.SET_NAME('GR',
422 'GR_UNEXPECTED_ERROR');
423 FND_MESSAGE.SET_TOKEN('TEXT',
424 l_msg_data,
425 FALSE);
426 IF FND_API.To_Boolean(p_called_by_form) THEN
427 APP_EXCEPTION.Raise_Exception;
428 ELSE
429 x_msg_data := FND_MESSAGE.Get;
430 END IF;
431
432 END Lock_Row;
433
434 PROCEDURE Delete_Row
435 (p_commit IN VARCHAR2,
436 p_called_by_form IN VARCHAR2,
437 p_rowid IN VARCHAR2,
438 p_combination_sequence IN NUMBER,
439 p_safety_phrase_code IN VARCHAR2,
440 p_combination_group_number IN NUMBER,
441 p_safety_phrase_code_combo IN VARCHAR2,
442 p_display_order IN NUMBER,
443 p_created_by IN NUMBER,
444 p_creation_date IN DATE,
445 p_last_updated_by IN NUMBER,
446 p_last_update_date IN DATE,
447 p_last_update_login IN NUMBER,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_oracle_error OUT NOCOPY NUMBER,
450 x_msg_data OUT NOCOPY VARCHAR2)
451 IS
452
453 /* Alpha Variables */
454
455 L_RETURN_STATUS VARCHAR2(1) := 'S';
456 L_MSG_DATA VARCHAR2(2000);
457 L_CALLED_BY_FORM VARCHAR2(1);
458
459 /* Number Variables */
460
461 L_ORACLE_ERROR NUMBER;
462
463 /* Exceptions */
464
465 CHECK_INTEGRITY_ERROR EXCEPTION;
466 ROW_MISSING_ERROR EXCEPTION;
467 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
468
469 BEGIN
470
471 /* Initialization Routine */
472
473 SAVEPOINT Delete_Row;
474 x_return_status := 'S';
475 l_called_by_form := 'F';
476 x_oracle_error := 0;
477 x_msg_data := NULL;
478
479 /* Now call the check integrity procedure */
480
481 Check_Integrity
482 (l_called_by_form,
483 p_combination_sequence,
484 p_safety_phrase_code,
485 p_combination_group_number,
486 p_safety_phrase_code_combo,
487 p_display_order,
488 l_return_status,
489 l_oracle_error,
490 l_msg_data);
491
492 IF l_return_status <> 'S' THEN
493 RAISE Check_Integrity_Error;
494 END IF;
495
496 DELETE FROM gr_safety_combinations
497 WHERE rowid = p_rowid;
498
499 /* Check the commit flag and if set, then commit the work. */
500
501 IF FND_API.TO_Boolean(p_commit) THEN
502 COMMIT WORK;
503 END IF;
504
505 EXCEPTION
506
507 WHEN Check_Integrity_Error THEN
508 ROLLBACK TO SAVEPOINT Delete_Row;
509 x_return_status := l_return_status;
510 x_oracle_error := l_oracle_error;
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 Row_Missing_Error THEN
518 ROLLBACK TO SAVEPOINT Delete_Row;
519 x_return_status := 'E';
520 x_oracle_error := APP_EXCEPTION.Get_Code;
521 FND_MESSAGE.SET_NAME('GR',
522 'GR_RECORD_NOT_FOUND');
523 FND_MESSAGE.SET_TOKEN('CODE',
524 p_safety_phrase_code,
525 FALSE);
526 IF FND_API.To_Boolean(p_called_by_form) THEN
527 APP_EXCEPTION.Raise_Exception;
528 ELSE
529 x_msg_data := FND_MESSAGE.Get;
530 END IF;
531
532 WHEN OTHERS THEN
533 ROLLBACK TO SAVEPOINT Delete_Row;
534 x_return_status := 'U';
535 x_oracle_error := APP_EXCEPTION.Get_Code;
536 l_msg_data := APP_EXCEPTION.Get_Text;
537 l_msg_data := APP_EXCEPTION.Get_Text;
538 FND_MESSAGE.SET_NAME('GR',
539 'GR_UNEXPECTED_ERROR');
540 FND_MESSAGE.SET_TOKEN('TEXT',
541 l_msg_data,
542 FALSE);
543 IF FND_API.To_Boolean(p_called_by_form) THEN
544 APP_EXCEPTION.Raise_Exception;
545 ELSE
546 x_msg_data := FND_MESSAGE.Get;
547 END IF;
548
549 END Delete_Row;
550
551 PROCEDURE Delete_Rows
552 (p_commit IN VARCHAR2,
553 p_called_by_form IN VARCHAR2,
554 p_safety_phrase_code IN VARCHAR2,
555 x_return_status OUT NOCOPY VARCHAR2,
556 x_oracle_error OUT NOCOPY NUMBER,
557 x_msg_data OUT NOCOPY VARCHAR2)
558 IS
559
563 L_MSG_DATA VARCHAR2(2000);
560 /* Alpha Variables */
561
562 L_RETURN_STATUS VARCHAR2(1) := 'S';
564 L_MSG_TOKEN VARCHAR2(100);
565
566 /* Number Variables */
567
568 L_ORACLE_ERROR NUMBER;
569
570 /* Define the cursors */
571
572 BEGIN
573
574 /* Initialization Routine */
575
576 SAVEPOINT Delete_Rows;
577 x_return_status := 'S';
578 x_oracle_error := 0;
579 x_msg_data := NULL;
580 l_msg_token := p_safety_phrase_code;
581
582 DELETE FROM gr_safety_combinations
583 WHERE safety_phrase_code = p_safety_phrase_code;
584
585 IF FND_API.To_Boolean(p_commit) THEN
586 COMMIT WORK;
587 END IF;
588
589 EXCEPTION
590
591 WHEN OTHERS THEN
592 ROLLBACK TO SAVEPOINT Delete_Rows;
593 x_return_status := 'U';
594 x_oracle_error := APP_EXCEPTION.Get_Code;
595 l_msg_data := APP_EXCEPTION.Get_Text;
596 FND_MESSAGE.SET_NAME('GR',
597 'GR_UNEXPECTED_ERROR');
598 FND_MESSAGE.SET_TOKEN('TEXT',
599 l_msg_token,
600 FALSE);
601 IF FND_API.To_Boolean(p_called_by_form) THEN
602 APP_EXCEPTION.Raise_Exception;
603 ELSE
604 x_msg_data := FND_MESSAGE.Get;
605 END IF;
606
607 END Delete_Rows;
608
609
610 PROCEDURE Check_Foreign_Keys
611 (p_combination_sequence IN NUMBER,
612 p_safety_phrase_code IN VARCHAR2,
613 p_combination_group_number IN NUMBER,
614 p_safety_phrase_code_combo IN VARCHAR2,
615 p_display_order IN NUMBER,
616 x_return_status OUT NOCOPY VARCHAR2,
617 x_oracle_error OUT NOCOPY NUMBER,
618 x_msg_data OUT NOCOPY VARCHAR2)
619 IS
620
621 /* Alpha Variables */
622
623 L_RETURN_STATUS VARCHAR2(1) := 'S';
624 L_MSG_DATA VARCHAR2(2000);
625 L_SAFETY_PHRASE VARCHAR2(15);
626 L_KEY_EXISTS VARCHAR2(1);
627 L_ROWID VARCHAR2(18);
628
629 /* Number Variables */
630
631 L_ORACLE_ERROR NUMBER;
632
633 /* Define the cursors */
634
635
636 BEGIN
637
638 /* Initialization Routine */
639
640 SAVEPOINT Check_Foreign_Keys;
641 x_return_status := 'S';
642 x_oracle_error := 0;
643 l_msg_data := NULL;
644 x_msg_data := NULL;
645
646 /* Check the main safety phrase code first. */
647
648 l_safety_phrase := p_safety_phrase_code;
649 GR_SAFETY_PHRASES_B_PKG.Check_Primary_Key
650 (l_safety_phrase,
651 'F',
652 l_rowid,
653 l_key_exists);
654
655 IF NOT FND_API.To_Boolean(l_key_exists) THEN
656 FND_MESSAGE.SET_NAME('GR',
657 'GR_RECORD_NOT_FOUND');
658 FND_MESSAGE.SET_TOKEN('CODE',
659 l_safety_phrase,
660 FALSE);
661 l_msg_data := l_msg_data || FND_MESSAGE.Get;
662 x_return_status := 'E';
663 END IF;
664
665 /* Now check the combination phrase */
666
667 l_safety_phrase := p_safety_phrase_code_combo;
668 GR_SAFETY_PHRASES_B_PKG.Check_Primary_Key
669 (l_safety_phrase,
670 'F',
671 l_rowid,
672 l_key_exists);
673
674 IF NOT FND_API.To_Boolean(l_key_exists) THEN
675 FND_MESSAGE.SET_NAME('GR',
676 'GR_RECORD_NOT_FOUND');
677 FND_MESSAGE.SET_TOKEN('CODE',
678 l_safety_phrase,
679 FALSE);
680 l_msg_data := l_msg_data || FND_MESSAGE.Get;
681 x_return_status := 'E';
682 END IF;
683
684 IF x_return_status <> 'S' THEN
685 x_msg_data := l_msg_data;
686 END IF;
687
688 EXCEPTION
689
690 WHEN OTHERS THEN
691 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
692
693 x_return_status := 'U';
694 x_oracle_error := APP_EXCEPTION.Get_Code;
695 l_msg_data := APP_EXCEPTION.Get_Text;
696 FND_MESSAGE.SET_NAME('GR',
697 'GR_UNEXPECTED_ERROR');
698 FND_MESSAGE.SET_TOKEN('TEXT',
699 l_msg_data,
700 FALSE);
701 x_msg_data := FND_MESSAGE.Get;
702
703 END Check_Foreign_Keys;
704
705 PROCEDURE Check_Integrity
706 (p_called_by_form IN VARCHAR2,
707 p_combination_sequence IN NUMBER,
708 p_safety_phrase_code IN VARCHAR2,
709 p_combination_group_number IN NUMBER,
710 p_safety_phrase_code_combo IN VARCHAR2,
711 p_display_order IN NUMBER,
712 x_return_status OUT NOCOPY VARCHAR2,
713 x_oracle_error OUT NOCOPY NUMBER,
714 x_msg_data OUT NOCOPY VARCHAR2)
715 IS
716
717 /* Alpha Variables */
718
719 L_RETURN_STATUS VARCHAR2(1) := 'S';
720 L_MSG_DATA VARCHAR2(2000);
721 L_CODE_BLOCK VARCHAR2(100);
722
723 /* Number Variables */
724
725 L_ORACLE_ERROR NUMBER;
726 L_RECORD_COUNT NUMBER;
727
728 /* Define the Cursors */
729
730 BEGIN
731
732 /* Initialization Routine */
733
734 SAVEPOINT Check_Integrity;
735 x_return_status := 'S';
736 x_oracle_error := 0;
737 x_msg_data := NULL;
738
739 /* No integrity checking is required */
740
741 /* Now sort out the error messaging */
742
743 IF l_return_status <> 'S' THEN
744 x_return_status := l_return_status;
745 x_msg_data := l_msg_data;
746 END IF;
747
748 EXCEPTION
749
753 x_oracle_error := APP_EXCEPTION.Get_Code;
750 WHEN OTHERS THEN
751 ROLLBACK TO SAVEPOINT Check_Integrity;
752 x_return_status := 'U';
754 l_msg_data := APP_EXCEPTION.Get_Text;
755 FND_MESSAGE.SET_NAME('GR',
756 'GR_UNEXPECTED_ERROR');
757 FND_MESSAGE.SET_TOKEN('TEXT',
758 l_msg_data,
759 FALSE);
760 IF FND_API.To_Boolean(p_called_by_form) THEN
761 APP_EXCEPTION.Raise_Exception;
762 ELSE
763 x_msg_data := FND_MESSAGE.Get;
764 END IF;
765
766 END Check_Integrity;
767
768 PROCEDURE Check_Primary_Key
769 /* p_combination_sequence is the safety phrase combination to check.
770 ** p_called_by_form is 'T' if called by a form or 'F' if not.
771 ** x_rowid is the row id of the record if found.
772 ** x_key_exists is 'T' is the record is found, 'F' if not.
773 */
774 (p_combination_sequence IN NUMBER,
775 p_called_by_form IN VARCHAR2,
776 x_rowid OUT NOCOPY VARCHAR2,
777 x_key_exists OUT NOCOPY VARCHAR2)
778 IS
779 /* Alphanumeric variables */
780
781 L_MSG_DATA VARCHAR2(80);
782
783 /* Declare any variables and the cursor */
784
785
786 CURSOR c_get_combo_rowid
787 IS
788 SELECT sc.rowid
789 FROM gr_safety_combinations sc
790 WHERE sc.combination_sequence_number = p_combination_sequence;
791 ComboRecord c_get_combo_rowid%ROWTYPE;
792
793 BEGIN
794
795 x_key_exists := 'F';
796 OPEN c_get_combo_rowid;
797 FETCH c_get_combo_rowid INTO ComboRecord;
798 IF c_get_combo_rowid%FOUND THEN
799 x_key_exists := 'T';
800 x_rowid := ComboRecord.rowid;
801 ELSE
802 x_key_exists := 'F';
803 END IF;
804 CLOSE c_get_combo_rowid;
805
806 EXCEPTION
807
808 WHEN Others THEN
809 l_msg_data := APP_EXCEPTION.Get_Text;
810 FND_MESSAGE.SET_NAME('GR',
811 'GR_UNEXPECTED_ERROR');
812 FND_MESSAGE.SET_TOKEN('TEXT',
813 l_msg_data,
814 FALSE);
815 IF FND_API.To_Boolean(p_called_by_form) THEN
816 APP_EXCEPTION.Raise_Exception;
817 END IF;
818
819 END Check_Primary_Key;
820
821 END GR_SAFETY_COMBINATIONS_PKG;