半成品收发数量流程管控

USE [RossERP_R7]
GO
/****** Object: StoredProcedure [dbo].[SP_frmWWInBillEx_Pre_submit] Script Date: 2020-09-01 17:26:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_frmWWInBillEx_Pre_submit]
@billno varchar(50)
as
--begin
--declare @result bit ----0为false ,1为true
--declare @CheckDes varchar(50) ----检查信息
--set @result=1
--if @result=1 ----结果为1返回信息 否则 返回空
--begin
--set @CheckDes=''
--end
--else
--begin
--set @checkDes=''
--end
--select @checkDes as checkdes,@result as result ----此为返回结果,不能删除,
--end
begin
if exists (select 1 from sysobjects where id=object_id('tempwwinQty2019') and type='U')
begin
drop table tempwwinQty2019
end

if exists (select 1 from sysobjects where id=object_id('tempResult') and type='U')
begin
drop table tempResult
end

begin
declare @mpsid varchar(50),@mpsmatid varchar(50)

create table tempResult(checkdes varchar(200))

create table tempwwinQty2019(matioid varchar(50),mpsid varchar(50),mpsmatid varchar(50),wpid varchar(20),
wwQty int,--外发数量
wwid VARCHAR(30),---外发加工单
CutQty int,--已裁数量
CJQty int,--裁剪入库数量
CJFHQty int,--裁剪发货登记数量
CHQty int,--车花入库数量
CHFHQty int,--车花发货数登记数量
YHQty int ,--印花入库数量
YHFHQty int ,--印花发货登记数量
CFQty int,--车缝入库数量
CFFHQty int,--车缝发货数登记数量
CPYHQty int,--成品印花入库数量
CPYHFHQty int,--成品印花发货登记数量
CPCHQty int,--成品车花入库数量
CPCHFHQty int,--成品车花发货登记数量
BZQty int,--包装入库数量
BZFHQty INT--包装发货登记数量

)

insert into tempwwinQty2019(matioid,mpsid,mpsmatid,wpid,wwQty,wwid)
select wwinlist.matioid,wwinlist.MPSID,wwinlist.MatID,WWInList.WPID,isnull(wwlist.qty,0) wwQty,WWInList.wwid from WWList(nolock)
left join WWInList(nolock) on WWList.matioid=WWInList.WWID and WWList.Lsh=WWInList.WWListLsh and wwlist.Wpid=WWInList.WPID and WWList.mpsid=WWInList.MPSID and wwlist.mpsmatid=WWInList.MatID
INNER JOIN wwin(NOLOCK) ON WWIn.MatIOID = WWInList.matioid AND wwin.ChannelID<>'111'
where WWInList.matioid=@billno and WWInList.MPSID not in
('1H765810=BDS',
'1I793810=BD1',
'1I793810=BD4',
'1I793810=BD6',
'1I793810=BD8',
'2I218010=T',
'AEF1303W0B=P',
'126G775A-ISR=BD1',
'126G775A-ISR=BD2',
'126G775A-ISR=BD3',
'126G775A-ISR=BD4',
'126G775A-ISR=BD5',
'126G776F19IY=BD2',
'BYSPF1226=R3',
'BYSPF1226=R4',
'17638610I=BD2',
'17638610I=BD5',
'17638610I=BD4',
'1H324110-CAD=BD'
)

 

--更新已裁数量
update tempwwinQty2019 set CutQty=isnull(b.TotalQty,0)+isnull(c.CP016Des3,0)
from tempwwinQty2019 a
left join (
select cut.mpsid,cut.matid,sum(isnull(cut.TotalQty,0))TotalQty --CP016Des3
from cut(nolock)
where cut.state='已审核' AND cut.IsNotMatchCut='No' -------and cut.Part='主身'
group by cut.mpsid,cut.matid) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid
left join mpsplanallnew(nolock) c on a.mpsid=c.mpsid
end

---裁剪入库时更新裁剪入库总数量
if(select top 1 wpid from tempwwinQty2019 )='003'
begin
update tempwwinQty2019 set CJQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where a.WPID='003'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.wwid=a.wwid
end

--车花入库时更新车花入库数量和已入账裁剪总数量
if(select top 1 wpid from tempwwinQty2019 )='001'
--已入账裁剪总数
begin
update tempwwinQty2019 set CJQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='003'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='001'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.Matioid=a.wwid
--更新车花合同入库数量
update tempwwinQty2019 set CHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where a.WPID='001'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
end

--印花入库时更新印花入库数量和已入账裁剪总数量
if(select top 1 wpid from tempwwinQty2019 )='002'
--已入账裁剪总数
begin
update tempwwinQty2019 set CJQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='003'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车花发货登记总数
update tempwwinQty2019 set YHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='002'
group by WWWPOutList.matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新车花合同入库数量
update tempwwinQty2019 set YHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where a.WPID='002'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
end

--车缝入库时更新已入账裁剪入库总数和车缝入库总数
if(select top 1 wpid from tempwwinQty2019 )='004'

begin
--更新车花
update tempwwinQty2019 set CHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='001'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='001'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.Matioid=a.wwid
--更新印花
update tempwwinQty2019 set YHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='002'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--印花发货登记总数
update tempwwinQty2019 set YHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='002'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新裁剪
update tempwwinQty2019 set CJQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='003'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车缝发货登记总数
update tempwwinQty2019 set CFFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='004'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新车缝合同入库数量
update tempwwinQty2019 set CFQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
INNER join wwin(nolock) c on a.MatIOID=c.MatIOID
where a.WPID='004'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
end

if(select top 1 wpid from tempwwinQty2019 )='007'
begin
--更新车花
update tempwwinQty2019 set CHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='001'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='001'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新印花
update tempwwinQty2019 set YHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='002'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--印花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='002'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新裁剪合同入库数量
update tempwwinQty2019 set CJQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='003'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--更新车缝合同入库数量
update tempwwinQty2019 set CFQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='004'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车缝发货登记总数
update tempwwinQty2019 set CFFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='004'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新成品印花合同入库数量
update tempwwinQty2019 set CPYHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where a.WPID='007'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
end

if(select top 1 wpid from tempwwinQty2019 )='008'
begin
--更新车花
update tempwwinQty2019 set CHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='001'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='001'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新印花
update tempwwinQty2019 set YHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='002'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--印花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='002'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新裁剪合同入库数量
update tempwwinQty2019 set CJQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='003'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--更新车缝合同入库数量
update tempwwinQty2019 set CFQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='004'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车缝发货登记总数
update tempwwinQty2019 set CFFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='004'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新成品车花合同入库数量
update tempwwinQty2019 set CPCHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where a.WPID='008'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
end

if(select top 1 wpid from tempwwinQty2019 )='005'
begin
--更新车花
update tempwwinQty2019 set CHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='001'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='001'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新印花
update tempwwinQty2019 set YHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='002'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--印花发货登记总数
update tempwwinQty2019 set CHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='002'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新裁剪合同入库数量
update tempwwinQty2019 set CJQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='003'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--更新车缝合同入库数量
update tempwwinQty2019 set CFQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
inner join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐' and a.WPID='004'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--车缝发货登记总数
update tempwwinQty2019 set CFFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='004'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新成品印花合同入库数量
update tempwwinQty2019 set CPCHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐'and a.WPID='007'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--成品印花发货登记总数
update tempwwinQty2019 set CPYHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='007'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新成品车花合同入库数量
update tempwwinQty2019 set CPYHQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where c.State='已入帐'and a.WPID='008'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
--成品车花发货登记总数
update tempwwinQty2019 set CPCHFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.State='已审核' and WWWPOutList.WPID='008'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--包装发货登记总数
update tempwwinQty2019 set BZFHQty=b.qty
from tempwwinQty2019 a
left join
(
select WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid,sum(WWWPOutList.Qty)qty
from WWWPOutList(NOLOCK)
where WWWPOutList.WPID='005'
group by WWWPOutList.Matioid,WWWPOutList.MPSID,WWWPOutList.MPSMatid
)b on a.mpsid=b.mpsid and a.mpsmatid=b.MPSMatid AND b.matioid=a.wwid
--更新包装合同入库数量
update tempwwinQty2019 set BZQty=b.qty
from tempwwinQty2019 a
left join(
select a.wwid,a.MPSID,a.MatID,sum(isnull(a.GoodQty,0))qty from WWInList(nolock) a
left join wwin(nolock) c on a.MatIOID=c.MatIOID
where a.WPID='005'
group by a.wwid,a.MPSID,a.MatID) b on a.mpsid=b.mpsid and a.mpsmatid=b.matid AND b.WWID=a.wwid
end

 

 

declare @result bit /*--0为false ,1为true */
declare @CheckDes varchar(50) /*--检查信息*/
declare @wpid varchar(10)
declare @wwid varchar(30)

DECLARE y_curr CURSOR FOR --申明游标
SELECT wwinlist.MPSID,wwinlist.MatID,WPID,WWInList.WWID from WWInList(nolock) where WWInList.matioid=@billno
OPEN y_curr --打开游标
FETCH NEXT FROM y_curr INTO @mpsid,@mpsmatid,@wpid,@wwid ----开始循环游标变量
WHILE(@@fetch_status=0)---返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
BEGIN
------------------------------------------------------------------------------------------------
--裁剪合同判断
if(@wpid='003')
begin
if not exists (select 1 from tempwwinQty2019 where isnull(CJQty,0)-isnull(CutQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='003' AND wwid=@wwid)
begin
set @CheckDes=''
end

if exists (select 1 from tempwwinQty2019 where isnull(CJQty,0)-isnull(CutQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='003'AND wwid=@wwid)
begin
set @CheckDes=@mpsid+'裁剪入库数大于已裁数'+char(13)+'不能提交'
end
end

------------------------------------------------------------------------------------------------
--车花合同判断
if(@wpid='001')
begin
if not exists (select 1 from tempwwinQty2019 where isnull(CHQty,0)-isnull(CHFHQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='001' AND wwid=@wwid)
begin
set @CheckDes=''
end

if exists (select 1 from tempwwinQty2019 where isnull(CHQty,0)-isnull(CHFHQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='001'AND wwid=@wwid)
begin
set @CheckDes=@mpsid+'车花入库数大于车花发货数'+char(13)+'不能提交'
end
end

------------------------------------------------------------------------------------------------
--印花合同判断
if(@wpid='002')
begin
if not exists (select 1 from tempwwinQty2019 where isnull(YHQty,0)-isnull(YHFHQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='002' AND wwid=@wwid )
begin
set @CheckDes=''
end

if exists (select 1 from tempwwinQty2019 where isnull(YHQty,0)-isnull(YHFHQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='002' AND wwid=@wwid)
begin
set @CheckDes=@mpsid+'印花入库数大于印花发货数'+char(13)+'不能提交'
end

end

------------------------------------------------------------------------------------------------
--车缝合同判断
if(@wpid='004')

begin
if not exists (select 1 from tempwwinQty2019 where isnull(CFQty,0)-isnull(CFFHQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='004' AND wwid=@wwid)
begin
set @CheckDes=''
end

if exists (select 1 from tempwwinQty2019 where isnull(CFQty,0)-isnull(CFFHQty,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='004' AND wwid=@wwid)
begin
set @CheckDes=@mpsid+'车缝入库数大于车缝发货数'+char(13)+'不能提交'
end
end

 

--包装合同判断
if(@wpid='005')
if not exists (select 1 from tempwwinQty2019 LEFT JOIN mpsplanallnew(NOLOCK) ON tempwwinQty2019.mpsid=MPSPlanAllnew.MPSID AND tempwwinQty2019.mpsmatid=MPSPlanAllnew.Matid WHERE isnull(BZQty,0)-isnull(BZFHQty,0)-ISNULL(CP016Des3,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='005' AND wwid=@wwid)
begin
set @CheckDes=''
end

if exists (select 1 from tempwwinQty2019 LEFT JOIN mpsplanallnew(NOLOCK) ON tempwwinQty2019.mpsid=MPSPlanAllnew.MPSID AND tempwwinQty2019.mpsmatid=MPSPlanAllnew.Matid WHERE isnull(BZQty,0)-isnull(BZFHQty,0)-ISNULL(CP016Des3,0)>0 and tempwwinQty2019.mpsid=@mpsid and tempwwinQty2019.mpsmatid=@mpsmatid and wpid='005' AND wwid=@wwid)
begin
set @CheckDes=@mpsid+'入库数大于包装发货数'+char(13)+'不能提交'
end

--判断是否录入重量
if ((select sum(case when isnull(WWInListSize.Des2,'')<>'' and PATINDEX('%[^0-9|.|]%', WWInListSize.Des2)<>1 then (convert(decimal(12,2),isnull(rtrim(ltrim(WWInListSize.Des2)),0))) else 0 end)
from WWInListSize(nolock) where WWInListSize.Matioid=@billno and WWInListSize.Type='B' and (WWInListSize.WPID='001' or WWInListSize.WPID='002') and isnull(WWInListSize.Des2,'')<>'.' AND isnull(WWInListSize.des2,'') not like '%p%'
group by WWInListSize.Matioid)<=0)
begin
set @CheckDes='没有录入重量，不能提交！！！'
end

--判断是否录入币种单价
if exists(select 1 from wwinlist(nolock) left join wwin(nolock) on wwinlist.MatIOID=WWIn.MatIOID where WWIn.BillType='01' and (wwinlist.WPID='001' or wwinlist.WPID='002') and wwin.MatIOID=@billno and isnull(wwinlist.CurrPrice,0)<=0)
begin
set @CheckDes='请录入单价！！！'
end

insert into tempResult(checkdes)
select @checkDes

FETCH NEXT FROM y_curr INTO @mpsid,@mpsmatid,@wpid,@wwid
END
CLOSE y_curr--关闭游标
DEALLOCATE y_curr --释放游标

 

---------------------------------------------------------------------------------------------

if exists (select 1 from tempResult where isnull(CheckDes,'')<>'')
begin
select @CheckDes=checkdes from tempResult where isnull(CheckDes,'')<>''
set @result=0
end

else
begin
set @CheckDes=''
set @result=1
end

select @checkDes as checkdes,@result as result /*--此为返回结果,不能删除,*/
end