ALTER TABLE out115 ADD o115_id_back_loc VARCHAR(60) COMMENT '退回库位号';
ALTER TABLE out115 ADD o115_id_back_i22 VARCHAR(60) COMMENT '退库接收i22Id';
ALTER TABLE out115 ADD o115_flag_received VARCHAR(1) DEFAULT '' COMMENT '接收状态';
UPDATE out115 INNER JOIN inv22 ON o115_id_i22 = i22_id SET o115_id_back_loc = i22_id_loc;
UPDATE out115 SET o115_id_back_i22 = o115_id_i22,o115_flag_received = 'Y';
UPDATE out115,inv22 a,inv22 b
SET o115_id_i22 = b.i22_id
WHERE o115_id_i22 = a.i22_id
AND a.i22_id_source_ori = b.i22_id_source_ori
AND b.i22_var_source = '退库出库';
DELIMITER //
DROP PROCEDURE if EXISTs `proc_deal_inv22_back_out`;//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `proc_deal_inv22_back_out`(IN var_serial VARCHAR(60),in ids varchar(60),in types varchar(60),in var_source varchar(60),out int_result int)
begin
		DECLARE ori_weight DECIMAL(20,7) DEFAULT 0;
		#领料单id
		DECLARE id_picking_order VARCHAR(60);
		
    #退库出库
    set int_result = 0;
		
		#找该流水号最近一次领料记录对应的领料单
		SELECT i22_id_main INTO id_picking_order
		FROM inv22 WHERE i22_var_source = '领料单'
		AND i22_var_serial = var_serial
		ORDER BY i22_date_create DESC 
		LIMIT 1;
		
		IF IFNULL(id_picking_order,'') != '' THEN
			#将此次退库操作记录在对应的领料单中
			INSERT INTO out115(o115_id_o11,o115_id_i22,o115_var_serial,o115_id_back_loc,o115_id_back_i22,o115_flag_received)
			SELECT id_picking_order,ids,var_serial,'','','';
		END IF;
		
		DELETE FROM inv21 WHERE i21_var_serial = var_serial;
		
		SELECT i31_dec_end INTO ori_weight
		FROM inv31 WHERE i31_id = var_serial;
		
		#i31_var_cata记录选择的退回仓库
		UPDATE inv31,inv22 
		SET i31_flag_inv = 'back',
		i31_var_remark = i22_var_remark,
		i31_id_loc = '',
		i31_dec_end = i22_dec_weight,
		i31_dec_tag = i22_dec_tag,
		i31_dec_pack_gross = i22_dec_pack_gross,
		i31_var_cata = i22_var_cata,
		i31_id_source_table = i22_id_source_ori,
		i31_dec_end_qty = i22_dec_qty,
		i31_flag_half = IF(i22_dec_weight <> ori_weight,'Y',i31_flag_half)
		WHERE i31_id = var_serial
		AND i22_id = ids;
end//
DELIMITER ;

DELIMITER //
DROP PROCEDURE if EXISTs `proc_deal_inv22_back`;//
DELIMITER;

DELIMITER //
CREATE PROCEDURE `proc_deal_inv22_back`(IN ids VARCHAR(60),IN p_var_serial VARCHAR(60),in var_source varchar(60),out int_result int)
begin
 
	#生产退料  
	declare counts integer default 0;
	declare i integer default 0;
	#操作量
	DECLARE dec_weight DECIMAL(20,7) DEFAULT 0;
	#仓库类型
	DECLARE flag_wh_type VARCHAR(10);
	declare rec_id VARCHAR(60);
	#领料单id
	declare id_picking_order VARCHAR(60);
	set int_result = 0;
	
	SELECT i22_dec_weight INTO dec_weight FROM inv22 WHERE i22_id = ids;
	
	IF var_source = '移库入库' THEN
				INSERT INTO inv22(i22_id, i22_date_create, i22_id_creater, i22_flag_state, i22_int_line, 
										i22_date_bill, i22_id_clerk, i22_id_source_table, i22_id_source, i22_bill_no, 
										i22_id_table, i22_int_year, i22_id_audit, i22_date_audit, i22_int_month, 
										i22_id_dept, i22_id_com, i22_id_main, i22_id_flow, i22_date_audit_act, 
										i22_var_bill_ori, i22_var_busi_no, i22_id_item,   i22_var_item, i22_code_item, 
										i22_var_serial, i22_var_batch, i22_dec_tag, i22_dec_tare, i22_id_unit, 
										i22_date_prod, i22_date_expire, i22_id_sup, i22_id_wh, i22_id_loc, 
										i22_id_container, i22_int_bag_count, i22_flag_half, i22_flag_checked, i22_flag_occu, 
										i22_flag_closed, i22_id_terminal, i22_flag_io, i22_var_serial_ori, i22_id_scale, 
										i22_flag_tag, i22_flag_bag, i22_dec_weight, i22_dec_qty, i22_var_source, 
										i22_id_pack, i22_id_source_ori,i22_id_supp,i22_var_supp,i22_id_customer,
										i22_dec_pack_gross,i22_var_batch_semi,i22_var_box_pattern,i22_dec_box_qty,i22_date_check,
										i22_date_check_expire,i22_var_checker,i22_var_maker,i22_var_gravity)
				SELECT uuid_short(), i22_date_create, i22_id_creater, i22_flag_state, i22_int_line, 
										i22_date_bill, i22_id_clerk, i22_id_source_table, i22_id_source, i22_bill_no, 
										i22_id_table,	i22_int_year, i22_id_audit, i22_date_audit, i22_int_month,
										i22_id_dept, i22_id_com, i22_id_main, i22_id_flow, i22_date_audit_act,
										i22_var_bill_ori, i22_var_busi_no, i22_id_item, i22_var_item, i22_code_item, 
										i22_var_serial, i22_var_batch, i22_dec_tag, i22_dec_tare, i22_id_unit, 
										i22_date_prod, i22_date_expire, i22_id_sup, i31_id_wh, i22_id_loc, 
										i22_id_container, i22_int_bag_count, i22_flag_half, i22_flag_checked, i22_flag_occu, 
										i22_flag_closed, i22_id_terminal, '0', i22_var_serial_ori, i22_id_scale,
										i22_flag_tag, i22_flag_tag, i22_dec_weight, i22_dec_qty, '移库出库',
										i22_id_pack, i22_id_source_ori,i22_id_supp,i22_var_supp,i22_id_customer,
										i31_dec_pack_gross,i31_var_batch_semi,i31_var_box_pattern,i31_dec_box_qty,i31_date_check,
										i31_date_check_expire,i31_var_checker,i31_var_maker,i31_var_gravity
				FROM inv22,inv31
				WHERE i22_id = ids 
				AND i22_var_serial = i31_id;
				
				#更新库存对应的仓库信息
				UPDATE inv22,inv31 SET i31_id_wh = i22_id_wh
				WHERE i31_id = i22_var_serial 
				AND i22_id = ids;
				
				UPDATE inv22,inv21 SET i21_id_wh = i22_id_wh
				WHERE i21_var_serial = i22_var_serial 
				AND i22_id = ids;
				
	ELSEIF var_source='退库' THEN 		
			
			#获取退的仓库类型
			SELECT c103_flag_type INTO flag_wh_type
			FROM ctlm103,inv22
			WHERE i22_id = ids
			AND c103_id = i22_id_wh;
			
			#若退的是不合格品库 库存变成不合格
			update inv31,inv22 
			SET i31_flag_inv = 'in',
			i31_id_wh = i22_id_wh,
			i31_id_loc = i22_id_loc,
			i31_var_cata = '',
			i31_flag_checked = IF(flag_wh_type = '4','N',i31_flag_checked)
			where i31_id = i22_var_serial 
			and i22_id = ids;
			
			#找该流水号最近一次领料记录对应的领料单
			SELECT i22_id_main INTO id_picking_order
			FROM inv22 WHERE i22_var_source = '领料单'
			AND i22_var_serial = p_var_serial
			ORDER BY i22_date_create DESC 
			LIMIT 1;
			
			IF IFNULL(id_picking_order,'') != '' THEN
				#根据领料单id、物料编码和批号更新最新一次的生产损耗记录
				SELECT rec01_id INTO rec_id 
				FROM record01,inv22
				WHERE i22_id = ids
				AND rec01_id_source = id_picking_order
				AND rec01_id_item = i22_id_item
				AND rec01_var_batch = i22_var_batch
				ORDER BY rec01_date_create DESC
				LIMIT 1;
				
				UPDATE record01
				SET rec01_dec_return = rec01_dec_return + dec_weight,
				rec01_dec_loss = rec01_dec_pick - rec01_dec_formula - rec01_dec_return
				WHERE rec01_id = rec_id;
				
				#将此次退库接收操作记录在对应的领料单中
				UPDATE out115,inv22
				SET o115_id_back_loc = i22_id_loc,
				o115_id_back_i22 = i22_id,
				o115_flag_received = 'Y'
				WHERE o115_id_o11 = id_picking_order
				AND o115_var_serial = p_var_serial
				AND i22_id = ids;

			END IF;
					
			#插入库存表
			select count(i21_id) into counts from inv21 where inv21.i21_var_serial = p_var_serial;

				if counts= 0 then
					insert into inv21(i21_id,i21_flag_state,i21_date_create,i21_id_source,i21_var_bill_ori,
					i21_var_busi_no,i21_id_item,i21_var_item,i21_code_item,i21_var_serial,
					i21_var_batch,i21_dec_tag,i21_dec_tare,i21_dec_in,i21_dec_end,
					i21_dec_move,i21_dec_out,	i21_dec_in_qty,i21_dec_end_qty,i21_dec_move_qty,
					i21_dec_out_qty,i21_id_unit,i21_date_prod,i21_date_expire,i21_id_sup,
					i21_id_wh,i21_id_loc,i21_id_container,i21_int_bag_count,i21_flag_half,
					i21_flag_checked,i21_flag_occu,i21_flag_closed,i21_id_terminal,i21_flag_tag,
					i21_id_pack,i21_var_batch_prod,i21_var_cata,i21_id_supp,i21_var_supp,
					i21_id_customer,i21_id_creater,i21_date_recheck,i21_var_source_item,i21_var_remark,
					i21_dec_pack_gross,i21_var_batch_semi,i21_var_box_pattern,i21_dec_box_qty,i21_date_check,
					i21_date_check_expire,i21_var_checker,i21_var_maker,i21_var_gravity,i21_date_last_check,
					i21_var_quality_desc)
					select uuid_short(),'正常',i22_date_create,ids,'',
					'',i22_id_item,i22_var_item,i22_code_item,i22_var_serial,
					i22_var_batch,i22_dec_tag,i22_dec_tare,i22_dec_weight,i22_dec_weight,
					0,0,round(i22_dec_weight/i22_dec_tag,2),round(i22_dec_weight/i22_dec_tag,2),0,
					0,i22_id_unit,i22_date_prod,i22_date_expire,IFNULL(i22_id_sup,''),
					i22_id_wh,i22_id_loc,i22_id_container,i22_int_bag_count,i31_flag_half,
					i31_flag_checked,'N','N',i22_id_terminal,i22_flag_tag,
					i22_id_pack,i22_var_batch_prod,'',IFNULL(i22_id_supp,''),i22_var_supp,
					i22_id_customer,i22_id_creater,i31_date_recheck,i31_var_source_item,i22_var_remark,
					i31_dec_pack_gross,i31_var_batch_semi,i31_var_box_pattern,i31_dec_box_qty,i31_date_check,
					i31_date_check_expire,i31_var_checker,i31_var_maker,i31_var_gravity,i31_date_last_check,
					i31_var_quality_desc
					from inv22,ctlm102,inv31
					where inv22.i22_id=ids 
					and ctlm102.c102_id_item=inv22.i22_code_item 
					and i31_id=i22_var_serial
					AND i22_dec_weight > 0;	
							
					#从检验表获取是否检验合格，
		
				else								
						set int_result = 1; #重复扫码
				end if;

	END IF;
end//
DELIMITER ;