[Home] [Help]
PACKAGE BODY: APPS.GR_ITEM_DISCLOSURES_PKG
Source
1 PACKAGE BODY GR_ITEM_DISCLOSURES_PKG AS
2 /*$Header: GRHIIDB.pls 115.6 2002/10/25 20:17:06 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_disclosure_code IN VARCHAR2,
8 p_print_on_document_flag IN VARCHAR2,
9 p_minimum_reporting_level IN NUMBER,
10 p_text_reporting_level IN NUMBER,
11 p_label_reporting_level IN NUMBER,
12 p_exposure_reporting_level IN NUMBER,
13 p_toxicity_reporting_level IN NUMBER,
14 p_created_by IN NUMBER,
15 p_creation_date IN DATE,
16 p_last_update_date IN DATE,
17 p_last_updated_by IN NUMBER,
18 p_last_update_login IN NUMBER,
19 x_rowid OUT NOCOPY VARCHAR2,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_oracle_error OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2)
23 IS
24 /* Alpha Variables */
25
26 L_RETURN_STATUS VARCHAR2(1) := 'S';
27 L_KEY_EXISTS VARCHAR2(1);
28 L_MSG_DATA VARCHAR2(2000);
29 L_ROWID VARCHAR2(18);
30 L_MSG_TOKEN VARCHAR2(80);
31
32 /* Number Variables */
33
34 L_ORACLE_ERROR NUMBER;
35 /* Exceptions */
36
37 FOREIGN_KEY_ERROR EXCEPTION;
38 ITEM_EXISTS_ERROR EXCEPTION;
39 ROW_MISSING_ERROR EXCEPTION;
40
41 /* Declare cursors */
42
43
44 BEGIN
45
46 /* Initialization Routine */
47
48 SAVEPOINT Insert_Row;
49 x_return_status := 'S';
50 x_oracle_error := 0;
51 x_msg_data := NULL;
52 l_msg_token := p_item_code || ' ' || p_disclosure_code;
53
54
55 /* Now call the check foreign key procedure */
56
57 Check_Foreign_Keys
58 (p_item_code,
59 p_disclosure_code,
60 p_print_on_document_flag,
61 p_minimum_reporting_level,
62 p_text_reporting_level,
63 p_label_reporting_level,
64 p_exposure_reporting_level,
65 p_toxicity_reporting_level,
66 p_created_by,
67 p_creation_date,
68 p_last_update_date,
69 p_last_updated_by,
70 p_last_update_login,
71 l_return_status,
72 l_oracle_error,
73 l_msg_data);
74 IF l_return_status <> 'S' THEN
75 RAISE Foreign_Key_Error;
76 END IF;
77
78 /* Now check the primary key doesn't already exist */
79
80 Check_Primary_Key
81 (p_item_code,
82 p_disclosure_code,
83 'F',
84 l_rowid,
85 l_key_exists);
86
87 IF FND_API.To_Boolean(l_key_exists) THEN
88 RAISE Item_Exists_Error;
89 END IF;
90
91 INSERT INTO gr_item_disclosures
92 (item_code,
93 disclosure_code,
94 print_on_document_flag,
95 minimum_reporting_level,
96 text_reporting_level,
97 label_reporting_level,
98 exposure_reporting_level,
99 toxicity_reporting_level,
100 created_by,
101 creation_date,
102 last_update_date,
103 last_updated_by,
104 last_update_login)
105 VALUES
106 (p_item_code,
107 p_disclosure_code,
108 p_print_on_document_flag,
109 p_minimum_reporting_level,
110 p_text_reporting_level,
111 p_label_reporting_level,
112 p_exposure_reporting_level,
113 p_toxicity_reporting_level,
114 p_created_by,
115 p_creation_date,
116 p_last_update_date,
117 p_last_updated_by,
118 p_last_update_login);
119
120 /* Now get the row id of the inserted record */
121
122 Check_Primary_Key
123 (p_item_code,
124 p_disclosure_code,
125 'F',
126 l_rowid,
127 l_key_exists);
128
129 IF FND_API.To_Boolean(l_key_exists) THEN
130 x_rowid := l_rowid;
131 ELSE
132 RAISE Row_Missing_Error;
133 END IF;
134
135 /* Check the commit flag and if set, then commit the work. */
136
137 IF FND_API.To_Boolean(p_commit) THEN
138 COMMIT WORK;
139 END IF;
140
141 EXCEPTION
142
143 WHEN Foreign_Key_Error THEN
144 ROLLBACK TO SAVEPOINT Insert_Row;
145 x_return_status := l_return_status;
146 x_oracle_error := l_oracle_error;
147 FND_MESSAGE.SET_NAME('GR',
148 'GR_FOREIGN_KEY_ERROR');
149 FND_MESSAGE.SET_TOKEN('TEXT',
150 l_msg_data,
151 FALSE);
152 IF FND_API.To_Boolean(p_called_by_form) THEN
153 APP_EXCEPTION.Raise_Exception;
154 ELSE
155 x_msg_data := FND_MESSAGE.Get;
156 END IF;
157
158 WHEN Item_Exists_Error THEN
159 ROLLBACK TO SAVEPOINT Insert_Row;
160 x_return_status := 'E';
161 x_oracle_error := APP_EXCEPTION.Get_Code;
162 FND_MESSAGE.SET_NAME('GR',
163 'GR_RECORD_EXISTS');
164 FND_MESSAGE.SET_TOKEN('CODE',
165 l_msg_token,
166 FALSE);
167 IF FND_API.To_Boolean(p_called_by_form) THEN
168 APP_EXCEPTION.Raise_Exception;
169 ELSE
170 x_msg_data := FND_MESSAGE.Get;
171 END IF;
172
173 WHEN Row_Missing_Error THEN
174 ROLLBACK TO SAVEPOINT Insert_Row;
175 x_return_status := 'E';
176 x_oracle_error := APP_EXCEPTION.Get_Code;
177 FND_MESSAGE.SET_NAME('GR',
178 'GR_NO_RECORD_INSERTED');
179 FND_MESSAGE.SET_TOKEN('CODE',
180 l_msg_token,
181 FALSE);
182 IF FND_API.To_Boolean(p_called_by_form) THEN
183 APP_EXCEPTION.Raise_Exception;
184 ELSE
185 x_msg_data := FND_MESSAGE.Get;
186 END IF;
187
188 WHEN OTHERS THEN
189 ROLLBACK TO SAVEPOINT Insert_Row;
190 x_return_status := 'U';
191 x_oracle_error := APP_EXCEPTION.Get_Code;
192 l_msg_data := APP_EXCEPTION.Get_Text;
193 FND_MESSAGE.SET_NAME('GR',
194 'GR_UNEXPECTED_ERROR');
195 FND_MESSAGE.SET_TOKEN('TEXT',
196 l_msg_token,
197 FALSE);
198 IF FND_API.To_Boolean(p_called_by_form) THEN
199 APP_EXCEPTION.Raise_Exception;
200 ELSE
201 x_msg_data := FND_MESSAGE.Get;
202 END IF;
203
204 END Insert_Row;
205
206 PROCEDURE Update_Row
207 (p_commit IN VARCHAR2,
208 p_called_by_form IN VARCHAR2,
209 p_rowid IN VARCHAR2,
210 p_item_code IN VARCHAR2,
211 p_disclosure_code IN VARCHAR2,
212 p_print_on_document_flag IN VARCHAR2,
213 p_minimum_reporting_level IN NUMBER,
214 p_text_reporting_level IN NUMBER,
215 p_label_reporting_level IN NUMBER,
216 p_exposure_reporting_level IN NUMBER,
217 p_toxicity_reporting_level IN NUMBER,
218 p_created_by IN NUMBER,
219 p_creation_date IN DATE,
220 p_last_update_date IN DATE,
221 p_last_updated_by IN NUMBER,
222 p_last_update_login IN NUMBER,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_oracle_error OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2)
226 IS
227
228 /* Alpha Variables */
229
230 L_RETURN_STATUS VARCHAR2(1) := 'S';
231 L_MSG_DATA VARCHAR2(2000);
232 L_MSG_TOKEN VARCHAR2(80);
233
234 /* Number Variables */
235
236 L_ORACLE_ERROR NUMBER;
237
238 /* Exceptions */
239
240 FOREIGN_KEY_ERROR EXCEPTION;
241 ROW_MISSING_ERROR EXCEPTION;
242 BEGIN
243
244 /* Initialization Routine */
245
246 SAVEPOINT Update_Row;
247 x_return_status := 'S';
248 x_oracle_error := 0;
249 x_msg_data := NULL;
250 l_msg_token := p_item_code || ' ' || p_disclosure_code;
251
252 /* Now call the check foreign key procedure */
253
254 Check_Foreign_Keys
255 (p_item_code,
256 p_disclosure_code,
257 p_print_on_document_flag,
258 p_minimum_reporting_level,
259 p_text_reporting_level,
260 p_label_reporting_level,
261 p_exposure_reporting_level,
262 p_toxicity_reporting_level,
263 p_created_by,
264 p_creation_date,
265 p_last_update_date,
266 p_last_updated_by,
267 p_last_update_login,
268 l_return_status,
269 l_oracle_error,
270 l_msg_data);
271
272 IF l_return_status <> 'S' THEN
273 RAISE Foreign_Key_Error;
274 ELSE
275 UPDATE gr_item_disclosures
276 SET item_code = p_item_code,
277 disclosure_code = p_disclosure_code,
278 print_on_document_flag = p_print_on_document_flag,
279 minimum_reporting_level = p_minimum_reporting_level,
280 text_reporting_level = p_text_reporting_level,
281 label_reporting_level = p_label_reporting_level,
282 exposure_reporting_level = p_exposure_reporting_level,
283 toxicity_reporting_level = p_toxicity_reporting_level,
284 created_by = p_created_by,
285 creation_date = p_creation_date,
286 last_update_date = p_last_update_date,
287 last_updated_by = p_last_updated_by,
288 last_update_login = p_last_update_login
289 WHERE rowid = p_rowid;
290 IF SQL%NOTFOUND THEN
291 RAISE Row_Missing_Error;
292 END IF;
293 END IF;
294
295 /* Check the commit flag and if set, then commit the work. */
296
297 IF FND_API.To_Boolean(p_commit) THEN
298 COMMIT WORK;
299 END IF;
300
301 EXCEPTION
302
303 WHEN Foreign_Key_Error THEN
304 ROLLBACK TO SAVEPOINT Update_Row;
305 x_return_status := l_return_status;
306 x_oracle_error := l_oracle_error;
307 FND_MESSAGE.SET_NAME('GR',
308 'GR_FOREIGN_KEY_ERROR');
309 FND_MESSAGE.SET_TOKEN('TEXT',
310 l_msg_data,
311 FALSE);
312 IF FND_API.To_Boolean(p_called_by_form) THEN
313 APP_EXCEPTION.Raise_Exception;
314 ELSE
315 x_msg_data := FND_MESSAGE.Get;
316 END IF;
317
318 WHEN Row_Missing_Error THEN
319 ROLLBACK TO SAVEPOINT Update_Row;
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_token,
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_token,
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_disclosure_code IN VARCHAR2,
357 p_print_on_document_flag IN VARCHAR2,
358 p_minimum_reporting_level IN NUMBER,
359 p_text_reporting_level IN NUMBER,
360 p_label_reporting_level IN NUMBER,
361 p_exposure_reporting_level IN NUMBER,
362 p_toxicity_reporting_level IN NUMBER,
363 p_created_by IN NUMBER,
364 p_creation_date IN DATE,
365 p_last_update_date IN DATE,
366 p_last_updated_by IN NUMBER,
367 p_last_update_login IN NUMBER,
368 x_return_status OUT NOCOPY VARCHAR2,
369 x_oracle_error OUT NOCOPY NUMBER,
370 x_msg_data OUT NOCOPY VARCHAR2)
371 IS
372
373 /* Alpha Variables */
374
375 L_RETURN_STATUS VARCHAR2(1) := 'S';
376 L_MSG_DATA VARCHAR2(2000);
377 L_MSG_TOKEN VARCHAR2(80);
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_discs
392 IS
393 SELECT *
394 FROM gr_item_disclosures
395 WHERE rowid = p_rowid
396 FOR UPDATE NOWAIT;
397 LockDiscRcd c_lock_discs%ROWTYPE;
398 BEGIN
399
400 /* Initialization Routine */
401
402 SAVEPOINT Lock_Row;
403 x_return_status := 'S';
404 x_oracle_error := 0;
405 x_msg_data := NULL;
406 l_msg_token := p_item_code || ' ' || p_disclosure_code;
407
408 /* Now lock the record */
409
410 OPEN c_lock_discs;
411 FETCH c_lock_discs INTO LockDiscRcd;
412 IF c_lock_discs%NOTFOUND THEN
413 CLOSE c_lock_discs;
414 RAISE No_Data_Found_Error;
415 END IF;
416 CLOSE c_lock_discs;
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 x_return_status := 'E';
427 FND_MESSAGE.SET_NAME('GR',
428 'GR_RECORD_NOT_FOUND');
429 FND_MESSAGE.SET_TOKEN('CODE',
430 l_msg_token,
431 FALSE);
432 IF FND_API.To_Boolean(p_called_by_form) THEN
433 APP_EXCEPTION.Raise_Exception;
434 ELSE
435 x_msg_data := FND_MESSAGE.Get;
436 END IF;
437
441 x_oracle_error := APP_EXCEPTION.Get_Code;
438 WHEN Row_Already_Locked_Error THEN
439 ROLLBACK TO SAVEPOINT Lock_Row;
440 x_return_status := 'E';
442 FND_MESSAGE.SET_NAME('GR',
443 'GR_ROW_IS_LOCKED');
444 IF FND_API.To_Boolean(p_called_by_form) THEN
445 APP_EXCEPTION.Raise_Exception;
446 ELSE
447 x_msg_data := FND_MESSAGE.Get;
448 END IF;
449
450 WHEN OTHERS THEN
451 ROLLBACK TO SAVEPOINT Lock_Row;
452 x_return_status := 'U';
453 x_oracle_error := APP_EXCEPTION.Get_Code;
454 l_msg_data := APP_EXCEPTION.Get_Text;
455 FND_MESSAGE.SET_NAME('GR',
456 'GR_UNEXPECTED_ERROR');
457 FND_MESSAGE.SET_TOKEN('TEXT',
458 l_msg_token,
459 FALSE);
460 IF FND_API.To_Boolean(p_called_by_form) THEN
461 APP_EXCEPTION.Raise_Exception;
462 ELSE
463 x_msg_data := FND_MESSAGE.Get;
464 END IF;
465
466 END Lock_Row;
467
468 PROCEDURE Delete_Row
469 (p_commit IN VARCHAR2,
470 p_called_by_form IN VARCHAR2,
471 p_rowid IN VARCHAR2,
472 p_item_code IN VARCHAR2,
473 p_disclosure_code IN VARCHAR2,
474 p_print_on_document_flag IN VARCHAR2,
475 p_minimum_reporting_level IN NUMBER,
476 p_text_reporting_level IN NUMBER,
477 p_label_reporting_level IN NUMBER,
478 p_exposure_reporting_level IN NUMBER,
479 p_toxicity_reporting_level IN NUMBER,
480 p_created_by IN NUMBER,
481 p_creation_date IN DATE,
482 p_last_update_date IN DATE,
483 p_last_updated_by IN NUMBER,
484 p_last_update_login IN NUMBER,
485 x_return_status OUT NOCOPY VARCHAR2,
486 x_oracle_error OUT NOCOPY NUMBER,
487 x_msg_data OUT NOCOPY VARCHAR2)
488 IS
489
490 /* Alpha Variables */
491
492 L_RETURN_STATUS VARCHAR2(1) := 'S';
493 L_MSG_DATA VARCHAR2(2000);
494 L_MSG_TOKEN VARCHAR2(80);
495 L_CALLED_BY_FORM VARCHAR2(1);
496
497 /* Number Variables */
498
499 L_ORACLE_ERROR NUMBER;
500
501 /* Exceptions */
502
503 CHECK_INTEGRITY_ERROR EXCEPTION;
504 ROW_MISSING_ERROR EXCEPTION;
505 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
506 BEGIN
507
508 /* Initialization Routine */
509
510 SAVEPOINT Delete_Row;
511 x_return_status := 'S';
512 l_called_by_form := 'F';
513 x_oracle_error := 0;
514 x_msg_data := NULL;
515 l_msg_token := p_item_code || ' ' || p_disclosure_code;
516
517 /* Now call the check integrity procedure */
518
519 Check_Integrity
520 (p_called_by_form,
521 p_item_code,
522 p_disclosure_code,
523 p_print_on_document_flag,
524 p_minimum_reporting_level,
525 p_text_reporting_level,
526 p_label_reporting_level,
527 p_exposure_reporting_level,
528 p_toxicity_reporting_level,
529 p_created_by,
530 p_creation_date,
531 p_last_update_date,
532 p_last_updated_by,
533 p_last_update_login,
534 l_return_status,
535 l_oracle_error,
536 l_msg_data);
537
538 IF l_return_status <> 'S' THEN
539 RAISE Check_Integrity_Error;
540 END IF;
541
542 DELETE FROM gr_item_disclosures
543 WHERE rowid = p_rowid;
544
545 /* Check the commit flag and if set, then commit the work. */
546
547 IF FND_API.TO_Boolean(p_commit) THEN
548 COMMIT WORK;
549 END IF;
550
551 EXCEPTION
552
553 WHEN Check_Integrity_Error THEN
554 ROLLBACK TO SAVEPOINT Delete_Row;
555 x_return_status := l_return_status;
556 x_oracle_error := l_oracle_error;
557 x_msg_data := l_msg_data;
558 IF FND_API.To_Boolean(p_called_by_form) THEN
559 APP_EXCEPTION.Raise_Exception;
560 END IF;
561
562 WHEN Row_Missing_Error THEN
563 ROLLBACK TO SAVEPOINT Delete_Row;
564 x_return_status := 'E';
565 x_oracle_error := APP_EXCEPTION.Get_Code;
566 FND_MESSAGE.SET_NAME('GR',
567 'GR_RECORD_NOT_FOUND');
568 FND_MESSAGE.SET_TOKEN('CODE',
569 l_msg_token,
570 FALSE);
571 IF FND_API.To_Boolean(p_called_by_form) THEN
572 APP_EXCEPTION.Raise_Exception;
573 ELSE
574 x_msg_data := FND_MESSAGE.Get;
575 END IF;
576
577 WHEN OTHERS THEN
578 ROLLBACK TO SAVEPOINT Delete_Row;
579 x_return_status := 'U';
580 x_oracle_error := APP_EXCEPTION.Get_Code;
581 l_msg_data := APP_EXCEPTION.Get_Text;
582 l_msg_data := APP_EXCEPTION.Get_Text;
583 FND_MESSAGE.SET_NAME('GR',
584 'GR_UNEXPECTED_ERROR');
585 FND_MESSAGE.SET_TOKEN('TEXT',
586 l_msg_token,
587 FALSE);
588 IF FND_API.To_Boolean(p_called_by_form) THEN
589 APP_EXCEPTION.Raise_Exception;
590 ELSE
591 x_msg_data := FND_MESSAGE.Get;
592 END IF;
596 PROCEDURE Delete_Rows
593
594 END Delete_Row;
595
597 (p_commit IN VARCHAR2,
598 p_called_by_form IN VARCHAR2,
599 p_item_code IN VARCHAR2,
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_oracle_error OUT NOCOPY NUMBER,
602 x_msg_data OUT NOCOPY VARCHAR2)
603 IS
604
605 /* Alpha Variables */
606
607 L_RETURN_STATUS VARCHAR2(1) := 'S';
608 L_MSG_DATA VARCHAR2(2000);
609 L_MSG_TOKEN VARCHAR2(80);
610
611 /* Number Variables */
612
613 L_ORACLE_ERROR NUMBER;
614
615 /* Define the cursors */
616
617 BEGIN
618
619 /* Initialization Routine */
620
621 SAVEPOINT Delete_Rows;
622 x_return_status := 'S';
623 x_oracle_error := 0;
624 x_msg_data := NULL;
625 l_msg_token := p_item_code;
626
627 DELETE FROM gr_item_disclosures
628 WHERE item_code = p_item_code;
629
630 IF FND_API.To_Boolean(p_commit) THEN
631 COMMIT WORK;
632 END IF;
633
634 EXCEPTION
635
636 WHEN OTHERS THEN
637 ROLLBACK TO SAVEPOINT Delete_Rows;
638 x_return_status := 'U';
639 x_oracle_error := APP_EXCEPTION.Get_Code;
640 l_msg_data := APP_EXCEPTION.Get_Text;
641 FND_MESSAGE.SET_NAME('GR',
642 'GR_UNEXPECTED_ERROR');
643 FND_MESSAGE.SET_TOKEN('TEXT',
644 l_msg_token,
645 FALSE);
646 IF FND_API.To_Boolean(p_called_by_form) THEN
647 APP_EXCEPTION.Raise_Exception;
648 ELSE
649 x_msg_data := FND_MESSAGE.Get;
650 END IF;
651
652 END Delete_Rows;
653
654 PROCEDURE Check_Foreign_Keys
655 (p_item_code IN VARCHAR2,
656 p_disclosure_code IN VARCHAR2,
657 p_print_on_document_flag IN VARCHAR2,
658 p_minimum_reporting_level IN NUMBER,
659 p_text_reporting_level IN NUMBER,
660 p_label_reporting_level IN NUMBER,
661 p_exposure_reporting_level IN NUMBER,
662 p_toxicity_reporting_level IN NUMBER,
663 p_created_by IN NUMBER,
664 p_creation_date IN DATE,
665 p_last_update_date IN DATE,
666 p_last_updated_by IN NUMBER,
667 p_last_update_login IN NUMBER,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_oracle_error OUT NOCOPY NUMBER,
670 x_msg_data OUT NOCOPY VARCHAR2)
671 IS
672
673 /* Alpha Variables */
674
675 L_RETURN_STATUS VARCHAR2(1) := 'S';
676 L_MSG_DATA VARCHAR2(2000);
677 L_MSG_TOKEN VARCHAR2(80);
678 L_ROWID VARCHAR2(18);
679 L_KEY_EXISTS VARCHAR2(1);
680
681 /* Number Variables */
682
683 L_ORACLE_ERROR NUMBER;
684
685 /* Define the cursors */
686
687 CURSOR c_get_disc_code
688 IS
689 SELECT dc.disclosure_code
690 FROM gr_disclosures dc
691 WHERE dc.disclosure_code = p_disclosure_code;
692 DiscRecord c_get_disc_code%ROWTYPE;
693
694 BEGIN
695
696 /* Initialization Routine */
697
698 SAVEPOINT Check_Foreign_Keys;
699 x_return_status := 'S';
700 x_oracle_error := 0;
701 x_msg_data := NULL;
702 l_msg_token := p_item_code || ' ' || p_disclosure_code;
703
704 /* Item General */
705
706 GR_ITEM_GENERAL_PKG.Check_Primary_Key
707 (p_item_code,
708 'F',
709 l_rowid,
710 l_key_exists);
711
712 IF NOT FND_API.To_Boolean(l_key_exists) THEN
713 x_return_status := 'E';
714 FND_MESSAGE.SET_NAME('GR',
715 'GR_RECORD_NOT_FOUND');
716 FND_MESSAGE.SET_TOKEN('CODE',
717 p_item_code,
718 FALSE);
719 l_msg_data := FND_MESSAGE.Get;
720 END IF;
721
722 /* Disclosure Code */
723
724 OPEN c_get_disc_code;
725 FETCH c_get_disc_code INTO DiscRecord;
726 IF c_get_disc_code%NOTFOUND THEN
727 x_return_status := 'E';
728 FND_MESSAGE.SET_NAME('GR',
729 'GR_RECORD_NOT_FOUND');
730 FND_MESSAGE.SET_TOKEN('CODE',
731 p_disclosure_code,
732 FALSE);
733 l_msg_data := l_msg_data || ' ' || FND_MESSAGE.Get;
734 END IF;
735 CLOSE c_get_disc_code;
736
737 IF x_return_status <> 'S' THEN
738 x_msg_data := l_msg_data;
739 END IF;
740
741 EXCEPTION
742
743 WHEN OTHERS THEN
744 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
745 x_return_status := 'U';
746 x_oracle_error := APP_EXCEPTION.Get_Code;
747 l_msg_data := APP_EXCEPTION.Get_Text;
748 FND_MESSAGE.SET_NAME('GR',
749 'GR_UNEXPECTED_ERROR');
750 FND_MESSAGE.SET_TOKEN('TEXT',
751 l_msg_token,
752 FALSE);
753 x_msg_data := FND_MESSAGE.Get;
754
755 END Check_Foreign_Keys;
756
757 PROCEDURE Check_Integrity
758 (p_called_by_form IN VARCHAR2,
759 p_item_code IN VARCHAR2,
760 p_disclosure_code IN VARCHAR2,
761 p_print_on_document_flag IN VARCHAR2,
762 p_minimum_reporting_level IN NUMBER,
763 p_text_reporting_level IN NUMBER,
764 p_label_reporting_level IN NUMBER,
765 p_exposure_reporting_level IN NUMBER,
766 p_toxicity_reporting_level IN NUMBER,
767 p_created_by IN NUMBER,
768 p_creation_date IN DATE,
769 p_last_update_date IN DATE,
770 p_last_updated_by IN NUMBER,
771 p_last_update_login IN NUMBER,
772 x_return_status OUT NOCOPY VARCHAR2,
773 x_oracle_error OUT NOCOPY NUMBER,
774 x_msg_data OUT NOCOPY VARCHAR2)
775 IS
776
777 /* Alpha Variables */
778
779 L_RETURN_STATUS VARCHAR2(1) := 'S';
783 /* Number Variables */
780 L_MSG_DATA VARCHAR2(2000);
781 L_CODE_BLOCK VARCHAR2(30);
782
784
785 L_ORACLE_ERROR NUMBER;
786 L_RECORD_COUNT NUMBER;
787
788 /* Define the Cursors */
789
790
791 BEGIN
792
793 /* Initialization Routine */
794
795 SAVEPOINT Check_Integrity;
796 x_return_status := 'S';
797 x_oracle_error := 0;
798 x_msg_data := NULL;
799
800 /* No integrity checking is needed */
801
802
803 EXCEPTION
804
805 WHEN OTHERS THEN
806 ROLLBACK TO SAVEPOINT Check_Integrity;
807 x_return_status := 'U';
808 x_oracle_error := APP_EXCEPTION.Get_Code;
809 l_msg_data := APP_EXCEPTION.Get_Text;
810 FND_MESSAGE.SET_NAME('GR',
811 'GR_UNEXPECTED_ERROR');
812 FND_MESSAGE.SET_TOKEN('TEXT',
813 l_msg_data,
814 FALSE);
815 IF FND_API.To_Boolean(p_called_by_form) THEN
816 APP_EXCEPTION.Raise_Exception;
817 ELSE
818 x_msg_data := FND_MESSAGE.Get;
819 END IF;
820
821 END Check_Integrity;
822
823 PROCEDURE Check_Primary_Key
824 /* p_item_code is the item code to check.
825 ** p_disclosure_code is the second part of the key
826 ** p_called_by_form is 'T' if called by a form or 'F' if not.
827 ** x_rowid is the row id of the record if found.
828 ** x_key_exists is 'T' is the record is found, 'F' if not.
829 */
830 (p_item_code IN VARCHAR2,
831 p_disclosure_code IN VARCHAR2,
832 p_called_by_form IN VARCHAR2,
833 x_rowid OUT NOCOPY VARCHAR2,
834 x_key_exists OUT NOCOPY VARCHAR2)
835 IS
836 /* Alphanumeric variables */
837
838 L_MSG_DATA VARCHAR2(80);
839
840 /* Declare any variables and the cursor */
841
842
843 CURSOR c_get_disclosure_rowid
844 IS
845 SELECT dc.rowid
846 FROM gr_item_disclosures dc
847 WHERE dc.item_code = p_item_code
848 AND dc.disclosure_code = p_disclosure_code;
849 DisclosureRecord c_get_disclosure_rowid%ROWTYPE;
850
851 BEGIN
852
853 l_msg_data := p_item_code || ' ' || p_disclosure_code;
854
855 x_key_exists := 'F';
856 OPEN c_get_disclosure_rowid;
857 FETCH c_get_disclosure_rowid INTO DisclosureRecord;
858 IF c_get_disclosure_rowid%FOUND THEN
859 x_key_exists := 'T';
860 x_rowid := DisclosureRecord.rowid;
861 ELSE
862 x_key_exists := 'F';
863 END IF;
864 CLOSE c_get_disclosure_rowid;
865
866 EXCEPTION
867
868 WHEN Others THEN
869 l_msg_data := APP_EXCEPTION.Get_Text;
870 FND_MESSAGE.SET_NAME('GR',
871 'GR_UNEXPECTED_ERROR');
872 FND_MESSAGE.SET_TOKEN('TEXT',
873 l_msg_data,
874 FALSE);
875 IF FND_API.To_Boolean(p_called_by_form) THEN
876 APP_EXCEPTION.Raise_Exception;
877 END IF;
878
879 END Check_Primary_Key;
880
881 END GR_ITEM_DISCLOSURES_PKG;