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