[Home] [Help]
PACKAGE BODY: APPS.GR_EMEA_PKG
Source
1 PACKAGE BODY GR_EMEA_PKG AS
2 /*$Header: GRHIIEUB.pls 115.8 2002/10/30 19:59:16 mgrosser 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_european_index_number IN VARCHAR2,
8 p_eec_number IN VARCHAR2,
9 p_consolidated_risk_phrase IN VARCHAR2,
10 p_consolidated_safety_phrase IN VARCHAR2,
11 p_approved_supply_list_conc IN VARCHAR2,
12 p_attribute_category IN VARCHAR2,
13 p_attribute1 IN VARCHAR2,
14 p_attribute2 IN VARCHAR2,
15 p_attribute3 IN VARCHAR2,
16 p_attribute4 IN VARCHAR2,
17 p_attribute5 IN VARCHAR2,
18 p_attribute6 IN VARCHAR2,
19 p_attribute7 IN VARCHAR2,
20 p_attribute8 IN VARCHAR2,
21 p_attribute9 IN VARCHAR2,
22 p_attribute10 IN VARCHAR2,
23 p_attribute11 IN VARCHAR2,
24 p_attribute12 IN VARCHAR2,
25 p_attribute13 IN VARCHAR2,
26 p_attribute14 IN VARCHAR2,
27 p_attribute15 IN VARCHAR2,
28 p_attribute16 IN VARCHAR2,
29 p_attribute17 IN VARCHAR2,
30 p_attribute18 IN VARCHAR2,
31 p_attribute19 IN VARCHAR2,
32 p_attribute20 IN VARCHAR2,
33 p_attribute21 IN VARCHAR2,
34 p_attribute22 IN VARCHAR2,
35 p_attribute23 IN VARCHAR2,
36 p_attribute24 IN VARCHAR2,
37 p_attribute25 IN VARCHAR2,
38 p_attribute26 IN VARCHAR2,
39 p_attribute27 IN VARCHAR2,
40 p_attribute28 IN VARCHAR2,
41 p_attribute29 IN VARCHAR2,
42 p_attribute30 IN VARCHAR2,
43 p_created_by IN NUMBER,
44 p_creation_date IN DATE,
45 p_last_updated_by IN NUMBER,
46 p_last_update_date IN DATE,
47 p_last_update_login IN NUMBER,
48 x_rowid OUT NOCOPY VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_oracle_error OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2)
52 IS
53 /* Alpha Variables */
54
55 L_RETURN_STATUS VARCHAR2(1) := 'S';
56 L_KEY_EXISTS VARCHAR2(1);
57 L_MSG_DATA VARCHAR2(2000);
58 L_ROWID VARCHAR2(18);
59
60 /* Number Variables */
61
62 L_ORACLE_ERROR NUMBER;
63
64 /* Exceptions */
65
66 FOREIGN_KEY_ERROR EXCEPTION;
67 ITEM_EXISTS_ERROR EXCEPTION;
68 ROW_MISSING_ERROR EXCEPTION;
69
70 /* Declare cursors */
71
72
73 BEGIN
74
75 /* Initialization Routine */
76
77 SAVEPOINT Insert_Row;
78 x_return_status := 'S';
79 x_oracle_error := 0;
80 x_msg_data := NULL;
81
82 /* Now call the check foreign key procedure */
83
84 Check_Foreign_Keys
85 (p_item_code,
86 p_european_index_number,
87 p_eec_number,
88 p_consolidated_risk_phrase,
89 p_consolidated_safety_phrase,
90 p_approved_supply_list_conc,
91 p_attribute_category,
92 p_attribute1,
93 p_attribute2,
94 p_attribute3,
95 p_attribute4,
96 p_attribute5,
97 p_attribute6,
98 p_attribute7,
99 p_attribute8,
100 p_attribute9,
101 p_attribute10,
102 p_attribute11,
103 p_attribute12,
104 p_attribute13,
105 p_attribute14,
106 p_attribute15,
107 p_attribute16,
108 p_attribute17,
109 p_attribute18,
110 p_attribute19,
111 p_attribute20,
112 p_attribute21,
113 p_attribute22,
114 p_attribute23,
115 p_attribute24,
116 p_attribute25,
117 p_attribute26,
118 p_attribute27,
119 p_attribute28,
120 p_attribute29,
121 p_attribute30,
122 l_return_status,
123 l_oracle_error,
124 l_msg_data);
125 IF l_return_status <> 'S' THEN
126 RAISE Foreign_Key_Error;
127 END IF;
128
129 /* Now check the primary key doesn't already exist */
130
131 Check_Primary_Key
132 (p_item_code,
133 'F',
134 l_rowid,
135 l_key_exists);
136
137 IF FND_API.To_Boolean(l_key_exists) THEN
138 RAISE Item_Exists_Error;
139 END IF;
140
141 INSERT INTO gr_emea
142 (item_code,
143 european_index_number,
144 eec_number,
145 consolidated_risk_phrase,
146 consolidated_safety_phrase,
147 approved_supply_list_conc,
148 attribute_category,
149 attribute1,
150 attribute2,
151 attribute3,
152 attribute4,
153 attribute5,
154 attribute6,
155 attribute7,
156 attribute8,
157 attribute9,
158 attribute10,
159 attribute11,
160 attribute12,
161 attribute13,
162 attribute14,
163 attribute15,
164 attribute16,
165 attribute17,
166 attribute18,
167 attribute19,
168 attribute20,
169 attribute21,
170 attribute22,
171 attribute23,
172 attribute24,
173 attribute25,
174 attribute26,
175 attribute27,
176 attribute28,
177 attribute29,
178 attribute30,
179 created_by,
180 creation_date,
181 last_updated_by,
182 last_update_date,
183 last_update_login)
184 VALUES
185 (p_item_code,
186 p_european_index_number,
187 p_eec_number,
188 p_consolidated_risk_phrase,
189 p_consolidated_safety_phrase,
190 p_approved_supply_list_conc,
191 p_attribute_category,
192 p_attribute1,
193 p_attribute2,
194 p_attribute3,
195 p_attribute4,
196 p_attribute5,
197 p_attribute6,
198 p_attribute7,
199 p_attribute8,
200 p_attribute9,
201 p_attribute10,
202 p_attribute11,
203 p_attribute12,
204 p_attribute13,
205 p_attribute14,
206 p_attribute15,
207 p_attribute16,
208 p_attribute17,
209 p_attribute18,
210 p_attribute19,
211 p_attribute20,
212 p_attribute21,
213 p_attribute22,
214 p_attribute23,
215 p_attribute24,
216 p_attribute25,
217 p_attribute26,
218 p_attribute27,
219 p_attribute28,
220 p_attribute29,
221 p_attribute30,
222 p_created_by,
223 p_creation_date,
224 p_last_updated_by,
225 p_last_update_date,
226 p_last_update_login);
227
228 /* Now get the row id of the inserted record */
229
230 Check_Primary_Key
231 (p_item_code,
232 'F',
233 l_rowid,
234 l_key_exists);
235
236 IF FND_API.To_Boolean(l_key_exists) THEN
237 x_rowid := l_rowid;
238 ELSE
239 RAISE Row_Missing_Error;
240 END IF;
241
242 /* Check the commit flag and if set, then commit the work. */
243
244 IF FND_API.To_Boolean(p_commit) THEN
245 COMMIT WORK;
246 END IF;
247
248 EXCEPTION
249
250 WHEN Foreign_Key_Error THEN
251 ROLLBACK TO SAVEPOINT Insert_Row;
252 x_return_status := l_return_status;
253 x_oracle_error := l_oracle_error;
254 FND_MESSAGE.SET_NAME('GR',
255 'GR_FOREIGN_KEY_ERROR');
256 FND_MESSAGE.SET_TOKEN('TEXT',
257 l_msg_data,
258 FALSE);
259 IF FND_API.To_Boolean(p_called_by_form) THEN
260 APP_EXCEPTION.Raise_Exception;
261 ELSE
262 x_msg_data := FND_MESSAGE.Get;
263 END IF;
264
265 WHEN Item_Exists_Error THEN
266 ROLLBACK TO SAVEPOINT Insert_Row;
267 x_return_status := 'E';
268 x_oracle_error := APP_EXCEPTION.Get_Code;
269 FND_MESSAGE.SET_NAME('GR',
270 'GR_RECORD_EXISTS');
271 FND_MESSAGE.SET_TOKEN('CODE',
272 p_item_code,
273 FALSE);
274 IF FND_API.To_Boolean(p_called_by_form) THEN
275 APP_EXCEPTION.Raise_Exception;
276 ELSE
277 x_msg_data := FND_MESSAGE.Get;
278 END IF;
279
280 WHEN Row_Missing_Error THEN
281 ROLLBACK TO SAVEPOINT Insert_Row;
282 x_return_status := 'E';
283 x_oracle_error := APP_EXCEPTION.Get_Code;
284 FND_MESSAGE.SET_NAME('GR',
285 'GR_NO_RECORD_INSERTED');
286 FND_MESSAGE.SET_TOKEN('CODE',
287 p_item_code,
288 FALSE);
289 IF FND_API.To_Boolean(p_called_by_form) THEN
290 APP_EXCEPTION.Raise_Exception;
291 ELSE
292 x_msg_data := FND_MESSAGE.Get;
293 END IF;
294
295 WHEN OTHERS THEN
296 ROLLBACK TO SAVEPOINT Insert_Row;
297 x_return_status := 'U';
298 x_oracle_error := APP_EXCEPTION.Get_Code;
299 l_msg_data := APP_EXCEPTION.Get_Text;
300 FND_MESSAGE.SET_NAME('GR',
301 'GR_UNEXPECTED_ERROR');
302 FND_MESSAGE.SET_TOKEN('TEXT',
303 l_msg_data,
304 FALSE);
305 IF FND_API.To_Boolean(p_called_by_form) THEN
306 APP_EXCEPTION.Raise_Exception;
307 ELSE
308 x_msg_data := FND_MESSAGE.Get;
309 END IF;
310
311 END Insert_Row;
312
313 PROCEDURE Update_Row
314 (p_commit IN VARCHAR2,
315 p_called_by_form IN VARCHAR2,
316 p_rowid IN VARCHAR2,
317 p_item_code IN VARCHAR2,
318 p_european_index_number IN VARCHAR2,
319 p_eec_number IN VARCHAR2,
320 p_consolidated_risk_phrase IN VARCHAR2,
321 p_consolidated_safety_phrase IN VARCHAR2,
322 p_approved_supply_list_conc IN VARCHAR2,
323 p_attribute_category IN VARCHAR2,
324 p_attribute1 IN VARCHAR2,
325 p_attribute2 IN VARCHAR2,
326 p_attribute3 IN VARCHAR2,
327 p_attribute4 IN VARCHAR2,
328 p_attribute5 IN VARCHAR2,
329 p_attribute6 IN VARCHAR2,
330 p_attribute7 IN VARCHAR2,
331 p_attribute8 IN VARCHAR2,
332 p_attribute9 IN VARCHAR2,
333 p_attribute10 IN VARCHAR2,
334 p_attribute11 IN VARCHAR2,
335 p_attribute12 IN VARCHAR2,
336 p_attribute13 IN VARCHAR2,
337 p_attribute14 IN VARCHAR2,
338 p_attribute15 IN VARCHAR2,
339 p_attribute16 IN VARCHAR2,
340 p_attribute17 IN VARCHAR2,
341 p_attribute18 IN VARCHAR2,
342 p_attribute19 IN VARCHAR2,
343 p_attribute20 IN VARCHAR2,
344 p_attribute21 IN VARCHAR2,
345 p_attribute22 IN VARCHAR2,
346 p_attribute23 IN VARCHAR2,
347 p_attribute24 IN VARCHAR2,
348 p_attribute25 IN VARCHAR2,
349 p_attribute26 IN VARCHAR2,
350 p_attribute27 IN VARCHAR2,
351 p_attribute28 IN VARCHAR2,
352 p_attribute29 IN VARCHAR2,
353 p_attribute30 IN VARCHAR2,
354 p_created_by IN NUMBER,
355 p_creation_date IN DATE,
356 p_last_updated_by IN NUMBER,
357 p_last_update_date IN DATE,
358 p_last_update_login IN NUMBER,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_oracle_error OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2)
362 IS
363
364 /* Alpha Variables */
365
366 L_RETURN_STATUS VARCHAR2(1) := 'S';
367 L_MSG_DATA VARCHAR2(2000);
368
369 /* Number Variables */
370
371 L_ORACLE_ERROR NUMBER;
372
373 /* Exceptions */
374
375 FOREIGN_KEY_ERROR EXCEPTION;
376 ROW_MISSING_ERROR EXCEPTION;
377 BEGIN
378
379 /* Initialization Routine */
380
381 SAVEPOINT Update_Row;
382 x_return_status := 'S';
383 x_oracle_error := 0;
384 x_msg_data := NULL;
385
386 /* Now call the check foreign key procedure */
387
388 Check_Foreign_Keys
389 (p_item_code,
390 p_european_index_number,
391 p_eec_number,
392 p_consolidated_risk_phrase,
393 p_consolidated_safety_phrase,
394 p_approved_supply_list_conc,
395 p_attribute_category,
396 p_attribute1,
397 p_attribute2,
398 p_attribute3,
399 p_attribute4,
400 p_attribute5,
401 p_attribute6,
402 p_attribute7,
403 p_attribute8,
404 p_attribute9,
405 p_attribute10,
406 p_attribute11,
407 p_attribute12,
408 p_attribute13,
409 p_attribute14,
410 p_attribute15,
411 p_attribute16,
412 p_attribute17,
413 p_attribute18,
414 p_attribute19,
415 p_attribute20,
416 p_attribute21,
417 p_attribute22,
418 p_attribute23,
419 p_attribute24,
420 p_attribute25,
421 p_attribute26,
422 p_attribute27,
423 p_attribute28,
424 p_attribute29,
425 p_attribute30,
426 l_return_status,
427 l_oracle_error,
428 l_msg_data);
429
430 IF l_return_status <> 'S' THEN
431 RAISE Foreign_Key_Error;
432 ELSE
433 UPDATE gr_emea
434 SET item_code = p_item_code,
435 european_index_number = p_european_index_number,
436 eec_number = p_eec_number,
437 consolidated_risk_phrase = p_consolidated_risk_phrase,
438 consolidated_safety_phrase = p_consolidated_safety_phrase,
439 approved_supply_list_conc = p_approved_supply_list_conc,
440 attribute_category = p_attribute_category,
441 attribute1 = p_attribute1,
442 attribute2 = p_attribute2,
443 attribute3 = p_attribute3,
444 attribute4 = p_attribute4,
445 attribute5 = p_attribute5,
446 attribute6 = p_attribute6,
447 attribute7 = p_attribute7,
448 attribute8 = p_attribute8,
449 attribute9 = p_attribute9,
450 attribute10 = p_attribute10,
451 attribute11 = p_attribute11,
452 attribute12 = p_attribute12,
453 attribute13 = p_attribute13,
454 attribute14 = p_attribute14,
455 attribute15 = p_attribute15,
456 attribute16 = p_attribute16,
457 attribute17 = p_attribute17,
458 attribute18 = p_attribute18,
459 attribute19 = p_attribute19,
460 attribute20 = p_attribute20,
461 attribute21 = p_attribute11,
462 attribute22 = p_attribute22,
463 attribute23 = p_attribute23,
464 attribute24 = p_attribute24,
465 attribute25 = p_attribute25,
466 attribute26 = p_attribute26,
467 attribute27 = p_attribute27,
468 attribute28 = p_attribute28,
469 attribute29 = p_attribute29,
470 attribute30 = p_attribute30,
471 created_by = p_created_by,
472 creation_date = p_creation_date,
473 last_updated_by = p_last_updated_by,
474 last_update_date = p_last_update_date,
475 last_update_login = p_last_update_login
476 WHERE rowid = p_rowid;
477 IF SQL%NOTFOUND THEN
481
478 RAISE Row_Missing_Error;
479 END IF;
480 END IF;
482 /* Check the commit flag and if set, then commit the work. */
483
484 IF FND_API.To_Boolean(p_commit) THEN
485 COMMIT WORK;
486 END IF;
487
488 EXCEPTION
489
490 WHEN Foreign_Key_Error THEN
491 ROLLBACK TO SAVEPOINT Update_Row;
492 x_return_status := l_return_status;
493 x_oracle_error := l_oracle_error;
494 FND_MESSAGE.SET_NAME('GR',
495 'GR_FOREIGN_KEY_ERROR');
496 FND_MESSAGE.SET_TOKEN('TEXT',
497 l_msg_data,
498 FALSE);
499 IF FND_API.To_Boolean(p_called_by_form) THEN
500 APP_EXCEPTION.Raise_Exception;
501 ELSE
502 x_msg_data := FND_MESSAGE.Get;
503 END IF;
504
505 WHEN Row_Missing_Error THEN
506 ROLLBACK TO SAVEPOINT Update_Row;
507 x_return_status := 'E';
508 x_oracle_error := APP_EXCEPTION.Get_Code;
509 FND_MESSAGE.SET_NAME('GR',
510 'GR_NO_RECORD_INSERTED');
511 FND_MESSAGE.SET_TOKEN('CODE',
512 p_item_code,
513 FALSE);
514 IF FND_API.To_Boolean(p_called_by_form) THEN
515 APP_EXCEPTION.Raise_Exception;
516 ELSE
517 x_msg_data := FND_MESSAGE.Get;
518 END IF;
519
520 WHEN OTHERS THEN
521 ROLLBACK TO SAVEPOINT Update_Row;
522 x_return_status := 'U';
523 x_oracle_error := APP_EXCEPTION.Get_Code;
524 l_msg_data := APP_EXCEPTION.Get_Text;
525 FND_MESSAGE.SET_NAME('GR',
526 'GR_UNEXPECTED_ERROR');
527 FND_MESSAGE.SET_TOKEN('TEXT',
528 l_msg_data,
529 FALSE);
530 IF FND_API.To_Boolean(p_called_by_form) THEN
531 APP_EXCEPTION.Raise_Exception;
532 ELSE
533 x_msg_data := FND_MESSAGE.Get;
534 END IF;
535
536 END Update_Row;
537
538 PROCEDURE Lock_Row
539 (p_commit IN VARCHAR2,
540 p_called_by_form IN VARCHAR2,
541 p_rowid IN VARCHAR2,
542 p_item_code IN VARCHAR2,
543 p_european_index_number IN VARCHAR2,
544 p_eec_number IN VARCHAR2,
545 p_consolidated_risk_phrase IN VARCHAR2,
546 p_consolidated_safety_phrase IN VARCHAR2,
547 p_approved_supply_list_conc IN VARCHAR2,
548 p_attribute_category IN VARCHAR2,
549 p_attribute1 IN VARCHAR2,
550 p_attribute2 IN VARCHAR2,
551 p_attribute3 IN VARCHAR2,
552 p_attribute4 IN VARCHAR2,
553 p_attribute5 IN VARCHAR2,
554 p_attribute6 IN VARCHAR2,
555 p_attribute7 IN VARCHAR2,
556 p_attribute8 IN VARCHAR2,
557 p_attribute9 IN VARCHAR2,
558 p_attribute10 IN VARCHAR2,
559 p_attribute11 IN VARCHAR2,
560 p_attribute12 IN VARCHAR2,
561 p_attribute13 IN VARCHAR2,
562 p_attribute14 IN VARCHAR2,
563 p_attribute15 IN VARCHAR2,
564 p_attribute16 IN VARCHAR2,
565 p_attribute17 IN VARCHAR2,
566 p_attribute18 IN VARCHAR2,
567 p_attribute19 IN VARCHAR2,
568 p_attribute20 IN VARCHAR2,
569 p_attribute21 IN VARCHAR2,
570 p_attribute22 IN VARCHAR2,
571 p_attribute23 IN VARCHAR2,
572 p_attribute24 IN VARCHAR2,
573 p_attribute25 IN VARCHAR2,
574 p_attribute26 IN VARCHAR2,
575 p_attribute27 IN VARCHAR2,
576 p_attribute28 IN VARCHAR2,
577 p_attribute29 IN VARCHAR2,
578 p_attribute30 IN VARCHAR2,
579 p_created_by IN NUMBER,
580 p_creation_date IN DATE,
581 p_last_updated_by IN NUMBER,
582 p_last_update_date IN DATE,
583 p_last_update_login IN NUMBER,
584 x_return_status OUT NOCOPY VARCHAR2,
585 x_oracle_error OUT NOCOPY NUMBER,
586 x_msg_data OUT NOCOPY VARCHAR2)
587 IS
588
589 /* Alpha Variables */
590
591 L_RETURN_STATUS VARCHAR2(1) := 'S';
592 L_MSG_DATA VARCHAR2(2000);
593
594 /* Number Variables */
595
596 L_ORACLE_ERROR NUMBER;
597
598 /* Exceptions */
599
600 NO_DATA_FOUND_ERROR EXCEPTION;
601 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
602 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
603
604 /* Define the cursors */
605
606 CURSOR c_lock_item
607 IS
608 SELECT *
609 FROM gr_emea
610 WHERE rowid = p_rowid
611 FOR UPDATE NOWAIT;
612 LockItemRcd c_lock_item%ROWTYPE;
613 BEGIN
614
615 /* Initialization Routine */
616
617 SAVEPOINT Lock_Row;
618 x_return_status := 'S';
619 x_oracle_error := 0;
620 x_msg_data := NULL;
621
622 /* Now lock the record */
623
624 OPEN c_lock_item;
625 FETCH c_lock_item INTO LockItemRcd;
626 IF c_lock_item%NOTFOUND THEN
627 CLOSE c_lock_item;
628 RAISE No_Data_Found_Error;
629 END IF;
630 CLOSE c_lock_item;
631
632 IF FND_API.To_Boolean(p_commit) THEN
633 COMMIT WORK;
634 END IF;
635
636 EXCEPTION
637
641 FND_MESSAGE.SET_NAME('GR',
638 WHEN No_Data_Found_Error THEN
639 ROLLBACK TO SAVEPOINT Lock_Row;
640 x_return_status := 'E';
642 'GR_RECORD_NOT_FOUND');
643 FND_MESSAGE.SET_TOKEN('CODE',
644 p_item_code,
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 WHEN Row_Already_Locked_Error THEN
653 ROLLBACK TO SAVEPOINT Lock_Row;
654 x_return_status := 'E';
655 x_oracle_error := APP_EXCEPTION.Get_Code;
656 FND_MESSAGE.SET_NAME('GR',
657 'GR_ROW_IS_LOCKED');
658 IF FND_API.To_Boolean(p_called_by_form) THEN
659 APP_EXCEPTION.Raise_Exception;
660 ELSE
661 x_msg_data := FND_MESSAGE.Get;
662 END IF;
663
664 WHEN OTHERS THEN
665 ROLLBACK TO SAVEPOINT Lock_Row;
666 x_return_status := 'U';
667 x_oracle_error := APP_EXCEPTION.Get_Code;
668 l_msg_data := APP_EXCEPTION.Get_Text;
669 FND_MESSAGE.SET_NAME('GR',
670 'GR_UNEXPECTED_ERROR');
671 FND_MESSAGE.SET_TOKEN('TEXT',
672 l_msg_data,
673 FALSE);
674 IF FND_API.To_Boolean(p_called_by_form) THEN
675 APP_EXCEPTION.Raise_Exception;
676 ELSE
677 x_msg_data := FND_MESSAGE.Get;
678 END IF;
679
680 END Lock_Row;
681
682 PROCEDURE Delete_Row
683 (p_commit IN VARCHAR2,
684 p_called_by_form IN VARCHAR2,
685 p_rowid IN VARCHAR2,
686 p_item_code IN VARCHAR2,
687 p_european_index_number IN VARCHAR2,
688 p_eec_number IN VARCHAR2,
689 p_consolidated_risk_phrase IN VARCHAR2,
690 p_consolidated_safety_phrase IN VARCHAR2,
691 p_approved_supply_list_conc IN VARCHAR2,
692 p_attribute_category IN VARCHAR2,
693 p_attribute1 IN VARCHAR2,
694 p_attribute2 IN VARCHAR2,
695 p_attribute3 IN VARCHAR2,
696 p_attribute4 IN VARCHAR2,
697 p_attribute5 IN VARCHAR2,
698 p_attribute6 IN VARCHAR2,
699 p_attribute7 IN VARCHAR2,
700 p_attribute8 IN VARCHAR2,
701 p_attribute9 IN VARCHAR2,
702 p_attribute10 IN VARCHAR2,
703 p_attribute11 IN VARCHAR2,
704 p_attribute12 IN VARCHAR2,
705 p_attribute13 IN VARCHAR2,
706 p_attribute14 IN VARCHAR2,
707 p_attribute15 IN VARCHAR2,
708 p_attribute16 IN VARCHAR2,
709 p_attribute17 IN VARCHAR2,
710 p_attribute18 IN VARCHAR2,
711 p_attribute19 IN VARCHAR2,
712 p_attribute20 IN VARCHAR2,
713 p_attribute21 IN VARCHAR2,
714 p_attribute22 IN VARCHAR2,
715 p_attribute23 IN VARCHAR2,
716 p_attribute24 IN VARCHAR2,
717 p_attribute25 IN VARCHAR2,
718 p_attribute26 IN VARCHAR2,
719 p_attribute27 IN VARCHAR2,
720 p_attribute28 IN VARCHAR2,
721 p_attribute29 IN VARCHAR2,
722 p_attribute30 IN VARCHAR2,
723 p_created_by IN NUMBER,
724 p_creation_date IN DATE,
725 p_last_updated_by IN NUMBER,
726 p_last_update_date IN DATE,
727 p_last_update_login IN NUMBER,
728 x_return_status OUT NOCOPY VARCHAR2,
729 x_oracle_error OUT NOCOPY NUMBER,
730 x_msg_data OUT NOCOPY VARCHAR2)
731 IS
732
733 /* Alpha Variables */
734
735 L_RETURN_STATUS VARCHAR2(1) := 'S';
736 L_MSG_DATA VARCHAR2(2000);
737 L_CALLED_BY_FORM VARCHAR2(1);
738
739 /* Number Variables */
740
741 L_ORACLE_ERROR NUMBER;
742
743 /* Exceptions */
744
745 CHECK_INTEGRITY_ERROR EXCEPTION;
746 ROW_MISSING_ERROR EXCEPTION;
747 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
748
749 BEGIN
750
751 /* Initialization Routine */
752
753 SAVEPOINT Delete_Row;
754 x_return_status := 'S';
755 l_called_by_form := 'F';
756 x_oracle_error := 0;
757 x_msg_data := NULL;
758
759 /* Now call the check integrity procedure */
760
761 Check_Integrity
762 (l_called_by_form,
763 p_item_code,
764 p_european_index_number,
765 p_eec_number,
766 p_consolidated_risk_phrase,
767 p_consolidated_safety_phrase,
768 p_approved_supply_list_conc,
769 p_attribute_category,
770 p_attribute1,
771 p_attribute2,
772 p_attribute3,
773 p_attribute4,
774 p_attribute5,
775 p_attribute6,
776 p_attribute7,
777 p_attribute8,
778 p_attribute9,
779 p_attribute10,
780 p_attribute11,
781 p_attribute12,
782 p_attribute13,
783 p_attribute14,
784 p_attribute15,
785 p_attribute16,
786 p_attribute17,
787 p_attribute18,
788 p_attribute19,
789 p_attribute20,
790 p_attribute21,
791 p_attribute22,
792 p_attribute23,
793 p_attribute24,
797 p_attribute28,
794 p_attribute25,
795 p_attribute26,
796 p_attribute27,
798 p_attribute29,
799 p_attribute30,
800 l_return_status,
801 l_oracle_error,
802 l_msg_data);
803
804 IF l_return_status <> 'S' THEN
805 RAISE Check_Integrity_Error;
806 END IF;
807
808 DELETE FROM gr_emea
809 WHERE rowid = p_rowid;
810
811 /* Check the commit flag and if set, then commit the work. */
812
813 IF FND_API.TO_Boolean(p_commit) THEN
814 COMMIT WORK;
815 END IF;
816
817 EXCEPTION
818
819 WHEN Check_Integrity_Error THEN
820 ROLLBACK TO SAVEPOINT Delete_Row;
821 x_return_status := l_return_status;
822 x_oracle_error := l_oracle_error;
823 IF FND_API.To_Boolean(p_called_by_form) THEN
824 APP_EXCEPTION.Raise_Exception;
825 ELSE
826 x_msg_data := FND_MESSAGE.Get;
827 END IF;
828
829 WHEN Row_Missing_Error THEN
830 ROLLBACK TO SAVEPOINT Delete_Row;
831 x_return_status := 'E';
832 x_oracle_error := APP_EXCEPTION.Get_Code;
833 FND_MESSAGE.SET_NAME('GR',
834 'GR_RECORD_NOT_FOUND');
835 FND_MESSAGE.SET_TOKEN('CODE',
836 p_item_code,
837 FALSE);
838 IF FND_API.To_Boolean(p_called_by_form) THEN
839 APP_EXCEPTION.Raise_Exception;
840 ELSE
841 x_msg_data := FND_MESSAGE.Get;
842 END IF;
843
844 WHEN OTHERS THEN
845 ROLLBACK TO SAVEPOINT Delete_Row;
846 x_return_status := 'U';
847 x_oracle_error := APP_EXCEPTION.Get_Code;
848 l_msg_data := APP_EXCEPTION.Get_Text;
849 l_msg_data := APP_EXCEPTION.Get_Text;
850 FND_MESSAGE.SET_NAME('GR',
851 'GR_UNEXPECTED_ERROR');
852 FND_MESSAGE.SET_TOKEN('TEXT',
853 l_msg_data,
854 FALSE);
855 IF FND_API.To_Boolean(p_called_by_form) THEN
856 APP_EXCEPTION.Raise_Exception;
857 ELSE
858 x_msg_data := FND_MESSAGE.Get;
859 END IF;
860
861 END Delete_Row;
862
863 PROCEDURE Check_Foreign_Keys
864 (p_item_code IN VARCHAR2,
865 p_european_index_number IN VARCHAR2,
866 p_eec_number IN VARCHAR2,
867 p_consolidated_risk_phrase IN VARCHAR2,
868 p_consolidated_safety_phrase IN VARCHAR2,
869 p_approved_supply_list_conc IN VARCHAR2,
870 p_attribute_category IN VARCHAR2,
871 p_attribute1 IN VARCHAR2,
872 p_attribute2 IN VARCHAR2,
873 p_attribute3 IN VARCHAR2,
874 p_attribute4 IN VARCHAR2,
875 p_attribute5 IN VARCHAR2,
876 p_attribute6 IN VARCHAR2,
877 p_attribute7 IN VARCHAR2,
878 p_attribute8 IN VARCHAR2,
879 p_attribute9 IN VARCHAR2,
880 p_attribute10 IN VARCHAR2,
881 p_attribute11 IN VARCHAR2,
882 p_attribute12 IN VARCHAR2,
883 p_attribute13 IN VARCHAR2,
884 p_attribute14 IN VARCHAR2,
885 p_attribute15 IN VARCHAR2,
886 p_attribute16 IN VARCHAR2,
887 p_attribute17 IN VARCHAR2,
888 p_attribute18 IN VARCHAR2,
889 p_attribute19 IN VARCHAR2,
890 p_attribute20 IN VARCHAR2,
891 p_attribute21 IN VARCHAR2,
892 p_attribute22 IN VARCHAR2,
893 p_attribute23 IN VARCHAR2,
894 p_attribute24 IN VARCHAR2,
895 p_attribute25 IN VARCHAR2,
896 p_attribute26 IN VARCHAR2,
897 p_attribute27 IN VARCHAR2,
898 p_attribute28 IN VARCHAR2,
899 p_attribute29 IN VARCHAR2,
900 p_attribute30 IN VARCHAR2,
901 x_return_status OUT NOCOPY VARCHAR2,
902 x_oracle_error OUT NOCOPY NUMBER,
903 x_msg_data OUT NOCOPY VARCHAR2)
904 IS
905
906 /* Alpha Variables */
907
908 L_RETURN_STATUS VARCHAR2(1) := 'S';
909 L_MSG_DATA VARCHAR2(2000);
910 L_ROWID VARCHAR2(18);
911 L_KEY_EXISTS VARCHAR2(1);
912
913 /* Number Variables */
914
915 L_ORACLE_ERROR NUMBER;
916
917 /* Define the cursors */
918
919 BEGIN
920
921 /* Initialization Routine */
922
923 SAVEPOINT Check_Foreign_Keys;
924 x_return_status := 'S';
925 x_oracle_error := 0;
926 x_msg_data := NULL;
927
928 /* Check the item code */
929
930 GR_ITEM_GENERAL_PKG.Check_Primary_Key
931 (p_item_code,
932 'F',
933 l_rowid,
934 l_key_exists);
935
936 IF NOT FND_API.To_Boolean(l_key_exists) THEN
937 x_return_status := 'E';
938 FND_MESSAGE.SET_NAME('GR',
939 'GR_RECORD_NOT_FOUND');
940 FND_MESSAGE.SET_TOKEN('CODE',
941 p_item_code,
942 FALSE);
943 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
944 END IF;
945
946 /* Check the not null columns */
947
948 IF p_european_index_number IS NULL THEN
949 x_return_status := 'E';
950 FND_MESSAGE.SET_NAME('GR',
951 'GR_NULL_VALUE');
952 FND_MESSAGE.SET_TOKEN('CODE',
953 p_european_index_number,
954 FALSE);
955 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
956 END IF;
957
958 IF p_approved_supply_list_conc IS NULL THEN
959 x_return_status := 'E';
960 FND_MESSAGE.SET_NAME('GR',
961 'GR_NULL_VALUE');
962 FND_MESSAGE.SET_TOKEN('CODE',
963 p_approved_supply_list_conc,
964 FALSE);
965 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
966 END IF;
967
968 IF x_return_status <> 'S' THEN
969 x_msg_data := l_msg_data;
970 END IF;
971
972 EXCEPTION
973
974 WHEN OTHERS THEN
975 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
976 x_return_status := 'U';
977 x_oracle_error := APP_EXCEPTION.Get_Code;
978 l_msg_data := APP_EXCEPTION.Get_Text;
979 FND_MESSAGE.SET_NAME('GR',
980 'GR_UNEXPECTED_ERROR');
981 FND_MESSAGE.SET_TOKEN('TEXT',
982 l_msg_data,
983 FALSE);
984 x_msg_data := FND_MESSAGE.Get;
985
986 END Check_Foreign_Keys;
987
988 PROCEDURE Check_Integrity
989 (p_called_by_form IN VARCHAR2,
990 p_item_code IN VARCHAR2,
991 p_european_index_number IN VARCHAR2,
992 p_eec_number IN VARCHAR2,
993 p_consolidated_risk_phrase IN VARCHAR2,
994 p_consolidated_safety_phrase IN VARCHAR2,
995 p_approved_supply_list_conc IN VARCHAR2,
996 p_attribute_category IN VARCHAR2,
997 p_attribute1 IN VARCHAR2,
998 p_attribute2 IN VARCHAR2,
999 p_attribute3 IN VARCHAR2,
1000 p_attribute4 IN VARCHAR2,
1001 p_attribute5 IN VARCHAR2,
1002 p_attribute6 IN VARCHAR2,
1003 p_attribute7 IN VARCHAR2,
1004 p_attribute8 IN VARCHAR2,
1005 p_attribute9 IN VARCHAR2,
1006 p_attribute10 IN VARCHAR2,
1007 p_attribute11 IN VARCHAR2,
1008 p_attribute12 IN VARCHAR2,
1009 p_attribute13 IN VARCHAR2,
1010 p_attribute14 IN VARCHAR2,
1011 p_attribute15 IN VARCHAR2,
1012 p_attribute16 IN VARCHAR2,
1013 p_attribute17 IN VARCHAR2,
1014 p_attribute18 IN VARCHAR2,
1015 p_attribute19 IN VARCHAR2,
1016 p_attribute20 IN VARCHAR2,
1017 p_attribute21 IN VARCHAR2,
1018 p_attribute22 IN VARCHAR2,
1019 p_attribute23 IN VARCHAR2,
1020 p_attribute24 IN VARCHAR2,
1021 p_attribute25 IN VARCHAR2,
1022 p_attribute26 IN VARCHAR2,
1023 p_attribute27 IN VARCHAR2,
1024 p_attribute28 IN VARCHAR2,
1025 p_attribute29 IN VARCHAR2,
1026 p_attribute30 IN VARCHAR2,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_oracle_error OUT NOCOPY NUMBER,
1029 x_msg_data OUT NOCOPY VARCHAR2)
1030 IS
1031
1032 /* Alpha Variables */
1033
1034 L_RETURN_STATUS VARCHAR2(1) := 'S';
1035 L_MSG_DATA VARCHAR2(2000);
1036 L_CODE_BLOCK VARCHAR2(2000);
1037
1038 /* Number Variables */
1039
1040 L_ORACLE_ERROR NUMBER;
1041 L_RECORD_COUNT NUMBER;
1042
1043 /* Define the Cursors */
1044
1045 BEGIN
1046
1047 /* Initialization Routine */
1048
1049 SAVEPOINT Check_Integrity;
1050 x_return_status := 'S';
1051 x_oracle_error := 0;
1052 x_msg_data := NULL;
1053
1054 /* No integrity checking required */
1055
1056
1057 /* Now sort out the error messaging */
1058
1059 IF l_return_status <> 'S' THEN
1060 x_return_status := l_return_status;
1061 x_msg_data := l_msg_data;
1062 FND_MESSAGE.SET_NAME('GR',
1063 'GR_UNEXPECTED_ERROR');
1064 FND_MESSAGE.SET_TOKEN('TEXT',
1065 l_msg_data,
1066 FALSE);
1067 IF FND_API.To_Boolean(p_called_by_form) THEN
1068 APP_EXCEPTION.Raise_Exception;
1069 ELSE
1070 x_msg_data := FND_MESSAGE.Get;
1071 END IF;
1072 END IF;
1073
1074 EXCEPTION
1075
1076 WHEN OTHERS THEN
1077 ROLLBACK TO SAVEPOINT Check_Integrity;
1078 x_return_status := 'U';
1079 x_oracle_error := APP_EXCEPTION.Get_Code;
1080 l_msg_data := APP_EXCEPTION.Get_Text;
1081 FND_MESSAGE.SET_NAME('GR',
1082 'GR_UNEXPECTED_ERROR');
1083 FND_MESSAGE.SET_TOKEN('TEXT',
1084 l_msg_data,
1085 FALSE);
1086 IF FND_API.To_Boolean(p_called_by_form) THEN
1087 APP_EXCEPTION.Raise_Exception;
1088 ELSE
1089 x_msg_data := FND_MESSAGE.Get;
1090 END IF;
1091
1092 END Check_Integrity;
1093
1094 PROCEDURE Check_Primary_Key
1095 /* p_item_code is the item code to check.
1096 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1097 ** x_rowid is the row id of the record if found.
1098 ** x_key_exists is 'T' is the record is found, 'F' if not.
1099 */
1100 (p_item_code IN VARCHAR2,
1101 p_called_by_form IN VARCHAR2,
1102 x_rowid OUT NOCOPY VARCHAR2,
1103 x_key_exists OUT NOCOPY VARCHAR2)
1104 IS
1105 /* Alphanumeric variables */
1106
1107 L_MSG_DATA VARCHAR2(80);
1108
1109 /* Declare any variables and the cursor */
1110
1111
1112 CURSOR c_get_item_rowid
1113 IS
1114 SELECT eur.rowid
1115 FROM gr_emea eur
1116 WHERE eur.item_code = p_item_code;
1117 ItemRecord c_get_item_rowid%ROWTYPE;
1118
1119 BEGIN
1120
1121 l_msg_data := p_item_code;
1122 x_key_exists := 'F';
1123
1124 OPEN c_get_item_rowid;
1125 FETCH c_get_item_rowid INTO ItemRecord;
1126 IF c_get_item_rowid%FOUND THEN
1127 x_key_exists := 'T';
1128 x_rowid := ItemRecord.rowid;
1129 ELSE
1130 x_key_exists := 'F';
1131 END IF;
1132 CLOSE c_get_item_rowid;
1133
1134 EXCEPTION
1135
1136 WHEN Others THEN
1137 l_msg_data := APP_EXCEPTION.Get_Text;
1138 FND_MESSAGE.SET_NAME('GR',
1139 'GR_UNEXPECTED_ERROR');
1140 FND_MESSAGE.SET_TOKEN('TEXT',
1141 l_msg_data,
1142 FALSE);
1143 IF FND_API.To_Boolean(p_called_by_form) THEN
1144 APP_EXCEPTION.Raise_Exception;
1145 END IF;
1146
1147 END Check_Primary_Key;
1148
1149 END GR_EMEA_PKG;