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