C11/System/ARI/SQL/schema/GC_ETL_ARI/views/load.Aftersales.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[Aftersales] AS

SELECT --TOP(10)
    convert(varchar(20), [Client_DB]) AS [Client_DB]
     , convert(varchar(20), [Department Code]) AS [Department Code]
     , convert(varchar(100), [Auftrags-Nr.]) AS [Auftrags-Nr.]
     , convert(varchar(20), [Gen Prod Posting Group]) AS [Gen Prod Posting Group]
     , convert(varchar(100), [Benutzer Abr.]) AS [Benutzer Abr.]
     , convert(varchar(100), [Serviceberater]) AS [Serviceberater]
     , convert(varchar(130), [Monteur]) AS [Monteur]
     , convert(varchar(20), [Umsatzart]) AS [Umsatzart]
     , convert(varchar(50), [Model]) AS [Model]
     , convert(varchar(100), [Fahrzeug]) AS [Fahrzeug]
     , convert(varchar(20), [Marke]) AS [Marke]
     , convert(varchar(20), [Auftragsart]) AS [Auftragsart]
     , convert(varchar(50), [Kundenart]) AS [Kundenart]
     , convert(varchar(200), [Kunde]) AS [Kunde]
     , convert(datetime, [Invoice Date]) AS [Invoice Date]
     , convert(decimal(28, 8), [ben. AW]) AS [ben. AW]
     , convert(decimal(28, 8), [verk. AW]) AS [verk. AW]
     , convert(varchar(200), [Auftragsposition]) AS [Auftragsposition]
     , convert(varchar(20), [SB_Gruppe]) AS [SB_Gruppe]
     , convert(decimal(28, 8), [Rabatt Lohn]) AS [Rabatt Lohn]
     , convert(decimal(28, 8), [Rabatt Teile]) AS [Rabatt Teile]
     , convert(varchar(20), [Rg-Nr.]) AS [Rg-Nr.]
     , convert(varchar(200), [RG/Auftrag/Kunde]) AS [RG/Auftrag/Kunde]
     , convert(varchar(200), [RG/SB/Kunde/Datum]) AS [RG/SB/Kunde/Datum]
     , convert(varchar(20), [Labor No]) AS [Labor No]
     , convert(varchar(200), [Artikel / AW-Nr.]) AS [Artikel / AW-Nr.]
     , convert(varchar(200), [Kunde_Verkaufskunde]) AS [Kunde_Verkaufskunde]
     , convert(varchar(20), [Make Code]) AS [Make Code]
     , convert(varchar(20), [Location Code]) AS [Location Code]
     , convert(decimal(28, 8), [Amount]) AS [Amount]
     , convert(decimal(28, 8), [Line Discount Amount]) AS [Line Discount Amount]
     , convert(decimal(28, 8), [Quantity]) AS [Quantity]
     , convert(decimal(28, 8), [Unit Cost (lcy)]) AS [Unit Cost (lcy)]
     , convert(varchar(20), [Source Table]) AS [Source Table]
     , convert(varchar(100), [PGRP-Bezeichnung]) AS [PGRP-Bezeichnung]
     , convert(varchar(20), [Zuordnung]) AS [Zuordnung]
     , convert(varchar(20), [Fabrikat]) AS [Fabrikat]
     , convert(int, [Fabrikat_Sortierung]) AS [Fabrikat_Sortierung]
     , convert(varchar(20), [Hauptbetrieb_ID]) AS [Hauptbetrieb_ID]
     , convert(varchar(50), [Hauptbetrieb_Name]) AS [Hauptbetrieb_Name]
     , convert(varchar(20), [Standort_ID]) AS [Standort_ID]
     , convert(varchar(50), [Standort_Name]) AS [Standort_Name]
     , convert(varchar(50), [Fokus-Gruppe]) AS [Fokus-Gruppe]
     , convert(varchar(50), [Fokus-Untergruppe]) AS [Fokus-Untergruppe]
     , convert(decimal(28, 8), [Lohn]) AS [Lohn]
     , convert(decimal(28, 8), [Teile]) AS [Teile]
     , convert(decimal(28, 8), [Sonst.]) AS [Sonst.]
     , convert(decimal(28, 8), [TÜV]) AS [TÜV]
     , convert(decimal(28, 8), [Fremdl.]) AS [Fremdl.]
     , convert(decimal(28, 8), [Mietw.]) AS [Mietw.]
     , convert(decimal(28, 8), [NL Lohn]) AS [NL Lohn]
     , convert(decimal(28, 8), [NL Teile]) AS [NL Teile]
     , convert(decimal(28, 8), [EW Teile]) AS [EW Teile]
     , convert(decimal(28, 8), [EW Fremdl.]) AS [EW Fremdl.]
     , convert(decimal(28, 8), [DG]) AS [DG]
     , convert(decimal(28, 8), [Gesamt]) AS [Gesamt]
     , convert(decimal(28, 8), [ben. Std. Auftrag_gesamt]) AS [ben. Std. Auftrag_gesamt]
     , convert(decimal(28, 8), [ben. Std. Auftrag]) AS [ben. Std. Auftrag]
     , convert(date, [Datum]) AS [Datum]
     , convert(varchar(20), [Jahr/Monat]) AS [Jahr/Monat]
     , convert(date, [Aktueller Tag]) AS [Aktueller Tag]
     , convert(date, [Monatserster]) AS [Monatserster]
     , convert(date, [Monatsletzter]) AS [Monatsletzter]
     , convert(varchar(100), [MUN_DATE]) AS [MUN_DATE]
     , convert(varchar(50), [Mitgliedsname]) AS [Mitgliedsname]
     , convert(date, [Beginn Geschäftsjahr]) AS [Beginn Geschäftsjahr]
     , convert(varchar(20), [Monat_Jahr]) AS [Monat_Jahr]
     , convert(varchar(20), [Jahr]) AS [Jahr]
     , convert(varchar(20), [Monat]) AS [Monat]
     , convert(varchar(20), [Monat_kurz]) AS [Monat_kurz]

FROM (SELECT [s].[Client_DB] AS [Client_DB]
           , [s].[Department Code] AS [Department Code]
           , [s].[Service Order No] AS [Auftrags-Nr.]
           , [s].[Gen Prod Posting Group] AS [Gen Prod Posting Group]
--    convert(varchar(100), s.[Serviceberater]) as [Serviceberater_alt],
           , coalesce([u].[Name], [s].[User Id], 'n.v.') AS [Benutzer Abr.]
           , iif([s].[Auftragsart] = ('Teile'), [s].[Serviceberater], [a].[Name]) AS [Serviceberater]
           , left([t].[Monteur_und_Stunden], 130) AS [Monteur]
           ,
--    s.[Hauptbetrieb],
--    s.[Standort],
          [s].[Umsatzart] AS [Umsatzart]
           , [s].[Model] AS [Model]
           , [s].[Fahrzeug] AS [Fahrzeug]
           , [s].[Marke] AS [Marke]
           , [s].[Auftragsart] AS [Auftragsart]
           , [s].[Kundenart] AS [Kundenart]
           , [s].[Kunde] AS [Kunde]
           ,
--    s.[Umsatz Lohn],
--    s.[Umsatz Teile Service],
--    s.[Einsatz Teile Service],
          [s].[Invoice Date] AS [Invoice Date]
           ,
--    s.[Order Number],
--    s.[Order Number_Rg_Ausg],
--    convert(decimal(28,8), s.[ben. AW_Time_Clock]) AS [ben. AW],
          12 * (isnull([t].[ben. Std. Auftrag], 0)) / nullif(count([s].[Client_DB]) OVER (PARTITION BY [s].[Service Order No]), 0) AS [ben. AW]
           , [s].[verk Std] AS [verk. AW]
--    s.[DG] AS DG_ALT,
           , [s].[Auftragsposition] AS [Auftragsposition]
           , [s].[SB_Gruppe] AS [SB_Gruppe]
           , [s].[Rabatt Lohn] AS [Rabatt Lohn]
           , [s].[Rabatt Teile] AS [Rabatt Teile]
           ,
--    s.[Order_Desc_100],
--    s.[Invoice_Desc_100],
--    s.[TÜV_Amount],
--    s.[FL_Lack_Amount],
--    s.[Mietw_Amount],
--    s.[Umsatz_Sonst_Rest],
          [s].[No] AS [Rg-Nr.]
           , [s].[No] + ' - ' + [s].[Service Order No] + ' - ' + [s].[Kunde] AS [RG/Auftrag/Kunde]
           , [s].[No] + ' - ' + [s].[Serviceberater] + ' - ' + [s].[Kunde] + ' - ' + format([s].[Invoice Date], 'dd.MM.yyyy') AS [RG/SB/Kunde/Datum]
           , [s].[Labor No] AS [Labor No]
           , [s].[Position] AS [Artikel / AW-Nr.]
           , [s].[Kunde_Verkaufskunde] AS [Kunde_Verkaufskunde]
           , [s].[Make Code] AS [Make Code]
           , [s].[Location Code] AS [Location Code]
           , [s].[Amount] AS [Amount]
           , [s].[Line Discount Amount] AS [Line Discount Amount]
           , [s].[Quantity] AS [Quantity]
           , [s].[Unit Cost (lcy)] AS [Unit Cost (lcy)]
           , [s].[Source Table] AS [Source Table]
           , [p].[Description] AS [PGRP-Bezeichnung]
           , [p].[Zuordnung] AS [Zuordnung]
           , isnull([m].[Fabrikat], 'Fremd') AS [Fabrikat]
           , [m].[Fabrikat_Sortierung] AS [Fabrikat_Sortierung]
           , [l].[Hauptbetrieb_ID] AS [Hauptbetrieb_ID]
           , [l].[Hauptbetrieb_Name] AS [Hauptbetrieb_Name]
           , [l].[Standort_ID] AS [Standort_ID]
           , [l].[Standort_Name] AS [Standort_Name]
           , '' AS [Fokus-Gruppe]
           , '' AS [Fokus-Untergruppe]
           , iif([p].[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.'), [s].[Amount], 0.0) AS [Lohn]
           , iif([p].[Zuordnung] IN ('Teile'), [s].[Amount], 0.0) AS [Teile]
           , iif([p].[Zuordnung] IN ('Sonst.'), [s].[Amount], 0.0) AS [Sonst.]
           , iif([p].[Zuordnung] IN (N'TÜV'), [s].[Amount], 0.0) AS [TÜV]
           , iif([p].[Zuordnung] IN ('Fremdl.'), [s].[Amount], 0.0) AS [Fremdl.]
           , iif([p].[Zuordnung] IN ('Mietw.'), [s].[Amount], 0.0) AS [Mietw.]
           , iif([p].[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.'), [s].[Line Discount Amount], 0.0) AS [NL Lohn]
           , iif([p].[Zuordnung] IN ('Teile'), [s].[Line Discount Amount], 0.0) AS [NL Teile]
           , iif([p].[Zuordnung] IN ('Teile'), ([s].[Quantity] * [s].[Unit Cost (lcy)]), 0.0) AS [EW Teile]
           , iif([p].[Zuordnung] IN ('Fremdl.'), ([s].[Quantity] * [s].[Unit Cost (lcy)]), 0.0) AS [EW Fremdl.]
           , iif([s].[Service Order No] <> 'Gutschrift ohne Auftrag', (1.0 / count([s].[Client_DB]) OVER (PARTITION BY [s].[Service Order No])), 0.0) AS [DG]
           , (
        iif([p].[Zuordnung] IN ('Mech.', 'Karo.', 'Lack', 'Elektr.'), [s].[Amount], 0.0) +
        iif([p].[Zuordnung] IN ('Teile'), [s].[Amount], 0.0) +
        iif([p].[Zuordnung] IN ('Sonst.'), [s].[Amount], 0.0) +
        iif([p].[Zuordnung] IN (N'TÜV'), [s].[Amount], 0.0) +
        iif([p].[Zuordnung] IN ('Fremdl.'), [s].[Amount], 0.0) +
        iif([p].[Zuordnung] IN ('Mietw.'), [s].[Amount], 0.0)
        ) AS [Gesamt]
           , isnull([t].[ben. Std. Auftrag], 0) AS [ben. Std. Auftrag_gesamt]
           , isnull([t].[ben. Std. Auftrag], 0) / nullif(count([s].[Client_DB]) OVER (PARTITION BY [s].[Service Order No]), 0) AS [ben. Std. Auftrag]
           , [s].[Invoice Date] AS [Datum]
           , getdate() AS [Aktueller Tag]
           , [k].[Jahr/Monat]
           , [k].[Monatserster]
           , [k].[Monatsletzter]
           , [k].[MUN_DATE]
           , [k].[Mitgliedsname]
           , [k].[Beginn Geschäftsjahr]
           , [k].[Monat_Jahr]
           , [k].[Jahr]
           , [k].[Monat]
           , [k].[Monat_kurz]
      FROM [GC_ETL_ARI].[load_2].[Service_gesamt] [s]
               LEFT JOIN [GC_ETL_ARI].[x_data].[Produktbuchungsgruppe] [p] ON [s].[Client_DB] = [p].[Client_DB]
          AND [s].[Gen Prod Posting Group] = [p].[Gen_ Prod_ Posting Group]
               LEFT JOIN [GC_ETL_ARI].[x_data].[Marke] [m] ON [s].[Make Code] = [m].[Make Code]
               LEFT JOIN [GC_ETL_ARI].[transform_basis].[Location_Standort] [l] ON [s].[Client_DB] = [l].[Client_DB]
          AND [s].[Location Code] = [l].[Location Code]
               LEFT JOIN [GC_ETL_ARI].[temp].[Time_Clock_Entry_fuer_ben_AW_Auftrag] [t] ON [s].[Client_DB] = [t].[Client_DB]
          AND [s].[Service Order No] = [t].[Service Order No_]
               LEFT JOIN [GC_ETL_ARI].[transform].[Auftraege_Serviceberater_Mitarbeiter] [a] ON [s].[Client_DB] = [a].[Client_DB]
          AND [s].[Service Order No] = [a].[No_]
               LEFT JOIN [GC_ETL_ARI].[transform_basis].[User_Mitarbeiter] [u] ON [s].[Client_DB] = [u].[Client_DB]
          AND [s].[User ID] = [u].[User ID]
               LEFT JOIN [transform].[Kalender_C11] [k] ON [s].[Invoice Date] = [k].[Datum]) AS [V1]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO