SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [load].[Forderungen]
AS
SELECT F.[Client_DB]
     , F.[Document Type]
     , F.[Document No]
     , F.[User Id]
     , F.[On Hold]
     , F.[Branch Code]
     , F.[Vin]
     , F.[Comment]
     , F.[offen]
     , F.[Gesamt offen KD (Info)]
     , F.[Invoice Date]
     , F.[Hauptbetrieb]
     , F.[Standort]
     , F.[Sel Name]
     , F.[Kunde]
	 , F.[Kunde_Dashboard]
     , F.[Beleg]
     , F.[Bereich]
     , F.[Tage]
     , F.[Staffel]
     , F.[Mahnstufe]
     , F.[Forderungsart]
     , F.[Abwarten]
     , F.[Message To Recipient]
     , F.[Haft_Kasko]
	 , S.[Hauptbetrieb_ID]
	 , S.[Hauptbetrieb_Name]
	 , S.[Standort_ID]
	 , S.[Standort_Name]
	 , S.[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] F
		LEFT OUTER JOIN x_data.AH_Standort S ON F.Client_DB = S.Client_DB AND F.[Branch Code] = S.[Branch Code]
WHERE S.[Bestand] IS NULL OR S.[Bestand] = '1'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO