U810.0升级到U810.1报错...请大使指点...

鱼小生

数据库名: UFDATA_005_2011
D:\U8SOFT\Admin\SQLFILE10100\Main\Ufdata\Structure\Data_PRV_AP_mix_NL.sql
错误信息:
-2147217900
列名 'iSrcApplyAmt_f' 无效。
执行如下语句时出错:

create VIEW [AP_V_ContractApply]
AS
Select convert(char,convert(money,C.tsTime),2) as ufts,dbo.UDF_GetResourceMessageByID('U8.CW.CM.Contract')As cVoucherType, dbo.UDF_GetResourceMessageByID('U8.CW.CM.Effect') As cContractState,  
  C.strContractGrp,C.strContractType,C.strContractKind,C.strContractName,C.strBisectionUnit,C.strParentID,C.strRepair,C.strBisectionPerson,C.strContractOrderDate,C.strContractStartDate,C.strContractEndDate,C.strContractDesc,C.dblMassassureScale,C.dblMassassure,
  C.cDefine1,C.cDefine2,C.cDefine3,C.cDefine4,C.cDefine5,C.cDefine6,C.cDefine7,C.cDefine8,C.cDefine9,C.cDefine10,C.cDefine11,C.cDefine12,C.cDefine13,C.cDefine14,C.cDefine15,C.cDefine16,
  C.strSetupPerson,C.strSetupDate,C.strEndCasePerson,C.strEndCaseDate,C.strInurePerson,C.strInureDate,C.strDeptID,C.strPersonID,C.intVaryID,C.strVaryCauseID,C.dtVaryDate,C.strVaryPersonID,C.strVaryPassPersonID,C.intPre,C.strWay,C.strCurrency,C.dblExchange
,C.strVaryPerson,  
  C.strSource,C.dblTotalCurrency,C.dblExecCurrency,C.dblTotalQuantity,C.dblExecQuqantity,C.cBusType,C.cSCCode,C.cGatheringPlan,C.IsWfControlled,C.IverifyState,C.IreturnCount,C.intAuditSymbol,C.cZbjComputeMode,  
  T.cTypeName,G.cGroupName, D.cDepName,P.cPersonName,   
--  (case Upper(C.strSource) when N'C' then CT.cCusName when N'V' then VD.cVenName end ) as strBisectionUnitName,
  VD.cVenName as strBisectionUnitName,M.intDetail,M.intKL,M.strSource As strXMSource,T.cControlType,T.decPermitError,      
  dbo.UDF_GetResourceMessageByID('U8.CW.CM.Effect') As strContractState,Right(I.strXMDL,1) As strFlag,
  I.GUID,I.ID,I.intFlag,I.strCode,I.strName,I.dblQuantity,I.strMeasureUnit,I.dblTaxRatio,I.dblDiscountRatio,I.dblUntaxPrice,I.dblUntaxPriceRMB,I.dblPrice,I.dblPriceRMB,I.dblUntaxSum,I.dblUntaxSumRMB,I.dblSum,I.dblSumRMB,I.dblUntaxExecSum,I.dblUntaxExecSumRMB,I.dblExecSum,I.dblExecSumRMB,I.dtStartDate,I.dtEndDate,I.strChief,
I.strCorrSource,
I.strCorrItemID,
I.strInvoiceID,
--I.strCorrItemID as cItemCode,
--I.cItemName as cItemName,
--I.strInvoiceID as cInvCode,
Inventory.cInvName as cInvName,
I.strMemo,I.cDefine22,I.cDefine23,I.cDefine24,I.cDefine25,I.cDefine26,I.cDefine27,I.cDefine28,I.cDefine29,I.cDefine30,I.cDefine31,I.cDefine32,I.cDefine33,I.cDefine34,I.cDefine35,I.cDefine36,I.cDefine37,I.dblExecQuantity,I.strXMDL,I.strContractID,I.intEnd,I.RowGUID,I.strSpare1,I.strSpare2,I.strSpare3,I.cFree1,I.cFree2,I.cFree3,I.cFree4,I.cFree5,I.cFree6,I.cFree7,I.cFree8,I.cFree9,I.cFree10,I.cInvDefine1,I.cInvDefine2,I.cInvDefine3,I.cInvDefine4,I.cInvDefine5,I.cInvDefine6,I.cInvDefine7,I.cInvDefine8,I.cInvDefine9,I.cInvDefine10,I.cInvDefine11,I.cInvDefine12,I.cInvDefine13,I.cInvDefine14,I.cInvDefine15,I.cInvDefine16,I.cInvStd,I.cCusCode,I.cVenCode,I.AuxMeasureUnit,I.ConversionRate,I.PieceNum,I.ExecPieceNum,I.iInvRCost,I.cInvAddCode,I.decZbjRatio,I.decNoRateZbjMoney,I.decNoRateZbjBenBiMoney,I.decZbjMoney,I.decZbjBenbiMoney,I.dtZbjStartDate,I.dtZbjEndDate,I.iAppIds,I.cAppCode,
--
--   iBillAmt_f,(ISNULL(dblSum,0)-ISNULL(iBillAmt_f,0)) as iApplySum_f,  
--   iBillAmt,(ISNULL(dblSumRMB,0)-ISNULL(iBillAmt,0)) as iApplySum  
  iBillAmt_f,  
-- iExecAmt_f,iBalAmt_f,
  (ISNULL(dblSum,0)-ISNULL(iBillAmt_f,0)-ISNULL(iExecAmt_f,0)-ISNULL(iBalAmt_f,0)) as iApplySum_f,  
  iBillAmt,  
-- iExecAmt,iBalAmt,
  (ISNULL(dblSumRMB,0)-ISNULL(iBillAmt,0)-ISNULL(iExecAmt,0)-ISNULL(iBalAmt,0)) as iApplySum  
from CM_Contract_B C      
  INNER JOIN CM_Contract_Item_B I ON C.strContractID=I.strContractID  
  LEFT JOIN dbo.CM_Type T ON C.strContractType = T.cTypeCode      
  LEFT JOIN dbo.CM_Group G ON C.strContractGrp = G.cGroupID   
  LEFT JOIN Inventory ON I.strInvoiceID = Inventory.cInvCode   
  LEFT JOIN dbo.Department D ON C.strDeptID = D.cDepCode      
  LEFT JOIN dbo.Person P ON C.strPersonID = P.cPersonCode      
--  LEFT JOIN dbo.Customer CT ON C.strBisectionUnit=CT.cCusCode      
  LEFT JOIN dbo.Vendor VD ON C.strBisectionUnit=VD.cVenCode      
  LEFT JOIN dbo.CM_Contract_Main M On C.strContractID=M.strContractID
  LEFT JOIN (  
    SELECT BalancesGuid,sum(iBillAmt_f) as iBillAmt_f,sum(iBillAmt) as iBillAmt   
    FROM AP_V_ApplyPayCal WHERE cSource=N'合同' GROUP BY BalancesGuid  
  ) AS AppPU ON I.RowGUID=AppPU.BalancesGuid  
  -- New：合同执行单已申请金额  
  LEFT JOIN (  
select M.cContractID, Ms.cContractObjectID as cCode, CMExec_Apply.iExecAmt_f,CMExec_Apply.iExecAmt
  from
   (SELECT BalancesGuid,sum(iBillAmt_f) as iExecAmt_f,sum(iBillAmt) as iExecAmt   
       FROM AP_V_ApplyPayCal WHERE cSource=N'合同执行单' GROUP BY BalancesGuid
   ) CMExec_Apply
    INNER JOIN dbo.CM_ExecuteBills Ms on Ms.cRowID = CMExec_Apply.BalancesGuid
    INNER JOIN dbo.CM_ExecuteBill M ON M.cExecID = Ms.cExecID
  ) AS CMExec ON CMExec.cContractID=C.strContractID AND CMExec.cCode = I.strCode
  -- New：合同结算单已申请金额  
  LEFT JOIN (
select Bs.Guid, Bs.cCode, CMBal_Apply.iBillAmt AS iBalAmt, CMBal_Apply.iBillAmt_f AS iBalAmt_f
  FROM
   (SELECT BalancesGuid,sum(iSrcApplyAmt_f) as iBillAmt_f, sum(iSrcApplyAmt) as iBillAmt   
         FROM AP_V_ApplyPayCal WHERE cSource=N'合同结算单' GROUP BY BalancesGuid
   ) AS CMBal_Apply
  INNER JOIN dbo.CM_Balances Bs on Bs.BalancesGuid = CMBal_Apply.BalancesGuid
) CMBal ON CMBal.Guid  = I.Guid AND CMBal.cCode = I.strCode
Where (strContractKind=N'应付类合同' or strContractKind=N'采购类合同' or strContractKind=N'进口类合同') --and ISNULL(decExecCount,0)=0 and ISNULL(decCount,0)=0
and C.strContractID not in (select distinct strContractID from CM_Contract_C where istatus=0)
-- 以标的为单位计算可申请金额；注释：
-- --应付类合同未生成结算单
-- and not exists (select 1 from CM_Balances BS inner join CM_Balance B on BS.cBalanceID=B.cBalanceID
--   where C.strContractID=B.cContractID and I.strCode=BS.cCode)
-- --合同未生成合同执行单
-- and not exists (select 1 from CM_ExecuteBills ES inner join CM_ExecuteBill E on ES.cExecID=E.cExecID
--   where C.strContractID=E.cContractID and I.strCode=ES.cContractObjectID)
--合同生成的采购订单未申请付款
and not exists (select 1 from AP_ApplyPayVouchs INNER JOIN PO_Podetails ON AP_ApplyPayVouchs.iBVid=PO_Podetails.ID
where PO_Podetails.ContractRowGUID=I.RowGUID and AP_ApplyPayVouchs.cSource=N'采购订单')
--合同生成的进口订单未申请付款
and not exists (select 1 from AP_ApplyPayVouchs INNER JOIN IM_OrderDetail ON AP_ApplyPayVouchs.iBVid=IM_OrderDetail.autoid
where IM_OrderDetail.ccontractrowguid=I.RowGUID and AP_ApplyPayVouchs.cSource=N'进口订单')
--合同未开票(PU)
and not exists (select 1 from PurBillVouchs where PurBillVouchs.ContractRowGUID is not null and PurBillVouchs.ContractRowGUID=I.RowGUID)
--合同未开票(IM)
and not exists (select 1 from IM_InvoiceDetail where IM_InvoiceDetail.ccontractrowguid is not null and IM_InvoiceDetail.ccontractrowguid=I.RowGUID)
--已生效未结案
and c.istatus=1

莫扎兔

上面的代码中

AP_V_ApplyPayCal  这个表没有 iSrcApplyAmt_f 这个字段 所以报了你那个错误

你可以试试给这个表 加这个字段  按iSrcApplyAmt 的类型去加

鱼小生

我自己的贴子...一楼给论坛嘛...三十金就没有啦...郁闷先...

其次...

我的天啊...用友...我想杀人哈...
U810.0  升级到U810.1屁大点东西居然要报错...我郁闷啦...我严重的郁闷啦...其他版本升级上来都一直报...我忍啦...MMD    居然10.0升级也报...郁闷...晕...点升级...不到一分钟就报错...




:@:@:@:@:@:@:@:@:@:@:@:@:@


求各位大侠给点经验...给点经书....谢谢......

鱼小生

莫扎兔 发表于 2012-5-25 18:02

                               
登录/注册后可看大图

上面的代码中

AP_V_ApplyPayCal  这个表没有 iSrcApplyAmt_f 这个字段 所以报了你那个错误

谢谢...但是ap_v_applypaycal 这个表在哪里????

找不到的嘛...

austinjian

最好打完所有补丁后，特别是数据库补丁 再来升级 祝你好运

5kb

AP_V_ContractApply在视图里找，你看下？

sxl1807

你可以是先试下，把升级里的视图表先重命名，然后建个演示帐套去里面复制过来。在升级试试看。一般都可以的。
如果不行就把视图的语句复制过来，到查询分析器里执行啊，看是什么表缺什么字段。对照演示帐套的字段及属性添加。

莫扎兔

鱼小生 发表于 2012-5-28 12:14

                               
登录/注册后可看大图

谢谢...但是ap_v_applypaycal 这个表在哪里????

找不到的嘛...

AP_V_ApplyPayCal   这个在视图中找下  把iSrcApplyAmt_f  补上


试图里面都是些检索语句  你让这个视图最后能 返回 iSrcApplyAmt_f   就好

鱼小生

莫扎兔 发表于 2012-5-29 09:56

                               
登录/注册后可看大图

AP_V_ApplyPayCal   这个在视图中找下  把iSrcApplyAmt_f  补上

好嘛...谢谢...