
A_BASE_DETAIL (VIEW)
VIEW BIMSALIAS.A_BASE_DETAIL
Snippet options
Download: Download snippet as a_base_detail-view.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!
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)');
Create a free my code stock.com account now.
my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!
Find out more and register nowYou can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.