[Home] [Help]
PACKAGE BODY: APPS.GMD_SPREAD_CALCULATE_PKG
Source
1 PACKAGE BODY GMD_SPREAD_CALCULATE_PKG AS
2 /* $Header: GMDSPDCB.pls 120.9 2011/11/30 15:44:57 rnalla ship $ */
3
4 /*##############################################################
5 # NAME
6 # calculate
7 # SYNOPSIS
8 # proc calculate
9 # DESCRIPTION
10 # This procedure calculates the values for the products
11 # by performing the rollups based on data type.
12 ###############################################################*/
13
14 PROCEDURE calculate (V_entity_id IN NUMBER,
15 V_orgn_id IN NUMBER,
16 X_return_status OUT NOCOPY VARCHAR2) IS
17 CURSOR Cur_get_prod IS
18 SELECT d.*, p.tech_parm_name, p.data_type, p.lm_unit_code,p.sort_seq,p.tech_parm_id
19 FROM gmd_material_details_gtmp d, gmd_technical_parameter_gtmp p
20 WHERE d.entity_id = V_entity_id
21 AND p.data_type > 3 AND (p.data_type = 4 OR d.line_type = 1);
22 l_count NUMBER;
23 j NUMBER;
24 l_data VARCHAR2(2000);
25 l_expression VARCHAR2(2000);
26 l_density VARCHAR2(2000);
27 BEGIN
28 DELETE FROM GMD_SPREAD_ERRORS_GTMP;
29 FOR l_rec IN Cur_get_prod LOOP
30 FND_MSG_PUB.INITIALIZE;
31 IF ((l_rec.data_type = 4) OR (l_rec.line_type = 1 AND l_rec.data_type = 11)) THEN
32 evaluate_expression (V_entity_id => V_entity_id,
33 V_line_id => l_rec.line_id,
34 V_parm_name => l_rec.tech_parm_name,
35 V_parm_id => l_rec.tech_parm_id,
36 V_sort_seq => l_rec.sort_seq,
37 X_expression => l_expression,
38 X_return_status => X_return_status);
39 ELSIF (l_rec.data_type = 5 OR l_rec.data_type = 12) THEN
40 rollup_wt_pct (V_entity_id => V_entity_id,
41 V_line_id => l_rec.line_id,
42 V_parm_name => l_rec.tech_parm_name,
43 V_parm_id => l_rec.tech_parm_id,
44 V_sort_seq => l_rec.sort_seq,
45 X_return_status => X_return_status);
46 ELSIF l_rec.data_type IN (6, 7) THEN
47 rollup_vol_pct(V_entity_id => V_entity_id,
48 V_orgn_id => V_orgn_id,
49 V_line_id => l_rec.line_id,
50 V_parm_name => l_rec.tech_parm_name,
51 V_parm_id => l_rec.tech_parm_id,
52 V_sort_seq => l_rec.sort_seq,
53 X_return_status => X_return_status);
54 ELSIF l_rec.data_type = 8 THEN
55 rollup_cost_update(V_entity_id => V_entity_id,
56 V_line_id => l_rec.line_id,
57 V_parm_name => l_rec.tech_parm_name,
58 V_parm_id => l_rec.tech_parm_id,
59 V_primary_qty => l_rec.primary_qty,
60 V_sort_seq => l_rec.sort_seq,
61 X_return_status => X_return_status);
62 ELSIF l_rec.data_type = 9 THEN
63 rollup_equiv_wt (V_entity_id => V_entity_id,
64 V_line_id => l_rec.line_id,
65 V_parm_name => l_rec.tech_parm_name,
66 V_parm_id => l_rec.tech_parm_id,
67 V_unit_code => l_rec.lm_unit_code,
68 V_orgn_id => V_orgn_id,
69 V_sort_seq => l_rec.sort_seq,
70 X_return_status => X_return_status);
71 ELSIF l_rec.data_type = 10 THEN
72 rollup_update (V_entity_id => V_entity_id,
73 V_line_id => l_rec.line_id,
74 V_parm_name => l_rec.tech_parm_name,
75 V_parm_id => l_rec.tech_parm_id,
76 V_sort_seq => l_rec.sort_seq,
77 X_return_status => X_return_status);
78 ELSE
79 NULL;
80 END IF;
81 l_count := FND_MSG_PUB.COUNT_MSG;
82 FOR i IN 1 .. l_count LOOP
83 FND_MSG_PUB.GET(P_msg_index => i, P_data => l_data, p_msg_index_out => j, P_encoded => 'F');
84 INSERT INTO GMD_SPREAD_ERRORS_GTMP
85 (ENTITY_ID,LINE_ID,LINE_TYPE,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,LOT_NUMBER,
86 TECH_PARM_ID,TECH_PARM_NAME,ERROR_MESSAGE,EXPRESSION_TYPE)
87 VALUES
88 (V_entity_id,l_rec.line_id,l_rec.line_type,l_rec.inventory_item_id,
89 l_rec.concatenated_segments,l_rec.lot_number,
90 l_rec.tech_parm_id,l_rec.tech_parm_name,l_data,l_expression);
91 END LOOP;
92 END LOOP;
93 END calculate;
94
95 /*##############################################################
96 # NAME
97 # rollup_wt_pct
98 # SYNOPSIS
99 # proc rollup_wt_pct
100 # DESCRIPTION
101 # This procedure gets the values for the products for the
102 # by performing the weight rollup.
103 ###############################################################*/
104
105 PROCEDURE rollup_wt_pct (V_entity_id IN NUMBER,
106 V_line_id IN NUMBER,
107 V_parm_name IN VARCHAR2,
108 V_parm_id IN NUMBER,
109 V_sort_seq IN NUMBER,
110 X_return_status OUT NOCOPY VARCHAR2) IS
111
112 CURSOR Cur_get_line (V_line_type NUMBER) IS
113 SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
114 FROM
115 (SELECT qty_mass weight, qty_mass * value weightpct
116 FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
117 WHERE line_type <> 1
118 AND (line_type = V_line_type OR line_type = 3)
119 AND d.line_id = t.line_id (+)
120 AND d.entity_id = t.entity_id (+)
121 AND d.entity_id = V_entity_id
122 AND t.tech_parm_id (+) = V_parm_id
123 AND rollup_ind = 1
124 AND EXISTS (SELECT 1
125 FROM gmd_material_details_gtmp d1
126 WHERE line_type = V_line_type
127 AND d1.parent_line_id = d.parent_line_id));
128 X_ingred_wt NUMBER ;
129 X_ingred_wtpct NUMBER ;
130 X_byprod_wt NUMBER ;
131 X_byprod_wtpct NUMBER ;
132 X_rollup NUMBER;
133 BEGIN
134 X_return_status := FND_API.g_ret_sts_success;
135
136 OPEN Cur_get_line (-1);
137 FETCH Cur_get_line INTO X_ingred_wt, X_ingred_wtpct;
138 CLOSE Cur_get_line;
139
140 OPEN Cur_get_line (2);
141 FETCH Cur_get_line INTO X_byprod_wt, X_byprod_wtpct;
142 CLOSE Cur_get_line;
143
144 IF (X_ingred_wt - X_byprod_wt) <> 0 THEN
145 X_rollup := (X_ingred_wtpct - X_byprod_wtpct) / (X_ingred_wt - X_byprod_wt);
146 END IF;
147
148
149 UPDATE gmd_technical_data_gtmp
150 SET value = X_rollup, num_data = X_rollup
151 WHERE line_id = V_line_id
152 AND tech_parm_id = V_parm_id;
153
154 IF SQL%NOTFOUND THEN
155 INSERT INTO GMD_TECHNICAL_DATA_GTMP
156 (ENTITY_ID,
157 LINE_ID,
158 SORT_SEQ,
159 TECH_PARM_NAME,
160 TECH_PARM_ID,
161 VALUE,
162 NUM_DATA)
163 VALUES
164 (V_entity_id,
165 V_line_id,
166 V_sort_seq,
167 V_parm_name,
168 V_parm_id,
169 X_rollup,
170 X_rollup);
171 END IF;
172
173 IF X_rollup IS NULL THEN
174 gmd_api_grp.log_message('GMD_WEIGHT_CALCULATE','V_PARM_NAME',V_parm_name);
175 X_return_status := FND_API.g_ret_sts_error;
176 END IF;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_CALCULATE_PKG', 'Rollup_Wt_Pct');
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 END rollup_wt_pct;
183
184 /*##############################################################
185 # NAME
186 # evaluate_expression
187 # SYNOPSIS
188 # proc evaluate_expression
189 # DESCRIPTION
190 # This procedure gets the values for the products for the
191 # by performing the expression evaluation.
192 ###############################################################*/
193
194 PROCEDURE evaluate_expression (V_entity_id IN NUMBER,
195 V_line_id IN NUMBER,
196 V_parm_name IN VARCHAR2,
197 V_parm_id IN NUMBER,
198 V_sort_seq IN NUMBER,
199 X_expression OUT NOCOPY VARCHAR2,
200 X_return_status OUT NOCOPY VARCHAR2) IS
201 X_rollup NUMBER;
202 l_param_id NUMBER;
203 l_value NUMBER;
204 BEGIN
205 X_return_status := FND_API.g_ret_sts_success;
206 gmd_expression_util.evaluate_expression (p_entity_id => v_entity_id,
207 p_line_id => v_line_id,
208 p_tech_parm_id => v_parm_id,
209 X_value => l_value,
210 X_expression => X_expression,
211 x_return_status => x_return_status);
212
213 UPDATE gmd_technical_data_gtmp
214 SET value = l_value, num_data = l_value
215 WHERE line_id = V_line_id
216 AND tech_parm_id = V_parm_id;
217
218 IF SQL%NOTFOUND THEN
219 INSERT INTO GMD_TECHNICAL_DATA_GTMP
220 (ENTITY_ID,
221 LINE_ID,
222 SORT_SEQ,
223 TECH_PARM_NAME,
224 TECH_PARM_ID,
225 VALUE,
226 NUM_DATA)
227 VALUES
228 (V_entity_id,
229 V_line_id,
230 V_sort_seq,
231 V_parm_name,
232 V_parm_id,
233 l_value,
234 l_value);
235 END IF;
236
237 IF X_return_status <> FND_API.g_ret_sts_success THEN
238 gmd_api_grp.log_message('GMD_EXPRESSION_CALCULATE','V_PARM_NAME', V_parm_name);
239 X_return_status := FND_API.g_ret_sts_error;
240 END IF;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_CALCULATE_PKG', 'Evaluate_Expression');
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 END evaluate_expression;
247
248 /*##############################################################
249 # NAME
250 # rollup_vol_pct
251 # SYNOPSIS
252 # proc rollup_vol_pct
253 # DESCRIPTION
254 # This procedure gets the values for the products for the
255 # by performing the voulme rollup.
256 ###############################################################*/
257
258 PROCEDURE rollup_vol_pct (V_entity_id IN NUMBER,
259 V_orgn_id IN NUMBER,
260 V_line_id IN NUMBER,
261 V_parm_name IN VARCHAR2,
262 V_parm_id IN NUMBER,
263 V_sort_seq IN NUMBER,
264 X_return_status OUT NOCOPY VARCHAR2) IS
265
266 CURSOR Cur_get_line1 (V_line_type NUMBER) IS
267 SELECT SUM(volume), SUM(volumepct)
268 FROM
269 (SELECT qty_vol volume, qty_vol * value volumepct
270 FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
271 WHERE line_type <> 1
272 AND (line_type = V_line_type OR line_type = 3)
273 AND d.line_id = t.line_id (+)
274 AND d.entity_id = t.entity_id (+)
275 AND d.entity_id = V_entity_id
276 AND t.tech_parm_id (+) = V_parm_id
277 AND rollup_ind = 1
278 AND EXISTS (SELECT 1
279 FROM gmd_material_details_gtmp d1
280 WHERE line_type = V_line_type
281 AND d1.parent_line_id = d.parent_line_id));
282 CURSOR Cur_std_um (V_uom_type VARCHAR2) IS
283 SELECT uom_code
284 FROM mtl_units_of_measure
285 WHERE uom_class = V_uom_type;
286
287 X_ingred_vol NUMBER ;
288 X_ingred_volpct NUMBER ;
289 X_byprod_vol NUMBER ;
290 X_byprod_volpct NUMBER ;
291 X_rollup NUMBER;
292 X_density_parameter VARCHAR2(240);
293 X_mass_uom VARCHAR2(30);
294 X_vol_uom VARCHAR2(30);
295 X_uom_type VARCHAR2(30);
296 L_return_status VARCHAR2(1);
297
298 NO_PARAMETER EXCEPTION;
299 BEGIN
300 X_return_status := FND_API.g_ret_sts_success;
301
302 OPEN Cur_get_line1 (-1);
303 FETCH Cur_get_line1 INTO X_ingred_vol, X_ingred_volpct;
304 CLOSE Cur_get_line1;
305
306 OPEN Cur_get_line1 (2);
307 FETCH Cur_get_line1 INTO X_byprod_vol, X_byprod_volpct;
308 CLOSE Cur_get_line1;
309
310 IF (NVL(X_ingred_vol,0) - NVL(X_byprod_vol,0)) <> 0 THEN
311 X_rollup := (NVL(X_ingred_volpct,0) - NVL(X_byprod_volpct,0)) / (NVL(X_ingred_vol,0) - NVL(X_byprod_vol,0));
312 END IF;
313
314 UPDATE gmd_technical_data_gtmp
315 SET value = X_rollup,
316 num_data = X_rollup
317 WHERE line_id = V_line_id
318 AND tech_parm_id = V_parm_id;
319
320 IF SQL%NOTFOUND THEN
321 INSERT INTO GMD_TECHNICAL_DATA_GTMP
322 (ENTITY_ID,
323 LINE_ID,
324 SORT_SEQ,
325 TECH_PARM_NAME,
326 TECH_PARM_ID,
327 VALUE,
328 NUM_DATA)
329 VALUES
330 (V_entity_id,
331 V_line_id,
332 V_sort_seq,
333 V_parm_name,
334 V_parm_id,
335 X_rollup,
336 X_rollup);
337 END IF;
338
339 IF X_rollup IS NULL THEN
340 gmd_api_grp.log_message('GMD_VOLUME_CALCULATE','V_PARM_NAME', V_parm_name);
341 X_return_status := FND_API.g_ret_sts_error;
342 END IF;
343 gmd_api_grp.fetch_parm_values(P_orgn_id => v_orgn_id,
344 P_parm_name => 'GMD_MASS_UM_TYPE',
345 P_parm_value => X_uom_type,
346 X_return_status => L_return_status);
347 IF (L_return_status <> FND_API.g_ret_sts_success) THEN
348 RAISE NO_PARAMETER;
349 END IF;
350 OPEN Cur_std_um (X_uom_type);
351 FETCH Cur_std_um INTO X_mass_uom;
352 CLOSE Cur_std_um;
353
354 gmd_api_grp.fetch_parm_values(P_orgn_id => v_orgn_id,
355 P_parm_name => 'GMD_VOLUME_UM_TYPE',
356 P_parm_value => X_uom_type,
357 X_return_status => L_return_status);
358 IF (L_return_status <> FND_API.g_ret_sts_success) THEN
359 RAISE NO_PARAMETER;
360 END IF;
361
362 OPEN Cur_std_um (X_uom_type);
363 FETCH Cur_std_um INTO X_vol_uom;
364 CLOSE Cur_std_um;
365 X_density_parameter := FND_PROFILE.VALUE('LM$DENSITY');
366
367 IF (V_parm_name = X_density_parameter) THEN
368 gmd_spread_fetch_pkg.update_line_mass_vol_qty (V_orgn_id => V_orgn_id,
369 V_line_id => V_line_id,
370 V_density_parameter => X_density_parameter,
371 V_mass_uom => X_mass_uom,
372 V_vol_uom => X_vol_uom,
373 X_return_status => X_return_status);
374 END IF;
375 IF x_return_status <> x_return_status THEN
376 X_return_status := x_return_status;
377 END IF;
378
379 EXCEPTION
380 WHEN NO_PARAMETER THEN
381 fnd_msg_pub.add_exc_msg ('GMD', 'GMD_PARM_NOT_FOUND');
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 WHEN OTHERS THEN
384 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_CALCULATE_PKG', 'Rollup_Vol_Pct');
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 END rollup_vol_pct;
387
388 /*##############################################################
389 # NAME
390 # rollup_cost_update
391 # SYNOPSIS
392 # proc rollup_cost_update
393 # DESCRIPTION
394 # This procedure gets the values for the products for the
395 # by performing the cost units rollup and updates the same.
396 ###############################################################*/
397
398 PROCEDURE rollup_cost_update(V_entity_id IN NUMBER,
399 V_line_id IN NUMBER,
400 V_parm_name IN VARCHAR2,
401 V_parm_id IN NUMBER,
402 V_primary_qty IN VARCHAR2,
403 V_sort_seq IN NUMBER,
404 X_return_status OUT NOCOPY VARCHAR2) IS
405 X_rollup_cost NUMBER;
406 BEGIN
407 X_rollup_cost := rollup_cost_units(V_entity_id,V_line_id,V_parm_name,V_parm_id,X_return_status);
408 IF V_primary_qty > 0 THEN
409 X_rollup_cost := X_rollup_cost / V_primary_qty;
410 ELSE
411 X_rollup_cost := 0;
412 END IF;
413 UPDATE gmd_technical_data_gtmp
414 SET value = X_rollup_cost,
415 num_data = X_rollup_cost
416 WHERE line_id = V_line_id
417 AND tech_parm_id = V_parm_id;
418
419 IF SQL%NOTFOUND THEN
420 INSERT INTO GMD_TECHNICAL_DATA_GTMP
421 (ENTITY_ID,
422 LINE_ID,
423 SORT_SEQ,
424 TECH_PARM_NAME,
425 TECH_PARM_ID,
426 VALUE,
427 NUM_DATA)
428 VALUES
429 (V_entity_id,
430 V_line_id,
431 V_sort_seq,
432 V_parm_name,
433 V_parm_id,
434 X_rollup_cost,
435 X_rollup_cost);
436 END IF;
437 END rollup_cost_update;
438
439 /*##############################################################
440 # NAME
441 # rollup_update
442 # SYNOPSIS
443 # proc rollup_update
444 # DESCRIPTION
445 # This procedure gets the values for the products for the
446 # by performing the cost units rollup and updates the same.
447 ###############################################################*/
448
449 PROCEDURE rollup_update (V_entity_id IN NUMBER,
450 V_line_id IN NUMBER,
451 V_parm_name IN VARCHAR2,
452 V_parm_id IN NUMBER,
453 V_sort_seq IN NUMBER,
454 X_return_status OUT NOCOPY VARCHAR2) IS
455 X_rollup_cost NUMBER;
456 BEGIN
457 X_rollup_cost := rollup_cost_units(V_entity_id,V_line_id,V_parm_name,V_parm_id,X_return_status);
458
459 UPDATE gmd_technical_data_gtmp
460 SET value = X_rollup_cost,
461 num_data = X_rollup_cost
462 WHERE line_id = V_line_id
463 AND tech_parm_id = V_parm_id;
464
465 IF SQL%NOTFOUND THEN
466 INSERT INTO GMD_TECHNICAL_DATA_GTMP
467 (ENTITY_ID,
468 LINE_ID,
469 SORT_SEQ,
470 TECH_PARM_NAME,
471 TECH_PARM_ID,
472 VALUE,
473 NUM_DATA)
474 VALUES
475 (V_entity_id,
476 V_line_id,
477 V_sort_seq,
478 V_parm_name,
479 V_parm_id,
480 X_rollup_cost,
481 X_rollup_cost);
482 END IF;
483 END rollup_update;
484
485 /*##############################################################
486 # NAME
487 # rollup_cost_units
488 # SYNOPSIS
489 # proc rollup_cost_units
490 # DESCRIPTION
491 # This procedure gets the values for the products for the
492 # by performing the cost units rollup.
493 ###############################################################*/
494
495 FUNCTION rollup_cost_units (V_entity_id IN NUMBER,
496 V_line_id IN NUMBER,
497 V_parm_name IN VARCHAR2,
498 V_parm_id IN NUMBER,
499 X_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
500
501 CURSOR Cur_get_line2 (V_line_type NUMBER) IS
502 SELECT NVL(SUM(volumepct), 0)
503 FROM
504 (SELECT primary_qty * value volumepct
505 FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
506 WHERE line_type <> 1
507 AND (line_type = V_line_type OR line_type = 3)
508 AND d.line_id = t.line_id (+)
509 AND d.entity_id = t.entity_id (+)
510 AND d.entity_id = V_entity_id
511 AND t.tech_parm_id (+) = V_parm_id
512 AND rollup_ind = 1
513 AND EXISTS (SELECT 1
514 FROM gmd_material_details_gtmp d1
515 WHERE line_type = V_line_type
516 AND d1.parent_line_id = d.parent_line_id));
517 X_ingred_volpct NUMBER ;
518 X_byprod_volpct NUMBER ;
519 X_rollup NUMBER;
520 BEGIN
521 X_return_status := FND_API.g_ret_sts_success;
522
523 OPEN Cur_get_line2 (-1);
524 FETCH Cur_get_line2 INTO X_ingred_volpct;
525 CLOSE Cur_get_line2;
526
527 OPEN Cur_get_line2 (2);
528 FETCH Cur_get_line2 INTO X_byprod_volpct;
529 CLOSE Cur_get_line2;
530
531 IF (X_ingred_volpct - X_byprod_volpct) <> 0 THEN
532 X_rollup := (X_ingred_volpct - X_byprod_volpct);
533 END IF;
534
535 IF X_rollup IS NULL THEN
536 gmd_api_grp.log_message('GMD_COST_CALCULATE','V_PARM_NAME', V_parm_name);
537 X_return_status := FND_API.g_ret_sts_error;
538 END IF;
539 RETURN(X_rollup);
540 EXCEPTION
541 WHEN OTHERS THEN
542 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_CALCULATE_PKG', 'Rollup_Cost_Units');
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 RETURN(0);
545 END rollup_cost_units;
546
547 /*##############################################################
548 # NAME
549 # rollup_equiv_wt
550 # SYNOPSIS
551 # proc rollup_equiv_wt
552 # DESCRIPTION
553 # This procedure gets the values for the products
554 # by performing the equiv wt rollup.
555 ###############################################################*/
556
557 PROCEDURE rollup_equiv_wt (V_entity_id IN NUMBER,
558 V_line_id IN NUMBER,
559 V_parm_name IN VARCHAR2,
560 V_parm_id IN NUMBER,
561 V_unit_code IN VARCHAR2,
562 V_orgn_id IN NUMBER,
563 V_sort_seq IN NUMBER,
564 X_return_status OUT NOCOPY VARCHAR2) IS
565
566 CURSOR Cur_get_line3 (V_line_type NUMBER) IS
567 SELECT qty,detail_uom,value,inventory_item_id,lot_number,tpformula_id
568 FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
569 WHERE line_type <> 1
570 AND (line_type = V_line_type OR line_type = 3)
571 AND d.line_id = t.line_id (+)
572 AND d.entity_id = t.entity_id (+)
573 AND d.entity_id = V_entity_id
574 AND t.tech_parm_id (+) = V_parm_id
575 AND t.value IS NOT NULL
576 AND rollup_ind = 1
577 AND EXISTS (SELECT 1
578 FROM gmd_material_details_gtmp d1
579 WHERE line_type = V_line_type
580 AND d1.parent_line_id = d.parent_line_id);
581 X_ingred_equiv NUMBER := 0 ;
582 X_byprod_equiv NUMBER := 0 ;
583 X_ingred_mass NUMBER := 0 ;
584 X_byprod_mass NUMBER := 0 ;
585 l_equiv_qty NUMBER;
586 X_rollup NUMBER;
587 l_error NUMBER;
588 BEGIN
589 X_return_status := FND_API.g_ret_sts_success;
590 FOR l_rec IN Cur_get_line3(-1) LOOP
591 IF (l_rec.detail_uom <> V_unit_code) THEN
592 l_equiv_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
593 pformula_id => NVL(l_rec.tpformula_id,0),
594 plot_number => l_rec.lot_number,
595 pcur_qty => l_rec.qty,
596 pcur_uom => l_rec.detail_uom,
597 pnew_uom => V_unit_code,
598 patomic => 0,
599 plab_id => V_orgn_id,
600 pcnv_factor => 0);
601 IF l_equiv_qty < 0 THEN
602 l_error := 1;
603 EXIT;
604 END IF;
605 ELSE
606 l_equiv_qty := l_rec.qty;
607 END IF;
608 X_ingred_equiv := X_ingred_equiv + NVL((l_equiv_qty / l_rec.value),0);
609 X_ingred_mass := X_ingred_mass + l_equiv_qty;
610 END LOOP;
611 IF L_error <> 1 THEN
612 FOR L_rec IN cur_get_line3(2) LOOP
613 IF (l_rec.detail_uom <> V_unit_code) THEN
614 l_equiv_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
615 pformula_id => NVL(l_rec.tpformula_id,0),
616 plot_number => l_rec.lot_number,
617 pcur_qty => l_rec.qty,
618 pcur_uom => l_rec.detail_uom,
619 pnew_uom => V_unit_code,
620 patomic => 0,
621 plab_id => V_orgn_id,
622 pcnv_factor => 0);
623 IF l_equiv_qty < 0 THEN
624 l_error := 1;
625 EXIT;
626 END IF;
627 ELSE
628 l_equiv_qty := l_rec.qty;
629 END IF;
630 X_byprod_equiv := X_byprod_equiv + NVL((l_equiv_qty / l_rec.value),0);
631 X_byprod_mass := X_byprod_mass + l_equiv_qty;
632 END LOOP;
633 END IF;
634 IF(l_error = 1) THEN
635 UPDATE gmd_technical_data_gtmp
636 SET value = NULL,
637 num_data = NULL
638 WHERE line_id = V_line_id
639 AND tech_parm_id = V_parm_id;
640 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
641 ELSE
642 IF (X_ingred_mass - X_byprod_mass) <> 0 THEN
643 X_rollup := (X_ingred_equiv - X_byprod_equiv) / (X_ingred_mass - X_byprod_mass);
644 END IF;
645 UPDATE gmd_technical_data_gtmp
646 SET value = X_rollup,
647 num_data = X_rollup
648 WHERE line_id = V_line_id
649 AND tech_parm_id = V_parm_id;
650
651 IF SQL%NOTFOUND THEN
652 INSERT INTO GMD_TECHNICAL_DATA_GTMP
653 (ENTITY_ID,
654 LINE_ID,
655 SORT_SEQ,
656 TECH_PARM_NAME,
657 TECH_PARM_ID,
658 VALUE,
659 NUM_DATA)
660 VALUES
661 (V_entity_id,
662 V_line_id,
663 V_sort_seq,
664 V_parm_name,
665 V_parm_id,
666 X_rollup,
667 X_rollup);
668 END IF;
669 END IF;
670
671 IF X_rollup IS NULL THEN
672 gmd_api_grp.log_message('GMD_EQUIV_WEIGHT_CALCULATE','V_PARM_NAME', V_parm_name);
673 X_return_status := FND_API.g_ret_sts_error;
674 END IF;
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 fnd_msg_pub.add_exc_msg ('GMD_SPREAD_CALCULATE_PKG', 'Rollup_Equiv_Wt');
679 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
680 END rollup_equiv_wt;
681
682
683 /* following procedures are wrote to debug the procedures */
684 procedure temp_dump (V_entity_id IN NUMBER) IS
685 cursor cur_rec IS
686 select *
687 from gmd_material_details_gtmp
688 where entity_id = V_entity_id
689 AND rollup_ind = 1;
690 begin
691 FOR L_RECORD IN CUR_REC LOOP
692 gmd_debug.put_line('item_no'||l_record.concatenated_segments);
693 gmd_debug.put_line('item_um'||l_record.detail_uom);
694 gmd_debug.put_line('item_qty'||l_record.qty);
695 gmd_debug.put_line('line_id'||l_record.line_id);
696 gmd_debug.put_line('entity'||l_record.entity_id);
697 gmd_debug.put_line('qtymass'||l_record.qty_mass);
698 gmd_debug.put_line('massuom'||l_record.mass_uom);
699 gmd_debug.put_line('qtyvol'||l_record.qty_vol);
700 gmd_debug.put_line('voluom'||l_record.vol_uom);
701 gmd_debug.put_line('rollupind'||l_record.rollup_ind);
702 END LOOP;
703 end temp_dump;
704
705 procedure temp_param (V_entity_id IN NUMBER,V_line_id IN NUMBER) IS
706 cursor cur_rec1 IS
707 select a.*,b.concatenated_segments,b.lot_number,b.qty
708 from gmd_technical_data_gtmp a, gmd_material_details_gtmp b
709 where a.entity_id = V_entity_id
710 AND a.line_id= b.line_id
711 and (v_line_id is null or a.line_id = v_line_id);
712 begin
713 FOR L_REC IN CUR_REC1 LOOP
714 gmd_debug.put_line('item qty lotno lineid techparmname value');
715 gmd_debug.put_line(l_rec.concatenated_segments|| '-' ||l_rec.qty|| '-' ||l_rec.lot_number|| '-' ||l_rec.line_id|| '-' ||l_rec.tech_parm_name|| '-' ||l_rec.value);
716 END LOOP;
717 end temp_param;
718
719
720 /*##############################################################
721 # NAME
722 # auto_calc_product
723 # SYNOPSIS
724 # proc auto_calc_product
725 # DESCRIPTION
726 # Kapil M 12-FEB-2007 Bug# 5716318 : Auto-Prod Calcualtion ME
727 # Added new procedure - auto_calc_product
728 # Kishore - 16-Mar-2009 - Bug No.8317833 : Changed unit_of_measure to
729 # uom_code for the cursor get_unit_of_measure.
730 # Raju 21-May-2009 Bug 8511720 is fixed by adding union to the exisitng cursor
731 # to include newly added ingredients and byprods.
732 ###############################################################*/
733 procedure auto_calc_product(V_entity_id IN NUMBER,
734 x_return_status OUT NOCOPY VARCHAR2,
735 x_msg_count OUT NOCOPY NUMBER,
736 x_msg_data OUT NOCOPY VARCHAR2 ) IS
737
738 -- Added the following code to Get formula id for passed in simulation bug 13406890
739 CURSOR Cur_get_formula_id IS
740 SELECT FORMULA_ID
741 FROM gmd_material_header_gtmp
742 WHERE entity_id = V_entity_id;
743
744 l_formula_id NUMBER;
745
746 CURSOR Cur_get_org_id IS
747 SELECT OWNER_ORGANIZATION_ID
748 FROM FM_FORM_MST
749 WHERE formula_id = l_formula_id;
750
751 CURSOR Cur_get_ingredient_qty(V_entity_id NUMBER) IS
752 SELECT a.qty , a.detail_uom , a.inventory_item_id
753 FROM gmd_material_details_gtmp a , fm_matl_dtl b
754 where a.entity_id = V_entity_id
755 and b.formula_id = l_formula_id
756 and a.inventory_item_id = b.inventory_item_id
757 and b.CONTRIBUTE_YIELD_IND = 'Y'
758 and a.line_type = b.line_type
759 and b.line_type = -1
760 UNION
761 SELECT a.qty , a.detail_uom , a.inventory_item_id
762 FROM gmd_material_details_gtmp a
763 where a.entity_id = V_entity_id
764 and a.line_type = -1
765 and a.inventory_item_id NOT IN (SELECT b.inventory_item_id
766 FROM fm_matl_dtl b
767 WHERE b.formula_id = l_formula_id
768 and b.line_type = -1);
769
770
771 CURSOR Cur_get_byproduct_qty(V_entity_id NUMBER) IS
772 SELECT a.qty , a.detail_uom , a.inventory_item_id
773 FROM gmd_material_details_gtmp a , fm_matl_dtl b
774 where a.entity_id = V_entity_id
775 and b.formula_id = l_formula_id
776 and a.inventory_item_id = b.inventory_item_id
777 and b.CONTRIBUTE_YIELD_IND = 'Y'
778 and a.line_type = b.line_type
779 and b.line_type = 2
780 UNION
781 SELECT a.qty , a.detail_uom , a.inventory_item_id
782 FROM gmd_material_details_gtmp a
783 where a.entity_id = V_entity_id
784 and a.line_type = 2
785 and a.inventory_item_id NOT IN (SELECT b.inventory_item_id
786 FROM fm_matl_dtl b
787 WHERE b.formula_id = l_formula_id
788 and b.line_type = 2);
789
790 CURSOR Cur_get_fixed_prod_qty(V_entity_id NUMBER) IS
791 SELECT b.qty , b.detail_uom , b.inventory_item_id
792 FROM fm_matl_dtl b
793 WHERE b.formula_id = V_entity_id
794 and b.CONTRIBUTE_YIELD_IND = 'Y'
795 and b.line_type = 1
796 and b.scale_type = 0;
797
798 CURSOR Cur_get_product_percent(V_entity_id NUMBER) IS
799 SELECT prod_percent , inventory_item_id , detail_uom
800 FROm fm_matl_dtl
801 WHERE formula_id = V_entity_id
802 AND line_type = 1
803 AND line_no = 1;
804
805 CURSOR Cur_get_uom (V_entity_id NUMBER) IS
806 SELECT qty , detail_uom
807 FROM gmd_material_details_gtmp
808 where entity_id = V_entity_id;
809
810 /* Bug No.8317833 - Changed the SELECT clause of below cursor from unit_of_measure to uom_code */
811 CURSOR get_unit_of_measure(v_yield_type VARCHAR2) IS
812 SELECT uom_code
813 FROM mtl_units_of_measure
814 WHERE uom_class = v_yield_type
815 AND base_uom_flag = 'Y';
816
817 l_org_id NUMBER;
818 l_ing_qty NUMBER := 0;
819 l_byprod_qty NUMBER := 0;
820 l_fix_prod_qty NUMBER := 0;
821 l_prod_qty NUMBER := 0;
822 l_prod_percent NUMBER ;
823
824 l_temp_qty NUMBER := 0;
825
826 l_count NUMBER := 0;
827 l_different_uom VARCHAR2(1);
828 l_uom VARCHAR2(20);
829 l_uom_class VARCHAR2(20);
830 l_common_uom_class VARCHAR2(20);
831 l_yield_type VARCHAR2(100);
832 l_conv_uom VARCHAR2(30);
833 l_return_status VARCHAr2(100);
834
835 CANNOT_CONVERT EXCEPTION;
836
837 BEGIN
838 -- Get formula id for passed in simulation bug 13406890
839 OPEN Cur_get_formula_id;
840 FETCH Cur_get_formula_id INTO l_formula_id;
841 CLOSE Cur_get_formula_id;
842
843 -- Get Owner Organization
844 OPEN Cur_get_org_id;
845 FETCH Cur_get_org_id INTO l_org_id;
846 CLOSE Cur_get_org_id;
847 -- Get the Yield type UOM - NPD Convergence
848 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_org_id ,
849 P_parm_name => 'FM_YIELD_TYPE' ,
850 P_parm_value => l_yield_type ,
851 X_return_status => x_return_status );
852
853 FOR l_rec IN Cur_get_uom(V_entity_id)
854 LOOP
855 l_count := l_count + 1;
856
857 IF NVL (l_uom, l_rec.detail_uom) <> l_rec.detail_uom
858 THEN
859 l_different_uom := 'Y';
860 END IF;
861 l_uom := l_rec.detail_uom;
862
863 -- UOM COnversions
864 IF l_rec.detail_uom IS NOT NULL THEN
865 SELECT uom_class
866 INTO l_uom_class
867 FROM mtl_units_of_measure
868 where uom_code = l_rec .detail_uom;
869
870 IF NVL(l_common_uom_class,l_uom_class) <> l_uom_class THEN
871 OPEN get_unit_of_measure(l_yield_type);
872 FETCH get_unit_of_measure INTO l_conv_uom;
873 CLOSE get_unit_of_measure;
874 END IF;
875 l_common_uom_class := l_uom_class;
876 END IF;
877 IF l_conv_uom IS NULL THEN
878 OPEN get_unit_of_measure(l_common_uom_class);
879 FETCH get_unit_of_measure INTO l_conv_uom;
880 CLOSE get_unit_of_measure;
881
882 END IF;
883
884 END LOOP;
885
886 FOR l_ing_rec IN Cur_get_ingredient_qty(V_entity_id)
887 LOOP
888 IF l_different_uom = 'Y'
889 THEN
890 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => l_ing_rec.inventory_item_id
891 ,precision => 5
892 ,from_quantity => l_ing_rec.qty
893 ,from_unit => l_ing_rec .detail_uom
894 ,to_unit => l_conv_uom
895 ,from_name => NULL
896 ,to_name => NULL);
897
898 IF l_temp_qty < 0
899 THEN
900 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
901 fnd_message.set_token('UOM',l_conv_uom);
902 fnd_msg_pub.ADD;
903 RAISE CANNOT_CONVERT;
904 -- EXIT;
905 END IF;
906 ELSE
907 l_temp_qty := l_ing_rec.qty;
908 END IF;
909 l_ing_qty := l_ing_qty + l_temp_qty;
910 END LOOP;
911
912 FOR l_byprod_rec IN Cur_get_byproduct_qty(V_entity_id)
913 LOOP
914 IF l_different_uom = 'Y'
915 THEN
916 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => l_byprod_rec.inventory_item_id
917 ,precision => 5
918 ,from_quantity => l_byprod_rec.qty
919 ,from_unit => l_byprod_rec .detail_uom
920 ,to_unit => l_conv_uom
921 ,from_name => NULL
922 ,to_name => NULL);
923
924 IF l_temp_qty < 0
925 THEN
926 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
927 fnd_message.set_token('UOM',l_conv_uom);
928 fnd_msg_pub.ADD;
929 RAISE CANNOT_CONVERT;
930 -- EXIT;
931 END IF;
932 ELSE
933 l_temp_qty := l_byprod_rec.qty;
934 END IF;
935 l_byprod_qty := l_byprod_qty + l_temp_qty;
936 END LOOP;
937
938 FOR l_fixprod_rec IN Cur_get_fixed_prod_qty(l_formula_id)
939 LOOP
940 IF l_different_uom = 'Y'
941 THEN
942 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => l_fixprod_rec.inventory_item_id
943 ,precision => 5
944 ,from_quantity => l_fixprod_rec.qty
945 ,from_unit => l_fixprod_rec .detail_uom
946 ,to_unit => l_conv_uom
947 ,from_name => NULL
948 ,to_name => NULL);
949
950 IF l_temp_qty < 0
951 THEN
952 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
953 fnd_message.set_token('UOM',l_conv_uom);
954 fnd_msg_pub.ADD;
955 RAISE CANNOT_CONVERT;
956 -- EXIT;
957 END IF;
958 ELSE
959 l_temp_qty := l_fixprod_rec.qty;
960 END IF;
961 l_fix_prod_qty := l_fix_prod_qty + l_temp_qty;
962 END LOOP;
963
964 l_prod_qty := l_ing_qty - l_byprod_qty - l_fix_prod_qty;
965
966 FOR l_prod_rec IN Cur_get_product_percent(l_formula_id)
967 LOOP
968 IF l_ing_qty <> 0 AND l_prod_rec.prod_percent IS NOT NULL THEN
969 l_prod_qty := l_prod_qty*l_prod_rec.prod_percent/100;
970 IF l_different_uom = 'Y'
971 THEN
972 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => l_prod_rec.inventory_item_id
973 ,precision => 5
974 ,from_quantity => l_prod_qty
975 ,from_unit => l_conv_uom
976 ,to_unit => l_prod_rec.detail_uom
977 ,from_name => NULL
978 ,to_name => NULL);
979
980 IF l_temp_qty < 0
981 THEN
982 x_return_status := 'Q';
983 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
984 fnd_message.set_token('UOM',l_conv_uom);
985 fnd_msg_pub.ADD;
986 EXIT;
987 END IF;
988 ELSE
989 l_temp_qty := l_prod_qty;
990 END IF;
991 UPDATE gmd_material_details_gtmp
992 SET qty = l_temp_qty
993 WHERE line_type = 1
994 AND entity_id = V_entity_id;
995 END IF;
996 END LOOP;
997
998 EXCEPTION
999 WHEN CANNOT_CONVERT THEN
1000 x_return_status := fnd_api.g_ret_sts_unexp_error;
1001 /* fnd_message.set_name ('GMD', 'GMD_UNEXPECTED_ERROR');
1002 fnd_message.set_token ('ERROR', SQLERRM);
1003 fnd_msg_pub.ADD; */
1004 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1005 p_data => x_msg_data);
1006 END auto_calc_product;
1007
1008
1009 END GMD_SPREAD_CALCULATE_PKG;