用友通10.1升级u852正常,升级861报错

ufidalijianfeng

60[Main]升级前，关闭了连接
860[Main]升级前，打开了连接
860[Main]升级
关闭了系统数据库连接
重新打开了系统数据库连接
开始读取升级列表
结束读取升级列表
开始事务
2013-03-24 03:45:51
数据库名: UFDATA_002_2012
采购视图, DPRO860_PU
错误信息:
-2147217900
列名 'iPBVQuantity' 不明确。
执行如下语句时出错:
CREATE   PROCEDURE PU_DelPurSettle
( @PurSID varchar(10),         --结算单主表ID
  @SMonth varchar(2),           --结算日期所在会计月
  @OutParam varchar(200) OUTPUT --返回检查结果
)
AS
DECLARE @bAccount bit
DECLARE @bIsPurAcc bit
DECLARE @SysVersion varchar(10) --当前版本（工业，商业）
DECLARE @QuanDecimal int --数量小数位数
DECLARE @CostDecimal int --单价小数位数
DECLARE @iEBV varchar(2048)
DECLARE @RdBackTempStr varchar(800) --回写入库单字符串
DECLARE @PurBVIDTemp varchar(10) --临时记忆受托代销要删除的发票主表ID
DECLARE @BVID varchar(20) --发票主表ID
DECLARE @BVCount int
DECLARE @BVSTCount int
DECLARE @BVsID varchar(20)
DECLARE @isRdAutoID INT
DECLARE @sBusType varchar(20) --产品名称
DECLARE @iErr TINYINT
DECLARE @bFirstDel BIT  --是否是首次删除结算单
Set NOCOUNT ON
----------------检查结算单合法性-----------------
SET @sBusType=(SELECT cBusType FROM PurSettleVouch WHERE [url=mailtoSVID=@PurSID]PSVID=@PurSID[/url])
SET @SysVersion=(SELECT ISNULL(cValue,cDefault) FROM AccInformation WHERE cName='cSysVersion')
SET @QuanDecimal=(SELECT ISNULL(cValue,cDefault) FROM AccInformation WHERE cName='iStrsQuanDecDgt')
SET @CostDecimal=(SELECT ISNULL(cValue,cDefault) FROM AccInformation WHERE cName='iStrsPriDecDgt')
IF @sBusType='普通采购' OR @sBusType='受托代销'
BEGIN
IF (SELECT bFlag_PU FROM GL_Mend WHERE iPeriod=@SMonth)=1
BEGIN
  SET @OutParam=@SMonth + '月已结帐，不能取消结算'
  RETURN
END
END
ELSE IF @sBusType='委外加工'
BEGIN
IF (SELECT bFlag_OM FROM GL_Mend WHERE iPeriod=@SMonth)=1
BEGIN
  SET @OutParam=@SMonth + '月已结帐，不能取消结算'
  RETURN
END  
END
DECLARE CurCheck CURSOR FOR SELECT bAccount,bIsPurAcc FROM PurSettleVouchs WHERE NOT(ABS(iSVQuantity)<0.00000001 AND ISNULL(cWhCode,'')='') AND [url=mailtoSVID=@PurSID]PSVID=@PurSID[/url]
OPEN CurCheck
FETCH NEXT FROM CurCheck INTO @bAccount,@bIsPurAcc
WHILE @@FETCH_STATUS=0
BEGIN
IF @bAccount<>@bIsPurAcc
BEGIN
  CLOSE CurCheck
  DEALLOCATE CurCheck
  SET @OutParam='此张结算单已经被存货核算系统处理，或者对应的入库单已经记帐，不能删除'
  RETURN
END
FETCH NEXT FROM CurCheck INTO @bAccount,@bIsPurAcc  
END
CLOSE CurCheck
DEALLOCATE CurCheck
IF (SELECT COUNT(*) FROM PurSettlevouchs WHERE (ISNULL(iBsID,0)=0 OR iBsID=0) AND [url=mailtoSVID=@PurSID)<]PSVID=@PurSID)<>(SELECT[/url] COUNT(*) FROM PurSettlevouchs WHERE PSVID=@PurSID)
BEGIN
IF(((SELECT cBusType FROM PurSettlevouch WHERE PSVID=@PurSID)='受托代销') AND @SysVersion='商业')
BEGIN
  IF (SELECT COUNT(*) FROM (PurBillVouch INNER JOIN PurBillVouchs ON PurBillVouch.PBVID=PurBillVouchs.PBVID)INNER JOIN PurSettleVouchs ON PurSettleVouchs.iBsID=PurBillVouchs.id WHERE  PSVID=@PurSID AND ((NOT(cClue IS NULL)) OR (iOriTotal<>0 OR iTotal<>0) OR (NOT(ISNULL(cPBVVerifier,'')=''))))>0
  BEGIN
   SET @OutParam='所结发票已做凭证或已付款或者受托结算发票已经审核'
   RETURN
  END
END
/*ELSE
BEGIN
  IF (SELECT COUNT(*) FROM (PurBillVouch INNER JOIN PurBillVouchs ON PurBillVouch.PBVID=PurBillVouchs.PBVID)INNER JOIN PurSettleVouchs.iBsID=PurBillVouchs.id WHERE  PSVID=@PurSID AND ((NOT(cClue IS NULL)) OR (iOriTotal<>0 OR iTotal<>0) AND bPayment=0))>0
  BEGIN
   SET @OutParam='所结发票已做凭证或已付款或者受托结算发票已经审核'
   RETURN
  END  
END */
END
IF (SELECT COUNT(*) FROM PurBillVouchs INNER JOIN PurSettleVouchs ON PurBillVouchs.ID=PurSettleVouchs.iBsID WHERE (ISNULL(iBsID,0)<>0 OR iBsID<>0) AND PurSettleVouchs.PSVID=@PurSID AND ISNULL(cCorInvCode,'')<>'' AND (bAccount=1))>0
BEGIN
SET @OutParam='所结费用发票已结帐'
RETURN
END
---------------------单据检查结束------------------------------
---------------------单据开始删除------------------------------
BEGIN TRANSACTION
IF @sBusType='普通采购' OR  @sBusType='委外加工' --如果是普通采购
BEGIN
-----------------开始处理发票------------------
IF @SysVersion='工业'       --如果是工业版
BEGIN
  UPDATE PurBillVouchs SET cCorInvCode=NULL,dInDate=NULL,iExMoney=0.00,iLostQuan=0,iNlostQuan=0,iNLostMoney=0.00,mNLostTax=0.00,cNLostType=NULL FROM PurSettleVouchs INNER JOIN PurBillVouchs ON PurSettleVouchs.iBsid=PurBillVouchs.id WHERE PurSettleVouchs.PSVID=@PurSID AND (PurSettleVouchs.iBsID<>0 OR ISNULL(PurSettleVouchs.iBsID,0)<>0) --工业版回写发票子表
END
ELSE         --如果是商业版
BEGIN
  UPDATE PurBillVouchs SET cCorInvCode=NULL,iExMoney=0.00,iLostQuan=0,iNlostQuan=0,iNLostMoney=0.00,mNLostTax=0.00,cNLostType=NULL FROM PurSettleVouchs INNER JOIN PurBillVouchs ON PurSettleVouchs.iBsid=PurBillVouchs.id WHERE PurSettleVouchs.PSVID=@PurSID AND (PurSettleVouchs.iBsID<>0 OR ISNULL(PurSettleVouchs.iBsID,0)<>0)   --商业版回写发票子表
END
DECLARE BVCur CURSOR FOR SELECT DISTINCT PurBillVouch.PBVID FROM (PurBillVouch INNER JOIN PurBillVouchs ON PurBillVouch.PBVID=PurBillVouchs.PBVID) INNER JOIN PurSettleVouchs ON PurBillVouchs.id=PurSettleVouchs.iBsID WHERE PurSettleVouchs.PSVID=@PurSID AND (PurSettleVouchs.iBsID<>0 OR ISNULL(PurSettleVouchs.iBsID,0)<>0)
OPEN BVCur
FETCH NEXT FROM BVCur INTO @BVID
While @@FETCH_STATUS=0
BEGIN
  SET @BVCount=(SELECT COUNT(*) FROM PurBillVouchs WHERE ISNULL(PurBillVouchs.dSDate,'')<>'' AND PBVID=@BVID)
  SET @BVSTCount=0
  DECLARE BVsCur CURSOR FOR SELECT DISTINCT PurBillVouchs.ID FROM (PurBillVouch INNER JOIN PurBillVouchs ON PurBillVouch.PBVID=PurBillVouchs.PBVID) INNER JOIN PurSettleVouchs ON PurBillVouchs.id=PurSettleVouchs.iBsID WHERE PurSettleVouchs.PSVID=@PurSID AND (PurSettleVouchs.iBsID<>0 OR ISNULL(PurSettleVouchs.iBsID,0)<>0) AND PurBillVouch.PBVID=@BVID
  OPEN BVsCur
  FETCH NEXT FROM BVsCur INTO @BVsID
  While @@FETCH_STATUS=0
  BEGIN
   SET @BVSTCount=@BVSTCount+1
   FETCH NEXT FROM BVsCur INTO @BVsID
  END
  CLOSE BVsCur
  DEALLOCATE BVsCur
  IF @BVCount=@BVSTCount
   UPDATE PurBillVouch SET dSDate=NULL WHERE PBVID=@BVID
  FETCH NEXT FROM BVCur INTO @BVID
END
CLOSE BVCur
DEALLOCATE BVCur
UPDATE PurBillVouchs SET dSDate=NULL FROM PurBillVouchs INNER JOIN PurSettleVouchs ON PurSettleVouchs.iBsID=PurBillVouchs.id WHERE PurSettleVouchs.PSVID=@PurSID AND (PurSettleVouchs.iBsID<>0 OR ISNULL(PurSettleVouchs.iBsID,0)<>0) --回写发票主表
----------------处理完毕-----------------------
----------------开始处理入库单-----------------
IF @sBusType='委外加工'
BEGIN
     SET @RdBackTempStr='UPDATE RdRecords SET RdRecords.iProcessFee = CONVERT(DECIMAL(20,2),RdRecords.iSProcessFee),RdRecords.iProcessCost = CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@CostDecimal) +'),CONVERT(DECIMAL(20,2),RdRecords.iSProcessFee) / RdRecords.iQuantity)'
        +' FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID'
                               +' AND CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),RdRecords.iSQuantity)=CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),RdRecords.iQuantity)'
                               +' INNER JOIN PurBillVouchs ON PurSettleVouchs.ibsid=PurBillVouchs.ID AND PurBillVouchs.bExbill=0 '
                        +' WHERE PurSettleVouchs.PSVID='+@PurSID
     EXEC(@RdBackTempStr)
--      SET @RdBackTempStr='UPDATE RdRecords SET RdRecords.iProcessCost = CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@CostDecimal) +'),RdRecords.iProcessFee / RdRecords.iQuantity)'
--                  +' FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID'
--                             +' AND CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),RdRecords.iSQuantity)=CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),RdRecords.iQuantity)'
--               +' WHERE PurSettleVouchs.PSVID='+@PurSID
--             EXEC(@RdBackTempStr)
     SET @RdBackTempStr='UPDATE RdRecords SET iSProcessFee=(CASE WHEN CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),RdRecords.iSQuantity)=CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),PurSettleVouchs.iSVQuantity) THEN 0 ELSE CONVERT(DECIMAL(20,2),RdRecords.iSProcessFee-RdRecords.iProcessCost*PurSettleVouchs.iSVQuantity)END)'
                        +' FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID'
                           +' INNER JOIN PurBillVouchs ON PurSettleVouchs.ibsid=PurBillVouchs.ID AND PurBillVouchs.bExbill=0'
                           +' WHERE PurSettleVouchs.PSVID='+@PurSID
     EXEC(@RdBackTempStr)
     SET @RdBackTempStr='UPDATE RdRecords SET iSMaterialFee=(CASE WHEN CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),RdRecords.iSQuantity)=CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),PurSettleVouchs.iSVQuantity) THEN 0 ELSE CONVERT(DECIMAL(20,2),RdRecords.iSMaterialFee-PurSettleVouchs.MaterialFee)END)'
                        +' FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID'
                        +' INNER JOIN PurBillVouchs ON PurSettleVouchs.ibsid=PurBillVouchs.ID AND PurBillVouchs.bExbill=0'
                        +' WHERE PurSettleVouchs.PSVID='+@PurSID
     EXEC(@RdBackTempStr)
END
----应该减去结算单的暂估结算金额，而不是结算单的结算金额 2003-09-25
SET @RdBackTempStr='UPDATE RdRecords SET Rdrecords.iSQuantity=CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),ISNULL(Rdrecords.iSquantity,0)-ISNULL(T1.iSVQuantity,0)),RdRecords.iMoney=(CASE WHEN CONVERT(DECIMAL(20,'+ CONVERT(VARCHAR(1),@QuanDecimal) +'),ISNULL(RdRecords.iSQuantity,0)-ISNULL(T1.iSVQuantity,0))=0 THEN 0 ELSE CONVERT(DECIMAL(20,2),ISNULL(Rdrecords.iMoney,0)-ISNULL(T1.iSVAPrice,0)) END) FROM (SELECT iRdsID,PSVID,SUM(ISNULL(PurSettleVouchs.iSVQuantity,0)) AS iSVQuantity,SUM(ISNULL(PurSettleVouchs.iSVAPrice,0)) AS iSVAPrice FROM PurSettleVouchs WHERE PurSettleVouchs.PSVID='+ @PurSID +' GROUP BY iRdsID,PSVID) AS T1 INNER JOIN RdRecords ON RdRecords.AutoID=T1.iRdsID WHERE T1.PSVID='+ @PurSID
UPDATE RdRecord SET RdRecord.ID=RdRecord.ID FROM (RdRecord INNER JOIN Rdrecords ON RdRecord.ID=Rdrecords.ID) INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID  WHERE ABS(ISNULL(PurSettleVouchs.iSVQuantity,0))>0.0000000001 AND PurSettleVouchs.PSVID=@PurSID
     --UPDATE RdRecords SET RdRecords.iSQuantity=CONVERT(DECIMAL( iSVPrice,iSVQuantity,iTax FROM (PurSettleVouchs INNER JOIN PurSettleVouch ON PurSettleVouch.PSVID=PurSettleVouchs.PSVID) INNER JOIN RdRecords ON RdRecords.AutoID=PurSettleVouchs.iRdsID  WHERE PurSettleVouch.PBVID=@PurSID
EXEC(@RdBackTempStr)

UPDATE RdRecords SET dSDate=(CASE WHEN ABS(iSQuantity)<0.0000000001 THEN NULL ELSE dSDate END),iPrice=iAPrice,iUnitCost=fACost FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID WHERE PurSettleVouchs.PSVID=@PurSID AND (ISNULL(iRdsID,0)<>0 OR iRdsID<>0)
SET @isRdAutoID=CONVERT(INT,(SELECT TOP 1 AutoID FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID WHERE PurSettleVouchs.PSVID=@PurSID AND (ISNULL(iRdsID,0)<>0 OR iRdsID<>0)))
        EXEC Pu_WBRkdCostPrice @isRdAutoID,@iErr
----------------处理完毕----------------------
--------------错误处理-------------
IF (@@Error<>0 OR @iErr=-1)
BEGIN
  SET @OutParam='回写入库单和发票失败！'
  ROLLBACK TRANSACTION
  RETURN
END
END
ELSE IF @sBusType='受托代销'        --如果是受托代销
BEGIN
/*IF EXISTS(SELECT Name FROM Tempdb..sysobjects WHERE Name='PUTEMP_BVID_DELPS' AND xType='u')
BEGIN
  DROP TABLE Tempdb..PUTEMP_BVID_DELPS
END
INSERT INTO Tempdb..PUTEMP_BVID_DELPS SELECT DISTINCT PurBillVouchs.PBVID FROM PurBillVouchs INNER JOIN PurSettleVouchs ON  WHERE */
------------------受托代销结算单删除中的发票删除--------------------
SET @PurBVIDTemp=(SELECT TOP 1 PBVID FROM PurBillVouchs INNER JOIN PurSettleVouchs ON PurBillVouchs.id=PurSettleVouchs.iBsID WHERE PurSettleVouchs.PSVID=@PurSID)
----如果发票关联订单要删除订单累计发票合计以及数量等回写字段的数据-----------
        UPDATE Po_PoDetails SET Po_Podetails.iInvQTY=CONVERT(DECIMAL(20,7),ISNULL(Po_Podetails.iInvQTY,0))-CONVERT(DECIMAL(20,7),T1.iPBVQuantity),Po_Podetails.iInvNum=CONVERT(DECIMAL(20,7),ISNULL(Po_Podetails.iInvNum,0))-CONVERT(DECIMAL(20,7),T1.iNum),
              Po_Podetails.iInvMoney=ISNULL(Po_Podetails.iInvMoney,0)-T1.iOriSum,Po_Podetails.iNatInvMoney=ISNULL(Po_Podetails.iNatInvMoney,0)-T1.iSum
              FROM Po_Podetails INNER JOIN (SELECT RdRecords.iPosID,SUM(iPBVQuantity) AS iPBVQuantity,SUM(ISNULL(PurBillVouchs.iNum,0)) AS iNum,SUM(ISNULL(PurBillVouchs.iorisum,0)) AS iorisum,SUM(ISNULL(PurBillVouchs.isum,0)) AS isum FROM
              PurBillVouchs INNER JOIN RdRecords ON PurBillVouchs.RdsID=RdRecords.Autoid WHERE PurBillVouchs.PBVID=@PurBVIDTemp GROUP BY RdRecords.iPosID) AS T1
              ON Po_PoDetails.ID=T1.iPosID
DELETE FROM PurBillVouchs FROM PurBillVouchs INNER JOIN PurSettleVouchs ON PurBillVouchs.id=PurSettleVouchs.iBsID WHERE PurSettleVouchs.PSVID=@PurSID
DELETE FROM PurBillVouch WHERE PurBillVouch.PBVID =@PurBVIDTemp
------------------回写入库单-----------------

UPDATE RdRecords SET RdRecords.iSQuantity=ISNULL(RdRecords.iSQuantity,0)-ISNULL(PurSettleVouchs.iSVQuantity,0),RdRecords.iMoney=CASE WHEN ISNULL(RdRecords.iSQuantity,0)-ISNULL(PurSettleVouchs.iSVQuantity,0)=0 THEN 0 ELSE ISNULL(RdRecords.iMoney,0)-ISNULL(PurSettleVouchs.iSVAPrice,0) END,RdRecords.iSNum=ISNULL(RdRecords.iSNum,0)-ISNULL(PurSettleVouchs.iSVNum,0)
  FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.autoid=PurSettleVouchs.iRdsID WHERE PurSettleVouchs.PSVID=@PurSID AND (ISNULL(iRdsID,0)<>0 OR iRdsID<>0)
UPDATE RdRecords SET dSDate=(CASE WHEN ABS(iSQuantity)<0.0000000001 THEN NULL ELSE dSDate END)
  FROM RdRecords INNER JOIN PurSettleVouchs ON RdRecords.autoid=PurSettleVouchs.iRdsID WHERE PurSettleVouchs.PSVID=@PurSID AND (ISNULL(iRdsID,0)<>0 OR iRdsID<>0)
UPDATE RdRecord SET RdRecord.ID=RdRecord.ID FROM (RdRecord INNER JOIN Rdrecords ON RdRecord.ID=Rdrecords.ID) INNER JOIN PurSettleVouchs ON RdRecords.AutoID=PurSettleVouchs.iRdsID  WHERE PurSettleVouchs.PSVID=@PurSID
--------------错误处理-------------
IF @@Error<>0
BEGIN
  SET @OutParam='回写入库单和发票失败！'
  ROLLBACK TRANSACTION
  RETURN
END
END
---------------------单据删除完成------------------------------
DELETE FROM PurSettleVouchs WHERE PSVID=@PurSID
DELETE FROM PurSettleVouch WHERE PSVID=@PurSID
IF @@Error<>0
BEGIN
SET @OutParam='删除结算单失败！'
ROLLBACK TRANSACTION
RETURN
END
SET @OutParam='成功'
COMMIT TRANSACTION
Set NOCOUNT OFF
RETURN