[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
749: WHERE formula_id = l_formula_id;
750:
751: CURSOR Cur_get_ingredient_qty(V_entity_id NUMBER) IS
752: SELECT a.qty , a.detail_uom , a.inventory_item_id
753: FROM gmd_material_details_gtmp a , fm_matl_dtl b
754: where a.entity_id = V_entity_id
755: and b.formula_id = l_formula_id
756: and a.inventory_item_id = b.inventory_item_id
757: and b.CONTRIBUTE_YIELD_IND = 'Y'
758: and a.line_type = b.line_type
759: and b.line_type = -1
760: UNION
761: SELECT a.qty , a.detail_uom , a.inventory_item_id
762: FROM gmd_material_details_gtmp a
763: where a.entity_id = V_entity_id
764: and a.line_type = -1
765: and a.inventory_item_id NOT IN (SELECT b.inventory_item_id
766: FROM fm_matl_dtl b
769:
770:
771: CURSOR Cur_get_byproduct_qty(V_entity_id NUMBER) IS
772: SELECT a.qty , a.detail_uom , a.inventory_item_id
773: FROM gmd_material_details_gtmp a , fm_matl_dtl b
774: where a.entity_id = V_entity_id
775: and b.formula_id = l_formula_id
776: and a.inventory_item_id = b.inventory_item_id
777: and b.CONTRIBUTE_YIELD_IND = 'Y'
778: and a.line_type = b.line_type
779: and b.line_type = 2
780: UNION
781: SELECT a.qty , a.detail_uom , a.inventory_item_id
782: FROM gmd_material_details_gtmp a
783: where a.entity_id = V_entity_id
784: and a.line_type = 2
785: and a.inventory_item_id NOT IN (SELECT b.inventory_item_id
786: FROM fm_matl_dtl b
803: AND line_no = 1;
804:
805: CURSOR Cur_get_uom (V_entity_id NUMBER) IS
806: SELECT qty , detail_uom
807: FROM gmd_material_details_gtmp
808: where entity_id = V_entity_id;
809:
810: /* Bug No.8317833 - Changed the SELECT clause of below cursor from unit_of_measure to uom_code */
811: CURSOR get_unit_of_measure(v_yield_type VARCHAR2) IS
987: END IF;
988: ELSE
989: l_temp_qty := l_prod_qty;
990: END IF;
991: UPDATE gmd_material_details_gtmp
992: SET qty = l_temp_qty
993: WHERE line_type = 1
994: AND entity_id = V_entity_id;
995: END IF;