|
|
马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？注册账号
×
公司由于供应商编码整合，几个账套，几千个供应商编码需要调整，按标准功能，只能通过供应商合并功能一个一个手工操作。
咨询用友并没有批量修改的方案，因此自行研究，直接更改数据库，目前验证是OK，分享给大家，但更改数据库存在风险，各自慎重，本人不负责。
第一步：导入数据至u8账套
建表：[供应商编码变更数据]
字段：oldCode,newCode
第二步：执行供应商复制，用于解决外键约束
- SELECT [cVenCode]
- ,[cVenName]
- ,[cVenAbbName]
- ,[cVCCode]
- ,[cDCCode]
- ,[cTrade]
- ,[cVenAddress]
- ,[cVenPostCode]
- ,[cVenRegCode]
- ,[cVenBank]
- ,[cVenAccount]
- ,[dVenDevDate]
- ,[cVenLPerson]
- ,[cVenPhone]
- ,[cVenFax]
- ,[cVenEmail]
- ,[cVenPerson]
- ,[cVenBP]
- ,[cVenHand]
- ,[cVenPPerson]
- ,[iVenDisRate]
- ,[iVenCreGrade]
- ,[iVenCreLine]
- ,[iVenCreDate]
- ,[cVenPayCond]
- ,[cVenIAddress]
- ,[cVenIType]
- ,[cVenHeadCode]
- ,[cVenWhCode]
- ,[cVenDepart]
- ,[iAPMoney]
- ,[dLastDate]
- ,[iLastMoney]
- ,[dLRDate]
- ,[iLRMoney]
- ,[dEndDate]
- ,[iFrequency]
- ,[bVenTax]
- ,[cVenDefine1]
- ,[cVenDefine2]
- ,[cVenDefine3]
- ,[cCreatePerson]
- ,[cModifyPerson]
- ,[dModifyDate]
- ,[cRelCustomer]
- ,[iId]
- ,[cBarCode]
- ,[cVenDefine4]
- ,[cVenDefine5]
- ,[cVenDefine6]
- ,[cVenDefine7]
- ,[cVenDefine8]
- ,[cVenDefine9]
- ,[cVenDefine10]
- ,[cVenDefine11]
- ,[cVenDefine12]
- ,[cVenDefine13]
- ,[cVenDefine14]
- ,[cVenDefine15]
- ,[cVenDefine16]
- ,[fRegistFund]
- ,[iEmployeeNum]
- ,[iGradeABC]
- ,[cMemo]
- ,[bLicenceDate]
- ,[dLicenceSDate]
- ,[dLicenceEDate]
- ,[iLicenceADays]
- ,[bBusinessDate]
- ,[dBusinessSDate]
- ,[dBusinessEDate]
- ,[iBusinessADays]
- ,[bProxyDate]
- ,[dProxySDate]
- ,[dProxyEDate]
- ,[iProxyADays]
- ,[bPassGMP]
- ,[bVenCargo]
- ,[bProxyForeign]
- ,[bVenService]
- ,[cVenTradeCCode]
- ,[cVenBankCode]
- ,[cVenExch_name]
- ,[iVenGSPType]
- ,[iVenGSPAuth]
- ,[cVenGSPAuthNo]
- ,[cVenBusinessNo]
- ,[cVenLicenceNo]
- ,[bVenOverseas]
- ,[bVenAccPeriodMng]
- ,[cVenPUOMProtocol]
- ,[cVenOtherProtocol]
- ,[cVenCountryCode]
- ,[cVenEnName]
- ,[cVenEnAddr1]
- ,[cVenEnAddr2]
- ,[cVenEnAddr3]
- ,[cVenEnAddr4]
- ,[cVenPortCode]
- ,[cVenPrimaryVen]
- ,[fVenCommisionRate]
- ,[fVenInsueRate]
- ,[bVenHomeBranch]
- ,[cVenBranchAddr]
- ,[cVenBranchPhone]
- ,[cVenBranchPerson]
- ,[cVenSSCode]
- ,[cOMWhCode]
- ,[cVenCMProtocol]
- ,[cVenIMProtocol]
- ,[iVenTaxRate]
- ,[dVenCreateDatetime]
- ,[cVenMnemCode]
- ,[cVenContactCode]
- ,[cvenbankall]
- into #1
- FROM [Vendor] a inner join [供应商编码变更数据] b on a.[cVenCode]=b.oldcode
-
- --select cVenDefine16 from [Vendor] where isnull(cVenDefine16,-1)<>-1 --检查自定义项16
- update #1 set cVenDefine16=1 --标记复制的新档案
-
- update [Vendor] set cVenDefine16=2 where [cVenCode] in(select oldcode from [供应商编码变更数据]) --标记复制过的旧档案
-
- --更改供应商编码
- update A set [cVenCode]=b.newCode,[CVenHeadCode]=b.newCode from #1 A inner join [供应商编码变更数据] b on A.[cVenCode]=B.oldCode and A.[CVenHeadCode]=B.oldCode --更改临时表档案编码（上级公司编码一致）
- update A set [cVenCode]=b.newCode from #1 A inner join [供应商编码变更数据] b on A.[cVenCode]=B.oldCode and A.[CVenHeadCode]<>B.oldCode;
- update A set [CVenHeadCode]=b.newCode from #1 A inner join [供应商编码变更数据] b on A.[CVenHeadCode]=B.oldCode; --更改档案中公司编码
-
- update #1 set [cVenName]=[cVenName] + '#$新档案',[cVenAbbName]=[cVenAbbName] + '#$新档案';
-
- insert into [Vendor]([cVenCode]
- ,[cVenName]
- ,[cVenAbbName]
- ,[cVCCode]
- ,[cDCCode]
- ,[cTrade]
- ,[cVenAddress]
- ,[cVenPostCode]
- ,[cVenRegCode]
- ,[cVenBank]
- ,[cVenAccount]
- ,[dVenDevDate]
- ,[cVenLPerson]
- ,[cVenPhone]
- ,[cVenFax]
- ,[cVenEmail]
- ,[cVenPerson]
- ,[cVenBP]
- ,[cVenHand]
- ,[cVenPPerson]
- ,[iVenDisRate]
- ,[iVenCreGrade]
- ,[iVenCreLine]
- ,[iVenCreDate]
- ,[cVenPayCond]
- ,[cVenIAddress]
- ,[cVenIType]
- ,[cVenHeadCode]
- ,[cVenWhCode]
- ,[cVenDepart]
- ,[iAPMoney]
- ,[dLastDate]
- ,[iLastMoney]
- ,[dLRDate]
- ,[iLRMoney]
- ,[dEndDate]
- ,[iFrequency]
- ,[bVenTax]
- ,[cVenDefine1]
- ,[cVenDefine2]
- ,[cVenDefine3]
- ,[cCreatePerson]
- ,[cModifyPerson]
- ,[dModifyDate]
- ,[cRelCustomer]
- ,[iId]
- ,[cBarCode]
- ,[cVenDefine4]
- ,[cVenDefine5]
- ,[cVenDefine6]
- ,[cVenDefine7]
- ,[cVenDefine8]
- ,[cVenDefine9]
- ,[cVenDefine10]
- ,[cVenDefine11]
- ,[cVenDefine12]
- ,[cVenDefine13]
- ,[cVenDefine14]
- ,[cVenDefine15]
- ,[cVenDefine16]
- ,[fRegistFund]
- ,[iEmployeeNum]
- ,[iGradeABC]
- ,[cMemo]
- ,[bLicenceDate]
- ,[dLicenceSDate]
- ,[dLicenceEDate]
- ,[iLicenceADays]
- ,[bBusinessDate]
- ,[dBusinessSDate]
- ,[dBusinessEDate]
- ,[iBusinessADays]
- ,[bProxyDate]
- ,[dProxySDate]
- ,[dProxyEDate]
- ,[iProxyADays]
- ,[bPassGMP]
- ,[bVenCargo]
- ,[bProxyForeign]
- ,[bVenService]
- ,[cVenTradeCCode]
- ,[cVenBankCode]
- ,[cVenExch_name]
- ,[iVenGSPType]
- ,[iVenGSPAuth]
- ,[cVenGSPAuthNo]
- ,[cVenBusinessNo]
- ,[cVenLicenceNo]
- ,[bVenOverseas]
- ,[bVenAccPeriodMng]
- ,[cVenPUOMProtocol]
- ,[cVenOtherProtocol]
- ,[cVenCountryCode]
- ,[cVenEnName]
- ,[cVenEnAddr1]
- ,[cVenEnAddr2]
- ,[cVenEnAddr3]
- ,[cVenEnAddr4]
- ,[cVenPortCode]
- ,[cVenPrimaryVen]
- ,[fVenCommisionRate]
- ,[fVenInsueRate]
- ,[bVenHomeBranch]
- ,[cVenBranchAddr]
- ,[cVenBranchPhone]
- ,[cVenBranchPerson]
- ,[cVenSSCode]
- ,[cOMWhCode]
- ,[cVenCMProtocol]
- ,[cVenIMProtocol]
- ,[iVenTaxRate]
- ,[dVenCreateDatetime]
- ,[cVenMnemCode]
- ,[cVenContactCode]
- ,[cvenbankall]) select [cVenCode]
- ,[cVenName]
- ,[cVenAbbName]
- ,[cVCCode]
- ,[cDCCode]
- ,[cTrade]
- ,[cVenAddress]
- ,[cVenPostCode]
- ,[cVenRegCode]
- ,[cVenBank]
- ,[cVenAccount]
- ,[dVenDevDate]
- ,[cVenLPerson]
- ,[cVenPhone]
- ,[cVenFax]
- ,[cVenEmail]
- ,[cVenPerson]
- ,[cVenBP]
- ,[cVenHand]
- ,[cVenPPerson]
- ,[iVenDisRate]
- ,[iVenCreGrade]
- ,[iVenCreLine]
- ,[iVenCreDate]
- ,[cVenPayCond]
- ,[cVenIAddress]
- ,[cVenIType]
- ,[cVenHeadCode]
- ,[cVenWhCode]
- ,[cVenDepart]
- ,[iAPMoney]
- ,[dLastDate]
- ,[iLastMoney]
- ,[dLRDate]
- ,[iLRMoney]
- ,[dEndDate]
- ,[iFrequency]
- ,[bVenTax]
- ,[cVenDefine1]
- ,[cVenDefine2]
- ,[cVenDefine3]
- ,[cCreatePerson]
- ,[cModifyPerson]
- ,[dModifyDate]
- ,[cRelCustomer]
- ,[iId]
- ,[cBarCode]
- ,[cVenDefine4]
- ,[cVenDefine5]
- ,[cVenDefine6]
- ,[cVenDefine7]
- ,[cVenDefine8]
- ,[cVenDefine9]
- ,[cVenDefine10]
- ,[cVenDefine11]
- ,[cVenDefine12]
- ,[cVenDefine13]
- ,[cVenDefine14]
- ,[cVenDefine15]
- ,[cVenDefine16]
- ,[fRegistFund]
- ,[iEmployeeNum]
- ,[iGradeABC]
- ,[cMemo]
- ,[bLicenceDate]
- ,[dLicenceSDate]
- ,[dLicenceEDate]
- ,[iLicenceADays]
- ,[bBusinessDate]
- ,[dBusinessSDate]
- ,[dBusinessEDate]
- ,[iBusinessADays]
- ,[bProxyDate]
- ,[dProxySDate]
- ,[dProxyEDate]
- ,[iProxyADays]
- ,[bPassGMP]
- ,[bVenCargo]
- ,[bProxyForeign]
- ,[bVenService]
- ,[cVenTradeCCode]
- ,[cVenBankCode]
- ,[cVenExch_name]
- ,[iVenGSPType]
- ,[iVenGSPAuth]
- ,[cVenGSPAuthNo]
- ,[cVenBusinessNo]
- ,[cVenLicenceNo]
- ,[bVenOverseas]
- ,[bVenAccPeriodMng]
- ,[cVenPUOMProtocol]
- ,[cVenOtherProtocol]
- ,[cVenCountryCode]
- ,[cVenEnName]
- ,[cVenEnAddr1]
- ,[cVenEnAddr2]
- ,[cVenEnAddr3]
- ,[cVenEnAddr4]
- ,[cVenPortCode]
- ,[cVenPrimaryVen]
- ,[fVenCommisionRate]
- ,[fVenInsueRate]
- ,[bVenHomeBranch]
- ,[cVenBranchAddr]
- ,[cVenBranchPhone]
- ,[cVenBranchPerson]
- ,[cVenSSCode]
- ,[cOMWhCode]
- ,[cVenCMProtocol]
- ,[cVenIMProtocol]
- ,[iVenTaxRate]
- ,[dVenCreateDatetime]
- ,[cVenMnemCode]
- ,[cVenContactCode]
- ,[cvenbankall]
- from #1;
-
- drop table #1;
-
-
- select * from [Vendor]
代码太长，附件无法上传，有需要的再联系吧
|
|
|站长微信|Archiver|手机版|小黑屋|用友之家
( 蜀ICP备07505338号|
51072502110008 )
IP卡
狗仔卡
发表于 2017-12-2 11:10:47
提升卡
置顶卡
变色卡
千斤顶
显身卡