是否有明细科目标记(jc_flag)字段,jc_flag=true表示该总账科目无明细科目,jc_flag=false表示该总账科目有明细科目。
(2)数据处理
a.查询生成借方分录
查询生成bm1pd01的借方分录表:用bm1pd01的“km_one”字段连接会计科目表(bm1kj01)的“scm”字段,合并“year”、“month”、“day”生成“凭证日期”字段,同时增加表示借贷方向的字段“借贷”并赋值“1”,设置筛选条件
bm1pd01.year=2004 and bm1kj01.jc_flag=true
具体sql处理语句为:
(sql3)
select ‘1’ as 借贷,a.pdh as 凭单号,trim(str(a.year))+‘-’+ trim(str(a.month))+‘-’+trim(str(a.day)) as 凭证日期,a.fdj as 单据数,a.zy as 摘要,a.val as 金额,b.kj_kmh as 会计科目 from bm1pd01 as a inner join bm1kj01 as b on a.km_one=b.scm where a.year=2004 and b.jc_flag=true
b.查询生成贷方分录
查询生成bm1pd01的贷方分录表:用bm1pd01的“km_two”字段连接会计科目表(bm1kj01)的“scm”字段,合并“year”、“month”、“day”生成“凭证日期”字段,同时增加表示借贷方向的字段“借贷”并赋值“-1”,设置筛选条件bm1pd01.year=2004 and bm1kj01.jc_flag=true
具体sql处理语句为:
(sql4)
select ‘-1’ as 借贷,a.pdh as 凭单号,trim(str(a.year))+‘-’+ trim(str(a.month))+‘-’+trim(str(a.day)) as 凭证日期,a.fdj as 单据数,a.zy as 摘要,a.val as 金额,b.kj_kmh as 会计科目 from bm1pd01 as a inner join bm1kj01 as b on a.km_two=b.scm where a.year=2004 and b.jc_flag=true
2.bm1pd02处理
分析发现,bm1pd02存储明细科目借方分录,记录结构如表三。
(表三)
flm分录源码 scm科目源码 val金额 zy摘要 236 1199 90000
查询生成明细科目借方分录表:用bm1pd02 的“flm”字段连接bm1pd01的“flm”字段、bm1pd02的“scm”字段连接bm1kj01的“scm”字段查询生成“凭单号”、“凭证日期”、“附单据”、“会计科目”等字段,增加表示借贷方向的字段“借贷”并赋值“1”,设置筛选条件bm1pd01.year=2004,具体sql处理语句为:
(sql5)
select ‘1’ as 借贷,bm1pd01.pdh as 凭单号,trim(str(bm1pd01.
year))+‘-’+trim(str(bm1pd01.month))+‘-’+trim(str(bm1pd01.day)) as 凭证日期,bm1pd01.fdj as 单据数,bm1pd02.zy as 摘要,bm1pd02.val as 金额,bm1kj01.kj_kmh as 会计科目 from bm1pd01 inner join (bm1kj01 inner join bm1pd02 on bm1kj01.scm = bm1pd02.scm) on bm1pd01.flm = bm1pd02.flm where bm1pd01.year=2004
3.bm1pd03处理
bm1pd03存储明细科目贷方分录,数据处理方法与bm1pd02类似,借贷方向字段“借贷”赋值“-1”,具体sql处理语句为:
(sql6)
select ‘-1’ as 借贷,bm1pd01.pdh as 凭单号,trim(str(bm1pd01.
year))+‘-’+trim(str(bm1pd01.month))+‘-’+trim(str(bm1pd01.day)) as 凭证日期,bm1pd01.fdj as 单据数,bm1pd03.zy as 摘要,bm1pd03.val as 金额,bm1kj01.kj_kmh as 会计科目 from bm1pd01 inner join (bm1kj01 inner join bm1pd03 on bm1kj01.scm = bm1pd03.scm) on bm1pd01.flm = bm1pd03.flm where bm1pd01.year=2004
4.生成完整凭证表
合并sql3、sql4、sql5、sql6生成完整凭证表,合并关键字为union.即:
(sql7)
sql3 union sql4 union sql5 union sql6
其中sql3、sql4、sql5、sql6为上述具体sql语句。
三、在ao中转换数据并制作数据库数据导入模板
(一)转换数据
1.通过ao系统“项目管理”→“资料管理”→“从系统引入资料”直接引入获取的“总会计2000”access数据库数据。
2.生成财务数据中
上一页 [1] [2] [3] 下一页