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