📄 mrp_enter_editpassmo.pas
字号:
+' Where WhPositionType=0 ) '
+' Group By CurrentInv.ItemCode ';
{ sqltext:='Select Item.ItemCode,(Item.CurrentOnHandInv-Item.BackFlushAllocInv)-#theMNItemList.MoCtrlQty As ReMainInv '
+' From Item '
+' Join #theMNItemList On Item.ItemCode=#theMNItemList.ItemCode '
+' Where (Item.CurrentOnHandInv-Item.BackFlushAllocInv)<#theMNItemList.MoCtrlQty ';}
//判断当前库存数是否够领量数，如果选出的记录数大于0则表示有子项不够库存
Executesql(AdoQry,sqltext,1);
sqltext:='Select Batch.ItemCode,sum(BatchCurrentInv.InvQty) as onhandInv '
+' into #tmpBatchCurrentInv '
+' From BatchLine '
+' Join Batch On Batch.BatchId=BatchLine.BatchId '
+' join BatchCurrentInv on Batchline.Batchno=BatchCurrentInv.Batchno '
+' join #themnItemList on Batch.ItemCode=#themnItemList.ItemCode '
+' Where BatchStatus=0 '
+' And (BatchLine.UsefulCtrl=0 Or (DateDiff(dd,FirstInDate,GetDate())<=UsefulDate ) ) '
+' And BatchCurrentInv.WHCode In (Select Distinct WHCode '
+' From WhPosition '
+' Where WhPositionType=0 And BackFlushWHP=1 And DeptCode='+quotedstr(getDeptCode(mono))+' )'
+' And BatchCurrentInv.WHCode+BatchCurrentInv.WhPositionCode Not In (Select WHCode+WhPositionCode '
+' From WhPosition '
+' Where BackFlushWHP=1) '
+' And BatchCurrentInv.WHCode+BatchCurrentInv.WhPositionCode In (Select WHCode+WhPositionCode '
+' From WhPosition '
+' Where WhPositionType=0 ) '
+' group by Batch.ItemCode ';
Executesql(AdoQry,sqltext,1);
sqltext:='Select #TheMNItemList.ItemCode '
+' into #themnItem '
+' From #TheMNItemList '
+' Join #TheCurrentInv On #TheMNItemList.ItemCode=#TheCurrentInv.ItemCode '
+' Where (#TheCurrentInv.OnHandInv-#TheMNItemList.MoCtrlQty)>=0 '
+' And BatchCtrl=0 '
+' Union '
+' Select #TheMNItemList.ItemCode As ReMainInv '
+' From #TheMNItemList '
+' Join #TMpBatchCurrentInv On #TheMNItemList.ItemCode=#TmpBatchCurrentInv.ItemCode '
+' Where (#TmpBatchCurrentInv.OnHandInv-#TheMNItemList.MoCtrlQty)>=0 '
+' And BatchCtrl=1 ';
Executesql(AdoQry,sqltext,1);
sqltext:='Select #TheMNItemList.ItemCode '
+' From #TheMNItemList '
+' Where ItemCode Not In (select ItemCode from #themnItem) ';
Executesql(AdoQry,sqltext,0);
if AdoQry.RecordCount>0 then
begin
haveInv:=0;
tmpCode:=AdoQry.fieldbyname('ItemCode').asstring;
exit;
end
else
begin
haveInv:=1;
Result:=True;
end;
{ sqltext:='Select #TheMNItemList.ItemCode '
+' into #tmPmnItem '
+' From #TheMNItemList '
+' Where ItemCode Not In (select ItemCode from #themnItem) ';
Executesql(AdoQry,sqltext,1);
sqltext:='Select Distinct WhPosition.WHCode,Warehouse.WHName '
+' From WhPosition '
+' Join Warehouse On WhPosition.WHCode=Warehouse.WHCode '
+' Where WhPosition.WHCode In (Select Distinct WHCode '
+' From CurrentInv '
+' Where ItemCode IN (select ItemCode from #tmPmnItem) '
+' And OnHandInv>0 ) '
+' And WhPosition.WHCode Not IN (Select Distinct WHCode '
+' From WhPosition '
+' Where BackFlushWHP=1 And WhPositionType=0 And DeptCode='+quotedstr(getDeptCode(mono))+') ';
//选出所有没设拉式货位的仓库
Executesql(AdoQry,sqltext,0);
if AdoQry.RecordCount>0 then
begin
strWHName:=AdoQry.fieldbyname('whName').asstring;
exit;
end;
Result:=True; }
finally
try
// if ishaveInv=0 then
Executesql(AdoQry,'drop table #themnItemList,#theCurrentInv,#tmpBatchCurrentInv,#themnItem',1);
except
end;
AdoQry.Free;
end;
end;
function TFrm_Mrp_Enter_EditPassMo.getDeptCode(mono: string): string;
var AdoQry:TAdoQuery;
sqltext:string;
begin
Result:='';
AdoQry:=TAdoQuery.Create(self);
AdoQry.Connection:=dbconnect;
sqltext:='select DeptCode from mo where mono='+quotedstr(mono);
try
Executesql(AdoQry,sqltext,0);
Result:=AdoQry.fieldbyname('DeptCode').asstring;
finally
AdoQry.Free;
end;
end;
function TFrm_Mrp_Enter_EditPassMo.CreatestallmoveBill(mono: string;
MoLineno: integer;BatchCtrl:integer): boolean;
var AdoQry:TAdoQuery;
sqltext:string;
Billno:string;
begin
Result:=False;
Billno:='';
AdoQry:=TAdoQuery.Create(self);
AdoQry.Connection:=dbconnect;
try
try
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 '
+' 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)) '
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -