委外仓每日未完结订单查询

—委外仓每日未完结订单查询
SELECT g.whna AS ‘仓库’,o.biid AS ‘销售单号’,o.aico AS ‘备货任务单’
,CASE WHEN o.flag =’13’THEN ‘已分配任务’WHEN o.flag =’16’THEN ‘分拣中’wHEN o.flag =’17’THEN ‘分拣完成’wHEN o.flag =’32’THEN ‘异常完成’ELSE ‘’END AS ‘状态’
,CASE WHEN e.biid IS NOT NULL THEN ‘已取消’ ELSE ‘’ END AS ‘是否取消’
,CASE WHEN isnull(n.isprint,0)>0 or isnull(n.ispriv,0)>0 THEN ‘已打印’ ELSE ‘未打印’ END AS ‘是否打印’
,i.lpid as’快递单号’
FROM obma o left join waho g on g.whid = o.whid AND whty=1 left join orga r on r.orid = o.orid —组织架构
left join obbm e on o.biid=e.biid left join bilp i on o.biid=i.biid and i.numb=1 —销售订单物流单号表
left join noin n on n.biid = o.biid
WHERE 1=1 AND e.biid IS NULL AND o.flag IN (‘13’,’16’,’17’,’32’) AND (isnull(n.isprint,0)>=0 or isnull(n.ispriv,0)>0)
AND r.orna LIKE ‘%委外仓%’
ORDER BY g.whna

—委外仓每日未完结订单汇总
SELECT g.whna AS ‘仓库’,count(DISTINCT o.aico) AS ‘备货任务单数量’,count( DISTINCT o.biid )AS ‘销售订单数量’
FROM obma o left join waho g on g.whid = o.whid AND whty=1 left join orga r on r.orid = o.orid —组织架构
left join obbm e on o.biid=e.biid left join noin n on n.biid = o.biid
WHERE 1=1 AND e.biid IS NULL AND o.flag IN (‘13’,’16’,’17’,’32’) AND (isnull(n.isprint,0)>=0 or isnull(n.ispriv,0)>0)
AND r.orna LIKE ‘%委外仓%’
group BY g.whna ORDER BY g.whna