SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform].[offene_Auftraege_Ums_ben_AW] AS

SELECT "offene_Auftraege_Ums_ben_AW"."Hauptbetrieb" AS [Hauptbetrieb_imr]
     , "offene_Auftraege_Ums_ben_AW"."Standort" AS [Standort_imr]
     , "GC_Department"."Hauptbetrieb_ID" AS "Hauptbetrieb_ID"
     , "GC_Department"."Hauptbetrieb_Name" AS "Hauptbetrieb_Name"
     , "GC_Department"."Standort_ID" AS "Standort_ID"
     , "GC_Department"."Standort_Name" AS "Standort_Name"
     , "offene_Auftraege_Ums_ben_AW"."Serviceberater" AS "Serviceberater"
     , "offene_Auftraege_Ums_ben_AW"."Order Number" AS "Order_Number"
     , "offene_Auftraege_Ums_ben_AW"."Fabrikat" AS [Fabrikat_imr]
     , CASE
           WHEN "GC_Marken"."Fabrikat" IS NULL THEN 'Fremd'
           ELSE
               "GC_Marken"."Fabrikat"
       END AS "Fabrikat"
     , CASE
           WHEN CASE
                    WHEN "GC_Marken"."Fabrikat" IS NULL THEN 'Fremd'
                    ELSE
                        "GC_Marken"."Fabrikat"
                END = 'Fremd' THEN 9
           ELSE "GC_Marken"."Order_By"
       END AS "Fabrikat_Order_By"
     , "offene_Auftraege_Ums_ben_AW"."Model" AS "Model"
     , '' AS "Fahrzeug"
     , '' AS "Kostenstelle"
     , "offene_Auftraege_Ums_ben_AW"."Description_Makes" AS "Marke"
     , "offene_Auftraege_Ums_ben_AW"."Kunde" AS "Kunde"
     , "offene_Auftraege_Ums_ben_AW"."Umsatzart" AS "Turnover_Type_Desc"
     , 0 AS "Tage_offen"
     , 0 AS "DG"
     , "offene_Auftraege_Ums_ben_AW"."Umsatz Lohn" AS "Lohn"
     , 0 AS "Teile"
     , 0 AS "Sonst_"
     , "offene_Auftraege_Ums_ben_AW"."ben. Std" AS "ben_Std"
     , "offene_Auftraege_Ums_ben_AW"."Order Date" AS "Invoice_Date"
     , "offene_Auftraege_Ums_ben_AW"."labour_operation_id" AS "labour_operation_id"
	 , "offene_Auftraege_Ums_ben_AW"."charge_type" AS "charge_type"
	 , "offene_Auftraege_Ums_ben_AW"."Order Position_Labours" AS "Order Position_Labours"
	 , "offene_Auftraege_Ums_ben_AW"."Text Line" AS "Text Line"
	 , LTRIM(RTRIM(
      CAST("offene_Auftraege_Ums_ben_AW"."Order Position_Labours" AS VARCHAR(20))
      + ' - '
      + ISNULL(LTRIM(RTRIM("offene_Auftraege_Ums_ben_AW"."labour_operation_id")), '')
      + ' - '
      + ISNULL(LTRIM(RTRIM("offene_Auftraege_Ums_ben_AW"."Text Line")), '')
      )) AS "Position"
FROM [transform_imr]."offene_Auftraege_Ums_ben_AW" "offene_Auftraege_Ums_ben_AW"
         LEFT OUTER JOIN "data"."GC_Department" "GC_Department" ON "offene_Auftraege_Ums_ben_AW"."Hauptbetrieb" = "GC_Department"."Hauptbetrieb" AND "offene_Auftraege_Ums_ben_AW"."Standort" = "GC_Department"."Standort"
         LEFT OUTER JOIN "data"."GC_Marken" "GC_Marken" ON "offene_Auftraege_Ums_ben_AW"."Fabrikat" = "GC_Marken"."Description"
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
