DBA_GET_WORFLOW_FOLDER

[LadyBird0]

USE [HRNet]
GO
/****** Object: StoredProcedure [dbo].[DBA_GET_WORFLOW_FOLDER] Script Date: 08/28/2018 15:22:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- procedure to find a workflow in the admin console folders.
ALTER PROC [dbo].[DBA_GET_WORFLOW_FOLDER](@workflowname AS VARCHAR(128) )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select OC_WORKFLOWS.WORKFLOWNAME,
	   f1.FOLDER_NAME,
	   f2.FOLDER_NAME,
	   f3.FOLDER_NAME,
	   f4.FOLDER_NAME 
from OC_ADMINCONSOLE_FOLDERS f1 
inner join  OC_WORKFLOWS on f1.FOLDER_ID = OC_WORKFLOWS.FOLDERID
left outer join OC_ADMINCONSOLE_FOLDERS f2 on f1.PARENT_ID = f2.FOLDER_ID
left outer join OC_ADMINCONSOLE_FOLDERS f3 on f2.PARENT_ID = f3.FOLDER_ID
left outer join OC_ADMINCONSOLE_FOLDERS f4 on f3.PARENT_ID = f4.FOLDER_ID
where OC_WORKFLOWS.WORKFLOWNAME like @workflowname
group by OC_WORKFLOWS.WORKFLOWNAME,f1.FOLDER_NAME,f2.FOLDER_NAME,f3.FOLDER_NAME,f4.FOLDER_NAME

--To test
--EXEC dbo.DBA_GET_WORFLOW_FOLDER @workflowname='Update Role when ISLineManager field is changed'
--EXEC dbo.DBA_GET_WORFLOW_FOLDER @workflowname='A%'

END;