Can Advanced search query all Owner's current WorkItem?

[毓謝]

I am using Windchill PDMLink Release 11.0 and Datecode with CPS M030-CPS09
Can Advanced search query all the current WorkItems of the Owner? Criteria: ( (Owner = 'Tim Hsieh(admin_tim)') AND (Status ≠ 'Completed') );Type=Work Item; Context=All Contexts If there is no interface, whether Is there a T-SQL syntax to directly query the database?

______________________________________
Translated the text to english using google translate on 26-Apr-23 at 9:50am IST by vnamboodheri

---------------------------------------------

Subject: Advanced search 是否可以查詢所有的 Owner目前的 WorkItem?

Body: Advanced search 是否可以查詢所有的 Owner目前的 WorkItem? Criteria: ( (Owner = 'Tim Hsieh(admin_tim)') AND (Status ≠ 'Completed') );Type=Work Item; Context=All Contexts 如果沒有介面, 是否有 T-SQL語法可以直接查詢資料庫?
---------------------------------------------

[avillanueva]

Work items are not something that can be returned via Advanced Search but you can do this via a Query Builder report. You might be able to link this report to a search results but none of what you are searching for is an object in advanced search.

[毓謝]

The following is the information I received through SQL Server Profiler, and I have adjusted it myself. It is about 90% similar to the information queried by Advanced Search.  It works for MS-SQL Server queries

WITH CheckWorkItemsList as
(
SELECT
A1.classnameA2A2 'WfAssignedActivityclassnameA2A2'
,A1.idA2A2
,A1.typeadministrativeLock
,A1.classnamekeydomainRef
,A1.securityLabels
,CONVERT(VARCHAR, NULL) 'Column6'
,CONVERT(VARCHAR, NULL) 'Column7'
,A1.idA3domainRef
,CONVERT(VARCHAR, NULL) 'Column9'
,A2.blob$entrySetadHocAclAC
,A2.classnameA2A2AC
,A2.branchIdA2typeDefinitionRefeAC
,A2.idA3A2ownershipAC
,A2.idA2A2AC
,A2.typeadministrativeLockAC
,A2.classnamekeydomainRefAC
,A2.securityLabelsAC
,A2.entrySetadHocAclAC
,A2.statestateAC
,A2.idA3domainRefAC
,A2.classnamekeyA2ownershipAC
--,A0.idA2A2 'WorkItemidA2A2'
,A0.idA3A4 'WorkItemidA3A4'
,A0.updateCountA2
,A0.classnameA2A2
,Replace(A0.classnamekeyB4,'VR:wt.','') 'classnamekeyB4'
,A0.role
,A0.status
,CONVERT(DECIMAL(28), NULL) 'Column28'
,CONVERT(DECIMAL(28), NULL) 'Column29'
,UPPER(A2.namecontainerInfo) C0
,A0.idA3A2ownership
,CWTUser.name 'CWTUsername'

FROM WfAssignedActivity A1 WITH(NOLOCK)
RIGHT OUTER JOIN WorkItem A0 WITH(NOLOCK) ON (
(A0.idA3A4 = A1.idA2A2)
AND (A1.markForDeleteA2 = 0)
)
LEFT OUTER JOIN WTUser as CWTUser WITH(NOLOCK)
on A0.idA3A2ownership=CWTUser.idA2A2
LEFT OUTER JOIN (
SELECT A2.blob$entrySetadHocAcl blob$entrySetadHocAclAC
,A2.classnameA2A2 classnameA2A2AC
,NULL branchIdA2typeDefinitionRefeAC
,NULL idA3A2ownershipAC
,A2.idA2A2 idA2A2AC
,A2.typeadministrativeLock typeadministrativeLockAC
,A2.classnamekeydomainRef classnamekeydomainRefAC
,A2.securityLabels securityLabelsAC
,A2.entrySetadHocAcl entrySetadHocAclAC
,CONVERT(VARCHAR, NULL) statestateAC
,A2.idA3domainRef idA3domainRefAC
,CONVERT(VARCHAR, NULL) classnamekeyA2ownershipAC
,A2.idA2A2
,A2.markForDeleteA2
,A2.namecontainerInfo
FROM ExchangeContainer A2 WITH(NOLOCK)

UNION ALL

SELECT A2.blob$entrySetadHocAcl blob$entrySetadHocAclAC
,A2.classnameA2A2 classnameA2A2AC
,NULL branchIdA2typeDefinitionRefeAC
,NULL idA3A2ownershipAC
,A2.idA2A2 idA2A2AC
,A2.typeadministrativeLock typeadministrativeLockAC
,A2.classnamekeydomainRef classnamekeydomainRefAC
,A2.securityLabels securityLabelsAC
,A2.entrySetadHocAcl entrySetadHocAclAC
,CONVERT(VARCHAR, NULL) statestateAC
,A2.idA3domainRef idA3domainRefAC
,CONVERT(VARCHAR, NULL) classnamekeyA2ownershipAC
,A2.idA2A2
,A2.markForDeleteA2
,A2.namecontainerInfo
FROM OrgContainer A2 WITH(NOLOCK)

UNION ALL

SELECT A2.blob$entrySetadHocAcl blob$entrySetadHocAclAC
,A2.classnameA2A2 classnameA2A2AC
,A2.branchIdA2typeDefinitionRefe branchIdA2typeDefinitionRefeAC
,NULL idA3A2ownershipAC
,A2.idA2A2 idA2A2AC
,A2.typeadministrativeLock typeadministrativeLockAC
,A2.classnamekeydomainRef classnamekeydomainRefAC
,A2.securityLabels securityLabelsAC
,A2.entrySetadHocAcl entrySetadHocAclAC
,CONVERT(VARCHAR, NULL) statestateAC
,A2.idA3domainRef idA3domainRefAC
,CONVERT(VARCHAR, NULL) classnamekeyA2ownershipAC
,A2.idA2A2
,A2.markForDeleteA2
,A2.namecontainerInfo
FROM WTLibrary A2 WITH(NOLOCK)

UNION ALL

SELECT A2.blob$entrySetadHocAcl blob$entrySetadHocAclAC
,A2.classnameA2A2 classnameA2A2AC
,A2.branchIdA2typeDefinitionRefe branchIdA2typeDefinitionRefeAC
,NULL idA3A2ownershipAC
,A2.idA2A2 idA2A2AC
,A2.typeadministrativeLock typeadministrativeLockAC
,A2.classnamekeydomainRef classnamekeydomainRefAC
,A2.securityLabels securityLabelsAC
,A2.entrySetadHocAcl entrySetadHocAclAC
,CONVERT(VARCHAR, NULL) statestateAC
,A2.idA3domainRef idA3domainRefAC
,CONVERT(VARCHAR, NULL) classnamekeyA2ownershipAC
,A2.idA2A2
,A2.markForDeleteA2
,A2.namecontainerInfo
FROM PDMLinkProduct A2 WITH(NOLOCK)
) A2 ON (
(A1.idA3containerReference = A2.idA2A2)
AND (A2.markForDeleteA2 = 0)
)
WHERE (
(
(
(UPPER(A0.status) NOT LIKE N'COMPLETED')
OR (A0.status IS NULL)
)
)
)
AND (A0.markForDeleteA2 = 0)
)
SELECT
b.name 'Name'
,a.CWTUsername 'Owner'
,a.classnamekeyB4
,a.C0 'Context'
,a.status
,a.role
,c.name 'WfProcessName'
,c.WT_FBI_COMPUTE_10_0 'WfProcessNameCOMPUTE'
,c.createStampA2
,c.modifyStampA2
,b.state
FROM
CheckWorkItemsList as a WITH(NOLOCK)
LEFT JOIN WfAssignedActivity as b WITH(NOLOCK)
on a.WorkItemidA3A4=b.idA2A2
LEFT JOIN WfProcess as c
on b.idA3parentProcessRef=c.idA2A2
where CWTUsername in
(
'uid'
)
Order by CWTUsername,C0