知识:用友做软件开发时,有一个表是专门存储最大单号的:
数据库名: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
————————————————