[Home] [Help]
15: V_orgn_id IN NUMBER,
16: X_return_status OUT NOCOPY VARCHAR2) IS
17: CURSOR Cur_get_prod IS
18: SELECT d.*, p.tech_parm_name, p.data_type, p.lm_unit_code,p.sort_seq,p.tech_parm_id
19: FROM gmd_material_details_gtmp d, gmd_technical_parameter_gtmp p
20: WHERE d.entity_id = V_entity_id
21: AND p.data_type > 3 AND (p.data_type = 4 OR d.line_type = 1);
22: l_count NUMBER;
23: j NUMBER;
112: CURSOR Cur_get_line (V_line_type NUMBER) IS
113: SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
114: FROM
115: (SELECT qty_mass weight, qty_mass * value weightpct
116: FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
117: WHERE line_type <> 1
118: AND (line_type = V_line_type OR line_type = 3)
119: AND d.line_id = t.line_id (+)
120: AND d.entity_id = t.entity_id (+)
121: AND d.entity_id = V_entity_id
122: AND t.tech_parm_id (+) = V_parm_id
123: AND rollup_ind = 1
124: AND EXISTS (SELECT 1
125: FROM gmd_material_details_gtmp d1
126: WHERE line_type = V_line_type
127: AND d1.parent_line_id = d.parent_line_id));
128: X_ingred_wt NUMBER ;
129: X_ingred_wtpct NUMBER ;
266: CURSOR Cur_get_line1 (V_line_type NUMBER) IS
267: SELECT SUM(volume), SUM(volumepct)
268: FROM
269: (SELECT qty_vol volume, qty_vol * value volumepct
270: FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
271: WHERE line_type <> 1
272: AND (line_type = V_line_type OR line_type = 3)
273: AND d.line_id = t.line_id (+)
274: AND d.entity_id = t.entity_id (+)
275: AND d.entity_id = V_entity_id
276: AND t.tech_parm_id (+) = V_parm_id
277: AND rollup_ind = 1
278: AND EXISTS (SELECT 1
279: FROM gmd_material_details_gtmp d1
280: WHERE line_type = V_line_type
281: AND d1.parent_line_id = d.parent_line_id));
282: CURSOR Cur_std_um (V_uom_type VARCHAR2) IS
283: SELECT uom_code
501: CURSOR Cur_get_line2 (V_line_type NUMBER) IS
502: SELECT NVL(SUM(volumepct), 0)
503: FROM
504: (SELECT primary_qty * value volumepct
505: FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
506: WHERE line_type <> 1
507: AND (line_type = V_line_type OR line_type = 3)
508: AND d.line_id = t.line_id (+)
509: AND d.entity_id = t.entity_id (+)
510: AND d.entity_id = V_entity_id
511: AND t.tech_parm_id (+) = V_parm_id
512: AND rollup_ind = 1
513: AND EXISTS (SELECT 1
514: FROM gmd_material_details_gtmp d1
515: WHERE line_type = V_line_type
516: AND d1.parent_line_id = d.parent_line_id));
517: X_ingred_volpct NUMBER ;
518: X_byprod_volpct NUMBER ;
564: X_return_status OUT NOCOPY VARCHAR2) IS
565:
566: CURSOR Cur_get_line3 (V_line_type NUMBER) IS
567: SELECT qty,detail_uom,value,inventory_item_id,lot_number,tpformula_id
568: FROM gmd_material_details_gtmp d, gmd_technical_data_gtmp t
569: WHERE line_type <> 1
570: AND (line_type = V_line_type OR line_type = 3)
571: AND d.line_id = t.line_id (+)
572: AND d.entity_id = t.entity_id (+)
574: AND t.tech_parm_id (+) = V_parm_id
575: AND t.value IS NOT NULL
576: AND rollup_ind = 1
577: AND EXISTS (SELECT 1
578: FROM gmd_material_details_gtmp d1
579: WHERE line_type = V_line_type
580: AND d1.parent_line_id = d.parent_line_id);
581: X_ingred_equiv NUMBER := 0 ;
582: X_byprod_equiv NUMBER := 0 ;
683: /* following procedures are wrote to debug the procedures */
684: procedure temp_dump (V_entity_id IN NUMBER) IS
685: cursor cur_rec IS
686: select *
687: from gmd_material_details_gtmp
688: where entity_id = V_entity_id
689: AND rollup_ind = 1;
690: begin
691: FOR L_RECORD IN CUR_REC LOOP
704:
705: procedure temp_param (V_entity_id IN NUMBER,V_line_id IN NUMBER) IS
706: cursor cur_rec1 IS
707: select a.*,b.concatenated_segments,b.lot_number,b.qty
708: from gmd_technical_data_gtmp a, gmd_material_details_gtmp b
709: where a.entity_id = V_entity_id
710: AND a.line_id= b.line_id
711: and (v_line_id is null or a.line_id = v_line_id);
712: begin
737: WHERE formula_id = V_entity_id;
738:
739: CURSOR Cur_get_ingredient_qty(V_entity_id NUMBER) IS
740: SELECT a.qty , a.detail_uom , a.inventory_item_id
741: FROM gmd_material_details_gtmp a , fm_matl_dtl b
742: where a.entity_id = V_entity_id
743: and b.formula_id = V_entity_id
744: and a.inventory_item_id = b.inventory_item_id
745: and b.CONTRIBUTE_YIELD_IND = 'Y'
747: and b.line_type = -1;
748:
749: CURSOR Cur_get_byproduct_qty(V_entity_id NUMBER) IS
750: SELECT a.qty , a.detail_uom , a.inventory_item_id
751: FROM gmd_material_details_gtmp a , fm_matl_dtl b
752: where a.entity_id = V_entity_id
753: and b.formula_id = V_entity_id
754: and a.inventory_item_id = b.inventory_item_id
755: and b.CONTRIBUTE_YIELD_IND = 'Y'
772: AND line_no = 1;
773:
774: CURSOR Cur_get_uom (V_entity_id NUMBER) IS
775: SELECT qty , detail_uom
776: FROM gmd_material_details_gtmp
777: where entity_id = V_entity_id;
778:
779: CURSOR get_unit_of_measure(v_yield_type VARCHAR2) IS
780: SELECT unit_of_measure
951: END IF;
952: ELSE
953: l_temp_qty := l_prod_qty;
954: END IF;
955: UPDATE gmd_material_details_gtmp
956: SET qty = l_temp_qty
957: WHERE line_type = 1
958: AND entity_id = V_entity_id;
959: END IF;