找出WebService方法用在哪些流程中

DECLARE @METHOD_NAME NVARCHAR(100) = '[要尋找的WebService方法]';  
  
SELECT  
 ProcessDefinition.id,  
 RedefinableHeader.version,  
 ProcessDefinition.processDefinitionName,  
 '最新版' AS STATUS  
FROM  
 WebApplication  
 INNER JOIN IAppDefContainer_AppDef ON WebApplication.OID = ApplicationDefinitionOID  
 INNER JOIN ProcessDefinition ON ProcessDefinition.OID = IAppDefContainer_AppDef.IAppDefContainerOID  
 INNER JOIN ProcessPackage_ProcessDef ON ProcessPackage_ProcessDef.ProcessDefinitionOID = ProcessDefinition.OID  
 INNER JOIN ProcessPackage ON ProcessPackage.OID = ProcessPackage_ProcessDef.ProcessPackageOID  
 INNER JOIN RedefinableHeader ON RedefinableHeader.OID = ProcessPackage.redefinableHeaderOID  
WHERE  
 WebApplication.OID IN (  
 SELECT  
 OID  
 FROM  
 WebApplication  
 WHERE  
 CONCAT(id, CAST(objectVersion AS VARCHAR)) IN (  
 SELECT  
 CONCAT(id, CAST(MAX(objectVersion) AS VARCHAR))  
 FROM  
 WebApplication  
 WHERE  
 urlString LIKE '%' + @METHOD_NAME +'%'  
 GROUP BY  
 id  
 )  
 )  
UNION  
SELECT  
 ProcessDefinition.id,  
 RedefinableHeader.version,  
 ProcessDefinition.processDefinitionName,  
 '進行中' AS STATUS  
FROM  
 WebApplication  
 INNER JOIN IAppDefContainer_AppDef ON WebApplication.OID = IAppDefContainer_AppDef.ApplicationDefinitionOID  
 INNER JOIN ProcessDefinition ON ProcessDefinition.OID = IAppDefContainer_AppDef.IAppDefContainerOID  
 INNER JOIN ProcessInstance ON ProcessInstance.processDefinitionId = ProcessDefinition.id  
 INNER JOIN ProcessPackage_ProcessDef ON ProcessPackage_ProcessDef.ProcessDefinitionOID = ProcessDefinition.OID  
 INNER JOIN ProcessPackage ON ProcessPackage.OID = ProcessPackage_ProcessDef.ProcessPackageOID  
 INNER JOIN RedefinableHeader ON RedefinableHeader.OID = ProcessPackage.redefinableHeaderOID  
WHERE  
 urlString LIKE '%' + @METHOD_NAME +'%'  
 AND ProcessInstance.currentState IN (0, 1, 2)  
UNION  
SELECT  
 ProcessDefinition.id,  
 RedefinableHeader.version,  
 ProcessDefinition.processDefinitionName,  
 '最新版' AS STATUS  
FROM  
 WebServicesApplication  
 INNER JOIN IAppDefContainer_AppDef ON WebServicesApplication.OID = ApplicationDefinitionOID  
 INNER JOIN ProcessDefinition ON ProcessDefinition.OID = IAppDefContainer_AppDef.IAppDefContainerOID  
 INNER JOIN ProcessPackage_ProcessDef ON ProcessPackage_ProcessDef.ProcessDefinitionOID = ProcessDefinition.OID  
 INNER JOIN ProcessPackage ON ProcessPackage.OID = ProcessPackage_ProcessDef.ProcessPackageOID  
 INNER JOIN RedefinableHeader ON RedefinableHeader.OID = ProcessPackage.redefinableHeaderOID  
WHERE  
 WebServicesApplication.OID IN (  
 SELECT  
 OID  
 FROM  
 WebServicesApplication  
 WHERE  
 CONCAT(id, CAST(objectVersion AS VARCHAR)) IN (  
 SELECT  
 CONCAT(id, CAST(MAX(objectVersion) AS VARCHAR))  
 FROM  
 WebServicesApplication  
 WHERE  
 operationName = @METHOD_NAME  
 GROUP BY  
 id  
 )  
 )  
UNION  
SELECT  
 ProcessDefinition.id,  
 RedefinableHeader.version,  
 ProcessDefinition.processDefinitionName,  
 '進行中' AS STATUS  
FROM  
 WebServicesApplication  
 INNER JOIN IAppDefContainer_AppDef ON WebServicesApplication.OID = ApplicationDefinitionOID  
 INNER JOIN ProcessDefinition ON ProcessDefinition.OID = IAppDefContainer_AppDef.IAppDefContainerOID  
 INNER JOIN ProcessInstance ON ProcessInstance.processDefinitionId = ProcessDefinition.id  
 INNER JOIN ProcessPackage_ProcessDef ON ProcessPackage_ProcessDef.ProcessDefinitionOID = ProcessDefinition.OID  
 INNER JOIN ProcessPackage ON ProcessPackage.OID = ProcessPackage_ProcessDef.ProcessPackageOID  
 INNER JOIN RedefinableHeader ON RedefinableHeader.OID = ProcessPackage.redefinableHeaderOID  
WHERE  
 operationName = @METHOD_NAME  
 AND ProcessInstance.currentState IN (0, 1, 2);

easyflow gp, sql — 2019年12月18日