Skip to end of metadata
Go to start of metadata


--问题查看


--1. 查看是否有漏单(建议五六个小时查看一次)

--淘宝漏单

select count(a.tid) as 淘宝漏单下载
from sys_info.dbo.jdp_tb_trade a
left join T_OMSTaobaoOrder b with(nolock) on a.tid=b.tid
where a.modified <DATEADD(hour,-1,GETDATE())
and a.status='WAIT_SELLER_SEND_GOODS'
and a.seller_nick in (select SellerNick from T_IESShop where OMS_DistributorId=-1 and PlatForm=2 and IsActive=1)
and b.Id is null


--线上分销漏单查看

select count(a.tid) as 线上分销漏单查看
from sys_info.dbo.jdp_tb_trade a
left join T_OMSDistributorOrder b with(nolock) on cast(a.tid as varchar(30))=b.tid
where a.modified <DATEADD(hour,-1,GETDATE())
and a.status='WAIT_SELLER_SEND_GOODS'
and a.seller_nick in (select SellerNick from T_IESShop where OMS_DistributorId<>-1 and PlatForm=2 and IsActive=1)
and b.Id is null

 

--2. 是否存在中间表明细为空

select COUNT(a.Id) as 是否存在中间表明细为空
from T_OMSTaobaoOrder a
left join T_OMSTaobaoProduct b on a.Id=b.OMSTaobaoOrderId
where
a.InsertDate >= '2016-11-11 00:00:00'
and b.Id is null

 

--3.是否存在一小时前转换的单据,还未分配 (非重点,可能由于单量大引起)

select COUNT(*) as 一小时前仍未分配非重点
from T_OMSOnlineOrder with(nolock)
where CreationDate between '2016-11-10 23:00:00' and DATEADD(HOUR,-1,GETDATE())
and OrderStatus =50


--处理方法 01.查订单明细 02.查看任务执行历史

 

--4. 是否存转换10分钟后,订单明细没有插入

select COUNT(a.Id) as 待分配无明细
from T_OMSOnlineOrder a with(nolock)
left join T_OMSOnlineOrderProduct b with(nolock) on b.OMSOnlineOrderId=a.Id
where
a.OrderStatus=50 and
a.CreationDate between '2016-11-10 23:00:00' and DATEADD(minute,-10,GETDATE())
and b.Id is null

--select '是否存转换10分钟后,订单明细没有插入' ,a.Id,a.SourceCode,a.OrderStatus,a.OrderShopName,a.OMS_DistributorId from T_OMSOnlineOrder a with(nolock)
--left join T_OMSOnlineOrderProduct b with(nolock) on b.OMSOnlineOrderId=a.Id
--where a.CreationDate<DATEADD(minute,-10,GETDATE())
--and b.Id is null

 

 

 

 

--5.是否存在重复的单据

select count(a.Id) as 存在重复的单据
from T_OMSOnlineOrder a with(nolock), T_OMSOnlineOrder b with(nolock)
where a.SourceCode=b.SourceCode and a.Id<>b.Id
and a.OrderStatus not in (7,8) and b.OrderStatus not in (7,8)
and a.CreationDate>='2016-11-11 00:00:00' and b.CreationDate >='2016-11-11 00:00:00'
--order by SourceCode

 

--6.是否存在合并订单原单未作废

SELECT COUNT(a.Id) as 合并原单未作废
FROM dbo.T_OMSOnlineOrder a WITH(NOLOCK),
(select Id from T_OMSOnlineOrder WITH(NOLOCK) where CreationDate>='2016-11-11 00:00:00' ) b
WHERE a.NewOrderId =b.Id
and a.CreationDate>='2016-11-11 00:00:00'
and a.OrderStatus not in (7,8)

 

--7. 是否存在单据未撤回,直接取消单据

select distinct a.Id as 未撤回已取消
from T_OMSOnlineOrder a WITH(NOLOCK)
inner join T_OMSOrderLogs b WITH(NOLOCK) on a.Id=b.OMSOnlineOrderId and b.LogType='订单打印' and b.LogMessage='通过接口标记订单已配货' and b.CreationDate>'2016-11-11 00:00:00'
left join T_OMSOrderLogs c WITH(NOLOCK) on a.Id=c.OMSOnlineOrderId and c.LogType='订单撤回成功' and c.LogMessage='订单撤回成功!' and c.CreationDate>'2016-11-11 00:00:00'
where a.OrderStatus in (7,8)
and a.CreationDate >='2016-11-11 00:00:00'
and c.Id is null

 


--8. 是否卡在已审核(30分钟前审核,仍未传入WMS)

select COUNT(*) as 三十分钟前审核仍未传入WMS非重点
from T_OMSOnlineOrder WITH(NOLOCK)
where OrderStatus=3 and AuditTime < DATEADD(minute,-30,GETDATE())
and CreationDate >='2016-11-11 00:00:00'


--9. 是否卡在仓库发货(1小时前),下午重点查看

--没有发货记录

select count(a.Id) as 没有发货记录卡在仓库发货非重点
from T_OMSOnlineOrder a WITH(NOLOCK)
left join T_OMSOrderLogs b WITH(NOLOCK) on a.Id=b.OMSOnlineOrderId and b.LogType='平台发货' and b.CreationDate>'2016-11-11 00:00:00'
where OrderStatus=5 and ScanTime < DATEADD(HOUR,-1,GETDATE())
and a.CreationDate>'2016-11-11 00:00:00'
and b.Id is null


--存在发货记录
select distinct a.Id as 卡在仓库发货非重点
from T_OMSOnlineOrder a WITH(NOLOCK)
inner join T_OMSOrderLogs b WITH(NOLOCK) on a.Id=b.OMSOnlineOrderId and b.LogType='平台发货' and b.CreationDate>'2016-11-11 00:00:00'
where OrderStatus=5 and ScanTime < DATEADD(HOUR,-1,GETDATE())
and a.CreationDate >='2016-11-11 00:00:00'

-- 处理方法
--delete from T_OMSOrderLogs where logtype='平台发货' and LogMessage like '平台发货失败%'
-- 京东单据多处理一步
--delete from T_OMSOrderLogs where logtype='JD电子面单发货' and LogMessage like 'JD电子面单发货%'

 

--10. 是否存在负在单,可以先找开发查看下再修复

select COUNT(*) as 存在负在单 from T_IESStock where OMSQtyOut<0


--修复语句
--update dbo.T_IESStock set OMSQtyOut=0,ModifiedDate=GETDATE() where OMSQtyOut<0 AND CompanyId=-1

 

--11. 是否存在一小时前未传分销单据

select COUNT(*) as 一小时前单据未传分销非重点
from T_OMSOnlineOrder WITH(NOLOCK)
where OrderStatus in (5,6) and ScanTime < DATEADD(HOUR,-1,GETDATE())
and IsToDrp=0
and CreationDate >='2016-11-11 00:00:00'


--12. 是否未分配发货仓库,先查看是否为设置问题

select COUNT(*) as 未分配发货仓库非重点
from T_OMSOnlineOrder WITH(NOLOCK)
where OrderStatus not in (50,7,8)
and ISNULL(IESWarehouseId,0)=0
and CreationDate >= '2016-11-11 00:00:00'

 

--13. 是否未分配物流公司

select COUNT(*) as 未分配物流公司非重点
from T_OMSOnlineOrder WITH(NOLOCK)
where OrderStatus not in (50,7,8)
and ISNULL(ShipperId,0)=0
and CreationDate >= '2016-11-11 00:00:00'


--14. 是否存在重复明细

select a.OOID as 存在重复明细 ,a.OMSOnlineOrderId ,b.IsMerge
from T_OMSOnlineOrderProduct a WITH(NOLOCK),T_OMSOnlineOrder b WITH(NOLOCK)
where a.OMSOnlineOrderId=b.Id
and b.OrderStatus not in (7,8)
and b.CreationDate >= '2016-11-11 00:00:00'
and a.Title not like '%组合%'
and ISNULL(a.OOID,'')<>''
group by a.OOID ,a.OMSOnlineOrderId,b.IsMerge
having (COUNT(a.OOID)>1)

 

 

--15. 是否存在未确认单据,头表与子表金额有误

--审核

;with OrderProductTable as
(
select SUM(a.StandardPrice * a.Num) as OrderProductAmount,a.OMSOnlineOrderId
FROM dbo.T_OMSOnlineOrderProduct a WITH ( NOLOCK )
,T_OMSOnlineOrder b WITH ( NOLOCK )
WHERE a.OMSOnlineOrderId=b.Id AND RefundStatus !=6
and b.OrderStatus=1
group by a.OMSOnlineOrderId
)
select count(b.Id) as 金额有误
from OrderProductTable a ,T_OMSOnlineOrder b WITH ( NOLOCK )
where a.OMSOnlineOrderId=b.Id
and a.OrderProductAmount<>b.ProductAmount
and b.CreationDate >= '2016-11-11 00:00:00'

--支付金额


select COUNT(*) AS 支付金额金额有误
from T_OMSOnlineOrder
where CreationDate>= '2016-11-11 00:00:00'
and OrderAmount<>(ReceivedAmount+CodAmount)

 

 

 

 

--16. 退单是否存在漏下载漏更新,11月12日开始查询

-- 按约定,均为半小时后没下载或更新,才查出数据


--漏下

select count(a.tid) as 退单漏单查看排除线上分销
from sys_info.dbo.jdp_tb_refund a
left join T_OMSTaoBaoRefund b with(nolock) on a.tid=b.tid and b.refund_id=a.refund_id
where a.modified < DATEADD(minute,-30,GETDATE())
and a.status='WAIT_SELLER_SEND_GOODS'
and a.seller_nick in (select SellerNick from T_IESShop where OMS_DistributorId=-1 and PlatForm=2 and IsActive=1)
and b.Id is null


--漏更新

select count(a.refund_id) as 退单漏更新 from sys_info.dbo.jdp_tb_refund a
inner join T_OMSTaoBaoRefund b with(nolock) on a.tid=b.tid and b.refund_id=a.refund_id
where a.modified <>b.modified
and a.modified < DATEADD(minute,-30,GETDATE())
and b.status<>'SUCCESS'

 

 

 

 

 

 

 


----17. 活动检测

--1.1 同一赠品明细出现多次的

--叠加也会导致,查看活动是否设置叠加
select OMSOnlineOrderId,Sku
from T_OMSOnlineOrderProduct with(nolock)
where CreationDate>='2016-11-10 23:59:00' and IsGift=1
group by OMSOnlineOrderId,Sku
having COUNT(id)>1

 


--1.2活动中没有累加的但是同一个赠品赠送数量有多件的
select * from T_OMSOnlineOrderProduct with(nolock)
where CreationDate>='2016-11-10 23:59:00' and IsGift=1 and Num>1
and not exists(select 1 from T_OMSActive where IsSuperImpose=1 and IsActive=1)

 


----18. 星盘订单同步
--2.1 统计星盘接口订单数

--非重要,查询为0有问题,牛瑞玲解决
--适用于JNBY、GANT、贵人鸟、奥康

;with t1 as
(
select sum(case IsAllocateXP when 1 then 1 else 0 end) as IsAllocateXP ,sum(case IsAcceptXP when 1 then 1 else 0 end) as IsAcceptXP,
sum(case IsToQM when 1 then 1 else 0 end) as IsToQM
from T_OMSOnlineOrder a with(nolock),T_OMSOnlineOrderExtend b with(nolock) where a.Id=b.OMSOnlineOrderId
and a.CreationDate>='2016-11-10 23:59:00' and CreationDate<=DATEADD(MI,-10,GETDATE())
),t2 as
(
select sum(case IsAllocateXP when 1 then 1 else 0 end) as IsAllocateXP ,sum(case IsAcceptXP when 1 then 1 else 0 end) as IsAcceptXP,
sum(case IsToQM when 1 then 1 else 0 end) as IsToQM
from T_OMSOnlineOrder a with(nolock),T_OMSOnlineOrderExtend b with(nolock) where a.Id=b.OMSOnlineOrderId
and a.CreationDate>='2016-11-10 23:59:00' and CreationDate<=GETDATE())
select SUM(IsAllocateXP) as '10分钟内分单数',SUM(IsAcceptXP) as '10分钟内接单数',SUM(IsToQM) as '10分钟内发货数' from
(select -IsAllocateXP as IsAllocateXP,-IsAcceptXP as IsAcceptXP,-IsToQM as IsToQM from t1
union all select IsAllocateXP,IsAcceptXP,IsToQM from t2)t3


---3分析零售单重复

-- 在分销服务器执行

--select oms_ebonum from oms_orderso t where t.crmode='INS01'
--and creationdate> to_date('2016-11-10 23:59:00','yyyy-mm-dd hh24:mi:ss')
--group by oms_ebonum
--having count(id)>1

 

 


--19. 查询三枪已存在订单(已优化,不会出现,语句以防万一)
--防止富乐接口出问题
SELECT DISTINCT(a.Id) FROM dbo.T_OMSOnlineOrder a with(nolock),dbo.T_OMSOrderLogs b with(nolock)
WHERE a.id=b.OMSOnlineOrderId
AND a.OrderStatus=3
AND b.LogType='标记已审核'




--20. 处理卡传WMS中状态单据,张凯执行
--UPDATE dbo.T_OMSOnlineOrder
--SET OrderStatus=3
--WHERE OrderStatus=21
-- -- AND CreationDate<''

 


--21.--以下为三枪特殊处理

-- 清传WMS失败日志,防止日志过大
--delete from T_OMSOrderLogs where logtype='传wms失败'
--delete from T_OMSOrderLogs where LogType='标记已审核'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Labels
  • None
Write a comment…