1 PACKAGE BODY GMICVAL AS
2 /* $Header: gmicvalb.pls 115.6 2004/04/30 17:40:42 adeshmuk ship $ */
3 /* =============================================
4 PROCEDURE:
5 trans_date_val
6
7 DESCRIPTION:
8 This PL/SQL function is responsible for
9 validating that a transaction date is in
10 an open warehouse and inventory calendar period.
11
12 SYNOPSIS:
13 iret := GMICVAL.trans_date_val(ptrans_date,
14 porgn_code, pwhse_code);
15
16 ptrans_date - transaction date
17 porgn_code - organization associated to the warehouse.
18 pwhse_code - warehouse affected by the transaction.
19
20 RETURNS:
21 0 Success
22 ============================================= */
23 PROCEDURE trans_date_val(ptrans_date DATE,
24 porgn_code VARCHAR2,
25 pwhse_code VARCHAR2) IS
26
27 /* Local variable initialization and
28 declarations.
29 =================================*/
30 l_iret NUMBER := -1;
31
32 BEGIN
33
34 l_iret := GMICCAL.trans_date_validate(ptrans_date,
35 porgn_code, pwhse_code);
36
37 IF (l_iret = -21) THEN
38 FND_MESSAGE.set_name('GMI', 'INVCAL_FISCALYR_ERR');
39 APP_EXCEPTION.raise_exception;
40
41 ELSIF (l_iret = -22) THEN
42 FND_MESSAGE.set_name('GMI', 'INVCAL_PERIOD_ERR');
43 APP_EXCEPTION.raise_exception;
44
45 ELSIF (l_iret = -23) THEN
46 FND_MESSAGE.set_name('GMI', 'INVCAL_CLOSED_PERIOD_ERR');
47 APP_EXCEPTION.raise_exception;
48
49 ELSIF (l_iret = -24) THEN
50 FND_MESSAGE.set_name('GMI', 'INVCAL_INVALIDCO_ERR');
51 APP_EXCEPTION.raise_exception;
52
53 ELSIF (l_iret = -25) THEN
54 FND_MESSAGE.set_name('GMI', 'INVCAL_WHSE_CLOSED_ERR');
55 APP_EXCEPTION.raise_exception;
56
57 ELSIF (l_iret = -26) THEN
58 FND_MESSAGE.set_name('GMI', 'INVCAL_TRANS_DATE_ERR');
59 APP_EXCEPTION.raise_exception;
60
61 ELSIF (l_iret = -27) THEN
62 FND_MESSAGE.set_name('GMI', 'INVCAL_INVALIDORGN_ERR');
63 APP_EXCEPTION.raise_exception;
64
65 ELSIF (l_iret = -28) THEN
66 FND_MESSAGE.set_name('GMI', 'INVCAL_WHSEPARM_ERR');
67 APP_EXCEPTION.raise_exception;
68
69 ELSIF (l_iret = -29) THEN
70 FND_MESSAGE.set_name('GMI', 'INVCAL_WHSE_ERR');
71 APP_EXCEPTION.raise_exception;
72
73 ELSIF (l_iret < -29) THEN
74 FND_MESSAGE.set_name('GMI', 'INVCAL_GENL_ERR');
75 APP_EXCEPTION.raise_exception;
76
77 END IF;
78
79 END trans_date_val;
80 /* =============================================
81 PROCEDURE:
82 deviation_val
83
84 DESCRIPTION:
85 This PL/SQL procedure is responsible for
86 calling the unit of measure deviation function
87 contained in this package and handling all
88 error messaging centrally.
89
90 SYNOPSIS:
91 GMICVAL.deviation_val(pitem_id, plot_id, pcur_qty,
92 pcur_uom, pnew_qty, pnew_uom);
93
94 ============================================= */
95 PROCEDURE deviation_val(pitem_id NUMBER,
96 plot_id NUMBER,
97 pcur_qty NUMBER,
98 pcur_uom VARCHAR2,
99 pnew_qty NUMBER,
100 pnew_uom VARCHAR2) IS
101
102 /* Local variable initialization and
103 declarations.
104 =================================*/
105 l_iret NUMBER := -1;
106 l_atomic NUMBER := 0;
107 l_neg_flag NUMBER := 0;
108 l_cur_qty NUMBER := 0;
109 l_new_qty NUMBER := 0;
110
111 BEGIN
112
113 /* Now let's check for dualum types 2 and 3 deviation
114 if appropriate.
115 ==================================================*/
116 IF(pcur_qty < 0) THEN
117 l_cur_qty := (pcur_qty * -1);
118 ELSE
119 l_cur_qty := pcur_qty;
120 END IF;
121
122 l_new_qty := pnew_qty;
123
124 l_iret := GMICVAL.dev_validation(pitem_id, plot_id, l_cur_qty,
125 pcur_uom, l_new_qty, pnew_uom, l_atomic);
126
127 IF(l_iret = -68) THEN
128 FND_MESSAGE.set_name('GMI', 'IC_DEVIATION_HI_ERR');
129 APP_EXCEPTION.raise_exception;
130
131 ELSIF (l_iret = -69) THEN
132 FND_MESSAGE.set_name('GMI', 'IC_DEVIATION_LO_ERR');
133 APP_EXCEPTION.raise_exception;
134 END IF;
135
136 END deviation_val;
137 /* =============================================
138 PROCEDURE:
139 itm_loct_validation
140
141 DESCRIPTION:
142 This PL/SQL function is responsible for
143 validating that a location is properly
144 validated. Please refer to the below grid.
145
146 XXXXX | whse0 | whse1 | whse2
147 ======= =====================
148 item0 | 0 | 0 | 0 0 = NO control
149 ============================= 1 = Location Controlled
150 item1 | 0 | 1 | 2 2 = Non-Validated
151 =============================
152 item2 | 0 | 2 | 2
153
154 SYNOPSIS:
155 iret := GMICVAL.location_validation(plocation,
156 pwhse_code,ploct_ctl);
157
158 plocation - location to be validated
159 pwhse_code - warehouse where the location resides.
160 ploct_ctl - the items loction control indicator
161 as found in ic_item_mst.
162
163 RETURNS:
164 0 Success
165 -62 Location is not valid.
166 -65 Warehouse is not valid.
167 -76 Cannot retrieve warehouse controls.
168 -77 System default LOCATION not found.
169 -78 Using default location ERROR.
170 -82 The item is not location controlled.
171 ============================================= */
172 PROCEDURE itm_loct_validation(plocation VARCHAR2,
173 pwhse_code VARCHAR2,
174 ploct_ctl NUMBER) IS
175
176 /* Local variable initialization and
177 declarations.
178 =================================*/
179 l_iret NUMBER := -1;
180
181 BEGIN
182
183 l_iret := GMICVAL.itm_location_val(plocation,
184 pwhse_code, ploct_ctl);
185
186 IF (l_iret = -62) THEN
187 FND_MESSAGE.set_name('GMI', 'IC_LOCATION_ERR');
188 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
189 APP_EXCEPTION.raise_exception;
190 ELSIF (l_iret = -65) THEN
191 FND_MESSAGE.set_name('GMI', 'IC_WHSE_ERR');
192 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
193 APP_EXCEPTION.raise_exception;
194 ELSIF (l_iret = -76) THEN
195 FND_MESSAGE.set_name('GMI', 'IC_WHSE_CNTLS_ERR');
196 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
197 APP_EXCEPTION.raise_exception;
198 ELSIF (l_iret = -77) THEN
199 FND_MESSAGE.set_name('GMI', 'IC_DEFAULT_LOCT_ERR');
200 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
201 APP_EXCEPTION.raise_exception;
202 ELSIF (l_iret = -78) THEN
203 FND_MESSAGE.set_name('GMI', 'IC_USING_DEFAULT_LOCT_ERR');
204 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
205 APP_EXCEPTION.raise_exception;
206 ELSIF (l_iret = -82) THEN
207 FND_MESSAGE.set_name('GMI', 'IC_NOT_LOCT_CTL_ERR');
208 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
209 APP_EXCEPTION.raise_exception;
210 END IF;
211
212 END itm_loct_validation;
213 /* =============================================
214 FUNCTION:
215 item_val OVER LOADED FUNCTION!
216
217 DESCRIPTION:
218 This PL/SQL function is responsible for
219 validating items based on item_id passed.
220 This is an over loaded function call.
221 The second way to call this validation is
222 by item_no. See below function.
223
224 SYNOPSIS:
225 iret := GMICVAL.item_val(pitem_id);
226
227 pitem_id the item surrogate of the item you
228 are validating.
229
230 RETURNS:
231 0 Success
232 -75 Reason code not valid.
233 ============================================= */
234 FUNCTION item_val(pitem_id NUMBER)
235 RETURN NUMBER IS
236
237 /* Cursor Definitions
238 ==================*/
239 CURSOR validate_item IS
240 SELECT item_no
241 FROM ic_item_mst
242 WHERE item_id = pitem_id
243 AND delete_mark = 0;
244
245 /* Local variables.
246 ================*/
247 l_item_no ic_item_mst.item_no%TYPE;
248
249 /* ================================================*/
250 BEGIN
251
252
253 /* ==================================
254
255 Initialize Variables
256 ==================== */
257 l_item_no := NULL;
258
259
260 OPEN validate_item;
261 FETCH validate_item INTO
262 l_item_no;
263
264 IF(validate_item%NOTFOUND) THEN
265
266 CLOSE validate_item;
267 RETURN VAL_ITEM_ERR;
268
269 END IF;
270 CLOSE validate_item;
271 RETURN 0;
272
273
274 EXCEPTION
275 WHEN OTHERS THEN
276
277 RETURN SQLCODE;
278 END item_val;
279 /* =============================================
280 FUNCTION:
281 item_val OVER LOADED FUNCTION!
282
283 DESCRIPTION:
284 This PL/SQL function is responsible for
285 validating items based on item_id passed.
286 This is an over loaded function call.
287 The second way to call this validation is
288 by item_id. See above function.
289
290 SYNOPSIS:
291 iret := GMICVAL.item_val(pitem_no);
292
293 pitem_no the item number of the item you
294 are validating.
295
296 RETURNS:
297 0 Success
298 -75 Reason code not valid.
299 ============================================= */
300 FUNCTION item_val(pitem_no VARCHAR2)
301 RETURN NUMBER IS
302
303 /* Cursor Definitions
304 ==================*/
305 CURSOR validate_item IS
306 SELECT item_id
307 FROM ic_item_mst
308 WHERE item_no = UPPER(pitem_no)
309 AND delete_mark = 0;
310
311 /* Local variables.
312 ================*/
313 l_item_id item_surg_type;
314
315 /* ================================================ */
316 BEGIN
317
318
319 /* ==================================
320
321 Initialize Variables
322 ====================*/
323 l_item_id := 0;
324
325
326 OPEN validate_item;
327 FETCH validate_item INTO
328 l_item_id;
329
330 IF(validate_item%NOTFOUND) THEN
331
332 CLOSE validate_item;
333 RETURN VAL_ITEM_ERR;
334
335 END IF;
336 CLOSE validate_item;
337 RETURN 0;
338
339
340 EXCEPTION
341 WHEN OTHERS THEN
342
343 RETURN SQLCODE;
344 END item_val;
345 /* =============================================
346 FUNCTION:
347 reason_code_val
348
349 DESCRIPTION:
350 This PL/SQL function is responsible for
351 validating valid reason codes.
352
353 SYNOPSIS:
354 iret := GMICVAL.reason_code_val(preason_code);
355
356 preason_code the reason code you are working on.
357
358 RETURNS:
359 0 Success
360 -61 Reason code not valid.
361 ============================================= */
362 FUNCTION reason_code_val(preason_code VARCHAR2)
363 RETURN NUMBER IS
364
365 /* Cursor Definitions
366 ================== */
367 CURSOR validate_reason IS
368 SELECT reason_desc1
369 FROM sy_reas_cds
370 WHERE reason_code = UPPER(preason_code)
371 AND delete_mark = 0;
372
373 /* Local variables.
374 ================*/
375 -- Shikha Nagar B2464367 made variable to VARCHAR2(80) from reason_type
376 l_reason_desc1 VARCHAR2(40);
377
378 BEGIN
379
380
381 /* Initialize Variables
382 ==================== */
383 l_reason_desc1 := NULL;
384
385
386 OPEN validate_reason;
387 FETCH validate_reason INTO
388 l_reason_desc1;
389
390 IF(validate_reason%NOTFOUND) THEN
391
392 CLOSE validate_reason;
393 RETURN VAL_REASONCODE_ERR;
394
395 END IF;
396 CLOSE validate_reason;
397 RETURN 0;
398
399
400 EXCEPTION
401 WHEN OTHERS THEN
402
403 RETURN SQLCODE;
404 END reason_code_val;
405 /* =============================================
406 FUNCTION:
407 itm_location_val
408
409 DESCRIPTION:
410 This PL/SQL function is responsible for
411 validating an items inventory location.
412
413 SYNOPSIS:
414 iret := GMICVAL.itm_location_val(plocation, pwhse_code,
415 ploct_ctl);
416
417 plocation the location you are working on.
418 pwhse_code the warehouse associated with the location.
419 ploct_ctl The location control of the item.
420
421 RETURNS:
422 -62 Location is not valid.
423 -65 Warehouse is not valid.
424 -76 Cannot retrieve warehouse controls.
425 -77 System default LOCATION not found.
426 -78 Using default location ERROR.
427 -82 The item is not location controlled.
428 0 Success
429 ============================================= */
430 FUNCTION itm_location_val(plocation VARCHAR2,
431 pwhse_code VARCHAR2,
432 ploct_ctl NUMBER)
433 RETURN NUMBER IS
434
435 /* Local Variable declaration:
436 ===========================*/
437 --l_loct_desc ic_loct_mst.loct_desc%TYPE; -- Bug 3585309
438 l_location ic_loct_mst.location%TYPE; -- Bug 3585309
439 l_default_loct ic_loct_mst.location%TYPE;
440 l_whse_loct_ctl ic_whse_mst.loct_ctl%TYPE;
441 l_whse ic_whse_mst.whse_code%TYPE;
442
443 /* Cursor Definitions
444 ==================*/
445 CURSOR validate_whse IS
446 SELECT whse_code
447 FROM ic_whse_mst
448 WHERE whse_code = UPPER(pwhse_code)
449 AND delete_mark = 0;
450
451 CURSOR validate_location IS
452 SELECT location
453 FROM ic_loct_mst
454 WHERE location = UPPER(plocation)
455 AND whse_code = UPPER(pwhse_code)
456 AND delete_mark = 0;
457
458 CURSOR get_whse_ctl IS
459 SELECT loct_ctl
460 FROM ic_whse_mst
461 WHERE whse_code = UPPER(pwhse_code);
462
463 BEGIN
464
465
466 /* ==================================
467 Initialize local Variables
468 ==================================*/
469 --l_loct_desc := NULL; -- Bug 3585309
470 l_location := NULL;
471 l_default_loct := NULL;
472 l_whse := NULL;
473 l_whse_loct_ctl := 0;
474
475 /* Is the item location controlled?
476 If not this is an error.
477 =============================================*/
478 IF(ploct_ctl = 0) THEN
479 RETURN VAL_NOTLOCATION_CTL_ERR;
480 END IF;
481
482 /* ========================================
483 OK .. First determine if the warehouse
484 code passed is valid.
485 ========================================== */
486 OPEN validate_whse;
487 FETCH validate_whse INTO
488 l_whse;
489 IF(validate_whse%NOTFOUND) THEN
490
491 CLOSE validate_whse;
492 RETURN VAL_WHSE_ERR;
493
494 END IF;
495 CLOSE validate_whse;
496
497 /* ========================================
498 OK .. Second determine the type of location
499 control we have for the warehouse.
500 ========================================== */
501 OPEN get_whse_ctl;
502 FETCH get_whse_ctl INTO
503 l_whse_loct_ctl;
504 IF(get_whse_ctl%NOTFOUND) THEN
505
506 CLOSE get_whse_ctl;
507 RETURN VAL_CONTROLS_ERR;
508
509 END IF;
510 CLOSE get_whse_ctl;
511
512 /* OK .. first let's get the system default
513 location BUSINESS RULE. NO LOCATION BEING
514 VALIDATED MAY HAVE THE SAME LOCATION AS
515 THE SYSTEM DEFAULT LOCATION.
516 ========================================== */
517 l_default_loct := FND_PROFILE.value('IC$DEFAULT_LOCT');
518
519 IF(l_default_loct IS NULL) THEN
520
521 RETURN VAL_DEFAULT_LOCT_ERR;
522 END IF;
523
524 IF((ploct_ctl = 1 AND l_whse_loct_ctl = 2) OR
525 (l_whse_loct_ctl > 1)) THEN
526 /* ========================================
527 NON Validated Location
528 Let it go.
529 ========================================*/
530
531 IF(plocation = l_default_loct) THEN
532 RETURN VAL_USING_DEFAULT_ERR;
533 END IF;
534 RETURN 0;
535 END IF;
536
537 /* If the location passed is the same
538 as the default location, do not bother
539 validating cause this is an error!
540 ====================================== */
541 IF(plocation = l_default_loct) THEN
542
543 RETURN VAL_USING_DEFAULT_ERR;
544 END IF;
545
546 /* Validate please!
547 ================ */
548 OPEN validate_location;
549 FETCH validate_location INTO
550 l_location;
551
552 IF(validate_location%NOTFOUND) THEN
553
554 CLOSE validate_location;
555 RETURN VAL_LOCATION_ERR;
556
557 END IF;
558 CLOSE validate_location;
559
560 -- Bug 3585309
561 -- Commented the following as desc is a nullable field.
562 --IF(l_loct_desc IS NULL) THEN
563 -- RETURN VAL_LOCATION_ERR;
564 --END IF;
565
566 RETURN 0;
567
568
569 EXCEPTION
570 WHEN OTHERS THEN
571
572 RETURN SQLCODE;
573 END itm_location_val;
574 /* =============================================
575 FUNCTION:
576 whse_location_val
577
578 DESCRIPTION:
579 This PL/SQL function is responsible for
580 validating an location in a particular warehouse.
581
582 SYNOPSIS:
583 iret := GMICVAL.whse_location_val(porgn_code, pwhse_code,
584 plocation);
585
586 porgn_code the organization associated to the whse.
587 pwhse_code the warehouse associated with the location.
588 plocation The location in the warehouse.
589
590 RETURNS:
591 0 Success
592 -62 Location is not valid.
593 -65 Warehouse is not valid.
594 -77 System default LOCATION not found.
595 -78 Using default location ERROR.
596 ============================================= */
597 FUNCTION whse_location_val(porgn_code VARCHAR2,
598 pwhse_code VARCHAR2,
599 plocation VARCHAR2)
600 RETURN NUMBER IS
601
602 /* Local Variable declaration:
603 =========================== */
604 --l_loct_desc ic_loct_mst.loct_desc%TYPE; -- Bug 3585309
605 l_location ic_loct_mst.location%TYPE; -- Bug 3585309
606 l_default_loct ic_loct_mst.location%TYPE;
607 l_whse_loct_ctl ic_whse_mst.loct_ctl%TYPE;
608 l_whse ic_whse_mst.whse_code%TYPE;
609
610 /* Cursor Definitions
611 ================== */
612 CURSOR validate_whse IS
613 SELECT whse_code
614 FROM ic_whse_mst
615 WHERE whse_code = UPPER(pwhse_code)
616 AND orgn_code = UPPER(porgn_code)
617 AND delete_mark = 0;
618
619 CURSOR validate_location IS
620 SELECT location
621 FROM ic_loct_mst
622 WHERE location = UPPER(plocation)
623 AND whse_code = UPPER(pwhse_code)
624 AND delete_mark = 0;
625
626 BEGIN
627
628
629 /* ==================================
630 Initialize local Variables
631 ================================== */
632 --l_loct_desc := NULL; -- Bug 3585309
633 l_location := NULL;
634 l_default_loct := NULL;
635 l_whse := NULL;
636 l_whse_loct_ctl := 0;
637
638 /* ========================================
639 OK .. First determine if the warehouse
640 code passed is valid.
641 ========================================== */
642 OPEN validate_whse;
643 FETCH validate_whse INTO
644 l_whse;
645 IF(validate_whse%NOTFOUND) THEN
646
647 CLOSE validate_whse;
648 RETURN VAL_WHSE_ERR;
649
650 END IF;
651 CLOSE validate_whse;
652
653 /* OK .. first let's get the system default
654 location BUSINESS RULE. NO LOCATION BEING
655 VALIDATED MAY HAVE THE SAME LOCATION AS
656 THE SYSTEM DEFAULT LOCATION.
657 ========================================== */
658 l_default_loct := FND_PROFILE.value('IC$DEFAULT_LOCT');
659
660 IF(l_default_loct IS NULL) THEN
661
662 RETURN VAL_DEFAULT_LOCT_ERR;
663 END IF;
664
665 IF(plocation = l_default_loct) THEN
666
667 RETURN VAL_USING_DEFAULT_ERR;
668 END IF;
669
670 OPEN validate_location;
671 FETCH validate_location INTO
672 l_location;
673
674 IF(validate_location%NOTFOUND) THEN
675
676 CLOSE validate_location;
677 RETURN VAL_LOCATION_ERR;
678
679 END IF;
680 CLOSE validate_location;
681
682 -- Bug 3585309
683 -- Commented the following as desc is a nullable field.
684 --IF(l_loct_desc IS NULL) THEN
685 -- RETURN VAL_LOCATION_ERR;
686 --END IF;
687
688 RETURN 0;
689
690
691 EXCEPTION
692 WHEN OTHERS THEN
693
694 RETURN SQLCODE;
695 END whse_location_val;
696 /* =============================================
697 FUNCTION:
698 grade_val
699
700 DESCRIPTION:
701 This PL/SQL function is responsible for
702 validating an item's QC grade.
703
704 SYNOPSIS:
705 iret := GMICVAL.grade_val(pqc_grade);
706
707 pqc_grade the QC grade of an item.
708
709 RETURNS:
710 -63 QC Grade is not valid.
711 0 Success
712 ============================================= */
713 FUNCTION grade_val(pqc_grade VARCHAR2)
714 RETURN NUMBER IS
715
716 /* Cursor Definitions
717 ================== */
718 CURSOR validate_grade IS
719 SELECT qc_grade_desc
720 FROM qc_grad_mst
721 WHERE qc_grade = UPPER(pqc_grade)
722 AND delete_mark = 0;
723
724 /* Local Variable Declaration:
725 =========================== */
726 l_qc_grade_desc qc_grad_mst.qc_grade_desc%TYPE;
727
728
729 BEGIN
730
731
732 /* Initialize Local Variables
733 ========================== */
734
735 l_qc_grade_desc := NULL;
736
737
738 OPEN validate_grade;
739 FETCH validate_grade INTO
740 l_qc_grade_desc;
741
742 IF(validate_grade%NOTFOUND) THEN
743
744 CLOSE validate_grade;
745 RETURN VAL_GRADE_ERR;
746
747 END IF;
748 CLOSE validate_grade;
749 RETURN 0;
750
751
752 EXCEPTION
753 WHEN OTHERS THEN
754
755 RETURN SQLCODE;
756 END grade_val;
757 /* =============================================
758 FUNCTION:
759 lot_status_val
760
761 DESCRIPTION:
762 This PL/SQL function is responsible for
763 validating the lot status of a lot controlled
764 and lot status controlled item.
765
766 SYNOPSIS:
767 iret := GMICVAL.lot_status_val(plot_status);
768
769 pclot_status the Lot Status of the item.
770
771 RETURNS:
772 -64 Lot Status is not valid.
773 0 Success
774 ============================================= */
775 FUNCTION lot_status_val(plot_status VARCHAR2)
776 RETURN NUMBER IS
777
778 /* Cursor Definitions
779 ================== */
780 CURSOR validate_status IS
781 SELECT status_desc
782 FROM ic_lots_sts
783 WHERE lot_status = UPPER(plot_status)
784 AND delete_mark = 0;
785
786 /* Local Variable Declaration:
787 =========================== */
788 l_status_desc ic_lots_sts.status_desc%TYPE;
789
790
791 BEGIN
792
793
794 /* ==================================
795 Local Variable Initialization
796 ============================= */
797 l_status_desc := NULL;
798
799
800 OPEN validate_status;
801 FETCH validate_status INTO
802 l_status_desc;
803
804 IF(validate_status%NOTFOUND) THEN
805
806 CLOSE validate_status;
807 RETURN VAL_LOTSTATUS_ERR;
808
809 END IF;
810 CLOSE validate_status;
811 RETURN 0;
812
813
814 EXCEPTION
815 WHEN OTHERS THEN
816
817 RETURN SQLCODE;
818 END lot_status_val;
819 /* =============================================
820 FUNCTION:
821 whse_val
822
823 DESCRIPTION:
824 This PL/SQL function is responsible for
825 validating valid warehouse codes.
826
827 SYNOPSIS:
828 iret := GMICVAL.whse_val(pwhse_code, porgn_code);
829
830 pwhse_code the warehouse code you are working on.
831 porgn_code The organization associated with the
832 warehouse being validated.
833
834 RETURNS:
835 -65 Reason code not valid.
836 0 Success
837 ============================================= */
838 FUNCTION whse_val(pwhse_code VARCHAR2, porgn_code VARCHAR2)
839 RETURN NUMBER IS
840
841 /* Cursor Definitions
842 ================== */
843 CURSOR validate_whse IS
844 SELECT whse_code
845 FROM ic_whse_mst
846 WHERE whse_code = UPPER(pwhse_code)
847 AND orgn_code = UPPER(porgn_code)
848 AND delete_mark = 0;
849
850 /* Local variables.
851 ================ */
852 l_whse_code whse_type;
853
854 BEGIN
855
856
857 /* ==================================
858
859 Initialize Variables
860 ==================== */
861 l_whse_code := NULL;
862
863
864 OPEN validate_whse;
865 FETCH validate_whse INTO
866 l_whse_code;
867
868 IF(validate_whse%NOTFOUND) THEN
869
870 CLOSE validate_whse;
871 RETURN VAL_WHSE_ERR;
872
873 END IF;
874 CLOSE validate_whse;
875
876 IF(l_whse_code IS NULL) THEN
877 RETURN VAL_WHSE_ERR;
878 END IF;
879
880 RETURN 0;
881
882
883 EXCEPTION
884 WHEN OTHERS THEN
885
886 RETURN SQLCODE;
887 END whse_val;
888 /* =============================================
889 FUNCTION:
890 lot_validate
891
892 DESCRIPTION:
893 This PL/SQL function is responsible for
894 validating valid lot surrogates, lot numbers,
895 sulot numbers or combinations.
896
897 SYNOPSIS:
898 iret := GMICVAL.lot_validate(pitem_no, plot_no, psublot_no);
899
900 pitem_no the item number are working on.
901 plot_no the lot number you want to validate.
902 psublot_no the sublot number you want to validate.
903
904 RETURNS:
905 -66 Reason code not valid.
906 0 Success
907 ============================================= */
908 FUNCTION lot_validate(pitem_no VARCHAR2, plot_no VARCHAR2,
909 psublot_no VARCHAR2)
910 RETURN NUMBER IS
911
912 /* Local variables.
913 ================ */
914 l_item_id ic_item_mst.item_id%TYPE;
915 l_lot_ctl ic_item_mst.lot_ctl%TYPE;
916 l_sublot_ctl ic_item_mst.sublot_ctl%TYPE;
917 l_lot_id ic_lots_mst.lot_id%TYPE;
918
919
920 CURSOR get_item_attributes IS
921 SELECT item_id, lot_ctl, sublot_ctl
922 FROM ic_item_mst
923 WHERE item_no = UPPER(pitem_no)
924 AND delete_mark = 0;
925
926 CURSOR validate_lot IS
927 SELECT lot_id
928 FROM ic_lots_mst
929 where item_id = l_item_id
930 AND lot_no = UPPER(plot_no)
931 AND delete_mark = 0;
932
933 CURSOR validate_sublot IS
934 SELECT lot_id
935 FROM ic_lots_mst
936 WHERE item_id = l_item_id
937 AND lot_no = UPPER(plot_no)
938 AND sublot_no = UPPER(psublot_no)
939 AND delete_mark = 0;
940
941
942 BEGIN
943
944
945 /* ==================================
946
947 Initialize Variables
948 ==================== */
949 l_item_id := 0;
950 l_lot_id := 0;
951 l_lot_ctl := 0;
952 l_sublot_ctl := 0;
953
954
955 OPEN get_item_attributes;
956 FETCH get_item_attributes INTO
957 l_item_id, l_lot_ctl, l_sublot_ctl;
958
959 IF(get_item_attributes%NOTFOUND) THEN
960
961 CLOSE get_item_attributes;
962 RETURN VAL_ITEMATTR_ERR;
963
964 END IF;
965 CLOSE get_item_attributes;
966
967 IF(l_lot_ctl = 0) THEN
968 RETURN VAL_NOTLOT_CTL_ERR;
969 END IF;
970
971 IF(l_lot_ctl = 1 AND plot_no IS NULL) THEN
972 RETURN VAL_LOT_PARM_ERR;
973 END IF;
974
975 IF(l_lot_ctl = 1 AND l_sublot_ctl = 0) THEN
976 OPEN validate_lot;
977 FETCH validate_lot INTO
978 l_lot_id;
979
980 IF(validate_lot%NOTFOUND) THEN
981
982 CLOSE validate_lot;
983 RETURN VAL_LOT_ERR;
984 END IF;
985
986 CLOSE validate_lot;
987 RETURN 0;
988
989 ELSIF(l_lot_ctl = 1 AND l_sublot_ctl = 1) THEN
990 /* =========================================
991 BUSINESS RULE:
992 If a sublot controlled item is passed a
993 NULL sublot, only validate the lot number
994 ========================================= */
995 IF(psublot_no IS NULL OR psublot_no = ' ') THEN
996 OPEN validate_lot;
997 FETCH validate_lot INTO
998 l_lot_id;
999
1000 IF(validate_lot%NOTFOUND) THEN
1001
1002 CLOSE validate_lot;
1003 RETURN VAL_LOT_ERR;
1004 END IF;
1005
1006 CLOSE validate_lot;
1007 RETURN 0;
1008 ELSE
1009 /* =======================================
1010 Perform normal sublot validation.
1011 ======================================= */
1012 OPEN validate_sublot;
1013 FETCH validate_sublot INTO
1014 l_lot_id;
1015
1016 IF(validate_sublot%NOTFOUND) THEN
1017
1018 CLOSE validate_sublot;
1019 RETURN VAL_SUBLOT_ERR;
1020 END IF;
1021
1022 CLOSE validate_sublot;
1023 RETURN 0;
1024 END IF;
1025 END IF;
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029
1030 RETURN SQLCODE;
1031 END lot_validate;
1032 /* =============================================
1033 FUNCTION:
1034 lot_validate
1035
1036 DESCRIPTION:
1037 This PL/SQL function is responsible for
1038 validating valid lot surrogates, lot numbers,
1039 sulot numbers or combinations.
1040
1041 SYNOPSIS:
1042 iret := GMICVAL.lot_validate(pitem_no, plot_no, psublot_no);
1043
1044 pitem_no the item number are working on.
1045 plot_no the lot number you want to validate.
1046 psublot_no the sublot number you want to validate.
1047
1048 RETURNS:
1049 -66 Reason code not valid.
1050 0 Success
1051 ============================================= */
1052 FUNCTION lot_validate(pitem_id NUMBER,
1053 plot_id NUMBER)
1054 RETURN NUMBER IS
1055
1056 /* Local variables.
1057 ================ */
1058 l_item_id ic_item_mst.item_id%TYPE;
1059 l_lot_ctl ic_item_mst.lot_ctl%TYPE;
1060 l_sublot_ctl ic_item_mst.sublot_ctl%TYPE;
1061 l_lot_id ic_lots_mst.lot_id%TYPE;
1062 /* Cursor Definitions
1063
1064
1065 ================== */
1066 CURSOR get_item_attributes IS
1067 SELECT item_id, lot_ctl, sublot_ctl
1068 FROM ic_item_mst
1069 WHERE item_no = UPPER(pitem_id)
1070 AND delete_mark = 0;
1071
1072 CURSOR validate_lot IS
1073 SELECT lot_id
1074 FROM ic_lots_mst
1075 where item_id = pitem_id
1076 AND lot_id = plot_id
1077 AND delete_mark = 0;
1078
1079 BEGIN
1080
1081
1082 /* ==================================
1083
1084 Initialize Variables
1085 ==================== */
1086 l_item_id := 0;
1087 l_lot_id := 0;
1088 l_lot_ctl := 0;
1089 l_sublot_ctl := 0;
1090
1091
1092 OPEN get_item_attributes;
1093 FETCH get_item_attributes INTO
1094 l_item_id, l_lot_ctl, l_sublot_ctl;
1095
1096 IF(get_item_attributes%NOTFOUND) THEN
1097
1098 CLOSE get_item_attributes;
1099 RETURN VAL_ITEMATTR_ERR;
1100
1101 END IF;
1102 CLOSE get_item_attributes;
1103
1104 IF(l_lot_ctl = 0) THEN
1105 RETURN VAL_NOTLOT_CTL_ERR;
1106 END IF;
1107
1108
1109 IF(l_lot_ctl = 1) THEN
1110 OPEN validate_lot;
1111 FETCH validate_lot INTO
1112 l_lot_id;
1113
1114 IF(validate_lot%NOTFOUND) THEN
1115
1116 CLOSE validate_lot;
1117 RETURN VAL_LOT_ERR;
1118 END IF;
1119 END IF;
1120 CLOSE validate_lot;
1121 RETURN 0;
1122
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125
1126 RETURN SQLCODE;
1127 END lot_validate;
1128 /* =============================================
1129 FUNCTION:
1130 co_code_val
1131
1132 DESCRIPTION:
1133 This PL/SQL function is responsible for
1134 validating that an organization passed is
1135 a company.
1136
1137 SYNOPSIS:
1138 iret := GMICVAL.co_code_val(porgn_code);
1139
1140 porgn_code the organization code you are working on.
1141
1142 RETURNS:
1143 <-79 RDBMS Oracle Error.
1144 -79 The orgnization code passed is not valid.
1145 0 Success
1146 ============================================= */
1147 FUNCTION co_code_val(porgn_code VARCHAR2)
1148 RETURN NUMBER IS
1149
1150 /* Cursor Definitions
1151 ================== */
1152 CURSOR validate_orgn IS
1153 SELECT orgn_name
1154 FROM sy_orgn_mst
1155 WHERE co_code = UPPER(porgn_code)
1156 AND orgn_code = UPPER(porgn_code)
1157 AND delete_mark = 0;
1158
1159 /* Local variables.
1160 ================ */
1161 l_orgn_name sy_orgn_mst.orgn_name%TYPE;
1162
1163 BEGIN
1164
1165
1166 /* ==================================
1167
1168 Initialize Variables
1169 ==================== */
1170 l_orgn_name := NULL;
1171
1172
1173 OPEN validate_orgn;
1174 FETCH validate_orgn INTO
1175 l_orgn_name;
1176
1177 IF(validate_orgn%NOTFOUND) THEN
1178
1179 CLOSE validate_orgn;
1180 RETURN VAL_CO_CODE_ERR;
1181
1182 END IF;
1183 CLOSE validate_orgn;
1184
1185 IF(l_orgn_name IS NULL) THEN
1186 RETURN VAL_CO_CODE_ERR;
1187 END IF;
1188
1189 RETURN 0;
1190
1191
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194
1195 RETURN SQLCODE;
1196 END co_code_val;
1197 /* =============================================
1198 FUNCTION:
1199 orgn_code_val
1200
1201 DESCRIPTION:
1202 This PL/SQL function is responsible for
1203 validating organizations.
1204
1205 SYNOPSIS:
1206 iret := GMICVAL.orgn_code_val(porgn_code);
1207
1208 porgn_code the orgn_code you are working on.
1209
1210 RETURNS:
1211 <-80 RDBMS Oracle Error.
1212 -80 The orgn_code passed is not valid.
1213 0 Success
1214 ============================================= */
1215 FUNCTION orgn_code_val(porgn_code VARCHAR2)
1216 RETURN NUMBER IS
1217
1218 /* Cursor Definitions
1219 ================== */
1220 CURSOR validate_orgn IS
1221 SELECT orgn_name
1222 FROM sy_orgn_mst
1223 WHERE orgn_code = UPPER(porgn_code)
1224 AND delete_mark = 0;
1225
1226 /* Local variables.
1227 ================ */
1228 l_orgn_name sy_orgn_mst.orgn_name%TYPE;
1229
1230 BEGIN
1231
1232
1233 /* ==================================
1234
1235 Initialize Variables
1236 ==================== */
1237 l_orgn_name := NULL;
1238
1239
1240 OPEN validate_orgn;
1241 FETCH validate_orgn INTO
1242 l_orgn_name;
1243
1244 IF(validate_orgn%NOTFOUND) THEN
1245
1246 CLOSE validate_orgn;
1247 RETURN VAL_ORGN_CODE_ERR;
1248
1249 END IF;
1250 CLOSE validate_orgn;
1251
1252 IF(l_orgn_name IS NULL) THEN
1253 RETURN VAL_ORGN_CODE_ERR;
1254 END IF;
1255
1256 RETURN 0;
1257
1258
1259 EXCEPTION
1260 WHEN OTHERS THEN
1261
1262 RETURN SQLCODE;
1263 END orgn_code_val;
1264 /* =============================================
1265 FUNCTION:
1266 uomcode_val
1267
1268 DESCRIPTION:
1269 This PL/SQL function is responsible for
1270 validating a unit of measure code (ie. LBS).
1271
1272 SYNOPSIS:
1273 iret := GMICVAL.uomcode_val(puom_code);
1274
1275 puom_code the uom code you are working on.
1276
1277 RETURNS:
1278 <-81 RDBMS Oracle Error.
1279 -81 The UOM CODE passed is not valid.
1280 0 Success
1281 ============================================= */
1282 FUNCTION uomcode_val(puom_code VARCHAR2)
1283 RETURN NUMBER IS
1284
1285 /* Cursor Definitions
1286 ================== */
1287 CURSOR validate_uomcode IS
1288 SELECT um_desc
1289 FROM sy_uoms_mst
1290 WHERE um_code = puom_code
1291 AND delete_mark = 0;
1292
1293 /* Local variables.
1294 ================ */
1295 l_um_desc sy_uoms_mst.um_desc%TYPE;
1296
1297 BEGIN
1298
1299
1300 /* ==================================
1301
1302 Initialize Variables
1303 ==================== */
1304 l_um_desc := NULL;
1305
1306
1307 OPEN validate_uomcode;
1308 FETCH validate_uomcode INTO
1309 l_um_desc;
1310
1311 IF(validate_uomcode%NOTFOUND) THEN
1312
1313 CLOSE validate_uomcode;
1314 RETURN VAL_UOMCODE_ERR;
1315
1316 END IF;
1317 CLOSE validate_uomcode;
1318
1319 IF(l_um_desc IS NULL) THEN
1320 RETURN VAL_UOMCODE_ERR;
1321 END IF;
1322
1323 RETURN 0;
1324
1325
1326 EXCEPTION
1327 WHEN OTHERS THEN
1328
1329 RETURN SQLCODE;
1330 END uomcode_val;
1331 /* =============================================
1332 FUNCTION:
1333 dev_validation
1334
1335 DESCRIPTION:
1336 This PL/SQL function is responsible for
1337 dualum indicator type 2 and 3 ONLY!
1338
1339 The purpose of the function is to validate
1340 that the secondary quantity is within the
1341 standard deviation high and low for an item
1342 as defined by the item in the ic_item_mst table.
1343
1344 PARAMETERS:
1345 pitem_id The surrogate key of the item number
1346
1347 plot_id The surrogate key for the lot number/
1348 sublot of the item number being converted.
1349
1350 ptrans_qty1 The quantity in the primary UOM for the
1351 item.
1352
1353 pprim_uom The UOM of trans_qty1.
1354
1355 ptrans_qty2 The quantity in the secondary UOM for the
1356 item.
1357 psec_uom The UOM of trans_qty2.
1358
1359 patomic Flag to signify if integer or full
1360 conversion with percision should be performed.
1361 0 - Full percision
1362 1 - Integer conversion.
1363
1364 SPECIAL NOTES:
1365
1366 RETURNS:
1367 0 - SUCCESS
1368 -1 - Package problem.
1369 -2 - Lab Type not passed for LAB conversion.
1370 -3 - UM_TYPE and conversion factor for current UOM not found.
1371 -4 - UM_TYPE and conversion factor for NEW UOM not found.
1372 -5 - Cannot determine INVENTORY UOM for item.
1373 -6 - UM_TYPE and conversion factor for INV UOM not found.
1374 -7 - Cannot find conversion factor for CURRENT UOM.
1375 -8 - LAB CONVERSION - LM$DENSITY variable not found.
1376 -9 - LAB CONVERSION - conversion factor not found.
1377 -10 - Cannot find conversion factor for NEW UOM.
1378 -11 - Package/Security Issue to gmicitm Package.
1379 -12 - Package/Security Issue to gmicuom Package.
1380 -13 - Quantity is GREATER than allowed deviation.
1381 -14 - Quantity is LESS THAN allowed deviation.
1382
1383 HISTORY:
1384 WJ Harris III 26-JUN-98 rel 4.01.06
1385 Changes to function dev_validation().
1386 Added parameters pprim_uom and psec_uom.
1387 Removed all references to OUT type parameters as
1388 well as rewriting functionality.
1389 ============================================= */
1390 FUNCTION dev_validation(pitem_id NUMBER,
1391 plot_id NUMBER,
1392 ptrans_qty1 NUMBER,
1393 pprim_uom VARCHAR2,
1394 ptrans_qty2 NUMBER,
1395 psec_uom VARCHAR2,
1396 patomic NUMBER)
1397 RETURN NUMBER IS
1398
1399 /* Variable Declarations
1400 ===================== */
1401 l_converted_qty quantity_type;
1402 l_iret NUMBER;
1403 BEGIN
1404
1405 /* ===============================
1406 Initialize Variables
1407 =============================== */
1408 l_converted_qty := 0;
1409 l_iret := -1;
1410
1411
1412 /* ============================================
1413 OK .... first let's go out and grab the
1414 required attributes we need for the item.
1415 ============================================ */
1416 l_iret := GMICVAL.det_dualum_ind(pitem_id);
1417
1418 IF(l_iret < -1) THEN
1419 RETURN l_iret;
1420 ELSIF(l_iret = -1) THEN
1421 RETURN VAL_PACKAGE_ERR;
1422 END IF;
1423
1424 /* =================================================
1425 If the item is either NOT DUAL controlled or DUAL
1426 Controlled type one .... we have nothing to do!.
1427 ================================================= */
1428 IF(l_iret = 0 OR l_iret = 1) THEN
1429 RETURN 0;
1430 END IF;
1431
1432 /* ==================================================
1433 Next .... let's perform a unit of measure
1434 conversion to determine the base converted amount.
1435 ================================================== */
1436 l_iret := -1;
1437 l_iret := GMICUOM.uom_conversion(pitem_id, plot_id,
1438 ptrans_qty1, pprim_uom, psec_uom, patomic);
1439
1440 IF(l_iret < -1) THEN
1441 RETURN l_iret;
1442 ELSIF(l_iret = -1) THEN
1443 RETURN VAL_PACKAGE_ERR;
1444 END IF;
1445
1446 /* ======================================================
1447 If the passed in secondary quantity is greater than
1448 the deviation high boundary OR less than the deviation
1449 low boundary, we have an error!
1450 ====================================================== */
1451 l_converted_qty := l_iret;
1452 l_iret := -1;
1453 l_iret := GMICVAL.calc_deviation(pitem_id, ptrans_qty2,
1454 l_converted_qty);
1455
1456 IF(l_iret = VAL_CALCDEV_HIGH_ERR) THEN
1457 RETURN l_iret;
1458 ELSIF(l_iret = VAL_CALCDEV_LO_ERR) THEN
1459 RETURN l_iret;
1460 ELSIF(l_iret = -1) THEN
1461 RETURN VAL_PACKAGE_ERR;
1462 END IF;
1463
1464 RETURN 0;
1465
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468
1469 RETURN SQLCODE;
1470 END dev_validation;
1471 /* =============================================
1472 FUNCTION:
1473 det_dualum_ind
1474
1475 DESCRIPTION:
1476 This PL/SQL function is responsible for
1477 returning the dual unit of measure indicator
1478 of an item.
1479
1480 SYNOPSIS:
1481 iret := GMICVAL.det_dualum_ind(pitem_id);
1482
1483 pitem_id the item surrogate you are working on.
1484 iodualum_ind The dual UOM indicator of the item.
1485 Valid values are:
1486 o 0 - Denotes single UOM controlled.
1487 o 1 - Denotes Dual UOM auto calculated.
1488 o 2 - Denotes Dual UOM auto calculated.
1489 o 3 - Denotes Dual UOM entry required.
1490
1491 SPECIAL NOTE:
1492 DUALUM 2 is auto calculated by the system HOWEVER allows
1493 the user to modify the quantity which is then compared to a
1494 high/low deviation. If the quantity entered by the
1495 user is outside these boundaries, this is an error.
1496
1497 DUALUM 3 requires entry from the user. NO CALCULATION IS
1498 DONE BY THE SYSTEM. The quantity entered is then
1499 validated the same as DUALUM 2.
1500
1501 RETURNS:
1502 -67 Whse code not passed.
1503 >0 Success
1504 ============================================= */
1505 FUNCTION det_dualum_ind(pitem_id NUMBER)
1506 RETURN NUMBER IS
1507
1508 /* Local variable definitions
1509 ========================== */
1510 l_dualum_ind dualum_type;
1511
1512 /* Cursor Definitions
1513 ================== */
1514 CURSOR item_uom_attr IS
1515 SELECT dualum_ind
1516 FROM ic_item_mst
1517 WHERE item_id = pitem_id
1518 AND delete_mark = 0;
1519
1520 BEGIN
1521
1522
1523 OPEN item_uom_attr;
1524 FETCH item_uom_attr INTO
1525 l_dualum_ind;
1526
1527 IF(item_uom_attr%NOTFOUND) THEN
1528
1529 CLOSE item_uom_attr;
1530 RETURN VAL_DUALUM_ERR;
1531
1532 END IF;
1533 CLOSE item_uom_attr;
1534
1535 RETURN l_dualum_ind;
1536
1537
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540
1541 RETURN SQLCODE;
1542 END det_dualum_ind;
1543 /* =============================================
1544 FUNCTION:
1545 calc_deviation
1546
1547 DESCRIPTION:
1548 This PL/SQL function is responsible for
1549 determining whether an entered quantity
1550 is within the allowable deviation + or -
1551 of an item.
1552
1553 SYNOPSIS:
1554 iret := GMICVAL.calc_deviation(pitem_id, ptrans_qty2,
1555 pconverted_qty);
1556
1557 pitem_id the item surrogate you are working on.
1558 ptrans_qty2 the secondary quantity entered by the user.
1559 pconverted_qty the system calculated secondary quantity.
1560
1561 SPECIAL NOTE:
1562 l_dev_hi The returned high boundary for allowed
1563 deviation percentage expressed as a decimal.
1564 (ie 20% is represented as .20)
1565 l_dev_lo The returned low boundary for allowed
1566 deviation percentage expressed as a decimal.
1567 (ie 20% is represented as .20)
1568
1569 DUALUM 2 is auto calculated by the system HOWEVER allows
1570 the user to modify the quantity which is then compared to a
1571 high/low deviation. If the quantity entered by the
1572 user is outside these boundaries, this is an error.
1573
1574 DUALUM 3 requires entry from the user. NO CALCULATION IS
1575 DONE BY THE SYSTEM. The quantity entered is then
1576 validated the same as DUALUM 2.
1577
1578 RETURNS:
1579 -1 Whse code not passed.
1580 > 0 Success
1581 ============================================= */
1582 FUNCTION calc_deviation(pitem_id NUMBER,
1583 ptrans_qty2 NUMBER,
1584 pconverted_qty NUMBER)
1585 RETURN NUMBER IS
1586
1587 /* Local variable definitions
1588 ========================== */
1589 l_deviation_hi dev_type;
1590 l_deviation_lo dev_type;
1591 l_hi_boundary NUMBER;
1592 l_lo_boundary NUMBER;
1593 ltrans_qty2 NUMBER;
1594
1595
1596 /* Cursor Definitions
1597 ================== */
1598 CURSOR item_uom_attr IS
1599 SELECT deviation_hi, deviation_lo
1600 FROM ic_item_mst
1601 WHERE item_id = pitem_id
1602 AND delete_mark = 0;
1603
1604 BEGIN
1605
1606 /* Local Variable Initialization
1607 ============================= */
1608 l_deviation_hi := 0.0;
1609 l_deviation_lo := 0.0;
1610 l_hi_boundary := 0;
1611 l_lo_boundary := 0;
1612
1613
1614
1615 OPEN item_uom_attr;
1616 FETCH item_uom_attr INTO
1617 l_deviation_hi, l_deviation_lo;
1618
1619 IF(item_uom_attr%NOTFOUND) THEN
1620
1621 CLOSE item_uom_attr;
1622 RETURN VAL_UOMATTR_ERR;
1623
1624 END IF;
1625 CLOSE item_uom_attr;
1626
1627 /* ========================
1628 Set temporary boundaries
1629 ======================== */
1630 l_hi_boundary := (pconverted_qty * (1 + l_deviation_hi));
1631 l_lo_boundary := (pconverted_qty * (1 - l_deviation_lo));
1632
1633
1634 /* ======================================================
1635 If the passed in secondary quantity is greater than
1636 the deviation high boundary OR less than the deviation
1637 low boundary, we have an error!
1638 ====================================================== */
1639 /* =====================================================
1640 Deviation check should restrict only to 9 precision
1641 as round till 9 is the apps standard
1642 ====================================================*/
1643 ltrans_qty2 := round(ptrans_qty2,9);
1644 IF(ltrans_qty2 > round(l_hi_boundary,9)) THEN
1645 RETURN VAL_CALCDEV_HIGH_ERR;
1646 ELSIF(ltrans_qty2 < round(l_lo_boundary,9)) THEN
1647 RETURN VAL_CALCDEV_LO_ERR;
1648 END IF;
1649
1650 RETURN 0;
1651
1652 EXCEPTION
1653 WHEN OTHERS THEN
1654
1655 RETURN SQLCODE;
1656 END calc_deviation;
1657 END;
[Home] [Help]