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