|
|
悬赏30金币已解决
数据库名: 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 的类型去加
|
|站长微信|Archiver|手机版|小黑屋|用友之家
( 蜀ICP备07505338号|
51072502110008 )
IP卡
狗仔卡
发表于 2012-5-25 16:03:13
提升卡
置顶卡
变色卡
千斤顶
显身卡
