总账常用的查询SQL原创
金蝶云社区-欧惠玲
欧惠玲
22人赞赏了该文章 2,579次浏览 未经作者许可,禁止转载编辑于2023年02月21日 17:02:47
summary-icon摘要由AI智能服务提供

本文介绍了财务系统中不同模块的数据查询操作,包括凭证查询(凭证记录、分录记录、辅助账记录、成本中心引用)、现金流量查询、往来核算(核销记录、挂账记录)以及余额表查询(科目余额、辅助账余额、客户/供应商余额)。每种查询均通过SQL语句实现,涉及多个数据表之间的关联查询,通过特定的条件(如期间编码、公司编码、凭证号等)来筛选所需的数据记录。

1、凭证

--查询某张凭证记录

select v.* from t_gl_voucher v 

inner join t_bd_period p on p.fid=v.fperiodid 

inner join T_BD_VoucherTypes vt on v.FVoucherTypeID=vt.fid

inner join t_org_company c on c.fid=v.fcompanyid 

where p.fnumber='期间编码'and c.fnumber='公司编码'and vt.fnumber='凭证类型编码' and v.fnumber='凭证号'


--查询某张凭证的对应分录记录

select  ve.* from t_gl_voucher v 

inner join t_bd_period p on p.fid=v.fperiodid 

inner join t_org_company c on c.fid=v.fcompanyid

inner join T_BD_VoucherTypes vt on v.FVoucherTypeID=vt.fid

inner join t_gl_voucherentry ve on ve.fbillid=v.fid

inner join t_bd_accountview av on ve.faccountid=av.fid 

where p.fnumber='期间编码'and c.fnumber='公司编码' and vt.fnumber='凭证类型编码' and v.fnumber='凭证号' and  av.fnumber='科目编码'  and ve.fseq='分录号'


--查询某张凭证的辅助账记录

select  vs.* from t_gl_voucher v 

inner join t_bd_period p on p.fid=v.fperiodid 

inner join t_org_company c on c.fid=v.fcompanyid

inner join T_BD_VoucherTypes vt on v.FVoucherTypeID=vt.fid

inner join t_gl_voucherentry ve on ve.fbillid=v.fid

inner join t_bd_accountview av on ve.faccountid=av.fid

inner join t_gl_voucherassistrecord vs on vs.fentryid=ve.fid 

where p.fnumber='期间编码'and c.fnumber='公司编码' and vt.fnumber='凭证类型编码' and v.fnumber='凭证号' and  av.fnumber='科目编码'  and ve.fseq='分录号'


--查询某个成本中心被哪些公司哪些期间的凭证所引用

select   distinct  c.fnumber 公司编码,c.FNAME_L2 公司名称,  p.fnumber 期间编码,vt.fnumber 凭证类型编码,v.fnumber 凭证号
from t_gl_voucher v
inner join t_gl_voucherentry ve on ve.fbillid=v.fid

inner join t_gl_voucherassistrecord vs on vs.fentryid=ve.fid

inner join T_BD_VoucherTypes vt on v.FVoucherTypeID=vt.fid

inner join t_bd_period p on p.fid=v.fperiodid 

inner join t_org_company c on c.fid=v.fcompanyid

inner join  t_bd_assistanthg hg on hg.fid=vs.fassgrpid
inner join T_ORG_CostCenter cc on hg.FCostOrgID=cc.fid

where cc.Fnumber='成本中心编码'


2、现金流量

--查询对应凭证的现金流量记录

select cf.* from t_gl_voucher v

inner join T_BD_VoucherTypes vt on v.FVoucherTypeID=vt.fid

inner join t_bd_period p on p.fid=v.fperiodid 

inner join t_org_company c on c.fid=v.fcompanyid

inner  join  t_gl_cashflowrecord cf on cf.fvoucherid=v.fid 

where p.fnumber='期间编码'and c.fnumber='公司编码' and vt.fnumber='凭证类型编码' and v.fnumber='凭证号'


3、往来核算

--查询凭证的核销记录

select av.* from t_gl_voucher v 

inner join T_BD_VoucherTypes vt on v.FVoucherTypeID=vt.fid

inner join t_gl_voucherentry ve on ve.fbillid=v.fid 

inner join t_gl_voucherassistrecord vs on vs.fentryid=ve.fid 

inner join t_bd_period p on p.fid=v.fperiodid 

inner join t_org_company c on c.fid=v.fcompanyid 

inner join t_gl_acctverify av on av.FVCHASSISTRECORDID=vs.fid 

where p.fnumber='期间编码'and c.fnumber='公司编码' and vt.fnumber='凭证类型编码' and v.fnumber='凭证号' and ve.seq='分录号' and vs.fseq='辅助账行号'


--查询凭证的挂账记录

select ac.* from t_gl_voucher v 

inner join T_BD_VoucherTypes vt on v.FVoucherTypeID=vt.fid

inner join t_gl_voucherentry ve on ve.fbillid=v.fid 

inner join t_gl_voucherassistrecord vs on vs.fentryid=ve.fid 

inner join t_bd_period p on p.fid=v.fperiodid 

inner join t_org_company c on c.fid=v.fcompanyid 

inner join t_gl_acctcussent ac  on ac.FVCHASSISTRECORDID=vs.fid 

where p.fnumber='期间编码'and c.fnumber='公司编码' and vt.fnumber='凭证类型编码' and v.fnumber='凭证号' and ve.seq='分录号' and vs.fseq='辅助账行号'


4、余额表

--查询某个科目某个期间的科目余额记录(以科目余额(包含未过账本位币)为例,其余科目余额表的表名请参考此贴:总账的相关表

select  ab1.* from  t_gl_accountbalance_lf ab1

inner join T_ORG_COMPANY  c on ab1.FORGUNITID =c.fid

inner join T_BD_ACCOUNTVIEW av ON AB1.FACCOUNTID = AV.FID

inner join T_BD_AccountTable at on av.FAccountTableID=at.fid

where   c.FNUMBER='公司编码' and ab1.fperiod='期间编码' and at.fnumber='科目表编码' and av.fnumber='科目编码'



--查询某个科目某个期间的辅助账余额记录(以辅助账余额(包含未过账原币)为例,其余辅助账余额表的表名请参考此贴:总账的相关表

select  ab1.* from  T_GL_ASSISTBALANCE_1F ab1

inner join T_ORG_COMPANY  c on ab1.FORGUNITID =c.fid

inner join T_BD_ACCOUNTVIEW av ON AB1.FACCOUNTID = AV.FID

inner join T_BD_AccountTable at on av.FAccountTableID=at.fid

where   c.FNUMBER='公司编码' and ab1.fperiod='期间编码' and at.fnumber='科目表编码' and av.fnumber='科目编码'


--查询某些客户在集团内所有公司的当前期间核算项目余额表(包含未过账凭证)是否存在余额(当前期间指的是系统状态控制里的总账当前期间)

select distinct cp.FNUMBER 公司编码,p.FNUMBER  期间编码,av.FNUMBER 科目编码,c.FNUMBER  客户编码,ab.FENDBALANCEFOR 期末余额  from T_GL_AssistBalance_1F ab

inner join T_BD_SYSTEMSTATUSCTROL ssc on ab.FORGUNITID =ssc.fcompanyid

inner join T_BD_SYSTEMSTATUS ss on ssc.FSYSTEMSTATUSID =ss.fid

inner join T_BD_assistanthg hg  ON AB.fassistgrpid = hg.fid

inner join T_BD_Customer c on hg.FCustomerID=c.fid

inner join T_BD_PERIOD  p on ssc.FCURRENTPERIODID =p.fid

inner join t_org_company cp on ab.FORGUNITID =cp.fid

inner join t_bd_accountview av ON AB.FACCOUNTID = AV.FID

where ss.FNAME =6 and ssc.FISSTART =1 and ab.FPERIOD =p.fnumber and ab.FENDBALANCEFOR <>0 and c.fnumber in('客户编码1','客户编码2','客户编码3')


--查询某些供应商在集团内所有公司的当前期间核算项目余额表(包含未过账凭证)是否存在余额(当前期间指的是系统状态控制里的总账当前期间)

select distinct cp.FNUMBER 公司编码,p.FNUMBER  期间编码,av.FNUMBER 科目编码,s.FNUMBER  供应商编码,ab.FENDBALANCEFOR 期末余额  from T_GL_AssistBalance_1F ab

inner join T_BD_SYSTEMSTATUSCTROL ssc on ab.FORGUNITID =ssc.fcompanyid

inner join T_BD_SYSTEMSTATUS ss on ssc.FSYSTEMSTATUSID =ss.fid

inner join T_BD_assistanthg hg  ON AB.fassistgrpid = hg.fid

inner join T_BD_Supplier  s on hg.FProviderID=s.fid

inner join T_BD_PERIOD  p on ssc.FCURRENTPERIODID =p.fid

inner join t_org_company cp on ab.FORGUNITID =cp.fid

inner join t_bd_accountview av ON AB.FACCOUNTID = AV.FID

where ss.FNAME =6 and ssc.FISSTART =1 and ab.FPERIOD =p.fnumber and ab.FENDBALANCEFOR <>0 and s.fnumber in ('供应商编码1','供应商编码2','供应商编码3')



图标赞 22
22人点赞
还没有人点赞,快来当第一个点赞的人吧!
图标打赏
0人打赏
还没有人打赏,快来当第一个打赏的人吧!