[Home] [Help]
PACKAGE BODY: APPS.GR_INGRED_CONCENTRATIONS_PKG
Source
1 PACKAGE BODY GR_INGRED_CONCENTRATIONS_PKG AS
2 /*$Header: GRHINCB.pls 120.0 2005/07/08 11:17:56 methomas 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_product_item_id IN NUMBER,
8 p_ingredient_item_id 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_product_item_id || ' ' || p_ingredient_item_id;
49
50 /* Now call the check foreign key procedure */
51
52 Check_Foreign_Keys
53 (p_organization_id,
54 p_product_item_id,
55 p_concentration_percentage,
56 l_return_status,
57 l_oracle_error,
58 l_msg_data);
59 IF l_return_status <> 'S' THEN
60 RAISE Foreign_Key_Error;
61 END IF;
62
63 /* Now check the primary key doesn't already exist */
64
65 Check_Primary_Key
66 (p_organization_id,
67 p_product_item_id,
68 p_ingredient_item_id,
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_ingredient_concentrations
78 (organization_id,
79 product_item_id,
80 ingredient_item_id,
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_organization_id,
89 p_product_item_id,
90 p_ingredient_item_id,
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_organization_id,
102 p_product_item_id,
103 p_ingredient_item_id,
104 'F',
105 l_rowid,
106 l_key_exists);
107
108 IF FND_API.To_Boolean(l_key_exists) THEN
109 x_rowid := l_rowid;
110 ELSE
111 RAISE Row_Missing_Error;
112 END IF;
113
114 /* Check the commit flag and if set, then commit the work. */
115
116 IF FND_API.To_Boolean(p_commit) THEN
117 COMMIT WORK;
118 END IF;
119
120 EXCEPTION
121
122 WHEN Foreign_Key_Error THEN
123 ROLLBACK TO SAVEPOINT Insert_Row;
124 x_return_status := l_return_status;
125 x_oracle_error := l_oracle_error;
126 FND_MESSAGE.SET_NAME('GR',
127 'GR_FOREIGN_KEY_ERROR');
128 FND_MESSAGE.SET_TOKEN('TEXT',
129 l_msg_data,
130 FALSE);
131 IF FND_API.To_Boolean(p_called_by_form) THEN
132 APP_EXCEPTION.Raise_Exception;
133 ELSE
134 x_msg_data := FND_MESSAGE.Get;
135 END IF;
136
137 WHEN Item_Exists_Error THEN
138 ROLLBACK TO SAVEPOINT Insert_Row;
139 l_msg_token := p_product_item_id;
140 x_return_status := 'E';
141 x_oracle_error := APP_EXCEPTION.Get_Code;
142 FND_MESSAGE.SET_NAME('GR',
143 'GR_RECORD_EXISTS');
144 FND_MESSAGE.SET_TOKEN('CODE',
145 l_msg_token,
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 Row_Missing_Error THEN
154 ROLLBACK TO SAVEPOINT Insert_Row;
155 l_msg_token := p_product_item_id;
156 x_return_status := 'E';
157 x_oracle_error := APP_EXCEPTION.Get_Code;
158 FND_MESSAGE.SET_NAME('GR',
159 'GR_NO_RECORD_INSERTED');
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 OTHERS THEN
170 ROLLBACK TO SAVEPOINT Insert_Row;
171 l_msg_token := p_product_item_id;
172 x_return_status := 'U';
173 x_oracle_error := APP_EXCEPTION.Get_Code;
174 l_msg_data := APP_EXCEPTION.Get_Text;
175 FND_MESSAGE.SET_NAME('GR',
176 'GR_UNEXPECTED_ERROR');
177 FND_MESSAGE.SET_TOKEN('TEXT',
178 l_msg_token,
179 FALSE);
180 IF FND_API.To_Boolean(p_called_by_form) THEN
181 APP_EXCEPTION.Raise_Exception;
182 ELSE
183 x_msg_data := FND_MESSAGE.Get;
184 END IF;
185
186 END Insert_Row;
187
188 PROCEDURE Update_Row
189 (p_commit IN VARCHAR2,
190 p_called_by_form IN VARCHAR2,
191 p_rowid IN VARCHAR2,
192 p_organization_id IN NUMBER,
193 p_product_item_id IN NUMBER,
194 p_ingredient_item_id IN NUMBER,
195 p_concentration_percentage IN NUMBER,
196 p_last_update_date IN DATE,
197 p_last_updated_by IN NUMBER,
198 p_last_update_login IN NUMBER,
199 p_creation_date IN DATE,
200 p_created_by IN NUMBER,
201 x_return_status OUT NOCOPY VARCHAR2,
202 x_oracle_error OUT NOCOPY NUMBER,
203 x_msg_data OUT NOCOPY VARCHAR2)
204 IS
205
206 /* Alpha Variables */
207
208 L_RETURN_STATUS VARCHAR2(1) := 'S';
209 L_MSG_DATA VARCHAR2(2000);
210 L_MSG_TOKEN VARCHAR2(80);
211
212 /* Number Variables */
213
214 L_ORACLE_ERROR NUMBER;
215
216 /* Exceptions */
217
218 FOREIGN_KEY_ERROR EXCEPTION;
219 ROW_MISSING_ERROR EXCEPTION;
220 BEGIN
221
222 /* Initialization Routine */
223
224 SAVEPOINT Update_Row;
225 x_return_status := 'S';
226 x_oracle_error := 0;
227 x_msg_data := NULL;
228 l_msg_token := p_product_item_id || ' ' || p_ingredient_item_id;
229
230 /* Now call the check foreign key procedure */
231
232 Check_Foreign_Keys
233 (p_organization_id,
234 p_product_item_id,
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_ingredient_concentrations
244 SET organization_id = p_organization_id,
245 product_item_id = p_product_item_id,
246 ingredient_item_id = p_ingredient_item_id,
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_organization_id IN NUMBER,
321 p_product_item_id IN NUMBER,
322 p_ingredient_item_id 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_ingredient_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_product_item_id || ' ' || p_ingredient_item_id;
368
369 /* Now lock the record */
370
371 OPEN c_lock_concs_tl;
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_organization_id IN NUMBER,
434 p_product_item_id IN NUMBER,
435 p_ingredient_item_id 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_product_item_id || ' ' || p_ingredient_item_id;
474
475 /* Now call the check integrity procedure */
476
477 Check_Integrity
478 (l_called_by_form,
479 p_organization_id,
480 p_product_item_id,
481 p_concentration_percentage,
482 l_return_status,
483 l_oracle_error,
484 l_msg_data);
485
486 IF l_return_status <> 'S' THEN
487 RAISE Check_Integrity_Error;
488 END IF;
489
490 DELETE FROM gr_ingredient_concentrations
491 WHERE rowid = p_rowid;
492
496 COMMIT WORK;
493 /* Check the commit flag and if set, then commit the work. */
494
495 IF FND_API.TO_Boolean(p_commit) THEN
497 END IF;
498
499 EXCEPTION
500
501 WHEN Check_Integrity_Error THEN
502 ROLLBACK TO SAVEPOINT Delete_Row;
503 x_return_status := l_return_status;
504 x_oracle_error := l_oracle_error;
505 IF FND_API.To_Boolean(p_called_by_form) THEN
506 APP_EXCEPTION.Raise_Exception;
507 ELSE
508 x_msg_data := FND_MESSAGE.Get;
509 END IF;
510
511 WHEN Row_Missing_Error THEN
512 ROLLBACK TO SAVEPOINT Delete_Row;
513 x_return_status := 'E';
514 x_oracle_error := APP_EXCEPTION.Get_Code;
515 FND_MESSAGE.SET_NAME('GR',
516 'GR_RECORD_NOT_FOUND');
517 FND_MESSAGE.SET_TOKEN('CODE',
518 l_msg_token,
519 FALSE);
520 IF FND_API.To_Boolean(p_called_by_form) THEN
521 APP_EXCEPTION.Raise_Exception;
522 ELSE
523 x_msg_data := FND_MESSAGE.Get;
524 END IF;
525
526 WHEN OTHERS THEN
527 ROLLBACK TO SAVEPOINT Delete_Row;
528 x_return_status := 'U';
529 x_oracle_error := APP_EXCEPTION.Get_Code;
530 l_msg_data := APP_EXCEPTION.Get_Text;
531 l_msg_data := APP_EXCEPTION.Get_Text;
532 FND_MESSAGE.SET_NAME('GR',
533 'GR_UNEXPECTED_ERROR');
534 FND_MESSAGE.SET_TOKEN('TEXT',
535 l_msg_token,
536 FALSE);
537 IF FND_API.To_Boolean(p_called_by_form) THEN
538 APP_EXCEPTION.Raise_Exception;
539 ELSE
540 x_msg_data := FND_MESSAGE.Get;
541 END IF;
542
543 END Delete_Row;
544
545 PROCEDURE Delete_Rows
546 (p_commit IN VARCHAR2,
547 p_called_by_form IN VARCHAR2,
548 p_organization_id IN NUMBER,
549 p_product_item_id IN NUMBER,
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_product_item_id;
576
577 DELETE FROM gr_ingredient_concentrations
578 WHERE organization_id = p_organization_id
579 AND product_item_id = p_product_item_id;
580
581 IF FND_API.To_Boolean(p_commit) THEN
582 COMMIT WORK;
583 END IF;
584
585 EXCEPTION
586
587 WHEN OTHERS THEN
588 ROLLBACK TO SAVEPOINT Delete_Rows;
589 x_return_status := 'U';
590 x_oracle_error := APP_EXCEPTION.Get_Code;
591 l_msg_data := APP_EXCEPTION.Get_Text;
592 FND_MESSAGE.SET_NAME('GR',
593 'GR_UNEXPECTED_ERROR');
594 FND_MESSAGE.SET_TOKEN('TEXT',
595 l_msg_token,
596 FALSE);
597 IF FND_API.To_Boolean(p_called_by_form) THEN
598 APP_EXCEPTION.Raise_Exception;
599 ELSE
600 x_msg_data := FND_MESSAGE.Get;
601 END IF;
602
603 END Delete_Rows;
604
605 PROCEDURE Check_Foreign_Keys
606 (p_organization_id IN NUMBER,
607 p_product_item_id 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_product_item_id;
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_organization_id IN NUMBER,
663 p_product_item_id IN NUMBER,
664 p_concentration_percentage 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(30);
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_product_item_id is the item id to check.
716 ** p_ingredient_item_id is the second part of the key
717 ** p_called_by_form is 'T' if called by a form or 'F' if not.
718 ** x_rowid is the row id of the record if found.
719 ** x_key_exists is 'T' is the record is found, 'F' if not.
720 */
721 (p_organization_id IN NUMBER,
722 p_product_item_id IN NUMBER,
723 p_ingredient_item_id IN NUMBER,
724 p_called_by_form IN VARCHAR2,
728 /* Alphanumeric variables */
725 x_rowid OUT NOCOPY VARCHAR2,
726 x_key_exists OUT NOCOPY VARCHAR2)
727 IS
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_ingredient_concentrations ic
739 WHERE ic.organization_id = p_organization_id
740 AND ic.product_item_id = p_product_item_id
741 AND ic.ingredient_item_id = p_ingredient_item_id;
742 ConcRecord c_get_concs_rowid%ROWTYPE;
743
744 BEGIN
745
746 l_msg_data := p_product_item_id || ' ' || p_ingredient_item_id;
747
748 x_key_exists := 'F';
749 OPEN c_get_concs_rowid;
750 FETCH c_get_concs_rowid INTO ConcRecord;
751 IF c_get_concs_rowid%FOUND THEN
752 x_key_exists := 'T';
753 x_rowid := ConcRecord.rowid;
754 ELSE
755 x_key_exists := 'F';
756 END IF;
757 CLOSE c_get_concs_rowid;
758
759 EXCEPTION
760
761 WHEN Others THEN
762 l_msg_data := APP_EXCEPTION.Get_Text;
766 l_msg_data,
763 FND_MESSAGE.SET_NAME('GR',
764 'GR_UNEXPECTED_ERROR');
765 FND_MESSAGE.SET_TOKEN('TEXT',
767 FALSE);
768 IF FND_API.To_Boolean(p_called_by_form) THEN
769 APP_EXCEPTION.Raise_Exception;
770 END IF;
771
772 END Check_Primary_Key;
773
774 END GR_INGRED_CONCENTRATIONS_PKG;