– 查詢缺失下游出庫單的銷售出庫單編號
SELECT a.* from
(SELECT t.fbillno FROM T_SAL_OUTSTOCK T LEFT JOIN T_SAL_OUTSTOCKFIN F ON T.FID=F.FID WHERE F.FSETTLEORGID=100645 and t.fstockorgid=100635 AND T.fdate>=‘2019-06-01’ and T.fdate<=‘2019-06-30’) a
where a.fbillno not in
(SELECT R.FSRCBILLNO FROM T_SAL_OUTSTOCK t LEFT JOIN T_SAL_OUTSTOCKENTRY_R r on t.FID=R.FID WHERE FSETTLEID=4232623 AND T.fdate>=‘2019-06-01’ and T.fdate<=‘2019-06-30’)

– 查詢缺失下游應收單的銷售出庫單編號
SELECT o.fbillno FROM T_SAL_OUTSTOCKENTRY_R r LEFT JOIN T_SAL_OUTSTOCK o on r.fid=o.fid
where FARJOINAMOUNT =0 and o.fdate>‘2019-06-01’ and o.fdate<‘2019-06-30’ and fbillno not like ‘XSCK%’ and o.FCREATORID=‘16394’

– 查詢缺失下游入庫單的銷售出庫單編號
SELECT a.* from
(
SELECT t.fbillno FROM T_SAL_OUTSTOCK T LEFT JOIN T_SAL_OUTSTOCKFIN F ON T.FID=F.FID WHERE F.FSETTLEORGID=100645 and t.fstockorgid=100635 AND T.fdate>=‘2019-06-01’ and T.fdate<=‘2019-06-30’
) a
where a.fbillno not in
(
SELECT o.fbillno from T_STK_INSTOCKENTRY_LK lk LEFT JOIN T_SAL_OUTSTOCKENTRY e on lk.FSID=e.FENTRYID LEFT JOIN T_SAL_OUTSTOCK o on e.FID=o.fid where o.fdate>=‘2019-06-01’ and o.fdate<=‘2019-06-30’
)

– 查詢某物料銷售出庫單成交費和應收單成交費不一致的數據
SELECT b.FBILLNO as ‘應收單號’, a.FBILLNO as ‘銷售出庫單號’,a.FDiscount as ‘銷售出庫單-成交費’,b.FDISCOUNTAMOUNTFOR as ‘應收單-成交費’ from (
SELECT o.FBILLNO ,f.FDiscount from T_SAL_OUTSTOCK o
LEFT JOIN T_SAL_OUTSTOCKENTRY e on e.FID=o.fid
LEFT JOIN T_SAL_OUTSTOCKENTRY_F f ON e.FENTRYID =f.FENTRYID
where
o.fdate >= ‘2019-06-01’ and o.fdate <= ‘2019-06-30’ and o.FCREATORID=‘16394’ – and f.FDISCOUNT!=0
) a
LEFT JOIN (
SELECT r.FBILLNO ,e.FSOURCEBILLNO ,e.FDISCOUNTAMOUNTFOR from t_AR_receivable r
LEFT JOIN t_AR_receivableEntry e on e.FID=r.fid
LEFT JOIN T_SAL_OUTSTOCK o on o.FBILLNO=e.FSOURCEBILLNO
LEFT JOIN T_SAL_OUTSTOCKENTRY en ON e.FENTRYID =en.FENTRYID
where
o.fdate >= ‘2019-06-01’ and o.fdate <= ‘2019-06-30’ and o.FCREATORID=‘16394’-- and e.FDISCOUNTAMOUNTFOR!=0
) b on a.FBILLNO=b.FSOURCEBILLNO
where a.FDiscount!=b.FDISCOUNTAMOUNTFOR

– 查詢成交費+關聯應收！=售價的數據
SELECT o.FBILLNO as ‘銷售出庫單號’,e.F_SJ_Amount as ‘售價’,f.FDISCOUNT as ‘成交費’,r. FARJOINAMOUNT as ‘關聯應收’
from T_SAL_OUTSTOCK o
LEFT JOIN T_SAL_OUTSTOCKENTRY e on o.FID=e.fid
LEFT JOIN T_SAL_OUTSTOCKENTRY_F f on e.FENTRYID =f.FENTRYID
LEFT JOIN T_SAL_OUTSTOCKENTRY_R r on e.FENTRYID =r.FENTRYID
where o.fdate>=‘2019-06-01’ and o.fdate<=‘2019-06-30’ and (r.FARJOINAMOUNT+ f.FDISCOUNT)!=e.F_SJ_Amount

– 查詢銷售出庫單和應收單銷售組織不一致的出庫單編號
SELECT o.FBILLNO FROM T_AR_RECEIVABLEENTRY e
LEFT JOIN T_SAL_OUTSTOCK o on e.FSOURCEBILLNO=o.FBILLNO
LEFT JOIN t_AR_receivable r on r.fid=e.fid
where o.fdate >=‘2019-06-01’ and o.fdate<=‘2019-06-30’ and o.FBILLNO not like ‘XSCK%’ and o.FSALEDEPTID!=r.FSALEDEPTID

– 查詢銷售出庫單成交費和應收單各部門數量,成交費不一致的情況
SELECT a.FBILLNO,a.saleDept as ‘銷售出庫單-部門’,a.successFee as ‘銷售出庫單-成交費’,b.successFee as ‘應收單成-交費’,a.quantity as ‘銷售出庫單-數量’, b.quantity as ‘應收單-數量’ from (
SELECT o.FBILLNO,o.FSALEDEPTID as saleDept,sum(f.FDiscount) as successFee,sum(e.FREALQTY) as quantity from T_SAL_OUTSTOCK o
LEFT JOIN T_SAL_OUTSTOCKENTRY e on e.FID=o.fid
LEFT JOIN T_SAL_OUTSTOCKENTRY_F f ON e.FENTRYID =f.FENTRYID
where o.fdate >= ‘2019-06-01’ and o.fdate <= ‘2019-06-30’ and o.FCREATORID=‘16394’ and o.FBILLNO not like ‘XSCK%’ GROUP BY o.FBILLNO,o.FSALEDEPTID
) a
LEFT JOIN (
SELECT e.FSOURCEBILLNO,r.FSALEDEPTID as saleDept,sum(e.FDISCOUNTAMOUNTFOR) as successFee,sum(e.FPRICEQTY) as quantity
from t_AR_receivable r
LEFT JOIN t_AR_receivableEntry e on e.FID=r.fid
LEFT JOIN T_SAL_OUTSTOCK o on o.FBILLNO=e.FSOURCEBILLNO
LEFT JOIN T_SAL_OUTSTOCKENTRY en ON e.FENTRYID =en.FENTRYID
where o.fdate >= ‘2019-06-01’ and o.fdate <= ‘2019-06-30’ and o.FCREATORID=‘16394’ GROUP BY e.FSOURCEBILLNO,r.FSALEDEPTID
) b on a.FBILLNO=b.FSOURCEBILLNO and a.saleDept=b.saleDept
where a.successFee!=b.successFee or a.quantity!=b.quantity

總結

以上是生活随笔為你收集整理的金蝶K3cloud问题单排查的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。