鼎捷易飞ERP优化版存货对总账查询语句
鼎捷 易飞ERP 财务对账 存货对总账
优化说明:
(1)操作简便:直接复制以下SQL语句到查询分析器里使用;
(2)改动面少:只需对会计科目编号、年月做更改,即红色字段是需要做更改的部分;
(3)缩短时间:减少因寻找差异单据而耗费在几个excel表中核对的时间;
(4)简化操作:无需在前台导太多的报表来寻找差异,只需后台执行以下语句即可。
使用说明:
(1)适用于长期对账,基本设置都没有问题,单据都抛分录和凭证的客户;
(2)适用于已有一定查账经验的人员使用;
(3)关于查暂估部分,目前只分析进货暂估,委外暂估暂未考虑;
(4)建议查询语句时*好分开单独执行,以免混乱。
**********************************************************************
**步:先在易飞中跑库存明细表或进耗存统计表和科目余额表来确定总差异
**********************************************************************
**********************************************************************
第二步:执行以下SQL语句来统计出存货、分录和总账的数据,以确定差异信息
说明:@ACCOUNT表示要查的会计科目,@MONTH表示要查的年月
**********************************************************************
declare @ACCOUNT char(20) set @ACCOUNT='1403'
DECLARE @MONTH CHAR(6) SELECT @MONTH = '201205'
SELECT 单别,SUM(ISNULL(INV本期发生额,0)) AS INV本期发生额,SUM(ISNULL(AJS本期发生额,0)) AS AJS本期发生额,
sum(INV本期发生额-AJS本期发生额) as 抛分录差额 from(
--INV本期发生额
SELECT LA006 as 单别, SUM(LA013*LA005)as INV本期发生额, 0 as AJS本期发生额 FROM INVLA
INNER JOIN INVMB ON MB001=LA001 INNER JOIN
CMSMC ON MC001=LA009 LEFT JOIN INVMA ON MA002=MB005 WHERE SUBSTRING(LA004,1,6)=@MONTH
AND MA004 =@ACCOUNT AND MC004=1
GROUP BY LA006
union all
--AJS当期发生额
SELECT TB013 AS 单别,0 as INV本期发生额, SUM(TB007*TB004)as AJS本期发生额 FROM AJSTA
INNER JOIN AJSTB ON TA001=TB001 AND TA002=TB002
WHERE SUBSTRING(TA006,1,6)=@MONTH AND TB005 =@ACCOUNT
GROUP BY TB013) AS F
group BY 单别
--having sum(INV本期发生额-AJS本期发生额) <>0
order BY 单别
--ACT当期发生额
SELECT TA006 as 凭证来源码,SUM(TB007*TB004)as ACT本期发生额 FROM ACTTA INNER JOIN ACTTB ON TA001=TB001 AND TA002=TB002
WHERE SUBSTRING(ACTTA.TA003,1,6)=@MONTH AND ACTTA.TA010 = 'Y' AND ACTTB.TB005 =@ACCOUNT GROUP BY TA006
**********************************************************************
第三步:执行INV-AJS差异明细确定具体哪些单别单号的INVLA和分录不一致
说明:@ACCOUNT表示要查的会计科目,@MONTH表示要查的年月,
@DanBie表示要查明细的具体单别
**********************************************************************
declare @ACCOUNT char(20) set @ACCOUNT='1403'
DECLARE @MONTH CHAR(6) SELECT @MONTH = '201205'
DECLARE @DanBie CHAR(6) SELECT @DanBie =('1101')
SELECT 单别单号,SUM(ISNULL(INV本期发生额,0)) AS INV本期发生额,SUM(ISNULL(AJS本期发生额,0)) AS AJS本期发生额,
sum(INV本期发生额-AJS本期发生额) as 抛分录差额 from(
--INV本期发生额
SELECT LA006+'-'+LA007 AS 单别单号, SUM(LA013*LA005)as INV本期发生额, 0 as AJS本期发生额 FROM INVLA
INNER JOIN INVMB ON MB001=LA001 INNER JOIN
CMSMC ON MC001=LA009 LEFT JOIN INVMA ON MA002=MB005 WHERE SUBSTRING(LA004,1,6)=@MONTH
AND MA004 =@ACCOUNT AND MC004=1 AND LA006=@DanBie
GROUP BY LA006,LA007
union all
--AJS当期发生额
SELECT TB013+'-'+TB014 AS 单别单号,0 as INV本期发生额, SUM(TB007*TB004)as AJS本期发生额 FROM AJSTA
INNER JOIN AJSTB ON TA001=TB001 AND TA002=TB002
WHERE SUBSTRING(TA006,1,6)=@MONTH AND TB005 =@ACCOUNT AND TB013=@DanBie
GROUP BY TB013,TB014) AS F
group BY 单别单号
having sum(INV本期发生额-AJS本期发生额)<>0
order BY 单别单号
**********************************************************************
第四步:执行AJS-ACT差异明细确定具体哪些单别单号的分录和凭证不一致
说明:@ACCOUNT表示要查的会计科目,@MONTH表示要查的年月,
**********************************************************************
declare @ACCOUNT char(20) set @ACCOUNT='1403'
DECLARE @MONTH CHAR(6) SELECT @MONTH = '201205'
SELECT 单别,单号,来源单别单号,SUM(ISNULL(ACT本期发生额,0))as 凭证发生额,SUM(ISNULL(AJS本期发生额,0)) as 分录发生额,SUM(ISNULL((ACT本期发生额-AJS本期发生额),0)) as 抛凭证差异 FROM (
SELECT TA004 as 单别,TA005 AS 单号,TB013+'-'+TB014 AS 来源单别单号,0 as ACT本期发生额, SUM(TB007*TB004)as AJS本期发生额 FROM AJSTA
INNER JOIN AJSTB ON TA001=TB001 AND TA002=TB002
WHERE SUBSTRING(TA006,1,6)=@MONTH AND TB005=@ACCOUNT
GROUP BY TA004,TA005,TB013+'-'+TB014
UNION ALL
SELECT TA001 AS 单别,TA002 as 单号,TA006 as 来源单别单号,SUM(TB007*TB004)as ACT本期发生额,0 as AJS本期发生额
FROM ACTTA INNER JOIN ACTTB ON TA001=TB001 AND TA002=TB002
WHERE ACTTA.TA010 = 'Y' AND ACTTB.TB005=@ACCOUNT and SUBSTRING(ACTTA.TA003,1,6)=@MONTH
GROUP BY TA001,TA002,TA006
)AS PZYC
GROUP BY 单别,单号,来源单别单号
having SUM(ISNULL((ACT本期发生额-AJS本期发生额),0))<>0
order by 单别,单号,来源单别单号
**********************************************************************
第五步:查暂估部分,以下分月初回冲和单到回冲来确定具体哪些单据暂估错了
说明:@ACCOUNT表示要查的会计科目,@MONTH表示要查的年月,
@OLDMONTH表示要查的上个月份
**********************************************************************
--月初回冲:恒等式:本月暂估-上月暂估+本月采购发票=本月进货+本月价差
如若单据对不上可将HAVING前加‘---’注释掉查询全部暂估数据
declare @ACCOUNT char(20) set @ACCOUNT='1403'
DECLARE @MONTH CHAR(6) SELECT @MONTH ='201205'
DECLARE @OLDMONTH CHAR(6) SELECT @OLDMONTH='201204'
select 进货单号, sum(ISNULL(本月进货,0)) AS 本月进货,SUM(ISNULL(价差,0)) AS 价差,SUM(ISNULL(本月发票,0)) as 发票金额,
SUM(ISNULL(本月暂估,0)) as 本月暂估, SUM(isnull(上月暂估,0)) AS 上月暂估,
sum(ISNULL(本月暂估,0)-ISNULL(上月暂估,0)+ISNULL(本月发票,0)-ISNULL(本月进货,0)-ISNULL(价差,0))AS 差额 from(
--本月进货
SELECT LA006+'-'+LA007 AS 进货单号,SUM(LA013*LA005) AS 本月进货,0 AS 价差,0 AS 本月发票,
0 AS 本月暂估,0 as 上月暂估 FROM INVLA
INNER JOIN INVMB ON MB001=LA001
INNER JOIN CMSMC ON MC001=LA009
LEFT JOIN INVMA ON MA002=MB005
LEFT JOIN CMSMQ ON LA006=MQ001
WHERE SUBSTRING(LA004,1,6)=@MONTH AND MA004=@ACCOUNT AND
MC004=1 AND MA001='1' AND MQ003 in('34','35')
GROUP BY LA006,LA007
UNION ALL
--价差
SELECT TB005+'-'+TB006 as 进货单号,0 AS 本月进货, sum(ISNULL(TB055,0))as 价差,
0 as 本月发票,0 AS 本月暂估,0 as 上月暂估 FROM ACPTB
LEFT JOIN ACPTA ON TB001=TA001 AND TB002=TA002
INNER JOIN INVMB ON MB001=TB037
LEFT JOIN INVMA ON MA002=MB005
WHERE SUBSTRING(TA003,1,6)=@MONTH AND TB056 <>'' AND TA024='Y'AND MA004=@ACCOUNT
group BY TB005,TB006
union all
--本月发票
SELECT TB005+'-'+TB006 as 进货单号,0 AS 本月进货, 0 as 价差,
case when TA079='1'THEN SUM(ISNULL(TB017*1,0))ELSE SUM(ISNULL(TB017*-1,0)) END '本月发票',0 AS 本月暂估,0 as 上月暂估 FROM ACPTB
LEFT JOIN ACPTA ON TB001=TA001 AND TB002=TA002
INNER JOIN INVMB ON MB001=TB037
LEFT JOIN INVMA ON MA002=MB005
WHERE SUBSTRING(TA003,1,6)=@MONTH AND TA024='Y'AND MA004=@ACCOUNT
group BY TB005,TB006,TA079
union all
--本月暂估
SELECT TQ010+'-'+TQ011 as 进货单号,0 AS 本月进货, 0 as 价差,0 as 本月发票,sum(TQ025)as 本月暂估,0 as 上月暂估
FROM ACPTQ AS ZG INNER JOIN INVMB ON MB001=TQ007
INNER JOIN CMSMC ON MC001=TQ017
LEFT JOIN INVMA ON MA002=MB005
LEFT JOIN ACPTP ON TQ001=TP001 AND TQ002=TP002
WHERE TP004=@MONTH AND MA004=@ACCOUNT AND MC004=1 AND MA001='1' and TP007='Y'
group BY TQ010,TQ011
union all
--上月暂估
SELECT TQ010+'-'+TQ011 as 进货单号,0 AS 本月进货, 0 as 价差,0 as 本月发票,0 as 本月暂估,SUM(TQ025) AS 上月暂估
FROM ACPTQ AS ZG INNER JOIN INVMB ON MB001=TQ007
INNER JOIN CMSMC ON MC001=TQ017
LEFT JOIN INVMA ON MA002=MB005
LEFT JOIN ACPTP ON TQ001=TP001 AND TQ002=TP002
WHERE TP004=@OLDMONTH AND MA004=@ACCOUNT AND MC004=1 AND MA001='1' and TP007='Y'
group BY TQ010,TQ011
)as F
group BY 进货单号
--HAVING(sum(ISNULL(本月暂估,0)-ISNULL(上月暂估,0)+ISNULL(本月发票,0)-ISNULL(本月进货,0)-ISNULL(价差,0)))<>0
order BY 进货单号
**********************************************************************
--单到回冲:恒等式:本月暂估+本月发票=本月进货+进货价差
declare @ACCOUNT char(20) set @ACCOUNT='1403'
DECLARE @MONTH CHAR(6) SELECT @MONTH ='201205'
select 进货单号, sum(ISNLL(本月进货,0)) AS 本月进货,SUM(ISNULL(价差,0)) AS 价差,SUM(ISNULL(本月发票,0)) as 发票金额,
SUM(ISNULL(本月暂估,0)) as 本月暂估,
sum(ISNULL(本月暂估,0)+ISNULL(本月发票,0)-ISNULL(本月进货,0)-ISNULL(价差,0))AS 差额 from(
--本月进货
SELECT LA006+'-'+LA007 AS 进货单号,SUM(LA013*LA005) AS 本月进货,0 AS 价差,0 AS 本月发票,
0 AS 本月暂估,0 as 前月暂估 FROM INVLA
INNER JOIN INVMB ON MB001=LA001
INNER JOIN CMSMC ON MC001=LA009
LEFT JOIN INVMA ON MA002=MB005
LEFT JOIN CMSMQ ON LA006=MQ001
WHERE SUBSTRING(LA004,1,6)=@MONTH AND MA004 =@ACCOUNT AND
MC004=1 AND MA001='1' AND MQ003 in('34','35')
GROUP BY LA006,LA007
UNION ALL
--价差
SELECT TB005+'-'+TB006 as 进货单号,0 AS 本月进货, sum(ISNULL(TB055,0))as 价差,
0 as 本月发票,0 AS 本月暂估,0 as 前月暂估 FROM ACPTB
LEFT JOIN ACPTA ON TB001=TA001 AND TB002=TA002
INNER JOIN INVMB ON MB001=TB037
LEFT JOIN INVMA ON MA002=MB005
WHERE SUBSTRING(TA003,1,6)=@MONTH AND TA024='Y'AND MA004 =@ACCOUNT
group BY TB005,TB006
union all
--本月发票
SELECT TB005+'-'+TB006 as 进货单号,0 AS 本月进货, 0 as 价差,
case when SUBSTRING(TB008,1,6)<SUBSTRING(TA003,1,6)THEN SUM(ISNULL(TB055,0))ELSE SUM(ISNULL(TB017*1,0)) END '本月发票',0 AS 本月暂估,0 as 前月暂估 FROM ACPTB
LEFT JOIN ACPTA ON TB001=TA001 AND TB002=TA002
INNER JOIN INVMB ON MB001=TB037
LEFT JOIN INVMA ON MA002=MB005
WHERE SUBSTRING(TA003,1,6)=@MONTH AND TA024='Y'AND MA004 =@ACCOUNT
group BY TB005,TB006,TA079,SUBSTRING(TB008,1,6),SUBSTRING(TA003,1,6)
union all
--本月暂估
SELECT TQ010+'-'+TQ011 as 进货单号,0 AS 本月进货, 0 as 价差,0 as 本月发票,sum(TQ025)as 本月暂估,0 as 前月暂估
FROM ACPTQ AS ZG INNER JOIN INVMB ON MB001=TQ007
INNER JOIN CMSMC ON MC001=TQ017
LEFT JOIN INVMA ON MA002=MB005
LEFT JOIN ACPTP ON TQ001=TP001 AND TQ002=TP002
WHERE TP004=@MONTH AND MA004 =@ACCOUNT AND MC004=1 AND MA001='1' and TP007='Y'
group BY TQ010,TQ011
)as F
group BY 进货单号
--HAVING sum(ISNUL(本月暂估,0)+ISNULL(本月发票,0)-ISNULL(本月进货,0)-ISNULL(价差,0))<>0
order BY 进货单号
适用对象:cc
电子电器 机械装备
汽配配件 家具家居
灯饰照明 化工食品
塑橡胶 智能家居
表面处理 ......
紧急电话:18620003468
地址:广州市番禺区石壁街石洲中路240号发现广场703
公司简介
愿景与使命
联系我们
COPYRIGHT 2020 ALL RIGHTS RESERVED 版权所有:广州市晶捷软件有限公司 未经许可 不得复制 粤ICP备2020114024号