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