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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[V_Verkauf]
AS
SELECT convert(varchar(20), [Client_DB]) AS [Client_DB]
     , convert(varchar(20), [Konto-Nr]) AS [Konto-Nr]
     , convert(decimal(28, 8), [Amount]) AS [Amount]
     , convert(decimal(28, 8), [Betrag]) AS [Betrag]
-- [Summe Betrag] und [Summe Erlös FZG] dienen nur als Filter
--      , convert(decimal(28, 8), [Summe Betrag]) AS [Summe Betrag]
--      , convert(decimal(28, 8), [Summe Erlös FZG]) AS [Summe Erlös FZG]
     , convert(varchar(20), [Branch Code]) AS [Branch Code]
     , convert(varchar(20), [Location Code]) AS [Location Code]
     , convert(varchar(20), [Vin]) AS [Vin]
     , convert(varchar(20), [Book No]) AS [Book No]
     , convert(varchar(10), [Vehicle Status]) AS [Vehicle Status]
     , convert(varchar(10), [Old Vehicle Status]) AS [Old Vehicle Status]
     , convert(varchar(20), [Make Code_Vehicle]) AS [Make Code_Vehicle]
     , convert(varchar(20), [Model]) AS [Model]
     , convert(varchar(50), [Modellbez]) AS [Modellbez]
     , convert(varchar(20), [Vat Prod Posting Group]) AS [Vat Prod Posting Group]
     , convert(varchar(20), [Inventory Posting Group]) AS [Inventory Posting Group]
     , convert(varchar(20), [Fahrzeugart]) AS [Fahrzeugart]
     , convert(varchar(20), [Fahrzeugtyp]) AS [Fahrzeugtyp]
     , convert(varchar(200), left([FZG], 200)) AS [FZG]
     , convert(varchar(100), [VB_Einkauf]) AS [VB_Einkauf]
     , convert(decimal(28, 8), [Standtage_ori]) AS [Standtage_ori]
     , convert(varchar(20), [Standtagestaffel]) AS [Standtagestaffel]
     , convert(decimal(28, 8), [Standtage]) AS [Standtage]
     , convert(datetime, [Invoice Date]) AS [Invoice Date]
     , convert(varchar(200), [Kunde]) AS [Kunde]
     , convert(varchar(50), [Kundenart]) AS [Kundenart]
     , convert(varchar(200), [Kunde_FZG_Dashboard]) AS [Kunde_FZG_Dashboard]
     , convert(varchar(200), [Kunde_FZG]) AS [Kunde_FZG]
     , convert(varchar(200), [Vorbesitzer]) AS [Vorbesitzer]
     , convert(varchar(100), [Verkäufer]) AS [Verkäufer]
     , convert(varchar(200), [Beleg]) AS [Beleg]
     , convert(varchar(200), left([FZG_Detail], 200)) AS [FZG_Detail]
     , convert(varchar(100), [Konto]) AS [Konto]
     , convert(decimal(28, 8), [Menge]) AS [Menge]
     , convert(int, [Tag]) AS [Tag]
     , convert(int, [Monat]) AS [Monat]
     , convert(int, [Jahr]) AS [Jahr]
     , convert(varchar(20), [Bereich Umsatz]) AS [Bereich Umsatz]
     , convert(varchar(50), [Ebene1]) AS [Ebene1]
     , convert(varchar(50), [Ebene2]) AS [Ebene2]
     , convert(varchar(50), [Ebene3]) AS [Ebene3]
     , convert(varchar(50), [Ebene4]) AS [Ebene4]
     , convert(varchar(50), [Ebene5]) AS [Ebene5]
     , convert(decimal(28, 8), [Erlös FZG]) AS [Erlös FZG]
     , convert(decimal(28, 8), [Einsatz FZG]) AS [Einsatz FZG]
     , convert(decimal(28, 8), [Boni]) AS [Boni]
     , convert(decimal(28, 8), [Instandsetz. ges.]) AS [Instandsetz. ges.]
     , 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(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), [Monat_kurz]) AS [Monat_kurz]

FROM (SELECT [VK].[Client_DB] AS [Client_DB]
           , [VK].[Vin] AS [Vin]
           , [VK].[Book No] AS [Book No]
           , sum([VK].[Betrag]) OVER (PARTITION BY [VK].[Client_DB], [VK].[Vin], [VK].[Book No]) AS [Summe Betrag]
           , sum(iif([K].[Ebene1] IN (N'Umsatzerlöse'), [VK].[Betrag] * -1, 0.0)) OVER (PARTITION BY [VK].[Client_DB], [VK].[Vin], [VK].[Book No]) AS [Summe Erlös FZG]

        -- Standtage und Menge geteilt durch die Anzahl der Rechnungspositionen
           , [VK].[Standtage_ori] * 1.0 / count(*) OVER (PARTITION BY [VK].[Client_DB], [VK].[Vin], [VK].[Book No]) AS [Standtage]
           , 1.0 / count(*) OVER (PARTITION BY [VK].[Client_DB], [VK].[Vin], [VK].[Book No]) AS [Menge]

           , [VK].[No] AS [Konto-Nr]
           , [VK].[Amount] AS [Amount]
           , [VK].[Betrag] AS [Betrag]
           , [VK].[Branch Code] AS [Branch Code]
           , [VK].[Location Code] AS [Location Code]
           , [VK].[Vehicle Status] AS [Vehicle Status]
           , [VK].[Old Vehicle Status] AS [Old Vehicle Status]
--  n.[Hauptbetrieb],
           , [VK].[Make Code_Vehicle] AS [Make Code_Vehicle]
           , [VK].[Model] AS [Model]
           , [VK].[Modellbez] AS [Modellbez]
           , [VK].[Vat Prod Posting Group] AS [Vat Prod Posting Group]
           , [VK].[Inventory Posting Group] AS [Inventory Posting Group]
--            , [n].[Fahrzeugart] AS [Fahrzeugart_alt]
--            , [n].[Fahrzeugtyp] AS [Fahrzeugtyp_alt]
           , [FT].[Zuordnung_Fahrzeugart] AS [Fahrzeugart]
           , [FT].[Inventory Posting Group] AS [Fahrzeugtyp]
           --   convert(varchar(100), n.[FZG]) as [FZG],
           , right([VK].[VIN], 7) + ' / ' + isnull([VK].[Model], 'n.N.') + ' / ' + isnull([VK].[Branch Code], 'n.N.') + ' / ' + isnull([VK].[Kunde], 'n.N.') AS [FZG]
           , [VK].[VB_Einkauf] AS [VB_Einkauf]
           , [VK].[Standtage_ori] AS [Standtage_ori]
           , [VK].[Standtagestaffel] AS [Standtagestaffel]
--    convert(decimal(28,8), n.[Standtage]) as [Standtage],
           , [VK].[Invoice Date] AS [Invoice Date]
           , isnull([VK].[Kunde], 'n.N.') AS [Kunde]
           , isnull([VK].[Kundenart], 'n.N.') AS [Kundenart]
           , isnull([FK].[Kunde_FZG_Dashboard], 'n.N.') AS [Kunde_FZG_Dashboard]
           , isnull([FK].[Kunde_FZG], 'n.N.') AS [Kunde_FZG]
           , [VK].[Vorbesitzer] AS [Vorbesitzer]
           ,
--  n.[Erlös] AS [Erlös_alt],
--  n.[Einsatz] AS [Einsatz_alt],
--  n.[Bonus] AS [Bonus_alt],
--  n.[int. Kosten] AS [int. Kosten_alt],
--  n.[Fabrikat],
          isnull([VK].[Verkäufer], 'n.N.') AS [Verkäufer]
           , [VK].[Beleg] AS [Beleg]
           ,
--  n.[Standort],
--    convert(varchar(200), n.[FZG_Detail]) as [FZG_Detail],
          isnull([VK].[VIN], 'n.N.') + ' - ' + isnull([VK].[Location Code], 'n.N.') + ' - ' + isnull([VK].[Fahrzeugtyp], 'n.N.') + ' - ' + isnull([VK].[Verkäufer], 'n.N.') + ' - ' + isnull([VK].[Kunde], 'n.N.') + ' - ' +
          isnull(format([VK].[Invoice Date], 'dd.MM.yyyy'), 'n.N.') AS [FZG_Detail]
           , [VK].[Konto] AS [Konto]
--  n.[Menge_final],
           , [VK].[Bereich Umsatz] AS [Bereich Umsatz]
           , [K].[Ebene1] AS [Ebene1]
           , [K].[Ebene2] AS [Ebene2]
           , [K].[Ebene3] AS [Ebene3]
           , [K].[Ebene4] AS [Ebene4]
           , [K].[Ebene5] AS [Ebene5]
           , iif([K].[Ebene1] IN (N'Umsatzerlöse'), [VK].[Betrag] * -1, 0.0) AS [Erlös FZG]
           , iif([K].[Ebene1] IN ('Materialaufwand'), [VK].[Betrag], 0.0) AS [Einsatz FZG]
           , iif([K].[Ebene1] IN ('Materialaufwand') AND left([K].[Ebene3], 4) IN ('2080', '2140', '3001', '3081'), [VK].[Betrag] * -1, 0.0) AS [Boni]
           , iif([K].[Ebene1] IN ('Kosten'), [VK].[Betrag], 0.0) AS [Instandsetz. ges.]
           , isnull([Marke].[Fabrikat], 'Fremd') AS [Fabrikat]
           , [Marke].[Fabrikat_Sortierung] AS [Fabrikat_Sortierung]
           , [Standort].[Hauptbetrieb_ID] AS [Hauptbetrieb_ID]
           , [Standort].[Hauptbetrieb_Name] AS [Hauptbetrieb_Name]
           , [Standort].[Standort_ID] AS [Standort_ID]
           , [Standort].[Standort_Name] AS [Standort_Name]
           , [VK].[Invoice Date] AS [Datum]
           , getdate() AS [Aktueller Tag]
           , [Kalender].[Jahr/Monat]
           , [Kalender].[Monatserster]
           , [Kalender].[Monatsletzter]
           , [Kalender].[MUN_DATE]
           , [Kalender].[Mitgliedsname]
           , [Kalender].[Beginn Geschäftsjahr]
           , [Kalender].[Monat_Jahr]
           , [Kalender].[Jahr]
           , [Kalender].[Monat]
           , [Kalender].[Tag]
           , [Kalender].[Monat_kurz]
      FROM [transform].[NW_GW_VK_TIM] [VK]
               INNER JOIN [x_data].[Fahrzeugtyp] [FT] ON [FT].[Inventory Posting Group] = [VK].[Inventory Posting Group]
               INNER JOIN [x_data].[Kontenrahmen] [K] ON [K].[Konto_Nr] = [VK].[No]

               LEFT JOIN [x_data].[Marke] [Marke] ON [VK].[Make Code_Vehicle] = [Marke].[Make Code]
               LEFT JOIN [x_data].[AH_Standort] [Standort] ON [VK].[Client_DB] = [Standort].[Client_DB]
          AND [VK].[Location Code] = [Standort].[Branch Code]
               LEFT JOIN [temp].[Fahrzeug_Kunde] [FK] ON [VK].[Client_DB] = [FK].[Client_DB]
          AND [VK].[Vin] = [FK].[VIN]

               LEFT JOIN [transform].[Kalender_C11] [Kalender] ON [VK].[Invoice Date] = [Kalender].[Datum]
      WHERE [FT].[Zuordnung_Fahrzeugart] IN ('NA', 'GA')
        AND ([K].[Ebene2] IN (N'Umsatzerlöse NA', N'Umsatzerlöse GA', 'Materialaufwand NA', 'Materialaufwand GA') OR [K].[Ebene3] IN ('1 - NA', '2 - GA'))) AS [V1]

WHERE
-- Stornierte Fahrzeuge werden herausgefiltert (Rechnung - Gutschrift = 0)
    [Summe Betrag] <> 0
-- interne Verbuchungen mit Rundungsfehler (10 Cent Toleranz)
  AND ([Summe Erlös FZG] < -0.1 OR [Summe Erlös FZG] > 0.1)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO