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