ETL_DWH_LOCOSOFT/schema/GC/views/transform.NW_GW_VK.sql

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