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