检查易飞关联单据正确性的SQL

检查易飞关联单据正确性的SQL

即使易飞正常运行，也会由于数据库本身的原因或员工操作原因而导致数据不一致。相关单据之间数据传递是否正确，这经常需要人工核对。核对效率太低，以致每月月结时间大幅延后。所以，必须写一套程序来自动检查这些单据的正确性

一、所有单据单头与单身数量金额核对。单头数据必须是单身汇总而来。单头原币与本币金额必须一致。考虑到税的问题。（单据异常，在易飞本身有个检查表可供使用

二、进货单、退货单与应付凭

三、销货单、销退单与结账

四、库存交易单、成本调整单、销货成本INVLA、销货成本INVLA、收款单、付款单、应付凭单、结账单与底

五、底稿与凭证

六、销货成本与销货金额差

二、进货与退货净值，必须等于应付凭单净值

--进货净

SELECT SUM(TG017) AS 进货金额,SUM(TG028+TG019) 原币金额,SUM(TG031+TG032) AS 本币金额,SUM(TG028+TG019)-SUM(TG031+TG032) AS 差异 FROM PURTG WHERE LEFT(TG003,6)='201205' AND TG013='Y'

--退货净

SELECT SUM(TI011+TI015) 原币金额,SUM(TI028+TI029) AS 本币金额,SUM(TI011+TI015)-SUM(TI028+TI029) AS 差异 FROM PURTI WHERE LEFT(TI003,6)='201205' AND TI013='Y'

--进退货净

SELECT SUM(原币金额) AS 原币金额,SUM(本币金额) AS 本币金额

FROM

(

SELECT  SUM(TG028+TG019) 原币金额, SUM(TG031+TG032) AS 本币金额 FROM PURTG WHERE LEFT(TG003,6)='201205' AND TG013='Y'

UNION ALL

SELECT -SUM(TI011+TI015) 原币金额,-SUM(TI028+TI029) AS 本币金额 FROM PURTI WHERE LEFT(TI003,6)='201205' AND TI013='Y'

) A

--应付净

SELECT SUM(TA028+TA029) 原币金额,SUM(TA037+TA038) AS 本币金额,SUM(TA028+TA029)-SUM(TA037+TA038) AS 差异

FROM ACPTA WHERE LEFT(TA003,6)='201205' AND TA024='Y' AND TA001='71'

SELECT 'A0应付与进退货差',103405477.61-103255071.81  --差额刚好是手工录入的应付。所以，进退货生成应付凭单没问题

--进货净      进货生成的应付金

SELECT SUM(TG017) AS 进货金额,SUM(TG028+TG019) 原币金额,SUM(TG031+TG032) AS 本币金额,SUM(TG028+TG019)-SUM(TG031+TG032) AS 差异 FROM PURTG WHERE LEFT(TG003,6)='201205' AND TG013='Y'

SELECT SUM(TA028+TA029) 原币金额,SUM(TA037+TA038) AS 本币金额,SUM(TA028+TA029)-SUM(TA037+TA038) AS 差异

FROM ACPTA WHERE LEFT(TA003,6)='201205' AND TA024='Y' AND TA001='71'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACPTB WHERE LEFT(TB005,2)='34')

--退货净    退货生成的应付金额

SELECT SUM(TI011+TI015) 原币金额,SUM(TI028+TI029) AS 本币金额,SUM(TI011+TI015)-SUM(TI028+TI029) AS 差异 FROM PURTI WHERE LEFT(TI003,6)='201205' AND TI013='Y'

SELECT SUM(TA028+TA029) 原币金额,SUM(TA037+TA038) AS 本币金额,SUM(TA028+TA029)-SUM(TA037+TA038) AS 差异

FROM ACPTA WHERE LEFT(TA003,6)='201205' AND TA024='Y' AND TA001='71'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACPTB WHERE LEFT(TB005,2)='35')

--手工录入的应付凭

SELECT SUM(TA028+TA029) 原币金额,SUM(TA037+TA038) AS 本币金额,SUM(TA028+TA029)-SUM(TA037+TA038) AS 差异

FROM ACPTA WHERE LEFT(TA003,6)='201205' AND TA024='Y' AND TA001='71'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACPTB WHERE LEFT(TB005,2) NOT IN ('34','35'))

SELECT TA001,TA002,TA003

FROM ACPTA WHERE LEFT(TA003,6)='201205' AND TA024='Y' AND TA001='71'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACPTB WHERE LEFT(TB005,2) NOT IN ('34','35'))

--进货是否都生成应(查询时间长达10分钟)

SELECT TOP 10 TH001,TH002,TH003

FROM PURTH

WHERE TH001+TH002 IN (SELECT TG001+TG002 FROM PURTG WHERE LEFT(TG003,6)='201205' AND TG013='Y')

AND

RTRIM(TH001)+RTRIM(TH002)+RTRIM(TH003) NOT IN

(

SELECT RTRIM(TB005)+RTRIM(TB006)+RTRIM(TB007) FROM ACPTB WHERE TB001='71' AND TB001+TB002 IN (SELECT TA001+TA002 FROM ACPTA WHERE LEFT(TA003,6)='201205' AND TA024='Y' AND TA001='71')

)

--进货是否重复生成应付

--退货是否生成应

SELECT TOP 10 TJ001,TJ002,TJ003

FROM PURTJ

WHERE TJ001+TJ002 IN (SELECT TI001+TI002 FROM PURTI WHERE LEFT(TI003,6)='201205' AND TI013='Y')

AND

RTRIM(TJ001)+RTRIM(TJ002)+RTRIM(TJ003) NOT IN

(

SELECT RTRIM(TB005)+RTRIM(TB006)+RTRIM(TB007) FROM ACPTB WHERE TB001='71' AND TB001+TB002 IN (SELECT TA001+TA002 FROM ACPTA WHERE LEFT(TA003,6)='201205' AND TA024='Y' AND TA001='71')

)

--三、销货单、销退单与结账

--销货净

SELECT SUM(TG013+TG025) 原币金额,SUM(TG045+TG046) AS 本币金额,SUM(TG013+TG025)-SUM(TG045+TG046) AS 差异

FROM COPTG WHERE LEFT(TG003,6)='201205' AND TG023='Y'

--销退净

SELECT SUM(TI010+TI011) 原币金额,SUM(TI037+TI038) AS 本币金额,SUM(TI010+TI011)-SUM(TI037+TI038) AS 差异

FROM COPTI WHERE LEFT(TI003,6)='201205' AND TI019='Y'

--销货与销退净

SELECT SUM(原币金额) AS 原币金额,SUM(本币金额) AS 本币金额

FROM

(

SELECT SUM(TG013+TG025) 原币金额,SUM(TG045+TG046) AS 本币金额 FROM COPTG WHERE LEFT(TG003,6)='201205' AND TG023='Y'

UNION ALL

SELECT -SUM(TI010+TI011) 原币金额,-SUM(TI037+TI038) AS 本币金额 FROM COPTI WHERE LEFT(TI003,6)='201205' AND TI019='Y'

) A

--应收净

SELECT SUM(TA029+TA030) 原币金额,SUM(TA041+TA042) AS 本币金额,SUM(TA029+TA030)-SUM(TA041+TA042) AS 差异

FROM ACRTA WHERE LEFT(TA003,6)='201205' AND TA025='Y' AND TA001='61'

SELECT 'A0应收与销货与销退差异',102482404.72-261209030.15  --差额刚好是手工录入的应收。所以，销货与销退生成应收凭单没问

--进货净      进货生成的应收金

SELECT SUM(TG013+TG025) 原币金额,SUM(TG045+TG046) AS 本币金额,SUM(TG013+TG025)-SUM(TG045+TG046) AS 差异

FROM COPTG WHERE LEFT(TG003,6)='201205' AND TG023='Y'

SELECT SUM(TA029+TA030) 原币金额,SUM(TA041+TA042) AS 本币金额,SUM(TA029+TA030)-SUM(TA041+TA042) AS 差异

FROM ACRTA WHERE LEFT(TA003,6)='201205' AND TA025='Y' AND TA001='61'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACRTB WHERE LEFT(TB005,2)='23')

--销退净      销退生成的应收金

SELECT SUM(TI010+TI011) 原币金额,SUM(TI037+TI038) AS 本币金额,SUM(TI010+TI011)-SUM(TI037+TI038) AS 差异

FROM COPTI WHERE LEFT(TI003,6)='201205' AND TI019='Y'

SELECT SUM(TA029+TA030) 原币金额,SUM(TA041+TA042) AS 本币金额,SUM(TA029+TA030)-SUM(TA041+TA042) AS 差异

FROM ACRTA WHERE LEFT(TA003,6)='201205' AND TA025='Y' AND TA001='61'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACRTB WHERE LEFT(TB005,2) IN ('24','35'))

--手工录入的结账单

SELECT SUM(TA029+TA030) 原币金额,SUM(TA041+TA042) AS 本币金额,SUM(TA029+TA030)-SUM(TA041+TA042) AS 差异

FROM ACRTA WHERE LEFT(TA003,6)='201205' AND TA025='Y' AND TA001='61'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACRTB WHERE LEFT(TB005,2) NOT IN ('23','24','35'))

SELECT TA001,TA002,TA003

FROM ACRTA WHERE LEFT(TA003,6)='201205' AND TA025='Y' AND TA001='61'

AND TA001+TA002 IN (SELECT TB001+TB002 FROM ACRTB WHERE LEFT(TB005,2) NOT IN ('23','24','35'))

--六、销货成本与销货金额差

--检查销货是否都已生成INVLA

SELECT TH001,TH002,TH003 FROM COPTH WHERE RTRIM(TH001)+RTRIM(TH002) IN (SELECT RTRIM(TG001)+RTRIM(TG002) FROM COPTG WHERE LEFT(TG003,6)='201205' AND TG023='Y')

AND RTRIM(TH001)+RTRIM(TH002)+RTRIM(TH003) NOT IN (SELECT RTRIM(LA006)+RTRIM(LA007)+RTRIM(LA008) FROM INVLA)

--检查销退是否都已生成INVLA

SELECT TJ001,TJ002,TJ003 FROM COPTJ WHERE RTRIM(TJ001)+RTRIM(TJ002) IN (SELECT RTRIM(TI001)+RTRIM(TI002) FROM COPTI WHERE LEFT(TI003,6)='201205' AND TI019='Y')

AND RTRIM(TJ001)+RTRIM(TJ002)+RTRIM(TJ003) NOT IN (SELECT RTRIM(LA006)+RTRIM(LA007)+RTRIM(LA008) FROM INVLA)

--销货成

SELECT SUM(LA013),SUM(LA012*LA011) FROM INVLA WHERE LEFT(LA004,6)='201205' AND LEFT(LA006,2) IN ('23')

208990244.81 208990242.777997000

--销退成本

SELECT -SUM(LA013),-SUM(LA012*LA011) FROM INVLA WHERE LEFT(LA004,6)='201205' AND LEFT(LA006,2) IN ('24','35')

-106330523.73 -106330521.692500000

--销货销退成本(一次性计算销货与销退成本之和)

SELECT -SUM(LA005*LA013),-SUM(LA005*LA012*LA011) FROM INVLA WHERE LEFT(LA004,6)='201205' AND LEFT(LA006,2) IN ('23','24','35')

103853878.56 103853878.565497000

--销货与销退金额

--销货净

SELECT SUM(TG013+TG025) 原币金额,SUM(TG045+TG046) AS 本币金额,SUM(TG013+TG025)-SUM(TG045+TG046) AS 差异

FROM COPTG WHERE LEFT(TG003,6)='201205' AND TG023='Y'

--销退净

SELECT -SUM(TI010+TI011) 原币金额,-SUM(TI037+TI038) AS 本币金额,SUM(TI010+TI011)-SUM(TI037+TI038) AS 差异

FROM COPTI WHERE LEFT(TI003,6)='201205' AND TI019='Y'

--销货销退金额

--销货与销退金额(一次性计算销货与销退成本之和)

SELECT SUM(原币金额) AS 原币金额,SUM(本币金额) AS 本币金额

FROM

(

SELECT SUM(TG013+TG025) 原币金额,SUM(TG045+TG046) AS 本币金额 FROM COPTG WHERE LEFT(TG003,6)='201205' AND TG023='Y'

UNION ALL

SELECT -SUM(TI010+TI011) 原币金额,-SUM(TI037+TI038) AS 本币金额 FROM COPTI WHERE LEFT(TI003,6)='201205' AND TI019='Y'

) A

102482404.72 102482404.72

SELECT 102482404.72-103853878.56

-1371473.84(这表明A0在５月亏135)

--差额较大的销货单与销退

SELECT TOP 500 TH001,TH002,TH003,LA013,TH013 FROM INVLA,COPTH WHERE TH001=LA006 AND TH002=LA007 AND TH003=LA008

AND LEFT(LA004,6)='201205' ORDER BY ABS(LA013-TH013) desc

SELECT TOP 10 * FROM COPTH

SELECT TOP 10 * FROM INVLA WHERE LA013 IS NULL

SELECT COUNT(*) FROM INVLA WHERE LA013 IS NULL

--INVLA金额为空

SELECT * FROM INVLA WHERE LA013 IS NULL

--23A0的单据，成本是其对应进货单的单价。如果不是，则必须更正

SELECT COUNT(*) FROM INVLA A,COPTH C,PURTH P

WHERE C.TH001=A.LA006 AND C.TH002=A.LA007 AND C.TH003=A.LA008

AND C.TH018=RTRIM(P.TH001)+'-'+P.TH002+'-'+RTRIM(P.TH003)

AND ISNULL(A.LA013,0)<>P.TH019

--查到925+65=990条记

--改正吧

SELECT C.TH001,C.TH002,C.TH003,P.TH001,P.TH002,P.TH003,A.LA012,A.LA013,P.TH018,P.TH019 FROM INVLA A,COPTH C,PURTH P

WHERE C.TH001=A.LA006 AND C.TH002=A.LA007 AND C.TH003=A.LA008

AND C.TH018=RTRIM(P.TH001)+'-'+P.TH002+'-'+RTRIM(P.TH003)

AND ISNULL(A.LA013,0)<>P.TH019

BEGIN TRAN

UPDATE INVLA SET LA012=P.TH018,LA013=P.TH019

FROM INVLA A,COPTH C,PURTH P

WHERE C.TH001=A.LA006 AND C.TH002=A.LA007 AND C.TH003=A.LA008

AND C.TH018=RTRIM(P.TH001)+'-'+P.TH002+'-'+RTRIM(P.TH003)

AND ISNULL(A.LA013,0)<>P.TH019

COMMIT

--再来核实一下销退单，35A1之类的销退单，成本是它自己，对应的销货单的成本是销退单的成本

--具体记录 

SELECT 

C.TH001,C.TH002,C.TH003,P.TJ001,P.TJ002,P.TJ003,A.LA012,A.LA013,P.TJ011,P.TJ012 FROM INVLA A,COPTH C,COPTJ P 

WHERE C.TH001=A.LA006 AND C.TH002=A.LA007 AND C.TH003=A.LA008 AND C.TH001='23' AND LEFT(P.TJ001,2)='35' 

AND C.TH018=RTRIM(P.TJ001)+'-'+P.TJ002+'-'+RTRIM(P.TJ003) AND ISNULL(A.LA013,0)<>P.TJ012 --共有多少 

SELECT COUNT() FROM INVLA A,COPTH C,COPTJ P 

WHERE C.TH001=A.LA006 AND C.TH002=A.LA007 AND C.TH003=A.LA008 AND C.TH001='23' AND LEFT(P.TJ001,2)='35' 

AND C.TH018=RTRIM(P.TJ001)+'-'+P.TJ002+'-'+RTRIM(P.TJ003) AND ISNULL(A.LA013,0)<>P.TJ012 

--总的差额算出来共-11.26），所以不用修改了 

SELECT SUM(A.LA013-P.TJ012) FROM INVLA A,COPTH C,COPTJ P WHERE C.TH001=A.LA006 AND C.TH002=A.LA007 AND C.TH003=A.LA008 AND C.TH001='23' AND LEFT(P.TJ001,2)='35' 

AND C.TH018=RTRIM(P.TJ001)+'-'+P.TJ002+'-'+RTRIM(P.TJ003) AND ISNULL(A.LA013,0)<>P.TJ012 

--销退成本有问题，调拨的销退单，成本必须与单价相同 

SELECT TJ001,TJ002,TJ003,LA011,LA012,LA013,TJ007,TJ011,TJ012  FROM INVLA A ,COPTJ J WHERE LA006=TJ001 AND LA007=TJ002 AND LA008=TJ003 

AND LA012<>TJ011 AND LEFT(TJ001,2)='35' AND LEN(TJ001)=4 AND TJ001+TJ002 IN (SELECT TI001+TI002 FROM COPTI WHERE LEFT(TI003,6)='201205') 

UPDATE INVLA SET LA012=TJ011,LA013=TJ011LA011 FROM INVLA A ,COPTJ J WHERE LA006=TJ001 AND LA007=TJ002 AND LA008=TJ003 

AND LA012<>TJ011 AND LEFT(TJ001,2)='35' AND LEN(TJ001)=4 AND TJ001+TJ002 IN (SELECT TI001+TI002 FROM COPTI WHERE LEFT(TI003,6)='201205') 

📱 扫码关注公众号

扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满