DBA Data[Home] [Help]

PACKAGE BODY: APPS.GR_RECIPIENT_INFO_PKG

Source


1 PACKAGE BODY GR_RECIPIENT_INFO_PKG AS
2 /*$Header: GRHIRECB.pls 115.6 2002/10/25 17:22:14 gkelly ship $*/
3 PROCEDURE Insert_Row
4 	   			 (p_commit IN VARCHAR2,
5 				  p_called_by_form IN VARCHAR2,
6 				  p_recipient_code IN VARCHAR2,
7 				  p_recipient_name IN VARCHAR2,
8 				  p_document_code IN VARCHAR2,
9 				  p_territory_code IN VARCHAR2,
10 				  p_print_recipient_product_code IN VARCHAR2,
11 				  p_update_address_flag IN VARCHAR2,
12 				  p_disclose_all_ingred_flag IN VARCHAR2,
13 				  p_address_to_print IN VARCHAR2,
14 				  p_document_print_frequency IN VARCHAR2,
15 				  p_disclosure_code IN VARCHAR2,
16 				  p_region_code IN VARCHAR2,
17 				  p_cover_letter_id IN VARCHAR2,
18 				  p_additional_documents_flag IN VARCHAR2,
19 				  p_additional_address_flag IN VARCHAR2,
20 				  p_shipping_address IN VARCHAR2,
21 				  p_invoice_address IN VARCHAR2,
22 				  p_attribute_category IN VARCHAR2,
23 				  p_attribute1 IN VARCHAR2,
24 				  p_attribute2 IN VARCHAR2,
25 				  p_attribute3 IN VARCHAR2,
26 				  p_attribute4 IN VARCHAR2,
27 				  p_attribute5 IN VARCHAR2,
28 				  p_attribute6 IN VARCHAR2,
29 				  p_attribute7 IN VARCHAR2,
30 				  p_attribute8 IN VARCHAR2,
31 				  p_attribute9 IN VARCHAR2,
32 				  p_attribute10 IN VARCHAR2,
33 				  p_attribute11 IN VARCHAR2,
34 				  p_attribute12 IN VARCHAR2,
35 				  p_attribute13 IN VARCHAR2,
36 				  p_attribute14 IN VARCHAR2,
37 				  p_attribute15 IN VARCHAR2,
38 				  p_attribute16 IN VARCHAR2,
39 				  p_attribute17 IN VARCHAR2,
40 				  p_attribute18 IN VARCHAR2,
41 				  p_attribute19 IN VARCHAR2,
42 				  p_attribute20 IN VARCHAR2,
43 				  p_attribute21 IN VARCHAR2,
44 				  p_attribute22 IN VARCHAR2,
45 				  p_attribute23 IN VARCHAR2,
46 				  p_attribute24 IN VARCHAR2,
47 				  p_attribute25 IN VARCHAR2,
48 				  p_attribute26 IN VARCHAR2,
49 				  p_attribute27 IN VARCHAR2,
50 				  p_attribute28 IN VARCHAR2,
51 				  p_attribute29 IN VARCHAR2,
52 				  p_attribute30 IN VARCHAR2,
53 				  p_created_by IN NUMBER,
54 				  p_creation_date IN DATE,
55 				  p_last_updated_by IN NUMBER,
56 				  p_last_update_date IN DATE,
57 				  p_last_update_login IN NUMBER,
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 
93    Check_Foreign_Keys
94 			     (p_recipient_code,
95 				  p_recipient_name,
96 				  p_document_code,
97 				  p_territory_code,
98 				  p_print_recipient_product_code,
99 				  p_update_address_flag,
100 				  p_disclose_all_ingred_flag,
101 				  p_address_to_print,
102 				  p_document_print_frequency,
103 				  p_disclosure_code,
104 				  p_region_code,
105 				  p_cover_letter_id,
106 				  p_additional_documents_flag,
107 				  p_additional_address_flag,
108 				  p_shipping_address,
109 				  p_invoice_address,
110 				  p_attribute_category,
111 				  p_attribute1,
112 				  p_attribute2,
113 				  p_attribute3,
114 				  p_attribute4,
115 				  p_attribute5,
116 				  p_attribute6,
117 				  p_attribute7,
118 				  p_attribute8,
119 				  p_attribute9,
120 				  p_attribute10,
121 				  p_attribute11,
122 				  p_attribute12,
123 				  p_attribute13,
124 				  p_attribute14,
125 				  p_attribute15,
126 				  p_attribute16,
127 				  p_attribute17,
128 				  p_attribute18,
129 				  p_attribute19,
130 				  p_attribute20,
131 				  p_attribute21,
132 				  p_attribute22,
133 				  p_attribute23,
134 				  p_attribute24,
135 				  p_attribute25,
136 				  p_attribute26,
137 				  p_attribute27,
138 				  p_attribute28,
139 				  p_attribute29,
140 				  p_attribute30,
141 				  l_return_status,
142 				  l_oracle_error,
143 				  l_msg_data);
144    IF l_return_status <> 'S' THEN
145       RAISE Foreign_Key_Error;
146    END IF;
147 
148 /* 	   Now check the primary key doesn't already exist */
149 
150    Check_Primary_Key
151    	   	   		 (p_recipient_code,
152 				  'F',
153 				  l_rowid,
154 				  l_key_exists);
155 
156    IF FND_API.To_Boolean(l_key_exists) THEN
157    	  RAISE Label_Exists_Error;
158    END IF;
159 
160    INSERT INTO gr_recipient_info
161    		  	     (recipient_code,
162 				  recipient_name,
163 				  document_code,
164 				  territory_code,
165 				  print_recipient_product_code,
166 				  update_address_flag,
167 				  disclose_all_ingredients_flag,
168 				  address_to_print,
169 				  document_print_frequency,
170 				  disclosure_code,
171 				  region_code,
172 				  cover_letter_id,
173 				  additional_documents_flag,
174 				  additional_address_flag,
175 				  shipping_address,
176 				  invoice_address,
177 				  attribute_category,
178 				  attribute1,
179 				  attribute2,
180 				  attribute3,
181 				  attribute4,
182 				  attribute5,
183 				  attribute6,
184 				  attribute7,
185 				  attribute8,
186 				  attribute9,
187 				  attribute10,
188 				  attribute11,
189 				  attribute12,
190 				  attribute13,
191 				  attribute14,
192 				  attribute15,
193 				  attribute16,
194 				  attribute17,
195 				  attribute18,
196 				  attribute19,
197 				  attribute20,
198 				  attribute21,
199 				  attribute22,
200 				  attribute23,
201 				  attribute24,
202 				  attribute25,
203 				  attribute26,
204 				  attribute27,
205 				  attribute28,
206 				  attribute29,
207 				  attribute30,
208 				  created_by,
209 				  creation_date,
210 				  last_updated_by,
211 				  last_update_date,
212 				  last_update_login)
213           VALUES
214 		         (p_recipient_code,
215 				  p_recipient_name,
216 				  p_document_code,
217 				  p_territory_code,
218 				  p_print_recipient_product_code,
219 				  p_update_address_flag,
220 				  p_disclose_all_ingred_flag,
221 				  p_address_to_print,
222 				  p_document_print_frequency,
223 				  p_disclosure_code,
224 				  p_region_code,
225 				  p_cover_letter_id,
226 				  p_additional_documents_flag,
227 				  p_additional_address_flag,
228 				  p_shipping_address,
229 				  p_invoice_address,
230 				  p_attribute_category,
231 				  p_attribute1,
232 				  p_attribute2,
233 				  p_attribute3,
234 				  p_attribute4,
235 				  p_attribute5,
236 				  p_attribute6,
237 				  p_attribute7,
238 				  p_attribute8,
239 				  p_attribute9,
240 				  p_attribute10,
241 				  p_attribute11,
242 				  p_attribute12,
243 				  p_attribute13,
244 				  p_attribute14,
245 				  p_attribute15,
246 				  p_attribute16,
247 				  p_attribute17,
248 				  p_attribute18,
249 				  p_attribute19,
250 				  p_attribute20,
251 				  p_attribute21,
252 				  p_attribute22,
253 				  p_attribute23,
254 				  p_attribute24,
255 				  p_attribute25,
256 				  p_attribute26,
257 				  p_attribute27,
258 				  p_attribute28,
259 				  p_attribute29,
260 				  p_attribute30,
261 				  p_created_by,
262 				  p_creation_date,
263 				  p_last_updated_by,
264 				  p_last_update_date,
265 				  p_last_update_login);
266 
267 /*   Now get the row id of the inserted record */
268 
269    Check_Primary_Key
270    	   	   		 (p_recipient_code,
271 				  'F',
272 				  l_rowid,
273 				  l_key_exists);
274 
275    IF FND_API.To_Boolean(l_key_exists) THEN
276    	  x_rowid := l_rowid;
277    ELSE
278    	  RAISE Row_Missing_Error;
279    END IF;
280 
281 /*   Check the commit flag and if set, then commit the work. */
282 
283    IF FND_API.To_Boolean(p_commit) THEN
284       COMMIT WORK;
285    END IF;
286 
287 EXCEPTION
288 
289    WHEN Foreign_Key_Error THEN
290       ROLLBACK TO SAVEPOINT Insert_Row;
291 	  x_return_status := l_return_status;
292 	  x_oracle_error := l_oracle_error;
293       FND_MESSAGE.SET_NAME('GR',
294                            'GR_FOREIGN_KEY_ERROR');
295       FND_MESSAGE.SET_TOKEN('TEXT',
296          		            l_msg_data,
297             			    FALSE);
298       IF FND_API.To_Boolean(p_called_by_form) THEN
299          APP_EXCEPTION.Raise_Exception;
300 	  ELSE
301 	     x_msg_data := FND_MESSAGE.Get;
302       END IF;
303 
304    WHEN Label_Exists_Error THEN
305       ROLLBACK TO SAVEPOINT Insert_Row;
306 	  x_return_status := 'E';
307 	  x_oracle_error := APP_EXCEPTION.Get_Code;
308       FND_MESSAGE.SET_NAME('GR',
309                            'GR_RECORD_EXISTS');
310       FND_MESSAGE.SET_TOKEN('CODE',
311          		            p_recipient_code,
312             			    FALSE);
313       IF FND_API.To_Boolean(p_called_by_form) THEN
314          APP_EXCEPTION.Raise_Exception;
315 	  ELSE
316 	     x_msg_data := FND_MESSAGE.Get;
317       END IF;
318 
319    WHEN Row_Missing_Error THEN
320       ROLLBACK TO SAVEPOINT Insert_Row;
321 	  x_return_status := 'E';
322 	  x_oracle_error := APP_EXCEPTION.Get_Code;
323       FND_MESSAGE.SET_NAME('GR',
324                            'GR_NO_RECORD_INSERTED');
325       FND_MESSAGE.SET_TOKEN('CODE',
326          		            p_recipient_code,
327             			    FALSE);
328       IF FND_API.To_Boolean(p_called_by_form) THEN
329          APP_EXCEPTION.Raise_Exception;
330 	  ELSE
331 	     x_msg_data := FND_MESSAGE.Get;
332       END IF;
333 
334    WHEN OTHERS THEN
335       ROLLBACK TO SAVEPOINT Insert_Row;
336 	  x_return_status := 'U';
337 	  x_oracle_error := APP_EXCEPTION.Get_Code;
338 	  l_msg_data := APP_EXCEPTION.Get_Text;
339 	  FND_MESSAGE.SET_NAME('GR',
340 	                       'GR_UNEXPECTED_ERROR');
341 	  FND_MESSAGE.SET_TOKEN('TEXT',
342 	                        l_msg_data,
343 	                        FALSE);
344       IF FND_API.To_Boolean(p_called_by_form) THEN
345          APP_EXCEPTION.Raise_Exception;
346 	  ELSE
347 	     x_msg_data := FND_MESSAGE.Get;
348       END IF;
349 
350 END Insert_Row;
351 
352 PROCEDURE Update_Row
353 	   			 (p_commit IN VARCHAR2,
354 				  p_called_by_form IN VARCHAR2,
355 				  p_rowid IN VARCHAR2,
356 				  p_recipient_code IN VARCHAR2,
357 				  p_recipient_name IN VARCHAR2,
358 				  p_document_code IN VARCHAR2,
359 				  p_territory_code IN VARCHAR2,
360 				  p_print_recipient_product_code IN VARCHAR2,
364 				  p_document_print_frequency IN VARCHAR2,
361 				  p_update_address_flag IN VARCHAR2,
362 				  p_disclose_all_ingred_flag IN VARCHAR2,
363 				  p_address_to_print IN VARCHAR2,
365 				  p_disclosure_code IN VARCHAR2,
366 				  p_region_code IN VARCHAR2,
367 				  p_cover_letter_id IN VARCHAR2,
368 				  p_additional_documents_flag IN VARCHAR2,
369 				  p_additional_address_flag IN VARCHAR2,
370 				  p_shipping_address IN VARCHAR2,
371 				  p_invoice_address IN VARCHAR2,
372 				  p_attribute_category IN VARCHAR2,
373 				  p_attribute1 IN VARCHAR2,
374 				  p_attribute2 IN VARCHAR2,
375 				  p_attribute3 IN VARCHAR2,
376 				  p_attribute4 IN VARCHAR2,
377 				  p_attribute5 IN VARCHAR2,
378 				  p_attribute6 IN VARCHAR2,
379 				  p_attribute7 IN VARCHAR2,
380 				  p_attribute8 IN VARCHAR2,
381 				  p_attribute9 IN VARCHAR2,
382 				  p_attribute10 IN VARCHAR2,
383 				  p_attribute11 IN VARCHAR2,
384 				  p_attribute12 IN VARCHAR2,
385 				  p_attribute13 IN VARCHAR2,
386 				  p_attribute14 IN VARCHAR2,
387 				  p_attribute15 IN VARCHAR2,
388 				  p_attribute16 IN VARCHAR2,
389 				  p_attribute17 IN VARCHAR2,
390 				  p_attribute18 IN VARCHAR2,
391 				  p_attribute19 IN VARCHAR2,
392 				  p_attribute20 IN VARCHAR2,
393 				  p_attribute21 IN VARCHAR2,
394 				  p_attribute22 IN VARCHAR2,
395 				  p_attribute23 IN VARCHAR2,
396 				  p_attribute24 IN VARCHAR2,
397 				  p_attribute25 IN VARCHAR2,
398 				  p_attribute26 IN VARCHAR2,
399 				  p_attribute27 IN VARCHAR2,
400 				  p_attribute28 IN VARCHAR2,
401 				  p_attribute29 IN VARCHAR2,
402 				  p_attribute30 IN VARCHAR2,
403 				  p_created_by IN NUMBER,
404 				  p_creation_date IN DATE,
405 				  p_last_updated_by IN NUMBER,
406 				  p_last_update_date IN DATE,
407 				  p_last_update_login IN NUMBER,
408 				  x_return_status OUT NOCOPY  VARCHAR2,
409 				  x_oracle_error OUT NOCOPY  NUMBER,
410 				  x_msg_data OUT NOCOPY  VARCHAR2)
411    IS
412 
413 /*   Alpha Variables */
414 
415 L_RETURN_STATUS	  VARCHAR2(1) := 'S';
416 L_MSG_DATA		  VARCHAR2(2000);
417 
418 /*   Number Variables */
419 
420 L_ORACLE_ERROR	  NUMBER;
421 
422 /*   Exceptions */
423 
424 FOREIGN_KEY_ERROR EXCEPTION;
425 ROW_MISSING_ERROR EXCEPTION;
426 
427 BEGIN
428 
429 /*       Initialization Routine */
430 
431    SAVEPOINT Update_Row;
432    x_return_status := 'S';
433    x_oracle_error := 0;
434    x_msg_data := NULL;
435 
436 /*	  Now call the check foreign key procedure */
437 
438    Check_Foreign_Keys
439 			     (p_recipient_code,
440 				  p_recipient_name,
441 				  p_document_code,
442 				  p_territory_code,
443 				  p_print_recipient_product_code,
444 				  p_update_address_flag,
445 				  p_disclose_all_ingred_flag,
446 				  p_address_to_print,
447 				  p_document_print_frequency,
448 				  p_disclosure_code,
449 				  p_region_code,
450 				  p_cover_letter_id,
451 				  p_additional_documents_flag,
452 				  p_additional_address_flag,
453 				  p_shipping_address,
454 				  p_invoice_address,
455 				  p_attribute_category,
456 				  p_attribute1,
457 				  p_attribute2,
458 				  p_attribute3,
459 				  p_attribute4,
460 				  p_attribute5,
461 				  p_attribute6,
462 				  p_attribute7,
463 				  p_attribute8,
464 				  p_attribute9,
465 				  p_attribute10,
466 				  p_attribute11,
467 				  p_attribute12,
468 				  p_attribute13,
469 				  p_attribute14,
470 				  p_attribute15,
471 				  p_attribute16,
472 				  p_attribute17,
473 				  p_attribute18,
474 				  p_attribute19,
478 				  p_attribute23,
475 				  p_attribute20,
476 				  p_attribute21,
477 				  p_attribute22,
479 				  p_attribute24,
480 				  p_attribute25,
481 				  p_attribute26,
482 				  p_attribute27,
483 				  p_attribute28,
484 				  p_attribute29,
485 				  p_attribute30,
486 				  l_return_status,
487 				  l_oracle_error,
488 				  l_msg_data);
489 
490    IF l_return_status <> 'S' THEN
491       RAISE Foreign_Key_Error;
492    ELSE
493       UPDATE gr_recipient_info
494 	  SET	 recipient_code 	 	 	 	 = p_recipient_code,
495 			 recipient_name	  				 = p_recipient_name,
496 			 document_code	  				 = p_document_code,
497 			 territory_code	  				 = p_territory_code,
498 			 print_recipient_product_code	 = p_print_recipient_product_code,
499 			 update_address_flag	  		 = p_update_address_flag,
500 			 disclose_all_ingredients_flag	 = p_disclose_all_ingred_flag,
501 			 address_to_print	  			 = p_address_to_print,
502 			 document_print_frequency	  	 = p_document_print_frequency,
503 			 disclosure_code	  			 = p_disclosure_code,
504 			 region_code	  				 = p_region_code,
505 			 cover_letter_id	  			 = p_cover_letter_id,
506 			 additional_documents_flag	  	 = p_additional_documents_flag,
507 			 additional_address_flag	     = p_additional_address_flag,
508 			 shipping_address	  			 = p_shipping_address,
509 			 invoice_address	  			 = p_invoice_address,
510 			 attribute_category				 = p_attribute_category,
511 			 attribute1						 = p_attribute1,
512 			 attribute2						 = p_attribute2,
513 			 attribute3						 = p_attribute3,
514 			 attribute4						 = p_attribute4,
515 			 attribute5						 = p_attribute5,
516 			 attribute6						 = p_attribute6,
517 			 attribute7						 = p_attribute7,
518 			 attribute8						 = p_attribute8,
519 			 attribute9						 = p_attribute9,
520 			 attribute10					 = p_attribute10,
521 			 attribute11					 = p_attribute11,
522 			 attribute12					 = p_attribute12,
523 			 attribute13					 = p_attribute13,
524 			 attribute14					 = p_attribute14,
525 			 attribute15					 = p_attribute15,
526 			 attribute16					 = p_attribute16,
527 			 attribute17					 = p_attribute17,
528 			 attribute18					 = p_attribute18,
529 			 attribute19					 = p_attribute19,
530 			 attribute20					 = p_attribute20,
531 			 attribute21					 = p_attribute11,
532 			 attribute22					 = p_attribute22,
533 			 attribute23					 = p_attribute23,
534 			 attribute24					 = p_attribute24,
535 			 attribute25					 = p_attribute25,
536 			 attribute26					 = p_attribute26,
537 			 attribute27					 = p_attribute27,
538 			 attribute28					 = p_attribute28,
539 			 attribute29					 = p_attribute29,
540 			 attribute30					 = p_attribute30,
541 			 created_by						 = p_created_by,
542 			 creation_date					 = p_creation_date,
543 			 last_updated_by				 = p_last_updated_by,
544 			 last_update_date				 = p_last_update_date,
545 			 last_update_login				 = p_last_update_login
546 	  WHERE  rowid = p_rowid;
547 	  IF SQL%NOTFOUND THEN
548 	     RAISE Row_Missing_Error;
549 	  END IF;
550    END IF;
551 
552 /*   Check the commit flag and if set, then commit the work. */
553 
554    IF FND_API.To_Boolean(p_commit) THEN
555       COMMIT WORK;
556    END IF;
557 
558 EXCEPTION
559 
560    WHEN Foreign_Key_Error THEN
561       ROLLBACK TO SAVEPOINT Update_Row;
562 	  x_return_status := l_return_status;
563 	  x_oracle_error := l_oracle_error;
564       FND_MESSAGE.SET_NAME('GR',
565                            'GR_FOREIGN_KEY_ERROR');
566       FND_MESSAGE.SET_TOKEN('TEXT',
567          		            l_msg_data,
568             			    FALSE);
569       IF FND_API.To_Boolean(p_called_by_form) THEN
570          APP_EXCEPTION.Raise_Exception;
571 	  ELSE
572 	     x_msg_data := FND_MESSAGE.Get;
573       END IF;
574 
575    WHEN Row_Missing_Error THEN
576       ROLLBACK TO SAVEPOINT Update_Row;
577 	  x_return_status := 'E';
578 	  x_oracle_error := APP_EXCEPTION.Get_Code;
579       FND_MESSAGE.SET_NAME('GR',
580                            'GR_NO_RECORD_INSERTED');
581       FND_MESSAGE.SET_TOKEN('CODE',
582          		            p_recipient_code,
583             			    FALSE);
584       IF FND_API.To_Boolean(p_called_by_form) THEN
585          APP_EXCEPTION.Raise_Exception;
586 	  ELSE
587 	     x_msg_data := FND_MESSAGE.Get;
588       END IF;
589 
590    WHEN OTHERS THEN
591       ROLLBACK TO SAVEPOINT Update_Row;
592 	  x_return_status := 'U';
593 	  x_oracle_error := APP_EXCEPTION.Get_Code;
594 	  l_msg_data := APP_EXCEPTION.Get_Text;
595 	  FND_MESSAGE.SET_NAME('GR',
596 	                       'GR_UNEXPECTED_ERROR');
597 	  FND_MESSAGE.SET_TOKEN('TEXT',
598 	                        l_msg_data,
599 	                        FALSE);
600       IF FND_API.To_Boolean(p_called_by_form) THEN
601          APP_EXCEPTION.Raise_Exception;
602 	  ELSE
603 	     x_msg_data := FND_MESSAGE.Get;
604       END IF;
605 
606 END Update_Row;
607 
608 PROCEDURE Lock_Row
609 	   			 (p_commit IN VARCHAR2,
610 				  p_called_by_form IN VARCHAR2,
611 				  p_rowid IN VARCHAR2,
612 				  p_recipient_code IN VARCHAR2,
613 				  p_recipient_name IN VARCHAR2,
614 				  p_document_code IN VARCHAR2,
615 				  p_territory_code IN VARCHAR2,
616 				  p_print_recipient_product_code IN VARCHAR2,
617 				  p_update_address_flag IN VARCHAR2,
618 				  p_disclose_all_ingred_flag IN VARCHAR2,
619 				  p_address_to_print IN VARCHAR2,
620 				  p_document_print_frequency IN VARCHAR2,
621 				  p_disclosure_code IN VARCHAR2,
622 				  p_region_code IN VARCHAR2,
623 				  p_cover_letter_id IN VARCHAR2,
624 				  p_additional_documents_flag IN VARCHAR2,
625 				  p_additional_address_flag IN VARCHAR2,
626 				  p_shipping_address IN VARCHAR2,
627 				  p_invoice_address IN VARCHAR2,
628 				  p_attribute_category IN VARCHAR2,
629 				  p_attribute1 IN VARCHAR2,
630 				  p_attribute2 IN VARCHAR2,
631 				  p_attribute3 IN VARCHAR2,
632 				  p_attribute4 IN VARCHAR2,
633 				  p_attribute5 IN VARCHAR2,
634 				  p_attribute6 IN VARCHAR2,
635 				  p_attribute7 IN VARCHAR2,
636 				  p_attribute8 IN VARCHAR2,
637 				  p_attribute9 IN VARCHAR2,
638 				  p_attribute10 IN VARCHAR2,
639 				  p_attribute11 IN VARCHAR2,
640 				  p_attribute12 IN VARCHAR2,
641 				  p_attribute13 IN VARCHAR2,
642 				  p_attribute14 IN VARCHAR2,
643 				  p_attribute15 IN VARCHAR2,
644 				  p_attribute16 IN VARCHAR2,
645 				  p_attribute17 IN VARCHAR2,
646 				  p_attribute18 IN VARCHAR2,
647 				  p_attribute19 IN VARCHAR2,
648 				  p_attribute20 IN VARCHAR2,
649 				  p_attribute21 IN VARCHAR2,
650 				  p_attribute22 IN VARCHAR2,
651 				  p_attribute23 IN VARCHAR2,
652 				  p_attribute24 IN VARCHAR2,
653 				  p_attribute25 IN VARCHAR2,
654 				  p_attribute26 IN VARCHAR2,
655 				  p_attribute27 IN VARCHAR2,
656 				  p_attribute28 IN VARCHAR2,
657 				  p_attribute29 IN VARCHAR2,
658 				  p_attribute30 IN VARCHAR2,
659 				  p_created_by IN NUMBER,
660 				  p_creation_date IN DATE,
661 				  p_last_updated_by IN NUMBER,
662 				  p_last_update_date IN DATE,
663 				  p_last_update_login IN NUMBER,
664 				  x_return_status OUT NOCOPY  VARCHAR2,
665 				  x_oracle_error OUT NOCOPY  NUMBER,
666 				  x_msg_data OUT NOCOPY  VARCHAR2)
667    IS
668 
669 /*  Alpha Variables */
670 
671 L_RETURN_STATUS	  VARCHAR2(1) := 'S';
672 L_MSG_DATA		  VARCHAR2(2000);
673 
674 /*  Number Variables */
675 
676 L_ORACLE_ERROR	  NUMBER;
677 
678 /*   Exceptions */
679 
680 NO_DATA_FOUND_ERROR 		EXCEPTION;
681 RECORD_CHANGED_ERROR	 	EXCEPTION;
682 
683 /*   Define the cursors */
684 
685 CURSOR c_lock_recipient
686  IS
687    SELECT	last_update_date
688    FROM		gr_recipient_info
689    WHERE	rowid = p_rowid
690    FOR UPDATE NOWAIT;
691 LockRecipientRcd	  c_lock_recipient%ROWTYPE;
692 
693 BEGIN
694 
695 /*      Initialization Routine */
696 
697    SAVEPOINT Lock_Row;
698    x_return_status := 'S';
699    x_oracle_error := 0;
700    x_msg_data := NULL;
701 
702 /*	   Now lock the record */
703 
704    OPEN c_lock_recipient;
705    FETCH c_lock_recipient INTO LockRecipientRcd;
706    IF c_lock_recipient%NOTFOUND THEN
707 	  CLOSE c_lock_recipient;
708 	  RAISE No_Data_Found_Error;
709    END IF;
710    CLOSE c_lock_recipient;
711 
712 
713    IF LockRecipientRcd.last_update_date <> p_last_update_date THEN
714      RAISE RECORD_CHANGED_ERROR;
715    END IF;
716 
717    IF FND_API.To_Boolean(p_commit) THEN
718       COMMIT WORK;
719    END IF;
720 
721 EXCEPTION
722 
723    WHEN No_Data_Found_Error THEN
724       ROLLBACK TO SAVEPOINT Lock_Row;
725 	  x_return_status := 'E';
726 	  FND_MESSAGE.SET_NAME('GR',
727 	                       'GR_RECORD_NOT_FOUND');
728 	  FND_MESSAGE.SET_TOKEN('CODE',
729 	                        p_recipient_code,
730 							FALSE);
731       IF FND_API.To_Boolean(p_called_by_form) THEN
732          APP_EXCEPTION.Raise_Exception;
733 	  ELSE
734 	     x_msg_data := FND_MESSAGE.Get;
735       END IF;
736 
737    WHEN RECORD_CHANGED_ERROR THEN
738      ROLLBACK TO SAVEPOINT Lock_Row;
739      X_return_status := 'E';
740      FND_MESSAGE.SET_NAME('FND',
741 	                  'FORM_RECORD_CHANGED');
745        x_msg_data := FND_MESSAGE.Get;
742      IF FND_API.To_Boolean(p_called_by_form) THEN
743        APP_EXCEPTION.Raise_Exception;
744      ELSE
746      END IF;
747    WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
748       ROLLBACK TO SAVEPOINT Lock_Row;
749 	  x_return_status := 'L';
750 	  x_oracle_error := APP_EXCEPTION.Get_Code;
751           IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
752             FND_MESSAGE.SET_NAME('GR',
753 	                       'GR_ROW_IS_LOCKED');
754             x_msg_data := FND_MESSAGE.Get;
755           END IF;
756 
757    WHEN OTHERS THEN
758       ROLLBACK TO SAVEPOINT Lock_Row;
759 	  x_return_status := 'U';
760 	  x_oracle_error := APP_EXCEPTION.Get_Code;
761 	  l_msg_data := APP_EXCEPTION.Get_Text;
762 	  FND_MESSAGE.SET_NAME('GR',
763 	                       'GR_UNEXPECTED_ERROR');
764 	  FND_MESSAGE.SET_TOKEN('TEXT',
765 	                        l_msg_data,
766 	                        FALSE);
767       IF FND_API.To_Boolean(p_called_by_form) THEN
768          APP_EXCEPTION.Raise_Exception;
769 	  ELSE
770 	     x_msg_data := FND_MESSAGE.Get;
771       END IF;
772 
773 END Lock_Row;
774 
775 PROCEDURE Delete_Row
776 	   			 (p_commit IN VARCHAR2,
777 				  p_called_by_form IN VARCHAR2,
778 				  p_rowid IN VARCHAR2,
779 				  p_recipient_code IN VARCHAR2,
780 				  p_recipient_name IN VARCHAR2,
781 				  p_document_code IN VARCHAR2,
782 				  p_territory_code IN VARCHAR2,
783 				  p_print_recipient_product_code IN VARCHAR2,
784 				  p_update_address_flag IN VARCHAR2,
785 				  p_disclose_all_ingred_flag IN VARCHAR2,
786 				  p_address_to_print IN VARCHAR2,
787 				  p_document_print_frequency IN VARCHAR2,
788 				  p_disclosure_code IN VARCHAR2,
789 				  p_region_code IN VARCHAR2,
790 				  p_cover_letter_id IN VARCHAR2,
791 				  p_additional_documents_flag IN VARCHAR2,
792 				  p_additional_address_flag IN VARCHAR2,
793 				  p_shipping_address IN VARCHAR2,
794 				  p_invoice_address IN VARCHAR2,
795 				  p_attribute_category IN VARCHAR2,
796 				  p_attribute1 IN VARCHAR2,
797 				  p_attribute2 IN VARCHAR2,
798 				  p_attribute3 IN VARCHAR2,
799 				  p_attribute4 IN VARCHAR2,
800 				  p_attribute5 IN VARCHAR2,
801 				  p_attribute6 IN VARCHAR2,
802 				  p_attribute7 IN VARCHAR2,
803 				  p_attribute8 IN VARCHAR2,
804 				  p_attribute9 IN VARCHAR2,
805 				  p_attribute10 IN VARCHAR2,
806 				  p_attribute11 IN VARCHAR2,
807 				  p_attribute12 IN VARCHAR2,
808 				  p_attribute13 IN VARCHAR2,
809 				  p_attribute14 IN VARCHAR2,
810 				  p_attribute15 IN VARCHAR2,
811 				  p_attribute16 IN VARCHAR2,
812 				  p_attribute17 IN VARCHAR2,
813 				  p_attribute18 IN VARCHAR2,
814 				  p_attribute19 IN VARCHAR2,
815 				  p_attribute20 IN VARCHAR2,
816 				  p_attribute21 IN VARCHAR2,
817 				  p_attribute22 IN VARCHAR2,
818 				  p_attribute23 IN VARCHAR2,
819 				  p_attribute24 IN VARCHAR2,
820 				  p_attribute25 IN VARCHAR2,
821 				  p_attribute26 IN VARCHAR2,
822 				  p_attribute27 IN VARCHAR2,
823 				  p_attribute28 IN VARCHAR2,
824 				  p_attribute29 IN VARCHAR2,
825 				  p_attribute30 IN VARCHAR2,
826 				  p_created_by IN NUMBER,
827 				  p_creation_date IN DATE,
828 				  p_last_updated_by IN NUMBER,
829 				  p_last_update_date IN DATE,
830 				  p_last_update_login IN NUMBER,
831 				  x_return_status OUT NOCOPY  VARCHAR2,
832 				  x_oracle_error OUT NOCOPY  NUMBER,
833 				  x_msg_data OUT NOCOPY  VARCHAR2)
834    IS
835 
836 /*   Alpha Variables */
837 
838 L_RETURN_STATUS	  VARCHAR2(1) := 'S';
839 L_MSG_DATA		  VARCHAR2(2000);
840 L_CALLED_BY_FORM  VARCHAR2(1);
841 
842 /*   Number Variables */
843 
844 L_ORACLE_ERROR	  NUMBER;
845 
846 /*   Exceptions */
847 
848 CHECK_INTEGRITY_ERROR EXCEPTION;
849 ROW_MISSING_ERROR	  EXCEPTION;
850 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
851 
852 BEGIN
853 
854 /*   Initialization Routine */
855 
856    SAVEPOINT Delete_Row;
857    x_return_status := 'S';
858    l_called_by_form := 'F';
859    x_oracle_error := 0;
860    x_msg_data := NULL;
861 
862 /*  Now call the check integrity procedure */
863 
864    Check_Integrity
865 			     (l_called_by_form,
866 				  p_recipient_code,
867 				  p_recipient_name,
868 				  p_document_code,
869 				  p_territory_code,
870 				  p_print_recipient_product_code,
871 				  p_update_address_flag,
872 				  p_disclose_all_ingred_flag,
873 				  p_address_to_print,
874 				  p_document_print_frequency,
875 				  p_disclosure_code,
876 				  p_region_code,
877 				  p_cover_letter_id,
878 				  p_additional_documents_flag,
879 				  p_additional_address_flag,
880 				  p_shipping_address,
881 				  p_invoice_address,
882 				  p_attribute_category,
883 				  p_attribute1,
884 				  p_attribute2,
885 				  p_attribute3,
886 				  p_attribute4,
887 				  p_attribute5,
888 				  p_attribute6,
889 				  p_attribute7,
890 				  p_attribute8,
891 				  p_attribute9,
892 				  p_attribute10,
893 				  p_attribute11,
894 				  p_attribute12,
895 				  p_attribute13,
896 				  p_attribute14,
897 				  p_attribute15,
898 				  p_attribute16,
899 				  p_attribute17,
900 				  p_attribute18,
901 				  p_attribute19,
902 				  p_attribute20,
903 				  p_attribute21,
904 				  p_attribute22,
905 				  p_attribute23,
906 				  p_attribute24,
907 				  p_attribute25,
908 				  p_attribute26,
909 				  p_attribute27,
910 				  p_attribute28,
914 				  l_oracle_error,
911 				  p_attribute29,
912 				  p_attribute30,
913 				  l_return_status,
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_recipient_info
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_recipient_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 := APP_EXCEPTION.Get_Code;
961 	  l_msg_data := APP_EXCEPTION.Get_Text;
962 	  l_msg_data := APP_EXCEPTION.Get_Text;
963 	  FND_MESSAGE.SET_NAME('GR',
964 	                       'GR_UNEXPECTED_ERROR');
965 	  FND_MESSAGE.SET_TOKEN('TEXT',
966 	                        l_msg_data,
967 	                        FALSE);
968       IF FND_API.To_Boolean(p_called_by_form) THEN
969          APP_EXCEPTION.Raise_Exception;
970 	  ELSE
971 	     x_msg_data := FND_MESSAGE.Get;
972       END IF;
973 
974 END Delete_Row;
975 
976 PROCEDURE Check_Foreign_Keys
977 	   			 (p_recipient_code IN VARCHAR2,
978 				  p_recipient_name IN VARCHAR2,
979 				  p_document_code IN VARCHAR2,
980 				  p_territory_code IN VARCHAR2,
981 				  p_print_recipient_product_code IN VARCHAR2,
982 				  p_update_address_flag IN VARCHAR2,
983 				  p_disclose_all_ingred_flag IN VARCHAR2,
984 				  p_address_to_print IN VARCHAR2,
985 				  p_document_print_frequency IN VARCHAR2,
986 				  p_disclosure_code IN VARCHAR2,
987 				  p_region_code IN VARCHAR2,
988 				  p_cover_letter_id IN VARCHAR2,
989 				  p_additional_documents_flag IN VARCHAR2,
990 				  p_additional_address_flag IN VARCHAR2,
991 				  p_shipping_address IN VARCHAR2,
992 				  p_invoice_address IN VARCHAR2,
993 				  p_attribute_category IN VARCHAR2,
994 				  p_attribute1 IN VARCHAR2,
995 				  p_attribute2 IN VARCHAR2,
996 				  p_attribute3 IN VARCHAR2,
997 				  p_attribute4 IN VARCHAR2,
998 				  p_attribute5 IN VARCHAR2,
999 				  p_attribute6 IN VARCHAR2,
1000 				  p_attribute7 IN VARCHAR2,
1001 				  p_attribute8 IN VARCHAR2,
1002 				  p_attribute9 IN VARCHAR2,
1003 				  p_attribute10 IN VARCHAR2,
1004 				  p_attribute11 IN VARCHAR2,
1005 				  p_attribute12 IN VARCHAR2,
1006 				  p_attribute13 IN VARCHAR2,
1007 				  p_attribute14 IN VARCHAR2,
1008 				  p_attribute15 IN VARCHAR2,
1009 				  p_attribute16 IN VARCHAR2,
1010 				  p_attribute17 IN VARCHAR2,
1011 				  p_attribute18 IN VARCHAR2,
1012 				  p_attribute19 IN VARCHAR2,
1013 				  p_attribute20 IN VARCHAR2,
1014 				  p_attribute21 IN VARCHAR2,
1015 				  p_attribute22 IN VARCHAR2,
1016 				  p_attribute23 IN VARCHAR2,
1017 				  p_attribute24 IN VARCHAR2,
1018 				  p_attribute25 IN VARCHAR2,
1019 				  p_attribute26 IN VARCHAR2,
1020 				  p_attribute27 IN VARCHAR2,
1021 				  p_attribute28 IN VARCHAR2,
1022 				  p_attribute29 IN VARCHAR2,
1023 				  p_attribute30 IN VARCHAR2,
1024 				  x_return_status OUT NOCOPY  VARCHAR2,
1025 				  x_oracle_error OUT NOCOPY  NUMBER,
1026 				  x_msg_data OUT NOCOPY  VARCHAR2)
1027    IS
1028 
1029 /*   Alpha Variables */
1030 
1031 L_RETURN_STATUS	  VARCHAR2(1) := 'S';
1032 L_MSG_DATA		  VARCHAR2(2000);
1033 L_ROWID			  VARCHAR2(18);
1034 L_KEY_EXISTS	  VARCHAR2(1);
1035 
1036 /*   Number Variables */
1037 
1038 L_ORACLE_ERROR	  NUMBER;
1039 
1040 /*   Define the cursors */
1041 
1042 CURSOR c_get_disc_code
1043  IS
1044    SELECT	dc.disclosure_code
1045    FROM		gr_disclosures dc
1046    WHERE	dc.disclosure_code = p_disclosure_code;
1047 DiscRecord			c_get_disc_code%ROWTYPE;
1048 
1049 CURSOR c_get_territory
1050  IS
1051    SELECT	ter.territory_code
1052    FROM		fnd_territories ter
1053    WHERE	ter.territory_code = p_territory_code;
1054 TerrRecord			c_get_territory%ROWTYPE;
1055 
1056 BEGIN
1057 
1058 /*   Initialization Routine */
1059 
1060    SAVEPOINT Check_Foreign_Keys;
1061    x_return_status := 'S';
1062    x_oracle_error := 0;
1063    x_msg_data := NULL;
1064 
1065 /*		Document Code */
1066    IF p_document_code IS NOT NULL THEN
1067      l_key_exists := 'T';
1068      GR_DOCUMENT_CODES_PKG.Check_Primary_Key
1069 					(p_document_code,
1070 					 'F',
1071 					 l_rowid,
1072 					 l_key_exists);
1073 
1074      IF NOT FND_API.To_Boolean(l_key_exists) THEN
1075         x_return_status := 'E';
1076         FND_MESSAGE.SET_NAME('GR',
1077                            'GR_RECORD_NOT_FOUND');
1078         FND_MESSAGE.SET_TOKEN('CODE',
1079                             p_document_code,
1083    END IF;
1080    	    			   	    FALSE);
1081 	  l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1082      END IF;
1084 /*		Region Code */
1085 
1086    IF p_region_code IS NOT NULL THEN
1087       l_key_exists := 'T';
1088       GR_REGIONS_PKG.Check_Primary_Key
1089 					(p_region_code,
1090 					 'F',
1091 					 l_rowid,
1092 					 l_key_exists);
1093 
1094       IF NOT FND_API.To_Boolean(l_key_exists) THEN
1095          x_return_status := 'E';
1096          FND_MESSAGE.SET_NAME('GR',
1097                               'GR_RECORD_NOT_FOUND');
1098          FND_MESSAGE.SET_TOKEN('CODE',
1099                                p_region_code,
1100    	    			   	       FALSE);
1101 	     l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1102 	  END IF;
1103    END IF;
1104 
1105 /*   Disclosure Code */
1106 
1107    IF p_disclosure_code IS NOT NULL THEN
1108       OPEN c_get_disc_code;
1109       FETCH c_get_disc_code INTO DiscRecord;
1110       IF c_get_disc_code%NOTFOUND THEN
1111          x_return_status := 'E';
1112 	     FND_MESSAGE.SET_NAME('GR',
1113 	   					      'GR_RECORD_NOT_FOUND');
1114 	     FND_MESSAGE.SET_TOKEN('CODE',
1115 						       p_disclosure_code,
1116 						       FALSE);
1117 	     l_msg_data := l_msg_data || ' ' || FND_MESSAGE.Get;
1118       END IF;
1119       CLOSE c_get_disc_code;
1120    END IF;
1121 
1122 /*   Territory Code */
1123 
1124    OPEN c_get_territory;
1125    FETCH c_get_territory INTO TerrRecord;
1126    IF c_get_territory%NOTFOUND THEN
1127       x_return_status := 'E';
1128 	  FND_MESSAGE.SET_NAME('GR',
1129 	   					   'GR_RECORD_NOT_FOUND');
1130 	  FND_MESSAGE.SET_TOKEN('CODE',
1131 						    p_territory_code,
1132 						    FALSE);
1133 	  l_msg_data := l_msg_data || ' ' || FND_MESSAGE.Get;
1134    END IF;
1135    CLOSE c_get_territory;
1136 
1137    IF x_return_status <> 'S' THEN
1138       x_msg_data := l_msg_data;
1139    END IF;
1140 
1141 EXCEPTION
1142 
1143    WHEN OTHERS THEN
1144       ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1145 	  x_return_status := 'U';
1146 	  x_oracle_error := APP_EXCEPTION.Get_Code;
1147 	  l_msg_data := APP_EXCEPTION.Get_Text;
1148 	  FND_MESSAGE.SET_NAME('GR',
1149 	                       'GR_UNEXPECTED_ERROR');
1150 	  FND_MESSAGE.SET_TOKEN('TEXT',
1151 	                        l_msg_data,
1152 	                        FALSE);
1153 	  x_msg_data := FND_MESSAGE.Get;
1154 
1155 END Check_Foreign_Keys;
1156 
1157 PROCEDURE Check_Integrity
1158 	   			 (p_called_by_form IN VARCHAR2,
1159 				  p_recipient_code IN VARCHAR2,
1160 				  p_recipient_name IN VARCHAR2,
1161 				  p_document_code IN VARCHAR2,
1162 				  p_territory_code IN VARCHAR2,
1163 				  p_print_recipient_product_code IN VARCHAR2,
1164 				  p_update_address_flag IN VARCHAR2,
1165 				  p_disclose_all_ingred_flag IN VARCHAR2,
1166 				  p_address_to_print IN VARCHAR2,
1167 				  p_document_print_frequency IN VARCHAR2,
1168 				  p_disclosure_code IN VARCHAR2,
1169 				  p_region_code IN VARCHAR2,
1170 				  p_cover_letter_id IN VARCHAR2,
1171 				  p_additional_documents_flag IN VARCHAR2,
1172 				  p_additional_address_flag IN VARCHAR2,
1173 				  p_shipping_address IN VARCHAR2,
1174 				  p_invoice_address IN VARCHAR2,
1175 				  p_attribute_category IN VARCHAR2,
1176 				  p_attribute1 IN VARCHAR2,
1177 				  p_attribute2 IN VARCHAR2,
1178 				  p_attribute3 IN VARCHAR2,
1179 				  p_attribute4 IN VARCHAR2,
1180 				  p_attribute5 IN VARCHAR2,
1181 				  p_attribute6 IN VARCHAR2,
1182 				  p_attribute7 IN VARCHAR2,
1183 				  p_attribute8 IN VARCHAR2,
1184 				  p_attribute9 IN VARCHAR2,
1185 				  p_attribute10 IN VARCHAR2,
1186 				  p_attribute11 IN VARCHAR2,
1187 				  p_attribute12 IN VARCHAR2,
1188 				  p_attribute13 IN VARCHAR2,
1189 				  p_attribute14 IN VARCHAR2,
1190 				  p_attribute15 IN VARCHAR2,
1191 				  p_attribute16 IN VARCHAR2,
1192 				  p_attribute17 IN VARCHAR2,
1193 				  p_attribute18 IN VARCHAR2,
1194 				  p_attribute19 IN VARCHAR2,
1195 				  p_attribute20 IN VARCHAR2,
1196 				  p_attribute21 IN VARCHAR2,
1197 				  p_attribute22 IN VARCHAR2,
1198 				  p_attribute23 IN VARCHAR2,
1199 				  p_attribute24 IN VARCHAR2,
1200 				  p_attribute25 IN VARCHAR2,
1201 				  p_attribute26 IN VARCHAR2,
1202 				  p_attribute27 IN VARCHAR2,
1203 				  p_attribute28 IN VARCHAR2,
1204 				  p_attribute29 IN VARCHAR2,
1205 				  p_attribute30 IN VARCHAR2,
1206 				  x_return_status OUT NOCOPY  VARCHAR2,
1207 				  x_oracle_error OUT NOCOPY  NUMBER,
1208 				  x_msg_data OUT NOCOPY  VARCHAR2)
1209    IS
1210 
1211 /*   Alpha Variables */
1212 
1213 L_RETURN_STATUS	  VARCHAR2(1) := 'S';
1214 L_MSG_DATA		  VARCHAR2(2000);
1215 L_CODE_BLOCK	  VARCHAR2(100);
1216 
1217 /*   Number Variables */
1218 
1219 L_ORACLE_ERROR	  NUMBER;
1220 L_RECORD_COUNT	  NUMBER;
1221 
1222 /*	 Define the Cursors */
1223 /* 	 Dispatch Histories */
1224 
1225 CURSOR c_get_disp_history
1226  IS
1227    SELECT	COUNT(*)
1228    FROM		gr_dispatch_histories
1229    WHERE	recipient_code = p_recipient_code;
1230 
1231 BEGIN
1232 
1233 /*     Initialization Routine */
1234 
1235    SAVEPOINT Check_Integrity;
1236    x_return_status := 'S';
1237    x_oracle_error := 0;
1238    x_msg_data := NULL;
1239 
1240    FND_MESSAGE.SET_NAME('GR',
1241                         'GR_INTEGRITY_HEADER');
1242    FND_MESSAGE.SET_TOKEN('CODE',
1243                          p_recipient_code,
1244 						 FALSE);
1245    l_msg_data := FND_MESSAGE.Get;
1246 
1247 /* 	Now read the cursors to make sure the recipient code isn't used. */
1248 
1249 /*  Dispatch Histories */
1253    OPEN c_get_disp_history;
1250 
1251    l_record_count := 0;
1252    l_code_block := 'c_get_disp_history';
1254    FETCH c_get_disp_history INTO l_record_count;
1255    IF l_record_count <> 0 THEN
1256       l_return_status := 'E';
1257 	  l_msg_data := l_msg_data || 'gr_dispatch_history, ';
1258    END IF;
1259    CLOSE c_get_disp_history;
1260 
1261 /*	 Now sort out the error messaging */
1262 
1263    IF l_return_status <> 'S' THEN
1264       x_return_status := l_return_status;
1265 	  x_msg_data := l_msg_data;
1266    END IF;
1267 
1268 EXCEPTION
1269 
1270    WHEN OTHERS THEN
1271       ROLLBACK TO SAVEPOINT Check_Integrity;
1272 	  x_return_status := 'U';
1273 	  x_oracle_error := APP_EXCEPTION.Get_Code;
1274 	  l_msg_data := APP_EXCEPTION.Get_Text;
1275 	  FND_MESSAGE.SET_NAME('GR',
1276 	                       'GR_UNEXPECTED_ERROR');
1277 	  FND_MESSAGE.SET_TOKEN('TEXT',
1278 	                        l_msg_data,
1279 	                        FALSE);
1280       IF FND_API.To_Boolean(p_called_by_form) THEN
1281          APP_EXCEPTION.Raise_Exception;
1282 	  ELSE
1283 	     x_msg_data := FND_MESSAGE.Get;
1284       END IF;
1285 
1286 END Check_Integrity;
1287 
1288 PROCEDURE Check_Primary_Key
1289 /*		  p_recipient_code is the recipient code to check.
1290 **		  p_called_by_form is 'T' if called by a form or 'F' if not.
1291 **		  x_rowid is the row id of the record if found.
1292 **		  x_key_exists is 'T' is the record is found, 'F' if not.
1293 */
1294 		  		 	(p_recipient_code IN VARCHAR2,
1295 					 p_called_by_form IN VARCHAR2,
1296 					 x_rowid OUT NOCOPY  VARCHAR2,
1297 					 x_key_exists OUT NOCOPY  VARCHAR2)
1298   IS
1299 /*	Alphanumeric variables	 */
1300 
1301 L_MSG_DATA VARCHAR2(80);
1302 
1303 /*		Declare any variables and the cursor */
1304 
1305 
1306 CURSOR c_get_recipient_rowid
1307  IS
1308    SELECT rec.rowid
1309    FROM	  gr_recipient_info rec
1310    WHERE  rec.recipient_code = p_recipient_code;
1311 RecipientRecord			   c_get_recipient_rowid%ROWTYPE;
1312 
1313 BEGIN
1314 
1315    x_key_exists := 'F';
1316    l_msg_data := p_recipient_code;
1317    OPEN c_get_recipient_rowid;
1318    FETCH c_get_recipient_rowid INTO RecipientRecord;
1319    IF c_get_recipient_rowid%FOUND THEN
1320       x_key_exists := 'T';
1321 	  x_rowid := RecipientRecord.rowid;
1322    ELSE
1323       x_key_exists := 'F';
1324    END IF;
1325    CLOSE c_get_recipient_rowid;
1326 
1327 EXCEPTION
1328 
1329 	WHEN Others THEN
1330 	  l_msg_data := APP_EXCEPTION.Get_Text;
1331 	  FND_MESSAGE.SET_NAME('GR',
1332 	                       'GR_UNEXPECTED_ERROR');
1333 	  FND_MESSAGE.SET_TOKEN('TEXT',
1334 	                        l_msg_data,
1335 	                        FALSE);
1336       IF FND_API.To_Boolean(p_called_by_form) THEN
1337 	     APP_EXCEPTION.Raise_Exception;
1338 	  END IF;
1339 
1340 END Check_Primary_Key;
1341 
1342 END GR_RECIPIENT_INFO_PKG;