mrp_enter_freepassmo.pas

一个MRPII系统源代码版本

Tmpbeforeedit:=AdoQry_Head.BeforeEdit;
AdoQry_Head.BeforeEdit:=nil;
AdoQry_Head.Edit;
AdoQry_Head.fieldbyname('MoLineStatus').AsInteger:=6;
AdoQry_Head.fieldbyname('MoLineCheck').AsInteger:=0;
AdoQry_Head.Post;
AdoQry_Head.BeforeEdit:=Tmpbeforeedit;
end;

procedure TFrm_Mrp_Enter_FreePassMo.PassbyProductMoLine(mono: string;
  MoLineno: integer);
var sqltext:string;
    AdoQry:TAdoQuery;
begin
  SqlText:=' Update MoLine '
           +' set MoLineStatus=6,'
           +'     PassEmployeeCode='+quotedstr(userCode)+','
           +'     confirmtime=getdate() '
           +' Where MoNo='''+mono+''''
           +' And  ParentMoLineNo='''+inttostr(MoLineno)+''''
           +' And MoLineStatus=5 ';
 AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
 try
 AdoQry.Close;
 AdoQry.SQL.Text:=SqlText;
 AdoQry.execsql;
 finally
 AdoQry.Free;
 end;

end;

procedure TFrm_Mrp_Enter_FreePassMo.PassMoLine(mono: string;
  MoLineno: integer);
var sqltext:string;
    AdoQry:TAdoQuery;
begin
  SqlText:=' Update MoLine '
           +' set MoLineStatus=6,'
           +'     PassEmployeeCode='+quotedstr(userCode)+','
           +'     confirmtime=getdate() '
           +' Where MoNo='''+mono+''''
           +' And  MoLineNo='''+inttostr(MoLineno)+''''
           +' And MoLineStatus=5 ';
 AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
 try
 AdoQry.Close;
 AdoQry.SQL.Text:=SqlText;
 AdoQry.execsql;
 finally
 AdoQry.Free;
 end;

end;

procedure TFrm_Mrp_Enter_FreePassMo.savebyProducthistory(mono: string;
  MoLineno: integer);
var sqltext:string;
    AdoQry:TAdoQuery;
begin
  SqlText:=' select * from MoLine '
           +' Where MoNo='''+mono+''''
           +' And  ParentMoLineNo='''+inttostr(MoLineno)+''''
           +' And MoLineStatus=5 '
           +' and byProduct=1 ';
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
  try
   AdoQry.Close;
   AdoQry.SQL.Text:=SqlText;
   AdoQry.open;
   AdoQry.First;
   while not AdoQry.Eof do
    begin
     saveMoLineHistory(dbconnect,AdoQry.fieldbyname('mono').asstring,AdoQry.fieldbyname('MoLineno').asinteger,userCode,3);
     AdoQry.next;
    end;
  finally
   AdoQry.Free;
  end;

end;


function TFrm_Mrp_Enter_FreePassMo.CreatestallmoveBill(mono: string;
  MoLineno: integer): boolean;
var AdoQry:TAdoQuery;
    sqltext:string;
    Billno:string;
begin
  Result:=False;
  Billno:='';
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
try
 try
sqltext:='Select ItemCode,Sum(MoCtrlQty) As MOCtrlQty '
         +'   Into #TmPmNItemList    '
         +'   From MNItemList        '
         +'  Where MoNO='+quotedstr(mono)
         +' and MoLineno='+inttostr(MoLineno)
         +'  Group By ItemCode '
         +' 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,getdate(),dateName(yy,getdate())+''.''+dateName(mm,getdate()),''1101'', '
         +quotedstr(userCode)+','+quotedstr(mono)+','+'1,@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的值
      +'   if convert(decimal(7,2),dateName(yy,getdate())+dateName(mm,getdate()))-(select Invstatus from Invstatus where InvstatusName=''clsperiod'')=0.01  '
         +'     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    '

         +'    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, '
      +'                                    WhPositionCode,                    '
      +'                                   OWhPositionCode                     '
      +'                                    From InvOutBill                    '
      +'                                    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 InvOutBillLine,InvOutBill                   '
      +'   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 convert(decimal(7,2),dateName(yy,getdate())+dateName(mm,getdate()))-(select Invstatus from Invstatus where InvstatusName=''clsperiod'')=0.01  '
      +'        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  '

      +'   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                   '

      +'   if convert(decimal(7,2),dateName(yy,getdate())+dateName(mm,getdate()))-(select Invstatus from Invstatus where InvstatusName=''clsperiod'')=0.01  '
      +'   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                   '

      +'             Update #TmPmNItemList Set MOCtrlQty=MOCtrlQty-InvOutBillLine.InvBillQty '
      +'          from #tmPmnItemList,InvOutBillline                            '
      +'                Where #tmPmnItemList.ItemCode=InvOutBillline.ItemCode  '
      +'                  and InvOutBillline.InvBillid=@Billid                 '
      +'      drop table #tmpCurrentInv,#tmPmaxrecord  '
      +' end      ';
  Executesql(AdoQry,sqltext,1);
  Executesql(AdoQry,'drop table #tmPmnItemList',1);
  Result:=True;