DROP VIEW BIMSALIAS.A_BASE_DETAIL;

CREATE VIEW BIMSALIAS.A_BASE_DETAIL (
	BASE_DETAIL FOR COLUMN BASEDETAIL,
	ACCOUNT_DESCRIPTION FOR COLUMN AMDES1,
	BASE_DETAIL_FULL FOR COLUMN BASEDTLFUL,
	ACCOUNT_ABBV FOR COLUMN AMABR1,
	ACCOUNT_TYPE FOR COLUMN AMTYPE,
	BUDGETED_ACCOUNT FOR COLUMN AMBDAC,
	CLASS1_CODE FOR COLUMN AMCLS1,
	CLASS1_DESCRIPTION FOR COLUMN AMCLS1DESC,
	CLASS1_FULL FOR COLUMN AMCLS1FULL,
	CLASS2_CODE FOR COLUMN AMCLS2,
	CLASS2_DESCRIPTION FOR COLUMN AMCLS2DESC,
	CLASS2_FULL FOR COLUMN AMCLS2FULL,
	USR3_MISSING FOR COLUMN USR3ERROR,
	USR3_CODE, 
	PERSONNEL_CLASS FOR COLUMN PERS_CLASS
	)
AS
(
		SELECT CAST(STRIP(G.AMACT1) CONCAT '.' CONCAT STRIP(G.AMACT2) AS CHAR(7)) AS BASE_DETAIL,
			CAST(COALESCE(FMT.ACCOUNT_DESCRIPTION, TRIM(G.AMDES1) CONCAT '*') AS CHAR(50)) AS ACCOUNT_DESCRIPTION,
			CAST(STRIP(G.AMACT1) CONCAT '.' CONCAT STRIP(G.AMACT2) CONCAT ' - ' CONCAT COALESCE(FMT.ACCOUNT_DESCRIPTION, TRIM(G.AMDES1) CONCAT '*') AS CHAR(60)) AS BASE_DETAIL_FULL,
			CAST(COALESCE(FMT.ACCOUNT_ABBV, TRIM(G.AMABR1)) AS CHAR(10)) AS ACCOUNT_ABBV,
			G.AMTYPE AS ACCOUNT_TYPE,
			G.AMBDAC AS BUDGETED_ACCOUNT,
			G.AMCLS1 AS CLASS1_CODE,
			C1.CLASS1_DESCRIPTION,
			CAST(G.AMCLS1 CONCAT ' - ' CONCAT C1.CLASS1_DESCRIPTION AS CHAR(60)) AS CLASS1_FULL,
CAST(COALESCE(G.AMCLS2,G.AMCLS1) AS CHAR(4)) AS CLASS2_CODE,
			CAST(CASE 
					WHEN TRIM(G.AMCLS2) = ''
						THEN ('*' CONCAT TRIM(C1.CLASS1_DESCRIPTION) CONCAT ' - Other')
					ELSE C2.CLASS2_DESCRIPTION
					END AS CHAR(50)) AS CLASS2_DESCRIPTION,
			CAST(CASE 
					WHEN TRIM(G.AMCLS2) = ''
						THEN (G.AMCLS1 CONCAT ' - *' CONCAT TRIM(C1.CLASS1_DESCRIPTION) CONCAT ' Other')
					ELSE (G.AMCLS2 CONCAT ' - ' CONCAT C2.CLASS2_DESCRIPTION)
					END AS CHAR(60)) AS CLASS2_FULL,
			CAST(CASE 
					WHEN FMT.ACCOUNT_DESCRIPTION IS NULL
						THEN 'Y'
					ELSE 'N'
					END AS CHAR(1)) AS USR3_MISSING,
	CAST(COALESCE(FMT.USR3_CODE, 'NULL') AS CHAR(4)) AS USR3_CODE,
			CASE 
				WHEN G.AMCLS2 IN (
						'5100',
						'5109',
						'5108',
						'5120',
						'5129',
						'5101'
						)
					THEN 0
				ELSE 1
				END AS PERSONNEL_CLASS
			
		FROM NWSFMFLS.GLACCT AS G
		LEFT OUTER JOIN (
			SELECT F.TECOD# AS USR3_CODE,
				SUBSTRING(F.TEALT, 1, 7) AS BASE_DETAIL,
				F.TEDESC AS ACCOUNT_DESCRIPTION,
				F.TEABBV AS ACCOUNT_ABBV,
				F.TEALT,
				F.TEINAC
			FROM NWSFMFLS.FMTABE F
			WHERE TEMST# = 51
			) AS FMT ON CAST(STRIP(G.AMACT1) CONCAT '.' CONCAT STRIP(G.AMACT2) AS CHAR(7)) = FMT.BASE_DETAIL
		LEFT OUTER JOIN (
			SELECT F.TECOD#,
				F.TEDESC AS CLASS1_DESCRIPTION,
				F.TEABBV
			FROM NWSFMFLS.FMTABE AS F
			WHERE F.TEMST# = 2
				AND F.TEINAC = 'Y'
			) AS C1 ON G.AMCLS1 = C1.TECOD#
		LEFT OUTER JOIN (
			SELECT F.TECOD#,
				F.TEDESC AS CLASS2_DESCRIPTION,
				F.TEABBV
			FROM NWSFMFLS.FMTABE AS F
			WHERE F.TEMST# = 3
				AND F.TEINAC = 'Y'
			) AS C2 ON G.AMCLS2 = C2.TECOD#
		WHERE G.AMTYPE IN (
				'EX',
				'RE'
				)
		);

/*--------------------------------------------------------------*/
GRANT ALL PRIVILEGES
	ON BIMSALIAS.A_BASE_DETAIL
	TO PUBLIC;

RENAME TABLE BIMSALIAS.A_BASE_DETAIL TO SYSTEM NAME BASEDETAIL;

/*--------------------------------------------------------------*/
LABEL ON TABLE BIMSALIAS.A_BASE_DETAIL IS '- BASE DETAIL ACCOUNT MASTER';


LABEL ON COLUMN BIMSALIAS.A_BASE_DETAIL(BASE_DETAIL TEXT IS 'BASE DETAIL', ACCOUNT_DESCRIPTION TEXT IS 'ACCOUNT DESCRIPTION', BASE_DETAIL_FULL TEXT IS 'BASE DETAIL FULL (DESCRIPTION)', ACCOUNT_ABBV TEXT IS 'ACCOUNT ABBREVIATION', ACCOUNT_TYPE TEXT IS 'ACCOUNT TYPE', BUDGETED_ACCOUNT TEXT IS 'BUDGETED ACCT (Y/N)', CLASS1_CODE TEXT IS 'ACCOUNT CLASS 1', CLASS1_DESCRIPTION TEXT IS 'ACCOUNT CLASS 1 DESCRIPTION', CLASS1_FULL TEXT IS 'ACCOUNT CLASS 1 FULL', CLASS2_CODE TEXT IS 'ACCOUNT CLASS 2', CLASS2_DESCRIPTION TEXT IS 'ACCOUNT CLASS 2 DESCRIPTION', CLASS2_FULL TEXT IS 'ACCOUNT CLASS 2 FULL', 
USR3_MISSING TEXT IS 'USR3 CODE MISSING DESCRIPTIONS',
PERSONNEL_CLASS TEXT IS 'CLASS 2 PERSONNEL (PAYROLL SYSTEM)');