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