[Home] [Help]
PACKAGE BODY: APPS.GR_DOCUMENT_HEADINGS_PKG
Source
1 PACKAGE BODY GR_DOCUMENT_HEADINGS_PKG AS
2 /*$Header: GRHIDHB.pls 115.10 2002/10/28 23:01:06 mgrosser ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_document_headings_seqno IN NUMBER,
7 p_document_code IN VARCHAR2,
8 p_main_heading_code IN VARCHAR2,
9 p_main_display_order IN NUMBER,
10 p_sub_heading_code IN VARCHAR2,
11 p_sub_display_order IN NUMBER,
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_current_seq OUT NOCOPY NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_oracle_error OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2)
22 IS
23 /* Alpha Variables */
24
25 L_RETURN_STATUS VARCHAR2(1) := 'S';
26 L_KEY_EXISTS VARCHAR2(1);
27 L_MSG_DATA VARCHAR2(2000);
28 L_ROWID VARCHAR2(18);
29
30 /* Number Variables */
31
32 L_ORACLE_ERROR NUMBER;
33 L_CURRENT_SEQ NUMBER;
34
35 /* Exceptions */
36
37 FOREIGN_KEY_ERROR EXCEPTION;
38 LABEL_EXISTS_ERROR EXCEPTION;
39 ROW_MISSING_ERROR EXCEPTION;
40
41 /* Declare cursors */
42
43 BEGIN
44
45 /* Initialization Routine */
46
47 SAVEPOINT Insert_Row;
48 x_return_status := 'S';
49 x_oracle_error := 0;
50 x_msg_data := NULL;
51
52 /* Now call the check foreign key procedure */
53
54 Check_Foreign_Keys
55 (p_document_headings_seqno,
56 p_document_code,
57 p_main_heading_code,
58 p_main_display_order,
59 p_sub_heading_code,
60 p_sub_display_order,
61 l_return_status,
62 l_oracle_error,
63 l_msg_data);
64 IF l_return_status <> 'S' THEN
65 RAISE Foreign_Key_Error;
66 END IF;
67
68 /* Now check the primary key doesn't already exist */
69
70 Check_Primary_Key
71 (p_document_headings_seqno,
72 'F',
73 l_rowid,
74 l_key_exists);
75
76 IF FND_API.To_Boolean(l_key_exists) THEN
77 RAISE Label_Exists_Error;
78 END IF;
79
80 INSERT INTO gr_document_headings
81 (document_headings_seqno,
82 document_code,
83 main_heading_code,
84 main_display_order,
85 sub_heading_code,
86 sub_display_order,
87 created_by,
88 creation_date,
89 last_updated_by,
90 last_update_date,
91 last_update_login)
92 VALUES
93 (gr_document_headings_s.nextval,
94 p_document_code,
95 p_main_heading_code,
96 p_main_display_order,
97 p_sub_heading_code,
98 p_sub_display_order,
99 p_created_by,
100 p_creation_date,
101 p_last_updated_by,
102 p_last_update_date,
103 p_last_update_login);
104
105 /* Get the assigned sequence number */
106
107 SELECT gr_document_headings_s.currval
108 INTO l_current_seq
109 FROM dual;
110
111 /* Now get the row id of the inserted record */
112
113 Check_Primary_Key
114 (l_current_seq,
115 'F',
116 l_rowid,
117 l_key_exists);
118
119 IF FND_API.To_Boolean(l_key_exists) THEN
120 x_rowid := l_rowid;
121 x_current_seq := l_current_seq;
122 ELSE
123 RAISE Row_Missing_Error;
124 END IF;
125
126 /* Check the commit flag and if set, then commit the work. */
127
128 IF FND_API.To_Boolean(p_commit) THEN
129 COMMIT WORK;
130 END IF;
131
132 EXCEPTION
133
134 WHEN Foreign_Key_Error THEN
135 ROLLBACK TO SAVEPOINT Insert_Row;
136 x_return_status := l_return_status;
137 x_oracle_error := l_oracle_error;
138 FND_MESSAGE.SET_NAME('GR',
139 'GR_FOREIGN_KEY_ERROR');
140 FND_MESSAGE.SET_TOKEN('TEXT',
141 l_msg_data,
142 FALSE);
143 IF FND_API.To_Boolean(p_called_by_form) THEN
144 APP_EXCEPTION.Raise_Exception;
145 ELSE
146 x_msg_data := FND_MESSAGE.Get;
147 END IF;
148
149 WHEN Label_Exists_Error THEN
150 ROLLBACK TO SAVEPOINT Insert_Row;
151 x_return_status := 'E';
152 x_oracle_error := APP_EXCEPTION.Get_Code;
153 FND_MESSAGE.SET_NAME('GR',
154 'GR_RECORD_EXISTS');
155 FND_MESSAGE.SET_TOKEN('CODE',
156 p_document_code,
157 FALSE);
158 IF FND_API.To_Boolean(p_called_by_form) THEN
159 APP_EXCEPTION.Raise_Exception;
160 ELSE
161 x_msg_data := FND_MESSAGE.Get;
162 END IF;
163
164 WHEN Row_Missing_Error THEN
165 ROLLBACK TO SAVEPOINT Insert_Row;
166 x_return_status := 'E';
167 x_oracle_error := APP_EXCEPTION.Get_Code;
168 FND_MESSAGE.SET_NAME('GR',
169 'GR_NO_RECORD_INSERTED');
170 FND_MESSAGE.SET_TOKEN('CODE',
171 p_document_code,
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 WHEN OTHERS THEN
180 ROLLBACK TO SAVEPOINT Insert_Row;
181 x_return_status := 'U';
182 x_oracle_error := APP_EXCEPTION.Get_Code;
183 l_msg_data := APP_EXCEPTION.Get_Text;
184 FND_MESSAGE.SET_NAME('GR',
185 'GR_UNEXPECTED_ERROR');
186 FND_MESSAGE.SET_TOKEN('TEXT',
187 l_msg_data,
188 FALSE);
189 IF FND_API.To_Boolean(p_called_by_form) THEN
190 APP_EXCEPTION.Raise_Exception;
191 ELSE
192 x_msg_data := FND_MESSAGE.Get;
193 END IF;
194
195 END Insert_Row;
196
197 PROCEDURE Update_Row
198 (p_commit IN VARCHAR2,
199 p_called_by_form IN VARCHAR2,
200 p_rowid IN VARCHAR2,
201 p_document_headings_seqno IN NUMBER,
202 p_document_code IN VARCHAR2,
203 p_main_heading_code IN VARCHAR2,
204 p_main_display_order IN NUMBER,
205 p_sub_heading_code IN VARCHAR2,
206 p_sub_display_order IN NUMBER,
207 p_created_by IN NUMBER,
208 p_creation_date IN DATE,
209 p_last_updated_by IN NUMBER,
210 p_last_update_date IN DATE,
211 p_last_update_login IN NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_oracle_error OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2)
215 IS
216
217 /* Alpha Variables */
218
219 L_RETURN_STATUS VARCHAR2(1) := 'S';
220 L_MSG_DATA VARCHAR2(2000);
221
222 /* Number Variables */
223
224 L_ORACLE_ERROR NUMBER;
225
226 /* Exceptions */
227
228 FOREIGN_KEY_ERROR EXCEPTION;
229 ROW_MISSING_ERROR EXCEPTION;
230
231 BEGIN
232
233 /* Initialization Routine */
234
235 SAVEPOINT Update_Row;
236 x_return_status := 'S';
237 x_oracle_error := 0;
238 x_msg_data := NULL;
239
240 /* Now call the check foreign key procedure */
241
242 Check_Foreign_Keys
243 (p_document_headings_seqno,
244 p_document_code,
245 p_main_heading_code,
246 p_main_display_order,
247 p_sub_heading_code,
248 p_sub_display_order,
249 l_return_status,
250 l_oracle_error,
251 l_msg_data);
252
253 IF l_return_status <> 'S' THEN
254 RAISE Foreign_Key_Error;
255 ELSE
256 UPDATE gr_document_headings
257 SET document_headings_seqno = p_document_headings_seqno,
258 document_code = p_document_code,
259 main_heading_code = p_main_heading_code,
260 main_display_order = p_main_display_order,
261 sub_heading_code = p_sub_heading_code,
262 sub_display_order = p_sub_display_order,
263 created_by = p_created_by,
264 creation_date = p_creation_date,
265 last_updated_by = p_last_updated_by,
266 last_update_date = p_last_update_date,
267 last_update_login = p_last_update_login
268 WHERE rowid = p_rowid;
269 IF SQL%NOTFOUND THEN
270 RAISE Row_Missing_Error;
271 END IF;
272 END IF;
273
274 /* Check the commit flag and if set, then commit the work. */
275
276 IF FND_API.To_Boolean(p_commit) THEN
277 COMMIT WORK;
278 END IF;
279
280 EXCEPTION
281
282 WHEN Foreign_Key_Error THEN
283 ROLLBACK TO SAVEPOINT Update_Row;
284 x_return_status := l_return_status;
285 x_oracle_error := l_oracle_error;
286 FND_MESSAGE.SET_NAME('GR',
287 'GR_FOREIGN_KEY_ERROR');
288 FND_MESSAGE.SET_TOKEN('TEXT',
289 l_msg_data,
290 FALSE);
291 IF FND_API.To_Boolean(p_called_by_form) THEN
292 APP_EXCEPTION.Raise_Exception;
293 ELSE
294 x_msg_data := FND_MESSAGE.Get;
295 END IF;
296
297 WHEN Row_Missing_Error THEN
298 ROLLBACK TO SAVEPOINT Update_Row;
299 x_return_status := 'E';
300 x_oracle_error := APP_EXCEPTION.Get_Code;
301 FND_MESSAGE.SET_NAME('GR',
302 'GR_NO_RECORD_INSERTED');
303 FND_MESSAGE.SET_TOKEN('CODE',
304 p_document_code,
305 FALSE);
306 IF FND_API.To_Boolean(p_called_by_form) THEN
307 APP_EXCEPTION.Raise_Exception;
308 ELSE
309 x_msg_data := FND_MESSAGE.Get;
310 END IF;
311
312 WHEN OTHERS THEN
313 ROLLBACK TO SAVEPOINT Update_Row;
314 x_return_status := 'U';
315 x_oracle_error := APP_EXCEPTION.Get_Code;
316 l_msg_data := APP_EXCEPTION.Get_Text;
317 FND_MESSAGE.SET_NAME('GR',
318 'GR_UNEXPECTED_ERROR');
319 FND_MESSAGE.SET_TOKEN('TEXT',
320 l_msg_data,
321 FALSE);
322 IF FND_API.To_Boolean(p_called_by_form) THEN
323 APP_EXCEPTION.Raise_Exception;
324 ELSE
325 x_msg_data := FND_MESSAGE.Get;
326 END IF;
327
328 END Update_Row;
329
330
331
332 PROCEDURE Update_Display_Columns
333 (p_commit IN VARCHAR2,
334 p_called_by_form IN VARCHAR2,
335 p_document_headings_seqno IN NUMBER,
336 p_main_display_order IN NUMBER,
337 p_sub_display_order IN NUMBER,
338 p_last_updated_by IN NUMBER,
339 p_last_update_date IN DATE,
340 p_last_update_login IN NUMBER,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_oracle_error OUT NOCOPY NUMBER,
343 x_msg_data OUT NOCOPY VARCHAR2) IS
344
345
346
347 /* Alpha Variables */
348
349 L_RETURN_STATUS VARCHAR2(1) := 'S';
350 L_MSG_DATA VARCHAR2(2000);
351
352 /* Number Variables */
353
354 L_ORACLE_ERROR NUMBER;
355
356 /* Exceptions */
357
358 ROW_MISSING_ERROR EXCEPTION;
359
360 BEGIN
361
362 /* Initialization Routine */
363
364 SAVEPOINT Update_Display_Columns;
365 x_return_status := 'S';
366 x_oracle_error := 0;
367 x_msg_data := NULL;
368
369 /* Update the table */
370
371 UPDATE gr_document_headings
372 SET main_display_order = p_main_display_order,
373 sub_display_order = p_sub_display_order,
374 last_updated_by = p_last_updated_by,
375 last_update_date = p_last_update_date,
376 last_update_login = p_last_update_login
377 WHERE document_headings_seqno = p_document_headings_seqno;
378 IF SQL%NOTFOUND THEN
379 RAISE Row_Missing_Error;
380 END IF;
381
382 /* Check the commit flag and if set, then commit the work. */
383
384 IF FND_API.To_Boolean(p_commit) THEN
385 COMMIT WORK;
386 END IF;
387
388 EXCEPTION
389
390 WHEN Row_Missing_Error THEN
391 ROLLBACK TO SAVEPOINT Update_Display_Columns;
392 x_return_status := 'E';
393 x_oracle_error := APP_EXCEPTION.Get_Code;
394 FND_MESSAGE.SET_NAME('GR',
395 'GR_NO_RECORD_INSERTED');
396 FND_MESSAGE.SET_TOKEN('CODE',
397 p_document_headings_seqno,
398 FALSE);
399 IF FND_API.To_Boolean(p_called_by_form) THEN
400 APP_EXCEPTION.Raise_Exception;
401 ELSE
405 WHEN OTHERS THEN
402 x_msg_data := FND_MESSAGE.Get;
403 END IF;
404
406 ROLLBACK TO SAVEPOINT Update_Display_Columns;
407 x_return_status := 'U';
408 x_oracle_error := APP_EXCEPTION.Get_Code;
409 l_msg_data := APP_EXCEPTION.Get_Text;
410 FND_MESSAGE.SET_NAME('GR',
411 'GR_UNEXPECTED_ERROR');
412 FND_MESSAGE.SET_TOKEN('TEXT',
413 l_msg_data,
414 FALSE);
415 IF FND_API.To_Boolean(p_called_by_form) THEN
416 APP_EXCEPTION.Raise_Exception;
417 ELSE
418 x_msg_data := FND_MESSAGE.Get;
419 END IF;
420
421 END Update_Display_Columns;
422
423
424 PROCEDURE Lock_Row
425 (p_commit IN VARCHAR2,
426 p_called_by_form IN VARCHAR2,
427 p_rowid IN VARCHAR2,
428 p_document_headings_seqno IN NUMBER,
429 p_document_code IN VARCHAR2,
430 p_main_heading_code IN VARCHAR2,
431 p_main_display_order IN NUMBER,
432 p_sub_heading_code IN VARCHAR2,
433 p_sub_display_order IN NUMBER,
434 p_created_by IN NUMBER,
435 p_creation_date IN DATE,
436 p_last_updated_by IN NUMBER,
437 p_last_update_date IN DATE,
438 p_last_update_login IN NUMBER,
439 x_return_status OUT NOCOPY VARCHAR2,
440 x_oracle_error OUT NOCOPY NUMBER,
441 x_msg_data OUT NOCOPY VARCHAR2)
442 IS
443
444 /* Alpha Variables */
445
446 L_RETURN_STATUS VARCHAR2(1) := 'S';
447 L_MSG_DATA VARCHAR2(2000);
448
449 /* Number Variables */
450
451 L_ORACLE_ERROR NUMBER;
452
453 /* Exceptions */
454
455 NO_DATA_FOUND_ERROR EXCEPTION;
456 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
457 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
458
459 /* Define the cursors */
460
461 CURSOR c_lock_document
462 IS
463 SELECT *
464 FROM gr_document_headings
465 WHERE rowid = p_rowid
466 FOR UPDATE NOWAIT;
467 LockDocumentRcd c_lock_document%ROWTYPE;
468
469 BEGIN
470
471 /* Initialization Routine */
472
473 SAVEPOINT Lock_Row;
474 x_return_status := 'S';
475 x_oracle_error := 0;
476 x_msg_data := NULL;
477
478 /* Now lock the record */
479
480 OPEN c_lock_document;
481 FETCH c_lock_document INTO LockDocumentRcd;
482 IF c_lock_document%NOTFOUND THEN
483 CLOSE c_lock_document;
484 RAISE No_Data_Found_Error;
485 END IF;
486 CLOSE c_lock_document;
487
488 IF FND_API.To_Boolean(p_commit) THEN
489 COMMIT WORK;
490 END IF;
491
492 EXCEPTION
493
494 WHEN No_Data_Found_Error THEN
495 ROLLBACK TO SAVEPOINT Lock_Row;
496 x_return_status := 'E';
497 FND_MESSAGE.SET_NAME('GR',
498 'GR_RECORD_NOT_FOUND');
499 FND_MESSAGE.SET_TOKEN('CODE',
500 p_document_code,
501 FALSE);
502 IF FND_API.To_Boolean(p_called_by_form) THEN
503 APP_EXCEPTION.Raise_Exception;
504 ELSE
505 x_msg_data := FND_MESSAGE.Get;
506 END IF;
507
508 WHEN Row_Already_Locked_Error THEN
509 ROLLBACK TO SAVEPOINT Lock_Row;
510 x_return_status := 'E';
511 x_oracle_error := APP_EXCEPTION.Get_Code;
512 FND_MESSAGE.SET_NAME('GR',
513 'GR_ROW_IS_LOCKED');
514 IF FND_API.To_Boolean(p_called_by_form) THEN
515 APP_EXCEPTION.Raise_Exception;
516 ELSE
517 x_msg_data := FND_MESSAGE.Get;
521 ROLLBACK TO SAVEPOINT Lock_Row;
518 END IF;
519
520 WHEN OTHERS THEN
522 x_return_status := 'U';
523 x_oracle_error := APP_EXCEPTION.Get_Code;
524 l_msg_data := APP_EXCEPTION.Get_Text;
525 FND_MESSAGE.SET_NAME('GR',
526 'GR_UNEXPECTED_ERROR');
527 FND_MESSAGE.SET_TOKEN('TEXT',
528 l_msg_data,
529 FALSE);
530 IF FND_API.To_Boolean(p_called_by_form) THEN
531 APP_EXCEPTION.Raise_Exception;
532 ELSE
533 x_msg_data := FND_MESSAGE.Get;
534 END IF;
535
536 END Lock_Row;
537
538 PROCEDURE Delete_Row
539 (p_commit IN VARCHAR2,
540 p_called_by_form IN VARCHAR2,
541 p_rowid IN VARCHAR2,
542 p_document_headings_seqno IN NUMBER,
543 p_document_code IN VARCHAR2,
544 p_main_heading_code IN VARCHAR2,
545 p_main_display_order IN NUMBER,
546 p_sub_heading_code IN VARCHAR2,
547 p_sub_display_order IN NUMBER,
548 p_created_by IN NUMBER,
549 p_creation_date IN DATE,
550 p_last_updated_by IN NUMBER,
551 p_last_update_date IN DATE,
552 p_last_update_login IN NUMBER,
553 x_return_status OUT NOCOPY VARCHAR2,
554 x_oracle_error OUT NOCOPY NUMBER,
555 x_msg_data OUT NOCOPY VARCHAR2)
556 IS
557
558 /* Alpha Variables */
559
560 L_RETURN_STATUS VARCHAR2(1) := 'S';
561 L_MSG_DATA VARCHAR2(2000);
562 L_CALLED_BY_FORM VARCHAR2(1);
563
564 /* Number Variables */
565
566 L_ORACLE_ERROR NUMBER;
567
568 /* Exceptions */
569
570 CHECK_INTEGRITY_ERROR EXCEPTION;
571 ROW_MISSING_ERROR EXCEPTION;
572 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
573
574 BEGIN
575
576 /* Initialization Routine */
577
578 SAVEPOINT Delete_Row;
579 x_return_status := 'S';
580 l_called_by_form := 'F';
581 x_oracle_error := 0;
582 x_msg_data := NULL;
583
584 /* Now call the check integrity procedure */
585
586 Check_Integrity
587 (l_called_by_form,
588 p_document_headings_seqno,
589 p_document_code,
590 p_main_heading_code,
591 p_main_display_order,
592 p_sub_heading_code,
593 p_sub_display_order,
594 l_return_status,
595 l_oracle_error,
596 l_msg_data);
597
598 IF l_return_status <> 'S' THEN
599 RAISE Check_Integrity_Error;
600 END IF;
601
602 DELETE FROM gr_document_headings
603 -- WHERE rowid = p_rowid;
604 WHERE document_headings_seqno = p_document_headings_seqno;
605
606 /* Check the commit flag and if set, then commit the work. */
607
608 IF FND_API.TO_Boolean(p_commit) THEN
609 COMMIT WORK;
610 END IF;
611
612 EXCEPTION
613
614 WHEN Check_Integrity_Error THEN
615 ROLLBACK TO SAVEPOINT Delete_Row;
616 x_return_status := l_return_status;
617 x_oracle_error := l_oracle_error;
618 IF FND_API.To_Boolean(p_called_by_form) THEN
619 APP_EXCEPTION.Raise_Exception;
620 ELSE
621 x_msg_data := FND_MESSAGE.Get;
622 END IF;
623
624 WHEN Row_Missing_Error THEN
625 ROLLBACK TO SAVEPOINT Delete_Row;
626 x_return_status := 'E';
630 FND_MESSAGE.SET_TOKEN('CODE',
627 x_oracle_error := APP_EXCEPTION.Get_Code;
628 FND_MESSAGE.SET_NAME('GR',
629 'GR_RECORD_NOT_FOUND');
631 p_document_code,
632 FALSE);
633 IF FND_API.To_Boolean(p_called_by_form) THEN
634 APP_EXCEPTION.Raise_Exception;
635 ELSE
636 x_msg_data := FND_MESSAGE.Get;
637 END IF;
638
639 WHEN OTHERS THEN
640 ROLLBACK TO SAVEPOINT Delete_Row;
641 x_return_status := 'U';
642 x_oracle_error := APP_EXCEPTION.Get_Code;
643 l_msg_data := APP_EXCEPTION.Get_Text;
644 l_msg_data := APP_EXCEPTION.Get_Text;
645 FND_MESSAGE.SET_NAME('GR',
646 'GR_UNEXPECTED_ERROR');
647 FND_MESSAGE.SET_TOKEN('TEXT',
648 l_msg_data,
649 FALSE);
650 IF FND_API.To_Boolean(p_called_by_form) THEN
651 APP_EXCEPTION.Raise_Exception;
652 ELSE
653 x_msg_data := FND_MESSAGE.Get;
654 END IF;
655
656 END Delete_Row;
657
658 PROCEDURE Delete_Rows
659 (p_commit IN VARCHAR2,
660 p_called_by_form IN VARCHAR2,
661 p_document_code IN VARCHAR2,
662 x_return_status OUT NOCOPY VARCHAR2,
663 x_oracle_error OUT NOCOPY NUMBER,
664 x_msg_data OUT NOCOPY VARCHAR2)
665 IS
666
667 /* Alpha Variables */
668
669 L_RETURN_STATUS VARCHAR2(1) := 'S';
670 L_MSG_DATA VARCHAR2(2000);
671 L_MSG_TOKEN VARCHAR2(30);
672
673 /* Number Variables */
674
675 L_ORACLE_ERROR NUMBER;
676
677 /* Define the cursors */
678
679 BEGIN
680
681 /* Initialization Routine */
682
683 SAVEPOINT Delete_Rows;
684 x_return_status := 'S';
685 x_oracle_error := 0;
686 x_msg_data := NULL;
687 l_msg_token := p_document_code;
688
689 DELETE FROM gr_document_headings
690 WHERE document_code = p_document_code;
691
692 IF FND_API.To_Boolean(p_commit) THEN
693 COMMIT WORK;
694 END IF;
695
696 EXCEPTION
697
698 WHEN OTHERS THEN
699 ROLLBACK TO SAVEPOINT Delete_Rows;
700 x_return_status := 'U';
701 x_oracle_error := APP_EXCEPTION.Get_Code;
702 l_msg_data := APP_EXCEPTION.Get_Text;
703 FND_MESSAGE.SET_NAME('GR',
704 'GR_UNEXPECTED_ERROR');
705 FND_MESSAGE.SET_TOKEN('TEXT',
706 l_msg_token,
707 FALSE);
708 IF FND_API.To_Boolean(p_called_by_form) THEN
709 APP_EXCEPTION.Raise_Exception;
710 ELSE
711 x_msg_data := FND_MESSAGE.Get;
712 END IF;
713
714 END Delete_Rows;
715
716 PROCEDURE Check_Foreign_Keys
717 (p_document_headings_seqno IN NUMBER,
718 p_document_code IN VARCHAR2,
719 p_main_heading_code IN VARCHAR2,
720 p_main_display_order IN NUMBER,
721 p_sub_heading_code IN VARCHAR2,
722 p_sub_display_order IN NUMBER,
723 x_return_status OUT NOCOPY VARCHAR2,
724 x_oracle_error OUT NOCOPY NUMBER,
725 x_msg_data OUT NOCOPY VARCHAR2)
726 IS
727
728 /* Alpha Variables */
729
730 L_RETURN_STATUS VARCHAR2(1) := 'S';
731 L_MSG_DATA VARCHAR2(2000);
732 L_ROWID VARCHAR2(18);
733 L_KEY_EXISTS VARCHAR2(1);
734
735 /* Number Variables */
736
737 L_ORACLE_ERROR NUMBER;
738
739 /* Define the cursors */
740 /* Main Headings */
741
742 CURSOR c_get_main_heading
743 IS
744 SELECT mh.main_heading_code
745 FROM gr_main_headings_b mh
749 /* Sub Headings */
746 WHERE mh.main_heading_code = p_main_heading_code;
747 MainHdgRcd c_get_main_heading%ROWTYPE;
748
750
751 CURSOR c_get_sub_heading
752 IS
753 SELECT sh.sub_heading_code
754 FROM gr_sub_headings_b sh
755 WHERE sh.sub_heading_code = p_sub_heading_code;
756 SubHdgRcd c_get_sub_heading%ROWTYPE;
757
758
759 BEGIN
760
761 /* Initialization Routine */
762
763 SAVEPOINT Check_Foreign_Keys;
764 x_return_status := 'S';
765 x_oracle_error := 0;
766 x_msg_data := NULL;
767
768 /* Check the main heading code for ingredient print */
769
770 OPEN c_get_main_heading;
771 FETCH c_get_main_heading INTO MainHdgRcd;
772 IF c_get_main_heading%NOTFOUND THEN
773 x_return_status := 'E';
774 FND_MESSAGE.SET_NAME('GR',
775 'GR_RECORD_NOT_FOUND');
776 FND_MESSAGE.SET_TOKEN('CODE',
777 p_main_heading_code,
778 FALSE);
779 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
780 END IF;
781 CLOSE c_get_main_heading;
782
783 /* Check the sub heading code */
784
785 IF p_sub_heading_code IS NOT NULL THEN
786 OPEN c_get_sub_heading;
787 FETCH c_get_sub_heading INTO SubHdgRcd;
788 IF c_get_sub_heading%NOTFOUND THEN
789 x_return_status := 'E';
790 FND_MESSAGE.SET_NAME('GR',
791 'GR_RECORD_NOT_FOUND');
792 FND_MESSAGE.SET_TOKEN('CODE',
793 p_sub_heading_code,
794 FALSE);
795 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
796 END IF;
797 CLOSE c_get_sub_heading;
798 END IF;
799
800 IF x_return_status <> 'S' THEN
801 x_msg_data := l_msg_data;
802 END IF;
803
804 EXCEPTION
805
806 WHEN OTHERS THEN
807 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
808 x_return_status := 'U';
809 x_oracle_error := APP_EXCEPTION.Get_Code;
810 l_msg_data := APP_EXCEPTION.Get_Text;
811 FND_MESSAGE.SET_NAME('GR',
812 'GR_UNEXPECTED_ERROR');
813 FND_MESSAGE.SET_TOKEN('TEXT',
814 l_msg_data,
815 FALSE);
816 x_msg_data := FND_MESSAGE.Get;
817
818 END Check_Foreign_Keys;
819
820 PROCEDURE Check_Integrity
821 (p_called_by_form IN VARCHAR2,
822 p_document_headings_seqno IN NUMBER,
823 p_document_code IN VARCHAR2,
824 p_main_heading_code IN VARCHAR2,
825 p_main_display_order IN NUMBER,
826 p_sub_heading_code IN VARCHAR2,
827 p_sub_display_order IN NUMBER,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_oracle_error OUT NOCOPY NUMBER,
830 x_msg_data OUT NOCOPY VARCHAR2)
831 IS
832
833 /* Alpha Variables */
834
835 L_RETURN_STATUS VARCHAR2(1) := 'S';
836 L_MSG_DATA VARCHAR2(2000);
837 L_CODE_BLOCK VARCHAR2(100);
838
839 /* Number Variables */
840
841 L_ORACLE_ERROR NUMBER;
842 L_RECORD_COUNT NUMBER := 0;
843
844 /* Define the Cursors */
845 CURSOR c_get_item_documents
846 IS
847 SELECT COUNT(*) count
848 FROM gr_item_document_dtls idd
852
849 WHERE idd.document_code = p_document_code
850 AND idd.main_heading_code = p_main_heading_code
851 AND idd.sub_heading_code = p_sub_heading_code;
853 BEGIN
854
855 /* Initialization Routine */
856
857 SAVEPOINT Check_Integrity;
858 x_return_status := 'S';
859 x_oracle_error := 0;
860 x_msg_data := NULL;
861
862 FND_MESSAGE.SET_NAME('GR',
863 'GR_INTEGRITY_HEADER');
864 FND_MESSAGE.SET_TOKEN('CODE',
865 p_main_heading_code || ' ' || p_sub_heading_code,
866 FALSE);
867 l_msg_data := FND_MESSAGE.Get;
868
869 /* Check for any rows defined in the item document table */
870
871 l_record_count := 0;
872 OPEN c_get_item_documents;
873 FETCH c_get_item_documents INTO l_record_count;
874 IF l_record_count <> 0 THEN
875 l_return_status := 'E';
876 l_msg_data := l_msg_data || ' gr_item_document_dtls';
877 END IF;
878 CLOSE c_get_item_documents;
879
880 /* Now sort out the error messaging */
881 IF l_return_status <> 'S' THEN
882 x_return_status := l_return_status;
883 x_msg_data := l_msg_data;
884 END IF;
885
886 EXCEPTION
887
888 WHEN OTHERS THEN
889 ROLLBACK TO SAVEPOINT Check_Integrity;
890 x_return_status := 'U';
891 x_oracle_error := APP_EXCEPTION.Get_Code;
892 l_msg_data := APP_EXCEPTION.Get_Text;
893 FND_MESSAGE.SET_NAME('GR',
894 'GR_UNEXPECTED_ERROR');
895 FND_MESSAGE.SET_TOKEN('TEXT',
896 l_msg_data,
897 FALSE);
898 IF FND_API.To_Boolean(p_called_by_form) THEN
899 APP_EXCEPTION.Raise_Exception;
900 ELSE
901 x_msg_data := FND_MESSAGE.Get;
902 END IF;
903
904 END Check_Integrity;
905
906 PROCEDURE Check_Primary_Key
907 /* p_document_headings_seqno is the sequence number to check.
908 ** p_called_by_form is 'T' if called by a form or 'F' if not.
909 ** x_rowid is the row id of the record if found.
910 ** x_key_exists is 'T' is the record is found, 'F' if not.
911 */
912 (p_document_headings_seqno IN NUMBER,
913 p_called_by_form IN VARCHAR2,
914 x_rowid OUT NOCOPY VARCHAR2,
915 x_key_exists OUT NOCOPY VARCHAR2)
916 IS
917 /* Alphanumeric variables */
918
919 L_MSG_DATA VARCHAR2(80);
920
921 /* Declare any variables and the cursor */
922
923
924 CURSOR c_get_document_rowid
925 IS
926 SELECT dh.rowid
927 FROM gr_document_headings dh
928 WHERE dh.document_headings_seqno = p_document_headings_seqno;
929 DocumentRecord c_get_document_rowid%ROWTYPE;
930
931 BEGIN
932
933 x_key_exists := 'F';
934 l_msg_data := p_document_headings_seqno;
935 OPEN c_get_document_rowid;
936 FETCH c_get_document_rowid INTO DocumentRecord;
937 IF c_get_document_rowid%FOUND THEN
938 x_key_exists := 'T';
939 x_rowid := DocumentRecord.rowid;
940 ELSE
941 x_key_exists := 'F';
942 END IF;
943 CLOSE c_get_document_rowid;
944
945 EXCEPTION
946
947 WHEN Others THEN
948 l_msg_data := APP_EXCEPTION.Get_Text;
949 FND_MESSAGE.SET_NAME('GR',
950 'GR_UNEXPECTED_ERROR');
954 IF FND_API.To_Boolean(p_called_by_form) THEN
951 FND_MESSAGE.SET_TOKEN('TEXT',
952 l_msg_data,
953 FALSE);
955 APP_EXCEPTION.Raise_Exception;
956 END IF;
957
958 END Check_Primary_Key;
959
960 PROCEDURE Lock_Rows
961 (p_document_code IN VARCHAR2,
962 p_last_update_date IN DATE,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_oracle_error OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2)
966 IS
967
968 /* Alpha Variables */
969
970 L_RETURN_STATUS VARCHAR2(1) := 'S';
971 L_MSG_DATA VARCHAR2(2000);
972
973 /* Number Variables */
974
975 L_ORACLE_ERROR NUMBER;
976
977 /* Exceptions */
978
979 NO_DATA_FOUND_ERROR EXCEPTION;
980 RECORD_CHANGED_ERROR EXCEPTION;
981 /* Define the cursors */
982
983 CURSOR c_lock_document
984 IS
985 SELECT last_update_date
986 FROM gr_document_headings
987 WHERE document_code = p_document_code
988 FOR UPDATE NOWAIT;
989 LockDocumentRcd c_lock_document%ROWTYPE;
990
991 BEGIN
992
993 /* Initialization Routine */
994
995 SAVEPOINT Lock_Rows;
996 x_return_status := 'S';
997 x_oracle_error := 0;
998 x_msg_data := NULL;
999
1000 /* Now lock the record */
1001
1002 OPEN c_lock_document;
1003 FETCH c_lock_document INTO LockDocumentRcd;
1004 IF c_lock_document%NOTFOUND THEN
1005 CLOSE c_lock_document;
1006 RAISE No_Data_Found_Error;
1007 END IF;
1008 CLOSE c_lock_document;
1009 IF LockDocumentRcd.last_update_date <> p_last_update_date THEN
1010 RAISE RECORD_CHANGED_ERROR;
1011 END IF;
1012
1013 EXCEPTION
1014
1015 WHEN No_Data_Found_Error THEN
1016 ROLLBACK TO SAVEPOINT Lock_Rows;
1017 x_return_status := 'E';
1018 FND_MESSAGE.SET_NAME('GR',
1019 'GR_RECORD_NOT_FOUND');
1020 FND_MESSAGE.SET_TOKEN('CODE',
1021 p_document_code,
1022 FALSE);
1023 APP_EXCEPTION.Raise_Exception;
1024 WHEN RECORD_CHANGED_ERROR THEN
1025 X_return_status := 'E';
1026 FND_MESSAGE.SET_NAME('FND',
1027 'FORM_RECORD_CHANGED');
1028 APP_EXCEPTION.Raise_Exception;
1029 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1030 ROLLBACK TO SAVEPOINT Lock_Rows;
1031 x_return_status := 'L';
1032 WHEN OTHERS THEN
1033 ROLLBACK TO SAVEPOINT Lock_Rows;
1034 x_return_status := 'U';
1035 x_oracle_error := APP_EXCEPTION.Get_Code;
1036 l_msg_data := APP_EXCEPTION.Get_Text;
1037 FND_MESSAGE.SET_NAME('GR',
1038 'GR_UNEXPECTED_ERROR');
1039 FND_MESSAGE.SET_TOKEN('TEXT',
1040 l_msg_data,
1041 FALSE);
1042 APP_EXCEPTION.Raise_Exception;
1043
1044 END Lock_Rows;
1045
1046 END GR_DOCUMENT_HEADINGS_PKG;