inv_priceaudit_poblnc_b.pas

一个MRPII系统源代码版本

        begin
          Close;
          sql.clear;
          sql.Add('select convert(varchAr(10),getdate(),102) as Currentdate ');
          open;
          Current_Date:=fieldbyname('Currentdate').asstring;
        end;
        if chck_Month<copy(Current_Date,1,7) then
           Current_Date:=chck_Month+copy(datetostr(incMonth(strtodate(chck_Month+'.01'),1)-1),8,3)
        else
           Current_Date:=chck_Month+copy(Current_Date,8,3);
        //如果新旧数据有改变，就产生红，蓝单，并重新计算当前加权平均价
        ChangeInvStatus(AdoQry_tmp,
                        AdoQry_InvInBill.fieldbyname('whCode').asstring,
                        copy(Current_Date,1,7));
        // 保存，如果产生红、蓝单，则原单、红单的InvBillFinChck=1
        //原单改InvBillFinChck=1
        with AdoQry_tmp do
        begin
          Close;
          sql.clear;
          sql.Add(' update InvInBill '+
                  '   set InvBillFinChck=1 '+
                  ' where InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring);
          execsql;
        end;

        //取旧的单据体记录，用于回冲加权平均价及产生一张回冲的单据(负向，红色)
       //不管核价还是暂估价，都回冲一加权平均价，因为在采购入库时改经更改了加权平均价
       // if NeedChangeAveragePrice(AdoQry_tmp,copy(Current_Date,1,7)) then
       if True then
        begin
          AdoQry_tmp.Close;
          AdoQry_tmp.sql.clear;
          //更改加权平均价  回冲当前加权平均价
          AdoQry_tmp.sql.text:=
             'Update AveragePrice Set'
            +' ApQty=ApQty-l.Qty'
            +',ApAmount=ApAmount-l.Amount'
            +' From'
            +'(Select ItemCode,Sum(InvBillQty) As Qty,Sum(InvBillNoTaxAmount) As Amount'
            +' From InvInBillLine'
            +' Where InvBillId='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring
            +' Group By ItemCode) l'
            +' Where AveragePrice.WHCode='''+AdoQry_InvInBill.fieldbyname('whCode').asstring+''''
            +' And AveragePrice.ItemCode=l.ItemCode';
          AdoQry_tmp.execsql;
        end;
        tmp_memo:='''订单结算核价''';
        Billno:='';
         //产生新单据号记录更改后的单据记录 (蓝单)
        Billno:=getno(AdoQry_Body.Connection,
                       Trim(AdoQry_InvInBill.fieldbyname('whCode').asstring)+
                       'R'+
                       copy(Current_Date,3,2)+
                       copy(Current_Date,6,2),
                       'InvBill');
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.text:=
            'insert InvInBill'+
                     '(InvBillno,'+
                     'whCode,'+
                     'InvBilldate,'+
                     'InvBillMonth,'+
                     'BillTypeCode,'+
                     'VendorCode,'+
                     'EmployeeCode,'+
                     'Stk_EmployeeCode,'+
                     //币种要控制
                     'currencyCode,'+
                     'pono,'+
                     'InvBilltaxrate,'+
                     'WhPositionCode,'+
                     'InvBillStkchck,'+
                     'InvBillwhchck,'+
                     'ParentBillno,'+
                     'RealBillFlag,'+
                     'PriceAuditTimes,'+
                     'InvBillremArk)'+
            'select '''+Billno+''','+
                     'whCode,'+
                     ''''+Current_Date+''','+
                     ''''+chck_Month+''','+
                     'BillTypeCode,'+
                     'VendorCode,'+
                     ''''+userCode+''','+
                     ''''+userCode+''','+
                     'currencyCode,'+
                     'pono,'+
                     ''''+lbl_taxrate.Caption+''','+
                     'WhPositionCode,'+
                     '1,'+
                     '1,'+
                     'InvBillno,'+
                     '1,'+
                     inttostr(PriceAuditTimes)+','+
                     tmp_memo+
            ' from InvInBill '+
            ' where InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring;
        AdoQry_tmp.execsql;
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.Add(' select @@IDENTITY as Billid ');
        AdoQry_tmp.open;
        blue_Billid:=AdoQry_tmp.fieldbyname('Billid').asstring;
         //这里有特殊处理，先把旧单据物料增加进来，然后把金额改变的物料更改
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.text:=
             'insert InvInBillline(InvBillid,'+
                     'InvBilllineno,'+
                     'ItemCode,'+
                     'pono,'+
                     'polineno,'+
                     'BilllineremArk,'+
                     'InvBillqty) '+
             ' select '+blue_Billid+','+
                      'InvBilllineno,'+
                      'ItemCode,'+
                      'pono,'+
                      'polineno,'+
                      'BilllineremArk,'+
                      'InvBillqty '+
             ' from InvInBillline '+
             '  where InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring;
        AdoQry_tmp.execsql;
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.text:=
             ' update InvInBillline '+
                 ' set InvBillPrice=pl.realpotaxPrice,'+
                      'InvBillAmount=InvBillqty*pl.realpotaxPrice,'+
                      'InvBillnotaxPrice=pl.realponotaxPrice,'+
                      'InvBillnotaxAmount=InvBillqty*pl.realponotaxPrice,'+
                      'InvBillPricec=pl.realpotaxPrice,'+
                      'InvBillAmountc=InvBillqty*pl.realpotaxPrice,'+
                      'InvBillnotaxPricec=pl.realponotaxPrice,'+
                      'InvBillnotaxAmountc=InvBillqty*pl.realponotaxPrice '+
            ' from  InvInBillline il,poline pl '+
            ' where il.InvBillid='+blue_Billid+
            '   and il.pono=pl.pono '+
            '   and il.polineno=pl.polineno ';
        AdoQry_tmp.execsql;

        //产生新单据号(负向 红色)
        Billno:=getno(AdoQry_Body.Connection,
                       Trim(AdoQry_InvInBill.fieldbyname('whCode').asstring)+
                       'R'+
                       copy(Current_Date,3,2)+
                       copy(Current_Date,6,2),
                       'InvBill');
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.text:=
            'insert InvInBill'+
                   '(InvBillno,'+
                   'whCode,'+
                   'InvBilldate,'+
                   'InvBillMonth,'+
                   'BillTypeCode,'+
                   'VendorCode,'+
                   'EmployeeCode,'+
                   //币种要控制
                   'currencyCode,'+
                   'pono,'+
                   'InvBilltaxrate,'+
                   'WhPositionCode,'+
                   'InvBillStkchck,'+
                   'InvBillwhchck,'+
                   'InvBillFinChck,'+
                   'Stk_EmployeeCode,'+
                   'ParentBillno,'+
                   'RealBillFlag,'+
                   'InvBillremArk)'+
            'select '''+Billno+''','+
                    'whCode,'+
                    ''''+Current_Date+''','+
                    ''''+chck_Month+''','+
                    'BillTypeCode,'+
                    'VendorCode,'+
                    ''''+userCode+''','+
                    'currencyCode,'+
                    'pono,'+
                    lbl_taxrate.Caption+','+
                    'WhPositionCode,'+
                    '1,'+
                    '1,'+
                    '1,'+
                    ''''+userCode+''','+
                    'InvBillno,'+
                    '0,'+
                    tmp_memo+
           ' from InvInBill '+
           ' where InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring ;
        AdoQry_tmp.execsql;
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.Add(' select @@IDENTITY as Billid ');
        AdoQry_tmp.open;
        Billid:=AdoQry_tmp.fieldbyname('Billid').asstring;
         //把旧单据增加到InvBillline  红色单据
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.text:=
             'insert InvInBillline(InvBillid,'+
                     'InvBilllineno,'+
                     'ItemCode,'+
                     'pono,'+
                     'polineno,'+
                     'BilllineremArk,'+
                     'InvBillqty,'+
                     'InvBillPrice,'+
                     'InvBillAmount,'+
                     'InvBillnotaxPrice,'+
                     'InvBillnotaxAmount,'+
                     'InvBillPricec,'+
                     'InvBillAmountc,'+
                     'InvBillnotaxPricec,'+
                     'InvBillnotaxAmountc,'+
                     'Valuation) '+
             ' select '+Billid+','+
                      'InvBilllineno,'+
                      'ItemCode,'+
                      'pono,'+
                      'polineno,'+
                      'BilllineremArk,'+
                      '-(InvBillqty),'+
                      'InvBillPrice,'+
                      '-(InvBillAmount),'+
                      'InvBillnotaxPrice,'+
                      '-(InvBillnotaxAmount),'+
                      'InvBillPricec,'+
                      '-(InvBillAmountc),'+
                      'InvBillnotaxPricec,'+
                      '-(InvBillnotaxAmountc),'+
                      '0' +
              ' from InvInBillline '+
              '  where InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring;
        AdoQry_tmp.execsql;

         //重新更新加权平均价
        if True then
        begin
           AdoQry_tmp.Close;
           AdoQry_tmp.sql.clear;
           AdoQry_tmp.sql.text:=
             'Update AveragePrice Set'
            +' ApQty=ApQty+l.Qty'
            +',ApAmount=ApAmount+l.Amount'
            +',AveragePrice=case when (ApQty+l.Qty)=0 then AveragePrice else (ApAmount+l.Amount)/(ApQty+l.Qty) end '
            +' From '
            +'(Select ItemCode,Sum(InvBillQty) As Qty,Sum(InvBillNoTaxAmount) As Amount'
            +' From InvInBillLine '
            +' Where InvBillId='+blue_Billid
            +' Group By ItemCode) l'
            +' Where AveragePrice.WHCode='''+AdoQry_InvInBill.fieldbyname('whCode').asstring+''''
            +' And AveragePrice.ItemCode=l.ItemCode';
           AdoQry_tmp.ExecSQL;
        end;
         //取出所有的新金额和
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.Add('select sum(InvBillAmount) as oldAmount '+
                ' from InvInBillline '+
                '  where InvBillid='+blue_Billid);
        AdoQry_tmp.open;
        oldAmount:=AdoQry_tmp.fieldbyname('oldAmount').asfloat;
        //更改当前供应商应收应付
        with AdoQry_tmp do
        begin
           Close;
           sql.clear;
           sql.Add( ' update CurrentAp '+
                    '    set ApNoInvoice=ApNoInvoice+('+floattostr(round(oldAmount*100)/100)+')'+
                    ' where VendorCode='''+getCode(lbl_Vendor.Caption)+''' and '+
                    '       currencyCode='''+getCode(lbl_Currency.Caption)+'''');
           execsql;
        end;
      end;
      next;
    end;
  end;
  AdoQry_Body.Connection.CommitTrans;
  act_Save.Enabled:=False;
  DispInfo('存盘成功!',3);
  except
    begin
      AdoQry_Body.Connection.RollBackTrans;
      DispInfo('存盘失败，请重试！',1);
      abort;
    end;
  end;
end;


procedure TFrm_Inv_PriceAudit_PoBlnc_B.Act_ModifyExecute(Sender: TObject);
begin
  inherited;
  //
end;

procedure TFrm_Inv_PriceAudit_PoBlnc_B.DataSourceDataChange(
  Sender: TObject; Field: TField);
begin
  inherited;
  AdoQry_Detail.Filtered:=False;
  AdoQry_Detail.Filter:=' polineno='+inttostr(AdoQry_Body.fieldbyname('polineno').asinteger);
  AdoQry_Detail.Filtered:=True;
end;

procedure TFrm_Inv_PriceAudit_PoBlnc_B.FormActivate(Sender: TObject);
begin
  inherited;
    act_Save.Enabled:=True;
end;

procedure TFrm_Inv_PriceAudit_PoBlnc_B.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  if noChange=1 then
  begin
   inherited;
  end; 
end;

procedure TFrm_Inv_PriceAudit_PoBlnc_B.AdoQry_BodyAfterPost(
  DataSet: TDataSet);
begin
  //noChange=1 表示数据源己经改变
  noChange:=1;
  inherited;
end;

end.