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