ERP车间报表开发

select distinct gzh=(select distinct top 1 orderno from mom10200 m inner join ord20111 on m.orderno=ord20111.docno where m.orderno= '05B11F001'),
(select distinct dept from sfc10500 where part in (select partp from sfc10510 where partc=s.part))as needdept,
(select top 1 description from sys10110 where sys10110.dept in (select distinct dept from sfc10500 where part in
(select partp from sfc10510 where partc=s.part))) as needdeptname,
jsdh = (select top 1 part from ord20111 where ord20111.docno = '05B11F001'),
tf=(select top 1 txqty from ord20111 where ord20111.docno = '05B11F001'),
mcgg = (select top 1 description from inv10100 inner join ord20111 on ord20111.part = inv10100.part where ord20111.docno = '05B11F001'),
s.part as ljth,
ljmc = (select top 1 description from inv10100 where inv10100.part=s.part),
th = (select top 1 draw from inv10100 where inv10100.part=s.part),
mtl=(select top 1 makeqty from sfc10500 where sfc10500.part=s.part),
sszj=(select cst_partm from cst_tbea10120 where cst_no=s.modocno),
'代' as gx,/*只有1道工序为剪*/
(select top 1 cst_xuhk from sfc10410 where sfc10410.partc=s.part) as xuhk

from
(select s1.dept,s1.prseq,s1.prcode,s1.modocno,s1.part from
(sfc10500 s1 inner join cst_tbea10120 c on s1.modocno=c.cst_no)
inner join(mom10200 m inner join sfc10560 s6 on m.no=s6.modocno
and s6.prdept='D07'and s6.preprdept='D07' and s6.nxtprdept='D07')
on s1.modocno=no and s1.part=m.part where m.orderno='05B11F001')s/*零件范围，工序只为1绝缘*/
where
exists( select top 1 description from inv10100 where inv10100.part=s.part and
description not like'%扇形垫块%')and
exists(select 1 from sfc10550 s5 where s.modocno=s5.modocno and s5.prseq='0010' and s5.prcode='030301'
and (select count(*) from sfc10500 where sfc1

select distinct gzh=(select distinct orderno from mom10200 m inner join ord20111 on m.orderno=ord20111.docno where m.orderno='05B11F001'),
(select distinct dept from sfc10500 where part in (select partp from sfc10510 where partc=s.part))as needdept,
(select top 1 description from sys10110 where sys10110.dept in (select distinct dept from sfc10500 where part in
(select partp from sfc10510 where partc=s.part))) as needdeptname,
jsdh = (select top 1 part from ord20111 where ord20111.docno ='05B11F001'),
tf=(select top 1 txqty from ord20111 where ord20111.docno='05B11F001'),
mcgg = (select top 1 description from inv10100 inner join ord20111 on ord20111.part = inv10100.part where ord20111.docno ='05B11F001'),
s.part as ljth,
ljmc = (select top 1 description from inv10100 where inv10100.part=s.part),
th = (select top 1 draw from inv10100 where inv10100.part=s.part),
mtl=(select top 1 makeqty from sfc10500 where sfc10500.part=s.part),
sszj=(select cst_partm from cst_tbea10120 where cst_no=s.modocno),
isnull((select top 1 prname from sfc10300 a where a.prcode in(select prcode from sfc10500 where modocno=s.modocno and
sfc10500.prcode='030101')),'')+'冲'+isnull((select top 1 prname from sfc10300 a where a.prcode in
(select prcode from sfc10500 where modocno=s.modocno and sfc10500.prcode='030402')),'')+
isnull((select top 1 prname from sfc10300 a where a.prcode in(select prcode from sfc10500 where modocno=s.modocno and
sfc10500.prcode='030202')),'') as gx,/*检查是否有'装'工序，没有则默认为剪冲*/
(select top 1 cst_xuhk from sfc10410 where sfc10410.partc=s.part) as xuhk

from
(select s1.dept,s1.prseq,s1.prcode,s1.modocno,s1.part from
(sfc10500 s1 inner join cst_tbea10120 c on s1.modocno=c.cst_no)
inner join(mom10200 m inner join sfc10560 s6 on m.no=s6.modocno
and s6.prdept='D07'and s6.preprdept='D07' and s6.nxtprdept='D07')
on s1.modocno=no and s1.part=m.part where m.orderno='05B11F001')s/*零件范围，工序只为1绝缘*/
where
(exists(select 1 from sfc10550 s5 where s.modocno=s5.modocno and s5.prseq='0011' and s5.prcode='030401' and
s5.preprseq='0010' and s5.preprcode='030101')and
exists(select 1 from sfc10550 s5 where s.modocno=s5.modocno and s5.prseq='0013' and s5.prcode='030202' and
s5.preprseq='0012' and s5.preprcode='030402') and (select count(*) from sfc10500 where sfc10500.part=s.part)=4)or
(exists(select 1 from sfc10550 s5 where s.modocno=s5.modocno and s5.prseq='0011' and s5.prcode='030402' and
s5.preprseq='0010' and s5.preprcode='030401') and (select count(*) from sfc10500 where sfc10500.part=s.part)=2)or
(exists(select 1 from sfc10550 s5 where s.modocno=s5.modocno and s5.prseq='0012' and s5.prcode='030202' and
s5.preprseq='0011' and s5.preprcode='030401') and (select count(*) from sfc10500 where sfc10500.part=s.part and
sfc10500.prseq='0010' and sfc10500.prcode='030101')=3)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8382469/viewspace-259855/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8382469/viewspace-259855/