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