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