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