[Home] [Help]
PACKAGE BODY: APPS.GR_ITEM_CONC_DETAILS_PKG
Source
1 PACKAGE BODY GR_ITEM_CONC_DETAILS_PKG AS
2 /*$Header: GRHIICDB.pls 115.6 2002/10/25 20:11:58 methomas 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_ingredient_item_code IN VARCHAR2,
8 p_source_item_code IN VARCHAR2,
9 p_level_hierarchy_code IN NUMBER,
10 p_work_concentration IN NUMBER,
11 p_uom IN VARCHAR2,
12 p_quantity IN NUMBER,
13 x_rowid OUT NOCOPY VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_oracle_error OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2)
17 IS
18 /* Alpha Variables */
19
20 L_RETURN_STATUS VARCHAR2(1) := 'S';
21 L_KEY_EXISTS VARCHAR2(1);
22 L_MSG_DATA VARCHAR2(2000);
23 L_ROWID VARCHAR2(18);
24 L_MSG_TOKEN VARCHAR2(80);
25
26 /* Number Variables */
27
28 L_ORACLE_ERROR NUMBER;
29
30 /* Exceptions */
31
32 FOREIGN_KEY_ERROR EXCEPTION;
33 ITEM_EXISTS_ERROR EXCEPTION;
34 ROW_MISSING_ERROR EXCEPTION;
35
36 /* Declare cursors */
37
38 BEGIN
39
40 /* Initialization Routine */
41
42 SAVEPOINT Insert_Row;
43 x_return_status := 'S';
44 x_oracle_error := 0;
45 x_msg_data := NULL;
46 l_msg_token := p_item_code || ' ' || p_ingredient_item_code;
47
48 /* Now call the check foreign key procedure */
49
50 Check_Foreign_Keys
51 (p_item_code,
52 p_ingredient_item_code,
53 p_source_item_code,
54 p_level_hierarchy_code,
55 p_work_concentration,
56 p_uom,
57 p_quantity,
58 l_return_status,
59 l_oracle_error,
60 l_msg_data);
61 IF l_return_status <> 'S' THEN
62 RAISE Foreign_Key_Error;
63 END IF;
64
65 /* Now check the primary key doesn't already exist */
66
67 Check_Primary_Key
68 (p_item_code,
69 p_ingredient_item_code,
70 p_source_item_code,
71 'F',
72 l_rowid,
73 l_key_exists);
74
75 IF FND_API.To_Boolean(l_key_exists) THEN
76 RAISE Item_Exists_Error;
77 END IF;
78
79 INSERT INTO gr_item_conc_details
80 (item_code,
81 ingredient_item_code,
82 source_item_code,
83 level_hierarchy_code,
84 work_concentration,
85 uom,
86 quantity)
87 VALUES
88 (p_item_code,
89 p_ingredient_item_code,
90 p_source_item_code,
91 p_level_hierarchy_code,
92 p_work_concentration,
93 p_uom,
94 p_quantity);
95
96 /* Now get the row id of the inserted record */
97
98 Check_Primary_Key
99 (p_item_code,
100 p_ingredient_item_code,
101 p_source_item_code,
102 'F',
103 l_rowid,
104 l_key_exists);
105
106 IF FND_API.To_Boolean(l_key_exists) THEN
107 x_rowid := l_rowid;
108 ELSE
109 RAISE Row_Missing_Error;
110 END IF;
111
112 /* Check the commit flag and if set, then commit the work. */
113
114 IF FND_API.To_Boolean(p_commit) THEN
115 COMMIT WORK;
116 END IF;
117
118 EXCEPTION
119
120 WHEN Foreign_Key_Error THEN
121 ROLLBACK TO SAVEPOINT Insert_Row;
122 x_return_status := l_return_status;
123 x_oracle_error := l_oracle_error;
124 FND_MESSAGE.SET_NAME('GR',
125 'GR_FOREIGN_KEY_ERROR');
126 FND_MESSAGE.SET_TOKEN('TEXT',
127 l_msg_data,
128 FALSE);
129 IF FND_API.To_Boolean(p_called_by_form) THEN
130 APP_EXCEPTION.Raise_Exception;
131 ELSE
132 x_msg_data := FND_MESSAGE.Get;
133 END IF;
134
135 WHEN Item_Exists_Error THEN
136 ROLLBACK TO SAVEPOINT Insert_Row;
137 l_msg_token := p_item_code;
138 x_return_status := 'E';
139 x_oracle_error := APP_EXCEPTION.Get_Code;
140 FND_MESSAGE.SET_NAME('GR',
141 'GR_RECORD_EXISTS');
142 FND_MESSAGE.SET_TOKEN('CODE',
143 l_msg_token,
144 FALSE);
145 IF FND_API.To_Boolean(p_called_by_form) THEN
146 APP_EXCEPTION.Raise_Exception;
147 ELSE
148 x_msg_data := FND_MESSAGE.Get;
149 END IF;
150
151 WHEN Row_Missing_Error THEN
152 ROLLBACK TO SAVEPOINT Insert_Row;
153 l_msg_token := p_item_code;
154 x_return_status := 'E';
155 x_oracle_error := APP_EXCEPTION.Get_Code;
156 FND_MESSAGE.SET_NAME('GR',
157 'GR_NO_RECORD_INSERTED');
158 FND_MESSAGE.SET_TOKEN('CODE',
159 l_msg_token,
160 FALSE);
161 IF FND_API.To_Boolean(p_called_by_form) THEN
162 APP_EXCEPTION.Raise_Exception;
163 ELSE
164 x_msg_data := FND_MESSAGE.Get;
165 END IF;
166
167 WHEN OTHERS THEN
168 ROLLBACK TO SAVEPOINT Insert_Row;
169 l_msg_token := p_item_code;
170 x_return_status := 'U';
171 x_oracle_error := APP_EXCEPTION.Get_Code;
172 l_msg_data := APP_EXCEPTION.Get_Text;
173 FND_MESSAGE.SET_NAME('GR',
174 'GR_UNEXPECTED_ERROR');
175 FND_MESSAGE.SET_TOKEN('TEXT',
176 l_msg_token,
177 FALSE);
178 IF FND_API.To_Boolean(p_called_by_form) THEN
179 APP_EXCEPTION.Raise_Exception;
180 ELSE
181 x_msg_data := FND_MESSAGE.Get;
182 END IF;
183
184 END Insert_Row;
185
186 PROCEDURE Update_Row
187 (p_commit IN VARCHAR2,
188 p_called_by_form IN VARCHAR2,
189 p_rowid IN VARCHAR2,
190 p_item_code IN VARCHAR2,
191 p_ingredient_item_code IN VARCHAR2,
192 p_source_item_code IN VARCHAR2,
193 p_level_hierarchy_code IN NUMBER,
194 p_work_concentration IN NUMBER,
195 p_uom IN VARCHAR2,
196 p_quantity IN NUMBER,
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_oracle_error OUT NOCOPY NUMBER,
199 x_msg_data OUT NOCOPY VARCHAR2)
200 IS
201
202 /* Alpha Variables */
203
204 L_RETURN_STATUS VARCHAR2(1) := 'S';
205 L_MSG_DATA VARCHAR2(2000);
206 L_MSG_TOKEN VARCHAR2(80);
207
208 /* Number Variables */
209
210 L_ORACLE_ERROR NUMBER;
211
212 /* Exceptions */
213
214 FOREIGN_KEY_ERROR EXCEPTION;
215 ROW_MISSING_ERROR EXCEPTION;
216
217 BEGIN
218
219 /* Initialization Routine */
220
221 SAVEPOINT Update_Row;
222 x_return_status := 'S';
223 x_oracle_error := 0;
224 x_msg_data := NULL;
225 l_msg_token := p_item_code || ' ' || p_ingredient_item_code;
226
227 /* Now call the check foreign key procedure */
228
229 Check_Foreign_Keys
230 (p_item_code,
231 p_ingredient_item_code,
232 p_source_item_code,
233 p_level_hierarchy_code,
234 p_work_concentration,
235 p_uom,
236 p_quantity,
237 l_return_status,
238 l_oracle_error,
239 l_msg_data);
240
241 IF l_return_status <> 'S' THEN
242 RAISE Foreign_Key_Error;
243 ELSE
244 UPDATE gr_item_conc_details
245 SET item_code = p_item_code,
246 ingredient_item_code = p_ingredient_item_code,
247 source_item_code = p_source_item_code,
248 level_hierarchy_code = p_level_hierarchy_code,
249 work_concentration = p_work_concentration,
250 uom = p_uom,
251 quantity = p_quantity
252 WHERE rowid = p_rowid;
253 IF SQL%NOTFOUND THEN
254 RAISE Row_Missing_Error;
255 END IF;
256 END IF;
257
258 /* Check the commit flag and if set, then commit the work. */
259
260 IF FND_API.To_Boolean(p_commit) THEN
261 COMMIT WORK;
262 END IF;
263
264 EXCEPTION
265
266 WHEN Foreign_Key_Error THEN
267 ROLLBACK TO SAVEPOINT Update_Row;
268 x_return_status := l_return_status;
269 x_oracle_error := l_oracle_error;
270 FND_MESSAGE.SET_NAME('GR',
271 'GR_FOREIGN_KEY_ERROR');
272 FND_MESSAGE.SET_TOKEN('TEXT',
273 l_msg_data,
274 FALSE);
275 IF FND_API.To_Boolean(p_called_by_form) THEN
276 APP_EXCEPTION.Raise_Exception;
277 ELSE
278 x_msg_data := FND_MESSAGE.Get;
279 END IF;
280
281 WHEN Row_Missing_Error THEN
282 ROLLBACK TO SAVEPOINT Update_Row;
283 x_return_status := 'E';
284 x_oracle_error := APP_EXCEPTION.Get_Code;
285 FND_MESSAGE.SET_NAME('GR',
286 'GR_NO_RECORD_INSERTED');
287 FND_MESSAGE.SET_TOKEN('CODE',
288 l_msg_token,
289 FALSE);
290 IF FND_API.To_Boolean(p_called_by_form) THEN
291 APP_EXCEPTION.Raise_Exception;
292 ELSE
293 x_msg_data := FND_MESSAGE.Get;
294 END IF;
295
296 WHEN OTHERS THEN
297 ROLLBACK TO SAVEPOINT Update_Row;
298 x_return_status := 'U';
299 x_oracle_error := APP_EXCEPTION.Get_Code;
300 l_msg_data := APP_EXCEPTION.Get_Text;
301 FND_MESSAGE.SET_NAME('GR',
302 'GR_UNEXPECTED_ERROR');
303 FND_MESSAGE.SET_TOKEN('TEXT',
304 l_msg_token,
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 END Update_Row;
313
314 PROCEDURE Lock_Row
315 (p_commit IN VARCHAR2,
316 p_called_by_form IN VARCHAR2,
317 p_rowid IN VARCHAR2,
318 p_item_code IN VARCHAR2,
319 p_ingredient_item_code IN VARCHAR2,
320 p_source_item_code IN VARCHAR2,
321 p_level_hierarchy_code IN NUMBER,
322 p_work_concentration IN NUMBER,
323 p_uom IN VARCHAR2,
324 p_quantity IN NUMBER,
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_oracle_error OUT NOCOPY NUMBER,
327 x_msg_data OUT NOCOPY VARCHAR2)
328 IS
329
330 /* Alpha Variables */
331
332 L_RETURN_STATUS VARCHAR2(1) := 'S';
333 L_MSG_DATA VARCHAR2(2000);
334 L_MSG_TOKEN VARCHAR2(80);
335
336 /* Number Variables */
337
338 L_ORACLE_ERROR NUMBER;
339
340 /* Exceptions */
341
342 NO_DATA_FOUND_ERROR EXCEPTION;
343 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
344 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
345
346 /* Define the cursors */
347
348 CURSOR c_lock_concs_tl
349 IS
350 SELECT *
351 FROM gr_item_conc_details
352 WHERE rowid = p_rowid
353 FOR UPDATE NOWAIT;
354 LockConcRcd c_lock_concs_tl%ROWTYPE;
355 BEGIN
356
357 /* Initialization Routine */
358
359 SAVEPOINT Lock_Row;
360 x_return_status := 'S';
361 x_oracle_error := 0;
362 x_msg_data := NULL;
363 l_msg_token := p_item_code || ' ' || p_ingredient_item_code;
364
365 /* Now lock the record */
366
367 OPEN c_lock_concs_tl;
368 FETCH c_lock_concs_tl INTO LockConcRcd;
369 IF c_lock_concs_tl%NOTFOUND THEN
370 CLOSE c_lock_concs_tl;
371 RAISE No_Data_Found_Error;
372 END IF;
373 CLOSE c_lock_concs_tl;
374
375 IF FND_API.To_Boolean(p_commit) THEN
376 COMMIT WORK;
377 END IF;
378
379 EXCEPTION
380
381 WHEN No_Data_Found_Error THEN
382 ROLLBACK TO SAVEPOINT Lock_Row;
383 x_return_status := 'E';
384 FND_MESSAGE.SET_NAME('GR',
385 'GR_RECORD_NOT_FOUND');
386 FND_MESSAGE.SET_TOKEN('CODE',
387 l_msg_token,
388 FALSE);
389 IF FND_API.To_Boolean(p_called_by_form) THEN
390 APP_EXCEPTION.Raise_Exception;
391 ELSE
392 x_msg_data := FND_MESSAGE.Get;
393 END IF;
394
395 WHEN Row_Already_Locked_Error THEN
396 ROLLBACK TO SAVEPOINT Lock_Row;
397 x_return_status := 'E';
398 x_oracle_error := APP_EXCEPTION.Get_Code;
399 FND_MESSAGE.SET_NAME('GR',
400 'GR_ROW_IS_LOCKED');
401 IF FND_API.To_Boolean(p_called_by_form) THEN
402 APP_EXCEPTION.Raise_Exception;
403 ELSE
404 x_msg_data := FND_MESSAGE.Get;
405 END IF;
406
407 WHEN OTHERS THEN
408 ROLLBACK TO SAVEPOINT Lock_Row;
409 x_return_status := 'U';
410 x_oracle_error := APP_EXCEPTION.Get_Code;
411 l_msg_data := APP_EXCEPTION.Get_Text;
412 FND_MESSAGE.SET_NAME('GR',
413 'GR_UNEXPECTED_ERROR');
414 FND_MESSAGE.SET_TOKEN('TEXT',
415 l_msg_token,
416 FALSE);
417 IF FND_API.To_Boolean(p_called_by_form) THEN
418 APP_EXCEPTION.Raise_Exception;
419 ELSE
420 x_msg_data := FND_MESSAGE.Get;
421 END IF;
422
423 END Lock_Row;
424
425 PROCEDURE Delete_Row
426 (p_commit IN VARCHAR2,
427 p_called_by_form IN VARCHAR2,
428 p_rowid IN VARCHAR2,
429 p_item_code IN VARCHAR2,
430 p_ingredient_item_code IN VARCHAR2,
431 p_source_item_code IN VARCHAR2,
432 p_level_hierarchy_code IN NUMBER,
433 p_work_concentration IN NUMBER,
434 p_uom IN VARCHAR2,
435 p_quantity IN NUMBER,
436 x_return_status OUT NOCOPY VARCHAR2,
437 x_oracle_error OUT NOCOPY NUMBER,
438 x_msg_data OUT NOCOPY VARCHAR2)
439 IS
440
441 /* Alpha Variables */
442
443 L_RETURN_STATUS VARCHAR2(1) := 'S';
444 L_MSG_DATA VARCHAR2(2000);
445 L_MSG_TOKEN VARCHAR2(80);
446 L_CALLED_BY_FORM VARCHAR2(1);
447
448 /* Number Variables */
449
450 L_ORACLE_ERROR NUMBER;
451
452 /* Exceptions */
453
454 CHECK_INTEGRITY_ERROR EXCEPTION;
455 ROW_MISSING_ERROR EXCEPTION;
456 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
457 BEGIN
458
459 /* Initialization Routine */
460
461 SAVEPOINT Delete_Row;
462 x_return_status := 'S';
463 x_oracle_error := 0;
464 x_msg_data := NULL;
465 l_msg_token := p_item_code || ' ' || p_ingredient_item_code;
466
467 /* Now call the check integrity procedure */
468
469 Check_Integrity
470 (p_called_by_form,
471 p_item_code,
472 p_ingredient_item_code,
473 p_level_hierarchy_code,
474 p_source_item_code,
475 p_work_concentration,
476 p_uom,
477 p_quantity,
478 l_return_status,
479 l_oracle_error,
480 l_msg_data);
481
482 IF l_return_status <> 'S' THEN
483 RAISE Check_Integrity_Error;
484 END IF;
485
486 DELETE FROM gr_item_conc_details
487 WHERE rowid = p_rowid;
488
489 /* Check the commit flag and if set, then commit the work. */
490
491 IF FND_API.TO_Boolean(p_commit) THEN
495 EXCEPTION
492 COMMIT WORK;
493 END IF;
494
496
497 WHEN Check_Integrity_Error THEN
498 ROLLBACK TO SAVEPOINT Delete_Row;
499 x_return_status := l_return_status;
500 x_oracle_error := l_oracle_error;
501 IF FND_API.To_Boolean(p_called_by_form) THEN
502 APP_EXCEPTION.Raise_Exception;
503 ELSE
504 x_msg_data := FND_MESSAGE.Get;
505 END IF;
506
507 WHEN Row_Missing_Error THEN
508 ROLLBACK TO SAVEPOINT Delete_Row;
509 x_return_status := 'E';
510 x_oracle_error := APP_EXCEPTION.Get_Code;
511 FND_MESSAGE.SET_NAME('GR',
512 'GR_RECORD_NOT_FOUND');
513 FND_MESSAGE.SET_TOKEN('CODE',
514 l_msg_token,
515 FALSE);
516 IF FND_API.To_Boolean(p_called_by_form) THEN
517 APP_EXCEPTION.Raise_Exception;
518 ELSE
519 x_msg_data := FND_MESSAGE.Get;
520 END IF;
521
522 WHEN OTHERS THEN
523 ROLLBACK TO SAVEPOINT Delete_Row;
524 x_return_status := 'U';
525 x_oracle_error := APP_EXCEPTION.Get_Code;
526 l_msg_data := APP_EXCEPTION.Get_Text;
527 l_msg_data := APP_EXCEPTION.Get_Text;
528 FND_MESSAGE.SET_NAME('GR',
529 'GR_UNEXPECTED_ERROR');
530 FND_MESSAGE.SET_TOKEN('TEXT',
531 l_msg_token,
532 FALSE);
533 IF FND_API.To_Boolean(p_called_by_form) THEN
534 APP_EXCEPTION.Raise_Exception;
535 ELSE
536 x_msg_data := FND_MESSAGE.Get;
537 END IF;
538
539 END Delete_Row;
540
541 PROCEDURE Delete_Rows
542 (p_commit IN VARCHAR2,
543 p_called_by_form IN VARCHAR2,
544 p_item_code IN VARCHAR2,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_oracle_error OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2)
548 IS
549
550 /* Alpha Variables */
551
552 L_RETURN_STATUS VARCHAR2(1) := 'S';
553 L_MSG_DATA VARCHAR2(2000);
554 L_MSG_TOKEN VARCHAR2(80);
555
556 /* Number Variables */
557
558 L_ORACLE_ERROR NUMBER;
559
560 /* Define the cursors */
561
562 BEGIN
563
564 /* Initialization Routine */
565
566 SAVEPOINT Delete_Rows;
567 x_return_status := 'S';
568 x_oracle_error := 0;
569 x_msg_data := NULL;
570 l_msg_token := p_item_code;
571
572 DELETE FROM gr_item_conc_details
573 WHERE item_code = p_item_code;
574
575 IF FND_API.To_Boolean(p_commit) THEN
576 COMMIT WORK;
577 END IF;
578
579 EXCEPTION
580
581 WHEN OTHERS THEN
582 ROLLBACK TO SAVEPOINT Delete_Rows;
583 x_return_status := 'U';
584 x_oracle_error := APP_EXCEPTION.Get_Code;
585 l_msg_data := APP_EXCEPTION.Get_Text;
586 FND_MESSAGE.SET_NAME('GR',
587 'GR_UNEXPECTED_ERROR');
588 FND_MESSAGE.SET_TOKEN('TEXT',
589 l_msg_token,
590 FALSE);
591 IF FND_API.To_Boolean(p_called_by_form) THEN
592 APP_EXCEPTION.Raise_Exception;
593 ELSE
594 x_msg_data := FND_MESSAGE.Get;
595 END IF;
596
597 END Delete_Rows;
598
599 PROCEDURE Check_Foreign_Keys
600 (p_item_code IN VARCHAR2,
601 p_ingredient_item_code IN VARCHAR2,
602 p_source_item_code IN VARCHAR2,
603 p_level_hierarchy_code IN NUMBER,
604 p_work_concentration IN NUMBER,
605 p_uom IN VARCHAR2,
606 p_quantity IN NUMBER,
607 x_return_status OUT NOCOPY VARCHAR2,
608 x_oracle_error OUT NOCOPY NUMBER,
609 x_msg_data OUT NOCOPY VARCHAR2)
610 IS
611
612 /* Alpha Variables */
613
614 L_RETURN_STATUS VARCHAR2(1) := 'S';
615 L_MSG_DATA VARCHAR2(2000);
616 L_MSG_TOKEN VARCHAR2(80);
617
618 /* Number Variables */
619
620 L_ORACLE_ERROR NUMBER;
621
622 /* Define the cursors */
623
624 BEGIN
625
626 /* Initialization Routine */
627
628 SAVEPOINT Check_Foreign_Keys;
629 x_return_status := 'S';
630 x_oracle_error := 0;
631 x_msg_data := NULL;
632 l_msg_token := p_item_code || ' ' || p_ingredient_item_code;
633
634 /* No foreign key references to check */
635
636 IF x_return_status <> 'S' THEN
637 x_msg_data := l_msg_data;
638 END IF;
639
640 EXCEPTION
641
642 WHEN OTHERS THEN
643 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
644 x_return_status := 'U';
645 x_oracle_error := APP_EXCEPTION.Get_Code;
646 l_msg_data := APP_EXCEPTION.Get_Text;
647 FND_MESSAGE.SET_NAME('GR',
648 'GR_UNEXPECTED_ERROR');
649 FND_MESSAGE.SET_TOKEN('TEXT',
650 l_msg_token,
651 FALSE);
652 x_msg_data := FND_MESSAGE.Get;
653
654 END Check_Foreign_Keys;
655
656 PROCEDURE Check_Integrity
660 p_source_item_code IN VARCHAR2,
657 (p_called_by_form IN VARCHAR2,
658 p_item_code IN VARCHAR2,
659 p_ingredient_item_code IN VARCHAR2,
661 p_level_hierarchy_code IN NUMBER,
662 p_work_concentration IN NUMBER,
663 p_uom IN VARCHAR2,
664 p_quantity IN NUMBER,
665 x_return_status OUT NOCOPY VARCHAR2,
666 x_oracle_error OUT NOCOPY NUMBER,
667 x_msg_data OUT NOCOPY VARCHAR2)
668 IS
669
670 /* Alpha Variables */
671
672 L_RETURN_STATUS VARCHAR2(1) := 'S';
673 L_MSG_DATA VARCHAR2(2000);
674 L_CODE_BLOCK VARCHAR2(80);
675
676 /* Number Variables */
677
678 L_ORACLE_ERROR NUMBER;
679 L_RECORD_COUNT NUMBER;
680
681 /* Define the Cursors */
682
683 BEGIN
684
685 /* Initialization Routine */
686
687 SAVEPOINT Check_Integrity;
688 x_return_status := 'S';
689 x_oracle_error := 0;
690 x_msg_data := NULL;
691
692 /* No integrity checking is needed */
693
694 EXCEPTION
695
696 WHEN OTHERS THEN
697 ROLLBACK TO SAVEPOINT Check_Integrity;
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_data,
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 Check_Integrity;
713
714 PROCEDURE Check_Primary_Key
715 /* p_item_code is the item code to check.
716 ** p_ingredient_item_code is the second part of the key
717 ** p_source_item_code is the third part of the key
718 ** p_called_by_form is 'T' if called by a form or 'F' if not.
719 ** x_rowid is the row id of the record if found.
720 ** x_key_exists is 'T' is the record is found, 'F' if not.
721 */
722 (p_item_code IN VARCHAR2,
723 p_ingredient_item_code IN VARCHAR2,
724 p_source_item_code IN VARCHAR2,
725 p_called_by_form IN VARCHAR2,
726 x_rowid OUT NOCOPY VARCHAR2,
727 x_key_exists OUT NOCOPY VARCHAR2)
728 IS
729 /* Alphanumeric variables */
730
731 L_MSG_DATA VARCHAR2(80);
732
733 /* Declare any variables and the cursor */
734
735
736 CURSOR c_get_concs_rowid
737 IS
738 SELECT icd.rowid
739 FROM gr_item_conc_details icd
740 WHERE icd.item_code = p_item_code
741 AND icd.ingredient_item_code = p_ingredient_item_code
742 AND icd.source_item_code = p_source_item_code;
743 ConcRecord c_get_concs_rowid%ROWTYPE;
744
745 BEGIN
746
747 l_msg_data := p_item_code || ' ' || p_ingredient_item_code;
748
749 x_key_exists := 'F';
750 OPEN c_get_concs_rowid;
751 FETCH c_get_concs_rowid INTO ConcRecord;
752 IF c_get_concs_rowid%FOUND THEN
753 x_key_exists := 'T';
754 x_rowid := ConcRecord.rowid;
755 ELSE
756 x_key_exists := 'F';
757 END IF;
758 CLOSE c_get_concs_rowid;
759
760 EXCEPTION
761
762 WHEN Others THEN
763 l_msg_data := APP_EXCEPTION.Get_Text;
764 FND_MESSAGE.SET_NAME('GR',
765 'GR_UNEXPECTED_ERROR');
766 FND_MESSAGE.SET_TOKEN('TEXT',
767 l_msg_data,
768 FALSE);
769 IF FND_API.To_Boolean(p_called_by_form) THEN
770 APP_EXCEPTION.Raise_Exception;
771 END IF;
772
773 END Check_Primary_Key;
774
775 END GR_ITEM_CONC_DETAILS_PKG;