总账常用的查询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_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='分录号'
--查询某个成本中心被哪些公司哪些期间的凭证所引用
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
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_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_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_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')
总账常用的查询SQL
本文2024-09-22 18:23:51发表“eas cloud知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-eas-100509.html
