[Home] [Help]
PACKAGE BODY: APPS.GR_ITEM_TOXIC_PKG
Source
1 PACKAGE BODY GR_ITEM_TOXIC_PKG AS
2 /*$Header: GRHIITB.pls 115.4 2002/10/25 21:17:01 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_toxic_exposure_code IN VARCHAR2,
7 p_label_code IN VARCHAR2,
8 p_item_code IN VARCHAR2,
9 p_toxic_route_code IN VARCHAR2,
10 p_toxic_species_code IN VARCHAR2,
11 p_toxic_effect_code IN VARCHAR2,
12 p_toxic_dose IN NUMBER,
13 p_toxic_uom IN VARCHAR2,
14 p_exposure_time IN VARCHAR2,
15 p_toxic_note IN VARCHAR2,
16 p_attribute_category IN VARCHAR2,
17 p_attribute1 IN VARCHAR2,
18 p_attribute2 IN VARCHAR2,
19 p_attribute3 IN VARCHAR2,
20 p_attribute4 IN VARCHAR2,
21 p_attribute5 IN VARCHAR2,
22 p_attribute6 IN VARCHAR2,
23 p_attribute7 IN VARCHAR2,
24 p_attribute8 IN VARCHAR2,
25 p_attribute9 IN VARCHAR2,
26 p_attribute10 IN VARCHAR2,
27 p_attribute11 IN VARCHAR2,
28 p_attribute12 IN VARCHAR2,
29 p_attribute13 IN VARCHAR2,
30 p_attribute14 IN VARCHAR2,
31 p_attribute15 IN VARCHAR2,
32 p_attribute16 IN VARCHAR2,
33 p_attribute17 IN VARCHAR2,
34 p_attribute18 IN VARCHAR2,
35 p_attribute19 IN VARCHAR2,
36 p_attribute20 IN VARCHAR2,
37 p_attribute21 IN VARCHAR2,
38 p_attribute22 IN VARCHAR2,
39 p_attribute23 IN VARCHAR2,
40 p_attribute24 IN VARCHAR2,
41 p_attribute25 IN VARCHAR2,
42 p_attribute26 IN VARCHAR2,
43 p_attribute27 IN VARCHAR2,
44 p_attribute28 IN VARCHAR2,
45 p_attribute29 IN VARCHAR2,
46 p_attribute30 IN VARCHAR2,
47 p_created_by IN NUMBER,
48 p_creation_date IN DATE,
49 p_last_updated_by IN NUMBER,
50 p_last_update_date IN DATE,
51 p_last_update_login IN NUMBER,
52 x_rowid OUT NOCOPY VARCHAR2,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_oracle_error OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2)
56 IS
57 /* Alpha Variables */
58
59 L_RETURN_STATUS VARCHAR2(1) := 'S';
60 L_KEY_EXISTS VARCHAR2(1);
61 L_MSG_DATA VARCHAR2(2000);
62 L_ROWID VARCHAR2(18);
63 L_MSG_TOKEN VARCHAR2(100);
64
65 /* Number Variables */
66
67 L_ORACLE_ERROR NUMBER;
68
69 /* Exceptions */
70
71 FOREIGN_KEY_ERROR EXCEPTION;
72 ITEM_EXISTS_ERROR EXCEPTION;
73 ROW_MISSING_ERROR EXCEPTION;
74
75 /* Declare cursors */
76
77
78 BEGIN
79
80 /* Initialization Routine */
81
82 SAVEPOINT Insert_Row;
83 x_return_status := 'S';
84 x_oracle_error := 0;
85 x_msg_data := NULL;
86 l_msg_token := p_item_code || ' ' || p_label_code || ' ';
87 l_msg_token := l_msg_token || p_toxic_exposure_code || ' ';
88 l_msg_token := l_msg_token || p_toxic_route_code || ' ';
89 l_msg_token := l_msg_token || p_toxic_species_code;
90
91 /* Now call the check foreign key procedure */
92
93 Check_Foreign_Keys
94 (p_toxic_exposure_code,
95 p_label_code,
96 p_item_code,
97 p_toxic_route_code,
98 p_toxic_species_code,
99 p_toxic_effect_code,
100 p_toxic_dose,
101 p_toxic_uom,
102 p_exposure_time,
103 p_toxic_note,
104 p_attribute_category,
105 p_attribute1,
106 p_attribute2,
107 p_attribute3,
108 p_attribute4,
109 p_attribute5,
110 p_attribute6,
111 p_attribute7,
112 p_attribute8,
113 p_attribute9,
114 p_attribute10,
115 p_attribute11,
116 p_attribute12,
117 p_attribute13,
118 p_attribute14,
119 p_attribute15,
120 p_attribute16,
121 p_attribute17,
122 p_attribute18,
123 p_attribute19,
124 p_attribute20,
125 p_attribute21,
126 p_attribute22,
127 p_attribute23,
128 p_attribute24,
129 p_attribute25,
130 p_attribute26,
131 p_attribute27,
132 p_attribute28,
133 p_attribute29,
134 p_attribute30,
135 l_return_status,
136 l_oracle_error,
137 l_msg_data);
138 IF l_return_status <> 'S' THEN
139 RAISE Foreign_Key_Error;
140 END IF;
141
142 /* Now check the primary key doesn't already exist */
143
144 Check_Primary_Key
145 (p_item_code,
146 p_label_code,
147 p_toxic_exposure_code,
148 p_toxic_route_code,
149 p_toxic_species_code,
150 'F',
151 l_rowid,
152 l_key_exists);
153
154 IF FND_API.To_Boolean(l_key_exists) THEN
155 RAISE Item_Exists_Error;
156 END IF;
157
158 INSERT INTO gr_item_toxic
159 (toxic_exposure_code,
160 label_code,
161 item_code,
162 toxic_route_code,
163 toxic_species_code,
164 toxic_effect_code,
165 toxic_dose,
166 toxic_uom,
167 exposure_time,
168 toxic_note,
169 attribute_category,
170 attribute1,
171 attribute2,
172 attribute3,
173 attribute4,
174 attribute5,
175 attribute6,
176 attribute7,
177 attribute8,
178 attribute9,
179 attribute10,
180 attribute11,
181 attribute12,
182 attribute13,
183 attribute14,
184 attribute15,
185 attribute16,
186 attribute17,
187 attribute18,
188 attribute19,
189 attribute20,
190 attribute21,
191 attribute22,
192 attribute23,
193 attribute24,
194 attribute25,
195 attribute26,
196 attribute27,
197 attribute28,
198 attribute29,
199 attribute30,
200 created_by,
201 creation_date,
202 last_updated_by,
203 last_update_date,
204 last_update_login)
205 VALUES
206 (p_toxic_exposure_code,
207 p_label_code,
208 p_item_code,
209 p_toxic_route_code,
210 p_toxic_species_code,
211 p_toxic_effect_code,
212 p_toxic_dose,
213 p_toxic_uom,
214 p_exposure_time,
215 p_toxic_note,
216 p_attribute_category,
217 p_attribute1,
218 p_attribute2,
219 p_attribute3,
220 p_attribute4,
221 p_attribute5,
222 p_attribute6,
223 p_attribute7,
224 p_attribute8,
225 p_attribute9,
226 p_attribute10,
227 p_attribute11,
228 p_attribute12,
229 p_attribute13,
230 p_attribute14,
231 p_attribute15,
232 p_attribute16,
233 p_attribute17,
234 p_attribute18,
235 p_attribute19,
236 p_attribute20,
237 p_attribute21,
238 p_attribute22,
239 p_attribute23,
240 p_attribute24,
241 p_attribute25,
242 p_attribute26,
243 p_attribute27,
244 p_attribute28,
245 p_attribute29,
246 p_attribute30,
247 p_created_by,
248 p_creation_date,
249 p_last_updated_by,
250 p_last_update_date,
251 p_last_update_login);
252
253 /* Now get the row id of the inserted record */
254
255 Check_Primary_Key
256 (p_item_code,
257 p_label_code,
258 p_toxic_exposure_code,
259 p_toxic_route_code,
260 p_toxic_species_code,
261 'F',
262 l_rowid,
263 l_key_exists);
264
265 IF FND_API.To_Boolean(l_key_exists) THEN
266 x_rowid := l_rowid;
267 ELSE
268 RAISE Row_Missing_Error;
269 END IF;
270
271 /* Check the commit flag and if set, then commit the work. */
272
273 IF FND_API.To_Boolean(p_commit) THEN
274 COMMIT WORK;
275 END IF;
276
277 EXCEPTION
278
279 WHEN Foreign_Key_Error THEN
280 ROLLBACK TO SAVEPOINT Insert_Row;
281 x_return_status := l_return_status;
282 x_oracle_error := l_oracle_error;
283 FND_MESSAGE.SET_NAME('GR',
284 'GR_FOREIGN_KEY_ERROR');
285 FND_MESSAGE.SET_TOKEN('TEXT',
286 l_msg_data,
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 Item_Exists_Error THEN
295 ROLLBACK TO SAVEPOINT Insert_Row;
296 x_return_status := 'E';
297 x_oracle_error := APP_EXCEPTION.Get_Code;
298 FND_MESSAGE.SET_NAME('GR',
299 'GR_RECORD_EXISTS');
300 FND_MESSAGE.SET_TOKEN('CODE',
301 l_msg_token,
302 FALSE);
303 IF FND_API.To_Boolean(p_called_by_form) THEN
304 APP_EXCEPTION.Raise_Exception;
305 ELSE
306 x_msg_data := FND_MESSAGE.Get;
307 END IF;
308
309 WHEN Row_Missing_Error THEN
310 ROLLBACK TO SAVEPOINT Insert_Row;
311 x_return_status := 'E';
312 x_oracle_error := APP_EXCEPTION.Get_Code;
313 FND_MESSAGE.SET_NAME('GR',
314 'GR_NO_RECORD_INSERTED');
315 FND_MESSAGE.SET_TOKEN('CODE',
316 l_msg_token,
317 FALSE);
318 IF FND_API.To_Boolean(p_called_by_form) THEN
319 APP_EXCEPTION.Raise_Exception;
320 ELSE
321 x_msg_data := FND_MESSAGE.Get;
322 END IF;
323
324 WHEN OTHERS THEN
325 ROLLBACK TO SAVEPOINT Insert_Row;
326 x_return_status := 'U';
327 x_oracle_error := APP_EXCEPTION.Get_Code;
328 l_msg_data := APP_EXCEPTION.Get_Text;
329 FND_MESSAGE.SET_NAME('GR',
330 'GR_UNEXPECTED_ERROR');
331 FND_MESSAGE.SET_TOKEN('TEXT',
332 l_msg_token,
333 FALSE);
334 IF FND_API.To_Boolean(p_called_by_form) THEN
335 APP_EXCEPTION.Raise_Exception;
336 ELSE
337 x_msg_data := FND_MESSAGE.Get;
338 END IF;
339
340 END Insert_Row;
341
342 PROCEDURE Update_Row
343 (p_commit IN VARCHAR2,
344 p_called_by_form IN VARCHAR2,
345 p_rowid IN VARCHAR2,
346 p_toxic_exposure_code IN VARCHAR2,
347 p_label_code IN VARCHAR2,
348 p_item_code IN VARCHAR2,
349 p_toxic_route_code IN VARCHAR2,
350 p_toxic_species_code IN VARCHAR2,
351 p_toxic_effect_code IN VARCHAR2,
352 p_toxic_dose IN NUMBER,
353 p_toxic_uom IN VARCHAR2,
354 p_exposure_time IN VARCHAR2,
355 p_toxic_note IN VARCHAR2,
356 p_attribute_category IN VARCHAR2,
357 p_attribute1 IN VARCHAR2,
358 p_attribute2 IN VARCHAR2,
359 p_attribute3 IN VARCHAR2,
360 p_attribute4 IN VARCHAR2,
361 p_attribute5 IN VARCHAR2,
362 p_attribute6 IN VARCHAR2,
363 p_attribute7 IN VARCHAR2,
364 p_attribute8 IN VARCHAR2,
365 p_attribute9 IN VARCHAR2,
366 p_attribute10 IN VARCHAR2,
367 p_attribute11 IN VARCHAR2,
368 p_attribute12 IN VARCHAR2,
369 p_attribute13 IN VARCHAR2,
370 p_attribute14 IN VARCHAR2,
371 p_attribute15 IN VARCHAR2,
372 p_attribute16 IN VARCHAR2,
373 p_attribute17 IN VARCHAR2,
374 p_attribute18 IN VARCHAR2,
375 p_attribute19 IN VARCHAR2,
376 p_attribute20 IN VARCHAR2,
377 p_attribute21 IN VARCHAR2,
378 p_attribute22 IN VARCHAR2,
379 p_attribute23 IN VARCHAR2,
380 p_attribute24 IN VARCHAR2,
381 p_attribute25 IN VARCHAR2,
382 p_attribute26 IN VARCHAR2,
383 p_attribute27 IN VARCHAR2,
384 p_attribute28 IN VARCHAR2,
385 p_attribute29 IN VARCHAR2,
386 p_attribute30 IN VARCHAR2,
387 p_created_by IN NUMBER,
388 p_creation_date IN DATE,
389 p_last_updated_by IN NUMBER,
390 p_last_update_date IN DATE,
391 p_last_update_login IN NUMBER,
392 x_return_status OUT NOCOPY VARCHAR2,
393 x_oracle_error OUT NOCOPY NUMBER,
394 x_msg_data OUT NOCOPY VARCHAR2)
395 IS
396
397 /* Alpha Variables */
398
399 L_RETURN_STATUS VARCHAR2(1) := 'S';
400 L_MSG_DATA VARCHAR2(2000);
401 L_MSG_TOKEN VARCHAR2(100);
402
403 /* Number Variables */
404
405 L_ORACLE_ERROR NUMBER;
406
407 /* Exceptions */
408
409 FOREIGN_KEY_ERROR EXCEPTION;
410 ROW_MISSING_ERROR EXCEPTION;
411
412 BEGIN
413
414 /* Initialization Routine */
415
416 SAVEPOINT Update_Row;
417 x_return_status := 'S';
418 x_oracle_error := 0;
419 x_msg_data := NULL;
420 l_msg_token := p_item_code || ' ' || p_label_code || ' ';
421 l_msg_token := l_msg_token || p_toxic_exposure_code || ' ';
422 l_msg_token := l_msg_token || p_toxic_route_code || ' ';
423 l_msg_token := l_msg_token || p_toxic_species_code;
424
425 /* Now call the check foreign key procedure */
426
427 Check_Foreign_Keys
428 (p_toxic_exposure_code,
429 p_label_code,
430 p_item_code,
431 p_toxic_route_code,
432 p_toxic_species_code,
433 p_toxic_effect_code,
434 p_toxic_dose,
435 p_toxic_uom,
436 p_exposure_time,
437 p_toxic_note,
438 p_attribute_category,
439 p_attribute1,
440 p_attribute2,
441 p_attribute3,
442 p_attribute4,
443 p_attribute5,
444 p_attribute6,
445 p_attribute7,
446 p_attribute8,
447 p_attribute9,
448 p_attribute10,
449 p_attribute11,
450 p_attribute12,
451 p_attribute13,
452 p_attribute14,
453 p_attribute15,
454 p_attribute16,
455 p_attribute17,
456 p_attribute18,
457 p_attribute19,
458 p_attribute20,
459 p_attribute21,
460 p_attribute22,
461 p_attribute23,
462 p_attribute24,
463 p_attribute25,
464 p_attribute26,
465 p_attribute27,
466 p_attribute28,
467 p_attribute29,
468 p_attribute30,
469 l_return_status,
470 l_oracle_error,
471 l_msg_data);
472
473 IF l_return_status <> 'S' THEN
474 RAISE Foreign_Key_Error;
475 ELSE
476 UPDATE gr_item_toxic
477 SET toxic_exposure_code = p_toxic_exposure_code,
478 label_code = p_label_code,
479 item_code = p_item_code,
480 toxic_route_code = p_toxic_route_code,
481 toxic_species_code = p_toxic_species_code,
482 toxic_effect_code = p_toxic_effect_code,
483 toxic_dose = p_toxic_dose,
484 toxic_uom = p_toxic_uom,
485 exposure_time = p_exposure_time,
486 toxic_note = p_toxic_note,
487 attribute_category = p_attribute_category,
488 attribute1 = p_attribute1,
489 attribute2 = p_attribute2,
490 attribute3 = p_attribute3,
491 attribute4 = p_attribute4,
492 attribute5 = p_attribute5,
493 attribute6 = p_attribute6,
494 attribute7 = p_attribute7,
495 attribute8 = p_attribute8,
496 attribute9 = p_attribute9,
497 attribute10 = p_attribute10,
498 attribute11 = p_attribute11,
499 attribute12 = p_attribute12,
500 attribute13 = p_attribute13,
501 attribute14 = p_attribute14,
502 attribute15 = p_attribute15,
503 attribute16 = p_attribute16,
504 attribute17 = p_attribute17,
505 attribute18 = p_attribute18,
506 attribute19 = p_attribute19,
507 attribute20 = p_attribute20,
508 attribute21 = p_attribute21,
509 attribute22 = p_attribute22,
510 attribute23 = p_attribute23,
511 attribute24 = p_attribute24,
512 attribute25 = p_attribute25,
513 attribute26 = p_attribute26,
514 attribute27 = p_attribute27,
515 attribute28 = p_attribute28,
516 attribute29 = p_attribute29,
517 attribute30 = p_attribute30,
518 created_by = p_created_by,
519 creation_date = p_creation_date,
520 last_updated_by = p_last_updated_by,
521 last_update_date = p_last_update_date,
522 last_update_login = p_last_update_login
523 WHERE rowid = p_rowid;
524 IF SQL%NOTFOUND THEN
525 RAISE Row_Missing_Error;
526 END IF;
527 END IF;
528
529 /* Check the commit flag and if set, then commit the work. */
530
531 IF FND_API.To_Boolean(p_commit) THEN
532 COMMIT WORK;
533 END IF;
534
535 EXCEPTION
536
537 WHEN Foreign_Key_Error THEN
538 ROLLBACK TO SAVEPOINT Update_Row;
539 x_return_status := l_return_status;
540 x_oracle_error := l_oracle_error;
541 FND_MESSAGE.SET_NAME('GR',
542 'GR_FOREIGN_KEY_ERROR');
543 FND_MESSAGE.SET_TOKEN('TEXT',
544 l_msg_data,
545 FALSE);
546 IF FND_API.To_Boolean(p_called_by_form) THEN
547 APP_EXCEPTION.Raise_Exception;
548 ELSE
549 x_msg_data := FND_MESSAGE.Get;
550 END IF;
551
552 WHEN Row_Missing_Error THEN
553 ROLLBACK TO SAVEPOINT Update_Row;
554 x_return_status := 'E';
555 x_oracle_error := APP_EXCEPTION.Get_Code;
556 FND_MESSAGE.SET_NAME('GR',
557 'GR_NO_RECORD_INSERTED');
558 FND_MESSAGE.SET_TOKEN('CODE',
559 l_msg_token,
560 FALSE);
561 IF FND_API.To_Boolean(p_called_by_form) THEN
562 APP_EXCEPTION.Raise_Exception;
563 ELSE
564 x_msg_data := FND_MESSAGE.Get;
565 END IF;
566
567 WHEN OTHERS THEN
568 ROLLBACK TO SAVEPOINT Update_Row;
569 x_return_status := 'U';
570 x_oracle_error := APP_EXCEPTION.Get_Code;
571 l_msg_data := APP_EXCEPTION.Get_Text;
572 FND_MESSAGE.SET_NAME('GR',
573 'GR_UNEXPECTED_ERROR');
574 FND_MESSAGE.SET_TOKEN('TEXT',
575 l_msg_token,
576 FALSE);
577 IF FND_API.To_Boolean(p_called_by_form) THEN
578 APP_EXCEPTION.Raise_Exception;
579 ELSE
580 x_msg_data := FND_MESSAGE.Get;
581 END IF;
582
583 END Update_Row;
584
585 PROCEDURE Lock_Row
586 (p_commit IN VARCHAR2,
587 p_called_by_form IN VARCHAR2,
588 p_rowid IN VARCHAR2,
589 p_toxic_exposure_code IN VARCHAR2,
590 p_label_code IN VARCHAR2,
591 p_item_code IN VARCHAR2,
592 p_toxic_route_code IN VARCHAR2,
593 p_toxic_species_code IN VARCHAR2,
594 p_toxic_effect_code IN VARCHAR2,
595 p_toxic_dose IN NUMBER,
596 p_toxic_uom IN VARCHAR2,
597 p_exposure_time IN VARCHAR2,
598 p_toxic_note IN VARCHAR2,
599 p_attribute_category IN VARCHAR2,
600 p_attribute1 IN VARCHAR2,
601 p_attribute2 IN VARCHAR2,
602 p_attribute3 IN VARCHAR2,
603 p_attribute4 IN VARCHAR2,
604 p_attribute5 IN VARCHAR2,
605 p_attribute6 IN VARCHAR2,
606 p_attribute7 IN VARCHAR2,
607 p_attribute8 IN VARCHAR2,
608 p_attribute9 IN VARCHAR2,
609 p_attribute10 IN VARCHAR2,
610 p_attribute11 IN VARCHAR2,
611 p_attribute12 IN VARCHAR2,
612 p_attribute13 IN VARCHAR2,
613 p_attribute14 IN VARCHAR2,
614 p_attribute15 IN VARCHAR2,
615 p_attribute16 IN VARCHAR2,
616 p_attribute17 IN VARCHAR2,
617 p_attribute18 IN VARCHAR2,
618 p_attribute19 IN VARCHAR2,
619 p_attribute20 IN VARCHAR2,
620 p_attribute21 IN VARCHAR2,
621 p_attribute22 IN VARCHAR2,
622 p_attribute23 IN VARCHAR2,
623 p_attribute24 IN VARCHAR2,
624 p_attribute25 IN VARCHAR2,
625 p_attribute26 IN VARCHAR2,
626 p_attribute27 IN VARCHAR2,
627 p_attribute28 IN VARCHAR2,
628 p_attribute29 IN VARCHAR2,
629 p_attribute30 IN VARCHAR2,
630 p_created_by IN NUMBER,
631 p_creation_date IN DATE,
632 p_last_updated_by IN NUMBER,
633 p_last_update_date IN DATE,
634 p_last_update_login IN NUMBER,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_oracle_error OUT NOCOPY NUMBER,
637 x_msg_data OUT NOCOPY VARCHAR2)
638 IS
639
640 /* Alpha Variables */
641
642 L_RETURN_STATUS VARCHAR2(1) := 'S';
643 L_MSG_DATA VARCHAR2(2000);
644 L_MSG_TOKEN VARCHAR2(100);
645
646 /* Number Variables */
647
648 L_ORACLE_ERROR NUMBER;
649
650 /* Exceptions */
651
652 NO_DATA_FOUND_ERROR EXCEPTION;
653 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
654 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
655
656 /* Define the cursors */
657
658 CURSOR c_lock_item_toxic
659 IS
660 SELECT *
661 FROM gr_item_toxic
662 WHERE rowid = p_rowid
663 FOR UPDATE NOWAIT;
664 LockItemToxRcd c_lock_item_toxic%ROWTYPE;
665
666 BEGIN
667
668 /* Initialization Routine */
669
670 SAVEPOINT Lock_Row;
671 x_return_status := 'S';
672 x_oracle_error := 0;
673 x_msg_data := NULL;
674 l_msg_token := p_item_code || ' ' || p_label_code || ' ';
675 l_msg_token := l_msg_token || p_toxic_exposure_code || ' ';
676 l_msg_token := l_msg_token || p_toxic_route_code || ' ';
677 l_msg_token := l_msg_token || p_toxic_species_code;
678
679 /* Now lock the record */
680
681 OPEN c_lock_item_toxic;
682 FETCH c_lock_item_toxic INTO LockItemToxRcd;
683 IF c_lock_item_toxic%NOTFOUND THEN
684 CLOSE c_lock_item_toxic;
685 RAISE No_Data_Found_Error;
686 END IF;
687 CLOSE c_lock_item_toxic;
688
689 IF FND_API.To_Boolean(p_commit) THEN
690 COMMIT WORK;
691 END IF;
692
693 EXCEPTION
694
695 WHEN No_Data_Found_Error THEN
696 ROLLBACK TO SAVEPOINT Lock_Row;
697 x_return_status := 'E';
698 FND_MESSAGE.SET_NAME('GR',
699 'GR_RECORD_NOT_FOUND');
700 FND_MESSAGE.SET_TOKEN('CODE',
701 l_msg_token,
702 FALSE);
703 IF FND_API.To_Boolean(p_called_by_form) THEN
704 APP_EXCEPTION.Raise_Exception;
705 ELSE
706 x_msg_data := FND_MESSAGE.Get;
707 END IF;
708
709 WHEN Row_Already_Locked_Error THEN
710 ROLLBACK TO SAVEPOINT Lock_Row;
711 x_return_status := 'E';
712 x_oracle_error := APP_EXCEPTION.Get_Code;
713 FND_MESSAGE.SET_NAME('GR',
714 'GR_ROW_IS_LOCKED');
715 IF FND_API.To_Boolean(p_called_by_form) THEN
716 APP_EXCEPTION.Raise_Exception;
717 ELSE
718 x_msg_data := FND_MESSAGE.Get;
719 END IF;
720
721 WHEN OTHERS THEN
722 ROLLBACK TO SAVEPOINT Lock_Row;
723 x_return_status := 'U';
724 x_oracle_error := APP_EXCEPTION.Get_Code;
725 l_msg_data := APP_EXCEPTION.Get_Text;
726 FND_MESSAGE.SET_NAME('GR',
727 'GR_UNEXPECTED_ERROR');
728 FND_MESSAGE.SET_TOKEN('TEXT',
729 l_msg_token,
730 FALSE);
731 IF FND_API.To_Boolean(p_called_by_form) THEN
732 APP_EXCEPTION.Raise_Exception;
733 ELSE
734 x_msg_data := FND_MESSAGE.Get;
735 END IF;
736
737 END Lock_Row;
738
739 PROCEDURE Delete_Row
740 (p_commit IN VARCHAR2,
741 p_called_by_form IN VARCHAR2,
742 p_rowid IN VARCHAR2,
743 p_toxic_exposure_code IN VARCHAR2,
744 p_label_code IN VARCHAR2,
745 p_item_code IN VARCHAR2,
746 p_toxic_route_code IN VARCHAR2,
747 p_toxic_species_code IN VARCHAR2,
748 p_toxic_effect_code IN VARCHAR2,
749 p_toxic_dose IN NUMBER,
750 p_toxic_uom IN VARCHAR2,
751 p_exposure_time IN VARCHAR2,
752 p_toxic_note IN VARCHAR2,
753 p_attribute_category IN VARCHAR2,
754 p_attribute1 IN VARCHAR2,
755 p_attribute2 IN VARCHAR2,
756 p_attribute3 IN VARCHAR2,
757 p_attribute4 IN VARCHAR2,
758 p_attribute5 IN VARCHAR2,
759 p_attribute6 IN VARCHAR2,
760 p_attribute7 IN VARCHAR2,
761 p_attribute8 IN VARCHAR2,
762 p_attribute9 IN VARCHAR2,
763 p_attribute10 IN VARCHAR2,
764 p_attribute11 IN VARCHAR2,
765 p_attribute12 IN VARCHAR2,
766 p_attribute13 IN VARCHAR2,
767 p_attribute14 IN VARCHAR2,
768 p_attribute15 IN VARCHAR2,
769 p_attribute16 IN VARCHAR2,
770 p_attribute17 IN VARCHAR2,
771 p_attribute18 IN VARCHAR2,
772 p_attribute19 IN VARCHAR2,
773 p_attribute20 IN VARCHAR2,
774 p_attribute21 IN VARCHAR2,
775 p_attribute22 IN VARCHAR2,
776 p_attribute23 IN VARCHAR2,
777 p_attribute24 IN VARCHAR2,
778 p_attribute25 IN VARCHAR2,
779 p_attribute26 IN VARCHAR2,
780 p_attribute27 IN VARCHAR2,
781 p_attribute28 IN VARCHAR2,
782 p_attribute29 IN VARCHAR2,
783 p_attribute30 IN VARCHAR2,
784 p_created_by IN NUMBER,
785 p_creation_date IN DATE,
786 p_last_updated_by IN NUMBER,
787 p_last_update_date IN DATE,
788 p_last_update_login IN NUMBER,
789 x_return_status OUT NOCOPY VARCHAR2,
790 x_oracle_error OUT NOCOPY NUMBER,
791 x_msg_data OUT NOCOPY VARCHAR2)
792 IS
793
794 /* Alpha Variables */
795
796 L_RETURN_STATUS VARCHAR2(1) := 'S';
797 L_MSG_DATA VARCHAR2(2000);
798 L_MSG_TOKEN VARCHAR2(100);
799 L_CALLED_BY_FORM VARCHAR2(1);
800
801 /* Number Variables */
802
803 L_ORACLE_ERROR NUMBER;
804
805 /* Exceptions */
806
807 CHECK_INTEGRITY_ERROR EXCEPTION;
808 ROW_MISSING_ERROR EXCEPTION;
809 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
810 BEGIN
811
812 /* Initialization Routine */
813
814 SAVEPOINT Delete_Row;
815 x_return_status := 'S';
816 l_called_by_form := 'F';
817 x_oracle_error := 0;
818 x_msg_data := NULL;
819 l_msg_token := p_item_code || ' ' || p_label_code || ' ';
820 l_msg_token := l_msg_token || p_toxic_exposure_code || ' ';
821 l_msg_token := l_msg_token || p_toxic_route_code || ' ';
822 l_msg_token := l_msg_token || p_toxic_species_code;
823
824 /* Now call the check integrity procedure */
825
826 Check_Integrity
827 (l_called_by_form,
828 p_toxic_exposure_code,
829 p_label_code,
830 p_item_code,
831 p_toxic_route_code,
832 p_toxic_species_code,
833 p_toxic_effect_code,
834 p_toxic_dose,
835 p_toxic_uom,
836 p_exposure_time,
837 p_toxic_note,
838 p_attribute_category,
839 p_attribute1,
840 p_attribute2,
841 p_attribute3,
842 p_attribute4,
843 p_attribute5,
844 p_attribute6,
845 p_attribute7,
846 p_attribute8,
847 p_attribute9,
848 p_attribute10,
849 p_attribute11,
850 p_attribute12,
851 p_attribute13,
852 p_attribute14,
853 p_attribute15,
854 p_attribute16,
855 p_attribute17,
856 p_attribute18,
857 p_attribute19,
858 p_attribute20,
859 p_attribute21,
860 p_attribute22,
861 p_attribute23,
862 p_attribute24,
863 p_attribute25,
864 p_attribute26,
865 p_attribute27,
866 p_attribute28,
867 p_attribute29,
868 p_attribute30,
869 l_return_status,
870 l_oracle_error,
871 l_msg_data);
872
873 IF l_return_status <> 'S' THEN
874 RAISE Check_Integrity_Error;
875 END IF;
876
877 DELETE FROM gr_item_toxic
878 WHERE rowid = p_rowid;
879
880 /* Check the commit flag and if set, then commit the work. */
881
882 IF FND_API.TO_Boolean(p_commit) THEN
883 COMMIT WORK;
884 END IF;
885
886 EXCEPTION
887
888 WHEN Check_Integrity_Error THEN
889 ROLLBACK TO SAVEPOINT Delete_Row;
890 x_return_status := l_return_status;
891 x_oracle_error := l_oracle_error;
892 IF FND_API.To_Boolean(p_called_by_form) THEN
893 APP_EXCEPTION.Raise_Exception;
894 ELSE
895 x_msg_data := FND_MESSAGE.Get;
896 END IF;
897
898 WHEN Row_Missing_Error THEN
899 ROLLBACK TO SAVEPOINT Delete_Row;
900 x_return_status := 'E';
901 x_oracle_error := APP_EXCEPTION.Get_Code;
902 FND_MESSAGE.SET_NAME('GR',
903 'GR_RECORD_NOT_FOUND');
904 FND_MESSAGE.SET_TOKEN('CODE',
905 l_msg_token,
906 FALSE);
907 IF FND_API.To_Boolean(p_called_by_form) THEN
908 APP_EXCEPTION.Raise_Exception;
909 ELSE
910 x_msg_data := FND_MESSAGE.Get;
911 END IF;
912
913 WHEN OTHERS THEN
914 ROLLBACK TO SAVEPOINT Delete_Row;
915 x_return_status := 'U';
916 x_oracle_error := APP_EXCEPTION.Get_Code;
917 l_msg_data := APP_EXCEPTION.Get_Text;
918 l_msg_data := APP_EXCEPTION.Get_Text;
919 FND_MESSAGE.SET_NAME('GR',
920 'GR_UNEXPECTED_ERROR');
921 FND_MESSAGE.SET_TOKEN('TEXT',
922 l_msg_token,
923 FALSE);
924 IF FND_API.To_Boolean(p_called_by_form) THEN
925 APP_EXCEPTION.Raise_Exception;
926 ELSE
927 x_msg_data := FND_MESSAGE.Get;
928 END IF;
929
930 END Delete_Row;
931
932 PROCEDURE Delete_Rows
933 (p_commit IN VARCHAR2,
934 p_called_by_form IN VARCHAR2,
935 p_delete_option IN VARCHAR2,
936 p_item_code IN VARCHAR2,
937 p_label_code IN VARCHAR2,
938 p_toxic_exposure_code IN VARCHAR2,
939 p_toxic_route_code IN VARCHAR2,
940 p_toxic_species_code IN VARCHAR2,
941 x_return_status OUT NOCOPY VARCHAR2,
942 x_oracle_error OUT NOCOPY NUMBER,
943 x_msg_data OUT NOCOPY VARCHAR2)
944 IS
945
946 /* Alpha Variables */
947
948 L_RETURN_STATUS VARCHAR2(1) := 'S';
949 L_MSG_DATA VARCHAR2(2000);
950 L_MSG_TOKEN VARCHAR2(100);
951
952 /* Number Variables */
953
954 L_ORACLE_ERROR NUMBER;
955
956 /* Define the exceptions */
957
958 NULL_DELETE_OPTION_ERROR EXCEPTION;
959
960 /* Define the cursors */
961
962 BEGIN
963
964 /* Initialization Routine */
965
966 SAVEPOINT Delete_Rows;
967 x_return_status := 'S';
968 x_oracle_error := 0;
969 x_msg_data := NULL;
970
971 /*
972 ** p delete option has one of five values
973 ** 'I' - Delete all rows for the specified item.
974 ** 'L' - Delete all rows for the specified label.
975 ** 'E' - Delete all rows for the specified exposure code.
976 ** 'R' - Delete all rows for the specified toxic route.
977 ** 'S' - Delete all rows for the specified toxic species.
978 ** 'B' - Delete all rows using the item, label, type, route
979 ** and species combination.
980 */
981 IF p_delete_option = 'I' THEN
982 IF p_item_code IS NULL THEN
983 l_msg_token := 'Item Code';
984 RAISE Null_Delete_Option_Error;
985 ELSE
986 l_msg_token := p_item_code;
987
988 DELETE FROM gr_item_toxic
989 WHERE item_code = p_item_code;
990 END IF;
991 ELSIF p_delete_option = 'L' THEN
992 IF p_label_code IS NULL THEN
993 l_msg_token := 'Label Code';
994 RAISE Null_Delete_Option_Error;
995 ELSE
996 l_msg_token := p_label_code;
997
998 DELETE FROM gr_item_toxic
999 WHERE label_code = p_label_code;
1000 END IF;
1001 ELSIF p_delete_option = 'E' THEN
1002 IF p_toxic_exposure_code IS NULL THEN
1003 l_msg_token := 'Exposure Code';
1004 RAISE Null_Delete_Option_Error;
1005 ELSE
1006 l_msg_token := p_toxic_exposure_code;
1007
1008 DELETE FROM gr_item_toxic
1009 WHERE toxic_exposure_code = p_toxic_exposure_code;
1010 END IF;
1011 ELSIF p_delete_option = 'R' THEN
1012 IF p_toxic_route_code IS NULL THEN
1013 l_msg_token := 'Route Code';
1014 RAISE Null_Delete_Option_Error;
1015 ELSE
1016 l_msg_token := p_toxic_route_code;
1017
1018 DELETE FROM gr_item_toxic
1019 WHERE toxic_route_code = p_toxic_route_code;
1020 END IF;
1021 ELSIF p_delete_option = 'S' THEN
1022 IF p_toxic_species_code IS NULL THEN
1023 l_msg_token := 'Species Code';
1024 RAISE Null_Delete_Option_Error;
1025 ELSE
1026 l_msg_token := p_toxic_species_code;
1027
1028 DELETE FROM gr_item_toxic
1029 WHERE toxic_species_code = p_toxic_species_code;
1030 END IF;
1031 ELSIF p_delete_option = 'B' THEN
1032 IF p_item_code IS NULL OR
1033 p_label_code IS NULL OR
1034 p_toxic_exposure_code IS NULL OR
1035 p_toxic_route_code IS NULL OR
1036 p_toxic_species_code IS NULL THEN
1037 l_msg_token := 'Item, Label, Exposure, Route or Species Code';
1038 RAISE Null_Delete_Option_Error;
1039 ELSE
1040 l_msg_token := p_item_code ||' ' || p_label_code || ' ';
1041 l_msg_token := l_msg_token || p_toxic_exposure_code || ' ';
1042 l_msg_token := l_msg_token || p_toxic_route_code || ' ';
1043 l_msg_token := l_msg_token || p_toxic_species_code;
1044
1045 DELETE FROM gr_item_toxic
1046 WHERE item_code = p_item_code
1047 AND label_code = p_label_code
1048 AND toxic_exposure_code = p_toxic_exposure_code
1049 AND toxic_route_code = p_toxic_route_code
1050 AND toxic_species_code = p_toxic_species_code;
1051 END IF;
1052 END IF;
1053
1054 IF FND_API.To_Boolean(p_commit) THEN
1055 COMMIT WORK;
1056 END IF;
1057
1058 EXCEPTION
1059
1060 WHEN Null_Delete_Option_Error THEN
1061 x_return_status := 'E';
1062 x_oracle_error := APP_EXCEPTION.Get_Code;
1063 FND_MESSAGE.SET_NAME('GR',
1064 'GR_NULL_VALUE');
1065 FND_MESSAGE.SET_TOKEN('CODE',
1066 l_msg_token,
1067 FALSE);
1068 IF FND_API.To_Boolean(p_called_by_form) THEN
1069 APP_EXCEPTION.Raise_Exception;
1070 ELSE
1071 x_msg_data := FND_MESSAGE.Get;
1072 END IF;
1073
1074 WHEN OTHERS THEN
1075 ROLLBACK TO SAVEPOINT Delete_Rows;
1076 x_return_status := 'U';
1077 x_oracle_error := APP_EXCEPTION.Get_Code;
1078 l_msg_data := APP_EXCEPTION.Get_Text;
1079 FND_MESSAGE.SET_NAME('GR',
1080 'GR_UNEXPECTED_ERROR');
1081 FND_MESSAGE.SET_TOKEN('TEXT',
1082 l_msg_token,
1083 FALSE);
1084 IF FND_API.To_Boolean(p_called_by_form) THEN
1085 APP_EXCEPTION.Raise_Exception;
1086 ELSE
1087 x_msg_data := FND_MESSAGE.Get;
1088 END IF;
1089
1090 END Delete_Rows;
1091
1092 PROCEDURE Check_Foreign_Keys
1093 (p_toxic_exposure_code IN VARCHAR2,
1094 p_label_code IN VARCHAR2,
1095 p_item_code IN VARCHAR2,
1096 p_toxic_route_code IN VARCHAR2,
1097 p_toxic_species_code IN VARCHAR2,
1098 p_toxic_effect_code IN VARCHAR2,
1099 p_toxic_dose IN NUMBER,
1100 p_toxic_uom IN VARCHAR2,
1101 p_exposure_time IN VARCHAR2,
1102 p_toxic_note IN VARCHAR2,
1103 p_attribute_category IN VARCHAR2,
1104 p_attribute1 IN VARCHAR2,
1105 p_attribute2 IN VARCHAR2,
1106 p_attribute3 IN VARCHAR2,
1107 p_attribute4 IN VARCHAR2,
1108 p_attribute5 IN VARCHAR2,
1109 p_attribute6 IN VARCHAR2,
1110 p_attribute7 IN VARCHAR2,
1111 p_attribute8 IN VARCHAR2,
1112 p_attribute9 IN VARCHAR2,
1113 p_attribute10 IN VARCHAR2,
1114 p_attribute11 IN VARCHAR2,
1115 p_attribute12 IN VARCHAR2,
1116 p_attribute13 IN VARCHAR2,
1117 p_attribute14 IN VARCHAR2,
1118 p_attribute15 IN VARCHAR2,
1119 p_attribute16 IN VARCHAR2,
1120 p_attribute17 IN VARCHAR2,
1121 p_attribute18 IN VARCHAR2,
1122 p_attribute19 IN VARCHAR2,
1123 p_attribute20 IN VARCHAR2,
1124 p_attribute21 IN VARCHAR2,
1125 p_attribute22 IN VARCHAR2,
1126 p_attribute23 IN VARCHAR2,
1127 p_attribute24 IN VARCHAR2,
1128 p_attribute25 IN VARCHAR2,
1129 p_attribute26 IN VARCHAR2,
1130 p_attribute27 IN VARCHAR2,
1131 p_attribute28 IN VARCHAR2,
1132 p_attribute29 IN VARCHAR2,
1133 p_attribute30 IN VARCHAR2,
1134 x_return_status OUT NOCOPY VARCHAR2,
1135 x_oracle_error OUT NOCOPY NUMBER,
1136 x_msg_data OUT NOCOPY VARCHAR2)
1137 IS
1138
1139 /* Alpha Variables */
1140
1141 L_RETURN_STATUS VARCHAR2(1) := 'S';
1142 L_MSG_DATA VARCHAR2(2000);
1143 L_MSG_TOKEN VARCHAR2(100);
1144 L_ROWID VARCHAR2(18);
1145 L_KEY_EXISTS VARCHAR2(1);
1146
1147 /* Number Variables */
1148
1149 L_ORACLE_ERROR NUMBER;
1150
1151 /* Error Definitions */
1152
1153 ROW_MISSING_ERROR EXCEPTION;
1154
1155
1156 BEGIN
1157
1158 /* Initialization Routine */
1159
1160 l_return_status := 'S';
1161 x_oracle_error := 0;
1162 x_msg_data := NULL;
1163 l_msg_token := NULL;
1164
1165 /* Check the item code */
1166
1167 GR_ITEM_GENERAL_PKG.Check_Primary_Key
1168 (p_item_code,
1169 'F',
1170 l_rowid,
1171 l_key_exists);
1172
1173 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1174 l_return_status := 'E';
1175 l_msg_token := l_msg_token || ' ' || p_item_code;
1176 END IF;
1177
1178 /* Check the label code */
1179
1180 GR_LABELS_B_PKG.Check_Primary_Key
1181 (p_label_code,
1182 'F',
1183 l_rowid,
1184 l_key_exists);
1185
1186 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1187 l_return_status := 'E';
1188 l_msg_token := l_msg_token || ' ' || p_label_code;
1189 END IF;
1190
1191 /* Check the toxic route code */
1192
1193 GR_TOXIC_ROUTES_B_PKG.Check_Primary_Key
1194 (p_toxic_route_code,
1195 'F',
1196 l_rowid,
1197 l_key_exists);
1198
1199 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1200 l_return_status := 'E';
1201 l_msg_token := l_msg_token || ' ' || p_toxic_route_code;
1202 END IF;
1203
1204 /* Check the toxic species code */
1205
1206 GR_TOXIC_SPECIES_B_PKG.Check_Primary_Key
1207 (p_toxic_species_code,
1208 'F',
1209 l_rowid,
1210 l_key_exists);
1211
1212 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1213 l_return_status := 'E';
1214 l_msg_token := l_msg_token || ' ' || p_toxic_species_code;
1215 END IF;
1216
1217 /* Check the toxic effects code if not null */
1218
1219 IF p_toxic_effect_code IS NOT NULL THEN
1220 GR_TOXIC_EFFECT_B_PKG.Check_Primary_Key
1221 (p_toxic_effect_code,
1222 'F',
1223 l_rowid,
1224 l_key_exists);
1225
1226 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1227 l_return_status := 'E';
1228 l_msg_token := l_msg_token || ' ' || p_toxic_effect_code;
1229 END IF;
1230 END IF;
1231
1232 IF l_return_status <> 'S' THEN
1233 RAISE Row_Missing_Error;
1234 ELSE
1235 x_return_status := 'S';
1236 END IF;
1237
1238 EXCEPTION
1239
1240 WHEN Row_Missing_Error THEN
1241 x_return_status := 'E';
1242 x_oracle_error := APP_EXCEPTION.Get_Code;
1243 FND_MESSAGE.SET_NAME('GR',
1244 'GR_RECORD_NOT_FOUND');
1245 FND_MESSAGE.SET_TOKEN('CODE',
1246 l_msg_token,
1247 FALSE);
1248 x_msg_data := FND_MESSAGE.Get;
1249
1250 WHEN OTHERS THEN
1251 x_return_status := 'U';
1252 x_oracle_error := APP_EXCEPTION.Get_Code;
1253 l_msg_data := APP_EXCEPTION.Get_Text;
1254 FND_MESSAGE.SET_NAME('GR',
1255 'GR_UNEXPECTED_ERROR');
1256 FND_MESSAGE.SET_TOKEN('TEXT',
1257 l_msg_token,
1258 FALSE);
1259 x_msg_data := FND_MESSAGE.Get;
1260
1261 END Check_Foreign_Keys;
1262
1263 PROCEDURE Check_Integrity
1264 (p_called_by_form IN VARCHAR2,
1265 p_toxic_exposure_code IN VARCHAR2,
1266 p_label_code IN VARCHAR2,
1267 p_item_code IN VARCHAR2,
1268 p_toxic_route_code IN VARCHAR2,
1269 p_toxic_species_code IN VARCHAR2,
1270 p_toxic_effect_code IN VARCHAR2,
1271 p_toxic_dose IN NUMBER,
1272 p_toxic_uom IN VARCHAR2,
1273 p_exposure_time IN VARCHAR2,
1274 p_toxic_note IN VARCHAR2,
1275 p_attribute_category IN VARCHAR2,
1276 p_attribute1 IN VARCHAR2,
1277 p_attribute2 IN VARCHAR2,
1278 p_attribute3 IN VARCHAR2,
1279 p_attribute4 IN VARCHAR2,
1280 p_attribute5 IN VARCHAR2,
1281 p_attribute6 IN VARCHAR2,
1282 p_attribute7 IN VARCHAR2,
1283 p_attribute8 IN VARCHAR2,
1284 p_attribute9 IN VARCHAR2,
1285 p_attribute10 IN VARCHAR2,
1286 p_attribute11 IN VARCHAR2,
1287 p_attribute12 IN VARCHAR2,
1288 p_attribute13 IN VARCHAR2,
1289 p_attribute14 IN VARCHAR2,
1290 p_attribute15 IN VARCHAR2,
1291 p_attribute16 IN VARCHAR2,
1292 p_attribute17 IN VARCHAR2,
1293 p_attribute18 IN VARCHAR2,
1294 p_attribute19 IN VARCHAR2,
1295 p_attribute20 IN VARCHAR2,
1296 p_attribute21 IN VARCHAR2,
1297 p_attribute22 IN VARCHAR2,
1298 p_attribute23 IN VARCHAR2,
1299 p_attribute24 IN VARCHAR2,
1300 p_attribute25 IN VARCHAR2,
1301 p_attribute26 IN VARCHAR2,
1302 p_attribute27 IN VARCHAR2,
1303 p_attribute28 IN VARCHAR2,
1304 p_attribute29 IN VARCHAR2,
1305 p_attribute30 IN VARCHAR2,
1306 x_return_status OUT NOCOPY VARCHAR2,
1307 x_oracle_error OUT NOCOPY NUMBER,
1308 x_msg_data OUT NOCOPY VARCHAR2)
1309 IS
1310
1311 /* Alpha Variables */
1312
1313 L_RETURN_STATUS VARCHAR2(1) := 'S';
1314 L_MSG_DATA VARCHAR2(2000);
1315 L_CODE_BLOCK VARCHAR2(100);
1316
1317 /* Number Variables */
1318
1319 L_ORACLE_ERROR NUMBER;
1320 L_RECORD_COUNT NUMBER;
1321
1322 /* Define the Cursors */
1323
1324 BEGIN
1325
1326 /* Initialization Routine */
1327
1328 SAVEPOINT Check_Integrity;
1329 x_return_status := 'S';
1330 x_oracle_error := 0;
1331 x_msg_data := NULL;
1332
1333 /* No integrity checking is needed */
1334
1335
1336 EXCEPTION
1337
1338 WHEN OTHERS THEN
1339 ROLLBACK TO SAVEPOINT Check_Integrity;
1340 x_return_status := 'U';
1341 x_oracle_error := APP_EXCEPTION.Get_Code;
1342 l_msg_data := APP_EXCEPTION.Get_Text;
1343 FND_MESSAGE.SET_NAME('GR',
1344 'GR_UNEXPECTED_ERROR');
1345 FND_MESSAGE.SET_TOKEN('TEXT',
1346 l_msg_data,
1347 FALSE);
1348 IF FND_API.To_Boolean(p_called_by_form) THEN
1349 APP_EXCEPTION.Raise_Exception;
1350 ELSE
1351 x_msg_data := FND_MESSAGE.Get;
1352 END IF;
1353
1354 END Check_Integrity;
1355
1356 PROCEDURE Check_Primary_Key
1357 /* p_item_code is the item code
1358 ** p_label_code is the label code
1359 ** p_toxic_exposure_code is the exposure code
1360 ** p_toxic_route_code is the toxic route.
1361 ** p_toxic_species_code is the species code.
1362 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1363 ** x_rowid is the row id of the record if found.
1364 ** x_key_exists is 'T' is the record is found, 'F' if not.
1365 */
1366 (p_item_code IN VARCHAR2,
1367 p_label_code IN VARCHAR2,
1368 p_toxic_exposure_code IN VARCHAR2,
1369 p_toxic_route_code IN VARCHAR2,
1370 p_toxic_species_code IN VARCHAR2,
1371 p_called_by_form IN VARCHAR2,
1372 x_rowid OUT NOCOPY VARCHAR2,
1373 x_key_exists OUT NOCOPY VARCHAR2)
1374 IS
1375 /* Alphanumeric variables */
1376
1377 L_MSG_DATA VARCHAR2(100);
1378
1379 /* Declare any variables and the cursor */
1380
1381
1382 CURSOR c_get_item_toxic_rowid
1383 IS
1384 SELECT it.rowid
1385 FROM gr_item_toxic it
1386 WHERE it.item_code = p_item_code
1387 AND it.label_code = p_label_code
1388 AND it.toxic_exposure_code = p_toxic_exposure_code
1389 AND it.toxic_route_code = p_toxic_route_code
1390 AND it.toxic_species_code = p_toxic_species_code;
1391 ItemToxicRecord c_get_item_toxic_rowid%ROWTYPE;
1392
1393 BEGIN
1394
1395 l_msg_data := p_item_code || ' ' || p_label_code || ' ';
1396 l_msg_data := l_msg_data || p_toxic_exposure_code || ' ';
1397 l_msg_data := l_msg_data || p_toxic_route_code || ' ';
1398 l_msg_data := l_msg_data || p_toxic_species_code;
1399
1400 x_key_exists := 'F';
1401 OPEN c_get_item_toxic_rowid;
1402 FETCH c_get_item_toxic_rowid INTO ItemToxicRecord;
1403 IF c_get_item_toxic_rowid%FOUND THEN
1404 x_key_exists := 'T';
1405 x_rowid := ItemToxicRecord.rowid;
1406 ELSE
1407 x_key_exists := 'F';
1408 END IF;
1409 CLOSE c_get_item_toxic_rowid;
1410
1411 EXCEPTION
1412
1413 WHEN Others THEN
1414 l_msg_data := APP_EXCEPTION.Get_Text;
1415 FND_MESSAGE.SET_NAME('GR',
1416 'GR_UNEXPECTED_ERROR');
1417 FND_MESSAGE.SET_TOKEN('TEXT',
1418 l_msg_data,
1419 FALSE);
1420 IF FND_API.To_Boolean(p_called_by_form) THEN
1421 APP_EXCEPTION.Raise_Exception;
1422 END IF;
1423
1424 END Check_Primary_Key;
1425
1426 END GR_ITEM_TOXIC_PKG;