ETL_DWH_LOCOSOFT/schema/GC/views/VW_Loc_Belege.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [dbo].[VW_Loc_Belege] AS
SELECT 
    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 Acct_Nr,
    LOC_Belege.Text,
	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 '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.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 'Großkunde'
        WHEN LEFT(LOC_Belege.Absatzkanal, 1) = '4' THEN 'Sonderabnehmer'
        WHEN LEFT(LOC_Belege.Absatzkanal, 1) = '5' THEN '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'
        ELSE NULL
    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 
    GC.locosoft.LOC_Belege LOC_Belege
    LEFT OUTER JOIN GC.locosoft.Kontenrahmen_GC_Struct_SKR Kontenrahmen_GC_Struct_SKR
        ON LOC_Belege.[Acct Nr] = Kontenrahmen_GC_Struct_SKR.[Konto Nr]
    LEFT OUTER JOIN GC.[data].GC_Department GC_Department
        ON LOC_Belege.Rechtseinheit = GC_Department.Hauptbetrieb 
           AND LOC_Belege.Betrieb = GC_Department.Standort
    LEFT OUTER JOIN GC.[data].GC_Marken GC_Marken
        ON LOC_Belege.Marke = 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