﻿ 經典案例 財務管理系統（6）[3]_電腦知識網

# 經典案例 財務管理系統（6）[3]

2022-06-13   來源: Delphi編程

如果科目代碼不是這樣則該存儲過程需要作相應的修改

delete from 資產負債表 where 會計期間 = @kjqj

insert into 資產負債表(會計期間) values(@kjqj)

update 資產負債表 set 現金及現金等價物 = b金額 from 資產負債表 as a

(select sum(isnull(本期借方余額)isnull(本期貸方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 >= and 科目代碼 <= )

as b where a會計期間 = @kjqj

update 資產負債表 set 應收帳款 = b金額 from 資產負債表 as a

(select sum(isnull(本期借方余額)isnull(本期貸方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 >= and 科目代碼 <= and 科目代碼<>)

as b where a會計期間 = @kjqj

注意壞帳准備為貸方

update 資產負債表 set 壞帳准備 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 應收帳款淨值 = 應收帳款 壞帳准備

update 資產負債表 set 流動資產總計 = 現金及現金等價物 + 應收帳款淨值

update 資產負債表 set 固定資產原值 = b金額 from 資產負債表 as a

(select sum(isnull(本期借方余額)isnull(本期貸方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and 科目代碼 =)

as b where a會計期間 = @kjqj

注意累計折舊為貸方

update 資產負債表 set 累計折舊 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and 科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 固定資產總計 = 固定資產原值 累計折舊

update 資產負債表 set 其他資產 = b金額 from 資產負債表 as a

(select sum(isnull(本期借方余額)isnull(本期貸方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 >= and 科目代碼 <= and 科目代碼<>

and 科目代碼 <> )

as b where a會計期間 = @kjqj

update 資產負債表 set 資產總計 = 流動資產總計 + 固定資產總計 + 其他資產

計算負債及所有者權益

負債類級別都是貸方金額

update 資產負債表 set 應付帳款 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 >= and 科目代碼 <= )

as b where a會計期間 = @kjqj

update 資產負債表 set 預收帳款 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 應付工資 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and 科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 其他負債 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 >= and 科目代碼 <= and 科目代碼 <> )

as b where a會計期間 = @kjqj

update 資產負債表 set 負債總計 = 應付帳款 + 預收帳款 + 應付工資 + 其他負債

計算所有者權益

update 資產負債表 set 實收資本 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and 科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 資本公積 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and 科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 贏余公積 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and 科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 未分配利潤 = b金額 from 資產負債表 as a

(select sum(isnull(本期貸方余額)isnull(本期借方余額)) as 金額

from 科目余額表 where 會計期間 = @kjqj and

科目代碼 = or 科目代碼 = )

as b where a會計期間 = @kjqj

update 資產負債表 set 所有者權益總計 = 實收資本 + 資本公積 +

贏余公積 + 未分配利潤

update 資產負債表 set 負債及所有者權益總計 = 負債總計 + 所有者權益總計

commit

GO

[]  []  []

From:http://tw.wingwit.com/Article/program/Delphi/201311/24740.html