用友870年结经验

mamiaoli1986

一、各模块12月月结

通常12月月结时，销售、库存、存货模块会出现问题
1、销售提示尚有未审核的发货单
如果发货单确认为无用单据，可直接关闭；如仍需要审核，建议先进行审核，后对销售出库单日期进行更新；
提示尚有未开票发货单：可直接选是，不影响
2、库存模块提示尚存在期初单据、出入库单据未审核
---------检查期初数据是否审核并更新--------------
Select chandler,dveridate,* From Rdrecord Where  (((Rdrecord.cVouChType=N'34' and rdrecord.biafirst<>1)or Rdrecord.bIsSTQc=1) and  Isnull(cHandler,'')='') And dDate<'2012-01-01'
update Rdrecord set chandler='asuser' ,dveridate='2012-01-01 00:00:00.000'
where (((Rdrecord.cVouChType=N'34' and rdrecord.biafirst<>1)or Rdrecord.bIsSTQc=1) and  Isnull(cHandler,'')='') And dDate<'2012-01-01'
-----------检查出入库单据是否审核并更新---------------
select chandler,dveridate,* from rdrecord where (chandler is null or chandler ='') and (dveridate is null or dveridate='') and year(ddate)=2012
order by ddate
update rdrecord set cdefine4=ddate where (chandler is null or chandler ='') and (dveridate is null or dveridate='') and year(ddate)=2012
update rdrecord set ddate='2013-01-01 00:00:00.000' where (chandler is null or chandler ='') and (dveridate is null or dveridate='') and year(ddate)=2012

select cVerifyPerson,dVerifyDate,* from TransVouch where (cVerifyPerson is null or cVerifyPerson ='') and (dVerifyDate is null or dVerifyDate='') and year(dTVDate)=2012
order by dTVDate

update TransVouch set cdefine4=dTVDate where (cVerifyPerson is null or cVerifyPerson ='') and (cVerifyPerson is null or cVerifyPerson ='') and year(dTVDate)=2012
update TransVouch set dTVDate='2013-01-01 00:00:00.000' where (cVerifyPerson is null or cVerifyPerson ='') and (cVerifyPerson is null or cVerifyPerson ='') and year(dTVDate)=2012

直接将没有审核的单据，通过更改单据日期，直接结转至下一年
3、存货核算提示：尚有已全部暂估报销的单据未进行处理，不能进行12月的月结处理。
a.
    select pursettlevouchs.* from ((rdrecord inner join rdrecords on rdrecord.id=rdrecords.id) left join pursettlevouchs on rdrecords.autoid=pursettlevouchs.irdsid) where (not rdrecord.caccounter is null) and pursettlevouchs.bAccount=0 and isnull(rdrecords.iquantity,0)=isnull(rdrecords.isquantity,0)
    update pursettlevouchs set baccount=1 where id in (select pursettlevouchs.id from ((rdrecord inner join rdrecords on rdrecord.id=rdrecords.id) left join pursettlevouchs on rdrecords.autoid=pursettlevouchs.irdsid) where (not rdrecord.caccounter is null) and pursettlevouchs.bAccount=0 and isnull(rdrecords.iquantity,0)=isnull(rdrecords.isquantity,0) )

b.select pursettlevouchs.*
from (rdrecords left join pursettlevouchs on rdrecords.autoid=pursettlevouchs.irdsid And pursettlevouchs.cUpsotype = N'01')
where (IsNUll(rdrecords.cbaccounter, N'') <> N'') and pursettlevouchs.bAccount=0
and isnull(rdrecords.iquantity,0)=isnull(rdrecords.isquantity,0)
update pursettlevouchs
set bAccount=1
from (rdrecords left join pursettlevouchs on rdrecords.autoid=pursettlevouchs.irdsid And pursettlevouchs.cUpsotype = N'01')
where (IsNUll(rdrecords.cbaccounter, N'') <> N'') and pursettlevouchs.bAccount=0
and isnull(rdrecords.iquantity,0)=isnull(rdrecords.isquantity,0)

二、开始年结
1、---先禁用用户----
select *  FROM ua_user
保险起见，并进行备份：select * into uflogbak..ua_user_20130107 from ua_user
select *  FROM ua_user  where   nstate='1' and  iadmin <> '1'
select *  FROM ua_user  where  iadmin <> '1'
update ua_user set    cUserHand  = null where  iadmin <> '1'
update ua_user set   cUserHand = nstate where  iadmin <> '1'     
update ua_user set nstate=1 where nstate=0  AND  iadmin <> '1'
保留demo的操作
select * from ua_user where cuser_name='demo'
update ua_user set nstate=0 where cuser_name='demo'
2、重启服务器
3、备份账套
4、备份ufsystem库
5、权限备份及清除ua_holdauth
select * into uflogbak..ua_holdauthbak20130107 from ufsystem..ua_holdauth
delete from ufsystem..ua_holdauth where cacc_id<>'001'
go
delete from ufsystem..ua_holdauth where cuser_id<>'demo'
6、备份基础档案表---此步对存货档案字段有开发的客户极为重要
7、更新inventory等基础档案字段，与用友基础表的字段需一致
8、建立新的年度账，并升级数据库
9、如有触发器，需要备份触发器，并清除触发器，用友年结时有触发器，是无法进行年结的
10、检查2012年度与2013年度表结构的变化，并更新2013年度表结构
     ps：由于我们存在导数据，所以表结构变化了很多，用友年结时对表结构的要求非常严
11、正式开始年度结转
12、更新基础档案数据
13、恢复触发器、用户权限
delete from ua_holdauth
go
insert into ua_holdauth(cacc_id,iyear,cuser_id,cauth_id,iisuser)
select cacc_id,iyear,cuser_id,cauth_id,iisuser from ua_holdauthbak20130107
go
----将2012年权限复制到2013年
insert into ua_holdauth(cacc_id,iyear,cuser_id,cauth_id,iisuser)
select cacc_id,'2013',cuser_id,cauth_id,iisuser from ua_holdauthbak20130107
where iyear='2012'
go
14、恢复注销操作员
use ufsystem
update ua_user set  nstate=cUserHand  where  iadmin <> '1'
15、各种单据格式调整
特别注意自定义字段的名称及赋值