涉及界面：
问题：财务抱怨外购入库单价格取错，单价多除了一次税率
例如，采购单里面注明了价格是不含税15.3256
结果在外购入库单里面，又自做主张除以税率17%，把采购成本搞成了13.0988，
咨询专家给了2句SQL来解决问题：
--更新收料通知下外购入库单价取数规则
update ICSelbills
set FAction='
CASE when EXISTS(select * from t_SystemProfile where FCategory ='''+'IC'+''' AND FKey='''+'POOrderTaxInPrice'+''' and FValue =1)
then u1.FAuxPrice*(1+(CAST((CASE when t4.FTaxRate<>0 /*税率-有税取税*/
then t4.FTaxRate
else case when t8.FValueAddRate<>0 /*没税取增值税*/
then t8.FValueAddRate
else 17 /*缺省税率17*/
end
end)
AS FLOAT)/100))
else u1.FAuxPrice
end'
where FID='A01' /*收料通知下外购入库单*/
and FFieldName='FSCBillInterID'
and FSelType=0
and FColName ='FPurchasePrice'
--更新采购订单下推收料通知单价取数规则
update ICSelbills set FAction='CAST(u1.FAuxPrice AS FLOAT)*(100-u1.FTaxRate)/100'
where FID='P03' /*采购订单下推收料通知单*/
and FFieldName='FCnnInterID'
and FColName='FAuxPrice'
原来外购入库单的取数规则是保持在ICSelbills的表里面。
用语句取出原来的值如下：
/*收料通知下外购入库单*/
select * from ICSelbills where FID='A01' AND FFieldName='FSCBillInterID' AND FSelType=0 AND FColName ='FPurchasePrice'
CAST(u1.FAuxPrice AS FLOAT)*CAST(v1.FExchangeRate AS FLOAT) --看起来考虑了汇率
/*采购订单下推收料通知单*/
select * from ICSelbills where FID='P03' AND FFieldName='FCnnInterID' AND FColName='FAuxPrice'
CASE (SELECT FValue FROM t_SystemProfile Where FCategory='IC' and FKey='POOrderTaxInPrice') /*取系统配置采购订单单价默认为含税单价，TW为1*/
WHEN '0'
THEN CAST(u1.FAuxPrice AS FLOAT)*CAST((100-u1.FTaxRate) AS FLOAT)/100 /*系统没配置默认为含税单价，则按单价回算税前价格*/
ELSE u1.FAuxPriceDiscount /*系统配置采购订单单价默认为含税单价，TW此处生效*/
END
CASE (SELECT FValue FROM t_SystemProfile Where FCategory='IC' and FKey='POOrderTaxInPrice') WHEN '0' THEN CAST(u1.FAuxPrice AS FLOAT)*CAST((100-u1.FTaxRate) AS FLOAT)/100 ELSE u1.FAuxPriceDiscount END
可见TW取错了u1.FAuxPriceDiscount 实际含税单价
实际执行了“更新采购订单下推收料通知单价取数规则”就可以了。
update ICSelbills set FAction='CAST(u1.FAuxPrice AS FLOAT)*(100-u1.FTaxRate)/100' where FID='P03' AND FFieldName='FCnnInterID' AND FColName='FAuxPrice'