[Home] [Help]
PACKAGE BODY: APPS.GR_ITEM_GENERAL_PKG
Source
1 PACKAGE BODY GR_ITEM_GENERAL_PKG AS
2 /*$Header: GRHIIG1B.pls 115.7 2002/10/25 20:48:23 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_item_group_code IN VARCHAR2,
7 p_primary_cas_number IN VARCHAR2,
8 p_ingredient_flag IN VARCHAR2,
9 p_explode_ingredient_flag IN VARCHAR2,
10 p_formula_source_indicator IN VARCHAR2,
11 p_user_id IN NUMBER,
12 p_internal_reference_number IN VARCHAR2,
13 p_label_code IN VARCHAR2,
14 p_version_code IN VARCHAR2,
15 p_last_version_code IN VARCHAR2,
16 p_product_class IN VARCHAR2,
17 p_item_code IN VARCHAR2,
18 p_actual_hazard IN NUMBER,
19 p_print_ing_phrases_flag IN VARCHAR2,
20 p_attribute_category IN VARCHAR2,
21 p_attribute1 IN VARCHAR2,
22 p_attribute2 IN VARCHAR2,
23 p_attribute3 IN VARCHAR2,
24 p_attribute4 IN VARCHAR2,
25 p_attribute5 IN VARCHAR2,
26 p_attribute6 IN VARCHAR2,
27 p_attribute7 IN VARCHAR2,
28 p_attribute8 IN VARCHAR2,
29 p_attribute9 IN VARCHAR2,
30 p_attribute10 IN VARCHAR2,
31 p_attribute11 IN VARCHAR2,
32 p_attribute12 IN VARCHAR2,
33 p_attribute13 IN VARCHAR2,
34 p_attribute14 IN VARCHAR2,
35 p_attribute15 IN VARCHAR2,
36 p_attribute16 IN VARCHAR2,
37 p_attribute17 IN VARCHAR2,
38 p_attribute18 IN VARCHAR2,
39 p_attribute19 IN VARCHAR2,
40 p_attribute20 IN VARCHAR2,
41 p_attribute21 IN VARCHAR2,
42 p_attribute22 IN VARCHAR2,
43 p_attribute23 IN VARCHAR2,
44 p_attribute24 IN VARCHAR2,
45 p_attribute25 IN VARCHAR2,
46 p_attribute26 IN VARCHAR2,
47 p_attribute27 IN VARCHAR2,
48 p_attribute28 IN VARCHAR2,
49 p_attribute29 IN VARCHAR2,
50 p_attribute30 IN VARCHAR2,
51 p_created_by IN NUMBER,
52 p_creation_date IN DATE,
53 p_last_updated_by IN NUMBER,
54 p_last_update_date IN DATE,
55 p_last_update_login IN NUMBER,
56 x_rowid OUT NOCOPY VARCHAR2,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_oracle_error OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2)
60 IS
61 /* Alpha Variables */
62
63 L_RETURN_STATUS VARCHAR2(1) := 'S';
64 L_KEY_EXISTS VARCHAR2(1);
65 L_MSG_DATA VARCHAR2(2000);
66 L_ROWID VARCHAR2(18);
67
68 /* Number Variables */
69
70 L_ORACLE_ERROR NUMBER;
71
72 /* Exceptions */
73
74 FOREIGN_KEY_ERROR EXCEPTION;
75 ITEM_EXISTS_ERROR EXCEPTION;
76 ROW_MISSING_ERROR EXCEPTION;
77
78 /* Declare cursors */
79
80
81 BEGIN
82
83 /* Initialization Routine */
84
85 SAVEPOINT Insert_Row;
86 x_return_status := 'S';
87 x_oracle_error := 0;
88 x_msg_data := NULL;
89
90 /* Now call the check foreign key procedure */
91
92 Check_Foreign_Keys
93 (p_item_group_code,
94 p_primary_cas_number,
95 p_ingredient_flag,
96 p_explode_ingredient_flag,
97 p_formula_source_indicator,
98 p_user_id,
99 p_internal_reference_number,
100 p_label_code,
101 p_version_code,
102 p_last_version_code,
103 p_product_class,
104 p_item_code,
105 p_actual_hazard,
106 p_print_ing_phrases_flag,
107 p_attribute_category,
108 p_attribute1,
109 p_attribute2,
110 p_attribute3,
111 p_attribute4,
112 p_attribute5,
113 p_attribute6,
114 p_attribute7,
115 p_attribute8,
116 p_attribute9,
117 p_attribute10,
118 p_attribute11,
119 p_attribute12,
120 p_attribute13,
121 p_attribute14,
122 p_attribute15,
123 p_attribute16,
124 p_attribute17,
125 p_attribute18,
126 p_attribute19,
127 p_attribute20,
128 p_attribute21,
129 p_attribute22,
130 p_attribute23,
131 p_attribute24,
132 p_attribute25,
133 p_attribute26,
134 p_attribute27,
135 p_attribute28,
136 p_attribute29,
137 p_attribute30,
138 l_return_status,
139 l_oracle_error,
140 l_msg_data);
141 IF l_return_status <> 'S' THEN
142 RAISE Foreign_Key_Error;
143 END IF;
144
145 /* Now check the primary key doesn't already exist */
146
147 Check_Primary_Key
148 (p_item_code,
149 'F',
150 l_rowid,
151 l_key_exists);
152
153 IF FND_API.To_Boolean(l_key_exists) THEN
154 RAISE Item_Exists_Error;
155 END IF;
156
157 INSERT INTO gr_item_general
158 (item_code,
159 item_group_code,
160 primary_cas_number,
161 ingredient_flag,
162 explode_ingredient_flag,
163 formula_source_indicator,
164 user_id,
165 internal_reference_number,
166 product_label_code,
167 version_code,
168 last_version_code,
169 product_class,
170 actual_hazard,
171 print_ingredient_phrases_flag,
172 attribute_category,
173 attribute1,
174 attribute2,
175 attribute3,
176 attribute4,
177 attribute5,
178 attribute6,
179 attribute7,
180 attribute8,
181 attribute9,
182 attribute10,
183 attribute11,
184 attribute12,
185 attribute13,
186 attribute14,
187 attribute15,
188 attribute16,
189 attribute17,
190 attribute18,
191 attribute19,
192 attribute20,
193 attribute21,
194 attribute22,
195 attribute23,
196 attribute24,
197 attribute25,
198 attribute26,
199 attribute27,
200 attribute28,
201 attribute29,
202 attribute30,
203 created_by,
204 creation_date,
205 last_updated_by,
206 last_update_date,
207 last_update_login)
208 VALUES
209 (p_item_code,
210 p_item_group_code,
211 p_primary_cas_number,
212 p_ingredient_flag,
213 p_explode_ingredient_flag,
214 p_formula_source_indicator,
215 p_user_id,
216 p_internal_reference_number,
217 p_label_code,
218 p_version_code,
219 p_last_version_code,
220 p_product_class,
221 p_actual_hazard,
222 p_print_ing_phrases_flag,
223 p_attribute_category,
224 p_attribute1,
225 p_attribute2,
226 p_attribute3,
227 p_attribute4,
228 p_attribute5,
229 p_attribute6,
230 p_attribute7,
231 p_attribute8,
232 p_attribute9,
233 p_attribute10,
234 p_attribute11,
235 p_attribute12,
236 p_attribute13,
237 p_attribute14,
238 p_attribute15,
239 p_attribute16,
240 p_attribute17,
241 p_attribute18,
242 p_attribute19,
243 p_attribute20,
244 p_attribute21,
245 p_attribute22,
246 p_attribute23,
247 p_attribute24,
248 p_attribute25,
249 p_attribute26,
250 p_attribute27,
251 p_attribute28,
252 p_attribute29,
253 p_attribute30,
254 p_created_by,
255 p_creation_date,
256 p_last_updated_by,
257 p_last_update_date,
258 p_last_update_login);
259
260 /* Now get the row id of the inserted record */
261
262 Check_Primary_Key
263 (p_item_code,
264 'F',
265 l_rowid,
266 l_key_exists);
267
268 IF FND_API.To_Boolean(l_key_exists) THEN
269 x_rowid := l_rowid;
270 ELSE
271 RAISE Row_Missing_Error;
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 Insert_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 Item_Exists_Error THEN
298 ROLLBACK TO SAVEPOINT Insert_Row;
299 x_return_status := 'E';
300 x_oracle_error := APP_EXCEPTION.Get_Code;
301 FND_MESSAGE.SET_NAME('GR',
302 'GR_RECORD_EXISTS');
303 FND_MESSAGE.SET_TOKEN('CODE',
304 p_item_code,
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 Row_Missing_Error THEN
313 ROLLBACK TO SAVEPOINT Insert_Row;
314 x_return_status := 'E';
315 x_oracle_error := APP_EXCEPTION.Get_Code;
316 FND_MESSAGE.SET_NAME('GR',
317 'GR_NO_RECORD_INSERTED');
318 FND_MESSAGE.SET_TOKEN('CODE',
319 p_item_code,
320 FALSE);
321 IF FND_API.To_Boolean(p_called_by_form) THEN
322 APP_EXCEPTION.Raise_Exception;
323 ELSE
324 x_msg_data := FND_MESSAGE.Get;
325 END IF;
326
327 WHEN OTHERS THEN
328 ROLLBACK TO SAVEPOINT Insert_Row;
329 x_return_status := 'U';
330 x_oracle_error := APP_EXCEPTION.Get_Code;
331 l_msg_data := APP_EXCEPTION.Get_Text;
332 FND_MESSAGE.SET_NAME('GR',
333 'GR_UNEXPECTED_ERROR');
334 FND_MESSAGE.SET_TOKEN('TEXT',
335 l_msg_data,
336 FALSE);
337 IF FND_API.To_Boolean(p_called_by_form) THEN
338 APP_EXCEPTION.Raise_Exception;
339 ELSE
340 x_msg_data := FND_MESSAGE.Get;
341 END IF;
342
343 END Insert_Row;
344
345 PROCEDURE Update_Row
346 (p_commit IN VARCHAR2,
347 p_called_by_form IN VARCHAR2,
348 p_rowid IN VARCHAR2,
349 p_item_group_code IN VARCHAR2,
350 p_primary_cas_number IN VARCHAR2,
351 p_ingredient_flag IN VARCHAR2,
352 p_explode_ingredient_flag IN VARCHAR2,
353 p_formula_source_indicator IN VARCHAR2,
354 p_user_id IN NUMBER,
355 p_internal_reference_number IN VARCHAR2,
356 p_label_code IN VARCHAR2,
357 p_version_code IN VARCHAR2,
358 p_last_version_code IN VARCHAR2,
359 p_product_class IN VARCHAR2,
360 p_item_code IN VARCHAR2,
361 p_actual_hazard IN NUMBER,
362 p_print_ing_phrases_flag IN VARCHAR2,
363 p_attribute_category IN VARCHAR2,
364 p_attribute1 IN VARCHAR2,
365 p_attribute2 IN VARCHAR2,
366 p_attribute3 IN VARCHAR2,
367 p_attribute4 IN VARCHAR2,
368 p_attribute5 IN VARCHAR2,
369 p_attribute6 IN VARCHAR2,
370 p_attribute7 IN VARCHAR2,
371 p_attribute8 IN VARCHAR2,
372 p_attribute9 IN VARCHAR2,
373 p_attribute10 IN VARCHAR2,
374 p_attribute11 IN VARCHAR2,
375 p_attribute12 IN VARCHAR2,
376 p_attribute13 IN VARCHAR2,
377 p_attribute14 IN VARCHAR2,
378 p_attribute15 IN VARCHAR2,
379 p_attribute16 IN VARCHAR2,
380 p_attribute17 IN VARCHAR2,
381 p_attribute18 IN VARCHAR2,
382 p_attribute19 IN VARCHAR2,
383 p_attribute20 IN VARCHAR2,
384 p_attribute21 IN VARCHAR2,
385 p_attribute22 IN VARCHAR2,
386 p_attribute23 IN VARCHAR2,
387 p_attribute24 IN VARCHAR2,
388 p_attribute25 IN VARCHAR2,
389 p_attribute26 IN VARCHAR2,
390 p_attribute27 IN VARCHAR2,
391 p_attribute28 IN VARCHAR2,
392 p_attribute29 IN VARCHAR2,
393 p_attribute30 IN VARCHAR2,
394 p_created_by IN NUMBER,
395 p_creation_date IN DATE,
396 p_last_updated_by IN NUMBER,
397 p_last_update_date IN DATE,
398 p_last_update_login IN NUMBER,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_oracle_error OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2)
402 IS
403
404 /* Alpha Variables */
405
406 L_RETURN_STATUS VARCHAR2(1) := 'S';
407 L_MSG_DATA VARCHAR2(2000);
408
409 /* Number Variables */
410
411 L_ORACLE_ERROR NUMBER;
412
413 /* Exceptions */
414
415 FOREIGN_KEY_ERROR EXCEPTION;
416 ROW_MISSING_ERROR EXCEPTION;
417 BEGIN
418
419 /* Initialization Routine */
420
421 SAVEPOINT Update_Row;
422 x_return_status := 'S';
423 x_oracle_error := 0;
424 x_msg_data := NULL;
425
426 /* Now call the check foreign key procedure */
427
428 Check_Foreign_Keys
429 (p_item_group_code,
430 p_primary_cas_number,
431 p_ingredient_flag,
432 p_explode_ingredient_flag,
433 p_formula_source_indicator,
434 p_user_id,
435 p_internal_reference_number,
436 p_label_code,
437 p_version_code,
438 p_last_version_code,
439 p_product_class,
440 p_item_code,
441 p_actual_hazard,
442 p_print_ing_phrases_flag,
443 p_attribute_category,
444 p_attribute1,
445 p_attribute2,
446 p_attribute3,
447 p_attribute4,
448 p_attribute5,
449 p_attribute6,
450 p_attribute7,
451 p_attribute8,
452 p_attribute9,
453 p_attribute10,
454 p_attribute11,
455 p_attribute12,
456 p_attribute13,
457 p_attribute14,
458 p_attribute15,
459 p_attribute16,
460 p_attribute17,
461 p_attribute18,
462 p_attribute19,
463 p_attribute20,
464 p_attribute21,
465 p_attribute22,
466 p_attribute23,
467 p_attribute24,
468 p_attribute25,
469 p_attribute26,
470 p_attribute27,
471 p_attribute28,
472 p_attribute29,
473 p_attribute30,
474 l_return_status,
475 l_oracle_error,
476 l_msg_data);
477
478 IF l_return_status <> 'S' THEN
479 RAISE Foreign_Key_Error;
480 ELSE
481 UPDATE gr_item_general
482 SET item_code = p_item_code,
483 item_group_code = p_item_group_code,
484 primary_cas_number = p_primary_cas_number,
485 ingredient_flag = p_ingredient_flag,
486 explode_ingredient_flag = p_explode_ingredient_flag,
487 formula_source_indicator = p_formula_source_indicator,
488 user_id = p_user_id,
489 internal_reference_number = p_internal_reference_number,
490 product_label_code = p_label_code,
491 version_code = p_version_code,
492 last_version_code = p_last_version_code,
493 product_class = p_product_class,
494 actual_hazard = p_actual_hazard,
495 print_ingredient_phrases_flag = p_print_ing_phrases_flag,
496 attribute_category = p_attribute_category,
497 attribute1 = p_attribute1,
498 attribute2 = p_attribute2,
499 attribute3 = p_attribute3,
500 attribute4 = p_attribute4,
501 attribute5 = p_attribute5,
502 attribute6 = p_attribute6,
503 attribute7 = p_attribute7,
504 attribute8 = p_attribute8,
505 attribute9 = p_attribute9,
506 attribute10 = p_attribute10,
507 attribute11 = p_attribute11,
508 attribute12 = p_attribute12,
509 attribute13 = p_attribute13,
510 attribute14 = p_attribute14,
511 attribute15 = p_attribute15,
512 attribute16 = p_attribute16,
513 attribute17 = p_attribute17,
514 attribute18 = p_attribute18,
515 attribute19 = p_attribute19,
516 attribute20 = p_attribute20,
517 attribute21 = p_attribute11,
518 attribute22 = p_attribute22,
519 attribute23 = p_attribute23,
520 attribute24 = p_attribute24,
521 attribute25 = p_attribute25,
522 attribute26 = p_attribute26,
523 attribute27 = p_attribute27,
524 attribute28 = p_attribute28,
525 attribute29 = p_attribute29,
526 attribute30 = p_attribute30,
527 created_by = p_created_by,
528 creation_date = p_creation_date,
529 last_updated_by = p_last_updated_by,
530 last_update_date = p_last_update_date,
531 last_update_login = p_last_update_login
532 WHERE rowid = p_rowid;
533 IF SQL%NOTFOUND THEN
534 RAISE Row_Missing_Error;
535 END IF;
536 END IF;
537
538 /* Check the commit flag and if set, then commit the work. */
539
540 IF FND_API.To_Boolean(p_commit) THEN
541 COMMIT WORK;
542 END IF;
543
544 EXCEPTION
545
546 WHEN Foreign_Key_Error THEN
547 ROLLBACK TO SAVEPOINT Update_Row;
548 x_return_status := l_return_status;
549 x_oracle_error := l_oracle_error;
550 FND_MESSAGE.SET_NAME('GR',
551 'GR_FOREIGN_KEY_ERROR');
552 FND_MESSAGE.SET_TOKEN('TEXT',
553 l_msg_data,
554 FALSE);
555 IF FND_API.To_Boolean(p_called_by_form) THEN
556 APP_EXCEPTION.Raise_Exception;
557 ELSE
558 x_msg_data := FND_MESSAGE.Get;
559 END IF;
560
561 WHEN Row_Missing_Error THEN
562 ROLLBACK TO SAVEPOINT Update_Row;
563 x_return_status := 'E';
564 x_oracle_error := APP_EXCEPTION.Get_Code;
565 FND_MESSAGE.SET_NAME('GR',
566 'GR_NO_RECORD_INSERTED');
567 FND_MESSAGE.SET_TOKEN('CODE',
568 p_item_code,
569 FALSE);
570 IF FND_API.To_Boolean(p_called_by_form) THEN
571 APP_EXCEPTION.Raise_Exception;
572 ELSE
573 x_msg_data := FND_MESSAGE.Get;
574 END IF;
575
576 WHEN OTHERS THEN
577 ROLLBACK TO SAVEPOINT Update_Row;
578 x_return_status := 'U';
579 x_oracle_error := APP_EXCEPTION.Get_Code;
580 l_msg_data := APP_EXCEPTION.Get_Text;
581 FND_MESSAGE.SET_NAME('GR',
582 'GR_UNEXPECTED_ERROR');
583 FND_MESSAGE.SET_TOKEN('TEXT',
584 l_msg_data,
585 FALSE);
586 IF FND_API.To_Boolean(p_called_by_form) THEN
587 APP_EXCEPTION.Raise_Exception;
588 ELSE
589 x_msg_data := FND_MESSAGE.Get;
590 END IF;
591
592 END Update_Row;
593
594 PROCEDURE Lock_Row
595 (p_commit IN VARCHAR2,
596 p_called_by_form IN VARCHAR2,
597 p_rowid IN VARCHAR2,
598 p_item_group_code IN VARCHAR2,
599 p_primary_cas_number IN VARCHAR2,
600 p_ingredient_flag IN VARCHAR2,
601 p_explode_ingredient_flag IN VARCHAR2,
602 p_formula_source_indicator IN VARCHAR2,
603 p_user_id IN NUMBER,
604 p_internal_reference_number IN VARCHAR2,
605 p_label_code IN VARCHAR2,
606 p_version_code IN VARCHAR2,
607 p_last_version_code IN VARCHAR2,
608 p_product_class IN VARCHAR2,
609 p_item_code IN VARCHAR2,
610 p_actual_hazard IN NUMBER,
611 p_print_ing_phrases_flag IN VARCHAR2,
612 p_attribute_category IN VARCHAR2,
613 p_attribute1 IN VARCHAR2,
614 p_attribute2 IN VARCHAR2,
615 p_attribute3 IN VARCHAR2,
616 p_attribute4 IN VARCHAR2,
617 p_attribute5 IN VARCHAR2,
618 p_attribute6 IN VARCHAR2,
619 p_attribute7 IN VARCHAR2,
620 p_attribute8 IN VARCHAR2,
621 p_attribute9 IN VARCHAR2,
622 p_attribute10 IN VARCHAR2,
623 p_attribute11 IN VARCHAR2,
624 p_attribute12 IN VARCHAR2,
625 p_attribute13 IN VARCHAR2,
626 p_attribute14 IN VARCHAR2,
627 p_attribute15 IN VARCHAR2,
628 p_attribute16 IN VARCHAR2,
629 p_attribute17 IN VARCHAR2,
630 p_attribute18 IN VARCHAR2,
631 p_attribute19 IN VARCHAR2,
632 p_attribute20 IN VARCHAR2,
633 p_attribute21 IN VARCHAR2,
634 p_attribute22 IN VARCHAR2,
635 p_attribute23 IN VARCHAR2,
636 p_attribute24 IN VARCHAR2,
637 p_attribute25 IN VARCHAR2,
638 p_attribute26 IN VARCHAR2,
639 p_attribute27 IN VARCHAR2,
640 p_attribute28 IN VARCHAR2,
641 p_attribute29 IN VARCHAR2,
642 p_attribute30 IN VARCHAR2,
643 p_created_by IN NUMBER,
644 p_creation_date IN DATE,
645 p_last_updated_by IN NUMBER,
646 p_last_update_date IN DATE,
647 p_last_update_login IN NUMBER,
648 x_return_status OUT NOCOPY VARCHAR2,
649 x_oracle_error OUT NOCOPY NUMBER,
650 x_msg_data OUT NOCOPY VARCHAR2)
651 IS
652
653 /* Alpha Variables */
654
655 L_RETURN_STATUS VARCHAR2(1) := 'S';
656 L_MSG_DATA VARCHAR2(2000);
657
658 /* Number Variables */
659
660 L_ORACLE_ERROR NUMBER;
661
662 /* Exceptions */
663
664 NO_DATA_FOUND_ERROR EXCEPTION;
665 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
666 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
667
668 /* Define the cursors */
669
670 CURSOR c_lock_item
671 IS
672 SELECT *
673 FROM gr_item_general
674 WHERE rowid = p_rowid
675 FOR UPDATE NOWAIT;
676 LockItemRcd c_lock_item%ROWTYPE;
677 BEGIN
678
679 /* Initialization Routine */
680
681 SAVEPOINT Lock_Row;
682 x_return_status := 'S';
683 x_oracle_error := 0;
684 x_msg_data := NULL;
685
686 /* Now lock the record */
687
688 OPEN c_lock_item;
689 FETCH c_lock_item INTO LockItemRcd;
690 IF c_lock_item%NOTFOUND THEN
691 CLOSE c_lock_item;
692 RAISE No_Data_Found_Error;
693 END IF;
694 CLOSE c_lock_item;
695
696 IF FND_API.To_Boolean(p_commit) THEN
697 COMMIT WORK;
698 END IF;
699
700 EXCEPTION
701
702 WHEN No_Data_Found_Error THEN
703 ROLLBACK TO SAVEPOINT Lock_Row;
704 x_return_status := 'E';
705 FND_MESSAGE.SET_NAME('GR',
706 'GR_RECORD_NOT_FOUND');
707 FND_MESSAGE.SET_TOKEN('CODE',
708 p_item_code,
709 FALSE);
710 IF FND_API.To_Boolean(p_called_by_form) THEN
711 APP_EXCEPTION.Raise_Exception;
712 ELSE
713 x_msg_data := FND_MESSAGE.Get;
714 END IF;
715
716 WHEN Row_Already_Locked_Error THEN
717 ROLLBACK TO SAVEPOINT Lock_Row;
718 x_return_status := 'E';
719 x_oracle_error := APP_EXCEPTION.Get_Code;
720 FND_MESSAGE.SET_NAME('GR',
721 'GR_ROW_IS_LOCKED');
722 IF FND_API.To_Boolean(p_called_by_form) THEN
723 APP_EXCEPTION.Raise_Exception;
724 ELSE
725 x_msg_data := FND_MESSAGE.Get;
726 END IF;
727
728 WHEN OTHERS THEN
729 ROLLBACK TO SAVEPOINT Lock_Row;
730 x_return_status := 'U';
731 x_oracle_error := APP_EXCEPTION.Get_Code;
732 l_msg_data := APP_EXCEPTION.Get_Text;
733 FND_MESSAGE.SET_NAME('GR',
734 'GR_UNEXPECTED_ERROR');
735 FND_MESSAGE.SET_TOKEN('TEXT',
736 l_msg_data,
737 FALSE);
738 IF FND_API.To_Boolean(p_called_by_form) THEN
739 APP_EXCEPTION.Raise_Exception;
740 ELSE
741 x_msg_data := FND_MESSAGE.Get;
742 END IF;
743
744 END Lock_Row;
745
746 PROCEDURE Delete_Row
747 (p_commit IN VARCHAR2,
748 p_called_by_form IN VARCHAR2,
749 p_rowid IN VARCHAR2,
750 p_item_group_code IN VARCHAR2,
751 p_primary_cas_number IN VARCHAR2,
752 p_ingredient_flag IN VARCHAR2,
753 p_explode_ingredient_flag IN VARCHAR2,
754 p_formula_source_indicator IN VARCHAR2,
755 p_user_id IN NUMBER,
756 p_internal_reference_number IN VARCHAR2,
757 p_label_code IN VARCHAR2,
758 p_version_code IN VARCHAR2,
759 p_last_version_code IN VARCHAR2,
760 p_product_class IN VARCHAR2,
761 p_item_code IN VARCHAR2,
762 p_actual_hazard IN NUMBER,
763 p_print_ing_phrases_flag IN VARCHAR2,
764 p_attribute_category IN VARCHAR2,
765 p_attribute1 IN VARCHAR2,
766 p_attribute2 IN VARCHAR2,
767 p_attribute3 IN VARCHAR2,
768 p_attribute4 IN VARCHAR2,
769 p_attribute5 IN VARCHAR2,
770 p_attribute6 IN VARCHAR2,
771 p_attribute7 IN VARCHAR2,
772 p_attribute8 IN VARCHAR2,
773 p_attribute9 IN VARCHAR2,
774 p_attribute10 IN VARCHAR2,
775 p_attribute11 IN VARCHAR2,
776 p_attribute12 IN VARCHAR2,
777 p_attribute13 IN VARCHAR2,
778 p_attribute14 IN VARCHAR2,
779 p_attribute15 IN VARCHAR2,
780 p_attribute16 IN VARCHAR2,
781 p_attribute17 IN VARCHAR2,
782 p_attribute18 IN VARCHAR2,
783 p_attribute19 IN VARCHAR2,
784 p_attribute20 IN VARCHAR2,
785 p_attribute21 IN VARCHAR2,
786 p_attribute22 IN VARCHAR2,
787 p_attribute23 IN VARCHAR2,
788 p_attribute24 IN VARCHAR2,
789 p_attribute25 IN VARCHAR2,
790 p_attribute26 IN VARCHAR2,
791 p_attribute27 IN VARCHAR2,
792 p_attribute28 IN VARCHAR2,
793 p_attribute29 IN VARCHAR2,
794 p_attribute30 IN VARCHAR2,
795 p_created_by IN NUMBER,
796 p_creation_date IN DATE,
797 p_last_updated_by IN NUMBER,
798 p_last_update_date IN DATE,
799 p_last_update_login IN NUMBER,
800 x_return_status OUT NOCOPY VARCHAR2,
801 x_oracle_error OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2)
803 IS
804
805 /* Alpha Variables */
806
807 L_RETURN_STATUS VARCHAR2(1) := 'S';
808 L_MSG_DATA VARCHAR2(2000);
809 L_CALLED_BY_FORM VARCHAR2(1);
810
811 /* Number Variables */
812
813 L_ORACLE_ERROR NUMBER;
814
815 /* Exceptions */
816
817 CHECK_INTEGRITY_ERROR EXCEPTION;
818 ROW_MISSING_ERROR EXCEPTION;
819 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
820
821 BEGIN
822
823 /* Initialization Routine */
824
825 SAVEPOINT Delete_Row;
826 x_return_status := 'S';
827 l_called_by_form := 'F';
828 x_oracle_error := 0;
829 x_msg_data := NULL;
830
831 /* Now call the check integrity procedure */
832
833 Check_Integrity
834 (l_called_by_form,
835 p_item_group_code,
836 p_primary_cas_number,
837 p_ingredient_flag,
838 p_explode_ingredient_flag,
839 p_formula_source_indicator,
840 p_user_id,
841 p_internal_reference_number,
842 p_label_code,
843 p_version_code,
844 p_last_version_code,
845 p_product_class,
846 p_item_code,
847 p_actual_hazard,
848 p_print_ing_phrases_flag,
849 p_attribute_category,
850 p_attribute1,
851 p_attribute2,
852 p_attribute3,
853 p_attribute4,
854 p_attribute5,
855 p_attribute6,
856 p_attribute7,
857 p_attribute8,
858 p_attribute9,
859 p_attribute10,
860 p_attribute11,
861 p_attribute12,
862 p_attribute13,
863 p_attribute14,
864 p_attribute15,
865 p_attribute16,
866 p_attribute17,
867 p_attribute18,
868 p_attribute19,
869 p_attribute20,
870 p_attribute21,
871 p_attribute22,
872 p_attribute23,
873 p_attribute24,
874 p_attribute25,
875 p_attribute26,
876 p_attribute27,
877 p_attribute28,
878 p_attribute29,
879 p_attribute30,
880 l_return_status,
881 l_oracle_error,
882 l_msg_data);
883
884 IF l_return_status <> 'S' THEN
885 RAISE Check_Integrity_Error;
886 END IF;
887
888 DELETE FROM gr_item_general
889 WHERE rowid = p_rowid;
890
891 /* Check the commit flag and if set, then commit the work. */
892
893 IF FND_API.TO_Boolean(p_commit) THEN
894 COMMIT WORK;
895 END IF;
896
897 EXCEPTION
898
899 WHEN Check_Integrity_Error THEN
900 ROLLBACK TO SAVEPOINT Delete_Row;
901 x_return_status := l_return_status;
902 x_oracle_error := l_oracle_error;
903 IF FND_API.To_Boolean(p_called_by_form) THEN
904 APP_EXCEPTION.Raise_Exception;
905 ELSE
906 x_msg_data := FND_MESSAGE.Get;
907 END IF;
908
909 WHEN Row_Missing_Error THEN
910 ROLLBACK TO SAVEPOINT Delete_Row;
911 x_return_status := 'E';
912 x_oracle_error := APP_EXCEPTION.Get_Code;
913 FND_MESSAGE.SET_NAME('GR',
914 'GR_RECORD_NOT_FOUND');
915 FND_MESSAGE.SET_TOKEN('CODE',
916 p_item_code,
917 FALSE);
918 IF FND_API.To_Boolean(p_called_by_form) THEN
919 APP_EXCEPTION.Raise_Exception;
920 ELSE
921 x_msg_data := FND_MESSAGE.Get;
922 END IF;
923
924 WHEN OTHERS THEN
925 ROLLBACK TO SAVEPOINT Delete_Row;
926 x_return_status := 'U';
927 x_oracle_error := APP_EXCEPTION.Get_Code;
928 l_msg_data := APP_EXCEPTION.Get_Text;
929 l_msg_data := APP_EXCEPTION.Get_Text;
930 FND_MESSAGE.SET_NAME('GR',
931 'GR_UNEXPECTED_ERROR');
932 FND_MESSAGE.SET_TOKEN('TEXT',
933 l_msg_data,
934 FALSE);
935 IF FND_API.To_Boolean(p_called_by_form) THEN
936 APP_EXCEPTION.Raise_Exception;
937 ELSE
938 x_msg_data := FND_MESSAGE.Get;
939 END IF;
940
941 END Delete_Row;
942
943 PROCEDURE Check_Foreign_Keys
944 (p_item_group_code IN VARCHAR2,
945 p_primary_cas_number IN VARCHAR2,
946 p_ingredient_flag IN VARCHAR2,
947 p_explode_ingredient_flag IN VARCHAR2,
948 p_formula_source_indicator IN VARCHAR2,
949 p_user_id IN NUMBER,
950 p_internal_reference_number IN VARCHAR2,
951 p_label_code IN VARCHAR2,
952 p_version_code IN VARCHAR2,
953 p_last_version_code IN VARCHAR2,
954 p_product_class IN VARCHAR2,
955 p_item_code IN VARCHAR2,
956 p_actual_hazard IN NUMBER,
957 p_print_ing_phrases_flag IN VARCHAR2,
958 p_attribute_category IN VARCHAR2,
959 p_attribute1 IN VARCHAR2,
960 p_attribute2 IN VARCHAR2,
961 p_attribute3 IN VARCHAR2,
962 p_attribute4 IN VARCHAR2,
963 p_attribute5 IN VARCHAR2,
964 p_attribute6 IN VARCHAR2,
965 p_attribute7 IN VARCHAR2,
966 p_attribute8 IN VARCHAR2,
967 p_attribute9 IN VARCHAR2,
968 p_attribute10 IN VARCHAR2,
969 p_attribute11 IN VARCHAR2,
970 p_attribute12 IN VARCHAR2,
971 p_attribute13 IN VARCHAR2,
972 p_attribute14 IN VARCHAR2,
973 p_attribute15 IN VARCHAR2,
974 p_attribute16 IN VARCHAR2,
975 p_attribute17 IN VARCHAR2,
976 p_attribute18 IN VARCHAR2,
977 p_attribute19 IN VARCHAR2,
978 p_attribute20 IN VARCHAR2,
979 p_attribute21 IN VARCHAR2,
980 p_attribute22 IN VARCHAR2,
981 p_attribute23 IN VARCHAR2,
982 p_attribute24 IN VARCHAR2,
983 p_attribute25 IN VARCHAR2,
984 p_attribute26 IN VARCHAR2,
985 p_attribute27 IN VARCHAR2,
986 p_attribute28 IN VARCHAR2,
987 p_attribute29 IN VARCHAR2,
988 p_attribute30 IN VARCHAR2,
989 x_return_status OUT NOCOPY VARCHAR2,
990 x_oracle_error OUT NOCOPY NUMBER,
991 x_msg_data OUT NOCOPY VARCHAR2)
992 IS
993
994 /* Alpha Variables */
995
996 L_RETURN_STATUS VARCHAR2(1) := 'S';
997 L_MSG_DATA VARCHAR2(2000);
998
999 /* Number Variables */
1000
1001 L_ORACLE_ERROR NUMBER;
1002
1003 /* Define the cursors */
1004 /* Item Group Code */
1005
1006 CURSOR c_get_item_group
1007 IS
1008 SELECT ig.item_group_code
1009 FROM gr_item_groups_b ig
1010 WHERE ig.item_group_code = p_item_group_code;
1011 ItemGrpRecord c_get_item_group%ROWTYPE;
1012
1013 /* Product Class */
1014
1015 CURSOR c_get_prod_class
1016 IS
1017 SELECT pc.product_class
1018 FROM gr_product_classes pc
1019 WHERE pc.product_class = p_product_class;
1020 ProdClsRecord c_get_prod_class%ROWTYPE;
1021
1022 /* User ID */
1023
1024 CURSOR c_get_user_id
1025 IS
1026 SELECT fnu.user_id
1027 FROM fnd_user fnu
1028 WHERE fnu.user_id = p_user_id;
1029 UserRcd c_get_user_id%ROWTYPE;
1030
1031 BEGIN
1032
1033 /* Initialization Routine */
1034
1035 SAVEPOINT Check_Foreign_Keys;
1036 x_return_status := 'S';
1037 x_oracle_error := 0;
1038 x_msg_data := NULL;
1039
1040 /* Check the item group code if there is a value */
1041
1042 IF p_item_group_code IS NOT NULL THEN
1043 OPEN c_get_item_group;
1044 FETCH c_get_item_group INTO ItemGrpRecord;
1045 IF c_get_item_group%NOTFOUND THEN
1046 CLOSE c_get_item_group;
1047 x_return_status := 'E';
1048 FND_MESSAGE.SET_NAME('GR',
1049 'GR_RECORD_NOT_FOUND');
1050 FND_MESSAGE.SET_TOKEN('CODE',
1051 p_item_group_code,
1052 FALSE);
1053 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1054 END IF;
1055 CLOSE c_get_item_group;
1056 END IF;
1057
1058 /* Check the product class code */
1059
1060 IF p_product_class IS NOT NULL THEN
1061 OPEN c_get_prod_class;
1062 FETCH c_get_prod_class INTO ProdClsRecord;
1063 IF c_get_prod_class%NOTFOUND THEN
1064 CLOSE c_get_prod_class;
1065 x_return_status := 'E';
1066 FND_MESSAGE.SET_NAME('GR',
1067 'GR_RECORD_NOT_FOUND');
1068 FND_MESSAGE.SET_TOKEN('CODE',
1069 p_product_class,
1070 FALSE);
1071 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1072 END IF;
1073 CLOSE c_get_prod_class;
1074 END IF;
1075
1076 /* Check the user id */
1077
1078 OPEN c_get_user_id;
1079 FETCH c_get_user_id INTO UserRcd;
1080 IF c_get_user_id%NOTFOUND THEN
1081 CLOSE c_get_user_id;
1082 x_return_status := 'E';
1083 FND_MESSAGE.SET_NAME('GR',
1084 'GR_RECORD_NOT_FOUND');
1085 FND_MESSAGE.SET_TOKEN('CODE',
1086 p_user_id,
1087 FALSE);
1088 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1089 END IF;
1090 CLOSE c_get_user_id;
1091
1092 /* Check the not null columns */
1093
1094 IF p_print_ing_phrases_flag IS NULL THEN
1095 x_return_status := 'E';
1096 FND_MESSAGE.SET_NAME('GR',
1097 'GR_NULL_VALUE');
1098 FND_MESSAGE.SET_TOKEN('CODE',
1099 p_print_ing_phrases_flag,
1100 FALSE);
1101 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1102 END IF;
1103
1104 IF x_return_status <> 'S' THEN
1105 x_msg_data := l_msg_data;
1106 END IF;
1107
1108 EXCEPTION
1109
1110 WHEN OTHERS THEN
1111 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1112 x_return_status := 'U';
1113 x_oracle_error := APP_EXCEPTION.Get_Code;
1114 l_msg_data := APP_EXCEPTION.Get_Text;
1115 FND_MESSAGE.SET_NAME('GR',
1116 'GR_UNEXPECTED_ERROR');
1117 FND_MESSAGE.SET_TOKEN('TEXT',
1118 l_msg_data,
1119 FALSE);
1120 x_msg_data := FND_MESSAGE.Get;
1121
1122 END Check_Foreign_Keys;
1123
1124 PROCEDURE Check_Integrity
1125 (p_called_by_form IN VARCHAR2,
1126 p_item_group_code IN VARCHAR2,
1127 p_primary_cas_number IN VARCHAR2,
1128 p_ingredient_flag IN VARCHAR2,
1129 p_explode_ingredient_flag IN VARCHAR2,
1130 p_formula_source_indicator IN VARCHAR2,
1131 p_user_id IN NUMBER,
1132 p_internal_reference_number IN VARCHAR2,
1133 p_label_code IN VARCHAR2,
1134 p_version_code IN VARCHAR2,
1135 p_last_version_code IN VARCHAR2,
1136 p_product_class IN VARCHAR2,
1137 p_item_code IN VARCHAR2,
1138 p_actual_hazard IN NUMBER,
1139 p_print_ing_phrases_flag IN VARCHAR2,
1140 p_attribute_category IN VARCHAR2,
1141 p_attribute1 IN VARCHAR2,
1142 p_attribute2 IN VARCHAR2,
1143 p_attribute3 IN VARCHAR2,
1144 p_attribute4 IN VARCHAR2,
1145 p_attribute5 IN VARCHAR2,
1146 p_attribute6 IN VARCHAR2,
1147 p_attribute7 IN VARCHAR2,
1148 p_attribute8 IN VARCHAR2,
1149 p_attribute9 IN VARCHAR2,
1150 p_attribute10 IN VARCHAR2,
1151 p_attribute11 IN VARCHAR2,
1152 p_attribute12 IN VARCHAR2,
1153 p_attribute13 IN VARCHAR2,
1154 p_attribute14 IN VARCHAR2,
1155 p_attribute15 IN VARCHAR2,
1156 p_attribute16 IN VARCHAR2,
1157 p_attribute17 IN VARCHAR2,
1158 p_attribute18 IN VARCHAR2,
1159 p_attribute19 IN VARCHAR2,
1160 p_attribute20 IN VARCHAR2,
1161 p_attribute21 IN VARCHAR2,
1162 p_attribute22 IN VARCHAR2,
1163 p_attribute23 IN VARCHAR2,
1164 p_attribute24 IN VARCHAR2,
1165 p_attribute25 IN VARCHAR2,
1166 p_attribute26 IN VARCHAR2,
1167 p_attribute27 IN VARCHAR2,
1168 p_attribute28 IN VARCHAR2,
1169 p_attribute29 IN VARCHAR2,
1170 p_attribute30 IN VARCHAR2,
1171 x_return_status OUT NOCOPY VARCHAR2,
1172 x_oracle_error OUT NOCOPY NUMBER,
1173 x_msg_data OUT NOCOPY VARCHAR2)
1174 IS
1175
1176 /* Alpha Variables */
1177
1178 L_RETURN_STATUS VARCHAR2(1) := 'S';
1179 L_MSG_DATA VARCHAR2(2000);
1180 L_CODE_BLOCK VARCHAR2(2000);
1181
1182 /* Number Variables */
1183
1184 L_ORACLE_ERROR NUMBER;
1185 L_RECORD_COUNT NUMBER;
1186
1187 /* Exceptions */
1188 INTEGRITY_ERROR EXCEPTION;
1189
1190 /* Define the Cursors */
1191 /* Calculations table */
1192
1193 CURSOR c_get_calculated
1194 IS
1195 SELECT COUNT(*)
1196 FROM gr_calculated ca
1197 WHERE ca.item_code = p_item_code;
1198
1199 /* Dispatch History */
1200
1201 CURSOR c_get_disp_history
1202 IS
1203 SELECT COUNT(*)
1204 FROM gr_dispatch_histories dh
1205 WHERE dh.item_code = p_item_code;
1206
1207 /* Document Print */
1208
1209 CURSOR c_get_doc_print
1210 IS
1211 SELECT COUNT(*)
1212 FROM gr_document_print dp
1213 WHERE dp.item_code = p_item_code;
1214
1215 /* European Information */
1216
1217 CURSOR c_get_emea
1218 IS
1219 SELECT COUNT(*)
1220 FROM gr_emea em
1221 WHERE em.item_code = p_item_code;
1222
1223 /* Generic Items */
1224
1225 CURSOR c_get_generics
1226 IS
1227 SELECT COUNT(*)
1228 FROM gr_generic_items_b gen
1229 WHERE gen.item_code = p_item_code;
1230
1231 /* Generic Names */
1232
1233 CURSOR c_get_generic_names
1234 IS
1235 SELECT COUNT(*)
1236 FROM gr_generic_ml_name_tl gmn
1237 WHERE gmn.item_code = p_item_code;
1238
1239 /* Item classifications */
1240
1241 CURSOR c_get_item_classn
1242 IS
1243 SELECT COUNT(*)
1244 FROM gr_item_classns ic
1245 WHERE ic.item_code = p_item_code;
1246
1247 /* Item Disclosures */
1248
1249 CURSOR c_get_item_disclosure
1250 IS
1251 SELECT COUNT(*)
1252 FROM gr_item_disclosures id
1253 WHERE id.item_code = p_item_code;
1254
1255 /* Item Document Details */
1256
1257 CURSOR c_get_item_doc_dtls
1258 IS
1259 SELECT COUNT(*)
1260 FROM gr_item_document_dtls idd
1261 WHERE idd.item_code = p_item_code;
1262
1263 /* Item Document Status */
1264
1265 CURSOR c_get_item_doc_status
1266 IS
1267 SELECT COUNT(*)
1268 FROM gr_item_doc_statuses ids
1269 WHERE ids.item_code = p_item_code;
1270
1271 /* Item Exposure */
1272
1273 CURSOR c_get_item_exposure
1274 IS
1275 SELECT COUNT(*)
1276 FROM gr_item_exposure ie
1277 WHERE ie.item_code = p_item_code;
1278
1279 /* Item Properties */
1280
1281 CURSOR c_get_item_properties
1282 IS
1283 SELECT COUNT(*)
1284 FROM gr_item_properties ip
1285 WHERE ip.item_code = p_item_code;
1286
1287 /* Item Right to Know */
1288
1289 CURSOR c_get_item_rtk
1290 IS
1291 SELECT COUNT(*)
1292 FROM gr_item_right_to_know irtk
1293 WHERE irtk.item_code = p_item_code;
1294
1295 /* Item Risk Phrases */
1296
1297 CURSOR c_get_item_risks
1298 IS
1299 SELECT COUNT(*)
1300 FROM gr_item_risk_phrases irp
1301 WHERE irp.item_code = p_item_code;
1302
1303 /* Item Safety Phrases */
1304
1305 CURSOR c_get_item_safety
1306 IS
1307 SELECT COUNT(*)
1308 FROM gr_item_safety_phrases isp
1309 WHERE isp.item_code = p_item_code;
1310
1311 /* Item Toxic */
1312
1313 CURSOR c_get_item_toxic
1314 IS
1315 SELECT COUNT(*)
1316 FROM gr_item_toxic it
1317 WHERE it.item_code = p_item_code;
1318
1319 /* Item Names */
1320
1321 CURSOR c_get_item_names
1322 IS
1323 SELECT COUNT(*)
1324 FROM gr_multilingual_name_tl mln
1325 WHERE mln.item_code = p_item_code;
1326
1327 /* Other names (synonyms) */
1328
1329 CURSOR c_get_other_names
1330 IS
1331 SELECT COUNT(*)
1332 FROM gr_other_names_tl onm
1333 WHERE onm.item_code = p_item_code;
1334
1335 BEGIN
1336
1337 /* Initialization Routine */
1338
1339 SAVEPOINT Check_Integrity;
1340 x_return_status := 'S';
1341 x_oracle_error := 0;
1342 x_msg_data := NULL;
1343
1344 /* Now read the cursors to make sure the item code isn't used. */
1345 /* Calculations Table */
1346
1347 l_record_count := 0;
1348 l_code_block := 'c_get_calculated';
1349 OPEN c_get_calculated;
1350 FETCH c_get_calculated INTO l_record_count;
1351 IF l_record_count <> 0 THEN
1352 l_return_status := 'E';
1353 l_msg_data := l_msg_data || 'gr_calculated, ';
1354 END IF;
1355 CLOSE c_get_calculated;
1356
1357 /* Dispatch History */
1358
1359 l_record_count := 0;
1360 l_code_block := 'c_get_disp_history';
1361 OPEN c_get_disp_history;
1362 FETCH c_get_disp_history INTO l_record_count;
1363 IF l_record_count <> 0 THEN
1364 l_return_status := 'E';
1365 l_msg_data := l_msg_data || 'gr_dispatch_histories, ';
1366 END IF;
1367 CLOSE c_get_disp_history;
1368
1369 /* Document Printing */
1370
1371 l_record_count := 0;
1372 l_code_block := 'c_get_doc_print';
1373 OPEN c_get_doc_print;
1374 FETCH c_get_doc_print INTO l_record_count;
1375 IF l_record_count <> 0 THEN
1376 l_return_status := 'E';
1377 l_msg_data := l_msg_data || 'gr_document_print, ';
1378 END IF;
1379 CLOSE c_get_doc_print;
1380
1381 /* European Information */
1382
1383 l_record_count := 0;
1384 l_code_block := 'c_get_emea';
1385 OPEN c_get_emea;
1386 FETCH c_get_emea INTO l_record_count;
1387 IF l_record_count <> 0 THEN
1388 l_return_status := 'E';
1389 l_msg_data := l_msg_data || 'gr_emea, ';
1390 END IF;
1391 CLOSE c_get_emea;
1392
1393 /* Generic Items */
1394
1395 l_record_count := 0;
1396 l_code_block := 'g_get_generics';
1397 OPEN c_get_generics;
1398 FETCH c_get_generics INTO l_record_count;
1399 IF l_record_count <> 0 THEN
1400 l_return_status := 'E';
1401 l_msg_data := l_msg_data || 'gr_generic_items_b, ';
1402 END IF;
1403 CLOSE c_get_generics;
1404
1405 /* Generic Item Names */
1406
1407 l_record_count := 0;
1408 l_code_block := 'c_get_generic_names ';
1409 OPEN c_get_generic_names;
1410 FETCH c_get_generic_names INTO l_record_count;
1411 IF l_record_count <> 0 THEN
1412 l_return_status := 'E';
1413 l_msg_data := l_msg_data || 'gr_generic_ml_name_tl, ';
1414 END IF;
1415 CLOSE c_get_generic_names;
1416
1417 /* Item Classifications */
1418
1419 l_record_count := 0;
1420 l_code_block := 'c_get_item_classn';
1421 OPEN c_get_item_classn;
1422 FETCH c_get_item_classn INTO l_record_count;
1423 IF l_record_count <> 0 THEN
1424 l_return_status := 'E';
1425 l_msg_data := l_msg_data || 'gr_item_classns, ';
1426 END IF;
1427 CLOSE c_get_item_classn;
1428
1429 /* Item Disclosures */
1430
1431 l_record_count := 0;
1432 l_code_block := 'c_get_item_disclosure';
1433 OPEN c_get_item_disclosure;
1434 FETCH c_get_item_disclosure INTO l_record_count;
1435 IF l_record_count <> 0 THEN
1436 l_return_status := 'E';
1437 l_msg_data := l_msg_data || 'gr_item_disclosures, ';
1438 END IF;
1439 CLOSE c_get_item_disclosure;
1440
1441 /* Item Document Details */
1442
1443 l_record_count := 0;
1444 l_code_block := 'c_get_item_doc_dtls';
1445 OPEN c_get_item_doc_dtls;
1446 FETCH c_get_item_doc_dtls INTO l_record_count;
1447 IF l_record_count <> 0 THEN
1448 l_return_status := 'E';
1449 l_msg_data := l_msg_data || 'gr_item_document_dtls, ';
1450 END IF;
1451 CLOSE c_get_item_doc_dtls;
1452
1453 /* Item Document Statuses */
1454
1455 l_record_count := 0;
1456 l_code_block := 'c_get_item_doc_status';
1457 OPEN c_get_item_doc_status;
1458 FETCH c_get_item_doc_status INTO l_record_count;
1459 IF l_record_count <> 0 THEN
1460 l_return_status := 'E';
1461 l_msg_data := l_msg_data || 'gr_item_doc_statuses, ';
1462 END IF;
1463 CLOSE c_get_item_doc_status;
1464
1465 /* Item Exposure */
1466
1467 l_record_count := 0;
1468 l_code_block := 'c_get_item_exposure';
1469 OPEN c_get_item_exposure;
1470 FETCH c_get_item_exposure INTO l_record_count;
1471 IF l_record_count <> 0 THEN
1472 l_return_status := 'E';
1473 l_msg_data := l_msg_data || 'gr_item_exposure, ';
1474 END IF;
1475 CLOSE c_get_item_exposure;
1476
1477 /* Item Properties */
1478
1479 l_record_count := 0;
1480 l_code_block := 'c_get_item_properties';
1481 OPEN c_get_item_properties;
1482 FETCH c_get_item_properties INTO l_record_count;
1483 IF l_record_count <> 0 THEN
1484 l_return_status := 'E';
1485 l_msg_data := l_msg_data || 'gr_item_properties, ';
1486 END IF;
1487 CLOSE c_get_item_properties;
1488
1489 /* Item Right to Know */
1490
1491 l_record_count := 0;
1492 l_code_block := 'c_get_item_rtk';
1493 OPEN c_get_item_rtk;
1494 FETCH c_get_item_rtk INTO l_record_count;
1495 IF l_record_count <> 0 THEN
1496 l_return_status := 'E';
1497 l_msg_data := l_msg_data || 'gr_item_right_to_know, ';
1498 END IF;
1499 CLOSE c_get_item_rtk;
1500
1501 /* Item Risk Phrases */
1502
1503 l_record_count := 0;
1504 l_code_block := 'c_get_item_risks';
1505 OPEN c_get_item_risks;
1506 FETCH c_get_item_risks INTO l_record_count;
1507 IF l_record_count <> 0 THEN
1508 l_return_status := 'E';
1509 l_msg_data := l_msg_data || 'gr_item_risk_phrases, ';
1510 END IF;
1511 CLOSE c_get_item_risks;
1512
1513 /* Item Safety Phrases */
1514
1515 l_record_count := 0;
1516 l_code_block := 'c_get_item_safety';
1517 OPEN c_get_item_safety;
1518 FETCH c_get_item_safety INTO l_record_count;
1519 IF l_record_count <> 0 THEN
1520 l_return_status := 'E';
1521 l_msg_data := l_msg_data || 'gr_item_safety_phrases, ';
1522 END IF;
1523 CLOSE c_get_item_safety;
1524
1525 /* Item Toxic */
1526
1527 l_record_count := 0;
1528 l_code_block := 'c_get_item_toxic';
1529 OPEN c_get_item_toxic;
1530 FETCH c_get_item_toxic INTO l_record_count;
1531 IF l_record_count <> 0 THEN
1532 l_return_status := 'E';
1533 l_msg_data := l_msg_data || 'gr_item_toxic, ';
1534 END IF;
1535 CLOSE c_get_item_toxic;
1536
1537 /* Multi Lingual Names */
1538
1539 l_record_count := 0;
1540 l_code_block := 'c_get_item_names';
1541 OPEN c_get_item_names;
1542 FETCH c_get_item_names INTO l_record_count;
1543 IF l_record_count <> 0 THEN
1544 l_return_status := 'E';
1545 l_msg_data := l_msg_data || 'gr_multilingual_name_tl, ';
1546 END IF;
1547 CLOSE c_get_item_names;
1548
1549 /* Other Names (Synonyms) */
1550
1551 l_record_count := 0;
1552 l_code_block := 'c_get_other_names';
1553 OPEN c_get_other_names;
1554 FETCH c_get_other_names INTO l_record_count;
1555 IF l_record_count <> 0 THEN
1556 l_return_status := 'E';
1557 l_msg_data := l_msg_data || 'gr_other_names, ';
1558 END IF;
1559 CLOSE c_get_other_names;
1560 /* Now sort out the error messaging */
1561
1562 IF l_return_status <> 'S' THEN
1563 RAISE INTEGRITY_ERROR;
1564 END IF;
1565
1566 EXCEPTION
1567
1568 WHEN INTEGRITY_ERROR THEN
1569 x_return_status := 'E';
1570 FND_MESSAGE.SET_NAME('GR',
1571 'GR_INTEGRITY_HEADER');
1572 FND_MESSAGE.SET_TOKEN('CODE',
1573 p_item_code,
1574 FALSE);
1575 FND_MESSAGE.SET_TOKEN('TABLES',
1576 SUBSTR(l_msg_data,1,LENGTH(l_msg_data)-1),
1577 FALSE);
1578 IF FND_API.To_Boolean(p_called_by_form) THEN
1579 APP_EXCEPTION.Raise_Exception;
1580 ELSE
1581 x_msg_data := FND_MESSAGE.Get;
1582 END IF;
1583 WHEN OTHERS THEN
1584 ROLLBACK TO SAVEPOINT Check_Integrity;
1585 x_return_status := 'U';
1586 x_oracle_error := APP_EXCEPTION.Get_Code;
1587 l_msg_data := APP_EXCEPTION.Get_Text;
1588 FND_MESSAGE.SET_NAME('GR',
1589 'GR_UNEXPECTED_ERROR');
1590 FND_MESSAGE.SET_TOKEN('TEXT',
1591 l_msg_data,
1592 FALSE);
1593 IF FND_API.To_Boolean(p_called_by_form) THEN
1594 APP_EXCEPTION.Raise_Exception;
1595 ELSE
1596 x_msg_data := FND_MESSAGE.Get;
1597 END IF;
1598
1599 END Check_Integrity;
1600
1601 PROCEDURE Check_Primary_Key
1602 /* p_item_code is the item code to check.
1603 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1604 ** x_rowid is the row id of the record if found.
1605 ** x_key_exists is 'T' is the record is found, 'F' if not.
1606 */
1607 (p_item_code IN VARCHAR2,
1608 p_called_by_form IN VARCHAR2,
1609 x_rowid OUT NOCOPY VARCHAR2,
1610 x_key_exists OUT NOCOPY VARCHAR2)
1611 IS
1612 /* Alphanumeric variables */
1613
1614 L_MSG_DATA VARCHAR2(80);
1615
1616 /* Declare any variables and the cursor */
1617
1618
1619 CURSOR c_get_item_rowid
1620 IS
1621 SELECT ig.rowid
1622 FROM gr_item_general ig
1623 WHERE ig.item_code = p_item_code;
1624 ItemRecord c_get_item_rowid%ROWTYPE;
1625
1626 BEGIN
1627
1628 l_msg_data := p_item_code;
1629 x_key_exists := 'F';
1630
1631 OPEN c_get_item_rowid;
1632 FETCH c_get_item_rowid INTO ItemRecord;
1633 IF c_get_item_rowid%FOUND THEN
1634 x_key_exists := 'T';
1635 x_rowid := ItemRecord.rowid;
1636 ELSE
1637 x_key_exists := 'F';
1638 END IF;
1639 CLOSE c_get_item_rowid;
1640
1641 EXCEPTION
1642
1643 WHEN Others THEN
1644 l_msg_data := APP_EXCEPTION.Get_Text;
1645 FND_MESSAGE.SET_NAME('GR',
1646 'GR_UNEXPECTED_ERROR');
1647 FND_MESSAGE.SET_TOKEN('TEXT',
1648 l_msg_data,
1649 FALSE);
1650 IF FND_API.To_Boolean(p_called_by_form) THEN
1651 APP_EXCEPTION.Raise_Exception;
1652 END IF;
1653
1654 END Check_Primary_Key;
1655
1656 END GR_ITEM_GENERAL_PKG;