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