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