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