📄 mrp_enter_newpassmo.pas
字号:
+' 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 '
+' while exists(select * from #tmPmnItemList where moCtrlqty>0) '
+' begin '
+' 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 '
+' Select WHCode,WhPositionCode,Count(*) As TotalRecord '
+' Into #TmPmaxRecord '
+' From #TMPCurrentInv '
+' Group By WHCode,WhPositionCode '
//选出每个可用货位所拥有的要建立货位移动清单的记录条数
+' DeclAre @StrWHCode varchAr(14) '
//要建立货位移动的清单的仓库
+' DeclAre @StrWhPositionCode varchAr(14) '
//要建立货位移动的清单的仓库的货位
+' DeclAre @StrBackFluchWhPositionCode varchAr(14) '
//要建立货位移动的清单的仓库的拉式领料货位
+' DeclAre @Billno varchAr(15) '//货位移动单单据流水号
+' declAre @tmpInvstatus int '
+' declAre @tmp varchAr(20) '
+' declAre @Billid int '
+' declAre @InBillno varchAr(15) '
+' declAre @InBillid int '
+' declAre @Billlineno int '
+' Select Top 1 @StrWHCode=WHCode,@StrWhPositionCode=WhPositionCode '
+' From #TmPmaxRecord '
+' Where TotalRecord=(Select Max(TotalRecord) '
+' From ( Select * '
+' From #TmPmaxRecord ) TmPmaxRecord1 ) '
+' 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 '
//列出拥有的要建立货位移动清单的最大记录条数的货位(只有一个)
+' 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 '
+' Select @Billid as Billid,identity(int,1,1) as ss,#TmpCurrentInv.ItemCode,'' '' as Batchno,'
+' Case '
+' When OnHandInv>=#TmPmNItemList.MoCtrlQty then #TmPmNItemList.MoCtrlQty'
+' Else OnHandInv '
+' end As InvBillQty '
+' into #tmpBillline '
+' From #TmpCurrentInv '
+' Join #TmPmNItemList On #TmpCurrentInv.ItemCode=#TmPmNItemList.ItemCode '
+' Where WHCode+WhPositionCode=@strwhCode+@strWhPositionCode '
+' Insert InvOutBillLine(InvBillid,InvBilllineno,ItemCode, '
+' BatchNo,InvBillQty) '
+' select * from #tmpBillline '
+' drop table #tmpBillline '
+' select * from InvOutBillline where InvBillid=@Billid '
//产生货位移出单表体
+' Update Item Set BackFlushAllocInv=BackFlushAllocInv+InvOutBillLine.InvBillQty '
+' from Item,InvOutBillline '
+' Where Item.ItemCode=InvOutBillline.ItemCode '
+' and InvOutBillline.InvBillid=@Billid '
//增加Item中BackFlushAllocInv的值
+' Update CurrentInv Set OnHandInv=OnhandInv-InvOutBillLine.InvBillQty '
+' from Item,InvOutBillline,InvOutBill '
+' Where CurrentInv.ItemCode=InvOutBillline.ItemCode '
+' and InvOutBillline.InvBillid=@Billid '
+' 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 CurrentperiodInv.WHCode=InvOutBill.whCode '
+' and InvOutBill.InvBillid=@Billid '
+' And CurrentperiodInv.WhPositionCode=InvOutBill.WhPositionCode '
//减CurrentperiodInv中相应货位OnHand的值
+' 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 '
+' select @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 '
+' 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(m⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -