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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[F_Belege]
AS
SELECT convert(varchar(20), [Client_DB]) AS [Client_DB]
     , convert(varchar(20), [Department Code]) AS [Department Code]
     , convert(varchar(20), [Make Code]) AS [Make Code]
     , convert(varchar(20), [Branch Code]) AS [Branch Code]
     , convert(datetime, [Bookkeep Date]) AS [Bookkeep Date]
     , convert(varchar(20), [Acct Nr]) AS [Acct Nr]
     , convert(decimal(28, 8), [Betrag]) AS [Betrag]
     , convert(varchar(300), [Text]) AS [Text]
     , convert(varchar(300), [Beleg]) AS [Beleg]
     , convert(varchar(20), [VIN]) AS [VIN]
     , 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(20), [Marke]) AS [Marke]
     , convert(int, [Fabrikat_Sortierung]) AS [Fabrikat_Sortierung]
     , 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 [Belege].[Client_DB] AS [Client_DB]
           , [Belege].[Department Code] AS [Department Code]
           , [Belege].[Make Code] AS [Make Code]
           , [Belege].[Branch Code] AS [Branch Code]
           , [Belege].[Bookkeep Date] AS [Bookkeep Date]
--     , Belege.[Betrieb Nr]
           , [Belege].[Acct Nr] AS [Acct Nr]
           , [Belege].[Betrag] AS [Betrag]
           , [Belege].[Text] AS [Text]
           , [Belege].[Beleg] AS [Beleg]
           , [Belege].[VIN] AS [VIN]
--     , Belege.[Hauptbetrieb]
--     , Belege.[Marke]
           , [Standort].[Hauptbetrieb_ID] AS [Hauptbetrieb_ID]
           , [Standort].[Hauptbetrieb_Name] AS [Hauptbetrieb_Name]
           , [Standort].[Standort_ID] AS [Standort_ID]
           , [Standort].[Standort_Name] AS [Standort_Name]
           , isnull([Marke].[Fabrikat], 'Fremd') AS [Marke]
           , [Marke].[Fabrikat_Sortierung] AS [Fabrikat_Sortierung]
           , [Belege].[Bookkeep 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].[Monat_kurz]
      FROM [transform].[Belege] [Belege]
               LEFT JOIN [x_data].[Marke] [Marke] ON [Belege].[Make Code] = [Marke].[Make Code]
               LEFT JOIN [x_data].[AH_Standort] [Standort] ON [Belege].[Client_DB] = [Standort].[Client_DB]
               LEFT JOIN [transform].[Kalender_C11] [Kalender] ON [Belege].[Bookkeep Date] = [Kalender].[Datum]
          AND [Belege].[Branch Code] = [Standort].[Branch Code]) AS [Belege_Join]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO