财务报表的一些方法

alter proc DOA_FAbinbalance(@hid int,@mode int)
as
set nocount on
begin
/******************************************************************************************************************
--@mode = 1 采购入库财务审核
--@mode = 2 销售出库财务审核
--@mode = 3 采购退货财务审核
--@mode = 4 销售退货财务审核
--@mode = 5 采购付款
--@mode = 6 销售收款
--@mode = 7 采购退货收款
--@mode = 8 销售退货付款

财务过账: thisbalance 即时帐
balancelog 明细账
******************************************************************************************************************/
if @mode = 1 --采购入库财务审核
begin
update thisbalance set balance=isnull(a.balance,0)+isnull(b.sumtotalmoney,0),changedate=getdate() --应付账
from thisbalance a inner join DWD_54 b on a.companyid=b.companyid and a.obj1=b.vndcode
where a.tabtype='应付账款' and acctname='应付账款RMB' and accttype='负债类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应付账款',companyid,vndcode,vndname,'','','','','应付账款RMB','RMB',sumtotalmoney,getdate(),'负债类'
from DWD_54 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)+isnull(b.sumtotalmoney,0),changedate=getdate() --资金账
from thisbalance a inner join DWD_54 b on a.companyid=b.companyid
where a.tabtype='库存商品' and acctname='库存商品' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '库存商品',companyid,'','','','','','','库存商品','RMB',sumtotalmoney,getdate(),'资产类'
from DWD_54 with(nolock)
where hid=@hid
end

/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '库存商品',GETDATE(),companyid,'库存商品','','','借','RMB',1,sumtotalmoney,'采购入库',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_54 with(nolock)
where hid=@hid --------贷

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应付账款',GETDATE(),companyid,'应付账款RMB',vndcode,vndname,'贷','RMB',1,sumtotalmoney,'采购入库',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_54 with(nolock)
where hid=@hid --------借
/****************************************************************************************************************/
update DWD_3 set shflag=1
from DWD_3 a inner join DWD_54 b on a.hid=b.refcode
where b.hid=@hid
end
if @mode = 2 --销售出库财务审核
begin
update thisbalance set balance=isnull(a.balance,0)+isnull(b.sumtotalmoney,0),changedate=getdate() --资产账
from thisbalance a inner join DWD_57 b on a.companyid=b.companyid and a.obj1=b.vndcode
where a.tabtype='业务收入' and acctname='主营业务收入' and accttype='损益类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '业务收入',companyid,vndcode,vndname,'','','','','主营业务收入','RMB',sumtotalmoney,getdate(),'损益类'
from DWD_57 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)+isnull(b.sumtotalmoney,0),changedate=getdate() --应收账
from thisbalance a inner join DWD_57 b on a.companyid=b.companyid and a.obj1=b.vndcode
where a.tabtype='应收账款' and acctname='应收账款RMB' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应收账款',companyid,vndcode,vndname,'','','','','应收账款RMB','RMB',sumtotalmoney,getdate(),'资产类'
from DWD_57 with(nolock)
where hid=@hid
end
/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应收账款',GETDATE(),companyid,'应收账款RMB',vndcode,vndname,'借','RMB',1,sumtotalmoney,'销售出库',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_57 with(nolock)
where hid=@hid --------贷

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '业务收入',GETDATE(),companyid,'主营业务收入',vndcode,vndname,'贷','RMB',1,sumtotalmoney,'销售出库',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_57 with(nolock)
where hid=@hid --------借
/****************************************************************************************************************/
update dwd_36 set shflag=1
from dwd_36 a inner join DWD_57 b on a.hid=b.refcode
where b.hid=@hid
end
if @mode = 3 --采购退货财务审核
begin
update thisbalance set balance=isnull(a.balance,0)-isnull(b.sumtotalmoney,0),changedate=getdate() --应付账
from thisbalance a inner join DWD_57 b on a.companyid=b.companyid and a.obj1=b.vndcode
where a.tabtype='应付账款' and acctname='应付账款RMB' and accttype='负债类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应付账款',companyid,vndcode,vndname,'','','','','应付账款RMB','RMB',-sumtotalmoney,getdate(),'负债类'
from DWD_57 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)-isnull(b.sumtotalmoney,0),changedate=getdate() --资金账
from thisbalance a inner join DWD_57 b on a.companyid=b.companyid
where a.tabtype='库存商品' and acctname='库存商品' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0 begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '库存商品',companyid,'','','','','','','库存商品','RMB',-sumtotalmoney,getdate(),'资产类'
from DWD_57 with(nolock)
where hid=@hid
end

/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '库存商品',GETDATE(),companyid,'库存商品','','','借','RMB',1,-sumtotalmoney,'采购退货',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_57 with(nolock)
where hid=@hid --------贷

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应付账款',GETDATE(),companyid,'应付账款RMB',vndcode,vndname,'贷','RMB',1,-sumtotalmoney,'采购退货',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_57 with(nolock)
where hid=@hid --------借
/****************************************************************************************************************/
update dwd_29 set shflag=1
from dwd_29 a inner join DWD_57 b on a.hid=b.refcode
where b.hid=@hid
end
if @mode = 4 --销售退货财务审核
begin
update thisbalance set balance=isnull(a.balance,0)-isnull(b.sumtotalmoney,0),changedate=getdate() --资产账
from thisbalance a inner join DWD_54 b on a.companyid=b.companyid and a.obj1=b.vndcode
where a.tabtype='业务收入' and acctname='主营业务收入' and accttype='损益类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '业务收入',companyid,vndcode,vndname,'','','','','主营业务收入','RMB',-sumtotalmoney,getdate(),'损益类'
from DWD_54 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)-isnull(b.sumtotalmoney,0),changedate=getdate() --应收账
from thisbalance a inner join DWD_54 b on a.companyid=b.companyid and a.obj1=b.vndcode
where a.tabtype='应收账款' and acctname='应收账款RMB' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应收账款',companyid,vndcode,vndname,'','','','','应收账款RMB','RMB',-sumtotalmoney,getdate(),'资产类'
from DWD_54 with(nolock)
where hid=@hid
end
/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应收账款',GETDATE(),companyid,'应收账款RMB',vndcode,vndname,'借','RMB',1,-sumtotalmoney,'销售退货',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_54 with(nolock)
where hid=@hid --------贷

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '业务收入',GETDATE(),companyid,'主营业务收入',vndcode,vndname,'贷','RMB',1,-sumtotalmoney,'销售退货',doctype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_54 with(nolock)
where hid=@hid --------借
/****************************************************************************************************************/
update dwd_27 set shflag=1
from dwd_27 a inner join DWD_54 b on a.hid=b.refcode
where b.hid=@hid
end

if @mode =5 --采购付款
begin
update thisbalance set balance=isnull(a.balance,0)-isnull(b.summoney,0),changedate=getdate() --应付账
from thisbalance a inner join DWD_56 b on a.companyid=b.companyid and a.obj1=b.cardcode
where a.tabtype='应付账款' and acctname='应付账款RMB' and accttype='负债类' and b.hid=2
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应付账款',companyid,cardcode,cardname,'','','','','应付账款RMB','RMB',-summoney,getdate(),'负债类'
from DWD_56 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)-isnull(b.summoney,0),changedate=getdate() --资金账
from thisbalance a inner join DWD_56 b on a.companyid=b.companyid
where a.tabtype='资金' and acctname='现金' and accttype='资产类' and b.hid=2
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '资金',companyid,'','','','','','','现金','RMB',-summoney,getdate(),'资产类'
from DWD_56 with(nolock) where hid=@hid
end

/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '资金',GETDATE(),companyid,'现金','','','贷','RMB',1,summoney,'付款单',doctype+fktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_56 with(nolock)
where hid=@hid --------贷

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应付账款',GETDATE(),companyid,'应付账款RMB',cardcode,cardname,'借','RMB',1,summoney,'付款单',doctype+fktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_56 with(nolock)
where hid=@hid --------借
/****************************************************************************************************************/
update DWD_8 set kd_fkze=isnull(a.kd_fkze,0)+b.summoney
from DWD_8 a inner join DWD_56 b on a.billNo=b.refhid
where b.hid=@hid
end
---------------------------------------------------------------------------------------------------------------------------------------------------------
if @mode =6 --销售收款
begin
update thisbalance set balance=isnull(a.balance,0)-isnull(b.summoney,0),changedate=getdate() --应收账
from thisbalance a inner join DWD_59 b on a.companyid=b.companyid and a.obj1=b.cardcode
where a.tabtype='应收账款' and acctname='应收账款RMB' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应收账款',companyid,cardcode,cardname,'','','','','应收账款RMB','RMB',-summoney,getdate(),'资产类'
from DWD_59 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)+isnull(b.summoney,0),changedate=getdate() --资金账
from thisbalance a inner join DWD_59 b on a.companyid=b.companyid
where a.tabtype='资金' and acctname='现金' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '资金',companyid,'','','','','','','现金','RMB',summoney,getdate(),'资产类'
from DWD_59 with(nolock)
where hid=@hid
end

/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '资金',GETDATE(),companyid,'现金','','','借','RMB',1,summoney,'收款单',doctype+sktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_59 with(nolock)
where hid=@hid --------借

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应收账款',GETDATE(),companyid,'应收账款RMB',cardcode,cardname,'贷','RMB',1,summoney,'收款单',doctype+sktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_59 with(nolock)
where hid=@hid --------贷
/****************************************************************************************************************/
update DWD_5 set yfmoney=isnull(a.yfmoney,0)+b.summoney
from DWD_5 a inner join DWD_59 b on a.billNo=b.refhid
where b.hid=@hid
end
-----------------------------------------------------------------------------------------------------------------------------------------
if @mode =7 --采购退货收款
begin
update thisbalance set balance=isnull(a.balance,0)+isnull(b.summoney,0),changedate=getdate() --应付账
from thisbalance a inner join DWD_59 b on a.companyid=b.companyid and a.obj1=b.cardcode
where a.tabtype='应付账款' and acctname='应付账款RMB' and accttype='负债类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应付账款',companyid,cardcode,cardname,'','','','','应付账款RMB','RMB',-summoney,getdate(),'负债类'
from DWD_59 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)+isnull(b.summoney,0),changedate=getdate() --资金账
from thisbalance a inner join DWD_59 b on a.companyid=b.companyid
where a.tabtype='资金' and acctname='现金' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '资金',companyid,'','','','','','','现金','RMB',summoney,getdate(),'资产类'
from DWD_59 with(nolock)
where hid=@hid
end

/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '资金',GETDATE(),companyid,'现金','','','贷','RMB',1,-summoney,'收款单',doctype+sktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_59 with(nolock)
where hid=@hid --------贷

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应付账款',GETDATE(),companyid,'应付账款RMB',cardcode,cardname,'借','RMB',1,-summoney,'收款单',doctype+sktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_59 with(nolock)
where hid=@hid --------借
/****************************************************************************************************************/
update dwd_29 set kd_tkze=isnull(a.kd_tkze,0)+b.summoney
from dwd_29 a inner join DWD_59 b on a.billNo=b.refhid
where b.hid=@hid
end
---------------------------------------------------------------------------------------------------------------------------------------------------------
if @mode =8 --销售退货付款
begin
update thisbalance set balance=isnull(a.balance,0)+isnull(b.summoney,0),changedate=getdate() --应收账
from thisbalance a inner join DWD_56 b on a.companyid=b.companyid and a.obj1=b.cardcode
where a.tabtype='应收账款' and acctname='应收账款RMB' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '应收账款',companyid,cardcode,cardname,'','','','','应收账款RMB','RMB',summoney,getdate(),'资产类'
from DWD_56 with(nolock)
where hid=@hid
end

update thisbalance set balance=isnull(a.balance,0)-isnull(b.summoney,0),changedate=getdate() --资金账
from thisbalance a inner join DWD_56 b on a.companyid=b.companyid
where a.tabtype='资金' and acctname='现金' and accttype='资产类' and b.hid=@hid
if @@rowcount = 0
begin
insert into thisbalance(tabtype,companyid,obj1,obj1name,obj2,obj2name,obj3,obj3name,Acctname,currency,balance,changedate,accttype)
select '资金',companyid,'','','','','','','现金','RMB',-summoney,getdate(),'资产类'
from DWD_56 with(nolock)
where hid=@hid
end

/*****************************************************明细账*****************************************************/
insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,debit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '资金',GETDATE(),companyid,'现金','','','借','RMB',1,-summoney,'付款单',doctype+fktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_56 with(nolock)
where hid=@hid --------借

insert into balancelog(tabtype,inserttime,companyid,Acctname,obj1,obj1name,dcflag,currency,
exchange_rate,credit_totalmoney,fname,doctype,billno,docdate,periodid,memo,docword,fid,sid,uid,hid)
select '应收账款',GETDATE(),companyid,'应收账款RMB',cardcode,cardname,'贷','RMB',1,-summoney,'付款单',doctype+fktype,billno,docdate,
convert(varchar(7),docdate,120),memo,'转',fid,sid,duid,hid
from DWD_56 with(nolock)
where hid=@hid --------贷
/****************************************************************************************************************/
update dwd_27 set kd_tkze=isnull(a.kd_tkze,0)+b.summoney
from dwd_27 a inner join DWD_56 b on a.billNo=b.refhid
where b.hid=@hid
end
-----------------------------------------------------------------------------------------------------------------------------------------
end