现实生活中,上级和下级之间可能存在跨级的关系,比如博士生下面是初中生,中间还有高中、大学,那么就把它补充入数据库。
SQL
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[FillOrgLevel] @year int,@quarter int as begin set nocount on --填充之前的操作 --delete from [SDM].[dbo].[ocm_QV_VOrg] -- where Year=@year and (Quarter=('Q'+cast(@quarter as char(1))) or Quarter=('Q'+cast((@quarter-1) as char(1)))) --insert into [SDM].[dbo].[ocm_QV_VOrg] -- select * from [SDM].dbo.vw_QV_VOrg o -- where o.Year=@year and (o.Quarter=('Q'+cast(@quarter as char(1))) or o.Quarter=('Q'+cast((@quarter-1) as char(1)))) --开始做填充处理 declare OrgCursor cursor for select distinct o.PeriodCode,o.OrgCode,o.OrgName,o.ParentOrgCodes,o.OrgAbbr,p.OrgCode,p.OrgName,p.ParentOrgCodes,p.OrgAbbr,p.EffectiveDate,p.ExpiredDate,p.StatusCode,p.Enabled,p.StartDay,p.EndDay,p.Year,p.Month,p.YearMonth,p.Quarter,p.PTGroupId,p.ValueCN from [dbo].[ocm_QV_VOrg] o inner join [dbo].[ocm_QV_VOrg] p on o.PeriodCode=p.PeriodCode and o.ParentOrgCode=p.OrgCode where ((p.OrgAbbr ='总经理' and o.OrgAbbr in ('大区销售经理','地区销售经理','区域销售经理','销售主管','医药信息主管','医药信息顾问')) or (p.OrgAbbr='BU总监' and o.OrgAbbr in ('地区销售经理','区域销售经理','销售主管','医药信息主管','医药信息顾问')) or (p.OrgAbbr='大区销售经理' and o.OrgAbbr in ('地区销售经理','销售主管','医药信息主管','医药信息顾问')) or (p.OrgAbbr='区域销售经理' and o.OrgAbbr in ('销售主管','医药信息主管','医药信息顾问')) or (p.OrgAbbr='地区销售经理' and o.OrgAbbr in('医药信息顾问','医药信息主管'))) and o.Year=@year and (o.Quarter=('Q'+cast(@quarter as char(1))) or o.Quarter=('Q'+cast((@quarter-1) as char(1)))) open OrgCursor declare @YM char(6) declare @CId char(36) declare @CCode varchar(20) declare @COrgCodes varchar(300) declare @CAbbr varchar(100) declare @PId char(36) declare @PCode varchar(20) declare @POrgCodes varchar(300) declare @PAbbr varchar(100) declare @PEffecDate datetime declare @PExpDate datetime declare @PStatus tinyint declare @PEnable bit declare @PStartDate datetime declare @PEndDate datetime declare @PYear int declare @PMonth int declare @PYearMonth varchar(6) declare @PQuarter varchar(2) declare @PPTGroupId uniqueidentifier declare @PValueCN nvarchar(100) fetch next from OrgCursor into @YM,@CId,@CCode,@COrgCodes,@CAbbr,@PId,@PCode,@POrgCodes,@PAbbr,@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN while(@@FETCH_STATUS=0) begin if @PAbbr='总经理' begin if @CAbbr='大区销售经理' begin declare @Id0_0 char(36)=NEWID() --插入中间缺层 insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id0_0+'|','1',@CCode+'_BUD_D','BU总监',NEWID(),cast(@Id0_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) --修改子层的上级、level update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id0_0,ParentPositionId=@Id0_0,ParentOrgCodes=@POrgCodes+@Id0_0+'|'+@CId+'|',OrgLevel='2' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId --修改子层的所有下级的ParentOrgCodes update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id0_0+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr='区域销售经理' begin declare @Id0_1 char(36)=NEWID() declare @Id0_2 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id0_1+'|','1',@CCode+'_BUD_D','BU总监',NEWID(),cast(@Id0_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_2 as uniqueidentifier),cast(@Id0_1 as uniqueidentifier),@POrgCodes+@Id0_1+'|'+@Id0_2+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id0_2 as uniqueidentifier),cast(@Id0_1 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id0_2,ParentPositionId=@Id0_2,ParentOrgCodes=@POrgCodes+@Id0_1+'|'+@Id0_2+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id0_1+'|'+@Id0_2+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr='地区销售经理' begin declare @Id0_3 char(36)=NEWID() declare @Id0_4 char(36)=NEWID() declare @Id0_5 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_3 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id0_3+'|','1',@CCode+'_BUD_D','BU总监',NEWID(),cast(@Id0_3 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_4 as uniqueidentifier),cast(@Id0_3 as uniqueidentifier),@POrgCodes+@Id0_3+'|'+@Id0_4+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id0_4 as uniqueidentifier),cast(@Id0_3 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_5 as uniqueidentifier),cast(@Id0_4 as uniqueidentifier),@POrgCodes+@Id0_3+'|'+@Id0_4+'|'+@Id0_5+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id0_5 as uniqueidentifier),cast(@Id0_4 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id0_5,ParentPositionId=@Id0_5,ParentOrgCodes=@POrgCodes+@Id0_3+'|'+@Id0_4+'|'+@Id0_5+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id0_3+'|'+@Id0_4+'|'+@Id0_5+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr='销售主管' begin declare @Id0_6 char(36)=NEWID() declare @Id0_7 char(36)=NEWID() declare @Id0_8 char(36)=NEWID() declare @Id0_9 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_6 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id0_6+'|','1',@CCode+'_BUD_D','BU总监',NEWID(),cast(@Id0_6 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_7 as uniqueidentifier),cast(@Id0_6 as uniqueidentifier),@POrgCodes+@Id0_6+'|'+@Id0_7+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id0_7 as uniqueidentifier),cast(@Id0_6 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_8 as uniqueidentifier),cast(@Id0_7 as uniqueidentifier),@POrgCodes+@Id0_6+'|'+@Id0_7+'|'+@Id0_8+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id0_8 as uniqueidentifier),cast(@Id0_7 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_9 as uniqueidentifier),cast(@Id0_8 as uniqueidentifier),@POrgCodes+@Id0_6+'|'+@Id0_7+'|'+@Id0_8+'|'+@Id0_9+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id0_9 as uniqueidentifier),cast(@Id0_8 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id0_9,ParentPositionId=@Id0_9,ParentOrgCodes=@POrgCodes+@Id0_6+'|'+@Id0_7+'|'+@Id0_8+'|'+@Id0_9+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id0_6+'|'+@Id0_7+'|'+@Id0_8+'|'+@Id0_9+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr in ('医药信息顾问','医药信息主管') begin declare @Id0_10 char(36)=NEWID() declare @Id0_11 char(36)=NEWID() declare @Id0_12 char(36)=NEWID() declare @Id0_13 char(36)=NEWID() declare @Id0_14 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_10 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id0_10+'|','1',@CCode+'_BUD_D','BU总监',NEWID(),cast(@Id0_10 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_11 as uniqueidentifier),cast(@Id0_10 as uniqueidentifier),@POrgCodes+@Id0_10+'|'+@Id0_11+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id0_11 as uniqueidentifier),cast(@Id0_10 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_12 as uniqueidentifier),cast(@Id0_11 as uniqueidentifier),@POrgCodes+@Id0_10+'|'+@Id0_11+'|'+@Id0_12+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id0_12 as uniqueidentifier),cast(@Id0_11 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_13 as uniqueidentifier),cast(@Id0_12 as uniqueidentifier),@POrgCodes+@Id0_10+'|'+@Id0_11+'|'+@Id0_12+'|'+@Id0_13+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id0_13 as uniqueidentifier),cast(@Id0_12 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id0_14 as uniqueidentifier),cast(@Id0_13 as uniqueidentifier),@POrgCodes+@Id0_10+'|'+@Id0_11+'|'+@Id0_12+'|'+@Id0_13+'|'+@Id0_14+'|','5',@CCode+'_SV_D','销售主管',NEWID(),cast(@Id0_14 as uniqueidentifier),cast(@Id0_13 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id0_14,ParentPositionId=@Id0_14,ParentOrgCodes=@POrgCodes+@Id0_10+'|'+@Id0_11+'|'+@Id0_12+'|'+@Id0_13+'|'+@Id0_14+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id0_10+'|'+@Id0_11+'|'+@Id0_12+'|'+@Id0_13+'|'+@Id0_14+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end end else if @PAbbr='BU总监' begin if @CAbbr='区域销售经理' begin declare @Id1_0 char(36) set @Id1_0=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id1_0+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id1_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id1_0,ParentPositionId=@Id1_0,ParentOrgCodes=@POrgCodes+@Id1_0+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id1_0+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr='地区销售经理' begin declare @Id1_1 char(36)=NEWID() declare @Id1_2 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id1_1+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id1_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_2 as uniqueidentifier),cast(@Id1_1 as uniqueidentifier),@POrgCodes+@Id1_1+'|'+@Id1_2+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id1_2 as uniqueidentifier),cast(@Id1_1 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id1_2,ParentPositionId=@Id1_2,ParentOrgCodes=@POrgCodes+@Id1_1+'|'+@Id1_2+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id1_1+'|'+@Id1_2+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr='销售主管' begin declare @Id1_3 char(36)=NEWID() declare @Id1_4 char(36)=NEWID() declare @Id1_5 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_3 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id1_3+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id1_3 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_4 as uniqueidentifier),cast(@Id1_3 as uniqueidentifier),@POrgCodes+@Id1_3+'|'+@Id1_4+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id1_4 as uniqueidentifier),cast(@Id1_3 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_5 as uniqueidentifier),cast(@Id1_4 as uniqueidentifier),@POrgCodes+@Id1_3+'|'+@Id1_4+'|'+@Id1_5+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id1_5 as uniqueidentifier),cast(@Id1_4 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id1_5,ParentPositionId=@Id1_5,ParentOrgCodes=@POrgCodes+@Id1_3+'|'+@Id1_4+'|'+@Id1_5+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id1_3+'|'+@Id1_4+'|'+@Id1_5+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr in ('医药信息顾问','医药信息主管') begin declare @Id1_6 char(36)=NEWID() declare @Id1_7 char(36)=NEWID() declare @Id1_8 char(36)=NEWID() declare @Id1_9 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_6 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id1_6+'|','2',@CCode+'_ASM_D','大区销售经理',NEWID(),cast(@Id1_6 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_7 as uniqueidentifier),cast(@Id1_6 as uniqueidentifier),@POrgCodes+@Id1_6+'|'+@Id1_7+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id1_7 as uniqueidentifier),cast(@Id1_6 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_8 as uniqueidentifier),cast(@Id1_7 as uniqueidentifier),@POrgCodes+@Id1_6+'|'+@Id1_7+'|'+@Id1_8+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id1_8 as uniqueidentifier),cast(@Id1_7 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id1_9 as uniqueidentifier),cast(@Id1_8 as uniqueidentifier),@POrgCodes+@Id1_6+'|'+@Id1_7+'|'+@Id1_8+'|'+@Id1_9+'|','5',@CCode+'_SV_D','销售主管',NEWID(),cast(@Id1_9 as uniqueidentifier),cast(@Id1_8 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id1_9,ParentPositionId=@Id1_9,ParentOrgCodes=@POrgCodes+@Id1_6+'|'+@Id1_7+'|'+@Id1_8+'|'+@Id1_9+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id1_6+'|'+@Id1_7+'|'+@Id1_8+'|'+@Id1_9+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end end else if @PAbbr='大区销售经理' begin if @CAbbr='地区销售经理' begin declare @Id2_0 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id2_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id2_0+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id2_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id2_0,ParentPositionId=@Id2_0,ParentOrgCodes=@POrgCodes+@Id2_0+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id2_0+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr='销售主管' begin declare @Id2_1 char(36)=NEWID() declare @Id2_2 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id2_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id2_1+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id2_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id2_2 as uniqueidentifier),cast(@Id2_1 as uniqueidentifier),@POrgCodes+@Id2_1+'|'+@Id2_2+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id2_2 as uniqueidentifier),cast(@Id2_1 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id2_2,ParentPositionId=@Id2_2,ParentOrgCodes=@POrgCodes+@Id2_1+'|'+@Id2_2+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id2_1+'|'+@Id2_2+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr in ('医药信息顾问','医药信息主管') begin declare @Id2_3 char(36)=NEWID() declare @Id2_4 char(36)=NEWID() declare @Id2_5 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id2_3 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id2_3+'|','3',@CCode+'_RSM_D','区域销售经理',NEWID(),cast(@Id2_3 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id2_4 as uniqueidentifier),cast(@Id2_3 as uniqueidentifier),@POrgCodes+@Id2_3+'|'+@Id2_4+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id2_4 as uniqueidentifier),cast(@Id2_3 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id2_5 as uniqueidentifier),cast(@Id2_4 as uniqueidentifier),@POrgCodes+@Id2_3+'|'+@Id2_4+'|'+@Id2_5+'|','5',@CCode+'_SV_D','销售主管',NEWID(),cast(@Id2_5 as uniqueidentifier),cast(@Id2_4 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id2_5,ParentPositionId=@Id2_5,ParentOrgCodes=@POrgCodes+@Id2_3+'|'+@Id2_4+'|'+@Id2_5+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id2_3+'|'+@Id2_4+'|'+@Id2_5+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end end else if @PAbbr='区域销售经理' begin if @CAbbr ='销售主管' begin declare @Id3_0 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id3_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id3_0+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id3_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id3_0,ParentPositionId=@Id3_0,ParentOrgCodes=@POrgCodes+@Id3_0+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id3_0+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end else if @CAbbr in ('医药信息顾问','医药信息主管') begin declare @Id3_1 char(36)=NEWID() declare @Id3_2 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id3_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id3_1+'|','4',@CCode+'_DSM_D','地区销售经理',NEWID(),cast(@Id3_1 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id3_2 as uniqueidentifier),cast(@Id3_1 as uniqueidentifier),@POrgCodes+@Id3_1+'|'+@Id3_2+'|','5',@CCode+'_SV_D','销售主管',NEWID(),cast(@Id3_2 as uniqueidentifier),cast(@Id3_1 as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id3_2,ParentPositionId=@Id3_2,ParentOrgCodes=@POrgCodes+@Id3_1+'|'+@Id3_2+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id3_1+'|'+@Id3_2+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end end else if @PAbbr='地区销售经理' begin if @CAbbr in ('医药信息顾问','医药信息主管') begin declare @Id4_0 char(36)=NEWID() insert into [dbo].[ocm_QV_VOrg] values(@YM,cast(@Id4_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@POrgCodes+@Id4_0+'|','5',@CCode+'_SV_D','销售主管',NEWID(),cast(@Id4_0 as uniqueidentifier),cast(@PId as uniqueidentifier),@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN) update [dbo].[ocm_QV_VOrg] set ParentOrgCode=@Id4_0,ParentPositionId=@Id4_0,ParentOrgCodes=@POrgCodes+@Id4_0+'|'+@CId+'|' where PeriodCode=@YM and OrgCode=@CId and ParentOrgCode=@PId update [dbo].[ocm_QV_VOrg] set ParentOrgCodes=@POrgCodes+@Id4_0+'|'+@CId+'|'+SUBSTRING(ParentOrgCodes,LEN(@COrgCodes)+1,LEN(ParentOrgCodes)-LEN(@COrgCodes)) where PeriodCode=@YM and ParentOrgCodes like @COrgCodes+'%' end end fetch next from OrgCursor into @YM,@CId,@CCode,@COrgCodes,@CAbbr,@PId,@PCode,@POrgCodes,@PAbbr,@PEffecDate,@PExpDate,@PStatus,@PEnable,@PStartDate,@PEndDate,@PYear,@PMonth,@PYearMonth,@PQuarter,@PPTGroupId,@PValueCN end close OrgCursor deallocate OrgCursor -------------------------------------reupdate the ParentOrgCodes and Level update [dbo].[ocm_QV_VOrg] set [dbo].[ocm_QV_VOrg].ParentOrgCodes=ss.pcodes+cast(ss.code as CHAR(36))+'|' from [dbo].[ocm_QV_VOrg] o inner join (select distinct o.PeriodCode as ym,o.OrgCode as code,o.OrgLevel,p.ParentOrgCodes as pcodes from [dbo].[ocm_QV_VOrg] o inner join [dbo].[ocm_QV_VOrg] p on o.PeriodCode=p.PeriodCode and o.ParentOrgCode=p.OrgCode where (o.OrgAbbr='总经理' and o.OrgLevel<0) or (o.OrgAbbr='BU总监' and o.OrgLevel<1) or (o.OrgAbbr='大区销售经理' and o.OrgLevel<2) or (o.OrgAbbr='区域销售经理' and o.OrgLevel<3) or (o.OrgAbbr='地区销售经理' and o.OrgLevel<4) or (o.OrgAbbr='销售主管' and o.OrgLevel<5) or (o.OrgAbbr in ('医药信息顾问','医药信息主管') and o.OrgLevel<6) ) as ss on o.PeriodCode=ss.ym and o.OrgCode=ss.code where o.Year=@year and (o.Quarter=('Q'+cast(@quarter as char(1))) or o.Quarter=('Q'+cast((@quarter-1) as char(1)))) update o set o.OrgLevel=LEN(o.ParentOrgCodes)/37-1 from [dbo].[ocm_QV_VOrg] o where o.Year=@year and (o.Quarter=('Q'+cast(@quarter as char(1))) or o.Quarter=('Q'+cast((@quarter-1) as char(1)))) --exec [dbo].[FillOrgLevel] --delete from [SDM-Report].[dbo].[ocm_QV_VOrg] --insert into [SDM-Report].[dbo].[ocm_QV_VOrg] select * from [SDM].[dbo].[ocm_QV_VOrg] -------------------------------------reupdate the ParentOrgCodes and Level end