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