ETL_DWH_LOCOSOFT/schema/GC/views/transform.Aftersales_Rechnungen_neu.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[Aftersales_Rechnungen_neu] AS
SELECT [GC_Department].[Hauptbetrieb_ID] AS [Hauptbetrieb_ID]
     , [GC_Department].[Hauptbetrieb_Name] AS [Hauptbetrieb_Name]
     , [GC_Department].[Standort_ID] AS [Standort_ID]
     , [GC_Department].[Standort_Name] AS [Standort_Name]
     , 'Serviceberater' AS [Employee_Function]
     , [Aftersales_Rechnungen_neu].[Serviceberater] AS [Serviceberater]
     , (([Aftersales_Rechnungen_neu]."Order Number" + ' - ') + cast(Aftersales_Rechnungen_neu."Invoice Date" AS VARCHAR (10))) AS [Order_Number]
     , '' AS [Invoice_Desc_100]
     , CASE
           WHEN [GC_Marken].[Fabrikat] IS NULL THEN 'Fremd'
           ELSE [GC_Marken].[Fabrikat]
       END AS [Fabrikat]
     , CASE
           WHEN CASE
                    WHEN [GC_Marken].[Fabrikat] IS NULL THEN 'Fremd'
                    ELSE [GC_Marken].[Fabrikat]
                END = 'Fremd' THEN 9
           ELSE [GC_Marken].[Order_By]
       END AS [Fabrikat_Order_By]
     , [Aftersales_Rechnungen_neu].[Model] AS [Model]
     , [Aftersales_Rechnungen_neu].[Fahrzeug] AS [Fahrzeug]
     , [Aftersales_Rechnungen_neu].[Kostenstelle] AS [Kostenstelle]
     , [Aftersales_Rechnungen_neu].[Marke] AS [Marke]
     , [Aftersales_Rechnungen_neu].[Umsatzart] AS [Umsatzart]
     , [Aftersales_Rechnungen_neu].[Auftragsart] AS [Auftragsart]
     , (([Kontenrahmen_GC_Struct_SKR]."Konto Nr" + ' - ') + Kontenrahmen_GC_Struct_SKR."Konto Bezeichnung") AS [Auftragsposition]
     , [Aftersales_Rechnungen_neu].[Kundenart] AS [Kundenart]
     , [Aftersales_Rechnungen_neu].[Kunde] AS [Kunde]
     , (([Aftersales_Rechnungen_neu]."Order Number" + ' - ') + cast(Aftersales_Rechnungen_neu."Invoice Date" AS VARCHAR (10))) AS [Order_Desc_30]
     , '' AS [Invoice_Desc_30]
     , [GC_Produktbuchungsgruppen].[Zuordnung] AS [c22]
     , [Aftersales_Rechnungen_neu].[Kostenstelle] AS [Produktbuchungsgruppe]
     , '' AS [FZG_Altersstaffel]
     , '' AS [Repair_Group_Desc]
     , '' AS [DB1____EK]
     , '' AS [Rechnung_Gutschrift]
     , '' AS [Parts_Focus_Group]
     , '' AS [Parts_Make_Desc]
     , '' AS [Parts_Group_Desc]
     , left([Aftersales_Rechnungen_neu]."Free Form Accounting Text", 1) AS [PLZ_1_Stelle]
     , left([Aftersales_Rechnungen_neu]."Free Form Accounting Text", 2) AS [PLZ_2_Stelle]
     , left([Aftersales_Rechnungen_neu]."Free Form Accounting Text", 3) AS [PLZ_3_Stelle]
     , left([Aftersales_Rechnungen_neu]."Free Form Accounting Text", 4) AS [PLZ_4_Stelle]
     , [Aftersales_Rechnungen_neu]."Free Form Accounting Text" AS [PLZ]
     , 'Serviceberater' AS [Zuordnung_Funktion]
     , CASE
           WHEN (datediff(day,Aftersales_Rechnungen_neu."Invoice Date",getdate()) < 100) THEN (([Aftersales_Rechnungen_neu]."Order Number" + ' - ') + cast(Aftersales_Rechnungen_neu."Invoice Date" AS VARCHAR (10)))
           ELSE N'älter 100 Tage'
       END AS [Order_Desc_100]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'Umsatz Lohn' THEN ([Aftersales_Rechnungen_neu].[Betrag] * (-1))
           ELSE 0
       END AS [Lohn_Umsatz]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'Umsatz Teile' THEN ([Aftersales_Rechnungen_neu].[Betrag] * (-1))
           ELSE 0
       END AS [Teile_Umsatz]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'Einsatz Teile' THEN [Aftersales_Rechnungen_neu].[Betrag]
           ELSE 0
       END AS [EW_Teile]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'Umsatz Sonst.' THEN ([Aftersales_Rechnungen_neu].[Betrag] * (-1))
           ELSE 0
       END AS [Sonst_]
     , 0 AS [TÜV]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'Umsatz FL' THEN ([Aftersales_Rechnungen_neu].[Betrag] * (-1))
           ELSE 0
       END AS [Fremdl_]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'Umsatz MW' THEN ([Aftersales_Rechnungen_neu].[Betrag] * (-1))
           ELSE 0
       END AS [Mietw_]
     , 0 AS [Rädereinl_]
     , 0 AS [verk__Std_]
     , 0 AS [ben__Std_]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'NL Lohn' THEN ([Aftersales_Rechnungen_neu].[Betrag] * (-1))
           ELSE 0
       END AS [NL_Lohn]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'NL Teile' THEN ([Aftersales_Rechnungen_neu].[Betrag] * (-1))
           ELSE 0
       END AS [NL_Teile]
     , 0 AS [NL_Sonst_]
     , [Aftersales_Rechnungen_neu].[DG] AS [DG]
     , 0 AS [NL_Sonst__]
     , 0 AS [Anz__AT]
     , '' AS [Anwesenheit_Mech_Karo_Lack]
     , '' AS [Anwesenheit_Meister]
     , [Aftersales_Rechnungen_neu]."Invoice Date" AS [Invoice_Date]
     , (([Aftersales_Rechnungen_neu]."Order Number" + ' - ') + cast(Aftersales_Rechnungen_neu."Invoice Date" AS VARCHAR (10))) AS [Rechnungsausgang]
     , CASE
           WHEN [Kontenrahmen_GC_Struct_SKR]."Konto 2" = 'Einsatz FL' THEN [Aftersales_Rechnungen_neu].[Betrag]
           ELSE 0
       END AS [EW_Fremdl_]
     , [Aftersales_Rechnungen_neu].[Serviceberater_Rg_Steller] AS [Serviceberater_Rg_Steller]
FROM locosoft.[Aftersales_Rechnungen_neu] [Aftersales_Rechnungen_neu]
         LEFT OUTER JOIN
     [locosoft].[Kontenrahmen_GC_Struct_SKR] [Kontenrahmen_GC_Struct_SKR] ON ([Aftersales_Rechnungen_neu]."Contra Account Text" = [Kontenrahmen_GC_Struct_SKR]."Konto Nr")
         LEFT OUTER JOIN
     "data".[GC_Department] [GC_Department] ON (([Aftersales_Rechnungen_neu].[Hauptbetrieb] = [GC_Department].[Hauptbetrieb]) AND ([Aftersales_Rechnungen_neu].[Standort] = [GC_Department].[Standort]))
         LEFT OUTER JOIN
     "data".[GC_Marken] [GC_Marken] ON ([Aftersales_Rechnungen_neu].[Fabrikat] = [GC_Marken].[Description])
         LEFT OUTER JOIN
     "data".[GC_Produktbuchungsgruppen] [GC_Produktbuchungsgruppen] ON ([Aftersales_Rechnungen_neu].[Kostenstelle] = [GC_Produktbuchungsgruppen].[Produktbuchungsgruppe])
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO