1 PACKAGE BODY gmd_scale AS
2 /* $Header: GMDSCALB.pls 120.1 2005/09/29 11:21:11 srsriran noship $ */
3
4 G_PKG_NAME VARCHAR2(32);
5
6 PROCEDURE scale
7 ( p_fm_matl_dtl_tab IN fm_matl_dtl_tab
8 , p_scale_factor IN NUMBER
9 , p_primaries IN VARCHAR2
10 , x_fm_matl_dtl_tab OUT NOCOPY fm_matl_dtl_tab
11 , x_return_status OUT NOCOPY VARCHAR2
12 )
13 IS
14 l_row_count NUMBER;
15 l_row_number NUMBER;
16 l_scale_tab scale_tab := scale_tab();
17 BEGIN
18 l_row_count := p_fm_matl_dtl_tab.count;
19 FOR l_row_number IN 1 .. l_row_count
20 LOOP
21 l_scale_tab.extend;
22 l_scale_tab(l_row_number).item_id := p_fm_matl_dtl_tab(l_row_number).item_id;
23 l_scale_tab(l_row_number).item_um := p_fm_matl_dtl_tab(l_row_number).item_um;
24 l_scale_tab(l_row_number).qty := p_fm_matl_dtl_tab(l_row_number).qty;
25 l_scale_tab(l_row_number).line_type := p_fm_matl_dtl_tab(l_row_number).line_type;
26 l_scale_tab(l_row_number).line_no := p_fm_matl_dtl_tab(l_row_number).line_no;
27 l_scale_tab(l_row_number).scale_type := p_fm_matl_dtl_tab(l_row_number).scale_type;
28 END LOOP;
29 scale( l_scale_tab
30 , p_scale_factor
31 , p_primaries
32 , l_scale_tab
33 , x_return_status
34 );
35 IF x_return_status = FND_API.G_RET_STS_SUCCESS
36 THEN
37 x_fm_matl_dtl_tab := p_fm_matl_dtl_tab;
38 FOR l_row_number in 1 .. l_row_count
39 LOOP
40 x_fm_matl_dtl_tab(l_row_number).qty := l_scale_tab(l_row_number).qty;
41 END LOOP;
42 END IF;
43 END scale;
44
45
46 PROCEDURE scale
47 ( p_scale_tab IN scale_tab
48 , p_scale_factor IN NUMBER
49 , p_primaries IN VARCHAR2
50 , x_scale_tab OUT NOCOPY scale_tab
51 , x_return_status OUT NOCOPY VARCHAR2
52 )
53 IS
54 l_scale_tab scale_tab := scale_tab() ;
55
56 l_qty NUMBER;
57 l_tab_size NUMBER;
58 l_conv_uom VARCHAR2(4);
59 l_type_0_outputs NUMBER:= 0;
60 l_type_1_outputs NUMBER:= 0;
61 l_type_2_outputs NUMBER:= 0;
62 l_type_3_outputs NUMBER:= 0;
63 l_type_0_inputs NUMBER:= 0;
64 l_type_1_inputs NUMBER:= 0;
65 l_type_2_inputs NUMBER:= 0;
66 l_type_3_inputs NUMBER:= 0;
67 l_fixed_input_qty NUMBER:= 0;
68 l_fixed_output_qty NUMBER:= 0;
69 l_proportional_input_qty NUMBER:= 0;
70 l_proportional_output_qty NUMBER:= 0;
71 P NUMBER:= 0;
72 S NUMBER:= 0;
73 A NUMBER:= 0;
74 k NUMBER:= 0;
75 b NUMBER:= 0;
76
77 BEGIN
78
79 -- Find the primary product. We will use its uom as the basis of the arithmetic
80 -- that follows. Whilst we are in the loop, accumulate a few totals which will
81 -- be handy later on.
82
83 l_tab_size := p_scale_tab.count;
84
85 FOR l_row_count IN 1 .. l_tab_size
86 LOOP
87 IF p_scale_tab(l_row_count).line_type = 1
88 AND p_scale_tab(l_row_count).line_no = 1
89 THEN
90 l_conv_uom := p_scale_tab(l_row_count).item_um;
91 END IF;
92
93 IF p_scale_tab(l_row_count).line_type IN (1,2)
94 THEN
95 IF p_scale_tab(l_row_count).scale_type = 0
96 THEN
97 l_type_0_outputs := l_type_0_outputs + 1;
98 ELSIF p_scale_tab(l_row_count).scale_type = 1
99 THEN
100 l_type_1_outputs := l_type_1_outputs+1;
101 ELSIF p_scale_tab(l_row_count).scale_type = 2
102 THEN
103 l_type_2_outputs := l_type_2_outputs+1;
104 ELSIF p_scale_tab(l_row_count).scale_type = 3
105 THEN
106 l_type_3_outputs := l_type_3_outputs+1;
107 ELSE
108 -- Use this branch for future scale types.
109 NULL;
110 END IF;
111 ELSIF p_scale_tab(l_row_count).line_type = -1
112 THEN
113 IF p_scale_tab(l_row_count).scale_type = 0
114 THEN
115 l_type_0_inputs := l_type_0_inputs + 1;
116 ELSIF p_scale_tab(l_row_count).scale_type = 1
117 THEN
118 l_type_1_inputs := l_type_1_inputs + 1;
119 ELSIF p_scale_tab(l_row_count).scale_type = 2
120 THEN
121 l_type_2_inputs := l_type_2_inputs+1;
122 ELSIF p_scale_tab(l_row_count).scale_type = 3
123 THEN
124 l_type_3_inputs := l_type_3_inputs+1;
125 ELSE
126 -- Use this branch for future scale types.
127 NULL;
128 END IF;
129 END IF;
130 END LOOP;
131
132 -- See if we can carry on, or if the scaling is trivial (eg everything is type 1)
133 -- then make an early exit after applying the factor appropriately
134
135 IF l_type_1_inputs + l_type_3_inputs + l_type_1_outputs + l_type_3_outputs = l_tab_size
136
137 THEN
141 x_scale_tab := p_scale_tab;
138 -- scaling is trivial, multiply all inputs and outputs by the scale factor
139 -- and exit
140 -- *********************************
142 FOR l_row_count IN 1 .. l_tab_size
143 LOOP
144 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * p_scale_factor;
145 END LOOP;
146
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148 RETURN;
149 -- *********************************
150
151 ELSIF l_type_0_inputs + l_type_2_inputs + l_type_0_outputs + l_type_2_outputs = l_tab_size
152 THEN
153 -- Scaling is not possible as no items are scalable.
154 x_scale_tab := p_scale_tab;
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156 RETURN;
157 END IF;
158
159 -- If we've reached here then the fun begins. First we must convert each
160 -- quantity to the primary product's unit of measure (determined above).
161
162 l_scale_tab := p_scale_tab;
163 x_scale_tab := p_scale_tab;
164
165 FOR l_row_count IN 1 .. l_tab_size
166 LOOP
167 IF l_scale_tab(l_row_count).scale_type IN (0,1)
168 THEN
169 l_qty := GMICUOM.uom_conversion
170 ( p_scale_tab(l_row_count).item_id
171 , 0
172 , p_scale_tab(l_row_count).qty
173 , p_scale_tab(l_row_count).item_um
174 , l_conv_uom
175 ,0
176 );
177 IF l_qty < 0
178 THEN
179 -- Report UOM failure
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 ELSE
182 l_scale_tab(l_row_count).qty := l_qty;
183 l_scale_tab(l_row_count).item_um := l_conv_uom;
184
185 IF l_scale_tab(l_row_count).line_type IN (1,2)
186 THEN
187 IF l_scale_tab(l_row_count).scale_type = 0
188 THEN
189 l_fixed_output_qty := l_fixed_output_qty + l_scale_tab(l_row_count).qty;
190 ELSIF l_scale_tab(l_row_count).scale_type = 1
191 THEN
192 l_proportional_output_qty := l_proportional_output_qty + l_scale_tab(l_row_count).qty;
193 END IF;
194 ELSE
195 IF l_scale_tab(l_row_count).scale_type = 0
196 THEN
197 l_fixed_input_qty := l_fixed_input_qty + l_scale_tab(l_row_count).qty;
198 ELSIF l_scale_tab(l_row_count).scale_type = 1
199 THEN
200 l_proportional_input_qty := l_proportional_input_qty + l_scale_tab(l_row_count).qty;
201
202 END IF;
203 END IF;
204 END IF;
205 END IF;
206 END LOOP;
207
208 -- So far, so good. With the above quantities we can scale the formula correctly.
209 -- If the parameter p_primaries indicates that we are scaling outputs then we apply the
210 -- given factor (p_scale_factor) to those outputs which can be scaled and apply the
211 -- calculated factor to the scalable inputs. If p_primaries indicates that we are scaling
212 -- inputs then we apply the given factor to the ingredients which can be scaled and apply the
213 -- calculated factor to the scalable outputs.
214
215 -- Calculate the factor to apply.
216 IF p_primaries = 'OUTPUTS'
217 THEN
218 P := l_fixed_output_qty + l_proportional_output_qty;
219 S := l_fixed_input_qty + l_proportional_input_qty;
220 k := P/S;
221 FOR l_row_count IN 1 .. l_tab_size
222 LOOP
223 IF l_scale_tab(l_row_count).line_type IN (1,2)
224 THEN
225 IF l_scale_tab(l_row_count).scale_type = 1
226 THEN
227 A := A + p_scale_factor * l_scale_tab(l_row_count).qty;
228 ELSIF l_scale_tab(l_row_count).scale_type = 0
229 THEN
230 A := A + l_scale_tab(l_row_count).qty;
231 END IF;
232 END IF;
233 END LOOP;
234
235 b := ((A/k) - (l_fixed_input_qty))/(l_proportional_input_qty);
236 FOR l_row_count IN 1 .. l_tab_size
237 LOOP
238 IF x_scale_tab(l_row_count).line_type IN (1,2)
239 THEN
240 IF x_scale_tab(l_row_count).scale_type IN (1,3)
241 THEN
242 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * p_scale_factor;
243 END IF;
244 ELSE
245 IF x_scale_tab(l_row_count).scale_type IN (1,3)
246 THEN
247 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * b;
248 END IF;
249 END IF;
250 END LOOP;
251 ELSIF p_primaries = 'INPUTS'
252 THEN
253 P := l_fixed_input_qty + l_proportional_input_qty;
254 S := l_fixed_output_qty + l_proportional_output_qty;
255 k := P/S;
256 FOR l_row_count IN 1 .. l_tab_size
257 LOOP
258 IF l_scale_tab(l_row_count).line_type = -1
259 THEN
260 IF l_scale_tab(l_row_count).scale_type = 1
261 THEN
262 A := A + p_scale_factor * l_scale_tab(l_row_count).qty;
263 ELSIF l_scale_tab(l_row_count).scale_type = 0
264 THEN
265 A := A + l_scale_tab(l_row_count).qty;
266 END IF;
267 END IF;
268 END LOOP;
269
270 b := ((A/k) - (l_fixed_output_qty))/(l_proportional_output_qty);
271
272 FOR l_row_count IN 1 .. l_tab_size
273 LOOP
274 IF x_scale_tab(l_row_count).line_type = -1
275 THEN
276 IF x_scale_tab(l_row_count).scale_type IN (1,3)
277 THEN
278 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * p_scale_factor;
279
280 END IF;
281 ELSE
282 IF x_scale_tab(l_row_count).scale_type IN (1,3)
283 THEN
284 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * b;
285 END IF;
289
286 END IF;
287 END LOOP;
288 END IF;
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 EXCEPTION
293 WHEN OTHERS
294 THEN x_return_status := FND_API.G_RET_STS_ERROR;
295 END scale;
296 END gmd_scale;
[Home] [Help]