DBA Data[Home] [Help]

PACKAGE BODY: APPS.GR_FIELD_NAME_MASKS_B_PKG

Source


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