[Home] [Help]
PACKAGE BODY: APPS.GR_LABEL_CLASSES_B_PKG
Source
1 PACKAGE BODY GR_LABEL_CLASSES_B_PKG AS
2 /*$Header: GRHILCBB.pls 115.2 2002/10/28 16:02:41 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_label_class_code IN VARCHAR2,
7 p_form_block IN VARCHAR2,
8 p_rollup_type IN NUMBER,
9 p_rollup_property IN VARCHAR2,
10 p_rollup_label IN VARCHAR2,
11 p_attribute_category IN VARCHAR2,
12 p_attribute1 IN VARCHAR2,
13 p_attribute2 IN VARCHAR2,
14 p_attribute3 IN VARCHAR2,
15 p_attribute4 IN VARCHAR2,
16 p_attribute5 IN VARCHAR2,
17 p_attribute6 IN VARCHAR2,
18 p_attribute7 IN VARCHAR2,
19 p_attribute8 IN VARCHAR2,
20 p_attribute9 IN VARCHAR2,
21 p_attribute10 IN VARCHAR2,
22 p_attribute11 IN VARCHAR2,
23 p_attribute12 IN VARCHAR2,
24 p_attribute13 IN VARCHAR2,
25 p_attribute14 IN VARCHAR2,
26 p_attribute15 IN VARCHAR2,
27 p_attribute16 IN VARCHAR2,
28 p_attribute17 IN VARCHAR2,
29 p_attribute18 IN VARCHAR2,
30 p_attribute19 IN VARCHAR2,
31 p_attribute20 IN VARCHAR2,
32 p_attribute21 IN VARCHAR2,
33 p_attribute22 IN VARCHAR2,
34 p_attribute23 IN VARCHAR2,
35 p_attribute24 IN VARCHAR2,
36 p_attribute25 IN VARCHAR2,
37 p_attribute26 IN VARCHAR2,
38 p_attribute27 IN VARCHAR2,
39 p_attribute28 IN VARCHAR2,
40 p_attribute29 IN VARCHAR2,
41 p_attribute30 IN VARCHAR2,
42 p_created_by IN NUMBER,
43 p_creation_date IN DATE,
44 p_last_updated_by IN NUMBER,
45 p_last_update_date IN DATE,
46 p_last_update_login IN NUMBER,
47 x_rowid OUT NOCOPY VARCHAR2,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_oracle_error OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2)
51 IS
52 /* Alpha Variables */
53
54 L_RETURN_STATUS VARCHAR2(1) := 'S';
55 L_KEY_EXISTS VARCHAR2(1);
56 L_MSG_DATA VARCHAR2(2000);
57 L_ROWID VARCHAR2(18);
58
59 /* Number Variables */
60
61 L_ORACLE_ERROR NUMBER;
62
63 /* Exceptions */
64
65 FOREIGN_KEY_ERROR EXCEPTION;
66 LABEL_EXISTS_ERROR EXCEPTION;
67 ROW_MISSING_ERROR EXCEPTION;
68
69 /* Declare cursors */
70
71 BEGIN
72
73 /* Initialization Routine */
74
75 SAVEPOINT Insert_Row;
76 x_return_status := 'S';
77 x_oracle_error := 0;
78 x_msg_data := NULL;
79
80 /* Now call the check foreign key procedure */
81
82 Check_Foreign_Keys
83 (p_label_class_code,
84 p_form_block,
85 p_rollup_type,
86 p_rollup_property,
87 p_rollup_label,
88 p_attribute_category,
89 p_attribute1,
90 p_attribute2,
91 p_attribute3,
92 p_attribute4,
93 p_attribute5,
94 p_attribute6,
95 p_attribute7,
96 p_attribute8,
97 p_attribute9,
98 p_attribute10,
99 p_attribute11,
100 p_attribute12,
101 p_attribute13,
102 p_attribute14,
103 p_attribute15,
104 p_attribute16,
105 p_attribute17,
106 p_attribute18,
107 p_attribute19,
108 p_attribute20,
109 p_attribute21,
110 p_attribute22,
111 p_attribute23,
112 p_attribute24,
113 p_attribute25,
114 p_attribute26,
115 p_attribute27,
116 p_attribute28,
117 p_attribute29,
118 p_attribute30,
119 l_return_status,
120 l_oracle_error,
121 l_msg_data);
122 IF l_return_status <> 'S' THEN
123 RAISE Foreign_Key_Error;
124 END IF;
125
126 /* Now check the primary key doesn't already exist */
127
128 Check_Primary_Key
129 (p_label_class_code,
130 'F',
131 l_rowid,
132 l_key_exists);
133
134 IF FND_API.To_Boolean(l_key_exists) THEN
135 RAISE Label_Exists_Error;
136 END IF;
137
138 INSERT INTO gr_label_classes_b
139 (label_class_code,
140 form_block,
141 rollup_type,
142 rollup_property,
143 rollup_label,
144 attribute_category,
145 attribute1,
146 attribute2,
147 attribute3,
148 attribute4,
149 attribute5,
150 attribute6,
151 attribute7,
152 attribute8,
153 attribute9,
154 attribute10,
155 attribute11,
156 attribute12,
157 attribute13,
158 attribute14,
159 attribute15,
160 attribute16,
161 attribute17,
162 attribute18,
163 attribute19,
164 attribute20,
165 attribute21,
166 attribute22,
167 attribute23,
168 attribute24,
169 attribute25,
170 attribute26,
171 attribute27,
172 attribute28,
173 attribute29,
174 attribute30,
175 created_by,
176 creation_date,
177 last_updated_by,
178 last_update_date,
179 last_update_login)
180 VALUES
181 (p_label_class_code,
182 p_form_block,
183 p_rollup_type,
184 p_rollup_property,
185 p_rollup_label,
186 p_attribute_category,
187 p_attribute1,
188 p_attribute2,
189 p_attribute3,
190 p_attribute4,
191 p_attribute5,
192 p_attribute6,
193 p_attribute7,
194 p_attribute8,
195 p_attribute9,
196 p_attribute10,
197 p_attribute11,
198 p_attribute12,
199 p_attribute13,
200 p_attribute14,
201 p_attribute15,
202 p_attribute16,
203 p_attribute17,
204 p_attribute18,
205 p_attribute19,
206 p_attribute20,
207 p_attribute21,
208 p_attribute22,
209 p_attribute23,
210 p_attribute24,
211 p_attribute25,
212 p_attribute26,
213 p_attribute27,
214 p_attribute28,
215 p_attribute29,
216 p_attribute30,
217 p_created_by,
218 p_creation_date,
219 p_last_updated_by,
220 p_last_update_date,
221 p_last_update_login);
222
223 /* Now get the row id of the inserted record */
224
225 Check_Primary_Key
226 (p_label_class_code,
227 'F',
228 l_rowid,
229 l_key_exists);
230
231 IF FND_API.To_Boolean(l_key_exists) THEN
232 x_rowid := l_rowid;
233 ELSE
234 RAISE Row_Missing_Error;
235 END IF;
236
237 /* Check the commit flag and if set, then commit the work. */
238
239 IF FND_API.To_Boolean(p_commit) THEN
240 COMMIT WORK;
241 END IF;
242
243 EXCEPTION
244
245 WHEN Foreign_Key_Error THEN
246 ROLLBACK TO SAVEPOINT Insert_Row;
247 x_return_status := l_return_status;
248 x_oracle_error := l_oracle_error;
249 FND_MESSAGE.SET_NAME('GR',
250 'GR_FOREIGN_KEY_ERROR');
251 FND_MESSAGE.SET_TOKEN('TEXT',
252 l_msg_data,
253 FALSE);
254 IF FND_API.To_Boolean(p_called_by_form) THEN
255 APP_EXCEPTION.Raise_Exception;
256 ELSE
257 x_msg_data := FND_MESSAGE.Get;
258 END IF;
259
260 WHEN Label_Exists_Error THEN
261 ROLLBACK TO SAVEPOINT Insert_Row;
262 x_return_status := 'E';
263 x_oracle_error := APP_EXCEPTION.Get_Code;
264 FND_MESSAGE.SET_NAME('GR',
265 'GR_RECORD_EXISTS');
266 FND_MESSAGE.SET_TOKEN('CODE',
267 p_label_class_code,
268 FALSE);
269 IF FND_API.To_Boolean(p_called_by_form) THEN
270 APP_EXCEPTION.Raise_Exception;
271 ELSE
272 x_msg_data := FND_MESSAGE.Get;
273 END IF;
274
275 WHEN Row_Missing_Error THEN
276 ROLLBACK TO SAVEPOINT Insert_Row;
277 x_return_status := 'E';
278 x_oracle_error := APP_EXCEPTION.Get_Code;
279 FND_MESSAGE.SET_NAME('GR',
280 'GR_NO_RECORD_INSERTED');
281 FND_MESSAGE.SET_TOKEN('CODE',
282 p_label_class_code,
283 FALSE);
284 IF FND_API.To_Boolean(p_called_by_form) THEN
285 APP_EXCEPTION.Raise_Exception;
286 ELSE
287 x_msg_data := FND_MESSAGE.Get;
288 END IF;
289
290 WHEN OTHERS THEN
291 ROLLBACK TO SAVEPOINT Insert_Row;
292 x_return_status := 'U';
293 x_oracle_error := APP_EXCEPTION.Get_Code;
294 l_msg_data := APP_EXCEPTION.Get_Text;
295 FND_MESSAGE.SET_NAME('GR',
296 'GR_UNEXPECTED_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 END Insert_Row;
307
308 PROCEDURE Update_Row
309 (p_commit IN VARCHAR2,
310 p_called_by_form IN VARCHAR2,
311 p_rowid IN VARCHAR2,
312 p_label_class_code IN VARCHAR2,
313 p_form_block IN VARCHAR2,
314 p_rollup_type IN NUMBER,
315 p_rollup_property IN VARCHAR2,
316 p_rollup_label IN VARCHAR2,
317 p_attribute_category IN VARCHAR2,
318 p_attribute1 IN VARCHAR2,
319 p_attribute2 IN VARCHAR2,
320 p_attribute3 IN VARCHAR2,
321 p_attribute4 IN VARCHAR2,
322 p_attribute5 IN VARCHAR2,
323 p_attribute6 IN VARCHAR2,
324 p_attribute7 IN VARCHAR2,
325 p_attribute8 IN VARCHAR2,
326 p_attribute9 IN VARCHAR2,
327 p_attribute10 IN VARCHAR2,
328 p_attribute11 IN VARCHAR2,
329 p_attribute12 IN VARCHAR2,
330 p_attribute13 IN VARCHAR2,
331 p_attribute14 IN VARCHAR2,
332 p_attribute15 IN VARCHAR2,
333 p_attribute16 IN VARCHAR2,
334 p_attribute17 IN VARCHAR2,
335 p_attribute18 IN VARCHAR2,
336 p_attribute19 IN VARCHAR2,
337 p_attribute20 IN VARCHAR2,
338 p_attribute21 IN VARCHAR2,
339 p_attribute22 IN VARCHAR2,
340 p_attribute23 IN VARCHAR2,
341 p_attribute24 IN VARCHAR2,
342 p_attribute25 IN VARCHAR2,
343 p_attribute26 IN VARCHAR2,
344 p_attribute27 IN VARCHAR2,
345 p_attribute28 IN VARCHAR2,
346 p_attribute29 IN VARCHAR2,
347 p_attribute30 IN VARCHAR2,
348 p_created_by IN NUMBER,
349 p_creation_date IN DATE,
350 p_last_updated_by IN NUMBER,
351 p_last_update_date IN DATE,
352 p_last_update_login IN NUMBER,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_oracle_error OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2)
356 IS
357
358 /* Alpha Variables */
359
360 L_RETURN_STATUS VARCHAR2(1) := 'S';
361 L_MSG_DATA VARCHAR2(2000);
362
363 /* Number Variables */
364
365 L_ORACLE_ERROR NUMBER;
366
367 /* Exceptions */
368
369 FOREIGN_KEY_ERROR EXCEPTION;
370 ROW_MISSING_ERROR EXCEPTION;
371
372 BEGIN
373
374 /* Initialization Routine */
375
376 SAVEPOINT Update_Row;
377 x_return_status := 'S';
378 x_oracle_error := 0;
379 x_msg_data := NULL;
380
381 /* Now call the check foreign key procedure */
382
383 Check_Foreign_Keys
384 (p_label_class_code,
385 p_form_block,
386 p_rollup_type,
387 p_rollup_property,
388 p_rollup_label,
389 p_attribute_category,
390 p_attribute1,
391 p_attribute2,
392 p_attribute3,
393 p_attribute4,
394 p_attribute5,
395 p_attribute6,
396 p_attribute7,
397 p_attribute8,
398 p_attribute9,
399 p_attribute10,
400 p_attribute11,
401 p_attribute12,
402 p_attribute13,
403 p_attribute14,
404 p_attribute15,
405 p_attribute16,
406 p_attribute17,
407 p_attribute18,
411 p_attribute22,
408 p_attribute19,
409 p_attribute20,
410 p_attribute21,
412 p_attribute23,
413 p_attribute24,
414 p_attribute25,
415 p_attribute26,
416 p_attribute27,
417 p_attribute28,
418 p_attribute29,
419 p_attribute30,
420 l_return_status,
421 l_oracle_error,
422 l_msg_data);
423
424 IF l_return_status <> 'S' THEN
425 RAISE Foreign_Key_Error;
426 ELSE
427 UPDATE gr_label_classes_b
428 SET label_class_code = p_label_class_code,
429 form_block = p_form_block,
430 rollup_type = p_rollup_type,
431 rollup_property = p_rollup_property,
432 rollup_label = p_rollup_label,
433 attribute_category = p_attribute_category,
434 attribute1 = p_attribute1,
435 attribute2 = p_attribute2,
436 attribute3 = p_attribute3,
437 attribute4 = p_attribute4,
438 attribute5 = p_attribute5,
439 attribute6 = p_attribute6,
440 attribute7 = p_attribute7,
441 attribute8 = p_attribute8,
442 attribute9 = p_attribute9,
443 attribute10 = p_attribute10,
444 attribute11 = p_attribute11,
445 attribute12 = p_attribute12,
446 attribute13 = p_attribute13,
447 attribute14 = p_attribute14,
448 attribute15 = p_attribute15,
449 attribute16 = p_attribute16,
450 attribute17 = p_attribute17,
451 attribute18 = p_attribute18,
452 attribute19 = p_attribute19,
453 attribute20 = p_attribute20,
454 attribute21 = p_attribute11,
455 attribute22 = p_attribute22,
456 attribute23 = p_attribute23,
457 attribute24 = p_attribute24,
458 attribute25 = p_attribute25,
459 attribute26 = p_attribute26,
460 attribute27 = p_attribute27,
461 attribute28 = p_attribute28,
462 attribute29 = p_attribute29,
463 attribute30 = p_attribute30,
464 created_by = p_created_by,
465 creation_date = p_creation_date,
466 last_updated_by = p_last_updated_by,
467 last_update_date = p_last_update_date,
468 last_update_login = p_last_update_login
469 WHERE rowid = p_rowid;
470 IF SQL%NOTFOUND THEN
471 RAISE Row_Missing_Error;
472 END IF;
473 END IF;
474
475 /* Check the commit flag and if set, then commit the work. */
476
477 IF FND_API.To_Boolean(p_commit) THEN
478 COMMIT WORK;
479 END IF;
480
481 EXCEPTION
482
483 WHEN Foreign_Key_Error THEN
484 ROLLBACK TO SAVEPOINT Update_Row;
485 x_return_status := l_return_status;
486 x_oracle_error := l_oracle_error;
487 FND_MESSAGE.SET_NAME('GR',
488 'GR_FOREIGN_KEY_ERROR');
489 FND_MESSAGE.SET_TOKEN('TEXT',
490 l_msg_data,
491 FALSE);
492 IF FND_API.To_Boolean(p_called_by_form) THEN
493 APP_EXCEPTION.Raise_Exception;
494 ELSE
495 x_msg_data := FND_MESSAGE.Get;
496 END IF;
497
498 WHEN Row_Missing_Error THEN
499 ROLLBACK TO SAVEPOINT Update_Row;
500 x_return_status := 'E';
501 x_oracle_error := APP_EXCEPTION.Get_Code;
502 FND_MESSAGE.SET_NAME('GR',
503 'GR_NO_RECORD_INSERTED');
504 FND_MESSAGE.SET_TOKEN('CODE',
505 p_label_class_code,
506 FALSE);
507 IF FND_API.To_Boolean(p_called_by_form) THEN
508 APP_EXCEPTION.Raise_Exception;
509 ELSE
510 x_msg_data := FND_MESSAGE.Get;
511 END IF;
512
513 WHEN OTHERS THEN
514 ROLLBACK TO SAVEPOINT Update_Row;
515 x_return_status := 'U';
516 x_oracle_error := APP_EXCEPTION.Get_Code;
517 l_msg_data := APP_EXCEPTION.Get_Text;
518 FND_MESSAGE.SET_NAME('GR',
522 FALSE);
519 'GR_UNEXPECTED_ERROR');
520 FND_MESSAGE.SET_TOKEN('TEXT',
521 l_msg_data,
523 IF FND_API.To_Boolean(p_called_by_form) THEN
524 APP_EXCEPTION.Raise_Exception;
525 ELSE
526 x_msg_data := FND_MESSAGE.Get;
527 END IF;
528
529 END Update_Row;
530
531 PROCEDURE Lock_Row
532 (p_commit IN VARCHAR2,
533 p_called_by_form IN VARCHAR2,
534 p_rowid IN VARCHAR2,
535 p_label_class_code IN VARCHAR2,
536 p_form_block IN VARCHAR2,
537 p_rollup_type IN NUMBER,
538 p_rollup_property IN VARCHAR2,
539 p_rollup_label IN VARCHAR2,
540 p_attribute_category IN VARCHAR2,
541 p_attribute1 IN VARCHAR2,
542 p_attribute2 IN VARCHAR2,
543 p_attribute3 IN VARCHAR2,
544 p_attribute4 IN VARCHAR2,
545 p_attribute5 IN VARCHAR2,
546 p_attribute6 IN VARCHAR2,
547 p_attribute7 IN VARCHAR2,
548 p_attribute8 IN VARCHAR2,
549 p_attribute9 IN VARCHAR2,
550 p_attribute10 IN VARCHAR2,
551 p_attribute11 IN VARCHAR2,
552 p_attribute12 IN VARCHAR2,
553 p_attribute13 IN VARCHAR2,
554 p_attribute14 IN VARCHAR2,
555 p_attribute15 IN VARCHAR2,
556 p_attribute16 IN VARCHAR2,
557 p_attribute17 IN VARCHAR2,
558 p_attribute18 IN VARCHAR2,
559 p_attribute19 IN VARCHAR2,
560 p_attribute20 IN VARCHAR2,
561 p_attribute21 IN VARCHAR2,
562 p_attribute22 IN VARCHAR2,
563 p_attribute23 IN VARCHAR2,
564 p_attribute24 IN VARCHAR2,
565 p_attribute25 IN VARCHAR2,
566 p_attribute26 IN VARCHAR2,
567 p_attribute27 IN VARCHAR2,
568 p_attribute28 IN VARCHAR2,
569 p_attribute29 IN VARCHAR2,
570 p_attribute30 IN VARCHAR2,
571 p_created_by IN NUMBER,
572 p_creation_date IN DATE,
573 p_last_updated_by IN NUMBER,
574 p_last_update_date IN DATE,
575 p_last_update_login IN NUMBER,
576 x_return_status OUT NOCOPY VARCHAR2,
577 x_oracle_error OUT NOCOPY NUMBER,
578 x_msg_data OUT NOCOPY VARCHAR2)
579 IS
580
581 /* Alpha Variables */
582
583 L_RETURN_STATUS VARCHAR2(1) := 'S';
584 L_MSG_DATA VARCHAR2(2000);
585
586 /* Number Variables */
587
588 L_ORACLE_ERROR NUMBER;
589
590 /* Exceptions */
591
592 NO_DATA_FOUND_ERROR EXCEPTION;
593 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
594 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
595
596 /* Define the cursors */
597
598 CURSOR c_lock_label_class
599 IS
600 SELECT *
601 FROM gr_label_classes_b
602 WHERE rowid = p_rowid
603 FOR UPDATE NOWAIT;
604 LockLabelClassRcd c_lock_label_class%ROWTYPE;
605
606 BEGIN
607
608 /* Initialization Routine */
609
610 SAVEPOINT Lock_Row;
611 x_return_status := 'S';
612 x_oracle_error := 0;
613 x_msg_data := NULL;
614
615 /* Now lock the record */
616
617 OPEN c_lock_label_class;
618 FETCH c_lock_label_class INTO LockLabelClassRcd;
619 IF c_lock_label_class%NOTFOUND THEN
620 CLOSE c_lock_label_class;
621 RAISE No_Data_Found_Error;
622 END IF;
623 CLOSE c_lock_label_class;
624
625 IF FND_API.To_Boolean(p_commit) THEN
626 COMMIT WORK;
627 END IF;
628
629 EXCEPTION
630
631 WHEN No_Data_Found_Error THEN
632 ROLLBACK TO SAVEPOINT Lock_Row;
633 x_return_status := 'E';
634 FND_MESSAGE.SET_NAME('GR',
635 'GR_RECORD_NOT_FOUND');
636 FND_MESSAGE.SET_TOKEN('CODE',
637 p_label_class_code,
638 FALSE);
639 IF FND_API.To_Boolean(p_called_by_form) THEN
640 APP_EXCEPTION.Raise_Exception;
641 ELSE
642 x_msg_data := FND_MESSAGE.Get;
643 END IF;
644
645 WHEN Row_Already_Locked_Error THEN
646 ROLLBACK TO SAVEPOINT Lock_Row;
650 'GR_ROW_IS_LOCKED');
647 x_return_status := 'E';
648 x_oracle_error := APP_EXCEPTION.Get_Code;
649 FND_MESSAGE.SET_NAME('GR',
651 IF FND_API.To_Boolean(p_called_by_form) THEN
652 APP_EXCEPTION.Raise_Exception;
653 ELSE
654 x_msg_data := FND_MESSAGE.Get;
655 END IF;
656
657 WHEN OTHERS THEN
658 ROLLBACK TO SAVEPOINT Lock_Row;
659 x_return_status := 'U';
660 x_oracle_error := APP_EXCEPTION.Get_Code;
661 l_msg_data := APP_EXCEPTION.Get_Text;
662 FND_MESSAGE.SET_NAME('GR',
663 'GR_UNEXPECTED_ERROR');
664 FND_MESSAGE.SET_TOKEN('TEXT',
665 l_msg_data,
666 FALSE);
667 IF FND_API.To_Boolean(p_called_by_form) THEN
668 APP_EXCEPTION.Raise_Exception;
669 ELSE
670 x_msg_data := FND_MESSAGE.Get;
671 END IF;
672
673 END Lock_Row;
674
675 PROCEDURE Delete_Row
676 (p_commit IN VARCHAR2,
677 p_called_by_form IN VARCHAR2,
678 p_rowid IN VARCHAR2,
679 p_label_class_code IN VARCHAR2,
680 p_form_block IN VARCHAR2,
681 p_rollup_type IN NUMBER,
682 p_rollup_property IN VARCHAR2,
683 p_rollup_label IN VARCHAR2,
684 p_attribute_category IN VARCHAR2,
685 p_attribute1 IN VARCHAR2,
686 p_attribute2 IN VARCHAR2,
687 p_attribute3 IN VARCHAR2,
688 p_attribute4 IN VARCHAR2,
689 p_attribute5 IN VARCHAR2,
690 p_attribute6 IN VARCHAR2,
691 p_attribute7 IN VARCHAR2,
692 p_attribute8 IN VARCHAR2,
693 p_attribute9 IN VARCHAR2,
694 p_attribute10 IN VARCHAR2,
695 p_attribute11 IN VARCHAR2,
696 p_attribute12 IN VARCHAR2,
697 p_attribute13 IN VARCHAR2,
698 p_attribute14 IN VARCHAR2,
699 p_attribute15 IN VARCHAR2,
700 p_attribute16 IN VARCHAR2,
701 p_attribute17 IN VARCHAR2,
702 p_attribute18 IN VARCHAR2,
703 p_attribute19 IN VARCHAR2,
704 p_attribute20 IN VARCHAR2,
705 p_attribute21 IN VARCHAR2,
706 p_attribute22 IN VARCHAR2,
707 p_attribute23 IN VARCHAR2,
708 p_attribute24 IN VARCHAR2,
709 p_attribute25 IN VARCHAR2,
710 p_attribute26 IN VARCHAR2,
711 p_attribute27 IN VARCHAR2,
712 p_attribute28 IN VARCHAR2,
713 p_attribute29 IN VARCHAR2,
714 p_attribute30 IN VARCHAR2,
715 p_created_by IN NUMBER,
716 p_creation_date IN DATE,
717 p_last_updated_by IN NUMBER,
718 p_last_update_date IN DATE,
719 p_last_update_login IN NUMBER,
720 x_return_status OUT NOCOPY VARCHAR2,
721 x_oracle_error OUT NOCOPY NUMBER,
722 x_msg_data OUT NOCOPY VARCHAR2)
723 IS
724
725 /* Alpha Variables */
726
727 L_RETURN_STATUS VARCHAR2(1) := 'S';
728 L_MSG_DATA VARCHAR2(2000);
729 L_CALLED_BY_FORM VARCHAR2(1);
730
731 /* Number Variables */
732
733 L_ORACLE_ERROR NUMBER;
734
735 /* Exceptions */
736
737 CHECK_INTEGRITY_ERROR EXCEPTION;
738 ROW_MISSING_ERROR EXCEPTION;
739 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
740
741 BEGIN
742
743 /* Initialization Routine */
744
745 SAVEPOINT Delete_Row;
746 x_return_status := 'S';
747 l_called_by_form := 'F';
748 x_oracle_error := 0;
749 x_msg_data := NULL;
750
751 /* Now call the check integrity procedure */
752
753 Check_Integrity
757 p_rollup_type,
754 (l_called_by_form,
755 p_label_class_code,
756 p_form_block,
758 p_rollup_property,
759 p_rollup_label,
760 p_attribute_category,
761 p_attribute1,
762 p_attribute2,
763 p_attribute3,
764 p_attribute4,
765 p_attribute5,
766 p_attribute6,
767 p_attribute7,
768 p_attribute8,
769 p_attribute9,
770 p_attribute10,
771 p_attribute11,
772 p_attribute12,
773 p_attribute13,
774 p_attribute14,
775 p_attribute15,
776 p_attribute16,
777 p_attribute17,
778 p_attribute18,
779 p_attribute19,
780 p_attribute20,
781 p_attribute21,
782 p_attribute22,
783 p_attribute23,
784 p_attribute24,
785 p_attribute25,
786 p_attribute26,
787 p_attribute27,
788 p_attribute28,
789 p_attribute29,
790 p_attribute30,
791 l_return_status,
792 l_oracle_error,
793 l_msg_data);
794
795 IF l_return_status <> 'S' THEN
796 RAISE Check_Integrity_Error;
797 END IF;
798
799 DELETE FROM gr_label_classes_b
800 WHERE rowid = p_rowid;
801
802 /* Check the commit flag and if set, then commit the work. */
803
804 IF FND_API.TO_Boolean(p_commit) THEN
805 COMMIT WORK;
806 END IF;
807
808 EXCEPTION
809
810 WHEN Check_Integrity_Error THEN
811 ROLLBACK TO SAVEPOINT Delete_Row;
812 x_return_status := l_return_status;
813 x_oracle_error := l_oracle_error;
814 IF FND_API.To_Boolean(p_called_by_form) THEN
815 APP_EXCEPTION.Raise_Exception;
816 ELSE
817 x_msg_data := FND_MESSAGE.Get;
818 END IF;
819
820 WHEN Row_Missing_Error THEN
821 ROLLBACK TO SAVEPOINT Delete_Row;
822 x_return_status := 'E';
823 x_oracle_error := APP_EXCEPTION.Get_Code;
824 FND_MESSAGE.SET_NAME('GR',
825 'GR_RECORD_NOT_FOUND');
826 FND_MESSAGE.SET_TOKEN('CODE',
827 p_label_class_code,
828 FALSE);
829 IF FND_API.To_Boolean(p_called_by_form) THEN
830 APP_EXCEPTION.Raise_Exception;
831 ELSE
832 x_msg_data := FND_MESSAGE.Get;
833 END IF;
834
835 WHEN OTHERS THEN
836 ROLLBACK TO SAVEPOINT Delete_Row;
837 x_return_status := 'U';
838 x_oracle_error := APP_EXCEPTION.Get_Code;
839 l_msg_data := APP_EXCEPTION.Get_Text;
840 FND_MESSAGE.SET_NAME('GR',
841 'GR_UNEXPECTED_ERROR');
842 FND_MESSAGE.SET_TOKEN('TEXT',
843 l_msg_data,
844 FALSE);
845 IF FND_API.To_Boolean(p_called_by_form) THEN
846 APP_EXCEPTION.Raise_Exception;
847 ELSE
848 x_msg_data := FND_MESSAGE.Get;
849 END IF;
850
851 END Delete_Row;
852
853 PROCEDURE Check_Foreign_Keys
854 (p_label_class_code IN VARCHAR2,
855 p_form_block IN VARCHAR2,
856 p_rollup_type IN NUMBER,
857 p_rollup_property IN VARCHAR2,
858 p_rollup_label IN VARCHAR2,
859 p_attribute_category IN VARCHAR2,
860 p_attribute1 IN VARCHAR2,
861 p_attribute2 IN VARCHAR2,
862 p_attribute3 IN VARCHAR2,
863 p_attribute4 IN VARCHAR2,
864 p_attribute5 IN VARCHAR2,
865 p_attribute6 IN VARCHAR2,
866 p_attribute7 IN VARCHAR2,
867 p_attribute8 IN VARCHAR2,
868 p_attribute9 IN VARCHAR2,
869 p_attribute10 IN VARCHAR2,
870 p_attribute11 IN VARCHAR2,
871 p_attribute12 IN VARCHAR2,
875 p_attribute16 IN VARCHAR2,
872 p_attribute13 IN VARCHAR2,
873 p_attribute14 IN VARCHAR2,
874 p_attribute15 IN VARCHAR2,
876 p_attribute17 IN VARCHAR2,
877 p_attribute18 IN VARCHAR2,
878 p_attribute19 IN VARCHAR2,
879 p_attribute20 IN VARCHAR2,
880 p_attribute21 IN VARCHAR2,
881 p_attribute22 IN VARCHAR2,
882 p_attribute23 IN VARCHAR2,
883 p_attribute24 IN VARCHAR2,
884 p_attribute25 IN VARCHAR2,
885 p_attribute26 IN VARCHAR2,
886 p_attribute27 IN VARCHAR2,
887 p_attribute28 IN VARCHAR2,
888 p_attribute29 IN VARCHAR2,
889 p_attribute30 IN VARCHAR2,
890 x_return_status OUT NOCOPY VARCHAR2,
891 x_oracle_error OUT NOCOPY NUMBER,
892 x_msg_data OUT NOCOPY VARCHAR2)
893 IS
894
895 /* Alpha Variables */
896
897 L_RETURN_STATUS VARCHAR2(1) := 'S';
898 L_MSG_DATA VARCHAR2(2000);
899 L_ROWID VARCHAR2(18);
900 L_KEY_EXISTS VARCHAR2(1);
901
902 /* Number Variables */
903
904 L_ORACLE_ERROR NUMBER;
905
906 /* Define the cursors */
907
908
909 BEGIN
910
911 /* Initialization Routine */
912
913 SAVEPOINT Check_Foreign_Keys;
914 x_return_status := 'S';
915 x_oracle_error := 0;
916 x_msg_data := NULL;
917
918 /* Rollup property id */
919 IF p_rollup_property IS NOT NULL THEN
920 l_key_exists := 'T';
921 GR_PROPERTIES_B_PKG.Check_Primary_Key
922 (p_rollup_property,
923 'F',
924 l_rowid,
925 l_key_exists);
926
927 IF NOT FND_API.To_Boolean(l_key_exists) THEN
928 x_return_status := 'E';
929 FND_MESSAGE.SET_NAME('GR',
930 'GR_RECORD_NOT_FOUND');
931 FND_MESSAGE.SET_TOKEN('CODE',
932 p_rollup_property,
933 FALSE);
934 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
935 END IF;
936 END IF;
937 /* Rollup Label */
938
939 IF p_rollup_label IS NOT NULL THEN
940 l_key_exists := 'T';
941 GR_LABELS_B_PKG.Check_Primary_Key
942 (p_rollup_label,
943 'F',
944 l_rowid,
945 l_key_exists);
946
947 IF NOT FND_API.To_Boolean(l_key_exists) THEN
948 x_return_status := 'E';
949 FND_MESSAGE.SET_NAME('GR',
950 'GR_RECORD_NOT_FOUND');
951 FND_MESSAGE.SET_TOKEN('CODE',
952 p_rollup_label,
953 FALSE);
954 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
955 END IF;
956 END IF;
957
958 IF x_return_status <> 'S' THEN
959 x_msg_data := l_msg_data;
960 END IF;
961
962 EXCEPTION
963
964 WHEN OTHERS THEN
965 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
966 x_return_status := 'U';
967 x_oracle_error := APP_EXCEPTION.Get_Code;
968 l_msg_data := APP_EXCEPTION.Get_Text;
969 FND_MESSAGE.SET_NAME('GR',
970 'GR_UNEXPECTED_ERROR');
971 FND_MESSAGE.SET_TOKEN('TEXT',
972 l_msg_data,
973 FALSE);
974 x_msg_data := FND_MESSAGE.Get;
975
976 END Check_Foreign_Keys;
977
978 PROCEDURE Check_Integrity
979 (p_called_by_form IN VARCHAR2,
980 p_label_class_code IN VARCHAR2,
981 p_form_block IN VARCHAR2,
982 p_rollup_type IN NUMBER,
983 p_rollup_property IN VARCHAR2,
984 p_rollup_label IN VARCHAR2,
985 p_attribute_category IN VARCHAR2,
986 p_attribute1 IN VARCHAR2,
987 p_attribute2 IN VARCHAR2,
988 p_attribute3 IN VARCHAR2,
989 p_attribute4 IN VARCHAR2,
990 p_attribute5 IN VARCHAR2,
991 p_attribute6 IN VARCHAR2,
995 p_attribute10 IN VARCHAR2,
992 p_attribute7 IN VARCHAR2,
993 p_attribute8 IN VARCHAR2,
994 p_attribute9 IN VARCHAR2,
996 p_attribute11 IN VARCHAR2,
997 p_attribute12 IN VARCHAR2,
998 p_attribute13 IN VARCHAR2,
999 p_attribute14 IN VARCHAR2,
1000 p_attribute15 IN VARCHAR2,
1001 p_attribute16 IN VARCHAR2,
1002 p_attribute17 IN VARCHAR2,
1003 p_attribute18 IN VARCHAR2,
1004 p_attribute19 IN VARCHAR2,
1005 p_attribute20 IN VARCHAR2,
1006 p_attribute21 IN VARCHAR2,
1007 p_attribute22 IN VARCHAR2,
1008 p_attribute23 IN VARCHAR2,
1009 p_attribute24 IN VARCHAR2,
1010 p_attribute25 IN VARCHAR2,
1011 p_attribute26 IN VARCHAR2,
1012 p_attribute27 IN VARCHAR2,
1013 p_attribute28 IN VARCHAR2,
1014 p_attribute29 IN VARCHAR2,
1015 p_attribute30 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_CODE_BLOCK VARCHAR2(100);
1026
1027 /* Number Variables */
1028
1029 L_ORACLE_ERROR NUMBER;
1030 L_RECORD_COUNT NUMBER;
1031
1032 /*
1033 ** Define the Cursors
1034 **
1035 ** Label codes
1036 */
1037
1038 CURSOR c_get_label_codes
1039 IS
1040 SELECT COUNT(*)
1041 FROM gr_labels_b lab
1042 WHERE lab.label_class_code = p_label_class_code;
1043
1044 BEGIN
1045
1046 /* Initialization Routine */
1047
1048 SAVEPOINT Check_Integrity;
1049 x_return_status := 'S';
1050 x_oracle_error := 0;
1051 x_msg_data := NULL;
1052
1053 FND_MESSAGE.SET_NAME('GR',
1054 'GR_INTEGRITY_HEADER');
1055 FND_MESSAGE.SET_TOKEN('CODE',
1056 p_label_class_code,
1057 FALSE);
1058 l_msg_data := FND_MESSAGE.Get;
1059 /*
1060 ** Now read the cursors to make sure the label class code isn't used.
1061 **
1062 ** Label codes
1063 */
1064 l_record_count := 0;
1065 l_code_block := 'c_get_label_codes';
1066 OPEN c_get_label_codes;
1067 FETCH c_get_label_codes INTO l_record_count;
1068 IF l_record_count <> 0 THEN
1069 l_return_status := 'E';
1070 l_msg_data := l_msg_data || 'gr_labels_b, ';
1071 END IF;
1072 CLOSE c_get_label_codes;
1073 /*
1074 ** Now sort out the error messaging
1075 */
1076 IF l_return_status <> 'S' THEN
1077 x_return_status := l_return_status;
1078 x_msg_data := l_msg_data;
1079 END IF;
1080
1081 EXCEPTION
1082
1083 WHEN OTHERS THEN
1084 ROLLBACK TO SAVEPOINT Check_Integrity;
1085 x_return_status := 'U';
1086 x_oracle_error := APP_EXCEPTION.Get_Code;
1087 l_msg_data := APP_EXCEPTION.Get_Text;
1088 FND_MESSAGE.SET_NAME('GR',
1089 'GR_UNEXPECTED_ERROR');
1090 FND_MESSAGE.SET_TOKEN('TEXT',
1091 l_msg_data,
1092 FALSE);
1093 IF FND_API.To_Boolean(p_called_by_form) THEN
1094 APP_EXCEPTION.Raise_Exception;
1095 ELSE
1096 x_msg_data := FND_MESSAGE.Get;
1097 END IF;
1098
1099 END Check_Integrity;
1100
1101 PROCEDURE Check_Primary_Key
1102 /* p_label_class_code is the code to check.
1103 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1104 ** x_rowid is the row id of the record if found.
1105 ** x_key_exists is 'T' is the record is found, 'F' if not.
1106 */
1107 (p_label_class_code IN VARCHAR2,
1108 p_called_by_form IN VARCHAR2,
1109 x_rowid OUT NOCOPY VARCHAR2,
1110 x_key_exists OUT NOCOPY VARCHAR2)
1111 IS
1112 /* Alphanumeric variables */
1113
1114 L_MSG_DATA VARCHAR2(80);
1115
1116 /* Declare any variables and the cursor */
1117
1118
1119 CURSOR c_get_label_class_rowid
1123 WHERE lcb.label_class_code = p_label_class_code;
1120 IS
1121 SELECT lcb.rowid
1122 FROM gr_label_classes_b lcb
1124 LabelClassRecord c_get_label_class_rowid%ROWTYPE;
1125
1126 BEGIN
1127
1128 x_key_exists := 'F';
1129 l_msg_data := p_label_class_code;
1130 OPEN c_get_label_class_rowid;
1131 FETCH c_get_label_class_rowid INTO LabelClassRecord;
1132 IF c_get_label_class_rowid%FOUND THEN
1133 x_key_exists := 'T';
1134 x_rowid := LabelClassRecord.rowid;
1135 ELSE
1136 x_key_exists := 'F';
1137 END IF;
1138 CLOSE c_get_label_class_rowid;
1139
1140 EXCEPTION
1141
1142 WHEN Others THEN
1143 l_msg_data := APP_EXCEPTION.Get_Text;
1144 FND_MESSAGE.SET_NAME('GR',
1145 'GR_UNEXPECTED_ERROR');
1146 FND_MESSAGE.SET_TOKEN('TEXT',
1147 l_msg_data,
1148 FALSE);
1149 IF FND_API.To_Boolean(p_called_by_form) THEN
1150 APP_EXCEPTION.Raise_Exception;
1151 END IF;
1152
1153 END Check_Primary_Key;
1154
1155 END GR_LABEL_CLASSES_B_PKG;