明细分类账SQL取数原创
35人赞赏了该文章
346次浏览
编辑于2024年07月23日 13:59:08
明细分类账的取数来源主要是凭证,如果需要显示核算维度的话,就还需要从对应维度的数据表中取数。
1,不带维度的SQL明细查询:如果不涉及核算维度,则SQL比较简单:
--查询不包含核算维度条件下的科目明细发生额 SELECT ABL.FNAME FBOOKNAME,V.FYEAR,FPERIOD,VP.FNAME FVOUCHERGROUPNAME,FVOUCHERGROUPNO,FENTRYSEQ, A.FNUMBER,AL.FNAME FACCOUNTNAME,FDETAILID,CL.FNAME, VE.FAMOUNTFOR,VE.FDEBIT,VE.FCREDIT FROM T_GL_VOUCHERENTRY VE JOIN T_GL_VOUCHER V ON V.FVOUCHERID = VE.FVOUCHERID JOIN T_BD_ACCOUNTBOOK AB ON AB.FBOOKID=V.FACCOUNTBOOKID LEFT JOIN T_BD_ACCOUNTBOOK_L ABL ON ABL.FBOOKID=V.FACCOUNTBOOKID AND ABL.FLOCALEID=2052 JOIN T_BD_ACCOUNT A ON A.FACCTID=VE.FACCOUNTID LEFT JOIN T_BD_ACCOUNT_L AL ON AL.FACCTID=A.FACCTID AND AL.FLOCALEID=2052 LEFT JOIN T_BD_VOUCHERGROUP_L VP ON VP.FVCHGROUPID=V.FVOUCHERGROUPID AND VP.FLOCALEID=2052 LEFT JOIN T_BD_CURRENCY_L CL ON CL.FCURRENCYID=VE.FCURRENCYID AND CL.FLOCALEID=2052 WHERE V.FDOCUMENTSTATUS<>'Z' AND V.FINVALID='0' AND V.FPOSTED='1' --已过账的正式凭证 AND AB.FNUMBER='001' AND V.FYEAR=2018 AND V.FPERIOD=1 AND A.FNUMBER='2238' AND VE.FDETAILID>0 AND VE.FCURRENCYID=1 ORDER BY AB.FNUMBER,V.FYEAR, V.FPERIOD,VP.FNAME,V.FVOUCHERGROUPNO,A.FNUMBER,VE.FENTRYSEQ
结果如下图所示:
2,带维度解析的SQL明细查询:但实现查询时,通常都需要包含指定核算维度来查询,因此,当查询带核算维度的明细账数据时,首先需要查询到相关维度的参数信息,主要包含该核算维度对应的维度标识列名,物理表名,主键字段名,编码字段名等。可以用以下SQL脚本来查询:
--查询核算维度参数信息 SELECT FID,FFLEXNUMBER,FFORMID,TM.FNAME,FTABLENAME,FSTRATEGYTYPE, CASE FSTRATEGYTYPE WHEN 1 THEN '共享' WHEN 2 THEN '分配' WHEN 3 THEN '私有' ELSE 'UNKNOW' END FSTRATEGYTYPENAME, FMASTERIDFIELDNAME,FNUMBERFIELDNAME,FNAMEFIELDNAME,FORGFIELDNAME,FPKFIELDTYPE,FPKFIELDNAME,FNAMEISLOCALE FROM ( SELECT C.FID,C.FFLEXNUMBER,A.FFORMID,CL.FNAME, ISNULL(A.FTABLENAME,' ') FTABLENAME, ISNULL(BT.FSTRATEGYTYPE,1) FSTRATEGYTYPE, CASE WHEN LEN(ISNULL(A.FMASTERIDFIELDNAME,' ')) <= 1 THEN A.FPKFIELDNAME ELSE A.FMASTERIDFIELDNAME END FMASTERIDFIELDNAME, A.FNUMBERFIELDNAME, A.FNAMEFIELDNAME,A.FORGFIELDNAME, A.FPKFIELDTYPE, A.FPKFIELDNAME,A.FNAMEISLOCALE FROM T_META_LOOKUPCLASS A LEFT JOIN T_BD_FLEXITEMPROPERTY C ON A.FFORMID=C.FVALUESOURCE LEFT JOIN T_BD_FLEXITEMPROPERTY_L CL ON CL.FID=C.FID AND CL.FLOCALEID=2052 LEFT JOIN T_META_BASEDATATYPE BT ON BT.FBASEDATATYPEID=C.FVALUESOURCE WHERE C.FVALUETYPE='0' AND C.FDOCUMENTSTATUS='C' UNION ALL SELECT DISTINCT C.FID,C.FFLEXNUMBER,C.FVALUESOURCE FFORMID,CL.FNAME, 'T_BAS_ASSISTANTDATAENTRY' FTABLENAME, 1 FSTRATEGYTYPE, 'FMASTERID' FMASTERIDFIELDNAME, 'FNUMBER' FNUMBERFIELDNAME, 'FDATAVALUE'FNAMEFIELDNAME,'FUSEORGID' FORGFIELDNAME, 2 FPKFIELDTYPE, 'FENTRYID' FPKFIELDNAME,1 FNAMEISLOCALE FROM T_BD_FLEXITEMPROPERTY C LEFT JOIN T_BD_FLEXITEMPROPERTY_L CL ON CL.FID=C.FID AND CL.FLOCALEID=2052 WHERE C.FVALUETYPE='1' AND C.FDOCUMENTSTATUS='C' ) TM ORDER BY FID
结果如下图所示:
有了这些信息,就可以在上面凭证查询的SQL中添加核算维度查询信息,关联对应的表和字段了。下面以组合维度部门和客户为条件,示例如下:
--查询维度组合条件下的科目明细发生额 SELECT ABL.FNAME FBOOKNAME,V.FYEAR,FPERIOD,VP.FNAME FVOUCHERGROUPNAME,FVOUCHERGROUPNO,FENTRYSEQ, A.FNUMBER,AL.FNAME FACCOUNTNAME,FDETAILID,CL.FNAME, T1.FNUMBER,T2.FNUMBER, --核算维度编码字段 VE.FAMOUNTFOR,VE.FDEBIT,VE.FCREDIT FROM T_GL_VOUCHERENTRY VE JOIN T_GL_VOUCHER V ON V.FVOUCHERID = VE.FVOUCHERID JOIN T_BD_ACCOUNTBOOK AB ON AB.FBOOKID=V.FACCOUNTBOOKID LEFT JOIN T_BD_ACCOUNTBOOK_L ABL ON ABL.FBOOKID=V.FACCOUNTBOOKID AND ABL.FLOCALEID=2052 JOIN T_BD_ACCOUNT A ON A.FACCTID=VE.FACCOUNTID LEFT JOIN T_BD_ACCOUNT_L AL ON AL.FACCTID=A.FACCTID AND AL.FLOCALEID=2052 LEFT JOIN T_BD_VOUCHERGROUP_L VP ON VP.FVCHGROUPID=V.FVOUCHERGROUPID AND VP.FLOCALEID=2052 LEFT JOIN T_BD_CURRENCY_L CL ON CL.FCURRENCYID=VE.FCURRENCYID AND CL.FLOCALEID=2052 LEFT JOIN T_BD_FLEXITEMDETAILV DV ON DV.FID=VE.FDETAILID LEFT JOIN T_BD_DEPARTMENT T1 ON DV.FFLEX5=T1.FDEPTID --部门维度 LEFT JOIN T_BD_CUSTOMER T2 ON DV.FFLEX6=T2.FCUSTID --客户维度 WHERE V.FDOCUMENTSTATUS<>'Z' AND V.FINVALID='0' AND V.FPOSTED='1' --已过账的正式凭证 AND AB.FNUMBER='001' AND V.FYEAR=2018 AND V.FPERIOD=1 AND A.FNUMBER='2238' AND VE.FDETAILID>0 AND VE.FCURRENCYID=1 --核算维度条件 AND T1.FNUMBER >='BM000001' AND T2.FNUMBER >='00001' ORDER BY AB.FNUMBER,V.FYEAR, V.FPERIOD,VP.FNAME,V.FVOUCHERGROUPNO,A.FNUMBER,VE.FENTRYSEQ
结果如下图所示:
3,带科目余额的SQL明细查询:值得注意的是,上述SQL均不包含余额的计算,如果需要显示余额,则必须从科目余额表取数,这个相对复杂很多,可以参考以下示例:
SELECT B.FNUMBER,T.FYEAR,T.FPERIOD,A.FNUMBER,AL.FFULLNAME,T.FDETAILID,CL.FNAME FCURRENCYID, VL.FNAME FVCHGROUP,T.FVOUCHERGROUPNO,T.FENTRYSEQ,T.FDATE, T.FBEGINBALANCE,T.FDEBIT,T.FCREDIT,T.FENDBALANCE,T.FINDEX FROM( SELECT B.FACCOUNTBOOKID,B.FYEAR,B.FPERIOD,B.FACCOUNTID,B.FDETAILID,B.FCURRENCYID, 0 FVOUCHERGROUPID,0 FVOUCHERGROUPNO,0 FENTRYSEQ,'' FDATE, B.FBEGINBALANCE,B.FDEBIT,B.FCREDIT,B.FENDBALANCE,1 FINDEX FROM T_GL_BALANCE B WHERE B.FACCOUNTBOOKID=697382 AND B.FYEAR=2014 AND B.FPERIOD=2 AND B.FCURRENCYID<>0 UNION SELECT V.FACCOUNTBOOKID,V.FYEAR,V.FPERIOD,VE.FACCOUNTID,VE.FDETAILID,VE.FCURRENCYID, V.FVOUCHERGROUPID,V.FVOUCHERGROUPNO,VE.FENTRYSEQ,V.FDATE,0,VE.FDEBIT,VE.FCREDIT,0,0 FINDEX FROM T_GL_VOUCHER V JOIN T_GL_VOUCHERENTRY VE ON V.FVOUCHERID=VE.FVOUCHERID AND V.FPOSTED='1' AND V.FINVALID='0' WHERE V.FACCOUNTBOOKID=697382 AND V.FYEAR=2014 AND V.FPERIOD=2 AND VE.FCURRENCYID=1 ) T INNER JOIN T_BD_ACCOUNT A ON A.FACCTID=T.FACCOUNTID LEFT JOIN T_BD_ACCOUNTBOOK B ON B.FBOOKID=T.FACCOUNTBOOKID LEFT JOIN T_BD_CURRENCY_L CL ON CL.FCURRENCYID=T.FCURRENCYID AND CL.FLOCALEID=2052 LEFT JOIN T_BD_VOUCHERGROUP_L VL ON VL.FVCHGROUPID=T.FVOUCHERGROUPID AND VL.FLOCALEID=2052 LEFT JOIN T_BD_ACCOUNT_L AL ON AL.FACCTID=T.FACCOUNTID AND AL.FLOCALEID=2052 WHERE A.FNUMBER LIKE '1812%' ORDER BY B.FNUMBER,T.FYEAR,T.FPERIOD,A.FNUMBER,T.FDETAILID,T.FCURRENCYID ,T.FINDEX DESC, T.FVOUCHERGROUPID,T.FVOUCHERGROUPNO,T.FENTRYSEQ,T.FDATE
注意:上图中,FINDEX为0的是明细上的明细分录行数据,为1的是余额表中对这些明细行的汇总,同时,还包含了核算维度组合ID和汇总。由于这里取的币别都是1(人民币)因此,本次查询未体现各币别汇总为综合本位币的关系。本示例未解析核算维度组合ID(FDETAILID),可以参考前面的示例自行解析具体维度值。
PS:余额表保存的是已过账凭证的数据,如需查询包含未过账凭证的余额数据,则需要调用官方提供的虚拟过账方法,暂时无法直接通过SQL语句取到数,详细请参考:【二开指导】如何取科目余额数据 (kingdee.com) 及 总账凭证虚拟过账参数说明 (kingdee.com)
推荐阅读