[Home] [Help]
PACKAGE BODY: APPS.GR_TOXIC_SPECIES_B_PKG
Source
1 PACKAGE BODY GR_TOXIC_SPECIES_B_PKG AS
2 /*$Header: GRHITSB.pls 115.9 2002/10/28 21:41:44 gkelly ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_toxic_species_code IN VARCHAR2,
7 /* Bug 1323951 M. Thomas 28-Jan-2002 Added the following for Toxicity Calculation */
8 p_hierarchy_seq IN NUMBER,
9 /* Bug 1323951 M. Thomas 28-Jan-2002 End of the changes for Toxicity Calculation */
10 p_created_by IN NUMBER,
11 p_creation_date IN DATE,
12 p_last_updated_by IN NUMBER,
13 p_last_update_date IN DATE,
14 p_last_update_login IN NUMBER,
15 x_rowid OUT NOCOPY VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_oracle_error OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2)
19 IS
20
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
32 /* Exceptions */
33
34 FOREIGN_KEY_ERROR EXCEPTION;
35 ITEM_EXISTS_ERROR EXCEPTION;
36 ROW_MISSING_ERROR EXCEPTION;
37
38 /* Declare cursors */
39
40
41 BEGIN
42 /* Initialization Routine */
43
44 SAVEPOINT Insert_Row;
45 x_return_status := 'S';
46 x_oracle_error := 0;
47 x_msg_data := NULL;
48
49 /* Now call the check foreign key procedure */
50
51 Check_Foreign_Keys
52 (p_toxic_species_code,
53 l_return_status,
54 l_oracle_error,
55 l_msg_data);
56
57 IF l_return_status <> 'S' THEN
58 RAISE Foreign_Key_Error;
59 END IF;
60
61 /* Now check the primary key doesn't already exist */
62
63 Check_Primary_Key
64 (p_toxic_species_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_toxic_species_b
74 (toxic_species_code,
75 /* Bug 1323951 M. Thomas 28-Jan-2002 Added the following for Toxicity Calculation */
76 hierarchy_seq,
77 /* Bug 1323951 M. Thomas 28-Jan-2002 End of the changes for Toxicity Calculation */
78 created_by,
79 creation_date,
80 last_updated_by,
81 last_update_date,
82 last_update_login)
83 VALUES
84 (p_toxic_species_code,
85 /* Bug 1323951 M. Thomas 28-Jan-2002 Added the following for Toxicity Calculation */
86 p_hierarchy_seq,
87 /* Bug 1323951 M. Thomas 28-Jan-2002 End of the changes for Toxicity Calculation */
88 p_created_by,
89 p_creation_date,
90 p_last_updated_by,
91 p_last_update_date,
92 p_last_update_login);
93
94 /* Now get the row id of the inserted record */
95
96 Check_Primary_Key
97 (p_toxic_species_code,
98 'F',
99 l_rowid,
100 l_key_exists);
101
102 IF FND_API.To_Boolean(l_key_exists) THEN
103 x_rowid := l_rowid;
104 ELSE
105 RAISE Row_Missing_Error;
106 END IF;
107
108 /* Check the commit flag and if set, then commit the work. */
109
110 IF FND_API.To_Boolean(p_commit) THEN
111 COMMIT WORK;
112 END IF;
113
114 EXCEPTION
115
116 WHEN Foreign_Key_Error THEN
117 ROLLBACK TO SAVEPOINT Insert_Row;
118 x_return_status := l_return_status;
119 x_oracle_error := l_oracle_error;
120 FND_MESSAGE.SET_NAME('GR',
121 'GR_FOREIGN_KEY_ERROR');
122 FND_MESSAGE.SET_TOKEN('TEXT',
123 l_msg_data,
124 FALSE);
125 IF FND_API.To_Boolean(p_called_by_form) THEN
126 APP_EXCEPTION.Raise_Exception;
127 ELSE
128 x_msg_data := FND_MESSAGE.Get;
129 END IF;
130
131 WHEN Item_Exists_Error THEN
132 ROLLBACK TO SAVEPOINT Insert_Row;
133 x_return_status := 'E';
134 x_oracle_error := APP_EXCEPTION.Get_Code;
135 FND_MESSAGE.SET_NAME('GR',
136 'GR_RECORD_EXISTS');
137 FND_MESSAGE.SET_TOKEN('CODE',
138 p_toxic_species_code,
139 FALSE);
140 IF FND_API.To_Boolean(p_called_by_form) THEN
141 APP_EXCEPTION.Raise_Exception;
142 ELSE
143 x_msg_data := FND_MESSAGE.Get;
144 END IF;
145
146 WHEN Row_Missing_Error THEN
147 ROLLBACK TO SAVEPOINT Insert_Row;
148 x_return_status := 'E';
149 x_oracle_error := APP_EXCEPTION.Get_Code;
150 FND_MESSAGE.SET_NAME('GR',
151 'GR_NO_RECORD_INSERTED');
152 FND_MESSAGE.SET_TOKEN('CODE',
153 p_toxic_species_code,
154 FALSE);
155 IF FND_API.To_Boolean(p_called_by_form) THEN
156 APP_EXCEPTION.Raise_Exception;
157 ELSE
158 x_msg_data := FND_MESSAGE.Get;
159 END IF;
160
161 WHEN OTHERS THEN
162 ROLLBACK TO SAVEPOINT Insert_Row;
163 x_return_status := 'U';
164 x_oracle_error := APP_EXCEPTION.Get_Code;
165 l_msg_data := APP_EXCEPTION.Get_Text;
166 FND_MESSAGE.SET_NAME('GR',
167 'GR_UNEXPECTED_ERROR');
168 FND_MESSAGE.SET_TOKEN('TEXT',
169 l_msg_data,
170 FALSE);
171 IF FND_API.To_Boolean(p_called_by_form) THEN
172 APP_EXCEPTION.Raise_Exception;
173 ELSE
174 x_msg_data := FND_MESSAGE.Get;
175 END IF;
176
177 END Insert_Row;
178
179 PROCEDURE Update_Row
180 (p_commit IN VARCHAR2,
181 p_called_by_form IN VARCHAR2,
182 p_rowid IN VARCHAR2,
183 p_toxic_species_code IN VARCHAR2,
184 /* Bug 1323951 M. Thomas 28-Jan-2002 Added the following for Toxicity Calculation */
185 p_hierarchy_seq IN NUMBER,
186 /* Bug 1323951 M. Thomas 28-Jan-2002 End of the changes for Toxicity Calculation */
187 p_created_by IN NUMBER,
188 p_creation_date IN DATE,
189 p_last_updated_by IN NUMBER,
190 p_last_update_date IN DATE,
191 p_last_update_login IN NUMBER,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_oracle_error OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2)
195 IS
196
197 /* Alpha Variables */
198
199 L_RETURN_STATUS VARCHAR2(1) := 'S';
200 L_MSG_DATA VARCHAR2(2000);
201
202 /* Number Variables */
203
204 L_ORACLE_ERROR NUMBER;
205
206 /* Exceptions */
207
208 FOREIGN_KEY_ERROR EXCEPTION;
209 ROW_MISSING_ERROR EXCEPTION;
210
211 BEGIN
212
213 /* Initialization Routine */
214
215 SAVEPOINT Update_Row;
216 x_return_status := 'S';
217 x_oracle_error := 0;
218 x_msg_data := NULL;
219
220 /* Now call the check foreign key procedure */
221
222 Check_Foreign_Keys
223 (p_toxic_species_code,
224 l_return_status,
225 l_oracle_error,
226 l_msg_data);
227
228 IF l_return_status <> 'S' THEN
229 RAISE Foreign_Key_Error;
230 ELSE
231 UPDATE gr_toxic_species_b
232 SET toxic_species_code = p_toxic_species_code,
233 /* Bug 1323951 M. Thomas 28-Jan-2002 Added the following for Toxicity Calculation */
234 hierarchy_seq = p_hierarchy_seq,
235 /* Bug 1323951 M. Thomas 28-Jan-2002 End of the changes for Toxicity Calculation */
236 created_by = p_created_by,
237 creation_date = p_creation_date,
238 last_updated_by = p_last_updated_by,
239 last_update_date = p_last_update_date,
240 last_update_login = p_last_update_login
241 WHERE rowid = p_rowid;
242
243 IF SQL%NOTFOUND THEN
244 RAISE Row_Missing_Error;
245 END IF;
246 END IF;
247
248 /* Check if do the commit */
249
250 IF FND_API.To_Boolean(p_commit) THEN
251 COMMIT WORK;
252 END IF;
253
254 EXCEPTION
255
256 WHEN Foreign_Key_Error THEN
257 ROLLBACK TO SAVEPOINT Update_Row;
258 x_return_status := l_return_status;
259 x_oracle_error := l_oracle_error;
260 FND_MESSAGE.SET_NAME('GR',
261 'GR_FOREIGN_KEY_ERROR');
262 FND_MESSAGE.SET_TOKEN('TEXT',
263 l_msg_data,
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 Update_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_toxic_species_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 Update_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 Update_Row;
303
304 PROCEDURE Lock_Row
305 (p_commit IN VARCHAR2,
306 p_called_by_form IN VARCHAR2,
307 p_rowid IN VARCHAR2,
308 p_toxic_species_code IN VARCHAR2,
309 p_created_by IN NUMBER,
310 p_creation_date IN DATE,
311 p_last_updated_by IN NUMBER,
312 p_last_update_date IN DATE,
313 p_last_update_login IN NUMBER,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_oracle_error OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2)
317 IS
318
319 /* Alpha Variables */
320
321 L_RETURN_STATUS VARCHAR2(1) := 'S';
322 L_MSG_DATA VARCHAR2(2000);
323
324 /* Number Variables */
325
326 L_ORACLE_ERROR NUMBER;
327
328 /* Exceptions */
329
330 NO_DATA_FOUND_ERROR EXCEPTION;
331 RECORD_CHANGED_ERROR EXCEPTION;
332
333 /* Define the cursors */
334
335 CURSOR c_lock_toxic_species
336 IS
337 SELECT last_update_date
338 FROM gr_toxic_species_b
339 WHERE rowid = p_rowid
340 FOR UPDATE NOWAIT;
341 LockSpeciesRcd c_lock_toxic_species%ROWTYPE;
342
343 BEGIN
344
345 /* Initialization Routine */
346
347 SAVEPOINT Lock_Row;
348 x_return_status := 'S';
349 x_oracle_error := 0;
350 x_msg_data := NULL;
351
352 /* Now lock the record */
353
354 OPEN c_lock_toxic_species;
355 FETCH c_lock_toxic_species INTO LockSpeciesRcd;
356 IF c_lock_toxic_species%NOTFOUND THEN
357 CLOSE c_lock_toxic_species;
358 RAISE No_Data_Found_Error;
359 END IF;
360 CLOSE c_lock_toxic_species;
361
362
363 IF LockSpeciesRcd.last_update_date <> p_last_update_date THEN
364 RAISE RECORD_CHANGED_ERROR;
365 END IF;
366
367 IF FND_API.To_Boolean(p_commit) THEN
368 COMMIT WORK;
369 END IF;
370
371 EXCEPTION
372
373 WHEN No_Data_Found_Error THEN
374 ROLLBACK TO SAVEPOINT Lock_Row;
375 x_return_status := 'E';
376 FND_MESSAGE.SET_NAME('GR',
377 'GR_RECORD_NOT_FOUND');
378 FND_MESSAGE.SET_TOKEN('CODE',
379 p_toxic_species_code,
380 FALSE);
381 IF FND_API.To_Boolean(p_called_by_form) THEN
382 APP_EXCEPTION.Raise_Exception;
383 ELSE
384 x_msg_data := FND_MESSAGE.Get;
385 END IF;
386
387 WHEN RECORD_CHANGED_ERROR THEN
388 ROLLBACK TO SAVEPOINT Lock_Row;
389 X_return_status := 'E';
390 FND_MESSAGE.SET_NAME('FND',
391 'FORM_RECORD_CHANGED');
392 IF FND_API.To_Boolean(p_called_by_form) THEN
393 APP_EXCEPTION.Raise_Exception;
394 ELSE
395 x_msg_data := FND_MESSAGE.Get;
396 END IF;
397 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
398 ROLLBACK TO SAVEPOINT Lock_Row;
399 x_return_status := 'L';
400 x_oracle_error := APP_EXCEPTION.Get_Code;
401 IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
402 FND_MESSAGE.SET_NAME('GR',
403 'GR_ROW_IS_LOCKED');
404 x_msg_data := FND_MESSAGE.Get;
405 END IF;
406
407
408 WHEN OTHERS THEN
409 ROLLBACK TO SAVEPOINT Lock_Row;
410 x_return_status := 'U';
411 x_oracle_error := APP_EXCEPTION.Get_Code;
412 l_msg_data := APP_EXCEPTION.Get_Text;
413 FND_MESSAGE.SET_NAME('GR',
414 'GR_UNEXPECTED_ERROR');
415 FND_MESSAGE.SET_TOKEN('TEXT',
416 l_msg_data,
417 FALSE);
418 IF FND_API.To_Boolean(p_called_by_form) THEN
419 APP_EXCEPTION.Raise_Exception;
423
420 ELSE
421 x_msg_data := FND_MESSAGE.Get;
422 END IF;
424 END Lock_Row;
425
426 PROCEDURE Delete_Row
427 (p_commit IN VARCHAR2,
428 p_called_by_form IN VARCHAR2,
429 p_rowid IN VARCHAR2,
430 p_toxic_species_code IN VARCHAR2,
431 p_created_by IN NUMBER,
432 p_creation_date IN DATE,
433 p_last_updated_by IN NUMBER,
434 p_last_update_date IN DATE,
435 p_last_update_login IN NUMBER,
436 x_return_status OUT NOCOPY VARCHAR2,
437 x_oracle_error OUT NOCOPY NUMBER,
438 x_msg_data OUT NOCOPY VARCHAR2)
439 IS
440 /* Alpha Variables */
441
442 L_RETURN_STATUS VARCHAR2(1) := 'S';
443 L_MSG_DATA VARCHAR2(2000);
444
445 /* Number Variables */
446
447 L_ORACLE_ERROR NUMBER;
448
449 /* Exceptions */
450
451 CHECK_INTEGRITY_ERROR EXCEPTION;
452 ROW_MISSING_ERROR EXCEPTION;
453 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
454
455 BEGIN
456
457 /* Initialization Routine */
458
459 SAVEPOINT Delete_Row;
460 x_return_status := 'S';
461 x_oracle_error := 0;
462 x_msg_data := NULL;
463
464 /* Now call the check integrity procedure */
465
466 Check_Integrity
467 (p_called_by_form,
468 p_toxic_species_code,
469 l_return_status,
470 l_oracle_error,
471 l_msg_data);
472
473 IF l_return_status <> 'S' THEN
474 RAISE Check_Integrity_Error;
475 END IF;
476
477 DELETE FROM gr_toxic_species_b
478 WHERE rowid = p_rowid;
479
480 /* Check the commit flag and if set, then commit the work. */
481
482 IF FND_API.TO_Boolean(p_commit) THEN
483 COMMIT WORK;
484 END IF;
485
486 EXCEPTION
487
488 WHEN Check_Integrity_Error THEN
489 ROLLBACK TO SAVEPOINT Delete_Row;
490 x_return_status := l_return_status;
491 x_oracle_error := l_oracle_error;
492 IF FND_API.To_Boolean(p_called_by_form) THEN
493 APP_EXCEPTION.Raise_Exception;
494 ELSE
495 x_msg_data := FND_MESSAGE.Get;
496 END IF;
497
498 WHEN Row_Missing_Error THEN
499 ROLLBACK TO SAVEPOINT Delete_Row;
500 x_return_status := 'E';
501 x_oracle_error := APP_EXCEPTION.Get_Code;
502 FND_MESSAGE.SET_NAME('GR',
503 'GR_RECORD_NOT_FOUND');
504 FND_MESSAGE.SET_TOKEN('CODE',
505 p_toxic_species_code,
506 FALSE);
507 IF FND_API.To_Boolean(p_called_by_form) THEN
508 APP_EXCEPTION.Raise_Exception;
509 ELSE
510 x_msg_data := FND_MESSAGE.Get;
511 END IF;
512
513 WHEN OTHERS THEN
514 ROLLBACK TO SAVEPOINT Delete_Row;
515 x_return_status := 'U';
516 x_oracle_error := APP_EXCEPTION.Get_Code;
517 l_msg_data := APP_EXCEPTION.Get_Text;
518 l_msg_data := APP_EXCEPTION.Get_Text;
519 FND_MESSAGE.SET_NAME('GR',
523 FALSE);
520 'GR_UNEXPECTED_ERROR');
521 FND_MESSAGE.SET_TOKEN('TEXT',
522 l_msg_data,
524 IF FND_API.To_Boolean(p_called_by_form) THEN
525 APP_EXCEPTION.Raise_Exception;
526 ELSE
527 x_msg_data := FND_MESSAGE.Get;
528 END IF;
529
530 END Delete_Row;
531
532 PROCEDURE Check_Foreign_Keys
533 (p_toxic_species_code IN VARCHAR2,
534 x_return_status OUT NOCOPY VARCHAR2,
535 x_oracle_error OUT NOCOPY NUMBER,
536 x_msg_data OUT NOCOPY VARCHAR2)
537 IS
538
539 /* Alpha Variables */
540
541 L_RETURN_STATUS VARCHAR2(1) := 'S';
542 L_MSG_DATA VARCHAR2(2000);
543 L_ROWID VARCHAR2(18);
544 L_KEY_EXISTS VARCHAR2(1);
545
546 /* Number Variables */
547
548 L_ORACLE_ERROR NUMBER;
549
550
551 BEGIN
552
553 /* Initialization Routine */
554
555 SAVEPOINT Check_Foreign_Keys;
556 x_return_status := 'S';
557 x_oracle_error := 0;
558 x_msg_data := NULL;
559
560 /* No foreign keys need to be checked */
561
562 EXCEPTION
563
564 WHEN OTHERS THEN
565 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
566 x_return_status := 'U';
567 x_oracle_error := APP_EXCEPTION.Get_Code;
568 l_msg_data := APP_EXCEPTION.Get_Text;
569 FND_MESSAGE.SET_NAME('GR',
570 'GR_UNEXPECTED_ERROR');
571 FND_MESSAGE.SET_TOKEN('TEXT',
572 l_msg_data,
573 FALSE);
574 x_msg_data := FND_MESSAGE.Get;
575
576 END Check_Foreign_Keys;
577
578 PROCEDURE Check_Integrity
579 (p_called_by_form IN VARCHAR2,
580 p_toxic_species_code IN VARCHAR2,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_oracle_error OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2)
584 IS
585
586 /* Alpha Variables */
587
588 L_RETURN_STATUS VARCHAR2(1) := 'S';
589 L_MSG_DATA VARCHAR2(2000);
590
591 /* Number Variables */
592
593 L_ORACLE_ERROR NUMBER;
594 L_RECORD_COUNT NUMBER;
595
596 /* Exceptions */
597 INTEGRITY_ERROR EXCEPTION;
598
599 /* Define Cursors */
600
601 CURSOR c_get_item_toxic
602 IS
603 SELECT COUNT(*)
604 FROM gr_item_toxic
605 WHERE toxic_species_code = p_toxic_species_code;
606 ToxicRecord c_get_item_toxic%ROWTYPE;
607
608 BEGIN
609 /*
610 ** Initialization Routine
611 */
612 SAVEPOINT Check_Integrity;
613 x_return_status := 'S';
614 x_oracle_error := 0;
615 x_msg_data := NULL;
616 l_record_count := 0;
617
618 OPEN c_get_item_toxic;
619 FETCH c_get_item_toxic INTO l_record_count;
620 CLOSE c_get_item_toxic;
621
622 IF l_record_count <> 0 THEN
623 l_return_status := 'E';
624 l_msg_data := l_msg_data || ' gr_item_toxic';
625 END IF;
626 /*
630 RAISE INTEGRITY_ERROR;
627 ** Now sort out the error messaging
628 */
629 IF l_return_status <> 'S' THEN
631 END IF;
632
633 EXCEPTION
634
635 WHEN INTEGRITY_ERROR THEN
636 x_return_status := 'E';
637 FND_MESSAGE.SET_NAME('GR',
638 'GR_INTEGRITY_HEADER');
639 FND_MESSAGE.SET_TOKEN('CODE',
640 p_toxic_species_code,
641 FALSE);
642 FND_MESSAGE.SET_TOKEN('TABLES',
643 SUBSTR(l_msg_data,1,LENGTH(l_msg_data)-1),
644 FALSE);
645 IF FND_API.To_Boolean(p_called_by_form) THEN
646 APP_EXCEPTION.Raise_Exception;
647 ELSE
648 x_msg_data := FND_MESSAGE.Get;
649 END IF;
650 WHEN OTHERS THEN
651 ROLLBACK TO SAVEPOINT Check_Integrity;
652 x_return_status := 'U';
653 x_oracle_error := APP_EXCEPTION.Get_Code;
654 l_msg_data := APP_EXCEPTION.Get_Text;
655 FND_MESSAGE.SET_NAME('GR',
656 'GR_UNEXPECTED_ERROR');
657 FND_MESSAGE.SET_TOKEN('TEXT',
658 l_msg_data,
659 FALSE);
660 IF FND_API.To_Boolean(p_called_by_form) THEN
661 APP_EXCEPTION.Raise_Exception;
662 ELSE
663 x_msg_data := FND_MESSAGE.Get;
664 END IF;
665
666 END Check_Integrity;
667
668 PROCEDURE Check_Primary_Key
669 (p_toxic_species_code IN VARCHAR2,
670 p_called_by_form IN VARCHAR2,
671 x_rowid OUT NOCOPY VARCHAR2,
672 x_key_exists OUT NOCOPY VARCHAR2)
673 IS
674 /* Declare any variables and the cursor */
675
676 L_MSG_DATA VARCHAR2(2000);
677
678 CURSOR c_get_toxic_species_rowid
679 IS
680 SELECT ts.rowid
681 FROM gr_toxic_species_b ts
682 WHERE ts.toxic_species_code = p_toxic_species_code;
683 ToxicSpeciesRecord c_get_toxic_species_rowid%ROWTYPE;
684
685 BEGIN
686
687 x_key_exists := 'F';
688 OPEN c_get_toxic_species_rowid;
689 FETCH c_get_toxic_species_rowid INTO ToxicSpeciesRecord;
690 IF c_get_toxic_species_rowid%FOUND THEN
691 x_key_exists := 'T';
692 x_rowid := ToxicSpeciesRecord.rowid;
693 ELSE
694 x_key_exists := 'F';
695 END IF;
696 CLOSE c_get_toxic_species_rowid;
697
698 EXCEPTION
699
700 WHEN Others THEN
701 l_msg_data := APP_EXCEPTION.Get_Text;
702 FND_MESSAGE.SET_NAME('GR',
703 'GR_UNEXPECTED_ERROR');
704 FND_MESSAGE.SET_TOKEN('TEXT',
705 l_msg_data,
706 FALSE);
707 IF FND_API.To_Boolean(p_called_by_form) THEN
708 APP_EXCEPTION.Raise_Exception;
709 END IF;
710
711 END Check_Primary_Key;
712
713 END GR_TOXIC_SPECIES_B_PKG;