GetOrgInOrgWideDetail_20250620.sql - Bank Mega - Redmine

Bug #3671 » GetOrgInOrgWideDetail_20250620.sql

Tri Rizqiaty, 06/20/2025 04:03 PM

    
      ??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

« Previous
1
2
3
4
Next »

(4-4/4)

Project

General

Profile

CLIENT SUPPORT » Bank Mega

Bug #3671 » GetOrgInOrgWideDetail_20250620.sql