修改活动所有者
select *from planactivity p where p.ida2a2=4386861;
update planactivity p set p.ida3a2ownership=90497 where p.ida2a2=4386861;
查询未关闭的流程，流程在xx节点的文档编号，进程id
select m.wtdocumentnumber,p.ida2a2 from wfprocess p
,wtdocument d,wtdocumentmaster m,wfprocesstemplate t,workitem w,wfassignedactivity a where p.state='OPEN_RUNNING'
and p.businessobjreference like '%WTDocument%'
and p.ida3a5=t.ida2a2
and t.name='流程名'
and a.ida3parentprocessref=p.ida2a2
and w.ida3a4=a.ida2a2
and (w.status='POTENTIAL' or w.status='ACCEPTED')
and a.name='活动节点名'
and d.ida3masterreference=m.ida2a2
and d.branchiditerationinfo=SUBSTR(p.businessobjreference,22,length(p.businessobjreference))
;
删除游离的可交付结果
select *from PlanDeliverable d where d.ida2a2 not in (select l.ida3b5 from plandeliverablelink l);
delete from PlanDeliverable d where d.ida2a2 not in (select l.ida3b5 from plandeliverablelink l);
查询项目模板
select p.namecontainerinfo,m.name from project2 p,
DefaultWTContainerTemplate t,
WTContainerTemplateMaster m
where p.ida3containertemplatereferen=t.ida2a2
and t.ida3masterreference=m.ida2a2
;
查询产品容器第一层文件夹
select p.namecontainerinfo,f.name,f.markfordeletea2
from subfolder f,PDMLinkProduct p,subfolderlink l
where f.classnamekeycontainerreferen='wt.pdmlink.PDMLinkProduct'
and f.ida3containerreference=p.ida2a2
and f.markfordeletea2=0
and f.ida2a2=l.ida3b5
and l.classnamekeyroleaobjectref='wt.folder.Cabinet'
查询产品容器所有文件夹
select *from(select (select p.namecontainerinfo from PDMLinkProduct p where p.ida2a2=tmp.containerId) as productName,
path from(
SELECT
(select f.classnamekeycontainerreferen from subfolder f where f.ida2a2=l.ida3b5) as containerType,
(select f.ida3containerreference from subfolder f where f.ida2a2=l.ida3b5) as containerId,
sys_connect_by_path(REPLACE((select f.name from subfolder f where f.ida2a2=l.ida3b5),'/','|'), '/') AS Path
FROM subfolderlink l
START WITH l.classnamekeyroleaobjectref ='wt.folder.Cabinet'
CONNECT BY l.ida3a5 = prior l.ida3b5
) tmp where containerType='wt.pdmlink.PDMLinkProduct')
按组织查找用户
select
o.namecontainerinfo,
u.name,
u.fullname
from wtuser u,AdministrativeDomain a,orgcontainer o
where u.ida3domainref=a.ida2a2
and o.ida2a2=a.ida3containerreference
and o.namecontainerinfo='组织名';