C11/System/ARI/SQL/schema/GC_DWH_ARI/views/Kontenrahmen.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [dbo].[Kontenrahmen]
AS
SELECT        Konto_Nr + Konto_Bezeichnung AS Konto, Konto_Nr, Konto_Bezeichnung, Konto_Art, Kostenstelle AS [KST-Nr], STK, Konto_1, Konto_2, Konto_3, Konto_4, Konto_5, Ebene1, Ebene2, Ebene3, Ebene4, Ebene5, Ebene6, Ebene7, Ebene8, 
                         Ebene9, Ebene10, Ebene11, Ebene12, Ebene13, Ebene14, Ebene15, Ebene16, Ebene17, Ebene18, Ebene19, Ebene20, Ebene21, Ebene22, Ebene23, Ebene24, Ebene25, Ebene26, Ebene27, Ebene28, Ebene29, Ebene30, 
                         Ebene31, Ebene32, Ebene33, Ebene34, Ebene35, Ebene36, Ebene37, Ebene38, Ebene39, Ebene40, Ebene41, Ebene42, Ebene43, Ebene44, Ebene45, Ebene46, Ebene47, Ebene48, Ebene49, Ebene50, Ebene51, Ebene52, 
                         Ebene53, Ebene54, Ebene55, Ebene56, Ebene57, Ebene58, Ebene59, Ebene60

, CASE
           WHEN [Ebene1] = 'XX' THEN 'XX'
           WHEN [Ebene1] = N'Umsatzerlöse'
               THEN '1_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN [Ebene1] = 'Materialaufwand'
               THEN '2_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN [Ebene1] = 'Kosten'
               THEN '3_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN left([Ebene1], 8) = 'Indirekt'
               THEN '4_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN left([Ebene1], 6) = 'Finanz'
               THEN '5_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           ELSE 'X?'
    END AS [Sortierung]

 , CASE 
	  WHEN ([Kostenstelle] = '0') THEN ('0 - VW') 
	  WHEN ([Kostenstelle] = '1') THEN ('1 - NA')
	  WHEN ([Kostenstelle] = '2') THEN ('2 - GA') 
	  WHEN ([Kostenstelle] = '3') THEN ('3 - T&Z')
	  WHEN ([Kostenstelle] = '4') THEN ('4 - SC')
	  WHEN ([Kostenstelle] = '5') THEN ('5 - sonst. Abt.')
	  ELSE [Kostenstelle] END AS Kostenstelle

FROM            GC_CONFIG.ari.Kontenrahmen AS Kontenrahmen_1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO