inv_realopqty_b.pas

一个MRPII系统源代码版本

    //设置核定标记
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update InvInBill Set'
      +' InvBillWHChck=1'
      +',Stk_EmployeeCode2='''+UserCode+''''
      +' Where InvBillId='+BillId;
    AdoQry_Tmp.ExecSQL;
    AdoQry_Material.First;
    while not AdoQry_Material.Eof do
    begin
      AdoQry_Material.Edit;
      if AdoQry_Material.fieldbyname('RealOpQtyNew').AsString='' then
        AdoQry_Material.fieldbyname('RealOpQtyNew').AsString:='0';
      if AdoQry_Material.fieldbyname('RealOPScrAp_PercentNew').AsString='' then
        AdoQry_Material.fieldbyname('RealOPScrAp_PercentNew').AsString:='0';
      if AdoQry_Material.fieldbyname('RealOpQty').AsString='' then
        AdoQry_Material.fieldbyname('RealOpQty').AsString:='0';
      if AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString='' then
        AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString:='0';
      AdoQry_Material.Post;
      //如果存在,更改所有物料的定额及损耗,否则增加
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='If Not Exists (Select ItemCode'
        +' From RealOpQty'
        +' Where InvBillId='+AdoQry_Material.fieldbyname('InvBillId').AsString
        +' And InvBillLineNo='''+AdoQry_Material.fieldbyname('InvBillLineNo').AsString+''''
        +' And Ite_ItemCode='''+AdoQry_Material.fieldbyname('Ite_ItemCode').AsString+''''
        +' And ItemCode='''+AdoQry_Material.fieldbyname('ItemCode').AsString+''')'
        +' Insert RealOpQty(InvBillId,InvBillLineNo,Ite_ItemCode,ItemCode'
        +',RealOpQty,RealOpQtyNew,RealOPScrAp_Percent,RealOPScrAp_PercentNew)'
        +' Values('
        +' '+AdoQry_Material.fieldbyname('InvBillId').AsString+''
        +','''+AdoQry_Material.fieldbyname('InvBillLineNo').AsString+''''
        +','''+AdoQry_Material.fieldbyname('Ite_ItemCode').AsString+''''
        +','''+AdoQry_Material.fieldbyname('ItemCode').AsString+''''
        +','+AdoQry_Material.fieldbyname('RealOpQty').AsString+''
        +','+AdoQry_Material.fieldbyname('RealOpQtyNew').AsString+''
        +','+AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString+''
        +','+AdoQry_Material.fieldbyname('RealOPScrAp_PercentNew').AsString+')'
        +' Else'
        +' Update RealOpQty Set'
        +' RealOpQtyNew='+AdoQry_Material.fieldbyname('RealOpQtyNew').AsString+''
        +',RealOPScrAp_PercentNew='+AdoQry_Material.fieldbyname('RealOPScrAp_PercentNew').AsString
        +' Where InvBillId='+AdoQry_Material.fieldbyname('InvBillId').AsString
        +' And InvBillLineNo='''+AdoQry_Material.fieldbyname('InvBillLineNo').AsString+''''
        +' And Ite_ItemCode='''+AdoQry_Material.fieldbyname('Ite_ItemCode').AsString+''''
        +' And ItemCode='''+AdoQry_Material.fieldbyname('ItemCode').AsString+'''';
      AdoQry_Tmp.ExecSQL;
      AdoQry_Material.Next;
    end;

    AdoQry_Body.First;
    while not AdoQry_Body.Eof do
    begin
      //根据新定额,新损耗重新更新OPBill
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert OPBill (InvBillId,InvBillLineNo,ItemCode'
        +',OPBillQty)'
        +' Select InvBillId'
        +',InvBillLineNo'
        +',ItemCode'
        +',Sum(IsNull(RealOpQtyNew*(1+RealOPScrAp_PercentNew/100)'
        +'*('+AdoQry_Body.fieldbyname('InvBillQty').AsString+'),0))'
        +' From RealOpQty'
        +' Where InvBillId='+AdoQry_Body.fieldbyname('InvBillId').AsString
        +' And InvBillLineNo='''+AdoQry_Body.fieldbyname('InvBillLineNo').AsString+''''
        +' Group By InvBillId,InvBillLineNo,ItemCode';
      AdoQry_Tmp.ExecSQL;
      //增加当前货位库存
      Tempstr:=UpdateInv(AdoQry_Tmp,GetCode(Lbl_WhCode.Caption),
        GetCode(Lbl_WhPositionCode.Caption),
        AdoQry_Body.fieldbyname('ItemCode').AsString,
        '('+AdoQry_Body.fieldbyname('InvBillQty').AsString+')',
        NeedChangePrice,True);
      if Tempstr<>'' then
      begin
        DispStr:=Tempstr;
        Abort;
      end;
      //增加批次库存量
      if AdoQry_Body.fieldbyname('BatchNo').AsString<>'' then
      begin
        if not BatchInvCheck(AdoQry_Tmp,AdoQry_Body.fieldbyname('BatchNo').AsString,
          'R',GetCode(Lbl_WhCode.Caption),GetCode(Lbl_WhPositionCode.Caption),
          AdoQry_Body.fieldbyname('InvBillQty').AsFloat)then
          Abort;
        ChangeBatchInv(AdoQry_Tmp,AdoQry_Body.fieldbyname('BatchNo').AsString,
          'R',Lbl_Date.Caption,GetCode(Lbl_WhCode.Caption),
          GetCode(Lbl_WhPositionCode.Caption),
          AdoQry_Body.fieldbyname('InvBillQty').AsFloat);
      end;
      //修改PoLine
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update PoLine Set'
        +' PORealInQty=PORealInQty+'+AdoQry_Body.fieldbyname('InvBillQty').AsString+''
        +',PoNoFinishQty=PoQty-PoRealInQty-('+AdoQry_Body.fieldbyname('InvBillQty').AsString+') '
        +' Where PoNo='''+Lbl_PoNo.Caption+''''
        +' And PoLineNo='''+AdoQry_Body.fieldbyname('PoLineNo').AsString+'''';
      AdoQry_Tmp.ExecSQL;
      //从PmParam表中读取参数,如果用户定义为自动关闭,
      //则判断POLine的PORealInQty是否超过POQty如果是,则关闭该订单行
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='If Exists'
        +' (Select PmParamValueN From PmParam'
        +'  Where PmParamCode=''autoClosePo'' And  PmParamValueN=1)'
        +' Update PoLine Set'
        +' PoLineStatus=7'
        +' Where PoNo='''+Lbl_PoNo.Caption+''''
        +' And PoLineNo='''+AdoQry_Body.fieldbyname('PoLineNo').AsString+''''
        +' And PoRealInQty>=PoQty';
      AdoQry_Tmp.ExecSQL;
      if (AdoQry_Head.fieldbyname('FromCheckIn').AsString='1') then
      begin
        //减少当前仓库待检货位库存
        Tempstr:=UpdateInv(AdoQry_Tmp,GetCode(Lbl_WhCode.Caption),FWhPositionCode,
          AdoQry_Body.fieldbyname('ItemCode').AsString,
          '(-('+AdoQry_Body.fieldbyname('InvBillSfcQty').AsString+'))',
          NeedChangePrice,True);
        if Tempstr<>'' then
        begin
          DispStr:=Tempstr;
          Abort;
        end;
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Update POLine Set'
          +' POInQty=POInQty-('+AdoQry_Body.fieldbyname('InvBillSfcQty').AsString+')'
          +' Where PONo='''+Lbl_PoNo.Caption+''''
          +' And POLineNo='+AdoQry_Body.fieldbyname('POLineNo').AsString;
        AdoQry_Tmp.ExecSql;
      end;
      AdoQry_Body.Next;
    end;
    //减少供应商端的材料库存OPCurrentInv
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Insert OPCurrentInv(VendorCode,ItemCode)'
      +' Select Distinct '''+GetCode(Lbl_Vendor.Caption)+''',ItemCode'
      +' From OPBill'
      +' Where InvBillId='+BillId+''
      +' And ItemCode Not In'
      +'(Select ItemCode'
      +' From OPCurrentInv'
      +' Where VendorCode='''+GetCode(Lbl_Vendor.Caption)+''')';
    AdoQry_Tmp.ExecSQL;
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update OPCurrentInv'
      +' Set OPInv=OPInv-OPBill.OPBillQty'
      +' From (Select ItemCode,Sum(OPBillQty) As OPBillQty From OPBill'
      +' Where InvBillId='+BillId
      +' Group By ItemCode) OPBill'
      +' Where OPCurrentInv.VendorCode='''+GetCode(Lbl_Vendor.Caption)+''''
      +' And OPCurrentInv.ItemCode=OPBill.ItemCode';
    AdoQry_Tmp.ExecSQL;
    //更新OpBill数据表的单价,金额
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update OPBill Set'
      +' OPBillNoTaxPrice=opAp.OPAveragePrice'
      +',OPBillNoTaxAmount=Round(opAp.OPAveragePrice*OPBill.OPBillQty,2)'
      +' From OPAveragePrice opAp '
      +' Where opAp.VendorCode='''+GetCode(Lbl_Vendor.Caption)+''''
      +' And opAp.ItemCode=OPBill.ItemCode'
      +' And OPBill.InvBillId='+BillId;
    AdoQry_Tmp.ExecSQL;
    //更新OPAveragePRICE数据表，供应商的移动加权平均价
    if True then
    begin
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update OPAveragePrice Set'
        +' OPApQty=OPApQty-opb.Qty'
        +',OPApAmount=OPApAmount-opb.Amount'
        +' From (Select ItemCode,Sum(OPBillQty) As Qty,Sum(OPBillNoTaxAmount) As Amount'
        +' From OPBill'
        +' Where InvBillId='+BillId
        +' Group By ItemCode) opb'
        +' Where OPAveragePrice.VendorCode='''+GetCode(Lbl_Vendor.Caption)+''''
        +' And OPAveragePrice.ItemCode=opb.ItemCode';
      AdoQry_Tmp.ExecSQL;
    end;
    //计算材料费InvBillline
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update InvInBillLine Set'
      +' InvBillNoTaxAmount=opb.Amount'
      +' From (Select InvBillId,InvBillLineNo,Sum(OPBillNoTaxAmount) As Amount'
      +' From OPBill'
      +' Where InvBillId='+BillId
      +' Group By InvBillId,InvBillLineNo) opb'
      +' Where InvInBillLine.InvBillId='+MaterialBillId
      +' And InvInBillLine.InvBillLineNo=opb.InvBillLineNo';
    AdoQry_Tmp.ExecSQL;
    //更新本厂的移动加权平均
    if True then
    begin
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
        +' ApAmount=ApAmount+l.Amount'
        +',ApQty=ApQty+l.Qty'
        +' From (Select ItemCode,Sum(InvBillNoTaxAmount) As Amount'
        +',Sum(InvBillQty) As Qty'
        +' From InvInBillLine'
        +' Where InvBillId='+BillId
        +' Group By ItemCode) l'
        +' Where AveragePrice.WHCode='''+GetCode(Lbl_WhCode.Caption)+''''
        +' And AveragePrice.ItemCode=l.ItemCode';
      AdoQry_Tmp.ExecSQL;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
        +' ApAmount=ApAmount+l.Amount'
        +',AveragePrice=Case When ApQty=0 Then AveragePrice'
        +' Else (ApAmount+l.Amount)/ApQty end'
        +' From (Select ItemCode,Sum(InvBillNoTaxAmount) As Amount'
        +' From InvInBillLine'
        +' Where InvBillId='+MaterialBillId
        +' Group By ItemCode) l'
        +' Where AveragePrice.WHCode='''+GetCode(Lbl_WhCode.Caption)+''''
        +' And AveragePrice.ItemCode=l.ItemCode';
      AdoQry_Tmp.ExecSQL;
    end;
    AdoQry_Head.Edit;
    AdoQry_Head.fieldbyname('InvBillWHChck').AsInteger:=1;
    AdoQry_Head.Post;
    DBConnect.CommitTrans;
    Act_Check.Enabled:=False;
    Act_DeleteLine.Enabled:=False;
    Act_Modify.Enabled:=False;
    Act_InsertLine.Enabled:=False;
  except
    on e:Exception do
    begin
      DBConnect.RollBackTrans;
      RecOrderror(Self,AdoQry_tmp,E.message);
      DispInfo('单据保存失败，请稍后再试',3);
    end;
  end;
  AdoQry_Material.Filtered:=True;
  AdoQry_Material.EnableControls;
  AdoQry_Body.EnableControls;
end;

end.