SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








--导入药品信息表 2017.6.16
ALTER proc [Master_BBX1_BBY1_Import]
  @lxml ntext
  ,@lMode tinyint  --0:清楚原数据，新增 1:追加与替换现有数据  
as
  DECLARE @iDOM int, @iret int
  Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lXml

  if @iret > 0
  BEGIN
    exec sp_xml_removedocument @iDOM	
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
    return @iret
  END 
  
  declare @ReBBX04 varchar(50)
  set @ReBBX04 = ''
    
  Create TABLE #TmpImport(uid int IDENTITY(1,1),BDA02 varchar(20),BDO03 varchar(64),BBX04 varchar(50)
    ,BBX05 varchar(128),BBY06 varchar(48),BAP02 varchar(20),Rownr int,BBY04 varchar(50)
    ,BAX03 varchar(20),ABF02 varchar(20),ABA02 varchar(64),BCH02 varchar(20),BCG02 varchar(20),BGF02 varchar(64)
    ,BHH02 varchar(64),AAS02 varchar(32),BBY10 varchar(50),ACF03 varchar(32),BBE02 varchar(64),BBY08 varchar(20)
    ,BAG03 numeric(18,6),BDG02 varchar(20),BDG02A varchar(20),BAG05 numeric(18,4),BDG02B varchar(20),BAG07 numeric(18,4)
    ,BDG02C varchar(20),BAG09 numeric(18,4),BBY25 numeric(18,6),BAG15 varchar(32),BDA01 varchar(2)
    ,ABF01 varchar(8),BDO01 int,BAX01 int,AAS01 varchar(4),ABA01 varchar(4),BCG01 varchar(4),BCH01 varchar(4)
    ,ACF01 int,BGF01 varchar(8),BHH01 varchar(8),BBX01 int,BBY01 int,BAP01 varchar(4),BBY50 varchar(50),BBY52 varchar(50),BBY53 varchar(50))
  
  create table #tmpBBX1(uid int IDENTITY(1,1),BBX01 int,BBX04 varchar(50),BDA01 varchar(2),BDO01 int,BBX05 varchar(128),BDG02 varchar(20),BAP02 varchar(20))    
  create table #tmpBBY1(uid int IDENTITY(1,1),BBY01 int,BBY04 varchar(50))      
 
  INSERT INTO #TmpImport(BDA02,BDO03,BBX04,BBX05,BBY06,BAP02,Rownr,BBY04
    ,BAX03,ABF02,ABA02,BCH02,BCG02,BGF02,BHH02,AAS02,BBY10,ACF03
    ,BBE02,BBY08,BAG03,BDG02,BDG02A,BAG05
    ,BDG02B,BAG07,BDG02C,BAG09,BBY25,BAG15,BBY50,BBY52,BBY53)
  SELECT 项目类型, 药理分类, 编号, 名称, 规格, 剂型,规格序号,规格编码
    ,收入项目,收据费别,病案费别,折扣费别,其他费别,财务费别
    ,业务费别,偿付类别,医保编码,服务对象
    ,产地,基本单位,含量,剂量单位,门诊单位,门诊包装
    ,住院单位,住院包装,药库单位,药库包装,单价,批准文号
	,本位码,卫计委YPID编码,社会保险药品与编码
  from OpenXml(@iDOM,'/Root/import/Ie',8)
    WITH (项目类型 varchar(20)
    ,药理分类 varchar(64)
    ,编号 varchar(50)
    ,名称 varchar(128)
    ,规格 varchar(48)
    ,剂型 varchar(20)
    ,规格序号 int
    ,规格编码 varchar(50)
    ,收入项目 varchar(20)
    ,收据费别 varchar(20)
    ,病案费别 varchar(64)
    ,折扣费别 varchar(20)
    ,其他费别 varchar(20)
    ,财务费别 varchar(64)
    ,业务费别 varchar(64)
    ,偿付类别 varchar(32)
    ,医保编码 varchar(50)
    ,服务对象 varchar(32)
    ,产地 varchar(64)
    ,基本单位 varchar(20)
    ,含量 numeric(18,6)
    ,剂量单位 varchar(20)
    ,门诊单位 varchar(20)
    ,门诊包装 numeric(18,4)
    ,住院单位 varchar(20)
    ,住院包装 numeric(18,4)
    ,药库单位 varchar(20)
    ,药库包装 numeric(18,4)
    ,单价 numeric(18,6)    
    ,批准文号 varchar(32)
	,本位码 varchar(50)
	,卫计委YPID编码 varchar(50)
	,社会保险药品与编码 varchar(50)
    )	
  
  If not exists(select * from #TmpImport)
  BEGIN
    exec sp_xml_removedocument @iDOM
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR('提交文档错误，没有数据信息.', 16, 1) with nowait
    RETURN 1
  END 
  exec sp_xml_removedocument @iDOM
  
  declare @smsg varchar(200)
  if exists(select * from #TmpImport a where not exists(select * from BDA1 b where b.BDA02 = a.BDA02)) 
  begin    
    select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BDA1 b where b.BDA02 = a.BDA02)    
    set @smsg = '['+@ReBBX04+']项目类型不对，数据库中不存在此项目类型，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 3
  end        
  if exists(select * from #TmpImport a where not exists(select * from BDO1 b where b.BDO03 = a.BDO03 and b.BDM01 <= '3')) 
  begin
    IF Object_id('tempdb..#TmpBDO1') IS NOT NULL DROP TABLE #TmpBDO1  
	Create TABLE #TmpBDO1(uid int IDENTITY(1,1),BDO03 varchar(64),BDA02 varchar(20))
    INSERT INTO #TmpBDO1 (BDO03,BDA02) select a.BDO03,a.BDA02 from #TmpImport a where not exists(select * from BDO1 b where b.BDO03 = a.BDO03 and b.BDM01 <= '3')
    declare @I8 int,@M8 int
	set @I8 = (select count(*) from #TmpBDO1)  
	set @M8 = 1
	while @M8 <= @I8
	begin
	  IF (select a.BDO03 from #TmpBDO1 a where not exists(select * from BDO1 b where b.BDO03 = a.BDO03 and b.BDM01 <= '3') and a.uid=@M8 ) IS NOT NULL
	  begin
	    insert into BDO1(BDO01,BDO02,BDO03,BDM01)
	      select  (select isnull(max(BDO01)+1,1) MaxCode FROM BDO1 WHERE IsNumeric(BDO01) > 0) as BBDO01,cast(a.uid as varchar(20)),a.BDO03,(select top 1 c.BDM01 from BDM1 c where c.BDM02=a.BDA02 and c.BDM01 <= '3') from #TmpBDO1 a where not exists(select * from BDO1 b where b.BDO03 = a.BDO03 and b.BDM01 <= '3') and a.uid=@M8 
	  end
	 set @M8 = @M8+1
	end
   /* select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BDO1 b where b.BDO03 = a.BDO03 and b.BDM01 <= '3')
    set @smsg = '['+@ReBBX04+']药理分类不对，数据库中不存在此药理分类，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end 
  if exists(select * from #TmpImport a where not exists(select * from BAX1 b where b.BAX03 = a.bax03)) 
  begin
    IF Object_id('tempdb..#TmpBAX1') IS NOT NULL DROP TABLE #TmpBAX1  
	Create TABLE #TmpBAX1(uid int IDENTITY(1,1),BAX03 varchar(20))
    INSERT INTO #TmpBAX1 (BAX03) select a.BAX03 from #TmpImport a where not exists(select * from BAX1 b where b.BAX03 = a.BAX03)
    declare @I11 int,@M11 int
	set @I11 = (select count(*) from #TmpBAX1)  
	set @M11 = 1
	while @M11 <= @I11
	begin
	   if (select a.BAX03 from #TmpBAX1 a where not exists(select * from BAX1 b where b.BAX03 = a.BAX03) and a.uid=@M11) is not null
	   begin
	    insert into BAX1(BAX01,BAX03) select (select isnull(max(BAX01)+1,1) MaxCode FROM BAX1 WHERE BAX01 > 0) as AABF01,a.BAX03 from #TmpBAX1 a where not exists(select * from BAX1 b where b.BAX03 = a.BAX03) and a.uid=@M11 
	   end
	set @M11 = @M11+1
	end
    /*select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BAX1 b where b.BAX03 = a.bax03)
    set @smsg = '['+@ReBBX04+']收入项目不对，数据库中不存在此收入项目，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end 
  if exists(select * from #TmpImport a where not exists(select * from ABF1 b where b.ABF02 = a.ABF02)) 
  begin
    IF Object_id('tempdb..#TmpABF1') IS NOT NULL DROP TABLE #TmpABF1  
	Create TABLE #TmpABF1(uid int IDENTITY(1,1),ABF02 varchar(20))
    INSERT INTO #TmpABF1 (ABF02) select a.ABF02 from #TmpImport a where not exists(select * from ABF1 b where b.ABF02 = a.ABF02)
    declare @II int,@MM int
	set @II = (select count(*) from #TmpABF1)  
	set @MM = 1
	while @MM <= @II
	begin
	  if (select a.ABF02 from #TmpABF1 a where not exists(select * from ABF1 b where b.ABF02 = a.ABF02) and a.uid=@MM) is not null
	  begin
	    insert into ABF1(ABF01,ABF02) select (select isnull(max(cast(ABF01 AS numeric)+@MM),1) MaxCode FROM ABF1 WHERE IsNumeric(ABF01) > 0) as AABF01,a.ABF02 from #TmpABF1 a where not exists(select * from ABF1 b where b.ABF02 = a.ABF02) and a.uid=@MM 
	  end
	 set @MM = @MM+1
	end
   /* select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from ABF1 b where b.ABF02 = a.ABF02)
    set @smsg = '['+@ReBBX04+']收据费别不对，数据库中不存在此收据费别，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 4*/
  end  
  if exists(select * from #TmpImport a where not exists(select * from ABA1 b where b.ABA02 = a.ABA02)) 
  begin
    IF Object_id('tempdb..#TmpABA1') IS NOT NULL DROP TABLE #TmpABA1  
	Create TABLE #TmpABA1(uid int IDENTITY(1,1),ABA02 varchar(64))
    INSERT INTO #TmpABA1 (ABA02) select a.ABA02 from #TmpImport a where not exists(select * from ABA1 b where b.ABA02 = a.ABA02)
    declare @I4 int,@M4 int
	set @I4 = (select count(*) from #TmpABA1)  
	set @M4 = 1
	while @M4 <= @I4
	begin
	  if (select a.ABA02 from #TmpABA1 a where not exists(select * from ABA1 b where b.ABA02 = a.ABA02) and a.uid=@M4 )is not null
	  begin
	    insert into ABA1(ABA01,ABA02) select  (select isnull(max(cast(ABA01 AS numeric))+1,1) MaxCode FROM ABA1 WHERE IsNumeric(ABA01) > 0) as BABA01,a.ABA02 from #TmpABA1 a where not exists(select * from ABA1 b where b.ABA02 = a.ABA02) and a.uid=@M4 
	  end
	 set @M4 = @M4+1
	end
    /*select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from ABA1 b where b.ABA02 = a.ABA02)
    set @smsg = '['+@ReBBX04+']病案费别不对，数据库中不存在此病案费别，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end   
  if exists(select * from #TmpImport a where not exists(select * from BCH1 b where b.BCH02 = a.BCH02)) 
  begin
    IF Object_id('tempdb..#TmpBCH1') IS NOT NULL DROP TABLE #TmpBCH1  
	Create TABLE #TmpBCH1(uid int IDENTITY(1,1),BCH02 varchar(20))
    INSERT INTO #TmpBCH1 (BCH02) select a.BCH02 from #TmpImport a where not exists(select * from BCH1 b where b.BCH02 = a.BCH02)
    declare @I5 int,@M5 int
	set @I5 = (select count(*) from #TmpBCH1)  
	set @M5 = 1
	while @M5 <= @I5
	begin
	  if (select a.BCH02 from #TmpBCH1 a where not exists(select * from BCH1 b where b.BCH02 = a.BCH02) and a.uid=@M5 ) is not null
	  begin
	    insert into BCH1(BCH01,BCH02) select  (select isnull(max(cast(BCH01 AS numeric))+1,1) MaxCode FROM BCH1 WHERE IsNumeric(BCH01) > 0) as BBCH01,a.BCH02 from #TmpBCH1 a where not exists(select * from BCH1 b where b.BCH02 = a.BCH02) and a.uid=@M5 
	  end
	 set @M5 = @M5+1
	end
    /*select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BCH1 b where b.BCH02 = a.BCH02)
    set @smsg = '['+@ReBBX04+']折扣费别不对，数据库中不存在此折扣费别，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end  
  if exists(select * from #TmpImport a where not exists(select * from BCG1 b where b.BCG02 = a.BCG02)) 
  begin
    IF Object_id('tempdb..#TmpBCG1') IS NOT NULL DROP TABLE #TmpBCG1  
	Create TABLE #TmpBCG1(uid int IDENTITY(1,1),BCG02 varchar(20))
    INSERT INTO #TmpBCG1 (BCG02) select a.BCG02 from #TmpImport a where not exists(select * from BCG1 b where b.BCG02 = a.BCG02)
    declare @I3 int,@M3 int
	set @I3 = (select count(*) from #TmpBCG1)  
	set @M3 = 1
	while @M3 <= @I3
	begin
	  if (select a.BCG02 from #TmpBCG1 a where not exists(select * from BCG1 b where b.BCG02 = a.BCG02) and a.uid=@M3 ) is not null
	  begin
	    insert into BCG1(BCG01,BCG02) select  (select isnull(max(cast(BCG01 AS numeric))+1,1) MaxCode FROM BCG1 WHERE IsNumeric(BCG01) > 0) as BBCG01,a.BCG02 from #TmpBCG1 a where not exists(select * from BCG1 b where b.BCG02 = a.BCG02) and a.uid=@M3 
	  end
	 set @M3 = @M3+1
	end
   /*select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BCG1 b where b.BCG02 = a.BCG02)
    set @smsg = '['+@ReBBX04+']其他费别不对，数据库中不存在此其他费别，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end 
  if exists(select * from #TmpImport a where not exists(select * from BGF1 b where b.BGF02 = a.BGF02)) 
  begin
    IF Object_id('tempdb..#TmpBGF1') IS NOT NULL DROP TABLE #TmpBGF1  
	Create TABLE #TmpBGF1(uid int IDENTITY(1,1),BGF02 varchar(64))
    INSERT INTO #TmpBGF1 (BGF02) select a.BGF02 from #TmpImport a where not exists(select * from BGF1 b where b.BGF02 = a.BGF02)
    declare @I6 int,@M6 int
	set @I6 = (select count(*) from #TmpBGF1)  
	set @M6 = 1
	while @M6 <= @I6
	begin
	  if (select a.BGF02 from #TmpBGF1 a where not exists(select * from BGF1 b where b.BGF02 = a.BGF02) and a.uid=@M6) is not null
	  begin
	    insert into BGF1(BGF01,BGF02) select  (select isnull(max(cast(BGF01 AS numeric))+1,1) MaxCode FROM BGF1 WHERE IsNumeric(BGF01) > 0) as BBGF01,a.BGF02 from #TmpBGF1 a where not exists(select * from BGF1 b where b.BGF02 = a.BGF02) and a.uid=@M6
	  end
	 set @M6 = @M6+1
	end
   /*select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BGF1 b where b.BGF02 = a.BGF02)
    set @smsg = '['+@ReBBX04+']财务费别不对，数据库中不存在此财务费别，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end 
  if exists(select * from #TmpImport a where not exists(select * from BHH1 b where b.BHH02 = a.BHH02)) 
  begin
    IF Object_id('tempdb..#TmpBHH1') IS NOT NULL DROP TABLE #TmpBHH1  
	Create TABLE #TmpBHH1(uid int IDENTITY(1,1),BHH02 varchar(20))
    INSERT INTO #TmpBHH1 (BHH02) select a.BHH02 from #TmpImport a where not exists(select * from BHH1 b where b.BHH02 = a.BHH02)
    declare @I7 int,@M7 int
	set @I7 = (select count(*) from #TmpBHH1)  
	set @M7 = 1
	while @M7 <= @I7
	begin
	  if (select a.BHH02 from #TmpBHH1 a where not exists(select * from BHH1 b where b.BHH02 = a.BHH02) and a.uid=@M7) is not null
	  begin
	    insert into BHH1(BHH01,BHH02) select  (select isnull(max(cast(BHH01 AS numeric))+1,1) MaxCode FROM BHH1 WHERE IsNumeric(BHH01) > 0) as BBHH01,a.BHH02 from #TmpBHH1 a where not exists(select * from BHH1 b where b.BHH02 = a.BHH02) and a.uid=@M7 
	  end
	 set @M7 = @M7+1
	end
   /*select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BHH1 b where b.BHH02 = a.BHH02)
    set @smsg = '['+@ReBBX04+']业务费别不对，数据库中不存在此业务费别，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end 
  if exists(select * from #TmpImport a where not exists(select * from AAS1 b where b.AAS02 = a.AAS02 and b.AAS05=1)) 
  begin
    IF Object_id('tempdb..#TmpAAS1') IS NOT NULL DROP TABLE #TmpAAS1  
	Create TABLE #TmpAAS1(uid int IDENTITY(1,1),AAS02 varchar(32))
    INSERT INTO #TmpAAS1 (AAS02) select a.AAS02 from #TmpImport a where not exists(select * from AAS1 b where b.AAS02 = a.AAS02)
    declare @I10 int,@M10 int
	set @I10 = (select count(*) from #TmpAAS1)  
	set @M10 = 1
	while @M10 <= @I10
	begin
	 if (select a.AAS02 from #TmpAAS1 a where not exists(select * from AAS1 b where b.AAS02 = a.AAS02) and a.uid=@M10) is  not null 
	 begin
	  insert into AAS1(AAS01,AAS02,AAS05) select  (select isnull(max(cast(AAS01 AS numeric))+1,1) MaxCode FROM AAS1 WHERE IsNumeric(AAS01) > 0) as BAAS01,a.AAS02,1 from #TmpAAS1 a where not exists(select * from AAS1 b where b.AAS02 = a.AAS02 and b.AAS05=1) and a.uid=@M10 
	 end
	 set @M10 = @M10+1
	end
    /*select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from AAS1 b where b.AAS02 = a.AAS02)
    set @smsg = '['+@ReBBX04+']偿付类别不对，数据库中不存在此偿付类别，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end
  
  if exists(select * from #TmpImport a where not exists(select * from ACF1 b where b.ACF03 = a.ACF03)) 
  begin
    IF Object_id('tempdb..#TmpACF1') IS NOT NULL DROP TABLE #TmpACF1  
	Create TABLE #TmpACF1(uid int IDENTITY(1,1),ACF03 varchar(32))
    INSERT INTO #TmpACF1 (ACF03) select a.ACF03 from #TmpImport a where not exists(select * from ACF1 b where b.ACF03 = a.ACF03 )
    declare @I12 int,@M12 int
	set @I12 = (select count(*) from #TmpACF1)  
	set @M12 = 1
	while @M12 <= @I12
	begin
	  if (select a.ACF03 from #TmpACF1 a where not exists(select * from ACF1 b where b.ACF03 = a.ACF03 ) and a.uid=@M12 ) is not null
	  begin
	    insert into ACF1(ACF01,ACF03)
	      select  (select isnull(max(ACF01)+1,1) MaxCode FROM ACF1 WHERE IsNumeric(ACF01) > 0) as BACF01,a.ACF03 from #TmpACF1 a where not exists(select * from ACF1 b where b.ACF03 = a.ACF03 ) and a.uid=@M12 
	  end
	 set @M12 = @M12+1
	end
   /* select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from ACF1 b where b.ACF03 = a.ACF03)
    set @smsg = '['+@ReBBX04+']服务对象不对，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5*/
  end        

  if exists(select * from #TmpImport a where isnull(a.BAP02,'')<>'' and  not exists(select * from BAP1 b where b.BAP02 = a.BAP02)) 
  begin
    IF Object_id('tempdb..#TmpBAP1') IS NOT NULL DROP TABLE #TmpBAP1  
	Create TABLE #TmpBAP1(uid int IDENTITY(1,1),BAP02 varchar(20))
    INSERT INTO #TmpBAP1 (BAP02) select a.BAP02 from #TmpImport a where not exists(select * from BAP1 b where b.BAP02 = a.BAP02)
    declare @I9 int,@M9 int
	set @I9 = (select count(*) from #TmpBAP1)  
	set @M9 = 1
	while @M9 <= @I9
	begin
	  if (select a.BAP02 from #TmpBAP1 a where not exists(select * from BAP1 b where b.BAP02 = a.BAP02) and a.uid=@M9) is not null
	  begin
	    insert into BAP1(BAP01,BAP02) select  (select isnull(max(cast(BAP01 AS numeric))+1,1) MaxCode FROM BAP1 WHERE IsNumeric(BAP01) > 0) as BBAP01,a.BAP02 from #TmpBAP1 a where not exists(select * from BAP1 b where b.BAP02 = a.BAP02) and a.uid=@M9
	  end
	 set @M9 = @M9+1
	end
   /* select @ReBBX04 = a.BBX04 from #TmpImport a where isnull(a.BAP02,'')<>'' and  not exists(select * from BAP1 b where b.BAP02 = a.BAP02)
    set @smsg = '['+@ReBBX04+']剂型不对，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 6*/
  end  
  
  if exists(select * from #TmpImport where ISNULL(BBX04,'')='' or ISNULL(BBX05,'')='' or ISNULL(BBY04,'')=''
     or ISNULL(BBY08,'')='' or ISNULL(BDG02,'')='' or ISNULL(BDG02A,'')='' or ISNULL(BDG02B,'')='' or ISNULL(BDG02C,'')=''
     or isnull(BAG03,-1)= -1 or isnull(BAG05,-1)= -1 or isnull(BAG07,-1)= -1 or isnull(BAG09,-1)= -1 or isnull(BBY25,-1)= -1) 
  begin
    select @ReBBX04 = BBX04 from #TmpImport where ISNULL(BBX04,'')='' or ISNULL(BBX05,'')='' or ISNULL(BBY04,'')=''
     or ISNULL(BBY08,'')='' or ISNULL(BDG02,'')='' or ISNULL(BDG02A,'')='' or ISNULL(BDG02B,'')='' or ISNULL(BDG02C,'')=''
     or isnull(BAG03,-1)= -1 or isnull(BAG05,-1)= -1 or isnull(BAG07,-1)= -1 or isnull(BAG09,-1)= -1 or isnull(BBY25,-1)= -1    
    set @smsg = '['+@ReBBX04+']编号、名称、规格编码、各种单位、包装系数、单价都不能省略，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 7
  end
    
  if exists(select BBY04,COUNT(1) from #tmpimport group by BBY04 having COUNT(1)>1)
  begin
    select @ReBBX04 = a.BBY04
    from (select BBY04,COUNT(1) num from #tmpimport group by BBY04 having COUNT(1)>1) a
    set @smsg = '['+@ReBBX04+']规格编号有重复号码，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 8
  end
  
  update a set a.BDA01 = b.BDA01
  from #TmpImport a
  join BDA1 b on b.BDA02 = a.BDA02
  update a set a.BDO01 = b.BDO01
  from #TmpImport a
  join (select BDO03,MAX(BDO01) BDO01 from BDO1 where BDM01 <= '3' group by BDO03) b on b.BDO03 = a.BDO03
  
  update a set a.BAX01 = b.BAX01
  from #TmpImport a
  join BAX1 b on b.BAX03 = a.BAX03
  
  update a set a.ABF01 = b.ABF01
  from #TmpImport a
  join ABF1 b on b.ABF02 = a.ABF02
  update a set a.ABA01 = b.ABA01
  from #TmpImport a
  join ABA1 b on b.ABA02 = a.ABA02
  update a set a.BCH01 = b.BCH01
  from #TmpImport a
  join BCH1 b on b.BCH02 = a.BCH02
  update a set a.BCG01 = b.BCG01
  from #TmpImport a
  join BCG1 b on b.BCG02 = a.BCG02
  update a set a.BGF01 = b.BGF01
  from #TmpImport a
  join BGF1 b on b.BGF02 = a.BGF02
  update a set a.BHH01 = b.BHH01
  from #TmpImport a
  join BHH1 b on b.BHH02 = a.BHH02  
  update a set a.AAS01 = b.AAS01
  from #TmpImport a
  join AAS1 b on b.AAS02 = a.AAS02
  update a set a.ACF01 = b.ACF01
  from #TmpImport a
  join ACF1 b on b.ACF03 = a.ACF03

  update a set a.BAP01 = b.BAP01
  from #TmpImport a
  join BAP1 b on b.BAP02 = a.BAP02

  insert into #tmpBBX1(BBX04,BBX05,BDA01,BDO01,BDG02,BAP02)
  select distinct BBX04,BBX05,BDA01,BDO01,BDG02,BAP02
  from #tmpImport a
  where (@lMode <> 1) or (@lMode = 1 and not exists(select * from BBX1 b where b.BBX04 = a.BBX04))
  select * from #tmpBBX1
  if exists(select BBX04,COUNT(1) from #tmpBBX1 group by BBX04 having COUNT(1)>1)
  begin
    select @ReBBX04 = s.BBX04
    from (select BBX04,COUNT(1) num from #tmpBBX1 group by BBX04 having COUNT(1)>1) s
    set @smsg = '['+@ReBBX04+']品种信息中，编号一致，而其它信息不一致导致有重复品种，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 8
  end
  
  insert into #tmpBBY1(BBY04)
  select BBY04
  from #tmpImport a
  where (@lMode <> 1) or (@lMode = 1 and not exists(select * from BBY1 b where b.BBY04 = a.BBY04))
  
  if (@lMode = 0) and exists(select * from VAJ1 where BDN01 <= '3')
  begin    
    set @smsg = '已有费用明细记录，不能清空原有数据进行保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  
  if (@lMode = 0) and exists(select * from VAF1)
  begin    
    set @smsg = '已有医嘱使用记录，不能清空原有数据进行保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  if (@lMode = 0) and exists(select * from DSB2)
  begin    
    set @smsg = '药品进货单已有使用记录，不能清空原有数据进行保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  
  if (@lMode = 2) and exists(select * from BBY1 a join #tmpBBY1 c on c.BBY04 = a.BBY04)
  begin
    set @ReBBX04 = (select top 1 d.BBX04
                   from BBY1 a join #tmpBBY1 c on c.BBY04 = a.BBY04 join #tmpImport d on d.BBY04 = c.BBY04)
    set @smsg = '['+@ReBBX04+']已存在此药品规格记录，不能保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  
  if (@lMode = 2) and exists(select * from BBX1 a join #tmpBBX1 c on c.BBX04 = a.BBX04)
  begin
    set @ReBBX04 = (select top 1 c.BBX04
                   from BBX1 a join #tmpBBX1 c on c.BBX04 = a.BBX04)
    set @smsg = '['+@ReBBX04+']已存在此药品品种记录，不能保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 10
  end
  
  declare @ID int, @aDate datetime
  set @aDate = GETDATE()
  Exec Core_NewID 'BBY1' , 'BBY01' , @ID out 
  set @ID = @ID + 1
  update #tmpBBY1 set BBY01 = uid + @ID
  
  Exec Core_NewID 'BBX1' , 'BBX01' , @ID out 
  set @ID = @ID + 1
  update #tmpBBX1 set BBX01 = uid + @ID
 begin tran 
   --if @lMode = 0
   --begin
   --  delete a from BDK1 a join BBX1 b on b.BBX01 = a.BBX01 where b.BDA01 <= '3'
   --  delete a from BCL1 a join BBY1 b on b.BBY01 = a.BBY01 where b.BDN01 <= '3' 
   --  delete from BBY1 where BDN01 <= '3'
   --  delete from BBX1 where BDA01 <= '3'
   --  truncate table BBT1
   --  truncate table BAG1
   --end
   if @lMode = 1
   begin
     update a set a.BDA01 = b.BDA01,a.BDO01 = b.BDO01,a.BBX05 = b.BBX05,a.BDG02 = b.BDG02
     from BBX1 a
     join #tmpImport b on b.BBX04 = a.BBX04
     
     update c set c.BAP02 = b.BAP02
     from BBX1 a
     join BBT1 c on c.BBX01 = a.BBX01
     join #tmpImport b on b.BBX04 = a.BBX04
     
     update a set a.BDN01 = b.BDA01,a.BCA01 = b.BDO01,a.BBY05 = b.BBX05,a.BBY06 = b.BBY06,a.BBE02 = b.BBE02,a.BBY08 = b.BBY08
       ,a.BBY10 = b.BBY10,a.BAX01 = b.BAX01,a.ABF01 = b.ABF01,a.AAS01 = b.AAS01,a.BBY25 = b.BBY25, a.BHH01 = b.BHH01, a.BGF01 = b.BGF01
	   ,a.BCG01 = b.BCG01,a.BCH01 = b.BCH01,a.ABA01 = b.ABA01,a.ACF01 = b.ACF01,a.BBY50 = b.BBY50,a.BBY52 = b.BBY52,a.BBY53 = b.BBY53
     from BBY1 a
     join #tmpImport b on b.BBY04 = a.BBY04
     
     update c set c.BAG03 = b.BAG03,c.BDG02A = b.BDG02A,c.BAG05 = b.BAG05,c.BDG02B = b.BDG02B,c.BAG07 = b.BAG07,c.BDG02C = b.BDG02C
       ,c.BAG09 = b.BAG09,c.BAG15 = b.BAG15
     from BBY1 a
     join BAG1 c on c.BBY01 = a.BBY01
     join #tmpImport b on b.BBY04 = a.BBY04
   end 
   
   insert into BBX1(BBX01,BDA01,BDO01,BBX04,BBX05,BBX06,BDG02,BBX08,BBX09,ABW01,BBX11
     ,BBX12,BBX13,BBX14,BBX15,ACF01,BBX17,BBX18,BBX19,BBX20,BBX21,BCW01,BCE01,BBX24  --创建时间
     ,BBX25,BBX26,ROWNR,BBX28,BBX29  ) 
   select BBX01,BDA01,BDO01,BBX04,BBX05,null,BDG02,0,0,'0',1
     ,0,'',0,0,3,1,0,'',0,0,0,0,@aDate,'9999-12-31',0,0,0,''
   from #tmpBBX1  
   
   insert into BBT1(BBX01,BAP02,BAM01,BAN01,BBA01,BBM01,BBT07,BBT08,BBT09,BBT10,BBT11,BBT12,BBT13,BBT14,AAH01,BBS01,BBT17,BBT18)
   select BBX01,BAP02,'1','1','1','1',0,0,0,0,0,0,0,0,'0',0,0,0
   from #tmpBBX1  
  
   insert into BBY1(BBY01,BDN01,BCA01,BBY04,BBY05,BBY06,BBE02,BBY08,BCF01,BBY10,BBY11,BAX01,AAS01,ABF01,ABA01,BCG01,BCH01,ACF01 
     ,BBY18,BBY19,BBY20,BBY21,BBY22,BBY23,BCK01,BBY25,BBY26,BBY27,BBY28,BBY29,BBY30,BBY31,BBY32,BBY34,BBY35,BGF01,BHH01,BBY38,BBY50,BBY52,BBY53 )
   select b.BBY01,a.BDA01,a.BDO01,b.BBY04,a.BBX05,a.BBY06,a.BBE02,a.BBY08,0,a.BBY10,'',a.BAX01,a.AAS01,a.ABF01,a.ABA01,a.BCG01,a.BCH01,a.ACF01
     ,0,0,0,0,0,0,0,a.BBY25,0,0,0,'',@aDate,'9999-12-31',0,0,0,a.BGF01,a.BHH01,0,a.BBY50,a.BBY52,a.BBY53
   from #tmpImport a
   join #tmpBBY1 b on b.BBY04 = a.BBY04 
   
   insert into BAG1(BBY01,BBX01,BAG03,BDG02A,BAG05,BDG02B,BAG07,BDG02C,BAG09,BAG40,BAG41,BAG10,BAG11,BAG12,BAG13,BAT02
     ,BAG15,BAG16,BAG17,BAG18,BAG19,BAG20,BAG21,BAG22,BAG23,BAG24,BAG25,BAG26,BAG27,BAG28,BAG29,BAG30,BAG31,BDG02D
     ,BAG33,BAG34,BBH01A,BBH01B,BAG37,BAG38,BAG39) 
   select a.BBY01,c.BBX01,b.BAG03,b.BDG02A,b.BAG05,b.BDG02B,b.BAG07,b.BDG02C,b.BAG09,'',1,null,null,0,0,null
     ,b.BAG15,'','',0,0,0,0,0,0,0,0,0,0,0,0,0,0,4
     ,1,0,0,0,'','',null
   from #tmpBBY1 a
   join #tmpImport b on b.BBY04 = a.BBY04
   join BBX1 c on c.BBX04 = b.BBX04  
   
 commit tran