bas_vendormerge.pas

一个MRPII系统源代码版本

    sql_txt:='update pc '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //pohistory
    sql_txt:='update pohistory '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //oncheckBill
    sql_txt:='update oncheckBill '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //InvInBill
    sql_txt:='update InvInBill '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //InvOutBill
    sql_txt:='update InvOutBill '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //OPAveragePrice
    sql_txt:='update OPAveragePrice '+
             'set OPApQty=convert(decimal(15,2),OPAveragePrice.OPApQty+aaa.OPApQty),'+
             'OPApAmount=convert(decimal(15,2),OPAveragePrice.OPApAmount+aaa.OPApAmount),'+
             'OPAveragePrice=convert(decimal(15,2),case aaa.OPApQty when 0 then 0 else aaa.OPApAmount/aaa.OPApQty end)'+
             'from OPAveragePrice,(select ItemCode,OPApQty,OPApAmount from OPAveragePrice where VendorCode='+
             format('''%s''',[src])+
             'and ItemCode in (select ItemCode from OPAveragePrice where VendorCode='+
             format('''%s''',[Dest])+')) aaa '+
             'where OPAveragePrice.ItemCode in '+
             '(select ItemCode from OPAveragePrice where VendorCode='+
             format('''%s''',[src])+
             'and ItemCode in '+
             '(select ItemCode from OPAveragePrice where VendorCode='+
             format('''%s''',[Dest])+')) and '+
             'VendorCode='+
             format('''%s''',[Dest])+
             'delete from OPAveragePrice '+
             'where VendorCode='+
             format('''%s''',[Src])+' and '+
             'ItemCode in '+
             '(select ItemCode from OPAveragePrice where VendorCode='+
             format('''%s''',[Src])+
             ' and ItemCode in '+
             '(select ItemCode from OPAveragePrice where VendorCode='+
             format('''%s''',[Dest])+'))'+
             'update OPAveragePrice '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             'where VendorCode='+
             format('''%s''',[src])+' and '+
             'ItemCode not in '+
             '(select ItemCode from OPAveragePrice where VendorCode='+
             format('''%s''',[Src])+
             ' and ItemCode in '+
             '(select ItemCode from OPAveragePrice where VendorCode='+
             format('''%s''',[Dest])+'))';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //OPCurrentInv
    sql_txt:='update OPCurrentInv '+
             'set OPInv=convert(decimal(15,2),OPCurrentInv.OPInv+aaa.OPInv)'+
             'from OPCurrentInv,(select ItemCode,OPInv from OPCurrentInv where VendorCode='+
             format('''%s''',[src])+
             'and ItemCode in (select ItemCode from OPCurrentInv where VendorCode='+
             format('''%s''',[Dest])+')) aaa '+
             'where OPCurrentInv.ItemCode in '+
             '(select ItemCode from OPCurrentInv where VendorCode='+
             format('''%s''',[src])+
             'and ItemCode in '+
             '(select ItemCode from OPCurrentInv where VendorCode='+
             format('''%s''',[Dest])+')) and '+
             'VendorCode='+
             format('''%s''',[Dest])+
             'delete from OPCurrentInv '+
             'where VendorCode='+
             format('''%s''',[Src])+' and '+
             'ItemCode in '+
             '(select ItemCode from OPCurrentInv where VendorCode='+
             format('''%s''',[Src])+
             ' and ItemCode in '+
             '(select ItemCode from OPCurrentInv where VendorCode='+
             format('''%s''',[Dest])+'))'+
             'update OPCurrentInv '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             'where VendorCode='+
             format('''%s''',[src])+' and '+
             'ItemCode not in '+
             '(select ItemCode from OPCurrentInv where VendorCode='+
             format('''%s''',[Src])+
             ' and ItemCode in '+
             '(select ItemCode from OPCurrentInv where VendorCode='+
             format('''%s''',[Dest])+'))';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //
    sql_txt:='update OpMonthSum '+
             'set InvLMQty=convert(decimal(15,2),OpMonthSum.InvLMQty+aaa.InvLMQty),'+
             'InvLMAmount=convert(decimal(15,2),OpMonthSum.InvLMAmount+aaa.InvLMAmount),'+
             'InvInQty=convert(decimal(15,2),OpMonthSum.InvInQty+aaa.InvInQty),'+
             'InvInAmount=convert(decimal(15,2),OpMonthSum.InvInAmount+aaa.InvInAmount),'+
             'InvOutQty=convert(decimal(15,2),OpMonthSum.InvOutQty+aaa.InvOutQty),'+
             'InvOutAmount=convert(decimal(15,2),OpMonthSum.InvOutAmount+aaa.InvOutAmount),'+
             'InvLMPrice=convert(decimal(15,2),case aaa.Invlmqty when 0 then 0 else aaa.InvlmAmount/aaa.Invlmqty end),'+
             'InvInPrice=convert(decimal(15,2),case aaa.InvInqty when 0 then 0 else aaa.InvInAmount/aaa.InvInQty end),'+
             'InvoutPrice=convert(decimal(15,2),case aaa.Invoutqty when 0 then 0 else aaa.InvoutAmount/aaa.Invoutqty end) '+
             ' from OpMonthSum,'+
             ' (select ItemCode,Invlmqty,InvlmAmount,InvlmPrice,InvInqty,'+
             'InvInAmount,InvInPrice,Invoutqty,InvoutAmount,InvoutPrice '+
             'from OpMonthSum where VendorCode='+
             format('''%s''',[src])+
             ' and ItemCode+InvMonth in '+
             '(select ItemCode+InvMonth from OpMonthSum where VendorCode='+
             format('''%s''',[Dest])+')) aaa '+
             'where OpMonthSum.ItemCode in '+
             '(select ItemCode from OpMonthSum where VendorCode='+
             format('''%s''',[Src])+' and ItemCode+InvMonth in '+
             '(select ItemCode+InvMonth from OpMonthSum where VendorCode='+
             format('''%s''',[Dest])+')) and VendorCode='+
             format('''%s''',[Dest])+
             ' delete from OpMonthSum where VendorCode='+
             format('''%s''',[Src])+' and ItemCode in '+
             '(select ItemCode from OpMonthSum where VendorCode='+
             format('''%s''',[Src])+' and '+
             'ItemCode+InvMonth in '+
             '(select ItemCode+InvMonth from OpMonthSum where VendorCode='+
             format('''%s''',[Dest])+')) '+
             ' update OpMonthSum set VendorCode='+
             format('''%s''',[Dest])+'where VendorCode='+
             format('''%s''',[Src])+' and ItemCode not in '+
             '(select ItemCode from OpMonthSum where VendorCode='+
             format('''%s''',[Src])+
             ' and ItemCode+InvMonth in '+
             '(select ItemCode+InvMonth from OpMonthSum where VendorCode='+
             format('''%s''',[Dest])+'))';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //CurrentAp
    sql_txt:='update CurrentAp '+
             'set ApNoInvoice=convert(decimal(15,2),CurrentAp.ApNoInvoice+aaa.ApNoInvoice),'+
             ' ApInvoiced=convert(decimal(15,2),CurrentAp.ApInvoiced+aaa.ApInvoiced) '+
             ' from CurrentAp, '+
             '(select ApNoInvoice,ApInvoiced '+
             ' from CurrentAp '+
             ' where VendorCode='+
             format('''%s''',[Src])+
             ') aaa '+
             ' where CurrentAp.VendorCode='+
             format('''%s''',[Dest])+
             ' delete from CurrentAp where VendorCode='+
             format('''%s''',[src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //ApInvoice
    sql_txt:='update ApInvoice '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
   //PayJournal
    sql_txt:='update PayJournal '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
  //ApBalance
    sql_txt:='update ApBalance '+
             'set InvoiceBalance=convert(decimal(15,2),ApBalance.InvoiceBalance+aaa.InvoiceBalance),'+
             'NoInviceBalance=convert(decimal(15,2),ApBalance.NoInviceBalance+aaa.NoInviceBalance) '+
             'from ApBalance, '+
             '(select InvoiceBalance,NoInviceBalance '+
             'from ApBalance '+
             'where VendorCode='+
             format('''%s''',[src])+') aaa '+
             'where ApBalance.VendorCode='+
             format('''%s''',[Dest])+
             ' delete from ApBalance '+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //Apsum
    sql_txt:='update Apsum '+
             'set ApSUMNoInvoice=convert(decimal(15,2),Apsum.ApSUMNoInvoice+aaa.ApSUMNoInvoice),'+
             'ApSUMPayED=convert(decimal(15,2),Apsum.ApSUMPayED+aaa.ApSUMPayED),'+
             'ApSUMAp=convert(decimal(15,2),Apsum.ApSUMAp+aaa.ApSUMAp),'+
             'ApSUMInvOICED=convert(decimal(15,2),Apsum.ApSUMInvOICED+aaa.ApSUMInvOICED) '+
             ' from Apsum,'+
             '(select ApSUMNoInvoice,ApSUMInvOICED,ApSUMPayED,ApSUMAp '+
             ' from Apsum '+
             'where VendorCode='+
             format('''%s''',[src])+') aaa '+
             'where Apsum.VendorCode='+
             format('''%s''',[Dest])+
             ' delete from Apsum '+
             ' where VendorCode='+
             format('''%s''',[src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
  end;
end;

procedure TFrm_Bas_VendorMerge.btn_CancelClick(Sender: TObject);
begin
  inherited;
  Frm_Bas_VendorMerge.Close;
end;

end.