SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[Forderungen]
AS
SELECT convert(varchar(20), [Client_DB]) AS [Client_DB]
     , convert(varchar(20), [Document Type]) AS [Document Type]
     , convert(varchar(30), [Document No]) AS [Document No]
     , convert(varchar(50), [User Id]) AS [User Id]
     , convert(varchar(20), [On Hold]) AS [On Hold]
     , convert(varchar(20), [Branch Code]) AS [Branch Code]
     , convert(varchar(20), [Vin]) AS [Vin]
     , convert(varchar(200), left([Comment], 200)) AS [Comment]
     , convert(decimal(28, 8), [offen]) AS [offen]
     , convert(decimal(28, 8), [Gesamt offen KD (Info)]) AS [Gesamt offen KD (Info)]
     , convert(datetime, [Invoice Date]) AS [Invoice Date]
     , convert(varchar(20), [Hauptbetrieb]) AS [Hauptbetrieb]
     , convert(varchar(20), [Standort]) AS [Standort]
     , convert(varchar(100), left([Sel Name], 100)) AS [Sel Name]
     , convert(varchar(100), left([Kunde], 100)) AS [Kunde]
     , convert(varchar(100), left([Kunde_Dashboard], 100)) AS [Kunde_Dashboard]
     , convert(varchar(200), left([Beleg], 200)) AS [Beleg]
     , convert(varchar(20), [Bereich]) AS [Bereich]
     , convert(int, [Tage]) AS [Tage]
     , convert(varchar(20), [Staffel]) AS [Staffel]
     , convert(varchar(10), [Mahnstufe]) AS [Mahnstufe]
     , convert(varchar(20), [Forderungsart]) AS [Forderungsart]
     , convert(varchar(100), left([Abwarten], 100)) AS [Abwarten]
     , convert(varchar(100), left([Message To Recipient], 100)) AS [Message To Recipient]
     , convert(varchar(20), [Haft_Kasko]) AS [Haft_Kasko]
     , 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), [Branch ID]) AS [Branch ID]
     , convert(decimal(28, 8), [< 2 Wochen]) AS [< 2 Wochen]
     , convert(decimal(28, 8), [2 - 4 Wochen]) AS [2 - 4 Wochen]
     , convert(decimal(28, 8), [4 - 6 Wochen]) AS [4 - 6 Wochen]
     , convert(decimal(28, 8), [6 - 12 Wochen]) AS [6 - 12 Wochen]
     , convert(decimal(28, 8), [> 12 Wochen]) AS [> 12 Wochen]
     , convert(decimal(28, 8), [noch nicht fällig]) AS [noch nicht fällig]
FROM (SELECT [o].[Client_DB]
           , [o].[Document Type]
           , [o].[Document No]
           , [o].[User Id]
           , [o].[On Hold]
           , [o].[Branch Code]
           , [o].[Vin]
           , [o].[Comment]
           , [o].[offen]
           , [o].[Gesamt offen KD (Info)]
           , [o].[Invoice Date]
           , [o].[Hauptbetrieb]
           , [o].[Standort]
           , [o].[Sel Name]
           , [o].[Kunde]
           , [o].[Kunde_Dashboard]
           , [o].[Beleg]
           , [o].[Bereich]
           , [o].[Tage]
           , [o].[Staffel]
           , [o].[Mahnstufe]
           , [o].[Forderungsart]
           , [o].[Abwarten]
           , [o].[Message To Recipient]
           , [o].[Haft_Kasko]
           , [s].[Hauptbetrieb_ID]
           , [s].[Hauptbetrieb_Name]
           , [s].[Standort_ID]
           , [s].[Standort_Name]
           , [s].[Branch ID]

           , iif([Staffel] = ' < 2 Wochen', [o].[offen], NULL) AS [< 2 Wochen]
           , iif([Staffel] = ' 2 - 4 Wochen', [o].[offen], NULL) AS [2 - 4 Wochen]
           , iif([Staffel] = ' 4 - 6 Wochen', [o].[offen], NULL) AS [4 - 6 Wochen]
           , iif([Staffel] = ' 6 - 12 Wochen', [o].[offen], NULL) AS [6 - 12 Wochen]
           , iif([Staffel] = '> 12 Wochen', [o].[offen], NULL) AS [> 12 Wochen]
           , iif([Staffel] = N'noch nicht fällig', [o].[offen], NULL) AS [noch nicht fällig]
      FROM [transform].[OP_ARI] [o]
               LEFT OUTER JOIN [x_data].[AH_Standort] [s] ON [o].[Client_DB] = [s].[Client_DB] AND [o].[Branch Code] = [s].[Branch Code]) AS [V1]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO