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