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