用友最大单据号修复语句

知识：用友做软件开发时，有一个表是专门存储最大单号的：

数据库名：Ufsystem.mdb，表名：UA_Identity，此表的字段如下：

字段名    意义

cAcc_Id    002

cVouchType    单据类型编码

iFatherId    主表标识，即主表ID

iChildId    子表标识，即子表ID

在表名：UA_Identity中，举例来说，对应cVouchType='Tr'的iFatherId和iChildId的数应对应主表TransVouch和子表TransVouchs最后一行的ID号，如果小于这两个数，保存单据时就会出现错误提示。

一般地，主表最大ID字段为ID，子表最大ID字段为 AutoID

两个错误提示的解决办法：

一、在使用用友软件中，在做某些业务单据时，无法保存，出现如下提示

这个问题主要是同一业务有多个人操作或在操作时非正常退出引起的，解决这个问题很简单，首先要保证这个业务就你一个人在操作，解决方法如下：

1.出现不能保存时，点确定，再点保存。

2.如果还是保存不上，返回第1步。

我发现，在Ufsystem.. UA_Identity表中，每保存一次，相应记录的两个字段iFatherId和iChildId会自动＋1，这样，反复执行这两步，就产生了最大的单号，可以保存了。

二、错误提示(不同的单据数据名不同，汉字基本相同)：

手工解决办法，打开对应数据库，主表TransVouch，子表TransVouchs，移到最下面一行，抄下其最大ID号，再打开Ufsystem.mdb….UA_Identity，找到相应的记录，填入这两个ID号即可。

也可以通过下面的SQL语句进行方便的修改，下面的SQL语句全部都是找出主表和子表的最大单据号，然后填入Ufsystem.mdb，表名为UA_Identity相应的记录字段中。

注意在执行某个SQL时，先把它复制到记事本中，然后修改002和2013，再打开SQL查询分析器执行。

范例：将"002"替换成"001"，"2013"替换成"2013"

原SQL语句

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..RDRECORD),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..RDRECORDS)

where (cvouchtype='rd'and cacc_id='002')

替换后

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_001_2013..RDRECORD),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_001_2013..RDRECORDS)

where (cvouchtype='rd'and cacc_id='001')

各业务重复单号的解决方案

--1.出入库单（收发记录主子表）

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..RDRECORD),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..RDRECORDS)

where (cvouchtype='rd'and cacc_id='002')

--2.采购订单PO_Pomain和PO_Podetails

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(moID,0),8) AS INT))as id from UFDATA_002_2013..PO_Pomain),

ichildid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..PO_Podetails)

where (cvouchtype='Pomain'andi cacc_id='002')

21 委外订单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(moID,0),8) AS INT))as id from UFDATA_002_2013..OM_MOMain),

ichildid= (Select MAX(CAST(RIGHT(isnull(MODetailsID,0),8) AS INT))as id from UFDATA_002_2013..OM_MODetails)

where (cvouchtype='om_mo'and cacc_id='002')

22 客户调价单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013.. SA_CusPriceJustMain),

ichildid= (Select MAX(CAST(RIGHT(isnull(icusjustautoid,0),8) AS INT))as id from UFDATA_002_2013.. SA_CusUPrice)

where (cvouchtype=' SA_CusPriceJustMain'and cacc_id='002')

--3.采购发票PURBILL和PurBillVouchS

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(PBVID,0),8) AS INT))as id from UFDATA_002_2013..PurBillVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..PurBillVouchS)

where (cvouchtype='PURBILL'and cacc_id='002')

--4.采购结算单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(PSVID,0),8) AS INT))as id from UFDATA_002_2013..PurSettleVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..PurSettleVouchs)

where (cvouchtype='PURSTID'and cacc_id='002')

--5.采购请购单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..PU_AppVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..PU_AppVouchs)

where (cvouchtype='PUAPP'and cacc_id='002')

--6.采购到货单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..PU_ArrivalVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..PU_ArrivalVouchS)

where (cvouchtype='PUARRIVAL'and cacc_id='002')

--7.销售发票

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(SBVID,0),8) AS INT))as id from UFDATA_002_2013..SaleBillVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..SaleBillVouchS)

where (cvouchtype='BILLVOUCH'and cacc_id='002')

--8.销售发货单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(DLID,0),8) AS INT))as id from UFDATA_002_2013..DispatchList),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..DispatchListS)

where (cvouchtype='DISPATCH'and cacc_id='002')

--9.销售订单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..SO_SOMain),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..SO_SODetails)

where (cvouchtype='SOMain'and cacc_id='002')

--10.销售_委托代销结算单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..SA_SettleVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..SA_SettleVouchS)

where (cvouchtype='SETTLEVOUCH'and cacc_id='002')

--11.销售_销售报价单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..SA_QuoMain),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..SA_QuoDetails)

where (cvouchtype='QUOMAIN'and cacc_id='002')

--12.销售_销售支出单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..SalePayVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..SalePayVouchS)

where (cvouchtype='SALEPAY'and cacc_id='002')

--13.销售_代垫费用单（应收）

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..ExpenseVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..ExpenseVouchS)

where (cvouchtype='EXPENSE'and cacc_id='002')

--14.库存_盘点单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..CheckVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..CheckVouchS)

where (cvouchtype='CH'and cacc_id='002')

--14.存货_调整单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..JustInVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..JustInVouchS)

where (cvouchtype='JU'and cacc_id='002')

--15.库存_限额领料单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..MatchVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..MatchVouchS)

where (cvouchtype='MA'and cacc_id='002')

--16.库存_库存调拨单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..TransVouch),

ichildid= (Select MAX(CAST(RIGHT(isnull(AUTOID,0),8) AS INT))as id from UFDATA_002_2013..TransVouchS)

where (cvouchtype='TR'and cacc_id='002')

--16.采购_销售现结线索号,本字段为"XJ"加流水号，所以要去除2位字符,但是反向操作"弃结"系统不回写减少ua_identity，下次增加直接加1。

update ufsystem..ua_identity

set ifatherid= (SELECT max(CAST(RIGHT(isnull(STUFF(cCancelNo,1,2, ''),0),8) as int))as id from UFDATA_002_2013..Ap_CloseBill),

ichildid= (SELECT max(CAST(RIGHT(isnull(STUFF(cCancelNo,1,2, ''),0),8) as int))as id from UFDATA_002_2013..Ap_CloseBill)

where (cvouchtype='XJ'and cacc_id='002')

--16.收款单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(iID,0),8) AS INT))as id from UFDATA_002_2013..Ap_CloseBill),

ichildid= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..Ap_CloseBillS)

where (cvouchtype='SK'and cacc_id='002')

--16.生产订单

update ufsystem..ua_identity

set ifatherid= (Select MAX(CAST(RIGHT(isnull(moID,0),8) AS INT))as id from UFDATA_002_2013..mom_order),

ichildidi= (Select MAX(CAST(RIGHT(isnull(ID,0),8) AS INT))as id from UFDATA_002_2013..Ap_CloseBillS)

where (cvouchtype='SK'and cacc_id='002')

17 收发记录（10.1以后版本）

select MAX(id)

from (select MAX(id) as id from RdRecord01 union

select MAX(id) as id from RdRecord08 union

select MAX(id) as id from RdRecord09 union

select MAX(id) as id from rdrecord10 union

select MAX(id) as id from rdrecord11 union

select MAX(id) as id from rdrecord32 union

select MAX(id) as id from rdrecord34)t

select MAX(autoid)

from (select MAX(autoid) as autoid from RdRecords01 union

select MAX(autoid) as autoid from RdRecords08 union

select MAX(autoid) as autoid from RdRecords09 union

select MAX(autoid) as autoid from rdrecords10 union

select MAX(autoid) as autoid from rdrecords11 union

select MAX(autoid) as autoid from rdrecords32 union

select MAX(autoid) as autoid from rdrecords34)t附录：业务所对应的表格名：

对应单据名称

cvouchtype

data库中相关联主子表

相关联主子表ID字段标识

采购请购单

PuApp

PU_AppVouch/s

ID/Autoid

采购订单

Pomain

PO_Pomain/PO_Podetails

POID/ID

采购到货单

PuArrival

PU_ArrivalVouch/s

ID/autoid

采购入库单

rd

Rdrecord/s

ID/Autoid

采购发票

Purbill

PurBillVouch/s

PBVID/ID

销售报价单

QuoMain

SA_QuoMain/ SA_QuoDetails

ID/Autoid

销售订单

Somain

SO_SOMain/SO_SODetails

ID/Autoid

销售发（退）货单

Dispatch

DispatchList/s

DLID/Autoid

销售发票

Billvouch

SaleBillVouch/s

SBVID/Autoid

委托代销发货(退)单

Dispatch

DispatchList/s

DLID/Autoid

委托代销结算单

SettleVouch

SA_SettleVouch/s

ID/Autoid

委托代销调整单

Dispatch

DispatchList/s

DLID/Autoid

销售调拨单(红、蓝)

Billvouch

SaleBillVouch/s

SBVID/Autoid

销售零售日报(红、蓝)

Billvouch

SaleBillVouch/s

SBVID/Autoid

代垫费用单

Expense

ExpenseVouch/s

ID/Autoid

销售支出单

Salepay

SalePayVouch/s

ID/autoid

委外发票

Wwpurbill

OM_PurBillVouch/s

PBVID/ID

产成品入库单

rd

Rdrecord/s

ID/Autoid

其他入库单

rd

Rdrecord/s

ID/Autoid

材料出库单

rd

Rdrecord/s

ID/Autoid

其他出库单

rd

Rdrecord/s

ID/Autoid

调拨单

tr

TransVouch/s

ID/Autoid

盘点单

ch

CheckVouch/s

ID/autoid

限额领料单

ma

MatchVouch/s

ID/autoid

不合格品记录单

sc

ScrapVouch/ScrapVouchs

ID/Autoid

不合格品处理单

sc

ScrapVouch/ScrapVouchs

ID/Autoid

货位调整单

ad

AdjustPVouch/s

ID/Autoid

应收应付单

sk

ap_closebill/s

IID/ID

客户调价单

SA_CusPriceJustMain

Sa_cuspricejustmain/sa_cuspricejustdetail

Id/autoid

委外订单

om_mo

Om_momain/om_modetails

Moid/MODetailsID

委外用料单子表

OM_Materials

OM_MOMaterials

MOMaterialsID

组装拆卸形态转换单

as

Assemvouch/assemvouchs

————————————————