[Home] [Help]
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;
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;
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);
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
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
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)
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
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)
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
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)
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
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
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)