检查凭证上录入的核算维度和对应科目上挂的核算维度是...

/* 该脚本功能：一次性检查凭证上录入的核算维度和对应科目上挂的核算维度是否一致。
包括以下几种有问题的情况：
1，必录维度未录入（少录或未录）
2，科目未设置核算维度但录入的维度（多录）
3，录入了科目上未设置的核算维度（多录）
注：本脚本仅适用于增加了自定义核算维度的情况，对于未新增自定义核算维度的情况，需要将脚本中@SQL2相关的部分脚本删除
*/
DECLARE @SQL1 VARCHAR(8000),@SQL2 VARCHAR(8000)
SET @SQL1=''
SET @SQL2=''
SELECT @SQL1=@SQL1+','+ FFLEXNUMBER FROM T_BD_FLEXITEMPROPERTY WHERE FDOCUMENTSTATUS='C' AND FFORBIDSTATUS='A' AND FVALUETYPE=0
SET @SQL1=SUBSTRING(@SQL1,2,LEN(@SQL1)-1)
SELECT @SQL2=@SQL2+','+ FFLEXNUMBER FROM T_BD_FLEXITEMPROPERTY WHERE FDOCUMENTSTATUS='C' AND FFORBIDSTATUS='A' AND FVALUETYPE=1
SET @SQL2=SUBSTRING(@SQL2,2,LEN(@SQL2)-1)
SET @SQL1= '
SELECT FID,FFLEXNUMBER,FFLEXVALUE INTO TM_FLEX FROM (
SELECT P1.FID,P1.FFLEXNUMBER,CONVERT(VARCHAR(800),P1.FFLEXVALUE) FFLEXVALUE
FROM ( SELECT * FROM T_BD_FLEXITEMDETAILV )T1
UNPIVOT ( FFLEXVALUE FOR FFLEXNUMBER IN (' + @SQL1+ ' ) ) P1
UNION ALL
SELECT P2.FID,P2.FFLEXNUMBER,P2.FFLEXVALUE
FROM ( SELECT * FROM T_BD_FLEXITEMDETAILV )T2
UNPIVOT (FFLEXVALUE FOR FFLEXNUMBER IN (' + @SQL2+ ' )) P2
)
TM WHERE FFLEXVALUE<>''0'' AND LTRIM(FFLEXVALUE)<>''''
'
--过程中用到的临时表
IF EXISTS ( SELECT * FROM SYSOBJECTS WHERE NAME = 'TM_FLEX' AND TYPE = 'U') DROP TABLE TM_FLEX
EXEC( @SQL1);
SELECT BL.FNAME 账簿,V.FYEAR 年,V.FPERIOD 期间,VL.FNAME 字,V.FVOUCHERGROUPNO 号,TM.FENTRYSEQ 分录行,TM.FNUMBER 科目编码, '必录维度['+PL.FNAME+']未录入' 问题描述
FROM T_GL_VOUCHER V
JOIN (
SELECT VE.FVOUCHERID,VE.FENTRYSEQ, A.FNUMBER,A.FACCTID,AE.FDATAFIELDNAME FROM T_GL_VOUCHERENTRY VE
JOIN T_BD_ACCOUNT A ON VE.FACCOUNTID=A.FACCTID
JOIN T_BD_ACCOUNTFLEXENTRY AE ON AE.FACCTID=A.FACCTID
LEFT JOIN TM_FLEX DV ON VE.FDETAILID= DV.FID AND AE.FDATAFIELDNAME=DV.FFLEXNUMBER
WHERE AE.FINPUTTYPE=1 AND DV.FFLEXVALUE IS NULL
) TM ON TM.FVOUCHERID=V.FVOUCHERID
LEFT JOIN T_BD_ACCOUNTBOOK_L BL ON BL.FBOOKID=V.FACCOUNTBOOKID AND BL.FLOCALEID=2052
LEFT JOIN T_BD_VOUCHERGROUP_L VL ON VL.FVCHGROUPID=V.FVOUCHERGROUPID AND VL.FLOCALEID=2052
JOIN T_BD_FLEXITEMPROPERTY P ON P.FFLEXNUMBER=TM.FDATAFIELDNAME
LEFT JOIN T_BD_FLEXITEMPROPERTY_L PL ON PL.FPKID=P.FID
UNION ALL
SELECT BL.FNAME 账簿,V.FYEAR 年,V.FPERIOD 期间,VL.FNAME 字,V.FVOUCHERGROUPNO 号,TM.FENTRYSEQ 分录行,TM.FNUMBER 科目编码, '录入了未设置的['+PL.FNAME+']核算维度' 问题描述 FROM T_GL_VOUCHER V
JOIN (
SELECT VE.FVOUCHERID,VE.FENTRYSEQ, A.FNUMBER,A.FACCTID,DV.FFLEXNUMBER FDATAFIELDNAME FROM T_GL_VOUCHERENTRY VE
JOIN T_BD_ACCOUNT A ON VE.FACCOUNTID=A.FACCTID
JOIN T_BD_ACCOUNTFLEXENTRY AE ON AE.FACCTID=A.FACCTID
JOIN TM_FLEX DV ON DV.FID=VE.FDETAILID AND DV.FFLEXNUMBER<>AE.FDATAFIELDNAME
WHERE NOT EXISTS(
SELECT 1 FROM (
SELECT VE.FVOUCHERID,VE.FENTRYSEQ, AE.FDATAFIELDNAME FROM T_GL_VOUCHERENTRY VE
JOIN T_BD_ACCOUNT A ON VE.FACCOUNTID=A.FACCTID
JOIN T_BD_ACCOUNTFLEXENTRY AE ON AE.FACCTID=A.FACCTID) T1
WHERE T1.FVOUCHERID=VE.FVOUCHERID AND T1.FENTRYSEQ=VE.FENTRYSEQ AND T1.FDATAFIELDNAME=DV.FFLEXNUMBER
)
) TM ON TM.FVOUCHERID=V.FVOUCHERID
LEFT JOIN T_BD_ACCOUNTBOOK_L BL ON BL.FBOOKID=V.FACCOUNTBOOKID AND BL.FLOCALEID=2052
LEFT JOIN T_BD_VOUCHERGROUP_L VL ON VL.FVCHGROUPID=V.FVOUCHERGROUPID AND VL.FLOCALEID=2052
JOIN T_BD_FLEXITEMPROPERTY P ON P.FFLEXNUMBER=TM.FDATAFIELDNAME
LEFT JOIN T_BD_FLEXITEMPROPERTY_L PL ON PL.FPKID=P.FID
UNION ALL
SELECT BL.FNAME 账簿,V.FYEAR 年,V.FPERIOD 期间,VL.FNAME 字,V.FVOUCHERGROUPNO 号,TM.FENTRYSEQ 分录行,TM.FNUMBER 科目编码, '未设置任何核算维度但录入了维度' 问题描述
FROM T_GL_VOUCHER V
JOIN (
SELECT VE.FVOUCHERID,VE.FENTRYSEQ, A.FNUMBER,A.FACCTID,AE.FDATAFIELDNAME FROM T_GL_VOUCHERENTRY VE
JOIN T_BD_ACCOUNT A ON VE.FACCOUNTID=A.FACCTID
LEFT JOIN T_BD_ACCOUNTFLEXENTRY AE ON AE.FACCTID=A.FACCTID
WHERE FDATAFIELDNAME IS NULL AND FDETAILID>0
) TM ON TM.FVOUCHERID=V.FVOUCHERID
LEFT JOIN T_BD_ACCOUNTBOOK_L BL ON BL.FBOOKID=V.FACCOUNTBOOKID AND BL.FLOCALEID=2052
LEFT JOIN T_BD_VOUCHERGROUP_L VL ON VL.FVCHGROUPID=V.FVOUCHERGROUPID AND VL.FLOCALEID=2052
JOIN T_BD_FLEXITEMPROPERTY P ON P.FFLEXNUMBER=TM.FDATAFIELDNAME
LEFT JOIN T_BD_FLEXITEMPROPERTY_L PL ON PL.FPKID=P.FID;