1 PACKAGE BODY gmd_common_scale AS
2 /* $Header: GMDVSCLB.pls 120.2 2006/11/21 16:49:46 txdaniel noship $ */
3 /*****************************************************************************************************
4 * FILE: GMDVSCLB.pls
5 * PROCEDURE: scale
6 * PURPOSE: Package body for GMD Common Scale Routine.
7 * AUTHOR: Paul Schofield
8 *
9 * HISTORY:
10 * =======
11 * Paul Schofield Created.
12 *
13 * E. Chen 03/21/01 Modified for 11iplus enhancements. Added new
14 * integer_multiple scale_type, new yield
15 * contributing indicator, error conditions.
16 * Shrikant Nene 04/30/2001 Added Theoretical_yield procedure.
17 * Bill Stearns 06/3/2001 Added Header string
18 * LeAta + Susan 24Aug2001 Bug 1955038. Commented out dbms_output. D
19 * Bug 1954995. Changed != to <> Bug 1954988 Removed line between CREATE and $Header
20 * Pawan Kumar 10/02/2001 Added validations for null input for scale tab. moved fm_yield_type_um
21 * Praveen Surakanti 05/22/03 Bug#2901379
22 * Do not validate the scale_rounding_variance being NULL.
23 * Kalyani Manda 08/28/03 Bug 3069408 Commented the code to check the
24 * scale rounding variance in order to do the
25 * integer scaling.
26 * P.Raghu 10/09/03 Bug#3164299
27 * Declared a new local variable x_scale_rec_out and
28 * passed it as OUT NOCOPY variable to INTEGER_MULTIPLE_SCALE procedure.
29 * and added code in INTEGER_MULTIPLE_SCALE procedure
30 * to retain all the values of scale_rec.
31 * V.Anitha 03/05/2004 BUG#3018432
32 * In floor_dowm and ceil_up procedures, p_scale_multiple datatype
33 * changed to NUMBER from PLS_INTEGER to get correct value in quantity
34 * field after scaling when integer is selected as scale_type.
35 * S.Sriram 15Jun2004 Bug# 3680537
36 * In the ceil_up and floor_down procedures, the qty. should first be rounded off
37 * to 9 digits before integer scaling is done.
38 * S.Sriram 26-Nov2004 NPD Convergence.
39 * Added orgn_id in scale and theoretical_yield procedures and modified references
40 * to sy_uoms table to mtl_units_of measure and used the INV_CONVERT.inv_um_convert
41 * procedure for Qty. conversion.
42 * TDaniel 21-NOV-2006 Bug 5667857 - Changed unit_of_measure reference to UOM_CODE.
43 *****************************************************************************************************************/
44
45 /**********************************************************************
46 * OVERVIEW:
47 * ========
48 *
49 The Scale Batch procedure accepts as input the local scale table,
50 which includes a subset of the detail lines of the batch as
51 they exist in gme_material_details.
52 This subset is defined by scale_rec record and contains the fields
53 necessary for scaling of the batch.
54 Initially, determine the uom of the FM_YIELD_TYPE.
55 Then Accumulate counter
56 values of batch output and input, which will determine the scale
57 type of the items and whether they contribute to yield.
58 From these counters, determine whether certain conditions
59 exist: does a simple scaling scenario exist that
60 will enable early exit or do we have to scale at all.
61 If these conditions are not met, continue on to PHASE I and
62 then PHASE II of the full scaling process.
63 **********************************************************************/
64
65 /* Procedure to get the standard uom for the 'fm_yield_type'
66 This will be used to convert all the quantities into single
67 unit of measure
68 */
69
70 PROCEDURE get_fm_yield_type
71 (
72 p_orgn_id IN NUMBER,
73 x_conv_uom OUT NOCOPY VARCHAR2,
74 x_return_status OUT NOCOPY VARCHAR2)
75 IS
76
77 CURSOR sy_uoms_typ_cursor(v_yield_type VARCHAR2) IS
78 SELECT uom_code
79 FROM mtl_units_of_measure
80 WHERE uom_class = v_yield_type
81 AND base_uom_flag = 'Y';
82
83 l_yield_type VARCHAR2(30);
84 l_return_status VARCHAR2(10);
85
86 BEGIN
87
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89
90 -- NPD Convergence
91 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => p_orgn_id ,
92 P_parm_name => 'FM_YIELD_TYPE' ,
93 P_parm_value => l_yield_type ,
94 X_return_status => l_return_status );
95
96 OPEN sy_uoms_typ_cursor(l_yield_type);
97 FETCH sy_uoms_typ_cursor INTO x_conv_uom;
98 CLOSE sy_uoms_typ_cursor;
99
100 IF (x_conv_uom IS NULL OR x_conv_uom = ' ')
101 THEN
102 fnd_message.set_name('GME','GME_STANDARD_UOM_NOT_DEFINED');
103 fnd_msg_pub.add;
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 RETURN;
106 END IF;
107
108 END get_fm_yield_type;
109
110
111 PROCEDURE scale
112 ( p_scale_tab IN scale_tab
113 , p_orgn_id IN NUMBER
114 , p_scale_factor IN NUMBER
115 , p_primaries IN VARCHAR2
116 , x_scale_tab OUT NOCOPY scale_tab
117 , x_return_status OUT NOCOPY VARCHAR2
118 )
119 IS
120 l_scale_tab scale_tab ;
121 x_scale_rec scale_rec ;
122 --Begin Bug#3164299 P.Raghu
123 x_scale_rec_out scale_rec ;
124 --End Bug#3164299
125 l_qty NUMBER;
126 l_tab_size NUMBER;
127 -- l_conv_uom sy_uoms_mst.um_code%TYPE;
128 l_conv_uom mtl_units_of_measure.uom_code%TYPE;
129 l_type_0_Y_outputs NUMBER:= 0;
130 l_type_1_Y_outputs NUMBER:= 0;
131 l_type_2_Y_outputs NUMBER:= 0;
132 l_type_0_N_outputs NUMBER:= 0;
133 l_type_1_N_outputs NUMBER:= 0;
134 l_type_2_N_outputs NUMBER:= 0;
135 l_type_0_Y_inputs NUMBER:= 0;
136 l_type_1_Y_inputs NUMBER:= 0;
137 l_type_2_Y_inputs NUMBER:= 0;
138 l_type_0_N_inputs NUMBER:= 0;
139 l_type_1_N_inputs NUMBER:= 0;
140 l_type_2_N_inputs NUMBER:= 0;
141 l_fixed_input_qty NUMBER:= 0;
142 l_fixed_output_qty NUMBER:= 0;
143 l_proportional_input_qty NUMBER:= 0;
144 l_proportional_output_qty NUMBER:= 0;
145 P NUMBER:= 0;
146 S NUMBER:= 0;
147 A NUMBER:= 0;
148 k NUMBER:= 0;
149 b NUMBER:= 0;
150 b1 NUMBER:= 0;
151 l_input_total NUMBER:= 0;
152 l_output_total NUMBER:= 0;
153
154 CURSOR sy_uoms_typ_cursor(v_yield_type VARCHAR2) IS
155 SELECT uom_code
156 FROM mtl_units_of_measure
157 WHERE uom_class = v_yield_type
158 AND base_uom_flag = 'Y';
159
160 l_yield_type VARCHAR2(30);
161 l_return_status VARCHAR2(10);
162
163 --**********************************************************************
164
165 BEGIN
166 -- added by pawan kumar for intializing success
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168 l_tab_size := p_scale_tab.count;
169 --**********************************************************************
170 --Determine the uom to which the items that conbribute to yield
171 --must be converted.
172 --**********************************************************************
173 --Moved this out the loop
174
175 -- NPD Convergence
176 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => p_orgn_id ,
177 P_parm_name => 'FM_YIELD_TYPE' ,
178 P_parm_value => l_yield_type ,
179 X_return_status => l_return_status );
180
181 OPEN sy_uoms_typ_cursor(l_yield_type);
182 FETCH sy_uoms_typ_cursor INTO l_conv_uom;
183 CLOSE sy_uoms_typ_cursor;
184
185 -- dbms_output.put_line('l_conv_uom ' || l_conv_uom);
186
187 IF (l_conv_uom IS NULL OR l_conv_uom = ' ')
188 THEN
189 fnd_message.set_name('GME','GME_STANDARD_UOM_NOT_DEFINED');
190 fnd_msg_pub.add;
191 -- dbms_output.put_line('No standard UOM for FM_YIELD_TYPE');
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 RETURN;
194 END IF;
195 FOR l_row_count IN 1 .. l_tab_size
196 LOOP
197 --*************************************************************
198 --Initial data validation. If the data is not correct,
199 --exit from scaling with an error message.
200 --*************************************************************
201
202 IF (p_scale_tab(l_row_count).inventory_item_id IS NULL
203 OR p_scale_tab(l_row_count).detail_uom IS NULL)
204 THEN
205 fnd_message.set_name('GME','GME_INVALID_ITEM_OR_ITEM_UM');
206 fnd_msg_pub.add;
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 -- dbms_output.put_line('INVALID_ITEM_AND_OR_ITEM_UM');
209 END IF;
210 -- Pawan Kumar added check for null
211 IF (p_scale_tab(l_row_count).line_type IS NULL) OR
212 (p_scale_tab(l_row_count).line_type NOT IN (-1,1,2))
213 THEN
214 fnd_message.set_name('GME','GME_INVALID_LINE_TYPE');
215 fnd_msg_pub.add;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 -- dbms_output.put_line('INVALID_LINE_TYPE');
218 END IF;
219 IF (p_scale_tab(l_row_count).line_no IS NULL
220 OR p_scale_tab(l_row_count).line_no <= 0 )
221 THEN
222 fnd_message.set_name('GME','GME_INVALID_LINE_NUMBER');
223 fnd_msg_pub.add;
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 -- dbms_output.put_line('INVALID_LINE_NUMBER');
226 END IF ;
227 -- Pawan Kumar added check for null
228 IF (p_scale_tab(l_row_count).scale_type IS NULL) OR
229 (p_scale_tab(l_row_count).scale_type NOT IN (0,1,2))
230 THEN
231 fnd_message.set_name('GME','GME_INVALID_SCALE_TYPE');
232 fnd_msg_pub.add;
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 -- dbms_output.put_line('INVALID_SCALE_TYPE');
235 END IF;
236 -- Pawan Kumar added check for null
237 IF (p_scale_tab(l_row_count).contribute_yield_ind IS NULL) OR
238 (p_scale_tab(l_row_count).contribute_yield_ind NOT IN ('Y','N'))
239 THEN
240 fnd_message.set_name('GME','GME_INVALID_CONTRIBUTE_YIELD');
241 fnd_msg_pub.add;
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 -- dbms_output.put_line('INVALID_CONTRIBUTE_YIELD_IND');
244 END IF;
245 -- Pawan Kumar added check for scale type = 2 as the following are required then only
246 IF (p_scale_tab(l_row_count).scale_type = 2) THEN
247 IF (p_scale_tab(l_row_count).scale_multiple IS NULL) OR
248 (p_scale_tab(l_row_count).scale_multiple < 0 )
249 THEN
250 fnd_message.set_name('GME','GME_INVALID_SCALE_MULTIPLE');
251 fnd_msg_pub.add;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 -- dbms_output.put_line('INVALID_SCALE_MULTIPLE');
254 END IF;
255 --BEGIN BUG#2901379
256 --Do not validate the scale_rounding_variance being NULL.
257 --IF (p_scale_tab(l_row_count).scale_rounding_variance IS NULL) OR
258 IF ((p_scale_tab(l_row_count).scale_rounding_variance < 0)
259 OR (p_scale_tab(l_row_count).scale_rounding_variance > 1))
260 THEN
261 fnd_message.set_name('GME','GME_INVALID_SCALE_VARIANCE');
262 fnd_msg_pub.add;
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 -- dbms_output.put_line('INVALID_SCALE_ROUNDING_VARIANCE');
265 END IF;
266 --END BUG#2901379
267
268 IF (p_scale_tab(l_row_count).rounding_direction IS NULL) OR
269 (p_scale_tab(l_row_count).rounding_direction NOT IN (0,1,2))
270 THEN
271 fnd_message.set_name('GME','GME_INVALID_ROUNDING_DIRECTION');
272 fnd_msg_pub.add;
273 x_return_status := FND_API.G_RET_STS_ERROR;
274 -- dbms_output.put_line('INVALID_ROUNDING_DIRECTION');
275 END IF;
276 END IF;
277 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
278 THEN
279 RETURN;
280 END IF;
281
282
283 --**********************************************************************
284 --Here determining the scale type of the outputs and whether they
285 --contribute to yield. Increment appropriate counter.
286 --**********************************************************************
287 IF p_scale_tab(l_row_count).line_type IN (1,2)
288 THEN
289 -- dbms_output.put_line('determining scale type of output ');
290 IF p_scale_tab(l_row_count).scale_type = 0 AND
291 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
292 THEN
293 l_type_0_Y_outputs := l_type_0_Y_outputs + 1;
294 ELSIF p_scale_tab(l_row_count).scale_type = 1 AND
295 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
296 THEN
297 l_type_1_Y_outputs := l_type_1_Y_outputs+1;
298 ELSIF p_scale_tab(l_row_count).scale_type = 2 AND
299 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
300 THEN
301 l_type_2_Y_outputs := l_type_2_Y_outputs+1;
302 ELSIF p_scale_tab(l_row_count).scale_type =0 AND
303 p_scale_tab(l_row_count).contribute_yield_ind = 'N'
304 THEN
305 l_type_0_N_outputs := l_type_0_N_outputs+1;
306 ELSIF p_scale_tab(l_row_count).scale_type =1 AND
307 p_scale_tab(l_row_count).contribute_yield_ind = 'N'
308 THEN
309 l_type_1_N_outputs := l_type_1_N_outputs+1;
310 ELSIF p_scale_tab(l_row_count).scale_type =2 AND
311 p_scale_tab(l_row_count).contribute_yield_ind = 'N'
312 THEN
313 l_type_2_N_outputs := l_type_2_N_outputs+1;
314 ELSE
315 -- Use this branch for future scale types.
316 NULL;
317 END IF;
318 l_output_total := l_output_total +1 ;
319
320 --**********************************************************************
321 --Here determining the scale type of the inputs and whether they
322 --contribute to yield. Increment appropriate counter.
323 --**********************************************************************
324
325 ELSIF p_scale_tab(l_row_count).line_type = -1
326 THEN
327 -- dbms_output.put_line('determining scale type of input ');
328 IF p_scale_tab(l_row_count).scale_type = 0 AND
329 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
330 THEN
331 l_type_0_Y_inputs := l_type_0_Y_inputs + 1;
332 ELSIF p_scale_tab(l_row_count).scale_type = 1 AND
333 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
334 THEN
335 l_type_1_Y_inputs := l_type_1_Y_inputs + 1;
336 ELSIF p_scale_tab(l_row_count).scale_type = 2 AND
337 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
338 THEN
339 l_type_2_Y_inputs := l_type_2_Y_inputs + 1;
340 ELSIF p_scale_tab(l_row_count).scale_type =0 AND
341 p_scale_tab(l_row_count).contribute_yield_ind = 'N'
342 THEN
343 l_type_0_N_inputs := l_type_0_N_inputs+1;
344 ELSIF p_scale_tab(l_row_count).scale_type =1 AND
345 p_scale_tab(l_row_count).contribute_yield_ind = 'N'
346 THEN
347 l_type_1_N_inputs := l_type_1_N_inputs+1;
348 ELSIF p_scale_tab(l_row_count).scale_type =2 AND
349 p_scale_tab(l_row_count).contribute_yield_ind = 'N'
350 THEN
351 l_type_2_N_inputs := l_type_2_N_inputs+1;
352 ELSE
353 -- Use this branch for future scale types.
354 NULL;
355 END IF;
356 l_input_total := l_input_total +1 ;
357 END IF;
358 END LOOP;
359
360 -- dbms_output.put_line('l_input_total = ' || to_char(l_input_total));
361 -- dbms_output.put_line('l_output_total = ' || to_char(l_output_total));
362
363 --**********************************************************************
364 --Now that we have the output/input counters, which tell us the scale_type
365 --and whether the output/input contributes to yield, we can test for some
366 --conditions that if satisfied will allow for uncomplicated scaling and/or
367 --early exit from scaling routine.
368 --**********************************************************************
369
370 --**********************************************************************
371 --Condition 1:
372 --If all outputs and inputs are scaleable(scale_type=1,2) and regardless
373 --of whether they contribute to yield(contribute_yield_ind=Y or N), apply
374 --the same entered scale factor to all outputs and inputs. Exit.
375 --**********************************************************************
376
377 IF l_type_1_Y_inputs + l_type_1_N_inputs + l_type_2_Y_inputs + l_type_2_N_inputs + l_type_1_Y_outputs + l_type_1_N_outputs + l_type_2_Y_outputs + l_type_2_N_outputs = l_tab_size
378 THEN
379 -- dbms_output.put_line('YES all input and output are scale=1,2 contribute=Y');
380 -- dbms_output.put_line('scale by factor and get out!');
381
382 x_scale_tab := p_scale_tab;
383 FOR l_row_count IN 1 .. l_tab_size
384 LOOP
385 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * p_scale_factor;
386 --BEGIN BUG#2901379 PR
387 --Commented the condition to check the boundary of scale_rounding_variance as this fails if cale_rounding_variance is NULL;
388 IF x_scale_tab(l_row_count).scale_type = 2
389 AND x_scale_tab(l_row_count).scale_multiple > 0
390 --AND x_scale_tab(l_row_count).scale_rounding_variance > 0
391 --AND x_scale_tab(l_row_count).scale_rounding_variance <= 1
392 --END BUG#2901379
393 THEN
394 -- dbms_output.put_line('just b4 call to integer_multiple_scale');
395 -- dbms_output.put_line('l_row_count = '||to_char(l_row_count));
396
397 integer_multiple_scale(x_scale_tab(l_row_count)
398 --Begin Bug#3164299 P.Raghu
399 , x_scale_rec_out
400 --End Bug#3164299
401 , x_return_status);
402 -- dbms_output.put_line('just after call to integer_multiple_scale');
403
404 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
405 THEN
406 fnd_message.set_name('GME','GME_INTEGER_MULTIPLE_SCALE_ERR');
407 fnd_msg_pub.add;
408 -- dbms_output.put_line('INTEGER_MULTIPLE_SCALE_ERROR ');
409 RETURN;
410 END IF;
411 --Begin Bug#3164299 P.Raghu
412 x_scale_tab(l_row_count) := x_scale_rec_out;
413 --End Bug#3164299
414 END IF;
415 END LOOP;
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
417 RETURN;
418
419 --**********************************************************************
420 --Condition 2:
421 --If all outputs and inputs are fixed(scale_type=0) and regardless
422 --of whether they contribute to yield(contribute_yield_ind=Y or N),
423 --Exit. No items are available for scaling.
424 --**********************************************************************
425
426 -- dbms_output.put_line('determine whether all input and output are fixed=0');
427
428 ELSIF l_type_0_Y_inputs + l_type_0_N_inputs + l_type_0_Y_outputs + l_type_0_N_outputs = l_tab_size
429 THEN
430 x_scale_tab := p_scale_tab;
431
432 fnd_message.set_name('GME','GME_ALL_FIXED_NO_SCALING');
433 fnd_msg_pub.add;
434 -- dbms_output.put_line('All items are non-scaleable. No scaling permitted');
435
436 x_return_status := FND_API.G_RET_STS_ERROR;
437 RETURN;
438
439 --**********************************************************************
440 --Condition 3:
441 --If all outputs or if all inputs are fixed(scale_type=0)
442 --or if no scaleable items that contribute to yield exist,
443 --scaling is not permitted.
444 --At least one ingredient must be scaleable and contribute to yield.
445 --At least one product must be scaleable and contribute to yield.
446
447 --**********************************************************************
448
449 ELSIF (l_type_0_Y_inputs + l_type_0_N_inputs + l_type_1_N_inputs
450 + l_type_2_N_inputs= l_input_total)
451 THEN
452 -- dbms_output.put_line('l_type_0_Y_inputs = ' || l_type_0_Y_inputs);
453 -- dbms_output.put_line('l_type_0_N_inputs = ' || l_type_0_N_inputs);
454 -- dbms_output.put_line('l_type_1_N_inputs = ' || l_type_1_N_inputs);
455 -- dbms_output.put_line('l_type_2_N_inputs = ' || l_type_2_N_inputs);
456
457 fnd_message.set_name('GME','GME_NO_YC_PROPORTIONAL_INPUT');
458 fnd_msg_pub.add;
459 -- dbms_output.put_line('At least one YC scaleable ingredient must exist.') ;
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 RETURN;
462
463 ELSIF (l_type_0_Y_outputs + l_type_0_N_outputs + l_type_1_N_outputs
464 + l_type_2_N_outputs = l_output_total)
465 THEN
466 -- dbms_output.put_line('l_type_0_Y_outputs = ' || l_type_0_Y_outputs);
467 -- dbms_output.put_line('l_type_0_N_outputs = ' || l_type_0_N_outputs);
468 -- dbms_output.put_line('l_type_1_N_outputs = ' || l_type_1_N_outputs);
469 -- dbms_output.put_line('l_type_2_N_outputs = ' || l_type_2_N_outputs);
470
471 fnd_message.set_name('GME','GME_NO_YC_PROPORTIONAL_OUTPUT');
472 fnd_msg_pub.add;
473 -- dbms_output.put_line('At least one YC scaleable product must exist.') ;
474 x_return_status := FND_API.G_RET_STS_ERROR;
475 RETURN;
476
477
478 END IF;
479
480 --**********************************************************************
481 --At this point, we know that at least one or more items of the batch
482 --differ in scale type. The items are not all scalable and they are not
483 --all fixed.The following phases of the scaling process begin here:
484
485 --PHASE I:
486 --Here regardless of scale type, all items that contribute
487 --to yield are passed to the uom_conversion program.
488 --Each qty that contributes to yield must be converted to the fm_yield_type.
489 --Counters are updated with the qty of the output/input
490 --and indicate whether the output/input is fixed or scalable.
491 --These qtys will be used in calculating the
492 --secondary scale factor(b).
493
494 --PHASE II:
495 --Phase II will execute two different sections of code depending
496 --on whether the primaries=OUTPUT or INPUT. The primary parameter is the
497 --one to which we will apply the entered scale factor. Both sections of
498 --Phase II consists of 4 parts. Within the 4 parts, we do the following:
499 -- Sum the item qtys that contribute to yield and store value in A.
500 -- Calculate the scale factor for the secondary using qty's
501 -- calculated in PHASE 1.
502 -- Apply entered scale factor to primary and calculated scale factor
503 -- to the secondary.
504 -- Determine whether integer_multiple scaling is required.
505 --**********************************************************************
506
507 l_scale_tab := p_scale_tab;
508 x_scale_tab := p_scale_tab;
509
510
511 --**********************************************************************
512 --PHASE I: UOM conversion and increment fixed and proportional
513 --output and input qty counters.
514 --Note:
515 --!Importance of these counters is to accumulate qty that
516 --contributes to yield.
517 --**********************************************************************
518
519 -- dbms_output.put_line('Phase I: calling uom conv ');
520
521 FOR l_row_count IN 1 .. l_tab_size
522 LOOP
523 IF l_scale_tab(l_row_count).scale_type IN (0,1,2) AND
524 l_scale_tab(l_row_count).contribute_yield_ind='Y'
525 THEN
526 -- NPD Conv.
527 l_qty := INV_CONVERT.inv_um_convert(item_id => p_scale_tab(l_row_count).inventory_item_id
528 ,precision => 5
529 ,from_quantity => p_scale_tab(l_row_count).qty
530 ,from_unit => p_scale_tab(l_row_count).detail_uom
531 ,to_unit => l_conv_uom
532 ,from_name => NULL
533 ,to_name => NULL);
534 IF l_qty < 0
535 THEN
536 -- Report UOM failure
537 -- dbms_output.put_line('x_return_status = ' || x_return_status);
538 fnd_message.set_name('GMI','GMI_OPM_UOM_NOT_FOUND');
539 fnd_msg_pub.add;
540 -- dbms_output.put_line('GMI UOM_CONVERSION_ERROR');
541 x_return_status := FND_API.G_RET_STS_ERROR;
542 RETURN;
543 ELSE
544 l_scale_tab(l_row_count).qty := l_qty;
545 l_scale_tab(l_row_count).detail_uom := l_conv_uom;
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 -- dbms_output.put_line('x_return_status = ' || x_return_status);
549
550 --**********************************************************************
551 --PHASE I:
552 --Importance of these counters is to accumulate prod/byprod qty that
553 --contributes to yield.
554 --If prod/byprod has a fixed scale type(scale_type=0) and contributes
555 --to yield, add its qty to fixed qty variable.
556 --If prod/byprod has a scalable scale type(scale_type=1,2) and contributes
557 --to yield, add its qty to proportional qty variable.
558 --**********************************************************************
559
560 -- dbms_output.put_line('Phase I:increment fix/proportional output qty counter');
561 IF l_scale_tab(l_row_count).line_type IN (1,2)
562 THEN
563 IF l_scale_tab(l_row_count).scale_type = 0 AND
564 l_scale_tab(l_row_count).contribute_yield_ind='Y'
565 THEN
566 l_fixed_output_qty := l_fixed_output_qty + l_scale_tab(l_row_count).qty;
567 -- dbms_output.put_line('l_fixed_output_qty= '||to_char(l_fixed_output_qty));
568 ELSIF l_scale_tab(l_row_count).scale_type IN (1,2) AND
569 l_scale_tab(l_row_count).contribute_yield_ind='Y'
570 THEN
571 l_proportional_output_qty := l_proportional_output_qty + l_scale_tab(l_row_count).qty;
572 -- dbms_output.put_line('l_proportional_output_qty= '||to_char(l_proportional_output_qty));
573 END IF;
574
575 --**********************************************************************
576 --PHASE I:
577 --Importance of these counters is to accumulate ingredient qty that
578 --contributes to yield.
579 --If ingredient has a fixed scale type(scale_type=0) and contributes
580 --to yield, add its qty to fixed qty variable.
581 --If ingredient has a scalable scale type(scale_type=1,2) and contributes
582 --to yield, add its qty to proportional qty variable.
583 --**********************************************************************
584 ELSE
585 -- dbms_output.put_line('Phase I:increment fix/proportional INPUT qty counter');
586 IF l_scale_tab(l_row_count).scale_type = 0 AND
587 l_scale_tab(l_row_count).contribute_yield_ind='Y'
588 THEN
589 l_fixed_input_qty := l_fixed_input_qty + l_scale_tab(l_row_count).qty;
590 -- dbms_output.put_line('l_fixed_input_qty= '||to_char(l_fixed_input_qty));
591
592 ELSIF l_scale_tab(l_row_count).scale_type IN (1,2) AND
593 l_scale_tab(l_row_count).contribute_yield_ind='Y'
594 THEN
595 l_proportional_input_qty := l_proportional_input_qty + l_scale_tab(l_row_count).qty;
596 -- dbms_output.put_line('l_proportional_input_qty= '||to_char(l_proportional_input_qty));
597 END IF;
598 END IF;
599 END IF;
600 END IF;
601 END LOOP;
602
603
604 /*
605 IF (l_fixed_output_qty + l_proportional_output_qty +
606 l_fixed_input_qty +l_proportional_input_qty = 0 )
607 THEN
608 fnd_message.set_name('GME','NO_YIELD_CONTR_PRIM_OR_SEC');
609 fnd_msg_pub.add;
610 -- dbms_output.put_line('NO_FIXED_OR_PROPORTIONAL_PRIMARY_OR_SECONDARY');
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 RETURN;
613 END IF;
614 */
615
616 -- dbms_output.put_line('PHASE I ') ;
617 -- dbms_output.put_line('l_fixed_output_qty= '||to_char(l_fixed_output_qty));
618 -- dbms_output.put_line('l_proportional_output_qty= '||to_char(l_proportional_output_qty));
619 -- dbms_output.put_line('l_fixed_input_qty= '||to_char(l_fixed_input_qty));
620 -- dbms_output.put_line('l_proportional_input_qty= '||to_char(l_proportional_input_qty));
621
622
623 --**********************************************************************
624 --PHASE II when primaries=OUTPUT:
625 --Part 1
626 --When p_primaries = 'OUTPUT', sum the prod/byprod that contribute to
627 --yield and store the accumulated value in variable A.
628 --**********************************************************************
629
630 IF p_primaries = 'OUTPUTS'
631 THEN
632 -- dbms_output.put_line('PHASE II; Part 1; primaries = OUTPUT') ;
633 --Sum of fixed and scalable prod/byprod that contribute to yield.
634 P := l_fixed_output_qty + l_proportional_output_qty;
635 IF (P = 0 )
636 THEN
637 -- dbms_output.put_line('P=0');
638 /*
639 fnd_message.set_name('GME','NO_YIELD_CONTR_PRIMARIES');
640 fnd_msg_pub.add;
641 -- dbms_output.put_line('NO_YIELD_CONTR_PRIMARIES_EXIT');
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 */
644 RETURN;
645 END IF;
646
647 --Sum of fixed and scalable ingred that contribute to yield.
648 S := l_fixed_input_qty + l_proportional_input_qty;
649 --Yield Ratio of Outputs/Inputs
650 IF (S = 0 )
651 THEN
652 -- dbms_output.put_line('S=0');
653
654 /*
655 fnd_message.set_name('GME','NO_YIELD_CONTR_SECONDARIES');
656 fnd_msg_pub.add;
657 -- dbms_output.put_line('NO_YIELD_CONTR_SECONDARIES_EXIST');
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 */
660 RETURN;
661 ELSIF (l_proportional_input_qty = 0)
662 THEN
663 -- dbms_output.put_line('l_proportional_input_qty=0');
664
665 /*
666 fnd_message.set_name('GME','NO_PROPORTIONAL_SECONDARIES');
667 fnd_msg_pub.add;
668 -- dbms_output.put_line('NO_PROPORTIONAL_SECONDARIES_EXIST');
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 */
671 RETURN;
672
673 END IF;
674
675 k := P/S;
676 -- dbms_output.put_line('P= '||to_char(P));
677 -- dbms_output.put_line('S= '||to_char(S));
678 -- dbms_output.put_line('k= '||to_char(k));
679
680 FOR l_row_count IN 1 .. l_tab_size
681 LOOP
682 IF l_scale_tab(l_row_count).line_type IN (1,2)
683 THEN
684 IF l_scale_tab(l_row_count).scale_type IN (1,2) AND
685 l_scale_tab(l_row_count).contribute_yield_ind='Y'
686 THEN
687 A := A + p_scale_factor * l_scale_tab(l_row_count).qty;
688 -- dbms_output.put_line('A calc w/scale factor= '||to_char(A));
689 ELSIF l_scale_tab(l_row_count).scale_type = 0 AND
690 l_scale_tab(l_row_count).contribute_yield_ind='Y'
691 THEN
692 A := A + l_scale_tab(l_row_count).qty;
693 -- dbms_output.put_line('A w/o scale factor= '||to_char(A));
694 END IF;
695 END IF;
696 END LOOP;
697
698 -- dbms_output.put_line('A calc = '||to_char(A));
699 -- dbms_output.put_line('A w/o scale factor= '||to_char(A));
700
701
702 --**********************************************************************
703 --PHASE II when primaries= OUTPUT
704 --Part 2
705 --Calcuate the scale factor(b) that will be applied to the scalable
706 --ingredients, which are the secondaries.
707 --**********************************************************************
708 -- dbms_output.put_line('PHASE II; Part 2') ;
709 -- dbms_output.put_line(' A = '||to_char(A));
710 -- dbms_output.put_line(' k = '||to_char(k));
711 -- dbms_output.put_line('l_fixed_input_qty= '||to_char(l_fixed_input_qty));
712 -- dbms_output.put_line('l_proportional_input_qty= '||to_char(l_proportional_input_qty));
713
714 --**********************************************************************
715 --Here checking for whether the l_fixed_input_qty is greater than
716 --the dividend of the primaries that contribute to yield and
717 --the yield ratio. If the value is greater, scaling would result in a
718 --in a negative scale factor applied to any scalable
719 --item that contributes to yield. As such, stop scaling.
720 --*********************************************************************
721 b1 := ((A/k) - l_fixed_input_qty);
722 -- dbms_output.put_line(' b1 = '||to_char(b1));
723 IF b1 < 0
724 THEN
725 fnd_message.set_name('GME','GME_NEGATIVE_SCALE_FACTOR_CALC');
726 fnd_msg_pub.add;
727 -- dbms_output.put_line('Cannot scale to a qty lower than the qty of your non-scaleable items ') ;
728
729 x_return_status := FND_API.G_RET_STS_ERROR;
730 RETURN;
731 ELSE
732 b := b1 / l_proportional_input_qty;
733 END IF;
734
735 -- dbms_output.put_line(' b = '||to_char(b));
736
737 --**********************************************************************
738 --PHASE II when primaries=OUTPUT:
739 --Part 3
740 --Loop thru the structure
741 -- For scalable prod/byprod, apply entered scale factor regardless
742 -- of whether prod/byprod contributes to yield.
743 -- For scalable ingredients, apply calculated scale factor regardless
744 -- of whether ingredient contributes to yield.
745 --**********************************************************************
746 -- dbms_output.put_line('PHASE II; Part 3-Apply scale factor') ;
747
748 FOR l_row_count IN 1 .. l_tab_size
749 LOOP
750 IF x_scale_tab(l_row_count).line_type IN (1,2)
751 THEN
752 IF x_scale_tab(l_row_count).scale_type IN (1,2)
753 THEN
754 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * p_scale_factor;
755 -- dbms_output.put_line('l_row_count = '||to_char(l_row_count));
756 -- dbms_output.put_line('prod scaled qty = '||to_char(x_scale_tab(l_row_count).qty));
757 END IF;
758 ELSE
759 IF x_scale_tab(l_row_count).scale_type IN (1,2)
760 THEN
761 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * b;
762 -- dbms_output.put_line('l_row_count = '||to_char(l_row_count));
763 -- dbms_output.put_line('ingred scaled qty-b applied = '||to_char(x_scale_tab(l_row_count).qty));
764 END IF;
765 END IF;
766
767 --**********************************************************************
768 --Determine whether integer_multiple scaling is required.
769 --**********************************************************************
770 IF x_scale_tab(l_row_count).scale_type = 2
771 AND x_scale_tab(l_row_count).scale_multiple > 0
772 -- Bug 3069408 Do not check the rounding variance
773 -- AND x_scale_tab(l_row_count).scale_rounding_variance > 0
774 -- AND x_scale_tab(l_row_count).scale_rounding_variance <= 1
775 THEN
776 -- dbms_output.put_line('just b4 call to integer_multiple_scale');
777 -- dbms_output.put_line('l_row_count = '||to_char(l_row_count));
778
779 integer_multiple_scale(x_scale_tab(l_row_count)
780 --Begin Bug#3164299 P.Raghu
781 , x_scale_rec_out
782 --End Bug#3164299
783 , x_return_status);
784 -- dbms_output.put_line('just after call to integer_multiple_scale');
785
786 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
787 THEN
788 fnd_message.set_name('GME','GME_INTEGER_MULTIPLE_SCALE_ERR');
789 fnd_msg_pub.add;
790 -- dbms_output.put_line('INTEGER_MULTIPLE_SCALE_ERROR ');
791 RETURN;
792 END IF;
793 --Begin Bug#3164299 P.Raghu
794 x_scale_tab(l_row_count) := x_scale_rec_out;
795 --End Bug#3164299
796 END IF;
797
798 END LOOP;
799
800 --**********************************************************************
801 --PHASE II when primaries=INPUT:
802 --Part 1
803 --When p_primaries = 'INPUT', sum the ingredients that contribute to
804 --yield and store the accumulated value in variable A.
805 --**********************************************************************
806
807 ELSIF p_primaries = 'INPUTS'
808 THEN
809 -- dbms_output.put_line('PHASE II; Part 1; primaries = INPUT') ;
810 --Sum of ingredients that contribute to yield.
811 P := l_fixed_input_qty + l_proportional_input_qty;
812 --Sum of fixed and scalable prod/byprod that contribute to yield.
813 IF (P = 0 )
814 THEN
815 -- dbms_output.put_line('P=0');
816 /*
817 fnd_message.set_name('GME','NO_YIELD_CONTR_PRIMARIES');
818 fnd_msg_pub.add;
819 -- dbms_output.put_line('NO_YIELD_CONTR_PRIMARIES_EXIT');
820 x_return_status := FND_API.G_RET_STS_ERROR;
821 */
822 RETURN;
823 END IF;
824
825 S := l_fixed_output_qty + l_proportional_output_qty;
826 --Yield Ratio of Inputs/Outputs
827 IF (S = 0 )
828 THEN
829 -- dbms_output.put_line('S=0');
830 /*
831 fnd_message.set_name('GME','NO_YIELD_CONTR_SECONDARIES');
832 fnd_msg_pub.add;
833 -- dbms_output.put_line('NO_YIELD_CONTR_SECONDARIES_EXIST');
834 x_return_status := FND_API.G_RET_STS_ERROR;
835 */
836 RETURN;
837
838
839 ELSIF (l_proportional_output_qty = 0)
840 THEN
841 -- dbms_output.put_line('l_proportional_output_qty=0');
842
843 /*
844 fnd_message.set_name('GME','NO_PROPORTIONAL_SECONDARIES');
845 fnd_msg_pub.add;
846 -- dbms_output.put_line('NO_PROPORTIONAL_SECONDARIES_EXIST');
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 */
849 RETURN;
850 END IF;
851
852 -- dbms_output.put_line('P= '||to_char(P));
853 -- dbms_output.put_line('S= '||to_char(S));
854
855 k := P/S;
856
857 -- dbms_output.put_line('k= '||to_char(k));
858 FOR l_row_count IN 1 .. l_tab_size
859 LOOP
860 IF l_scale_tab(l_row_count).line_type = -1
861 THEN
862 IF l_scale_tab(l_row_count).scale_type IN (1,2) AND
863 l_scale_tab(l_row_count).contribute_yield_ind='Y'
864 THEN
865 A := A + p_scale_factor * l_scale_tab(l_row_count).qty;
866 -- dbms_output.put_line('A calc w/scale factor= '||to_char(A));
867 ELSIF l_scale_tab(l_row_count).scale_type = 0 AND
868 l_scale_tab(l_row_count).contribute_yield_ind='Y'
869 THEN
870 A := A + l_scale_tab(l_row_count).qty;
871 -- dbms_output.put_line('A calc w/o scale factor= '||to_char(A));
872 END IF;
873 END IF;
874 END LOOP;
875 -- dbms_output.put_line('A calc = '||to_char(A));
876 -- dbms_output.put_line('A calc w/o scale factor= '||to_char(A));
877
878 --**********************************************************************
879 --PHASE II when primaries=INPUT:
880 --Part 2
881 --Calcuate the scale factor(b) that will be applied to the scalable
882 --products, which are the secondaries.
883 --**********************************************************************
884 -- dbms_output.put_line('PHASE II; Part 2') ;
885 -- dbms_output.put_line(' A = '||to_char(A));
886 -- dbms_output.put_line(' k = '||to_char(k));
887 -- dbms_output.put_line('l_fixed_output_qty= '||to_char(l_fixed_output_qty));
888 -- dbms_output.put_line('l_proportional_output_qty= ' || to_char(l_proportional_output_qty));
889
890 --**********************************************************************
891 --Here checking for whether there are scalable prods/byprods.
892 --If there are no scalable prods/byprods, there
893 --is no need to calculate the secondary scale factor(b).
894 --In including this check, we are eliminating the possibility
895 --of a divide by zero.
896 --**********************************************************************
897
898 b1 := ((A/k) - l_fixed_output_qty);
899 -- dbms_output.put_line(' b1 = '||to_char(b1));
900 IF b1 < 0
901 THEN
902 fnd_message.set_name('GME','GME_NEGATIVE_SCALE_FACTOR_CALC');
903 fnd_msg_pub.add;
904 -- dbms_output.put_line('Cannot scale to a qty lower than the qty of your non-scaleable items ') ;
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 RETURN;
907 ELSE
908 b := b1 / l_proportional_output_qty;
909 END IF;
910 -- dbms_output.put_line(' b = '||to_char(b));
911
912 --**********************************************************************
913 --PHASE II when primaries=INPUT:
914 --Part 3
915 --Loop thru the structure
916 -- For scalable ingredients, apply entered scale factor regardless
917 -- of whether ingredient contributes to yield.
918 -- For scalable prod/byprod, apply calculated scale factor regardless
919 -- of whether prod/byprod contributes to yield.
920 --**********************************************************************
921 -- dbms_output.put_line('PHASE II; Part 3-Apply scale factor') ;
922
923 FOR l_row_count IN 1 .. l_tab_size
924 LOOP
925 IF x_scale_tab(l_row_count).line_type = -1
926 THEN
927 IF x_scale_tab(l_row_count).scale_type IN (1,2)
928 THEN
929 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * p_scale_factor;
930 -- dbms_output.put_line('l_row_count = '||to_char(l_row_count));
931 -- dbms_output.put_line('ingred scaled qty = '||to_char(x_scale_tab(l_row_count).qty));
932 END IF;
933 ELSE
934 IF x_scale_tab(l_row_count).scale_type IN (1,2)
935 THEN
936 x_scale_tab(l_row_count).qty := x_scale_tab(l_row_count).qty * b;
937 -- dbms_output.put_line('l_row_count = '||to_char(l_row_count));
938 -- dbms_output.put_line('prod scaled qty-b applied = '||to_char(x_scale_tab(l_row_count).qty));
939 END IF;
940 END IF;
941
942 --**********************************************************************
943 --Determine whether integer_multiple scaling is required.
944 --**********************************************************************
945 IF x_scale_tab(l_row_count).scale_type = 2
946 AND x_scale_tab(l_row_count).scale_multiple > 0
947 -- Bug 3069408 Do not check the rounding variance
948 -- AND x_scale_tab(l_row_count).scale_rounding_variance > 0
949 -- AND x_scale_tab(l_row_count).scale_rounding_variance <= 1
950 THEN
951 -- dbms_output.put_line('just b4 call to integer_multiple_scale');
952 -- dbms_output.put_line('l_row_count = '||to_char(l_row_count));
953 -- dbms_output.put_line('x_scale_tab(l_row_count).scale_type = '|| to_char(x_scale_tab(l_row_count).scale_type));
954
955 integer_multiple_scale(x_scale_tab(l_row_count)
956 --Begin Bug#3164299 P.Raghu
957 , x_scale_rec_out
958 --End Bug#3164299
959 , x_return_status);
960 -- dbms_output.put_line('just after call to integer_multiple_scale');
961
962 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
963 THEN
964 fnd_message.set_name('GME','GME_INTEGER_MULTIPLE_SCALE_ERR');
965 fnd_msg_pub.add;
966 -- dbms_output.put_line('INTEGER_MULTIPLE_SCALE_ERROR ');
967 RETURN;
968 END IF;
969 --Begin Bug#3164299 P.Raghu
970 x_scale_tab(l_row_count) := x_scale_rec_out;
971 --End Bug#3164299
972 END IF;
973
974 END LOOP;
975
976 END IF;
977
978 x_return_status := FND_API.G_RET_STS_SUCCESS;
979
980 EXCEPTION
981 WHEN OTHERS
982 THEN x_return_status := FND_API.G_RET_STS_ERROR;
983 END scale;
984
985
986 /**********************************************************************
987 *
988 * PROCEDURE: integer_multiple_scale
989 * PURPOSE: 11iplus enhanced functionality that allows for
990 * rounding in integer_increments.
991 * AUTHOR: Elizabeth Chen
992 *
993 * HISTORY:
994 * =======
995 * Elizabeth Chen Created.
996 *
997 * Praveen Surakanti 22-MAY-03 Bug#2901379
998 * Modified the integer_multiple_scale to handle
999 * the case of scale_rounding_variance being NULL
1000 *
1001 * P.Raghu 09-OCT-03 Bug#3164299
1002 * Added code to retain the values of scale_rec for OUT parameter.
1003 **********************************************************************/
1004
1005 /**********************************************************************
1006 * OVERVIEW:
1007 * =========
1008 *
1009 Procedure is called if scaling must be in integer increments.
1010 The variance value(v) is crucial in the determination of whether
1011 a value is eligible for rounding. Rounding is not permitted if the
1012 initial scaled qty of the item would be changed more than the
1013 variance value. The objective of the scaling procedure is to round to
1014 the nearest multiple, unless rounding is specified as UP or DOWN.
1015 In keeping the rounding allowance within this variance value, we
1016 eliminate the need for a rebalancing function.
1017
1018 --v = Variance Value.
1019 --v := scale_rounding_variance * scale_qty
1020 --This value will be used in the condition to evaluate
1021 --whether the quantity must be rounded up or down.
1022
1023 --a = Dividend of the initial scaled qty and the scale_multiple.
1024 --Through division, remove the multiple from the qty value. This
1025 --step is necessary as the floor and ceil functions only determine
1026 --the next integer up or down from a fractionalized value.
1027
1028 --floor_d = Difference between the initial scale qty and the
1029 --floored value.
1030
1031 --ceil_d = Difference between the ceiled qty and the
1032 --initial scaled qty.
1033
1034 ********************************************************************/
1035
1036 PROCEDURE integer_multiple_scale
1037 ( p_scale_rec IN scale_rec
1038 , x_scale_rec OUT NOCOPY scale_rec
1039 , x_return_status OUT NOCOPY VARCHAR2
1040 )
1041 IS
1042 l_im_scale_rec scale_rec ;
1043
1044 v NUMBER :=0;
1045 a NUMBER :=0;
1046 floor_qty NUMBER :=0;
1047 floor_d NUMBER :=0;
1048 ceil_qty NUMBER :=0;
1049 ceil_d NUMBER :=0;
1050
1051 BEGIN
1052
1053 -- dbms_output.put_line('in integer_multiple_scale function');
1054
1055 --*********************************************************************
1056 --Load fields of scale_rec into local variables.
1057 --*********************************************************************
1058 --Begin Bug#3164299 P.Raghu
1059 x_scale_rec := p_scale_rec;
1060 --End Bug#3164299
1061
1062 l_im_scale_rec.qty := p_scale_rec.qty;
1063
1064 -- dbms_output.put_line('IN qty = ' || to_char(l_im_scale_rec.qty));
1065 l_im_scale_rec.scale_multiple := p_scale_rec.scale_multiple;
1066 -- dbms_output.put_line('IN scale_multiple = ' || to_char(l_im_scale_rec.scale_multiple));
1067 l_im_scale_rec.scale_rounding_variance := p_scale_rec.scale_rounding_variance;
1068 -- dbms_output.put_line('IN rounding_variance = ' || to_char(l_im_scale_rec.scale_rounding_variance));
1069 l_im_scale_rec.rounding_direction := p_scale_rec.rounding_direction;
1070 -- dbms_output.put_line('IN rounding_direction = ' || to_char(l_im_scale_rec.rounding_direction));
1071
1072 --*********************************************************************
1073 --Determine variance value, v.
1074 --*********************************************************************
1075 v := l_im_scale_rec.scale_rounding_variance *
1076 l_im_scale_rec.qty ;
1077 -- dbms_output.put_line('v = ' || to_char(v));
1078
1079 --*********************************************************************
1080 --Determine dividend value, a.
1081 --*********************************************************************
1082 a := l_im_scale_rec.qty /
1083 l_im_scale_rec.scale_multiple ;
1084 -- dbms_output.put_line('a = ' || to_char(a));
1085
1086 --*********************************************************************
1087 --Evaluate rounding_direction value.
1088 --Phase - Round DOWN
1089 --If rounding_direction = 2 for DOWN, call floor function and
1090 --continue with calculations and evaluations.
1091 --*********************************************************************
1092 IF l_im_scale_rec.rounding_direction = 2
1093 THEN
1094 floor_down( a
1095 , l_im_scale_rec.scale_multiple
1096 , floor_qty
1097 , x_return_status );
1098 -- dbms_output.put_line('floor_qty = ' || to_char(floor_qty));
1099
1100 floor_d := l_im_scale_rec.qty - floor_qty;
1101 -- dbms_output.put_line('floor_d = ' || to_char(floor_d));
1102
1103 --*********************************************************************
1104 --Rounding down not permitted if the difference between the
1105 --initial scaled qty of the item and the floor_qty is greater
1106 --than the variance value(v) or if the floor_qty is 0.
1107 --*********************************************************************
1108 --BEGIN BUG#2901379 PR
1109 --Modified the condition to accommodate the NULL for the scale rounding variance.
1110 IF (v IS NULL OR floor_d <= v) AND floor_qty <> 0
1111 THEN
1112 x_scale_rec.qty := floor_qty;
1113 -- dbms_output.put_line('in IF') ;
1114 ELSE
1115 x_scale_rec.qty := l_im_scale_rec.qty;
1116 -- dbms_output.put_line('in ELSE') ;
1117 END IF;
1118 --END BUG#2901379 PR
1119 -- dbms_output.put_line('x_scale_rec.qty = ' || to_char(x_scale_rec.qty));
1120
1121 x_return_status := FND_API.G_RET_STS_SUCCESS;
1122 RETURN;
1123
1124 --*********************************************************************
1125 --Phase - Round UP
1126 --If rounding_direction = 1 for UP, call ceil function and
1127 --continue with calculations and evaluations.
1128 --*********************************************************************
1129 ELSIF l_im_scale_rec.rounding_direction = 1
1130 THEN
1131 ceil_up( a
1132 , l_im_scale_rec.scale_multiple
1133 , ceil_qty
1134 , x_return_status );
1135 -- dbms_output.put_line('ceil_qty = ' || to_char(ceil_qty));
1136
1137 ceil_d := ceil_qty - l_im_scale_rec.qty ;
1138 -- dbms_output.put_line('ceil_d = ' || to_char(ceil_d));
1139
1140 --*********************************************************************
1141 --Rounding UP not permitted if the difference between the
1142 --ceil_qty and the initial scaled qty of the item is greater
1143 --than the variance value(v).
1144 --*********************************************************************
1145 --BEGIN BUG# 2901379 PR
1146 --Modified the condition to accommodate the NULL for the scale rounding variance.
1147 IF (v IS NULL OR ceil_d <= v)
1148 THEN
1149 x_scale_rec.qty := ceil_qty;
1150 -- dbms_output.put_line('in ceil IF') ;
1151 ELSE
1152 x_scale_rec.qty := l_im_scale_rec.qty;
1153 -- dbms_output.put_line('in ceil ELSE') ;
1154 END IF;
1155 --END BUG#2901379 PR
1156 -- dbms_output.put_line('x_scale_rec.qty = ' || to_char(x_scale_rec.qty));
1157
1158 x_return_status := FND_API.G_RET_STS_SUCCESS;
1159 RETURN;
1160
1161 --*********************************************************************
1162 --Phase - Round EITHER
1163 --If rounding_direction = 0 for EITHER, call both floor and
1164 --ceil functions and continue with calculations and evaluations.
1165 --*********************************************************************
1166
1167 ELSIF l_im_scale_rec.rounding_direction = 0
1168 THEN
1169 floor_down( a
1170 , l_im_scale_rec.scale_multiple
1171 , floor_qty
1172 , x_return_status );
1173 -- dbms_output.put_line('either floor_qty = ' || to_char(floor_qty));
1174
1175 floor_d := l_im_scale_rec.qty - floor_qty;
1176 -- dbms_output.put_line('either floor_d = ' || to_char(floor_d));
1177
1178 ceil_up( a
1179 , l_im_scale_rec.scale_multiple
1180 , ceil_qty
1181 , x_return_status );
1182 -- dbms_output.put_line('either ceil_qty = ' || to_char(ceil_qty));
1183
1184 ceil_d := ceil_qty - l_im_scale_rec.qty ;
1185 -- dbms_output.put_line('either ceil_d = ' || to_char(ceil_d));
1186
1187 --*********************************************************************
1188 --Determine wheter floor_d and ceil_d values are 0.
1189 --If they are, the scale_qty is on the multiple; no rounding
1190 --required.
1191 --*********************************************************************
1192 IF floor_d = 0 AND ceil_d = 0
1193 THEN
1194 -- dbms_output.put_line('floor_d and ceil_d = 0');
1195 x_scale_rec.qty := l_im_scale_rec.qty;
1196 -- dbms_output.put_line('x_scale_rec.qty = ' || to_char(x_scale_rec.qty));
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198 RETURN;
1199 -- dbms_output.put_line('test to see if hits message after RETURN');
1200 END IF;
1201
1202 --*********************************************************************
1203 --Since the goal is to round to the closest multiple, a
1204 --determination must be made as to whether the floor_d is less
1205 --than the ceil_d.
1206 --*********************************************************************
1207 IF floor_d < ceil_d
1208 THEN
1209 -- dbms_output.put_line('floor_d < ceil_d ');
1210 --*********************************************************************
1211 --Rounding down not permitted if the difference between the
1212 --initial scaled qty of the item and the floor_qty is greater
1213 --than the variance value(v) or if the floor_qty is 0.
1214 --*********************************************************************
1215 --BEGIN BUG#2901379PR
1216 --Modified the condition to accommodate the NULL for the scale rounding variance.
1217 IF (v IS NULL OR floor_d <= v) AND floor_qty <> 0
1218 THEN
1219 x_scale_rec.qty := floor_qty;
1220 -- dbms_output.put_line('in IF for floor_d <= v or floor_qty<>0 ') ;
1221 ELSE
1222 x_scale_rec.qty := l_im_scale_rec.qty;
1223 -- dbms_output.put_line('in ELSE for floor_d > v or floor_qty=0 ') ;
1224 END IF;
1225 --END BUG#2901379 PR
1226 -- dbms_output.put_line('x_scale_rec.qty = ' || to_char(x_scale_rec.qty));
1227 x_return_status := FND_API.G_RET_STS_SUCCESS;
1228 RETURN;
1229
1230 ELSE
1231 -- dbms_output.put_line('ceil_d <= floor_d');
1232 --*********************************************************************
1233 --Rounding UP not permitted if the difference between the
1234 --ceil_qty and the initial scaled qty of the item is greater
1235 --than the variance value(v).
1236 --*********************************************************************
1237 --BEGIN BUG#2901379 PR
1238 --Modified the condition to accommodate the NULL for the scale rounding variance.
1239 IF (v IS NULL OR ceil_d <= v)
1240 THEN
1241 x_scale_rec.qty := ceil_qty;
1242 -- dbms_output.put_line('in ceil IF if ceild_d <= v') ;
1243 ELSE
1244 x_scale_rec.qty := l_im_scale_rec.qty;
1245 -- dbms_output.put_line('in ceil ELSE if ceild_d >v') ;
1246 END IF;
1247 --END BUG#2901379 PR
1248 -- dbms_output.put_line('x_scale_rec.qty = ' || to_char(x_scale_rec.qty));
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250 RETURN;
1251 END IF;
1252
1253 END IF; --End Rounding Direction Evaluation
1254 EXCEPTION
1255 WHEN OTHERS
1256 THEN x_return_status := FND_API.G_RET_STS_ERROR;
1257
1258 END integer_multiple_scale;
1259
1260
1261 /**********************************************************************
1262 *
1263 * PROCEDURE: floor_down
1264 * PURPOSE: Determines the floor value of the inital scaled qty.
1265 * AUTHOR: Elizabeth Chen
1266 *
1267 * HISTORY:
1268 * =======
1269 * Elizabeth Chen Created.
1270 *
1271 * V.Anitha 5-MAR-2004 BUG#3018432
1272 * Datatype of p_scale_multiple changed to NUMBER from
1273 * PLS_INTEGER to store scale multiple value in decimals also.
1274 * S.Sriram 15Jun2004 Bug# 3680537
1275 * The qty. should first be rounded off to 9 digits before
1276 * integer scaling is done.
1277 **********************************************************************/
1278
1279 /**********************************************************************
1280 *
1281 * OVERVIEW:
1282 * =========
1283 *
1284 The floor_down function accepts the dividend value (a) of the
1285 initial scaled qty and the scale_multiple. From this value, the
1286 floor function determines the previous integer down from this
1287 dividend value. Once done, the scale multiple is applied to the
1288 initial floored value thereby returning the floor_qty of the
1289 initial scaled qty.
1290
1291 *********************************************************************/
1292
1293 PROCEDURE floor_down
1294 ( p_a IN NUMBER
1295 , p_scale_multiple IN NUMBER -- BUG#3018432
1296 , x_floor_qty OUT NOCOPY NUMBER
1297 , x_return_status OUT NOCOPY VARCHAR2
1298 )
1299 IS
1300
1301 l_floor_qty NUMBER := 0;
1302 l_p_a NUMBER := 0;
1303
1304 BEGIN
1305 l_p_a := ROUND(p_a,9);
1306 -- dbms_output.put_line('in floor_down function');
1307 l_floor_qty := floor(l_p_a);
1308 -- dbms_output.put_line('init floor_qty = ' || to_char(l_floor_qty));
1309
1310 x_floor_qty :=l_floor_qty * p_scale_multiple;
1311 -- dbms_output.put_line('x_floor_qty send back to call= ' || to_char(x_floor_qty));
1312
1313 END floor_down;
1314
1315
1316 /*********************************************************************
1317 CEIL_UP
1318
1319 /**********************************************************************
1320 *
1321 * PROCEDURE: ceil_up
1322 * PURPOSE: Determines the ceil value of the inital scaled qty.
1323 * AUTHOR: Elizabeth Chen
1324 *
1325 * HISTORY:
1326 * =======
1327 * Elizabeth Chen Created.
1328 *
1329 * V.Anitha 5-MAR-2004 BUG#3018432
1330 * Datatype of p_scale_multiple changed to NUMBER from
1331 * PLS_INTEGER to store scale multiple value in decimals also.
1332 * S.Sriram 15Jun2004 Bug# 3680537
1333 * The qty. should first be rounded off to 9 digits before
1334 * integer scaling is done.
1335 **********************************************************************/
1336
1337 /**********************************************************************
1338 *
1339 * OVERVIEW:
1340 * =========
1341 *
1342 The ceil_up function accepts the dividend value (a) of the
1343 initial scaled qty and the scale_multiple. From this value, the
1344 ceil function determines the next integer up from this
1345 dividend value. Once done, the scale multiple is applied to the
1346 initial ceiled value thereby returning the ceil_qty of the
1347 initial scaled qty.
1348
1349 *********************************************************************/
1350
1351 PROCEDURE ceil_up
1352 ( p_a IN NUMBER
1353 , p_scale_multiple IN NUMBER -- BUG#3018432
1354 , x_ceil_qty OUT NOCOPY NUMBER
1355 , x_return_status OUT NOCOPY VARCHAR2
1356 )
1357 IS
1358
1359 l_ceil_qty NUMBER := 0;
1360 l_p_a NUMBER := 0;
1361
1362 BEGIN
1363 l_p_a := ROUND(p_a,9);
1364 -- dbms_output.put_line('in ceil_up function');
1365 l_ceil_qty := ceil(l_p_a);
1366 -- dbms_output.put_line('init ceil_qty = ' || to_char(l_ceil_qty));
1367
1368 x_ceil_qty :=l_ceil_qty * p_scale_multiple;
1369 -- dbms_output.put_line('x_ceil_qty send back to call= ' || to_char(x_ceil_qty));
1370
1371 END ceil_up;
1372 --*********************************************************************
1373
1374 /**********************************************************************
1375 *
1376 * PROCEDURE: scale
1377 * PURPOSE: Wrapper for formula scaling.
1378 * AUTHOR: Chandrashekar Reddy
1379 *
1380 * HISTORY:
1381 * =======
1382 * Chandrashekar Reddy Created.
1383 * Jeff Baird 15-Sep-2004 Bug #3890191 Added l_scale_out_tab.
1384 *
1385 **********************************************************************/
1386 PROCEDURE scale
1387 ( p_fm_matl_dtl_tab IN fm_matl_dtl_tab
1388 , p_orgn_id IN NUMBER
1389 , p_scale_factor IN NUMBER
1390 , p_primaries IN VARCHAR2
1391 , x_fm_matl_dtl_tab OUT NOCOPY fm_matl_dtl_tab
1392 , x_return_status OUT NOCOPY VARCHAR2
1393 )
1394 IS
1395 l_row_count NUMBER;
1396 l_row_number NUMBER;
1397 l_scale_tab scale_tab;
1398 l_scale_out_tab scale_tab;
1399 -- Bug #3890191 (JKB) Added l_scale_out_tab above.
1400 BEGIN
1401 l_row_count := p_fm_matl_dtl_tab.count;
1402 FOR l_row_number IN 1 .. l_row_count
1403 LOOP
1404 -- NPD Conv. Use inventory_item_id and detail_uom instead of item_id and item_um
1405 l_scale_tab(l_row_number).inventory_item_id := p_fm_matl_dtl_tab(l_row_number).inventory_item_id;
1406 l_scale_tab(l_row_number).detail_uom := p_fm_matl_dtl_tab(l_row_number).detail_uom;
1407 l_scale_tab(l_row_number).qty := p_fm_matl_dtl_tab(l_row_number).qty;
1408 l_scale_tab(l_row_number).line_type := p_fm_matl_dtl_tab(l_row_number).line_type;
1409 l_scale_tab(l_row_number).line_no := p_fm_matl_dtl_tab(l_row_number).line_no;
1410 l_scale_tab(l_row_number).scale_type := p_fm_matl_dtl_tab(l_row_number).scale_type;
1411 l_scale_tab(l_row_number).scale_rounding_variance := p_fm_matl_dtl_tab(l_row_number).scale_rounding_variance;
1412 l_scale_tab(l_row_number).scale_multiple := p_fm_matl_dtl_tab(l_row_number).scale_multiple;
1413 l_scale_tab(l_row_number).contribute_yield_ind := p_fm_matl_dtl_tab(l_row_number).contribute_yield_ind;
1414 END LOOP;
1415 scale( l_scale_tab
1416 , p_orgn_id -- NPD Conv.
1417 , p_scale_factor
1418 , p_primaries
1419 , l_scale_out_tab
1420 , x_return_status
1421 );
1422 -- Bug #3890191 (JKB) Added l_scale_out_tab above.
1423 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1424 THEN
1425 x_fm_matl_dtl_tab := p_fm_matl_dtl_tab;
1426 FOR l_row_number in 1 .. l_row_count
1427 LOOP
1428 x_fm_matl_dtl_tab(l_row_number).qty := l_scale_out_tab(l_row_number).qty;
1429 -- Bug #3890191 (JKB) Added l_scale_out_tab above.
1430 END LOOP;
1431 END IF;
1432 END scale;
1433
1434 /**********************************************************************
1435 *
1436 * PROCEDURE: theoretical_yield
1437 * PURPOSE: To scale the formula/batch based on theoretical yield
1438 * AUTHOR: Shrikant Nene
1439 *
1440 * HISTORY:
1441 * =======
1442 * Shrikant Nene Created.
1443 *
1444 **********************************************************************/
1445 PROCEDURE theoretical_yield
1446 ( p_scale_tab IN scale_tab
1447 , p_orgn_id IN NUMBER
1448 , p_scale_factor IN NUMBER
1449 , x_scale_tab OUT NOCOPY scale_tab
1450 , x_return_status OUT NOCOPY VARCHAR2
1451 )
1452 IS
1453 l_scale_tab scale_tab ;
1454 x_scale_rec scale_rec ;
1455
1456 l_qty NUMBER;
1457 l_tab_size NUMBER;
1458 --l_conv_uom sy_uoms_mst.um_code%TYPE;
1459 l_conv_uom mtl_units_of_measure.uom_code%TYPE;
1460 l_type_0_Y_outputs NUMBER:= 0;
1461 l_type_1_Y_outputs NUMBER:= 0;
1462 l_type_2_Y_outputs NUMBER:= 0;
1463 l_type_0_Y_inputs NUMBER:= 0;
1464 l_type_1_Y_inputs NUMBER:= 0;
1465 l_type_2_Y_inputs NUMBER:= 0;
1466 l_fixed_input_qty NUMBER:= 0;
1467 l_fixed_output_qty NUMBER:= 0;
1468 l_proportional_input_qty NUMBER:= 0;
1469 l_proportional_output_qty NUMBER:= 0;
1470 l_input_qty NUMBER:= 0;
1471 l_input_total NUMBER:= 0;
1472 l_output_total NUMBER:= 0;
1473 scale_factor NUMBER:= 0;
1474
1475 --**********************************************************************
1476
1477 BEGIN
1478
1479 x_return_status := FND_API.G_RET_STS_SUCCESS;
1480
1481
1482 l_tab_size := p_scale_tab.count;
1483
1484 l_scale_tab := p_scale_tab;
1485 x_scale_tab := p_scale_tab;
1486
1487
1488 FOR l_row_count IN 1 .. l_tab_size
1489 LOOP
1490 --*************************************************************
1491 --Initial data validation. If the data is not correct,
1492 --exit from scaling with an error message.
1493 --*************************************************************
1494 IF (p_scale_tab(l_row_count).inventory_item_id IS NULL
1495 OR p_scale_tab(l_row_count).detail_uom IS NULL)
1496 THEN
1497 fnd_message.set_name('GME','GME_INVALID_ITEM_OR_ITEM_UM');
1498 fnd_msg_pub.add;
1499 x_return_status := FND_API.G_RET_STS_ERROR;
1500 -- dbms_output.put_line('INVALID_ITEM_AND_OR_ITEM_UM');
1501 END IF;
1502 IF (p_scale_tab(l_row_count).line_type IS NULL) OR
1503 (p_scale_tab(l_row_count).line_type NOT IN (-1,1,2))
1504 THEN
1505 fnd_message.set_name('GME','GME_INVALID_LINE_TYPE');
1506 fnd_msg_pub.add;
1507 x_return_status := FND_API.G_RET_STS_ERROR;
1508 -- dbms_output.put_line('INVALID_LINE_TYPE');
1509 END IF;
1510 IF (p_scale_tab(l_row_count).line_no IS NULL
1511 OR p_scale_tab(l_row_count).line_no <= 0 )
1512 THEN
1513 fnd_message.set_name('GME','GME_INVALID_LINE_NUMBER');
1514 fnd_msg_pub.add;
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516 -- dbms_output.put_line('INVALID_LINE_NUMBER');
1517 END IF ;
1518 IF (p_scale_tab(l_row_count).scale_type IS NULL) OR
1519 (p_scale_tab(l_row_count).scale_type NOT IN (0,1,2))
1520 THEN
1521 fnd_message.set_name('GME','GME_INVALID_SCALE_TYPE');
1522 fnd_msg_pub.add;
1523 x_return_status := FND_API.G_RET_STS_ERROR;
1524 -- dbms_output.put_line('INVALID_SCALE_TYPE');
1525 END IF;
1526 IF (p_scale_tab(l_row_count).contribute_yield_ind IS NULL ) OR
1527 (p_scale_tab(l_row_count).contribute_yield_ind NOT IN ('Y','N'))
1528 THEN
1529 fnd_message.set_name('GME','GME_INVALID_CONTRIBUTE_YIELD');
1530 fnd_msg_pub.add;
1531 x_return_status := FND_API.G_RET_STS_ERROR;
1532 -- dbms_output.put_line('INVALID_CONTRIBUTE_YIELD_IND');
1533 END IF;
1534 IF (p_scale_tab(l_row_count).scale_multiple < 0 )
1535 THEN
1536 fnd_message.set_name('GME','GME_INVALID_SCALE_MULTIPLE');
1537 fnd_msg_pub.add;
1538 x_return_status := FND_API.G_RET_STS_ERROR;
1539 -- dbms_output.put_line('INVALID_SCALE_MULTIPLE');
1540 END IF;
1541 IF ((p_scale_tab(l_row_count).scale_rounding_variance < 0)
1542 OR (p_scale_tab(l_row_count).scale_rounding_variance > 1))
1543 THEN
1544 fnd_message.set_name('GME','GME_INVALID_SCALE_VARIANCE');
1545 fnd_msg_pub.add;
1546 x_return_status := FND_API.G_RET_STS_ERROR;
1547 -- dbms_output.put_line('INVALID_SCALE_ROUNDING_VARIANCE');
1548 END IF;
1549 IF (p_scale_tab(l_row_count).rounding_direction NOT IN (0,1,2))
1550 THEN
1551 fnd_message.set_name('GME','GME_INVALID_ROUNDING_DIRECTION');
1552 fnd_msg_pub.add;
1553 x_return_status := FND_API.G_RET_STS_ERROR;
1554 -- dbms_output.put_line('INVALID_ROUNDING_DIRECTION');
1555 END IF;
1556
1557 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1558 THEN
1559 RETURN;
1560 END IF;
1561
1562 --**********************************************************************
1563 --Here determining the scale type of the outputs and whether they
1564 --contribute to yield. Increment appropriate counter.
1565 --**********************************************************************
1566 IF p_scale_tab(l_row_count).line_type IN (1,2)
1567 THEN
1568 -- dbms_output.put_line('determining scale type of output ');
1569 IF p_scale_tab(l_row_count).scale_type = 0 AND
1570 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
1571 THEN
1572 l_type_0_Y_outputs := l_type_0_Y_outputs + 1;
1573 ELSIF p_scale_tab(l_row_count).scale_type = 1 AND
1574 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
1575 THEN
1576 l_type_1_Y_outputs := l_type_1_Y_outputs+1;
1577 ELSIF p_scale_tab(l_row_count).scale_type = 2 AND
1578 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
1579 THEN
1580 l_type_2_Y_outputs := l_type_2_Y_outputs+1;
1581 ELSE /* No need to get the non contributing to yield product/byproduct */
1582 -- Use this branch for future scale types.
1583 NULL;
1584 END IF;
1585 l_output_total := l_output_total +1 ;
1586
1587 --**********************************************************************
1588 --Here determining the scale type of the inputs and whether they
1589 --contribute to yield. Increment appropriate counter.
1590 --**********************************************************************
1591
1592 ELSIF p_scale_tab(l_row_count).line_type = -1
1593 THEN
1594 -- dbms_output.put_line('determining scale type of input ');
1595 IF p_scale_tab(l_row_count).scale_type = 0 AND
1596 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
1597 THEN
1598 l_type_0_Y_inputs := l_type_0_Y_inputs + 1;
1599 ELSIF p_scale_tab(l_row_count).scale_type = 1 AND
1600 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
1601 THEN
1602 l_type_1_Y_inputs := l_type_1_Y_inputs + 1;
1603 ELSIF p_scale_tab(l_row_count).scale_type = 2 AND
1604 p_scale_tab(l_row_count).contribute_yield_ind = 'Y'
1605 THEN
1606 l_type_2_Y_inputs := l_type_2_Y_inputs + 1;
1607 ELSE /* No need to get the non contributing to yield ingredients*/
1608 -- Use this branch for future scale types.
1609 NULL;
1610 END IF;
1611 l_input_total := l_input_total +1 ;
1612 END IF;
1613 END LOOP;
1614
1615 IF (l_type_1_Y_outputs + l_type_2_Y_outputs = 0) THEN
1616 /*fnd_message.set_name('GME','NO_YIELD_CONTR_SECONDARIES');
1617 fnd_msg_pub.add;
1618 -- dbms_output.put_line('NO_YIELD_CONTR_SECONDARIES_EXIST');
1619 x_return_status := FND_API.G_RET_STS_ERROR;*/
1620 RETURN;
1621 END IF;
1622
1623 -- dbms_output.put_line('Phase I: calling uom conv ');
1624
1625 get_fm_yield_type(p_orgn_id, l_conv_uom, x_return_status); -- NPD Convergence
1626
1627 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1628 THEN
1629 RETURN;
1630 END IF;
1631
1632 FOR l_row_count IN 1 .. l_tab_size
1633 LOOP
1634
1635 IF l_scale_tab(l_row_count).scale_type IN (0,1,2) AND
1636 l_scale_tab(l_row_count).contribute_yield_ind='Y'
1637 THEN
1638
1639 l_qty := INV_CONVERT.inv_um_convert(item_id => p_scale_tab(l_row_count).inventory_item_id
1640 ,precision => 5
1641 ,from_quantity => p_scale_tab(l_row_count).qty
1642 ,from_unit => p_scale_tab(l_row_count).detail_uom
1643 ,to_unit => l_conv_uom
1644 ,from_name => NULL
1645 ,to_name => NULL);
1646
1647 -- dbms_output.put_line('in the loop '||l_row_count||' line '||l_scale_tab(l_row_count).inventory_item_id||' Qty '||l_qty);
1648
1649 IF l_qty < 0
1650 THEN
1651 -- Report UOM failure
1652 -- dbms_output.put_line('x_return_status = ' || x_return_status);
1653 fnd_message.set_name('GMI','GMI_OPM_UOM_NOT_FOUND');
1654 fnd_msg_pub.add;
1655 -- dbms_output.put_line('GMI UOM_CONVERSION_ERROR');
1656 x_return_status := FND_API.G_RET_STS_ERROR;
1657 RETURN;
1658 ELSE
1659 l_scale_tab(l_row_count).qty := l_qty;
1660 l_scale_tab(l_row_count).detail_uom := l_conv_uom;
1661 x_return_status := FND_API.G_RET_STS_SUCCESS;
1662
1663 -- dbms_output.put_line('x_return_status = ' || x_return_status);
1664
1665 --**********************************************************************
1666 --PHASE I:
1667 --If prod/byprod has a fixed scale type(scale_type=0) and contributes
1668 --to yield, add its qty to fixed qty variable.
1669 --If prod/byprod has a scalable scale type(scale_type=1,2) and contributes
1670 --to yield, add its qty to proportional qty variable.
1671 --**********************************************************************
1672
1673 -- dbms_output.put_line('Phase I:increment fix/proportional output qty counter');
1674 IF l_scale_tab(l_row_count).line_type IN (1,2) AND
1675 l_scale_tab(l_row_count).contribute_yield_ind='Y'
1676 THEN
1677 IF l_scale_tab(l_row_count).scale_type = 0
1678 THEN
1679 l_fixed_output_qty := l_fixed_output_qty + l_scale_tab(l_row_count).qty;
1680 -- dbms_output.put_line('l_fixed_output_qty= '||to_char(l_fixed_output_qty));
1681 ELSIF l_scale_tab(l_row_count).scale_type IN (1,2) AND
1682 l_scale_tab(l_row_count).contribute_yield_ind='Y'
1683 THEN
1684 l_proportional_output_qty := l_proportional_output_qty +
1685 l_scale_tab(l_row_count).qty;
1686 -- dbms_output.put_line('l_proportional_output_qty= '||to_char(l_proportional_output_qty));
1687 END IF;
1688
1689 --**********************************************************************
1690 --PHASE I:
1691 --If ingredient has a fixed scale type(scale_type=0) and contributes
1692 --to yield, add its qty to fixed qty variable.
1693 --If ingredient has a scalable scale type(scale_type=1,2) and contributes
1694 --to yield, add its qty to proportional qty variable.
1695 --**********************************************************************
1696 ELSE
1697 -- dbms_output.put_line('Phase I:increment fix/proportional INPUT qty counter');
1698 IF l_scale_tab(l_row_count).scale_type = 0 AND
1699 l_scale_tab(l_row_count).contribute_yield_ind='Y'
1700 THEN
1701 l_fixed_input_qty := l_fixed_input_qty + l_scale_tab(l_row_count).qty;
1702 -- dbms_output.put_line('l_fixed_input_qty= '||to_char(l_fixed_input_qty));
1703
1704 ELSIF l_scale_tab(l_row_count).scale_type IN (1,2) AND
1705 l_scale_tab(l_row_count).contribute_yield_ind='Y'
1706 THEN
1707 l_proportional_input_qty := l_proportional_input_qty +
1708 l_scale_tab(l_row_count).qty;
1709 -- dbms_output.put_line('l_proportional_input_qty= '||to_char(l_proportional_input_qty));
1710 END IF;
1711 END IF;
1712 END IF;
1713 END IF;
1714 END LOOP;
1715
1716
1717 l_input_qty := p_scale_factor * (l_proportional_input_qty + l_fixed_input_qty);
1718 IF l_fixed_output_qty >= l_input_qty THEN
1719 -- dbms_output.put_line('scale fact '||p_scale_factor||
1720 -- 'prop inp '||l_proportional_input_qty||
1721 -- 'fixed inp '||l_fixed_input_qty||
1722 -- 'fixed output '||l_fixed_output_qty);
1723 fnd_message.set_name('GME','GME_FIX_ITEM_GTR_YIELD');
1724 fnd_msg_pub.add;
1725 -- dbms_output.put_line('Fixed items are greater than desired yield');
1726 x_return_status := FND_API.G_RET_STS_ERROR;
1727 RETURN;
1728 END IF;
1729
1730 IF l_proportional_output_qty <= 0 THEN
1731 /* If ther are no scalable outputs defined then spread the inputs equaly */
1732 l_proportional_output_qty := l_type_1_Y_outputs + l_type_2_Y_outputs;
1733 END IF;
1734
1735 scale_factor := (l_input_qty - l_fixed_output_qty )/ l_proportional_output_qty;
1736 -- dbms_output.put_line('Scale factor is '||scale_factor);
1737
1738 FOR l_row_count IN 1 .. l_tab_size
1739 LOOP
1740 IF l_scale_tab(l_row_count).scale_type IN (1,2) AND
1741 l_scale_tab(l_row_count).contribute_yield_ind='Y' AND
1742 l_scale_tab(l_row_count).line_type IN (1,2) THEN
1743
1744 IF l_proportional_output_qty <= 0 THEN
1745 x_scale_tab(l_row_count).qty := scale_factor;
1746 ELSE
1747 x_scale_tab(l_row_count).qty := scale_factor * x_scale_tab(l_row_count).qty;
1748 END IF;
1749 END IF;
1750 END LOOP;
1751 RETURN;
1752
1753 END theoretical_yield;
1754
1755 END gmd_common_scale;
[Home] [Help]