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