金蝶K3cloud问题单排查

– 查询缺失下游出库单的销售出库单编号
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