18721107673
18721107673
注意:请首先做好账套备份
SQL查询金蝶凭证和批量更新金蝶凭证的制单人----参考用
--------------------------
--第1步:查询原有凭证信息
select t_Voucher.FDate As [凭证日期] , t_Voucher.FYear As [会计年度] , t_Voucher.FPeriod As [会计期间] , t_VoucherGroup.FName As [凭证字名称] , t_Voucher.FNumber As [凭证号] , t_Account.FNumber As [科目代码] , t_Account.FName As [科目名称] , t_VoucherEntry.FEntryID As [分录号] , t_VoucherEntry.FExplanation As [摘要]
,(case t_VoucherEntry.FDC when 1 then '借' else '贷' end ) As [借贷方向] , t_VoucherEntry.FAmount As [本位币金额] , t_Voucher.FPreparerID As [制单人ID] , t_user.FName As [制单人名称]
, t_Voucher.FReference As [参考信息] , t_Voucher.FDebitTotal As [借方金额合计] , t_Voucher.FCreditTotal As [贷方金额合计] , t_Voucher.FChecked As [是否审核] , t_Voucher.FPosted As [是否过账], t_Voucher.FSerialNum As [凭证序号] , t_Voucher.FTranType As [单据类型] , t_Voucher.FExplanation As [备注] , t_Voucher.FAttachments As [附件张数] , t_Voucher.FInternalInd As [机制凭证_凭证表] , t_Voucher.FTransDate As [业务日期] , t_Voucher.FBrNo As [公司代码_凭证表] , t_Voucher.FVoucherID As [凭证内码_凭证表] , t_Voucher.FGroupID As [凭证字内码_凭证表] , t_Voucher.FEntryCount As [分录数] , t_Voucher.FCheckerID As [审核人] , t_Voucher.FPosterID As [记账人] , t_Voucher.FCashierID As [出纳员] , t_Voucher.FHandler As [会计主管] , t_Voucher.FOwnerGroupID As [制单人所属工作组] , t_Voucher.FObjectName As [对象接口] , t_Voucher.FParameter As [接口参数] , t_Voucher.FFrameWorkID As [集团组织机构内码] , t_Voucher.FApproveID As [审批] , t_Voucher.FFootNote As [批注] , t_Voucher.UUID As [UUID_凭证表] , t_Voucher.FModifyTime As [修改时间] , t_VoucherEntry.FDetailID As [核算项目使用状况内码_凭证分录表] , t_VoucherEntry.FCurrencyID As [币别] , t_VoucherEntry.FExchangeRate As [汇率_凭证分录表] , t_VoucherEntry.FAmountFor As [原币金额], t_VoucherEntry.FAccountID2 As [对方科目] , t_VoucherEntry.FSettleTypeID As [结算方式] , t_VoucherEntry.FSettleNo As [结算号] , t_VoucherEntry.FCashFlowItem As [现金流量] , t_VoucherEntry.FBrNo As [公司代码_凭证分录表] , t_VoucherEntry.FVoucherID As [凭证内码_凭证分录表] , t_VoucherEntry.FAccountID As [科目内码_凭证分录表] , t_VoucherEntry.FQuantity As [数量] , t_VoucherEntry.FMeasureUnitID As [单位内码_凭证分录表] , t_VoucherEntry.FUnitPrice As [单价] , t_VoucherEntry.FInternalInd As [机制凭证_凭证分录表] , t_VoucherEntry.FTransNo As [业务号] , t_VoucherEntry.FTaskID As [项目任务内码] , t_VoucherEntry.FResourceID As [项目资源内码] , t_Account.FLevel As [科目级次] , t_Account.FDetail As [明细科目_1是_0否] , t_Account.FParentID As [顶级_即1级科目内码] , t_Account.FDC As [借贷方向_1借_负1贷] , t_Account.FHelperCode As [助记码] , t_Account.FDelete As [是否禁用_科目表] , t_Account.FFullName As [科目全名] , t_Currency.FNumber As [币别代码] , t_Currency.FName As [币别名称] , t_Currency.FExchangeRate As [汇率_币别表] , t_VoucherGroup.FGroupID 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_Voucher.FYear = 2019 and t_Voucher.FPeriod = 6
order by t_Voucher.FYear , t_Voucher.FPeriod , t_VoucherGroup.FName , t_Voucher.FNumber , t_VoucherEntry.FEntryID
---------------------------
--第2步:在金蝶中增加准备更新后的制单人信息
--比如: <新制单人名称,比如：张三>
---------------------------
--第3步:在金蝶中增加准备更新后的制单人信息
--比如: 将原来的<administrator>更新为<新制单人名称,比如：张三>
update t_Voucher
-- 更新后的制单人名称
set FPreparerID = ( select FUserID from t_user where FName = '张三' )
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_Voucher.FYear = 2019 --年度
and t_Voucher.FPeriod = 3 --期间(可以根据实际情况调整范围)
and t_VoucherGroup.FName = '记' --凭证字
and t_Voucher.FNumber = 5 --凭证号(可以根据实际情况设置范围)
and t_user.FName = 'administrator' --原制单人
sql查询金蝶凭证和批量更新金蝶凭证的制单人
----------------------------