ALTER FUNCTION [dbo].[GetOrgInOrgWideDetail]
	(@org_id varchar(8), 
	@ref_date varchar(8), 
	@landscape varchar(3),
	@is_start bit,
	@org_name varchar(250), 
	@org_level VARCHAR(2),
	@org_id_00 VARCHAR(8), 
	@org_name_00 VARCHAR(250), 
	@org_id_01 VARCHAR(8), 
	@org_name_01 VARCHAR(250), 
	@org_id_02 varchar(8), 
	@org_name_02 varchar(250), 
	@org_id_03 varchar(8), 
	@org_name_03 varchar(250), 
	@org_id_04 varchar(8), 
	@org_name_04 varchar(250), 
	@org_id_05 varchar(8), 
	@org_name_05 varchar(250), 
	@org_id_06 varchar(8), 
	@org_name_06 varchar(250), 
	@org_id_07 varchar(8), 
	@org_name_07 varchar(250), 
	@org_id_08 varchar(8), 
	@org_name_08 varchar(250)
	)
RETURNS @result TABLE (
	org_id varchar(8), 
	org_name varchar(250), 
	org_level varchar(4),
	parent_org_id varchar(8),
	parent_org_name varchar(250),
	parent_org_level varchar(4),
	org_id_00 varchar(8), 
	org_name_00 varchar(250), 
	org_id_01 varchar(8), 
	org_name_01 varchar(250), 
	org_id_02 varchar(8), 
	org_name_02 varchar(250), 
	org_id_03 varchar(8), 
	org_name_03 varchar(250), 
	org_id_04 varchar(8), 
	org_name_04 varchar(250), 
	org_id_05 varchar(8), 
	org_name_05 varchar(250), 
	org_id_06 varchar(8), 
	org_name_06 varchar(250), 
	org_id_07 varchar(8), 
	org_name_07 varchar(250), 
	org_id_08 varchar(8), 
	org_name_08 varchar(250))
/*
SELECT * FROM dbo.GetOrgInOrgWide('00000411','20110725','100')
*/
BEGIN

	DECLARE @result_parent TABLE (
		org_id varchar(8), 
		org_name varchar(250), 
		org_level varchar(4),
		parent_org_id varchar(8),
		parent_org_name varchar(250),
		parent_org_level varchar(4),
		org_id_00 varchar(8), 
		org_name_00 varchar(250), 
		org_id_01 varchar(8), 
		org_name_01 varchar(250), 
		org_id_02 varchar(8), 
		org_name_02 varchar(250), 
		org_id_03 varchar(8), 
		org_name_03 varchar(250), 
		org_id_04 varchar(8), 
		org_name_04 varchar(250), 
		org_id_05 varchar(8), 
		org_name_05 varchar(250), 
		org_id_06 varchar(8), 
		org_name_06 varchar(250), 
		org_id_07 varchar(8), 
		org_name_07 varchar(250), 
		org_id_08 varchar(8), 
		org_name_08 varchar(250))

	DECLARE @result_child TABLE (
		org_id varchar(8), 
		org_name varchar(250), 
		org_level varchar(4),
		parent_org_id varchar(8),
		parent_org_name varchar(250),
		parent_org_level varchar(4),
		org_id_00 varchar(8), 
		org_name_00 varchar(250), 
		org_id_01 varchar(8), 
		org_name_01 varchar(250), 
		org_id_02 varchar(8), 
		org_name_02 varchar(250), 
		org_id_03 varchar(8), 
		org_name_03 varchar(250), 
		org_id_04 varchar(8), 
		org_name_04 varchar(250), 
		org_id_05 varchar(8), 
		org_name_05 varchar(250), 
		org_id_06 varchar(8), 
		org_name_06 varchar(250), 
		org_id_07 varchar(8), 
		org_name_07 varchar(250), 
		org_id_08 varchar(8), 
		org_name_08 varchar(250))

	DECLARE @_org_name varchar(250), 
		@_org_level varchar(4),
		@_org_id_00 varchar(8), 
		@_org_name_00 varchar(250), 
		@_org_id_01 varchar(8), 
		@_org_name_01 varchar(250), 
		@_org_id_02 varchar(8), 
		@_org_name_02 varchar(250), 
		@_org_id_03 varchar(8), 
		@_org_name_03 varchar(250), 
		@_org_id_04 varchar(8), 
		@_org_name_04 varchar(250), 
		@_org_id_05 varchar(8), 
		@_org_name_05 varchar(250), 
		@_org_id_06 varchar(8), 
		@_org_name_06 varchar(250), 
		@_org_id_07 varchar(8), 
		@_org_name_07 varchar(250), 
		@_org_id_08 varchar(8), 
		@_org_name_08 varchar(250)


	IF (@is_start = 1)
	BEGIN
		INSERT INTO @result_parent
		SELECT org.[object], org.description, orgd.org_level, '','', '',
			CASE WHEN orgd.org_level = '00' THEN org.[object] ELSE '' END,
			CASE WHEN orgd.org_level = '00' THEN org.description ELSE '' END,
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			''
		FROM hr_md_orm_object org
			INNER JOIN hr_md_orm_o_o orgd
				ON org.[object] = orgd.[object]
		WHERE
			org.landscape = @landscape
			AND org.[class] = 'O'
			AND org.[object] = @org_id
			AND org.start_date <= @ref_date
			AND org.end_date >= @ref_date
			AND orgd.landscape = @landscape
			AND orgd.[object] = @org_id
			AND orgd.start_date <= @ref_date
			AND orgd.end_date >= @ref_date
	
		SELECT TOP 1 @_org_name = org_name, @_org_level = org_level
		FROM @result_parent;
		
		IF @_org_level = '00' SELECT @_org_id_00 = @org_id, @_org_name_00 = @_org_name
		IF @_org_level = '01' SELECT @_org_id_01 = @org_id, @_org_name_01 = @_org_name
		IF @_org_level = '02' SELECT @_org_id_02 = @org_id, @_org_name_02 = @_org_name
		IF @_org_level = '03' SELECT @_org_id_03 = @org_id, @_org_name_03 = @_org_name
		IF @_org_level = '04' SELECT @_org_id_04 = @org_id, @_org_name_04 = @_org_name
		IF @_org_level = '05' SELECT @_org_id_05 = @org_id, @_org_name_05 = @_org_name
		IF @_org_level = '06' SELECT @_org_id_06 = @org_id, @_org_name_06 = @_org_name
		IF @_org_level = '07' SELECT @_org_id_07 = @org_id, @_org_name_07 = @_org_name
		IF @_org_level = '08' SELECT @_org_id_08 = @org_id, @_org_name_08 = @_org_name
		
		INSERT INTO @result
		SELECT * FROM @result_parent
	END
	ELSE
	BEGIN
		SET @_org_name = @org_name
		SET @_org_level = @org_level
		SET @_org_id_00 = @org_id_00
		SET @_org_name_00 = @org_name_00
		SET @_org_id_01 = @org_id_01
		SET @_org_name_01 = @org_name_01
		SET @_org_id_02 = @org_id_02
		SET @_org_name_02 = @org_name_02
		SET @_org_id_03 = @org_id_03
		SET @_org_name_03 = @org_name_03
		SET @_org_id_04 = @org_id_04
		SET @_org_name_04 = @org_name_04
		SET @_org_id_05 = @org_id_05
		SET @_org_name_05 = @org_name_05
		SET @_org_id_06 = @org_id_06
		SET @_org_name_06 = @org_name_06
		SET @_org_id_07 = @org_id_07
		SET @_org_name_07 = @org_name_07
		SET @_org_id_08 = @org_id_08
		SET @_org_name_08 = @org_name_08
	END
		
	DECLARE 
		@__org_id varchar(8), 
		@__org_name varchar(50), 
		@__org_level varchar(3),
		@__parent_org_id varchar(8), 
		@__parent_org_name varchar(50), 
		@__parent_org_level varchar(3),
		@__org_id_00 varchar(8), 
		@__org_name_00 varchar(50), 
		@__org_id_01 varchar(8), 
		@__org_name_01 varchar(50), 
		@__org_id_02 varchar(8), 
		@__org_name_02 varchar(50), 
		@__org_id_03 varchar(8), 
		@__org_name_03 varchar(50), 
		@__org_id_04 varchar(8), 
		@__org_name_04 varchar(50), 
		@__org_id_05 varchar(8), 
		@__org_name_05 varchar(50), 
		@__org_id_06 varchar(8), 
		@__org_name_06 varchar(50), 
		@__org_id_07 varchar(8), 
		@__org_name_07 varchar(50), 
		@__org_id_08 varchar(8), 
		@__org_name_08 varchar(50)
	
	DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
	FOR 
		SELECT org.[object], org.[description], orgd.org_level,
			@org_id,
			@_org_name,
			@_org_level,
			CASE WHEN orgd.org_level = '00' THEN org.[object] ELSE @_org_id_00 END,
			CASE WHEN orgd.org_level = '00' THEN org.description ELSE @_org_name_00 END,
			CASE WHEN orgd.org_level = '01' THEN org.[object] ELSE @_org_id_01 END,
			CASE WHEN orgd.org_level = '01' THEN org.description ELSE @_org_name_01 END,
			CASE WHEN orgd.org_level = '02' THEN org.[object] ELSE @_org_id_02 END,
			CASE WHEN orgd.org_level = '02' THEN org.description ELSE @_org_name_02 END,
			CASE WHEN orgd.org_level = '03' THEN org.[object] ELSE @_org_id_03 END,
			CASE WHEN orgd.org_level = '03' THEN org.description ELSE @_org_name_03 END,
			CASE WHEN orgd.org_level = '04' THEN org.[object] ELSE @_org_id_04 END,
			CASE WHEN orgd.org_level = '04' THEN org.description ELSE @_org_name_04 END,
			CASE WHEN orgd.org_level = '05' THEN org.[object] ELSE @_org_id_05 END,
			CASE WHEN orgd.org_level = '05' THEN org.description ELSE @_org_name_05 END,
			CASE WHEN orgd.org_level = '06' THEN org.[object] ELSE @_org_id_06 END,
			CASE WHEN orgd.org_level = '06' THEN org.description ELSE @_org_name_06 END,
			CASE WHEN orgd.org_level = '07' THEN org.[object] ELSE @_org_id_07 END,
			CASE WHEN orgd.org_level = '07' THEN org.description ELSE @_org_name_07 END,
			CASE WHEN orgd.org_level = '08' THEN org.[object] ELSE @_org_id_08 END,
			CASE WHEN orgd.org_level = '08' THEN org.description ELSE @_org_name_08 END
		FROM hr_md_orm_relationship rel
			INNER JOIN hr_md_orm_object org
				ON rel.[rel_object] = org.[object]
			INNER JOIN hr_md_orm_o_o orgd
				ON org.[object] = orgd.[object]
		WHERE
			rel.landscape = @landscape
			AND rel.[class] = 'O'
			AND rel.[object] = @org_id
			AND rel.rel_type = '001'
			AND rel.rel_dir = 'A'
			AND rel.start_date <= @ref_date
			AND rel.end_date >= @ref_date
			AND rel.rel_class = 'O'
			
			AND org.landscape = @landscape
			AND org.[class] = 'O'
			AND org.start_date <= @ref_date
			AND org.end_date >= @ref_date
			
			AND orgd.landscape = @landscape
			AND orgd.start_date <= @ref_date
			AND orgd.end_date >= @ref_date

	OPEN cur
	
	
	DECLARE @fetch_status INT
	SET @fetch_status = 0
	WHILE @fetch_status = 0
	BEGIN
		FETCH NEXT FROM cur INTO @__org_id, @__org_name, @__org_level,
			@__parent_org_id, @__parent_org_name, @__parent_org_level,
			@__org_id_00, @__org_name_00, 
			@__org_id_01, @__org_name_01, 
			@__org_id_02, @__org_name_02, 
			@__org_id_03, @__org_name_03, 
			@__org_id_04, @__org_name_04, 
			@__org_id_05, @__org_name_05, 
			@__org_id_06, @__org_name_06, 
			@__org_id_07, @__org_name_07, 
			@__org_id_08, @__org_name_08 
		
		SET @fetch_status = @@FETCH_STATUS
		IF (@fetch_status = 0)
		BEGIN
			INSERT INTO @result_child VALUES (@__org_id, @__org_name, @__org_level, --@__org_name, @__org_level,
			@__parent_org_id, @__parent_org_name, @__parent_org_level,
			@__org_id_00, @__org_name_00, 
			@__org_id_01, @__org_name_01, 
			@__org_id_02, @__org_name_02, 
			@__org_id_03, @__org_name_03, 
			@__org_id_04, @__org_name_04, 
			@__org_id_05, @__org_name_05, 
			@__org_id_06, @__org_name_06, 
			@__org_id_07, @__org_name_07, 
			@__org_id_08, @__org_name_08 )
			
			INSERT INTO @result_child
			SELECT * FROM GetOrgInOrgWideDetail(@__org_id, @ref_date, @landscape, 0, @__org_name, @__org_level,
				@__org_id_00, @__org_name_00, 
				@__org_id_01, @__org_name_01, 
				@__org_id_02, @__org_name_02, 
				@__org_id_03, @__org_name_03, 
				@__org_id_04, @__org_name_04, 
				@__org_id_05, @__org_name_05, 
				@__org_id_06, @__org_name_06,
				@__org_id_07, @__org_name_07, 
				@__org_id_08, @__org_name_08 
			)
		END
	END
	
	CLOSE cur
	DEALLOCATE cur
	
	INSERT INTO @result
	SELECT * FROM @result_child

	RETURN
END
