SQL Scripts for Microsoft Dynamics GP: SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script can be used to create a Management Reporter reporting tree of the division segment, segment 1, of the chart of accounts as well as including user-defined fields 3 and 4 from the accounts.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE VIEW [dbo].[uv_AZRCRV_BuildSchemeTreeWithUDF3and4] AS
SELECT
	DISTINCT ACTNUMBR_1 AS 'Company'
	,'TWO-Curr' AS 'A Company'
	,GL4.DSCRIPTN as 'B Unit Name'
	,ACTNUMBR_1 AS 'C Unit Description'
	,'+Company = [' + RTRIM(ACTNUMBR_1) + '], Division = [' + RTRIM(ACTNUMBR_1) + '], User-Defined 3 = [' + RTRIM(USRDEFS1) + '], User-Defined 4 = [' + RTRIM(USRDEFS2) + ']' AS 'D Dimensions'
	,USRDEFS1 AS UDF3
	,USRDEFS2 AS UDF4
FROM
	GL00100 AS GL1
INNER JOIN
	GL40200 AS GL4
		ON GL4.SGMTNUMB = 2 AND CAST(GL4.SGMNTID AS VARCHAR(100)) = CAST(GL1.ACTNUMBR_2 AS VARCHAR(100))
GO