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