select cm.heatno,cm.lotheatno,cm.heatorder
,max(cm.cutdate)cutdate,cm.cutdimensiona,cm.cutdimensionb,cm.length
,sum( cm.weight/1000)weight
,sum( cm.weightwgt/1000)weightwgt,count(cm.qty)qty
,(case status
when 20 then '20-產出等待'
when 22 then '22-質量封鎖'
when 23 then '23-可編計劃'
when 24 then '24-編入計劃'
when 29 then '29-待掛訂單'
else status
end ) status
,cm.qualitycode,cm.gkno,cm.spec,cm.warehouseno,cm.areano,cm.rowno,cm.ccno as ccnoid,ws.workshopname as ccno,cm.stdprodcode
,max(convert(nvarchar(50), cm.lastintime, 20))lastintime,cm.slabsrc,cm.memo,bm.vehicleno,bm.providername
,( case cm.msdp
when 00 then '未修磨'
when 01 then '全修'
when 02 then '角修'
when 05 then '全修探傷'
when 06 then '角修探傷'
when 07 then '拋丸探傷'
when 08 then '全修探傷拋丸'
when 09 then '角修探傷拋丸'
when 11 then '拋丸'
when 12 then '探傷'
else ''
end ) msdp
,( case when cm.msdp is null then ''
else '是'
end ) ismsdp
from t_gpkcmx cm
left join t_ibmes25 bm on cm.heatno = bm.heatno
left join b_workshop ws on cm.ccno = ws.workshopcode
where cm.status not in (21,54) and exists (select distinct warehouseno from zgzx_basekq where cm.warehouseno = warehouseno)
group by cm.heatno,cm.lotheatno,cm.heatorder,cm.cutdimensiona,cm.cutdimensionb,cm.length,cm.qualitycode,cm.gkno,cm.spec,cm.warehouseno,
cm.areano,cm.rowno,cm.ccno,ws.workshopname,cm.stdprodcode ,cm.status ,cm.slabsrc,cm.memo,bm.vehicleno,bm.providername,cm.msdp order by lastintime desc"
