[Home] [Help]
PACKAGE BODY: APPS.GR_ITEM_SAFETY
Source
1 PACKAGE BODY GR_ITEM_SAFETY AS
2 /*$Header: GRFMISB.pls 120.3 2005/10/20 07:30:09 methomas noship $*/
3
4 PROCEDURE get_properties
5 (p_organization_id IN NUMBER,
6 p_inventory_item_id IN NUMBER,
7 p_label_code IN VARCHAR2,
8 x_prop_data IN OUT NOCOPY t_property_data)
9 IS
10
11 /* Numeric variables */
12
13 L_RECORD_NUMBER NUMBER := 0;
14 L_ORACLE_ERROR NUMBER;
15
16 /* Alpha Variables */
17
18 L_CODE_BLOCK VARCHAR2(80);
19
20 /* Define the cursor
21 **
22 ** Get the label properties
23 GR*/
24
25 CURSOR c_get_label_props
26 IS
27 SELECT lp.property_id,
28 lp.label_code,
29 lp.sequence_number,
30 pro.property_type_indicator,
31 pro.length,
32 pro.precision,
33 pro.range_min,
34 pro.range_max,
35 pd.description
36 FROM gr_properties_tl pd,
37 gr_label_properties lp,
38 gr_properties_b pro
39 WHERE lp.label_code = p_label_code
40 AND lp.property_id = pro.property_id
41 AND pd.property_id = lp.property_id
42 AND pd.language = USERENV('LANG')
43 ORDER BY lp.sequence_number;
44 PropRecord c_get_label_props%ROWTYPE;
45
46 /* Get the item properties */
47 CURSOR c_get_item_props
48 IS
49 SELECT ip.rowid,
50 ip.organization_id,
51 ip.inventory_item_id,
52 ip.number_value,
53 ip.alpha_value,
54 ip.date_value,
55 ip.created_by,
56 ip.creation_date,
57 ip.last_updated_by,
58 ip.last_update_date,
59 ip.last_update_login
60 FROM gr_inv_item_properties ip
61 WHERE ip.organization_id = p_organization_id
62 AND ip.inventory_item_id = p_inventory_item_id
63 AND ip.label_code = p_label_code
64 AND ip.property_id = PropRecord.property_id;
65 ItemPropRecord c_get_item_props%ROWTYPE;
66
67 /* Get the property value meaning */
68
69 CURSOR c_get_value_meaning
70 IS
71 SELECT pv.meaning
72 FROM gr_property_values_tl pv
73 WHERE pv.property_id = PropRecord.property_id
74 AND pv.language = USERENV('LANG')
75 AND pv.value = ItemPropRecord.alpha_value;
76 ValueRecord c_get_value_meaning%ROWTYPE;
77
78 BEGIN
79 /* Start by initialising the table. Ideally it should be an OUT
80 ** parameter only, but that conflicts with the requirements of
81 ** Forms to handle the data.
82 */
83 x_prop_data.DELETE;
84
85 OPEN c_get_label_props;
86 l_code_block := 'Opened the cursor';
87
88 /* Read the label properties first to get all of the properties
89 ** associated with the label.
90 */
91 LOOP
92 l_record_number := l_record_number + 1;
93 l_code_block := 'Into the loop';
94
95 FETCH c_get_label_props INTO PropRecord;
96
97 /* Have the label properties data -- move that into the data record
98 ** and then read the item properties.
99 */
100
101 IF c_get_label_props%FOUND THEN
102 x_prop_data(l_record_number).property_id := PropRecord.property_id;
103 x_prop_data(l_record_number).label_code := PropRecord.label_code;
104 x_prop_data(l_record_number).sequence_number := PropRecord.sequence_number;
105 x_prop_data(l_record_number).property_type_indicator := PropRecord.property_type_indicator;
106 x_prop_data(l_record_number).length := PropRecord.length;
107 x_prop_data(l_record_number).precision := PropRecord.precision;
108 x_prop_data(l_record_number).range_min := PropRecord.range_min;
109 x_prop_data(l_record_number).range_max := PropRecord.range_max;
110 x_prop_data(l_record_number).description := PropRecord.description;
111
112 OPEN c_get_item_props;
113 FETCH c_get_item_props INTO ItemPropRecord;
114
115 /* If the item properties row is found then move that information into
116 ** the data record, otherwise leave it blank.
117 */
118 IF c_get_item_props%FOUND THEN
119 x_prop_data(l_record_number).rowid := ItemPropRecord.rowid;
120 x_prop_data(l_record_number).organization_id := ItemPropRecord.organization_id;
121 x_prop_data(l_record_number).inventory_item_id := ItemPropRecord.inventory_item_id;
122 x_prop_data(l_record_number).number_value := ItemPropRecord.number_value;
123 x_prop_data(l_record_number).alpha_value := ItemPropRecord.alpha_value;
124 x_prop_data(l_record_number).date_value := ItemPropRecord.date_value;
125 x_prop_data(l_record_number).created_by := ItemPropRecord.created_by;
126 x_prop_data(l_record_number).creation_date := ItemPropRecord.creation_date;
127 x_prop_data(l_record_number).last_updated_by := ItemPropRecord.last_updated_by;
128 x_prop_data(l_record_number).last_update_date := ItemPropRecord.last_update_date;
129 x_prop_data(l_record_number).last_update_login := ItemPropRecord.last_update_login;
130
131 /*
132 ** If property type is 'F' for a flag, get the meaning of the alpha value
133 */
134 IF PropRecord.property_type_indicator = 'F' THEN
135 OPEN c_get_value_meaning;
136 FETCH c_get_value_meaning INTO ValueRecord;
137 IF c_get_value_meaning%FOUND THEN
138 x_prop_data(l_record_number).meaning := ValueRecord.meaning;
139 END IF;
140 CLOSE c_get_value_meaning;
141 END IF;
142 END IF;
143 CLOSE c_get_item_props;
144 ELSE
145 EXIT;
146 END IF;
147 END LOOP;
148 CLOSE c_get_label_props;
149
150 EXCEPTION
151
152 WHEN OTHERS THEN
153 l_oracle_error := APP_EXCEPTION.Get_Code;
154 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
155 FND_MESSAGE.SET_NAME('GR',
156 'GR_UNEXPECTED_ERROR');
157 FND_MESSAGE.SET_TOKEN('TEXT',
158 l_code_block,
159 FALSE);
160 APP_EXCEPTION.Raise_Exception;
161
162 END get_properties;
163
164 PROCEDURE paste_item_safety
165 (p_organization_id IN NUMBER,
166 p_copy_from_item IN NUMBER,
167 p_paste_to_item IN NUMBER,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_oracle_error OUT NOCOPY NUMBER,
170 x_msg_data OUT NOCOPY VARCHAR2)
171 IS
172
173 /*
174 ** Alpha Variables
175 */
176 L_CODE_BLOCK VARCHAR2(2000);
177 L_ROWID VARCHAR2(18);
178 L_CALLED_BY_FORM VARCHAR2(1) := 'F';
179 L_KEY_EXISTS VARCHAR2(1);
180 L_COMMIT VARCHAR2(1) := 'F';
181 L_MSG_DATA VARCHAR2(2000);
182 L_RETURN_STATUS VARCHAR2(1);
183
184 L_CURRENT_DATE DATE := sysdate;
185 /*
186 ** Numeric Variables
187 */
188 L_ORACLE_ERROR NUMBER;
189 L_USER_ID NUMBER;
190
191 /*
192 ** Exceptions
193 */
194 ITEM_NULL_ERROR EXCEPTION;
195 INVALID_ITEM_CODE EXCEPTION;
196 PASTE_ITEM_EXISTS_ERROR EXCEPTION;
197 INSERT_ROWS_ERROR EXCEPTION;
198 UPDATE_ROWS_ERROR EXCEPTION;
199
200 /*
201 ** Cursors
202 **
203 ** Get the Copy item general information
204 */
205 CURSOR c_get_item_general
206 IS
207 SELECT ig1.ROWID,
208 ig1.ingredient_flag,
209 ig1.explode_ingredient_flag,
210 ig1.organization_id,
211 ig1.inventory_item_id,
212 ig1.actual_hazard
213 FROM gr_item_explosion_properties ig1
214 WHERE ig1.organization_id = p_organization_id
215 AND ig1.inventory_item_id = p_copy_from_item;
216 LocalItemRecord c_get_item_general%ROWTYPE;
217
218 /*
219 ** Cursors
220 **
221 ** Get the paste item general information
222 */
223
224 CURSOR c_get_item_general_rowid
225 IS
226 SELECT ig1.ROWID
227 FROM gr_item_explosion_properties ig1
228 WHERE ig1.organization_id = p_organization_id
229 AND ig1.inventory_item_id = p_paste_to_item;
230 LocalPItemRecord c_get_item_general_rowid%ROWTYPE;
231
232 /*
233 ** Copy Item Properties
234 */
235 CURSOR c_get_item_properties
236 IS
237 SELECT ip.sequence_number,
238 ip.property_id,
239 ip.label_code,
240 ip.number_value,
241 ip.alpha_value,
242 ip.date_value
243 FROM gr_inv_item_properties ip
244 WHERE ip.organization_id = p_organization_id
245 AND ip.inventory_item_id = p_copy_from_item;
246 LocalItemPropRecord c_get_item_properties%ROWTYPE;
247
248 /*
249 ** Copy Item Properties
250 */
251 CURSOR c_get_item_properties_rowid (V_label_code VARCHAR2, V_property_id VARCHAR2, V_sequence_number NUMBER)
252 IS
253 SELECT ip.rowid
254 FROM gr_inv_item_properties ip
255 WHERE ip.organization_id = p_organization_id
256 AND ip.inventory_item_id = p_paste_to_item
257 AND ip.label_code = V_label_code
258 AND ip.property_id = V_property_id
259 AND ip.sequence_number = V_sequence_number;
260 LocalPItemPropRecord c_get_item_properties_rowid%ROWTYPE;
261
262 /*
263 **
264 */
265 /*
266 ** Get the Item Code
267 */
268 CURSOR c_get_item_code (V_item_id NUMBER) IS
269 select item_code
270 from gr_item_general_v
271 where organization_id = p_organization_id
272 and inventory_item_id = V_item_id;
273
274 l_copy_item VARCHAR2(240);
275 l_paste_item VARCHAR2(240);
276
277 BEGIN
278
279 l_code_block := NULL;
280 l_user_id := FND_GLOBAL.User_id;
281 /*
282 ** Check the entered item codes are not null
283 */
284 IF p_copy_from_item IS NULL THEN
285 RAISE Item_Null_Error;
286 END IF;
287
288 OPEN c_get_item_code (p_copy_from_item);
289 FETCH c_get_item_code INTO l_copy_item;
290 CLOSE c_get_item_code;
291
292 IF p_paste_to_item IS NULL THEN
293 RAISE Item_Null_Error;
294 END IF;
295
296 OPEN c_get_item_code (p_paste_to_item);
297 FETCH c_get_item_code INTO l_paste_item;
298 CLOSE c_get_item_code;
299
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301 /*
302 ** Check the copy from item exists.
303 */
304 OPEN c_get_item_general;
305 FETCH c_get_item_general INTO LocalItemRecord;
306 IF c_get_item_general%NOTFOUND THEN
307 CLOSE c_get_item_general;
308 RAISE Invalid_Item_Code;
309 END IF;
310
311 CLOSE c_get_item_general;
312 /*
313 ** Check the paste to item does not exist
314 */
315 GR_ITEM_EXPLOSION_PROP_PKG.Check_Primary_Key
316 (p_organization_id,
317 p_paste_to_item,
318 l_called_by_form,
319 l_rowid,
320 l_key_exists);
321
322 IF FND_API.To_Boolean(l_key_exists) THEN
323 -- RAISE Paste_Item_Exists_Error;
324 /*
325 ** Now get the from info and write the to info
326 **
327 ** Item General -- data is already there from the earlier
328 ** cursor read
329 */
330 l_code_block := ' table - Update gr_item_explosion_properties';
331 l_return_status := FND_API.G_RET_STS_SUCCESS;
332 /*
333 ** Check the copy from item exists.
334 */
335 OPEN c_get_item_general_rowid;
336 FETCH c_get_item_general_rowid INTO LocalPItemRecord;
337 CLOSE c_get_item_general_rowid;
338
339 GR_ITEM_EXPLOSION_PROP_PKG.Update_Row
340 (l_commit,
341 l_called_by_form,
342 LocalPItemRecord.rowid,
343 LocalItemRecord.ingredient_flag,
344 LocalItemRecord.explode_ingredient_flag,
345 p_organization_id,
346 p_paste_to_item,
347 LocalItemRecord.actual_hazard,
348 l_user_id,
349 l_current_date,
350 l_user_id,
351 l_current_date,
352 l_user_id,
353 l_return_status,
354 l_oracle_error,
355 l_msg_data);
356
357 IF l_return_status <> 'S' THEN
358 RAISE Update_Rows_Error;
359 END IF;
360
361 ELSE
362 /*
363 ** Now get the from info and write the to info
364 **
365 ** Item General -- data is already there from the earlier
366 ** cursor read
367 */
368 l_code_block := ' table - Insert gr_item_explosion_properties';
369 GR_ITEM_EXPLOSION_PROP_PKG.Insert_Row
370 (l_commit,
371 l_called_by_form,
372 LocalItemRecord.ingredient_flag,
373 LocalItemRecord.explode_ingredient_flag,
374 p_organization_id,
375 p_paste_to_item,
376 LocalItemRecord.actual_hazard,
377 l_user_id,
378 l_current_date,
379 l_user_id,
380 l_current_date,
381 l_user_id,
382 l_rowid,
383 l_return_status,
384 l_oracle_error,
385 l_msg_data);
386
387 IF l_return_status <> 'S' THEN
388 RAISE Insert_Rows_Error;
389 END IF;
390 END IF;
391 /*
392 ** Item Properties
393 */
394 OPEN c_get_item_properties;
395 FETCH c_get_item_properties INTO LocalItemPropRecord;
396 IF c_get_item_properties%FOUND THEN
397 WHILE c_get_item_properties%FOUND LOOP
398 l_code_block := ' table - gr_inv_item_properties ';
399 l_return_status := FND_API.G_RET_STS_SUCCESS;
400 /*B1319565 Added for Technical Parameters */
401
402 OPEN c_get_item_properties_rowid (LocalItemPropRecord.label_code,
403 LocalItemPropRecord.property_id,
404 LocalItemPropRecord.sequence_number);
405 FETCH c_get_item_properties_rowid INTO LocalPItemPropRecord;
406 IF c_get_item_properties_rowid%NOTFOUND THEN
407 l_code_block := ' Insert table - gr_inv_item_properties ';
408 GR_INV_ITEM_PROPERTIES_PKG.Insert_Row
409 (l_commit,
410 l_called_by_form,
411 p_organization_id,
412 p_paste_to_item,
413 LocalItemPropRecord.sequence_number,
414 LocalItemPropRecord.property_id,
415 LocalItemPropRecord.label_code,
416 LocalItemPropRecord.number_value,
417 LocalItemPropRecord.alpha_value,
418 LocalItemPropRecord.date_value,
419 l_user_id,
420 l_current_date,
421 l_user_id,
422 l_current_date,
423 l_user_id,
424 l_rowid,
425 l_return_status,
426 l_oracle_error,
427 l_msg_data);
428
429 IF l_return_status <> 'S' THEN
430 RAISE Insert_Rows_Error;
431 END IF;
432 ELSE
433 l_code_block := ' Update table - gr_inv_item_properties ';
434 GR_INV_ITEM_PROPERTIES_PKG.Update_Row
435 (l_commit,
436 l_called_by_form,
437 LocalPItemPropRecord.rowid,
438 p_organization_id,
439 p_paste_to_item,
440 LocalItemPropRecord.sequence_number,
441 LocalItemPropRecord.property_id,
442 LocalItemPropRecord.label_code,
443 LocalItemPropRecord.number_value,
444 LocalItemPropRecord.alpha_value,
445 LocalItemPropRecord.date_value,
446 l_user_id,
447 l_current_date,
448 l_user_id,
449 l_current_date,
450 l_user_id,
451 l_return_status,
452 l_oracle_error,
453 l_msg_data);
454
455 IF l_return_status <> 'S' THEN
456 RAISE Update_Rows_Error;
457 END IF;
458 END IF;
459 CLOSE c_get_item_properties_rowid;
460 FETCH c_get_item_properties INTO LocalItemPropRecord;
461 END LOOP;
462 END IF;
463 CLOSE c_get_item_properties;
464
465 EXCEPTION
466
467 WHEN Item_Null_Error THEN
468 l_oracle_error := APP_EXCEPTION.Get_Code;
469 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
470 FND_MESSAGE.SET_NAME('GR',
471 'GR_PRINT_ITEM_NULL');
472 APP_EXCEPTION.Raise_Exception;
473
474 WHEN Invalid_Item_Code THEN
475 l_oracle_error := APP_EXCEPTION.Get_Code;
476 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
477 FND_MESSAGE.SET_NAME('GR',
478 'GR_INVALID_ITEM_CODE');
479 FND_MESSAGE.SET_TOKEN('CODE',
480 l_copy_item,
481 FALSE);
482 APP_EXCEPTION.Raise_Exception;
483
484 WHEN Paste_Item_Exists_Error THEN
485 l_oracle_error := APP_EXCEPTION.Get_Code;
486 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
487 FND_MESSAGE.SET_NAME('GR',
488 'GR_RECORD_EXISTS');
489 FND_MESSAGE.SET_TOKEN('CODE',
490 l_paste_item,
491 FALSE);
492 APP_EXCEPTION.Raise_Exception;
493
494 WHEN Insert_Rows_Error THEN
495 l_oracle_error := APP_EXCEPTION.Get_Code;
496 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
497 FND_MESSAGE.SET_NAME('GR',
498 'GR_NO_RECORD_INSERTED');
499 FND_MESSAGE.SET_TOKEN('CODE',
500 l_code_block,
501 FALSE);
502 APP_EXCEPTION.Raise_Exception;
503
504 WHEN Update_Rows_Error THEN
505 l_oracle_error := APP_EXCEPTION.Get_Code;
506 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
507 FND_MESSAGE.SET_NAME('GR',
508 'GR_NO_RECORD_INSERTED');
509 FND_MESSAGE.SET_TOKEN('CODE',
510 l_code_block,
511 FALSE);
512 APP_EXCEPTION.Raise_Exception;
513
514 WHEN OTHERS THEN
515 l_oracle_error := APP_EXCEPTION.Get_Code;
516 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
517 FND_MESSAGE.SET_NAME('GR',
518 'GR_UNEXPECTED_ERROR');
519 FND_MESSAGE.SET_TOKEN('TEXT',
520 l_code_block,
521 FALSE);
522 APP_EXCEPTION.Raise_Exception;
523
524 END paste_item_safety;
525
526 PROCEDURE delete_item_safety
527 (p_delete_item IN VARCHAR2,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_oracle_error OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2)
531
532 IS
533
534 /*
535 ** Alpha Variables
536 */
537 L_CODE_BLOCK VARCHAR2(2000);
538 L_ROWID VARCHAR2(18);
539 L_CALLED_BY_FORM VARCHAR2(1) := 'F';
540 L_KEY_EXISTS VARCHAR2(1);
541 L_DELETE_OPTION VARCHAR2(1);
542 L_COMMIT VARCHAR2(1) := 'F';
543 L_MSG_DATA VARCHAR2(2000);
544 L_RETURN_STATUS VARCHAR2(1);
545
546 /*
547 ** Numeric Variables
548 */
549 L_ORACLE_ERROR NUMBER;
550
551
552 /*
553 ** Exceptions
554 */
555 ITEM_NULL_ERROR EXCEPTION;
556 INVALID_ITEM_ERROR EXCEPTION;
557 CANNOT_LOCK_ITEM_ERROR EXCEPTION;
558 OTHER_API_ERROR EXCEPTION;
559
560 /*
561 ** Cursors
562 **
563 ** Get the item general information
564 */
565 CURSOR c_get_item_general
566 IS
567 SELECT ig1.ROWID,
568 ig1.item_group_code,
569 ig1.primary_cas_number,
570 ig1.ingredient_flag,
571 ig1.explode_ingredient_flag,
572 ig1.formula_source_indicator,
573 ig1.user_id,
574 ig1.internal_reference_number,
575 ig1.product_label_code,
576 ig1.version_code,
577 ig1.last_version_code,
578 ig1.product_class,
579 ig1.item_code,
580 ig1.actual_hazard,
581 ig1.print_ingredient_phrases_flag,
582 ig1.attribute_category,
583 ig1.attribute1,
584 ig1.attribute2,
585 ig1.attribute3,
586 ig1.attribute4,
587 ig1.attribute5,
588 ig1.attribute6,
589 ig1.attribute7,
590 ig1.attribute8,
591 ig1.attribute9,
592 ig1.attribute10,
593 ig1.attribute11,
594 ig1.attribute12,
595 ig1.attribute13,
596 ig1.attribute14,
597 ig1.attribute15,
598 ig1.attribute16,
599 ig1.attribute17,
600 ig1.attribute18,
601 ig1.attribute19,
602 ig1.attribute20,
603 ig1.attribute21,
604 ig1.attribute22,
605 ig1.attribute23,
606 ig1.attribute24,
607 ig1.attribute25,
608 ig1.attribute26,
609 ig1.attribute27,
610 ig1.attribute28,
611 ig1.attribute29,
612 ig1.attribute30,
613 ig1.created_by,
614 ig1.creation_date,
615 ig1.last_updated_by,
616 ig1.last_update_date,
617 ig1.last_update_login
618 FROM gr_item_general ig1
619 WHERE ig1.item_code = p_delete_item;
620 LocalItemRecord c_get_item_general%ROWTYPE;
621 /*
622 ** Get the EMEA row
623 */
624 CURSOR c_get_item_emea
625 IS
626 SELECT em.ROWID,
627 em.item_code,
628 em.european_index_number,
629 em.eec_number,
630 em.consolidated_risk_phrase,
631 em.consolidated_safety_phrase,
632 em.approved_supply_list_conc,
633 em.attribute_category,
634 em.attribute1,
635 em.attribute2,
636 em.attribute3,
637 em.attribute4,
638 em.attribute5,
639 em.attribute6,
640 em.attribute7,
641 em.attribute8,
642 em.attribute9,
643 em.attribute10,
644 em.attribute11,
645 em.attribute12,
646 em.attribute13,
647 em.attribute14,
648 em.attribute15,
649 em.attribute16,
650 em.attribute17,
651 em.attribute18,
652 em.attribute19,
653 em.attribute20,
654 em.attribute21,
655 em.attribute22,
656 em.attribute23,
657 em.attribute24,
658 em.attribute25,
659 em.attribute26,
660 em.attribute27,
661 em.attribute28,
662 em.attribute29,
663 em.attribute30,
664 em.created_by,
665 em.creation_date,
666 em.last_updated_by,
667 em.last_update_date,
668 em.last_update_login
669 FROM gr_emea em
670 WHERE em.item_code = p_delete_item;
671 LocalEmeaRecord c_get_item_emea%ROWTYPE;
672
673 BEGIN
674
675 l_code_block := NULL;
676 /*
677 ** Check the entered item code is not null
678 */
679 IF p_delete_item IS NULL THEN
680 RAISE Item_Null_Error;
681 END IF;
682 /*
683 ** Check the item exists
684 */
685 OPEN c_get_item_general;
686 FETCH c_get_item_general INTO LocalItemRecord;
687 IF c_get_item_general%NOTFOUND THEN
688 CLOSE c_get_item_general;
689 RAISE Invalid_Item_Error;
690 ELSE
691 CLOSE c_get_item_general;
692 END IF;
693
694 l_return_status := FND_API.G_RET_STS_SUCCESS;
695 GR_ITEM_GENERAL_PKG.Lock_Row
696 (l_commit,
697 l_called_by_form,
698 LocalItemRecord.ROWID,
699 LocalItemRecord.item_group_code,
700 LocalItemRecord.primary_cas_number,
701 LocalItemRecord.ingredient_flag,
702 LocalItemRecord.explode_ingredient_flag,
703 LocalItemRecord.formula_source_indicator,
704 LocalItemRecord.user_id,
705 LocalItemRecord.internal_reference_number,
706 LocalItemRecord.product_label_code,
707 LocalItemRecord.version_code,
708 LocalItemRecord.last_version_code,
709 LocalItemRecord.product_class,
710 LocalItemRecord.item_code,
711 LocalItemRecord.actual_hazard,
712 LocalItemRecord.print_ingredient_phrases_flag,
713 LocalItemRecord.attribute_category,
714 LocalItemRecord.attribute1,
715 LocalItemRecord.attribute2,
716 LocalItemRecord.attribute3,
717 LocalItemRecord.attribute4,
718 LocalItemRecord.attribute5,
719 LocalItemRecord.attribute6,
720 LocalItemRecord.attribute7,
721 LocalItemRecord.attribute8,
722 LocalItemRecord.attribute9,
723 LocalItemRecord.attribute10,
724 LocalItemRecord.attribute11,
725 LocalItemRecord.attribute12,
726 LocalItemRecord.attribute13,
727 LocalItemRecord.attribute14,
728 LocalItemRecord.attribute15,
729 LocalItemRecord.attribute16,
730 LocalItemRecord.attribute17,
731 LocalItemRecord.attribute18,
732 LocalItemRecord.attribute19,
733 LocalItemRecord.attribute20,
734 LocalItemRecord.attribute21,
735 LocalItemRecord.attribute22,
736 LocalItemRecord.attribute23,
737 LocalItemRecord.attribute24,
738 LocalItemRecord.attribute25,
739 LocalItemRecord.attribute26,
740 LocalItemRecord.attribute27,
741 LocalItemRecord.attribute28,
742 LocalItemRecord.attribute29,
743 LocalItemRecord.attribute30,
744 LocalItemRecord.created_by,
745 LocalItemRecord.creation_date,
746 LocalItemRecord.last_updated_by,
747 LocalItemRecord.last_update_date,
748 LocalItemRecord.last_update_login,
749 l_return_status,
750 l_oracle_error,
751 l_msg_data);
752
753 IF l_return_status <> 'S' THEN
754 RAISE Cannot_Lock_Item_Error;
755 END IF;
756 /*
757 ** Main row locked ok, now delete the subsidiary tables
758 */
759 l_delete_option := 'I';
760 l_return_status := FND_API.G_RET_STS_SUCCESS;
761 GR_OTHER_NAMES_TL_PKG.Delete_Rows
762 (l_commit,
763 l_called_by_form,
764 l_delete_option,
765 p_delete_item,
766 '',
767 l_return_status,
768 l_oracle_error,
769 l_msg_data);
770
771 IF l_return_status <> 'S' THEN
772 RAISE Other_API_ERROR;
773 END IF;
774
775 l_delete_option := 'I';
776 l_return_status := FND_API.G_RET_STS_SUCCESS;
777 GR_MULTILINGUAL_NAME_TL_PKG.Delete_Rows
778 (l_commit,
779 l_called_by_form,
780 l_delete_option,
781 p_delete_item,
782 '',
783 l_return_status,
784 l_oracle_error,
785 l_msg_data);
786
787 IF l_return_status <> 'S' THEN
788 RAISE Other_API_ERROR;
789 END IF;
790
791 l_delete_option := 'I';
792 l_return_status := FND_API.G_RET_STS_SUCCESS;
793 GR_ITEM_SAFETY_PHRASES_PKG.Delete_Rows
794 (l_commit,
795 l_called_by_form,
796 l_delete_option,
797 p_delete_item,
798 '',
799 l_return_status,
800 l_oracle_error,
801 l_msg_data);
802
803 IF l_return_status <> 'S' THEN
804 RAISE Other_API_ERROR;
805 END IF;
806
807 IF l_return_status <> 'S' THEN
808 RAISE Other_API_ERROR;
809 END IF;
810
811 l_delete_option := 'I';
812 l_return_status := FND_API.G_RET_STS_SUCCESS;
813 GR_ITEM_RISK_PHRASES_PKG.Delete_Rows
814 (l_commit,
815 l_called_by_form,
816 l_delete_option,
817 p_delete_item,
818 '',
819 l_return_status,
820 l_oracle_error,
821 l_msg_data);
822
823 IF l_return_status <> 'S' THEN
824 RAISE Other_API_ERROR;
825 END IF;
826
827 l_return_status := FND_API.G_RET_STS_SUCCESS;
828 GR_ITEM_RIGHT_TO_KNOW_PKG.Delete_Rows
829 (l_commit,
830 l_called_by_form,
831 p_delete_item,
832 l_return_status,
833 l_oracle_error,
834 l_msg_data);
835
836 IF l_return_status <> 'S' THEN
837 RAISE Other_API_ERROR;
838 END IF;
839
840 l_delete_option := 'I';
841 l_return_status := FND_API.G_RET_STS_SUCCESS;
842 GR_ITEM_SAFETY.Delete_item_document
843 (p_delete_item,
844 '',
845 l_delete_option,
846 l_return_status,
847 l_oracle_error,
848 l_msg_data);
849
850 IF l_return_status <> 'S' THEN
851 RAISE Other_API_ERROR;
852 END IF;
853
854 l_delete_option := 'I';
855 l_return_status := FND_API.G_RET_STS_SUCCESS;
856 GR_ITEM_CLASSNS_PKG.Delete_Rows
857 (l_commit,
858 l_called_by_form,
859 l_delete_option,
860 p_delete_item,
861 '',
862 l_return_status,
863 l_oracle_error,
864 l_msg_data);
865
866 IF l_return_status <> 'S' THEN
867 RAISE Other_API_ERROR;
868 END IF;
869
870 l_return_status := FND_API.G_RET_STS_SUCCESS;
871 GR_ITEM_DISCLOSURES_PKG.Delete_Rows
872 (l_commit,
873 l_called_by_form,
874 p_delete_item,
875 l_return_status,
876 l_oracle_error,
877 l_msg_data);
878
879 IF l_return_status <> 'S' THEN
880 RAISE Other_API_ERROR;
881 END IF;
882
883 l_return_status := FND_API.G_RET_STS_SUCCESS;
884 GR_ITEM_CONC_DETAILS_PKG.Delete_Rows
885 (l_commit,
886 l_called_by_form,
887 p_delete_item,
888 l_return_status,
889 l_oracle_error,
890 l_msg_data);
891
892 IF l_return_status <> 'S' THEN
893 RAISE Other_API_ERROR;
894 END IF;
895
896 l_return_status := FND_API.G_RET_STS_SUCCESS;
897 GR_ITEM_CONCENTRATIONS_PKG.Delete_Rows
898 (l_commit,
899 l_called_by_form,
900 p_delete_item,
901 l_return_status,
902 l_oracle_error,
903 l_msg_data);
904
905 IF l_return_status <> 'S' THEN
906 RAISE Other_API_ERROR;
907 END IF;
908
909 l_delete_option := 'I';
910 l_return_status := FND_API.G_RET_STS_SUCCESS;
911 GR_ITEM_PROPERTIES_PKG.Delete_Rows
912 (l_commit,
913 l_called_by_form,
914 l_delete_option,
915 p_delete_item,
916 '',
917 l_return_status,
918 l_oracle_error,
919 l_msg_data);
920
921 IF l_return_status <> 'S' THEN
922 RAISE Other_API_ERROR;
923 END IF;
924
925 l_delete_option := 'I';
926 l_return_status := FND_API.G_RET_STS_SUCCESS;
927 GR_ITEM_TOXIC_PKG.Delete_Rows
928 (l_commit,
929 l_called_by_form,
930 l_delete_option,
931 p_delete_item,
932 '',
933 '',
934 '',
935 '',
936 l_return_status,
937 l_oracle_error,
938 l_msg_data);
939
940 IF l_return_status <> 'S' THEN
941 RAISE Other_API_ERROR;
942 END IF;
943
944 l_delete_option := 'I';
945 l_return_status := FND_API.G_RET_STS_SUCCESS;
946 GR_ITEM_EXPOSURE_PKG.Delete_Rows
947 (l_commit,
948 l_called_by_form,
949 l_delete_option,
950 p_delete_item,
951 '',
952 '',
953 '',
954 l_return_status,
955 l_oracle_error,
956 l_msg_data);
957
958 IF l_return_status <> 'S' THEN
959 RAISE Other_API_ERROR;
960 END IF;
961
962 l_delete_option := 'G';
963 l_return_status := FND_API.G_RET_STS_SUCCESS;
964 GR_GENERIC_ML_NAME_TL_PKG.Delete_Rows
965 (l_commit,
966 l_called_by_form,
967 l_delete_option,
968 p_delete_item,
969 '',
970 l_return_status,
971 l_oracle_error,
972 l_msg_data);
973
974 IF l_return_status <> 'S' THEN
975 RAISE Other_API_ERROR;
976 END IF;
977
978 l_delete_option := 'G';
979 l_return_status := FND_API.G_RET_STS_SUCCESS;
980 GR_GENERIC_ITEMS_B_PKG.Delete_Rows
981 (l_commit,
982 l_called_by_form,
983 l_delete_option,
984 p_delete_item,
985 '',
986 l_return_status,
987 l_oracle_error,
988 l_msg_data);
989
990 IF l_return_status <> 'S' THEN
991 RAISE Other_API_ERROR;
992 END IF;
993 /*
994 ** Clear the EMEA row
995 */
996 OPEN c_get_item_emea;
997 FETCH c_get_item_emea INTO LocalEmeaRecord;
998 IF c_get_item_emea%NOTFOUND THEN
999 CLOSE c_get_item_emea;
1000 ELSE
1001 CLOSE c_get_item_emea;
1002
1003 l_return_status := FND_API.G_RET_STS_SUCCESS;
1004 GR_EMEA_PKG.Delete_Row
1005 (l_commit,
1006 l_called_by_form,
1007 LocalEmeaRecord.ROWID,
1008 LocalEmeaRecord.item_code,
1009 LocalEmeaRecord.european_index_number,
1010 LocalEmeaRecord.eec_number,
1011 LocalEmeaRecord.consolidated_risk_phrase,
1012 LocalEmeaRecord.consolidated_safety_phrase,
1013 LocalEmeaRecord.approved_supply_list_conc,
1014 LocalEmeaRecord.attribute_category,
1015 LocalEmeaRecord.attribute1,
1016 LocalEmeaRecord.attribute2,
1017 LocalEmeaRecord.attribute3,
1018 LocalEmeaRecord.attribute4,
1019 LocalEmeaRecord.attribute5,
1020 LocalEmeaRecord.attribute6,
1021 LocalEmeaRecord.attribute7,
1022 LocalEmeaRecord.attribute8,
1023 LocalEmeaRecord.attribute9,
1024 LocalEmeaRecord.attribute10,
1025 LocalEmeaRecord.attribute11,
1026 LocalEmeaRecord.attribute12,
1027 LocalEmeaRecord.attribute13,
1028 LocalEmeaRecord.attribute14,
1029 LocalEmeaRecord.attribute15,
1030 LocalEmeaRecord.attribute16,
1031 LocalEmeaRecord.attribute17,
1032 LocalEmeaRecord.attribute18,
1033 LocalEmeaRecord.attribute19,
1034 LocalEmeaRecord.attribute20,
1035 LocalEmeaRecord.attribute21,
1036 LocalEmeaRecord.attribute22,
1037 LocalEmeaRecord.attribute23,
1038 LocalEmeaRecord.attribute24,
1039 LocalEmeaRecord.attribute25,
1040 LocalEmeaRecord.attribute26,
1041 LocalEmeaRecord.attribute27,
1042 LocalEmeaRecord.attribute28,
1043 LocalEmeaRecord.attribute29,
1044 LocalEmeaRecord.attribute30,
1045 LocalEmeaRecord.created_by,
1046 LocalEmeaRecord.creation_date,
1047 LocalEmeaRecord.last_updated_by,
1048 LocalEmeaRecord.last_update_date,
1049 LocalEmeaRecord.last_update_login,
1050 l_return_status,
1051 l_oracle_error,
1052 l_msg_data);
1053
1054 IF l_return_status <> 'S' THEN
1055 RAISE Other_API_ERROR;
1056 END IF;
1057 END IF;
1058 /*
1059 ** Delete the item general row
1060 */
1061 l_return_status := FND_API.G_RET_STS_SUCCESS;
1062 GR_ITEM_GENERAL_PKG.Delete_Row
1063 (l_commit,
1064 l_called_by_form,
1065 LocalItemRecord.ROWID,
1066 LocalItemRecord.item_group_code,
1067 LocalItemRecord.primary_cas_number,
1068 LocalItemRecord.ingredient_flag,
1069 LocalItemRecord.explode_ingredient_flag,
1070 LocalItemRecord.formula_source_indicator,
1071 LocalItemRecord.user_id,
1072 LocalItemRecord.internal_reference_number,
1073 LocalItemRecord.product_label_code,
1074 LocalItemRecord.version_code,
1075 LocalItemRecord.last_version_code,
1076 LocalItemRecord.product_class,
1077 LocalItemRecord.item_code,
1078 LocalItemRecord.actual_hazard,
1079 LocalItemRecord.print_ingredient_phrases_flag,
1080 LocalItemRecord.attribute_category,
1081 LocalItemRecord.attribute1,
1082 LocalItemRecord.attribute2,
1083 LocalItemRecord.attribute3,
1084 LocalItemRecord.attribute4,
1085 LocalItemRecord.attribute5,
1086 LocalItemRecord.attribute6,
1087 LocalItemRecord.attribute7,
1088 LocalItemRecord.attribute8,
1089 LocalItemRecord.attribute9,
1090 LocalItemRecord.attribute10,
1091 LocalItemRecord.attribute11,
1092 LocalItemRecord.attribute12,
1093 LocalItemRecord.attribute13,
1094 LocalItemRecord.attribute14,
1095 LocalItemRecord.attribute15,
1096 LocalItemRecord.attribute16,
1097 LocalItemRecord.attribute17,
1098 LocalItemRecord.attribute18,
1099 LocalItemRecord.attribute19,
1100 LocalItemRecord.attribute20,
1101 LocalItemRecord.attribute21,
1102 LocalItemRecord.attribute22,
1103 LocalItemRecord.attribute23,
1104 LocalItemRecord.attribute24,
1105 LocalItemRecord.attribute25,
1106 LocalItemRecord.attribute26,
1107 LocalItemRecord.attribute27,
1108 LocalItemRecord.attribute28,
1109 LocalItemRecord.attribute29,
1110 LocalItemRecord.attribute30,
1111 LocalItemRecord.created_by,
1112 LocalItemRecord.creation_date,
1113 LocalItemRecord.last_updated_by,
1114 LocalItemRecord.last_update_date,
1115 LocalItemRecord.last_update_login,
1116 l_return_status,
1117 l_oracle_error,
1118 l_msg_data);
1119
1120 IF l_return_status <> 'S' THEN
1121 RAISE Other_API_ERROR;
1122 END IF;
1123
1124 EXCEPTION
1125
1126 WHEN Item_Null_Error THEN
1127 l_oracle_error := APP_EXCEPTION.Get_Code;
1128 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1129 FND_MESSAGE.SET_NAME('GR',
1130 'GR_UNEXPECTED_ERROR');
1131 FND_MESSAGE.SET_TOKEN('TEXT',
1132 l_code_block,
1133 FALSE);
1134 APP_EXCEPTION.Raise_Exception;
1135
1136 WHEN Invalid_Item_Error THEN
1137 l_oracle_error := APP_EXCEPTION.Get_Code;
1138 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1139 FND_MESSAGE.SET_NAME('GR',
1140 'GR_INVALID_ITEM_CODE');
1141 FND_MESSAGE.SET_TOKEN('CODE',
1142 p_delete_item,
1143 FALSE);
1144 APP_EXCEPTION.Raise_Exception;
1145
1146 WHEN Cannot_Lock_Item_Error THEN
1147 l_oracle_error := APP_EXCEPTION.Get_Code;
1148 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1149 FND_MESSAGE.SET_NAME('GR',
1150 'GR_UNEXPECTED_ERROR');
1151 FND_MESSAGE.SET_TOKEN('TEXT',
1152 l_code_block,
1153 FALSE);
1154 APP_EXCEPTION.Raise_Exception;
1155
1156 WHEN OTHER_API_ERROR THEN
1157 FND_MESSAGE.SET_NAME('GR',
1158 'GR_UNEXPECTED_ERROR');
1159 FND_MESSAGE.SET_TOKEN('TEXT',
1160 l_msg_data,
1161 FALSE);
1162 APP_EXCEPTION.Raise_Exception;
1163
1164 WHEN OTHERS THEN
1165 l_oracle_error := APP_EXCEPTION.Get_Code;
1166 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1167 FND_MESSAGE.SET_NAME('GR',
1168 'GR_UNEXPECTED_ERROR');
1169 FND_MESSAGE.SET_TOKEN('TEXT',
1170 l_code_block,
1171 FALSE);
1172 APP_EXCEPTION.Raise_Exception;
1173
1174 END delete_item_safety;
1175
1176 PROCEDURE delete_item_document
1177 (p_delete_item IN VARCHAR2,
1178 p_document_code IN VARCHAR2,
1179 p_delete_option IN VARCHAR2,
1180 x_return_status OUT NOCOPY VARCHAR2,
1181 x_oracle_error OUT NOCOPY NUMBER,
1182 x_msg_data OUT NOCOPY VARCHAR2)
1183 IS
1184
1185 /*
1186 ** Alpha Variables
1187 */
1188 L_CODE_BLOCK VARCHAR2(2000);
1189 L_ROWID VARCHAR2(18);
1190 L_CALLED_BY_FORM VARCHAR2(1) := 'F';
1191 L_KEY_EXISTS VARCHAR2(1);
1192 L_COMMIT VARCHAR2(1) := 'F';
1193 L_MSG_DATA VARCHAR2(2000);
1194 L_RETURN_STATUS VARCHAR2(1);
1195
1196 /*
1197 ** Numeric Variables
1198 */
1199 L_ORACLE_ERROR NUMBER;
1200
1201
1202 /*
1203 ** Exceptions
1204 */
1205 ITEM_NULL_ERROR EXCEPTION;
1206 OTHER_API_ERROR EXCEPTION;
1207
1208 /*
1209 ** Get the document print header information
1210 */
1211 CURSOR c_get_document_print
1212 IS
1213 SELECT dp.document_text_id
1214 FROM gr_document_print dp
1215 WHERE dp.item_code = p_delete_item
1216 AND (p_document_code IS NULL OR
1217 dp.document_code = p_document_code);
1218 LocalDocumentPrint c_get_document_print%ROWTYPE;
1219
1220 BEGIN
1221
1222 l_code_block := NULL;
1223 /*
1224 ** Check the entered item code is not null
1225 */
1226 IF p_delete_item IS NULL THEN
1227 RAISE Item_Null_Error;
1228 END IF;
1229
1230
1231 l_return_status := FND_API.G_RET_STS_SUCCESS;
1232 GR_ITEM_DOCUMENT_DTLS_PKG.Delete_Rows
1233 (l_commit,
1234 l_called_by_form,
1235 p_delete_option,
1236 p_delete_item,
1237 p_document_code,
1238 '',
1239 l_return_status,
1240 l_oracle_error,
1241 l_msg_data);
1242
1243 IF l_return_status <> 'S' THEN
1244 RAISE Other_API_ERROR;
1245 END IF;
1246
1247 l_return_status := FND_API.G_RET_STS_SUCCESS;
1248 GR_ITEM_DOC_STATUSES_PKG.Delete_Rows
1249 (l_commit,
1250 l_called_by_form,
1251 p_delete_option,
1252 p_delete_item,
1253 p_document_code,
1254 l_return_status,
1255 l_oracle_error,
1256 l_msg_data);
1257
1258 IF l_return_status <> 'S' THEN
1259 RAISE Other_API_ERROR;
1260 END IF;
1261
1262 /*
1263 ** Delete from dispatch history
1264 */
1265 l_return_status := FND_API.G_RET_STS_SUCCESS;
1266 GR_DISPATCH_HISTORIES_PKG.Delete_Rows
1267 (l_commit,
1268 l_called_by_form,
1269 p_delete_option,
1270 p_document_code,
1271 p_delete_item,
1272 '',
1273 l_return_status,
1274 l_oracle_error,
1275 l_msg_data);
1276 /*
1277 ** Delete from document print tables.
1278 */
1279 OPEN c_get_document_print;
1280 FETCH c_get_document_print INTO LocalDocumentPrint;
1281 IF c_get_document_print%FOUND THEN
1282 WHILE c_get_document_print%FOUND LOOP
1283 l_return_status := FND_API.G_RET_STS_SUCCESS;
1284 GR_DOCUMENT_DETAILS_PKG.Delete_Rows
1285 (l_commit,
1286 l_called_by_form,
1287 LocalDocumentPrint.document_text_id,
1288 l_return_status,
1289 l_oracle_error,
1290 l_msg_data);
1291 IF l_return_status <> 'S' THEN
1292 RAISE Other_API_Error;
1293 END IF;
1294
1295 FETCH c_get_document_print INTO LocalDocumentPrint;
1296 END LOOP;
1297 END IF;
1298 CLOSE c_get_document_print;
1299
1300 l_return_status := FND_API.G_RET_STS_SUCCESS;
1301 GR_DOCUMENT_PRINT_PKG.Delete_Rows
1302 (l_commit,
1303 l_called_by_form,
1304 p_delete_option,
1305 p_document_code,
1306 p_delete_item,
1307 '',
1308 '',
1309 l_return_status,
1310 l_oracle_error,
1311 l_msg_data);
1312
1313 IF l_return_status <> 'S' THEN
1314 RAISE Other_API_Error;
1315
1316 END IF;
1317
1318 EXCEPTION
1319
1320 WHEN Item_Null_Error THEN
1321 l_oracle_error := APP_EXCEPTION.Get_Code;
1322 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1323 FND_MESSAGE.SET_NAME('GR',
1324 'GR_UNEXPECTED_ERROR');
1325 FND_MESSAGE.SET_TOKEN('TEXT',
1326 l_code_block,
1327 FALSE);
1328 APP_EXCEPTION.Raise_Exception;
1329
1330 WHEN OTHER_API_ERROR THEN
1331 FND_MESSAGE.SET_NAME('GR',
1332 'GR_UNEXPECTED_ERROR');
1333 FND_MESSAGE.SET_TOKEN('TEXT',
1334 l_msg_data,
1335 FALSE);
1336 APP_EXCEPTION.Raise_Exception;
1337
1338 WHEN OTHERS THEN
1339 l_oracle_error := APP_EXCEPTION.Get_Code;
1340 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
1341 FND_MESSAGE.SET_NAME('GR',
1342 'GR_UNEXPECTED_ERROR');
1343 FND_MESSAGE.SET_TOKEN('TEXT',
1344 l_code_block,
1345 FALSE);
1346 APP_EXCEPTION.Raise_Exception;
1347
1348 END delete_item_document;
1349
1350 END GR_ITEM_SAFETY;