teamcenter 按照审批节点和节点的目标分组统计任务数量

select count(*),

-- ( case

-- when 'L8_DesignRevision' then '图对象'

-- when 'L8_DocumentRevision' then '文档'

-- when 'L8_JcsjDocumentRevision' then '检测数据'

-- WHEN INSTR(v.pobject_type,'PartRevision') >0 then '物料'

-- when 'L8_ECN' then '更改单'

-- when 'L8_PR' then '问题'

-- else v.pobject_type

-- END) AS a ,

(case

WHEN INSTR(v.pobject_type,'PartRevision') >0 then '物料'

WHEN INSTR(v.pobject_type,'DesignRevision') >0 then '图对象'

WHEN INSTR(v.pobject_type,'DocumentRevision') >0 then '文档'

WHEN v.pobject_type='L8_ECN' then 'ECN'

WHEN v.pobject_type='L8_PR' then 'PR'

ELSE v.pobject_type

End) as

pobject_type1

--,wf.signoffuserid

-- ,wf.taskname

from infodba.V_WORKFLOW_ATTACHMENTS v ,infodba.v_workflow_signoffs wf

where v.processuid=wf.processuid

and wf.signoffdate >= to_date('2020-01-01','yyyy-MM-dd')

and wf.signoffdate <= to_date('2024-06-01','yyyy-MM-dd')

AND wf.taskname LIKE '%标准化%'

AND wf.signoffuserid ='xxb02'

group by v.pobject_type

--,wf.signoffuserid

-- ,wf.taskname

order by v.pobject_type；

 

流程附件视图：

create or replace view v_workflow_attachments as
select job.puid as processuid,t.ptype_name, rel.rsecondary_objectu attachmentsuid ,obj.pobject_name,obj.pobject_desc,obj.pobject_type,pi.pitem_id,pir.pitem_revision_id
from pepmjob job,pimanrelation rel,pimantype t,pworkspaceobject obj,pitemRevision pir,pitem pi
where rel.rprimary_objectu = job.rroot_tasku and t.puid=rel.rrelation_typeu and obj.puid = rel.rsecondary_objectu and pir.puid=obj.puid and pir.ritems_tagu =pi.puid

union

select job.puid as processuid,t.ptype_name, rel.rsecondary_objectu attachmentsuid ,obj.pobject_name,obj.pobject_desc,obj.pobject_type,pi.pitem_id,'' "pitem_revision_id"
from pepmjob job,pimanrelation rel,pimantype t,pworkspaceobject obj,pitem pi
where rel.rprimary_objectu = job.rroot_tasku and t.puid=rel.rrelation_typeu and obj.puid = rel.rsecondary_objectu and pi.puid=obj.puid;

 

流程信息视图：

create or replace view v_workflow_signoffs as
select tmp.ptemplate_name,wf.puid processuid,wfobj.pobject_name processname, ta.puid taskuid,taobj.pobject_name taskname,taobj.pobject_type task_type,u.puser_id signoffuserid,u.puser_name signoffusername,
app.plast_mod_date signoffdate,decode(pstate_value,8,'Commited','NoCommit') as signoffresult,pstate_value taskstate,
ta.pcomments
from pepmjob wf, pepmtask ta,pworkspaceobject wfobj, pworkspaceobject taobj, PEPMTASKTEMPLATE tmp,ppom_application_object app, ppom_user u
where ta.rparent_processu = wf.puid and wf.rprocess_templateu = tmp.puid and wfobj.puid = wf.puid and taobj.puid = ta.puid
and taobj.pobject_type='EPMDoTask' and app.puid = ta.puid and u.puid = ta.rresponsible_partyu
--order by tmp.ptemplate_name,signoffdate

union

select tmp.ptemplate_name,wf.puid processuid,wfobj.pobject_name processname, ta.puid taskuid,taobj.pobject_name taskname,taobj.pobject_type task_type,pfu.puser_id signoffuserid,pfu.puser_name signoffusername,
pf.pdecision_date signoffdate,decode(pf.pdecision,89,'Approve',78,'Rejected','Nodecid') signoffresult,pstate_value taskstate,
pf.pcomments
from pepmjob wf, pepmtask ta,pworkspaceobject wfobj, pworkspaceobject taobj, PEPMTASKTEMPLATE tmp,psignoff pf, ppom_user pfu--, ppom_user sfu
where ta.rparent_processu = wf.puid and wf.rprocess_templateu = tmp.puid and wfobj.puid = wf.puid and taobj.puid = ta.puid
and exists(select 'x' from pepmtask ctask where ctask.rparent_tasku = ta.puid and exists(select 'x' from pworkspaceobject obj where obj.puid = ctask.puid and
obj.pobject_type = 'EPMPerformSignoffTask') and exists(select 'x' from pimanrelation attr where attr.rprimary_objectu = ctask.puid
and attr.rsecondary_objectu = pf.puid))
and exists(select 'x' from ppom_member mem where mem.puid = pf.rgroup_memberu and mem.ruseru = pfu.puid)

order by ptemplate_name,processuid,signoffdate
;