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