SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [load].[OP_ARI]
AS
SELECT [transform].[OP_ARI].[Client_DB]
     , [transform].[OP_ARI].[Document Type]
     , [transform].[OP_ARI].[Document No]
     , [transform].[OP_ARI].[User Id]
     , [transform].[OP_ARI].[On Hold]
     , [transform].[OP_ARI].[Branch Code]
     , [transform].[OP_ARI].[Vin]
     , [transform].[OP_ARI].[Comment]
     , [transform].[OP_ARI].[offen]
     , [transform].[OP_ARI].[Gesamt offen KD (Info)]
     , [transform].[OP_ARI].[Invoice Date]
     , [transform].[OP_ARI].[Hauptbetrieb]
     , [transform].[OP_ARI].[Standort]
     , [transform].[OP_ARI].[Sel Name]
     , [transform].[OP_ARI].[Kunde]
	 , [transform].[OP_ARI].[Kunde_Dashboard]
     , [transform].[OP_ARI].[Beleg]
     , [transform].[OP_ARI].[Bereich]
     , [transform].[OP_ARI].[Tage]
     , [transform].[OP_ARI].[Staffel]
     , [transform].[OP_ARI].[Mahnstufe]
     , [transform].[OP_ARI].[Forderungsart]
     , [transform].[OP_ARI].[Abwarten]
     , [transform].[OP_ARI].[Message To Recipient]
     , [transform].[OP_ARI].[Haft_Kasko]
	 , [x_data].[AH_Standort].[Hauptbetrieb_ID]
	 , [x_data].[AH_Standort].[Hauptbetrieb_Name]
	 , [x_data].[AH_Standort].[Standort_ID]
	 , [x_data].[AH_Standort].[Standort_Name]
	 , [x_data].[AH_Standort].[Branch ID]
	 , CASE WHEN Staffel = ' < 2 Wochen' THEN [offen]  ELSE NULL END AS '< 2 Wochen'
	 , CASE WHEN Staffel = ' 2 - 4 Wochen' THEN [offen]  ELSE NULL END AS '2 - 4 Wochen' 
     , CASE WHEN Staffel = ' 4 - 6 Wochen' THEN [offen]  ELSE NULL END AS '4 - 6 Wochen' 
     , CASE WHEN Staffel = ' 6 - 12 Wochen' THEN [offen]  ELSE NULL END AS '6 - 12 Wochen' 
     , CASE WHEN Staffel = '> 12 Wochen' THEN [offen]  ELSE NULL END  AS '> 12 Wochen' 
	 , CASE WHEN Staffel = 'noch nicht fällig' THEN [offen]  ELSE NULL END  AS 'noch nicht fällg'
FROM [transform].[OP_ARI]
		LEFT OUTER JOIN x_data.AH_Standort ON [transform].OP_ARI.Client_DB = x_data.AH_Standort.Client_DB AND [transform].OP_ARI.[Branch Code] = x_data.AH_Standort.[Branch Code]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO