U8v821升级出错求救！！！

zhang1yu

数据库名: UFDATA_002_2002
E:\U8SOFT\Admin\Update822.sql
错误信息:
-2147217900
列名 'cInvM_Unit' 无效。
执行如下语句时出错:

CREATE PROCEDURE Sa_MoveSaleProfit(   /* 取得销售毛利分析数据存储过程 作者：刘小东 */

@chrtable  varchar(200) = null ,  /*保存销售毛利分析明细账*/
@chrWhere1  varchar(255) =null ,     /* 查询条件名称,不包括日期条件 */
@chrStartDate varchar (200)=null,  /* 分析上期日期区间*/
@chrEndDate varchar (200)=null,  /* 分析本期日期区间*/
@chrDate  varchar (200)=null ,/* 总的日期区间 */
@chrBZ varchar(30)   /* 币种条件 */
)
AS
declare @chrSQL varchar(4000)
declare @chrCost varchar(50)
declare @chrWhere varchar(1000)
declare @chrCostWhere varchar(300)
declare @chrstartdate1 varchar (50)
/*   取得除去日期的条件  */
if  ltrim(rtrim(@chrwhere1 ))  = 'newReport_ParameterFromTempTable'
  begin
   /*条件参数通过临时表传递 */
   set @chrCostWhere = (select name from tempdb..newReportParameter )
  end
else  /*条件参数直接传递  */
  begin  
   set @chrCostWhere  = rtrim(ltrim(@chrwhere1))
  end
/*构造收入条件和成本条件  */
set  @chrwhere  = @chrCostWhere
if  not (@chrbz is null ) and ltrim(rtrim(@chrbz)) <> ''  
  if  not(@chrwhere is null  ) and  ltrim(rtrim(@chrWhere)) <> ''
   set @chrwhere = @chrwhere + ' and ' + @chrBZ
/* 取销售系统启用日期 */
set @chrStartDate1 = (select isnull(cValue,'1900-01-01') from accinformation  where cSysid='Sa' and cName='dStartDate' )  
if  @chrstartdate1 <> ''  
begin   
  if  len(ltrim(rtrim(@chrstartdate1))) <= 8 set @chrstartdate1 = convert(varchar(10),convert(smalldatetime,@chrstartdate1,2),121)
  set @chrstartDate1 = ' and SalebillVouch.dDate>=''' + @chrstartdate1 + ''''
end   
/* 查询成本明细账，计算出成本，毛利，成本单价，销售单价，数量，金额   */
if  exists(select * from tempdb..sysobjects where name='Pr_SaleIncomefirst') drop table tempdb..Pr_SaleIncomefirst
/*  查询收入的，金额售价，售价  */
set  @chrSQL ='
SELECT
       SaleBillVouchs.AutoID AS AutoID,
       SaleBillVouchs.iQuantity AS iQuantity,
       SaleBillVouchs.iNatUnitPrice AS iNatUnitPrice,
       SaleBillVouchs.iNatMoney as iNatMoney ,
       SaleBillVouch.cExch_name as cExch_name,
       SaleBillVouch.dDate as dDate,
      
       SaleType.cSTName as cSTName ,
       Customer.cCusName as cCusName ,
       CustomerClass.cCCName as cCCname ,  
       Inventory.cInvName as cInvName ,
       Inventory.cInvStd as cInvStd ,  
       Inventory.cInvM_Unit as cInvM_Unit,
       Inventory.cInvCCode as cInvCCode,
         
       Department.cDepName as cDepName ,   
       Department.cDepCode as cDepCode ,
       SaleBillVouch.cSTCode as cSTCode,
       SaleBillVouch.cCusCode as cCusCode,
       SaleBillVouchs.cInvCode as cInvCode,
       Customer.cCCCode as cCCCode ,
       Inventory.bService as bService   
      
INTO  tempdb..Pr_SaleInComefirst
FROM SaleBillVouchs INNER JOIN SaleBillVouch On SalebillVouchs.sbvid = SalebillVouch.sbvid
  LEFT JOIN  Customer On SalebillVouch.cCusCode   = Customer.cCusCode
  LEFT JOIN  CustomerClass On Customer.cCCCode  =  CustomerClass.cCCCode  
  LEFT JOIN  SaleType On SaleBillVouch.cSTCode  = SaleType.cSTCode
  LEFT JOIN  Inventory On SaleBillVouchs.cInvCode = Inventory.cInvCode
  LEFT JOIN  Department On SaleBillVouch.cDepCode   = Department.cDepCode
WHERE isnull(Salebillvouch.cInvalider,'''')=''''
  and  isnull(Salebillvouch.cSTCode,'''') <> ''''
  ' + @chrstartdate1  /*作废发票不能计算在内，销售类型不能为空,开票日期必须大于系统起用日期 */
/*  执行存储过程，取得本期销售内容  */
if not  (@chrDate is null  ) and  ltrim(rtrim(@chrdate)) <>'' set @chrSQL  = @chrSQL + ' And ' + @chrDate
exec (@chrsql )
/*  按普通条件抽取数据 */
if  exists(select * from tempdb..sysobjects where name='Pr_SaleIncome') drop table tempdb..Pr_SaleIncome
set @chrsql  = 'Select * into tempdb..Pr_saleIncome from tempdb..Pr_saleincomefirst '
if not ( @chrwhere is null) and @chrwhere<>'' set @chrSQL = @chrSQL +' WHERE ' + @chrWhere
exec (@chrsql )

/* 计算成本明细，按发票ID号取得成本明细 */
/*数据准备*/
if  exists(select * from tempdb..sysobjects where name='Pr_SaleCostFirst') drop table  tempdb..Pr_SaleCostFirst   SELECT IA_Subsidiary.dKeepDate AS ddate,
       IA_Subsidiary.cVouType AS cvoutype,
       IA_Subsidiary.iOutCost AS iOutCost,
       case when (cWhvaluestyle='计划价法'or cWhvalueStyle='售价法') and IA_Subsidiary.bMoneyFlag=1 then
   isnull(IA_Subsidiary.iAOutPrice,0)- isnull( IA_Subsidiary.iDebitDifCost,0) + isnull( IA_Subsidiary.iCreditDifCost,0)
     else
   isnull(IA_Subsidiary.iAOutPrice ,0)
     end  
       as  iAOutPrice,   
       IA_SubSidiary.cSTCode as cSTCode,
       Customer.cCCCode as cCCCode ,
       Inventory.cInvCode as cInvCode ,  
       Inventory.cInvCCode as cInvCCode ,
       IA_Subsidiary.cCusCode AS cCusCode,
       IA_Subsidiary.cAccDep AS cDepCode,
       IA_Subsidiary.cDepCode AS cAccDep ,
       IA_Subsidiary.iMonth AS iMonth,
       IA_Subsidiary.bMoneyFlag AS bMoneyFlag,
       IA_Subsidiary.bSale AS bSale,
       IA_Subsidiary.cWhCode as cWhCode  ,  
       Warehouse.cWhValueStyle AS cWhValueStyle ,
       IA_Subsidiary.cBillCode AS cBillCode,
       IA_Subsidiary.cDLCode AS cDlCode ,
       IA_Subsidiary.iAOutQuantity as iAOutQuantity ,
       SaleType.cSTName as cSTName ,
       Customer.cCusName as cCusName ,
       CustomerClass.cCCName as cCCname ,  
       Inventory.cInvName as cInvName ,
       Inventory.cInvStd as cInvStd ,  
       Inventory.cInvM_Unit as cInvM_Unit,
        
       Department.cDepName as cDepName  ,
       Inventory.bService  as bService   
  
       INTO tempdb..Pr_SaleCostfirst
       FROM IA_Subsidiary LEFT JOIN Warehouse ON IA_Subsidiary.cWhCode = Warehouse.cWhCode
         LEFT JOIN Customer On IA_Subsidiary.cCusCode  = Customer.cCusCode
   LEFT JOIN Inventory On IA_SubSidiary.cInvCode = Inventory.cInvCode  
   LEFT JOIN Department On IA_SubSidiary.cAccDep = Department.cDepCode
   LEFT JOIN  CustomerClass On Customer.cCCCode  =  CustomerClass.cCCCode  
   LEFT JOIN  SaleType On IA_SubSidiary.cSTCode  = SaleType.cSTCode
       WHERE bRdFlag=0  AND (cVouType in ('26','27','28','29','32') or (cVoutype='21' and Ia_subsidiary.bSale=1 ) )

/*取得成本明细账数据 */
if  exists(select * from tempdb..sysobjects where name='pr_SaleCostsec') drop table  tempdb..pr_salecostsec  
set  @chrsql ='select * into tempdb..pr_salecostSec from tempdb..pr_salecostfirst '
if not (@chrdate is null) and ltrim(rtrim(@chrdate))<>''
begin   
  set @chrSQL = @chrSQL +' Where ' + @chrdate
  if  not (@chrcostwhere is null ) and ltrim(rtrim(@chrcostwhere)) <>''
   set @chrsql = @chrsql + ' and ' + @chrCostWhere
end
else
begin  
  if  not (@chrcostwhere is null ) and ltrim(rtrim(@chrcostwhere)) <>''
   set @chrsql = @chrsql + ' where ' + @chrCostWhere
end  
exec ( @chrsql )
/* 计算计划价/售价成本明细账 */
set @chrCost=(SELECT cValue FROM AccInformation WHERE (cSysID = 'ia') AND (cName = 'cvaluestyle'))
if @chrCost ='按部门核算'
begin
  update tempdb..pr_salecostsec   set tempdb..pr_salecostsec.iAOutPrice=
   case when (tempdb..pr_salecostsec.cWhvalueStyle='售价法' ) then  
     tempdb..pr_salecostsec.iAoutPrice*(1- isnull(ia_summary.iDifRate,0))
    else  
     tempdb..pr_salecostsec.iAoutPrice*(1 + isnull(ia_summary.iDifRate,0))
     end
  from tempdb..pr_salecostsec inner join ia_summary
   on   tempdb..pr_salecostsec.iMonth = ia_summary.iMonth and
        tempdb..pr_salecostsec.cAccDep = ia_summary.cDepCode and   
        tempdb..pr_salecostsec.cInvCode  = ia_summary.cInvCode
  where  (tempdb..pr_salecostsec.cWhvaluestyle='计划价法'or tempdb..pr_salecostsec.cWhvalueStyle='售价法')
   and tempdb..pr_salecostsec.bMoneyFlag=0 and  tempdb..pr_salecostsec.bSale<> 1
   
end
else
begin
  update tempdb..pr_salecostsec set tempdb..pr_salecostsec.iAOutPrice=
   case when (tempdb..pr_salecostsec.cWhvalueStyle='售价法' ) then  
     tempdb..pr_salecostsec.iAoutPrice*(1- isnull(ia_summary.iDifRate,0))
    else  
     tempdb..pr_salecostsec.iAoutPrice*(1 + isnull(ia_summary.iDifRate,0))
     end
  from tempdb..pr_salecostsec  inner join ia_summary  
   on   tempdb..pr_salecostsec.iMonth = ia_summary.iMonth and
        tempdb..pr_salecostsec.cWhCode = ia_summary.cWhCode  and
        tempdb..pr_salecostsec.cInvCode  = ia_summary.cInvCode
  where  (tempdb..pr_salecostsec.cWhvaluestyle='计划价法'or tempdb..pr_salecostsec.cWhvalueStyle='售价法')
   and tempdb..pr_salecostsec.bMoneyFlag=0 and  tempdb..pr_salecostsec.bSale<> 1
end
/* 计划价售价法，重新计算成本单价  */
update tempdb..pr_salecostsec
set ioutCost = iAoutPrice /
  case when iAOutQuantity =0 then
   1
  else
   isnull(iAOutQuantity ,0 )
         end   
From tempdb..pr_salecostsec
where cWhvaluestyle='计划价法' or cWhvaluestyle='售价法'

if  exists(select * from tempdb..sysobjects where name='pr_Saleincomecost') drop table  tempdb..pr_saleincomecost

/* 将没有成本项目追加到销售明细账 */
select  
  AutoID,
  isnull (iQuantity,0) as iQuantity ,
  isnull(iNatUnitPrice,0) as iNatUnitPrice ,
  isnull(iNatMoney,0) as iNatMoney ,
  cExch_name, dDate,
  cSTName , cCusName ,
  cCCname , cInvName ,
  cInvStd , cInvM_Unit,
  cDepName , cDepCode ,cSTCode,
  cCusCode,cInvCode,
  cCCCode ,cInvCCode,
  iNatMoney -iNatMoney  as  iOutCost,
  iNatMoney -iNatMoney  as  iAOutPrice,
  iNatMoney -iNatMoney as iAOutQuantity
  into tempdb..pr_saleincomecost

from   tempdb..pr_saleincome  
/* 将成本项目追加到销售明细账   */
insert into  tempdb..pr_saleincomecost (
  AutoID, iQuantity,
  iNatUnitPrice,  iNatMoney ,
  cExch_name, dDate,
  cSTName , cCusName ,
  cCCname , cInvName ,
  cInvStd , cInvM_Unit,
  cDepName ,cDepCode ,cSTCode,
  cCusCode,cInvCode,
  cCCCode ,cInvCCode,
  iOutCost, iAOutPrice ,
  iAOutQuantity
  )
select  
  0, 0 ,
  0,  0 ,
  '', dDate,
  cSTName , cCusName ,
  cCCname , cInvName ,
  cInvStd , cInvM_Unit,
  cDepName ,cDepCode ,
  cSTCode, cCusCode,cInvCode,
  cCCCode ,cInvCCode,
  isnull(iOutCost , 0),
  isnull( iAOutPrice ,0 ) ,
  isnull(iAOutQuantity,0)
from   tempdb..pr_saleCostsec

/*  组织销售毛利分析明细账 临时表  */
if  exists(select * from tempdb..sysobjects where name='pr_SaleProfit' ) drop table tempdb..pr_SaleProfit
/*   取得销售毛利分析前期数据   */
if  not ( @chrStartDate is null  ) and  ltrim(rtrim( @chrStartDate )) <> ''
begin
  set @chrSQL = 'Select cDepName,cExch_name,cSTName,cCCname,
   cInvCCode,cInvName,cCusName,cInvStd,cInvM_Unit,
   iQuantity as q_iQuantity ,
   case when iQuantity = 0 then
    0
   else  
    iNatMoney / iQuantity
   end
   as q_iNatUnitPrice,
   iNatMoney  as q_iNatMoney ,
   case when iAOutQuantity  = 0 then
    0
   else
    iAOutPrice / iAOutQuantity
   end   as q_iOutCost ,
   iAOutPrice as q_iAOutPrice ,  
   
   iNatMoney - iAoutPrice as q_iProfit ,
   iNatMoney -iNatMoney as b_iQuantity ,
   iNatMoney -iNatMoney  as b_iNatUnitPrice,
   iNatMoney -iNatMoney as b_iNatMoney ,  
   iNatMoney -iNatMoney as b_iOutCost,
   iNatMoney -iNatMoney as b_iAOutPrice ,  
   iNatMoney -iNatMoney  as b_iProfit ,
   iNatMoney -iNatMoney as c_iProfit,
   iNatMoney -iNatMoney  as c_iQuantity ,
   iNatMoney -iNatMoney as c_iUnitPrice ,
   iNatMoney -iNatMoney  as C_iOutCost
   into  tempdb..pr_SaleProfit From tempdb..pr_SaleIncomeCost Where ' + @chrStartDate
  exec( @chrSQL )  
end   
/*   取得销售毛利分析本期数据   */
if  exists(select * from tempdb..sysobjects where name='pr_saleprofit' )
begin    /*  有期初数据则追加本期数据   */
  set @chrSQL = 'Insert into tempdb..pr_SaleProfit  Select cDepName,cExch_name,cSTName,cCCname,
   cInvCCode,cInvName,cCusName,cInvStd,cInvM_Unit,
   iNatMoney -iNatMoney  as q_iQuantity ,
   iNatMoney -iNatMoney  as q_iNatUnitPrice,
   0  as q_iNatMoney ,   
   iNatMoney -iNatMoney  as q_iOutCost,
   0  as q_iAOutPrice  ,  
   iNatMoney -iNatMoney  as q_iProfit ,
   
   iQuantity as b_iQuantity ,
   case when  iQuantity = 0 then  
    0
   else
    iNatMoney / iQuantity  
   end as b_iNatUnitPrice,
   iNatMoney  as b_iNatMoney  ,
   case when iAOutQuantity = 0 then
    0
   else
    iAOutPrice / iAOutQuantity   
   end  as b_iOutCost ,
   iAOutPrice as b_iAOutPrice  ,
   iNatMoney - iAoutPrice as b_iProfit ,
   iNatMoney -iNatMoney  as c_iProfit,
   iNatMoney -iNatMoney  as c_iQuantity ,
   iNatMoney -iNatMoney  as c_iUnitPrice ,
   iNatMoney -iNatMoney as C_iOutCost
   From tempdb..pr_SaleIncomeCost '  
  if  not (@chrEndDate is null ) and ltrim(rtrim(@chrEndDate )) <> '' set @chrsql  =  @chrsql  +  ' Where ' + @chrEndDate   
  exec (@chrsql)
end
else
begin  /*  无期初数据则直接创建临时表  */   
  set @chrSQL = 'Select cDepName,cExch_name,cSTName,cCCname,
   cInvCCode,cInvName,cCusName,cInvStd,cInvM_Unit,
   iNatMoney -iNatMoney  as q_iQuantity ,
   iNatMoney -iNatMoney  as q_iNatUnitPrice,
   iNatMoney -iNatMoney  as q_iNatMoney ,  
   iNatMoney -iNatMoney  as q_iOutCost,
   iNatMoney -iNatMoney  as q_iAOutPrice  ,  
   iNatMoney -iNatMoney  as q_iProfit ,
   iQuantity as b_iQuantity ,
   case when  iQuantity  = 0 then
    0
   else
    iNatMoney / iQuantity   
   end  as b_iNatUnitPrice,
   iNatMoney  as b_iNatMoney  ,
   case when  iAOutQuantity  = 0  then
    0
   else
    iAOutPrice / iAOutQuantity
   end  as b_iOutCost ,
   iAOutPrice as b_iAOutPrice  ,
   iNatMoney - iAoutPrice as b_iProfit ,
   iNatMoney -iNatMoney as c_iProfit,
   iNatMoney -iNatMoney  as c_iQuantity ,
   iNatMoney -iNatMoney as c_iUnitPrice ,
   iNatMoney -iNatMoney as C_iOutCost
   into  tempdb..pr_SaleProfit  From tempdb..pr_SaleIncomeCost '  
  if  not (@chrEndDate is null ) and ltrim(rtrim(@chrEndDate )) <> '' set @chrsql  =  @chrsql  +  ' Where ' + @chrEndDate
  exec (@chrsql )
end
/*  计算毛利变动及影响原因 */
/*  边际贡献  = 单位售价 - 单位成本
     毛利金额 = 本期毛利 - 上期毛利
     成本影响 = 本期数量 * 本期成本 - 上期数量* 上期成本  
     售价影响 = 本期数量 * 本期售价 - 上期数量* 上期售价
     数量影响 = 本期边际贡献 * 本期数量 - 上期边际贡献* 上期数量 */
if  exists(select * from tempdb..sysobjects where name='pr_SaleProfit' )  
begin
  set @chrsql = 'Update tempdb..pr_SaleProfit  set c_iprofit = b_iprofit- q_iprofit ,
         c_iquantity = (b_iNatUnitPrice - b_iOutCost) * b_iQuantity - (q_iNatUnitPrice - q_iOutCost)*q_iquantity,
         c_iUnitPrice =b_iQuantity * b_iNatUnitPrice - q_iQuantity * q_iNatUnitPrice,
         c_iOutCost = b_iQuantity * b_iOutCost - q_iQuantity * q_iOutCost '
  exec (@chrsql )
end
/*存货分类按编码级次展开  */

declare @chrfieldlist varchar(4000)
declare @chrInner   varchar (4000)
declare @chrbmjc  varchar(20)
declare @i int  
declare @n int  
/*存货分类按编码级次展开  */
/*取得存货分类编码级次 */
set @chrbmjc   = ''
set @chrbmjc   = (select  cValue  from accInformation where cName ='cGoodClass')
/*连接展开字符串*/
set @chrfieldlist  = ''
set @chrInner = ''
set @i  =1  
set @n = 0  
while (@i <= 8 )
begin  
  set @n = @n + substring(@chrbmjc,@i,1)  /* 分解级次 */
  /* 组合字段列表 */
  set @chrfieldlist = @chrfieldlist +'b'+ ltrim(rtrim(str(@i)))
    + '.cInvCName as cInvCName' + ltrim(rtrim(@i)) +','
  /* 组合关联 */
  if (@i <= len(@chrbmjc))
  begin  
   set @chrinner  = @chrinner + ' left join inventoryClass b'
     + ltrim(rtrim(str(@i)))
     + ' on b'+ ltrim(rtrim(str(@i)))
     +'.cInvCCode = left( a.cInvCCode,'
     +ltrim(rtrim(str(@n)))+')'
  end
  else  
  begin
   set @chrinner  = @chrinner +
    ' left join inventoryClass  b'
    + ltrim(rtrim(str(@i)))
    + ' on a.cInvCCode = b'
    + ltrim(rtrim(str(@i)))+ '.cInvCCode '
  end  
  set @i = @i +1
end
/*  组织销售毛利分析明细账   */
if  exists(select * from tempdb..sysobjects where name=@chrtable ) exec( 'drop table tempdb..' + @chrtable )
set @chrsql   = 'select ' + @chrfieldlist + ' a.* into tempdb..'
  + @chrtable +   ' from tempdb..pr_SaleProfit a ' + @chrinner  
exec(@chrsql )



效率测试报告：开始升级UFDATA_002_2002数据库

    升级起始时间：2018-04-10 17:44:14，结束时间：2018-04-10 17:44:15，0小时0分钟1秒。

nazi8888

表里面缺少字段，添加上就可以了！

yaoxiaoxiang

执行下，看看那个字段出问题了。。。。。。。

y4633045

数据表Inventory缺少cInvM_Unit 字段。
alter table  Inventory  ADD cInvM_Unit     varchar (8)

偷媳妇

如果是总账就找个U8 低版本环境更直接导标标，复杂模块踏实跟踪没好办法

偷媳妇

确实取消一些字段的必填属性导表快的多的多