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

DELIMITER //
CREATE PROCEDURE `proc_deal_c508`()
BEGIN
	#Routine body goes here...
	declare var_result VARCHAR(60) DEFAULT '';
	DECLARE result_code varchar(1) DEFAULT '0'; 
	DECLARE count INT;
	DECLARE done INT;
	
	START TRANSACTION;

	
	select count(1) into count from ctlm508 where c508_var_result <> '' and c508_flag_state <> '删除';
	
	if count > 0 THEN
	set var_result = '数据异常,无法导入';
	SET result_code = '1';
	else
	
	
	INSERT INTO ctlm102(c102_id,c102_date_create,c102_id_creater,c102_flag_state,c102_int_line,
	c102_date_audit,c102_id_item,c102_var_item,c102_int_level,c102_flag_leaf,
	c102_id_flow,c102_date_audit_act,c102_id_item_type,c102_id_unit,c102_flag_check,
	c102_dec_pack_w,c102_dec_pack_t,c102_id_pack,c102_dec_wh_min,c102_dec_wh_max,
	c102_dec_limit_prod,c102_dec_limit_unpack,c102_dec_limit_stew,c102_var_store,c102_flag_out,
	c102_flag_recycle,c102_dec_pallet,c102_int_expire,c102_int_recheck,c102_flag_stock_un,
	c102_flag_check_rate,c102_var_en,c102_var_sap)
	select 
	c508_id_item,
	NOW(),
	c508_id_creater,
	'正常',
	'1',
	
	NOW(),
	c508_id_item,
	c508_var_item,
	'0',
	'N',
	
	'N',
	NOW(),
	c508_id_item_type,
	c508_id_unit,
	c508_flag_check,
	
	c508_dec_pack_w,
	c508_dec_pack_t,
	c508_id_pack,
	c508_dec_wh_min,
	c508_dec_wh_max,
	
	c508_dec_limit_prod,
	c508_dec_limit_unpack,
	c508_dec_limit_stew,
	c508_var_store,
	c508_flag_out,
	
	c508_flag_recycle,
	c508_dec_pallet,
	c508_int_expire,
	c508_int_recheck,
	c508_flag_stock_un,
	
	c508_flag_check_rate,
	c508_var_en,
	c508_var_sap
	 
	FROM ctlm508 
	WHERE NOT EXISTS(SELECT 1 FROM ctlm102 WHERE c102_id = c508_id_item ) 
	AND c508_flag_state <> '删除';
	
	update ctlm508 set c508_flag_state = '删除';
	update ctlm508_i set c508_flag_state = '删除';
	
	END IF;

	IF result_code = '1' THEN 
  ROLLBACK;

	     select CONCAT(result_code,'|',var_result) as result_code ;
    ELSE
       COMMIT;
			 select CONCAT(result_code,'|导入成功') as result_code;
	END IF;

END//
DELIMITER ;

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

DELIMITER //
CREATE PROCEDURE `proc_update_comp_mix`(
	IN `ids` varchar(60) character set utf8  COLLATE utf8_general_ci,
	IN `batch` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `var_state` VARCHAR(50) character set utf8  COLLATE utf8_general_ci,
	IN `var_remark` VARCHAR(50) character set utf8  COLLATE utf8_general_ci,
	IN `id_user` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `batch_prod` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `dec_inventory` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `dec_discharge` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `dec_water` DECIMAL(20,7),
	IN `flag_continue` VARCHAR(10) character set utf8  COLLATE utf8_general_ci,
	IN `date_start` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `date_end` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN channel_loss VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN sampling_loss VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN balanced_yield VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN cleaning_type VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN allowable_range_text VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN allowable_result_text VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN confirmor VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN confirmDate VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN c407Id VARCHAR(60) character set utf8  COLLATE utf8_general_ci)
begin
	declare id VARCHAR(60);
	declare cfg_var VARCHAR(60);
	declare count1 int;
	DECLARE id_main VARCHAR(60);
	#最后一次收料人
	DECLARE last_receive_user VARCHAR(60);
	#最后一次收料时间
	DECLARE last_receive_time datetime;
 
	DECLARE result_code varchar(1) DEFAULT '0'; 
	DECLARE msg TEXT;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code = '1';
	-- 获取异常code,异常信息
	GET DIAGNOSTICS CONDITION 1
	msg = MESSAGE_TEXT;
	START TRANSACTION; 

	select pr02_id into id from prod02 where pr02_bill_no = ids and pr02_flag_state = '正常';
	select s302_var_cfg into cfg_var from sys302 WHERE s302_id_cfg = 'Cfg_finished_product' and s302_flag_state = '正常';
		
	update prod023,prod02
	set pr023_flag_mix = 'Y',
	pr023_var_batch_prod=batch_prod,
	pr023_dec_inventory_act = dec_inventory,
	pr023_dec_discharge_act = dec_discharge,
	pr023_dec_water=dec_water,
	pr023_flag_continuous_production = flag_continue,
	pr023_date_equipment_cleaning_start = date_start,
	pr023_date_equipment_cleaning_end = date_end 
	where  prod023.pr023_id_main=prod02.pr02_id
	and prod023.pr023_var_batch=batch
	and prod02.pr02_bill_no=ids;
		
	###生成生产批记录主表
	SET id_main = UUID();
	#获取最后一次收料记录的操作人和时间  作为批记录的拼车人 拼车时间
	SELECT r01_date_create,r01_id_creater INTO last_receive_time,last_receive_user
	FROM receive01
	WHERE r01_flag_state = '正常'
	AND r01_var_task = ids
	AND r01_var_batch = batch
	ORDER BY r01_date_create DESC LIMIT 1;
	
	#插入额外信息表
	INSERT INTO process_batch_record_main_extra (id_pr07,channel_loss,sampling_loss,balanced_yield,cleaning_type,
	allowable_range_text,allowable_result_text,confirmor,confirmDate)
	SELECT id_main,channel_loss,sampling_loss,balanced_yield,cleaning_type,
	allowable_range_text,allowable_result_text,confirmor,confirmDate;
	
	INSERT INTO prod07 (pr07_id,pr07_date_create,pr07_id_creater,pr07_flag_state,pr07_int_line,
	pr07_date_bill,pr07_id_clerk,pr07_id_source_table,pr07_id_source,pr07_bill_no,
	pr07_id_table,pr07_int_year,pr07_id_audit,pr07_date_audit,pr07_int_month,
	pr07_id_dept,pr07_id_com,pr07_id_main,pr07_id_flow,pr07_date_audit_act,
	pr07_id_unit,pr07_var_formula,pr07_id_item,pr07_var_item,pr07_var_batch, 
	pr07_dec_qty,pr07_date_prod,pr07_date_expire,pr07_int_valid,pr07_flag_continuous_production,
	pr07_dec_inventory_act,pr07_dec_discharge_act,pr07_date_equipment_cleaning_start,pr07_date_equipment_cleaning_end,pr07_flag_equipment_cleaning_type,
	pr07_var_batch_prod,pr07_dec_water,pr07_var_sap,pr07_var_img_list)
	select id_main,
	NOW(),
	a.pr02_id_creater,
	'正常',
	a.pr02_int_line,
	
	NOW(),
	a.pr02_id_clerk,
	'pr02',
	id,
	a.pr02_bill_no,
	
	'pr07',
	a.pr02_int_year,
	last_receive_user,
	last_receive_time,
	a.pr02_int_month,
	
	a.pr02_id_dept,
	SUBSTRING_INDEX(batch, ' ', 1),
	'',
	a.pr02_id_formula,
	NOW(),
	
	a.pr02_id_unit,
	'',
	a.pr02_id_item,
	c102_var_item,
	batch,
	
	0 + CAST(a.pr02_dec_batch_w as CHAR) as pr07_dec_qty,
	DATE_FORMAT(a.pr02_date_plan,'%Y-%m-%d'),
	DATE_FORMAT(DATE_ADD(DATE(a.pr02_date_plan),INTERVAL c102_int_expire DAY),'%Y-%m-%d'),
	c102_int_expire,
	flag_continue,
	
	dec_inventory,
	dec_discharge,
	date_start,
	date_end,
	'普通水包油型',
	
	batch_prod,
	dec_water,
	c102_var_sap,
	c407_var_storage
	from prod02 a,ctlm102,ctlm407
	where a.pr02_id = id
	and a.pr02_id_item = c102_id
	AND c407_id = c407Id;
		
	select count(1) into count1 from prod021 where pr021_flag_comp <> 'Y' and pr021_id_main = id;

	IF count1 = 0 THEN
		UPDATE weight01 
		set w01_flag_mix = 'Y' 
		where var_task_supple = ids
		and var_batch_supple = batch;
		
		update prod02 
		set pr02_flag_comp = 'Y',
		pr02_flag_state_comp = var_state,
		pr02_var_forceend = var_remark 
		where pr02_id = id 
		and not EXISTS(select 1 from prod023 a where a.pr023_id_main= prod02.pr02_id and (IFNULL(pr023_flag_mix,'N')='N' or pr023_flag_mix = '' )) ;
	END IF;
	
	#生成半成品入库单
	IF count1 = 0 && cfg_var='1' THEN 
		call proc_deal_pr02_inv(id);
	END IF;
	
	#生成批记录明细
	CALL proc_deal_pr02_record(id, ids, batch, id_main);
	
	#将对应的混料罐组 设置为待清洗状态
	UPDATE ctlm407 SET c407_flag_container = '3' WHERE c407_id = c407Id;

	INSERT INTO sys318(s318_id,s318_date_create,s318_id_creater,s318_flag_state,s318_int_line,
	s318_id_table,s318_id_audit,s318_date_audit,s318_id_dept,s318_id_com,
	s318_id_main,s318_var_title,s318_var_content,s318_var_dept_pub,s318_date_valid,
	s318_id_post,s318_id_dept_obj,s318_id_post_obj)
	select
	UUID(),NOW(),id_user,'正常',1,
	's318','',NOW(),pr02_id_dept,pr02_id_com,
	'','任务单完成','',CONCAT('单据号：',ids,',批次：',batch,var_state),DATE_ADD(now(),INTERVAL 7 DAY),
	'','','' 
	from prod02 
	where pr02_id = id 
	and count1 = 0 
	and not EXISTS(select 1 from prod023 a where a.pr023_id_main= prod02.pr02_id and (IFNULL(pr023_flag_mix,'N')='N' or pr023_flag_mix = '' )) ;

	#select msg;
	IF result_code = '1' THEN 
		ROLLBACK;
		select CONCAT(result_code,'|更新失败') as result_code ;
	ELSE
		COMMIT;
		select CONCAT(result_code,'|更新成功') as result_code;
	END IF;
end//
DELIMITER ;

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

DELIMITER //
CREATE PROCEDURE `proc_deal_pr02_record`(
	IN `ids` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `bill_no` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN `var_batch` VARCHAR(60) character set utf8  COLLATE utf8_general_ci,
	IN id_main VARCHAR(60) CHARACTER SET utf8 COLLATE utf8_general_ci)
BEGIN
	DECLARE cur_time datetime;

	SET cur_time = NOW();
	
	INSERT INTO prod071(pr071_id,pr071_date_create,pr071_id_creater,pr071_flag_state,pr071_int_line,
	pr071_date_bill,pr071_id_clerk,pr071_id_source_table,pr071_id_source,pr071_bill_no,
	pr071_id_table,pr071_int_year,pr071_id_audit,pr071_date_audit,pr071_int_month,
	pr071_id_dept,pr071_id_com,pr071_id_main,pr071_id_flow,pr071_date_audit_act,
	pr071_id_item,pr071_var_item,pr071_id_manufacturer,pr071_var_manufacturer,pr071_date_expire,
	pr071_dec_stand,pr071_dec_weight,pr071_id_scale,pr071_var_scale,pr071_date_expire_scale,
	pr071_id_unit,pr071_var_batch,pr071_var_sap,pr071_var_batch_prod)
	select UUID(),
	NOW(),
	w01_id_creater,
	'正常',
	0, 
	
	NOW(),
	'',
	'w01',
	'',
	w01_id_terminal,
	
	'pr071',
	0,
	w01_id_audit,
	NOW(),
	0,
	
	'',
	'',
	id_main,
	'',
	NOW(),
	
	w01_id_item,
	c102_var_item,
	t_i31.i31_id_sup,
	t_i31.c202_var_supp,
	t_i31.i31_date_expire,
	
	0 + CAST(w01_dec_stand as CHAR) as pr071_dec_stand,
	SUM(0 + CAST(w01_dec_w as CHAR)) as pr071_dec_weight,
	w01_var_scale,
	'',
	NULL,
	
	w01_id_unit_w,
	w01_var_item_batch,
	c102_var_sap,
	t_i31.i31_var_batch_prod
	from weight01 
	LEFT JOIN ctlm102 ON w01_id_item  = c102_id
	LEFT JOIN (select i31_id,i31_id_sup,i31_var_batch_prod,c202_var_supp,DATE_FORMAT(i31_date_expire,'%Y-%m-%d') AS i31_date_expire FROM inv31 LEFT JOIN ctlm202 on i31_id_sup = c202_id) t_i31 ON w01_var_item_serial = t_i31.i31_id
	where var_task_supple = bill_no  
	AND var_batch_supple = var_batch
	and w01_flag_valid = '正常' 
	and w01_flag_state = '正常' 
	GROUP BY w01_id_item,c102_var_item,c102_var_sap,w01_var_item_batch,w01_id_terminal,w01_id_audit,w01_dec_stand,w01_var_scale,w01_id_unit_w,w01_id_creater,t_i31.i31_id_sup,t_i31.c202_var_supp,t_i31.i31_date_expire,t_i31.i31_var_batch_prod;
	
	INSERT INTO prod072(pr072_id,pr072_date_create,pr072_id_creater,pr072_flag_state,pr072_int_line,
	pr072_date_bill,pr072_id_clerk,pr072_id_source_table,pr072_id_source,pr072_bill_no,
	pr072_id_table,pr072_int_year,pr072_id_audit,pr072_date_audit,pr072_int_month,
	pr072_id_dept,pr072_id_com,pr072_id_main,pr072_id_flow,pr072_date_audit_act,
	pr072_var_title,pr072_date_begin,pr072_date_end,pr072_var_custom,pr072_var_record
	)
	select UUID(),
	NOW(),
	pr012_id_creater,
	'正常',
	pr012_int_line,
	
	NOW(),
	pr012_id_clerk,
	'pr012',
	pr012_id,
	pr012_bill_no,
	
	'pr072',
	pr012_int_year,
	pr012_id_audit,
	NOW(),
	pr012_int_month,
	
	pr012_id_dept,
	pr012_id_com,
	id_main,
	bbb.c701_flag_leaf,
	NOW(),
	
	pr012_var_title,
	bbb.c701_date_begin,
	bbb.c701_date_end,
	bbb.c701_var_custom,
	IFNULL(bbb.record,'')
	from prod02,prod012 LEFT JOIN (
SELECT '' AS record,c701_id_flow AS line1,c701_date_begin,c701_date_end,c701_var_custom,c701_flag_leaf FROM ctlm701 WHERE c701_id_task = ids AND c701_var_batch = var_batch AND c701_flag_leaf = 'custom') bbb ON pr012_int_line =
bbb.line1 
	where pr012_id_main = pr02_id_formula
	and pr02_id = ids;
	
	INSERT INTO prod073(pr073_id,pr073_date_create,pr073_id_creater,pr073_flag_state,pr073_int_line,
	pr073_date_bill,pr073_id_clerk,pr073_id_source_table,pr073_id_source,pr073_bill_no,
	pr073_id_table,pr073_int_year,pr073_id_audit,pr073_date_audit,pr073_int_month,
	pr073_id_dept,pr073_id_com,pr073_id_main,pr073_id_flow,pr073_id_item,
	pr073_var_item,pr073_var_item_batch,pr073_var_phase,pr073_date_mix,pr073_id_container,
	pr073_dec_weight,pr073_id_unit,pr073_var_sap,pr073_var_item_batch_prod)
	SELECT UUID(),NOW(),c01_id_creater,'正常',0,
	NOW(),c01_id_clerk,'c01','','',
	'pr073',YEAR(NOW()),'',NOW(),MONTH(NOW()),
	'','',id_main,'',c01_id_item,
	c102_var_item,c01_var_item_batch,c01_var_phase,c01_date_create,c01_id_container,
	SUM(0 + CAST(c01_dec_weight AS CHAR)),c01_id_unit,c102_var_sap,i31_var_batch_prod
	FROM check01 
	LEFT JOIN ctlm102 ON c01_id_item = c102_id_item 
	LEFT JOIN inv31 ON c01_var_item_serial = i31_id
	WHERE c01_var_task = bill_no 
	AND c01_var_batch = var_batch
	AND c01_flag_state = '正常'
	AND c01_flag_valid <> 'N'
	GROUP BY c01_id_creater,c01_id_clerk,c01_id_item,c102_var_item,c01_var_item_batch,c01_var_phase,c01_date_create,c01_id_container,c01_id_unit,c102_var_sap,i31_var_batch_prod;
	
	INSERT INTO prod075(pr075_id,pr075_date_create,pr075_id_creater,pr075_flag_state,pr075_int_line,
	pr075_date_bill,pr075_id_clerk,pr075_id_source_table,pr075_id_source,pr075_bill_no,
	pr075_id_table,pr075_int_year,pr075_id_audit,pr075_date_audit,pr075_int_month,
	pr075_id_dept,pr075_id_com,pr075_id_main,pr075_id_flow,pr075_var_record,
	pr075_var_deal)
	SELECT UUID(),cur_time,c702_id_creater,'正常',c702_int_line,
	cur_time,c702_id_creater,'c702',c702_id,'',
	'pr075',0,c702_id_audit,c702_date_audit,0,
	'','',id_main,'',c702_var_record,
	c702_var_deal
	FROM ctlm702
	WHERE c702_flag_state = '正常'
	AND c702_id_task = ids
	AND c702_var_batch = var_batch
	AND c702_flag_type = '0'
	ORDER BY c702_int_line;
	
	INSERT INTO prod076(pr076_id,pr076_date_create,pr076_id_creater,pr076_flag_state,pr076_int_line,
	pr076_date_bill,pr076_id_clerk,pr076_id_source_table,pr076_id_source,pr076_bill_no,
	pr076_id_table,pr076_int_year,pr076_id_audit,pr076_date_audit,pr076_int_month,
	pr076_id_dept,pr076_id_com,pr076_id_main,pr076_id_flow,pr076_var_record,
	pr076_var_deal)
	SELECT UUID(),cur_time,c702_id_creater,'正常',c702_int_line,
	cur_time,c702_id_creater,'c702',c702_id,'',
	'pr075',0,c702_id_audit,c702_date_audit,0,
	'','',id_main,'',c702_var_record,
	c702_var_deal
	FROM ctlm702
	WHERE c702_flag_state = '正常'
	AND c702_id_task = ids
	AND c702_var_batch = var_batch
	AND c702_flag_type = '1'
	ORDER BY c702_int_line;
	
	INSERT INTO prod074(pr074_id,pr074_date_create,pr074_id_creater,pr074_flag_state,pr074_int_line,
	pr074_id_table,pr074_id_main,pr074_var_desc,pr074_var_result)
	SELECT UUID(),pr074_date_create,pr074_id_creater,'正常',pr074_int_line,
	pr074_id_table,id_main,pr074_var_desc,pr074_var_result
	FROM prod074 
	WHERE pr074_id_source = ids
	AND pr074_flag_state = '正常';
	
	/*
	update prod071,(select c110_id_item,c110_var_batch,c110_id_manufacturer,c110_date_valid,c202_var_supp from ctlm110 LEFT JOIN ctlm202 ON c110_id_manufacturer = c202_id_supp) a 
	set pr071_id_manufacturer = a.c110_id_manufacturer,pr071_var_manufacturer = a.c202_var_supp,pr071_date_expire = DATE_FORMAT(a.c110_date_valid,'%Y-%m-%d')
	where pr071_id_main = id_main  
	and pr071_id_item = a.c110_id_item 
	and pr071_var_batch = a.c110_var_batch;
	*/
	
	update prod071,(select c301_var_ip,c302_id_scale,c302_code_scale,c302_date_check from ctlm302,ctlm301 where c302_id_main = c301_id) a 
	set pr071_var_scale = a.c302_code_scale,pr071_date_expire_scale = DATE_FORMAT(a.c302_date_check,'%Y-%m-%d')
	where pr071_id_main = id_main 
	and pr071_bill_no = a.c301_var_ip 
	and pr071_id_scale = a.c302_id_scale;
	
	/*
	update prod071,ctlm102 
	set pr071_var_item = c102_var_item 
	where pr071_id_main = id_main 
	and pr071_id_item = c102_id;
	*/
	
	update prod077 set pr077_id_main=id_main where pr077_id_source=ids AND pr077_id_com = var_batch and pr077_flag_state="正常";
	
END//
DELIMITER ;

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

DELIMITER //
CREATE PROCEDURE `proc_deal_c503_check`()
BEGIN
	#Routine body goes here...
	declare count varchar(60);
	
	
	UPDATE ctlm503 set c503_var_result = '';
	update ctlm503 set c503_var_result = CONCAT(c503_var_result,'单据号已存在',',') where EXISTS(select 1 FROM prod02 WHERE pr02_bill_no = c503_bill_no and pr02_flag_state <> '删除');
	update ctlm503 set c503_var_result = CONCAT(c503_var_result,'产品编码不存在',',') where NOT EXISTS (SELECT 1 FROM ctlm102 WHERE c102_id = c503_id_item);
	update ctlm503 set c503_var_result = CONCAT(c503_var_result,'配方不存在',',') where NOT EXISTS (SELECT 1 FROM prod01 WHERE pr01_id_item = c503_id_item AND pr01_var_code = c503_var_code AND pr01_var_ver = c503_var_version AND pr01_flag_state <> '删除');
	update ctlm503 set c503_var_result =CONCAT(c503_var_result,'单据号不能为空',',') where IFNULL(c503_bill_no,'') = '';
	update ctlm503 set c503_var_result =CONCAT(c503_var_result,'产品编码不能为空',',') where IFNULL(c503_id_item,'') = '';
	update ctlm503 set c503_var_result =CONCAT(c503_var_result,'配方编码不能为空',',') where IFNULL(c503_var_code,'') = '';
	update ctlm503 set c503_var_result =CONCAT(c503_var_result,'终端不存在',',') where IFNULL(c503_var_terminal,'') <> '' AND NOT EXISTS(SELECT 1 FROM ctlm301 WHERE c301_var_terminal = c503_var_terminal AND c301_flag_state = '正常');
	update ctlm503 set c503_var_result = CONCAT(c503_var_result,'计划投产量不能为空',',') where (c503_dec_plan =0 or isnull(c503_dec_plan));
	update ctlm503 set c503_int_batch = 1 where IFNULL(c503_int_batch,0)=0;
	
END//
DELIMITER ;

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

DELIMITER //
CREATE PROCEDURE `proc_deal_c503`()
BEGIN
	#Routine body goes here...

	declare var_result VARCHAR(60) DEFAULT '';
	DECLARE result_code varchar(1) DEFAULT '0'; 
	DECLARE count INT;
	
	START TRANSACTION;
	
	#select count(1) into count from ctlm503 where c503_var_result <> '' and c503_flag_state <> '删除';
	set count = 0;
	
	if count > 0 THEN
	set var_result = '数据异常,无法导入';
	SET result_code = '1';
	else
	
	insert into prod02(pr02_id,pr02_date_create,pr02_id_creater,pr02_flag_state,pr02_id_main,
	pr02_id_formula,pr02_date_plan,pr02_var_remark,pr02_id_unit,pr02_id_item,
	pr02_dec_f_w,pr02_dec_plan,pr02_dec_out,pr02_dec_counts,pr02_int_batch,
	pr02_dec_batch_w,pr02_var_batchs,pr02_id_terminal,pr02_date_prod,pr02_dec_prod,
	pr02_bill_no,pr02_id_pack,pr02_flag_append,pr02_flag_comp,pr02_flag_comp_formula,
	pr02_int_qty,pr02_flag_sec,pr02_flag_wCheck,pr02_flag_afterSec,pr02_flag_down,
	pr02_dec_erp,pr02_date_bill,pr02_id_table,pr02_flag_type,pr02_flag_pre,
	pr02_flag_lack)
select        
UUID(),NOW(),c503_id_creater,'录入','',
pr01_id,c503_date_plan,c503_var_remark,pr01_id_unit,c503_id_item,
pr01_dec_sum,c503_dec_plan,(c503_dec_plan/pr01_dec_sum),(c503_dec_plan/pr01_dec_sum/c503_int_batch),c503_int_batch,
(c503_dec_plan/c503_int_batch),c503_var_batchs,c301_id,IFNULL(c503_date_prod,NOW()),100,
c503_bill_no,pr01_id_pack,'N','N','N',
'1','N','N','N','N',
c503_bill_no,NOW(),'pr02','N','2',
'04'
from 
( select DISTINCT c503_bill_no,
c503_id_creater,
c503_id, 
c503_date_plan,
c503_var_remark,
c503_id_item,
c503_dec_plan,
c503_var_terminal,
c503_int_batch,
c503_var_batchs,
c503_var_code,
c503_var_version,
c503_date_prod from ctlm503 where c503_var_result= '') tt 
LEFT JOIN ctlm301 on c301_var_terminal = tt.c503_var_terminal and c301_flag_state = '正常'
left join prod01 on pr01_id_item = tt.c503_id_item and pr01_var_code = tt.c503_var_code and pr01_var_ver = tt.c503_var_version and pr01_flag_state <> '删除';

	INSERT INTO ctlm503_hst SELECT * FROM ctlm503 WHERE c503_var_result = '';
	DELETE FROM ctlm503 WHERE c503_var_result = '';
	DELETE FROM ctlm503_i;

	END IF;

IF result_code = '1' THEN 
  ROLLBACK;

	     select CONCAT(result_code,'|',var_result) as result_code ;
    ELSE
       COMMIT;
			 select CONCAT(result_code,'|导入成功') as result_code;
	END IF;
	
	

END//
DELIMITER ;