[Home] [Help]
PACKAGE BODY: APPS.EDW_MRP_FORECAST_M_SIZE
Source
1 PACKAGE BODY EDW_MRP_FORECAST_M_SIZE AS
2 /* $Header: ISCSGD1B.pls 115.2 2002/12/19 00:45:28 scheung ship $ */
3
4 /* ------------------------------------------
5 PROCEDURE NAME : cnt_rows
6 INPUT PARAMETERS : p_from_date, p_to_date
7 OUTPUT PARAMETERS: p_num_rows
8 DESCRIPTION : Count the number of rows
9 ------------------------------------------- */
10
11 PROCEDURE cnt_rows(p_from_date DATE,
12 p_to_date DATE,
13 p_num_rows OUT NOCOPY NUMBER) IS
14
15 BEGIN
16
17 SELECT count(*)
18 INTO p_num_rows
19 FROM mrp_forecast_designators fd,
20 mtl_parameters mp
21 WHERE fd.forecast_set IS NOT NULL
22 AND fd.organization_id = mp.organization_id
23 AND fd.last_update_date BETWEEN p_from_date AND p_to_date;
24
25 Exception When others then
26 rollback;
27
28 END;
29
30 /* ------------------------------------------
31 PROCEDURE NAME : est_row_len
32 INPUT PARAMETERS : p_from_date, p_to_date
33 OUTPUT PARAMETERS: p_avg_row_len
34 DESCRIPTION : Estimate input_f
35 ------------------------------------------ */
36
37 PROCEDURE est_row_len(p_from_date DATE,
38 p_to_date DATE,
39 p_avg_row_len OUT NOCOPY NUMBER) IS
40
41 x_total number := 0;
42 x_org_code number := 0;
43 x_forecast_designator number := 0;
44 x_org_id number := 0;
45 x_forecast_set number := 0;
46 x_FORECAST_PK NUMBER;
47 x_FORECAST_SET_FK NUMBER;
48 x_FORECAST_NAME NUMBER;
49 x_DESCRIPTION NUMBER;
50 x_FORECAST_DP NUMBER;
51 x_NAME NUMBER;
52 x_DISABLE_DATE NUMBER;
53 x_FORECAST_SET_PK NUMBER;
54 x_FORECAST_SET_NAME NUMBER;
55 x_CONSUMPTION_LEVEL NUMBER;
56 x_SET_DESCRIPTION NUMBER;
57 x_FORECAST_SET_DP NUMBER;
58 x_SET_NAME NUMBER;
59 x_SET_DISABLE_DATE NUMBER;
60
61 CURSOR c_1 IS
62 SELECT nvl(avg(nvl(vsize(organization_code),0)),0)
63 FROM mtl_parameters;
64
65 CURSOR c_2 IS
66 SELECT nvl(avg(nvl(vsize(forecast_designator),0)),0),
67 nvl(avg(nvl(vsize(organization_id),0)),0),
68 nvl(avg(nvl(vsize(forecast_set),0)),0),
69 nvl(avg(nvl(vsize(description),0)),0),
70 nvl(avg(nvl(vsize(disable_date),0)),0)
71 FROM mrp_forecast_designators
72 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
73
74 CURSOR c_3 IS
75 SELECT nvl(avg(nvl(vsize(meaning),0)),0)
76 FROM mfg_lookups;
77
78 BEGIN
79
80 OPEN c_1;
81 FETCH c_1 INTO x_org_code;
82 CLOSE c_1;
83
84 OPEN c_2;
85 FETCH c_2 INTO x_forecast_designator, x_org_id,
86 x_forecast_set, x_DESCRIPTION, x_DISABLE_DATE;
87 CLOSE c_2;
88
89 x_FORECAST_PK := x_forecast_designator + x_org_id;
90 x_FORECAST_SET_FK := x_forecast_set + x_org_id;
91 x_FORECAST_NAME := x_forecast_designator + x_org_code;
92 x_FORECAST_DP := x_FORECAST_NAME;
93 x_NAME := x_FORECAST_NAME;
94
95 x_FORECAST_SET_PK := x_FORECAST_PK;
96 x_FORECAST_SET_NAME := x_FORECAST_NAME;
97 x_FORECAST_SET_DP := x_FORECAST_NAME;
98
99 x_total := 3 + x_total + ceil(x_FORECAST_PK + 1) + ceil(x_FORECAST_SET_FK + 1) +
100 ceil(x_FORECAST_NAME + 1) + ceil(x_DESCRIPTION + 1) + ceil(x_FORECAST_DP + 1) +
101 ceil(x_NAME + 1) + ceil(x_DISABLE_DATE + 1) + ceil(x_FORECAST_SET_PK + 1) +
102 ceil(x_FORECAST_SET_NAME + 1) + ceil(x_DESCRIPTION + 1) +
103 ceil(x_FORECAST_SET_DP + 1) + ceil(x_NAME + 1) + ceil(x_DISABLE_DATE + 1);
104
105 OPEN c_3;
106 FETCH c_3 INTO x_CONSUMPTION_LEVEL;
107 CLOSE c_3;
108
109 x_total := x_total + ceil(x_CONSUMPTION_LEVEL + 1);
110
111 p_avg_row_len := x_total;
112
113 Exception When others then
114 rollback;
115
116 END;
117
118 END EDW_MRP_FORECAST_M_SIZE;