sfc_mnrequestoverplan_b.pas

一个MRPII系统源代码版本

       ' select t.mono,'+
           't.MoLineno,'+
           't.ItemCode,'+
           't.ite_ItemCode,'+
         ' case '+
         ' when (status1 is not null and alterNative is null) then ''+''+t.ItemCode '+
         '  when (status1 is null and alterNative=1 and moCtrlqty>0) then ''-''+t.ItemCode '+
         ' else t.ItemCode end  tmpItemCode,'+
           'i.ItemName,'+
           'null as EmployeeName,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BilllineremArk,'+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.Bomqty,'+
           't.BomScrAp_Percent,'+
           't.status,'+
           't.alterNative,'+
           't.Parentid,'+
           't.ItemListid,'+
           't.Batchno,'+
           'null as onhandInv '+
       'from #tmp1 t,Item i,Uom u '+
       ' where t.ItemCode=i.ItemCode '+
       '   and i.UomCode=u.UomCode '+
       '   and t.status1 is null '+
       '   and t.alterNative=1 '+
       '   and t.moCtrlqty>0 ) a '+
       ' Order by a.status '+
       ' drop table #tmp3 '+
       ' drop table #tmp2 '+
       ' drop table #tmp1 ';
  end
  else
  begin
    sql_text:=
        'set noCount on '+
        ' create table #tmp1(mono varchAr(16),MoLineno int ,ItemCode varchAr(16) ,'+
                       'ite_ItemCode varchAr(16) null,BilllineremArk varchAr(250) null,'+
                       'status varchAr(10) null ,status1 varchAr(10) null,moCtrlqty float null,'+
                       'MoRealqty float null,moRequestqty float null,alterNative int,'+
                       'ItemListid int,Parentid int,Bomqty float null,'+
                       'Batchno varchAr(16),BomScrAp_Percent float null) '+
        ' insert #tmp1(mono,MoLineno,ItemCode,ite_ItemCode,BilllineremArk,'+
                      'moCtrlqty,MoRealqty,moRequestqty,alterNative,ItemListid,Parentid) '+
        ' select m.mono,'+
             'm.MoLineno,'+
             'm.ItemCode,'+
             'm.ite_ItemCode,'+
             'm.BilllineremArk,'+
             'convert(int,m.moCtrlqty),'+
             'm.MoRealqty,'+
             'm.moRequestqty,'+
             'm.alterNative,'+
             'm.ItemListid,'+
             'm.Parentid '+
        ' from mnItemList m '+
        ' where m.mono='''+lbl_Mono.Caption+''' '+
           '   and m.MoLineno='+lbl_MoLineno.Caption+
           '   and m.alterNative=0 '+
           '   and convert(int,m.moCtrlqty)<=m.MoRealqty '+
           tmp_DeptCode+
        //把代换虚项加入到临时表中
      ' insert #tmp1(mono,MoLineno,ItemCode,ite_ItemCode,BilllineremArk,moCtrlqty,'+
                    'MoRealqty,moRequestqty,alterNative,ItemListid,Parentid) '+
      ' select m.mono,'+
           'm.MoLineno,'+
           'm.ItemCode,'+
           'm.ite_ItemCode,'+
           'm.BilllineremArk,'+
           'm.moCtrlqty,'+
           'm.MoRealqty,'+
           'm.moRequestqty,'+
           'm.alterNative,'+
           'm.ItemListid,'+
           'm.Parentid'+
      ' from mnItemList m '+
      ' where m.mono='''+lbl_Mono.Caption+''' '+
         '   and m.MoLineno='+lbl_MoLineno.Caption+
         '   and m.alterNative=1 '+
         '   and m.moCtrlqty>0  '+
         '   and convert(int,m.moCtrlqty)<=m.MoRealqty '+
             tmp_DeptCode+
         //改变排序的标志位STATUS STATUS1
        ' insert #tmp1(mono,MoLineno,ItemCode) '+
        '  select distinct mono,MoLineno,ite_ItemCode from #tmp1 '+
        '  where ite_ItemCode<>'''''+
        '    and ite_ItemCode<>'''+getCode(lbl_Item.Caption)+''''+
        '    and alterNative=0 '+
        '  select distinct ite_ItemCode into #tmp2 '+
        '    from #tmp1 '+
        '   where ite_ItemCode<>'''''+
        '    and ite_ItemCode<>'''+getCode(lbl_Item.Caption)+''''+
        '    and alterNative=0 '+
        '  declAre Item_Cursor cursor for select ite_ItemCode from #tmp2 '+
        ' declAre @tmp_ItemCode varchAr(16) '+
        ' declAre @m int '+
        ' set @m=65 '+
        ' open Item_Cursor '+
        '  fetch Item_Cursor into @tmp_ItemCode '+
        ' while @@fetch_Status=0 '+
        ' begin '+
        '   update #tmp1 set status=chAr(@m),status1=chAr(@m) where ItemCode=@tmp_ItemCode '+
        '   update #tmp1 set status=chAr(@m)+''1'' where ite_ItemCode=@tmp_ItemCode '+
        '   set @m=@m+1 '+
        '   fetch Item_Cursor into @tmp_ItemCode '+
        ' end '+
        ' Close Item_Cursor '+
        ' deallocate Item_Cursor '+

        //把代换项实际项加入到临时表中
      ' insert #tmp1(mono,MoLineno,ItemCode,ite_ItemCode,BilllineremArk,'+
                    'moCtrlqty,MoRealqty,moRequestqty,alterNative,ItemListid,'+
                    'Parentid,Bomqty,BomScrAp_Percent) '+
      ' select m.mono,'+
           'm.MoLineno,'+
           'm.ItemCode,'+
           'm.ite_ItemCode,'+
           'm.BilllineremArk,'+
           'm.moCtrlqty,'+
           'm.MoRealqty,'+
           'm.moRequestqty,'+
           'm.alterNative,'+
           'm.ItemListid,'+
           'm.Parentid,'+
           'm.Bomqty,'+
           'm.BomScrAp_Percent '+
      ' from mnItemList m '+
      ' where Parentid in ( select ItemListid '+
                           ' from mnItemList '+
                           ' where mono='''+lbl_Mono.Caption+''' '+
                           '   and MoLineno='+lbl_MoLineno.Caption+
                           '   and alterNative=1 '+
                           '   and moCtrlqty>0  '+
                           '   and convert(int,moCtrlqty)<=MoRealqty '+
                           tmp_DeptCode1+')'+
         ' declAre @tmp_Status varchAr(10) '+
        ' declAre @tmp_Parentid int '+
       ' declAre @n int '+
       ' select @n=65 '+
       ' select distinct ite_ItemCode,Parentid '+
       '  into #tmp3 '+
       '  from #tmp1 '+
       ' where  ite_ItemCode<>'''' and alterNative=1 and moCtrlqty=0 '+
       ' declAre Item_Cursor cursor for select ite_ItemCode,Parentid from #tmp3 '+
       ' open Item_Cursor '+
       ' fetch Item_Cursor into @tmp_ItemCode,@tmp_Parentid '+
       ' while @@fetch_Status=0 '+
       ' begin '+
       '   select @tmp_Status=status from #tmp1 where ItemListid=@tmp_Parentid '+
       '   if @tmp_Status is null  '+
       '     select @tmp_Status=chAr(@m) '+
       '   select @tmp_Status=@tmp_Status+chAr(@n) '+
       '   update #tmp1 set status=@tmp_Status where ItemListid=@tmp_Parentid '+
       '   select @tmp_Status=status from #tmp1 where ItemListid=@tmp_Parentid '+
       '   update #tmp1 set status=@tmp_Status+''1'' where Parentid=@tmp_Parentid '+
       '   select @n=@n+1 '+
       '   fetch Item_Cursor into @tmp_ItemCode,@tmp_Parentid '+
       ' end '+
       ' Close Item_Cursor '+
       ' deallocate Item_Cursor '+
        ' select * from ( '+
        ' select t.mono,'+
             't.MoLineno,'+
             't.ItemCode,'+
             't.alterNative,'+
             't.ItemListid,'+
             't.Parentid,'+
             't.ite_ItemCode,'+
           'case '+
           '  when (status1 is not null and alterNative=0) then ''+''+t.ItemCode '+
           '  when (status1 is null and alterNative=1 and moCtrlqty>0 ) then ''-''+t.ItemCode '+
           ' else t.ItemCode end  tmpItemCode,'+
             'i.ItemName,'+
             'i.ItemCode2,'+
             'e.EmployeeName,'+
             'u.UomName,'+
             't.BilllineremArk,'+
             't.moCtrlqty,'+
             't.MoRealqty,'+
             't.moRequestqty, '+
             't.Bomqty,'+
             't.BomScrAp_Percent,'+
             'c.'+tmpfields+' as onhandInv,'+
             't.Batchno,'+
             't.status '+
         'from #tmp1 t'+
         ' join Item i '+
         '      on  t.ItemCode=i.ItemCode '+
        // '   and i.whCode='''+getCode(cmbbx_WhCode.text)+''' '+
         tmp_WhEmployee+
         ' left join Uom u '+
         '      on i.UomCode=u.UomCode '+
         ' left join Employee e '+
         '      on i.wh_EmployeeCode=e.EmployeeCode '+
         ' left join CurrentInv c '+
         '      on t.ItemCode=c.ItemCode '+
         '     and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '     and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         ' where t.status1 is null '+
         '   and (i.whCode='''+getCode(cmbbx_WhCode.text)+''' or '+
         '   c.'+tmpfields+'>0) '+
         ' union '+
         //虚项不受条件限制
        ' select t.mono,'+
             't.MoLineno,'+
             't.ItemCode,'+
             't.alterNative,'+
             't.ItemListid,'+
             't.Parentid,'+
             't.ite_ItemCode,'+
             'case '+
             '  when (status1 is not null and alterNative is null) then ''+''+t.ItemCode '+
             '  when (status1 is null and alterNative=1 and moCtrlqty>0 ) then ''-''+t.ItemCode '+
             ' else t.ItemCode end  tmpItemCode,'+
             'i.ItemName,'+
             'i.ItemCode2,'+
             'null as EmployeeName,'+
             'u.UomName,'+
             't.BilllineremArk,'+
             't.moCtrlqty,'+
             't.MoRealqty,'+
             't.moRequestqty, '+
             't.Bomqty,'+
             't.BomScrAp_Percent,'+
             'c.'+tmpfields+' as onhandInv,'+
             't.Batchno,'+
             't.status '+
         'from #tmp1 t'+
         ' join Item i '+
         '      on  t.ItemCode=i.ItemCode '+
         ' left join Uom u '+
         '      on i.UomCode=u.UomCode '+
         ' left join CurrentInv c '+
         '      on t.ItemCode=c.ItemCode '+
         '     and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '     and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         ' where t.status1 is not null ) a'+
         ' Order by a.status '+
         ' drop table #tmp3 '+
         ' drop table #tmp2 '+
         ' drop table #tmp1 ';
  end;

  try
    with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('drop table #tmp1');
      execsql;
    end;
  except
  end;

  try
    with AdoQry_Body do
    begin
      Close;
      sql.clear;
      sql.Add(sql_text);
      Open;
    end
  except
    begin
      DispInfo('连接失败，请重试！',3);
    end;
  end;
end;

procedure TFrm_Sfc_MnRequestOverPlan_B.InitCmbbx_WhEmployee(whCode:string);
begin
 //初始化仓管员，从物料主文件中选出当前仓库的缺省仓管员，可以为空
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select distinct i.wh_EmployeeCode,e.EmployeeName '+
            '  from Item i,Employee e '+
            '  where whCode='''+whCode+''''+
            '    and i.wh_EmployeeCode is not null '+
            '    and i.wh_EmployeeCode=e.EmployeeCode '+
            ' Order by i.wh_EmployeeCode');
    open;
    cmbbx_WhEmployee.clear;
    cmbbx_WhEmployee.Items.Add('');
    if not Eof then
    begin
      First;
      while not Eof do
      begin
        cmbbx_WhEmployee.Items.Add(fieldbyname('wh_EmployeeCode').asstring+' '+fieldbyname('EmployeeName').asstring);
        Next;
      end;
      cmbbx_WhEmployee.Itemindex:=0;
    end;
  end;
end;


procedure TFrm_Sfc_MnRequestOverPlan_B.InitCmbbx_WhPositionCode(whCode:string);
begin
  //初始化货位，根据当前传入的仓库号码，选出不是待检货位及不是拉式货位的货位
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select WhPositionCode,WhPositionName '+
              ' from WhPosition  '+
              ' where whCode='''+whCode+''''+
              ' and WhPositionType<>1 '+
              ' and BackFlushWhP=0 ');
    open;
    cmbbx_WhPositionCode.clear;
    if not Eof then
    begin
      First;
      while not Eof do
      begin
        cmbbx_WhPositionCode.Items.Add(fieldbyname('WhPositionCode').asstring+' '+fieldbyname('WhPositionName').asstring);
        Next;
      end;
      cmbbx_WhPositionCode.Itemindex:=0;
    end;
  end;
end;