ALTER FUNCTION [dbo].[CBKT_GetOrgInOrgWideDetail]
	(@org_id VARCHAR(20), 
	@ref_date VARCHAR(20), 
	--@landscape varchar(3),
	@is_start bit,
	@org_name varchar(250), 
	@org_level varchar(20),
	@org_id_00 varchar(20), 
	@org_name_00 varchar(250), 
	@org_id_01 varchar(20), 
	@org_name_01 varchar(250), 
	@org_id_02 varchar(20), 
	@org_name_02 varchar(250), 
	@org_id_03 varchar(20), 
	@org_name_03 varchar(250), 
	@org_id_04 varchar(20), 
	@org_name_04 varchar(250), 
	@org_id_05 varchar(20), 
	@org_name_05 varchar(250), 
	@org_id_06 varchar(20), 
	@org_name_06 varchar(250), 
	@org_id_07 varchar(20), 
	@org_name_07 varchar(250), 
	@org_id_08 varchar(20), 
	@org_name_08 varchar(250),
	@org_id_09 varchar(20), 
	@org_name_09 varchar(250),
	@org_id_10 varchar(20), 
	@org_name_10 varchar(250),
	@org_id_11 varchar(20), 
	@org_name_11 varchar(250),
	@org_id_12 VARCHAR(20), 
	@org_name_12 varchar(250)
	)
RETURNS @result TABLE (
	org_id varchar(20), 
	org_name varchar(250), 
	org_level varchar(20),
	parent_org_id varchar(20),
	parent_org_name varchar(250),
	parent_org_level varchar(20),
	org_id_00 varchar(20), 
	org_name_00 varchar(250), 
	org_id_01 varchar(20), 
	org_name_01 varchar(250), 
	org_id_02 varchar(20), 
	org_name_02 varchar(250), 
	org_id_03 varchar(20), 
	org_name_03 varchar(250), 
	org_id_04 varchar(20), 
	org_name_04 varchar(250), 
	org_id_05 varchar(20), 
	org_name_05 varchar(250), 
	org_id_06 varchar(20), 
	org_name_06 varchar(250), 
	org_id_07 varchar(20), 
	org_name_07 varchar(250), 
	org_id_08 varchar(20), 
	org_name_08 varchar(250),
	org_id_09 varchar(20), 
	org_name_09 varchar(250),
	org_id_10 varchar(20), 
	org_name_10 varchar(250),
	org_id_11 varchar(20), 
	org_name_11 varchar(250),
	org_id_12 VARCHAR(20), 
	org_name_12 varchar(250)
	)
/*
SELECT * FROM dbo.GetOrgInOrgWide('00000411','20110725','100')
*/
BEGIN

	DECLARE @result_parent TABLE (
		org_id varchar(20), 
		org_name varchar(250), 
		org_level varchar(20),
		parent_org_id varchar(20),
		parent_org_name varchar(250),
		parent_org_level varchar(20),
		org_id_00 varchar(20), 
		org_name_00 varchar(250), 
		org_id_01 varchar(20), 
		org_name_01 varchar(250), 
		org_id_02 varchar(20), 
		org_name_02 varchar(250), 
		org_id_03 varchar(20), 
		org_name_03 varchar(250), 
		org_id_04 varchar(20), 
		org_name_04 varchar(250), 
		org_id_05 varchar(20), 
		org_name_05 varchar(250), 
		org_id_06 varchar(20), 
		org_name_06 varchar(250), 
		org_id_07 varchar(20), 
		org_name_07 varchar(250), 
		org_id_08 varchar(20), 
		org_name_08 varchar(250),
		org_id_09 varchar(20), 
		org_name_09 varchar(250),
		org_id_10 varchar(20), 
		org_name_10 varchar(250),
		org_id_11 varchar(20), 
		org_name_11 varchar(250),
		org_id_12 VARCHAR(20), 
		org_name_12 varchar(250))

	DECLARE @result_child TABLE (
		org_id varchar(20), 
		org_name varchar(250), 
		org_level varchar(20),
		parent_org_id varchar(20),
		parent_org_name varchar(250),
		parent_org_level varchar(20),
		org_id_00 varchar(20), 
		org_name_00 varchar(250), 
		org_id_01 varchar(20), 
		org_name_01 varchar(250), 
		org_id_02 varchar(20), 
		org_name_02 varchar(250), 
		org_id_03 varchar(20), 
		org_name_03 varchar(250), 
		org_id_04 varchar(20), 
		org_name_04 varchar(250), 
		org_id_05 varchar(20), 
		org_name_05 varchar(250), 
		org_id_06 varchar(20), 
		org_name_06 varchar(250), 
		org_id_07 varchar(20), 
		org_name_07 varchar(250), 
		org_id_08 varchar(20), 
		org_name_08 varchar(250),
		org_id_09 varchar(20), 
		org_name_09 varchar(250),
		org_id_10 varchar(20), 
		org_name_10 varchar(250),
		org_id_11 varchar(20), 
		org_name_11 varchar(250),
		org_id_12 VARCHAR(20), 
		org_name_12 varchar(250))

	DECLARE @_org_name varchar(250), 
		@_org_level varchar(20),
		@_org_id_00 varchar(20), 
		@_org_name_00 varchar(250), 
		@_org_id_01 varchar(20), 
		@_org_name_01 varchar(250), 
		@_org_id_02 varchar(20), 
		@_org_name_02 varchar(250), 
		@_org_id_03 varchar(20), 
		@_org_name_03 varchar(250), 
		@_org_id_04 varchar(20), 
		@_org_name_04 varchar(250), 
		@_org_id_05 varchar(20), 
		@_org_name_05 varchar(250), 
		@_org_id_06 varchar(20), 
		@_org_name_06 varchar(250), 
		@_org_id_07 varchar(20), 
		@_org_name_07 varchar(250), 
		@_org_id_08 varchar(20), 
		@_org_name_08 varchar(250),
		@_org_id_09 varchar(20), 
		@_org_name_09 varchar(250),
		@_org_id_10 varchar(20), 
		@_org_name_10 varchar(250),
		@_org_id_11 varchar(20), 
		@_org_name_11 varchar(250),
		@_org_id_12 VARCHAR(20), 
		@_org_name_12 varchar(250)


	IF (@is_start = 1)
	BEGIN
		INSERT INTO @result_parent
		SELECT org.EndDate, org.ObjectDescription, orgd.OrganizationLevel, '','', '',
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectID ELSE '' END,
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectDescription ELSE '' END,
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			''
			,''
			,''
			,''
			,''
			,''
			,''
			,''
			,''
		FROM phrom0001 org
			INNER JOIN phrom0004 orgd
				ON org.ObjectID = orgd.ObjectID
		WHERE
			org.ObjectClass = 'O'
			AND org.ObjectID = @org_id
			AND org.StartDate <= @ref_date
			AND org.EndDate >= @ref_date
			--AND orgd.landscape = @landscape
			AND orgd.ObjectID = @org_id
			AND orgd.StartDate <= @ref_date
			AND orgd.EndDate >= @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
		IF @_org_level = '09' SELECT @_org_id_09 = @org_id, @_org_name_09 = @_org_name
		IF @_org_level = '10' SELECT @_org_id_10 = @org_id, @_org_name_10 = @_org_name
		IF @_org_level = '11' SELECT @_org_id_11 = @org_id, @_org_name_11 = @_org_name
		IF @_org_level = '12' SELECT @_org_id_12 = @org_id, @_org_name_12 = @_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
		SET @_org_id_09 = @org_id_09
		SET @_org_name_09 = @org_name_09
		SET @_org_id_10 = @org_id_10
		SET @_org_name_10 = @org_name_10
		SET @_org_id_11 = @org_id_11
		SET @_org_name_11 = @org_name_11
		SET @_org_id_12 = @org_id_12
		SET @_org_name_12 = @org_name_12
	END
		
	DECLARE 
		@__org_id varchar(20), 
		@__org_name varchar(250), 
		@__org_level varchar(20),
		@__parent_org_id varchar(20), 
		@__parent_org_name varchar(250), 
		@__parent_org_level varchar(20),
		@__org_id_00 varchar(20), 
		@__org_name_00 varchar(250), 
		@__org_id_01 varchar(20), 
		@__org_name_01 varchar(250), 
		@__org_id_02 varchar(20), 
		@__org_name_02 varchar(250), 
		@__org_id_03 varchar(20), 
		@__org_name_03 varchar(250), 
		@__org_id_04 varchar(20), 
		@__org_name_04 varchar(250), 
		@__org_id_05 varchar(20), 
		@__org_name_05 varchar(250), 
		@__org_id_06 varchar(20), 
		@__org_name_06 varchar(250), 
		@__org_id_07 varchar(20), 
		@__org_name_07 varchar(250), 
		@__org_id_08 varchar(20), 
		@__org_name_08 varchar(250),
		@__org_id_09 varchar(20), 
		@__org_name_09 varchar(250),
		@__org_id_10 varchar(20), 
		@__org_name_10 varchar(250),
		@__org_id_11 varchar(20), 
		@__org_name_11 varchar(250),
		@__org_id_12 VARCHAR(20), 
		@__org_name_12 varchar(250)
	
	DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
	FOR 
		SELECT org.ObjectID, org.[ObjectDescription], orgd.OrganizationLevel,
			@org_id,
			@_org_name,
			@_org_level,
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectID ELSE @_org_id_00 END,
			CASE WHEN orgd.OrganizationLevel = '00' THEN org.ObjectDescription ELSE @_org_name_00 END,
			CASE WHEN orgd.OrganizationLevel = '01' THEN org.ObjectID ELSE @_org_id_01 END,
			CASE WHEN orgd.OrganizationLevel = '01' THEN org.ObjectDescription ELSE @_org_name_01 END,
			CASE WHEN orgd.OrganizationLevel = '02' THEN org.ObjectID ELSE @_org_id_02 END,
			CASE WHEN orgd.OrganizationLevel = '02' THEN org.ObjectDescription ELSE @_org_name_02 END,
			CASE WHEN orgd.OrganizationLevel = '03' THEN org.ObjectID ELSE @_org_id_03 END,
			CASE WHEN orgd.OrganizationLevel = '03' THEN org.ObjectDescription ELSE @_org_name_03 END,
			CASE WHEN orgd.OrganizationLevel = '04' THEN org.ObjectID ELSE @_org_id_04 END,
			CASE WHEN orgd.OrganizationLevel = '04' THEN org.ObjectDescription ELSE @_org_name_04 END,
			CASE WHEN orgd.OrganizationLevel = '05' THEN org.ObjectID ELSE @_org_id_05 END,
			CASE WHEN orgd.OrganizationLevel = '05' THEN org.ObjectDescription ELSE @_org_name_05 END,
			CASE WHEN orgd.OrganizationLevel = '06' THEN org.ObjectID ELSE @_org_id_06 END,
			CASE WHEN orgd.OrganizationLevel = '06' THEN org.ObjectDescription ELSE @_org_name_06 END,
			CASE WHEN orgd.OrganizationLevel = '07' THEN org.ObjectID ELSE @_org_id_07 END,
			CASE WHEN orgd.OrganizationLevel = '07' THEN org.ObjectDescription ELSE @_org_name_07 END,
			CASE WHEN orgd.OrganizationLevel = '08' THEN org.ObjectID ELSE @_org_id_08 END,
			CASE WHEN orgd.OrganizationLevel = '08' THEN org.ObjectDescription ELSE @_org_name_08 END,
			CASE WHEN orgd.OrganizationLevel = '09' THEN org.ObjectID ELSE @_org_id_09 END,
			CASE WHEN orgd.OrganizationLevel = '09' THEN org.ObjectDescription ELSE @_org_name_09 END,
			CASE WHEN orgd.OrganizationLevel = '10' THEN org.ObjectID ELSE @_org_id_10 END,
			CASE WHEN orgd.OrganizationLevel = '10' THEN org.ObjectDescription ELSE @_org_name_10 END,
			CASE WHEN orgd.OrganizationLevel = '11' THEN org.ObjectID ELSE @_org_id_11 END,
			CASE WHEN orgd.OrganizationLevel = '11' THEN org.ObjectDescription ELSE @_org_name_11 END,
			CASE WHEN orgd.OrganizationLevel = '12' THEN org.ObjectID ELSE @_org_id_12 END,
			CASE WHEN orgd.OrganizationLevel = '12' THEN org.ObjectDescription ELSE @_org_name_12 END
		FROM phrom0002 rel
			INNER JOIN phrom0001 org
				ON rel.RelationShipObject = org.ObjectID
			INNER JOIN phrom0004 orgd
				ON org.ObjectID = orgd.ObjectID
		WHERE
			rel.ObjectClass = 'O'
			AND rel.ObjectID = @org_id
			AND rel.RelationShipType = '001'
			AND rel.RelationShipDirectory = 'A'
			AND rel.StartDate <= @ref_date
			AND rel.EndDate >= @ref_date
			AND rel.RelationShipClass = 'O'
			
			--AND org.landscape = @landscape
			AND org.ObjectClass = 'O'
			AND org.StartDate <= @ref_date
			AND org.EndDate >= @ref_date
			
			--AND orgd.landscape = @landscape
			AND orgd.StartDate <= @ref_date
			AND orgd.EndDate >= @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,
			@__org_id_09, @__org_name_09,
			@__org_id_10, @__org_name_10,
			@__org_id_11, @__org_name_11,
			@__org_id_12, @__org_name_12
		
		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,
			@__org_id_09, @__org_name_09,
			@__org_id_10, @__org_name_10,
			@__org_id_11, @__org_name_12,
			@__org_id_12, @__org_name_12
			)
			
			INSERT INTO @result_child
			SELECT * FROM CBKT_GetOrgInOrgWideDetail(@__org_id, @ref_date, 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,
				@__org_id_09, @__org_name_09,
				@__org_id_10, @__org_name_10,
				@__org_id_11, @__org_name_11,
				@__org_id_12, @__org_name_12
			)
		END
	END
	
	CLOSE cur
	DEALLOCATE cur
	
	INSERT INTO @result
	SELECT * FROM @result_child

	RETURN
END
