SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[NW_GW_VK_Journal_Accountings] 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",
       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",
       "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", 7) + '/' +
       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"                                                                                   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",
       ''                                                                                                              AS "Name_Einkäufer",
       "LOC_Belege_NW_GW_VK"."Menge_2"                                                                                  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",
       case
           when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Boni/VK-Hilfen' then "LOC_Belege_NW_GW_VK"."Betrag"
           else 0 end                                                                                                  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_aus_imr",
       "LOC_Belege_NW_GW_VK"."VAK ges "                                                                                AS "Einsatz_Gesamt_aus_imr",
       "LOC_Belege_NW_GW_VK"."Invoice Date"                                                                            AS "Invoice_Date",
       ((case
             when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös FZG') then "LOC_Belege_NW_GW_VK"."Betrag" * -1
             else 0 end + case
                              when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Nachlass')
                                  then "LOC_Belege_NW_GW_VK"."Betrag" * -1
                              else 0 end) + (case
                                                 when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Prov.')
                                                     then "LOC_Belege_NW_GW_VK"."Betrag" * -1
                                                 else 0 end + case
                                                                  when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Überf.')
                                                                      then "LOC_Belege_NW_GW_VK"."Betrag" * -1
                                                                  else 0 end)) + ((case
                                                                                       when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Gar.')
                                                                                           then "LOC_Belege_NW_GW_VK"."Betrag" * -1
                                                                                       else 0 end + case
                                                                                                        when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Zulass.')
                                                                                                            then "LOC_Belege_NW_GW_VK"."Betrag" * -1
                                                                                                        else 0 end) +
                                                                                  (case
                                                                                       when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Erlös Nachr./Aufb.')
                                                                                           then "LOC_Belege_NW_GW_VK"."Betrag" * -1
                                                                                       else 0 end + 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_ges_",
       case
           when "Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK intern' then "LOC_Belege_NW_GW_VK"."Betrag"
           else 0 end                                                                                                  AS "VAK_intern",
       ((case when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK FZG') then "LOC_Belege_NW_GW_VK"."Betrag" else 0 end +
         case
             when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK intern') then "LOC_Belege_NW_GW_VK"."Betrag"
             else 0 end) + (case
                                when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'Boni/VK-Hilfen')
                                    then "LOC_Belege_NW_GW_VK"."Betrag"
                                else 0 end + case
                                                 when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Überf.')
                                                     then "LOC_Belege_NW_GW_VK"."Betrag"
                                                 else 0 end)) + ((case
                                                                      when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Gar.')
                                                                          then "LOC_Belege_NW_GW_VK"."Betrag"
                                                                      else 0 end + case
                                                                                       when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Zulass.')
                                                                                           then "LOC_Belege_NW_GW_VK"."Betrag"
                                                                                       else 0 end) + (case
                                                                                                          when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Nachr./Aufb.')
                                                                                                              then "LOC_Belege_NW_GW_VK"."Betrag"
                                                                                                          else 0 end +
                                                                                                      case
                                                                                                          when ("Kontenrahmen_GC_Struct_SKR"."Konto 2" = 'VAK Sonst.')
                                                                                                              then "LOC_Belege_NW_GW_VK"."Betrag" * -1
                                                                                                          else 0 end)) AS "VAK_ges_",
       "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 "data"."GC_Marken" "GC_Marken"
      on "LOC_Belege_NW_GW_VK"."Fabrikat" = "GC_Marken"."Description" and
         "LOC_Belege_NW_GW_VK"."Rechtseinheit" = "GC_Marken"."Client_DB")
         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
