文章来源:http://blog.sina.com.cn/s/blog_6169ba7b0101atdg.html
知识:用友做软件开发时,有一个表是专门存储最大单号的:
数据库名:Ufsystem.mdb,表名:UA_Identity,此表的字段如下:
字段名
cAcc_Id
cVouchType
iFatherId
iChildId
在表名: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')
--16.委外核销单
update ufsystem..ua_identity
set ifatherid= (Select MAX(CAST(RIGHT(isnull(msID,0),8) AS INT))as id from UFDATA_002_2013..OM_MatSettleVouch),
ichildidi= (Select MAX(CAST(RIGHT(isnull(MSDetailsID,0),8) AS INT))as id from UFDATA_002_2013..OM_MatSettleVouchs)
where (cvouchtype='om_ms'and cacc_id='002')
附录:业务所对应的表格名:
对应单据名称 | 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 |
领料申请单 | mv | MaterialAppVouch/MaterialAppVouchs | ID/AUTOID |
备注
1
强制类型转换
cast(column_name as varchar2(20))之类的
2
Isnull
Isnull |
参数check_expression ,是待检查是否为空的表达式,参数replacement_value是带替换的指定值,如果参数check_expression为空则参数replacement_value进行填充,如果check_expression不为空,则返回表达式相应的返回值类型,需要注意到是参数check_expression和参数replacement_value的类型需要保持一致,否则要进行相应的转换
3
select right('1234',2)
结果:34
反回字符右边的指定长度的字符