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