技术区 › 悬赏问答区 › U8v821升级出错求救！！！

zhang1yu 发表于 2018-4-10 18:40:41

U8v821升级出错求救！！！

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

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

@chrtablevarchar(200) = null ,/*保存销售毛利分析明细账*/
@chrWhere1varchar(255) =null ,   /* 查询条件名称,不包括日期条件 */
@chrStartDate varchar (200)=null,/* 分析上期日期区间*/
@chrEndDate varchar (200)=null,/* 分析本期日期区间*/
@chrDatevarchar (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)
/*   取得除去日期的条件*/
ifltrim(rtrim(@chrwhere1 ))= 'newReport_ParameterFromTempTable'
begin
   /*条件参数通过临时表传递 */
   set @chrCostWhere = (select name from tempdb..newReportParameter )
end
else/*条件参数直接传递*/
begin
   set @chrCostWhere= rtrim(ltrim(@chrwhere1))
end
/*构造收入条件和成本条件*/
set@chrwhere= @chrCostWhere
ifnot (@chrbz is null ) and ltrim(rtrim(@chrbz)) <> ''
ifnot(@chrwhere is null) andltrim(rtrim(@chrWhere)) <> ''
   set @chrwhere = @chrwhere + ' and ' + @chrBZ
/* 取销售系统启用日期 */
set @chrStartDate1 = (select isnull(cValue,'1900-01-01') from accinformationwhere cSysid='Sa' and cName='dStartDate' )
if@chrstartdate1 <> ''
begin   
iflen(ltrim(rtrim(@chrstartdate1))) <= 8 set @chrstartdate1 = convert(varchar(10),convert(smalldatetime,@chrstartdate1,2),121)
set @chrstartDate1 = ' and SalebillVouch.dDate>=''' + @chrstartdate1 + ''''
end   
/* 查询成本明细账，计算出成本，毛利，成本单价，销售单价，数量，金额   */
ifexists(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   
      
INTOtempdb..Pr_SaleInComefirst
FROM SaleBillVouchs INNER JOIN SaleBillVouch On SalebillVouchs.sbvid = SalebillVouch.sbvid
LEFT JOINCustomer On SalebillVouch.cCusCode   = Customer.cCusCode
LEFT JOINCustomerClass On Customer.cCCCode=CustomerClass.cCCCode
LEFT JOINSaleType On SaleBillVouch.cSTCode= SaleType.cSTCode
LEFT JOINInventory On SaleBillVouchs.cInvCode = Inventory.cInvCode
LEFT JOINDepartment On SaleBillVouch.cDepCode   = Department.cDepCode
WHERE isnull(Salebillvouch.cInvalider,'''')=''''
andisnull(Salebillvouch.cSTCode,'''') <> ''''
' + @chrstartdate1/*作废发票不能计算在内，销售类型不能为空,开票日期必须大于系统起用日期 */
/*执行存储过程，取得本期销售内容*/
if not(@chrDate is null) andltrim(rtrim(@chrdate)) <>'' set @chrSQL= @chrSQL + ' And ' + @chrDate
exec (@chrsql )
/*按普通条件抽取数据 */
ifexists(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号取得成本明细 */
/*数据准备*/
ifexists(select * from tempdb..sysobjects where name='Pr_SaleCostFirst') drop tabletempdb..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
       asiAOutPrice,   
       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.bServiceas 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 JOINCustomerClass On Customer.cCCCode=CustomerClass.cCCCode
   LEFT JOINSaleType On IA_SubSidiary.cSTCode= SaleType.cSTCode
       WHERE bRdFlag=0AND (cVouType in ('26','27','28','29','32') or (cVoutype='21' and Ia_subsidiary.bSale=1 ) )

/*取得成本明细账数据 */
ifexists(select * from tempdb..sysobjects where name='pr_SaleCostsec') drop tabletempdb..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
ifnot (@chrcostwhere is null ) and ltrim(rtrim(@chrcostwhere)) <>''
   set @chrsql = @chrsql + ' and ' + @chrCostWhere
end
else
begin
ifnot (@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 andtempdb..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_salecostsecinner join ia_summary
   on   tempdb..pr_salecostsec.iMonth = ia_summary.iMonth and
      tempdb..pr_salecostsec.cWhCode = ia_summary.cWhCodeand
      tempdb..pr_salecostsec.cInvCode= ia_summary.cInvCode
where(tempdb..pr_salecostsec.cWhvaluestyle='计划价法'or tempdb..pr_salecostsec.cWhvalueStyle='售价法')
   and tempdb..pr_salecostsec.bMoneyFlag=0 andtempdb..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='售价法'

ifexists(select * from tempdb..sysobjects where name='pr_Saleincomecost') drop tabletempdb..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 -iNatMoneyasiOutCost,
iNatMoney -iNatMoneyasiAOutPrice,
iNatMoney -iNatMoney as iAOutQuantity
into tempdb..pr_saleincomecost

from   tempdb..pr_saleincome
/* 将成本项目追加到销售明细账   */
insert intotempdb..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

/*组织销售毛利分析明细账 临时表*/
ifexists(select * from tempdb..sysobjects where name='pr_SaleProfit' ) drop table tempdb..pr_SaleProfit
/*   取得销售毛利分析前期数据   */
ifnot ( @chrStartDate is null) andltrim(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,
   iNatMoneyas 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 -iNatMoneyas b_iNatUnitPrice,
   iNatMoney -iNatMoney as b_iNatMoney ,
   iNatMoney -iNatMoney as b_iOutCost,
   iNatMoney -iNatMoney as b_iAOutPrice ,
   iNatMoney -iNatMoneyas b_iProfit ,
   iNatMoney -iNatMoney as c_iProfit,
   iNatMoney -iNatMoneyas c_iQuantity ,
   iNatMoney -iNatMoney as c_iUnitPrice ,
   iNatMoney -iNatMoneyas C_iOutCost
   intotempdb..pr_SaleProfit From tempdb..pr_SaleIncomeCost Where ' + @chrStartDate
exec( @chrSQL )
end   
/*   取得销售毛利分析本期数据   */
ifexists(select * from tempdb..sysobjects where name='pr_saleprofit' )
begin    /*有期初数据则追加本期数据   */
set @chrSQL = 'Insert into tempdb..pr_SaleProfitSelect cDepName,cExch_name,cSTName,cCCname,
   cInvCCode,cInvName,cCusName,cInvStd,cInvM_Unit,
   iNatMoney -iNatMoneyas q_iQuantity ,
   iNatMoney -iNatMoneyas q_iNatUnitPrice,
   0as q_iNatMoney ,   
   iNatMoney -iNatMoneyas q_iOutCost,
   0as q_iAOutPrice,
   iNatMoney -iNatMoneyas q_iProfit ,
   
   iQuantity as b_iQuantity ,
   case wheniQuantity = 0 then
    0
   else
    iNatMoney / iQuantity
   end as b_iNatUnitPrice,
   iNatMoneyas b_iNatMoney,
   case when iAOutQuantity = 0 then
    0
   else
    iAOutPrice / iAOutQuantity   
   endas b_iOutCost ,
   iAOutPrice as b_iAOutPrice,
   iNatMoney - iAoutPrice as b_iProfit ,
   iNatMoney -iNatMoneyas c_iProfit,
   iNatMoney -iNatMoneyas c_iQuantity ,
   iNatMoney -iNatMoneyas c_iUnitPrice ,
   iNatMoney -iNatMoney as C_iOutCost
   From tempdb..pr_SaleIncomeCost '
ifnot (@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 -iNatMoneyas q_iQuantity ,
   iNatMoney -iNatMoneyas q_iNatUnitPrice,
   iNatMoney -iNatMoneyas q_iNatMoney ,
   iNatMoney -iNatMoneyas q_iOutCost,
   iNatMoney -iNatMoneyas q_iAOutPrice,
   iNatMoney -iNatMoneyas q_iProfit ,
   iQuantity as b_iQuantity ,
   case wheniQuantity= 0 then
    0
   else
    iNatMoney / iQuantity   
   endas b_iNatUnitPrice,
   iNatMoneyas b_iNatMoney,
   case wheniAOutQuantity= 0then
    0
   else
    iAOutPrice / iAOutQuantity
   endas b_iOutCost ,
   iAOutPrice as b_iAOutPrice,
   iNatMoney - iAoutPrice as b_iProfit ,
   iNatMoney -iNatMoney as c_iProfit,
   iNatMoney -iNatMoneyas c_iQuantity ,
   iNatMoney -iNatMoney as c_iUnitPrice ,
   iNatMoney -iNatMoney as C_iOutCost
   intotempdb..pr_SaleProfitFrom tempdb..pr_SaleIncomeCost '
ifnot (@chrEndDate is null ) and ltrim(rtrim(@chrEndDate )) <> '' set @chrsql=@chrsql+' Where ' + @chrEndDate
exec (@chrsql )
end
/*计算毛利变动及影响原因 */
/*边际贡献= 单位售价 - 单位成本
   毛利金额 = 本期毛利 - 上期毛利
   成本影响 = 本期数量 * 本期成本 - 上期数量* 上期成本
   售价影响 = 本期数量 * 本期售价 - 上期数量* 上期售价
   数量影响 = 本期边际贡献 * 本期数量 - 上期边际贡献* 上期数量 */
ifexists(select * from tempdb..sysobjects where name='pr_SaleProfit' )
begin
set @chrsql = 'Update tempdb..pr_SaleProfitset 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 @chrbmjcvarchar(20)
declare @i int
declare @n int
/*存货分类按编码级次展开*/
/*取得存货分类编码级次 */
set @chrbmjc   = ''
set @chrbmjc   = (selectcValuefrom 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 inventoryClassb'
    + ltrim(rtrim(str(@i)))
    + ' on a.cInvCCode = b'
    + ltrim(rtrim(str(@i)))+ '.cInvCCode '
end
set @i = @i +1
end
/*组织销售毛利分析明细账   */
ifexists(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 发表于 2018-4-11 18:36:56

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

yaoxiaoxiang 发表于 2018-7-3 17:33:23

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

y4633045 发表于 2019-4-25 23:39:24

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

偷媳妇 发表于 2020-10-31 07:27:01

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

偷媳妇 发表于 2020-12-16 15:12:39

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

查看完整版本: U8v821升级出错求救！！！