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)
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
