CREATE PROC [dbo].[upDemo]
@BatchID varchar(50), ---批次
@LocationID varchar(20), ---仓库区位
@OrderNo varchar(20), ---订单号
@MatNo varchar(50), ---物料编号
@OrderUser varchar(20), ---订料组
@Supplier varchar(20), ---供应商
@Season varchar(20), ---季度
@Designer varchar(20), ---设计师
@TrackUser varchar(20), ---跟单员
@Flag varchar(20), ---上架状态
@mattype varchar(20), --物料类型
@isnewmat varchar(20), --新旧布
@colorid varchar(50),
@colordesc varchar(50),
@matdesc varchar(100),
@loginid varchar(20),
@crockid varchar(30),
@noindocno varchar(20),
@storeno varchar(20),
@banid varchar(50),
@revnumber varchar(30),
@reqname varchar(20), --订购人
@tracgpname varchar(20), --跟单组长
@ipaddress Varchar(20),
@isImportPDAData VARCHAR(10), --是否导入PDA数据 1：表示导入PDA数据 0：表示不导入PDA数据
@docType VARCHAR(10), --单据类型标记 0:表示开领料单; 1:表示开预留单;
@pdaDocNo VARCHAR(50), --PDA单号，把PDA同一时间点发送的批次认为是同一单据
@styleno varchar(50),
@bantype varchar(20),
@receivedpt varchar(60)
as
declare @sql varchar(8000),@charenter varchar(5)
declare @qx_12 bit
/* deal with 'vm_batchsendqty' _ begin */
--temp_c
,@sql_temp_c VARCHAR(8000)
,@temptb_c VARCHAR(50)
--temp_d
,@sql_temp_d VARCHAR(8000)
,@temptb_d VARCHAR(50)
--vm_batchsendqty
,@sql_e_0 VARCHAR(8000)
,@sql_f_0 VARCHAR(8000)
,@sql_h_0 VARCHAR(8000)
,@sql_i_0 VARCHAR(8000)
,@sql_vm_0 VARCHAR(8000)
,@sql_drop_tb_0 VARCHAR(1000)
,@temptb_e_0 VARCHAR(50)
,@temptb_f_0 VARCHAR(50)
,@temptb_h_0 VARCHAR(50)
,@temptb_i_0 VARCHAR(50)
,@temptb_vm_0 VARCHAR(50)
--vm_batchstoreqty
,@sql_d_01 VARCHAR(8000),@sql_d_02 VARCHAR(8000)
,@sql_e VARCHAR(8000)
,@sql_f VARCHAR(8000)
,@sql_h VARCHAR(8000)
,@sql_i VARCHAR(8000)
,@sql_vm VARCHAR(8000)
,@temptb_d_01 VARCHAR(50),@temptb_d_02 VARCHAR(50)
,@temptb_e VARCHAR(50)
,@temptb_f VARCHAR(50)
,@temptb_h VARCHAR(50)
,@temptb_i VARCHAR(50)
,@temptb_vm VARCHAR(50)
--temp_c
SET @temptb_c='TAB'+REPLACE(NEWID(),'-','')
--temp_d
SET @temptb_d='TAB'+REPLACE(NEWID(),'-','')
--vm_batchstoreqty
SET @temptb_d_01='TAB'+REPLACE(NEWID(),'-','') --1.1
SET @temptb_d_02='TAB'+REPLACE(NEWID(),'-','') --1.2
SET @temptb_e='TAB'+REPLACE(NEWID(),'-','') --2.
SET @temptb_f='TAB'+REPLACE(NEWID(),'-','') --3.
SET @temptb_h='TAB'+REPLACE(NEWID(),'-','') --4.
SET @temptb_i='TAB'+REPLACE(NEWID(),'-','') --5.
SET @temptb_vm='VM'+REPLACE(NEWID(),'-','') --6.
set @charenter=char(13)+char(10)
set @qx_12=ISNULL((select isnull(qx_7,0) from s_function where userid=@loginid and funcid='frmStockSendLL_msqDetaild'),0)
--vm_batchsendqty
SET @temptb_e_0='TAB'+REPLACE(NEWID(),'-','') --2.
SET @temptb_f_0='TAB'+REPLACE(NEWID(),'-','') --3.
SET @temptb_h_0='TAB'+REPLACE(NEWID(),'-','') --4.
SET @temptb_i_0='TAB'+REPLACE(NEWID(),'-','') --5.
SET @temptb_vm_0='VM'+REPLACE(NEWID(),'-','') --6.
--temp_c
SET @sql_temp_c='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_c+''') and [type]=''U'') drop table '+@temptb_c+'
Select c.pre_number,c.pre_line,SUM(a.length) as outlength
into '+@temptb_c+'
from MaterialOut_Detail a left join
MaterialOut_Head b on b.DocNo=a.DocNo left join
MaterialSend_DetailD c on c.DocNo=b.SendNo and c.BatchID=a.BatchID
where c.resno is null and b.SendNo not like ''BCK%'' and c.pre_number is not null
--group by c.pre_number,c.pre_line
'
IF @BatchID IS NOT NULL SET @sql_temp_c=RTRIM(@sql_temp_c)+ ' and a.BatchID like ' +@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_temp_c=RTRIM(@sql_temp_c)+ ' and a.MatNo like ' +@MatNo +@charenter
SET @sql_temp_c=RTRIM(@sql_temp_c)+ ' group by c.pre_number,c.pre_line' +@charenter
--temp_d
SET @sql_temp_d='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_d+''') and [type]=''U'') drop table '+@temptb_d+'
Select a.pre_number,a.pre_line,SUM(length) as sendlength
into '+@temptb_d+'
from MaterialSend_DetailD a inner join
MaterialSend_head c on c.docno=a.docno left join
MaterialOut_Head b on b.SendNo=a.DocNo
where c.indate>=getdate()-15 and a.resno is null and b.DocNo is null and a.docno like ''SED%'' and a.pre_number is not null
-- group by a.pre_number,a.pre_line
'
IF @BatchID IS NOT NULL SET @sql_temp_d=RTRIM(@sql_temp_d)+ ' and a.BatchID like ' +@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_temp_d=RTRIM(@sql_temp_d)+ ' and a.MatNo like ' +@MatNo +@charenter
SET @sql_temp_d=RTRIM(@sql_temp_d)+ ' group by a.pre_number,a.pre_line' +@charenter
--(一)
--2. @temptb_e_0 正常领料未出货
SET @sql_e_0='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_e_0+''') and [type]=''U'') drop table '+@temptb_e_0+'
select batchid, SUM(length)as length,SUM(qtyi) as qtyi
into '+@temptb_e_0+'
from materialsend_detaild a(nolock) inner join
materialsend_head d(nolock) on a.docno=d.docno left join
materialout_head c(nolock) on a.docno=c.sendno
where d.checkflag=1 and a.docno like ''SED%'' and isnull(a.resno,'''')='''' and isnull(c.docno,'''')=''''
--group by BatchID
'
IF @BatchID IS NOT NULL SET @sql_e_0=RTRIM(@sql_e_0)+' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_e_0=RTRIM(@sql_e_0)+' and a.MatNo like '+@MatNo +@charenter
SET @sql_e_0=RTRIM(@sql_e_0)+ ' group by BatchID' +@charenter
--3.@temptb_f_0 预留数量
SET @sql_f_0='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_f_0+''') and [type]=''U'') drop table '+@temptb_f_0+'
select batchid, SUM(length)as length,SUM(qtyi) as qtyi
into '+@temptb_f_0+'
from materialsend_detaild a(nolock) inner join
materialsend_head d(nolock) on a.docno=d.docno
where d.checkflag=1 and a.docno like ''RES%''
-- group by BatchID
'
IF @BatchID IS NOT NULL SET @sql_f_0=RTRIM(@sql_f_0)+' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_f_0=RTRIM(@sql_f_0)+' and a.MatNo like '+@MatNo +@charenter
SET @sql_f_0=RTRIM(@sql_f_0)+ ' group by BatchID' +@charenter
--4.@temptb_h_0 领预留数量
SET @sql_h_0='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_h_0+''') and [type]=''U'') drop table '+@temptb_h_0+'
select batchid, SUM(length)as length, SUM(qtyi) as qtyi
into '+@temptb_h_0+'
from materialsend_detaild a(nolock) inner join
materialsend_head d(nolock) on a.docno=d.docno
where d.checkflag=1 and a.resno like ''RES%''
-- group by BatchID
'
IF @BatchID IS NOT NULL SET @sql_h_0=RTRIM(@sql_h_0)+ ' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_h_0=RTRIM(@sql_h_0)+ ' and a.MatNo like '+@MatNo +@charenter
SET @sql_h_0=RTRIM(@sql_h_0)+ ' group by BatchID' +@charenter
--5.@temptb_i_0 领预留未出数量
SET @sql_i_0='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_i_0+''') and [type]=''U'') drop table '+@temptb_i_0+'
select batchid, SUM(length)as length, SUM(qtyi) as qtyi
into '+@temptb_i_0+'
from materialsend_detaild a(nolock) inner join
materialsend_head d(nolock) on a.docno=d.docno left join
materialout_head c(nolock) on a.docno=c.sendno
where d.checkflag=1 and a.docno like ''SED%'' and a.resno like ''RES%'' and c.docno is null
-- group by BatchID
'
IF @BatchID IS NOT NULL SET @sql_i_0=RTRIM(@sql_i_0)+ ' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_i_0=RTRIM(@sql_i_0)+ ' and a.MatNo like '+@MatNo +@charenter
SET @sql_i_0=RTRIM(@sql_i_0)+ ' group by BatchID' +@charenter
--6.@temptb_vm_0
SET @sql_vm_0='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_vm_0+''') and [type]=''U'') drop table '+@temptb_vm_0+'
select a.BatchID,a.MatNo,a.volumeid,a.mattype,a.mattypename,a.mattypedesc,a.MatDesc,a.OrderNo,a.line,a.orderserial,a.lockflag,
a.ColorID,a.ColorDesc,a.Season,a.reqperson,a.Designer,a.dsgdeptdesc,a.supplierid, a.SupplierDesc,a.Package,a.batch,
a.conversion,a.crockid,a.dsgpoperson,a.poprice,a.isoldmat,a.storeno,a.podept,a.podeptdesc,a.reqdept,a.reqdeptdesc,
a.revperson,a.supply_colorname,a.part,a.dsgstyle,a.element,a.needqty,a.banid,a.goodsperiod,a.styleno,a.rev_number,
a.clothareano,a.weight as kz,a.dsgclothareano,a.itemtype,
b.LocationID,b.sampleqty,a.Lunit,a.Wunit,
b.Length as StoreLength,--库存数量
b.weight as StoreWeight,--库存重量
b.inlength,--入库数量
b.inweight,
(case when b.length-isnull(e.length,0)-isnull(i.length,0)<0 then 0 else b.length-isnull(e.length,0)-isnull(i.length,0) end) avlength,--可用数量=库存-正常未出-预留未出
(case when b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0)<0 then 0 else b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0) end) avweight,--可用重量
case WHEN isnull(f.length,0)-isnull(h.length,0) < 0 THEN b.length-isnull(e.length,0)
WHEN b.length-isnull(e.length,0)-isnull(i.length,0)-(isnull(f.length,0)-isnull(h.length,0)) < 0 THEN 0
else b.length-isnull(e.length,0)-isnull(i.length,0)-(isnull(f.length,0)-isnull(h.length,0))
end as avnoreslength, --实际可用数量= 库存-正常未出-预留未出-预留剩余
case WHEN isnull(f.qtyi,0)-isnull(h.qtyi,0) < 0 THEN b.weight-isnull(e.qtyi,0)
WHEN b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0)-(isnull(f.qtyi,0)-isnull(h.qtyi,0)) < 0 THEN 0
else b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0)-(isnull(f.qtyi,0)-isnull(h.qtyi,0))
end as avnoresweight, --实际可用重量
e.length as nochecknoreslen , --未审核(不包括领预留)|数量
e.qtyi as nochecknoresweight,
h.length as reslylen , --预留|已领数量(b)
h.qtyi as reslyqtyi,
case when (isnull(f.length,0)-isnull(h.length,0)) > 0 then isnull(f.length,0)-isnull(h.length,0)
else 0.0
end as reslensy , --预留|剩余数量(c=a-b)
case when (isnull(f.qtyi,0)-isnull(h.qtyi,0)) > 0 then isnull(f.qtyi,0)-isnull(h.qtyi,0)
else 0.0
end as resweightsy,
f.length as reslen , --预留|最初数量(a)
f.qtyi as resweight
into '+@temptb_vm_0+'
from materialbatchinfo (nolock) a left join
MaterialStoreQty (nolock) b on b.BatchID=a.BatchID left join
'+@temptb_e_0+' e on e.BatchID=a.BatchID left join
'+@temptb_f_0+' f on f.BatchID=a.BatchID left join
'+@temptb_h_0+' h on h.BatchID=a.BatchID left join
'+@temptb_i_0+' i on i.BatchID=a.BatchID
where 1=1
'
IF @MatNo IS NOT NULL SET @sql_vm_0=RTRIM(@sql_vm_0)+ ' and a.MatNo like '+@MatNo +@charenter
IF @mattype IS NOT NULL SET @sql_vm_0=RTRIM(@sql_vm_0)+ ' and a.mattype like '+@mattype +@charenter
--(二)vm_batchstoreqty
--1.1 @temptb_d_01
SET @sql_d_01='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_d_01+''') and [type]=''U'') drop table '+@temptb_d_01+'
select mm.docno,mm.batchid,matno,length,lunit,weight,wunit,conversion, turnoldragsflag,turnoldragsdate
,cc.CollectDate ,cc.color_name,cc.color_desc,mm.remark,'''' as returndpt
into '+@temptb_d_01+'
from MaterialCollect_detail (nolock) mm inner join
MaterialCollect_head cc on mm.docno=cc.docno
where 1=1
'
IF @BatchID IS NOT NULL SET @sql_d_01=RTRIM(@sql_d_01)+' and mm.batchid like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_d_01=RTRIM(@sql_d_01)+' and mm.matno like '+@MatNo +@charenter
--1.2 @temptb_d_02
SET @sql_d_02='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_d_02+''') and [type]=''U'') drop table '+@temptb_d_02+'
select rr.docno,newbatchid,rr.matno,length,lunit,weight,wunit,conversion ,'''' AS turnoldragsflag,'''' AS turnoldragsdate
,ss.InDate CollectDate,ss.ColorID color_name,ss.ColorDesc color_desc ,rr.remark,returndpt
into '+@temptb_d_02+'
from MaterialReturnStore_Detail (nolock) rr inner join
MaterialReturnStore_head ss on rr.docno=ss.docno
where 1=1
'
IF @BatchID IS NOT NULL SET @sql_d_02=RTRIM(@sql_d_02)+' and newbatchid like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_d_02=RTRIM(@sql_d_02)+' and rr.matno like '+@MatNo +@charenter
--2. @temptb_e 正常领料未出货
SET @sql_e='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_e+''') and [type]=''U'') drop table '+@temptb_e+'
select batchid, SUM(length)as length,SUM(qtyi) as qtyi
into '+@temptb_e+'
from materialsend_detaild a(nolock)
inner join materialsend_head d(nolock) on a.docno=d.docno
left join materialout_head c(nolock) on a.docno=c.sendno
where d.checkflag=1 and a.docno not like ''RES%'' and a.resno is null and c.docno is null
'
IF @BatchID IS NOT NULL SET @sql_e=RTRIM(@sql_e)+' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_e=RTRIM(@sql_e)+' and a.MatNo like '+@MatNo +@charenter
SET @sql_e=RTRIM(@sql_e)+ ' group by BatchID' +@charenter
--3. @temptb_f 预留数量
SET @sql_f='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_f+''') and [type]=''U'') drop table '+@temptb_f+'
select batchid, SUM(length)as length,SUM(qtyi) as qtyi
into '+@temptb_f+'
from materialsend_detaild a(nolock) inner join
materialsend_head d(nolock) on a.docno=d.docno
where d.checkflag=1 and a.docno like ''RES%''
'
IF @BatchID IS NOT NULL SET @sql_f=RTRIM(@sql_f)+ ' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_f=RTRIM(@sql_f)+ ' and a.MatNo like '+@MatNo +@charenter
SET @sql_f=RTRIM(@sql_f)+ ' group by BatchID' +@charenter
--4. @temptb_h 领预留数量
SET @sql_h='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_h+''') and [type]=''U'') drop table '+@temptb_h+'
select batchid, SUM(length)as length, SUM(qtyi) as qtyi
into '+@temptb_h+'
from materialsend_detaild a(nolock) inner join
materialsend_head d(nolock) on a.docno=d.docno
where d.checkflag=1 and a.resno like ''RES%''
'
IF @BatchID IS NOT NULL SET @sql_h=RTRIM(@sql_h)+ ' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_h=RTRIM(@sql_h)+ ' and a.MatNo like '+@MatNo +@charenter
SET @sql_h=RTRIM(@sql_h)+ ' group by BatchID' +@charenter
--5. @temptb_i 领预留未出数量
SET @sql_i='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_i+''') and [type]=''U'') drop table '+@temptb_i+'
select batchid, SUM(length)as length, SUM(qtyi) as qtyi
into '+@temptb_i+'
from materialsend_detaild a(nolock) inner join
materialsend_head d(nolock) on a.docno=d.docno
left join materialout_head c(nolock) on a.docno=c.sendno
where d.checkflag=1 and a.docno not like ''RES%'' and a.resno like ''RES%'' and c.docno is null
--group by BatchID
'
IF @BatchID IS NOT NULL SET @sql_i=RTRIM(@sql_i)+ ' and a.BatchID like '+@BatchID +@charenter
IF @MatNo IS NOT NULL SET @sql_i=RTRIM(@sql_i)+ ' and a.MatNo like '+@MatNo +@charenter
SET @sql_i=RTRIM(@sql_i)+ ' group by BatchID' +@charenter
--6. @temptb_vm
SET @sql_vm='
if exists(select 1 from sysobjects where id=object_id('''+@temptb_vm+''') and [type]=''U'') drop table '+@temptb_vm+'
select a.BatchID,a.MatNo,a.volumeid,a.mattype,a.mattypename,a.mattypedesc,a.MatDesc,a.OrderNo,a.line,a.orderserial,a.lockflag,
a.ColorID,a.ColorDesc,a.Season,a.reqperson,a.Designer,a.dsgdeptdesc,a.supplierid, a.SupplierDesc,a.Package,a.batch,
a.conversion,a.crockid,a.dsgpoperson,a.poprice,a.isoldmat,a.storeno,a.podept,a.podeptdesc,a.reqdept,a.reqdeptdesc,
a.revperson,a.supply_colorname,a.part,a.dsgstyle,a.element,a.needqty,a.banid,a.goodsperiod,a.styleno,a.rev_number,
a.isoptionalflag,a.optionalflaguser,a.optionalflagdate,a.clothareano,a.weight as kz,a.dsgclothareano,a.itemtype,
(case when (a.mattypename=''针织'' or a.mattypename=''毛织'' or a.mattypename=''鸭绒'')
then isnull(a.poprice,0)*isnull(b.weight,0)
else isnull(a.poprice,0)*isnull(b.Length,0)
end) as amount,
b.LocationID,b.sampleqty,a.Lunit,a.Wunit,
b.Length as StoreLength,--库存数量
b.weight as StoreWeight,--库存重量
d.Length inlength,--入库数量
d.Weight inweight,
(case when b.length-isnull(e.length,0)-isnull(i.length,0)<0 then 0 else b.length-isnull(e.length,0)-isnull(i.length,0) end) avlength,--可用数量=库存-正常未出-预留未出
(case when b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0)<0 then 0 else b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0) end) avweight,--可用重量
case WHEN isnull(f.length,0)-isnull(h.length,0) < 0 THEN b.length-isnull(e.length,0)
WHEN b.length-isnull(e.length,0)-isnull(i.length,0)-(isnull(f.length,0)-isnull(h.length,0)) < 0 THEN 0
else b.length-isnull(e.length,0)-isnull(i.length,0)-(isnull(f.length,0)-isnull(h.length,0))
end as avnoreslength, --实际可用数量= 库存-正常未出-预留未出-预留剩余
case WHEN isnull(f.qtyi,0)-isnull(h.qtyi,0) < 0 THEN b.weight-isnull(e.qtyi,0)
WHEN b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0)-(isnull(f.qtyi,0)-isnull(h.qtyi,0)) < 0 THEN 0
else b.weight-isnull(e.qtyi,0)-isnull(i.qtyi,0)-(isnull(f.qtyi,0)-isnull(h.qtyi,0))
end as avnoresweight, --实际可用重量
e.length as nochecknoreslen , --未审核(不包括领预留)|数量
e.qtyi as nochecknoresweight,
h.length as reslylen , --预留|已领数量(b)
h.qtyi as reslyqtyi,
case when (isnull(f.length,0)-isnull(h.length,0)) > 0 then isnull(f.length,0)-isnull(h.length,0)
else 0.0
end as reslensy , --预留|剩余数量(c=a-b)
case when (isnull(f.qtyi,0)-isnull(h.qtyi,0)) > 0 then isnull(f.qtyi,0)-isnull(h.qtyi,0)
else 0.0
end as resweightsy,
f.length as reslen , --预留|最初数量(a)
f.qtyi as resweight,b.inoutdate,d.remark,d.CollectDate,d.DocNo as coldocno,d.turnoldragsdate,d.returndpt
INTO '+@temptb_vm+'
from materialbatchinfo (nolock) a left join
MaterialStoreQty (nolock) b on b.BatchID=a.BatchID left join
(select * from '+@temptb_d_01+' union all select * from '+@temptb_d_02+') d on a.batchid=d.BatchID left join
'+@temptb_e+' e on e.BatchID=a.BatchID left join
'+@temptb_f+' f on f.BatchID=a.BatchID left join
'+@temptb_h+' h on h.BatchID=a.BatchID left join
'+@temptb_i+' i on i.BatchID=a.BatchID
where 1=1
'
IF @MatNo IS NOT NULL SET @sql_vm=RTRIM(@sql_vm)+ ' and a.MatNo like '+@MatNo +@charenter
IF @mattype IS NOT NULL SET @sql_vm=RTRIM(@sql_vm)+ ' and a.mattype like '+@mattype +@charenter
/* end */
if (@banid is not null) or (@styleno is not null)
begin
set @sql='Select distinct cast(0 as bit ) as selectflag,
a.LocationID,y.item_code MatNo,y.item_desc MatDesc,a.BatchID,a.volumeid,a.Package,
a.avnoresweight Qtyi,
a.Wunit as Qunit,a.storelength stocklength,
a.storeweight stockweight,
a.Wunit,a.conversion,
a.avnoreslength as Length,
a.lunit,
a.avnoreslength as usablelength,
(case when isnull(a.colorid,'''')<>'''' then a.colorid else y.color_name end) colorid,
(case when isnull(a.colordesc,'''')<>'''' then a.colordesc else y.color_desc end) colordesc,
a.mattype,y.Season,a.reqperson as person,
case when reqd.req_serial is null then a.dsgpoperson else reqd.bantracknm end as dsgpoperson,a.crockid,a.sampleqty,NULL as resno,NULL as resline,a.storeno,
case when reqd.req_serial is null then y.styleno else reqd.styleno end as styleno,case when reqd.req_serial is null then a.banid else reqd.banid end as templetid ,
a.itemtype,
case when a.storeno in (''B'',''C'') then ''主料'' when a.storeno=''A'' then ''辅料'' else '''' end as itemtype1,
--y.itemtypedesc,
a.OrderNo,a.inoutdate,a.SupplierDesc,a.inLength as YLength,a.inWeight AS YWeight,a.supplierid,a.podeptdesc,a.supply_colorname, a.rev_number,
a.mattypename,a.mattypedesc,
case when reqd.req_serial is null then a.banid else reqd.banid end as banid,
case when reqd.req_serial is null then a.part else reqd.part end as part,
case when reqd.req_serial is null then a.Designer else reqd.dsgdeptname end as Designer,
case when reqd.req_serial is null then a.dsgstyle else reqd.dsgstyle end as dsgstyle,
reqd.pre_number,reqd.pre_line,reqd.needqty,pre.unit as preunit,
case when pre.unit<>a.Lunit and pre.unit=''磅'' then (pre.needqty-ISNULL(pre.cancleQty,0)-ISNULL(pre.cancleQty2,0))*a.sampleqty/a.conversion
else
(pre.needqty-ISNULL(pre.cancleQty,0)-ISNULL(pre.cancleQty2,0)) end preneedqty,(isnull(c.outlength,0)+ISNULL(d.sendlength,0)) bcklength,
case when a.storeno in (''B'',''C'') and a.avnoreslength-((case when pre.unit<>a.Lunit and pre.unit=''磅'' then pre.rtnqty*a.sampleqty/a.conversion else pre.rtnqty end)-isnull(c.outlength,0)-ISNULL(d.sendlength,0))>=0 then
((case when pre.unit<>a.Lunit and pre.unit=''磅'' then pre.rtnqty*a.sampleqty/a.conversion else pre.rtnqty end)-isnull(c.outlength,0)-ISNULL(d.sendlength,0))
when a.storeno in (''B'',''C'') and a.avnoreslength-((case when pre.unit<>a.Lunit and pre.unit=''磅'' then pre.rtnqty*a.sampleqty/a.conversion else pre.rtnqty end)-isnull(c.outlength,0)-ISNULL(d.sendlength,0))<0 then
a.avnoreslength
when a.storeno in (''A'') and a.avnoreslength-(pre.rtnqty-isnull(c.outlength,0)-ISNULL(d.sendlength,0))>=0
then pre.rtnqty-isnull(c.outlength,0)-ISNULL(d.sendlength,0)
when a.storeno in (''A'') and a.avnoreslength-(pre.rtnqty-isnull(c.outlength,0)-ISNULL(d.sendlength,0))<0
then a.avnoreslength
else a.avnoreslength end as prerestqty'
IF @isImportPDAData='1'
set @sql=@sql+',m.pdaid,m.pkey '
set @sql=@sql+' into #tmpmaterialsend
from dsg_bi.dbo.Ban_Material_Needs y
left join dsg_prelist_detail(nolock) pre on pre.banid=y.matioid and pre.item_code=y.item_code and pre.color_name=y.itemcolorid
left join req_detaild reqd(nolock) on reqd.pre_number=pre.pre_number and reqd.pre_line=pre.pre_line
left join req_head reqh(nolock) on reqh.req_serial=reqd.req_serial
left join '+@temptb_vm+' a
on a.orderserial=reqd.req_serial and a.line=reqd.req_line
left join materialtype (nolock) b on a.mattype=b.mattypeid
left join MaterialLocationInfo (nolock) k on a.LocationID = k.locationID
left join '+@temptb_c+' c on c.pre_number=reqd.pre_number and c.pre_line=reqd.pre_line --已出库
left join '+@temptb_d+' d on d.pre_number=reqd.pre_number and d.pre_line=reqd.pre_line --未出库
'+@charenter
IF @isImportPDAData='1'
begin
set @sql=@sql+' INNER JOIN (SELECT batchid,pdaid,pkey FROM dbo.pdadsgclothsendll_res (NOLOCK)
WHERE userid='''+@loginid+''''+' AND isend=''0'' AND docType='''+@docType+'''' +' AND datediff(day,worktime,GETDATE())<7 '+@charenter
IF @pdaDocNo IS NOT NULL
BEGIN
SET @sql = @sql + ' and pkey like ' + @pdadocno + @charenter
END
SET @sql = @sql + ' ) m ON a.batchid=m.batchid ' + @charenter
end
SET @sql = @sql + 'where 1=1 and (k.lockflag is null or k.lockflag <> ''1'') '+@charenter
if @qx_12=1
SET @sql = @sql +' and isnull(reqh.tjkflag,0)=0 '
if @bantype is not null
set @sql=@sql+' and y.bantype like '+@bantype+@charenter
else
set @sql=@sql+' and y.bantype=''齐色'' '+@charenter
if @banid is not null set @sql=@sql+' and y.matioid like '+@banid+@charenter
if @styleno is not null set @sql=@sql+' and y.styleno like '+@styleno+@charenter
end
else
begin
set @sql='Select top 4000 cast(0 as bit ) as selectflag,a.LocationID,a.MatNo,a.MatDesc,a.BatchID,a.volumeid,a.Package,
a.avnoresweight Qtyi,
a.Wunit as Qunit,a.storelength stocklength,--库存数量
a.storeweight stockweight,
a.Wunit,a.conversion,
a.avnoreslength as Length,
a.Lunit,
a.avnoreslength as usablelength,--实际可用
a.ColorID,a.ColorDesc, a.mattype,a.Season,a.reqperson as person,
case when reqd.req_serial is null then a.dsgpoperson else reqd.bantracknm end as dsgpoperson,a.crockid,a.sampleqty,NULL as resno,NULL as resline,a.storeno,
case when reqd.req_serial is null then a.styleno else reqd.styleno end as styleno,case when reqd.req_serial is null then a.banid else reqd.banid end as templetid ,
a.itemtype,
case when a.storeno in (''B'',''C'') then ''主料'' when a.storeno=''A'' then ''辅料'' else '''' end as itemtype1,
a.OrderNo,a.SupplierDesc,a.inLength as YLength,a.inWeight AS YWeight,a.supplierid,a.podeptdesc,a.supply_colorname, a.rev_number,
a.mattypename,a.mattypedesc,
case when reqd.req_serial is null then a.banid else reqd.banid end as banid,
case when reqd.req_serial is null then a.part else reqd.part end as part,
case when reqd.req_serial is null then a.Designer else reqd.dsgdeptname end as Designer,
case when reqd.req_serial is null then a.dsgstyle else reqd.dsgstyle end as dsgstyle,
reqd.pre_number,reqd.pre_line,pre.unit as preunit,'
if @receivedpt='订购'
set @sql=@sql+' case when pre.unit<>a.Lunit and pre.unit=''磅'' then (pre.needqty+reqd.qtyotherorder-ISNULL(pre.cancleQty,0)-ISNULL(pre.cancleQty2,0))*a.sampleqty/a.conversion
else
(pre.needqty+reqd.qtyotherorder-ISNULL(pre.cancleQty,0)-ISNULL(pre.cancleQty2,0)) end preneedqty,'
else
set @sql=@sql+' case when pre.unit<>a.Lunit and pre.unit=''磅'' then (pre.needqty-ISNULL(pre.cancleQty,0)-ISNULL(pre.cancleQty2,0))*a.sampleqty/a.conversion
else
(pre.needqty-ISNULL(pre.cancleQty,0)-ISNULL(pre.cancleQty2,0)) end preneedqty,'
set @sql=@sql+'(isnull(c.outlength,0)+ISNULL(d.sendlength,0)) bcklength,
case when a.storeno in (''B'',''C'') and a.avnoreslength-((case when pre.unit<>a.Lunit and pre.unit=''磅'' then pre.rtnqty*a.sampleqty/a.conversion
else pre.rtnqty end)-isnull(c.outlength,0)-ISNULL(d.sendlength,0))>=0
then ((case when pre.unit<>a.Lunit and pre.unit=''磅'' then pre.rtnqty*a.sampleqty/a.conversion
else pre.rtnqty end)-isnull(c.outlength,0)-ISNULL(d.sendlength,0))
when a.storeno in (''B'',''C'') and a.avnoreslength-((case when pre.unit<>a.Lunit and pre.unit=''磅'' then pre.rtnqty*a.sampleqty/a.conversion
else pre.rtnqty end)-isnull(c.outlength,0)-ISNULL(d.sendlength,0))<0 then a.avnoreslength
when a.storeno in (''A'') and a.avnoreslength-(pre.rtnqty-isnull(c.outlength,0)-ISNULL(d.sendlength,0))>=0
then pre.rtnqty-isnull(c.outlength,0)-ISNULL(d.sendlength,0)
when a.storeno in (''A'') and a.avnoreslength-(pre.rtnqty-isnull(c.outlength,0)-ISNULL(d.sendlength,0))<0
then a.avnoreslength
else a.avnoreslength end as prerestqty '
IF @isImportPDAData='1'
set @sql=@sql+',m.pdaid,m.pkey '
set @sql=@sql+' into #tmpmaterialsend
--from vm_batchsendqty a
from '+@temptb_vm_0+' a
left join materialtype (nolock) b on a.mattype=b.mattypeid
left join req_detaild reqd(nolock) on a.orderserial=reqd.req_serial and a.line=reqd.req_line
left join req_head reqh(nolock) on reqh.req_serial=reqd.req_serial
left join MaterialLocationInfo (nolock) k on a.LocationID = k.locationID
Left join dsg_prelist_detail(nolock) pre on pre.pre_number=reqd.pre_number and pre.pre_line=reqd.pre_line
left join '+@temptb_c+' c on c.pre_number=reqd.pre_number and c.pre_line=reqd.pre_line --已出库
left join '+@temptb_d+' d on d.pre_number=reqd.pre_number and d.pre_line=reqd.pre_line --未出库
'+@charenter
IF @isImportPDAData='1'
begin
set @sql=@sql+' INNER JOIN (SELECT batchid,pdaid,pkey FROM dbo.pdadsgclothsendll_res (NOLOCK)
WHERE userid='''+@loginid+''''+' AND isend=''0'' AND docType='''+@docType+'''' +' AND datediff(day,worktime,GETDATE())<7 '+@charenter
IF @pdaDocNo IS NOT NULL
BEGIN
SET @sql = @sql + ' and pkey like ' + @pdadocno + @charenter
END
SET @sql = @sql + ' ) m ON a.batchid=m.batchid ' + @charenter
end
SET @sql = @sql + 'where 1=1 and a.storelength>0 and a.storeweight>0 and a.avnoreslength >0 and a.avnoresweight>0
and (k.lockflag is null or k.lockflag <> ''1'') '+@charenter
if @qx_12=1
SET @sql = @sql +' and isnull(reqh.tjkflag,0)=0 '
end
if @BatchID is not null set @sql=@sql+
' and a.BatchID like '+@BatchID+@charenter
if @LocationID is not null set @sql=@sql+
' and a.LocationID like '+@LocationID+@charenter
if @docType='1' set @sql=@sql+ --如果是开预留单，排除此货架
' AND a.LocationID <>''BA666666'''+@charenter
IF @docType <> '1'--如果是开领料单和退料出库单，只有以下用户可以开单
AND @loginid NOT IN ( 'BF0011', 'BF1854', 'BF0625', 'BF0014', 'BF0632',
'BF0667', 'BF0028', 'BF0025', 'BF1683', 'BF0012',
'BF1255', 'BF0030', 'BF0043', 'BF0675', 'BF0609',
'BF1287', 'BF0006', 'BF0013', 'BF0020', 'BF1781',
'BF1896', 'BF1921','SA' )
SET @sql = @sql + ' AND a.LocationID <>''BA666666''' + @charenter
if @MatNo is not null set @sql=@sql+
' and a.MatNo like '+@MatNo+@charenter
if @OrderNo is not null set @sql=@sql+
' and a.OrderNo like '+@OrderNo+@charenter
if @OrderUser is not null set @sql=@sql+
' and a.reqperson like '+@OrderUser+@charenter
if @Supplier is not null set @sql=@sql+
' and a.SupplierID like '+@Supplier+@charenter
if @Season is not null set @sql=@sql+
' and a.Season like '+@Season+@charenter
if @Designer is not null set @sql=@sql+
' and ( reqd.dsgdeptname like '+@Designer+' or a.Designer like '+@Designer+')'+ @charenter
if @TrackUser is not null set @sql=@sql+
' and (reqd.bantracknm like '+@TrackUser+' or a.dsgpoperson like '+@TrackUser+')'+ @charenter
if @colorid is not null set @sql=@sql+
' and a.colorid = '+ replace(@colorid,'%','')+@charenter
if @colordesc is not null set @sql=@sql+
' and a.colordesc like '+@colordesc+@charenter
if @matdesc is not null set @sql=@sql+
' and a.matdesc like '+@matdesc+@charenter
if @Flag=1 set @sql=@sql+
' and isnull(a.locationid,'''')<>'''''+@charenter
if @Flag=2 set @sql=@sql+
' and isnull(a.locationid,'''')='''''+@charenter
if @isnewmat=1 set @sql=@sql+
' and isnull(a.isoldmat,0)>0'+@charenter
if @isnewmat=2 set @sql=@sql+
' and isnull(a.isoldmat,0)=0'+@charenter
if @mattype is not null set @sql=@sql+
' and a.mattype like '+@mattype+@charenter
if @crockid is not null set @sql=@sql+
' and isnull(a.crockid,'''') like '+@crockid+@charenter
if @storeno is not null set @sql=@sql+
' and a.storeno= '+@storeno+@charenter
if @revnumber is not null set @sql=@sql+
' and a.rev_number = '+@revnumber+@charenter
if @reqname is not null set @sql=@sql+
' and (reqd.reqperson like '+@reqname + ')'+ @charenter
if @tracgpname is not null set @sql=@sql+
' and (reqd.rptgroupnm like '+@tracgpname + ')'+ @charenter
set @sql=@sql+' select * from #tmpmaterialsend '
--删除临时表
SET @sql_drop_tb_0=' drop table ' + @temptb_e_0
+ ' drop table ' + @temptb_f_0
+ ' drop table ' + @temptb_h_0
+ ' drop table ' + @temptb_i_0
+ ' drop table ' + @temptb_vm_0
+ ' drop table '+@temptb_d_01
+ ' drop table '+@temptb_d_02
+ ' drop table '+@temptb_e
+ ' drop table '+@temptb_f
+ ' drop table '+@temptb_h
+ ' drop table '+@temptb_i
+ ' drop table '+@temptb_vm
+ ' drop table '+@temptb_c
+ ' drop table '+@temptb_d
EXEC ( @sql_temp_c+@sql_temp_d+ @sql_e_0+@sql_f_0+@sql_h_0+@sql_i_0+@sql_vm_0 )
EXEC ( @sql_d_01+@sql_d_02+@sql_e+@sql_f+@sql_h+@sql_i+@sql_vm)
exec(@sql + @sql_drop_tb_0)
浙公网安备 33010602011771号