SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW [transform].[NW_GW_VK] AS select "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", "LOC_Belege_NW_GW_VK"."Verkäufer" AS "Verkäufer", "LOC_Belege_NW_GW_VK"."Fabrikat" AS "Fabrikat", 0 AS "Fabrikat_Order_By", "LOC_Belege_NW_GW_VK"."Model" AS "Model", "LOC_Belege_NW_GW_VK"."FZG" AS "FZG", "LOC_Belege_NW_GW_VK"."Konto" AS "Konto", "LOC_Belege_NW_GW_VK"."Model_Detail" AS "Modell_Beschreibung", "LOC_Belege_NW_GW_VK"."Kunde" AS "Kunde", "LOC_Belege_NW_GW_VK"."Kundenart" AS "Kundenart", "LOC_Belege_NW_GW_VK"."Fahrzeugart" AS "Fahrzeugart", "LOC_Belege_NW_GW_VK"."Fahrzeugtyp" AS "Fahrzeugtyp", '' AS "Fahrzeugtyp_1", left("LOC_Belege_NW_GW_VK"."Vehicle Reference_berechnet", 8) + '/' + left("LOC_Belege_NW_GW_VK"."Internal Number_Vehicles", 5) + ' - ' + "GC_Department"."Standort_Name" + ' - ' + "LOC_Belege_NW_GW_VK"."Fahrzeugtyp" + ' - ' + "LOC_Belege_NW_GW_VK"."Verkäufer" + ' - ' + "LOC_Belege_NW_GW_VK"."Kunde" + ' - ' + CONVERT(VARCHAR(10), "LOC_Belege_NW_GW_VK"."Invoice Date", 121) AS "FZG_Detail", "LOC_Belege_NW_GW_VK"."Text" AS "Buch_Text", left("LOC_Belege_NW_GW_VK"."Free Form Accounting Text", 1) AS "PLZ_1", left("LOC_Belege_NW_GW_VK"."Free Form Accounting Text", 2) AS "PLZ_2", left("LOC_Belege_NW_GW_VK"."Free Form Accounting Text", 3) AS "PLZ_3", left("LOC_Belege_NW_GW_VK"."Free Form Accounting Text", 4) AS "PLZ_4", "LOC_Belege_NW_GW_VK"."Free Form Accounting Text" AS "PLZ_Code_Deb", '' AS "Name_Lieferant", '' AS "DB1___0", "LOC_Belege_NW_GW_VK"."Contra Account Text" AS "Name_Einkäufer", "LOC_Belege_NW_GW_VK"."Menge2" AS "Menge", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös FZG' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Erlös_FZG", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK FZG' then "LOC_Belege_NW_GW_VK"."Betrag" else 0 end AS "Einsatz_FZG", "LOC_Belege_NW_GW_VK"."Boni VK-Hilfen" AS "Boni", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Nachlass' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Nachlass", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Prov.' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Erlös_Prov_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Überf.' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Erlös_Überf_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Überf.' then "LOC_Belege_NW_GW_VK"."Betrag" else 0 end AS "VAK_Überf_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Gar.' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Erlös_Gar_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Gar.' then "LOC_Belege_NW_GW_VK"."Betrag" else 0 end AS "VAK_Gar_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Zulass.' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Erlös_Zul_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Zulass.' then "LOC_Belege_NW_GW_VK"."Betrag" else 0 end AS "VAK_Zul_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Nachr./Aufb.' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Erlös_Nachr_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Nachr./Aufb.' then "LOC_Belege_NW_GW_VK"."Betrag" else 0 end AS "VAK_Nachr_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Sonst._8900' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "Erlös_Sonst_", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Sonst.' then "LOC_Belege_NW_GW_VK"."Betrag" * -1 else 0 end AS "VAK_Sonst_", 0 AS "VAK_Inst_setz_", "LOC_Belege_NW_GW_VK"."Customer Number" AS "Standtage", 0 AS "Instands__Werkstatt", 0 AS "Instands__Teile", 0 AS "Instandsetzung_ges_", "LOC_Belege_NW_GW_VK"."Erlös ges " AS "Gesamterlöse", "LOC_Belege_NW_GW_VK"."VAK ges " AS "Einsatz_Gesamt", "LOC_Belege_NW_GW_VK"."Invoice Date" AS "Invoice_Date", case when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK intern' then "LOC_Belege_NW_GW_VK"."Betrag" else 0 end AS "VAK_intern", "LOC_Belege_NW_GW_VK"."Document Type Description" AS "PKW_NFZ" from (locosoft."LOC_Belege_NW_GW_VK" "LOC_Belege_NW_GW_VK" LEFT OUTER JOIN "data"."GC_Department" "GC_Department" on "LOC_Belege_NW_GW_VK"."Hauptbetrieb" = "GC_Department"."Hauptbetrieb" and "LOC_Belege_NW_GW_VK"."Standort" = "GC_Department"."Standort") LEFT OUTER JOIN locosoft."Kontenrahmen_GC_Struct_SKR" "Kontenrahmen_GC_Struct_SKR" on "LOC_Belege_NW_GW_VK"."Acct Nr" = "Kontenrahmen_GC_Struct_SKR"."Konto Nr" GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO GO