SQL更新-金蝶k3-手工填写的结转损益凭证更新为机
将期间损益结转凭证更新为机制凭证
因为在金蝶中手工填写的期间损益凭证,金蝶的利润表不能取的数据,
(用友手工填写的期间损益凭证,利润表是可以取得数据的)
因为金蝶的利润表公式是从金蝶数据库中的"损益类科目实际发生额"表抽取数据的,
而手工填写的期间损益凭证,数据不进入到这个表中,这里可以通过更新凭证的必要字段进行调整
调整后的凭证,通过记账,金蝶会将该类记录写入到"损益类科目实际发生额"表
--------------------------------------
select *
from t_Voucher order by fperiod , fgroupid , fnumber
--凭证<主表>涉及损益的字段
select FInternalInd as A_TransferPL , FTranType as A_20002 , *
from t_Voucher
where t_Voucher.FExplanation = '期间损益结转'
order by fperiod , fgroupid , fnumber
--凭证<子表>涉及损益的字段
select FInternalInd as A_TransferPL , *
from t_VoucherEntry
where t_VoucherEntry.FExplanation = '期间损益结转'
--凭证<主表>关联子表
select FSideEntryID AS 对方科目分录号, t_Voucher.* , t_VoucherEntry.*
from t_Voucher left outer join t_VoucherEntry on t_Voucher.fvoucherid = t_VoucherEntry.fvoucherid
where t_Voucher.FExplanation = '期间损益结转' or ( t_Voucher.fnumber = 66 and t_Voucher.fperiod = 1 )
order by t_Voucher.fperiod , t_Voucher.fgroupid , t_Voucher.fnumber
--*********************************************************************************
--更新凭证<主表>,将凭证设置为机制损益类凭证
update t_Voucher
set FInternalInd = 'TransferPL' , FTranType = 20002
where FExplanation = '期间损益结转'
--更新凭证<子表>,将凭证设置为机制损益类凭证
update t_VoucherEntry
set FInternalInd = 'TransferPL'
where FExplanation = '期间损益结转'
--*********************************************************************************
--凭证字
select * from t_vouchergroup
--凭证主表--凭证字
select *
from t_Voucher
left outer join t_vouchergroup on t_Voucher.FGroupID = t_vouchergroup.FGroupID
where t_Voucher.FYear = 2019 and t_Voucher.FPeriod = 1 and t_Voucher.Fnumber = 1 and t_vouchergroup.fname = '记'
--凭证主表--凭证字--凭证子表
select *
from t_Voucher
left outer join t_vouchergroup on t_Voucher.FGroupID = t_vouchergroup.FGroupID
left outer join t_VoucherEntry on t_Voucher.FVoucherID = t_voucherentry.FVoucherID
where t_Voucher.FYear = 2019 and t_Voucher.FPeriod = 1 and t_Voucher.Fnumber = 66 and t_vouchergroup.fname = '记'
--*********************************************************************************
--手工将金蝶后台的某些凭证--调整为结转损益类凭证的sql语句
--注意事项1,请确保能准确定位到具体需要调整凭证的,1.年度/2.期间/3.凭证字/4.凭证号
--注意事项2,更新的凭证,必须是未过账的凭证(如果不确认,可以在where条件中加上未过账的标志)
--更新凭证<主表>,将凭证设置为机制损益类凭证--(凭证主表--关联--凭证字)
update t_Voucher
set FInternalInd = 'TransferPL' , FTranType = 20002
from t_Voucher
left outer join t_vouchergroup on t_Voucher.FGroupID = t_vouchergroup.FGroupID
where t_Voucher.FYear = 2019
and t_Voucher.FPeriod = 1
and t_Voucher.Fnumber = 1
and t_vouchergroup.fname = '记'
--更新凭证<子表>,将凭证设置为机制损益类凭证
update t_VoucherEntry
set FInternalInd = 'TransferPL'
from t_Voucher
left outer join t_vouchergroup on t_Voucher.FGroupID = t_vouchergroup.FGroupID
left outer join t_VoucherEntry on t_Voucher.FVoucherID = t_voucherentry.FVoucherID
where t_Voucher.FYear = 2019
and t_Voucher.FPeriod = 1
and t_Voucher.Fnumber = 66
and t_vouchergroup.fname = '记'
---------------------------------------------------------
--*********************************************************************************
--取消<主表>--<期间损益结转>标志:FInternalInd = null / FTranType = 0
update t_Voucher
set FInternalInd = null , FTranType = 0
from t_Voucher
left outer join t_vouchergroup on t_Voucher.FGroupID = t_vouchergroup.FGroupID
where t_Voucher.FYear = 2019
and t_Voucher.FPeriod = 1
and t_Voucher.Fnumber = 1
and t_vouchergroup.fname = '记'
--取消<子表>--<期间损益结转>标志:FInternalInd = null
update t_VoucherEntry
set FInternalInd = null
from t_Voucher
left outer join t_vouchergroup on t_Voucher.FGroupID = t_vouchergroup.FGroupID
left outer join t_VoucherEntry on t_Voucher.FVoucherID = t_voucherentry.FVoucherID
where t_Voucher.FYear = 2019
and t_Voucher.FPeriod = 1
and t_Voucher.Fnumber = 66
and t_vouchergroup.fname = '记'
--------------------------------
