巧用SQL语句将辅助核算代码转为科目代码实现辅助账数据采集_

1、巧用SQL语句将辅助核算代码转为科目代码实现辅助账数据采集7 _0 H* G2 D( v- f4 c* S, p: E- M% O( e0 E5 8 K( $ x j. F4 Q襄樊市审计局  崔纲# l6 b! H2 r8 E2 p: W( q- h4 H        目前，关于方正春元财政总预算数据的采集方法中大多只用到了凭证主表、凭证明细表、余额表、会计科目表，而该软件的会计科目表中只有一级和二级科目，有关预算支出分类以辅助核算形式存在“支出功能分类科目表”中，直接用系统的会计科目表采集转换数据得不到支出辅

2、助账。因此需要从支出功能分类表中提取编码和支出分类名称来扩充科目编码表，这样生成的财务账套中就会包含支出分类明细，能使审计人员分析预算支出时有更清晰、直观。本文简要介绍巧用SQL语句将总预算账中的辅助核算代码转为科目代码，进而实现辅助账数据采集的方法。9 Q' s# K6 B$ w4 ?5 g: 6 v% P* X9 0 t2 L6 W% o. ! . O( M* _        一、数据结构特点2 f8 h9 M" x        从获取的财务后台数据

3、来看，方正春元数据结构有以下特点：* p0 q/ ? |7 X, b4 o        （1）收支分类、往来、预算单位等信息采用辅助核算形式。各单位财务数据初使化方式不一样，后台数据结构也有区别，如有的单位往来是以科目代码形式存储，未采用辅助核算形式。3 N2 P( 1 U! e% 9 S        （2）一个数据库存储多年度预算内、外、集中支付及专项账套数据。如凭证明细表中存储多年度、多个账套的凭证，通过年度（set_year）和账套编码(set_code区分。年度字

4、段值即为会计年度，账套编码值一般预算内为“0001”、预算外为“0002”,如果出现其他账套编码即为其他财政专项账簿。6 r+ Z# w: u  Q; k. z! E% V        （3）数据表中用到的各类编码分内码（in_code）和外码(disp_code对照管理。内码是级别码，各级代码长度都为3-3-3-3形式，在后台数据运行时使用；外码的各级代码长度不一样，一般为3-2-2-2形式，方正春元软件前台输入格式为外码。9 f% ?& D" G2 D/ A! i! r% M( l( p!

5、m/ & s$ y" ?0 T1 y- F9 N M3 t: C# _/ l8 X* Z  _# _2 H0 a9 2 : # _% H: z6 E* b$         二、数据转换方法m/ " |/ G' 4 Y* M7 c2 W/ J.         数据转换是将采集到的财务数据在SQL Server 2000中通过SQL语句实现的。根据一般审计人员的习惯，笔者将辅助核算的形式转化为科目代码形式，以便浏览电子账

6、簿。如果不进行科目代码转换，则在AO中生成的账套仅为一级科目流水账，使用极不方便。转换科目代码的原则是，生成新科目编码一定要保持科目表、余额表和凭证表一致，否则数据出错将无法顺利实现账表重建。因各地数据结构会有差别，所以在转换数据时应灵活运用本文中SQL例句，使转换后的数据能够满足审计需要。0 R" R: c  r# V6 X+ _/ E6 z& B; x! 0 O+ t# s  P0 B. r, 6 ?: x3 , A        （一）数据字典# 3 8 w9 d4 P%

7、         在采集的数据中有两张非常重要的表，一张是table_manager表（业务表和基础表管理表），存储表的中文名的表，另一张是 field_manager表（业务表和基础数据表的字段管理表），存储各表的字段中文名及相关联的表及字段。这两张表对分析表结构及数据转换非常有帮助。- S$ f3 o2 E/ ?8 v% j6 O, Y+ F0 u& a4 q0 G2 n# / t# C/ I* W        （二）数据转换涉及的方正春元软件后台数据的七张表。

8、( J8 h- r( a0 a7 b/ L9 B  / FACCOUNTANT_SUBJECT      会计科目表8 U( " x  : v5 qBUDGET_SUBJECT          支出功能分类科目表 N" _, j2 Y4 O O& bBUDGET_SUBJECT_INCOME   收入分类科目表' r0 N8 b8 1 w2 a( D0 6 c0 tBUDGET_SUBJECT_IT

9、EM     支出经济分类科目表9 n5 & e$ ( q- e' GENTERPRISE              预算单位表# y0 j# E$ B5 F8 p( J$ UREMAIN_BUDGET           总预算会计账期初数表1 N, h5 M9 s( X# pVOUCHER_BUDGET      &#

10、160;   凭证主表& x2 I. Y2 _$ k6 X7 eVOUCHER_DETAIL_BUDGET   凭证明细表' 0 y+ h! Y$ j; J; ?0 c' 9 f7 M6 Q* Z% t' t* e6 ?( * Q& l( Z9 I8 a# v（三）数据转换后新生成四张表* n6 b2 l; h. yPZ                      凭证表! f* S# Y

11、, r; F3 f' SKMBMB                   科目编码表- G& A7 l' x3 G0 8 f8 0 5 pYEB                     余额表. z6 h4 K; ? wBMB          

12、          部门表  b, e5 B( q% Z  W7 L0 . v- A K1 l+ J- c! T& . D0 G! y" b+ J% h$ j. r" X' E9 I（四）数据转换过程+ 4 u- i! N5 S- J+ # 1、转换预算单位表$ y& G! P$ x& k/ |转换思路：通过设置年度条件，将某年的预算单位编码和预算单位名称筛选出来，生成新的预算单位表。/ Y8 R  p&

13、q9 " . i. G$ y参考语句：9 h$ u0 _. q* B! O# _* r5 s2 E7 Sselect eid as bmbm,NAME as bmmc0 r8 q- : M$ i7 |% winto bmb2 ' Q8 d+ r; i( Y, t- Mfrom dbo.ENTERPRISE( M. U  c+ V: _% b. _! X! Lwhere YEAR='2009': a: U; R( H t0 & % Y. I3 n |2转换凭证表1 B % B/ A4 q( （1）转换凭证明细表: l$ i0 a. |

14、! d  X   t转换思路：通过转换凭证表，生成新的收入科目、支出科目和往来科目，并在新生成的凭证明细表中保留预算单位编码，同时对年度和账套编码字段设定条件。+ g3 w* M3 y# Y5 L  T参考语句：! W/ t, q! C0 m/ _$ Wselect VOUCHER_ID lsh ,V_LINE  mxh,SORT_LINE flmxh,2 . M8 F/ j(        kmbm=casel2 m9 R5 n4 d# B3 A   &

15、#160;   when EN_CODE is not null and BS_CODE is not null and left(AS_CODE,3 in ('501','505'9 m/ v5 4 A# O- U* 3 l          then  AS_CODE+BS_CODE' N* M, x+ f8 Q8 t       when BS_CODE is not null and left(AS_CODE,3 in

16、 ('401','405'& 0 7 J* E( x* S0 n3 s& 8 T          then AS_CODE+BS_CODE& L$ # _! B3 K% m       else AS_CODE( I. L9 X" v3 w  2 R+ L       end& x: V0 d# 6 X  u8 L( G  

17、0;    , SUMMARY zy,DEB_MONEY jfje,CRE_MONEY dfje,3 l- _! D1 X- $ O* i       bmbm=case2 / h, a( 1 Z5 f8 ? D       when EN_CODE is not null and BS_CODE is not null3 M. J4 o, y i9 s!             then EN_CODE# a/ z

18、4 l! G: T$ k0 l       when  EN_CODE is not null and BS_CODE is  null9 g1 c  v$ J& |6 % B, c            then null4 V2 z! T, w |" w& d, L" c       else EN_CODE" P7 v

19、0; L0 f" + i          end' . h  X' O+ ainto pz_mx& d* 7 u& b- |5 2 p0 x( bfrom dbo.VOUCHER_DETAIL_BUDGET5 w+ * ( Y2   bwhere set_code='0001' and set_year='2009'7 Z. r4 j7 B# ( K  F; s9 j（2）转换凭证主表! r

20、/ a: R9 8 % Q% p3 O转换思路：转换凭证主表的同时合并凭证年、月、日三个字段，生成凭证日期字段，同时对年度和账套编码字段设定条件。% _  i" P" k7 q参考语句：; s5 v4 f0 _& d. oselect  VOUCHER_ID lsh,cast(cast(SET_YEAR as varchar(4+'-' B7 s+ s7 b; |' h4 B7 t         +cast(V_MONTH as varcha

21、r(2+'-'+cast(V_DAY as varchar(2as datetime  pzrq,VOUCHER_NO pzh,DEFINE_ID pzlb8 |4 I: 7 F; h3 C- h& z" into pz_zb6 9 Q+ w: b  L6 _( wfrom dbo.VOUCHER_BUDGET, ?7 W5 r6 ?$ gwhere set_code='0001' and set_year='2009'/ ?' h6 H' L! D. J（3）合并凭证主表

22、和凭证明细表' X1 X6 A( Z* B1 $ B9 o# _" a& r转换思路：将凭证主表和凭证明细表合并为一张凭证表，对于数据量大的单位可以缩短导入AO的时间。, G, & t6 $ Q参考语句：  _% d% - e1 + r3 Cselect a.lsh,b.pzh,b.pzrq,b.pzlb,a.mxh,a.flmxh,6 i* L: a2 D/ L5 U* D6 t  V5 : f       a.kmbm,a.zy,a.jfje,a.dfje,a.bmbm,c.b

23、mmc4 Z" F! v. r# e9 into pz/   Z% T+ W# A; F; E7 W, B% J8 pfrom pz_mx a left join pz_zb b on a.lsh=b.lsh left join% ?6 m  q5 z& y, h' m     bmb c on a.bmbm=c.bmbm  h8 M  I" V3 u3 e5 lorder by month(b.pzrq,b.pzh,a.mxh,a.flm

24、xh  u/ I9 F6 ; ?$ . z进行表连接，找到新生成的科目编码对应的科目名称。2 k+ j% B: k- R- Y5 ?; s5 Y9 s3、转换科目编码表（kmbmb）6 V* N# Q0 c  r1 % 3 M转换思路：襄阳区预算的收入和支出分类科目都存放在“支出分类科目表”一张表中。因此，要把凭证表（pz）中新生成的三级科目编码及对应的科目名称归入科目编码表中，并生成对应的二级科目编码和名称也归入科目编码表中。在转换时需要将新生成的凭证表（pz）和支出分类科目表（BUDGET_SUBJECT）进行表连接，以找到新生成的科目编码对应的科目

25、名称。! I4 I' Z5 r T( c- Y生成科目编码表' z& B# I! r& 9 I/ J; j" pselect in_code as kmbm,disp_name as kmmc into kmbmb" Y$ s" # 0 Z1 Q/ from accountant_subject . K% v 2 p' Wwhere set_year=2009 order by in_code8 n& C    r" A! MG3 P5 j" B6 B. g

26、  L& m生成二级科目代码表  O+ 9 U! ( ainsert into kmbmb3 Z9 U' ' s. p( zselect distinct left(kmbm,6,disp_name  2 b+ y5 N+ U/ m- k4 Ifrom (select distinct kmbm  from pz where len(kmbm>6 as a1 ,BUDGET_SUBJECT+ k- r9 Z0 v; d- ?" vwhere SUBSTRING(a1.kmbm

27、,4,3=in_code2 M+ p" w. K/ c2 o$ f* Cand BUDGET_SUBJECT.set_year='2009' 3 r* |* # k/ R* q6 e7 4 n( 8 E6 l0 |0 _/ G a9 G; a生成三级科目代码表( A S+ 8 u/ z. M8 Oinsert into kmbmb?* x( G% r2 1 S9 r: g$ yselect kmbm,disp_name  from (select distinct kmbm  from pz where len(kmbm>

28、6 as a1 ,BUDGET_SUBJECT& A7 M; F3 Z ' y  B( Qwhere right(a1.kmbm,6=in_code ' S c* c. v" + + m x5 xand BUDGET_SUBJECT.set_year='2009'- c/ 0 0 E( D1 G. p2 g5 o* a1 N% j3 K4、转换余额表8 m# H- C% 7 Z转换思路：在转换余额表的同时生成余额方向（yefx）字段。7 j4 q8 1 C+ C参考语句： - ( f" q% d; / r1 T&q

29、uot; T* select kmbm= AS_CODE,BALANCE ncye, F. j6 R2 N. L        yefx=case, i; . q( J8 c4 Y" v' K&         when left(AS_CODE,1 in('1','5' then '1'( n/ q3 E5 ?8 C, u8 Q; B1 D' p        when left(AS_CODE,1 in('2','3','4' then '0'0 s8 C4 |' A  h+ g          end + 3 p" f9 B3 P6 C; e* h4 Ainto yeb' j& # a' & : L$ 9 J, _/ bfrom dbo.REMAIN_BUDGE