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