SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ctlm533
-- ----------------------------
DROP TABLE IF EXISTS `ctlm533`;
CREATE TABLE `ctlm533`  (
  `c533_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c533_date_create` datetime(0) NULL DEFAULT NULL,
  `c533_bill_no` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`c533_id`) USING BTREE,
  INDEX `billNo`(`c533_bill_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ctlm533_hst
-- ----------------------------
DROP TABLE IF EXISTS `ctlm533_hst`;
CREATE TABLE `ctlm533_hst`  (
  `c533_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c533_date_create` datetime(0) NULL DEFAULT NULL,
  `c533_bill_no` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`c533_id`) USING BTREE,
  INDEX `billNo`(`c533_bill_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ctlm543
-- ----------------------------
DROP TABLE IF EXISTS `ctlm543`;
CREATE TABLE `ctlm543`  (
  `c543_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id',
  `c543_date_create` datetime(0) NULL DEFAULT NULL,
  `c543_bill_no` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单据号',
  PRIMARY KEY (`c543_id`) USING BTREE,
  INDEX `billNO`(`c543_bill_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ctlm543_hst
-- ----------------------------
DROP TABLE IF EXISTS `ctlm543_hst`;
CREATE TABLE `ctlm543_hst`  (
  `c543_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c543_date_create` datetime(0) NULL DEFAULT NULL,
  `c543_bill_no` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`c543_id`) USING BTREE,
  INDEX `billNo`(`c543_bill_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

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

DELIMITER //
CREATE PROCEDURE `proc_deal_w01`(IN id varchar(60) character set utf8  COLLATE utf8_general_ci,IN var_task VARCHAR(60) character set utf8  COLLATE utf8_general_ci)
BEGIN
	#Routine body goes here...
	
	DECLARE w_id_creater VARCHAR(60);
	declare id_item varchar(60);
	
	declare batchcomplete varchar(60);
		
	declare id_task varchar(60);
	declare id_pr021 varchar(60);

	declare id_source varchar(60);
	declare dec_w decimal(20,7);
	declare dec_p DECIMAL(20,7);
	
	DECLARE dec_out DECIMAL(20,9);
	DECLARE w01_sum DECIMAL(20,7);
	DECLARE pickweight DECIMAL(20,9);
	
	#原任务单号
	DECLARE temp_bill_no VARCHAR(60);
	
	declare var_item_serial VARCHAR(60);
	DECLARE var_scale VARCHAR(60);
	DECLARE id_terminal VARCHAR(60);
	DECLARE var_batch_prod VARCHAR(60);
	DECLARE cfg VARCHAR(60);

	declare dec_weight DECIMAL(20,7);
	declare countrec INTEGER default 0;
	declare int_result integer default 0;
	
	DECLARE id_unit VARCHAR(60);
	
	#补料标记
	DECLARE flag_supple VARCHAR(10);
	DECLARE countsw01 INTEGER DEFAULT 0;
	DECLARE countspr022 INTEGER DEFAULT 0;
	#批次收料状态
	DECLARE flag_check VARCHAR(10) DEFAULT 'N';
	#是否是打印记录
	DECLARE flag_print VARCHAR(60);
	
	DECLARE c_id VARCHAR(60);
	
  DECLARE msg TEXT;	
  DECLARE result_code INTEGER DEFAULT 0; 
     ## DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code = 1;
			
			
			 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    begin
    
     -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
      msg = MESSAGE_TEXT;

            SET result_code = 1;
    end ;
		
      START TRANSACTION; 
			
	SELECT w01_flag_supple
	INTO flag_supple
	FROM weight01
	WHERE w01_id = id;
	
	IF flag_supple = 'Y' THEN
			#补料任务单
			SELECT pr023_flag_comp INTO flag_check
			FROM prod023,weight01,prod06
			WHERE w01_id = id
			AND w01_var_task = pr06_bill_no
			AND pr06_var_batch_ori = pr023_var_batch;

			update weight01 , prod06 
			set w01_id_task = pr06_id,
			w01_bill_no_ori = pr06_var_pr02_bill,
			w01_var_batch_ori = pr06_var_batch_ori,
			w01_flag_check = flag_check
			where w01_id = id
			and w01_var_task = pr06_bill_no;
			
			UPDATE weight01,prod061
			SET w01_id_pr011 = pr061_id_pr011
			WHERE w01_id = id
			AND w01_id_pr21 = pr061_id;
	ELSE
			#正常任务单
			update weight01 ,prod02
			set pr02_flag_step = '02' 
			where weight01.w01_id=id
			and weight01.w01_var_task=prod02.pr02_bill_no;
			
	END IF;
	
	SELECT w01_id_creater,w01_id_item,w01_id_task,w01_id_pr21,w01_id_source,
	w01_dec_weight,w01_var_item_serial,w01_var_scale,w01_id_terminal,var_batch_supple,
	w01_id_com,var_task_supple,w01_id_unit,w01_id_prop
	INTO w_id_creater,id_item,id_task,id_pr021,id_source,
	dec_weight,var_item_serial,var_scale,id_terminal,var_batch_prod,
	batchcomplete,temp_bill_no,id_unit,flag_print
	FROM weight01
	WHERE w01_id = id;
	
	#检查配料记录中 终端批次称量完成的数量是否达到 该批次分配的终端数量
	IF batchcomplete='Y' THEN
	
		SELECT COUNT(1) INTO countsw01 
		FROM weight01 
		WHERE w01_id_task = id_task 
		AND w01_var_batch = var_batch_prod 
		AND w01_id_com = 'Y'
		AND w01_flag_state = '正常'
		AND w01_flag_valid = '正常';
		
		SELECT COUNT(DISTINCT pr022_id_terminal) INTO countspr022 
		FROM prod022 
		WHERE pr022_id_main = id_task;
		
		#所有终端都称完后 将对应的批次记录置为称量完成
		IF countsw01 >= countspr022 THEN
			UPDATE prod023 SET pr023_id_com ='Y'
			WHERE pr023_id_main = id_task
			AND pr023_var_batch = var_batch_prod;
		END IF;
	
	END IF;
	
	#插入生产开始表
	INSERT INTO ctlm543(c543_id,c543_date_create,c543_bill_no)
	SELECT UUID(),NOW(),temp_bill_no;

	select i31_dec_out 
	into dec_out 
	from inv31 
	where i31_id = var_item_serial;
	
	select sum(w01_dec_weight) into dec_w
	from weight01
	where weight01.w01_id_item=id_item
	and weight01.w01_id_task=id_task;

	select sum(w01_dec_weight) into dec_p
	from weight01
	where weight01.w01_id_pr21=id_pr021;

	update out112,out111 set out112.o112_dec_weight_use=dec_w
	where out111.o111_id_pr02_id=id_task
	and out112.o112_id_item=id_item
	and out111.o111_id_main=out112.o112_id_main;
	
	#释放 占用表的占用量 
-- 	update weight01,occupy01 
-- 	set oc01_dec_release = oc01_dec_release + w01_dec_weight,
-- 	oc01_dec_occu = IF(oc01_dec_occu - w01_dec_weight  < 0,0,oc01_dec_occu - w01_dec_weight ) 
-- 	where w01_id_task = oc01_id_pr02 
-- 	and w01_id_item = oc01_id_item 
-- 	and w01_id = id;

	update prod021 set prod021.pr021_dec_act=dec_p
	where prod021.pr021_id=id_pr021; 

	select IFNULL(i31_dec_end,0) into pickweight from inv31 where i31_id = var_item_serial;
	
	#库存
	if LOCATE('store',id_source) > 0 THEN 
	
		#物料平衡率表
		INSERT INTO inv26(i26_id,i26_date_create,i26_flag_state,i26_id_task,i26_var_task,
		i26_id_batch,i26_var_batch,i26_var_serial,i26_id_item,i26_var_item,
		i26_dec_weight,i26_dec_back,i26_dec_last_end,i26_id_weigher,i26_var_weigher,
		i26_id_backer,i26_var_backer,i26_id_unit,i26_dec_ratio)
		SELECT UUID(),NOW(),'正常',id_task,temp_bill_no,
		'',var_batch_prod,var_item_serial,id_item,'',
		dec_weight,IF(LOCATE('autoback',id_source) > 0,IF((pickweight - dec_weight) < 0,0,(pickweight - dec_weight)) ,0),pickweight,w_id_creater,'',
		IF(LOCATE('autoback',id_source) > 0,w_id_creater,''),'',id_unit,ROUND((IF(LOCATE('autoback',id_source) > 0,IF((pickweight - dec_weight) < 0,0,(pickweight - dec_weight)) ,0) + dec_weight) / pickweight * 100,2);
		
		update inv31 
		set i31_dec_end = i31_dec_end - dec_weight,
		i31_dec_out = dec_out + dec_weight,
		i31_dec_loss = i31_dec_in - dec_out - dec_weight
		where i31_id = var_item_serial;
	END IF;
		#####称量数据同步到库存
	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)

	select uuid_short(), w01_date_create, w01_id_creater, w01_flag_state, w01_int_line, 
							w01_date_bill, w01_id_clerk, 'w01', id, id, 
							'i22',	w01_int_year, w01_id_audit, w01_date_audit, w01_int_month,
							w01_id_dept, w01_id_com, '', '', w01_date_audit_act,
							w01_var_item_serial, '', w01_id_item, c102_var_item, w01_id_item, 
							w01_var_serial, w01_var_item_batch, w01_dec_weight, w01_dec_tare, w01_id_unit, 						
							w01_date_create,i31_date_expire,'',c301_id_terminal, '', 
							w01_id_container, 1, 'N', 'Y', 'N', 
							'N', w01_id_terminal, '0', '', w01_var_scale,
							'1', '1', w01_dec_weight, 1, '称量入库',
							'', '','',''
	from weight01,ctlm102,inv31,ctlm301
	where w01_id = id 
	AND w01_id_item = c102_id
	AND w01_var_item_serial = i31_id
	and w01_id_terminal=c301_var_ip;
	
	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)			
	select uuid_short(),'正常',w01_date_create,id,'',
			'',w01_id_item,c102_var_item,w01_id_item,w01_var_serial,
			w01_var_item_batch,w01_dec_weight,w01_dec_tare,w01_dec_weight ,w01_dec_weight ,
			0,0,1,1,0,
			0,w01_id_unit,w01_date_create,i31_date_expire,'',
			c301_id_terminal,'',w01_id_container,1,'N',
			'Y','N','N',w01_id_terminal,'1',
			w01_id_pack,'','','',''	
			from weight01,ctlm102,inv31,ctlm301
			where w01_id = id
			AND w01_id_item = c102_id
			AND w01_var_item_serial = i31_id
			and w01_id_terminal=c301_var_ip;

	insert into inv31(i31_id,i31_flag_state,i31_date_create,i31_id_source,i31_var_bill_ori,i31_var_busi_no,
			i31_id_item,i31_var_item,i31_code_item,i31_var_serial,i31_var_batch,i31_dec_tag,i31_dec_tare,
			i31_dec_in,i31_dec_end,i31_dec_move,i31_dec_out,	i31_dec_in_qty,i31_dec_end_qty,i31_dec_move_qty,i31_dec_out_qty,
			i31_id_unit,	i31_date_prod,i31_date_expire,i31_id_sup,i31_id_wh,i31_id_loc,i31_id_container,i31_int_bag_count,
			i31_flag_half,i31_flag_checked,i31_flag_occu,i31_flag_closed,i31_id_terminal,i31_flag_tag,i31_id_pack,i31_var_batch_prod,
			i31_var_cata,
			i31_id_supp,i31_var_supp,
			i31_flag_inv,i31_dec_loss,i31_id_process,i31_var_process,i31_var_task,
			i31_var_batch_process,i31_flag_child,i31_var_serial_label)			
	select w01_var_serial,'正常',w01_date_create,id,'','',
			w01_id_item,c102_var_item,w01_id_item,w01_var_serial,w01_var_item_batch,w01_dec_weight,w01_dec_tare,
			w01_dec_weight,
			w01_dec_weight,
			0,0,1,1,0,0,
			w01_id_unit,w01_date_create,i31_date_expire,'',c301_id_terminal,'',w01_id_container,1,
			'N','Y','N','N',w01_id_terminal,1,w01_id_pack,'',
			'',
			'','',
			'in',0,w01_process_code,w01_process_name,IF(w01_flag_supple = 'Y',w01_bill_no_ori,w01_var_task),
			IF(w01_flag_supple = 'Y',w01_var_batch_ori,w01_var_batch),'Y',w01_id_flow 
			from weight01,ctlm102,inv31,ctlm301
			where w01_id = id
			AND w01_id_item = c102_id
			AND w01_var_item_serial = i31_id
			and w01_id_terminal=c301_var_ip;	
			
		
	########################
	IF flag_print != '' THEN
		#此数据是给ERP做出库用的 除了换包，其他称量记录写入c518
		INSERT INTO ctlm518(c518_id,c518_var_source,c518_date_create,c518_id_item,c518_var_item,
			c518_var_batch,c518_var_batch_prod,c518_var_serial,c518_dec_qty,c518_id_unit,
			c518_date_prod,c518_date_expire,c518_id_supp,c518_id_loc,c518_id_wh,
			c518_id_wh_obj,c518_id_creater,c518_var_plan_no,c518_var_bill_ori,c518_c504_main_field1,
			c518_c504_main_field2,c518_c504_main_field3,c518_c504_child_field1,c518_c504_child_field2,c518_c504_child_field3,
			c518_c503_field1,c518_c503_field2,c518_c503_field3,c518_date_erp,c518_flag_erp,
			c518_flag_state) 
			SELECT id,'自动领料',NOW(),i31_id_item,i31_var_item,
			i31_var_batch,i31_var_batch_prod,i31_var_serial,dec_weight * convertUnit(LOWER(id_unit), LOWER(c102_id_unit)),c102_id_unit,
			i31_date_prod,i31_date_expire,i31_id_supp,i31_id_loc,i31_id_wh,
			'',w_id_creater,pr02_bill_no,id,'',
			'','','','','',
			pr02_var_erp_field1,pr02_var_erp_field2,pr02_var_erp_field3,NULL,'',
			'正常'
			FROM inv31,ctlm102,prod02
			WHERE i31_id = var_item_serial
			AND i31_id_item = c102_id
			AND pr02_id = id_task;
	END IF;

	#自动领料
	if LOCATE('autoout',id_source) > 0 THEN 
			IF LOCATE('autoback',id_source) = 0 THEN
			
				#没有自动退库的标志时才把在库状态改为out
				update inv31 set i31_flag_inv = 'out' where i31_id = var_item_serial;
				
			END IF;
				
			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_var_remark,i22_var_batch_prod,i22_var_cata,
			i22_flag_erp,i22_date_erp,i22_id_supp,i22_var_supp)
			select id, NOW(), w_id_creater, i21_flag_state, i21_int_line, 
			NOW(), i21_id_clerk, 'pr02', id_task, id, 
			'i22', YEAR(NOW()), i21_id_audit, NOW(), MONTH(NOW()), 
			i21_id_dept, i21_id_com, var_batch_prod,var_batch_prod, i21_date_audit_act, 
			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_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, id_terminal, '1', i21_var_serial, var_scale, 
			i21_flag_tag, '' as i22_flag_bag, i21_dec_end, i21_dec_end_qty, '自动领料', 
			i21_id_pack, i21_id ,'',i21_var_batch_prod,i21_var_cata,
			'',NULL,i21_id_supp,i21_var_supp 
			from inv21
			where i21_var_serial = var_item_serial;

			delete from inv21 where i21_var_serial = var_item_serial;
	end IF;

	#自动退库
	if (LOCATE('autoback',id_source) > 0) then
			update inv31 
			set i31_flag_inv = 'in',
			i31_dec_loss = i31_dec_in - i31_dec_end - i31_dec_out 
			where i31_id = var_item_serial;
			
			SELECT UUID_SHORT()	INTO c_id;
			
			## 先写流水表
			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_var_remark,i22_var_batch_prod,i22_var_cata,
				i22_flag_erp,i22_date_erp,i22_id_supp,i22_var_supp)
			select c_id, NOW(), w_id_creater, i31_flag_state, i31_int_line, 
				NOW(), i31_id_clerk, 'pr02', id_task, id, 
				'i22', YEAR(NOW()), i31_id_audit, NOW(), MONTH(NOW()), 
				i31_id_dept, i31_id_com, var_batch_prod, var_batch_prod, NOW(), 
				i31_var_bill_ori, i31_var_busi_no, i31_id_item, i31_var_item, i31_code_item, 
				i31_var_serial, i31_var_batch, i31_dec_tag, i31_dec_tare, i31_id_unit, 
				i31_date_prod, i31_date_expire, i31_id_sup, i31_id_wh, i31_id_loc, 
				i31_id_container, i31_int_bag_count, 'Y', i31_flag_checked, i31_flag_occu, 
				i31_flag_closed, id_terminal, '0', i31_var_serial, var_scale, 
				i31_flag_tag, '' as i22_flag_tag, i31_dec_end, i31_dec_end_qty, '自动退库', 
				i31_id_pack, i31_id ,'',i31_var_batch_prod,i31_var_cata,
				'',NULL,i31_id_supp,i31_var_supp 
			from inv31
			where i31_id = var_item_serial
			and i31_dec_end > 0;
			
			/*
			INSERT INTO ctlm518(c518_id,c518_var_source,c518_date_create,c518_id_item,c518_var_item,
			c518_var_batch,c518_var_batch_prod,c518_var_serial,c518_dec_qty,c518_id_unit,
			c518_date_prod,c518_date_expire,c518_id_supp,c518_id_loc,c518_id_wh,
			c518_id_wh_obj,c518_id_creater,c518_var_plan_no,c518_var_bill_ori,c518_c504_main_field1,
			c518_c504_main_field2,c518_c504_main_field3,c518_c504_child_field1,c518_c504_child_field2,c518_c504_child_field3,
			c518_c503_field1,c518_c503_field2,c518_c503_field3,c518_date_erp,c518_flag_erp,
			c518_flag_state) 
			SELECT c_id,'自动退库',NOW(),i31_id_item,i31_var_item,
			i31_var_batch,i31_var_batch_prod,i31_var_serial,i31_dec_end * convertUnit(LOWER(i31_id_unit), LOWER(c102_id_unit)),c102_id_unit,
			i31_date_prod,i31_date_expire,i31_id_supp,i31_id_loc,i31_id_wh,
			'',w_id_creater,pr02_bill_no,c_id,'',
			'','','','','',
			pr02_var_erp_field1,pr02_var_erp_field2,pr02_var_erp_field3,NULL,'',
			'正常'
			FROM inv31,ctlm102,prod02
			WHERE i31_id = var_item_serial
			AND i31_id_item = c102_id
			AND pr02_id = id_task
			AND i31_dec_end > 0;
			*/
			
			##	插入到i21
			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)			
			select uuid_short(),'正常',NOW(),id,'',
				'',i31_id_item,i31_var_item,i31_code_item,i31_var_serial,
				i31_var_batch,i31_dec_tag,i31_dec_tare,i31_dec_end ,i31_dec_end ,
				0,0,1,1,0,
				0,i31_id_unit,i31_date_prod,i31_date_expire,IFNULL(i31_id_sup,''),
				i31_id_wh,i31_id_loc,i31_id_container,i31_int_bag_count,'Y',
				i31_flag_checked,'N','N',id_terminal,i31_flag_tag,
				i31_id_pack,i31_var_batch_prod,ifnull(i31_var_cata,''),IFNULL(i31_id_supp,''),i31_var_supp,
				i31_id_customer		
			from inv31
			where i31_id = var_item_serial
			and i31_dec_end > 0;
			
	END if;
	
	#任务单损耗采集
	select ifnull(s302_var_cfg,"1") into cfg from sys302 where s302_id_cfg="cfg_collectloss";
	if cfg="0" then 
			select count(1) into countrec from record01 where rec01_id_pr02=id_task and rec01_id_main=var_item_serial and rec01_var_batch_prod=var_batch_prod;
			
			if countrec>0 then
			
				
				select sum(b.w01_dec_weight) into w01_sum from weight01 a,weight01 b where a.w01_id=id and a.w01_var_item_serial=b.w01_var_item_serial and a.w01_id_task=b.w01_id_task and a.w01_var_batch=b.w01_var_batch;
				update record01,weight01 set rec01_dec_formula=w01_sum,rec01_dec_loss=rec01_dec_pick-w01_sum-rec01_dec_return where rec01_id_pr02=w01_id_task and rec01_id_main=w01_var_item_serial and rec01_var_batch_prod=w01_var_batch and w01_id=id;
			
			else

					insert into record01(rec01_id,rec01_date_create,rec01_id_creater,rec01_flag_state,rec01_int_line,rec01_date_bill,
					rec01_id_clerk,rec01_id_source_table,rec01_id_source,rec01_bill_no,rec01_id_table,rec01_int_year,rec01_id_audit,
					rec01_date_audit,rec01_int_month,rec01_id_dept,rec01_id_com,rec01_id_main,rec01_id_flow,rec01_date_audit_act,
					rec01_id_pr02,rec01_var_task,rec01_var_batch_prod,rec01_id_item_prod,rec01_var_item_prod,rec01_id_item,
					rec01_var_item,rec01_var_batch,rec01_dec_pick,rec01_dec_formula,rec01_dec_return,rec01_dec_loss)
					select UUID(),w01_date_create,w01_id_creater,'正常',1,w01_date_bill,
					w01_id_clerk,'','','','',w01_int_year,w01_id_audit,
					w01_date_audit,w01_int_month,w01_id_dept,w01_id_com,w01_var_item_serial,'',w01_date_audit_act,
					w01_id_task,w01_var_task,w01_var_batch,w01_process_code,w01_process_name,w01_id_item,
					c102_var_item,w01_var_item_batch,pickweight,w01_dec_weight,0,pickweight-w01_dec_weight
					from weight01,ctlm102
					where weight01.w01_id=id and w01_id_item=c102_id;
					
					
			end if;
	end if;
#SELECT msg;
IF result_code = 1  or int_result = 1  THEN 
				ROLLBACK;
				select '1|重复扫码' as result_code ;
    ELSE
        COMMIT;
				select '0|更新成功' as result_code ;
      END IF;
END//
DELIMITER ;