旗舰版5.0销售出库单下推发票报错
754次浏览
编辑于2017年08月04日 09:44:31
新装旗舰版5.0,但是在销售出库单下推发票,点生成的时候提示
数据读取错误。列名‘fsourceentryid’无效
经过排查,是一段SQL语句有问题
SELECT v1.FSupplyID AS FAddress,t1.FAddress AS FAddress1,t1.FAddress AS FAddress2,u2.FAmount*(CONVERT (DECIMAL(23,10),(CASE WHEN tnew1.FValue <> '1' THEN u2.FTaxRate ELSE 0 END ))/100) AS FAmtDiscount,
u2.FTaxPrice*t7.FCoefficient AS FAuxOrderPrice,(CASE WHEN u1.FSourceTranType=1014301 THEN
(CASE WHEN tnew5.FValue <> '1' THEN
case when tnew1.FValue='1' and tnew6.FValue='0' then
case t15.FOperator when '*' then u1.FConsignPrice/t15.FExchangeRate
else u1.FConsignPrice*t15.FExchangeRate end
else
isnull(weborderentry.FPrice,0)
end
ELSE u1.FConsignPrice END)
ELSE
(CASE WHEN tnew1.FValue <> '1' THEN
(CASE WHEN (IsNull(u2.FEntryID,0)>0 AND t17.FValue<>'1') THEN IsNull(u2.FAuxPrice,0)
WHEN IsNull(u2.FEntryID,0)=0 THEN u1.FConsignPrice ELSE 0 END)
ELSE u1.FConsignPrice END)
End) AS FAuxPrice,t4.FUnitGroupID as FItemUnitGroupID,sign(t4.FAuxClassID)-sign(u1.fauxpropid) AS FAuxPropCls,
u1.FAuxPropID,t105.FName AS FAuxPropName,t105.FNumber AS FAuxPropNum,Case When (u1.FAuxQty-u1.FAuxQtyInvoice)*sign(v1.FROB)>0 Then (u1.FAuxQty-u1.FAuxQtyInvoice) Else 0 End AS FAuxQty,
(case when u1.FSourceTranType=1014301 then
(CASE WHEN tnew5.FValue <> '1' THEN
case when tnew1.FValue='1' and tnew6.FValue='0' then
case t15.FOperator when '*' then (CASE WHEN T17.FVALUE=1 THEN u1.FConsignPrice ELSE 0 END) /t15.FExchangeRate
else (CASE WHEN T17.FVALUE=1 THEN u1.FConsignPrice ELSE 0 END)*t15.FExchangeRate end
else
CAST(isnull(weborderentry.FPrice,0) AS FLOAT)*CAST(t7.FCoefficient AS FLOAT)
end
else (CASE WHEN T17.FVALUE=1 THEN u1.FConsignPrice ELSE 0 END) end)
else
(CASE WHEN tnew1.FValue <> '1' THEN
(CASE WHEN IsNull(u2.FEntryID,0)>0 THEN CAST(u2.FTaxPrice AS FLOAT)*CAST(t7.FCoefficient AS FLOAT)
WHEN t17.FValue='1' THEN u1.FConsignPrice ELSE 0 END)
else (CASE WHEN T17.FVALUE=1 THEN u1.FConsignPrice ELSE 0 END) end )
end) AS FAuxTaxPrice,v1.FSupplyID AS FBank,t1.FBank AS FBank1,t1.FBank AS FBank2,
t10.FName AS FBaseUnitName,u1.FBatchNo,v1.FBillNO,v1.FBrID,tson.FName AS FBrName,
tson.FNumber AS FBrNumber, (case when u1.FSourceTranType=1014301 then
(CASE WHEN tnew5.FValue <> '1' THEN weborder.FTotalFreight
else u1.FBuyerFreight end)
else
u1.FBuyerFreight
end) AS FBuyerFreight,t7.FCoefficient,u1.FComplexQty,u1.FContractBillNo,u1.FContractEntryID,
u1.FContractInterID,(case when u1.FSourceTranType=1014301 then
(CASE WHEN tnew5.FValue <> '1' THEN isnull(weborder.FCurrencyID,1)
else 1 end)
else
(CASE WHEN tnew1.FValue <> '1' THEN (case when u1.forderinterid>0 then v2.FCurrencyID else 1 end)
else 1 end )
end) AS FCurrencyID,(case when u1.FSourceTranType=1014301 then
(CASE WHEN tnew5.FValue <> '1' THEN t15.FName
else (select fname from t_Currency where fcurrencyid=1) end)
else
(CASE WHEN tnew1.FValue <> '1' THEN (case when u1.forderinterid>0 then t14.fname
else (select fname from t_Currency where fcurrencyid=1) end)
else (select fname from t_Currency where fcurrencyid=1) end )
end) AS FCurrencyName,(case when u1.FSourceTranType=1014301 then
(CASE WHEN tnew5.FValue <> '1' THEN t15.FNumber
else (select fnumber from t_Currency where fcurrencyid=1) end)
else
(CASE WHEN tnew1.FValue <> '1' THEN (case when u1.forderinterid>0 then t14.fname
else (select fnumber from t_Currency where fcurrencyid=1) end)
else (select fnumber from t_Currency where fcurrencyid=1) end )
end) AS FCurrencyNumber,v1.FSupplyID AS FCustID,t1.FName AS FCustName,t1.FNumber AS FCustNumber,
v1.FDeptID,t2.FName AS FDeptIDName,t2.FNumber AS FDeptIDNumber,(CASE WHEN tnew1.FValue <> '1' AND ISNULL(u1.FOrderInterID,0) >0 THEN (CASE WHEN u2.FTaxRate <> 0 THEN u2.FTaxRate WHEN u2.FAuxTaxPrice * u2.FUniDiscount > 0 THEN (CASE WHEN tnew4.FValue = '1' THEN CONVERT(DECIMAL(28, 16), u2.FUniDiscount * 100 / u2.FAuxTaxPrice) ELSE CONVERT(DECIMAL(28, 16), u2.FUniDiscount * 100 / u2.FAuxPrice) END) ELSE 0 END) ELSE (1 - (1 - u1.FDiscountRate / 100) * (1 - v1.FHolisticDiscountRate / 100)) * 100 END) AS FDiscountRate,
v1.FEmpID,t3Emp.FName AS FEmpIDName,t3Emp.FNumber AS FEmpIDNumber,u1.FEntryID,(case when u1.FSourceTranType=1014301 then
(CASE WHEN tnew5.FValue <> '1' THEN t15.FExchangerate
else 1 end)
else
(CASE WHEN tnew1.FValue <> '1' THEN (case when u1.forderinterid>0 then v2.fexchangerate else 1 end)
else 1 end )
end) AS FExchangeRate,v1.FExplanation,v1.FInterID,u1.FItemID,u1.FKFDate,u1.FKFPeriod,
V1.FManagerID,t15_1.FName AS FManagerIDName,t15_1.FNumber AS FManagerIDNumber,u1.FMapName,
u1.FMapNumber,t4.FName AS FMatName,t4.FNumber as FMatNumber ,t4.Fmodel,u1.FMTONo,
u1.FNote,u1.FOLOrderBillNo,u1.FOrderBillNo,u1.FOrderEntryID,u1.FOrderInterID,u2.FTaxPrice AS FOrderPrice,
u1.FPlanMode,tPlanMode.FName AS FPlanModeName,tPlanMode.FID AS FPlanModeNumber,
t4.FPriceDecimal,CASE WHEN (u1.FQty-u1.FQtyInvoice)*sign(v1.FROB)>0 THEN (u1.FQty-u1.FQtyInvoice) ELSE 0 END AS FQty,
t4.FQtyDecimal,v1.FSaleStyle,t18.FName AS FSaleStyleName,t18.FID AS FSaleStyleNumber,
(CASE WHEN ABS(u1.FSecQty)>ABS(u1.FSecInvoiceQty) THEN (u1.FQty-u1.FQtyInvoice)/(u1.FSecQty-u1.FSecInvoiceQty) ELSE 0 END) AS FSecCoefficient,
(CASE WHEN ABS(u1.FSecQty)>ABS(u1.FSecInvoiceQty) THEN u1.FSecQty-u1.FSecInvoiceQty ELSE 0 END) AS FSecQty,
t501.FName AS FSecUnitName,v1.FTranType AS FSelTranTypeID,(SELECT FName FROM v_ICTransType WHERE FID=v1.FTranType) AS FSelTranTypeName,
(SELECT FName FROM v_ICTransType WHERE FID=v1.FTranType) AS FSelTranTypeNumber,
v1.FSettleDate,tSettle.FItemID AS FSettleID,tSettle.FName AS FSettleName,v1.FBillNo AS FSourceBillNo,
v1.FInterId AS FSourceInterID,v1.FTranType AS FSourceTranType,v1.FSupplyID AS FTaxNum,
t1.FTaxNum AS FTaxNum1,t1.FTaxNum AS FTaxNum2, (CASE WHEN tnew1.FValue <> '1' THEN (case when u2.FCess>0 then u2.FCess when t13.FValue='1' then t4.FTaxRate else t1.FValueAddRate end) else (case when t13.FValue='1' then t4.FTaxRate else t1.FValueAddRate end) end ) AS FTaxRate,
t4.FTrack,u1.FUniDiscount,u1.FUnitID,t7.FName AS FUnitName,t7.FNumber AS FUnitNumber,
t1.FValueAddRate
INTO #TmpSelBills
FROM ICStockBill v1 WITH (READPAST)
INNER JOIN ICStockBillEntry u1 WITH (READPAST) ON v1.FInterID=u1.FInterID
LEFT OUTER JOIN t_Organization t1 ON v1.FSupplyID=t1.FItemID
LEFT OUTER JOIN t_SubMessage t18 ON v1.FSaleStyle=t18.FInterID
LEFT JOIN t_Emp t15_1 ON v1.FManagerID=t15_1.FItemID
LEFT OUTER JOIN t_SystemProfile t13 ON t13.FCategory='IC' AND t13.FKey='SaleTaxRateOption'
LEFT OUTER JOIN t_SystemProfile t17 ON t17.FCategory='IC' AND t17.FKey='SEOrderTaxInPrice'
LEFT JOIN t_Department t2 ON v1.FDeptID = t2.FItemID
LEFT JOIN t_Emp t3emp ON v1.FEmpID = t3emp.FItemID
LEFT JOIN t_SonCompany tson ON v1.FBrID=tson.FItemID
LEFT OUTER JOIN t_SystemProfile tnew1 ON tnew1.FCategory='IC' AND tnew1.FKey='SEOrderInvoicePrice'
LEFT OUTER JOIN t_SystemProfile tnew4 ON tnew4.FCategory='IC' AND tnew4.FKey='DisCountIncludeTax'
LEFT JOIN t_SystemProfile tnew5 ON tnew5.FCategory='EC' AND tnew5.FKey='PushInvoiceCurrency'
LEFT JOIN t_SystemProfile tnew6 ON tnew6.FCategory='IC' AND tnew6.FKey='SaleToInvoiceCtlType'
INNER JOIN t_ICItem t4 ON u1.FItemID=t4.FItemID
LEFT OUTER JOIN t_MeasureUnit t7 ON u1.FUnitID=t7.FItemID
LEFT JOIN t_AuxItem t105 ON u1.FAuxPropID=t105.FItemID
LEFT OUTER JOIN SEOrderEntry u2 WITH (READPAST) ON u1.FOrderInterID=u2.FInterID AND u1.FOrderEntryID=u2.FEntryID
LEFT OUTER JOIN t_SubMessage tplanmode ON u1.FPlanMode=tplanmode.FInterID
LEFT JOIN IC_Web2ERPOrders weborder ON u1.FSourceInterId=weborder.FID
LEFT JOIN IC_Web2ERPOrdersEntry weborderentry ON u1.FSourceInterId=weborderentry.FID AND u1.FSourceEntryID=weborderentry.FEntryID
LEFT OUTER JOIN t_MeasureUnit t10 ON t4.FUnitID=t10.FItemID
LEFT OUTER JOIN t_MeasureUnit t501 ON t4.FSecUnitID=t501.FItemID
LEFT OUTER JOIN SEOrder v2 WITH (READPAST) ON u2.FInterID=v2.FInterID
LEFT JOIN t_Currency t15 ON weborder.FCurrencyID=t15.FCurrencyID
LEFT OUTER JOIN t_Currency t14 ON v2.FCurrencyID=t14.FCurrencyID
LEFT JOIN t_Settle tsettle ON v2.FSettleID=tsettle.FItemID
WHERE ((v1.FInterID=1838 AND u1.FEntryID=1))
ORDER BY v1.FInterID, u1.FEntryID
select cast(t1.fsourceinterid as int) as fparentinterid,cast(t1.fsourceentryid as int) as fparententryid,
cast(t1.fsourcetrantype as int) as fsourcetrantype,cast(t1.fsourceinterid as int) as fsourceinterid,
cast(t1.fsourceentryid as int) as fsourceentryid,cast(1 as int) as flevel
into #tmpLinkBills
from #TmpSelBills t1 inner join ictransactiontype t2 on t1.fsourcetrantype=t2.fid
left outer join seorderentry t3 on t3.finterid=t1.forderinterid and t3.fentryid=t1.forderentryid
left outer join t_icitem t4 on t4.fitemid=t3.fitemid
where (t1.fsourceinterid>0 and (isnull(t1.forderinterid,0)=0) or isnull(t4.FErpClsID,0)=5)
这个如何处理呢???