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