if exists (select * from dbo.sysobjects where id = object_id(N'Usp_TrackCodeCB') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
begin
 drop proc Usp_TrackCodeCB; 
end
go

create PROCEDURE  Usp_TrackCodeCB 
	@i BIT,@c VARCHAR(2000)     
AS        
BEGIN  
    PRINT @c;

	DECLARE @d Table (JCode varchar(100),Level int,PackFlag int,TaskNo varchar(100),TaskNo1 varchar(100),ParentJCode VARCHAR(100),Status int);
	DECLARE @e VARCHAR(100);
	DECLARE @f VARCHAR(1000);
   	 
   IF @i=1
   BEGIN
   	 DECLARE Cura CURSOR FOR SELECT jcode FROM TRC_TrackCode WITH(nolock) WHERE TaskNo=@c AND PackFlag=1 AND isnull(parentjcode,'')='';   	 
   	 OPEN    Cura; 	 
   	 FETCH Cura INTO   @e;   	 
   	 WHILE @@FETCH_STATUS=0
   	 BEGIN   	 
   	    BEGIN TRY   	    
   	      INSERT INTO @d(JCode ,Level ,PackFlag ,TaskNo ,TaskNo1 ,ParentJCode ,Status )SELECT JCode ,Level ,PackFlag ,TaskNo ,TaskNo1 ,ParentJCode ,Status FROM dbo.fn_GetAllSubCodes(@e);
   	    END TRY
   	    BEGIN CATCH 
   	     SET @f=@e+':'+ error_message();
   	     PRINT @f;
   	    END CATCH
   	    FETCH Cura INTO   @e;   	 
   	 END 
   	 CLOSE Cura;
   	 DEALLOCATE Cura;
   END 
   ELSE
   BEGIN
     DECLARE Curb CURSOR FOR SELECT jcode FROM TRC_TrackCode WITH(nolock) WHERE JCode IN (SELECT val FROM dbo.split(@c));   	 
   	 OPEN    Curb;     
   	 FETCH Curb INTO @e;
   	 WHILE @@FETCH_STATUS=0
   	 BEGIN
   	    BEGIN TRY
   	      INSERT INTO @d(JCode ,Level ,PackFlag ,TaskNo ,TaskNo1 ,ParentJCode ,Status ) SELECT JCode ,Level ,PackFlag ,TaskNo ,TaskNo1 ,ParentJCode ,Status FROM dbo.fn_GetAllSubCodes(@e);
   	    END TRY
   	    BEGIN CATCH 
   	     SET @f=@e+':'+ error_message();
   	     PRINT @f;
   	    END CATCH 
   	    FETCH Curb INTO   @e;   	 
   	 END     	 
   	 CLOSE Curb;
   	 DEALLOCATE Curb;
   END 
   
   SELECT * FROM @d;

   	UPDATE c SET c.PackFlag = (CASE isnull(c.ParentJCode,'') WHEN '' then (CASE (select count(*) from dbo.fn_GetAllSubCodes(c.JCode) where Level=c.[Level]-1) WHEN (select Count from TRC_PackSpec_Detail with(nolock) where PackSpecNo=(select PackSpecNo from Trc_WorkTask with(nolock) where TaskNo=c.TaskNo) and level=c.[Level]-1) THEN 2 ELSE 0 end) ELSE 2 END), c.Status=(CASE isnull(c.ParentJCode,'') WHEN '' then (CASE ScanNo2 WHEN 0 THEN 1 ELSE 2 END) ELSE 2 END),c.ScanNo2=0 FROM TRC_TrackCode c WHERE c.JCode IN (SELECT jcode FROM @d)

END