--------------*******商贸版结转新帐套*******---------------
--***********结转之前必须本期结账且无凭证和单据**************--
--vw_cc_Receive--收入表
--vw_cc_Send--支出表
--t_cc_stock_BatchNo
--清除业务余额表和即时库存表
delete t_CC_Bal where fperiod<(SELECT FValue from t_SystemProfile WHERE FKey='CurrentPeriod' AND FCategory='IC')
delete t_cc_stock where fperiod<(SELECT FValue from t_SystemProfile WHERE FKey='CurrentPeriod' AND FCategory='IC')
--修改存货初始数据
delete ICInvInitial
go
--delete t_cc_inventory where fqty=0
--exec sp_UpdateInventory --重算即时库存表
insert into ICInvInitial(FBrNo,fperiod,fstockid,fitemid,fbatchno,fqty,fprice,famount,fbillinterid)
select '',1,fstockid,fitemid,fbatchno,fbegqty,fbegbal/fbegqty,fbegbal,fid from t_cc_bal where fbegqty<>0
update ICInvInitial set fperiod=(select fvalue from t_SystemProfile WHERE FKey='CurrentPeriod' AND FCategory='ic' )
--select * from t_balance
--清除科目余额表
delete t_Balance where fperiod<(SELECT FValue from t_SystemProfile WHERE FKey='CurrentPeriod' AND FCategory='GL')
go
--清除科目累积数
update t_Balance set fytddebitfor=0,fytdcreditfor=0,fytddebit=0,fytdcredit=0,FBeginBalanceFor=FEndBalanceFor,FBeginBalance =FEndBalance,FDebitFor=0,FCreditFor=0,FDebit=0,FCredit=0
go
--清除损益科目本年实际发生额
delete t_ProfitAndLoss
go
--修改启用期间
update t_SystemProfile set fvalue=(select fvalue from t_SystemProfile WHERE FKey='CurrentPeriod' AND FCategory='GL' ) where FKey='StartPeriod' AND FCategory='GL'
update t_SystemProfile set fvalue=(select fvalue from t_SystemProfile WHERE FKey='CurrentYear' AND FCategory='GL' ) where FKey='StartYear' AND FCategory='GL'
update t_SystemProfile set fvalue=(select fvalue from t_SystemProfile WHERE FKey='CurrentPeriod' AND FCategory='ic' ) where FKey='StartPeriod' AND FCategory='ic'
update t_SystemProfile set fvalue=(select fvalue from t_SystemProfile WHERE FKey='CurrentYear' AND FCategory='ic' ) where FKey='StartYear' AND FCategory='ic'
go
--修改当前年度和期间（为删除凭证做准备）
update t_SystemProfile set fvalue=(select top 1 fperiod from t_Voucher order by fyear,fperiod asc) where FKey='CurrentPeriod' AND FCategory='GL'
update t_SystemProfile set fvalue=(select top 1 fyear from t_Voucher order by fyear,fperiod asc ) where FKey='CurrentYear' AND FCategory='GL'
go
--修改凭证状态（审核，过账状态）
update t_Voucher set fchecked=0,fposted=0,fcheckerid=-1,fposterid=-1
go
--修改客户初始余额
update t_Organization set fcheckamount=0,fprepcheckamount=0, funcheckamount=fcurreceamount , forireceamount=fcurreceamount,funprepcheckamount=fcurprepamount,foriprepamount=fcurprepamount,FToItemId=fitemid
go
--修改供应商初始余额
update t_Supplier set fcheckamount=0,fprepcheckamount=0, funcheckamount=fcurreceamount , forireceamount=fcurreceamount,funprepcheckamount=fcurprepamount,foriprepamount=fcurprepamount,FToItemId=fitemid
go
--清除历史数据
DELETE FROM t_ProfitAndLoss
Go
DELETE FROM t_mutex
Go
delete t_RP_RBill --收款单表
go
delete t_RP_RBillEntry --收款单分录表
go
delete t_RP_ROtherBill --其它收款单表
go
delete t_RP_ROtherBillEntry --其它收款单分录表
go
delete T_CC_StockBill --仓存单据表
go
delete T_CC_StockBillEntry --仓存单据分录表
go
delete t_RP_PBillEntry --付款单分录表
go
delete t_RP_PBill --付款单表
go
delete t_RP_POtherBill --其它付款单表
go
delete t_RP_POtherBillEntry --其它付款单分录表
go
delete t_cg_order --采购订单表
go
delete t_cg_orderEntry --采购订单表体表
go
delete t_XS_Order --销售订单表
go
delete t_XS_OrderEntry --销售订单表体表
go
delete T_CC_Templet --组装清单表
go
delete T_CC_TempletEntry --组装清单分录表
go
delete T_CC_ZZCX --组装拆卸单表
go
delete T_CC_ZZCX_Entry --组装拆卸单分录表1
go
delete T_CC_ZZCX_Entry2 --组装拆卸单分录表2
go
delete t_VoucherEntry --凭证分录表
go
delete t_Voucher --凭证表
go
delete t_RP_CheckBill --核销单表
go
delete t_RP_CheckBillEntry1 --核销单分录表1
go
delete t_RP_CheckBillEntry2 --核销单分录表2
go
delete t_SearchInfo_temp --搜索表
go
delete t_RP_BankBill --银行存取款单
go
-------
--修改当前年度和期间（改回正确的当前期间）
update t_SystemProfile set fvalue=(select fvalue from t_SystemProfile WHERE FKey='StartPeriod' AND FCategory='GL' ) where FKey='CurrentPeriod' AND FCategory='GL'
update t_SystemProfile set fvalue=(select fvalue from t_SystemProfile WHERE FKey='StartYear' AND FCategory='GL' ) where FKey='CurrentYear' AND FCategory='GL'
go
--exec StockINIDataOn
/*
select * from t_tabledescription
where ftablename ='ICInvInitial'
--where fdescription like '%取%'
--t_cc_inventory 即时库存
select * from t_cc_inventory
ICInvInitial --存货初始数据
t_CC_Bal --仓库余额
t_cc_stock
ICInvInitial
t_cc_inventory
select * from t_CC_Bal
From ICInvInitial u1,t_ICItem t1,t_MeasureUnit t2,t_SubMessage t3
Where t1.FDeleted=0 And u1.FItemID=t1.FItemID and
t1.FUnitID=t2.FMeasureUnitID and
t1.FTrack=t3.FInterID and
t3.FTypeID = 200 And u1.FStockID =125 And u1.FPeriod =7
and t1.FTrack<>78 And t1.FTrack<>80 and t1.FISBatchManager=0
*/