鼎捷易飞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