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