📄 mrp_enter_passmo.pas
字号:
+' BillTypeCode,EmployeeCode,Mono,MoLineno, '
+' oWhPositionCode, '
+' WhPositionCode '
+' From InvOutBill with(index(InvOutBillno)) '
+' Where InvBillNo=@Billno '
+' and mono='+quotedstr(mono)
+' and MoLineno='+inttostr(MoLineno)
+' select @InBillid=@@identity '
+' Select @InBillid as Billid,identity(int,1,1) as ss,ItemCode, '
+' BatchNo,InvBillQty '
+' into #tmpBillline2 '
+' From InvOutBill with (index(InvOutBillno)),InvOutBillLine with (index(Relation_10096_FK)) '
+' where InvOutBill.InvBillid=InvOutBillline.InvBillid '
+' and InvOutBill.InvBillNo=@Billno '
+' and InvOutBill.mono='+quotedstr(mono)
+' and InvOutBill.MoLineno='+inttostr(MoLineno)
+' Insert InvInBillLine(InvBillid,InvBillLineNo,ItemCode, '
+' BatchNo,InvBillQty) '
+' select * from #tmpBillline2 '
+' drop table #tmpBillline2 '
//产生货位移入单表体
+' Update CurrentInv Set OnHandInv=OnhandInv+InvInBillLine.InvBillQty '
+' from Item,InvInBillline,InvInBill '
+' Where CurrentInv.ItemCode=InvInBillline.ItemCode '
+' and InvInBillline.InvBillid=@InBillid '
+' And CurrentInv.WHCode=InvInBill.whCode '
+' and InvInBill.InvBillid=@InBillid '
+' And CurrentInv.WhPositionCode=InvInBill.WhPositionCode '
//加CurrentInv中相应拉式货位OnHand的值
+' if dateDiff(mm,@CloseMonth,@toMonth)=1 '
+' Update CurrentperiodInv Set OnHandInv=OnhandInv+InvInBillLine.InvBillQty '
+' from Item,InvInBillline,InvInBill '
+' Where CurrentperiodInv.ItemCode=InvInBillline.ItemCode '
+' and InvInBillline.InvBillid=@InBillid '
+' And CurrentperiodInv.WHCode=InvInBill.whCode '
+' and InvInBill.InvBillid=@InBillid '
+' And CurrentperiodInv.WhPositionCode=InvInBill.WhPositionCode '
//加CurrentperiodInv中相应拉式货位OnHand的值
// +' if not exists(select CurrentInv.* from CurrentInv,InvOutBillline '
// +' where whCode=@strwhCode '
// +' and InvOutBillline.InvBillid=@Billid '
// +' and CurrentInv.ItemCode=InvOutBillline.ItemCode '
// +' and WhPositionCode=@StrBackFluchWhPositionCode ) '
+' Insert CurrentInv(WHCode,ItemCode,OnHandInv,WhPositionCode) '
+' Select InvOutBill.whCode,InvOutBillLine.ItemCode,InvOutBillLine.InvBillQty,'
+' InvOutBill.OWhPositionCode As WhPositionCode '
+' From InvOutBillLine '
+' Join InvOutBill On InvOutBillLine.InvBillid=InvOutBill.InvBillid '
+' and InvOutBill.InvBillid=@Billid '
+' where InvOutBill.whCode+InvOutBill.oWhPositionCode+InvOutBillline.ItemCode '
+' not in (select whCode+WhPositionCode+ItemCode from CurrentInv) '
+' if dateDiff(mm,@CloseMonth,@toMonth)=1 '
// +' if not exists(select CurrentperiodInv.* from CurrentperiodInv,InvOutBillline '
// +' where whCode=@strwhCode '
// +' and InvOutBillline.InvBillid=@Billid '
// +' and CurrentperiodInv.ItemCode=InvOutBillline.ItemCode '
// +' and WhPositionCode=@StrBackFluchWhPositionCode ) '
+' Insert CurrentperiodInv(WHCode,ItemCode,OnHandInv,WhPositionCode) '
+' Select InvOutBill.whCode,InvOutBillLine.ItemCode,InvOutBillLine.InvBillQty,'
+' InvOutBill.OWhPositionCode As WhPositionCode '
+' From InvOutBillLine '
+' Join InvOutBill On InvOutBillLine.InvBillid=InvOutBill.InvBillid '
+' and InvOutBill.InvBillid=@Billid '
+' where InvOutBill.whCode+InvOutBill.oWhPositionCode+InvOutBillline.ItemCode '
+' not in (select whCode+WhPositionCode+ItemCode from CurrentperiodInv) '
+' Update #TmPmNItemList Set MOCtrlQty=MOCtrlQty-InvOutBillLine.InvBillQty '
+' from #tmPmnItemList,InvOutBillline '
+' Where #tmPmnItemList.ItemCode=InvOutBillline.ItemCode '
+' and InvOutBillline.InvBillid=@Billid '
+' drop table #tmpCurrentInv,#tmPmaxrecord '
+' end '; }
sqltext:='Select mnItemList.ItemCode,BatchCtrl,Sum(MoCtrlQty) As MOCtrlQty'
+' into #tmPmnItemList '
+' From MNItemList,Item '
+' Where MoNO='+quotedstr(mono)
+' and MoLineno='+inttostr(MoLineno)
+' and mnItemList.ItemCode=Item.ItemCode '
+' and Item.BatchCtrl=0 '
+' Group By mnItemList.ItemCode,Item.BatchCtrl '
+' Select ItemCode,WHCode,WhPositionCode,OnHandInv '
+' into #tmpCurrentInv'
+' From CurrentInv '
+' Where WHCode In ( Select WHCode '
+' From Warehouse '
+' Where WHCode IN (Select Distinct WHCode From WhPosition Where BackFlushWHP=1 And WhPositionType=0 And DeptCode='+quotedstr(getDeptCode(mono))+'))'
+' And ItemCode IN (Select ItemCode From #TmPmNItemList Where MOCtrlQty>0) '
+' And whCode+WhPositionCode Not In (Select Distinct whCode+WhPositionCode From WhPosition Where BackFlushWHP=1) '
+' And ONHandInv>0 '
+' Order by whCode,WhPositionCode '
+ ' while exists(select * from #tmPmnItemList where moCtrlqty>0) '
+ ' begin '
//选出每个可用货位所拥有的要建立货位移动清单的记录条数
+ ' DeclAre @StrWHCode varchAr(14) '
//要建立货位移动的清单的仓库
+ ' DeclAre @StrWhPositionCode varchAr(14) '
//要建立货位移动的清单的仓库的货位
+' DeclAre @StrBackFluchWhPositionCode varchAr(14) '
//要建立货位移动的清单的仓库的拉式领料货位
+' DeclAre @Billno varchAr(15) '//货位移动单单据流水号
+' declAre @Batchno varchAr(30)'//批号
+ ' declAre @ItemCode varchAr(30) '
+ ' declAre @tmpInvstatus int '
+ ' declAre @tmp varchAr(20) '
+ ' declAre @Billid int '
+ ' declAre @InBillno varchAr(15) '
+ ' declAre @InBillid int '
+ ' declAre @Billlineno int '
+ ' declAre @onhandInv float '
+ ' declAre @lineno int '
+ ' Select top 1 @ItemCode=ItemCode,@onhandInv=onhandInv,@StrWHCode=WHCode,@StrWhPositionCode=WhPositionCode '
+ ' From #TmpCurrentInv '
+ ' select @ItemCode,@onhandInv,@strwhCode,@strWhPositionCode '
//-------------------------------------------------------------------------------
+ ' Select @StrBackFluchWhPositionCode=WhPositionCode '
+ ' From WhPosition '
+ ' Where BackFlushWHP=1 And WhPositionType=0 '
+ ' And WHCode=@StrWHCode And DeptCode='+quotedstr(getDeptCode(mono))
+ ' select @strwhCode '
+ ' select @strWhPositionCode '
+ ' select @strBackfluchWhPositionCode '
//列出拥有的要建立货位移动清单的最大记录条数的货位(只有一个)
+ ' if not exists(select * from InvOutBill '
+ ' where mono='+quotedstr(mono)
+ ' and MoLineno='+inttostr(MoLineno)
+ ' and whCode=@strwhCode '
+ ' and WhPositionCode=@strWhPositionCode '
+ ' and oWhPositionCode=@strBackfluchWhPositionCode) '
+ ' begin '
+ ' select @tmp=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
+ ' select @tmpInvstatus=Invstatus from Invstatus '
+ ' where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
+ ' select @tmpInvstatus '
+ ' if @tmpInvstatus is null '
+ ' begin '
+ ' insert into Invstatus(InvstatusName,Invstatus,whCode) '
+ ' Values(substring(@tmp, '
+ ' len(@tmp)-4,5), '
+ ' 0,@strwhCode) '
+ ' select @Billno=''00001'' '
+ ' end '
+ ' else select @Billno=substring(convert(varchAr,100000+@tmpInvstatus+1),2,5) '
+ ' update Invstatus '
+ ' set Invstatus=@Billno '
+ ' where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
+ ' select @Billno=substring(@tmp,len(@tmp)-4,5)+@Billno '
//产生货位移动单单据流水号@Billno '
+ ' Insert InvOutBill(InvBillNo,WHCode,InvBillDate,InvBillMonth, '
+ ' BillTypeCode,EmployeeCode,Mono,MoLineno, '
+ ' WhPositionCode,OWhPositionCode)Values(@Billno,@strwhCode,dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()),dateName(yy,getdate())+''.''+dateName(mm,getdate()),''1101'', '
+quotedstr(userCode)+','+quotedstr(mono)+','+inttostr(MoLineno)+',@StrWhPositionCode,@StrBackFluchWhPositionCode) '
//产生货位移出单表头
+ ' select @Billid=@@identity '
+ ' select @Billid '
+ ' end '
+'else '
+ ' select @Billid=InvBillid from InvOutBill '
+ ' where mono='+quotedstr(mono)
+ ' and MoLineno='+inttostr(MoLineno)
+ ' and whCode=@strwhCode '
+ ' and WhPositionCode=@strWhPositionCode '
+ ' and oWhPositionCode=@strBackfluchWhPositionCode '
+ ' if not exists(select * from InvOutBillline where InvBillid=@Billid) '
+ ' select @lineno=1 '
+ ' else '
+ ' select @lineno=isnull(InvBilllineno,0)+1 from InvOutBillline where InvBillid=@Billid '
+ ' Insert InvOutBillLine(InvBillid,InvBilllineno,ItemCode, '
+ ' InvBillQty) '
+ ' Select @Billid as Billid,@lineno,@ItemCode, '
+ ' Case '
+ ' When @OnHandInv>=#TmPmNItemList.MoCtrlQty then #TmPmNItemList.MoCtrlQty '
+ ' Else @OnHandInv '
+ ' end As InvBillQty '
+ ' From #TmPmNItemList '
+ ' where #TmPmNItemList.ItemCode=@ItemCode '
//产生货位移出单表体 '
+ ' Update Item Set BackFlushAllocInv=BackFlushAllocInv+InvOutBillLine.InvBillQty '
+ ' from Item,InvOutBillline '
+ ' Where Item.ItemCode=InvOutBillline.ItemCode '
+ ' and InvOutBillline.InvBillid=@Billid '
+ ' and InvOutBillline.ItemCode=@ItemCode '
//增加Item中BackFlushAllocInv的值 '
+ ' Update CurrentInv Set OnHandInv=OnhandInv-InvOutBillLine.InvBillQty '
+ ' from Item,InvOutBillline,InvOutBill '
+ ' Where CurrentInv.ItemCode=InvOutBillline.ItemCode '
+ ' and InvOutBillline.InvBillid=@Billid '
+ ' and InvOutBillline.ItemCode=@ItemCode '
+ ' And CurrentInv.WHCode=InvOutBill.whCode '
+ ' and InvOutBill.InvBillid=@Billid '
+ ' And CurrentInv.WhPositionCode=InvOutBill.WhPositionCode '
//减CurrentInv中相应货位OnHand的值
+ ' declAre @CloseMonth varchAr(10), '
+ ' @toMonth varchAr(10) '
+ ' select @toMonth=dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.01'' '
+ ' select @toMonth '
+ ' select @CloseMonth=substring(convert(varchAr,Invstatus),1,7)+''.01'' from Invstatus where InvstatusName=''clsperiod'' '
+ ' select @CloseMonth '
+ ' if dateDiff(mm,@CloseMonth,@toMonth)=1 '
+ ' Update CurrentperiodInv Set OnHandInv=OnhandInv-InvOutBillLine.InvBillQty '
+ ' from Item,InvOutBillline,InvOutBill '
+ ' Where CurrentperiodInv.ItemCode=InvOutBillline.ItemCode '
+ ' and InvOutBillline.InvBillid=@Billid '
+ ' and InvOutBillline.ItemCode=@ItemCode '
+ ' And CurrentperiodInv.WHCode=InvOutBill.whCode '
+ ' and InvOutBill.InvBillid=@Billid '
+ ' And CurrentperiodInv.WhPositionCode=InvOutBill.WhPositionCode '
//减BatchCurrentInv中相应货位OnHand的值
+ ' if not exists(select * from InvInBill '
+ ' where mono='+quotedstr(mono)
+ ' and MoLineno='+inttostr(MoLineno)
+ ' and whCode=@strwhCode '
+ ' and WhPositionCode=@strBackfluchWhPositionCode '
+ ' and oWhPositionCode=@strWhPositionCode) '
+ ' begin '
+ ' select @tmp=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
+ ' select @tmpInvstatus=Invstatus from Invstatus '
+ ' where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
+ ' if @tmpInvstatus is null '
+ ' begin '
+ ' insert into Invstatus(InvstatusName,Invstatus,whCode) '
+ ' Values(substring(@tmp, '
+ ' len(@tmp)-4,5), '
+ ' 0,@strwhCode) '
+ ' select @InBillno=''00001'' '
+ ' end '
+ ' else select @InBillno=substring(convert(varchAr,100000+@tmpInvstatus+1),2,5) '
+ ' update Invstatus '
+ ' set Invstatus=@InBillno '
+ ' where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
+ ' select @InBillno=substring(@tmp,len(@tmp)-4,5)+@InBillno '
+ ' Insert InvInBill(InvBillNo,WHCode,InvBillDate,InvBillMonth, '
+ ' BillTypeCode,EmployeeCode,Mono,MoLineno,WhPositionCode, '
+ ' OWhPositionCode) '
+ ' Select @InBillno,WHCode,InvBillDate,InvBillMonth, '
+ ' BillTypeCode,EmployeeCode,Mono,MoLineno, '
+ ' oWhPositionCode, '
+ ' WhPositionCode '
+ ' From InvOutBill with(index(InvOutBillno)) '
+ ' Where InvBillNo=@Billno '
+ ' and mono='+quotedstr(mono)
+ ' and MoLineno='+inttostr(MoLineno)
+ ' select @InBillid=@@identity '
+ ' end '
+' else '
+ ' select @InBillid=InvBillid from InvInBill '
+ ' where mono='+quotedstr(mono)
+ ' and MoLineno='+inttostr(MoLineno)
+ ' and whCode=@strwhCode '
+ ' and WhPositionCode=@strBackfluchWhPositionCode '
+ ' and oWhPositionCode=@strWhPositionCode '
+ ' if not exists(select * from InvInBillline where InvBillid=@InBillid) '
+ ' select @lineno=1 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -