【正文】 JECTPROPERTY(id, N39。) and OBJECTPROPERTY(id, N39。 then (借方 貸方) else (貸方 借方) end from 本期匯總帳簿 as a, (select 科目代碼, sum(借方) as 借方, sum(貸方) as 貸方 from 分錄表 as f, 憑證表 as p where = group by ) as b where = 設(shè)置本期明細(xì)帳簿信息 insert into 本期明細(xì)帳簿(分錄編號(hào),會(huì)計(jì)期間,憑證字號(hào), 憑證編號(hào), 摘要, 科目代碼, 借方, 貸方, 數(shù)量, 單價(jià), 結(jié)算方式, 結(jié)算號(hào), 結(jié)算日期) select 編號(hào),會(huì)計(jì)期間,憑證字號(hào), , 摘要, 科目代碼, 借方, 貸方, 數(shù)量, 單價(jià), 結(jié)算方式, 結(jié)算號(hào), 結(jié)算日期 from 憑證表 as p, 分錄表 as f where = 計(jì)算余額 update 本期明細(xì)帳簿 set 余額方向= from 科目表 as a where = 通過(guò)游標(biāo)循環(huán)計(jì)算余額 declare bh int, dm char(20), jf money, df money declare fx char(4), bj char(4), ye money, kjqj int declare mycursor cursor for select 內(nèi)部編號(hào),科目代碼, 借方,貸方,余額方向,余額標(biāo)記,會(huì)計(jì)期間 from 本期明細(xì)帳簿 where 余額標(biāo)記 is null order by 內(nèi)部編號(hào) open mycursor fetch next from mycursor into bh,dm,jf,df,fx,bj,kjqj while (fetch_status=0) begin select ye=NULL select ye = 余額 from 本期明細(xì)帳簿 where 內(nèi)部編號(hào) in (select max(內(nèi)部編號(hào)) from 本期明細(xì)帳簿 where 內(nèi)部編號(hào) bh and 科目代碼=dm and 余額標(biāo)記 = 39。 then 本期借方余額 本期貸方余額 else 本期貸方余額 本期借方余額 end from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = dm select ye = isnull(ye,0) update 本期明細(xì)帳簿 set 余額=case when fx=39。 where 內(nèi)部編號(hào)=bh fetch next from mycursor into bh,dm,jf,df,fx,bj,kjqj end close mycursor deallocate mycursor 更改狀態(tài) update 憑證表 set 過(guò)帳狀態(tài) = 39。 update 系統(tǒng)參數(shù)表 set 取值 = datepart(mm,getdate()) where 參數(shù)名稱(chēng) = 39。 insert into 科目余額表(科目代碼, 期初借方余額, 期初貸方余額, 本期借方發(fā)生額, 本期貸方發(fā)生額, 本年借方累計(jì)發(fā)生額, 本年貸方累計(jì)發(fā)生額, 本期借方余額, 本期貸方余額, 余額方向) select 科目代碼,累計(jì)借方,累計(jì)貸方,0,0,0,0, case when 余額方向=39。 then 期初余額 else 0 end, 余額方向 from 帳簿初始化表 update 科目余額表 set 會(huì)計(jì)期間= from 系統(tǒng)參數(shù)表 as b where 參數(shù)名稱(chēng)=39。 將本期匯總帳簿和明細(xì)帳簿倒入歷史,實(shí)例程序不演示 清除匯總帳簿和明細(xì)帳簿,進(jìn)入新的會(huì)計(jì)期間 update 本期匯總帳簿 set 會(huì)計(jì)期間 = 會(huì)計(jì)期間 + 1, 期初余額=余額, 本期借方合計(jì) = 0, 本期貸方合計(jì) = 0, 余額 = 0 delete from 本期明細(xì)帳簿 新會(huì)計(jì)期間的科目余額表 insert into 科目余額表(科目代碼, 期初借方余額, 期初貸方余額, 本期借方發(fā)生額, 本期貸方發(fā)生額, 本年借方累計(jì)發(fā)生額, 本年貸方累計(jì)發(fā)生額, 本期借方余額, 本期貸方余額, 余額方向,會(huì)計(jì)期間) select 科目代碼, 本期借方余額, 本期貸方余額,0,0,0,0, 本期借方余額, 本期貸方余額, 余額方向,會(huì)計(jì)期間+1 from 科目余額表 where 會(huì)計(jì)期間 = kjqjmitGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate proc sf_計(jì)算資產(chǎn)負(fù)債表 kjqj intas begin tran 計(jì)算資產(chǎn)負(fù)債表,這里提供計(jì)算的方法供演示 沒(méi)有列出的明細(xì)項(xiàng)目都?xì)w到其他資產(chǎn)和其他負(fù)債里 注意,這里按照科目代碼統(tǒng)計(jì),主要說(shuō)明資產(chǎn)負(fù)債表的含意和計(jì)算方法 如果科目代碼不是這樣,改存儲(chǔ)過(guò)程需要作相應(yīng)的修改 delete from 資產(chǎn)負(fù)債表 where 會(huì)計(jì)期間 = kjqj insert into 資產(chǎn)負(fù)債表(會(huì)計(jì)期間) values(kjqj) update 資產(chǎn)負(fù)債表 set 現(xiàn)金及現(xiàn)金等價(jià)物 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期借方余額,0)isnull(本期貸方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 101 and 科目代碼 = 111) as b where = kjqj update 資產(chǎn)負(fù)債表 set 應(yīng)收帳款 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期借方余額,0)isnull(本期貸方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 121 and 科目代碼 = 129 and 科目代碼125) as b where = kjqj 注意壞帳準(zhǔn)備為貸方 update 資產(chǎn)負(fù)債表 set 壞帳準(zhǔn)備 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 125) as b where = kjqj update 資產(chǎn)負(fù)債表 set 應(yīng)收帳款凈值 = 應(yīng)收帳款 壞帳準(zhǔn)備 update 資產(chǎn)負(fù)債表 set 流動(dòng)資產(chǎn)總計(jì) = 現(xiàn)金及現(xiàn)金等價(jià)物 + 應(yīng)收帳款凈值 update 資產(chǎn)負(fù)債表 set 固定資產(chǎn)原值 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期借方余額,0)isnull(本期貸方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 =171) as b where = kjqj 注意累計(jì)折舊為貸方 update 資產(chǎn)負(fù)債表 set 累計(jì)折舊 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 175) as b where = kjqj update 資產(chǎn)負(fù)債表 set 固定資產(chǎn)總計(jì) = 固定資產(chǎn)原值 累計(jì)折舊 update 資產(chǎn)負(fù)債表 set 其他資產(chǎn) = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期借方余額,0)isnull(本期貸方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 131 and 科目代碼 = 195 and 科目代碼171 and 科目代碼 175) as b where = kjqj update 資產(chǎn)負(fù)債表 set 資產(chǎn)總計(jì) = 流動(dòng)資產(chǎn)總計(jì) + 固定資產(chǎn)總計(jì) + 其他資產(chǎn) 計(jì)算負(fù)債及所有者權(quán)益 負(fù)債類(lèi)級(jí)別都是貸方金額 update 資產(chǎn)負(fù)債表 set 應(yīng)付帳款 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 201 and 科目代碼 = 204 ) as b where = kjqj update 資產(chǎn)負(fù)債表 set 預(yù)收帳款 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 206) as b where = kjqj update 資產(chǎn)負(fù)債表 set 應(yīng)付工資 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 215) as b where = kjqj update 資產(chǎn)負(fù)債表 set 其他負(fù)債 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 209 and 科目代碼 = 281 and 科目代碼 215) as b where = kjqj update 資產(chǎn)負(fù)債表 set 負(fù)債總計(jì) = 應(yīng)付帳款 + 預(yù)收帳款 + 應(yīng)付工資 + 其他負(fù)債 計(jì)算所有者權(quán)益 update 資產(chǎn)負(fù)債表 set 實(shí)收資本 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額 from 科目余額表 where 會(huì)計(jì)期間 = kjqj and 科目代碼 = 301) as b where = kjqj update 資產(chǎn)負(fù)債表 set 資本公積 = from 資產(chǎn)負(fù)債表 as a, (select sum(isnull(本期貸方余額,0)isnull(本期借方余額,0)) as 金額