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