mrp_global.pas
来自「一个MRPII系统源代码版本」· PAS 代码 · 共 1,229 行 · 第 1/5 页
PAS
1,229 行
'Create Table #TmPmdlOpenBom(ItemCode varChAr(16)) '+
'Create Table #TmpOpenBom(ItemCode varChAr(16)) '+
'Create Table #TmpComponent(ItemCode varchAr(16)) '+
'Insert #TmpComponent(ItemCode)'+R_StrItemCode+' '+//--传入子项物料代码
'While Exists (Select * From #TmpComponent) '+// --循环到没有父项为止
'begin '+
'Delete From #TmPmdlOpenBom '+// --产生下一层的子项
'Insert #TmPmdlOpenBom(ItemCode) '+
'Select Ite_ItemCode As ItemCode '+
'From Bom '+
'Join #TmpComponent On Bom.ItemCode=#TmpComponent.ItemCode '+
'Delete From #TmpComponent '+
'Insert #TmpComponent '+
'Select #TmPmdlOpenBom.ItemCode '+
'From #TmPmdlOpenBom '+
'Join Bom On #TmPmdlOpenBom.ItemCode=Bom.ItemCode '+
'Where Bom.BomItemType=3 '+
'Insert #TmpOpenBom '+
'Select #TmPmdlOpenBom.ItemCode '+
'From #TmPmdlOpenBom '+
'Join Bom On #TmPmdlOpenBom.ItemCode=Bom.ItemCode '+
'Where Bom.BomItemType<>3 '+
'end '+
'Drop Table #TmpComponent '+
'Drop Table #TmPmdlOpenBom '+
'Insert #TmpOpenBom(ItemCode) '+R_StrItemCode+' '+
'Select #TmpItem.ItemCode,ReleaseDate As MpsDate,GrossQty As MpsQty,'+
'#TmpItem.PmCode,0 As LLC,#TmpItem.ItemType,Pegging '+
'Into #TmPmps '+
'From '+StrMrpTable+' '+
'Join #TmpItem With(Index(ItemCode)) On '+StrMrpTable+'.ItemCode=#TmpItem.ItemCode '+
'Where '+StrMrpTable+'.ItemCode In ('+R_StrItemCode+') And Ordinal=1 '+
'Create Index LLC On #TmPmps(LLC) '+
'Select ItemCode,MoLine.MONo As OrderNo,MoLineNo As OrderLineNo,'+
'MoNoFinishQty 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 With(Index(MoLine_idx_ItemCode)) '+
'Join Mo On MoLine.MoNo=Mo.MoNo '+
'Where ItemCode In (Select Distinct ItemCode From #TmpOpenBom ) And MoLineStatus<7 And MoNoFinishQty>=0 And MoLine.MoNo Not In '+
'(Select MoNo '+
'From Mo '+
'Where MoSpecial=1) '+
'Insert #TmPmoPo '+
'Select ItemCode,PoLine.PoNo As OrderNo,PoLineNo As OrderLineNo,'+
'PoNoFinishQty 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 With(Index(poline_idx_ItemCode)) '+
'Join Po On PoLine.PoNo=Po.PoNo '+
'Where ItemCode In (Select Distinct ItemCode From #TmpOpenBom ) And PoLineStatus<7 And PoNoFinishQty>=0 And PoLine.PoNo Not In '+
'(Select PoNo '+
'From Po '+
'Where PoSpecial=1) '+
'Create Index ItemCode On #TmPmoPo(ItemCode,ReleaseDate) '+
'Drop Table #TmpOpenBom '+
'Select * Into #TmPmoPoAlterNative '+
'From #TmPmoPo '+
'Create Index ItemCode On #TmPmoPoAlterNative(ItemCode) '+
'Select InvOutBillLine.ItemCode,Sum(InvBillQty) As InvBillQty,'+ //统计领料单
'InvOutBillLine.PoNo As OrderNo,InvOutBillLine.PoLineNo As OrderLineNo '+
'Into #TmpInvOutBillLine '+
'From InvOutBillLine With(Index(Relation_10100_FK)) '+
'Join InvOutBill On InvOutBillLine.InvBillID=InvOutBill.InvBillID '+
'Where InvOutBillLine.PONo Is Not Null And '+
'InvOutBill.BillTypeCode In (''0201'',''0202'') And '+
'InvOutBillLine.ItemCode In ('+R_StrItemCode+') And '+
'InvOutBillLine.PONo+Convert(varchAr,InvOutBillLine.PoLineNo) In '+
'(Select OrderNo+Convert(varchAr,OrderLineNo) '+
'From #TmPmoPo '+
'Where #TmPmOPo.OrderLineStatus=6 And #TmPmOPo.NOFinishQty>0 ) '+
'Group By InvOutBillLine.ItemCode,InvOutBillLine.PoNo,InvOutBillLine.PoLineNo '+
'Insert #TmpInvOutBillLine '+
'Select InvOutBillLine.ItemCode,Sum(InvBillQty) As InvBillQty,'+
'InvOutBillLine.MoNo As OrderNo,InvOutBillLine.MoLineNo As OrderLineNo '+
'From InvOutBillLine With(Index(Relation_10100_FK)) '+
'Join InvOutBill On InvOutBillLine.InvBillID=InvOutBill.InvBillID '+
'Where InvOutBillLine.MONo Is Not Null And '+
'InvOutBill.BillTypeCode In (''0201'',''0202'') And '+
'InvOutBillLine.ItemCode In ('+R_StrItemCode+') And '+
'InvOutBillLine.MONo+Convert(varchAr,InvOutBillLine.MoLineNo) In '+
'(Select OrderNo+Convert(varchAr,OrderLineNo) '+
'From #TmPmoPo '+
'Where #TmPmOPo.OrderLineStatus=6 And #TmPmOPo.NoFinishQty>0 ) '+
'Group By InvOutBillLine.ItemCode,InvOutBillLine.MoNo,InvOutBillLine.MoLineNo '+
'Select ItemCode,MONo As OrderNo,MoLineNo As OrderLineNo,Sum(MOCtrlQty) As MoCtrlQty '+
'Into #TmPmNItemList '+
'From MNItemList With(Index(Relation_7305_FK)) '+
'Where MONo+Convert(varchAr,MoLineNo) In '+
'(Select OrderNo+Convert(varchAr,OrderLineNo) '+
'From #TmPmoPo '+
'Where #TmPmOPo.OrderLineStatus=6 And #TmPmOPo.NoFinishQty>0 ) '+
'Group By ItemCode,MONo,MoLineNo '+
'Insert #TmPmNItemList '+
'Select ItemCode,PoNo As OrderNo,POLineNo As OrderLineNo,Sum(POCtrlQty) As MoCtrlQty '+
'From OPItemList With(Index(OPItemList_IDX_Po)) '+
'Where PONo+Convert(varchAr,POLineNo) In '+
'(Select OrderNo+Convert(varchAr,OrderLineNo) '+
'From #TmPmoPo '+
'Where #TmPmOPo.OrderLineStatus=6 And #TmPmOPo.NoFinishQty>0 ) '+
'Group By ItemCode,PoNo,PoLineNo '+
'Select #TmPmNItemList.ItemCode,#TmPmNItemList.OrderNo,#TmPmNItemList.OrderLineNo,'+
'#TmPmNItemList.MOCtrlQty,#TmPmOPO.OrderQty,#TmPmoPo.Qty '+
'Into #TmPmNItemList1 '+
'From #TmPmNItemList '+
'Join #TmPmoPo On #TmPmNItemList.OrderNo+Convert(varchAr,#TmPmNItemList.OrderLineNo)= '+
'#TmPmoPo.OrderNo+Convert(varchAr,#TmPmoPo.OrderLineNo) '+
'Select #TmpInvOutBillLine.ItemCode, '+
'Case '+
'When #TmPmNItemList1.MoCtrlQty<=#TmpInvOutBillLine.InvBillQty '+
'Then #TmpInvOutBillLine.InvBillQty*#TmPmNItemList1.OrderQty/#TmPmNItemList1.Qty '+
'Else #TmpInvOutBillLine.InvBillQty-#TmPmNItemList1.MoCtrlQty*(#TmPmNItemList1.Qty-#TmPmNItemList1.OrderQty)/#TmPmNItemList1.Qty '+
'end As BillQty '+
'Into #TmpBillQty '+
'From #TmpInvOutBillLine '+
'Join #TmPmNItemList1 On #TmpInvOutBillLine.ItemCode=#TmPmNItemList1.ItemCode '+
'And #TmpInvOutBillLine.OrderNo=#TmPmNItemList1.OrderNo '+
'And #TmpInvOutBillLine.OrderLineNo=#TmPmNItemList1.OrderLineNo '+
'Update #TmpItem Set OnHand=OnHand+TmpBillQty.Billqty,AssignedQty=AssignedQty+TmpBillQty.Billqty,'+
'TmpOnHand=TmpOnHand+TmpBillQty.Billqty,TmPAssignedQty=TmPAssignedQty+TmpBillQty.Billqty '+
'From #TmpItem, '+
'(Select ItemCode,Case When Sum(BillQty)>0 Then Sum(BillQty) '+
'Else Convert(Float,0) '+
'end As BillQty '+
'From #TmpBillQty '+
'Group By ItemCode ) TmpBillQty '+
'Where #TmpItem.ItemCode=TmpBillQty.ItemCode '+
'Drop Table #TmpInvOutBillLine,#TmpBillQty,#TmPmNItemList,#TmPmNItemList1 '+
'Create Table #TmPMrpResult'+
'(ItemCode varChAr(16), '+
'DueDate DateTime, '+
'ReleaseDate DateTime, '+
'OrderNo varChAr(16), '+
'GrossQty Float Default 0, '+
'OrderQty Float Default 0, '+
'OnHand Float Default 0, '+
'CanUseOnHand Float Default 0, '+
'NetQty Float Default 0, '+
'Ordinal Integer, '+
'SSCode Integer, '+
'PmCode Integer,'+
'OrderLineStatus Integer,'+
'OrderLineNo Integer,'+
'Pegging varChAr(20),'+
'OnCheck Float Default 0,'+
'AssignedQty Float Default 0,'+
'RealDueDate DateTime,'+
'RealReleaseDate DateTime,'+
'GroupOrdinal Integer) '+
'Create Table #TmPMrpResult1'+
'(MrpResultId numeric(18, 0) IDENTITY (1, 1) NOT NULL ,'+
'ItemCode varChAr(16), '+
'DueDate DateTime, '+
'ReleaseDate DateTime, '+
'OrderNo varChAr(16), '+
'GrossQty Float Default 0, '+
'OrderQty Float Default 0, '+
'OnHand Float Default 0, '+
'CanUseOnHand Float Default 0, '+
'NetQty Float Default 0, '+
'Ordinal Integer, '+
'SSCode Integer, '+
'PmCode Integer,'+
'OrderLineStatus Integer,'+
'OrderLineNo Integer,'+
'Pegging varChAr(20), '+
'OnCheck Float Default 0,'+
'AssignedQty Float Default 0,'+
'RealDueDate DateTime,'+
'RealReleaseDate DateTime,'+
'GroupOrdinal Integer) '+
'Create Table #TmPmdlMps(ItemCode varchAr(16),'+
'MpsDate DateTime,'+
'MpsQty Float,'+
'PmCode Integer,'+
'LLC Integer,'+
'ItemType Integer) '+
'Create Table #TmPmdlMps1(ItemCode varchAr(16),'+
'MpsDate DateTime,'+
'MpsQty Float,'+
'PmCode Integer,'+
'LLC Integer,'+
'ItemType Integer) '+
'Create Table #TmpCapacity'+
'(WCCode varChAr(16),'+
'MoNo varChAr(16),'+
'MoLineNo Int,'+
'ItemCode varChAr(16),'+
'MoHours Float,'+
'MpsType varChAr(8),'+
'MoQty Float,'+
'MoDate DateTime) '+
'Create Table #TMpss'+
'(ItemCode varchAr(16),'+
'SsSysInfoFlag Integer,'+
'OrderNo varChAr(10),'+
'OrderLineNo Integer,'+
'SSDate smalldatetime,'+
'SSQty Float,'+
'PmType Integer,'+
'SScheck Integer Default 0,'+
'GrossRequireQty Float,'+
'PlanInvQty Float,'+
'OrderLineStatus Integer,'+
'DeptVendorCode varChAr(12),'+
'DueDate SmallDateTime,'+
'ReMainQty Float,'+
'RealDueDate DateTime,'+
'RealReleaseDate DateTime)';
ExecuteCmd(R_AdOCmd_FirstStep,StrSqlText);
end;
// MoLine.ItemCode In ('+R_StrItemCode+') Or MoLine.ItemCode In (Select Ite_ItemCode As ItemCode From Bom Where ItemCode In ('+R_StrItemCode+'))) And
procedure SaveCurrentMoPo(R_AdoQry_Save1:TAdoQuery;//将当前MOPO记录插入#TmPMrpResult
R_AdoQry_Save2:TAdoQuery;
R_DblSaveQty:Double);
var StrSqlText:String;
begin
With R_AdoQry_Save1 Do
begin
Close;
Sql.clear;
StrSqlText:='Insert #TmPMrpResult'+
'( ItemCode,'+
'DueDate,'+
'ReleaseDate,'+
'OrderNo,' +
'OrderLineNo,'+
'OrderQty,'+
'Ordinal,'+
'PmCode,'+
'SSCode,'+
'OrderLineStatus) '+
'Values( :ItemCode,'+
':DueDate,'+
':ReleaseDate,'+
':OrderNo,' +
':OrderLineNo,'+
':OrderQty,'+
':Ordinal,'+
':PmCode,'+
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?