mrp_enter_newrunmrp.pas

一个MRPII系统源代码版本

                                     'From #TmPmoPo '+
                                     'Where #TmPmOPo.OrderLineStatus<=6  ) '+
                      'Group By ItemCode,MONo,MoLineNo '+
              'Insert #TmPmNItem '+
              'Select ItemCode,PoNo As OrderNo,POLineNo As OrderLineNo,'+
                           'Sum(POCtrlQty) As MoCtrlQty,Sum(PoRealQty) As MoRealQty '+
                      'From OPItemList With(Index(OPItemList_IDX_Po)) '+
                      'Where PONo+Convert(varchAr,POLineNo) In '+
                             '(Select OrderNo+Convert(varchAr,OrderLineNo) '+
                                     'From #TmPmoPo '+
                                     'Where #TmPmOPo.OrderLineStatus<=6 ) '+
                      'Group By ItemCode,PoNo,PoLineNo '+
              'Select #TmPmNItem.*,#TmPmOPO.Qty As OrderQty '+
                      'Into #TmPmNItemList '+
                      'From #TmPmNItem,#TmPmoPo '+
                      'Where #TmPmOPO.OrderNO+Convert(varchAr,#TmPmOPO.OrderLineNO)=#TmPmNItem.OrderNO+Convert(varchAr,#TmPmNItem.OrderLineNO) '+
              'Drop Table #TmPmNItem '+
              'Truncate Table DynamicOPeration '+
              'Create Table #TmpAlterNativeOrder '+
                      '(ItemCode varchAr(16))';
  ExecuteCmd(R_AdOCmd_FirstStep,StrSqlText);
end;

procedure TFrm_Mrp_Enter_NewRunMrp.VoidCalculateMrpResult(
            R_AdoQry_Calculate:TAdoQuery;
            R_AdoQry_Calculate1:TAdoQuery;
            R_StrItemCode:String;R_StrReleaseDate:String;
            R_StrMinQty:String;R_StrBatchStrat:String;
            R_StrBatchQty:String;R_StrSubReleaseDate:String;
            R_StrMrpResult:String;R_StrDueDate:String);//计算Mrp结果
var StrSqlText:String;
    DblOnHand:Double;
    DblSSQty:Double;
    IntOrdinal:Integer;
    BMKBookMArk:String;
begin
  StrSQLText:='Select * '+
                     'From #TmPMrpResult '+
                     'Order By ReleaseDate,Ordinal';
  ExecuteSql(R_AdoQry_Calculate,StrSqlText,0);
  DblOnHand:=0;
  DblSSQty:=0;
  While Not R_AdoQry_Calculate.Eof  Do
  begin
    IntOrdinal:=R_AdoQry_Calculate.fieldbyname('Ordinal').Value;
    Case IntOrdinal OF
      0: begin
           DblOnHand:=R_AdoQry_Calculate.fieldbyname('OnHand').Value;
           R_AdoQry_Calculate.Next;
           Continue;
         end;
      1: begin
            DblOnHand:=DblOnHand+R_AdoQry_Calculate.fieldbyname('OnHand').Value;
            R_AdoQry_Calculate.Next;
            Continue;
         end;
      2: DblOnHand:=DblOnHand+R_AdoQry_Calculate.fieldbyname('OrderQty').Value;
      3: Break;
      4: DblOnHand:=DblOnHand-R_AdoQry_Calculate.fieldbyname('GrossQty').Value;
    end;

    StrPegging:=R_AdoQry_Calculate.fieldbyname('Pegging').Value;
    If DblOnHand<0 Then
      begin
        StrReleaseDate:=R_AdoQry_Calculate.fieldbyname('ReleaseDate').AsString;
        StrDueDate:=R_AdoQry_Calculate.fieldbyname('DueDate').AsString;
        If (R_StrBatchStrat='0') Or (R_StrBatchQty='0')  Then
          DblSSQty:=Abs(DblOnHand)
        Else
          begin
            DblSSQty:=Abs(DblOnHand);
            If DblSSQty-StrToFloat(R_StrBatchQty)*Floor(DblSSQty/StrToFloat(R_StrBatchQty))=0 Then
               DblSSQty:=DblSSQty
            Else
               DblSSQty:=StrToFloat(R_StrBatchQty)*( (DblSSQty-(DblSSQty-StrToFloat(R_StrBatchQty)*Floor(DblSSQty/StrToFloat(R_StrBatchQty))) )/StrToFloat(R_StrBatchQty)+1);
          end;
        DblOnHand:=DblOnHand+DblSSQty;

        BMKBookMArk:=R_AdoQry_Calculate.BookMArk;
        StrSqlText:='Insert #TmPMrpResult'+
                            '(ItemCode,'+
                            ' DueDate,'+
                            ' ReleaseDate,'+
                            ' RealDueDate,'+
                            ' RealReleaseDate,'+
                            ' OnHand,'+
                            ' ReMainQty,'+
                            ' Ordinal,'+
                            ' PmCode,'+
                            ' Pegging,'+
                            ' GroupOrdinal)'+
                         'Values'+
                            '('''+StrItemCode+''','+
                            ' '''+StrDueDate+''','+
                            ' '''+StrReleaseDate+''','+
                            ' '''+StrDueDate+''','+
                            ' '''+StrReleaseDate+''','+
                            ' '+FloatToStr(DblSSQty)+','+
                            ' '+FloatToStr(DblSSQty)+','+
                            ' 3,'+
                            ' '+StrPmCode+','+
                            ' '''+StrPegging+''','+
                            ' '+IntToStr(IntGroupOrdinal)+')';
        ExecuteSql(R_AdoQry_Calculate1,StrSqlText,1);
        R_AdoQry_Calculate.BookMArk:=BMKBookMArk;
      end;
    R_AdoQry_Calculate.Next;
  end;

  If DblOnHand>0 Then
  begin
    StrSQLText:='Select * '+
                       'From #TmPMrpResult '+
                       'Where Ordinal<>4 '+
                       'Order By ReleaseDate,Ordinal';
    ExecuteSql(R_AdoQry_Calculate,StrSqlText,0);
    R_AdoQry_Calculate.Last;
    While (Not R_AdoQry_Calculate.Bof) And (DblOnHand>0)  Do
    begin
      If R_AdoQry_Calculate.fieldbyname('Ordinal').Value=1 Then
          Break
      Else
        begin
          R_AdoQry_Calculate.Edit;
          Case R_AdoQry_Calculate.fieldbyname('Ordinal').Value Of
             2:begin
                  If DblONHand>=R_AdoQry_Calculate.fieldbyname('OnHand').Value Then
                     begin
                       DblONHand:=DblONHand-R_AdoQry_Calculate.fieldbyname('OnHand').Value;
                       R_AdoQry_Calculate.fieldbyname('OnHand').Value:=0;
                     end
                  Else
                     begin
                      R_AdoQry_Calculate.fieldbyname('OnHand').Value:=
                      R_AdoQry_Calculate.fieldbyname('OnHand').Value-DblOnHand;
                      DblONHand:=0;
                     end;

               end;
             3:begin
               If DblONHand>=R_AdoQry_Calculate.fieldbyname('OnHand').Value Then
                 begin
                   DblONHand:=DblONHand-R_AdoQry_Calculate.fieldbyname('OnHand').Value;
                   R_AdoQry_Calculate.fieldbyname('OnHand').Value:=0;
                   R_AdoQry_Calculate.Delete();
                   R_AdoQry_Calculate.prior;
                   Continue;
                 end
               Else
                 begin
                  DblONHand:=R_AdoQry_Calculate.fieldbyname('OnHand').Value-DblOnHand;;
                  If (R_StrBatchStrat='0') Or (R_StrBatchQty='0')  Then
                     DblSSQty:=Abs(DblOnHand)
                  Else
                    begin
                      DblSSQty:=Abs(DblOnHand);
                      If DblSSQty-StrToFloat(R_StrBatchQty)*Floor(DblSSQty/StrToFloat(R_StrBatchQty))=0 Then
                         DblSSQty:=DblSSQty
                      Else
                         DblSSQty:=StrToFloat(R_StrBatchQty)*( (DblSSQty-(DblSSQty-StrToFloat(R_StrBatchQty)*Floor(DblSSQty/StrToFloat(R_StrBatchQty))) )/StrToFloat(R_StrBatchQty)+1);
                    end;
                  R_AdoQry_Calculate.fieldbyname('OnHand').Value:=DblSSQty;
                  DblONHand:=0;
                  end;
               end;
             end;
          R_AdoQry_Calculate.Post;
        end;
      R_AdoQry_Calculate.prior;
    end;
  end;

  StrSqlText:='Insert '+R_StrMrpResult+' '+
      '(ItemCode,DueDate,ReleaseDate, '+
        'OrderNo,GrossQty,OrderQty,RealOrderQty,OnHand,'+
        'CanUseOnHand,NetQty,Ordinal,'+
        'PmCode,OrderLineStatus,OrderLineNo,'+
        'Pegging,PeggingNo,OnCheck,AssignedQty,RealDueDate,'+
        'RealReleaseDate,GroupOrdinal,ReMainQty,DeptVendorCode) '+
      'Select ItemCode,DueDate,ReleaseDate, '+
                'OrderNo,GrossQty,OrderQty,RealOrderQty,OnHand,'+
                'CanUseOnHand,NetQty,Ordinal,'+
                'PmCode,OrderLineStatus,OrderLineNo,'+
                'Pegging,PeggingNo,OnCheck,AssignedQty,RealDueDate,'+
                'RealReleaseDate,GroupOrdinal,ReMainQty,DeptVendorCode '+
              'From #TmPMrpResult '+
              'Order By ReleaseDate,Ordinal';
  ExecuteSql(R_AdoQry_Calculate,StrSqlText,1);
end;

{
procedure TFrm_Mrp_Enter_NewRunMrp.CalculateMrpResult(
            R_AdoQry_Calculate:TAdoQuery;
            R_AdoQry_Calculate1:TAdoQuery;
            R_StrItemCode:String;R_StrReleaseDate:String;
            R_StrMinQty:String;R_StrBatchStrat:String;
            R_StrBatchQty:String;R_StrSubReleaseDate:String;
            R_StrMrpResult:String;R_StrDueDate:String);//计算Mrp结果
var StrSqlText:String;
    DblOnHand:Double;
    DblSSQty:Double;
    IntOrdinal:Integer;
    BMKBookMArk:String;
    StrDueDate1:String;
    strReleaseDate1:String;
begin
  StrSQLText:='Select * '+
                     'From #TmPMrpResult '+
                     'Order By ReleaseDate,Ordinal';
  ExecuteSql(R_AdoQry_Calculate,StrSqlText,0);
  DblOnHand:=0;
  While Not R_AdoQry_Calculate.Eof  Do
  begin
    IF (R_AdoQry_Calculate.fieldbyname('ReleaseDate').AsString>StrToDate) And (DblOnHand<0) Then
    begin
      DblOnHand:=DblOnHand+DblSSQty;
      BMKBookMArk:=R_AdoQry_Calculate.BookMArk;
      StrSqlText:='Select * From #TmPMrpResult '+
                          'Where Ordinal=3 And ReleaseDate='''+StrReleaseDate1+''' ';
      ExecuteSql(R_AdoQry_Calculate1,StrSqlText,0);
      If R_AdoQry_Calculate1.RecordCount<>0 Then
        StrSqlText:='Update #TmPMrpResult '+
                       'Set Onhand=OnHand+'+FloatToStr(DblSSQty)+','+
                            'ReMainQty=ReMainQty+'+FloatToStr(DblSSQty)+' '+
                       'Where Ordinal=3 And ReleaseDate='''+StrReleaseDate1+''' '
      Else
        StrSqlText:='Insert #TmPMrpResult'+
                          '(ItemCode,'+
                          ' DueDate,'+
                          ' ReleaseDate,'+
                          ' RealDueDate,'+
                          ' RealReleaseDate,'+
                          ' OnHand,'+
                          ' ReMainQty,'+
                          ' Ordinal,'+
                          ' PmCode,'+
                          ' Pegging,'+
                          ' GroupOrdinal)'+
                       'Values'+
                          '('''+StrItemCode+''','+
                          ' '''+StrDueDate1+''','+
                          ' '''+StrReleaseDate1+''','+
                          ' '''+StrDueDate+''','+
                          ' '''+StrReleaseDate+''','+
                          ' '+FloatToStr(DblSSQty)+','+
                          ' '+FloatToStr(DblSSQty)+','+
                          ' 3,'+
                          ' '+StrPmCode+','+
                          ' '''+StrPegging+''','+
                          ' '+IntToStr(IntGroupOrdinal)+')';
      ExecuteSql(R_AdoQry_Calculate1,StrSqlText,1);
      R_AdoQry_Calculate.BookMArk:=BMKBookMArk;
    end;
    IntOrdinal:=R_AdoQry_Calculate.fieldbyname('Ordinal').Value;
    Case IntOrdinal OF
      0: begin
           DblOnHand:=R_AdoQry_Calculate.fieldbyname('OnHand').Value;
           R_AdoQry_Calculate.Next;
           Continue;
         end;
      1: begin
            DblOnHand:=DblOnHand+R_AdoQry_Calculate.fieldbyname('OnHand').Value;
            R_AdoQry_Calculate.Next;
            Continue;
         end;
      2: DblOnHand:=DblOnHand+R_AdoQry_Calculate.fieldbyname('OrderQty').Value;
      3: Break;
      4: DblOnHand:=DblOnHand-R_AdoQry_Calculate.fieldbyname('GrossQty').Value;
    end;
    StrPegging:='系统建议下达';
    If DblOnHand<0 Then
      begin
        StrReleaseDate:=R_AdoQry_Calculate.fieldbyname('ReleaseDate').AsString;
        StrDueDate:=R_AdoQry_Calculate.fieldbyname('DueDate').AsString;
        Case R_AdoQry_Calculate.fieldbyname('PmCode').Value Of
        1..2:
             StrDueDate:=GetCalendar(DBConnect,StrReleaseDate,IntPurchLDTime);
        Else
            StrDueDate:=GetCalendar(DBConnect,StrReleaseDate,IntRunLTTime);