SQL更新-金蝶k3/kis专业版旗舰版-单价公式
金蝶k3/kis专业版旗舰版-更新单价公式
---------------------------------------------------------------
001-凭证单价采用外币金额除于数量
高清图片查看方法：点击图片---右键---在新标签页中打开图片（或全屏看图）
---------------------------------------------------------------
SQL语句
------------------------------------------
--查询凭证中数量核算的凭证分录数据
select
t_Voucher.FDate As [凭证日期] , t_Voucher.FYear As [会计年度] , t_Voucher.FPeriod As [会计期间] , t_VoucherGroup.FName As [凭证字名称] , t_Voucher.FNumber As [凭证号]
, t_VoucherEntry.FEntryID As [分录号] , t_VoucherEntry.FExplanation As [摘要] , t_Currency.FNumber As [币别代码] , t_Currency.FName As [币别名称] , t_VoucherEntry.FExchangeRate As [汇率_凭证分录表]
, t_Account.FNumber As [科目代码] , t_Account.FName As [科目名称], t_VoucherEntry.FDC As [借贷方向] , t_VoucherEntry.FQuantity As [数量]
, t_VoucherEntry.FAmount As [本位币金额] , t_VoucherEntry.FUnitPrice As [金蝶单价为_本位币金额除于数量]
, t_VoucherEntry.FAmountFor As [原币金额] , Round(ABS(FAmountFor)/ABS(FQuantity),2) as 用户希望单价为_原币金额除于数量_且保留两位小数
from
t_Voucher left outer join t_VoucherEntry on t_Voucher.FVoucherID = t_VoucherEntry.FVoucherID left outer join t_VoucherGroup on t_Voucher.FGroupID = t_VoucherGroup.FGroupID left outer join t_user on t_Voucher.FPreparerID = t_user.FUserID left outer join t_Account on t_VoucherEntry.FAccountID = t_Account.FAccountID left outer join t_Currency on t_VoucherEntry.FCurrencyID = t_Currency.FCurrencyID
where t_VoucherEntry.FQuantity <> 0
order by t_Voucher.FYear , t_Voucher.FPeriod , t_VoucherGroup.FName , t_Voucher.FNumber , t_VoucherEntry.FEntryID
-----------------------------------------
--将凭证中单价更新为原币/数量
update t_VoucherEntry
set FUnitPrice = Round(ABS(FAmountFor)/ABS(FQuantity),2)
where FQuantity <> 0
---------------------------------------------------------------
---------------------------------------------------------------
