用友维护语句总结:

用用友比较痛苦,bug多的让人痛苦,打完补丁,常常会出现由于这个补丁导致的新的问题,所以打或不都是一件非常非常痛苦的事.
即使如此,还是做了将近一年的实施和维护.
写了一些语句. 记录下来

1.制单出现产品互斥的清除语句
select UU.CUSER_NAME,UA.cAuth_Name,UTL.dInTime from ua_task TU
INNER JOIN UA_USER UU ON TU.CUSER_ID = UU.CUSER_ID
INNER JOIN UA_Auth UA ON UA.cAuth_ID = TU.cAuth_ID
INNER JOIN UA_TASKLOG UTL ON UTL.CTASKID = TU.CTASKID
where TU.cauth_id IN(
SELECT CREPELLENT FROM UA_CONTROL WHERE CAUTH_ID='IA2001');  --有时可能是'IA2006'

2.供应商和客户被锁定
delete from lockvouch; --问清楚有没有在制单,然后执行就ok了.

3.采购入库单对应采购发票
select distinct  wh.cwhname,pbv.cPBVCode
from rdrecord rd
inner join rdrecords rds on rd.id = rds.id
inner join purbillvouchs pbvs on rds.autoid = pbvs.RdsId
inner join purbillvouch pbv on pbvs.pbvid = pbv.pbvid
inner join warehouse wh on wh.cwhcode = rd.cwhcode;
财务制单的时候,因为分工,所以要按照仓制单,但是发票又没有保存这个信息,所以要对应起来.
--改动采购发票,把仓库转为发票的业务员, 通过业务员来制单(必须保证,一张发票对应一个仓库)
BEGIN TRANSACTION;
--UPDATE PBV SET PBV.cPersonCode = '***********'    ---不加注到表体,直接更新业务员
UPDATE PBVS SET PBVS.CDEFINE33 = '*********'           ----先将仓库拷贝到表体的某一行,我用的是define33
from purbillvouchs PBVS
INNER JOIN PURBILLVOUCH PBV ON PBVS.PBVID = PBV.PBVID
inner join rdrecords rds on PBVS.rdsid = rds.autoid
inner join rdrecord rd on rds.id = rd.id
where PBVS.cdefine33 is null and PBVS.rdsid is not null
and rd.cwhcode = '***************' ;

--改动应收应付单 (如果已经审核了,又不想放弃的话,只好去修改应收单了.)
update set cperson='CL01'
where cvouchtype='02' and cvouchid in(
SELECT cpbvcode FROM PURBILLVOUCH
 where pbvid in(
select  distinct pbvid
from purbillvouchs where cdefine33 like '***********'
) ) and cperson is null

4.登录人员试图,(主要是给那些,比较有管理欲望的老大们看的,他们要看到有人才踏实)
create view v_leniz_user_detail as

select ut.cTaskid as [登陆标识],
uu.cUser_name as [登陆人],
uaa.cAuth_Name +'[' + us.cSub_Name +'('+   uaa.cAuth_Id  + ')]' as [单元模块],
utl.dINtime as [登陆时间],
utl.cStation as [登陆主机],
ut.cAcc_id +'_' + ua.cAcc_name + '[' + cast(ut.iYear as nvarchar(20))+ ']'  as [帐套名称] from
ua_task ut
inner join ua_tasklog utl on ut.cTaskid = utl.cTaskid
inner join UA_Account ua on ut.cAcc_id = ua.cAcc_id
inner join ua_user uu on uu.cUser_id = ut.cUser_id
inner join UA_Auth uaa on uaa.cAuth_Id = ut.cAuth_Id
inner join UA_SubSys us on us.cSub_id = ut.cSub_id

5.梯状的物料类别显示,(为了各个职能的统计方便)

SELECT
   cinvccode,
   CASE
      WHEN len(cinvccode)=1  Then className1
      WHEN len(cinvccode)=3  Then className1 + '-' + className2
      WHEN len(cinvccode)=5  Then className1 + '-' + className2 + '-' + className3
      WHEN len(cinvccode)=7  Then className1 + '-' + className2 + '-' + className3 + '-' + className4
      WHEN len(cinvccode)=9  Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5
      WHEN len(cinvccode)=11 Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5 + '-' + className6
      ELSE ''
   END AS className
FROM v_leniz_step_inventoryclass

 

create view v_leniz_step_inventoryclass as
select
vl.cinvccode,
vl.classCode1, isNUll(invl1.cinvcname,'') as className1,
vl.classCode2, isNUll(invl2.cinvcname,'') as className2,
vl.classCode3, isNUll(invl3.cinvcname,'') as className3,
vl.classCode4, isNUll(invl4.cinvcname,'') as className4,
vl.classCode5, isNUll(invl5.cinvcname,'') as className5,
vl.classCode6, isNUll(invl6.cinvcname,'') as className6
from 
v_leniz_inventoryclass vl
left join inventoryclass invl1 on  vl.classCode1 = invl1.cinvccode
left join inventoryclass invl2 on  vl.classCode2 = invl2.cinvccode
left join inventoryclass invl3 on  vl.classCode3 = invl3.cinvccode
left join inventoryclass invl4 on  vl.classCode4 = invl4.cinvccode
left join inventoryclass invl5 on  vl.classCode5 = invl5.cinvccode
left join inventoryclass invl6 on  vl.classCode6 = invl6.cinvccode

 

create view v_leniz_stepclass as
SELECT
   cinvccode as classCode,
   CASE
      WHEN len(cinvccode)=1  Then className1
      WHEN len(cinvccode)=3  Then className1 + '-' + className2
      WHEN len(cinvccode)=5  Then className1 + '-' + className2 + '-' + className3
      WHEN len(cinvccode)=7  Then className1 + '-' + className2 + '-' + className3 + '-' + className4
      WHEN len(cinvccode)=9  Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5
      WHEN len(cinvccode)=11 Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5 + '-' + className6
      ELSE ''
   END AS className
FROM v_leniz_step_inventoryclass

建了一堆试图,其实用一个语句是可以搞定的,反正做了就不去改了.  将这个 字段更新到物料的自定义类别中就ok了.
导到excel时 要教那些人用分列的方式把这列分解开来.

6.产成品添置单价后更新 本币金额 (典型的bug)
begin transaction;
update rdrecords set iorimoney = iprice,iorisum = iprice
 where ioricost is not null and iorimoney is null;
commit;

7.存货核算出现调拨单制单时出现 “数据冲突，缩小单据类型 *” 类似提示

delete from Ia_pzmutex ; --不过之前最好select一下,免得清掉正常的业务锁定