[Home] [Help]
PACKAGE BODY: APPS.GMD_OPTIMIZE_FETCH_PKG
Source
1 PACKAGE BODY GMD_OPTIMIZE_FETCH_PKG AS
2 /* $Header: GMDOPTMB.pls 120.1 2005/07/13 07:28:26 rajreddy noship $ */
3
4 /*##############################################################
5 # NAME
6 # load_optimizer_details
7 # SYNOPSIS
8 # proc load_optimizer_details
9 # DESCRIPTION
10 # This procedure inserts the data into temp tables and will
11 # be fetched in the form.
12 ###############################################################*/
13
14 PROCEDURE load_optimizer_details (V_entity_id IN NUMBER,V_maintain_type IN NUMBER,
15 X_return_status OUT NOCOPY VARCHAR2) IS
16 CURSOR Cur_get_prod IS
17 SELECT *
18 FROM gmd_material_details_gtmp
19 WHERE line_type = 1;
20 l_prod_rec Cur_get_prod%ROWTYPE;
21
22 CURSOR Cur_get_prod_param(V_line_id NUMBER) IS
23 SELECT a.*,b.value
24 FROM gmd_technical_parameter_gtmp a, gmd_technical_data_gtmp b
25 WHERE a.tech_parm_id = b.tech_parm_id
26 AND a.entity_id = b.entity_id
27 AND b.line_id = V_line_id
28 AND b.entity_id = V_entity_id
29 AND a.data_type IN (5,6,9,10);
30 l_prod_param_rec Cur_get_prod_param%ROWTYPE;
31
32 CURSOR Cur_get_ingred IS
33 SELECT *
34 FROM gmd_material_details_gtmp
35 WHERE line_type IN (-1,3)
36 ORDER BY line_no;
37 l_ingred_rec Cur_get_ingred%ROWTYPE;
38
39 CURSOR Cur_get_value(V_line_id NUMBER) IS
40 SELECT a.*
41 FROM gmd_technical_data_gtmp a, gmd_optimizer_prm_gtmp b
42 WHERE a.entity_id = b.entity_id
43 AND a.tech_parm_id = b.tech_parm_id
44 AND a.line_id = V_line_id;
45 l_value_rec Cur_get_value%ROWTYPE;
46 BEGIN
47 /* Inserting the product data to optimize temp tables */
48 DELETE FROM gmd_optimizer_hdr_gtmp;
49 DELETE FROM gmd_optimizer_prm_gtmp;
50 DELETE FROM gmd_optimizer_line_gtmp;
51 DELETE FROM gmd_optimizer_value_gtmp;
52 OPEN Cur_get_prod;
53 FETCH Cur_get_prod INTO l_prod_rec;
54 CLOSE Cur_get_prod;
55 INSERT INTO GMD_OPTIMIZER_HDR_GTMP
56 (ENTITY_ID,MAINTAIN_TYPE,YIELD,INVENTORY_ITEM_ID,PRODUCT_QTY,PRODUCT_UOM)
57 VALUES (V_entity_id,NVL(V_maintain_type,0),100,l_prod_rec.inventory_item_id,l_prod_rec.qty,l_prod_rec.detail_uom);
58
59 OPEN Cur_get_prod_param(l_prod_rec.line_id);
60 LOOP
61 FETCH Cur_get_prod_param INTO l_prod_param_rec;
62 EXIT WHEN Cur_get_prod_param%NOTFOUND;
63 INSERT INTO GMD_OPTIMIZER_PRM_GTMP
64 (ENTITY_ID,OPTIMIZE_TYPE,TECH_PARM_ID,TECH_PARM_NAME,VALUE,MIN_VALUE,MAX_VALUE,PRECISION,LM_UNIT_CODE)
65 VALUES (V_entity_id,NVL(l_prod_param_rec.optimize_type,0),l_prod_param_rec.tech_parm_id,l_prod_param_rec.tech_parm_name,
66 l_prod_param_rec.value,l_prod_param_rec.lowerbound_num,l_prod_param_rec.upperbound_num,
67 l_prod_param_rec.signif_figures,l_prod_param_rec.lm_unit_code);
68 END LOOP;
69 CLOSE Cur_get_prod_param;
70
71 OPEN Cur_get_ingred;
72 LOOP
73 FETCH Cur_get_ingred INTO l_ingred_rec;
74 EXIT WHEN Cur_get_ingred%NOTFOUND;
75 INSERT INTO GMD_OPTIMIZER_LINE_GTMP
76 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,INVENTORY_ITEM_ID,DESCRIPTION,
77 LOT_NUMBER,QTY,DETAIL_UOM,BUFFER_IND,PARENT_LINE_ID,PRIMARY_QTY,PRIMARY_UOM,
78 SECONDARY_QTY,SECONDARY_UOM,QTY_MASS,MASS_UOM,QTY_VOL,VOL_UOM,ROLLUP_IND)
79 VALUES (V_entity_id,l_ingred_rec.line_id,l_ingred_rec.line_type,l_ingred_rec.line_no,l_ingred_rec.inventory_item_id,
80 l_ingred_rec.description,l_ingred_rec.lot_number,
81 l_ingred_rec.qty,l_ingred_rec.detail_uom,NVL(l_ingred_rec.buffer_ind,0),l_ingred_rec.parent_line_id,
82 l_ingred_rec.primary_qty,l_ingred_rec.primary_uom,l_ingred_rec.secondary_qty,l_ingred_rec.secondary_uom,
83 l_ingred_rec.qty_mass,l_ingred_rec.mass_uom,l_ingred_rec.qty_vol,l_ingred_rec.vol_uom,l_ingred_rec.rollup_ind);
84 OPEN Cur_get_value(l_ingred_rec.line_id);
85 LOOP
86 FETCH Cur_get_value INTO l_value_rec;
87 EXIT WHEN Cur_get_value%NOTFOUND;
88 INSERT INTO GMD_OPTIMIZER_VALUE_GTMP
89 (ENTITY_ID,LINE_ID,TECH_PARM_ID,TECH_PARM_VALUE)
90 VALUES (l_value_rec.entity_id,l_value_rec.line_id,l_value_rec.tech_parm_id,l_value_rec.value);
91 END LOOP;
92 CLOSE Cur_get_value;
93 END LOOP;
94 CLOSE Cur_get_ingred;
95 EXCEPTION
96 WHEN OTHERS THEN
97 fnd_msg_pub.add_exc_msg ('GMD_OPTIMZE_FETCH_PKG', 'Load_Optimizer_Details');
98 x_return_status := FND_API.g_ret_sts_unexp_error;
99 END load_optimizer_details;
100
101 /*##############################################################
102 # NAME
103 # calculate
104 # SYNOPSIS
105 # proc calculate
106 # DESCRIPTION
107 # This procedure calculates the values for the products
108 # by performing the rollups based on data type.
109 ###############################################################*/
110
111 PROCEDURE calculate (V_entity_id IN NUMBER,
112 V_orgn_id IN NUMBER,
113 X_return_status OUT NOCOPY VARCHAR2) IS
114 CURSOR Cur_get_prod IS
115 SELECT a.tech_parm_name,a.lm_unit_code,a.tech_parm_id,b.data_type
116 FROM gmd_optimizer_prm_gtmp a, gmd_technical_parameter_gtmp b
117 WHERE a.entity_id = V_entity_id
118 AND a.entity_id = b.entity_id
119 AND a.tech_parm_id = b.tech_parm_id;
120 BEGIN
121 FOR l_rec IN Cur_get_prod LOOP
122 FND_MSG_PUB.INITIALIZE;
123 IF l_rec.data_type = 5 THEN
124 rollup_wt_pct (V_entity_id => V_entity_id,
125 V_parm_name => l_rec.tech_parm_name,
126 V_parm_id => l_rec.tech_parm_id,
127 X_return_status => X_return_status);
128 ELSIF l_rec.data_type = 6 THEN
129 rollup_vol_pct (V_entity_id => V_entity_id,
130 V_parm_name => l_rec.tech_parm_name,
131 V_parm_id => l_rec.tech_parm_id,
132 X_return_status => X_return_status);
133 ELSIF l_rec.data_type = 9 THEN
134 rollup_equiv_wt (V_entity_id => V_entity_id,
135 V_parm_name => l_rec.tech_parm_name,
136 V_parm_id => l_rec.tech_parm_id,
137 V_unit_code => l_rec.lm_unit_code,
138 V_orgn_id => V_orgn_id,
139 X_return_status => X_return_status);
140 ELSIF l_rec.data_type = 10 THEN
141 rollup_update (V_entity_id => V_entity_id,
142 V_parm_name => l_rec.tech_parm_name,
143 V_parm_id => l_rec.tech_parm_id,
144 X_return_status => X_return_status);
145 END IF;
146 END LOOP;
147 END calculate;
148
149 /*##############################################################
150 # NAME
151 # rollup_wt_pct
152 # SYNOPSIS
153 # proc rollup_wt_pct
154 # DESCRIPTION
155 # This procedure gets the values for the products for the
156 # by performing the weight rollup.
157 ###############################################################*/
158
159 PROCEDURE rollup_wt_pct (V_entity_id IN NUMBER,
160 V_parm_name IN VARCHAR2,
161 V_parm_id IN NUMBER,
162 X_return_status OUT NOCOPY VARCHAR2) IS
163
164 CURSOR Cur_get_line_ingred IS
165 SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
166 FROM
167 (SELECT qty_mass weight, qty_mass * tech_parm_value weightpct
168 FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t
169 WHERE d.line_id = t.line_id (+)
170 AND d.entity_id = t.entity_id (+)
171 AND d.entity_id = V_entity_id
172 AND t.tech_parm_id (+) = V_parm_id
173 AND rollup_ind = 1);
174
175 CURSOR Cur_get_line_byprod IS
176 SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
177 FROM
178 (SELECT qty_mass weight, qty_mass * tech_parm_value weightpct
179 FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
180 WHERE d.line_id = t.line_id (+)
181 AND (line_type = 2 OR line_type = 3)
182 AND d.entity_id = t.entity_id (+)
183 AND d.entity_id = V_entity_id
184 AND t.tech_parm_id (+) = V_parm_id
185 AND rollup_ind = 1
186 AND EXISTS (SELECT 1
187 FROM gmd_material_details_gtmp d1
188 WHERE line_type = 2
189 AND d1.parent_line_id = d.parent_line_id));
190 X_ingred_wt NUMBER ;
191 X_ingred_wtpct NUMBER ;
192 X_byprod_wt NUMBER ;
193 X_byprod_wtpct NUMBER ;
194 X_rollup NUMBER;
195 BEGIN
196 X_return_status := FND_API.g_ret_sts_success;
197
198 OPEN Cur_get_line_ingred;
199 FETCH Cur_get_line_ingred INTO X_ingred_wt, X_ingred_wtpct;
200 CLOSE Cur_get_line_ingred;
201
202 OPEN Cur_get_line_byprod;
203 FETCH Cur_get_line_byprod INTO X_byprod_wt, X_byprod_wtpct;
204 CLOSE Cur_get_line_byprod;
205
206 IF (X_ingred_wt - X_byprod_wt) <> 0 THEN
207 X_rollup := (X_ingred_wtpct - X_byprod_wtpct) / (X_ingred_wt - X_byprod_wt);
208 END IF;
209
210
211 UPDATE gmd_optimizer_prm_gtmp
212 SET value = X_rollup
213 WHERE tech_parm_id = V_parm_id;
214
215 IF SQL%NOTFOUND THEN
216 INSERT INTO GMD_OPTIMIZER_PRM_GTMP
217 (ENTITY_ID,
218 TECH_PARM_ID,
219 TECH_PARM_NAME,
220 VALUE)
221 VALUES
222 (V_entity_id,
223 V_parm_id,
224 V_parm_name,
225 X_rollup);
226 END IF;
227
228 IF X_rollup IS NULL THEN
229 gmd_api_grp.log_message('GMD_WEIGHT_CALCULATE','V_PARM_NAME', V_parm_name);
230 X_return_status := FND_API.g_ret_sts_error;
231 END IF;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 fnd_msg_pub.add_exc_msg ('GMD_OPTIMIZE_FETCH_PKG', 'Rollup_Wt_Pct');
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 END rollup_wt_pct;
238
239 /*##############################################################
240 # NAME
241 # rollup_vol_pct
242 # SYNOPSIS
243 # proc rollup_vol_pct
244 # DESCRIPTION
245 # This procedure gets the values for the products for the
246 # by performing the voulme rollup.
247 ###############################################################*/
248
249 PROCEDURE rollup_vol_pct (V_entity_id IN NUMBER,
250 V_parm_name IN VARCHAR2,
251 V_parm_id IN NUMBER,
252 X_return_status OUT NOCOPY VARCHAR2) IS
253
254 CURSOR Cur_get_line_ing IS
255 SELECT SUM(volume), SUM(volumepct)
256 FROM
257 (SELECT qty_vol volume, qty_vol * tech_parm_value volumepct
258 FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t
259 WHERE d.line_id = t.line_id (+)
260 AND d.entity_id = t.entity_id (+)
261 AND d.entity_id = V_entity_id
262 AND t.tech_parm_id (+) = V_parm_id
263 AND rollup_ind = 1);
264
265 CURSOR Cur_get_line_byp IS
266 SELECT SUM(volume), SUM(volumepct)
267 FROM
268 (SELECT qty_vol volume, qty_vol * tech_parm_value volumepct
269 FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
270 WHERE d.line_id = t.line_id (+)
271 AND (line_type = 2 OR line_type = 3)
272 AND d.entity_id = t.entity_id (+)
273 AND d.entity_id = V_entity_id
274 AND t.tech_parm_id (+) = V_parm_id
275 AND rollup_ind = 1
276 AND EXISTS (SELECT 1
277 FROM gmd_material_details_gtmp d1
278 WHERE line_type = 2
279 AND d1.parent_line_id = d.parent_line_id));
280
281 X_ingred_vol NUMBER ;
282 X_ingred_volpct NUMBER ;
283 X_byprod_vol NUMBER ;
284 X_byprod_volpct NUMBER ;
285 X_rollup NUMBER;
286 BEGIN
287 X_return_status := FND_API.g_ret_sts_success;
288
289 OPEN Cur_get_line_ing;
290 FETCH Cur_get_line_ing INTO X_ingred_vol, X_ingred_volpct;
291 CLOSE Cur_get_line_ing;
292
293 OPEN Cur_get_line_byp;
294 FETCH Cur_get_line_byp INTO X_byprod_vol, X_byprod_volpct;
295 CLOSE Cur_get_line_byp;
296
297 IF (NVL(X_ingred_vol,0) - NVL(X_byprod_vol,0)) <> 0 THEN
298 X_rollup := (NVL(X_ingred_volpct,0) - NVL(X_byprod_volpct,0)) / (NVL(X_ingred_vol,0) - NVL(X_byprod_vol,0));
299 END IF;
300
301 UPDATE gmd_optimizer_prm_gtmp
302 SET value = X_rollup
303 WHERE tech_parm_id = V_parm_id;
304
305 IF SQL%NOTFOUND THEN
306 INSERT INTO GMD_OPTIMIZER_PRM_GTMP
307 (ENTITY_ID,
308 TECH_PARM_ID,
309 TECH_PARM_NAME,
310 VALUE)
311 VALUES
312 (V_entity_id,
313 V_parm_id,
314 V_parm_name,
315 X_rollup);
316 END IF;
317
318 IF X_rollup IS NULL THEN
319 gmd_api_grp.log_message('GMD_VOLUME_CALCULATE','V_PARM_NAME', V_parm_name);
320 X_return_status := FND_API.g_ret_sts_error;
321 END IF;
322 EXCEPTION
323 WHEN OTHERS THEN
324 fnd_msg_pub.add_exc_msg ('GMD_OPTIMIZE_FETCH_PKG', 'Rollup_Vol_Pct');
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326 END rollup_vol_pct;
327
328 /*##############################################################
329 # NAME
330 # rollup_update
331 # SYNOPSIS
332 # proc rollup_update
333 # DESCRIPTION
334 # This procedure gets the values for the products for the
335 # by performing the cost units rollup and updates the same.
336 ###############################################################*/
337
338 PROCEDURE rollup_update (V_entity_id IN NUMBER,
339 V_parm_name IN VARCHAR2,
340 V_parm_id IN NUMBER,
341 X_return_status OUT NOCOPY VARCHAR2) IS
342 X_rollup_cost NUMBER;
343 BEGIN
344 X_rollup_cost := rollup_cost_units(V_entity_id,V_parm_name,V_parm_id,X_return_status);
345
346 UPDATE gmd_optimizer_prm_gtmp
347 SET value = X_rollup_cost
348 WHERE tech_parm_id = V_parm_id;
349
350 IF SQL%NOTFOUND THEN
351 INSERT INTO GMD_OPTIMIZER_PRM_GTMP
352 (ENTITY_ID,
353 TECH_PARM_ID,
354 TECH_PARM_NAME,
355 VALUE)
356 VALUES
357 (V_entity_id,
358 V_parm_id,
359 V_parm_name,
360 X_rollup_cost);
361 END IF;
362 END rollup_update;
363
364
365 /*##############################################################
366 # NAME
367 # rollup_cost_units
368 # SYNOPSIS
369 # proc rollup_cost_units
370 # DESCRIPTION
371 # This procedure gets the values for the products for the
372 # by performing the cost units rollup.
373 ###############################################################*/
374
375 FUNCTION rollup_cost_units (V_entity_id IN NUMBER,
376 V_parm_name IN VARCHAR2,
377 V_parm_id IN NUMBER,
378 X_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
379
380 CURSOR Cur_get_line_ing2 IS
381 SELECT NVL(SUM(volumepct), 0)
382 FROM
383 (SELECT primary_qty * tech_parm_value volumepct
384 FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t
385 WHERE d.line_id = t.line_id (+)
386 AND d.entity_id = t.entity_id (+)
387 AND d.entity_id = V_entity_id
388 AND t.tech_parm_id (+) = V_parm_id
389 AND rollup_ind = 1);
390
391 CURSOR Cur_get_line_byp2 IS
392 SELECT NVL(SUM(volumepct), 0)
393 FROM
394 (SELECT primary_qty * tech_parm_value volumepct
395 FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
396 WHERE d.line_id = t.line_id (+)
397 AND (line_type = 2 OR line_type = 3)
398 AND d.entity_id = t.entity_id (+)
399 AND d.entity_id = V_entity_id
400 AND t.tech_parm_id (+) = V_parm_id
401 AND rollup_ind = 1
402 AND EXISTS (SELECT 1
403 FROM gmd_material_details_gtmp d1
404 WHERE line_type = 2
405 AND d1.parent_line_id = d.parent_line_id));
406
407 X_ingred_volpct NUMBER ;
408 X_byprod_volpct NUMBER ;
409 X_rollup NUMBER;
410 BEGIN
411 X_return_status := FND_API.g_ret_sts_success;
412
413 OPEN Cur_get_line_ing2;
414 FETCH Cur_get_line_ing2 INTO X_ingred_volpct;
415 CLOSE Cur_get_line_ing2;
416
417 OPEN Cur_get_line_byp2;
418 FETCH Cur_get_line_byp2 INTO X_byprod_volpct;
419 CLOSE Cur_get_line_byp2;
420
421 IF (X_ingred_volpct - X_byprod_volpct) <> 0 THEN
422 X_rollup := (X_ingred_volpct - X_byprod_volpct);
423 END IF;
424 RETURN(X_rollup);
425
426 IF X_rollup IS NULL THEN
427 gmd_api_grp.log_message('GMD_COST_CALCULATE','V_PARM_NAME', V_parm_name);
428 X_return_status := FND_API.g_ret_sts_error;
429 END IF;
430 EXCEPTION
431 WHEN OTHERS THEN
432 fnd_msg_pub.add_exc_msg ('GMD_OPTIMIZE_FETCH_PKG', 'Rollup_Cost_Units');
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434 END rollup_cost_units;
435
436 /*##############################################################
437 # NAME
438 # rollup_equiv_wt
439 # SYNOPSIS
440 # proc rollup_equiv_wt
441 # DESCRIPTION
442 # This procedure gets the values for the products
443 # by performing the equiv wt rollup.
444 ###############################################################*/
445
446 PROCEDURE rollup_equiv_wt (V_entity_id IN NUMBER,
447 V_parm_name IN VARCHAR2,
448 V_parm_id IN NUMBER,
449 V_unit_code IN VARCHAR2,
450 V_orgn_id IN NUMBER,
451 X_return_status OUT NOCOPY VARCHAR2) IS
452
453 CURSOR Cur_get_line_ing3 IS
454 SELECT d.qty,d.detail_uom,d.lot_number,t.tech_parm_value,d.inventory_item_id,p.tpformula_id
455 FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t, gmd_material_details_gtmp p
456 WHERE d.line_id = t.line_id (+)
457 AND d.entity_id = t.entity_id (+)
458 AND d.entity_id = p.entity_id
459 AND d.line_id = p.line_id
460 AND d.entity_id = V_entity_id
461 AND t.tech_parm_id (+) = V_parm_id
462 AND t.tech_parm_value IS NOT NULL
463 AND d.rollup_ind = 1;
464
465 CURSOR Cur_get_line_byp3 IS
466 SELECT qty,detail_uom,lot_number,tech_parm_value,inventory_item_id,d.tpformula_id
467 FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
468 WHERE d.line_id = t.line_id (+)
469 AND (line_type = 2 OR line_type = 3)
470 AND d.entity_id = t.entity_id (+)
471 AND d.entity_id = V_entity_id
472 AND t.tech_parm_id (+) = V_parm_id
473 AND t.tech_parm_value IS NOT NULL
474 AND rollup_ind = 1
475 AND EXISTS (SELECT 1
476 FROM gmd_material_details_gtmp d1
477 WHERE line_type = 2
478 AND d1.parent_line_id = d.parent_line_id);
479
480 X_ingred_equiv NUMBER := 0 ;
481 X_byprod_equiv NUMBER := 0 ;
482 X_ingred_mass NUMBER := 0 ;
483 X_byprod_mass NUMBER := 0 ;
484 l_equiv_qty NUMBER;
485 X_rollup NUMBER;
486 l_error NUMBER;
487 BEGIN
488 X_return_status := FND_API.g_ret_sts_success;
489 FOR l_rec IN Cur_get_line_ing3 LOOP
490 IF (l_rec.detail_uom <> V_unit_code) THEN
491 l_equiv_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
492 pformula_id => NVL(l_rec.tpformula_id,0),
493 plot_number => l_rec.lot_number,
494 pcur_qty => l_rec.qty,
495 pcur_uom => l_rec.detail_uom,
496 pnew_uom => V_unit_code,
497 patomic => 0,
498 plab_id => V_orgn_id,
499 pcnv_factor => 0);
500 IF l_equiv_qty < 0 THEN
501 l_error := 1;
502 EXIT;
503 END IF;
504 ELSE
505 l_equiv_qty := l_rec.qty;
506 END IF;
507 X_ingred_equiv := X_ingred_equiv + (l_equiv_qty / l_rec.tech_parm_value);
508 X_ingred_mass := X_ingred_mass + X_ingred_equiv;
509 END LOOP;
510 IF L_error <> 1 THEN
511 FOR L_rec IN cur_get_line_byp3 LOOP
512 IF (l_rec.detail_uom <> V_unit_code) THEN
513 l_equiv_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
514 pformula_id => NVL(l_rec.tpformula_id,0),
515 plot_number => l_rec.lot_number,
516 pcur_qty => l_rec.qty,
517 pcur_uom => l_rec.detail_uom,
518 pnew_uom => V_unit_code,
519 patomic => 0,
520 plab_id => V_orgn_id,
521 pcnv_factor => 0);
522 IF l_equiv_qty < 0 THEN
523 l_error := 1;
524 EXIT;
525 END IF;
526 ELSE
527 l_equiv_qty := l_rec.qty;
528 END IF;
529 X_byprod_equiv := X_byprod_equiv + (l_equiv_qty / l_rec.tech_parm_value);
530 X_byprod_mass := X_byprod_mass + X_ingred_equiv;
531 END LOOP;
532 END IF;
533 IF(l_error = 1) THEN
534 UPDATE gmd_optimizer_prm_gtmp
535 SET value = NULL
536 WHERE tech_parm_id = V_parm_id;
537 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
538 ELSE
539 IF (X_ingred_mass - X_byprod_mass) <> 0 THEN
540 X_rollup := (X_ingred_equiv - X_byprod_equiv) / (X_ingred_mass - X_byprod_mass);
541 END IF;
542 UPDATE gmd_optimizer_prm_gtmp
543 SET value = X_rollup
544 WHERE tech_parm_id = V_parm_id;
545
546 IF SQL%NOTFOUND THEN
547 INSERT INTO GMD_OPTIMIZER_PRM_GTMP
548 (ENTITY_ID,
549 TECH_PARM_ID,
550 TECH_PARM_NAME,
551 VALUE)
552 VALUES
553 (V_entity_id,
554 V_parm_id,
555 V_parm_name,
556 X_rollup);
557 END IF;
558 END IF;
559
560 IF X_rollup IS NULL THEN
561 gmd_api_grp.log_message('GMD_EQUIV_WEIGHT_CALCULATE','V_PARM_NAME', V_parm_name);
562 X_return_status := FND_API.g_ret_sts_error;
563 END IF;
564
565 EXCEPTION
566 WHEN OTHERS THEN
567 fnd_msg_pub.add_exc_msg ('GMD_OPTIMIZE_FETCH_PKG', 'Rollup_Equiv_Wt');
568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
569 END rollup_equiv_wt;
570
571 /*##############################################################
572 # NAME
573 # is_lot_selected
574 # SYNOPSIS
575 # proc is_lot_selected
576 # DESCRIPTION
577 # This function will check if nay lots are selected for optimzation.
578 ###############################################################*/
579
580 FUNCTION is_lot_selected(V_parentline_id IN NUMBER) RETURN VARCHAR2 IS
581 CURSOR Cur_get_select IS
582 SELECT 1
583 FROM DUAL
584 WHERE EXISTS (SELECT 1
585 FROM gmd_optimizer_line_gtmp
586 WHERE buffer_ind = 1
587 AND line_type = 3
588 AND parent_line_id = V_parentline_id);
589 l_exist NUMBER;
590 BEGIN
591 OPEN Cur_get_select;
592 FETCH Cur_get_select INTO l_exist;
593 IF (Cur_get_select%FOUND) THEN
594 CLOSE Cur_get_select;
595 RETURN('T');
596 END IF;
597 CLOSE Cur_get_select;
598 RETURN('F');
599 END is_lot_selected;
600
601 /*##############################################################
602 # NAME
603 # consider_line
604 # SYNOPSIS
605 # proc consider_line
606 # DESCRIPTION
607 # This function will retunr the T or F based on the lot selected
608 # for that item.
609 ###############################################################*/
610
611 FUNCTION consider_line(V_line_id IN NUMBER) RETURN VARCHAR2 IS
612 CURSOR Cur_get_linetype IS
613 SELECT line_type,parent_line_id
614 FROM gmd_optimizer_line_gtmp
615 WHERE line_id = V_line_id;
616 l_line_type NUMBER;
617 l_parent_line_id NUMBER;
618 BEGIN
619 OPEN Cur_get_linetype;
620 FETCH Cur_get_linetype INTO l_line_type, l_parent_line_id;
621 CLOSE Cur_get_linetype;
622 IF (is_lot_selected(l_parent_line_id) = 'T') THEN
623 IF (l_line_type = 3) THEN
624 RETURN('T');
625 ELSE
626 RETURN('F');
627 END IF;
628 ELSE
629 IF (l_line_type = 3) THEN
630 RETURN('F');
631 ELSE
632 RETURN('T');
633 END IF;
634 END IF;
635 END consider_line;
636
637 /*##############################################################
638 # NAME
639 # get_density_value
640 # SYNOPSIS
641 # proc get_density_value
642 # DESCRIPTION
643 # This procedure gets the density value for uom conversion.
644 ###############################################################*/
645
646 FUNCTION get_density_value (V_line_id IN NUMBER,
647 V_density_parameter IN VARCHAR2) RETURN NUMBER IS
648 CURSOR Cur_density IS
649 SELECT value
650 FROM gmd_technical_data_gtmp
651 WHERE line_id = V_line_id
652 AND tech_parm_name = V_density_parameter;
653 l_value NUMBER;
654 BEGIN
655 OPEN Cur_density;
656 FETCH Cur_density INTO l_value;
657 CLOSE Cur_density;
658 RETURN (l_value);
659 END get_density_value;
660
661 /*##############################################################
662 # NAME
663 # update_line_mass_vol_qty
664 # SYNOPSIS
665 # proc update_line_mass_vol_qty
666 # DESCRIPTION
667 # This procedure calculates the qtys to mass and volume.
668 ###############################################################*/
669
670 PROCEDURE update_line_mass_vol_qty (V_orgn_id IN NUMBER,
671 V_line_id IN NUMBER,
672 V_density_parameter IN VARCHAR2,
673 V_mass_uom IN VARCHAR2,
674 V_vol_uom IN VARCHAR2,
675 X_return_status OUT NOCOPY VARCHAR2) IS
676
677 CURSOR Cur_line_qty IS
678 SELECT inventory_item_id, lot_number, qty,
679 detail_uom,primary_uom,secondary_uom
680 FROM gmd_optimizer_line_gtmp
681 WHERE line_id = V_line_id;
682
683 CURSOR Cur_line_item_number (V_inventory_item_id NUMBER)IS
684 SELECT concatenated_segments
685 FROM mtl_system_items_kfv
686 WHERE inventory_item_id = V_inventory_item_id;
687
688 l_conv_factor NUMBER;
689 l_mass_qty NUMBER;
690 l_primary_qty NUMBER;
691 l_vol_qty NUMBER;
692 l_item_no VARCHAR2(1000);
693 l_error NUMBER(5) := 0;
694 l_rec Cur_line_qty%ROWTYPE;
695 LINE_NOT_FOUND EXCEPTION;
696 BEGIN
697 X_return_status := FND_API.g_ret_sts_success;
698
699 l_conv_factor := get_density_value (V_line_id => V_line_id,
700 V_density_parameter => V_density_parameter);
701
702 OPEN Cur_line_qty;
703 FETCH Cur_line_qty INTO l_rec;
704 IF Cur_line_qty%NOTFOUND THEN
705 CLOSE Cur_line_qty;
706 RAISE LINE_NOT_FOUND;
707 END IF;
708 CLOSE Cur_line_qty;
709
710 IF l_rec.detail_uom <> V_mass_uom THEN
711 l_mass_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
712 pformula_id => 0,
713 plot_number => l_rec.lot_number,
714 pcur_qty => l_rec.qty,
715 pcur_uom => l_rec.detail_uom,
716 pnew_uom => V_mass_uom,
717 patomic => 0,
718 plab_id => V_orgn_id,
719 pcnv_factor => l_conv_factor);
720 IF l_mass_qty < 0 THEN
721 l_error := 1;
722 l_mass_qty := NULL;
723 END IF;
724 ELSE
725 l_mass_qty := l_rec.qty;
726 END IF;
727
728 IF l_rec.detail_uom <> V_vol_uom THEN
729 l_vol_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
730 pformula_id => 0,
731 plot_number => l_rec.lot_number,
732 pcur_qty => l_rec.qty,
733 pcur_uom => l_rec.detail_uom,
734 pnew_uom => V_vol_uom,
735 patomic => 0,
736 plab_id => V_orgn_id,
737 pcnv_factor => l_conv_factor);
738 IF l_vol_qty < 0 THEN
739 l_error := 1;
740 l_vol_qty := NULL;
741 END IF;
742 ELSE
743 l_vol_qty := l_rec.qty;
744 END IF;
745
746 IF l_rec.detail_uom <> l_rec.primary_uom THEN
747 l_primary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
748 pformula_id => 0,
749 plot_number => l_rec.lot_number,
750 pcur_qty => l_rec.qty,
751 pcur_uom => l_rec.detail_uom,
752 pnew_uom => l_rec.primary_uom,
753 patomic => 0,
754 plab_id => V_orgn_id);
755 IF l_primary_qty < 0 THEN
756 l_error := 1;
757 l_primary_qty := NULL;
758 END IF;
759 ELSE
760 l_primary_qty := l_rec.qty;
761 END IF;
762
763 UPDATE gmd_optimizer_line_gtmp
764 SET qty_mass = l_mass_qty,
765 mass_uom = V_mass_uom,
766 qty_vol = l_vol_qty,
767 vol_uom = V_vol_uom,
768 primary_qty = l_primary_qty,
769 primary_uom = l_rec.primary_uom
770 WHERE line_id = V_line_id;
771
772 OPEN Cur_line_item_number(l_rec.inventory_item_id);
773 FETCH Cur_line_item_number INTO l_item_no;
774 CLOSE Cur_line_item_number;
775
776 IF l_error = 1 THEN
777 X_return_status := FND_API.g_ret_sts_error;
778 gmd_api_grp.log_message('LM_BAD_UOMCV', 'ITEM_NO',l_item_no);
779 END IF;
780
781 EXCEPTION
782 WHEN line_not_found THEN
783 X_return_status := FND_API.g_ret_sts_error;
784 WHEN OTHERS THEN
785 fnd_msg_pub.add_exc_msg ('GMD_OPTIMIZE_FETCH_PKG', 'Update_Line_Mass_Vol_Qty');
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 END update_line_mass_vol_qty;
788
789 /*##############################################################
790 # NAME
791 # update_line_mass_qty
792 # SYNOPSIS
793 # proc update_line_mass_qty
794 # DESCRIPTION
795 # This procedure calculates the qtys to mass and volume.
796 ###############################################################*/
797
798 PROCEDURE update_mass_vol_qty (V_orgn_id IN NUMBER,
799 V_entity_id IN NUMBER,
800 V_density_parameter IN VARCHAR2,
801 V_mass_uom IN VARCHAR2,
802 V_vol_uom IN VARCHAR2,
803 X_return_status OUT NOCOPY VARCHAR2) IS
804 CURSOR Cur_get_lines IS
805 SELECT line_id
806 FROM gmd_optimizer_line_gtmp
807 WHERE rollup_ind = 1
808 AND entity_id = V_entity_id;
809
810 l_return_status VARCHAR2(1);
811 BEGIN
812 X_return_status := FND_API.g_ret_sts_success;
813
814 FOR l_rec IN Cur_get_lines LOOP
815 l_return_status := FND_API.g_ret_sts_success;
816
817 update_line_mass_vol_qty (V_orgn_id => V_orgn_id,
818 V_line_id => l_rec.line_id,
819 V_density_parameter => V_density_parameter,
820 V_mass_uom => V_mass_uom,
821 V_vol_uom => V_vol_uom,
822 X_return_status => l_return_status);
823 IF l_return_status <> x_return_status THEN
824 X_return_status := l_return_status;
825 END IF;
826 END LOOP;
827 END update_mass_vol_qty;
828
829
830
831
832
833
834
835 END GMD_OPTIMIZE_FETCH_PKG;