1 PACKAGE BODY MRP_GET_BIS_VALUES AS
2 /* $Header: MRPBISUB.pls 120.1 2005/09/20 13:15:28 ichoudhu noship $ */
3
4 -- ==============================================================
5 -- Function to calculate the forecast error
6 -- ==============================================================
7 FUNCTION forecast_error (p_forecast_qty IN NUMBER,
8 p_order_qty IN NUMBER) RETURN NUMBER IS
9
10 BEGIN
11
12 IF (NVL(p_forecast_qty,0) = 0 AND NVL(p_order_qty,0) = 0) THEN
13 RETURN 0;
14 ELSIF NVL(p_forecast_qty,0) = 0 THEN
15 RETURN 100;
16 ELSIF NVL(p_order_qty,0) = 0 THEN
17 RETURN 100;
18 ELSE
19 RETURN ABS(((p_forecast_qty - p_order_qty)/p_forecast_qty)*100);
20 END IF;
21
22 END forecast_error;
23
24 -- ==============================================================
25 -- Function to determine the expected ship date of a late order
26 -- ==============================================================
27 FUNCTION expected_ship_date (p_demand_id IN NUMBER,
28 p_organization_id IN NUMBER,
29 p_compile_designator IN VARCHAR2)
30 RETURN NUMBER IS
31
32 l_days NUMBER;
33
34 CURSOR MAX_LATE_CURSOR IS
35 SELECT max(rec.new_schedule_date - peg2.demand_date)
36 FROM mrp_recommendations rec,
37 mrp_full_pegging peg1,
38 mrp_full_pegging peg2
39 WHERE peg1.demand_id = p_demand_id
40 AND peg1.transaction_id = rec.transaction_id
41 AND peg1.pegging_id = peg2.end_pegging_id
42 AND peg2.organization_id = p_organization_id
43 AND peg2.compile_designator = p_compile_designator
44 AND peg1.organization_id = p_organization_id
45 AND peg1.compile_designator = p_compile_designator;
46
47 BEGIN
48
49 OPEN MAX_LATE_CURSOR;
50 FETCH MAX_LATE_CURSOR INTO l_days;
51 CLOSE MAX_LATE_CURSOR;
52
53 RETURN l_days;
54
55 EXCEPTION
56
57 WHEN OTHERS THEN
58
59 RETURN 0;
60
61 END expected_ship_date;
62
63 END MRP_GET_BIS_VALUES;
[Home] [Help]