📄 mrp_enter_newrunmrp.pas
字号:
'LLC Integer,'+
'BomItemType Integer) '+
'Create Table #TmpVoidmdlOpenBom(ItemCode varChAr(16),'+
'LLC Integer,'+
'BomItemType Integer) '+
'Create Table #TmpParent(ItemCode varchAr(16),BomItemType Integer)';
ExecuteSql(AdoQry_Bom,StrSqlText,1);
While Not R_AdoQry_Mrpmain.Eof Do
begin
Frm_Mrp_Enter_NewRunMrp.EdtRunMrpStatus.Text:='第二步:正在分解'+R_AdoQry_Mrpmain.fieldbyname('ItemCode').AsString+'......';
WrIteMrpFlag(Frm_Mrp_Enter_NewRunMrp.EdtRunMrpStatus.Text);//写入Mrp日志
Frm_Mrp_Enter_NewRunMrp.EdtRunMrpStatus.Refresh;
OpenBomDetail(AdoQry_Bom,R_AdoQry_Mrpmain.fieldbyname('ItemCode').Value);
Application.ProcessMessages;
R_AdoQry_Mrpmain.Next;
Frm_Mrp_Enter_NewRunMrp.Repaint;
end;
StrSqlText:='Drop Table #TmpOpenBom '+
'Drop Table #TmPmdlOpenBom '+
'Drop Table #TmpVoidmdlOpenBom '+
'Drop Table #TmpParent';
ExecuteSql(AdoQry_Bom,StrSqlText,1);
StrSqlText:='Select Max(LowLevelCode) As LLC From #TmpItem';
ExecuteSql(AdoQry_Bom,StrSqlText,0);
Result:=AdoQry_Bom.fieldbyname('LLC').Value;
AdoQry_Bom.Free;
AdoQry_SaveLC.Free;
end;
procedure TFrm_Mrp_Enter_NewRunMrp.RunMrpFirstStep(R_AdOCmd_FirstStep:TAdOCommand;
R_MrpCondition:String;
R_MrpCondition1:String);//做运算Mrp的前期工作
var StrSqlText:String;
begin
StrSqlText:='DeclAre @@ToDay varchAr(10) '+
'If Len(Convert(varchAr,DatePArt(mm,GetDate())))=1 '+
'Select @@ToDay=Convert(varchAr,DatePArt(yyyy,GetDate()))+''.0''+Convert(varchAr,DatePArt(mm,GetDate())) '+
'Else '+
'Select @@ToDay=Convert(varchAr,DatePArt(yyyy,GetDate()))+''.''+Convert(varchAr,DatePArt(mm,GetDate())) '+
'If Len(Convert(varchAr,DatePArt(dd,GetDate())))=1 '+
'Select @@ToDay=@@ToDay+''.0''+Convert(varchAr,DatePArt(dd,GetDate())) '+
'Else '+
'Select @@ToDay=@@ToDay+''.''+Convert(varchAr,DatePArt(dd,GetDate())) '+
'Select ItemCode,ItemType,Case When ItemType=4 Then Convert(Float,0) '+
'Else MinQty '+
'end As MinQty, '+
'Case When ItemType=4 Then 0 '+
'Else PurchLDTime '+
'end As PurchLDTime, '+
'Case When ItemType=4 Then 0 '+
'Else RunLT+QcLT '+
'end As MNLDTime, '+
'Case When ItemType=4 Then 0 '+
'Else PrepareLT '+
'end As PrepareLTTime, '+
'Case When ItemType=4 Then 0 '+
'Else RunLT '+
'end As RunLTTime, '+
'Case When ItemType=4 Then 0 '+
'Else QcLT '+
'end As QcLTTime, '+
'Case When ItemType=4 Then 10 '+
'When ItemType=7 Then 9 '+
'Else PmCode '+
'end As PmCode, '+
'Case When ItemType=4 Then 0 '+
'Else BatchStrat '+
'end As BatchStrat, '+
'CurrentOnHandInv As OnHand,CurrentOnCheckInv As OnCheck,Convert(Float,0) As AssignedQty,'+
'CurrentOnHandInv As TmpOnHand,CurrentOnCheckInv As TmpOnCheck,Convert(Float,0) As TmPAssignedQty,'+
'BatchCtrl,PmBatch,CapacityHours,0 As LowLevelCode,ItemUsable '+
'Into #TmpItem '+
'From Item '+
'Create Index ItemCode On #TmpItem(ItemCode) '+
'IF (Select MrpParamValueN From MrpParam Where MrpParamCode=''VisualManuObject'' )=1 '+
'Update #TmpItem '+
' Set OnHand=0,OnCheck=0,TmpOnHand=0,TmpOnCheck=0 '+
' Where ItemCode In (Select ItemCode From Mps where Mpsstatus=3) '+
'CREATE TABLE #TmPmps( '+
'ItemCode varchAr(16),'+
'MpsDate Smalldatetime,'+
'MpsQty Decimal(21,8),'+
'PmCode Integer,'+
'LLC Integer,'+
'ItemType Integer,'+
'Pegging varchAr(20),'+
'PeggingNo varchAr(46)) '+
'DeclAre @@MrpReSource Integer '+
'Select @@MrpReSource=MrpParamValueN '+
'From MrpParam '+
'Where MrpParamCode=''MrpDatAreSource'' '+
'If @@MrpReSource=1 '+//销售订单
'Insert #TmPmps '+
'Select Sa_SaleOrderLine.ItemCode,SloLineDate As MpsDate,SloQty-ShipQty As MpsQty,'+
'#TmpItem.PmCode,Convert(Int,0) As LLC,Convert(Int,0) As ItemType,''销售订单需求 '' As Pegging,'+
'SloNo+Convert(varchAr,SloLineNO) As PeggingNo '+
'From Sa_SaleOrderLine '+
'Join #TmpItem With(Index(ItemCode)) On Sa_SaleOrderLine.ItemCode=#TmpItem.ItemCode '+
'Where '+R_MrpCondition1+' '+
'And Sa_SaleOrderLine.ItemCode Not In (Select ItemCode From #TmpItem Where ItemUsable=0) '+
'Union '+
'Select ItemCode,Convert(SmallDateTime,@@ToDay) As MpsDate,MinQty As MpsQty, '+
'PmCode,Convert(Int,0) As LLC,Convert(Int,0) As ItemType,''安全库存需求 '' As Pegging,'+
''''' As PeggingNo '+
'From #TmpItem '+
'Where MinQty>0 '+
'Else '+
'begin '+
'IF @@MrpReSource=2 '+//主计划
'Insert #TmPmps '+
'Select Mps.ItemCode,Mps.MpsDate,Mps.MpsQty-(Mps.MpsFinishQty) As MpsQty,'+
'#TmpItem.PmCode,Convert(Int,0) As LLC,Convert(Int,0) As ItemType,''主计划需求 '' As Pegging,'+
'Convert(varchAr,MpsId) As PeggingNo '+
'From Mps '+
'Join #TmpItem With(Index(ItemCode)) On Mps.ItemCode=#TmpItem.ItemCode '+
'Where '+R_MrpCondition+' '+
'And Mps.ItemCode Not In (Select ItemCode From #TmpItem Where ItemUsable=0) '+
'Union '+
'Select ItemCode,Convert(SmallDateTime,@@ToDay) As MpsDate,MinQty As MpsQty, '+
'PmCode,Convert(Int,0) As LLC,Convert(Int,0) As ItemType,''安全库存需求 '' As Pegging,'+
''''' As PeggingNo '+
'From #TmpItem '+
'Where MinQty>0 '+
'Else '+ // 销售订单+主计划
'begin '+
'Insert #TmPmps '+
'Select Sa_SaleOrderLine.ItemCode,SloLineDate As MpsDate,SloQty-ShipQty As MpsQty,'+
'#TmpItem.PmCode,Convert(Int,0) As LLC,Convert(Int,0) As ItemType,''销售订单需求 '' As Pegging,'+
'SloNo+Convert(varchAr,SloLineNO) As PeggingNo '+
'From Sa_SaleOrderLine '+
'Join #TmpItem With(Index(ItemCode)) On Sa_SaleOrderLine.ItemCode=#TmpItem.ItemCode '+
'Where '+R_MrpCondition1+' '+
'And Sa_SaleOrderLine.ItemCode Not In (Select ItemCode From #TmpItem Where ItemUsable=0) '+
'Insert #TmPmps '+
'Select Mps.ItemCode,Mps.MpsDate,Mps.MpsQty-(Mps.MpsFinishQty) As MpsQty,'+
'#TmpItem.PmCode,Convert(Int,0) As LLC,Convert(Int,0) As ItemType,''主计划需求 '' As Pegging,'+
'Convert(varchAr,MpsId) As PeggingNo '+
'From Mps '+
'Join #TmpItem With(Index(ItemCode)) On Mps.ItemCode=#TmpItem.ItemCode '+
'Where '+R_MrpCondition+' '+
'And Mps.ItemCode Not In (Select ItemCode From #TmpItem Where ItemUsable=0) '+
'Union '+
'Select ItemCode,Convert(SmallDateTime,@@ToDay) As MpsDate,MinQty As MpsQty, '+
'PmCode,Convert(Int,0) As LLC,Convert(Int,0) As ItemType,''安全库存需求 '' As Pegging,'+
''''' As PeggingNo '+
'From #TmpItem '+
'Where MinQty>0 '+
'end '+
'end '+
'Create Index LLC On #TmPmps(LLC) '+
'Select ItemCode,MoLine.MONo As OrderNo,MoLineNo As OrderLineNo,'+
'Case When MoNoFinishQty>=0 Then '+
'MoNoFinishQty '+
'Else 0 '+
'end As OrderQty, '+
'MoQty As Qty,MoLineStatus As OrderLineStatus,Mo.DeptCode As DeptVendorCode,'+
'0 As PmCode,MoStArtWorkDate As DueDate,MoLineDate As ReleaseDate,MoNoFinishQty As NOFinishQty '+
'Into #TmPmoPo '+
'From MoLine '+
'Join Mo On MoLine.MoNo=Mo.MoNo '+
'Where MoLineStatus<7 And MOQty>0 And MoLine.MoNo Not In '+
'(Select MoNo '+
'From Mo '+
'Where MoSpecial=1) '+
'Insert #TmPmoPo '+
'Select ItemCode,OrderNo,OrderLineNo,'+
'Case When OrderQty>=0 Then '+
'OrderQty '+
'Else 0 '+
'end As OrderQty,Qty,OrderLineStatus,'+
'DeptVendorCode,PmCode,DueDate,ReleaseDate,NoFinishQty '+
'From (Select ItemCode,PoLine.PoNo As OrderNo,PoLineNo As OrderLineNo,'+
'Case When PoNoFinishQty>=0 Then '+
'PONoFinishQty-POInQty '+
'Else 0 '+
'end As OrderQty,'+
'PoQty As Qty,PoLineStatus As OrderLineStatus,'+
'Po.VendorCode As DeptVendorCode,'+
'Case Po.PoType '+
'When 1 Then 2 '+
'Else 1 '+
'end '+
'As PmCode,PoStArtWorkDate As DueDate,PoLineDate As ReleaseDate,PoNoFinishQty As NoFinishQty '+
'From PoLine '+
'Join Po On PoLine.PoNo=Po.PoNo '+
'Where PoLineStatus<7 And PoQty>0 And PoLine.PoNo Not In '+
'(Select PoNo '+
'From Po '+
'Where PoSpecial=1))TmpPOLine '+
'Create Index ItemCode On #TmPmoPo(ItemCode,ReleaseDate) '+
'Select * Into #TmPmoPoAlterNative '+
'From #TmPmoPo '+
'Create Index ItemCode On #TmPmoPoAlterNative(ItemCode) '+
'Select * '+
'Into #TmpBom '+
'From Bom '+
'Where (BomStatus=0 Or BomStatus=2) '+//ItemUsable
'And ItemCode Not In (Select ItemCode From #TmpItem Where ItemUsable=0) '+
'And Ite_ItemCode Not In (Select ItemCode From #TmpItem Where ItemUsable=0) '+
'Create Index Ite_ItemCode On #TmpBom(Ite_ItemCode) '+
'Select Sdate,WorkDay,DayOrdinal '+
'Into #TmpCalendar '+
'From Calendar '+
'Where GetDate()-90<=SDate And SDate<=GetDate()+360 '+
'Create Index SDate On #TmpCalendar(SDate) '+
'Create Table #TmPMrpResult('+
'GroupOrdinal int NULL,'+
'MrpResultID int NULL ,'+
'Ordinal int NULL ,'+
'ItemCode varchAr (16) NULL ,'+
'OnHand Decimal(20,8) NULL Default 0 ,'+
'OnCheck Decimal(20,8) NULL Default 0 ,'+
'AssignedQty Decimal(20,8) NULL Default 0 ,'+
'CanUseOnHand Decimal(20,8) NULL Default 0 ,'+
'GrossQty Decimal(20,8) NULL Default 0 ,'+
'DueDate datetime NULL ,'+
'ReleaseDate datetime NULL ,'+
'RealReleaseDate smalldatetime NULL ,'+
'RealDueDate smalldatetime NULL ,'+
'OrderNo varchAr (16) NULL ,'+
'OrderLineNo int NULL ,'+
'OrderLineStatus int NULL ,'+
'OrderQty Decimal(20,8) NULL ,'+
'RealOrderQty Decimal(20,8) Null,'+
'NetQty Decimal(20,8) NULL ,'+
'PmCode int NULL ,'+
'Pegging varchAr (20) NULL ,'+
'PeggingNo varchAr (20) NULL ,'+
'SScheck Int NULL ,'+
'ReMainQty Decimal(20,8) NULL ,'+
'DeptVendorCode varchAr(12) NULL )'+
'Create Table #TmPMrpResult1('+
'GroupOrdinal int NULL,'+
'MrpResultID int Null ,'+
'Ordinal int NULL ,'+
'ItemCode varchAr (16) NULL ,'+
'OnHand Decimal(20,8) NULL ,'+
'OnCheck Decimal(20,8) NULL ,'+
'AssignedQty Decimal(20,8) NULL ,'+
'CanUseOnHand Decimal(20,8) NULL ,'+
'GrossQty Decimal(20,8) NULL ,'+
'DueDate datetime NULL ,'+
'ReleaseDate datetime NULL ,'+
'RealReleaseDate smalldatetime NULL ,'+
'RealDueDate smalldatetime NULL ,'+
'OrderNo varchAr (16) NULL ,'+
'OrderLineNo int NULL ,'+
'OrderLineStatus int NULL ,'+
'OrderQty Decimal(20,8) NULL ,'+
'RealOrderQty Decimal(20,8) Null,'+
'NetQty Decimal(20,8) NULL ,'+
'PmCode int NULL ,'+
'Pegging varchAr (20) NULL ,'+
'PeggingNo varchAr (20) NULL ,'+
'SScheck Int NULL ,'+
'ReMainQty Decimal(20,8) NULL ,'+
'DeptVendorCode varchAr(12) NULL )'+
'Create Table #TmPmdlMps(ItemCode varchAr(16),'+
'MpsDate DateTime,'+
'MpsQty Float,'+
'PmCode Integer,'+
'LLC Integer,'+
'ItemType Integer,'+
'Pegging varchAr(20))'+
'Create Table #TmPmdlMps1(ItemCode varchAr(16),'+
'MpsDate DateTime,'+
'MpsQty Float,'+
'PmCode Integer,'+
'LLC Integer,'+
'ItemType Integer,'+
'Pegging varchAr(20))'+
'Select ItemCode,MONo As OrderNo,MoLineNo As OrderLineNo,'+
'Sum(MOCtrlQty) As MoCtrlQty,Sum(MoRealQty) As MoRealQty '+
'Into #TmPmNItem '+
'From MNItemList With(Index(Relation_7305_FK)) '+
'Where MONo+Convert(varchAr,MoLineNo) In '+
'(Select OrderNo+Convert(varchAr,OrderLineNo) '+
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -