SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform].[LOC_BELEGE_DETAIL] AS
SELECT [LOC_Belege].[Rechtseinheit] AS [Hauptbetrieb_imr]
     , [LOC_Belege].[Betrieb] AS [Standort_imr]
     , [GC_Department].[Hauptbetrieb_ID] AS [Rechtseinheit_ID]
     , [GC_Department].[Hauptbetrieb_Name] AS [Rechtseinheit_Name]
     , [GC_Department].[Standort_ID] AS [Betrieb_ID]
     , [GC_Department].[Standort_Name] AS [Betrieb_Name]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene1]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene2]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene3]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene4]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene5]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene6]
     , left([Kontenrahmen_GC_Struct_SKR].[Konto Nr], 4) + ' - ' + [Kontenrahmen_GC_Struct_SKR].[Konto Bezeichnung] AS [Konto]
     , [LOC_Belege].[Acct Nr] AS [Konto_Nr_imr]
     , [LOC_Belege].[Acct Nr] AS [Acct_Nr]
     , [LOC_Belege].[Text]
     , convert(varchar, [LOC_Belege].[Document Number]) + ' - ' + [LOC_Belege].[Posting Text] + ' - ' + [LOC_Belege].[Vehicle Reference] + ' - ' + rtrim([LOC_Belege].[Employee Number]) AS [Beleg]
     , CASE
           WHEN left([LOC_Belege].[KST], 1) = '1' THEN 'Neuwagen'
           WHEN left([LOC_Belege].[KST], 1) = '2' THEN 'Gebrauchtwagen'
           WHEN left([LOC_Belege].[KST], 1) = '3' THEN N'Teile & Zubehör'
           WHEN left([LOC_Belege].[KST], 1) = '4' THEN 'Service'
           WHEN left([LOC_Belege].[KST], 1) = '5' THEN 'Weitere Bereiche'
           WHEN left([LOC_Belege].[KST], 1) = '0' THEN 'ohne Herkunft'
           ELSE 'Verwaltung'
       END AS [Kostenstelle]
     , [LOC_Belege].[KST] AS [KST_mit_Bez]
     , left([LOC_Belege].[KST], 2) AS [KST]
     , left([LOC_Belege].[Marke], 1) AS [Markencode]
     , [LOC_Belege].[Vehicle Reference] AS [Fahrgestellnummer]
     , left([Kontenrahmen_GC_Struct_SKR].[Konto Nr], 4) + ' - ' + [Kontenrahmen_GC_Struct_SKR].[Konto Bezeichnung] AS [Konto_Buchung]
     , [LOC_Belege].[Skr51 Make] AS [Marke_FIBU_imr]
     , [LOC_Belege].[Marke] AS [Marke_mit_Bez]
     , left([LOC_Belege].[Marke], 1) AS [Marke]
     , CASE
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '1' THEN 'Privater Endkunde'
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '2' THEN 'Gewerbekunde'
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '3' THEN N'Großkunde'
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '4' THEN 'Sonderabnehmer'
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '5' THEN N'Wiederverkäufer'
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '6' THEN 'Wirtschaftsraum'
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '9' THEN 'Sonstige'
           WHEN left([LOC_Belege].[Absatzkanal], 1) = '0' THEN 'ohne Absatzkanal'
       END AS [Ebene21]
     , [LOC_Belege].[Absatzkanal] AS [Absatzkanal_mit_Bez]
     , left([LOC_Belege].[Absatzkanal], 2) AS [Absatzkanal]
     , [LOC_Belege].[Free Form Document Text] AS [Ebene31]
     , [LOC_Belege].[Kostenträger] AS [Kostenträger_mit_Bez]
     , [LOC_Belege].[Skr51 Cost Unit] AS [Kostenträger_FIBU]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene71]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene72]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene73]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene74]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene75]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene76]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene77]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene81]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene82]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene83]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene84]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene85]
     , [Kontenrahmen_GC_Struct_SKR].[Ebene86]
     , [LOC_Belege].[GuV_Bilanz]
     , [LOC_Belege].[Susa]
     , [LOC_Belege].[Mandant]
     , [LOC_Belege].[Betrag] AS [Ist]
     , [LOC_Belege].[Menge] AS [Menge_]
     , [GC_Department].[Gruppe_ID]
     , [GC_Department].[Gruppe_Name]
     , [LOC_Belege].[Bookkeep Date] AS [Invoice_Date]
     , CASE
           WHEN [GC_Marken].[Fabrikat] IS NULL THEN 'Fremd'
           ELSE [GC_Marken].[Fabrikat]
       END AS [Fabrikat]
     , CASE
           WHEN isnull([GC_Marken].[Fabrikat], 'Fremd') = 'Fremd' THEN 9
           ELSE [GC_Marken].[Order_By]
       END AS [Fabrikat_Order_By]
     , convert(date, [LOC_Belege].[Invoice Date]) AS [Datum]
     , convert(varchar(20), format([LOC_Belege].[Invoice Date], 'yyyy/MM')) AS [Jahr/Monat]
     , cast(getdate() AS date) AS [Aktueller Tag]
     , convert(date, (dateadd(MONTH, datediff(MONTH, 0, [LOC_Belege].[Invoice Date]), 0))) AS [Monatserster]
     , eomonth([LOC_Belege].[Invoice Date]) AS [Monatsletzter]
     , convert(varchar(100), '("' +
                             convert(varchar, dateadd(MONTH, datediff(MONTH, 0, [LOC_Belege].[Invoice Date]), 0), 23) +
                             '","' +
                             convert(varchar, eomonth([LOC_Belege].[Invoice Date]), 23) +
                             '")') AS [MUN_DATE]
     , convert(varchar(50), convert(varchar, dateadd(MONTH, datediff(MONTH, 0, [LOC_Belege].[Invoice Date]), 0), 112) +
                            '-' +
                            convert(varchar, eomonth([LOC_Belege].[Invoice Date]), 112)) AS [Mitgliedsname]
     , datefromparts(year([LOC_Belege].[Invoice Date]), 1, 1) AS [Beginn Geschäftsjahr]
     , convert(varchar(20), format([LOC_Belege].[Invoice Date], 'MMM', 'de') + './' + format([LOC_Belege].[Invoice Date], 'yyyy', 'de')) AS [Monat_Jahr]
     , convert(varchar(20), format([LOC_Belege].[Invoice Date], 'yyyy')) AS [Jahr]
     , convert(varchar(20), format([LOC_Belege].[Invoice Date], 'MM')) AS [Monat]
     , format([LOC_Belege].[Invoice Date], 'MMM', 'de-DE') AS [Monat_kurz]

FROM [transform_imr].[LOC_Belege] [LOC_Belege]
         LEFT OUTER JOIN [data].[Kontenrahmen_GC_Struct_SKR] [Kontenrahmen_GC_Struct_SKR] ON [LOC_Belege].[Acct Nr] = [Kontenrahmen_GC_Struct_SKR].[Konto Nr]
         LEFT OUTER JOIN [data].[GC_Department] [GC_Department] ON [LOC_Belege].[Rechtseinheit] = [GC_Department].[Hauptbetrieb]
    AND [LOC_Belege].[Betrieb] = [GC_Department].[Standort]
         LEFT OUTER JOIN [data].[GC_Marken] [GC_Marken] ON [LOC_Belege].[Skr51 Make] = [GC_Marken].[Marke_FIBU]
    AND [LOC_Belege].[Rechtseinheit] = [GC_Marken].[Client_DB]
--WHERE [GC_Department].[Hauptbetrieb_ID] = '1'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
