USE [E9_Track7.7]
GO
/****** Object:  StoredProcedure [dbo].[usp_TrackCodeAssociate]    Script Date: 2024-07-04 9:20:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[usp_TrackCodeAssociate]                

    @action INT, --动作， 1：解除关系， 2：替换条码，3: 创建关系, 4: 检验,5: 完工后替换,6：查箱码      

    @scope INT,--范围 , 1: 父级码（外包装），2：子级码（内包装），3：双级（内外包装）,4 全部        

    @code1 VARCHAR(64), --条码1，父级码        

    @code2 VARCHAR(64)='',--条码2，子级码        

    @par1 VARCHAR(64)='', -- 备用参数， 可用 创建关系中的包装类型     

    @user varchar(32)='001',

    @ip   varchar(50)=''

  --WITH ENCRYPTION         

AS         

      

BEGIN        

	DECLARE @result VARCHAR(MAX);--返回参数        

	SET @result='操作成功';        

	declare @ParentJCode nvarchar(64);      

	DECLARE @taskStatus1 INT,@taskStatus2 INT;        

	Declare @AppendCode varchar(64),@TaskNo varchar(64);        

	DECLARE @PackSpecLayer NVARCHAR(20);--当前包装类型       

	declare @TaskNoReplace nvarchar(60);      

	declare @EquipNo nvarchar(20);     

	declare @EquipNo1 nvarchar(20);     

	declare @EquipNo2 nvarchar(20);      

	declare @status int;       

	declare @scanNo varchar(32), @boxNo varchar(32);

	declare @LogDes varchar(1000);     
	
	declare @maxCodeLevel int;

	declare @TaskNo1 nvarchar(20);     

	declare @PackFlag int; 
	--declare @currentCode1Level int;
	declare @topCode varchar(100);

	SELECT @taskStatus1=w.Status ,@AppendCode=c.AppendCode,@TaskNo=w.TaskNo,@PackSpecLayer=w.PkgLayerAction ,@maxCodeLevel=ps.LevelCount

	FROM trc_worktask w JOIN uv_TrackCode c ON w.taskno=c.taskno        
	join TRC_PackSpec ps on w.PackSpecNo=ps.PackSpecNo
	WHERE c.Jcode=@code1; 
     

	IF  @taskStatus1 IS NULL and @action in (2,4)        

	BEGIN        

	 SET @result=@code1+N' 所属任务不存在'        

	 RAISERROR (@result, 12,1);        

	 goto goout;        

	END        

	        

	IF @taskStatus1>5 and @taskStatus1<>13  and @action in (1,2,3,4)        

	BEGIN        

	SET @result=@code1+N' 所属任务已导出关联关系'        

	  RAISERROR (@result,12,1);        

	  goto goout;        

	END        

	SELECT @taskStatus2=w.Status FROM trc_worktask w JOIN uv_TrackCode c ON w.taskno=c.taskno WHERE c.Jcode=@code2;       

	declare @isMaxLevel int;  

	set @isMaxLevel=0;      

	select @isMaxLevel=(case t.Level when LevelCount then 1 else 0 end)        

	from TRC_PackSpec p join TRC_WorkTask w on p.PackSpecNo=w.PackSpecNo        

	join TRC_TrackCode t  on t.TaskNo=w.TaskNo        

	where JCode=@code1;  

	       

	declare @isMinLevel int;        

	set @isMaxLevel=0;        

	select @isMinLevel= Level from TRC_TrackCode where JCode=@code1;        

	declare @currentTaskNo varchar(100);
	
	
	set @currentTaskNo=(select TaskNo from Trc_TrackCode with(nolock) where JCode=@code1);      

	IF @action=1--解除关系        

	BEGIN  

		IF @taskStatus1>=6        

		BEGIN        

		SET @result=@code1+N' 所属任务已导出，不能解除关系'        

		   RAISERROR (@result,12,1);        

		   goto goout;        

		END 
				
		IF isnull( (Select parentjcode From TRC_TrackCode WHERE JCode=@code1),'')!='' and @scope<=4  and @scope<>1      

		BEGIN        

			SET @result=@code1+N' 存在父级码，不允许解除下级'        

			RAISERROR (@result,12,1);        

			goto goout;        

		END   

		IF exists (Select * From TRC_TrackCode with(nolock) WHERE JCode=@code1 and status=4)

		BEGIN        

			SET @result=@code1+N' 已报废，不允许解除操作'        

			RAISERROR (@result,12,1);        

			goto goout;        

		END  

		declare @currentFlag int;
		declare @sanzhuangFanhui bit=1; --解除下级散装码是否返回到原任务
		declare @tempLevelCodes CodeLevelTable; -- Table(JCode varchar(100),Level int,PackFlag int,TaskNo varchar(100),TaskNo1 varchar(100)); --存储码和级别
		declare @tempMaxLevel int;
		declare @releaseAction varchar(100);

		set @currentFlag=(select PackFlag from Trc_TrackCode with(nolock) where JCode=@code1);

		if exists (SELECT * FROM TRC_ParaSet WITH(nolock) WHERE ParaCode='PinxiangCodeToOldTaskWhenReleasePack' and ParaValue='0')
		begin
		 set @sanzhuangFanhui=0;
		end

		insert into @tempLevelCodes(JCode,Level,PackFlag,TaskNo,TaskNo1) 
		select JCode,Level,PackFlag,TaskNo,TaskNo1 from dbo.fn_GetAllSubCodes(@code1);
			   
		set @tempMaxLevel=(select max([Level]) from @tempLevelCodes);

		if @scope<4 and @scope<>1  --解除下级  
		begin 

			set @releaseAction= @code1+'解除下级';

			if @sanzhuangFanhui=1 and @currentFlag in (4,5)
			begin

			    declare @subLevelValue int;

				set @subLevelValue=(select Level-1 from Trc_TrackCode with(nolock) where JCode=@code1);

			    exec Usp_ReturnMultiUnSanzhuangCodeToOrginState @tempLevelCodes,@currentTaskNo,@releaseAction,@subLevelValue;

				exec Usp_ReturnMultiSanzhuangCodeToOrginTask @tempLevelCodes,@currentTaskNo,@releaseAction;
				
			end
			else
			begin
				update t 
				set 
				t.Status=(case isnull(ScanNo2,0) when 0 then 1 else 2 end),
				t.ParentJCode=null,
				t.EquipNo=isnull(EquipNo2,null), 
				t.TaskNo=(case (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=t.JCode) when 0 then null else TaskNo end),
				t.TaskNo1=(case (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=t.JCode) when 0 then null else TaskNo1 end),
				t.EquipNo1=null, 
				t.ScanNo=0,
				t.ScanNo2=0 ,
				PackFlag=(
				            case Level 
							when 1 then 0 
							else (
							        case (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=t.JCode) 
								    when 0 then 0 
									else (
									        case PackFlag 
											when 4 then (case (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=t.JCode and PackFlag=4) when 0 then 2 else PackFlag end) 
											when 5 then (case (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=t.JCode and PackFlag in (3,5)) when 0 then 3 else PackFlag end) 
											else PackFlag 
											end) 
									end) 
							 end),
				t.Remark=(isnull(t.Remark,'')+'->[UnReturn_Sub_'+@releaseAction+']') 
				from TRC_TrackCode t
				where t.ParentJCode=@code1;
			end

			if (select Count(*) from Trc_TrackCode with(nolock) where JCode = @code1 and PackFlag in (4,5) and isnull(TaskNo,'')<>isnull(TaskNo1,''))=0 or @sanzhuangFanhui<>1
			begin
				--把当前的父级码状态给清掉
				update c 
				set Status=1,
				PackFlag=(case (select count(*) from Trc_TrackCode with(nolock) where JCode=c.JCode) when 0 then 0 else PackFlag end),
				ParentJCode=null,
				BoxNo=null,
				EquipNo2=null,
				ScanNo=0,
				TaskNo=(case TaskNo when TaskNo1 then null else TaskNo1 end),
				TaskNo1=(case TaskNo when TaskNo1 then null else TaskNo1 end),
				Remark=isnull(Remark,'')+'->[UnReturnPar_解除下级]' 
				from TRC_TrackCode c
				where  c.JCode=@code1;

				update TRC_TrackCode_StateRecord set IsCancel=1 where JCode=@code1 and ToTask=@currentTaskNo+'_P4' and IsCancel<>1;
			end
			else
			begin
				exec Usp_ReturnSanzhuangCodeToOrginTask @code1,@currentTaskNo,@releaseAction;
			end
			  
			set @LogDes =  '解除下级:'+@code1;

		end    

		else if @scope=4  --解除全部下级  
		begin   
		       set @releaseAction=@code1+'解除全部下级';

			   if @sanzhuangFanhui=1
			   begin

			    --把拼箱内的非散装码处理掉
			    exec Usp_ReturnMultiUnSanzhuangCodeToOrginState @tempLevelCodes,@currentTaskNo,@releaseAction;

			    --当前任务内的非拼箱的正常码再处理掉
				update TRC_TrackCode 
				set Status=1,
				ParentJCode=null,
				EquipNo=null,
				EquipNo1=null,
				EquipNo2=null,
				TaskNo=null,
				TaskNo1=null,
				ScanNo=0,
				ScanNo2=0,
				PackFlag=0,
				Remark=(isnull(Remark,'')+'->[Return_'+@releaseAction+']') 
				where JCode in (select JCode from @tempLevelCodes where PackFlag not in (4,5) or (PackFlag in (4,5) and TaskNo=TaskNo1))
				and TaskNo=@currentTaskNo;

				--3、将散装码都归还到原任务中去				
				exec Usp_ReturnMultiSanzhuangCodeToOrginTask @tempLevelCodes,@currentTaskNo,@releaseAction;


			   end
			   else
			   begin
					update TRC_TrackCode 
						set Status=1,
						ParentJCode=null,
						EquipNo=null,
						EquipNo1=null,
						EquipNo2=null,
						TaskNo=null,
						TaskNo1=null,
						ScanNo=0,
						ScanNo2=0 ,
						PackFlag=0,
						Remark=(isnull(Remark,'')+'->[UnReturn_'+@releaseAction+']') 
					where JCode in (select JCode from @tempLevelCodes) ;
			   end

			  set @LogDes =  '解除全部下级:'+@code1;      

		end 

		else if @scope=5 or @scope=1 --解除上级

			begin

			   set @releaseAction= @code1+'_解除上级';

			   if (select Count(*) from Trc_TrackCode with(nolock) where JCode = @code1 and PackFlag in (4,5) and isnull(TaskNo,'')<>isnull(TaskNo1,''))=0 or @sanzhuangFanhui<>1
			   begin
					update t 
					set Status=(case isnull(ScanNo2,0) when 0 then 1 else 2 end ),
					ParentJCode=null,
					BoxNo=null,
					EquipNo1=null,
					EquipNo=isnull(EquipNo2,null),
					ScanNo2=0 ,
					t.TaskNo=(case (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=t.JCode) when 0 then null else TaskNo end),
					t.TaskNo1=(case (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=t.JCode) when 0 then null else TaskNo1 end),
					Remark=(isnull(Remark,'')+'->[UnPinXiang_'+@releaseAction+']') 
					from TRC_TrackCode t where JCode=@code1;  
			   end
			   else
			   begin
			      --能走到这里说明当前的这个码是别的任务拼箱过来的

				  --1、将散装码都归还到原任务中去				
				  exec Usp_ReturnMultiSanzhuangCodeToOrginTask @tempLevelCodes,@currentTaskNo,@releaseAction;

				  --2、当前任务中的码
				  update TRC_TrackCode 
				  set 
				  Status=(case isnull(ScanNo2,0) when 0 then (case Level when 1 then 0 else 1 end) else 2 end),
				  ParentJCode=(case Level when @tempMaxLevel then null else ParentJCode end), --如果是当前解除下级码的直接子级那么清空父级码，否则要保留
				  EquipNo=isnull(EquipNo2,null),
				  TaskNo=TaskNo1,
				  EquipNo1=(case Level when @tempMaxLevel then null else EquipNo1 end),
				  ScanNo=(case Level when @tempMaxLevel then 0 else ScanNo end),
				  ScanNo2=(case Level when @tempMaxLevel then 0 else ScanNo2 end),
				  PackFlag=1, --恢复散装状态
				  Remark=(isnull(Remark,'')+'->[PinXiang_'+@releaseAction+']') 
				  where JCode in (Select JCode From @tempLevelCodes) and TaskNo=@currentTaskNo;

			   end

				set @LogDes =  '解除上级:'+@code1;    

			end

     
		update trc_worktask set FinishCount=(select count(*) from TRC_TrackCode with(nolock) where TaskNo=@currentTaskNo and Level=1 and status not in (0,4)) where TaskNo=@currentTaskNo; 

		exec usp_LogTrackCode_Asso '解除',@code1,'', @LogDes,@user,@ip,123,@currentTaskNo; 

		exec Usp_TrackCodeExceptionForWMS @code1,@code2,@action,@topCode;

		insert into IT_WMS_PreTransferInfo2PtsV8(JCode1,JCode2,Action,Scope,AddTime) values (@code1,@code2,@action,@scope,SYSDATETIME());

	END        

	ELSE IF @action=2 --替换条码        

	BEGIN  
	
		if isnull(@code2,'')=''
		begin
			SET @result=N'  新码为空，不能替换'        

			RAISERROR (@result,12,1);        

			goto goout; 
		end

		if @scope=1 and @code1=@code2    

		  begin        

			  SET @result=@code2+N'  新旧替换码不能相同'        

			  RAISERROR (@result,12,1);        

			  goto goout;        

		  end  

		  if @scope=1 and (exists (select * from Trc_TrackCode with(nolock) where ParentJCode=@code2))

		  begin        

			  SET @result=@code2+N'  存在子码，不能替换'        

			  RAISERROR (@result,12,1);        

			  goto goout;        

		  end  

		if exists(select * from TRC_TrackCode with(nolock) where JCode=@code1 and (Status=0 or Status=4))   
		begin        

			SET @result=@code1+N'  未使用或已废弃,不能替换'        

			RAISERROR (@result,12,1);        

			goto goout;        

		end  

		if exists(select * from TRC_TrackCode with(nolock) where JCode=@code2 and isnull(ParentJCode,'')<>'')   
		begin        

			SET @result=@code2+N'  已扫描,不能替换'        

			RAISERROR (@result,12,1);        

			goto goout;        

		end  

		if exists(select * from TRC_TrackCode with(nolock) where JCode=@code2 and Status=4)     
		begin        

			SET @result=@code2+N'  已废弃,不能替换'        

			RAISERROR (@result,12,1);        

			goto goout;        

		end  
		
		if @scope=2 and ( exists(select * from TRC_TrackCode with(nolock) where JCode=@code2 and (Status=0 or Status=9)))      
		begin        

			SET @result=@code2+N'  不存在或已扫描，不能替换'        

			RAISERROR (@result,12,1);        

			goto goout;        

		end     
		
		if @scope=1 and ( not exists(select * from TRC_TrackCode with(nolock) where JCode=@code2) )     

		  begin        

			  SET @result=@code2+N'  不存在，不能替换'        

			  RAISERROR (@result,12,1);        

			  goto goout;        

		  end  

	   --如果还没有建立关联关系，不能替换       

	   if  exists(select JCode from TRC_TrackCode where JCode=@code1 and (taskNO is  null or ( parentJCode is null and level=1)))      
		begin            

		  SET @result=@code1+N'  尚未建立关联关系，不能替换'        

		  RAISERROR (@result, 12, 1);        

		  goto goout;        

		end  
		
		declare @replaceCodeLevel int;

		select @status=Status,@parentJCode =ParentJCode,@TaskNoReplace=TaskNo,@replaceCodeLevel=[Level], @EquipNo=EquipNo,@EquipNo1=EquipNo1,@EquipNo2=EquipNo2,@scanNo=ScanNo,@boxNo=BoxNo ,@TaskNo1=TaskNo1,@PackFlag=PackFlag from TRC_TrackCode where JCode=@code1;      

		if @scope=1  --内包装替换：标签替换      
		begin      

		  update TRC_TrackCode set ParentJCode=@code2 where ParentJCode=@code1;      

		  update TRC_TrackCode SET  Status= @status,ParentJCode=@parentJCode, TaskNo=@TaskNoReplace,TaskNo1=@TaskNo1,EquipNo=@EquipNo,EquipNo1=@EquipNo1,EquipNo2=@EquipNo2,PackFlag=@PackFlag,ScanNo=@scanNo,BoxNo=@boxNo,EditOn=SYSDATETIME(),Remark=isnull(Remark,'')+'->标签替换:将原有'+@code1+'替换掉' where JCode=@code2;       

		  update TRC_TrackCode set ParentJCode = null,Status=4,PackFlag=0,Remark=isnull(Remark,'')+'->标签替换:被'+@code2+'替换' where JCode=@code1;  

		  set @LogDes ='追溯码内包装替换:' + @code1+'被替换成'+@code2+','+@code1+'码废弃同时自身信息及上下级关系被'+@code2+'取代';      

		end      
		else  if @scope=2 --外包装替换 ：实物替换[场景是拿了一个实物中包直接替换了另外一个旧的实物中包，例如一个新的中包替换了一个旧的中包，新的中包是带着自己的下级的，原有的中包和内部的自己实物都被搬走了]     
		begin      

		   update TRC_TrackCode SET ParentJCode=@parentJCode,Status=2,TaskNo=@TaskNoReplace,TaskNo1=@TaskNo1,EquipNo=@EquipNo,EquipNo1=@EquipNo1,EquipNo2=@EquipNo2,PackFlag=@PackFlag,ScanNo=@scanNo,BoxNo=@boxNo,Remark=isnull(Remark,'')+'->整包替换:将原包'+@code1+'替换掉' where JCode=@code2;  

		   update Trc_TrackCode Set TaskNo=@TaskNoReplace,BoxNo=@boxNo,EquipNo=@EquipNo,Remark=isnull(Remark,'')+'->整包替换:将原包'+@code1+'的子级替换掉' where  ParentJCode=@code2;

		   declare @parLevelBoxType int;
		   declare @subLevelBoxType int;

		   --set @parLevelBoxType=(select dbo.fn_GetWorkBoxType(@TaskNoReplace,@replaceCodeLevel));

		   --set @parLevelBoxType=(select dbo.fn_GetWorkBoxType(@TaskNoReplace,@replaceCodeLevel-1));

		   update TRC_TrackCode set ParentJCode = null,Status=4,Remark=isnull(Remark,'')+'->整包替换:被'+@code2+'替换掉' where JCode=@code1; 

		   --update TRC_TrackCode set Status=4,Remark=isnull(Remark,'')+'->整包替换:被'+@code2+'的新子级替换掉' where ParentJCode=@code1; 

		   --一般中盒坏了把中盒废弃，子码还有在12级工位重新关联，因此子码状态变为已打印
		   update TRC_TrackCode set Status=1,TaskNo=null,TaskNo1=null,BoxNo='',ParentJCode=null where ParentJCode=@code1; 

		   --update TRC_TrackCode set ParentJCode = null,Status=(case @parLevelBoxType when 1 then 4 else 1 end) where JCode=@code1; 

		   --update TRC_TrackCode set Status=(case @subLevelBoxType when 1 then 1 else 2 end) where ParentJCode=@code1; 

		   set @LogDes ='追溯码外包装替换:' + @code1+'被替换成'+@code2+ ','+@code1+'码废弃的同时自身信息及同上级的关系被'+@code2+'取代';  

		end      

		exec usp_LogTrackCode_Asso '替换',@code1,@code2, @LogDes ,@user,@ip,122,@currentTaskNo;  
		
		exec Usp_TrackCodeExceptionForWMS @code1,@code2,@action,@topCode;

		insert into IT_WMS_PreTransferInfo2PtsV8(JCode1,JCode2,Action,Scope,AddTime) values (@code1,@code2,@action,@scope,SYSDATETIME());

	END        

	ELSE IF @action=5--完工后替换        
	BEGIN        

		IF NOT EXISTS (select * from TRC_TrackCode where JCode=@code2 and Status=1)        
		begin        

			SET @result=@code2+N'  不存在或已扫描，不能替换'        

			RAISERROR (@result,12,1);        

			goto goout;        

		end         

		--如果还没有建立关联关系，不能替换       
		IF NOT EXISTS(select ParentJcode from TRC_TrackCode_Export where JCode=@code1)        
		begin            

			SET @result=@code1+N'  不存在或尚未导出，不能替换'        

			RAISERROR (@result,12,1);        

			goto goout;        

		end   
		
		set @currentTaskNo=(select TaskNo from TRC_TrackCode_Export with(nolock) where JCode=@code1);    

		if exists (select * from Trc_TrackCode where JCode=@code2)
		begin
			INSERT INTO  TRC_TrackCode_Export(JCode,RID,RCode,TaskNo,TaskNo1,[LEVEL],Status,ParentJCode,remark,printCount,PrintBatchNo,ImportBatchNo,AppendCode,PackFlag,BoxNo,LineName,scanno,scanno2,equipno,addby,addon,editby,editon,BatchNum1,BatchNum2)        
			SELECT JCode,RID,RCode,TaskNo,TaskNo1,[LEVEL],Status,ParentJCode,remark,printCount,PrintBatchNo,ImportBatchNo,AppendCode,isnull(PackFlag,0) PackFlag,BoxNo,LineName,scanno,scanno2,equipno,addby,addon,editby,editon,BatchNum1,BatchNum2        
			FROM trc_trackCode WHERE JCode=@code2 or ParentJCode=@code2; 

			DELETE FROM  TRC_TrackCode WHERE JCode=@code2 or ParentJCode=@code2; 
		
		end
		else if exists (select * from TRC_TrackCode_Actived where JCode=@code2)
		begin
			INSERT INTO  TRC_TrackCode_Export(JCode,RID,RCode,TaskNo,TaskNo1,[LEVEL],Status,ParentJCode,remark,printCount,PrintBatchNo,ImportBatchNo,AppendCode,PackFlag,BoxNo,LineName,scanno,scanno2,equipno,addby,addon,editby,editon,BatchNum1,BatchNum2)        
			SELECT JCode,RID,RCode,TaskNo,TaskNo1,[LEVEL],Status,ParentJCode,remark,printCount,PrintBatchNo,ImportBatchNo,AppendCode,isnull(PackFlag,0) PackFlag,BoxNo,LineName,scanno,scanno2,equipno,addby,addon,editby,editon,BatchNum1,BatchNum2        
			FROM TRC_TrackCode_Actived WHERE JCode=@code2 or ParentJCode=@code2; 

			DELETE FROM  TRC_TrackCode_Actived WHERE JCode=@code2 or ParentJCode=@code2; 
		end

		select @status=Status,@parentJCode =ParentJCode,@TaskNoReplace=TaskNo,@EquipNo=EquipNo,@EquipNo1=EquipNo1,@EquipNo2=EquipNo2,@scanNo=ScanNo,@boxNo=BoxNo,@PackFlag=PackFlag from TRC_TrackCode_Export where JCode=@code1;      

		if @scope=1  --内包装替换       
		begin      

		  update TRC_TrackCode_Export set ParentJCode=@code2 where ParentJCode=@code1;      

		  update TRC_TrackCode_Export SET ParentJCode=@parentJCode, Status= @status,TaskNo=@TaskNoReplace,TaskNo1=@TaskNoReplace,EquipNo=@EquipNo,EquipNo1=@EquipNo1,EquipNo2=@EquipNo2,ScanNo=@scanNo,BoxNo=@boxNo,PackFlag=@PackFlag where JCode=@code2;       

		  update TRC_TrackCode_Export set ParentJCode = null,Status=4,TaskNo=null where JCode=@code1;      

		  set @LogDes ='追溯码内包装替换:' + @code1+'被替换成'+@code2+','+@code1+'码废弃同时自身信息及上下级关系被'+@code2+'取代'; 

		end      
		else  if @scope=2 --外包装替换      
		begin      

           --接管原有码信息

           update TRC_TrackCode_Export SET ParentJCode=@parentJCode,Status=2,TaskNo=@TaskNoReplace,TaskNo1=@TaskNoReplace,EquipNo=@EquipNo,EquipNo1=@EquipNo1,EquipNo2=@EquipNo2,PackFlag=2,ScanNo=@scanNo,BoxNo=@boxNo where JCode=@code2;       

		   --新的子码接收原有任务信息

           update TRC_TrackCode_Export Set TaskNo=@TaskNoReplace,BoxNo=@boxNo,EquipNo=@EquipNo where  ParentJCode=@code2;

		   --原有父级码解除与老上级的关系并废弃

           update TRC_TrackCode_Export set ParentJCode = null,Status=4 where JCode=@code1; 

		   --原有的子码废弃

           update TRC_TrackCode_Export set Status=4 where ParentJCode=@code1; 

		   set @LogDes ='控制端完工后追溯码外包装替换:' + @code1+'被替换成'+@code2+ ','+@code1+'码废弃的同时自身信息及同上级的关系被'+@code2+'取代';  


		end  

		exec usp_LogTrackCode_Asso '完工后替换',@code1,@code2, @LogDes,@user,@ip,122,@currentTaskNo;         

		exec Usp_TrackCodeExceptionForWMS @code1,@code2,@action,@topCode;

	END         

	ELSE IF @action=3--创建关系        
	BEGIN        

		declare @c1 int,@c2 int;  
		declare @sbc int;
		declare @tc int;

		set @c1=1;set @c1=1;  

		if exists(select * from TRC_TrackCode where JCode=@Code1)        

		set @c1=0;  

		if exists(select * from TRC_TrackCode where JCode=@Code2)        

		set @c2=0; 

		select @EquipNo1=EquipNo1 from trc_TrackCode with(nolock) where JCode=@code2;

		declare @fullPackCount int;       

		if @c1=0 and @c2=0        
		begin 
		
			IF EXISTS (select * from TRC_TrackCode where JCode=@code1 and Status=4)        
			begin        

				SET @result=@code1+N' 已废弃不能追加'        

				RAISERROR (@result,12,1);        

				goto goout;        

			end 

		  set @fullPackCount =( select isnull(pd.Count,0) from Trc_TrackCode c with(nolock) join Trc_WorkTask t with(nolock) on c.TaskNo=t.TaskNo join  TRC_PackSpec_Detail pd with(nolock) on pd.PackSpecNo=t.PackSpecNo and pd.Level=c.Level-1 where c.JCode=@code2);

		  if (select count(*) from TRC_TrackCode with(nolock) where ParentJCode=@code2)>=@fullPackCount
		   begin

			SET @result=@code2+N'  已经满包装，不能继续追加'        

			RAISERROR (@result, 12, 1);        

			goto goout;  
		   end

			UPDATE t 
			SET ParentJCode=@Code2,
			Status=2,
			LineName=(select LineName from Trc_TrackCode with(nolock) where JCode=@Code2),
			EquipNo=(select EquipNo from Trc_TrackCode with(nolock) where JCode=@Code2),
			BoxNo=(select BoxNo from Trc_TrackCode with(nolock) where JCode=@Code2),
			EquipNo1=(select EquipNo2 from Trc_TrackCode with(nolock) where JCode=@Code2),
			TaskNo=(select TaskNo from Trc_TrackCode with(nolock) where JCode=@Code2), 
			TaskNo1=(case isnull(TaskNo1,'') when '' then (select TaskNo from Trc_TrackCode with(nolock) where JCode=@Code2) else TaskNo1 end),
			PackFlag=(case isnull(PackFlag,0) when 0 then 2 else PackFlag end)
			from trc_trackCode t
			WHERE Jcode=@Code1;  
			
			set @sbc = (select count(*) from Trc_TrackCode with(nolock) where ParentJCode=@code2);
			set @tc=(select count(*) from (select distinct TaskNo1 from Trc_TrackCode with(nolock) where ParentJCode=@code2 and isnull(TaskNo1,'')<>'') v);

			UPDATE trc_trackCode SET packFlag=(CASE @sbc WHEN @fullPackCount THEN (case @tc when 1 then 2 else 4 end) else  (case @tc when 1 then 3 else 5 end) END) WHERE Jcode=@Code2;
          

		   set @LogDes ='生产表的码:' + @code1+'被追加到生产表的码：'+@code2;  

		   insert into IT_WMS_PreTransferInfo2PtsV8(JCode1,JCode2,Action,Scope,AddTime) values (@code1,@code2,@action,@scope,SYSDATETIME());

		end        
		else if @c1=1 and @c2=1        

		begin 

		 set @fullPackCount =( select isnull(pd.Count,0) from TRC_TrackCode_Export c with(nolock) join Trc_WorkTask t with(nolock) on c.TaskNo=t.TaskNo join  TRC_PackSpec_Detail pd with(nolock) on pd.PackSpecNo=t.PackSpecNo and pd.Level=c.Level-1 where c.JCode=@code2);

		  if (select count(*) from TRC_TrackCode_Export with(nolock) where ParentJCode=@code2)>=@fullPackCount
		   begin

			SET @result=@code2+N'  已经满包装，不能继续追加'        

			RAISERROR (@result, 12, 1);        

			goto goout;  
		   end
		
		    SET @currentTaskNo = (SELECT TaskNo FROM TRC_TrackCode_Export WITH(nolock) WHERE JCode=@Code1);

		    select @EquipNo1=EquipNo1 from TRC_TrackCode_Export with(nolock) where JCode=@code2;       

			UPDATE TRC_TrackCode_Export SET ParentJCode=@Code2,EquipNo=@EquipNo1 WHERE Jcode=@Code1;            

			set @sbc = (select count(*) from Trc_TrackCode_Export with(nolock) where ParentJCode=@code2);
			set @tc=(select count(*) from (select distinct TaskNo1 from Trc_TrackCode_Export with(nolock) where ParentJCode=@code2 and isnull(TaskNo1,'')<>'') v);

			UPDATE Trc_TrackCode_Export SET packFlag=(CASE @sbc WHEN @fullPackCount THEN (case @tc when 1 then 2 else 4 end) else  (case @tc when 1 then 3 else 5 end) END) WHERE Jcode=@Code2;
                         
			set @LogDes ='完工表的码:' + @code1+'被追加到完工表的码：'+@code2;     
		end        

		else         

		begin 

		    select @EquipNo1=EquipNo1 from TRC_TrackCode_Export with(nolock) where JCode=@code2;       

			INSERT INTO  TRC_TrackCode_Export(JCode,RID,RCode,TaskNo,TaskNo1,[LEVEL],Status,ParentJCode,remark,printCount,PrintBatchNo,ImportBatchNo,AppendCode,PackFlag,BoxNo,LineName,scanno,scanno2,equipno,addby,addon,editby,editon,BatchNum1,BatchNum2)  

			SELECT JCode,RID,RCode,TaskNo,TaskNo1,[LEVEL],Status,ParentJCode,remark,printCount,PrintBatchNo,ImportBatchNo,AppendCode,PackFlag,BoxNo,LineName,scanno,scanno2,equipno,addby,addon,editby,editon,BatchNum1,BatchNum2 FROM trc_trackCode  where JCode=@code1;    

			delete trc_trackCode  where JCode=@code1;        

			UPDATE TRC_TrackCode_Export SET ParentJCode=@Code2,EquipNo=@EquipNo1 WHERE Jcode=@Code1;            

			set @sbc = (select count(*) from Trc_TrackCode_Export with(nolock) where ParentJCode=@code2);
			set @tc=(select count(*) from (select distinct TaskNo1 from Trc_TrackCode_Export with(nolock) where ParentJCode=@code2 and isnull(TaskNo1,'')<>'') v);

			UPDATE Trc_TrackCode_Export SET packFlag=(CASE @sbc WHEN @fullPackCount THEN (case @tc when 1 then 2 else 4 end) else  (case @tc when 1 then 3 else 5 end) END) WHERE Jcode=@Code2;             

			set @LogDes ='生产表的码:' + @code1+'被追加到完工表的码：'+@code2;  
		end;         

		exec usp_LogTrackCode_Asso '追加',@code1,@Code2,@LogDes,@user,@ip,124,@currentTaskNo;   
		
		insert into IT_WMS_PreTransferInfo2PtsV8(JCode1,JCode2,Action,Scope,AddTime) values (@code1,@code2,@action,@scope,SYSDATETIME());

	END        

	ELSE IF @action=4--检验        

	BEGIN           

		IF EXISTS(SELECT * FROM uv_trackCode WHERE SourceFlag in (1,3) and JCode=@code2 AND ParentJCode=@code1)

		begin         

			SET @result='正确';		

		end        

		else 

		begin

			SET @result='错误';     

		end 

		SET @currentTaskNo = (SELECT TaskNo FROM TRC_TrackCode WITH(nolock) WHERE JCode=@Code1);

		set @LogDes='检验包装关系:'+@result;     

		exec usp_LogTrackCode_Asso '验证',@code1,@Code2,@LogDes,@user,@ip,121,@currentTaskNo;          

	END

	ELSE IF @action=6--检验2，通过物流码或追溯码检索父码，箱号，      

	BEGIN    

	       

		set @result='';      

		select @result= '追溯码:'+JCode +'\r\n 物流码:'+ ISNULL(appendCode,'')+'\r\n父码'+ISNULL(ParentJCode,'')+'\r\n箱号'+ISNULL(BoxNo,'') FROM uv_trackCode WHERE  JCode=@code1;      



		if ISNULL(@result,'')=''      

			SET @result='无效码';   

			--exec usp_LogTrackCode_Asso '验证',@code1,@Code2,'检验包装关系',@user,@ip;      

	END

		      

	goout:           

	SELECT @result;  

      

END